Search Results agent_start_date

Overview

ICX_POR_REQ_CONTRACTS is an APPS-owned database view in the Oracle iProcurement (ICX) module, maintained with VALID status and exposed in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents a consolidated, requisition-facing picture of purchasing documents of type BLANKET and QUOTATION, joining header and line detail from the PO schema with supplier, unit of measure, currency, and buyer information. Its primary role is to supply the iProcurement "Search for a Contract" and contract-referencing requisition flows with the contract attributes required to validate, display, and price a requisition line. Report and integration developers also use the view as a lightweight, security-relevant source for blanket purchase agreement (BPA) and global agreement data without navigating the full PO_HEADERS_ALL / PO_LINES_ALL join set.

Underlying Base Objects

The documented base objects are PO_HEADERS_ALL and PO_LINES_ALL (synonyms) for the header and line records; PO_VENDORS (view) for supplier name and identifiers; PER_PEOPLE_F (view) for the buyer/agent; MTL_UNITS_OF_MEASURE_TL (synonym) for the UOM code; GL_SETS_OF_BOOKS (view), FINANCIALS_SYSTEM_PARAMS_ALL (synonym), and GL_DAILY_CONVERSION_TYPES (synonym) for set-of-books, operating unit, and rate type context; and the HR_GENERAL, HR_PERSON_NAME, and HR_SECURITY packages used for person naming and security enforcement.

The header restriction is PH.TYPE_LOOKUP_CODE IN ('BLANKET','QUOTATION'), so only agreements and quotations are returned; standard purchase orders are excluded. The agent join (PH.AGENT_ID = PPF.PERSON_ID) and the vendor join (PH.VENDOR_ID = PV.VENDOR_ID) are inner joins, while the rate type join to GL_DAILY_CONVERSION_TYPES is an outer join. Operating unit filtering is handled by the OR predicate on ORG_ID between the header and FINANCIALS_SYSTEM_PARAMS_ALL, which permits both global (ORG_ID IS NULL) and organization-specific agreements.

Key Columns

• CONTRACT_NUM, CONTRACT_LINE, CONTRACT_ID, CONTRACT_LINE_ID — document number, line number, and the underlying PO_HEADER_ID / PO_LINE_ID keys.
• CONTRACT_START_DATE, CONTRACT_END_DATE — derived from PH.START_DATE and PH.END_DATE; these are the columns most relevant to a search on "contract_start_date," used to determine contract validity for a given effective date.
• CONTRACT_APPROVED_DATE, CONTRACT_APPROVED_FLAG, CONTRACT_CANCEL_FLAG, CONTRACT_FROZEN_FLAG, CONTRACT_CLOSED_CODE, CONTRACT_LINE_CLOSED_CODE, CONTRACT_LINE_CANCEL_FLAG, CONTRACT_STATUS_LOOKUP_CODE — approval, cancel, freeze, and closure state of the header and line.
• CONTRACT_QUOTATION_CLASS_CODE — distinguishes quotation classes among agreement documents.
• UNIT_PRICE / CONTRACT_PRICE, CURRENCY, CONTRACT_CURRENCY, CONTRACT_RATE_TYPE, CONTRACT_RATE, CONTRACT_RATE_DATE — pricing at the document rate, with converted unit price computed as ROUND(UNIT_PRICE * NVL(RATE,1),5).
• SUPPLIER, SUPPLIER_ID, SUPPLIER_SITE_ID, SUPPLIER_PART_NUMBER — sourcing details for the agreement.
• AGENT_ID, AGENT_START_DATE, AGENT_END_DATE — buyer and effective dating from PER_PEOPLE_F.
• ITEM_ID, ITEM_DESCRIPTION, CATEGORY_ID, LINE_TYPE_ID, UNIT_OF_MEASURE, OPERATING_UNIT_ID, PICTURE_URL (ATTRIBUTE14), EXPIRATION_DATE — item and display attributes used by iProcurement screens.

Common Use Cases and Queries

Typical scenarios include determining which blanket agreements or quotations are active on a requisition date, retrieving contracted price and supplier for punchout or internal item selection, and reporting agreement coverage by operating unit.

SELECT contract_num, contract_line, supplier, item_description,
       unit_price, currency, contract_start_date, contract_end_date
FROM   apps.icx_por_req_contracts
WHERE  contract_start_date <= SYSDATE
AND    NVL(contract_end_date, SYSDATE) >= SYSDATE
AND    operating_unit_id = :org_id;

A second common pattern checks approved, non-frozen agreements for a specific item and supplier:

SELECT contract_id, contract_line_id, contract_num, unit_price
FROM   apps.icx_por_req_contracts
WHERE  item_id = :item_id
AND    supplier_id = :vendor_id
AND    contract_approved_flag = 'Y'
AND    NVL(contract_frozen_flag,'N') = 'N'
AND    NVL(contract_cancel_flag,'N') = 'N';

Because the view enforces agreement/quote document types and incorporates HR security packages, direct ad hoc queries should respect operating unit context and the standard iProcurement security model rather than bypassing it.