API to Create HR Lookup Values

DECLARE

   CURSOR get_lookup_details

   IS

      SELECT ltype.application_id,

             ltype.customization_level,

             ltype.creation_date,

             ltype.created_by,

             ltype.last_update_date,

             ltype.last_updated_by,

             ltype.last_update_login,

             tl.lookup_type,

             tl.security_group_id,

             tl.view_application_id,

             tl.description,

             tl.meaning

        FROM fnd_lookup_types_tl tl, fnd_lookup_types ltype

       WHERE     ltype.lookup_type = 'XX JOBS'

             AND ltype.lookup_type = tl.lookup_type

             AND language = 'US';

   CURSOR get_value

   IS

      SELECT *

        FROM xxhr_job_t

       WHERE lkp_process_flag = 'N';

   l_rowid   VARCHAR2 (100) := 0;

   l_code    NUMBER := 1;

   l_err_msg varchar2(1000) := NULL;

BEGIN

   FOR i IN get_lookup_details

   LOOP

      FOR j IN get_value

      LOOP

         l_rowid := NULL;

         l_err_msg := NULL;

         l_code := l_code + 1;

         BEGIN

            fnd_lookup_values_pkg.insert_row (

               x_rowid                 => l_rowid,

               x_lookup_type           => i.lookup_type,

               x_security_group_id     => i.security_group_id,

               x_view_application_id   => i.view_application_id,

               x_lookup_code           => l_code,

               x_tag                   => NULL,

               x_attribute_category    => NULL,

               x_attribute1            => NULL,

               x_attribute2            => NULL,

               x_attribute3            => NULL,

               x_attribute4            => NULL,

               x_enabled_flag          => 'Y',

               x_start_date_active     => TO_DATE ('01-JAN-1950',

                                                   'DD-MON-YYYY'),

               x_end_date_active       => NULL,

               x_territory_code        => NULL,

               x_attribute5            => NULL,

               x_attribute6            => NULL,

               x_attribute7            => NULL,

               x_attribute8            => NULL,

               x_attribute9            => NULL,

               x_attribute10           => NULL,

               x_attribute11           => NULL,

               x_attribute12           => NULL,

               x_attribute13           => NULL,

               x_attribute14           => NULL,

               x_attribute15           => NULL,

               x_meaning               => j.job_name,

               x_description           => j.job_name,

               x_creation_date         => SYSDATE,

               x_created_by            => i.created_by,

               x_last_update_date      => i.last_update_date,

               x_last_updated_by       => i.last_updated_by,

               x_last_update_login     => i.last_update_login);

            UPDATE xxhr_job_t --> Stagging Table to Log Status

               SET lkp_process_flag = 'Y', lkp_err_msg = NULL

             WHERE UPPER (job_name) = UPPER (j.job_name);

            COMMIT;

         EXCEPTION

            WHEN OTHERS

            THEN

               l_err_msg := SQLERRM;

               UPDATE xxhr_job_t

                  SET lkp_process_flag = 'N', lkp_err_msg = l_err_msg

                WHERE UPPER (job_name) = UPPER (j.job_name);

               

                COMMIT;

         END;

      END LOOP;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;