Search Results icx_por_price_list_lines_cache




Overview
Explore in AI Chat

Overview

The ICX_POR_PRICE_LIST_LINES_CACHE table is a core data object within the Oracle iProcurement (ICX) module of Oracle E-Business Suite (EBS) releases 12.1.1 and 12.2.2. It functions as a performance-optimized cache for price list line information. Its primary role is to store pre-processed and readily accessible pricing data, which is critical for the real-time display of item costs, supplier catalogs, and requisition creation within the iProcurement self-service interface. By caching this data, the system significantly reduces the computational overhead of dynamically calculating prices from complex sourcing rules and agreements for each user session, thereby enhancing application responsiveness and user experience.

Key Information Stored

The table's structure is designed to hold denormalized price list details keyed for rapid retrieval. The primary key, enforced by the ICX_POR_PRICE_LIST_LINES_C_PK constraint, uniquely identifies a cached price line through a combination of HEADER_ID (linking to the parent price list), ITEM_ID (linking to the catalog item), and SESSION_KEY (often used for session-specific or temporary data). Important foreign key relationships define its data integrity, linking to supplier and buyer party information (HZ_PARTIES), the master price list header (ICX_POR_PRICE_LISTS), the item master (ICX_POR_ITEMS), and currency definitions (FND_CURRENCIES). While the specific non-key columns are not detailed in the provided metadata, a cache of this nature typically stores critical pricing attributes such as unit price, effective dates, price breaks, and any applicable modifiers or discounts for the given item, supplier, and currency context.

Common Use Cases and Queries

The primary use case is the real-time price lookup during a shopping session in Oracle iProcurement. When a user searches for or selects an item, the application queries this cache to determine the applicable price based on their operating unit, supplier agreements, and active date. Common SQL patterns involve joining this table to item and supplier tables to generate price catalogs or validate pricing during requisition submission. For reporting and troubleshooting, administrators may query the cache to analyze price availability or identify caching issues. A typical diagnostic query would join to related master tables:

  • SELECT ipllc.*, ipi.item_number, hzp_s.party_name supplier_name FROM icx.icx_por_price_list_lines_cache ipllc JOIN icx.icx_por_items ipi ON ipllc.item_id = ipi.item_id JOIN hz_parties hzp_s ON ipllc.supplier_id = hzp_s.party_id WHERE ipllc.header_id = <PRICE_LIST_ID>;

Related Objects

As indicated by its foreign key constraints, ICX_POR_PRICE_LIST_LINES_CACHE is centrally connected to several fundamental EBS tables. Its immediate parent is the ICX_POR_PRICE_LISTS table, which stores the header definitions for the cached lines. It references the ICX_POR_ITEMS table for catalog item details. For party information, it links to the HZ_PARTIES table twice: once for the SUPPLIER_ID and once for the BUYER_ID. It also maintains referential integrity with the FND_CURRENCIES table for the CURRENCY_CODE. This object is intrinsically part of the iProcurement pricing engine and is populated by background concurrent processes or application logic that refreshes the cache from the transactional sourcing rules defined in modules like Oracle Sourcing or Purchasing.