Creating reservation for inventory items is one of most common actions regarding business requirements in Oracle E-Business Suite platform. In this example, we transfer existing reservation quantity between different transaction source types/organizations/uom’s in R12 version of EBS.

For each three item-organization-transaction source type , there should be only one unique row in INV.MTL_RESERVATIONS table. So following loop is for a better code review and look. (using cursor depends on you).  We use standart Oracle Inventory API’s and table/record types in this procedure. Here is the PL/SQL script;

procedure transfer_reservation  (  p_rsv_rec in  inv_reservation_global.mtl_reservation_rec_type,
								   x_msg_count out nocopy number,
								   x_msg_data  out nocopy varchar2,
								   x_return_status out varchar2,
								   x_reservation_id out number ) is

x_serial_number   inv_reservation_global.serial_number_tbl_type;
v_rsv_from_rec    inv_reservation_global.mtl_reservation_rec_type ;
v_rsv_to_rec      inv_reservation_global.mtl_reservation_rec_type ;
ln_total_res_qty number:=0 ;

cursor c_res(p_inv_item_id number , p_organization_id number , p_uom varchar2)  is
		select	re.reservation_id,
		        /*you can use a your conversion functions if your source 
				and destination for different UOM's*/
				xxln_inv_pkg.UOM_CONVERSION(p_inventory_item_id =>p_inv_item_id,
					  			       pcur_qty =>re.reservation_quantity,
								       pcur_uom =>re.reservation_uom_code,
								       pnew_uom =>p_uom) as reservation_quantity,
				re.reservation_uom_code,
				inventory_item_id,organization_id
		   from mtl_reservations re
		  where organization_id = p_organization_id
			and inventory_item_id = p_inv_item_id
			and demand_source_type_id = (select transaction_source_type_id
										   from mtl_txn_source_types														
									      where transaction_source_type_name = 'SOME-TRANSACTION-SOURCE')
	   order by re.creation_date;

begin
		v_rsv_to_rec.demand_source_type_id  := p_rsv_rec.demand_source_type_id; 
		v_rsv_to_rec.demand_source_name     := to_char(sysdate);

		for crec in c_res(p_rsv_rec.inventory_item_id,p_rsv_rec.organization_id, p_rsv_rec.reservation_uom_code)  loop

			v_rsv_from_rec.reservation_id  := crec.reservation_id;
			
			/*Here I use each remaining quantity from source until it equals to demand amount.
			 After process , we could have still remaining amount in our quota*/
			if( p_rsv_rec.reservation_quantity-ln_total_res_qty<crec.reservation_quantity ) then

				v_rsv_to_rec.reservation_quantity :=p_rsv_rec.reservation_quantity-ln_total_res_qty;
				v_rsv_to_rec.reservation_uom_code :=p_rsv_rec.reservation_uom_code;
				ln_total_res_qty:=ln_total_res_qty+v_rsv_to_rec.reservation_quantity ;

			else

				v_rsv_to_rec.reservation_quantity := crec.reservation_quantity;
				v_rsv_to_rec.reservation_uom_code := p_rsv_rec.reservation_uom_code;
				ln_total_res_qty:=ln_total_res_qty+v_rsv_to_rec.reservation_quantity;

			end if;

			inv_reservation_pub.transfer_reservation(p_api_version_number        => 1.0,
													 p_init_msg_lst              => FND_API.G_TRUE,
													 x_return_status             => x_return_status,
													 x_msg_count                 => x_msg_count,
													 x_msg_data                  => x_msg_data,
												   --p_is_transfer_supply       IN  VARCHAR2 DEFAULT fnd_api.g_true
													 p_original_rsv_rec          =>  v_rsv_from_rec,
													 p_to_rsv_rec                =>  v_rsv_to_rec ,
													 p_original_serial_number    =>  x_serial_number ,
													 p_to_serial_number          =>  x_serial_number ,
												   --p_validation_flag          IN  VARCHAR2 DEFAULT fnd_api.g_true
		   										   --p_over_reservation_flag    IN  NUMBER DEFAULT 0
													 x_to_reservation_id         =>x_reservation_id);


			if (x_return_status<>'S') then

				return  ;

			end if ;

		end loop ;

end ;

That’s it !