View: HR_HIER_ORG_PEOPLE_V

Product:	PER - Human Resources
Description:
Implementation/DBA Data:	APPS.HR_HIER_ORG_PEOPLE_V

View Text

SELECT HOU1.NAME
, LOC.LOCATION_CODE
, HOU1.INTERNAL_ADDRESS_LINE
, OS.NAME
, OSV.VERSION_NUMBER
, OSV.DATE_FROM
, OSV.DATE_TO
, HR_GENERAL.DECODE_LOOKUP('ORG_TYPE'
, HOU1.TYPE)
, PASG.FULL_NAME
, PASG.EMPLOYEE_NUMBER
, PASG.KNOWN_AS
, PASG.EMAIL_ADDRESS
, PASG.MAILSTOP
, PASG.WORK_PHONE_NUMBER
, BG.NAME
, OSE.ORGANIZATION_ID_CHILD
, OSE.ORGANIZATION_ID_PARENT
, OSV.ORG_STRUCTURE_VERSION_ID
FROM HR_LOCATIONS LOC
, HR_HIER_ASG_PEOPLE_V PASG
, HR_ALL_ORGANIZATION_UNITS BG
, HR_ALL_ORGANIZATION_UNITS HOU1
, PER_ORG_STRUCTURE_ELEMENTS OSE
, PER_ORG_STRUCTURE_VERSIONS OSV
, PER_ORGANIZATION_STRUCTURES OS
WHERE OS.ORGANIZATION_STRUCTURE_ID = OSV.ORGANIZATION_STRUCTURE_ID
AND PASG.MANAGER_FLAG(+)='Y'
AND OSV.ORG_STRUCTURE_VERSION_ID = OSE.ORG_STRUCTURE_VERSION_ID
AND OSE.ORGANIZATION_ID_CHILD = HOU1.ORGANIZATION_ID
AND OSE.ORGANIZATION_ID_CHILD = PASG.ORGANIZATION_ID(+)
AND HOU1.LOCATION_ID = LOC.LOCATION_ID(+)
AND OS.BUSINESS_GROUP_ID = BG.ORGANIZATION_ID(+)
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN OSV.DATE_FROM
AND NVL(OSV.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN HOU1.DATE_FROM
AND NVL(HOU1.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN NVL(BG.DATE_FROM(+)
, TO_DATE('01-01-0001'
, 'DD-MM-YYYY'))
AND NVL(BG.DATE_TO(+)
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')) UNION SELECT DISTINCT HOU1.NAME
, LOC.LOCATION_CODE
, HOU1.INTERNAL_ADDRESS_LINE
, OS.NAME
, OSV.VERSION_NUMBER
, OSV.DATE_FROM
, OSV.DATE_TO
, HR_GENERAL.DECODE_LOOKUP('ORG_TYPE'
, HOU1.TYPE) ORG_TYPE
, PASG.FULL_NAME
, PASG.EMPLOYEE_NUMBER
, PASG.KNOWN_AS
, PASG.EMAIL_ADDRESS
, PASG.MAILSTOP
, PASG.WORK_PHONE_NUMBER
, BG.NAME
, OSE.ORGANIZATION_ID_PARENT
, 0
, OSV.ORG_STRUCTURE_VERSION_ID
FROM HR_LOCATIONS LOC
, HR_HIER_ASG_PEOPLE_V PASG
, HR_ALL_ORGANIZATION_UNITS BG
, HR_ALL_ORGANIZATION_UNITS HOU1
, PER_ORG_STRUCTURE_ELEMENTS OSE
, PER_ORG_STRUCTURE_VERSIONS OSV
, PER_ORGANIZATION_STRUCTURES OS
WHERE OS.ORGANIZATION_STRUCTURE_ID = OSV.ORGANIZATION_STRUCTURE_ID
AND PASG.MANAGER_FLAG(+)='Y'
AND OSV.ORG_STRUCTURE_VERSION_ID = OSE.ORG_STRUCTURE_VERSION_ID
AND OSE.ORGANIZATION_ID_PARENT= HOU1.ORGANIZATION_ID
AND OSE.ORGANIZATION_ID_PARENT = PASG.ORGANIZATION_ID (+)
AND OSE.BUSINESS_GROUP_ID = BG.ORGANIZATION_ID(+)
AND NOT EXISTS ( SELECT NULL
FROM PER_ORG_STRUCTURE_ELEMENTS OSE2
WHERE OSE2.ORGANIZATION_ID_CHILD = OSE.ORGANIZATION_ID_PARENT)
AND HOU1.LOCATION_ID = LOC.LOCATION_ID(+)
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN OSV.DATE_FROM
AND NVL(OSV.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN HOU1.DATE_FROM
AND NVL(HOU1.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN NVL(BG.DATE_FROM(+)
, TO_DATE('01-01-0001'
, 'DD-MM-YYYY'))
AND NVL(BG.DATE_TO(+)
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))

Columns