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