Search Results igs_ad_appl_ltr_pk

Overview

The IGS_AD_APPL_LTR table is a core data object within the Oracle E-Business Suite (EBS) Student System (IGS) module. It serves as the central repository for tracking all formal correspondence generated and dispatched in relation to an applicant's admission application. This includes critical communications such as offer letters, acknowledgment letters, rejection notices, and requests for additional information. Its primary role is to maintain a complete, auditable history of all application-related interactions, ensuring data integrity and supporting key admissions workflow processes like communication tracking, applicant service, and compliance reporting across both EBS 12.1.1 and 12.2.2 versions.

Key Information Stored

The table's structure is designed to uniquely identify each piece of correspondence within the context of a specific application. Its composite primary key consists of four columns: PERSON_ID, ADMISSION_APPL_NUMBER, CORRESPONDENCE_TYPE, and SEQUENCE_NUMBER. This key structure ensures that multiple letters of the same type for a single application can be tracked sequentially. While the provided metadata does not list all columns, the foreign key relationships indicate the presence of critical fields such as LETTER_REFERENCE_NUMBER and SPL_SEQUENCE_NUMBER, which are likely used to link to the physical letter template or batch print job. The table essentially stores metadata about the correspondence event, not the letter content itself.

Common Use Cases and Queries

This table is fundamental for admissions office reporting and operational queries. Common use cases include generating a history of all communications sent to a specific applicant, verifying the dispatch of an offer letter, and identifying applicants who have not received a required acknowledgment. A typical reporting query would join this table to the main application table (IGS_AD_APPL_ALL) to pull applicant details alongside their correspondence history. For example, to find all offer letters sent for a given application cycle, one might use a SQL pattern such as:

• SELECT aap.person_id, aap.admission_appl_number, aal.correspondence_type, aal.sequence_number, aal.letter_reference_number FROM igs_ad_appl_all aap JOIN igs_ad_appl_ltr aal ON aap.person_id = aal.person_id AND aap.admission_appl_number = aal.admission_appl_number WHERE aal.correspondence_type = 'OFFER' AND aap.admission_appl_number LIKE '&cycle%';

Related Objects

The IGS_AD_APPL_LTR table sits at the center of a relational structure within the IGS schema. Its documented relationships, as per the provided metadata, are as follows:

• Primary Key: IGS_AD_APPL_LTR_PK (PERSON_ID, ADMISSION_APPL_NUMBER, CORRESPONDENCE_TYPE, SEQUENCE_NUMBER).
• Foreign Keys (This Table References):
  • IGS_AD_APPL_ALL via columns (PERSON_ID, ADMISSION_APPL_NUMBER), establishing the foundational link to the application header record.
  • Other referenced tables (indicated by '%' in the metadata) for CORRESPONDENCE_TYPE and PERSON_ID, which are likely validation tables or code lookup tables.
• Foreign Keys (Tables Referencing This Table):
  • IGS_AD_APPL_LTR_PHR references IGS_AD_APPL_LTR via its primary key columns (PERSON_ID, ADMISSION_APPL_NUMBER, CORRESPONDENCE_TYPE) and the column AAL_SEQUENCE_NUMBER (which maps to SEQUENCE_NUMBER). This suggests a child table for storing paragraph-level details or specific content placeholders used within the correspondence.