Search Results pmifv_lot_list_v




Overview
Explore in AI Chat

Overview

PMIFV_LOT_LIST_V is an Oracle E-Business Suite database view owned by the APPS schema and categorized under the PMI (Process Manufacturing Intelligence) product family. Its documented purpose is to serve as the "Lot List View used by Lot Genealogy workbook," meaning it functions as a consolidated data source for lot-level reporting and drill-down within Oracle Process Manufacturing Intelligence analytics. The view is validated and available in both EBS 12.1.1 and 12.2.2 environments, and it is a pure read-only construct — an interface for reporting rather than a transactional object. Because it materializes a flattened, denormalized projection of lot, item, organization, and receipt attributes, it is well suited to embedded BI Publisher data templates, OBIEE physical layers, and ad-hoc SQL used by process manufacturing analysts investigating lot provenance and origin.

Underlying Base Objects

The view is defined as a UNION ALL of two asymmetrical branches, each anchored on the IC_TRAN_PND pending transaction table filtered by DOC_TYPE. The first branch handles purchase order receipts (DOC_TYPE = 'PORC') and joins to IC_ITEM_MST, IC_LOTS_MST, RCV_SHIPMENT_HEADERS, RCV_SHIPMENT_LINES, RCV_TRANSACTIONS, PO_VENDOR_SITES_ALL, PO_VENDORS_VIEW, and SY_ORGN_MST. The second branch covers receiving transactions (DOC_TYPE = 'RECV') and joins the same item, lot, and organization masters against PO_RECV_HDR, PO_RECV_DTL, and PO_VEND_MST. FND_GLOBAL is referenced, and GME_BATCH_HEADER appears in the broader dependency list, reflecting the process manufacturing (GME) context. Outer joins (denoted by the (+) operator) preserve lot records even when receipt, vendor, or organization detail is absent, ensuring the view acts as a comprehensive lot inventory regardless of source document completeness. The HAVING SUM(TRANS_QTY) > 0 clause in the first branch excludes lots with zero or negative net pending quantity.

Key Columns

  • ITEM_NO, ITEM_DESC1, ITEM_ID — item identifier, description, and internal surrogate key from IC_ITEM_MST.
  • INV_CLASS, INV_TYPE — inventory classification and type attributes carried from the item master.
  • LOT_ID, LOT_NO, SUBLOT_NO — the internal lot key and human-readable lot/sublot numbers from IC_LOTS_MST.
  • LOT_CREATED (TRUNC) — the date the lot was created, truncated to day granularity.
  • QC_GRADE — quality grade assigned to the lot.
  • ORGN_CODE, ORGN_NAME — organization short code and descriptive name from SY_ORGN_MST.
  • VENDOR_NUMBER, VENDOR_NAME, VENDOR_ID — supplying vendor identity, resolved through PO_VENDORS_VIEW or PO_VEND_MST depending on branch.
  • RECEIPT_NUM — receiving document number (shipment header or PO receipt header).
  • Four placeholder literals and two zeros — positional placeholders retained for column-count alignment across the UNION ALL, typically consumed by the workbook template.

Common Use Cases and Queries

Typical use cases include lot genealogy tracing (identifying which vendor supplied a given lot), inventory aging analysis by LOT_CREATED, QC grade distribution reporting, and reconciliation of received lots against organizations and receipt documents. A representative query returns all lots for a specific item and organization:

  • SELECT LOT_NO, SUBLOT_NO, ITEM_NO, ORGN_CODE, VENDOR_NAME, RECEIPT_NUM, QC_GRADE FROM APPS.PMIFV_LOT_LIST_V WHERE ITEM_NO = :item AND ORGN_CODE = :orgn ORDER BY LOT_CREATED DESC;
  • SELECT VENDOR_NAME, COUNT(DISTINCT LOT_NO) FROM APPS.PMIFV_LOT_LIST_V WHERE LOT_CREATED >= SYSDATE - 90 GROUP BY VENDOR_NAME;
  • SELECT ORGN_CODE, QC_GRADE, COUNT(*) FROM APPS.PMIFV_LOT_LIST_V GROUP BY ORGN_CODE, QC_GRADE;

Because the view performs the receipt and vendor resolution at query time, report authors should apply item, organization, or date filters to constrain the underlying IC_TRAN_PND scan, which can otherwise be large in high-volume process manufacturing installations.