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 */ ; | |||