Results for “as_forecast_categories_b”

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

Overview

AS_FORECAST_CATEGORIES_B is a Foundation table in the Oracle E-Business Suite Sales (AS) product family, owned by the OSM schema. It defines the forecast categories available to the forecasting process, and each category determines the selection of interest types that may be applied to a forecast. An opportunity can only be used for forecasting purposes when its interest types are mapped to a forecast category, which makes this table an access-control and classification layer over the sales pipeline rather than a transactional store.

Because the row count is small, stable, and referenced by numerous dependent tables, the heuristic Data Vault classification of hub-leaning is appropriate. Modeled as a hub, FORECAST_CATEGORY_ID would serve as the business key around which satellite tables (created/updated audit attributes) and link tables (relationships to interest types, actuals, and internal forecasts) are organized.

Key Information Stored

The table carries eleven documented columns. The most significant are:

• FORECAST_CATEGORY_ID — the surrogate primary key, enforced by AS_FORECAST_CATEGORIES_B_PK, and the value propagated to all dependent tables.
• START_DATE_ACTIVE / END_DATE_ACTIVE — the effective dating window that governs whether the category may be selected. This is the core of Oracle's date-tracked reference data pattern.
• SECURITY_GROUP_ID — a foreign key to FND_SECURITY_GROUPS, implementing multi-tenant / operating-unit level data segregation.
• ZD_EDITION_NAME — the editioning column introduced in EBS 12.2 online patching, used to distinguish editions of the same logical row during change cycles.
• OBJECT_VERSION_NUMBER — used for optimistic locking by the OAF framework.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard WHO columns recording audit history.

The unique index AS_FORECAST_CATEGORIES_B_U1 is defined on (FORECAST_CATEGORY_ID, ZD_EDITION_NAME), which the metadata identifies as the business-key candidate. This composite key reflects the 12.2 editioning requirement: the surrogate ID alone is not guaranteed unique across editions.

Common Use Cases and Queries

Typical usage is validation and lookup rather than direct reporting. A common pattern is to restrict a category list to currently effective rows:

• SELECT forecast_category_id FROM as_forecast_categories_b WHERE TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active, TRUNC(SYSDATE)) AND security_group_id = :p_security_group_id;

Other scenarios include:

• Validating a forecast category before inserting into AS_FORECAST_ACTUALS or AS_INTERNAL_FORECASTS.
• Auditing which categories are mapped to interest types for a given sales campaign.
• Reporting on the pipeline by joining AS_FST_SALES_CATEGORIES.FORECAST_CATEGORY_ID back to this table.
• Determining why an opportunity cannot be forecast — usually a missing interest-type mapping to a category.
• Edition-aware queries in 12.2 should include ZD_EDITION_NAME = 'ORA$BASE' when reading base-edition rows.

Related Objects

The FK relationship data shows this table as a hub referenced by three transactional children:

• AS_FORECAST_ACTUALS — joined on FORECAST_CATEGORY_ID; stores actual forecast output results.
• AS_INTERNAL_FORECASTS — joined on FORECAST_CATEGORY_ID; stores internally generated forecasts.
• AS_FST_SALES_CATEGORIES — joined on FORECAST_CATEGORY_ID; links sales categories to the forecast framework.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID, the parent of the security model.

Applications should resolve categories through these documented join paths rather than assuming the surrogate key is globally unique without the edition column.