Search Results rec_length

Overview

IGF.IGF_SL_DL_FILE_TYPE is a seed data table in the Oracle E-Business Suite application schema IGF, associated with the Oracle Financials for the United States Direct Loan processing module. Its documented purpose is to hold the mapping from a file specification version number to a message class and batch identifier. In practical terms, the table acts as a control and reference register that describes each Direct Loan file layout the application recognizes, including the physical record length of the file and the batch context in which the file is processed. It resides in the APPS_TS_SEED tablespace with a PCT Free of 10, consistent with low-volatility, setup-oriented data that is populated at implementation or patch time rather than by day-to-day transaction entry.

Per the heuristic Data Vault classification mined from the foreign key structure, this object is a standalone entity. A modeling suggestion, therefore, is to treat IGF_SL_DL_FILE_TYPE as a hub-like reference table: it has no inbound foreign key dependencies on other database objects and is not documented as referencing any other database object. It is best understood as a descriptive dimension that qualifies inbound or outbound Direct Loan files rather than as a transactional link or a dependent satellite.

Key Information Stored

The documented primary key is IGF_SL_DL_FILE_TYPE_PK, defined on the composite of DL_VERSION, DL_FILE_TYPE, and DL_LOAN_CATG. The metadata records no separate surrogate key column for this table; the composite primary key itself functions as the unique row identifier. The unique index IGF_SL_DL_FILE_TYPE_U1 is defined on the identical three-column combination in the same APPS_TS_SEED tablespace, confirming that this triplet is the business key and that no two rows may share the same version, file type, and loan category.

• DL_VERSION (VARCHAR2(30)) — the Direct Loan version number for the file; part of the primary key.
• DL_FILE_TYPE (VARCHAR2(30)) — the file type, for example Direct Loan origination send file, origination acknowledgement file, change send file, or change acknowledgement file; part of the primary key.
• DL_LOAN_CATG (VARCHAR2(30)) — the loan category carried in the file; part of the primary key.
• MESSAGE_CLASS (VARCHAR2(30)) — the message class file name as recognized by LOC.
• BATCH_TYPE (VARCHAR2(30)) — the batch type specified for each file.
• CYCLE_YEAR (VARCHAR2(30)) — the year associated with the file specification.
• REC_LENGTH (NUMBER(15)) — the record length for the file; this is the attribute most directly associated with fixed-width parsing and validation of Direct Loan output.

The remaining columns are the standard Who columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — which provide audit lineage for each setup row and are populated automatically by the application.

Common Use Cases and Queries

The most frequent use of this table is to resolve the correct record length and batch context for a given Direct Loan file version, file type, and loan category before generation or parsing of a flat file. A typical lookup query is:

SELECT DL_VERSION, DL_FILE_TYPE, DL_LOAN_CATG,
       MESSAGE_CLASS, BATCH_TYPE, CYCLE_YEAR, REC_LENGTH
FROM   IGF.IGF_SL_DL_FILE_TYPE
WHERE  DL_VERSION = :p_version
AND    DL_FILE_TYPE = :p_file_type
AND    DL_LOAN_CATG = :p_loan_catg;

Because the table is small and seed-populated, reporting scenarios usually involve configuration verification. Common questions include which file types are configured for a given cycle year, which message classes are associated with acknowledgement files, and whether record lengths differ across loan categories for the same version. A validation pattern for confirming completeness of setup is:

SELECT DL_VERSION, CYCLE_YEAR, COUNT(*) file_type_count
FROM   IGF.IGF_SL_DL_FILE_TYPE
GROUP  BY DL_VERSION, CYCLE_YEAR;

Typically, joins are performed to the three key columns rather than to surrogate identifiers, since no surrogate key is documented.

Related Objects

The documentation states that IGF.IGF_SL_DL_FILE_TYPE does not reference any database object, and lists the APPS synonym IGF_SL_DL_FILE_TYPE as the referencing object. Join relationships are therefore driven by the composite business key rather than by enforced foreign keys. The most significant objects to consider alongside it are the APPS synonym that exposes it to application code, any Direct Loan processing package or concurrent program that reads the version, file type, and loan category to determine record length, and the LOC message class definitions referenced by MESSAGE_CLASS. Because the ETRM metadata identifies no dependent child tables, integrations should join IGF_SL_DL_FILE_TYPE to downstream file-processing tables on the DL_VERSION, DL_FILE_TYPE, and DL_LOAN_CATG columns when correlating a generated or received file with its specification. Oracle Proprietary, Confidential Information — Legal Notices apply.