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

Advertisements

5 Responses to Oracle Application “Position Hierarchy SQL Query”

  1. Marco van der Linden says:

    Nice solution. One small comment though: the way you determine the starting position of the hierarchy assumes that the structure is a clean setup with not changes over time. I think the start with clause below will give a better solution.

    Regards,
    Marco van der Linden

    START WITH pse.parent_position_id = (SELECT MAX(pse2.parent_position_id)
    FROM per_pos_structure_elements pse2
    WHERE pse2.business_group_id = pse.business_group_id
    AND pse2.pos_structure_version_id = pse.pos_structure_version_id
    AND pse2.parent_position_id = pse.parent_position_id
    AND NOT EXISTS (SELECT NULL
    FROM per_pos_structure_elements pse3
    WHERE pse3.business_group_id = pse2.business_group_id
    AND pse3.pos_structure_version_id = pse2.pos_structure_version_id
    AND pse3.subordinate_position_id = pse2.parent_position_id
    )
    )

    • windows7bugs says:

      Excellent Marco!
      As we have mentioned with the home page, such are what we tried, tested and confirmed solutions. We need visitors like you who can always come up with better solutions. Do let us know whether you are interested about sharing knowledge through our blog. We will be more than happy to.

      Thanks once again

      for Windows7bugs
      admin

  2. Dear windows7bugs,
    thanks for the excellent help.
    I am trying to design an audit report on the Positional Hierarchies so that unauthorized changes to the Approval Hierarchies can be detected. I’ve almost completed the task. However there’s a small problem: when I create a version of a hierarchy the top level position is not recorded as an element in the pos_structure_elements table. That’s why, the audit shadow table I created on pos_structure_elements doesn’t also track that information. How would it be possible to do? Thanks.

    • windows7bugs says:

      Hello Ahmed, thanks for your comment. I’m out of station, and soon as I get access to the application, I’ll try to see whether there is a possibility to help your situation.
      regards,

      rajesh

  3. windows7bugs says:

    Hello Ahmed
    We need more information, will you please send us email @ w 7 b u g s at g m a I l

    regards,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: