Search Results get_role_status_menus




Overview
Explore in AI Chat

Overview

PA_ROLE_STATUS_MENU_UTILS is a PL/SQL utility package owned by the APPS schema in Oracle E-Business Suite, used within the Oracle Projects (PA) module. Its business function is to support the administration and validation of the mapping between project roles and project status menus. In Oracle Projects, project statuses drive the workflow of project lifecycle transitions, and the Project Status form allows administrators to define statuses and control which roles may interact with them. This package provides the server-side validation logic that governs those relationships: it prevents duplicate mappings, blocks the deletion of statuses still referenced by role-menu mappings, and supplies the security API with the status menu data assigned to a given role.

The package was authored in 2003 (Ranjana Murthy) and is declared with AUTHID CURRENT_USER, meaning its procedures execute with the privileges of the calling user rather than the definer. This is consistent with its role as an internal utility invoked from other Oracle Projects utilities and forms.

Key Procedures and Functions

The package exposes three documented procedures:

  • CHECK_STATUS_IS_IN_USE — The procedure associated with the search term "check_status_is_in_use." It determines whether a given status code already exists in the PA_ROLE_STATUS_MENU_MAP table. If a mapping exists, the status is considered "in use" and therefore cannot be deleted from the Project Status form. The procedure returns an in-use flag, a return status, and an error message code. It is called by PA_PROJECT_STUS_UTILS.Allow_Status_Deletion, which relies on this check to decide whether deletion is permitted.
  • CHECK_DUP_ROLE_STATUS — Validates that the same status has not already been mapped to a menu for a given role. It accepts a role status menu identifier, a role identifier, and a status code, and returns a return status and error message code. This guards against duplicate role-status-menu assignments during setup or maintenance.
  • GET_ROLE_STATUS_MENUS — Used by the PA Security API to retrieve all status menu mappings for a given role. It returns collections of role status menu identifiers, status types, status codes, and menu names, along with a status level, a default menu name, and standard return status and error message outputs.

All three procedures use the standard Oracle EBS error-handling convention of paired p_return_status and p_error_message_code out parameters.

Tables Accessed

According to the documented metadata, the package references three tables via APPS synonyms:

  • PA_ROLE_STATUS_MENU_MAP — The central table holding the mapping between roles and status menus. CHECK_STATUS_IS_IN_USE queries it to detect usage; CHECK_DUP_ROLE_STATUS reads it to detect duplicates; GET_ROLE_STATUS_MENUS retrieves its rows.
  • PA_PROJECT_ROLE_TYPES_B — The base table defining project role types, used to resolve role-related information for the mappings.
  • FND_MENUS — The Oracle Application Object Library menus table, used to resolve menu names associated with the retrieved status menu mappings.

Usage Notes

PA_ROLE_STATUS_MENU_UTILS is an internal utility package rather than a public API. It is invoked primarily from other Oracle Projects packages: the metadata notes it is referenced by three other packages, and CHECK_STATUS_IS_IN_USE is called specifically from PA_PROJECT_STUS_UTILS.Allow_Status_Deletion. It is therefore exercised indirectly through the Project Status form (PAXPROJS / project status setup) and through the PA Security API when role-based status menu access is resolved.

Custom code should not call these procedures in isolation, since they assume the calling context established by the Project Status utilities and security API. Extensions requiring role-status-menu validation should invoke the standard form or the PA Security API, allowing this package to run with the correct session state and error handling.