Results for “conversion_status_flag”

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

Overview

ASF_OPP_CMP_FRCST_MV is an Oracle E-Business Suite table owned by the APPS schema and associated with the ASF – Sales Online product family. The object name and column profile indicate that it stores forecast rollup information for sales opportunities, expressed at the level of salesforce hierarchies, sales groups, and parent groups, with supporting references to leads, credit types, promotions, product categories, and accounting periods. It functions as a consolidated forecast staging or rollup structure used by Sales Online reporting and pipeline management features rather than as a transactional opportunity table.

The ETRM metadata describes this object as VALID and documents a physical schema of seventeen columns. The heuristic Data Vault classification mined from the foreign key structure is standalone, which suggests that the table is not a pure hub, link, or satellite but a derived or aggregate structure. Practitioners modeling this object in a warehouse may therefore choose to treat it as a fact or summary entity rather than decomposing it into Data Vault constructs.

Key Information Stored

The table is not documented as exposing a single-column surrogate primary key in the ETRM excerpt. Where a surrogate key exists it is typically a generated identifier used for internal uniqueness, while the business identity is carried by the combination of lead, salesforce, sales group, product category, and period values. The most significant columns are:

• LEAD_ID – foreign key to AS_LEADS_ALL, identifying the opportunity or lead being forecast.
• SALESFORCE_ID – foreign key to AS_SALESFORCE, attaching the forecast to a sales team or resource hierarchy.
• CREDIT_TYPE_ID – foreign key to IGS_FI_CR_TYPES_ALL, classifying the revenue or credit treatment.
• LEADS – descriptive or denormalized lead information accompanying the identifier.
• PERIOD_NAME – the accounting or forecast period the row applies to, a primary reporting dimension.
• BUDGET – the forecast or budget amount associated with the row.
• CURRENCY_CODE – currency of the stored budget amount.
• SALES_GROUP_ID and PARENT_GROUP_ID – the owning sales group and its parent, supporting hierarchical rollups.
• PRODUCT_CATEGORY_ID and PRODUCT_CAT_SET_ID – product classification dimensions.
• SOURCE_PROMOTION_ID – the promotion or campaign that originated the opportunity.
• FORECAST_ROLLUP_FLAG, OPP_OPEN_STATUS_FLAG, WIN_LOSS_INDICATOR, IMMEDIATE_PARENT_FLAG, and CONVERSION_STATUS_FLAG – status and control indicators governing how rows participate in rollup, whether the opportunity is open, and whether it was won or lost.

Common Use Cases and Queries

The principal use case is weighted or summarized pipeline reporting by period, salesforce, and product category. A typical query aggregates budget by period and sales group while filtering for open opportunities:

SELECT period_name, sales_group_id, SUM(budget) forecast_amt
FROM apps.asf_opp_cmp_frcst_mv
WHERE opp_open_status_flag = 'Y'
GROUP BY period_name, sales_group_id;

A second pattern joins the table to AS_LEADS_ALL to enrich forecast lines with opportunity attributes, and to AS_SALESFORCE to resolve the owning team. Because the object is a materialized view or summary-style structure, consumers should expect refreshed data rather than real-time transactions, and should avoid DML against it.

Related Objects

The documented foreign keys identify three directly referenced objects, and additional dependencies follow from the ASF schema:

• AS_LEADS_ALL – joined on LEAD_ID to retrieve opportunity and lead detail.
• AS_SALESFORCE – joined on SALESFORCE_ID to resolve sales team membership.
• IGS_FI_CR_TYPES_ALL – joined on CREDIT_TYPE_ID for credit type descriptions.
• AS_SALES_GROUPS and related group tables – supporting SALES_GROUP_ID and PARENT_GROUP_ID hierarchy resolution.
• AS_PRODUCT_CATEGORIES – joined on PRODUCT_CATEGORY_ID and PRODUCT_CAT_SET_ID.
• AS_OPPORTUNITIES – the underlying opportunity entity that feeds forecast lines through LEAD_ID.
• ASF_OPP_CMP_FRCST base tables and the Sales Online forecast concurrent programs – the sources and processes that populate this structure.