Search Results csf_tds_pub




Overview
Explore in AI Chat

Overview

CSF_TDS_PUB is a public PL/SQL package in the Oracle E-Business Suite Applications (APPS) schema that supports the Transportation and Distribution Set (TDS) functionality within Oracle's supply chain and order management modules. Its primary business purpose is to manage the caching and computation of geographic routing data—specifically, distance calculations between spatial coordinates—used to determine transportation lanes, shipment routes, and delivery scheduling. The package encapsulates logic for purging stale cache entries, identifying routes that require recalculation, and performing point-to-point geographic distance computations. As a PUB-classified API, it is intended for invocation by other application modules, concurrent programs, or custom extensions rather than being an internal-only implementation detail. The header comment indicates the source was last modified in 2008, consistent with the long-lived codebase lineage carried forward into EBS 12.1.1 and 12.2.2.

Key Procedures and Functions

The package exposes three documented procedures:

  • PURGE_UNUSED_CACHE — Removes obsolete or unused entries from the transportation route cache. It accepts optional start and end date parameters to scope the purge window and returns standard concurrent program completion parameters (errbuf and retcode), indicating it is designed to be submitted as a concurrent program.
  • TDS_ROUTES_TO_BE_CALCULATED — Identifies routes that require distance or route recalculation for a given query, configuration, and user context. It returns a status code and message, allowing the caller to determine success or failure. This procedure likely stages candidate routes for subsequent processing.
  • GEO_DISTANCE — Computes the geographic distance between two coordinate pairs (x1, y1) and (x2, y2). It accepts a spatial reference identifier (srId) to resolve the coordinate system and returns the computed result. This is the function directly underlying the "geo_distance" search term.

Tables Accessed

The package reads and writes the following documented tables via APPS synonyms:

  • CSF_DC_QUERIES_B — Stores query definitions that drive which data is retrieved or calculated.
  • CSF_SPATIAL_CTRY_MAPPINGS — Maps countries to spatial reference systems, essential for resolving the srId used in GEO_DISTANCE.
  • CSF_TDS_ROUTE_CACHE — The primary cache of computed route and distance results, purged by PURGE_UNUSED_CACHE.
  • CSF_TDS_ROUTE_TO_BE_CALCULATED — A staging table listing routes identified by TDS_ROUTES_TO_BE_CALCULATED as pending recalculation.
  • DBMS_OUTPUT, DUAL, PLITBLM — Utility references for debugging output, single-row queries, and PL/SQL internal table manipulation.

Usage Notes

PURGE_UNUSED_CACHE is typically scheduled as a concurrent program (the constant G_CONC_PROGRAM_NAME = 'CSFTDPUB' confirms this) to periodically clean the route cache and prevent unbounded growth. TDS_ROUTES_TO_BE_CALCULATED and GEO_DISTANCE are invoked by downstream transportation planning processes, likely triggered from scheduling or shipping forms and batch jobs. Because ETRM metadata records zero packages referencing CSFTDS_PUB, it functions as a top-level API rather than a dependency of other PL/SQL units, making it suitable for direct invocation from custom code, Oracle Forms personalizations, or OAF extensions in 12.1.1 and 12.2.2 environments.