Performance and Unresponsive Scheduling Order Organizer Tab (Doc ID 1339880.1)

Last updated on JULY 13, 2016

Applies to:

Oracle Order Management - Version 12.1.1 and later
Information in this document applies to any platform.

Symptoms


When Order Organizer Tab is used to query orders across the OU's (OU field left empty), system keeps
searching indefinitely

The trace file shows following long running statement:

SELECT /*+ push_pred(v) */ LINE_NUMBER,SHIPMENT_NUMBER,INVENTORY_ITEM,SOLD_TO,
REQUEST_DATE,ORDERED_QUANTITY,ORDER_QUANTITY_UOM,SCHEDULE_SHIP_DATE,
SHIP_FROM,SHIP_TO,DEMAND_CLASS,ORDER_NUMBER,CUSTOMER_NUMBER,
ITEM_IDENTIFIER_TYPE,ORDERED_QUANTITY2,ORDERED_QUANTITY_UOM2,
PREFERRED_GRADE,ACTUAL_SHIPMENT_DATE,CALCULATE_PRICE_FLAG,LINE_TYPE,
ORDER_TYPE,OPTION_NUMBER,COMPONENT_NUMBER,SERVICE_NUMBER,CANCELLED_QUANTITY,
CANCELLED_QUANTITY2,FULFILLED_QUANTITY,INVOICED_QUANTITY,SHIPPED_QUANTITY,
PROMISE_DATE,SCHEDULE_ARRIVAL_DATE,LATEST_ACCEPTABLE_DATE,
ORIGINAL_INVENTORY_ITEM_ID,ORIGINAL_ORDERED_ITEM_ID,
LATE_DEMAND_PENALTY_FACTOR,ORIGINAL_ITEM_IDENTIFIER_TYPE,
OVERRIDE_ATP_DATE_CODE,SUBINVENTORY,SHIPMENT_PRIORITY,DERIVED_QTY,
DERIVED_QTY2,DERIVED_QTY_UOM,CORRECTED_QTY,CORRECTED_QTY2,PROCESS_FLAG,
FIRM_DEMAND_FLAG,SHIP_TO_CONTACT,DELIVERY_LEAD_TIME,CUST_PO_NUMBER,
CUSTOMER_LINE_NUMBER,CUSTOMER_DOCK_CODE,CUST_MODEL_SERIAL_NUMBER,
CUSTOMER_PRODUCTION_LINE,CUSTOMER_JOB,RLA_SCHEDULE_TYPE_CODE,
AUTHORIZED_TO_SHIP_FLAG,SHIP_TOLERANCE_ABOVE,SHIP_TOLERANCE_BELOW,
END_ITEM_UNIT_NUMBER,MARGIN_PERCENT,PLANNING_PRIORITY,BOOKED_FLAG,
ITEM_TYPE_CODE,SHIPPING_INSTRUCTIONS,PACKING_INSTRUCTIONS,
CUST_PRODUCTION_SEQ_NUM,ITEM_RELATIONSHIP_TYPE,ORDERED_ITEM,
UNIT_SELLING_PERCENT,UNIT_LIST_PERCENT,UNIT_PERCENT_BASE_PRICE,
ORDER_DATE_TYPE_CODE,first_ack_code,last_ack_code,SHIPPING_QUANTITY_UOM,
SHIPPING_QUANTITY,PRICING_QUANTITY_UOM,PRICING_QUANTITY,PRICING_DATE,
TAX_EXEMPT_NUMBER,TAX_CODE,TAX_DATE,ITEM_SUBSTITUTION_TYPE_CODE,
DEMAND_CLASS_CODE,TAX_EXEMPT_FLAG,FREIGHT_TERMS_CODE,SHIPMENT_PRIORITY_CODE,
SHIPPING_METHOD_CODE,TAX_POINT_CODE,FOB_POINT_CODE,UNIT_SELLING_PRICE,
TAX_VALUE,UNIT_LIST_PRICE,SCHEDULE_STATUS_CODE,TAX_EXEMPT_REASON_CODE,
SOURCE_DOCUMENT_TYPE_ID,SOURCE_DOCUMENT_ID,SOURCE_DOCUMENT_LINE_ID,ROW_ID,
LINE_ID,ORG_ID,HEADER_ID,LINE_TYPE_ID,ORDER_TYPE_ID,CONFIG_HEADER_ID,
CONFIG_REV_NBR,SHIP_FROM_ORG_ID,SHIP_TO_ORG_ID,DELIVER_TO_ORG_ID,
INTMED_SHIP_TO_ORG_ID,INVOICE_TO_ORG_ID,SHIP_TO_CONTACT_ID,
DELIVER_TO_CONTACT_ID,COMMITMENT_ID,INTMED_SHIP_TO_CONTACT_ID,
INVOICE_TO_CONTACT_ID,ORDERED_ITEM_ID,INVENTORY_ITEM_ID,PRICE_LIST_ID,
AGREEMENT_ID,ATO_LINE_ID,PAYMENT_TERM_ID,INVOICING_RULE_ID,
CREDIT_INVOICE_LINE_ID,ACCOUNTING_RULE_ID,CONTEXT,SPLIT_FROM_LINE_ID,
LINE_SET_ID,SPLIT_BY,MODEL_REMNANT_FLAG,VEH_CUS_ITEM_CUM_KEY_ID,SALESREP_ID,
ARRIVAL_SET_ID,SHIP_SET_ID,ORDER_SOURCE_ID,OVER_SHIP_REASON_CODE,
FULFILLED_FLAG,FULFILLMENT_METHOD_CODE,FLOW_STATUS_CODE,
OVER_SHIP_RESOLVED_FLAG,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,
ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,
ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,ATTRIBUTE16,
ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,GLOBAL_ATTRIBUTE2,GLOBAL_ATTRIBUTE3,GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,GLOBAL_ATTRIBUTE6,GLOBAL_ATTRIBUTE7,GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,GLOBAL_ATTRIBUTE10,GLOBAL_ATTRIBUTE11,GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,GLOBAL_ATTRIBUTE14,GLOBAL_ATTRIBUTE15,GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,GLOBAL_ATTRIBUTE18,GLOBAL_ATTRIBUTE19,GLOBAL_ATTRIBUTE20,
INDUSTRY_CONTEXT,INDUSTRY_ATTRIBUTE1,INDUSTRY_ATTRIBUTE2,
INDUSTRY_ATTRIBUTE3,INDUSTRY_ATTRIBUTE4,INDUSTRY_ATTRIBUTE5,
INDUSTRY_ATTRIBUTE6,INDUSTRY_ATTRIBUTE7,INDUSTRY_ATTRIBUTE8,
INDUSTRY_ATTRIBUTE9,INDUSTRY_ATTRIBUTE10,INDUSTRY_ATTRIBUTE11,
INDUSTRY_ATTRIBUTE13,INDUSTRY_ATTRIBUTE12,INDUSTRY_ATTRIBUTE14,
INDUSTRY_ATTRIBUTE15,INDUSTRY_ATTRIBUTE16,INDUSTRY_ATTRIBUTE17,
INDUSTRY_ATTRIBUTE18,INDUSTRY_ATTRIBUTE19,INDUSTRY_ATTRIBUTE20,
INDUSTRY_ATTRIBUTE21,INDUSTRY_ATTRIBUTE22,INDUSTRY_ATTRIBUTE23,
INDUSTRY_ATTRIBUTE24,INDUSTRY_ATTRIBUTE25,INDUSTRY_ATTRIBUTE26,
INDUSTRY_ATTRIBUTE27,INDUSTRY_ATTRIBUTE28,INDUSTRY_ATTRIBUTE29,
INDUSTRY_ATTRIBUTE30,TP_CONTEXT,TP_ATTRIBUTE1,TP_ATTRIBUTE2,TP_ATTRIBUTE3,
TP_ATTRIBUTE4,TP_ATTRIBUTE5,TP_ATTRIBUTE6,TP_ATTRIBUTE7,TP_ATTRIBUTE8,
TP_ATTRIBUTE9,TP_ATTRIBUTE10,TP_ATTRIBUTE11,TP_ATTRIBUTE12,TP_ATTRIBUTE13,
TP_ATTRIBUTE14,TP_ATTRIBUTE15,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,
LAST_UPDATED_BY,LAST_UPDATE_LOGIN,PROGRAM_APPLICATION_ID,PROGRAM_ID,
PROGRAM_UPDATE_DATE,REQUEST_ID,COMPONENT_SEQUENCE_ID,CONFIGURATION_ID,
TOP_MODEL_LINE_ID,LINK_TO_LINE_ID,CONFIG_DISPLAY_SEQUENCE,COMPONENT_CODE,
SOLD_TO_ORG_ID,FREIGHT_CARRIER_CODE,DEMAND_BUCKET_TYPE_CODE,
SOURCE_TYPE_CODE,PROJECT_ID,TASK_ID,INVOICE_INTERFACE_STATUS_CODE,TAX_RATE,
ITEM_REVISION,LINE_CATEGORY_CODE,CUSTOMER_TRX_LINE_ID,OPTION_FLAG,
VISIBLE_DEMAND_FLAG,CANCELLED_FLAG,OPEN_FLAG,LOCK_CONTROL,
ORIG_SYS_DOCUMENT_REF,ORIG_SYS_LINE_REF
FROM
OE_SCH_ORDER_LINES_V WHERE open_flag = 'Y' AND line_id in (select /*+
push_subq unnest leading(h) index(h OE_ORDER_HEADERS_N8) use_nl(l)*/
l.line_id from oe_order_headers h, oe_order_lines l where h.header_id =
l.header_id AND l.open_flag ='Y' AND h.open_flag ='Y' AND
l.line_category_code ='ORDER' AND nvl(l.item_type_code, 'STANDARD') <>
'SERVICE' AND l.shipped_quantity is null AND nvl(l.cancelled_flag, 'N') =
'N' AND nvl(l.ordered_quantity, 0) <> 0 AND nvl(l.transaction_phase_code,
'F')='F' and l.inventory_item_id = :1 and l.ship_from_org_id = :2 and
l.source_type_code = 'INTERNAL') order by ORG_ID,ORDER_NUMBER desc,
LINE_NUMBER, SHIPMENT_NUMBER, nvl(Option_Number,-1),nvl(COMPONENT_NUMBER,-1)

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