No Sales Order Id Found in MTL_SALES_ORDERS (Datafix #23) (文档 ID 281836.1)

Applies to:

Symptoms

The Pick Release Process (WSHPSGL) completes in Warning with the following error message.
The pick release does not succeed for the item.
The sales order is missing from the MTL_SALES_ORDERS table.

IDENTIFICATION SCRIPT

The following SQL could be used to help identify the issue on open sales orders with particular organization and item.

STEPS

This occurs for multiple orders and can be reproduced at will.

1. Create/book a sales order.
2. Navigate to Order Management > Shipping > Pick release
3. Run pick release.
4. The program (Pick Select List Generation) completes in warning.

ERROR

No Mtl_Sales_Order ID found for oe header

LOG SNIPPET

Here is a snippet from the log file:

Cause

Here are two possible causes:
1. Case #1: The 'Order Type' (SEGMENT2) from the Key flexfield 'Sales Orders'
had the Format Validation checkbox 'Uppercase Only (A-Z)' set to yes.
The setting caused the issue as order type was actually in mixed case
but the value was saved into the MTL_SALES_ORDERS table as uppercase.

 2. Case #2: The data could simply be missing from the MTL_SALES_ORDERS  table. This could be a result of a migration issue, database failure,  etc. An exact cause has not been determined.

Solution

Case 1: Order Type is Mixed Case But Segment is Uppercase

In the first case, the Order Type is Mixed Case But Segment is Uppercase. To correct this issue, take the following steps:
1. Set the value to No for 'Uppercase Only (A-Z)' in the 'Order Type' (SEGMENT2)  from the Key flexfield 'Sales Orders'.

2. Compile the flexfield

3. Retest the issue.

4. Confirm if a datafix is required --  If existing orders with this issue cannot be cancelled and recreated, a  datafix from Oracle Support is required. Please log a Service Request to  correct the data in MTL_SALES_ORDERS. When logging the SR, provide  Oracle Support with the following SQL results:

SELECT distinct a.order_number, a.header_id, a.order_type_id
FROM oe_order_headers_all a, oe_order_lines_all c
WHERE c.INVENTORY_ITEM_ID = NVL('&EnterItemID', c.INVENTORY_ITEM_ID)
AND c.SHIP_FROM_ORG_ID = NVL('&EnterOrgID', c.SHIP_FROM_ORG_ID)
AND a.header_id = c.header_id
and a.OPEN_FLAG = 'Y'
and NOT EXISTS
(SELECT 1
FROM mtl_sales_orders b
WHERE b.segment1 = to_char(a.order_number));

Case 2: Record Completely Missing from MTL_SALES_ORDERS

In the second case, the record is completely missing from the MTL_SALES_ORDERS. Take the following steps:

1. Confirm that the order is not in the MTL_SALES_ORDERS table with the following SQL:

2. If no value exists in MTL_SALES_ORDERS, a datafix is required. If  existing orders with this issue cannot be cancelled and recreated or if  this issue can be reproduced, a data or code fix from Oracle Support is  required. Please check for high priority patches that may already  resolve the issue. If none are found, please log a Service Request to  correct the data in MTL_SALES_ORDERS.

References