Search Results set_viewby_filter




Overview
Explore in AI Chat

Overview

APPS.HRI_OLTP_PMV_UTIL_PKG is a utility package within the Oracle EBS Human Resources Intelligence (HRMSi) subsystem, specifically the OLTP Performance Management Viewer (PMV) layer. It consolidates the shared PL/SQL helper logic required to construct, filter, and secure dynamic SQL statements that underpin PMV dashboards and analytics. Rather than duplicating clause-building logic across the many PMV report packages, this package centralizes reusable fragments such as manager-level security predicates, order-by normalization, view-by filtering, bind substitution, and change percentage calculations. The header comment indicates a relatively stable utility that has been in place since at least 2005 (hrioputl.pkh 120.1), and it is referenced by 32 other packages, confirming its role as a foundational cross-cutting dependency for HRMSi reporting rather than a business-facing API in its own right.

Key Procedures and Functions

  • GET_SECURITY_CLAUDE (GET_SECURITY_CLAUSE) — Returns the common AK_QUERY PL/SQL clause used to enforce manager-level (MGR) or organization-level (ORG) security. The security type is supplied by the caller, allowing a single function to serve both supervisor hierarchy and organization hierarchy restrictions.
  • CALC_ANL_FACTOR — Returns the annualization factor for a given period type. This supports period-normalized comparisons (for example, converting weekly or monthly values to an annualized basis) in PMV metrics.
  • SET_DEFAULT_ORDER_BY — Accepts an order-by clause and returns a normalized version, applying a default ordering when no explicit clause is supplied. This ensures deterministic result ordering across PMV queries.
  • SET_VIEWBY_FILTER — Builds the filter fragment for the view-by dimension, taking a parameter record, a bind table, and a view-by alias. It is described as applying a filter for “small” view-bys, where the attribute granularity warrants a tighter predicate.
  • SUBSTITUTE_BIND_VALUES — A procedure that replaces PMV bind placeholders with literal SQL values, using a bind table and a format specification. The SQL parameter is IN OUT NOCOPY, reflecting in-place textual substitution of the statement.
  • CHK_EMP_DIR_LNK — Checks the profile option governing the enable/disable state of the link to the HR Employee Directory, returning a numeric indicator used to conditionally render or suppress that navigation element.
  • GET_CHANGE_PERCENT_SQL — Returns the SQL fragment used to compute the percentage change between a previous column and a current column, supporting period-over-period variance metrics.

Tables Accessed

According to the documented metadata, the package references two objects through APPS synonyms: PER_EMPDIR_PEOPLE and PLITBLM. PER_EMPDIR_PEOPLE supports the employee directory link validation performed by CHK_EMP_DIR_LNK, providing the people-level context against which the directory link profile option is evaluated. PLITBLM is a standard Oracle HRMS/PLSQL utility object, typically associated with PL/SQL internal table list management and bulk processing support. Because this is a utility package, table access is incidental to its clause- and fragment-generation responsibilities rather than representing primary business data manipulation; it does not, on the documented evidence, serve as the source of truth for any transactional entity.

Usage Notes

HRI_OLTP_PMV_UTIL_PKG is invoked indirectly rather than directly by end users. Its callers are the 32 PMV reporting packages that assemble dynamic SQL for HRMSi dashboards and performance management viewers. Typical invocation patterns include: PMV report packages calling GET_SECURITY_CLAUSE to append the correct MGR or ORG predicate to a generated query; those same packages calling SET_VIEWBY_FILTER and SET_DEFAULT_ORDER_BY to finalize the WHERE and ORDER BY portions of the statement; and SUBSTITUTE_BIND_VALUES being called immediately before execution to resolve PMV bind tokens into literal values. CALC_ANL_FACTOR and GET_CHANGE_PERCENT_SQL are used where annualized and period-over-period metrics are required. CHK_EMP_DIR_LNK is consulted when rendering employee directory hyperlinks subject to profile-option control. Custom code extending PMV functionality can call these utilities to maintain consistency with delivered security and filtering behavior, but developers should be aware that the package is classified as OTHER and is not a supported public API, so direct dependencies carry upgrade risk across EBS 12.1.1 and 12.2.2.