Search Results csi_counter_readings_u01

Overview

CSI.CSI_COUNTER_READINGS is a transactional table within the Oracle E-Business Suite Customer and Service Intelligence (CSI) schema. It stores the historical readings captured against counter instances and meter instances. Counters and meters are used throughout EBS for asset utilization, contract entitlement consumption, service usage, and warranty tracking. Each row represents a single reading event, storing the instantaneous counter reading along with derived values such as net reading and life-to-date reading, and metadata concerning resets, adjustments, rollovers and disabled readings.

The table is physically stored in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10. Its unique index, CSI_COUNTER_READINGS_U01, resides in APPS_TS_TX_IDX and enforces uniqueness on COUNTER_VALUE_ID. Two non-unique indexes, CSI_COUNTER_READINGS_N01 and CSI_COUNTER_READINGS_N02, support access paths keyed on COUNTER_ID/VALUE_TIMESTAMP and SOURCE_COUNTER_VALUE_ID respectively.

From a heuristic Data Vault modeling perspective, this object is best classified as a satellite. It captures time-variant, descriptive measurements attached to a counter (which behaves as a hub-like business entity), and it does not itself act as a hub or a link. Analysts adopting a Data Vault representation would typically model CSI_COUNTER_READINGS as a satellite riding on a counter hub keyed by COUNTER_ID, with VALUE_TIMESTAMP serving as the load date. The metadata record classification flags this object as standalone, indicating no outgoing foreign key relationships other than to FND_SECURITY_GROUPS via SECURITY_GROUP_ID.

Key Information Stored

The table's surrogate primary key is COUNTER_VALUE_ID, which is an internally generated number. This column is also the single column underlying the unique index CSI_COUNTER_READINGS_U01, making it simultaneously the physical primary key and the operational business-key candidate for uniqueness resolution. Notable columns include:

• COUNTER_ID — identifies the counter instance to which the reading belongs. It forms the leading column of the N01 index and is the natural join key back to the counter definition.
• VALUE_TIMESTAMP — capture date of the reading; paired with COUNTER_ID in the N01 index to support time-ordered retrieval of a counter's readings.
• COUNTER_READING — the raw reading value recorded at the time of capture.
• NET_READING — reading net of adjustments or resets, typically used for billing and usage calculations.
• LIFE_TO_DATE_READING — cumulative reading since the counter's inception, unaffected by periodic resets.
• RESET_MODE and RESET_REASON — describe how and why the counter was reset, essential for reconciliation.
• ADJUSTMENT_TYPE and ADJUSTMENT_READING — capture manual or system adjustments applied to the reading.
• AUTOMATIC_ROLLOVER_FLAG and INCLUDE_TARGET_RESETS — control flags governing rollover behavior across period boundaries.
• SOURCE_COUNTER_VALUE_ID — references a prior or originating reading, and is the indexed column of N02 for lineage queries.
• TRANSACTION_ID — associates the reading with the originating transaction.
• DISABLED_FLAG — identifies readings that have been logically disabled.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-org style data segregation.
• OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY — standard concurrent WHO and optimistic locking columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1..30 — the flexfield descriptor columns enabling extensible, customer-defined attributes.
• MIGRATED_FLAG, SOURCE_CODE and SOURCE_LINE_ID — identify readings imported via migration or external interfaces.

Common Use Cases and Queries

Typical use cases include retrieving the latest reading per counter, trending usage over time, reconciling net versus life-to-date readings, and auditing resets or adjustments. A representative query for the latest reading per counter is:

• SELECT counter_id, MAX(value_timestamp) FROM csi.csi_counter_readings GROUP BY counter_id;
• SELECT * FROM csi.csi_counter_readings WHERE counter_id = :p_counter_id ORDER BY value_timestamp DESC;
• SELECT * FROM csi.csi_counter_readings WHERE source_counter_value_id = :p_source_id; -- lineage lookup via N02
• SELECT net_reading, life_to_date_reading, counter_reading FROM csi.csi_counter_readings WHERE counter_value_id = :p_id;
• SELECT * FROM csi.csi_counter_readings WHERE disabled_flag = 'Y' AND adjustment_type IS NOT NULL;

Reporting scenarios frequently join to counter definition tables via COUNTER_ID to attach counter names and units, and filter by VALUE_TIMESTAMP ranges for period-based usage reports.

Related Objects

Although the metadata classifies this object as standalone, several logical relationships exist:

• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID; the only documented foreign key.
• CSI_COUNTERS (counter definition) — joined on COUNTER_ID via the N01 index to resolve counter metadata.
• CSI_COUNTER_READINGS itself, self-referenced through SOURCE_COUNTER_VALUE_ID (index N02) for reading lineage.
• CSI_COUNTER_VALUES / counter value APIs — the public PL/SQL counter API packages typically insert into this table during reading capture.
• CSI_TRANSACTIONS — referenced semantically through TRANSACTION_ID to tie readings to originating transactions.
• CSI_ITEM_INSTANCES — meter and counter instances are frequently associated with item instances, providing the broader asset context.
• FND_SECURITY_GROUPS and standard WHO columns also link to FND_USER for audit trails.