create or replace procedure XX_ALOCATE_TRANSACT_MOVE_ORDER (P_REQUEST_NUMBER VARCHAR2 ,P_ORGANIZATION_ID NUMBER,P_TRANSACTION_DATE varchar2)
as
----------------- ALLOCATE MOVE ORDER API REQUIREMENTS -------------------------
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 ;
----------------- TRANSACT MOVE ORDER API REQUIREMENTS -------------------------
a_l_api_version NUMBER := 1.0;
a_l_init_msg_list VARCHAR2 (2) := fnd_api.g_true;
a_l_commit VARCHAR2 (2) := fnd_api.g_false;
a_x_return_status VARCHAR2 (2);
a_x_msg_count NUMBER := 0;
a_x_msg_data VARCHAR2 (255);
a_l_move_order_type NUMBER := 3;
a_l_transaction_mode NUMBER := 1;
a_l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
a_l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
a_x_mmtt_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
a_x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
a_l_transaction_date DATE := SYSDATE;
a_l_user_id NUMBER;
a_l_resp_id NUMBER;
a_l_appl_id NUMBER;
v_transaction_date date;
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 = P_REQUEST_NUMBER OR P_REQUEST_NUMBER IS NULL )--'8007'
AND (mtrh.organization_id = P_ORGANIZATION_ID OR P_ORGANIZATION_ID IS NULL ); -- 755;
BEGIN
FND_GLOBAL.APPS_INITIALIZE(1130, 50632, 401);
FOR i IN c_mo_details
LOOP
mo_global.set_policy_context ('S', i.org_id);
inv_globals.set_org_id (NVL(i.organization_id,P_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 ('ALLOCATE API SUCCESSFUL WITH - Trx temp ID: ');
DBMS_OUTPUT.put_line (x_transaction_temp_id);
END IF;
DBMS_OUTPUT.put_line('==========================================================');
IF x_transaction_temp_id IS NOT NULL OR x_transaction_temp_id <> 0 THEN
mo_global.set_policy_context ('S', i.org_id);
inv_globals.set_org_id (i.organization_id);
mo_global.init ('INV');
a_l_trolin_tbl (1).line_id := i.line_id;
select to_date (P_TRANSACTION_DATE) --('31-AUG-2010')
into v_transaction_date
from dual;
FND_GLOBAL.APPS_INITIALIZE(1130, 50632, 401);
inv_pick_wave_pick_confirm_pub.pick_confirm
(p_api_version_number => a_l_api_version,
p_init_msg_list => a_l_init_msg_list,
p_commit => a_l_commit,
x_return_status => a_x_return_status,
x_msg_count => a_x_msg_count,
x_msg_data => a_x_msg_data,
p_move_order_type => i.move_order_type,
p_transaction_mode => a_l_transaction_mode,
p_trolin_tbl => a_l_trolin_tbl,
p_mold_tbl => a_l_mold_tbl,
x_mmtt_tbl => a_x_mmtt_tbl,
x_trolin_tbl => a_x_trolin_tbl,
p_transaction_date => v_transaction_date -- l_transaction_date
);
DBMS_OUTPUT.put_line('=======================================================');
DBMS_OUTPUT.put_line ('Return Status - '||a_x_return_status||' '||sqlerrm);
DBMS_OUTPUT.put_line ('Return Message - '||a_x_msg_data);
DBMS_OUTPUT.put_line (a_x_msg_count);
IF (a_x_return_status <> fnd_api.g_ret_sts_success) THEN
DBMS_OUTPUT.put_line (a_x_msg_data);
END IF;
IF (a_x_return_status = fnd_api.g_ret_sts_success) THEN
DBMS_OUTPUT.put_line ('TRANSACT API SUCCESSFULLY PROCESSED');
END IF;
DBMS_OUTPUT.put_line('=======================================================');
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('OUTER Exception Occured :');
DBMS_OUTPUT.put_line (SQLCODE||':'||SQLERRM);
DBMS_OUTPUT.put_line ('=======================================================');
END;
END;
begin
XX_ALOCATE_TRANSACT_MOVE_ORDER('',763,'31-AUG-2010');
end;
