Search Results in_spec_lot_status_code




Overview
Explore in AI Chat

Overview

APPS.GMD_ALL_SPEC_VRS_VL is a consolidated Oracle EBS view that presents specification versions across all quality specification types within Oracle Process Manufacturing (OPM) / Enterprise Technical Reference Management (ETRM). It is a "VL" (validation/view layer) object that unions together several specification-version base entities — inventory, customer, supplier, stability, monitoring, and WIP specifications — into a single reporting surface. Because specification versions in OPM are stored in type-specific tables, this view provides a unified interface for querying specification version data regardless of the originating specification category.

The view is primarily used for reporting, integration, and inventory-lot quality evaluation. One of its notable columns, OUT_OF_SPEC_LOT_STATUS_CODE, is central to the workflows that determine what lot or material status a batch should assume when material fails a specification test, making this view relevant to quality disposition and inventory status management.

Underlying Base Objects

The view is defined as a union over the following OPM specification-version synonyms: GMD_INVENTORY_SPEC_VRS, GMD_CUSTOMER_SPEC_VRS, GMD_SUPPLIER_SPEC_VRS, GMD_STABILITY_SPEC_VRS, GMD_MONITORING_SPEC_VRS, and GMD_WIP_SPEC_VRS. Each branch joins to common supporting objects, including GMD_SPECIFICATIONS_B (the specification header/master), GMD_QC_STATUS_TL (the translated quality status descriptions), MTL_SYSTEM_ITEMS_KFV and MTL_ITEM_LOCATIONS_KFV (item and locator concatenated key flexfield views), MTL_MATERIAL_STATUSES, and MTL_PARAMETERS.

The type-specific version rows are joined on SPEC_VR_ID and consolidated so that the same column set is returned for every specification type. The literal SPEC_TYPE is hard-coded per branch (for example, 'I' for inventory) to identify the source specification family. Out-of-spec and in-spec lot status codes are sourced through joins to the quality status translation table, with ms1.status_code populating out_of_spec_lot_status_code and ms2.status_code populating in_spec_lot_status_code.

Key Columns

Common Use Cases and Queries

A frequent requirement is to retrieve the out-of-spec lot status configured for a given item or organization, driven by the search term out_of_spec_lot_status_code:

  • Identify which material status is applied to failing lots:

SELECT spec_vr_id, spec_name, organization_code, item_number, lot_number, out_of_spec_lot_status_code, in_spec_lot_status_code FROM apps.gmd_all_spec_vrs_vl WHERE inventory_item_id = :item_id AND organization_id = :org_id AND delete_mark = 0;

  • Produce a listing of all specifications for an organization that define an out-of-spec disposition:

SELECT spec_type, spec_name, spec_vers, lot_number, out_of_spec_lot_status_code FROM apps.gmd_all_spec_vrs_vl WHERE organization_id = :org_id AND out_of_spec_lot_status_code IS NOT NULL AND delete_mark = 0 ORDER BY spec_name, spec_vers;

  • Join the view to MTL_MATERIAL_STATUSES or GMD_QC_STATUS_TL to resolve status descriptions, or to lot/inventory transaction tables for disposition reporting.
  • Use SPEC_TYPE to filter a single specification family, and START_DATE/END_DATE to isolate currently effective versions.

The view filters on DELETE_MARK = 0 in most production queries to exclude logically deleted records. Because it unions multiple specification types, always constrain by ORGANIZATION_ID, INVENTORY_ITEM_ID, or SPEC_TYPE to control result volume and avoid cross-type duplication.