View: JTF_TASK_PARTY_CONTACTS_V

Product:	JTF - CRM Foundation
Description:	View all contacts, using hz_relationship table as the driving table. The view expose data for subject and object party. Please note that the view does not join with hz_org_contacts
Implementation/DBA Data:	APPS.JTF_TASK_PARTY_CONTACTS_V

View Text

SELECT PARTY_SUBJECT.PARTY_ID SUBJECT_PARTY_ID
, PARTY_SUBJECT.PARTY_NUMBER SUBJECT_PARTY_NUMBER
, PARTY_SUBJECT.PARTY_NAME SUBJECT_PARTY_NAME
, PARTY_SUBJECT.VALIDATED_FLAG
, PARTY_SUBJECT.PERSON_PRE_NAME_ADJUNCT
, PARTY_SUBJECT.PERSON_FIRST_NAME
, PARTY_SUBJECT.PERSON_MIDDLE_NAME
, PARTY_SUBJECT.PERSON_LAST_NAME
, PARTY_SUBJECT.PERSON_NAME_SUFFIX
, PARTY_SUBJECT.PERSON_TITLE
, PARTY_SUBJECT.PERSON_ACADEMIC_TITLE
, PARTY_SUBJECT.PERSON_PREVIOUS_LAST_NAME
, PARTY_SUBJECT.KNOWN_AS
, PARTY_SUBJECT.PERSON_IDEN_TYPE
, PARTY_SUBJECT.PERSON_IDENTIFIER
, PARTY_SUBJECT.JGZZ_FISCAL_CODE
, PARTY_SUBJECT.TAX_NAME
, PARTY_SUBJECT.TAX_REFERENCE
, REL.START_DATE
, REL.END_DATE
, REL.SUBJECT_ID
, REL.RELATIONSHIP_TYPE
, REL.RELATIONSHIP_ID
, REL.PARTY_ID
, REL.OBJECT_ID
, PARTY_REL.EMAIL_ADDRESS
, REL.STATUS
, PARTY_REL.ADDRESS1
, PARTY_REL.ADDRESS2
, PARTY_REL.ADDRESS3
, PARTY_REL.ADDRESS4
, PARTY_REL.CITY
, PARTY_REL.POSTAL_CODE
, PARTY_REL.STATE
, PARTY_REL.COUNTRY
, HCP.PHONE_COUNTRY_CODE
, HCP.PHONE_AREA_CODE
, HCP.PHONE_NUMBER
, HCP.PHONE_EXTENSION
, ORG_CONTACT.JOB_TITLE
FROM HZ_RELATIONSHIPS REL
, HZ_PARTIES PARTY_SUBJECT
, HZ_PARTIES PARTY_REL
, HZ_CONTACT_POINTS HCP
, HZ_ORG_CONTACTS ORG_CONTACT
WHERE REL.SUBJECT_ID = PARTY_SUBJECT.PARTY_ID
AND REL.PARTY_ID = PARTY_REL.PARTY_ID
AND DIRECTIONAL_FLAG = 'F'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND EXISTS ( SELECT 'Y'
FROM HZ_CODE_ASSIGNMENTS CODE
, HZ_RELATIONSHIP_TYPES RELTYPE
WHERE RELTYPE.RELATIONSHIP_TYPE = REL.RELATIONSHIP_TYPE
AND CODE.OWNER_TABLE_ID = RELTYPE.RELATIONSHIP_TYPE_ID
AND CODE.CLASS_CATEGORY = 'RELATIONSHIP_TYPE_GROUP'
AND CODE.CLASS_CODE = 'PARTY_REL_GRP_CONTACTS'
AND CODE.OWNER_TABLE_NAME = 'HZ_RELATIONSHIP_TYPES')
AND HCP.OWNER_TABLE_ID (+) = PARTY_REL.PARTY_ID
AND HCP.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE (+) = 'PHONE'
AND HCP.PRIMARY_FLAG (+) = 'Y'
AND HCP.STATUS (+) = 'A'
AND ORG_CONTACT.PARTY_RELATIONSHIP_ID (+) = REL.RELATIONSHIP_ID

Columns