Results for “i_item”

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

Overview

PMIBV_LOT_DEST_SHIP_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite. It is delivered as part of the Process Manufacturing Intelligence (PMI) product family and carries a VALID status in both EBS 12.1.1 and 12.2.2. The view materialises the relationship between a source lot and the shipments in which its destination (derived) lots have participated. In process manufacturing, a lot is frequently consumed into, or transformed into, one or more downstream lots. This view traces that genealogy forward to the outbound shipment transactions, allowing analysts and reporting tools to connect upstream ingredient or production lots with the customers and orders that ultimately received them.

The view is intended primarily for analytical reporting, lot tracing, and quality/compliance investigations rather than for transactional processing. Its read-only nature means it should never be used as a target for DML.

Underlying Base Objects

The view is defined over a mix of PMI, Inventory, Order Management, and party objects. Documented referenced base objects include:

• PMI_LOT_GENEALOGY — the driver table that records the parent/destination lot relationships.
• IC_TRAN_PND — the inventory transaction table supplying shipment movement quantities, dates, and warehouse/location.
• IC_ITEM_MST and IC_LOTS_MST — item and lot master data, joined for both the ingredient (I_) and product (P_) sides of the relationship.
• OP_ORDR_HDR, OE_ORDER_HEADERS_ALL, and OE_ORDER_LINES_ALL — order header/line detail providing organization, order number, and shipment linkage.
• OP_CUST_MST and PMI_HZ_PARTY_V — customer number, name, and party information.
• HR_ALL_ORGANIZATION_UNITS_VL and FND_PROFILE — organizational context and profile option resolution.

The join path anchors on PMI_LOT_GENEALOGY, matching product item/lot to inventory transactions of document type 'OPSO' (shipment order), then links to the order and customer masters.

Key Columns

• PRODUCT_ITEM_ID / PRODUCT_LOT_ID — the destination product item and lot produced or derived.
• INGRED_ITEM_ID / INGRED_LOT_ID — the originating (ingredient/source) item and lot traced forward.
• WHSE_CODE, LOCATION, TRANS_DATE — warehouse, storage location, and transaction date of the shipment movement.
• TRANS_QTY / TRANS_QTY2 — absolute summed transaction quantities in primary and secondary units (returned via ABS(SUM(...))).
• ORGN_CODE, ORDER_NO — shipping organization and order number.
• CUST_NO, CUST_NAME — the ship-to customer.
• Item and lot descriptive attributes (ITEM_NO, ITEM_DESC1, ITEM_UM, INV_CLASS, PLANNING_CLASS, SALES_CLASS, LOT_NO, SUBLOT_NO) for both I_ and P_ prefixes.
• COMPLETED_IND — indicates transaction completion status.

Common Use Cases and Queries

Typical usage includes forward lot tracing for recalls, customer shipment history for a specific source lot, and reconciliation of produced quantity against shipped quantity.

Trace all shipments derived from a given source lot:

SELECT product_lot_id, order_no, cust_name, trans_date, trans_qty
FROM   apps.pmibv_lot_dest_ship_v
WHERE  ingred_item_id = :p_ingred_item_id
AND    ingred_lot_id  = :p_ingred_lot_id
ORDER BY trans_date;

Shipments of a product lot to a specific customer:

SELECT order_no, org_code, trans_date, SUM(trans_qty) shipped_qty
FROM   apps.pmibv_lot_dest_ship_v
WHERE  product_item_id = :p_item_id
AND    cust_no = :p_cust_no
GROUP BY order_no, org_code, trans_date;

Because the view aggregates and unions shipment movements, results should be filtered aggressively by item and lot to maintain performance.