View: dbo.Bank_Transaction_Running_Balance_View
View definition
-- A view that calculates running balance for bank transactions
CREATE VIEW dbo.Bank_Transaction_Running_Balance_View
AS
SELECT bt.bank_transaction_KEY,
bt.transaction_status_KEY,
bt.bank_transaction_type_KEY,
bt.data_origin_KEY,
c.csa_client_had_tbs_or_gl_module_marked,
cbk.checkbook_KEY,
bt.transaction_date,
bt.reference_number,
CASE
WHEN (bt.transaction_status_KEY = 1 -- Live
AND bt.bank_transaction_type_KEY in (1,3,10,11)) -- Check, Payment, AP Check, AP Payment
OR
(bt.transaction_status_KEY = 1 -- Live
AND bt.bank_transaction_type_KEY = 5 -- Payroll Check
AND (bt.data_origin_KEY <> 2 -- Didn't come from a CSA client with the TBS or GL module
OR c.csa_client_had_tbs_or_gl_module_marked = 0))
THEN -amount
ELSE amount
END AS amount
FROM dbo.Bank_Transaction AS bt
INNER JOIN dbo.Checkbook AS cbk
ON cbk.checkbook_KEY = bt.checkbook_KEY
INNER JOIN dbo.Client AS c
ON cbk.client_KEY = c.client_KEY
WHERE EXISTS (SELECT 1
FROM dbo.GL_Transaction_N_Bank_Transaction AS gltx
WHERE bt.bank_transaction_KEY = gltx.bank_transaction_KEY);