Search Results igs_ad_schl_aply_to_u2

Overview

The IGS_AD_SCHL_APLY_TO table is a core reference data object within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Oracle Student Management (OSM) product, as indicated by the IGS schema. This table functions as a master list or lookup table that stores the distinct schools or educational institutions to which a student can apply. Its primary role is to maintain a centralized, organization-specific catalog of potential destination schools, enabling consistent data entry and validation across the admissions and student lifecycle modules. The table's design supports multi-org structures through the ORG_UNIT_CD column, allowing the same school entity to be configured differently or have distinct operational statuses across various business units within the institution.

Key Information Stored

The table's structure is designed to capture essential identifying and descriptive information for each school record, along with standard audit details. The critical columns are:

• SCH_APL_TO_ID (NUMBER): The system-generated, unique primary key identifier for each record. This is the column referenced by the user's search term and is used as a foreign key in transactional tables.
• SCHOOL_APPLYING_TO (VARCHAR2): The name of the school or institution.
• DESCRIPTION (VARCHAR2): A more detailed explanation or note about the school.
• ORG_UNIT_CD (VARCHAR2): The identifier for the organizational unit, enforcing the multi-org context for the record.
• CLOSED_IND (VARCHAR2): A status flag indicating whether the record is active ('N') or inactive/closed ('Y') for future use, a common soft-delete mechanism.
• Standard WHO Columns (CREATED_BY, CREATION_DATE, etc.): Audit columns tracking the user and timestamp for record creation and last update.

The unique constraint IGS_AD_SCHL_APLY_TO_U2 on SCHOOL_APPLYING_TO and ORG_UNIT_CD ensures that no duplicate school names exist within a given organization.

Common Use Cases and Queries

This table is primarily referenced during application processing and reporting. A common operational use case is populating a list of values (LOV) for a "School Applying To" field on a student application form, filtered by the user's organization and active status. For data validation and reporting, administrators often query this master table to reconcile or audit transactional data. Sample queries include retrieving all active schools for a specific org unit or identifying applications linked to a school that has since been closed.

-- Retrieve all active schools for organization 'MAIN'
SELECT sch_apl_to_id, school_applying_to, description
FROM igs.igs_ad_schl_aply_to
WHERE org_unit_cd = 'MAIN'
  AND closed_ind = 'N'
ORDER BY school_applying_to;

-- Find the count of applications staged per school
SELECT s.school_applying_to, COUNT(a.sch_apl_to_id) AS application_count
FROM igs.igs_ad_schl_aply_to s
LEFT JOIN igs.igs_ss_app_pgm_stg a ON s.sch_apl_to_id = a.sch_apl_to_id
WHERE s.closed_ind = 'N'
GROUP BY s.school_applying_to;

Related Objects

The IGS_AD_SCHL_APLY_TO table serves as a parent reference table within the data model. Its primary key (SCH_APL_TO_ID) is referenced as a foreign key to ensure data integrity in related transactional tables. Based on the provided dependency and relationship data:

• Primary Key Constraint: IGS_AD_SCHL_APLY_TO_PK on the SCH_APL_TO_ID column.
• Referencing Object (Foreign Key):
  • TABLE: IGS.IGS_SS_APP_PGM_STG: The SCH_APL_TO_ID column in this table references IGS_AD_SCHL_APLY_TO.SCH_APL_TO_ID. This indicates that records in the application program staging table are linked to a valid school from this master list.

This relationship confirms the table's role in the application pipeline, where staged application data must point to a predefined, approved school entity.