Search Results fnd_document_datatypes




Overview
Explore in AI Chat

Overview

FND_DOCUMENT_DATATYPES is an Oracle E-Business Suite reference table owned by the APPLSYS schema and delivered under the FND – Application Object Library product. It defines the set of document datatypes available to the Oracle EBS document management and attachment framework. Each row describes a datatype that can be associated with documents, attachments, or categorized content managed through the FND document schema, allowing the application to distinguish among storage formats, sources, and handling rules.

The table carries a multilingual dimension: every datatype definition is stored once per installed language, with the LANGUAGE column acting as the discriminator. This design supports the Oracle Applications translation model, where seeded reference data is maintained in multiple languages. From a heuristic Data Vault modeling perspective, the metadata suggests a satellite-leaning classification, since the table’s grain is a descriptive attribute set keyed by a parent identifier and language rather than a standalone hub of business events.

Key Information Stored

The table contains 13 documented columns. The most significant are:

  • DATATYPE_ID — the numeric identifier for the document datatype. It is part of the composite primary key and the anchor of all unique indexes.
  • LANGUAGE — the language code for the translated row; combined with DATATYPE_ID it forms the physical primary key FND_DOCUMENT_DATATYPES_PK.
  • NAME — the displayed name of the datatype, subject to translation by language.
  • USER_NAME — the internal, language-independent user-facing name used by the application for lookups and coded references.
  • START_DATE_ACTIVE and END_DATE_ACTIVE — effective dating controls that determine whether the datatype is active and selectable.
  • SOURCE_LANG — identifies the source language of the translatable columns.
  • ZD_EDITION_NAME — the editioning column used by the Oracle EBS online patching and edition-based redefinition framework in Release 12.2.x.
  • Standard audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN.

The surrogate primary key is FND_DOCUMENT_DATATYPES_PK (DATATYPE_ID, LANGUAGE). Business-key candidates are captured by the unique indexes FND_DOCUMENT_DATATYPES_U1 (DATATYPE_ID, LANGUAGE, NAME, ZD_EDITION_NAME) and FND_DOCUMENT_DATATYPES_U2 (DATATYPE_ID, LANGUAGE, USER_NAME, ZD_EDITION_NAME).

Common Use Cases and Queries

Typical usage involves resolving a datatype identifier to its display label for attachment and document listings, or filtering to only active datatypes. A representative query retrieves active datatypes in the session language:

  • SELECT datatype_id, user_name, name FROM fnd_document_datatypes WHERE language = USERENV('LANG') AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,SYSDATE) AND NVL(end_date_active,SYSDATE);
  • Joining to FND_LANGUAGES to report which languages have a given datatype defined.
  • Feeding lookups for attachment-dialog LOVs, document categorization reports, and migration scripts that map legacy datatype names to current IDs.

Related Objects

The documented foreign key ties this table to language validation, while the primary key participates in referencing relationships within the document framework:

  • FND_LANGUAGES — joined on FND_DOCUMENT_DATATYPES.LANGUAGE = FND_LANGUAGES.LANGUAGE, enforcing that each row’s language is installed.
  • Downstream document and attachment tables that reference DATATYPE_ID as a lookup into this table.
  • FND document management APIs and concurrent programs that consume datatype metadata when storing or retrieving attachments.

Because the table is seeded reference data with effective dating and translation, customization should be limited to controlled data fixes rather than structural change.