Search Results as_lookups1




Overview
Explore in AI Chat

Overview

APPS.AMS_DS_SALES_LEADS_V is a denormalized reporting view in Oracle E-Business Suite that consolidates sales lead header information, lead line details, lookup translations, and descriptive flexfield attributes into a single queryable object. Its name follows the AMS (Marketing) module convention, with the "DS" prefix typically indicating a data source view intended for downstream reporting, analytics, or integration consumption rather than operational transaction entry. Within Oracle EBS 12.1.1 and 12.2.2, the view is owned by the APPS schema and is primarily referenced by Oracle Sales, Sales Lead management, and Marketing dashboards, as well as custom BI Publisher reports and OBIEE/OAC extracts.

The view resolves the common reporting requirement of exposing business-meaningful descriptions in place of encoded identifiers. Rather than forcing consumers to join to AS_LOOKUPS, AS_STATUSES_TL, or MTL_UNITS_OF_MEASURE independently, the view performs those joins once, allowing report authors to consume fully resolved attributes such as lead status, budget status, timeframe, rank, and decline reasons.

Underlying Base Objects

Per the ETRM metadata, AMS_DS_SALES_LEADS_V is defined over the following documented base objects:

  • AS_SALES_LEADS (synonym) — the primary header table supplying lead identity, status, customer and address references, assignment information, promotion linkage, and descriptive flexfield columns.
  • AS_SALES_LEAD_LINES (synonym) — supplies line-level attributes such as inventory item, organization, UOM, quantity, category, and line-level flexfield columns.
  • AS_SALES_LEAD_RANKS_TL (synonym) — provides the translatable rank meaning exposed as RANK.
  • AS_STATUSES_TL (synonym) — referenced for status translations.
  • AS_LOOKUPS (view) — joined multiple times (aliased AS_LOOKUPS1 through AS_LOOKUPS5) to translate status codes, budget status, decision timeframe, decline reason, and vehicle response code into meanings.
  • ASO_I_SALES_CHANNELS_V (view) — resolves the sales channel description from CHANNEL_CODE.
  • AMS_P_SOURCE_CODES_V (view) — supplies promotion name and source code.
  • JTF_RS_RESOURCE_EXTNS (synonym) — provides the assigned resource name.
  • MTL_SYSTEM_ITEMS_VL (view), MTL_UNITS_OF_MEASURE (synonym), and MTL_DEFAULT_CATEGORY_SETS (synonym) — resolve item concatenated segments, item description, unit of measure, and category set context.

The AS_LOOKUPS1 alias specifically corresponds to the LEAD_STATUS column, translating AS_SALES_LEADS.STATUS_CODE into a user-visible meaning.

Key Columns

The view exposes lead header identifiers (SALES_LEAD_ID, LEAD_NUMBER), audit columns (CREATION_DATE, LAST_UPDATE_DATE), and status information (STATUS_CODE along with the translated LEAD_STATUS). Customer and contact context is delivered through CUSTOMER_ID, ADDRESS_ID, PRIMARY_CONTACT_PARTY_ID, PRIMARY_CNT_PERSON_PARTY_ID, and PRIMARY_CONTACT_PHONE_ID. Assignment data includes ASSIGN_TO_PERSON_ID, ASSIGN_TO_SALESFORCE_ID, ASSIGN_SALES_GROUP_ID, and RESOURCE_NAME.

Lookup-derived descriptive columns include BUDGET_STATUS, TIME_FRAME, RANK, DECLINE_REASON, and VEH_RESP_CODE_MEANING. Promotion context is provided by SOURCE_PROMOTION_ID, SOURCE_PROMOTION_NAME, and SOURCE_PROMOTION_CODE, while CHANNEL_CODE resolves to SALES_CHANNEL. Sixty flexfield columns (ATTRIBUTE_CATEGORY plus ATTRIBUTE1–15 for both header and line) are exposed, with line-level attributes named LINE_ATTRIBUTE1 through LINE_ATTRIBUTE15. Line item details include CATEGORY_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, UOM_CODE, QUANTITY, UNIT_OF_MEASURE_TL, CONCATENATED_SEGMENTS, ITEM_DESCRIPTION, and CATEGORY_SET_ID.

Common Use Cases and Queries

Typical scenarios include lead pipeline dashboards, conversion-rate analysis by channel and promotion, and extracts feeding CRM or data-warehouse platforms. Because the view flattens header, line, and lookup information, it is suitable for direct reporting without additional joins.

A representative query listing open leads with resolved status and channel:

  • SELECT lead_number, lead_status, sales_channel, resource_name, budget_amount, currency_code FROM apps.ams_ds_sales_leads_v WHERE qualified_flag = 'Y' ORDER BY creation_date DESC;

A promotion effectiveness query aggregating lead counts by source:

  • SELECT source_promotion_name, sales_channel, COUNT(*) lead_count FROM apps.ams_ds_sales_leads_v WHERE creation_date >= :p_start_date GROUP BY source_promotion_name, sales_channel;

A line-level extract for item demand analysis:

  • SELECT lead_number, concatenated_segments, item_description, quantity, unit_of_measure_tl FROM apps.ams_ds_sales_leads_v WHERE inventory_item_id IS NOT NULL;

Because AS_LOOKUPS is joined multiple times and the view fans out across lines, consumers should apply appropriate filters (for example, on ORGANIZATION_ID or a single line) to avoid row multiplication when header-level counts are required.