HWR_ORG_MGR_JOBTITLES

Details

Schema: FUSION
Object owner: HWR
Object type: VIEW

Columns

Name
MANAGER_ASSIGNMENT_ID ASSIGNMENT_ID PERSON_ID MANAGER_ID MANAGER_LEVEL EFFECTIVE_END_DATE EFFECTIVE_START_DATE MANAGER_TYPE BUSINESS_GROUP_ID EFFECTIVE_LATEST_CHANGE EFFECTIVE_SEQUENCE LAST_UPDATE_DATE JOB_ID NAME

Name

MANAGER_ASSIGNMENT_ID

ASSIGNMENT_ID

PERSON_ID

MANAGER_ID

MANAGER_LEVEL

EFFECTIVE_END_DATE

EFFECTIVE_START_DATE

MANAGER_TYPE

BUSINESS_GROUP_ID

EFFECTIVE_LATEST_CHANGE

EFFECTIVE_SEQUENCE

LAST_UPDATE_DATE

JOB_ID

NAME

Query

SQL_Statement
SELECT HRC.MANAGER_ASSIGNMENT_ID, HRC.ASSIGNMENT_ID, HRC.PERSON_ID, HRC.MANAGER_ID, HRC.MANAGER_LEVEL, HRC.EFFECTIVE_END_DATE, HRC.EFFECTIVE_START_DATE, HRC.MANAGER_TYPE, HRC.BUSINESS_GROUP_ID, ASSG.EFFECTIVE_LATEST_CHANGE, ASSG.EFFECTIVE_SEQUENCE,ASSG.LAST_UPDATE_DATE, JOBS.JOB_ID,JOBS.NAME FROM FUSION.PER_MANAGER_HRCHY_DN HRC, PER_ALL_ASSIGNMENTS_M ASSG, PER_JOBS_F_TL JOBS WHERE HRC.PERSON_ID = ASSG.PERSON_ID AND HRC.ASSIGNMENT_ID = ASSG.ASSIGNMENT_ID AND ASSG.JOB_ID = JOBS.JOB_ID AND TRUNC(SYSDATE) BETWEEN HRC.EFFECTIVE_START_DATE AND HRC.EFFECTIVE_END_DATE AND HRC.MANAGER_LEVEL IN (1,2) AND HRC.MANAGER_TYPE = 'LINE_MANAGER' AND ASSG.EFFECTIVE_LATEST_CHANGE = 'Y' AND ASSG.EFFECTIVE_SEQUENCE=(SELECT MAX(EFFECTIVE_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M A WHERE A.ASSIGNMENT_ID=ASSG.ASSIGNMENT_ID) AND JOBS.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG') UNION SELECT HRC.MANAGER_ASSIGNMENT_ID, HRC.MANAGER_ASSIGNMENT_ID AS ASSIGNMENT_ID, HRC.MANAGER_ID AS PERSON_ID, HRC.MANAGER_ID, HRC.MANAGER_LEVEL, NULL, NULL, HRC.MANAGER_TYPE, HRC.BUSINESS_GROUP_ID, ASSG.EFFECTIVE_LATEST_CHANGE, ASSG.EFFECTIVE_SEQUENCE,ASSG.LAST_UPDATE_DATE, JOBS.JOB_ID,JOBS.NAME FROM FUSION.PER_MANAGER_HRCHY_DN HRC, PER_ALL_ASSIGNMENTS_M ASSG, PER_JOBS_F_TL JOBS WHERE HRC.MANAGER_ID = ASSG.PERSON_ID AND HRC.MANAGER_ASSIGNMENT_ID = ASSG.ASSIGNMENT_ID AND ASSG.JOB_ID = JOBS.JOB_ID AND TRUNC(SYSDATE) BETWEEN HRC.EFFECTIVE_START_DATE AND HRC.EFFECTIVE_END_DATE AND HRC.MANAGER_LEVEL IN (1) AND HRC.MANAGER_TYPE = 'LINE_MANAGER' AND ASSG.EFFECTIVE_LATEST_CHANGE = 'Y' AND ASSG.EFFECTIVE_SEQUENCE=(SELECT MAX(EFFECTIVE_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M A WHERE A.ASSIGNMENT_ID=ASSG.ASSIGNMENT_ID) AND JOBS.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG') UNION SELECT ASSG.ASSIGNMENT_ID AS MANAGER_ASSIGNMENT_ID, ASSG.ASSIGNMENT_ID, ASSG.PERSON_ID, ASSG.PERSON_ID AS MANAGER_ID, 0, NULL, NULL, NULL, NULL, ASSG.EFFECTIVE_LATEST_CHANGE, ASSG.EFFECTIVE_SEQUENCE,ASSG.LAST_UPDATE_DATE, JOBS.JOB_ID,JOBS.NAME FROM PER_ALL_ASSIGNMENTS_M ASSG, PER_JOBS_F_TL JOBS WHERE ASSG.JOB_ID = JOBS.JOB_ID AND ASSG.EFFECTIVE_LATEST_CHANGE = 'Y' AND ASSG.EFFECTIVE_SEQUENCE=(SELECT MAX(EFFECTIVE_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M A WHERE A.ASSIGNMENT_ID=ASSG.ASSIGNMENT_ID) AND ASSG.PERSON_ID NOT IN(SELECT DISTINCT MANAGER_ID FROM PER_MANAGER_HRCHY_DN) AND TRUNC(SYSDATE) BETWEEN ASSG.EFFECTIVE_START_DATE AND ASSG.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN JOBS.EFFECTIVE_START_DATE AND JOBS.EFFECTIVE_END_DATE AND ASSG.PRIMARY_FLAG='Y' AND ASSG.ASSIGNMENT_STATUS_TYPE='ACTIVE' AND JOBS.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG')

SQL_Statement

SELECT

HRC.MANAGER_ASSIGNMENT_ID, HRC.ASSIGNMENT_ID,

HRC.PERSON_ID, HRC.MANAGER_ID,

HRC.MANAGER_LEVEL, HRC.EFFECTIVE_END_DATE,

HRC.EFFECTIVE_START_DATE, HRC.MANAGER_TYPE,

HRC.BUSINESS_GROUP_ID, ASSG.EFFECTIVE_LATEST_CHANGE,

ASSG.EFFECTIVE_SEQUENCE,ASSG.LAST_UPDATE_DATE,

JOBS.JOB_ID,JOBS.NAME

FROM

FUSION.PER_MANAGER_HRCHY_DN HRC,

PER_ALL_ASSIGNMENTS_M ASSG,

PER_JOBS_F_TL JOBS

WHERE

HRC.PERSON_ID = ASSG.PERSON_ID

AND HRC.ASSIGNMENT_ID = ASSG.ASSIGNMENT_ID

AND ASSG.JOB_ID = JOBS.JOB_ID

AND TRUNC(SYSDATE) BETWEEN HRC.EFFECTIVE_START_DATE AND HRC.EFFECTIVE_END_DATE

AND HRC.MANAGER_LEVEL IN (1,2)

AND HRC.MANAGER_TYPE = 'LINE_MANAGER'

AND ASSG.EFFECTIVE_LATEST_CHANGE = 'Y' AND

ASSG.EFFECTIVE_SEQUENCE=(SELECT MAX(EFFECTIVE_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M A WHERE A.ASSIGNMENT_ID=ASSG.ASSIGNMENT_ID)

AND JOBS.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG')

UNION

SELECT

HRC.MANAGER_ASSIGNMENT_ID, HRC.MANAGER_ASSIGNMENT_ID AS ASSIGNMENT_ID,

HRC.MANAGER_ID AS PERSON_ID, HRC.MANAGER_ID,

HRC.MANAGER_LEVEL, NULL,

NULL, HRC.MANAGER_TYPE,

HRC.BUSINESS_GROUP_ID, ASSG.EFFECTIVE_LATEST_CHANGE,

ASSG.EFFECTIVE_SEQUENCE,ASSG.LAST_UPDATE_DATE,

JOBS.JOB_ID,JOBS.NAME

FROM

FUSION.PER_MANAGER_HRCHY_DN HRC,

PER_ALL_ASSIGNMENTS_M ASSG,

PER_JOBS_F_TL JOBS

WHERE

HRC.MANAGER_ID = ASSG.PERSON_ID

AND HRC.MANAGER_ASSIGNMENT_ID = ASSG.ASSIGNMENT_ID

AND ASSG.JOB_ID = JOBS.JOB_ID

AND TRUNC(SYSDATE) BETWEEN HRC.EFFECTIVE_START_DATE AND HRC.EFFECTIVE_END_DATE

AND HRC.MANAGER_LEVEL IN (1)

AND HRC.MANAGER_TYPE = 'LINE_MANAGER'

AND ASSG.EFFECTIVE_LATEST_CHANGE = 'Y' AND

ASSG.EFFECTIVE_SEQUENCE=(SELECT MAX(EFFECTIVE_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M A WHERE A.ASSIGNMENT_ID=ASSG.ASSIGNMENT_ID)

AND JOBS.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG')

UNION

SELECT ASSG.ASSIGNMENT_ID AS MANAGER_ASSIGNMENT_ID, ASSG.ASSIGNMENT_ID,

ASSG.PERSON_ID, ASSG.PERSON_ID AS MANAGER_ID,

0, NULL,

NULL, NULL,

NULL, ASSG.EFFECTIVE_LATEST_CHANGE,

ASSG.EFFECTIVE_SEQUENCE,ASSG.LAST_UPDATE_DATE,

JOBS.JOB_ID,JOBS.NAME

FROM

PER_ALL_ASSIGNMENTS_M ASSG,

PER_JOBS_F_TL JOBS

WHERE

ASSG.JOB_ID = JOBS.JOB_ID

AND ASSG.EFFECTIVE_LATEST_CHANGE = 'Y'

AND ASSG.EFFECTIVE_SEQUENCE=(SELECT MAX(EFFECTIVE_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M A WHERE A.ASSIGNMENT_ID=ASSG.ASSIGNMENT_ID)

AND ASSG.PERSON_ID NOT IN(SELECT DISTINCT MANAGER_ID FROM PER_MANAGER_HRCHY_DN)

AND TRUNC(SYSDATE) BETWEEN ASSG.EFFECTIVE_START_DATE AND ASSG.EFFECTIVE_END_DATE

AND TRUNC(SYSDATE) BETWEEN JOBS.EFFECTIVE_START_DATE AND JOBS.EFFECTIVE_END_DATE

AND ASSG.PRIMARY_FLAG='Y' AND ASSG.ASSIGNMENT_STATUS_TYPE='ACTIVE'

AND JOBS.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG')