Search Results base_sales_uom_code

Overview

AMS_CAMPAIGN_PRODUCT_VOLUMES_V is a reporting view owned by the APPS schema within the Oracle Marketing (AMS) module. It presents campaign-level product information enriched with inventory item details and base sales volume metrics. The view consolidates marketing campaign product assignments with descriptive item attributes sourced from the inventory master and sales volume data captured in the marketing base sales tables. It is designed to support analytical reporting, dashboards, and integrations that require a unified representation of which products belong to a campaign, along with their pricing and historical sale quantities.

The view is read-only and is not intended for transactional data entry. Database objects such as this are typically consumed by Oracle Marketing analytics, custom reports, and third-party integration layers that need a denormalized, query-friendly projection of campaign and product activity. Because it exposes both level identifiers and human-readable descriptions, it can be used directly in BI Publisher reports, OBIEE subject areas, or ad hoc SQL without additional joins to lookup tables.

Underlying Base Objects

The view is defined over three documented base objects. The first, AMS_ACT_PRODUCTS_V, supplies the campaign activity and product assignment context, including the master object type and its meaning, level type, category identifiers, organization, inventory item, and item description. The join predicate restricts records to those where MASTER_OBJECT_TYPE equals 'CAMP', ensuring only campaign-related rows are returned.

The second base object, MTL_SYSTEM_ITEMS_VL, is the inventory item master view. It is joined on matching INVENTORY_ITEM_ID and ORGANIZATION_ID, and contributes LIST_PRICE_PER_UNIT as well as PRIMARY_UOM_CODE. This is the object most directly relevant to the user's search for "inventory_item_description," since item numbering, description, and organization context flow through this join.

The third base object, AMS_BASE_SALES_VOL_ALL, is referenced through a synonym and contributes base sales quantities, UOM codes, period information, and the on-date value. Its joins to the item master on PRODUCT_LEVEL_VALUE and BASE_SALES_UOM_CODE are outer joins, meaning campaigns with items that have no recorded sales volume still appear, with BASE_SALES_QUANTITY defaulting to zero via NVL.

Key Columns

• ACTIVITY_PRODUCT_ID — unique identifier for the activity product record, useful as a join key.
• MASTER_OBJECT_TYPE / MASTER_OBJECT_TYPE_MEANING — the parent object type (campaign) and its descriptive label.
• MASTER_OBJECT_ID — the identifier of the parent campaign.
• LEVEL_TYPE_CODE / LEVEL_TYPE — indicates the level at which the product is associated.
• CATEGORY_ID / CATEGORY_NAME / CATEGORY_SET_ID / CATEGORY_SET_NAME — categorization metadata for the product.
• ORGANIZATION_ID — the inventory organization context.
• INVENTORY_ITEM_ID / INVENTORY_ITEM_NUMBER / INVENTORY_ITEM_DESCRIPTION — item identity and descriptive attributes sourced from MTL_SYSTEM_ITEMS_VL.
• LIST_PRICE_PER_UNIT — unit list price of the item in the given organization.
• BASE_SALES_QUANTITY / BASE_SALES_UOM_CODE — aggregated base sales quantity and its unit of measure.
• PERIOD_ID / PERIOD_TYPE / ON_DATE — the reporting period and as-of date associated with the sales volume.

Common Use Cases and Queries

Typical use cases include campaign product performance analysis, price-versus-volume reporting, and feeds that need a flattened view of campaign products with inventory descriptions. A common query retrieves item descriptions for products in a specific campaign:

SELECT inventory_item_number, inventory_item_description, list_price_per_unit, base_sales_quantity FROM ams_campaign_product_volumes_v WHERE master_object_id = :campaign_id ORDER BY inventory_item_number;

Another frequent pattern aggregates sales volume by period for a set of campaigns, or filters on LEVEL_TYPE and CATEGORY_NAME to isolate product segments. Because BASE_SALES_QUANTITY is outer-joined and defaulted to zero, the view is safe for item-level reports where some campaign products have not yet generated volume. Joining this view back to AMS_ACT_PRODUCTS_V or the campaign header tables on MASTER_OBJECT_ID and ACTIVITY_PRODUCT_ID enables richer campaign-level analytics while retaining the item description and price information already resolved by the view.