HCM | SQL query to get details of the employees belonging to departments below the selected departme
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
Best Answer
-
Hi @User_Z86UI ,
Seems this is related to HCM product. Actually this forum is for Oracle Analytics Cloud and Server product, so please post your question/request in the proper forum. Try below forum and see if that helps.
https://community.oracle.com/customerconnect/categories/hcm-reporting-and-analytics-for-hcm
Thank you!
1
Answers
-
Dear All, Pls give me the complete (employee/person/worker) query to get all the details for these employees load through HDL worker.dat (worker,person name,person email,work relationship,work terms,assignments) please help me. thank you
0