[Home] [Help]
| Product: | BIC - Customer Intelligence (obsolete) |
|---|---|
| Description: | |
| Implementation/DBA Data: | Not implemented in this database |
SELECT PA.PARTY_ID PARTY_ID
, PA.PARTY_NAME CUSTOMER_NAME
, AL.MEANING RELATIONSHIP
, PO.PARTY_NAME RELATED_PARTY
, PA.CREATION_DATE ACQUIRED_DATE
, PA.CUSTOMER_CATEGORY_FK
, PA.MARKET_SEGMENT_FK
, 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
, PA.ACT_PERIOD_NAME
, PA.START_DATE
, PA.ACT_PERIOD_START_DATE
, PA.ACT_PERIOD_END_DATE
, PA.ACT_YEAR
, PA.ACT_PERIOD_NUM
, PA.ACT_QUARTER
, PA.ACT_HALF_YEAR
, PA.ACT_YEAR_START_DATE
, PA.ACT_QUARTER_START_DATE
, PA.ACQUISITION
, PA.ACTIVATION
, PA.RETENTION
, PA.LIFE_CYCLE
FROM BIC_PARTY_STATUS_SUMM_V PA
, HZ_PARTIES PO
, HZ_PARTY_RELATIONSHIPS RL
, AR_LOOKUPS AL
WHERE PA.PARTY_ID = RL.SUBJECT_ID(+)
AND PO.PARTY_ID(+) = RL.OBJECT_ID
AND AL.LOOKUP_TYPE (+)= 'PARTY_RELATIONS_TYPE'
AND RL.PARTY_RELATIONSHIP_TYPE = AL.LOOKUP_CODE (+)
AND NOT EXISTS ( SELECT 1
FROM BIC_RELATIONSHIP_TYPES A WHERE A.PARTY_RELATIONSHIP_TYPE(+) = RL.PARTY_RELATIONSHIP_TYPE )