Results for “ams_fcast_spread”

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

Overview

APPS.AMS_CAMPAIGN_FORECASTS_ALL_V is a reporting and integration view within the Oracle E-Business Suite Oracle Marketing (AMS) module. It presents campaign-level forecast records by joining the base forecast table AMS_ACT_FORECASTS_ALL to campaign header information in AMS_CAMPAIGNS_VL and to two lookup code descriptions in AMS_LOOKUPS. The view is filtered so that only forecast records whose usage context is a campaign (ARC_ACT_FCAST_USED_BY = 'CAMP') are returned, and it exposes decoded meanings for the base quantity source and the forecast spread type.

The view is important for reporting because it delivers forecast quantities and remaining quantities alongside the campaign name, theme, and scheduled execution dates, together with human-readable descriptions for lookup-driven attributes. This makes it suitable for operational reporting, marketing analytics, and downstream integrations that require campaign forecast data without having to resolve lookup codes or campaign names independently.

Underlying Base Objects

The view is defined over three documented objects in the APPS schema.

• AMS_ACT_FORECASTS_ALL (documented as a synonym) — the primary forecast fact table, aliased as F. It supplies all forecast measures, keys, WHO columns, and descriptive flexfield attributes. The join restriction F.ARC_ACT_FCAST_USED_BY = 'CAMP' ensures that only campaign forecasts are included.
• AMS_CAMPAIGNS_VL (view) — aliased as C, supplying the campaign translation/subject information. It is joined on F.ACT_FCAST_USED_BY_ID = C.CAMPAIGN_ID, linking each forecast to its owning campaign.
• AMS_LOOKUPS (view) — aliased twice as LKP1 and LKP2, providing decoded lookup meanings. LKP1 uses LOOKUP_TYPE = 'AMS_FCAST_BASE_VOL_SOURCE' joined on BASE_QUANTITY_TYPE, and LKP2 uses LOOKUP_TYPE = 'AMS_FCAST_SPREAD' joined on FORECAST_SPREAD_TYPE.

Because the view performs inner joins to AMS_LOOKUPS for both lookup types, forecast rows will only be returned when their BASE_QUANTITY_TYPE and FORECAST_SPREAD_TYPE values have corresponding active lookup entries in the AMS_LOOKUPS view.

Key Columns

The view exposes the full column set of AMS_ACT_FORECASTS_ALL plus campaign and lookup attributes. Significant columns include:

• FORECAST_ID, FORECAST_TYPE, ACT_FCAST_USED_BY_ID — identification and type of the forecast, plus the campaign identifier.
• HIERARCHY, HIERARCHY_LEVEL, LEVEL_VALUE — structure and level of the forecast hierarchy.
• FORECAST_CALENDAR, PERIOD_LEVEL, FORECAST_PERIOD_ID, FORECAST_DATE — the calendar and period context for the forecast.
• FORECAST_UOM_CODE, FORECAST_QUANTITY, FORECAST_REMAINING_QUANTITY, FORECAST_REMAINING_PERCENT — the unit of measure and the core quantity measures, including remaining amounts expressed in quantity and percent.
• FORWARD_BUY_QUANTITY, FORWARD_BUY_PERIOD, CUMULATION_PERIOD_CHOICE — forward-buy and cumulation-related attributes.
• BASE_QUANTITY, BASE_QUANTITY_TYPE, MEANING (LKP1) — the base quantity and its decoded source meaning, resolved through AMS_FCAST_BASE_VOL_SOURCE.
• FORECAST_SPREAD_TYPE, MEANING (LKP2) — the spread type and its decoded meaning, resolved through AMS_FCAST_SPREAD.
• CONTEXT and ATTRIBUTE_CATEGORY/ATTRIBUTE1–15 — descriptive flexfield context and segments.
• ORG_ID — the multi-org operating unit identifier.
• CAMPAIGN_NAME, CAMPAIGN_THEME, DESCRIPTION, ACTUAL_EXEC_START_DATE, ACTUAL_EXEC_END_DATE — campaign identity and execution dates.

Common Use Cases and Queries

Typical uses include campaign forecast reporting, remaining-forecast analysis, and integration extracts that require decoded forecast attributes.

• Listing forecasts for campaigns within an operating unit.
• Aggregating forecast and remaining quantities by campaign or calendar period.
• Filtering by base quantity source, such as forecasts whose base volume source matches a specific lookup meaning.

Sample query retrieving forecasts with decoded lookup meanings:

• SELECT campaign_name, forecast_date, forecast_quantity, forecast_remaining_quantity, base_quantity, base_quantity_type, forecast_spread_type
• FROM apps.ams_campaign_forecasts_all_v
• WHERE org_id = :p_org_id
• AND base_quantity_type = :p_base_vol_source
• AND forecast_date BETWEEN :p_start_date AND :p_end_date
• ORDER BY campaign_name, forecast_date;

Because the view joins AMS_CAMPAIGNS_VL and AMS_LOOKUPS internally, consumers can report campaign context and lookup descriptions directly without additional joins.