API to Delete Position Hierarchy

DECLARE

   CURSOR c1

   IS

      SELECT ppse.*

        FROM per_pos_structure_versions ppsv,

             per_position_structures pps,

             per_pos_structure_elements ppse

       WHERE     ppsv.pos_structure_version_id =

                    ppse.pos_structure_version_id

             AND ppsv.position_structure_id = pps.position_structure_id

             AND UPPER (pps.name) = 'XXHR POSITION HIERARCHY'

             AND TRUNC (SYSDATE) BETWEEN ppsv.date_from

                                     AND NVL (ppsv.date_to,

                                              hr_general.end_of_time)

             AND pps.primary_position_flag = 'Y'

             AND pps.business_group_id = 10665;

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

         hr_pos_hierarchy_ele_api.delete_pos_hierarchy_ele (

            p_validate                   => FALSE,

            p_pos_structure_element_id   => i.pos_structure_element_id,

            p_object_version_number      => i.object_version_number,

            p_hr_installed               => 'I');

         COMMIT;

         DBMS_OUTPUT.put_line (

            i.pos_structure_element_id || ' has been deleted');

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (

               'Failure: ' || SQLERRM || '-->' || i.pos_structure_element_id);

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

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

END;