SELECT ScheduleItems.ScheduleItems_ID, ScheduleItems.Rev_Code, ScheduleItems.HCPCS, ScheduleItems.Visit_Date, ScheduleItems.Total_Units, ScheduleItems.Total_EClaim, ScheduleItems.Payer_ID, ScheduleItems.Assessment_Tracker_ID FROM #Request.prefix_db_agency#.ScheduleItems WHERE ScheduleItems_ID IN (#Form.ScheduleItems_ID#) Insert Into #Request.prefix_db_lookup#.Claim (Agency_Id, ScheduleItems_ID, Rev_Code, HCPCS, Total_Units, Visit_Date, Total_EClaim, Assessment_Tracker_ID, Payer_ID, Ticket) values ('#session.AgencyId#', '#ScheduleItems_Id#', '#Rev_Code#', '#HCPCS#', #decimalformat(Total_Units)#, '#DateFormat(Visit_Date,"yyyy-mm-dd")#', #NumberFormat(Total_EClaim,'_____.__')#, #Assessment_Tracker_ID#, #Payer_ID#, 1) Insert Into #Request.prefix_db_lookup#.ClaimII (Agency_Id, ScheduleItems_ID) values ('#session.AgencyId#', '#ScheduleItems_Id#') SELECT s.ScheduleItems_ID, s.Rev_Code, s.HCPCS, s.Total_Units, a.Certification_Start, s.Assessment_Tracker_ID, s.Payer_ID, s.Total_EClaim,s.Visit_Date FROM #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_lookup#.Claim c, #Request.prefix_db_agency#.Assessment_New a WHERE s.ScheduleItems_ID = c.ScheduleItems_ID and s.Assessment_Tracker_ID = a.Assessment_Tracker_ID and a.Assessment_Reason_M0100 = '1 - Start of care - further visits planned' and s.Deleted = 0 SELECT s.ScheduleItems_ID, s.Revenue_Code, s.HCPCS, s.Total_Units, c.Visit_Date, s.Assessment_Tracker_ID, s.Payer_ID, s.Total_Price, s.Ticket FROM #Request.prefix_db_lookup#.Supply_Sched s, #Request.prefix_db_lookup#.Claim c WHERE s.ScheduleItems_ID = c.ScheduleItems_ID and s.Deleted = 0 Insert IGNORE Into #Request.prefix_db_lookup#.Claim (Agency_Id,ScheduleItems_ID, Rev_Code, HCPCS, Total_Units, Visit_Date, Total_EClaim, Assessment_Tracker_ID, Payer_ID, Ticket) values (#session.AgencyId#,'#ScheduleItems_Id#', '#Revenue_Code#', '#HCPCS#', #decimalformat(Total_Units)#, '#DateFormat(Visit_Date,"yyyy-mm-dd")#', #NumberFormat(Total_Price,'_____.__')#, #Assessment_Tracker_ID#, #Payer_ID#, 1) UPDATE IGNORE #Request.prefix_db_agency#.patient p, #Request.prefix_db_lookup#.Physician h, #Request.prefix_db_lookup#.Employee e, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_lookup#.Claim c set a.HHRG_450_Score = h.Natl_Provider_ID, p.Lives_With_M0340 = e.Last_Name, p.Race_M0140_NA = e.First_Name WHERE p.Agency_ID = #session.AgencyId# AND p.Certifying_Physician = h.Physician_ID AND p.Certifying_Physician != 0 and p.Patient_ID = a.Patient_ID and a.Assessment_Tracker_ID = c.Assessment_Tracker_ID AND h.Employee_ID = e.Employee_ID SELECT Claim.ScheduleItems_ID, Claim.Claim_ID, Claim.Rev_Code, Claim.HCPCS, Claim.Total_Units, Claim.Total_EClaim, Claim.Visit_Date, Assessment_New.Assessment_ID, Assessment_New.Assessment_Reason_M0100,Assessment_New.Start_of_Care_M0030, Assessment_New.Certification_Start, Assessment_New.Certification_End, Assessment_New.Source_of_Admission, patient.Admission_Status, Assessment_New.HIPPS_Code AS HIPPS, Assessment_New.Admission_Description,Assessment_New.Homebound_Status,Assessment_New.Assessment_Reason_M0100, Assessment_New.Assessment_Tracker_ID, patient.HI_Claim_Number, Assessment_New.Claim_Key, Assessment_New.Information_Release, Assessment_New.HHRG_175_Score, Assessment_New.HHRG_230_Score, Assessment_New.HHRG_250_Score, Assessment_New.HHRG_390_Score, Assessment_New.HHRG_420_Score, Assessment_New.HHRG_440_Score, Assessment_New.HHRG_450_Score, patient.Patient_ID, patient.Last_Name_M0040,patient.Patient_ID, patient.First_Name_M0040, patient.Middle_Initial_M0040, patient.Street_Address, patient.Gender_M0069, patient.City, patient.State_M0050, patient.Zip_M0060, patient.DOB_M0066, patient.MSA_Code, patient.FIPS_Code, patient.Agency_Assigned_ID, patient.Admission_Status, patient.Lives_With_M0340, patient.Race_M0140_NA, Agency.Agency_Name, Agency.Agency_Medicare_ID, Agency.Agency_Address1, Agency.Agency_City, Agency.Agent_FEIN, Agency.Agency_State, Agency.Agency_Zip, Agency.Agency_Contact, Agency.Agency_Contact_Phone, Agency.Natl_Provider_ID AS ANPI, Agency.Agency_ID, Agency.Hospice_Medicare_NPI AS HNPI, Physician.Natl_Provider_ID AS PNPI, Employee.Last_Name as LastName, Employee.First_Name as FirstName, Payer.Payer_ID, Payer.Submitter_ID, Payer.Receiver_ID, Payer.PayName, Payer.Payer_Number, Payer.Address1 AS PayAdd1, Payer.Address2 AS PayAdd2, Payer.City AS PayCity, Payer.State AS PayState, Payer.Zip AS PayZip,Payer.TurboClaim, (SELECT MIN(Visit_Date) FROM #Request.prefix_db_lookup#.Claim WHERE Agency_Id = '#session.AgencyId#' AND Assessment_Tracker_Id = Assessment_New.Assessment_Tracker_Id ) AS FirstVisitDate ,(SELECT MIN(Total_Units) FROM #Request.prefix_db_lookup#.Claim WHERE Agency_Id = '#session.AgencyId#' AND Assessment_Tracker_Id = Assessment_New.Assessment_Tracker_Id ) AS Units FROM #Request.prefix_db_agency#.Assessment_New, #Request.prefix_db_agency#.patient, #Request.prefix_db_lookup#.Agency, #Request.prefix_db_lookup#.Physician, #Request.prefix_db_agency#.Payer, #Request.prefix_db_lookup#.Claim, #Request.prefix_db_lookup#.Employee WHERE Claim.Agency_Id = '#session.AgencyId#' AND Claim.Ticket = 1 AND Claim.Total_EClaim > '0.0000' AND (Claim.Visit_Date >= Assessment_New.Certification_Start or Claim.Visit_Date is null) AND Claim.Assessment_Tracker_ID = Assessment_New.Assessment_Tracker_ID AND Assessment_New.Patient_ID = patient.Patient_ID AND Assessment_New.Agency_Number = Agency.Agency_ID AND patient.Primary_ReferringPhys_ID_M0072 = Physician.Physician_ID AND Claim.Payer_ID = Payer.Payer_ID AND Employee.Employee_ID = Physician.Employee_ID ORDER BY Assessment_Tracker_ID, Visit_Date, Claim_ID SELECT * FROM #Request.prefix_db_agency#.ScheduleItems where Assessment_ID = #Assessment_ID# order by Visit_date ASC limit 1 ~" & chr(13)& chr(10) & "GS*HC*#GetData.Submitter_ID#*00*#DateFormat(Now(), "YYYYMMDD")#*#DateFormat(Now(), "HHMM")#*#ISA13#*X*005010X223A2~" & chr(13)& chr(10) > ~" & chr(13)& chr(10) & "GS*HC*#GetData.Submitter_ID#*#GetData.Receiver_ID#*#DateFormat(Now(), "YYYYMMDD")#*#DateFormat(Now(), "HHMM")#*#ISA13#*X*005010X223A2~" & chr(13)& chr(10) > "NM1*41*2*#UCase(GetData.Agency_Name)#*****46*#GetData.Agent_FEIN#~" & chr(13)& chr(10) & "PER*IC*#UCase(GetData.Agency_Contact)#*TE*#Left(GetData.Agency_Contact_Phone,3)##Mid(GetData.Agency_Contact_Phone,5,3)##Mid(GetData.Agency_Contact_Phone,9,4)#~" & chr(13)& chr(10) & "NM1*40*2*#UCase(GetData.PayName)#*****46~"& chr(13)& chr(10) & "HL*1**20*1~" & chr(13)& chr(10) & "PRV*BI*PXC*251E00000X~" & chr(13)& chr(10) & "NM1*85*2*#UCase(GetData.Agency_Name)#*****XX*#GetData.ANPI#~" & chr(13)& chr(10) & "N3*#UCase(GetData.Agency_Address1)#~" & chr(13)& chr(10) & "N4*#UCase(GetData.Agency_City)#*#UCase(GetData.Agency_State)#*#GetData.Agency_Zip#~" & chr(13)& chr(10)> *****MA~" & chr(13)& chr(10) & "NM1*IL*1*#UCase(GetData.Last_Name_M0040)#*#UCase(GetData.First_Name_M0040)#****MI*#GetData.HI_Claim_Number#~"& chr(13)& chr(10) & "N3*#UCase(GetData.Street_Address)#~"& chr(13)& chr(10) & "N4*#UCase(GetData.City)#*#GetData.State_M0050#*#GetData.Zip_M0060#~" & chr(13)& chr(10) & "DMG*D8*#DateFormat(GetData.DOB_M0066,"yyyymmdd")#*#GetData.Gender_M0069#~"& chr(13)& chr(10) & "NM1*PR*2*#UCase(GetData.PayName)#*****PI*#GetData.Payer_Number#~"& chr(13)& chr(10) & "N3*#UCase(GetData.PayAdd1)#~"& chr(13)& chr(10) & "N4*#UCase(GetData.PayCity)#*#GetData.PayState#*#GetData.PayZip#~"& chr(13)& chr(10) & "CLM*#CONTROL#*#invoiceTotal#***32>A>9**A*Y*Y*********~"& chr(13)& chr(10) & "DTP*434*RD8*#DateFormat(GetData.Certification_start,"yyyymmdd")#-#DateFormat(GetData.Certification_end,"yyyymmdd")#~"& chr(13)& chr(10) & "DTP*435*DT*#DateFormat(GetData.Start_of_Care_M0030,"yyyymmdd")#0001~"& chr(13)& chr(10) & "CL1*9*#GetData.Source_of_Admission#*#GetData.Admission_Status#~"& chr(13)& chr(10) & "REF*G1*#GetData.Claim_Key#~"& chr(13)& chr(10) & "HI*ABK>#Replace(GetData.HHRG_175_Score, ".", "", "ALL")#~"& chr(13)& chr(10) > #Replace(GetData.HHRG_230_Score, ".", "", "ALL")#*" > #Replace(GetData.HHRG_250_Score, ".", "", "ALL")#*" > #Replace(GetData.HHRG_390_Score, ".", "", "ALL")#*" > #Replace(GetData.HHRG_420_Score, ".", "", "ALL")#*" > #Replace(GetData.HHRG_440_Score, ".", "", "ALL")#~" & chr(13)& chr(10) > 85>>>#GetData.MSA_Code#~"& chr(13)& chr(10) & "NM1*71*1*#UCase(GetData.LastName)#*#UCase(GetData.FirstName)#****XX*#GetData.PNPI#~"& chr(13)& chr(10)> #Trim(GetData.HIPPS)#*0*UN*#Trim(GetData.Units)#~"& chr(13)& chr(10) & "DTP*472*D8*#DateFormat(FirstVisit,"yyyymmdd")#~" & chr(13)& chr(10) & "LX*2~"& chr(13)& chr(10) & "SV2*0551*HC>#Trim(QCode)#*.01*UN*1~"& chr(13)& chr(10) & "DTP*472*D8*#DateFormat(FirstVisit,"yyyymmdd")#~" & chr(13)& chr(10)> #Trim(GetData.HCPCS)#*#Trim(NumberFormat(GetData.Total_EClaim,'_____.__'))#*UN*#NumberFormat(GetData.Total_Units)#~"& chr(13)& chr(10) & "DTP*472*D8*#DateFormat(FirstVisit,"yyyymmdd")#~" & chr(13)& chr(10)> #Trim(GetData.HCPCS)#*#Trim(NumberFormat(GetData.Total_EClaim,'_____.__'))#*UN*#NumberFormat(GetData.Total_Units)#~"& chr(13)& chr(10) & "DTP*472*D8*#DateFormat(GetData.Visit_Date,"yyyymmdd")#~"& chr(13)& chr(10)> Select s.ScheduleItems_ID, s.Invoice_Id,s.Patient_ID, s.Assessment_Tracker_ID, s.Payer_ID, s.Agency_ID, a.Homebound_Status, a.Assessment_Reason_M0100, (Select Amount from #Request.prefix_db_agency#.Payments where Invoice_ID = s.Invoice_ID limit 1) as Paid, (Select PaymentDate from #Request.prefix_db_agency#.Payments where Invoice_ID = s.Invoice_ID limit 1) as PaymentDate, (select PayerBalance from #Request.prefix_db_agency#.Invoices where Invoice_ID = s.Invoice_ID limit 1) AS PayerBalance from #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a where s.ScheduleItems_ID in (#FORM.ScheduleItems_ID#) and s.Assessment_Tracker_ID = a.Assessment_Tracker_ID select distinct(scheduleitems_id) from GetData INSERT IGNORE INTO #Request.prefix_db_agency#.Invoices ( PatientPayer_ID ,Agency_ID ,Patient_ID ,Assessment_Tracker_ID ,Output ,DateSent ,MethodSent ,InvoiceTotal ,PayerBalance ,Internal_control_number ,WhereTreated ,Certification_End ,No_Days ,Record_Created_By ,Record_Created_Date ,Record_Modified_By ,Record_Modified_Date ) VALUES ( '#Payer_ID#' ,'#Agency_ID#' ,'#Patient_ID#' ,'#Assessment_Tracker_ID#' ,'Final' ,NOW() ,'Electronic' ,#NumberFormat(Homebound_Status,'_____.__')*.40##NumberFormat(Homebound_Status,'_____.__')*.50# ,#NumberFormat(Homebound_Status,'_____.__')*.40##NumberFormat(Homebound_Status,'_____.__')*.50# ,'#CONTROL#' ,'' ,#Certificat_End# ,#total_Days# ,#Session.EmployeeId# ,#now()# ,'#Session.EmployeeId#' ,#now()# ) SELECT LAST_INSERT_ID() as LastInsertID INSERT IGNORE INTO #Request.prefix_db_agency#.Payments ( Invoice_ID ,PatientPayer_ID ,PaymentDate ,Amount ,PaymentMethod ,WriteOffAmount ,WriteOffType ,Internal_control_number ,Record_Created_By ,Record_Created_Date ,Record_Modified_By ,Record_Modified_Date ) VALUES ( '#Last_Invoice_ID.LastInsertID#', '#Payer_ID#', #CreateODBCDateTime(Now())#, #CreateODBCDateTime(Now())#, 0.00, 'Electronic', '0.00', '0.00' ,'#CONTROL#' ,#Session.EmployeeId# ,#now()# ,'#Session.EmployeeId#' ,#now()# ) INSERT IGNORE INTO #Request.prefix_db_agency#.claim_bill( Type_of_Bill , Assignment_Accept ,Submitter_ID , Receiver_ID , Agency_Name ,Agent_FEIN , Agency_Contact ,Agency_Contact_Phone , PayName , ANPI ,Agency_Address1 ,Agency_City , Agency_State ,Agency_Zip , Last_Name_M0040 ,First_Name_M0040 , HI_Claim_Number ,Street_Address ,City , State_M0050 ,Zip_M0060 ,DOB_M0066 ,Gender_M0069 ,Payer_Number ,PayAdd1 ,PayCity ,PayState ,PayZip ,Certification_start ,Certification_end , Start_of_Care_M0030 ,Source_of_Admission ,Admission_Status ,Claim_Key ,HHRG_175_Score ,HHRG_230_Score ,HHRG_250_Score ,HHRG_390_Score , HHRG_420_Score ,HHRG_440_Score ,MSA_Code ,Referring_LastName ,Referring_FirstName ,Referring_PNPI ,Certifying_LastName ,Certifying_FirstName , Certifying_PNPI,Invoice_ID,Telephone,Assessment_Reason_M0100,Agency_Assigned_ID) VALUES ('32>A>9','','#Submitter_ID#','#Receiver_ID#','#Agency_Name#','#Agent_FEIN#','#Agency_Contact#','#Agency_Contact_Phone#','#PayName#', '#ANPI#','#Agency_Address1#','#Agency_City#','#Agency_State#' ,'#Agency_Zip#','#Last_Name_M0040#','#First_Name_M0040#','#HI_Claim_Number#','#Street_Address#','#City#', '#State_M0050#','#Zip_M0060#','#DATEFORMAT(DOB_M0066,'yyyy-mm-dd')#'null,'#Gender_M0069#','#Payer_Number#','#PayAdd1#','#PayCity#','#PayState#','#PayZip#', '#DATEFORMAT(Certification_Start,'yyyy-mm-dd')#'null, '#DATEFORMAT(Certification_End,'yyyy-mm-dd')#'null, #Start_of_Care_M0030#,'#Source_of_Admission#','#Admission_Status#','#Claim_Key#','#HHRG_175_Score#','#HHRG_230_Score#','#HHRG_250_Score#','#HHRG_390_Score#', '#HHRG_420_Score#','#HHRG_440_Score#','#MSA_Code#','#LastName#','#FirstName#' ,'#PNPI#' ,'','','',#Last_Invoice_ID.LastInsertID#,'','#Assessment_Reason_M0100#','#Agency_Assigned_ID#') Update IGNORE #Request.prefix_db_agency#.Invoices SET Updated_by = 'Automatic', claim_status = 'Waiting'claim_status = 'Rejected' ,Record_Modified_By = '#Session.EmployeeId#' ,Record_Modified_Date = #now()# WHERE Invoice_ID = #Last_Invoice_ID.LastInsertID# UPDATE IGNORE #Request.prefix_db_agency#.ScheduleItems SET EClaimed = 1 ,EClaimed_Final = 1 ,Invoice_ID = #Last_Invoice_ID.LastInsertID# ,Export_Date = #CreateODBCDateTime(Now())# ,Output_Type = 'Final' WHERE ScheduleItems_ID IN (#thelist#) AND Assessment_Tracker_ID = #Assessment_Tracker_ID# Select * from #Request.prefix_db_agency#.Invoices where Invoice_ID in ('#get_invoice_ID#')

Agency: #Session.AgencyName#

5-Digit ID: #Session.AgencyLogin# 3-Digit ID: #Session.AgencyID#

Name: #session.Display#

Cell: #Session.Phone#  Agency Phone: #Session.Agency_Phone#
Final Claims Export

!!Files Submitted by TurboClaim

Final Claims Ready for Export

!!Your Final Claim visits have been prepared for export. Right click on the hyperlink
below and click Save Target As...

      Save as final_claim.dat in c://winpcace/impub92

This claims just moved into Rejected Claims


Agency: #Session.AgencyName#

5-Digit ID: #Session.AgencyLogin# 3-Digit ID: #Session.AgencyID#

Name: #session.Display#

Cell: #Session.Phone#  Agency Phone: #Session.Agency_Phone#
Bill Medicare

Your Claims Moved to Rejected Now.

So Please check that Claim in REJECTED

select distinct(scheduleitems_id) from GetData UPDATE #Request.prefix_db_agency#.ScheduleItems SET EClaimed = 1, EClaimed_Final = 1, Nonbillable = 0, Export_Date = #CreateODBCDateTime(Now())# WHERE ScheduleItems_ID IN (#thelist#) DELETE FROM #Request.prefix_db_lookup#.Claim DELETE FROM #Request.prefix_db_lookup#.ClaimII

Final Claim Visits Ready for Export

No Final Claim visits ready