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