Jumbled Order-Handling

How to Handle Jumbled Order?

Question: What is jumbled ( or mangled) Order?

Answer: Jumbled Order is the order for which delivery information and order information is not in sync. The typical case we are going to discuss is as below.

Case: Say the order has 4 lines with different item and quantity 10 for each line. Line 3 splits into two lines with 7 and 3 quantities.

e.g.

Line# ----- Item ----- Quantity

1.1 ------ AAAA ---- 10

2.1 ------ BBBB ---- 10

3.1 ------ CCCC ---- 7

3.2 ------ CCCC ---- 3

4.1 ------ DDDD ---- 10

Line# 3.2 will have reference of line 3.1. SPLIT_FROM_LINE_ID column of OE_ORDER_LINES_ALL table. i.e LINE_ID of 3.1 will be populated in SPLIT_FROM_LINE_ID of line 3.2

Now shipment happens for line 1.1, 2.1, 3.1 and 4.1 later 3.2 gets shipped. For all shipments details goes to table WSH_DELIVERY_DETAILS with LINE_ID populated in column named SOURCE_LINE_ID. After completing Interface Trip Stop entries gets posted to Inventory and order line gets closed. Also gets WSH_DELIVERY_DETAILS.OE_INTERFACED_FLAG updated with ‘Y’

But in some cases it does not work as usual hence we call it as Jumbled order. Most of the time this happens for the lines which are split lines (e.g. Line 3.2 in above case), in those cases order line gets shipped properly but does not get closed. The reason behind this is, record/s for that particular split order line in WSH_DELIVERY_DETAILS , carries LINE_ID of parent line (e.g. Line 3.1 in above case) in column SOURCE_LINE_ID also line number (i.e. 3.1) gets populated in SOURCE_LINE_NUMBER which correctly should be 3.2.

Now when Interface Trip Stop runs it completes and closes the trip as well as posts entries to inventory, when the Oracle turn to close order line it does not get proper reference in table WSH_DELIVERY_DETAILS and hence it fails to complete other operations on order line. The result of it is order line remains open even if everything is complete. Workflow background engine also plays important role in posting entries in different application / tables.

To fix this issue we need to feed the correct information to Oracle through a script, and then run program so that in again processes the record/s correctly and closes the line/s.

Solution: Correct data in two WSH_DELIVERY_DETAILS and WSH_TRIP_STOPS tables and run Interface Trip Stop (Most of the time this program is scheduled)

1. Fix data in table WSH_DELIVERY_DETAILS .

1. This requires to know LINE_ID and line number of the line for which issue explained above had occurred. Run the script blow to find LINE_ID

select

to_number(ool.line_number'.'ool.shipment_number) line_number,

ooh.order_number,

ool.line_id,

ool.split_from_line_id,

msi.segment1 item_number,

ool.ordered_quantity ord_qty,

ool.cancelled_quantity can_qty,

ool.shipped_quantity shp_qty,

ool.flow_status_code,

ool.org_id

from apps.MTL_SYSTEM_ITEMS msi,

apps.OE_ORDER_LINES_ALL ool,

apps.OE_ORDER_HEADERS_ALL ooh

where ooh.order_number = &order_num

and ooh.org_id in (:org_id)

and ool.header_id = ooh.header_id

and msi.organization_id in (:master_organization_id)

and msi.inventory_item_id = ool.inventory_item_id

order by to_number(ool.line_number'.'ool.shipment_number);

2. Also is DELIVERY_DETAIL_ID is required which can be obtained by running script below and pick the appropriate DELIVERY_DETAIL_ID depending on ITEM and its quantity. Most probably; correct line number or LINE_ID you will not get here, it will be of parent line from which the line got a split so ignore and looks for on ITEM and its quantity. Here note DELIVERY_ID also which will help to find STOP_ID from table WSH_TRIP_STOPS

select wnd.DELIVERY_ID,

wnd.status_code st,

wda.delivery_assignment_id wdai,

wdd.delivery_detail_id ,

wdd.move_order_line_id moli,

to_number(ool.line_number'.'ool.shipment_number) ola_ln,

wdd.source_line_number wdd_ln,

wdd.source_line_id linid,

wdd.released_status rs,

wdd.inv_interfaced_flag iv,

wdd.oe_interfaced_flag io,

wdd.requested_quantity reqqty,

wdd.cancelled_quantity canqty,

wdd.shipped_quantity shpqty,

wdd.cycle_count_quantity boqty,

wdd.split_from_delivery_detail_id split,

msi.segment1 item

from apps.MTL_SYSTEM_ITEMS msi,

apps.OE_ORDER_LINES_ALL ool,

apps.OE_ORDER_HEADERS_ALL ooh,

apps.WSH_DELIVERY_DETAILS wdd,

apps.WSH_DELIVERY_ASSIGNMENTS wda,

apps.WSH_NEW_DELIVERIES wnd

where ooh.order_number = &order_num

and ooh.org_id in (:org_id)

and ool.header_id = ooh.header_id

and msi.organization_id in (:master_organization_id)

and msi.inventory_item_id = ool.inventory_item_id

and wdd.source_header_id = ooh.header_id

and wdd.source_line_id = ool.line_id

and wda.delivery_detail_id(+) = wdd.delivery_detail_id

and wnd.delivery_id(+) = wda.delivery_id

order by ola_ln;

3. Update script for table WSH_DELIVERY_DETAILS

UPDATE WSH_DELIVERY_DETAILS

SET SOURCE_LINE_ID = :LINE_ID,

SOURCE_LINE_NUMBER = ':LINE_NUMBER',

OE_INTERFACED_FLAG = 'N',

LAST_UPDATED_BY = :User_ID,

LAST_UPDATE_DATE = SYSDATE

WHERE DELIVERY_DETAIL_ID = :delivery_detail_id;

2. Fix data in table WSH_TRIP_STOPS.

1. Use the DELIVERY_ID from above step (i.e. 1-b) in the script below to find STOP_ID. Looks for relevant data and catch the STOP_ID.

select wt.trip_id trip,

wt.status_code sc,

oola.line_id,

wtsp.stop_id ,

wtsp.status_code sc,

wtsp.pending_interface_flag pif,

wtsd.status_code sc,

wtsd.pending_interface_flag pif,

wtsd.lock_stop_id lockid,

wnd.organization_id org,

wnd.status_code,

wnd.planned_flag pf,

wdd.delivery_detail_id wddi,

wdd.released_status rs,

wdd.inv_interfaced_flag inv,

wdd.oe_interfaced_flag oe,

ooha.order_number, to_number(nvl(oola.line_number,0)'.'nvl(oola.shipment_number,0)) ln,

oola.flow_status_code flow_status,

oola.org_id ordou,

wdd.org_id wddou,

wdd.source_code sc,

wdd.source_line_id line_id,

wdd.source_header_id hdr_id

from apps.wsh_new_deliveries wnd,

apps.wsh_delivery_assignments wda,

apps.wsh_delivery_details wdd,

apps.oe_order_headers_all ooha,

apps.oe_order_lines_all oola,

apps.wsh_delivery_legs wdl,

apps.wsh_trip_stops wtsp,

apps.wsh_trip_stops wtsd,

apps.wsh_trips wt

where wnd.delivery_id = :delivery_id

and wda.delivery_id(+) = wnd.delivery_id

and wdd.delivery_detail_id(+) = wda.delivery_detail_id

and ooha.header_id(+) = wdd.source_header_id

and oola.line_id(+) = wdd.source_line_id

and wdl.delivery_id (+) = wnd.delivery_id

and wtsp.stop_id (+) = wdl.pick_up_stop_id

and wtsd.stop_id (+) = wdl.drop_off_stop_id

and wt.trip_id (+) = wtsp.trip_id

order by ooha.order_number, oola.line_number;

Else the other way to find STOP_ID is, you can open Shipping transaction form and query the order number. There find the trip number in Path by Trip tab.

Then find associated STOP_ID.

2. Update script for table WSH_TRIP_STOPS

UPDATE WSH_TRIP_STOPS

SET PENDING_INTERFACE_FLAG = 'Y',

LAST_UPDATED_BY = :USER_ID,

LAST_UPDATE_DATE = SYSDATE

WHERE STOP_ID = :STOP_ID;

3. Run Interface Trip Stop program.

If you are running this program and entries are already posted to inventory then run the program with mode ‘OM and DSNO’, provide the trip name which you get from Shipping Transaction screen. (It really does not matter a lot as the inventory interfaced flag for the record is already set to YES, so it is not going to update the inventory again)

For better visibility CLICK HERE