Search Results bic_customer_summary_u1

Overview

The BIC.BIC_CUSTOMER_SUMMARY_ALL table is a Customer Intelligence summary object within the Oracle EBS Business Intelligence/CRM (BIC) product family. It stores aggregated, org-striped measures such as satisfaction and loyalty scores calculated for customers across discrete time periods. The table resides in the APPS_TS_SUMMARY tablespace and is registered under FND Design Data as BIC.BIC_CUSTOMER_SUMMARY_ALL with a status of VALID. Rows are populated exclusively by the concurrent program Extract Customer Summary, which is designed for organization-striped measures only.

From a heuristic Data Vault modeling perspective, the metadata classifies this object as standalone. In practice, however, the structure exhibits satellite-like characteristics: it carries descriptive measure values (VALUE, SCORE) keyed by a composite business key of PERIOD_START_DATE, CUSTOMER_ID, and MEASURE_ID. A modeler could reasonably treat the customer/measure/period combination as a link to the underlying hub entities (customer and measure), with this table acting as the effectivity satellite capturing time-sliced measurements.

Key Information Stored

The table contains 18 documented columns. The most operationally significant are the following:

• PERIOD_START_DATE — the beginning of the measurement period; part of the composite unique key and primary key.
• CUSTOMER_ID — foreign key to HZ_PARTIES, identifying the customer entity; part of the composite unique key.
• MEASURE_ID — identifier for the measure (for example Satisfaction or Loyalty); part of the composite unique key.
• MEASURE_CODE — human-readable measure code, indexed by the non-unique BIC_CUSTOMER_SUMMARY_N1 index for fast filtering.
• VALUE — the computed value for the associated bucket.
• SCORE — an additional stored score for the measure.
• BUCKET_ID — identifies the measure bucket; foreign key to BIC_MEASURE_BUCKETS.
• ORG_ID — the operating unit/organization identifier enabling multi-org striping.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, governing row-level security access.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — concurrent program Who columns recording the request that last populated the row.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS Who columns.

The surrogate primary key is BIC_CUSTOMER_SUMMARY_ALL_PK (PERIOD_START_DATE, CUSTOMER_ID, MEASURE_ID). The business-key candidate is the unique index BIC_CUSTOMER_SUMMARY_U1, which covers the identical three columns and is the index most commonly referenced by users searching by name.

Common Use Cases and Queries

Typical use cases include customer satisfaction trending, loyalty score analysis, and period-over-period measure comparison for a given operating unit.

Retrieve the latest satisfaction measure for a customer:

SELECT period_start_date, value, score
FROM   bic.bic_customer_summary_all
WHERE  customer_id = :p_customer_id
AND    measure_code = 'SATISFACTION'
AND    org_id = :p_org_id
ORDER BY period_start_date DESC;

Aggregate average score by measure code across a period:

SELECT measure_code, AVG(score)
FROM   bic.bic_customer_summary_all
WHERE  period_start_date BETWEEN :p_from AND :p_to
GROUP BY measure_code;

Because the extract runs on a scheduled basis, reporting queries should filter on ORG_ID to honor multi-org access and leverage the BIC_CUSTOMER_SUMMARY_U1 unique index when joining by period, customer, and measure.

Related Objects

• HZ_PARTIES — joined via CUSTOMER_ID = PARTY_ID to resolve customer names and attributes.
• BIC.BIC_MEASURE_BUCKETS — referenced by BUCKET_ID; defines measured buckets.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID; enforces row-level security.
• FND_CONCURRENT_REQUESTS — joined via REQUEST_ID to trace the populating concurrent request.
• FND_CONCURRENT_PROGRAMS — joined via PROGRAM_ID; identifies the Extract Customer Summary program.
• FND_USER — joined via CREATED_BY and LAST_UPDATED_BY for audit attribution.
• BIC_MEASURES and related measure definition tables — logically joined via MEASURE_ID / MEASURE_CODE.