VIEW: APPS.JTF_RS_PARTY_CONTACTS_VL

Source

View Text - Preformatted

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

View Text - HTML Formatted

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