Results for “pa_rep_org_util_v”

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

Overview

PA_REP_ORG_UTIL_V is a Projects (PA) module view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its purpose is to resolve the set of organizations for which a given application user holds utilization authority, restricted to the reporting organization hierarchy. Specifically, the view returns every parent organization to which the user has been granted authority, together with the corresponding child organizations that roll up under that parent within the reporting hierarchy.

The view is not a transactional object; it is a security-filtering construct. It translates the abstract grant records maintained by the Oracle Application Object Library (FND_GRANTS, FND_OBJECTS) into a concrete, hierarchical list of organization identifiers that downstream reporting and inquiry functions can consume. Because the filter is driven by USERENV('CLIENT_INFO'), the result set is session- and user-dependent, and the view is therefore typically used inside other report queries rather than being exposed directly to end users.

Underlying Base Objects

The view text is defined over four documented base objects. The primary driving object is PA_ORG_HIERARCHY_DENORM, a denormalized hierarchy table that stores parent and child organization relationships. The view restricts this table to rows where PA_ORG_USE_TYPE equals 'REPORTING'. PA_ORG_HIERARCHY_DENORM also carries an ORG_ID column, which the view matches against the current operating unit derived from USERENV('CLIENT_INFO'), defaulting to -99 when the value cannot be resolved.

The second component is a correlated subquery over FND_GRANTS joined to FND_OBJECTS, with a supplementary inline query invoking PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_UTL_AUTH') and PA_SECURITY_PVT.GET_GRANTEE_KEY. This subquery confirms that the user holds an active, non-expired grant on the ORGANIZATION object for the utilization authority menu. Finally, the view calls the packaged function PA_EXPENDITURES_UTILS.GETORGTLNAME twice, once per organization identifier, to translate organization IDs into their translated display names.

Key Columns

• PARENT_ORGANIZATION_ID — Identifier of the parent organization in the reporting hierarchy for which the current user holds utilization authority.
• PARENT_ORG_NAME — Translated name of the parent organization, derived from PA_EXPENDITURES_UTILS.GETORGTLNAME.
• CHILD_ORGANIZATION_ID — Identifier of the child organization subordinate to the parent, also within the reporting hierarchy.
• CHILD_ORG_NAME — Translated name of the child organization, resolved by the same utility function.

The result set is ordered by parent level descending, then by parent organization name ascending, producing a hierarchy-oriented presentation.

Common Use Cases and Queries

The view is principally used to scope utilization reports so that users see only organizations within their authority. A common pattern is to join it to expenditure or utilization facts to enforce row-level security.

SELECT parent_org_name,
       child_organization_id,
       child_org_name
FROM   apps.pa_rep_org_util_v
ORDER  BY parent_org_name, child_org_name;

A second pattern restricts a fact query by the authorized organizations:

SELECT e.expenditure_id,
       e.organization_id,
       v.child_org_name
FROM   apps.pa_expenditures_all e,
       apps.pa_rep_org_util_v  v
WHERE  e.organization_id = v.child_organization_id;

Because the view resolves grants dynamically from FND_GRANTS and depends on session context established via USERENV('CLIENT_INFO'), queries must execute within a properly initialized Oracle EBS session with an active application user and operating unit. Direct SQL execution outside the EBS login environment typically returns no rows or an incorrect set, as the menu and grantee keys cannot be resolved.