Results for “igs_ps_fee_trg_hist_all”

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

Overview

The IGS_PS_FEE_TRG_HIST_ALL table is a core historical reference object within the Oracle E-Business Suite Student System (IGS) module. It records the complete change history of fee triggers associated with program types, program groups, programs, and program offering options. Each row captures a prior or current state of a fee trigger definition, enabling institutions to audit how fee application rules evolved over time across academic structures.

Functionally, the table serves two purposes: it preserves the configuration state of fee triggers as of a given effective date, and it provides the temporal lineage needed for enrollment, billing, and fee assessment processes that must reference the rule set in force when a student transaction occurred.

From a dimensional modeling perspective, the heuristic Data Vault classification derived from the foreign key structure is satellite-leaning. This suggests the table is best modeled as a satellite attached to a fee trigger business key, capturing descriptive and historical attributes keyed by a change date and sequence. The primary key IGS_PS_FEE_TRG_HIST_PK, composed of FEE_CAT, FEE_CAL_TYPE, FEE_CI_SEQUENCE_NUMBER, FEE_TYPE, COURSE_CD, SEQUENCE_NUMBER, and HIST_START_DT, reinforces this interpretation, since the composite key includes a date and sequence component typical of a satellite's change-tracking grain.

Key Information Stored

The documented schema exposes 22 columns. The most operationally significant include:

• FEE_CAT, FEE_CAL_TYPE, FEE_CI_SEQUENCE_NUMBER, FEE_TYPE — the fee category and fee calendar/type combination that identifies the fee being triggered.
• COURSE_CD — the course against which the fee trigger is defined.
• SEQUENCE_NUMBER — orders fee trigger records where multiple triggers exist for the same fee construct.
• HIST_START_DT and HIST_END_DT — the effective date range of the historical record; HIST_START_DT is part of the primary key.
• HIST_WHO — the user or process responsible for the change.
• VERSION_NUMBER — supports concurrency and lineage tracking of successive edits.
• CAL_TYPE, LOCATION_CD, ATTENDANCE_MODE, ATTENDANCE_TYPE — contextual attributes describing the calendar, delivery location, and attendance characteristics tied to the trigger.
• FEE_TRIGGER_GROUP_NUMBER — groups related triggers into a single assessment unit.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, ORG_ID — standard EBS WHO columns and multi-org context.

The surrogate-style PK is IGS_PS_FEE_TRG_HIST_PK, while the business-key candidate is defined by the unique index IGS_PS_FEE_TRG_HIST_ALL_U1 over FEE_CAT, FEE_CAL_TYPE, FEE_CI_SEQUENCE_NUMBER, FEE_TYPE, COURSE_CD, SEQUENCE_NUMBER, and HIST_START_DT. The _ALL suffix and ORG_ID column indicate multi-org partitioning support.

Common Use Cases and Queries

Typical reporting scenarios include reconstructing the fee trigger configuration active on a past enrollment date, auditing changes made by specific users, and comparing successive versions of a trigger.

• Point-in-time retrieval:

  SELECT * FROM igs.igs_ps_fee_trg_hist_all
  WHERE fee_cat = :p_fee_cat
    AND course_cd = :p_course_cd
    AND :p_asof_dt BETWEEN hist_start_dt AND NVL(hist_end_dt, :p_asof_dt);

• Change audit by user:

  SELECT hist_who, version_number, hist_start_dt, hist_end_dt
  FROM igs.igs_ps_fee_trg_hist_all
  WHERE fee_type = :p_fee_type
  ORDER BY hist_start_dt DESC;

• Group analysis:

  SELECT fee_trigger_group_number, COUNT(*)
  FROM igs.igs_ps_fee_trg_hist_all
  GROUP BY fee_trigger_group_number;

Related Objects

The principal foreign key relationship links FEE_CAT, FEE_CAL_TYPE, FEE_CI_SEQUENCE_NUMBER, and FEE_TYPE to IGS_FI_F_CAT_FEE_LBL_ALL, which holds fee label and category definitions. Other significant related objects include the base fee trigger table(s) whose history this table shadows, the program and program offering tables that scope the trigger, and the standard EBS concurrent programs that populate HISTORY and WHO columns. Joins should consistently qualify by the four FK columns listed above to preserve fee category and calendar alignment.