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;

Script to Compile all invalid Objects in the Data base.

DECLARE
   l_schema                                          VARCHAR2 (240) := '&Schema_name';
   l_include_java                                    INTEGER := 0;
   l_max_passes                                      INTEGER := 10;
   l_debug_flag                                      INTEGER := 0;
   -- Above four variables can be used as parameters if defining a procedure for the Anonymous block
   l_alter_string                                    VARCHAR2 (4000);
   c_cursor                                          INTEGER;
   l_ret_code                                        INTEGER;
   l_object_count                                    INTEGER;
   l_pass                                            INTEGER;
   l_message                                         VARCHAR2 (8000);
   l_status                                          NUMBER;

   CURSOR c_objects_dtls (
      c_schema                   IN             VARCHAR2
   )
   IS
      SELECT   owner
             , object_type
             , object_name
             , DECODE (object_type, 'TYPE', 1, 'OPERATOR', 2, 'PACKAGE', 3, 4) obj_type
          FROM all_objects
         WHERE (status = 'INVALID')
           AND (   c_schema IS NULL
                OR owner = UPPER (c_schema))
           AND object_type IN
                  ('DIMENSION', 'FUNCTION', 'INDEXTYPE', 'JAVA CLASS', 'JAVA SOURCE', 'MATERIALIZED VIEW', 'OPERATOR'
                 , 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TRIGGER', 'TYPE', 'TYPE BODY', 'VIEW')
      ORDER BY obj_type;
BEGIN
   c_cursor                                                 := SYS.DBMS_SQL.open_cursor;
   l_pass                                                   := 1;

   LOOP
      l_object_count                                           := 0;

      FOR c_objects_dtls_rec IN c_objects_dtls (l_schema)
      LOOP
         IF (c_objects_dtls_rec.object_type = 'PACKAGE')
         THEN
            l_alter_string                                           :=
               'ALTER PACKAGE "' || c_objects_dtls_rec.owner || '"."' || c_objects_dtls_rec.object_name
               || '" COMPILE PACKAGE';
         ELSIF (c_objects_dtls_rec.object_type = 'PACKAGE BODY')
         THEN
            l_alter_string                                           :=
                'ALTER PACKAGE "' || c_objects_dtls_rec.owner || '"."' || c_objects_dtls_rec.object_name || '" COMPILE BODY';
         ELSIF (c_objects_dtls_rec.object_type = 'TYPE')
         THEN
            l_alter_string                                           :=
                  'ALTER TYPE "'
               || c_objects_dtls_rec.owner
               || '"."'
               || c_objects_dtls_rec.object_name
               || '" COMPILE SPECIFICATION';
         ELSIF (c_objects_dtls_rec.object_type = 'TYPE BODY')
         THEN
            l_alter_string                                           :=
                   'ALTER TYPE "' || c_objects_dtls_rec.owner || '"."' || c_objects_dtls_rec.object_name || '" COMPILE BODY';
         ELSE
            l_alter_string                                           :=
                  'ALTER '
               || c_objects_dtls_rec.object_type
               || ' "'
               || c_objects_dtls_rec.owner
               || '"."'
               || c_objects_dtls_rec.object_name
               || '" COMPILE';
         END IF;

         IF (    l_include_java <> 1
             AND c_objects_dtls_rec.object_type LIKE '%JAVA%')
         THEN
            l_alter_string                                           := NULL;
         END IF;

         IF (l_alter_string IS NOT NULL)
         THEN
            l_object_count                                           := l_object_count + 1;
            l_message                                                :=
                  'CompileAllObjects pass '
               || TO_CHAR (l_pass, '99999')
               || ' processing object '
               || TO_CHAR (l_object_count, '99999')
               || ' ['
               || l_alter_string
               || ']';

            BEGIN
               DBMS_SQL.parse (c_cursor, l_alter_string, DBMS_SQL.native);
               l_ret_code                                               := DBMS_SQL.EXECUTE (c_cursor);
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_message                                                :=
                                                                  l_message || ' => ERROR # ' || SQLCODE || ' - ' || SQLERRM;
                  NULL;
            END;

            IF (l_debug_flag <> 0)
            THEN
               DBMS_PIPE.pack_message (LENGTH (l_message));
               DBMS_PIPE.pack_message (l_message);
               l_status                                                 :=
                                                      DBMS_PIPE.send_message ('plsql_debug', DBMS_PIPE.maxwait, 1024 * 1024);
            END IF;
         END IF;
      END LOOP;

      EXIT WHEN l_object_count = 0
            OR l_pass >= l_max_passes;
      l_pass                                                   := l_pass + 1;
   END LOOP;

   DBMS_SQL.close_cursor (c_cursor);

   IF l_debug_flag <> 0
   THEN
      l_message                                                :=
            '*** The Invalid Object Compilation is Completed'
         || TO_CHAR (l_object_count)
         || ' objects in '
         || l_pass
         || ' passes ***';
      DBMS_PIPE.pack_message (LENGTH (l_message));
      DBMS_PIPE.pack_message (l_message);
      l_status                                                 :=
                                                       DBMS_PIPE.send_message ('plsql_debug', DBMS_PIPE.maxwait, 1024 * 1024);
   END IF;
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;
/