Search Results gmd_storage_plans_b_pk

Overview

GMD_STORAGE_PLANS_B is a base table in the GMD schema (Process Manufacturing Product Development) that stores Stability Study Storage Plan definitions. A storage plan defines the environmental and scheduling conditions under which stability samples are held and tested over the life of a stability study — for example, the temperature and humidity regime, the interval schedule at which samples are pulled and assayed, and the associated test plan. The _B suffix indicates that the table participates in the Oracle Applications multi-language (MLS) pattern: the base table holds language-independent attributes, while a corresponding _TL table (where defined for the entity) carries translated descriptive text. Rows in this table are referenced by stability study headers and by detail lines that further describe the plan.

From a heuristic Data Vault modeling perspective, the FK/relationship mining classifies this entity as a standalone object. That is a suggestion rather than a normative rule: it behaves much like a reference or lookup hub, keyed by a surrogate identifier and uniquely named by a natural business key, and it is not obviously decomposed into dependent link or satellite structures within the documented FK graph. A Data Vault practitioner would likely model STORAGE_PLAN_ID as a hub key with descriptive satellites for the name, interval plan association, and audit attributes.

Key Information Stored

The table is documented with 41 physical columns in ETRM 12.2.2. The most significant are:

• STORAGE_PLAN_ID — the surrogate primary key (constraint GMD_STORAGE_PLANS_B_PK). This is the internal identifier used by all dependent tables.
• STORAGE_PLAN_NAME — the natural business key, enforced by unique index GMD_STORAGE_PLANS_B_U1. Users select a storage plan by this name in the Stability Study UI, so it is the primary lookup column.
• BASE_TEST_INTERVAL_PLAN_ID — reference to the interval plan that governs the pull/test schedule applied when the storage plan is used. This links the storage condition definition to the timing discipline of the study.
• TEXT_CODE — a code token typically used to resolve translated or externalized descriptive text for the plan, consistent with the MLS design implied by the _B suffix.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 … ATTRIBUTE30 — the standard Oracle EBS descriptive flexfield (DFF) columns. ATTRIBUTE_CATEGORY determines the context, and the numbered ATTRIBUTE columns hold customer-defined descriptive data. These are the extension points customers most often populate.
• DELETE_MARK — the soft-delete indicator (0/1). Active rows are those with DELETE_MARK = 0; queries should always filter on this column.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard who-columns maintained by the EBS audit framework.

The distinction between the surrogate key (STORAGE_PLAN_ID) and the business key (STORAGE_PLAN_NAME) matters in integration and data conversion work: source systems frequently supply names rather than internal IDs, and the U1 index is what guarantees uniqueness during load validation.

Common Use Cases and Queries

Typical operational reporting resolves storage plans for stability studies, lists the interval plans in use, or validates reference data before conversion. A standard lookup query is:

SELECT storage_plan_id, storage_plan_name, base_test_interval_plan_id
FROM gmd.gmd_storage_plans_b
WHERE delete_mark = 0
ORDER BY storage_plan_name;

To find the stability studies that use a given plan, join through the study header:

SELECT s.study_no, s.storage_plan_id, p.storage_plan_name
FROM gmd.gmd_stability_studies_b s, gmd.gmd_storage_plans_b p
WHERE s.storage_plan_id = p.storage_plan_id
AND p.storage_plan_name = :plan_name;

Detail-level reporting joins GMD_STORAGE_PLAN_DETAILS on STORAGE_PLAN_ID to expand the plan into its individual conditions or lines. Because the DFF columns are generic, a common analytics task is pivoting ATTRIBUTE_CATEGORY plus selected ATTRIBUTEn values to expose customer-specific attributes. Conversion and migration scripts typically stage names, look up STORAGE_PLAN_ID via the U1 unique index, and default unused ATTRIBUTE columns to NULL.

Related Objects

• GMD_STORAGE_PLAN_DETAILS — joins on STORAGE_PLAN_ID; holds the detail rows that describe the individual steps or conditions of the storage plan.
• GMD_STABILITY_STUDIES_B — joins on STORAGE_PLAN_ID; the stability study header that consumes the storage plan as one of its defining references.
• GMD_STABILITY_STUDIES_TL — translated text for the study header; reached through the study base table rather than directly by FK to this table.
• GMD_STORAGE_PLANS_TL — where defined, carries translated name/description text keyed to STORAGE_PLAN_ID, complementing the _B base record.
• Interval plan tables in GMD — referenced indirectly through BASE_TEST_INTERVAL_PLAN_ID, providing the schedule definitions applied to samples held under the storage plan.
• Stability Study APIs and concurrent programs — the PL/SQL and OAF interfaces used to create and maintain storage plans; these enforce the U1 uniqueness and populate the DFF columns.

Because the table is self-contained (standalone classification), referential dependencies run outward from GMD_STORAGE_PLANS_B: child tables point to it, but it depends only on the interval plan reference and standard EBS audit infrastructure. Deletion should be performed through the application or by setting DELETE_MARK rather than by physical DELETE, to avoid orphaned rows in the dependent detail and study tables.