Search Results igffv_ffelp_orig_responses

Overview

The view IGFFV_FFELP_ORIG_RESPONSES (owner APPS) is a Financial Aid module object within Oracle E-Business Suite, delivered under the IGF product. It is documented as the "Full View for the Entity that holds FFELP Origination Response Details." In the Federal Family Education Loan Program (FFELP) origination cycle, a school or servicer submits origination requests to a guarantor or the Common Origination and Disbursement (COD) process, and receives back response records confirming or adjusting the requested loan attributes. This view exposes the flattened, denormalized response side of that exchange, presenting borrower, student, loan, disbursement, and guarantee attributes in a single reporting surface.

Its role in EBS reporting and integration is primarily read-only: it serves as a data source for operational reports, reconciliation extracts, downstream interfaces, and ad hoc queries that need to reconcile what was returned by the guarantor against the institution's internal loan records. It is a "Full View," meaning it exposes the complete column set of the underlying entity rather than a narrow subset, which makes it suitable for both investigative querying and integration staging.

Underlying Base Objects

The ETRM metadata for this object records no documented referenced base objects, and the view text excerpt is expressed as a single SELECT over an alias RESP1 without a visible FROM clause or join graph. Consequently, the precise base table set cannot be enumerated from the provided metadata alone. In practice, the naming convention indicates a response-detail entity (the FFELP_ORIG_RESPONSES family) that stores parsed guarantor/COD origination response records, with the view layered directly over that entity.

Because the view is defined in the APPS schema and is reported as VALID, its dependency chain is maintained inside the EBS database; DBAs can enumerate actual dependencies through ALL_DEPENDENCIES and ALL_VIEWS where ETRM does not document them. The view text also performs inline lookup translation rather than separate joins visible in the excerpt.

Key Columns

The column list reflects the structure of an FFELP origination response. Grouped by purpose:

• Response control: REC_CODE, REC_TYPE_IND — identify the record and its type within the response file.
• Borrower identity: B_FIRST_NAME, B_LAST_NAME, B_MIDDLE_NAME, B_SSN, B_DATE_OF_BIRTH, plus permanent address, city, state, ZIP, and phone fields.
• Loan request and certification: CL_LOAN_TYPE, REQ_LOAN_AMT, LOAN_NUMBER, CL_SEQ_NUMBER, LOAN_PER_BEGIN_DATE, LOAN_PER_END_DATE.
• Need and cost analysis: COA_AMT, EFC_AMT, EST_FA_AMT, and the certified amounts FLS_CERT_AMT (subsidized), FLU_CERT_AMT (unsubsidized), FLP_CERT_AMT (PLUS), and ALT_CERT_AMT.
• Approved amounts: FLS_APPROVED_AMT, FLU_APPROVED_AMT, FLP_APPROVED_AMT, ALT_APPROVED_AMT.
• Guarantee data: LENDER_ID, GUARANTOR_ID, GUARANTEE_DATE, and the column the user searched for, GUARANTEE_AMT — the guaranteed principal amount returned on the origination response.
• Student attributes: S_LAST_NAME, S_FIRST_NAME, S_MIDDLE_NAME, S_SSN, S_DATE_OF_BIRTH, S_SIGNATURE_CODE.
• Lookup-translated columns: expressions such as _LA:RESP1.B_CITIZENSHIP_STATUS:IGF_LOOKUPS_VIEW:IGF_SL_CITIZENSHIP_STAT:MEANING resolve codes to meanings via IGF_LOOKUPS_VIEW for citizenship, default status, grade level, enrollment, and serial loan code.
• Flags and indicators: BORW_INTEREST_IND, EFT_AUTH_CODE, B_INDICATOR_CODE, LEND_BLKT_GUARNT_IND, GUARNT_ADJ_IND, ALT_APPL_VER_CODE, FED_APPL_FORM_CODE, DEFER_REQ_CODE.

Common Use Cases and Queries

Typical uses include reconciling guaranteed amounts against certified and approved amounts, verifying guarantor-assigned loan numbers, extracting guarantee dates for financial reporting, and auditing adjustments flagged by GUARNT_ADJ_IND.

To inspect guarantee amounts by loan type for a period:

• SELECT LOAN_NUMBER, CL_LOAN_TYPE, REQ_LOAN_AMT, GUARANTEE_AMT, GUARANTEE_DATE, GUARANTOR_ID FROM APPS.IGFFV_FFELP_ORIG_RESPONSES WHERE LOAN_PER_BEGIN_DATE >= :p_start;

To compare certified versus guaranteed principal and surface shortfalls:

• SELECT LOAN_NUMBER, LENDER_ID, GUARANTOR_ID, (FLS_CERT_AMT + FLU_CERT_AMT) CERT_TOTAL, GUARANTEE_AMT FROM APPS.IGFFV_FFELP_ORIG_RESPONSES WHERE GUARNT_ADJ_IND = 'Y';

Because lookup columns are already translated, reports can select the derived meaning fields directly without additional joins to IGF_LOOKUPS_VIEW, simplifying extract logic for downstream interfaces and reconciliation worksheets.