Search Results get_visit_trend_sql




Overview
Explore in AI Chat

Overview

The APPS.IBW_BI_VST_CMP_KPI_PVT package body is a private (PVT-classified) PL/SQL implementation module belonging to the Oracle E-Business Suite 12.1.1 / 12.2.2 Oracle Business Intelligence (OBI) infrastructure. The "IBW" prefix identifies it as part of the Oracle Marketing / iStore web intelligence and business intelligence subsystem, while the "BI" and "KPI" tokens indicate that it supplies business intelligence query definitions and Key Performance Indicator metadata to the BI presentation layer. The suffix _PVT confirms that the package is intended for internal consumption only; it is not a public API and should not be called directly by customer extensions.

The package functions as a provider of dynamically generated SQL statements for BI dashboard and analytics pages. Each procedure conforms to the standard signature used by the EBS BI query-attribute framework, accepting a BIS_PMV_PAGE_PARAMETER_tbl parameter collection and returning a custom SQL string (x_custom_sql) plus a bis_query_attributes_TBL result collection. This conforms to the Oracle PMV (Page Metadata View) customization model used by the BI Discoverer / Forms-based analytics engine, where parameter values captured on the page drive the SELECT statement executed against the warehouse. As shipped in release 12, the header comment ($Header: ibwbvckb.pls 120.20) indicates the file has been stable since at least 2011, with all procedure bodies implemented as NULL stubs — the package serves as an extension hook that customers or Oracle patches can override.

Key Procedures and Functions

  • GET_VISTR_CONV_TRND_SQL — Generates the SQL for the visitor conversion trend analysis view. The name concatenates "visitor / conversion / trend," indicating it produces the query used by BI conversion-rate dashboards tracking visitor-to-buyer progression over time.
  • GET_VISIT_TREND_SQL — Supplies the SQL for the visitor traffic trend report, typically used to chart session or visit volumes across time buckets on a BI page.
  • GET_WEB_CAMPAIGN_SQL — Returns the SQL backing web campaign performance analytics, correlating campaign activity with traffic or conversion metrics sourced from the marketing warehouse.
  • GET_KPI_SQL — The procedure targeted by the search term "get_kpi_sql." It is the entry point that delivers SQL for Key Performance Indicator computations displayed on BI pages. Because it implements the same PMV signature, it accepts page parameters and outputs both the SQL text and the associated query attribute collection. As shipped, the body is a NULL stub, meaning the default behavior defers to the standard BI seeding logic unless patched.

Tables Accessed

The documented excerpt lists no direct table references, which is consistent with a package whose bodies are empty stubs and whose SQL is entirely returned as text rather than executed internally. When functional, queries produced by these procedures would target the Oracle Marketing and web-intelligence warehouse tables surfaced through APPS synonyms (for example, visitor, campaign, and KPI fact tables within the BIS schema family). No INSERT, UPDATE, or DELETE activity is implied by the documented metadata; the object is read-oriented, and its output is consumed by the calling BI engine at runtime, not by the package itself.

Usage Notes

The procedures are invoked by the EBS BI / PMV page framework when rendering marketing analytics dashboards, not by end users directly. In 12.1.1 and 12.2.2 the file is a standard customization point: customers who need to alter the SQL behind a visitor, campaign, or KPI BI region copy the package body and implement the appropriate GET_*_SQL procedure. Because the ETRM metadata records zero referencing packages and a PVT classification, direct invocation from forms, concurrent programs, or custom code is unsupported. Any modification must preserve the documented PMV parameter and output signatures, or the BI page will fail to resolve its query at runtime.