Search Results csf_tds_route_cache

Overview

CSF_TDS_ROUTE_CACHE is a table owned by the CSF schema within the Field Service module of Oracle E-Business Suite (validated across 12.1.1 and 12.2.2). Its purpose is to persist pre-computed routing results so that repeated route calculations between a given origin ("from") segment and destination ("to") segment can be reused rather than regenerated. In Field Service, technicians and dispatch planners frequently request the same origin-to-destination permutations; caching route geometry, time, distance, and cost dramatically reduces the load on the external routing or geocoding engine.

The table carries a security-group identifier, tying each cached route to a specific FND security group, which supports multi-tenant or operating-unit-level separation of routing data. In the supplied metadata, the heuristic Data Vault classification is "standalone," suggesting the object behaves as a self-contained reference or lookup structure rather than participating in a classic hub-link-satellite chain. It can be treated as an independent cache record keyed by its own surrogate identifier.

Key Information Stored

The table contains 27 documented columns. The most significant are summarized below. Because the metadata lists only physical indexes (three SYS_IL LOB/interim indexes with no column list), the surrogate key and business-key candidates are inferred from column naming conventions rather than from a confirmed unique constraint.

• ROUTE_CACHE_ID — the surrogate primary key uniquely identifying each cached route entry.
• FROM_SEGMENT_ID / TO_SEGMENT_ID — the business identifiers of the origin and destination road segments, forming the natural composite key for a cache lookup.
• SEGMENT_FROM, SIDE_FROM, SPOT_FROM, SEGMENT_TO, SIDE_TO, SPOT_TO — addressable points on the network, capturing segment, side, and spot locators at each endpoint.
• FROM_SEGMENT_SIDE, TO_SEGMENT_SIDE, FROM_SEGMENT_POSITION, TO_SEGMENT_POSITION — finer positioning detail used to disambiguate locations on the same segment.
• RESULTTIME, RESULTDISTANCE — the computed travel time and distance returned by the routing engine.
• TIME, DISTANCE, COST — cost-model attributes for the cached route.
• ROUTE, GEOMETRY — the persisted path geometry and route definition, stored as large objects.
• DATETIME, HITCOUNT, RAMHITCOUNT — cache-freshness and utilization metrics; HITCOUNT and RAMHITCOUNT indicate how often the entry was served, useful for tuning.
• CONFIG — routing configuration context that qualifies the cached result.
• CREATION_DATE, LAST_UPDATE_DATE — audit columns.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, scoping the entry to a security group.

Common Use Cases and Queries

The primary use case is cache lookup during route calculation. A query on the endpoint identifiers returns the cached result when the configuration and security group match:

• SELECT route_cache_id, resulttime, resultdistance, route FROM csf_tds_route_cache WHERE from_segment_id = :p_from AND to_segment_id = :p_to AND security_group_id = :p_group;
• Hit-rate reporting: SELECT hitcount, ramhitcount, datetime FROM csf_tds_route_cache ORDER BY hitcount DESC;
• Cache maintenance: DELETE stale rows based on DATETIME or LAST_UPDATE_DATE thresholds.
• Cost/distance aggregation across the cached network for dispatch planning and travel-time analytics.

Related Objects

Relationship analysis is limited to one documented foreign key. The most relevant joined objects are:

• FND_SECURITY_GROUPS — joined on CSF_TDS_ROUTE_CACHE.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID, enforcing data segregation.
• CSF route/geocode engine tables — the source routing service that populates and consumes cache rows via FROM_SEGMENT_ID and TO_SEGMENT_ID.
• Field Service dispatch and scheduling objects — task and trip tables that reference cached routes when planning technician movement.
• CSF_TDS_ROUTE_CACHE's LOB segments (SYS_IL... indexes) — supporting storage for ROUTE and GEOMETRY.