Results for “base_quantity_type”

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

Overview

AMS_CAMPAIGN_FORECASTS_ALL_V is an APPS-owned database view within the Oracle E-Business Suite Marketing (AMS) module. It consolidates all forecast information associated with a marketing campaign into a single, denormalized reporting structure. The view is classified as VALID under both Oracle EBS 12.1.1 and 12.2.2, and it serves as the primary read interface for campaign-linked forecast data that would otherwise require joining multiple transactional and lookup objects.

In the broader AMS architecture, forecasts are held in the AMS_ACT_FORECASTS_ALL table, which stores forecast records for multiple forecast-using entities (activities, campaigns, and similar objects). This view narrows that population to campaign forecasts only, filters on the ARC_ACT_FCAST_USED_BY discriminator of 'CAMP', and enriches each row with campaign descriptive attributes and decoded lookup meanings. Because the result set spans all operating units, the view includes ORG_ID and is treated as an _ALL view, making it suitable for multi-org reporting and integration extraction patterns.

Underlying Base Objects

The view is defined over three documented referenced objects:

• AMS_ACT_FORECASTS_ALL (SYNONYM) — the primary forecast fact source, aliased as F. All forecast measures, calendar attributes, hierarchy values, and DFF columns are drawn from this table. The join predicate F.ARC_ACT_FCAST_USED_BY = 'CAMP' restricts rows to campaign forecasts.
• AMS_CAMPAIGNS_VL (VIEW) — aliased as C. Joined on F.ACT_FCAST_USED_BY_ID = C.CAMPAIGN_ID, it supplies the campaign name, theme, description, and actual execution start and end dates.
• AMS_LOOKUPS (VIEW) — joined twice as LKP1 and LKP2 to decode BASE_QUANTITY_TYPE against lookup type 'AMS_FCAST_BASE_VOL_SOURCE' and FORECAST_SPREAD_TYPE against lookup type 'AMS_FCAST_SPREAD'. These joins return the MEANING column, exposed twice in the select list.

The combination of an _ALL fact synonym, a _VL (translated) campaign view, and lookup decoding means the view returns descriptive, user-facing text rather than raw codes, which is why it is frequently used directly in reports and extracts without further joins.

Key Columns

The view exposes the full FORECAST selection of AMS_ACT_FORECASTS_ALL plus campaign and lookup attributes. Notable columns include:

• FORECAST_ID — primary identifier of the forecast record.
• FORECAST_TYPE / FORECAST_UOM_CODE — classification and unit of measure for the forecast values.
• FORECAST_CALENDAR — the forecast calendar against which the forecast is profiled; this is the attribute most commonly referenced when users search for "forecast_calendar".
• PERIOD_LEVEL, FORECAST_PERIOD_ID, FORECAST_DATE — the periodization context, identifying the calendar period level and the specific forecast date or period.
• FORECAST_QUANTITY, FORECAST_REMAINING_QUANTITY, FORECAST_REMAINING_PERCENT — the forecasted amount and its remaining balances expressed in absolute and percentage terms.
• BASE_QUANTITY, BASE_QUANTITY_TYPE — the baseline volume and its source, with the source decoded to a MEANING via AMS_FCAST_BASE_VOL_SOURCE.
• FORWARD_BUY_QUANTITY, FORWARD_BUY_PERIOD, CUMULATION_PERIOD_CHOICE — forward-buy treatment applied to the campaign forecast.
• FORECAST_SPREAD_TYPE — the spreading method, decoded to MEANING via AMS_FCAST_SPREAD.
• HIERARCHY, HIERARCHY_LEVEL, LEVEL_VALUE — hierarchy context for the forecast record.
• CAMPAIGN_NAME, CAMPAIGN_THEME, DESCRIPTION, ACTUAL_EXEC_START_DATE, ACTUAL_EXEC_END_DATE — campaign descriptors sourced from AMS_CAMPAIGNS_VL.
• ORG_ID — operating unit, enabling multi-org filtering.
• ATTRIBUTE1 through ATTRIBUTE15, CONTEXT, ATTRIBUTE_CATEGORY — descriptive flexfield columns available for customer-specific extensions.
• Standard WHO columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, OBJECT_VERSION_NUMBER, and concurrent program identifiers.

Common Use Cases and Queries

Typical scenarios include campaign forecast reporting by calendar, quantity-vs-remaining-quantity analysis, forward-buy exposure review, and integration extracts into planning or data-warehouse systems. Because the view already decodes lookups and joins the campaign header, ad hoc SQL remains concise.

• List all forecasts for a campaign
  SELECT campaign_name, forecast_calendar, forecast_date, forecast_uom_code, forecast_quantity, forecast_remaining_quantity FROM ams_campaign_forecasts_all_v WHERE campaign_id = :p_campaign_id ORDER BY forecast_date;
• Filter by forecast calendar
  SELECT campaign_name, period_level, forecast_period_id, forecast_quantity FROM ams_campaign_forecasts_all_v WHERE forecast_calendar = :p_calendar AND org_id = :p_org_id;
• Summarize forecasted vs. remaining by campaign
  SELECT campaign_name, SUM(forecast_quantity) total_qty, SUM(forecast_remaining_quantity) remaining_qty FROM ams_campaign_forecasts_all_v GROUP BY campaign_name;
• Review forward-buy exposure
  SELECT campaign_name, forward_buy_period, forward_buy_quantity, forecast_remaining_percent FROM ams_campaign_forecasts_all_v WHERE forward_buy_quantity IS NOT NULL ORDER BY campaign_name, forward_buy_period;
• Inspect campaign execution context
  SELECT campaign_name, campaign_theme, actual_exec_start_date, actual_exec_end_date, forecast_spread_type, meaning FROM ams_campaign_forecasts_all_v WHERE actual_exec_start_date >= :p_start_date;

Queries against this view should always consider ORG_ID where operating-unit isolation is required, and should account for the FORECAST_CALENDAR and PERIOD_LEVEL columns when aligning results to a specific forecast calendar definition.