Search Results pa_xbs_denorm




Overview
Explore in AI Chat

Overview

PA_XBS_DENORM is a denormalized Projects (PA) schema table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores flattened work breakdown structure (WBS) and program hierarchy relationships. Rather than requiring recursive traversal of parent-child element structures at query time, the table materializes each superior-to-subordinate relationship as a single row, together with level indicators and rollup attributes. This makes it a high-value access path for reporting, hierarchical drill-down, and cross-project structure analysis.

The ETRM metadata records the object as VALID and owned by the PA schema, with twenty documented columns in the 12.2.2 physical definition. Based on its foreign key structure—three inbound references to PA_PROJ_ELEMENT_VERSIONS and PA_PROJ_ELEMENTS via STRUCT_VERSION_ID, SUP_ID, and SUB_ID—the heuristic Data Vault classification for this table is a link. This is a modeling suggestion only: the table behaves as a relationship entity connecting superior and subordinate project elements within a specific structure version, with descriptive attributes (levels, rollup IDs, leaf flags) carried alongside the linkage.

Key Information Stored

The most significant columns fall into three functional groups: structural identity, hierarchy linkage, and descriptive attributes.

  • STRUCT_VERSION_ID — Foreign key to PA_PROJ_ELEMENT_VERSIONS. Identifies the specific structure version in which the relationship is valid, enabling point-in-time reconstruction of the hierarchy.
  • SUP_ID and SUB_ID — Foreign keys to PA_PROJ_ELEMENTS. Together they define the parent (superior) and child (subordinate) element of each relationship, forming the core business key of the denormalized row.
  • SUP_PROJECT_ID — Project identifier of the superior element, supporting project-scoped filtering without an additional join.
  • SUP_EMT_ID, SUB_EMT_ID, and STRUCT_EMT_ID — Element and structure type identifiers that classify the nature of each node and relationship.
  • STRUCT_TYPE and RELATIONSHIP_TYPE — Control the semantic category of the hierarchy record (for example, WBS versus program rollup), which is essential when the same element participates in multiple structure contexts.
  • PRG_GROUP — Program grouping used to bucket related elements for program-level reporting.
  • SUP_LEVEL and SUB_LEVEL — Precomputed depth indicators for superior and subordinate nodes, allowing level-based queries and indentation logic without recursive SQL.
  • SUB_ROLLUP_ID and SUB_LEAF_FLAG — Denormalized rollup pointer and leaf indicator, frequently used to drive cost rollup and to exclude parent nodes from detail reporting.
  • SUBRO_ID — Subordinate rollup reference retained for rollup-oriented traversals.
  • LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, and LAST_UPDATED_BY — Standard auditing columns used for change tracking and incremental extract logic.

PA_XBS_DENORM does not expose a single-column numeric surrogate in the documented metadata; identification relies on the STRUCT_VERSION_ID, SUP_ID, and SUB_ID combination. In the 12.2.2 schema that combination functions as the effective business-key candidate.

Common Use Cases and Queries

The primary use case is flattened hierarchy reporting: locating all descendants or ancestors of a WBS element within a given structure version without recursive CONNECT BY traversal. A typical pattern joins PA_XBS_DENORM to PA_PROJ_ELEMENTS on both SUP_ID and SUB_ID to retrieve parent and child names in a single pass:

  • Descendant extraction: SELECT sub_id, sub_level FROM pa_xbs_denorm WHERE struct_version_id = :ver AND sup_id = :element.
  • Leaf-only detail reporting: filter on SUB_LEAF_FLAG = 'Y' to exclude summary nodes from expenditure or budget detail extracts.
  • Level-based rollups: group by SUP_LEVEL or SUB_LEVEL for budget-versus-actual analysis at a consistent hierarchy depth.
  • Program rollup analysis: aggregate by PRG_GROUP and STRUCT_TYPE where program structures share element definitions with WBS structures.
  • Incremental interfaces: restrict extracts using LAST_UPDATE_DATE to feed downstream data warehouses.

Related Objects

  • PA_PROJ_ELEMENTS — Referenced twice, via SUP_ID and SUB_ID, supplying superior and subordinate element definitions.
  • PA_PROJ_ELEMENT_VERSIONS — Referenced via STRUCT_VERSION_ID, providing version-effective structure context.
  • PA_PROJECTS_ALL — Joined on SUP_PROJECT_ID to resolve project name and number for the superior element.
  • PA_PROJ_ELEMENT_DENORM — Companion denormalized element table commonly queried alongside this object in hierarchy reporting.
  • PA_PROJECT_STRUCTURES and PA_STRUCTURES — Define structure types and versions referenced by STRUCT_VERSION_ID and STRUCT_TYPE.
  • PA_EXPENDITURE_ITEMS_ALL and PA_BUDGET_LINES_ALL — Transaction tables whose WBS references are frequently rolled up through this table's level and leaf attributes.
  • PA_WBS_DENORM_V and related public views — Reporting-layer objects that expose denormalized hierarchy data to Oracle Projects inquiry pages.