Search Results resource_effective_end_date




Overview
Explore in AI Chat

Overview

APPS.PA_REP_ALL_MGR_V is a reporting view in the Oracle E-Business Suite Projects (PA) module that resolves the reporting hierarchy between resources and their managers. Its purpose is to return, for each manager, the set of managers that fall beneath that manager in the resource reporting structure, while tagging each row as either a DIRECT_REPORTS relationship or an ALL_RESOURCES relationship. The view is central to Oracle Project Resource Management (PRM) and Oracle Project Management reporting where organizational roll-up, resource authorization, and manager-centric queries are required.

The view is defined in the APPS schema and is owned at the application level, meaning it is exposed for use in concurrent programs, OAF pages, BI Publisher reports, and ad hoc SQL. Because it is a view and not a table, it always reflects the current state of the underlying resource and reporting denormalized tables, including effective-dated rows valid as of SYSDATE.

Underlying Base Objects

The view is built from a UNION of two queries over the same core objects. The documented referenced base objects are:

Hierarchical traversal across managers is performed using a CONNECT BY PRIOR clause on PA_RESOURCES_DENORM, walking from each manager down through subordinate persons while restricting to rows effective on SYSDATE.

Key Columns

The view exposes manager identifiers and names paired with the relationship classification. The critical columns are:

  • manager_id — the manager identifier that heads the reporting branch. This is the column users most frequently filter or join on.
  • manager_name — the display name of that manager.
  • resource manager_id — the subordinate manager returned by the hierarchy walk; when it equals the top-level manager_id, the row represents a direct report.
  • Relationship indicator — a DECODE expression producing 'DIRECT_REPORTS' when the two manager identifiers match, and 'ALL_RESOURCES' otherwise.

Common Use Cases and Queries

Typical uses include manager roll-up reporting, resource authorization checks, and manager hierarchy prompts in PRM pages. Searching on manager_id is the most common access pattern:

  • Returning the full reporting tree for a given manager.
  • Listing only direct reports by filtering on the relationship indicator.
  • Joining the view to PA_RESOURCES_DENORM to enrich reports with person attributes.

A representative query is:

SELECT manager_id, manager_name, resource_manager_id, relationship
FROM apps.pa_rep_all_mgr_v
WHERE manager_id = :p_manager_id;

Restricting with AND relationship = 'DIRECT_REPORTS' yields only immediate subordinates. Because the view applies SYSDATE-based effective dating and PA_SUPER_RESOURCE gating internally, callers do not need to repeat those predicates.