View: dbo.GL_Transaction_Detail__Bank_Transaction_Distribution__View | |||
View definition | |||
-- SUMMARY -- This view produces ledger entries against bank transaction distribution GL accounts. CREATE VIEW dbo.GL_Transaction_Detail__Bank_Transaction_Distribution__View AS select NULL as auto_reversing , btd.bank_transaction_distribution_KEY , NULL as payroll_check_payroll_item_distribution_KEY , NULL as payroll_check_tax_item_distribution_KEY , NULL as payroll_bank_transaction_worker_compensation_item_distribution_KEY , btd.bank_transaction_KEY , bt.checkbook_KEY , cbk.client_KEY , N'' COLLATE Latin1_General_CI_AS as distribution_workpaper_reference , btd.first__w2_1099_box_type_KEY , btd.gl_account_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 as gl_balance_effect_KEY , NULL as gl_balancing_entry_KEY , glt.gl_period_KEY , btd.amount as gl_transaction_amount , bt.transaction_date as gl_transaction_date , bt.description as gl_transaction_description , COALESCE(btd.description, gla.description, N'') as gl_transaction_detail_description , glt.gl_transaction_KEY , bt.reference_number as gl_transaction_reference_number , bt.reference_number_sortable as gl_transaction_reference_number_sortable , CAST(8 AS TINYINT) AS gl_transaction_source_expression_KEY -- GLTransactionSourceExpression.BankTransactionDistribution , 2 as gl_transaction_source_KEY -- GLTransactionSource.BankTransaction , bt.bank_transaction_type_KEY as gl_transaction_type_KEY , 0 as is_activity_journal_entry , NULL as journal_entry_distribution_KEY , NULL as journal_entry_KEY , NULL as journal_entry_misstatement_type_KEY , 101 /* Regular */ as journal_entry_type_KEY , glt.journal_KEY , NULL as schedule_m3_tax_code_attribute_KEY , btd.second__w2_1099_box_type_KEY , 1 as source_was_a_distribution , bt.transaction_status_KEY , vbt.vendor_KEY , N'' COLLATE Latin1_General_CI_AS as workpaper_reference , btd.sequence_number 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 */, 9 /* ImpoundMemo */, 14 /*ImpoundMemoDeleted*/, 15 /*ImpoundMemoVoided*/) -- 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) ) ; | |||