Search Results end_onh_qty




Overview
Explore in AI Chat

Overview

OPI.OPI_PMI_IDS_TEMP is a transient interface table owned by the OPI (Oracle Process Manufacturing Intelligence / Process Manufacturing Inventory) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It resides in the APPS_TS_INTERFACE tablespace, a storage location reserved for staging and interface data rather than persistent transactional records. The table is documented as ETRM VALID and is designated Oracle Internal Use Only, meaning Oracle Corporation does not support direct customer access except through standard Oracle Applications programs. Its columns and naming pattern indicate that it serves as a landing and aggregation area for material movement and on-hand quantity metrics, populated during inventory reconciliation, period-close, or reporting extraction routines that summarize consumption, receipts, transfers, and adjustments by organization, warehouse, and item.

The heuristic Data Vault classification mined from its foreign key structure is standalone, meaning it is not modeled as a hub, link, or satellite but as an independent staging structure. Operationally, it behaves closer to a satellite-style snapshot of quantity measures over a transaction date, keyed by the composite business key described below, yet it carries no enforced relational links to parent hubs in this schema. The table is referenced by the APPS synonym OPI_PMI_IDS_TEMP, which is the typical access path for application code and concurrent programs.

Key Information Stored

The primary key is the unique index OPI_PMI_IDS_TEMP_PK, defined on the composite of CO_CODE, ORGN_CODE, WHSE_CODE, LOCATION, ITEM_ID, LOT_ID, and TRX_DATE. This composite is the business-key candidate: it identifies a single quantity snapshot for a given company, organization, warehouse, location, item, lot, and transaction date. No separate surrogate key column is documented; the composite business key functions as the table key.

  • CO_CODE, ORGN_CODE, WHSE_CODE, LOCATION — Company, organization, warehouse, and location identifiers that scope each inventory record.
  • ITEM_ID, LOT_ID, TRX_DATE — Item and lot identifiers with the transaction date that completes the composite key.
  • BEG_ONH_QTY and END_ONH_QTY — Beginning and ending on-hand quantities for the aggregation window. END_ONH_QTY is the column users most frequently target when searching this object.
  • AVG_ONH_QTY — Average on-hand quantity across the period.
  • TOTAL_REC_QTY, PO_DEL_QTY — Total receipt quantity and purchase order delivery quantity.
  • TOT_ISSUES_QTY, TOT_CUST_SHIP_QTY — Total issue quantity and total customer shipment quantity.
  • TO_ORG_QTY, FROM_ORG_QTY — Inter-organization transfer quantities outbound and inbound.
  • WIP_ISSUE_QTY, WIP_COMP_QTY — Work in process issue and completion quantities.
  • INV_ADJ_QTY — Inventory adjustment quantity.
  • PERIOD_FLAG, OPERATION_CODE — Period indicator and the operation or program code that populated the row.
  • CREATION_DATE, LAST_UPDATE_DATE — Audit stamps for row creation and last modification.

Common Use Cases and Queries

Because the table is an interface structure, the dominant use case is reconciliation and reporting of on-hand movement by item, lot, and organization. A typical query isolates ending balances for a location or lot:

  • SELECT ORGN_CODE, ITEM_ID, LOT_ID, TRX_DATE, BEG_ONH_QTY, END_ONH_QTY FROM OPI.OPI_PMI_IDS_TEMP WHERE TRX_DATE BETWEEN :start_date AND :end_date ORDER BY ORGN_CODE, ITEM_ID, TRX_DATE;
  • Filtering by PERIOD_FLAG to validate period-close snapshots before promoting data to permanent tables.
  • Aggregating TOTAL_REC_QTY, TOT_ISSUES_QTY, TO_ORG_QTY, and FROM_ORG_QTY to reconcile material balance equations where BEG_ONH_QTY plus receipts less issues and transfers should equal END_ONH_QTY.
  • Comparing WIP_ISSUE_QTY and WIP_COMP_QTY against inventory movement for manufacturing variance analysis.
  • Extracting OPERATION_CODE to trace which concurrent program or interface process created a given snapshot.

Related Objects

The relationship metadata states that OPI.OPI_PMI_IDS_TEMP does not reference any database object directly, but it is referenced by the APPS synonym OPI_PMI_IDS_TEMP, which exposes it to application code and concurrent processing. Because no foreign keys are documented, joins to master data are performed logically on the composite key rather than through enforced constraints: CO_CODE and ORGN_CODE join to organization and company definitions, ITEM_ID to inventory item master (for example MTL_SYSTEM_ITEMS_B), LOT_ID to lot master (MTL_LOT_NUMBERS), and WHSE_CODE and LOCATION to warehouse and locator definitions (MTL_PARAMETERS and MTL_ITEM_LOCATIONS). WIP_* columns relate conceptually to WIP job and transaction tables, and PO_DEL_QTY to purchasing receipt tables. Before building production reports against this table, verify that rows are valid and current, since its interface tablespace location and internal-use designation signal that content is transient and may be purged or refreshed by Oracle's standard inventory aggregation programs.