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 */
;