View: dbo.GL_Transaction_For_Use_By_Enter_Transaction_Via_Journal_With_Created_Date_View | |||
View definition | |||
--The views GL_Transaction_View, GL_Transaction_For_Use_By_Enter_Transaction_View, and --GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_With_Created_Date_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. --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_With_Created_Date_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_With_Created_Date_View has been forked from --GL_Transaction_For_Use_By_Enter_Transaction_View --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. --NOTE: GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_View has been removed and --GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_With_Created_Date_View is used in its place. --Re-adding a previous version of view dbo.GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_With_Created_Date_View as --GL_Transaction_For_Use_By_Enter_Transaction_Via_Journal_With_Created_Date_View. Rather than create another view or try --to use the GLTransactionForUseByEnterTransaction with unknown performance implications we will revert to something --that worked for the Enter Transactions screen in Journal mode until enough time can be set aside to do more rigorous testing. CREATE VIEW dbo.GL_Transaction_For_Use_By_Enter_Transaction_Via_Journal_With_Created_Date_View AS SELECT _T6."bank_transaction_displayable_status_KEY", _T3."bank_transaction_KEY", _T32."checkbook_KEY", _T2."client_KEY", _T32."deposit", ISNULL(_T6."does_not_affect_accounting", 0) AS "does_not_affect_accounting", ISNULL(_T29."gl_account_number_list", ISNULL(_T30."gl_account_number_list", ISNULL(_T31."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(_T29."gl_account_number_list", 5000), ISNULL(LEFT(_T30."gl_account_number_list", 5000), ISNULL(LEFT(_T31."gl_account_number_list", 5000), N''))) AS "gl_account_number_list_up_to_5000_chars", _T13."gl_balancing_entry_KEY", _T28."gl_note_display", _T1."gl_period_KEY", CASE WHEN ( ISNULL(ISNULL(_T6."transaction_status_KEY", _T9."transaction_status_KEY"), _T15."gl_balancing_entry__transaction_status_KEY") IN (2 /* Deleted */, 3 /* Voided */, 14 /* ImpoundMemoDeleted*/, 15 /* ImpoundMemoVoided */) ) THEN CAST(0.00 AS DECIMAL) ELSE ISNULL(ISNULL(ISNULL(_T6."gl_transaction_amount", _T12."amount"), _T15."amount"), CAST(0.00 AS DECIMAL)) END AS "gl_transaction_amount", ISNULL(ISNULL(_T6."gl_transaction_date", _T9."transaction_date"), _T15."transaction_date") AS "gl_transaction_date", ISNULL(ISNULL(_T6."gl_transaction_description", _T9."description"), _T15."description") AS "gl_transaction_description", CAST(CASE WHEN (((ISNULL(ISNULL(_T6."transaction_status_KEY", _T9."transaction_status_KEY"), _T15."gl_balancing_entry__transaction_status_KEY") IN (2 /* Deleted */, 3 /* Voided */, 14 /* ImpoundMemoDeleted*/, 15 /* ImpoundMemoVoided */)) )) THEN 0.00 ELSE ISNULL(ISNULL(_T6."gl_transaction_amount", _T12."amount"), _T15."amount") END AS NVARCHAR(max)) AS "gl_transaction_display_amount", _T1."gl_transaction_KEY", ISNULL(ISNULL(ISNULL(_T6."gl_transaction_reference_number_sortable", _T9."reference_number_sortable"), _T15."reference_number_sortable"), N'') AS "gl_transaction_reference_number_sortable", ISNULL(ISNULL(ISNULL(_T6."gl_transaction_reference_number", _T9."reference_number"), _T15."reference_number"), N'') AS "gl_transaction_reference_number", _T1."gl_transaction_type_KEY", _T32."id", _T32."id_sortable", _T8."journal_entry_KEY", _T1."journal_KEY", _T32."memo", _T32."payment", ISNULL(ISNULL(ISNULL(_T6."gl_transaction_date", _T9."transaction_date"), _T15."transaction_date"), CONVERT(DATETIME, '2079-06-06T00:00:00.000', 126)) AS "sort_date", _T32."sort_deposit", _T32."sort_payment", ISNULL(ISNULL(_T6."transaction_status_KEY", _T9."transaction_status_KEY"), _T15."gl_balancing_entry__transaction_status_KEY") AS "transaction_status_KEY", _T7."vendor_KEY", ISNULL(ISNULL(ISNULL(_T6."created_date", JEE."created_date"), _T15."created_date"), CAST(N'2079-06-06T00:00:00' AS DATETIME /* Never expected to be reached; consisency with sort_date */)) AS "created_date" FROM dbo.GL_Transaction_View AS _T1 LEFT OUTER JOIN dbo.Journal AS _T2 ON _T1."journal_KEY" = _T2."journal_KEY" LEFT OUTER JOIN dbo.GL_Transaction_N_Bank_Transaction AS _T3 ON _T1."gl_transaction_KEY" = _T3."gl_transaction_KEY" LEFT OUTER JOIN (SELECT _T4."bank_transaction_displayable_status_KEY", _T4."bank_transaction_KEY", _T4."checkbook_KEY", _T4."does_not_affect_accounting", _T4."amount" AS "gl_transaction_amount", _T4."transaction_date" AS "gl_transaction_date", _T4."description" AS "gl_transaction_description", _T4."reference_number" AS "gl_transaction_reference_number", _T4."reference_number_sortable" AS "gl_transaction_reference_number_sortable", CASE WHEN (((_T5."fund_payroll_transaction" = 1) OR (_T5."fund_tax_agent_transaction" = 1) OR (_T5."fund_firm_vendor_transaction" = 1) OR (_T5."fund_payroll_agent_transaction" = 1))) THEN CONVERT(BIT, 1) ELSE CONVERT(BIT, 0) END AS "is_impound_checkbook", _T4."transaction_status_KEY", BTE.created_date FROM dbo.Bank_Transaction_View AS _T4 --This inner join is one of the critical differences between this view and the V3004_75 of --GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_With_Created_Date_View, that instead --uses a left outer join below. Please visit the other view for additional comments if needed. INNER JOIN (SELECT bank_transaction_KEY, event_date AS created_date FROM Bank_Transaction_Event WHERE bank_transaction_event_type_KEY = 1) AS BTE ON _T4."bank_transaction_KEY" = BTE."bank_transaction_KEY" INNER JOIN dbo.Checkbook_View AS _T5 ON _T4."checkbook_KEY" = _T5."checkbook_KEY") AS _T6 ON _T3."bank_transaction_KEY" = _T6."bank_transaction_KEY" LEFT OUTER JOIN dbo.Vendor_Bank_Transaction_View AS _T7 ON _T3."bank_transaction_KEY" = _T7."bank_transaction_KEY" LEFT OUTER JOIN dbo.GL_Transaction_N_Journal_Entry AS _T8 ON _T1."gl_transaction_KEY" = _T8."gl_transaction_KEY" LEFT OUTER JOIN dbo.Journal_Entry AS _T9 INNER JOIN (SELECT journal_entry_KEY, event_date AS created_date FROM Journal_Entry_Event WHERE journal_entry_event_type_KEY = 1) AS JEE ON _T9."journal_entry_KEY" = JEE."journal_entry_KEY" INNER JOIN (SELECT SUM(_T11."amount") AS "amount", _T11."journal_entry_KEY" FROM (SELECT _T10."amount", _T10."description", _T10."gl_account_KEY", _T10."is_subsidiary_client_summary", _T10."journal_entry_distribution_KEY", _T10."journal_entry_KEY", _T10."sequence_number", _T10."workpaper_reference" FROM dbo.Journal_Entry_Distribution AS _T10 WHERE (_T10."amount" >= 0)) AS _T11 GROUP BY _T11."journal_entry_KEY") AS _T12 ON _T9."journal_entry_KEY" = _T12."journal_entry_KEY" ON _T8."journal_entry_KEY" = _T9."journal_entry_KEY" AND _T2."client_KEY" = _T9."client_KEY" LEFT OUTER JOIN dbo.GL_Transaction_N_GL_Balancing_Entry AS _T13 ON _T1."gl_transaction_KEY" = _T13."gl_transaction_KEY" LEFT OUTER JOIN (SELECT _T14."amount", _T14."description", 1 /* Live */ AS "gl_balancing_entry__transaction_status_KEY", _T14."gl_balancing_entry_KEY", _T14."reference_number", _T14."reference_number_sortable", _T14."transaction_date", _T14.creation_date AS created_date FROM dbo.GL_Balancing_Entry_View AS _T14) AS _T15 ON _T13."gl_balancing_entry_KEY" = _T15."gl_balancing_entry_KEY" LEFT OUTER JOIN (SELECT COUNT(*) AS "_extension_1", SUM(CASE WHEN ((_T24."bank_reconciliation_aggregate_clear_status_KEY" = 1 /* Uncleared */)) THEN 1 ELSE 0 END) AS "_extension_2", SUM(CASE WHEN ((_T24."bank_reconciliation_aggregate_clear_status_KEY" = 2 /* Cleared */)) THEN 1 ELSE 0 END) AS "_extension_3", SUM(CASE WHEN ((_T24."bank_reconciliation_aggregate_clear_status_KEY" = 3 /* SelectedToClear */)) THEN 1 ELSE 0 END) AS "_extension_4", SUM(CASE WHEN ((_T24."bank_reconciliation_aggregate_clear_status_KEY" = 5 /* BankAccountReconciliationAdjustment */)) THEN 1 ELSE 0 END) AS "_extension_5", SUM(CASE WHEN ((_T24."bank_reconciliation_aggregate_clear_status_KEY" = 6 /* NotEligible */)) THEN 1 ELSE 0 END) AS "_extension_6", SUM(CASE WHEN ((_T24."bank_reconciliation_aggregate_clear_status_KEY" = 5 /* BankAccountReconciliationAdjustment */)) THEN 1 ELSE 0 END) AS "adjustment__detail_count", CASE WHEN ((SUM(CASE WHEN ((_T24."bank_reconciliation_aggregate_clear_status_KEY" = 6 /* NotEligible */)) THEN 1 ELSE 0 END) > 0)) THEN 6 /* NotEligible */ WHEN ((SUM(CASE WHEN ((_T24."bank_reconciliation_aggregate_clear_status_KEY" = 5 /* BankAccountReconciliationAdjustment */)) THEN 1 ELSE 0 END) > 0)) THEN 5 /* BankAccountReconciliationAdjustment */ WHEN ((SUM(CASE WHEN ((_T24."bank_reconciliation_aggregate_clear_status_KEY" = 3 /* SelectedToClear */)) THEN 1 ELSE 0 END) = COUNT(*))) THEN 3 /* SelectedToClear */ WHEN ((SUM(CASE WHEN ((_T24."bank_reconciliation_aggregate_clear_status_KEY" = 2 /* Cleared */)) THEN 1 ELSE 0 END) = COUNT(*))) THEN 2 /* Cleared */ WHEN ((SUM(CASE WHEN ((_T24."bank_reconciliation_aggregate_clear_status_KEY" = 1 /* Uncleared */)) THEN 1 ELSE 0 END) = COUNT(*))) THEN 1 /* Uncleared */ ELSE 4 /* PartiallyClearedOrSelectedToPartiallyClear */ END AS "bank_reconciliation_aggregate_clear_status_KEY", _T24."bank_transaction_KEY", SUM(CASE WHEN ((_T24."bank_reconciliation_aggregate_clear_status_KEY" = 2 /* Cleared */)) THEN 1 ELSE 0 END) AS "cleared__detail_count", COUNT(*) AS "detail_count", SUM(CASE WHEN ((_T24."bank_reconciliation_aggregate_clear_status_KEY" = 6 /* NotEligible */)) THEN 1 ELSE 0 END) AS "not_eligible__detail_count", SUM(CASE WHEN ((_T24."bank_reconciliation_aggregate_clear_status_KEY" = 3 /* SelectedToClear */)) THEN 1 ELSE 0 END) AS "selected_to_clear__detail_count", SUM(CASE WHEN ((_T24."bank_reconciliation_aggregate_clear_status_KEY" = 1 /* Uncleared */)) THEN 1 ELSE 0 END) AS "uncleared__detail_count" FROM (SELECT _T18."bank_reconciliation_adjustment_type_KEY", CASE WHEN ((_T17."bank_transaction_reconciliation_detail_KEY" IS NULL)) THEN 6 /* NotEligible */ WHEN (((_T18."bank_reconciliation_adjustment_type_KEY" IS NOT NULL) AND (_T18."bank_reconciliation_adjustment_type_KEY" <> 1 /* NotAnAdjustment */))) THEN 5 /* BankAccountReconciliationAdjustment */ WHEN ((_T17."bank_reconciliation_statement_KEY" IS NOT NULL)) THEN 2 /* Cleared */ WHEN (((_T19."bank_reconciliation_clear_status_KEY" IS NOT NULL) AND (_T19."bank_reconciliation_clear_status_KEY" = 2 /* Cleared */))) THEN 3 /* SelectedToClear */ ELSE 1 /* Uncleared */ END AS "bank_reconciliation_aggregate_clear_status_KEY", _T19."bank_reconciliation_clear_status_KEY", _T17."bank_reconciliation_statement_KEY", _T16."bank_transaction_KEY", _T17."bank_transaction_reconciliation_detail_KEY" FROM dbo.Bank_Transaction_View AS _T16 LEFT OUTER JOIN dbo.Bank_Transaction_Reconciliation_Detail AS _T17 ON _T16."bank_transaction_KEY" = _T17."bank_transaction_KEY" LEFT OUTER JOIN dbo.Bank_Reconciliation_Statement_Detail AS _T18 ON _T17."bank_transaction_reconciliation_detail_KEY" = _T18."bank_transaction_reconciliation_detail_KEY" AND _T17."bank_reconciliation_statement_KEY" = _T18."bank_reconciliation_statement_KEY" LEFT OUTER JOIN dbo.Bank_Reconciliation_Statement_Detail AS _T19 INNER JOIN (SELECT MAX(_T21."statement_index") AS "_extension_7", _T21."checkbook_KEY", MAX(_T21."statement_index") AS "statement_index" FROM (SELECT _T20."adjusted_period_balance_amount", _T20."adjustments_amount", _T20."bank_errors_amount", _T20."bank_reconciliation_statement_KEY", _T20."bank_reconciliation_status_KEY", _T20."beginning_statement_balance_amount", _T20."checkbook_KEY", _T20."checks_debits_reconciliation_analysis_difference_amount", _T20."checks_debits_reconciliation_analysis_total_amount", _T20."checks_other_debits_amount", _T20."current_period_financial_account_beginning_balance_amount", _T20."current_period_financial_account_credits_amount", _T20."current_period_financial_account_debits_amount", _T20."current_period_financial_account_ending_balance_amount", _T20."deposits_credits_reconciliation_analysis_difference_amount", _T20."deposits_credits_reconciliation_analysis_total_amount", _T20."deposits_other_credits_amount", _T20."ending_statement_balance_amount", _T20."ending_statement_balance_amount_override", _T20."financial_account_reconciled_amount", _T20."open_checks_other_debits_amount", _T20."open_deposits_other_credits_amount", _T20."prior_period_bank_reconciliation_ending_balance_amount", _T20."prior_statement_uncleared_checks_debits_amount", _T20."prior_statement_uncleared_deposits_credits_amount", _T20."statement_beginning_date", _T20."statement_ending_date", _T20."statement_file_name", _T20."statement_index", _T20."unreconciled_amount" FROM dbo.Bank_Reconciliation_Statement AS _T20) AS _T21 GROUP BY _T21."checkbook_KEY") AS _T22 INNER JOIN dbo.Bank_Reconciliation_Statement AS _T23 ON _T22."statement_index" = _T23."statement_index" AND _T22."checkbook_KEY" = _T23."checkbook_KEY" ON _T19."bank_reconciliation_statement_KEY" = _T23."bank_reconciliation_statement_KEY" ON _T17."bank_transaction_reconciliation_detail_KEY" = _T19."bank_transaction_reconciliation_detail_KEY") AS _T24 GROUP BY _T24."bank_transaction_KEY") AS _T25 ON _T3."bank_transaction_KEY" = _T25."bank_transaction_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" LEFT OUTER JOIN dbo.Bank_Transaction_Account_List_View AS _T29 ON _T3."bank_transaction_KEY" = _T29."bank_transaction_KEY" LEFT OUTER JOIN dbo.Journal_Entry_Account_List_View AS _T30 ON _T8."journal_entry_KEY" = _T30."journal_entry_KEY" LEFT OUTER JOIN dbo.GL_Balancing_Entry_Account_List_View AS _T31 ON _T13."gl_balancing_entry_KEY" = _T31."gl_balancing_entry_KEY" LEFT OUTER JOIN dbo.Bank_Transaction_List_View AS _T32 ON _T3."bank_transaction_KEY" = _T32."bank_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 ((_T6."transaction_status_KEY" IS NULL) OR (_T6."transaction_status_KEY" IN (1 /* Live */, 5 /* Unprinted */, 3 /* Voided */, 9 /* ImpoundMemo */, 14 /* ImpoundMemoDeleted*/, 15 /* ImpoundMemoVoided */))) AND ((_T6."is_impound_checkbook" IS NULL) OR NOT (1 = (_T6."is_impound_checkbook"))) AND ((_T3."bank_transaction_KEY" IS NOT NULL) OR (_T8."journal_entry_KEY" IS NOT NULL) OR (_T13."gl_balancing_entry_KEY" IS NOT NULL)); | |||