Results for “pos_asn_view_for_search”

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

Overview

The POS_ASN_VIEW_FOR_SEARCH view is a reporting object owned by the APPS schema within the iSupplier Portal (POS) module of Oracle E-Business Suite. It is a UNION query that consolidates Advance Shipment Notice (ASN) shipment data drawn from two distinct sourcing paths: the open inbound interface tables (RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE) and the confirmed shipment tables (RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES). The view exposes a flattened, de-normalized result set designed specifically to support ASN search and inquiry screens within the iSupplier Portal, allowing suppliers and internal buyers to locate ASNs by shipment number, purchase order, item, and destination.

The object is documented as VALID and, per ETRM 12.2.2 metadata, is defined over synonym-based references to eight underlying base objects. Its UNION structure effectively presents both pending and confirmed ASN records in a single, uniform projection, making it a convenient search substrate for the portal's ASN query functionality across both 12.1.1 and 12.2.2 releases.

Underlying Base Objects

The documented base objects referenced by the view are listed below. The first SELECT branch reads from the receiving interface tables, while the second branch reads from the shipment tables; both branches share the PO headers, releases, items, and location lookups.

• PO_HEADERS_ALL — purchase order headers, supplying SEGMENT1 and VENDOR_CONTACT_ID.
• PO_RELEASES_ALL — release information used to construct the PO number (header-release concatenation).
• RCV_HEADERS_INTERFACE — inbound ASN header interface rows (first branch).
• RCV_TRANSACTIONS_INTERFACE — inbound ASN transaction interface rows (first branch).
• RCV_SHIPMENT_HEADERS — confirmed ASN shipment headers (second branch).
• RCV_SHIPMENT_LINES — confirmed ASN shipment lines (second branch).
• MTL_SYSTEM_ITEMS_KFV — item key flexfield view providing CONCATENATED_SEGMENTS as the item number.
• HR_LOCATIONS_ALL — HR location master supplying LOCATION_CODE.
• HZ_LOCATIONS — Trading Community location data supplying ADDRESS1 and CITY.

The two branches are joined on PO header and release, with outer joins (+) applied to PO_RELEASES_ALL, MTL_SYSTEM_ITEMS_KFV, HR_LOCATIONS_ALL, and HZ_LOCATIONS so that records are retained even when the corresponding location or item detail is absent. Both branches restrict results to RSH/RHI.ASN_TYPE values of 'ASN' or 'ASBN'.

Key Columns

The view projects seven documented columns that align to the search screen's presentation fields.

• ASN_SHIPMENT_NUM — the ASN shipment number from the interface or shipment header.
• ASN_PO_NUMBER — the PO number, formatted as SEGMENT1 when no release exists, or SEGMENT1-RELEASE_NUM when a release applies.
• ASN_ITEM_NUMBER — the item's concatenated segment value from MTL_SYSTEM_ITEMS_KFV.
• ASN_SUPPLIER_ITEM_NUMBER — the vendor item number from the transaction or shipment line.
• ASN_VENDOR_CONTACT_ID — the vendor contact identifier from PO_HEADERS_ALL.
• SHIP_TO_LOCATION_CODE — the destination code. The view prefers HR_LOCATIONS_ALL.LOCATION_CODE; where that is null, it derives a fallback value from the combined address (ADDRESS1-CITY, truncated to 40 characters).
• HEADER_ID — the ASN header identifier, sourced from RHI.HEADER_INTERFACE_ID in the first branch and RSH.SHIPMENT_HEADER_ID in the second.

Common Use Cases and Queries

The primary use case is the iSupplier Portal ASN search, which matches supplier-entered search terms against shipment number, PO number, and item fields. The view's DISTINCT keyword prevents duplicate rows across the two sourcing branches, so a query returns one row per unique combination of shipment, PO, item, and location.

A typical query searching by destination location code, consistent with the ship_to_location_code search term, is shown below.

• Locate ASNs for a specific destination: SELECT asn_shipment_num, asn_po_number, asn_item_number, ship_to_location_code FROM apps.pos_asn_view_for_search WHERE ship_to_location_code = :p_location_code;
• Search by PO and item: SELECT asn_shipment_num, asn_supplier_item_number, ship_to_location_code FROM apps.pos_asn_view_for_search WHERE asn_po_number = :p_po AND asn_item_number = :p_item;
• Resolve a header for drill-down: SELECT header_id, asn_shipment_num FROM apps.pos_asn_view_for_search WHERE asn_shipment_num = :p_shipment_num;

Because SHIP_TO_LOCATION_CODE may be derived from the Trading Community address when no HR location code exists, reports relying on exact location codes should account for both the HR and HZ sourcing paths when interpreting results.