View: AS_LOCATION_CONTACTS_V

View Text

SELECT CUST.CUSTOMER_ID
, CUST.CUSTOMER_NAME
, CUST.CUSTOMER_NUMBER
, CUST.TAX_REFERENCE
, CUST.STATUS
, SITE.SITE_USE_ID
, SITE.LOCATION
, SITE.SITE_USE_CODE
, LOOK1.MEANING
, 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
, PHON.PHONE_ID
, PHON.AREA_CODE
, PHON.PHONE_NUMBER
, PHON.EXTENSION
, CUST.CUSTOMER_KEY
, ADDR.ADDRESS_KEY
, CONT.CONTACT_KEY
, CONT.CONTACT_NUMBER
, LOOK3.MEANING
, CONT.JOB_TITLE
, LOOK4.MEANING
, ADDR.DO_NOT_MAIL_FLAG
, CONT.DO_NOT_MAIL_FLAG
, ADDR.ADDRESS3
, ADDR.ADDRESS4
, ADDR.COUNTY
, ADDR.PROVINCE
, TERR.TERRITORY_SHORT_NAME
, ADDR.COUNTRY
, LOOK5.MEANING
, CUST.CUSTOMER_PROSPECT_CODE
, CUST.COMPETITOR_FLAG
, ADDR.KEY_ACCOUNT_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 RA_CUSTOMERS CUST
, RA_SITE_USES SITE
, RA_ADDRESSES ADDR
, RA_CONTACTS CONT
, RA_PHONES PHON
, AR_LOOKUPS LOOK1
, AR_LOOKUPS LOOK3
, AS_LOOKUPS LOOK4
, AR_LOOKUPS LOOK5
, FND_TERRITORIES_TL TERR
WHERE ADDR.CUSTOMER_ID = CUST.CUSTOMER_ID
AND ADDR.ADDRESS_ID = CONT.ADDRESS_ID
AND ADDR.ADDRESS_ID = SITE.ADDRESS_ID
AND ADDR.CUSTOMER_ID = CONT.CUSTOMER_ID
AND SITE.SITE_USE_CODE = LOOK1.LOOKUP_CODE(+)
AND LOOK1.LOOKUP_TYPE(+) = 'SITE_USE_CODE'
AND CUST.STATUS = 'A'
AND ADDR.STATUS = 'A'
AND SITE.STATUS = 'A'
AND PHON.STATUS(+)= 'A'
AND CONT.CONTACT_ID = PHON.CONTACT_ID(+)
AND PHON.PRIMARY_FLAG(+) = 'Y'
AND CONT.TITLE = LOOK3.LOOKUP_CODE(+)
AND LOOK3.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND CONT.NATIVE_LANGUAGE = LOOK4.LOOKUP_CODE(+)
AND LOOK4.LOOKUP_TYPE(+) = 'LANGUAGE'
AND CUST.CUSTOMER_PROSPECT_CODE = LOOK5.LOOKUP_CODE(+)
AND LOOK5.LOOKUP_TYPE(+) = 'CUSTOMER_PROSPECT_CODE'
AND ADDR.COUNTRY = TERR.TERRITORY_CODE(+)
AND TERR.LANGUAGE(+) = USERENV('LANG')

Columns