Search Results dtl_sql




Overview
Explore in AI Chat

Overview

APPS.POA_DBI_UFR_PKG is a PL/SQL package that belongs to the Oracle Procurement Intelligence (POA) product family, specifically the Daily Business Intelligence (DBI) reporting layer. Its name suggests a role in supplying "UFR" (user-defined or updatable financial reporting) query logic that feeds DBI dashboard regions and analytic pages. Rather than storing business data itself, the package functions as a SQL-generation utility: it returns dynamic SQL text and associated query-attribute metadata to the DBI/PMV reporting engine at runtime, allowing the presentation layer to render configurable analytic content without embedding SQL directly in the page definition.

The package header carries the EBS standard header comment $Header: poadbiufrs.pls 120.0 2005/06/01 15:00:07 appldev noship $, indicating a version 120.0 script produced by the applications development team. It is classified in the ETRM repository with an API classification of OTHER, confirming it is an internal reporting utility rather than a public, supported extension API. The owner is APPS, and no other packages are documented as referencing it, which is consistent with a leaf-level reporting helper invoked directly by the DBI framework.

Key Procedures and Functions

The package exposes five public procedures, all of which share the same DBI callback signature: they accept a page-parameter collection and return an outbound custom SQL string plus a table of query attributes. This uniform contract allows the DBI engine to call any of them interchangeably when building a report region.

  • AMT_SQL — Produces the SQL used for the amount-oriented report region, emitting the query text and its attribute definitions.
  • SUM_RPT_SQL — Supplies SQL for the summary report region, aggregating reporting content at the summary level.
  • STATUS_SQL — Returns SQL that drives the status view of the report, presenting status-bucketed results.
  • DTL_SQL — Generates the detail-level SQL for the report. This is the procedure most commonly located when searching the data dictionary or trace output for "dtl_sql", since it supplies the row-level detail query behind a DBI drill-down region.
  • AGE_SQL — Builds the SQL for the ageing analysis of the report, typically used to bucket transactions by elapsed time.

Each procedure's returned x_custom_sql value is the executable query text, while x_custom_output describes the columns and formatting the reporting engine must apply. No parameter lists beyond the shared signature are documented.

Tables Accessed

The ETRM metadata documents two tables referenced through APPS synonyms: DUAL and PLITBLM. DUAL is the standard single-row Oracle utility table, used here for constant evaluation and lightweight query scaffolding during SQL construction. PLITBLM is an EBS internal table associated with PL/SQL interlocking and temporary storage of large text values; within this package it supports the assembly and return of SQL strings that may exceed ordinary VARCHAR2 line limits, consistent with the package's role as a dynamic SQL builder. The package does not appear to read or write transactional purchasing or payables data directly; all substantive data access is performed by the generated SQL when it executes.

Usage Notes

POA_DBI_UFR_PKG is invoked by the Oracle DBI/PMV reporting framework rather than by end users or forms directly. When a DBI dashboard region is rendered, the framework calls the appropriate procedure, passing the current page parameters, and receives back the SQL and attribute set needed to execute the region query. Because the procedures use the BIS_PMV_PAGE_PARAMETER_TBL and BIS_QUERY_ATTRIBUTES_TBL types, they are tied to the PMV page-model infrastructure common across EBS 12.1.1 and 12.2.2.

Given its OTHER classification and the absence of dependent packages, it should be treated as an internal implementation object. Customizations should avoid modifying the package directly, as patching or upgrade activity may overwrite it; extensions are better placed in customer-owned packages or in the supported DBI personalization mechanisms. Developers diagnosing a slow or incorrect DBI detail region can inspect the SQL returned by DTL_SQL to understand the query being issued.