Search Results derive_single_race

Overview

The APPS.PER_US_HR_UTILITY_PKG package is a United States–specific Human Resources utility package that supplies derived statutory classification values for a person record. Its primary business purpose is to support US regulatory and reporting requirements — most notably EEO-1, VETS-100/100A, and affirmative action reporting — by computing values that are not stored directly on the person record but must be inferred from the combination of race, ethnicity, and citizenship-related attributes. The package is owned by APPS and is classified in the ETRM documentation with an API classification of OTHER, indicating that it is an internal utility rather than a formally published or supported public API.

Two utility functions are documented: DERIVE_ALIEN_ETHNIC_ORIGIN and DERIVE_SINGLE_RACE. Both return a numeric value derived from the person identifier supplied by the caller. The package header is declared AUTHID CURRENT_USER, so its SQL executes with the privileges of the calling schema rather than as the definer, which is significant for any custom code invoking it.

Key Procedures and Functions

• DERIVE_ALIEN_ETHNIC_ORIGIN — Returns a numeric code representing the derived alien ethnic origin classification for a person. Its declaration accepts a person identifier, a report date defaulting to SYSDATE, and an optional indicator (defaulting to 'N') that controls whether alien-related logic is applied. The report date parameter allows the derivation to be performed as of a historical point in time, which is essential for period-accurate regulatory reporting.
• DERIVE_SINGLE_RACE — Returns a numeric code representing a single consolidated race value for a person. Because Oracle EBS permits collection of multiple race selections (the multi-racial data model), this function resolves those selections into one value suitable for reporting formats that accept only a single race designation. This is the function most directly associated with the search term derive_single_race.

Neither function exposes an OUT parameter list in the documented header; both return a NUMBER. The ETRM metadata does not document any exceptions raised, so callers should not assume a documented error contract.

Tables Accessed

The only table documented as referenced (via an APPS synonym) is PER_PEOPLE_EXTRA_INFO. This is the EBS table that stores additional person-level information in a flexible key/value structure, and it is the logical source both for multi-race selections and for citizenship, alien status, and ethnic origin attributes. DERIVE_SINGLE_RACE reads the race-related rows from this table and reduces them to one value, while DERIVE_ALIEN_ETHNIC_ORIGIN reads the corresponding alien and ethnic origin rows.

Usage Notes

Because this package is classified as OTHER, it is not part of the supported public API surface and should be treated as an internal utility. It is referenced by twelve other packages within the EBS schema, and is therefore most commonly invoked indirectly — for example from US HR regulatory reports, person-details or EEO data entry forms, and concurrent programs that extract affirmative action and EEO-1 data. Direct invocation from custom code is possible given the APPS owner, but callers should pass a valid PER_PEOPLE_EXTRA_INFO person identifier, supply an appropriate report date where historical accuracy matters, and be prepared for the function to return a derived or default value when the underlying extra-information rows are incomplete. No change should be assumed to be upward compatible across 12.1.1 and 12.2.2 without verification, since internal utilities are not governed by the same compatibility guarantees as published APIs.