Search Results csf_lf_roadsegm_posts_pk

Overview

CSF_LF_ROADSEGM_POSTS is a Field Service (CSF) reference table within the Oracle E-Business Suite 12.1.1 and 12.2.2 environments. Its stated purpose is to define the relationship between road segments and postcodes, supporting the Oracle Field Service/Logistics Foundation geographic data model used for territory assignment, dispatch planning, and service coverage validation. In practice, this table functions as an intersection or mapping entity that associates a road segment record with one or more postal codes, enabling address normalization and geographic routing logic in field service operations.

From a Data Vault modeling perspective, the heuristic classification for this table is a link. This classification derives from the table's structure: it contains no descriptive business attributes beyond the relationship itself and the side qualifier, and it carries two foreign keys pointing to CSF_LF_POSTCODES and CSF_LF_ROADSEGMENTS respectively. A link classification is offered here as a modeling suggestion rather than a documented Oracle designation, and is inferred strictly from the FK topology mined from the ETRM metadata.

Key Information Stored

The table is documented with five physical columns in the 12.2.2 schema under the CSF owner. The most significant columns are:

• ROADSEGMENT_POSTCODE_ID — The surrogate primary key, enforced by the constraint CSF_LF_ROADSEGM_POSTS_PK. This is a system-generated unique identifier for each association row, not a business key.
• ROADSEGMENT_ID — Foreign key referencing CSF_LF_ROADSEGMENTS. Identifies the road segment participating in the relationship.
• POSTAL_CODE_ID — Foreign key referencing CSF_LF_POSTCODES. Identifies the postcode associated with the road segment.
• POSTCODE_SIDE — A qualifier column that likely denotes which side of the road segment the postcode falls on (for example, odd/even or left/right side addressing conventions). No enumerated value list is documented in the ETRM metadata.
• SECURITY_GROUP_ID — Foreign key referencing FND_SECURITY_GROUPS, used for multi-org or row-level security partitioning in the Oracle EBS security model.

The combination of ROADSEGMENT_ID and POSTAL_CODE_ID (and possibly POSTCODE_SIDE) is the natural business-key candidate, since no alternate unique index other than the primary key is documented. The surrogate ROADSEGMENT_POSTCODE_ID exists solely to provide a stable technical identifier for each link row.

Common Use Cases and Queries

Typical use cases include geographic validation of service addresses, deriving the set of postcodes served by a given road segment, and resolving a postcode to the road segments that traverse it for routing or sla calculations. A representative query retrieving all postcodes for a road segment is:

• SELECT p.postal_code FROM csf_lf_roadsegm_posts rp, csf_lf_postcodes p WHERE rp.postal_code_id = p.postal_code_id AND rp.roadsegment_id = :roadsegment_id;
• Reporting on coverage density by joining CSF_LF_ROADSEGM_POSTS to CSF_LF_ROADSEGMENTS and grouping by postcode.
• Security-filtered extracts joining FND_SECURITY_GROUPS via SECURITY_GROUP_ID to restrict results to the caller's permitted operating unit or group.
• Data quality audits identifying road segments with no associated postcodes or postcodes mapped to multiple segments.

Related Objects

The following objects are most significant in relation to this table, based on the documented foreign key relationships:

• CSF_LF_ROADSEGMENTS — joined via ROADSEGMENT_ID; the parent road segment definition.
• CSF_LF_POSTCODES — joined via POSTAL_CODE_ID; the parent postcode definition.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID; governs row-level access.
• CSF_LF_ROADSEGM_POSTS_PK — the primary key constraint on ROADSEGMENT_POSTCODE_ID.
• Any Field Service territory or dispatch configuration tables that consume road-segment/postcode mappings indirectly for scheduling and coverage logic.