API – Inventory Item Category Combination Creation

CREATE OR REPLACE PROCEDURE APPS.xx_create_new_item_category
IS
   l_category_rec    inv_item_category_pub.category_rec_type;
/* 
            Query to check results 
            SELECT  M1.SEGMENT1||'.'||M1.SEGMENT2||'.'||M1.SEGMENT3||'.'||M1.SEGMENT4||'.'||M1.SEGMENT5||'.'||M1.SEGMENT6
            FROM    MTL_CATEGORIES_B M1,
                    MTL_CATEGORIES_TL M2
            WHERE   M1.CATEGORY_ID = M2.CATEGORY_ID
            AND     M2.LANGUAGE = 'US'
            
            
            Table  Structure 
          CREATE TABLE XX_CREATE_NEW_CATEGORY
            (
              STRUCTURE_ID  NUMBER,
              SEGMENT1      VARCHAR2(2000 BYTE),
              SEGMENT2      VARCHAR2(2000 BYTE),
              SEGMENT3      VARCHAR2(2000 BYTE),
              SEGMENT4      VARCHAR2(2000 BYTE),
              SEGMENT5      VARCHAR2(2000 BYTE),
              SEGMENT6      VARCHAR2(2000 BYTE)
            )

*/
   CURSOR c_cat
   IS
   SELECT *
   FROM xx_create_new_category;

   l_api_version     NUMBER;
   o_return_status   VARCHAR2 (2000);
   o_msg_count       NUMBER;
   o_msg_data        VARCHAR2 (2000);
   o_errorcode       VARCHAR2 (2000);
   l_error_code      VARCHAR2 (2000);
   l_error_desc      VARCHAR2 (2000);
   l_conv_status     VARCHAR2 (2000);
   v_category_id     NUMBER;
   
   
BEGIN
fnd_global.apps_initialize (1130, 50634, 401);

   FOR i IN c_cat
   LOOP
      l_category_rec := NULL;
      l_category_rec.structure_id := i.structure_id;   -- inventory super user > setup > items > categories > Query The Category Set > get the Struture id from Examine
      --   l_category_rec.structure_code := g_cat_flex_code;
      l_category_rec.summary_flag := 'N';
      l_category_rec.enabled_flag := 'Y';
      l_category_rec.segment1 := TO_CHAR (i.segment1);
      l_category_rec.segment2 := TO_CHAR (i.segment2);
      l_category_rec.segment3 := TO_CHAR (i.segment3);
      l_category_rec.segment4 := TO_CHAR (i.segment4);
      l_category_rec.segment5 := TO_CHAR (i.segment5);
      l_category_rec.segment6 := TO_CHAR (i.segment6);
      --
      -- After the category record is loaded, then call the create_category api to
      -- create the new mtl_categories record.
      inv_item_category_pub.create_category
                                       (p_api_version        => 1.0,
                                                              --l_api_version,
                                        p_init_msg_list      => fnd_api.g_false,
                                        p_commit             => fnd_api.g_true,
                                        x_return_status      => o_return_status,
                                        x_errorcode          => o_errorcode,
                                        x_msg_count          => o_msg_count,
                                        x_msg_data           => o_msg_data,
                                        p_category_rec       => l_category_rec,
                                        x_category_id        => v_category_id
                                       );       --l_category_rec.category_id);

      IF o_return_status = 'S'
      THEN
         DBMS_OUTPUT.put_line ('*****************');
         DBMS_OUTPUT.put_line (   'Category successfully created. New Category Id = '|| v_category_id);
         DBMS_OUTPUT.put_line (   'Segment Combination = '|| i.segment1|| '.'|| i.segment2|| '.'|| i.segment3|| '.'|| i.segment4|| '.'|| i.segment5|| '.'|| i.segment6);
         DBMS_OUTPUT.put_line ('*****************');
      ELSE
         l_error_code := NVL (l_error_code, '') || 'CATCONV005,';
         l_error_desc := NVL (l_error_desc, '') || '#API Error while creating Category';
         l_conv_status := 'ERROR';
         DBMS_OUTPUT.put_line ('API STATUS : ' || o_return_status);
         DBMS_OUTPUT.put_line (   'Segment Combination = '|| i.segment1|| '.'|| i.segment2|| '.'|| i.segment3|| '.'|| i.segment4|| '.'|| i.segment5
                               || '.'|| i.segment6|| SQLERRM);

         IF o_msg_count > 0
         THEN
            DBMS_OUTPUT.put_line (o_errorcode);
            DBMS_OUTPUT.put_line (o_msg_data);

            FOR i IN 1 .. o_msg_count
            LOOP
               DBMS_OUTPUT.put_line
                      (   i|| '.'|| SUBSTR(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),1,255));
            END LOOP;
         END IF;
      END IF;
   END LOOP;
END;
/

begin
xx_create_new_item_category;
end;