[Home] [Help]
| Product: | AS - Sales Foundation |
|---|---|
| Description: | View of contacts with invalid addresses |
| 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
, 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
FROM FND_TERRITORIES_TL TERR2
, RA_ADDRESSES CONT_ADDR
, RA_CUSTOMERS CUST
, RA_CONTACTS CONT
, AS_INVALID_ADDR INADDR
WHERE CUST.CUSTOMER_ID = CONT.CUSTOMER_ID
AND CONT.MAILING_ADDRESS_ID = CONT_ADDR.ADDRESS_ID
AND INADDR.ADDRESS_ID = CONT.MAILING_ADDRESS_ID
AND CONT_ADDR.COUNTRY = TERR2.TERRITORY_CODE(+)
AND TERR2.LANGUAGE(+) = USERENV('LANG')