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