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