Search Results hri_oltp_pmv_util_pkg




Overview
Explore in AI Chat

Overview

APPS.HRI_OLTP_PMV_UTIL_PKG is a utility package body that supports the Oracle Human Resources Intelligence (HRI) Online Transaction Processing (OLTP) reporting and analytics infrastructure. It belongs to the broader family of HRI_OLTP_PMV (Page Materialized View) utility packages used by Oracle HRMS and related self-service applications to construct, secure, and parameterize analytical SQL queries. The package operates within the APPS schema and holds a VALID status in both Oracle EBS 12.1.1 and 12.2.2. As a supporting utility layer, it abstracts common operations such as applying row-level security predicates, generating dynamic ORDER BY clauses, and substituting runtime bind values into analytical SQL statements. ETRM classifies the package as an "OTHER" API and confirms it is referenced by 32 other database objects, though it is not itself referenced externally beyond that dependency set, indicating a foundational role rather than a public entry point.

Key Procedures and Functions

The package body exposes seven documented programs, each providing a focused utility for the HRI/PMV analytics layer:

  • GET_SECURITY_CLAUSE — Builds a security predicate that restricts query results according to the current user's data access privileges, enabling row-level security in HR analytical queries.
  • CALC_ANL_FACTOR — Computes an analytical factor used in normalization or weighting when aggregating HR metrics for reporting.
  • SET_DEFAULT_ORDER_BY — Establishes the default ORDER BY clause applied when no explicit sort order is supplied by the calling report.
  • SET_VIEWBY_FILTER — Applies a "view by" filter, controlling the dimension or grouping used to organize displayed analytical results.
  • SUBSTITUTE_BIND_VALUES — Replaces bind variable placeholders within dynamically assembled SQL statements, a core requirement for generating parameterized analytical queries safely.
  • CHK_EMP_DIR_LNK — Checks the Employee Directory link, validating whether the employee directory linkage is available or should be rendered in HR self-service output.
  • GET_CHANGE_PERCENT_SQL — Generates SQL that calculates percentage change between analytical values, supporting period-over-period and trend reporting.

Tables Accessed

The package references two tables through APPS synonyms:

  • PER_EMPDIR_PEOPLE — An HRMS employee directory table supplying people/personnel data used primarily by CHK_EMP_DIR_LNK and related directory-linkage logic.
  • PLITBLM — The standard Oracle PL/SQL integer-indexed table type (typically definition or coding table references). Its presence indicates collection-based processing within the package.

In addition to these tables, the package depends on FND_PROFILE (to resolve profile option values, commonly for security and localization settings), the standalone HRI_OLTP_PMV_UTIL_PKG package specification, the HRI_OLTP_PMV_UTIL_PARAM parameter object, and the SYS.STANDARD package.

Usage Notes

HRI_OLTP_PMV_UTIL_PKG is typically invoked indirectly. It is called by HR analytical reports, HRI materialized-view generators, and the 32 dependent packages that depend on its utilities. Standard invocation paths include Oracle HRMS self-service pages, the Oracle HRi reporting suite, and concurrent programs that render analytical dashboards and employee directory content. Because it relies on FND_PROFILE and the HRI_OLTP_PMV utility specification, it should be invoked within an APPS-authenticated session so that profile values and security context resolve correctly. Custom code should avoid direct modification of this package body; instead, extensions should wrap or complement it. Any changes must preserve the GET_SECURITY_CLAUSE and SUBSTITUTE_BIND_VALUES behavior, as deviations will affect the correctness and security of every dependent analytical report in the HR Intelligence stack.