Search Results per_gen_hierarchy_versions




Overview
Explore in AI Chat

Overview

The PER_GEN_HIERARCHY_VERSIONS table, owned by the HR schema, is a core repository in the Oracle E-Business Suite Human Resources (PER) module that stores versioned definitions of generic hierarchies. It serves as the master container for each logical version of a hierarchy, capturing the temporal and status attributes that control whether a given version is active, pending, or historical. Within Oracle EBS 12.1.1 and 12.2.2, this table underpins organizational and reporting hierarchies used across HR, payroll, and other modules that consume hierarchical structures.

From a Data Vault modeling perspective, the heuristic classification of this object is satellite-leaning. Because it stores descriptive, version-scoped attributes (dates, status, validate flags) attached to a business concept and is itself referenced by dependent node records, it behaves as a satellite rather than a pure hub or link table. This classification is offered as a modeling suggestion and reflects the ETRM FK-structure analysis rather than a prescriptive design mandate.

Key Information Stored

Each row in PER_GEN_HIERARCHY_VERSIONS represents a single version of a hierarchy. The most significant columns include:

The table contains 81 columns, including a large DESCRIPTIVE flexfield set (ATTRIBUTE1–ATTRIBUTE30) and an INFORMATION flexfield set (INFORMATION1–INFORMATION30), plus ATTRIBUTE_CATEGORY and INFORMATION_CATEGORY. These flexfield columns enable customers to extend the hierarchy version without customization.

Common Use Cases and Queries

Typical scenarios involve identifying the currently effective version of a hierarchy, auditing version history, and joining versions to their node rows for reporting. A common pattern is:

  • Selecting active versions for a business group within a date range: SELECT HIERARCHY_VERSION_ID, HIERARCHY_ID, VERSION_NUMBER, DATE_FROM, DATE_TO FROM PER_GEN_HIERARCHY_VERSIONS WHERE BUSINESS_GROUP_ID = :p_bg AND STATUS = 'A' AND SYSDATE BETWEEN DATE_FROM AND DATE_TO;
  • Auditing changes by concurrent program via REQUEST_ID and PROGRAM_ID.
  • Joining to PER_GEN_HIERARCHY_NODES on HIERARCHY_VERSION_ID to reconstruct the full node tree for a chosen version.

Report writers frequently filter on VALIDATE_FLAG to exclude unvalidated drafts from downstream processes.

Related Objects

  • HR_ALL_ORGANIZATION_UNITS — referenced via BUSINESS_GROUP_ID; provides the organizational context.
  • PER_GEN_HIERARCHY_NODES — references this table via HIERARCHY_VERSION_ID; stores the individual node records belonging to each version.
  • PER_GEN_HIERARCHIES (or the equivalent hierarchy-definition entity implied by HIERARCHY_ID) — the parent concept linking multiple versions.

APIs and programmatic access typically route through the Human Resources hierarchy management concurrent programs, which populate REQUEST_ID and PROGRAM_ID on insert and update.