View: dbo.GL_Transaction_Detail__Payable_Transaction__View | |||
View definition | |||
-- SUMMARY -- This view produces ledger entries from payable transactions. A single balanced set of ledger entries are produced -- that debit the payable transaction's expense accounts and credit the payable transaction's AP GL account. If the -- transaction is of type Payable Liablity, then these ledger entries are for the accrual accounting method only. If -- the transaction is of type Credit Memo, then the ledger entries are marked for use by both the accrual accounting -- method and the cash basis accounting method. -- The columns produced by this view are: -- ** gl_transaction_KEY, int, not null -- The payable transaction's GL transaction key. -- ** ledger_entry_source_KEY, int, not null -- The kind of transaction that produced the ledger entry. -- ** gl_accounting_method_KEY, int, null -- Whether the ledger entry is used only by the Accrual accounting method or the Cash Basis accounting method. If -- the column is null, then the ledger entry is used by *both* accounting methods. -- ** gl_balance_effect_KEY, int, not null -- Whether the ledger entry is a debit or a credit. -- ** gl_period_KEY -- The period when the ledger entry posts. -- ** gl_journal_KEY -- The journal the ledger entry is included in. -- ** gl_account_KEY, int, null -- The GL account number being credited or debited. -- ** amount, decimal(17,2), not null -- The amount that the GL account is being credit or debited. -- ** payable_transaction_KEY, int, not null -- ** payable_transaction_distribution_KEY, int, null -- The natural key for this view is (gl_transaction_KEY, payable_transaction_KEY, payable_transaction_distribution_KEY). -- DROP VIEW dbo.GL_Transaction_Detail__Payable_Transaction__View CREATE VIEW dbo.GL_Transaction_Detail__Payable_Transaction__View AS select le.gl_transaction_KEY , le.ledger_entry_source_KEY , le.gl_accounting_method_KEY , le.gl_balance_effect_KEY , glt.gl_period_KEY , glt.journal_KEY , le.gl_account_KEY , le.amount -- Source keys , le.payable_transaction_KEY , le.payable_transaction_distribution_KEY -- Additional info , le.transaction_date , le.description , le.reference_number , le.reference_number_sortable , le.payable_transaction_type_KEY , le.vendor_KEY , le.source_was_a_distribution , le.sequence_number , le.ledger_entry_type_KEY from ( -- Grab the credit side (debit side for a Credit Memo) of the journal entry from the payable transaction's GL -- account. select gltnpt.gl_transaction_KEY , 11 /*Payable Transaction*/ as ledger_entry_source_KEY , 1 /*Accrual*/ as gl_accounting_method_KEY , case pt.payable_transaction_type_KEY when 204 /*Payable Liability*/ then 2 /*Credit*/ when 205 /*Credit Memo*/ then 1 /*Debit*/ end as gl_balance_effect_KEY , pt.payable__gl_account_KEY as gl_account_KEY , pt.amount -- Source keys , pt.payable_transaction_KEY , null as payable_transaction_distribution_KEY -- Additional info , pt.transaction_date , null as description , pt.reference_number , pt.reference_number_sortable , pt.payable_transaction_type_KEY , pt.vendor_KEY , cast(0 as bit) as source_was_a_distribution , 0 as sequence_number , ledger_entry_type_KEY = 11 /*Payable Transaction AP Cr Dr*/ from dbo.Payable_Transaction as pt inner join dbo.GL_Transaction_N_Payable_Transaction as gltnpt on gltnpt.payable_transaction_KEY = pt.payable_transaction_KEY left join dbo.Vendor_AP_Beginning_Balance as hist on hist.payable_transaction_KEY = pt.payable_transaction_KEY where pt.payable_transaction_type_KEY in (204, 205) -- Payable Liability, Credit Memo and pt.transaction_status_KEY in (10, 11) -- Open, Paid and hist.payable_transaction_KEY is null -- Historical payables are not posted. UNION ALL -- Grab the debit side (credit side of a Credit Memo) of the journal entry from the payable transaction's -- distributions. select gltnpt.gl_transaction_KEY , 12 /*Payable Transaction Distribution*/ as ledger_entry_source_KEY , 1 /*Accrual*/ as gl_accounting_method_KEY , case pt.payable_transaction_type_KEY when 204 /*Payable Liability*/ then 1 /*Debit*/ when 205 /*Credit Memo*/ then 2 /*Credit*/ end as gl_balance_effect_KEY , ptd.gl_account_KEY as gl_account_KEY , ptd.amount -- Source keys , pt.payable_transaction_KEY , ptd.payable_transaction_distribution_KEY -- Additional info , pt.transaction_date , ptd.description , pt.reference_number , pt.reference_number_sortable , pt.payable_transaction_type_KEY , pt.vendor_KEY , cast(1 as bit) as source_was_a_distribution , ptd.sequence_number , ledger_entry_type_KEY = 12 /*Payable Transaction Distribution Cr Dr*/ from dbo.Payable_Transaction as pt inner join dbo.GL_Transaction_N_Payable_Transaction as gltnpt on gltnpt.payable_transaction_KEY = pt.payable_transaction_KEY inner join dbo.Payable_Transaction_Distribution as ptd on ptd.payable_transaction_KEY = pt.payable_transaction_KEY left join dbo.Vendor_AP_Beginning_Balance as hist on hist.payable_transaction_KEY = pt.payable_transaction_KEY where pt.payable_transaction_type_KEY in (204, 205) -- Payable Liability, Credit Memo and pt.transaction_status_KEY in (10, 11) -- Open, Paid and hist.payable_transaction_KEY is null -- Historical payables are not posted. ) as le inner join dbo.GL_Transaction as glt on glt.gl_transaction_KEY = le.gl_transaction_KEY ; | |||