Search Results populate_bix_sum_grp_cls




Overview
Explore in AI Chat

Overview

BIX_SUMMARY_PUB is a public PL/SQL package body owned by APPS that belongs to the Oracle EBS Interaction Center / Telephony (BIX) schema, the same technology stack that underlies Oracle Advanced Inbound and Advanced Outbound telephony and the interaction history and metrics infrastructure used by the Call Center and Customer Care modules. Its business function is to aggregate raw interaction records captured in BIX_INTERACTIONS into pre-summarized, time-bucketed tables so that reporting, analytics, and agent/group performance dashboards can query denormalized results rather than scanning high-volume transactional interaction rows.

The package populates two families of summary structures: a group/classification-level summary (BIX_SUM_GRP_CLS) and an agent/classification-level summary (BIX_SUM_AGT_CLS). Each aggregates interaction metrics such as ivr_time, route_time, party_wait_time, talk_time, wrap_time, idle_time, preview_time, non_productive_time, response_time, resolution_time, and resolution_time_internal. The user search term "ivr_time" appears directly in the package source: the POPULATE_BIX_SUM_GRP_CLS cursor selects SUM(ivr_time) ivr_time from BIX_INTERACTIONS, meaning IVR (Interactive Voice Response) time is one of the core telephony durations summarized by the procedure. IVR time represents the interval an interaction spends in the automated voice response system before being routed to an agent, and it is a key metric for measuring self-service effectiveness and queue deflection.

Key Procedures and Functions

  • WRITE_LOG — a private helper that writes diagnostic messages to the concurrent manager log file via FND_FILE.PUT_LINE, prefixing each line with "Load Interactions Log - ". It supports troubleshooting of the summarization run.
  • POPULATE_BIX_SUM_X — the documented public procedure (the ETRM entry lists POPULATE_BIX_SUM_X among the documented procedures). In the package body this corresponds to the summarization logic, notably POPULATE_BIX_SUM_GRP_CLS, which opens a cursor over BIX_INTERACTIONS, groups rows by interaction_type, media_item_type, interaction_center_id, campaign_id, resource_id, interaction_classification, and an hour bucket derived via TO_DATE(to_char(start_ts,'DD-MM-YYYY-HH24'),'DD-MM-YYYY-HH24'), and inserts the aggregated results into the summary tables. It first deletes existing rows for the reporting window bounded by the package globals g_start_date and g_end_date to make the run idempotent. Derived counts include transfers, abandoned_count, wait_time_to_abandon, first_interaction_resoln_count, interactions_answered_live, and number_of_interactions, the last two driven by NVL checks on USER_ATTRIBUTE2 and USER_ATTRIBUTE1 respectively.

Tables Accessed

  • BIX_INTERACTIONS — the primary source table; read to obtain raw interaction timing, classification, campaign, resource, and subtype data.
  • BIX_SUM_GRP_CLS (with synonym/sequence BIX_SUM_GRP_CLS_S) — target table for group-level, classification-level hourly summaries; deleted and re-inserted for the active date range.
  • BIX_SUM_AGT_CLS (with synonym/sequence BIX_SUM_AGT_CLS_S) — target table for agent-level summaries produced by the companion aggregation path.

Usage Notes

The package is typically invoked by a concurrent program scheduled to run periodically (for example nightly or at short intervals) to refresh interaction summaries. Because it deletes and rebuilds data for a bounded window controlled by g_start_date and g_end_date, it should not be run concurrently against overlapping date ranges. It is referenced by zero other packages according to the ETRM metadata, so custom code, concurrent program definitions, and reporting queries are the expected consumers. Any extension or customization should preserve the hour-bucketing convention and the DECODE-based counting logic to keep summary figures consistent with base interaction data.