View: dbo.Payroll_Bank_Transaction_Full_View | |||
View definition | |||
CREATE VIEW dbo.Payroll_Bank_Transaction_Full_View AS SELECT bank_transaction_KEY = BT.bank_transaction_KEY , transaction_date = BT.transaction_date , bank_transaction_type_KEY = BT.bank_transaction_type_KEY , checkbook_KEY = BT.checkbook_KEY , transaction_status_KEY = BT.transaction_status_KEY , amount = BT.amount , reference_number = BT.reference_number , memo = BT.memo , description = BT.description , is_handwritten_check = BT.is_handwritten_check , export_journal_entry_to_csa_status_KEY = BT.export_journal_entry_to_csa_status_KEY , funding_processed_status_KEY = BT.funding_processed_status_KEY , employee_KEY = PBT.employee_KEY , pay_frequency_KEY = PBT.pay_frequency_KEY , period_begin_date = PBT.period_begin_date , period_end_date = PBT.period_end_date , weeks_worked = PBT.weeks_worked , ste_location_code = PBT.ste_location_code , do_not_create_check_from_enter_batch_checks = PBT.do_not_create_check_from_enter_batch_checks , direct_deposit_amount = COALESCE(DDBTA.direct_deposit_amount, 0.00) , origin__accounting_application_KEY = BT.origin__accounting_application_KEY , funding_processed_status_filter_type_KEY = ( CASE WHEN BT.funding_processed_status_KEY IN (1, 2) THEN 2 -- Pending WHEN BT.funding_processed_status_KEY = 3 THEN 3 -- Funded WHEN BT.funding_processed_status_KEY = 4 THEN 4 -- Not Applicable ELSE 10 -- Invalid funding_processed_status_KEY END ) FROM dbo.Bank_Transaction BT INNER JOIN dbo.Payroll_Bank_Transaction PBT ON PBT.bank_transaction_KEY = BT.bank_transaction_KEY LEFT OUTER JOIN (SELECT bank_transaction_KEY ,direct_deposit_amount = SUM(amount) FROM dbo.Direct_Deposit_Bank_Transaction_Allocation GROUP BY bank_transaction_KEY) DDBTA ON DDBTA.bank_transaction_KEY = BT.bank_transaction_KEY WHERE bank_transaction_type_KEY = 5 | |||