Search Results expected_receipt_date

Overview

The APPS.JAI_PO_SHIPMENT_DTLS_V view is a reporting object delivered as part of the JA (Asia/Pacific Localizations) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its purpose is to present a consolidated, query-friendly projection of receiving shipment data sourced from the Oracle Receiving (RCV) and Purchasing (PO) tables. The view surfaces both internally generated shipments (inventory and internal order receipt sources) and externally generated shipments (vendor receipt source) in a single result set, normalized through a UNION ALL construct.

A defining characteristic of the view is that it converts the underlying DATE columns SHIPPED_DATE and EXPECTED_RECEIPT_DATE into character strings using the TO_CHAR function with the format mask 'DD-MON-RRRR' (the first branch of the union also uses 'DD-MON-RRRR', while the second branch uses a mixed 'DD-MON-YYYY' for shipped date and 'DD-MON-RRRR' for expected receipt date). This design reflects its role as a localization reporting view, where formatted output is preferred over native date types. Users searching for the "expected_receipt_date" attribute will find it exposed directly as the EXPECTED_RECEIPT_DATE column.

Underlying Base Objects

The view is defined over the following documented base objects:

• RCV_SHIPMENT_HEADERS (SYNONYM) — the shipment header, providing SHIPPED_DATE, EXPECTED_RECEIPT_DATE, SHIPMENT_NUM, VENDOR_ID and RECEIPT_SOURCE_CODE.
• RCV_SHIPMENT_LINES (SYNONYM) — shipment lines, supplying FROM_ORGANIZATION_ID, TO_ORGANIZATION_ID and SHIPMENT_LINE_STATUS_CODE.
• ORG_ORGANIZATION_DEFINITIONS (VIEW) — resolves organization names for the FROM_ORGANIZATION_ID.
• PO_VENDORS (VIEW) — resolves vendor names for the vendor-sourced union branch.
• MTL_SUPPLY (SYNONYM) — used to link supply records to shipment headers and to-organization for internal sources.
• HR_GENERAL and HR_SECURITY (PACKAGES) — supporting security and general-purpose functions referenced by the view definition.

All objects reside in the APPS schema, and the view is reported with VALID status. The joins rely on outer joins (OOD.ORGANIZATION_ID(+) and POV.VENDOR_ID(+)) to preserve shipment rows even where the name lookups are unavailable.

Key Columns

• SHIPPED_DATE — the shipment date, formatted as a string via TO_CHAR.
• EXPECTED_RECEIPT_DATE — the anticipated receipt date of the shipment, also returned as a formatted string. This is the column most frequently targeted by users searching on that term.
• SHIPMENT_HEADER_ID — the unique identifier of the shipment header record.
• SHIPMENT_NUM — the human-readable shipment number; the view filters out rows where this is null.
• FROM_ORGANIZATION_ID — the sending organization (organization ID for internal shipments, vendor ID for vendor shipments).
• ORGANIZATION_NAME — the resolved organization or vendor name, depending on the union branch.
• TO_ORGANIZATION_ID — the receiving organization; NULL in the vendor branch.
• TYPE — a discriminator with the literal values 'ORGANIZATION' or 'VENDOR', identifying the origin of each row.

Common Use Cases and Queries

This view is typically used to build shipment tracking and expected receipt reports, particularly for Asia/Pacific localization requirements where formatted dates are required. A common query retrieves shipment details filtered by expected receipt date:

• SELECT shipment_num, shipped_date, expected_receipt_date, organization_name, type FROM apps.jai_po_shipment_dtls_v WHERE expected_receipt_date = '15-JAN-2024';
• SELECT type, COUNT(*) FROM apps.jai_po_shipment_dtls_v GROUP BY type;
• SELECT shipment_num, organization_name, expected_receipt_date FROM apps.jai_po_shipment_dtls_v WHERE type = 'VENDOR';

Because EXPECTED_RECEIPT_DATE is returned as a character string, filtering comparisons must use the matching 'DD-MON-RRRR' format, and date arithmetic requires an explicit TO_DATE conversion. The TYPE column enables straightforward segregation of internal versus vendor shipments in a single query.