Search Results update_timebuckets_label

Overview

QRM_PA_AGGREGATION_P is a PL/SQL package owned by the APPS schema in Oracle E-Business Suite. In ETRM (Enterprise Treasury and Risk Management), it plays a central role in the Portfolio Analysis feature set, where large volumes of deal, position, and market data must be condensed into meaningful aggregations before calculations and reporting can occur. The package sits directly beneath the calculation layer, transforming detailed deal-level rows into aggregated result sets, applying time-bucket logic, computing totals, percentages, currency conversions, and relative date ranges.

The internal name itself reflects its purpose: "PA" denotes Portfolio Analysis, and "AGGREGATION" denotes the reduction and summarization of granular data. Because the package uses dynamic SQL constructs, it builds and executes aggregation statements at runtime rather than relying on static cursors, which allows it to support flexible, user-defined analysis configurations.

Key Procedures and Functions

The package exposes thirteen documented procedures and functions. Their names indicate the functional scope of the aggregation engine:

• TRANSFORM_AND_SAVE — the main driver; transforms source data into the aggregated result set and persists it.
• CALCULATE_RELATIVE_DATE — derives relative date values used to drive time-based aggregation.
• UPDATE_TIMEBUCKETS — applies time-bucket grouping to aggregated rows.
• UPDATE_TIMEBUCKETS_LABEL — assigns display labels to those time buckets.
• UPDATE_TOTAL — populates total amounts within the aggregate result set.
• UPDATE_PERCENT — computes percentage contributions relative to totals.
• UPDATE_LABEL — sets descriptive labels on aggregate output.
• CALC_TABLE_TOTAL_AND_FXRATE — computes table-level totals and applies foreign exchange rates.
• UPDATE_AGGREGATE_CURR — converts or restates aggregate amounts in the target reporting currency.
• UPDATE_SEMIDIRTY — handles semi-dirty (semi-additive) measure handling for balances that cannot simply be summed.
• GET_FX_RATE — retrieves the applicable exchange rate for conversion.
• CALC_TB_START_END_DATES — derives start and end dates for each time bucket.

Tables Accessed

The package reads from and writes to several core ETRM tables via APPS synonyms. QRM_SAVED_ANALYSES_COL and QRM_SAVED_ANALYSES_ROW define the user-configured analysis layout (columns and rows), while QRM_ANALYSIS_SETTINGS and QRM_ANALYSIS_ATTS hold analytical parameters and attribute definitions, with QRM_ANA_ATTS_LOOKUPS supplying lookup values. QRM_DEALS_ANALYSES and QRM_DEAL_CALCULATIONS store deal-level analysis output and calculation results consumed during aggregation. QRM_TIME_BUCKETS and QRM_TIME_INTERVALS provide the time-bucketing definitions that drive CALC_TB_START_END_DATES and UPDATE_TIMEBUCKETS.

Calendar and date logic draws on GL_TRANSACTION_CALENDAR and GL_TRANSACTION_DATES. Market data comes from XTR_SPOT_RATES, while trade master data comes from XTR_DEAL_TYPES and XTR_PARTY_INFO. DBMS_SQL is used for dynamic statement execution, and the internal collection types QRM_DATE_TABLE, QRM_VARCHAR_TABLE, and QRM_VARCHAR240_TABLE are referenced. Notably, QRM_DATE_TABLE is the object surfaced by the user's search term "qrm_date_table," confirming that this package is among the key consumers of that collection type.

Usage Notes

QRM_PA_AGGREGATION_P is invoked indirectly rather than from end-user forms. It is called by the QRM_PA_CALCULATION_P package, which orchestrates the broader portfolio analysis calculation flow. Custom or extension code should not call the package's procedures directly, as their execution order and internal state are managed by the parent calculation package. Typical execution is triggered through portfolio analysis runs initiated from ETRM's analysis or report screens, or via concurrent programs that perform recalculation. When troubleshooting related issues, DBMS_SQL-based dynamic SQL is a frequent source of error messages, and trace/log output should be reviewed alongside the analysis settings and time-bucket definitions.