Results for “struct_version_id”

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

Overview

The PJI_RBS_DENORM table resides in the PJI schema, which corresponds to the Project Intelligence module of Oracle EBS. The module is marked Obsolete in the ETRM metadata, indicating that Oracle has desupported or superseded this functionality in the 12.1.1 and 12.2.2 release streams. The table stores normalized resource breakdown structure (RBS) information, denormalizing resource hierarchy relationships into a flat, query-efficient form. Rather than reconstructing parent-child chains recursively from the base RBS elements, Project Intelligence reporting can read precomputed level and leaf indicators from this table.

The ETRM data vault classification mined from the foreign key structure is link. Under this heuristic, PJI_RBS_DENORM is best modeled as a link table that associates two business entities — a resource breakdown structure version and its member elements — across a defined hierarchy. The SUP_ID and SUB_ID columns resolve to the same parent table, PA_RBS_ELEMENTS, making this a self-referencing association expressed as a link between a superior element, a subordinate element, and a structure version context.

Key Information Stored

The documented physical schema contains twelve columns. The most significant are:

• STRUCT_VERSION_ID — Foreign key to PA_RBS_VERSIONS_B; identifies the RBS version whose hierarchy is being flattened.
• SUP_ID — Foreign key to PA_RBS_ELEMENTS; the superior (parent) resource element in the pair.
• SUB_ID — Foreign key to PA_RBS_ELEMENTS; the subordinate (child) resource element.
• SUP_LEVEL — The hierarchy depth of the superior element within the structure version.
• SUB_LEVEL — The hierarchy depth of the subordinate element.
• SUB_LEAF_FLAG — Indicates whether the subordinate element is a leaf node, useful for pruning detail-level rollups.
• SUBRO_ID — An additional subordinate-related identifier retained for denormalized join resolution.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns recording who created and last modified each row and through which login session.

No surrogate primary key column is documented in the ETRM extract; the table appears to rely on the composite of STRUCT_VERSION_ID, SUP_ID, and SUB_ID as its business key. Because the documented metadata does not list a unique index, the composite should be treated as a business-key candidate rather than a confirmed unique constraint.

Common Use Cases and Queries

Typical usage centers on reconstructing or reporting resource breakdown hierarchies without recursive SQL. A common pattern joins the denormalized rows to element descriptions:

• Listing all subordinate elements one level below a given superior: filter on STRUCT_VERSION_ID and SUP_ID, then join SUB_ID to PA_RBS_ELEMENTS.
• Identifying leaf resources for detailed reporting: filter on SUB_LEAF_FLAG.
• Computing depth-based rollups: compare SUP_LEVEL and SUB_LEVEL to determine hierarchy spans.
• Auditing hierarchy changes: use LAST_UPDATE_DATE and LAST_UPDATED_BY to trace maintenance activity.

Sample SQL:

SELECT d.STRUCT_VERSION_ID, d.SUP_ID, d.SUB_ID, d.SUP_LEVEL, d.SUB_LEVEL, d.SUB_LEAF_FLAG FROM PJI.PJI_RBS_DENORM d WHERE d.STRUCT_VERSION_ID = :version_id AND d.SUB_LEAF_FLAG = 'Y';

Because the PJI module is obsolete, use is limited to legacy installations and historical reporting rather than new development.

Related Objects

• PA_RBS_VERSIONS_B — joined on STRUCT_VERSION_ID; defines the RBS version context.
• PA_RBS_ELEMENTS — joined twice, on SUP_ID and SUB_ID; supplies element attributes and names.
• PA_RBS_VERSIONS_TL — translation table for version names, joined via STRUCT_VERSION_ID.
• PA_RBS_ELEMENT_VERSIONS — links elements to versions, providing alternate hierarchy traversal paths.
• PJI_RBS_* tables — sibling Project Intelligence RBS objects that share the same version and element keys.