Results for “shared_item”

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

Overview

PMIBV_LOT_SHARED_SHIP_V is a VALID database view owned by the APPS schema within the PMI – Process Manufacturing Intelligence product family in Oracle E-Business Suite 12.1.1 and 12.2.2. The view establishes the relationship between a source lot and the shipments in which the destination lots derived from that source lot have participated. As stated in the ETRM documentation, a "Shared Lot" is defined as a lot that shares the same source lots as another lot — that is, lots that trace back to a common originating lot.

The reporting role of this view is fundamentally genealogical: it extends the parent view PMI_LOT_SHARED_V, which supplies the source-to-shared lot-to-lot mapping, by joining shipment transaction activity against the shared (destination) lots. The result is a flattened denormalized recordset that answers the question "which customer shipments consumed lots that share my source lot?" This makes the view valuable for lot traceability, recall impact analysis, quality disposition reporting, and downstream distribution reporting in process manufacturing environments where lot co-products, by-products, or split lots originate from the same source.

The view sits in a reporting layer rather than a transactional maintenance layer. It is read-only and intended for inquiry and extraction.

Underlying Base Objects

The ETRM 12.2.2 metadata lists the following referenced base objects: FND_PROFILE (PACKAGE), HR_ALL_ORGANIZATION_UNITS_VL (VIEW), IC_ITEM_MST (SYNONYM), IC_LOTS_MST (SYNONYM), IC_TRAN_PND (SYNONYM), OE_ORDER_HEADERS_ALL (SYNONYM), OE_ORDER_LINES_ALL (SYNONYM), OP_CUST_MST (SYNONYM), OP_ORDR_HDR (SYNONYM), PMI_HZ_PARTY_V (VIEW), and PMI_LOT_SHARED_V (VIEW).

• PMI_LOT_SHARED_V — the driving view, supplying START_ITEM_ID, START_LOT_ID, SHARED_ITEM_ID, and SHARED_LOT_ID columns that define the lot sharing relationship.
• IC_ITEM_MST — joined twice, once as START_ITEM and once as SHARED_ITEM, to supply item numbers, descriptions, units of measure, inventory class, planning class, and sales class.
• IC_LOTS_MST — joined twice, as START_LOT and SHARED_LOT, to supply lot and sublot numbers.
• IC_TRAN_PND — aliased SHARED_EVENT, filtered to DOC_TYPE = 'OPSO', providing transaction quantity, warehouse, location, transaction date, and completed indicator.
• OP_ORDR_HDR — aliased SHARED_ORDR, joined on ORDER_ID = DOC_ID, supplying organization code and order number.
• OP_CUST_MST — aliased CUST, joined on SHIPCUST_ID, supplying customer number and name.

Supporting objects such as OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL, PMI_HZ_PARTY_V, HR_ALL_ORGANIZATION_UNITS_VL, and FND_PROFILE support the parent view chain and organizational/customer resolution.

Key Columns

The view exposes paired source and shared attributes.

• START_ITEM_ID / START_LOT_ID and SHARED_ITEM_ID / SHARED_LOT_ID — the identifying keys linking source lot to shared (destination) lot.
• ITEM_NO, ITEM_DESC1, LOT_NO, SUBLOT_NO — appear for both the source and shared entities; note the duplicated column names in the SELECT list, so aliasing is required when querying directly.
• ITEM_UM, ITEM_UM2, INV_CLASS, PLANNING_CLASS, SALES_CLASS — attributes of the shared item.
• ORGN_CODE, WHSE_CODE, LOCATION — organizational and warehouse context of the shipment event.
• ORDER_NO, CUST_NO, CUST_NAME — the sales order and ship-to customer.
• TRANS_DATE, COMPLETED_IND — timing and completion state of the shipment transaction.
• ABS(SUM(TRANS_QTY)) and ABS(SUM(TRANS_QTY2)) — aggregated absolute transaction quantities, summed and grouped per lot/shipment combination.

Common Use Cases and Queries

Typical uses include recall scope determination, where a defective source lot must be traced to all shipments of lots sharing that source; co-product distribution analysis; and customer exposure reporting for quality holds.

SELECT start_item_id, start_lot_id,
       shared_item_id, shared_lot_id,
       order_no, cust_no, cust_name,
       whse_code, trans_date
FROM   apps.pmibv_lot_shared_ship_v
WHERE  start_lot_id = :p_lot_id
ORDER  BY trans_date;

Because the SELECT list contains duplicate column names (ITEM_NO, LOT_NO, SUBLOT_NO, ITEM_DESC1), direct wildcard selection is not advisable; explicit projection or a wrapper view with aliases is recommended. Always qualify the view with the APPS schema, and restrict by START_LOT_ID, SHARED_LOT_ID, or ORDER_NO to keep execution efficient against the large IC_TRAN_PND transaction pool.