Search Results source_promotion_code

Overview

AMS_DS_SALES_LEADS_V is a denormalized reporting view owned by the APPS schema in Oracle E-Business Suite, registered under the AMS (Marketing) product family. It exposes sales lead data captured in Oracle Marketing and Oracle Sales foundation tables, presenting a single flat record per lead together with its associated promotion source, status meanings, resource assignment, and lead line item detail. The view is intended for downstream reporting, data warehouse extraction, and integration scenarios where a stable, human-readable projection of the lead entity is preferable to joining the normalized transactional tables directly.

Because it pre-resolves lookup codes, channel identifiers, promotion source attributes, and item concatenated segments, AMS_DS_SALES_LEADS_V is particularly relevant to lead-generation analytics, campaign attribution, and territory/pipeline reporting in EBS 12.1.1 and 12.2.2.

Underlying Base Objects

The view is defined over a combination of synonyms, views, and lookup views in the APPS schema, including:

• AS_SALES_LEADS and AS_SALES_LEAD_LINES — the primary transaction tables supplying lead and lead-line attributes.
• AS_SALES_LEAD_RANKS_TL and AS_STATUSES_TL — translated rank and status definitions.
• AS_LOOKUPS — used multiple times (aliased AS_LOOKUPS1 through AS_LOOKUPS5) to resolve codes such as lead status, budget status, decision timeframe, decline reason, and vehicle response meaning.
• AMS_P_SOURCE_CODES_V — the promotion source view supplying SOURCE_PROMOTION_NAME and SOURCE_PROMOTION_CODE.
• ASO_I_SALES_CHANNELS_V — resolves SALES_CHANNEL from CHANNEL_CODE.
• JTF_RS_RESOURCE_EXTNS — provides the assigned resource name.
• MTL_SYSTEM_ITEMS_VL, MTL_UNITS_OF_MEASURE, MTL_DEFAULT_CATEGORY_SETS, and a denormalized category structure supplying item, UOM, and category lineage columns.

Key Columns

Core lead identifiers include SALES_LEAD_ID, LEAD_NUMBER, CREATION_DATE, and LAST_UPDATE_DATE. Status and classification columns include STATUS_CODE with its decoded LEAD_STATUS, QUALIFIED_FLAG, ACCEPT_FLAG, URGENT_FLAG, LEAD_RANK_CODE and its RANK meaning, and CLOSE_REASON.

Attribution and channel columns include CHANNEL_CODE, SALES_CHANNEL, ORIG_SYSTEM_REFERENCE, VEHICLE_RESPONSE_CODE, and VEH_RESP_CODE_MEANING. The promotion attribution fields relevant to the source_promotion_code search are SOURCE_PROMOTION_ID, SOURCE_PROMOTION_NAME, and SOURCE_PROMOTION_CODE — the last being the alphanumeric promotion source identifier carried from AMS_P_SOURCE_CODES_V.

Assignment and party columns include ASSIGN_TO_PERSON_ID, ASSIGN_TO_SALESFORCE_ID, ASSIGN_SALES_GROUP_ID, PRIMARY_CONTACT_PARTY_ID, PRIMARY_CNT_PERSON_PARTY_ID, PRIMARY_CONTACT_PHONE_ID, and RESOURCE_NAME. Financial and timing fields include BUDGET_AMOUNT, BUDGET_STATUS_CODE, BUDGET_STATUS, CURRENCY_CODE, DECISION_TIMEFRAME_CODE, and TIME_FRAME.

Lead line detail is exposed through CATEGORY_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, UOM_CODE, QUANTITY, CATEGORY_DESC, UNIT_OF_MEASURE_TL, CONCATENATED_SEGMENTS, ITEM_DESCRIPTION, and CONCAT_CAT_PARENTAGE. Fifteen descriptive ATTRIBUTE columns plus LINE_ATTRIBUTE columns provide the standard EBS flexfield extension points.

Common Use Cases and Queries

Typical usage includes campaign ROI reporting, lead pipeline analysis, promotion source effectiveness, and extract-load routines feeding external CRM or BI platforms.

Retrieving leads by promotion source code:

• SELECT lead_number, source_promotion_code, source_promotion_name, sales_channel, lead_status, budget_amount, currency_code FROM apps.ams_ds_sales_leads_v WHERE source_promotion_code = :p_source_code;

Aggregating lead counts and budget by promotion and status:

• SELECT source_promotion_code, lead_status, COUNT(*) lead_cnt, SUM(budget_amount) total_budget FROM apps.ams_ds_sales_leads_v GROUP BY source_promotion_code, lead_status ORDER BY source_promotion_code;

Filtering qualified leads assigned to a resource for pipeline review:

• SELECT lead_number, resource_name, rank, qualified_flag, time_frame FROM apps.ams_ds_sales_leads_v WHERE qualified_flag = 'Y' AND resource_name = :p_resource;

Because the view performs the joins to lookup, channel, promotion, and item tables, ad hoc queries against it avoid the complexity of reconstructing the same decoding logic manually across AS_SALES_LEADS, AS_LOOKUPS, and AMS_P_SOURCE_CODES_V.