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