Search Results per_number_generation_controls




Overview
Explore in AI Chat

Overview

PER_NUMBER_GENERATION_CONTROLS 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 counters and control values used by Oracle HRMS to automatically generate sequential numbers for records belonging to a specific Business Group. Rather than deriving identifiers by scanning existing rows, Oracle HR reads the current counter from this table, increments it, and assigns the resulting value to the target entity. This mechanism underpins the auto-numbering of key HR entities such as employees, applicants, positions, and other Business Group–scoped records that require a system-generated reference number.

Because the table is keyed by both a generation TYPE and a BUSINESS_GROUP_ID, it isolates numbering sequences per Business Group and per numbered entity type. This prevents number collisions between Business Groups and allows each Group to maintain independent sequences. From a dimensional modeling perspective, the ETRM metadata classifies this object as satellite-leaning. This is best read as a modeling suggestion: the table behaves as a satellite that hangs off the Business Group (organization) hub, carrying descriptive and mutable state — the current counter value — rather than acting as an independent hub of business keys or a link resolving many-to-many relationships. Its foreign key to HR_ALL_ORGANIZATION_UNITS reinforces this hub-and-satellite interpretation.

Key Information Stored

The documented physical schema contains eight columns. The most significant for functional and integration purposes are:

  • TYPE — Identifies the class of entity for which numbers are being generated (for example, employee number, applicant number, or another Business Group–scoped sequence). It is part of the primary key.
  • BUSINESS_GROUP_ID — The Business Group (organization) to which the generated sequence belongs. It is part of the primary key and a foreign key to HR_ALL_ORGANIZATION_UNITS.
  • NEXT_VALUE — The next value to be assigned in the sequence. This is the operative counter that Oracle HR increments and consumes each time a new number is generated.
  • LAST_UPDATE_DATE — Audit timestamp of the most recent modification to the row.
  • LAST_UPDATED_BY — The user or process that last updated the row.
  • LAST_UPDATE_LOGIN — The login context associated with the last update.
  • CREATED_BY — The user or process that created the row.
  • CREATION_DATE — Audit timestamp for row creation.

The surrogate primary key is defined by the unique index PER_NUMBER_GENERATION_CONT_PK, whose columns are (TYPE, BUSINESS_GROUP_ID). In this table the unique index and the primary key coincide, so TYPE plus BUSINESS_GROUP_ID together form the business-key candidate that uniquely identifies each sequence. The remaining columns (NEXT_VALUE and the standard WHO audit columns) are descriptive or state attributes rather than key components.

Common Use Cases and Queries

The primary operational use case is auto-number generation during HR data entry. When a user or concurrent process creates a numbered record, HR reads NEXT_VALUE for the relevant TYPE and BUSINESS_GROUP_ID, assigns it, and advances the counter. Database administrators and technical consultants query this table to diagnose numbering issues, such as gaps, duplicates, or a sequence that has fallen behind.

A typical inspection query retrieves the current counter for a Business Group:

  • SELECT type, business_group_id, next_value FROM per_number_generation_controls WHERE business_group_id = :p_bg_id;

To review all sequences for a given Business Group with organization context, join to HR_ALL_ORGANIZATION_UNITS:

  • SELECT p.type, o.name, p.next_value FROM per_number_generation_controls p JOIN hr_all_organization_units o ON o.organization_id = p.business_group_id;

Reporting use cases include auditing sequence headroom to detect approaching limits, reconciling the highest assigned numbers against the stored NEXT_VALUE, and comparing sequences across Business Groups after a consolidation or migration. Because auditing of WHO columns is standard, change tracking queries can leverage LAST_UPDATE_DATE and LAST_UPDATED_BY to identify who last advanced a sequence.

Related Objects

The documented foreign key relationship and the numbering architecture point to the following significant related objects and join columns:

  • HR_ALL_ORGANIZATION_UNITS — Referenced by BUSINESS_GROUP_ID (PER_NUMBER_GENERATION_CONTROLS.BUSINESS_GROUP_ID → HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID). This is the defining parent relationship, identifying the Business Group.
  • HR_ALL_ORGANIZATION_UNITS (Business Group view) — Commonly filtered to Business Group classification to resolve group names for reporting.
  • PER_ALL_PEOPLE_F — Consumers of generated employee numbers; assigned numbers here correspond to person records.
  • PER_ALL_ASSIGNMENTS_F — Assignment records that rely on person numbering downstream.
  • PER_APPLICANTS / PER_ALL_PEOPLE_F (applicant rows) — Applicant records frequently use dedicated TYPE sequences from this table.
  • PER_NUMBER_GENERATION_CONTROLS API and Business Group setup forms — UI and API layers that read and advance NEXT_VALUE during data entry.

Together these objects form the numbering chain: a Business Group owns one or more numbered sequences, and the values those sequences produce are consumed by people, assignments, and applicant records across Oracle HRMS.