Results for “sub_id”

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

Overview

PA_RBS_DENORM is a table in the PA (Projects) schema of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It stores normalized resource breakdown structure (RBS) information, providing the parent-child relationships that define how resources are organized within a resource breakdown structure. In Oracle Projects, an RBS classifies the resources — people, organizations, equipment, and other cost-bearing entities — used in project planning, budgeting, and costing. The RBS is versioned, so the denormalized mapping contained in this table is tied to a specific structure version and is reused across projects and project templates that share the same definition.

The table functions as an adjacency-style map in which each row identifies a superior (parent) element and a subordinate (child) element, together with the level positions of each. This redundancy of both parent and child in the same row, along with stored level values, allows hierarchical traversal without recursive joins. Following a Data Vault classification heuristic mined from the foreign key structure, PA_RBS_DENORM is best modeled as a link table: it connects two instances of a resource element (the superior and the subordinate) and a structure version, capturing the relationships between them rather than descriptive attributes of the elements themselves.

Key Information Stored

PA_RBS_DENORM contains twelve documented columns. The structural and hierarchical columns are the most significant:

• STRUCT_VERSION_ID — Foreign key to PA_RBS_VERSIONS_B, identifying the resource breakdown structure version to which the relationship belongs. This is the primary scoping attribute for any query.
• SUB_ID — Foreign key to PA_RBS_ELEMENTS, identifying the subordinate (child) resource element in the relationship.
• SUP_ID — Identifies the superior (parent) resource element, effectively a self-referencing business key against PA_RBS_ELEMENTS.
• SUBRO_ID — Identifies the subordinate element in the rolled-up relationship context, supporting the denormalized representation of the hierarchy.
• SUP_LEVEL and SUB_LEVEL — The level numbers of the superior and subordinate elements within the structure, enabling depth-based reporting and ordering without recursion.
• SUB_LEAF_FLAG — Indicates whether the subordinate element is a leaf node, useful for identifying terminal resources in the hierarchy.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard Oracle EBS audit columns recording who created and last modified each relationship row and when.

The table has no documented surrogate primary key column of its own; the effective unique business key is the combination of STRUCT_VERSION_ID, SUP_ID, and SUB_ID. Developers should treat this composite as the logical identifier when joining or de-duplicating.

Common Use Cases and Queries

Typical uses include reporting on RBS hierarchy levels, validating that resource assignments fall to valid RBS nodes, and exporting structure definitions for integration with planning or costing processes. A common pattern retrieves all children of a given parent within a version:

SELECT SUB_ID, SUB_LEVEL, SUB_LEAF_FLAG
FROM   PA_RBS_DENORM
WHERE  STRUCT_VERSION_ID = :version_id
AND    SUP_ID = :parent_element_id
ORDER BY SUB_ID;

Leaf resources can be isolated for resource-level budgeting or assignment queries by filtering on SUB_LEAF_FLAG. Level-based roll-ups may aggregate subordinate counts by SUP_LEVEL to show the breadth of each tier. Because PA_RBS_DENORM is denormalized, hierarchy paths spanning multiple levels may be reconstructed by self-joining on matching STRUCT_VERSION_ID values rather than issuing recursive queries.

Related Objects

• PA_RBS_VERSIONS_B — Referenced via PA_RBS_DENORM.STRUCT_VERSION_ID; holds the version header that scopes every relationship row.
• PA_RBS_ELEMENTS — Referenced via PA_RBS_DENORM.SUB_ID (and mirrored by SUP_ID); holds the resource elements that participate in each parent-child pairing.

Additional tables in the PA resource breakdown structure family, such as the version translations and element assignment tables, frequently join to PA_RBS_DENORM through STRUCT_VERSION_ID and the element identifiers. Developers should confirm current column definitions against the specific EBS release in use, as the documented physical schema reflects 12.2.2.