Search Results fin_award_year




Overview
Explore in AI Chat

Overview

IGF_SL_REP_SMRY_V is a reporting view in the Oracle E-Business Suite (EBS) Financial Aid / Student Loan module (IGF schema, formerly part of the Oracle Student System). Its name — SL (Student Loan) REP (Reporting) SMRY (Summary) — indicates that it aggregates William D. Ford Federal Direct Loan award and disbursement activity into a summary form suitable for loan reconciliation and reporting to the U.S. Department of Education and to institutional reporting entities. In EBS 12.1.1 and 12.2.2, the view is owned by the APPS schema and is exposed for reporting tools (Oracle Reports, OBIEE, Discoverer, and ad-hoc SQL) that need a pre-aggregated picture of Direct Loan volume.

The view is specifically relevant to the "loan_type" search because it filters to and derives the three Direct Loan subtypes from the LOAN_TYPE code column. It is a pure read-only reporting object; it does not store data, enforce business logic, or participate in transaction processing.

Underlying Base Objects

Per the ETRM metadata, the view text is defined over two references to the same base table, IGF_SL_LOR_LOC_ALL (aliased LOC1 and LOC2), together with an inline aggregation subquery. The metadata lists no formally documented base objects, but the view text reveals the following dependencies:

Key Columns

  • DOCUMENT_ID_TXT — the loan document identifier used as a grouping key.
  • REP_ENTITY_ID_TXT — the reporting entity (campus or school code) to which the loan activity belongs.
  • FIN_AWARD_YEAR — the financial aid award year being summarized.
  • LOAN_TYPE — the raw code ('DLP', 'DLS', or 'DLU') from IGF_SL_LOR_LOC_ALL.
  • FIN_AWARD_TYPE — a decoded label derived via DECODE: 'DLS' → DLSubsidized, 'DLP' → DLPLUS, 'DLU' → DLUnsubsidized.
  • TOT_CNT_NUM — COUNT(DISTINCT BASE_ID), the number of distinct borrowers/awards in the group.
  • TOT_REP_AWD_AMT — SUM(LOAN_AMT_ACCEPTED), the total accepted award amount.
  • TOT_REP_DISB_AMT — SUM(NVL(DISB_AMT,0)), the total accepted disbursement amount.

Common Use Cases and Queries

Typical uses include award-versus-disbursement reconciliation, reporting Direct Loan volume by entity and award year, and feeding downstream summaries where only the three Direct Loan types are relevant (the view explicitly restricts LOAN_TYPE to 'DLP','DLS','DLU').

Retrieve all Direct Loan summary rows for a given award year:

  • SELECT rep_entity_id_txt, fin_award_year, fin_award_type, tot_cnt_num, tot_rep_awd_amt, tot_rep_disb_amt FROM apps.igf_sl_rep_smry_v WHERE fin_award_year = :year ORDER BY rep_entity_id_txt, fin_award_type;

Compare awards against disbursements to find under-disbursed loans:

  • SELECT rep_entity_id_txt, fin_award_type, tot_rep_awd_amt, tot_rep_disb_amt, (tot_rep_awd_amt - tot_rep_disb_amt) variance FROM apps.igf_sl_rep_smry_v WHERE (tot_rep_awd_amt - tot_rep_disb_amt) > 0;

Aggregate totals by loan type for a reporting entity:

  • SELECT loan_type, SUM(tot_cnt_num) borrowers, SUM(tot_rep_disb_amt) disbursed FROM apps.igf_sl_rep_smry_v WHERE rep_entity_id_txt = :entity GROUP BY loan_type;

Because results are already grouped by DOCUMENT_ID_TXT, REP_ENTITY_ID_TXT, FIN_AWARD_YEAR, and LOAN_TYPE, consumers should apply any further aggregation with these dimensions in mind.