Search Results ams_forecast_products




Overview
Explore in AI Chat

Overview

The AMS_FORECAST_PRODUCTS table is a core data object within the Oracle E-Business Suite Marketing (AMS) module, specifically designed to support forecasting functionality. Its primary role is to define and maintain the set of products that are considered eligible or in-scope for a specific version of a marketing forecast. This establishes a critical link between forecast planning activities and the product catalog, ensuring that revenue, demand, or campaign projections are calculated against a controlled and relevant subset of items. The table acts as a junction, associating forecast versions with specific products, thereby enabling granular and product-specific forecast analysis and reporting.

Key Information Stored

Based on the provided ETRM metadata, the central piece of information stored is the unique identifier for each record that links a product to a forecast version. The documented primary key column is FORECAST_PRODUCT_ID. While the full column list is not detailed in the excerpt, a table of this nature in the AMS schema typically would also include foreign key columns such as FORECAST_VERSION_ID (linking to AMS_FORECAST_VERSIONS_B) and INVENTORY_ITEM_ID and ORGANIZATION_ID (linking to MTL_SYSTEM_ITEMS_B). It may also contain audit columns like CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY, which are standard in Oracle EBS tables.

Common Use Cases and Queries

The primary use case is to retrieve the list of all products configured for a given forecast, which is essential for generating forecast reports and analytics. A common query would join this table to the product master and forecast version tables. For example, to list products for a specific forecast version, one might use a pattern such as:

  • SELECT afp.forecast_product_id, msib.segment1 product_code, afv.forecast_version_name FROM ams_forecast_products afp, ams_forecast_versions_b afv, mtl_system_items_b msib WHERE afp.forecast_version_id = afv.forecast_version_id AND afp.inventory_item_id = msib.inventory_item_id AND afp.organization_id = msib.organization_id AND afv.forecast_version_id = :p_version_id;

Another critical use case is data validation, ensuring that forecast entries or transactional data being compared to forecasts reference products that are officially part of the forecast model.

Related Objects

The primary documented relationship for this table is its primary key constraint, AMS_FORECAST_PRODUCTS_PK, on the FORECAST_PRODUCT_ID column. While the excerpt does not list explicit foreign key constraints, this table is inherently relational. It is a child table to AMS_FORECAST_VERSIONS_B (via a likely foreign key column like FORECAST_VERSION_ID). It is also a child to the inventory item master, MTL_SYSTEM_ITEMS_B (via likely foreign keys INVENTORY_ITEM_ID and ORGANIZATION_ID). In application logic, it is referenced by forecasting engines and reporting views within the AMS module that aggregate forecast data by product.