Results for “contract_num”

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

Overview

ICX_POR_EXT_ACTIVE_CONTRACTS is an APPS-owned database view within the Oracle iProcurement (ICX) product family, delivered as part of Oracle E-Business Suite 12.1.1 and 12.2.2. The view exposes currently active purchasing contracts — blanket purchase agreements and catalog quotations — in a flattened, reporting-friendly structure. Its name derives from the ICX iProcurement "POR" (Purchasing/Procurement) namespace and the "EXT" designation indicating an external-facing or extract-oriented projection.

The view consolidates header-level and line-level contract data from the purchasing schema, resolving supplier names, currencies, operating units, and exchange-rate information into a single denormalized result set. This makes it suitable for reporting, supplier punch-out and content refresh processing, catalog ingestion routines, and integration with external procurement or self-service requisitioning tools. In iProcurement, this view underpins the visibility of contract pricing information surfaced to requesters during catalog shopping.

Underlying Base Objects

The view is defined over the following documented base objects:

• PO_HEADERS_ALL — contract header records (blanket and quotation document types).
• PO_LINES_ALL — contract line records supplying item, category, price, and expiration data.
• PO_LINE_TYPES — line type reference table filtered to exclude outside-operation lines.
• PO_VENDORS — supplier name resolution via VENDOR_ID.
• PO_LINE_LOCATIONS_ALL and PO_QUOTATION_APPROVALS_ALL — additional purchasing objects referenced in the view's dependency chain.
• GL_SETS_OF_BOOKS — set-of-books context.
• GL_DAILY_CONVERSION_TYPES — exchange rate type validation.
• FINANCIALS_SYSTEM_PARAMS_ALL — financials system parameters joined by ORG_ID for operating-unit scoping.

The header-to-line join (PH.PO_HEADER_ID = PL.PO_HEADER_ID) is the structural backbone, with reference lookups applied as outer or inner joins depending on the attribute.

Key Columns

• CONTRACT_NUM (PH.SEGMENT1) — the human-readable contract document number.
• CONTRACT_LINE (PL.LINE_NUM) and CONTRACT_LINE_ID (PL.PO_LINE_ID) — line identifier and surrogate key.
• ITEM_ID, ITEM_DESCRIPTION, CATEGORY_ID — item and category identification.
• UNIT_PRICE / CONTRACT_PRICE — computed price via ROUND(PL.UNIT_PRICE * NVL(PH.RATE,1), 5); CONTRACT_PRICE retains the raw unit price.
• CURRENCY / CONTRACT_CURRENCY — set-of-books and contract currencies respectively.
• SUPPLIER, SUPPLIER_ID, SUPPLIER_SITE_ID — vendor details from PO_VENDORS.
• OPERATING_UNIT_ID (PL.ORG_ID) — the inventory organization/operating unit scope.
• LAST_UPDATE_DATE — GREATEST of header and line last update timestamps, useful for incremental extracts.

Common Use Cases and Queries

Typical scenarios include validating active contract pricing for a supplier during punch-out content refresh, auditing expired or frozen contract visibility, and reconciling contract extracts by operating unit. A representative query retrieving active lines for a supplier follows:

• SELECT CONTRACT_NUM, CONTRACT_LINE, ITEM_DESCRIPTION, UNIT_PRICE, CURRENCY, SUPPLIER, OPERATING_UNIT_ID FROM APPS.ICX_POR_EXT_ACTIVE_CONTRACTS WHERE SUPPLIER_ID = :p_vendor_id AND OPERATING_UNIT_ID = :p_org_id;
• SELECT CONTRACT_NUM, COUNT(*) FROM APPS.ICX_POR_EXT_ACTIVE_CONTRACTS GROUP BY CONTRACT_NUM HAVING COUNT(*) > 1;

The view's WHERE clause applies date-boundary checks (START_DATE, END_DATE, EXPIRATION_DATE), approval and freeze filters, and closed-code exclusions, ensuring only currently effective contracts are returned. Because PO_VENDORS and GL_SETS_OF_BOOKS are themselves views in 12.2.x, underlying performance depends on those definitions.