View: dbo.Check_Bank_Transaction_View | |||
View definition | |||
CREATE VIEW dbo.Check_Bank_Transaction_View AS SELECT bank_transaction_KEY = BT.bank_transaction_KEY , transaction_date = BT.transaction_date , bank_transaction_type_KEY = BT.bank_transaction_type_KEY , checkbook_KEY = BT.checkbook_KEY , transaction_status_KEY = BT.transaction_status_KEY , amount = BT.amount , reference_number = BT.reference_number , memo = BT.memo , description = BT.description , is_handwritten_check = BT.is_handwritten_check , vendor_KEY = VBT.vendor_KEY , vendor_amortization_schedule_entry_KEY= VBT.vendor_amortization_schedule_entry_KEY , address_1 = COALESCE(A.address_1, '') , address_2 = COALESCE(A.address_2, '') , city = COALESCE(A.city, '') , state_abbreviation = COALESCE(A.state_abbreviation, '') , postal_code = COALESCE(A.postal_code, '') , county = COALESCE(A.county, '') , country = COALESCE(A.country, '') , direct_deposit_amount = COALESCE(DDBTA.direct_deposit_amount, 0.00) , origin__accounting_application_KEY = BT.origin__accounting_application_KEY , funding_processed_status_filter_type_KEY = ( CASE WHEN BT.funding_processed_status_KEY IN (1, 2) THEN 2 -- Pending WHEN BT.funding_processed_status_KEY = 3 THEN 3 -- Funded WHEN BT.funding_processed_status_KEY = 4 THEN 4 -- Not Applicable ELSE 10 -- Invalid funding_processed_status_KEY END ) FROM dbo.Bank_Transaction BT INNER JOIN dbo.Vendor_Bank_Transaction VBT ON VBT.bank_transaction_KEY = BT.bank_transaction_KEY LEFT JOIN dbo.Bank_Transaction_Address BTA ON BTA.bank_transaction_KEY = BT.bank_transaction_KEY LEFT JOIN dbo.Address A ON A.address_KEY = BTA.address_KEY LEFT JOIN (SELECT bank_transaction_KEY ,direct_deposit_amount = SUM(amount) FROM dbo.Direct_Deposit_Bank_Transaction_Allocation GROUP BY bank_transaction_KEY) DDBTA ON DDBTA.bank_transaction_KEY = BT.bank_transaction_KEY WHERE BT.bank_transaction_type_KEY in (1,3,10,11) -- Check, Payment, AP Check, AP Payment | |||