Search Results hri_oltp_pmv_query_wrkfc

Overview

The APPS.HRI_OLTP_PMV_QUERY_WRKFC package is a component of the Oracle Human Resources Intelligence (HRMS Intelligence / HRMSi) subsystem in Oracle E-Business Suite. It belongs to the family of OLTP-based Page Materialized View (PMV) query packages that construct dynamic SQL statements for the HRMSi Workforce analytics dashboards ("Workforce" or PMV-based management reports). In EBS 12.1.1 and 12.2.2, these packages operate beneath the Oracle Daily Business Intelligence / HRMSi reporting layer, translating report parameter selections and bind values into executable SQL text that is then executed by the reporting engine.

The package is classified in the ETRM repository with an API classification of OTHER, indicating it is not a public, documented, callable business API but rather an internal implementation utility. Its owner is the APPS schema, and the header comment ($Header: hriopqwk.pkh 120.0 2005/05/29 ...) confirms it has been stable since the early 11i/12.x lineage, with no significant revision since 2005. The package exposes a single public function, GET_SQL, whose sole purpose is to return a dynamically built SQL string for a Workforce fact query.

Key Procedures and Functions

• GET_SQL — The only documented program unit in the package specification. It returns a VARCHAR2 value containing the SQL text that the HRMSi reporting layer will execute. Based on the ETRM source listing, its inputs comprise: a generic parameter record (hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE), a collection of bind variables (hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE), a Workforce-fact-specific parameter structure (hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type), and a calling-module identifier (VARCHAR2). These parameters allow the function to be tailored to the specific report context, the active bind values, and the Workforce fact configuration being queried. The body of the package (not shown in the specification excerpt) contains the logic that assembles the SELECT statement, applying the relevant filters, joins, and fact-table references.

No other procedures or functions are documented for this package; the specification is deliberately minimal and exposes only the SQL-generation entry point.

Tables Accessed

The ETRM metadata does not enumerate any base tables referenced through APPS synonyms. Consistent with its role as a SQL generator, the package does not itself read or write data at parse time; instead, GET_SQL returns a text statement that, when executed, will access the Workforce fact and dimension tables defined within the HRMSi schema. The actual tables are those of the Oracle HRMS Intelligence workforce star schema (fact and dimension objects), referenced indirectly through the hri_bpl_fact_sup_wrkfc_sql type and the PMV utility layer. Because the package builds rather than executes SQL, it holds no direct DML privileges and does not modify application data.

Usage Notes

HRI_OLTP_PMV_QUERY_WRKFC is invoked internally by the HRMSi reporting infrastructure rather than by end users or forms. The ETRM metadata records that it is referenced by 5 other packages, all of which are part of the same OLTP PMV query framework. Typical invocation paths include:

• HRMSi Workforce dashboard pages and PMV-based OBI/BI Publisher reports that require dynamic, parameter-driven SQL.
• Sibling PMV query packages that delegate Workforce fact SQL construction to this package.
• Any custom report or concurrent program within the HRMSi framework that calls GET_SQL and executes the returned statement using the supplied bind table.

Because the function is not a supported public API and there is no documented signature stability guarantee beyond the internal framework contract, customizations should avoid direct calls to GET_SQL where possible. When customization is unavoidable, the caller must supply parameter and bind records conforming to the HRI_PMV_* types and the wrkfc_fact_param_type structure, and must manage the returned SQL string as a dynamic statement, binding the collected values before execution. In EBS 12.1.1 and 12.2.2 the package resides in the APPS schema and is compiled with the standard HRMSi synonym configuration; no editioning or online patching concerns beyond those common to all APPS packages apply.