Results for “pa_rep_res_orgs_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PA_REP_RES_ORGS_V is an APPS-owned reporting view in the Oracle E-Business Suite Projects (PA) module. Its documented purpose is to list all organizations of the resources associated with a given manager. In practice, the view exposes the reporting relationship between a manager and the resources that report to that manager, together with the resource's owning organization. This makes it a convenience layer for manager-centric reporting where the reporting hierarchy and resource organization are required in a single query without navigating the full denormalized resource model.

The view is particularly relevant to the search term resource_organization_id, which is one of its exposed columns. That column identifies the inventory-style organization to which a resource belongs within Oracle Projects, and it is central to any query that groups, filters, or joins resources by organization. Because the view already resolves the manager-to-resource relationship, it removes the need to derive the reporting hierarchy manually when building dashboards, extracts, or integration payloads.

Underlying Base Objects

Per the documented 12.2.2 metadata, PA_REP_RES_ORGS_V is defined over two referenced objects:

• PA_RESOURCES_DENORM (SYNONYM) — the denormalized resource table that stores the resource rows, including manager, person/resource identifiers, and resource organization.
• PA_EXPENDITURES_UTILS (PACKAGE) — the PL/SQL package whose function GETORGTLNAME is called in the view's SELECT statement to derive the organization name for the resource organization identifier.

The view text confirms this directly: it selects MANAGER_ID, PERSON_ID, RESOURCE_ORGANIZATION_ID, and the result of PA_EXPENDITURES_UTILS.GETORGTLNAME(RESOURCE_ORGANIZATION_ID) from PA_RESOURCES_DENORM. The organization name is therefore not stored on the base table but is resolved at query time through the expenditure utilities package. Because the view is not materialized, both the resource lookup and the organization-name resolution occur when the view is queried, which is an important performance consideration for large resource populations.

Key Columns

• MANAGER_ID — the identifier of the manager to whom the resource reports. This is the primary filter when retrieving the resources for a specific manager.
• PERSON_ID — the identifier of the resource/person, used to join to person and resource-based HR or Projects data.
• RESOURCE_ORGANIZATION_ID — the organization identifier that owns the resource. This is the column matched by the search term and is the key to organization-based filtering, grouping, and joins to organization definition tables.
• ORGANIZATION_NAME — the descriptive organization name returned by PA_EXPENDITURES_UTILS.GETORGTLNAME, supplied as an unaliased expression in the view text.

Note that the documented view text lists PERSON_ID in the SELECT, while the reported column list references RESOURCE_ID and ORGANIZATION_ID/ORGANIZATION_NAME. When relying on these columns, verify the exact exposed column names in the target instance with DESC APPS.PA_REP_RES_ORGS_V, as minor naming variance exists in the documentation.

Common Use Cases and Queries

Typical uses include manager resource reports, organization rollups for project staffing, and integration extracts that require the manager-to-resource-to-organization mapping. A simple query by manager:

• SELECT manager_id, person_id, resource_organization_id, organization_name FROM apps.pa_rep_res_orgs_v WHERE manager_id = :p_manager_id;

To group resources by organization:

• SELECT resource_organization_id, organization_name, COUNT(*) resource_count FROM apps.pa_rep_res_orgs_v GROUP BY resource_organization_id, organization_name;

To locate a specific resource organization relationship:

• SELECT manager_id, person_id FROM apps.pa_rep_res_orgs_v WHERE resource_organization_id = :p_org_id;

Because ORGANIZATION_NAME is derived through a PL/SQL function call, filtering on RESOURCE_ORGANIZATION_ID rather than the name generally yields better performance. For high-volume extracts, consider staging the manager-resource-organization results into a temporary table when the same data is reused across multiple reports.