View: EGO_PEOPLE_V

Product:	EGO - Advanced Product Catalog
Description:	This view contains the people information
Implementation/DBA Data:	APPS.EGO_PEOPLE_V

View Text

SELECT P.PARTY_ID PERSON_ID
, P.PARTY_NAME PERSON_NAME
, U.USER_NAME USER_NAME
, U.USER_ID USER_ID
, CASE WHEN (U.EMPLOYEE_ID IS NOT NULL
AND U.PERSON_PARTY_ID = P.PARTY_ID ) THEN (SELECT EMAIL_ADDRESS
FROM PER_ALL_PEOPLE_F
WHERE PARTY_ID = U.PERSON_PARTY_ID
AND PERSON_ID = U.EMPLOYEE_ID
AND EFFECTIVE_START_DATE <= SYSDATE
AND NVL(EFFECTIVE_END_DATE
, SYSDATE) >= SYSDATE
AND (CURRENT_EMPLOYEE_FLAG = 'Y' OR CURRENT_NPW_FLAG = 'Y') ) WHEN (U.EMPLOYEE_ID IS NULL
AND NVL(R.RELATIONSHIP_CODE
, 'EMPLOYEE_OF') = 'EMPLOYEE_OF') THEN (SELECT U.EMAIL_ADDRESS
FROM DUAL) ELSE (SELECT HCP.EMAIL_ADDRESS
FROM HZ_CONTACT_POINTS HCP
WHERE HCP.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND HCP.OWNER_TABLE_ID = R.PARTY_ID
AND HCP.CONTACT_POINT_TYPE = 'EMAIL'
AND HCP.STATUS = 'A'
AND HCP.PRIMARY_FLAG = 'Y'
AND ROWNUM = 1 ) END AS EMAIL_ADDRESS
, CASE WHEN (U.EMPLOYEE_ID IS NOT NULL
AND U.PERSON_PARTY_ID = P.PARTY_ID ) THEN (SELECT 'INTERNAL'
FROM DUAL) WHEN (U.EMPLOYEE_ID IS NULL
AND NVL(R.RELATIONSHIP_CODE
, 'EMPLOYEE_OF') = 'EMPLOYEE_OF') THEN (SELECT 'CUSTOMER'
FROM DUAL) ELSE (SELECT 'VENDOR'
FROM DUAL) END AS PERSON_TYPE
, C.PARTY_ID COMPANY_ID
, C.PARTY_NAME COMPANY_NAME
, (SELECT MEANING
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'CONTACT_TITLE'
AND LOOKUP_CODE = P.PERSON_PRE_NAME_ADJUNCT
AND START_DATE_ACTIVE <= SYSDATE
AND NVL(END_DATE_ACTIVE
, SYSDATE) >= SYSDATE) AS PERSON_TITLE
, (SELECT PRE_NAME_ADJUNCT
FROM PER_ALL_PEOPLE_F
WHERE PARTY_ID = U.PERSON_PARTY_ID
AND PERSON_ID = U.EMPLOYEE_ID
AND EFFECTIVE_START_DATE <= SYSDATE
AND NVL(EFFECTIVE_END_DATE
, SYSDATE) >= SYSDATE
AND (CURRENT_EMPLOYEE_FLAG = 'Y' OR CURRENT_NPW_FLAG = 'Y')) AS PERSON_PREFIX
, P.PERSON_FIRST_NAME PERSON_FIRST_NAME
, P.PERSON_MIDDLE_NAME PERSON_MIDDLE_NAME
, P.PERSON_LAST_NAME PERSON_LAST_NAME
, P.PERSON_NAME_SUFFIX PERSON_NAME_SUFFIX
, PCP.PHONE_COUNTRY_CODE PHONE_COUNTRY_CODE
, PCP.PHONE_AREA_CODE PHONE_AREA_CODE
, NVL( PCP.PHONE_NUMBER
, NVL( (SELECT PHONE_NUMBER
FROM PER_PHONES
WHERE PHONE_TYPE = 'W1'
AND PARTY_ID = P.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 = P.PARTY_ID
AND DATE_FROM <= SYSDATE
AND NVL(DATE_TO
, SYSDATE) >= SYSDATE) ) )AS PHONE_NUMBER
, PCP.PHONE_EXTENSION PHONE_EXTENSION
, FCP.PHONE_COUNTRY_CODE FAX_COUNTRY_CODE
, FCP.PHONE_AREA_CODE FAX_AREA_CODE
, NVL(FCP.PHONE_NUMBER
, NVL( (SELECT PHONE_NUMBER
FROM PER_PHONES
WHERE PHONE_TYPE = 'WF'
AND PARTY_ID = P.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 = P.PARTY_ID
AND DATE_FROM <= SYSDATE
AND NVL(DATE_TO
, SYSDATE) >= SYSDATE) ) ) AS FAX_NUMBER
, FCP.PHONE_EXTENSION FAX_EXTENSION
, CASE WHEN (U.EMPLOYEE_ID IS NOT NULL
AND U.PERSON_PARTY_ID = P.PARTY_ID ) THEN (SELECT KNOWN_AS
FROM PER_ALL_PEOPLE_F
WHERE PARTY_ID = U.PERSON_PARTY_ID
AND PERSON_ID = U.EMPLOYEE_ID
AND EFFECTIVE_START_DATE <= SYSDATE
AND NVL(EFFECTIVE_END_DATE
, SYSDATE) >= SYSDATE
AND (CURRENT_EMPLOYEE_FLAG = 'Y' OR CURRENT_NPW_FLAG = 'Y') ) ELSE (SELECT NULL
FROM DUAL) END AS KNOWN_AS
FROM HZ_PARTIES P
, HZ_PARTIES C
, HZ_RELATIONSHIPS R
, FND_USER U
, HZ_CONTACT_POINTS PCP
, HZ_CONTACT_POINTS FCP
WHERE ( ( U.EMPLOYEE_ID IS NOT NULL
AND P.PARTY_ID = (SELECT PARTY_ID
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = U.EMPLOYEE_ID
AND EFFECTIVE_START_DATE <= SYSDATE
AND NVL(EFFECTIVE_END_DATE
, SYSDATE) >= SYSDATE
AND (CURRENT_EMPLOYEE_FLAG = 'Y' OR CURRENT_NPW_FLAG = 'Y') )
AND NVL(R.RELATIONSHIP_CODE
, 'EMPLOYEE_OF') = 'EMPLOYEE_OF' ) OR ( U.EMPLOYEE_ID IS NULL
AND EXISTS (SELECT 'X'
FROM HZ_CUST_ACCOUNTS HCA1
WHERE C.PARTY_ID = HCA1.PARTY_ID )
AND NVL(R.RELATIONSHIP_CODE
, 'EMPLOYEE_OF') = 'EMPLOYEE_OF' ) OR ( U.EMPLOYEE_ID IS NULL
AND EXISTS (SELECT 'X'
FROM PO_VENDORS PV
, AK_WEB_USER_SEC_ATTR_VALUES SEC
WHERE PV.PARTY_ID = C.PARTY_ID
AND SEC.WEB_USER_ID = U.USER_ID
AND SEC.ATTRIBUTE_CODE = 'ICX_SUPPLIER_ORG_ID'
AND SEC.NUMBER_VALUE = PV.VENDOR_ID
AND SEC.ATTRIBUTE_APPLICATION_ID = 177 )
AND NVL(R.RELATIONSHIP_CODE
, 'CONTACT_OF') = 'CONTACT_OF' ) )
AND U.PERSON_PARTY_ID = P.PARTY_ID
AND P.PARTY_TYPE = 'PERSON'
AND U.START_DATE <= SYSDATE
AND NVL(U.END_DATE
, SYSDATE) >= SYSDATE
AND R.SUBJECT_ID (+) = P.PARTY_ID
AND R.SUBJECT_TYPE (+) = 'PERSON'
AND R.SUBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND R.OBJECT_TYPE (+) = 'ORGANIZATION'
AND R.OBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND R.OBJECT_ID = C.PARTY_ID (+)
AND R.START_DATE(+) <= SYSDATE
AND R.STATUS(+) = 'A'
AND PCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND PCP.OWNER_TABLE_ID(+) = P.PARTY_ID
AND PCP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND PCP.PHONE_LINE_TYPE(+) = 'GEN'
AND PCP.PRIMARY_FLAG(+) = 'Y'
AND PCP.STATUS(+) = 'A'
AND FCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND FCP.OWNER_TABLE_ID(+) = P.PARTY_ID
AND FCP.CONTACT_POINT_TYPE(+) = 'FAX'
AND FCP.STATUS(+) = 'A'

Columns