Results for “um_type”

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

Overview

SY_UOMS_STD is a table owned by the GMA schema within the Oracle EBS Process Manufacturing Systems product family. Its documented purpose is to associate Units of Measure with Unit of Measure Types. The table is registered as VALID in the ETRM data dictionary for releases 12.1.1 and 12.2.2, and it carries a note that it is "Not yet used" — meaning it exists as a defined, supported object in the schema but does not participate in the active functional processing of GMA transactions.

Functionally, SY_UOMS_STD sits between the base unit-of-measure master and the unit-of-measure type definitions. It acts as a bridge that resolves which unit of measure belongs to which UOM type classification, since a single unit of measure can conceptually be valid for more than one type. Its presence in the schema reflects an architectural intent to normalize the UOM-to-type association rather than hard-code it.

Applying the heuristic Data Vault classification mined from the foreign key structure, this object is best modeled as a link. A link table is appropriate because SY_UOMS_STD does not hold its own descriptive attributes of business interest; its documented columns consist of two foreign keys plus standard audit columns. The table's role is to record the relationship between two parent entities — SY_UOMS_TYP and SY_UOMS_MST — and therefore it functions as an associative resolution table rather than a business entity hub or a descriptive satellite.

Key Information Stored

The documented physical schema contains seven columns. The most significant are the two associative columns and the audit trail set.

• UM_TYPE — The unit-of-measure type identifier. This is the column declared as the primary key constraint SY_UOMS_STD_PK, and it also serves as a foreign key to SY_UOMS_TYP. It is the only documented column promoted to a unique index, making it the sole documented business-key candidate.
• UM_CODE — The unit-of-measure code, acting as a foreign key to SY_UOMS_MST. Together with UM_TYPE this column forms the practical association pair that defines which UOM is linked to which UOM type.
• CREATED_BY — Standard EBS audit column identifying the application user or concurrent program that inserted the row.
• CREATION_DATE — The date and time the association record was first created.
• LAST_UPDATED_BY — The user or process responsible for the most recent modification of the row.
• LAST_UPDATE_DATE — Timestamp of the most recent modification, and the column most commonly used for delta and incremental extract logic.
• LAST_UPDATE_LOGIN — The login identifier associated with the last update, used primarily for audit and diagnostic tracing.

The surrogate-versus-business-key distinction is worth noting: the documented unique index SY_UOMS_STD_PK is defined on UM_TYPE alone, which means the database enforces one row per UOM type rather than one row per UOM-type/UM-code pair. Because the table is documented as unused, this constraint definition has not been exercised against production data volumes.

Common Use Cases and Queries

Because the table is documented as not yet used, the primary use cases are investigative and preparatory rather than transactional. Typical scenarios include determining whether any data has been loaded, confirming schema availability during upgrades or custom development, and planning future integrations that would populate the association.

A basic existence check is the most frequent query pattern:

• SELECT COUNT(*) FROM gma.sy_uoms_std; — Establishes whether the table has been populated in the current instance.
• SELECT um_type, um_code FROM gma.sy_uoms_std ORDER BY um_type; — Lists all defined associations, useful when validating a proposed mapping load.
• SELECT s.um_type, s.um_code, t.um_type_description FROM gma.sy_uoms_std s, gma.sy_uoms_typ t WHERE s.um_type = t.um_type; — Joins the association to its UOM type parent to produce a human-readable mapping report.
• SELECT s.um_type, s.um_code, m.um_description FROM gma.sy_uoms_std s, gma.sy_uoms_mst m WHERE s.um_code = m.um_code; — Joins to the UOM master to validate that each association references a defined unit of measure.
• SELECT * FROM gma.sy_uoms_std WHERE last_update_date >= :p_since; — Incremental extraction pattern for downstream reporting or integration staging.

For reporting, the association table is best presented as a bridge dimension in a star schema, enabling analysis of transactions by UOM type through the UM_TYPE attribute.

Related Objects

The following objects are the most significant dependencies, derived from the documented foreign key relationships and naming conventions within the GMA UOM object family.

• SY_UOMS_TYP — The UOM type definition table, joined via SY_UOMS_STD.UM_TYPE = SY_UOMS_TYP.UM_TYPE. This is the parent for the UM_TYPE foreign key and the most direct dependency.
• SY_UOMS_MST — The unit-of-measure master table, joined via SY_UOMS_STD.UM_CODE = SY_UOMS_MST.UM_CODE. This is the parent for the UM_CODE foreign key.
• SY_UOMS_STD_PK — The unique index constraint enforcing the primary key on UM_TYPE.
• GMA schema metadata views — Data dictionary objects used to verify column definitions and constraint status for this table.
• UOM conversion and validation logic in GMA — Processes that resolve UOM types from base UOM codes would consume this association if it were activated.

Consumers should treat SY_UOMS_STD as a defined but dormant associative link. Any custom development that reads or writes it should be validated against the specific instance, since the documented "not yet used" status means functional behavior is not guaranteed by Oracle support.