Search Results aal_sequence_number

Overview

The IGS_AD_APPL_LTR_PHR table is a core data object within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically in the Admissions (IGS) module. It serves as a transactional repository for special phrases used in correspondence generated for admission applications. The table's primary function is to store institution-defined, dynamic text snippets that can be merged into letter templates. This enables the production of personalized and context-specific communication, such as acceptance letters, waitlist notifications, or requests for additional documentation, by substituting placeholder codes with the actual phrase text stored in this table for a specific application.

Key Information Stored

The table stores a combination of application identifiers, phrase metadata, and the actual text. Key columns include the composite primary key (PERSON_ID, ADMISSION_APPL_NUMBER, CORRESPONDENCE_TYPE, AAL_SEQUENCE_NUMBER, SEQUENCE_NUMBER), which uniquely identifies each phrase instance for a specific application letter. The PHRASE_CD column is critical, storing the unique institution-defined code that identifies the type of phrase, such as a conditional offer clause or a financial aid note. This code is foreign-keyed to the master list of available phrases. The PHRASE_TEXT column (VARCHAR2 2000) holds the substantive text that will be substituted into the correspondence. Other important columns are PHRASE_ORDER_NUMBER, which dictates the display sequence of multiple phrases, and LETTER_PARAMETER_TYPE, which links to parameter types that can be merged with templates for further customization.

Common Use Cases and Queries

A primary use case is the generation and review of application correspondence. Administrators may query this table to verify or audit the text included in letters sent to applicants. A common reporting need is to list all phrases used for a specific applicant or application. For example, to retrieve all phrase text for a particular admission application letter, a query would join on the parent letter table using the key columns:

• SELECT phr.phrase_cd, phr.phrase_text, phr.phrase_order_number FROM igs.igs_ad_appl_ltr_phr phr WHERE phr.person_id = :1 AND phr.admission_appl_number = :2 AND phr.correspondence_type = :3 AND phr.aal_sequence_number = :4 ORDER BY phr.phrase_order_number;

Another frequent scenario involves troubleshooting or data fixes, where queries filter on the PHRASE_CD to find all applications where a specific phrase code has been used, potentially with incorrect text. The non-unique index on PHRASE_CD (IGS_AD_APPL_LTR_PHR_N3) is optimized for such lookups.

Related Objects

The table maintains defined foreign key relationships with several master and transactional tables, ensuring referential integrity. The primary key is enforced by the IGS_AD_APPL_LTR_PHR_PK constraint. Key documented relationships include:

• IGS_AD_APPL_LTR: The table is a child of the main application letter table. The foreign key on (PERSON_ID, ADMISSION_APPL_NUMBER, CORRESPONDENCE_TYPE, AAL_SEQUENCE_NUMBER) ties each phrase record to a specific letter instance.
• IGS_CO_LTR_PHR: This is the master table for letter phrase codes. The foreign key on PHRASE_CD ensures that only valid, institution-defined phrase codes are stored in the application-specific table.
• IGS_CO_LTR_PARM_TYPE: This master table for letter parameter types is referenced via the LETTER_PARAMETER_TYPE column, linking the phrase to broader template merging parameters.