1 PACKAGE BODY hz_elocation_pkg AS
2 /*$Header: ARHELOCB.pls 120.17.12010000.2 2008/11/12 01:03:40 nsinghai ship $*/
3
4 g_sets_per_commit CONSTANT NUMBER := 5;
5 g_file_debug BOOLEAN := FALSE;
6 g_cp_detail CONSTANT VARCHAR2(1) := FND_PROFILE.VALUE('HZ_CP_DETAIL');
7
8 --------------------------------------
9 -- private procedures and functions
10 --------------------------------------
11 --
12 -- PRIVATE PROCEDURE enable_debug
13 -- DESCRIPTION
14 -- Turn on debug mode.
15 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
16 -- HZ_UTILITY_V2PUB.enable_debug
17 -- MODIFICATION HISTORY
18 -- 01-10-2002 Herve Yu
19 --------------------------------------
20 /*PROCEDURE enable_debug IS
21 BEGIN
22 g_debug_count := g_debug_count + 1;
23 IF G_DEBUG_COUNT = 1 THEN
24 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
25 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
26 THEN
27 hz_utility_v2pub.enable_debug;
28 g_debug := TRUE;
29 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' THEN
30 g_file_debug := TRUE;
31 END IF;
32 END IF;
33 END IF;
34 END enable_debug;
35 */
36
37 --------------------------------------
38 -- PRIVATE PROCEDURE disable_debug
39 -- DESCRIPTION
40 -- Turn off debug mode.
41 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
42 -- HZ_UTILITY_V2PUB.disable_debug
43 -- MODIFICATION HISTORY
44 -- 01-10-2002 Herve Yu
45 --------------------------------------
46 /*PROCEDURE disable_debug IS
47 BEGIN
48 IF g_debug THEN
49 g_debug_count := g_debug_count - 1;
50 IF g_debug_count = 0 THEN
51 hz_utility_v2pub.disable_debug;
52 g_debug := FALSE;
53 g_file_debug := FALSE;
54 END IF;
55 END IF;
56 END disable_debug;
57 */
58
59 --------------------------------------
60 -- Copy the status for the upper layer
61 --------------------------------------
62 PROCEDURE status_handler(
63 l_return_status IN VARCHAR2,
64 x_return_status IN OUT NOCOPY VARCHAR2
65 ) IS
66 BEGIN
67 IF x_return_status = fnd_api.g_ret_sts_error THEN
68 NULL;
69 ELSIF x_return_status = 'W'
70 AND l_return_status = fnd_api.g_ret_sts_success
71 THEN
72 NULL;
73 ELSE
74 x_return_status := l_return_status;
75 END IF;
76 END status_handler;
77
78 PROCEDURE trace_handler (msg IN VARCHAR2) IS
79 v VARCHAR2(2000);
80 BEGIN
81 v := hz_geocode_pkg.remove_whitespace(msg);
82 v := TRIM(v);
83 IF v IS NOT NULL THEN
84 -- Fix perf bug 3669930, 4220460, cache profile option value into global variable
85 IF g_cp_detail = 'Y' THEN
86 fnd_file.put_line(fnd_file.output,v);
87 END IF;
88 fnd_file.put_line(fnd_file.log,v);
89 END IF;
90 END trace_handler;
91
92 --------------------------------------
93 -- PRIVATE PROCEDURE time_put_line
94 -- DESCRIPTION
95 -- Utility routine for performance testing. Prints the argument with
96 -- a timestamp.
97 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
98 -- hz_utility_v2pub.debug
99 -- MODIFICATION HISTORY
100 -- 01-10-2002 J. del Callar
101 --------------------------------------
102 /*PROCEDURE time_put_line (msg IN VARCHAR2) IS
103 BEGIN
104 IF g_file_debug THEN
105 fnd_file.put_line(fnd_file.log,
106 TO_CHAR(SYSDATE, 'HH:MI:SS') ||
107 ': ' || SUBSTRB(msg, 1, 240));
108 ELSE
109 hz_utility_v2pub.debug(TO_CHAR(SYSDATE, 'HH:MI:SS') ||
110 ': ' || SUBSTRB(msg, 1, 240));
111 END IF;
112 END time_put_line;
113 */
114 PROCEDURE time_put_line (msg IN VARCHAR2) IS
115 BEGIN
116 fnd_file.put_line(fnd_file.log,
117 TO_CHAR(SYSDATE, 'HH:MI:SS') ||
118 ': ' || SUBSTRB(msg, 1, 240));
119 END time_put_line;
120
121 -------------------------------------------------------------
122 -- Call the HZ_LOCATIONS_PKG Table handler to update location
123 -------------------------------------------------------------
124 PROCEDURE update_geo_location (
125 p_location_id IN NUMBER,
126 p_geo IN mdsys.sdo_geometry,
127 p_geo_status IN VARCHAR2,
128 x_count IN OUT NOCOPY NUMBER,
129 x_return_status IN OUT NOCOPY VARCHAR2,
130 x_msg_count IN OUT NOCOPY NUMBER,
131 x_msg_data IN OUT NOCOPY VARCHAR2
132 ) IS
133 l_rowid VARCHAR2(1000);
134 l_debug_prefix VARCHAR2(30) := '';
135 CURSOR curowid IS
136 SELECT rowid
137 FROM hz_locations
138 WHERE location_id = p_location_id;
139 BEGIN
140 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
141 hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_geo_location for location_id :'||
142 TO_CHAR(p_location_id)||'(+)',
143 p_prefix=>l_debug_prefix,
144 p_msg_level=>fnd_log.level_procedure);
145 END IF;
146 OPEN curowid;
147 FETCH curowid INTO l_rowid;
148 IF curowid%NOTFOUND THEN
149 x_return_status := fnd_api.g_ret_sts_error;
150 fnd_message.set_name('AR', 'HZ_NO_LOCATION_FOUND');
151 fnd_message.set_token('LOC_ID', TO_CHAR(p_location_id));
152 fnd_msg_pub.add;
153 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
154 hz_utility_v2pub.debug(p_message=>'No Record found : Error',
155 p_prefix=>'ERROR',
156 p_msg_level=>fnd_log.level_error);
157 END IF;
158 ELSE
159 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
160 hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_row for location_id :' ||
161 TO_CHAR(p_location_id)||'(+)',
162 p_prefix=>l_debug_prefix,
163 p_msg_level=>fnd_log.level_procedure);
164 END IF;
165 hz_locations_pkg.update_row (
166 x_rowid => l_rowid,
167 x_location_id => p_location_id,
168 x_attribute_category => NULL,
169 x_attribute1 => NULL,
170 x_attribute2 => NULL,
171 x_attribute3 => NULL,
172 x_attribute4 => NULL,
173 x_attribute5 => NULL,
174 x_attribute6 => NULL,
175 x_attribute7 => NULL,
176 x_attribute8 => NULL,
177 x_attribute9 => NULL,
178 x_attribute10 => NULL,
179 x_attribute11 => NULL,
180 x_attribute12 => NULL,
181 x_attribute13 => NULL,
182 x_attribute14 => NULL,
183 x_attribute15 => NULL,
184 x_attribute16 => NULL,
185 x_attribute17 => NULL,
186 x_attribute18 => NULL,
187 x_attribute19 => NULL,
188 x_attribute20 => NULL,
189 x_orig_system_reference => NULL,
190 x_country => NULL,
191 x_address1 => NULL,
192 x_address2 => NULL,
193 x_address3 => NULL,
194 x_address4 => NULL,
195 x_city => NULL,
196 x_postal_code => NULL,
197 x_state => NULL,
198 x_province => NULL,
199 x_county => NULL,
200 x_address_key => NULL,
201 x_address_style => NULL,
202 x_validated_flag => NULL,
203 x_address_lines_phonetic => NULL,
204 x_po_box_number => NULL,
205 x_house_number => NULL,
206 x_street_suffix => NULL,
207 x_street => NULL,
208 x_street_number => NULL,
209 x_floor => NULL,
210 x_suite => NULL,
211 x_postal_plus4_code => NULL,
212 x_position => NULL,
213 x_location_directions => NULL,
214 x_address_effective_date => NULL,
215 x_address_expiration_date => NULL,
216 x_clli_code => NULL,
217 x_language => NULL,
218 x_short_description => NULL,
219 x_description => NULL,
220 x_content_source_type => NULL,
221 x_loc_hierarchy_id => NULL,
222 x_sales_tax_geocode => NULL,
223 x_sales_tax_inside_city_limits => NULL,
224 x_fa_location_id => NULL,
225 x_geometry => p_geo,
226 x_geometry_status_code => p_geo_status,
227 x_object_version_number => NULL,
228 x_timezone_id => NULL,
229 x_created_by_module => NULL,
230 x_application_id => NULL,
231 --3326341.
232 x_delivery_point_code => NULL
233 );
234
235 x_count := x_count + 1;
236
237 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
238 hz_utility_v2pub.debug(p_message=>'Location successfully updated for location_id :'||TO_CHAR(p_location_id),
239 p_prefix =>l_debug_prefix,
240 p_msg_level=>fnd_log.level_statement);
241 END IF;
242 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
243 hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_row for location_id :' ||
244 TO_CHAR(p_location_id)||'(-)',
245 p_prefix=>l_debug_prefix,
246 p_msg_level=>fnd_log.level_procedure);
247 END IF;
248
249 -- Fix perf bug 3669930, 4220460, cache profile option value into global variable
250 IF g_cp_detail = 'Y' THEN
251 fnd_message.set_name('AR','HZ_LOCATION_UPDATED');
252 fnd_message.set_token('LOC_ID', TO_CHAR(p_location_id));
253 fnd_msg_pub.add;
254 END IF;
255 END IF;
256 CLOSE curowid;
257
258 fnd_msg_pub.count_and_get(
259 p_encoded => fnd_api.g_false,
260 p_count => x_msg_count,
261 p_data => x_msg_data
262 );
263 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
264 hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_geo_location for location_id :'
265 || TO_CHAR(p_location_id)||'(-)',
266 p_prefix=>l_debug_prefix,
267 p_msg_level=>fnd_log.level_procedure);
268 END IF;
269 END update_geo_location;
270
271 --------------------------------------
272 -- PUBLIC PROCEDURE update_geometry
273 -- DESCRIPTION
274 -- Synchronized geometry in hz_locations with latitude and longitude from
275 -- Oracle's eLocation service.
276 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
277 -- hz_utility_v2pub
278 -- fnd_message
279 -- fnd_msg_pub
280 -- MODIFICATION HISTORY
281 -- 01-10-2002 H. Yu Created.
282 -- 03-21-2002 J. del Callar Bug 2252141: Added call to spatial index
283 -- rebuild.
284 -- 10-03-2005 swbhatna ER 4549508: Modified code to handle update of Spatial Information
285 -- for Standalone Locations,ie. Locations without any Party Site information.
286 --------------------------------------
287 PROCEDURE update_geometry (
288 errbuf OUT NOCOPY VARCHAR2,
289 retcode OUT NOCOPY VARCHAR2,
290 p_loc_type IN VARCHAR2 DEFAULT 'P',
291 p_site_use_type IN VARCHAR2 DEFAULT NULL,
292 p_country IN VARCHAR2 DEFAULT NULL,
293 p_iden_addr_only IN VARCHAR2 DEFAULT 'N',
294 p_incremental IN VARCHAR2 DEFAULT 'N',
295 p_all_partial IN VARCHAR2 DEFAULT 'ALL',
296 p_nb_row_update IN VARCHAR2 DEFAULT 'ALL',
297 p_nb_row IN NUMBER DEFAULT 20,
298 p_nb_try IN NUMBER DEFAULT 3
299
300 ) IS
301 TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
302 TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
303 TYPE citylist IS TABLE OF hz_locations.city%TYPE;
304 TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
305 TYPE statelist IS TABLE OF hz_locations.state%TYPE;
306 TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
307 TYPE countylist IS TABLE OF hz_locations.county%TYPE;
308 TYPE provincelist IS TABLE OF hz_locations.province%TYPE;
309 l_location_ids locationlist;
310 l_address1s addresslist;
311 l_address2s addresslist;
312 l_address3s addresslist;
313 l_address4s addresslist;
314 l_cities citylist;
315 l_postal_codes pcodelist;
316 l_states statelist;
317 l_countries countrylist;
318 l_counties countylist;
319 l_provinces provincelist;
320
321 -- run this query if the party site use is specified,
322 -- we are NOT running in incremental mode
323 -- and we are NOT running for Standalone Locations
324 -- and p_country was not passed
325 CURSOR cu_loc1a (p_request_id IN NUMBER) IS
329 WHERE EXISTS (SELECT 1
326 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
327 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
328 FROM hz_locations hl
330 FROM hz_party_site_uses hpsu,
331 hz_party_sites hps
332 WHERE hpsu.site_use_type = p_site_use_type
333 AND hpsu.party_site_id = hps.party_site_id
334 AND hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
335 AND hps.location_id = hl.location_id)
336 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2);
337
338 -- run this query if the party site use is specified,
339 -- run this query if the party site use is specified,
340 -- we are NOT running in incremental mode
341 -- and we are NOT running for Standalone Locations
342 -- and p_country was passed
343 CURSOR cu_loc1ac (p_request_id IN NUMBER) IS
344 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
345 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
346 FROM hz_locations hl
347 WHERE EXISTS (SELECT 1
348 FROM hz_party_site_uses hpsu,
349 hz_party_sites hps
350 WHERE hpsu.site_use_type = p_site_use_type
351 AND hpsu.party_site_id = hps.party_site_id
352 AND hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
353 AND hps.location_id = hl.location_id)
354 AND hl.country = p_country
355 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2);
356
357 -- run this query if the party site use is specified,
358 -- we are running in incremental mode.
359 -- and we are NOT running for Standalone Locations
360 -- and p_country was not passed
361 CURSOR cu_loc1b (p_request_id IN NUMBER) IS
362 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
363 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
364 FROM hz_locations hl
365 WHERE EXISTS (SELECT 1
366 FROM hz_party_site_uses hpsu,
367 hz_party_sites hps
368 WHERE hpsu.site_use_type = p_site_use_type
369 AND hpsu.party_site_id = hps.party_site_id
370 AND hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
371 AND hps.location_id = hl.location_id)
372 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
373 AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL);
374
375 -- run this query if the party site use is specified,
376 -- we are running in incremental mode.
377 -- and we are NOT running for Standalone Locations
378 -- and p_country was passed
379 CURSOR cu_loc1bc (p_request_id IN NUMBER) IS
380 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
381 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
382 FROM hz_locations hl
383 WHERE EXISTS (SELECT 1
384 FROM hz_party_site_uses hpsu,
385 hz_party_sites hps
386 WHERE hpsu.site_use_type = p_site_use_type
387 AND hpsu.party_site_id = hps.party_site_id
388 AND hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
389 AND hps.location_id = hl.location_id)
390 AND hl.country = p_country
391 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
392 AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL);
393
394 -- run this query if party site use is not specified,
395 -- we are NOT running in incremental mode
396 -- and we are NOT running for Standalone Locations
397 -- and p_country was not passed
398 CURSOR cu_loc2a (p_request_id IN NUMBER) IS
399 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
400 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
401 FROM hz_locations hl
402 WHERE EXISTS (SELECT 1
403 FROM hz_party_sites hps
404 WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
405 AND hps.location_id = hl.location_id)
406 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2);
407
408 -- run this query if party site use is not specified,
409 -- we are NOT running in incremental mode
410 -- and we are NOT running for Standalone Locations
411 -- and p_country was passed
412 CURSOR cu_loc2ac (p_request_id IN NUMBER) IS
413 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
414 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
415 FROM hz_locations hl
416 WHERE EXISTS (SELECT 1
417 FROM hz_party_sites hps
418 WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
419 AND hps.location_id = hl.location_id)
420 AND hl.country = p_country
421 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2);
422
426 CURSOR cu_loc2b (p_request_id IN NUMBER) IS
423 -- run this query if party site use is not specified,
424 -- we are running in incremental mode
425 -- and we are NOT running for Standalone Locations
427 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
428 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
429 FROM hz_locations hl
430 WHERE EXISTS (SELECT 1
431 FROM hz_party_sites hps
432 WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
433 AND hps.location_id = hl.location_id)
434 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
435 AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL);
436
437 -- run this query if party site use is not specified,
438 -- we are running in incremental mode
439 -- and we are NOT running for Standalone Locations
440 -- and p_country was passed
441 CURSOR cu_loc2bc (p_request_id IN NUMBER) IS
442 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
443 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
444 FROM hz_locations hl
445 WHERE EXISTS (SELECT 1
446 FROM hz_party_sites hps
447 WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
448 AND hps.location_id = hl.location_id)
449 AND hl.country = p_country
450 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
451 AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL);
452
453 -- run this query if we are running for Standalone Locations
454 -- and NOT in incremental mode
455 -- and p_country was not passed
456 CURSOR cu_loc3a (p_request_id IN NUMBER) IS
457 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
458 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
459 FROM hz_locations hl
460 WHERE NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
461 AND NOT EXISTS (SELECT 1
462 FROM HZ_PARTY_SITES hps
463 WHERE hps.location_id = hl.location_id );
464
465 -- run this query if we are running for Standalone Locations
466 -- and NOT in incremental mode
467 -- and p_country was passed
468 CURSOR cu_loc3ac (p_request_id IN NUMBER) IS
469 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
470 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
471 FROM hz_locations hl
472 WHERE hl.country = p_country
473 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
474 AND NOT EXISTS (SELECT 1
475 FROM HZ_PARTY_SITES hps
476 WHERE hps.location_id = hl.location_id );
477
478 -- run this query if we are running for Standalone Locations
479 -- and in incremental mode
480 -- and p_country was not passed
481 CURSOR cu_loc3b (p_request_id IN NUMBER) IS
482 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
483 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
484 FROM hz_locations hl
485 WHERE NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
486 AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL)
487 AND NOT EXISTS (SELECT 1
488 FROM HZ_PARTY_SITES hps
489 WHERE hps.location_id = hl.location_id );
490
491 -- run this query if we are running for Standalone Locations
492 -- and in incremental mode
493 -- and p_country was passed
494 CURSOR cu_loc3bc (p_request_id IN NUMBER) IS
495 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
496 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
497 FROM hz_locations hl
498 WHERE hl.country = p_country
499 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
500 AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL)
501 AND NOT EXISTS (SELECT 1
502 FROM HZ_PARTY_SITES hps
503 WHERE hps.location_id = hl.location_id );
504
505 l_array hz_geocode_pkg.loc_array := hz_geocode_pkg.loc_array();
506 l_rec hz_location_v2pub.location_rec_type;
507 l_http_ad VARCHAR2(200);
508 l_proxy VARCHAR2(100);
509 l_port VARCHAR2(10);
510 l_port_num NUMBER;
511 x_return_status VARCHAR2(10);
512 x_msg_count NUMBER;
513 x_msg_data VARCHAR2(2000);
514 cpt NUMBER := 0;
515 cpt_update NUMBER := 0;
516 l_nb_row_update NUMBER DEFAULT NULL;
517 l_nb_update NUMBER;
518 l_str_exe VARCHAR2(500);
519 expect_http_ad EXCEPTION;
520 exchttp EXCEPTION;
521 port_number EXCEPTION;
522 nlsnumexp EXCEPTION;
523 morethanmaxrow EXCEPTION;
524 atleastonerow EXCEPTION;
525 msg VARCHAR2(2000);
526 l_return_status VARCHAR2(10);
530 l_nb_retries NUMBER := NVL(p_nb_try, 3);
527 l_set_size NUMBER;
528 l_request_id NUMBER := hz_utility_v2pub.request_id;
529 i NUMBER;
531 l_batch_size NUMBER := NVL(p_nb_row, hz_geocode_pkg.g_max_rows);
532 l_retcode VARCHAR2(10);
533 l_errbuf VARCHAR2(4000);
534 l_proxy_var VARCHAR2(240);
535 l_port_var VARCHAR2(240);
536 l_debug_prefix VARCHAR2(30) := '';
537 BEGIN
538
539 --enable_debug;
540
541 l_nb_update := 0;
542 l_set_size := l_batch_size * g_sets_per_commit;
543
544 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
545 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (+)',
546 p_prefix=>l_debug_prefix,
547 p_msg_level=>fnd_log.level_procedure);
548 END IF;
549
550
551
552 x_return_status := fnd_api.g_ret_sts_success;
553 l_return_status := fnd_api.g_ret_sts_success;
554 fnd_msg_pub.initialize;
555
556 retcode := '0';
557 fnd_file.put_line(fnd_file.log,
558 fnd_message.get_string('FND',
559 'CONC-START PROGRAM EXECUTION'));
560 fnd_file.put_line(fnd_file.log, '');
561
562 fnd_file.put_line(fnd_file.output,
563 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS: '));
564 fnd_file.put_line(fnd_file.output,
565 fnd_message.get_string('FND',
566 'CONC-START PROGRAM EXECUTION'));
567
568 IF hz_geocode_pkg.is_nls_num_char_pt_com <> 'Y' THEN
569 l_str_exe := 'ALTER SESSION SET nls_numeric_characters = ''.,''';
570 EXECUTE IMMEDIATE l_str_exe;
571 END IF;
572
573 IF p_all_partial <> 'ALL' THEN
574 IF p_nb_row_update IS NULL OR p_nb_row_update = 'ALL' THEN
575 l_nb_row_update := 1000;
576 ELSE
577 l_nb_row_update := TO_NUMBER(p_nb_row_update);
578 END IF;
579 IF l_nb_row_update IS NULL OR l_nb_row_update <= 0 THEN
580 time_put_line('At least one row error.');
581 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
582 hz_utility_v2pub.debug(p_message=>'At least one row error.',
583 p_prefix=>'ERROR',
584 p_msg_level=>fnd_log.level_error);
585 END IF;
586 RAISE atleastonerow;
587 END IF;
588 END IF;
589
590 IF l_batch_size > hz_geocode_pkg.g_max_rows THEN
591 RAISE morethanmaxrow;
592 END IF;
593
594 -- Get the website we're supposed to access for geospatial information.
595 fnd_profile.get('HZ_GEOCODE_WEBSITE', l_http_ad);
596 IF l_http_ad IS NULL THEN
597 time_put_line('HTTP address missing');
598 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
599 hz_utility_v2pub.debug(p_message=>'HTTP address missing',
600 p_prefix=>'ERROR',
601 p_msg_level=>fnd_log.level_error);
602 END IF;
603 RAISE expect_http_ad;
604 END IF;
605
606 -- Only get the proxy server if we need it - check the proxy bypass list.
607 IF hz_geocode_pkg.in_bypass_list(
608 l_http_ad,
609 fnd_profile.value('WEB_PROXY_BYPASS_DOMAINS')
610 )
611 THEN
612 -- site is in the bypass list.
613 l_proxy_var := 'Null proxy';
614 l_port_var := 'Null port';
615 l_proxy := NULL;
616 l_port := NULL;
617 ELSE
618 -- site is not in the bypass list.
619 -- First, attempt to get proxy value from FND. If the proxy name is not
620 -- found, try the TCA values regardless of whether the port is found.
621 l_proxy_var := 'WEB_PROXY_HOST';
622 l_port_var := 'WEB_PROXY_PORT';
623 l_proxy := fnd_profile.value(l_proxy_var);
624 l_port := fnd_profile.value(l_port_var);
625 END IF;
626
627 -- log the profile options that are being used to run this program.
628 fnd_file.put_line(fnd_file.log,
629 fnd_message.get_string('FND', 'PROFILES-VALUES'));
630 fnd_file.put_line(fnd_file.log, 'HZ_GEOCODE_WEBSITE: ' || l_http_ad);
631 fnd_file.put_line(fnd_file.log, l_proxy_var || ': ' || l_proxy);
632 fnd_file.put_line(fnd_file.log, l_port_var || ': ' || l_port);
633 fnd_file.put_line(fnd_file.log, '');
634
635 -- repeat in the output file.
636 fnd_file.put_line(fnd_file.output,
637 fnd_message.get_string('FND', 'PROFILES-VALUES'));
638 fnd_file.put_line(fnd_file.output, 'HZ_GEOCODE_WEBSITE: ' || l_http_ad);
639 fnd_file.put_line(fnd_file.output, l_proxy_var || ': ' || l_proxy);
640 fnd_file.put_line(fnd_file.output, l_port_var || ': ' || l_port);
641 fnd_file.put_line(fnd_file.output, '');
642
643 -- repeat in debug output.
644 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
645 hz_utility_v2pub.debug(p_message=>'HZ_GEOCODE_WEBSITE: ' || l_http_ad,
646 p_prefix =>l_debug_prefix,
647 p_msg_level=>fnd_log.level_statement);
648 hz_utility_v2pub.debug(p_message=>l_proxy_var || ': ' || l_proxy,
649 p_prefix =>l_debug_prefix,
650 p_msg_level=>fnd_log.level_statement);
654 END IF;
651 hz_utility_v2pub.debug(p_message=>l_port_var || ': ' || l_port,
652 p_prefix =>l_debug_prefix,
653 p_msg_level=>fnd_log.level_statement);
655
656 IF l_port IS NOT NULL THEN
657 -- J. del Callar: set the port number and handle non-numeric values
658 BEGIN
659 l_port_num := TO_NUMBER(l_port);
660 EXCEPTION
661 WHEN OTHERS THEN
662 RAISE port_number;
663 END;
664 ELSE
665 l_port_num := NULL;
666 END IF;
667
668 -- J. del Callar: main transaction loop: process all records picked up
669 -- by cu_loc and commit every l_set_size records.
670 LOOP
671 -- J. del Callar: re-open the cursor only if it has been closed by the
672 -- commit statement, or if it has not been opened before.
673
674 -- swbhatna: Added IF loop to check for p_loc_type value. If 'P', then earlier code remains intact
675 IF p_loc_type = 'P' THEN
676
677 IF p_site_use_type IS NOT NULL AND p_incremental = 'N' THEN
678 -- J. del Callar: use cu_loc1a for non-null site uses
679 -- and non-incremental mode.
680 IF(p_country IS NULL) THEN
681 IF NOT cu_loc1a%ISOPEN THEN
682 OPEN cu_loc1a (l_request_id);
683 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
684 hz_utility_v2pub.debug(p_message=>'Opening cursor 1a with args:',
685 p_prefix =>l_debug_prefix,
686 p_msg_level=>fnd_log.level_statement);
687 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
688 p_prefix =>l_debug_prefix,
689 p_msg_level=>fnd_log.level_statement);
690 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
691 p_prefix =>l_debug_prefix,
692 p_msg_level=>fnd_log.level_statement);
693 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
694 p_prefix =>l_debug_prefix,
695 p_msg_level=>fnd_log.level_statement);
696 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
697 p_prefix =>l_debug_prefix,
698 p_msg_level=>fnd_log.level_statement);
699 END IF;
700 END IF;
701
702 -- J. del Callar: fetch the next set of location information.
703 FETCH cu_loc1a BULK COLLECT
704 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
705 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
706 LIMIT l_set_size;
707
708 -- J. del Callar: exit the loop if we've processed all records
709 IF cu_loc1a%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
710 time_put_line('Exiting because of NOTFOUND condition');
711 time_put_line('Count=' || l_location_ids.COUNT);
712 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
713 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
714 p_prefix=>'WARNING',
715 p_msg_level=>fnd_log.level_exception);
716 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
717 p_prefix=>'WARNING',
718 p_msg_level=>fnd_log.level_exception);
719 END IF;
720 EXIT;
721 END IF;
722 ELSE -- cu_loc1a/cu_loc1ac check p_country is null or not
723 IF NOT cu_loc1ac%ISOPEN THEN
724 OPEN cu_loc1ac (l_request_id);
725 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
726 hz_utility_v2pub.debug(p_message=>'Opening cursor 1ac with args:',
727 p_prefix =>l_debug_prefix,
728 p_msg_level=>fnd_log.level_statement);
729 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
730 p_prefix =>l_debug_prefix,
731 p_msg_level=>fnd_log.level_statement);
732 hz_utility_v2pub.debug(p_message=>'country=' || p_country,
733 p_prefix =>l_debug_prefix,
734 p_msg_level=>fnd_log.level_statement);
735 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
736 p_prefix =>l_debug_prefix,
737 p_msg_level=>fnd_log.level_statement);
738 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
739 p_prefix =>l_debug_prefix,
740 p_msg_level=>fnd_log.level_statement);
741 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
742 p_prefix =>l_debug_prefix,
743 p_msg_level=>fnd_log.level_statement);
744 END IF;
745 END IF;
746
747 -- J. del Callar: fetch the next set of location information.
748 FETCH cu_loc1ac BULK COLLECT
752
749 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
750 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
751 LIMIT l_set_size;
753 -- J. del Callar: exit the loop if we've processed all records
754 IF cu_loc1ac%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
755 time_put_line('Exiting because of NOTFOUND condition');
756 time_put_line('Count=' || l_location_ids.COUNT);
757 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
758 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
759 p_prefix=>'WARNING',
760 p_msg_level=>fnd_log.level_exception);
761 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
762 p_prefix=>'WARNING',
763 p_msg_level=>fnd_log.level_exception);
764 END IF;
765 EXIT;
766 END IF;
767 END IF; -- cu_loc1a/cu_loc1ac check p_country is null or not
768
769 ELSIF p_site_use_type IS NOT NULL AND p_incremental = 'Y' THEN
770 -- J. del Callar: use cu_loc1b for non-null site uses
771 -- and incremental mode.
772 IF(p_country IS NULL) THEN
773 IF NOT cu_loc1b%ISOPEN THEN
774 OPEN cu_loc1b (l_request_id);
775 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
776 hz_utility_v2pub.debug(p_message=>'Opening cursor 1b with args:',
777 p_prefix =>l_debug_prefix,
778 p_msg_level=>fnd_log.level_statement);
779 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
780 p_prefix =>l_debug_prefix,
781 p_msg_level=>fnd_log.level_statement);
782 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
783 p_prefix =>l_debug_prefix,
784 p_msg_level=>fnd_log.level_statement);
785 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
786 p_prefix =>l_debug_prefix,
787 p_msg_level=>fnd_log.level_statement);
788 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
789 p_prefix =>l_debug_prefix,
790 p_msg_level=>fnd_log.level_statement);
791 END IF;
792 END IF;
793
794 -- J. del Callar: fetch the next set of location information.
795 FETCH cu_loc1b BULK COLLECT
796 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
797 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
798 LIMIT l_set_size;
799
800 -- J. del Callar: exit the loop if we've processed all records
801 IF cu_loc1b%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
802 time_put_line('Exiting because of NOTFOUND condition');
803 time_put_line('Count=' || l_location_ids.COUNT);
804 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
805 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
806 p_prefix=>'WARNING',
807 p_msg_level=>fnd_log.level_exception);
808 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
809 p_prefix=>'WARNING',
810 p_msg_level=>fnd_log.level_exception);
811 END IF;
812 EXIT;
813 END IF;
814 ELSE -- cu_loc1b/cu_loc1bc, check if p_country is null or not
815 IF NOT cu_loc1bc%ISOPEN THEN
816 OPEN cu_loc1bc (l_request_id);
817 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
818 hz_utility_v2pub.debug(p_message=>'Opening cursor 1bc with args:',
819 p_prefix =>l_debug_prefix,
820 p_msg_level=>fnd_log.level_statement);
821 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
822 p_prefix =>l_debug_prefix,
823 p_msg_level=>fnd_log.level_statement);
824 hz_utility_v2pub.debug(p_message=>'country=' || p_country,
825 p_prefix =>l_debug_prefix,
826 p_msg_level=>fnd_log.level_statement);
827 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
828 p_prefix =>l_debug_prefix,
829 p_msg_level=>fnd_log.level_statement);
830 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
831 p_prefix =>l_debug_prefix,
832 p_msg_level=>fnd_log.level_statement);
833 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
834 p_prefix =>l_debug_prefix,
835 p_msg_level=>fnd_log.level_statement);
836 END IF;
837 END IF;
838
839 -- J. del Callar: fetch the next set of location information.
843 LIMIT l_set_size;
840 FETCH cu_loc1bc BULK COLLECT
841 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
842 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
844
845 -- J. del Callar: exit the loop if we've processed all records
846 IF cu_loc1bc%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
847 time_put_line('Exiting because of NOTFOUND condition');
848 time_put_line('Count=' || l_location_ids.COUNT);
849 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
850 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
851 p_prefix=>'WARNING',
852 p_msg_level=>fnd_log.level_exception);
853 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
854 p_prefix=>'WARNING',
855 p_msg_level=>fnd_log.level_exception);
856 END IF;
857 EXIT;
858 END IF;
859 END IF; -- cu_loc1b/cu_loc1bc, check if p_country is null or not
860
861 ELSIF p_site_use_type IS NULL AND p_incremental = 'N' THEN
862 -- J. del Callar: use cu_loc2a for null site uses
863 -- and non-incremental mode.
864 IF(p_country IS NULL) THEN
865 IF NOT cu_loc2a%ISOPEN THEN
866 OPEN cu_loc2a (l_request_id);
867 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
868 hz_utility_v2pub.debug(p_message=>'Opening cursor 2a with args:',
869 p_prefix =>l_debug_prefix,
870 p_msg_level=>fnd_log.level_statement);
871 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
872 p_prefix =>l_debug_prefix,
873 p_msg_level=>fnd_log.level_statement);
874 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
875 p_prefix =>l_debug_prefix,
876 p_msg_level=>fnd_log.level_statement);
877 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
878 p_prefix =>l_debug_prefix,
879 p_msg_level=>fnd_log.level_statement);
880 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
881 p_prefix =>l_debug_prefix,
882 p_msg_level=>fnd_log.level_statement);
883 END IF;
884 END IF;
885
886 -- J. del Callar: fetch the next set of location information.
887 FETCH cu_loc2a BULK COLLECT
888 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
889 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
890 LIMIT l_set_size;
891
892 -- J. del Callar: exit the loop if we've processed all records
893 IF cu_loc2a%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
894 time_put_line('Exiting because of NOTFOUND condition');
895 time_put_line('Count=' || l_location_ids.COUNT);
896 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
897 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
898 p_prefix=>'WARNING',
899 p_msg_level=>fnd_log.level_exception);
900 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
901 p_prefix=>'WARNING',
902 p_msg_level=>fnd_log.level_exception);
903 END IF;
904 EXIT;
905 END IF;
906 ELSE -- cu_loc2a/cu_loc2ac, check if p_country is null or not
907 IF NOT cu_loc2ac%ISOPEN THEN
908 OPEN cu_loc2ac (l_request_id);
909 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
910 hz_utility_v2pub.debug(p_message=>'Opening cursor 2ac with args:',
911 p_prefix =>l_debug_prefix,
912 p_msg_level=>fnd_log.level_statement);
913 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
914 p_prefix =>l_debug_prefix,
915 p_msg_level=>fnd_log.level_statement);
916 hz_utility_v2pub.debug(p_message=>'country=' || p_country,
917 p_prefix =>l_debug_prefix,
918 p_msg_level=>fnd_log.level_statement);
919 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
920 p_prefix =>l_debug_prefix,
921 p_msg_level=>fnd_log.level_statement);
922 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
923 p_prefix =>l_debug_prefix,
924 p_msg_level=>fnd_log.level_statement);
925 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
926 p_prefix =>l_debug_prefix,
927 p_msg_level=>fnd_log.level_statement);
928 END IF;
929 END IF;
930
934 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
931 -- J. del Callar: fetch the next set of location information.
932 FETCH cu_loc2ac BULK COLLECT
933 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
935 LIMIT l_set_size;
936
937 -- J. del Callar: exit the loop if we've processed all records
938 IF cu_loc2ac%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
939 time_put_line('Exiting because of NOTFOUND condition');
940 time_put_line('Count=' || l_location_ids.COUNT);
941 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
942 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
943 p_prefix=>'WARNING',
944 p_msg_level=>fnd_log.level_exception);
945 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
946 p_prefix=>'WARNING',
947 p_msg_level=>fnd_log.level_exception);
948 END IF;
949 EXIT;
950 END IF;
951 END IF; -- cu_loc2a/cu_loc2ac, check if p_country is null or not
952
953 ELSIF p_site_use_type IS NULL AND p_incremental = 'Y' THEN
954 -- J. del Callar use cu_loc2b for null site uses
955 -- and incremental mode.
956 IF(p_country IS NULL) THEN
957 IF NOT cu_loc2b%ISOPEN THEN
958 OPEN cu_loc2b (l_request_id);
959 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
960 hz_utility_v2pub.debug(p_message=>'Opening cursor 2b with args:',
961 p_prefix =>l_debug_prefix,
962 p_msg_level=>fnd_log.level_statement);
963 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
964 p_prefix =>l_debug_prefix,
965 p_msg_level=>fnd_log.level_statement);
966 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
967 p_prefix =>l_debug_prefix,
968 p_msg_level=>fnd_log.level_statement);
969 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
970 p_prefix =>l_debug_prefix,
971 p_msg_level=>fnd_log.level_statement);
972 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
973 p_prefix =>l_debug_prefix,
974 p_msg_level=>fnd_log.level_statement);
975 END IF;
976 END IF;
977
978 -- J. del Callar: fetch the next set of location information.
979 FETCH cu_loc2b BULK COLLECT
980 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
981 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
982 LIMIT l_set_size;
983
984 -- J. del Callar: exit the loop if we've processed all records
985 IF cu_loc2b%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
986 time_put_line('Exiting because of NOTFOUND condition');
987 time_put_line('Count=' || l_location_ids.COUNT);
988 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
989 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
990 p_prefix=>'WARNING',
991 p_msg_level=>fnd_log.level_exception);
992 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
993 p_prefix=>'WARNING',
994 p_msg_level=>fnd_log.level_exception);
995 END IF;
996 EXIT;
997 END IF;
998 ELSE -- cu_loc2b/cu_loc2bc, check if p_country is null or not
999 IF NOT cu_loc2bc%ISOPEN THEN
1000 OPEN cu_loc2bc (l_request_id);
1001 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1002 hz_utility_v2pub.debug(p_message=>'Opening cursor 2bc with args:',
1003 p_prefix =>l_debug_prefix,
1004 p_msg_level=>fnd_log.level_statement);
1005 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1006 p_prefix =>l_debug_prefix,
1007 p_msg_level=>fnd_log.level_statement);
1008 hz_utility_v2pub.debug(p_message=>'country=' || p_country,
1009 p_prefix =>l_debug_prefix,
1010 p_msg_level=>fnd_log.level_statement);
1011 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1012 p_prefix =>l_debug_prefix,
1013 p_msg_level=>fnd_log.level_statement);
1014 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1015 p_prefix =>l_debug_prefix,
1016 p_msg_level=>fnd_log.level_statement);
1017 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1018 p_prefix =>l_debug_prefix,
1022
1019 p_msg_level=>fnd_log.level_statement);
1020 END IF;
1021 END IF;
1023 -- J. del Callar: fetch the next set of location information.
1024 FETCH cu_loc2bc BULK COLLECT
1025 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
1026 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
1027 LIMIT l_set_size;
1028
1029 -- J. del Callar: exit the loop if we've processed all records
1030 IF cu_loc2bc%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
1031 time_put_line('Exiting because of NOTFOUND condition');
1032 time_put_line('Count=' || l_location_ids.COUNT);
1033 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1034 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1035 p_prefix=>'WARNING',
1036 p_msg_level=>fnd_log.level_exception);
1037 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1038 p_prefix=>'WARNING',
1039 p_msg_level=>fnd_log.level_exception);
1040 END IF;
1041 EXIT;
1042 END IF;
1043 END IF; -- cu_loc2b/cu_loc2bc, check if p_country is null or not
1044 END IF;
1045
1046 -- swbhatna: If p_loc_type = 'S', then open cursor
1047 -- cu_loc3a or cu_loc3b depending on p_incremental value
1048 ELSIF p_loc_type = 'S' THEN
1049
1050 IF p_incremental = 'N' THEN
1051 -- swbhatna: use cu_loc3a for updating standalone locations
1052 -- and in non-incremental mode.
1053
1054 IF(p_country IS NULL) THEN
1055 IF NOT cu_loc3a%ISOPEN THEN
1056 OPEN cu_loc3a (l_request_id);
1057 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1058 hz_utility_v2pub.debug(p_message=>'Opening cursor 3a with args:',
1059 p_prefix =>l_debug_prefix,
1060 p_msg_level=>fnd_log.level_statement);
1061 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1062 p_prefix =>l_debug_prefix,
1063 p_msg_level=>fnd_log.level_statement);
1064 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1065 p_prefix =>l_debug_prefix,
1066 p_msg_level=>fnd_log.level_statement);
1067 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1068 p_prefix =>l_debug_prefix,
1069 p_msg_level=>fnd_log.level_statement);
1070 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1071 p_prefix =>l_debug_prefix,
1072 p_msg_level=>fnd_log.level_statement);
1073 hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1074 p_prefix =>l_debug_prefix,
1075 p_msg_level=>fnd_log.level_statement);
1076 END IF;
1077 END IF;
1078
1079 -- swbhatna: fetch the next set of location information.
1080 FETCH cu_loc3a BULK COLLECT
1081 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
1082 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
1083 LIMIT l_set_size;
1084
1085 -- swbhatna: exit the loop if we've processed all records
1086 IF cu_loc3a%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
1087 time_put_line('Exiting because of NOTFOUND condition');
1088 time_put_line('Count=' || l_location_ids.COUNT);
1089 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1090 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1091 p_prefix=>'WARNING',
1092 p_msg_level=>fnd_log.level_exception);
1093 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1094 p_prefix=>'WARNING',
1095 p_msg_level=>fnd_log.level_exception);
1096 END IF;
1097 EXIT;
1098 END IF;
1099 ELSE -- cu_loc3a/cu_loc3ac, check if p_country is null or not
1100 IF NOT cu_loc3ac%ISOPEN THEN
1101 OPEN cu_loc3ac (l_request_id);
1102 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1103 hz_utility_v2pub.debug(p_message=>'Opening cursor 3ac with args:',
1104 p_prefix =>l_debug_prefix,
1105 p_msg_level=>fnd_log.level_statement);
1106 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1107 p_prefix =>l_debug_prefix,
1108 p_msg_level=>fnd_log.level_statement);
1109 hz_utility_v2pub.debug(p_message=>'country=' || p_country,
1110 p_prefix =>l_debug_prefix,
1111 p_msg_level=>fnd_log.level_statement);
1112 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1116 p_prefix =>l_debug_prefix,
1113 p_prefix =>l_debug_prefix,
1114 p_msg_level=>fnd_log.level_statement);
1115 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1117 p_msg_level=>fnd_log.level_statement);
1118 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1119 p_prefix =>l_debug_prefix,
1120 p_msg_level=>fnd_log.level_statement);
1121 hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1122 p_prefix =>l_debug_prefix,
1123 p_msg_level=>fnd_log.level_statement);
1124 END IF;
1125 END IF;
1126
1127 -- swbhatna: fetch the next set of location information.
1128 FETCH cu_loc3ac BULK COLLECT
1129 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
1130 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
1131 LIMIT l_set_size;
1132
1133 -- swbhatna: exit the loop if we've processed all records
1134 IF cu_loc3ac%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
1135 time_put_line('Exiting because of NOTFOUND condition');
1136 time_put_line('Count=' || l_location_ids.COUNT);
1137 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1138 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1139 p_prefix=>'WARNING',
1140 p_msg_level=>fnd_log.level_exception);
1141 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1142 p_prefix=>'WARNING',
1143 p_msg_level=>fnd_log.level_exception);
1144 END IF;
1145 END IF;
1146 END IF; -- cu_loc3a/cu_loc3ac, check if p_country is null or not
1147
1148 ELSIF p_incremental = 'Y' THEN
1149 -- swbhatna: use cu_loc3b for updating standalone locations
1150 -- and in incremental mode.
1151
1152 IF(p_country IS NULL) THEN
1153 IF NOT cu_loc3b%ISOPEN THEN
1154 OPEN cu_loc3b (l_request_id);
1155 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1156 hz_utility_v2pub.debug(p_message=>'Opening cursor 3b with args:',
1157 p_prefix =>l_debug_prefix,
1158 p_msg_level=>fnd_log.level_statement);
1159 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1160 p_prefix =>l_debug_prefix,
1161 p_msg_level=>fnd_log.level_statement);
1162 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1163 p_prefix =>l_debug_prefix,
1164 p_msg_level=>fnd_log.level_statement);
1165 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1166 p_prefix =>l_debug_prefix,
1167 p_msg_level=>fnd_log.level_statement);
1168 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1169 p_prefix =>l_debug_prefix,
1170 p_msg_level=>fnd_log.level_statement);
1171 hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1172 p_prefix =>l_debug_prefix,
1173 p_msg_level=>fnd_log.level_statement);
1174 END IF;
1175 END IF;
1176
1177 -- swbhatna: fetch the next set of location information.
1178 FETCH cu_loc3b BULK COLLECT
1179 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
1180 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
1181 LIMIT l_set_size;
1182
1183 -- swbhatna: exit the loop if we've processed all records
1184 IF cu_loc3b%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
1185 time_put_line('Exiting because of NOTFOUND condition');
1186 time_put_line('Count=' || l_location_ids.COUNT);
1187 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1188 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1189 p_prefix=>'WARNING',
1190 p_msg_level=>fnd_log.level_exception);
1191 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1192 p_prefix=>'WARNING',
1193 p_msg_level=>fnd_log.level_exception);
1194 END IF;
1195 EXIT;
1196 END IF;
1197 ELSE -- cu_loc3b/cu_loc3bc, check if p_country is null or not
1198 IF NOT cu_loc3bc%ISOPEN THEN
1199 OPEN cu_loc3bc (l_request_id);
1200 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1201 hz_utility_v2pub.debug(p_message=>'Opening cursor 3bc with args:',
1202 p_prefix =>l_debug_prefix,
1203 p_msg_level=>fnd_log.level_statement);
1207 hz_utility_v2pub.debug(p_message=>'country=' || p_country,
1204 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1205 p_prefix =>l_debug_prefix,
1206 p_msg_level=>fnd_log.level_statement);
1208 p_prefix =>l_debug_prefix,
1209 p_msg_level=>fnd_log.level_statement);
1210 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1211 p_prefix =>l_debug_prefix,
1212 p_msg_level=>fnd_log.level_statement);
1213 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1214 p_prefix =>l_debug_prefix,
1215 p_msg_level=>fnd_log.level_statement);
1216 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1217 p_prefix =>l_debug_prefix,
1218 p_msg_level=>fnd_log.level_statement);
1219 hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1220 p_prefix =>l_debug_prefix,
1221 p_msg_level=>fnd_log.level_statement);
1222 END IF;
1223 END IF;
1224
1225 -- swbhatna: fetch the next set of location information.
1226 FETCH cu_loc3bc BULK COLLECT
1227 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
1228 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
1229 LIMIT l_set_size;
1230
1231 -- swbhatna: exit the loop if we've processed all records
1232 IF cu_loc3bc%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
1233 time_put_line('Exiting because of NOTFOUND condition');
1234 time_put_line('Count=' || l_location_ids.COUNT);
1235 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1236 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1237 p_prefix=>'WARNING',
1238 p_msg_level=>fnd_log.level_exception);
1239 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1240 p_prefix=>'WARNING',
1241 p_msg_level=>fnd_log.level_exception);
1242 END IF;
1243 END IF;
1244 END IF; -- cu_loc3b/cu_loc3bc, check if p_country is null or not
1245 END IF;
1246
1247 ELSE
1248 l_return_status := fnd_api.g_ret_sts_unexp_error;
1249 time_put_line('Unexpected mode encountered');
1250 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1251 hz_utility_v2pub.debug(p_message=>'Unexpected mode encountered',
1252 p_prefix=>'ERROR',
1253 p_msg_level=>fnd_log.level_error);
1254 END IF;
1255 END IF;
1256 -- J. del Callar: exit the loop if no records were fetched the first time
1257 IF l_location_ids.COUNT = 0 THEN
1258 time_put_line('Exiting because COUNT=0');
1259 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1260 hz_utility_v2pub.debug(p_message=>'Exiting because COUNT=0',
1261 p_prefix=>'WARNING',
1262 p_msg_level=>fnd_log.level_exception);
1263 END IF;
1264 EXIT;
1265 END IF;
1266
1267 -- J. del Callar: exit the loop if our update limit has been exceeded.
1268 IF p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update THEN
1269 time_put_line('Exiting because partial=' || p_all_partial);
1270 time_put_line('cpt_update=' || cpt_update);
1271 time_put_line('nb_row_update=' || l_nb_row_update);
1272 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1273 hz_utility_v2pub.debug(p_message=>'Exiting because partial=' || p_all_partial,
1274 p_prefix=>'WARNING',
1275 p_msg_level=>fnd_log.level_exception);
1276 hz_utility_v2pub.debug(p_message=>'cpt_update=' || cpt_update,
1277 p_prefix=>'WARNING',
1278 p_msg_level=>fnd_log.level_exception);
1279 hz_utility_v2pub.debug(p_message=>'nb_row_update=' || l_nb_row_update,
1280 p_prefix=>'WARNING',
1281 p_msg_level=>fnd_log.level_exception);
1282 END IF;
1283 EXIT;
1284 END IF;
1285
1286 -- J. del Callar: main update loop: process up to l_set_size records.
1287 FOR i IN l_location_ids.first..l_location_ids.last LOOP
1288 cpt_update := cpt_update + 1;
1289
1290 -- J. del Callar: copy the cursor values into a new location array rec
1291 l_array.EXTEND;
1292 cpt := cpt + 1;
1293 l_array(cpt).location_id := l_location_ids(i);
1294 l_array(cpt).address1 := l_address1s(i);
1295 l_array(cpt).address2 := l_address2s(i);
1296 l_array(cpt).address3 := l_address3s(i);
1297 l_array(cpt).address4 := l_address4s(i);
1298 l_array(cpt).city := l_cities(i);
1299 l_array(cpt).postal_code := l_postal_codes(i);
1300 l_array(cpt).state := l_states(i);
1301 l_array(cpt).country := l_countries(i);
1302 l_array(cpt).province := l_provinces(i);
1303 l_array(cpt).county := l_counties(i);
1304
1305 fnd_file.put_line(fnd_file.log,
1309 p_prefix =>l_debug_prefix,
1306 'Processing location '||l_array(cpt).location_id);
1307 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1308 hz_utility_v2pub.debug(p_message=>'Processing location ' || l_array(cpt).location_id,
1310 p_msg_level=>fnd_log.level_statement);
1311 END IF;
1312
1313 -- execute the synchronization routine every l_batch_size, or
1314 -- if we have reached the last record to be updated, or
1315 -- if we have exceeded our update limit.
1316 IF cpt >= l_batch_size
1317 OR i = l_location_ids.last
1318 OR (p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update)
1319 THEN
1320 -- Process the records in the array.
1321 hz_geocode_pkg.get_spatial_coords(
1322 p_loc_array => l_array,
1323 p_name => NULL,
1324 p_http_ad => l_http_ad,
1325 p_proxy => l_proxy,
1326 p_port => l_port,
1327 p_retry => l_nb_retries,
1328 x_return_status => l_return_status,
1329 x_msg_count => x_msg_count,
1330 x_msg_data => x_msg_data
1331 );
1332
1333 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1334 time_put_line('Unexpected error encountered');
1335 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1336 hz_utility_v2pub.debug(p_message=>'Unexpected error encountered',
1337 p_prefix=>'ERROR',
1338 p_msg_level=>fnd_log.level_error);
1339 END IF;
1340
1341 -- Close the open cursor (depends on the site use type and the
1342 -- update mode).
1343 IF p_loc_type = 'P' THEN
1344 IF p_site_use_type IS NOT NULL AND p_incremental = 'N' THEN
1345 -- J. del Callar: use cu_loc1a for non-null site uses
1346 -- and non-incremental mode.
1347 IF (p_country IS NULL) THEN
1348 CLOSE cu_loc1a;
1349 ELSE
1350 CLOSE cu_loc1ac;
1351 END IF;
1352 ELSIF p_site_use_type IS NOT NULL AND p_incremental = 'Y' THEN
1353 -- J. del Callar: use cu_loc1b for non-null site uses
1354 -- and incremental mode.
1355 IF (p_country IS NULL) THEN
1356 CLOSE cu_loc1b;
1357 ELSE
1358 CLOSE cu_loc1bc;
1359 END IF;
1360 ELSIF p_site_use_type IS NULL AND p_incremental = 'N' THEN
1361 -- J. del Callar: use cu_loc2a for null site uses
1362 -- and non-incremental mode.
1363 IF (p_country IS NULL) THEN
1364 CLOSE cu_loc2a;
1365 ELSE
1366 CLOSE cu_loc2ac;
1367 END IF;
1368 ELSIF p_site_use_type IS NULL AND p_incremental = 'Y' THEN
1369 -- J. del Callar use cu_loc2b for null site uses
1370 -- and incremental mode.
1371 IF (p_country IS NULL) THEN
1372 CLOSE cu_loc2b;
1373 ELSE
1374 CLOSE cu_loc2bc;
1375 END IF;
1376 END IF;
1377 ELSIF p_loc_type = 'S' THEN
1378 IF p_incremental = 'N' THEN
1379 -- swbhatna: use cu_loc3a for updating Standalone locations
1380 -- and in non-incremental mode.
1381 IF (p_country IS NULL) THEN
1382 CLOSE cu_loc3a;
1383 ELSE
1384 CLOSE cu_loc3ac;
1385 END IF;
1386 ELSIF p_incremental = 'Y' THEN
1387 -- swbhatna: use cu_loc3b for updating Standalone locations
1388 -- and in incremental mode.
1389 IF (p_country IS NULL) THEN
1390 CLOSE cu_loc3b;
1391 ELSE
1392 CLOSE cu_loc3bc;
1393 END IF;
1394 END IF;
1395 END IF;
1396
1397 status_handler(l_return_status, x_return_status);
1398
1399 l_array.DELETE;
1400 RAISE exchttp;
1401 END IF;
1402
1403 IF l_return_status = fnd_api.g_ret_sts_error THEN
1404 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1405 msg := SUBSTRB(fnd_msg_pub.get(j, fnd_api.g_false),1,256);
1406 trace_handler(msg);
1407 END LOOP;
1408 -- J. del Callar: Re-initialize the stack for the next set.
1409 fnd_msg_pub.initialize;
1410 status_handler(l_return_status, x_return_status);
1411 END IF;
1412
1413 FOR j IN 1..l_array.COUNT LOOP
1414 -- J. del Callar: update the geometry and status of each of the
1415 -- location records.
1416 update_geo_location(
1417 p_location_id => l_array(j).location_id,
1418 p_geo => l_array(j).geometry,
1419 p_geo_status => l_array(j).geometry_status_code,
1420 x_count => l_nb_update,
1424 );
1421 x_return_status => l_return_status,
1422 x_msg_count => x_msg_count,
1423 x_msg_data => x_msg_data
1425 END LOOP;
1426
1427 status_handler(l_return_status, x_return_status);
1428 l_array.DELETE;
1429 cpt := 0;
1430 END IF;
1431
1432 -- J. del Callar: exit the loop if our update limit has been exceeded.
1433 IF p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update THEN
1434 time_put_line('Exiting due to update limit.');
1435 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1436 hz_utility_v2pub.debug(p_message=>'Exiting due to update limit.',
1437 p_prefix=>'WARNING',
1438 p_msg_level=>fnd_log.level_exception);
1439
1440 END IF;
1441 EXIT;
1442 END IF;
1443 END LOOP;
1444
1445 -- J. del Callar: commit every l_set_size records, and clear all arrays.
1446 COMMIT;
1447
1448 -- Fix bug 3612034, close cursor and then reopen it again due to
1449 -- snapshot too old problem
1450 fnd_file.put_line(fnd_file.log,'Process committed');
1451
1452 IF p_loc_type = 'P' THEN
1453 IF p_site_use_type IS NOT NULL AND p_incremental = 'N' THEN
1454 IF (p_country IS NULL) THEN
1455 CLOSE cu_loc1a;
1456 OPEN cu_loc1a(l_request_id);
1457 ELSE
1458 CLOSE cu_loc1ac;
1459 OPEN cu_loc1ac(l_request_id);
1460 END IF;
1461 ELSIF p_site_use_type IS NOT NULL AND p_incremental = 'Y' THEN
1462 IF (p_country IS NULL) THEN
1463 CLOSE cu_loc1b;
1464 OPEN cu_loc1b(l_request_id);
1465 ELSE
1466 CLOSE cu_loc1bc;
1467 OPEN cu_loc1bc(l_request_id);
1468 END IF;
1469 ELSIF p_site_use_type IS NULL AND p_incremental = 'N' THEN
1470 IF (p_country IS NULL) THEN
1471 CLOSE cu_loc2a;
1472 OPEN cu_loc2a(l_request_id);
1473 ELSE
1474 CLOSE cu_loc2ac;
1475 OPEN cu_loc2ac(l_request_id);
1476 END IF;
1477 ELSIF p_site_use_type IS NULL AND p_incremental = 'Y' THEN
1478 IF (p_country IS NULL) THEN
1479 CLOSE cu_loc2b;
1480 OPEN cu_loc2b(l_request_id);
1481 ELSE
1482 CLOSE cu_loc2bc;
1483 OPEN cu_loc2bc(l_request_id);
1484 END IF;
1485 END IF;
1486 ELSIF p_loc_type = 'S' THEN
1487 IF p_incremental = 'N' THEN
1488 IF (p_country IS NULL) THEN
1489 CLOSE cu_loc3a;
1490 OPEN cu_loc3a(l_request_id);
1491 ELSE
1492 CLOSE cu_loc3ac;
1493 OPEN cu_loc3ac(l_request_id);
1494 END IF;
1495 ELSIF p_incremental = 'Y' THEN
1496 IF (p_country IS NULL) THEN
1497 CLOSE cu_loc3b;
1498 OPEN cu_loc3b(l_request_id);
1499 ELSE
1500 CLOSE cu_loc3bc;
1501 OPEN cu_loc3bc(l_request_id);
1502 END IF;
1503 END IF;
1504 END IF;
1505
1506 l_location_ids.DELETE;
1507 l_address1s.DELETE;
1508 l_address2s.DELETE;
1509 l_address3s.DELETE;
1510 l_address4s.DELETE;
1511 l_cities.DELETE;
1512 l_postal_codes.DELETE;
1513 l_counties.DELETE;
1514 l_states.DELETE;
1515 l_provinces.DELETE;
1516 l_countries.DELETE;
1517 END LOOP;
1518
1519 -- J. del Callar: close the cursor if it has not been closed by the
1520 -- commit statement.
1521 IF p_loc_type = 'P' THEN
1522 IF p_site_use_type IS NOT NULL AND p_incremental = 'N' THEN
1523 -- J. del Callar: use cu_loc1a for non-null site uses
1524 -- and non-incremental mode.
1525 IF(p_country IS NULL) THEN
1526 IF cu_loc1a%ISOPEN THEN
1527 CLOSE cu_loc1a;
1528 END IF;
1529 ELSE
1530 IF cu_loc1ac%ISOPEN THEN
1531 CLOSE cu_loc1ac;
1532 END IF;
1533 END IF;
1534 ELSIF p_site_use_type IS NOT NULL AND p_incremental = 'Y' THEN
1535 -- J. del Callar: use cu_loc1b for non-null site uses
1536 -- and incremental mode.
1537 IF(p_country IS NULL) THEN
1538 IF cu_loc1b%ISOPEN THEN
1539 CLOSE cu_loc1b;
1540 END IF;
1541 ELSE
1542 IF cu_loc1bc%ISOPEN THEN
1543 CLOSE cu_loc1bc;
1544 END IF;
1545 END IF;
1546 ELSIF p_site_use_type IS NULL AND p_incremental = 'N' THEN
1547 -- J. del Callar: use cu_loc2a for null site uses
1548 -- and non-incremental mode.
1549 IF(p_country IS NULL) THEN
1550 IF cu_loc2a%ISOPEN THEN
1551 CLOSE cu_loc2a;
1552 END IF;
1553 ELSE
1554 IF cu_loc2ac%ISOPEN THEN
1555 CLOSE cu_loc2ac;
1556 END IF;
1557 END IF;
1558 ELSIF p_site_use_type IS NULL AND p_incremental = 'Y' THEN
1559 -- J. del Callar use cu_loc2b for null site uses
1560 -- and incremental mode.
1561 IF(p_country IS NULL) THEN
1562 IF cu_loc2b%ISOPEN THEN
1563 CLOSE cu_loc2b;
1567 CLOSE cu_loc2bc;
1564 END IF;
1565 ELSE
1566 IF cu_loc2bc%ISOPEN THEN
1568 END IF;
1569 END IF;
1570 END IF;
1571 ELSIF p_loc_type = 'S' THEN
1572 IF p_incremental = 'N' THEN
1573 -- swbhatna: use cu_loc3a for updating Standalone locations
1574 -- and in non-incremental mode.
1575 IF(p_country IS NULL) THEN
1576 IF cu_loc3a%ISOPEN THEN
1577 CLOSE cu_loc3a;
1578 END IF;
1579 ELSE
1580 IF cu_loc3ac%ISOPEN THEN
1581 CLOSE cu_loc3ac;
1582 END IF;
1583 END IF;
1584 ELSIF p_incremental = 'Y' THEN
1585 -- swbhatna: use cu_loc3b for updating Standalone locations
1586 -- and in incremental mode.
1587 IF(p_country IS NULL) THEN
1588 IF cu_loc3b%ISOPEN THEN
1589 CLOSE cu_loc3b;
1590 END IF;
1591 ELSE
1592 IF cu_loc3bc%ISOPEN THEN
1593 CLOSE cu_loc3bc;
1594 END IF;
1595 END IF;
1596 END IF;
1597 END IF;
1598
1599 -- J. del Callar, bug 2252141: changed to always print out NOCOPY the message
1600 -- stack.
1601 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1602 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256)
1603 || fnd_global.local_chr(10);
1604 trace_handler(msg);
1605 END LOOP;
1606 fnd_message.clear;
1607
1608 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1609 retcode := '1';
1610 -- J. del Callar: instruct user to look at log if warnings are found.
1611 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1612 fnd_file.put_line(fnd_file.output, errbuf);
1613 END IF;
1614
1615 -- J. del Callar: reflect successful program termination in output and
1616 -- log files.
1617 fnd_file.put_line(fnd_file.output,
1618 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS: '));
1619 fnd_file.put_line(fnd_file.output,
1620 fnd_message.get_string('FND',
1621 'CONC-CP SUCCESSFUL TERMINATION'));
1622 fnd_file.put_line(fnd_file.log,
1623 fnd_message.get_string('FND',
1624 'CONC-CP SUCCESSFUL TERMINATION'));
1625
1626 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1627 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (-)',
1628 p_prefix=>l_debug_prefix,
1629 p_msg_level=>fnd_log.level_procedure);
1630 END IF;
1631
1632 --disable_debug;
1633
1634 EXCEPTION
1635 WHEN expect_http_ad THEN
1636 fnd_message.set_name('AR','HZ_MISSING_HTTP_SITE');
1637 fnd_msg_pub.add;
1638 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1639 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
1640 fnd_global.local_chr(10);
1641 trace_handler(msg);
1642 END LOOP;
1643 fnd_message.clear;
1644 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1645 hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry (- expect_http_ad)',
1646 p_prefix=>'ERROR',
1647 p_msg_level=>fnd_log.level_error);
1648 END IF;
1649 --disable_debug;
1650 retcode := '2';
1651 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1652
1653 WHEN port_number THEN
1654 fnd_message.set_name('AR','HZ_PORT_NUMBER_EXPECTED');
1655 fnd_message.set_token('PORT', l_port);
1656 fnd_msg_pub.add;
1657 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1658 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
1659 fnd_global.local_chr(10);
1660 trace_handler(msg);
1661 END LOOP;
1662 fnd_message.clear;
1663 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1664 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- port_number)',
1665 p_prefix=>'ERROR',
1666 p_msg_level=>fnd_log.level_error);
1667 END IF;
1668 --disable_debug;
1669 retcode := '2';
1670 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1671
1672 WHEN nlsnumexp THEN
1673 fnd_message.set_name('AR','HZ_NUMERIC_CHAR_SET');
1674 fnd_msg_pub.add;
1675 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1676 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
1677 fnd_global.local_chr(10);
1678 trace_handler(msg);
1679 END LOOP;
1680 fnd_message.clear;
1681 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1682 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- nlsnumexp)',
1683 p_prefix=>'ERROR',
1684 p_msg_level=>fnd_log.level_error);
1685 END IF;
1686 --disable_debug;
1687 retcode := '2';
1688 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1689
1690 WHEN atleastonerow THEN
1691 fnd_message.set_name('AR','HZ_AT_LEAST_ONE_ROW');
1692 fnd_msg_pub.add;
1693 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1694 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
1695 fnd_global.local_chr(10);
1696 trace_handler(msg);
1697 END LOOP;
1701 p_prefix=>'ERROR',
1698 fnd_message.clear;
1699 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1700 hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry (- atleastonerow)',
1702 p_msg_level=>fnd_log.level_error);
1703 END IF;
1704 --disable_debug;
1705 retcode := '2';
1706 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1707
1708 WHEN morethanmaxrow THEN
1709 fnd_message.set_name('AR','HZ_MAX_BATCH_SIZE_EXCEEDED');
1710 fnd_message.set_token('MAX', hz_geocode_pkg.g_max_rows);
1711 fnd_msg_pub.add;
1712 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1713 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
1714 fnd_global.local_chr(10);
1715 trace_handler(msg);
1716 END LOOP;
1717 fnd_message.clear;
1718 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1719 hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry (- morethanmaxrow)',
1720 p_prefix=>'ERROR',
1721 p_msg_level=>fnd_log.level_error);
1722 END IF;
1723 --disable_debug;
1724 retcode := '2';
1725 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1726
1727 WHEN exchttp THEN
1728 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1729 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
1730 fnd_global.local_chr(10);
1731 trace_handler(msg);
1732 END LOOP;
1733 fnd_message.clear;
1734 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1735 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- exchttp)',
1736 p_prefix=>'ERROR',
1737 p_msg_level=>fnd_log.level_error);
1738 END IF;
1739 --disable_debug;
1740 retcode := '2';
1741 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1742
1743 WHEN OTHERS THEN
1744 fnd_file.put_line(fnd_file.log, SQLERRM);
1745 msg := SQLERRM;
1746 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1747 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- others)',
1748 p_prefix=>'SQL ERROR',
1749 p_msg_level=>fnd_log.level_error);
1750 hz_utility_v2pub.debug(p_message=>'msg='||SUBSTRB(msg, 1, 250),
1751 p_prefix=>'SQL ERROR',
1752 p_msg_level=>fnd_log.level_error);
1753 END IF;
1754 --disable_debug;
1755 retcode := '2';
1756 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1757 END update_geometry;
1758
1759 --------------------------------------
1760 -- PUBLIC PROCEDURE rebuild_location_index
1761 -- DESCRIPTION
1762 -- Rebuilds the spatial index on HZ_LOCATIONS.GEOMETRY. Rebuilding the
1763 -- spatial index is required so that the index performs adequately and
1764 -- that queries can accurately extract the spatial data.
1765 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1766 -- hz_utility_v2pub
1767 -- fnd_message
1768 -- fnd_msg_pub
1769 -- ARGUMENTS
1770 -- IN:
1771 -- p_concurrent_mode Set to 'Y' if the rebuild is running
1772 -- as its own concurrent program. Set to
1773 -- 'N' to indicate that the rebuild was
1774 -- called from another PL/SQL program.
1775 -- OUT:
1776 -- errbuf Standard AOL concurrent program error
1777 -- buffer.
1778 -- retcode Standard AOL concurrent program return
1779 -- code. If the rebuild is not being run
1780 -- independently, the calling program
1781 -- should check the value of this return.
1782 -- MODIFICATION HISTORY
1783 -- 03-20-2002 J. del Callar Created.
1784 -- 24-SEP-02 P.Suresh Bug No : 2685781. Added code to
1785 -- recreate the spatial index.
1786 -- 15-SEP-04 Arnold Ng Bug No : 3872778. Put enable policy
1787 -- function at exception block.
1788 -- 11-NOV-2008 Nishant Singhai Bug 7262437 : changed insert statement
1789 -- from view user_sdo_geom_metadata to
1790 -- table MDSYS.SDO_GEOM_METADATA_TABLE
1791 -- in insert proper table owner for HZ_LOCATIONS
1792 -- when setup data is created from conc. program.
1793 --------------------------------------
1794 PROCEDURE rebuild_location_index (
1795 errbuf OUT NOCOPY VARCHAR2,
1796 retcode OUT NOCOPY VARCHAR2,
1797 p_concurrent_mode IN VARCHAR2 DEFAULT 'Y'
1798 ) IS
1799 l_rebuild_string VARCHAR2(100) := 'ALTER INDEX ' || g_index_owner ||
1800 '.' || g_index_name || ' REBUILD ' ||
1804 SELECT status, domidx_opstatus
1801 'PARAMETERS(''sdo_commit_interval=' ||
1802 g_commit_interval || ''')';
1803 CURSOR c_index (x_index_owner IN VARCHAR2) IS
1805 FROM sys.all_indexes
1806 WHERE index_name = 'HZ_LOCATIONS_N15' and owner = x_index_owner;
1807 /* Fix perf bug 4956727
1808 CURSOR c_policies IS
1809 SELECT object_owner, object_name, policy_name
1810 FROM sys.all_policies
1811 WHERE enable LIKE 'Y%'
1812 AND object_name = 'HZ_LOCATIONS';
1813 */
1814 CURSOR c_policies(l_schema VARCHAR2) IS
1815 SELECT object_owner, object_name, policy_name
1816 FROM sys.dba_policies
1817 WHERE enable = 'YES'
1818 AND object_owner = l_schema
1819 AND object_name = 'HZ_LOCATIONS';
1820
1821 TYPE t_namelist IS TABLE OF VARCHAR2(30);
1822 l_owners t_namelist;
1823 l_objects t_namelist;
1824 l_policies t_namelist;
1825 str varchar2(1000);
1826 l_status sys.all_indexes.status%type;
1827 l_domidx_opstatus sys.all_indexes.DOMIDX_OPSTATUS%type;
1828 x_dummy BOOLEAN;
1829 x_status varchar2(30);
1830 x_ind varchar2(30);
1831 x_index_owner varchar2(50);
1832 x_drop_index varchar2(255);
1833 x_del_meta varchar2(255);
1834 x_ins_meta varchar2(2000);
1835 l_debug_prefix VARCHAR2(30) := '';
1836
1837 BEGIN
1838 --enable_debug;
1839 x_dummy := fnd_installation.GET_APP_INFO('AR',x_status,x_ind,x_index_owner);
1840 OPEN c_policies(x_index_owner);
1841 FETCH c_policies BULK COLLECT INTO l_owners, l_objects, l_policies;
1842 CLOSE c_policies;
1843 -- disable currently enabled policies
1844 FOR l_count IN 1..l_owners.COUNT LOOP
1845 dbms_rls.enable_policy(l_owners(l_count) , l_objects(l_count),
1846 l_policies(l_count), FALSE);
1847 END LOOP;
1848 OPEN c_index(x_index_owner);
1849 FETCH c_index into l_status,l_domidx_opstatus;
1850 IF c_index%NOTFOUND THEN /* Index is Missing */
1851 -- Delete Meta Data
1852 -- Bug 7262437 : Changed deleting from view to directly from table
1853 /* x_del_meta := 'Delete user_sdo_geom_metadata
1854 Where table_name = ''HZ_LOCATIONS''
1855 And column_name= ''GEOMETRY''';
1856 */
1857 x_del_meta := 'Delete MDSYS.SDO_GEOM_METADATA_TABLE
1858 Where sdo_table_name = ''HZ_LOCATIONS''
1859 AND sdo_column_name= ''GEOMETRY''
1860 AND sdo_owner = '''||x_index_owner||'''';
1861 EXECUTE IMMEDIATE x_del_meta;
1862 -- Create Meta Data
1863 -- Bug 7262437 : Changed inserting directly into table insteda of view
1864 -- to avoid default user name (APPS) getting inserted in sdo_owner column
1865 /* x_ins_meta := 'INSERT INTO user_sdo_geom_metadata (
1866 table_name, column_name, diminfo, srid ) VALUES (
1867 ''HZ_LOCATIONS'', ''GEOMETRY'',
1868 mdsys.sdo_dim_array(
1869 mdsys.sdo_dim_element(''longitude'', -180, 180, 0.00005),
1873 sdo_owner, sdo_table_name, sdo_column_name, sdo_diminfo, sdo_srid ) VALUES ('''
1870 mdsys.sdo_dim_element(''latitude'', -90, 90, 0.00005)), 8307 )';
1871 */
1872 x_ins_meta := 'INSERT INTO MDSYS.SDO_GEOM_METADATA_TABLE (
1874 ||x_index_owner||''', '||
1875 '''HZ_LOCATIONS'', ''GEOMETRY'',
1876 mdsys.sdo_dim_array(
1877 mdsys.sdo_dim_element(''longitude'', -180, 180, 0.00005),
1878 mdsys.sdo_dim_element(''latitude'', -90, 90, 0.00005)), 8307 )';
1879
1880 EXECUTE IMMEDIATE x_ins_meta;
1881 -- Create Index
1882 Create_Index;
1883 ELSIF c_index%FOUND THEN
1884 IF l_status <> 'VALID' OR l_domidx_opstatus <> 'VALID' THEN
1885 /* Index Is Invalid */
1886 -- Drop Index
1887 x_drop_index := 'drop index '||x_index_owner||'.'|| 'HZ_LOCATIONS_N15 force';
1888 EXECUTE IMMEDIATE x_drop_index;
1889 -- Create Index
1890 Create_Index;
1891 ELSE /* Index Exists and Valid */
1892 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1893 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.rebuild_location_index (+)',
1894 p_prefix=>l_debug_prefix,
1895 p_msg_level=>fnd_log.level_procedure);
1896 END IF;
1897 -- Initialize the return code only if we are running as an independent
1898 -- concurrent program. We do not want to change the value of the return
1899 -- code if it has been initialized by the calling program.
1900 IF p_concurrent_mode = 'Y' THEN
1901 retcode := '0';
1902 END IF;
1903 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1904 hz_utility_v2pub.debug(p_message=>'rebuilding with:' || l_rebuild_string,
1905 p_prefix =>l_debug_prefix,
1906 p_msg_level=>fnd_log.level_statement);
1907 END IF;
1908 EXECUTE IMMEDIATE l_rebuild_string;
1909 END IF; /* Index Is Invalid */
1910 END IF; /* Index is Missing */
1911
1912 -- notify the user that the spatial index was successfully rebuilt, and of
1913 -- successful concurrent program termination only if we are running as a
1914 -- concurrent program.
1915 IF p_concurrent_mode = 'Y' THEN
1916 fnd_file.put_line(fnd_file.log,
1917 fnd_message.get_string('AR', 'HZ_GEO_INDEX_REBUILT'));
1918 fnd_file.put_line(fnd_file.output,
1919 fnd_message.get_string('AR', 'HZ_GEO_INDEX_REBUILT'));
1920 fnd_file.put_line(fnd_file.log,
1921 fnd_message.get_string('FND', 'CONC-CP SUCCESSFUL TERMINATION'));
1922 fnd_file.put_line(fnd_file.output,
1923 fnd_message.get_string('FND', 'CONC-CP SUCCESSFUL TERMINATION'));
1924 ELSE
1925 -- otherwise, just push the error onto the stack.
1926 fnd_message.set_name('AR', 'HZ_GEO_INDEX_REBUILT');
1927 fnd_msg_pub.add;
1928 END IF;
1929 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1930 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.rebuild_location_index (-)',
1931 p_prefix=>l_debug_prefix,
1932 p_msg_level=>fnd_log.level_procedure);
1933 END IF;
1934 Close c_index;
1935 --disable_debug;
1936 -- restore previous state: re-enable previously enabled policies
1937 FOR l_count IN 1..l_owners.COUNT LOOP
1938 dbms_rls.enable_policy(l_owners(l_count), l_objects(l_count),
1939 l_policies(l_count), TRUE);
1940 END LOOP;
1941 -- clean up.
1942 l_owners.DELETE;
1943 l_objects.DELETE;
1944 l_policies.DELETE;
1945 EXCEPTION
1946 WHEN OTHERS THEN
1947 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1948 fnd_message.set_token('ERROR', SQLERRM);
1949
1950 -- log the error only if we are running as a concurrent program.
1951 -- otherwise, push the error onto the stack.
1952 IF p_concurrent_mode = 'Y' THEN
1953 fnd_file.put_line(fnd_file.log, fnd_message.get);
1954 fnd_file.put_line(fnd_file.output,
1955 fnd_message.get_string('FND',
1956 'CONC-CHECK LOG FOR DETAILS'));
1957 ELSE
1958 fnd_msg_pub.add;
1959 END IF;
1960 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1961 hz_utility_v2pub.debug(p_message=>g_pkg_name||' Error:',
1962 p_prefix=>'SQL ERROR',
1963 p_msg_level=>fnd_log.level_error);
1964 hz_utility_v2pub.debug(p_message=>SQLERRM,
1965 p_prefix=>'SQL ERROR',
1966 p_msg_level=>fnd_log.level_error);
1967 END IF;
1968 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1969 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.rebuild_location_index (-)',
1970 p_prefix=>l_debug_prefix,
1971 p_msg_level=>fnd_log.level_procedure);
1972 END IF;
1973 -- bug fix 3872778 enable policy function
1974 FOR l_count IN 1..l_owners.COUNT LOOP
1975 dbms_rls.enable_policy(l_owners(l_count), l_objects(l_count),
1976 l_policies(l_count), TRUE);
1977 END LOOP;
1978 --disable_debug;
1979 retcode := '2';
1980 errbuf := SQLERRM;
1981 END rebuild_location_index;
1982
1983 PROCEDURE Create_Index
1984 IS
1985 object_exists EXCEPTION;
1986 column_not_found EXCEPTION;
1987 domainobj_exists EXCEPTION;
1988 no_metadata_found EXCEPTION;
1989
1990 PRAGMA EXCEPTION_INIT(object_exists, -955);
1991 PRAGMA EXCEPTION_INIT(column_not_found, -904);
1992 PRAGMA EXCEPTION_INIT(domainobj_exists, -29879);
1993 PRAGMA EXCEPTION_INIT(no_metadata_found, -13203);
1994
1995 l_exec_string VARCHAR2(1000) ;
1996 x_dummy BOOLEAN;
1997 x_status varchar2(30);
1998 x_ind varchar2(30);
1999 x_index_owner varchar2(50);
2000 check_tspace_exist varchar2(100); --Bug 3299301
2001 physical_tspace_name varchar2(100); --Bug 3299301
2002 BEGIN
2003 x_dummy := fnd_installation.GET_APP_INFO('AR',x_status,x_ind,x_index_owner);
2004 AD_TSPACE_UTIL.get_tablespace_name('AR','TRANSACTION_INDEXES','Y',check_tspace_exist,physical_tspace_name);
2005 l_exec_string := 'CREATE INDEX '||x_index_owner||'.'|| 'hz_locations_n15 ON '||x_index_owner||'.'||
2006 'hz_locations(geometry) INDEXTYPE IS mdsys.spatial_index parameters(''TABLESPACE='||
2007 physical_tspace_name||''')'; --Bug 3299301
2008 -- create the index
2009 BEGIN
2010 if(check_tspace_exist = 'Y') THEN --Bug 3299301
2011 EXECUTE IMMEDIATE l_exec_string;
2012 end if;
2013 EXCEPTION
2014 WHEN column_not_found THEN
2015 NULL;
2016 WHEN object_exists THEN
2017 NULL;
2018 WHEN domainobj_exists THEN
2019 NULL;
2020 WHEN no_metadata_found THEN
2021 NULL;
2022 END;
2023
2024 END Create_Index;
2025 END hz_elocation_pkg;
[Home] [Help]