View: HR_HIER_POS_PEOPLE_V

Product:	PER - Human Resources
Description:	People with position details and place in position hierarchy
Implementation/DBA Data:	APPS.HR_HIER_POS_PEOPLE_V

View Text

SELECT PFT.NAME
, POS.TIME_NORMAL_START
, POS.TIME_NORMAL_FINISH
, PASG.TIME_NORMAL_START
, PASG.TIME_NORMAL_FINISH
, JBT.NAME
, ORG.NAME
, PS.NAME
, PSV.DATE_FROM
, PSV.DATE_TO
, PSV.VERSION_NUMBER
, LOC.LOCATION_CODE
, PASG.ASSIGNMENT_LOCATION
, PASG.FULL_NAME
, PASG.EMPLOYEE_NUMBER
, PASG.KNOWN_AS
, PASG.EMAIL_ADDRESS
, PASG.MAILSTOP
, PASG.WORK_PHONE_NUMBER
, BG.NAME
, PSE.SUBORDINATE_POSITION_ID
, PSE.PARENT_POSITION_ID
FROM HR_HIER_ASG_PEOPLE_V PASG
, HR_LOCATIONS LOC
, HR_ALL_ORGANIZATION_UNITS BG
, HR_ALL_ORGANIZATION_UNITS ORG
, PER_JOBS_TL JBT
, PER_JOBS JOB
, HR_ALL_POSITIONS_F_TL PFT
, HR_ALL_POSITIONS_F POS
, PER_POS_STRUCTURE_ELEMENTS PSE
, PER_POS_STRUCTURE_VERSIONS PSV
, PER_POSITION_STRUCTURES PS
WHERE PS.POSITION_STRUCTURE_ID = PSV.POSITION_STRUCTURE_ID
AND PSV.POS_STRUCTURE_VERSION_ID = PSE.POS_STRUCTURE_VERSION_ID
AND PSE.SUBORDINATE_POSITION_ID = POS.POSITION_ID
AND PS.BUSINESS_GROUP_ID = BG.ORGANIZATION_ID
AND POS.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND PFT.POSITION_ID = POS.POSITION_ID
AND PFT.LANGUAGE = USERENV('LANG')
AND POS.JOB_ID = JOB.JOB_ID
AND JBT.JOB_ID = JOB.JOB_ID
AND JBT.LANGUAGE = USERENV('LANG')
AND POS.LOCATION_ID = LOC.LOCATION_ID (+)
AND POS.POSITION_ID = PASG.POSITION_ID (+)
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN PSV.DATE_FROM
AND NVL(PSV.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN BG.DATE_FROM
AND NVL(BG.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN ORG.DATE_FROM
AND NVL(ORG.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN JOB.DATE_FROM
AND NVL(JOB.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN POS.EFFECTIVE_START_DATE
AND POS.EFFECTIVE_END_DATE UNION SELECT PFT.NAME
, POS.TIME_NORMAL_START
, POS.TIME_NORMAL_FINISH
, PASG.TIME_NORMAL_START
, PASG.TIME_NORMAL_FINISH
, JBT.NAME
, ORG.NAME
, PS.NAME
, PSV.DATE_FROM
, PSV.DATE_TO
, PSV.VERSION_NUMBER
, LOC.LOCATION_CODE
, PASG.ASSIGNMENT_LOCATION
, PASG.FULL_NAME
, PASG.EMPLOYEE_NUMBER
, PASG.KNOWN_AS
, PASG.EMAIL_ADDRESS
, PASG.MAILSTOP
, PASG.WORK_PHONE_NUMBER
, BG.NAME
, PSE.PARENT_POSITION_ID
, 0
FROM HR_HIER_ASG_PEOPLE_V PASG
, HR_LOCATIONS LOC
, HR_ALL_ORGANIZATION_UNITS BG
, HR_ALL_ORGANIZATION_UNITS ORG
, PER_JOBS_TL JBT
, PER_JOBS JOB
, HR_ALL_POSITIONS_F_TL PFT
, PER_ALL_POSITIONS POS
, PER_POS_STRUCTURE_ELEMENTS PSE
, PER_POS_STRUCTURE_VERSIONS PSV
, PER_POSITION_STRUCTURES PS
WHERE PS.POSITION_STRUCTURE_ID = PSV.POSITION_STRUCTURE_ID
AND PSV.POS_STRUCTURE_VERSION_ID = PSE.POS_STRUCTURE_VERSION_ID
AND PSE.PARENT_POSITION_ID = POS.POSITION_ID
AND PS.BUSINESS_GROUP_ID = BG.ORGANIZATION_ID
AND POS.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND PFT.POSITION_ID = POS.POSITION_ID
AND PFT.LANGUAGE = USERENV('LANG')
AND POS.JOB_ID = JOB.JOB_ID
AND JBT.JOB_ID = JOB.JOB_ID
AND JBT.LANGUAGE = USERENV('LANG')
AND POS.LOCATION_ID = LOC.LOCATION_ID (+)
AND POS.POSITION_ID = PASG.POSITION_ID (+)
AND NOT EXISTS (SELECT NULL
FROM PER_POS_STRUCTURE_ELEMENTS PSE2
WHERE PSE.PARENT_POSITION_ID = PSE2.SUBORDINATE_POSITION_ID)
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN PSV.DATE_FROM
AND NVL(PSV.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN BG.DATE_FROM
AND NVL(BG.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN ORG.DATE_FROM
AND NVL(ORG.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN JOB.DATE_FROM
AND NVL(JOB.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN POS.DATE_EFFECTIVE
AND NVL(POS.DATE_END
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')) WITH READ ONLY

Columns