Search Results gms_reference_numbers_u1

Overview

GMS.GMS_REFERENCE_NUMBERS is a transactional table in the Oracle E-Business Suite Grants Management (GMS) schema. It stores the reference numbers associated with a particular award, allowing users to define and describe as many references as needed for a given award. Each row represents a single reference value of a specific reference type attached to an award. The object carries a VALID status in the ETRM 12.2.2 documentation and is designated under FND Design Data as GMS.GMS_REFERENCE_NUMBERS, with its physical storage in the APPS_TS_TX_DATA tablespace at PCT Free 10.

From a Data Vault modeling perspective, the dependency metadata classifies this object as standalone, meaning it neither references nor is referenced by other database objects through foreign keys. Its documented primary key, GMS_REFERENCE_NUMBERS_PK, is composed of AWARD_ID, TYPE, and VALUE. Heuristically, this object can be modeled as a satellite attached to the award hub, since it stores descriptive, multi-valued reference attributes qualified by type and value rather than the award identity itself. The absence of FK dependencies in the metadata suggests relationships are enforced at the application layer rather than through database constraints.

Key Information Stored

The table contains nine documented columns. The most significant are:

• AWARD_ID (NUMBER, 15) — Award identifier; the anchor linking each reference to its parent award.
• TYPE (VARCHAR2, 30) — Reference type, qualifying the category of the reference value.
• VALUE (VARCHAR2, 30) — The actual value of the reference.
• REQUIRED_FLAG (VARCHAR2) — Indicates whether the reference is mandatory for the award.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard Who columns capturing audit and user-tracking information.

The surrogate primary key GMS_REFERENCE_NUMBERS_PK spans AWARD_ID, TYPE, and VALUE. The unique index GMS_REFERENCE_NUMBERS_U1, stored in APPS_TS_TX_IDX, enforces uniqueness on the same three columns: AWARD_ID, TYPE, and VALUE. This means a given award cannot hold duplicate combinations of reference type and value. Note that GMS_REFERENCE_NUMBERS_U1 is a business-key candidate rather than a true surrogate; it is a NORMAL, UNIQUE index providing the enforced uniqueness constraint.

Common Use Cases and Queries

Typical scenarios include retrieving all references attached to a specific award, auditing the reference types configured for awards, and reporting on mandatory references that remain unpopulated. A standard query pattern lists all references for a given award:

• SELECT AWARD_ID, TYPE, VALUE, REQUIRED_FLAG FROM GMS.GMS_REFERENCE_NUMBERS WHERE AWARD_ID = :award_id;
• SELECT TYPE, VALUE FROM GMS.GMS_REFERENCE_NUMBERS WHERE AWARD_ID = :award_id AND REQUIRED_FLAG = 'Y';
• SELECT AWARD_ID, COUNT(*) FROM GMS.GMS_REFERENCE_NUMBERS GROUP BY AWARD_ID;

Reporting use cases include verifying that mandatory reference types are populated per award, analyzing distribution of reference types across an award portfolio, and auditing who created or last modified reference values via the Standard Who columns.

Related Objects

The ETRM metadata states that GMS.GMS_REFERENCE_NUMBERS does not reference any database object and that it is referenced by the APPS synonym GMS_REFERENCE_NUMBERS. Within the Grants Management schema, the principal logical relationship is to the award entity, joined on AWARD_ID. Because FK enforcement is not documented on this table, application-level integrity governs the linkage. Significant related objects to consider include:

• GMS_REFERENCE_NUMBERS (APPS synonym) — the exposed access point for this table.
• GMS_AWARDS — the parent award entity, joined on AWARD_ID.
• GMS_AWARD_REFERENCES — award reference configuration associations.
• GMS_REFERENCE_TYPES — defines the valid TYPE values usable for references.
• GMS award-related public APIs and views that surface reference data to end users.

These relationships are asserted through documented join columns and schema conventions rather than enforced foreign keys.