View: ASF_ORG_ORG_RELS_EO_V

View Text

SELECT REL.RELATIONSHIP_ID
, OPARTY.PARTY_NAME
, REL.OBJECT_ID
, SPARTY.PARTY_NAME
, REL.SUBJECT_ID
, REL.RELATIONSHIP_CODE
, REL.START_DATE
, REL.END_DATE
, REL.LAST_UPDATE_DATE
, REL.PARTY_ID
, PARTY.LAST_UPDATE_DATE
, SITE.PARTY_SITE_ID
, LOC.LOCATION_ID
, SITE.LAST_UPDATE_DATE
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.STATE
, LOC.PROVINCE
, LOC.POSTAL_CODE
, LOC.COUNTY
, LOC.COUNTRY
, PHONE.PHONE_COUNTRY_CODE
, PHONE.PHONE_AREA_CODE
, PHONE.PHONE_NUMBER
, PHONE.PHONE_EXTENSION
, EMAIL.EMAIL_ADDRESS
FROM HZ_RELATIONSHIPS REL
, HZ_PARTIES SPARTY
, HZ_PARTIES OPARTY
, HZ_PARTIES PARTY
, HZ_PARTY_SITES SITE
, HZ_LOCATIONS LOC
, HZ_CONTACT_POINTS PHONE
, HZ_CONTACT_POINTS EMAIL
WHERE REL.SUBJECT_ID = SPARTY.PARTY_ID
AND SPARTY.PARTY_TYPE = 'ORGANIZATION'
AND REL.OBJECT_ID = OPARTY.PARTY_ID
AND OPARTY.PARTY_TYPE = 'ORGANIZATION'
AND REL.PARTY_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = SITE.PARTY_ID (+)
AND LOC.LOCATION_ID (+) = SITE.LOCATION_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(SITE.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