Query of Position Hierarchy

Query of Position Hierarchy 


    SELECT DISTINCT
           CONNECT_BY_ROOT (PARENT_POSITION_ID) perant_id,
           HR_GENERAL.DECODE_POSITION_LATEST_NAME (parent_POSITION_ID)
              direct_parent_name,
           SUBORDINATE_POSITION_ID child_id,
           HR_GENERAL.DECODE_POSITION_LATEST_NAME (SUBORDINATE_POSITION_ID)
              child_name,
           ---------------------------------
           SYS_CONNECT_BY_PATH (SUBORDINATE_POSITION_ID, '/') PATH,
           ---------------------------------
           HR_GENERAL.DECODE_POSITION_LATEST_NAME (
              REPLACE (
                 SUBSTR (
                    SYS_CONNECT_BY_PATH (SUBORDINATE_POSITION_ID, '/'),
                    0,
                    INSTR (SYS_CONNECT_BY_PATH (SUBORDINATE_POSITION_ID, '/'),
                           '/',
                           2)),
                 '/',
                 NULL))
              substr_parent_name,
           ---------------------------------
           HR_GENERAL.DECODE_POSITION_LATEST_NAME (
              SUBSTR (SYS_CONNECT_BY_PATH (SUBORDINATE_POSITION_ID, '/'), 2, 4))
              parent_name,
           ---------------------------------
           LEVEL "LVL",
           BUSINESS_GROUP_ID
      FROM PER_POS_STRUCTURE_ELEMENTS ss
     WHERE SUBORDINATE_POSITION_ID = :pchild_id                        -- 4101
--                                               AND LEVEL > (8)-3
CONNECT BY NOCYCLE PRIOR SUBORDINATE_POSITION_ID = parent_POSITION_ID  --PRIOR
START WITH                                                         --LEVEL = 0
           (   SUBORDINATE_POSITION_ID) IS NOT NULL
           AND (PARENT_POSITION_ID) IS NOT NULL
  ORDER BY LEVEL DESC

Delete Person Absence API

Note this when its Submit but if still in AME Cycle Use Wrok Flow to approve then delete it or Reject 
--
The API 
---
DECLARE
   CURSOR get_absence
   IS
      SELECT absence_attendance_id, object_version_number
        FROM per_absence_attendances
       WHERE TRUNC (creation_date) = TRUNC (SYSDATE);
BEGIN
   FOR fetch_absence IN get_absence
   LOOP
      BEGIN
         hr_person_absence_api.delete_person_absence (
            p_validate                => FALSE,
            p_absence_attendance_id   => fetch_absence.absence_attendance_id,
            p_object_version_number   => fetch_absence.object_version_number);
         COMMIT;
         DBMS_OUTPUT.put_line (
               fetch_absence.absence_attendance_id
            || ' has been deleted Successfully !!!');
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  'Inner Exception: '
               || fetch_absence.absence_attendance_id
               || ' - - '
               || SQLERRM);
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;