View: dbo.Etl_Helper_Ledger_Entry_Deposit_Summary_Memo_Dr_View | |||
View definition | |||
CREATE VIEW dbo.Etl_Helper_Ledger_Entry_Deposit_Summary_Memo_Dr_View AS SELECT -- Unique key nbe.gl_transaction_KEY, ledger_entry_type_KEY = 27, /* Deposit 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 = 1, /* Debit */ 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(16 AS TINYINT), /* GLTransactionSourceExpression.DepositSummaryMemo */ gl_transaction_source_KEY = 9, /* GLTransactionSource.DepositSummaryMemo */ 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 Deposit 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 Deposit 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 WHERE glbev.gl_balancing_entry_type_KEY = 302 /* Deposit summary memo */ ; | |||