Search Results get_table_sel_clause




Overview
Explore in AI Chat

Overview

The APPS.OKI_DBI_SRM_BKRNWL_PVT package body is a private Oracle E-Business Suite PL/SQL construct that supports the Booking-to-Renewal Ratios Drill Down Report within the Service Renewal Management (SRM) reporting framework. Classified as a private (PVT) API under the APPS schema, it functions as the SQL-generation engine behind an Oracle Daily Business Intelligence (DBI) style report. Its central responsibility is to dynamically assemble a query string and the corresponding query attributes that the reporting infrastructure executes at runtime.

Unlike business-facing APIs, this package is not intended for direct invocation by end users or external integrations. Instead, it constructs context-sensitive SQL fragmwnts — including selection clauses, view-by dimensions, trend clauses, and date-filtered where conditions — based on parameters supplied by the reporting page. The package header reveals a modular design in which individual helper functions produce reusable SQL fragments that are concatenated into the final statement returned through get_rates_table_sql. The build version header (OKIIBKGB.pls 120.1, dated 2006/03/28) indicates the package has remained stable across the 12.1.1 and 12.2.2 release lines.

Key Procedures and Functions

Three procedures and functions are documented for this package:

  • GET_RATES_TABLE_SQL — The main report driver. It builds the complete SQL statement for the Booking-to-Renewal Ratios Drill Down Report, accepting reporting page parameters and returning the generated query and its output attribute definitions. Internally it declares an extensive set of local variables covering view-by columns, as-of and prior-as-of dates, comparison and period types, currency conversion flags, join tables, and filter where-clauses supplied by the reporting framework utilities.
  • GET_TABLE_SQL — A supporting routine that assembles the table-level SQL select statement, resolving the view-by dimension and column configuration for the report.
  • GET_TREND_SQL — Produces the trend-related SQL selection clause used when the report presents period-over-period trend analysis.

Additional internal helper functions visible in the source excerpt include get_table_sel_clause (the subject of the user's search), get_book_start_2way_sql, get_trend_sel_clause, and get_col_name. These encapsulate reusable SQL construction logic such as the table selection clause, the two-way booking/start clause, and dimension-to-column name mapping.

Tables Accessed

The only documented table referenced through APPS synonyms is PLITBLM, an Oracle Applications descriptive flexfield (DFF) table. This access typically supports translation or attribute lookup for flexfield segments used in report filtering or column labeling. All other data retrieval is performed against the database objects embedded directly in the generated SQL string rather than through static table references in the package body.

Usage Notes

This package is invoked indirectly through the Oracle DBI/PMV reporting infrastructure. The reporting page supplies a parameter collection (BIS_PMV_PAGE_PARAMETER_TBL), and get_rates_table_sql returns a dynamically constructed SQL statement plus query attributes that the reporting engine executes and renders. It is not referenced by any other package in the documented metadata, confirming its role as a terminal, report-specific private component.

Because the package is classified PVT, Oracle does not guarantee its interface across patches. Customizations that call get_table_sel_clause or other internal functions directly risk breakage during upgrades. The recommended approach for extending the report is to overlay or wrap the report definition rather than modify the package body. Its persistence across 12.1.1 and 12.2.2 indicates broad compatibility, but the standard Oracle support policy for private APIs still applies.