View: CS_W_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
, CS_PARTIES_PKG.GET_PARTY_PHONE(PARTY_ID
, FND_PROFILE.VALUE('CS_AR_WORK_PHONE_TYPE')) WORK_PHONE
, CS_PARTIES_PKG.GET_PARTY_PHONE(PARTY_ID
, FND_PROFILE.VALUE('CS_AR_PAGER_PHONE_TYPE')) PAGER_PHONE
, CS_PARTIES_PKG.GET_PARTY_PHONE(PARTY_ID
, FND_PROFILE.VALUE('CS_AR_CELL_PHONE_TYPE')) CELL_PHONE
, CS_PARTIES_PKG.GET_PARTY_EMAIL(PARTY_ID) EMAIL
, CS_PARTIES_PKG.GET_PARTY_FAX(PARTY_ID) FAX
, CS_PARTIES_PKG.GET_PARTY_PHONE(PARTY_ID
, FND_PROFILE.VALUE('CS_AR_HOME_PHONE_TYPE')) HOME_PHONE
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