Search Results ext_lookup_code




Overview
Explore in AI Chat

Overview

The IGF_AW_INT_EXT_MAP table is a core configuration object within the Oracle E-Business Suite's Institutional Grants and Financial Aid (IGF) module, specifically for versions 12.1.1 and 12.2.2. Its primary function is to manage the mapping between the application's internal lookup values and the standardized, federally mandated external codes required for regulatory reporting and data exchange. This mapping is essential for ensuring that internal financial aid classifications, such as award types or enrollment statuses, are correctly translated into the external codes mandated by entities like the U.S. Department of Education for processes including the submission of the Integrated Postsecondary Education Data System (IPEDS) reports or other federal aid submissions. The table maintains these mappings on a per-award-year basis, allowing for code changes across different academic years.

Key Information Stored

The table's structure is designed to enforce precise and unique mappings. Key columns include the system-generated primary key, IXM_ID, which uniquely identifies each mapping record. The SYS_AWARD_YEAR column anchors each mapping to a specific academic year. The core mapping logic is defined by two pairs of columns: INT_LOOKUP_TYPE and INT_LOOKUP_CODE, which represent the internal application lookup (e.g., 'IGF_AWARD_TYPE'), and EXT_LOOKUP_TYPE and EXT_LOOKUP_CODE, which represent the corresponding external standard (e.g., 'FEDERAL_AWARD_TYPE'). The table's unique indexes (U1, U2, U3) enforce data integrity by preventing duplicate mappings. Standard "Who" columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) track all changes for auditing purposes.

Common Use Cases and Queries

The primary use case is the translation of internal codes to external codes during batch processes for federal reporting or electronic data interchange. Administrators also use this table for configuration and validation during award setup. Common SQL queries involve retrieving the external code for a given internal value within a specific award year, or vice-versa. For example, to find the external code for an internal award type 'SCHOLARSHIP' in award year '2023-2024', one would execute:

SELECT ext_lookup_code
FROM igf.igf_aw_int_ext_map
WHERE sys_award_year = '2023-2024'
  AND int_lookup_type = 'IGF_AWARD_TYPE'
  AND int_lookup_code = 'SCHOLARSHIP';

Another critical pattern is validating the completeness of mappings for a given year and lookup type to ensure reporting readiness:

SELECT int_lookup_type, int_lookup_code, COUNT(*)
FROM igf.igf_aw_int_ext_map
WHERE sys_award_year = '2023-2024'
GROUP BY int_lookup_type, int_lookup_code
HAVING COUNT(*) != 1;

Related Objects

The table's primary key, IGF_AW_INT_EXT_MAP_PK, is defined on the IXM_ID column. While the provided metadata does not list explicit foreign key relationships, this table is inherently related to the core lookup tables (such as FND_LOOKUP_TYPES and FND_LOOKUP_VALUES) that store the internal definitions referenced by INT_LOOKUP_TYPE and INT_LOOKUP_CODE. It is a foundational seed data table, likely referenced by various financial aid batch processing engines, reporting views, and public APIs within the IGF module that handle award packaging and federal compliance reporting. Its storage in the APPS_TS_SEED tablespace confirms its role as a configuration entity loaded during system setup or patch application.