Product: | AS - Sales Foundation |
---|---|
Description: | Shows accounts and their primary phone number |
Implementation/DBA Data: | Not implemented in this database |
SELECT CUST.ROWID
, ADDR.ROWID
, PHON.ROWID
, CUST.CUSTOMER_ID
, CUST.CUSTOMER_NAME
, CUST.CUSTOMER_NUMBER
, CUST.STATUS
, CUST.CUSTOMER_PROSPECT_CODE
, CUST.ORIG_SYSTEM_REFERENCE
, CUST.CUSTOMER_KEY
, ADDR.ADDRESS_ID
, ADDR.ADDRESS_STYLE
, ADDR.STATUS
, ADDR.ADDRESS_LINES_PHONETIC
, ADDR.ORIG_SYSTEM_REFERENCE
, ADDR.ADDRESS1
, ADDR.ADDRESS2
, ADDR.ADDRESS3
, ADDR.ADDRESS4
, ADDR.CITY
, ADDR.POSTAL_CODE
, ADDR.STATE
, ADDR.PROVINCE
, ADDR.COUNTY
, ADDR.COUNTRY
, ADDR.LAST_UPDATE_DATE
, ADDR.LAST_UPDATED_BY
, ADDR.CREATION_DATE
, ADDR.CREATED_BY
, ADDR.LAST_UPDATE_LOGIN
, 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
, ADDR.ADDRESS_KEY
, ADDR.REFERENCE_USE_FLAG
, CUST.ANALYSIS_FY
, CUST.FISCAL_YEAREND_MONTH
, CUST.NUM_OF_EMPLOYEES
, CUST.POTENTIAL_REVENUE_CURR_FY
, CUST.POTENTIAL_REVENUE_NEXT_FY
, CUST.COMPETITOR_FLAG
, CUST.THIRD_PARTY_FLAG
, CUST.YEAR_ESTABLISHED
, ADDR.DO_NOT_MAIL_FLAG
, PHON.PHONE_ID
, PHON.STATUS
, PHON.PHONE_TYPE
, PHON.AREA_CODE
, PHON.PHONE_NUMBER
, PHON.EXTENSION
, PHON.PRIMARY_FLAG
, PHON.ORIG_SYSTEM_REFERENCE
, TERR.TERRITORY_SHORT_NAME
, LOOK1.MEANING
, ADDR.KEY_ACCOUNT_FLAG
, CUST.MISSION_STATEMENT
, CUST.TAX_REFERENCE
, CUST.URL
FROM AR_LOOKUPS LOOK1
, RA_PHONES PHON
, FND_TERRITORIES_TL TERR
, RA_CUSTOMERS CUST
, RA_ADDRESSES ADDR
WHERE LOOK1.LOOKUP_TYPE(+) = 'COMMUNICATION_TYPE'
AND PHON.PHONE_TYPE = LOOK1.LOOKUP_CODE(+)
AND PHON.PRIMARY_FLAG(+) = 'Y'
AND PHON.CONTACT_ID(+) IS NULL
AND ADDR.ADDRESS_ID = PHON.ADDRESS_ID(+)
AND ADDR.COUNTRY = TERR.TERRITORY_CODE(+)
AND TERR.LANGUAGE(+) = USERENV('LANG')
AND ADDR.CUSTOMER_ID = CUST.CUSTOMER_ID
AND ADDR.CUSTOMER_ID > 0