Search Results biv_dbi_res_pkg

Overview

APPS.BIV_DBI_RES_PKG is a PL/SQL package that belongs to the Oracle E-Business Suite "BIV" module, which corresponds to the Oracle Daily Business Intelligence (DBI) and Business Intelligence (BI) reporting infrastructure. The package serves as a custom SQL provider for the Oracle Balanced Scorecard and DBI reporting engine, generating dynamically constructed SQL statements that drive resource-oriented analytical dashboards and drill-down reports. Its package header, defined with the source identifier bivsrvrress.pls, exposes a small set of procedures that conform to the standard DBI custom SQL interface, accepting a bis_pmv_page_parameter_tbl parameter collection and returning both a SQL string (x_custom_sql) and a query attribute collection (x_custom_output) to the calling reporting framework.

The package is classified as an "OTHER" API in the ETRM 12.1.1 documentation. It is not an extension of a published business API, but rather an internal helper used by the DBI reporting engine to resolve the appropriate query at runtime based on user-supplied page parameters such as time period, resource, and organization context.

Key Procedures and Functions

Five procedures are documented in the package specification. Each follows the same signature pattern, returning a SQL statement and its associated output attributes through OUT parameters, so that the DBI engine can execute the query and render the result set.

• GET_TBL_SQL — Generates the SQL for the table-level (TBL) resource view, with an optional p_distribution flag defaulting to 'N' to control whether distribution-oriented output is produced.
• GET_DBN_TBL_SQL — Generates the SQL for the drill-down-by-number (DBN) variation of the table-level resource view.
• GET_TRD_SQL — Generates the SQL for the trend (TRD) resource view, again with the optional p_distribution parameter.
• GET_DBN_TRD_SQL — Generates the SQL for the drill-down-by-number variation of the trend resource view.
• GET_DETAIL_SQL — The procedure associated with the search term "get_detail_sql". It generates the detail-level SQL used when a user drills into the underlying transactional records behind an aggregated resource metric. This is the deepest level of the drill-down hierarchy in the DBI resource dashboard, returning row-level detail rather than summarized figures.

Tables Accessed

The provided ETRM metadata lists no directly referenced tables via APPS synonyms, which is consistent with the package's function: it does not itself query business tables, but instead constructs and returns SQL text that the DBI reporting engine subsequently executes against the underlying materialized views, summary tables, and transactional base tables of the resource subject area. The actual table references in the generated SQL are resolved at runtime, which is why they do not appear as static dependencies in the package metadata.

Similarly, the package is documented as being referenced by zero other packages, confirming that it is a leaf-level implementation detail of the reporting engine and not a shared utility.

Usage Notes

BIV_DBI_RES_PKG is not intended to be called directly by application forms, concurrent programs, or custom code. It is invoked by the Oracle DBI/Balanced Scorecard page rendering framework when a user navigates a resource-related dashboard region. The framework passes the current page parameters to the appropriate procedure, receives the constructed SQL, and executes it to populate the region.

Because the procedures return dynamic SQL, any customization should be performed by copying the package and registering a replacement through the standard DBI customization mechanism rather than editing the seeded package in place. Direct modification risks being overwritten during patching, and the generated SQL depends on internal summary structures whose schema is not part of the public data model. In 12.1.1 and 12.2.2, the package is shipped under the APPS schema and carries a "$Header" comment dated 2005, indicating that the implementation has remained stable across releases.