Results for “ar_trans_field_formats”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AR.AR_TRANS_FIELD_FORMATS is a Receivables (AR) configuration table that defines the layout and formatting rules for each individual field within a Lockbox transmission format. Lockbox processing converts bank-supplied electronic payment files into AutoCash receipt batches, and the accuracy of that conversion depends entirely on how the file's fixed-position or delimited fields are interpreted. This table stores those interpretation rules: where each field begins and ends, its data type, and any picture or format masks that govern date, time, justification, and amount handling.

The table sits at the lowest level of the Lockbox format hierarchy, beneath the transmission format header and the record format layer. In a heuristic Data Vault classification derived from its foreign-key structure, AR_TRANS_FIELD_FORMATS is modelled as a link entity: it resolves the many-to-many-style relationship between record formats and transmission formats while carrying its own descriptive attributes. It is maintained through the Receivables Lockbox setup forms and is consumed at runtime by the Lockbox import program.

Key Information Stored

The surrogate primary key is FIELD_FORMAT_ID, enforced by AR_TRANS_FIELD_FORMATS_PK. The documented unique index AR_TRANS_FIELD_FORMATS_U1 spans FIELD_FORMAT_ID and ZD_EDITION_NAME, reflecting the edition-based redefinition strategy used in 12.2.2; ZD_EDITION_NAME is therefore a business-key candidate alongside the identifier. The most significant descriptive columns are:

• RECORD_FORMAT_ID — identifies the parent record format to which this field belongs.
• TRANSMISSION_FORMAT_ID — identifies the owning transmission format, enabling direct access without traversing the record layer.
• FIELD_START_POSITION and FIELD_END_POSITION — define the byte or character offsets of the field in the incoming transmission record.
• FIELD_TYPE_LOOKUP_CODE — classifies the field (for example, amount, date, or alphanumeric), driving subsequent interpretation.
• DESCRIPTION — the user-defined label explaining the field's purpose.
• DATE_PICTURE_LOOKUP_CODE and TIME_PICTURE_LOOKUP_CODE — specify the date and time format masks applied during conversion.
• AMOUNT_FORMAT_LOOKUP_CODE — defines how numeric amounts are parsed, including sign handling.
• JUSTIFICATION_LOOKUP_CODE — indicates left or right justification for free-form values.
• OVERFLOW_REC_INDICATOR — flags whether field content may continue onto an overflow record.
• FILL_CHARACTER_LOOKUP_CODE — identifies the pad character used to fill unused positions.
• Audit columns CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The primary use case is diagnostic: when Lockbox import rejects or misreads a bank file, querying this table reveals the expected positions and formats for each field. A typical pattern joins the three-level hierarchy to display the complete layout for a named transmission format:

• Retrieve all fields for a record format: SELECT FIELD_START_POSITION, FIELD_END_POSITION, FIELD_TYPE_LOOKUP_CODE, AMOUNT_FORMAT_LOOKUP_CODE FROM AR_TRANS_FIELD_FORMATS WHERE RECORD_FORMAT_ID = :record_format_id ORDER BY FIELD_START_POSITION;
• Validate layout completeness by counting defined fields per record format before go-live cutover.
• Audit configuration changes by comparing LAST_UPDATE_DATE across transmission formats.
• Support migration or cloning by extracting all format definitions for a target operating unit.

Related Objects

The table participates in the Lockbox setup chain through two documented foreign keys:

• AR_TRANS_RECORD_FORMATS — joined on AR_TRANS_FIELD_FORMATS.RECORD_FORMAT_ID = AR_TRANS_RECORD_FORMATS.RECORD_FORMAT_ID; the immediate parent defining record-level structure.
• AR_TRANSMISSION_FORMATS — joined on AR_TRANS_FIELD_FORMATS.TRANSMISSION_FORMAT_ID = AR_TRANSMISSION_FORMATS.TRANSMISSION_FORMAT_ID; the top-level Lockbox format definition.
• AR_TRANS_FIELD_FORMATS_PK and AR_TRANS_FIELD_FORMATS_U1 — the constraints that guarantee uniqueness and support the AutoCash import process.
• The Receivables Lockbox import program and its associated concurrent program definition, which reads these rows at runtime.

Together these objects form the complete Lockbox transmission specification consumed by Receivables AutoCash.