View: dbo.GL_Transaction_Detail__AR_Payment_Application_Distribution__View
View definition
-- drop view dbo.GL_Transaction_Detail__AR_Payment_Application_Distribution__View
CREATE VIEW dbo.GL_Transaction_Detail__AR_Payment_Application_Distribution__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
, le.ar_payment_application_distribution_KEY
, le.ar_transaction_item_KEY
, le.ar_transaction_item_gl_account_KEY
-- Additional info
, le.application_date
, le.reference_number
, le.reference_number_sortable
, le.description
, cast(1 as bit) as source_was_a_distribution
from
(
-- Credit each sales account.
-- Debit the cost of goods sold account.
-- Credit the inventory account.
select
arpa.gl_transaction_KEY
, 19 /* AR Payment Application Distribution */ as ledger_entry_source_KEY
, 2 /* Cash Basis */ as gl_accounting_method_KEY
, case when artigla.ar_transaction_item_gl_account_type_KEY = 2 /* Cost of Goods Sold */
then 1 /* Debit */
else 2 /* Credit */
end as gl_balance_effect_KEY
, artigla.gl_account_KEY
, arpad.distributed__paid_amount + distributed__discount_honored_amount as amount
-- Source keys
, arpad.ar_payment_application_distribution_KEY
, artigla.ar_transaction_item_KEY
, artigla.ar_transaction_item_gl_account_KEY
-- Additional info
, arpa.application_date
, arpa.reference_number
, arpa.reference_number_sortable
, arpa.description
from
dbo.AR_Payment_Application_View as arpa
inner join dbo.AR_Payment_Application_Distribution as arpad
on arpa.gl_transaction_KEY = arpad.payment_application__gl_transaction_KEY
inner join dbo.AR_Transaction_Item_GL_Account as artigla
on arpad.ar_transaction_item_gl_account_KEY = artigla.ar_transaction_item_gl_account_KEY
left join dbo.AR_Payment as arp
on arpa.payment__gl_transaction_KEY = arp.gl_transaction_KEY
where
arp.ar_payment_type_KEY is not null and arp.ar_payment_type_KEY in (550, 551, 552) -- Check, Cash, Credit Card
) as le
inner join dbo.GL_Transaction as glt
on glt.gl_transaction_KEY = le.gl_transaction_KEY
;