Search Results pa_rep_managers_v

Overview

PA_REP_MANAGERS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined within the Projects (PA) product family. It exposes manager information derived from project resource data and is intended for reporting, integration, and ad hoc query use rather than transaction processing. The view lists managers recorded in the system, together with a classification that distinguishes each manager's relationship to other managers in the hierarchy.

The object is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2, and its canonical name is APPS.PA_REP_MANAGERS_V. The description in the ETRM metadata states that the view is a secured reporting object that lists all managers in the system. The "REP" prefix and the accompanying description text indicate that this is one of the reporting (non-base) views supplied with the Projects module, designed to simplify queries against the denormalized resource table without requiring the caller to reconstruct the hierarchical relationships manually.

Because it is a secured view, access is governed through Oracle's application security model; the set of rows returned is filtered according to the responsibilities and security profiles applied to the querying user, so not every caller necessarily sees the complete manager population. Users searching on the manager_id column will typically encounter this view as a convenient, pre-joined source of manager identifiers and names.

Underlying Base Objects

The view is defined over a single documented base object, the APPS.PA_RESOURCES_DENORM synonym. PA_RESOURCES_DENORM is a denormalized snapshot of project resource assignments that carries, for each resource record, both the person identifier (PERSON_ID) and the identifier of that person's manager (MANAGER_ID), alongside the manager name and the effective start and end dates of the resource record.

PA_REP_MANAGERS_V does not reference this synonym once but three times within its definition, aliased as RES, RES2, and RES3. The RES and RES2 inline views select the distinct manager identifiers and manager names that are effective as of the current system date, filtered by the condition that SYSDATE falls between RESOURCE_EFFECTIVE_START_DATE and RESOURCE_EFFECTIVE_END_DATE, and that MANAGER_NAME is not null. The RES3 instance is used in a CONNECT BY hierarchical query that walks upward from each manager through the manager chain, again restricted to currently effective records, so that the view can determine whether a given manager is reachable from another manager in the reporting line.

This design means the view is a pure read-only projection; it holds no storage of its own and reflects changes to the underlying resource denormalization as soon as those changes are effective.

Key Columns

The view exposes four columns, three of which correspond to manager identifiers and names and one of which is a derived classification:

• STARTING_MANAGER_ID — the manager identifier from the RES inline view, representing the manager from whom the hierarchy traversal begins.
• STARTING_MANAGER_NAME — the corresponding manager name for the starting manager, populated only where MANAGER_NAME is not null.
• MANAGER_ID — the manager identifier from the RES2 inline view, representing a manager reachable within the hierarchy of the starting manager.
• VIEW_RESOURCES — a derived flag produced by a DECODE over the two manager identifiers. When RES2.MANAGER_ID equals RES.MANAGER_ID the value is 'DIRECT_REPORTS'; when the two differ the value is 'ALL_RESOURCES'. This column therefore tells the consumer whether the relationship being examined is the self-relationship (the manager's own direct reporting context) or a broader hierarchical relationship spanning other managers in the chain.

The column set is deliberately narrow: rather than exposing full resource detail, the view surfaces the manager-to-manager relationships needed to drive manager selection lists and hierarchical reporting. The presence of both the starting manager and the reached manager in the same row is what enables the VIEW_RESOURCES classification to be computed.

Common Use Cases and Queries

The primary use case is populating a manager selection list, such as a parameter or LOV, where the user must choose a manager by identifier or name. Because the view resolves names from the denormalized resource table and restricts results to currently effective records, it returns only managers that are valid as of the current date.

A second use case is hierarchical reporting over the management chain. The CONNECT BY logic embedded in the view definition allows callers to retrieve all managers reachable from a given starting manager without writing the recursive query themselves. A representative query selecting the distinct manager population is:

• SELECT DISTINCT starting_manager_id, starting_manager_name FROM apps.pa_rep_managers_v WHERE starting_manager_name IS NOT NULL;
• SELECT starting_manager_id, manager_id, view_resources FROM apps.pa_rep_managers_v WHERE starting_manager_id = :p_manager_id;
• SELECT starting_manager_id, starting_manager_name FROM apps.pa_rep_managers_v WHERE view_resources = 'ALL_RESOURCES' ORDER BY starting_manager_name;

The first query is typical of an LOV source. The second inspects the full set of managers reachable from a specified manager and uses VIEW_RESOURCES to separate the direct-report context from the broader hierarchy. The third returns only those starting managers that have at least one non-self hierarchical relationship, which is useful when building an organizational roll-up. In all cases, values are constrained to records effective on the current system date, so results change as resource effective dates advance.