For 11.5.10 only - Planning Data Pull Worker Performance When Inserting Rows Into MSC_ST_SALES_ORDERS

(Doc ID 1313413.1)

Last updated on APRIL 06, 2018

Applies to:

Oracle Advanced Supply Chain Planning - Version 11.5.10 to 11.5.10.2 [Release 11.5]
Oracle Order Management - Version 11.5.10.0 to 11.5.10.3 [Release 11.5.10]
Information in this document applies to any platform.
This document only relates to versions of Oracle EBS 11i

Symptoms

Find that the Planning Data Pull (MSCPDP) is taking over 1 hour and 7 minutes to complete. The procedure LOAD_SALES_ORDER(2) is taking 66.6 minutes in the Planning Data Pull Worker (MSCPDPW). The statement that is performing poorly is:

INSERT INTO MSC_ST_SALES_ORDERS ( INVENTORY_ITEM_ID,
SOURCE_INVENTORY_ITEM_ID, ORGANIZATION_ID, PRIMARY_UOM_QUANTITY,
RESERVATION_TYPE, RESERVATION_QUANTITY, DEMAND_SOURCE_TYPE,
DEMAND_SOURCE_HEADER_ID, COMPLETED_QUANTITY, SUBINVENTORY,
DEMAND_CLASS, REQUIREMENT_DATE, DEMAND_SOURCE_LINE,
SOURCE_DEMAND_SOURCE_LINE, DEMAND_SOURCE_DELIVERY, DEMAND_SOURCE_NAME,
DEMAND_ID, SOURCE_DEMAND_ID, PARENT_DEMAND_ID,
SALES_ORDER_NUMBER, FORECAST_VISIBLE, DEMAND_VISIBLE,
SALESREP_CONTACT, SALESREP_ID, CUSTOMER_ID, SHIP_TO_SITE_USE_ID,
BILL_TO_SITE_USE_ID, REQUEST_DATE, PROJECT_ID, TASK_ID,
PLANNING_GROUP, SELLING_PRICE, END_ITEM_UNIT_NUMBER,
ORDERED_ITEM_ID, ORIGINAL_ITEM_ID, LINK_TO_LINE_ID ,
CUST_PO_NUMBER, CUSTOMER_LINE_NUMBER, MFG_LEAD_TIME, ORG_FIRM_FLAG,
SHIP_SET_ID, ARRIVAL_SET_ID, SHIP_SET_NAME, ARRIVAL_SET_NAME,
ATP_REFRESH_NUMBER, DELETED_FLAG, ORIGINAL_SYSTEM_LINE_REFERENCE,
ORIGINAL_SYSTEM_REFERENCE, CTO_FLAG, AVAILABLE_TO_MRP,
DEMAND_PRIORITY, PROMISE_DATE, REFRESH_ID, SR_INSTANCE_ID,
SCHEDULE_ARRIVAL_DATE, LATEST_ACCEPTABLE_DATE, SHIPPING_METHOD_CODE,
ATO_LINE_ID, ORDER_DATE_TYPE_CODE, INTRANSIT_LEAD_TIME) SELECT
x.INVENTORY_ITEM_ID, x.INVENTORY_ITEM_ID, x.ORGANIZATION_ID,
x.PRIMARY_UOM_QUANTITY, x.RESERVATION_TYPE, x.RESERVATION_QUANTITY,
x.DEMAND_SOURCE_TYPE, x.DEMAND_SOURCE_HEADER_ID,
x.COMPLETED_QUANTITY, x.SUBINVENTORY, x.DEMAND_CLASS,
x.REQUIREMENT_DATE, x.DEMAND_SOURCE_LINE, x.DEMAND_SOURCE_LINE,
x.DEMAND_SOURCE_DELIVERY, x.DEMAND_SOURCE_NAME, x.DEMAND_ID,
x.DEMAND_ID, x.PARENT_DEMAND_ID, x.SALES_ORDER_NUMBER,
x.FORECAST_VISIBLE, x.DEMAND_VISIBLE, x.SALESREP_CONTACT,
x.SALESREP_ID, x.CUSTOMER_ID, x.SHIP_TO_SITE_ID, x.BILL_TO_SITE_ID,
x.REQUEST_DATE, x.PROJECT_ID, x.TASK_ID, x.PLANNING_GROUP,
x.LIST_PRICE, x.END_ITEM_UNIT_NUMBER , x.ordered_item_id,
x.ORIGINAL_INVENTORY_ITEM_ID , x.LINK_TO_LINE_ID, x.cust_po_number,
x.customer_line_number,x.MFG_LEAD_TIME,x.FIRM_DEMAND_FLAG,x.SHIP_SET_ID,
x.ARRIVAL_SET_ID,x.SHIP_SET_NAME,x.ARRIVAL_SET_NAME, x.RN1, 2,
x.ORIGINAL_SYSTEM_LINE_REFERENCE, x.ORIGINAL_SYSTEM_REFERENCE,
x.CTO_FLAG, x.AVAILABLE_TO_MRP, x.DEMAND_PRIORITY, x.PROMISE_DATE,
:v_refresh_id, :v_instance_id,x.SCHEDULE_ARRIVAL_DATE,
x.LATEST_ACCEPTABLE_DATE,x.SHIPPING_METHOD_CODE,x.ATO_LINE_ID,
x.ORDER_DATE_TYPE_CODE,x.DELIVERY_LEAD_TIME FROM
MRP_AP2_SALES_ORDERS_V@to_orbit x WHERE x.ORGANIZATION_ID IN (90,84,108,107,
105,106,93,87,83,185,388,447,569,689,690,694,691,697,692,693,695,696,788,
789,790,803,825,834,591)


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms