Results for “igs_ad_ps_appl_inst_aplinst_v”

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

Overview

IGS_AD_PS_APPL_INST_APLINST_V is a read-only database view owned by the APPS schema within the Oracle E-Business Suite environment. It belongs to the IGS (Student System) product family and is classified as VALID in both Oracle EBS 12.1.1 and 12.2.2 instances. The view is defined over IGS_AD_PS_APPL_INST and is designed to present admission application instance data in a denormalized, reporting-friendly form. Its central purpose is to expose applicant admission records together with resolved alternate code descriptions and calendar instance start and end dates for both the Admission calendar and the Deferred Admission calendar. It also surfaces Admission Course Application details, including course, version, location, attendance mode, unit set, and preference information.

Functionally, the view acts as a consolidation layer that shields downstream reports, extracts, and integration interfaces from the need to perform manual joins against calendar instance, party, and unit set tables. Because several of the columns returned are the result of scalar subqueries and NVL-based fallback logic, the view supplies a "best available" value for calendar identification—falling back to the application-level calendar when the instance-level alternate is null.

Underlying Base Objects

The ETRM metadata documents no formally registered base objects for this view, though the accompanying view text clearly references IGS_AD_PS_APPL_INST (aliased ACAI) as the driving table. Additional inline subqueries reference IGS_EN_UNIT_SET (aliased ESU) to retrieve the unit set title, and IGS_CA_INST (aliased CI and CI2) to resolve alternate codes for the deferred admission calendar and the deferred academic calendar respectively. The party number is sourced via a join to a party table (aliased P) and a second party alias (P1) resolves the admission outcome status authorized person number. Because the reference list is not catalogued in the ETRM metadata, dependencies should be confirmed against the actual database definition in each environment.

Key Columns

• ROW_ID, PERSON_ID, PERSON_NUMBER – Unique row identifier, internal party identifier, and the human-readable party number for the applicant.
• ADMISSION_APPL_NUMBER, SEQUENCE_NUMBER, PREFERENCE_NUMBER – The admission application number, its sequence, and the applicant's course preference ordering.
• ACAD_CAL_TYPE, ADM_CAL_TYPE, ADM_CI_SEQUENCE_NUMBER – Academic and admission calendar identifiers used to place the application in time.
• DEFERRED_ADM_CAL_TYPE, DEFERRED_ADM_CI_SEQUENCE_NUM, DEF_ACAD_CAL_TYPE – Deferred admission and deferred academic calendar references, with alternate codes resolved through IGS_CA_INST.
• COURSE_CD, CRV_VERSION_NUMBER, LOCATION_CD, ATTENDANCE_MODE, ATTENDANCE_TYPE – Admission course application attributes.
• UNIT_SET_CD, US_VERSION_NUMBER, UNIT_SET_TITLE – Unit set context and its resolved title.
• ADM_DOC_STATUS, ADM_ENTRY_QUAL_STATUS, LATE_ADM_FEE_STATUS, ADM_OUTCOME_STATUS – Status indicators tracking document, entry qualification, late fee, and admission outcome processing.
• OFFER_DT, OFFER_RESPONSE_DT, PRPSD_COMMENCEMENT_DT, ACTUAL_RESPONSE_DT – Offer, response, and proposed commencement dates.
• ADM_CNDTNL_OFFER_STATUS, CNDTNL_OFFER_CNDTN, CNDTNL_OFFER_SATISFIED_DT, CNDTNL_OFFER_MUST_BE_STSFD_IND – Conditional offer tracking fields.
• ADM_OFFER_RESP_STATUS, ADM_OFFER_DFRMNT_STATUS, APPLICANT_ACPTNCE_CNDTN – Offer response, deferment, and applicant acceptance attributes.

Common Use Cases and Queries

The view is typically used for applicant tracking reports, admission funnel analysis, deferred admission monitoring, and interfaces that feed external student recruitment systems. A representative query retrieves active applications with their resolved calendar and course details:

• SELECT PERSON_NUMBER, ADMISSION_APPL_NUMBER, COURSE_CD, ADM_CAL_TYPE, ADM_CI_SEQUENCE_NUMBER, UNIT_SET_TITLE, ADM_OUTCOME_STATUS FROM IGS_AD_PS_APPL_INST_APLINST_V WHERE ADM_DOC_STATUS = 'COMPLETE';
• Filtering on DEFERRED_ADM_CAL_TYPE IS NOT NULL isolates applicants who have deferred admission, supporting deferred intake reporting.
• Joining back to IGS_AD_PS_APPL_INST on ROW_ID reconciles the view output with the base application instance record.

Because the view embeds scalar subqueries, queries returning large volumes should be tuned with appropriate predicates on PERSON_ID or ADMISSION_APPL_NUMBER to avoid full scans of the calendar and unit set reference tables.