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