Search Results base_spec_vers

Overview

GMD_QM_E_STABILITY_STUDIES_V is a reporting and integration view owned by the APPS schema within the Oracle E-Business Suite environment. It belongs to the GMD product family, which corresponds to Process Manufacturing Product Development, and its status in the ETRM 12.1.1 and 12.2.2 data dictionary is VALID. The view consolidates stability study header data with related descriptive attributes, presenting a denormalized rowset that is convenient for concurrent programs, Oracle Reports, OAF pages, and external integration consumers.

The view exposes stability study identifiers, item and specification context, notification and testing parameters, storage plan references, a full set of schedule and actual date fields, shelf life recommendations, and status/delete indicators. Its role is to shield callers from the multi-table joins required to resolve item concatenated segments, QC status meanings, and specification names and versions, thereby simplifying downstream SQL.

Underlying Base Objects

The view text is defined over four referenced base objects, each surfaced as a synonym in the APPS schema:

• GMD_STABILITY_STUDIES — aliased as A; the driving table supplying the study identifiers, timing, status, and shelf life columns.
• GMD_QC_STATUS — aliased as B; supplies the decoded status MEANING where ENTITY_TYPE equals 'STABILITY' and STATUS_CODE matches the study status.
• MTL_SYSTEM_ITEMS_KFV — aliased as C; supplies the concatenated item segments and item description, joined on ORGANIZATION_ID and INVENTORY_ITEM_ID.
• GMD_SPECIFICATIONS — aliased as D; supplies the base specification name and version, joined on BASE_SPEC_ID to SPEC_ID.

Because the joins on item and specification are mandatory equality joins, the view returns only stability studies whose item and base specification records exist in the respective tables. The QC status join is likewise equating, so a missing status decode will exclude the row.

Key Columns

The view projects the study primary key (SS_ID) together with the organization, study number (SS_NO), status meaning, and description. Item context is provided through ITEM_NO, ITEM_DESC1, and ITEM_REVISION, derived from the concatenated segments view. Specification context is exposed as BASE_SPEC_ID, BASE_SPEC_NAME, and BASE_SPEC_VERS.

Laboratory and storage references appear as LAB_ORGANIZATION_ID and STORAGE_PLAN_ID. Count columns (MATERIAL_SOURCES_CNT, STORAGE_CONDITIONS_CNT, PACKAGES_CNT) summarize related child records. Lifecycle timing is represented by three pairs of dates: SCHEDULED_START_DATE and SCHEDULED_END_DATE, REVISED_START_DATE and REVISED_END_DATE, and ACTUAL_START_DATE and ACTUAL_END_DATE. Notification lead time and testing grace period are exposed with their respective unit columns, and recommended shelf life is paired with its unit. The STATUS and DELETE_MARK columns support filtering for active records.

Common Use Cases and Queries

A frequent scenario is identifying studies whose planned completion is imminent or overdue, which directly explains user interest in the "scheduled_end_date" column. For example:

• Overdue active studies: SELECT SS_NO, ITEM_NO, SCHEDULED_END_DATE FROM GMD_QM_E_STABILITY_STUDIES_V WHERE SCHEDULED_END_DATE < SYSDATE AND STATUS = 'ACTIVE' AND DELETE_MARK = 0 ORDER BY SCHEDULED_END_DATE;
• Schedule versus actual variance: SELECT SS_NO, SCHEDULED_END_DATE, ACTUAL_END_DATE FROM GMD_QM_E_STABILITY_STUDIES_V WHERE ACTUAL_END_DATE IS NULL;
• Studies by item for a given organization: SELECT SS_NO, MEANING, BASE_SPEC_NAME FROM GMD_QM_E_STABILITY_STUDIES_V WHERE ORGANIZATION_ID = :p_org AND ITEM_NO LIKE :p_item;
• Shelf life review: SELECT SS_NO, RECOMMENDED_SHELF_LIFE, RECOMMENDED_SHELF_LIFE_UNIT FROM GMD_QM_E_STABILITY_STUDIES_V WHERE DELETE_MARK = 0;

Because the view already resolves status meanings and specification versions, these queries avoid additional joins and are suitable for direct use in reports and integration extracts.