Search Results igf_ap_usr_def_flds_all_uk

Overview

IGF_AP_USR_DEF_FLDS_ALL is a financial aid configuration table within the Oracle E-Business Suite (EBS) IGF module. It stores user-defined verification fields for each award year, providing the mechanism by which institutions map custom verification requirements to specific ISIR (Institutional Student Information Record) columns. The data captured in these mapped columns represents values that must be verified against documentation supplied by the student.

From a Data Vault modeling perspective, the mined foreign-key structure suggests a satellite-leaning classification. The table hangs off parent verification entities and carries descriptive attributes (field labels, sequence numbers, organizational context) rather than acting as an independent hub or a pure association link. In EBS 12.1.1 and 12.2.2, this is a reference/configuration table rather than a transactional fact table, and the ETRM documentation indicates it is not implemented in every database instance — it is only populated when the institution elects to use user-defined verification fields.

Key Information Stored

The documented columns of interest are as follows:

• VERN_ITEM_SEQ_NUM — The surrogate primary key (IGF_AP_USR_DEF_FLDS_ALL_PK). It uniquely identifies each user-defined verification field row and is the target of the foreign key from IGF_AP_INST_VER_ITEM_ALL.UDF_VERN_ITEM_SEQ_NUM.
• CI_CAL_TYPE — Calendar type component of the unique business key (IGF_AP_USR_DEF_FLDS_ALL_UK). Collectively with the sequence number it anchors the award year context in which the field applies.
• CI_SEQUENCE_NUMBER — Sequence component of the unique business key. Together with CI_CAL_TYPE it identifies the calendar (award year) instance.
• FIELD_LABEL — The display label applied to the user-defined field as presented to users.
• GROUP_CODE — Foreign key column referencing an organizational/grouping construct (the ETRM metadata shows the reference target as unresolved, denoted "%").
• VERG_ID — Foreign key column referencing a verification group identifier (target also denoted "%").
• ORG_ID — Multi-organization identifier supporting operating unit partitioning, a standard EBS construct.

The surrogate key is VERN_ITEM_SEQ_NUM; the business-key candidate is the composite of CI_CAL_TYPE, CI_SEQUENCE_NUMBER, and FIELD_LABEL. Note the important distinction: the unique index as documented lists CI_CAL_TYPE and CI_SEQUENCE_NUMBER, with FIELD_LABEL named in the same key block.

Common Use Cases and Queries

Typical scenarios include configuring which verification items are active for a given award year, reporting the list of user-defined fields applied to ISIR verification, and diagnosing mismatches between the institution's verification item setup and its user-defined field mappings.

A representative query retrieving the user-defined fields for a specific award year:

• SELECT VERN_ITEM_SEQ_NUM, CI_CAL_TYPE, CI_SEQUENCE_NUMBER, FIELD_LABEL, GROUP_CODE, VERG_ID, ORG_ID FROM IGF_AP_USR_DEF_FLDS_ALL WHERE CI_CAL_TYPE = :cal_type AND CI_SEQUENCE_NUMBER = :seq_num AND ORG_ID = :org_id;

Reporting use cases focus on configuration audits and reconciliation of the user-defined fields against their parent institutional verification items. Because the table is configuration rather than transactional, queries typically join to the institutional verification item table to produce a complete verification setup listing.

Related Objects

• IGF_AP_INST_VER_ITEM_ALL — The principal dependent object. Its UDF_VERN_ITEM_SEQ_NUM column references IGF_AP_USR_DEF_FLDS_ALL, tying each institution verification item to its user-defined field. This is the primary join path.
• IGF_AP_USR_DEF_FLDS_ALL (self-referencing entries for GROUP_CODE and VERG_ID) — the metadata records FK columns GROUP_CODE and VERG_ID originating from this table; these support grouping and verification-group categorization.
• IGF user-defined field setup and ISIR verification configuration objects that consume FIELD_LABEL and the calendar keys to render verification screens and drive ISIR column mapping.
• Standard EBS calendar (CI_CAL_TYPE / CI_SEQUENCE_NUMBER) and organization (ORG_ID) reference objects that provide context for the unique key and multi-org partitioning.

Because the ETRM metadata marks the FK targets for GROUP_CODE and VERG_ID as unresolved, DBAs validating a specific instance should confirm the actual reference tables locally before constructing joins beyond the documented IGF_AP_INST_VER_ITEM_ALL relationship.