Search Results ar_trans_record_formats




Overview
Explore in AI Chat

Overview

The AR_TRANS_RECORD_FORMATS table is a core configuration object within the Oracle E-Business Suite Receivables (AR) module, specifically for the Lockbox processing feature. It defines the structure and format of individual record types that comprise a complete Lockbox transmission file. A Lockbox transmission format, defined in the parent AR_TRANSMISSION_FORMATS table, is composed of one or more record formats stored in this table. These records typically correspond to different sections of a bank file, such as a header record, detail (payment) records, and a trailer record. The table is essential for the system to correctly parse, validate, and process incoming electronic payment data from banking institutions, automating the cash application process.

Key Information Stored

The table stores metadata that describes each record type within a transmission format. Key columns include RECORD_FORMAT_ID, the primary key surrogate identifier; TRANSMISSION_FORMAT_ID, a foreign key linking to AR_TRANSMISSION_FORMATS; RECORD_IDENTIFIER, which holds the literal character or value in the transmission file that identifies this record type (e.g., 'H' for Header); and RECORD_TYPE_LOOKUP_CODE, which classifies the record's purpose (e.g., 'HEADER', 'PAYMENT', 'TRAILER'). Other columns typically define the record's length, sequence within the transmission, and control attributes that govern how the record is processed during the Lockbox import.

Common Use Cases and Queries

This table is primarily accessed during the setup, maintenance, and troubleshooting of Lockbox formats. Common scenarios include auditing the configuration of all record formats for a specific transmission format or diagnosing parsing errors during Lockbox receipt processing. A typical query would join to the transmission formats table to provide a complete view.

SELECT atf.name transmission_format_name,
       atrf.record_identifier,
       atrf.record_type_lookup_code,
       atrf.record_length
  FROM ar_trans_record_formats atrf,
       ar_transmission_formats atf
 WHERE atrf.transmission_format_id = atf.transmission_format_id
   AND atf.name = 'CUSTOM_BANK_FORMAT'
 ORDER BY atrf.record_sequence;

This query retrieves all record definitions for a given Lockbox format, ordered by their sequence in the file, which is crucial for understanding the expected file layout.

Related Objects

AR_TRANS_RECORD_FORMATS sits at the center of the Lockbox format definition hierarchy. Its documented relationships, as per the provided metadata, are as follows:

  • Parent Table (Foreign Key Reference): AR_TRANSMISSION_FORMATS. The column AR_TRANS_RECORD_FORMATS.TRANSMISSION_FORMAT_ID references AR_TRANSMISSION_FORMATS. This defines the parent transmission format to which this record belongs.
  • Child Table (Referenced by Foreign Key): AR_TRANS_FIELD_FORMATS. The column AR_TRANS_FIELD_FORMATS.RECORD_FORMAT_ID references AR_TRANS_RECORD_FORMATS.RECORD_FORMAT_ID. This is a critical relationship, as the field formats table defines the individual data fields (columns) within each record format, specifying their position, length, and data type.

Therefore, the complete definition chain for a Lockbox format is: AR_TRANSMISSION_FORMATS → AR_TRANS_RECORD_FORMATS → AR_TRANS_FIELD_FORMATS.