Results for “functional_price”

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

Overview

ICX_CAT_ITEM_PRICES is the pricing table that underpins the Oracle iProcurement catalog and searchable storefront in Oracle E-Business Suite 12.1.1 and 12.2.2. Owned by the ICX schema, it stores the negotiated, contract, supplier-list, and template-based prices that iProcurement renders to requisitioning users when they shop a catalog, perform a keyword search, or compare supplier offers for a given inventory item. Each row associates a purchasable item with a price, a source construct (contract, ASL, price list, or template), a supplier site, and the currency and unit of measure in which that price is expressed, allowing iProcurement to return localized, buyer-ready pricing at the point of requisition.

The table is physically wide, with 46 documented columns in the ETRM 12.2.2 schema. Functionally it behaves as a transactional price container for catalog shopping rather than a master or reference entity. Under the heuristic Data Vault classification supplied with the metadata, ICX_CAT_ITEM_PRICES is modeled as standalone. In Data Vault terms this suggests treating it as an independent satellite-like structure that captures pricing attributes keyed to external business entities, rather than as a true hub or link owned by ICX; the referencing relationships are modeled as foreign keys to other products' hubs. This classification should be read as a modeling suggestion only, since the object carries no documented self-referencing parent-child hierarchy.

Key Information Stored

The table's central business content is the price itself and the context that qualifies it. The most operationally significant columns are:

• RT_ITEM_ID – foreign key to ICX_POR_ITEMS, identifying the iProcurement catalog item being priced.
• PRICE_TYPE – discriminates the pricing source, distinguishing contract, ASL, template, and related price origins.
• UNIT_PRICE, FUNCTIONAL_PRICE, CURRENCY, UNIT_OF_MEASURE – the quoted price, its functional-currency equivalent, and the currency and UOM in which it is stated.
• ASL_ID – foreign key to PO_APPROVED_SUPPLIER_LIST, tying the price to an approved supplier-list entry.
• SUPPLIER_SITE_ID, SUPPLIER_SITE_CODE, SUPPLIER_NUMBER – the supplier and ship-to site context for the offer.
• CONTRACT_ID, CONTRACT_LINE_ID, CONTRACT_NUM, CONTRACT_LINE_NUM – the purchasing contract and line that source the price.
• TEMPLATE_ID, TEMPLATE_LINE_ID – the iProcurement template lineage for template-driven pricing.
• LINE_TYPE_ID – foreign key to PO_LINE_TYPES_B, classifying the purchasing line type.
• INVENTORY_ITEM_ID, MTL_CATEGORY_ID, ORG_ID – the inventory item, category, and operating unit context.
• ACTIVE_FLAG, ALLOW_PRICE_OVERRIDE_FLAG, NOT_TO_EXCEED_PRICE – enablement and price-control behavior exposed to the shopper.
• OBJECT_VERSION_NUMBER – optimistic locking for concurrent updates.

No surrogate primary key or unique business-key index is documented in the supplied metadata. Business-key candidates are the combination of RT_ITEM_ID with PRICE_TYPE, ASL_ID, SUPPLIER_SITE_ID, and CONTRACT_LINE_ID; in practice the row is identified by the item-plus-source combination rather than by a single column. Audit columns include CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical reporting scenarios include catalog price validation, contract price-versus-ASL price reconciliation, and requisition price sourcing diagnostics. A representative query joins the price back to the iProcurement item and supplier:

• List active catalog prices for an item: SELECT rt_item_id, price_type, unit_price, currency FROM icx.icx_cat_item_prices WHERE rt_item_id = :p_item_id AND active_flag = 'Y';
• Compare contract versus ASL pricing: SELECT price_type, contract_num, asl_id, supplier_site_code, unit_price FROM icx.icx_cat_item_prices WHERE inventory_item_id = :p_item AND org_id = :p_org;
• Audit rows changed within a window: SELECT * FROM icx.icx_cat_item_prices WHERE last_update_date >= :p_from_date;
• Trace template-driven price overrides via TEMPLATE_LINE_ID for a given buyer and operating unit.

Because pricing is refreshed by concurrent programs, reporting queries should constrain ORG_ID and ACTIVE_FLAG to avoid obsolete rows.

Related Objects

The documented foreign keys anchor ICX_CAT_ITEM_PRICES to three master objects, which with their related companions form the principal join surface:

• ICX_POR_ITEMS – joined on RT_ITEM_ID; the iProcurement catalog item master.
• PO_APPROVED_SUPPLIER_LIST – joined on ASL_ID; the approved supplier-list header for sourcing.
• PO_LINE_TYPES_B – joined on LINE_TYPE_ID; the purchasing line type definition.
• PO_HEADERS_ALL / PO_LINES_ALL – referenced indirectly through CONTRACT_ID and CONTRACT_LINE_ID for contract pricing.
• POZ_SUPPLIERS / AP_SUPPLIERS and supplier site tables – resolve SUPPLIER_NUMBER and SUPPLIER_SITE_ID.
• MTL_SYSTEM_ITEMS_B and MTL_CATEGORIES_B – resolve INVENTORY_ITEM_ID and MTL_CATEGORY_ID.
• ICX_POR_ITEM_TEMPLATES / template lines – resolve TEMPLATE_ID and TEMPLATE_LINE_ID.

These relationships let developers reconstruct the full sourcing path, from catalog item to supplier, contract, and price basis, directly from ICX_CAT_ITEM_PRICES.