Search Results load_all_positions

Overview

HRI_OPL_POSH is an Oracle EBS Applications (APPS) package that supports the loading and refresh of position hierarchy data used by Oracle HR Intelligence and related HR reporting/analytics functionality. The suffix "POSH" refers to position structures — the hierarchical relationships defined between positions in Oracle HRMS (PER_POSITION_STRUCTURES and its associated version and element tables). The package's role is to transfer or materialize position hierarchy information into the HR Intelligence collection schema (HRI_CS_POSH), thereby enabling downstream reporting, analytics, and workforce intelligence queries to operate against a consolidated, denormalized representation of the position structure rather than querying the transactional HRMS tables directly.

In the Oracle EBS 12.1.1 and 12.2.2 releases, this package is classified as an "OTHER" API, meaning it is not a public, supported business API in the same sense as an open interface or a published public API. It is an internal utility that supports the HR Intelligence data collection process. The package is owned by the APPS schema. Because position hierarchies can be large, the load logic is designed to process data incrementally, which is why the primary entry point accepts a chunk size parameter that controls batch granularity.

Key Procedures and Functions

The ETRM metadata documents two overloaded procedures, both named LOAD_ALL_POSITIONS. The package specification declares them as:

• LOAD_ALL_POSITIONS (p_chunk_size IN NUMBER) — A programmatic overload intended to be called directly from PL/SQL. It accepts a chunk size that determines how many position structure records are processed per iteration or batch, allowing the load to be tuned to control memory consumption and transaction volume.
• LOAD_ALL_POSITIONS (errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, p_chunk_size IN NUMBER) — A concurrent-program-style overload. The errbuf and retcode parameters follow the standard Oracle EBS concurrent program signature, allowing the procedure to be registered and executed as a concurrent program from the Applications concurrent manager, with the chunk size exposed as a parameter.

Both procedures perform the same essential function: reading source position hierarchy data and populating or refreshing the HR Intelligence position structure table. The duplication of names with different signatures is a common EBS pattern that supports both direct invocation and concurrent-manager invocation from a single package.

Tables Accessed

The documented tables referenced through APPS synonyms are:

• HRI_CS_POSH — The HR Intelligence collection/analytics table for position structures. This is the primary write target; the load procedures populate or refresh this table.
• PER_POSITION_STRUCTURES — Defines the position hierarchies (the structural definition of each position structure).
• PER_POS_STRUCTURE_ELEMENTS — Holds the individual positions that participate in each structure and their parent/child relationships.
• PER_POS_STRUCTURE_VERSIONS — Stores versioning information for position structures, allowing the load to respect dated/versioned hierarchy definitions.

These three PER_ tables are the transactional HRMS source of truth; the package reads them to derive the flattened hierarchy that is written into HRI_CS_POSH for analytical consumption.

Usage Notes

Because this is an internal utility rather than a public API, it is typically invoked in the following ways:

• Concurrent program execution — The errbuf/retcode overload is the form used when the procedure is registered as a concurrent program. Administrators or the HR Intelligence collection process submit it, supplying a chunk size appropriate to the data volume.
• Direct PL/SQL calls — The p_chunk_size-only overload may be called from other PL/SQL packages or custom scripts. The metadata indicates the package is referenced by one other package, consistent with its role as a dependency in a larger collection routine.
• Chunk sizing — The p_chunk_size parameter should be set based on position hierarchy volume and available rollback/undo capacity; smaller chunks reduce resource spikes at the cost of more iterations.

Developers should treat the package as unsupported for customization. Any changes to underlying PER_ position structure data should be followed by re-execution of the appropriate collection process to keep HRI_CS_POSH synchronized. Users searching for "load_all_positions" are typically looking to run or schedule this load; the correct approach is to invoke the concurrent-program overload rather than calling the procedure ad hoc, so that error handling and logging are captured through the standard concurrent manager framework.