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;