Search Results dtl_status_sql2




Overview
Explore in AI Chat

Overview

APPS.POA_DBI_TEMPLATE_PKG is a PL/SQL package belonging to the Oracle Procurement Analytics (POA) product family, specifically the DBI (Daily Business Intelligence) subsystem. Its role is to generate the dynamic SQL fragments, status calculations, and trend expressions that back the Procurement DBI dashboards and its supporting materialized views. The package header carries the source control stamp $Header: poadbitmpls.pls 120.2 2005/09/08 15:46:54 nnewadka noship $, indicating it is an internal, shipped (but not customer-modifiable) component whose version lineage predates the EBS 12.1.1 release and remains unchanged through 12.2.2. Because DBI reporting is time-period oriented, the package declares a set of period and as-of date constants that bind to BIS substitution variables at runtime — current and previous effective start/end dates, current and previous as-of dates, and the prior-previous date used for trend comparisons. A set of bitmap constants governs which reporting windows (extended, extended-to-date, year-to-date, rolling, and balance) are included in a generated in-list, allowing a single code path to emit different SQL shapes for different dashboard queries. The package is referenced by 90 other packages, confirming that it functions as a shared SQL-generation utility rather than a standalone report driver.

Key Procedures and Functions

The package exposes nine documented program units:

  • STATUS_SQL — builds the core status query string. It accepts a fact name, where clause, join tables, windowing flag, column name, group-id mode, parenthesis count, filter, generated view-by flag, and in-join tables, returning VARCHAR2.
  • UNION_ALL_STATUS_SQL — composes the UNION ALL variant of status SQL across a materialized view definition and its joins.
  • GET_STATUS_COL_CALC — returns the computed status column expression used within those queries.
  • GET_TREND_COL_CLAUSES — produces the trend-related column clauses for period-over-period comparisons.
  • TREND_SQL — assembles the trend query string analogous to STATUS_SQL.
  • UNION_ALL_TREND_SQL — the UNION ALL counterpart for trend queries.
  • DTL_STATUS_SQL and DTL_STATUS_SQL2 — build detail-level status queries, providing the drill-down SQL behind summary dashboard figures.
  • GET_VIEWBY_RANK_CLAUSE — the function matching the user's search term. It generates the ranking and view-by clause inserted into generated DBI SQL, ordering or ranking result rows by the selected dimension (view-by) attribute.

Tables Accessed

The documented table references are DUAL and PLITBLM (both accessed through APPS synonyms). DUAL is used for trivial single-row evaluations, such as validating substitution-variable expressions and returning constant or computed scalar values. PLITBLM is the internal PL/SQL table/column metadata repository that the POA DBI utility layer (POA_DBI_UTIL_PKG) consults to resolve column names and materialized-view definitions; POA_DBI_TEMPLATE_PKG depends on it indirectly through the POA_DBI_UTIL_PKG record types (poa_dbi_join_tbl, poa_dbi_col_tbl, poa_dbi_in_join_tbl, poa_dbi_mv_tbl) that appear in its signatures. Actual dashboard data is read from the generated materialized views rather than directly by this package.

Usage Notes

This package is not invoked directly by end users or via standard concurrent program submission. It is called at build/runtime by the Procurement DBI collection and refresh programs and by the dashboard pages that assemble their queries dynamically. Customizations should not modify the shipped package body; the supported extension path is to create site-specific wrappers or additional views that call the public functions. Because the SQL it generates depends on BIS substitution variables, any invocation outside the DBI framework must supply or simulate those values, or the resulting SQL will not execute. In 12.1.1 and 12.2.2 the package behaves identically, and the "noship" header annotation means it is deliberately excluded from customer-facing patch lists despite being present in the APPLSYS schema.