View: dbo.Scheduled_Payroll_Run_Invoice_Amount_View | |||
View definition | |||
-- The following complex query sums the gross amount of the firm receivable transactions associated with a client's -- scheduled payroll run instance. This association occurs via... -- ...the checks belonging to the scheduled payroll run instance -- ...to the check print run containing these checks -- ...to the billable items also associated with the check print run -- ...to the receivable transaction fees created for the billable items -- ...and finally to the receivable transactions that contain the fees. -- This path is per analyst specifications. It is ambiguous, and resolves the ambiguities by preferring the most recent -- relevant check print run, and preferring smaller bank transaction and check print run keys (see the -- priority_selection column). Ordering by key values does not provide any guarantees, but smaller numbers tend to -- refer to older rows, discouraging arbitrary changes in the query output when new rows are added to the database. The -- end result is that a given receivable's gross amount will be included at most once in the output, associated with a -- scheduled payroll run instance. -- -- drop view dbo.Scheduled_Payroll_Run_Invoice_Amount_View create view dbo.Scheduled_Payroll_Run_Invoice_Amount_View as with -- Find all the firm receivable transactions related to the client's scheduled payroll runs. Because of the ambiguity -- of the relationships, the receivable transactions will be duplicated in the result set. Columns -- receivable_transaction_KEY and priority_selection provide a unique key. All_Receivable_Transactions_Related_To_A_Client_Scheduled_Payroll_Run as ( select bt.client_pay_schedule_detail_KEY , cprnbt.check_print_run_KEY , rt.receivable_transaction_KEY , rt.gross_amount , priority_selection = row_number() over ( partition by rt.receivable_transaction_KEY order by cpr.print_date desc, cpr.check_print_run_KEY asc, bt.bank_transaction_KEY asc) from ( -- Pick up payroll checks that belong to this schedule payroll run. select cbk.client_KEY , n.client_pay_schedule_detail_KEY , bt1.checkbook_KEY , bt1.bank_transaction_KEY from dbo.Payroll_Bank_Transaction_N_Client_Pay_Schedule_Detail as n inner join dbo.Bank_Transaction as bt1 on n.bank_transaction_KEY = bt1.bank_transaction_KEY inner join dbo.Checkbook as cbk on bt1.checkbook_KEY = cbk.checkbook_KEY WHERE EXISTS( SELECT 1 FROM dbo.Client AS cl WHERE cl.client_KEY = cbk.client_KEY AND cl.record_status_KEY <> 3 /* Deleted */ ) union all -- Pick up vendor (payroll agent) checks that belong to this schedule payroll run. Unlike payroll checks, -- there is no direct link between a scheduled payroll run and the vendor checks produced by it. So, for -- the purposes of this script, any payroll agent check that is dated on the pay schedule date is -- considered part of the pay schedule. This has the potential to pick up extra checks that were not -- actually part of the pay schedule but the analysts feel this will be more correct than only picking -- up payroll agent checks that are paying payroll liabilities generated by the scheduled payroll run. select vendor_checks.client_KEY , vendor_checks.client_pay_schedule_detail_KEY , vendor_checks.checkbook_KEY , vendor_checks.bank_transaction_KEY FROM ( SELECT cps2.client_KEY , cpsd2.client_pay_schedule_detail_KEY , bt2.checkbook_KEY , bt2.bank_transaction_KEY , selection_priority = row_number() over ( partition by bt2.bank_transaction_KEY order by case -- Completed schedules are preferred. when cpsd2.client_pay_schedule_status_KEY = 10 -- Complete then 1 -- Pending states are third in preference. when cpsd2.client_pay_schedule_status_KEY = 2 -- Pending Update then 3 -- Inactive states are last in preference. when cpsd2.client_pay_schedule_status_KEY in (1, 7, 9) -- Unknown, Cancelled, Suspended then 4 -- All active states are second in preference. else 2 end , cpsd2.client_pay_schedule_detail_KEY ) from dbo.Client_Pay_Schedule_Detail as cpsd2 inner join dbo.Client_Pay_Schedule as cps2 on cps2.client_pay_schedule_KEY = cpsd2.client_pay_schedule_KEY inner join dbo.Checkbook as cbk2 on cps2.client_KEY = cbk2.client_KEY inner join dbo.Bank_Transaction as bt2 on cpsd2.check_date = bt2.transaction_date and cbk2.checkbook_KEY = bt2.checkbook_KEY inner join dbo.Vendor_Bank_Transaction as vbt on bt2.bank_transaction_KEY = vbt.bank_transaction_KEY inner join dbo.Vendor as v on vbt.vendor_KEY = v.vendor_KEY where bt2.bank_transaction_type_KEY = 1 -- Check and bt2.transaction_status_KEY = 1 -- Live and bt2.funding_processed_status_KEY = 4 -- Do Not Process For Funding and v.current__vendor_type_KEY = 2 -- Payroll Agent AND EXISTS( SELECT 1 FROM dbo.Client AS cl WHERE cl.client_KEY = cps2.client_KEY AND cl.record_status_KEY <> 3 /* Deleted */ ) ) AS vendor_checks WHERE -- Since vendor checks are related to the pay schedule only by date, a particular check may be -- eligible to be included in more than one pay schedule. In order to not count these checks more than -- once, pick pay schedules that are Complete and have the lowest key value to include them on. selection_priority = 1 ) as bt -- Follow each bank transaction to it's check print run to billiable items to receivable detail fees to the -- receivable transaction. inner join dbo.Check_Print_Run_N_Bank_Transaction as cprnbt on bt.bank_transaction_KEY = cprnbt.bank_transaction_KEY inner join dbo.Check_Print_Run as cpr on cprnbt.check_print_run_KEY = cpr.check_print_run_KEY inner join dbo.Billable_Item_N_Check_Print_Run as bincpr on cpr.check_print_run_KEY = bincpr.check_print_run_KEY inner join dbo.Billable_Item as bi on bincpr.billable_item_KEY = bi.billable_item_KEY and bt.client_KEY = bi.client_KEY inner join dbo.Receivable_Transaction_Fee as rtf on bi.billable_item_KEY = rtf.billable_item_KEY inner join dbo.Receivable_Transaction_Detail as rtd on rtf.receivable_transaction_detail_KEY = rtd.receivable_transaction_detail_KEY inner join dbo.Receivable_Transaction as rt on rtd.receivable_transaction_KEY = rt.receivable_transaction_KEY ), -- Remove duplicate receivables by keep the ones associated with the most recent check print run and lowest bank -- transaction key. Column receivable_transaction_KEY provides a unique key. Scheduled_Payroll_Run_Receivable_Transactions as ( select client_pay_schedule_detail_KEY , receivable_transaction_KEY , gross_amount from All_Receivable_Transactions_Related_To_A_Client_Scheduled_Payroll_Run where priority_selection = 1 ) -- Sum the receivable gross amounts associated with each scheduled payroll run instance. Column -- scheduled_payroll_run_KEY (client_pay_schedule_detail_KEY) provides a unique key. select scheduled_payroll_run_KEY = client_pay_schedule_detail_KEY , invoice_amount = sum(gross_amount) from Scheduled_Payroll_Run_Receivable_Transactions group by client_pay_schedule_detail_KEY; | |||