Move Orders Allocate and Transact APIs in R12

1. Create a package using the below script.

Package Specification:

CREATE OR REPLACE PACKAGE APPS.xxcona_transact_move_orders AUTHID CURRENT_USER

----------------------------------------------------------------------------------------------------

-- File name : xxcona_transact_move_orders.prc

-- Author : Arjun Prasad

-- Created : 18-May-2015

-- Description : This PL/SQL procedure is used to Transact Move Order From Inventory.

-----------------------------------------------------------------------------------------------------

-- Date Author Version Reason

-------------- --------------- -------- ----------------------------------------------------------

-- 18-May-2015 Arjun Prasad 1.0 Initial creation

-----------------------------------------------------------------------------------------------------

-- WHO columns

l_user_id NUMBER := -1;

l_resp_id NUMBER := -1;

l_application_id NUMBER := -1;

l_user_name VARCHAR2 (30) := 'CONACENT';

l_resp_name VARCHAR2 (30) := 'INVENTORY';

PROCEDURE xxcona_alloc_move_order (

p_move_order_line_id IN NUMBER,

x_return_status OUT VARCHAR2,

x_detailed_qty OUT NUMBER

);

PROCEDURE xxcona_process_move_order_line (

p_move_order_line_id IN NUMBER,

p_new_quantity_detailed IN NUMBER,

x_return_status OUT VARCHAR2

);

PROCEDURE xxcona_transact_mo_line (

p_header_id IN NUMBER,

p_line_id IN NUMBER

);

PROCEDURE xxcona_main (

p_move_order_no IN VARCHAR2,

p_organization_id IN NUMBER

);

END xxcona_transact_move_orders;

Package Body:

CREATE OR REPLACE PACKAGE BODY APPS.xxcona_transact_move_orders

PROCEDURE xxcona_alloc_move_order (

p_move_order_line_id IN NUMBER,

x_return_status OUT VARCHAR2,

x_detailed_qty OUT NUMBER

)

-- Common Declarations

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

-- WHO columns

--l_user_id NUMBER := -1;

--l_resp_id NUMBER := -1;

--l_application_id NUMBER := -1;

l_row_cnt NUMBER := 1;

--l_user_name VARCHAR2 (30) := 'CONACENT';

--l_resp_name VARCHAR2 (30) := 'INVENTORY';

-- API specific declarations

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;

l_line_id mtl_txn_request_lines.line_id%TYPE

:= p_move_order_line_id;

l_move_order_type mtl_txn_request_headers.move_order_type%TYPE

:= 1;

--x_detailed_qty NUMBER := 0;

x_number_of_rows NUMBER := 0;

x_revision VARCHAR2 (3);

x_locator_id NUMBER := 0;

x_transfer_to_location NUMBER := 0;

x_lot_number VARCHAR2 (30);

x_expiration_date DATE;

x_transaction_temp_id NUMBER := 0;

BEGIN

-- Get the user_id

/*SELECT user_id

INTO l_user_id

FROM fnd_user

WHERE user_name = l_user_name;

-- Get the application_id and responsibility_id

SELECT application_id, responsibility_id

INTO l_application_id, l_resp_id

FROM fnd_responsibility

WHERE responsibility_key = l_resp_name;

fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);

-- MFG / Mfg Mgr / INV

DBMS_OUTPUT.put_line ( 'Initialized applications context: '

|| l_user_id

|| ' '

|| l_resp_id

|| ' '

|| l_application_id

);*/

-- Allocate each line of the Move Order

inv_replenish_detail_pub.line_details_pub

(p_line_id => l_line_id,

x_number_of_rows => x_number_of_rows,

x_detailed_qty => x_detailed_qty,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

x_revision => x_revision,

x_locator_id => x_locator_id,

x_transfer_to_location => x_transfer_to_location,

x_lot_number => x_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 => l_move_order_type,

p_serial_flag => fnd_api.g_false,

p_plan_tasks => FALSE

--FND_API.G_FALSE

p_auto_pick_confirm => FALSE

--FND_API.G_FALSE

p_commit => TRUE

--FND_API.G_FALSE

);

DBMS_OUTPUT.put_line

('==========================================================');

DBMS_OUTPUT.put_line ('Return Status: ' || x_return_status);

IF (x_return_status <> fnd_api.g_ret_sts_success)

THEN

DBMS_OUTPUT.put_line ('Error Message :' || x_msg_data);

END IF;

IF (x_return_status = fnd_api.g_ret_sts_success)

THEN

COMMIT;

DBMS_OUTPUT.put_line ('Detailed Qty: ' || x_detailed_qty);

DBMS_OUTPUT.put_line ('Number of rows: ' || x_number_of_rows);

DBMS_OUTPUT.put_line ('Trx temp ID: ' || x_transaction_temp_id);

END IF;

DBMS_OUTPUT.put_line

('==========================================================');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Exception Occured :');

DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);

DBMS_OUTPUT.put_line

('=======================================================');

END xxcona_alloc_move_order;

PROCEDURE xxcona_process_move_order_line (

p_move_order_line_id IN NUMBER,

p_new_quantity_detailed IN NUMBER,

x_return_status OUT VARCHAR2

)

-- Main parameters -- Move order to update, and new quantity

l_new_quantity_detailed NUMBER := p_new_quantity_detailed;

l_mo_line_id NUMBER := p_move_order_line_id;

-- User Information

--l_user_id NUMBER := -1;

--l_user_name VARCHAR2 (20) := 'CONACENT';

--l_resp_id NUMBER := -1;

--l_resp_name VARCHAR2 (40) := 'INVENTORY';

--l_application_id NUMBER := -1;

l_rowcnt NUMBER := 1;

-- Errors

--x_return_status VARCHAR2 (10);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (255);

x_message_list error_handler.error_tbl_type;

-- Move order variables

l_trolin_tbl inv_move_order_pub.trolin_tbl_type;

l_trolin_old_tbl inv_move_order_pub.trolin_tbl_type;

x_trolin_tbl inv_move_order_pub.trolin_tbl_type;

l_mo_line_rec inv_move_order_pub.trolin_rec_type;

BEGIN

l_mo_line_rec := inv_trolin_util.query_row (p_line_id => l_mo_line_id);

l_trolin_tbl (1) := l_mo_line_rec;

l_trolin_old_tbl (1) := l_mo_line_rec;

l_trolin_tbl (1).operation := 'UPDATE'; -- INV_GLOBALS.G_OPR_UPDATE;

l_trolin_tbl (1).quantity_detailed := l_new_quantity_detailed;

inv_move_order_pub.process_move_order_line

(p_api_version_number => 1.0,

p_init_msg_list => fnd_api.g_true,

p_return_values => fnd_api.g_false

-- Convert ids to values

p_commit => fnd_api.g_false

--FND_API.G_FALSE

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

p_trolin_tbl => l_trolin_tbl,

p_trolin_old_tbl => l_trolin_old_tbl,

x_trolin_tbl => x_trolin_tbl

);

DBMS_OUTPUT.put_line ('=========================================');

DBMS_OUTPUT.put_line ('Return Status: ' || x_return_status);

IF (x_return_status <> fnd_api.g_ret_sts_success)

THEN

DBMS_OUTPUT.put_line ('x_msg_data :' || x_msg_data);

DBMS_OUTPUT.put_line ('Error Messages :');

error_handler.get_message_list (x_message_list => x_message_list);

FOR i IN 1 .. x_message_list.COUNT

LOOP

DBMS_OUTPUT.put_line (x_message_list (i).MESSAGE_TEXT);

END LOOP;

ELSE

COMMIT;

END IF;

DBMS_OUTPUT.put_line ('=========================================');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Exception Occured :');

DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);

DBMS_OUTPUT.put_line ('========================================');

END xxcona_process_move_order_line;

PROCEDURE xxcona_transact_mo_line (

p_header_id IN NUMBER,

p_line_id IN NUMBER

)

-- Common Declarations

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

-- API specific declarations

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;

-- WHO columns

--l_user_id NUMBER := -1;

--l_resp_id NUMBER := -1;

--l_application_id NUMBER := -1;

l_row_cnt NUMBER := 1;

--l_user_name VARCHAR2 (30) := 'CONACENT';

--l_resp_name VARCHAR2 (30) := 'INVENTORY';

BEGIN

-- Get the user_id

/*SELECT user_id

INTO l_user_id

FROM fnd_user

WHERE user_name = l_user_name;

-- Get the application_id and responsibility_id

SELECT application_id, responsibility_id

INTO l_application_id, l_resp_id

FROM fnd_responsibility

WHERE responsibility_key = l_resp_name;

fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);

DBMS_OUTPUT.put_line ( 'Initialized applications context: '

|| l_user_id

|| ' '

|| l_resp_id

|| ' '

|| l_application_id

);*/

l_trolin_tbl (1).line_id := p_line_id;

l_trolin_tbl (1).header_id := p_header_id; -- Added

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

IF (x_return_status <> fnd_api.g_ret_sts_success)

THEN

DBMS_OUTPUT.put_line ('Error Message :' || x_msg_data);

ELSE

COMMIT;

END IF;

DBMS_OUTPUT.put_line

('=======================================================');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Exception Occured :');

DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);

DBMS_OUTPUT.put_line

('=======================================================');

END xxcona_transact_mo_line;

PROCEDURE xxcona_main (

p_move_order_no IN VARCHAR2,

p_organization_id IN NUMBER

)

x_return_status VARCHAR2 (2);

x_detailed_qty NUMBER;

CURSOR move_order_cur

SELECT mtrh.request_number, mtrh.organization_id, mtrh.header_id,

mtrl.line_id, mtrh.move_order_type, mtrl.quantity,

mtrl.quantity_delivered,

(mtrl.quantity - mtrl.quantity_delivered) unallocated_qty

FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl

WHERE mtrh.organization_id = mtrl.organization_id

AND mtrh.header_id = mtrl.header_id

AND mtrh.request_number = p_move_order_no

AND mtrh.organization_id = p_organization_id;

BEGIN

-- Get the user_id

SELECT user_id

INTO l_user_id

FROM fnd_user

WHERE user_name = l_user_name;

-- Get the application_id and responsibility_id

SELECT application_id, responsibility_id

INTO l_application_id, l_resp_id

FROM fnd_responsibility

WHERE responsibility_key = l_resp_name;

fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);

DBMS_OUTPUT.put_line ( 'Initialized applications context: '

|| l_user_id

|| ' '

|| l_resp_id

|| ' '

|| l_application_id

);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line

('Exception Occured : Not able to Initialize applications context'

);

DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);

DBMS_OUTPUT.put_line

('=======================================================');

END;

BEGIN

FOR cur_rec IN move_order_cur

LOOP

xxcona_alloc_move_order (cur_rec.line_id,

x_return_status,

x_detailed_qty

);

IF x_return_status = fnd_api.g_ret_sts_success

AND x_detailed_qty > 0

THEN

x_return_status := NULL; --- Reset Status

xxcona_process_move_order_line (cur_rec.line_id,

x_detailed_qty,

x_return_status

);

IF x_return_status = fnd_api.g_ret_sts_success

THEN

xxcona_transact_mo_line (cur_rec.header_id,

cur_rec.line_id);

END IF;

ELSE

DBMS_OUTPUT.put_line

('Exception Occured : Unable to allocate requested quantity.'

);

END IF;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line

('Exception Occured : Unable to process the request --- Transact Move Orders'

);

DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);

DBMS_OUTPUT.put_line

('=======================================================');

END;

END xxcona_main;

END xxcona_transact_move_orders;

2.Syntax to call the API.

BEGIN

xxcona_transact_move_orders.xxcona_main (p_move_order_no => 't21',

p_organization_id => 101

);

END;

Oracle EBs Apps Blog of Mahfuz, OPM and OBIEE Consultant

Friday, 21 November 2025

OM Order Info of respective ORDER_NUMBER in R12