Search Results fnd_doc_categories_active_vl




Overview
Explore in AI Chat

Overview

FND_DOC_CATEGORIES_ACTIVE_VL is an APPS-owned reporting view within the FND – Application Object Library product of Oracle E-Business Suite. Its documented purpose is to determine the language of a document category, effectively presenting the translatable and non-translatable attributes of active document categories resolved to the language of the current session. The "_VL" suffix indicates a "view of language" pattern: the view joins the base entity table with its translation table and filters the translation rows by the session language, so consumers see a single, language-resolved row per category without being exposed to multi-language storage details.

In EBS reporting and integration, this view serves as a convenient, language-aware source for the document category reference data that governs Oracle Attachments and the FND document management framework, including the Attachment feature used across many EBS modules. Because it restricts output to categories currently in effect, it returns only records whose activation window includes the current date, making it suitable for validation lists (LOVs), attachment setup queries, and interfaces that must not present retired or not-yet-effective categories.

Underlying Base Objects

The documented referenced base objects are FND_DOCUMENT_CATEGORIES, FND_DOCUMENT_CATEGORIES_TL, and FND_DOCUMENT_DATATYPES, each accessed through APPS synonyms. The view joins them as follows:

  • FND_DOCUMENT_CATEGORIES C — the base (non-translatable) table holding the category definition, its activation dates, default datatype, application, and DFF attributes.
  • FND_DOCUMENT_CATEGORIES_TL T — the translation table, joined on CATEGORY_ID and filtered by T.LANGUAGE = USERENV('LANG'), supplying the user-facing category name.
  • FND_DOCUMENT_DATATYPES DD — the default datatype definition, joined with an outer join (DD.DATATYPE_ID(+) and DD.LANGUAGE(+) = USERENV('LANG')) so categories with no valid default datatype still appear.

Note that the single-letter alias T is used for the TL table while C is used for the base table; the USER_NAME column is sourced from the translation row, and DEFAULT_DATATYPE_NAME is sourced from the datatype table.

Key Columns

  • ROW_ID — the ROWID of the base category row, provided for row identification.
  • CATEGORY_ID — primary key of the document category, used during setup and attachment configuration.
  • NAME — the language-resolved category name from the translation table.
  • USER_NAME — the user-entered display name from the translation table (distinct from the seeded NAME). The same alias is reused for the datatype's name, exposed as DEFAULT_DATATYPE_NAME.
  • START_DATE_ACTIVE / END_DATE_ACTIVE — the effective-dating window; NULL start is treated as always-started and NULL end as never-ending via the NVL logic.
  • DEFAULT_DATATYPE_ID / DEFAULT_DATATYPE_NAME — the datatype assigned by default to the category and its language-resolved name.
  • APPLICATION_ID — owning application of the category, enabling product-scoped filtering.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — descriptive flexfield context and segments carried through from the base table.
  • Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical scenarios include building attachment category LOVs, auditing which categories are currently active per application, and resolving a category's default datatype during integration loads.

Listing active categories for the session language:

SELECT category_id, name, user_name,
       default_datatype_id, default_datatype_name,
       application_id, start_date_active, end_date_active
FROM   apps.fnd_doc_categories_active_vl
ORDER  BY user_name;

Scoping to one application:

SELECT category_id, user_name, default_datatype_name
FROM   apps.fnd_doc_categories_active_vl
WHERE  application_id = :app_id;

Resolving a specific category for attachment setup:

SELECT name, user_name, default_datatype_id
FROM   apps.fnd_doc_categories_active_vl
WHERE  category_id = :category_id;

Because the view already enforces the session-language filter and the effective-dating predicate, these queries require no additional DATE or LANGUAGE handling and remain consistent with standard EBS attachment behavior.