Results for “as_contact_names_v”

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

Overview

AS_CONTACT_NAMES_V is a reporting view in the Oracle E-Business Suite Sales Foundation (AS) module. As its description indicates, it is a "Contact names view" that consolidates contact, customer, address, and territory information into a single flattened result set for query and reporting purposes. The view is defined in the ETRM metadata as documented under release 12.2.2 (and applicable to 12.1.1), but is annotated "Not implemented in this database," meaning the definition exists in the reference metadata without a corresponding deployed object in that particular environment. This is a common characteristic of seed or reference views that ship with documentation but may not be compiled in every instance.

The view's practical role is to provide an integration and reporting surface for contact records without requiring the caller to join RA_CONTACTS, RA_CUSTOMERS, RA_ADDRESSES, and FND_TERRITORIES_TL manually. For a user searching on "contact_number," the view is particularly relevant because CONTACT_NUMBER is one of its directly exposed columns, making it a convenient single-source object for contact number lookups alongside the associated customer and address context.

Underlying Base Objects

The ETRM metadata documents no referenced base objects explicitly, but the view text itself reveals the tables involved. AS_CONTACT_NAMES_V is constructed from four sources:

• RA_CONTACTS (aliased CONT) — the primary contact records, supplying CONTACT_ID, name, CONTACT_NUMBER, JOB_TITLE, STATUS, CUSTOMER_ID, and ADDRESS_ID.
• RA_CUSTOMERS (aliased CUST) — the customer master, joined on CONT.CUSTOMER_ID = CUST.CUSTOMER_ID, supplying CUSTOMER_NAME and CUSTOMER_NUMBER.
• RA_ADDRESSES (aliased ADDR) — joined on CONT.ADDRESS_ID = ADDR.ADDRESS_ID, supplying ADDRESS1, CITY, STATE, PROVINCE, and POSTAL_CODE.
• FND_TERRITORIES_TL (aliased TERR) — joined with an outer (+) operator on ADDR.COUNTRY = TERR.TERRITORY_CODE and TERR.LANGUAGE = USERENV('LANG'), supplying TERRITORY_SHORT_NAME.

The outer join to FND_TERRITORIES_TL means contacts with addresses lacking a matching territory (or lacking a translation in the session language) are still returned. The relationship is fundamentally a contact-centric star: one row per contact, enriched by its owning customer and address.

Key Columns

• CONTACT_ID — Primary key of the contact in RA_CONTACTS.
• LAST_NAME / FIRST_NAME — The contact's name components.
• CONTACT_NUMBER — The contact number, the column of primary interest for the originating search.
• JOB_TITLE — The contact's job title.
• STATUS_CODE / STATUS — Contact status; ACTIVE is a DECODE that returns '*' when STATUS is 'A'.
• CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_NUMBER — The owning customer's identifiers and name.
• ADDRESS_ID, ADDRESS1, CITY, STATE, PROVINCE, POSTAL_CODE — Address attributes; STATE_PROVINCE concatenates state and province with a '/' separator via nested DECODE.
• COUNTRY (TERRITORY_SHORT_NAME) — Territory short name from FND_TERRITORIES_TL.

Note that the metadata column list also includes ACCOUNT_CODE, which does not appear in the provided view text; it may be supplied by a variant definition or a dependent object.

Common Use Cases and Queries

Typical scenarios include contact directory reports, customer 360 views, and inbound integration extracts keyed on contact number or customer number. A representative query retrieving contacts by number is:

• SELECT contact_id, contact_number, last_name, first_name, customer_number, customer_name, city, state, province, country, active FROM as_contact_names_v WHERE contact_number = :p_contact_number;
• SELECT customer_number, contact_number, last_name, first_name FROM as_contact_names_v WHERE customer_id = :p_customer_id ORDER BY last_name, first_name;

Because COUNTRY is derived through the language-dependent TERRITORY_SHORT_NAME, results vary with the session language. Confirm that the view is compiled in the target instance before relying on it, since the ETRM annotation states it is "Not implemented in this database." Where the object is unavailable, an equivalent query can be assembled directly from RA_CONTACTS, RA_CUSTOMERS, RA_ADDRESSES, and FND_TERRITORIES_TL using the join conditions shown in the view text.