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