Search Results inv_trl_num

Overview

The APPS.IGF_DB_DL_RECONC package body forms part of the Oracle E-Business Suite Financial Aid / Student Systems module, operating within the IGF (Institution of Higher Education Financial Aid) application schema. Its principal business function is to reconcile and load disbursement-related data extracted from external files, particularly those associated with the U.S. Department of Education's Direct Loan (DL) program. The package is responsible for validating inbound flat-file structures — including header and trailer records — before committing disbursement records to the staging and processing tables used by the downstream disbursement creation logic.

The package header, dated 22-January-2002 and last amended under version 120.3, carries the header comment "To load the," confirming its role as a loader/reconciler. Change history records show refinements for the 2003–2004 and 2004–2005 award years, a fix under Bug 3102439 for multiple Federal Aid (FA) offices, and handling of school-type and school-code parameters in place of internal school identifiers.

Key Procedures and Functions

Two procedures are formally documented through the ETRM classification: MAIN_SMR and MAIN_DTL. These are the entry points invoked to process the summary (header) and detail (disbursement) levels of an inbound DL file respectively.

• MAIN_SMR — Processes the summary-level record. Following the change history, this routine was modified under Bug 3102439 to accept school_type and p_school_code parameters, replacing prior references to an internal school_id, thereby supporting environments with multiple FA offices.
• MAIN_DTL — Processes the detail-level disbursement records. It likewise received the school_type and p_school_code parameters under the same fix.

The body also declares several private validation functions, including valid_header and valid_trailer, which return BOOLEAN flags and populate batch identifiers, message classes, creation dates, rejection codes, and record counters. Exception handlers such as INV_HDR_OR_TLR, INVALID_FILE, and INV_TRL_NUM signal invalid header/trailer conditions or file-level failures during reconciliation.

Tables Accessed

The package interacts with two principal documented tables, accessed through APPS synonyms:

• IGF_SL_DL_FILE_TYPE — Defines the recognized file types that the loader can process; the file_type parameter is drawn from this table to determine the layout and validation rules applied.
• IGF_SL_LOAD_FILE_T — The load-file control table that governs which files are eligible for reconciliation and drives the valid_header logic.

Additional declared variables, such as batch_id, message_class, and batch_rej_code, are typed against columns of staging structures like igf_sl_dl_batch, indicating that the package writes batch metadata consumed by subsequent processing. The documentation does not enumerate further tables, and readers should treat any additional table references as undocumented.

Usage Notes

Because the package is classified as "OTHER" and is not referenced by any other documented package, it is not exposed as a published PL/SQL API. It is most likely invoked by a concurrent program or a shell/SQL*Loader wrapper that calls the MAIN_SMR and MAIN_DTL procedures after a Direct Loan disbursement file has been staged in the load-file directory. The typical invocation sequence is: the loader stages the external DL file, the package validates the header and trailer, and on success the summary and detail routines reconcile the records against the expected batch, updating batch status and rejection codes accordingly. The search term "record_data" corresponds to the record-level data processed by these detail routines during reconciliation. Custom code should not call this package directly; it is intended as an internal implementation unit of the IGF disbursement load process.