Results for “product_cat_set_id”

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

Overview

ASF_WIN_LOSS_PRODCAT_MV is a materialized view owned by the APPS schema in Oracle E-Business Sales Online (ASF), the foundation module underlying Oracle Sales, Oracle TeleSales, and Oracle Partners products. It stores aggregated win/loss performance metrics broken down by product category, sales resource, and sales group, and is consumed by sales management dashboards, pipeline analytics, and quota attainment reporting. In EBS 12.1.1 the documented physical schema exposes 13 columns; the object remains VALID in 12.2.2 and is queried through the same APPS synonym. Because the object is materialized, its contents reflect the state of the underlying transactional tables at the time of the most recent refresh, so latency between the base tables (opportunity and product category data) and this view must be accounted for in operational reporting.

Key Information Stored

The documented metadata classifies ASF_WIN_LOSS_PRODCAT_MV as standalone under a heuristic Data Vault assessment. In Data Vault terms a materialized aggregate of this shape most closely resembles a satellite, since each row describes measurable outcome attributes (won and loss counts) attached to a business key rather than acting as an independent hub or an associating link.

• PRODUCT_CATEGORY_ID and PRODUCT_CAT_SET_ID — the product category and its category set, forming the primary analytical grain of the view.
• WON and LOSS — aggregated counts or amounts of successful and unsuccessful sales outcomes per category.
• SALESFORCE_ID — foreign key to AS_SALESFORCE, identifying the sales organization whose performance is summarized.
• CREDIT_TYPE_ID — foreign key to IGS_FI_CR_TYPES_ALL, indicating the credit type associated with the win/loss record.
• PARENT_RESOURCE_ID, SALES_GROUP_ID, and PARENT_GROUP_ID — hierarchical identifiers enabling roll-up reporting across resource and group structures.
• FORECAST_ROLLUP_FLAG — indicates whether the row participates in forecast aggregation.
• CURRENCY_CODE, CONVERSION_PERIOD_TYPE, and CONVERSION_PERIOD_NAME — currency and period context used for monetary conversion.

The view's uniqueness is derived from the combination of product category, category set, and the resource/group dimensions rather than from a single surrogate key column, which is typical of aggregate materialized views.

Common Use Cases and Queries

Typical scenarios include win rate analysis by product category, period-over-period comparison of wins and losses, and roll-up of results to parent sales groups. A representative query pattern joining the two documented foreign keys is:

• Win rate by category: SELECT p.product_category_id, p.won, p.loss, (p.won / NULLIF(p.won + p.loss,0)) win_rate FROM asf_win_loss_prodcat_mv p WHERE p.forecast_rollup_flag = 'Y'
• Salesforce-scoped performance: join p.salesforce_id = s.salesforce_id to AS_SALESFORCE to filter results by organization.
• Credit-type reporting: join p.credit_type_id = c.credit_type_id to IGS_FI_CR_TYPES_ALL to break results down by credit type.
• Currency normalization using CURRENCY_CODE together with CONVERSION_PERIOD_TYPE and CONVERSION_PERIOD_NAME for period-consistent financial roll-ups.

Related Objects

The documented foreign keys identify the principal dependencies. AS_SALESFORCE (joined on SALESFORCE_ID) supplies the sales organization context, and IGS_FI_CR_TYPES_ALL (joined on CREDIT_TYPE_ID) supplies credit-type definitions. The product category columns (PRODUCT_CATEGORY_ID, PRODUCT_CAT_SET_ID) reference the standard EBS product category structures, commonly FND_ID_FLEX_STRUCTURES and the category set tables in the inventory/advanced pricing schema. Because this object is a materialized view, it depends on the underlying base tables for opportunities and product category assignments; its refresh schedule should be verified prior to relying on it for near-real-time reporting.