Search Results bic_measure_buckets_u1

Overview

BIC.BIC_MEASURE_BUCKETS is a seed-data configuration table within the Oracle E-Business Suite Customer Intelligence (BIC) schema. It stores the bucket group definitions used by the Loyalty and Satisfaction measures in Oracle EBS releases 12.1.1 and 12.2.2. Each measure is partitioned into five seeded groups, and the user may subsequently add or delete groups to reflect business-specific scoring models. The time unit applied to the LOW_VALUE and HIGH_VALUE range columns is inherited from the parent measure (Satisfaction or Loyalty) as recorded in BIC_MEASURES_ALL through the TIME_UNIT_CODE attribute.

The table resides in the APPS_TS_SEED tablespace with PCTFREE 10, consistent with its role as seeded configuration rather than transactional data. From a Data Vault modeling perspective, the mined foreign-key structure classifies this object as hub-leaning: BUCKET_ID functions as a durable business key that anchors dependent descriptive and transactional satellites, while the measure and security group references act as connecting links to their respective hubs.

Key Information Stored

• BUCKET_ID — Surrogate primary key (BIC_MEASURE_BUCKETS_PK) and the column enforced by unique index BIC_MEASURE_BUCKETS_U1. It is the business-key candidate most commonly joined against by downstream tables.
• MEASURE_ID — Foreign key to BIC_MEASURES_ALL; identifies the parent measure whose time unit governs the range interpretation. Indexed non-uniquely by BIC_MEASURE_BUCKETS_N1.
• BUCKET_NAME — VARCHAR2(60) descriptive label for the group.
• BUCKET_POINTS — Points awarded to a customer who falls within the bucket, driving loyalty scoring.
• LOW_VALUE / HIGH_VALUE — Numeric range boundaries applied to measures where LOV_FLAG is 'N', using the parent measure's time unit.
• OPERATOR — VARCHAR2(30) holding 'AND' or 'OR' for measures that employ a list of values.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enforcing row-level security.
• Standard WHO columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) capture audit lineage.

Common Use Cases and Queries

Typical reporting joins buckets to measures and to customer-level summary results to evaluate loyalty tier distribution.

SELECT b.bucket_name, b.bucket_points, b.low_value, b.high_value
FROM   bic.bic_measure_buckets b
WHERE  b.measure_id = :p_measure_id
ORDER  BY b.low_value;

Security-filtered queries should constrain on SECURITY_GROUP_ID to honor the active responsibility. Bucket ranges are also frequently combined with BIC_CUSTOMER_SUMMARY_ALL to score active customers, and with BIC_BUCKET_VALUES for list-of-values measure evaluations.

Related Objects

• BIC.BIC_MEASURES_ALL — parent measure definition (join on MEASURE_ID).
• FND_SECURITY_GROUPS — row-level security anchor (join on SECURITY_GROUP_ID).
• BIC.BIC_BUCKET_VALUES — detail values per bucket (join on BUCKET_ID).
• BIC.BIC_CUSTOMER_SUMMARY_ALL — customer scoring summary (join on BUCKET_ID).
• BIC.BIC_PARTY_SUMMARY — party-level aggregation (join on BUCKET_ID).
• BIS.BIS_BUCKET and BIS.BIS_BUCKET_TL — backwards-compatibility bucket views/tables.
• BIS.BIS_BUCKET_CUSTOMIZATIONS — user bucket tailoring records.