Results for “asf_leads_chn_sum_mv”

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

Overview

ASF_LEADS_CHN_SUM_MV is a materialized view in the Oracle E-Business Suite Sales Online (ASF) module that stores summarized lead information organized around channel and period dimensions. The object resides in the APPS schema and, per the ETRM 12.1.1 documentation, exposes 11 columns. In the 12.1.1 and 12.2.2 environments documented here, the object is reported as "Not implemented in this database," meaning it is delivered as part of the Oracle-proprietary dictionary but is not instantiated in the reference instance. Its name suffix (MV) and the CHN_SUM token indicate a pre-aggregated, channel-oriented summary layer used for lead pipeline analysis rather than transactional lead capture.

Under the heuristic Data Vault classification mined from the foreign-key structure, this object is modeled as a standalone construct. Because it aggregates measures (budget, channel, period) around a lead identifier, it is best treated as a satellite-like summary over the AS_LEADS_ALL hub rather than as an independent hub or a link between business entities. The single documented foreign key — LEAD_ID → AS_LEADS_ALL — anchors the summary back to the core lead entity.

Key Information Stored

The columns most significant to interpretation and reporting are:

• LEAD_ID — The join key to AS_LEADS_ALL; identifies the lead being summarized. It is the documented foreign-key column and the natural candidate for a unique index in combination with period and channel attributes.
• DESCRIPTION — Descriptive text associated with the lead or summary record.
• BUDGETAMOUNT — The monetary value attributed to the lead; the primary measure for pipeline and forecast reporting.
• CURRENCY_CODE — Currency of BUDGETAMOUNT, essential when consolidating multi-currency pipelines.
• CHANNEL_CODE — The acquisition channel, which drives channel-effectiveness analysis (the "CHN" in the view name).
• SOURCE_PROMOTION_ID — Identifies the campaign or promotion that generated the lead.
• CUSTOMER_ID — The customer or prospect associated with the lead.
• MANAGER_PERSON_ID — The resource or manager responsible for the lead, enabling ownership reporting.
• PERIOD_NAME and PERIOD_TYPE — The accounting or reporting period and its granularity, which together define the time dimension of the summary.
• PARENT_RESOURCE_ID — The parent resource, supporting roll-up of leads through the sales hierarchy.

No surrogate primary key is documented in the supplied metadata; LEAD_ID serves as the business-key anchor and is the only documented foreign key.

Common Use Cases and Queries

The summary structure supports channel and period roll-ups without scanning the full lead table. Typical patterns include pipeline value by channel, period-over-period lead trend, and campaign return analysis. A representative query joining back to the base lead entity:

• SELECT s.CHANNEL_CODE, s.PERIOD_NAME, SUM(s.BUDGETAMOUNT) FROM ASF_LEADS_CHN_SUM_MV s GROUP BY s.CHANNEL_CODE, s.PERIOD_NAME;
• SELECT s.LEAD_ID, l.LEAD_NAME, s.BUDGETAMOUNT FROM ASF_LEADS_CHN_SUM_MV s JOIN AS_LEADS_ALL l ON s.LEAD_ID = l.LEAD_ID;
• Currency-normalized forecasting using CURRENCY_CODE against GL daily rates to compare channels across regions.
• Resource-performance reporting by joining MANAGER_PERSON_ID and PARENT_RESOURCE_ID to resource tables for territory roll-ups.

Because the object is a materialized view, refresh scheduling and staleness must be considered in any operational report.

Related Objects

The most significant dependencies, grounded in the documented foreign-key relationship, are:

• AS_LEADS_ALL — The base lead table; joined on LEAD_ID and the sole documented foreign key target.
• AS_LEADS — The original lead entity underlying AS_LEADS_ALL, relevant for legacy joins.
• AS_PROMOTIONS / AS_SOURCE_PROMOTIONS — Referenced via SOURCE_PROMOTION_ID for campaign attribution.
• HZ_CUST_ACCOUNTS — Resolves CUSTOMER_ID to the trading partner.
• PER_PERSONS / PER_ALL_PEOPLE_F — Resolves MANAGER_PERSON_ID and PARENT_RESOURCE_ID.
• JTF_RS_RESOURCE_EXTNS — Resource detail used for sales-hierarchy reporting.
• GL_DAILY_RATES — Currency conversion for CURRENCY_CODE measures.

All relationships remain contingent on the object being implemented, which the ETRM metadata records as absent in this database.