Search Results dl_file_type

Overview

IGF_SL_DL_FILE_TYPE is a configuration table in the IGF (Financial Aid) product module of Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the mapping between a file specification version number and the corresponding message class, batch type, and record layout attributes used by the Student Loan (SL) Direct Loan (DL) processing component. In practice, this table acts as a control reference that the Direct Loan outbound and inbound file generation routines consult to determine how a given loan file version should be formatted and routed.

The catalogued description states that the table "contains the mapping from file specification version number to message class and batch identifier." This makes it a low-volume, relatively static reference table that is typically seeded or maintained by administrators rather than populated through day-to-day transactional activity. Rows change only when a new federal file specification version, loan category, or batch convention must be supported.

The heuristic Data Vault classification mined from the foreign key structure is standalone. As a modeling suggestion, this indicates the table functions independently, without documented parent-child dependencies to other hubs or links, and would most naturally be represented as a reference or lookup construct rather than as a hub, link, or satellite within a Data Vault model.

Key Information Stored

The physical schema documented for 12.1.1 exposes twelve columns. The most significant business columns are:

• DL_VERSION — the file specification version number. This is the central discriminator that identifies which version of the Direct Loan file format a row governs.
• DL_FILE_TYPE — the file type (for example, a specific submission or disbursement file category) associated with the version.
• DL_LOAN_CATG — the loan category, allowing distinct mappings for different loan programs.
• MESSAGE_CLASS — the message class resolved for the version/type/category combination; a key output of the mapping.
• BATCH_TYPE — the batch identifier that determines how records are grouped and transmitted.
• CYCLE_YEAR — the award or processing cycle year to which the configuration applies.
• REC_LENGTH — the expected record length for the file, used for fixed-width parsing and validation.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Oracle EBS audit columns capturing who created and last modified each row and when.

The primary key is IGF_SL_DL_FILE_TYPE_PK, defined over the composite business key (DL_VERSION, DL_FILE_TYPE, DL_LOAN_CATG). A unique index, IGF_SL_DL_FILE_TYPE_U1, covers the same three columns. Because the primary key is a composite natural key rather than a single surrogate identifier, the business-key candidates and the primary key coincide; there is no separate synthetic surrogate column documented.

Common Use Cases and Queries

Typical usage centers on resolving the correct message class and batch configuration for a given file version before generating or interpreting a Direct Loan file. A representative query retrieves the mapping for a specific version and loan category:

• SELECT DL_VERSION, DL_FILE_TYPE, DL_LOAN_CATG, MESSAGE_CLASS, BATCH_TYPE, REC_LENGTH FROM IGF.IGF_SL_DL_FILE_TYPE WHERE DL_VERSION = :p_version AND DL_LOAN_CATG = :p_category;
• Cycle-year filtering: adding CYCLE_YEAR = :p_year restricts configuration to the active processing cycle.
• Audit reporting: selecting CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, and LAST_UPDATE_DATE to track when file-format configurations were introduced or revised.
• Validation queries: detecting duplicate or missing version/type/category combinations ahead of a federal reporting cycle, and verifying that REC_LENGTH matches the actual generated file width.

Reporting use cases include producing a cross-reference of supported file specification versions, and confirming that every active loan category has a corresponding message class and batch type before a production run.

Related Objects

The mined relationship data classifies IGF_SL_DL_FILE_TYPE as standalone, meaning no foreign keys are documented linking it to parent tables. Its integration with the rest of the Financial Aid module is therefore driven primarily through its composite business key rather than enforced referential constraints. Related objects are consequently inferred from shared key columns and functional roles within IGF Student Loan processing:

• IGF_SL_DL_* transactional tables — Direct Loan disbursement and origination tables that carry DL_VERSION, DL_FILE_TYPE, and DL_LOAN_CATG values resolved against this configuration.
• Student Loan file generation concurrent programs — the batch processes that read MESSAGE_CLASS, BATCH_TYPE, and REC_LENGTH to format outbound files.
• Inbound Direct Loan response/acknowledgement processing — routines that use the version mapping to parse returned files.
• IGF lookup and reference tables — supporting code lists for loan category and file type values referenced by these columns.
• Standard EBS audit and concurrent manager views — including FND_USER joins on CREATED_BY and LAST_UPDATED_BY for administration reporting.

Because the object is standalone, joins to related tables are performed on the shared DL_VERSION, DL_FILE_TYPE, and DL_LOAN_CATG columns rather than through declared foreign keys.