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, ScheduleItems.Agency_ID, ScheduleItems.Patient_ID, ScheduleItems.Auth_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, Auth_ID, Ticket ) VALUES ( '#session.AgencyId#', #ScheduleItems_ID#, '#Rev_Code#', '#HCPCS#', #Total_Units#, '#Visit_Date#', #Total_EClaim#, #Assessment_Tracker_ID#, #Payer_ID#, #Auth_ID#, 1 ) UPDATE #Request.prefix_db_lookup#.Supply_Sched SET Ticket = 1, Visit_Date =#GetVisits.Visit_Date# WHERE ScheduleItems_ID =#GetVisits.ScheduleItems_ID# SELECT * FROM #Request.prefix_db_agency#.Payer WHERE Payer_ID = #GetVisits.Payer_ID# Insert Into #Request.prefix_db_lookup#.Claim ( Agency_Id, ScheduleItems_ID, Rev_Code, HCPCS, Total_Units, Visit_Date, Assessment_Tracker_ID, Payer_ID, Total_EClaim, Ticket ) SELECT '#session.AgencyId#', ScheduleItems_ID, Revenue_Code, HCPCS, Total_Units, Visit_Date, Assessment_Tracker_ID, Payer_ID, Total_Price, 1 From #Request.prefix_db_lookup#.Supply_Sched Where Ticket = 1 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 Claim.ScheduleItems_ID, Claim.Claim_ID, Claim.Rev_Code, Claim.HCPCS, Claim.Total_Units, Claim.Total_EClaim, Claim.Visit_Date, 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, Assessment_New.Assessment_Tracker_ID, patient.Medicaid_Number_M0065, authorizations.Authorization_Number AS Claim_Key, authorizations.Authorization_Number AS 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, patient.Patient_ID, patient.Last_Name_M0040, patient.Agency_Assigned_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.Admission_Status, Agency.Agency_Name, Agency.Agency_Medicare_ID, Agency.Agency_Address1, Agency.Agency_City, patient.Medicare_Number_M0063, patient.HI_Claim_Number, 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, Physician.Natl_Provider_ID AS NPI, ScheduleItems.Output_Type, Employee.Last_Name as LastName, Employee.First_Name as FirstName, Payer.Payer_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.Submitter_ID,Payer.Receiver_ID, Payer.TurboClaim, Payer.PayerSource AS PAYSRC, Payer.PayerType, authorizations.Authorization_Number as AUTHNO FROM #Request.prefix_db_lookup#.Claim, #Request.prefix_db_agency#.Assessment_New, #Request.prefix_db_agency#.patient, #Request.prefix_db_lookup#.Agency, #Request.prefix_db_lookup#.Physician, #Request.prefix_db_lookup#.Employee, #Request.prefix_db_agency#.authorizations, #Request.prefix_db_agency#.ScheduleItems, #Request.prefix_db_agency#.Payer WHERE Claim.Agency_Id = '#session.AgencyId#' AND Claim.Ticket = 1 AND Claim.Assessment_Tracker_ID = Assessment_New.Assessment_Tracker_ID and Claim.ScheduleItems_ID = ScheduleItems.ScheduleItems_ID and Claim.Auth_ID = authorizations.Auth_ID and ScheduleItems.Auth_ID = authorizations.Auth_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, Claim_ID SELECT patient.patient_id, SUM(Claim.Total_EClaim) AS invAmt, COUNT(Claim_ID) as NOITEMS 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.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 patient.patient_id ArrayAppend(patients, #PrefetchData.patient_id#); ArrayAppend(invAmts, #PrefetchData.invAmt#); ArrayAppend(agencyIds, #GetData.Agency_ID#); ArrayAppend(assessmentTrackerIds, #GetData.Assessment_Tracker_ID#); ArrayAppend(patientIds, #GetData.Patient_ID#); ArrayAppend(invoiceAmounts, #GetData.Total_EClaim#); SELECT * FROM #Request.prefix_db_agency#.ScheduleItems s JOIN #Request.prefix_db_agency#.Revenue_Code r WHERE s.Rev_CodeID = r.Rev_CodeID AND s.ScheduleItems_ID = #GetData.ScheduleItems_ID# #Trim(GetData.HCPCS)#*#NumberFormat(GetData.Total_EClaim,'_____.__')#*UN*#TotalUnits#*12**1**N~"& chr(13)& chr(10) & "DTP*472*D8*#DateFormat(GetData.Visit_Date,"yyyymmdd")#~"& chr(13)& chr(10) & "REF*6R*00#ctr_329#H0000000010#DateFormat(Now(),"yyyymmdd")#~"& chr(13)& chr(10)> ~" & chr(13)& chr(10) & "GS*HC*#GetData.Submitter_ID#*#GetData.Payer_Number#*#DateFormat(Now(), "YYYYMMDD")#*#DateFormat(Now(), "HHMM")#*#YB#001*X*005010X222A1~" & chr(13)& chr(10) & "ST*837*#ST02#*005010X222A1~" & 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.Submitter_ID#~" & 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*#GetData.Payer_Number#~"& chr(13)& chr(10) & "HL*1**20*1~" & chr(13)& chr(10) & "PRV*BI*PXC*251E00000X~" & chr(13)& chr(10) & "NM1*85*1*#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)#*#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)#*FX*#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*#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#*#PayZip#~"& chr(13)& chr(10) & "CLM*#CONTROL#*#invoiceTotal#***#TOB#*Y**Y*Y~"& chr(13)& chr(10) & "REF*G1*#authorization_number#~"& 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)#*#Trim(GetData.Admission_Status)#~"& chr(13)& chr(10) &---> #GetData.HHRG_175_Score#~*"> #Replace(GetData.HHRG_175_Score, ".", "", "ALL")#~*"& chr(13)& chr(10) > #Replace(GetData.HHRG_230_Score, ".", "", "ALL")#*" > #GetData.HHRG_230_Score#*" > #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) > #Replace(GetData.HHRG_175_Score, ".", "", "ALL")#*ABF>#Replace(GetData.HHRG_230_Score, ".", "", "ALL")#*ABF>#Replace(GetData.HHRG_250_Score, ".", "", "ALL")#*ABF>#Replace(GetData.HHRG_390_Score, ".", "", "ALL")#*ABF>#Replace(GetData.HHRG_420_Score, ".", "", "ALL")#*ABF>#Replace(GetData.HHRG_440_Score, ".", "", "ALL")#~"& chr(13)& chr(10) &---> "NM1*82*1*#UCase(GetData.Agency_Contact)#****XX*#GetData.ANPI#~"& chr(13)& chr(10) & "NM1*77*1*#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)#*#Agency_Zip#~" & chr(13)& chr(10) & "HI*BH>#OC#>D8>~"& chr(13)& chr(10) & "HI*BE>61>>>#GetData.MSA_Code#~"& chr(13)& chr(10) & ---> "NM1*DN*1*#UCase(GetData.LastName)#*#UCase(GetData.FirstName)#****XX*#GetData.NPI#~"& chr(13)& chr(10) > 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 ,Record_Created_By ,Record_Created_Date ,Record_Modified_By ,Record_Modified_Date ) VALUES ( #Payer_ID# ,'#Agency_ID#' ,'#Patient_ID#' ,'#Assessment_Tracker_ID#' ,'1500' ,NOW() ,"Electronic" ,#invoiceTotal# ,0 ,#invoiceTotal# ,'#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 ('#TOB#','','#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#
1500 Visits exported by TurboClaim

#GetData.PayName# Files Submitted by TurboClaim!!!

1500 Visits exported by TurboClaim

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

      Save as 1500_claim.dat

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 1500

Your 1500 Claims Moved to Rejected Now.So Please check that Claim in REJECTED

You have created #Get_claim.Recordcount# Invoices. If you need to Edit/update Claim please click that Invoices


Fix/Edit Claims
UPDATE #Request.prefix_db_lookup#.Supply_Sched SET Ticket = 9 WHERE Ticket = 1 AND ScheduleItems_ID IN (#thelist#) Info Message 1
 DELETE FROM #Request.prefix_db_lookup#.Claim WHERE Agency_Id = '#session.AgencyId#' AND ScheduleItems_ID IN (#thelist#) Info Message 2

No 1500 Claim visits