Search Results ver_status_code

Overview

IGF_GR_REP_PELL_V is a reporting view owned by the APPS schema within the IGF (Financial Aid) product module of Oracle E-Business Suite. It exposes Pell Grant reporting data associated with the Cod detail records used by the financial aid grant reporting process. The "GR" prefix denotes the Grant Reporting family of objects, "REP" indicates it is intended for reporting purposes, and "PELL" identifies the specific federal Pell Grant program context. In both EBS 12.1.1 and 12.2.2, the object is registered as VALID.

The view's role is to flatten and normalize the underlying Pell Grant detail storage into a form suitable for downstream reporting, extracts, and integration with external agencies or COD (Common Origination and Disbursement) processing. It presents a single, deduplicated row per unique combination of reporting-relevant attributes, making it well suited for concurrent program extracts, XML Publisher reports, and interface staging.

Underlying Base Objects

The view is defined over a single documented base table, IGF_GR_COD_DTLS. No other base objects are documented in the ETRM metadata. The defining SELECT retrieves columns from IGF_GR_COD_DTLS and applies a GROUP BY over every selected expression, which effectively performs a DISTINCT-style deduplication of the source rows. This design suggests IGF_GR_COD_DTLS may hold duplicated or multi-context rows that the view consolidates into one logical record per Pell Grant reporting entity combination.

The GROUP BY list includes all trimmed text identifiers, the BASE_ID and AWARD_ID keys, FIN_AWARD_YEAR, CPS_TRANS_NUM, and the monetary and status attributes. Because the grouping spans the full projection, the view does not aggregate sums or counts; it exists purely to return a normalized, one-row-per-distinct-combination result set.

Key Columns

• DOCUMENT_ID_TXT, REP_ENTITY_ID_TXT, ATD_ENTITY_ID_TXT — Trimmed character identifiers for the document, reporting entity, and attended entity. TRIM is applied to eliminate padding introduced by fixed-length storage.
• BASE_ID, AWARD_ID — Internal identifiers linking the record to the financial aid base record and the specific award.
• FIN_AWARD_YEAR — The financial aid award year for which the Pell data applies.
• CPS_TRANS_NUM — the CPS transaction number. This is the column users most often search for, since it identifies the specific ISIR/CPS transaction against which the Pell award was calculated and reported.
• AWARD_AMT, COA_AMT, LOW_TUTION_FEE — Monetary attributes: the award amount, cost of attendance, and the low-tuition fee value used in Pell calculation.
• INCARC_FLAG — Decoded from the source flag into the literal strings TRUE or FALSE using a DECODE on 'Y' and 'F'.
• VER_STATUS_CODE — Verification status code for the record.
• ENROLLMENT_DATE — Converted to a character string in the format YYYY-MM-DD via TO_CHAR, rather than returned as a native date.
• SEC_EFC_CODE — Secondary Expected Family Contribution code.
• NOTE_MESSAGE — Free-text note associated with the record.

Note that the metadata's column list also references ATTENDANCE_COST, which corresponds to the COA_AMT projection in the view text.

Common Use Cases and Queries

Typical uses include Pell Grant reconciliation, COD extract verification, and troubleshooting of CPS transaction mismatches. Because CPS_TRANS_NUM is grouped, it can be used to isolate the specific transaction that produced a reported award.

Retrieve all rows for a given award year and institution entity:

SELECT document_id_txt, award_id, cps_trans_num, award_amt, coa_amt
FROM   apps.igf_gr_rep_pell_v
WHERE  fin_award_year = :p_year
AND    rep_entity_id_txt = :p_entity;

Locate records by CPS transaction number:

SELECT base_id, award_id, cps_trans_num, enrollment_date, ver_status_code
FROM   apps.igf_gr_rep_pell_v
WHERE  cps_trans_num = :p_cps_trans_num;

Identify incarcerated-student records, exploiting the decoded flag:

SELECT document_id_txt, award_id, cps_trans_num, sec_efc_code
FROM   apps.igf_gr_rep_pell_v
WHERE  incarc_flag = 'TRUE';