Search Results frm_doc_reviewers




Overview
Explore in AI Chat

Overview

FRM_DOC_REVIEWERS is a transactional table in the FRM (Report Manager) product schema of Oracle E-Business Suite, present in both release 12.1.1 and 12.2.2. Its documented purpose is to store the user identification information of those individuals authorized to release an on-hold report. In the Oracle Reports Manager workflow, reports and concurrent program outputs may be placed on hold pending review; this table defines the review roster that determines which users may subsequently release those held documents.

The ETRM metadata records no foreign key relationships for this object, and the heuristic Data Vault classification is therefore standalone. From a modeling perspective, this suggests the table may be treated as an independent entity rather than a strict hub, link, or satellite within a Data Vault representation, although its composite key of DOCUMENT_ID, TIMEFRAME, and USER_ID behaves functionally like a link associating reviewers with documents over discrete time windows.

Key Information Stored

The table contains eleven documented columns in the 12.2.2 physical schema. The most operationally significant columns are:

  • DOCUMENT_ID — Identifies the report document or output subject to the review/hold process.
  • TIMEFRAME — A period or sequence qualifier that scopes the reviewer assignment, permitting different reviewers across successive review windows for the same document.
  • USER_ID — The Oracle Applications user authorized to release the on-hold report.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the OAF/ADF framework to detect concurrent updates.
  • CREATION_DATE, CREATED_BY — Standard WHO columns recording when and by whom the reviewer record was created.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns capturing the most recent modification and the login session responsible.
  • END_DATE — Effective end date, supporting date-tracked (logical delete) semantics for reviewer assignments.
  • ARCHIVED_FLAG — Indicates whether the reviewer record has been archived and is no longer active.

The primary key is FRM_DOC_REVIEWERS_PK, defined on the composite of DOCUMENT_ID, TIMEFRAME, and USER_ID. A unique index, FRM_DOC_REVIEWERS_UK1, is documented on the identical column set, confirming these three columns constitute the business-key candidate and that no duplicate reviewer entry can exist for the same document and timeframe.

Common Use Cases and Queries

Typical usage centers on determining who may release a held report and on auditing reviewer assignments. A common query lists active reviewers for a given document:

  • Identify authorized reviewers: SELECT user_id FROM frm.frm_doc_reviewers WHERE document_id = :doc_id AND archived_flag = 'N' AND (end_date IS NULL OR end_date > SYSDATE);
  • Validate release authority for a specific user before permitting the release action.
  • Audit reviewer history by timeframe, examining END_DATE and ARCHIVED_FLAG to distinguish current from historical assignments.
  • Reporting on reviewer coverage across documents, joining USER_ID to FND_USER for reviewer names and to identify documents lacking any active reviewer.
  • Change-tracking extracts using CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, and LAST_UPDATE_DATE for compliance reporting.

Because the object is standalone with no documented foreign keys, joins to parent document or user tables are implicit and must be constructed from application logic rather than enforced constraints.

Related Objects

The ETRM metadata documents no foreign key relationships for FRM_DOC_REVIEWERS, so the following associations are based on column semantics rather than enforced constraints:

  • FND_USER — Referenced via USER_ID, CREATED_BY, and LAST_UPDATED_BY to resolve reviewer and auditor identities.
  • FRM_DOCUMENTS (or equivalent Report Manager document tables) — Associated via DOCUMENT_ID, the parent object being reviewed and held.
  • FND_CONCURRENT_REQUESTS — Related indirectly through report output and hold-processing logic involving DOCUMENT_ID.
  • FRM_DOC_REVIEWERS_PK and FRM_DOC_REVIEWERS_UK1 — The constraints and unique index enforcing key integrity.
  • FND_LOGINS — Linked through LAST_UPDATE_LOGIN for session-level audit tracing.

Report Manager release-processing APIs operate on this table indirectly when evaluating whether a user may release an on-hold report, making it a supporting control structure rather than a primary transaction driver.