Search Results bic_customer_summary_all




Overview
Explore in AI Chat

Overview

BIC.BIC_CUSTOMER_SUMMARY_ALL is a summary table belonging to the BIC — Customer Intelligence product, an obsolete module within Oracle E-Business Suite (documented in both 12.1.1 and 12.2.2). The table stores aggregated customer-level measures, expressly described in ETRM metadata as "Customer Intelligence Summary Information — for Org striped measures only." The reference to "Org striped" indicates that this table is multi-org enabled, with data partitioned by operating unit through the ORG_ID column, a standard pattern for system-level summary objects in EBS.

Because Customer Intelligence has been classified as obsolete, this table is best treated as a legacy artifact. It may still be queried for historical trending, but should not be extended for new development. From a Data Vault modeling heuristic, the metadata classifies this object as standalone — that is, it does not behave purely as a hub, link, or satellite. Its composite key carries both dimension-reference and measure-identifying semantics, so a pragmatic modeling suggestion would be to treat it as a fact-like or performance-satellite structure rather than a strict Data Vault hub.

Key Information Stored

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

  • PERIOD_START_DATE — the beginning of the aggregation period; part of the composite primary key.
  • CUSTOMER_ID — identifies the customer to which the measure applies; part of the composite primary key.
  • MEASURE_ID — identifies the measure definition being summarized; part of the composite primary key.
  • BUCKET_ID — resolves the summary into a defined bucket, with a foreign key to BIC_MEASURE_BUCKETS.
  • MEASURE_CODE — the human-readable code identifying the measure.
  • VALUE — the aggregated numeric measure value.
  • SCORE — a secondary scoring value associated with the summary record.
  • ORG_ID — the operating unit under which the summary is striped, enabling multi-org security.
  • SECURITY_GROUP_ID — references FND_SECURITY_GROUPS, supporting row-level security classification.

The surrogate-physical primary key is BIC_CUSTOMER_SUMMARY_ALL_PK, defined over (PERIOD_START_DATE, CUSTOMER_ID, MEASURE_ID). A unique index, BIC_CUSTOMER_SUMMARY_U1, is documented over the same three columns, confirming that this triplet is the business-key candidate for the table. The remaining columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE — are standard EBS audit and concurrent-program trail columns.

Common Use Cases and Queries

Typical use cases involve historical customer-measure trending and org-specific aggregation reporting. A representative query joining to the measure bucket and customer dimensions is:

  • Trending a specific measure for a customer across periods: SELECT PERIOD_START_DATE, VALUE, SCORE FROM BIC.BIC_CUSTOMER_SUMMARY_ALL WHERE CUSTOMER_ID = :cust AND MEASURE_CODE = :code ORDER BY PERIOD_START_DATE;
  • Org-restricted reporting: SELECT ORG_ID, SUM(VALUE) FROM BIC.BIC_CUSTOMER_SUMMARY_ALL WHERE ORG_ID = :org AND PERIOD_START_DATE = :p GROUP BY ORG_ID;
  • Bucket-level detail: join BUCKET_ID to BIC_MEASURE_BUCKETS to expose bucket names.
  • Security-aware extracts: join SECURITY_GROUP_ID to FND_SECURITY_GROUPS to reconcile the row-level security classification.

Because the object is obsolete, queries are generally read-only and oriented toward historical analysis or migration to a current analytics platform.

Related Objects

The documented foreign-key relationships identify the principal related objects and their join columns:

  • BIC_MEASURE_BUCKETS — referenced via BIC_CUSTOMER_SUMMARY_ALL.BUCKET_ID, providing the bucket dimension for each measure.
  • FND_SECURITY_GROUPS — referenced via BIC_CUSTOMER_SUMMARY_ALL.SECURITY_GROUP_ID, supplying the security-group classification used in row-level access logic.
  • The unique index BIC_CUSTOMER_SUMMARY_U1 and primary key BIC_CUSTOMER_SUMMARY_ALL_PK — structurally related indexes that constrain the business key.

The metadata identifies no additional inbound foreign keys or dependent tables, consistent with the "standalone" Data Vault classification. Any joins to customer or measure master data should therefore be derived from the CUSTOMER_ID, MEASURE_ID, and MEASURE_CODE columns rather than inferred from documented constraints.