Create Reservation using API

Hi Guys,

Please find below Tested code to create reservation using standard  API.



PROCEDURE call_reserve (p_qty               IN NUMBER,
                           p_item_id           IN NUMBER,
                           p_organziation_id   IN NUMBER,
                           p_lot_number        IN VARCHAR2,
                           p_uom_code          IN VARCHAR2) --, x_error OUT VARCHAR2)
   IS
      ln_user_id                NUMBER := FND_GLOBAL.User_ID;
      ld_sysdate                DATE := SYSDATE;
      ln_login_id               NUMBER := FND_GLOBAL.Conc_Login_ID;
      lv_return_status          VARCHAR2 (1) := FND_API.G_RET_STS_SUCCESS;
      ln_msg_count              NUMBER;
      lv_msg_data               VARCHAR2 (3000);
      ln_group_id               NUMBER := 9999;
      l_msg_index_out           NUMBER;
      l_error_message           VARCHAR2 (1000);
      l_serial_number           inv_reservation_global.serial_number_tbl_type;
      lr_orig_rsv_rec           inv_reservation_global.mtl_reservation_rec_type;
      l_rsv_rec                 inv_reservation_global.mtl_reservation_rec_type;
      lr_orig_serial_number     inv_reservation_global.serial_number_tbl_type;
      x_serial_number           INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
      ln_msg_index              NUMBER;
      l_init_msg_list           VARCHAR2 (2) := FND_API.G_TRUE;
      gv_res_source_typ_name    VARCHAR2(100):='Custom Source Name';
      x_quantity_reserved       NUMBER := 0;
      x_reservation_id          NUMBER := 0;
      ln_aft_adj_qty            NUMBER;

      ln_reserve_qty            NUMBER;
      lv_subinv_code            VARCHAR2 (30);
      ln_location_id            NUMBER;
      x_error                   VARCHAR2 (255);
      ln_avlbl_to_reserve_qty   NUMBER;


      ln_organization_id        NUMBER;
      ln_inventory_item_id      NUMBER;
      lv_uom_code               VARCHAR2 (30);
      lv_lot_number             VARCHAR2 (100);

      ln_already_reserved_qty NUMBER;
   BEGIN
      ln_reserve_qty := p_qty;
      lv_subinv_code := gv_reservation_subinv;
      ln_organization_id := p_organziation_id;
      ln_location_id := find_location (ln_organization_id, lv_subinv_code);----------------Function to Fetch Locator ID
      ln_inventory_item_id := p_item_id;
      lv_uom_code := p_uom_code;
      lv_lot_number := p_lot_number;



http://oracleappspractices.blogspot.com/2016/12/get-on-hand-available-to-transact.html

      ln_avlbl_to_reserve_qty :=
         (XXX_LOT_RESERVATION_PKG.get_available_qty (------------------------Separate Function to get Available Quantity By Using inv_quantity_tree_pub.query_quantities
             ln_organization_id,
             ln_inventory_item_id,
             gv_reservation_subinv,
             find_location (ln_organization_id, gv_reservation_subinv),
             lv_lot_number,
             0));

    BEGIN
       
          select NVL(reservation_quantity,0)
      INTO ln_already_reserved_qty
      from mtl_reservations
          where demand_source_name=gv_res_source_typ_name
          and inventory_item_id=ln_inventory_item_id
          and organization_id=ln_organization_id
          and lot_number=lv_lot_number;


        EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    WHEN OTHERS THEN
     XXX_log_messages_pkg.display_msg (' Error while calculating Already reserved Quantity inside call_reserve procedure  ' || SQLERRM);
   
    END;

        ln_reserve_qty := NVL(ln_reserve_qty,0) - NVL(ln_already_reserved_qty,0);

      IF ln_avlbl_to_reserve_qty >= ln_reserve_qty
      THEN
         l_rsv_rec.organization_id := ln_organization_id;
         l_rsv_rec.inventory_item_id := ln_inventory_item_id;
         l_rsv_rec.requirement_date := SYSDATE;
         l_rsv_rec.demand_source_type_id := gv_reservation_source_id;
         l_rsv_rec.supply_source_type_id := gv_reservation_source_id;
         l_rsv_rec.demand_source_name := gv_res_source_typ_name;
         l_rsv_rec.primary_reservation_quantity := ln_reserve_qty; --rec_process_data.adjustment_qty;
         l_rsv_rec.primary_uom_code := lv_uom_code;
         l_rsv_rec.subinventory_code := lv_subinv_code; --rec_process_data.subinventory_code;
         l_rsv_rec.demand_source_header_id := NULL;
         l_rsv_rec.demand_source_line_id := NULL;
         l_rsv_rec.reservation_uom_code := lv_uom_code;
         l_rsv_rec.reservation_quantity := ln_reserve_qty;
         --l_rsv_rec.secondary_reservation_quantity := 100;
         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 := lv_lot_number;
         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 := ln_location_id; --rec_process_data.location_id ;---inventory_location_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;
         l_rsv_rec.crossdock_flag := 'N';
         l_rsv_rec.secondary_uom_code := NULL;
         l_rsv_rec.detailed_quantity := NULL; --lrec_batch_details.shipped_quantity;
         l_rsv_rec.secondary_detailed_quantity := NULL; --ln_shipped_quantity;--lrec_batch_details.shipped_quantity;
         ln_msg_count := NULL;
         lv_msg_data := NULL;
         lv_return_status := NULL;


         INV_RESERVATION_PUB.Create_Reservation (
            P_API_VERSION_NUMBER         => 1.0,
            P_INIT_MSG_LST               => l_init_msg_list,
            P_RSV_REC                    => l_rsv_rec,
            P_SERIAL_NUMBER              => l_serial_number,
            P_PARTIAL_RESERVATION_FLAG   => FND_API.G_FALSE,
            P_FORCE_RESERVATION_FLAG     => FND_API.G_FALSE,
            P_PARTIAL_RSV_EXISTS         => FALSE,
            P_VALIDATION_FLAG            => FND_API.G_TRUE,
            X_SERIAL_NUMBER              => x_serial_number,
            X_RETURN_STATUS              => lv_return_status,
            X_MSG_COUNT                  => ln_msg_count,
            X_MSG_DATA                   => lv_msg_data,
            X_QUANTITY_RESERVED          => x_quantity_reserved,
            X_RESERVATION_ID             => x_reservation_id);


         IF lv_return_status = fnd_api.g_ret_sts_success
         THEN
            XXX_log_messages_pkg.display_msg (
               'Quantity succesfully reserved by the API');

            UPDATE XXX_NVCFG_ORCLGEN_INV_SYNC
               SET PROCESSING_STATUS =
                      PROCESSING_STATUS || '  ' ||   'Reservation Hold Successfully Done For Quantity : '
                      || ln_reserve_qty,
                   ACTION_PERFORMED =
                      ACTION_PERFORMED || '   ' || gv_reservation_action
             WHERE LOT_NUMBER = lv_LOT_NUMBER AND Request_id = gn_request_id;
         ELSE
            IF ln_msg_count = 0
            THEN
               x_error := 'Error by Reserve Order API ' || lv_msg_data;
               XXX_log_messages_pkg.display_msg (x_error);

               UPDATE XXX_NVCFG_ORCLGEN_INV_SYNC
                  SET PROCESSING_STATUS = PROCESSING_STATUS || '  ' || x_error
                WHERE     LOT_NUMBER = lv_LOT_NUMBER
                      AND Request_id = gn_request_id;
            ELSE
               FOR l_index IN 1 .. ln_msg_count
               LOOP
                  fnd_msg_pub.get (p_msg_index       => l_index,
                                   p_data            => lv_msg_data,
                                   p_encoded         => 'F',
                                   p_msg_index_out   => l_msg_index_out);
                  x_error := 'Error by Reserve Order API ' || lv_msg_data;
                  XXX_log_messages_pkg.display_msg (x_error);
               END LOOP;
            END IF;
         END IF;
      ELSE
         UPDATE XXX_NVCFG_ORCLGEN_INV_SYNC
            SET PROCESSING_STATUS =
                   PROCESSING_STATUS ||  '  ' ||  'Reserve Order API fails due to allocation to reserve qty is less then the adjustmend qty.'
          WHERE     lot_number = lv_lot_number
                AND TRUNC (PROCESSING_DATE) = TRUNC (SYSDATE)
                AND ROW_PROCESSED_FLAG = 'N';
      END IF;
   --END LOOP;

   EXCEPTION
      WHEN OTHERS
      THEN
         x_error :=
            'Exception in call_reserve_order ' || SQLCODE || '-' || SQLERRM;
         XXX_log_messages_pkg.display_msg (x_error);
   END;