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, -- An IX$gl_transaction_KEY index is already present on GL_Transaction_Note, ensuring optimal performance for the EXISTS subquery. CASE WHEN EXISTS (SELECT 1 FROM dbo.GL_Transaction_Note AS gtn WHERE gtn.gl_transaction_KEY = _T1.gl_transaction_KEY) THEN CONVERT(bit, 1) ELSE NULL END AS 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, _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 base.gl_transaction_KEY, base.gl_transaction_type_KEY, base.description, base.reference_number, base.transaction_date, base.reference_number_sortable, base.bank_transaction_displayable_status_KEY, base.gl_account_number_list, base.transaction_status_KEY, base.amount, base.does_not_affect_accounting, base.bank_transaction_bank_transaction_KEY, base.bank_transaction_checkbook_KEY, base.bank_transaction_deposit, base.bank_transaction_payment, base.bank_transaction_sort_deposit, base.bank_transaction_sort_payment, base.bank_transaction_client_KEY, base.bank_transaction_is_impound_checkbook, base.bank_transaction_id, base.bank_transaction_id_sortable, base.bank_transaction_memo, base.bank_transaction_vendor_KEY, base.created_date FROM --------------------------------------------- bank transaction ------------------------------------ --------------------------------------------------------------------------------------------------- (SELECT nbt.gl_transaction_KEY, bt.bank_transaction_type_KEY AS gl_transaction_type_KEY, bt.description, bt.reference_number, bt.transaction_date, bt.reference_number_sortable, bt.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, bt.is_impound_checkbook AS bank_transaction_is_impound_checkbook, bt.amount, bt.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 INNER JOIN dbo.GL_Transaction_Bank_Transaction_Shared_View AS bt ON nbt.bank_transaction_KEY = bt.bank_transaction_KEY ) AS base ) AS _T1 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)));