Search Results fnd_document_datatypes_u2

Overview

APPLSYS.FND_DOCUMENT_DATATYPES is an Oracle E-Business Suite seed and reference table that defines the document datatypes supported by the Oracle Applications document management framework. It is owned by the APPLSYS schema, carries FND design data as FND.FND_DOCUMENT_DATATYPES, and holds status VALID in release 12.1.1 and 12.2.2. The table stores the datatypes that Oracle Forms attachments and the underlying document manager can store, view, and edit. Four rows are shipped as initial values: short text (DATATYPE_ID = 1), long text (DATATYPE_ID = 2), image (DATATYPE_ID = 3), and OLE Object (DATATYPE_ID = 4). Customers and implementers may add rows to support documents stored outside the Oracle database, viewed and edited through non-native Forms applications, which makes this table an extension point for the attachments architecture.

The table follows a "duplicate record" model for multi-lingual needs. For each logical datatype there is one record per installed language sharing the same DATATYPE_ID, distinguished by the LANGUAGE column, so translation and NLS behaviour are managed at row level rather than through a translation table. Based on the documented foreign key to FND_LANGUAGES and the presence of descriptive attributes keyed by DATATYPE_ID and LANGUAGE, the object exhibits satellite-leaning characteristics under a heuristic Data Vault classification; that is, it is best modelled as a descriptive satellite hanging off a datatype hub keyed on DATATYPE_ID, with LANGUAGE acting as a contributing attribute of the key rather than an independent hub.

Key Information Stored

The physical table contains thirteen documented columns. The most significant are:

• DATATYPE_ID (NUMBER) — the numeric datatype identifier and the principal business identifier for the datatype. Values 1 through 4 are reserved for Oracle's seeded names and must always remain active.
• LANGUAGE (VARCHAR2(30)) — the language of the current record, constrained by a foreign key to FND_LANGUAGES. It forms part of the primary key.
• NAME (VARCHAR2(80)) — the developer name of the datatype, language independent in intent and used programmatically.
• USER_NAME (VARCHAR2(80)) — the language-specific, user-facing display name shown in the attachments UI.
• START_DATE_ACTIVE (DATE) — the date on which the datatype becomes available for use; null means immediately available. Datatypes 1–4 must always be available.
• END_DATE_ACTIVE (DATE) — the date on which the datatype is no longer available; null means indefinitely available. Datatypes 1–4 must never be end-dated.
• SOURCE_LANG (VARCHAR2) — the language whose text is mirrored when a translation for LANGUAGE does not yet exist, supporting the NLS mirroring model.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard Who columns used for audit and concurrency tracing.
• ZD_EDITION_NAME — the editioning column used by the 12.2 online patching / edition-based redefinition infrastructure.

The primary key is FND_DOCUMENT_DATATYPES_PK on (DATATYPE_ID, LANGUAGE). Two unique indexes act as business-key candidates: FND_DOCUMENT_DATATYPES_U1 on (DATATYPE_ID, LANGUAGE, NAME, ZD_EDITION_NAME) and FND_DOCUMENT_DATATYPES_U2 on (DATATYPE_ID, LANGUAGE, USER_NAME, ZD_EDITION_NAME), the pair effectively enforcing uniqueness of both the developer name and the user-facing name within a given datatype and language.

Common Use Cases and Queries

The most frequent use is resolving a DATATYPE_ID stored against an attachment or document record into a translated, user-facing label, and validating that the datatype is active at a point in time.

• Listing the seeded datatypes and their user-facing names for a session language:
  SELECT datatype_id, name, user_name, start_date_active, end_date_active FROM apps.fnd_document_datatypes WHERE language = USERENV('LANG') ORDER BY datatype_id;
• Identifying custom (non-seeded) datatypes: WHERE datatype_id NOT BETWEEN 1 AND 4.
• Enforcing availability windows: filtering on NVL(start_date_active, SYSDATE) <= SYSDATE AND NVL(end_date_active, SYSDATE) >= SYSDATE.
• Auditing translation completeness by comparing row counts per DATATYPE_ID across languages, and confirming SOURCE_LANG is populated where a translation is pending.
• Reporting on which datatypes were end-dated but still referenced by existing attachments, to plan migration or reactivation.

Because the table lives in the APPS_TS_SEED tablespace with 10 percent PCTFREE, it is small, read-intensive, and normally cached, so it is safe to join directly in attachment reports without significant performance concern.

Related Objects

The documented foreign key relationship identifies the principal dependent object: LANGUAGE references APPLSYS.FND_LANGUAGES (LANGUAGE_CODE), so any query joining for translated language names should use FND_LANGUAGES on the language code. Beyond this documented relationship, the datatype identifier is consumed by the document management and attachments tables and views that record the datatype of a stored document, including the document entities and categories defined under the FND_DOCUMENT_* family and the attachments interface used by Oracle Forms and OA Framework pages. Application programming interfaces that resolve or display document datatypes read from this table rather than writing to it, since it is seeded reference data; extensions should add rows respecting the reserved identifiers 1 through 4.