Results for “as_account_codes_v”

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

Overview

AS_ACCOUNT_CODES_V is a reporting view in the Oracle E-Business Suite (EBS) Sales Foundation module (application short name AS). It exposes account-level information that combines customer and address attributes into a single denormalized result set, which is why it is presented under the description "Account codes." In the context of EBS 12.1.1 and 12.2.2, the view serves as a convenient read-only projection for reporting, inquiry, and integration scenarios where a joint view of customer identity and address detail is required without navigating the underlying transactional tables individually.

The view is not a base or interface table; it is a query-only construct. According to the ETRM metadata, the view is documented as "Not implemented in this database," meaning that in the environment from which the metadata was extracted, the object did not physically exist. This is a common situation in EBS metadata catalogs, where a view may be present in some installations or patch levels but absent in others. Consequently, developers and DBAs should verify the object's presence in the target instance before referencing it in custom reports or concurrent programs.

Underlying Base Objects

The ETRM metadata describes the view text as a three-table join. The documented base objects are:

• RA_CUSTOMERS — the customer master table (alias CUST), supplying customer number, name, and status.
• RA_ADDRESSES — the customer address table (alias ADDR), supplying address lines, city, state, province, county, postal code, country, and address status.
• FND_TERRITORIES_TL — the translated territories table (alias TERR), supplying the territory short name used as the country label, joined with the language condition TERR.LANGUAGE(+) = USERENV('LANG').

The join conditions are ADDR.CUSTOMER_ID = CUST.CUSTOMER_ID and ADDR.COUNTRY = TERR.TERRITORY_CODE(+). The outer-join syntax on the territory table ensures that addresses with a country code lacking a matching translated territory row are still returned, with a null territory short name. The metadata reports no additionally documented referenced base objects beyond these three.

Key Columns

The view projects the following documented columns:

• ACCOUNT_CODE — the account code derived from the address/customer relationship, the column that gives the view its name.
• CUSTOMER_ID — the unique identifier from RA_CUSTOMERS (and referenced by RA_ADDRESSES).
• ADDRESS_ID — the unique identifier from RA_ADDRESSES.
• CUSTOMER_NAME and CUSTOMER_NUMBER — customer display name and the business-facing customer number.
• CUSTOMER_STATUS and ADDRESS_STATUS — the active/inactive status flags for the customer record and the address record respectively.
• ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4 — the street address lines.
• CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE — geographic attributes of the address.
• COUNTRY — the territory short name resolved through FND_TERRITORIES_TL.

The inclusion of both ADDRESS_STATUS and CUSTOMER_STATUS is significant. Because the view text carries ADDR.STATUS and CUST.STATUS directly, applications that need to determine whether an address is currently active—the scenario often associated with a search for "address_status"—can filter on the exposed status column without joining back to RA_ADDRESSES.

Common Use Cases and Queries

Typical use cases include customer account listings, address validation extracts, territory-based reporting, and integration feeds that require customer number, name, and current status alongside mailing address detail. A representative query filtering on the address status column is shown below.

SELECT account_code,
       customer_number,
       customer_name,
       address1,
       city,
       state,
       postal_code,
       country,
       address_status,
       customer_status
FROM   as_account_codes_v
WHERE  address_status = 'A'
ORDER  BY customer_number;

To list all active accounts for a specific country, the COUNTRY column resolved from the territory table can be constrained directly:

SELECT customer_id,
       customer_name,
       address_id,
       address1,
       country
FROM   as_account_codes_v
WHERE  customer_status = 'A'
AND    country = 'US';

Because the view is documented as possibly not implemented in a given database, scripts referencing AS_ACCOUNT_CODES_V should be preceded by a metadata check such as querying ALL_VIEWS or DBA_OBJECTS. Where the view is unavailable, the equivalent result set can be reproduced by joining RA_CUSTOMERS, RA_ADDRESSES, and FND_TERRITORIES_TL directly, mirroring the join conditions documented in the view text. This fallback preserves both the outer join on territory and the language-sensitive territory lookup, ensuring consistent results across EBS 12.1.1 and 12.2.2 environments.