VIEW: APPS.EGO_INTERNAL_PEOPLE_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'