Поиск прямых и косвенных репортеров менеджера с использованием Oracle SQL
Мне нужно получать прямые и косвенные отчеты менеджера до 3-го уровня, используя Oracle SQL.
Я использую иерархический запрос Oracle для получения отчетов. Но я получаю странный результат.
Предположим, у меня есть менеджер, которого зовут Джон. Энн отчитывается перед Джоном, а Раджан отчитывается перед Энн.
Теперь, если я запускаю отчет для Джона, я должен получить все прямые отчеты, если Джон, а также отчеты, которые отчитываются перед Энн и Раджаном. У Раджана есть 5 прямых сообщений.
Но я получаю только одно сообщение о Раджане, когда работаю над отчетом для Джона. И получаю 5 отчетов, если я запускаю отчет для Раджана.
Это происходит и с Анной.
Не все отчеты перечислены Энн во время гонки за Джона.
Я не понимаю проблему.
Это мой код:
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')
Я пытался найти, но ничего не смог найти.. Пожалуйста, помогите.