Search Results step_no




Overview
Explore in AI Chat

Overview

GMD_COM_SPEC_VRS_VL is an APPS-owned database view in Oracle E-Business Suite that consolidates the base columns of all Specification Validity Rules tables used by GMD - Process Manufacturing Product Development. Specification validity rules determine the conditions under which a quality specification applies to a material, lot, organization, or other qualifying attribute, including date ranges, sampling plans, and certificate of analysis (COA) behavior. Because the individual source tables are structured around different specification "types" (inventory, WIP, customer, supplier, stability, and monitoring), this view presents them through a single, uniform column projection so that reporting, integration, and inquiry screens can query specification validity rules without hard-coding each underlying table.

The view text demonstrates this consolidation explicitly: the first branch selects from GMD_INVENTORY_SPEC_VRS with a literal SPEC_TYPE of 'I' and real values for the inventory-specific columns (PARENT_LOT_NUMBER, LOT_NUMBER, SUBINVENTORY, LOCATOR_ID), while subsequent UNION ALL branches select from other specification validity tables with those same columns typed as NULL. This pattern produces a superset row shape across all validity rule types.

Underlying Base Objects

The documented base objects referenced by APPS.GMD_COM_SPEC_VRS_VL are the following synonyms, each resolving to its corresponding specification validity rules table in the GMD schema:

The view is therefore a UNION-based aggregation layered over six base tables. The literal SPEC_TYPE column is the discriminator that identifies which source table a given row originated from. Columns not applicable to a particular source table are projected as strongly typed NULLs, for example TO_NUMBER(NULL) for numeric attributes and NULL for character attributes, to satisfy the datatype compatibility required by the UNION ALL.

Key Columns

The following columns are central to interpreting and querying the view:

Common Use Cases and Queries

The view is typically used to report on specification applicability across specification types, and to trace which validity rule governs a given inventory lot. A common query identifies parent lot scoping, which aligns to the "parent_lot_number" search term:

  • Listing all validity rules for an organization: SELECT * FROM APPS.GMD_COM_SPEC_VRS_VL WHERE ORGANIZATION_ID = :org_id AND DELETE_MARK = 0.
  • Filtering to inventory-scoped rules with a parent lot: SELECT SPEC_VR_ID, SPEC_ID, PARENT_LOT_NUMBER, LOT_NUMBER FROM APPS.GMD_COM_SPEC_VRS_VL WHERE SPEC_TYPE = 'I' AND PARENT_LOT_NUMBER IS NOT NULL.

Because PARENT_LOT_NUMBER and LOT_NUMBER are populated only in the inventory branch and NULL elsewhere, queries referencing them should generally also constrain SPEC_TYPE. The view supports ETL extraction and validation tooling that must navigate specification validity rules uniformly across all GMD specification domains.