Payment Claims

claim_type NEQ '4'> SELECT Invoices.Invoice_ID,Invoices.PatientPayer_ID,Invoices.Internal_control_number, Invoices.Patient_ID,pPayer.Output ,pPayer.Pay_Name, pPatients.Pt_Last,pPatients.Pt_First FROM #Request.prefix_db_agency#.Invoices JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = Invoices.Patient_ID AND pPatients.Status < 2 JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_ID = Invoices.PatientPayer_ID AND pPayer.Status = 0 WHERE Invoices.Internal_control_number IN ('#Internal_control_number#') UPDATE #Request.prefix_db_agency#.Invoices SET Seq_Amount = '#Seq_Amount#', Seq_code = '#Seq_code#' WHERE Internal_control_number = '#Internal_control_number#' AND (IFNULL(Invoice_Lock,0) <> 1) UPDATE #Request.prefix_db_agency#.pDeposit SET Status = 1, Record_Modified_Date = #now()# WHERE Remit_No = '#Remit_No#' AND status =0 UPDATE #Request.prefix_db_agency#.pSchedules SET payment = '#Paid_amount#', write_offamount = '#Adjust_amount#', write_offcode = '#group_code#', write_offcodesecond = '#Adjust_code#', Date_Change = #now()# WHERE Status = 0 AND Visit_Date = '#Visit_Date#' AND Skill = '#Skill#' AND Patient_ID = '#getinvoices.Patient_ID#' INSERT IGNORE into #Request.prefix_db_agency#.pDeposit (Date_Deposit,Payer_ID, Date_Create,Date_Check, Deposit_Type, Check_Total, Remit_No, Internal_Control_number,HCPCS,SVC04,SVC03,Writeoff_Amount,Visit_Date, Pay_Name, Net_Total, Record_Created_Date, Record_Modified_Date) VALUES ('#Date_Deposit#','#getinvoices.PatientPayer_ID#','#toddaydate#','#Date_Check#','#Deposit_Type#', '#Check_Total#','#Remit_No#', '#Internal_control_number#','#HCPCS_ID#','#SVC04#','#SVC03#','#Amount_Writeoff#','#Visit_Date#', '#Pay_Name#','#Net_Total#', #now()#,#now()#) UPDATE #Request.prefix_db_agency#.Payments SET Deleted = 1, Record_Modified_Date = #now()# WHERE Invoice_ID = '#getinvoices.Invoice_ID#' AND EFT_No = '#Remit_No#' AND Amount = '#total_paymentamount#' AND WriteOffAmount = '#total_WriteOffAmount#' AND (IFNULL(Payment_Lock,0) <> 1) INSERT INTO #Request.prefix_db_agency#.Payments( Invoice_ID, PatientPayer_ID, PaymentDate, Amount, PaymentMethod, EFT_Amount, EFT_No, Claim, Comments, WriteOffAmount, WriteOffType, Deleted, Internal_control_number) VALUES ('#getinvoices.Invoice_ID#','#getinvoices.PatientPayer_ID#','#Date_Deposit#', '#total_paymentamount#','Electronic','#Check_Total#', '#Remit_No#', '#Internal_control_number#','Auto claim payment processing', '#total_WriteOffAmount#','#Adjust_code#', 0, '#Internal_control_number#')

Patient Name	Date Deposit	Deposit_Type	Date Check/ Status	Remit_No	Internal Control_number	Pay Name	Payer type	Visit Date	Revenue Code	Net Total	Paid Amount	Adjustment Amount	Adjustment Group code	Adjustment code	Seq Amount	Seq Code
#getinvoices.Pt_Last#, #getinvoices.Pt_First#	#dateformat(Date_Deposit,"yyyy-mm-dd")#	#Deposit_Type#	#dateformat(Date_Check,"yyyy-mm-dd")#	#Remit_No#	#Internal_control_number#	#Left(getinvoices.Pay_Name,30)#..	#getinvoices.Output#	#Visit_Date#	#HCPCS_ID#	#Total_amount#	#Paid_amount#	#Adjust_amount#	#group_code#	#Adjust_code#	#Seq_Amount#	#Seq_code#