[Home] [Help]
| Product: | AS - Sales Foundation |
|---|---|
| Description: | Contact addresses view |
| Implementation/DBA Data: | Not implemented in this database |
SELECT CONT.CONTACT_ID
, CONT.LAST_NAME
, CONT.FIRST_NAME
, CONT.CONTACT_NUMBER
, CONT.JOB_TITLE
, CONT.STATUS
, DECODE(CONT.STATUS
, 'A'
, '*'
, NULL) ACTIVE
, CONT.CUSTOMER_ID
, CUST.CUSTOMER_NAME
, CUST.CUSTOMER_NUMBER
, CONT.ADDRESS_ID
, CUST_ADDR.ADDRESS1
, CUST_ADDR.ADDRESS2
, CUST_ADDR.ADDRESS3
, CUST_ADDR.ADDRESS4
, CUST_ADDR.CITY
, CUST_ADDR.STATE
, CUST_ADDR.PROVINCE
, CUST_ADDR.STATE || DECODE(CUST_ADDR.STATE
, NULL
, NULL
, DECODE(CUST_ADDR.PROVINCE
, NULL
, NULL
, '/')) || CUST_ADDR.PROVINCE
, CUST_ADDR.POSTAL_CODE
, CUST_ADDR.COUNTY
, TERR1.TERRITORY_SHORT_NAME
, CONT.MAILING_ADDRESS_ID
, CONT_ADDR.ADDRESS1
, CONT_ADDR.ADDRESS2
, CONT_ADDR.ADDRESS3
, CONT_ADDR.ADDRESS4
, CONT_ADDR.CITY
, CONT_ADDR.STATE
, CONT_ADDR.PROVINCE
, CONT_ADDR.POSTAL_CODE
, CONT_ADDR.COUNTY
, CONT_ADDR.COUNTRY
, TERR2.TERRITORY_SHORT_NAME
, CONT.EMAIL_ADDRESS
FROM FND_TERRITORIES_TL TERR2
, FND_TERRITORIES_TL TERR1
, RA_ADDRESSES CONT_ADDR
, RA_ADDRESSES CUST_ADDR
, RA_CUSTOMERS CUST
, RA_CONTACTS CONT
WHERE CONT.ADDRESS_ID = CUST_ADDR.ADDRESS_ID
AND CUST.CUSTOMER_ID = CONT.CUSTOMER_ID
AND CONT.MAILING_ADDRESS_ID = CONT_ADDR.ADDRESS_ID(+)
AND CUST_ADDR.COUNTRY = TERR1.TERRITORY_CODE
AND TERR1.LANGUAGE(+) = USERENV('LANG')
AND CONT_ADDR.COUNTRY = TERR2.TERRITORY_CODE(+)
AND TERR2.LANGUAGE(+) = USERENV('LANG')