Search Results get_bookings_sel_clause




Overview
Explore in AI Chat

Overview

OKI_DBI_SRM_PRNWL_PVT is a private PL/SQL package body owned by APPS that supports the Oracle Daily Business Intelligence (DBI) reporting framework for Order Management, specifically the Period Renewals Summary subject area. In Oracle EBS 12.1.1 and 12.2.2, this package is part of the OKI (Order Management Intelligence) module and collaborates with the BIS (Business Intelligence System) page framework to dynamically construct SQL statements executed against pre-aggregated materialized views. Its principal responsibility is to translate runtime page parameters — view-by dimensions, comparison types, period types, and currency/OU/CC flags — into valid, target-specific SQL that drives the Period Renewals Summary Report (internally referenced as DBI 6.0 and carried forward under a DBI 7.0 header). The package is classified as PVT, indicating it is not a published public API but an internal implementation element consumed by the BIS reporting layer.

Key Procedures and Functions

The documented inventory of this package comprises six procedures and functions, each dedicated to a specific report or clause-generation task in the DBI reporting pipeline:

  • GET_TABLE_SQL — The central engine of the package. It receives the BIS page parameter table and returns a fully assembled custom SQL string and a query-attributes structure to the calling page. It orchestrates the view-by resolution, comparison-type handling, XTD/period derivation, and assembly of nested materialized-view queries used by the Period Renewals Summary Report.
  • GET_BOOKINGS_SQL — Generates the SQL statement used to retrieve bookings amounts for the reporting period. This is the function most closely associated with the search term "get_bookings_sel_clause", since bookings-specific selection clauses are constructed in support of this output.
  • GET_RRATE_SQL — Produces the SQL that returns renewal rate metrics, a core performance indicator in the Period Renewals Summary subject area.
  • GET_PR_TREND_SQL — Builds the SQL driving the period-renewals trend analysis, enabling period-over-period comparison of renewal activity.
  • GET_TREND_SEL_CLAUSE — Returns the reusable selection (WHERE-clause fragment) applied to the trend queries so that consistent filtering is enforced across trend outputs.
  • GET_BKNGS_BY_CUST_SQL — Generates the SQL for the bookings-by-customer breakdown. A legacy companion function, GET_BKGS_BY_CUST_SEL_CLAUSE, is present but commented out in the source, reflecting evolution of the DBI reporting model.

Although the search term references GET_BOOKINGS_SEL_CLAUSE, the documented function closest in purpose is GET_BOOKINGS_SQL, which consumes the same kind of suffix and period-type inputs used to assemble bookings selection logic.

Tables Accessed

The only documented table referenced through APPS synonyms is PLITBLM, a materialized-view support table. In DBI SRM reporting, PLITBLM functions as the target of the aggregated renewal, bookings, and trend data that the dynamically generated SQL queries against. The package does not write to base transactional tables; instead it reads pre-aggregated results from PLITBLM to satisfy the report while minimizing runtime cost on the Order Management transactional schema.

Usage Notes

OKI_DBI_SRM_PRNWL_PVT is invoked indirectly through the BIS page framework, most commonly when a user opens the Period Renewals Summary report in the Daily Business Intelligence or Order Management Intelligence dashboard. The BIS runtime passes the page parameter table into GET_TABLE_SQL, which returns the SQL that the framework executes against PLITBLM. Because the package is classified as PVT and exposes no public API contract, it should not be called from custom code, concurrent programs, or forms outside the supported BIS reporting path. No other packages are documented as referencing it, confirming its role as a terminal implementation unit within the DBI stack. The header revision (120.3, dated 2006) indicates stability across both EBS 12.1.1 and 12.2.2, where the DBI reporting architecture retained the same internal composition for this subject area.