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
;