Search Results edw_hr_service_pkg




Overview
Explore in AI Chat

Overview

EDW_HR_SERVICE_PKG is an Oracle E-Business Suite PL/SQL package owned by the APPS schema and classified under the ETRM (E-Business Technical Reference Manual) as an "OTHER" API. Its name follows the naming convention used by the Oracle HRMS Intelligence / Enterprise Data Warehouse (EDW) family of objects, which supply derived human-resources attributes to downstream reporting and analytics layers. In the 12.1.1 and 12.2.2 releases the package is delivered as a small, focused utility whose single documented responsibility is the translation of a numeric length-of-service value into the corresponding service-band identifier.

Service bands are the configurable ranges (for example, 0–1 year, 2–5 years, 6–10 years) that Oracle HRMS uses to group employees by tenure for compensation review, benefits eligibility, grade progression, and workforce analytics. Rather than repeating the banding logic wherever tenure must be categorised, EDW_HR_SERVICE_PKG centralises it so that every caller resolves a given number of service days to the same band code.

Key Procedures and Functions

The ETRM record documents exactly one program unit inside the package interface:

  • SERVICE_BAND_FK — a function that accepts an employee's length of service expressed in days and returns the foreign-key value (band identifier) of the matching service band. The name signals its purpose: "FK" indicates that the returned value is the primary-key/foreign-key reference into the service-band definition, not a descriptive label. The function is deterministic in nature — the same number of service days always yields the same band for a given set of band definitions. No parameter list beyond the input service-days value is documented, and no overloads are declared in the package specification.

The package specification is deliberately minimal. Because only this one function is exposed, the package acts as a single-purpose lookup helper rather than a general HR service toolkit. Any band-maintenance or recalculation logic resides elsewhere in the HRMS schema.

Tables Accessed

The only documented table reference is HRI_SERVICE_BANDS, resolved through an APPS synonym. This table stores the band definitions — the lower and upper bounds of each tenure range together with the band identifier returned by SERVICE_BAND_FK. The function reads this table to determine which band encompasses the supplied service-days value. In the documented API classification the table is shown as referenced (read) rather than written; the package itself does not maintain band setup data, so HRI_SERVICE_BANDS is populated and maintained through HRMS configuration or the corresponding setup forms. Callers that subsequently join the returned foreign key back to HRI_SERVICE_BANDS obtain the descriptive band text or range boundaries.

Usage Notes

ETRM records indicate that EDW_HR_SERVICE_PKG is referenced by eight other packages, confirming that it is an internal dependency of the HRMS/EDW code base rather than a directly invoked end-user component. Typical invocation patterns include:

  • HRMS Intelligence extract and transformation packages that must bucket employees by tenure when populating EDW tables.
  • Concurrent programs and report generators that derive a service-band code for each assignment during data preparation.
  • Custom PL/SQL that needs the same banding result and calls the packaged function to avoid duplicating the range logic.

Because the interface exposes only SERVICE_BAND_FK, customisations should call the packaged function rather than querying HRI_SERVICE_BANDS directly; doing so insulates custom code from changes to band definitions and from any internal range-handling rules. As with all APPS-owned APIs classified as "OTHER", the package is supported for reference and execution but is not a formally published public API, so behaviour should be validated against the target release (12.1.1 versus 12.2.2) before being relied upon in a production customisation.