Search Results iby_formats_vl




Overview
Explore in AI Chat

Overview

The IBY_FORMATS_VL view is a translated (VL, "view language") reporting and integration object in the Oracle Payments (IBY) module of Oracle E-Business Suite. It exposes payment format definitions used by the Payments engine to control how payment instructions, remittance advices, and related payment documents are formatted for transmission to banks and payment systems. A payment format in Oracle Payments defines the file layout, extract logic, and template used when generating a payment instruction file or an accompanying document.

Because IBY_FORMATS_VL is a _VL view, it joins a base table to its translation table and filters the translation row to the session's language using USERENV('LANG'). This design allows the same underlying format definition to present a language-appropriate FORMAT_NAME while keeping the operational columns (format code, template code, extract identifier, etc.) identical across languages. The view is owned by APPS and is marked VALID in the ETRM repository for both 12.1.1 and 12.2.2, making it a stable, queryable interface for reporting on configured payment formats without touching the underlying tables directly.

Underlying Base Objects

The view is defined over two documented base objects, referenced as synonyms in the APPS schema:

  • IBY_FORMATS_B — the base (non-translated) table holding the format definition: format code, format type, template code, reference format, extract identifier, descriptive flexfield attributes, audit columns, object version number, and the seeded flag.
  • IBY_FORMATS_TL — the translation table storing the language-specific FORMAT_NAME keyed by FORMAT_CODE and LANGUAGE.

The join correlates B.FORMAT_CODE = T.FORMAT_CODE and restricts T.LANGUAGE = USERENV('LANG'), so each FORMAT_CODE returns a single row in the language of the current session. The ROW_ID column is sourced from B.ROWID, and ORACLE maintains the view as a key-preserved, updatable join over the base table for translation-enabled columns.

Key Columns

  • ROW_ID — the ROWID of the underlying IBY_FORMATS_B row, useful for de-duplication or direct row identification.
  • FORMAT_CODE — the unique, language-independent identifier of the payment format; the primary join key and the value typically referenced by setup and payment process profiles.
  • FORMAT_NAME — the translated display name of the format, returned in the session language; the column most report authors filter or display on.
  • FORMAT_TYPE_CODE — classifies the format (for example, payment instruction format versus remittance or other document types), driving which generation process consumes it.
  • FORMAT_TEMPLATE_CODE / REFERENCE_FORMAT_CODE — identify the template and any reference/source format used to build the output, supporting format derivation and inheritance.
  • EXTRACT_ID — links the format to its extract definition, which determines how payment data is selected and structured into the file.
  • SEEDED_FLAG — indicates whether the format is Oracle-seeded (Y) or user-defined (N), relevant when distinguishing standard formats from customizations.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — descriptive flexfield columns for customer-defined format attributes.
  • Audit and concurrency columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER provide standard who-column auditing and optimistic locking.

Common Use Cases and Queries

Typical uses include auditing which payment formats are configured, listing seeded versus custom formats, and joining formats to payment process profiles or payment documents for downstream reporting and integration. Because FORMAT_NAME is translated, the view is the correct source when a report must display format names in the user's language.

A simple listing of formats and their type:

  • SELECT format_code, format_name, format_type_code, seeded_flag FROM apps.iby_formats_vl ORDER BY format_name;

Isolating custom formats for review:

  • SELECT format_code, format_name, format_template_code, extract_id FROM apps.iby_formats_vl WHERE seeded_flag = 'N';

Resolving the display name for a known format code referenced elsewhere:

  • SELECT format_name FROM apps.iby_formats_vl WHERE format_code = :p_format_code;

All queries should be run with the APPS schema or an appropriate synonym and respect the language of the connecting session via USERENV('LANG').