Search Results pos_asn_search_result




Overview
Explore in AI Chat

Overview

POS_ASN_SEARCH_RESULT is a transactional, session-scoped table owned by the ICX schema in Oracle E-Business Suite, where it supports the Oracle iProcurement module. Its name reflects its function: it stores the results returned by Advanced Shipment Notice (ASN) search operations performed within iProcurement, allowing the application to persist a user's search result set for the duration of a session and to page through, sort, and act upon those results without re-executing the underlying query.

The table resides in the ICX (iProcurement) schema rather than in the PO (Purchasing) schema, which underscores its role as an application-layer staging structure rather than a core transactional entity. Rows are transient by design: they are keyed by a SESSION_ID that identifies the searching user session, and they are typically purged or refreshed as sessions expire or new searches are initiated. The documentation records 27 columns and a single unique index, POS_ASN_SEARCH_RESULT_U1, defined on (SESSION_ID, PO_SHIPMENT_ID). The only documented foreign key is PO_RELEASE_ID, which references PO_RELEASES_ALL. Based on the heuristic Data Vault classification mined from the foreign key structure, this table is modeled as a standalone object; it behaves as a denormalized staging or result-cache table rather than a conformed hub, link, or satellite.

Key Information Stored

The table is a flattened snapshot of purchase order shipment detail, enriched with supplier and item attributes required for iProcurement ASN display. Its most significant columns include:

No surrogate primary key column is documented; the unique index on (SESSION_ID, PO_SHIPMENT_ID) serves as the business-key candidate that guarantees one row per shipment per session.

Common Use Cases and Queries

The primary use case is diagnostic and performance-oriented: examining exactly which shipments a given iProcurement search returned, and validating that search criteria (supplier, ship-to, category, due date) produced the expected result set. A typical query retrieves the result set for a session:

  • SELECT po_number, line_number, shipment_number, supplier_name, item_number, quantity_ordered, due_date FROM icx.pos_asn_search_result WHERE session_id = :session_id ORDER BY due_date;

Because SESSION_ID is the leading column of the unique index, session-based access is efficient. Additional patterns include: joining to PO_RELEASES_ALL on PO_RELEASE_ID to retrieve release-level attributes not duplicated in the result table; aggregating counts by supplier or ship-to organization to reconcile search hit rates; identifying stale sessions by comparing SESSION_ID ranges against ICX session or FND logon data; and troubleshooting reported discrepancies where an expected shipment does not appear in iProcurement ASN results, by confirming whether a row exists for the relevant PO_SHIPMENT_ID.

Reporting use cases are usually short-lived, since rows are purged with the session. The table is therefore best treated as operational diagnostic data rather than as a historical reporting source; for historical ASN and receipt analysis, the permanent Purchasing and Receiving tables remain authoritative.

Related Objects

The following objects are most significant for joining and dependency analysis:

  • PO_RELEASES_ALL — The sole documented foreign key target, joined via POS_ASN_SEARCH_RESULT.PO_RELEASE_ID = PO_RELEASES_ALL.PO_RELEASE_ID.
  • PO_HEADERS_ALL — Source of authoritative header data; join on PO_HEADER_ID.
  • PO_LINES_ALL — Source of line-level detail; join on PO_LINE_ID.
  • PO_LINE_LOCATIONS_ALL — Source of shipment/schedule detail corresponding to PO_SHIPMENT_ID.
  • POZ_SUPPLIERS — Supplier master data corresponding to SUPPLIER_ID.
  • POZ_SUPPLIER_SITES_ALL — Supplier site data corresponding to SUPPLIER_SITE_ID.
  • MTL_SYSTEM_ITEMS_B — Item master data corresponding to ITEM_ID.
  • EGP_CATEGORIES_B — Category definitions corresponding to CATEGORY_ID.
  • HR_ALL_ORGANIZATION_UNITS — Ship-to inventory organization definitions corresponding to SHIP_TO_ORGANIZATION_ID.

Because the table is a denormalized result cache, most of these relationships are logical rather than enforced by database constraints; only the PO_RELEASE_ID foreign key is documented in the ETRM metadata.