Search Results gach_id

Overview

The IGS_GR_AWD_CRMN_HIST table is a core data object within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Oracle Student Management (OSM) product, owned by the IGS schema. It functions as a historical audit table, capturing the evolving relationship between a graduand, their specific program attempt, and a scheduled graduation ceremony. Its primary role is to maintain a complete, time-versioned record of how graduands are assigned to ceremonies for the conferral of awards, enabling institutions to track changes over time for audit, reporting, and operational purposes.

Key Information Stored

The table stores a combination of unique identifiers, ceremony details, award specifics, and historical audit data. The primary key, GACH_ID, is a unique system-generated number for each row. The core business keys link a PERSON_ID and CREATE_DT (identifying a specific graduand record) to a ceremony defined by GRD_CAL_TYPE, GRD_CI_SEQUENCE_NUMBER, and CEREMONY_NUMBER. The awarded course and specific award are stored in AWARD_COURSE_CD, AWARD_CRS_VERSION_NUMBER, and AWARD_CD. Critical for its historical function are the HIST_START_DT and HIST_END_DT columns, which define the effective date range for the record, and HIST_WHO, which records the creating user. The ORDER_IN_PRESENTATION column is vital for ceremony logistics, dictating the graduand's sequence during the award presentation.

Common Use Cases and Queries

This table is essential for historical reporting, audit trails, and resolving data discrepancies. Common use cases include generating a complete history of ceremony assignments for a specific graduand, auditing changes made by administrative users, and reconciling historical award data. A typical query to retrieve the active ceremony assignment at a specific point in time would leverage the historical date columns.

• Retrieve Ceremony History for a Graduand: SELECT * FROM igs.igs_gr_awd_crmn_hist WHERE person_id = <PERSON_ID> ORDER BY hist_start_dt;
• Find Active Assignment on a Given Date: SELECT * FROM igs.igs_gr_awd_crmn_hist WHERE person_id = <PERSON_ID> AND '<DATE>' BETWEEN hist_start_dt AND NVL(hist_end_dt, SYSDATE);
• Report on Ceremony Seating Order: SELECT person_id, order_in_presentation FROM igs.igs_gr_awd_crmn_hist WHERE grd_cal_type = '<TYPE>' AND ceremony_number = <NUMBER> AND hist_end_dt IS NULL ORDER BY order_in_presentation;

Related Objects

The table's integrity is enforced through defined key relationships. Its primary key (GACH_ID) is supported by the unique index IGS_GR_AWD_CRMN_HIST_U1. A critical foreign key relationship exists, linking this history table back to the core graduand master data.

• IGS_GR_GRADUAND_ALL: The PERSON_ID column in IGS_GR_AWD_CRMN_HIST references the IGS_GR_GRADUAND_ALL table. This ensures every historical ceremony record is associated with a valid, existing graduand entity.

Furthermore, the non-unique index IGS_GR_AWD_CRMN_HIST_N1 on (PERSON_ID, CREATE_DT) optimizes queries that join on these business keys to retrieve graduand-specific history.