View: JTF_RS_PARTY_CONTACTS_VL

Product:	JTF - CRM Foundation
Description:	The list of party contacts
Implementation/DBA Data:	APPS.JTF_RS_PARTY_CONTACTS_VL

View Text

SELECT PARTY.PARTY_ID PARTY_ID
, ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID
, ORG_CONT.ORG_CONTACT_ID CONTACT_ID
, ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER
, PARTY.PARTY_NAME CONTACT_NAME
, CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
FROM HZ_PARTIES PARTY
, HZ_RELATIONSHIPS PARTY_REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_ORG_CONTACT_ROLES CONT_ROLE
WHERE PARTY.STATUS = 'A'
AND PARTY.PARTY_TYPE = 'PERSON'
AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
AND PARTY.PARTY_ID = PARTY_REL.SUBJECT_ID
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
AND PARTY_REL.STATUS = 'A' UNION SELECT PARTY5.PARTY_ID PARTY_ID
, ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID
, ORG_CONT.ORG_CONTACT_ID CONTACT_ID
, ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER
, PARTY5.PARTY_NAME CONTACT_NAME
, CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
FROM HZ_PARTIES PARTY3
, HZ_PARTIES PARTY4
, HZ_PARTIES PARTY5
, HZ_RELATIONSHIPS PARTY_REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_ORG_CONTACT_ROLES CONT_ROLE
WHERE PARTY_REL.PARTY_ID = PARTY5.PARTY_ID
AND PARTY5.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND PARTY5.STATUS = 'A'
AND TRUNC (NVL (PARTY_REL.END_DATE
, SYSDATE)) >= TRUNC (SYSDATE)
AND PARTY_REL.SUBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'PERSON'
AND PARTY3.STATUS = 'A'
AND PARTY_REL.OBJECT_ID = PARTY4.PARTY_ID
AND PARTY4.PARTY_TYPE = 'ORGANIZATION'
AND PARTY4.STATUS = 'A'
AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
AND PARTY_REL.STATUS = 'A' UNION SELECT PARTY4.PARTY_ID PARTY_ID
, ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID
, ORG_CONT.ORG_CONTACT_ID CONTACT_ID
, ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER
, PARTY3.PARTY_NAME CONTACT_NAME
, CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
FROM HZ_PARTIES PARTY3
, HZ_PARTIES PARTY4
, HZ_RELATIONSHIPS PARTY_REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_ORG_CONTACT_ROLES CONT_ROLE
WHERE PARTY_REL.SUBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'PERSON'
AND PARTY3.STATUS = 'A'
AND PARTY_REL.OBJECT_ID = PARTY4.PARTY_ID
AND PARTY4.PARTY_TYPE = 'ORGANIZATION'
AND PARTY4.STATUS = 'A'
AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
AND TRUNC (PARTY_REL.START_DATE) <= TRUNC (SYSDATE)
AND TRUNC (NVL (PARTY_REL.END_DATE
, SYSDATE)) >= TRUNC (SYSDATE)
AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
AND PARTY_REL.STATUS = 'A' ORDER BY 1
, 3

Columns