Creating Move Order
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,
SUM (a.primary_transaction_quantity) total_onhand
FROM mtl_onhand_quantities_detail a,
mtl_system_items_kfv b,
org_organization_definitions c
WHERE a.inventory_item_id = b.inventory_item_id
AND a.organization_id = b.organization_id
AND a.organization_id = c.organization_id
AND b.concatenated_segments = 'AS54888'
AND c.organization_code = 'M1'
GROUP BY a.organization_id,
c.operating_unit,
a.inventory_item_id,
b.concatenated_segments,
a.subinventory_code;
BEGIN
SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = 'MFG';
SELECT fr.responsibility_id,
fr.application_id
INTO l_resp_id,
l_appl_id
FROM fnd_responsibility fr,
fnd_responsibility_tl frt
WHERE fr.responsibility_id = frt.responsibility_id
AND frt.responsibility_name = 'Manufacturing and Distribution Manager'; --Responsibility
fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
DBMS_OUTPUT.put_line ('Creating MO');
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 = '01-520-5250-0000-000';
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; --Type:Move 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 := 'PTIAN Move Order Test';
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 := 11;
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 ('===================================');
DBMS_OUTPUT.put_line ('Calling INV_MOVE_ORDER_PUB to Create MO');
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 :=> '||x_hdr_rec.request_number);
DBMS_OUTPUT.put_line ('===================================');
ELSE
ROLLBACK;
DBMS_OUTPUT.put_line ('Move Order Creation Failed Due to Following Reasons');
DBMS_OUTPUT.put_line ('===================================');
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;
Allocating Move Order
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 = '4076052'
AND mtrh.organization_id = 207;
BEGIN
SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = 'MFG';
SELECT responsibility_id,
application_id
INTO l_resp_id,
l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = 'Manufacturing and Distribution Manager';
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 ('==========================================================');
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 ('return status:'||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: '||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;
Transacting Move Order
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 = '4076052'
AND mtrh.organization_id = 207;
BEGIN
SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = 'MFG';
SELECT responsibility_id,
application_id
INTO l_resp_id,
l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = 'Manufacturing and Distribution Manager';
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 ('Return Status:'||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;
Reference Note:
Note 729998.1:Oracle Inventory Management Application Program Interface (APIs)
Note 729265.1:How To Process Move Orders Using INV_MOVE_ORDER_PUB.
Note 729513.1 How to use APIs: This demonstration walks the user through the API specifics, creating the PL/SQL procedure for using an API and also debugging guidelines.
Note 729261.1 How To Create A Move Order Header Using INV_MOVE_ORDER_PUB.Create_Move_Order_Header API
Note 729263.1 How To Create Move Order Lines Using INV_MOVE_ORDER_PUB.Create_Move_Order_Lines API
Note 729265.1 How To Process Move Orders Using INV_MOVE_ORDER_PUB.Process_Move_Order API