The current directory is #dirlist.recordCount# date_of_paid - #date_of_paid#
eft_number - #eft_number#

Payment Claims

End Vefiry---> SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA like '%#Request.prefix_db_agency#%' group by TABLE_SCHEMA select Last_Name_M0040 ,First_Name_M0040,Assessment_Tracker_ID from #Request.prefix_db_agency#.patient join #Request.prefix_db_agency#.Invoices on patient.Patient_ID = Invoices.patient_ID where Internal_Control_Number = '#Internal_control_number#' INSERT IGNORE into #Request.prefix_db_agency#.Invoices (PatientPayer_ID,Agency_ID,Patient_ID,Assessment_Tracker_ID ,Output ,DateSent,MethodSent,InvoiceTotal,PayerBalance , WhereTreated,Deleted ,Comment,TeamBill,QBExport,claim_status, errors,Error_description,claim_received_date, claim_rejected_date, TOB, Medicare_number,Date_of_service, Date_of_Remits, remit_numebr, DTM_232_date,DTM_233_date, Updated_by,Type_Of_bill,Assessment_Completed_M0090,Certification_End, No_Days,User_Defined_Status,Record_Created_By) SELECT PatientPayer_ID,Agency_ID,Patient_ID,Assessment_Tracker_ID ,Output ,#date_of_billed#DateSent,MethodSent,'0.00','0.00' ,WhereTreated, Deleted ,Comment,TeamBill,QBExport,'DUPLICATE', errors,Error_description,claim_received_date, claim_rejected_date, TOB, Medicare_number,'#date_of_service#', '#date_of_paid#', '#eft_number#', #DTM_232_date#,#DTM_233_date#, 'Automatic','320',Assessment_Completed_M0090,Certification_End, No_Days,'Needs fix', Record_Created_By FROM #Request.prefix_db_agency#.Invoices WHERE Internal_control_number = '#Internal_Control_number#' AND Internal_control_number !=0 WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Invoice_Lock,0) <> 1) ---> UPDATE IGNORE #Request.prefix_db_agency#.Invoices SET User_defined_Status ='Needs fix', Date_of_service = '#date_of_service#', Date_of_Remits = '#date_of_paid#', remit_numebr = '#eft_number#', Updated_by = 'Automatic', DTM_232_date = #DTM_232_date#, DTM_233_date = #DTM_233_date#, Type_Of_bill = '327' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Invoice_Lock,0) <> 1) UPDATE IGNORE #Request.prefix_db_agency#.Invoices SET claim_status ='RAP Reversal', claim_status ='cancelled', InvoiceTotal = '#Amount#', Date_of_service = '#date_of_service#', Date_of_Remits = '#date_of_paid#', remit_numebr = '#eft_number#', Updated_by = 'Automatic', DTM_232_date = #DTM_232_date#, DTM_233_date = #DTM_233_date#, Type_Of_bill = '328', User_defined_Status ='final paid/RAP cancelled' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Invoice_Lock,0) <> 1) UPDATE IGNORE #Request.prefix_db_agency#.Invoices SET claim_status ='RAP Reversal', claim_status ='cancelled', InvoiceTotal = '#Amount#', Date_of_service = '#date_of_service#', Date_of_Remits = '#date_of_paid#', remit_numebr = '#eft_number#', Updated_by = 'Automatic', DTM_232_date = #DTM_232_date#, DTM_233_date = #DTM_233_date#, Type_Of_bill = '328', User_defined_Status ='RAP cancelled/no final payment' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Invoice_Lock,0) <> 1) select Case when claim_status = 'paid' then Invoicetotal ELSE 0 END AS paid_total, Case when claim_status = 'cancelled' then Invoicetotal ELSE 0 END AS cancelled_total from #Request.prefix_db_agency#.Invoices where Assessment_Tracker_ID = #select_patient.Assessment_Tracker_ID# AND Output = 'RAP' and deleted <> 1 UPDATE IGNORE #Request.prefix_db_agency#.Payments SET Amount = '#paid#', PaymentDate = '#paydate#', WriteoffAmount = '#Amount#', PaymentMethod = 'EFT', WriteOffType = 'payer adjusted', Created_By = '1' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Payment_Lock,0) <> 1) UPDATE IGNORE #Request.prefix_db_agency#.Invoices SET claim_status ='Paid', InvoiceTotal = '#Amount#', Date_of_service = '#date_of_service#', Date_of_Remits = '#date_of_paid#', remit_numebr = '#eft_number#', Updated_by = 'Automatic', DTM_232_date = #DTM_232_date#, DTM_233_date = #DTM_233_date#, Type_Of_bill = '329', User_defined_Status ='final paid/RAP cancelled' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Invoice_Lock,0) <> 1) UPDATE IGNORE #Request.prefix_db_agency#.Payments SET Amount = '#paid#', PaymentDate = '#paydate#', WriteoffAmount = '#Amount#', PaymentMethod = 'EFT', Created_By = '1' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Payment_Lock,0) <> 1) UPDATE IGNORE #Request.prefix_db_agency#.Invoices SET claim_status ='Paid', Date_of_service = '#date_of_service#', Date_of_Remits = '#date_of_paid#', remit_numebr = '#eft_number#', Updated_by = 'Automatic', DTM_232_date = #DTM_232_date#, DTM_233_date = #DTM_233_date#, Type_Of_bill = '322', InvoiceTotal = '#totalAmount#' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Invoice_Lock,0) <> 1) UPDATE IGNORE #Request.prefix_db_agency#.Payments SET Amount = '#paid#', PaymentDate = '#paydate#', WriteoffAmount = '#Amount#', WriteOffType = 'payer adjusted', PaymentMethod = 'EFT', Created_By = '1' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Payment_Lock,0) <> 1) INSERT IGNORE into #Request.prefix_db_agency#.Invoices (PatientPayer_ID,Agency_ID,Patient_ID,Assessment_Tracker_ID ,Output ,DateSent,MethodSent,InvoiceTotal,PayerBalance , WhereTreated,Deleted ,Comment,TeamBill,QBExport,claim_status, errors,Error_description,claim_received_date, claim_rejected_date, TOB, Medicare_number,Date_of_service, Date_of_Remits, remit_numebr, DTM_232_date,DTM_233_date, Updated_by,Type_Of_bill,Assessment_Completed_M0090,Certification_End, No_Days,User_Defined_Status,Record_Created_By) SELECT PatientPayer_ID,Agency_ID,Patient_ID,Assessment_Tracker_ID ,Output ,DateSent,MethodSent,'0.00','0.00' ,WhereTreated, Deleted ,Comment,TeamBill,QBExport,'DUPLICATE', errors,Error_description,claim_received_date, claim_rejected_date, TOB, Medicare_number,'#date_of_service#', '#date_of_paid#', '#eft_number#', #DTM_232_date#,#DTM_233_date#, 'Automatic','320',Assessment_Completed_M0090,Certification_End, No_Days,'Needs fix', Record_Created_By FROM #Request.prefix_db_agency#.Invoices WHERE Internal_control_number = '#Internal_Control_number#' AND Internal_control_number !=0 WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Invoice_Lock,0) <> 1) ---> UPDATE IGNORE #Request.prefix_db_agency#.Invoices SET User_defined_Status ='Needs fix', Date_of_service = '#date_of_service#', Date_of_Remits = '#date_of_paid#', remit_numebr = '#eft_number#', Updated_by = 'Automatic', DTM_232_date = #DTM_232_date#, DTM_233_date = #DTM_233_date#, Type_Of_bill = '327' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Invoice_Lock,0) <> 1) UPDATE IGNORE #Request.prefix_db_agency#.Invoices SET claim_status ='RAP Reversal', claim_status ='cancelled', InvoiceTotal = '#Amount#', Date_of_service = '#date_of_service#', Date_of_Remits = '#date_of_paid#', remit_numebr = '#eft_number#', Updated_by = 'Automatic', DTM_232_date = #DTM_232_date#, DTM_233_date = #DTM_233_date#, Type_Of_bill = '328', User_defined_Status ='final paid/RAP cancelled' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Invoice_Lock,0) <> 1) UPDATE IGNORE #Request.prefix_db_agency#.Invoices SET claim_status ='RAP Reversal', claim_status ='cancelled', InvoiceTotal = '#Amount#', Date_of_service = '#date_of_service#', Date_of_Remits = '#date_of_paid#', remit_numebr = '#eft_number#', Updated_by = 'Automatic', DTM_232_date = #DTM_232_date#, DTM_233_date = #DTM_233_date#, Type_Of_bill = '328', User_defined_Status ='RAP cancelled/no final payment' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Invoice_Lock,0) <> 1) UPDATE IGNORE #Request.prefix_db_agency#.Payments SET Amount = '#paid#', PaymentDate = '#paydate#', WriteoffAmount = '#Amount#', PaymentMethod = 'EFT', WriteOffType = 'payer adjusted', Created_By = '1' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Payment_Lock,0) <> 1) UPDATE IGNORE #Request.prefix_db_agency#.Invoices SET claim_status ='Paid', InvoiceTotal = '#Amount#', Date_of_service = '#date_of_service#', Date_of_Remits = '#date_of_paid#', remit_numebr = '#eft_number#', Updated_by = 'Automatic', DTM_232_date = #DTM_232_date#, DTM_233_date = #DTM_233_date#, Type_Of_bill = '329', User_defined_Status ='final paid/RAP cancelled' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Invoice_Lock,0) <> 1) UPDATE IGNORE #Request.prefix_db_agency#.Payments SET Amount = '#paid#', PaymentDate = '#paydate#', WriteoffAmount = '#Amount#', PaymentMethod = 'EFT', Created_By = '1' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Payment_Lock,0) <> 1) UPDATE IGNORE #Request.prefix_db_agency#.Invoices SET claim_status ='Paid', Date_of_service = '#date_of_service#', Date_of_Remits = '#date_of_paid#', remit_numebr = '#eft_number#', Updated_by = 'Automatic', DTM_232_date = #DTM_232_date#, DTM_233_date = #DTM_233_date#, Type_Of_bill = '322', InvoiceTotal = '#totalAmount#' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Invoice_Lock,0) <> 1) UPDATE IGNORE #Request.prefix_db_agency#.Payments SET Amount = '#paid#', PaymentDate = '#paydate#', WriteoffAmount = '#Amount#', WriteOffType = 'payer adjusted', PaymentMethod = 'EFT', Created_By = '1' WHERE Internal_control_number ='#Internal_Control_number#' AND Internal_control_number !=0 AND (IFNULL(Payment_Lock,0) <> 1)

Internal Control Number	Total_Amount	Paid Amount	Writeoff Amount	PaymentDate	PAYMENT TYPE	Patient_name
#Internal_Control_number#	#Amount#	#paid#	#WriteoffAmount#	#paydate#	#type1# #type2#	#DTM# #DTM1# #DTM2#  #RAP_cancel#  #RAP_reversal#  #DTM_232_date#   #DTM_233_date#	#select_patient.Last_Name_M0040#,#select_patient.First_Name_M0040#