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;