SELECT pSchedules.* , pSchedules.Schedule_ID, pSchedules.Total_Units, pSchedules.Visit_Date, pSchedules.Rev_ID, pAssessments.F300 AS HIPPS_Code,pAssessments.Assmt_ID,pAssessments.F26 AS Assessment_Reason_M0100,pAssessments.F177 Certification_Start,pAssessments.F178 Certification_End,pAssessments.Patient_ID FROM #Request.prefix_db_agency#.pSchedules, #Request.prefix_db_agency#.pAssessments WHERE pSchedules.Schedule_ID IN (#Form.Schedule_ID#) AND pAssessments.Assmt_ID = pSchedules.Assmt_ID AND pSchedules.Status = 0 AND pAssessments.Status = 0 SELECT * FROM #Request.prefix_db_agency#.pRevenue_Code WHERE Rev_CodeID = '#Rev_ID#' 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#, '#Visit_Date#', '#GetRevenue.Rev_Code#', '#GetRevenue.HCPCS#',#NumberFormat(Request.Total_Price,'_____.__')#, #totalUnits#, #Assmt_ID#, #PAYER_ID#, 1) UPDATE #Request.prefix_db_agency#.pSchedules SET Total_Eclaim = #NumberFormat(Request.Total_Price,'_____.__')# WHERE Schedule_ID = #Schedule_ID# 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 as RAP_Date, Claim.Rev_Code, Claim.HCPCS, Claim.Total_Units , pAssessments.F177 Certification_Start, pAssessments.Assmt_ID, pAssessments.F26 AS Assessment_Reason_M0100,Claim.Visit_Date, pAdmit.M0030_START_CARE_DT AS Start_of_Care_M0030,pAssessments.F178 AS Certification_End, pAdmit.Admission_Source AS Source_of_Admission, pAssessments.F300 AS HIPPS ,"" AS Claim_Key, "" AS Information_Release, "" AS Admission_Description ,"" AS Homebound_Status ,"" AS HHRG_175_Score, "" AS HHRG_230_Score,"" AS HHRG_250_Score ,"" AS HHRG_390_Score, "" AS HHRG_420_Score, "" AS HHRG_440_Score,"" AS HHRG_450_Score ,pAssessments.F176 AS Assessment_Completed_M0090,pAssessments.Assmt_ID ,pPatients.Patient_ID, pAssessments.F25 AS Admission_Status, pPtPayer.Sub_HIC AS HI_Claim_Number, pPtPayer.Policy_Group_Number AS GRNO, pPtPayer.Plan_Type, pAuths.Auth_No ,pPatients.Pt_Last AS Last_Name_M0040, pPatients.Patient_ID,pPatients.Pt_First AS First_Name_M0040, pPatients.Pt_Middle AS Middle_Initial_M0040, pPatients.Pt_Street AS Street_Address ,pPatients.Pt_Gender AS Gender_M0069, pPatients.Pt_City AS City, pPatients.Pt_State AS State_M0050, pPatients.Pt_Zip AS Zip_M0060, pPatients.Pt_DOB AS DOB_M0066,CBSA_Code AS MSA_Code, FIPS_Code, pPatients.Pt_Agy_ID AS Agency_Assigned_ID,"" AS Lives_With_M0340, pPatients.Pt_Race AS 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.Hospice_Medicare_NPI AS HNPI, Agency.Natl_Provider_ID AS ANPI ,Agency.Agency_ID, Agency.Wage_Index, Agency.Rural, "" AS NPI ,pPhysicians.Phys_Last as LastName, pPhysicians.Phys_First as FirstName, pPhysicians.Phys_NPI as PNPI ,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_Zip AS PayZip , 0 AS TurboClaim, (SELECT MIN(c.Visit_Date) FROM #Request.prefix_db_lookup#.Claim c WHERE c.Agency_Id = '#session.AgencyId#' AND c.Assmt_ID = pAssessments.Assmt_ID ) AS FirstVisitDate , "" AS FVD, (SELECT MIN(Total_Units) FROM #Request.prefix_db_lookup#.Claim WHERE Agency_Id = '#session.AgencyId#' AND Assmt_ID = pAssessments.Assmt_ID ) AS Units FROM #Request.prefix_db_agency#.pAssessments,#Request.prefix_db_agency#.pAdmit, #Request.prefix_db_agency#.pSchedules, #Request.prefix_db_agency#.pPatients, #Request.prefix_db_lookup#.Agency, #Request.prefix_db_lookup#.pEmployee, #Request.prefix_db_agency#.pPayer, #Request.prefix_db_agency#.pAuths, #Request.prefix_db_lookup#.Claim, #Request.prefix_db_agency#.pPtPayer, #Request.prefix_db_lookup#.pPhysicians WHERE Claim.Agency_Id = '#session.AgencyId#' AND Claim.Ticket = 1 -- AND Claim.Total_EClaim > '0.0000' AND Claim.Assmt_ID = pAssessments.Assmt_ID AND pAssessments.Patient_Id = pPatients.Patient_Id AND pPtPayer.Patient_Id = pPatients.Patient_Id AND pPatients.Loc_ID = Agency.Agency_ID AND pSchedules.Assmt_ID = pAssessments.Assmt_ID AND pSchedules.Assmt_ID = pAuths.Assmt_ID AND Claim.Payer_Id = pPayer.Pay_Id AND pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.Render_Phys_ID = pPhysicians.Phys_ID AND pSchedules.Emp_Id = pEmployee.Emp_Id AND (Claim.Visit_Date >= pAssessments.F177 or Claim.Visit_Date is null) AND pSchedules.status = 0 AND pAssessments.status = 0 AND pPatients.status < 2 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>#tobRight#**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 ,Record_Created_Date ,Record_Modified_By ,Record_Modified_Date ) VALUES ( '#Payer_ID#' ,'#Agency_ID#' ,'#Patient_ID#' ,'#Assmt_ID#' ,'Final' ,NOW() ,'Electronic' ,#NumberFormat(InvoiceTotal,'_____.__')# ,#NumberFormat(InvoiceTotal,'_____.__')# ,'#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 ('#form.TOB#','','#Submitter_ID#','#Receiver_ID#','#Agency_Name#','#Agent_FEIN#','#Agency_Contact#','#Agency_Contact_Phone#','#Pay_Name#', '#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#.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