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