[Home] [Help]
PACKAGE BODY: APPS.CSF_TASK_ADDRESS_PVT
Source
1 PACKAGE BODY CSF_TASK_ADDRESS_PVT AS
2 /* $Header: CSFVTADB.pls 120.39.12020000.8 2013/04/18 11:55:47 kramini ship $ */
3 g_debug_enabled varchar2(1);
4 g_debug_level NUMBER;
5 TYPE REF_CURSOR IS REF CURSOR;
6 PROCEDURE dbgl (p_msg_data VARCHAR2);
7
8 PROCEDURE put_stream (p_handle IN NUMBER, p_msg_data IN VARCHAR2);
9
10 PROCEDURE show_messages (p_msg_data VARCHAR2);
11
12 PROCEDURE success_log_info (
13 p_api_version IN NUMBER,
14 p_init_msg_list IN VARCHAR2,
15 p_commit IN VARCHAR2,
16 p_validation_level IN NUMBER,
17 p_task_rec IN task_rec_type,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2
21 );
22
23 procedure init_package is
24 begin
25 g_debug_enabled := nvl(fnd_profile.value('AFLOG_ENABLED'), 'N');
26 g_debug_level := nvl(fnd_profile.value('AFLOG_LEVEL'), fnd_log.level_event);
27 END init_package;
28
29 procedure debug(p_message varchar2, p_module varchar2, p_level number) is
30 begin
31 IF g_debug_enabled = 'Y' AND p_level >= g_debug_level THEN
32 IF fnd_file.log > 0 THEN
33 IF p_message = ' ' THEN
34 fnd_file.put_line(fnd_file.log, '');
35 ELSE
36 fnd_file.put_line(fnd_file.log, rpad(p_module, 20) || ': ' || p_message);
37 END IF;
38 ELSE
39 fnd_log.string(p_level, 'csf.plsql.CSF_TASK_ADDRESS_PVT.' || p_module, p_message);
40 END IF;
41 END IF;
42 --dbms_output.put_line(rpad(p_module, 20) || ': ' || p_message);
43 END debug;
44
45 PROCEDURE update_geometry (p_location_id IN NUMBER)
46 IS
47 PRAGMA AUTONOMOUS_TRANSACTION;
48 BEGIN
49 UPDATE hz_locations
50 SET geometry = NULL
51 WHERE location_id = p_location_id;
52
53 COMMIT;
54 END;
55
56 PROCEDURE validate_address (
57 errbuf OUT NOCOPY VARCHAR2,
58 retcode OUT NOCOPY VARCHAR2,
59 p_start_date in varchar2 default null,
60 p_end_date in varchar2 default null,
61 p_unstamped_only in varchar2 default 'Yes'
62 )
63 IS
64 l_api_name CONSTANT VARCHAR2 (30) := 'VALIDATE_ADDRESS';
65 l_api_version CONSTANT NUMBER := 1.0;
66 -- predefined error codes for concurrent programs
67 l_rc_succ CONSTANT NUMBER := 0;
68 l_rc_warn CONSTANT NUMBER := 1;
69 l_rc_err CONSTANT NUMBER := 2;
70 -- predefined error buffer output strings (replaced by translated messages)
71 l_msg_succ VARCHAR2 (80);
72 l_msg_warn VARCHAR2 (80);
73 l_msg_err VARCHAR2 (80);
74 --
75 -- the date range
76 --
77 l_start_date DATE;
78 l_end_date DATE;
79 --
80 -- date format mask for output message
81 --
82 l_fmt VARCHAR2 (100);
83 i NUMBER;
84 l_result VARCHAR2 (30);
85 l_locus MDSYS.SDO_GEOMETRY;
86 l_validated_flag VARCHAR2 (30);
87 l_task_rec task_rec_type;
88 l_task_rec_tbl task_rec_tbl_type;
89 l_return_status VARCHAR2 (1);
90 l_msg_count NUMBER;
91 l_msg_data VARCHAR2 (2000);
92 l_dup_msg_data VARCHAR2 (2000);
93 x_app_name VARCHAR2 (32767);
94 x_msg_name VARCHAR2 (32767);
95 l_time_zone_check BOOLEAN;
96 l_timezone_id VARCHAR2 (80);
97
98 CURSOR c_timezone_check (p_timezone_id NUMBER)
99 IS
100 SELECT NAME
101 FROM fnd_timezones_vl
102 WHERE timezone_code = p_timezone_id;
103 BEGIN
104 -- Initialize message list
105 fnd_msg_pub.initialize;
106 -- get termination messages
107 fnd_message.set_name ('CSF', 'CSF_GST_DONE_SUCC');
108 l_msg_succ := fnd_message.get;
109 fnd_message.set_name ('CSF', 'CSF_GST_DONE_WARN');
110 l_msg_warn := fnd_message.get;
111 fnd_message.set_name ('CSF', 'CSF_GST_DONE_ERR');
112 l_msg_err := fnd_message.get;
113 -- Initialize API return status to success
114 retcode := l_rc_succ;
115 errbuf := l_msg_succ;
116 -- API body
117 fnd_message.set_name ('CSF', 'CSF_FIND_INVALID_ADDRESS');
118 l_msg_data := fnd_message.get;
119 put_stream (g_output, l_msg_data);
120
121 --
122 -- start date defaults to today (truncated)
123 -- later converted back to server timezone
124 -- e.g. client timezone is CET (GMT+1)
125 -- server timezone is PST (GMT-8)
126 -- If it is 6-Aug 06:00 for the client, then it is 5-Aug 21:00 for the
127 -- server, and trunc(sysdate) will give 5-Aug instead of 6-Aug. Hence
128 -- we need to convert to client timezone before truncating.
129 -- When the parameter *is* specified, in the same case it will already
130 -- read 6-aug-2003.
131 --
132 IF p_start_date IS NULL
133 THEN
134 l_start_date :=
135 TRUNC (csf_timezones_pvt.date_to_client_tz_date (SYSDATE));
136 -- convert to server timezone
137 l_start_date :=
138 csf_timezones_pvt.date_to_server_tz_date (l_start_date);
139 ELSE
140 -- all fnd_date converts to server timezone so need for conversion
141 l_start_date := fnd_date.canonical_to_date (p_start_date);
142 END IF;
143
144 --
145 -- end date defaults to same day as start date (also truncated)
146 --
147 IF p_end_date IS NULL
148 THEN
149 l_end_date := l_start_date + 15;
150 ELSE
151 l_end_date := fnd_date.canonical_to_date (p_end_date);
152 END IF;
153
154 --
155 -- get date format
156 l_fmt := fnd_profile.VALUE ('ICX_DATE_FORMAT_MASK');
157
158 IF l_fmt IS NULL
159 THEN
160 l_fmt := 'dd-MON-yyyy';
161 END IF;
162
163 --
164 -- feedback the date range
165 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_DATE_RANGE');
166 fnd_message.set_token ('P_START_DATE', TO_CHAR (l_start_date, l_fmt));
167 fnd_message.set_token ('P_END_DATE', TO_CHAR (l_end_date, l_fmt));
168 put_stream (g_output, fnd_message.get);
169 --
170 -- finally convert the date range to server timezone before processing
171 --
172 l_start_date := csf_timezones_pvt.date_to_server_tz_date (l_start_date);
173 l_end_date := csf_timezones_pvt.date_to_server_tz_date (l_end_date);
174 retrieve_data (p_api_version => 1.0,
175 p_init_msg_list => fnd_api.g_false,
176 p_commit => fnd_api.g_false,
177 p_validation_level => fnd_api.g_valid_level_full,
178 p_start_date => l_start_date,
179 p_end_date => l_end_date,
180 x_task_rec_tbl => l_task_rec_tbl,
181 x_return_status => l_return_status,
182 x_msg_count => l_msg_count,
183 x_msg_data => l_msg_data,
184 p_unstamped_only => p_unstamped_only
185 );
186
187 IF l_return_status <> fnd_api.g_ret_sts_success
188 THEN
189 fnd_message.set_name ('CSF', 'CSF_RETRIEVE_DATA_ERROR');
190 l_msg_data := fnd_message.get;
191 put_stream (g_log, l_msg_data);
192 RAISE fnd_api.g_exc_error;
193 END IF;
194
195 IF l_task_rec_tbl.COUNT > 0
196 THEN
197 i := l_task_rec_tbl.FIRST;
198
199 WHILE i IS NOT NULL
200 LOOP
201 l_task_rec := l_task_rec_tbl (i);
202
203 /* IF l_task_rec.timezone_id IS NULL
204 THEN
205 l_time_zone_check := FALSE;
206 ELSE
207 OPEN c_timezone_check (l_task_rec.timezone_id);
208
209 FETCH c_timezone_check
210 INTO l_timezone_id;
211
212 IF l_timezone_id IS NOT NULL
213 THEN
214 l_time_zone_check := TRUE;
215 ELSE
216 l_time_zone_check := FALSE;
217 END IF;
218
219 CLOSE c_timezone_check;
220 END IF;
221
222 IF l_time_zone_check
223 THEN */
224
225 csf_resource_address_pvt.resolve_address
226 (p_api_version => 1.0,
227 p_init_msg_list => fnd_api.g_false,
228 p_country => NVL (l_task_rec.country,
229 '_'
230 ),
231 p_state => NVL (l_task_rec.state,
232 '_'
233 ),
234 p_city => NVL (l_task_rec.city,
235 '_'
236 ),
237 p_county => NVL (l_task_rec.county,
238 '_'
239 ),
240 p_province => NVL (l_task_rec.province,
241 '_'
242 ),
243 p_postalcode => NVL
244 (l_task_rec.postal_code,
245 '_'
246 ),
247 p_address1 => NVL (l_task_rec.address1,
248 '_'
249 ),
250 p_address2 => NVL (l_task_rec.address2,
251 '_'
252 ),
253 p_address3 => NVL (l_task_rec.address3,
254 '_'
255 ),
256 p_address4 => NVL (l_task_rec.address4,
257 '_'
258 ),
259 p_building_num => '_',
260 p_alternate => '_',
261 p_location_id => NVL
262 (l_task_rec.location_id,
263 -1
264 ),
265 p_country_code => NVL
266 (l_task_rec.country_code,
267 '_'
268 ),
269 x_return_status => l_return_status,
270 x_msg_count => l_msg_count,
271 x_msg_data => l_msg_data,
272 x_geometry => l_locus,
273 p_update_address => 'F'
274 );
275
276 if l_return_status <> fnd_api.g_ret_sts_success
277 then
278 csf_lf_geopvt.move_invalid_to_geo_batch(l_task_rec);
279 update_geometry (l_task_rec.location_id);
280 l_dup_msg_data := l_msg_data;
281 fnd_message.set_name ('CSF', 'CSF_RESOLVE_ADDRESS_ERROR');
282 fnd_message.set_token ('RETURN_STATUS', l_return_status);
283 fnd_message.set_token ('LOCATION_ID', l_task_rec.location_id);
284 l_msg_data := fnd_message.get;
285 put_stream (g_log, l_msg_data);
286 l_task_rec.validated_flag := g_valid_false;
287 l_task_rec.override_flag := g_valid_false;
288 /*log_info (p_api_version => 1.0,
289 p_init_msg_list => fnd_api.g_false,
290 p_commit => fnd_api.g_true,
291 p_validation_level => fnd_api.g_valid_level_full,
292 p_task_rec => l_task_rec,
293 x_return_status => l_return_status,
294 x_msg_count => l_msg_count,
295 x_msg_data => l_msg_data
296 ); */
297 log_info_detail (p_api_version => 1.0,
298 p_init_msg_list => fnd_api.g_false,
299 p_commit => fnd_api.g_true,
300 p_validation_level => fnd_api.g_valid_level_full,
301 p_task_rec => l_task_rec,
302 x_return_status => l_return_status,
303 x_msg_count => l_msg_count,
304 x_msg_data => l_msg_data,
305 x_dup_msg_data => l_dup_msg_data );
306 IF l_return_status <> fnd_api.g_ret_sts_success
307 THEN
308 fnd_message.set_name ('CSF', 'CSF_LOG_INFO_ERROR');
309 fnd_message.set_token ('LOCATION_ID',
310 l_task_rec.location_id
311 );
312 fnd_message.set_token ('RETURN_STATUS', l_return_status);
313 l_msg_data := fnd_message.get;
314 put_stream (g_log, l_msg_data);
315 put_stream (g_output, l_msg_data);
316 RAISE fnd_api.g_exc_error;
317 END IF;
318 ELSIF l_return_status = fnd_api.g_ret_sts_success
319 THEN
320 l_task_rec.validated_flag := g_valid_true;
321 l_task_rec.override_flag := g_valid_true;
322 success_log_info (p_api_version => 1.0,
323 p_init_msg_list => fnd_api.g_false,
324 p_commit => fnd_api.g_true,
325 p_validation_level => fnd_api.g_valid_level_full,
326 p_task_rec => l_task_rec,
327 x_return_status => l_return_status,
328 x_msg_count => l_msg_count,
329 x_msg_data => l_msg_data
330 );
331 IF l_return_status <> fnd_api.g_ret_sts_success
332 THEN
333 fnd_message.set_name ('CSF', 'CSF_LOG_INFO_ERROR');
334 fnd_message.set_token ('LOCATION_ID', l_task_rec.location_id);
335 fnd_message.set_token ('RETURN_STATUS', l_return_status);
336 l_msg_data := fnd_message.get;
337 put_stream (g_log, l_msg_data);
338 put_stream (g_output, l_msg_data);
339 RAISE fnd_api.g_exc_error;
340 END IF;
341 END IF; -- resolve_address
342 /* ELSE -- l_Time_zone_check false
343 update_geometry (l_task_rec.location_id);
344 l_return_status := fnd_api.g_ret_sts_unexp_error;
345 fnd_message.set_name ('CSF', 'CSF_TIME_ZONE_ERROR');
346 fnd_message.set_token ('RETURN_STATUS', l_return_status);
347 fnd_message.set_token ('LOCATION_ID', l_task_rec.location_id);
348 log_info (p_api_version => 1.0,
349 p_init_msg_list => fnd_api.g_false,
350 p_commit => fnd_api.g_true,
351 p_validation_level => fnd_api.g_valid_level_full,
352 p_task_rec => l_task_rec,
353 x_return_status => l_return_status,
354 x_msg_count => l_msg_count,
355 x_msg_data => l_msg_data
356 );
357
358 IF l_return_status <> fnd_api.g_ret_sts_success
359 THEN
360 fnd_message.set_name ('CSF', 'CSF_LOG_INFO_ERROR');
361 fnd_message.set_token ('LOCATION_ID', l_task_rec.location_id);
362 fnd_message.set_token ('RETURN_STATUS', l_return_status);
363 l_msg_data := fnd_message.get;
364 put_stream (g_log, l_msg_data);
365 put_stream (g_output, l_msg_data);
366 RAISE fnd_api.g_exc_error;
367 END IF;
368 END IF; -- l_Time_zone_check */
369
370 i := l_task_rec_tbl.NEXT (i);
371 END LOOP;
372 ELSIF l_task_rec_tbl.COUNT = 0
373 THEN
374 fnd_message.set_name ('CSF', 'CSF_NO_DATA_TOPROCESS');
375 l_msg_data := fnd_message.get;
376 put_stream (g_log, l_msg_data);
377 END IF;
378
379 put_stream (g_log, l_msg_succ);
380 put_stream (g_output, l_msg_succ);
381 -- End of API body
382
383 -- Standard call to get message count and return the message info if the count is 1
384 fnd_msg_pub.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
385 EXCEPTION
386 WHEN fnd_api.g_exc_error
387 THEN
388 retcode := l_rc_err;
389 errbuf := l_msg_err;
390 fnd_msg_pub.count_and_get (p_count => l_msg_count,
391 p_data => l_msg_data
392 );
393 WHEN fnd_api.g_exc_unexpected_error
394 THEN
395 retcode := l_rc_err;
396 errbuf := l_msg_err;
397 fnd_msg_pub.count_and_get (p_count => l_msg_count,
398 p_data => l_msg_data
399 );
400 WHEN OTHERS
401 THEN
402 retcode := l_rc_err;
403 errbuf := l_msg_err;
404
405 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
406 THEN
407 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
408 END IF;
409
410 fnd_msg_pub.count_and_get (p_count => l_msg_count,
411 p_data => l_msg_data);
412 END validate_address;
413
414 PROCEDURE log_info_detail (
415 p_api_version IN NUMBER,
416 p_init_msg_list IN VARCHAR2,
417 p_commit IN VARCHAR2,
418 p_validation_level IN NUMBER,
419 p_task_rec IN task_rec_type,
420 x_return_status OUT NOCOPY VARCHAR2,
421 x_msg_count OUT NOCOPY NUMBER,
422 x_msg_data OUT NOCOPY VARCHAR2,
423 x_dup_msg_data IN OUT NOCOPY VARCHAR2
424 )
425 IS
426 l_api_name CONSTANT VARCHAR2 (30) := 'LOG_INFO_DETAIL';
427 l_api_version CONSTANT NUMBER := 1.0;
428 l_msg_data VARCHAR2 (32767);
429 l_error VARCHAR2 (2000);
430 l_error_detail VARCHAR2 (2000);
431 l_exp_accuracy_level VARCHAR2(5) DEFAULT '0';
432
433 CURSOR c_check_ext_locations (l_task_id csf_ext_locations.task_id%TYPE)
434 IS
435 SELECT task_id
436 FROM csf_ext_locations
437 WHERE task_id = l_task_id;
438
439 l_location_id hz_locations.location_id%TYPE;
440 l_task_id csf_ext_locations.task_id%TYPE;
441 BEGIN
442 -- Standard check for call compatibility
443 IF NOT fnd_api.compatible_api_call (l_api_version,
444 p_api_version,
445 l_api_name,
446 g_pkg_name
447 )
448 THEN
449 RAISE fnd_api.g_exc_unexpected_error;
450 END IF;
451
452 -- Initialize message list if p_init_msg_list is set to TRUE
453 IF fnd_api.to_boolean (p_init_msg_list)
454 THEN
455 fnd_msg_pub.initialize;
456 END IF;
457
458 -- Initialize API return status to success
459 x_return_status := fnd_api.g_ret_sts_success;
460 -- API body
461 fnd_message.set_name ('CSF', 'CSF_INVALID_TASK_INFO');
462 fnd_message.set_token ('TASK_ID', p_task_rec.task_id);
463 fnd_message.set_token ('TASK_NUMBER', p_task_rec.task_number);
464 fnd_message.set_token ('CITY', p_task_rec.city);
465 fnd_message.set_token ('POSTAL_CODE', p_task_rec.postal_code);
466 fnd_message.set_token ('COUNTY', p_task_rec.county);
467 fnd_message.set_token ('STATE', p_task_rec.state);
468 fnd_message.set_token ('COUNTRY', p_task_rec.country);
469 l_msg_data := fnd_message.get;
470 put_stream (g_log, l_msg_data);
471 put_stream (g_output, l_msg_data);
472
473 IF l_location_id IS NULL
474 THEN
475 l_location_id := p_task_rec.location_id;
476 END IF;
477
478 BEGIN
479 OPEN c_check_ext_locations (p_task_rec.task_id);
480
481 FETCH c_check_ext_locations
482 INTO l_task_id;
483
484 CLOSE c_check_ext_locations;
485 EXCEPTION
486 WHEN NO_DATA_FOUND
487 THEN
488 CLOSE c_check_ext_locations;
489 WHEN OTHERS
490 THEN
491 CLOSE c_check_ext_locations;
492 END;
493
494 if (x_dup_msg_data = 'CSF_LF 223634:CSF_LF_INVALID_ADDRESS_DETAILS') then
495 fnd_message.set_name ('CSF', 'CSF_LF_INVALID_ADDRESS_DETAILS');
496 l_error := fnd_message.get;
497 l_error_detail := l_error;
498 put_stream (g_output, l_error_detail);
499 elsif (x_dup_msg_data = 'CSF_LF 223632:CSF_LF_POSTAL_OR_STATE_NOT_RESOLVED') then
500 fnd_message.set_name ('CSF', 'CSF_LF_ZIP_STATE_UNRESOLVED');
501 l_error := fnd_message.get;
502 l_error_detail := l_error;
503 put_stream (g_output, l_error_detail);
504 elsif(x_dup_msg_data = 'CSF_LF 223633:CSF_LF_CITY_OR_STATE_NOT_RESOLVED') then
505 fnd_message.set_name ('CSF', 'CSF_LF_CITY_STATE_UNRESOLVED');
506 l_error := fnd_message.get;
507 l_error_detail := l_error;
508 put_stream (g_output, l_error_detail);
509 elsif(x_dup_msg_data = 'CSF_LF 223630:CSF_LF_ELOC_ADDRESS_UNRESOLVED') then
510 fnd_message.set_name ('CSF', 'CSF_LF_ELOC_ADDR_UNRESOLVED');
511 l_error := fnd_message.get;
512 l_error_detail := l_error;
513 put_stream (g_output, l_error_detail);
514 elsif(x_dup_msg_data = 'CSF_LF 223602:CSF_LF_COUNTRY_NOT_RESOLVED') then
515 fnd_message.set_name ('CSF', 'CSF_COUNTRY_NOT_FOUND_ERROR');
516 fnd_message.set_token ('COUNTRY', p_task_rec.country);
517 l_error := fnd_message.get;
518 l_error_detail := l_error;
519 put_stream (g_output, l_error_detail);
520 elsif(x_dup_msg_data = 'CSF_LF 223629:ADDRESS_ACCURACY_EXCEPTION') then
521 fnd_message.set_name ('CSF', 'CSF_ADDRESS_ACC_FACTOR_ERROR');
522 l_exp_accuracy_level := fnd_profile.VALUE('CSF_LOC_ACC_LEVELS');
523 l_exp_accuracy_level := NVL(l_exp_accuracy_level,'0');
524 if (l_exp_accuracy_level = '0') then
525 fnd_message.set_token ('ACCURACY','zip code or city level accuracy');
526 end if;
527 if (l_exp_accuracy_level = '1') then
528 fnd_message.set_token ('ACCURACY','street level accuracy');
529 end if;
530 if (l_exp_accuracy_level = '2') then
531 fnd_message.set_token ('ACCURACY','building number level accuracy');
532 end if;
533 l_error := fnd_message.get;
534 --x_error_detail := x_error_detail || ' ' || x_error;
535 l_error_detail := l_error;
536 put_stream (g_output, l_error_detail);
537 end if;
538
539
540
541 /* get_error_detail (p_api_version => 1.0,
542 p_init_msg_list => fnd_api.g_false,
543 p_commit => fnd_api.g_false,
544 p_validation_level => fnd_api.g_valid_level_full,
545 p_task_rec => p_task_rec,
546 x_error => l_error,
547 x_error_detail => l_error_detail,
548 x_return_status => x_return_status,
549 x_msg_count => x_msg_count,
550 x_msg_data => x_msg_data
551 );
552
553 IF x_return_status <> fnd_api.g_ret_sts_success
554 THEN
555 fnd_message.set_name ('CSF', 'CSF_GET_ERROR_DETAIL_ERROR');
556 fnd_message.set_token ('LOCATION_ID', p_task_rec.location_id);
557 fnd_message.set_token ('RETURN_STATUS', x_return_status);
558 l_msg_data := fnd_message.get;
559 put_stream (g_log, l_msg_data);
560 put_stream (g_output, l_msg_data);
561 END IF;
562
563 IF l_error IS NULL
564 THEN
565 fnd_message.set_name ('CSF', 'CSF_ADDRESS_INVALID_INFO');
566 l_error := fnd_message.get;
567 END IF;
568
569 IF l_error_detail IS NULL
570 THEN
571 fnd_message.set_name ('CSF', 'CSF_NO_ADDRESS_ENTERED_ERROR');
572 l_error_detail := fnd_message.get;
573 END IF;
574 */
575
576 IF l_task_id IS NULL
577 THEN
578 csf_locations_pkg.insert_row_ext
579 (p_csf_ext_location_id => l_location_id,
580 p_last_update_date => SYSDATE,
581 p_last_updated_by => NVL (fnd_global.user_id,
582 -1
583 ),
584 p_creation_date => SYSDATE,
585 p_created_by => NVL (fnd_global.user_id,
586 -1
587 ),
588 p_last_update_login => NVL
589 (fnd_global.conc_login_id,
590 -1
591 ),
592 p_request_id => NVL
593 (fnd_global.conc_request_id,
594 -1
595 ),
596 p_program_application_id => NVL
597 (fnd_global.prog_appl_id,
598 -1
599 ),
600 p_program_id => NVL
601 (fnd_global.conc_program_id,
602 -1
603 ),
604 p_program_update_date => SYSDATE,
605 p_task_id => p_task_rec.task_id,
606 p_location_id => p_task_rec.location_id,
607 p_validated_flag => p_task_rec.validated_flag,
608 p_override_flag => p_task_rec.override_flag,
609 p_log_detail_short => l_error,
610 p_log_detail_long => l_error_detail
611 );
612 ELSE
613 csf_locations_pkg.update_row_ext
614 (p_csf_ext_location_id => p_task_rec.location_id,
615 p_last_update_date => SYSDATE,
616 p_last_updated_by => NVL (fnd_global.user_id,
617 -1
618 ),
619 p_last_update_login => NVL
620 (fnd_global.conc_login_id,
621 -1
622 ),
623 p_request_id => NVL
624 (fnd_global.conc_request_id,
625 -1
626 ),
627 p_program_application_id => NVL
628 (fnd_global.prog_appl_id,
629 -1
630 ),
631 p_program_id => NVL
632 (fnd_global.conc_program_id,
633 -1
634 ),
635 p_program_update_date => SYSDATE,
636 p_location_id => p_task_rec.location_id,
637 p_validated_flag => p_task_rec.validated_flag,
638 p_override_flag => p_task_rec.override_flag,
639 p_log_detail_short => l_error,
640 p_log_detail_long => l_error_detail
641 );
642 END IF;
643
644 -- End of API body
645
646 -- Standard check of p_commit
647 IF fnd_api.to_boolean (p_commit)
648 THEN
649 COMMIT WORK;
650 END IF;
651
652 -- Standard call to get message count and return the message info if the count is 1
653 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
654 EXCEPTION
655 WHEN fnd_api.g_exc_unexpected_error
656 THEN
657 x_return_status := fnd_api.g_ret_sts_unexp_error;
658 fnd_msg_pub.count_and_get (p_count => x_msg_count,
659 p_data => x_msg_data
660 );
661 WHEN OTHERS
662 THEN
663 x_return_status := fnd_api.g_ret_sts_unexp_error;
664
665 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
666 THEN
667 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
668 END IF;
669
670 fnd_msg_pub.count_and_get (p_count => x_msg_count,
671 p_data => x_msg_data);
672 END log_info_detail;
673
674
675 procedure form_task_address_query(
676 p_dataset in varchar2,
677 p_start_date in date,
678 p_end_date in date,
679 i in out nocopy number,
680 x_task_rec_tbl in out nocopy task_rec_tbl_type,
681 p_task_address_cursor in out nocopy ref_cursor,
682 p_unstamped_only IN VARCHAR2 DEFAULT 'Yes' )
683 IS
684 l_api_name CONSTANT VARCHAR2 (30) := 'form_task_address_query';
685 l_query VARCHAR2(4000);
686 l_geo_coder_url VARCHAR2(50);
687
688 TYPE task_address_rec_type IS RECORD (
689 task_id jtf_tasks_b.task_id%TYPE,
690 task_number jtf_tasks_b.task_number%TYPE,
691 location_id hz_locations.location_id%TYPE,
692 address_style hz_locations.address_style%TYPE,
693 address1 hz_locations.address1%TYPE,
694 address2 hz_locations.address2%TYPE,
695 address3 hz_locations.address3%TYPE,
696 address4 hz_locations.address4%type,
697 city hz_locations.city%TYPE,
698 postal_code hz_locations.postal_code%TYPE,
699 county hz_locations.county%TYPE,
700 state hz_locations.state%TYPE,
701 province hz_locations.province%TYPE,
702 country hz_locations.country%TYPE,
703 country_code VARCHAR2 (30),
704 timezone_id hz_locations.timezone_id%type
705 );
706
707 i_retrieve_task_data task_address_rec_type;
708 BEGIN
709 l_geo_coder_url:= fnd_profile.value('CSF_GEOSPATIAL_SERVICE_URL');
710 IF(l_geo_coder_url is null)
711 THEN
712 l_query := 'SELECT t.task_id,
713 t.task_number,
714 l.location_id,
715 l.address_style,
716 l.address1,
717 l.address2,
718 l.address3,
719 l.address4,
720 l.city,
721 l.postal_code,
722 l.county,
723 l.state,
724 l.province,
725 tl.territory_short_name country,
726 l.country country_code,
727 l.timezone_id
728 FROM jtf_tasks_b t,
729 jtf_task_types_b tt,
730 hz_locations l,
731 fnd_territories_tl tl,
732 jtf_task_statuses_vl jts,
733 csf_sdm_ctry_profiles' || p_dataset || ' cp,
734 csf_spatial_ctry_mappings cm
735 WHERE tt.task_type_id = t.task_type_id
736 AND t.location_id is not null
737 AND l.location_id = t.location_id
738 AND tl.territory_code = l.country
739 and cp.country_code_a3 = cm.spatial_country_code
740 and cm.hr_country_code = l.country
741 AND tl.language = ''US''
742 AND t.source_object_type_code = ''SR''
743 AND t.deleted_flag <> ''Y''
744 AND tt.rule = ''DISPATCH''
745 AND t.task_status_id = jts.task_status_id
746 AND NVL (jts.schedulable_flag, ''N'') = ''Y''
747 AND t.planned_start_date BETWEEN Trunc(:1) AND Trunc(:2) + .99998';
748
749 IF(p_unstamped_only = 'Yes')
750 THEN
751 l_query := l_query || ' AND l.geometry IS NULL';
752 END IF;
753
754 l_query := l_query || ' UNION
755 SELECT t.task_id,
756 t.task_number,
757 l.location_id,
758 l.address_style,
759 l.address1,
760 l.address2,
761 l.address3,
762 l.address4,
763 l.city,
764 l.postal_code,
765 l.county,
766 l.state,
767 l.province,
768 tl.territory_short_name country,
769 l.country country_code,
770 l.timezone_id
771 FROM jtf_tasks_b t,
772 jtf_task_types_b tt,
773 hz_party_sites hps,
774 hz_locations l,
775 fnd_territories_tl tl,
776 jtf_task_statuses_vl jts,
777 csf_sdm_ctry_profiles' || p_dataset || ' cp,
778 csf_spatial_ctry_mappings cm
779 WHERE tt.task_type_id = t.task_type_id
780 AND t.address_id is not null
781 AND t.address_id = hps.party_site_id
782 AND l.location_id = hps.location_id
783 and cp.country_code_a3 = cm.spatial_country_code
784 and cm.hr_country_code = l.country
785 AND tl.territory_code = l.country
786 AND tl.language = ''US''
787 AND t.source_object_type_code = ''SR''
788 AND t.deleted_flag <> ''Y''
789 AND tt.rule = ''DISPATCH''
790 AND t.task_status_id = jts.task_status_id
791 AND NVL (jts.schedulable_flag, ''N'') = ''Y''
792 AND t.planned_start_date BETWEEN Trunc(:3) AND Trunc(:4) + .99998';
793
794 IF(p_unstamped_only = 'Yes')
795 THEN
796 l_query := l_query || ' AND l.geometry IS NULL';
797 END IF;
798 ELSE
799 l_query := 'SELECT t.task_id,
800 t.task_number,
801 l.location_id,
802 l.address_style,
803 l.address1,
804 l.address2,
805 l.address3,
806 l.address4,
807 l.city,
808 l.postal_code,
809 l.county,
810 l.state,
811 l.province,
812 tl.territory_short_name country,
813 l.country country_code,
814 l.timezone_id
815 FROM jtf_tasks_b t,
816 jtf_task_types_b tt,
817 hz_locations l,
818 fnd_territories_tl tl,
819 jtf_task_statuses_vl jts,
820 csf_odf_ctry_mapping coc
821 WHERE tt.task_type_id = t.task_type_id
822 AND t.location_id is not null
823 AND l.location_id = t.location_id
824 AND tl.territory_code = l.country
825 AND tl.language = ''US''
826 AND t.source_object_type_code = ''SR''
827 AND t.deleted_flag <> ''Y''
828 AND tt.rule = ''DISPATCH''
829 AND t.task_status_id = jts.task_status_id
830 AND NVL (jts.schedulable_flag, ''N'') = ''Y''
831 AND tl.territory_code = coc.country_code_2
832 AND t.planned_start_date BETWEEN Trunc(:1) AND Trunc(:2) + .99998';
833
834 IF(p_unstamped_only = 'Yes')
835 THEN
836 l_query := l_query || ' AND l.geometry IS NULL';
837 END IF;
838
839 l_query := l_query || ' UNION
840 SELECT t.task_id,
841 t.task_number,
842 l.location_id,
843 l.address_style,
844 l.address1,
845 l.address2,
846 l.address3,
847 l.address4,
848 l.city,
849 l.postal_code,
850 l.county,
851 l.state,
852 l.province,
853 tl.territory_short_name country,
854 l.country country_code,
855 l.timezone_id
856 FROM jtf_tasks_b t,
857 jtf_task_types_b tt,
858 hz_party_sites hps,
859 hz_locations l,
860 fnd_territories_tl tl,
861 jtf_task_statuses_vl jts,
862 csf_odf_ctry_mapping coc
863 WHERE tt.task_type_id = t.task_type_id
864 AND t.address_id is not null
865 AND t.address_id = hps.party_site_id
866 AND l.location_id = hps.location_id
867 AND tl.territory_code = l.country
868 AND tl.language = ''US''
869 AND t.source_object_type_code = ''SR''
870 AND t.deleted_flag <> ''Y''
871 AND tt.rule = ''DISPATCH''
872 AND t.task_status_id = jts.task_status_id
873 AND NVL (jts.schedulable_flag, ''N'') = ''Y''
874 AND tl.territory_code = coc.country_code_2
875 AND t.planned_start_date BETWEEN Trunc(:3) AND Trunc(:4) + .99998';
876
877 IF(p_unstamped_only = 'Yes')
878 THEN
879 l_query := l_query || ' AND l.geometry IS NULL';
880 END IF;
881 END IF;
882
883 OPEN p_task_address_cursor FOR l_query USING p_start_date, p_end_date, p_start_date, p_end_date;
884 LOOP
885 FETCH p_task_address_cursor INTO i_retrieve_task_data;
886 EXIT WHEN p_task_address_cursor%NOTFOUND;
887 x_task_rec_tbl (i).task_id := i_retrieve_task_data.task_id;
888 x_task_rec_tbl (i).task_number := i_retrieve_task_data.task_number;
889 x_task_rec_tbl (i).location_id := i_retrieve_task_data.location_id;
890 x_task_rec_tbl (i).address1 :=
891 UPPER (i_retrieve_task_data.address1);
892 x_task_rec_tbl (i).address2 :=
893 UPPER (i_retrieve_task_data.address2);
894 x_task_rec_tbl (i).address3 :=
895 UPPER (i_retrieve_task_data.address3);
896 x_task_rec_tbl (i).address4 :=
897 UPPER (i_retrieve_task_data.address4);
898 x_task_rec_tbl (i).address_style :=
899 UPPER (i_retrieve_task_data.address_style);
900 x_task_rec_tbl (i).postal_code :=
901 UPPER (i_retrieve_task_data.postal_code);
902 x_task_rec_tbl (i).city := UPPER (i_retrieve_task_data.city);
903 x_task_rec_tbl (i).province :=
904 UPPER (i_retrieve_task_data.province);
905 x_task_rec_tbl (i).state := UPPER (i_retrieve_task_data.state);
906 x_task_rec_tbl (i).county := UPPER (i_retrieve_task_data.county);
907 x_task_rec_tbl (i).country := UPPER (i_retrieve_task_data.country);
908 x_task_rec_tbl (i).country_code :=
909 UPPER (i_retrieve_task_data.country_code);
910 x_task_rec_tbl (i).timezone_id :=
911 upper (i_retrieve_task_data.timezone_id);
912 i := i + 1;
913 END LOOP;
914 CLOSE p_task_address_cursor;
915 EXCEPTION
916 WHEN OTHERS
917 THEN
918 debug('CSF_TASK_ADDRESS_PVT.form_task_address_query: Error = ' || SQLERRM, l_api_name, fnd_log.level_error);
919 END form_task_address_query;
920
921 PROCEDURE retrieve_data (
922 p_api_version IN NUMBER,
923 p_init_msg_list IN VARCHAR2,
924 p_commit IN VARCHAR2,
925 p_validation_level IN NUMBER,
926 p_start_date IN DATE DEFAULT NULL,
927 p_end_date IN DATE DEFAULT NULL,
928 x_task_rec_tbl OUT NOCOPY task_rec_tbl_type,
929 x_return_status OUT NOCOPY VARCHAR2,
930 x_msg_count OUT NOCOPY NUMBER,
931 x_msg_data out nocopy varchar2,
932 p_unstamped_only IN VARCHAR2 DEFAULT 'Yes'
933 )
934 IS
935 l_api_name CONSTANT VARCHAR2 (30) := 'RETRIEVE_DATA';
936 l_api_version CONSTANT NUMBER := 1.0;
937 i number;
938 l_task_rec task_rec_type;
939 task_address_cursor ref_cursor;
940 l_mds_enabled varchar2(10);
941 l_geo_coder_url varchar2(250);
942
943 cursor mdsListCur is
944 select distinct spatial_dataset from csf_spatial_ctry_mappings;
945
946 BEGIN
947 -- Standard check for call compatibility
948 IF NOT fnd_api.compatible_api_call (l_api_version,
949 p_api_version,
950 l_api_name,
951 g_pkg_name
952 )
953 THEN
954 RAISE fnd_api.g_exc_unexpected_error;
955 END IF;
956
957 -- Initialize message list if p_init_msg_list is set to TRUE
958 IF fnd_api.to_boolean (p_init_msg_list)
959 THEN
960 fnd_msg_pub.initialize;
961 END IF;
962
963 -- Initialize API return status to success
964 x_return_status := fnd_api.g_ret_sts_success;
965 -- API body
966 i := 1;
967
968
969 l_mds_enabled:= fnd_profile.value('CSF_SPATIAL_MULTIDATASET_ENABLED');
970 l_geo_coder_url:= fnd_profile.value('CSF_GEOSPATIAL_SERVICE_URL');
971 --If Single dataset, select tasks which belongs to that dataset
972 --If Multi dataset, select tasks which belongs to datasets listed in csf_spatial_ctry_mappings
973 if( l_mds_enabled = 'N' or l_mds_enabled is null or l_geo_coder_url is not null)
974 then
975 form_task_address_query('',
976 p_start_date,
977 p_end_date,
978 i,
979 x_task_rec_tbl,
980 task_address_cursor,
981 p_unstamped_only);
982 ELSE
983 FOR mdsList IN mdsListCur
984 loop
985 form_task_address_query(mdslist.spatial_dataset,
986 p_start_date,
987 p_end_date,
988 i,
989 x_task_rec_tbl,
990 task_address_cursor,
991 p_unstamped_only);
992 END LOOP;
993 END IF;
994
995 -- Standard check of p_commit
996 IF fnd_api.to_boolean (p_commit)
997 THEN
998 COMMIT WORK;
999 END IF;
1000
1001 -- Standard call to get message count and return the message info if the count is 1
1002 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1003 EXCEPTION
1004 WHEN fnd_api.g_exc_unexpected_error
1005 THEN
1006 x_return_status := fnd_api.g_ret_sts_unexp_error;
1007 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1008 p_data => x_msg_data
1009 );
1010 debug('CSF_TASK_ADDRESS_PVT.retrieve_data: Unexpected Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
1011 WHEN OTHERS
1012 THEN
1013 x_return_status := fnd_api.g_ret_sts_unexp_error;
1014
1015 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1016 THEN
1017 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1018 END IF;
1019
1020 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1021 p_data => x_msg_data);
1022 debug('CSF_TASK_ADDRESS_PVT.retrieve_data: Unexpected Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
1023 END retrieve_data;
1024
1025 /*
1026 Utility procedure to fetch the locus for a location id.
1027 returns the locus given the location_id from the hz locations table
1028 */
1029 PROCEDURE get_geometry (
1030 p_api_version IN NUMBER,
1031 p_init_msg_list IN VARCHAR2,
1032 p_commit IN VARCHAR2,
1033 p_validation_level IN NUMBER,
1034 p_location_id IN hz_locations.location_id%TYPE,
1035 x_locus OUT NOCOPY hz_locations.geometry%TYPE,
1036 x_return_status OUT NOCOPY VARCHAR2,
1037 x_msg_count OUT NOCOPY NUMBER,
1038 x_msg_data OUT NOCOPY VARCHAR2
1039 )
1040 IS
1041 l_api_name CONSTANT VARCHAR2 (30) := 'GET_GEOMETRY';
1042 l_api_version CONSTANT NUMBER := 1.0;
1043
1044 CURSOR l_check_locus_csr (l_location_id hz_locations.location_id%TYPE)
1045 IS
1046 SELECT geometry
1047 FROM hz_locations
1048 WHERE location_id = l_location_id;
1049 BEGIN
1050 -- Standard check for call compatibility
1051 IF NOT fnd_api.compatible_api_call (l_api_version,
1052 p_api_version,
1053 l_api_name,
1054 g_pkg_name
1055 )
1056 THEN
1057 RAISE fnd_api.g_exc_unexpected_error;
1058 END IF;
1059
1060 -- Initialize message list if p_init_msg_list is set to TRUE
1061 IF fnd_api.to_boolean (p_init_msg_list)
1062 THEN
1063 fnd_msg_pub.initialize;
1064 END IF;
1065
1066 -- Initialize API return status to success
1067 x_return_status := fnd_api.g_ret_sts_success;
1068
1069 -- API body
1070 BEGIN
1071 OPEN l_check_locus_csr (p_location_id);
1072
1073 FETCH l_check_locus_csr
1074 INTO x_locus;
1075
1076 CLOSE l_check_locus_csr;
1077 EXCEPTION
1078 WHEN NO_DATA_FOUND
1079 THEN
1080 CLOSE l_check_locus_csr;
1081 WHEN OTHERS
1082 THEN
1083 CLOSE l_check_locus_csr;
1084
1085 RAISE fnd_api.g_exc_unexpected_error;
1086 END;
1087
1088 -- End of API body
1089
1090 -- Standard check of p_commit
1091 IF fnd_api.to_boolean (p_commit)
1092 THEN
1093 COMMIT WORK;
1094 END IF;
1095
1096 -- Standard call to get message count and return the message info if the count is 1
1097 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1098 EXCEPTION
1099 WHEN fnd_api.g_exc_error
1100 THEN
1101 x_return_status := fnd_api.g_ret_sts_error;
1102 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1103 p_data => x_msg_data
1104 );
1105 WHEN fnd_api.g_exc_unexpected_error
1106 THEN
1107 x_return_status := fnd_api.g_ret_sts_unexp_error;
1108 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1109 p_data => x_msg_data
1110 );
1111 WHEN OTHERS
1112 THEN
1113 x_return_status := fnd_api.g_ret_sts_unexp_error;
1114
1115 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1116 THEN
1117 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1118 END IF;
1119
1120 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1121 p_data => x_msg_data);
1122 END get_geometry;
1123
1124 -- This function is used to check if the geometry is null.
1125 --
1126 -- returns:
1127 -- FALSE if the geometry is null or any of the components are null.
1128 -- TRUE otherwise
1129 FUNCTION is_geometry_null (p_locus IN MDSYS.SDO_GEOMETRY)
1130 RETURN BOOLEAN
1131 IS
1132 BEGIN
1133 RETURN ( (p_locus IS NULL)
1134 OR (p_locus.sdo_srid IS NULL)
1135 OR (p_locus.sdo_elem_info IS NULL)
1136 OR (p_locus.sdo_ordinates IS NULL)
1137 );
1138 END is_geometry_null;
1139
1140 -- This function is used to check if the geometry is valid.
1141 -- returns :
1142 -- 'Y' if the geometry is valid ,
1143 -- 'N' otherwise
1144 PROCEDURE is_geometry_valid (
1145 p_api_version IN NUMBER,
1146 p_init_msg_list IN VARCHAR2,
1147 p_commit IN VARCHAR2,
1148 p_validation_level IN NUMBER,
1149 p_locus IN hz_locations.geometry%TYPE,
1150 x_result OUT NOCOPY VARCHAR2,
1151 x_return_status OUT NOCOPY VARCHAR2,
1152 x_msg_count OUT NOCOPY NUMBER,
1153 x_msg_data OUT NOCOPY VARCHAR2
1154 )
1155 IS
1156 l_api_name CONSTANT VARCHAR2 (30) := 'IS_GEOMETRY_VALID';
1157 l_api_version CONSTANT NUMBER := 1.0;
1158 BEGIN
1159 -- Standard check for call compatibility
1160 IF NOT fnd_api.compatible_api_call (l_api_version,
1161 p_api_version,
1162 l_api_name,
1163 g_pkg_name
1164 )
1165 THEN
1166 RAISE fnd_api.g_exc_unexpected_error;
1167 END IF;
1168
1169 -- Initialize message list if p_init_msg_list is set to TRUE
1170 IF fnd_api.to_boolean (p_init_msg_list)
1171 THEN
1172 fnd_msg_pub.initialize;
1173 END IF;
1174
1175 -- Initialize API return status to success
1176 x_return_status := fnd_api.g_ret_sts_success;
1177 -- API body
1178 x_result := g_valid_false;
1179 /*
1180 Returns :
1181 'TRUE' on success ,
1182 'FALSE' otherwise
1183 */
1184 csf_locus_pub.verify_locus (p_api_version => 1.0,
1185 p_locus => p_locus,
1186 x_msg_count => x_msg_count,
1187 x_msg_data => x_msg_data,
1188 x_result => x_result,
1189 x_return_status => x_return_status
1190 );
1191
1192 IF (x_result = 'TRUE')
1193 THEN
1194 x_result := g_valid_true;
1195 ELSE
1196 x_result := g_valid_false;
1197 END IF;
1198
1199 x_return_status := fnd_api.g_ret_sts_success;
1200
1201 -- End of API body
1202
1203 -- Standard check of p_commit
1204 IF fnd_api.to_boolean (p_commit)
1205 THEN
1206 COMMIT WORK;
1207 END IF;
1208
1209 -- Standard call to get message count and return the message info if the count is 1
1210 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1211 EXCEPTION
1212 WHEN fnd_api.g_exc_error
1213 THEN
1214 x_return_status := fnd_api.g_ret_sts_error;
1215 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1216 p_data => x_msg_data
1217 );
1218 WHEN fnd_api.g_exc_unexpected_error
1219 THEN
1220 x_return_status := fnd_api.g_ret_sts_unexp_error;
1221 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1222 p_data => x_msg_data
1223 );
1224 WHEN OTHERS
1225 THEN
1226 x_return_status := fnd_api.g_ret_sts_unexp_error;
1227
1228 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1229 THEN
1230 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1231 END IF;
1232
1233 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1234 p_data => x_msg_data);
1235 END is_geometry_valid;
1236
1237 PROCEDURE get_error_detail (
1238 p_api_version IN NUMBER,
1239 p_init_msg_list IN VARCHAR2,
1240 p_commit IN VARCHAR2,
1241 p_validation_level IN NUMBER,
1242 p_task_rec IN task_rec_type,
1243 x_error OUT NOCOPY VARCHAR2,
1244 x_error_detail OUT NOCOPY VARCHAR2,
1245 x_return_status OUT NOCOPY VARCHAR2,
1246 x_msg_count OUT NOCOPY NUMBER,
1247 x_msg_data OUT NOCOPY VARCHAR2
1248 )
1249 IS
1250 l_api_name CONSTANT VARCHAR2 (30) := 'GET_ERROR_DETAIL';
1251 l_api_version CONSTANT NUMBER := 1.0;
1252 l_tmp VARCHAR2 (2000);
1253 l_place VARCHAR2 (250);
1254 l_flag VARCHAR2 (1);
1255
1256 CURSOR l_place_csr (l_place_name VARCHAR2, l_parent_level NUMBER)
1257 IS
1258 SELECT NAME
1259 FROM csf_lf_places p, csf_lf_names n, csf_lf_place_names pn
1260 WHERE n.name_id = pn.name_id
1261 AND pn.place_id = p.place_id
1262 AND NAME = l_place_name
1263 AND p.place_parent_level = l_parent_level;
1264
1265 l_uk_city VARCHAR2(1000);
1266 l_dataset_profile_value VARCHAR2(1000);
1267 l_place_country VARCHAR2(1000);
1268 l_place_state VARCHAR2(1000);
1269 l_place_city VARCHAR2(1000);
1270 l_place_zip VARCHAR2(1000);
1271 l_spatail_dataset VARCHAR2(10) := '';
1272
1273 TYPE REF_CURSOR IS REF CURSOR;
1274 process_cursor REF_CURSOR;
1275
1276 l_country VARCHAR2(100);
1277 CURSOR ctry_hr_to_spatial IS
1278 SELECT SPATIAL_COUNTRY_NAME,spatial_dataset
1279 FROM CSF_SPATIAL_CTRY_MAPPINGS
1280 WHERE HR_COUNTRY_NAME = p_task_rec.country;
1281
1282 BEGIN
1283 -- Standard check for call compatibility
1284 IF NOT fnd_api.compatible_api_call (l_api_version,
1285 p_api_version,
1286 l_api_name,
1287 g_pkg_name
1288 )
1289 THEN
1290 RAISE fnd_api.g_exc_unexpected_error;
1291 END IF;
1292
1293 -- Initialize message list if p_init_msg_list is set to TRUE
1294 IF fnd_api.to_boolean (p_init_msg_list)
1295 THEN
1296 fnd_msg_pub.initialize;
1297 END IF;
1298
1299 -- Initialize API return status to success
1300 x_return_status := fnd_api.g_ret_sts_success;
1301
1302
1303 open ctry_hr_to_spatial;
1304 fetch ctry_hr_to_spatial into l_country, l_spatail_dataset;
1305 close ctry_hr_to_spatial;
1306
1307 IF (l_country is null) THEN
1308 l_country := p_task_rec.country;
1309 END IF;
1310
1311 l_dataset_profile_value := fnd_profile.value('CSF_SPATIAL_MULTIDATASET_ENABLED');
1312 IF (l_dataset_profile_value = 'N' OR l_dataset_profile_value IS NULL ) THEN
1313 l_dataset_profile_value := '';
1314 ELSE
1315 l_dataset_profile_value := l_spatail_dataset;
1316 END IF;
1317
1318 l_uk_city :=' SELECT cln.NAME
1319 FROM csf_lf_names'||l_dataset_profile_value||' cln,
1320 csf_lf_place_names'||l_dataset_profile_value||' clpn,
1321 csf_lf_places'||l_dataset_profile_value||' clp
1322 WHERE cln.name_id = clpn.name_id
1323 AND clpn.place_id = clp.place_id
1324 AND clp.place_parent_level NOT IN (-1, 0)
1325 AND UPPER (cln.NAME) = UPPER ('''||p_task_rec.city||''')';
1326
1327 l_place_country :=' SELECT n.NAME
1328 FROM csf_lf_places'||l_dataset_profile_value||' p,
1329 csf_lf_place_names'||l_dataset_profile_value||' pn,
1330 csf_lf_names'||l_dataset_profile_value||' n
1331 WHERE n.NAME = UPPER ('''||l_country||''')
1332 AND pn.name_id = n.name_id
1333 AND p.place_id = pn.place_id
1334 AND p.place_parent_level = -1';
1335
1336 l_place_state :=' SELECT n.NAME
1337 FROM csf_lf_names'||l_dataset_profile_value||' n,
1338 csf_lf_place_names'||l_dataset_profile_value||' pn,
1339 (SELECT place_id
1340 FROM csf_lf_places'||l_dataset_profile_value||'
1341 CONNECT BY PRIOR place_id = parent_place_id
1342 START WITH place_id IN (
1343 SELECT pn.place_id
1344 FROM csf_lf_names'||l_dataset_profile_value||' n,
1345 csf_lf_place_names'||l_dataset_profile_value||' pn
1346 WHERE n.NAME = UPPER ('''|| l_country||''')
1347 AND pn.name_id = n.name_id)) p
1348 WHERE pn.place_id = p.place_id
1349 AND n.name_id = pn.name_id
1350 AND n.NAME = UPPER ('''||p_task_rec.state||''')';
1351
1352
1353 -- API body
1354 IF p_task_rec.country_code = 'GB'
1355 THEN
1356 BEGIN
1357 l_tmp := NULL;
1358
1359 OPEN process_cursor FOR l_uk_city;
1360
1361 FETCH process_cursor
1362 INTO l_tmp;
1363
1364 IF l_tmp IS NULL AND trim(p_task_rec.city) <> '_'
1365 THEN
1366 fnd_message.set_name ('CSF', 'CSF_CITY_NOT_FOUND_ERROR');
1367 fnd_message.set_token ('CITY', p_task_rec.city);
1368 fnd_message.set_token ('COUNTRY', p_task_rec.country);
1369 x_error := fnd_message.get;
1370 x_error_detail := x_error_detail || ' ' || x_error;
1371 END IF;
1372
1373 CLOSE process_cursor;
1374 EXCEPTION
1375 WHEN NO_DATA_FOUND
1376 THEN
1377 CLOSE process_cursor;
1378 WHEN OTHERS
1379 THEN
1380 CLOSE process_cursor;
1381
1382 RAISE fnd_api.g_exc_unexpected_error;
1383 END;
1384 ELSE
1385
1386 BEGIN
1387 l_tmp := NULL;
1388
1389 OPEN process_cursor FOR l_place_country;
1390
1391 FETCH process_cursor
1392 INTO l_tmp;
1393
1394 IF l_tmp IS NULL AND trim(p_task_rec.country) <> '_'
1395 THEN
1396 fnd_message.set_name ('CSF', 'CSF_COUNTRY_NOT_FOUND_ERROR');
1397 fnd_message.set_token ('COUNTRY', p_task_rec.country);
1398 x_error := fnd_message.get;
1399 x_error_detail := x_error_detail || ' ' || x_error;
1400 END IF;
1401
1402 CLOSE process_cursor;
1403 EXCEPTION
1404 WHEN NO_DATA_FOUND
1405 THEN
1406 CLOSE process_cursor;
1407 WHEN OTHERS
1408 THEN
1409 CLOSE process_cursor;
1410
1411 RAISE fnd_api.g_exc_unexpected_error;
1412 END;
1413
1414 BEGIN
1415 l_tmp := NULL;
1416
1417 OPEN process_cursor FOR l_place_state;
1418
1419 FETCH process_cursor
1420 INTO l_tmp;
1421
1422 IF l_tmp IS NULL AND trim(p_task_rec.state) <> '_' AND trim(p_task_rec.country) <> '_'
1423 THEN
1424 fnd_message.set_name ('CSF', 'CSF_STATE_NOT_FOUND_ERROR');
1425 fnd_message.set_token ('STATE', p_task_rec.state);
1426 fnd_message.set_token ('COUNTRY', p_task_rec.country);
1427 x_error := fnd_message.get;
1428 x_error_detail := x_error_detail || ' ' || x_error;
1429 END IF;
1430
1431 CLOSE process_cursor;
1432 EXCEPTION
1433 WHEN NO_DATA_FOUND
1434 THEN
1435 CLOSE process_cursor;
1436 WHEN OTHERS
1437 THEN
1438 CLOSE process_cursor;
1439
1440 RAISE fnd_api.g_exc_unexpected_error;
1441 END;
1442
1443 BEGIN
1444 l_tmp := NULL;
1445
1446 l_flag := NULL;
1447
1448 IF p_task_rec.state IS NOT NULL AND trim(p_task_rec.state) <> '_'
1449 THEN
1450 l_place := p_task_rec.state;
1451 l_flag := 'S';
1452 ELSE
1453 l_place := l_country;
1454 l_flag := NULL;
1455 END IF;
1456
1457 l_place_city :=' SELECT n.NAME
1458 FROM csf_lf_names'||l_dataset_profile_value||' n,
1459 csf_lf_place_names'||l_dataset_profile_value||' pn,
1460 (SELECT place_id
1461 FROM csf_lf_places'||l_dataset_profile_value||'
1462 WHERE place_parent_level IN (1, 8)
1463 CONNECT BY PRIOR place_id = parent_place_id
1464 START WITH place_id IN (
1465 SELECT pn.place_id
1466 FROM csf_lf_names'||l_dataset_profile_value||' n,
1467 csf_lf_place_names'||l_dataset_profile_value||' pn
1468 WHERE n.NAME = UPPER ('''||l_place||''')
1469 AND pn.name_id = n.name_id)) p
1470 WHERE pn.place_id = p.place_id
1471 AND n.name_id = pn.name_id
1472 AND n.NAME = UPPER ('''||REPLACE(p_task_rec.city,'''','''''')||''')';
1473
1474 OPEN process_cursor FOR l_place_city;
1475
1476 FETCH process_cursor
1477 INTO l_tmp;
1478
1479 IF l_tmp IS NULL
1480 THEN
1481 IF l_flag = 'S' AND trim(p_task_rec.state) <> '_' AND trim(p_task_rec.city) <> '_'
1482 THEN
1483 fnd_message.set_name ('CSF',
1484 'CSF_CITY_NOT_FOUND_ERROR_STATE');
1485 fnd_message.set_token ('CITY', p_task_rec.city);
1486 fnd_message.set_token ('STATE', p_task_rec.state);
1487 ELSIF trim(p_task_rec.country) <> '_' AND trim(p_task_rec.city) <> '_'
1488 THEN
1489 fnd_message.set_name ('CSF', 'CSF_CITY_NOT_FOUND_ERROR');
1490 fnd_message.set_token ('CITY', p_task_rec.city);
1491 fnd_message.set_token ('COUNTRY', p_task_rec.country);
1492 END IF;
1493
1494 x_error := fnd_message.get;
1495 x_error_detail := x_error_detail || ' ' || x_error;
1496 END IF;
1497
1498 CLOSE process_cursor;
1499 EXCEPTION
1500 WHEN NO_DATA_FOUND
1501 THEN
1502 CLOSE process_cursor;
1503 WHEN OTHERS
1504 THEN
1505 CLOSE process_cursor;
1506
1507 RAISE fnd_api.g_exc_unexpected_error;
1508 END;
1509
1510 BEGIN
1511 l_tmp := NULL;
1512
1513 IF p_task_rec.city IS NOT NULL AND trim(p_task_rec.city) <> '_'
1514 THEN
1515 l_place := REPLACE(p_task_rec.city,'''','''''');
1516 l_flag := 'C';
1517 ELSIF p_task_rec.state IS NOT NULL AND trim(p_task_rec.state) <> '_'
1518 THEN
1519 l_place := p_task_rec.state;
1520 l_flag := 'S';
1521 ELSE
1522 l_place := l_country;
1523 l_flag := NULL;
1524 END IF;
1525
1526 l_place_zip := 'SELECT pc.postal_code
1527 FROM csf_lf_postcodes'||l_dataset_profile_value||' pc,
1528 csf_lf_place_postcs'||l_dataset_profile_value||' ppc,
1529 csf_lf_place_names'||l_dataset_profile_value||' pn,
1530 csf_lf_names'||l_dataset_profile_value||' n
1531 WHERE pc.postal_code_id = ppc.postal_code_id
1532 AND ppc.place_id = pn.place_id
1533 AND pn.name_id = n.name_id
1534 AND pc.postal_code = '''||p_task_rec.postal_code||'''
1535 AND EXISTS (
1536 SELECT 1
1537 FROM csf_lf_places'||l_dataset_profile_value||'
1538 WHERE place_id IN (
1539 SELECT clpn.place_id
1540 FROM csf_lf_names'||l_dataset_profile_value||' cln,
1541 csf_lf_place_names'||l_dataset_profile_value||' clpn
1542 WHERE cln.NAME = '''||l_place||'''
1543 AND cln.name_id = clpn.name_id)
1544 CONNECT BY place_id = PRIOR parent_place_id
1545 START WITH place_id = pn.place_id)';
1546
1547 OPEN process_cursor FOR l_place_zip;
1548
1549 FETCH process_cursor
1550 INTO l_tmp;
1551
1552 IF l_tmp IS NULL
1553 THEN
1554 IF l_flag = 'C' AND trim(p_task_rec.postal_code) <> '_' AND trim (p_task_rec.city) <> '_'
1555 THEN
1556 fnd_message.set_name ('CSF',
1557 'CSF_ZIP_NOT_FOUND_ERROR_CITY'
1558 );
1559 fnd_message.set_token ('ZIP', p_task_rec.postal_code);
1560 fnd_message.set_token ('CITY', p_task_rec.city);
1561 x_error := fnd_message.get;
1562 x_error_detail := x_error_detail || ' ' || x_error;
1563 ELSIF l_flag = 'S' AND trim(p_task_rec.postal_code) <> '_' AND trim (p_task_rec.state) <> '_'
1564 THEN
1565 fnd_message.set_name ('CSF',
1566 'CSF_ZIP_NOT_FOUND_ERROR_STATE'
1567 );
1568 fnd_message.set_token ('ZIP', p_task_rec.postal_code);
1569 fnd_message.set_token ('STATE', p_task_rec.state);
1570 x_error := fnd_message.get;
1571 x_error_detail := x_error_detail || ' ' || x_error;
1572 ELSIF trim(p_task_rec.postal_code) <> '_' AND trim (p_task_rec.country) <> '_'
1573 THEN
1574 fnd_message.set_name ('CSF', 'CSF_ZIP_NOT_FOUND_ERROR');
1575 fnd_message.set_token ('ZIP', p_task_rec.postal_code);
1576 fnd_message.set_token ('COUNTRY', p_task_rec.country);
1577 x_error := fnd_message.get;
1578 x_error_detail := x_error_detail || ' ' || x_error;
1579 END IF;
1580 END IF;
1581 CLOSE process_cursor;
1582 EXCEPTION
1583 WHEN NO_DATA_FOUND
1584 THEN
1585 CLOSE process_cursor;
1586 WHEN OTHERS
1587 THEN
1588 CLOSE process_cursor;
1589
1590 RAISE fnd_api.g_exc_unexpected_error;
1591 END;
1592 END IF;
1593
1594 -- Added for LF enhancement of Forced accuracy
1595 DECLARE
1596 l_exp_accuracy_level VARCHAR2(5) DEFAULT '0';
1597 BEGIN
1598
1599 fnd_message.set_name ('CSF', 'CSF_ADDRESS_ACC_FACTOR_ERROR');
1600 l_exp_accuracy_level := fnd_profile.VALUE('CSF_LOC_ACC_LEVELS');
1601 l_exp_accuracy_level := NVL(l_exp_accuracy_level,'0');
1602 IF (l_exp_accuracy_level = '0')
1603 THEN
1604 fnd_message.set_token ('ACCURACY','zip code or city level accuracy');
1605 END IF;
1606 IF (l_exp_accuracy_level = '1')
1607 THEN
1608 fnd_message.set_token ('ACCURACY','street level accuracy');
1609 END IF;
1610 IF (l_exp_accuracy_level = '2')
1611 THEN
1612 fnd_message.set_token ('ACCURACY','building number level accuracy');
1613 END IF;
1614 x_error := fnd_message.get;
1615 x_error_detail := x_error_detail || ' ' || x_error;
1616
1617 END;
1618 -- Enhancement Code ends here
1619
1620 -- End of API body
1621
1622 -- Standard check of p_commit
1623 IF fnd_api.to_boolean (p_commit)
1624 THEN
1625 COMMIT WORK;
1626 END IF;
1627
1628 -- Standard call to get message count and return the message info if the count is 1
1629 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1630 EXCEPTION
1631 WHEN fnd_api.g_exc_unexpected_error
1632 THEN
1633 x_return_status := fnd_api.g_ret_sts_unexp_error;
1634 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1635 p_data => x_msg_data
1636 );
1637 WHEN OTHERS
1638 THEN
1639 x_return_status := fnd_api.g_ret_sts_unexp_error;
1640
1641 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1642 THEN
1643 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1644 END IF;
1645
1646 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1647 p_data => x_msg_data);
1648 END get_error_detail;
1649
1650
1651 PROCEDURE log_info (
1652 p_api_version IN NUMBER,
1653 p_init_msg_list IN VARCHAR2,
1654 p_commit IN VARCHAR2,
1655 p_validation_level IN NUMBER,
1656 p_task_rec IN task_rec_type,
1657 x_return_status OUT NOCOPY VARCHAR2,
1658 x_msg_count OUT NOCOPY NUMBER,
1659 x_msg_data OUT NOCOPY VARCHAR2
1660 )
1661 IS
1662 l_api_name CONSTANT VARCHAR2 (30) := 'LOG_INFO';
1663 l_api_version CONSTANT NUMBER := 1.0;
1664 l_msg_data VARCHAR2 (32767);
1665 l_error VARCHAR2 (2000);
1666 l_error_detail VARCHAR2 (2000);
1667
1668 CURSOR c_check_ext_locations (l_task_id csf_ext_locations.task_id%TYPE)
1669 IS
1670 SELECT task_id
1671 FROM csf_ext_locations
1672 WHERE task_id = l_task_id;
1673
1674 l_location_id hz_locations.location_id%TYPE;
1675 l_task_id csf_ext_locations.task_id%TYPE;
1676 BEGIN
1677 -- Standard check for call compatibility
1678 IF NOT fnd_api.compatible_api_call (l_api_version,
1679 p_api_version,
1680 l_api_name,
1681 g_pkg_name
1682 )
1683 THEN
1684 RAISE fnd_api.g_exc_unexpected_error;
1685 END IF;
1686
1687 -- Initialize message list if p_init_msg_list is set to TRUE
1688 IF fnd_api.to_boolean (p_init_msg_list)
1689 THEN
1690 fnd_msg_pub.initialize;
1691 END IF;
1692
1693 -- Initialize API return status to success
1694 x_return_status := fnd_api.g_ret_sts_success;
1695 -- API body
1696 fnd_message.set_name ('CSF', 'CSF_INVALID_TASK_INFO');
1697 fnd_message.set_token ('TASK_ID', p_task_rec.task_id);
1698 fnd_message.set_token ('TASK_NUMBER', p_task_rec.task_number);
1699 fnd_message.set_token ('CITY', p_task_rec.city);
1700 fnd_message.set_token ('POSTAL_CODE', p_task_rec.postal_code);
1701 fnd_message.set_token ('COUNTY', p_task_rec.county);
1702 fnd_message.set_token ('STATE', p_task_rec.state);
1703 fnd_message.set_token ('COUNTRY', p_task_rec.country);
1704 l_msg_data := fnd_message.get;
1705 put_stream (g_log, l_msg_data);
1706 put_stream (g_output, l_msg_data);
1707
1708 IF l_location_id IS NULL
1709 THEN
1710 l_location_id := p_task_rec.location_id;
1711 END IF;
1712
1713 BEGIN
1714 OPEN c_check_ext_locations (p_task_rec.task_id);
1715
1716 FETCH c_check_ext_locations
1717 INTO l_task_id;
1718
1719 CLOSE c_check_ext_locations;
1720 EXCEPTION
1721 WHEN NO_DATA_FOUND
1722 THEN
1723 CLOSE c_check_ext_locations;
1724 WHEN OTHERS
1725 THEN
1726 CLOSE c_check_ext_locations;
1727 END;
1728
1729 get_error_detail (p_api_version => 1.0,
1730 p_init_msg_list => fnd_api.g_false,
1731 p_commit => fnd_api.g_false,
1732 p_validation_level => fnd_api.g_valid_level_full,
1733 p_task_rec => p_task_rec,
1734 x_error => l_error,
1735 x_error_detail => l_error_detail,
1736 x_return_status => x_return_status,
1737 x_msg_count => x_msg_count,
1738 x_msg_data => x_msg_data
1739 );
1740
1741 IF x_return_status <> fnd_api.g_ret_sts_success
1742 THEN
1743 fnd_message.set_name ('CSF', 'CSF_GET_ERROR_DETAIL_ERROR');
1744 fnd_message.set_token ('LOCATION_ID', p_task_rec.location_id);
1745 fnd_message.set_token ('RETURN_STATUS', x_return_status);
1746 l_msg_data := fnd_message.get;
1747 put_stream (g_log, l_msg_data);
1748 put_stream (g_output, l_msg_data);
1749 END IF;
1750
1751 IF l_error IS NULL
1752 THEN
1753 fnd_message.set_name ('CSF', 'CSF_ADDRESS_INVALID_INFO');
1754 l_error := fnd_message.get;
1755 END IF;
1756
1757 IF l_error_detail IS NULL
1758 THEN
1759 fnd_message.set_name ('CSF', 'CSF_NO_ADDRESS_ENTERED_ERROR');
1760 l_error_detail := fnd_message.get;
1761 END IF;
1762
1763 IF l_task_id IS NULL
1764 THEN
1765 csf_locations_pkg.insert_row_ext
1766 (p_csf_ext_location_id => l_location_id,
1767 p_last_update_date => SYSDATE,
1768 p_last_updated_by => NVL (fnd_global.user_id,
1769 -1
1770 ),
1771 p_creation_date => SYSDATE,
1772 p_created_by => NVL (fnd_global.user_id,
1773 -1
1774 ),
1775 p_last_update_login => NVL
1776 (fnd_global.conc_login_id,
1777 -1
1778 ),
1779 p_request_id => NVL
1780 (fnd_global.conc_request_id,
1781 -1
1782 ),
1783 p_program_application_id => NVL
1784 (fnd_global.prog_appl_id,
1785 -1
1786 ),
1787 p_program_id => NVL
1788 (fnd_global.conc_program_id,
1789 -1
1790 ),
1791 p_program_update_date => SYSDATE,
1792 p_task_id => p_task_rec.task_id,
1793 p_location_id => p_task_rec.location_id,
1794 p_validated_flag => p_task_rec.validated_flag,
1795 p_override_flag => p_task_rec.override_flag,
1796 p_log_detail_short => l_error,
1797 p_log_detail_long => l_error_detail
1798 );
1799 ELSE
1800 csf_locations_pkg.update_row_ext
1801 (p_csf_ext_location_id => p_task_rec.location_id,
1802 p_last_update_date => SYSDATE,
1803 p_last_updated_by => NVL (fnd_global.user_id,
1804 -1
1805 ),
1806 p_last_update_login => NVL
1807 (fnd_global.conc_login_id,
1808 -1
1809 ),
1810 p_request_id => NVL
1811 (fnd_global.conc_request_id,
1812 -1
1813 ),
1814 p_program_application_id => NVL
1815 (fnd_global.prog_appl_id,
1816 -1
1817 ),
1818 p_program_id => NVL
1819 (fnd_global.conc_program_id,
1820 -1
1821 ),
1822 p_program_update_date => SYSDATE,
1823 p_location_id => p_task_rec.location_id,
1824 p_validated_flag => p_task_rec.validated_flag,
1825 p_override_flag => p_task_rec.override_flag,
1826 p_log_detail_short => l_error,
1827 p_log_detail_long => l_error_detail
1828 );
1829 END IF;
1830
1831 -- End of API body
1832
1833 -- Standard check of p_commit
1834 IF fnd_api.to_boolean (p_commit)
1835 THEN
1836 COMMIT WORK;
1837 END IF;
1838
1839 -- Standard call to get message count and return the message info if the count is 1
1840 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1841 EXCEPTION
1842 WHEN fnd_api.g_exc_unexpected_error
1843 THEN
1844 x_return_status := fnd_api.g_ret_sts_unexp_error;
1845 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1846 p_data => x_msg_data
1847 );
1848 WHEN OTHERS
1849 THEN
1850 x_return_status := fnd_api.g_ret_sts_unexp_error;
1851
1852 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1853 THEN
1854 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1855 END IF;
1856
1857 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1858 p_data => x_msg_data);
1859 END log_info;
1860
1861
1862
1863 PROCEDURE get_error_detail_info (
1864 p_api_version IN NUMBER,
1865 p_init_msg_list IN VARCHAR2,
1866 p_country_code IN VARCHAR2,
1867 p_country IN VARCHAR2,
1868 p_state IN VARCHAR2,
1869 p_city IN VARCHAR2,
1870 p_postal_code IN VARCHAR2,
1871 x_error_detail OUT NOCOPY VARCHAR2,
1872 x_dup_msg_data IN OUT NOCOPY VARCHAR2
1873 )
1874 IS
1875 l_api_name CONSTANT VARCHAR2 (30) := 'GET_ERROR_DETAIL_INFO';
1876 l_api_version CONSTANT NUMBER := 1.0;
1877 l_exp_accuracy_level VARCHAR2(5) DEFAULT '0';
1878
1879 BEGIN
1880 -- Standard check for call compatibility
1881 IF NOT fnd_api.compatible_api_call (l_api_version,
1882 p_api_version,
1883 l_api_name,
1884 g_pkg_name
1885 )
1886 THEN
1887 RAISE fnd_api.g_exc_unexpected_error;
1888 END IF;
1889
1890 -- Initialize message list if p_init_msg_list is set to TRUE
1891 IF fnd_api.to_boolean (p_init_msg_list)
1892 THEN
1893 fnd_msg_pub.initialize;
1894 END IF;
1895 if (x_dup_msg_data = 'CSF_LF 223634:CSF_LF_INVALID_ADDRESS_DETAILS') then
1896 fnd_message.set_name ('CSF', 'CSF_LF_INVALID_ADDRESS_DETAILS');
1897 x_error_detail := fnd_message.get;
1898 elsif (x_dup_msg_data = 'CSF_LF 223632:CSF_LF_POSTAL_OR_STATE_NOT_RESOLVED') then
1899 fnd_message.set_name ('CSF', 'CSF_LF_ZIP_STATE_UNRESOLVED');
1900 x_error_detail := fnd_message.get;
1901 elsif(x_dup_msg_data = 'CSF_LF 223633:CSF_LF_CITY_OR_STATE_NOT_RESOLVED') then
1902 fnd_message.set_name ('CSF', 'CSF_LF_CITY_STATE_UNRESOLVED');
1903 x_error_detail := fnd_message.get;
1904 elsif(x_dup_msg_data = 'CSF_LF 223630:CSF_LF_ELOC_ADDRESS_UNRESOLVED') then
1905 fnd_message.set_name ('CSF', 'CSF_LF_ELOC_ADDR_UNRESOLVED');
1906 x_error_detail := fnd_message.get;
1907 elsif(x_dup_msg_data = 'CSF_LF 223602:CSF_LF_COUNTRY_NOT_RESOLVED') then
1908 fnd_message.set_name ('CSF', 'CSF_COUNTRY_NOT_FOUND_ERROR');
1909 fnd_message.set_token ('COUNTRY', p_country);
1910 x_error_detail := fnd_message.get;
1911 elsif(x_dup_msg_data = 'CSF_LF 223629:ADDRESS_ACCURACY_EXCEPTION') then
1912 fnd_message.set_name ('CSF', 'CSF_ADDRESS_ACC_FACTOR_ERROR');
1913 l_exp_accuracy_level := fnd_profile.VALUE('CSF_LOC_ACC_LEVELS');
1914 l_exp_accuracy_level := NVL(l_exp_accuracy_level,'0');
1915 IF (l_exp_accuracy_level = '0') THEN
1916 fnd_message.set_token ('ACCURACY','zip code or city level accuracy');
1917 END IF;
1918 IF (l_exp_accuracy_level = '1') THEN
1919 fnd_message.set_token ('ACCURACY','street level accuracy');
1920 END IF;
1921 IF (l_exp_accuracy_level = '2') THEN
1922 fnd_message.set_token ('ACCURACY','building number level accuracy');
1923 END IF;
1924 x_error_detail := fnd_message.get;
1925 END IF;
1926
1927 EXCEPTION
1928 WHEN fnd_api.g_exc_unexpected_error
1929 THEN
1930 NULL;
1931 WHEN OTHERS
1932 THEN
1933 NULL;
1934 END get_error_detail_info;
1935
1936
1937
1938 --
1939 -- Added for bug 7571215
1940
1941 PROCEDURE get_error_detail (
1942 p_api_version IN NUMBER,
1943 p_init_msg_list IN VARCHAR2,
1944 p_country_code IN VARCHAR2,
1945 p_country IN VARCHAR2,
1946 p_state IN VARCHAR2,
1947 p_city IN VARCHAR2,
1948 p_postal_code IN VARCHAR2,
1949 x_error_detail OUT NOCOPY VARCHAR2
1950 )
1951 IS
1952 l_api_name CONSTANT VARCHAR2 (30) := 'GET_ERROR_DETAIL';
1953 l_api_version CONSTANT NUMBER := 1.0;
1954 l_tmp VARCHAR2 (2000);
1955 l_place VARCHAR2 (250);
1956 l_flag VARCHAR2 (1);
1957 x_error VARCHAR2 (100);
1958
1959 CURSOR l_place_csr (l_place_name VARCHAR2, l_parent_level NUMBER)
1960 IS
1961 SELECT NAME
1962 FROM csf_lf_places p, csf_lf_names n, csf_lf_place_names pn
1963 WHERE n.name_id = pn.name_id
1964 AND pn.place_id = p.place_id
1965 AND NAME = l_place_name
1966 AND p.place_parent_level = l_parent_level;
1967
1968 l_uk_city VARCHAR2(1000);
1969 l_dataset_profile_value VARCHAR2(1000);
1970 l_place_country VARCHAR2(1000);
1971 l_place_state VARCHAR2(1000);
1972 l_place_city VARCHAR2(1000);
1973 l_place_zip VARCHAR2(1000);
1974
1975 TYPE REF_CURSOR IS REF CURSOR;
1976 process_cursor REF_CURSOR;
1977 l_spatial_dataset VARCHAR2(10);
1978 l_country VARCHAR2(100);
1979 CURSOR ctry_hr_to_spatial IS
1980 SELECT SPATIAL_COUNTRY_NAME, spatial_dataset
1981 FROM CSF_SPATIAL_CTRY_MAPPINGS
1982 WHERE HR_COUNTRY_NAME = upper(p_country);
1983
1984
1985 BEGIN
1986 -- Standard check for call compatibility
1987 IF NOT fnd_api.compatible_api_call (l_api_version,
1988 p_api_version,
1989 l_api_name,
1990 g_pkg_name
1991 )
1992 THEN
1993 RAISE fnd_api.g_exc_unexpected_error;
1994 END IF;
1995
1996 -- Initialize message list if p_init_msg_list is set to TRUE
1997 IF fnd_api.to_boolean (p_init_msg_list)
1998 THEN
1999 fnd_msg_pub.initialize;
2000 END IF;
2001
2002 l_dataset_profile_value := fnd_profile.value('CSF_SPATIAL_MULTIDATASET_ENABLED');
2003
2004 open ctry_hr_to_spatial;
2005 fetch ctry_hr_to_spatial into l_country, l_spatial_dataset;
2006 close ctry_hr_to_spatial;
2007
2008 IF (l_country is null) THEN
2009 l_country := p_country;
2010 END IF;
2011
2012 IF (l_dataset_profile_value = 'N' OR l_dataset_profile_value IS NULL ) THEN
2013 l_dataset_profile_value := '';
2014 ELSE
2015 l_dataset_profile_value := l_spatial_dataset;
2016 END IF;
2017
2018 l_uk_city :=' SELECT cln.NAME
2019 FROM csf_lf_names'||l_dataset_profile_value||' cln,
2020 csf_lf_place_names'||l_dataset_profile_value||' clpn,
2021 csf_lf_places'||l_dataset_profile_value||' clp
2022 WHERE cln.name_id = clpn.name_id
2023 AND clpn.place_id = clp.place_id
2024 AND clp.place_parent_level NOT IN (-1, 0)
2025 AND UPPER (cln.NAME) = UPPER ('''||p_city||''')';
2026
2027 l_place_country :=' SELECT n.NAME
2028 FROM csf_lf_places'||l_dataset_profile_value||' p,
2029 csf_lf_place_names'||l_dataset_profile_value||' pn,
2030 csf_lf_names'||l_dataset_profile_value||' n
2031 WHERE n.NAME = UPPER ('''||l_country||''')
2032 AND pn.name_id = n.name_id
2033 AND p.place_id = pn.place_id
2034 AND p.place_parent_level = -1';
2035
2036 l_place_state :=' SELECT n.NAME
2037 FROM csf_lf_names'||l_dataset_profile_value||' n,
2038 csf_lf_place_names'||l_dataset_profile_value||' pn,
2039 (SELECT place_id
2040 FROM csf_lf_places'||l_dataset_profile_value||'
2041 CONNECT BY PRIOR place_id = parent_place_id
2042 START WITH place_id IN (
2043 SELECT pn.place_id
2044 FROM csf_lf_names'||l_dataset_profile_value||' n,
2045 csf_lf_place_names'||l_dataset_profile_value||' pn
2046 WHERE n.NAME = UPPER ('''|| l_country||''')
2047 AND pn.name_id = n.name_id)) p
2048 WHERE pn.place_id = p.place_id
2049 AND n.name_id = pn.name_id
2050 AND n.NAME = UPPER ('''||p_state||''')';
2051
2052
2053 -- Initialize API return status to success
2054 -- x_return_status := fnd_api.g_ret_sts_success;
2055 -- API body
2056 IF p_country_code = 'GB'
2057 THEN
2058 BEGIN
2059 l_tmp := NULL;
2060
2061 OPEN process_cursor FOR l_uk_city;
2062
2063 FETCH process_cursor
2064 INTO l_tmp;
2065
2066 IF l_tmp IS NULL AND trim(p_city) <> '_'
2067 THEN
2068 fnd_message.set_name ('CSF', 'CSF_CITY_NOT_FOUND_ERROR');
2069 fnd_message.set_token ('CITY', p_city);
2070 fnd_message.set_token ('COUNTRY', p_country);
2071 x_error := fnd_message.get;
2072 x_error_detail := x_error_detail || ' ' || x_error;
2073 END IF;
2074
2075 CLOSE process_cursor;
2076 EXCEPTION
2077 WHEN NO_DATA_FOUND
2078 THEN
2079 CLOSE process_cursor;
2080 WHEN OTHERS
2081 THEN
2082 CLOSE process_cursor;
2083
2084 RAISE fnd_api.g_exc_unexpected_error;
2085 END;
2086 ELSE
2087
2088 BEGIN
2089 l_tmp := NULL;
2090
2091 OPEN process_cursor FOR l_place_country;
2092
2093 FETCH process_cursor
2094 INTO l_tmp;
2095
2096 IF l_tmp IS NULL AND trim(p_country) <> '_'
2097 THEN
2098 fnd_message.set_name ('CSF', 'CSF_COUNTRY_NOT_FOUND_ERROR');
2099 fnd_message.set_token ('COUNTRY', p_country);
2100 x_error := fnd_message.get;
2101 x_error_detail := x_error_detail || ' ' || x_error;
2102 END IF;
2103
2104 CLOSE process_cursor;
2105 EXCEPTION
2106 WHEN NO_DATA_FOUND
2107 THEN
2108 CLOSE process_cursor;
2109 WHEN OTHERS
2110 THEN
2111 CLOSE process_cursor;
2112
2113 RAISE fnd_api.g_exc_unexpected_error;
2114 END;
2115
2116 BEGIN
2117 l_tmp := NULL;
2118
2119 OPEN process_cursor FOR l_place_state;
2120
2121 FETCH process_cursor
2122 INTO l_tmp;
2123
2124 IF l_tmp IS NULL AND trim(p_country) <> '_' AND trim(p_state) <> '_'
2125 THEN
2126 fnd_message.set_name ('CSF', 'CSF_STATE_NOT_FOUND_ERROR');
2127 fnd_message.set_token ('STATE', p_state);
2128 fnd_message.set_token ('COUNTRY', p_country);
2129 x_error := fnd_message.get;
2130 x_error_detail := x_error_detail || ' ' || x_error;
2131 END IF;
2132
2133 CLOSE process_cursor;
2134 EXCEPTION
2135 WHEN NO_DATA_FOUND
2136 THEN
2137 CLOSE process_cursor;
2138 WHEN OTHERS
2139 THEN
2140 CLOSE process_cursor;
2141
2142 RAISE fnd_api.g_exc_unexpected_error;
2143 END;
2144
2145 BEGIN
2146 l_tmp := NULL;
2147
2148 l_flag := NULL;
2149
2150 IF p_state IS NOT NULL AND trim(p_state) <> '_'
2151 THEN
2152 l_place := p_state;
2153 l_flag := 'S';
2154 ELSE
2155 l_place := l_country;
2156 l_flag := NULL;
2157 END IF;
2158
2159 l_place_city := ' SELECT n.NAME
2160 FROM csf_lf_names'||l_dataset_profile_value||' n,
2161 csf_lf_place_names'||l_dataset_profile_value||' pn,
2162 (SELECT place_id
2163 FROM csf_lf_places'||l_dataset_profile_value||'
2164 WHERE place_parent_level IN (1, 8)
2165 CONNECT BY PRIOR place_id = parent_place_id
2166 START WITH place_id IN (
2167 SELECT pn.place_id
2168 FROM csf_lf_names'||l_dataset_profile_value||' n,
2169 csf_lf_place_names'||l_dataset_profile_value||' pn
2170 WHERE n.NAME = UPPER ('''||l_place||''')
2171 AND pn.name_id = n.name_id)) p
2172 WHERE pn.place_id = p.place_id
2173 AND n.name_id = pn.name_id
2174 AND n.NAME = UPPER ('''||p_city||''')';
2175
2176 OPEN process_cursor FOR l_place_city;
2177
2178 FETCH process_cursor
2179 INTO l_tmp;
2180
2181 IF l_tmp IS NULL
2182 THEN
2183 IF l_flag = 'S' AND trim(p_state) <> '_' AND trim(p_city) <> '_'
2184 THEN
2185 fnd_message.set_name ('CSF',
2186 'CSF_CITY_NOT_FOUND_ERROR_STATE');
2187 fnd_message.set_token ('CITY', p_city);
2188 fnd_message.set_token ('STATE', p_state);
2189 ELSIF trim(p_country) <> '_' AND trim(p_city) <> '_'
2190 THEN
2191 fnd_message.set_name ('CSF', 'CSF_CITY_NOT_FOUND_ERROR');
2192 fnd_message.set_token ('CITY', p_city);
2193 fnd_message.set_token ('COUNTRY', p_country);
2194 END IF;
2195
2196 x_error := fnd_message.get;
2197 x_error_detail := x_error_detail || ' ' || x_error;
2198 END IF;
2199
2200 CLOSE process_cursor;
2201 EXCEPTION
2202 WHEN NO_DATA_FOUND
2203 THEN
2204 CLOSE process_cursor;
2205 WHEN OTHERS
2206 THEN
2207 CLOSE process_cursor;
2208
2209 RAISE fnd_api.g_exc_unexpected_error;
2210 END;
2211
2212 BEGIN
2213 l_tmp := NULL;
2214
2215 IF p_city IS NOT NULL AND trim(p_city) <> '_'
2216 THEN
2217 l_place := p_city;
2218 l_flag := 'C';
2219 ELSIF p_state IS NOT NULL AND trim(p_state) <> '_'
2220 THEN
2221 l_place := p_state;
2222 l_flag := 'S';
2223 ELSE
2224 l_place := l_country;
2225 l_flag := NULL;
2226 END IF;
2227
2228 l_place_zip := 'SELECT pc.postal_code
2229 FROM csf_lf_postcodes'||l_dataset_profile_value||' pc,
2230 csf_lf_place_postcs'||l_dataset_profile_value||' ppc,
2231 csf_lf_place_names'||l_dataset_profile_value||' pn,
2232 csf_lf_names'||l_dataset_profile_value||' n
2233 WHERE pc.postal_code_id = ppc.postal_code_id
2234 AND ppc.place_id = pn.place_id
2235 AND pn.name_id = n.name_id
2236 AND pc.postal_code = '''||p_postal_code||'''
2237 AND EXISTS (
2238 SELECT 1
2239 FROM csf_lf_places'||l_dataset_profile_value||'
2240 WHERE place_id IN (
2241 SELECT clpn.place_id
2242 FROM csf_lf_names'||l_dataset_profile_value||' cln,
2243 csf_lf_place_names'||l_dataset_profile_value||' clpn
2244 WHERE cln.NAME = UPPER('''||l_place||''')
2245 AND cln.name_id = clpn.name_id)
2246 CONNECT BY place_id = PRIOR parent_place_id
2247 START WITH place_id = pn.place_id)';
2248
2249 OPEN process_cursor FOR l_place_zip;
2250
2251 FETCH process_cursor
2252 INTO l_tmp;
2253
2254 IF l_tmp IS NULL
2255 THEN
2256 IF l_flag = 'C' AND trim(p_postal_code) <> '_' AND
2257 trim (p_city) <> '_'
2258 THEN
2259 fnd_message.set_name ('CSF',
2260 'CSF_ZIP_NOT_FOUND_ERROR_CITY'
2261 );
2262 fnd_message.set_token ('ZIP', p_postal_code);
2263 fnd_message.set_token ('CITY', p_city);
2264 x_error := fnd_message.get;
2265 x_error_detail := x_error_detail || ' ' || x_error;
2266 ELSIF l_flag = 'S' AND trim(p_postal_code) <> '_' AND
2267 trim (p_state) <> '_'
2268 THEN
2269 fnd_message.set_name ('CSF',
2270 'CSF_ZIP_NOT_FOUND_ERROR_STATE'
2271 );
2272 fnd_message.set_token ('ZIP', p_postal_code);
2273 fnd_message.set_token ('STATE', p_state);
2274 x_error := fnd_message.get;
2275 x_error_detail := x_error_detail || ' ' || x_error;
2276 ELSIF trim(p_postal_code) <> '_' AND
2277 trim (p_country) <> '_'
2278 THEN
2279 fnd_message.set_name ('CSF', 'CSF_ZIP_NOT_FOUND_ERROR');
2280 fnd_message.set_token ('ZIP', p_postal_code);
2281 fnd_message.set_token ('COUNTRY', p_country);
2282 x_error := fnd_message.get;
2283 x_error_detail := x_error_detail || ' ' || x_error;
2284 END IF;
2285 END IF;
2286 CLOSE process_cursor;
2287 EXCEPTION
2288 WHEN NO_DATA_FOUND
2289 THEN
2290 CLOSE process_cursor;
2291 WHEN OTHERS
2292 THEN
2293 CLOSE process_cursor;
2294
2295 RAISE fnd_api.g_exc_unexpected_error;
2296 END;
2297 END IF;
2298
2299 -- Added for LF enhancement of Forced accuracy
2300 DECLARE
2301 l_exp_accuracy_level VARCHAR2(5) DEFAULT '0';
2302 BEGIN
2303
2304 fnd_message.set_name ('CSF', 'CSF_ADDRESS_ACC_FACTOR_ERROR');
2305 l_exp_accuracy_level := fnd_profile.VALUE('CSF_LOC_ACC_LEVELS');
2306 l_exp_accuracy_level := NVL(l_exp_accuracy_level,'0');
2307 IF (l_exp_accuracy_level = '0')
2308 THEN
2309 fnd_message.set_token ('ACCURACY','zip code or city level accuracy');
2310 END IF;
2311 IF (l_exp_accuracy_level = '1')
2312 THEN
2313 fnd_message.set_token ('ACCURACY','street level accuracy');
2314 END IF;
2315 IF (l_exp_accuracy_level = '2')
2316 THEN
2317 fnd_message.set_token ('ACCURACY','building number level accuracy');
2318 END IF;
2319 x_error := fnd_message.get;
2320 x_error_detail := x_error_detail || ' ' || x_error;
2321
2322 END;
2323 -- Enhancement Code ends here
2324
2325 -- End of API body
2326 EXCEPTION
2327 WHEN fnd_api.g_exc_unexpected_error
2328 THEN
2329 NULL;
2330 WHEN OTHERS
2331 THEN
2332 NULL;
2333 END get_error_detail;
2334
2335 -- End of addition
2336 PROCEDURE validate_task_data (
2337 p_api_version IN NUMBER,
2338 p_init_msg_list IN VARCHAR2,
2339 p_commit IN VARCHAR2,
2340 p_validation_level IN NUMBER,
2341 p_invalid_task_id IN jtf_tasks_b.task_id%TYPE,
2342 p_location_id IN hz_locations.location_id%TYPE,
2343 p_address1 IN hz_locations.address1%TYPE,
2344 p_address2 IN hz_locations.address2%TYPE,
2345 p_address3 IN hz_locations.address3%TYPE,
2346 p_address4 IN hz_locations.address4%TYPE,
2347 p_city IN hz_locations.city%TYPE,
2348 p_postal_code IN hz_locations.postal_code%TYPE,
2349 p_state IN hz_locations.state%TYPE,
2350 p_province IN hz_locations.province%TYPE,
2351 p_county IN hz_locations.county%TYPE,
2352 p_country IN hz_locations.country%TYPE,
2353 p_timezone_id IN hz_locations.timezone_id%TYPE,
2354 x_result OUT NOCOPY VARCHAR2,
2355 x_return_status OUT NOCOPY VARCHAR2,
2356 x_msg_count OUT NOCOPY NUMBER,
2357 x_msg_data OUT NOCOPY VARCHAR2
2358 )
2359 IS
2360 l_api_name CONSTANT VARCHAR2 (30) := 'VALIDATE_TASK_DATA';
2361 l_api_version CONSTANT NUMBER := 1.0;
2362 l_locus hz_locations.geometry%TYPE;
2363 l_country_code hz_locations.country%TYPE;
2364 l_time_zone_check BOOLEAN;
2365 l_timezone_id VARCHAR2 (80);
2366 l_dup_msg_data VARCHAR2 (2000);
2367
2368 CURSOR c_timezone_check (p_timezone_id NUMBER)
2369 IS
2370 SELECT NAME
2371 FROM hz_timezones_vl
2372 WHERE timezone_id = p_timezone_id;
2373
2374 CURSOR c_country_code (p_country hz_locations.country%TYPE)
2375 IS
2376 SELECT ftt.territory_code country_code
2377 FROM fnd_territories_tl ftt
2378 WHERE UPPER (ftt.territory_short_name) = UPPER (p_country)
2379 AND ftt.language = 'US';
2380
2381 BEGIN
2382 -- Standard check for call compatibility
2383 IF NOT fnd_api.compatible_api_call (l_api_version,
2384 p_api_version,
2385 l_api_name,
2386 g_pkg_name
2387 )
2388 THEN
2389 RAISE fnd_api.g_exc_unexpected_error;
2390 END IF;
2391
2392 -- Initialize message list if p_init_msg_list is set to TRUE
2393 IF fnd_api.to_boolean (p_init_msg_list)
2394 THEN
2395 fnd_msg_pub.initialize;
2396 END IF;
2397
2398 -- Initialize API return status to success
2399 x_return_status := fnd_api.g_ret_sts_success;
2400 x_result := g_valid_true;
2401
2402 -- API body
2403 BEGIN
2404 OPEN c_country_code (p_country);
2405
2406 FETCH c_country_code
2407 INTO l_country_code;
2408
2409 CLOSE c_country_code;
2410 EXCEPTION
2411 WHEN NO_DATA_FOUND
2412 THEN
2413 CLOSE c_country_code;
2414 WHEN OTHERS
2415 THEN
2416 CLOSE c_country_code;
2417 END;
2418
2419 /* OPEN c_timezone_check (p_timezone_id);
2420
2421 FETCH c_timezone_check
2422 INTO l_timezone_id;
2423
2424 IF l_timezone_id IS NULL
2425 THEN
2426 fnd_message.set_name ('CSF', 'CSF_TIME_ZONE_ERROR');
2427 fnd_msg_pub.add_detail ();
2428 RAISE fnd_api.g_exc_error;
2429 END IF;
2430
2431 CLOSE c_timezone_check; */
2432
2433 csf_resource_address_pvt.resolve_address
2434 (p_api_version => 1.0,
2435 p_init_msg_list => fnd_api.g_false,
2436 p_country => NVL (p_country,
2437 '_'
2438 ),
2439 p_state => NVL (p_state,
2440 '_'
2441 ),
2442 p_city => NVL (p_city,
2443 '_'
2444 ),
2445 p_postalcode => NVL
2446 (p_postal_code,
2447 '_'
2448 ),
2449 p_address1 => NVL
2450 (p_address1,
2451 '_'
2452 ),
2453 p_address2 => NVL
2454 (p_address2,
2455 '_'
2456 ),
2457 p_address3 => NVL
2458 (p_address3,
2459 '_'
2460 ),
2461 p_address4 => NVL
2462 (p_address4,
2463 '_'
2464 ),
2465 p_building_num => '_',
2466 p_alternate => '_',
2467 p_location_id => NVL
2468 (p_location_id,
2469 -1
2470 ),
2471 p_country_code => NVL
2472 (l_country_code,
2473 '_'
2474 ),
2475 p_province => NVL
2476 (p_province,
2477 '_'
2478 ),
2479 x_return_status => x_return_status,
2480 x_msg_count => x_msg_count,
2481 x_msg_data => x_msg_data,
2482 x_geometry => l_locus,
2483 p_update_address => 'T'
2484 );
2485
2486
2487 l_dup_msg_data := x_msg_data;
2488 IF x_return_status = fnd_api.g_ret_sts_success
2489 THEN
2490 x_result := g_valid_true;
2491 ELSE
2492 x_result := g_valid_false;
2493 /* fnd_message.set_name ('CSF', 'CSF_RESOLVE_ADDRESS_ERROR');
2494 fnd_message.set_token ('LOCATION_ID', p_location_id);
2495 fnd_message.set_token ('RETURN_STATUS', x_return_status);
2496 fnd_msg_pub.add_detail ();*/
2497 -- Added for Bug 7571215
2498 get_error_detail_info (
2499 p_api_version => 1.0,
2500 p_init_msg_list => fnd_api.g_false,
2501 p_country_code => NVL(l_country_code,'_'),
2502 p_country => NVL (p_country,'_'),
2503 p_state => NVL (p_state,'_'),
2504 p_city => NVL (p_city,'_'),
2505 p_postal_code => NVL (p_postal_code,'_'),
2506 x_error_detail => x_msg_data,
2507 x_dup_msg_data => l_dup_msg_data
2508 );
2509 RAISE fnd_api.g_exc_error;
2510 END IF;
2511
2512 -- End of API body
2513
2514 -- Standard check of p_commit
2515 IF fnd_api.to_boolean (p_commit)
2516 THEN
2517 COMMIT WORK;
2518 END IF;
2519
2520 -- Standard call to get message count and return the message info if the count is 1
2521 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2522 EXCEPTION
2523 WHEN fnd_api.g_exc_error
2524 THEN
2525 x_return_status := fnd_api.g_ret_sts_error;
2526 x_result := fnd_api.g_false;
2527 IF x_msg_data IS NULL
2528 THEN
2529 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2530 p_data => x_msg_data
2531 );
2532 END IF;
2533 WHEN fnd_api.g_exc_unexpected_error
2534 THEN
2535 x_return_status := fnd_api.g_ret_sts_unexp_error;
2536 x_result := fnd_api.g_false;
2537 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2538 p_data => x_msg_data
2539 );
2540 WHEN OTHERS
2541 THEN
2542 x_return_status := fnd_api.g_ret_sts_unexp_error;
2543 x_result := fnd_api.g_false;
2544
2545 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2546 THEN
2547 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2548 END IF;
2549
2550 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2551 p_data => x_msg_data);
2552 END validate_task_data;
2553
2554 FUNCTION is_task_address_valid (p_task_id IN jtf_tasks_b.task_id%TYPE)
2555 RETURN BOOLEAN
2556 IS
2557 CURSOR l_check_address_csr (l_task_id jtf_tasks_b.task_id%TYPE)
2558 IS
2559 SELECT task_id
2560 FROM csf_validate_tasks_v
2561 WHERE task_id = l_task_id AND validated_flag = 'N';
2562
2563 l_task_id jtf_tasks_b.task_id%TYPE;
2564 BEGIN
2565 BEGIN
2566 OPEN l_check_address_csr (p_task_id);
2567
2568 FETCH l_check_address_csr
2569 INTO l_task_id;
2570
2571 CLOSE l_check_address_csr;
2572 EXCEPTION
2573 WHEN NO_DATA_FOUND
2574 THEN
2575 CLOSE l_check_address_csr;
2576 WHEN OTHERS
2577 THEN
2578 CLOSE l_check_address_csr;
2579 END;
2580
2581 RETURN (l_task_id IS NULL);
2582 END is_task_address_valid;
2583
2584 PROCEDURE update_task_address (
2585 p_api_version IN NUMBER,
2586 p_init_msg_list IN VARCHAR2,
2587 p_commit IN VARCHAR2,
2588 p_validation_level IN NUMBER,
2589 p_location_id IN hz_locations.location_id%TYPE,
2590 p_address1 IN hz_locations.address1%TYPE,
2591 p_address2 IN hz_locations.address2%TYPE,
2592 p_address3 IN hz_locations.address3%TYPE,
2593 p_address4 IN hz_locations.address4%TYPE,
2594 p_city IN hz_locations.city%TYPE,
2595 p_postal_code IN hz_locations.postal_code%TYPE,
2596 p_state IN hz_locations.state%TYPE,
2597 p_province IN hz_locations.province%TYPE,
2598 p_county IN hz_locations.county%TYPE,
2599 p_country IN hz_locations.country%TYPE,
2600 p_validated_flag IN csf_ext_locations.validated_flag%TYPE,
2601 p_override_flag IN csf_ext_locations.override_flag%TYPE,
2602 p_timezone_id IN hz_locations.timezone_id%TYPE,
2603 x_return_status OUT NOCOPY VARCHAR2,
2604 x_msg_count OUT NOCOPY NUMBER,
2605 x_msg_data OUT NOCOPY VARCHAR2
2606 )
2607 IS
2608 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_TASK_ADDRESS';
2609 l_api_version CONSTANT NUMBER := 1.0;
2610 l_short_msg csf_ext_locations.log_detail_short%TYPE;
2611 l_long_msg csf_ext_locations.log_detail_long%TYPE;
2612 l_country_code hz_locations.country%TYPE;
2613 l_country hz_locations.country%TYPE;
2614 CURSOR c_country_code (p_country hz_locations.country%TYPE)
2615 IS
2616 SELECT ftt.territory_code country_code
2617 FROM fnd_territories_tl ftt
2618 WHERE UPPER (ftt.territory_short_name) = UPPER (p_country)
2619 AND ftt.language = 'US';
2620 BEGIN
2621 -- Standard check for call compatibility
2622 IF NOT fnd_api.compatible_api_call (l_api_version,
2623 p_api_version,
2624 l_api_name,
2625 g_pkg_name
2626 )
2627 THEN
2628 RAISE fnd_api.g_exc_unexpected_error;
2629 END IF;
2630
2631 -- Initialize message list if p_init_msg_list is set to TRUE
2632 IF fnd_api.to_boolean (p_init_msg_list)
2633 THEN
2634 fnd_msg_pub.initialize;
2635 END IF;
2636
2637 -- Initialize API return status to success
2638 x_return_status := fnd_api.g_ret_sts_success;
2639
2640 -- API body
2641 IF upper(p_country) = 'US VIRGIN ISLANDS'
2642 THEN
2643 l_country := 'VIRGIN ISLANDS, U.S.';
2644 ElSIF upper(p_country) = 'MACEDONIA'
2645 THEN
2646 l_country := 'MACEDONIA, THE FORMER YUGOSLAV REPUBLIC OF';
2647 ELSIF upper(p_country) = 'SLOVAK REPUBLIC'
2648 THEN
2649 l_country := 'SLOVAKIA';
2650 ELSIF upper(p_country) = 'RUSSIA'
2651 THEN
2652 l_country := 'RUSSIAN FEDERATION';
2653 ELSIF upper(p_country) = 'VATICAN CITY'
2654 THEN
2655 l_country := 'HOLY SEE (VATICAN CITY STATE)';
2656 ELSIF upper(p_country) = 'LUXEMBURG'
2657 THEN
2658 l_country := 'LUXEMBOURG';
2659 ELSE
2660 l_country := upper(p_country);
2661 END IF;
2662
2663 IF p_location_id IS NOT NULL
2664 THEN
2665 BEGIN
2666 OPEN c_country_code (l_country);
2667
2668 FETCH c_country_code
2669 INTO l_country_code;
2670
2671 CLOSE c_country_code;
2672 EXCEPTION
2673 WHEN NO_DATA_FOUND
2674 THEN
2675 CLOSE c_country_code;
2676 WHEN OTHERS
2677 THEN
2678 CLOSE c_country_code;
2679 END;
2680
2681 csf_locations_pkg.update_row_hz
2682 (p_last_update_date => SYSDATE,
2683 p_last_updated_by => NVL (fnd_global.user_id,
2684 -1
2685 ),
2686 p_last_update_login => NVL
2687 (fnd_global.conc_login_id,
2688 -1
2689 ),
2690 p_request_id => NVL
2691 (fnd_global.conc_request_id,
2692 -1
2693 ),
2694 p_program_application_id => NVL
2695 (fnd_global.prog_appl_id,
2696 -1
2697 ),
2698 p_program_id => NVL
2699 (fnd_global.conc_program_id,
2700 -1
2701 ),
2702 p_program_update_date => SYSDATE,
2703 p_address1 => p_address1,
2704 p_address2 => p_address2,
2705 p_address3 => p_address3,
2706 p_address4 => p_address4,
2707 p_city => p_city,
2708 p_postal_code => p_postal_code,
2709 p_county => p_county,
2710 p_state => p_state,
2711 p_province => p_province,
2712 p_country => l_country_code,
2713 p_validated_flag => p_validated_flag,
2714 p_location_id => p_location_id,
2715 p_timezone_id => p_timezone_id
2716 );
2717
2718 IF p_validated_flag = g_valid_true
2719 THEN
2720 fnd_message.set_name ('CSF', 'CSF_ADDRESS_VALIDATED_INFO');
2721 l_short_msg := fnd_message.get;
2722 fnd_message.set_name ('CSF', 'CSF_ADDRESS_VALIDATED_INFO');
2723 l_long_msg := fnd_message.get;
2724 END IF;
2725
2726 IF p_override_flag = g_valid_true
2727 THEN
2728 fnd_message.set_name ('CSF', 'CSF_ADDRESS_OVERRIDDEN_INFO');
2729 l_short_msg := fnd_message.get;
2730 fnd_message.set_name ('CSF', 'CSF_ADDRESS_OVERRIDDEN_INFO');
2731 l_long_msg := fnd_message.get;
2732 END IF;
2733
2734 csf_locations_pkg.update_row_ext
2735 (p_csf_ext_location_id => p_location_id,
2736 p_last_update_date => SYSDATE,
2737 p_last_updated_by => NVL (fnd_global.user_id,
2738 -1
2739 ),
2740 p_last_update_login => NVL
2741 (fnd_global.conc_login_id,
2742 -1
2743 ),
2744 p_request_id => NVL
2745 (fnd_global.conc_request_id,
2746 -1
2747 ),
2748 p_program_application_id => NVL
2749 (fnd_global.prog_appl_id,
2750 -1
2751 ),
2752 p_program_id => NVL
2753 (fnd_global.conc_program_id,
2754 -1
2755 ),
2756 p_program_update_date => SYSDATE,
2757 p_location_id => p_location_id,
2758 p_validated_flag => p_validated_flag,
2759 p_override_flag => p_override_flag,
2760 p_log_detail_short => l_short_msg,
2761 p_log_detail_long => l_long_msg
2762 );
2763 END IF;
2764
2765 -- End of API body
2766
2767 -- Standard check of p_commit
2768 IF fnd_api.to_boolean (p_commit)
2769 THEN
2770 COMMIT WORK;
2771 END IF;
2772
2773 -- Standard call to get message count and return the message info if the count is 1
2774 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2775 EXCEPTION
2776 WHEN fnd_api.g_exc_unexpected_error
2777 THEN
2778 x_return_status := fnd_api.g_ret_sts_unexp_error;
2779 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2780 p_data => x_msg_data
2781 );
2782 WHEN OTHERS
2783 THEN
2784 x_return_status := fnd_api.g_ret_sts_unexp_error;
2785
2786 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2787 THEN
2788 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2789 END IF;
2790
2791 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2792 p_data => x_msg_data);
2793 END update_task_address;
2794
2795 PROCEDURE dbgl (p_msg_data VARCHAR2)
2796 IS
2797 i PLS_INTEGER;
2798 l_msg VARCHAR2 (300);
2799 BEGIN
2800 i := 1;
2801
2802 LOOP
2803 l_msg := SUBSTR (p_msg_data, i, 255);
2804 EXIT WHEN l_msg IS NULL;
2805
2806 EXECUTE IMMEDIATE g_debug_p
2807 USING l_msg;
2808
2809 i := i + 255;
2810 END LOOP;
2811 END dbgl;
2812
2813 PROCEDURE put_stream (p_handle IN NUMBER, p_msg_data IN VARCHAR2)
2814 IS
2815 BEGIN
2816 IF p_handle = 0
2817 THEN
2818 dbgl (p_msg_data);
2819 ELSIF p_handle = -1
2820 THEN
2821 IF g_debug
2822 THEN
2823 dbgl (p_msg_data);
2824 END IF;
2825 ELSE
2826 fnd_file.put_line (p_handle, p_msg_data);
2827 END IF;
2828 END put_stream;
2829
2830 PROCEDURE show_messages (p_msg_data VARCHAR2)
2831 IS
2832 l_msg_count NUMBER;
2833 l_msg_data VARCHAR2 (2000);
2834 BEGIN
2835 IF p_msg_data IS NOT NULL
2836 THEN
2837 put_stream (g_output, p_msg_data);
2838 put_stream (g_log, p_msg_data);
2839 END IF;
2840
2841 fnd_msg_pub.count_and_get (fnd_api.g_false, l_msg_count, l_msg_data);
2842
2843 IF l_msg_count = 1
2844 THEN
2845 put_stream (g_output, l_msg_data);
2846 put_stream (g_log, l_msg_data);
2847 END IF;
2848
2849 put_stream (g_output,
2850 fnd_msg_pub.get (fnd_msg_pub.g_last, fnd_api.g_false)
2851 );
2852 fnd_msg_pub.RESET;
2853
2854 LOOP
2855 l_msg_data := fnd_msg_pub.get_detail (p_encoded => fnd_api.g_false);
2856 EXIT WHEN l_msg_data IS NULL;
2857 put_stream (g_log, l_msg_data);
2858 END LOOP;
2859 END show_messages;
2860
2861 PROCEDURE success_log_info (
2862 p_api_version IN NUMBER,
2863 p_init_msg_list IN VARCHAR2,
2864 p_commit IN VARCHAR2,
2865 p_validation_level IN NUMBER,
2866 p_task_rec IN task_rec_type,
2867 x_return_status OUT NOCOPY VARCHAR2,
2868 x_msg_count OUT NOCOPY NUMBER,
2869 x_msg_data OUT NOCOPY VARCHAR2
2870 )
2871 IS
2872 l_api_name CONSTANT VARCHAR2 (30) := 'SUCCESS_LOG_INFO';
2873 l_api_version CONSTANT NUMBER := 1.0;
2874 l_msg_data VARCHAR2 (32767);
2875 l_error VARCHAR2 (2000);
2876 l_error_detail VARCHAR2 (2000);
2877
2878 CURSOR c_check_ext_locations (l_location_id csf_ext_locations.location_id%TYPE)
2879 IS
2880 SELECT task_id
2881 FROM csf_ext_locations
2882 WHERE location_id = l_location_id
2883 AND validated_flag = 'N'
2884 AND override_flag = 'N';
2885
2886 l_location_id csf_ext_locations.location_id%TYPE;
2887 l_task_id csf_ext_locations.task_id%TYPE;
2888 BEGIN
2889 -- Standard check for call compatibility
2890 IF NOT fnd_api.compatible_api_call (l_api_version,
2891 p_api_version,
2892 l_api_name,
2893 g_pkg_name
2894 )
2895 THEN
2896 RAISE fnd_api.g_exc_unexpected_error;
2897 END IF;
2898
2899 -- Initialize message list if p_init_msg_list is set to TRUE
2900 IF fnd_api.to_boolean (p_init_msg_list)
2901 THEN
2902 fnd_msg_pub.initialize;
2903 END IF;
2904
2905 -- Initialize API return status to success
2906 x_return_status := fnd_api.g_ret_sts_success;
2907 -- API body
2908
2909 BEGIN
2910 OPEN c_check_ext_locations (p_task_rec.location_id);
2911
2912 FETCH c_check_ext_locations
2913 INTO l_task_id;
2914
2915 CLOSE c_check_ext_locations;
2916 EXCEPTION
2917 WHEN NO_DATA_FOUND
2918 THEN
2919 CLOSE c_check_ext_locations;
2920 WHEN OTHERS
2921 THEN
2922 CLOSE c_check_ext_locations;
2923 END;
2924
2925 IF l_task_id IS NOT NULL
2926 THEN
2927 csf_locations_pkg.update_row_ext
2928 (p_csf_ext_location_id => p_task_rec.location_id,
2929 p_last_update_date => SYSDATE,
2930 p_last_updated_by => NVL (fnd_global.user_id,
2931 -1
2932 ),
2933 p_last_update_login => NVL
2934 (fnd_global.conc_login_id,
2935 -1
2936 ),
2937 p_request_id => NVL
2938 (fnd_global.conc_request_id,
2939 -1
2940 ),
2941 p_program_application_id => NVL
2942 (fnd_global.prog_appl_id,
2943 -1
2944 ),
2945 p_program_id => NVL
2946 (fnd_global.conc_program_id,
2947 -1
2948 ),
2949 p_program_update_date => SYSDATE,
2950 p_location_id => p_task_rec.location_id,
2951 p_validated_flag => p_task_rec.validated_flag,
2952 p_override_flag => p_task_rec.override_flag,
2953 p_log_detail_short => NULL,
2954 p_log_detail_long => NULL
2955 );
2956 END IF;
2957
2958 -- End of API body
2959
2960 -- Standard check of p_commit
2961 IF fnd_api.to_boolean (p_commit)
2962 THEN
2963 COMMIT WORK;
2964 END IF;
2965
2966 -- Standard call to get message count and return the message info if the count is 1
2967 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2968 EXCEPTION
2969 WHEN fnd_api.g_exc_unexpected_error
2970 THEN
2971 x_return_status := fnd_api.g_ret_sts_unexp_error;
2972 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2973 p_data => x_msg_data
2974 );
2975 WHEN OTHERS
2976 THEN
2977 x_return_status := fnd_api.g_ret_sts_unexp_error;
2978
2979 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2980 THEN
2981 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2982 END IF;
2983
2984 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2985 p_data => x_msg_data);
2986 END success_log_info;
2987 -- This procedure used by Change Invalid address to get the counrty code
2988 -- and place id
2989 procedure get_country_details(
2990 p_country IN VARCHAR2,
2991 place_id OUT NOCOPY VARCHAR2,
2992 country_code OUT NOCOPY VARCHAR2)
2993 is
2994 TYPE country_refcur IS REF CURSOR;
2995 ref_cur country_refcur;
2996 sql_stmt_str VARCHAR2(1000);
2997 l_data_set_name VARCHAR2(40);
2998 BEGIN
2999
3000 l_data_set_name := fnd_profile.value('CSF_SPATIAL_MULTIDATASET_ENABLED');
3001 IF (l_data_set_name = 'N' OR l_data_set_name IS NULL ) THEN
3002 l_data_set_name := '';
3003 ELSE
3004 BEGIN
3005 l_data_set_name := '';
3006 sql_stmt_str := 'select spatial_dataset from csf_spatial_ctry_mappings
3007 WHERE spatial_country_name = ''' || upper(p_country)|| '''';
3008 OPEN ref_cur FOR sql_stmt_str;
3009 FETCH ref_cur INTO l_data_set_name;
3010 CLOSE ref_cur;
3011 EXCEPTION
3012 WHEN NO_DATA_FOUND THEN
3013 l_data_set_name := '';
3014 END;
3015 END IF;
3016
3017 sql_stmt_str := 'SELECT PLACE_ID, COUNTRY_CODE_A3 FROM csf_sdm_ctry_profiles'||l_data_set_name||'
3018 WHERE country_name = ''' || upper(p_country)|| '''';
3019 OPEN ref_cur FOR sql_stmt_str;
3020 FETCH ref_cur INTO place_id,country_code;
3021 CLOSE ref_cur;
3022 END;
3023
3024 procedure odf_post_installation_steps(
3025 errbuf OUT NOCOPY VARCHAR2,
3026 retcode OUT NOCOPY VARCHAR2,
3027 owner_name IN VARCHAR2,
3028 data_set IN VARCHAR2)
3029 is
3030 TYPE cur_typ IS REF CURSOR;
3031 obj_count NUMBER(10);
3032 c cur_typ;
3033 query_str VARCHAR2(200);
3034 country_name VARCHAR2(60);
3035 country_code_2 VARCHAR2(2);
3036 area_id NUMBER(10);
3037 --l_data_set_name VARCHAR2(40);
3038 --l_user_name VARCHAR2(40);
3039 BEGIN
3040 query_str := 'SELECT country_name,country_code_2,area_id FROM '||owner_name||'.GC_COUNTRY_PROFILE';
3041
3042 select count(*) into obj_count from all_objects where owner=owner_name;
3043 if(obj_count>0) then
3044 delete from csf_odf_ctry_mapping where dataset=data_set;
3045 put_stream (g_output, '***********Field Service Geospatial Post Installation Steps Started***********');
3046 put_stream (g_output, ' ');
3047 --query_str_srch := 'SELECT country_name, where dataset='||data_set||'';
3048 OPEN c FOR query_str;
3049 LOOP
3050 FETCH c INTO country_name,country_code_2,area_id;
3051 EXIT WHEN c%NOTFOUND;
3052 insert into csf_odf_ctry_mapping (user_name,country_name,country_code_2,dataset,area_id) values (owner_name,country_name,country_code_2,data_set,area_id);
3053 put_stream (g_output, 'Inserting the country :"'||country_name||'" with UserName:'||owner_name);
3054 put_stream (g_log, 'Inserting the country :"'||country_name||'" with UserName:'||owner_name);
3055 --process row here
3056 END LOOP;
3057 CLOSE c;
3058 end if;
3059 COMMIT;
3060 put_stream (g_log, ' ');
3061 put_stream (g_output, ' ');
3062 select count(*) into obj_count from csf_odf_ctry_mapping where dataset=data_set;
3063 put_stream (g_output, obj_count || 'rows has been inserted into csf_odf_ctry_mapping table');
3064 put_stream (g_log, obj_count || 'rows has been inserted into csf_odf_ctry_mapping table');
3065 put_stream (g_output, ' ');
3066 put_stream (g_output, '***********Field Service Geospatial Post Installation Steps Completed***********');
3067 END;
3068
3069
3070 procedure get_odf_admin_level(
3071 l_place_id IN VARCHAR2,
3072 l_ctry_code IN VARCHAR2,
3073 region_admin_level OUT NOCOPY NUMBER,
3074 municipality_admin_level OUT NOCOPY NUMBER,
3075 settlement_admin_level OUT NOCOPY NUMBER)
3076 is
3077 TYPE country_refcur IS REF CURSOR;
3078 ref_cur country_refcur;
3079 sql_stmt_str VARCHAR2(1000);
3080 l_data_set_name VARCHAR2(40);
3081 l_user_name VARCHAR2(40);
3082 BEGIN
3083
3084 BEGIN
3085 --l_data_set_name := '';
3086 sql_stmt_str := 'select user_name from csf_odf_ctry_mapping where country_code_2 = '''||l_ctry_code||'''';
3087 OPEN ref_cur FOR sql_stmt_str;
3088 FETCH ref_cur INTO l_user_name;
3089 CLOSE ref_cur;
3090 EXCEPTION
3091 WHEN NO_DATA_FOUND THEN
3092 l_data_set_name := '';
3093 END;
3094 sql_stmt_str := 'select REGION_LEVEL,MUNICIPALITY_LEVEL,SETTLEMENT_LEVEL from '||l_user_name||'.gc_country_profile where area_id = '||l_place_id||'';
3095 OPEN ref_cur FOR sql_stmt_str;
3096 FETCH ref_cur INTO region_admin_level,municipality_admin_level,settlement_admin_level;
3097 CLOSE ref_cur;
3098 END;
3099
3100 procedure get_odf_country_details(
3101 p_country IN VARCHAR2,
3102 p_ctry_lang_code OUT NOCOPY VARCHAR2,
3103 place_id OUT NOCOPY VARCHAR2,
3104 country_code OUT NOCOPY VARCHAR2)
3105 is
3106 TYPE country_refcur IS REF CURSOR;
3107 ref_cur country_refcur;
3108 sql_stmt_str VARCHAR2(1000);
3109 l_data_set_name VARCHAR2(40);
3110 l_user_name VARCHAR2(40);
3111 l_country_name varchar2(40);
3112 BEGIN
3113
3114 BEGIN
3115 --l_data_set_name := '';
3116 l_country_name := p_country;
3117 if upper(l_country_name) = 'UNITED KINGDOM' then
3118 l_country_name := 'England';
3119 end if;
3120 sql_stmt_str := 'select user_name from csf_odf_ctry_mapping where country_name = ''' ||l_country_name|| '''';
3121 OPEN ref_cur FOR sql_stmt_str;
3122 FETCH ref_cur INTO l_user_name;
3123 CLOSE ref_cur;
3124 EXCEPTION
3125 WHEN NO_DATA_FOUND THEN
3126 l_user_name := NULL;
3127 END;
3128 if(l_user_name is not null) then
3129 sql_stmt_str := 'select area_id,country_code_2,lang_code_1 from '||l_user_name||'.gc_country_profile where country_name = ''' ||p_country|| '''';
3130 OPEN ref_cur FOR sql_stmt_str;
3131 FETCH ref_cur INTO place_id,country_code,p_ctry_lang_code;
3132 CLOSE ref_cur;
3133 else
3134 place_id := null;
3135 country_code := null;
3136 p_ctry_lang_code := null;
3137 end if;
3138 END;
3139
3140 -- This procedure used by Change Invalid address to get the counrty code for United Kingdom
3141 procedure get_country_details_GBR(
3142 p_city IN VARCHAR2,
3143 p_country IN VARCHAR2,
3144 place_id OUT NOCOPY VARCHAR2)
3145 is
3146 TYPE country_refcur IS REF CURSOR;
3147 c_countryfromcity country_refcur;
3148 ref_cur country_refcur;
3149 sql_stmt_str VARCHAR2(1000);
3150 l_data_set_name VARCHAR2(40);
3151 BEGIN
3152
3153 l_data_set_name := fnd_profile.value('CSF_SPATIAL_MULTIDATASET_ENABLED');
3154 IF (l_data_set_name = 'N' OR l_data_set_name IS NULL ) THEN
3155 l_data_set_name := '';
3156 ELSE
3157 BEGIN
3158 l_data_set_name := '';
3159 sql_stmt_str := 'select spatial_dataset from csf_spatial_ctry_mappings
3160 WHERE spatial_country_name = ''' || upper(p_country)|| '''';
3161 OPEN ref_cur FOR sql_stmt_str;
3162 FETCH ref_cur INTO l_data_set_name;
3163 CLOSE ref_cur;
3164 EXCEPTION
3165 WHEN NO_DATA_FOUND THEN
3166 l_data_set_name := '';
3167 END;
3168 END IF;
3169
3170 sql_stmt_str := 'SELECT DISTINCT place_id
3171 FROM csf_lf_places'||l_data_set_name||
3172 ' WHERE place_parent_level = -1 START WITH place_id IN
3173 (SELECT pn.place_id
3174 FROM csf_lf_names'||l_data_set_name|| ' n,
3175 csf_lf_place_names'||l_data_set_name||' pn
3176 WHERE n.name_id = pn.name_id
3177 AND n.name = ''' || upper(p_city)|| ''' )
3178 CONNECT BY PRIOR parent_place_id = place_id';
3179 OPEN c_countryfromcity FOR sql_stmt_str;
3180 FETCH c_countryfromcity INTO place_id;
3181 CLOSE c_countryfromcity;
3182 END;
3183
3184 begin
3185 init_package;
3186 end csf_task_address_pvt;
3187