Search Results process_qty_uom

Overview

The EDW_OPI_OPRN_OPRN_LCV view is a reporting layer object owned by the APPS schema within the Oracle E-Business Suite Operations Intelligence (OPI) product family. It presents a consolidated, reporting-ready list of manufacturing operations used in routings across both Discrete Manufacturing and Process Manufacturing. The view normalizes operation records from these two distinct manufacturing models into a single structure suitable for the Oracle Enterprise Data Warehouse (EDW) and OPI analytics.

The suffix "LCV" denotes a Loader Column View, indicating that this object is designed to feed an ETL process that populates downstream dimensional or staging tables. By exposing a uniform key (OPRN_PK) and a common set of descriptive attributes, the view allows operations intelligence reporting to treat process-manufacturing and discrete-manufacturing operations as a unified dimension. Notably, in Oracle EBS 12.1.1 and 12.2.2, the object remains a read-only query view; it stores no data itself and reflects the current state of the underlying transactional tables at query time.

Underlying Base Objects

The view definition is constructed as a UNION of three SELECT statements, each drawing on a different set of base tables. Documented external/ETRM metadata lists no referenced base objects, but the view text itself identifies them explicitly.

• FM_OPRN_MST — the Process Manufacturing operation master, joined to EDW_LOCAL_INSTANCE, supplies process-routing operations. Its primary key column is OPRN_ID.
• BOM_OPERATION_SEQUENCES — joined to BOM_OPERATIONAL_ROUTINGS, BOM_STANDARD_OPERATIONS (outer-joined), MTL_PARAMETERS, and EDW_LOCAL_INSTANCE, supplies discrete-manufacturing routing operations.
• EDW_LOCAL_INSTANCE — a warehouse-local instance reference used to stamp INSTANCE_CODE into keys and class identifiers.
• DUAL — used to synthesize a placeholder "NON-STANDARD" row so reports can classify non-standard operations.

The first branch emits keys suffixed "-OPM", the second "-OPI", and the third is a literal constant. This convention encodes the source system directly into the primary key, which is essential for lineage and de-duplication in the warehouse.

Key Columns

The view exposes a fixed column list, several of which are directly relevant to the user's term of interest, process_qty_uom.

• OPRN_PK — composite surrogate primary key built from the source identifier, instance code, and source tag (OPM/OPI).
• OPRC_FK — foreign key to the operation class (OPRN_CLASS for process, 'NA_EDW' otherwise).
• OPRN_DP — discriminator column; 'OPRN' for standard operations and 'NON-STANDARD' for the placeholder row.
• NAME / OPRN_NAME — operation code or name as used in routing.
• DESCRIPTION — operation description; for discrete rows it falls back to the standard operation description when a standard operation is referenced.
• ORGN_CODE — organization code from MTL_PARAMETERS (discrete branch only; NULL for process rows).
• DEPARTMENT — department identifier (discrete branch only).
• PROCESS_QTY_UOM — the unit of measure for process quantity. It is populated from FM_OPRN_MST.PROCESS_QTY_UM in the process branch and explicitly set to NULL for the discrete and non-standard branches, reflecting that discrete routing operations do not carry a process quantity UOM.
• USER_ATTRIBUTE1–5 — descriptive flexfield placeholders, currently NULL across all branches.
• LAST_UPDATE_DATE / CREATION_DATE — audit stamps sourced from the respective base tables (SYSDATE for the DUAL branch).

Common Use Cases and Queries

This view is typically queried by OPI/EDW ETL programs and by analysts building operation-level reporting across mixed manufacturing environments. A representative query lists process operations and their process quantity UOM:

• SELECT oprn_pk, name, process_qty_uom FROM apps.edw_opi_oprn_oprn_lcv WHERE process_qty_uom IS NOT NULL; — isolates process-manufacturing operations that define a quantity UOM.
• SELECT oprn_dp, COUNT(*) FROM apps.edw_opi_oprn_oprn_lcv GROUP BY oprn_dp; — counts standard versus non-standard operations.
• SELECT oprn_pk, description, orgn_code FROM apps.edw_opi_oprn_oprn_lcv WHERE orgn_code IS NOT NULL; — retrieves discrete operations by organization.

Because the view performs no aggregation, filtering on PROCESS_QTY_UOM or ORGN_CODE at the source is the most efficient way to narrow results before warehouse loading.