Results for “asf_win_loss_mv”

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

Overview

The table ASF_WIN_LOSS_MV is an APPS-owned database object within the Oracle E-Business Suite Release 12.1.1 / 12.2.2 environment, belonging to the ASF – Sales Online product family. As the _MV suffix indicates, this object is a materialized view physically maintained as a table, designed to persist pre-aggregated win/loss analytical data across sales resources, sales groups, periods, and credit (revenue) types. Its role is to support sales performance analysis, forecast versus actual comparison, and territory or group rollup reporting without incurring the cost of repeatedly scanning transactional sales opportunity and forecast base tables.

The metadata classifies this object with a heuristic Data Vault designation of standalone. Within a Data Vault modeling context this suggests the object behaves closer to a satellite structure — it attaches descriptive and measurable attributes (won, lose, forecast rollup flags) to business keys such as salesforce, sales group, credit type, and period, rather than acting as a pure hub or a link between two independent hubs. The optional foreign keys to AS_SALESFORCE and IGS_FI_CR_TYPES_ALL reinforce this reading: the view contextualizes metrics around reference entities rather than modelling inter-entity relationships.

Key Information Stored

The documented physical schema for 12.1.1 exposes 11 columns. The most significant attributes are:

• SALESFORCE_ID – identifies the sales resource (salesforce member) associated with the win/loss record; a foreign key to AS_SALESFORCE.
• PARENT_RESOURCE_ID – the parent sales resource, enabling hierarchical resource rollups.
• SALES_GROUP_ID – the sales group to which the resource belongs.
• PARENT_GROUP_ID – the parent sales group, supporting group hierarchy aggregation.
• CREDIT_TYPE_ID – the credit/revenue type; foreign key to IGS_FI_CR_TYPES_ALL.
• PRODUCT_CATEGORY_ID – the product category dimension for slicing win/loss results.
• PERIOD_TYPE and PERIOD_NAME – the calendar and period against which results are measured, essential for time-series reporting.
• FORECAST_ROLLUP_FLAG – indicates whether the row participates in forecast rollup calculations.
• WON – the aggregated won measure (count or amount, depending on implementation).
• LOSE – the aggregated lost measure.

The metadata does not document a single-column surrogate primary key; the identifying grain is composite, formed by the combination of resource, group, period, credit type, and product category dimensions. Business-key candidates therefore include the (SALESFORCE_ID, PERIOD_NAME, CREDIT_TYPE_ID, PRODUCT_CATEGORY_ID) tuple.

Common Use Cases and Queries

Typical use cases include win/loss ratio reporting by sales representative, trend analysis by period, product category performance, and forecast rollup validation. Representative query patterns:

• Win rate by salesforce member: SELECT SALESFORCE_ID, WON, LOSE, WON/(WON+LOSE) FROM ASF_WIN_LOSS_MV WHERE PERIOD_NAME = :p.
• Group-level rollup: aggregate on SALES_GROUP_ID and PARENT_GROUP_ID for territory dashboards.
• Forecast participation filtering: WHERE FORECAST_ROLLUP_FLAG = 'Y'.
• Credit-type segmentation joined to IGS_FI_CR_TYPES_ALL for revenue classification reporting.

Related Objects

• AS_SALESFORCE – joined via ASF_WIN_LOSS_MV.SALESFORCE_ID = AS_SALESFORCE.SALESFORCE_ID; source of resource detail.
• IGS_FI_CR_TYPES_ALL – joined via CREDIT_TYPE_ID; supplies credit type definitions.
• ASF_WIN_LOSS (base object) – the underlying detail that this materialized view aggregates.
• Sales group and territory tables referenced by SALES_GROUP_ID / PARENT_GROUP_ID.
• Product category reference (PRODUCT_CATEGORY_ID) used for dimensional slicing.
• ASF Sales Online reporting and analytics pages that consume this view for dashboards.