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

Move Orders Allocate and Transact APIs in R12

Move Orders Allocate and Transact APIs in R12

No comments:

Post a Comment

OM Order Info of respective ORDER_NUMBER in R12

Report Abuse

Labels