Transact Move Order Using INV_PICK_WAVE_PICK_CONFIRM_PUB

R12 - INV - Sample Script to Transact Move Order Using
INV_PICK_WAVE_PICK_CONFIRM_PUB API

DECLARE

l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2 (2) := fnd_api.g_true;
l_commit VARCHAR2 (2) := fnd_api.g_false;
x_return_status VARCHAR2 (2);
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2 (255);
l_move_order_type NUMBER := 1;
l_transaction_mode NUMBER := 1;
l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
x_mmtt_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
l_transaction_date DATE := SYSDATE;
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;

CURSOR c_mo_details IS

SELECT mtrh.header_id, mtrh.request_number, mtrh.move_order_type,
mtrh.organization_id, mtrl.line_id, mtrl.line_number,
mtrl.inventory_item_id, mtrl.lot_number, mtrl.quantity,
revision, mtrl.from_locator_id,
(SELECT DISTINCT operating_unit
FROM org_organization_definitions
WHERE organization_id = mtrh.organization_id) org_id
FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
WHERE mtrh.header_id = mtrl.header_id
AND mtrh.request_number = '332557'
AND mtrh.organization_id = 381;

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 = 'Inventory';

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN c_mo_details

LOOP

mo_global.set_policy_context ('S', i.org_id);
inv_globals.set_org_id (i.organization_id);
mo_global.init ('INV');
l_trolin_tbl (1).line_id := i.line_id;
-- call API to create move order header
DBMS_OUTPUT.put_line
('=======================================================');
DBMS_OUTPUT.put_line
('Calling INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm API');

inv_pick_wave_pick_confirm_pub.pick_confirm
(p_api_version_number => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_move_order_type => i.move_order_type,
p_transaction_mode => l_transaction_mode,
p_trolin_tbl => l_trolin_tbl,
p_mold_tbl => l_mold_tbl,
x_mmtt_tbl => x_mmtt_tbl,
x_trolin_tbl => x_trolin_tbl,
p_transaction_date => l_transaction_date
);

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

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line (x_msg_data);
END IF;
DBMS_OUTPUT.put_line
('=======================================================');
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Exception Occured :');
DBMS_OUTPUT.put_line (SQLCODE ':' SQLERRM);
DBMS_OUTPUT.put_line
('=======================================================');
END;

DBMS Output:
=======================================================
Calling INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm API
=======================================================
S
=======================================================

Verification:

select * from mtl_material_transactions_temp
where transaction_temp_id = 1094230

No Rows Returned

-- Link the Move Order with the Material Transcations Table

SELECT
mmt.transaction_id,
mtrl.organization_id,
mtrh.request_number,
mtrh.header_id,
mtrl.line_number,
mtrl.line_id,
mtrl.inventory_item_id,
mtrh.description,
mtrh.move_order_type,
mtrl.line_status,
(select meaning from mfg_lookups
where lookup_type = 'MTL_TXN_REQUEST_STATUS'
and lookup_code = mtrl.line_status) Line_status_meaning,
mtrl.quantity,
mtrl.quantity_delivered,
mmt.transaction_type_id,
mmt.transaction_date
FROM mtl_txn_request_headers mtrh,
mtl_txn_request_lines mtrl,
mtl_material_transactions mmt
WHERE mtrh.header_id = mtrl.header_id
AND mtrh.organization_id = mtrl.organization_id
AND mtrl.line_id = mmt.move_order_line_id
AND mtrh.request_number = 332557
AND mtrh.organization_id = 381

Allocating Move Order INV_REPLENISH_DETAIL_PUB

R12 - INV - Sample Script to Allocate Move Order using
INV_REPLENISH_DETAIL_PUB API

DECLARE

l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2 (2) := fnd_api.g_true;
l_return_values VARCHAR2 (2) := fnd_api.g_false;
l_commit VARCHAR2 (2) := fnd_api.g_false;
x_return_status VARCHAR2 (2);
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2 (255);
l_user_id NUMBER ;
l_resp_id NUMBER ;
l_appl_id NUMBER ;
l_row_cnt NUMBER := 1;
l_trohdr_rec inv_move_order_pub.trohdr_rec_type;
l_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
x_trohdr_rec inv_move_order_pub.trohdr_rec_type;
x_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
l_validation_flag VARCHAR2 (2) := inv_move_order_pub.g_validation_yes;
l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
l_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
x_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
x_number_of_rows NUMBER ;
x_transfer_to_location NUMBER ;
x_expiration_date DATE;
x_transaction_temp_id NUMBER ;

CURSOR c_mo_details IS

SELECT mtrh.header_id, mtrh.request_number, mtrh.move_order_type,
mtrh.organization_id, mtrl.line_id, mtrl.line_number,
mtrl.inventory_item_id, mtrl.lot_number, mtrl.quantity,
revision,mtrl.from_locator_id,
(select distinct operating_unit from org_organization_definitions
where organization_id = mtrh.organization_id) org_id
FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
WHERE mtrh.header_id = mtrl.header_id
AND mtrh.request_number = '332557'
AND mtrh.organization_id = 381;

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 = 'Inventory';

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN c_mo_details

LOOP

mo_global.set_policy_context ('S', i.org_id);
inv_globals.set_org_id (i.organization_id);
mo_global.init ('INV');

SELECT COUNT (*)
INTO x_number_of_rows
FROM mtl_txn_request_lines
WHERE header_id = i.header_id;

DBMS_OUTPUT.put_line ('Calling INV_REPLENISH_DETAIL_PUB to Allocate MO');
-- Allocate each line of the Move Order

inv_replenish_detail_pub.line_details_pub
(p_line_id => i.line_id,
x_number_of_rows => x_number_of_rows,
x_detailed_qty => i.quantity,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_revision => i.revision,
x_locator_id => i.from_locator_id,
x_transfer_to_location => x_transfer_to_location,
x_lot_number => i.lot_number,
x_expiration_date => x_expiration_date,
x_transaction_temp_id => x_transaction_temp_id,
p_transaction_header_id => NULL,
p_transaction_mode => NULL,
p_move_order_type => i.move_order_type,
p_serial_flag => fnd_api.g_false,
p_plan_tasks => FALSE,
p_auto_pick_confirm => FALSE,
p_commit => FALSE
);

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

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line (x_msg_data);
END IF;

IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line ('Trx temp ID: ');
DBMS_OUTPUT.put_line (x_transaction_temp_id);
END IF;
DBMS_OUTPUT.put_line
('==========================================================');
END LOOP;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Exception Occured :');
DBMS_OUTPUT.put_line (SQLCODE ':' SQLERRM);
DBMS_OUTPUT.put_line
('=======================================================');
END;

DBMS Output: -


Calling INV_REPLENISH_DETAIL_PUB to Allocate MO
==========================================================
S
Trx temp ID: 1094230
==========================================================


select * from mtl_material_transactions_temp
where transaction_temp_id = 1094230

1 Row Returned

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 Requisition
Generated 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.

DECLARE

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,
a.inventory_item_id,b.concatenated_segments,
a.subinventory_code,a.lot_number,
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;

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 = '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_line_tbl.DELETE;
x_line_tbl.DELETE;
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 ('===================================');

INV_MOVE_ORDER_PUB.PROCESS_MOVE_ORDER
(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'
THEN
COMMIT;

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);

ELSE
ROLLBACK;
DBMS_OUTPUT.put_line ('Move Order Creation Failed Due to Following Reasons');
END IF;

IF x_msg_count > 0
THEN
FOR v_index IN 1 .. x_msg_count
LOOP
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);

END LOOP;
END IF;
END LOOP;

END;

Sample DBMS Output: -

Calling INV_MOVE_ORDER_PUB to Create MO
===================================
S
0
Move Order Successfully Created
Move Order Number is :=> 332557

Performing Back Order on a SO Line using Wsh_Delivery_Pub API

-- R12 - OM - Script to Back Order SO Line using Wsh_Delivery_Pub API --
--===================================================== --

DECLARE

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
IS

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';

BEGIN

-- 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

LOOP

-- Mandatory initialization for R12

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

BEGIN
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;
EXCEPTION
WHEN OTHERS
THEN
l_ship_method_code := NULL;
END;

/* 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 ('=============================================');

wsh_deliveries_pub.delivery_action
(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');

ROLLBACK;
RAISE fail_api;

ELSE
DBMS_OUTPUT.put_line ('Back Order Successfully Completed For SO');
COMMIT;

END IF;
END LOOP;

EXCEPTION

WHEN fail_api
THEN
DBMS_OUTPUT.put_line ('==============');
DBMS_OUTPUT.put_line ('Error Details If Any');
DBMS_OUTPUT.put_line ('==============');

wsh_util_core.get_messages ('Y',
x_msg_summary,
x_msg_details,
x_msg_count
);

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

END;

Performing Shipping Transaction Using WSH_DELIVERY_PUB API

Ship Confirm Process
================

What happened during Ship Confirm:
******************************************
The results of the picking process are recorded against a Delivery.

• Ship Confirm can only be performed on Deliveries with Delivery Lines that have been Pick Confirmed.
• Ship confirming a delivery records the results of the picking process. These results could be shipped, backordered, staged or cycle count or a combination of all 4.
• The workflow activity will be completed when the quantity picked is recorded as “Shipped”.
• If the ordered item is set up with the item attribute “Shippable” checked, Ship Confirming is a prerequisite for Invoicing.

Shipped Quantities:
************************
• Once the delivery is closed the Order Line is updated with the shipped quantities and the status of the line is changed to “Shipped”. This enables the order line to proceed to its next workflow activity.
• The Ship Confirm transaction initiates the Inventory Interface to generate the “Issue of Stores” transactions which will decrement inventory and remove the material reservation. Then the OM interfaces is initiated to update the Sales Order Line with Shipped quantities, freight charges, etc.
• The Cost of goods sold account number that is passed to inventory is workflow generated. In Inventory it creates a Material Distribution record that is ultimately passed to the General Ledger.
• If Ship confirm is partial the remaining quantity can be either staged or backordered.

Backordered Quantities:
****************************
• Backordered quantities are left in the Staging Subinventory. They are not automatically returned to their source location.
• A new pick release will be required before they can be ship confirmed.
• The backordered quantity is removed from the delivery being Ship Confirmed.and the reservation is removed making the quantity available to ATP.
• The Sales Order line splits into shipment schedules. One schedule will have the quantity that was shipped and a status of “Shipped”. The other schedule will have the quantity that was backordered and a status of “Awaiting Shipment”.

Staged Quantities:
**********************
• Staged quantities are left in the Staging subinventory and can be Ship Confirmed at a later time. The staged quantity is removed from the delivery being confirmed and optionally linked to a new delivery number.
• The Sales Order line splits into shipment schedules. One schedule will have the quantity that was shipped and a status of “Shipped”. The other schedule will have the quantity that remained Staged with a status of “Picked”.

Different Delivery Line Statuses:
*************************************
select * from wsh_lookups
where lookup_type = 'PICK_STATUS'

- Not Applicable (Code X)
The delivery line is invoiceable but non-shippable, for example, a service line or a warranty line.

- Not Ready for Release (Code N)
The delivery line is not eligible for pick release. Occurs when the order line is manually imported into Oracle Shipping Execution using the Import Delivery Line concurrent process. It has not reached the Awaiting Shipping workflow activity.

- Ready for Release (Code R)
The delivery line is eligible for pick release. Occurs when the order line has reached the Awaiting Shipping workflow activity (it is booked, scheduled, and in Oracle Shipping Execution).

- Submitted to Warehouse (Code S)
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. If you are using auto-pick confirm, 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.

- Staged (Code Y)
The delivery line is pick confirmed; inventory is transferred from storage subinventory to staging subinventory. It remains staged until ship confirm.

- Backordered (Code B)
Any of the following circumstances occurs: Pick release has processed the delivery line and cannot find the entire quantity. This typically occurs when the Oracle Inventory inventory balance indicates that there is not enough material (either because there is not enough material or because the inventory balance is incorrect).
At ship confirm, you: Enter Shipped Quantity that is less than Original Requested Quantity Backorder the entire delivery quantity Transfer a reservation to cycle count. This typically occurs when the material that you want to ship: Has become unavailable, for example, damaged, between picking and shipping. Is available and you backorder material for specific business reasons. For example, all available material has been allocated to a specific customer when you find out additional supply for other orders will be delayed. For information on the backorder processing in pick release and ship confirm,

- Shipped (Code C)
The delivery line’s delivery is ship confirmed and posted as intransit, OM Interface and Inventory Interface have processed, and the trip is closed.

- Cancelled (Code D)
The order line that the delivery line supports is cancelled.
=================================
:WSH_DELIVERIES_PUB.Delivery_Action:
=================================
We can call “WSH_DELIVERIES_PUB.Delivery_Action” API in order to ship confirm the delivery Programatically.

Specific Parameters:

p_action_code(Required) => action to be performed on Delivery
p_delivery_id (Required) => delivery id on which the action is performed
p_trip_name => Trip identifier for assignment of trip to delivery
p_asg_pickup_loc_code => Stop location code for pickup assignment
p_asg_pickup_dep_date => Stop location departure date for pickup assignment
p_asg_dropoff_loc_code => Stop location code for dropoff assignment
p_asg_dropoff_dep_date => Stop location departure date for dropoff assignment
p_sc_action_flag => Ship Confirm option - S, B, T, A, C. Used
p_sc_intransit_flag => Ship Confirm set in-transit flag.
p_sc_close_trip_flag => Ship Confirm close trip flag.
p_sc_create_bol_flag => Ship Confirm create Bill of Lading flag
p_sc_stage_del_flag => Ship Confirm create delivery for stage quantity flag
p_sc_trip_ship_method => Ship Confirm trip ship method.
p_wv_override_flag => Override flag for weight volume calculations.
x_trip_name => Name of autocreated trip.

What Are the P_SC_ACTION_FLAG Possible Values S, B, T, A, C?
These are the possible values and what they mean:
'S' - Ship Entered Quantities, Ship Unspecified
'B' - Ship Entered Quantities, Backorder Unspecified
'T' - Ship Entered Quantities, Stage Unspecified
'A' - Ship All
'C' - Completely Backordered

-- R12 - OM - Sample Script to Ship SO using WSH_DELIVERY_PUB API --========================================================


DECLARE

p_api_version_number NUMBER := 1.0;
init_msg_list VARCHAR2 (200);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
x_return_status VARCHAR2 (3);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (3000);
p_validation_level NUMBER;
v_errbuf VARCHAR2 (2000);
v_retcode VARCHAR2 (20);
v_released_status wsh_delivery_details.released_status%TYPE;
v_inv_interfaced_flag wsh_delivery_details.inv_interfaced_flag%TYPE;
v_oe_interfaced_flag wsh_delivery_details.oe_interfaced_flag%TYPE;
v_source_code wsh_delivery_details.source_code%TYPE;
v_pending_interface_flag wsh_trip_stops.pending_interface_flag%TYPE;
-- Parameters for WSH_DELIVERIES_PUB
p_delivery_name VARCHAR2 (30);
p_action_code VARCHAR2 (15);
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_intransit_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_sc_defer_interface_flag VARCHAR2 (60);
p_sc_send_945_flag VARCHAR2 (60);
p_sc_rule_id NUMBER;
p_sc_rule_name VARCHAR2 (60);
p_wv_override_flag VARCHAR2 (10);
p_asg_pickup_stop_seq NUMBER;
p_asg_dropoff_stop_seq NUMBER;
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
fail_api EXCEPTION;
x_debug_file VARCHAR2 (100);
l_ship_method_code VARCHAR2 (100);
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;

CURSOR c_ord_details IS

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 = '10013618'
AND det.org_id = 308
AND shipped_quantity IS NULL
AND NVL (del.status_code, 'OP') <> 'CL'
AND det.released_status = 'Y';

BEGIN

-- 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

LOOP

-- Mandatory initialization for R12
mo_global.set_policy_context ('S', i.org_id);
mo_global.init ('ONT');

-- Ship Confirming

p_delivery_name := TO_CHAR (i.delivery_id);
DBMS_OUTPUT.put_line (p_delivery_name);

BEGIN
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;
EXCEPTION
WHEN OTHERS
THEN
l_ship_method_code := NULL;
END;

p_action_code := 'CONFIRM'; -- The action code for ship confirm
p_sc_action_flag := 'S'; -- Ship entered quantity.
p_sc_intransit_flag := 'Y';
--In transit flag is set to 'Y' closes the pickup stop and sets the delivery in transit.
p_sc_close_trip_flag := 'Y'; -- Close the trip after ship confirm
p_sc_trip_ship_method := l_ship_method_code; -- The ship method code
p_sc_defer_interface_flag := 'Y';
p_sc_stage_del_flag := 'Y';
p_sc_create_bol_flag := 'N';
p_wv_override_flag := 'N';

-- API Call for Ship Confirmation

DBMS_OUTPUT.put_line
('Calling WSH_DELIVERIES_PUB to Perform Ship Confirmation');
DBMS_OUTPUT.put_line ('=============================================');

wsh_deliveries_pub.delivery_action
(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 => p_sc_intransit_flag,
p_sc_close_trip_flag => p_sc_close_trip_flag,
p_sc_create_bol_flag => p_sc_create_bol_flag,
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 => p_sc_defer_interface_flag,
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 => p_wv_override_flag,
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('Ship confirm has not been Completed For SO');
ROLLBACK;
RAISE fail_api;

ELSE
DBMS_OUTPUT.put_line ('Ship confirm Successfully Completed For SO');
COMMIT;

DBMS_OUTPUT.put_line
('Checking the Delivery Status after delivery action API Call');
DBMS_OUTPUT.put_line ('==========================================');

SELECT wdd.source_code, wdd.released_status,
wdd.inv_interfaced_flag, wdd.oe_interfaced_flag,
wts.pending_interface_flag
INTO v_source_code, v_released_status,
v_inv_interfaced_flag, v_oe_interfaced_flag,
v_pending_interface_flag
FROM wsh_trips wtr,
wsh_trip_stops wts,
wsh_delivery_legs wlg,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd
WHERE wtr.trip_id = wts.trip_id
AND wts.stop_id = wlg.pick_up_stop_id
AND wts.pending_interface_flag = 'Y'
AND wdd.inv_interfaced_flag <> 'Y'
AND wlg.delivery_id = wnd.delivery_id
AND wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wnd.delivery_id = p_delivery_name
AND wdd.source_line_id = i.source_line_id;

IF ( v_source_code = 'OE'
AND v_released_status = 'C'
AND v_inv_interfaced_flag <> 'Y'
AND v_oe_interfaced_flag <> 'Y'
AND v_pending_interface_flag = 'Y'
)

THEN

DBMS_OUTPUT.put_line
('The Delivery has been Shipped & the Next Step is - Run Interface');
DBMS_OUTPUT.put_line('Calling Interface Trip Stop');
DBMS_OUTPUT.put_line ('======================================');

-- API Call for Submitting Interface Trip Stop

wsh_ship_confirm_actions.interface_all_wrp
(errbuf => v_errbuf,
retcode => v_retcode,
p_mode => 'ALL',
p_stop_id => NULL,
p_delivery_id => p_delivery_name,
p_log_level => 0,
p_batch_id => NULL,
p_trip_type => NULL,
p_organization_id => i.organization_id,
p_num_requests => 1,
p_stops_per_batch => 1
);

DBMS_OUTPUT.put_line (v_retcode);
DBMS_OUTPUT.put_line (v_errbuf);

ELSE

DBMS_OUTPUT.put_line ('The Delivery has not Shipped Properly');
END IF;
END IF;

END LOOP;

EXCEPTION
WHEN fail_api
THEN

DBMS_OUTPUT.put_line ('==============');
DBMS_OUTPUT.put_line ('Error Details If Any');
DBMS_OUTPUT.put_line ('==============');

wsh_util_core.get_messages ('Y',
x_msg_summary,
x_msg_details,
x_msg_count
);

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

END;

Performing Pick Release Using WSH_DELIVERY_PUB API

Performing Pick Release Using WSH_DELIVERY_PUB API
************************************************************

Creating Deliveries:
===============
• A Delivery is required to perform Ship Confirm. It represent all the goods that were shipped from the same warehouse, going to the same Customer location.
• A Delivery can be created automatically or manually from the Shipping Transaction form at any time after the order lines have become “Awaiting Shipment” or can be automatically created during the Release Sales Order process.

• The delivery can also be created Programatically using shipping API “WSH_DELIVERIES_PUB.Create_update_delivery” by passing the required and optional parameter values.

Specific parameters:

• p_api_version_number => 1.0
• p_action_code => CREATE

-- for creating new delivery
• p_delivery_info => Attributes of the delivery entity of type Delivery_Pub_Rec_Type

Performing Pick Release:
===================

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 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.
• 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.

Allocate Inventory to the move order:
***************************************
• Allocating can be done automatically after the move order is created by setting “Auto Allocate” to yes on the Release Sales Order form or postponed until later, then manually allocated from the Transaction Move Orders form.
• The Release Sequence Rule, that was entered on the Release Sales Orders form, or defaulted from the Shipping Parameters, will be used to determine in what sequence to fill the move orders.
• Move orders use inventory’s picking rules to suggest the material that should be allocated. The sourcing values for subinventory, locators, revisions, and lots are defaulted on to the move order.
• The source defaults can be manually updated from the Transaction Move Orders form.
• A high level reservation is placed for the Move Order’s material requirements.
• Allocating inventory is a prerequisite for printing Pick Slips. The Pick Slip Grouping Rule entered on the Release Sales Orders form or defaulted from the Shipping Parameters is used while printing pick slips.

Pick Confirm the move order
******************************
• Pick Confirm can be done automatically during the picking process by setting “Pick Confirm” to yes on the Release Sales Orders form or by unchecking the Pick Confirm Required checkbox in the Organization Parameters form. If it is not done automatically during pick release it can be done manually from the Transaction Move Orders form by selecting the Transact button.
• The Pick Confirm transaction executes the subinventory transfer, moving the material from it’s source location to it’s destination staging location. Only 1 staging subinventory is supported per picking batch.
• The high level reservations are replaced with detail reservations.
• If you transact less than the requested quantity, the Move Order will stay open until the total quantity is transacted, or the order is closed or cancelled.
• The status of the Sales Order Line, which is linked to the Move Order, is changed to “Picked” making it eligible for Ship Confirm.
• If not all of the Move order quantity was transacted the status of the Sales Order Line, is changed to “Picked Partial”. During Ship Confirm the order line will split into 2 shipment schedules (e.g. 1.1 and 1.2). The status of the unfilled line will be changed to “Awaiting Shipping”.

For performing Pick Release Programatically we can use the shipping public API "WSH_DELIVERIES_PUB.Delivery_Action”, which enables pick release of the sales order line. The relevant pick release parameters are retrieved from the Shipping and Organization Parameter setup.

Specific Parameters:

p_action_code = > PICK_RELEASE
p_delivery_id/p_delivery_name => Id/name of delivery


-- R12 - OM - Sample Script to Perform Pick using WSH_DELIVERY_PUB API --
-- ======================================================== --

DECLARE

x_return_status VARCHAR2 (2);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
p_api_version_number NUMBER := 1.0;
init_msg_list VARCHAR2 (200);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
p_line_rows wsh_util_core.id_tab_type;
x_del_rows wsh_util_core.id_tab_type;
l_ship_method_code VARCHAR2 (100);
i NUMBER;
l_commit VARCHAR2 (30);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2 (30);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
exep_api EXCEPTION;
l_picked_flag VARCHAR2 (10);
l_return_status VARCHAR2 (1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;

CURSOR c_ord_details
IS

SELECT oha.order_number sales_order, oha.org_id, ola.line_number,
ola.shipment_number, ola.flow_status_code,
wdd.delivery_detail_id, wdd.inv_interfaced_flag,
wdd.oe_interfaced_flag, wdd.released_status
FROM apps.oe_order_headers_all oha,
apps.oe_order_lines_all ola,
apps.wsh_delivery_details wdd
WHERE oha.header_id = ola.header_id
AND oha.org_id = ola.org_id
AND oha.header_id = wdd.source_header_id
AND ola.line_id = wdd.source_line_id
AND oha.booked_flag = 'Y'
AND NVL (ola.cancelled_flag, 'N') <> 'Y'
AND wdd.released_status in ('R','B')
AND ola.flow_status_code = 'AWAITING_SHIPPING'
AND oha.order_number = 10001059
AND oha.org_id = 308;

BEGIN

-- 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);

x_return_status := wsh_util_core.g_ret_sts_success;
i := 0;

FOR i IN c_ord_details

LOOP

-- Mandatory initialization for R12
mo_global.set_policy_context ('S', i.org_id);
mo_global.init ('ONT');

p_line_rows (1) := i.delivery_detail_id;

-- API Call for Auto Create Deliveries

DBMS_OUTPUT.put_line
('Calling WSH_DELIVERY_DETAILS_PUB to Perform AutoCreate Delivery');
DBMS_OUTPUT.put_line
('====================================================');

wsh_delivery_details_pub.autocreate_deliveries
(p_api_version_number => 1.0,
p_init_msg_list => apps.fnd_api.g_true,
p_commit => l_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_rows => p_line_rows,
x_del_rows => x_del_rows
);

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
('Failed to Auto create delivery for Sales Order');
RAISE exep_api;

ELSE

DBMS_OUTPUT.put_line
('Auto Create Delivery Action has successfully completed for SO');
DBMS_OUTPUT.put_line ('=============================================');
END IF;

-- Pick release.
p_delivery_id := x_del_rows (1);
p_delivery_name := TO_CHAR (x_del_rows (1));

DBMS_OUTPUT.put_line
('Calling WSH_DELIVERIS_PUB to Perform Pick Release of SO');
DBMS_OUTPUT.put_line ('=============================================');
-- API Call for Pick Release

wsh_deliveries_pub.delivery_action (p_api_version_number => 1.0,
p_init_msg_list => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => 'PICK-RELEASE',
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => NULL,
p_asg_trip_name => NULL,
p_asg_pickup_stop_id => NULL,
p_asg_pickup_loc_id => NULL,
p_asg_pickup_stop_seq => NULL,
p_asg_pickup_loc_code => NULL,
p_asg_pickup_arr_date => NULL,
p_asg_pickup_dep_date => NULL,
p_asg_dropoff_stop_id => NULL,
p_asg_dropoff_loc_id => NULL,
p_asg_dropoff_stop_seq => NULL,
p_asg_dropoff_loc_code => NULL,
p_asg_dropoff_arr_date => NULL,
p_asg_dropoff_dep_date => NULL,
p_sc_action_flag => 'S',
p_sc_intransit_flag => 'N',
p_sc_close_trip_flag => 'N',
p_sc_create_bol_flag => 'N',
p_sc_stage_del_flag => 'Y',
p_sc_trip_ship_method => NULL,
p_sc_actual_dep_date => NULL,
p_sc_report_set_id => NULL,
p_sc_report_set_name => NULL,
p_sc_defer_interface_flag => 'Y',
p_sc_send_945_flag => NULL,
p_sc_rule_id => NULL,
p_sc_rule_name => NULL,
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 ('Failed to Pick Release the sales order');
RAISE exep_api;

ELSE

DBMS_OUTPUT.put_line ('Sales Order has successfully Pick Released');
DBMS_OUTPUT.put_line ('==============================');

END IF;

--for pick confirm
COMMIT;

END LOOP;

EXCEPTION

WHEN exep_api THEN

DBMS_OUTPUT.put_line ('==============');
DBMS_OUTPUT.put_line ('Error Details If Any');
DBMS_OUTPUT.put_line ('==============');

wsh_util_core.get_messages ('Y',
x_msg_summary,
x_msg_details,
x_msg_count
);

IF x_msg_count > 1 THEN

x_msg_data := x_msg_summary x_msg_details;
DBMS_OUTPUT.put_line (x_msg_data);

ELSE

x_msg_data := x_msg_summary x_msg_details;
DBMS_OUTPUT.put_line (x_msg_data);

END IF;

END;

Updating PO using PO_CHANGE_API1_S.Update_PO

Using the PO_CHANGE_API1_S.update_po, the following columns can be updated in a Purchase Order.

- Promised_date
- Need_by_date
- Quantity
- Unit_price

Importance of LAUNCH_APPROVALS_FLAG
=================================

- Indicates if you want to launch APPROVAL workflow after the update. Its value could be either 'Y' or 'N'. If not provided, the default value is 'N'.
- If we are passing launch_approvals_flag =>'N', the PO is remaining in the Requires Reapproval status.

Post Update PO Validation:
====================
1. Set the PO status to REQUIRES REAPPROVAL.
2. Increment revision number if the PO was in APPROVED status before the update.
3. Launch the PO Approval workflow if LAUNCH_APPROVALS_FLAG = 'Y'.
4. If the quantity was adjusted down to be equal to the total quantity received or billed, then set the appropriate closed code and roll up the closed code to line and header levels.

-- R12 - PO - Sample Script to Update PO Using po_change_api1_s
--=================================================


DECLARE

CURSOR po_date_update
IS

SELECT pha.segment1 po_number, pha.revision_num,pha.po_header_id,
pha.authorization_status, pla.po_line_id, pla.line_num, pha.org_id,
pla.unit_price, pola.line_location_id, pola.shipment_num,
pola.quantity, pola.promised_date, pola.need_by_date,
pha.closed_code
FROM po_headers_all pha, po_lines_all pla,
po_line_locations_all pola
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pola.po_line_id
AND NVL (pha.approved_flag, 'N') = 'Y'
AND NVL (pola.cancel_flag, 'N') <> 'Y'
AND NVL (pola.closed_code, 'OPEN') = 'OPEN'
AND NVL (pola.quantity_received, 0) = 0
AND NVL (pola.quantity_billed, 0) = 0
AND pha.segment1 = '11170007326'
AND pla.line_num = 1
AND pha.type_lookup_code = 'STANDARD'
AND pha.org_id = 308;

l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_result NUMBER;
l_api_errors po_api_errors_rec_type;
l_revision_num NUMBER;
l_promised_date DATE;
l_need_by_date DATE;
l_price po_lines_all.unit_price%TYPE;
l_quantity po_line_locations_all.quantity%TYPE;

BEGIN

SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'CONVERSION';

SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = 'Purchasing Super User';

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN po_date_update

LOOP

mo_global.set_policy_context ('S', i.org_id);
mo_global.init ('PO');

l_promised_date := i.promised_date + 30;
l_need_by_date := i.need_by_date + 35;
l_quantity := i.quantity + 100;
l_price := i.unit_price - 1;

DBMS_OUTPUT.put_line ('Calling po_change_api1_s.update_po To Update PO');
DBMS_OUTPUT.put_line ('===================================');
DBMS_OUTPUT.put_line ('Retrieving the Current Revision Number of PO');

select revision_num
into l_revision_num
from po_headers_all
where segment1 = i.po_number;

l_result :=
po_change_api1_s.update_po
(x_po_number => i.po_number, --Enter the PO Number
x_release_number => NULL, --Enter the Release Num
x_revision_number => l_revision_num, --Enter the Revision Number
x_line_number => i.line_num, --Enter the Line Number
x_shipment_number => i.shipment_num, --Enter the Shipment Number
new_quantity => l_quantity, --Enter the new quantity
new_price => l_price, --Enter the new price,
new_promised_date => l_promised_date, --Enter the new promised date,
new_need_by_date => l_need_by_date, --Enter the new need by date,
launch_approvals_flag => 'Y',
update_source => NULL,
VERSION => '1.0',
x_override_date => NULL,
x_api_errors => l_api_errors,
p_buyer_name => NULL,
p_secondary_quantity => NULL,
p_preferred_grade => NULL,
p_org_id => i.org_id
);

DBMS_OUTPUT.put_line (l_result);

IF (l_result = 1)
THEN
DBMS_OUTPUT.put_line('Successfully update the PO :=>');
END IF;

IF (l_result <> 1)
THEN
DBMS_OUTPUT.put_line ('Failed to update the PO Due to Following Reasons');
-- Display the errors
FOR j IN 1 .. l_api_errors.MESSAGE_TEXT.COUNT
LOOP
DBMS_OUTPUT.put_line (l_api_errors.MESSAGE_TEXT (j));
END LOOP;
END IF;

END LOOP;

END;

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

Alternative Way of Deleting an Incomplete Purchase Order

Alternative Way of Deleting an Incomplete Purchase Order
************************************************************

Before approving the Purchase Order & when the PO is in INCOMPLETE status, Oracle allows us to delete the same from the Purchase Order Form. The same can be achieved using the following mentioned API.

But we need to remember few points before using this API for deleting Incomplete PO.

- The PO Should be INCOMPLETE & Should not be Approved Once.
- Once the PO is approved, records gets inserted into mtl_supply & all the archive tables.
This API never deletes the records from the mtl_supply & archive tables and only deletes the
records from the base tables.

-- Sample Script to Delete Incomplete PO Using 'PO_HEADERS_SV1'

--=================================================

DECLARE

l_deleted BOOLEAN;

CURSOR c_po_det IS

SELECT po_header_id, segment1,org_id
FROM po.po_headers_all pha
WHERE 1 = 1
AND segment1 = '11170002356'
AND org_id = 308
AND NVL (approved_flag, 'N') <> 'Y'
AND NVL (cancel_flag, 'N') = 'N'
AND type_lookup_code = 'STANDARD'
AND authorization_status = 'INCOMPLETE'
AND NOT EXISTS ( SELECT 1
FROM po_headers_archive_all a
WHERE pha.po_header_id = a.po_header_id
AND pha.org_id = a.org_id)
AND NOT EXISTS ( SELECT 1
FROM mtl_supply b WHERE pha.po_header_id = b.po_header_id AND
supply_type_code = 'PO');

BEGIN

FOR c1 IN c_po_det LOOP

DBMS_OUTPUT.put_line ( 'Calling PO_HEADERS_SV1 API To Delete PO');
DBMS_OUTPUT.put_line ( '==========================');

l_deleted := po_headers_sv1.delete_po (c1.po_header_id, 'STANDARD');

IF l_deleted = TRUE
THEN
COMMIT;
DBMS_OUTPUT.put_line ( 'Successfully Deleted the PO');
ELSE
ROLLBACK;
DBMS_OUTPUT.put_line ( 'Failed to Delete the PO');
END IF;
END LOOP;

END;

Cancelling Single AP Invoice using ap_cancel_package API

R12 - Sample Script to Cancel Single AP Invoice using ap_cancel_package API

DECLARE

l_message_name VARCHAR2 (1000);
l_invoice_amount NUMBER;
l_base_amount NUMBER;
l_tax_amount NUMBER;
l_temp_cancelled_amount NUMBER;
l_cancelled_by VARCHAR2 (1000);
l_cancelled_amount NUMBER;
l_cancelled_date DATE;
l_last_update_date DATE;
l_original_prepayment_amount NUMBER;
l_pay_curr_invoice_amount NUMBER;
l_token VARCHAR2 (100);
l_boolean BOOLEAN;
l_user_id NUMBER := 2083;
l_resp_id NUMBER := 20639;
l_appl_id NUMBER := 200;

CURSOR C_Inv_Det is

SELECT distinct aia.*
FROM ap_invoices_all aia, ap_invoice_lines_all aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.org_id = aila.org_id
AND aia.org_id = 308
AND aia.invoice_num = '300040823'
AND aia.payment_status_flag = 'N'
AND NVL(aila.cancelled_flag, 'N') <> 'Y';

BEGIN

fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);

FOR l_inv_rec IN C_Inv_Det LOOP

mo_global.init ('SQLAP');
mo_global.set_policy_context ('S', l_inv_rec.org_id);

DBMS_OUTPUT.put_line ('Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice: ' l_inv_rec.invoice_num);
DBMS_OUTPUT.put_line ('**************************************************************');

l_boolean := ap_cancel_pkg.ap_cancel_single_invoice
(p_invoice_id => l_inv_rec.invoice_id,
p_last_updated_by => l_inv_rec.last_updated_by,
p_last_update_login => l_inv_rec.last_update_login,
p_accounting_date => l_inv_rec.gl_date,
p_message_name => l_message_name,
p_invoice_amount => l_invoice_amount,
p_base_amount => l_base_amount,
p_temp_cancelled_amount => l_temp_cancelled_amount,
p_cancelled_by => l_cancelled_by,
p_cancelled_amount => l_cancelled_amount,
p_cancelled_date => l_cancelled_date,
p_last_update_date => l_last_update_date,
p_original_prepayment_amount => l_original_prepayment_amount,
p_pay_curr_invoice_amount => l_pay_curr_invoice_amount,
P_Token => l_token,
p_calling_sequence => NULL);

DBMS_OUTPUT.put_line ('l_message_name => ' l_message_name);
DBMS_OUTPUT.put_line ('l_invoice_amount => ' l_invoice_amount);
DBMS_OUTPUT.put_line ('l_base_amount => ' l_base_amount);
DBMS_OUTPUT.put_line ('l_tax_amount => ' l_tax_amount);
DBMS_OUTPUT.put_line ('l_temp_cancelled_amount => ' l_temp_cancelled_amount);
DBMS_OUTPUT.put_line ('l_cancelled_by => ' l_cancelled_by);
DBMS_OUTPUT.put_line ('l_cancelled_amount => ' l_cancelled_amount);
DBMS_OUTPUT.put_line ('l_cancelled_date => ' l_cancelled_date);
DBMS_OUTPUT.put_line ('P_last_update_date => ' l_last_update_date);
DBMS_OUTPUT.put_line ('P_original_prepayment_amount => ' l_original_prepayment_amount);
DBMS_OUTPUT.put_line ('l_pay_curr_invoice_amount => ' l_pay_curr_invoice_amount);

IF l_boolean
THEN
DBMS_OUTPUT.put_line ('Successfully Cancelled the Invoice => ' l_inv_rec.invoice_num);
COMMIT;
ELSE
DBMS_OUTPUT.put_line ('Failed to Cancel the Invoice => ' l_inv_rec.invoice_num);
ROLLBACK;
END IF;

END LOOP;
END;

DBMS Output -

Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice: 300040823
*********************************************************************************
l_message_name =>
l_invoice_amount => 0
l_base_amount => 0
l_tax_amount =>
l_temp_cancelled_amount => 15678
l_cancelled_by => 1831
l_cancelled_amount => 15678
l_cancelled_date => 06-OCT-09
P_last_update_date => 06-OCT-09
P_original_prepayment_amount =>
l_pay_curr_invoice_amount => 0
Successfully Cancelled the Invoice => 300040823

Script to Cancel Sales Order Line Using Oe_order_pub

-- R12 - OM - Sample Script to Cancel Sales Order Line Using Oe_order_pub
=========================================================

DECLARE

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';

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 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;

oe_msg_pub.delete_msg;
mo_global.init ('ONT');
mo_global.set_policy_context ('S', iso_rec.org_id);

oe_order_pub.process_order
(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);

END LOOP;

END IF;

DBMS_OUTPUT.ENABLE (10000);

DBMS_OUTPUT.put_line ('Sales Order => '
iso_rec.order_number
' - Line Number => '
iso_rec.line_number
' - Shipment Number => '
iso_rec.shipment_number
' Having Line ID=> '
iso_rec.line_id
' Cancelled Successfully' );

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

END LOOP;

END;

Receiving PO Using Receiving Open Interface (ROI)

The Receiving Transaction Processor processes pending or unprocessed receiving transactions. We can receive the Purchase Order either using the Expected Receipt form or by putting the record into the Receiving Open Interface (ROI). And then if we will submit the receiving transactions processor so the PO will be received.

Records needs to be inserted into rcv_transactions_interface with processing_status_code and transaction_status_code as 'PENDING' and transaction_type of 'RECEIVE'. and also inserted into rcv_shipment_headers which creates the shipment header.

Interface Tables: -

• rcv_headers_interface
• rcv_transactions_interface
• mtl_transaction_lots_interface

Error Table: -

• po_interface_errors

Base Tables:

• rcv_shipment_headers
• rcv_shipment_lines
• rcv_transactions
• mtl_lot_numbers
• mtl_material_transactions
• rcv_lot_transactions

R12 - Sample Procedure to Receive PO by inserting records into ROI

DECLARE

x_user_id NUMBER;
x_resp_id NUMBER;
x_appl_id NUMBER;
x_po_header_id NUMBER;
x_vendor_id NUMBER;
x_segment1 VARCHAR2 (20);
x_org_id NUMBER;
x_line_num NUMBER;
l_chr_lot_number VARCHAR2 (50);
l_chr_return_status VARCHAR2 (2000);
l_num_msg_count NUMBER;
l_chr_msg_data VARCHAR2 (50);
v_count NUMBER;

BEGIN

DBMS_OUTPUT.put_line ('RCV Sample Insert Script Starts');
DBMS_OUTPUT.put_line ('**************************************');

SELECT po_header_id, vendor_id, segment1, org_id
INTO x_po_header_id, x_vendor_id, x_segment1, x_org_id
FROM po_headers_all
WHERE segment1 = '380087' -- Enter The Po Number which needs to be received
AND org_id = 308 -- Enter the org_id
AND approved_flag = 'Y'
AND nvl(cancel_flag, 'N') = 'N';

SELECT DISTINCT
u.user_id,
to_char(a.responsibility_id) responsibility_id,
b.application_id
INTO
x_user_id, x_resp_id, x_appl_id
from
apps.fnd_user_resp_groups_direct a,
apps.fnd_responsibility_vl b,
apps.fnd_user u,
apps.fnd_application fa
where
a.user_id = u.user_id
and a.responsibility_id = b.responsibility_id
and a.responsibility_application_id = b.application_id
and sysdate between a.start_date and nvl(a.end_date,sysdate+1)
and fa.application_id (+) = b.application_id
and upper(u.user_name) = 'A42485' -- Enter the User_name
and b.responsibility_name = 'Inventory'; -- Enter The Responsibility Name

DBMS_OUTPUT.put_line ('Inserting the Record into Rcv_headers_interface');
DBMS_OUTPUT.put_line ('*********************************************');

INSERT INTO rcv_headers_interface
(header_interface_id, GROUP_ID, processing_status_code,
receipt_source_code, transaction_type, last_update_date,
last_updated_by, last_update_login, creation_date, created_by,
vendor_id,expected_receipt_date, validation_flag)
SELECT rcv_headers_interface_s.NEXTVAL, rcv_interface_groups_s.NEXTVAL,
'PENDING', 'VENDOR', 'NEW', SYSDATE, x_user_id, 0,SYSDATE, x_user_id,
x_vendor_id, SYSDATE, 'Y'
FROM DUAL;

DECLARE

CURSOR po_line
IS
SELECT
pl.org_Id, pl.po_header_id, pl.item_id, pl.po_line_id, pl.line_num, pll.quantity,
pl.unit_meas_lookup_code, mp.organization_code,
pll.line_location_id, pll.closed_code, pll.quantity_received,
pll.cancel_flag, pll.shipment_num,
pda.destination_type_code,
pda.deliver_to_person_id,
pda.deliver_to_location_id,
pda.destination_subinventory,
pda.destination_organization_id
FROM po_lines_all pl, po_line_locations_all pll,mtl_parameters mp, apps.po_distributions_all pda
WHERE pl.po_header_id = x_po_header_id
AND pl.po_line_id = pll.po_line_id
AND pll.line_location_id = pda.line_location_id
AND pll.ship_to_organization_id = mp.organization_id;

BEGIN

FOR rec_det IN po_line LOOP

IF rec_det.closed_code IN ('APPROVED', 'OPEN')
AND rec_det.quantity_received <>

THEN

DBMS_OUTPUT.put_line ('Inserting the Record into Rcv_Transactions_Interface');
DBMS_OUTPUT.put_line ('*********************************************');

INSERT INTO rcv_transactions_interface
(interface_transaction_id, GROUP_ID,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login, transaction_type,
transaction_date, processing_status_code,
processing_mode_code, transaction_status_code,
po_header_id, po_line_id, item_id, quantity, unit_of_measure,
po_line_location_id, auto_transact_code,
receipt_source_code, to_organization_code,
source_document_code, document_num,
destination_type_code,deliver_to_person_id,
deliver_to_location_id,subinventory,
header_interface_id, validation_flag)
SELECT rcv_transactions_interface_s.NEXTVAL,
rcv_interface_groups_s.CURRVAL, SYSDATE, x_user_id,
SYSDATE, x_user_id, 0, 'RECEIVE', SYSDATE, 'PENDING',
'BATCH', 'PENDING', rec_det.po_header_id,rec_det.po_line_id,
rec_det.item_id, rec_det.quantity,
rec_det.unit_meas_lookup_code,
rec_det.line_location_id, 'DELIVER', 'VENDOR',
rec_det.organization_code, 'PO', x_segment1,
rec_det.destination_type_code, rec_det.deliver_to_person_id,
rec_det.deliver_to_location_id, rec_det.destination_subinventory,
rcv_headers_interface_s.CURRVAL, 'Y'
FROM DUAL;

DBMS_OUTPUT.put_line ('PO line:' rec_det.line_num ' Shipment: ' rec_det.shipment_num ' has been inserted into ROI.');

select count(*)
into v_count
from mtl_system_items
where inventory_item_id = rec_det.item_id
and lot_control_code = 2 -- 2 - full_control, 1 - no control
and organization_id = rec_det.destination_organization_id;

IF v_count > 0 then

DBMS_OUTPUT.put_line ('The Ordered Item is Lot Controlled');
DBMS_OUTPUT.put_line ('Generate the Lot Number for the Lot Controlled Item');

BEGIN

-- initialization required for R12
mo_global.set_policy_context ('S', rec_det.org_id);
mo_global.init ('INV');
-- Initialization for Organization_id
inv_globals.set_org_id (rec_det.destination_organization_id);
-- initialize environment
fnd_global.apps_initialize (user_id => x_user_id,
resp_id => x_resp_id,
resp_appl_id => x_appl_id);

DBMS_OUTPUT.put_line ('Calling inv_lot_api_pub.auto_gen_lot API to Create Lot Numbers');
DBMS_OUTPUT.put_line ('*********************************************');

l_chr_lot_number :=
inv_lot_api_pub.auto_gen_lot
(p_org_id => rec_det.destination_organization_id,
p_inventory_item_id => rec_det.item_id,
p_parent_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
p_api_version => 1.0,
p_init_msg_list => 'F',
p_commit => 'T',
p_validation_level => 100,
x_return_status => l_chr_return_status,
x_msg_count => l_num_msg_count,
x_msg_data => l_chr_msg_data);

IF l_chr_return_status = 'S' THEN
COMMIT;
ELSE
ROLLBACK;
END IF;

DBMS_OUTPUT.put_line ('Lot Number Created for the item is => ' l_chr_lot_number);

END;

DBMS_OUTPUT.put_line ('Inserting the Record into mtl_transaction_lots_interface ');
DBMS_OUTPUT.put_line ('*********************************************');

INSERT INTO mtl_transaction_lots_interface
( transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
lot_number,
transaction_quantity,
primary_quantity,
serial_transaction_temp_id,
product_code,
product_transaction_id)
(select
mtl_material_transactions_s.nextval,--transaction_interface_id
sysdate, --last_update_date
x_user_id, --last_updated_by
sysdate, --creation_date
x_user_id, --created_by
-1, --last_update_login
l_chr_lot_number, --lot_number
rec_det.quantity, --transaction_quantity
rec_det.quantity, --primary_quantity
NULL, --serial_transaction_temp_id
'RCV', --product_code
rcv_transactions_interface_s.currval --product_transaction_id
from dual);

ELSE

DBMS_OUTPUT.put_line ('The Ordered Item is Not Lot Controlled');
DBMS_OUTPUT.put_line ('********************************************');

END IF;

ELSE
DBMS_OUTPUT.put_line ( 'PO line ' rec_det.line_num'-' rec_det.shipment_num ' is either closed, cancelled, received.');
DBMS_OUTPUT.put_line ('*********************************************');

END IF;

END LOOP;

DBMS_OUTPUT.put_line ('RCV Sample Insert Script Ends');
DBMS_OUTPUT.put_line ('*****************************************');

END;

COMMIT;

END;

-- Cross Check the Records in the Interface Table

select * from apps.rcv_headers_interface
where created_by = 2083
and group_id = ***

select *
from apps.rcv_transactions_interface
where created_by = 2083
and group_id = ***

select * from apps.mtl_transaction_lots_interface
where created_by = 2083
and lot_number = ***
and product_transaction_id in
(select interface_transaction_id from apps.rcv_transactions_interface
where created_by = 2083 and group_id = ***)

-- Check for the Error

select * from po_interface_errors
where batch_id = ***

-- Reprocessing the records from the interface if the same errored out there.

UPDATE rcv_headers_interface
SET processing_request_id = NULL,
validation_flag = 'Y',
processing_status_code = 'PENDING'
WHERE GROUP_ID = ***

UPDATE rcv_transactions_interface
SET request_id = NULL,
processing_request_id = NULL,
validation_flag = 'Y',
processing_status_code = 'PENDING',
transaction_status_code = 'PENDING',
processing_mode_code = 'BATCH'
WHERE interface_transaction_id = ***
AND batch_id = ***


-- Verification of the base tables Once the Receiving Transactions Processor is Completed

select * from apps.rcv_shipment_headers
where created_by = 2083

select * from apps.rcv_shipment_lines
where created_by = 2083
and po_header_id = 619
select * from apps.rcv_transactions
where po_header_id = 619
and created_by = 2083

select * from apps.mtl_lot_numbers
where lot_number in ('A6631684', 'A6631685', 'A6631686')
select * from apps.rcv_lot_transactions
where lot_num in ('A6631684', 'A6631685', 'A6631686')
select * from apps.mtl_material_transactions
where created_by = 2083
and rcv_transaction_id in (select transaction_id from apps.rcv_transactions
where po_header_id = 619
and created_by = 2083)

SELECT (SELECT segment1
FROM po_headers_all
WHERE po_header_id = pl.po_header_id
AND org_id = pl.org_id) po_number, pl.po_header_id,
pl.item_id, pl.po_line_id, pl.line_num, pll.shipment_num,
pll.quantity, pl.unit_meas_lookup_code, mp.organization_code, pll.line_location_id,
pll.closed_code, pll.quantity_received, pll.cancel_flag,
pll.shipment_num, pda.destination_type_code, pda.deliver_to_person_id,
pda.deliver_to_location_id, pda.destination_subinventory
FROM
apps.po_lines_all pl,
apps.po_line_locations_all pll,
apps.mtl_parameters mp,
apps.po_distributions_all pda
WHERE 1 = 1
AND pl.po_header_id = 619
AND pl.org_id = 308
AND pl.po_line_id = pll.po_line_id
AND pll.line_location_id = pda.line_location_id
AND pll.ship_to_organization_id = mp.organization_id
order by 1, 5, 6

A Small Correction

For your information:

The scripts which were uploaded in the blog are tested in R12.1.1 version. After throughly checking & crossverifying the result I am uploading them.

The Issue what I am observing is that the concatenated operator ('') [or may the special characters] are not coming upon into the blog. I am really sorry about this. At this point I am running out of ideas how is this going to come up. I will definitely rectify these once I am come up with any solution for this.

Regads,
Jyoti Ranjan Mohanty

To Check for the available onhand For An Item in a Given Organization Using Inv_Quantity_Tree_Pub API

Inv_Quantity_Tree_Pub API can be used for querying the available Onhand in a given subinventory or organization.

The output of the API inv_quantity_tree_pub.query_quantities will show the Total Available Onhand , Reservations , Suggestions and the Actual Onhand that can be Transacted.

-- R12 - INV - Sample Script to Get Onhand Using INV_Quantity_Tree_PUB API:

DECLARE

l_api_return_status VARCHAR2 (1);
l_qty_oh NUMBER;
l_qty_res_oh NUMBER;
l_qty_res NUMBER;
l_qty_sug NUMBER;
l_qty_att NUMBER;
l_qty_atr NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
v_item VARCHAR2 (250) := '&Item_Num';
v_org VARCHAR2 (10) := '&Org_code';

Cursor c_item_info is

SELECT concatenated_segments item, msi.inventory_item_id,msi.organization_id, mp.organization_code
FROM mtl_system_items_kfv msi, mtl_parameters mp
WHERE concatenated_segments = v_item
AND msi.organization_id = mp.organization_id
AND mp.organization_code = v_org;

BEGIN

inv_quantity_tree_grp.clear_quantity_cache;

DBMS_OUTPUT.put_line ('Transaction Mode');

For i in c_item_info

LOOP

DBMS_OUTPUT.put_line ('Extracting the Onhand For the Item ===========> ' i.item);
DBMS_OUTPUT.put_line ('Extracting the Onhand For the Organization ======> ' i.organization_code);

apps.inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => l_api_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => i.organization_id,
p_inventory_item_id => i.inventory_item_id,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_onhand_source => 3,
p_is_revision_control => FALSE,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
x_qoh => l_qty_oh,
x_rqoh => l_qty_res_oh,
x_qr => l_qty_res,
x_qs => l_qty_sug,
x_att => l_qty_att,
x_atr => l_qty_atr);

DBMS_OUTPUT.put_line ('Quantity on hand ======================> ' TO_CHAR (l_qty_oh));
DBMS_OUTPUT.put_line ('Reservable quantity on hand ===============> ' TO_CHAR (l_qty_res_oh));
DBMS_OUTPUT.put_line ('Quantity reserved =====================> ' TO_CHAR (l_qty_res));
DBMS_OUTPUT.put_line ('Quantity suggested ====================> ' TO_CHAR (l_qty_sug));
DBMS_OUTPUT.put_line ('Quantity Available To Transact ==============> ' TO_CHAR (l_qty_att));
DBMS_OUTPUT.put_line ('Quantity Available To Reserve ==============> ' TO_CHAR (l_qty_atr));

END LOOP;
END;

DBMS OUTPUT:

Transaction Mode
Extracting the Onhand For the Item =========> ELXV350ELL121MH12D^NIPPONSCC
Extracting the Onhand For the Organization ====> A66
Quantity on hand =======================> 3400
Reservable quantity on hand ===============> 3400
Quantity reserved =======================> 1000
Quantity suggested ======================> 0
Quantity Available To Transact==============> 2400
Quantity Available To Reserve ==============> 2400

Use of inv_lot_api_pub API for Inserting & Auto Generating the Lot Numbers

A lot number is a combination of an alphanumeric prefix and a numeric suffix. When we define an item, we can specify the starting lot prefix and the starting lot number. Oracle Inventory uses this information to generate defaults during transaction entry.

1. Establish lot control for an item.

We can establish lot control for an item when We define it. We can choose from No control or Full control. If We choose lot control We must assign lot numbers when We receive the item into inventory. Thereafter, when We transact this item, We must provide a lot number We specified when We received the item.We can update lot control options for an item if it has zero on-hand quantity.
2 . Establish lot number uniqueness.

We use the Organization Parameters window to specify whether lot numbers should be unique for an item. If We do not establish lot number uniqueness, We can assign the same lot number to multiple items in the same organization and across organizations.

If We control lot number uniqueness at the Master level, We can assign a specific lot number only to one item in the same organization and across organizations. When We perform transactions, Oracle Inventory checks the lot number uniqueness control to generate lot number defaults.

3. Optionally, determine whether to automatically generate lot number defaults.

We use the Organization Parameters window to specify how to generate lot number defaults. We can choose to generate sequential lot numbers based on an alphanumeric prefix We specify when We define an item. Oracle Inventory can also generate lot number defaults for the entire organization. In this case, We must define a lot number prefix at the Organization level in the Organization Parameters window.

How to generate/insert lot numbers using inv_lot_api_pub.auto_gen_lot API?================================================================

-- R12 - INV - Sample Script to Generate Lot Number using inv_lot_api_pub

DECLARE

l_chr_lot_number VARCHAR2 (50);
l_chr_return_status VARCHAR2 (2000);
l_num_msg_count NUMBER;
l_chr_msg_data VARCHAR2 (50);

Cursor c_item_info is

select * from mtl_system_items_kfv
where concatenated_segments = 'TSTITEM^3M' -- Enter the item for which Lot Number needs to be created
and organization_id = 381; -- Enter the organization_id

BEGIN

-- initialization required for R12
mo_global.set_policy_context ('S', 308);
mo_global.init('INV');

-- Initialization for Organization_id
inv_globals.set_org_id (381);

-- initialize environment
fnd_global.apps_initialize (user_id => 2083,
resp_id => 20634,
resp_appl_id => 401);

For i in c_item_info

LOOP
dbms_output.put_line ('Calling inv_lot_api_pub.auto_gen_lot API to Create Lot Numbers');
dbms_output.put_line ('*********************************************');

l_chr_lot_number := inv_lot_api_pub.auto_gen_lot (
p_org_id => i.organization_id,
p_inventory_item_id => i.inventory_item_id,
p_parent_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
p_api_version => 1.0,
p_init_msg_list => 'F',
p_commit => 'T',
p_validation_level => 100,
x_return_status => l_chr_return_status,
x_msg_count => l_num_msg_count,
x_msg_data => l_chr_msg_data);

dbms_output.put_line ('The Status Returned by the API is => ' l_chr_return_status);

IF l_chr_return_status = 'S'
THEN
COMMIT;
ELSE
ROLLBACK;
END IF;

dbms_output.put_line ('The Message Count Returned by the API is => ' l_num_msg_count);
dbms_output.put_line ('The Message Returned by the API is => ' l_chr_return_status);
dbms_output.put_line ('Lot Number Created for the item ' i.concatenated_segments ' is => ' l_chr_lot_number);

END LOOP;

END;

-- R12 - INV - Sample Script to Insert Lot Number using inv_lot_api_pub


DECLARE
x_object_id NUMBER;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (4000);
x_expire_date DATE;

Cursor c_item_info is

LOOP

select * from mtl_system_items_kfv
where concatenated_segments = 'TSTITEM^3M' -- Enter the item for which Lot Number needs to be created
and organization_id = 381; -- Enter the organization_id

BEGIN

-- initialization required for R12
mo_global.set_policy_context ('S', 308);
mo_global.init('INV');

-- Initialization for Organization_id
inv_globals.set_org_id (381);

-- initialize environment
fnd_global.apps_initialize (user_id => 2083,
resp_id => 20634,
resp_appl_id => 401);

For i in c_item_info

dbms_output.put_line ('Calling inv_lot_api_pub.auto_gen_lot API to Create Lot Numbers');
dbms_output.put_line ('*********************************************');

inv_lot_api_pub.insertlot
(p_api_version => 1,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_inventory_item_id => i.inventory_item_id,
p_organization_id => i.organization_id,
p_lot_number => 'A6644001',
p_expiration_date => x_expire_date,
x_object_id => x_object_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);

dbms_output.put_line ('The Status Returned by the API is => 'x_return_status);

IF x_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
ELSE
ROLLBACK;
END IF;

DBMS_OUTPUT.put_line ('x_object_id :' x_object_id);
DBMS_OUTPUT.put_line ('x_msg_count :' x_msg_count);
DBMS_OUTPUT.put_line ('x_msg_data :' x_msg_data);

END LOOP;
END;