View: dbo.GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_With_Created_Date_View
View definition
--The views GL_Transaction_View, GL_Transaction_For_Use_By_Enter_Transaction_View,
--GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_View and
--GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_View contain much of the
--same logic. Changes to any of these views may necessitate changes in the others.
--GL_Transaction_For_Use_By_Enter_Transaction_View provides only bank transaction,
--gl balancing entries and journal entries from GL_Transaction_View.
--GL_Transaction_For_Use_By_Enter_Transaction_View joins in additional information to these
--three types. These three types are a subset of the records returned by GL_Transaction_View.
--GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_View provides only bank transactions
--and can only be used when filtering by the checkbook_KEY.
--Bank transactions are a subset of records returned by GL_Transaction_For_Use_By_Enter_Transaction_View.
--GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_View can load data faster than
--GL_Transaction_For_Use_By_Enter_Transaction_View as it does not need to load data from the
--GL_Transaction table.
--GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_View has been forked from
--GL_Transaction_For_Use_By_Enter_Transaction_View
--GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_View is the same as
--GL_Transaction_For_Use_By_Enter_Transaction_View that was released with 2014.1.Q3.
--We needed to revert back to this view for enter transaction edit multiple to
--perform in a decent way. WI 573282 was written up for 2014.1.YE to use
--GL_Transaction_For_Use_By_Enter_Transaction_View for edit multiple as well as ET/EBAT
--context list transaction loading.
--NOTE: in the 2014 October release, using GL_Transaction_For_Use_By_Enter_Transaction_View
--for Enter Transactions journal mode context list loading introduced a serious performance
--regression for entering vendor checks. So, we changed Enter Transactions journal mode to use
--GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_View. (If the comments in the
--block above this block are correct, then in effect that is the same as using the 2014.1.Q3
--version of GL_Transaction_For_Use_By_Enter_Transaction_View.) So, if the work required for
--WI 573282 includes no longer using GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_View
--for journal mode context list loading, then that must be done in a manner which ensures that the
--vendor check entry performance regression does not reappear again.
--For WI 1528931, We have introduced two new views GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_With_Created_Date_View and
--GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_With_Created_Date_View which includes Created_Date logic.
--In WI 1621107, we are going to remove deprecated views GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_View and
--GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_View if no performance degradation noticed while using new views.
CREATE VIEW [dbo].[GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_With_Created_Date_View]
AS
SELECT
_T1.bank_transaction_displayable_status_KEY,
_T1.bank_transaction_bank_transaction_KEY AS bank_transaction_KEY,
_T1.bank_transaction_checkbook_KEY AS checkbook_KEY,
_T1.bank_transaction_client_KEY AS client_KEY,
_T1.bank_transaction_deposit AS deposit,
ISNULL(_T1."does_not_affect_accounting", 0) AS "does_not_affect_accounting",
ISNULL(_T1.gl_account_number_list , N'') AS "gl_account_number_list",
-- Substring gl_account_number_list upto 5000 characters to display it in grid, As grid support maximum 5000 chars
ISNULL(LEFT(_T1.gl_account_number_list, 5000) , N'') AS "gl_account_number_list_up_to_5000_chars",
_T28."gl_note_display",
CASE WHEN
(_T1."transaction_status_KEY" IN (2 /* Deleted */, 3 /* Voided */, 14 /* ImpoundMemoDeleted*/, 15 /* ImpoundMemoVoided */))
THEN
CAST(0.00 AS DECIMAL(17,2))
ELSE
_T1."amount"
END AS "gl_transaction_amount",
_T1.transaction_date AS "gl_transaction_date",
_T1.description AS "gl_transaction_description",
CAST(CASE WHEN (((_T1."transaction_status_KEY" IN (2 /* Deleted */, 3 /* Voided */, 14 /* ImpoundMemoDeleted*/, 15 /* ImpoundMemoVoided */))
)) THEN CAST(0.00 AS DECIMAL(17,2)) ELSE _T1."amount" END AS NVARCHAR(max)) AS "gl_transaction_display_amount",
_T1."gl_transaction_KEY",
_T1.reference_number AS "gl_transaction_reference_number",
_T1.reference_number_sortable AS "gl_transaction_reference_number_sortable",
_T1."gl_transaction_type_KEY",
_T1.bank_transaction_id AS "id",
_T1.bank_transaction_id_sortable AS "id_sortable",
_T1.bank_transaction_memo AS "memo",
_T1.bank_transaction_payment AS "payment",
ISNULL(_T1.transaction_date, CONVERT(DATETIME, '2079-06-06T00:00:00.000', 126)) AS "sort_date",
_T1.bank_transaction_sort_deposit AS "sort_deposit",
_T1.bank_transaction_sort_payment AS "sort_payment",
_T1."transaction_status_KEY" AS "transaction_status_KEY",
_T1.bank_transaction_vendor_KEY AS "vendor_KEY",
ISNULL(_T1.created_date, CAST(N'2079-06-06T00:00:00' /* Never expected to be reached; consisency with sort_date */ AS DATETIME)) AS created_date
FROM
(SELECT gl_transaction_KEY ,
gl_transaction_type_KEY ,
description ,
reference_number ,
transaction_date ,
reference_number_sortable ,
bank_transaction_displayable_status_KEY ,
gl_account_number_list,
transaction_status_KEY,
amount,
does_not_affect_accounting,
bank_transaction_bank_transaction_KEY,
bank_transaction_checkbook_KEY,
bank_transaction_deposit,
bank_transaction_payment,
bank_transaction_sort_deposit,
bank_transaction_sort_payment,
bank_transaction_client_KEY,
bank_transaction_is_impound_checkbook,
bank_transaction_id,
bank_transaction_id_sortable,
bank_transaction_memo,
bank_transaction_vendor_KEY,
created_date
FROM
--------------------------------------------- bank transaction ------------------------------------
---------------------------------------------------------------------------------------------------
(SELECT nbt.gl_transaction_KEY ,
bt.bank_transaction_type_KEY AS gl_transaction_type_KEY ,
bt.description AS description ,
bt.reference_number AS reference_number ,
bt.transaction_date AS transaction_date ,
bt.reference_number_sortable AS reference_number_sortable ,
bt.bank_transaction_displayable_status_KEY AS bank_transaction_displayable_status_KEY,
CASE
WHEN LEN(SUBSTRING(LTRIM(bt.btx_gl_account_number),1,1))>0
AND LEN(SUBSTRING(LTRIM(bt.dist_gl_account_number_list),1,1))>0
AND LTRIM(bt.btx_gl_account_number) <> LTRIM(bt.dist_gl_account_number_list) THEN
bt.btx_gl_account_number + N', ' + bt.dist_gl_account_number_list
WHEN LEN(SUBSTRING(LTRIM(bt.btx_gl_account_number),1,1))>0 THEN
bt.btx_gl_account_number
ELSE
bt.dist_gl_account_number_list
END AS gl_account_number_list,
bt.transaction_status_KEY AS transaction_status_KEY,
bt.is_impound_checkbook AS bank_transaction_is_impound_checkbook,
bt.amount AS amount,
bt.does_not_affect_accounting AS does_not_affect_accounting,
bt.bank_transaction_KEY AS bank_transaction_bank_transaction_KEY,
bt.checkbook_KEY AS bank_transaction_checkbook_KEY,
bt.deposit AS bank_transaction_deposit,
bt.payment AS bank_transaction_payment,
bt.sort_deposit AS bank_transaction_sort_deposit,
bt.sort_payment AS bank_transaction_sort_payment,
bt.client_KEY AS bank_transaction_client_KEY,
bt.id AS bank_transaction_id,
bt.id_sortable AS bank_transaction_id_sortable,
bt.memo AS bank_transaction_memo,
bt.vendor_KEY AS bank_transaction_vendor_KEY,
BTE.created_date
FROM
dbo.GL_Transaction_N_Bank_Transaction AS nbt
-- It is the case that a Bank_Transaction_Event entry
-- Exists for most entries in the Bank_Transaction Table.
-- A notable exception, which caused Bug # 2373464
-- is the case of a Payroll Check Template.
-- Previously to the addition of the creation_date,
-- such entries were excluded from appearing in this view
-- because their _T1.transaction_status_KEY would be 12 /* Template */.
--
-- Technically, what was a dire bug in the
-- GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_With_Created_Date_View
-- was benign here because the only cases where the INNER JOIN
-- versus LEFT OUTER JOIN became restrictive were targetted
-- to be filtered out by the WHERE clause on the overall view.
-- However, clarity and consistency are to be preferred over
-- things that happen to work due to a combination of arcane
-- factors, until and unless performance considerations force
-- the arcane route, and then it should be well-documented.
LEFT OUTER JOIN
(SELECT bank_transaction_KEY, event_date AS created_date
FROM Bank_Transaction_Event WHERE bank_transaction_event_type_KEY = 1) AS BTE ON nbt."bank_transaction_KEY" = BTE."bank_transaction_KEY"
JOIN
dbo.GL_Transaction_Bank_Transaction_Shared_View AS bt
ON
nbt.bank_transaction_KEY = bt.bank_transaction_KEY
) AS base
) AS _T1
----------------------------------------- -------------- --------------------------------------
-----------------------------------------------------------------------------------------------
LEFT OUTER JOIN
(SELECT CONVERT(bit, 1) AS "gl_note_display",
_T27."gl_transaction_KEY"
FROM
(SELECT _T26."gl_transaction_KEY"
FROM dbo.GL_Transaction_Note AS _T26
) AS _T27
GROUP BY _T27."gl_transaction_KEY"
) AS _T28 ON _T1."gl_transaction_KEY" = _T28."gl_transaction_KEY"
WHERE
(_T1."gl_transaction_type_KEY" IN (
1 /*Check*/,
2 /*Deposit*/,
3 /*Payment*/,
4 /*Adjustment*/,
5 /*PayrollCheck*/,
6 /*Addition*/,
10 /*ApCheck*/,
11 /*ApPayment*/,
12 /*Ar Deposit*/,
7 /*HistoricalPayment*/,
8 /*HistoricalPayrollCheck*/,
9 /*ThirdPartySickPayPayrollCheck*/,
101 /*JournalEntry*/,
103 /*AdjustingJournalEntry*/,
104 /*TaxAdjustmentJournalEntry*/,
105 /*ReclassifyingJournalEntry*/,
106 /*OtherJournalEntry*/,
107 /*PotentialJournalEntry*/,
301 /*CheckSummaryMemo*/,
302 /*DepositSummarMemo*/))
-- DAL Triggered action BankTransactionRequiresCreateAndCurrentStatusEvents exists to guarantee a created date when status key < 6, > 13 or = 9;
-- therefore, we do not need a test that _T6.created_date IS NOT NULL
--
-- A prior version of this view had code to permit the key tested on the next line to be NULL, but
-- that is statically impossible, and SSMS agrees. The type of the transaction_status_KEY on this
-- view is INT NOT NULL as of 2020-10-26
AND _T1."transaction_status_KEY" IN (1 /* Live */, 5 /* Unprinted */, 3 /* Voided */, 9 /* ImpoundMemo */, 14 /* ImpoundMemoDeleted*/, 15 /* ImpoundMemoVoided */)
AND ( (_T1."bank_transaction_is_impound_checkbook" IS NULL)
OR NOT (1 = (_T1."bank_transaction_is_impound_checkbook")));