Transactions AND Average Costing

Source

The following table indicates whether specific transactions update the average unit cost of an item.

Transactions	Update Average
Purchase Order Receipt to Receiving Inspection	No
Delivery from Receiving Inspection to Inventory	Yes
Purchase Order Receipt to Inventory	Yes
Return to Supplier from Receiving	No
Return to Supplier from Inventory	Yes
Miscellaneous Issue	No (if default cost is used)
Miscellaneous Receipt	No (if default cost is used)
Shipment Transaction/FOB Receipt	No
Shipment Transaction/FOB Shipment: Sending Org	No
Shipment Transaction/FOB Shipment: Receiving Org	Yes
Receipt Transaction/FOB Receipt: Sending Org	No
Receipt Transaction/FOB Receipt: Receiving Org	Yes
Receipt Transaction/FOB Shipment	No
Direct Inter-Organization Transfer: Sending Org	No
Direct Inter-Organization Transfer: Receiving Org	Yes
Cycle Count	No
Physical Inventory	No
Sales Order Shipments	No
RMA Receipts	Yes
RMA Returns	No
Average Cost Update	Yes

Note: The accounts in average costing transactions are the default accounts when average costing is used. If Subledger Accounting (SLA) is enabled and SLA rules are customized, then the default accounts are not used.

Inventory GL

SELECT   xld.source_distribution_type,
         xld.accounting_line_code,
         xld.accounting_line_type_code,
         xld.line_definition_code,
         xld.event_class_code,
         xld.event_type_code,
         xld.rounding_class_code,
         xld.unrounded_entered_cr,
         xld.unrounded_entered_dr,
         xld.unrounded_accounted_cr,
         xld.unrounded_accounted_dr,
         ael.gl_sl_link_id,
         ael.gl_sl_link_table,
         GJB.NAME BATCH_NAME,
         gL.period_name,
         gl.accounted_cr,
         gl.accounted_dr,
         gl.entered_cr,
         gl.entered_dr,
         gh.je_source,
         gh.je_category,
         gh.posted_date,
         mta.inv_sub_ledger_id,
         mta.base_transaction_value,
         mta.currency_conversion_rate,
         mta.rate_or_amount,
         mta.currency_code,
         mmt.subinventory_code,
         TRUNC (mmt.transaction_date) transaction_date,
         mmt.transaction_quantity,
         mmt.transaction_uom,
         mmt.primary_quantity,
         mmt.actual_cost,
         mmt.source_code,
         mmt.source_line_id,
         mmt.rcv_transaction_id,
         msi.concatenated_segments item,
         gcc.concatenated_segments GL_ACCOUNT_STRING,
         ood.organization_code,
         ood.organization_name,
         mmt.transaction_id,
         ael.ae_header_id,
         ael.ae_line_num,
         gl.je_header_id,
         gl.je_line_num,
         gcc.code_combination_id,
         msi.inventory_item_id,
         msi.organization_id,
         gjb.je_batch_id,
--         GCC.SEGMENT2 GCC#50353#ACCOUNT,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT2,
--                                               'MAS_GL_COA_ACCOUNT')
--            GCC#50353#ACCOUNT#DESCR,
--         GCC.SEGMENT1 GCC#50353#COMPANY,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT1,
--                                               'MAS_GL_COA_COMPANY')
--            GCC#50353#COMPANY#DESCR,
--         GCC.SEGMENT6 GCC#50353#DEPARTMENT,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT6,
--                                               'MAS_GL_COA_DEPARTMENT')
--            GCC#50353#DEPARTMENT#DESCR,
--         GCC.SEGMENT8 GCC#50353#FUTURE,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT8,
--                                               'MAS_GL_COA_FUTURE')
--            GCC#50353#FUTURE#DESCR,
--         GCC.SEGMENT7 GCC#50353#IC,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT7,
--                                               'MAS_GL_COA_COMPANY')
--            GCC#50353#IC#DESCR,
--         GCC.SEGMENT4 GCC#50353#LOB,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT4,
--                                               'MAS_GL_COA_LOB')
--            GCC#50353#LOB#DESCR,
--         GCC.SEGMENT5 GCC#50353#LOCATIONS,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT5,
--                                               'MAS_GL_COA_LOCATION')
--            GCC#50353#LOCATIONS#DESCR,
--         GCC.SEGMENT3 GCC#50353#QUALIFIER,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT3,
--                                               'MAS_GL_COA_ACCT_QUAL')
--            GCC#50353#QUALIFIER#DESCR
  FROM   xla_transaction_entities_upg ent,
         xla_events e,
         xla_distribution_links xld,
         mtl_transaction_accounts mta,
         mtl_material_transactions mmt,
         xla_ae_headers ah,
         xla_ae_lines ael,
         gl_import_references gir,
         gl_je_lines gl,
         gl_code_combinations_kfv gcc,
         gl_je_headers gh,
         GL_JE_BATCHES GJB,
         mtl_system_items_kfv msi,
         org_organization_definitions ood
 WHERE       mmt.transaction_id = NVL (ent.source_id_int_1, -99)
         AND ent.entity_code = 'MTL_ACCOUNTING_EVENTS'
         AND ent.application_id = 707
         AND ent.entity_id = e.entity_id
         AND e.application_id = 707
         AND e.event_id = xld.event_id
         AND ah.application_id = 707
         AND ah.entity_id = ent.entity_id
         AND ah.event_id = e.event_id
         AND ah.ledger_id = ent.ledger_id
         AND ah.ae_header_id = ael.ae_header_id
         AND ael.application_id = 707
         AND ael.ledger_id = ah.ledger_id
         AND ael.AE_HEADER_ID = xld.AE_HEADER_ID
         AND ael.AE_LINE_NUM = xld.AE_LINE_NUM
         AND xld.application_id = 707
         AND xld.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
         AND xld.source_distribution_id_num_1 = mta.inv_sub_ledger_id
         AND mta.transaction_id = mmt.transaction_id
         AND ael.gl_sl_link_id = gir.gl_sl_link_id
         AND ael.gl_sl_link_table = gir.gl_sl_link_table
         AND gir.je_header_id = gl.je_header_id
         AND gir.je_line_num = gl.je_line_num
         AND gl.code_combination_id = gcc.code_Combination_id
         AND gl.je_header_id = gh.je_header_id
         AND GH.JE_BATCH_ID = GJB.JE_BATCH_ID
         AND mta.transaction_id = mmt.transaction_id
         AND mmt.inventory_item_id = msi.inventory_item_id
         AND mmt.organization_id = msi.organization_id
         AND msi.organization_id = ood.organization_id

Consigned Inventory Flow in R12

Source

Consigned Inventory Flow in R12

Setup steps

Step 1) Create an Item "CONSIGNED". Apply Purchasing template. Also tick "Use Approved Supplier" in purchasing tab

Step 2) Create a Blanket Purchase Agreement for this item. Take supplier as "Abbott Laboratories, Inc." and site as "CORP HQ". Make sure pay on "Use" is ticked in Supplier setups

Step 3) Create an ASL for this Item and Supplier combination. On Item-attributes form assign above blanket Agreement and  in Inventory tab tick the Consigned from Supplier.

Step 4) Define Consigned\VMI Consumption rules in inventory. Navigate to Setup>Transactions>Consigned\VMI Consumption. Here take From Sub-inventory as "Stores" and To Sub-inventory as "FGI"

Note : The weight value allows you to set the processing order. For example, if two transactions satisfy the transaction criteria, the system processes the transaction with the highest weight.

Test Case

Step 1) Create a Standard PO and Receive the material. Please keep receipt routing as Direct

You can notice Consigned flag on Shipment line is checked automatically. All consigned PO is always closed for invoicing

Step 2) Now go to iSupplier Portal and create an ASN for this PO 6356

ASN1234

Step 3) Now receive against this ASN

Step 4) You can check that no accounting transactions created yet

Step 5) Check owning party information

Step 6) Now when we require material we can transfer material from Stores to FGI. Transfer 10 qty

Step 7) Check Material transactions

Step 8) Now run Consumption Advise

Step 9) Now check the consumption advise from the po summary form. consumption advice create a release against the BPA we have created in setup steps. Remember the BPA number was 6355.

You can see you can not open the release i.e. you can not modify the consumption advise. also this release is closed for receiving as material is already received

Step 10) Now log in iSupplier to see consumption advice

You can click on consumption advise and see it

Inventory related Queries

SOURCE

--- get item attributes NOT UNDER status control 
SELECT   meaning1 attrib_group, user_attribute_name_gui,

-- ,control_level, status_control_code,attribute_name,
-- attribute_group_id,data_type,
-- user_attribute_name,level_updateable_flag,
-- validation_code ,lookup_type1,
-- lookup_code1,enabled_flag1,lookup_type2,lookup_code2,
meaning2 control_level,
-- ,enabled_flag2,
-- lookup_type3,lookup_code3,
meaning3 status_control,
-- enabled_flag3,lookup_type4,lookup_code4,
meaning4 VALIDATION
-- ,enabled_flag4
FROM     mtl_item_attributes_v
WHERE control_level IN (1, 2)
AND status_control_code IS NULL
AND user_attribute_name_gui IS NOT NULL
AND attribute_name IN (SELECT attribute_name
FROM mtl_item_attr_appl_inst_v)
ORDER BY attribute_group_id_gui, sequence_gui
/

-- get item status attribute controls

SELECT   ia.attribute_group_id GROUP_ID, ia.user_attribute_name_gui,
lk.meaning controlled_at, ia.attribute_name,
--   ia.user_attribute_name,
ia.status_control_code,
ia.validation_code
FROM fnd_lookup_values lk, mtl_item_attributes ia
WHERE ia.control_level = lk.lookup_code
AND lk.lookup_type = 'ITEM_CONTROL_LEVEL_GUI'
ORDER BY ia.attribute_group_id, 1
/

-- find item status attributes :

SELECT   mis.inventory_item_status_code item_status, mis.description,
mis.disable_date, av.attribute_name, av.attribute_value VALUE
FROM mtl_item_status mis, mtl_status_attribute_values av
WHERE mis.inventory_item_status_code = av.inventory_item_status_code
ORDER BY 1
/
-- get item attributes UNDER status control :

SELECT   meaning1 attrib_group, user_attribute_name_gui,

-- ,control_level, status_control_code,attribute_name,
-- attribute_group_id,data_type,
-- user_attribute_name,level_updateable_flag,
-- validation_code ,lookup_type1, lookup_code1,enabled_flag1,lookup_type2,lookup_code2,
meaning2 control_level,
-- ,enabled_flag2,
-- lookup_type3,lookup_code3,
meaning3 status_control,
-- enabled_flag3,lookup_type4,lookup_code4,
meaning4 VALIDATION
-- ,enabled_flag4
FROM     mtl_item_attributes_v
WHERE control_level IN (1, 2)
AND status_control_code IS NOT NULL
AND user_attribute_name_gui IS NOT NULL
AND attribute_name IN (SELECT attribute_name
FROM mtl_item_attr_appl_inst_v)
ORDER BY attribute_group_id_gui, sequence_gui
/

--- find an Item attribute info :

SELECT   segment1 item, msi.description, inventory_item_id,
ml.meaning item_type,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.inventory_item_status_code
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.inventory_item_status_code')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.purchasing_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.purchasing_item_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.shippable_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.shippable_item_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.mtl_transactions_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.mtl_transactions_enabled_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.so_transactions_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.so_transactions_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.internal_order_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.internal_order_enabled_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.customer_order_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.customer_order_enabled_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.purchasing_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.purchasing_enabled_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.inventory_asset_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.inventory_asset_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.eng_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) = 'mtl_system_items.eng_item_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.inventory_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.inventory_item_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name
|| '.'
|| msi.service_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.service_item_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.internal_order_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.internal_order_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.build_in_wip_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.build_in_wip_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.bom_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.bom_enabled_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.stock_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.stock_enabled_flag')
ATTRIBUTE
FROM fnd_lookup_values ml, mtl_system_items msi
WHERE msi.segment1 LIKE 'AS18947%'
AND msi.organization_id = 204
AND msi.item_type = ml.lookup_code(+)
AND ml.lookup_type(+) = 'ITEM_TYPE'
ORDER BY 1, 2
/

--- find Item template attribute VALUES :

SELECT   it.template_name, ita.attribute_name, ita.attribute_value
FROM mtl_item_templates it, mtl_item_templ_attributes ita
WHERE it.template_name LIKE 'xxx%'
AND it.template_id = ita.template_id
AND ita.attribute_value IS NOT NULL
ORDER BY 1, 2
/

--- find  item cross-REFERENCES :


/* Formatted on 2010/08/24 11:27 (Formatter Plus v4.8.0) */
SELECT   msi.segment1 item, mcr.cross_reference_type reference_type,
mcr.cross_reference, mcr.description
FROM mtl_cross_references mcr, mtl_system_items msi
WHERE mcr.cross_reference_type = 'Vendor'
AND mcr.inventory_item_id = msi.inventory_item_id
AND mcr.organization_id = msi.organization_id
ORDER BY 1, 2
/


-- find Customer items :


/* Formatted on 2010/08/24 11:27 (Formatter Plus v4.8.0) */
SELECT   hp.party_name customer, ci.customer_item_number,
ci.customer_item_desc, msi.segment1 item, msi.description item_desc,
ci.customer_category_code, ci.item_definition_level,
ci.commodity_code_id, ci.address_id
FROM hz_parties hp,
hz_cust_accounts hca,
mtl_system_items msi,
mtl_customer_items ci,
mtl_customer_item_xrefs ix
WHERE ci.customer_item_id = ix.customer_item_id
AND ix.inventory_item_id = msi.inventory_item_id
AND ix.master_organization_id = msi.organization_id
AND ci.customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
ORDER BY 1, 2
/

---find Manufacturer items :


SELECT   mm.manufacturer_name, mp.mfg_part_num, mp.description,
msi.segment1 inv_item, msi.description item_desc
FROM mtl_system_items msi, mtl_mfg_part_numbers mp, mtl_manufacturers mm
WHERE mm.manufacturer_id = mp.manufacturer_id
AND mp.inventory_item_id = msi.inventory_item_id
AND mp.organization_id = msi.organization_id
ORDER BY 1, 2
/

--find related items :

SELECT   ito.segment1 item, ito.description, itr.segment1 related_item,
itr.description, ml.meaning relation, ri.reciprocal_flag
FROM mfg_lookups ml,
mtl_system_items itr,
mtl_system_items ito,
mtl_related_items ri
WHERE ri.inventory_item_id = ito.inventory_item_id
AND ri.organization_id = ito.organization_id
AND ri.related_item_id = itr.inventory_item_id
AND ri.organization_id = itr.organization_id
AND ri.relationship_type_id = ml.lookup_code
AND ml.lookup_type(+) = 'MTL_RELATIONSHIP_TYPES'
ORDER BY 1, 2
/
-- find DEFAULT category FOR a category SET :

/* Formatted on 2010/08/24 11:28 (Formatter Plus v4.8.0) */
SELECT   mcats.category_set_name, mcat.segment1 default_category,
mcat.description cat_desc, mcat.category_id, mcats.category_set_id
FROM mtl_category_sets mcats, mtl_categories mcat
WHERE mcats.category_set_name LIKE '%'
AND mcat.category_id = mcats.default_category_id
ORDER BY 1, 2
/

-- find ALL items assigned TO categories OF a category SET :

SELECT   mcats.category_set_name,
mcat.segment1 || '.' || mcat.segment2 CATEGORY, msi.segment1 item,
msi.description item_desc
FROM mtl_item_categories micat,
mtl_category_sets mcats,
mtl_categories mcat,
mtl_system_items_vl msi
WHERE mcats.category_set_name LIKE 'Inv%'
AND micat.category_set_id = mcats.category_set_id
AND micat.category_id = mcat.category_id
AND mcat.segment1 LIKE 'N%'
AND msi.inventory_item_id = micat.inventory_item_id
AND msi.organization_id = micat.organization_id
AND msi.organization_id = 204
ORDER BY 1, 2, 3
/

Understanding ATO and PTO Models

In Oracle there are 3 different type of Models which can be created

1. ATO Models - (Assemble to Order)
2. PTO Models - (Pick to Order)
3. Hybrid Models - These are the PTO Models which have ATO models as Child

Before you start creating any of the above Models following criteria must be understood

1. If the finished product is shipped as an Assembled unit then we can use ATO Model
2. If the finishd product is shipped as disassembled unit (as loose parts) then we can use PTO Model
3. If the finshed product is shipped as disassembled unit (as loose parts) and has Assemly items which needs to be configured then we can use Hybrid Models (PTO Model having ATO Model as Child)

Few things which needs to be considered while Model designing

ATO Models can have ATO Option Class as well as ATO Models as Child.
PTO Models can have PTO Option Class as well as PTO Models and ATO Models as Child.
ATO Option Class can have ATO Option Class as well as ATO Models as Child.
PTO Option Class can have PTO Option Class as well as PTO Models and ATO models as Child.

ATO Models:

Configuration items can be created.
Standard Bill of Material can be created for Configuration items.
This BOM is consisting of the selected items in Oracle Configurator from the ATO Model.
This BOM is a single level BOM showing ATO Option Class and the item(s) selected in the ATO Option Class.
Job Order (Work Order) can be generated for the Configuration items.
Only the Top Configuration item will be eligible for Shipping.

The are standard products and are often configured by customers.
Subassemblies are manufactured prior to receiving the order and when the order is received ,the subassemblies are assembled to make the finished products
For Example : Automobiles , Laptops

This is a pull based manufacturing system where the parts are kept in stock but not manufactured until the actual order is created.  Based on the order, the work order gets created (based on a predefined BOM) and fulfilled.

PTO Models:

No Configuraion item can be created.
All the selected items in PTO Option Classes or in the PTO Model are shipping eligible.
If an ATO model is a child for the PTO Model then in that case only the Configuration item created for that ATO Model will be shipping eligible.

Models can be deisgned in various ways depending upon the Business Product Structure/Manufacturing Flow for the Product etc.

A Variety of shippable components are stocked.

Customers order kits or collection of these parts under a single item number.

Kits can be predefined or configured by the customer during the order entry process.

There is no additional value added after the customer order.

For Example : Computer System (CPU , Monitor , and Printer)

Oracle Master Scheduling/MRP and Supply Chain Planning does not support planning For PTO

Pick to Order (PTO) items have the Pick Component attribute set to Yes. 

Pick-to-order bills cannot have fractional component quantities if Oracle Order Management is installed. 

You cannot create routings for planning or pick-to-order items. 

This is a push based manufacturing system where the goods are manufactured based on forecasted demand. The goods are picked as and when the orders come in and fulfilled against the order.

KIT

A kit is similar to a pick–to–order model because it has shippable components, but it has no available options to choose like PTO model
In Oracle Inventory module, kit item type and kit item template are pre-defined by Oracle
An example of a product kit is a motor car maintenance kit which consists spanners, jack etc.
The word ‘Kitting’ is used when you make a kit after picking it's components from subinventory/ies and pack it. 


Configure-To-Order (CTO):

It is a method of manufacturing which allows you, or your customer, to select a "base product" and configure all the variable parameters associated with that product. 

It allows you, or your customer, to choose a base product at the very moment of ordering and then configure all the variable parameters (features) associated with that product from defined/available options. Based on these selections, configurable items on each quote or order typically generates the "unique product" configuration and manufacturing routing and/or bill of materials based on various features and options. Vendor/order receiving company subsequently builds that configuration dynamically upon receipt of the order.