View: EGO_INTERNAL_PEOPLE_V

Product:	EGO - Advanced Product Catalog
Description:	View contains all the Internal People.
Implementation/DBA Data:	APPS.EGO_INTERNAL_PEOPLE_V

View Text

SELECT EMPLOYEE.PARTY_ID PERSON_ID
, FND_USER.USER_NAME USER_NAME
, EMPLOYEE.PARTY_NAME PERSON_NAME
, HR_EMPLOYEE.EMAIL_ADDRESS
, ENTERPRISE.PARTY_ID COMPANY_ID
, ENTERPRISE.PARTY_NAME COMPANY_NAME
, (SELECT MEANING
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'CONTACT_TITLE'
AND LOOKUP_CODE = EMPLOYEE.PERSON_PRE_NAME_ADJUNCT
AND START_DATE_ACTIVE <= SYSDATE
AND NVL(END_DATE_ACTIVE
, SYSDATE) >= SYSDATE) AS PERSON_TITLE
, HR_EMPLOYEE.PRE_NAME_ADJUNCT PERSON_PREFIX
, EMPLOYEE.PERSON_FIRST_NAME PERSON_FIRST_NAME
, EMPLOYEE.PERSON_MIDDLE_NAME PERSON_MIDDLE_NAME
, EMPLOYEE.PERSON_LAST_NAME PERSON_LAST_NAME
, EMPLOYEE.PERSON_NAME_SUFFIX PERSON_NAME_SUFFIX
, PHONE_CONTACT.PHONE_COUNTRY_CODE PHONE_COUNTRY_CODE
, PHONE_CONTACT.PHONE_AREA_CODE PHONE_AREA_CODE
, NVL( PHONE_CONTACT.PHONE_NUMBER
, NVL( (SELECT PHONE_NUMBER
FROM PER_PHONES
WHERE PHONE_TYPE = 'W1'
AND PARTY_ID = EMPLOYEE.PARTY_ID
AND DATE_FROM <= SYSDATE
AND NVL(DATE_TO
, SYSDATE) >= SYSDATE)
, (SELECT PHONE_NUMBER
FROM PER_PHONES
WHERE PHONE_TYPE = 'H1'
AND PARTY_ID = EMPLOYEE.PARTY_ID
AND DATE_FROM <= SYSDATE
AND NVL(DATE_TO
, SYSDATE) >= SYSDATE) ) ) AS PHONE_NUMBER
, PHONE_CONTACT.PHONE_EXTENSION PHONE_EXTENSION
, FAX_CONTACT.PHONE_COUNTRY_CODE FAX_COUNTRY_CODE
, FAX_CONTACT.PHONE_AREA_CODE FAX_AREA_CODE
, NVL(FAX_CONTACT.PHONE_NUMBER
, NVL( (SELECT PHONE_NUMBER
FROM PER_PHONES
WHERE PHONE_TYPE = 'WF'
AND PARTY_ID = EMPLOYEE.PARTY_ID
AND DATE_FROM <= SYSDATE
AND NVL(DATE_TO
, SYSDATE) >= SYSDATE)
, (SELECT PHONE_NUMBER
FROM PER_PHONES
WHERE PHONE_TYPE = 'HF'
AND PARTY_ID = EMPLOYEE.PARTY_ID
AND DATE_FROM <= SYSDATE
AND NVL(DATE_TO
, SYSDATE) >= SYSDATE) ) ) AS FAX_NUMBER
, FAX_CONTACT.PHONE_EXTENSION FAX_EXTENSION
, FND_USER.USER_ID USER_ID
, HR_EMPLOYEE.KNOWN_AS KNOWN_AS
FROM HZ_PARTIES EMPLOYEE
, HZ_RELATIONSHIPS EMP_CMPY
, HZ_PARTIES ENTERPRISE
, HZ_CONTACT_POINTS PHONE_CONTACT
, HZ_CONTACT_POINTS FAX_CONTACT
, FND_USER FND_USER
, PER_ALL_PEOPLE_F HR_EMPLOYEE
WHERE HR_EMPLOYEE.PERSON_ID = FND_USER.EMPLOYEE_ID
AND FND_USER.START_DATE <= SYSDATE
AND NVL(FND_USER.END_DATE
, SYSDATE) >= SYSDATE
AND (HR_EMPLOYEE.CURRENT_EMPLOYEE_FLAG = 'Y' OR HR_EMPLOYEE.CURRENT_NPW_FLAG = 'Y')
AND HR_EMPLOYEE.EFFECTIVE_START_DATE <= SYSDATE
AND NVL(HR_EMPLOYEE.EFFECTIVE_END_DATE
, SYSDATE) >= SYSDATE
AND EMPLOYEE.PARTY_TYPE = 'PERSON'
AND EMPLOYEE.STATUS = 'A'
AND EMPLOYEE.PARTY_ID = HR_EMPLOYEE.PARTY_ID
AND EMP_CMPY.SUBJECT_TYPE (+) = 'PERSON'
AND EMP_CMPY.SUBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND EMP_CMPY.OBJECT_TYPE(+) = 'ORGANIZATION'
AND EMP_CMPY.RELATIONSHIP_CODE(+) = 'EMPLOYEE_OF'
AND EMP_CMPY.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND EMP_CMPY.STATUS(+) = 'A'
AND EMP_CMPY.START_DATE(+) <= SYSDATE
AND NVL(EMP_CMPY.END_DATE
, SYSDATE) >= SYSDATE
AND EMPLOYEE.PARTY_ID = EMP_CMPY.SUBJECT_ID (+)
AND EMP_CMPY.RELATIONSHIP_TYPE(+) = 'POS_EMPLOYMENT'
AND ENTERPRISE.PARTY_ID (+) = EMP_CMPY.OBJECT_ID
AND ENTERPRISE.STATUS (+) = 'A'
AND PHONE_CONTACT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND PHONE_CONTACT.OWNER_TABLE_ID(+) = EMPLOYEE.PARTY_ID
AND PHONE_CONTACT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND PHONE_CONTACT.PHONE_LINE_TYPE(+) = 'GEN'
AND PHONE_CONTACT.PRIMARY_FLAG(+) = 'Y'
AND PHONE_CONTACT.STATUS(+) = 'A'
AND FAX_CONTACT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND FAX_CONTACT.OWNER_TABLE_ID(+) = EMPLOYEE.PARTY_ID
AND FAX_CONTACT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND FAX_CONTACT.PHONE_LINE_TYPE(+) = 'FAX'
AND FAX_CONTACT.PRIMARY_FLAG(+) = 'Y'
AND FAX_CONTACT.STATUS(+) = 'A'

Columns