Search Results abs_fact_param_type

Overview

HRI_BPL_FACT_ABS_SQL is a PL/SQL package owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Human Resources Intelligence (HRI) business intelligence layer. The package forms part of the Oracle HRMS embedded analytics (Daily Business Intelligence / HR Intelligence) infrastructure, and its responsibility is to generate the SQL text that materializes the "fact" data used by absence-related key performance indicators. Rather than storing fact rows directly, the package builds a dynamic SQL statement that populates a fact table from operational absence data, then returns that table name to the caller. In this sense it acts as a fact-table management utility: it decides whether a materialized fact structure must be (re)built or whether an existing aggregated snapshot can be reused. The package metadata identifies it as a standalone (non-API) package with an "OTHER" API classification, indicating that it is intended for internal consumption by the HR Intelligence framework rather than as a supported public interface for customer extensions.

Key Procedures and Functions

The package specification declares a record type, ABS_FACT_PARAM_TYPE, which carries the flags and bucket dimensions that control how absence facts are aggregated — for example whether absence duration in days, duration in hours, in-period absence, notification-period absence, and compensation are to be included, along with the KPI mode and bucket dimension. This record is the principal mechanism by which the requesting report or KPI passes its aggregation requirements to the SQL generator.

• SET_FACT_TABLE — The central procedure. It accepts the standard HRI parameter record (HRI_PMV_PARAM_REC_TYPE), the individual absence-aggregation flags and bucket dimension, a parameter count and single-parameter indicator used for bind-variable handling, and an IN OUT NOCOPY boolean that signals whether a snapshot is to be used. It returns, via an OUT NOCOPY VARCHAR2 parameter, the name of the fact table (or SQL source) that the caller should query. Its purpose is to determine and prepare the appropriate fact source for the requested absence KPI.
• GET_SQL (two overloads) — Functions that return the generated SQL text as a VARCHAR2. Both take the HRI parameter record, a bind-variable table (HRI_PMV_BIND_TAB_TYPE), and the ABS_FACT_PARAM_TYPE record describing the absence aggregation options. The second overload adds a calling-module argument, allowing the generated SQL to be tailored to the context that invoked it.

Tables Accessed

The documented table reference for this package is PLITBLM, referenced through an APPS synonym. PLITBLM is a diagnostic/logging table used by the PL/SQL instrumentation and tracing facilities, so its presence reflects internal logging or instrumentation activity rather than the business data being aggregated. The actual absence fact data is drawn from HRMS operational tables at runtime by the dynamically generated SQL; the package metadata does not enumerate those base tables because the access is constructed at execution time rather than declared statically.

Usage Notes

HRI_BPL_FACT_ABS_SQL is not intended to be called directly by end users. It is invoked programmatically by HR Intelligence reporting components — the metadata records that the package is referenced by four other packages — which supply the HRI parameter and bind records on behalf of a KPI or dashboard request. Typical invocation occurs when an absence KPI is refreshed or displayed: the calling component prepares the absence parameter flags, calls SET_FACT_TABLE to obtain the fact source, and receives back either a snapshot table name or a generated SQL statement for direct execution. The p_use_snapshot IN OUT flag allows the caller and the package to negotiate whether a pre-aggregated snapshot satisfies the request or whether a fresh query must be constructed. Consumers should treat the package as an internal HRMS implementation detail; because it generates SQL dynamically and its signature is not classified as a public API, custom code should not depend on it, and any direct use risks breakage across patches and upgrades between releases such as 12.1.1 and 12.2.2.