View: dbo.GL_Transaction_Bank_Transaction_Shared_View
View definition
--This view is used by both GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_View
--and GL_Transaction_For_Use_By_Enter_Transaction_View to supply
--the necessary bank transaction information.
CREATE VIEW [dbo].[GL_Transaction_Bank_Transaction_Shared_View]
AS
SELECT
NULLIF(BT.bank_transaction_KEY, -1) AS bank_transaction_KEY, --This needs to be forced to allow nulls so that the data pager that loads enter transaction gl transaction data allows nulls for journal entry items.
transaction_date = BT.transaction_date ,
bank_transaction_type_KEY = BT.bank_transaction_type_KEY ,
reference_number = BT.reference_number ,
description = BT.description ,
( 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
WHEN BT.transaction_status_KEY IN (
2, 4, 5 )
THEN BT.transaction_status_KEY
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
WHEN BT.transaction_status_KEY IN (
6, 7, 8, 13 )
THEN 998 -- Invalid
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)
) AS bank_transaction_displayable_status_KEY ,
BT.reference_number_sortable,
CASE -- Transactions that do not affect a client's account balances are:
--Using multiple WHEN clauses instead of ORs to reduce values
--checked before assigning a value
WHEN BT.is_proxy_for_a_distribution = 1 -- * a proxy for a bank transaction distribution or a journal entry distribution
THEN 1
WHEN 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
THEN 1
WHEN 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
THEN 1
WHEN (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 AS does_not_affect_accounting,
CASE WHEN BT.transaction_status_KEY in (9,14,15) -- Impound Memo, Impound Memo Deleted, Impound Memo Voided
THEN COALESCE(gla_impound.gl_account_number, gla_undistributed.gl_account_number)
ELSE COALESCE(gla_standard.gl_account_number, gla_undistributed.gl_account_number) END AS btx_gl_account_number,
STUFF
( RTRIM(
(
SELECT
N', ' + CAST(gla.gl_account_number AS varchar(MAX))
FROM
dbo.Bank_Transaction_Distribution AS dist
INNER JOIN dbo.GL_Account AS gla ON gla.gl_account_KEY = dist.gl_account_KEY
WHERE dist.bank_transaction_KEY = BT.bank_transaction_KEY and dist.gl_account_KEY is not null
ORDER BY sequence_number
FOR XML PATH (N'')
)
)
, 1, 2, N'') COLLATE Latin1_General_CI_AS AS dist_gl_account_number_list,
BT.transaction_status_KEY,
CAST(BT.amount AS decimal(17,2)) AS amount,
CASE WHEN (((CB."fund_payroll_transaction" = 1) OR (CB."fund_tax_agent_transaction" = 1) OR (CB."fund_firm_vendor_transaction" = 1) OR (CB."fund_payroll_agent_transaction" = 1))) THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END AS "is_impound_checkbook",
CB.checkbook_KEY,
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 AS deposit,
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 AS payment,
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 CAST(BT.amount AS DECIMAL(17,2))
END
ELSE CAST(-1000000000002 AS DECIMAL(17,2)) /*Payments*/
END AS sort_deposit,
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 CAST(-BT.amount AS DECIMAL(17,2))
ELSE CAST(BT.amount AS DECIMAL(17,2))
END
ELSE CAST(-1000000000002 AS DECIMAL(17,2)) /*Deposits*/
END as sort_payment,
c.client_KEY,
COALESCE(V.vendor_id COLLATE Latin1_General_CI_AS,
E.employee_id COLLATE Latin1_General_CI_AS, N'') AS id,
COALESCE(V.vendor_id_sortable COLLATE Latin1_General_CI_AS,
E.employee_id_sortable COLLATE Latin1_General_CI_AS, N'') AS id_sortable,
BT.memo,
VBT.vendor_KEY
FROM dbo.Bank_Transaction AS BT
INNER JOIN dbo.Checkbook AS CB ON BT."checkbook_KEY" = CB."checkbook_KEY"
INNER JOIN dbo.Client AS c ON CB.client_KEY = c.client_KEY
INNER JOIN dbo.Client_Undistributed_GL_Account AS undistributed ON undistributed.client_KEY = CB.client_KEY
INNER JOIN dbo.GL_Account AS gla_undistributed ON gla_undistributed.gl_account_KEY = undistributed.gl_account_KEY
LEFT OUTER JOIN dbo.GL_Account AS gla_impound ON gla_impound.gl_account_KEY = CB.impounding__gl_account_KEY
LEFT OUTER JOIN dbo.GL_Account AS gla_standard ON gla_standard.gl_account_KEY = CB.gl_account_KEY
LEFT OUTER JOIN dbo.Vendor_Bank_Transaction AS VBT ON VBT.bank_transaction_KEY = BT.bank_transaction_KEY
LEFT OUTER JOIN dbo.Vendor AS V ON VBT.vendor_KEY = V.vendor_KEY
LEFT OUTER JOIN dbo.Payroll_Bank_Transaction AS PBT ON PBT.bank_transaction_KEY = BT.bank_transaction_KEY
LEFT OUTER JOIN dbo.Employee AS E ON E.employee_KEY = PBT.employee_KEY
;