View: dbo.Scheduled_Payroll_Run_View
View definition
--Create View for Payroll Schedule View for Filters
CREATE VIEW dbo.Scheduled_Payroll_Run_View
AS
SELECT
scheduled_payroll_run_KEY = cpsdv.client_pay_schedule_detail_KEY
, check_date = cpsdv.check_date
, time_in_date = cpsdv.time_in_date
, checks_out_date = cpsdv.checks_out_date
, client_pay_schedule_KEY = cps.client_pay_schedule_KEY
, payschedule_name = cps.payschedule_name
, is_schedule_inactive = cps.is_schedule_inactive
, client_pay_schedule_type_KEY = cps.client_pay_schedule_type_KEY
, backup_processor__staff_KEY = sv1.staff_KEY
, backup_processor_id = sv1.staff_id
, backup_processor__description = sv1.description
, client__description = cv.description
, client__date_left = cv.date_left
, client_type_KEY = cv.client_type_KEY
, do_not_pay_reason_KEY = sptd.do_not_pay_reason_KEY
, pay_frequency_KEY = pf.pay_frequency_KEY
, pay_frequency__description = pf.description
, primary_processor__staff_KEY = sv.staff_KEY
, processor_id = sv.staff_id
, primary_processor_description = sv.description
, scheduled_backup_processor__staff_KEY = sv3.staff_KEY
, scheduled_backup_processor__staff_id = sv3.staff_id
, scheduled_backup_processor__description = sv3.description
, scheduled_payroll_change_initiator_KEY = sptd.scheduled_payroll_change_initiator_KEY
, scheduled_primary_processor__staff_KEY = sv2.staff_KEY
, scheduled_primary_processor__staff_id = sv2.staff_id
, scheduled_primary_processor__description = sv2.description
, time_entry_method__description = tem.description
, check_output_type__description = cot.description
, client_KEY = cps.client_KEY
, client_id = cv.client_id
, is_client_self_service_enabled = cpi.is_client_self_service_enabled
, notes = sptd.notes
, printed_check_count = bt_metrics.printed_check_count
, payroll_check_count = bt_metrics.payroll_check_count
, vendor_check_count = bt_metrics.vendor_check_count
, paper_payroll_check_count = bt_metrics.paper_payroll_check_count
, remote_payroll_check_count = bt_metrics.remote_payroll_check_count
, assigned_processor__staff_KEY = COALESCE(sv2.staff_KEY, sv.staff_KEY)
, is_do_not_pay = COALESCE(sptd.is_do_not_pay, 0)
, active_status_KEY = cps.active_status_KEY
, exclude_from_client_self_service = cps.exclude_from_client_self_service
, is_sbpr_client_access_enabled = cpi.is_sbpr_client_access_enabled
, exclude_from_client_access = cps.exclude_from_client_access
, scheduled_payroll_run_status_KEY =
CASE cpsdv.client_pay_schedule_displayable_status_KEY
WHEN 10 /* Completed */ THEN
CASE
WHEN bt_metrics.printed_check_count = bt_metrics.check_count
THEN 11 /* Printed */
WHEN bt_metrics.printed_check_count = 0
THEN 12 /* Unprinted */
ELSE 13 /* Partially printed */
END
ELSE
cpsdv.client_pay_schedule_displayable_status_KEY
END
, date_completed =
CASE
WHEN bt_metrics.client_pay_schedule_detail_KEY is not NULL
AND bt_metrics.check_count = bt_metrics.printed_check_count
AND bt_metrics.completion_date_candidate is not NULL
AND cpsdv.client_pay_schedule_displayable_status_KEY = 10 /*Payroll Scheule should be Completed (Printed, Unprinted or Partially Printed)*/
THEN bt_metrics.completion_date_candidate
ELSE
sptd.date_completed
END
, payroll_journal_entry_status_KEY =
CASE
WHEN bt_metrics.client_pay_schedule_detail_KEY is NULL
or bt_metrics.check_count = bt_metrics.not_exported_check_count
THEN
CASE ejetci.export_destination_KEY
WHEN 0 /* None */THEN 1
WHEN 1 /* CSA */THEN 2
WHEN 2 /* Quickbooks */ THEN 3
WHEN 3 /* ACS */THEN 4
END
WHEN bt_metrics.check_count = bt_metrics.exported_check_count
THEN /* Completed */ 5
ELSE /* Partially completed */ 6
END
, impound_profile__description = ip.description
, vendor_layout_report_definition__description = rd.vendor_description
, payroll_layout_report_definition__description = rd.payroll_description
FROM dbo.Client_Pay_Schedule_Detail_View AS cpsdv
INNER JOIN dbo.Client_Pay_Schedule AS cps ON cpsdv.client_pay_schedule_KEY = cps.client_pay_schedule_KEY
INNER JOIN dbo.Export_Journal_Entry_To_Csa_Information AS ejetci ON cps.client_KEY = ejetci.client_KEY
INNER JOIN dbo.Pay_Frequency AS pf ON cps.pay_frequency_KEY = pf.pay_frequency_KEY
INNER JOIN dbo.Client AS cv ON cps.client_KEY = cv.client_KEY
INNER JOIN dbo.Client_Payroll_Information AS cpi ON cps.client_KEY = cpi.client_KEY
INNER JOIN dbo.Time_Entry_Method AS tem ON cps.time_entry_method_KEY = tem.time_entry_method_KEY
INNER JOIN dbo.Check_Output_Type AS cot ON cpi.check_output_type_KEY = cot.check_output_type_KEY
LEFT OUTER JOIN dbo.Scheduled_Payroll_Tracking_Detail AS sptd ON cpsdv.client_pay_schedule_detail_KEY = sptd.client_pay_schedule_detail_KEY
LEFT OUTER JOIN dbo.Staff AS sv ON cpi.primary_processor__staff_KEY = sv.staff_KEY
LEFT OUTER JOIN dbo.Staff AS sv1 ON cpi.backup_processor__staff_KEY = sv1.staff_KEY
LEFT OUTER JOIN dbo.Do_Not_Pay_Reason AS dnpr ON sptd.do_not_pay_reason_KEY = dnpr.do_not_pay_reason_KEY
LEFT OUTER JOIN dbo.Staff AS sv2 ON sptd.primary_processor__staff_KEY = sv2.staff_KEY
LEFT OUTER JOIN dbo.Staff AS sv3 ON sptd.backup_processor__staff_KEY = sv3.staff_KEY
LEFT OUTER JOIN dbo.Impound_Profile AS ip ON cpi.impound_profile_KEY = ip.impound_profile_KEY
LEFT OUTER JOIN dbo.Check_Layout_Report_Definition_Descriptions_Per_Client_View AS rd ON cv.client_KEY = rd.client_KEY
-- Gather metrics from the associated collection of bank transactions, if any.
LEFT OUTER JOIN
(
SELECT
bt.client_pay_schedule_detail_KEY
, check_count = count(*)
, printed_check_count = sum(CASE WHEN bt.transaction_status_KEY in (4 /*Pending*/, 5 /*Unprinted*/) THEN 0 ELSE 1 END)
, not_exported_check_count = sum(CASE WHEN bt.export_journal_entry_to_csa_status_KEY = 1 /*LiveNotExported*/ THEN 1 ELSE 0 END)
, exported_check_count = sum(CASE WHEN bt.export_journal_entry_to_csa_status_KEY = 2 /*LiveExported*/ THEN 1 ELSE 0 END)
, payroll_check_count = sum(CASE WHEN bt.bank_transaction_type_KEY = 5 /*Payroll check*/ THEN 1 ELSE 0 END)
, vendor_check_count = sum(CASE WHEN bt.transaction_status_KEY = 1 /*Live*/ and bt.funding_processed_status_KEY = 4 /*Do Not Process For Funding*/and bt.bank_transaction_type_KEY = 1 /*Vendor Check*/ THEN 1 ELSE 0 END)
, paper_payroll_check_count = sum(CASE WHEN bt.bank_transaction_type_KEY = 5 /*Payroll check*/ and bt.check_output_type_KEY = 1 /* Paper check/stubs */ THEN 1 ELSE 0 END)
, remote_payroll_check_count = sum(CASE WHEN bt.bank_transaction_type_KEY = 5 /*Payroll check*/ and bt.check_output_type_KEY = 2 /* Remote check printing */ THEN 1 ELSE 0 END)
, completion_date_candidate = max(bte.finalize__event_date)
FROM
(
-- Pick up payroll checks that belong to this schedule payroll run.
SELECT
n.client_pay_schedule_detail_KEY
, bt1.bank_transaction_KEY
, bt1.transaction_status_KEY
, bt1.bank_transaction_type_KEY
, bt1.export_journal_entry_to_csa_status_KEY
, bt1.funding_processed_status_KEY
, bt1.check_output_type_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
UNION ALL
-- Pick up checks that belong to this schedule payroll run. 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 aren't 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
client_pay_schedule_detail_KEY
, bank_transaction_KEY
, transaction_status_KEY
, bank_transaction_type_KEY
, export_journal_entry_to_csa_status_KEY
, funding_processed_status_KEY
, check_output_type_KEY
FROM
(
SELECT
cpsd2.client_pay_schedule_detail_KEY
, bt2.bank_transaction_KEY
, bt2.transaction_status_KEY
, bt2.bank_transaction_type_KEY
, bt2.export_journal_entry_to_csa_status_KEY
, bt2.funding_processed_status_KEY
, bt2.check_output_type_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
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 EXISTS ( SELECT 1
FROM
dbo.Vendor_Bank_Transaction
AS vbt
INNER JOIN dbo.Vendor
AS v ON vbt.vendor_KEY = v.vendor_KEY
WHERE
bt2.bank_transaction_KEY = vbt.bank_transaction_KEY
AND v.current__vendor_type_KEY IN (
2, 3 ))-- Payroll Agent, Tax Agent
) 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
LEFT JOIN
(
-- Grab the bank transaction's finalize event if it has one.
SELECT
bank_transaction_KEY
, finalize__event_date = event_date
FROM
dbo.Bank_Transaction_Event as bte
WHERE
bank_transaction_event_type_KEY = 2 -- Finalize
and EXISTS (select 1 from dbo.Payroll_Bank_Transaction_N_Client_Pay_Schedule_Detail as pbtncpsd
WHERE bte.bank_transaction_KEY = pbtncpsd.bank_transaction_KEY)
) AS bte
ON bt.bank_transaction_KEY = bte.bank_transaction_KEY
WHERE
bt.transaction_status_KEY in (1 /*Live*/, 5 /*Unprinted*/, 9 /*ImpoundMemo*/)
-- Don't include reversed bank transaction and their reversals.
and not exists (SELECT 1
FROM dbo.Bank_Transaction_Reversal AS btr
INNER JOIN dbo.Bank_Transaction_View AS btv_reversed
ON btr.reversed__bank_transaction_KEY = btv_reversed.bank_transaction_KEY
INNER JOIN dbo.Bank_Transaction_View AS btv_reversal
ON btr.reversal__bank_transaction_KEY = btv_reversal.bank_transaction_KEY
WHERE (btr.reversed__bank_transaction_KEY = bt.bank_transaction_KEY
OR btr.reversal__bank_transaction_KEY = bt.bank_transaction_KEY)
AND (btv_reversed.transaction_status_KEY IN (1 /* Live */, 9 /* ImpoundMemo */))
AND (btv_reversal.transaction_status_KEY IN (1 /* Live */, 9 /* ImpoundMemo */)))
GROUP BY
bt.client_pay_schedule_detail_KEY
) AS bt_metrics
ON cpsdv.client_pay_schedule_detail_KEY = bt_metrics.client_pay_schedule_detail_KEY
WHERE cv.record_status_KEY <> 3 /* Deleted */
;