Search Results csf_tds_segments_pk

Overview

CSF_TDS_SEGMENTS is a table in the CSF (Field Service) product schema of Oracle E-Business Suite, valid in releases 12.1.1 and 12.2.2. Per the documented description, it "defines a connection between two nodes," which positions it as the core representation of a traversable graph edge within the Oracle Field Service routing and scheduling data model. The TDS prefix refers to the Transportation/Telematics Data Store structures used to model road networks, travel intervals, and node connections that underpin dispatch, service-area, and technician-routing calculations.

Each row represents a single segment — a directional link between adjacent network nodes — and carries an extensive set of attributes describing physical characteristics, allowed access classes, and travel parameters for that link. The heuristic Data Vault classification for this table is hub-leaning, driven by its single-column surrogate primary key and its role as the referenced parent of many dependent child tables. In a Data Vault model, CSF_TDS_SEGMENTS would therefore best be modeled as a hub, with dependent descriptive attributes distributed across satellites and its child relationships expressed as links.

Key Information Stored

The table is documented with 28 columns in the 12.2.2 physical schema. Its surrogate primary key is SEGMENT_ID, enforced by the constraint CSF_TDS_SEGMENTS_PK. This identifier is the join key used by virtually every dependent child table. No alternate unique business key is documented in the supplied metadata, so the surrogate remains the authoritative identifier.

The most operationally significant columns include:

• SEGMENT_ID — surrogate primary key uniquely identifying each segment.
• FUNCTIONAL_CLASS — categorizes the segment's functional role within the network.
• LENGTH — physical length of the segment, used in distance and travel-time calculation.
• AVERAGE_SPEED — average traversal speed applied to the segment for time estimation.
• LANE_CATEGORY — lane classification affecting capacity and routing eligibility.
• ONEWAY_DIRECTION — indicates directional restrictions on traversal.
• ROAD_CONDITION, SPECIAL_RESTRICTIONS — qualitative attributes affecting usability.
• TOLL_ROAD, FERRY_TYPE, DIVIDED_ROAD_ELEMENT, DIVIDER_TYPE — infrastructure characteristics influencing cost and access decisions.
• Access flags ACCESS_CARS, ACCESS_TRUCK, ACCESS_EMERGENCY, ACCESS_DELIVERY, ACCESS_BUS, and the parallel pedestrian/taxi/bicycle/carpool/through-traffic flags — restrict which vehicle classes may use the segment.
• TILE_ID (foreign key to CSF_TDS_TILES), INTERVAL_ID (foreign key to CSF_TDS_INTERVALS), and SECURITY_GROUP_ID (foreign key to FND_SECURITY_GROUPS) — establish the segment's parent tile, its interval association, and its multi-org/security partition.

Common Use Cases and Queries

Typical uses center on routing analysis, access-class feasibility checks, and distance or travel-time estimation. A segment-by-segment reporting query joining parent tiles and intervals resembles:

SELECT s.segment_id, s.length, s.average_speed,
       s.lane_category, s.toll_road, t.tile_id
  FROM csf.csf_tds_segments s,
       csf.csf_tds_tiles    t
 WHERE s.tile_id = t.tile_id
   AND s.security_group_id = :p_security_group;

A second common pattern detects segments with restricted truck access within a given interval:

SELECT s.segment_id, s.access_truck, s.special_restrictions
  FROM csf.csf_tds_segments s
 WHERE s.interval_id = :p_interval
   AND s.access_truck = 'N';

Reporting use cases include service-area coverage analysis (aggregating segment lengths by tile), route-feasibility validation against access flags, and cost analysis driven by TOLL_ROAD and FERRY_TYPE. Because several BI materialized views (BIM_SGMT_*) reference SEGMENT_ID, segment data also flows into broader analytical extracts.

Related Objects

CSF_TDS_SEGMENTS participates in a dense referential web. Its own foreign keys reference CSF_TDS_TILES (via TILE_ID), CSF_TDS_INTERVALS (via INTERVAL_ID), and FND_SECURITY_GROUPS (via SECURITY_GROUP_ID). The most significant dependent tables that reference it by SEGMENT_ID are:

• CSF_TDS_SEGM_NODES — maps segment endpoints back to network nodes, complementing the tile/segment relationship.
• CSF_TDS_COND_SEGS — conditional segment rules keyed by SEGMENT_ID.
• CSF_TDS_RDBLCK_SGMNTS — road-block restrictions applied to specific segments.
• QP_SEGMENTS_B and QP_SEGMENTS_TL — Oracle Advanced Pricing segment definitions and translations.
• QP_QUALIFIERS, QP_ATTRIBUTE_SOURCING, QP_PATTERNS — pricing qualifier and sourcing logic tied to segments.
• GL_PLCY_SEG — accounting policy segment mapping.
• Analytical views BIM_SGMT_VAL_F_MV, BIM_SGMT_ACT_B_MV, and the related BIM_SGMT_ACT_*_MV materialized views, which surface segment metrics for reporting.

Any query or interface touching the Field Service routing model should treat SEGMENT_ID as the canonical join key and observe SECURITY_GROUP_ID partitioning to respect multi-org access controls.