View: dbo.GL_Transaction_Detail__AR_Payment__View
View definition
-- drop view dbo.GL_Transaction_Detail__AR_Payment__View
CREATE VIEW dbo.GL_Transaction_Detail__AR_Payment__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.transaction_date
, le.reference_number
, le.reference_number_sortable
, le.customer_name
, le.ar_payment_type_KEY
, le.customer_KEY
, le.source_was_a_distribution
, le.ledger_entry_type_KEY
from
(
-- Debit the undeposited payment (or bad dept expense) account.
select
arp.gl_transaction_KEY
, 17 /* AR Payment */ as ledger_entry_source_KEY
, case when arp.ar_payment_type_KEY = 553 /* Write off */
then 1 /* Accrual */
else null /* Both Accrual & Cash Basis*/
end as gl_accounting_method_KEY
, 1 /* Debit */ as gl_balance_effect_KEY
, arp.undeposited_payment__gl_account_KEY as gl_account_KEY
, arp.amount
-- Source keys
-- Additional info
, arp.transaction_date
, arp.reference_number
, arp.reference_number_sortable
, arp.customer_name
, arp.ar_payment_type_KEY
, arp.customer_KEY
, cast(0 as bit) as source_was_a_distribution
, ledger_entry_type_KEY = 21 /* AR Payment Undeposited Payment Dr */
from
dbo.AR_Payment as arp
where
arp.transaction_status_KEY = 1 -- Live
UNION ALL
-- Credit the unearned revenue account for the amount of the payment that was not applied when the payment was
-- created. Don't let the portion of the payment that was applied when the payment was created hit the unearned
-- revenue account since it was never actually unearned revenue because it started life already applied to one or
-- more receivables!
select
arp.gl_transaction_KEY
, 17 /* AR Payment */ as ledger_entry_source_KEY
, case when arp.ar_payment_type_KEY = 553 /* Write off */
then 1 /* Accrual */
else null /* Both Accrual & Cash Basis*/
end as gl_accounting_method_KEY
, 2 /* Credit */ as gl_balance_effect_KEY
, arp.unearned_revenue__gl_account_KEY as gl_account_KEY
, arp.amount - isnull(applied_at_payment_creation_amount, 0) as amount
-- Source keys
-- Additional info
, arp.transaction_date
, arp.reference_number
, arp.reference_number_sortable
, arp.customer_name
, arp.ar_payment_type_KEY
, arp.customer_KEY
, cast(1 as bit) as source_was_a_distribution
, ledger_entry_type_KEY = 22 /* AR Payment Unearned Revenue Cr */
from
dbo.AR_Payment as arp
left join
(
select
payment__gl_transaction_KEY
, sum(paid_amount) as applied_at_payment_creation_amount
from
dbo.AR_Payment_Application_View
where
was_applied_at_payment_creation = 1
group by
payment__gl_transaction_KEY
) as arpa
on arp.gl_transaction_KEY = arpa.payment__gl_transaction_KEY
where
arp.transaction_status_KEY = 1 -- Live
and arp.amount - isnull(applied_at_payment_creation_amount, 0) <> 0 -- Only create an unearned revenue entry if there is actually unearned revenue!
) as le
inner join dbo.GL_Transaction as glt
on glt.gl_transaction_KEY = le.gl_transaction_KEY
;