View: dbo.GL_Transaction_Detail__Summary_Memo__View
View definition
-- SUMMARY
-- From a ledger entry viewpoint, a bank transaction consists of one or more distributions and a balancing entry against
-- a bank account's GL account number. Check type and deposit type bank transactions can be grouped together in check and
-- deposit summary memos respectively. These summary memos summarize the balancing entry of the associated check/deposit
-- into a single balancing entry against the bank account's GL account number. This view produces summarized balancing ledger entries.
CREATE VIEW dbo.GL_Transaction_Detail__Summary_Memo__View
AS
SELECT DISTINCT
NULL AS "auto_reversing",
NULL AS "bank_transaction_distribution_KEY",
NULL AS "bank_transaction_KEY",
_T006."checkbook_KEY",
_T007."client_KEY",
N'' COLLATE Latin1_General_CI_AS AS "distribution_workpaper_reference",
NULL AS "first__w2_1099_box_type_KEY",
coalesce(_T007.gl_account_KEY, CUGA.gl_account_KEY) as gl_account_KEY,
CASE
WHEN _T002."gl_balancing_entry_type_KEY" = 301 /* GLBalancingEntryType.CheckSummaryMemo */ THEN 2 /* Credit */
WHEN _T002."gl_balancing_entry_type_KEY" = 302 /* GLBalancingEntryType.DepositSummaryMemo */ THEN 1 /* Debit */
END AS "gl_balance_effect_KEY",
nbe.gl_balancing_entry_KEY,
_T001."gl_period_KEY",
_T002."amount" AS "gl_transaction_amount",
_T002."transaction_date" AS "gl_transaction_date",
_T002."description" AS "gl_transaction_description",
_T002."description" AS "gl_transaction_detail_description",
_T001."gl_transaction_KEY",
_T002."reference_number" AS "gl_transaction_reference_number",
_T002."reference_number_sortable" AS "gl_transaction_reference_number_sortable",
CAST(
CASE
WHEN _T002."gl_balancing_entry_type_KEY" = 301 /* GLBalancingEntryType.CheckSummaryMemo */ THEN 6 /* GLTransactionSourceExpression.CheckSummaryMemo */
WHEN _T002."gl_balancing_entry_type_KEY" = 302 /* GLBalancingEntryType.DepositSummaryMemo */ THEN 16 /* GLTransactionSourceExpression.DepositSummaryMemo */
END
AS TINYINT) AS gl_transaction_source_expression_KEY,
CASE
WHEN _T002."gl_balancing_entry_type_KEY" = 301 /* GLBalancingEntryType.CheckSummaryMemo */ THEN 3 /* GLTransactionSource.CheckSummaryMemo */
WHEN _T002."gl_balancing_entry_type_KEY" = 302 /* GLBalancingEntryType.DepositSummaryMemo */ THEN 9 /* GLTransactionSource.DepositSummaryMemo */
END as gl_transaction_source_KEY,
_T002.gl_balancing_entry_type_KEY as gl_transaction_type_KEY,
0 AS "is_activity_journal_entry",
NULL AS "journal_entry_distribution_KEY",
NULL AS "journal_entry_KEY",
NULL AS "journal_entry_misstatement_type_KEY",
101 /* Regular */ AS "journal_entry_type_KEY",
_T001."journal_KEY",
_T002."payroll_amount",
NULL AS "schedule_m3_tax_code_attribute_KEY",
NULL AS "second__w2_1099_box_type_KEY",
0 AS "source_was_a_distribution",
1 /* Live */ AS "transaction_status_KEY",
NULL AS "vendor_KEY",
N'' COLLATE Latin1_General_CI_AS AS "workpaper_reference"
FROM dbo.GL_Transaction AS _T001
inner join dbo.GL_Transaction_N_GL_Balancing_Entry as nbe on _T001.gl_transaction_KEY = nbe.gl_transaction_KEY
INNER JOIN dbo.GL_Balancing_Entry_View AS _T002 ON nbe.gl_balancing_entry_KEY = _T002."gl_balancing_entry_KEY"
INNER JOIN (SELECT MIN(_T004."bank_transaction_KEY") AS "_extension_1",
MIN(_T004."bank_transaction_KEY") AS "bank_transaction_KEY",
_T004."gl_balancing_entry_KEY"
FROM (SELECT _T003."bank_transaction_KEY",
_T003."gl_balancing_entry_KEY"
FROM dbo.GL_Balancing_Entry_N_Bank_Transaction AS _T003) AS _T004
GROUP BY _T004."gl_balancing_entry_KEY") AS _T005 ON nbe.gl_balancing_entry_KEY = _T005."gl_balancing_entry_KEY"
INNER JOIN dbo.Bank_Transaction AS _T006 ON _T005."bank_transaction_KEY" = _T006."bank_transaction_KEY"
INNER JOIN dbo.Checkbook AS _T007 ON _T006."checkbook_KEY" = _T007."checkbook_KEY"
JOIN dbo.Client_Undistributed_GL_Account as CUGA on _T007.client_KEY = CUGA.client_KEY
LEFT JOIN dbo.Payable_Transaction pt ON _T006.bank_transaction_KEY = pt.bank_transaction_KEY
LEFT JOIN dbo.Transfer_Bank_Transaction tbt ON _T006.bank_transaction_KEY = tbt.client__bank_transaction_KEY
LEFT JOIN dbo.Manual_Funding_Bank_Transaction mfbt ON _T006.bank_transaction_KEY = mfbt.bank_transaction_KEY
WHERE ((_T006.bank_transaction_type_KEY NOT IN (5 /*PayrollCheck*/, 8 /*HistoricalPayrollCheck*/, 9 /*ThirdPartySickPayPayrollCheck*/) AND
COALESCE(pt.payable_transaction_type_KEY,0) NOT IN (201 /*PayrollLiability*/, 202 /*BillingLiability*/, 203 /*PayrollTaxAdjustmentLiability*/) AND
tbt.client__bank_transaction_KEY IS NULL AND mfbt.bank_transaction_KEY IS NULL
AND _T006.transaction_status_KEY NOT IN (9 /*ImpoundMemo*/, 14 /*ImpoundMemoDeleted*/, 15 /*ImpoundMemoVoided*/))
OR _T001."gl_period_KEY" NOT IN (SELECT "gl_period_KEY"
FROM dbo."Non_Posting_Payroll_Transaction_GL_Period"))
AND _T002."gl_balancing_entry_type_KEY" IN (
301 /* GLBalancingEntryType.CheckSummaryMemo */
,302 /* GLBalancingEntryType.DepositSummaryMemo */
)
;