Search Results cod_award_number_txt

Overview

The IGF.IGF_SL_DL_RESP_DTLS table is a core student lending response detail table within the Oracle E-Business Suite Financial Aid module, owned by the IGF schema. It stores the Direct Loan response elements — specifically the "DLU", "DLS", and "DLP" tag responses — that are parsed from the Common Origination and Disbursement (COD) response XML files returned to the institution after submitting loan origination and disbursement records. In the context of ETRM 12.1.1 and 12.2.2, this table functions as the persisted landing zone for borrower-level, award-level, and loan-identifier-level detail received back from the federal processor.

From a Data Vault modeling perspective, the heuristic classification for this object is hub-leaning. The table is keyed by a single surrogate primary key, DL_LOAN_RESP_ID, and carries a substantial payload of descriptive borrower, MPN (Master Promissory Note), credit-decision, and award attributes. This suggests the object mixes a business-key hub (the loan response identity) with satellite-style descriptive attributes. When designing a dimensional or Data Vault model, analysts should treat DL_LOAN_RESP_ID as the hub key and separate the borrower/MPN columns into satellites to preserve historical change tracking.

Key Information Stored

The table contains 72 documented columns. The most significant are summarized below.

• DL_LOAN_RESP_ID (NUMBER) — Surrogate primary key, enforced by the unique index IGF_SL_DL_RESP_DTLS_PK, identifying each direct loan response record.
• STDNT_RESP_ID (NUMBER) — Foreign key linking to the student-level response record; distinguishes the student hub from the loan detail record.
• DL_LOAN_TYPE (VARCHAR2 30) — Identifies the loan program type: 'DLU' (unsubsidized), 'DLS' (subsidized), or 'DLP' (PLUS).
• LOAN_KEY_NUM (NUMBER) — The internal loan key supplied by COD.
• COD_AWARD_NUMBER_TXT (NUMBER) — Documented as the last three digits of the Direct Loan Loan Identifier. This is the column the user searched for.
• LOAN_NUMBER_TXT (VARCHAR2 30) — The unique full Direct Loan Loan Identifier.
• FIN_AWD_YR (VARCHAR2 30) — Award year associated with the loan.
• CPS_TRANS_NUM (NUMBER) — Transaction number from the eligible ISIR used to calculate the award.
• AWARD_AMT (NUMBER) — Total award amount the student is eligible to receive, as determined by the school.
• AWARD_CREATE_DATE (DATE) — Date the school created the award record.
• DEPNCY_STATUS (VARCHAR2 30) — Student dependency status code.
• DEFAULT_STATUS and B_DEFAULT_STATUS (VARCHAR2 30) — Default/overpayment code status at loan and borrower level.
• MPN_STATUS_CODE, ELEC_MPN_FLAG, PNOTE_MPN_ID — Master Promissory Note status, electronic MPN flag, and MPN identifier.
• CRDT_DECISION_STATUS and CRDT_DECISION_DATE — Plus-loan credit decision outcome and its date.
• B_SSN, B_FIRST_NAME, B_LAST_NAME, B_BIRTH_DATE — Borrower identity attributes.

Note that COD_AWARD_NUMBER_TXT is described as storing the last three digits of the loan identifier, while LOAN_NUMBER_TXT holds the complete identifier. Business-key candidates are limited to the surrogate PK; no independent business unique index is documented beyond IGF_SL_DL_RESP_DTLS_PK.

Common Use Cases and Queries

The table is primarily queried for COD reconciliation, award reporting, and borrower-level analytics. A typical query joins response details to student responses and filters by award year:

• Reconciling loan awards by type and award year: SELECT dl_loan_type, COUNT(*), SUM(award_amt) FROM igf.igf_sl_dl_resp_dtls WHERE fin_awd_yr = :yr GROUP BY dl_loan_type;
• Locating a loan by its COD identifier: SELECT dl_loan_resp_id, loan_number_txt, cod_award_number_txt FROM igf.igf_sl_dl_resp_dtls WHERE loan_number_txt = :loan_id;
• Retrieving borrower MPN status for PLUS loans: filter DL_LOAN_TYPE = 'DLP' and report MPN_STATUS_CODE and CRDT_DECISION_STATUS.
• Auditing processing results through the dependent edit results table (see below) to identify rejected or edited response rows.

Because the table stores COD response payloads, reporting often drives disbursement validation and exception handling.

Related Objects

• IGF.IGF_SL_DLDB_RSP_DTL — References this table through DL_LOAN_RESP_ID; holds disbursement-level response detail for each loan response.
• IGF.IGF_SL_ED_PRC_RSLTS — References this table through EDIT_ID; stores edit and processing results generated against the response record.
• IGF.IGF_SL_DL_RESP_DTLS (self-referenced via STDNT_RESP_ID) — Links to the student response parent, forming the hub-to-detail chain.
• COD import/parse concurrent programs — the XML loaders that populate DLU, DLS, and DLP response rows.

Together these objects provide the full origination-to-disbursement reconciliation chain required for Title IV compliance in Oracle EBS Financial Aid.