View: CS_RESOURCE_DETAILS_V

View Text

SELECT /*+ RULE */ PARTY_NAME RESOURCE_NAME
, PARTY_NUMBER RESOURCE_NUMBER
, PARTY_ID RESOURCE_ID
, RESOURCE_TYPE
, ADDRESS
, ORIGINAL_PARTY_ID
, 'PARTY' RESOURCE_CATEGORY
, SORT_ORDER
FROM ( SELECT HP.PARTY_NAME PARTY_NAME
, HP.PARTY_NUMBER PARTY_NUMBER
, HP.PARTY_ID PARTY_ID
, HP.PARTY_TYPE RESOURCE_TYPE
, HP.ADDRESS1||' '||HP.ADDRESS2||' '||HP.ADDRESS3||' '||HP.ADDRESS4||' '||HP.CITY||' '|| HP.STATE||' '||HP.POSTAL_CODE||' '||HP.PROVINCE||' '||HP.COUNTY ADDRESS
, HP.PARTY_ID ORIGINAL_PARTY_ID
, DECODE(HP.PARTY_TYPE
, 'PERSON'
, 'AAAA'
, HP.PARTY_TYPE ) SORT_ORDER
FROM HZ_PARTIES HP
, AR_LOOKUPS ARL
WHERE ARL.LOOKUP_TYPE (+) = 'PARTY_TYPE'
AND HP.PARTY_TYPE = ARL.LOOKUP_CODE (+) UNION SELECT HP.PARTY_NAME PARTY_NAME
, HP.PARTY_NUMBER PARTY_NUMBER
, HP.PARTY_ID PARTY_ID
, HP.PARTY_TYPE RESOURCE_TYPE
, HP.ADDRESS1||' '||HP.ADDRESS2||' '||HP.ADDRESS3||' '||HP.ADDRESS4||' '||HP.CITY||' '|| HP.STATE||' '||HP.POSTAL_CODE||' ' ||HP.PROVINCE||''||HP.COUNTY ADDRESS
, HPR.OBJECT_ID ORIGINAL_PARTY_ID
, DECODE(HP.PARTY_TYPE
, 'PERSON'
, 'AAAA'
, HP.PARTY_TYPE ) SORT_ORDER
FROM HZ_PARTIES HP
, HZ_PARTY_RELATIONSHIPS HPR
, AR_LOOKUPS ARL WHERE HPR.SUBJECT_ID = HP.PARTY_ID
AND HP.PARTY_TYPE IN ('PERSON'
, 'ORGANIZATION'
, 'PARTNER')
AND HPR.PARTY_RELATIONSHIP_TYPE = 'CONTACT_OF'
AND ARL.LOOKUP_TYPE (+) = 'PARTY_TYPE'
AND HP.PARTY_TYPE = ARL.LOOKUP_CODE (+) )

Columns