Results for “generate_using_formula_id”

50+ results




Overview
Explore in AI Chat

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

Overview

AMS_PRODUCT_PRICING_V is a read-only view owned by the APPS schema and registered as a VALID database object in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the AMS (Marketing) product family and serves as the integration bridge between Advanced Pricing list definitions and the marketing item attribute model. The view exposes a denormalized, item-level price list picture: each row resolves an inventory item against its pricing attribute, its price list header and line, the applicable list price, and the user-defined list status. Because this object materializes the relationship between the QP (Advanced Pricing) tables and the AMS_ITEM_ATTRIBUTES registry, it is the canonical source for reports and interfaces that must answer the question "which price list line applies to which item, at what price, under what status." The presence of the PRICING_ATTRIBUTE_ID column, transcribed verbatim from QP_PRICING_ATTRIBUTES.PRICING_ATTRIBUTE_ID, is significant: it is the searched identifier that links a marketing item record to its specific pricing attribute row, making this view the natural lookup path when a pricing_attribute_id must be resolved back to an item, a price list, or a unit price.

Underlying Base Objects

The view definition joins six relations across the APPS schema:

Two program units are invoked per row: AMS_UTILITY_PVT.GET_UOM_NAME for UNIT_OF_MEASURE and OZF_PRICE_LIST_PVT.GET_USER_STATUS_NAME for USER_STATUS_NAME. Because these are PL/SQL calls embedded in the SELECT list, performance is sensitive to row volume; predicates on LIST_HEADER_ID or PRICING_ATTRIBUTE_ID should be pushed down wherever possible.

Key Columns

  • PRICING_ATTRIBUTE_ID — the pricing attribute identifier from QP_PRICING_ATTRIBUTES; the primary joining key back to pricing setups.
  • PRODUCT_ID — the character form of the inventory item identifier (PRODUCT_ATTR_VALUE).
  • LIST_HEADER_ID / LIST_LINE_ID — price list header and line identifiers.
  • NAME / DESCRIPTION / CURRENCY — header name, description, and currency code.
  • UNIT_PRICE / OPERAND — the list price and its operand operator.
  • START_DATE / END_DATE — line effective dates.
  • UNIT / UNIT_OF_MEASURE — the pricing UOM code and its decoded name.
  • PRODUCT_NAME — the concatenated item segment string from MTL_SYSTEM_ITEMS_KFV.
  • STATUS_CODE / USER_STATUS_ID / USER_STATUS_NAME — price list approval/status information.
  • ITEM_OWNER_ID / OWNER_ID — owning party identifiers for the item and the list.

Common Use Cases and Queries

A frequent requirement is resolving a known pricing attribute to its item and price. The following query returns the item name, list, and unit price for a specific identifier:

  • SELECT pricing_attribute_id, product_id, product_name, name, currency, unit_price, unit_of_measure, start_date, end_date FROM ams_product_pricing_v WHERE pricing_attribute_id = :p_pricing_attribute_id;
  • SELECT product_name, name, currency, unit_price, user_status_name FROM ams_product_pricing_v WHERE product_id = TO_CHAR(:p_inventory_item_id) AND SYSDATE BETWEEN start_date AND NVL(end_date, SYSDATE + 1);
  • SELECT name, COUNT(*) FROM ams_product_pricing_v GROUP BY name; — a diagnostic count of item-level lines per price list, useful when validating pricing attribute setup before migration or reconciliation.

Typical applications include marketing collateral price feeds, item-to-price-list reconciliation reports, and interfaces that must publish active list prices to external channels. Queries should always constrain by date range or by pricing_attribute_id, since the embedded PL/SQL functions and the cross-join structure make full scans expensive on large QP_PRICING_ATTRIBUTES volumes.