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;

API to Create/Delete the Categories.

DECLARE
   l_category_rec                                    inv_item_category_pub.category_rec_type;
   x_return_status                                   VARCHAR2 (1);
   x_error_code                                      NUMBER;
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (250);
   l_out_category_id                                 NUMBER;
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2 (2000);
BEGIN
   l_category_rec.segment1                                  := 'Test';
   l_category_rec.segment2                                  := 'Test';
   l_category_rec.segment3                                  := 'Test';
   l_category_rec.segment4                                  := 'Test';
   l_category_rec.segment5                                  := 'Test';
   l_category_rec.segment6                                  := 'Test1';
   fnd_global.apps_initialize (10089, 20634, 401);
   COMMIT;

   SELECT f.id_flex_num
     INTO l_category_rec.structure_id
     FROM fnd_id_flex_structures f
    WHERE UPPER (f.id_flex_structure_code) = 'REV ITEM CATEGORY';

   l_category_rec.description                               := 'Test Category Codes';
   inv_item_category_pub.create_category (p_api_version                 => 1.0
                                        , p_init_msg_list               => fnd_api.g_false
                                        , p_commit                      => fnd_api.g_true
                                        , x_return_status               => x_return_status
                                        , x_errorcode                   => x_error_code
                                        , x_msg_count                   => x_msg_count
                                        , x_msg_data                    => x_msg_data
                                        , p_category_rec                => l_category_rec
                                        , x_category_id                 => l_out_category_id
                                         );
   DBMS_OUTPUT.put_line ('API Return Status : ' || l_return_status);

   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 ('Category Comibination Create Successfully. Category Id = ' || l_out_category_id);
      DBMS_OUTPUT.put_line ('*****************************************');
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Unexpected Error ' || SUBSTR (SQLERRM, 1, 250));
END;

---******************************
--- Delete Category Api
---******************************

DECLARE
   x_return_status                                   VARCHAR2 (80);
   x_error_code                                      NUMBER;
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (80);
   l_category_id                                     NUMBER := 1842;
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2 (2000);
BEGIN
   inv_item_category_pub.delete_category (p_api_version                 => 1.0
                                        , p_init_msg_list               => fnd_api.g_false
                                        , p_commit                      => fnd_api.g_true
                                        , x_return_status               => x_return_status
                                        , x_errorcode                   => x_error_code
                                        , x_msg_count                   => x_msg_count
                                        , x_msg_data                    => x_msg_data
                                        , p_category_id                 => l_category_id
                                         );
   DBMS_OUTPUT.put_line ('l_return_status : ' || l_return_status);

   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 ('Deleted Category Id : ' || l_category_id || ' Successfully');
      DBMS_OUTPUT.put_line ('*****************************************');
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Unexpected Error ' || SUBSTR (SQLERRM, 1, 250));
END;

API scripts to Create/Update/delete the Category Set/Category Assignment for a Item

/*****************************************************************************
 Script to Create the Category for an item
*****************************************************************************/

DECLARE
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2 (2000);
   x_return_status                                   VARCHAR2 (80);
   x_error_code                                      NUMBER;
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (250);
   l_category_id                                     NUMBER;
   l_category_set_id                                 NUMBER;
   l_inventory_item_id                               NUMBER;
   l_organization_id                                 NUMBER;
BEGIN
   SELECT mcs_tl.category_set_id
     INTO l_category_set_id
     FROM mtl_category_sets_tl mcs_tl
    WHERE mcs_tl.category_set_name = '<category_set_name>';

   SELECT mcb.category_id
     INTO l_category_id
     FROM mtl_categories_b mcb
    WHERE mcb.segment1 = '<category_name>'
      AND mcb.structure_id = (SELECT mcs.structure_id
                                FROM mtl_category_sets_b mcs
                               WHERE mcs.category_set_id = l_category_set_id);

   SELECT organization_id
     INTO l_organization_id
     FROM mtl_parameters
    WHERE organization_code = '<organization_name>';

   SELECT inventory_item_id
     INTO l_inventory_item_id
     FROM mtl_system_items_b
    WHERE segment1 = '<Item_name>'
      AND organization_id = l_organization_id;

   inv_item_category_pub.create_category_assignment (p_api_version                 => 1.0
                                                   , p_init_msg_list               => fnd_api.g_true
                                                   , p_commit                      => fnd_api.g_true
                                                   , x_return_status               => x_return_status
                                                   , x_errorcode                   => x_error_code
                                                   , x_msg_count                   => x_msg_count
                                                   , x_msg_data                    => x_msg_data
                                                   , p_category_id                 => l_category_id
                                                   , p_category_set_id             => l_category_set_id
                                                   , p_inventory_item_id           => l_inventory_item_id
                                                   , p_organization_id             => l_organization_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 ('*****************************************');
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Created Category Assiginment from Item id : ' || l_inventory_item_id || ' Successfully');
      DBMS_OUTPUT.put_line ('*****************************************');
   END IF;
END;
/

/*****************************************************************************
 Script to Update the Category for an item
*****************************************************************************/

DECLARE
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2 (2000);
   x_return_status                                   VARCHAR2 (80);
   x_error_code                                      NUMBER;
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (250);
   l_category_id                                     NUMBER;
   l_category_set_id                                 NUMBER;
   l_inventory_item_id                               NUMBER;
   l_organization_id                                 NUMBER;
   l_old_category_id                                 NUMBER;
BEGIN
   SELECT mcs_tl.category_set_id
     INTO l_category_set_id
     FROM mtl_category_sets_tl mcs_tl
    WHERE mcs_tl.category_set_name = '<category_set_name>';

   SELECT mcb.category_id
     INTO l_category_id
     FROM mtl_categories_b mcb
    WHERE mcb.segment1 = '<category_name>'
      AND mcb.structure_id = (SELECT mcs_b.structure_id
                                FROM mtl_category_sets_b mcs_b
                               WHERE mcs_b.category_set_id = l_category_set_id);

   SELECT organization_id
     INTO l_organization_id
     FROM mtl_parameters
    WHERE organization_code = '<organization_id>';

   SELECT inventory_item_id
     INTO l_inventory_item_id
     FROM mtl_system_items_b
    WHERE segment1 = '<Item_name>'
      AND organization_id = l_organization_id;

   SELECT mcb.category_id
     INTO l_old_category_id
     FROM mtl_system_items_b msi
        , mtl_item_categories mic
        , mtl_categories_b mcb
        , mtl_category_sets mcs
    WHERE 1 = 1
      AND mic.inventory_item_id = msi.inventory_item_id
      AND mic.organization_id = msi.organization_id
      AND mic.category_id = mcb.category_id
      AND mic.category_set_id = mcs.category_set_id
      AND mcb.structure_id = mcs.structure_id
      AND msi.inventory_item_id = c_inventory_item_id
      AND msi.organization_id = c_orgnaization_id
      AND mcs.category_set_id = c_category_set_id;

   inv_item_category_pub.update_category_assignment (p_api_version                 => 1.0
                                                   , p_init_msg_list               => fnd_api.g_false
                                                   , p_commit                      => fnd_api.g_true
                                                   , x_return_status               => l_return_status
                                                   , x_errorcode                   => l_error_code
                                                   , x_msg_count                   => l_msg_count
                                                   , x_msg_data                    => l_msg_data
                                                   , p_category_id                 => l_category_id
                                                   , p_category_set_id             => l_category_set_id
                                                   , p_inventory_item_id           => l_inventory_item_id
                                                   , p_organization_id             => l_organization_id
                                                   , p_old_category_id             => l_old_category_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 ('*****************************************');
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Created Category Assiginment from Item id : ' || l_inventory_item_id || ' Successfully');
      DBMS_OUTPUT.put_line ('*****************************************');
   END IF;
END;
/

/*****************************************************************************
 Script to delete the categroy assignment for a item
*****************************************************************************/

DECLARE
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2 (2000);
   x_return_status                                   VARCHAR2 (80);
   x_error_code                                      NUMBER;
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (250);

   CURSOR c_get_item_categories
   IS
      SELECT msi.segment1
           , msi.inventory_item_id
           , mcs.category_set_id
           , mcb.category_id
           , msi.organization_id
        FROM mtl_system_items_b msi
           , mtl_item_categories mic
           , mtl_categories_b mcb
           , mtl_category_sets mcs
       WHERE 1 = 1
         AND mic.inventory_item_id = msi.inventory_item_id
         AND mic.organization_id = msi.organization_id
         AND mic.category_id = mcb.category_id
         AND mic.category_set_id = mcs.category_set_id
         AND mcb.structure_id = mcs.structure_id
         AND msi.inventory_item_id = c_inventory_item_id
         AND msi.organization_id = c_orgnaization_id;
BEGIN
   FOR c_get_item_categories_rec IN c_get_item_categories
   LOOP
      inv_item_category_pub.delete_category_assignment (p_api_version                 => 1.0
                                                      , p_init_msg_list               => fnd_api.g_true
                                                      , p_commit                      => fnd_api.g_true
                                                      , x_return_status               => x_return_status
                                                      , x_errorcode                   => x_error_code
                                                      , x_msg_count                   => x_msg_count
                                                      , x_msg_data                    => x_msg_data
                                                      , p_category_id                 => c_get_item_categories_rec.category_id
                                                      , p_category_set_id             => c_get_item_categories_rec.category_set_id
                                                      , p_inventory_item_id           => c_get_item_categories_rec.inventory_item_id
                                                      , p_organization_id             => c_get_item_categories_rec.organization_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 ('*****************************************');
      ELSE
         DBMS_OUTPUT.put_line ('*****************************************');
         DBMS_OUTPUT.put_line (   'Removed Category Assiginment from Item : '
                               || c_get_item_categories_rec.segment1
                               || ' Successfully'
                              );
         DBMS_OUTPUT.put_line ('*****************************************');
      END IF;
   END LOOP;
END;
/
********************************************************************
 Bulk Update script to Update the Category on Item
********************************************************************
DECLARE
   CURSOR c_category
   IS
      SELECT mcb.category_id
           , mcs.category_set_id
           , msi.organization_id
           , msi.inventory_item_id
           , msi.segment1
        FROM mtl_system_items_b msi
           , mtl_item_categories mic
           , mtl_categories_b mcb
           , mtl_category_sets mcs
       WHERE 1 = 1
         AND mic.inventory_item_id = msi.inventory_item_id
         AND mic.organization_id = msi.organization_id
         AND mic.category_id = mcb.category_id
         AND mic.category_set_id = mcs.category_set_id
         AND mcb.structure_id = mcs.structure_id
         AND msi.segment1 = :p_segment1
          OR msi.inventory_item_id = :p_inventory_item_id
         AND msi.organization_id = :p_organization_id
         AND mcs.category_set_id = :p_category_set_id
         AND mcb.category_id = :p_category_id;

   l_msg_index_out                              NUMBER;
   l_error_message                              VARCHAR2(2000);
   x_return_status                              VARCHAR2(80);
   x_error_code                                 NUMBER;
   x_msg_count                                  NUMBER;
   x_msg_data                                   VARCHAR2(250);
   l_category_id                                NUMBER;
   l_category_set_id                            NUMBER;
   l_inventory_item_id                          NUMBER;
   l_organization_id                            NUMBER;
   l_old_category_id                            NUMBER;
BEGIN
   fnd_global.apps_initialize( 12247, 20634, 401);
   COMMIT;

   FOR z IN c_category
   LOOP
      inv_item_category_pub.update_category_assignment(
         p_api_version                             => 1.0
       , p_init_msg_list                           => fnd_api.g_false
       , p_commit                                  => fnd_api.g_true
       , x_return_status                           => x_return_status
       , x_errorcode                               => x_error_code
       , x_msg_count                               => x_msg_count
       , x_msg_data                                => x_msg_data
       , p_category_id                             => :p_new_category_id                                                    --3372
       , p_category_set_id                         => z.category_set_id
       , p_inventory_item_id                       => z.inventory_item_id
       , p_organization_id                         => z.organization_id
       , p_old_category_id                         => z.category_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( '*****************************************');
      ELSE
         DBMS_OUTPUT.put_line( '*****************************************');
         DBMS_OUTPUT.put_line(
               'Created Category Assiginment from Item id : '
            || l_inventory_item_id
            || ' Successfully');
         DBMS_OUTPUT.put_line( '*****************************************');
      END IF;
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(
            'UNEXP_ERROR IN MAIN : '
         || SUBSTR( SQLERRM, 1, 250));
END;
/