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"))
;