View: dbo.Etl_Helper_Ledger_Entry_Check_Summary_Memo_Cr_View | |||
View definition | |||
CREATE VIEW dbo.Etl_Helper_Ledger_Entry_Check_Summary_Memo_Cr_View AS SELECT -- Unique key nbe.gl_transaction_KEY, ledger_entry_type_KEY = 2, /* Check Summary Memo Cr */ -- Ledger entry payload cbk.client_KEY, glt.journal_KEY, glt.gl_period_KEY, gl_account_KEY = COALESCE(cbk.gl_account_KEY, cugla.gl_account_KEY), gl_accounting_method_KEY = NULL, gl_balance_effect_KEY = 2, /* Credit */ gl_transaction_type_KEY = glbev.gl_balancing_entry_type_KEY, transaction_status_KEY = 1, /* Live */ gl_transaction_date = glbev.transaction_date, gl_transaction_amount = glbev.amount, gl_transaction_description = glbev.description, gl_transaction_detail_description = glbev.description, gl_transaction_reference_number = glbev.reference_number, gl_transaction_reference_number_sortable = glbev.reference_number_sortable, -- Standard supplemental information gl_transaction_source_expression_KEY = CAST(6 AS TINYINT), -- GLTransactionSourceExpression.CheckSummaryMemo (WAS N'GLBalancingEntry') gl_transaction_source_KEY = 3, -- GLTransactionSource.BalancingEntry 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 bt.checkbook_KEY, nbe.gl_balancing_entry_KEY FROM dbo.GL_Transaction_N_GL_Balancing_Entry AS nbe INNER JOIN dbo.GL_Transaction AS glt ON glt.gl_transaction_KEY = nbe.gl_transaction_KEY INNER JOIN dbo.GL_Balancing_Entry_View AS glbev ON nbe.gl_balancing_entry_KEY = glbev.gl_balancing_entry_KEY INNER JOIN ( -- Choose one of the bank transactions that the Check Summary Memo is balancing. Use it to find the checkbook and -- client that the summary memo belongs to. There is a DAL constraint that guarantees all bank transactions -- associated with a given Check Summary Memo entry will belong to the same checkbook. SELECT gl_balancing_entry_KEY, MIN(bank_transaction_KEY) AS bank_transaction_KEY FROM dbo.GL_Balancing_Entry_N_Bank_Transaction GROUP BY gl_balancing_entry_KEY ) AS glbenbt ON nbe.gl_balancing_entry_KEY = glbenbt.gl_balancing_entry_KEY INNER JOIN dbo.Bank_Transaction AS bt ON glbenbt.bank_transaction_KEY = bt.bank_transaction_KEY INNER JOIN dbo.Checkbook AS cbk ON bt.checkbook_KEY = cbk.checkbook_KEY INNER JOIN dbo.Client_Undistributed_GL_Account AS cugla ON cbk.client_KEY = cugla.client_KEY LEFT JOIN ( SELECT DISTINCT bank_transaction_KEY FROM dbo.Payable_Transaction WHERE bank_transaction_KEY IS NOT NULL ) AS pt ON bt.bank_transaction_KEY = pt.bank_transaction_KEY WHERE (( bt.bank_transaction_type_KEY NOT IN (5 /*PayrollCheck*/, 8 /*HistoricalPayrollCheck*/, 9 /*ThirdPartySickPayPayrollCheck*/) AND bt.transaction_status_KEY NOT IN (9 /*ImpoundMemo*/, 14 /*ImpoundMemoDeleted*/, 15 /*ImpoundMemoVoided*/) AND pt.bank_transaction_KEY IS NULL AND bt.bank_transaction_KEY NOT IN (SELECT client__bank_transaction_KEY FROM dbo.Transfer_Bank_Transaction) AND bt.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 glbev.gl_balancing_entry_type_KEY = 301 -- Check summary memo ; | |||