Search Results comp_serial_number




Overview
Explore in AI Chat

Overview

MTL_MFG_PRODUCTS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the INV (Inventory) product. It exposes manufacturing product and component transaction information by joining Work in Process (WIP) entities and discrete jobs to material transactions, lot records, and serial records. The view answers the operational question of which components were consumed against which assemblies, on which jobs, and in which organizations and subinventories.

The view has particular relevance to serialized manufacturing environments. It is one of the few INV/WIP constructs that exposes both the assembly-level serial number and the component-level serial number in a single result set, via the columns SERIAL_NUMBER and COMP_SERIAL_NUMBER respectively. This makes it a natural starting point for genealogy, traceability, and as-built reporting where a user needs to resolve a component serial to the parent assembly and job that consumed it.

Underlying Base Objects

The view is defined over a set of WIP and INV base objects. The documented referenced objects are WIP_ENTITIES, WIP_DISCRETE_JOBS, WIP_REQUIREMENT_OPERATIONS, MTL_MATERIAL_TRANSACTIONS, MTL_TRANSACTION_LOT_NUMBERS, MTL_UNIT_TRANSACTIONS, MTL_SYSTEM_ITEMS_KFV, MTL_PARAMETERS, HR_ORGANIZATION_UNITS, and GME_MATERIAL_DETAILS. Most are accessed through APPS synonyms, and the definition also references the HR_GENERAL and HR_SECURITY packages for organization-level security and validation.

Joins are driven from WIP_ENTITIES to WIP_DISCRETE_JOBS on WIP_ENTITY_ID, and from the job to WIP_REQUIREMENT_OPERATIONS to obtain the component item. MTL_SYSTEM_ITEMS_KFV supplies the concatenated item number and description for the assembly, with outer joins on PRIMARY_ITEM_ID and ORGANIZATION_ID. MTL_MATERIAL_TRANSACTIONS is restricted to TRANSACTION_SOURCE_TYPE_ID = 5, which corresponds to WIP issue and completion transactions, and is linked to the entity on TRANSACTION_SOURCE_ID. MTL_TRANSACTION_LOT_NUMBERS and MTL_UNIT_TRANSACTIONS are joined to resolve lot and serial detail for both the assembly side and the component side, the latter producing the COMP_LOT_NUMBER and COMP_SERIAL_NUMBER columns.

Key Columns

Common Use Cases and Queries

The primary use case is serial genealogy: given a component serial number, resolve the assembly, job, and organization into which it was issued. A representative query is:

  • SELECT wip_entity_name, organization_code, item_number, serial_number, comp_serial_number, transaction_date FROM mtl_mfg_products_v WHERE comp_serial_number = :p_serial;
  • SELECT wip_entity_name, item_number, comp_lot_number, primary_quantity FROM mtl_mfg_products_v WHERE lot_number = :p_lot;
  • SELECT wip_entity_name, COUNT(DISTINCT comp_serial_number) FROM mtl_mfg_products_v WHERE wip_entity_name = :p_job GROUP BY wip_entity_name;

Because the view is not partitioned or indexed directly, filter predicates should be applied on the serial, lot, job, or organization columns, and joins to MTL_SYSTEM_ITEMS_KFV or MTL_SERIAL_NUMBERS performed on the base tables where deeper attribute detail is required. The view does not expose transaction reversal flags or costing data; for reconciliation against MTL_MATERIAL_TRANSACTIONS the base table should be queried.