Search Results ret_percentage

Overview

The IGS.IGS_FI_TP_RET_SCHD table, titled "Retention Schedule for the Teaching Period," is a Student Systems foundation table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments. It is owned by the IGS schema (the Student Information Systems product family) and stores the retention rules that govern how much of a fee or charge is retained by the institution when a student withdraws or changes status partway through a teaching period. Because tuition and fee refunds are schedule-driven, this table provides the effective-date mapping between a teaching period, a fee category, and the percentage or monetary amount that the institution keeps.

From a dimensional modeling perspective, the metadata's heuristic Data Vault classification is a link table. This is appropriate: the table's unique business key (IGS_FI_TP_RET_SCHD_U1) unites a teaching calendar instance, a fee calendar instance, a fee type, and a date alias into a many-to-many association, while RET_PERCENTAGE and RET_AMOUNT are descriptive, time-stamped measures carried on the relationship itself.

Key Information Stored

The surrogate primary key is FTCI_TEACH_RETENTION_ID, a system-generated sequence value surfaced through the IGS_FI_TP_RET_SCHD_PK unique index. The user-searched column RET_AMOUNT is a NUMBER holding the fixed monetary value retained for the matching teaching period and fee combination; it works in tandem with RET_PERCENTAGE, which expresses the same retention rule as a proportion of the base fee. Exactly one of these is typically populated depending on whether the retention policy is expressed as a flat amount or a percentage.

The uniqueness constraint IGS_FI_TP_RET_SCHD_U1 spans seven columns and therefore constitutes the de facto business key: TEACH_CAL_TYPE and TEACH_CI_SEQUENCE_NUMBER identify the teaching calendar and its instance; FEE_CAL_TYPE and FEE_CI_SEQUENCE_NUMBER identify the fee calendar and instance; FEE_TYPE identifies the specific fee (for example tuition or a material charge); and DT_ALIAS with DAI_SEQUENCE_NUMBER pin the retention point to a teaching-period date alias instance. The remaining columns are standard WHO audit attributes (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and Concurrent Manager WHO columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE).

Common Use Cases and Queries

Retention logic is consumed during withdrawal processing, refund calculation, and fee reassessment. A typical retrieval for a given teaching period and fee type joins the teaching calendar instance and fee calendar instance to return the governing schedule rows:

• Retention lookup for a withdrawal: SELECT ret_percentage, ret_amount FROM igs_fi_tp_ret_schd WHERE teach_cal_type = :p_tcal AND teach_ci_sequence_number = :p_tci AND fee_type = :p_fee AND dt_alias = :p_alias;
• Reporting retention policy by amount versus percentage: filter on RET_AMOUNT IS NOT NULL to isolate flat-amount retention rules, or on RET_PERCENTAGE IS NOT NULL for proportional rules, grouped by FEE_TYPE and TEACH_CAL_TYPE.
• Audit and lineage: retrieve CREATION_DATE, LAST_UPDATE_DATE, and PROGRAM_UPDATE_DATE to trace when a retention schedule row was loaded or amended, and to identify the concurrent program responsible for scheduled fee recalculation.

Related Objects

The table participates in the following documented foreign-key and dependency relationships:

• IGS.IGS_FI_F_TYP_CA_INST_ALL — referenced via FEE_TYPE; defines the valid fee type against which retention rules are applied.
• IGS.IGS_CA_DA_INST — referenced via DT_ALIAS; resolves the teaching-period date alias instance that anchors the retention point.
• Teaching and fee calendar instance objects — keyed by TEACH_CAL_TYPE/TEACH_CI_SEQUENCE_NUMBER and FEE_CAL_TYPE/FEE_CI_SEQUENCE_NUMBER, providing the calendar context.
• Fee assessment and refund logic — downstream EBS processes that read RET_PERCENTAGE and RET_AMOUNT to compute amounts retained on student withdrawal.

Because the table is Data Vault-link in nature, these relationships should be modeled as a composite hub linkage over the teaching calendar, fee calendar, fee type, and date alias dimensions, with the retention measures treated as link satellites.