Search Results igs_gr_awd_crmn_hist_v

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

Overview

IGS_GR_AWD_CRMN_HIST_V is a PL/SQL-based view owned by the APPS schema in the Oracle EBS Student System (IGS) product family. It presents the audited history of award ceremony information for graduands. The view exposes a chronological record of how award ceremony attributes — such as presentation order, seat number, name pronunciation, and announced name — changed over time. Each row represents a historical version of a ceremony award record, bounded by HIST_START_DT and HIST_END_DT, and attributed to the user identified by HIST_WHO. The "special_requirements" search term typically surfaces this view because ceremony history records include presentation and special-handling attributes that graduation offices review when administering special requirements for award recipients.

From an EBS 12.1.1 / 12.2.2 perspective, the view is read-only and is intended for reporting and integration, not for direct DML. It is a key object for auditing ceremony data changes and for reconstructing point-in-time snapshots of ceremony lists for print or downstream systems.

Underlying Base Objects

The view is defined over the ceremony award history base table, aliased as GACH1, and a current ceremony award table, aliased as GAC1, which supplies fallback values where historical columns are null. The view does not simply project the history table; it resolves selected columns through the audited-column retrieval function IGS_AU_GEN_003.AUDP_GET_GACH_COL, which returns the value of a named column as it existed at a specified historical timestamp.

The resolution logic applies a three-tier pattern for each attribute: the value stored directly on the history row (GACH1), then the value returned by the audit function for the given key set and HIST_END_DT, then the current value from GAC1. This makes the view resilient when history rows predate full auditing of certain columns. The key set passed to the audit function comprises PERSON_ID, CREATE_DT, GRD_CAL_TYPE, GRD_CI_SEQUENCE_NUMBER, CEREMONY_NUMBER, AWARD_COURSE_CD, AWARD_CRS_VERSION_NUMBER, and AWARD_CD, together with HIST_END_DT.

Key Columns

• PERSON_ID — the graduand's party identifier; the primary join key to person and award records.
• CREATE_DT — creation timestamp of the original ceremony award record.
• GRD_CAL_TYPE, GRD_CI_SEQUENCE_NUMBER, CEREMONY_NUMBER — identify the graduation calendar, calendar instance, and ceremony.
• AWARD_COURSE_CD, AWARD_CRS_VERSION_NUMBER, AWARD_CD — identify the award course, version, and award for which ceremony details apply.
• HIST_START_DT, HIST_END_DT — validity window of the historical version; HIST_END_DT is also passed to the audit retrieval function.
• HIST_WHO — the database user or application user who made the change.
• US_GROUP_NUMBER, ORDER_IN_PRESENTATION — numeric presentation ordering attributes, resolved via NVL and the audit function.
• GRADUAND_SEAT_NUMBER, NAME_PRONUNCIATION, NAME_ANNOUNCED — text attributes subject to SUBSTR truncation (lengths 10, 60, and as defined) when retrieved through the audit function.

Common Use Cases and Queries

Typical uses include auditing ceremony changes, reconstructing a ceremony roster as of a past date, and feeding downstream print or integration processes for special requirements.

• Current history rows for a person:

  SELECT person_id, ceremony_number, order_in_presentation,
         graduand_seat_number, hist_start_dt, hist_end_dt, hist_who
  FROM   apps.igs_gr_awd_crmn_hist_v
  WHERE  person_id = :p_person_id
  ORDER BY hist_start_dt DESC;

• As-of snapshot for a ceremony:

  SELECT person_id, award_cd, order_in_presentation
  FROM   apps.igs_gr_awd_crmn_hist_v
  WHERE  grd_cal_type = :cal AND ceremony_number = :cer
  AND    :as_of_dt BETWEEN hist_start_dt AND NVL(hist_end_dt, :as_of_dt);

• Change audit by user:

  SELECT hist_who, COUNT(*)
  FROM   apps.igs_gr_awd_crmn_hist_v
  WHERE  hist_start_dt >= :from_dt
  GROUP BY hist_who;

Because the view invokes IGS_AU_GEN_003.AUDP_GET_GACH_COL per row, broad queries without selective predicates on the key columns can be expensive; constrain by person, calendar, ceremony, or date range wherever possible.