Search Results st_resp_code

Overview

IGF_SL_RESP_DTLS_V is a reporting view within the Oracle E-Business Suite Financial Aid module (product code IGF), documented under the "Obsolete" designation in ETRM. Its stated purpose is to consolidate the response details for all XML documents received for the Federal Direct Loan program. The view is a denormalized, read-only construct that flattens the multi-level hierarchy of Direct Loan response processing — from the document envelope down through school, borrower, loan information, and loan-level response records — into a single row-per-response format suitable for inquiry, reconciliation, and downstream reporting.

The name itself decomposes into IGF (Financial Aid), SL (Student Loan), RESP (response), DTLS (details), and the V suffix denoting a database view. The column CRDT_DECISION_STATUS, which the user searched for, is one of the loan-level attributes exposed by this view; it carries the credit decision outcome returned on a Direct Loan response record.

Underlying Base Objects

The view is defined over six IGF base tables joined on a chain of surrogate and business keys, and completed by a UNION ALL with a second query block (the excerpt truncates the second block, but the structure mirrors the first). The documented base tables are:

• IGF_SL_CR_RESP_DTLS — the "Common Record" level detail; anchored by DOCUMENT_ID_TXT and providing the document envelope attributes.
• IGF_SL_RS_RESP_DTLS — the reporting-school response level (REP_SCHL_RESP_ID).
• IGF_SL_AS_RESP_DTLS — the attendance-school response level (ATD_SCHL_RESP_ID).
• IGF_SL_ST_RESP_DTLS — the student/borrower response level (STDNT_RESP_ID), holding SSN, name, and date of birth.
• IGF_SL_DI_RESP_DTLS — the loan information detail (DL_INFO_ID, LOAN_KEY_NUM) with fee percentages, rebate percentages, grade level, and print codes.
• IGF_SL_DL_RESP_DTLS — the Direct Loan response level (DL_LOAN_RESP_ID), carrying award, disbursement, MPN, and credit decision data.

The join path follows the natural hierarchy: CR.DOCUMENT_ID_TXT = RS.DOCUMENT_ID_TXT, RS.REP_SCHL_RESP_ID = ASH.REP_SCHL_RESP_ID, ASH.ATD_SCHL_RESP_ID = ST.ATD_SCHL_RESP_ID, then ST.STDNT_RESP_ID fans out to both DI and DL, with DI.LOAN_KEY_NUM = DL.LOAN_KEY_NUM completing the loan-level linkage. ETRM notes that the view is "not implemented in this database," meaning it may not exist in every environment; it is a legacy object retained for reference under the obsolete Financial Aid footprint.

Key Columns

The view exposes approximately sixty columns spanning the full response hierarchy:

• Document-level: DOCUMENT_ID_TXT, DOC_CREATED_DATE, SOURCE_ENTITY_ID_TXT, DEST_ENTITY_ID_TXT, SOFT_PROVIDER, SOFT_VERSION, FULL_RESP_CODE, RECEIPT_DATE, DOC_TYPE_CODE, DOC_STATUS_CODE, PROCESS_DATE.
• School-level: REP_SCHL_RESP_ID, REP_ENTITY_ID_TXT, RESP_CODE, ATD_SCHL_RESP_ID, ATD_ENTITY_ID_TXT.
• Student-level: STDNT_RESP_ID, S_SSN, FIRST_NAME, S_LAST_NAME, S_DATE_OF_BIRTH.
• Loan information: DL_INFO_ID, LOAN_KEY_NUM, ORIG_FEE_PCT_NUM, INT_REB_PCT_NUM, PNOTE_PRINT_CODE, DISCLOSURE_PRINT_CODE, GRADE_LEVEL_CODE.
• Direct Loan response: DL_LOAN_TYPE, FIN_AWD_YR, CPS_TRANS_NUM, AWARD_AMT, DEPNCY_STATUS, DEFAULT_STATUS, COD_AWARD_NUMBER_TXT, LOAN_NUMBER_TXT, ELEC_MPN_FLAG, PNOTE_MPN_ID, MPN_STATUS_CODE, MPN_LINK_FLAG, PYMT_SERVICER_AMT, PYMT_SERVICER_DATE, BOOK_LOAN_AMT, ENDORSER_AMT, and the credit decision trio: CRDT_DECISION_STATUS, CRDT_DECISION_DATE, CRDT_DECISION_OVRD_CODE.
• Audit/WHO columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID.

CRDT_DECISION_STATUS is the loan-level credit decision indicator (for example, approving or denying a PLUS loan based on the credit check), while CRDT_DECISION_DATE records when that determination was made and CRDT_DECISION_OVRD_CODE captures any manual override applied to the automated decision.

Common Use Cases and Queries

The primary use cases center on credit decision monitoring, direct loan reconciliation, and response audit trailing. Because the view is a single flattened row per response, it supports ad-hoc reporting without requiring callers to reconstruct the six-table join themselves.

Identifying credit decision outcomes by student and loan:

• SELECT S_SSN, S_LAST_NAME, LOAN_NUMBER_TXT, DL_LOAN_TYPE, CRDT_DECISION_STATUS, CRDT_DECISION_DATE FROM IGF_SL_RESP_DTLS_V WHERE CRDT_DECISION_STATUS IS NOT NULL ORDER BY CRDT_DECISION_DATE DESC;

Reviewing overrides applied to automated credit decisions:

• SELECT DOCUMENT_ID_TXT, S_LAST_NAME, LOAN_KEY_NUM, CRDT_DECISION_STATUS, CRDT_DECISION_OVRD_CODE FROM IGF_SL_RESP_DTLS_V WHERE CRDT_DECISION_OVRD_CODE IS NOT NULL;

Reconciling responses received within an award year:

• SELECT FIN_AWD_YR, SOURCE_ENTITY_ID_TXT, COUNT(*) RESP_COUNT, SUM(AWARD_AMT) TOTAL_AWARD FROM IGF_SL_RESP_DTLS_V GROUP BY FIN_AWD_YR, SOURCE_ENTITY_ID_TXT;

Because the view is marked obsolete and "not implemented in this database" per ETRM, queries should be validated against the actual environment before use. Where the view is absent, the same results can be obtained by reproducing the documented join across the six IGF_SL_*_RESP_DTLS tables.