Results for “as_customer_leads_v”

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

Overview

AS_CUSTOMER_LEADS_V is a reporting view in the Oracle E-Business Suite Sales Foundation module (application code AS). It presents customer lead records alongside their associated address, territory, and lookup information in a single, denormalized result set. The view joins the leads base table to the addresses table, the territories translation table, and the lookup values table so that consumers can retrieve descriptive attributes without performing the joins themselves. In the ETRM documentation for release 12.2.2, the view is described simply as "Customer leads view" and is marked as not implemented in the reference database, meaning the definition is delivered but the object may not be instantiated in every environment.

Because the view surfaces both a raw status code and a decoded lookup meaning, it is particularly relevant when users search on terms such as lead_status. The literal 'LEAD_STATUS' appears in the view definition as the lookup type used to resolve the STATUS column into a user-facing meaning.

Underlying Base Objects

The documented definition of the view references the following base tables and objects:

• ASLKP.MEANING from AS_LOOKUPS — resolves the lead status lookup code to its display meaning.
• RA_ADDRESSES ADDR — the customer address source, supplying street, city, and postal attributes.
• FND_TERRITORIES_TL TERR — the territory translation table, supplying the short territory name.
• AS_OPPORTUNITIES LEAD — despite the view name, the lead source is the AS_OPPORTUNITIES table aliased as LEAD, from which lead identifiers, status, description, and rank are drawn.

The joins are established on LEAD.ADDRESS_ID = ADDR.ADDRESS_ID, a country-to-territory join, and a status-to-lookup join filtered by lookup type 'LEAD_STATUS'. The lookup and territory joins carry the outer-join (+) operator, so leads survive even when no matching lookup or territory row exists. The ETRM metadata documents no referenced base objects explicitly, but the embedded view text provides the authoritative join logic.

Key Columns

• LEAD_ID, LEAD_NUMBER — primary identifiers for the lead record.
• STATUS / STATUS_CODE — the lead status code stored on the lead; when decoded, the meaning appears in the STATUS column populated from the lookup.
• DESCRIPTION — free-text description of the lead.
• RANK / RANK_CODE — the lead's ranking value.
• CUSTOMER_ID, ACCOUNT_CODE — the associated customer or account.
• ADDRESS_ID, ADDRESS1, CITY, STATE, PROVINCE, POSTAL_CODE, COUNTRY — address attributes from RA_ADDRESSES.
• TERRITORY_SHORT_NAME — the territory associated with the lead's country, resolved through FND_TERRITORIES_TL.

Common Use Cases and Queries

The view is typically used for lead reporting, status distribution analysis, and integrations that need decoded status text. A representative query filtering on lead status is:

• SELECT lead_number, status, meaning, city, territory_short_name FROM as_customer_leads_v WHERE status = (SELECT meaning FROM as_lookups WHERE lookup_type = 'LEAD_STATUS' AND lookup_code = 'ACTIVE');
• SELECT status, COUNT(*) FROM as_customer_leads_v GROUP BY status ORDER BY 2 DESC; — produces a status breakdown.
• SELECT lead_id, lead_number, address1, city, territory_short_name FROM as_customer_leads_v WHERE customer_id = :customer_id; — lists leads tied to a customer.

Analysts should note the documented caveat that this view is not implemented in the reference database; availability must be verified in the target environment before use in production reporting.