View: dbo.Pay_Direct_Deposit_Allocation_With_Partial_Impound_Bugfix_View | |||
View definition | |||
CREATE VIEW dbo.Pay_Direct_Deposit_Allocation_With_Partial_Impound_Bugfix_View AS SELECT DDBTA.direct_deposit_bank_transaction_allocation_KEY as direct_deposit_allocation_KEY , DDBTA.bank_account_number , DDBTA.bank_account_type_KEY , DDBTA.bank_KEY , DDBTA.amount AS allocation_amount , DDBTA.direct_deposit_status_KEY as allocation_direct_deposit_status_KEY , DDBTA.use_international_format , DDBTA.us_gateway_operator__bank_KEY , DDBTA.destination__country_code_KEY , DDBTA.is_processed , DDBTA.effective_date , PBT.employee_KEY , PBT.bank_transaction_KEY , EE.social_security_number as ssn_ein , BT.description as payee_name , BT.bank_transaction_type_KEY , BT.checkbook_KEY , BT.transaction_date , BT.reference_number , BT.funding_processed_status_KEY , BT.origin__accounting_application_KEY , CB.client_KEY , CB.description AS checkbook__description , CB.bank_KEY AS checkbook__bank_KEY , CB.bank_account_number AS checkbook__bank_account_number , CB.bank_account_type_KEY AS checkbook__bank_account_type_KEY FROM dbo.Direct_Deposit_Bank_Transaction_Allocation AS DDBTA INNER JOIN dbo.Direct_Deposit_Bank_Transaction AS DDBT ON DDBTA.bank_transaction_KEY = DDBT.bank_transaction_KEY INNER JOIN dbo.Payroll_Bank_Transaction AS PBT ON DDBTA.bank_transaction_KEY = PBT.bank_transaction_KEY INNER JOIN dbo.Bank_Transaction AS BT ON PBT.bank_transaction_KEY = BT.bank_transaction_KEY INNER JOIN dbo.Checkbook AS CB ON BT.checkbook_KEY = CB.checkbook_KEY INNER JOIN dbo.Employee AS EE ON PBT.employee_KEY = EE.employee_KEY WHERE --If we are funding just the net check then the direct deposit portion will be split out separately into a live --client side transaction. In such a case we do not want to look at the impound memo, but just the live client --side transaction. --If we are funding the entire transaction or only the direct deposit then we will want to load the direct deposit --from the impound memo (as the direct deposit will be impounded/funded). (BT.transaction_status_KEY = 1 /* Live */ OR (BT.transaction_status_KEY = 9 /* Impound Memo */ AND DDBT.fund_payroll_transaction_type_KEY IN (1 /* Entire transaction */,2 /* Direct deposit only */))) AND BT.bank_transaction_type_KEY IN (1 /* Check */,5 /* Payroll check */,10 /* AP check */) AND BT.is_handwritten_check = 0; | |||