[Home] [Help]
PACKAGE BODY: APPS.CSI_GIS_INSTANCE_LOC_PUB
Source
1 PACKAGE BODY CSI_GIS_INSTANCE_LOC_PUB AS
2 /* $Header: csipgilb.pls 120.0.12010000.10 2009/01/09 08:54:19 jgootyag noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2008 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- csipgilb.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package CSI_GIS_INSTANCE_LOC_PUB
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 11-NOV-2008 jgootyag Initial Creation
21 ***************************************************************************/
22
23 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_GIS_INSTANCE_LOC_PUB';
24 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csipgisb.pls';
25
26 /*Procedure to create or update asset/instance geolocation latitude and longitude values*/
27 PROCEDURE CREATEUPDATE_INST_GEOLOC_INFO
28 (
29 p_api_version IN NUMBER
30 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
31 ,p_CSI_instance_geoloc_tbl IN CSI_GIS_INSTANCE_LOC_PUB.csi_instance_geoloc_tbl_type
32 ,p_asset_context IN VARCHAR2 :='EAM'
33 ,x_return_status OUT NOCOPY VARCHAR2
34 ,x_msg_count OUT NOCOPY NUMBER
35 ,x_msg_data OUT NOCOPY VARCHAR2
36 ) IS
37 l_api_name CONSTANT VARCHAR2(30) := 'CREATEUPDATE_INST_GEOLOC_INFO';
38 l_api_version CONSTANT NUMBER := 1.0;
39 l_debug_level NUMBER;
40 l_return_status VARCHAR2(1);
41 l_error_count NUMBER:=0;
42 l_msg_count NUMBER;
43 l_msg_data VARCHAR2(4000);
44 l_inst_latitude_dd_value NUMBER;
45 l_inst_longitude_dd_value NUMBER;
46 l_create_update VARCHAR2(1);
47 l_instance_number VARCHAR2(30);
48 TYPE l_instance_tbl_type IS TABLE OF VARCHAR2(1)
49 INDEX BY VARCHAR2(32760);
50 l_instance_tbl l_instance_tbl_type;
51
52
53 BEGIN
54
55 SAVEPOINT CREATEUPDATE_INST_GEOLOC_INFO;
56
57 -- Standard call to check for call compatibility.
58 IF NOT FND_API.Compatible_API_Call (l_api_version,
59 p_api_version,
60 l_api_name ,
61 G_PKG_NAME ) THEN
62 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
63 END IF;
64
65 -- Initialize API return status to success
66 x_return_status := FND_API.G_RET_STS_SUCCESS;
67
68 FND_MSG_PUB.initialize;
69
70 -- Check the profile option debug_level for debug message reporting
71 l_debug_level:=to_number(fnd_profile.value('CSI_DEBUG_LEVEL'));
72
73
74 csi_t_gen_utility_pvt.build_file_name( p_file_segment1 => 'csi_gis',
75 p_file_segment2 => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
76
77 csi_t_gen_utility_pvt.add('In createupdate_inst_geoloc_info');
78 l_instance_tbl('init'):='Y';
79
80 IF p_csi_instance_geoloc_tbl.COUNT > 0 THEN
81
82 FOR i in p_csi_instance_geoloc_tbl.FIRST..p_csi_instance_geoloc_tbl.LAST LOOP
83
84 BEGIN
85 csi_t_gen_utility_pvt.add( 'Processing Record : '||i);
86 IF l_debug_level > 0 THEN
87 csi_t_gen_utility_pvt.add( 'Instance Id:'||p_csi_instance_geoloc_tbl(i).instance_id);
88 csi_t_gen_utility_pvt.add( 'Latitude:'||p_csi_instance_geoloc_tbl(i).inst_latitude);
89 csi_t_gen_utility_pvt.add( 'Longitude:'||p_csi_instance_geoloc_tbl(i).inst_longitude);
90 csi_t_gen_utility_pvt.add( 'Geocode Format:'||p_csi_instance_geoloc_tbl(i).geocode_format);
91 csi_t_gen_utility_pvt.add( 'Valid Flag:'||p_csi_instance_geoloc_tbl(i).Valid_flag);
92 END IF;
93
94 csi_t_gen_utility_pvt.add( 'calling validate instance');
95
96 IF p_csi_instance_geoloc_tbl(i).instance_id IS NULL
97 OR p_csi_instance_geoloc_tbl(i).instance_id = FND_API.G_MISS_NUM THEN
98 IF p_asset_context = 'EAM' THEN
99 FND_MESSAGE.SET_NAME('CSI','CSI_ASSET_NOT_NULL');
100 ELSE
101 FND_MESSAGE.SET_NAME('CSI','CSI_INSTANCE_NOT_NULL');
102 END IF;
103 FND_MSG_PUB.ADD;
104 x_return_status := FND_API.G_RET_STS_ERROR;
105 l_error_count:=l_error_count+1;
106 RAISE FND_API.G_EXC_ERROR;
107 END IF;
108
109 /*validate Asset/instance */
110 CSI_GIS_INSTANCE_LOC_UTL_PKG.VALIDATE_INSTANCE_NUMBER
111 (p_instance_id => p_csi_instance_geoloc_tbl(i).instance_id
112 ,p_asset_context => p_asset_context
113 ,x_instance_number => l_instance_number
114 ,x_create_update => l_create_update
115 ,x_return_status => l_return_status
116 ,x_msg_count => l_msg_count
117 ,x_msg_data => l_msg_data
118 );
119
120 IF (TRIM(p_csi_instance_geoloc_tbl(i).inst_latitude) = 'DMS'
121 AND TRIM(p_csi_instance_geoloc_tbl(i).inst_longitude) = 'DMS') THEN
122
123 IF l_create_update = 'U' THEN
124 IF (l_debug_level > 0) THEN
125 csi_t_gen_utility_pvt.add( 'Invalidating Instance geo location info');
126 END IF;
127
128
129 CSI_GIS_INSTANCE_GEO_LOC_PVT.UPDATE_ROW
130 ( p_instance_id => p_csi_instance_geoloc_tbl(i).instance_id
131 ,p_inst_latitude => l_inst_latitude_dd_value
132 ,p_inst_longitude => l_inst_longitude_dd_value
133 ,p_valid_flag => 'N'
134 ,x_return_status => l_return_status
135 );
136
137 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
138
139 l_msg_data:=fnd_msg_pub.get(fnd_msg_pub.G_LAST, FND_API.G_FALSE);
140 csi_t_gen_utility_pvt.add('Error Message:'||l_msg_data);
141 IF FND_API.To_Boolean(p_commit) THEN
142 UPDATE csi_ii_geoloc_interface
143 SET process_flag = 'E',
144 error_message=l_msg_data,
145 process_date=sysdate
146 WHERE instance_number = (select instance_number
147 FROM CSI_ITEM_INSTANCES
148 WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
149 AND PROCESS_FLAG = 'R';
150 END IF;
151 l_error_count:=l_error_count+1;
152 -- Continue processing other records
153 RAISE FND_API.G_EXC_ERROR;
154 END IF;
155 RAISE FND_API.G_EXC_ERROR;
156 ELSE
157 RAISE FND_API.G_EXC_ERROR;
158 END IF;
159 END IF;
160
161 IF (l_instance_tbl.EXISTS(p_csi_instance_geoloc_tbl(i).instance_id)) THEN
162 IF p_asset_context = 'EAM' THEN
163 FND_MESSAGE.SET_NAME('CSI','CSI_GIS_DUPLICATE_ASSET');
164 ELSE
165 FND_MESSAGE.SET_NAME('CSI','CSI_GIS_DUPLICATE_INSTANCE');
166 END IF;
167 FND_MESSAGE.SET_TOKEN('INSTANCE',l_instance_number);
168 FND_MSG_PUB.ADD;
169 x_return_status := FND_API.G_RET_STS_ERROR;
170 l_error_count:=l_error_count+1;
171 RAISE FND_API.G_EXC_ERROR;
172 END IF;
173
174 l_instance_tbl(p_csi_instance_geoloc_tbl(i).instance_id) := 'Y' ;
175
176 csi_t_gen_utility_pvt.add( 'Return Status:'||l_return_status);
177 IF l_debug_level > 0 THEN
178 csi_t_gen_utility_pvt.add( 'l_create_update:'||l_create_update);
179 csi_t_gen_utility_pvt.add( 'l_instance_number:'||l_instance_number);
180 END IF;
181
182 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
183
184 l_error_count:=l_error_count+1;
185 l_msg_data:=fnd_msg_pub.get(fnd_msg_pub.G_LAST, FND_API.G_FALSE);
186 csi_t_gen_utility_pvt.add('Error Message:'||l_msg_data);
187 IF FND_API.To_Boolean(p_commit) THEN
188 UPDATE csi_ii_geoloc_interface
189 SET process_flag = 'E',
190 error_message=l_msg_data,
191 process_date=sysdate
192 WHERE instance_number = (select instance_number
193 FROM CSI_ITEM_INSTANCES
194 WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
195 AND PROCESS_FLAG = 'R';
196 END IF;
197 -- Continue processing other records
198 RAISE FND_API.G_EXC_ERROR;
199 END IF;
200
201 /*If delete from UI, this invalidates the geolocation information for asset/instance */
202 IF p_CSI_instance_geoloc_tbl(i).valid_flag = 'N' THEN
203
204 IF (l_debug_level > 0) THEN
205 csi_t_gen_utility_pvt.add( 'Invalidating Instance geo location info');
206 END IF;
207
208 CSI_GIS_INSTANCE_GEO_LOC_PVT.UPDATE_ROW
209 ( p_instance_id => p_csi_instance_geoloc_tbl(i).instance_id
210 ,p_inst_latitude => l_inst_latitude_dd_value
211 ,p_inst_longitude => l_inst_longitude_dd_value
212 ,p_valid_flag => p_csi_instance_geoloc_tbl(i).valid_flag
213 ,x_return_status => l_return_status
214 );
215
216 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
217
218 l_msg_data:=fnd_msg_pub.get(fnd_msg_pub.G_LAST, FND_API.G_FALSE);
219 csi_t_gen_utility_pvt.add('Error Message:'||l_msg_data);
220 IF FND_API.To_Boolean(p_commit) THEN
221 UPDATE csi_ii_geoloc_interface
222 SET process_flag = 'E',
223 error_message=l_msg_data,
224 process_date=sysdate
225 WHERE instance_number = (select instance_number
226 FROM CSI_ITEM_INSTANCES
227 WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
228 AND PROCESS_FLAG = 'R';
229 END IF;
230 l_error_count:=l_error_count+1;
231 -- Continue processing other records
232 Return;
233 END IF;
234 Return;
235 END IF;
236
237 csi_t_gen_utility_pvt.add( 'calling validate latitude longitude');
238
239 /*This procedure is called to validate the latitude and longitude values*/
240 CSI_GIS_INSTANCE_LOC_UTL_PKG.VALIDATE_LATITUDE_LONGITUDE
241 (p_latitude => p_csi_instance_geoloc_tbl(i).inst_latitude
242 ,p_longitude => p_csi_instance_geoloc_tbl(i).inst_longitude
243 ,p_geocode_format => p_csi_instance_geoloc_tbl(i).geocode_format
244 ,p_instance_number => l_instance_number
245 ,x_return_status => l_return_status
246 ,x_msg_count => l_msg_count
247 ,x_msg_data => l_msg_data
248 ) ;
249
250 csi_t_gen_utility_pvt.add( 'Return Status:'||l_return_status);
251
252 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
253 l_msg_data:=fnd_msg_pub.get(fnd_msg_pub.G_LAST, FND_API.G_FALSE);
254 csi_t_gen_utility_pvt.add('Error Message:'||l_msg_data);
255
256 IF FND_API.To_Boolean(p_commit) THEN
257 UPDATE csi_ii_geoloc_interface
258 SET process_flag = 'E',
259 error_message=l_msg_data,
260 process_date=sysdate
261 WHERE instance_number = (select instance_number
262 FROM CSI_ITEM_INSTANCES
263 WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
264 AND PROCESS_FLAG = 'R';
265 END IF;
266
267 l_error_count:=l_error_count+1;
268 -- Continue processing other records
269 RAISE FND_API.G_EXC_ERROR;
270 END IF;
271
272 /*IF geocode format is 'DD' no need to convert latitude and longitude values*/
273 IF p_CSI_instance_geoloc_tbl(i).geocode_format IN ('DMS','DM') THEN
274
275 csi_t_gen_utility_pvt.add( 'Convert Latitude value to DD');
276
277 /* calling procedure to convert latitude to DD value */
278 CSI_GIS_INSTANCE_LOC_UTL_PKG.CONVERT_DMS_OR_DM_TO_DD
279 (p_value => p_csi_instance_geoloc_tbl(i).inst_latitude
280 ,p_mode => 'LAT'
281 ,p_geocode_format => p_csi_instance_geoloc_tbl(i).geocode_format
282 ,p_instance_number => l_instance_number
283 ,x_value => l_inst_latitude_dd_value
284 ,x_return_status => l_return_status
285 ,x_msg_count => l_msg_count
286 ,x_msg_data => l_msg_data
287 );
288
289 csi_t_gen_utility_pvt.add( 'Return Status:'||l_return_status);
290
291 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
292 l_msg_data:=fnd_msg_pub.get(fnd_msg_pub.G_LAST, FND_API.G_FALSE);
293 csi_t_gen_utility_pvt.add('Error Message:'||l_msg_data);
294 IF FND_API.To_Boolean(p_commit) THEN
295 UPDATE csi_ii_geoloc_interface
296 SET process_flag = 'E',
297 error_message=l_msg_data,
298 process_date=sysdate
299 WHERE instance_number = (select instance_number
300 FROM CSI_ITEM_INSTANCES
301 WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
302 AND PROCESS_FLAG = 'R';
303 END IF;
304 l_error_count:=l_error_count+1;
305 -- Continue processing other records
306 RAISE FND_API.G_EXC_ERROR;
310
307 END IF;
308
309 csi_t_gen_utility_pvt.add( 'Convert Latitude value to DD');
311 /* calling procedure to convert longitude to DD value */
312 CSI_GIS_INSTANCE_LOC_UTL_PKG.CONVERT_DMS_OR_DM_TO_DD
313 (p_value => p_csi_instance_geoloc_tbl(i).inst_longitude
314 ,p_mode => 'LON'
315 ,p_geocode_format => p_csi_instance_geoloc_tbl(i).geocode_format
316 ,p_instance_number => l_instance_number
317 ,x_value => l_inst_longitude_dd_value
318 ,x_return_status => l_return_status
319 ,x_msg_count => l_msg_count
320 ,x_msg_data => l_msg_data
321 );
322
323 csi_t_gen_utility_pvt.add( 'Return Status:'||l_return_status);
324
325 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
326 l_msg_data:=fnd_msg_pub.get(fnd_msg_pub.G_LAST, FND_API.G_FALSE);
327 csi_t_gen_utility_pvt.add('Error Message:'||l_msg_data);
328 IF FND_API.To_Boolean(p_commit) THEN
329 UPDATE csi_ii_geoloc_interface
330 SET process_flag = 'E',
331 error_message=l_msg_data,
332 process_date=sysdate
333 WHERE instance_number = (select instance_number
334 FROM CSI_ITEM_INSTANCES
335 WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
336 AND PROCESS_FLAG = 'R';
337 END IF;
338 l_error_count:=l_error_count+1;
339 -- Continue processing other records
340 RAISE FND_API.G_EXC_ERROR;
341 END IF;
342 ELSIF p_CSI_instance_geoloc_tbl(i).geocode_format IN ('DD') THEN
343
344 l_inst_latitude_dd_value:=to_number(p_csi_instance_geoloc_tbl(i).inst_latitude);
345 l_inst_longitude_dd_value:=to_number(p_csi_instance_geoloc_tbl(i).inst_longitude);
346
347 END IF;
348
349 /*l_create_update = 'C' indicates that latitude and longitude values are being entered for the first time for asset/instance */
350 IF l_create_update = 'C' THEN
351
352 csi_t_gen_utility_pvt.add( 'Calling Insert row');
353
354 CSI_GIS_INSTANCE_GEO_LOC_PVT.INSERT_ROW
355 ( p_instance_id => p_CSI_instance_geoloc_tbl(i).instance_id
356 ,p_inst_latitude => l_inst_latitude_dd_value
357 ,p_inst_longitude => l_inst_longitude_dd_value
358 , x_return_status => l_return_status
359 );
360
361 csi_t_gen_utility_pvt.add( 'Return Status:'||l_return_status);
362
363 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
364 l_msg_data:=fnd_msg_pub.get(fnd_msg_pub.G_LAST, FND_API.G_FALSE);
365 csi_t_gen_utility_pvt.add('Error Message:'||l_msg_data);
366 IF FND_API.To_Boolean(p_commit) THEN
367 UPDATE csi_ii_geoloc_interface
368 SET process_flag = 'E',
369 error_message=l_msg_data,
370 process_date=sysdate
371 WHERE instance_number = (select instance_number
372 FROM CSI_ITEM_INSTANCES
373 WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
374 AND PROCESS_FLAG = 'R';
375 END IF;
376 l_error_count:=l_error_count+1;
377 -- Continue processing other records
378 RAISE FND_API.G_EXC_ERROR;
379 END IF;
380
381 IF FND_API.To_Boolean( p_commit ) THEN
382 UPDATE csi_ii_geoloc_interface
383 SET process_flag = 'P',
384 process_date = sysdate
385 WHERE instance_number = (select instance_number
386 FROM CSI_ITEM_INSTANCES
387 WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
388 AND PROCESS_FLAG = 'R';
389 COMMIT WORK;
390 csi_t_gen_utility_pvt.add( 'Completed Processing Record#:'||i);
391 END IF;
392
393 /*l_create_update = 'U' indicates that latitude and longitude values are already present for the asset/instance and they are just being updated*/
394 ELSIF l_create_update = 'U' THEN
395
396 csi_t_gen_utility_pvt.add( 'Calling Update row');
397 /* Updating information for an existing instance */
398 CSI_GIS_INSTANCE_GEO_LOC_PVT.UPDATE_ROW
399 ( p_instance_id => p_CSI_instance_geoloc_tbl(i).instance_id
400 ,p_inst_latitude => l_inst_latitude_dd_value
401 ,p_inst_longitude => l_inst_longitude_dd_value
402 ,p_valid_flag => p_CSI_instance_geoloc_tbl(i).valid_flag
403 , x_return_status => l_return_status
404 );
405
406 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
407 l_msg_data:=fnd_msg_pub.get(fnd_msg_pub.G_LAST, FND_API.G_FALSE);
408 csi_t_gen_utility_pvt.add(l_msg_data);
409 IF FND_API.To_Boolean(p_commit) THEN
410 UPDATE csi_ii_geoloc_interface
411 SET process_flag = 'E',
412 error_message=l_msg_data,
413 process_date = sysdate
414 WHERE instance_number = (select instance_number
415 FROM CSI_ITEM_INSTANCES
416 WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
417 AND PROCESS_FLAG = 'R';
418 END IF;
419 l_error_count:=l_error_count + 1;
420 -- Continue processing other records
421 RAISE FND_API.G_EXC_ERROR;
422 END IF;
423
424 IF FND_API.To_Boolean( p_commit ) THEN
425 UPDATE csi_ii_geoloc_interface
426 SET process_flag = 'P',
427 process_date = sysdate
428 WHERE instance_number = (select instance_number
432 COMMIT WORK;
429 FROM CSI_ITEM_INSTANCES
430 WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
431 AND PROCESS_FLAG = 'R' ;
433 csi_t_gen_utility_pvt.add( 'Completed Processing Record#:'||i);
434 END IF;
435 END IF;
436 EXCEPTION
437 WHEN FND_API.G_EXC_ERROR THEN
438
439 NULL;
440 END;
441 END LOOP;
442 END IF;
443
444 IF FND_API.To_Boolean( p_commit ) THEN
445 csi_t_gen_utility_pvt.add( 'Returning to Import API');
446 return;
447 END IF;
448
449 IF l_error_count > 0 THEN
450 RAISE FND_API.G_EXC_ERROR;
451 END IF;
452
453 COMMIT WORK;
454
455 csi_t_gen_utility_pvt.add( 'Completed Processing Records');
456
457 EXCEPTION
458
459 WHEN FND_API.G_EXC_ERROR THEN
460 x_return_status := FND_API.G_RET_STS_ERROR ;
461 csi_t_gen_utility_pvt.add( 'Error in Processing Records');
462 csi_t_gen_utility_pvt.add( 'Error Count:'||l_error_count);
463 ROLLBACK TO CREATEUPDATE_INST_GEOLOC_INFO;
464 FND_MSG_PUB.Count_And_Get
465 ( p_encoded => FND_API.G_FALSE,
466 p_count => x_msg_count,
467 p_data => x_msg_data
468 );
469
470 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
472 csi_t_gen_utility_pvt.add( 'Error in Processing Records');
473 ROLLBACK TO CREATEUPDATE_INST_GEOLOC_INFO;
474 FND_MSG_PUB.Count_And_Get
475 ( p_encoded => FND_API.G_FALSE,
476 p_count => x_msg_count,
477 p_data => x_msg_data
478 );
479
480 WHEN OTHERS THEN
481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
482 csi_t_gen_utility_pvt.add( 'Error in Processing Records');
483 ROLLBACK TO CREATEUPDATE_INST_GEOLOC_INFO;
484 IF FND_MSG_PUB.Check_Msg_Level
485 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
486 THEN
487 FND_MSG_PUB.Add_Exc_Msg
488 (G_PKG_NAME,
489 l_api_name
490 );
491 END IF;
492 FND_MSG_PUB.Count_And_Get
493 ( p_encoded => FND_API.G_FALSE,
494 p_count => x_msg_count,
495 p_data => x_msg_data
496 );
497
498 END CREATEUPDATE_INST_GEOLOC_INFO;
499
500 PROCEDURE IMPORT_INSTANCE_GEO_LOCATION
501 (
502 p_api_version IN NUMBER,
503 p_commit IN VARCHAR2 := FND_API.G_TRUE ,
504 x_return_status OUT NOCOPY VARCHAR2 ,
505 x_msg_count OUT NOCOPY NUMBER ,
506 x_msg_data OUT NOCOPY VARCHAR2
507 ) IS
508 l_api_name CONSTANT VARCHAR2(30) := 'IMPORT_INSTANCE_GEO_LOCATION';
509 l_api_version CONSTANT NUMBER := 1.0;
510 l_debug_level NUMBER;
511 l_return_status VARCHAR2(1);
512 l_record_status VARCHAR2(1);
513 l_error_count NUMBER:=0;
514 l_msg_count NUMBER;
515 l_msg_data VARCHAR2(4000);
516 l_index BINARY_INTEGER:=0;
517 l_instance_id NUMBER;
518 l_csi_instance_geoloc_tbl CSI_GIS_INSTANCE_LOC_PUB.CSI_instance_geoloc_tbl_type;
519 l_count NUMBER;
520 l_geocode_format VARCHAR2(3);
521
522 CURSOR import_inst_geo_loc_cur IS
523 SELECT *
524 FROM csi_ii_geoloc_interface
525 WHERE process_flag = 'R';
526
527 TYPE import_inst_geo_loc_tbl_type IS TABLE OF csi_ii_geoloc_interface%ROWTYPE;
528 import_inst_geo_loc_tbl import_inst_geo_loc_tbl_type;
529 BEGIN
530
531 -- Standard call to check for call compatibility.
532 IF NOT FND_API.Compatible_API_Call (l_api_version,
533 p_api_version,
534 l_api_name ,
535 G_PKG_NAME ) THEN
536 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
537 END IF;
538
539 -- Initialize API return status to success
540 x_return_status := FND_API.G_RET_STS_SUCCESS;
541
542 -- Check the profile option debug_level for debug message reporting
543 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
544
545 -- If debug_level >0 then dump the procedure name
546 IF (l_debug_level > 0) THEN
547 csi_t_gen_utility_pvt.add( 'IMPORT_INSTANCE_GEO_LOCATION');
548 END IF;
549
550 OPEN import_inst_geo_loc_cur;
551 FETCH import_inst_geo_loc_cur BULK COLLECT INTO import_inst_geo_loc_tbl;
552 CLOSE import_inst_geo_loc_cur;
553
554
555
556 FOR i IN import_inst_geo_loc_tbl.FIRST..import_inst_geo_loc_tbl.LAST LOOP
557
558 BEGIN
559 SELECT Count(instance_number)
560 INTO l_count
561 FROM csi_ii_geoloc_interface
562 WHERE instance_number = import_inst_geo_loc_tbl(i).instance_number
563 AND PROCESS_FLAG = 'R';
564
565 /*checking for duplicate instances*/
566 IF l_count > 1 OR l_count = 0 THEN
567 l_error_count := l_error_count + 1;
568 l_record_status:='E';
569 UPDATE csi_ii_geoloc_interface
570 SET process_flag = 'E',
571 error_message = FND_MESSAGE.Get_String('CSI', 'CSI_GIS_DUP_INST_ASSET'),
572 process_date=sysdate
573 WHERE instance_number = import_inst_geo_loc_tbl(i).instance_number
574 AND PROCESS_FLAG = 'R';
575 RAISE FND_API.G_EXC_ERROR;
576 END IF;
577
578 BEGIN
579 SELECT instance_id
580 INTO l_instance_id
581 FROM csi_item_instances
582 WHERE instance_number = import_inst_geo_loc_tbl(i).instance_number;
583 EXCEPTION
584 WHEN NO_DATA_FOUND THEN
585
586 l_error_count := l_error_count + 1;
587 l_record_status:='E';
588 UPDATE csi_ii_geoloc_interface
589 SET process_flag = 'E',
590 error_message = FND_MESSAGE.Get_String('CSI', 'CSI_INSTANCE_NOT_FOUND'),
591 process_date=sysdate
592 WHERE instance_number = import_inst_geo_loc_tbl(i).instance_number
593 AND PROCESS_FLAG = 'R';
594 RAISE FND_API.G_EXC_ERROR;
595
596 END;
597
598 /*checking for valid geocode formats*/
599
600 l_geocode_format:=Trim(import_inst_geo_loc_tbl(i).geocode_format);
601
602 IF NOT( l_geocode_format = 'DMS'
603 OR l_geocode_format = 'DM'
604 OR l_geocode_format = 'DD'
605 OR l_geocode_format IS NULL
606 OR Length(l_geocode_format)IS NULL) THEN
607
608 l_error_count := l_error_count + 1;
609 l_record_status:='E';
610 UPDATE csi_ii_geoloc_interface
611 SET process_flag = 'E',
612 error_message = FND_MESSAGE.Get_String('CSI', 'CSI_INVALID_GEOCODE_FORMAT'),
613 process_date=sysdate
614 WHERE instance_number = import_inst_geo_loc_tbl(i).instance_number
615 AND PROCESS_FLAG = 'R';
616 RAISE FND_API.G_EXC_ERROR;
617 END IF;
618
619 l_index:=l_index + 1;
620 l_csi_instance_geoloc_tbl(l_index).INSTANCE_ID :=l_instance_id;
621 l_csi_instance_geoloc_tbl(l_index).INST_LATITUDE :=import_inst_geo_loc_tbl(i).inst_latitude;
622 l_csi_instance_geoloc_tbl(l_index).INST_LONGITUDE :=import_inst_geo_loc_tbl(i).inst_longitude;
623 IF Length(l_geocode_format)IS NULL OR l_geocode_format IS NULL THEN
624 l_csi_instance_geoloc_tbl(l_index).GEOCODE_FORMAT := 'DMS';
625 ELSE
626 l_csi_instance_geoloc_tbl(l_index).GEOCODE_FORMAT :=import_inst_geo_loc_tbl(i).geocode_format;
627 END IF;
628 EXCEPTION
629 WHEN FND_API.G_EXC_ERROR THEN
630 NULL;
631 END;
632 END LOOP;
633
634 /*calling create update API*/
635 IF l_csi_instance_geoloc_tbl.count > 0 THEN
636
637 CSI_GIS_INSTANCE_LOC_PUB.CREATEUPDATE_INST_GEOLOC_INFO(p_api_version => 1,
638 p_commit => FND_API.G_TRUE
639 ,p_csi_instance_geoloc_tbl => l_csi_instance_geoloc_tbl
640 ,x_return_status => l_return_status
641 ,x_msg_count => l_msg_count
642 ,x_msg_data => l_msg_data);
643 END IF;
644
645 csi_t_gen_utility_pvt.add( 'No of errored records: '||to_char(l_msg_count + l_error_count));
646 csi_t_gen_utility_pvt.add( 'Completed processing records');
647
648 END IMPORT_INSTANCE_GEO_LOCATION;
649
650 END CSI_GIS_INSTANCE_LOC_PUB;