Product: | AS - Sales Foundation |
---|---|
Description: | Account contacts |
Implementation/DBA Data: | Not implemented in this database |
SELECT CUST.CUSTOMER_ID
, CUST.CUSTOMER_NAME
, CUST.CUSTOMER_NUMBER
, CUST.TAX_REFERENCE
, CUST.STATUS
, ADDR.ADDRESS_ID
, ADDR.STATUS
, ADDR.ADDRESS1
, ADDR.ADDRESS2
, ADDR.CITY
, ADDR.POSTAL_CODE
, ADDR.STATE
, CONT.CONTACT_ID
, CONT.FIRST_NAME
, CONT.LAST_NAME
, CONT.STATUS
, CONT.CONTACT_NUMBER
, PHON.PHONE_ID
, PHON.AREA_CODE
, PHON.PHONE_NUMBER
, PHON.EXTENSION
, CUST.CUSTOMER_KEY
, ADDR.ADDRESS_KEY
, CONT.CONTACT_KEY
, LOOK2.MEANING
, CONT.JOB_TITLE
, LOOK3.MEANING
, ADDR.DO_NOT_MAIL_FLAG
, ADDR.ADDRESS3
, ADDR.ADDRESS4
, ADDR.COUNTY
, ADDR.PROVINCE
, TERR.TERRITORY_SHORT_NAME
, ADDR.COUNTRY
, LOOK4.MEANING
, CUST.CUSTOMER_PROSPECT_CODE
, CUST.COMPETITOR_FLAG
, ADDR.KEY_ACCOUNT_FLAG
, CONT.DO_NOT_MAIL_FLAG
, CONT.ATTRIBUTE_CATEGORY
, CONT.ATTRIBUTE1
, CONT.ATTRIBUTE2
, CONT.ATTRIBUTE3
, CONT.ATTRIBUTE4
, CONT.ATTRIBUTE5
, CONT.ATTRIBUTE6
, CONT.ATTRIBUTE7
, CONT.ATTRIBUTE8
, CONT.ATTRIBUTE9
, CONT.ATTRIBUTE10
, CONT.ATTRIBUTE11
, CONT.ATTRIBUTE12
, CONT.ATTRIBUTE13
, CONT.ATTRIBUTE14
, CONT.ATTRIBUTE15
, CONT.ATTRIBUTE16
, CONT.ATTRIBUTE17
, CONT.ATTRIBUTE18
, CONT.ATTRIBUTE19
, CONT.ATTRIBUTE20
, CONT.ATTRIBUTE21
, CONT.ATTRIBUTE22
, CONT.ATTRIBUTE23
, CONT.ATTRIBUTE24
, CONT.ATTRIBUTE25
, ADDR.ATTRIBUTE_CATEGORY
, ADDR.ATTRIBUTE1
, ADDR.ATTRIBUTE2
, ADDR.ATTRIBUTE3
, ADDR.ATTRIBUTE4
, ADDR.ATTRIBUTE5
, ADDR.ATTRIBUTE6
, ADDR.ATTRIBUTE7
, ADDR.ATTRIBUTE8
, ADDR.ATTRIBUTE9
, ADDR.ATTRIBUTE10
, ADDR.ATTRIBUTE11
, ADDR.ATTRIBUTE12
, ADDR.ATTRIBUTE13
, ADDR.ATTRIBUTE14
, ADDR.ATTRIBUTE15
FROM AR_LOOKUPS LOOK2
, AS_LOOKUPS LOOK3
, AR_LOOKUPS LOOK4
, FND_TERRITORIES_TL TERR
, RA_PHONES PHON
, RA_ADDRESSES ADDR
, RA_CUSTOMERS CUST
, RA_CONTACTS CONT
WHERE PHON.STATUS(+) = 'A'
AND CONT.CONTACT_ID = PHON.CONTACT_ID(+)
AND PHON.PRIMARY_FLAG(+) = 'Y'
AND CONT.TITLE =LOOK2.LOOKUP_CODE(+)
AND LOOK2.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND CONT.NATIVE_LANGUAGE = LOOK3.LOOKUP_CODE(+)
AND LOOK3.LOOKUP_TYPE(+) = 'LANGUAGE'
AND CUST.CUSTOMER_PROSPECT_CODE = LOOK4.LOOKUP_CODE(+)
AND LOOK4.LOOKUP_TYPE(+) = 'CUSTOMER_PROSPECT_CODE'
AND ADDR.COUNTRY = TERR.TERRITORY_CODE(+)
AND TERR.LANGUAGE(+) = USERENV('LANG')
AND ADDR.CUSTOMER_ID = CUST.CUSTOMER_ID
AND ADDR.CUSTOMER_ID = CONT.CUSTOMER_ID
AND ADDR.ADDRESS_ID = CONT.ADDRESS_ID