Planning Data Pull Worker Performance When Inserting Rows Into MSC_ST_SALES_ORDERS (Doc ID 1313413.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 11.5.10 and later
Oracle Order Management - Version 11.5.10.0 and later
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