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