Search Results admp_get_chg_pref_dt

Overview

IGS_AD_ACK_LETTER_V is a view in the Oracle E-Business Suite Applications (APPS) schema, owned by the Student Systems / Admissions product family (IGS). It exposes the data set required to generate acknowledgement letters for applicants within the Oracle Student System admissions process. The view consolidates party-level information from the Trading Community Architecture (TCA) registry (HZ tables), course and offering details from the admissions catalogue, and application-instance attributes, so that a single query returns everything needed to render a personalized acknowledgement letter — applicant name, correspondence address, nominated course, preference ranking, and admission application status.

The object is a read-only reporting and integration view. It is not a base table and has no DML semantics; it functions as a denormalized projection joining TCA party data to admissions application data. Because it carries a party_name, prefixed address columns, and a SQL function call to admp_get_chg_pref_dt, it is designed for letter generation and for downstream correspondence workflows rather than for transactional maintenance.

Underlying Base Objects

The ETRM metadata does not enumerate documented base objects, but the view text reveals the source aliases: hz, acaiv, ps, loc, am, aty, and aav. These correspond to TCA party and party-site tables (the HZ family), the admissions course application instance entity (ACAIV — IGS admissions application/course instance), the course catalogue table (PS), location definitions (LOC), attendance mode and attendance type reference tables (AM, ATY), and the admissions application/instance table (AAV). Two database function calls are embedded directly in the SELECT list: IGS_AD_GEN_004.admp_get_chg_pref_dt(...) and IGS_CO_GEN_003.get_per_addr_for_corr(...), plus a TO_CHAR(SYSDATE,...) expression. The literal TO_DATE(null) wh_update_date is a placeholder column to satisfy the expected shape of the warehouse/correspondence consumer.

Key Columns

• party_id, party_name, party_number, party_type — core TCA identity columns identifying the applicant.
• person_first_name, person_last_name, salutation, known_as, person_academic_title — name components used in the letter's greeting and address block.
• address1..address4, city, state, province, postal_code, country, county — correspondence address assembled from the TCA party site.
• email_address, url — digital contact points.
• acaiv.admission_appl_number, nominated_course_cd, sequence_number, preference_number — the application and course preference context.
• ps.course_cd, ps.title — the nominated course code and description.
• location_cd, attendance_mode, attendance_type with loc.description, am.description, aty.description — delivery and location detail for the offered course.
• acad_cal_type, acad_ci_sequence_number, adm_cal_type, adm_ci_sequence_number, commencement_period — academic and admission calendar instance references.
• adm_appl_status — the applicant's current admission status.
• admp_get_chg_pref_dt(...) — computed change-of-preference date for the applicant/course/sequence.
• get_per_addr_for_corr(party_id) — computed correspondence address flag/value.
• wh_update_date — null placeholder, present only to match the target interface.

Common Use Cases and Queries

The primary use case is acknowledgement letter generation for a specific applicant, and secondarily the extraction of applicant correspondence data for batch mail-merge or ETL routines. A typical filtered query selects a single applicant by party_id or admission application number:

• SELECT party_name, person_first_name, person_last_name, address1, city, postal_code, course_cd, title, adm_appl_status FROM apps.igs_ad_ack_letter_v WHERE party_id = :p_party_id;
• SELECT admission_appl_number, nominated_course_cd, preference_number, course_cd, title FROM apps.igs_ad_ack_letter_v WHERE admission_appl_number = :p_appl;
• SELECT party_number, party_name, email_address, adm_appl_status FROM apps.igs_ad_ack_letter_v WHERE adm_appl_status = 'AC' ORDER BY party_name;

Because the view embeds PL/SQL function calls, performance is best when queries are restricted by indexed TCA or application keys rather than full-table scans. When the ETRM query term admp_get_chg_pref_dt is used, it resolves to the function invoked in the SELECT list, exposed through this view as the change-of-preference date for the applicant's nominated course preference.