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