Search Results get_filter_where

Overview

APPS.POA_DBI_UTIL_PKG is a shared PL/SQL utility package within the Oracle E-Business Suite Procurement and Oracle Purchasing Intelligence (POA) product family. Its primary business function is to centralize the construction of dynamic SQL fragments, joins, security predicates, and calculation templates used by the POA DBI (Daily Business Intelligence) reporting layer. Rather than embedding repetitive SQL-building logic across dozens of dependent programs, the POA reporting objects delegate common tasks — building WHERE clauses, resolving security profiles, generating join definitions, and formatting bucket and drill-down queries — to this single package.

The package carries a header tag dated 2005 and version 120.2, indicating it is an established, shipped component of the ETRM and Procurement Intelligence codebase that remains in service across Release 12.1.1 and 12.2.2. Its modular design, expressed through the several collection types declared in the specification (join records, column lists, union query records, and calculation records), shows that it was purpose-built to support metadata-driven report generation rather than a fixed, hard-coded report.

Key Procedures and Functions

The package exposes 35 documented procedures and functions. The most prominent groups are as follows:

• Security and profile resolution: GET_SEC_PROFILE, GET_FND_USER_PROFILE, GET_FND_EMPLOYEE_PROFILE, and the MOAC-related globals (g_org_id, g_sec_profile_id) resolve the operating unit and security context that constrain report output.
• WHERE clause generation: GET_WHERE_CLAUSES, GET_FILTER_WHERE, GET_COMMODITY_SEC_WHERE, and GET_IN_COMMODITY_SEC_WHERE build predicate strings, including the commodity-level security filters the user searched for.
• Join and query assembly: GET_NESTED_PATTERN, GET_BUCKET_OUTER_QUERY, GET_BUCKET_DRILL_URL, and the poa_dbi_join_rec/tbl types support construction of multi-table and union-all report templates.
• Column and calculation helpers: ADD_COLUMN, ADD_BUCKET_COLUMNS, CHANGE_CLAUSE, RATE_CLAUSE, and the poa_dbi_col_calc_rec type format column expressions and date decodes.
• Period and parameter utilities: GET_CALENDAR_TABLE, GET_NESTED_PERIOD_TYPE_ID, GET_PARAMETER_VALUES, GET_DRILL_PARAM_VALUES, and BITOR handle calendar-period resolution and bitwise flag logic.
• Refresh: REFRESH is used to synchronize materialized view or summary data supporting the DBI reporting layer.

Tables Accessed

The documented references list DBMS_MVIEW and PLITBLM. DBMS_MVIEW is the standard Oracle-supplied package used to refresh materialized views, confirming that REFRESH drives summary data used by the DBI dashboards. PLITBLM is the PL/SQL table-to-BLOB utility used to handle large string or collection payloads, consistent with the package's heavy string-building role. The package also joins against reporting views such as poa_items_v, as evidenced by example aliases in the type declarations.

Usage Notes

POA_DBI_UTIL_PKG is not an end-user-facing API. It is a foundational library invoked internally by 108 other packages in the POA/DBI framework, including those that back the Procurement Intelligence dashboards, reports, and drill-down pages. Custom code extending POA reporting should call these procedures to remain consistent with the shipped security and SQL-generation conventions rather than reimplementing them. Because it references DBMS_MVIEW and PLITBLM and is granted to APPS, appropriate execution privileges are already established. Direct invocation in Forms or concurrent programs is uncommon; the package is generally reached transitively through the POA report engine.