Results for “as_locations_v”

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

Overview

AS_LOCATIONS_V is a reporting view in the Oracle E-Business Suite Sales Foundation (AS) module, described in the ETRM metadata as "Locations view." It consolidates customer, address, and site-use information into a single flattened result set, exposing data that would otherwise require joining multiple base tables. Its primary role is to support reporting and integration scenarios in which a complete customer location profile is required, including the customer identity, the physical address, the site-use classification, the associated territory, and descriptive lookups.

Because the view joins the customer master, addresses, site uses, territories, and lookups, it presents a denormalized picture of "who the customer is" combined with "where the location is" and "how that location is used." This makes it suitable for operational reporting, data extracts, and outbound interfaces where a single query must resolve customer-to-location relationships without hand-coding the underlying join logic.

Underlying Base Objects

The view is defined over the following documented base objects:

• RA_CUSTOMERS — the customer master, supplying customer identity, number, status, tax reference, customer key, prospect code, and competitor flag.
• RA_ADDRESSES — the customer address records, supplying address lines, city, postal code, state, province, county, country, and address attributes.
• RA_SITE_USES — the site-use records that associate a customer and address with a specific usage code and status.
• AR_LOOKUPS — referenced twice (aliases LOOK1 and LOOK3) to translate the site-use code and the customer prospect code into descriptive meanings.
• FND_TERRITORIES_TL — the territory translations table, supplying the territory short name for the address country.

The joins are: address to customer on CUSTOMER_ID; address to site use on ADDRESS_ID; site use to lookup on SITE_USE_CODE; address country to territory on TERRITORY_CODE with language matching the session language; and customer prospect code to lookup. All lookup and territory joins are outer joins, and the query filters to active site uses (SITE.STATUS = 'A'). The metadata records the object as not implemented in the reference database, meaning the view text is documented but may not be deployed in every environment.

Key Columns

The view exposes columns spanning the joined sources:

• CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_NUMBER, CUSTOMER_KEY — customer identity and the surrogate key used in integration.
• TAX_REFERENCE, CUSTOMER_STATUS_CODE, CUSTOMER_PROSPECT_CODE, COMPETITOR_FLAG — customer-level attributes, including the prospect classification resolved through AR_LOOKUPS.
• SITE_USE_ID, SITE_USE_CODE, SITE_USE, LOCATION — the site-use record and its translated meaning.
• ADDRESS_ID, ADDRESS_KEY, ADDRESS1–ADDRESS4, CITY, POSTAL_CODE, STATE, PROVINCE, COUNTY, COUNTRY, COUNTRY_CODE — the physical address and country details, with the country name resolved via FND_TERRITORIES_TL.
• ACCOUNT_DO_NOT_MAIL_FLAG, KEY_ACCOUNT_FLAG, ADDR_ATTRIBUTE_CATEGORY, ADDR_ATTRIBUTE1 through ADDR_ATTRIBUTE15 — address-level flags and the descriptive flexfield attribute columns.

Common Use Cases and Queries

Typical uses include generating customer location listings, validating ship-to and bill-to site data, and feeding downstream systems with normalized address information. A simple retrieval follows.

SELECT CUSTOMER_NAME, CUSTOMER_NUMBER, SITE_USE, ADDRESS1, CITY, STATE, POSTAL_CODE, COUNTRY FROM AS_LOCATIONS_V WHERE CUSTOMER_NUMBER = :p_customer_number ORDER BY SITE_USE;

Filtering on address geography is equally common:

SELECT CUSTOMER_ID, CUSTOMER_NAME, CITY, POSTAL_CODE, TERRITORY_SHORT_NAME FROM AS_LOCATIONS_V WHERE COUNTRY = 'AU' AND CITY = :p_city;

Because only active site uses are returned, the view is well suited to reporting on currently valid customer locations; historical or inactive site-use records must be obtained from the underlying RA_SITE_USES table directly.