Results for “personnel_id”

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

Overview

The GMS_PERSONNEL table in the GMS (Grants Accounting) schema of Oracle E-Business Suite stores the personnel assignments associated with an award. Each row records an individual — identified by PERSON_ID — who holds a specific role on a sponsored project or grant, such as Principal Investigator, Co-Investigator, or other award-related responsibilities defined by AWARD_ROLE. The table therefore serves as the authoritative repository for personnel-to-award relationships within the Grants Accounting module, supporting effort reporting, compliance tracking, and sponsor-facing personnel documentation in both EBS 12.1.1 and 12.2.2.

From a dimensional modeling perspective, the metadata classifies GMS_PERSONNEL heuristically as standalone, indicating that no foreign keys originate from other tables into this object within the documented FK graph. This suggests the table may be modeled as an independent hub or satellite rather than as a junction or link between two hubs. Because AWARD_ID is a foreign key to IGF_AW_AWARD_ALL and PERSON_ID denotes an external party, the most natural business interpretation is a link between the award and the person, though the metadata does not enforce this classification. The documented foreign key relationship (GMS_PERSONNEL.AWARD_ID → IGF_AW_AWARD_ALL) anchors the table firmly to the award entity, while PERSON_ID ties each record to a person in the corresponding HR or TCA person tables (not explicitly documented here).

Key Information Stored

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

• PERSONNEL_ID — the surrogate primary key uniquely identifying each personnel assignment record.
• AWARD_ID — foreign key to IGF_AW_AWARD_ALL, linking the person to a specific award.
• PERSON_ID — identifier of the assigned individual.
• AWARD_ROLE — the role the person plays on the award (e.g., PI, Co-PI).
• START_DATE_ACTIVE / END_DATE_ACTIVE — the effective dating range of the assignment.
• REQUIRED_FLAG — indicates whether the role is mandatory for the award.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard audit columns.

The documented unique index GMS_PERSONNEL_U1 spans (AWARD_ID, PERSON_ID, AWARD_ROLE, START_DATE_ACTIVE), making this composite the natural business key candidate. This means a single person cannot be assigned the same role on the same award with the same start date more than once.

Common Use Cases and Queries

Typical scenarios include listing all personnel on a given award, identifying awards where a specific investigator is engaged, and reporting on active roles. For example:

• Personnel roster for an award: SELECT PERSON_ID, AWARD_ROLE FROM GMS_PERSONNEL WHERE AWARD_ID = :award_id AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE);
• All awards for a person: SELECT AWARD_ID, AWARD_ROLE FROM GMS_PERSONNEL WHERE PERSON_ID = :person_id;
• Active role count by award role: SELECT AWARD_ROLE, COUNT(*) FROM GMS_PERSONNEL WHERE SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE) GROUP BY AWARD_ROLE;

These queries support effort certification, sponsor reporting, and compliance reviews.

Related Objects

The most significant related object is IGF_AW_AWARD_ALL, joined via AWARD_ID, which holds the award header. PERSON_ID logically joins to the person tables in the HR or TCA schemas. Also relevant are award role setup tables, effort reporting tables, and grants reporting views that aggregate personnel data for sponsor submissions.