Results for “igs_ad_appl_ltr”

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

Overview

IGS_AD_APPL_LTR is a table in the IGS (Student System) product family within Oracle E-Business Suite, documented under the ETRM repository. It describes correspondence produced for an admission application — that is, each row records a letter or notification generated in the context of a prospective student's application. The product is flagged as obsolete in the 12.1.1 and 12.2.2 documentation set, and the ETRM record explicitly states that the object is not implemented in the reference database. It should therefore be treated as a legacy or archival definition rather than an active runtime object in current installations.

Functionally, the table sits at the intersection of the applicant (identified by PERSON_ID), the application instance (ADMISSION_APPL_NUMBER), and the type of letter issued (CORRESPONDENCE_TYPE). Multiple letters of the same type can be produced against a single application, which is why SEQUENCE_NUMBER participates in the primary key. Column names such as COMPOSED_IND, LETTER_REFERENCE_NUMBER, and SPL_SEQUENCE_NUMBER indicate that the table tracks the composition and dispatch state of the correspondence, not the letter text itself.

From a dimensional-modelling perspective, the mined foreign-key structure suggests a satellite-leaning classification. The table carries descriptive and state attributes attached to a business key composed of PERSON_ID and ADMISSION_APPL_NUMBER, rather than acting as an independent hub or a pure associative link. This classification is heuristic and should be validated against the actual ETL lineage before being adopted as a design decision.

Key Information Stored

The documented physical schema for release 12.1.1 contains sixteen columns owned by the IGS schema.

• PERSON_ID — party identifier for the applicant; part of the primary key and the principal join to the application tables.
• ADMISSION_APPL_NUMBER — the admission application number; combined with PERSON_ID it scopes the correspondence to one application.
• CORRESPONDENCE_TYPE — classification of the letter produced (for example, acknowledgement, offer, or rejection).
• SEQUENCE_NUMBER — disambiguates multiple letters of the same type for one application.
• COMPOSED_IND — indicator of whether the letter has been composed.
• LETTER_REFERENCE_NUMBER — external or internal reference used to identify the letter.
• SPL_SEQUENCE_NUMBER — sequence value used in the source-parameter or supply linkage.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent-program context columns recording which request and program last touched the row.

The surrogate primary key is IGS_AD_APPL_LTR_PK, defined over PERSON_ID, ADMISSION_APPL_NUMBER, CORRESPONDENCE_TYPE, and SEQUENCE_NUMBER. A unique index, IGS_AD_APPL_LTR_U1, covers the identical column list, making it the business-key candidate for the table. No separate numeric surrogate identifier is documented.

Common Use Cases and Queries

Typical reporting on this table centres on correspondence history for an applicant or an application. A standard lookup retrieves all letters for a given person:

SELECT admission_appl_number,
       correspondence_type,
       sequence_number,
       letter_reference_number,
       composed_ind,
       creation_date
FROM   igs_ad_appl_ltr
WHERE  person_id = :p_person_id
ORDER  BY admission_appl_number, correspondence_type, sequence_number;

A second common pattern counts letters by type and composition state, supporting operational dashboards that show which applicants have not yet received a required notification:

SELECT correspondence_type, composed_ind, COUNT(*)
FROM   igs_ad_appl_ltr
GROUP  BY correspondence_type, composed_ind;

A third pattern joins to the application header to reconcile letters against applications, using PERSON_ID and ADMISSION_APPL_NUMBER as the join columns. Data-quality checks should test for orphan correspondence, duplicate type-plus-sequence combinations, and audit rows where LAST_UPDATE_DATE precedes CREATION_DATE.

Related Objects

The most significant related objects, derived from the documented foreign-key relationships, are:

• IGS_AD_APPL_ALL — referenced by PERSON_ID and ADMISSION_APPL_NUMBER; the application header against which correspondence is raised.
• IGS_AD_APPL_LTR_PHR — the phrase or text table that references this table on PERSON_ID, ADMISSION_APPL_NUMBER, CORRESPONDENCE_TYPE, and AAL_SEQUENCE_NUMBER, holding the letter body content.
• IGS_AD_APPL_LTR_PK — the primary-key constraint enforcing uniqueness of person, application, type, and sequence.
• IGS_AD_APPL_LTR_U1 — the unique index supporting the business key.
• CORRESPONDENCE_TYPE reference — the lookup or validation entity that qualifies the letter type, shown in the metadata as a partial foreign-key target.

Because the object is documented as obsolete and not implemented, integration work should confirm whether an equivalent structure exists in the target release before building dependencies on it.