Search Results asf_sc_bin_frcst_mv

Overview

ASF_SC_BIN_FRCST_MV is a materialized view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the ASF product family, which supports Sales Online functionality, and is associated with the Oracle Sales Foundation / Sales application stack that also encompasses CRM-oriented objects such as AS_LEADS_ALL and AS_SALESFORCE. Within that stack, the object functions as a pre-aggregated forecast reporting structure, presenting opportunity, pipeline, and attainment figures grouped at the level of bin, forecast category, period, and sales group.

The documented ETRM metadata records fifteen physical columns for this object in the 12.1.1 repository and confirms that the table is standalone in the sense that no downstream objects reference it through a foreign key. Because it is a materialized view, it does not carry its own referential constraints; the foreign key relationships listed in the metadata are inherited references from underlying base tables and are surfaced here for lineage purposes. From a heuristic Data Vault modeling perspective, the structure most closely resembles a derived link joined to a satellite, in that it combines several independent business keys — salesforce, lead, credit type, forecast category, and period — with descriptive and numeric measures. This classification is offered as a modeling suggestion rather than a documented fact.

Key Information Stored

The materialized view stores rollup measures and dimensional identifiers rather than a transactional surrogate key. There is no documented single-column primary key; instead, the following columns carry the analytical and dimensional content:

• CNT — the count of contributing opportunity or forecast records, the base for averaging and ratio calculations.
• PIPELINE_AMOUNT — the monetary value of open pipeline represented for the grouping combination.
• WON_AMOUNT — the value of business already closed as won.
• CURRENCY_CODE — the currency in which the pipeline and won amounts are expressed, required for any cross-currency comparison.
• FORECAST_DATE and PERIOD_NAME — the two time descriptors, supporting point-in-time and period-based reporting simultaneously.
• FORECAST_CATEGORY_ID — identifies the forecast bucket (for example, committed, best case, or pipeline).
• SALESFORCE_ID — the sales organization or team responsible for the contribution, referencing AS_SALESFORCE.
• LEAD_ID — the originating lead reference, joining to AS_LEADS_ALL.
• CREDIT_TYPE_ID — the credit or revenue categorization, joining to IGS_FI_CR_TYPES_ALL.
• SALES_GROUP_ID and PARENT_GROUP_ID — the operational sales group and its hierarchy parent, enabling rollup and hierarchy reporting.
• FORECAST_ROLLUP_FLAG and IMMEDIATE_PARENT_FLAG — indicators controlling whether a row participates in rollup and whether it belongs to the immediate parent level.
• OPP_DELETED_FLAG — marks contributions tied to deleted opportunities, allowing consumers to exclude stale data.

Common Use Cases and Queries

Typical usage centers on sales forecasting, pipeline coverage analysis, and attainment reporting. Because the object is pre-aggregated by group and period, queries are generally inexpensive. A representative pattern filters by period and currency, then groups by rollup level:

• SELECT sales_group_id, period_name, SUM(pipeline_amount), SUM(won_amount), SUM(cnt) FROM asf_sc_bin_frcst_mv WHERE period_name = :p AND opp_deleted_flag = 'N' GROUP BY sales_group_id, period_name;
• Coverage ratios are computed by dividing pipeline by won for each forecast category, useful for comparing committed versus best-case positions.
• Hierarchy traversal uses PARENT_GROUP_ID joined back to SALES_GROUP_ID to build a tree of forecast ownership, restricted with FORECAST_ROLLUP_FLAG = 'Y' to avoid double counting.
• Currency normalization requires joining to the currency conversion tables once CURRENCY_CODE is known.
• Lead-source effectiveness reporting joins LEAD_ID to AS_LEADS_ALL to attribute forecast amounts to campaign origins.

Related Objects

The most significant related objects, based on the documented join columns, include:

• IGS_FI_CR_TYPES_ALL — joined via CREDIT_TYPE_ID for credit type descriptions.
• AS_SALESFORCE — joined via SALESFORCE_ID for sales organization attributes.
• AS_LEADS_ALL — joined via LEAD_ID for lead and campaign detail.
• AS_OPPORTUNITIES_ALL — the source of opportunity amounts and deletion status that feed the flag.
• AS_FORECAST_CATEGORIES — lookup for FORECAST_CATEGORY_ID values.
• GL_PERIODS / FND_CURRENCIES — supporting period and currency validation.

Because the object is a materialized view, refresh scheduling and query rewrite settings should be reviewed in the APPS schema, and any heavy direct querying should account for staleness relative to the base opportunity tables.