Search Results list_price_per_unit

Overview

ICX_CATALOG_AVAIL_ITEMS_V (Available Items View) is a seeded APPS-owned database view in Oracle E-Business Suite, owned by the ICX product family associated with Oracle iProcurement. Its purpose is to expose a consolidated, query-ready list of catalog items that are available for requisitioning against active sourcing agreements. Rather than housing data itself, the view projects a curated subset of columns drawn from purchasing and inventory base objects, joined and filtered so that only items tied to currently usable agreements are returned.

In EBS 12.1.1 and 12.2.2 the view carries virtually identical semantics; the release difference lies primarily in the underlying purchasing table structures (notably the multi-org and MOAC changes introduced in 12.2), not in the view's purpose. The view plays a supporting reporting and integration role: it allows iProcurement catalog browsing logic, custom reports, and downstream interfaces to retrieve item-level pricing and sourcing attributes without re-implementing the agreement eligibility rules. This is directly relevant to the searched term list_price_per_unit, which is one of the pricing columns projected by the view from PO_LINES.

Underlying Base Objects

The view is defined over three documented base objects, referenced through synonyms in the APPS schema, plus one PL/SQL package:

• PO_HEADERS (SYNONYM) — the agreement header, supplying agreement type and status/approval attributes.
• PO_LINES (SYNONYM) — the agreement line, supplying item, description, vendor product number, unit price, list price per unit, market price, unit of measure, category, and vendor references.
• MTL_SYSTEM_ITEMS (SYNONYM) — the item master, joined on inventory item ID to validate the item and support item number derivation.
• ICX_UTIL (PACKAGE) — provides the ITEM_FLEX_SEG function invoked to flexfield-derive the item number.

The join logic ties PO_HEADERS to PO_LINES by PO_HEADER_ID, and PO_LINES to MTL_SYSTEM_ITEMS by ITEM_ID = INVENTORY_ITEM_ID. Eligibility is enforced through the filter: the header must be either an approved Quotation (TYPE_LOOKUP_CODE = 'QUOTATION' and STATUS_LOOKUP_CODE = 'A') or a Blanket agreement (TYPE_LOOKUP_CODE = 'BLANKET' and APPROVED_FLAG = 'Y'). Only items with INVENTORY_ITEM_ID > 0, a non-null PO line ITEM_ID, and valid joins survive the DISTINCT projection.

Key Columns

• ITEM_ID — Inventory item identifier on the PO line; the join key to MTL_SYSTEM_ITEMS.
• CREATION_DATE — Creation date inherited from the PO line.
• ITEM_NUMBER — Item number derived via ICX_UTIL.ITEM_FLEX_SEG(MSI.ROWID), i.e., concatenated flexfield segments.
• ITEM_DESCRIPTION — Item description from the agreement line.
• VENDOR_PRODUCT_NUM — Supplier's product number for the item.
• UNIT_PRICE — Negotiated unit price on the agreement line.
• LIST_PRICE_PER_UNIT — The supplier's list price per unit captured on the PO line; useful for computing negotiated savings versus unit price.
• UNIT_MEAS_LOOKUP_CODE — Unit of measure lookup code for the line.
• MARKET_PRICE — Market price recorded on the agreement line.
• VENDOR_ID — Supplier identifier from PO_HEADERS.
• CATEGORY_ID — Purchasing category identifier for the line.

Common Use Cases and Queries

Typical uses include catalog availability reporting, agreement price comparison, savings analysis using list_price_per_unit versus unit_price, and feeding item/vendor extracts into external procurement or punchout systems.

List available items with pricing:

• SELECT item_number, item_description, vendor_id, unit_price, list_price_per_unit, market_price FROM apps.icx_catalog_avail_items_v;

Compare negotiated price to list price for savings:

• SELECT item_number, vendor_id, unit_price, list_price_per_unit, (list_price_per_unit - unit_price) AS price_delta FROM apps.icx_catalog_avail_items_v WHERE list_price_per_unit IS NOT NULL ORDER BY price_delta DESC;

Restrict by item or vendor:

• SELECT item_number, vendor_product_num, unit_price FROM apps.icx_catalog_avail_items_v WHERE vendor_id = :p_vendor_id;

Note that PO_HEADERS and PO_LINES are multi-org objects in 12.2; queries should respect the operating unit context (e.g., via MO_GLOBAL or org_id filters) appropriate to the reporting requirement, even though the view itself does not expose org_id.