INTERNAL SALES ORDERS : CREATION TO RECEIPT CYCLE in Oracle e-business.

Oracle e-business INTERNAL SALES ORDERS : CREATION TO RECEIPT CYCLE

Overview This article outlines the functionality and flow of information from Purchasing to Order Management to Shipping and Inventory during the life cycle from an Internal Requisition creation until the Internal Order is received into the destination organization. The process of receiving against Internal Sales Orders involves the following steps: 1.     Create an Internally Orderable Item 2.     Setup Shipping Network 3.     Create an Internal Requisition 4.     Run Create Internal Orders concurrent program 5.     Run Order Import concurrent program 6.     Pick Release 7.     Ship Confirm 8.     Interface Trip Stop 9.     Receive/Deliver the Shipment for the Internal Order 10.   Recommended Patch List 11.   Important Queries/Scripts for Internal Orders 1.0 Create an Internally Orderable Item Navigate to: Inventory/Items/Master Items. ·       Create an Item. ·       Under Inventory alternative region, check the boxes for Inventory Item, Stockable, Transactable. ·       Under Purchasing alternative region, check the boxes for Purchased, Purchasable. ·       Under Order Management alternative region, check the boxes for Internal Ordered, Internal Orders Enabled, OE Transactable        and Shippable. Enable the item in Source and Destination Organization. Setup Shipping Network This information describes the relationships and accounting information that exists between a From (Source) Organization and a To (Destination) Organization. Navigate to: Inventory/Setup/Organizations/Shipping Network ·       Specify the From and To Organization. ·       If you specify the Transfer Type as Intransit, then you need to explicitly receive using the Enter Receipts screen. It would        be a Standard Receipt. ·       If you specify the Transfer Type as Direct, then goods will get directly shipped into inventory. It would be a Direct Delivery. ·       Make sure to check the Internal Order Required checkbox.     ReceiptRouting can be Standard or Direct. Create an Internal Requisition Navigate to: Purchasing/Requisitions/Requisitions. ·       Create an Internal Requisition using the item from Step 1.0 ·       Specify the quantity. ·       Specify the Source and Destination Organizations. Source is on the right and Destination on the left in the overflow region. ·       Save and Approve the Requisition. Major Tables Impacted Major Columns po_requisition_headers_all SEGMENT1, REQUISITION_HEADER_ID,TRANSFERRED_TO_OE_FLAG,ORG_ID, AUTHORIZATION_STATUS, CANCEL_FLAG, CLOSED_CODE po_requisition_lines_all REQUISITION_LINE_ID, DESTINATION_ORGANIZATION_ID, DESTINATION_SUBINVENTORY, SOURCE_ORGANIZATION_ID, SOURCE_SUBINVENTORY, DELIVER_TO_LOCATION_ID, ORG_ID, CANCEL_FLAG, CLOSED_CODE, QUANTITY,QUANTITY_CANCELLED, QUANTITY_DELIVERED, QUANTITY_RECEIVED TRANSFERRED_TO_OE_FLAG = 'N' AUTHORIZATION_STATUS = 'APPROVED' CANCEL_FLAG <> 'Y' CLOSED_CODE <> 'FINALLY CLOSED' Record is created in MTL_SUPPLY with SUPPLY_TYPE_CODE = 'REQ'. Run Create Internal Orders concurrent program Run the Create Internal Orders concurrent program. The Create Internal Orders process loads the OM open interface tables using the Order Type you define in the Purchasing Options window. Run the foll. queries to confirm if the interface record got created: SQL1> Select count(*) From oe_headers_iface_all             Where orig_sys_document_ref = <'Requisition Number from step 3.0'>; SQL2> Select count(*) From oe_lines_iface_all             Where orig_sys_document_ref = <'Requisition Number from step 3.0'>; Major Tables Impacted Major Columns oe_headers_iface_all ORDER_NUMBER, HEADER_ID, ORDER_TYPE, SHIP_FROM_ORG_ID, SHIP_TO_ORG_ID, BOOKED_FLAG, CANCELLED_FLAG, CLOSED_FLAG, STATUS_FLAG,ORIG_SYS_DOCUMENT_REF,DELIVER_TO_ORG_ID, ORG_ID oe_lines_iface_all LINE_ID, SHIP_FROM_ORG_ID, SHIP_TO_ORG_ID, SHIPMENT_NUMBER, SHIPPED_QUANTITY, CLOSED_FLAG, DELIVER_TO_ORG_ID, ORG_ID,ORIG_SYS_DOCUMENT_REF   BOOKED_FLAG = 'N' 5.0 Run Order Import Navigate to: Order Management/Orders, Returns/Import Orders. Run the Order Import concurrent program. Order Import creates Internal Sales Orders from the records in the interface tables and determines the order cycle and defaults values based on the order type. Note down the Order Number. Run the foll. Sql to get the Order Number: SQL1> Select order_number From oe_order_headers_all              Where orig_sys_document_ref = <'Requisition Number from step 3.0'>; Check TRANSFERRED_TO_OE_FLAG = 'Y' in po_requisition_headers_all. Major Tables Impacted Major Columns oe_order_headers_all ORDER_NUMBER, HEADER_ID, ORDER_TYPE_ID, SHIP_FROM_ORG_ID, SHIP_TO_ORG_ID, OPEN_FLAG, BOOKED_FLAG, CANCELLED_FLAG,  FLOW_STATUS_CODE, DELIVER_TO_ORG_ID,ORIG_SYS_DOCUMENT_REF,ORG_ID oe_order_lines_all LINE_ID, SHIP_FROM_ORG_ID, SHIP_TO_ORG_ID, SHIPMENT_NUMBER, SHIPPED_QUANTITY, OPEN_FLAG, BOOKED_FLAG, CANCELLED_FLAG, DELIVER_TO_ORG_ID,ORIG_SYS_DOCUMENT_REF,FLOW_STATUS_CODE,SHIPPING_INTERFACED_FLAG,ORG_ID BOOKED_FLAG = 'Y' FLOW_STATUS_CODE = 'BOOKED' SHIPPING_INTERFACED_FLAG = 'N' Running Create Internal Orders and Order Import concurrent programs has no affect on MTL_SUPPLY. By default the Create Internal Orders process populates the oe_actions_iface_all table with anOPERATION_CODE of 'BOOK_ORDER', causing the Order to be Booked by the Order Import process. So there is no need to Book the Order. If for whatever reason a hold gets placed on the Order at Booking, then after removing the hold the user would have to peform the actions Progress Order to move it to "Awaiting Shipping". 6.0 Pick Release Pick Release finds and releases eligible delivery lines that meet the release criteria, and creates move orders. You can pick release by order, trip, stop, container, delivery, warehouse, customer, schedule, or requested dates, shipment priority or combinations of the above criteria. The default release criteria is set up in Shipping Parameters, but you can override the default criteria in the Release Sales Order window at pick release. Major Tables Impacted Major Columns wsh_delivery_details OE_INTERFACED_FLAG, INV_INTERFACED_FLAG, ORGANIZATION_ID, RELEASED_STATUS, SHIP_FROM_LOCATION_ID, SHIP_TO_LOCATION_ID, SHIPPED_QUANTITY, DELIVERY_DETAIL_ID Your Order should be Pick Release Eligible. RELEASED_STATUS: 'R' – Ready to Release ORGANIZATION_ID is the Source Organization_id Navigate to: Order Management/Shipping/Release Sales Orders/ Release Sales Orders Enter Order Number and complete your entries in the Release Sales Order for Picking window or select a Based On Rule to automatically default the remaining criteria. Under Shipping tab region, make sure Autocreate Delivery is Yes. It will automatically create theDelivery. Under Inventory tab region, make sure Auto Pick Confirm is Yes. It will make the line Staged/Pick Confirmed after Pick Release is done successfully. Choose the Concurrent or Online button. Pick release is launched and pick releases the deliveries and delivery lines for the trip. Save your work. Your Order should now be Ship Confirm Eligible. RELEASED_STATUS: 'Y' – Staged/Pick Confirmed Ship Confirm Ship Confirm is the process of recording that items have shipped. When you Ship Confirm a delivery, Shipping Execution confirms that the delivery lines associated with the delivery have shipped. Your Order should be Ship Confirm Eligible. RELEASED_STATUS: 'Y' – Staged/Pick Confirmed Navigate to: Order Management/Shipping/Transactions Navigate to the Query Manager window, and find the Delivery. The Delivery displays in the Shipping Transactions window. Delivery is a not null column. If Delivery is null, you need to perform the action Auto-create Deliveries. Delivery is passed as Shipment Number to INV and PO. Under Delivery alternative region, select Ship Confirmfrom the Actions menu to display the Confirm Deliverywindow. In the Ship Options region, select Ship All to ship confirm the entire quantity regardless of what was entered in the Shipped Quantity field. In the Auto-create Trip Options region, select the ship method and the actual departure date. The simplest way to ship confirm one or more deliveries is to enable the Set Delivery in-Transit and Close Trip fields. Set Delivery In-transit: Creates a trip and stops for the delivery. Closes first stop of the delivery, but leaves second stop open. Sets status of delivery to In-transit and initiates Order Management (OM) and Inventory interfaces. Close Trip: Creates a trip and stops for the delivery. Closes trip, all stops, and the delivery. If you uncheck the Defer Interface box and run Ship Confirm, the Inventory Interface is run automatically, and the Order Line status is updated. A request is submitted to run Interface Trip Stop with mode All for the trip stop. If you check the Defer Interface box and run Ship Confirm, you need to run the Interface Trip Stop-SRSconcurrent request to update the Inventory and the Order Line status. When the Defer Interface box is checked, a request is not automatically submitted to interface the trip stops. Choose the Go button to ship confirm and save your work. Your Order should now be Ship Confirm, Confirmed and Inventory Interface, Interfaced. RELEASED_STATUS: 'C' – Ship Confirmed Major Tables Impacted Major Columns wsh_delivery_details OE_INTERFACED_FLAG, INV_INTERFACED_FLAG, ORGANIZATION_ID, RELEASED_STATUS, SHIP_FROM_LOCATION_ID, SHIP_TO_LOCATION_ID, SHIPPED_QUANTITY, DELIVERY_DETAIL_ID INV_INTERFACED_FLAG = 'N' – Not Interfaced                                             'Y' – Record in MTL_MATERIAL_TRANSACTIONS                                             'P' – Error Condition OE_INTERFACED_FLAG = 'N' – Not Interfaced                              'Y' – Record in OE_ORDER_LINES_ALL If the Interface Trip Stop concurrent request is run, record is created in MTL_SUPPLY with SUPPLY_TYPE_CODE = 'SHIPMENT'. Ship Confirm inserts records into MTL_TRANSACTIONS_INTERFACE. Interface Trip Stop Navigate to: Order Management/Shipping/Interfaces/Run Run the request: Interface Trip Stop – SRS. Mode: All – All Interfaces            Inventory – Interface to Inventory            OM and DSNO – Interface to Order Management and Submit DSNO Inventory Interface- SRS is run to update inventory with the ship confirmation information. Inventory balances are decremented and reservations relieved. This program always spawns the Order Management Interface - SRS program. It is very important in the process flow that the Inventory Interface complete before the Order Management Interface to ensure the integrity of the reservation information. Inventory Interface picks records from MTI (MTL_TRANSACTIONS_INTERFACE) and inserts them into MMTT (MTL_MATERIAL_TRANSACTIONS_TEMP). The Inventory Manager then inserts rows into RTI (RCV_TRANSACTIONS_INTERFACE) and calls the Receiving Transaction Processor. If transfer type is Direct, the Receiving Transaction Processor inserts a record into MMTT and calls the inventory function inltpu() which inserts records into MMT (MTL_MATERIAL_TRANSACTIONS) and MOQ (MTL_ONHAND_QUANTITIES) and completes the delivery of the item into Inventory. The Receiving Transaction Processor also inserts records into RSH (RCV_SHIPMENT_HEADERS), RSL (RCV_SHIPMENT_LINES) and RT (RCV_TRANSACTIONS). It also updates theQUANTITY_RECEIVED and QUANTITY_DELIVERED on PO_REQUISITION_LINES_ALL. Records get deleted from MTL_SUPPLY on delivery into Inventory. Major Tables Impacted Major Columns MTL_MATERIAL_TRANSACTIONS_TEMP TRANSACTION_HEADER_ID, TRANSACTION_TEMP_ID, ORGANIZATION_ID, OWNING_ORGANIZATION_ID, SOURCE_CODE, PARENT_LINE_ID, PARENT_TRANSACTION_TEMP_ID, PRIMARY_QUANTITY, RCV_TRANSACTION_ID, SHIPMENT_NUMBER, SHIPPED_QUANTITY, TRANSACTION_QUANTITY, SHIP_TO_LOCATION, REQUISITION_DISTRIBUTION_ID, REQUISITION_LINE_ID MTL_MATERIAL_TRANSACTIONS ORGANIZATION_ID, OWNING_ORGANIZATION_ID, SOURCE_CODE, SOURCE_LINE_ID, PRIMARY_QUANTITY, RCV_TRANSACTION_ID, SHIP_TO_LOCATION_ID, SHIPMENT_NUMBER, TRANSACTION_ID, TRANSACTION_TEMP_ID, TRANSACTION_QUANTITY, TRANSACTION_DATE MTL_ONHAND_QUANTITIES DATE_RECEIVED, ORGANIZATION_ID, SUBINVENTORY_CODE, TRANSACTION_QUANTITY, UPDATE_TRANSACTION_ID RCV_SHIPMENT_HEADERS RECEIPT_NUM, SHIPMENT_HEADER_ID, SHIPMENT_NUM, SHIPPED_DATE, RECEIPT_SOURCE_CODE, SHIP_TO_LOCATION_ID, SHIP_TO_ORG_ID, ORGANIZATION_ID RCV_SHIPMENT_LINES FROM_ORGANIZATION_ID, TO_ORGANIZATION_ID, TO_SUBINVENTORY, SHIP_TO_LOCATION_ID, QUANTITY_RECEIVED, QUANTITY_SHIPPED, SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID, REQ_DISTRIBUTION_ID, REQUISITION_LINE_ID, OE_ORDER_HEADER_ID, OE_ORDER_LINE_ID, DELIVER_TO_LOCATION_ID, DESTINATION_TYPE_CODE RCV_TRANSACTIONS ORGANIZATION_ID, SUBINVENTORY, SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID, QUANTITY, TRANSACTION_DATE, TRANSACTION_ID, TRANSACTION_TYPE, REQ_DISTRIBUTION_ID, REQUISITION_LINE_ID, OE_ORDER_HEADER_ID, OE_ORDER_LINE_ID, DELIVER_TO_LOCATION_ID, DESTINATION_TYPE_CODE PO_REQUISITION_LINES_ALL REQUISITION_LINE_ID, DESTINATION_ORGANIZATION_ID, DESTINATION_SUBINVENTORY, SOURCE_ORGANIZATION_ID, SOURCE_SUBINVENTORY, DELIVER_TO_LOCATION_ID, ORG_ID, CANCEL_FLAG, CLOSED_CODE, QUANTITY,QUANTITY_CANCELLED, QUANTITY_DELIVERED, QUANTITY_RECEIVED If transfer type is Intransit, the Receiving Transaction Processor just creates the shipment by inserting records into RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES. Then you need to explicitly receive from the Enter Receipts screen. Record is also created in MTL_SUPPLY with SUPPLY_TYPE_CODE = 'SHIPMENT'. Order Management  Interface is run to update Order Management with the ship confirmation information. Order line shipped quantities will be updated. . Receive/Deliver the Shipment for the Internal Order Navigate to: Purchasing/Receiving/Enter Receipts. Query against the Requisition Number or the Shipment Number (Delivery Number on the Shipping Transactions window) and do the Receive/Deliver Transactions. Major Tables Impacted Major Columns rcv_shipment_headers RECEIPT_NUM, SHIPMENT_HEADER_ID, SHIPMENT_NUM, SHIPPED_DATE, RECEIPT_SOURCE_CODE, SHIP_TO_LOCATION_ID, SHIP_TO_ORG_ID, ORGANIZATION_ID rcv_shipment_lines FROM_ORGANIZATION_ID, TO_ORGANIZATION_ID, TO_SUBINVENTORY, SHIP_TO_LOCATION_ID, SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID, QUANTITY_RECEIVED, QUANTITY_SHIPPED, REQ_DISTRIBUTION_ID, REQUISITION_LINE_ID, OE_ORDER_HEADER_ID, OE_ORDER_LINE_ID, DELIVER_TO_LOCATION_ID, DESTINATION_TYPE_CODE rcv_transactions ORGANIZATION_ID, SUBINVENTORY, SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID, QUANTITY, TRANSACTION_DATE, TRANSACTION_ID, TRANSACTION_TYPE, REQ_DISTRIBUTION_ID, REQUISITION_LINE_ID, OE_ORDER_HEADER_ID, OE_ORDER_LINE_ID, DELIVER_TO_LOCATION_ID, DESTINATION_TYPE_CODE Record is created in MTL_SUPPLY with SUPPLY_TYPE_CODE = 'RECEIVING' on performing a Receive transaction. But records get deleted from MTL_SUPPLY on delivery into Inventory.

Oracle Process Manufacturing FORMULA Query

select a.FORMULA_ID,a.formula_no,a.FORMULA_DESC1,b.INVENTORY_ITEM_ID,c.description,
b.organization_id,decode(b.line_type,-1,'Ingredient','Product') Type
from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c
where a.formula_id=b.FORMULA_ID
and b.ORGANIZATION_ID=:your_Org_id
and a.FORMULA_CLASS = 'PKG'
---and a.FORMULA_CLASS<>'COSTING'
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and b.ORGANIZATION_ID=c.organization_id
order by a.FORMULA_ID

Select b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,decode(c.line_type,-1,'Ingredient','Product') Type,
sum(e.TRANSACTION_QUANTITY) quantity
from apps.GME_BATCH_HEADER a,apps.gmd_recipes b,gmd_recipe_validity_rules grr,apps.gme_material_details c,
apps.mtl_system_items d,apps.mtl_material_transactions e
where a.FORMULA_ID=b.FORMULA_ID
and a.ROUTING_ID=b.ROUTING_ID
and a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID
and grr.RECIPE_ID=b.recipe_id
and a.BATCH_ID=c.BATCH_ID
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID
and c.ORGANIZATION_ID=d.organization_id
and a.batch_id=e.TRANSACTION_SOURCE_ID
and a.ORGANIZATION_ID=e.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID
and a.batch_no in (select batch_no from apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and :to_date)
and a.ORGANIZATION_ID=:your_org_id
and trunc(e.transaction_date) between :from_date and :to_date
group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type
order by RECIPE_DESCRIPTION


sum(e.TRANSACTION_QUANTITY) quantity



from apps.GME_BATCH_HEADER a,apps.gmd_recipes b,gmd_recipe_validity_rules grr,apps.gme_material_details
c,apps.mtl_system_items d,apps.mtl_material_transactions e
where a.FORMULA_ID=b.FORMULA_ID
and a.ROUTING_ID=b.ROUTING_ID
and a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID
and grr.RECIPE_ID=b.recipe_id
and a.BATCH_ID=c.BATCH_ID
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID
and c.ORGANIZATION_ID=d.organization_id
and a.batch_id=e.TRANSACTION_SOURCE_ID
and a.ORGANIZATION_ID=e.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID
and a.batch_no in (select batch_no from apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and :to_date)
and a.ORGANIZATION_ID=:your_org_id
and trunc(e.transaction_date) between :from_date and :to_date
group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type
order by RECIPE_DESCRIPTION
sum(e.TRANSACTION_QUANTITY) quantity

Oracle Process Manufacturing Query:

select a.FORMULA_ID,a.formula_no,a.FORMULA_DESC1,b.INVENTORY_ITEM_ID,c.description,
b.organization_id,decode(b.line_type,-1,'Ingredient','Product') Type
from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c
where a.formula_id=b.FORMULA_ID
and b.ORGANIZATION_ID=:your_Org_id
and a.FORMULA_CLASS = 'PKG'
---and a.FORMULA_CLASS<>'COSTING'
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and b.ORGANIZATION_ID=c.organization_id
order by a.FORMULA_ID


from apps.GME_BATCH_HEADER a,apps.gmd_recipes b,gmd_recipe_validity_rules grr,apps.gme_material_details c,
apps.mtl_system_items d,apps.mtl_material_transactions e
where a.FORMULA_ID=b.FORMULA_ID
and a.ROUTING_ID=b.ROUTING_ID
and a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID
and grr.RECIPE_ID=b.recipe_id
and a.BATCH_ID=c.BATCH_ID
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID
and c.ORGANIZATION_ID=d.organization_id
and a.batch_id=e.TRANSACTION_SOURCE_ID
and a.ORGANIZATION_ID=e.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID
and a.batch_no in (select batch_no from apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and :to_date)
and a.ORGANIZATION_ID=:your_org_id
and trunc(e.transaction_date) between :from_date and :to_date
group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type
order by RECIPE_DESCRIPTION

ORACLE APPLICATION OPM INTERVIEW QUESTIONS R12

1) What are the different types of manufacturing processes? And what are the primary differences between them? OR Explain Process Manufacturing v/s Discrete manufacturing?

Discrete Manufacturing is the production of distinct items that use bills of material and routings to determine costs and lead times. Eg: Car manufacturing, Computer Manufacturing etc you get all the parts and assemble them.
OPM on the other hand caters to capabilities to allow for multiple units of measure because the flexibility in batch production is required in process industries. e.g Chemical Industries, food processing etc. This involves complex internal processes and need a high level of control.
Eg: You mix x kgs of sugar and n litres of milk, you get a sweet as the output. This can be in kilos, litres or smaller units. If you do not allow the water to evaporate the output will be a liquid, if you heat longer the output will be a burfi.

What is the difference between the two?

1)ODM uses BOM (Bill of Materials), OPM uses formulas and Recipes
2)UOM will vary depending on the material used in OPM but in ODM UOM will be each or unit
3)ODM assembles or builds things, OPM mixes , blends and transforms or converts.
4)OPM is driven by item attributes and ODM is driven by part number

Oracle Process Manufacturing – OPM.

It the entire product lifecycle for recipe-based manufacturing, from new product development, recipe

management and production, to cost, quality, and regulatory management. It enables you to formulate products to individual customer specifications, manage variability, optimize capacity, and drive continuous process improvement 

2)  What are the modules that come under oracle’s OPM solution?

Answer: OPM includes OPM Process Planning, Product Development(which includes Formula,Recipe,Quality), Production, Financials(Costing,MAC), Logistics, Regulatory Management etc. 

These are products which come under this umbrella..

·         OPM Cost Management

·         OPM Formula Management

·         OPM Intelligence

·         OPM Inventory Management

·         OPM Laboratory Management

·         OPM Master Production Scheduling

·         OPM Material Requirements Planning

·         OPM Production Management

·         OPM Purchasing Management

·         OPM Quality Management

·         OPM Capacity

·         OPM Sales Management

·         Oracle Financial

3)      Name and explain frequently used terms in Oracle Process Manufacturing?

Answer:

4)      Name some important tables used in OPM? ORWhich tables stores the formula information?

------------------------------------------------------------------------

Answer:

1)      select a.FORMULA_ID,a.formula_no,a.FORMULA_DESC1,b.INVENTORY_ITEM_ID,c.description,b.organization_id,decode(b.line_type,-1,’Ingredient’,’Product’) Type

from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c

where a.formula_id=b.FORMULA_ID

and b.ORGANIZATION_ID=:your_Org_id

and a.FORMULA_CLASS<>’COSTING’

and b.INVENTORY_ITEM_ID=c.inventory_item_id

and b.ORGANIZATION_ID=c.organization_id

order by a.FORMULA_ID

from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c

where a.formula_id=b.FORMULA_ID

and b.ORGANIZATION_ID=:your_Org_id

and a.FORMULA_CLASS<>’COSTING’

and b.INVENTORY_ITEM_ID=c.inventory_item_id

and b.ORGANIZATION_ID=c.organization_id

order by a.FORMULA_ID

from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c

where a.formula_id=b.FORMULA_ID

and b.ORGANIZATION_ID=:your_Org_id

and a.FORMULA_CLASS<>’COSTING’

and b.INVENTORY_ITEM_ID=c.inventory_item_id

and b.ORGANIZATION_ID=c.organization_id

order by a.FORMULA_ID

2)      Select b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,decode(c.line_type,-1,’Ingredient’,’Product’) Type,

sum(e.TRANSACTION_QUANTITY) quantity

from apps.GME_BATCH_HEADER a,apps.gmd_recipes b,gmd_recipe_validity_rules grr,apps.gme_material_details c,apps.mtl_system_items d,apps.mtl_material_transactions e

where a.FORMULA_ID=b.FORMULA_ID

and a.ROUTING_ID=b.ROUTING_ID

and a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID

and grr.RECIPE_ID=b.recipe_id

and a.BATCH_ID=c.BATCH_ID

and a.ORGANIZATION_ID=c.ORGANIZATION_ID

and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID

and c.ORGANIZATION_ID=d.organization_id

and a.batch_id=e.TRANSACTION_SOURCE_ID

and a.ORGANIZATION_ID=e.ORGANIZATION_ID

and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID

and a.batch_no in (select batch_no from apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and :to_date)

and a.ORGANIZATION_ID=:your_org_id

and trunc(e.transaction_date) between :from_date and :to_date

group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type

order by RECIPE_DESCRIPTION

sum(e.TRANSACTION_QUANTITY) quantity

from apps.GME_BATCH_HEADER a,apps.gmd_recipes b,gmd_recipe_validity_rules grr,apps.gme_material_details c,apps.mtl_system_items d,apps.mtl_material_transactions e

where a.FORMULA_ID=b.FORMULA_ID

and a.ROUTING_ID=b.ROUTING_ID

and a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID

and grr.RECIPE_ID=b.recipe_id

and a.BATCH_ID=c.BATCH_ID

and a.ORGANIZATION_ID=c.ORGANIZATION_ID

and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID

and c.ORGANIZATION_ID=d.organization_id

and a.batch_id=e.TRANSACTION_SOURCE_ID

and a.ORGANIZATION_ID=e.ORGANIZATION_ID

and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID

and a.batch_no in (select batch_no from apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and :to_date)

and a.ORGANIZATION_ID=:your_org_id

and trunc(e.transaction_date) between :from_date and :to_date

group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type

order by RECIPE_DESCRIPTION

sum(e.TRANSACTION_QUANTITY) quantity

from apps.GME_BATCH_HEADER a,apps.gmd_recipes b,gmd_recipe_validity_rules grr,apps.gme_material_details c,apps.mtl_system_items d,apps.mtl_material_transactions e

where a.FORMULA_ID=b.FORMULA_ID

and a.ROUTING_ID=b.ROUTING_ID

and a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID

and grr.RECIPE_ID=b.recipe_id

and a.BATCH_ID=c.BATCH_ID

and a.ORGANIZATION_ID=c.ORGANIZATION_ID

and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID

and c.ORGANIZATION_ID=d.organization_id

and a.batch_id=e.TRANSACTION_SOURCE_ID

and a.ORGANIZATION_ID=e.ORGANIZATION_ID

and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID

and a.batch_no in (select batch_no from apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and :to_date)

and a.ORGANIZATION_ID=:your_org_id

and trunc(e.transaction_date) between :from_date and :to_date

group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type

order by RECIPE_DESCRIPTION

5)  Explain what do you mean by  Formula and Recipe?

Formula is Ingredients and their proportions

Receipe is Formula + Routing.

6) What are different kinds of losses?

Fixed loss and Variable loss.

Q. Once an item is assigned to an organization, is it possible to remove this association at a later time?

A. If you have assigned an item to an organization, it can only be deleted from that organization if the item has NOT been transacted. If it has been transacted against you cannot delete the item from the organization. You can, however, make the status of the item “inactive” on the main region of the Item Define form INVIDITM in ‘Item Status’ field. This will prevent users from transacting against the item although the item will continue to be included in LOV’s. 

Q. How do you setup Unit of Measure for an item?

A. When transacting an item, it will always default to the defined Primary Unit of Measure, so if you need to use another UOM, you must select it from the List of Values when you transact it. This implies that you want the Primary Unit of Measure to be that which is used most often and which is likely to be the smallest unit of that type of item. Once you have set the primary UOM for an item, it CANNOT be changed.

Q. How do I change an item’s locator control method once I have transacted against it, for example from No Control to Prespecified?

A. You cannot change this attribute if there is onhand quantity for the item. You must first perform a miscellaneous issue of all quantity. Then the attribute can be changed and once the locator(s) are set up, you can perform a miscellaneous receipt to receive the quantity back into a specified subinventory/locator.

 

Q. How do I change an item’s Unit of Measure?

A. You CANNOT change the UOM for an item once the UOM is assigned to the item. The suggested solution is as follows: If the item has not been transacted you can delete the item and then re-add it with the correct UOM. If the item has been transacted and has no outstanding orders, purchase orders or WIP jobs you can do the following if approved by your management.

1. Rename the existing item ’123′ to ’123-Bad UOM’ and save the item. Any prior history for item ’123′ will now be associated with item ’123-Bad UOM’.

2. Add a new item ’123′ with the correct UOM.

 

Q. How do I delete an Item Template I no longer use?

A. Suggested steps to follow to delete unwanted Item Templates:

1. Navigate Setup: Items: Templates – Find Item Templates form appears.

2. Click on the LOV, and select the Item Template that is to be removed.

3. Click the [Find] button on the Find Item Templates form. The Item Templates Summary form appears, with the Item Template selected.

4. Click the [Open] button on the Item Templates Summary form, and the Item Templates form appears.

5. From the menu, select Edit | Delete Record, then select Action | Save and Proceed. 6. Close the Item Templates Summary form. Be careful not to delete Item Templates you may need again as they are not retrievable once deleted.

 

Q. Is there a way to create custom item attributes?

A. No but you can create and define the descriptive flexfield for this form. This will allows you to associate additional data with an item such as an attribute. See the Oracle System Administrators

Users Guide and the Oracle Flexfields Manual for information on creating Descriptive Flexfields.

 

Q. Why does the application prompt you for Organization when you enter the Master Item form for the very first time even though you have only one Master Organization.

A. Once you setup a Multi-Org environment and you enter the form for the first time, there is the possibility there can be more than one Master Organization. The application

therefore prompts you for an organization which is then used to determine the Master Organization associated with it. In other words, you are choosing the Master Organization by choosing one of the Orgs that belongs to it.

 

Q. How do you change the control level for item attributes from master level to organization level for example. When I try to change this in the Attribute Control Form, I get the message: frm-40200: field protected against update.

A. First change value of the field called “status” to “not used”. You should then be able to change the control level of the attribute from master level to org level. Make sure and change to status back to what it was before the change and save the record

 

Q. How do I inactivate an item?

A. On the Master Item Form (Inventory->Items->Master Items), change the item status field to “Inactive” or any another defined status which disables all attributes that make an item usable (stockable, shippable, orderable, etc.). The “Inactivate” status comes pre-seeded with the Application and is for this purpose.

Q. What characters can I use in an item name (including special characters)?

A. The only hard fast rule is not to use the character designated in the segment delimiter for the System Item Flexfield. Also, you will not be able to enter any characters not allowed by the valueset associate with the flexfield. It is recommended that you avoid the use of any char that can be used as a delimiter to avoid future problems. It is best to not use special characters like #@%& if at all possible as these may have a negative impact down the road. Some characters, like percent (%), underscore (_), and pound(#), have special meaning when performing queries by form as do the query operator characters (=!><).

Q. Can you change the costing enabled and inventory asset flags on an item that has already been transacted against even if there is no on-hand quantities for the item?

A. Yes, you can change the flags. If you are changing the flags from checked to unchecked you will receive a warning that the associated cost records will be deleted alerting you that the Cost of Goods will not be calculated any longer for transactions of this item. In effect what you are doing is changing the item from an asset item to an expense item. If you are changing the flags from being unchecked to checked then a database

trigger will fire and cause cost records to be added for this item. There is no warning that is given.

Q. When I create a new item, and choose to assign to All orgs, the application allows me to even though some of those organizations are restricted to my responsibility. Is this the way it is supposed to work? If I assign in an Organization by Organization fashion, I am not allowed to assign to the restricted organizations.

A. The situation described is standard functionality. Even if a responsibility has limited access to a certain number of organizations, the “Assign to All Organizations” check box will assign the item to all orgs, not just the ones limited to the user’s responsibility.

Q. How do you tell that an item is an expense item or asset item?

A. Navigate to the Organization Items form: Inventory->Items->Organization Items. Query up the item in question. Change to the “Costing” alternative region. The value of the “Inventory Asset Value” flag determines this. If checked, the item is an asset item.

Q. Is there a way to delete or disable an item revision once entered?

A. There is no way to delete a revision nor is there a way to disable it. It must remain there for historical data. The suggested workaround is to create a new item, transfer any Quantity on-hand from the old item to new item and delete the old item. The only down side is you will lose some transaction history

Oracle Process Manufacturing Query

Oracle Process Manufacturing Query:


SELECT 'gme_batch_header', gbh.*, 'gme_batch_steps', gbs.*,
       'gme_batch_step_activities', gbsa.*, 'gme_batch_step_resources',
       gbsr.*, 'gmp_process_parameters', gppp.*, 'gme_process_parameters',
       gpp.*
  FROM gmp_process_parameters gppp,
       gme_process_parameters gpp,
       gme_batch_step_resources gbsr,
       gme_batch_step_activities gbsa,
       gme_batch_steps gbs,
       gme_batch_header gbh
 WHERE 1=1
AND gbh.BATCH_STATUS =3
 and gbh.batch_id = gbs.batch_id
   AND gbs.batchstep_id = gbsa.batchstep_id
   AND gbsa.batchstep_activity_id = gbsr.batchstep_activity_id
   AND gbh.batch_id = gpp.batch_id
   AND gbs.batchstep_id = gpp.batchstep_id
   AND gbsa.batchstep_activity_id = gpp.batchstep_activity_id
   AND gbsr.batchstep_resource_id = gpp.batchstep_resource_id
   AND gpp.parameter_id = gppp.parameter_id
   AND gbh.CREATION_DATE >= TO_DATE('01/07/2018 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
   AND gbh.CREATION_DATE <= TO_DATE('05/07/2018 23:59:59', 'DD-MM-YYYY HH24:MI:SS')