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
;