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
;