Search Results get_pr_trend_sql




Overview
Explore in AI Chat

Overview

OKI_DBI_SRM_PRNWL_PVT is a private PL/SQL package owned by the APPS schema in Oracle E-Business Suite. It belongs to the Oracle Knowledge Information (OKI) product family, specifically the DBI (Daily Business Intelligence) subsystem for Supplier Relationship Management (SRM). The package functions as a SQL generation engine for DBI dashboard regions within the OKI Procurement Reporting framework.

Unlike conventional PL/SQL packages that perform business transactions directly, OKI_DBI_SRM_PRNWL_PVT constructs dynamic SQL statements at runtime. Each procedure accepts a page parameter table (bis_pmv_page_parameter_tbl) that carries dashboard region context, such as user selections and filter criteria, and returns a fully assembled SQL statement along with query attributes. This architecture is characteristic of the BIS/BIS_PMV framework, where the DBI page engine delegates SQL construction to product-specific packages so that dashboards can present tailored procurement metrics without embedding SQL in the page definition itself.

Key Procedures and Functions

The package exposes six documented procedures and functions, each responsible for generating a distinct DBI dashboard query:

  • GET_TABLE_SQL — Produces the SQL for a tabular dashboard region, returning the select statement text and its associated output attributes.
  • GET_BOOKINGS_SQL — Generates SQL for the bookings analytics region, supporting the procurement bookings metric displayed on SRM dashboards.
  • GET_RRATE_SQL — Constructs the SQL behind the receipt rate or related rate-based reporting region.
  • GET_PR_TREND_SQL — Builds the trend query used for purchase requisition trend analysis over time.
  • GET_TREND_SEL_CLAUDE — A function returning a VARCHAR2 trend selection clause, used as a shared building block by the trend-oriented procedures rather than as a standalone dashboard query.
  • GET_BKNGS_BY_CUST_SQL — The object matching the search term "get_bkngs_by_cust_sql." It generates SQL for the bookings-by-customer breakdown region, allowing dashboard users to view procurement bookings segmented by customer.

All procedures follow the same output signature pattern: a custom SQL string and a bis_query_attributes_tbl output collection. Exact parameter lists are not reproduced here; the documented interface for each is the page parameter table plus these two OUT NOCOPY parameters.

Tables Accessed

ETRM documents a single referenced table, PLITBLM, accessed through an APPS synonym. Given the package's role as a SQL generator rather than a data processor, table access is largely indirect: the generated statements are executed by the BIS_PMV page framework, and PLITBLM appears to serve as a reference or lookup table consulted while assembling query text. The heavy analytical data access occurs downstream when the returned SQL is run against the DBI materialized views and fact tables of the procurement reporting schema.

Usage Notes

OKI_DBI_SRM_PRNWL_PVT is classified as a private (PVT) API, indicating that it is an internal implementation detail of the DBI procurement dashboard and not part of a supported public interface. It is invoked by the BIS_PMV region rendering engine when a user opens the corresponding SRM dashboard page; the framework passes region parameters in and executes the returned SQL. The package is referenced by zero other packages in the documented metadata, confirming its narrow, page-driven role. Custom code should not call these procedures directly, as the private classification signals that signatures and behavior may change without notice between releases. In Oracle EBS 12.1.1 and 12.2.2, the package operates identically from a developer perspective, with 12.2.2 offering no documented API change for this object.