Search Results frm_user_pub_options




Overview
Explore in AI Chat

Overview

The FRM_USER_PUB_OPTIONS table is a Report Manager (FRM) module object within the Oracle E-Business Suite. It stores the publishing options configured for a report on a per-user basis, allowing individual users to define how a given report is published, distributed, or made available within their own reporting context. In EBS 12.1.1 and 12.2.2, this table is owned by the FRM schema and is classified as VALID in the ETRM repository.

From a heuristic Data Vault modeling perspective, FRM_USER_PUB_OPTIONS is best characterized as a satellite. Its grain is defined by the composite business key (DOCUMENT_ID, REPORT_ID, USER_ID), which describes a relationship between a report and a user, while the remaining columns capture descriptive, mutable attributes — templates, output types, security settings, and availability rules — that belong to that relationship rather than to an independent business entity. The table is not classified as a link because it does not primarily model a many-to-many association between two hubs; instead, it stores attribute history keyed by the report/user/document combination.

Key Information Stored

The table contains 23 documented columns. The most significant are summarized below.

  • REPORT_ID and USER_ID — together form the primary key FRM_USER_PUB_OPTIONS_PK and identify the report and the user to whom the publishing options apply.
  • DOCUMENT_ID — contributes to the unique business-key candidate FRM_USER_PUB_OPTIONS_UK1 (DOCUMENT_ID, REPORT_ID, USER_ID).
  • TEMPLATE_CODE — identifies the publishing template applied when the report is generated.
  • APPLICATION_SHORT_NAME — the owning application of the report, used for context and filtering.
  • OUTPUT_TYPE — the format in which the report is published (for example, spreadsheet, PDF, or text).
  • AVAILABILITY_TYPE and AVAILABILITY_DATE — control when the published report becomes available and until when, defining distribution windows.
  • SECURITY_TYPE and SECURITY_CODE — define who may access the published report, supporting user, role, or code-based restrictions.
  • PUBLISHER_TYPE — indicates the publishing mechanism or destination (for example, a menu, function, or value set).
  • VALUE_SET_ID — foreign key to FRM_PART_VALUE_SETS, identifying the parameter value set associated with the publishing option.
  • FUNCTION_ID, MENU_ID, and MENU_ENTRY_SEQ — associate the report with a function or menu entry; MENU_ID references FND_MENUS.
  • EXPANDED_FLAG — tracks whether the report or its options have been expanded in the publishing UI.
  • ARCHIVE_PERIOD_MONTHS — defines how long the published output is retained.

Standard who-columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER) support auditing and optimistic locking.

Common Use Cases and Queries

Typical reporting queries retrieve the publishing configuration for a specific report and user, or enumerate all options published to a given menu or function.

  • Retrieve all publishing options for a user:
    SELECT report_id, template_code, output_type, availability_type FROM frm.frm_user_pub_options WHERE user_id = :p_user_id;
  • Find reports published to a specific menu entry:
    SELECT report_id, user_id, menu_id, menu_entry_seq FROM frm.frm_user_pub_options WHERE menu_id = :p_menu_id ORDER BY menu_entry_seq;
  • Audit security and retention settings:
    SELECT report_id, security_type, security_code, archive_period_months FROM frm.frm_user_pub_options WHERE security_type IS NOT NULL;
  • Join to FRM_PART_VALUE_SETS to resolve parameter value set names:
    SELECT o.report_id, v.value_set_name FROM frm.frm_user_pub_options o, frm.frm_part_value_sets v WHERE o.value_set_id = v.value_set_id;

These queries support publishing administration, security reviews, and menu/distribution reconciliation.

Related Objects

  • FRM_PART_VALUE_SETS — referenced via VALUE_SET_ID; supplies parameter value set definitions.
  • FND_MENUS — referenced via MENU_ID; identifies the target menu for the published report.
  • FND_MENU_ENTRIES — used with MENU_ID and MENU_ENTRY_SEQ to resolve the specific menu entry.
  • FND_FUNCTIONS — correlates with FUNCTION_ID to associate the published report with a function.
  • FND_USER — joins on USER_ID for user identity and responsibility context.
  • FRM_DOCUMENTS (or the corresponding document repository table) — joins on DOCUMENT_ID to identify the published document instance.

Together these objects form the publishing and distribution path that FRM_USER_PUB_OPTIONS drives within the Report Manager module.