View: dbo.Etl_Helper_Ledger_Entry_Bank_Transaction_Balancing_Cr_Dr_View
View definition
CREATE VIEW dbo.Etl_Helper_Ledger_Entry_Bank_Transaction_Balancing_Cr_Dr_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 = btv.amount,
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
(
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)
;