Search Results migration_code




Overview
Explore in AI Chat

Overview

APPS.PA_BUDGET_RES_ALL_V is an Oracle E-Business Suite view that exposes the hierarchical membership of project resource lists, filtered to only those members that are active and eligible for display. It is defined in the APPS schema and is closely associated with the Project Resource Management and Project Budgeting functional areas. The view is a thin, read-only projection over PA_RESOURCE_LIST_MEMBERS, but it enriches that base structure in three significant ways: it resolves the resource name through a server-side PL/SQL call, it derives an indented alias suitable for tree-style presentation, and it applies the EBS row-filtering conventions (ENABLED_FLAG and DISPLAY_FLAG) so that consumers receive only currently usable resource list members.

The view is documented for both the 12.1.1 and 12.2.2 releases with identical SQL text, making it a stable integration surface across those releases. Because it contains a CONNECT BY clause, it is a recursive view returning every level of a resource list hierarchy in a single result set.

Underlying Base Objects

The view is defined over a single base object plus one PL/SQL dependency:

  • PA_RESOURCE_LIST_MEMBERS (accessed via a SYNONYM) — the primary source of all rows. It holds the resource list header reference, member identifier, parent-child linkage, resource identifier, resource type code, labor tracking flag, display flag, enabled flag, and migration code.
  • PA_RESOURCES_PKG (PACKAGE) — invoked as PA_RESOURCES_PKG.GET_RESOURCE_NAME(RESOURCE_ID, RESOURCE_TYPE_ID) to return the human-readable resource name. The result is truncated to 30 characters via SUBSTR. This dependency means the view cannot be queried if the package is invalid, and it also means the view is not a simple join and cannot be tuned purely at the SQL level.

No base tables are exposed directly; all access is mediated through the view's predicate set. This is typical of EBS views that enforce business filtering rules centrally so that concurrent programs and forms do not each re-implement them.

Key Columns

  • ALIAS — an indentation prefix generated by LPAD(' ', 2*(LEVEL-1)) concatenated with the member alias value. It gives a visual representation of hierarchy depth.
  • ALIAS2 — the raw alias value from the base record, without indentation.
  • RESOURCE_LIST_ID — identifies the parent resource list to which the member belongs.
  • RESOURCE_LIST_MEMBER_ID — the unique member identifier and the pivot of the CONNECT BY relationship.
  • RESOURCE_ID — identifier of the resource (person, organization, or other) referenced by the member.
  • PARENT_MEMBER_ID — the parent member's identifier; NVL is applied to return 0 rather than NULL for root nodes.
  • RESOURCE_NAME — resolved by PA_RESOURCES_PKG.GET_RESOURCE_NAME and limited to 30 characters.
  • TRACK_AS_LABOR_FLAG — indicates whether the resource is tracked as labor for budgeting and costing purposes.
  • MIGRATION_CODE — a data-migration control column on the base record.

Common Use Cases and Queries

The view is commonly used for reporting the flattened membership of a given resource list, validating that a resource is reachable within a list hierarchy, and supporting data-migration or data-fix scripts that must respect EBS filtering conventions.

SELECT alias, resource_list_member_id, resource_id, resource_name,
       track_as_labor_flag, parent_member_id, migration_code
FROM   apps.pa_budget_res_all_v
WHERE  resource_list_id = :p_resource_list_id
ORDER  BY alias;

To identify members that carry a migration code, which is directly relevant to migration work:

SELECT resource_list_id, resource_list_member_id, resource_name,
       migration_code
FROM   apps.pa_budget_res_all_v
WHERE  migration_code IS NOT NULL
ORDER  BY resource_list_id, resource_list_member_id;

To locate the children of a specific member within the hierarchy:

SELECT alias2, resource_name
FROM   apps.pa_budget_res_all_v
WHERE  parent_member_id = :p_parent_member_id;

Two predicate behaviors must be noted. First, only rows with ENABLED_FLAG = 'Y' are returned. Second, the DECODE on RESOURCE_TYPE_CODE forces DISPLAY_FLAG to be treated as 'Y' for UNCLASSIFIED resource types, while other types must have DISPLAY_FLAG = 'Y'. Consequently, any disabled member is invisible even if it has enabled descendants, which affects traversal results. The MIGRATION_CODE filter NVL(migration_code, '-99') <> 'N' excludes records explicitly marked as not migrated but allows NULL and other values through.