Delete from #Request.prefix_db_lookup#.cron_GetTotals SELECT pSchedules.Schedule_ID, pSchedules.Rev_ID, pSchedules.HCPCS_ID, pSchedules.Visit_Date, pSchedules.Total_Units, pSchedules.Total_EClaim, pSchedules.Pay_ID, pSchedules.Assmt_ID,#session.agencyid# AS Agency_ID, pSchedules.Patient_ID, pSchedules.Auth_ID FROM #Request.prefix_db_agency#.pSchedules WHERE Schedule_ID IN (#schid#) AND pSchedules.status = 0 SELECT * FROM #Request.prefix_db_agency#.pAdmit WHERE Status = 0 AND Patient_ID= '#GetVisits.Patient_ID#' ORDER BY M0030_START_CARE_DT DESC SELECT * FROM #Request.prefix_db_agency#.pRevenue_Code WHERE Rev_CodeID = '#Rev_ID#' AND Status = 0 SELECT * FROM #Request.prefix_db_agency#.pRevenue_Code WHERE Rev_CodeID = '#HCPCS_ID#' '#Rev_ID#' AND Status = 0 Insert Into #Request.prefix_db_lookup#.Claim (Agency_Id, Schedule_ID, Visit_Date, Rev_Code, HCPCS,Total_EClaim,Total_Contract, Total_Units, Assmt_ID, Payer_ID,Auth_ID, Ticket) VALUES ('#session.AgencyId#', #Schedule_ID#, '#Visit_Date#', '#GetRevenue.Rev_Code#', '#GetHCPCS.HCPCS#', #NumberFormat(Request.Total_Price,'_____.__')#,#NumberFormat(Request.Total_Contract,'_____.__')#, #Total_Units#, #Assmt_ID#, #PAYER_ID#,#Auth_ID#, 1) UPDATE #Request.prefix_db_agency#.pSchedules SET Total_Eclaim = #NumberFormat(Request.Total_Price,'_____.__')#, Total_Contract = #NumberFormat(Request.Total_Contract,'_____.__')# WHERE Schedule_ID = #Schedule_ID# AND Status = 0 Insert IGNORE INTO #Request.prefix_db_lookup#.cron_GetTotals ( DateField1,DateField2,NumField3 ) SELECT MAX(pSchedules.Visit_Date) AS First_Visit,MIN(pSchedules.Visit_Date) AS Last_Visit,Assmt_ID FROM #Request.prefix_db_agency#.pSchedules WHERE Schedule_ID IN (#schid#) AND status = 0 GROUP BY Assmt_ID Update #Request.prefix_db_lookup#.Claim cl, #Request.prefix_db_lookup#.cron_GetTotals c SET cl.Max_Visitdate = c.DateField1, cl.Min_Visitdate = c.DateField2 WHERE cl.Assmt_ID = c.NumField3 Delete from #Request.prefix_db_lookup#.cron_GetTotals SELECT * FROM #Request.prefix_db_agency#.pPayer WHERE Pay_ID = #PAYER_ID# SELECT s.Schedule_ID, s.Revenue_Code, s.HCPCS, s.Total_Units, s.Visit_Date, s.Assmt_ID, s.Payer_ID, s.Total_Price, c.Total_Contract, s.Ticket,s.Supply_ID 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, Total_Contract, 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,'_____.__')#,#NumberFormat(Total_Contract,'_____.__')#, #Assmt_ID#, #Payer_ID#, 1) 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 pAssessments.Patient_ID, Visit_Date Limit 1 SELECT Claim.Min_Visitdate,Claim.Max_Visitdate, Claim.Schedule_ID, Claim.Claim_ID, Claim.Total_EClaim,Claim.Total_Contract, Claim.Visit_Date as RAP_Date, Claim.Rev_Code, Claim.HCPCS, Claim.Total_Units, Claim.Rev_Code , pAssessments.F177 Certification_Start, pAssessments.Assmt_ID,pAssessments.F300 AS HIPPS_Code, 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, pPtPayer.Plan_Type, pAuths.Auth_No AS AUTHNO ,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,pPatients.CBSA_Code AS MSA_Code, "" AS 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 ,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.Output AS POUT, 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, pPayer.PayerSource AS PAYSRC,pPayer.Qcode_req,pPayer.Revenue_0023,pPayer.Value_Codes, (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_lookup#.Claim JOIN #Request.prefix_db_agency#.pAssessments ON Claim.Assmt_ID = pAssessments.Assmt_ID AND pAssessments.status = 0 JOIN #Request.prefix_db_agency#.pSchedules ON Claim.Schedule_ID = pSchedules.Schedule_ID AND pSchedules.status = 0 JOIN #Request.prefix_db_agency#.pPatients ON pSchedules.Patient_ID = pPatients.Patient_ID AND pPatients.Status IN (#Request.patient_status#) JOIN #Request.prefix_db_agency#.pAdmit ON pPatients.Patient_ID = pAdmit.Patient_ID pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.status = 0 JOIN #Request.prefix_db_lookup#.Agency ON Claim.Agency_Id = Agency.Agency_Id JOIN #Request.prefix_db_lookup#.pEmployee ON pSchedules.Emp_Id = pEmployee.Emp_Id JOIN #Request.prefix_db_agency#.pPayer ON Claim.Payer_Id = pPayer.Pay_Id JOIN #Request.prefix_db_agency#.pPtPayer ON pPtPayer.Patient_Id = pPatients.Patient_Id AND pPayer.Pay_Id = pPtPayer.Payer_ID JOIN #Request.prefix_db_agency#.pAuths ON Claim.Auth_ID = pAuths.Auth_ID AND pSchedules.status = 0 JOIN #Request.prefix_db_lookup#.pPhysicians ON pAdmit.Render_Phys_ID = pPhysicians.Phys_ID WHERE Claim.Agency_Id = '#session.AgencyId#' AND Claim.Ticket = 1 AND Claim.Total_EClaim > '0.0000' AND (Claim.Visit_Date >= pAssessments.F177 or Claim.Visit_Date is null OR pAssessments.F177 IS NULL) AND pSchedules.status = 0 AND pAssessments.status = 0 AND pPatients.Status IN (#Request.patient_status#) ORDER BY pAssessments.Assmt_ID, Claim.Visit_Date, Claim.Claim_ID A>' & RIGHT(Session.TOB,1) > ArrayAppend(agencyIds, #GetData.Agency_ID#); ArrayAppend(assessmentTrackerIds, #GetData.Assmt_ID#); ArrayAppend(patientIds, #GetData.Patient_ID#); ArrayAppend(invoiceAmounts, #GetData.Total_EClaim#); SELECT * FROM #Request.prefix_db_agency#.pSchedules s JOIN #Request.prefix_db_agency#.pRevenue_Code r WHERE s.Rev_ID = r.Rev_CodeID AND s.Schedule_ID = #GetData.Schedule_ID# #Trim(GetData.HCPCS)#*#Trim(NumberFormat(GetData.Total_EClaim,'____.__'))#*UN*#TotalUnits#~"& chr(13)& chr(10) & "DTP*472*D8*#DateFormat(GetData.Visit_Date,"yyyymmdd")#~"& chr(13)& chr(10) > ~" & chr(13)& chr(10) & "GS*HC*#GetData.Submitter_ID#*#GetData.Receiver_ID#*#DateFormat(Now(), "yyyymmdd")#*#TimeFormat(Now(), "HHMM")#*#GS06#*X*005010X223A2~" & chr(13)& chr(10) & "ST*837*#ST02#*005010X223A2~" & chr(13)& chr(10) & "BHT*0019*00*00000#YB#*#DateFormat(Now(), "yyyymmdd")#*#TimeFormat(Now(), "HHMM")#*CH~" & 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.Pay_Name)#*****46*#GetData.Payer_Number#~"& chr(13)& chr(10) & "HL*1**20*1~" & chr(13)& chr(10) & "PRV*BI*PXC*#TAX#~" & chr(13)& chr(10) & "NM1*85*2*#UCase(GetData.Agency_Name)#*****XX*#NPI#~" & chr(13)& chr(10) & "N3*#UCase(GetData.Agency_Address1)#~" & chr(13)& chr(10) & "N4*#UCase(GetData.Agency_City)#*#UCase(GetData.Agency_State)#*#Agency_Zip#~" & chr(13)& chr(10)> "REF*EI*#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) & "HL*2*1*22*0~" & chr(13)& chr(10) & "SBR*P*18**UNKNOWN*****#Trim(GetData.PAYSRC)#~" & chr(13)& chr(10) & "NM1*IL*1*#UCase(GetData.Last_Name_M0040)#*#UCase(GetData.First_Name_M0040)#****MI*#claim#~"& chr(13)& chr(10) & "N3*#UCase(GetData.Street_Address)#~"& chr(13)& chr(10) & "N4*#UCase(GetData.City)#*#GetData.State_M0050#*#Left(GetData.Zip_M0060,5)#~" & chr(13)& chr(10) & "DMG*D8*#DateFormat(GetData.DOB_M0066,"yyyymmdd")#*#GetData.Gender_M0069#~"& chr(13)& chr(10) & "NM1*PR*2*#UCase(GetData.Pay_Name)#*****PI*#GetData.Payer_Number#~"& chr(13)& chr(10) & "N3*#UCase(GetData.PayAdd1)#~"& chr(13)& chr(10) & "N4*#UCase(GetData.PayCity)#*#GetData.PayState#*#PayZip#~"& chr(13)& chr(10) & "CLM*#CONTROL#*#invoiceTotal#***#TOB#**A*Y*Y*********~"& chr(13)& chr(10) & "DTP*434*RD8*#DateFormat(GetData.Min_Visitdate,"yyyymmdd")#-#DateFormat(GetData.Max_Visitdate,"yyyymmdd")#~"& chr(13)& chr(10) & "DTP*435*DT*#DateFormat(GetData.Start_of_Care_M0030,"yyyymmdd")#0001~"& chr(13)& chr(10) & "CL1*9*#Trim(GetData.Source_of_Admission)#*30~"& chr(13)& chr(10) & "REF*G1*#Trim(authorization_number)#~"& 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 () pProblem.Assmt_ID IN ( SELECT Assmt_ID FROM #Request.prefix_db_agency#.pAssessments WHERE pAssessments.Patient_ID = #GetData.Patient_ID# AND pAssessments.Status = 0 AND pAssessments.Admit_ID = #getlatestadmit.Admit_ID#) 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")#*" > "HI*BE>61>>>#GetData.MSA_Code#~"& chr(13)& chr(10) /> "NM1*71*1*#UCase(GetData.LastName)#*#UCase(GetData.FirstName)#****XX*#GetData.PNPI#~"& chr(13)& chr(10) /> #Trim(HIPPS2)#*0*UN*1~"& chr(13)& chr(10) & "DTP*472*D8*#DateFormat(Date,"yyyymmdd")#~" & chr(13)& chr(10) > select distinct(Schedule_ID) from GetData INSERT INTO #Request.prefix_db_agency#.Invoices ( PatientPayer_ID ,Agency_ID ,Patient_ID ,Assmt_ID ,Output ,DateSent ,MethodSent ,InvoiceTotal ,PayerBalance ,ContractTotal ,Internal_control_number ,WhereTreated ,Record_Created_By ,Record_Created_Date ,Record_Modified_By ,Record_Modified_Date ) VALUES ( #Payer_ID# ,'#Agency_ID#' ,'#Patient_ID#' ,'#Assmt_ID#' ,'#POUT#' ,NOW() ,"Electronic" ,#invoiceTotal# ,0 ,#invoiceTotal# ,#NumberFormat(Request.Invoice_ContractTotal,'_____.__')# ,'#CONTROL#' ,"" ,'#Session.EmployeeId#' ,#now()# ,'#Session.EmployeeId#' ,#now()# ) SELECT LAST_INSERT_ID() as LastInsertID 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 ('#Session.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 #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 #Request.prefix_db_agency#.pSchedules SET EClaimed = '1' ,Invoice_ID = '#Last_Invoice_ID.LastInsertID#' ,Export_Date = #CreateODBCDateTime(Now())# WHERE Schedule_ID IN (#thelist#) AND Assmt_ID = '#Assmt_ID#' ANd Status = 0 SELECT * FROM #Request.prefix_db_agency#.Invoices where Invoice_ID in ('#get_invoice_ID#')

UB04 Visits

Automatically your UB04 visits have been sent to Inovalon

UB04 Visits

Your UB04 visits have been prepared for export. Click on the hyperlink
below and click Save Target As...

Save as UB04_claim.dat

select distinct(Schedule_ID) from GetData UPDATE #Request.prefix_db_agency#.Supply_Sched SET Ticket = 9 WHERE Ticket = 1 AND Schedule_ID IN (#thelist#) Update Flags Error
DELETE FROM #Request.prefix_db_lookup#.Claim WHERE Agency_Id = '#session.AgencyId#' DELETE Claims Error
UPDATE #Request.prefix_db_agency#.pSchedules SET EClaimed = 1, Nonbillable = 0, Export_Date = #CreateODBCDateTime(Now())# WHERE Schedule_ID IN (#thelist#) AND Status = 0

No UB04 Claim visits