Results for “igs_ca_inst_all_u3”

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

Overview

IGS.IGS_CA_INST_ALL is the core calendar instance table within the Oracle E-Business Suite Student System (IGS) schema. It describes an instance of a calendar type — for example a specific academic term, admission period, or fee calendar — capturing the effective date range, status, and alternate coding used throughout the institution's academic and financial processing. In EBS 12.1.1 and 12.2.2 the table resides in the APPS_TS_TX_DATA tablespace with a PCT FREE of 10, and its indexes are held in APPS_TS_TX_IDX. The table carries an ORG_ID column and the standard WHO audit columns, indicating it participates in Multi-Org and standard audit conventions. The object is registered as FND Design Data under IGS.IGS_CA_INST_ALL, and the metadata records that the table was modified to capture the enrollment method type related to self service. From a Data Vault modeling perspective, the mined FK structure suggests this object behaves as a hub: it holds a stable business key (calendar type and sequence) that numerous other tables reference, with descriptive attributes attached.

Key Information Stored

The documented physical schema exposes 21 columns. The most significant are:

• CAL_TYPE (VARCHAR2 10) — the name of the calendar type used within the organization; part of every business key candidate.
• SEQUENCE_NUMBER (NUMBER) — the unique identifier for the calendar instance within its type.
• START_DT and END_DT (DATE) — the effective window during which the calendar instance is valid; for example, Semester 1 1999 may run from 1/3/1999 to 11/6/1999.
• CAL_STATUS (VARCHAR2 10) — the calendar instance status, mapped to one of the system statuses ACTIVE, INACTIVE, or PLANNED.
• ALTERNATE_CODE (VARCHAR2 10) — a short reference code mandatory for academic, admission progression, and teaching calendars, used for mass keying of enrolments, unit and course offerings, and displayed on forms and reports.
• SUP_CAL_STATUS_DIFFER_IND — system-set indicator showing whether this instance's status differs from any superior calendar instance.
• PRIOR_CI_SEQUENCE_NUMBER — the prior calendar instance sequence, used for rollover and progression logic.
• DESCRIPTION, SS_DISPLAYED, IVR_DISPLAY_IND — self-service display flags and descriptive text.
• TERM_INSTRUCTION_TIME, PLANNING_FLAG, SCHEDULE_FLAG, ADMIN_FLAG — instructional and functional flags controlling term behavior.
• ORG_ID and the WHO audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) — Multi-Org and audit tracking.

The primary key is documented as CI_UK1 (CAL_TYPE, SEQUENCE_NUMBER, START_DT, END_DT). Two unique indexes act as business-key candidates: IGS_CA_INST_ALL_U1 on (CAL_TYPE, SEQUENCE_NUMBER) and IGS_CA_INST_ALL_U3 on (CAL_TYPE, START_DT, END_DT) — the latter being the index the user searched for. Non-unique indexes IGS_CA_INST_ALL_N1 (CAL_STATUS), _N2 (CAL_TYPE, PRIOR_CI_SEQUENCE_NUMBER), and _N3 (ALTERNATE_CODE) support common filter paths.

Common Use Cases and Queries

Typical reporting resolves the currently active term for a given calendar type, joins calendar instances to offerings or enrolments, and validates date ranges for fee or admission processing.

• Active term lookup: SELECT CAL_TYPE, SEQUENCE_NUMBER, START_DT, END_DT, ALTERNATE_CODE FROM IGS_CA_INST_ALL WHERE CAL_STATUS = 'ACTIVE' AND SYSDATE BETWEEN START_DT AND END_DT;
• Term by alternate code: query on ALTERNATE_CODE to drive mass enrolment keying.
• Progression chain: join on CAL_TYPE and PRIOR_CI_SEQUENCE_NUMBER to trace rollover from one term to the next.
• Self-service display: filter on SS_DISPLAYED to expose only terms visible in student self service.

Because the _U3 index covers CAL_TYPE, START_DT, and END_DT, range predicates on the effective dates perform efficiently.

Related Objects

IGS_CA_INST_ALL is heavily referenced across the Student System and Financial Aid schemas. Significant referencing objects include:

• IGS_CA_INST_REL — joins on both SUP_CAL_TYPE and SUB_CAL_TYPE, defining superior/subordinate calendar relationships.
• IGS_PS_OFR_INST — the program offering instance, joined on CAL_TYPE.
• IGS_EN_SU_ATTEMPT_ALL — student unit attempts, joined on CAL_TYPE.
• IGS_AS_EXAM_SESSION_ALL — exam sessions, joined on EXAM_CAL_TYPE.
• IGS_FI_F_CAT_CA_INST and IGS_FI_BILL_PLN_CRD — fee calendars, joined on FEE_CAL_TYPE.
• IGS_AD_PS_APPL_INST_ALL — admission application instances, joined on ADM_CAL_TYPE and FUTURE_ACAD_CAL_TYPE.
• IGS_PE_TEACH_PERIODS_ALL — teaching periods, joined on CAL_TYPE.
• IGF_AW_AWD_DISB_ALL and other Financial Aid (IGF) tables — joined on CI_CAL_TYPE, LD_CAL_TYPE, or TP_CAL_TYPE.
• IGS_CA_STAT — the referenced status table for CAL_STATUS.

These relationships confirm the table's role as the central calendar hub referenced by enrolment, admission, fees, teaching, and financial aid processing throughout Oracle EBS Student System.