Search Results cust_addr

Overview

The AS_CONTACT_ADDRESSES_V view belongs to the AS - Sales Foundation product family within Oracle E-Business Suite and is documented as the "Contact addresses view." It presents a consolidated, reporting-friendly projection that combines customer contact records with both the customer's primary address and the contact's designated mailing address. In Oracle EBS 12.1.1 and 12.2.2 the underlying transactional tables in the Customer Relationship Management and Order Management schemas are highly normalized and heavily keyed, which makes ad hoc joins on contacts, customers, and addresses cumbersome for report developers and integration touchpoints. This view removes that complexity by pre-joining the relevant entities and resolving territory codes into human-readable territory short names through the FND_TERRITORIES_TL lookup.

Because the object is a view rather than a base table, it carries no storage of its own and is refreshed at query time. It is therefore well suited to operational reports, LOV queries, and interface extracts that require contact and address context without depending on the full Customer model.

Underlying Base Objects

The documented view text defines joins across the following base objects:

• RA_CONTACTS — the driving table, supplying contact identity, status, and job title.
• RA_CUSTOMERS — supplies customer name and number for the contact's parent account.
• RA_ADDRESSES (aliased twice) — once as the customer's address and once as the contact's mailing address. This double use of RA_ADDRESSES is why the view is frequently located when searching for ra_addresses.
• FND_TERRITORIES_TL (aliased twice) — resolves country codes to territory short names, restricted by USERENV('LANG').

The joins are: CONT.ADDRESS_ID = CUST_ADDR.ADDRESS_ID, CUST.CUSTOMER_ID = CONT.CUSTOMER_ID, and an outer join CONT.MAILING_ADDRESS_ID = CONT_ADDR.ADDRESS_ID(+). Territory resolution uses outer joins as well, so records persist even when a territory translation is missing.

Key Columns

The view exposes contact attributes, customer attributes, and two sets of address attributes. Notable columns include:

• CONTACT_ID, LAST_NAME, FIRST_NAME, CONTACT_NUMBER, JOB_TITLE — standard contact identification.
• CONTACT_STATUS_CODE / CONTACT_ACTIVE — the status flag and a derived asterisk indicator produced by DECODE(CONT.STATUS,'A','*',NULL).
• CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_NUMBER — parent account details.
• CUSTOMER_ADDRESS_ID and the unprefixed address columns (ADDRESS1–COUNTY, COUNTRY) — the customer's address.
• MAILING_ADDRESS_ID and the MAILING_* columns — the contact's separately stored mailing address, if any.
• STATE_PROVINCE — a concatenation of state and province using a '/' separator.
• EMAIL_ADDRESS — contact email for correspondence.

Common Use Cases and Queries

Typical uses include contact roster reports, mailing label extracts, and integration payloads that need a resolved address in a single query. A representative query returning active contacts with their mailing city is:

• SELECT customer_number, last_name, first_name, mailing_city, mailing_country
• FROM as_contact_addresses_v
• WHERE contact_status_code = 'A'
• ORDER BY customer_name, last_name;

Because the mailing address is outer-joined, consumers should apply NVL or COALESCE between the mailing and customer address columns when a guaranteed address is required. Note that the view is documented as "Not implemented in this database," indicating it may not exist in every environment and should be verified before deployment in 12.1.1 or 12.2.2.