View: ASF_ORG_CNT_RELS_VO_V

View Text

SELECT REL.RELATIONSHIP_ID
, REL.SUBJECT_ID
, SPARTY.PERSON_TITLE
, SPARTY.PERSON_FIRST_NAME
, SPARTY.PERSON_LAST_NAME
, CONT.ORG_CONTACT_ID
, CONT.JOB_TITLE
, REL.OBJECT_ID
, OPARTY.PARTY_NAME
, REL.RELATIONSHIP_CODE
, PHONE.PHONE_COUNTRY_CODE
, PHONE.PHONE_AREA_CODE
, PHONE.PHONE_NUMBER
, PHONE.PHONE_EXTENSION
, EMAIL.EMAIL_ADDRESS
, REL.START_DATE
, REL.END_DATE
, REL.LAST_UPDATE_DATE
, REL.PARTY_ID
, PARTY.LAST_UPDATE_DATE
, CONT.LAST_UPDATE_DATE
FROM HZ_RELATIONSHIPS REL
, HZ_PARTIES SPARTY
, HZ_ORG_CONTACTS CONT
, HZ_PARTIES OPARTY
, HZ_PARTIES PARTY
, HZ_CONTACT_POINTS PHONE
, HZ_CONTACT_POINTS EMAIL
WHERE REL.OBJECT_ID = OPARTY.PARTY_ID
AND SPARTY.PARTY_TYPE = 'PERSON'
AND OPARTY.PARTY_TYPE = 'ORGANIZATION'
AND REL.RELATIONSHIP_ID = CONT.PARTY_RELATIONSHIP_ID
AND REL.SUBJECT_ID = SPARTY.PARTY_ID
AND REL.PARTY_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = PHONE.OWNER_TABLE_ID (+)
AND PHONE.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND PHONE.CONTACT_POINT_TYPE (+) = 'PHONE'
AND PHONE.PRIMARY_FLAG (+) = 'Y'
AND REL.PARTY_ID = EMAIL.OWNER_TABLE_ID (+)
AND EMAIL.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND EMAIL.CONTACT_POINT_TYPE (+) = 'EMAIL'
AND EMAIL.PRIMARY_FLAG (+) = 'Y'
AND REL.STATUS = 'A'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND SPARTY.STATUS IN ('A'
, 'I')
AND OPARTY.STATUS IN ('A'
, 'I')
AND PARTY.STATUS IN ('A'
, 'I')
AND DECODE(CONT.STATUS(+)
, 'A'
, 1
, 'I'
, 1
, NULL
, 1
, -99) = 1
AND DECODE(PHONE.STATUS(+)
, 'A'
, 1
, 'I'
, 1
, NULL
, 1
, -99) = 1
AND DECODE(EMAIL.STATUS(+)
, 'A'
, 1
, 'I'
, 1
, NULL
, 1
, -99) = 1

Columns