Search Results audp_get_aah_col

Overview

The view APPS.IGS_AD_APPL_HIST_APHSTRY_V is a reporting and integration object within the Oracle E-Business Suite Student System (IGS) product family. It exposes the historical audit trail of admission applications, presenting one row for each recorded change captured against an applicant's admission application. The name follows the IGS naming convention, where the _V suffix denotes a view and the HIST / APHSTRY segments indicate an application history source.

Its primary role is to give downstream reports and interfaces a denormalised, point-in-time picture of an application — including the admission application status (the ADM_APPL_STATUS column most commonly associated with the adm_appl_status search term), fee status, admission category and process type — alongside the validity window (HIST_START_DT, HIST_END_DT) during which each historical value applied. This makes it suitable for auditing status changes over the application lifecycle rather than simply reading the current application record.

Underlying Base Objects

Although the documented ETRM metadata lists no formally catalogued base objects for this view, the view definition reveals its underlying sources directly. The FROM clause references:

• IGS_AD_APPL_HIST (aliased AAH1) — the driving history table holding the versioned application column values.
• IGS_AD_APPL (aliased AA1) — the current admission application record, used as the fallback for values not yet versioned in history.
• IGS_CA_INST (aliased CI1 and CI2) — calendar instance definitions, joined on CAL_TYPE and SEQUENCE_NUMBER.
• IGS_PE_PERSON_BASE_V (aliased PE) — the person base view supplying PERSON_NUMBER.

The joins link history to current application by PERSON_ID and ADMISSION_APPL_NUMBER, and to the person by PERSON_ID. A distinctive feature is the pervasive use of the audit helper package IGS_AU_GEN_001.AUDP_GET_AAH_COL, which retrieves the historical value of a named column; each attribute is resolved through nested NVL logic — first the history row value, then the audited historical lookup, then the current application value.

Key Columns

• ROW_ID — unique identifier for the history record.
• PERSON_ID, PERSON_NUMBER — applicant identity and human-readable person number.
• ADMISSION_APPL_NUMBER — the admission application reference.
• HIST_START_DT, HIST_END_DT, HIST_WHO — the effective window and actor for the historical version.
• APPL_DT — application date, resolved through the audit lookup when not directly stored.
• ADM_APPL_STATUS — the admission application status, the column of principal interest for adm_appl_status enquiries.
• ADM_FEE_STATUS — fee status of the application.
• ADMISSION_CAT, S_ADMISSION_PROCESS_TYPE, TAC_APPL_IND — admission category, process type and TAC indicator.
• ALTERNATE_CODE, CAL_TYPE, SEQUENCE_NUMBER (from CI1/CI2) — calendar instance details.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.

Common Use Cases and Queries

Typical uses include status-change audits, trend reporting across admission cycles, and feeding student-records interfaces with historical application context.

To trace the status history for a specific applicant:

• SELECT person_number, admission_appl_number, adm_appl_status, hist_start_dt, hist_end_dt, hist_who FROM igs_ad_appl_hist_aphstry_v WHERE admission_appl_number = :p_appl ORDER BY hist_start_dt;

To count applications by current status end-date:

• SELECT adm_appl_status, COUNT(*) FROM igs_ad_appl_hist_aphstry_v WHERE hist_end_dt IS NULL GROUP BY adm_appl_status;

Because values are resolved through the audit lookup, callers should expect the view to return the effective value at each history point, and should always constrain by person or application to avoid full scans of the history table.