SELECT ScheduleItems.ScheduleItems_Id ,ScheduleItems.Total_Units ,ScheduleItems.Payer_ID ,ScheduleItems.Visit_Date ,Assessment_New.HIPPS_Code ,Assessment_New.Assessment_Tracker_Id ,Assessment_New.Assessment_Reason_M0100 ,Assessment_New.Certification_Start ,Assessment_New.Agency_Id ,Assessment_New.Patient_Id FROM #Request.prefix_db_agency#.ScheduleItems, #Request.prefix_db_agency#.Assessment_New WHERE ScheduleItems.ScheduleItems_ID IN (#Form.ScheduleItems_Id#) AND ScheduleItems.Assessment_Tracker_Id = Assessment_New.Assessment_Tracker_Id AND ScheduleItems.Output_Type = 'RAP' Insert Into #Request.prefix_db_lookup#.Claim (Agency_Id, ScheduleItems_ID, Visit_Date, Rev_Code, HCPCS, Total_Units, Assessment_Tracker_ID, Payer_ID, Ticket) values ('#session.AgencyId#', #ScheduleItems_ID#, #Visit_Date#, '0023', '#HIPPS_Code#', #Total_Units#, #Assessment_Tracker_ID#, #Payer_ID#, 1) SELECT ScheduleItems.ScheduleItems_ID, ScheduleItems.Total_Units, ScheduleItems.Payer_ID, ScheduleItems.Visit_Date, Assessment_New.HIPPS_Code, Assessment_New.Assessment_Tracker_ID, Assessment_New.Certification_Start FROM #Request.prefix_db_agency#.ScheduleItems, #Request.prefix_db_agency#.Assessment_New WHERE ScheduleItems.Assessment_Tracker_ID = #GetVisits.Assessment_Tracker_ID# AND ScheduleItems.Assessment_Tracker_ID = Assessment_New.Assessment_Tracker_ID AND ScheduleItems.Visit_Date >= Assessment_New.Certification_Start ORDER BY ScheduleItems.Visit_Date Limit 1 Insert Into #Request.prefix_db_lookup#.Claim (Agency_Id, ScheduleItems_ID, Visit_Date, Rev_Code, HCPCS, Total_Units, Assessment_Tracker_ID, Payer_ID, Ticket) values ('#session.AgencyId#', #GetVisits.ScheduleItems_ID#, #GetVisits.Certification_Start#, '0023', '#GetVisits.HIPPS_Code#', #GetVisits.Total_Units#, #GetVisits.Assessment_Tracker_ID#, #GetVisits.Payer_ID#, 1) Insert Into #Request.prefix_db_lookup#.Claim (Agency_Id, ScheduleItems_ID, Visit_Date, Rev_Code, HCPCS, Total_Units, Assessment_Tracker_ID, Payer_ID, Ticket) values ('#session.AgencyId#', #GetVisits.ScheduleItems_ID#, #GetFirst.Visit_Date#, '0023', '#GetFirst.HIPPS_Code#', #GetFirst.Total_Units#, #GetFirst.Assessment_Tracker_ID#, #GetFirst.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 and p.Certifying_Physician != 0 SELECT Claim.ScheduleItems_ID, Claim.Claim_ID, Claim.Total_Units, Claim.Visit_Date, Assessment_New.HIPPS_Code FROM #Request.prefix_db_lookup#.Claim, #Request.prefix_db_agency#.Assessment_New WHERE Claim.Agency_Id = '#session.AgencyId#' AND Claim.Ticket = 1 AND Claim.Assessment_Tracker_ID = Assessment_New.Assessment_Tracker_ID ORDER BY Patient_ID, Visit_Date Limit 1 SELECT Assessment_New.Assessment_ID, patient.patient_id, SUM(Claim.Total_EClaim) AS invAmt 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.Assessment_Tracker_ID = Assessment_New.Assessment_Tracker_ID AND Assessment_New.Patient_ID = patient.Patient_ID AND Assessment_New.HHAC_Agency_ID = Agency.Agency_Login AND patient.Primary_ReferringPhys_ID_M0072 = Physician.Physician_ID AND Claim.Payer_ID = Payer.Payer_ID AND Physician.Employee_ID = Employee.Employee_ID GROUP BY Assessment_New.Assessment_ID, patient.patient_id ORDER BY patient.patient_id ArrayAppend(patients, #PrefetchData.patient_id#); RAPCalc = CreateObject("component", "MC_RAP_Calc"); rap = RAPCalc.getRAP(PrefetchData.Assessment_Id); ArrayAppend(invAmts, #Replace(rap, ",", "", "All")#); SELECT Claim.ScheduleItems_ID, Claim.Claim_ID, Claim.Total_EClaim, Claim.Visit_Date, Claim.Rev_Code, Claim.HCPCS, Claim.Total_Units ,Assessment_New.Start_of_Care_M0030, Assessment_New.Certification_Start, Assessment_New.Assessment_Tracker_ID,Assessment_New.Assessment_Reason_M0100 ,Assessment_New.Certification_End, Assessment_New.Source_of_Admission, Assessment_New.HIPPS_Code ,Assessment_New.Claim_Key, Assessment_New.Information_Release, Assessment_New.Homebound_Status ,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 ,Assessment_New.Assessment_Completed_M0090 ,patient.Patient_ID, patient.Admission_Status, patient.HI_Claim_Number ,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.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.Wage_Index, Agency.Rural ,Physician.Natl_Provider_ID AS NPI ,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 FROM #Request.prefix_db_agency#.Assessment_New, #Request.prefix_db_agency#.ScheduleItems, #Request.prefix_db_agency#.patient, #Request.prefix_db_lookup#.Agency, #Request.prefix_db_lookup#.Physician, #Request.prefix_db_lookup#.Employee, #Request.prefix_db_agency#.Payer, #Request.prefix_db_lookup#.Claim WHERE Claim.Agency_Id = '#session.AgencyId#' AND Claim.Ticket = 1 AND Claim.Assessment_Tracker_Id = Assessment_New.Assessment_Tracker_Id AND Assessment_New.Patient_Id = patient.Patient_Id AND Assessment_New.HHAC_Agency_ID = Agency.Agency_Login AND ScheduleItems.Assessment_Tracker_Id = Assessment_New.Assessment_Tracker_Id AND ScheduleItems.Output_Type = 'RAP' AND patient.Primary_ReferringPhys_Id_M0072 = Physician.Physician_Id AND Claim.Payer_Id = Payer.Payer_Id AND Physician.Employee_Id = Employee.Employee_Id AND (Claim.Visit_Date >= Assessment_New.Certification_Start or Claim.Visit_Date is null) ORDER BY Patient_Id, Claim_Id ArrayAppend(agencyIds, #GetData.Agency_ID#); ArrayAppend(assessmentTrackerIds, #GetData.Assessment_Tracker_ID#); ArrayAppend(patientIds, #GetData.Patient_ID#); ArrayAppend(invoiceAmounts, #GetData.Total_EClaim#); #Trim(GetData.HCPCS)#*#Trim(NumberFormat(GetData.Total_EClaim,'_____.__'))#*UN*#NumberFormat(GetData.Total_Units)#~"& chr(13)& chr(10) & "DTP*472*D8*#DateFormat(GetData.FirstVisitDate,"yyyymmdd")#~"& 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) & "ST*837*#ST02#*005010X223A2~" & chr(13)& chr(10) & "BHT*0019*00*00000#YB#*#DateFormat(Now(), "YYYYMMDD")#*#DateFormat(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.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#~"> *****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)#*#UCase(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>2**A*Y*Y*********~"& chr(13)& chr(10) & "DTP*434*RD8*#DateFormat(GetData.Certification_start,"yyyymmdd")#-#DateFormat(GetData.Certification_Start,"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#*30~"& 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) > 61>>>#GetData.MSA_Code#~"& chr(13)& chr(10) & "HI*BE>85>>>#GetData.FIPS_Code#~"& chr(13)& chr(10) & "NM1*71*1*#UCase(GetData.LastName)#*#UCase(GetData.FirstName)#****XX*#GetData.NPI#~"& 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 INTO #Request.prefix_db_agency#.Invoices ( PatientPayer_ID ,Agency_ID ,Patient_ID ,Assessment_Tracker_ID ,Output ,DateSent ,MethodSent ,InvoiceTotal ,PayerBalance ,Internal_control_number ,WhereTreated ,Assessment_Completed_M0090 ,No_Days ,Record_Created_By ,Record_Created_Date ,Record_Modified_By ,Record_Modified_Date ) VALUES ( '#Payer_ID#' ,'#Agency_ID#' ,'#Patient_ID#' ,'#Assessment_Tracker_ID#' ,'RAP' ,NOW() ,'Electronic' ,#NumberFormat(Homebound_Status,'_____.__')*.60##NumberFormat(Homebound_Status,'_____.__')*.50# ,#NumberFormat(Homebound_Status,'_____.__')*.60##NumberFormat(Homebound_Status,'_____.__')*.50# ,'#CONTROL#' ,'' ,#Assessment_Completed# ,#total_Days# ,'#Session.EmployeeId#' ,#now()# ,'#Session.EmployeeId#' ,#now()# ) SELECT LAST_INSERT_ID() as LastInsertID INSERT 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>2','','#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#' ,'#NPI#' ,'','','',#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#.ScheduleItems SET EClaimed = 1 ,Invoice_ID = #Last_Invoice_ID.LastInsertID# ,Agency_ID = #Agency_ID# ,Patient_ID = #Patient_ID# ,Assessment_Tracker_ID = #Assessment_Tracker_ID# ,Export_Date = #CreateODBCDateTime(Now())# 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#
RAP Claims Export

!!Files Submitted by TurboClaim

RAPs Ready for Export to PC-ACE

Your RAPs have been prepared for export. Right click on the hyperlink
below and click Save Target As...

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

This claims just moved into Rejectec 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 RAP

Your RAP 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 ,Export_Date = #CreateODBCDateTime(Now())# WHERE ScheduleItems_ID IN (#thelist#) AND Output_Type = 'RAP' UPDATE #Request.prefix_db_lookup#.Supply_Sched SET Ticket = 9 WHERE Ticket = 1 AND ScheduleItems_ID IN (#thelist#) DELETE FROM #Request.prefix_db_lookup#.Claim WHERE Agency_Id = '#session.AgencyId#' AND ScheduleItems_ID IN (#thelist#)

RAPs Ready for Export to PC-ACE

No RAP Visits Selected