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