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