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