CREATE or REPPLACE PROCEDURE RelieveReservation
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);
x_error_code NUMBER := 0;
-- 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_serial_number INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
l_validation_flag VARCHAR2(2) := FND_API.G_TRUE;
x_mtl_reservation_tbl INV_RESERVATION_GLOBAL.MTL_RESERVATION_TBL_TYPE;
x_mtl_reservation_tbl_count NUMBER := 0;
x_primary_relieved_qty NUMBER := 0;
x_primary_remain_qty NUMBER := 0;
l_primary_reservation_qty NUMBER := 10; -- total qty
l_primary_relieved_qty NUMBER := 2;
l_subinventory_code VARCHAR2(40) := NULL; --'Stores'; -- will create a hard reservation
-- 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;
-- Load required serial numbers to be 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 msi.segment1 = 'SU_TEST_STS3'
AND mp.organization_code = 'M1'
AND msi.serial_number_control_code not in (1, 6) -- item is not serial controlled / controlled at sales order issue
AND msn.serial_number BETWEEN '' AND ''; -- can leave this NULL if item is not serial controlled
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.organization_id := ir.organization_id;
l_rsv_rec.inventory_item_id := ir.inventory_item_id;
EXIT;
END LOOP;
-- Get all reservations that exist for this item
-- call API to get all the reservations for this item
DBMS_OUTPUT.PUT_LINE('=======================================================');
DBMS_OUTPUT.PUT_LINE('Calling INV_RESERVATION_PUB.Query_Reservation');
INV_RESERVATION_PUB.QUERY_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_QUERY_INPUT => l_rsv_rec
, P_LOCK_RECORDS => FND_API.G_FALSE
, P_SORT_BY_REQ_DATE => INV_RESERVATION_GLOBAL.G_QUERY_NO_SORT
, P_CANCEL_ORDER_MODE => INV_RESERVATION_GLOBAL.G_CANCEL_ORDER_NO
, X_MTL_RESERVATION_TBL => x_mtl_reservation_tbl
, X_MTL_RESERVATION_TBL_COUNT => x_mtl_reservation_tbl_count
, X_ERROR_CODE => x_error_code
);
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
FOR i IN 1..x_mtl_reservation_tbl_count LOOP
DBMS_OUTPUT.PUT_LINE('=======================================================');
dbms_output.put_line('reservation_id : '|| TO_CHAR(x_mtl_reservation_tbl(i).reservation_id));
dbms_output.put_line('requirement_date : '|| TO_CHAR(x_mtl_reservation_tbl(i).requirement_date, 'YYYY/MM/DD'));
dbms_output.put_line('organization_id : '|| TO_CHAR(x_mtl_reservation_tbl(i).organization_id));
dbms_output.put_line('inventory_item_id : '|| TO_CHAR(x_mtl_reservation_tbl(i).inventory_item_id));
dbms_output.put_line('demand_source_type_id : '|| TO_CHAR(x_mtl_reservation_tbl(i).demand_source_type_id));
dbms_output.put_line('demand_source_name : '|| x_mtl_reservation_tbl(i).demand_source_name);
dbms_output.put_line('demand_source_header_id : '|| TO_CHAR(x_mtl_reservation_tbl(i).demand_source_header_id));
dbms_output.put_line('demand_source_line_id : '|| TO_CHAR(x_mtl_reservation_tbl(i).demand_source_line_id));
dbms_output.put_line('demand_source_line_detail : '|| TO_CHAR(x_mtl_reservation_tbl(i).demand_source_line_detail));
dbms_output.put_line('primary_uom_code : '|| x_mtl_reservation_tbl(i).primary_uom_code);
dbms_output.put_line('reservation_uom_code : '|| x_mtl_reservation_tbl(i).reservation_uom_code);
dbms_output.put_line('reservation_quantity : '|| TO_CHAR(x_mtl_reservation_tbl(i).reservation_quantity));
dbms_output.put_line('primary_reservation_quantity: '|| TO_CHAR(x_mtl_reservation_tbl(i).primary_reservation_quantity));
dbms_output.put_line('detailed_quantity : '|| TO_CHAR(x_mtl_reservation_tbl(i).detailed_quantity));
dbms_output.put_line('supply_source_type_id : '|| TO_CHAR(x_mtl_reservation_tbl(i).supply_source_type_id));
dbms_output.put_line('supply_source_header_id : '|| TO_CHAR(x_mtl_reservation_tbl(i).supply_source_header_id));
dbms_output.put_line('supply_source_line_id : '|| TO_CHAR(x_mtl_reservation_tbl(i).supply_source_line_id));
dbms_output.put_line('supply_source_name : '|| (x_mtl_reservation_tbl(i).supply_source_name));
dbms_output.put_line('supply_source_line_detail : '|| TO_CHAR(x_mtl_reservation_tbl(i).supply_source_line_detail));
dbms_output.put_line('subinventory_code : '|| x_mtl_reservation_tbl(i).subinventory_code);
dbms_output.put_line('ship_ready_flag : '|| TO_CHAR(x_mtl_reservation_tbl(i).ship_ready_flag));
dbms_output.put_line('staged_flag : '|| x_mtl_reservation_tbl(i).staged_flag);
DBMS_OUTPUT.PUT_LINE('=======================================================');
END LOOP;
END IF;
-- call API to relieve all the queried reservations for this item
DBMS_OUTPUT.PUT_LINE('=======================================================');
DBMS_OUTPUT.PUT_LINE('Calling INV_RESERVATION_PUB.Relieve_Reservation');
FOR i IN 1..x_mtl_reservation_tbl_count LOOP
BEGIN
-- Initialize Serials to be relieved
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_row_cnt := l_row_cnt + 1;
END LOOP;
IF (l_serial_number.COUNT > 0) THEN
l_primary_relieved_qty := l_serial_number.COUNT;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Item not serial controlled / serials not provided');
END;
-- Call the API to relieve reservations for the provided serial numbers
INV_RESERVATION_PUB.RELIEVE_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_RSV_REC => x_mtl_reservation_tbl(i)
, P_PRIMARY_RELIEVED_QUANTITY => l_primary_relieved_qty
, P_RELIEVE_ALL => FND_API.G_FALSE -- Relieve ALL -> set to false, relieve partially
, P_ORIGINAL_SERIAL_NUMBER => l_serial_number
, P_VALIDATION_FLAG => l_validation_flag
, X_PRIMARY_RELIEVED_QUANTITY => x_primary_relieved_qty
, X_PRIMARY_REMAIN_QUANTITY => x_primary_remain_qty
);
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:'||x_mtl_reservation_tbl(i).reservation_id);
DBMS_OUTPUT.PUT_LINE('Primary Relieved Quantity: '||x_primary_relieved_qty);
DBMS_OUTPUT.PUT_LINE('Primary Remain Quantity: '||x_primary_remain_qty);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception Occured :');
DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('=======================================================');
END RelieveReservation;