Search Results poa_dbi_ufr_pkg




Overview
Explore in AI Chat

Overview

POA_DBI_UFR_PKG is a PL/SQL package body owned by the APPS schema in Oracle E-Business Suite, classified under the ETRM as an "OTHER" type API rather than a formally published public interface. It forms part of the Procurement and Oracle Applications (POA) Business Intelligence product family, specifically the subset designated by the "DBI" (Database Intelligence) and "UFR" prefixes. In Oracle EBS 12.1.1 and 12.2.2, packages bearing these prefixes are the backend implementation for the Procurement dashboards and embedded analytics regions exposed through Oracle Business Intelligence and the Daily Business Intelligence (DBI) framework.

The package is therefore best understood as a reporting and query-generation utility. Rather than performing transactional updates, it constructs and returns SQL fragments and result sets consumed by the DBI/PMV page rendering engine to populate procurement-related performance summaries. Its dependency footprint confirms this role: it depends on BIS_BUCKET_PUB, BIS_PMV_PAGE_PARAMETER_REC/TBL, and BIS_QUERY_ATTRIBUTES_TBL, all of which are components of the Oracle Business Intelligence System (BIS) page and query framework.

Key Procedures and Functions

The ETRM documents five procedures or functions:

  • AMT_SQL — Generates the SQL statement or clause used to retrieve monetary amount metrics for the dashboard. It is the core method for producing the numeric measure columns displayed in the procurement report.
  • SUM_RPT_SQL — Builds the SQL for the summary-level report, producing the aggregated view presented at the top of the dashboard.
  • STATUS_SQL — Returns the SQL that drives status-based groupings or counts, such as the distribution of purchasing documents across approval, processing, or completion states.
  • DTL_SQL — Constructs the detail-level query, supplying the drill-down rows that underlie the summary figures.
  • AGE_SQL — Produces the SQL used for aging analysis, which classifies records by elapsed time (for example, days outstanding) to support cycle-time and exception reporting.

Each routine is named after the SQL artifact it emits rather than a discrete business transaction, which is consistent with the template-driven DBI design. No parameter signatures are published in the ETRM metadata.

Tables Accessed

The documented table references are DUAL and PLITBLM, both resolved through APPS synonyms. DUAL is the standard single-row Oracle dictionary table, used for expression evaluation and constant selection within generated SQL. PLITBLM is the internal PL/SQL table used by the BIS_PMV_PAGE_PARAMETER_TBL collection type to pass multi-value page parameters between the dashboard framework and this package. In practice, the dynamic queries assembled by the _SQL routines go on to select from procurement transaction tables, but those targets are supplied at runtime through the BIS query attributes and template mechanism rather than hard-coded here.

Usage Notes

POA_DBI_UFR_PKG is invoked indirectly. It is called by the DBI/PMV page rendering pipeline and by the shared POA_DBI_TEMPLATE_PKG, POA_DBI_UTIL_PKG, and POA_DBI_SUTIL_PKG routines, which assemble dashboard regions and invoke POA_DBI_UFR_PKG to obtain their SQL. It also references FND_GLOBAL and FND_PROFILE to obtain the active user and profile-option context required for row-level security and reporting parameters.

The ETRM records that POA_DBI_UFR_PKG is not referenced by any other database object, confirming it sits at the top of the dependency chain. It is internal implementation code for the Procurement dashboard, so customizations should not call it directly; developers integrating the same analytics should instead use the DBI/PMV public APIs. Any modification carries upgrade risk and should be handled through the supported personalization layer.