HWR_COMBO_ORG

Details

Schema: FUSION
Object owner: HWR
Object type: VIEW

Columns

Name
MGR_ID ORG_NAME PRFL_ID CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE ALUD A_END_DATE A_START_DATE

Name

MGR_ID

ORG_NAME

PRFL_ID

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

ALUD

A_END_DATE

A_START_DATE

Query

SQL_Statement
SELECT IUH.MGR_ID, IUH.ORG_NAME, PROFILEB_2.PRFL_ID, IUH.CREATED_BY, IUH.CREATION_DATE, IUH.LAST_UPDATED_BY, IUH.LAST_UPDATE_DATE, IUH.ALUD, IUH.A_END_DATE, IUH.A_START_DATE FROM ( SELECT SP.CREATED_BY, SP.CREATION_DATE, SP.LAST_UPDATED_BY, SP.LAST_UPDATE_DATE, SP.LAST_UPDATE_LOGIN, SP.MGR_ID, SP.ORG_NAME, SP.SOURCE_ID, SA.MANAGER_ID, TO_CHAR(SA.PERSON_ID) AS PID, SA.ALUD, SA.EFFECTIVE_END_DATE AS A_START_DATE, SA.EFFECTIVE_START_DATE AS A_END_DATE FROM ( SELECT DISTINCT PROFILEB_1.CREATED_BY, PROFILEB_1.CREATION_DATE, PROFILEB_1.LAST_UPDATED_BY, PROFILEB_1.LAST_UPDATE_DATE, PROFILEB_1.LAST_UPDATE_LOGIN, PROFILEB_1.PRFL_ID AS MGR_ID, NVL2(PROFILEB_1.FIRST_NAME,PROFILEB_1.FIRST_NAME \|\| ' ','') \|\| NVL2(PROFILEB_1.MIDDLE_NAME,PROFILEB_1.MIDDLE_NAME \|\| ' ','') \|\| NVL2(PROFILEB_1.LAST_NAME,PROFILEB_1.LAST_NAME \|\| ' ','') AS ORG_NAME, PROFILEB_1.SOURCE_ID FROM HWR_PER_PROFILE_B PROFILEB_1 INNER JOIN ( SELECT SRCB.SOURCE_ID FROM HWR_SRC_SOURCE_B SRCB INNER JOIN HWR_SRC_SRCTYPE_B STB ON SRCB.SOURCE_TYPE_ID = STB.SOURCE_TYPE_ID WHERE STB.SOURCE_TYPE_KEY = 'Fusion' ) S ON S.SOURCE_ID = PROFILEB_1.SOURCE_ID WHERE PROFILEB_1.IS_ACTIVE_FLAG = 'Y' ) SP INNER JOIN ( SELECT DISTINCT ASSIGNMENTSUPERVISORDPEO.MANAGER_ID, ASSIGNMENTSUPERVISORDPEO.PERSON_ID, ASSIGNMENTSUPERVISORDPEO.LAST_UPDATE_DATE AS ALUD, ASSIGNMENTSUPERVISORDPEO.EFFECTIVE_END_DATE, ASSIGNMENTSUPERVISORDPEO.EFFECTIVE_START_DATE FROM PER_ASSIGNMENT_SUPERVISORS_F ASSIGNMENTSUPERVISORDPEO LEFT JOIN PER_ALL_ASSIGNMENTS_M ASSIGNMENTDPEO ON ASSIGNMENTSUPERVISORDPEO.ASSIGNMENT_ID = ASSIGNMENTDPEO.ASSIGNMENT_ID WHERE TRUNC(SYSDATE) BETWEEN ASSIGNMENTSUPERVISORDPEO.EFFECTIVE_START_DATE AND ASSIGNMENTSUPERVISORDPEO.EFFECTIVE_END_DATE AND ASSIGNMENTSUPERVISORDPEO.PRIMARY_FLAG = 'Y' AND ASSIGNMENTSUPERVISORDPEO.MANAGER_TYPE = 'LINE_MANAGER' AND ASSIGNMENTDPEO.PRIMARY_FLAG = 'Y' AND ASSIGNMENTDPEO.ASSIGNMENT_TYPE IN ( 'E', 'C', 'N' ) ) SA ON SP.MGR_ID = SA.MANAGER_ID ) IUH INNER JOIN HWR_PER_PROFILE_B PROFILEB_2 ON IUH.PID = PROFILEB_2.PRFL_ID AND IUH.SOURCE_ID = PROFILEB_2.SOURCE_ID WHERE PROFILEB_2.IS_ACTIVE_FLAG = 'Y'

SQL_Statement

SELECT

IUH.MGR_ID,

IUH.ORG_NAME,

PROFILEB_2.PRFL_ID,

IUH.CREATED_BY,

IUH.CREATION_DATE,

IUH.LAST_UPDATED_BY,

IUH.LAST_UPDATE_DATE,

IUH.ALUD,

IUH.A_END_DATE,

IUH.A_START_DATE

FROM

(

SELECT

SP.CREATED_BY,

SP.CREATION_DATE,

SP.LAST_UPDATED_BY,

SP.LAST_UPDATE_DATE,

SP.LAST_UPDATE_LOGIN,

SP.MGR_ID,

SP.ORG_NAME,

SP.SOURCE_ID,

SA.MANAGER_ID,

TO_CHAR(SA.PERSON_ID) AS PID,

SA.ALUD,

SA.EFFECTIVE_END_DATE AS A_START_DATE,

SA.EFFECTIVE_START_DATE AS A_END_DATE

FROM

(

SELECT DISTINCT

PROFILEB_1.CREATED_BY,

PROFILEB_1.CREATION_DATE,

PROFILEB_1.LAST_UPDATED_BY,

PROFILEB_1.LAST_UPDATE_DATE,

PROFILEB_1.LAST_UPDATE_LOGIN,

PROFILEB_1.PRFL_ID AS MGR_ID,

NVL2(PROFILEB_1.FIRST_NAME,PROFILEB_1.FIRST_NAME

|| ' ','')

|| NVL2(PROFILEB_1.MIDDLE_NAME,PROFILEB_1.MIDDLE_NAME

|| ' ','')

|| NVL2(PROFILEB_1.LAST_NAME,PROFILEB_1.LAST_NAME

|| ' ','') AS ORG_NAME,

PROFILEB_1.SOURCE_ID

FROM

HWR_PER_PROFILE_B PROFILEB_1

INNER JOIN (

SELECT

SRCB.SOURCE_ID

FROM

HWR_SRC_SOURCE_B SRCB

INNER JOIN HWR_SRC_SRCTYPE_B STB

ON SRCB.SOURCE_TYPE_ID = STB.SOURCE_TYPE_ID

WHERE

STB.SOURCE_TYPE_KEY = 'Fusion'

) S

ON S.SOURCE_ID = PROFILEB_1.SOURCE_ID

WHERE

PROFILEB_1.IS_ACTIVE_FLAG = 'Y'

) SP

INNER JOIN (

SELECT DISTINCT

ASSIGNMENTSUPERVISORDPEO.MANAGER_ID,

ASSIGNMENTSUPERVISORDPEO.PERSON_ID,

ASSIGNMENTSUPERVISORDPEO.LAST_UPDATE_DATE AS ALUD,

ASSIGNMENTSUPERVISORDPEO.EFFECTIVE_END_DATE,

ASSIGNMENTSUPERVISORDPEO.EFFECTIVE_START_DATE

FROM

PER_ASSIGNMENT_SUPERVISORS_F ASSIGNMENTSUPERVISORDPEO

LEFT JOIN PER_ALL_ASSIGNMENTS_M ASSIGNMENTDPEO ON ASSIGNMENTSUPERVISORDPEO.ASSIGNMENT_ID = ASSIGNMENTDPEO.ASSIGNMENT_ID

WHERE

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

AND ASSIGNMENTSUPERVISORDPEO.PRIMARY_FLAG = 'Y'

AND ASSIGNMENTSUPERVISORDPEO.MANAGER_TYPE = 'LINE_MANAGER'

AND ASSIGNMENTDPEO.PRIMARY_FLAG = 'Y'

AND ASSIGNMENTDPEO.ASSIGNMENT_TYPE IN (

'E',

'C',

'N'

)

) SA ON SP.MGR_ID = SA.MANAGER_ID

) IUH

INNER JOIN HWR_PER_PROFILE_B PROFILEB_2 ON

IUH.PID = PROFILEB_2.PRFL_ID

AND IUH.SOURCE_ID = PROFILEB_2.SOURCE_ID

WHERE

PROFILEB_2.IS_ACTIVE_FLAG = 'Y'