Using API inv_mo_backorder_pvt.backorder to back order move order
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 = \'65136\' --Sales Order Number
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 = \'MFG\'; --User
SELECT responsibility_id,
application_id
INTO l_resp_id,
l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = \'Manufacturing and Distribution Manager\'; --Responsibility
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;
After back order,Move order line status is changed from \'Pre-Approved\' to \'Closed\',and Requested Quantity is cleared to zero.
Shipping Transaction Status is also changed to \'Backordered\'
转载于:http://blog.itpub.net/26687597/viewspace-1204349/