Results for “ext_lookup_code”
10 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
IGF_AW_INT_EXT_MAP is a Financial Aid (IGF) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the mapping between internal Oracle lookup types and lookup codes and the external lookup types and codes mandated by federal reporting standards. In the context of US federal student aid processing, institutions are required to report certain values using federally prescribed code sets that differ from the codes used internally in Oracle EBS. This table bridges that gap, providing a translation layer that allows Financial Aid administrators to maintain internal lookups while still producing federally compliant output for processes such as COD (Common Origination and Disbursement) reporting and ISIR processing.
The documented ETRM metadata classifies this object heuristically as standalone within a Data Vault style model. In Data Vault terms, this suggests treating it as an independent reference or mapping entity rather than a hub, link, or satellite, since no foreign key dependencies were mined from the physical structure. Analysts building a warehouse layer should consider it a standalone cross-reference table whose natural business keys are the award year plus lookup type/code combinations.
Key Information Stored
The table contains 11 documented columns. The most significant are:
- IXM_ID — Surrogate primary key, enforced by IGF_AW_INT_EXT_MAP_PK and mirrored by unique index IGF_AW_INT_EXT_MAP_U1. This is a system-generated identifier with no business meaning.
- SYS_AWARD_YEAR — The federal award year (for example, 2013 for the 2012–2013 cycle) to which the mapping applies. This column participates in both business-key unique indexes, reflecting that mappings are year-specific and can change across award cycles.
- INT_LOOKUP_TYPE and INT_LOOKUP_CODE — The internal Oracle lookup type and code used within EBS. These form the internal side of the mapping and are constrained by IGF_AW_INT_EXT_MAP_U2 (SYS_AWARD_YEAR, INT_LOOKUP_TYPE, INT_LOOKUP_CODE), guaranteeing that a single internal value maps to only one external equivalent per award year.
- EXT_LOOKUP_TYPE and EXT_LOOKUP_CODE — The federally mandated external lookup type and code. A second unique index, IGF_AW_INT_EXT_MAP_U3 (SYS_AWARD_YEAR, EXT_LOOKUP_TYPE, EXT_LOOKUP_CODE, INT_LOOKUP_TYPE, INT_LOOKUP_CODE), enforces uniqueness across the combined external and internal bindings.
- CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Oracle WHO-audit columns capturing insert and update provenance for each mapping row.
Common Use Cases and Queries
The primary use case is translation during federal reporting and validation. A typical query retrieves the external code for a given internal value within an award year:
SELECT ext_lookup_type, ext_lookup_code FROM igf.igf_aw_int_ext_map WHERE sys_award_year = :year AND int_lookup_type = :type AND int_lookup_code = :code;- Reverse lookups map an incoming federal code back to the internal code using EXT_LOOKUP_TYPE and EXT_LOOKUP_CODE.
- Reporting queries enumerate all mappings for an award year to audit completeness, for example
WHERE sys_award_year = 2013 ORDER BY int_lookup_type, int_lookup_code. - Change-tracking reports compare mappings between award years by joining on INT_LOOKUP_TYPE and INT_LOOKUP_CODE across two SYS_AWARD_YEAR values.
Related Objects
The ETRM metadata records no mined foreign key relationships, so the table is documented as standalone. In practice it is referenced functionally by the Financial Aid lookup and federal reporting components that consume internal lookup values (such as the FSA lookup views and COD transmission processes) and by the lookup type/code definitions held in the shared Oracle lookup framework. Because no hard FK constraints are documented, joins to dependent objects are performed on the logical keys SYS_AWARD_YEAR, INT_LOOKUP_TYPE, and INT_LOOKUP_CODE rather than on IXM_ID. Supplementary integration or reporting views may also project these mappings for downstream federal extracts.
-
Stores the mapping of internal Lookup Types/Lookup Codes to External lookup type/Codes that are federally mandated
-
View: IGF_AW_INT_EXT_MAP_V 12.1.1
Stores the mapping of internal Lookup Types/Lookup Codes to External lookup type/Codes that are federally mandated
APPS.IGF_AW_INT_EXT_MAP_V·↳ IGF_AW_INT_EXT_MAP·Explore IGF module →
-
View: IGF_AW_INT_EXT_MAP_V 12.2.2
Stores the mapping of internal Lookup Types/Lookup Codes to External lookup type/Codes that are federally mandated
Not implemented in this database·Explore IGF module →
-
Table: IGF_AW_INT_EXT_MAP 12.2.2
Stores the mapping of internal Lookup Types/Lookup Codes to External lookup type/Codes that are federally mandated
Not implemented in this database·Explore IGF module →
-
eTRM - IGF Tables and Views 12.1.1
Holds pays only unit details for a pays only program
-
eTRM - IGF Tables and Views 12.1.1
Holds pays only unit details for a pays only program