Results for “adm_ps_appl_inst_unit_hist_id”

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

Overview

IGS_AD_PS_APINTUNTHS_ALL is an Oracle E-Business Suite table belonging to the IGS (Student System) product family. In the 12.1.1 and 12.2.2 releases documented by ETRM, the IGS module is flagged as obsolete, and this specific object is recorded as "not implemented in this database." Its stated purpose is to describe the history of changes to an admission program application instance unit, that is, the chronological record of how a prospective student's unit-level admission data has evolved over time.

Functionally, the table acts as an audit/versioning companion to the live admission instance unit record. Each row captures a snapshot of an admission instance unit as it existed during a defined historical interval, bounded by HIST_START_DT and HIST_END_DT. This pattern is characteristic of Oracle's date-tracked reference and transactional history tables, where the current state lives in a base table and prior states are retained here.

From a Data Vault modeling perspective, the ETRM relationship analysis suggests a satellite-leaning classification. The presence of a dedicated history identifier, start/end date columns, and a foreign key to a parent instance unit entity is consistent with the structure of a satellite attached to an admission instance unit hub, recording attribute history rather than representing an independent business entity. This classification is offered as a heuristic modeling suggestion, not a documented Oracle designation.

Key Information Stored

The physical schema documents 28 columns. The most significant are summarized below, distinguishing surrogate keys from business-key candidates.

• ADM_PS_APPL_INST_UNIT_HIST_ID — the surrogate primary key defined by IGS_AD_PS_APINTUNTHS_PK. It uniquely identifies each historical version row and is the column most commonly referenced by the user search term.
• ADM_PS_APPL_INST_UNIT_ID — foreign key to IGS_AD_PS_APLINSTUNT_ALL, linking each history row to its parent admission instance unit.
• HIST_START_DT and HIST_END_DT — the effective date boundaries of the historical snapshot, with HIST_WHO identifying the actor responsible for the change.
• PERSON_ID — the applicant or person associated with the admission instance unit; joins to HZ_PARTIES-adjacent person data.
• ADMISSION_APPL_NUMBER, NOMINATED_COURSE_CD, ACAI_SEQUENCE_NUMBER — admission application identifiers and nominated course context.
• UNIT_CD, UV_VERSION_NUMBER, CAL_TYPE, CI_SEQUENCE_NUMBER, LOCATION_CD, UNIT_CLASS — the unit-level academic attributes tracked across changes.
• ADM_UNIT_OUTCOME_STATUS, UNIT_MODE, ASS_TRACKING_ID — outcome, mode, and assessment tracking identifiers.
• RULE_WAIVED_PERSON_ID and RULE_WAIVED_DT — the person and date associated with a waived admission rule; RULE_WAIVED_PERSON_ID is a foreign key to HZ_PARTIES.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle WHO audit columns.

Business-key candidates are defined by two unique indexes. IGS_AD_PS_APINTUNTHS_ALL_U1 spans PERSON_ID, ADMISSION_APPL_NUMBER, NOMINATED_COURSE_CD, ACAI_SEQUENCE_NUMBER, UNIT_CD, UV_VERSION_NUMBER, CAL_TYPE, CI_SEQUENCE_NUMBER, LOCATION_CD, UNIT_CLASS, and HIST_START_DT. The documentation additionally notes a unique key (UK2) over ADM_PS_APPL_INST_UNIT_ID and HIST_START_DT, reinforcing the one-snapshot-per-unit-per-date rule.

Common Use Cases and Queries

The primary use case is historical traceability: reconstructing the state of an admission instance unit as of any point in time, auditing who changed a unit attribute, or reporting on unit outcome transitions. Typical query patterns join the history table to the parent unit table on ADM_PS_APPL_INST_UNIT_ID and filter on the effective date range.

• Point-in-time reconstruction: SELECT * FROM IGS_AD_PS_APINTUNTHS_ALL WHERE ADM_PS_APPL_INST_UNIT_ID = :unit_id AND :as_of_dt BETWEEN HIST_START_DT AND NVL(HIST_END_DT, :as_of_dt)
• Change audit for an applicant: filter on PERSON_ID and ADMISSION_APPL_NUMBER, ordering by HIST_START_DT to trace the sequence of edits.
• Outcome reporting: group by ADM_UNIT_OUTCOME_STATUS and CAL_TYPE to analyze admission outcomes over an intake cycle.
• Waiver analysis: join RULE_WAIVED_PERSON_ID to HZ_PARTIES to identify who authorized rule waivers.

Related Objects

• IGS_AD_PS_APLINSTUNT_ALL — parent admission instance unit table, joined via ADM_PS_APPL_INST_UNIT_ID.
• HZ_PARTIES — joined via RULE_WAIVED_PERSON_ID to resolve the person who waived a rule.
• HZ_PERSON_PROFILES / PER_ALL_PEOPLE_F — commonly joined via PERSON_ID for applicant details.
• IGS_AD_PS_APINTUNTHS_ALL_PK and the associated unique indexes U1/UK2 — key constraints enforcing row uniqueness.
• IGS Admission APIs — the base instance unit maintenance APIs that populate this history table.