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