Search Results orginal_text_code

Overview

GMD.GMD_MATERIAL_HEADER_GTMP is a global temporary table within the GMD (Process Manufacturing) schema of Oracle E-Business Suite, documented as VALID in ETRM for releases 12.1.1 and 12.2.2. It functions as a session-scoped staging structure, holding transient rows that are visible only to the session that inserted them. The documented data duration is SYS$SESSION, meaning rows persist for the life of the session and are removed when the session terminates or the transaction boundary defined by the on-commit/on-rollback clause is reached. Typical usage is as an intermediate holding area during material maintenance, batch or formula-driven material transactions, where application logic populates the table and subsequently reads it back within the same database session to drive inventory updates or move order creation.

From a heuristic Data Vault modeling perspective, the table is classified as standalone. It is best treated as a transient staging satellite: it carries descriptive attributes and transactional context keyed by a surrogate identifier, but the materialized rows are discarded at session end, so it is unsuitable as a persistent hub or link in a warehouse design. Any durable model should source from the permanent transaction tables that write into or read from this temporary structure.

Key Information Stored

The table contains 25 documented columns. The primary key and sole unique index is GMD_MATERIAL_HDR_GTMP_PK, defined on ENTITY_ID (NUMBER(15)). This surrogate key is the only documented business-key candidate; because the row set is temporary, ENTITY_ID uniqueness applies only within the session rather than as a durable constraint.

• ENTITY_ID — surrogate primary key and unique index column, identifying each staged row.
• BATCH_ID, FORMULA_ID, SPRD_ID — foreign references to the batch, formula, and SPRD (formula/routing) records being processed; SPRD_ID is documented as referencing LM_SPRD_FLS.
• FORMULA_NO, FORMULA_VERS, FORMULA_DESC1, SPRD_NAME — descriptive formula and SPRD context carried for display or validation.
• BATCH_NO — the human-readable batch number associated with the staged material header.
• ORGN_CODE, ORGN_NAME, PLANT_CODE — organization and plant identifiers used to determine the target inventory organization.
• ORGANIZATION_ID, LAB_ORGANIZATION_ID, PLANT_ORGANIZATION_ID — numeric organization keys distinguishing the transacting organization, laboratory organization, and plant organization.
• UPDATE_INVENTORY_IND — flag controlling whether inventory is updated for the staged material.
• MAINTAIN_TYPE, TEXT_CODE, ORGINAL_TEXT_CODE — maintenance type and text token references (note the documented spelling ORGINAL_TEXT_CODE).
• MOVE_ORDER_HEADER_ID — links staged rows to a move order header generated as a result of processing.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.

Common Use Cases and Queries

Because the table is session-scoped, all practical queries execute inside the same database session that populated it, typically immediately before a batch material maintenance or move order creation routine. A standard retrieval pattern selects the full column list by surrogate key:

SELECT ENTITY_ID, BATCH_ID, FORMULA_ID, SPRD_ID, BATCH_NO, FORMULA_NO, ORGN_CODE, UPDATE_INVENTORY_IND, ORGANIZATION_ID, MOVE_ORDER_HEADER_ID FROM GMD.GMD_MATERIAL_HEADER_GTMP WHERE ENTITY_ID = :p_entity_id;

Reporting scenarios include validating staged batches prior to inventory update (filtering on UPDATE_INVENTORY_IND), reconciling formula versions against SPRD records, and auditing move order generation by grouping on MOVE_ORDER_HEADER_ID. Diagnostic queries commonly join the temporary rows back to LM_SPRD_FLS on SPRD_ID to confirm that the staged SPRD references are valid before committing downstream transactions. Because rows vanish at session end, the table must never be used as an integration or reporting source outside the originating session.

Related Objects

• LM_SPRD_FLS — referenced by SPRD_ID; the primary documented foreign key relationship used to validate formula/SPRD context.
• GMD_MATERIAL_HEADER — the permanent material header entity into which staged data is ultimately written or against which it is validated.
• GMD_BATCH_HEADER — provides batch context for BATCH_ID and BATCH_NO values held in the staging rows.
• GMD_FORMULA_HEADER — source of FORMULA_NO, FORMULA_VERS, and FORMULA_DESC1 attributes.
• MTL_MOVE_ORDER_HEADERS — relates to MOVE_ORDER_HEADER_ID when staged processing results in move order creation.
• HR_OPERATING_UNITS / ORG_ORGANIZATION_DEFINITIONS — resolve ORGANIZATION_ID, LAB_ORGANIZATION_ID, and PLANT_ORGANIZATION_ID to organization names.
• FND_GLOBAL — supplies the session context (user, login, org) that governs visibility of rows in this temporary table.
• GMD_MATERIAL_HEADER_GTMP indexes — the GMD_MATERIAL_HDR_GTMP_PK unique index on ENTITY_ID supports single-row lookups during processing.