CREATE or REPPLACE PROCEDURE CreateReservation 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_serial_number INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
x_serial_number INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
l_partial_reservation_flag VARCHAR2(2) := FND_API.G_FALSE;
l_force_reservation_flag VARCHAR2(2) := FND_API.G_FALSE;
l_validation_flag VARCHAR2(2) := FND_API.G_TRUE;
l_partial_reservation_exists BOOLEAN := FALSE;
x_quantity_reserved NUMBER := 0;
x_reservation_id NUMBER := 0;
l_primary_reservation_qty NUMBER := 2; -- total qty
l_subinventory_code VARCHAR2(40) := NULL; -- will create a hard reservation
-- Load required data into cursor, the present cursor will load serial numbers for given item
CURSOR c_items IS
SELECT msi.organization_id, msi.inventory_item_id, msi.segment1, msi.primary_uom_code
FROM mtl_system_items_b msi, mtl_parameters mp
WHERE msi.organization_id = mp.organization_id
AND msi.segment1 = 'SU_TEST_STS3'
AND mp.organization_code = 'M1';
-- 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 'S1' AND 'S2'; -- 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 );
-- Initialize Serials to be reserved (if Item is serial controlled)
BEGIN
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;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Item not serial controlled / serials not provided');
END;
-- Initialize Reservations Record
FOR itm IN c_items LOOP
-- Initialize the variables
l_rsv_rec.organization_id := itm.organization_id;
l_rsv_rec.inventory_item_id := itm.inventory_item_id;
l_rsv_rec.requirement_date := sysdate + 3;
l_rsv_rec.demand_source_type_id := INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INV;
l_rsv_rec.supply_source_type_id := INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INV;
l_rsv_rec.demand_source_name := 'INV_RSV_'||itm.segment1;
l_rsv_rec.primary_reservation_quantity := l_primary_reservation_qty;
l_rsv_rec.primary_uom_code := itm.primary_uom_code;
l_rsv_rec.subinventory_code := l_subinventory_code;
l_rsv_rec.demand_source_header_id := NULL ;
l_rsv_rec.demand_source_line_id := NULL ;
l_rsv_rec.reservation_uom_code := NULL ;
l_rsv_rec.reservation_quantity := NULL ;
l_rsv_rec.supply_source_header_id := NULL ;
l_rsv_rec.supply_source_line_id := NULL ;
l_rsv_rec.supply_source_name := NULL ;
l_rsv_rec.supply_source_line_detail := NULL ;
l_rsv_rec.lot_number := NULL ; -- optional 'EXPLOT200' ;
l_rsv_rec.serial_number := NULL ;
l_rsv_rec.ship_ready_flag := NULL ;
l_rsv_rec.attribute15 := NULL ;
l_rsv_rec.attribute14 := NULL ;
l_rsv_rec.attribute13 := NULL ;
l_rsv_rec.attribute12 := NULL ;
l_rsv_rec.attribute11 := NULL ;
l_rsv_rec.attribute10 := NULL ;
l_rsv_rec.attribute9 := NULL ;
l_rsv_rec.attribute8 := NULL ;
l_rsv_rec.attribute7 := NULL ;
l_rsv_rec.attribute6 := NULL ;
l_rsv_rec.attribute5 := NULL ;
l_rsv_rec.attribute4 := NULL ;
l_rsv_rec.attribute3 := NULL ;
l_rsv_rec.attribute2 := NULL ;
l_rsv_rec.attribute1 := NULL ;
l_rsv_rec.attribute_category := NULL ;
l_rsv_rec.lpn_id := NULL ;
l_rsv_rec.pick_slip_number := NULL ;
l_rsv_rec.lot_number_id := NULL ;
l_rsv_rec.locator_id := NULL ;
l_rsv_rec.subinventory_id := NULL ;
l_rsv_rec.revision := NULL ;
l_rsv_rec.external_source_line_id := NULL ;
l_rsv_rec.external_source_code := NULL ;
l_rsv_rec.autodetail_group_id := NULL ;
l_rsv_rec.reservation_uom_id := NULL ;
l_rsv_rec.primary_uom_id := NULL ;
l_rsv_rec.demand_source_delivery := NULL ;
-- call API to create reservation
DBMS_OUTPUT.PUT_LINE('=======================================================');
DBMS_OUTPUT.PUT_LINE('Calling INV_RESERVATION_PUB.Create_Reservation API');
INV_RESERVATION_PUB.Create_Reservation(
P_API_VERSION_NUMBER => l_api_version
, P_INIT_MSG_LST => l_init_msg_list
, P_RSV_REC => l_rsv_rec
, P_SERIAL_NUMBER => l_serial_number
, P_PARTIAL_RESERVATION_FLAG => l_partial_reservation_flag
, P_FORCE_RESERVATION_FLAG => l_force_reservation_flag
, P_PARTIAL_RSV_EXISTS => l_partial_reservation_exists
, P_VALIDATION_FLAG => l_validation_flag
, X_SERIAL_NUMBER => x_serial_number
, X_RETURN_STATUS => x_return_status
, X_MSG_COUNT => x_msg_count
, X_MSG_DATA => x_msg_data
, X_QUANTITY_RESERVED => x_quantity_reserved
, X_RESERVATION_ID => x_reservation_id);
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_reservation_id||' Quantity Reserved:'||x_quantity_reserved);
FOR srl IN 1..x_serial_number.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Serial Reserved:'||x_serial_number(srl).serial_number);
END LOOP;
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 CreateReservation;