Search Results igs_co_gen_003

Overview

The view IGS_AD_OUTCOME_LETTERS_V belongs to the IGS — Student System product family within Oracle E-Business Suite, a module now flagged as obsolete in the ETRM 12.2.2 repository. Its documented purpose is narrowly scoped: it supplies the data set used to drive the validations that are applied to individual outcome letters, with those validations embedded at the SQL query level of the master document definition. In other words, rather than enforcing letter-level business rules through PL/SQL packages or form logic, the view exposes a denormalized row set that the master document consumes directly, filtering and validating at query execution time.

The view joins Oracle Trading Community Architecture (TCA) party and location data (the HZ aliases) with academic admissions and course structures (aliased ACAIV, PS, and OU), producing a consolidated record per applicant/student outcome. Because the ETRM entry records that the object is not implemented in the database, the view exists only as a candidate or legacy definition; administrators should confirm availability before referencing it in a 12.1.1 or 12.2.2 instance.

Underlying Base Objects

The ETRM metadata documents no referenced base objects, and the view owner is blank. This absence is significant: it indicates the view is not formally registered in the ETRM dependency map, so reverse-engineering must rely on the view text itself and on the alias prefixes. The query text references at minimum:

• HZ_* TCA party, party site, and location tables (aliased HZ), supplying name, address, and party classification attributes.
• An admissions/applicant instance table (aliased ACAIV), supplying preference number, location, attendance mode/type, admission outcome status, entry status, and scheduled application/school identifiers.
• A program/course structure (aliased PS), supplying course code, title, responsible org unit, course type, and start date.
• An organization unit source (aliased OU), supplying the responsible unit description via PARTY_NAME.

Because the metadata does not enumerate exact table names, the join columns cannot be confirmed from ETRM alone; the documented excerpt ends mid-list at SCHOOL_APP.

Key Columns

The projection is dominated by TCA party attributes. Notable columns include:

• Identity: PARTY_ID, PARTY_NAME, PARTY_NUMBER, PARTY_TYPE, CUSTOMER_KEY, ORIG_SYSTEM_REFERENCE.
• Person name parts: PERSON_FIRST_NAME, PERSON_LAST_NAME, PERSON_MIDDLE_NAME, PERSON_NAME_SUFFIX, PERSON_PRE_NAME_ADJUNCT, SALUTATION, plus phonetic variants.
• Address: ADDRESS1–ADDRESS4, CITY, STATE, PROVINCE, POSTAL_CODE, COUNTRY, COUNTY.
• Academic context: COURSE_CD, TITLE, COURSE_TYPE, RESPONSIBLE_ORG_UNIT_CD (with OU.PARTY_NAME as DESCRIPTION), ADM_OUTCOME_STATUS, ENTRY_STATUS, ENTRY_LEVEL and their _NAME/_DESC counterparts, ATTENDANCE_MODE, ATTENDANCE_TYPE, LOCATION_CD, PREFERENCE_NUMBER.
• Flags/miscellaneous: VALIDATED_FLAG, DO_NOT_MAIL_FLAG, THIRD_PARTY_FLAG, REFERENCE_USE_FLAG, EMAIL_ADDRESS, URL.
• Placeholder: TO_DATE(NULL) WH_UPDATE_DATE — a null-typed update date, likely a warehousing artifact.

Regarding the searched term citizenship_status: no column of that name appears in the documented projection. Citizenship-related attributes in the IGS/TCA model typically reside on party or applicant attribute tables rather than this view, so a direct filter on CITIZENSHIP_STATUS would fail unless such a column exists in the unlisted portion of the view text.

Common Use Cases and Queries

The primary scenario is generating outcome letters for applicants, applying per-letter validation at the query level. A representative query retrieves candidate rows for a specific course and outcome:

• SELECT party_id, party_name, course_cd, adm_outcome_status, entry_status_name FROM igs_ad_outcome_letters_v WHERE course_cd = :course AND adm_outcome_status = :status;
• Restricting by responsible organization: WHERE responsible_org_unit_cd = :org.
• Excluding non-mailable parties: WHERE do_not_mail_flag = 'N'.
• Award-year or applicant grouping using preference_number, sch_apl_to_id, and course identifiers.

Because the object is obsolete and not implemented in the documented environment, any integration referencing it should be re-validated against current IGS replacements before deployment on 12.1.1 or 12.2.2.