Search Results dm_node

Overview

FND_DOCUMENTS_VL is a seeded, VALID view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the FND — Application Object Library product and serves as the language-resolved presentation layer for the FND_DOCUMENTS entity, the central repository that stores metadata about attachments, documents, and files referenced throughout the EBS application. The "_VL" suffix denotes a "view language" construct: the view joins the base FND_DOCUMENTS table to its translation tables and filters each translated row to the session language returned by USERENV('LANG'). As a result, a query against FND_DOCUMENTS_VL always returns the description, title, and category name in the language of the connected user, without requiring the caller to write explicit joins to the _TL tables. This makes it the standard access point for reporting, integration, and form-level logic that must display document descriptors consistently in the user's native language.

Underlying Base Objects

The ETRM metadata documents the view as defined over six referenced base objects, each accessed through an APPS synonym: FND_DOCUMENTS (the primary entity holding document identity, file name, media, storage type, security, and DM/URL attributes), FND_DOCUMENTS_TL (translated description, title, and the fifteen DOC_ATTRIBUTE columns), FND_DOCUMENT_CATEGORIES and its translation table FND_DOCUMENT_CATEGORIES_TL (category identity and language-specific category user name), FND_DOCUMENT_DATATYPES (data type identity and language-specific user name), and FND_LOOKUP_VALUES (the lookup that supplies the meaning of USAGE_TYPE from lookup type ATCHMT_DOCUMENT_TYPE). The join condition equates DOCUMENT_ID across the document base and translation tables, CATEGORY_ID across the category pair, and applies LANGUAGE = USERENV('LANG') to every translated source and to the lookup row, so exactly one language-resolved row is returned per document for the active session language.

Key Columns

• DOCUMENT_ID — Primary document identifier; the join key to FND_DOCUMENTS and all translation tables.
• DATATYPE_ID / DATATYPE_NAME — The document's data type and its language-resolved user name from FND_DOCUMENT_DATATYPES.
• DESCRIPTION / TITLE — Language-resolved descriptive text sourced from FND_DOCUMENTS_TL.
• FILE_NAME, MEDIA_ID, STORAGE_TYPE, URL — Physical location and retrieval attributes of the stored content.
• CATEGORY_ID, CATEGORY_APPLICATION_ID, CATEGORY_DESCRIPTION — Category identity and its translated category user name.
• USAGE_TYPE / USAGE_TYPE_DESCR — Attachment usage code and its meaning from the ATCHMT_DOCUMENT_TYPE lookup.
• SECURITY_TYPE / SECURITY_ID — Access control attributes governing who may view the document.
• START_DATE_ACTIVE / END_DATE_ACTIVE — Effective-dating range for the document record.
• PUBLISH_FLAG, IMAGE_TYPE, DM_NODE, DM_FOLDER_PATH, DM_TYPE, DM_DOCUMENT_ID, DM_VERSION_NUMBER, ORIG_DOC_ID — Publication, imaging, and document-management integration attributes.
• DOC_ATTRIBUTE_CATEGORY and DOC_ATTRIBUTE1–DOC_ATTRIBUTE15 — Descriptive flexfield context and segment values for the document.
• WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) and ROWID — Audit and concurrent-program context, with ROWID supporting form-based update of the underlying FND_DOCUMENTS row.

Common Use Cases and Queries

The view is commonly used for attachment reporting, document inventory reconciliation, and integration extracts where human-readable descriptions are required in the user's language. A representative query lists active documents with their category and datatype:

• SELECT document_id, title, description, file_name, usage_type_descr FROM fnd_documents_vl WHERE TRUNC(SYSDATE) BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE);
• To find all versions of a document-managed file: SELECT document_id, dm_document_id, dm_version_number, title FROM fnd_documents_vl WHERE orig_doc_id = :p_doc_id ORDER BY dm_version_number;
• To extract category usage by lookup meaning: SELECT category_description, usage_type_descr, COUNT(*) FROM fnd_documents_vl GROUP BY category_description, usage_type_descr;
• To join attachments to their owning entities, join FND_DOCUMENTS_VL to FND_ATTACHED_DOCUMENTS on DOCUMENT_ID and then to the entity-specific table using PK1_VALUE.

Because the view already enforces the language join, callers should not additionally filter on language columns unless operating in a multilingual reporting context where a specific language other than the session default is required.