Search Results igs_ad_resi_letter_v




Overview
Explore in AI Chat

Overview

The IGS_AD_RESI_LETTER_V view is a reporting object within the Oracle E-Business Suite Student System (IGS) product family. As its name implies, it is designed to support the generation of residency letters and related communications by returning the residency details of a student party together with the associated person and party demographic information. In the ETRM metadata for release 12.2.2 the object is classified as a VIEW owned by the APPS schema with a status of VALID, and its documented description states that it "would return the Residency Details of a student." It is therefore a read-only, query-oriented construct rather than a transactional entity — no DML is performed against it directly.

Functionally the view joins party-level data (the HZ alias, drawn from the Trading Community Architecture/HZ party model) to residency evaluation data (the RDV alias) and academic calendar context (the AA and CI aliases). This makes it suitable for letter generation, residency status verification, and ad-hoc reporting on students whose residency classification has been evaluated for a given academic period.

Underlying Base Objects

The ETRM documentation for this view lists no formally documented base objects, and the excerpt provided does not enumerate a FROM clause or join conditions. Based on the column aliases present in the view text, the view is defined over the following underlying structures:

  • HZ party tables (aliased HZ) — supplying party identifiers, names, address components, and person/organization attributes such as PARTY_ID, PARTY_NAME, PARTY_NUMBER, and PERSON_FIRST_NAME.
  • Residency detail table (aliased RDV) — supplying RESIDENT_DETAILS_ID, residency classification and status, evaluation date and evaluator, start and end dates, and calendar context.
  • Academic calendar/CI tables (aliased AA and CI) — supplying ACAD_CAL_TYPE, ACAD_CI_SEQUENCE_NUMBER, and a calendar description or alternate code.

The view additionally invokes the packaged function IGS_CO_GEN_003.GET_RESIDENCY_DFF_VALUES against RESIDENT_DETAILS_ID, exposing descriptive flexfield values for the residency detail record. It also exposes a derived WH_UPDATE_DATE column populated as TO_DATE(NULL), a placeholder used for warehouse-style change tracking.

Key Columns

Because the user searched for res_sequence_number, that column is of central interest. In the view it is produced as RDV.SEQUENCE_NUMBER RES_SEQUENCE_NUMBER — that is, the sequence number of the residency record on the student's residency calendar. It identifies the specific occurrence of a residency evaluation within a calendar, and is typically combined with the calendar type (RES_CAL_TYPE) and calendar description (RES_CALENDAR_DESC) to uniquely locate a residency record.

Other notable columns include:

  • PARTY_ID, PARTY_NAME, PARTY_NUMBER, PARTY_TYPE — student/party identification.
  • RESIDENT_DETAILS_ID — primary identifier of the residency detail row.
  • RESIDENCY_CLASS / RESIDENCY_CLASS_DESC and RESIDENCY_STATUS / RESIDENCY_STATUS_DESC — the residency classification (e.g., resident vs. non-resident) and its current status, in both coded and described forms.
  • EVALUATION_DATE and EVALUATOR — when and by whom the residency was assessed.
  • START_DT and END_DT — effective period of the residency determination.
  • ACAD_CAL_TYPE and ACAD_CI_SEQUENCE_NUMBER — the associated academic calendar context.

Common Use Cases and Queries

The view is most commonly used to drive residency confirmation letters and to report on a student's current residency standing for an academic term. A typical query retrieving the latest residency record for a party would filter on the sequence number and order descending:

  • Letter generation: select party name, address, and residency classification and status to populate a mail-merge template.
  • Residency audit: list all evaluations for a party by academic calendar, ordered by RES_SEQUENCE_NUMBER.
  • Status verification: retrieve the effective residency record where START_DT and END_DT bracket the current date.

Sample SQL:

SELECT party_id, party_name, residency_class_cd, residency_status_cd,
       res_cal_type, res_sequence_number, evaluation_date, evaluator
  FROM apps.igs_ad_resi_letter_v
 WHERE party_id = :p_party_id
 ORDER BY res_sequence_number DESC;

As with all APPS-owned views, queries should be executed with the appropriate MO or responsibility context so that the underlying IGS and HZ security policies are honored.