Search Results igs_ad_ack_letter_v




Overview
Explore in AI Chat

Overview

The IGS_AD_ACK_LETTER_V view is a reporting object within the Oracle E-Business Suite Student System (IGS) product family, owned by the APPS schema and holding VALID status in both EBS 12.1.1 and 12.2.2. Its stated purpose, per the ETRM repository, is to "return the Acknowledgement Details of a student." In the admissions and recruitment cycle, an acknowledgement letter is the correspondence issued to an applicant confirming that an admission application has been received and registered by the institution. This view consolidates the applicant's party-level identity and address data with application-level and course-level admissions data, providing a single denormalized source for producing acknowledgement letters, confirmation mailings, and related admissions correspondence.

Because the view joins the Trading Community Architecture (TCA) party model against IGS admissions entities, it is equally useful as an integration and reporting surface. Oracle BI Publisher, Oracle Reports, and custom PL/SQL letter-generation routines can query it directly to derive the recipient name, postal address, nominated course, and admission calendar details needed to render correspondence without re-implementing the underlying join logic.

Underlying Base Objects

The ETRM metadata documents no formally registered base objects for this view, but the embedded view text reveals its composition. The view is defined over a TCA party/address source (aliased HZ, exposing columns such as PARTY_ID, PARTY_NAME, PARTY_NUMBER, and the ADDRESS1–ADDRESS4 and POSTAL_CODE attributes), an admissions application view (aliased ACAIV, supplying PREFERENCE_NUMBER, LOCATION_CD, ATTENDANCE_MODE, ATTENDANCE_TYPE, ADMISSION_APPL_NUMBER, NOMINATED_COURSE_CD, and SEQUENCE_NUMBER), a program-of-study source (aliased PS, providing COURSE_CD and TITLE), and a calendar/application-status view (aliased AAV, supplying ACAD_CAL_TYPE, ACAD_CI_SEQUENCE_NUMBER, ADM_CAL_TYPE, ADM_CI_SEQUENCE_NUMBER, COMMENCEMENT_PERIOD, ACAD_ALTERNATE_CODE, and ADM_APPL_STATUS). Descriptive lookups for location, attendance mode, and attendance type are joined through LOC, AM, and ATY respectively. The view also invokes the IGS function IGS_AD_GEN_004.ADMP_GET_CHG_PREF_DT to derive the preferred/change date.

Key Columns

The most operationally significant columns fall into three groups:

  • Party and recipient attributes: PARTY_ID, PARTY_NAME, PARTY_NUMBER, PARTY_TYPE, PERSON_FIRST_NAME, PERSON_LAST_NAME, SALUTATION, EMAIL_ADDRESS, and the address block (ADDRESS1–ADDRESS4, CITY, STATE, POSTAL_CODE, COUNTRY). The DO_NOT_MAIL_FLAG is critical for suppressing correspondence.
  • Application attributes: ADMISSION_APPL_NUMBER, SEQUENCE_NUMBER, PREFERENCE_NUMBER, and the derived preferred-change date from IGS_AD_GEN_004.ADMP_GET_CHG_PREF_DT.
  • Course and calendar attributes: COURSE_CD, TITLE, NOMINATED_COURSE_CD, LOCATION_CD, ATTENDANCE_MODE, ATTENDANCE_TYPE, and their description columns, plus ACAD_CAL_TYPE, ADM_CAL_TYPE, COMMENCEMENT_PERIOD, and ADM_APPL_STATUS.

Common Use Cases and Queries

Typical scenarios include batch acknowledgement letter generation, applicant status confirmation reporting, and integration feeds to document-management or CRM systems. A representative query retrieving acknowledgement data for a specific applicant follows:

  • SELECT party_id, party_name, address1, city, postal_code, country, admission_appl_number, course_cd, title, adm_appl_status
  • FROM apps.igs_ad_ack_letter_v
  • WHERE admission_appl_number = :p_appl_number;

To drive a mailing run while honoring TCA suppression rules:

  • SELECT party_id, party_name, address1, address2, city, postal_code, email_address
  • FROM apps.igs_ad_ack_letter_v
  • WHERE do_not_mail_flag = 'N'
  • AND adm_cal_type = :p_adm_cal_type
  • AND adm_ci_sequence_number = :p_ci_seq;

Because the view is not indexed and resolves several joins plus a PL/SQL function call, filter predicates on application, calendar, or party identifiers should be applied wherever possible to bound the result set.