View: JTF_PARTY_CONTACTS_V

Product:	JTF - CRM Foundation
Description:	JTF_PARTY_CONTACTS_V retrieves all contacts for a party.
Implementation/DBA Data:	APPS.JTF_PARTY_CONTACTS_V

View Text

SELECT ORG_CONT.ROWID
, ORG_CONT.ORG_CONTACT_ID
, PARTY.PARTY_ID
, ORG_CONT.PARTY_SITE_ID
, ORG_CONT.ORG_CONTACT_ID
, PARTY.PARTY_ID
, ORG_CONT.PARTY_SITE_ID
, PARTY.PARTY_TYPE
, PARTY.PARTY_NUMBER
, ORG_CONT.CONTACT_NUMBER
, ORG_CONT.LAST_UPDATE_DATE
, ORG_CONT.LAST_UPDATE_LOGIN
, ORG_CONT.LAST_UPDATED_BY
, ORG_CONT.CREATION_DATE
, ORG_CONT.CREATED_BY
, ORG_CONT.ORIG_SYSTEM_REFERENCE
, PARTY.PERSON_LAST_NAME
, PROFILE.DATE_OF_BIRTH
, PROFILE.MARITAL_STATUS
, ORG_CONT.STATUS
, ORG_CONT.MATCH_GROUP_ID
, ORG_CONT.TITLE
, PARTY.PERSON_FIRST_NAME
, PARTY.PARTY_NAME
, PROFILE.KNOWN_AS
, PROFILE.PERSON_FIRST_NAME_PHONETIC
, PROFILE.PERSON_LAST_NAME_PHONETIC
, ORG_CONT.JOB_TITLE
, ORG_CONT.MAIL_STOP
, ORG_CONT.MAILING_ADDRESS_ID
, ORG_CONT.CONTACT_KEY
, ORG_CONT.DECISION_MAKER_FLAG
, 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
, CONT_ROLE.ROLE_TYPE
, PARTY.PERSON_NAME_SUFFIX
, CONT_POINT.EMAIL_ADDRESS
, PROFILE.GENDER
, PARTY.PERSON_PRE_NAME_ADJUNCT
, ORG_CONT.DEPARTMENT_CODE
, ORG_CONT.DEPARTMENT
, REL.STATUS
, ORG_CONT.REQUEST_ID
, ORG_CONT.PROGRAM_APPLICATION_ID
, ORG_CONT.PROGRAM_ID
, ORG_CONT.PROGRAM_UPDATE_DATE
, ORG_CONT.ATTRIBUTE_CATEGORY
, ORG_CONT.ATTRIBUTE1
, ORG_CONT.ATTRIBUTE2
, ORG_CONT.ATTRIBUTE3
, ORG_CONT.ATTRIBUTE4
, ORG_CONT.ATTRIBUTE5
, ORG_CONT.ATTRIBUTE6
, ORG_CONT.ATTRIBUTE7
, ORG_CONT.ATTRIBUTE8
, ORG_CONT.ATTRIBUTE9
, ORG_CONT.ATTRIBUTE10
, ORG_CONT.ATTRIBUTE11
, ORG_CONT.ATTRIBUTE12
, ORG_CONT.ATTRIBUTE13
, ORG_CONT.ATTRIBUTE14
, ORG_CONT.ATTRIBUTE15
, ORG_CONT.ATTRIBUTE16
, ORG_CONT.ATTRIBUTE17
, ORG_CONT.ATTRIBUTE18
, ORG_CONT.ATTRIBUTE19
, ORG_CONT.ATTRIBUTE20
FROM HZ_CONTACT_POINTS CONT_POINT
, HZ_ORG_CONTACT_ROLES CONT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_PERSON_PROFILES PROFILE
, HZ_ORG_CONTACTS ORG_CONT
WHERE REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND CONT_ROLE.ORG_CONTACT_ID = ORG_CONT.ORG_CONTACT_ID
AND CONT_ROLE.PRIMARY_FLAG(+) = 'Y'
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONT_POINT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID
AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND PARTY.PARTY_ID = PROFILE.PARTY_ID

Columns