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