Script to update the locator in MTL_RESERVATIONS

CREATE OR REPLACE PROCEDURE CLC_MTL_RESERVATIONS_PRG (
   errbuf    OUT VARCHAR2,
   retcode   OUT NUMBER,
  p_org_code         VARCHAR2
)
AS
l_rsv_old inv_reservation_global.mtl_reservation_rec_type;
l_rsv_new inv_reservation_global.mtl_reservation_rec_type;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);
l_rsv_id NUMBER;
l_dummy_sn inv_reservation_global.serial_number_tbl_type;
l_status VARCHAR2 (1);
l_quantity_reserved NUMBER;
l_cnt number :=0;
l_err number :=0;

cursor c_resv (l_org_code number)is
select ooha.order_number,oola.header_id, oola.line_id, mr.RESERVATION_ID,msi.segment1--,wdd.RELEASED_STATUS
from MTL_RESERVATIONS mr,
oe_order_lines_all oola,
oe_order_headers_all ooha,
mtl_system_items_b msi,
wsh_delivery_details wdd
where
ooha.header_id =oola.header_id
and ooha.org_id =oola.org_id
and oola.line_id = mr.DEMAND_SOURCE_LINE_ID
and mr.INVENTORY_ITEM_ID =msi.INVENTORY_ITEM_ID
and mr.ORGANIZATION_ID = msi.ORGANIZATION_ID
and wdd.SOURCE_HEADER_ID = ooha.header_id
and wdd.SOURCE_LINE_ID = oola.line_id
and mr.DEMAND_SOURCE_TYPE_ID=2
and mr.ORGANIZATION_ID =l_org_code
--and mr.INVENTORY_ITEM_ID=32299
and (wdd.RELEASED_STATUS ='R'
or not exists (select 'A' from wsh_delivery_assignments wda, wsh_new_deliveries wnd
where wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id));

/*select oola.header_id, oola.line_id, mr.RESERVATION_ID
from MTL_RESERVATIONS mr,
oe_order_lines_all oola
where
oola.line_id = mr.DEMAND_SOURCE_LINE_ID
and mr.DEMAND_SOURCE_TYPE_ID=2
and ORGANIZATION_ID =l_org_code
--and mr.INVENTORY_ITEM_ID=32299
and not exists (select 'A' from wsh_delivery_details wdd
where  wdd.SOURCE_HEADER_ID =oola.HEADER_ID and
 wdd.SOURCE_LINE_ID=oola.line_id);*/

BEGIN

--fnd_global.apps_initialize (10688,50606,401);

   FND_FILE.PUT_LINE (FND_FILE.LOG, 'Initializing Apps');
   FND_GLOBAL.Apps_Initialize (FND_GLOBAL.USER_ID,
                               FND_GLOBAL.RESP_ID,
                               FND_GLOBAL.RESP_APPL_ID);
 --- Could be "located" by order_header_id, order_line_id, item and warehouse - in case Several reservations That Could be updated

   fnd_file.put_line (fnd_file.LOG, 'p_org_code-' ||p_org_code);
l_cnt :=0;
l_err :=0;
FOR l_resv IN c_resv (p_org_code)
LOOP

l_rsv_old.reservation_id := l_resv.reservation_id;

-- Specify the new values
l_rsv_new.reservation_id := l_resv.reservation_id;
--l_rsv_new.primary_reservation_quantity: = 10;
--l_rsv_new.requirement_date: = SYSDATE + 2;
l_rsv_new.LOCATOR_ID := null;
     
inv_reservation_pub.update_reservation (
p_api_version_number => 1.0
, P_init_msg_lst => fnd_api.g_true
, X_return_status => l_status
, X_msg_count => l_msg_count
, X_msg_data => l_msg_data
, P_original_rsv_rec => l_rsv_old
, P_to_rsv_rec => l_rsv_new
, P_original_serial_number => l_dummy_sn -- no serial contorl
, p_to_serial_number  => L_dummy_sn -- no serial control
, P_validation_flag => fnd_api.g_true
, P_check_availability => fnd_api.g_false
, P_over_reservation_flag => 0
);
IF  l_status = fnd_api.g_ret_sts_success THEN
--dbms_output.put_line ( 'reservation updated');
   fnd_file.put_line (fnd_file.LOG, 'reservation updated for Order Number' || l_resv.order_number ||' reservation_id:' ||l_resv.RESERVATION_ID ||' Item:' ||l_resv.segment1 );
   l_cnt :=l_cnt+1;
ELSE
IF l_msg_count>= 1 THEN
FOR  I IN 1..l_msg_count
LOOP
--dbms_output.put_line (  SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255) );
--fnd_file.put_line (fnd_file.log, I || || SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255) '.');
fnd_file.put_line (fnd_file.LOG, 'reservation error out for Order Number' || l_resv.order_number ||' reservation_id:' ||l_resv.RESERVATION_ID ||' Item:' ||l_resv.segment1 ||'-'||SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255));
END LOOP;

   l_err :=l_err+1;
END IF;
END IF;
COMMIT;

END LOOP;
   fnd_file.put_line (fnd_file.LOG, 'total reservation updated succesfully:' ||l_cnt );
 --  fnd_file.put_line (fnd_file.LOG, 'total reservation updated succesfully:' ||l_err );

EXCEPTION
   WHEN OTHERS
   THEN
      FND_FILE.PUT_LINE (FND_FILE.LOG, ' OTHER exception:- '||sqlerrm);
END CLC_MTL_RESERVATIONS_PRG;
/