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