Search Results igs_fi_tp_ret_schd_pk

Overview

IGS_FI_TP_RET_SCHD is a legacy Oracle EBS table belonging to the IGS (Student System) product family, now classified as obsolete. Its documented purpose is to store the Retention Schedule for the Teaching Period, defining how fees are retained against a student's teaching period and fee calendar enrolment. The table sits at the intersection of teaching calendar instances and fee calendar instances, expressing which portion of a fee is retained under a given retention policy.

Under a heuristic Data Vault classification derived from the foreign-key structure, IGS_FI_TP_RET_SCHD models as a link. Its foreign keys resolve to two dimension-style parents — IGS_FI_F_TYP_CA_INST_ALL (fee type calendar instance) and IGS_CA_DA_INST (calendar instance alias instance) — so the table principally records relationships between existing calendar and fee entities while carrying retention measures. This is a modeling suggestion only; the physical object remains a conventional relational table with a surrogate primary key.

The object is not implemented in the current database per the ETRM documentation, and the product is marked obsolete, so it should be treated as reference metadata for historical upgrades, data migration, or schema archaeology rather than as an active integration point in Oracle EBS 12.1.1 or 12.2.2.

Key Information Stored

The table carries 19 documented columns. The most significant are:

• FTCI_TEACH_RETENTION_ID — the surrogate primary key defined by index IGS_FI_TP_RET_SCHD_PK. This is the unique row identifier.
• TEACH_CAL_TYPE and TEACH_CI_SEQUENCE_NUMBER — identify the teaching calendar type and its calendar instance sequence number to which the retention schedule applies.
• FEE_CAL_TYPE and FEE_CI_SEQUENCE_NUMBER — identify the fee calendar type and fee calendar instance sequence number.
• FEE_TYPE — the fee type for which retention is defined; joins to IGS_FI_F_TYP_CA_INST_ALL.
• DT_ALIAS and DAI_SEQUENCE_NUMBER — the date alias and its instance sequence number, joining to IGS_CA_DA_INST and establishing the effective date context of the retention rule.
• RET_PERCENTAGE and RET_AMOUNT — the retention measures, expressed as a percentage and an absolute amount respectively.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE, providing standard EBS WHO-column and concurrent program traceability.

The unique business-key index IGS_FI_TP_RET_SCHD_U1 spans TEACH_CAL_TYPE, TEACH_CI_SEQUENCE_NUMBER, FEE_CAL_TYPE, FEE_CI_SEQUENCE_NUMBER, FEE_TYPE, DT_ALIAS, and DAI_SEQUENCE_NUMBER — meaning the retention rule is uniquely defined per teaching instance, fee instance, fee type, and date alias combination. The surrogate key remains FTCI_TEACH_RETENTION_ID.

Common Use Cases and Queries

Typical reporting scenarios involve reconciling retention percentages and amounts against fee calendar instances and teaching periods, supporting refund and withdrawal calculations, and auditing fee retention configuration.

SELECT r.ftci_teach_retention_id,
       r.teach_cal_type,
       r.fee_type,
       r.dt_alias,
       r.ret_percentage,
       r.ret_amount
FROM   igs_fi_tp_ret_schd r
WHERE  r.teach_cal_type = :p_cal_type
AND    r.teach_ci_sequence_number = :p_ci_seq;

A join-driven query resolves the fee type and date alias context:

SELECT r.*, f.fee_type, d.dt_alias
FROM   igs_fi_tp_ret_schd r,
       igs_fi_f_typ_ca_inst_all f,
       igs_ca_da_inst d
WHERE  r.fee_type  = f.fee_type
AND    r.fee_cal_type = f.fee_cal_type
AND    r.fee_ci_sequence_number = f.fee_ci_sequence_number
AND    r.dt_alias  = d.dt_alias
AND    r.dai_sequence_number = d.dai_sequence_number;

Related Objects

The most significant related objects, grounded in the documented foreign-key relationships, are:

• IGS_FI_F_TYP_CA_INST_ALL — referenced by FEE_TYPE, FEE_CAL_TYPE, and FEE_CI_SEQUENCE_NUMBER; supplies fee type calendar instance detail.
• IGS_CA_DA_INST — referenced by DT_ALIAS and DAI_SEQUENCE_NUMBER; supplies the date alias instance context.
• IGS_FI_TP_RET_SCHD_PK and IGS_FI_TP_RET_SCHD_U1 — the primary and unique indexes enforcing identity and business uniqueness.
• Teaching calendar instance objects — the TEACH_CAL_TYPE and TEACH_CI_SEQUENCE_NUMBER columns logically relate to teaching calendar instance tables used throughout the IGS Student System, though not all are explicitly documented as foreign keys here.

Because the product is obsolete and the table is not implemented in the current database, related objects should be validated against the specific EBS 12.1.1 or 12.2.2 instance before use in any live reporting or integration.