[Home] [Help]
View: ICX_CONTACT_SITES_V
| Product: | ICX - Oracle iProcurement |
|---|
| Description: | Web Store Customer Contacts Sites View |
|---|
| Implementation/DBA Data: |
Not implemented in this database
|
|---|
View Text
SELECT RC.CUSTOMER_ID
, RC.CONTACT_ID
, RAADR.ADDRESS_ID
, RAADR2.ADDRESS_ID
, RC.ADDRESS_ID
, RAADR.ADDRESS1 || DECODE(RAADR.ADDRESS2
, NULL
, NULL
, '
') || RAADR.ADDRESS2 || DECODE(RAADR.ADDRESS3
, NULL
, NULL
, '
') || RAADR.ADDRESS3 || DECODE(RAADR.ADDRESS4
, NULL
, NULL
, '
') || RAADR.ADDRESS4 || DECODE(RAADR.CITY
, NULL
, NULL
, '
') || RAADR.CITY || DECODE(RAADR.PROVINCE
, NULL
, NULL
, '
') || RAADR.PROVINCE || DECODE(RAADR.STATE || ' ' || RAADR.POSTAL_CODE
, NULL
, NULL
, '
') || RAADR.STATE || ' ' || RAADR.POSTAL_CODE || DECODE(NVL(TERR1.TERRITORY_SHORT_NAME
, RAADR.COUNTRY)
, NULL
, NULL
, '
') || NVL(TERR1.TERRITORY_SHORT_NAME
, RAADR.COUNTRY) SHIP_TO_ADDRESS
, RAADR2.ADDRESS1 || DECODE(RAADR2.ADDRESS2
, NULL
, NULL
, '
') || RAADR2.ADDRESS2 || DECODE(RAADR2.ADDRESS3
, NULL
, NULL
, '
') || RAADR2.ADDRESS3 || DECODE(RAADR2.ADDRESS4
, NULL
, NULL
, '
') || RAADR2.ADDRESS4 || DECODE(RAADR2.CITY
, NULL
, NULL
, '
') || RAADR2.CITY || DECODE(RAADR2.PROVINCE
, NULL
, NULL
, '
') || RAADR2.PROVINCE || DECODE(RAADR2.STATE || ' ' || RAADR2.POSTAL_CODE
, NULL
, NULL
, '
') || RAADR2.STATE || ' ' || RAADR2.POSTAL_CODE || DECODE(NVL(TERR2.TERRITORY_SHORT_NAME
, RAADR2.COUNTRY)
, NULL
, NULL
, '
') || NVL(TERR2.TERRITORY_SHORT_NAME
, RAADR2.COUNTRY) BILL_TO_ADDRESS
, RASIT.SITE_USE_ID
, RAADR.CREATED_BY
, RAADR2.CREATED_BY
, RASIT2.SITE_USE_ID
FROM FND_TERRITORIES_VL TERR1
, FND_TERRITORIES_VL TERR2
, RA_CONTACTS RC
, RA_SITE_USES RASIT
, RA_SITE_USES RASIT2
, RA_ADDRESSES RAADR
, RA_ADDRESSES RAADR2
WHERE RAADR.COUNTRY = TERR1.TERRITORY_CODE(+)
AND RAADR2.COUNTRY = TERR2.TERRITORY_CODE(+)
AND RASIT.BILL_TO_SITE_USE_ID = RASIT2.SITE_USE_ID
AND RAADR.CUSTOMER_ID = RC.CUSTOMER_ID
AND RAADR2.ADDRESS_ID = RASIT2.ADDRESS_ID
AND RASIT.ADDRESS_ID = RAADR.ADDRESS_ID
AND RAADR.STATUS = 'A'
AND RASIT.STATUS = 'A'
AND RASIT.SITE_USE_CODE = 'SHIP_TO'
AND RASIT2.SITE_USE_CODE = 'BILL_TO'
AND RASIT2.STATUS = 'A'
AND RASIT.BILL_TO_SITE_USE_ID IS NOT NULL UNION SELECT RC.CUSTOMER_ID
, RC.CONTACT_ID
, RAADR.ADDRESS_ID
, RAADR2.ADDRESS_ID
, RC.ADDRESS_ID
, RAADR.ADDRESS1 || DECODE(RAADR.ADDRESS2
, NULL
, NULL
, '
') || RAADR.ADDRESS2 || DECODE(RAADR.ADDRESS3
, NULL
, NULL
, '
') || RAADR.ADDRESS3 || DECODE(RAADR.ADDRESS4
, NULL
, NULL
, '
') || RAADR.ADDRESS4 || DECODE(RAADR.CITY
, NULL
, NULL
, '
') || RAADR.CITY || DECODE(RAADR.PROVINCE
, NULL
, NULL
, '
') || RAADR.PROVINCE || DECODE(RAADR.STATE || ' ' || RAADR.POSTAL_CODE
, NULL
, NULL
, '
') || RAADR.STATE || ' ' || RAADR.POSTAL_CODE || DECODE(NVL(TERR1.TERRITORY_SHORT_NAME
, RAADR.COUNTRY)
, NULL
, NULL
, '
') || NVL(TERR1.TERRITORY_SHORT_NAME
, RAADR.COUNTRY) SHIP_TO_ADDRESS
, RAADR2.ADDRESS1 || DECODE(RAADR2.ADDRESS2
, NULL
, NULL
, '
') || RAADR2.ADDRESS2 || DECODE(RAADR2.ADDRESS3
, NULL
, NULL
, '
') || RAADR2.ADDRESS3 || DECODE(RAADR2.ADDRESS4
, NULL
, NULL
, '
') || RAADR2.ADDRESS4 || DECODE(RAADR2.CITY
, NULL
, NULL
, '
') || RAADR2.CITY || DECODE(RAADR2.PROVINCE
, NULL
, NULL
, '
') || RAADR2.PROVINCE || DECODE(RAADR2.STATE || ' ' || RAADR2.POSTAL_CODE
, NULL
, NULL
, '
') || RAADR2.STATE || ' ' || RAADR2.POSTAL_CODE || DECODE(NVL(TERR2.TERRITORY_SHORT_NAME
, RAADR2.COUNTRY)
, NULL
, NULL
, '
') || NVL(TERR2.TERRITORY_SHORT_NAME
, RAADR2.COUNTRY) BILL_TO_ADDRESS
, RASIT.SITE_USE_ID
, RAADR.CREATED_BY
, RAADR2.CREATED_BY
, RASIT2.SITE_USE_ID
FROM FND_TERRITORIES_VL TERR1
, FND_TERRITORIES_VL TERR2
, RA_CONTACTS RC
, RA_SITE_USES RASIT
, RA_SITE_USES RASIT2
, RA_ADDRESSES RAADR
, RA_ADDRESSES RAADR2
WHERE RAADR.COUNTRY = TERR1.TERRITORY_CODE(+)
AND RAADR2.COUNTRY = TERR2.TERRITORY_CODE(+)
AND RC.CUSTOMER_ID = RAADR.CUSTOMER_ID
AND RAADR2.ADDRESS_ID = RASIT2.ADDRESS_ID
AND RAADR2.CUSTOMER_ID = RC.CUSTOMER_ID
AND RASIT.ADDRESS_ID = RAADR.ADDRESS_ID
AND RAADR.STATUS = 'A'
AND RASIT.STATUS = 'A'
AND RASIT.SITE_USE_CODE = 'SHIP_TO'
AND RASIT2.SITE_USE_CODE = 'BILL_TO'
AND RASIT2.STATUS = 'A'
AND RASIT.BILL_TO_SITE_USE_ID IS NULL
Columns
| Name |
| CUSTOMER_ID |
| CONTACT_ID |
| SHIP_TO_ADDRESS_ID |
| BILL_TO_ADDRESS_ID |
| CONTACT_ADDRESS_ID |
| SHIP_TO_ADDRESS |
| BILL_TO_ADDRESS |
| SITE_USE_ID |
| SHIP_TO_ADDR_CREATED_BY |
| BILL_TO_ADDR_CREATED_BY |
| BILL_TO_SITE_ID |