Search Results pa_rep_res_mgr_v




Overview
Explore in AI Chat

Overview

The APPS-owned view PA_REP_RES_MGR_V belongs to the Oracle Projects (PA) module and provides a hierarchical listing of the direct and indirect managers recorded in Oracle HR for a given application user. It answers the reporting question: "Who are all the managers above a specified user in the HR supervisory hierarchy?" Rather than exposing only the immediate (direct) manager, the view returns the complete upward chain of management — direct managers, their managers, and so on up the reporting line — for the currently logged-in application user.

Because it resolves the user identity from the FND_GLOBAL session context, the view behaves as a self-scoped reporting object: it does not require the caller to pass the user ID as a bind parameter, since it implicitly derives it from the active application session. This makes it suitable for embedded use in Oracle Projects reporting, workflow routing, and integrations where approval or notification paths must follow the HR management structure. The view is documented as VALID in the ETRM 12.2.2 metadata.

Underlying Base Objects

PA_REP_RES_MGR_V is defined over a small set of documented base objects:

  • PA_RESOURCES_DENORM (referenced as a synonym) — the denormalized resources table that supplies the MANAGER_ID, MANAGER_NAME, PERSON_ID, and effective-dating columns used in the hierarchical query.
  • PA_RESOURCE_UTILS (package) — provides the GET_RESOURCE_MANAGER_ID function, which maps an application user to a resource (person) and returns the starting manager ID for the CONNECT BY traversal.
  • FND_GLOBAL (package) — supplies USER_ID, the identity of the currently connected application user, which drives the starting condition of the hierarchy.

The view text uses a hierarchical CONNECT BY query. The START WITH clause seeds the traversal using PA_RESOURCE_UTILS.GET_RESOURCE_MANAGER_ID(FND_GLOBAL.USER_ID), and the CONNECT BY PRIOR PERSON_ID = MANAGER_ID clause walks upward through successive managers. Effective-dating is enforced with SYSDATE BETWEEN RESOURCE_EFFECTIVE_START_DATE AND RESOURCE_EFFECTIVE_END_DATE at each level.

Key Columns

  • MANAGER_ID — the identifier of each manager in the reporting chain. Because the query is SELECT DISTINCT over the CONNECT BY result, this column yields the unique set of managers above the user.
  • MANAGER_NAME — the display name corresponding to MANAGER_ID, sourced from PA_RESOURCES_DENORM, suitable for direct presentation in reports and notifications.

The view therefore exposes only two columns, keeping the interface simple and focused on manager identification and naming.

Common Use Cases and Queries

Typical scenarios include approval hierarchies in Oracle Projects, notification and escalation routing, and reporting that must show all managers above a user. A standard query is:

  • SELECT manager_id, manager_name FROM apps.pa_rep_res_mgr_v; — returns every direct and indirect manager for the currently logged-in application user.
  • Use as a subquery or validation set when an application must confirm that a person is legitimately in a user's management chain before granting project-related approvals.
  • Feed manager lists into workflow lookup routines or personalization logic that must respect the HR reporting hierarchy rather than static responsibility assignments.

Because the effective-dating filters are applied within the hierarchy, results reflect only currently active manager relationships. Callers should be aware that the view is session-bound through FND_GLOBAL.USER_ID, so it returns data for the connected user only; adopters requiring an arbitrary user's hierarchy must replicate the underlying CONNECT BY logic with an explicit START WITH value.