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 Internal_control_number = '#Internal_control_number#' AND status =0 SELECT IFNULL(MAX(CLP_ID), 0) AS CLPID FROM #Request.prefix_db_lookup#.cron_835 UPDATE #Request.prefix_db_agency#.pSchedules SET payment = '#Paid_amount#', write_offamount = '#Adjustment_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#' SELECT * FROM #Request.prefix_db_agency#.pSchedules WHERE Status = 0 AND Visit_Date = '#Visit_Date#' AND Skill = '#Skill#' AND Patient_ID = '#getinvoices.Patient_ID#' INSERT IGNORE into #Request.prefix_db_lookup#.cron_835 (Agency_ID, Patient_ID, Schedule_ID, Invoice_ID, Payer_ID, batch, Patient_Name, Date_Deposit, Deposit_Type,Check_Total, Date_Check, Remit_No, ICN, Pay_Name, Payer_Type, Visit_Date, Revenue_Code, Net_Total, Paid_Amount, Adjustment_Amount, Dep_Writeoff_Amount, Adjustment_Group_Code, Adjustment_Code, Seq_Amount, Seq_Code, Adjustment_Code_2,Adjustment_Group_Code_2,Adjustment_Amount_2, total_paymentamount,total_WriteOffAmount, file_name, Date_Create, Craeted_By) VALUES ('#agencynumber[1]#','#getinvoices.Patient_ID#', '#getsch.Schedule_ID#','#getinvoices.Invoice_ID#', '#getinvoices.PatientPayer_ID#',1, '#Patient_Name#','#dateformat(Date_Deposit,"yyyy-mm-dd")#', '#Deposit_Type#','#Check_Total#','#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#','#Adjustment_Amount#','#Amount_Writeoff#','#group_code#','#Adjust_code#', '#Seq_Amount#','#Seq_code#', '#Adjustment_Code_2#','#Adjustment_Group_Code_2#','#Adjustment_Amount_2#', '#total_paymentamount#','#total_WriteOffAmount#', '#name2#',#now()#, 2) SELECT LAST_INSERT_ID() as LastInsertID UPDATE #Request.prefix_db_lookup#.cron_835 SET CLP_ID = '#CLP_ID#' WHERE ID IN (#cron_ids#) AND status = 0 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#	#Adjustment_Amount#	#group_code#	#Adjust_code#	#Seq_Amount#	#Seq_code#