Search Results customer_prospect




Overview
Explore in AI Chat

Overview

The AS_CUSTOMER_ACCOUNTS_V view belongs to the Oracle EBS AS – Sales Foundation product family and is documented in ETRM 12.2.2 as the "Customer, accounts view used in Account form." It presents a single denormalized row per customer account, combining customer header attributes from the Receivables customer entity with the corresponding address attributes and decoded lookup values. Its primary purpose in Oracle EBS is to back the Account form and related Sales Foundation user interfaces, but it is equally suitable for reporting, integration extracts, and custom form or OAF development where a flat customer-account picture is required. The view is a standard read-only database view rather than a table, so it performs no insert, update, or delete operations of its own. The ETRM metadata notes that the view was "not implemented in this database," meaning the object exists as a shipped definition but was not instantiated in the reference environment used to populate the documentation.

Underlying Base Objects

The ETRM excerpt does not enumerate referenced base objects under the "Referenced base objects" attribute, but the view text exposes the join structure explicitly. It is defined over four sources:

  • RA_CUSTOMERS CUST – the customer header record, supplying customer identity, number, tax reference, key, status, competitor flag, and prospect code.
  • RA_ADDRESSES ADDR – the customer's addresses, joined as CUST.CUSTOMER_ID = ADDR.CUSTOMER_ID(+), preserving customers that have no address row.
  • FND_TERRITORIES_TL TERR – the translated territory table, joined by ADDR.COUNTRY = TERR.TERRITORY_CODE(+) and constrained to the session language via USERENV('LANG'), providing territory short name and country information.
  • AR_LOOKUPS LOOK2 – the Receivables lookup table filtered to LOOKUP_TYPE = 'CUSTOMER_PROSPECT_CODE', joined to CUST.CUSTOMER_PROSPECT_CODE = LOOK2.LOOKUP_CODE(+) to decode the prospect classification into a user-facing meaning.

All foreign joins use the Oracle outer-join syntax, so the customer row is always retained even when address, territory, or lookup data is absent.

Key Columns

The view exposes forty-plus columns. The customer identification set includes CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_NUMBER, TAX_REFERENCE, CUSTOMER_KEY, and the status columns CUSTOMER_STATUS_CODE and ADDRESS_STATUS_CODE, the latter derived via NVL(ADDR.STATUS, CUST.STATUS) so that the customer status is returned when no address row exists. Address attributes include ADDRESS_ID, ADDRESS_KEY, ADDRESS1 through ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, and COUNTRY, with COUNTRY_CODE and TERRITORY_SHORT_NAME supplied from the territory translation. Classification and control flags include CUSTOMER_PROSPECT_CODE, CUSTOMER_PROSPECT (the decoded meaning), COMPETITOR_FLAG, ACCOUNT_DO_NOT_MAIL_FLAG, REFERENCE_USE_FLAG, and KEY_ACCOUNT_FLAG. Finally, the full descriptive flexfield set ADDR_ATTRIBUTE_CATEGORY and ADDR_ATTRIBUTE1 through ADDR_ATTRIBUTE15 is exposed for address-level flexfield reporting.

Common Use Cases and Queries

Typical uses include validating prospect and customer account data, producing address and territory reports, and extracting account lists for integrations. A basic query selecting active prospects is:

SELECT customer_id, customer_number, customer_name,
       customer_prospect, territory_short_name, city
FROM   as_customer_accounts_v
WHERE  customer_prospect_code = 'P'
  AND  customer_status_code = 'A';

Because the view flattens customer, address, and lookup data, it is convenient for reconciliation against RA_CUSTOMERS and RA_ADDRESSES, and for supplying the Account form's query block. As with any EBS view containing USERENV('LANG'), results are language-dependent, and callers should be aware that outer joins can return null address columns for customers lacking an address record.