Search Results parent_sort_order

Overview

PA_RESOURCE_LIST_MEMBERS_V is an APPS-owned view in the Oracle E-Business Suite Projects (PA) module that exposes the membership records of resource lists. A resource list is the mechanism by which Oracle Projects groups people, organizations, and other resource types for use in project staffing, assignment searches, and reporting. This view presents every member of every list, together with the member's resource alias and its ordering information, and it is the standard read-only interface used by forms, concurrent programs, and custom reports that need to display or extract resource list hierarchies.

The view is valid and available in both EBS 12.1.1 and 12.2.2. Its distinguishing feature is that it flattens the parent/child relationship held in the base table and computes a display alias with indentation proportional to the member's level in the hierarchy. This makes it suitable for presenting a resource list as an indented tree without the caller writing recursive SQL.

Underlying Base Objects

The view is defined over a single base object, the synonym PA_RESOURCE_LIST_MEMBERS, which resolves to APPS.PA_RESOURCE_LIST_MEMBERS. The view text references that table twice, using the aliases RLM1 and RLM2. This self-join is the core of the view's logic:

• RLM1 supplies the primary member row — the record being reported.
• RLM2 supplies the parent member row, matched by the condition NVL(RLM1.PARENT_MEMBER_ID, RLM1.RESOURCE_LIST_MEMBER_ID) = RLM2.RESOURCE_LIST_MEMBER_ID.

The NVL construct is significant: a top-level member has no parent, so PARENT_MEMBER_ID is null and the row is joined to itself. Child members join upward to their actual parent. This single predicate simultaneously satisfies the parent lookup and the self-reference case, and it means the view returns one row per resource list member with no duplication.

Key Columns

• ALIAS — The member's alias, left-padded with two spaces per level of depth (LPAD(' ', 2*(RLM1.MEMBER_LEVEL-1))). The result is a pre-indented label suitable for direct display in reports and lists.
• RESOURCE_LIST_ID — Identifier of the resource list to which the member belongs.
• RESOURCE_LIST_MEMBER_ID — Primary key of the member row in the base table. This is also the value referenced by PARENT_MEMBER_ID of child rows.
• RESOURCE_ID — Identifier of the underlying resource (person, organization, or other resource type) assigned to the list.
• TRACK_AS_LABOR_FLAG — Indicates whether the member is tracked as labor, which affects how effort and cost are attributed for that resource.
• PARENT_MEMBER_ID — The parent member within the same resource list; null for top-level members. This is the column most frequently queried when reconstructing or validating list hierarchies, and it is the key column implicated in parent/child lookups.
• PARENT_SORT_ORDER — Derived via DECODE(RLM1.PARENT_MEMBER_ID, NULL, RLM1.SORT_ORDER, RLM2.SORT_ORDER). For top-level members the member's own sort order is returned; for children, the parent's sort order is returned, so children can be grouped beneath their parent.
• SORT_ORDER — Derived via DECODE(RLM1.PARENT_MEMBER_ID, NULL, 0, RLM1.SORT_ORDER). Top-level members always return zero; children return their own sequence number within the parent.
• MIGRATION_CODE — Migration-related code carried from the base table, used to identify the provenance of migrated list data.

Common Use Cases and Queries

Typical uses include populating resource list values in staffing and assignment LOVs, producing indented list reports, and auditing hierarchy integrity. Sorting by RESOURCE_LIST_ID, PARENT_SORT_ORDER, and SORT_ORDER reproduces the intended display order.

• List all members of a given list in display order:

SELECT alias
     , resource_list_member_id
     , resource_id
     , track_as_labor_flag
     , parent_member_id
  FROM pa_resource_list_members_v
 WHERE resource_list_id = :p_resource_list_id
 ORDER BY parent_sort_order, sort_order, alias;

• Retrieve only the top-level members of a list:

SELECT alias
     , resource_list_member_id
     , resource_id
  FROM pa_resource_list_members_v
 WHERE resource_list_id = :p_resource_list_id
   AND parent_member_id IS NULL
 ORDER BY sort_order, alias;

• Find all children of a specific member using the parent_member_id predicate:

SELECT alias
     , resource_list_member_id
     , resource_id
  FROM pa_resource_list_members_v
 WHERE resource_list_id = :p_resource_list_id
   AND parent_member_id = :p_parent_member_id
 ORDER BY sort_order, alias;

Because the view reads only PA_RESOURCE_LIST_MEMBERS, it is inexpensive to query, but it exposes no effective dates and no resource name attributes. Reports requiring resource names should join RESOURCE_ID to the appropriate resource views or tables. As with all APPS views, the object is owned by APPS and must be accessed through a synonym or with the APPS schema qualified when used in custom code.