Search Results gmd_stability_spec_vrs




Overview
Explore in AI Chat

Overview

The table GMD.GMD_STABILITY_SPEC_VRS belongs to the Oracle EBS Process Manufacturing Product Development module (GMD) and stores Stability Study Validity Rules. In Oracle Process Manufacturing, stability studies track how a product's quality attributes behave over time under defined storage or environmental conditions. Each study relies on a specification that defines the acceptable limits an item must meet. The GMD_STABILITY_SPEC_VRS table captures the validity rules that determine when a given specification version is considered active and enforceable for a stability study, binding that specification to a sampling plan and to a bounded date range.

From a data modeling perspective, the ETRM metadata classifies this object heuristically through its foreign key structure. Because it holds only a primary key constraint and a single outbound foreign key to QA_SAMPLING_PLANS, with no dependent child tables deriving from it, the classification is standalone. In Data Vault terms, such a structure is best modeled as a satellite or reference entity rather than a hub or link: it describes the temporal validity context of a specification and sampling relationship rather than representing the durable business key of a core entity. This classification is offered as a modeling suggestion, not an authoritative declaration.

Key Information Stored

The table documents twelve physical columns. The most operationally significant are listed below, distinguishing the surrogate key from business-key candidates.

  • SPEC_VR_ID — Surrogate primary key, enforced by the unique index GMD_STABILITY_SPEC_VRS_PK. This is the single documented unique index and therefore the only business-key candidate identified in the metadata.
  • SPEC_ID — Identifies the specification (or specification version) to which the validity rule applies; the operative link to the stability specification being governed.
  • SPEC_VR_STATUS — The status of the validity rule, governing whether the rule is active, pending, or retired.
  • START_DATE — Beginning of the effective window for the validity rule.
  • END_DATE — End of the effective window, enabling date-bounded validity.
  • SAMPLING_PLAN_ID — Foreign key to QA_SAMPLING_PLANS, associating the rule with a defined sampling plan.
  • DELETE_MARK — Standard Oracle soft-delete indicator for logical row removal.
  • CREATION_DATE, CREATED_BY — Audit columns recording row creation.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Audit columns recording the most recent modification and session.

Together these columns define a specification validity rule that is time-bounded, status-controlled, and tied to a sampling plan, with full Oracle auditability.

Common Use Cases and Queries

Reporting and validation scenarios typically resolve which specification version is valid for a stability study on a given date, or which sampling plan governs a specification.

SELECT spec_vr_id, spec_id, spec_vr_status,
       start_date, end_date, sampling_plan_id
  FROM gmd.gmd_stability_spec_vrs
 WHERE spec_id = :spec_id
   AND delete_mark = 0
   AND TRUNC(SYSDATE) BETWEEN start_date AND NVL(end_date, SYSDATE);

A second common pattern joins to the sampling plan to report governing plans:

SELECT v.spec_id, v.spec_vr_status, p.sampling_plan_id
  FROM gmd.gmd_stability_spec_vrs v,
       qa_sampling_plans p
 WHERE v.sampling_plan_id = p.sampling_plan_id
   AND v.delete_mark = 0;

Audit queries using LAST_UPDATE_DATE support change-tracking, while status-filtered extracts feed stability study configuration reports.

Related Objects

  • QA_SAMPLING_PLANS — Referenced through the documented foreign key GMD_STABILITY_SPEC_VRS.SAMPLING_PLAN_ID. This is the only documented relationship.
  • GMD_STABILITY_SPEC_VRS_PK — The primary key index unique on SPEC_VR_ID, defining row identity.
  • GMD stability specification tables — Related specification tables referenced by SPEC_ID supply the governed specification data.
  • GMD stability study tables — Parent stability study and study specification entities consume these validity rules.
  • QA sampling entities — Associated sampling objects beyond plans depend on the shared sampling identifier.

Because the metadata documents only one foreign key, the relationship to QA_SAMPLING_PLANS is the authoritative dependency; all other associations are inferred from the specification and sampling domains.