Results for “per_number_generation_cont_pk”

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

Overview

PER_NUMBER_GENERATION_CONTROLS is an Oracle HR (HR schema) transactional table that stores the next value in a numeric sequence for a given sequence type within a specific Business Group. Rather than relying on database sequences to generate employee and applicant numbers, Oracle E-Business Suite maintains this control table so that number generation remains contiguous and independent across multiple Business Groups operating within the same Oracle HR account. A single row is held for each TYPE within a Business Group, and the NEXT_VALUE column is incremented by one each time a number of that type is consumed.

The object is registered under FND Design Data as PER.PER_NUMBER_GENERATION_CONTROLS, carries a status of VALID, and resides in the APPS_TS_TX_DATA tablespace with PCT Free of 10. From a Data Vault modeling perspective, the heuristic classification is satellite-leaning, since the table primarily captures descriptive, mutable state (the current counter value) keyed by a parent Business Group rather than representing an independent business entity or an associative relationship between two hubs.

Key Information Stored

The table comprises eight documented columns. The most operationally significant are:

• TYPE (VARCHAR2, 30) — Identifies the numeric sequence type being tracked (for example, employee or applicant number generation). Part of the composite primary key.
• BUSINESS_GROUP_ID (NUMBER, 15) — Foreign key to HR_ORGANIZATION_UNITS identifying the Business Group that owns the sequence. Part of the composite primary key.
• NEXT_VALUE (NUMBER, 30) — The next value that will be assigned when a number of this TYPE is requested; updated to NEXT_VALUE + 1 after each use.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard Who columns providing audit traceability for row creation and modification.

The primary key is PER_NUMBER_GENERATION_CONT_PK, a unique composite index on (TYPE, BUSINESS_GROUP_ID). This pair constitutes the business-key candidate that uniquely identifies a generation control row. A second nonunique index, PER_NUMBER_GENERATION_CONT_FK1, exists on BUSINESS_GROUP_ID to support the foreign key to HR_ALL_ORGANIZATION_UNITS. The metadata does not document a separate surrogate key; the composite natural key serves as the row identifier.

Common Use Cases and Queries

The primary use case is retrieving and reserving the next number for a given TYPE and Business Group during employee or applicant creation. Administrators also query this table to audit current counter values, diagnose noncontiguous numbering, or plan data migrations between Business Groups.

A representative query retrieves the current counter for all Business Groups:

• SELECT TYPE, BUSINESS_GROUP_ID, NEXT_VALUE FROM HR.PER_NUMBER_GENERATION_CONTROLS ORDER BY BUSINESS_GROUP_ID, TYPE;
• Reporting on counters for a single Business Group: SELECT TYPE, NEXT_VALUE FROM HR.PER_NUMBER_GENERATION_CONTROLS WHERE BUSINESS_GROUP_ID = :p_bg_id;
• Joining to organization units to resolve the Business Group name: SELECT c.TYPE, o.NAME, c.NEXT_VALUE FROM HR.PER_NUMBER_GENERATION_CONTROLS c, HR_ALL_ORGANIZATION_UNITS o WHERE c.BUSINESS_GROUP_ID = o.ORGANIZATION_ID;

Because NEXT_VALUE is updated in place, scripts that modify this column directly must be carefully coordinated with the HR number generation APIs to avoid duplicate or skipped numbers.

Related Objects

The FK relationship data documents one direct dependency: BUSINESS_GROUP_ID references HR_ALL_ORGANIZATION_UNITS (HR_ORGANIZATION_UNITS), which resolves the owning Business Group. Indirectly, this table underpins the HR employee and applicant number generation routines that read and increment NEXT_VALUE during record creation. The composite primary key PER_NUMBER_GENERATION_CONT_PK and the supporting FK index PER_NUMBER_GENERATION_CONT_FK1 are the principal physical structures tied to the object. Consumers of this data include the HR person and applicant management APIs, payroll and assignment setup processes that rely on Business Group scoped numbering, and reporting extracts that reconcile assigned employee numbers against the counter values stored here.