Results for “ship_to_organization_code”

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

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:

• SESSION_ID — Identifier of the iProcurement user session that owns the result set; part of the unique business key.
• PO_SHIPMENT_ID — The purchasing shipment (order schedule) identifier; the second component of the unique key POS_ASN_SEARCH_RESULT_U1 and the effective grain of the row.
• PO_HEADER_ID, PO_LINE_ID, PO_RELEASE_ID — Foreign-key-style references to the purchase order header, line, and release. PO_RELEASE_ID is the only documented FK, pointing to PO_RELEASES_ALL.
• PO_NUMBER, LINE_NUMBER, SHIPMENT_NUMBER — Human-readable document numbering that allows results to be displayed without joining back to Purchasing tables.
• SUPPLIER_ID, SUPPLIER_NAME, SUPPLIER_SITE_ID, SUPPLIER_SITE_CODE — Supplier and supplier site identity for the shipment.
• SHIP_TO_LOCATION_ID, SHIP_TO_LOCATION_CODE, SHIP_TO_ORGANIZATION_ID, SHIP_TO_ORGANIZATION_CODE, SHIP_TO_ORGANIZATION_NAME — Destination location and inventory organization attributes.
• ITEM_ID, ITEM_NUMBER, ITEM_REVISION, SUPPLIER_ITEM_NUMBER, ITEM_DESCRIPTION — Item identification and descriptive attributes, including the supplier's own item number.
• QUANTITY_ORDERED, UNIT_OF_MEASURE_CODE, DUE_DATE — Ordering quantity, UOM, and scheduled due date.
• CATEGORY_ID, CATEGORY — Purchasing category assignment used for filtering and display.

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.