View: dbo.GL_Transaction_For_Use_By_Enter_Transaction_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_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, _T2."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", _T1."gl_balancing_entry_KEY", _T28."gl_note_display", _T1."gl_period_KEY", 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.journal_entry_KEY, _T1."journal_KEY", _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", CAST(_T1.bank_transaction_sort_deposit as decimal(17,2)) as "sort_deposit", CAST(_T1.bank_transaction_sort_payment as decimal(17,2)) as "sort_payment", _T1."transaction_status_KEY" AS "transaction_status_KEY", _T1.bank_transaction_vendor_KEY AS "vendor_KEY" FROM ( SELECT glt.gl_transaction_KEY , glt.journal_KEY , glt.gl_period_KEY , glt.override_source_type_to_display_as_journal_entry , gl_transaction_source_KEY , gl_transaction_type_KEY , description , reference_number , transaction_date , bank_transaction_KEY , gl_balancing_entry_KEY , journal_entry_KEY , payable_transaction_KEY , reference_number_sortable , bank_transaction_displayable_status_KEY , glt.is_posting_period_overridden, gl_account_number_list, transaction_status_KEY, amount, does_not_affect_accounting, bank_transaction_transaction_status, --null for non bank transaction types bank_transaction_is_impound_checkbook, --null for non bank transaction types bank_transaction_bank_transaction_KEY, --null for non bank transaction types bank_transaction_checkbook_KEY, --null for non bank transaction types bank_transaction_deposit, --null for non bank transaction types bank_transaction_payment, --null for non bank transaction types bank_transaction_sort_deposit, --null for non bank transaction types bank_transaction_sort_payment, --null for non bank transaction types bank_transaction_id, --null for non bank transaction types bank_transaction_id_sortable, --null for non bank transaction types bank_transaction_memo, --null for non bank transaction types bank_transaction_vendor_KEY --null for non bank transaction types FROM dbo.GL_Transaction AS glt --------------------------------------------- bank transaction ------------------------------------ --------------------------------------------------------------------------------------------------- LEFT OUTER JOIN ( SELECT nbt.gl_transaction_KEY , 2 AS gl_transaction_source_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 , nbt.bank_transaction_KEY AS bank_transaction_KEY, NULL AS gl_balancing_entry_KEY , NULL AS journal_entry_KEY , NULL AS payable_transaction_KEY , 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 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.amount AS amount, bt.does_not_affect_accounting AS does_not_affect_accounting, bt.transaction_status_KEY AS bank_transaction_transaction_status, bt.is_impound_checkbook AS bank_transaction_is_impound_checkbook, 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.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 FROM dbo.GL_Transaction_N_Bank_Transaction AS nbt JOIN dbo.GL_Transaction_Bank_Transaction_Shared_View AS bt ON nbt.bank_transaction_KEY = bt.bank_transaction_KEY --------------------------------------------- balancing entry ------------------------------------ -------------------------------------------------------------------------------------------------- UNION ALL SELECT nbe.gl_transaction_KEY , CASE WHEN be.gl_balancing_entry_type_KEY = 301 /* GLBalancingEntryType.CheckSummaryMemo */ THEN 3 /* GLTransactionSource.CheckSummaryMemo */ WHEN be.gl_balancing_entry_type_KEY = 302 /* GLBalancingEntryType.DepositSummaryMemo */ THEN 9 /* GLTransactionSource.DepositSummaryMemo */ END AS gl_transaction_source_KEY, be.gl_balancing_entry_type_KEY AS gl_transaction_type_KEY, be.description , be.reference_number , be.transaction_date , ( SELECT MIN(bank_transaction_KEY) AS bank_transaction_KEY FROM dbo.GL_Balancing_Entry_N_Bank_Transaction WHERE gl_balancing_entry_KEY = be.gl_balancing_entry_KEY ) , nbe.gl_balancing_entry_KEY , NULL AS journal_entry_KEY, NULL AS payable_transaction_KEY, be.reference_number_sortable , NULL AS bank_transaction_displayable_status_KEY, ACNL.gl_account_number_list, 1 AS transaction_status_KEY, -- Live transaction status be.amount, 0 AS does_not_affect_accounting, NULL AS bank_transaction_transaction_status, NULL AS bank_transaction_is_impound_checkbook, NULL AS bank_transaction_bank_transaction_KEY, -- (only from bank transactions) NULL AS bank_transaction_checkbook_KEY, NULL AS bank_transaction_deposit, NULL AS bank_transaction_payment, NULL AS bank_transaction_sort_deposit, NULL AS bank_transaction_sort_payment, NULL AS bank_transaction_id, NULL AS bank_transaction_id_sortable, NULL AS bank_transaction_memo, NULL AS bank_transaction_vendor_KEY FROM dbo.GL_Transaction_N_GL_Balancing_Entry AS nbe JOIN ( SELECT gl_balancing_entry_KEY = glbe.gl_balancing_entry_KEY , reference_number = glbe.reference_number , transaction_date = glbe.transaction_date , amount = CAST(SUM(COALESCE(bt.amount,0)) AS decimal(17,2)) , description = glbe.description , gl_balancing_entry_type_KEY = glbe.gl_balancing_entry_type_KEY , reference_number_sortable = glbe.reference_number_sortable , creation_date = glbe.creation_date , staff_KEY = glbe.staff_KEY FROM dbo.GL_Balancing_Entry AS glbe left join dbo.GL_Balancing_Entry_N_Bank_Transaction AS blbenbt ON blbenbt.gl_balancing_entry_KEY = glbe.gl_balancing_entry_KEY left join ( SELECT bank_transaction_KEY, bank_transaction_type_KEY, amount FROM dbo.Bank_Transaction_View WHERE transaction_status_KEY = 1 -- Live AND does_not_affect_accounting = 0 -- Only include the portion that actually affects the GL balance. ) AS bt on bt.bank_transaction_KEY = blbenbt.bank_transaction_KEY GROUP BY glbe.gl_balancing_entry_KEY , glbe.reference_number , glbe.transaction_date , glbe.description , glbe.gl_balancing_entry_type_KEY , glbe.reference_number_sortable , glbe.creation_date , glbe.staff_KEY) AS be ON nbe.gl_balancing_entry_KEY = be.gl_balancing_entry_KEY LEFT OUTER JOIN ( select glbenbt.gl_balancing_entry_KEY , gl_account_number_list = isnull(gla.gl_account_number, cugla_gla.gl_account_number) from ( select gl_balancing_entry_KEY, min(bank_transaction_KEY) as bank_transaction_KEY from dbo.GL_Balancing_Entry_N_Bank_Transaction group by gl_balancing_entry_KEY ) as glbenbt INNER JOIN dbo.Bank_Transaction as bt on bt.bank_transaction_KEY = glbenbt.bank_transaction_KEY INNER JOIN dbo.Checkbook as cbk on bt.checkbook_KEY = cbk.checkbook_KEY INNER JOIN dbo.Client_Undistributed_GL_Account as cugla on cugla.client_KEY = cbk.client_KEY INNER JOIN dbo.GL_Account as cugla_gla on cugla_gla.gl_account_KEY = cugla.gl_account_KEY LEFT JOIN dbo.GL_Account as gla ON gla.gl_account_KEY = cbk.gl_account_KEY ) AS ACNL ON nbe.gl_balancing_entry_KEY = ACNL.gl_balancing_entry_KEY --------------------------------------------- journal entry ------------------------------------ -------------------------------------------------------------------------------------------------- UNION ALL SELECT nje.gl_transaction_KEY , 1 AS gl_transaction_source_KEY, je.journal_entry_type_KEY AS gl_transaction_type_KEY, je.description , je.reference_number, je.transaction_date, NULL AS bank_transaction_KEY, NULL AS gl_balancing_entry_KEY, nje.journal_entry_KEY, NULL AS payable_transaction_KEY, je.reference_number_sortable, NULL AS bank_transaction_displayable_status_KEY, --Matches with Bank_Transaction displayable transaction status key. JNL.gl_account_number_list, je.transaction_status_KEY, --Matches with Bank_Transaction transaction status key. JEA.amount, 0 AS does_not_affect_accounting, NULL AS bank_transaction_transaction_status, NULL AS bank_transaction_is_impound_checkbook, NULL AS bank_transaction_bank_transaction_KEY, NULL AS bank_transaction_checkbook_KEY, NULL AS bank_transaction_deposit, NULL AS bank_transaction_payment, NULL AS bank_transaction_sort_deposit, NULL AS bank_transaction_sort_payment, NULL AS bank_transaction_id, NULL AS bank_transaction_id_sortable, NULL AS bank_transaction_memo, NULL AS bank_transaction_vendor_KEY FROM dbo.GL_Transaction_N_Journal_Entry AS nje INNER JOIN dbo.Journal_Entry AS je ON nje.journal_entry_KEY = je.journal_entry_KEY LEFT JOIN (SELECT journal_entry_KEY = target.journal_entry_KEY, gl_account_number_list = REPLACE(RTRIM(( SELECT CAST(outerView.gl_account_number AS varchar(MAX)) + N' ' FROM dbo.Journal_Entry_Account_List_Detail_View AS outerView WHERE (outerView.journal_entry_KEY = target.journal_entry_KEY) AND NOT EXISTS ( SELECT 1 FROM dbo.Journal_Entry_Account_List_Detail_View AS innerView WHERE outerView.journal_entry_KEY = innerView.journal_entry_KEY AND outerView.sequence_number > innerView.sequence_number -- We cannot elimintate duplicates on sequence number, and are living with that ) ORDER BY sequence_number FOR XML PATH (N'') ) ) ,N' ' ,N', ') FROM dbo.Journal_Entry as target) AS JNL ON nje.journal_entry_KEY = JNL.journal_entry_KEY INNER JOIN (SELECT CAST(SUM(MJED."amount") AS decimal(17,2)) AS "amount", MJED."journal_entry_KEY" FROM ( SELECT JED."amount", JED."description", JED."gl_account_KEY", JED."is_subsidiary_client_summary", JED."journal_entry_distribution_KEY", JED."journal_entry_KEY", JED."sequence_number", JED."workpaper_reference" FROM dbo.Journal_Entry_Distribution AS JED WHERE (JED."amount" >= 0) ) AS MJED GROUP BY MJED."journal_entry_KEY") AS JEA ON je."journal_entry_KEY" = JEA."journal_entry_KEY" ) AS base ON glt.gl_transaction_KEY = base.gl_transaction_KEY ) AS _T1 ----------------------------------------- -------------- -------------------------------------- ----------------------------------------------------------------------------------------------- LEFT OUTER JOIN dbo.Journal AS _T2 ON _T1."journal_KEY" = _T2."journal_KEY" 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 /*DepositSummaryMemo*/)) AND ( (_T1."bank_transaction_transaction_status" IS NULL) OR (_T1."bank_transaction_transaction_status" 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"))) | |||