View: AST_L_PARTY_PEOPLE_V

View Text

SELECT DECODE(PTY.PARTY_TYPE
, 'ORGANIZATION'
, REL.OBJECT_ID
, 'PERSON'
, REL.SUBJECT_ID
, REL.OBJECT_ID)
, PTY.PARTY_TYPE
, ORG.PARTY_NAME
, REL.SUBJECT_ID
, PER.PERSON_FIRST_NAME
, PER.PERSON_LAST_NAME
, PER.PERSON_MIDDLE_NAME
, PER.PERSON_NAME_SUFFIX
, REL.PARTY_RELATIONSHIP_TYPE
FROM HZ_PARTY_RELATIONSHIPS REL
, HZ_PARTIES PER
, HZ_PARTIES ORG
, HZ_PARTIES PTY
WHERE PTY.PARTY_ID = REL.PARTY_ID
AND ORG.PARTY_ID = REL.OBJECT_ID
AND PER.PARTY_ID = REL.SUBJECT_ID UNION SELECT PARTY.PARTY_ID
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, PARTY.PARTY_ID
, PARTY.PERSON_FIRST_NAME
, PARTY.PERSON_LAST_NAME
, PARTY.PERSON_MIDDLE_NAME
, PARTY.PERSON_NAME_SUFFIX
, NULL
FROM HZ_PARTIES PARTY
WHERE NOT EXISTS (SELECT 'X'
FROM HZ_PARTY_RELATIONSHIPS REL
WHERE PARTY.PARTY_ID = REL.OBJECT_ID
AND PARTY.PARTY_TYPE = 'PERSON')

Columns