View: dbo.GL_Transaction_Detail__Bank_Transaction__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. This view produces bank transaction balancing ledger entries. If a bank -- transaction is included in a check summary memo, then a balancing entry is *not* produced for that bank transaction -- under the assumption that it will be included as part of the check summary memo (see the -- dbo.GL_Transaction_Check_Summary_Memo_Detail_View.sql script). -- DROP VIEW dbo.GL_Transaction_Detail__Bank_Transaction__View CREATE VIEW dbo.GL_Transaction_Detail__Bank_Transaction__View AS SELECT DISTINCT NULL AS "auto_reversing", NULL AS "bank_transaction_distribution_KEY", nbt.bank_transaction_KEY, _T002."checkbook_KEY", _T004."client_KEY", N'' COLLATE Latin1_General_CI_AS AS "distribution_workpaper_reference", NULL AS "first__w2_1099_box_type_KEY", case when _T002.transaction_status_KEY in (9, 14, 15) -- Impound Memo, Impound Memo Deleted, Impound Memo Voided then coalesce(_T004.impounding__gl_account_KEY, CUGA.gl_account_KEY) else coalesce(_T004.gl_account_KEY, CUGA.gl_account_KEY) end AS "gl_account_KEY", CASE WHEN ((_T002."bank_transaction_type_KEY" IN (1 /* Check */, 3 /* Payment */, 5 /* PayrollCheck */, 10 /*AP Check*/, 11 /*AP Payment*/))) THEN 2 /* Credit */ ELSE 1 /* Debit */ END AS "gl_balance_effect_KEY", _T003."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(15 AS TINYINT) AS gl_transaction_source_expression_KEY, -- GLTransactionSourceExpression.AutomaticGLBalancingEntry 2 as gl_transaction_source_KEY, -- GLTransactionSource.BankTransaction _T002.bank_transaction_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", NULL AS "schedule_m3_tax_code_attribute_KEY", NULL AS "second__w2_1099_box_type_KEY", 0 AS "source_was_a_distribution", _T002."transaction_status_KEY", _T005."vendor_KEY", N'' COLLATE Latin1_General_CI_AS AS "workpaper_reference" FROM dbo.GL_Transaction AS _T001 inner join dbo.GL_Transaction_N_Bank_Transaction AS nbt ON _T001.gl_transaction_KEY = nbt.gl_transaction_KEY INNER JOIN dbo.Bank_Transaction_View AS _T002 ON nbt.bank_transaction_KEY = _T002."bank_transaction_KEY" LEFT OUTER JOIN dbo.GL_Balancing_Entry_N_Bank_Transaction AS _T003 ON nbt.bank_transaction_KEY = _T003."bank_transaction_KEY" INNER JOIN dbo.Checkbook AS _T004 ON _T002."checkbook_KEY" = _T004."checkbook_KEY" LEFT OUTER JOIN dbo.Vendor_Bank_Transaction AS _T005 ON nbt.bank_transaction_KEY = _T005."bank_transaction_KEY" JOIN dbo.Client_Undistributed_GL_Account AS CUGA ON _T004.client_KEY = CUGA.client_KEY LEFT JOIN dbo.Payable_Transaction pt ON _T002.bank_transaction_KEY = pt.bank_transaction_KEY LEFT JOIN dbo.Transfer_Bank_Transaction tbt ON _T002.bank_transaction_KEY = tbt.client__bank_transaction_KEY LEFT JOIN dbo.Manual_Funding_Bank_Transaction mfbt ON _T002.bank_transaction_KEY = mfbt.bank_transaction_KEY WHERE _T002."transaction_status_KEY" IN (1 /* Live */, 5 /* Unprinted */, 3 /* Voided */, 9 /* ImpoundMemo */, 14 /*ImpoundMemoDeleted*/, 15 /*ImpoundMemoVoided*/) AND _T002.does_not_affect_accounting = 0 AND ((_T002.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 _T002.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")) ;`