API to Create Checklist Items

CREATE OR REPLACE PROCEDURE xxh_create_checklist_p (p_person_id    NUMBER,

                                                    p_bg_name      VARCHAR2)

IS

   CURSOR check_list

   IS

        SELECT hl.meaning, tag, lookup_code

          FROM fnd_lookup_values hl

         WHERE     lookup_type = 'CHECKLIST_ITEM'

               AND enabled_flag = 'Y'

               AND language = 'US'

               AND UPPER (tag) = UPPER (p_bg_name)

               AND NOT EXISTS

                      (SELECT 'X'

                         FROM per_checklist_items_v

                        WHERE person_id = p_person_id)

      ORDER BY hl.meaning;

   l_checklist_item_id       NUMBER;

   l_object_version_number   NUMBER;

BEGIN

   FOR i IN check_list

   LOOP

      BEGIN

         per_checklist_items_api.

          create_checklist_items (

            p_validate                => FALSE,

            p_effective_date          => SYSDATE,

            p_person_id               => p_person_id,

            p_item_code               => i.lookup_code,

            p_status                  => 'GLB_PENDING',

            p_notes                   => NULL,

            p_checklist_item_id       => l_checklist_item_id,

            p_object_version_number   => l_object_version_number);

         COMMIT;

      EXCEPTION

         WHEN OTHERS

         THEN

            NULL;

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      NULL;

END;