Search Results pa_rep_all_mgr_v




Overview
Explore in AI Chat

Overview

PA_REP_ALL_MGR_V is an Oracle Projects (PA) view owned by the APPS schema and registered as VALID in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to list all direct and indirect managers in Oracle HR for a given application user who has Resource Authority. In other words, the view resolves the management hierarchy that a specific EBS user is permitted to see based on the Resource Authority granted through Oracle Projects security, and it flattens that hierarchy into a manager-to-manager relationship set.

The view functions as a security-filtered reporting and integration object. Rather than exposing the full HR management chain, it restricts results to those managers reachable by the current FND_GLOBAL.USER_ID through granted Resource Authority. Because it is a view rather than a table, it carries no storage of its own and always reflects the current state of the underlying denormalized resource and grant data at query time.

Underlying Base Objects

The documented ETRM metadata for 12.2.2 lists the following referenced base objects: FND_GLOBAL (package), FND_GRANTS (synonym), FND_OBJECTS (synonym), FND_PROFILE (package), FND_USER (synonym), PA_REP_RES_MGR_V (view), PA_RESOURCES_DENORM (synonym), PA_RESOURCE_UTILS (package), PA_SECURITY_PVT (package), and DUAL (synonym).

  • PA_RESOURCES_DENORM — the denormalized resource table supplying person, manager, and manager name attributes, with effective dating validated against SYSDATE.
  • PA_REP_RES_MGR_V — a reporting view of resource manager relationships used as one of the driving row sources.
  • FND_GRANTS, FND_OBJECTS, FND_USER — together they resolve the Resource Authority grant for the current user, matching grants of type INSTANCE on the ORGANIZATION object against the user's party key.
  • PA_SECURITY_PVT, PA_RESOURCE_UTILS — security packages used to obtain the Resource Authority menu identifier and related security logic.
  • FND_PROFILE — supplies the PA_SUPER_RESOURCE profile value, which governs whether the super-resource bypass applies.
  • FND_GLOBAL — provides the session context, notably FND_GLOBAL.USER_ID used to scope the grant lookup.

Key Columns

The documented view text exposes a small, focused column set:

  • MANAGER_ID — the identifier of the manager in the hierarchy reachable by the user. This column appears twice in the projection: once representing the manager from the reporting resource view and once from the denormalized resource side.
  • MANAGER_NAME — the display name of the manager.
  • Relationship indicator — a DECODE expression compares the two manager identifiers and returns 'DIRECT_REPORTS' when they match, or 'ALL_RESOURCES' otherwise. This distinguishes direct reporting lines from indirect or broader resource scope.

The effective-dating predicates (SYSDATE BETWEEN RESOURCE_EFFECTIVE_START_DATE AND RESOURCE_EFFECTIVE_END_DATE) ensure only currently active resource and manager records are returned.

Common Use Cases and Queries

Typical uses include validating which managers a user can act upon, populating manager selection lists in Projects security screens, and auditing Resource Authority grants. A representative query limits output to the current user session:

  • SELECT manager_id, manager_name FROM apps.pa_rep_all_mgr_v; — returns the manager set visible under the current session's Resource Authority.
  • Filtering on the relationship indicator to isolate direct reports versus the full resource scope.
  • Joining to PA_RESOURCES_DENORM or HR person tables to enrich results with additional manager attributes.

Because visibility depends on FND_GLOBAL.USER_ID, the same query can return different result sets for different sessions, which is expected behavior for a security-driven view.