View: dbo.GL_Transaction_Detail__AR_Transaction__View
View definition
-- drop view dbo.GL_Transaction_Detail__AR_Transaction__View
CREATE VIEW dbo.GL_Transaction_Detail__AR_Transaction__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_transaction_item_KEY
, le.ar_transaction_item_gl_account_KEY
-- Additional info
, le.transaction_date
, le.reference_number
, le.reference_number_sortable
, le.ar_transaction_type_KEY
, le.customer_KEY
, le.source_was_a_distribution
, le.sequence_number
, le.ledger_entry_type_KEY
from
(
-- Debit the AR account.
select
artv.gl_transaction_KEY
, 15 /* AR Transaction */ as ledger_entry_source_KEY
, 1 /* Accrual */ as gl_accounting_method_KEY
, 1 /* Debit */ as gl_balance_effect_KEY
, artv.gl_account_KEY
, case when artv.ar_transaction_type_KEY = 502 /* Credit Memo */
then -artv.amount
else artv.amount end as amount
-- Source keys
, null as ar_transaction_item_KEY
, null as ar_transaction_item_gl_account_KEY
-- Additional info
, artv.transaction_date
, artv.reference_number
, artv.reference_number_sortable
, artv.ar_transaction_type_KEY
, artv.customer_KEY
, cast(0 as bit) as source_was_a_distribution
, 0 as sequence_number
, ledger_entry_type_KEY = 19 /* AR Transaction AR Dr */
from
dbo.AR_Transaction_View as artv
where
artv.transaction_status_KEY = 1 -- Live
-- Exempt beginning balance (historical) AR transactions.
and artv.gl_transaction_KEY not in (select gl_transaction_KEY from dbo.Customer_Historical_AR_Transaction)
UNION ALL
-- Credit each sales account.
-- Debit the cost of goods sold account.
-- Credit the inventory account.
select
art.gl_transaction_KEY
, 16 /* AR Transaction Item */ as ledger_entry_source_KEY
, 1 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
, case
when artigla.ar_transaction_item_gl_account_type_KEY = 4 /* Additional Tax */
then case when art.ar_transaction_type_KEY = 502 /* Credit Memo */then -arti.additional_tax_amount else arti.additional_tax_amount end
when artigla.ar_transaction_item_gl_account_type_KEY in (2, 3) /* Cost of Goods Sold, Inventory*/
then case when art.ar_transaction_type_KEY = 502 /* Credit Memo */then -arti.cost_amount else arti.cost_amount end
else case when art.ar_transaction_type_KEY = 502 /* Credit Memo */then -arti.amount else arti.amount end
end as amount
-- Source keys
, arti.ar_transaction_item_KEY
, artigla.ar_transaction_item_gl_account_KEY
-- Additional info
, art.transaction_date
, art.reference_number
, art.reference_number_sortable
, art.ar_transaction_type_KEY
, art.customer_KEY
, cast(1 as bit) as source_was_a_distribution
, arti.sequence_number
, ledger_entry_type_KEY = 20 /* AR Transaction Item Cr Dr */
from
dbo.AR_Transaction as art
inner join dbo.AR_Transaction_Item as arti
on art.gl_transaction_KEY = arti.gl_transaction_KEY
inner join dbo.AR_Transaction_Item_GL_Account as artigla
on arti.ar_transaction_item_KEY = artigla.ar_transaction_item_KEY
where
art.transaction_status_KEY = 1 -- Live
-- Exempt beginning balance (historical) AR transactions.
and art.gl_transaction_KEY not in (select gl_transaction_KEY from dbo.Customer_Historical_AR_Transaction)
) as le
inner join dbo.GL_Transaction as glt
on glt.gl_transaction_KEY = le.gl_transaction_KEY
;