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