View: dbo.Vendor_Payment_Funding_Transaction_View
View definition
CREATE VIEW dbo.Vendor_Payment_Funding_Transaction_View
AS
SELECT
BT.bank_transaction_KEY
, BT.transaction_date
, BT.reference_number
, BT.amount
, direct_deposit_amount
, BT.transaction_status_KEY
, BT.bank_transaction_type_KEY
, BT.edited_ach_effective_date
, BT.funding_processed_status_KEY
, CB.checkbook_KEY
, CB.bank_KEY
, CB.description
, VBT.vendor_KEY
, CAST(CASE V.current__vendor_type_KEY
WHEN 5 THEN 1
ELSE 0
END AS bit) AS is_firm_vendor
, BT.description AS payee_name
, C.client_KEY
, C.client_id
, C.description AS client_name
, C.name_1
, C.name_2
, CPI.fund_tax_agent_transaction
, CPI.fund_payroll_agent_transaction
, CPI.fund_firm_vendor_transaction
, CPI.tax_agent_impound_deposit__checkbook_KEY
, CPI.tax_agent_impound_withdrawal__checkbook_KEY
, CPI.tax_agent_impound_deferred_start_date
, CPI.payroll_agent_impound_deposit__checkbook_KEY
, CPI.payroll_agent_impound_withdrawal__checkbook_KEY
, CPI.payroll_agent_impound_deferred_start_date
, CPI.firm_vendor_impound_deposit__checkbook_KEY
, CPI.firm_vendor_impound_withdrawal__checkbook_KEY
, CPI.firm_vendor_impound_deferred_start_date
FROM
dbo.Checkbook AS CB
INNER JOIN
dbo.Bank_Transaction_View AS BT ON CB.checkbook_KEY = BT.checkbook_KEY
INNER JOIN
dbo.Vendor_Bank_Transaction AS VBT ON BT.bank_transaction_KEY = VBT.bank_transaction_KEY
INNER JOIN
dbo.Vendor AS V ON VBT.vendor_KEY = V.vendor_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
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
BT.bank_transaction_displayable_status_KEY IN (1, 4, 5, 50)