Search Results pa_role_status_menu_map_pk




Overview
Explore in AI Chat

Overview

PA_ROLE_STATUS_MENU_MAP is a Projects (PA) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that implements security on project role types based on the current status of a project. It functions as a role-based access control mapping layer, associating a project role, a project status, and an Oracle EBS menu so that the menus available to a user are filtered according to both the role they hold and the lifecycle status of the project they are working on. The table resides in the PA schema and is documented with a status of VALID.

The primary key of the table is defined as PA_ROLE_STATUS_MENU_MAP_PK, keyed on the ROLE_STATUS_MENU_ID column, and is further constrained by a unique index, PA_ROLE_STATUS_MENU_MAP_U1, on the same surrogate column. The documented business-key candidate is the combination of ROLE_ID, STATUS_CODE and MENU_ID. From a Data Vault modeling perspective, the metadata's heuristic classification is link, indicating that this table primarily serves as an associative entity resolving many-to-many relationships among roles, project statuses, and menus rather than as a descriptive hub or satellite.

Key Information Stored

The table contains eleven documented columns. The most significant for functional and reporting purposes are:

  • ROLE_STATUS_MENU_ID — the surrogate primary key and unique identifier for each mapping row.
  • ROLE_ID — the project role to which the menu restriction applies; part of the documented business key.
  • STATUS_CODE — the project status code for which the mapping is valid; foreign key to PA_PROJECT_STATUSES and part of the documented business key.
  • STATUS_TYPE — categorizes the status context associated with the mapping.
  • MENU_ID — the Oracle EBS menu granted or restricted; foreign key to FND_MENUS and part of the documented business key.
  • OBJECT_VERSION_NUMBER — optimistic locking column used by the framework during concurrent updates.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard Oracle EBS audit columns capturing who created and last modified each row and when.

The surrogate key (ROLE_STATUS_MENU_ID) is distinct from the business-key candidate (ROLE_ID, STATUS_CODE, MENU_ID), which enforces uniqueness of the role/status/menu combination.

Common Use Cases and Queries

The primary use case is the determination of which menus a user may access for a project given their assigned role and the project's current status. Administrative reporting typically enumerates the mappings to audit security configuration, for example:

  • Listing all menus mapped to a specific role: SELECT menu_id FROM pa_role_status_menu_map WHERE role_id = :role_id;
  • Listing all role/status/menu combinations for a given project status: SELECT role_id, menu_id FROM pa_role_status_menu_map WHERE status_code = :status_code;
  • Joining to FND_MENUS to resolve menu names: SELECT m.menu_name FROM pa_role_status_menu_map r, fnd_menus m WHERE r.menu_id = m.menu_id AND r.status_code = :status_code;
  • Auditing recent changes via audit columns: SELECT * FROM pa_role_status_menu_map WHERE last_update_date > SYSDATE - 30;

Because the table drives runtime menu security, reporting helps identify configuration gaps, duplicate mappings, or stale entries following project status changes.

Related Objects

The following objects are the most significant related entities, based on the documented foreign-key relationships:

  • PA_PROJECT_STATUSES — referenced via STATUS_CODE; defines the valid project statuses used in the mapping.
  • FND_MENUS — referenced via MENU_ID; defines the Oracle EBS menu being mapped to a role and status.
  • PA_ROLE_STATUS_MENU_MAP_PK — the primary key constraint on ROLE_STATUS_MENU_ID.
  • PA_ROLE_STATUS_MENU_MAP_U1 — the unique index on ROLE_STATUS_MENU_ID.
  • Project role definitions and status-driven access logic in the Projects module, which consume this table to enforce role-based menu security.

Together these objects allow administrators to control project information exposure by coupling role assignments to project lifecycle states.