API to Update SIT

DECLARE

   CURSOR c1 (

      p_id_flex_num NUMBER)

   IS

      SELECT per.employee_number,

             pac.segment1,

             pac.segment10,

             ppa.person_analysis_id,

             ppa.object_version_number,

             pac.analysis_criteria_id

        FROM per_person_analyses ppa,

             per_analysis_criteria pac,

             per_people_x per

       WHERE     ppa.id_flex_num = p_id_flex_num

             AND ppa.analysis_criteria_id = pac.analysis_criteria_id

             AND ppa.person_id = per.person_id

             AND ppa.analysis_criteria_id = 11261;

   l_analysis_criteria_id   NUMBER := NULL;

   l_id_flex_num            NUMBER := NULL;

BEGIN

   -- Get Flex Structure Code Flex Num

   SELECT fifs.id_flex_num

     INTO l_id_flex_num

     FROM fnd_id_flex_structures_tl fifst, fnd_id_flex_structures fifs

    WHERE     UPPER (fifst.id_flex_structure_name) = 'VISA DETAILS'

          AND fifst.language = 'US'

          AND fifs.id_flex_code = fifst.id_flex_code

          AND fifs.id_flex_num = fifst.id_flex_num;

   IF l_id_flex_num IS NOT NULL

   THEN

      FOR i IN c1 (l_id_flex_num)

      LOOP

         BEGIN

            l_analysis_criteria_id := NULL;

            hr_sit_api.

             update_sit (

               p_validate                    => FALSE,

               p_person_analysis_id          => i.person_analysis_id,

               p_pea_object_version_number   => i.object_version_number,

               p_segment10                   => 'Completed',

               p_analysis_criteria_id        => l_analysis_criteria_id);

            COMMIT;

            DBMS_OUTPUT.

             put_line (

               i.person_analysis_id || ' has been updated Successfully !!!!');

         EXCEPTION

            WHEN OTHERS

            THEN

               DBMS_OUTPUT.

                put_line (

                  'Failure - ' || i.person_analysis_id || ' - ' || SQLERRM);

         END;

      END LOOP;

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

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

END;