Search Results igf_ap_fa_base_results_v




Overview
Explore in AI Chat

Overview

The view IGF_AP_FA_BASE_RESULTS_V resides in the APPS schema of Oracle E-Business Suite and belongs to the IGF — Financial Aid product family. It is a reporting and integration view that consolidates financial aid base records with person, party, and calendar context. Its documented purpose is to expose the award years that are available from the calendar setup, joined to the financial aid base record and the associated person identity. In practice the view returns one row per financial aid base record, enriched with the party number, person name, date of birth, an alternate person identifier (typically the SSN-based identifier), and the calendar type and sequence number of the award year instance.

Because it joins several core Oracle objects — HZ_PARTIES, HZ_PERSON_PROFILES, IGS_CA_INST, and IGF_AP_FA_BASE_REC — the view is best understood as a denormalized convenience layer. It removes the need for reporting tools, extracts, and downstream integrations to reproduce the same joins. The view is documented as VALID under ETRM 12.2.2 and remains structurally consistent with 12.1.1, where the same underlying tables exist.

Underlying Base Objects

The view is defined over four base tables plus one inline subquery. The driving table is IGF_AP_FA_BASE_REC (aliased FA), which stores the financial aid base record containing PERSON_ID and BASE_ID, along with the calendar type and sequence number that identify the award year instance. Person data is supplied by HZ_PARTIES (PE) for PARTY_NUMBER, last name, and first name, and by HZ_PERSON_PROFILES (HPP) for DATE_OF_BIRTH. The calendar instance table IGS_CA_INST (CA) supplies the award year calendar context and is joined on CAL_TYPE and SEQUENCE_NUMBER. The inline subquery joins IGS_PE_PERSON_ID_TYP and IGS_PE_ALT_PERS_ID to derive API_PERSON_ID from the alternate person identifier whose person ID type maps to the SSN designation, restricted by the effective date range.

All joins are equijoins on party and person keys; the derived identifier is applied with an outer join ((+)), so a base record is returned even when no qualifying SSN-style alternate identifier exists.

Key Columns

  • PERSON_ID — Party identifier of the financial aid person, sourced from IGF_AP_FA_BASE_REC.
  • BASE_ID — Identifier of the financial aid base record.
  • PERSON_NUMBER — The PARTY_NUMBER from HZ_PARTIES, the human-readable person number.
  • PERSON_NAME — Concatenation of last name, a comma and space, and first name.
  • API_PERSON_ID — Alternate person identifier derived from the SSN-type record in IGS_PE_ALT_PERS_ID; may be null due to the outer join.
  • DATE_OF_BIRTH — Birth date from the currently effective HZ_PERSON_PROFILES row.
  • CI_CAL_TYPE — Calendar type of the award year instance.
  • CI_SEQUENCE_NUMBER — Sequence number of the award year instance within that calendar type.

Common Use Cases and Queries

Typical uses include award-year validation, person-level financial aid extracts, and reconciliation of base records against the academic calendar. The date-effective filter on HZ_PERSON_PROFILES (SYSDATE BETWEEN EFFECTIVE_START_DATE AND NVL(EFFECTIVE_END_DATE, SYSDATE)) ensures only the current profile row is returned. A representative query lists all award years for a given person:

SELECT person_id,
       person_number,
       person_name,
       date_of_birth,
       ci_cal_type,
       ci_sequence_number
FROM   apps.igf_ap_fa_base_results_v
WHERE  person_id = :p_person_id
ORDER  BY ci_cal_type, ci_sequence_number;

To enumerate the award years available from calendar setup for reporting:

SELECT DISTINCT ci_cal_type, ci_sequence_number
FROM   apps.igf_ap_fa_base_results_v
ORDER  BY ci_cal_type, ci_sequence_number;

Because the view performs no aggregation, it can be joined directly to other financial aid tables on PERSON_ID or BASE_ID for integrated extracts.