Search Results opp_deleted_flag




Overview
Explore in AI Chat

Overview

ASF_SC_BIN_FRCST_MV is an APPS-owned materialized view within the Oracle EBS Sales Foundation (ASF) schema, registered with FND Design Data identifier ASF.ASF_SC_BIN_FRCST_MV. It stores summarized forecast data organized into time-bucketed "bins," supporting sales forecasting, pipeline analysis, and quota-versus-actual reporting. The object resides in the APPS_TS_SUMMARY tablespace with a PCT_FREE of 0, a storage characteristic typical of read-mostly summary objects that are read in full-block fashion.

The records in this object are consumed primarily by Oracle Territory Manager and Sales Foundation forecasting reports. Because it is a materialized view, the underlying detail is drawn from the sales opportunity and forecast tables and aggregated by sales group, salesforce, credit type, forecast category, and period. From a Data Vault modeling perspective, the heuristic classification is standalone: the object does not reference any database object and is not referenced by any database object at the schema level, so it functions as a denormalized summary layer rather than a hub, link, or satellite. A modeler would therefore treat it as a derived reporting mart aggregated from the sales pipeline rather than a canonical master entity.

Key Information Stored

The object is composed of 15 documented columns, of which the following carry the bulk of the reporting semantics:

  • FORECAST_DATE — the date dimension for the forecast bucket; combined with PERIOD_NAME it defines the reporting time axis.
  • PERIOD_NAME (VARCHAR2, 15) — the GL/forecast period label associated with the bucket.
  • CNT — the count of opportunities or forecast records rolled into the bucket.
  • PIPELINE_AMOUNT — the aggregated value of open pipeline opportunities in the bucket.
  • WON_AMOUNT — the aggregated value of won opportunities in the bucket.
  • CURRENCY_CODE (VARCHAR2, 15) — the currency in which the amounts are expressed.
  • OPP_DELETED_FLAG — a flag indicating whether the underlying opportunity has been deleted, allowing reports to include or exclude soft-deleted pipeline records. This is the column most frequently searched by implementers, since filtering on it is required to reconcile totals.
  • FORECAST_ROLLUP_FLAG — indicates whether the row reflects a rollup of subordinate forecast entries.
  • IMMEDIATE_PARENT_FLAG — distinguishes rows at the immediate parent level of the sales hierarchy.
  • SALES_GROUP_ID — foreign key to the sales group (territory) that owns the forecast.
  • SALESFORCE_ID — reference to AS_SALESFORCE, identifying the salesforce assigned to the row.
  • CREDIT_TYPE_ID — reference to IGS_FI_CR_TYPES_ALL, identifying the credit type applied to the forecast amount.
  • LEAD_ID — reference to AS_LEADS_ALL, linking the forecast to an originating sales lead.
  • PARENT_GROUP_ID and FORECAST_CATEGORY_ID — support rollup navigation and forecast category classification.

No surrogate primary key is documented; the columns are all non-mandatory in the metadata, so uniqueness is enforced by the materialized view's defining query and refresh logic rather than by a declared constraint.

Common Use Cases and Queries

Typical usage centers on territory-level and period-level forecast reporting. A representative query excluding deleted opportunities is:

  • SELECT period_name, sales_group_id, SUM(pipeline_amount), SUM(won_amount) FROM apps.asf_sc_bin_frcst_mv WHERE opp_deleted_flag = 'N' GROUP BY period_name, sales_group_id
  • Pipeline-versus-won comparison by salesforce: join SALESFORCE_ID to AS_SALESFORCE to report performance by sales organization.
  • Lead-sourced forecast analysis: join LEAD_ID to AS_LEADS_ALL to attribute forecast value back to originating leads.
  • Credit type analysis: join CREDIT_TYPE_ID to IGS_FI_CR_TYPES_ALL for revenue recognition breakdowns.
  • Rollup reconciliation: filter on FORECAST_ROLLUP_FLAG and IMMEDIATE_PARENT_FLAG to avoid double counting parent and child rows.

Because the object is a materialized view, refresh timing must be considered; reports run between refreshes reflect the last snapshot.

Related Objects

The documented foreign key relationships identify the most significant related objects:

  • AS_SALESFORCE — joined via SALESFORCE_ID to resolve salesforce names.
  • AS_LEADS_ALL — joined via LEAD_ID for lead attribution.
  • IGS_FI_CR_TYPES_ALL — joined via CREDIT_TYPE_ID for credit type descriptions.
  • ASF_SC_BIN_FRCST and related ASF forecast base tables — the underlying detail from which the materialized view aggregates.
  • AS_OPPORTUNITIES_ALL / AS_OPP_FORECAST — source opportunity and forecast records underpinning amounts and the OPP_DELETED_FLAG.
  • GL_PERIODS — referenced indirectly through PERIOD_NAME for period validation.

No Oracle API is documented as depending on this object; consumption is through SQL reporting against the materialized view itself.