, , , , , , , , , ,

Script to Cancel Sales Order Line Using API OE_ORDER_PUB


l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_header_rec_in oe_order_pub.header_rec_type; — pl/sql table and record definition to be used as IN parameters
l_line_tbl_in oe_order_pub.line_tbl_type; — pl/sql table and record definition to be used as IN parameters
l_action_request_tbl_in oe_order_pub.request_tbl_type;– Used to assigining Book Order related input parameters
l_header_rec_out oe_order_pub.header_rec_type; — pl/sql table and record definition to be used as OUT parameters
l_line_tbl_out oe_order_pub.line_tbl_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_chr_program_unit_name VARCHAR2 (100); — To store the package and procedure name for logging
l_chr_ret_status VARCHAR2 (1000) := NULL;– To store the error message code returned by API
l_msg_count NUMBER := 0; — To store the number of error messages API has encountered
l_msg_data VARCHAR2 (2000); — To store the error message text returned by API
l_num_api_version NUMBER := 1.0; — API version

CURSOR c_so_details IS

SELECT oh.order_number, ol.*
FROM oe_order_lines_all ol, oe_order_headers_all oh
WHERE oh.header_id = ol.header_id
AND oh.org_id = ol.org_id
AND NVL (ol.cancelled_flag,’N’) = ‘N’
AND oh.order_number = ‘10001690’ — Enter the Order Number
AND ol.line_number = 1 — Enter the Line Number
AND ol.shipment_number = 2 — Enter the Shipment Number
AND ol.flow_status_code = ‘AWAITING_SHIPPING’;


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 iso_rec IN c_so_details LOOP

l_line_tbl_in (1) := oe_order_pub.g_miss_line_rec;
l_line_tbl_in (1).line_id := iso_rec.line_id;
l_line_tbl_in (1).ordered_quantity := 0;
l_line_tbl_in (1).change_reason := ‘Admin Error’;
l_line_tbl_in (1).change_comments := ‘CANCEL ORDER’;
l_line_tbl_in (1).operation := oe_globals.g_opr_update;

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

(p_api_version_number => l_num_api_version,
p_org_id => mo_global.get_current_org_id,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_action_commit => fnd_api.g_false,
p_line_tbl => l_line_tbl_in,
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl=> l_header_scredit_val_tbl_out,
x_line_tbl => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_chr_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);

l_msg_data := NULL;

IF l_chr_ret_status <> ‘S’ THEN

FOR iindx IN 1 .. l_msg_count LOOP

l_msg_data := l_msg_data ‘ .’ oe_msg_pub.get (iindx);




DBMS_OUTPUT.put_line (‘Sales Order => ‘
‘ – Line Number => ‘
‘ – Shipment Number => ‘
‘ Having Line ID=> ‘
‘ Cancelled Successfully’ );

DBMS_OUTPUT.put_line (‘Return Status: ‘ l_chr_ret_status);
DBMS_OUTPUT.put_line (‘Error Message: ‘ l_msg_data);



, , , , , , , , , ,

Performing Back Order on a SO Line using Wsh_Delivery_Pub API


x_return_status VARCHAR2 (3000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (3000);
–Standard Parameters.
p_api_version_number NUMBER;
init_msg_list VARCHAR2 (30);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
p_validation_level NUMBER;
p_commit VARCHAR2 (30);
–Parameters for WSH_DELIVERIES_PUB
p_action_code VARCHAR2 (15);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2 (30);
p_asg_trip_id NUMBER;
p_asg_trip_name VARCHAR2 (30);
p_asg_pickup_stop_id NUMBER;
p_asg_pickup_loc_id NUMBER;
p_asg_pickup_loc_code VARCHAR2 (30);
p_asg_pickup_arr_date DATE;
p_asg_pickup_dep_date DATE;
p_asg_dropoff_stop_id NUMBER;
p_asg_dropoff_loc_id NUMBER;
p_asg_dropoff_loc_code VARCHAR2 (30);
p_asg_dropoff_arr_date DATE;
p_asg_dropoff_dep_date DATE;
p_sc_action_flag VARCHAR2 (10);
p_sc_close_trip_flag VARCHAR2 (10);
p_sc_create_bol_flag VARCHAR2 (10);
p_sc_stage_del_flag VARCHAR2 (10);
p_sc_trip_ship_method VARCHAR2 (30);
p_sc_actual_dep_date VARCHAR2 (30);
p_sc_report_set_id NUMBER;
p_sc_report_set_name VARCHAR2 (60);
p_asg_pickup_stop_seq NUMBER;
p_asg_dropoff_stop_seq NUMBER;
p_sc_send_945_flag VARCHAR2 (60);
p_sc_rule_id NUMBER;
p_sc_rule_name VARCHAR2 (60);
p_wv_override_flag VARCHAR2 (10);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
/*Handle exceptions*/
fail_api EXCEPTION;

l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_ship_method_code VARCHAR2 (100);

CURSOR c_ord_details

SELECT DISTINCT det.source_header_number sales_order, det.org_id,
det.source_line_number, det.source_header_id,
det.source_line_id, det.source_header_type_name,
det.inventory_item_id, det.requested_quantity,
(SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = det.inventory_item_id
AND organization_id = det.organization_id) ordered_item,
det.organization_id, det.src_requested_quantity,
det.shipped_quantity, del.delivery_id,
del.status_code delivery_status_code,
det.released_status pick_release_status,
det.oe_interfaced_flag, det.inv_interfaced_flag
FROM wsh_delivery_details det,
wsh_delivery_assignments asn,
wsh_new_deliveries del
WHERE 1 = 1
AND det.delivery_detail_id = asn.delivery_detail_id
AND asn.delivery_id = del.delivery_id(+)
AND det.source_header_number = ‘10001059’
AND det.org_id = 308
AND shipped_quantity IS NULL
AND NVL (del.status_code, ‘OP’) <> ‘CL’
AND det.released_status = ‘Y’;


— Initializing the Applications

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_ord_details


— Mandatory initialization for R12

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

SELECT shipping_method_code
INTO l_ship_method_code
FROM oe_order_headers_all
WHERE order_number = i.sales_order AND org_id = i.org_id;
l_ship_method_code := NULL;

/* Initialize return status*/

x_return_status := wsh_util_core.g_ret_sts_success;

p_action_code := ‘CONFIRM’; — The action code
p_delivery_id := i.delivery_id;– The delivery that needs to be confirmed
p_delivery_name := TO_CHAR (i.delivery_id); — The delivery name,
p_sc_action_flag := ‘C’; — Ship entered quantity
p_sc_stage_del_flag := ‘N’; —
p_sc_trip_ship_method := l_ship_method_code; — The ship method code

DBMS_OUTPUT.put_line (‘Calling WSH_DELIVERIES_PUB to Perform Back Order’);
DBMS_OUTPUT.put_line (‘=============================================’);

(p_api_version_number => 1.0,
p_init_msg_list => init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => p_action_code,
p_delivery_id => i.delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => p_asg_trip_id,
p_asg_trip_name => p_asg_trip_name,
p_asg_pickup_stop_id => p_asg_pickup_stop_id,
p_asg_pickup_loc_id => p_asg_pickup_loc_id,
p_asg_pickup_stop_seq => p_asg_pickup_stop_seq,
p_asg_pickup_loc_code => p_asg_pickup_loc_code,
p_asg_pickup_arr_date => p_asg_pickup_arr_date,
p_asg_pickup_dep_date => p_asg_pickup_dep_date,
p_asg_dropoff_stop_id => p_asg_dropoff_stop_id,
p_asg_dropoff_loc_id => p_asg_dropoff_loc_id,
p_asg_dropoff_stop_seq => p_asg_dropoff_stop_seq,
p_asg_dropoff_loc_code => p_asg_dropoff_loc_code,
p_asg_dropoff_arr_date => p_asg_dropoff_arr_date,
p_asg_dropoff_dep_date => p_asg_dropoff_dep_date,
p_sc_action_flag => p_sc_action_flag,
p_sc_intransit_flag => ‘N’,
p_sc_close_trip_flag => ‘N’,
p_sc_create_bol_flag => ‘N’,
p_sc_stage_del_flag => p_sc_stage_del_flag,
p_sc_trip_ship_method => p_sc_trip_ship_method,
p_sc_actual_dep_date => p_sc_actual_dep_date,
p_sc_report_set_id => p_sc_report_set_id,
p_sc_report_set_name => p_sc_report_set_name,
p_sc_defer_interface_flag => ‘Y’,
p_sc_send_945_flag => p_sc_send_945_flag,
p_sc_rule_id => p_sc_rule_id,
p_sc_rule_name => p_sc_rule_name,
p_wv_override_flag => ‘N’,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name

DBMS_OUTPUT.put_line (x_return_status);
DBMS_OUTPUT.put_line (x_msg_count);
DBMS_OUTPUT.put_line (x_msg_data);

IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN

DBMS_OUTPUT.put_line (‘Back Order has not been Completed For SO’);

RAISE fail_api;

DBMS_OUTPUT.put_line (‘Back Order Successfully Completed For SO’);



WHEN fail_api
DBMS_OUTPUT.put_line (‘==============’);
DBMS_OUTPUT.put_line (‘Error Details If Any’);
DBMS_OUTPUT.put_line (‘==============’);

wsh_util_core.get_messages (‘Y’,

IF x_msg_count > 1
x_msg_data := x_msg_summary x_msg_details;
DBMS_OUTPUT.put_line (x_msg_data);
x_msg_data := x_msg_summary x_msg_details;
DBMS_OUTPUT.put_line (x_msg_data);


, , , , , , , , ,

Creating Move Order Using INV_MOVE_ORDER_PUB

Move Order:

Move orders are requests for the movement of material within a single organization. They allow planners and facility managers to request the movement of material within a warehouse or facility for purposes like replenishment, material storage relocations, and quality handling. We can generate move orders either manually or automatically depending on the source type we use. Move orders are restricted to transactions within an organization.

Move Order source types

There are three Move Order source types:
A. Move Order RequisitionGenerated by:
* Manually generated request inside Inventory

The requisition is a manually generated request for a Move Order. May optionally utilize the approval processing – the requisition can optionally go through an Oracle Workflow approval process before it becomes a Move Order. If no approval process is used, the requisition becomes a move order immediately.

B. Replenishment Move Order

Generated by:
* Kanban Cards when pull sequence calls for subinventory transfer (intra-org Kanbans)
* Min Max Planning Report planned at the Subinventory Level

* Replenishment Counts

Replenishment Move Orders are pre-approved and ready to be transacted.

C. Wave Pick Move Order

Generated by:
* Sales Order picking
* Internal Sales Order picking

The Order Management pick release process generates move orders to bring material from its source location in Inventory to a staging location, which is defined as a subinventory. In other words, a subinventory transfer. Pick Wave Move Orders are pre-approved and ready to transact. Pick slips can also be generated for these Move Orders from Inventory with the release of the Oracle Order Management module.

Tables used to store the details about move order process

1) MTL_TXN_REQUEST_HEADERS: Move order headers, this stores the move order number in column (REQUEST_NUMBER). It has a status, but this is not used as much as the lines status to drive functionality.
2) MTL_TXN_REQUEST_LINES: Move order lines, this is the one that drives most queries and status checks for the move order as each line can be transacted individually.
3) MTL_MATERIAL_TRANSACTIONS_TEMP: Pending material transactions table also called the transaction temporary table, this holds allocations that act like reservations on inventory. An allocation is where you pick a specific item in inventory down to the lot, locator, serial, revision to move, but you do not actually perform the move yet.
4) MTL_MATERIAL_TRANSACTIONS : Finally the Material Transactions will get into this table.

R12 – Sample Script to Create Move Order Using INV_MOVE_ORDER_PUB.
x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (4000);
x_msg_count NUMBER;
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_hdr_rec inv_move_order_pub.trohdr_rec_type:= inv_move_order_pub.g_miss_trohdr_rec;
x_hdr_rec inv_move_order_pub.trohdr_rec_type:= inv_move_order_pub.g_miss_trohdr_rec;
l_line_tbl inv_move_order_pub.trolin_tbl_type:= inv_move_order_pub.g_miss_trolin_tbl;
x_line_tbl inv_move_order_pub.trolin_tbl_type:= inv_move_order_pub.g_miss_trolin_tbl;
x_hdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
x_line_val_tbl inv_move_order_pub.trolin_val_tbl_type;
v_msg_index_out NUMBER;
l_rsr_type inv_reservation_global.mtl_reservation_tbl_type;
l_code_combination_id gl_code_combinations.code_combination_id%type;

CURSOR c_itm_onhand IS

SELECT a.organization_id, c.operating_unit org_id,
SUM (a.primary_transaction_quantity) total_onhand
FROM mtl_onhand_quantities_detail a,
mtl_system_items_kfv b,
org_organization_definitions c,
mtl_lot_numbers d
WHERE a.inventory_item_id = b.inventory_item_id
AND a.lot_number = d.lot_number
AND a.inventory_item_id = d.inventory_item_id
AND a.organization_id = b.organization_id
AND a.organization_id = c.organization_id
AND a.organization_id = d.organization_id
AND b.concatenated_segments = ‘RC0805FR-0768KL^YAGEO’
AND a.lot_number = ‘A6680983’
AND c.organization_code = ‘A66’
GROUP BY a.organization_id,c.operating_unit,
a.inventory_item_id, b.concatenated_segments,
a.subinventory_code, a.lot_number;


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 = ‘Inventory’;

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN c_itm_onhand LOOP
mo_global.set_policy_context (‘S’, i.org_id);
inv_globals.set_org_id (i.organization_id);
mo_global.init (‘INV’);

select code_combination_id
into l_code_combination_id
from gl_code_combinations_kfv
where concatenated_segments = ‘6420-00000-0000-999999-91000-0000-00000-0-0’;

l_hdr_rec.date_required := SYSDATE;
l_hdr_rec.header_status := inv_globals.g_to_status_preapproved;
l_hdr_rec.organization_id := i.organization_id;
l_hdr_rec.status_date := SYSDATE;
l_hdr_rec.transaction_type_id := inv_globals.g_type_transfer_order_issue;
l_hdr_rec.move_order_type := inv_globals.g_move_order_requisition;
l_hdr_rec.db_flag := fnd_api.g_true;
l_hdr_rec.operation := inv_globals.g_opr_create;
l_hdr_rec.description := ‘Test Move Order One’;
l_hdr_rec.to_account_id := l_code_combination_id;
l_hdr_rec.from_subinventory_code := i.subinventory_code;
l_line_tbl (1).date_required := SYSDATE;
l_line_tbl (1).inventory_item_id := i.inventory_item_id;
l_line_tbl (1).line_id := fnd_api.g_miss_num;
l_line_tbl (1).line_number := 1;
l_line_tbl (1).line_status := inv_globals.g_to_status_preapproved;
l_line_tbl (1).transaction_type_id := inv_globals.g_type_transfer_order_issue;
l_line_tbl (1).organization_id := i.organization_id;
l_line_tbl (1).quantity := 5000;
l_line_tbl (1).status_date := SYSDATE;
l_line_tbl (1).uom_code := ‘EA’;
l_line_tbl (1).db_flag := fnd_api.g_true;
l_line_tbl (1).operation := inv_globals.g_opr_create;
l_line_tbl (1).from_subinventory_code := i.subinventory_code;
l_line_tbl (1).to_account_id := l_code_combination_id;
l_line_tbl (1).lot_number := i.lot_number;

DBMS_OUTPUT.put_line (‘Calling INV_MOVE_ORDER_PUB to Create MO’);
DBMS_OUTPUT.put_line (‘===================================’);

(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_trohdr_rec => l_hdr_rec,
p_trolin_tbl => l_line_tbl,
x_trohdr_rec => x_hdr_rec,
x_trohdr_val_rec => x_hdr_val_rec,
x_trolin_tbl => x_line_tbl,
x_trolin_val_tbl => x_line_val_tbl);
DBMS_OUTPUT.put_line (x_return_status);
DBMS_OUTPUT.put_line (x_msg_count);
IF x_return_status = ‘S’

DBMS_OUTPUT.put_line (‘Move Order Successfully Created’);

DBMS_OUTPUT.put_line (‘Move Order Number is :=> ‘);
DBMS_OUTPUT.put_line (x_hdr_rec.request_number);
DBMS_OUTPUT.put_line (‘Move Order Creation Failed Due to Following Reasons’);
IF x_msg_count > 0
FOR v_index IN 1 .. x_msg_count
fnd_msg_pub.get (p_msg_index => v_index,
p_encoded => ‘F’,
p_data => x_msg_data,
p_msg_index_out => v_msg_index_out
x_msg_data := SUBSTR (x_msg_data, 1, 200);
DBMS_OUTPUT.put_line (x_msg_data);
Sample DBMS Output: –
Calling INV_MOVE_ORDER_PUB to Create MO
Move Order Successfully Created
Move Order Number is :=> 332557