Search Results get_trend_sel_clause

Overview

APPS.POA_DBI_SPND_PKG is a PL/SQL package body belonging to the Oracle EBS Procurement/Spend analysis family of DBI (Daily Business Intelligence) reporting components. Its responsibility is to construct the dynamic SQL statements that back the Spend Management dashboard pages. The package receives a set of page parameters from the DBI page framework and returns a rendered query together with output attribute definitions, allowing the reporting engine to execute the statement and format the resulting dashboard region. The header comment ($Header: poadbispndb.pls 120.2) indicates this is a non-shipped, internal implementation unit rather than a published API, consistent with its "OTHER" API classification. In EBS 12.1.1 and 12.2.2 the package remains an internal helper within the POA (Procurement Analyzer/spend) schema footprint and is not intended for direct customer invocation.

Key Procedures and Functions

The ETRM record documents a single public procedure, TREND_SQL. This procedure accepts the DBI page parameter table and returns a dynamically built SQL string along with a table of query attributes used by the reporting framework.

• TREND_SQL — The core driver for spend trend visualizations. It evaluates the incoming page parameters (view-by dimension, as-of date, prior as-of date, comparison type, XTD indicator, and nested pattern) and assembles the appropriate query against the spend materialized views. Results are returned through the x_custom_sql and x_custom_output out parameters so the DBI engine can execute and render the trend region.
• get_trend_sel_clause — Declared at line 9 as a forward declaration returning VARCHAR2 and accepting a view-by value. Although not separately catalogued in the ETRM procedure list, it is the selection-clause builder that TREND_SQL relies upon: given a view-by choice, it produces the corresponding SELECT-list fragment. This is the object most commonly identified when users search on "get_trend_sel_clause".

Tables Accessed

The documented table reference is PLITBLM, an APPS synonym used by the package during query construction. In addition, the body declares collection types from POA_DBI_UTIL_PKG — POA_DBI_COL_TBL, POA_DBI_JOIN_TBL, POA_DBI_IN_JOIN_TBL, POA_DBI_JOIN_REC, and POA_DBI_MV_TBL — which hold column, join, and materialized-view metadata assembled by TREND_SQL. Multiple parallel column, where-clause, materialized-view, and in-join collections (l_col_tbl1 through l_col_tbl4, etc.) are populated to support comparative and nested query patterns. The package therefore reads predominantly from DBI metadata structures and the spend materialized views referenced by l_mv1 through l_mv4, rather than performing direct DML against base transaction tables.

Usage Notes

POA_DBI_SPND_PKG is invoked indirectly. DBI dashboard regions configured in the EBS personalization framework call TREND_SQL through the standard BIS_PMV page-parameter contract; the framework supplies the parameter table and consumes the generated SQL and attribute output. Because the package is flagged noship and classified as OTHER, it should not be called directly from custom forms, concurrent programs, or client code. Any customization should be layered on the reporting metadata or the DBI page definition rather than on this package body, since Oracle may replace its contents during patching. The get_trend_sel_clause forward declaration is internal and is resolved within the same body; it is not exposed as an supported entry point.