Performing Back Order in Move Order Line Using inv_mo_backorder_pvt API

Performing Back Order in a Move Order Line Using inv_mo_backorder_pvt API *********************************************************************************
What Happens during Pick Release?
*************************************

• A pre-approved Move Order is automatically created in Inventory.
• A Move order is a request for a subinventory transfer from the source (stocking) subinventory to the destination (staging) subinventory.
• A Move order is created for every Sales Order Line that has the status of “Awaiting Shipping” and passes the picking criteria (Use your Release Rules to guide this process).
• The Destination subinventory is the Staging subinventory entered on the Release Sales Orders form or defaulted from the Shipping Parameters. Only one staging subinventory is allowed per Picking Batch.
• Note: A Picking Batch is the total number of Order Lines that were released at one time.
• Inventory uses the move order to manage material requisitions within an Organization.
• Some times, the Pick Release Process (Pick Selection List Generation Program) either completed with warning or with Error. While dubuging, it shows the line has not been pick released & the delivery status is showing as “Released to Warehouse”. In the delivery detail window, the next steps indicate “Transaction Move Order”.

What exactly Release to Warehouse – (Released Status S in the wsh_delivery_details Table) Status is meant by?

• Pick release has processed the delivery line and has created move order headers and lines. Found available quantity and created inventory allocations. Not pick confirmed. In other words Pick Release has started but not completed. Either no allocations were created or the allocations have not been Pick Confirmed.
• If you are using auto-pick confirm during the Pick Release process then it changes release status to Staged. If you are not using auto-pick confirm and want to progress the delivery lines, navigate to Oracle Inventory Move Order Transaction window and perform manual pick confirm that is nothing but Transacting the Move Order.
• Once you navigate to the move order window, either you can Allocate & Transact the move order to complete the Pick Release process orYou can backorder that particular line (In the Move Order Transact Window > Search for the Move Order by the Number > Tools > Back Order Lines) & Proceed with the Pick Release Process Again.

This Particular Operation can be done by using a Private API “inv_mo_backorder_pvt.backorder”.

— R12 – OM – Sample Script to Back Order a Move Order Line Using INV_MO_BACKORDER_PVT API

DECLARE

CURSOR c_order_det IS

SELECT dd.delivery_detail_id, dd.source_code, dd.source_line_id,dd.org_id,
dd.source_header_id, dd.source_header_number,
dd.source_line_number,
NVL (dl.ship_method_code, dd.ship_method_code) ship_method_code,
dd.inventory_item_id,
(SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = dd.inventory_item_id
AND organization_id = dd.organization_id) ordered_item,
NVL (wsn.quantity, dd.requested_quantity) requested_quantity,
NVL (wsn.quantity, dd.shipped_quantity) shipped_quantity,
dd.requested_quantity_uom, dd.src_requested_quantity_uom,
dd.requested_quantity2, dd.shipped_quantity2,
dd.requested_quantity_uom2, dd.src_requested_quantity_uom2,
dd.ship_set_id, dd.revision, dd.lot_number,
NVL (wsn.fm_serial_number,
DECODE (dd.oe_interfaced_flag, ‘Y’, dd.serial_number, NULL)
) serial_number,
dd.released_status, wl2.meaning, dl.delivery_id, dl.NAME,
dl.currency_code, dl.status_code delivery_status_code,
DECODE (NVL (dl.status_code, ‘-99’),
‘-99’, NULL, wl1.meaning ) delivery_status_meaning,
dd.organization_id, dl.initial_pickup_date, dl.ultimate_dropoff_date,
NVL (wsn.to_serial_number, dd.to_serial_number) to_serial_number,
dd.move_order_line_id,
(select request_number from mtl_txn_request_headers where header_id in
(select header_id from mtl_txn_request_lines
where line_id = dd.move_order_line_id)) Move_order,
(select Line_number from mtl_txn_request_lines
where line_id = dd.move_order_line_id) Move_order_Line
FROM wsh_lookups wl1,
wsh_lookups wl2,
wsh_new_deliveries dl,
wsh_delivery_assignments_v da,
wsh_delivery_details dd,
wsh_locations wlf,
wsh_locations wlt,
wsh_serial_numbers wsn
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = dl.delivery_id(+)
AND NVL (dl.status_code, ‘OP’) = wl1.lookup_code
AND wl1.lookup_type = ‘DELIVERY_STATUS’
AND ( ( wl2.lookup_code = ‘K’
AND dd.released_status = ‘S’
AND dd.move_order_line_id IS NULL)
OR ( wl2.lookup_code = dd.released_status
AND ( (dd.move_order_line_id IS NOT NULL)
OR (dd.released_status <> ‘S’))
AND (dd.replenishment_status IS NULL))
OR ( wl2.lookup_code = ‘E’
AND dd.released_status IN (‘R’, ‘B’)
AND dd.replenishment_status = ‘R’)
OR ( wl2.lookup_code = ‘F’
AND dd.released_status IN (‘R’, ‘B’)
AND dd.replenishment_status = ‘C’))
AND wl2.lookup_type = ‘PICK_STATUS’
AND dd.ship_from_location_id = wlf.wsh_location_id(+)
AND dd.ship_to_location_id = wlt.wsh_location_id(+)
AND dd.delivery_detail_id = wsn.delivery_detail_id(+)
AND NVL (dd.line_direction, ‘O’) IN (‘O’, ‘IO’)
AND NVL (dl.delivery_type, ‘STANDARD’) = ‘STANDARD’
AND dd.source_header_number = ‘10000596’
AND dd.released_status = ‘S’;

l_return_status VARCHAR2 (100);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_msg_index NUMBER;
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;

BEGIN

SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘A42485’;

SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘Order Management Super User’;
fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN c_order_det

LOOP

mo_global.set_policy_context (‘S’, i.org_id);
mo_global.init (‘ONT’);

DBMS_OUTPUT.put_line (‘Calling INV_MO_BACKORDER_PVT to Backorder MO’);
DBMS_OUTPUT.put_line (‘===============================’);

inv_mo_backorder_pvt.backorder (
p_line_id => i.move_order_line_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

DBMS_OUTPUT.put_line (‘Return Status is=> ‘ l_return_status);
— Check Return Status

IF l_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line (‘Successfully BackOrdered the Move Order Line’);
COMMIT;
ELSE
DBMS_OUTPUT.put_line
(‘Could not able to Back Order Line Due to Following Reasons’ );
ROLLBACK;

FOR j IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => j,
p_encoded => fnd_api.g_false,
p_data => l_msg_data,
p_msg_index_out => l_msg_index);
DBMS_OUTPUT.put_line (‘Error Message is=> ‘ l_msg_data);
END LOOP;
END IF;

END LOOP;

END;

DBMS Output: –
***************

Calling INV_MO_BACKORDER_PVT to Backorder MO
===============================
Return Status is=> S
Successfully BackOrdered the Move Order Line