Search Results get_no_viewby_query




Overview
Explore in AI Chat

Overview

HRI_OLTP_PMV_DYNSQLGEN is an Oracle E-Business Suite PL/SQL package owned by the APPS schema that generates dynamic SQL statements for the Oracle HRMS (Human Resources Management System) online transaction processing (OLTP) Performance Management Viewer (PMV) framework. The package name reflects its role: "HRI" denotes the HR Intelligence/HRMS schema family, "OLTP" indicates the transactional inquiry layer, "PMV" refers to the Performance Management Viewer, and "DYNSQLGEN" identifies it as a dynamic SQL generator. The package is classified as OTHER in the ETRM API registry, indicating it is an internal or supporting utility rather than a public concurrent-program or business API.

In functional terms, this package constructs the runtime SELECT statements that populate the configurable regions of PMV-based HRMS dashboards. Administrators and end users customize these regions through the Personalization framework (also known as OA Framework personalization or the AK region infrastructure), choosing which view-by dimensions (for example, organization, job, position, or supervisor) and drill targets should appear. The package translates those personalization selections into syntactically valid SQL that the PMV rendering engine executes against OLTP tables. It is declared with AUTHID CURRENT_USER, meaning invoked SQL executes with the privileges of the calling user rather than the package owner, which allows data security policies and row-level security in the calling session to apply correctly.

Key Procedures and Functions

Three public functions are documented in the package specification. Each returns a VARCHAR2 containing a dynamically assembled SQL query string.

  • GET_QUERY — Returns the primary dynamic SQL statement for a PMV region. It accepts the page parameter table (BIS_PMV_PAGE_PARAMETER_TBL) and an AK region code, and produces the query that drives the standard region display, including any view-by dimension selected by the user or configured through personalization.
  • GET_NO_VIEWBY_QUERY — Returns a variant of the dynamic query that omits the view-by dimension. This is used when the region is rendered in a mode where no grouping or view-by breakdown applies, for example when displaying a flat summary list without dimension pivoting.
  • GET_DRILL_INTO_QUERY — Returns the SQL used when a user drills into a PMV region cell or row to obtain the detail records underlying an aggregate figure. Drill-into behavior is central to the PMV user experience, allowing navigation from summary metrics to the transactional or assignment-level rows that produced them.

All three functions share the same two input parameters and are stateless with respect to package state; each invocation produces a query string based on the page parameters and region definition passed in.

Tables Accessed

The package reads metadata from the following tables, accessed through APPS synonyms:

  • AK_REGIONS — Stores the definition of the AK (Application Kit) region being rendered. The package queries it to resolve region attributes such as the underlying query block, region type, and other configuration needed to synthesize the appropriate SQL.
  • AK_REGION_ITEMS — Stores the individual items (columns, attributes, and view-by candidates) belonging to each AK region. The package uses these definitions to determine which columns to select and how to construct grouping, ordering, and drill criteria.
  • PLITBLM — An Oracle Tools/Forms support table historically used for storing PL/SQL library and menu source. In this context it is referenced as part of the HRMS OLTP PL/SQL repository, supporting the resolution of metadata used when generating the dynamic statement.

The package itself performs no inserts, updates, or deletes against these tables; they are read-only metadata sources consulted while building query strings.

Usage Notes

HRI_OLTP_PMV_DYNSQLGEN is not intended for direct invocation by end users or routine custom development. It is called internally by the HRMS PMV rendering engine, which passes in the current page parameter table and the AK region code for the region being processed, and then executes the returned SQL. Typical callers are other PL/SQL packages in the HR Intelligence and OLTP stack; the ETRM metadata records that this package is referenced by one other package.

Because the generated SQL depends on runtime personalization and page parameters, behavior varies with user configuration, so troubleshooting rendering issues generally involves inspecting AK region definitions and personalization settings rather than the package body itself. Custom developers extending PMV regions should rely on the supported personalization and region-definition mechanisms rather than modifying this package, since it is an internal component and its interface is not part of the supported public API surface. The AUTHID CURRENT_USER clause further reinforces that any dynamic SQL it produces is subject to the calling user's data security context, a consideration worth remembering when diagnosing unexpected data visibility in customized PMV regions.