Search Results out_of_spec_lot_status_code




Overview
Explore in AI Chat

Overview

GMD_ALL_SPEC_VRS_VL is an APPS-owned database view in Oracle EBS Process Manufacturing Product Development (GMD). It consolidates specification version records across all specification categories — inventory, customer, supplier, WIP, stability, and monitoring — into a single unified reporting interface. The "ALL" prefix reflects this multi-source design, while the "_VL" suffix denotes a "value list" view intended for LOV (List of Values) population and lookup validation in OAF and Forms-based screens. The view carries a SPEC_TYPE discriminator column (hard-coded as 'I' in the inventory branch of the UNION ALL that comprises its definition) so that each contributing source can be distinguished by consumers.

Its central role is to provide a normalized read model of specification versions, blending descriptive attributes from GMD_SPECIFICATIONS_B with transactional context (lot, subinventory, locator) and decoded reference values sourced from lookup and status tables. The user's search term "spec_vr_status_desc" corresponds to a decoded column in this view, populated by joining GMD_QC_STATUS_TL on the QC status identifier.

Underlying Base Objects

The definition documented in ETRM 12.2.2 references a UNION-composed set of specification version tables plus a series of joined reference and descriptive objects:

Cross-joining to these tables enriches raw version rows with human-readable values; GMD_QC_STATUS_TL in particular populates SPEC_VR_STATUS_DESC and links directly to the user's search term.

Key Columns

  • SPEC_VR_ID / SPEC_ID — primary identifiers for the version and its parent specification.
  • SPEC_TYPE — discriminator indicating the source category of the record.
  • ORGANIZATION_ID / ORGANIZATION_CODE — organization context of the specification version.
  • SPEC_NAME / SPEC_VERS / REVISION / GRADE_CODE — identification and grading attributes of the specification.
  • INVENTORY_ITEM_ID / ITEM_NUMBER / DESCRIPTION — the item to which the specification applies.
  • SPEC_STATUS / SPEC_STATUS_DESC — current status of the specification header, with decoded description from GMD_QC_STATUS_TL.
  • SPEC_VR_STATUS / SPEC_VR_STATUS_DESC — status of the specific version, decoded via GMD_QC_STATUS_TL; this is the target of the searched term.
  • PARENT_LOT_NUMBER / LOT_NUMBER / SUBINVENTORY / LOCATOR — lot-level and locational context, with locator decoded through MTL_ITEM_LOCATIONS_KFV.
  • START_DATE / END_DATE — effective dating of the version.
  • OUT_OF_SPEC_LOT_STATUS_CODE / IN_SPEC_LOT_STATUS_CODE — decoded material status codes from MTL_MATERIAL_STATUSES.
  • DELETE_MARK — soft-delete flag; active rows typically require DELETE_MARK = 0.

Common Use Cases and Queries

The view is typically used to drive specification version LOVs, status report extracts, and analytical dashboards. A representative query retrieving active versions with decoded statuses is:

  • SELECT spec_vr_id, spec_name, spec_vers, revision, item_number, spec_vr_status_desc, spec_status_desc, lot_number, organization_code FROM apps.gmd_all_spec_vrs_vl WHERE delete_mark = 0 AND spec_vr_status = :p_status;
  • Filtering by organization_id and inventory_item_id to retrieve the applicable specification versions for a given item in a given org.
  • Joining to transaction or lot tables on spec_vr_id for QA reporting on which specification applied to a particular lot or receipt.

Because it unifies all specification types, the view is well suited to cross-category reporting; however, consumers should be aware of the NULL placeholders (batch, recipe, formula, routing, order, and PO columns) present in the inventory branch, which are populated only by other branches of the underlying UNION.