I need to fetch direct and Indirect reports of a Manager up to 3rd level using Oracle SQL.
I am using Oracle's Hierarchical Query to fetch the reports. But I am getting some weird result.
Suppose I have a Manager whose name is John. Anne reports to John and Rajan reports to Anne.
Now if I run the report for John then I should get all the direct reports if John and Also the reports who report to Anne and Rajan. Rajan has 5 direct reports.
But I am getting only one report of Rajan when I am running the report for John. And getting 5 reports if I run the report for Rajan.
This is happening for Anne also.
Not all the reports are listed of Anne while running for John.
I am not getting the issue.
This is my code :
SELECT DISTINCT PAPF.PERSON_NUMBER AS "EMPLOYEE_ID",PPNF.FULL_NAME AS "EMPLOYEE_NAME",PAAM.MANAGER_FLAG AS "MANAGER",
(SELECT PAPF1.PERSON_NUMBER FROM FUSION.PER_ALL_PEOPLE_F PAPF1,FUSION.PER_ASSIGNMENT_SUPERVISORS_F PASFWHERE PASF.MANAGER_ID = PAPF1.PERSON_ID
AND NVL( TO_CHAR (:AS_ON_DATE, 'YYYY-MM-DD'), to_char( sysdate, 'YYYY-MM-DD')) BETWEEN to_char(PASF.EFFECTIVE_START_DATE, 'YYYY-MM-DD') AND to_char(PASF.EFFECTIVE_END_DATE, 'YYYY-MM-DD')
AND NVL( TO_CHAR (:AS_ON_DATE, 'YYYY-MM-DD'), to_char( sysdate, 'YYYY-MM-DD')) BETWEEN to_char(PAPF1.EFFECTIVE_START_DATE, 'YYYY-MM-DD') AND to_char(PAPF1.EFFECTIVE_END_DATE, 'YYYY-MM-DD')
AND PASF.MANAGER_TYPE = 'LINE_MANAGER'
AND PAAM.ASSIGNMENT_ID = PASF.ASSIGNMENT_ID
AND PAAM.PERSON_ID=PASF.PERSON_ID) AS "SUPERVISOR_ID",
(SELECT PPNF1.FULL_NAME FROM PER_PERSON_NAMES_F PPNF1, PER_ASSIGNMENT_SUPERVISORS_F PASF WHERE PPNF1.PERSON_ID=PASF.MANAGER_ID AND PASF.MANAGER_TYPE= 'LINE_MANAGER' AND PAAM.ASSIGNMENT_ID=PASF.ASSIGNMENT_ID AND NVL( TO_CHAR (:AS_ON_DATE, 'YYYY-MM-DD'), to_char( sysdate, 'YYYY-MM-DD')) BETWEEN to_char(PASF.EFFECTIVE_START_DATE, 'YYYY-MM-DD') AND to_char(PASF.EFFECTIVE_END_DATE, 'YYYY-MM-DD') AND NVL( TO_CHAR (:AS_ON_DATE, 'YYYY-MM-DD'), to_char( sysdate, 'YYYY-MM-DD')) BETWEEN to_char(PPNF1.EFFECTIVE_START_DATE, 'YYYY-MM-DD') AND to_char(PPNF1.EFFECTIVE_END_DATE, 'YYYY-MM-DD') AND PPNF1.NAME_TYPE='GLOBAL')AS "SUPERVISOR_NAME", TO_CHAR(PPS.ORIGINAL_DATE_OF_HIRE,'MM/DD/YYYY') AS "START_DATE",
(SELECT HAPF.ATTRIBUTE2 FROM HR_ALL_POSITIONS_F HAPF WHERE PAAM.POSITION_ID=HAPF.POSITION_ID AND NVL( TO_CHAR (:AS_ON_DATE, 'YYYY-MM-DD'), to_char( sysdate, 'YYYY-MM-DD')) BETWEEN to_char(HAPF.EFFECTIVE_START_DATE, 'YYYY-MM-DD') AND to_char(HAPF.EFFECTIVE_END_DATE, 'YYYY-MM-DD'))AS "OPERATING BU",
(SELECT HLA.LOCATION_NAME FROM HR_LOCATIONS_ALL HLA WHERE PAAM.LOCATION_ID=HLA.LOCATION_ID AND NVL( TO_CHAR (:AS_ON_DATE, 'YYYY-MM-DD'), to_char( sysdate, 'YYYY-MM-DD')) BETWEEN to_char(HLA.EFFECTIVE_START_DATE, 'YYYY-MM-DD') AND to_char(HLA.EFFECTIVE_END_DATE, 'YYYY-MM-DD'))AS "LOCATION_DESCRIPTION", PAAM.ORGANIZATION_ID AS "DEPARTMENT_ID",
(SELECT HOFT.NAME FROM HR_ALL_ORGANIZATION_UNITS HOFT WHERE PAAM.ORGANIZATION_ID=HOFT.ORGANIZATION_ID AND NVL( TO_CHAR (:AS_ON_DATE, 'YYYY-MM-DD'), to_char( sysdate, 'YYYY-MM-DD')) BETWEEN to_char(HOFT.EFFECTIVE_START_DATE, 'YYYY-MM-DD') AND to_char(HOFT.EFFECTIVE_END_DATE, 'YYYY-MM-DD'))AS "DEPARTMENT_DESCRIPTION",
(SELECT HAPF.FTE FROM HR_ALL_POSITIONS_F HAPF WHERE PAAM.POSITION_ID=HAPF.POSITION_ID AND NVL( TO_CHAR (:AS_ON_DATE, 'YYYY-MM-DD'), to_char( sysdate, 'YYYY-MM-DD')) BETWEEN to_char(HAPF.EFFECTIVE_START_DATE, 'YYYY-MM-DD') AND to_char(HAPF.EFFECTIVE_END_DATE, 'YYYY-MM-DD'))AS "HEADCOUNT",
(SELECT PG.NAME FROM PER_GRADES PG WHERE PAAM.GRADE_ID=PG.GRADE_ID AND NVL( TO_CHAR (:AS_ON_DATE, 'YYYY-MM-DD'), to_char( sysdate, 'YYYY-MM-DD')) BETWEEN to_char(PG.EFFECTIVE_START_DATE, 'YYYY-MM-DD') AND to_char(PG.EFFECTIVE_END_DATE, 'YYYY-MM-DD'))AS "BAND",
(SELECT HAPFT.NAME FROM HR_ALL_POSITIONS_F_TL HAPFT WHERE PAAM.POSITION_ID=HAPFT.POSITION_ID AND NVL( TO_CHAR (:AS_ON_DATE, 'YYYY-MM-DD'), to_char( sysdate, 'YYYY-MM-DD')) BETWEEN to_char(HAPFT.EFFECTIVE_START_DATE, 'YYYY-MM-DD') AND to_char(HAPFT.EFFECTIVE_END_DATE, 'YYYY-MM-DD'))AS "BUSINESS_TITLE"
FROM PER_ALL_PEOPLE_F PAPF,PER_ALL_ASSIGNMENTS_M PAAM,
PER_PERIODS_OF_SERVICE PPS,PER_PERSON_NAMES_F PPNF
WHERE PAPF.PERSON_ID=PAAM.PERSON_ID
AND PAAM.PERSON_ID=PPS.PERSON_ID
AND PAAM.PERIOD_OF_SERVICE_ID=PPS.PERIOD_OF_SERVICE_ID
AND PAAM.PERSON_ID=PPNF.PERSON_ID(+)
AND PPNF.NAME_TYPE='GLOBAL'
AND PAAM.SYSTEM_PERSON_TYPE='EMP'
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.PRIMARY_FLAG='Y'
AND PAAM.PRIMARY_ASSIGNMENT_FLAG='Y'
AND PAAM.PRIMARY_WORK_RELATION_FLAG='Y'
AND PAAM.PERSON_ID IN
(SELECT PERSON_ID FROM
(SELECT DISTINCT PASF.PERSON_ID,PASF.MANAGER_ID
FROM PER_ASSIGNMENT_SUPERVISORS_F PASF)TEMP
START WITH PERSON_ID in(:manager_name)
connect by prior PERSON_ID=MANAGER_ID and level <='3')
AND PAAM.ASSIGNMENT_STATUS_TYPE IN ( 'ACTIVE', DECODE(:INCLUDE_INACTIVE, 'Y','INACTIVE','ACTIVE')) AND NVL( TO_CHAR (:AS_ON_DATE, 'YYYY-MM-DD'), to_char( sysdate, 'YYYY-MM-DD')) BETWEEN to_char(PAAM.EFFECTIVE_START_DATE, 'YYYY-MM-DD') AND to_char(PAAM.EFFECTIVE_END_DATE, 'YYYY-MM-DD') AND NVL( TO_CHAR (:AS_ON_DATE, 'YYYY-MM-DD'), to_char( sysdate, 'YYYY-MM-DD')) BETWEEN to_char(PPNF.EFFECTIVE_START_DATE, 'YYYY-MM-DD') AND to_char(PPNF.EFFECTIVE_END_DATE, 'YYYY-MM-DD')AND NVL( TO_CHAR (:AS_ON_DATE, 'YYYY-MM-DD'), to_char( sysdate, 'YYYY-MM-DD')) BETWEEN to_char(PAPF.EFFECTIVE_START_DATE, 'YYYY-MM-DD') AND to_char(PAPF.EFFECTIVE_END_DATE, 'YYYY-MM-DD')
I have tried searching but could not find anything.. Please help.