Results for “mrkt_level_value”

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

Overview

The AMS_BASE_SALES_VOL_ALL table is a core data object within the Oracle Marketing (AMS) module of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. Its documented purpose is to store the base sales volumes for a product prior to the application of any promotions. In this capacity the table serves as the baseline demand reference against which promotional lift, incremental volume, and promotion effectiveness are measured. Each record captures a measured or forecast quantity for a given product, market level, customer, and time period, free of promotional influence. Analysts and promotion planners use these rows as the control value in lift calculations, comparing promoted sales against the underlying unpromoted baseline.

From a dimensional modeling perspective, the mined Data Vault classification is satellite-leaning. This suggests the table behaves primarily as a descriptive satellite attached to a business hub keyed by product, customer, or market, with the surrogate BASE_SALES_ID providing row-level identity rather than a standalone business key. The presence of WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY), ORG_ID, and a SECURITY_GROUP_ID foreign key reinforces the satellite interpretation, indicating the table records changing descriptive measures over time for an underlying business entity.

Key Information Stored

The surrogate primary key is BASE_SALES_ID, documented as the table's single-column primary key. This is the technical identifier; It is not a business key. The remaining columns describe the measurement context and value. The most significant are:

• BASE_SALES_QUANTITY and BASE_SALES_UOM_CODE – the measured base volume and its unit of measure.
• INVENTORY_ITEM_ID – the product for which the base volume is recorded.
• CUST_ACCOUNT_ID – the customer account (references HZ_CUST_ACCOUNTS) associated with the volume.
• MRKT_HIERARCHY, MRKT_HIERARCHY_LEVEL, and MRKT_LEVEL_VALUE – the marketing hierarchy context; MRKT_LEVEL_VALUE is a foreign key to JTF_TERR_ALL.
• PRODUCT_HIERARCHY, PRODUCT_HIERARCHY_LEVEL, and PRODUCT_LEVEL_VALUE – the product hierarchy context for aggregation.
• PERIOD_TYPE, PERIOD_ID, and ON_DATE – the time dimension of the measurement.
• ORG_ID – the operating unit, supporting multi-org data segregation.
• SECURITY_GROUP_ID – a foreign key to FND_SECURITY_GROUPS controlling row-level access.

Fifteen generic ATTRIBUTE columns and CONTEXT/ATTRIBUTE_CATEGORY are provided for extensibility. No unique business-key index is documented beyond the surrogate primary key.

Common Use Cases and Queries

Typical scenarios include baseline reporting, promotion lift analysis, and forecast preparation. A representative query to retrieve base volumes for a product and period follows:

• SELECT base_sales_quantity, base_sales_uom_code, on_date FROM ams.ams_base_sales_vol_all WHERE inventory_item_id = :item AND period_id = :period;
• Comparison of base versus promoted volumes by joining this table to promotion result tables on product, customer, and period.
• Aggregation by market hierarchy for territory-level demand reporting, filtering on MRKT_LEVEL_VALUE.
• Multi-org reporting filtered by ORG_ID and secured through SECURITY_GROUP_ID.

Related Objects

The documented foreign keys and schema relationships connect this table to the following significant objects:

• JTF_TERR_ALL – joined via MRKT_LEVEL_VALUE for marketing territory context.
• HZ_CUST_ACCOUNTS – joined via CUST_ACCOUNT_ID for customer identity.
• FND_SECURITY_GROUPS – joined via SECURITY_GROUP_ID for row-level security.
• AMS promotional and result tables – logically joined on INVENTORY_ITEM_ID, CUST_ACCOUNT_ID, and PERIOD_ID to compute promotion lift.
• INVENTORY ITEM master – referenced through INVENTORY_ITEM_ID for product descriptions.

Together these relationships position AMS_BASE_SALES_VOL_ALL as the unpromoted baseline source within the Oracle Marketing analytical model.