Search Results gmd_storage_plan_details




Overview
Explore in AI Chat

Overview

GMD_STORAGE_PLAN_DETAILS is a table in the GMD schema, owned by the Oracle Process Manufacturing Product Development (OPM PD) module. It stores the detail lines that make up a Stability Study Storage Plan, defining the physical storage conditions, organization, subinventory, and location at which stability samples are held, as well as the test interval plan that governs testing at each condition. The parent header object is GMD_STORAGE_PLANS_B, so the table functions as the child entity within a master–detail relationship that describes how a stability study's samples are distributed across the warehouse for a given study period.

Mined from its foreign-key structure, the heuristic Data Vault classification for this table is standalone. It is neither a pure hub nor a link: it carries its own surrogate primary key and descriptive attributes while also acting as a dependent child of GMD_STORAGE_PLANS_B and GMD_TEST_INTERVAL_PLANS_B. The standalone classification should be read as a modeling suggestion rather than a prescriptive Data Vault design.

Key Information Stored

The table is owned by GMD and contains 48 documented columns in the 12.2.2 physical schema. The most significant columns for functional and integration purposes are:

  • STORAGE_PLAN_DETAIL_ID — the surrogate primary key, enforced by the unique index GMD_STORAGE_PLAN_DETAILS_PK. It uniquely identifies each storage plan detail line and is the column referenced by dependent child tables.
  • STORAGE_PLAN_ID — the foreign key to GMD_STORAGE_PLANS_B, linking each detail line to its parent storage plan definition. This is the principal business-key relationship used in joins.
  • STORAGE_SPEC_ID — identifies the storage specification associated with the line, describing the environmental or physical storage standard that applies.
  • TEST_INTERVAL_PLAN_ID — the foreign key to GMD_TEST_INTERVAL_PLANS_B, defining the testing schedule applied to samples held under this detail line.
  • STORAGE_ORGANIZATION_ID, STORAGE_SUBINVENTORY, STORAGE_LOCATOR_ID — the inventory organization, subinventory, and locator that specify precisely where material is stored.
  • WHSE_CODE, LOCATION — legacy warehouse and location identifiers retained alongside the newer inventory keys.
  • RESOURCES — identifies the resource or resource combination associated with the storage plan detail.
  • TEXT_CODE — a reference to descriptive text attached to the line.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE30 — the standard Oracle EBS descriptive flexfield (DFF) columns, enabling client-specific extension of the storage plan detail without schema changes.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns.
  • MIGRATED_IND — a flag identifying rows migrated from a prior release or legacy system.

The only documented unique index is the primary key on STORAGE_PLAN_DETAIL_ID; no additional business-key unique indexes are documented, so the surrogate key is the sole enforced uniqueness constraint.

Common Use Cases and Queries

Typical reporting scenarios include listing all storage conditions defined under a stability study storage plan, resolving the warehouse and locator placement for each condition, and joining to the test interval plan to derive the testing schedule. A representative query joining the detail table to its two parent headers is:

  • SELECT d.storage_plan_detail_id, d.storage_plan_id, d.storage_spec_id, d.whse_code, d.location, d.storage_organization_id, d.storage_subinventory, d.storage_locator_id, t.interval_plan_name FROM gmd_storage_plan_details d, gmd_storage_plans_b p, gmd_test_interval_plans_b t WHERE d.storage_plan_id = p.storage_plan_id AND d.test_interval_plan_id = t.test_interval_plan_id(+) AND d.storage_plan_id = :p_plan_id;
  • Filtering on MIGRATED_IND to isolate rows created by a legacy migration for reconciliation.
  • Reading the ATTRIBUTE1–ATTRIBUTE30 DFF columns to extract client-defined storage attributes in custom reports.
  • Joining to GMD_SS_VARIANTS by STORAGE_PLAN_DETAIL_ID to identify which stability study variants are assigned to a given storage condition, supporting sample distribution and workload analysis.

Related Objects

  • GMD_STORAGE_PLANS_B — the parent header table; joined on STORAGE_PLAN_ID.
  • GMD_TEST_INTERVAL_PLANS_B — the test interval plan header; joined on TEST_INTERVAL_PLAN_ID.
  • GMD_SS_VARIANTS — a dependent child referencing this table via STORAGE_PLAN_DETAIL_ID; each variant record points to the storage plan detail that governs its sample placement.
  • GMD_STORAGE_PLAN_DETAILS_PK — the primary key constraint/index on STORAGE_PLAN_DETAIL_ID, used for direct row lookup and referential enforcement.
  • GMD_STORAGE_SPECS (via STORAGE_SPEC_ID) — the storage specification referenced by each detail line, providing the descriptive standard for the condition.
  • Inventory organization, subinventory, and locator master objects referenced through STORAGE_ORGANIZATION_ID, STORAGE_SUBINVENTORY, and STORAGE_LOCATOR_ID.