[Home] [Help]
View: AST_L_PARTY_PEOPLE_V
Product: | AST - TeleSales |
Description: | |
Implementation/DBA Data: |
Not implemented in this database
|
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
Name |
PARTY_ID |
PARTY_TYPE |
PARTY_NAME |
RELATED_PARTY_ID |
FIRST_NAME |
LAST_NAME |
MIDDLE_NAME |
NAME_SUFFIX |
RELATIONSHIP_TYPE |