Results for “igs_ad_ps_appl_inst_all”

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

Overview

IGS_AD_PS_APPL_INST_ALL is a table in the Oracle E-Business Suite IGS — Student System module, which Oracle has classified as obsolete in current EBS releases (12.1.1 and 12.2.2). The table stores the request for program entry for an admission period — essentially the institution-level application instance record that captures an applicant's intention to be admitted to a specific program of study for a specific admission period. Each row represents one program-of-study choice attached to an admission application.

The table is owned by the IGS schema and contains 141 columns, making it one of the broader admissions staging entities in the legacy Student System schema. The primary key, IGS_AD_PS_APPL_INST_PK, is composed of (PERSON_ID, ADMISSION_APPL_NUMBER, NOMINATED_COURSE_CD, SEQUENCE_NUMBER), which uniquely identifies a single program-application-instance row.

From a Data Vault modelling perspective, the FK structure suggests a satellite-leaning classification. The table appears to enrich an existing hub/link structure (parties, applications, calendar instances) with descriptive attributes — statuses, decisions, offer details, deferred terms, and financial categories. This means it is best understood as a descriptive extension around the applicant and admission-application spine rather than a canonical hub.

Key Information Stored

Although 141 columns are documented, the following are the most operationally significant:

• Business key columns (unique index): PERSON_ID, ADMISSION_APPL_NUMBER, NOMINATED_COURSE_CD, SEQUENCE_NUMBER — together forming the primary key IGS_AD_PS_APPL_INST_PK. The metadata does not expose a separate surrogate column, so the composite key functions as the identifier.
• ORG_ID — multi-org partitioning marker.
• ADM_CAL_TYPE / ADM_CI_SEQUENCE_NUMBER — admission calendar and instance references (FK to IGS_CA_INST_ALL).
• COURSE_CD / CRV_VERSION_NUMBER — programme/course reference and curriculum version.
• LOCATION_CD — teaching location of the nominated programme.
• ATTENDANCE_MODE / ATTENDANCE_TYPE — study mode and type (FK to IGS_EN_ATD_MODE_ALL and IGS_EN_ATD_TYPE_ALL).
• APPL_INST_STATUS / AIS_REASON — the overriding application-instance status and reason code.
• ADM_OUTCOME_STATUS and its authorising attributes (ADM_OTCM_STATUS_AUTH_PERSON_ID, ADM_OUTCOME_STATUS_AUTH_DT, ADM_OUTCOME_STATUS_REASON) — the admission decision outcome.
• ADM_OFFER_RESP_STATUS / ADM_CNDTNL_OFFER_STATUS / ADM_OFFER_DFRMNT_STATUS — offer, conditional-offer, and deferment statuses.
• DECISION_DATE / DECISION_NOTES / DECISION_MAKE_ID / DECISION_REASON_ID — decision audit fields.
• FEE_CAT / FUNDING_SOURCE / HECS_PAYMENT_OPTION / ENROLMENT_CAT — financial and enrolment classification.
• FUTURE_ACAD_CAL_TYPE / FUTURE_ADM_CAL_TYPE and their CI_SEQUENCE siblings — deferred/future term references (four separate FKs to IGS_CA_INST_ALL).

Common Use Cases and Queries

In legacy reporting and admissions data marts, this table is used to reconstruct the applicant decision funnel and pipeline. Typical queries include:

• Programme application listing by applicant: joining PERSON_ID back to IGS_AD_APPL_ALL or IGS_AD_PS_APPL_ALL to retrieve applicant context while selecting all programme choices.
• Offer status pipeline: summarising counts by APPL_INST_STATUS, ADM_OUTCOME_STATUS, and ADM_OFFER_RESP_STATUS grouped by ADM_CAL_TYPE to build admissions dashboards.
• Deferred-entry tracking: filtering on DEFERRED_ADM_CAL_TYPE and FUTURE_ADM_CI_SEQUENCE_NUMBER to list applicants admitted to a later term.
• Conditional offer compliance: selecting rows where ADM_CNDTNL_OFFER_STATUS is unresolved and CNDTNL_OFFER_MUST_BE_STSFD_IND = 'Y'.

A representative pattern:

SELECT a.person_id, a.admission_appl_number, a.nominated_course_cd,
       a.sequence_number, a.appl_inst_status, a.adm_outcome_status,
       a.offer_dt, a.adm_offer_resp_status
FROM   igs_ad_ps_appl_inst_all a
WHERE  a.adm_cal_type = :p_cal_type
AND    a.adm_ci_sequence_number = :p_ci_seq
AND    a.appl_inst_status IN ('A','P');

Because the table is obsolete, the practical usage pattern in 12.1.1/12.2.2 environments is limited to historical data extraction and migration away from Student System modules.

Related Objects

The following referenced objects are the most material to understanding this table:

• IGS_AD_APPL_ALL and IGS_AD_PS_APPL_ALL — parent application tables joined on (PERSON_ID, ADMISSION_APPL_NUMBER).
• IGS_AD_PS_APPL_ALL — also joined on NOMINATED_COURSE_CD for the programme-choice relationship.
• IGS_CA_INST_ALL — referenced four times (ADM_CAL_TYPE, DEFERRED_ADM_CAL_TYPE, FUTURE_ACAD_CAL_TYPE, FUTURE_ADM_CAL_TYPE) to resolve calendar instances.
• IGS_AD_APPL_STAT — supplies APPL_INST_STATUS decode.
• IGS_AD_APLINS_ADMREQ, IGS_AD_APPL_EVAL, IGS_AD_APPL_NOTES, IGS_AD_APPL_PGMAPPRV — child tables keyed by PERSON_ID, carrying admission requirements, evaluations, notes, and program approvals.
• IGS_RE_CANDIDATURE_ALL — downstream candidature record for successful applicants.
• HZ_PARTIES — referenced via ADM_OTCM_STATUS_AUTH_PERSON_ID for the authorising individual.

Together these relationships position IGS_AD_PS_APPL_INST_ALL as a central descriptive record between the applicant/application spine and the downstream enrolment and candidature lifecycle, even though the table itself is no longer actively implemented in supported EBS configurations.