Search Results parent_sort_order




Overview
Explore in AI Chat

Overview

APPS.PA_RESOURCE_LIST_MEMBERS_V is a reporting view in Oracle E-Business Suite (validated against 12.1.1 and 12.2.2) that presents the hierarchical members of project resource lists. Resource lists group people, organizations, or non-labor resources for use in project planning, staffing, and costing. The view exposes the flat rows stored in the underlying members table while simultaneously resolving each member against its parent row via a self-join, allowing consumers to reconstruct the tree structure without recursive queries. It is owned by the APPS schema and is intended primarily for reporting, Oracle Discoverer/BI Publisher extracts, and integration interfaces that need a denormalized, level-aware representation of resource list membership.

Underlying Base Objects

The view is defined exclusively over the single base object PA_RESOURCE_LIST_MEMBERS (referenced through a synonym). The view text joins that table to itself using two aliases, RLM1 and RLM2. The join predicate is:

This self-join ensures that every member row is matched either to its parent member row (when PARENT_MEMBER_ID is populated) or to itself (when it is a top-level member). The result is a one-row-per-member result set that carries both the member's own attributes and the parent's ordering attributes. Because the view is built only from PA_RESOURCE_LIST_MEMBERS, it inherits that table's multi-org and installation data context; no additional joined tables are referenced in the documented definition.

Key Columns

Each column is derived directly from RLM1 unless noted otherwise:

  • ALIAS — the member alias, prefixed with indentation generated by LPAD(' ',2*(RLM1.MEMBER_LEVEL-1)), producing a visual hierarchy for report output. (Note: MEMBER_LEVEL is referenced in the LPAD expression but is not projected as a separate column.)
  • RESOURCE_LIST_ID — identifies the parent resource list to which the member belongs.
  • RESOURCE_LIST_MEMBER_ID — primary key of the member row, also used as the self-join target (parent_member_id values point here).
  • RESOURCE_ID — the underlying resource (person, organization, or non-labor resource) that the member represents.
  • TRACK_AS_LABOR_FLAG — indicates whether the member is tracked as labor.
  • PARENT_MEMBER_ID — the self-referencing foreign key that establishes hierarchy; the user's search term. A NULL value denotes a top-level member.
  • Sort order (DECODE expressions) — two derived ordering columns: one returns RLM1.SORT_ORDER for top-level members and RLM2.SORT_ORDER for children; the second returns 0 for top-level members and RLM1.SORT_ORDER for children. Together these support hierarchical sort sequencing.
  • MIGRATION_CODE — a migration/upgrade tracking code carried on the member row.

Common Use Cases and Queries

Typical scenarios include listing all members of a resource list in hierarchical order, locating top-level members, and resolving parent-child relationships, which is directly relevant to searches on parent_member_id.

  • Hierarchical listing for a specific list:

SELECT alias, resource_list_member_id, resource_id, parent_member_id FROM apps.pa_resource_list_members_v WHERE resource_list_id = :list_id ORDER BY resource_list_id, sort_order;

  • Locating top-level (root) members:

SELECT alias, resource_list_member_id FROM apps.pa_resource_list_members_v WHERE parent_member_id IS NULL AND resource_list_id = :list_id;

  • Identifying all children of a given parent:

SELECT alias, resource_list_member_id FROM apps.pa_resource_list_members_v WHERE parent_member_id = :member_id;

Because the view performs the self-join internally, these queries avoid manual recursion, though note the join filters rows where PARENT_MEMBER_ID points to a nonexistent member. The view is recommended for read-only reporting; DML should target PA_RESOURCE_LIST_MEMBERS directly. Oracle EBS 12.1.1 and 12.2.2 share this definition, so SQL written against one release remains portable to the other.