Search Results frm_document_details_uk1




Overview
Explore in AI Chat

Overview

FRM.FRM_DOCUMENT_DETAILS is a transaction-data table in the Oracle EBS 12.1.1 / 12.2.2 schema owned by FRM, the module that underpins Oracle Financial Reporting and the reporting/consolidation components of General Ledger. As its documented purpose states, the table "stores the detail level information about a document or document set." In practical terms, it is the child table that decomposes a parent document—defined in FRM_DOCUMENTS_B—into the individual timeframe and expanded-value rows that drive reporting, expansion, and file generation. The stored report output is held externally, and this table carries the FILE_ID pointer to the corresponding entry in FRM_REPOSITORY_LOBS.

Using the heuristic Data Vault classification derived from the foreign-key structure, this object is satellite-leaning. That is a modeling suggestion rather than a physical declaration: the table behaves as a descriptive satellite hanging off the FRM_DOCUMENTS_B hub, with a composite key that includes both the parent identifier and contextual attributes (TIMEFRAME, EXPANDED_VALUE). It is not a pure hub because its identity is not self-contained, and it is not a pure link because the relationship to the parent is many-to-one via DOCUMENT_ID.

The table resides in the APPS_TS_TX_DATA tablespace with PCTFREE 10, and its indexes are placed in APPS_TS_TX_IDX—a standard transactional configuration.

Key Information Stored

Documented physical schema lists 14 columns. The most significant are summarized below.

  • DOCUMENT_ID (NUMBER(15), mandatory) — Foreign key to FRM_DOCUMENTS_B; identifies the parent document or document set. The metadata describes it as an entity identifier across all applications.
  • TIMEFRAME (VARCHAR2(240), mandatory) — The timeframe context for the detail row, such as an accounting period or range.
  • EXPANDED_VALUE (VARCHAR2(240), mandatory) — The expanded value for that timeframe, keyed together with DOCUMENT_ID and TIMEFRAME.
  • FILE_ID (NUMBER(15)) — The identifier of the report output file stored in FRM_REPOSITORY_LOBS.
  • END_DATE (DATE) — The date the row is archived or deleted.
  • ARCHIVED_FLAG (VARCHAR2) — Flag indicating whether the row is archived.
  • OBJECT_VERSION_NUMBER (NUMBER(15)) — Applications standard column enabling stateless optimistic locking.
  • CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE — Standard WHO audit columns.

The surrogate/composite primary key FRM_DOCUMENT_DETAILS_PK is (DOCUMENT_ID, TIMEFRAME, EXPANDED_VALUE). The unique index FRM_DOCUMENT_DETAILS_UK1 carries the identical column set (DOCUMENT_ID, TIMEFRAME, EXPANDED_VALUE) and therefore functions as the business-key candidate enforcing the natural uniqueness of a document detail row. Three non-unique indexes support retrieval: FRM_DOCUMENT_DETAILS_N1 on END_DATE, N2 on ARCHIVED_FLAG, and N3 on FILE_ID.

Two additional columns appear in the documented column list without detailed comments: PE_SEGMENT and PE_SEGMENT_VALUE, which typically carry segment context used in the reporting/expansion logic.

Common Use Cases and Queries

The dominant scenario is reporting decomposition: for a given parent document, retrieves all timeframe/expanded-value detail rows, then joining to FRM_DOCUMENTS_B to resolve document context and to FRM_REPOSITORY_LOBS via FILE_ID to locate the rendered report output.

  • Detail retrieval for a document: SELECT DOCUMENT_ID, TIMEFRAME, EXPANDED_VALUE, FILE_ID FROM FRM.FRM_DOCUMENT_DETAILS WHERE DOCUMENT_ID = :p_id ORDER BY TIMEFRAME;
  • Locating report output files: join on FILE_ID where an output file must be mapped back to its business detail row.
  • Archival and purging analysis: the N1 (END_DATE) and N2 (ARCHIVED_FLAG) indexes make these the efficient predicates: SELECT * FROM FRM.FRM_DOCUMENT_DETAILS WHERE ARCHIVED_FLAG = 'Y' AND END_DATE < SYSDATE;
  • Uniqueness/integrity checks: querying on (DOCUMENT_ID, TIMEFRAME, EXPANDED_VALUE) validates the FRM_DOCUMENT_DETAILS_UK1 business key.
  • Custom operational reports: count detail rows per document to verify completeness before producing consolidated output.

Because the columns TIMEFRAME and EXPANDED_VALUE store the specialization of the parent document, these are the most common filter and grouping targets in reporting SQL.

Related Objects

  • FRM.FRM_DOCUMENTS_B — Parent object; joined on FRM_DOCUMENT_DETAILS.DOCUMENT_ID = FRM_DOCUMENTS_B.DOCUMENT_ID. This is the documented foreign-key relationship and the principal upward join.
  • FRM.FRM_REPOSITORY_LOBS — Holds the report output file identified by FRM_DOCUMENT_DETAILS.FILE_ID.
  • APPS.FRM_DOCUMENT_DETAILS — The APPS-layer synonym/view through which applications and reports normally access the table.
  • FND Design Data: FRM.FRM_DOCUMENT_DETAILS — The registered design-data identity used by the application dictionary and form/report definitions.
  • FRM_DOCUMENT_DETAILS_N1 / _N2 / _N3 — Supporting non-unique indexes on END_DATE, ARCHIVED_FLAG, and FILE_ID that shape query plans for archival and file-resolution access paths.
  • FRM_DOCUMENT_DETAILS_UK1 / FRM_DOCUMENT_DETAILS_PK — The unique structures defining the business key and primary key over (DOCUMENT_ID, TIMEFRAME, EXPANDED_VALUE).

The metadata records no further database objects referencing this table beyond the APPS wrapper; its dependency direction is upward to FRM_DOCUMENTS_B and outward to the LOB repository by FILE_ID.