View: dbo.GL_Transaction_Detail__AR_Payment_Application__View | |||
View definition | |||
-- drop view dbo.GL_Transaction_Detail__AR_Payment_Application__View CREATE VIEW dbo.GL_Transaction_Detail__AR_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 , le.amount -- Source keys -- Additional info , le.application_date , le.customer_KEY , le.reference_number , le.reference_number_sortable , le.description , le.source_was_a_distribution , le.ledger_entry_type_KEY from ( -- For payments, debit the unearned revenue account for those applications that were not applied when the payment -- was created. Don't let applications that were applied when the payment was created hit the unearned revenue -- account since that portion of the payment was never actually unearned revenue because it started life already -- applied to one or more receivables! -- For credit memos, generate an accrual debit entry for the credit memo's AR account. select arpav.gl_transaction_KEY , 18 /* AR Payment Application */ as ledger_entry_source_KEY , case when arp.ar_payment_type_KEY is null -- Credit Memo or arp.ar_payment_type_KEY = 553 /* Write off */ then 1 /* Accrual */ else null /* Both accrual and cash basis */ end as gl_accounting_method_KEY , 1 /* Debit */ as gl_balance_effect_KEY , coalesce(arp.unearned_revenue__gl_account_KEY, cm_art.gl_account_KEY) as gl_account_KEY , arpav.paid_amount as amount -- Source keys ---- Additional info , arpav.application_date , arp.customer_KEY , arpav.reference_number , arpav.reference_number_sortable , arpav.description , cast(1 as bit) as source_was_a_distribution , ledger_entry_type_KEY = 23 /* AR Payment Application Credit Memo AR Dr */ from dbo.AR_Payment_Application_View as arpav left join dbo.AR_Payment as arp on arpav.payment__gl_transaction_KEY = arp.gl_transaction_KEY left join dbo.AR_Transaction as cm_art on arpav.payment__gl_transaction_KEY = cm_art.gl_transaction_KEY where arpav.was_applied_at_payment_creation = 0 -- Only create an unearned revenue entry if there is actually unearned revenue! or cm_art.gl_transaction_KEY is not null -- Credit memos create a debit to their AR account. UNION ALL -- Credit account receivables select arpav.gl_transaction_KEY , 18 /* AR Payment Application */ as ledger_entry_source_KEY , 1 /* Accrual */ as gl_accounting_method_KEY , 2 /* Credit */ as gl_balance_effect_KEY , art.gl_account_KEY , arpav.paid_amount + arpav.discount_honored_amount as amount -- Source keys -- Additional info , arpav.application_date , art.customer_KEY , arpav.reference_number , arpav.reference_number_sortable , arpav.description , cast(0 as bit) as source_was_a_distribution , ledger_entry_type_KEY = 24 /* AR Payment Application AR Cr */ from dbo.AR_Payment_Application_View as arpav inner join dbo.AR_Transaction as art on arpav.receivable__gl_transaction_KEY = art.gl_transaction_KEY UNION ALL -- Debit discount honored select arpav.gl_transaction_KEY , 18 /* AR Payment Application */ as ledger_entry_source_KEY , case when arp.ar_payment_type_KEY is null -- A credit memo payment or arp.ar_payment_type_KEY = 553 /* Write off */ then 1 /* Accrual */ else null /* Both accrual and cash basis */ end as gl_accounting_method_KEY , 1 /* Debit */ as gl_balance_effect_KEY , art.payment_discount__gl_account_KEY as gl_account_KEY , arpav.discount_honored_amount as amount -- Source keys -- Additional info , arpav.application_date , art.customer_KEY , arpav.reference_number , arpav.reference_number_sortable , arpav.description , cast(1 as bit) as source_was_a_distribution , ledger_entry_type_KEY = 25 /* AR Payment Application Discount Honored Dr */ from dbo.AR_Payment_Application_View as arpav inner join dbo.AR_Transaction as art on arpav.receivable__gl_transaction_KEY = art.gl_transaction_KEY left join dbo.AR_Payment as arp on arpav.payment__gl_transaction_KEY = arp.gl_transaction_KEY where arpav.discount_honored_amount <> 0 ) as le inner join dbo.GL_Transaction as glt on glt.gl_transaction_KEY = le.gl_transaction_KEY ; | |||