Item Category Interface

This script will help to migrate Item Master with Item Category details
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

/* Formatted on 2010/08/07 17:57 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE xxx_item_master_org_api
AS
   l_err_flag          VARCHAR2 (3);
   l_err_msg           VARCHAR2 (2500);
   l_count             NUMBER (2);
   l_category_set_id   NUMBER (20);
   l_category_id       NUMBER (20);
   l_org_id            NUMBER (4);
   l_template_name     VARCHAR2 (250);
   l_uom               VARCHAR2 (20);
   l_item_type         VARCHAR2 (20);
   l_organization_id   NUMBER (10);

--CURSOR TO FETCH THE DATA FROM STAGING TABLE
   CURSOR c1
   IS
      SELECT *
        FROM xxx_item_master_stg
       WHERE NVL (verify_flag, 'N') = 'N';
BEGIN
   FOR c_rec IN c1
   LOOP
      BEGIN
--- initialize the variables
         l_err_flag := 'Y';
         l_err_msg := NULL;

---*********** MASTER ORGANIZATION VALIDATION
         BEGIN
            SELECT organization_id
              INTO l_organization_id
              FROM org_organization_definitions
             WHERE UPPER (organization_name) =
                                              UPPER (TRIM ('XXX ITEM MASTER'));
         EXCEPTION
            WHEN OTHERS
            THEN
               l_err_flag := 'Y';
               l_err_msg := 'INVALID ORGANIZATION';
         END;

---*********** ITEM TYPE VALIDATION
         BEGIN
            SELECT lookup_code
              INTO l_item_type
              FROM fnd_lookup_values
             WHERE lookup_type = 'ITEM_TYPE'
               AND UPPER (meaning) = TRIM (UPPER (c_rec.item_type));
         EXCEPTION
            WHEN OTHERS
            THEN
               l_err_flag := 'N';
               l_err_msg := l_err_msg || 'INVALID ITEM TYPE';
         END;

---*************** ITEM CODE VALIDATION
         BEGIN
            l_count := 0;

            SELECT COUNT (*)
              INTO l_count
              FROM mtl_system_items_b
             WHERE UPPER (TRIM (segment1)) = UPPER (TRIM (c_rec.item_code))
               AND organization_id = l_organization_id;

            IF l_count > 0
            THEN
               l_err_flag := 'N';
               l_err_msg := l_err_msg || 'ITEM ALREADY EXISTING';
            END IF;

----*********** VALIDATE THE DESCRIPTION
            IF TRIM (c_rec.description) IS NULL
            THEN
               l_err_flag := 'N';
               l_err_msg := l_err_msg || 'INVALID DESCRIPTION';
            END IF;
         END;

--- ITEM TEMPLATE VALIDATION
         BEGIN
            SELECT template_name
              INTO l_template_name
              FROM mtl_item_templates
             WHERE UPPER (TRIM (template_name)) =
                                            UPPER (TRIM (c_rec.template_name));
         EXCEPTION
            WHEN OTHERS
            THEN
               l_err_flag := 'N';
               l_err_msg := l_err_msg || 'INVALID TEMPLATE NAME';
         END;
      END;

--- PRIMARY UOM VALIDATION
      BEGIN
         SELECT unit_of_measure
           INTO l_uom
           FROM mtl_units_of_measure
          WHERE UPPER (TRIM (uom_code)) = UPPER (TRIM (c_rec.uom));
                                                          ---eg litre, numbers
      EXCEPTION
         WHEN OTHERS
         THEN
            l_err_flag := 'N';
            l_err_msg := l_err_msg || 'INVALID UOM';
      END;

--- ITEM CATEGORY VALIDATION
      BEGIN
         SELECT category_id
           INTO l_category_id
           FROM mtl_categories_v
          WHERE segment1 = TRIM (c_rec.cat_segment1)
            AND segment2 = TRIM (c_rec.cat_segment2);
      EXCEPTION
         WHEN OTHERS
         THEN
            l_err_flag := 'N';
            l_err_msg := l_err_msg || 'INVALID CATEGORY';
      END;

---************** ITEM CATEGORY SET VALIDATION
      BEGIN
         SELECT category_set_id
           INTO l_category_set_id
           FROM mtl_category_sets_tl
          WHERE category_set_name = TRIM (c_rec.cat_set_name);
      EXCEPTION
         WHEN OTHERS
         THEN
            l_err_flag := 'N';
            l_err_msg := l_err_msg || 'INVALID CATEGORY SET';
      END;

--- ITEM CATEGORY COMBINATION VALIDATION
      BEGIN
         l_count := 0;

         SELECT COUNT (*)
           INTO l_count
           FROM mtl_category_set_valid_cats_v
          WHERE category_set_id = l_category_set_id
            AND category_id = l_category_id;

         IF l_count = 0
         THEN
            l_err_flag := 'N';
            l_err_msg := l_err_msg || 'INVALID CATEGORY COMBINATION';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_err_flag := 'N';
            l_err_msg := l_err_msg || 'INVALID CATEGORY COMBINATION';
      END;

---- IF ALL VALIDATIONS ARE CORRECT, THEN INSERT DATA INTO INTERFACE TABLE
---- THEN BY USING IMPORT ITEM CONCURRENT PGM, IMPORT ITEMS FROM INTERFACE TABLE INTO BASE TABLE
      IF l_err_flag = 'Y'
      THEN
         BEGIN
            INSERT INTO mtl_system_items_interface
                        (item_type, process_flag, segment1, description
                        ,primary_unit_of_measure, set_process_id
                        ,template_name, organization_id, transaction_type
                        )
                 VALUES (l_item_type, 1, c_rec.item_code, c_rec.description
                        ,l_uom, 3
                        ,l_template_name, l_organization_id, 'CREATE'
                        );

            INSERT INTO mtl_item_categories_interface
                        (item_number, category_set_id, category_id
                        ,process_flag, organization_id, set_process_id
                        ,transaction_type
                        )
                 VALUES (c_rec.item_code, l_category_set_id, l_category_id
                        ,1, l_organization_id, 3
                        ,'CREATE'
                        );

            UPDATE xxx_item_master_stg
               SET verify_flag = 'Y'
                  ,err_msg = NULL
             WHERE item_code = c_rec.item_code;

            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_err_msg := SQLERRM;

               UPDATE xxx_item_master_stg
                  SET verify_flag = 'N'
                     ,err_msg = l_err_msg
                WHERE item_code = c_rec.item_code;
         END;

         COMMIT;
      ELSE
         UPDATE xxx_item_master_stg
            SET verify_flag = 'N'
               ,err_msg = l_err_msg
          WHERE item_code = c_rec.item_code;
      END IF;

      COMMIT;
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END xxx_item_master_org_api;
/