View: CSC_HZ_PER_PROFILES_V

Product:	CSC - Customer Care
Description:	View of HZ_PERSON_PROFILES, HZ_PARTY_RELATIONSHIPS. Detailed information about a party. Refer to base table for details.
Implementation/DBA Data:	APPS.CSC_HZ_PER_PROFILES_V

View Text

SELECT PERSON_PROFILE.PARTY_ID
, PERSON_PROFILE.KNOWN_AS
, PERSON_PROFILE.PERSON_NAME_PHONETIC
, PERSON_PROFILE.DATE_OF_BIRTH
, PERSON_PROFILE.PERSONAL_INCOME
, PERSON_PROFILE.LAST_UPDATE_DATE
, PERSON_PROFILE.LAST_UPDATE_DATE
, ORG_CONT.ORG_CONTACT_ID
, ORG_CONT.DECISION_MAKER_FLAG
, ORG_CONT.JOB_TITLE
, ORG_CONT.JOB_TITLE_CODE
, ORG_CONT.MANAGED_BY
, ORG_CONT.NATIVE_LANGUAGE
, ORG_CONT.REFERENCE_USE_FLAG
, ORG_CONT.OTHER_LANGUAGE_1
, ORG_CONT.OTHER_LANGUAGE_2
, ORG_CONT.RANK
, ORG_CONT.DEPARTMENT_CODE
, ORG_CONT.DEPARTMENT
, ORG_CONT.LAST_UPDATE_DATE
, ORG_CONT.MAIL_STOP
, PERSON_PROFILE.PERSON_NAME_SUFFIX
, PERSON_PROFILE.PERSON_TITLE
FROM HZ_PERSON_PROFILES PERSON_PROFILE
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTY_RELATIONSHIPS PARTY_REL
WHERE ORG_CONT.PARTY_RELATIONSHIP_ID = PARTY_REL.PARTY_RELATIONSHIP_ID
AND (PARTY_REL.SUBJECT_ID = PERSON_PROFILE.PARTY_ID OR PARTY_REL.OBJECT_ID = PERSON_PROFILE.PARTY_ID)
AND (PERSON_PROFILE.EFFECTIVE_END_DATE IS NULL OR SYSDATE BETWEEN PERSON_PROFILE.EFFECTIVE_START_DATE
AND PERSON_PROFILE.EFFECTIVE_END_DATE) UNION SELECT PERSON_PROFILE.PARTY_ID
, PERSON_PROFILE.KNOWN_AS
, PERSON_PROFILE.PERSON_NAME_PHONETIC
, PERSON_PROFILE.DATE_OF_BIRTH
, PERSON_PROFILE.PERSONAL_INCOME
, PERSON_PROFILE.LAST_UPDATE_DATE
, PERSON_PROFILE.LAST_UPDATE_DATE
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_DATE(NULL)
, NULL
, PERSON_PROFILE.PERSON_NAME_SUFFIX
, PERSON_PROFILE.PERSON_TITLE
FROM HZ_PERSON_PROFILES PERSON_PROFILE
WHERE ( NOT EXISTS (SELECT 'X'
FROM HZ_PARTY_RELATIONSHIPS PARTY_REL
WHERE PARTY_REL.SUBJECT_ID = PERSON_PROFILE.PARTY_ID)
AND NOT EXISTS (SELECT 'X'
FROM HZ_PARTY_RELATIONSHIPS PARTY_REL
WHERE PARTY_REL.OBJECT_ID = PERSON_PROFILE.PARTY_ID) )
AND (PERSON_PROFILE.EFFECTIVE_END_DATE IS NULL OR SYSDATE BETWEEN PERSON_PROFILE.EFFECTIVE_START_DATE
AND PERSON_PROFILE.EFFECTIVE_END_DATE)

Columns