select nvl(gl.short_name, gl.name) Ledger,
haou2.name Operating_Unit,
mtl_acct.organization_code Org_Code,
oap.period_name Period_Name,
&segment_columns
mtl_acct.item_number Item_Number,
mtl_acct.item_description Item_Description,
fcl.meaning Item_Type,
mtl_acct.inventory_item_status_code Item_Status,
-- Revision for version 1.4
ml1.meaning Make_Buy_Code,
-- Revision for version 1.1
&category_columns
ml2.meaning Accounting_Line_Type,
-- Comment this out to net the two lines to zero
-- mtl_acct.transaction_type_name Transaction_Type,
mtl_acct.transaction_source Transaction_Source,
mtl_acct.document_order_num Sales_Order_Number,
decode(mtl_acct.transaction_source,
'Internal order', mtl_acct.order_type,
'RMA',mtl_acct.order_type,
'Sales order', mtl_acct.order_type,
null) Order_Type,
mtl_acct.primary_uom_code UOM_Code,
-- Net the SO line to see if it nets to zero
sum(mtl_acct.primary_quantity) Net_Deferred_COGS_Quantity,
gl.currency_code Curr_Code,
sum(mtl_acct.mta_amount) Net_Deferred_COGS_Amount
from gl_code_combinations gcc,
-- Revision for version 1.4
mfg_lookups ml1,
mfg_lookups ml2,
fnd_common_lookups fcl,
hr_organization_information hoi,
hr_all_organization_units_vl haou, -- inv_organization_id
hr_all_organization_units_vl haou2, -- operating unit
gl_ledgers gl,
-- End revision for version 1.4
-- Revision for version 1.3
-- Remove subledger accounting tables and replace with oap
org_acct_periods oap,
-- ==========================================================================
-- Use this inline table to fetch the DCOGS material transactions
-- Do not group results, select by cost element id to avoid cross-joining quantities.
-- ==========================================================================
(
-- ===========================================
-- Now get the deferred COGS accounting entries
-- ===========================================
select mp.organization_code organization_code,
mp.organization_id organization_id,
mmt.acct_period_id acct_period_id,
mta.reference_account reference_account,
mta.inv_sub_ledger_id inv_sub_ledger_id,
msiv.concatenated_segments item_number,
msiv.description item_description,
-- Revision for version 1.4
msiv.inventory_item_status_code,
msiv.planning_make_buy_code,
msiv.item_type item_type,
-- For category SQL
msiv.inventory_item_id,
-- End revision for version 1.4
mta.accounting_line_type accounting_line_type,
mtt.transaction_type_name transaction_type_name,
mtst.transaction_source_type_name transaction_source,
Decode(mmt.transaction_source_type_id,
1, (select poh.segment1 -- Purchase Order
from po_headers_all poh
where poh.po_header_id = mmt.transaction_source_id),
2, (select ooh.order_number -- Sales order
from oe_order_headers_all ooh,
oe_order_lines_all ool
where ooh.header_id = ool.header_id
and ool.line_id = mmt.trx_source_line_id),
-- Revision for version 1.20, remove concatonated acct segments
-- 3, (select concatenated_segments -- Account
-- from gl_code_combinations_kfv gcc
-- where gcc.code_combination_id = mmt.distribution_account_id),
3, 'Account', -- Account
4, (select ooh.order_number -- Move order
from ont.oe_order_headers_all ooh,
ont.oe_order_lines_all ool
where ooh.header_id = ool.header_id
and ool.line_id = mmt.trx_source_line_id),
5, (select we.wip_entity_name -- Job or Schedule
from wip_entities we
where we.wip_entity_id = mmt.transaction_source_id),
6, (select mgd.segment1 -- Account alias
from mtl_generic_dispositions mgd
where mgd.disposition_id = mmt.transaction_source_id),
7, (select prh.segment1 -- Internal requisition
from po_requisition_headers_all prh
where prh.requisition_header_id = mmt.transaction_source_id),
8, (select ooh.order_number -- Internal order
from ont.oe_order_headers_all ooh,
ont.oe_order_lines_all ool
where ooh.header_id = ool.header_id
and ool.line_id = mmt.trx_source_line_id),
9, (select mcch.cycle_count_header_name -- Cycle count
from mtl_cycle_count_headers mcch
where mcch.cycle_count_header_id = mmt.transaction_source_id),
10, (select mpi.description -- Physical inventory
from mtl_physical_inventories mpi
where mpi.physical_inventory_id = mmt.transaction_source_id),
11, (select description -- Std cost update
from cst_cost_updates ccu
where ccu.cost_update_id = mmt.cost_update_id),
12, (select ooh.order_number -- RMA
from ont.oe_order_headers_all ooh,
ont.oe_order_lines_all ool
where ooh.header_id = ool.header_id
and ool.line_id = mmt.trx_source_line_id),
13, decode(mmt.transaction_action_id, -- Inventory
-- Revision for version 1.4 shorten document number
1, 'Issue from Stores', -- Issue from stores
2, 'Sub Xfer', -- Subinventory transfer
3, 'Direct Xfer', -- Direct organization transfer
5, 'Planning Xfer', -- Planning transfer
6, 'Ownership Xfer', -- Ownership xfer / consignment
9, (select ooh.order_number -- Logical Intercompany Sales
from ont.oe_order_headers_all ooh,
ont.oe_order_lines_all ool,
mtl_material_transactions mmt_parent
where ooh.header_id = ool.header_id
and ool.line_id = mmt_parent.trx_source_line_id
and mmt.parent_transaction_id = mmt_parent.transaction_id),
10, (select ooh.order_number -- Logical Intercompany Receipt
from ont.oe_order_headers_all ooh,
ont.oe_order_lines_all ool,
mtl_material_transactions mmt_parent
where ooh.header_id = ool.header_id
and ool.line_id = mmt_parent.trx_source_line_id
and mmt.parent_transaction_id = mmt_parent.transaction_id),
12, 'Intransit Receipt', -- Intransit Receipt
13, (select ooh.order_number -- Logical Intercompany Sales Return
from ont.oe_order_headers_all ooh,
ont.oe_order_lines_all ool
where ooh.header_id = ool.header_id
and ool.line_id = mmt.trx_source_line_id),
14, (select ooh.order_number -- Logical Intercompany Sales Return
from ont.oe_order_headers_all ooh,
ont.oe_order_lines_all ool,
mtl_material_transactions mmt_parent
where ooh.header_id = ool.header_id
and ool.line_id = mmt_parent.trx_source_line_id
and mmt.parent_transaction_id = mmt_parent.transaction_id),
15, 'Logical Intransit Receipt', -- Logical Intransit Receipt
21, 'Intransit Shipment', -- Intransit Shipment
22, 'Logical Intransit Shipment', -- Logical Intransit Shipment
24, ' Average Cost Update', -- Average Cost Update
-- Revision for version 1.20
27, 'Receipt into Stores', -- Receipt into Stores
mtst.transaction_source_type_name )) document_order_num,
nvl(( select ottt.name
from oe_order_lines_all ool,
oe_order_headers_all ooh,
oe_transaction_types_tl ottt
where ooh.order_type_id = ottt.transaction_type_id
and ooh.header_id = ool.header_id
and mmt.trx_source_line_id = ool.line_id
and ottt.language = userenv('lang')),'') order_type,
mmt.transaction_id,
mmt.parent_transaction_id,
decode(
mta.accounting_line_type,
7, 'WIP',
14, 'Intransit',
1, decode(
mmt.transaction_action_id,
2, decode(sign(mta.primary_quantity),
-1, mmt.subinventory_code,
1, mmt.transfer_subinventory,
mmt.subinventory_code),
3, decode(
mmt.organization_id,
mta.organization_id, mmt.subinventory_code,
mmt.transfer_subinventory),
21, decode(sign(mta.primary_quantity),
-1, mmt.subinventory_code,
1, mmt.transfer_subinventory,
mmt.subinventory_code),
22, decode(sign(mta.primary_quantity),
-1, mmt.subinventory_code,
1, mmt.transfer_subinventory,
mmt.subinventory_code),
28, decode(sign(mta.primary_quantity),
-1, mmt.subinventory_code,
1, mmt.transfer_subinventory,
mmt.subinventory_code),
mmt.subinventory_code
),
mmt.subinventory_code)
subinventory_code,
-- Revision for version 1.4
-- msiv.primary_uom_code,
muomv.uom_code primary_uom_code,
-- Revision for version 1.3
-- decode(
-- mmt.transaction_action_id,
-- 24, mmt.quantity_adjusted,
-- -- Revision for version 1.15
-- decode(mta.base_transaction_value, 0, mta.primary_quantity,
-- abs(mta.primary_quantity) * decode(sign(mta.base_transaction_value), 1,1,-1))) primary_quantity,
-1 * mmt.primary_quantity primary_quantity,
mta.base_transaction_value mta_amount
from mtl_transaction_accounts mta,
mtl_material_transactions mmt,
mtl_transaction_types mtt,
mtl_system_items_vl msiv,
-- Revision for version 1.4
mtl_item_status_vl misv,
mtl_units_of_measure_vl muomv,
-- End revision for version 1.4
mtl_txn_source_types mtst,
mtl_parameters mp
-- ========================================================
-- Material Transaction, org and item joins
-- ========================================================
-- If the item cost is zero the Credit to DCOGS is not
-- recorded to mtl_transaction_accounts.
where mta.transaction_id (+) = mmt.transaction_id
and mta.accounting_line_type = 36 -- Deferred COGS
and mmt.transaction_type_id = mtt.transaction_type_id
and mmt.organization_id = msiv.organization_id
and mmt.inventory_item_id = msiv.inventory_item_id
and mp.organization_id = msiv.organization_id
and mmt.transaction_source_type_id = mtst.transaction_source_type_id
-- Revision for version 1.4
and msiv.primary_uom_code = muomv.uom_code
and msiv.inventory_item_status_code = misv.inventory_item_status_code
-- End revision for version 1.4
-- ========================================================
-- Material Transaction date and accounting code joins
-- ========================================================
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
and 4=4 -- p_trx_date_from and p_trx_date_to
-- ========================================================
-- Limit to only Sales Order and RMA material transactions
-- ========================================================
and mmt.transaction_source_type_id in (2,12)
and mta.transaction_source_type_id in (2,12)
and mta.accounting_line_type = 36 -- Deferred COGS
) mtl_acct
-- Revision for version 1.4
where 1=1
and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+))
and haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
and ml1.lookup_type = 'MTL_PLANNING_MAKE_BUY'
and ml1.lookup_code = mtl_acct.planning_make_buy_code
and ml2.lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
and ml2.lookup_code = mtl_acct.accounting_line_type
and fcl.lookup_type (+) = 'ITEM_TYPE'
and fcl.lookup_code (+) = mtl_acct.item_type
-- ========================================================
-- Using base tables to avoid the performance issues
-- with org_organization_definitions and hr_operating_units
-- ========================================================
and hoi.org_information_context = 'Accounting Information'
and hoi.organization_id = mtl_acct.organization_id
and hoi.organization_id = haou.organization_id -- this gets the organization name
and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id
and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id
and gl.name = decode('&p_ledger', null, gl.name, '&p_ledger') -- p_ledger
and haou2.name = decode('&p_operating_unit', null, haou2.name, '&p_operating_unit') -- p_operating_unit
-- Avoid selecting disabled inventory organizations
and sysdate < nvl(haou.date_to, sysdate + 1)
-- ========================================================
-- End revision for version 1.4
-- Revision for version 1.3
-- Remove subledger accounting tables join to mta and oap instead
-- ========================================================
and gcc.code_combination_id (+) = mtl_acct.reference_account
and oap.acct_period_id = mtl_acct.acct_period_id
and oap.organization_id = mtl_acct.organization_id
group by
nvl(gl.short_name, gl.name), -- Ledger
haou2.name, -- Operating Unit
mtl_acct.organization_code,
mtl_acct.organization_id,
oap.period_name,
&segment_columns2
mtl_acct.item_number,
mtl_acct.item_description,
fcl.meaning, -- Item Type
mtl_acct.inventory_item_status_code,
ml1.meaning, -- Make Buy Code
-- Revision for version 1.4, for category SQL
mtl_acct.inventory_item_id,
-- End revision for version 1.1
ml2.meaning, -- Acct Line Type
-- End revision for version 1.4
-- Comment this out to net the two lines to zero
-- mtl_acct.transaction_type_name
mtl_acct.transaction_source,
mtl_acct.document_order_num,
mtl_acct.order_type,
mtl_acct.primary_uom_code,
gl.currency_code
having (sum(mtl_acct.primary_quantity) <> 0
and
sum(mtl_acct.mta_amount) <> 0
)
order by
nvl(gl.short_name, gl.name), -- Ledger
haou2.name, -- Operating Unit
mtl_acct.organization_code,
oap.period_name,
&segment_columns2
mtl_acct.item_number,
ml2.meaning, -- Accounting_Line_Type
mtl_acct.transaction_source,
mtl_acct.document_order_num |