View: dbo.Bank_Transaction_View | |||
View definition | |||
-- IMPORTANT NOTE: If the logic to calculate bank_transaction_displayable_status_KEY below changes, please make the same appropriate changes in -- Payroll_Funding_Transaction_View, Pay_Direct_Deposit_Allocation_View and Vendor_Check_Direct_Deposit_Allocation_View if needed. Bypassing -- Bank_Transaction_View in these three views was done for Direct Deposit/Funding performance enhancement (to get rid of redundant joins). CREATE VIEW dbo.Bank_Transaction_View AS SELECT BT.* , bank_transaction_displayable_status_KEY = ( CASE -- Live, Impound Memo WHEN BT.transaction_status_KEY in (1, 9) THEN CASE WHEN BT.bank_transaction_type_KEY IN (1,5,10) AND BT.is_handwritten_check = 0 THEN 50 -- Printed WHEN BT.bank_transaction_type_KEY IN (1,5,10) AND BT.is_handwritten_check = 1 THEN 51 -- Handwritten WHEN BT.bank_transaction_type_KEY IN (7,8) THEN 52 -- Historical WHEN BT.bank_transaction_type_KEY = 9 THEN 53 -- Third Party Sick Pay ELSE 1 -- Live END -- Deleted, Pending, Unprinted WHEN BT.transaction_status_KEY IN (2,4,5) THEN BT.transaction_status_KEY -- Voided, Impound Memo Voided WHEN BT.transaction_status_KEY in (3,15) THEN CASE WHEN BT.bank_transaction_type_KEY = 7 THEN 998 -- Invalid (cannot void a Historical Payment) WHEN BT.bank_transaction_type_KEY = 8 THEN 54 -- Historical - Voided WHEN BT.bank_transaction_type_KEY = 9 THEN 55 -- Third Party Sick Pay - Voided ELSE 3 -- Voided END -- exclude these four bank reconciliation transactions. -- Bank Reconciliation Initial Open Item (6), -- Bank Reconciliation Initial Open Item Deleted (7), -- Bank Reconciliation Adjustment Pending (8), -- Bank Reconciliation Bank Error (13) WHEN BT.transaction_status_KEY in (6, 7, 8, 13) THEN 998 -- Invalid -- (exclude these bank reconciliation only transactions) -- Payroll Check Template WHEN BT.transaction_status_KEY = 12 AND BT.bank_transaction_type_KEY = 5 THEN 56 -- Template -- Impound Memo Deleted WHEN BT.transaction_status_KEY = 14 THEN 2 -- Deleted ELSE 999 -- Unknown (an unknown transaction_status_KEY value was END -- encountered; code should fail when encoutering this value) ) -- The is_transfer column was created when we thought the Transfer_Bank_Transaction table would -- contain records for non-impound related transfers. We never did, so this is simplified to -- always return false. Someday we can clean this up more and remove the column. , is_transfer = 0 , does_not_affect_accounting = case -- Transactions that do not affect a client's account balances are: when BT.is_proxy_for_a_distribution = 1 or BT.bank_transaction_type_KEY not in (1,2,3,4,5,6,10,11,12) -- * a transaction that isn't a Check, Deposit, Payment, Adjustment, Payroll Check or Addition or AR Deposit, AP Check, AP Payment or BT.transaction_status_KEY not in (1,2,3,5,9,14,15) -- * a transaction with a status that isn't Live, Deleted, Voided, Unprinted, Impound Memo, Impound Memo Deleted, Impound Memo Voided or (BT.bank_transaction_type_KEY = 5 -- * a Payroll Check that came from a CSA client with and BT.data_origin_KEY = 2 -- the TBS or GL module marked and c.csa_client_had_tbs_or_gl_module_marked = 1) then 1 else 0 end FROM dbo.Bank_Transaction BT inner join dbo.Checkbook cbk on cbk.checkbook_KEY = BT.checkbook_KEY inner join dbo.Client c on cbk.client_KEY = c.client_KEY | |||