Search Results mpn_reject_code

Overview

The table IGF.IGF_SL_LI_DLOR_INTS is a public interface (staging) table in the Oracle E-Business Suite Financial Aid module (product code IGF). It is described in ETRM documentation as the Legacy Direct Loans Origination Interface Table, and it is registered under the display name "Legacy - Direct Loan Origination and Disbursement Data Import Process." Its lifecycle status is active, and it resides in the APPS_TS_INTERFACE tablespace, the standard location for inbound interface data before it is validated and promoted into the core application tables.

Functionally, the table captures Direct Loan and Common Line loan origination, disbursement, and promissory note (MPN) data received from external loan servicing systems and legacy sources. Records are grouped into processing batches, imported, status-tracked, and then consumed by concurrent programs that move the data into the production financial aid tables. The table therefore serves as a transient yet auditable landing zone for the loan origination lifecycle.

The metadata's Data Vault classification heuristic reports this object as standalone. In Data Vault modeling terms, this suggests it behaves less like a classic hub or link and more like a batch-oriented staging satellite or multi-active staging structure, keyed by a compound business key. This classification is a modeling suggestion only; the physical table is a flat interface object with no documented foreign key dependencies.

Key Information Stored

The table exposes 61 documented columns in the 12.1.1 physical schema. The most operationally significant are summarized below.

• BATCH_NUM – Numeric batch identifier (15 digits) used to group records into a processing run; indexed by IGF_SL_LI_DLOR_INTS_N2.
• CI_ALTERNATE_CODE – Alternate batch-year code (10 characters), part of the unique primary key.
• PERSON_NUMBER – Student person number (30 characters), part of the unique primary key.
• AWARD_NUMBER_TXT – Award number (15 characters), part of the unique primary key.
• BORR_PERSON_NUMBER – Borrower person number, which may differ from the student.
• LOAN_NUMBER_TXT – Unique identifier for the Direct Loan or Common Line loan.
• LOAN_SEQ_NUM – Sequence of loans within the current loan type for the processing year.
• LOAN_PER_BEGIN_DATE / LOAN_PER_END_DATE – Loan period boundaries.
• LOAN_STATUS_CODE / LOAN_STATUS_DATE – Current loan status and the date it changed.
• IMPORT_STATUS_TYPE – Import processing status; indexed by IGF_SL_LI_DLOR_INTS_N1 and central to batch monitoring.
• ORIG_SEND_BATCH_ID_TXT – Identifier of the outbound origination send batch, used to correlate transmitted origination records with their acknowledgements.
• ORIG_BATCH_DATE / ORIG_ACKNOWLEDGEMENT_DATE – Origination send date and response/acknowledgement date.
• PNOTE_STATUS_CODE, PNOTE_ID_TXT, MPN_ACKNOWLEDGEMENT_DATE, MPN_REJECT_CODE – Promissory note status, identifier, and MPN acknowledgement details.
• GRADE_LEVEL_CODE – Student grade level at the time of origination.
• ACTIVE_FLAG / ACTIVE_DATE – Whether the loan is active and the activation date.

The surrogate primary key is enforced through the unique index IGF_SL_LI_DLOR_INTS_PK, while the business-key candidates are the composite of CI_ALTERNATE_CODE, PERSON_NUMBER, and AWARD_NUMBER_TXT. The user's search term, orig_send_batch_id_txt, maps directly to the ORIG_SEND_BATCH_ID_TXT column, which supports reconciliation between origination submissions and their acknowledgements.

Common Use Cases and Queries

The most frequent scenario is monitoring batch import progress. For example, a report can surface records that remain unprocessed for a given batch:

• Batch status reporting: SELECT BATCH_NUM, IMPORT_STATUS_TYPE, COUNT(*) FROM IGF.IGF_SL_LI_DLOR_INTS GROUP BY BATCH_NUM, IMPORT_STATUS_TYPE;
• Origination reconciliation: SELECT ORIG_SEND_BATCH_ID_TXT, PERSON_NUMBER, AWARD_NUMBER_TXT, ORIG_ACKNOWLEDGEMENT_DATE FROM IGF.IGF_SL_LI_DLOR_INTS WHERE ORIG_SEND_BATCH_ID_TXT = :batch_id;
• Borrower loan detail: join on PERSON_NUMBER and LOAN_NUMBER_TXT to retrieve loan periods, status, and promissory note state for a student.
• Exception handling: filter by ORIG_REJECT_CODE or MPN_REJECT_CODE to identify rejected originations requiring correction.
• Error recovery: purge or reprocess records where IMPORT_STATUS_TYPE indicates a failure, scoped to a single BATCH_NUM.

Typical reporting uses include loan volume by processing year (via CI_ALTERNATE_CODE), outstanding MPN acceptances (PNOTE_ACCEPT_AMT, PNOTE_ACCEPT_DATE), and disbursement reconciliation against ACAD_BEGIN_DATE and ACAD_END_DATE.

Related Objects

The metadata records no foreign keys, so this table is treated as standalone. In practice, integration occurs through shared business keys and concurrent import processes rather than declared constraints.

• IGF_SL_LI_DLOR_INTS_PK – Unique index enforcing the composite primary key.
• IGF_SL_LI_DLOR_INTS_N1 – Non-unique index on IMPORT_STATUS_TYPE for status filtering.
• IGF_SL_LI_DLOR_INTS_N2 – Non-unique index on BATCH_NUM for batch retrieval.
• Student/person registry tables – Joined on PERSON_NUMBER and BORR_PERSON_NUMBER.
• Award tables – Linked through AWARD_NUMBER_TXT.
• Loan and disbursement tables – Populated from this interface after import.
• MPN/promissory note tables – Correlated by PNOTE_ID_TXT and MPN_ACKNOWLEDGEMENT_DATE.
• Concurrent request tables – Linked via REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID to identify the import job that produced each row.

Together these objects support the Legacy Direct Loan Origination and Disbursement Data Import Process, with this interface table acting as the controlled entry point for external loan data into Oracle EBS.