What you should know about Oracle OPM products

Oracle OPM designed exclusively for catering the business need for process manufacturers industry like:

• Consumer Packaged Goods
  • Food, Beverage, Health and Beauty, Household Products
• Industrial Products
  • Chemical, Pharmaceutical, Petroleum, Metals, Mining, Paper and Glass

Is there any difference from Oracle Discrete Manufacturing?

Yes, there is ..If you came from Discrete manufacturing background, these are one -to-one mapping between modules.

Overview of Procure to Pay Cycle in Oracle Apps

Procure to Pay Lifecycle is one of the important Process in Oracle Applications. Procure to Pay means Procuring Raw Materials required to manufacture the final or finished Goods from a Supplier to Paying the Supplier from whom the material was purchased. But this process is not just two steps. It involves many steps. Let’s see the steps and Oracle Application involved in performing those steps.

1.Creation Of Purchase Requisition
Requisition is nothing but a formal request to buy something. Requisitions represent the demand for any goods or services that an organization needs.

Requisitions for goods and services:

• Are generated by applications including Inventory, Work in Process (WIP), Material Requirements Planning (MRP) and Order Management.
• May be entered manually through Purchasing windows.
• May be entered using iProcurement.
• May be imported from external systems.

There are two types of Requisitions
1] Internal Requisition – Basically used when there is a Requirement from One Inventory Organization to the Other Inventory Organization (Inter- Organization Transfer)
2] Purchase Requisition – Basically used when there is a Requirement to be fulfilled by External Sources i.e. Suppliers, Requirement from MRP, Requirement from WIP, Requirement from Sales Order etc.
With Oracle Purchasing module, you can create, edit, and review requisition information on-line.  Then the Requisitions went for approvals from proper authorities.
2. Creation of Purchase Order
Based on the Purchase Requisition and its approval next we have to create a Purchase order to buy the item.

Purchasing supports four types of purchase orders:
1] Standard: Create standard purchase orders for one-time purchases of various items. You create standard purchase orders when you know the details of the goods or services you require, estimated costs, quantities, delivery schedules, and accounting distributions.
2] Blanket: Create blanket purchase agreements when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not yet know the detail of your delivery schedules.
3] Contract: Create contract purchase agreements with your suppliers to agree on specific terms and conditions without indicating the goods and services that you will be purchasing.
4] Planned: A planned purchase order is a long-term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.
Once purchase orders are created, they may be submitted for approval. The approval process checks to see if the submitter has sufficient authority to approve the purchase order.  Once the document is approved, it may be sent to your supplier using a variety of methods including: printed document, EDI, fax, e-mail, iSupplier Portal and XML. Once the purchase order or release is sent to your supplier, they are authorized to ship goods at the times and to the locations that have been agreed upon.
3. Receipt of Material
After receiving the PO, the supplier will send the items.

Purchasing lets you control the items you order through receiving, inspection, transfer, and internal delivery. You can use these features to control the quantity, quality, and internal delivery of the items you receive.
4. Creation of Payables Invoice
Once you’ve received goods or service from your supplier, you’ll also receive an invoice.  Using Payables you can record invoices in a number of different ways.

With Payables you can:

• Import/Enter invoices manually, either individually or in batches.
• Use Quick Invoices for rapid, high-volume entry of standard invoices and credit memos that are not complex and do not require extensive online validation.
• Automate invoice creation for periodic invoices using the Recurring Invoice functionality.
• Use iExpenses to enter employee expense reports using a web browser.
• Import EDI invoices processed with the e-Commerce Gateway.
• Import XML invoices.
• Match invoices to purchase orders or receipts to ensure you only pay what you’re supposed to be paying for.

5. Payment to Supplier
Once invoices are validated, they can be paid. Payables integrates with Oracle Payments, the E-Business Suite payment engine, to handle every form of payment, including checks, manual payments, wire transfers, EDI payments, bank drafts, and electronic funds transfers.  Payables also integrates with Oracle Cash Management to support automatic or manual reconciliation of your payments with bank statements sent by the bank.
This is how the P2P Cycle occurs in Oracle Apps.

SQL Query for Oracle OPM Receipe and Formula Dump

SQL Query for Oracle OPM Receipe and Formula Dump

select gmd.RECIPE_NO Receipe_code,RECIPE_DESCRIPTION Receipe_Name ,gmd.RECIPE_VERSION,
(select distinct description from mtl_system_items mtl1,FM_MATL_DTL fd1
where fd1.line_type=1
and fd1.formula_id=fd.formula_id
and fd1.inventory_item_id = mtl1.inventory_item_id
and fd1.organization_id= mtl1.organization_id) product,
fm.formula_no,fm.FORMULA_DESC1,fm.FORMULA_VERS,
decode (gmd.RECIPE_STATUS,'900', 'Frozen','700','Approved for General Use') Rec_Status,
b.segment1||'-'||b.segment2 Item_code,
b.description item_desc,
fd.line_no,
fd.qty
from FM_FORM_MST fm,
FM_MATL_DTL fd,
mtl_system_items b,
GMD_RECIPE_VALIDITY_RULES GV,
gmd_recipes gmd
where  fd.formula_id=fm.formula_id
and fd.organization_id=fm.OWNER_ORGANIZATION_ID
and fm.formula_id =gmd.formula_id
and fm.owner_organization_id=gmd.owner_organization_id
AND GV.RECIPE_ID=GMd.RECIPE_ID
and b.inventory_item_id=fd.inventory_item_id
and b.organization_id=fm.OWNER_ORGANIZATION_ID
and fm.OWNER_ORGANIZATION_ID in (XXXX)
and fd.line_type in (-1,2)
--and fm.FORMULA_STATUS=700
and gmd.RECIPE_STATUS in (700)
--and gmd.RECIPE_NO=''XXXX
order by 1

Query to find Pegging Details in ASCP

SELECT mso.sales_order_number, md1.*,
MSI1.ITEM_NAME||' ('||MSI1.INVENTORY_ITEM_ID||')' "COMPONENT (ID)"
, substr( md1.order_number,1,22) order_num
, MS1.NEW_ORDER_QUANTITY COMP_QTY -- this is the pegged or previously pegged column
, decode (ms1.order_type,'1','Purchase order'
,'11','Intransit shipment'
,'12','Intransit receipt'
,'13','Suggested repetitive schedule'
,'14','Discrete job co-product/by-product'
,'15','Nonstandard job by-product'
,'16','Repetitive schedule by-product'
,'17','Planned order co-product/by-product'
,'18','On Hand'
,'2','Purchase requisition'
,'27','Flow schedule'
,'28','Flow schedule by-product'
,'29','Payback Supply'
,'3','Discrete job'
,'30','Current repetitive schedule'
,'32','Returns'
,'4','Suggested aggregate repetitive schedule'
,'41','User Supply'
,'45','Demand Class Consumption'
,'46','Supply Due To Stealing'
,'47','Demand Due To Stealing'
,'48','Supply Adjustment'
,'49','PO Acknowledgment'
,'5','Planned order'
,'50','ATP Aggregate Supply'
,'51','Planned inbound shipment'
,'52','Requested inbound shipment'
,'53','Internal requisition'
,'60','Order Rescheduling Adjustment'
,'7','Non-standard job'
,'70','Maintenance Work Order'
,'8','PO in receiving') Order_type
-- , decode (MFP1.supply_type, 1, 'Purchase Order', 2, 'Purchase Requisition', 3, 'Work Order', 11, 'Intransit Shipment', 12, 'Intransit receipt') Supply_type
-- ,decode (MFP2.supply_type, 1, 'Purchase Order', 2, 'Purchase Requisition', 3, 'Work Order', 11, 'Intransit Shipment', 12, 'Intransit receipt') Supply_type2
, MFP1.demand_quantity
--, MFP1.supply_quantity
, MFP1.demand_date
, MFP1.supply_date
, ROUND(MFP1.ALLOCATED_QUANTITY,1) COMP_ALLOC
, decode(MD1.origination_type, '29','Peg_Fcast','30','Peg_Sales') Pegged_To
, decode (MFP1.DEMAND_ID, -1,'peg_Excess',-2,'Peg_SS') Pegged_To_1
, MFP2.ORGANIZATION_ID
,MFP1.ORGANIZATION_ID
, decode(MD2.origination_type, '29','Peg_Fcast','30','Peg_Sales') Pegged_To_2
, TRUNC(MS1.NEW_SCHEDULE_DATE) COMP_SUP_DUE
, 'X'
, DECODE(MD2.ORDER_NUMBER,NULL
,TO_CHAR(MD2.DEMAND_ID)
,(SUBSTR(MD2.ORDER_NUMBER,1,14)||SUBSTR(MD2.ORDER_NUMBER,42,5))) SO_NUM
, MSI2.ITEM_NAME||' ('||MSI2.INVENTORY_ITEM_ID||')' "ASSEMBLY (ID)"
-- , MSI2.DESCRIPTION
, TO_CHAR(MD2.REQUEST_SHIP_DATE,'DD/MM/YYYY') RDATE
, TRUNC(MD2.USING_ASSEMBLY_DEMAND_DATE) SSDATE
, MD1.Creation_date
, ROUND((MD2.DMD_SATISFIED_DATE-MD2.USING_ASSEMBLY_DEMAND_DATE),1) LATE
, MD2.DEMAND_PRIORITY PRIORITY
, ROUND(MFP2.DEMAND_QUANTITY,1) so_QTY
, ROUND(MFP2.ALLOCATED_QUANTITY,1) WIP_QTY
, TRUNC(MD1.OLD_DEMAND_DATE) ULSD
, TRUNC(MD1.USING_ASSEMBLY_DEMAND_DATE) COMP_DEM_DUE
FROM APPS.MSC_SUPPLIES MS1 -- COMP
, APPS.MSC_SYSTEM_ITEMS MSI1 -- COMP
, APPS.MSC_FULL_PEGGING MFP1 -- COMP
, apps.msc_sales_orders mso
, (SELECT *
FROM APPS.MSC_DEMANDS
WHERE SR_INSTANCE_ID =&1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MD1 -- COMP
, (SELECT *
FROM APPS.MSC_FULL_PEGGING
WHERE SR_INSTANCE_ID =&1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MFP2 -- FG
, (SELECT *
FROM APPS.MSC_DEMANDS
WHERE SR_INSTANCE_ID =&1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MD2 -- FG
, (SELECT *
FROM APPS.MSC_SYSTEM_ITEMS
WHERE SR_INSTANCE_ID = &1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MSI2 -- FG LEVEL
WHERE 1=1
AND MS1.SR_INSTANCE_ID =&1
AND MS1.ORGANIZATION_ID =&2
AND MS1.PLAN_ID =&3
and mso.demand_id=md1.demand_id
AND MSI1.SR_INSTANCE_ID = MS1.SR_INSTANCE_ID
AND MSI1.ORGANIZATION_ID = MS1.ORGANIZATION_ID
AND MSI1.PLAN_ID = MS1.PLAN_ID
AND MSI1.INVENTORY_ITEM_ID = MS1.INVENTORY_ITEM_ID
AND MFP1.SR_INSTANCE_ID = MS1.SR_INSTANCE_ID
AND MFP1.ORGANIZATION_ID = MS1.ORGANIZATION_ID
AND MFP1.PLAN_ID = MS1.PLAN_ID
AND MFP1.TRANSACTION_ID = MS1.TRANSACTION_ID
AND MD1.DEMAND_ID (+) = MFP1.DEMAND_ID
AND MFP2.PEGGING_ID (+) = MFP1.PREV_PEGGING_ID
AND MD2.DEMAND_ID (+) = MFP2.DEMAND_ID
AND MSI2.INVENTORY_ITEM_ID (+) = MFP2.INVENTORY_ITEM_ID
AND MSI1.ITEM_NAME = 'item'
and md1.order_number='ord'
ORDER BY MS1.OLD_SCHEDULE_DATE, MD2.REQUEST_SHIP_DATE

What are all Oracle ASCP Plan Types?

In R12, you can launch three type of plans:

1. Production Plan
2. Manufacturing Plan
3. Master Plan

For MPS Planning item level, you should run Production Plan

And for MRP Planning Item, you should run Manufacturing plan.

Please see the explanation from Dev in similar question from other customers in Oracle Technical forum:

"The below are the 3 plan types :
1. MPP (Master Plan)
2. MPS (Production Plan)
3. MRP (Manufacturing Plan)

A. In 11.5.9, we had three plan types: DRP (Distribution Plan), MPS (Production Plan) and MRP (Manufacturing Plan)

B. In 11.5.10, we had three plan types: MPP, MPS and MRP. We changed the DRP name to MPP.
Why? Because in R12, we introduced a new Distribution Planning engine and having the term DRP used for the old plan type would have caused more confusion than MPP."

What is Batch Close Variance in OPM?

Batch Close Variances can occur in the following situations when using Actual Costing,

• If the batch was released in one cost period and the debit to WIP is valued at one cost, but the batch was completed in a later cost period when the credit to WIP for the same quantities is valued at a different cost.
  This results in a left over balance WIP due to the cost change and must be cleared out.

• Batch is released in one period and closed in the next period and has ingredient issues after last product yield and no product yield in next period.

• When the Ingredient consumptions and product yields are recorded in a period, and in the next period the ingredient, resource, or byproduct consumptions for these batches are updated without any further product yields.

• Ingredient issued in one period and returned back in next period with cost changes for ingredient across periods.
  - Essentially Ingredient/By-product transactions should be posted prior to product transactions in general, and certainly at least prior to last of product transaction so that such transactions do not contribute to batch close variance.

• Cost Allocation factors on the Formula are not same as Cost Allocation Factors on the Batch Material details which could happen when using Dynamic Cost Allocation factors (profile option - GMF: Cost Allocation Factor Calculation - set to "Dynamic")
  Any cost allocation factor change made after a Batch is released ( as it happens in case of dynamic cost allocation factors) would require re-layering.
  Re-layering these Batches followed by running the Actual Cost Process, Cost Update and OPM-Preprocessor will eliminate the Batch Close Variances for these Batches.

• The profile option - 'GMF: Batch Actual Cost Calculation Basis' is set to 'Use Virtual Incremental Backflush Quantities' for a situation where on a Batch all the Ingredients and Resources are not issued out Upfront.
  Virtual Incremental Backflush is essentially designed to address a common situation in process industries where most or all ingredients are issued upfront and there are multiple product yields. 
  Without using the Virtual Incremental Backflush, the first yield would be posted at very high cost and subsequent yields would be posted at zero cost.
  With Virtual Incremental Back flush, costs are apportioned based on the Formula setup even before transactions occur.
  Thus if those transactions do not actually occur prior to batch close, Batch Close Variances would result.

• Use of Virtual Incremental Backflush with consumption activity and cost changes for ingredients across periods.

Important OPM Process Execution & Inventory Tables in R12

OPM PROCESS EXECUTION TABLES R12

• GME_BATCH_HEADER
• GME_BATCH_HEADER_MIG
• GME_BATCH_HISTORY
• GME_BATCH_MAPPING_MIG
• GME_BATCH_SALES_ORDERS
• GME_BATCH_STEPS
• GME_BATCH_STEPS_MIG
• GME_BATCH_STEP_ACTIVITIES
• GME_BATCH_STEP_ACTIV_MIG
• GME_BATCH_STEP_CHARGES
• GME_BATCH_STEP_CHARGES_MIG
• GME_BATCH_STEP_DEPENDENCIES
• GME_BATCH_STEP_DEP_MIG
• GME_BATCH_STEP_ITEMS
• GME_BATCH_STEP_ITEMS_MIG
• GME_BATCH_STEP_RESOURCES
• GME_BATCH_STEP_RESOURCES_MIG
• GME_BATCH_STEP_RSRC_SUMMARY
• GME_BATCH_STEP_TRANSFERS
• GME_BATCH_STEP_TRANSFERS_MIG
• GME_BATCH_TXNS_MIG
• GME_ERES_GTMP
• GME_EXCEPTIONS_GTMP
• GME_GANTT_DOCUMENT_FILTER
• GME_INVENTORY_TXNS_GTMP
• GME_LAB_BATCH_LOTS
• GME_MATERIAL_DETAILS
• GME_MATERIAL_DETAILS_MIG
• GME_MATERIAL_DISPENSING_GTMP
• GME_MIGRATION_CONTROL
• GME_PARAMETERS
• GME_PENDING_PRODUCT_LOTS
• GME_PROCESS_PARAMETERS
• GME_PROCESS_PARAMETERS_MIG
• GME_RESOURCE_TXNS
• GME_RESOURCE_TXNS_GTMP
• GME_RESOURCE_TXNS_MIG
• GME_RESOURCE_TXNS_SUMMARY
• GME_SCALE_DETAIL
• GME_TEMP_EXCEPTIONS
• GME_TEXT_HEADER
• GME_TEXT__TL
• GME_TRANSACTION_PAIRS
• GME_UNALLOCATED_ITEMS_GTMP

OPM INVENTORY TABLE R12

• BISOPM_ONHANDSALE_SUM
• BIS_OPM_PROD_SUM
• GMI_AUTO_ALLOCATION_BATCH
• GMI_CATEGORY_SETS
• GMI_CLOS_WAREHOUSES
• GMI_DISCRETE_TRANSFERS
• GMI_DISCRETE_TRANSFER_LINES
• GMI_DISCRETE_TRANSFER_LOTS
• GMI_ITEMS_XML_INTERFACE
• GMI_ITEM_CATEGORIES
• GMI_ITEM_CONV_AUDIT
• GMI_ITEM_CONV_AUDIT_DETAILS
• GMI_ITEM_ORGANIZATIONS
• GMI_LOTS_CONV_XML_INTERFACE
• GMI_LOTS_XML_INTERFACE
• GMI_LOT_TRACE
• GMI_MASSTRANS_CTL
• GMI_MIGRATION_PARAMETERS
• GMI_OBSOLETE_ITEM_COLUMNS
• GMI_QUANTITY_XML_INTERFACE
• GMI_SUBLOT_GENERATE
• GMI_TRAN_TMP
• IC_ADJS_JNL
• IC_ALLC_CLS
• IC_ALOT_PRM
• IC_CLDR_DTL
• IC_CLDR_HDR
• IC_CLDR_HDR_B
• IC_CLDR_HDR_TL
• IC_COMD_CDS
• IC_COST_CLS
• IC_CRUL_CLS
• IC_CTMS_CLS
• IC_CYCL_ADT
• IC_CYCL_DTL
• IC_CYCL_ERR
• IC_CYCL_HDR
• IC_CYCL_MSC
• IC_DECM_MST
• IC_FRGT_CLS
• IC_GLED_CLS
• IC_INVN_CLS
• IC_INVN_TYP
• IC_ITEM_CDT
• IC_ITEM_CNV
• IC_ITEM_CPG
• IC_ITEM_HIERARCHY
• IC_ITEM_MST
• IC_ITEM_MST_B
• IC_ITEM_MST_B_MIG
• IC_ITEM_MST_TL
• IC_ITEM_WHS
• IC_JRNL_MST
• IC_LOCT_INV
• IC_LOCT_MST
• IC_LOTS_CPG
• IC_LOTS_MST
• IC_LOTS_MST_MIG
• IC_LOTS_STS
• IC_PERD_BAL
• IC_PHYS_CNT
• IC_PHYS_ERR
• IC_PKGS_MST
• IC_PLNT_INV
• IC_PRCE_CLS
• IC_PRCH_CLS
• IC_PURG_PRM
• IC_RANK_MST
• IC_SALE_CLS
• IC_SHIP_CLS
• IC_STAT_RPT
• IC_STOR_CLS
• IC_SUMM_INV
• IC_TAXN_ASC
• IC_TAXN_CLS
• IC_TEXT_HDR
• IC_TEXT_TBL_TL
• IC_TRAN_ARC
• IC_TRAN_CMP
• IC_TRAN_PND
• IC_TXN_REQUEST_HEADERS
• IC_TXN_REQUEST_LINES
• IC_WHSE_INV
• IC_WHSE_MST
• IC_WHSE_REL
• IC_WHSE_STS
• IC_XFER_MST
• IN_ADDR_MST
• IN_CTRY_MST
• IN_ITEM_MST
• IN_REGN_MST
• IN_STAT_CTL
• IN_STAT_DTL
• IN_STAT_HDR
• IN_STAT_MSG
• IN_TEXT_HDR
• IN_TEXT_TBL_TL
• IN_TOFD_MST
• SY_WF_ITEM_ROLES