Search Results igf_gr_reg_amts_pk

Overview

The IGF.IGF_GR_REG_AMTS table is a Financial Aid module object within Oracle E-Business Suite (documented in both 12.1.1 and 12.2.2). It stores the Regular Pell Amount applicable to a given combination of cost-of-attendance (COA) ranges and expected family contribution (EFC) ranges, filtered by system award year and enrollment status. In effect, it is the master lookup that resolves "how much Pell grant a student qualifies for" once their COA and EFC have been determined.

The ETRM metadata classifies this object as a standalone entity at the Data Vault level (no foreign-key dependencies into other hubs or links were mined). In Data Vault modeling terms, this suggests treating the table as a satellite holding the descriptive Pell amount attribute, keyed by its business-key combination of award year, enrollment status, and the four range boundaries. Because it has no outbound FK relationships, it functions as an independent reference/lookup table rather than a transactional hub.

Key Information Stored

The table contains 13 documented columns. The most significant are:

• SEQUENCE_TXT — the surrogate primary key (via IGF_GR_REG_AMTS_PK), uniquely identifying each Pell amount row. It also participates in unique index IGF_GR_REG_AMTS_U1.
• SYS_AWD_YR — the system award year the Pell amount applies to (e.g., 2011-2012).
• ENROLLMENT_STAT_CODE — the enrollment status (full-time, part-time, etc.) affecting the Pell amount.
• COA_RANGE_START / COA_RANGE_END — the lower and upper bounds of the cost-of-attendance band.
• EFC_RANGE_START / EFC_RANGE_END — the lower and upper bounds of the expected family contribution band.
• PELL_AMOUNT — the resolved Regular Pell Award amount for that range combination.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle WHO columns for audit tracking.

The surrogate key (SEQUENCE_TXT) is distinct from the business-key candidate captured by IGF_GR_REG_AMTS_U2, which is the composite of SYS_AWD_YR, ENROLLMENT_STAT_CODE, COA_RANGE_START, COA_RANGE_END, EFC_RANGE_START, and EFC_RANGE_END. That unique index guarantees that no two rows define overlapping amount definitions for the same award year/status/range combination.

Common Use Cases and Queries

The principal use case is Pell award lookup and reconciliation. A Financial Aid process selects the Pell amount whose defined ranges encompass a student's computed COA and EFC:

SELECT peLL_amount
FROM   igf.igf_gr_reg_amts
WHERE  sys_awd_yr           = :award_year
AND    enrollment_stat_code = :enroll_status
AND    :coa BETWEEN coa_range_start AND coa_range_end
AND    :efc BETWEEN efc_range_start AND efc_range_end;

Typical reporting scenarios include: generating award-year configuration extracts, verifying that no range gaps or overlaps exist in the lookup grid (SELECT COUNT(*) ... GROUP BY sys_awd_yr, enrollment_stat_code), and reconciling disbursed Pell amounts against the configured PELL_AMOUNT. Because the table is range-based, join conditions against student-level COA/EFC data must always use BETWEEN semantics on both range pairs rather than equality.

Related Objects

The ETRM metadata documents no foreign-key relationships for this table, so it is best understood as a self-contained configuration lookup. Dependent and co-referenced objects in practice include:

• Student COA / EFC assessment tables in the IGF schema — supplying the runtime COA and EFC values queried via BETWEEN against this table's ranges.
• IGF Pell award / disbursement tables — consuming the resolved PELL_AMOUNT for award packaging.
• IGF_GR_REG_AMTS_PK and IGF_GR_REG_AMTS_U1/U2 — the primary and unique indexes enforcing row and business-key integrity.
• IGF Award Year setup objects — the master source of SYS_AWD_YR values.
• Pell Grant configuration/parameter forms within the Financial Aid module — the maintenance UI that populates and maintains this lookup table.