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

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 SCRIPTThe following SQL could be used to help identify the issue on open sales orders with particular organization and item.

STEPSThis 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.ERRORNo Mtl_Sales_Order ID found for oe headerLOG SNIPPETHere 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.%20Retest%20the%20issue. %20 %204.%20Confirm%20if%20a%20datafix%20is%20required%20--%20 If%20existing%20orders%20with%20this%20issue%20cannot%20be%20cancelled%20and%20recreated,%20a%20 datafix%20from%20Oracle%20Support%20is%20required.%20Please%20log%20a%20Service%20Request%20to%20 correct%20the%20data%20in%20MTL_SALES_ORDERS.%20When%20logging%20the%20SR,%20provide%20 Oracle%20Support%20with%20the%20following%20SQL%20results:SELECT%20distinct%20a.order_number,%20a.header_id,%20a.order_type_id FROM%20oe_order_headers_all%20a,%20oe_order_lines_all%20c WHERE%20c.INVENTORY_ITEM_ID%20=%20NVL('&EnterItemID',%20c.INVENTORY_ITEM_ID) AND%20c.SHIP_FROM_ORG_ID%20=%20NVL('&EnterOrgID',%20c.SHIP_FROM_ORG_ID) AND%20a.header_id%20=%20c.header_id and%20a.OPEN_FLAG%20=%20'Y' and%20NOT%20EXISTS (SELECT%201 FROM%20mtl_sales_orders%20b WHERE%20b.segment1%20=%20to_char(a.order_number));

Case%202:%20Record%20Completely%20Missing%20from%20MTL_SALES_ORDERS

In%20the%20second%20case,%20the%20record%20is%20completely%20missing%20from%20the%20MTL_SALES_ORDERS.%20Take%20the%20following%20steps: %20 %201.%20Confirm%20that%20the%20order%20is%20not%20in%20the%20MTL_SALES_ORDERS%20table%20with%20the%20following%20SQL:

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

References