Search Results bix_sum_info_n1

Overview

The table BIX.BIX_SUM_INFO serves as a central summary table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, specifically for the Oracle Interaction Center Intelligence domain. This schema object belongs to the BIX module, which is part of Oracle’s CRM and contact center analytics framework. BIX_SUM_INFO is designed to store aggregated, historical data about interactions—primarily telephone calls—that occur within a contact center environment. It is used to support reporting, dashboards, and trend analysis by pre-summarizing high-volume transactional data into hourly records. The table is marked as "Oracle Internal Use Only," and Oracle Corporation does not support direct access from custom programs; all queries should ideally be routed through standard Oracle Applications APIs or reporting interfaces. The table resides in the APPS_TS_SUMMARY tablespace, indicating it is intended for summary-level data with a balance between performance and storage efficiency.

Key Information Stored

BIX_SUM_INFO stores one or more rows per hour for each combination of dimension attributes related to an interaction. Its primary key is SUM_INFO_ID (data type NUMBER). The following columns capture the core dimensions and measures:

• HOUR (DATE): The hour of the summary period, serving as the primary time grain. This is indexed via the nonunique index BIX_SUM_INFO_N1.
• CAMPAIGN_ID, LIST_ID, SCRIPT_ID, PRODUCT_GROUP_ID, RESOURCE_ID, RESOURCE_GROUP_ID, INTERACTION_CENTER_ID: Foreign keys to dimension tables identifying the campaign, contact list, script, product, agent, agent group, and call center associated with the interaction.
• MEDIA_ITEM_TYPE (VARCHAR2, 240): Describes the medium of the interaction (e.g., Inbound Phone, Outbound Phone, Email, Chat).
• INTERACTION_TYPE (VARCHAR2, 32): Specifies the type of call placed (e.g., Inbound, Outbound, Transfer).
• INERACTION_CLASSIFICATION (VARCHAR2, 240): Represents the classification assigned by the routing module, such as "IVR Path 1" or "Skill Group Transfer." This is a critical attribute for analyzing how interactions are categorized within the routing engine.
• HANDLER_ID, OUTCOME_ID, RESULT_ID, REASON_ID: Numerical identifiers linking to the client application, general outcome, specific result, and reason for the outcome.
• PARTY_ID (NUMBER, 15): Links to a customer party or account identifier.
• KEY, SUBKEY, VALUE, NUMBER_OF_INTERACTIONS: A flexible measure structure where KEY stores a metric label (e.g., "Revenue"), SUBKEY stores optional detail, VALUE stores the aggregated numeric value (e.g., 55.00), and NUMBER_OF_INTERACTIONS records the count of interactions contributing to that value.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE5 (VARCHAR2, 240): Five user-definable custom columns for extensibility.
• SECURITY_GROUP_ID (NUMBER): Supports multi-tenant security models for CRM hosting.

Common Use Cases and Queries

BIX_SUM_INFO is typically queried in business intelligence and operational reporting contexts within Oracle EBS. The most common use cases include:

• Hourly Interaction Volume Reports: Aggregating NUMBER_OF_INTERACTIONS by HOUR and INTERACTION_CENTER_ID to monitor call volume patterns.
• Campaign Performance Analysis: Joining on CAMPAIGN_ID and LIST_ID to summarize VALUE (e.g., total revenue) by campaign across different time periods.
• Routing Classification Breakdown: Using INERACTION_CLASSIFICATION to analyze how interactions are distributed across different routing paths or skill groups.

A sample SQL query pattern for reporting purposes:

SELECT 
  TRUNC(s.HOUR) as DAY,
  s.INERACTION_CLASSIFICATION,
  SUM(s.NUMBER_OF_INTERACTIONS) as TOTAL_INTERACTIONS,
  SUM(s.VALUE) as TOTAL_VALUE
FROM BIX.BIX_SUM_INFO s
WHERE s.HOUR >= SYSDATE - 30
  AND s.INTERACTION_CENTER_ID = :p_center_id
GROUP BY TRUNC(s.HOUR), s.INERACTION_CLASSIFICATION
ORDER BY DAY, s.INERACTION_CLASSIFICATION;

Related Objects

Based on the ETRM metadata and typical Oracle EBS data modeling, BIX_SUM_INFO has the following documented relationships:

• Index BIX_SUM_INFO_N1: A nonunique index on column HOUR (tablespace APPS_TS_SUMMARY), used to accelerate time-based queries.
• Primary Key Constraint BIX_SUM_INFO_PK: Enforces uniqueness on column SUM_INFO_ID.
• Foreign Key to CCT_CALL_CENTER: The column INTERACTION_CENTER_ID references CCT_CALL_CENTER (as indicated in the metadata: "From CCT_CALL_CENTER").
• Dimensional References: CAMPAIGN_ID likely references IBY_CAMPAIGNS or equivalent; RESOURCE_ID references JTF_RS_RESOURCE_EXTNS; RESOURCE_GROUP_ID references group tables; SCRIPT_ID, PRODUCT_GROUP_ID, LIST_ID, and OUTCOME_ID link to respective dimension tables in the CRM data model.
• Potential Dependencies: The table may be referenced by summary management views or materialized views in the BIX schema for reporting, such as BIX_SUM_V or custom BI Publisher data sources.