Results for “igs_ad_appl_hist_u1”

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

Overview

IGS.IGS_AD_APPL_HIST_ALL is a transactional history table within the Oracle E-Business Suite Student System (IGS) schema. It records the chronological evolution of an admission application, capturing each state or attribute change as a dated history row. The table is owned by the IGS schema, is documented as VALID, and resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. Its FND Design Data reference is IGS.IGS_AD_APPL_HIST_ALL, confirming it is a registered, supported object in the EBS 12.1.1 and 12.2.2 data model.

Functionally, the object supports audit and temporal reporting of admissions: rather than overwriting an application record, the system retains dated snapshots keyed by person and application number. This preserves the effective period of each change through HIST_START_DT and HIST_END_DT. From a Data Vault modeling perspective, the heuristic classification provided is standalone, meaning the object does not exhibit mined foreign-key dependencies on other tables. As a modeling suggestion, it may be treated as a satellite-style history entity attached to an application business key rather than as a link or a hub.

Key Information Stored

The primary key IGS_AD_APPL_HIST_ALL_PK is composed of PERSON_ID, ADMISSION_APPL_NUMBER, and HIST_START_DT. The unique index IGS_AD_APPL_HIST_U1, stored in APPS_TS_TX_IDX, covers the same three columns (PERSON_ID, ADMISSION_APPL_NUMBER, HIST_START_DT) and therefore represents the business-key candidate for this history object. The most significant columns are:

• PERSON_ID — the identifier of the person to whom the admission application belongs.
• ADMISSION_APPL_NUMBER — uniquely identifies an admission application for a person.
• HIST_START_DT / HIST_END_DT — the effective start and end date or time of the history record.
• HIST_WHO — the Oracle username of the person who created the history record.
• ORG_ID — the operating unit identifier, supporting multi-org security.
• APPL_DT — the date the admission application was made.
• ACAD_CAL_TYPE and ACAD_CI_SEQUENCE_NUMBER — the academic calendar type and sequence number the application applies to.
• ADM_CAL_TYPE and ADM_CI_SEQUENCE_NUMBER — the admission calendar type and sequence number.
• ADMISSION_CAT — the institution-defined admission category.
• S_ADMISSION_PROCESS_TYPE — the system-defined admission process type.
• ADM_APPL_STATUS and ADM_FEE_STATUS — the institution-defined application and fee statuses.
• TAC_APPL_IND — indicates whether a TAC application condition applies.

Standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) are also present, drawing a clear distinction between row creation metadata and the historical effective dating held in HIST_START_DT and HIST_END_DT.

Common Use Cases and Queries

Typical uses include reconstructing the state of an application at a point in time, auditing status transitions, and reporting admission funnel changes. A point-in-time query filters on the effective window:

• SELECT * FROM igs.igs_ad_appl_hist_all h WHERE h.person_id = :p AND h.admission_appl_number = :n AND :asof BETWEEN h.hist_start_dt AND NVL(h.hist_end_dt, :asof);
• Change tracking: order rows by HIST_START_DT to sequence ADM_APPL_STATUS or ADM_FEE_STATUS transitions per application.
• Audit reporting: join HIST_WHO to FND_USER to identify who made each change.
• Multi-org reporting: constrain by ORG_ID to respect operating unit security.

Related Objects

The documented relationship data classifies this object as standalone, so no mined foreign keys are available. In practice it participates in the admissions model through shared business keys rather than enforced constraints. The most significant associated objects include the current application base table (joined on PERSON_ID and ADMISSION_APPL_NUMBER), the person records in the IGS/HR person model via PERSON_ID, FND_USER via HIST_WHO, and the academic and admission calendar validation tables referenced by ACAD_CAL_TYPE and ADM_CAL_TYPE. Reporting views over IGS_AD_APPL_HIST_ALL typically rejoin these validation sets to resolve descriptive names for status, category, and process type codes.