Search Results igf_sl_dl_pnote_resp_all_u1

Overview

The IGF.IGF_SL_DL_PNOTE_RESP_ALL table is a core staging and response-tracking object within the Oracle E-Business Suite Financial Aid module (IGF), specifically supporting the Federal Direct Loan (DL) processing stream. It stores the response records returned from the Direct Loan Promissory Note Acknowledgement file that institutions upload back into the system after the Loan Origination Center (LOC) has processed paper or electronic Master Promissory Notes. This table therefore sits at the intersection of the outbound promissory note submission process and the inbound acknowledgement cycle, providing the data foundation for determining whether a given loan's promissory note has been accepted, rejected, or is still pending with the LOC.

From a dimensional modeling perspective, the metadata's Data Vault classification heuristic identifies this table as satellite-leaning. This is a reasonable modeling suggestion: the table records descriptive, time-stamped attributes (acknowledgement date, status, rejection codes, accepted amount) keyed to a primary surrogate identifier and a foreign key to the Direct Loan batch. It behaves less like an independent hub and more like a dependent satellite that captures the evolving state of promissory note acknowledgements tied to an upstream batch entity.

Key Information Stored

The table is defined with 21 columns in the ETRM 12.1.1 physical schema. The most significant columns include:

• DLPNR_ID – NUMBER(15), the surrogate primary key (IGF_SL_DL_PNOTE_RESP_ALL_PK) and the column behind the unique index IGF_SL_DL_PNOTE_RESP_ALL_U1. It uniquely identifies each promissory note response record.
• DBTH_ID – NUMBER(15), a foreign key to IGF_SL_DL_BATCH_ALL, linking each response to its originating Direct Loan batch.
• PNOTE_ACK_DATE – the date the LOC acknowledged the promissory note.
• PNOTE_BATCH_ID – VARCHAR2(40), the batch identifier for the promissory note batch, useful for reconciliation against LOC submission files.
• LOAN_NUMBER – VARCHAR2(30), the loan identifier, the primary business reference used when matching responses to awards.
• PNOTE_STATUS – VARCHAR2(30), the paper promissory note status at the LOC.
• PNOTE_REJ_CODES – VARCHAR2(30), the reason(s) a paper note was rejected.
• MPN_IND and ELEC_MPN_IND – indicators for Master Promissory Note and Electronic Master Promissory Note status.
• PNOTE_ACCEPT_AMT – NUMBER(15), the accepted loan amount.
• STATUS – VARCHAR2(30), the processing state of the record: Y (processed), N (yet to be processed), U (processed with update not taken), or I (invalid loan number).
• ORG_ID – the operating unit identifier.
• Standard Who columns – CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, plus the concurrent program columns REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

Typical use cases center on reconciliation and exception reporting. Financial aid administrators query this table to identify rejected promissory notes, unprocessed response records, and accepted amounts for disbursement validation. A common pattern retrieves rejected notes for a batch:

• SELECT LOAN_NUMBER, PNOTE_STATUS, PNOTE_REJ_CODES, PNOTE_ACK_DATE FROM IGF_SL_DL_PNOTE_RESP_ALL WHERE STATUS = 'I';
• Joining to the batch table to trace a response back to its submission: SELECT r.LOAN_NUMBER, b.* FROM IGF_SL_DL_PNOTE_RESP_ALL r, IGF_SL_DL_BATCH_ALL b WHERE r.DBTH_ID = b.DBTH_ID;
• Aggregating accepted amounts by acknowledgement date for reporting: SELECT TRUNC(PNOTE_ACK_DATE), SUM(PNOTE_ACCEPT_AMT) FROM IGF_SL_DL_PNOTE_RESP_ALL WHERE STATUS = 'Y' GROUP BY TRUNC(PNOTE_ACK_DATE);

Related Objects

• IGF.IGF_SL_DL_BATCH_ALL – the parent batch table; joined via DBTH_ID.
• IGF.IGF_SL_DL_PDET_RESP – references this table via DLPNR_ID, holding promissory note detail response lines.
• IGF.IGF_SL_DL_PNOTE_RESP_ALL_PK – the primary key constraint on DLPNR_ID.
• IGF.IGF_SL_DL_PNOTE_RESP_ALL_U1 – the unique index on DLPNR_ID, the object the user searched for.
• Concurrent program request tables referenced through REQUEST_ID and PROGRAM_APPLICATION_ID for tracing the import process that populated the acknowledgements.