Search Results frm_documents_b_n1

Overview

FRM.FRM_DOCUMENTS_B is a core table in the Oracle EBS Report Manager (FRM) schema that stores the un-translated, language-independent attributes of a Report Manager document. In Oracle EBS 12.1.1 and 12.2.2 it functions as the base table in a translated/un-translated pair, where FRM_DOCUMENTS_B holds the shared definitions and FRM_DOCUMENTS_TL holds the corresponding language-specific (translated) content. Every report, template, or published document registered through XML Publisher and Report Manager is persisted here, making the table foundational to EBS reporting and concurrent program output management.

Physically the table resides in the APPS_TS_TX_DATA tablespace with PCTFREE 10. All indexes are stored in APPS_TS_TX_IDX. The table carries 14 documented columns and four indexes: one unique index (FRM_DOCUMENTS_B_UK1) and three non-unique indexes. Under a heuristic Data Vault classification mined from its foreign-key structure, FRM_DOCUMENTS_B is hub-leaning: its DOCUMENT_ID is a stable entity identifier, and it is referenced by dependent detail and translation tables. This should be treated as a modeling suggestion rather than an EBS-native concept.

Key Information Stored

The surrogate primary key of the table is DOCUMENT_ID (NUMBER(15)), described in the metadata as the entity identifier across all applications. A unique index, FRM_DOCUMENTS_B_UK1, is defined on DOCUMENT_ID, making it the primary business-key candidate as documented. Note that DOCUMENT_ID is exposed both as the declared primary key and as the unique index column, so the surrogate key and the unique business key converge on the same attribute.

• DOCUMENT_ID — Surrogate/primary identifier for the document; the join key used by all dependent tables.
• DIRECTORY_ID — The directory that contains this document; foreign key to FRM_DIRECTORY_B.
• SEQUENCE_NUMBER — The ordering of the document within its directory.
• EXPANDED_FLAG — Indicates whether the document is expanded within the Report Manager tree.
• DS_APP_SHORT_NAME — Application short name of the data source code in the XML Publisher template repository.
• DATA_SOURCE_CODE — The data source code attached to the report.
• OBJECT_VERSION_NUMBER — Standard EBS column supporting stateless (optimistic locking) updates.
• END_DATE — Date on which the row was archived or deleted; drives the FRM_DOCUMENTS_B_N1 index.
• ARCHIVED_FLAG — Indicates whether the row is archived; drives the FRM_DOCUMENTS_B_N2 index.
• CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE — Standard WHO audit columns present on all EBS transactional tables.

The remaining non-unique index, FRM_DOCUMENTS_B_N3, is defined on DIRECTORY_ID, supporting directory-to-document lookups.

Common Use Cases and Queries

Typical uses center on locating a document by identifier, enumerating the contents of a directory, filtering active versus archived rows, and resolving the data source attached to a report. The un-translated table is generally joined to FRM_DOCUMENTS_TL to obtain display names and descriptions.

• Retrieve a document with its translation: SELECT b.document_id, t.document_name FROM frm.frm_documents_b b, frm.frm_documents_tl t WHERE b.document_id = t.document_id AND t.language = USERENV('LANG');
• List active (non-archived) documents in a directory: SELECT document_id, sequence_number, expanded_flag FROM frm.frm_documents_b WHERE directory_id = :p_dir AND archived_flag = 'N' AND end_date IS NULL ORDER BY sequence_number;
• Identify reports bound to a specific data source: SELECT document_id, data_source_code FROM frm.frm_documents_b WHERE ds_app_short_name = :app AND data_source_code = :code;
• Reporting on archived documents over a period, using the END_DATE and ARCHIVED_FLAG indexes for efficient access.

Because DOCUMENT_ID is uniquely indexed, single-row lookups are the most efficient access path.

Related Objects

FRM_DOCUMENTS_B participates in a small but tightly coupled set of dependencies. The most significant are listed below with their join columns.

• FRM.FRM_DIRECTORY_B — Referenced by FRM_DOCUMENTS_B.DIRECTORY_ID, providing the containing directory definition.
• FRM.FRM_DOCUMENTS_TL — Translation table joining on DOCUMENT_ID; supplies language-specific document text.
• FRM.FRM_DOCUMENT_DETAILS — Detail table joining on DOCUMENT_ID; holds additional document attributes.
• XML Publisher (XDO) repository tables — Referenced indirectly through DS_APP_SHORT_NAME and DATA_SOURCE_CODE, which link documents to their template data sources.
• Report Manager (FRM) concurrent program APIs — Access documents through DOCUMENT_ID when publishing, expanding, or archiving report output.

Together these objects model the document hierarchy used by EBS Report Manager and XML Publisher reporting.