Results for “contract_line_num”

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

Overview

ICX_CAT_PRICE_HISTORY is a table in the ICX schema (Oracle iProcurement) within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores catalog price history records used by iProcurement's catalog and punchout/search infrastructure, capturing the unit price, currency, and source (contract, price list, or approved supplier list) associated with a given item and supplier at a point in time. The table supports price validation, comparison, and archival behavior for shopping catalog content presented to requisitioners.

From a Data Vault modeling perspective, the ETRM heuristic classifies this object as standalone. Although it carries foreign keys to ICX_POR_ITEMS and PO_APPROVED_SUPPLIER_LIST, the mined heuristic does not treat it as a classic hub or link; a reasonable modeling suggestion would be to treat it as a satellite-like priced-history record keyed on item, supplier, and effective attributes. This is a heuristic suggestion only and should be validated against actual ETL and retro-fit requirements.

Key Information Stored

The documented physical schema for 12.2.2 contains 32 columns in the ICX schema. The most significant are:

• RT_ITEM_ID — foreign key to ICX_POR_ITEMS; the catalog item this price record describes.
• ASL_ID — foreign key to PO_APPROVED_SUPPLIER_LIST; identifies the approved supplier list entry supplying the price.
• STATUS and ACTIVE_FLAG — control whether the price row is active/valid.
• PRICE_TYPE and SEARCH_TYPE — classify the source and shopping/search mode of the price.
• UNIT_PRICE, CURRENCY, UNIT_OF_MEASURE, FUNCTIONAL_PRICE — the priced values and conversion context.
• SUPPLIER_SITE_ID, SUPPLIER_SITE_CODE — supplier site identity.
• CONTRACT_ID, CONTRACT_LINE_ID, CONTRACT_NUM, CONTRACT_LINE_NUM — contract and contract-line sourcing context.
• TEMPLATE_ID, TEMPLATE_LINE_ID and PRICE_LIST_ID — template and price list references.
• MTL_CATEGORY_ID and ORG_ID — category and operating unit partitioning.
• ARCHIVED_DATE — archival marker for historic rows.

No single-column surrogate primary key is documented in the metadata; RT_ITEM_ID and ASL_ID are the principal business-key candidate components (combined with supplier site, contract, and template context). The standard WHO audit columns — OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — support concurrency control and concurrent-program traceability.

Common Use Cases and Queries

Typical uses include catalog price audit reporting, contract-vs-price-list comparison, and investigating why a shopper saw a given price. A representative join pattern follows:

• Price by item: join ICX_CAT_PRICE_HISTORY to ICX_POR_ITEMS on RT_ITEM_ID, filtering ACTIVE_FLAG = 'Y' and ARCHIVED_DATE IS NULL.
• Supplier sourcing: join to PO_APPROVED_SUPPLIER_LIST on ASL_ID to confirm the approved source and unit price.
• Contract price analysis: aggregate by CONTRACT_NUM and CONTRACT_LINE_NUM to trend UNIT_PRICE and FUNCTIONAL_PRICE over CREATION_DATE.
• Archival/cleanup: report rows where ARCHIVED_DATE is populated, using LAST_UPDATE_DATE and REQUEST_ID to attribute logical deletions to the originating concurrent program.

Related Objects

The most significant related objects are:

• ICX_POR_ITEMS — parent catalog item; joined via RT_ITEM_ID.
• PO_APPROVED_SUPPLIER_LIST — approved supplier source; joined via ASL_ID.
• PO_HEADERS_ALL / PO_LINES_ALL — contract context referenced by CONTRACT_ID and CONTRACT_LINE_ID.
• MTL_CATEGORIES_B — category source for MTL_CATEGORY_ID.
• MTL_SYSTEM_ITEMS_B — underlying item definition reached through ICX_POR_ITEMS.
• ORG_ORGANIZATION_DEFINITIONS — operating unit for ORG_ID.