Search Results dl_loan_resp_id

Overview

IGF_SL_DL_RESP_DTLS is a table in the IGF (Financial Aid) product schema of Oracle E-Business Suite, documented as valid in both release 12.1.1 and 12.2.2. It stores the 'DLU', 'DLS', and 'DLP' tag response elements parsed from the Common Origination and Disbursement (COD) response XML file received from the U.S. Department of Education for Direct Loan processing. In practical terms, the table acts as the staging and persistence layer for borrower-level and endorser-level attributes returned by the federal processor, including demographic, address, credit decision, and promissory note data.

The ETRM relationship metadata suggests a hub-leaning Data Vault classification, meaning IGF_SL_DL_RESP_DTLS is best modeled as a hub candidate whose primary key — DL_LOAN_RESP_ID — is the durable business identifier carried across integrations. Reference data such as addresses, phone numbers, and MPN statuses that arrive on the same response would, under a strict Data Vault design, be separated into satellite structures. This classification is a heuristic derived from the foreign key topology and should be treated as a modeling suggestion rather than a documented implementation constraint.

Key Information Stored

The table contains 72 documented columns. The single-column primary key, IGF_SL_DL_RESP_DTLS_PK, is defined on DL_LOAN_RESP_ID, which serves as the surrogate identifier for each loan response detail row. The foreign key to IGF_SL_DLDB_RSP_DTL via STDNT_RESP_ID ties the row back to the parent response header, while ED-related processing results reference the row through EDIT_ID.

• DL_LOAN_RESP_ID — primary key and surrogate identifier for the response detail record.
• STDNT_RESP_ID — foreign key linking the detail record to its parent response in IGF_SL_DLDB_RSP_DTL.
• DL_LOAN_TYPE, LOAN_KEY_NUM, FIN_AWD_YR, CPS_TRANS_NUM, AWARD_AMT — loan identification, award year, and award amount returned by COD.
• COD_AWARD_NUMBER_TXT, LOAN_NUMBER_TXT, AWARD_CREATE_DATE — COD-assigned award and loan reference values.
• B_SSN, B_BIRTH_DATE, B_FIRST_NAME, B_MIDDLE_NAME, B_LAST_NAME — borrower identity attributes from the response XML.
• B_PERMT_ADDR_* and B_TEMP_ADDR_* — permanent and temporary borrower address components, including foreign flag, type code, line 1–3, city, state, postal code, county, and country.
• B_PHONE_NUMBER_1, B_PHONE_NUMBER_2, B_PHONE_NUMBER_3, B_EMAIL_ADDRESS — borrower contact data.
• B_CITZN_STATUS_CODE, B_DEFAULT_STATUS, B_RESP_CODE — borrower citizenship, default, and response status indicators.
• ELEC_MPN_FLAG, PNOTE_MPN_ID, MPN_STATUS_CODE, MPN_LINK_FLAG — Master Promissory Note electronic flag, identifier, status, and link indicator.
• CRDT_DECISION_STATUS, CRDT_DECISION_DATE, CRDT_DECISION_OVRD_CODE — credit decision outcome for PLUS and endorser scenarios.
• RESP_CODE, STATUS_CODE — overall response and status codes for the detail record.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID — standard EBS audit and concurrent program context columns.

Common Use Cases and Queries

The most frequent use case is reconciliation of federal COD responses against locally originated Direct Loan awards. Financial aid administrators query by award year, borrower SSN, or COD award number to verify that the borrower data returned by COD matches the institution's records.

A typical join returns response details with their parent header:

• SELECT d.DL_LOAN_RESP_ID, d.STDNT_RESP_ID, d.COD_AWARD_NUMBER_TXT, d.LOAN_KEY_NUM, d.AWARD_AMT, d.MPN_STATUS_CODE FROM IGF.IGF_SL_DL_RESP_DTLS d WHERE d.FIN_AWD_YR = :award_year AND d.B_SSN = :ssn;

Additional scenarios include monitoring MPN status (ELEC_MPN_FLAG, MPN_STATUS_CODE) for electronic promissory note completion, tracking credit decision outcomes for PLUS loan borrowers, and generating exception reports where RESP_CODE or STATUS_CODE indicates a rejection or correction. Disaster recovery and borrower outreach workflows often select the B_PERMT_ADDR and B_TEMP_ADDR columns to refresh contact information returned by COD. Batch reconciliation joins against IGF_SL_ED_PRC_RSLTS on EDIT_ID are used to correlate edit processing results with their originating detail record.

Related Objects

The following objects are documented as related through primary and foreign key definitions:

• IGF_SL_DLDB_RSP_DTL — parent response detail table; joined on IGF_SL_DLDB_RSP_DTL.DL_LOAN_RESP_ID = IGF_SL_DL_RESP_DTLS.STDNT_RESP_ID.
• IGF_SL_ED_PRC_RSLTS — edit processing results table; joined on IGF_SL_ED_PRC_RSLTS.EDIT_ID = IGF_SL_DL_RESP_DTLS.DL_LOAN_RESP_ID.
• IGF_SL_DL_RESP_DTLS_PK — the unique index enforcing the primary key on DL_LOAN_RESP_ID.

Other Direct Loan processing tables in the IGF_SL_* family, such as loan origination and disbursement entities, reference this table indirectly through the parent response hierarchy. Concurrent programs that import COD response XML populate this table and its parent, so the concurrent request context columns (REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID) also link the data back to the EBS concurrent manager request log for traceability.