Search Results por_rcv_all_items_v

Overview

POR_RCV_ALL_ITEMS_V is a reporting view owned by the Oracle iProcurement (ICX) module within Oracle E-Business Suite 12.1.1 and 12.2.2. As its ETRM description states, it is a "View for all items that can be received," consolidating receipt-eligible purchasing line and shipment information into a single denormalized structure. The view joins purchasing, vendor, and HR tables to expose the shipment, distribution, and item attributes that drive receiving and delivery transactions in iProcurement and the receiving workbench.

The ETRM metadata notes that this object is not implemented in this database, meaning it is deployed only where the relevant iProcurement and receiving products are licensed and installed. The view references the hint INDEX(PH PO_HEADERS_U2), which is the object the user searched for; this hints the optimizer toward the PO_HEADERS unique key index when resolving header joins, a common tuning pattern for receipt-related reporting where header lookups dominate execution cost.

Underlying Base Objects

The documented base objects are consolidated into a single SELECT with no separately documented referenced objects. The view is defined over the following tables:

• PO_HEADERS POH — purchasing document header (also aliased as PH in the index hint).
• PO_LINES POL — document lines carrying item and description data.
• PO_LINE_LOCATIONS POLL — shipment rows keyed by LINE_LOCATION_ID.
• PO_DISTRIBUTIONS POD — distribution detail supplying quantities and destination type.
• PO_RELEASES POR — blanket/release context and release numbers.
• PO_VENDORS, PO_VENDOR_SITES, PO_LOOKUP_CODES, MTL_UNITS_OF_MEASURE — supplier, site, lookup, and UOM enrichment.
• PER_ALL_PEOPLE_F PAPF — requestor identity resolved from DELIVER_TO_PERSON_ID or AGENT_ID.

A scalar function, POR_RCV_TRANSACTION_SV.GET_SHIPMENT_NUM, is invoked to derive the shipment display value, and the join to PAPF is constrained by an effective-date condition on the person record.

Key Columns

• PO_HEADER_ID, PO_LINE_ID, PO_LINE_LOCATION_ID, PO_DISTRIBUTION_ID — primary transaction keys used across receiving.
• PO_NUMBER, PO_NUM_REL_NUM, PO_RELEASE_NUMBER, PO_LINE_NUMBER, PO_SHIPMENT_NUMBER — human-readable document identifiers.
• ITEM_ID, ITEM_DESCRIPTION, PRIMARY_UOM, UOM_CLASS — item and unit-of-measure detail (description truncated to 240 characters via SUBSTR).
• QUANTITY_ORDERED, QUANTITY_DELIVERED, QUANTITY_CANCELLED, EXPECTED_RECEIPT_QTY — derived quantities; expected receipt is the ordered less cancelled and delivered amounts.
• REQUESTOR_ID, REQUESTOR — buyer or delivery person resolved through NVL.
• SOURCE, SUPPLIER_SITE, VENDOR_ID, VENDOR_SITE_ID — supplier identification.
• NEED_BY_DATE, PROMISED_DATE, EXPECTED_RECEIPT_DATE — scheduling with promise date falling back to need-by date.
• RECEIPT_REQUIRED_FLAG, DESTINATION_TYPE_CODE, RECEIPT_DATE (SYSDATE) — receiving eligibility and control flags.

Common Use Cases and Queries

The view is typically queried to populate iProcurement receiving pages, build open receipt reports, and drive supplier delivery metrics. It supports filtering on destination type and receipt-required flag to isolate goods eligible for receipt versus those requiring inspection or destination-based handling.

Sample query listing all outstanding receiptable shipments:

• SELECT PO_NUMBER, PO_NUM_REL_NUM, PO_LINE_NUMBER, PO_SHIPMENT_NUMBER, ITEM_DESCRIPTION, ORDERED_QTY, EXPECTED_RECEIPT_QTY, EXPECTED_RECEIPT_DATE FROM POR_RCV_ALL_ITEMS_V WHERE EXPECTED_RECEIPT_QTY > 0 AND RECEIPT_REQUIRED_FLAG = 'Y' ORDER BY EXPECTED_RECEIPT_DATE;

Requestors can be linked to outstanding deliverables via REQUESTOR_ID, and the PO_DISTRIBUTION_ID supports reconciliation back to accounting distributions. Because the view carries a partial outer-join signature through its NVL-based requestor resolution, query predicates should generally be applied on base columns rather than computed expressions to preserve full-table access paths and encourage the PO_HEADERS_U2 index hint to remain effective.