Search Results pmits_inv_line_summ_v




Overview
Explore in AI Chat

Overview

The PMITS_INV_LINE_SUMM_V view belongs to the Process Manufacturing Intelligence (PMI) product family within Oracle E-Business Suite and resides in the APPS schema. Its documented purpose is to summarize inventory transactions at the line level, aggregating detail rows drawn from the inventory transaction and lot master sources into a single, rolled-up record per document line. The view is exposed to reporting and analytics consumers — particularly PMI dashboards, Oracle Discoverer workbooks, and BI Publisher reports — where transaction volumes must be condensed without losing lot and sublot lineage.

The user search term "sublot_number" maps directly to the view's SUBLOT_NUMBER column, which is derived from IC_LOTS_MST.SUBLOT_NO. This column is central to process manufacturing because a single lot may be subdivided into one or more sublots, and consolidated reporting must indicate when transactions span multiple sublots. The view uses a DECODE/COUNT DISTINCT pattern to detect this: if exactly one distinct sublot value exists per line, that value is returned; otherwise the literal asterisk (*) is returned, signaling that the line aggregates across multiple sublots. The same consolidation logic applies to lot number, lot status, quality control grade, reason code, company code, organization code, warehouse, location, and transaction date.

Underlying Base Objects

ETRM metadata documents the following referenced base objects:

  • IC_TRAN_VW1 (VIEW) — the driving transaction source, aliased TRAN, supplying document, line, item, quantity, UOM, lot, sublot, status, and reason attributes.
  • IC_LOTS_MST (SYNONYM) — the lot master, aliased LOT, joined on LOT_ID and ITEM_ID, supplying LOT_NO and SUBLOT_NO.
  • IC_ITEM_MST (SYNONYM) — the item master, aliased ITM, joined on ITEM_ID.
  • FND_DATE (PACKAGE) — used via FND_DATE.DATE_TO_DISPLAYDT to render the transaction date in display format.
  • PMI_SECURITY_PKG (PACKAGE) — invoked as PMI_SECURITY_PKG.SHOW_RECORD(TRAN.ORGN_CODE) to enforce organization-level row security.

All joins are inner joins, so only transactions with valid item and lot master entries are returned.

Key Columns

Common Use Cases and Queries

The view is typically used to report aggregated material movement by document line, filter lots lacking a sublot assignment, or reconcile transfer pairs.

-- Transactions grouped by lot and sublot
SELECT lot_number, sublot_number,
       SUM(transaction_qty) total_qty
FROM   apps.pmits_inv_line_summ_v
WHERE  sublot_number IS NOT NULL
  AND  sublot_number <> '*'
GROUP BY lot_number, sublot_number;
-- Lines consolidating multiple sublots (exception report)
SELECT doc_id, line_id, item_id, lot_number
FROM   apps.pmits_inv_line_summ_v
WHERE  sublot_number = '*';
-- Line detail with transfer-pair normalization
SELECT join_doc_id, doc_type, line_id,
       organization_code, transaction_date, transaction_qty
FROM   apps.pmits_inv_line_summ_v
WHERE  doc_id = :p_doc_id
ORDER BY line_id;

Because the view enforces PMI_SECURITY_PKG.SHOW_RECORD, only organizations visible to the connected user's security profile are returned; report developers must therefore grant the view (not the base synonyms alone) and ensure the responsibility's organization access is configured correctly.