Search Results hri_bpl_fact_sup_wrkfc_sql




Overview
Explore in AI Chat

Overview

APPS.HRI_BPL_FACT_SUP_WRKFC_SQL is a valid PL/SQL package body in the Oracle E-Business Suite APPS schema. It belongs to the Oracle Human Resources Intelligence (HRI) family of components, which supply the business intelligence layer used by Oracle HRMS analytics, Oracle Daily Business Intelligence for HRMS, and the Oracle HRMSi reporting stack. Within that architecture, the package occupies the SQL-generation tier of the workforce fact-building pipeline: it is responsible for assembling the query text that materialises a workforce fact table from the surrounding HR OLTP and metadata structures. The "_SQL" suffix in the object name indicates that the package encapsulates statement construction rather than performing bulk data manipulation itself, while the "FACT_SUP_WRKFC" component identifies its subject area as the workforce fact supplied to the HR analytics star schema. The package is classified as OTHER in the ETRM API registry, meaning it is an internal implementation object rather than a published extension API. It is referenced by fifteen other database objects, all of which depend on its SQL-generation behaviour, and it does not itself reference any object outside the APPS and SYS schemas listed in its dependency tree.

Key Procedures and Functions

The ETRM metadata documents three procedures and functions within the package body:

  • SET_FACT_TABLE — Establishes the target fact table context for the package, recording the identity of the workforce fact table that subsequent SQL generation will address. It functions as an initialisation or configuration routine invoked before query text is requested.
  • GET_SQL — Returns the dynamically constructed SQL statement used to populate or query the workforce fact table. This is the principal entry point for calling code, which receives the statement text and executes it against the fact table established by SET_FACT_TABLE.

The third documented program unit is unnamed in the excerpt; the dependency listing indicates the body also draws on HRI_MTDT_DIM_LVL for dimension-level metadata, HRI_OLTP_PMV_UTIL_PARAM and HRI_OLTP_PMV_UTIL_SNPSHT for parameter resolution and snapshot handling, and the SYS.STANDARD package. No parameter lists are published in the ETRM extract, and none should be assumed.

Tables Accessed

The only table documented as referenced through APPS synonyms is PLITBLM, the standard Oracle PL/SQL indexed-table-of-VARCHAR2 collection type used to pass lists of strings between PL/SQL and SQL. Its presence confirms that SET_FACT_TABLE and GET_SQL exchange SQL fragments and bind information as PL/SQL collections rather than as single scalar values. The package additionally consumes the dictionary and metadata views owned by HRI_MTDT_DIM_LVL, HRI_OLTP_PMV_UTIL_PARAM, and HRI_OLTP_PMV_UTIL_SNPSHT, which supply the dimension-level definitions and runtime parameters needed to shape the generated statement. The workforce fact table itself is resolved at runtime through SET_FACT_TABLE and therefore is not fixed in the dependency metadata.

Usage Notes

Because the package is an internal HRI object, it is not intended for direct invocation from Oracle Forms, concurrent programs, or customer-written code. It is called by the fifteen dependent APPS packages that make up the workforce fact-collection and HR analytics refresh flow, typically during the execution of HRMSi or Daily Business Intelligence for HRMS collection programs. In a 12.1.1 or 12.2.2 environment, DBAs and developers encountering this object should treat it as read-only: it is a valid, dictionary-registered artifact whose behaviour is bound to the HRI metadata and OLTP utility packages already present in the instance. Customisations should be applied to the calling collection programs, not to this SQL-generation body, and any investigation of incorrect workforce fact data should begin with the parameter and snapshot utility packages it depends upon.