[Home] [Help]
PACKAGE BODY: APPS.CSI_GIS_INSTANCE_LOC_UTL_PKG
Source
1 PACKAGE BODY CSI_GIS_INSTANCE_LOC_UTL_PKG AS
2 /* $Header: csigilub.pls 120.3 2011/03/14 06:57:01 somitra noship $*/
3
4 /***************************************************************************
5 --
6 -- Copyright (c) 2008 Oracle Corporation, Redwood Shores, CA, USA
7 -- All rights reserved.
8 --
9 -- FILENAME
10 --
11 -- csigilub.pls
12 --
13 -- DESCRIPTION
14 --
15 -- Body of package CSI_GIS_INSTANCE_LOC_UTL_PKG
16 --
17 -- NOTES
18 --
19 -- HISTORY
20 --
21 -- 11-NOV-2008 jgootyag Initial Creation
22 ***************************************************************************/
23
24 /*validate instance */
25 PROCEDURE VALIDATE_INSTANCE_NUMBER
26 (
27 p_instance_id IN NUMBER
28 ,p_asset_context IN VARCHAR2
29 ,x_instance_number OUT NOCOPY VARCHAR2
30 ,x_create_update OUT NOCOPY VARCHAR2
31 ,x_return_status OUT NOCOPY VARCHAR2
32 ,x_msg_count OUT NOCOPY NUMBER
33 ,x_msg_data OUT NOCOPY VARCHAR2
34 ) IS
35
36 l_inventory_item_id NUMBER;
37 l_organization_id NUMBER;
38 l_serial_number VARCHAR2(30);
39 l_instance_number VARCHAR2(30);
40 l_csi_item_type NUMBER;
41 l_serial_number_control_code NUMBER;
42 l_network_asset_flag VARCHAR2(1);
43 l_exists VARCHAR2(1);
44 l_create_update VARCHAR2(1);
45 l_instance_type VARCHAR2(3):='EAM';
46 l_debug_level NUMBER;
47
48 BEGIN
49 l_debug_level:=to_number(fnd_profile.value('CSI_DEBUG_LEVEL'));
50 csi_t_gen_utility_pvt.add( 'In Validate Instance Number');
54 BEGIN
51 x_return_status := FND_API.G_RET_STS_SUCCESS;
52
53 /* check if the Instance exists */
55
56 SELECT 'Y', INVENTORY_ITEM_ID, LAST_VLD_ORGANIZATION_ID, SERIAL_NUMBER, INSTANCE_NUMBER,NETWORK_ASSET_FLAG
57 INTO l_exists, l_inventory_item_id, l_organization_id,l_serial_number, l_instance_number,l_network_asset_flag
58 FROM CSI_ITEM_INSTANCES
59 WHERE INSTANCE_ID = p_instance_id;
60
61
62 IF l_debug_level > 0 THEN
63 csi_t_gen_utility_pvt.add( 'Instance exists in csi_item_instances');
64 csi_t_gen_utility_pvt.add( 'Inventory Item Id:'||l_inventory_item_id);
65 csi_t_gen_utility_pvt.add( 'Organization Id:'||l_organization_id);
66 csi_t_gen_utility_pvt.add( 'Serial Number:'||l_serial_number);
67 csi_t_gen_utility_pvt.add( 'Instance Number:'||l_instance_number);
68 END IF;
69
70 SELECT Nvl((SELECT 'EAM'
71 From MFG_LOOKUPS ml1, MTL_SYSTEM_ITEMS_B_KFV msi,csi_item_instances cii
72 where
73 msi.inventory_item_id = cii.inventory_item_id
74 AND cii.last_vld_organization_id=msi.organization_id
75 AND (cii.active_start_date IS NULL OR (cii.active_start_date <= sysdate))
76 AND (cii.active_end_date IS NULL OR (cii.active_end_date >= sysdate))
77 AND ml1.lookup_type = 'EAM_ITEM_TYPE'
78 AND msi.eam_item_type = ml1.lookup_code
79 AND NVL(cii.maintainable_flag,'Y') = 'Y'
80 AND cii.instance_id = p_instance_id
81 ),'CSE' )
82 INTO l_instance_type
83 FROM DUAL;
84
85 csi_t_gen_utility_pvt.add( 'Instance Type:'||l_instance_type);
86
87 EXCEPTION WHEN NO_DATA_FOUND THEN
88 csi_t_gen_utility_pvt.add( 'Instance does not exist in csi_item_instances');
89 FND_MESSAGE.SET_NAME('CSI','CSI_INSTANCE_NOT_FOUND');
90 FND_MESSAGE.SET_TOKEN('INSTANCE',l_instance_number);
91 FND_MSG_PUB.ADD;
92 x_return_status := FND_API.G_RET_STS_ERROR;
93 return;
94
95 END;
96
97
98 /*If the asset is EAM asset validate it */
99 IF l_instance_type = 'EAM' THEN
100 csi_t_gen_utility_pvt.add( 'Validating EAM Asset');
101 SELECT EAM_ITEM_TYPE,SERIAL_NUMBER_CONTROL_CODE
102 INTO l_csi_item_type,l_serial_number_control_code
103 FROM MTL_SYSTEM_ITEMS_B_KFV
104 WHERE INVENTORY_ITEM_ID = l_inventory_item_id
105 AND ORGANIZATION_ID = l_organization_id;
106
107
108 IF l_csi_item_type NOT IN (1,3) THEN
109 -- Raise error
110 FND_MESSAGE.SET_NAME('CSI','CSI_ASSET_NOT_CAPTL_REBUILD');
111 FND_MESSAGE.SET_TOKEN('INSTANCE',l_instance_number);
112 FND_MSG_PUB.ADD;
113 x_return_status := FND_API.G_RET_STS_ERROR;
114 return;
115
116 END IF;
117
118 IF l_serial_number_control_code = 1 THEN
119 FND_MESSAGE.SET_NAME('CSI','CSI_ASSET_NOT_SERIALIZED');
120 FND_MESSAGE.SET_TOKEN('INSTANCE',l_instance_number);
121 FND_MSG_PUB.ADD;
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 return;
124 END IF;
125
126 IF nvl(l_network_asset_flag,'N') = 'Y' THEN
127 FND_MESSAGE.SET_NAME('CSI','CSI_ASSET_ROUTE_CNT_GEOCODE');
128 FND_MESSAGE.SET_TOKEN('INSTANCE',l_instance_number);
129 FND_MSG_PUB.ADD;
130 x_return_status := FND_API.G_RET_STS_ERROR;
131 return;
132 END IF;
133 END IF;
134
135 /* check if Geo location info already exists for the instance, else mark it as create */
136 BEGIN
137 SELECT 'U'
138 INTO l_create_update
139 FROM CSI_II_GEOLOCATIONS
140 WHERE INSTANCE_ID = p_instance_id;
141
142 EXCEPTION WHEN NO_DATA_FOUND THEN
143 l_create_update := 'C';
144
145 END;
146 x_instance_number:=l_instance_number;
147 x_create_update := l_create_update;
148
149 END VALIDATE_INSTANCE_NUMBER;
150
151 /* Procedure to convert latitude/longitude to DD format */
152
153 PROCEDURE CONVERT_DMS_OR_DM_TO_DD
154 (
155 p_value IN VARCHAR2
156 ,p_mode IN VARCHAR2
157 ,p_geocode_format IN VARCHAR2
158 ,p_instance_number IN VARCHAR2
159 ,x_value OUT NOCOPY NUMBER
160 ,x_return_status OUT NOCOPY VARCHAR2
161 ,x_msg_count OUT NOCOPY NUMBER
162 ,x_msg_data OUT NOCOPY VARCHAR2
163 ) IS
164
165 l_length NUMBER;
166 l_value VARCHAR2(100);
167 l_dd_value NUMBER;
168 l_direction VARCHAR2(1);
169 l_sign VARCHAR2(1);
170
171 BEGIN
172 csi_t_gen_utility_pvt.add( 'Converting latitude or longitude value to DD format');
173
174 l_value := UPPER(TRIM(p_value));
175 l_length := LENGTH(l_value);
176
177 SELECT decode(substr(l_value,l_length),'N','N','S','S','E','E','W','W','A')
178 INTO l_direction
179 FROM DUAL;
180
181 SELECT decode(substr(l_value,1,1),'+','+','-','-','+')
182 INTO l_sign
183 FROM DUAL;
184
185 IF p_geocode_format = 'DMS' THEN
186 IF l_direction = 'S' AND instr(substr(l_value,1,l_length-1),'S') = 0 THEN
187 l_direction := 'A';
188 END IF;
189 END IF;
190
194
191 IF l_direction IN ('S','W') THEN
192 l_sign := '-';
193 END IF;
195 IF l_sign = '-' THEN
196
197 IF l_direction ='A' THEN
198 IF substr(l_value,1,1) = '-' THEN
199 l_value := substr(l_value,2);
200 ELSE
201 l_value := substr(l_value,1);
202 END IF;
203 ELSE
204 IF substr(l_value,1,1) = '-' THEN
205 l_value := substr(l_value,2,l_length-2);
206 ELSE
207 l_value := substr(l_value,1,l_length-1);
208 END IF;
209
210 END IF;
211
212 Calculate_DD( p_value=>l_value,
213 p_geocode_format=>p_geocode_format,
214 p_mode => p_mode,
215 p_instance_number=>p_instance_number,
216 x_value=>l_dd_value,
217 x_return_status=>x_return_status,
218 x_msg_count=>x_msg_count,
219 x_msg_data=>x_msg_data);
220
221 x_value:= - l_dd_value;
222
223 ELSIF l_sign = '+' THEN
224
225 IF l_direction ='A' THEN
226 IF substr(l_value,1,1) = '+' THEN
227 l_value := substr(l_value,2);
228 ELSE
229 l_value := substr(l_value,1);
230 END IF;
231 ELSE
232 IF substr(l_value,1,1) = '+' THEN
233 l_value := substr(l_value,2,l_length-2);
234 ELSE
235 l_value := substr(l_value,1,l_length-1);
236 END IF;
237 END IF;
238
239 Calculate_DD( p_value=>l_value,
240 p_geocode_format=>p_geocode_format,
241 p_mode => p_mode,
242 p_instance_number=>p_instance_number,
243 x_value=>l_dd_value,
244 x_return_status=>x_return_status,
245 x_msg_count=>x_msg_count,
246 x_msg_data=>x_msg_data);
247
248 x_value:= l_dd_value;
249
250 END IF;
251
252 IF p_mode = 'LAT' THEN
253 IF NOT (x_value BETWEEN -90 AND +90) THEN
254 FND_MESSAGE.SET_NAME('CSI','CSI_OOR_LAT_VALUE');
255 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
256 FND_MSG_PUB.ADD;
257 x_return_status := FND_API.G_RET_STS_ERROR;
258 return;
259 END IF;
260 ELSIF p_mode = 'LON' THEN
261 IF NOT (x_value BETWEEN -180 AND +180) THEN
262 FND_MESSAGE.SET_NAME('CSI','CSI_OOR_LON_VALUE');
263 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
264 FND_MSG_PUB.ADD;
265 x_return_status := FND_API.G_RET_STS_ERROR;
266 return;
267 END IF;
268 END IF;
269
270 END CONVERT_DMS_OR_DM_TO_DD;
271
272 /*Procedure to calculate DD value */
273 PROCEDURE Calculate_DD
274 (
275 p_value IN VARCHAR2
276 ,p_geocode_format IN VARCHAR2
277 ,p_mode IN VARCHAR2
278 ,p_instance_number IN VARCHAR2
279 ,x_value OUT NOCOPY NUMBER
280 ,x_return_status OUT NOCOPY VARCHAR2
281 ,x_msg_count OUT NOCOPY NUMBER
282 ,x_msg_data OUT NOCOPY VARCHAR2
283 ) IS
284 l_degrees NUMBER:=0;
285 l_minutes NUMBER:=0;
286 l_seconds NUMBER:=0;
287 l_d_pos NUMBER:=0;
288 l_m_pos NUMBER:=0;
289 l_s_pos NUMBER:=0;
290 l_value VARCHAR2(100);
291 Invalid_Decimal Exception;
292 BEGIN
293 csi_t_gen_utility_pvt.add( 'Calculating DD value');
294 l_value := Trim(Upper(p_value));
295
296 l_d_pos := INSTR(l_value,'D');
297 l_m_pos := INSTR(l_value,'M');
298 l_s_pos := INSTR(l_value,'S');
299
300
301 IF p_geocode_format = 'DMS' THEN
302 IF (NOT(l_d_pos < l_m_pos and l_m_pos < l_s_pos)) OR (substr(l_value,l_s_pos + 1) IS NOT NULL) THEN
303 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_LAT_LONG_DMS_VALUE');
304 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
305 IF p_mode = 'LAT' THEN
306 FND_MESSAGE.SET_TOKEN('ORD','Latitude');
307 ELSE
308 FND_MESSAGE.SET_TOKEN('ORD','Longitude');
309 END IF;
310 FND_MSG_PUB.ADD;
311 x_return_status := FND_API.G_RET_STS_ERROR;
312 RETURN;
313 END IF;
314 ELSE
315 IF (NOT(l_d_pos < l_m_pos) or (l_s_pos > 0) or (substr(l_value,l_m_pos + 1) IS NOT NULL)) THEN
316 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_LAT_LONG_DM_VALUE');
317 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
318 IF p_mode = 'LAT' THEN
319 FND_MESSAGE.SET_TOKEN('ORD','Latitude');
320 ELSE
321 FND_MESSAGE.SET_TOKEN('ORD','Longitude');
322 END IF;
323 FND_MSG_PUB.ADD;
324 x_return_status := FND_API.G_RET_STS_ERROR;
325 RETURN;
326 END IF;
327 END IF;
328
329
330 BEGIN
331
332 SELECT to_number(nvl(substr(l_value,1,l_d_pos-1),-1))
333 INTO l_degrees
334 FROM DUAL;
335
336 IF l_degrees - Trunc(l_degrees) > 0 THEN
337 RAISE Invalid_Decimal;
338 END IF;
339
340 IF l_degrees < 0 THEN
341 FND_MESSAGE.SET_NAME('CSI','CSI_INV_DEGREE_VALUE');
342 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
343 IF p_mode = 'LAT' THEN
344 FND_MESSAGE.SET_TOKEN('ORD','Latitude');
345 ELSE
346 FND_MESSAGE.SET_TOKEN('ORD','Longitude');
347 END IF;
348 FND_MSG_PUB.ADD;
349 x_return_status := FND_API.G_RET_STS_ERROR;
350 return;
351 END IF;
352
353 EXCEPTION
354 WHEN INVALID_NUMBER THEN
355 FND_MESSAGE.SET_NAME('CSI','CSI_INV_DEGREE_VALUE');
356 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
357 IF p_mode = 'LAT' THEN
358 FND_MESSAGE.SET_TOKEN('ORD','Latitude');
359 ELSE
360 FND_MESSAGE.SET_TOKEN('ORD','Longitude');
361 END IF;
362 FND_MSG_PUB.ADD;
363 x_return_status := FND_API.G_RET_STS_ERROR;
364 RETURN;
365 WHEN Invalid_Decimal THEN
366 FND_MESSAGE.SET_NAME('CSI','CSI_INV_DECIMAL_DEGREE_VALUE');
367 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
368 IF p_mode = 'LAT' THEN
369 FND_MESSAGE.SET_TOKEN('ORD','Latitude');
370 ELSE
371 FND_MESSAGE.SET_TOKEN('ORD','Longitude');
372 END IF;
373 FND_MSG_PUB.ADD;
374 x_return_status := FND_API.G_RET_STS_ERROR;
375 RETURN;
376 END;
377
378 BEGIN
379
380 SELECT to_number(nvl(substr(l_value,l_d_pos+1,(l_m_pos-l_d_pos-1)),-1))
381 INTO l_minutes
382 FROM DUAL;
383
384 IF p_geocode_format = 'DMS' THEN
385 IF l_minutes - Trunc(l_minutes) > 0 THEN
386 RAISE Invalid_Decimal;
387 END IF;
388
389 END IF;
390 IF l_minutes < 0 OR l_minutes > 59 THEN
391 FND_MESSAGE.SET_NAME('CSI','CSI_OOR_MINUTES_VALUE');
392 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
393 IF p_mode = 'LAT' THEN
394 FND_MESSAGE.SET_TOKEN('ORD','Latitude');
395 ELSE
396 FND_MESSAGE.SET_TOKEN('ORD','Longitude');
397 END IF;
398 FND_MSG_PUB.ADD;
399 x_return_status := FND_API.G_RET_STS_ERROR;
400 return;
401 END IF;
402
403 EXCEPTION
404 WHEN INVALID_NUMBER THEN
405 FND_MESSAGE.SET_NAME('CSI','CSI_INV_MINUTES_VALUE');
406 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
407 IF p_mode = 'LAT' THEN
408 FND_MESSAGE.SET_TOKEN('ORD','Latitude');
409 ELSE
410 FND_MESSAGE.SET_TOKEN('ORD','Longitude');
411 END IF;
412 FND_MSG_PUB.ADD;
413 x_return_status := FND_API.G_RET_STS_ERROR;
414 RETURN;
415 WHEN Invalid_Decimal THEN
416 FND_MESSAGE.SET_NAME('CSI','CSI_INV_DECIMAL_MINUTES_VALUE');
417 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
418 IF p_mode = 'LAT' THEN
419 FND_MESSAGE.SET_TOKEN('ORD','Latitude');
420 ELSE
421 FND_MESSAGE.SET_TOKEN('ORD','Longitude');
422 END IF;
423 FND_MSG_PUB.ADD;
424 x_return_status := FND_API.G_RET_STS_ERROR;
425 RETURN;
426 END;
427
428 IF p_geocode_format <> 'DM' THEN
429 BEGIN
430
431 SELECT to_number(nvl(substr(l_value,l_m_pos+1,(l_s_pos-l_m_pos-1)),-1))
432 INTO l_seconds
433 FROM DUAL;
434
435 IF l_seconds < 0 OR l_seconds > 59.99 THEN
436 FND_MESSAGE.SET_NAME('CSI','CSI_OOR_SECONDS_VALUE');
437 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
438 IF p_mode = 'LAT' THEN
439 FND_MESSAGE.SET_TOKEN('ORD','Latitude');
440 ELSE
441 FND_MESSAGE.SET_TOKEN('ORD','Longitude');
442 END IF;
443 FND_MSG_PUB.ADD;
444 x_return_status := FND_API.G_RET_STS_ERROR;
445 return;
446 END IF;
447
448 EXCEPTION
449 WHEN INVALID_NUMBER THEN
450 FND_MESSAGE.SET_NAME('CSI','CSI_INV_SECONDS_VALUE');
451 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
452 IF p_mode = 'LAT' THEN
453 FND_MESSAGE.SET_TOKEN('ORD','Latitude');
454 ELSE
455 FND_MESSAGE.SET_TOKEN('ORD','Longitude');
456 END IF;
457 FND_MSG_PUB.ADD;
458 x_return_status := FND_API.G_RET_STS_ERROR;
459 RETURN;
460 END;
461 END IF;
462
463 x_value:= (l_degrees + l_minutes/60 + l_seconds/3600);
464
465
466
467 END Calculate_DD;
468
469 /*Procedure to Validate latitude, longitude values */
470 PROCEDURE VALIDATE_LATITUDE_LONGITUDE
471 (
472 p_latitude IN VARCHAR2
473 ,p_longitude IN VARCHAR2
474 ,p_geocode_format IN VARCHAR2
475 ,p_instance_number IN VARCHAR2
476 ,x_return_status OUT NOCOPY VARCHAR2
477 ,x_msg_count OUT NOCOPY NUMBER
478 ,x_msg_data OUT NOCOPY VARCHAR2
479 ) IS
480 l_msg_count NUMBER:=0;
481 l_msg_data VARCHAR2(4000);
482 l_return_status VARCHAR2(1);
483 l_latitude_length NUMBER:=0;
484 l_longitude_length NUMBER:=0;
485 l_latitude_value NUMBER:=0;
486 l_longitude_value NUMBER:=0;
487 l_latitude VARCHAR2(100);
488 l_longitude VARCHAR2(100);
489 l_latitude_direction VARCHAR2(1);
490 l_longitude_direction VARCHAR2(1);
491 l_latitude_sign VARCHAR2(1);
492 l_longitude_sign VARCHAR2(1);
493 l_char VARCHAR2(2);
494 l_valid VARCHAR2(1);
495 l_direction_count NUMBER:=0;
496 l_error_count NUMBER:=0;
497
498
499 BEGIN
500 csi_t_gen_utility_pvt.add( 'In Validate latitude/longitude');
501
502 l_latitude := UPPER(TRIM(p_latitude));
503 l_latitude_length := LENGTH(l_latitude);
504 l_longitude := UPPER(TRIM(p_longitude));
505 l_longitude_length:= LENGTH(l_longitude);
506
507 SELECT decode(substr(l_latitude,1,1),'+','+','-','-','+'),decode(substr(l_longitude,1,1),'+','+','-','-','+')
508 INTO l_latitude_sign,l_longitude_sign
509 FROM DUAL;
510
511 /* Get the direction*/
512 SELECT decode(substr(l_latitude,l_latitude_length),'N','N','S','S','E','E','W','W','A')
513 ,decode(substr(l_longitude,l_longitude_length),'N','N','S','S','E','E','W','W','A')
514 INTO l_latitude_direction
515 ,l_longitude_direction
516 FROM DUAL;
517
518 IF p_geocode_format = 'DMS' THEN
519 IF l_latitude_direction = 'S' AND instr(substr(l_latitude,1,l_latitude_length-1),'S') = 0 THEN
520 l_latitude_direction := 'A';
521 END IF;
522 IF l_longitude_direction = 'S' AND instr(substr(l_longitude,1,l_longitude_length-1),'S') = 0 THEN
523 l_longitude_direction := 'A';
524 END IF;
525 END IF;
526
527 IF p_geocode_format = 'DD' THEN
528
529 BEGIN
530 SELECT to_number(l_latitude)
531 INTO l_latitude_value
532 FROM DUAL;
533 EXCEPTION
534 WHEN INVALID_NUMBER THEN
535 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_LAT_DD_VALUE');
536 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
537 FND_MSG_PUB.ADD;
538 x_return_status := FND_API.G_RET_STS_ERROR;
539 return;
540 END;
541 BEGIN
542 SELECT to_number(l_longitude)
543 INTO l_longitude_value
544 FROM DUAL;
545 EXCEPTION
546 WHEN INVALID_NUMBER THEN
547 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_LONG_DD_VALUE');
551 return;
548 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
549 FND_MSG_PUB.ADD;
550 x_return_status := FND_API.G_RET_STS_ERROR;
552 END;
553
554 IF NOT (l_latitude_value BETWEEN -90 AND +90) THEN
555 FND_MESSAGE.SET_NAME('CSI','CSI_OOR_LAT_VALUE');
556 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
557 FND_MSG_PUB.ADD;
558 x_return_status := FND_API.G_RET_STS_ERROR;
559 return;
560 END IF;
561 IF NOT (l_longitude_value BETWEEN -180 AND +180) THEN
562 FND_MESSAGE.SET_NAME('CSI','CSI_OOR_LON_VALUE');
563 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
564 FND_MSG_PUB.ADD;
565 x_return_status := FND_API.G_RET_STS_ERROR;
566 return;
567 END IF;
568
569 END IF;
570 /*Checking for invalid Characters*/
571 FOR i IN 1..l_latitude_length LOOP
572
573 l_char:=substr(l_latitude,i,1);
574
575 IF l_char NOT IN ('0','1','2','3','4','5','6','7','8','9','N','S','E','W','D','M','S',' ','+','-','.') THEN
576 FND_MESSAGE.SET_NAME('CSI','CSI_INV_CHAR_LAT_VALUE');
577 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
578 FND_MSG_PUB.ADD;
579 x_return_status := FND_API.G_RET_STS_ERROR;
580 l_error_count :=l_error_count + 1;
581 return;
582 END IF;
583
584 IF l_latitude_direction in ('N','E','W','A','S') AND l_char IN ('N','S','E','W') THEN
585 l_direction_count := l_direction_count + 1;
586 END IF;
587
588 IF i <> 1 and l_char in ('+','-') THEN
589 FND_MESSAGE.SET_NAME('CSI','CSI_INV_LAT_VALUE');
590 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
591 FND_MSG_PUB.ADD;
592 x_return_status := FND_API.G_RET_STS_ERROR;
593 return;
594 END IF;
595
596 IF p_geocode_format = 'DMS' THEN
597 IF l_direction_count > 2 THEN
598 FND_MESSAGE.SET_NAME('CSI','CSI_INV_LAT_VALUE');
599 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
600 FND_MSG_PUB.ADD;
601 x_return_status := FND_API.G_RET_STS_ERROR;
602 l_error_count :=l_error_count + 1;
603 return;
604 END IF;
605
606 ELSIF p_geocode_format = 'DM' THEN
607 IF l_direction_count > 1 THEN
608 FND_MESSAGE.SET_NAME('CSI','CSI_INV_LAT_VALUE');
609 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
610 FND_MSG_PUB.ADD;
611 x_return_status := FND_API.G_RET_STS_ERROR;
612 l_error_count :=l_error_count + 1;
613 return;
614 END IF;
615 END IF;
616 END LOOP;
617
618
619
620
621 l_direction_count := 0;
622
623 FOR i IN 1..l_longitude_length LOOP
624
625 l_char:=substr(l_longitude,i,1);
626
627 IF l_char NOT IN ('0','1','2','3','4','5','6','7','8','9','N','S','E','W','D','M','S',' ','+','-','.') THEN
628 FND_MESSAGE.SET_NAME('CSI','CSI_INV_CHAR_LONG_VALUE');
629 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
630 FND_MSG_PUB.ADD;
631 x_return_status := FND_API.G_RET_STS_ERROR;
632 return;
633 END IF;
634
635 IF l_longitude_direction in ('N','E','W','A','S') AND l_char IN ('N','S','E','W') THEN
636 l_direction_count := l_direction_count + 1;
637 END IF;
638
639 IF i <> 1 and l_char in ('+','-') THEN
640 FND_MESSAGE.SET_NAME('CSI','CSI_INV_LON_VALUE');
641 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
642 FND_MSG_PUB.ADD;
643 x_return_status := FND_API.G_RET_STS_ERROR;
644 return;
645 END IF;
646
647 IF p_geocode_format = 'DMS' THEN
648 IF l_direction_count > 2 THEN
649 FND_MESSAGE.SET_NAME('CSI','CSI_INV_LON_VALUE');
650 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
651 FND_MSG_PUB.ADD;
652 x_return_status := FND_API.G_RET_STS_ERROR;
653 return;
654 END IF;
655 ELSIF p_geocode_format = 'DM' THEN
656 IF l_direction_count > 1 THEN
657 FND_MESSAGE.SET_NAME('CSI','CSI_INV_LON_VALUE');
658 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
659 FND_MSG_PUB.ADD;
660 x_return_status := FND_API.G_RET_STS_ERROR;
661 return;
662 END IF;
663 END IF;
664 END LOOP;
665
666 IF p_geocode_format = 'DMS' THEN
667
668 IF NOT (instr(l_latitude,'D') > 0 AND instr(l_latitude,'M') > 0 AND instr(l_latitude,'S') > 0) THEN
669 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_LAT_LONG_DMS_VALUE');
670 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
671 FND_MESSAGE.SET_TOKEN('ORD','Latitude');
672 FND_MSG_PUB.ADD;
673 x_return_status := FND_API.G_RET_STS_ERROR;
674 return;
675 END IF;
676 IF NOT (instr(l_longitude,'D') > 0 AND instr(l_longitude,'M') > 0 AND instr(l_longitude,'S') > 0) THEN
677 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_LAT_LONG_DMS_VALUE');
678 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
679 FND_MESSAGE.SET_TOKEN('ORD','Longitude');
680 FND_MSG_PUB.ADD;
681 x_return_status := FND_API.G_RET_STS_ERROR;
682 return;
683 END IF;
684
685
686 ELSIF p_geocode_format = 'DM' THEN
687
688 IF (instr(SubStr(l_latitude,1,l_latitude_length-1),'S') > 0) THEN
689 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_LAT_LONG_DM_VALUE');
690 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
691 FND_MESSAGE.SET_TOKEN('ORD','Latitude');
692 FND_MSG_PUB.ADD;
693 x_return_status := FND_API.G_RET_STS_ERROR;
694 return;
695 END IF;
696
697 IF (instr(SubStr(l_longitude,1,l_longitude_length-1),'S') > 0 ) THEN
698 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_LAT_LONG_DM_VALUE');
699 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
700 FND_MESSAGE.SET_TOKEN('ORD','Longitude');
701 FND_MSG_PUB.ADD;
702 x_return_status := FND_API.G_RET_STS_ERROR;
703 return;
704 END IF;
705
706 IF NOT (instr(l_latitude,'D') > 0 AND instr(l_latitude,'M') > 0 ) THEN
707 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_LAT_LONG_DM_VALUE');
708 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
709 FND_MESSAGE.SET_TOKEN('ORD','Latitude');
710 FND_MSG_PUB.ADD;
711 x_return_status := FND_API.G_RET_STS_ERROR;
712 return;
713 END IF;
714
715 IF NOT (instr(l_longitude,'D') > 0 AND instr(l_longitude,'M') > 0 ) THEN
716 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_LAT_LONG_DM_VALUE');
717 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
718 FND_MESSAGE.SET_TOKEN('ORD','Longitude');
719 FND_MSG_PUB.ADD;
720 x_return_status := FND_API.G_RET_STS_ERROR;
721 return;
722 END IF;
723
724 END IF;
725
726
727
728 IF (l_latitude_direction = 'A' and NOT(l_longitude_direction = 'A')) OR
729 (l_longitude_direction = 'A' and NOT(l_latitude_direction = 'A')) THEN
730 FND_MESSAGE.SET_NAME('CSI','CSI_LAT_LONG_DIFF_FORMAT');
731 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
732 FND_MSG_PUB.ADD;
733 x_return_status := FND_API.G_RET_STS_ERROR;
734 return;
735 END IF;
736
737 IF l_latitude_direction NOT IN ('N','S','A') THEN
738 FND_MESSAGE.SET_NAME('CSI','CSI_INV_LAT_DIRECTION');
739 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
740 FND_MSG_PUB.ADD;
741 x_return_status := FND_API.G_RET_STS_ERROR;
742 return;
743 END IF;
744
745 IF l_latitude_sign = '-' THEN
746 IF l_latitude_direction = 'N' THEN
747 FND_MESSAGE.SET_NAME('CSI','CSI_INV_LAT_NEG_VALUE');
748 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
749 FND_MSG_PUB.ADD;
750 x_return_status := FND_API.G_RET_STS_ERROR;
751 return;
752 END IF;
753 END IF;
754
755 IF l_longitude_direction NOT IN ('E','W','A') THEN
756 FND_MESSAGE.SET_NAME('CSI','CSI_INV_LON_DIRECTION');
757 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
758 FND_MSG_PUB.ADD;
759 x_return_status := FND_API.G_RET_STS_ERROR;
760 return;
761 END IF;
762
763 IF l_longitude_sign = '-' THEN
764 IF l_longitude_direction = 'E' THEN
765 FND_MESSAGE.SET_NAME('CSI','CSI_INV_LON_NEG_VALUE');
766 FND_MESSAGE.SET_TOKEN('INSTANCE',p_instance_number);
767 FND_MSG_PUB.ADD;
768 x_return_status := FND_API.G_RET_STS_ERROR;
769 return;
770 END IF;
771 END IF;
772
773 END VALIDATE_LATITUDE_LONGITUDE;
774
775 /*Function to get Degrees from DD value */
776 FUNCTION GET_DEGREES_FROM_DD
777 ( p_value IN NUMBER)
778 RETURN VARCHAR2 IS
779 l_value VARCHAR2(4);
780 BEGIN
781
782 l_value:=TO_CHAR(TRUNC(p_value));
783 return ABS(l_value);
784
785 END GET_DEGREES_FROM_DD;
786
787 /*Function to get Minutesfrom DD value */
788 FUNCTION GET_MINUTES_FROM_DD
789 ( p_value IN NUMBER)
790 RETURN VARCHAR2 IS
791 l_value VARCHAR2(2);
792 l_integer_part NUMBER;
793 l_decimal_part NUMBER;
794 BEGIN
795
796 l_integer_part := ABS(TRUNC(p_value));
797 l_decimal_part:= ABS(p_value) - l_integer_part ;
798 l_value:= TO_CHAR(TRUNC(l_decimal_part * 60));
799 return l_value;
800
801 END GET_MINUTES_FROM_DD;
802
803 /*Function to get Seconds from DD value */
804 FUNCTION GET_SECONDS_FROM_DD
805 ( p_value IN NUMBER)
806 RETURN VARCHAR2 IS
807 l_value VARCHAR2(40);
808 l_minutes_decimal_part NUMBER;
809 l_seconds_decimal NUMBER;
810 l_seconds_integer NUMBER;
811 BEGIN
812
813 l_minutes_decimal_part:= ABS(p_value) - ABS(TRUNC(p_value)) ;
814 l_seconds_integer:= l_minutes_decimal_part * 60;
815 l_seconds_decimal:= l_seconds_integer - TRUNC(l_seconds_integer);
816 l_value:= TO_CHAR(ROUND((l_seconds_decimal * 60),2));
817 return l_value;
818
819 END GET_SECONDS_FROM_DD;
820
821 /*Function to get Direction from DD value */
822 FUNCTION GET_DIRECTION_FROM_DD
823 (p_mode IN VARCHAR2
824 ,p_value IN NUMBER)
825 RETURN VARCHAR2 IS
826 l_sign NUMBER;
827 l_direction VARCHAR2(1);
828 BEGIN
829 IF p_value IS NULL THEN
830 return ' ';
831 END IF;
832 l_sign := SIGN(p_value);
833 IF p_mode = 'LAT' THEN
834 IF l_sign = -1 THEN
835 return 'S';
836 ELSE
837 return 'N';
838 END IF;
839 ELSIF p_mode = 'LON' THEN
840 IF l_sign = -1 THEN
841 return 'W';
842 ELSE
843 return 'E';
844 END IF;
845 END IF;
846
847 END GET_DIRECTION_FROM_DD;
848
849 END CSI_GIS_INSTANCE_LOC_UTL_PKG;