View: dbo.Payroll_Funding_Transaction_View | |||
View definition | |||
CREATE VIEW dbo.Payroll_Funding_Transaction_View AS SELECT BT.bank_transaction_KEY , BT.transaction_date , BT.reference_number , BT.amount , direct_deposit_amount , BT.bank_transaction_type_KEY , BT.transaction_status_KEY , BT.edited_ach_effective_date , BT.funding_processed_status_KEY , CB.checkbook_KEY , CB.bank_KEY , CB.description , PBT.employee_KEY , EE.social_security_number as ssn_ein , BT.description AS payee_name , C.client_KEY , C.client_id , C.description AS client_name , C.name_1 , C.name_2 , CPI.fund_payroll_transaction , CPI.payroll_impound_deposit__checkbook_KEY , CPI.payroll_impound_withdrawal__checkbook_KEY , CPI.payroll_impound_deferred_start_date FROM dbo.Checkbook AS CB INNER JOIN dbo.Bank_Transaction AS BT ON CB.checkbook_KEY = BT.checkbook_KEY INNER JOIN dbo.Payroll_Bank_Transaction AS PBT ON BT.bank_transaction_KEY = PBT.bank_transaction_KEY INNER JOIN dbo.Client AS C ON CB.client_KEY = C.client_KEY INNER JOIN dbo.Client_Payroll_Information AS CPI ON C.client_KEY = CPI.client_KEY INNER JOIN dbo.Employee AS EE ON PBT.employee_KEY = EE.employee_KEY LEFT JOIN ( SELECT bank_transaction_KEY, SUM(amount) AS direct_deposit_amount FROM dbo.Direct_Deposit_Bank_Transaction_Allocation GROUP BY bank_transaction_KEY ) AS DDBTASum ON BT.bank_transaction_KEY = DDBTASum.bank_transaction_KEY WHERE BT.funding_processed_status_KEY <> 4 AND -- Bank_Transaction_View.bank_transaction_displayable_status_KEY = 1 (BT.transaction_status_KEY IN (1, 9) AND BT.bank_transaction_type_KEY NOT IN (1,5,7,8,9,10) OR -- Bank_Transaction_View.bank_transaction_displayable_status_KEY IN (4, 5) BT.transaction_status_KEY IN (4, 5) OR -- Bank_Transaction_View.bank_transaction_displayable_status_KEY = 50 (BT.transaction_status_KEY IN (1, 9) AND BT.bank_transaction_type_KEY IN (1,5,10) AND BT.is_handwritten_check = 0)); | |||