View: dbo.Scheduled_Payroll_Run_Enhanced_View
View definition
--Scheduled_Payroll_Run_Enhanced_View is added since the Scheduled_Payroll_Run view
--has a performance issue for large databases due to the vendor check logic.
CREATE VIEW dbo.Scheduled_Payroll_Run_Enhanced_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
) 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 */
;