View: dbo.Bank_Transaction_List_View
View definition
CREATE VIEW dbo.Bank_Transaction_List_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
, reference_number = CASE WHEN LEN(BT.reference_number) > 0 THEN BT.reference_number
ELSE CASE WHEN BT.bank_transaction_type_KEY IN (1,5,8,9,10)
THEN N'Unprinted'
ELSE BTT.description
END
END
, memo = BT.memo
, description = BT.description
, client_KEY = CB.client_KEY
, id = COALESCE(V.vendor_id COLLATE Latin1_General_CI_AS,
E.employee_id COLLATE Latin1_General_CI_AS, N'')
, payment = CASE WHEN BT.bank_transaction_type_KEY IN (1,3,5,7,8,9,10,11)
OR (BT.bank_transaction_type_KEY = 4 and BT.amount <= 0)
THEN CASE WHEN BT.transaction_status_KEY in (2, 14) -- Deleted, Impound Memo Deleted
THEN N'**DELETE**'
WHEN BT.transaction_status_KEY in (3, 15) -- Voided, Impound Memo Voided
THEN N'**VOID**'
WHEN BT.bank_transaction_type_KEY = 4
THEN convert(varchar(50),convert(money,-BT.amount),1)
ELSE convert(varchar(50),convert(money,BT.amount),1)
END
ELSE null
END
, deposit = CASE WHEN BT.bank_transaction_type_KEY IN (2,6,12)
OR (BT.bank_transaction_type_KEY = 4 and BT.amount > 0)
THEN CASE WHEN BT.transaction_status_KEY = 2
THEN N'**DELETE**'
WHEN BT.transaction_status_KEY = 3
THEN N'**VOID**'
ELSE convert(varchar(50),convert(money,BT.amount),1)
END
ELSE null
END
, sort_date = COALESCE(BT.transaction_date , N'2079-06-06 00:00:00:00')
, sort_payment = CASE WHEN BT.bank_transaction_type_KEY IN (1,3,5,7,8,9,10,11)
OR (BT.bank_transaction_type_KEY = 4 and BT.amount <= 0)
THEN CASE WHEN BT.transaction_status_KEY = 2 /*Deleted*/
THEN CAST(-1000000000000 AS DECIMAL(17,2))
WHEN BT.transaction_status_KEY = 3 /*Voided*/
THEN CAST(-1000000000001 AS DECIMAL(17,2))
WHEN BT.bank_transaction_type_KEY = 4
THEN -BT.amount
ELSE BT.amount
END
ELSE CAST(-1000000000002 AS DECIMAL(17,2)) /*Deposits*/
END
, sort_deposit = CASE WHEN BT.bank_transaction_type_KEY IN (2,6,12)
OR (BT.bank_transaction_type_KEY = 4 and BT.amount > 0)
THEN CASE WHEN BT.transaction_status_KEY = 2 /*Deleted*/
THEN CAST(-1000000000000 AS DECIMAL(17,2))
WHEN BT.transaction_status_KEY = 3 /*Voided*/
THEN CAST(-1000000000001 AS DECIMAL(17,2))
ELSE BT.amount
END
ELSE CAST(-1000000000002 AS DECIMAL(17,2)) /*Payments*/
END
, bank_transaction_displayable_status_KEY = BT.bank_transaction_displayable_status_KEY
, is_transfer = BT.is_transfer
, does_not_affect_accounting = BT.does_not_affect_accounting
, id_sortable = COALESCE(V.vendor_id_sortable COLLATE Latin1_General_CI_AS,
E.employee_id_sortable COLLATE Latin1_General_CI_AS, N'')
, reference_number_sortable = BT.reference_number_sortable
FROM
dbo.Bank_Transaction_View BT
JOIN
dbo.Bank_Transaction_Type BTT ON BT.bank_transaction_type_KEY = BTT.bank_transaction_type_KEY
JOIN
dbo.Checkbook CB ON BT.checkbook_KEY = CB.checkbook_KEY
LEFT JOIN
dbo.Vendor_Bank_Transaction VBT ON VBT.bank_transaction_KEY = BT.bank_transaction_KEY
LEFT JOIN
dbo.Vendor V ON VBT.vendor_KEY = V.vendor_KEY
LEFT JOIN
dbo.Payroll_Bank_Transaction PBT ON PBT.bank_transaction_KEY = BT.bank_transaction_KEY
LEFT JOIN
dbo.Employee E ON E.employee_KEY = PBT.employee_KEY