View: dbo.Etl_Helper_Ledger_Entry_Bank_Transaction_Distribution_Cr_Dr_View | |||
View definition | |||
CREATE VIEW dbo.Etl_Helper_Ledger_Entry_Bank_Transaction_Distribution_Cr_Dr_View AS SELECT -- Unique key glt.gl_transaction_KEY, ledger_entry_type_KEY = 4, -- Bank Transaction Distribution Cr DrLedger_Entry_Payroll_Check_Payroll_Item_Expense_View btd.bank_transaction_distribution_KEY, -- Ledger entry payload cbk.client_KEY, glt.journal_KEY, glt.gl_period_KEY, btd.gl_account_KEY, gl_accounting_method_KEY = NULL, gl_balance_effect_KEY = CASE WHEN bt.bank_transaction_type_KEY IN (1 /* Check */, 3 /* Payment */, 10 /*AP Check*/, 11 /*AP Payment*/) THEN 1 /* Debit */ ELSE 2 /* Credit */ END, gl_transaction_type_KEY = bt.bank_transaction_type_KEY, bt.transaction_status_KEY, gl_transaction_date = bt.transaction_date, gl_transaction_amount = btd.amount, gl_transaction_description = bt.description, gl_transaction_detail_description = COALESCE(btd.description, gla.description, N''), gl_transaction_reference_number = bt.reference_number, gl_transaction_reference_number_sortable = bt.reference_number_sortable, -- Standard supplemental information gl_transaction_source_expression_KEY = CAST(8 AS TINYINT), -- GLTransactionSourceExpression.BankTransactionDistribution gl_transaction_source_KEY = 2, -- GLTransactionSource.BankTransaction is_activity_journal_entry = CAST(0 AS BIT), journal_entry_type_KEY = 101, -- Regular source_was_a_distribution = CAST(1 AS BIT), btd.sequence_number, workpaper_reference = N'' COLLATE Latin1_General_CI_AS, distribution_workpaper_reference = N'' COLLATE Latin1_General_CI_AS, -- Custom supplemental information bt.checkbook_KEY, vbt.vendor_KEY, btd.bank_transaction_KEY, btd.first__w2_1099_box_type_KEY, btd.second__w2_1099_box_type_KEY FROM dbo.Bank_Transaction_Distribution AS btd INNER JOIN dbo.Bank_Transaction AS bt ON btd.bank_transaction_KEY = bt.bank_transaction_KEY INNER JOIN dbo.Checkbook AS cbk ON bt.checkbook_KEY = cbk.checkbook_KEY INNER JOIN dbo.GL_Transaction_N_Bank_Transaction AS nbt ON btd.bank_transaction_KEY = nbt.bank_transaction_KEY INNER JOIN dbo.GL_Transaction AS glt ON nbt.gl_transaction_KEY = glt.gl_transaction_KEY LEFT JOIN dbo.Vendor_Bank_Transaction AS vbt ON btd.bank_transaction_KEY = vbt.bank_transaction_KEY LEFT JOIN dbo.GL_Account AS gla ON gla.gl_account_KEY = btd.gl_account_KEY -- The following 3 logically equivalent mechanisms were considered for gathering the following information: -- 1) OUTER APPLY -- This was chosen because, empirically, it performed best on the large database being tested with. Looking at -- the execution plan, this mechanism forced each of the three tables to be processed independently, in -- parallel. Since the contents of the tables being searched was large, this approach performed better than a -- strategy that first cancatenated the bank transaction keys from the three tables. -- 2) LEFT JOIN -- This performed as well as the OUTER APPLY. It probably results in the same execution plan; I didn''t check. -- 3) btd.bank_transaction_KEY NOT IN (subquery) -- This performed markedly slower. Analysis of the execution plan revealed that the bank transaction keys from -- the 3 tables were concatenated together then the concatenated list was searched. These tables contained a -- large number of rows in the database used for testing and the resulting concatenated list didn''t not have an -- index to aid in searching it. These factors made this process slow. OUTER APPLY ( SELECT bank_transaction_KEY FROM dbo.Payable_Transaction WHERE bank_transaction_KEY = btd.bank_transaction_KEY GROUP BY bank_transaction_KEY ) AS pt OUTER APPLY ( SELECT client__bank_transaction_KEY FROM dbo.Transfer_Bank_Transaction WHERE client__bank_transaction_KEY = btd.bank_transaction_KEY GROUP BY client__bank_transaction_KEY ) AS tbt OUTER APPLY ( SELECT bank_transaction_KEY FROM dbo.Manual_Funding_Bank_Transaction WHERE bank_transaction_KEY = btd.bank_transaction_KEY GROUP BY bank_transaction_KEY ) AS mfbt WHERE bt.bank_transaction_type_KEY IN (1,2,3,4,6,10,11,12) AND ( bt.transaction_status_KEY IN ( 1 /* Live */, 5 /* Unprinted */, 3 /* Voided */, 14 /*ImpoundMemoDeleted*/, 15 /*ImpoundMemoVoided*/) OR ( bt.transaction_status_KEY = 9 /* ImpoundMemo */ -- Check if the clients "Do not post payroll transactions" is checked or not. AND EXISTS( SELECT 1 FROM dbo.Client_GL_Setup AS cgls WHERE cgls.client_KEY = cbk.client_KEY AND cgls.post_payroll_transactions = 1 ) ) ) -- Proxy bank transactions do not affect accounting. AND bt.is_proxy_for_a_distribution = 0 AND ( -- Bank transaction must not be payroll related OR it must not be associated with a non-posting GL period. ( pt.bank_transaction_KEY IS NULL AND tbt.client__bank_transaction_KEY IS NULL AND mfbt.bank_transaction_KEY IS NULL ) OR glt.gl_period_KEY NOT IN (SELECT gl_period_KEY FROM dbo.Non_Posting_Payroll_Transaction_GL_Period) ) ; | |||