View: BIC_PARTY_LIST_V

View Text

SELECT PA.PARTY_ID PARTY_ID
, PA.PARTY_NAME CUSTOMER_NAME
, NULL RELATIONSHIP
, NULL RELATED_PARTY
, PA.CREATION_DATE ACQUIRED_DATE
, PA.CATEGORY_CODE CUSTOMER_CATEGORY_FK
, A.MARKET_SEGMENT_ID MARKET_SEGMENT_FK
, PA.COUNTRY
, PA.STATE
, PA.CITY
, DECODE(PA.ADDRESS1
, NULL
, NULL
, PA.ADDRESS1||CHR(10)|| PA.CITY||' '||PA.STATE||' '||PA.POSTAL_CODE||CHR(10)|| PA.COUNTY||CHR(10)||PA.COUNTRY) ADDRESS
FROM HZ_PARTIES PA
, AMS_PARTY_MARKET_SEGMENTS A
WHERE PARTY_TYPE <> 'PARTY_RELATIONSHIP'
AND PA.PARTY_ID = A.PARTY_ID (+) UNION SELECT PAS.PARTY_ID PARTY_ID
, PAS.PARTY_NAME CUSTOMER_NAME
, ARL.MEANING RELATIONSHIP
, PAO.PARTY_NAME RELATED_PARTY
, PAS.CREATION_DATE ACQUIRED_DATE
, PAS.CATEGORY_CODE CUSTOMER_CATEGORY_FK
, A.MARKET_SEGMENT_ID MARKET_SEGMENT_FK
, PAS.COUNTRY
, PAS.STATE
, PAS.CITY
, DECODE(PAS.ADDRESS1
, NULL
, NULL
, PAS.ADDRESS1||CHR(10)|| PAS.CITY||' '||PAS.STATE||' '||PAS.POSTAL_CODE||CHR(10)|| PAS.COUNTY||CHR(10)||PAS.COUNTRY) ADDRESS
FROM HZ_PARTIES PAS
, HZ_PARTY_RELATIONSHIPS PR
, HZ_PARTIES PAO
, AMS_PARTY_MARKET_SEGMENTS A
, AR_LOOKUPS ARL
WHERE PAS.PARTY_ID = PR.SUBJECT_ID
AND PR.OBJECT_ID = PAO.PARTY_ID
AND PAS.PARTY_ID = A.PARTY_ID (+)
AND ARL.LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE'
AND PR.PARTY_RELATIONSHIP_TYPE = ARL.LOOKUP_CODE
AND PR.PARTY_RELATIONSHIP_TYPE NOT IN ( SELECT PARTY_RELATIONSHIP_TYPE
FROM BIC_RELATIONSHIP_TYPES ) WITH READ ONLY

Columns