SELECT * FROM #Request.prefix_db_agency#.claim_schedules JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Schedule_ID = claim_schedules.Schedule_ID AND pSchedules.Status = 0 JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID AND pAssessments.Status = 0 WHERE Claim_ID IN (#Form.Claim_ID#) AND claim_schedules.Status = 0 Insert Into #Request.prefix_db_lookup#.Claim (Agency_Id, Schedule_ID, Visit_Date, Rev_Code, HCPCS, Total_EClaim, Total_Units, Assmt_ID, Payer_ID, Ticket) VALUES ('#session.AgencyId#', #Schedule_ID#, '#bill_visitdate#', '#bill_rev_code#', '#bill_HCPCS#', #NumberFormat(bill_totalcharges,'_____.__')#, #bill_units#, #Assmt_ID#, #PAYER_ID#, 1) UPDATE IGNORE #Request.prefix_db_agency#.pSchedules SET Total_Eclaim = #NumberFormat(bill_totalcharges,'_____.__')# WHERE Schedule_ID = #Schedule_ID# SELECT Claim.Schedule_ID, Claim.Claim_ID, Claim.Total_Units, Claim.Visit_Date, pAssessments.F300 AS HIPPS_Code FROM #Request.prefix_db_lookup#.Claim, #Request.prefix_db_agency#.pAssessments WHERE Claim.Agency_Id = '#session.AgencyId#' AND Claim.Ticket = 1 AND Claim.Assmt_ID = pAssessments.Assmt_ID ORDER BY Patient_ID, Visit_Date Limit 1 SELECT s.Schedule_ID, s.Revenue_Code, s.HCPCS, s.Total_Units, c.Visit_Date, s.Assmt_ID, s.Payer_ID, s.Total_Price, s.Ticket FROM #Request.prefix_db_agency#.Supply_Sched s, #Request.prefix_db_lookup#.Claim c WHERE s.Schedule_ID = c.Schedule_ID and s.Deleted = 0 Insert IGNORE Into #Request.prefix_db_lookup#.Claim (Agency_Id,Schedule_ID, Rev_Code, HCPCS, Total_Units, Visit_Date, Total_EClaim, Assmt_ID, Payer_ID, Ticket) values (#session.AgencyId#,'#Schedule_ID#', '#Revenue_Code#', '#HCPCS#', #decimalformat(Total_Units)#, '#DateFormat(Visit_Date,"yyyy-mm-dd")#', #NumberFormat(Total_Price,'_____.__')#, #Assmt_ID#, #Payer_ID#, 1) SELECT Claim.Schedule_ID, Claim.Claim_ID, Claim.Total_EClaim,Claim.Visit_Date, Claim.Visit_Date as RAP_Date, Claim.Rev_Code, Claim.HCPCS, Claim.Total_Units,Claim.Agency_Id, pAssessments.Patient_Id, "" AS HHRG_450_SCORE, claim_bill.ANPI AS NPI, "Q5001" AS Admission_Description, pAssessments.F300 AS HIPPS_Code, pAssessments.F176 AS Assessment_Completed_M0090,pAssessments.Assmt_ID, "" AS MSA_Code, "" AS FIPS_Code, "" AS Lives_With_M0340, "" AS Race_M0140_NA, pPayer.Pay_ID AS Payer_ID, pPayer.Submitter_ID, pPayer.Receiver_ID, pPayer.Pay_Name, pPayer.Payer_No AS Payer_Number, pPayer.Pay_Street AS PayAdd1,"" AS PayAdd2, pPayer.Pay_City AS PayCity, pPayer.Pay_State AS PayState, pPayer.Pay_State AS PayZip , 0 AS TurboClaim, (SELECT MIN(Visit_Date) FROM #Request.prefix_db_lookup#.Claim WHERE Agency_Id = '#session.AgencyId#' AND Assmt_ID = pAssessments.Assmt_ID ) AS FirstVisitDate, claim_bill.* , claim_schedules.bill_auth AS Auth_No, claim_bill.Referring_LastName AS LastName, claim_bill.Referring_FirstName AS FirstName , Referring_PNPI AS PNPI , pAssessments.F299 AS HIPPS, bill_units AS Units, bill_rev_code AS Rev_Code, bill_HCPCS AS HCPCS, bill_units AS Total_Units ,bill_totalcharges AS Total_EClaim FROM #Request.prefix_db_agency#.claim_schedules JOIN #Request.prefix_db_agency#.claim_bill ON claim_schedules.Claim_ID = claim_bill.ID JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Schedule_ID = claim_schedules.Schedule_ID JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID JOIN #Request.prefix_db_lookup#.Claim ON Claim.Assmt_ID = pSchedules.Assmt_ID JOIN #Request.prefix_db_agency#.pPayer ON Claim.Payer_Id = pPayer.Pay_Id WHERE Claim.Agency_Id = '#session.AgencyId#' AND Claim.Ticket = 1 AND (Claim.Visit_Date >= Certification_Start or Claim.Visit_Date is null) ORDER BY pAssessments.Assmt_ID, Claim.Visit_Date, Claim.Claim_ID ~" & 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) > A>9**A*Y*Y*********~"& chr(13)& chr(10) & "DTP*434*RD8*#DateFormat(cert_start_date,"yyyymmdd")#-#DateFormat(cert_end_date,"yyyymmdd")#~"& chr(13)& chr(10) & "REF*G1*#GetData.Auth_No#*~"& 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#*30~"& chr(13)& chr(10) > SELECT pProblem.*,vetted_190924.ICD FROM #Request.prefix_db_agency#.pProblem JOIN #Request.prefix_db_lookup#.vetted_190924 ON vetted_190924.Diagnosis = pProblem.Description WHERE pProblem.Assmt_ID IN () AND pProblem.status = AND (M0_No like 'M1021%' OR M0_No like 'M1023%') ORDER BY dx_order,RIGHT(M0_No, 1) ASC #Replace(M0230Diagnosis.ICD, ".", "", "ALL")#~"& chr(13)& chr(10) > #Replace(M0230Diagnosis.ICD, ".", "", "ALL")#*" > #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.Schedule_ID, s.Invoice_Id,s.Patient_ID, s.Assmt_ID, #url.ID# AS Payer_ID, #session.agencyid# AS Agency_ID, "" AS Homebound_Status, a.F26 AS Assessment_Reason_M0100, "" as Paid, "" as PaymentDate, "" AS PayerBalance from #Request.prefix_db_agency#.pSchedules s, #Request.prefix_db_agency#.pAssessments a WHERE s.Schedule_ID in (#FORM.Schedule_ID#) and s.Assmt_ID = a.Assmt_ID select distinct(Schedule_ID) from GetData INSERT IGNORE INTO #Request.prefix_db_agency#.Invoices ( PatientPayer_ID ,Agency_ID ,Patient_ID ,Assmt_ID ,Output ,DateSent ,MethodSent ,InvoiceTotal ,PayerBalance ,Internal_control_number ,WhereTreated ,Certification_End ,No_Days ,Record_Created_By ) VALUES ( '#Payer_ID#' ,'#Agency_ID#' ,'#Patient_ID#' ,'#Assmt_ID#' ,'Final' ,NOW() ,'Electronic' ,#NumberFormat(InvoiceTotal,'_____.__')# ,#NumberFormat(InvoiceTotal,'_____.__')# ,'#CONTROL#' ,'' ,#Certificat_End# ,#total_Days# ,#Session.EmployeeId# ) 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 ,Payment_Lock ) VALUES ( '#Last_Invoice_ID.LastInsertID#', '#Payer_ID#', #CreateODBCDateTime(Now())#, #CreateODBCDateTime(Now())#, 0.00, 'Electronic', '0.00', '0.00' ,'#CONTROL#' ,1 ) Update IGNORE #Request.prefix_db_agency#.Invoices SET Updated_by = 'Automatic', claim_status = 'Waiting' WHERE Invoice_ID = #Last_Invoice_ID.LastInsertID# UPDATE IGNORE #Request.prefix_db_agency#.pSchedules SET Invoice_ID = #Last_Invoice_ID.LastInsertID# WHERE Schedule_ID IN (#thelist#) AND status = 0 AND Assmt_ID = #Assmt_ID# Select Invoice_ID from #Request.prefix_db_agency#.Invoices where Invoice_ID in ('#get_invoice_ID#')
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

select distinct(Schedule_ID) from GetData DELETE FROM #Request.prefix_db_lookup#.Claim

Final Claim Visits Ready for Export

No Final Claim visits ready