Search Results get_sql

Overview

The APPS.HRI_OLTP_PMV_QUERY_WRKFC package body is a lightweight wrapper within the Oracle E-Business Suite Human Resources Intelligence (HRI) family of PL/SQL objects. Its purpose is to expose a workforce-related fact SQL generator to the Oracle Daily Business Intelligence (DBI) / PMV (Performance Management Viewer) query engine. Rather than implementing query logic itself, the package delegates all substantive work to the underlying shared library HRI_BPL_FACT_SUP_WRKFC_SQL, which builds SQL statements for the workforce fact records used in workforce analytics and PMV dashboards.

In the Oracle EBS 12.1.1 and 12.2.2 releases, HRI packages form the foundation of the HR Analytics dashboards, delivering headcount, assignment, and workforce composition metrics to the Performance Management Viewer. This package acts as one of several thin façade objects that adapt the generic PMV query contract to the workforce fact collection.

Key Procedures and Functions

• GET_SQL — The single documented function in this package. It returns a VARCHAR2 string containing the SQL text to be executed for the workforce fact query. It accepts parameter, bind, and workforce-specific parameter records along with the calling module identifier, and forwards them unchanged to HRI_BPL_FACT_SUP_WRKFC_SQL.GET_SQL. All SQL construction and bind manipulation are handled by the delegated routine; this wrapper only provides a stable entry point through the PMV query framework.

No other procedures or functions are documented for this package body, and no local business logic is implemented beyond the pass-through call.

Tables Accessed

No tables are documented as directly referenced through APPS synonyms within this package body. Because all SQL construction is delegated to HRI_BPL_FACT_SUP_WRKFC_SQL, the underlying fact and dimension tables (typically the workforce fact tables in the HRI schema and their supporting HRMS base tables) are accessed indirectly when the returned SQL statement is executed by the calling framework. From the perspective of HRI_OLTP_PMV_QUERY_WRKFC, the package performs no DML and no direct SELECT against repository tables.

Usage Notes

The package is not typically invoked directly by end users or forms. It is called by the PMV/DBI query engine, which resolves a fact collection name to its corresponding query wrapper and invokes GET_SQL to obtain the SQL statement to run. The ETRM metadata records that this package is referenced by five other packages, confirming its role as a shared entry point within the HR analytics stack.

Because the operation is a pure delegation, changes to the actual workforce fact SQL are made in HRI_BPL_FACT_SUP_WRKFC_SQL rather than here. Customizations or extensions that need to alter workforce fact queries should target the delegate package, while HRI_OLTP_PMV_QUERY_WRKFC remains a stable interface. Typical invocation scenarios include loading of the Performance Management Viewer dashboards, concurrent processing of workforce fact collections, and any custom code that programmatically requests workforce fact SQL via the standard PMV query contract.