Search Results per_gen_hierarchy




Overview
Explore in AI Chat

Overview

PER_GEN_HIERARCHY is a Human Resources (PER) module table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the definition of generic hierarchies used within Oracle HRMS, most notably providing the structural header that calendar entries reference through the HIERARCHY_ID foreign key. The table therefore acts as the parent definition record for time-related hierarchy structures consumed by payroll, absence, and workforce scheduling functionality.

Under the heuristic Data Vault classification derived from its foreign key topology, PER_GEN_HIERARCHY is characterized as satellite-leaning. It holds descriptive attributes and audit information tied to a defined hierarchy rather than serving purely as a link between two business entities. The presence of a small number of inbound references and a single outbound foreign key to HR_ALL_ORGANIZATION_UNITS supports this classification as a modeling suggestion rather than a strict Data Vault designation.

Key Information Stored

The documented physical schema comprises 77 columns. The most operationally significant columns are:

  • HIERARCHY_ID — the surrogate primary key, defined by PER_GEN_HIERARCHY_PK. It uniquely identifies each hierarchy record and is the column referenced by dependent tables.
  • NAME — the business-meaningful descriptive name of the hierarchy; part of the unique business-key index PER_GEN_HIERARCHY_UK1.
  • BUSINESS_GROUP_ID — the foreign key to HR_ALL_ORGANIZATION_UNITS, scoping the hierarchy to a specific business group; also part of the unique business key.
  • TYPE — classifies the kind of hierarchy being defined.
  • ZD_EDITION_NAME — the editioning column included in the extended primary key, supporting the 12.2 online patching (adop) architecture.
  • OBJECT_VERSION_NUMBER — the optimistic locking column used by Oracle Application Framework (OAF) and the HRMS APIs.
  • REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program audit columns identifying the request that created or last modified the record.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — the standard WHO-column audit trail.
  • ATTRIBUTE_CATEGORY, ATTRIBUTE1–30 — the descriptive flexfield (DFF) segment storage.
  • INFORMATION_CATEGORY, INFORMATION1–30 — the extra information / developer extensibility column set used by HRMS to store additional descriptive details without schema change.

The unique index PER_GEN_HIERARCHY_PK covers (HIERARCHY_ID, ZD_EDITION_NAME), while the business-key candidate PER_GEN_HIERARCHY_UK1 covers NAME and BUSINESS_GROUP_ID. Note that the foreign key relationship is documented from PER_CALENDAR_ENTRIES.HIERARCHY_ID into this table.

Common Use Cases and Queries

Typical scenarios involve resolving a calendar entry to its parent hierarchy, reporting on hierarchy definitions within a business group, and driving validation logic in absence or payroll calendar configuration.

  • Retrieving the hierarchy behind a calendar entry:

    SELECT h.hierarchy_id, h.name, h.type FROM per_gen_hierarchy h, per_calendar_entries c WHERE c.hierarchy_id = h.hierarchy_id;

  • Listing all hierarchies for a business group:

    SELECT hierarchy_id, name, type FROM per_gen_hierarchy WHERE business_group_id = :p_bg_id ORDER BY name;

  • Resolving the business group name via the HR_ALL_ORGANIZATION_UNITS join for reporting.
  • Auditing records created by a specific concurrent request using REQUEST_ID and PROGRAM_ID.
  • Identifying the hierarchy type distribution to support calendar configuration reviews.

Related Objects

  • PER_CALENDAR_ENTRIES — references PER_GEN_HIERARCHY through the HIERARCHY_ID foreign key column.
  • HR_ALL_ORGANIZATION_UNITS — parent object for the BUSINESS_GROUP_ID foreign key.
  • PER_GEN_HIERARCHY_PK / PER_GEN_HIERARCHY_UK1 — primary key and unique indexes enforcing identity and business-key uniqueness.
  • HRMS generic hierarchy APIs and the HR_PER_GEN_HIERARCHY combined DFF — process the descriptive and information flexfield columns.
  • ZD_EDITION infrastructure objects — support editioning for online patching in 12.2.