View: dbo.Etl_Helper_Ledger_Entry_Bank_Transaction_Balancing_Cr_Dr_With_Partial_Impound_Bugfix_View
View definition
`CREATE VIEW dbo.Etl_Helper_Ledger_Entry_Bank_Transaction_Balancing_Cr_Dr_With_Partial_Impound_Bugfix_View AS SELECT -- Unique key nbt.gl_transaction_KEY, ledger_entry_type_KEY = 3, /* Bank Transaction Balancing Cr Dr */ -- Ledger entry payload cbk.client_KEY, glt.journal_KEY, glt.gl_period_KEY, gl_account_KEY = CASE WHEN btv.transaction_status_KEY IN (9 /* Impound Memo */, 14 /* Impound Memo Deleted */, 15 /* Impound Memo Voided */) THEN COALESCE(cbk.impounding__gl_account_KEY, CUGA.gl_account_KEY) ELSE COALESCE(cbk.gl_account_KEY, CUGA.gl_account_KEY) END, gl_accounting_method_KEY = NULL, gl_balance_effect_KEY = CASE WHEN btv.bank_transaction_type_KEY IN (1 /* Check */, 3 /* Payment */, 5 /* PayrollCheck */, 10 /* AP Check */, 11 /* AP Payment */) THEN 2 /* Credit */ ELSE 1 /* Debit */ END, gl_transaction_type_KEY = btv.bank_transaction_type_KEY, transaction_status_KEY = btv.transaction_status_KEY, gl_transaction_date = btv.transaction_date, gl_transaction_amount = CASE --If we are funding only net checks then remove the direct deposit total from the --full transaction amount stored with the impound memo. The direct deposit portion will --be split out separately into a live client side transaction that is separate from this impound memo. --Impound memos store the entire transaction amount even though it is possible only part of --the transaction is funded. It was designed this way long before partial impounding was a thing, and --partial impounding did not change that area of the system. --If we have found ddbt.fund_payroll_transaction_type_KEY to equal 3 then it does not appear a NULL is actually possible here, --but using ISNULL just in case there is some data missing unexpectedly. WHEN ddbt.fund_payroll_transaction_type_KEY = 3 /* Net checks only */ AND btv.transaction_status_KEY = 9 /* Impound Memo */ THEN btv.amount - ISNULL ( ( SELECT SUM(ddbta.amount) FROM dbo.Direct_Deposit_Bank_Transaction_Allocation AS ddbta WHERE ddbta.bank_transaction_KEY = btv.bank_transaction_KEY GROUP BY ddbta.bank_transaction_KEY ) , CAST(0 AS DECIMAL(17,2)) ) --If we are funding only direct deposit then only use the direct deposit amount that is tied to the --full transaction amount stored with the impound memo. In such a case the direct deposit amount --is part of the impound memo amount, but only the direct deposit portion of it. Impound memos store the entire --transaction amount even though it is possible only part of the transaction is funded. --It was designed this way long before partial impounding was a thing, and --partial impounding did not change that area of the system. --If we have found ddbt.fund_payroll_transaction_type_KEY to equal 2 then it does not appear a NULL is actually possible here, --but using ISNULL just in case there is some data missing unexpectedly. WHEN ddbt.fund_payroll_transaction_type_KEY = 2 /* Direct deposit only */ AND btv.transaction_status_KEY = 9 /* Impound Memo */ THEN ISNULL ( ( SELECT SUM(ddbta.amount) FROM dbo.Direct_Deposit_Bank_Transaction_Allocation AS ddbta WHERE ddbta.bank_transaction_KEY = btv.bank_transaction_KEY GROUP BY ddbta.bank_transaction_KEY ) , CAST(0 AS DECIMAL(17,2)) ) --For all other cases (including funding the entire transaction) use the full bank transaction amount. ELSE btv.amount END, gl_transaction_description = btv.description, gl_transaction_detail_description = btv.description, gl_transaction_reference_number = btv.reference_number, gl_transaction_reference_number_sortable = btv.reference_number_sortable, -- Standard supplemental information gl_transaction_source_expression_KEY = CAST(15 AS TINYINT), -- GLTransactionSourceExpression.AutomaticGLBalancingEntry gl_transaction_source_KEY = 2 /* BankTransaction */, is_activity_journal_entry = CAST(0 AS BIT), journal_entry_type_KEY = 101 /* Regular */, source_was_a_distribution = CAST(0 AS BIT), sequence_number = 0, workpaper_reference = N'' COLLATE Latin1_General_CI_AS, distribution_workpaper_reference = N'' COLLATE Latin1_General_CI_AS, -- Custom supplemental information btv.checkbook_KEY, v.vendor_KEY, nbt.bank_transaction_KEY FROM dbo.GL_Transaction_N_Bank_Transaction AS nbt INNER JOIN dbo.GL_Transaction AS glt ON glt.gl_transaction_KEY = nbt.gl_transaction_KEY INNER JOIN dbo.Bank_Transaction_View AS btv ON nbt.bank_transaction_KEY = btv.bank_transaction_KEY INNER JOIN dbo.Checkbook AS cbk ON btv.checkbook_KEY = cbk.checkbook_KEY INNER JOIN dbo.Client_Undistributed_GL_Account AS CUGA ON cbk.client_KEY = CUGA.client_KEY LEFT JOIN dbo.Vendor_Bank_Transaction AS v ON nbt.bank_transaction_KEY = v.bank_transaction_KEY LEFT JOIN dbo.Direct_Deposit_Bank_Transaction AS ddbt ON btv.bank_transaction_KEY = ddbt.bank_transaction_KEY LEFT JOIN ( SELECT DISTINCT bank_transaction_KEY FROM dbo.Payable_Transaction WHERE bank_transaction_KEY IS NOT NULL ) AS pt ON nbt.bank_transaction_KEY = pt.bank_transaction_KEY WHERE btv.transaction_status_KEY IN (1 /* Live */, 5 /* Unprinted */, 3 /* Voided */, 9 /* ImpoundMemo */, 14 /*ImpoundMemoDeleted*/, 15 /*ImpoundMemoVoided*/) AND btv.does_not_affect_accounting = 0 AND ( ( btv.bank_transaction_type_KEY NOT IN (5 /*PayrollCheck*/, 8 /*HistoricalPayrollCheck*/, 9 /*ThirdPartySickPayPayrollCheck*/) AND btv.transaction_status_KEY NOT IN (9 /*ImpoundMemo*/, 14 /*ImpoundMemoDeleted*/, 15 /*ImpoundMemoVoided*/) AND pt.bank_transaction_KEY IS NULL AND btv.bank_transaction_KEY NOT IN (SELECT client__bank_transaction_KEY FROM dbo.Transfer_Bank_Transaction) AND btv.bank_transaction_KEY NOT IN (SELECT bank_transaction_KEY FROM dbo.Manual_Funding_Bank_Transaction WHERE bank_transaction_KEY IS NOT NULL) ) OR glt.gl_period_KEY NOT IN (SELECT gl_period_KEY FROM dbo.Non_Posting_Payroll_Transaction_GL_Period) ) AND nbt.bank_transaction_KEY NOT IN (SELECT bank_transaction_KEY FROM dbo.GL_Balancing_Entry_N_Bank_Transaction) ;`