Search Results get_cncl_table_sel_clause

Overview

OKI_DBI_SRM_RNWL_PVT is a private PL/SQL package body in the APPS schema that supports Oracle E-Business Suite's Service Renewal Management (SRM) reporting and analytics. Its primary role is to build dynamic SQL statements that drive the DBI (Daily Business Intelligence) dashboard pages and Oracle Daily Business Intelligence for Service Renewals. The package is classified as PVT (private), meaning it is not part of the public integration API surface but rather an internal implementation unit invoked by other components of the OKI/DBI reporting framework.

The core business function is the generation of query strings ("selection clauses" and full SQL) that aggregate renewal bookings, late renewals, cancellations, and forecasts across configurable time buckets and view-by dimensions. Notably, several functions produce the SQL logic that calculates a cancelled_value — a key metric reported in service renewal dashboards representing the monetary value of subscriptions or contracts that were cancelled during a reporting period. The user's search term "cancelled_value" maps directly to the cancellation-related functions such as GET_CANCELLATIONS_SQL and GET_CNCL_TABLE_SQL, which assemble the columns and WHERE conditions required to surface that measure.

Key Procedures and Functions

• GET_TABLE_SQL — The central entry point. It accepts the standard DBI page parameter table (bis_pmv_page_parameter_tbl) and returns the custom SQL string plus a query-attributes collection. It determines the view-by dimension, comparison type, period type, and as-of date, then delegates to the appropriate selection-clause builder.
• GET_BOOKINGS_SQL — Builds SQL for renewal bookings analysis, including the selection clauses required to display booking amounts for the chosen period.
• GET_TOP_BOOKINGS_SQL — Produces SQL ranking top renewal bookings, typically for the "Top N" dashboard regions.
• GET_RENEWAL_FORECAST_SQL — Assembles SQL that forecasts expected future renewals based on historical booking patterns.
• GET_LATE_RNWL_TABLE_SQL — Generates the table query for late renewals, identifying contracts renewed past their due date.
• GET_CNCL_TABLE_SQL — Builds the cancellation table SQL, the primary source of the cancelled_value metric columns.
• GET_CANCELLATIONS_SQL — Assembles the full cancellation analytic SQL, combining the cancellation selection clause with joins and grouping. This function is directly implicated in computing cancelled_value.
• GET_BUCKET_SQL — Produces SQL for time-bucket aggregations (e.g., weekly, monthly, quarterly) used in trend charts.
• GET_BKNG_TREND_SQL and GET_BKNG_TREND_SEL_CLAUSE — Build the booking-trend query and its reusable SELECT/WHERE clause fragment.
• GET_BKNGS_BY_CUST_SQL, GET_EXP_BKNGS_BY_CUST_SQL, and GET_CANCLN_BY_CUST_SQL — Assemble customer-dimension queries for bookings, expected bookings, and cancellations respectively, the last again supplying cancelled_value by customer.
• Numerous private clause-builder functions (GET_TABLE_SEL_CLAUSE, GET_TOP_BOOKINGS_SEL_CLAUSE, GET_BOOKINGS_SEL_CLAUSE, GET_RENWLFORECAST_SEL_CLAUSE, GET_LATE_RNWL_TABLE_SEL_CLAUSE, GET_CNCL_TABLE_SEL_CLAUSE, GET_CANCELLATIONS_SEL_CLAUSE) return reusable SQL fragments and are declared ahead of the main body.

Tables Accessed

The documented table reference is PLITBLM (accessed through its APPS synonym), a materialized-view or fact table underlying the DBI renewal analytics. The package reads from this object when constructing the SQL returned by its functions; it does not itself execute DML against base transactional tables, since its purpose is to hand SQL text back to the DBI page framework. Additional joins are described through the poa_dbi_util_pkg.poa_dbi_join_tbl structure, indicating dynamic join definitions against DBI star-schema objects.

Usage Notes

Because this is a private (PVT) package body, it is not intended for direct customer invocation. It is called by the DBI page framework — specifically the BIS PMV runtime — when a user opens a service-renewal dashboard page and the framework requests a dynamic SQL string. The function GET_TABLE_SQL receives the bis_pmv_page_parameter_tbl structure populated from page parameters such as view-by dimension, period type, and as-of date.

The package is referenced by zero other documented packages, confirming its role as a leaf-level utility. Customizations or extensions should not modify this object directly; instead, the standard Oracle practice of patching or creating a site-specific DBI extension should be followed. Any changes to the cancellation SQL logic will directly affect how the cancelled_value measure appears in the Service Renewal dashboards, making source control and regression testing essential for this unit. It is not registered as a public API and carries no documented signature contracts beyond the private functions listed above.