Search Results get_bucket_sql




Overview
Explore in AI Chat

Overview

OKI_DBI_SRM_RNWL_PVT is a private (PVT) PL/SQL package owned by the APPS schema in Oracle E-Business Suite. It belongs to the Service Renewals Management (SRM) reporting layer associated with the Oracle Contracts and Service modules, and forms part of the DBI (Daily Business Intelligence) family of packages whose naming convention carries the OKI_DBI prefix. The package serves as a SQL-generation engine for Business Intelligence dashboards and HTML-based reporting pages that present renewal, bookings, cancellation, and forecast analytics. Rather than storing or manipulating transactional data directly, it exposes a set of procedures that construct dynamic SQL statements at runtime, returning them to the Business Intelligence reporting framework through standard output parameters. This design is characteristic of Oracle's PMV (Page Metadata View) reporting architecture, where a caller passes page parameters and receives a generated query string plus associated query attribute metadata used to render the resulting report region. The package header carries a header tag dated March 2006 (script version 120.1), indicating it is a legacy component carried forward through the 12.1.1 and 12.2.2 releases largely unchanged.

Key Procedures and Functions

The package documents thirteen public procedures and functions, each producing a targeted SQL fragment for a distinct report element. All procedures follow the same signature pattern: they accept p_param of type bis_pmv_page_parameter_tbl and return x_custom_sql and x_custom_output of types VARCHAR2 and bis_query_attributes_tbl respectively.

  • get_table_sql — Builds the base query used by the primary renewals summary table region.
  • get_bookings_sql — Generates the SQL for the bookings analysis report.
  • get_top_bookings_sql — Produces SQL for a ranked view of top bookings.
  • get_renewal_forecast_sql — Constructs the query behind the renewal forecast region.
  • get_late_rnwl_table_sql — Generates SQL for the late renewal table.
  • get_cncl_table_sql — Builds the SQL for the cancellation table region.
  • get_cancellations_sql — Produces the cancellations analysis query.
  • get_bucket_sql — Generates bucketed (aged) analysis SQL.
  • get_bkng_trend_sql — The procedure matching the user's search term; constructs the bookings trend query used to plot booking movement over time.
  • get_bkng_trend_sel_clause — A companion function returning the SELECT clause fragment supporting the bookings trend report.
  • get_bkngs_by_cust_sql — Produces SQL for bookings aggregated by customer.
  • get_exp_bkngs_by_cust_sql — Generates SQL for expiring bookings by customer.
  • get_cancln_by_cust_sql — Builds the cancellations-by-customer query.

Tables Accessed

According to the documented metadata, the package references a single table via APPS synonyms: PLITBLM. In the Oracle EBS schema, PLITBLM is a PL/SQL internal table structure commonly used to hold page-parameter or list values during dynamic SQL assembly. Its presence here confirms that the package manipulates parameter collections in memory rather than persisting data. No base transactional tables are read or written directly by this package; all real data access occurs through the dynamically generated SQL strings executed by the calling Business Intelligence framework.

Usage Notes

Because the procedures are classified as PRIVATE and return only query text, they are not intended for direct invocation by end users or standalone customer code. They are called by the DBI/PMV reporting infrastructure — typically when an SRM dashboard or HTML report region is rendered — to obtain the SQL that the framework then executes and formats. In EBS 12.1.1 and 12.2.2 environments, calls originate from the Business Intelligence page rendering engine, not from Forms or standard concurrent programs. The package is referenced by zero other packages, underscoring its role as a terminal, framework-invoked utility. Customizations to SRM renewal, bookings, cancellation, or forecast dashboard queries are sometimes implemented by cloning or wrapping these procedures, but modifications to the seeded package should be avoided in favor of supported extension mechanisms.