View: dbo.GL_Transaction_Detail__Payable_Payment_Application__View
View definition
-- SUMMARY
-- This view produces ledger entries from payable payment applications. Two balanced sets of ledger entries are
-- produced. The first, for the accrual accounting method, debits the payable's AP GL account and credits the payment's
-- one or more AP GL accounts. This journal entry is often a wash, since the payable and payment will likely have the
-- same AP GL account number assigned. The second, for the cash basis accounting method, debits the payable's expense
-- GL accounts and credits the payment's one or more AP GL accounts.
-- The columns produced by this view are:
-- ** gl_transaction_KEY, int, not null
-- The payable payment application'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_payment_application_payable_distribution_KEY, int, null
-- ** payable_payment_application_payment_distribution_KEY, int, null
-- The natural key for this view is (gl_transaction_KEY, payable_transaction_KEY,
-- payable_payment_application_payable_distribution_KEY, payable_payment_application_payment_distribution_KEY).
-- DROP VIEW dbo.GL_Transaction_Detail__Payable_Payment_Application__View
CREATE VIEW dbo.GL_Transaction_Detail__Payable_Payment_Application__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
, isnull(cast(le.amount as decimal(17,2)),0) as amount
-- Source keys
, le.payable_transaction_KEY
, le.payment__gl_transaction_KEY
, le.payable_payment_application_payable_distribution_KEY
, le.payable_payment_application_payment_distribution_KEY
-- Additional info
, le.application_date
, le.reference_number
, le.reference_number_sortable
, le.description
, le.source_was_a_distribution
, le.ledger_entry_type_KEY
from
(
-- This select statement yields the accrual ledger entries that debit the payable's GL account. This is usually the
-- AP GL account or some other wash account that holds the cash until payment is received.
select
ppa.gl_transaction_KEY
, 13 /*Payable Payment Application*/ as ledger_entry_source_KEY
, 1 /*Accrual*/ as gl_accounting_method_KEY
, 1 /*Debit*/ as gl_balance_effect_KEY
, pt.payable__gl_account_KEY as gl_account_KEY
, (ppa.paid_amount + ppa.discount_taken_amount) as amount
-- Source keys
, ppa.payable_transaction_KEY
, ppa.payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, ppa.application_date
, ppa.reference_number
, ppa.reference_number_sortable
, ppa.description
, cast(0 as bit) as source_was_a_distribution
, ledger_entry_type_KEY = 14 /*AP Payment Application AP Dr*/
from
dbo.Payable_Transaction as pt
inner join dbo.Payable_Payment_Application_Full_View as ppa
on ppa.payable_transaction_KEY = pt.payable_transaction_KEY
UNION ALL
-- This select statement yields the accrual ledger entries that credit the credit memo's GL account.
select
ppa.gl_transaction_KEY
, 13 /*Payable Payment Application*/ as ledger_entry_source_KEY
, 1 /*Accrual*/ as gl_accounting_method_KEY
, 2 /*Credit*/ as gl_balance_effect_KEY
, cm_pt.payable__gl_account_KEY as gl_account_KEY
, ppa.paid_amount as amount
-- Source keys
, ppa.payable_transaction_KEY
, ppa.payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, ppa.application_date
, ppa.reference_number
, ppa.reference_number_sortable
, ppa.description
, cast(0 as bit) as source_was_a_distribution
, ledger_entry_type_KEY = 13 /*AP Payment Application Credit Memo Cr*/
from
dbo.Payable_Payment_Application_Full_View as ppa
inner join dbo.GL_Transaction_N_Payable_Transaction as cm_gltnpt
on ppa.payment__gl_transaction_KEY = cm_gltnpt.gl_transaction_KEY
inner join dbo.Payable_Transaction as cm_pt
on cm_gltnpt.payable_transaction_KEY = cm_pt.payable_transaction_KEY
UNION ALL
-- This select statement yields ledger entries that credit the cash payment's distribution GL accounts. These are
-- usually one or more AP GL accounts or some other wash accounts that holds the cash until payment is recieved.
-- The generated ledger entries apply to both accrual and cash basis accounting methods.
select
ppa.gl_transaction_KEY
, 13 /*Payable Payment Application*/ as ledger_entry_source_KEY
, null /*Accrual and Cash Basis*/ as gl_accounting_method_KEY
, 2 /*Credit*/ as gl_balance_effect_KEY
, btd.gl_account_KEY
, ppapd.distributed__paid_amount as amount
-- Source keys
, ppa.payable_transaction_KEY
, ppa.payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, ppapd.payable_payment_application_payment_distribution_KEY
, ppa.application_date
, ppa.reference_number
, ppa.reference_number_sortable
, ppa.description
, cast(0 as bit) as source_was_a_distribution
, ledger_entry_type_KEY = 16 /*AP Payment Application Payment Distribution Cr*/
from
dbo.Payable_Payment_Application_Full_View as ppa
inner join dbo.Payable_Payment_Application_Payment_Distribution as ppapd
on ppapd.gl_transaction_KEY = ppa.gl_transaction_KEY
inner join dbo.Bank_Transaction_Distribution as btd
on btd.bank_transaction_distribution_KEY = ppapd.bank_transaction_distribution_KEY
UNION ALL
-- This select statement yields ledger entries that credit the payable's discount GL account for the amount of
-- discount taken with the payment application. The generated ledger entries apply only to the accrual accounting
-- method.
select
ppa.gl_transaction_KEY
, 13 /*Payable Payment Application*/ as ledger_entry_source_KEY
, case
when cm_gltnpt.gl_transaction_KEY is null -- A cash payment; not a credit memo.
then null /*Accrual and Cash Basis*/
else 1 /*Accrual*/
end as gl_accounting_method_KEY
, 2 /*Credit*/ as gl_balance_effect_KEY
, pt.ap_discount__gl_account_KEY as gl_account_KEY
, ppa.discount_taken_amount as amount
-- Source keys
, ppa.payable_transaction_KEY
, ppa.payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, ppa.application_date
, ppa.reference_number
, ppa.reference_number_sortable
, ppa.description
, cast(0 as bit) as source_was_a_distribution
, ledger_entry_type_KEY = 15 /*AP Payment Application Discount Taken Cr*/
from
dbo.Payable_Payment_Application_Full_View as ppa
inner join dbo.Payable_Transaction as pt
on ppa.payable_transaction_KEY = pt.payable_transaction_KEY
left join dbo.GL_Transaction_N_Payable_Transaction as cm_gltnpt
on ppa.payment__gl_transaction_KEY = cm_gltnpt.gl_transaction_KEY
where
pt.ap_discount__gl_account_KEY is not null
UNION ALL
-- This select statement yields the cash basis ledger entries that debit the payable's distribution GL accounts.
-- These are expense accounts that are being recognized as paid by the cash payment application.
select
ppa.gl_transaction_KEY
, 13 /*Payable Payment Application*/ as ledger_entry_source_KEY
, 2 /*Cash Basis*/ as gl_accounting_method_KEY
, 1 /*Debit*/ as gl_balance_effect_KEY
, ptd.gl_account_KEY
, (ppapd.distributed__paid_amount + ppapd.distributed__discount_taken_amount) as amount
-- Source keys
, ppa.payable_transaction_KEY
, ppa.payment__gl_transaction_KEY
, ppapd.payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, ppa.application_date
, ppa.reference_number
, ppa.reference_number_sortable
, ppa.description
, cast(1 as bit) as source_was_a_distribution
, ledger_entry_type_KEY = 17 /*AP Payment Application Payable Distribution Dr*/
from
dbo.Payable_Payment_Application_Full_View as ppa
inner join dbo.GL_Transaction_N_Bank_Transaction as gltnbt -- Include only cash payments; no Credit Memos
on ppa.payment__gl_transaction_KEY = gltnbt.gl_transaction_KEY
inner join dbo.Payable_Payment_Application_Payable_Distribution as ppapd
on ppapd.gl_transaction_KEY = ppa.gl_transaction_KEY
inner join dbo.Payable_Transaction_Distribution as ptd
on ptd.payable_transaction_distribution_KEY = ppapd.payable_transaction_distribution_KEY
) as le
inner join dbo.GL_Transaction as glt
on glt.gl_transaction_KEY = le.gl_transaction_KEY
;