View: dbo.GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_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_Edit_Multiple_With_Created_Date_View AS SELECT -- When this is not a bank transaction according to gl_transaction_type_KEY and BankTransactionRules.IsCheck() -- code will use a status of 998 = Invalid. -- We discussed and decided to use 999 = Unknown in our failure case here. -- We want the view to have this column as NOT NULL. ISNULL(_T6."bank_transaction_displayable_status_KEY", 999 /* Unknown */) AS 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", _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", -- Make reference_number and reference_number_sortable come up as NOT NULL -- Program behavior treats null and empty always always identically. 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 -- 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 _T6.transaction_status_KEY would be 12 /* Template */. -- With the INNER JOIN to the event table, their _T6.Anything was NULL -- and they started appearing in this view, which caused problems. -- To avoid surfacing items that are bank transactions without a -- Bank Transaction creation_date, we will augment the overall -- WHERE clause. 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 _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 -- Unlike the Bank_Transaction_Event having a loophole which permits certain transaction_status values -- to exist without a Create type event, the JournalEntryEventConstraint.ScheduleConstraintTest -- has no such loophole. 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 -- Considered, but left for future work: -- This could be refactored to be the UNION of several SELECT -- instead of doing the LEFT OUTER JOIN in all cases. -- If we do that, we can potentially segment each of these -- gl_transaction_type_KEY values into each branch of the UNION. -- BankTransactionRules.IsCheck(GLTransactionType type) would -- be a useful first guide; it may have siblings. (_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) -- 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 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)); | |||