Monday, September 23, 2013

Script to create reservation For a Item in the Subinventory using API

DECLARE
   l_rsv_rec                                         inv_reservation_global.mtl_reservation_rec_type;
   l_serial_number                                   inv_reservation_global.serial_number_tbl_type;
   l_partial_reservation_flag                        VARCHAR2 (1) := fnd_api.g_false;
   l_force_reservation_flag                          VARCHAR2 (1) := fnd_api.g_false;
   l_validation_flag                                 VARCHAR2 (1) := fnd_api.g_true;
   l_partial_reservation_exists                      BOOLEAN := FALSE;
   l_primary_reservation_qty                         NUMBER;
   l_subinventory_code                               VARCHAR2 (40);
   l_error_message                                   VARCHAR2 (2000);
   l_msg_index_out                                   NUMBER;
   x_serial_number                                   inv_reservation_global.serial_number_tbl_type;
   x_quantity_reserved                               NUMBER := 0;
   x_reservation_id                                  NUMBER := 0;
   x_return_status                                   VARCHAR2 (2);
   x_msg_count                                       NUMBER := 0;
   x_msg_data                                        VARCHAR2 (250);
BEGIN
   l_rsv_rec.organization_id                                := '<organization_id>';
   l_rsv_rec.inventory_item_id                              := '<inventory_item_id>';
   l_rsv_rec.requirement_date                               := SYSDATE + 1;
   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                             := '<segment1>';
   l_rsv_rec.primary_reservation_quantity                   := '<primary_reservation_qty>';
   l_rsv_rec.primary_uom_code                               := '<primary_uom_code>';
   l_rsv_rec.subinventory_code                              := '<subinventory_code>';
   -- call API to create reservation
   inv_reservation_pub.create_reservation (p_api_version_number          => 1.0
                                         , p_init_msg_lst                => fnd_api.g_true
                                         , 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
                                          );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get (p_msg_index                   => i
                             , p_encoded                     => fnd_api.g_false
                             , p_data                        => x_msg_data
                             , p_msg_index_out               => l_msg_index_out
                              );

         IF l_error_message IS NULL
         THEN
            l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line ('*****************************************');
      ROLLBACK;
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Reservation Created Successfully ');
      DBMS_OUTPUT.put_line ('Reservation ID :' || x_reservation_id);
      DBMS_OUTPUT.put_line (' Quantity Reserved:' || x_quantity_reserved);
      DBMS_OUTPUT.put_line ('Serial Reserved:' || x_serial_number);
      DBMS_OUTPUT.put_line ('*****************************************');
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Unexpected Error ' || SUBSTR (SQLERRM, 1, 250));
END;

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.