Oracle Application “Position Hierarchy SQL Query”

 

If you are asked to make a report for listing the elements with a named Position Hierarchy, it could quite difficult because of the complexity with the way Oracle is maintaining the position hierarchies. please find below a practical solution to this requirement

Step 1

Create a view

Create view XXPOSHIERARCHY_V
AS
SELECT pps.NAME, LPAD (' ', 5 * LEVEL) || has.NAME hierarchy, has.position_id,LEVEL rep_level,
hap.NAME parent_name, pse.parent_position_id, has.NAME child_name,
pse.subordinate_position_id
FROM (SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV ('LANG')) hap,
(SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV ('LANG')) has,
per_pos_structure_elements pse,
per_pos_structure_versions pve,
per_position_structures pps
WHERE pse.business_group_id = 81 --Replace with your own business group id
AND pve.position_structure_id = pps.position_structure_id
AND pse.POS_STRUCTURE_VERSION_ID = pve.POS_STRUCTURE_VERSION_ID
AND sysdate between pve.date_from and NVL(pve.date_to, sysdate)
AND hap.position_id = pse.parent_position_id
AND has.position_id = pse.subordinate_position_id
start with pse.parent_position_id = 
(SELECT parent_position_id FROM per_pos_structure_elements a
WHERE A.POS_STRUCTURE_VERSION_ID = pse.pos_structure_version_id
AND a.POS_STRUCTURE_ELEMENT_ID = (SELECT MIN (POS_STRUCTURE_ELEMENT_ID)
FROM per_pos_structure_elements b WHERE b.POS_STRUCTURE_VERSION_ID = A.POS_STRUCTURE_VERSION_ID))
CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
AND PRIOR pse.business_group_id = pse.business_group_id;

Now from the view above, you can easily populate the reporting structure using the following query (including the positions, employee names etc)

Select 0 rnum, opv.parent_name position_effective, 0 rep_level, paaf.person_id, papf.full_name,
opv.parent_position_id, opv.name hierarchy_name
from XXPOSHIERARCHY_V opv, per_all_assignments_f paaf,
per_all_people_f  papf
where NAME LIKE 'XYZ PR%'
and paaf.position_id = opv.parent_position_id
and papf.person_id = paaf.person_id
and sysdate between paaf.effective_start_date and nvl(paaf.effective_end_date, sysdate)
and sysdate between papf.effective_start_date and nvl(papf.effective_end_date, sysdate)
and rep_level = 1
UNION ALL
Select ROWNUM rnum, opv.hierarchy, opv.rep_level, paaf.person_id, papf.full_name,
opv.parent_position_id, opv.name
from XXPOSHIERARCHY_V opv, per_all_assignments_f paaf,
per_all_people_f  papf
where NAME LIKE 'XYZ PR%'
and paaf.position_id = opv.position_id
and papf.person_id = paaf.person_id
and sysdate between paaf.effective_start_date and nvl(paaf.effective_end_date, sysdate)
and sysdate between papf.effective_start_date and nvl(papf.effective_end_date, sysdate)
order by 1;

Adding rownum along with the query will provide you the flexibility to maintain the rpad -ed position names intact while retrieving a particular position hierarchy details.

In order to make the entire reporting dynamic we have created a PL/SQL sequence, populating all hierarchies into a local table. Please find the logic below

CREATE TABLE XXHIERELEMENTS
(
  RNUM                NUMBER,
  POSITION_EFFECTIVE  VARCHAR2(4000 BYTE),
  REP_LEVEL           NUMBER,
  PERSON_ID           NUMBER(10),
  FULL_NAME           VARCHAR2(240 BYTE),
  PARENT_POSITION_ID  NUMBER(15),
  HIERARCHY_NAME      VARCHAR2(30 BYTE)
)

and by executing the below PL/SQL sequence populate the table one time prior the report ran (please do not forget to truncate the table prior each report run!)

 

SET SERVEROUTPUT ON;

DECLARE
   CURSOR c1
   IS
        SELECT DISTINCT name hierarchy_name
          FROM XXPOSHIERARCHY_V  ORDER BY 1;
BEGIN
   FOR i IN C1
   LOOP
      DBMS_OUTPUT.PUT_LINE (i.hierarchy_name);

      INSERT INTO XXHIERELEMENTS
         SELECT 0 rnum,
                opv.parent_name position_effective,
                0 rep_level,
                paaf.person_id,
                papf.full_name,
                opv.parent_position_id,
                opv.name hierarchy_name
           FROM XXPOSHIERARCHY_V opv,
                per_all_assignments_f paaf,
                per_all_people_f papf
          WHERE     NAME = i.hierarchy_name
                AND paaf.position_id = opv.parent_position_id
                AND papf.person_id = paaf.person_id
                AND SYSDATE BETWEEN paaf.effective_start_date
                                AND NVL (paaf.effective_end_date, SYSDATE)
                AND SYSDATE BETWEEN papf.effective_start_date
                                AND NVL (papf.effective_end_date, SYSDATE)
                AND rep_level = 1
         UNION ALL
         SELECT ROWNUM rnum,
                opv.hierarchy,
                opv.rep_level,
                paaf.person_id,
                papf.full_name,
                opv.parent_position_id,
                opv.name
           FROM XXPOSHIERARCHY_V opv,
                per_all_assignments_f paaf,
                per_all_people_f papf
          WHERE     NAME = i.hierarchy_name
                AND paaf.position_id = opv.position_id
                AND papf.person_id = paaf.person_id
                AND SYSDATE BETWEEN paaf.effective_start_date
                                AND NVL (paaf.effective_end_date, SYSDATE)
                AND SYSDATE BETWEEN papf.effective_start_date
                                AND NVL (papf.effective_end_date, SYSDATE);
   END LOOP;

   COMMIT;
END;

We hope this thread is useful for you

For Windows7bugs,

Admin