Results for “as_location_contacts_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AS_LOCATION_CONTACTS_V is a reporting view in the AS (Sales Foundation) product family of Oracle E-Business Suite, exposing the relationship between customers, their addresses or site uses, and the contacts and telephone numbers associated with those locations. Its documented description in the ETRM repository is simply "Location contacts view," and it is classified as not implemented in the reference database, meaning the view definition shipped as documented text rather than as a live object in that particular instance. Functionally the view denormalizes data that is otherwise spread across the Trading Community Architecture (TCA) and the legacy Receivables customer model, allowing a single query to return a customer, the address or site use at which the contact resides, the contact identity, and the contact telephone number in one row. This makes it well suited to reporting, extracts, and integration scenarios where a flat "who do I contact, and where" result set is required without writing the multi-table join manually.

Underlying Base Objects

The view is defined over a documented join involving ten base tables. Customer-level attributes come from RA_CUSTOMERS. Address and location attributes come from RA_ADDRESSES, while site-use context is supplied by RA_SITE_USES. Contact details come from RA_CONTACTS and telephone details from RA_PHONES. Four lookup joins supply descriptive meaning: AR_LOOKUPS appears three times (aliased LOOK1, LOOK3, and LOOK5) and AS_LOOKUPS appears once (aliased LOOK4), providing site use code meaning and contact status or job title meaning respectively. Territory information is resolved through FND_TERRITORIES_TL. The join is anchored on ADDR.CUSTOMER_ID = CUST.CUSTOMER_ID, with the remaining relationships driven from the address, site use, contact, and phone records. Because these are the legacy RA_* customer tables rather than the newer HZ_* TCA tables, the view reflects the pre-TCA data model and is therefore most useful against data that has not been migrated exclusively into the HZ schema.

Key Columns

• Customer columns: CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_NUMBER, CUSTOMER_KEY, TAX_REFERENCE, STATUS, CUSTOMER_PROSPECT_CODE, and COMPETITOR_FLAG identify the customer and its classification.
• Address/location columns: ADDRESS_ID, ADDRESS_KEY, ADDRESS1 through ADDRESS4, CITY, POSTAL_CODE, STATE, COUNTY, PROVINCE, COUNTRY, DO_NOT_MAIL_FLAG, and KEY_ACCOUNT_FLAG describe the physical location.
• Site use columns: SITE_USE_ID, LOCATION, SITE_USE_CODE, and the LOOK1.MEANING derived from that code.
• Contact columns: CONTACT_ID, CONTACT_KEY, CONTACT_NUMBER, FIRST_NAME, LAST_NAME, JOB_TITLE, STATUS, the descriptive meaning from LOOK3 and LOOK4, and ATTRIBUTE1 through ATTRIBUTE25 for descriptive flexfield content.
• Phone columns: PHONE_ID, AREA_CODE, PHONE_NUMBER, and EXTENSION provide dialing details for the contact.
• Territory column: TERRITORY_SHORT_NAME supplies the localized territory abbreviation.

Common Use Cases and Queries

Typical uses include producing customer contact directories, feeding downstream notification or mailing processes, and validating that contacts and phone numbers are populated for key sites. A representative query that retrieves a contact person and number for a given customer name or city follows.

SELECT customer_name, city, first_name, last_name,
       job_title, area_code, phone_number, extension
  FROM as_location_contacts_v
 WHERE customer_name LIKE :p_customer
   AND do_not_mail_flag = 'N';

Administrators investigating a specific account, such as a customer site in a named locality, can filter by CITY or POSTAL_CODE and return the associated contact and telephone columns for verification. Because the view is documented as not implemented in some databases, availability should be confirmed in the target instance with a query against ALL_VIEWS or ALL_OBJECTS before the view is referenced in concurrent programs or interfaces.