Search Results pmifv_lot_source_purch_v




Overview
Explore in AI Chat

Overview

PMIFV_LOT_SOURCE_PURCH_V is a database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the PMI (Process Manufacturing Intelligence) product family. The view is documented as "PMI Lot Genealogy Raw Materials" and is designed to expose the purchased (external) source of raw material lots that feed into a manufactured or process lot. In lot genealogy terms, it answers the question "where did the raw materials in this produced lot come from, and which purchase receipt supplied them?" Rather than requiring report authors to navigate the full genealogy, receipt, and purchasing schemas independently, the view pre-joins purchased raw material consumption to receipt headers, purchase orders, suppliers, warehouses, items, and lots, returning one consolidated row per product lot / ingredient lot / transaction grouping.

The object carries a VALID status in ETRM for both 12.1.1 and 12.2.2, and its definition is stable across those releases. Its role is primarily analytical and reporting-oriented: it underpins PMI lot genealogy inquiries, raw material traceability reporting, and downstream recall or quality investigations. Because it is a view over operational tables rather than a table itself, no data is stored; it is materialized at query time and reflects current transactional state.

Underlying Base Objects

The view is defined over the PMI lot genealogy table and a set of inventory, receiving, and purchasing objects. The central driver is PMI_LOT_GENEALOGY, which links a product item/lot to its ingredient item/lot. Transactional quantity and location data are joined from IC_TRAN_PND (pending inventory transactions) filtered to DOC_TYPE = 'PORC', with DOC_ID mapping to the receipt shipment header and LINE_ID mapping to the receipt transaction.

Supporting master data is drawn from IC_ITEM_MST and IC_LOTS_MST (both aliased twice to resolve product and ingredient descriptions, UOMs, and lot/sublot numbers), and IC_WHSE_MST for the warehouse organization code. Purchasing and receipt detail is sourced from RCV_SHIPMENT_HEADERS, RCV_SHIPMENT_LINES, and RCV_TRANSACTIONS, with purchase order information from PO_HEADERS_ALL and vendor information from PO_VENDOR_SITES_ALL and the PO_VENDORS_VIEW view. The documented reference list also includes synonyms such as IC_TRAN_CMP, PO_ORDR_HDR, PO_RECV_DTL, PO_RECV_HDR, and PO_VEND_MST, along with the PMI_LOT_SOURCE_LEAF_V view and the FND_GLOBAL package, reflecting the underlying 11i-to-R12 compatibility synonyms that resolve to the R12 base tables. Vendor and PO joins are largely outer joins, so consumption rows without a fully resolved receipt or PO still surface.

Key Columns

  • GEN.PRODUCT_ITEM_ID / PRODUCT_LOT_ID — the manufactured item and lot whose genealogy is being reported.
  • GEN.INGRED_ITEM_ID / INGRED_LOT_ID — the raw material item and lot consumed.
  • TRANS_QTY / TRANS_QTY2 — summed transaction quantities in primary and secondary units of measure.
  • WHSE_CODE / LOCATION / ORGN_CODE — warehouse and location identifiers for the transaction and receipt.
  • TRANS_DATE — transaction date used for time-based traceability.
  • RECEIPT_NUM — receipt number from RCV_SHIPMENT_HEADERS.
  • PO.SEGMENT1 — the purchase order number.
  • VENDOR_NUMBER / VENDOR_NAME — supplier identifiers.
  • I_ITEM.* / I_LOT.* — ingredient item number, description, UOMs, inventory/planning/sales class, and lot/sublot.
  • P_ITEM.* / P_LOT.* — product item number, description, UOMs, and lot/sublot.

Common Use Cases and Queries

Typical uses include raw material traceability for a finished lot, supplier attribution of consumed ingredients, and receipt-level reconciliation of purchased quantities. A representative query filters by product lot:

  • SELECT product_lot_id, ingred_item_id, ingred_lot_id, receipt_num, segment1, vendor_name, trans_qty FROM apps.pmifv_lot_source_purch_v WHERE product_lot_id = :lot_id;
  • Supplier concentration analysis: SELECT vendor_name, SUM(trans_qty) FROM apps.pmifv_lot_source_purch_v GROUP BY vendor_name;
  • Receipt-based traceability: SELECT receipt_num, po_segment1, item_no, lot_no, trans_date FROM apps.pmifv_lot_source_purch_v WHERE receipt_num = :receipt;

Because rows are aggregated by genealogy, warehouse, location, date, and receipt attributes, quantities are consolidated per grouping; report logic should account for this when summing totals.