VIEW: APPS.EGO_PEOPLE_V

Source

View Text - Preformatted

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 <= TRUNC(SYSDATE) AND NVL(effective_end_date,SYSDATE) >= TRUNC(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 <= TRUNC(SYSDATE) AND NVL(effective_end_date,SYSDATE) >= TRUNC(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 parent_table = 'PER_ALL_PEOPLE_F' AND parent_id = U.employee_id and date_from <= TRUNC(SYSDATE) and NVL(date_to,SYSDATE) >= TRUNC(SYSDATE)), (SELECT phone_number FROM per_phones WHERE phone_type = 'H1' and party_id = P.party_id AND parent_table = 'PER_ALL_PEOPLE_F' AND parent_id = U.employee_id and date_from <= TRUNC(SYSDATE) and NVL(date_to,SYSDATE) >= TRUNC(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 parent_table = 'PER_ALL_PEOPLE_F' AND parent_id = U.employee_id and date_from <= TRUNC(SYSDATE) and NVL(date_to,SYSDATE) >= TRUNC(SYSDATE)), (SELECT phone_number FROM per_phones WHERE phone_type = 'HF' and party_id = P.party_id AND parent_table = 'PER_ALL_PEOPLE_F' AND parent_id = U.employee_id and date_from <= TRUNC(SYSDATE) and NVL(date_to,SYSDATE) >= TRUNC(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 <= TRUNC(SYSDATE) AND NVL(effective_end_date,SYSDATE) >= TRUNC(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 <= TRUNC(SYSDATE) AND NVL(effective_end_date,SYSDATE) >= TRUNC(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 <= TRUNC(SYSDATE) AND NVL(U.end_date,SYSDATE) >= TRUNC(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'

View Text - HTML Formatted

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 <= TRUNC(SYSDATE)
AND NVL(EFFECTIVE_END_DATE
, SYSDATE) >= TRUNC(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 <= TRUNC(SYSDATE)
AND NVL(EFFECTIVE_END_DATE
, SYSDATE) >= TRUNC(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 PARENT_TABLE = 'PER_ALL_PEOPLE_F'
AND PARENT_ID = U.EMPLOYEE_ID
AND DATE_FROM <= TRUNC(SYSDATE)
AND NVL(DATE_TO
, SYSDATE) >= TRUNC(SYSDATE))
, (SELECT PHONE_NUMBER
FROM PER_PHONES
WHERE PHONE_TYPE = 'H1'
AND PARTY_ID = P.PARTY_ID
AND PARENT_TABLE = 'PER_ALL_PEOPLE_F'
AND PARENT_ID = U.EMPLOYEE_ID
AND DATE_FROM <= TRUNC(SYSDATE)
AND NVL(DATE_TO
, SYSDATE) >= TRUNC(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 PARENT_TABLE = 'PER_ALL_PEOPLE_F'
AND PARENT_ID = U.EMPLOYEE_ID
AND DATE_FROM <= TRUNC(SYSDATE)
AND NVL(DATE_TO
, SYSDATE) >= TRUNC(SYSDATE))
, (SELECT PHONE_NUMBER
FROM PER_PHONES
WHERE PHONE_TYPE = 'HF'
AND PARTY_ID = P.PARTY_ID
AND PARENT_TABLE = 'PER_ALL_PEOPLE_F'
AND PARENT_ID = U.EMPLOYEE_ID
AND DATE_FROM <= TRUNC(SYSDATE)
AND NVL(DATE_TO
, SYSDATE) >= TRUNC(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 <= TRUNC(SYSDATE)
AND NVL(EFFECTIVE_END_DATE
, SYSDATE) >= TRUNC(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 <= TRUNC(SYSDATE)
AND NVL(EFFECTIVE_END_DATE
, SYSDATE) >= TRUNC(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 <= TRUNC(SYSDATE)
AND NVL(U.END_DATE
, SYSDATE) >= TRUNC(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'