Search Results gms_reference_numbers_pk

Overview

GMS_REFERENCE_NUMBERS is a Grants Accounting (GMS) module table that stores the reference numbers associated with awards. In Oracle E-Business Suite 12.1.1 and 12.2.2, this table serves as a supporting detail repository within the award management data model, allowing individual awards to be tagged with multiple external or internal identifiers — such as sponsor reference numbers, grant numbers, contract IDs, or legacy identifiers — that are meaningful to the institution but distinct from the system-generated award number.

The table is owned by the GMS schema and has a documented status of VALID. Its primary key is defined by the constraint GMS_REFERENCE_NUMBERS_PK, and a unique index, GMS_REFERENCE_NUMBERS_U1, enforces uniqueness across the business key columns AWARD_ID, TYPE, and VALUE. From a Data Vault modeling perspective, the metadata's heuristic classification is standalone, suggesting this object could be modeled as an independent entity rather than a satellite of a larger hub — the multiplicity of values per award is captured through the TYPE and VALUE pairing rather than through separate entities.

Key Information Stored

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

• AWARD_ID — The foreign key to the award entity. This column forms part of both the primary key and the unique business key, tying each reference number to a specific award record.
• TYPE — Categorizes the reference number (for example, sponsor award ID, contract number, or other classification). It is part of the primary key and unique index.
• VALUE — The actual reference number string. Together with AWARD_ID and TYPE, it completes the composite primary key GMS_REFERENCE_NUMBERS_PK and the unique constraint GMS_REFERENCE_NUMBERS_U1.
• REQUIRED_FLAG — Indicates whether the reference number is mandatory for the award, supporting validation and business rules.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS WHO columns providing audit and concurrency control metadata.

There is no separate surrogate key column; the primary key is the composite of AWARD_ID, TYPE, and VALUE. The unique index GMS_REFERENCE_NUMBERS_U1 mirrors this composite, confirming it as the business-key candidate.

Common Use Cases and Queries

Typical reporting scenarios include retrieving all reference numbers for a given award, resolving a sponsor-provided grant number back to an internal award, and auditing which awards carry mandatory reference types. A representative query is:

SELECT r.award_id, r.type, r.value, r.required_flag
FROM   gms.gms_reference_numbers r
WHERE  r.award_id = :award_id
ORDER BY r.type;

To locate an award by an external identifier:

SELECT r.award_id
FROM   gms.gms_reference_numbers r
WHERE  r.type  = :type
AND    r.value = :value;

These patterns support grant reconciliation, sponsor reporting, and integration feeds that map external award codes to Oracle EBS award records.

Related Objects

The metadata identifies GMS_REFERENCE_NUMBERS as standalone, with no outbound foreign keys documented beyond its own composite key. The most significant related objects, joined on AWARD_ID, are:

• GMS_AWARDS — The parent award entity; join on AWARD_ID.
• GMS_AWARD_VERSIONS / GMS_AWARD_HEADERS — Award header and version tracking, joined on AWARD_ID.
• GMS_AWARD_TEMPLATES — Template-derived awards referencing the same award identifiers.
• GMS_AWARD_QUESTIONS — Award-level attributes sharing the AWARD_ID key.
• GMS_AWARD_FUNDING — Funding distributions tied to AWARD_ID.

No public APIs are documented specifically for this table; it is typically accessed through Grants Accounting award UI screens or direct SQL reporting. Because it is heuristic-classified as standalone, integrations should treat reference numbers as an independent, award-scoped attribute set rather than a dependent detail of a larger hierarchy.