Search Results csi_ctr_derived_filters_u01

Overview

CSI.CSI_COUNTER_DERIVED_FILTERS is a transactional configuration table in the Oracle E-Business Suite Customer Intelligence (CSI) schema. It stores the filter criteria applied when a group-based formula counter calculates its readings. A group-based formula counter may be of type Average, Sum, or Count, and this table defines predicate rows that restrict which underlying readings participate in the aggregation. For example, a user may wish to exclude all readings having a value of 1 from the calculation of a Sum group-based counter; such exclusion rules are persisted here as filter expressions rather than being hard-coded into the counter definition.

The table is owned by CSI and carries a VALID status. It resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10 and is registered in FND Design Data as CSI.CSI_COUNTER_DERIVED_FILTERS. Under the heuristic Data Vault classification derived from its foreign-key structure, the object is modeled as a standalone entity rather than a hub, link, or satellite. Its single referenced relationship, SECURITY_GROUP_ID to FND_SECURITY_GROUPS, reflects integration with the multi-tenant security model, not a warehouse-style dimensional association.

Key Information Stored

Each row represents one filter element within a logical expression that governs counter derivation. The most significant columns include:

• COUNTER_DERIVED_FILTER_ID — Surrogate primary key, enforced by the primary key constraint CSI_COUNTER_DERIVED_FILTERS_PK.
• COUNTER_ID — Identifies the owning counter to which the filter belongs.
• SEQ_NO — Sequence number preserving evaluation order of the filter elements.
• COUNTER_PROPERTY_ID — The counter property being tested by the filter condition.
• RELATIONAL_OPERATOR — The comparison operator applied to the property value.
• RIGHT_VALUE — The derived property value used on the right-hand side of the comparison.
• LEFT_PARENT and RIGHT_PARENT — Delimiters that establish expression grouping and precedence.
• LOGICAL_OPERATOR — The connective (for example, AND/OR) joining adjacent filter expressions.
• START_DATE_ACTIVE and END_DATE_ACTIVE — Effective dating window for the filter criteria.
• OBJECT_VERSION_NUMBER — Optimistic locking column for concurrent updates.
• SECURITY_GROUP_ID — Security grouping, with a documented foreign key to FND_SECURITY_GROUPS.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — Standard descriptive flexfield columns for customer-defined extensions.
• MIGRATED_FLAG — Indicator supporting data migration tracking.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns.

The unique index CSI_CTR_DERIVED_FILTERS_U01 is defined on COUNTER_DERIVED_FILTER_ID in the APPS_TS_TX_IDX tablespace. Because it duplicates the primary key column, it functions as a unique constraint rather than a distinct business-key candidate.

Common Use Cases and Queries

Typical usage centers on inspecting, replicating, or troubleshooting the exclusion logic behind derived counters. Common query patterns include:

• Retrieving all filter rows for a given counter ordered by evaluation sequence: SELECT * FROM CSI.CSI_COUNTER_DERIVED_FILTERS WHERE COUNTER_ID = :counter_id ORDER BY SEQ_NO;
• Listing active filters against the current date: SELECT * FROM CSI.CSI_COUNTER_DERIVED_FILTERS WHERE TRUNC(SYSDATE) BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, TRUNC(SYSDATE));
• Reconstructing the logical expression from LOGICAL_OPERATOR, LEFT_PARENT, RIGHT_PARENT, and the operator/value pairs for documentation or audit.
• Identifying filters scoped to a security group by joining SECURITY_GROUP_ID to FND_SECURITY_GROUPS.
• Auditing configuration changes through the WHO columns and OBJECT_VERSION_NUMBER.

Reports that validate counter configuration or explain why a given reading was excluded from an aggregate output frequently join this table to the counter definition tables to present human-readable criteria.

Related Objects

The following objects are the most significant relationships for this table:

• CSI_COUNTERS (or the corresponding counter definition entity) — Joined on COUNTER_ID, providing the counter header that owns each filter.
• CSI_COUNTER_PROPERTIES — Joined on COUNTER_PROPERTY_ID, resolving the property tested by the filter.
• FND_SECURITY_GROUPS — The documented foreign-key target of SECURITY_GROUP_ID.
• CSI_COUNTER_DERIVED_FILTERS_PK — The primary key constraint enforcing uniqueness of COUNTER_DERIVED_FILTER_ID.
• CSI_CTR_DERIVED_FILTERS_U01 — The unique index on COUNTER_DERIVED_FILTER_ID in APPS_TS_TX_IDX.
• Counter/reading fact tables within CSI — Consumed indirectly, since filter rows determine which readings contribute to group-based Average, Sum, or Count calculations.

Because the object is classified as standalone, no warehouse-style hub or link dependencies are suggested; integration is limited to the documented security-group reference and functional joins to counter configuration entities.