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