VIEW: APPS.CSC_HZ_PER_PROFILES_V

Source

View Text - Preformatted

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_RELATIONSHIPS PARTY_REL WHERE ORG_CONT.PARTY_RELATIONSHIP_ID = PARTY_REL.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) AND party_rel.subject_table_name = 'HZ_PARTIES' AND party_rel.object_table_name = 'HZ_PARTIES' AND party_rel.directional_flag = 'F' 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_RELATIONSHIPS PARTY_REL WHERE PARTY_REL.SUBJECT_ID = PERSON_PROFILE.PARTY_ID AND party_rel.subject_table_name = 'HZ_PARTIES' AND party_rel.object_table_name = 'HZ_PARTIES' AND party_rel.directional_flag = 'F') AND NOT EXISTS (SELECT 'x' FROM HZ_RELATIONSHIPS PARTY_REL WHERE PARTY_REL.OBJECT_ID = PERSON_PROFILE.PARTY_ID AND party_rel.subject_table_name = 'HZ_PARTIES' AND party_rel.object_table_name = 'HZ_PARTIES' AND party_rel.directional_flag = 'F') ) AND (PERSON_PROFILE.EFFECTIVE_END_DATE IS NULL OR SYSDATE BETWEEN PERSON_PROFILE.EFFECTIVE_START_DATE AND PERSON_PROFILE.EFFECTIVE_END_DATE)

View Text - HTML Formatted

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_RELATIONSHIPS PARTY_REL
WHERE ORG_CONT.PARTY_RELATIONSHIP_ID = PARTY_REL.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)
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F' 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_RELATIONSHIPS PARTY_REL
WHERE PARTY_REL.SUBJECT_ID = PERSON_PROFILE.PARTY_ID
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F')
AND NOT EXISTS (SELECT 'X'
FROM HZ_RELATIONSHIPS PARTY_REL
WHERE PARTY_REL.OBJECT_ID = PERSON_PROFILE.PARTY_ID
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F') )
AND (PERSON_PROFILE.EFFECTIVE_END_DATE IS NULL OR SYSDATE BETWEEN PERSON_PROFILE.EFFECTIVE_START_DATE
AND PERSON_PROFILE.EFFECTIVE_END_DATE)