Results for “pos_consigned_shipments_v”

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

Overview

POS_CONSIGNED_SHIPMENTS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the POS (iSupplier Portal) product family and is documented as being "used to select consigned shipments." In practice, the view consolidates approved purchasing document data — headers, lines, and shipment (line location) records — and filters that data down to consigned inventory arrangements, where material is held at a buyer location but title remains with the supplier until consumption.

Because consigned shipments are settled through consumption rather than conventional receipt, operational reporting and supplier-facing integration require a purpose-built projection that joins procurement structures to item, vendor, and organization reference data and applies the consignment, approval, cancellation, and freeze predicates in one place. POS_CONSIGNED_SHIPMENTS_V supplies exactly that projection. It is typically consumed by iSupplier Portal pages, custom concurrent programs, OBIEE/BIP extracts, and ad hoc SQL used by sourcing and materials analysts.

Underlying Base Objects

The documented base objects are PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL, PO_RELEASES_ALL, PO_VENDORS, PO_VENDOR_SITES_ALL, MTL_SYSTEM_ITEMS_KFV, ORG_ORGANIZATION_DEFINITIONS, HR_LOCATIONS_ALL_TL, HR_ALL_ORGANIZATION_UNITS_TL, and HZ_LOCATIONS, together with the FND_GLOBAL, HR_GENERAL, and HR_SECURITY packages used for language resolution and organization security.

The core driving join is POH.PO_HEADER_ID = POL.PO_HEADER_ID and POL.PO_LINE_ID = PLL.PO_LINE_ID, with PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+) as an outer join so blanket and planned release context is optional. Vendor identity is resolved through POV.VENDOR_ID = POH.VENDOR_ID and PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID. Item master data is attached with POL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+) and the organization predicate NVL(MSI.ORGANIZATION_ID, PLL.SHIP_TO_ORGANIZATION_ID) = PLL.SHIP_TO_ORGANIZATION_ID. Ship-to location text is obtained through outer joins to HR_LOCATIONS_ALL_TL and HZ_LOCATIONS constrained by USERENV('LANG'), with organization names from HR_ALL_ORGANIZATION_UNITS_TL and codes from ORG_ORGANIZATION_DEFINITIONS.

Key Columns

• PO_NUMBER — header SEGMENT1, concatenated with RELEASE_NUM for releases via DECODE on PO_RELEASE_ID.
• PO_HEADER_ID / PO_LINE_ID / LINE_LOCATION_ID / PO_RELEASE_ID — primary keys linking back to the transactional tables.
• LINE_NUM / SHIPMENT_NUM — document line and schedule identifiers.
• ORG_ID / ORGANIZATION_CODE / ORG_NAME — operating unit and inventory organization context.
• VENDOR_ID / VENDOR_SITE_ID / VENDOR_NAME / VENDOR_SITE_CODE — supplier identification.
• ITEM_ID / ITEM_NUM — ITEM_NUM is the concatenated item segment (MSI.CONCATENATED_SEGMENTS), the column most often searched as "item_num."
• ITEM_REVISION / ITEM_DESCRIPTION / SUPPLIER_ITEM_NUM — revision, description, and the vendor product number (POL.VENDOR_PRODUCT_NUM).
• DUE_DATE — NVL(PLL.PROMISED_DATE, PLL.NEED_BY_DATE).
• QUANTITY_ORDERED / QUANTITY_RECEIVED — shipment quantity and quantity received to date.
• SHIP_TO_LOCATION / SHIP_TO_LOCATION_ID — derived from HR location code or an HZ address substring.
• VMI_FLAG / CONSIGNED_FLAG — flags carried from PO_LINE_LOCATIONS_ALL; CONSIGNED_FLAG is forced to 'Y'.

Common Use Cases and Queries

Typical scenarios include open consigned shipment reporting by supplier, exposure and reconciliation for VMI arrangements, iSupplier Portal displays of consigned schedules, and item-level drill-down reporting. The following query retrieves consigned shipments for a specific item.

SELECT po_number, line_num, shipment_num, item_num, item_description,
vendor_name, organization_code, due_date,
quantity_ordered, quantity_received
FROM apps.pos_consigned_shipments_v
WHERE item_num = :item_num
ORDER BY due_date;

Aggregate consumption exposure by supplier and organization:

SELECT vendor_name, organization_code, SUM(quantity_ordered - NVL(quantity_received,0)) open_qty
FROM apps.pos_consigned_shipments_v
GROUP BY vendor_name, organization_code;

Because organization security and language are resolved internally through HR_SECURITY, HR_GENERAL, and FND_GLOBAL, results are automatically scoped to the caller's accessible organizations and session language, making the view suitable for both portal runtime use and standalone reporting.