Search Results csf_lf_roadsegments_pk

Overview

CSF_LF_ROADSEGMENTS is a core transactional and reference table within the CSF (Field Service) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. The table stores road segments together with their navigable attributes, forming the geographic backbone used by Field Service location intelligence and routing functionality. Each row represents a single road segment that can be navigated, associated with a set of names, places, posts, and points of interest defined in dependent child tables.

The table is owned by the CSF schema and is documented as VALID in the ETRM repository. Based on the heuristic Data Vault classification mined from its foreign key structure, this table behaves as a hub — a central entity store whose primary key is referenced by multiple satellite-like child tables. This classification is a modeling suggestion only; in native EBS terms the object is a standard relational master table. Its single foreign key to FND_SECURITY_GROUPS confirms that access to road segment data is controlled through the standard EBS security group mechanism.

Key Information Stored

The documented physical schema for 12.2.2 lists five columns, together with two system-generated unique indexes that serve as business-key candidates. The most important columns are:

• ROADSEGMENT_ID — the surrogate primary key (CSF_LF_ROADSEGMENTS_PK). This is the value propagated to all child tables and is the join key for the entire road-segment cluster.
• ROADSEGMENT_LENGTH — the navigable length of the segment, used in distance calculations, routing estimates, and travel-time derivation.
• TIME_ZONE — the time zone in which the segment resides, supporting scheduling and elapsed-time calculations across geographies.
• ROADSEGMENT_GEOMETRY — the spatial geometry (SDO geometry) of the segment, enabling map rendering, spatial queries, and proximity analysis.
• SECURITY_GROUP_ID — the foreign key to FND_SECURITY_GROUPS, enforcing multi-org/security-group based data visibility.

The two documented unique indexes, SYS_IL0000083003C00010$$ and SYS_IL0000083003C00011$$, are internal indexes and do not expose meaningful business-key names; ROADSEGMENT_ID remains the definitive unique identifier. No additional descriptive name column is stored directly on the hub — human-readable segment names are maintained in the child table CSF_LF_ROADSEGM_NAMES.

Common Use Cases and Queries

Typical usage centers on geographic routing, territory assignment, and field engineer dispatch. Common query patterns include:

• Joining road segments to names for readable reporting: SELECT r.ROADSEGMENT_ID, n.ROADSEGMENT_NAME FROM CSF_LF_ROADSEGMENTS r, CSF_LF_ROADSEGM_NAMES n WHERE r.ROADSEGMENT_ID = n.ROADSEGMENT_ID.
• Aggregating segment length by time zone to estimate regional travel coverage.
• Spatial proximity queries using ROADSEGMENT_GEOMETRY (SDO) to find segments near a service location.
• Filtering by SECURITY_GROUP_ID to honor data access restrictions in custom reports.
• Identifying points of interest or blocks associated with a segment by joining to CSF_LF_POIS and CSF_LF_BLOCKS.

Related Objects

The table participates in a hub-and-child relationship cluster. The primary child tables referencing CSF_LF_ROADSEGMENTS.ROADSEGMENT_ID are:

• CSF_LF_BLOCKS — blocks that lie along or are assigned to a road segment.
• CSF_LF_POIS — points of interest mapped to a segment.
• CSF_LF_ROADSEGM_NAMES — the human-readable names for each segment.
• CSF_LF_ROADSEGM_PLACES — place assignments associated with a segment.
• CSF_LF_ROADSEGM_POSTS — postal or boundary posts defined along a segment.
• FND_SECURITY_GROUPS — the parent security group referenced by SECURITY_GROUP_ID.