Search Results effective_term_sequence_num




Overview
Explore in AI Chat

Overview

IGS.IGS_PS_STDNT_TRN is a Student System (IGS) transactional table that records the link between two academic programs involved in a student program transfer. Each row captures the fact that a student's program attempt was moved from an originating program (TRANSFER_COURSE_CD) to the currently enrolled program (COURSE_CD), together with the effective term in which that transfer applied. In Oracle EBS 12.1.1 and 12.2.2, this table supports admissions and student records processing for institutions running the Student System, particularly the program transfer and advanced standing workflows.

From a Data Vault modeling perspective, the mined heuristic classifies this object as satellite-leaning, meaning it is best understood as descriptive context attached to a student/program business key rather than an independent hub. The migration key is the composite primary key IGS_PS_STDNT_TRN_PK on (PERSON_ID, COURSE_CD, TRANSFER_COURSE_CD, TRANSFER_DT), which combines the person, destination program, source program, and transfer date. The table resides in the APPS_TS_TX_DATA tablespace with PCT Free 10, and its indexes are held in APPS_TS_TX_IDX.

Key Information Stored

The columns below represent the most significant data held in this table; the object has 19 documented columns in total.

  • PERSON_ID (NUMBER, 15) — The person uniquely identified as the transferring student. This is part of the composite primary key and the unique business key.
  • COURSE_CD (VARCHAR2) — The program code in which the student is enrolled (the transfer destination). Part of the composite PK.
  • TRANSFER_COURSE_CD (VARCHAR2) — The program code the student program attempt was transferred from. Part of the composite PK.
  • TRANSFER_DT (DATE) — The date the student program attempt record transfer was processed. Part of the composite PK.
  • EFFECTIVE_TERM_CAL_TYPE (VARCHAR2, 10) — The calendar type of the effective term for the transfer.
  • EFFECTIVE_TERM_SEQUENCE_NUM (NUMBER) — The sequence number of the effective term; together with EFFECTIVE_TERM_CAL_TYPE it identifies the academic term in which the transfer takes effect.
  • APPROVED_DATE (DATE) — The date the transfer was approved.
  • DISCONTINUE_SOURCE_FLAG (VARCHAR2) — Indicates whether the source program should be discontinued after the transfer.
  • TRANSFER_ADV_STAND_FLAG (VARCHAR2) — Indicates whether advanced standing should be transferred for a future-dated transfer.
  • COMMENTS (VARCHAR2, 2000) — Free-text comments about the link between the transfer-to and transfer-from programs.
  • STATUS_FLAG / STATUS_DATE — Status indicator and date for the transfer record.
  • UOOIDS_TO_TRANSFER / SUSA_TO_TRANSFER — Delimited lists of unit-of-study and advanced standing identifiers carried across during the transfer.
  • Standard WHO columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

The unique index IGS_PS_STDNT_TRN_U1 on (PERSON_ID, COURSE_CD, TRANSFER_COURSE_CD, TRANSFER_DT) is the business-key candidate. The non-unique index IGS_PS_STDNT_TRN_N3 on (EFFECTIVE_TERM_CAL_TYPE, EFFECTIVE_TERM_SEQUENCE_NUM) supports term-based lookups — relevant to the reported search term.

Common Use Cases and Queries

Typical reporting scenarios include identifying all program transfers for a cohort, reconciling advanced standing carried into a destination program, and listing transfers effective within a given academic term.

  • Term-specific transfer reporting:
SELECT person_id, course_cd, transfer_course_cd, transfer_dt
FROM   igs.igs_ps_stdnt_trn
WHERE  effective_term_cal_type = :cal_type
AND    effective_term_sequence_num = :seq_num;
  • Advanced standing transfers not yet approved, filtered on TRANSFER_ADV_STAND_FLAG and APPROVED_DATE.
  • Detecting whether the source program is discontinued after transfer via DISCONTINUE_SOURCE_FLAG.

Related Objects

  • IGS.IGS_EN_STDNT_PS_ATT_ALL — referenced via PERSON_ID; the student program attempt that is transferred.
  • IGS.IGS_CA_INST_ALL — referenced via EFFECTIVE_TERM_CAL_TYPE; resolves the effective term instance.
  • IGS.IGS_PS_STDNT_UNT_TRN — references this table via PERSON_ID; holds unit-level transfer detail.