Search Results get_bucket_drill_url
Overview
POA_DBI_UTIL_PKG is a utility package in the APPS schema that provides the shared plumbing for the Procurement and Spend Analytics (POA) Oracle Business Intelligence (DBI) dashboards in Oracle E-Business Suite. It abstracts the repetitive, parameter-driven mechanics of building OracleBI Discoverer / PMV-style analytical queries: constructing WHERE clauses, resolving security profiles, attaching calendar periods, generating bucket (measure) columns, and assembling drill-down URLs. In effect, it is the common toolkit that the various POA_DBI_* dashboard packages call upon so that procurement spend, savings, supplier, and cycle-time analytics render consistently and honour the same row-level security and date bucketing rules.
The object is the package body of a package whose specification is also present (the dependency list shows the body references its own package, POA_DBI_UTIL_PKG, as expected). It is marked VALID and is a heavily reused component: the ETRM metadata records that it is referenced by 108 other database objects, while it itself depends on nothing external within APPS beyond its own helpers and binary_* BIS utilities.
Key Procedures and Functions
The 35 documented program units fall into several functional groups:
- WHERE / filter construction: GET_WHERE_CLAUSES, GET_FILTER_WHERE, GET_IN_COMMODITY_SEC_WHERE, and GET_COMMODITY_SEC_WHERE build and apply the filtering predicates (including commodity-level security restrictions) that restrict dashboard data to the requesting user's authorised scope. CHANGE_CLAUSE and RATE_CLAUSE produce standard clause fragments for change and rate measures.
- Security and profile resolution: GET_SEC_PROFILE, GET_FND_USER_PROFILE, and GET_FND_EMPLOYEE_PROFILE derive the security profile and user/employee attribute values that drive row-level access.
- Calendar and period handling: GET_CALENDAR_TABLE, GET_NESTED_PATTERN, GET_NESTED_PERIOD_TYPE_ID, and GET_BUCKET_DRILL_URL support time-based bucketing and drilled navigation.
- Bucket / column generation: ADD_COLUMN, ADD_BUCKET_COLUMNS, and GET_BUCKET_OUTER_QUERY assemble the measure columns and the outer query wrapper for each dashboard page.
- Parameter and helper utilities: GET_PARAMETER_VALUES, GET_DRILL_PARAM_VALUES retrieve page parameter values; BITOR provides a bitwise-OR flag helper; REFRESH triggers materialized view refresh via DBMS_MVIEW.
Only the above names, and not their signatures, are documented in ETRM; parameter lists should be confirmed from the deployed source.
Tables Accessed
The ETRM documentation records only two objects in the "tables referenced" list: the package DBMS_MVIEW and PLITBLM (the PL/SQL integer table type used in bulk operations). In practice the package does not persist business data itself; it reads from the BIS (Business Intelligence System) PMV/collection utilities (BIS_BUCKET_PUB, BIS_COLLECTION_UTILITIES, BIS_COMMON_PARAMETERS, BIS_PMV_PARAMETERS_PUB, BIS_QUERY_ATTRIBUTES, BIS_UTILITIES_PUB), the FII_TIME_API, FND_DATE, FND_GLOBAL, FND_PROFILE, POA_DBI_CALENDAR_PKG, and POA_LOG. DBMS_MVIEW is invoked specifically for materialized-view refresh, and PLITBLM for bulk processing. These dependencies confirm the package acts as a read/derivation layer rather than a data store.
Usage Notes
POA_DBI_UTIL_PKG is invoked indirectly by the POA_DBI_* dashboard packages (108 dependent objects) rather than by end users. It is typically triggered when a Procurement and Spend Analytics dashboard page is executed through the OracleBI / PMV framework, when a drill-down link is followed (GET_BUCKET_DRILL_URL, GET_DRILL_PARAM_VALUES), or when a refresh is scheduled through POA_DBI_CALENDAR_PKG. Custom code extending POA analytics can call its utility functions, but should avoid duplicating logic already present in this shared package. Because it is a body-only dependency, it should not be modified without recompiling dependent POA packages and testing affected dashboards.