1 PACKAGE csf_locus_pub AS
2 /*$Header: CSFPLCSS.pls 120.7.12010000.1 2008/07/28 07:37:13 appldev ship $*/
3 g_ret_locus_success CONSTANT NUMBER := 0;
4 g_ret_locus_invalid_locus CONSTANT NUMBER := -1;
5 g_ret_locus_invalid_geometry CONSTANT NUMBER := -2;
6
7 /**
8 * The API reads the locus and returns the Geometry (SDO_GEOMETRY),
9 * Segment ID, Offset and Side of the Road.
10 * <br>
11 * The API has a PRAGMA Restriction to avoid reading the database or
12 * modifying it.
13 *
14 * @param p_api_version API Version (1.0)
15 * @param x_return_status Return Status of the Procedure.
16 * @param x_msg_count Number of Messages in the Stack.
17 * @param x_msg_data Stack of Error Messages.
18 * @param p_locus Locus Object (SDO_GEOMETRY)
19 * @param x_geom Geometry of the Locus (SDO_GEOMETRY)
20 * @param x_segid Segment ID of the road pointed to by Locus
21 * @param x_offset Offset of the Address in the road pointed to by Locus (0 .. 1)
22 * @param x_direction Direction of the road pointed to by Locus
23 */
24 PROCEDURE read_locus(
25 p_api_version IN NUMBER
26 , x_return_status OUT NOCOPY VARCHAR2
27 , x_msg_count OUT NOCOPY NUMBER
28 , x_msg_data OUT NOCOPY VARCHAR2
29 , p_locus IN MDSYS.SDO_GEOMETRY
30 , x_geom OUT NOCOPY MDSYS.SDO_GEOMETRY
31 , x_segid OUT NOCOPY NUMBER
32 , x_offset OUT NOCOPY NUMBER
33 , x_direction OUT NOCOPY NUMBER
34 );
35
36 PRAGMA RESTRICT_REFERENCES(read_locus, RNDS, WNDS, WNPS);
37
38 /**
39 * The API returns the locus given the Geometry (SDO_GEOMETRY),
40 * Segment ID, Offset and Side of the Road.
41 * <br>
42 * The API has a PRAGMA Restriction to avoid reading the database or
43 * modifying it.
44 *
45 * @param p_api_version API Version (1.0)
46 * @param x_return_status Return Status of the Procedure.
47 * @param x_msg_count Number of Messages in the Stack.
48 * @param x_msg_data Stack of Error Messages.
49 * @param p_geom Road Geometry (SDO_GEOMETRY)
50 * @param p_segid Segment ID of the road
51 * @param p_offset Offset of the Address in the road (0 .. 1)
52 * @param p_direction Direction of the road
53 * @param x_locus Locus Object (SDO_GEOMETRY)
54 */
55 PROCEDURE write_locus(
56 p_api_version IN NUMBER
57 , x_return_status OUT NOCOPY VARCHAR2
58 , x_msg_count OUT NOCOPY NUMBER
59 , x_msg_data OUT NOCOPY VARCHAR2
60 , p_geom IN MDSYS.SDO_GEOMETRY
61 , p_segid IN NUMBER
62 , p_offset IN NUMBER
63 , p_direction IN NUMBER
64 --added for LF enhancement of forced accuracy
65 ,p_accuracyFactor IN NUMBER
66 , x_locus OUT NOCOPY MDSYS.SDO_GEOMETRY
67 );
68
69 PRAGMA RESTRICT_REFERENCES(write_locus, RNDS, WNDS, WNPS);
70
71 /**
72 * The API verifies whether the given locus follow the prescribed norms.
73 * <br>
74 * The API has a PRAGMA Restriction to avoid reading the database or
75 * modifying it.
76 *
77 * @param p_api_version API Version (1.0)
78 * @param x_return_status Return Status of the Procedure.
79 * @param x_msg_count Number of Messages in the Stack.
80 * @param x_msg_data Stack of Error Messages.
81 * @param p_locus Locus to be verified (SDO_GEOMETRY)
82 * @param x_result Status indicating validity of Locus.
83 */
84 PROCEDURE verify_locus(
85 p_api_version IN NUMBER
86 , x_return_status OUT NOCOPY VARCHAR2
87 , x_msg_count OUT NOCOPY NUMBER
88 , x_msg_data OUT NOCOPY VARCHAR2
89 , p_locus IN MDSYS.SDO_GEOMETRY
90 , x_result OUT NOCOPY VARCHAR2
91 );
92
93 PRAGMA RESTRICT_REFERENCES(verify_locus, RNDS, WNDS, RNDS, WNPS);
94
95 /**
96 * Utility Function to get the Segment ID of the Road from the given
97 * Locus / Geometry of the Road or -9999 in case of invalid Locus.
98 *
99 * @param p_api_version API Version (1.0)
100 * @param x_return_status Return Status of the Procedure.
101 * @param x_msg_count Number of Messages in the Stack.
102 * @param x_msg_data Stack of Error Messages.
103 * @param p_geom Geometry of the Road (SDO_GEOMETRY)
104 */
105 FUNCTION get_locus_segmentid(
106 p_api_version IN NUMBER
107 , x_return_status OUT NOCOPY VARCHAR2
108 , x_msg_count OUT NOCOPY NUMBER
109 , x_msg_data OUT NOCOPY VARCHAR2
110 , p_geom IN MDSYS.SDO_GEOMETRY
111 )
112 RETURN NUMBER;
113
114 /**
115 * Utility Function to get the Segment ID of the Road from the given
116 * Locus / Geometry of the Road or -9999 in case of invalid Locus.
117 * <br>
118 * This function can be used in SQL's where output variables cant be bound.
119 *
120 * @param p_geom Geometry of the Road (SDO_GEOMETRY)
121 */
122 FUNCTION get_locus_segmentid(p_geom MDSYS.SDO_GEOMETRY)
123 RETURN NUMBER;
124
125 /**
126 * Utility Function to get the Offset / Spot of the Road from the given
127 * Locus / Geometry of the Road or -9999 in case of invalid Locus.
128 *
129 * @param p_api_version API Version (1.0)
130 * @param x_return_status Return Status of the Procedure.
131 * @param x_msg_count Number of Messages in the Stack.
132 * @param x_msg_data Stack of Error Messages.
133 * @param p_geom Geometry of the Road (SDO_GEOMETRY)
134 */
135 FUNCTION get_locus_spot(
136 p_api_version IN NUMBER
137 , x_return_status OUT NOCOPY VARCHAR2
138 , x_msg_count OUT NOCOPY NUMBER
139 , x_msg_data OUT NOCOPY VARCHAR2
140 , p_geom IN MDSYS.SDO_GEOMETRY
141 )
142 RETURN NUMBER;
143
144 /**
145 * Utility Function to get the Offset / Spot of the Road from the given
146 * Locus / Geometry of the Road or -9999 in case of invalid Locus.
147 * <br>
148 * This function can be used in SQL's where output variables cant be bound.
149 *
150 * @param p_geom Geometry of the Road (SDO_GEOMETRY)
151 */
152 FUNCTION get_locus_spot(p_geom MDSYS.SDO_GEOMETRY)
153 RETURN NUMBER;
154
155 /**
156 * Utility Function to get the Side of the Road from the given
157 * Locus / Geometry of the Road or -9999 in case of invalid Locus.
158 *
159 * @param p_api_version API Version (1.0)
160 * @param x_return_status Return Status of the Procedure.
161 * @param x_msg_count Number of Messages in the Stack.
162 * @param x_msg_data Stack of Error Messages.
163 * @param p_geom Geometry of the Road (SDO_GEOMETRY)
164 */
165 FUNCTION get_locus_side(
166 p_api_version IN NUMBER
167 , x_return_status OUT NOCOPY VARCHAR2
168 , x_msg_count OUT NOCOPY NUMBER
169 , x_msg_data OUT NOCOPY VARCHAR2
170 , p_geom IN MDSYS.SDO_GEOMETRY
171 )
172 RETURN NUMBER;
173
174 /**
175 * Utility Function to get the Side of the Road from the given
176 * Locus / Geometry of the Road or -9999 in case of invalid Locus.
177 * <br>
178 * This function can be used in SQL's where output variables cant be bound.
179 *
180 * @param p_geom Geometry of the Road (SDO_GEOMETRY)
181 */
182 FUNCTION get_locus_side(p_geom MDSYS.SDO_GEOMETRY)
183 RETURN NUMBER;
184
185 /**
186 * Utility Function to get the Latitude of the Road from the given
187 * Locus / Geometry of the Road or -9999 in case of invalid Locus.
188 *
189 * @param p_api_version API Version (1.0)
190 * @param x_return_status Return Status of the Procedure.
191 * @param x_msg_count Number of Messages in the Stack.
192 * @param x_msg_data Stack of Error Messages.
193 * @param p_geom Geometry of the Road (SDO_GEOMETRY)
194 */
195 FUNCTION get_locus_lat(
196 p_api_version IN NUMBER
197 , x_return_status OUT NOCOPY VARCHAR2
198 , x_msg_count OUT NOCOPY NUMBER
199 , x_msg_data OUT NOCOPY VARCHAR2
200 , p_geom IN MDSYS.SDO_GEOMETRY
201 )
202 RETURN NUMBER;
203
204 /**
205 * Utility Function to get the Latitude of the Road from the given
206 * Locus / Geometry of the Road or -9999 in case of invalid Locus.
207 * <br>
208 * This function can be used in SQL's where output variables cant be bound.
209 *
210 * @param p_geom Geometry of the Road (SDO_GEOMETRY)
211 */
212 FUNCTION get_locus_lat(p_geom MDSYS.SDO_GEOMETRY)
213 RETURN NUMBER;
214
215 /**
216 * Utility Function to get the Longitude of the Road from the given
217 * Locus / Geometry of the Road or -9999 in case of invalid Locus.
218 *
219 * @param p_api_version API Version (1.0)
220 * @param x_return_status Return Status of the Procedure.
221 * @param x_msg_count Number of Messages in the Stack.
222 * @param x_msg_data Stack of Error Messages.
223 * @param p_geom Geometry of the Road (SDO_GEOMETRY)
224 */
225 FUNCTION get_locus_lon(
226 p_api_version IN NUMBER
227 , x_return_status OUT NOCOPY VARCHAR2
228 , x_msg_count OUT NOCOPY NUMBER
229 , x_msg_data OUT NOCOPY VARCHAR2
230 , p_geom IN MDSYS.SDO_GEOMETRY
231 )
232 RETURN NUMBER;
233
234 /**
235 * Utility Function to get the Longitude of the Road from the given
236 * Locus / Geometry of the Road or -9999 in case of invalid Locus.
237 * <br>
238 * This function can be used in SQL's where output variables cant be bound.
239 *
240 * @param p_geom Geometry of the Road (SDO_GEOMETRY)
241 */
242 FUNCTION get_locus_lon(p_geom MDSYS.SDO_GEOMETRY)
243 RETURN NUMBER;
244
245 /**
246 * Function name : GET_LOCUS_SRID
247 * Description : Given a geometry,returns the Spatial Ref ID of
248 * the locus; if invalid locus returns -9999
249 * @param p_geom : input point geometry object
250 *
251 */
252 Function get_locus_srid (p_geom IN MDSYS.SDO_GEOMETRY)
253 return NUMBER;
254
255 /**
256 * Checks whether the Geometry is proper or not and inturn indicates
257 * whether to call Location Finder or not.
258 *
259 * @returns FND_API.G_TRUE/G_FALSE
260 */
261 FUNCTION should_call_lf(p_geom IN MDSYS.SDO_GEOMETRY)
262 RETURN VARCHAR2;
263
264 /** This function is called with p_item = 'SDO_POINT' and p_index = 1 (X)
265 * or p_index = 2 (Y) to determine the coordinates of a point location
266 * such as a customer, resource or task.
267 * If the SDO_POINT item is null it is assumed that the geometry is a
268 * valid locus (see package CSF_LOCUS_PUB). For performance reasons this
269 * will not be checked. The X and Y values can then be obtained from the
270 * first two elements of the SDO_ORDINATES array.
271 */
272
273 FUNCTION get_geometry(
274 p_geometry MDSYS.SDO_GEOMETRY
275 , p_item VARCHAR2
276 , p_index NUMBER DEFAULT NULL
277 )
278 RETURN NUMBER;
279
280
281 /** This function is used to get the x1, y1, x2 and y2 coordinates for
282 * the selected service area on Map
283 */
284 FUNCTION get_serv_area_coordinates(
285 p_country_id NUMBER
286 , p_index NUMBER
287 )
288 RETURN NUMBER;
289
290 /**
291 * Returns the Geometry as a String corresponding to the list of Segment IDs
292 * given
293 *
294 * @param p_segment_id_tbl Table of Segment IDs
295 * @param p_sampling_level Whats the Sampling Rate to be used on the Geometry
296 */
297 FUNCTION get_geometry_tbl(
298 p_segment_id_tbl jtf_number_table
299 , p_sampling_level VARCHAR2 DEFAULT NULL
300 )
301 RETURN jtf_varchar2_table_2000;
302
303 /**
304 * Computes the Geometry of the Route given as the Segment IDs
305 * Table and then saves the Geometry of the Route in
306 * CSF_TDS_ROUTE_CACHE to be used in future computations.
307 *
308 * p_api_version API Version
309 * p_init_msg_list Initialize the Message List
310 * p_commit Commit the Transaction at the end
311 * x_return_status Return Status
312 * x_msg_count Message Count
313 * x_msg_data Message Data
314 * p_segment_id_tbl List of Segment IDs
315 * p_start_side Start Side
316 * p_start_offset Start Offset
317 * p_end_side End Side
318 * p_end_offset End Offset
319 * p_cost_type Type of Cost Calculator used
320 * p_travel_time Travel Time
321 * p_travel_distance Travel Distance
322 *
323 */
324 PROCEDURE compute_and_save_route(
325 p_api_version IN NUMBER
326 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
327 , p_commit IN VARCHAR2 DEFAULT NULL
328 , x_return_status OUT NOCOPY VARCHAR2
329 , x_msg_count OUT NOCOPY NUMBER
330 , x_msg_data OUT NOCOPY VARCHAR2
331 , p_segment_id_tbl IN jtf_number_table
332 , p_start_side IN NUMBER
333 , p_start_offset IN NUMBER
334 , p_end_side IN NUMBER
335 , p_end_offset IN NUMBER
336 , p_tds_calc_type IN NUMBER DEFAULT NULL
337 , p_travel_time IN NUMBER
338 , p_travel_distance IN NUMBER
339 );
340
341 /**
342 * For each Location given in the Location Table, the Location Record
343 * in HZ_LOCATIONS will be updated with the Geometry containing the
344 * Latitude and Longitude as given by the corresponding PLSQL Tables.
345 *
346 * p_api_version API Version
347 * p_init_msg_list Initialize the Message List
348 * p_commit Commit the Transaction at the end
349 * x_return_status Return Status
350 * x_msg_count Message Count
351 * x_msg_data Message Data
352 * p_location_id_tbl List of Location IDs
353 * p_latitude_tbl List of Latitudes for the above Locations
354 * p_longitude_tbl List of Longitudes for the above Locations
355 */
356 PROCEDURE compute_and_save_locuses(
357 p_api_version IN NUMBER
358 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
359 , p_commit IN VARCHAR2 DEFAULT NULL
360 , x_return_status OUT NOCOPY VARCHAR2
361 , x_msg_count OUT NOCOPY NUMBER
362 , x_msg_data OUT NOCOPY VARCHAR2
363 , p_srid IN NUMBER DEFAULT 8307
364 , p_location_id_tbl IN jtf_number_table
365 , p_latitude_tbl IN jtf_number_table
366 , p_longitude_tbl IN jtf_number_table
367 );
368
369 /**
370 * Returns the Given Geometry in String Representation with each attribute
371 * separated by @. The sequence of the attributes are Longitude, Latitude,
372 * Segment Id, Offset and Side.
373 *
374 * In case Soft Validation is sufficient, then VERIFY_LOCUS is not called.
375 * Rather the most basic steps are performed for validation. Thus this
376 * mode can return Locus of those Geometries where only Longitude and Latitude
377 * are populated.
378 *
379 * @param p_geom Geometry whose String Representation is Required.
380 * @param p_soft_validation To enable Soft Validation or not (T / N)
381 * @return @ separated geometry attributes.
382 */
383 FUNCTION get_locus_string(
384 p_geom IN MDSYS.SDO_GEOMETRY
385 , p_soft_validation IN VARCHAR2 DEFAULT NULL
386 )
387 RETURN VARCHAR2;
388
389 END csf_locus_pub;
[Home] [Help]