API to Delete SIT

DECLARE

   CURSOR c1

   IS

      SELECT paa.person_analysis_id, paa.object_version_number

        FROM per_person_analyses paa,

             per_analysis_criteria pac,

             fnd_id_flex_structures_vl fifv,

             per_all_people_f per

       WHERE     paa.analysis_criteria_id = pac.analysis_criteria_id

             AND fifv.id_flex_num = paa.id_flex_num

             AND UPPER (fifv.id_flex_structure_code) = 'XXX_VISA_DETAILS'

             AND fifv.id_flex_code = 'PEA'

             AND paa.date_from BETWEEN per.effective_start_date

                                   AND per.effective_end_date

             AND paa.person_id = per.person_id;

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

         hr_sit_api.

          delete_sit (

            p_validate                    => FALSE,

            p_person_analysis_id          => i.person_analysis_id,

            p_pea_object_version_number   => i.object_version_number);

         COMMIT;

         DBMS_OUTPUT.

          put_line (

            i.person_analysis_id || ' has been deleted successfully !!!');

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.

             put_line (

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

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

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

END;