SELECT COUNT(*) AS n FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = AND TABLE_NAME IN ('Invoices', 'Payments') AND COLUMN_NAME = 'lock_date' 1) " /> 1 OR lock_date IS NOT NULL) " /> SELECT lock_date, #arguments.dateCol# AS record_date, IFNULL(adjusted_from_id, 0) AS adjusted_from_id FROM #Request.prefix_db_agency#.#arguments.tableName# WHERE #arguments.keyCol# = LIMIT 1 SELECT 1 FROM #Request.prefix_db_agency#.Payments WHERE Invoice_ID = AND lock_date IS NOT NULL AND IFNULL(Deleted, 0) <> 1 LIMIT 1 SELECT * FROM ( SELECT i.Invoice_ID, i.adjusted_from_id, py.Pay_ID, py.Pay_Name, py.PayerSource, p.Patient_ID, p.Pt_Last, p.Pt_First, p.Pt_Agy_ID, p.Pt_DOB, i.DateSent, ROUND(IFNULL(i.ContractTotal, 0), 2) AS adj_amount, ROUND(IFNULL(pay.paid, 0), 2) AS paid, ROUND(IFNULL(i.ContractTotal, 0) - IFNULL(pay.paid, 0), 2) AS balance, DATEDIFF(, i.DateSent) AS AR_age FROM #Request.prefix_db_agency#.Invoices i JOIN #Request.prefix_db_agency#.pPayer py ON py.Pay_ID = i.PatientPayer_ID AND py.Status = 0 JOIN #Request.prefix_db_agency#.pPatients p ON p.Patient_ID = i.Patient_ID AND p.Status IN (#Request.patient_status#) LEFT JOIN ( SELECT Invoice_ID, SUM(IFNULL(Amount, 0) + CASE WHEN IFNULL(WriteOffType, '') LIKE 'PR%' THEN 0 ELSE IFNULL(WriteOffAmount, 0) END) AS paid FROM #Request.prefix_db_agency#.Payments WHERE Deleted <> 1 AND PaymentDate <= AND Invoice_ID IN (SELECT Invoice_ID FROM #Request.prefix_db_agency#.Invoices WHERE adjusted_from_id > 0) GROUP BY Invoice_ID ) pay ON pay.Invoice_ID = i.Invoice_ID WHERE i.Agency_ID = AND i.Deleted <> 1 AND i.adjusted_from_id > 0 AND i.DateSent IS NOT NULL AND i.DateSent <= AND i.DateSent >= AND i.Assmt_ID > 0 AND i.Assmt_ID = 0 AND py.Pay_ID = AND p.Patient_ID = AND DATEDIFF(CURDATE(), i.DateSent) >= ) adj WHERE adj.balance <> 0 ORDER BY adj.Pay_Name, adj.Pay_ID, adj.Pt_Last, adj.Pt_First, adj.Invoice_ID SELECT t.#arguments.keyCol# FROM #Request.prefix_db_agency#.#arguments.tableName# t JOIN #Request.prefix_db_agency#.Invoices i ON i.Invoice_ID = t.Invoice_ID WHERE t.#arguments.keyCol# = AND t.lock_date IS NOT NULL AND IFNULL(t.Deleted, 0) <> 1 AND i.Agency_IDt.Agency_ID = SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = AND TABLE_NAME = AND COLUMN_NAME NOT IN ('#arguments.keyCol#', 'cdate_cdarte') ORDER BY ORDINAL_POSITION INSERT INTO #Request.prefix_db_agency#.#arguments.tableName# (#ArrayToList(insertCols)#) SELECT #PreserveSingleQuotes(selectSql)# FROM #Request.prefix_db_agency#.#arguments.tableName# WHERE #arguments.keyCol# =