Results for “sy_geog_mst”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

SY_GEOG_MST is a master reference table owned by the GMA schema within the Oracle EBS Process Manufacturing (OPM) module family. It stores the canonical definitions of geographic regions used throughout the process manufacturing application stack — states, provinces, countries, or other territorial groupings referenced by formulas, plants, warehouses, and regulatory constructs. The table functions as a controlled lookup, ensuring that downstream transactions and setups point to a consistent, validated set of geographic identifiers rather than free-text values.

From a Data Vault modeling perspective, the mined relationship structure classifies SY_GEOG_MST as satellite-leaning. Its composite primary key (GEOG_TYPE, GEOG_CODE) anchors a natural business key, while descriptive attributes such as GEOG_DESC and TEXT_CODE behave like satellite payload columns. The single foreign key outbound to SY_TEXT_HDR reinforces this interpretation, indicating the table acts primarily as a descriptive reference dimension rather than a transactional hub or a many-to-many linking table.

Key Information Stored

The table contains twelve documented columns. The most significant are summarized below:

• GEOG_TYPE – Part of the composite primary key; classifies the geographic region (for example, country versus state/province).
• GEOG_CODE – The second component of the primary key; the user-facing shorthand identifier for the region.
• GEOG_ID – Surrogate identifier used for internal referential integrity.
• GEOG_DESC – The descriptive name of the geographic region.
• TEXT_CODE – Foreign key to SY_TEXT_HDR, providing extended or translated descriptive text.
• DELETE_MARK – Standard soft-delete flag; a value of 1 indicates the row is logically removed.
• TRANS_CNT – Transaction counter maintained by OPM for concurrency and row-version tracking.
• CREATION_DATE / CREATED_BY – Audit trail recording who created the record and when.
• LAST_UPDATE_DATE / LAST_UPDATED_BY / LAST_UPDATE_LOGIN – Standard EBS audit columns capturing the last modification event.

The business-key candidate is documented through the unique index SY_GEOG_MST_PK, defined on (GEOG_TYPE, GEOG_CODE). This distinguishes the natural key from the surrogate GEOG_ID and is the correct join predicate when resolving geographic regions from dependent tables.

Common Use Cases and Queries

Typical scenarios include validating that a plant or warehouse is assigned to a valid region, driving regional reporting across process manufacturing facilities, and resolving display text for geographic codes. A basic lookup follows:

• SELECT GEOG_TYPE, GEOG_CODE, GEOG_DESC FROM GMA.SY_GEOG_MST WHERE DELETE_MARK = 0 ORDER BY GEOG_TYPE, GEOG_CODE;
• SELECT GEOG_DESC FROM GMA.SY_GEOG_MST WHERE GEOG_TYPE = :p_type AND GEOG_CODE = :p_code AND DELETE_MARK = 0;
• Joining to retrieve extended text: SELECT m.GEOG_CODE, h.TEXT_DESCRIPTION FROM GMA.SY_GEOG_MST m, GMA.SY_TEXT_HDR h WHERE m.TEXT_CODE = h.TEXT_CODE AND m.DELETE_MARK = 0;

Reports commonly filter on DELETE_MARK and group by GEOG_TYPE to produce regional roll-ups. Because the table is a low-volume reference set, it is a candidate for caching or value-set attachment in concurrent reporting logic.

Related Objects

The following objects are most significant in relation to SY_GEOG_MST:

• SY_TEXT_HDR – Referenced via the documented foreign key SY_GEOG_MST.TEXT_CODE → SY_TEXT_HDR, supplying extended descriptive text.
• SY_GEOG_MST_PK – The primary key constraint/index on (GEOG_TYPE, GEOG_CODE), underpinning all lookups.
• Tables within OPM that store plant, warehouse, or resource geography (referencing GEOG_CODE or GEOG_ID).
• Value-set and flexfield definitions that consume geographic codes for validation.
• Concurrent programs and DBA utilities that maintain or purge this reference data.

Because the FK footprint is deliberately narrow, most dependent relationships are enforced at the application layer rather than through explicit database constraints, so query authors should confirm join semantics against the process manufacturing data model where the FK is not physically declared.