Search Results gmd_inventory_spec_vrs




Overview
Explore in AI Chat

Overview

GMD_INVENTORY_SPEC_VRS is an Oracle Process Manufacturing (OPM) Product Development table that stores Inventory Specification Validity Rules. It belongs to the GMD schema and supports the OPM Quality module by defining the conditions under which a quality specification applies to a given inventory context — organization, warehouse, location, lot, and sublot. The object carries 72 documented columns in ETRM 12.2.2 and is classified as VALID across Oracle EBS 12.1.1 and 12.2.2 environments.

From a Data Vault modeling perspective, the mined foreign key structure suggests a standalone classification, meaning the table is best treated as a satellite-style structure with limited outward dependencies. Its single documented foreign key to QA_SAMPLING_PLANS implies that specification validity rules are not deeply linked to other transactional hubs in the vault model, and the table may function as an independent reference container for rule configuration. This is a heuristic suggestion rather than a canonical design.

Key Information Stored

The table's surrogate primary key is SPEC_VR_ID, enforced by the GMD_INVENTORY_SPEC_VRS_PK constraint. This column uniquely identifies each validity rule record and is the sole documented unique index (business-key candidate). The most significant columns include:

Common Use Cases and Queries

Typical reporting scenarios include determining which specification is active for a given organization and lot on a specific date, and tracing which sampling plan governs a rule. A representative query joining to the sampling plan table:

  • SELECT v.SPEC_VR_ID, v.SPEC_ID, v.ORGN_CODE, v.LOT_NO, v.START_DATE, v.END_DATE FROM GMD.GMD_INVENTORY_SPEC_VRS v WHERE v.DELETE_MARK = 0 AND SYSDATE BETWEEN v.START_DATE AND NVL(v.END_DATE, SYSDATE);
  • SELECT v.SPEC_VR_ID, v.SPEC_ID, s.PLAN_ID FROM GMD.GMD_INVENTORY_SPEC_VRS v, QA_SAMPLING_PLANS s WHERE v.SAMPLING_PLAN_ID = s.PLAN_ID;

Additional uses include auditing inactive or expired rules via END_DATE, retrieving flexfield attribute values for custom integrations, and reconciling in-spec versus out-of-spec lot status assignments for quality disposition processes.

Related Objects

The following objects are most significant in relation to GMD_INVENTORY_SPEC_VRS:

  • QA_SAMPLING_PLANS — referenced via SAMPLING_PLAN_ID; defines sampling behavior for the rule.
  • GMD_SPECIFICATIONS / GMD_SPEC_VRS — parent specification records referenced by SPEC_ID.
  • GMD_INVENTORY_SPEC_VRS_PK — the primary key constraint enforcing SPEC_VR_ID uniqueness.
  • Oracle Inventory lot and locator tables (MTL_LOT_NUMBERS, MTL_ITEM_LOCATIONS) — supply lot and locator context referenced through LOT_ID and LOCATOR_ID.
  • Oracle HR organizations (HR_OPERATING_UNITS / ORG_ORGANIZATION_DEFINITIONS) — provide organization context for ORGANIZATION_ID and ORGN_CODE.