Search Results bind_reqfact_date




Overview
Explore in AI Chat

Overview

APPS.POA_DBI_SUTIL_PKG is a shared utility package within the Oracle EBS Procurement and Oracle Purchasing Intelligence (POA) schema. It provides the common SQL-construction, filter-generation, and materialized-view resolution logic that underpins the POA Daily Business Intelligence (DBI) dashboards and the Procurement "Spend" analytics in releases 12.1.1 and 12.2.2. Rather than embedding query text inside each dashboard page definition, the POA DBI pages delegate to this package to dynamically assemble the view-by columns, security predicates, aggregation levels, and fact-table joins required to render a given page.

The package is best understood as a parameter-processing and SQL factory. It translates page parameter tables (BIS_PMV_PAGE_PARAMETER_TBL) into a normalized set of outputs — a view-by dimension, an as-of date, a comparison type, a where clause, and a materialized-view name — which are then combined into the query returned to the BI presentation layer. It also exposes a large set of public constants and a bitmap scheme (OPER_UNIT_BMAP, REASON_BMAP, COSTCTR_BMAP, and others) that encode each supported dimension as a distinct bit, allowing dimensions to be combined arithmetically and tested with bitwise operations.

Key Procedures and Functions

  • GET_FILTER_WHERE — Builds a WHERE fragment from a supplied table of filter columns.
  • PROCESS_PARAMETERS — The central entry point; derives view-by, view-by value, comparison type, as-of date, previous as-of date, suffix, nested pattern, where clause, materialized view, and join tables from incoming page parameters.
  • DRILL_PROCESS_PARAMETERS — Variant of parameter processing used for drill-down navigation.
  • BIND_REQFACT_DATE — Binds the requisition fact date used in date-sensitive queries.
  • INIT_DIM_MAP — Initializes the dimension-to-materialized-view mapping.
  • GET_MV — Returns the materialized view appropriate to the requested aggregation and dimension combination.
  • GET_COL_NAME — Resolves a dimension to its underlying column name.
  • GET_SECURITY_WHERE_CLAUSES / GET_IN_SECURITY_WHERE_CLAUSES — Produce the row-level security predicates for company and cost-center data, expressed as standard and IN-list forms.
  • GET_JOIN_INFO / POPULATE_IN_JOIN_TBL — Determine and populate join metadata between the fact and dimension structures.
  • GET_TABLE — Returns the base table or view name for a given query context.
  • GET_VIEWBY_SELECT_CLAUSE — Emits the SELECT list corresponding to the chosen view-by dimension.
  • GET_FACT_HINT — Supplies an optimizer hint for the fact table access path.
  • POPULATE_AGG_LEVEL / GET_AGG_LEVEL — Populate and retrieve the aggregation level and its associated bitmap.
  • GET_DISPLAY_CATEGORY / GET_COST_CTR_SQL / GET_DISPLAY_SUPPLIER — Return display-oriented SQL for category, cost center, and supplier dimensions respectively. GET_COST_CTR_SQL is the documented member that generates the cost-center SQL fragment used when the COSTCTR_BMAP bit is set in the view-by bitmap, and is the function users typically locate when troubleshooting cost-center spend reporting.

Tables Accessed

Usage Notes

POA_DBI_SUTIL_PKG is not a user-facing API. It is invoked internally by the POA DBI page logic and by approximately twenty-two other packages in the same schema, which call its parameter-processing and SQL-generation routines to render Procurement dashboards, spend analyses, and supplier or cost-center drill-downs. It operates entirely within the context of the logged-in user, relying on FND_USER and the FII grant tables to enforce row-level security on company and cost-center dimensions. Customizations should not modify the package; extensions should instead wrap or replicate its behavior, and any change to the bitmap constants must remain consistent with the view-by values passed by the standard DBI pages.