Content

Requirement:

1) Report should display employees belonging to the departments of the selected organization type.
2) Report should also display employees belonging to departments below the selected departments as per organization hierarchy.

Scenario: Suppose the department of the employee is DEPARTMENT4.3 then the report should fetch the data of the employee whose department is DEPARTMENT4.3 along with the employees who belong to  DEPARTMENT4.3.1,  DEPARTMENT4.3.2,  DEPARTMENT4.3.3,  DEPARTMENT4.3.3.1.

Organization tree hierarchy:

NODE1:LEGAL EMPLOYER(ROOT)

NODE2: DEPARTMENT1

NODE3:          DEPARTMENT1.1

NODE4:                          DEPARTMENT1.1.1

                                      DEPARTMENT1.1.2

NODE3:           DEPARTMENT1.2

NODE2: DEPARTMENT2

NODE2: DEPARTMENT3

NODE3:           DEPARTMENT3.1

                        DEPARTMENT3.2

NODE2: DEPARTMENT4

NODE3:           DEPARTMENT4.1

                        DEPARTMENT4.2

                        DEPARTMENT4.3

NODE4:                         DEPARTMENT4.3.1

                                      DEPARTMENT4.3.2

                                      DEPARTMENT4.3.3

NODE5:                                          DEPARTMENT4.3.3.1

PS: I am able to achieve the first requirement by using attached SQL code but I need help to achieve the second requirement. I am not able to get any lead how should I accomplish the second requirement like does it require code modification or can be handled in template( currently using RTF template of excel type).

Code Snippet

SELECT             
                   PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
                   PPNF.FULL_NAME EMPLOYEE_NAME, 
                   HAOU.NAME LEGAL_EMPLOYER,
                   PAAM.ASSIGNMENT_NUMBER,
                   PAAM.PRIMARY_FLAG,
                   HL.LOCATION_NAME,
                   PD.ORGANIZATION_ID,
                   PD.NAME DEPT_NAME,
                   TREE.DEPTH,
                    
                   (SELECT PDS.NAME
                    FROM PER_ORG_TREE_NODE_CF CF,
                    HR_ALL_ORGANIZATION_UNITS PDS
                    WHERE PDS.ORGANIZATION_ID = CF.DEP31_PK1_VALUE
                    -- AND TREE_CODE like 'CCS_HR_STRUCTURE_HIERARCHY'
                    AND DEP25_PK1_VALUE = TREE.PK1_START_VALUE
                    and rownum=1
                    ) LEVEL_1,
                    
                    (SELECT PDS.NAME
                    FROM PER_ORG_TREE_NODE_CF CF,
                    HR_ALL_ORGANIZATION_UNITS PDS
                    WHERE PDS.ORGANIZATION_ID = CF.DEP30_PK1_VALUE
                    -- AND TREE_CODE = 'CCS_HR_STRUCTURE_HIERARCHY'
                    AND DEP25_PK1_VALUE = TREE.PK1_START_VALUE
                    AND DISTANCE >= 1
                    and rownum=1
                    ) LEVEL_2,
                    
                    (SELECT PDS.NAME
                    FROM PER_ORG_TREE_NODE_CF CF,
                    HR_ALL_ORGANIZATION_UNITS PDS
                    WHERE PDS.ORGANIZATION_ID = CF.DEP29_PK1_VALUE
                    -- AND TREE.TREE_CODE = 'CCS_HR_STRUCTURE_HIERARCHY'
                    AND DEP25_PK1_VALUE = TREE.PK1_START_VALUE
                    AND DISTANCE >= 2
                    and rownum=1
                    ) LEVEL_3,
                    
                   (SELECT PDS.NAME
                   FROM PER_ORG_TREE_NODE_CF CF,
                   HR_ALL_ORGANIZATION_UNITS PDS
                   WHERE PDS.ORGANIZATION_ID = CF.DEP28_PK1_VALUE
                   -- AND TREE_CODE = 'CCS_HR_STRUCTURE_HIERARCHY'
                   AND DEP25_PK1_VALUE = TREE.PK1_START_VALUE
                   AND DISTANCE >= 3
                   and rownum=1
                   ) LEVEL_4,
                   
                   (SELECT PDS.NAME
                   FROM PER_ORG_TREE_NODE_CF CF,
                   HR_ALL_ORGANIZATION_UNITS PDS
                   WHERE PDS.ORGANIZATION_ID = CF.DEP27_PK1_VALUE
                   -- AND TREE_CODE = 'CCS_HR_STRUCTURE_HIERARCHY'
                   AND DEP25_PK1_VALUE = TREE.PK1_START_VALUE
                   AND DISTANCE >= 4
                   and rownum=1
                   ) LEVEL_5,
                   
                   (SELECT PDS.NAME
                   FROM PER_ORG_TREE_NODE_CF CF,
                   HR_ALL_ORGANIZATION_UNITS PDS
                   WHERE PDS.ORGANIZATION_ID = CF.DEP26_PK1_VALUE
                   -- AND TREE_CODE = 'CCS_HR_STRUCTURE_HIERARCHY'
                   AND DEP25_PK1_VALUE = TREE.PK1_START_VALUE
                   AND DISTANCE >= 5
                   and rownum=1
                   ) LEVEL_6,
                   
                   (SELECT PDS.NAME
                   FROM PER_ORG_TREE_NODE_CF CF,
                   HR_ALL_ORGANIZATION_UNITS PDS
                   WHERE PDS.ORGANIZATION_ID = CF.DEP25_PK1_VALUE
                   -- AND TREE_CODE = 'CCS_HR_STRUCTURE_HIERARCHY'
                   AND DEP25_PK1_VALUE = TREE.PK1_START_VALUE
                   AND DISTANCE >= 6
                   and rownum=1
                   ) LEVEL_7,
                   
                   (SELECT PDS.NAME
                   FROM PER_ORG_TREE_NODE_CF CF,
                   HR_ALL_ORGANIZATION_UNITS PDS
                   WHERE PDS.ORGANIZATION_ID = CF.DEP25_PK1_VALUE
                   -- AND TREE_CODE = 'CCS_HR_STRUCTURE_HIERARCHY'
                   AND DEP25_PK1_VALUE = TREE.PK1_START_VALUE
                   AND DISTANCE >= 7
                   and rownum=1
                   ) LEVEL_8,
                   
                   (SELECT PDS.NAME
                   FROM PER_ORG_TREE_NODE_CF CF,
                   HR_ALL_ORGANIZATION_UNITS PDS
                   WHERE PDS.ORGANIZATION_ID = CF.DEP25_PK1_VALUE
                   -- AND TREE_CODE = 'CCS_HR_STRUCTURE_HIERARCHY'
                   AND DEP25_PK1_VALUE = TREE.PK1_START_VALUE
                   AND DISTANCE >= 8
                   and rownum=1
                   ) LEVEL_9,
                   
                   PD.ATTRIBUTE7 ORGANIZATION_TYPE,
                   PJ.NAME JOB_NAME,
                   POS.NAME POSITION_NAME  ,
                   PG.NAME GRADE_NAME,
                   
                   (SELECT NAME 
                   FROM PER_GRADE_LADDERS_F_TL 
                   WHERE GRADE_LADDER_ID = PAAM.GRADE_LADDER_PGM_ID
                   AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
                   ) GRADE_LADDER,
                   
                   (SELECT PGST.NAME 
                   FROM 
                   PER_ASSIGN_GRADE_STEPS_F P1,
                   PER_GRADE_STEPS_F_TL PGST
                   WHERE P1.GRADE_STEP_ID = PGST.GRADE_STEP_ID
                   AND P1.ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID
                   AND TRUNC(SYSDATE) BETWEEN PGST.EFFECTIVE_START_DATE AND PGST.EFFECTIVE_END_DATE
                   AND TRUNC(SYSDATE) BETWEEN P1.EFFECTIVE_START_DATE AND P1.EFFECTIVE_END_DATE
                   ) GRADE_STEP
                   
FROM
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_PERIODS_OF_SERVICE PPS,
HR_ALL_ORGANIZATION_UNITS HAOU,
HR_ALL_ORGANIZATION_UNITS PD,
PER_JOBS PJ,
PER_GRADES PG,
HR_LOCATIONS HL,
HR_ALL_POSITIONS_F_TL POS,
PER_ORG_TREE_NODE TREE
WHERE 1=1
AND PAPF.PERSON_ID = PPNF.PERSON_ID
AND   PAPF.PERSON_ID = PAAM.PERSON_ID
AND   PAPF.PERSON_ID = PPS.PERSON_ID
AND   PAAM.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID
AND   PD.ORGANIZATION_ID(+) = PAAM.ORGANIZATION_ID
AND  PAAM.LEGAL_ENTITY_ID =HAOU.ORGANIZATION_ID(+)
AND  PAAM.JOB_ID = PJ.JOB_ID(+)
AND  PG.GRADE_ID(+)= PAAM.GRADE_ID
AND  HL.LOCATION_ID(+)= PAAM.LOCATION_ID
AND  POS.POSITION_ID(+) = PAAM.POSITION_ID
AND  TREE.PK1_START_VALUE = PD.ORGANIZATION_ID
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PG.EFFECTIVE_START_DATE(+)  AND PG.EFFECTIVE_END_DATE(+) 
AND TRUNC(SYSDATE) BETWEEN PJ.EFFECTIVE_START_DATE(+)  AND PJ.EFFECTIVE_END_DATE(+) 
AND TRUNC(SYSDATE) BETWEEN POS.EFFECTIVE_START_DATE(+)  AND POS.EFFECTIVE_END_DATE(+) 
AND PD.ATTRIBUTE7 IN NVL(:P_ORGANIZATION_TYPE,PD.ATTRIBUTE7)/* DFF in PAAM table*/
AND TRIM(TREE_CODE) ='CCS_HR_STRUCTURE_HIERARCHY' 
AND PAAM.ASSIGNMENT_TYPE IN ('E','C')
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PPNF.NAME_TYPE='GLOBAL'
ORDER BY  TREE.DEPTH