Search Results bix_sum_agt_prd_pk

Overview

The BIX_SUM_AGT_PRD table is a denormalized summary (aggregate) table within the BIX — Interaction Center Intelligence product of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. Its documented purpose is to summarize data from BIX_INTERACTIONS down to the day level, organized along four analytic dimensions: time, agent, campaign, and interaction classification. This design makes it the primary fact source for agent- and product-oriented interaction center reporting, where high volumes of transactional interaction rows would otherwise make operational reporting prohibitively expensive.

The ETRM metadata classifies BIX_SUM_AGT_PRD heuristically as standalone under the Data Vault model, with no parent hub or link dependencies mined from its foreign key structure. As a modeling suggestion, this table is therefore best treated as an aggregate fact or summary satellite rather than a normalized hub, link, or detail satellite. It exists to serve query performance and pre-computed measures rather than to enforce transactional integrity.

Key Information Stored

The table contains 22 documented columns. The most significant are:

• SUM_AGT_PRD_ID — surrogate primary key, defined by the constraint BIX_SUM_AGT_PRD_PK. This is a system-generated identifier only and carries no business meaning.
• HOUR, RESOURCE_ID, CAMPAIGN_ID, INTERACTION_CLASSIFICATION — the documented unique-key candidate columns forming BIX_SUM_AGT_PRD_UK. Together they define the grain of the table: one row per hour, agent (resource), campaign, and classification combination, which is what makes daily and sub-daily trend analysis possible.
• RESOURCE_ID — the agent resource identifier, the atomic unit of agent-level performance reporting.
• CAMPAIGN_ID — links summarized facts to the marketing or telemarketing campaign that generated the interactions.
• INTERACTION_TYPE and INTERACTION_CLASSIFICATION — categorize the nature of the interaction for breakdowns by contact reason or channel.
• NUMBER_OF_INTERACTIONS, TRANSACTIONS, SALES_QUOTES, LEADS_GENERATED, SALES_ORDERS — the primary count-based measures.
• SALES_REVENUE, AMOUNT_COLLECTED, PROMISE_TO_PAY — monetary measures supporting revenue and collections reporting.
• PRODUCT_GROUP_ID and SECURITY_GROUP_ID — foreign keys to FND_PRODUCT_GROUPS and FND_SECURITY_GROUPS respectively, providing multi-org and data-security filtering.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — flexfields for customer-specific extension of the summary.

Common Use Cases and Queries

Typical reporting scenarios include agent productivity dashboards, campaign return-on-investment analysis, and interaction volume trending. Because the table is pre-aggregated by agent, campaign, and hour, queries are lightweight relative to BIX_INTERACTIONS.

Agent performance for a period:

• SELECT RESOURCE_ID, SUM(NUMBER_OF_INTERACTIONS) interactions, SUM(SALES_REVENUE) revenue FROM BIX_SUM_AGT_PRD WHERE SECURITY_GROUP_ID = :org_id GROUP BY RESOURCE_ID ORDER BY revenue DESC;

Campaign effectiveness by hour of day:

• SELECT CAMPAIGN_ID, HOUR, SUM(SALES_ORDERS), SUM(LEADS_GENERATED) FROM BIX_SUM_AGT_PRD WHERE CAMPAIGN_ID = :campaign_id GROUP BY CAMPAIGN_ID, HOUR;

Classification breakdown:

• SELECT INTERACTION_CLASSIFICATION, INTERACTION_TYPE, SUM(TRANSACTIONS), SUM(AMOUNT_COLLECTED) FROM BIX_SUM_AGT_PRD GROUP BY INTERACTION_CLASSIFICATION, INTERACTION_TYPE;

Related Objects

• BIX_INTERACTIONS — the source transactional table from which this summary is derived; the primary dependency.
• FND_PRODUCT_GROUPS — referenced via PRODUCT_GROUP_ID; governs operating-unit context.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID; enforces row-level security.
• BIX_SUM_AGT_PRD_PK / BIX_SUM_AGT_PRD_UK — the primary and unique constraints enforcing grain.
• Other BIX summary tables (for example, sibling daily summary objects) that share the same dimensional pattern for cross-fact reporting.