Search Results source_promotion_code

Overview

AS_SALES_LEADS_V is a seeded Oracle E-Business Suite view owned by the APPS schema and registered as VALID in the ETRM repository. It belongs to the AS – Sales Foundation product family, which provides the shared lead and opportunity foundation consumed by Oracle Sales, Oracle TeleSales, and Oracle Partner Management. The view exposes sales lead header details, that is, one row per sales lead as stored in the AS_SALES_LEADS base entity, enriched with descriptive values resolved from related lookup, party, resource, channel, campaign, and offer sources.

Its principal role is reporting and integration. Because the view denormalizes codes into their translated meanings and joins the lead record to HZ_PARTIES and JTF_RS_RESOURCE_EXTNS, it allows concurrent programs, Oracle Discoverer workbooks, OBIEE extracts, and custom interfaces to retrieve human-readable lead information without reproducing the multi-table join logic. In Oracle EBS 12.1.1 and 12.2.2 the object definition is unchanged in substance; both releases resolve the same APPS-owned view against the same set of synonyms and lookup views.

Underlying Base Objects

The documented dependency list for 12.2.2 identifies the following referenced objects:

• AS_SALES_LEADS (synonym) – the primary lead header table supplying ROWID, identifiers, audit columns, status, budget, ranking, assignment, and descriptive attribute columns.
• AS_LOOKUPS (view) – joined multiple times to translate STATUS_CODE, BUDGET_STATUS_CODE, and DECISION_TIMEFRAME_CODE into their MEANING values (LEAD_STATUS, BUDGET_STATUS, TIME_FRAME).
• AS_STATUSES_B and AS_STATUSES_TL (synonyms) – status base and translation tables supporting status semantics.
• AS_SALES_LEAD_RANKS_TL (synonym) – provides the translated rank meaning exposed as RANK.
• HZ_PARTIES (synonym) – supplies PARTY_NAME, PARTY_NUMBER, and PARTY_TYPE for the customer on the lead.
• HZ_CONTACT_POINTS (synonym) – referenced for contact point resolution associated with the lead.
• JTF_RS_RESOURCE_EXTNS (synonym) – resolves ASSIGN_TO_PERSON_ID and ASSIGN_TO_SALESFORCE_ID to the resource SOURCE_NAME exposed as ASSIGNED_NAME.
• ASO_I_SALES_CHANNELS_V (view) – translates CHANNEL_CODE into SALES_CHANNEL.
• AMS_P_SOURCE_CODES_V (view) – resolves the source promotion reference to CAMP.NAME (SOURCE_PROMOTION_NAME) and CAMP.SOURCE_CODE (SOURCE_PROMOTION_CODE); the same pattern supplies OFFER_NAME and OFFER_CODE for the offer reference.

The view therefore functions as a read-only consolidation layer over the AS Sales Foundation transactional model and the Trading Community Architecture (HZ) and Resource (JTF_RS) models.

Key Columns

• ROW_ID, SALES_LEAD_ID – row identifier and the primary key of the underlying lead record; use SALES_LEAD_ID for programmatic joins back to AS_SALES_LEADS.
• LEAD_NUMBER, STATUS_CODE, LEAD_STATUS – the user-visible lead number and the code/meaning pair for lead status.
• CUSTOMER_ID, PARTY_NAME, PARTY_NUMBER, PARTY_TYPE – the customer party associated with the lead.
• ASSIGN_TO_PERSON_ID, ASSIGN_TO_SALESFORCE_ID, ASSIGNED_NAME – assignment targets and the resolved resource name.
• CHANNEL_CODE, SALES_CHANNEL – channel of the lead with translated channel description.
• BUDGET_AMOUNT, BUDGET_STATUS_CODE, BUDGET_STATUS, CURRENCY_CODE – budgeted value, budget status code and meaning, and currency.
• LEAD_RANK_CODE, LEAD_RANK_ID, RANK, TOTAL_SCORE, SCORECARD_ID – ranking and scoring information produced by lead qualification.
• SOURCE_PROMOTION_ID, SOURCE_PROMOTION_NAME, SOURCE_PROMOTION_CODE, OFFER_ID, OFFER_NAME, OFFER_CODE – campaign and offer attribution.
• QUALIFIED_FLAG, ACCEPT_FLAG, KEEP_FLAG, URGENT_FLAG, IMPORT_FLAG, DELETED_FLAG, REJECT_REASON_CODE, CLOSE_REASON – lifecycle and disposition flags.
• ATTRIBUTE_CATEGORY through ATTRIBUTE15, plus created_by/creation_date and last_updated_by/last_update_date – descriptive flexfield and standard audit columns.

Common Use Cases and Queries

Typical uses include lead pipeline reports by status and rank, assignment workload analysis by sales resource, campaign and offer attribution reporting, and staged extraction for CRM or data-warehouse loads. Bulk extraction is normally filtered on LAST_UPDATE_DATE and DELETED_FLAG.

Leads by status and assigned resource:

• SELECT lead_number, party_name, lead_status, rank, assigned_name, budget_amount, currency_code FROM as_sales_leads_v WHERE deleted_flag = 'N' AND status_code = 'QUALIFIED' ORDER BY assigned_name, lead_number;

Join back to the base table for columns not surfaced by the view:

• SELECT v.lead_number, v.lead_status, l.assign_date FROM as_sales_leads_v v, as_sales_leads l WHERE v.sales_lead_id = l.sales_lead_id AND v.creation_date >= :p_start_date;

Campaign attribution totals:

• SELECT source_promotion_name, COUNT(*) lead_count, SUM(budget_amount) total_budget FROM as_sales_leads_v WHERE deleted_flag = 'N' GROUP BY source_promotion_name;

Because the view resolves meanings at query time, reports written against it remain portable across 12.1.1 and 12.2.2 provided the underlying lookups are not customized.