Search Results get_nearest_parent




Overview
Explore in AI Chat

Overview

HRI_EDW_DIM_PERSON is a PL/SQL package body owned by the APPS schema that supports the Oracle E-Business Suite Human Resources Intelligence (HRI) extract, transform, and load (ETL) layer. Its role is to populate and enrich the person dimension used by the HR data warehouse, resolving person records against related operational entities such as buyers, planners, and sales representatives. The package provides lightweight boolean-style attribute resolution helpers that classify whether a given person identifier participates in another functional role within the E-Business Suite. These resolved attributes allow the warehouse to describe a person not only by HR attributes but also by their cross-module associations in Procurement, Inventory, and Order Management. The body is classified as OTHER rather than a public API, indicating it is intended for internal consumption by other EBS components rather than direct external callers. It is referenced by two other packages, confirming its role as a reusable utility layer within the HRI ETL stack.

Key Procedures and Functions

The package exposes four documented functions, each providing a targeted lookup or classification:

  • GET_NEAREST_PARENT — Returns the identifier of the fifteenth-level supervisor above a given person. It resolves this by querying the supervisory hierarchy summary table filtered to supervisory level 14, effectively locating the ancestor positioned below the sixteenth hierarchy level.
  • IS_A_BUYER — Tests whether the supplied person identifier also exists as a buyer. This is the function associated with the search term "is_a_buyer." It returns a single-character indicator, 'Y' or 'N', depending on whether a matching buyer record is found. The function traps all exceptions and defaults to 'N' when no match or an error occurs.
  • IS_A_PLANNER — Tests whether the given person identifier also exists as a planner, returning 'Y' or 'N' using the same defensive pattern.
  • IS_A_SALES_REP — Tests whether the given person identifier exists as a sales representative, again returning a single-character indicator.

Each function is designed to be side-effect free and safe to invoke repeatedly during dimension builds. Parameter lists are not fully documented in the metadata, but IS_A_BUYER and its siblings accept a person identifier, and GET_NEAREST_PARENT accepts a person identifier and returns a parent identifier.

Tables Accessed

The package reads from four underlying tables through APPS synonyms:

  • HRI_SUPV_HRCHY_SUMMARY — Supplies the flattened supervisory hierarchy used by GET_NEAREST_PARENT to resolve ancestor relationships at fixed levels.
  • PO_AGENTS — The Purchasing buyer table, queried by IS_A_BUYER to determine whether the person is registered as a buyer.
  • MTL_PLANNERS — The Inventory planner table, queried by IS_A_PLANNER.
  • RA_SALESREPS_ALL — The Order Management sales representative table, queried by IS_A_SALES_REP.

All access is read-only, consistent with a dimension-population utility.

Usage Notes

In Oracle EBS 12.1.1 and 12.2.2, this package is typically invoked from the HRI dimension build logic, from dependent ETL packages, or from concurrent programs that refresh the person dimension. Because it performs simple, single-row lookups with exception safety, it is well suited to bulk dimension loads. Custom code extending HRI reporting may call these functions to derive role flags for persons; however, since the package is classified as OTHER, direct invocation should be treated as an internal dependency rather than a guaranteed public interface.