Search Results get_actual_value_plsql




Overview
Explore in AI Chat

Overview

BIS_PMV_ACTUAL_PVT is a private PL/SQL package owned by the APPS schema in Oracle E-Business Suite. It forms part of the Oracle Business Intelligence System (BIS) / Performance Management Viewer (PMV) infrastructure. Its principal role is to compute and return the actual and comparison values displayed on Performance Management Viewer regions, which are graphical and tabular indicators that present business metrics to end users within HTML-based dashboards and portal pages.

The package is declared with AUTHID CURRENT_USER, meaning its SQL statements execute under the privileges of the calling user rather than the definer. It is classified as a private (PVT) API, so it is not intended as a public customization interface; it is invoked internally by the PMV rendering and engine layers. The package defines several collection and record types—including MEASURE_ATTR_CODES_TYPE, TIME_PARAMETER_REC_TYPE, PARAMETER_REC_TYPE, ACTUAL_VALUE_REC_TYPE, ACTUAL_VALUE_TBL_TYPE, and PARAMETER_TBL_TYPE—that are used to pass time filters, parameter lists, and computed result sets between the engine and the package body. The header comment indicates the source file BISVACLS.pls and a version string that reflects the historical lineage of the BIS code line.

Key Procedures and Functions

  • GET_ACTUAL_VALUE — The primary entry point for retrieving an actual metric value and its comparison-to value for a given PMV region. It accepts a region code, optional function name, user and responsibility identifiers, a time parameter record, a parameter table, an indicator of whether parameter IDs are supplied, and the actual and comparison attribute codes. It returns the actual value, comparison value, return status, message count, and message data through OUT parameters.
  • GET_ACTUAL_VALUE_PLSQL — A variant of the value retrieval routine oriented toward PL/SQL-driven invocation, accepting region, function, user, responsibility, and session context inputs to produce the region's actual values.
  • STORE_PARAMETERS — Persists or caches the region parameter set supplied by the caller so that subsequent value calculations can reference a consistent parameter context.
  • SETUP_BIND_VARIABLES — Prepares and binds the dynamic SQL bind variables required by the package's dynamic query construction. This is the procedure most closely associated with the user's search term "setup_bind_variables"; it underpins the dynamic SQL execution path used by the value retrieval routines.
  • GET_CALCULATED_VALUE — Derives a computed or calculated metric value, as distinct from a directly stored actual value, supporting derived measures defined on a PMV region.
  • SORTBY_BASE_COLUMN_LENGTH — A helper routine that sorts output by the length of a base column, typically used to order or truncate region display content.

Tables Accessed

  • AK_REGIONS — The Oracle Application Framework / AK region definition table, read to resolve region metadata such as region code and associated attributes.
  • AK_REGION_ITEMS — Read to obtain the items and attribute definitions belonging to a region, which determine the measures and view-by dimensions available for value calculation.
  • BIS_NOTIFICATION_ID_S — The sequence used to generate notification identifiers within the BIS notification framework.
  • DBMS_SQL — The Oracle-supplied dynamic SQL package, referenced to parse, bind, and execute the dynamically constructed metric queries.
  • DUAL — Used for single-row evaluations and simple value expressions.
  • PLITBLM — The PL/SQL table-to-integer conversion utility package, used to manipulate the index-by tables defined in this package.

Usage Notes

Because BIS_PMV_ACTUAL_PVT is a private API, it is not documented for direct customer invocation. It is called by the PMV engine and rendering layers when a user opens a Performance Management Viewer page or refreshes a region, and it may also be invoked indirectly by concurrent programs that precompute or cache region values. The three packages that reference it are part of the same BIS/PMV internal stack. Customizations should avoid calling this package directly, since its signature and behavior are subject to change across patch levels. When diagnosing PMV value display issues—particularly those involving dynamic SQL binding—the SETUP_BIND_VARIABLES procedure and the DBMS_SQL path are the most relevant areas to trace.