CREATE or REPPLACE PROCEDURE UpdateReservation
AS
-- Common Declarations
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := FND_API.G_TRUE;
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) := 'MFG';
l_resp_name VARCHAR2(50) := 'Manufacturing and Distribution Manager';
-- API specific declarations
l_rsv_rec INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
l_new_rsv_rec INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
l_serial_number INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
l_new_serial_number INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
l_validation_flag VARCHAR2(2) := FND_API.G_TRUE;
-- Load required serial numbers that are reserved
CURSOR c_serials IS
SELECT msn.inventory_item_id , msn.serial_number
FROM mtl_system_items_b msi, mtl_serial_numbers msn, mtl_parameters mp
WHERE msi.organization_id = mp.organization_id
AND msi.organization_id = msn.current_organization_id
AND msi.inventory_item_id = msn.inventory_item_id
AND msn.group_mark_id IS NOT NULL
AND msi.serial_number_control_code not in (1, 6) -- item is not serial controlled / controlled at sales order issue
AND msi.segment1 = 'SU_TEST_STS3'
AND mp.organization_code = 'M1'
AND msn.serial_number BETWEEN '' AND ''
ORDER BY msn.serial_number DESC;
-- Load reservation for this item
CURSOR c_item_reservations IS
SELECT msi.organization_id, msi.inventory_item_id, res.reservation_id, res.reservation_quantity, res.demand_source_name
FROM mtl_system_items_b msi, mtl_parameters mp, mtl_reservations res
WHERE msi.segment1 = 'SU_TEST_STS3'
AND mp.organization_code = 'M1'
AND msi.organization_id = mp.organization_id
AND res.organization_id = msi.organization_id
AND res.inventory_item_id = msi.inventory_item_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_vl
WHERE responsibility_name = 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 );
-- Get the first row
FOR ir IN c_item_reservations LOOP
l_rsv_rec.reservation_id := ir.reservation_id;
l_rsv_rec.demand_source_name := ir.demand_source_name;
l_rsv_rec.reservation_quantity := ir.reservation_quantity;
-- Update Demand Source Name, reservation qty for reservations that exist for this item
l_new_rsv_rec.reservation_id := ir.reservation_id;
l_new_rsv_rec.demand_source_name := ir.demand_source_name; --||'_0723';
l_new_rsv_rec.reservation_quantity := 10; -- new reservation quantity
BEGIN
-- Initialize Serials to be updated / reserved
FOR ser IN c_serials LOOP
l_serial_number(l_row_cnt).inventory_item_id := ser.inventory_item_id;
l_serial_number(l_row_cnt).serial_number := ser.serial_number;
l_new_serial_number(l_row_cnt).inventory_item_id := ser.inventory_item_id;
l_new_serial_number(l_row_cnt).serial_number := ser.serial_number + 10;
l_row_cnt := l_row_cnt + 1;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Item not serial controlled / serials not provided');
END;
-- call API to update all the reservations for this item
DBMS_OUTPUT.PUT_LINE('=======================================================');
DBMS_OUTPUT.PUT_LINE('Calling INV_RESERVATION_PUB.Update_Reservation');
INV_RESERVATION_PUB.UPDATE_RESERVATION(
P_API_VERSION_NUMBER => l_api_version
, P_INIT_MSG_LST => l_init_msg_list
, X_RETURN_STATUS => x_return_status
, X_MSG_COUNT => x_msg_count
, X_MSG_DATA => x_msg_data
, P_ORIGINAL_RSV_REC => l_rsv_rec
, P_TO_RSV_REC => l_new_rsv_rec
, P_ORIGINAL_SERIAL_NUMBER => l_serial_number
, P_TO_SERIAL_NUMBER => l_new_serial_number
, P_VALIDATION_FLAG => l_validation_flag
, P_CHECK_AVAILABILITY => 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
DBMS_OUTPUT.PUT_LINE('Reservation ID :'||l_new_rsv_rec.reservation_id);
DBMS_OUTPUT.PUT_LINE('Demand Source Name (old) :'||l_rsv_rec.demand_source_name);
DBMS_OUTPUT.PUT_LINE('Demand Source Name (new) :'||l_new_rsv_rec.demand_source_name);
DBMS_OUTPUT.PUT_LINE('Reservation Qty (old) :'||l_rsv_rec.reservation_quantity);
DBMS_OUTPUT.PUT_LINE('Reservation Qty (new) :'||l_new_rsv_rec.reservation_quantity);
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 UpdateReservation;