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 !