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));