Introduction
This Post is about to Get Employee Supervisor Relationships Query in oracle R12.
Query to Get Employee Supervisor Relationships.
SELECT
DISTINCT
LPAD(‘ ‘,5*(LEVEL-1)) || PPF.FULL_NAME,
PERA.SUPERVISOR_ID,
PERA.PERSON_ID,
PER_JOBS.NAME JobName,
LEVEL,
SYS_CONNECT_BY_PATH(pera.person_id, ‘/’) Path
FROM PER_ASSIGNMENTS_F PERA,PER_JOBS,PER_ALL_PEOPLE_F PPF
WHERE PER_JOBS.JOB_ID = PERA.JOB_ID
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
AND PPF.PERSON_ID = PERA.PERSON_ID
AND EXISTS
(SELECT ‘1.’
FROM PER_PEOPLE_F PERF, PER_ASSIGNMENTS_F PERA1
WHERE TRUNC(SYSDATE) BETWEEN PERF.EFFECTIVE_START_DATE AND PERF.EFFECTIVE_END_DATE
AND PERF.PERSON_ID = PERA.SUPERVISOR_ID
AND PERA1.PERSON_ID = PERF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PERA1.EFFECTIVE_START_DATE AND PERA1.EFFECTIVE_END_DATE
AND PERA1.PRIMARY_FLAG = ‘Y’
AND PERA1.ASSIGNMENT_TYPE = ‘E’
AND EXISTS
(SELECT ‘1’ FROM PER_PERSON_TYPES PPT WHERE PPT.SYSTEM_PERSON_TYPE IN (‘EMP’,’EMP_APL’) AND PPT.PERSON_TYPE_ID = PERF.PERSON_TYPE_ID)
)
START WITH PERA.PERSON_ID = 6247
AND TRUNC(SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE AND PERA.EFFECTIVE_END_DATE
AND PERA.PRIMARY_FLAG = ‘Y’
AND PERA.ASSIGNMENT_TYPE = ‘E’
CONNECT BY NOCYCLE PRIOR PERA.PERSON_ID = PERA.SUPERVISOR_ID
AND TRUNC(SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE AND PERA.EFFECTIVE_END_DATE
AND PERA.PRIMARY_FLAG = ‘Y’
AND PERA.ASSIGNMENT_TYPE = ‘E’
ORDER BY PATH
What we expect in the script.
This script helps us to comprehend how to Get Employee Supervisor Relationships Query. A couple of tables which is being used in the scripts are PER_PEOPLE_F PERF, PER_ASSIGNMENTS_F PERA1 etc.
Summary
This Post described the script to Get Employee Supervisor Relationships Query in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
