View: CS_SYSTEM_CONTACTS_RG_V

View Text

SELECT /*+ FIRST_ROWS */ HP.PARTY_NAME PARTY_NAME
, HP.PARTY_NUMBER PARTY_NUMBER
, HP.PARTY_TYPE PARTY_TYPE
, HP.PARTY_ID PARTY_ID
, ARL.MEANING MEANING
, DECODE(PARTY_TYPE
, 'PERSON'
, 'AAAA'
, PARTY_TYPE ) SORT_ORDER
, HP.PARTY_ID ORIGINAL_PARTY_ID
FROM HZ_PARTIES HP
, AR_LOOKUPS ARL
WHERE ARL.LOOKUP_TYPE (+) = 'PARTY_TYPE'
AND HP.PARTY_TYPE = ARL.LOOKUP_CODE (+)
AND HP.PARTY_ID > 0 UNION ALL SELECT /*+ FIRST_ROWS */ HP.PARTY_NAME PARTY_NAME
, HP.PARTY_NUMBER PARTY_NUMBER
, HP.PARTY_TYPE PARTY_TYPE
, HP.PARTY_ID PARTY_ID
, ARL.MEANING MEANING
, DECODE(PARTY_TYPE
, 'PERSON'
, 'AAAA'
, PARTY_TYPE ) SORT_ORDER
, HPR.OBJECT_ID ORIGINAL_PARTY_ID
FROM HZ_PARTIES HP
, HZ_PARTY_RELATIONSHIPS HPR
, AR_LOOKUPS ARL
WHERE HPR.SUBJECT_ID = HP.PARTY_ID
AND ARL.LOOKUP_TYPE (+) = 'PARTY_TYPE'
AND HPR.PARTY_RELATIONSHIP_TYPE = 'CONTACT_OF'
AND HP.PARTY_TYPE = ARL.LOOKUP_CODE (+)
AND HP.PARTY_ID > 0

Columns