Search Results igs_fi_f_ret_schd_ht_all

Overview

The table IGS_FI_F_RET_SCHD_HT_ALL is a core data object within the Oracle E-Business Suite's Student System (IGS) module, specifically for releases 12.1.1 and 12.2.2. It functions as a historical audit table, designed to track changes made to fee retention schedules over time. A fee retention schedule defines the rules and percentages for retaining fees under various conditions, such as student withdrawal. This table captures a complete version history of these schedules, enabling the system to maintain an immutable record of what the schedule rules were at any given point in the past. This is critical for audit compliance, financial reporting, and resolving disputes related to fee calculations based on historical policies.

Key Information Stored

The table stores a snapshot of a fee retention schedule at the moment a change occurs. Its composite primary keys are essential for uniquely identifying each historical record. The key columns include identifiers for the fee calendar (FEE_CAL_TYPE, FEE_CI_SEQUENCE_NUMBER), the specific fee type and category (FEE_TYPE, FEE_CAT), and the schedule instance (SCHEDULE_NUMBER). The HIST_START_DT column is pivotal, marking the effective date from which that particular version of the schedule became active. The table also includes a separate primary key constraint (IGS_FI_F_RET_SCHD_HT_PK) that incorporates S_RELATION_TYPE and SEQUENCE_NUMBER, indicating the table likely supports complex schedule structures and sequencing.

Common Use Cases and Queries

Primary use cases involve auditing, reporting, and historical fee liability assessment. A common requirement is to retrieve the exact fee retention schedule that was in effect for a given fee type and category on a specific past date, which is essential for recalculating charges for a student who withdrew in a prior term.

• Sample Query to Find Historical Schedule:
  SELECT * FROM igs.igs_fi_f_ret_schd_ht_all
WHERE fee_cal_type = 'TERM'
AND fee_ci_sequence_number = 202301
AND fee_type = 'TUITION'
AND fee_cat = 'FULLTIME'
AND hist_start_dt <= TO_DATE('15-MAR-2023', 'DD-MON-YYYY')
ORDER BY hist_start_dt DESC;
• Reporting Use Case: Generating a report showing all changes made to a particular fee retention schedule, including the old and new retention percentages and the effective dates of change, for regulatory audit trails.

Related Objects

The table maintains strict referential integrity with several foundational fee setup tables in the IGS module, as documented by its foreign key constraints. These relationships ensure that historical records are always linked to valid master data.

• IGS_FI_F_CAT_CA_INST: Validates the combination of FEE_CAT, FEE_CAL_TYPE, and FEE_CI_SEQUENCE_NUMBER. This links the history to the valid fee category for a calendar instance.
• IGS_FI_F_CAT_FEE_LBL_ALL: Also validates the FEE_CAT, FEE_CAL_TYPE, FEE_CI_SEQUENCE_NUMBER, and FEE_TYPE combination, linking to the fee label definitions.
• IGS_FI_F_TYP_CA_INST_ALL: Validates the combination of FEE_TYPE, FEE_CAL_TYPE, and FEE_CI_SEQUENCE_NUMBER. This links the history to the valid fee type for a calendar instance.