4 g_sets_per_commit CONSTANT NUMBER := 5;
1 PACKAGE BODY hz_elocation_pkg AS
2 /*$Header: ARHELOCB.pls 120.28.12020000.2 2013/01/29 11:10:30 rgokavar ship $*/
3
5 g_file_debug BOOLEAN := FALSE;
6 g_cp_detail CONSTANT VARCHAR2(1) := FND_PROFILE.VALUE('HZ_CP_DETAIL');
7
8 --------------------------------------
12 -- PRIVATE PROCEDURE enable_debug
9 -- private procedures and functions
10 --------------------------------------
11 --
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 :' ||
164 END IF;
161 TO_CHAR(p_location_id)||'(+)',
162 p_prefix=>l_debug_prefix,
163 p_msg_level=>fnd_log.level_procedure);
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
286 -- 29-11-2010 Sudhir Gokavarapu ER10211310 Complete logic is moved to update_geometry_all
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.
287 -- procedure, removed complete code as this procedure will not
288 -- be called from any where.
289 --------------------------------------
290 PROCEDURE update_geometry (
291 errbuf OUT NOCOPY VARCHAR2,
292 retcode OUT NOCOPY VARCHAR2,
293 p_loc_type IN VARCHAR2 DEFAULT 'P',
294 p_site_use_type IN VARCHAR2 DEFAULT NULL,
295 p_country IN VARCHAR2 DEFAULT NULL,
296 p_iden_addr_only IN VARCHAR2 DEFAULT 'N',
297 p_incremental IN VARCHAR2 DEFAULT 'N',
298 p_all_partial IN VARCHAR2 DEFAULT 'ALL',
299 p_nb_row_update IN VARCHAR2 DEFAULT 'ALL',
300 p_nb_row IN NUMBER DEFAULT 20,
301 p_nb_try IN NUMBER DEFAULT 3
302
303 ) IS
304 /* TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
305 TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
306 TYPE citylist IS TABLE OF hz_locations.city%TYPE;
307 TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
308 TYPE statelist IS TABLE OF hz_locations.state%TYPE;
309 TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
310 TYPE countylist IS TABLE OF hz_locations.county%TYPE;
311 TYPE provincelist IS TABLE OF hz_locations.province%TYPE;
312 l_location_ids locationlist;
313 l_address1s addresslist;
314 l_address2s addresslist;
315 l_address3s addresslist;
316 l_address4s addresslist;
317 l_cities citylist;
318 l_postal_codes pcodelist;
319 l_states statelist;
320 l_countries countrylist;
321 l_counties countylist;
322 l_provinces provincelist;
323
324 -- run this query if the party site use is specified,
325 -- we are NOT running in incremental mode
326 -- and we are NOT running for Standalone Locations
327 -- and p_country was not passed
328 CURSOR cu_loc1a (p_request_id IN NUMBER) IS
329 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
330 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
331 FROM hz_locations hl
332 WHERE EXISTS (SELECT 1
333 FROM hz_party_site_uses hpsu,
334 hz_party_sites hps
335 WHERE hpsu.site_use_type = p_site_use_type
336 AND hpsu.party_site_id = hps.party_site_id
337 AND hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
338 AND hps.location_id = hl.location_id)
339 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2);
340
341 -- run this query if the party site use is specified,
342 -- run this query if the party site use is specified,
343 -- we are NOT running in incremental mode
344 -- and we are NOT running for Standalone Locations
345 -- and p_country was passed
346 CURSOR cu_loc1ac (p_request_id IN NUMBER) IS
347 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
348 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
349 FROM hz_locations hl
350 WHERE EXISTS (SELECT 1
351 FROM hz_party_site_uses hpsu,
352 hz_party_sites hps
353 WHERE hpsu.site_use_type = p_site_use_type
354 AND hpsu.party_site_id = hps.party_site_id
355 AND hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
356 AND hps.location_id = hl.location_id)
357 AND hl.country = p_country
358 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2);
359
360 -- run this query if the party site use is specified,
361 -- we are running in incremental mode.
362 -- and we are NOT running for Standalone Locations
363 -- and p_country was not passed
364 CURSOR cu_loc1b (p_request_id IN NUMBER) IS
365 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
366 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
367 FROM hz_locations hl
368 WHERE EXISTS (SELECT 1
369 FROM hz_party_site_uses hpsu,
370 hz_party_sites hps
371 WHERE hpsu.site_use_type = p_site_use_type
372 AND hpsu.party_site_id = hps.party_site_id
373 AND hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
374 AND hps.location_id = hl.location_id)
375 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
376 AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL);
377
378 -- run this query if the party site use is specified,
379 -- we are running in incremental mode.
380 -- and we are NOT running for Standalone Locations
381 -- and p_country was passed
382 CURSOR cu_loc1bc (p_request_id IN NUMBER) IS
383 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
384 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
385 FROM hz_locations hl
386 WHERE EXISTS (SELECT 1
387 FROM hz_party_site_uses hpsu,
388 hz_party_sites hps
389 WHERE hpsu.site_use_type = p_site_use_type
390 AND hpsu.party_site_id = hps.party_site_id
394 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
391 AND hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
392 AND hps.location_id = hl.location_id)
393 AND hl.country = p_country
395 AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL);
396
397 -- run this query if party site use is not specified,
398 -- we are NOT running in incremental mode
399 -- and we are NOT running for Standalone Locations
400 -- and p_country was not passed
401 CURSOR cu_loc2a (p_request_id IN NUMBER) IS
402 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
403 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
404 FROM hz_locations hl
405 WHERE EXISTS (SELECT 1
406 FROM hz_party_sites hps
407 WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
408 AND hps.location_id = hl.location_id)
409 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2);
410
411 -- run this query if party site use is not specified,
412 -- we are NOT running in incremental mode
413 -- and we are NOT running for Standalone Locations
414 -- and p_country was passed
415 CURSOR cu_loc2ac (p_request_id IN NUMBER) IS
416 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
417 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
418 FROM hz_locations hl
419 WHERE EXISTS (SELECT 1
420 FROM hz_party_sites hps
421 WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
422 AND hps.location_id = hl.location_id)
423 AND hl.country = p_country
424 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2);
425
426 -- run this query if party site use is not specified,
427 -- we are running in incremental mode
428 -- and we are NOT running for Standalone Locations
429 CURSOR cu_loc2b (p_request_id IN NUMBER) IS
430 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
431 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
432 FROM hz_locations hl
433 WHERE EXISTS (SELECT 1
434 FROM hz_party_sites hps
435 WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
436 AND hps.location_id = hl.location_id)
437 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
438 AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL);
439
440 -- run this query if party site use is not specified,
441 -- we are running in incremental mode
442 -- and we are NOT running for Standalone Locations
443 -- and p_country was passed
444 CURSOR cu_loc2bc (p_request_id IN NUMBER) IS
445 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
446 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
447 FROM hz_locations hl
448 WHERE EXISTS (SELECT 1
449 FROM hz_party_sites hps
450 WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
451 AND hps.location_id = hl.location_id)
452 AND hl.country = p_country
453 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
454 AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL);
455
456 -- run this query if we are running for Standalone Locations
457 -- and NOT in incremental mode
458 -- and p_country was not passed
459 CURSOR cu_loc3a (p_request_id IN NUMBER) IS
460 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
461 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
462 FROM hz_locations hl
463 WHERE NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
464 AND NOT EXISTS (SELECT 1
465 FROM HZ_PARTY_SITES hps
466 WHERE hps.location_id = hl.location_id );
467
468 -- run this query if we are running for Standalone Locations
469 -- and NOT in incremental mode
470 -- and p_country was passed
471 CURSOR cu_loc3ac (p_request_id IN NUMBER) IS
472 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
473 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
474 FROM hz_locations hl
475 WHERE hl.country = p_country
476 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
477 AND NOT EXISTS (SELECT 1
478 FROM HZ_PARTY_SITES hps
479 WHERE hps.location_id = hl.location_id );
480
481 -- run this query if we are running for Standalone Locations
482 -- and in incremental mode
483 -- and p_country was not passed
484 CURSOR cu_loc3b (p_request_id IN NUMBER) IS
485 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
486 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
487 FROM hz_locations hl
488 WHERE NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
489 AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL)
490 AND NOT EXISTS (SELECT 1
491 FROM HZ_PARTY_SITES hps
492 WHERE hps.location_id = hl.location_id );
493
494 -- run this query if we are running for Standalone Locations
495 -- and in incremental mode
496 -- and p_country was passed
497 CURSOR cu_loc3bc (p_request_id IN NUMBER) IS
501 WHERE hl.country = p_country
498 SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
499 hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
500 FROM hz_locations hl
502 AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
503 AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL)
504 AND NOT EXISTS (SELECT 1
505 FROM HZ_PARTY_SITES hps
506 WHERE hps.location_id = hl.location_id );
507
508 l_array hz_geocode_pkg.loc_array := hz_geocode_pkg.loc_array();
509 l_rec hz_location_v2pub.location_rec_type;
510 l_http_ad VARCHAR2(200);
511 l_proxy VARCHAR2(100);
512 l_port VARCHAR2(10);
513 l_port_num NUMBER;
514 x_return_status VARCHAR2(10);
515 x_msg_count NUMBER;
516 x_msg_data VARCHAR2(2000);
517 cpt NUMBER := 0;
518 cpt_update NUMBER := 0;
519 l_nb_row_update NUMBER DEFAULT NULL;
520 l_nb_update NUMBER;
521 l_str_exe VARCHAR2(500);
522 expect_http_ad EXCEPTION;
523 exchttp EXCEPTION;
524 port_number EXCEPTION;
525 nlsnumexp EXCEPTION;
526 morethanmaxrow EXCEPTION;
527 atleastonerow EXCEPTION;
528 msg VARCHAR2(2000);
529 l_return_status VARCHAR2(10);
530 l_set_size NUMBER;
531 l_request_id NUMBER := hz_utility_v2pub.request_id;
532 i NUMBER;
533 l_nb_retries NUMBER := NVL(p_nb_try, 3);
534 l_batch_size NUMBER := NVL(p_nb_row, hz_geocode_pkg.g_max_rows);
535 l_retcode VARCHAR2(10);
536 l_errbuf VARCHAR2(4000);
537 l_proxy_var VARCHAR2(240);
538 l_port_var VARCHAR2(240);
539 l_debug_prefix VARCHAR2(30) := '';
540 */
541 BEGIN
542
543 --enable_debug;
544
545 /* l_nb_update := 0;
546 l_set_size := l_batch_size * g_sets_per_commit;
547
548 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
549 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (+)',
550 p_prefix=>l_debug_prefix,
551 p_msg_level=>fnd_log.level_procedure);
552 END IF;
553
554
555
556 x_return_status := fnd_api.g_ret_sts_success;
557 l_return_status := fnd_api.g_ret_sts_success;
558 fnd_msg_pub.initialize;
559
560 retcode := '0';
561 fnd_file.put_line(fnd_file.log,
562 fnd_message.get_string('FND',
563 'CONC-START PROGRAM EXECUTION'));
564 fnd_file.put_line(fnd_file.log, '');
565
566 fnd_file.put_line(fnd_file.output,
567 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS: '));
568 fnd_file.put_line(fnd_file.output,
569 fnd_message.get_string('FND',
570 'CONC-START PROGRAM EXECUTION'));
571
572 IF hz_geocode_pkg.is_nls_num_char_pt_com <> 'Y' THEN
573 l_str_exe := 'ALTER SESSION SET nls_numeric_characters = ''.,''';
574 EXECUTE IMMEDIATE l_str_exe;
575 END IF;
576
577 IF p_all_partial <> 'ALL' THEN
578 IF p_nb_row_update IS NULL OR p_nb_row_update = 'ALL' THEN
579 l_nb_row_update := 1000;
580 ELSE
581 l_nb_row_update := TO_NUMBER(p_nb_row_update);
582 END IF;
583 IF l_nb_row_update IS NULL OR l_nb_row_update <= 0 THEN
584 time_put_line('At least one row error.');
585 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
586 hz_utility_v2pub.debug(p_message=>'At least one row error.',
587 p_prefix=>'ERROR',
588 p_msg_level=>fnd_log.level_error);
589 END IF;
590 RAISE atleastonerow;
591 END IF;
592 END IF;
593
594 IF l_batch_size > hz_geocode_pkg.g_max_rows THEN
595 RAISE morethanmaxrow;
596 END IF;
597
598 -- Get the website we're supposed to access for geospatial information.
599 fnd_profile.get('HZ_GEOCODE_WEBSITE', l_http_ad);
600 IF l_http_ad IS NULL THEN
601 time_put_line('HTTP address missing');
602 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
603 hz_utility_v2pub.debug(p_message=>'HTTP address missing',
604 p_prefix=>'ERROR',
605 p_msg_level=>fnd_log.level_error);
606 END IF;
607 RAISE expect_http_ad;
608 END IF;
609
610 -- Only get the proxy server if we need it - check the proxy bypass list.
611 IF hz_geocode_pkg.in_bypass_list(
612 l_http_ad,
613 fnd_profile.value('WEB_PROXY_BYPASS_DOMAINS')
614 )
615 THEN
616 -- site is in the bypass list.
617 l_proxy_var := 'Null proxy';
618 l_port_var := 'Null port';
619 l_proxy := NULL;
620 l_port := NULL;
621 ELSE
622 -- site is not in the bypass list.
623 -- First, attempt to get proxy value from FND. If the proxy name is not
624 -- found, try the TCA values regardless of whether the port is found.
625 l_proxy_var := 'WEB_PROXY_HOST';
626 l_port_var := 'WEB_PROXY_PORT';
627 l_proxy := fnd_profile.value(l_proxy_var);
628 l_port := fnd_profile.value(l_port_var);
629 END IF;
630
631 -- log the profile options that are being used to run this program.
632 fnd_file.put_line(fnd_file.log,
633 fnd_message.get_string('FND', 'PROFILES-VALUES'));
634 fnd_file.put_line(fnd_file.log, 'HZ_GEOCODE_WEBSITE: ' || l_http_ad);
638
635 fnd_file.put_line(fnd_file.log, l_proxy_var || ': ' || l_proxy);
636 fnd_file.put_line(fnd_file.log, l_port_var || ': ' || l_port);
637 fnd_file.put_line(fnd_file.log, '');
639 -- repeat in the output file.
640 fnd_file.put_line(fnd_file.output,
641 fnd_message.get_string('FND', 'PROFILES-VALUES'));
642 fnd_file.put_line(fnd_file.output, 'HZ_GEOCODE_WEBSITE: ' || l_http_ad);
643 fnd_file.put_line(fnd_file.output, l_proxy_var || ': ' || l_proxy);
644 fnd_file.put_line(fnd_file.output, l_port_var || ': ' || l_port);
645 fnd_file.put_line(fnd_file.output, '');
646
647 -- repeat in debug output.
648 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
649 hz_utility_v2pub.debug(p_message=>'HZ_GEOCODE_WEBSITE: ' || l_http_ad,
650 p_prefix =>l_debug_prefix,
651 p_msg_level=>fnd_log.level_statement);
652 hz_utility_v2pub.debug(p_message=>l_proxy_var || ': ' || l_proxy,
653 p_prefix =>l_debug_prefix,
654 p_msg_level=>fnd_log.level_statement);
655 hz_utility_v2pub.debug(p_message=>l_port_var || ': ' || l_port,
656 p_prefix =>l_debug_prefix,
657 p_msg_level=>fnd_log.level_statement);
658 END IF;
659
660 IF l_port IS NOT NULL THEN
661 -- J. del Callar: set the port number and handle non-numeric values
662 BEGIN
663 l_port_num := TO_NUMBER(l_port);
664 EXCEPTION
665 WHEN OTHERS THEN
666 RAISE port_number;
667 END;
668 ELSE
669 l_port_num := NULL;
670 END IF;
671
672 -- J. del Callar: main transaction loop: process all records picked up
673 -- by cu_loc and commit every l_set_size records.
674 LOOP
675 -- J. del Callar: re-open the cursor only if it has been closed by the
676 -- commit statement, or if it has not been opened before.
677
678 -- swbhatna: Added IF loop to check for p_loc_type value. If 'P', then earlier code remains intact
679 IF p_loc_type = 'P' THEN
680
681 IF p_site_use_type IS NOT NULL AND p_incremental = 'N' THEN
682 -- J. del Callar: use cu_loc1a for non-null site uses
683 -- and non-incremental mode.
684 IF(p_country IS NULL) THEN
685 IF NOT cu_loc1a%ISOPEN THEN
686 OPEN cu_loc1a (l_request_id);
687 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
688 hz_utility_v2pub.debug(p_message=>'Opening cursor 1a with args:',
689 p_prefix =>l_debug_prefix,
690 p_msg_level=>fnd_log.level_statement);
691 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
692 p_prefix =>l_debug_prefix,
693 p_msg_level=>fnd_log.level_statement);
694 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
695 p_prefix =>l_debug_prefix,
696 p_msg_level=>fnd_log.level_statement);
697 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
698 p_prefix =>l_debug_prefix,
699 p_msg_level=>fnd_log.level_statement);
700 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
701 p_prefix =>l_debug_prefix,
702 p_msg_level=>fnd_log.level_statement);
703 END IF;
704 END IF;
705
706 -- J. del Callar: fetch the next set of location information.
707 FETCH cu_loc1a BULK COLLECT
708 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
709 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
710 LIMIT l_set_size;
711
712 -- J. del Callar: exit the loop if we've processed all records
713 IF cu_loc1a%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
714 time_put_line('Exiting because of NOTFOUND condition');
715 time_put_line('Count=' || l_location_ids.COUNT);
716 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
717 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
718 p_prefix=>'WARNING',
719 p_msg_level=>fnd_log.level_exception);
720 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
721 p_prefix=>'WARNING',
722 p_msg_level=>fnd_log.level_exception);
723 END IF;
724 EXIT;
725 END IF;
726 ELSE -- cu_loc1a/cu_loc1ac check p_country is null or not
727 IF NOT cu_loc1ac%ISOPEN THEN
728 OPEN cu_loc1ac (l_request_id);
729 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
730 hz_utility_v2pub.debug(p_message=>'Opening cursor 1ac with args:',
731 p_prefix =>l_debug_prefix,
732 p_msg_level=>fnd_log.level_statement);
733 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
734 p_prefix =>l_debug_prefix,
735 p_msg_level=>fnd_log.level_statement);
736 hz_utility_v2pub.debug(p_message=>'country=' || p_country,
737 p_prefix =>l_debug_prefix,
738 p_msg_level=>fnd_log.level_statement);
739 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
743 p_prefix =>l_debug_prefix,
740 p_prefix =>l_debug_prefix,
741 p_msg_level=>fnd_log.level_statement);
742 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
744 p_msg_level=>fnd_log.level_statement);
745 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
746 p_prefix =>l_debug_prefix,
747 p_msg_level=>fnd_log.level_statement);
748 END IF;
749 END IF;
750
751 -- J. del Callar: fetch the next set of location information.
752 FETCH cu_loc1ac BULK COLLECT
753 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
754 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
755 LIMIT l_set_size;
756
757 -- J. del Callar: exit the loop if we've processed all records
758 IF cu_loc1ac%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
759 time_put_line('Exiting because of NOTFOUND condition');
760 time_put_line('Count=' || l_location_ids.COUNT);
761 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
762 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
763 p_prefix=>'WARNING',
764 p_msg_level=>fnd_log.level_exception);
765 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
766 p_prefix=>'WARNING',
767 p_msg_level=>fnd_log.level_exception);
768 END IF;
769 EXIT;
770 END IF;
771 END IF; -- cu_loc1a/cu_loc1ac check p_country is null or not
772
773 ELSIF p_site_use_type IS NOT NULL AND p_incremental = 'Y' THEN
774 -- J. del Callar: use cu_loc1b for non-null site uses
775 -- and incremental mode.
776 IF(p_country IS NULL) THEN
777 IF NOT cu_loc1b%ISOPEN THEN
778 OPEN cu_loc1b (l_request_id);
779 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
780 hz_utility_v2pub.debug(p_message=>'Opening cursor 1b with args:',
781 p_prefix =>l_debug_prefix,
782 p_msg_level=>fnd_log.level_statement);
783 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
784 p_prefix =>l_debug_prefix,
785 p_msg_level=>fnd_log.level_statement);
786 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
787 p_prefix =>l_debug_prefix,
788 p_msg_level=>fnd_log.level_statement);
789 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
790 p_prefix =>l_debug_prefix,
791 p_msg_level=>fnd_log.level_statement);
792 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
793 p_prefix =>l_debug_prefix,
794 p_msg_level=>fnd_log.level_statement);
795 END IF;
796 END IF;
797
798 -- J. del Callar: fetch the next set of location information.
799 FETCH cu_loc1b BULK COLLECT
800 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
801 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
802 LIMIT l_set_size;
803
804 -- J. del Callar: exit the loop if we've processed all records
805 IF cu_loc1b%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
806 time_put_line('Exiting because of NOTFOUND condition');
807 time_put_line('Count=' || l_location_ids.COUNT);
808 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
809 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
810 p_prefix=>'WARNING',
811 p_msg_level=>fnd_log.level_exception);
812 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
813 p_prefix=>'WARNING',
814 p_msg_level=>fnd_log.level_exception);
815 END IF;
816 EXIT;
817 END IF;
818 ELSE -- cu_loc1b/cu_loc1bc, check if p_country is null or not
819 IF NOT cu_loc1bc%ISOPEN THEN
820 OPEN cu_loc1bc (l_request_id);
821 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
822 hz_utility_v2pub.debug(p_message=>'Opening cursor 1bc with args:',
823 p_prefix =>l_debug_prefix,
824 p_msg_level=>fnd_log.level_statement);
825 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
826 p_prefix =>l_debug_prefix,
827 p_msg_level=>fnd_log.level_statement);
828 hz_utility_v2pub.debug(p_message=>'country=' || p_country,
829 p_prefix =>l_debug_prefix,
830 p_msg_level=>fnd_log.level_statement);
831 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
832 p_prefix =>l_debug_prefix,
833 p_msg_level=>fnd_log.level_statement);
834 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
838 p_prefix =>l_debug_prefix,
835 p_prefix =>l_debug_prefix,
836 p_msg_level=>fnd_log.level_statement);
837 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
839 p_msg_level=>fnd_log.level_statement);
840 END IF;
841 END IF;
842
843 -- J. del Callar: fetch the next set of location information.
844 FETCH cu_loc1bc BULK COLLECT
845 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
846 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
847 LIMIT l_set_size;
848
849 -- J. del Callar: exit the loop if we've processed all records
850 IF cu_loc1bc%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
851 time_put_line('Exiting because of NOTFOUND condition');
852 time_put_line('Count=' || l_location_ids.COUNT);
853 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
854 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
855 p_prefix=>'WARNING',
856 p_msg_level=>fnd_log.level_exception);
857 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
858 p_prefix=>'WARNING',
859 p_msg_level=>fnd_log.level_exception);
860 END IF;
861 EXIT;
862 END IF;
863 END IF; -- cu_loc1b/cu_loc1bc, check if p_country is null or not
864
865 ELSIF p_site_use_type IS NULL AND p_incremental = 'N' THEN
866 -- J. del Callar: use cu_loc2a for null site uses
867 -- and non-incremental mode.
868 IF(p_country IS NULL) THEN
869 IF NOT cu_loc2a%ISOPEN THEN
870 OPEN cu_loc2a (l_request_id);
871 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
872 hz_utility_v2pub.debug(p_message=>'Opening cursor 2a with args:',
873 p_prefix =>l_debug_prefix,
874 p_msg_level=>fnd_log.level_statement);
875 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
876 p_prefix =>l_debug_prefix,
877 p_msg_level=>fnd_log.level_statement);
878 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
879 p_prefix =>l_debug_prefix,
880 p_msg_level=>fnd_log.level_statement);
881 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
882 p_prefix =>l_debug_prefix,
883 p_msg_level=>fnd_log.level_statement);
884 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
885 p_prefix =>l_debug_prefix,
886 p_msg_level=>fnd_log.level_statement);
887 END IF;
888 END IF;
889
890 -- J. del Callar: fetch the next set of location information.
891 FETCH cu_loc2a BULK COLLECT
892 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
893 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
894 LIMIT l_set_size;
895
896 -- J. del Callar: exit the loop if we've processed all records
897 IF cu_loc2a%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
898 time_put_line('Exiting because of NOTFOUND condition');
899 time_put_line('Count=' || l_location_ids.COUNT);
900 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
901 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
902 p_prefix=>'WARNING',
903 p_msg_level=>fnd_log.level_exception);
904 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
905 p_prefix=>'WARNING',
906 p_msg_level=>fnd_log.level_exception);
907 END IF;
908 EXIT;
909 END IF;
910 ELSE -- cu_loc2a/cu_loc2ac, check if p_country is null or not
911 IF NOT cu_loc2ac%ISOPEN THEN
912 OPEN cu_loc2ac (l_request_id);
913 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
914 hz_utility_v2pub.debug(p_message=>'Opening cursor 2ac with args:',
915 p_prefix =>l_debug_prefix,
916 p_msg_level=>fnd_log.level_statement);
917 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
918 p_prefix =>l_debug_prefix,
919 p_msg_level=>fnd_log.level_statement);
920 hz_utility_v2pub.debug(p_message=>'country=' || p_country,
921 p_prefix =>l_debug_prefix,
922 p_msg_level=>fnd_log.level_statement);
923 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
924 p_prefix =>l_debug_prefix,
925 p_msg_level=>fnd_log.level_statement);
926 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
927 p_prefix =>l_debug_prefix,
928 p_msg_level=>fnd_log.level_statement);
929 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
933 END IF;
930 p_prefix =>l_debug_prefix,
931 p_msg_level=>fnd_log.level_statement);
932 END IF;
934
935 -- J. del Callar: fetch the next set of location information.
936 FETCH cu_loc2ac BULK COLLECT
937 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
938 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
939 LIMIT l_set_size;
940
941 -- J. del Callar: exit the loop if we've processed all records
942 IF cu_loc2ac%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
943 time_put_line('Exiting because of NOTFOUND condition');
944 time_put_line('Count=' || l_location_ids.COUNT);
945 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
946 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
947 p_prefix=>'WARNING',
948 p_msg_level=>fnd_log.level_exception);
949 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
950 p_prefix=>'WARNING',
951 p_msg_level=>fnd_log.level_exception);
952 END IF;
953 EXIT;
954 END IF;
955 END IF; -- cu_loc2a/cu_loc2ac, check if p_country is null or not
956
957 ELSIF p_site_use_type IS NULL AND p_incremental = 'Y' THEN
958 -- J. del Callar use cu_loc2b for null site uses
959 -- and incremental mode.
960 IF(p_country IS NULL) THEN
961 IF NOT cu_loc2b%ISOPEN THEN
962 OPEN cu_loc2b (l_request_id);
963 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
964 hz_utility_v2pub.debug(p_message=>'Opening cursor 2b with args:',
965 p_prefix =>l_debug_prefix,
966 p_msg_level=>fnd_log.level_statement);
967 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
968 p_prefix =>l_debug_prefix,
969 p_msg_level=>fnd_log.level_statement);
970 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
971 p_prefix =>l_debug_prefix,
972 p_msg_level=>fnd_log.level_statement);
973 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
974 p_prefix =>l_debug_prefix,
975 p_msg_level=>fnd_log.level_statement);
976 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
977 p_prefix =>l_debug_prefix,
978 p_msg_level=>fnd_log.level_statement);
979 END IF;
980 END IF;
981
982 -- J. del Callar: fetch the next set of location information.
983 FETCH cu_loc2b BULK COLLECT
984 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
985 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
986 LIMIT l_set_size;
987
988 -- J. del Callar: exit the loop if we've processed all records
989 IF cu_loc2b%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
990 time_put_line('Exiting because of NOTFOUND condition');
991 time_put_line('Count=' || l_location_ids.COUNT);
992 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
993 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
994 p_prefix=>'WARNING',
995 p_msg_level=>fnd_log.level_exception);
996 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
997 p_prefix=>'WARNING',
998 p_msg_level=>fnd_log.level_exception);
999 END IF;
1000 EXIT;
1001 END IF;
1002 ELSE -- cu_loc2b/cu_loc2bc, check if p_country is null or not
1003 IF NOT cu_loc2bc%ISOPEN THEN
1004 OPEN cu_loc2bc (l_request_id);
1005 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1006 hz_utility_v2pub.debug(p_message=>'Opening cursor 2bc with args:',
1007 p_prefix =>l_debug_prefix,
1008 p_msg_level=>fnd_log.level_statement);
1009 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1010 p_prefix =>l_debug_prefix,
1011 p_msg_level=>fnd_log.level_statement);
1012 hz_utility_v2pub.debug(p_message=>'country=' || p_country,
1013 p_prefix =>l_debug_prefix,
1014 p_msg_level=>fnd_log.level_statement);
1015 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1016 p_prefix =>l_debug_prefix,
1017 p_msg_level=>fnd_log.level_statement);
1018 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1019 p_prefix =>l_debug_prefix,
1020 p_msg_level=>fnd_log.level_statement);
1021 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1022 p_prefix =>l_debug_prefix,
1023 p_msg_level=>fnd_log.level_statement);
1024 END IF;
1025 END IF;
1029 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
1026
1027 -- J. del Callar: fetch the next set of location information.
1028 FETCH cu_loc2bc BULK COLLECT
1030 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
1031 LIMIT l_set_size;
1032
1033 -- J. del Callar: exit the loop if we've processed all records
1034 IF cu_loc2bc%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
1035 time_put_line('Exiting because of NOTFOUND condition');
1036 time_put_line('Count=' || l_location_ids.COUNT);
1037 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1038 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1039 p_prefix=>'WARNING',
1040 p_msg_level=>fnd_log.level_exception);
1041 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1042 p_prefix=>'WARNING',
1043 p_msg_level=>fnd_log.level_exception);
1044 END IF;
1045 EXIT;
1046 END IF;
1047 END IF; -- cu_loc2b/cu_loc2bc, check if p_country is null or not
1048 END IF;
1049
1050 -- swbhatna: If p_loc_type = 'S', then open cursor
1051 -- cu_loc3a or cu_loc3b depending on p_incremental value
1052 ELSIF p_loc_type = 'S' THEN
1053
1054 IF p_incremental = 'N' THEN
1055 -- swbhatna: use cu_loc3a for updating standalone locations
1056 -- and in non-incremental mode.
1057
1058 IF(p_country IS NULL) THEN
1059 IF NOT cu_loc3a%ISOPEN THEN
1060 OPEN cu_loc3a (l_request_id);
1061 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1062 hz_utility_v2pub.debug(p_message=>'Opening cursor 3a with args:',
1063 p_prefix =>l_debug_prefix,
1064 p_msg_level=>fnd_log.level_statement);
1065 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1066 p_prefix =>l_debug_prefix,
1067 p_msg_level=>fnd_log.level_statement);
1068 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1069 p_prefix =>l_debug_prefix,
1070 p_msg_level=>fnd_log.level_statement);
1071 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1072 p_prefix =>l_debug_prefix,
1073 p_msg_level=>fnd_log.level_statement);
1074 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1075 p_prefix =>l_debug_prefix,
1076 p_msg_level=>fnd_log.level_statement);
1077 hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1078 p_prefix =>l_debug_prefix,
1079 p_msg_level=>fnd_log.level_statement);
1080 END IF;
1081 END IF;
1082
1083 -- swbhatna: fetch the next set of location information.
1084 FETCH cu_loc3a BULK COLLECT
1085 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
1086 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
1087 LIMIT l_set_size;
1088
1089 -- swbhatna: exit the loop if we've processed all records
1090 IF cu_loc3a%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
1091 time_put_line('Exiting because of NOTFOUND condition');
1092 time_put_line('Count=' || l_location_ids.COUNT);
1093 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1094 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1095 p_prefix=>'WARNING',
1096 p_msg_level=>fnd_log.level_exception);
1097 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1098 p_prefix=>'WARNING',
1099 p_msg_level=>fnd_log.level_exception);
1100 END IF;
1101 EXIT;
1102 END IF;
1103 ELSE -- cu_loc3a/cu_loc3ac, check if p_country is null or not
1104 IF NOT cu_loc3ac%ISOPEN THEN
1105 OPEN cu_loc3ac (l_request_id);
1106 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1107 hz_utility_v2pub.debug(p_message=>'Opening cursor 3ac with args:',
1108 p_prefix =>l_debug_prefix,
1109 p_msg_level=>fnd_log.level_statement);
1110 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1111 p_prefix =>l_debug_prefix,
1112 p_msg_level=>fnd_log.level_statement);
1113 hz_utility_v2pub.debug(p_message=>'country=' || p_country,
1114 p_prefix =>l_debug_prefix,
1115 p_msg_level=>fnd_log.level_statement);
1116 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1117 p_prefix =>l_debug_prefix,
1118 p_msg_level=>fnd_log.level_statement);
1119 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1120 p_prefix =>l_debug_prefix,
1121 p_msg_level=>fnd_log.level_statement);
1125 hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1122 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1123 p_prefix =>l_debug_prefix,
1124 p_msg_level=>fnd_log.level_statement);
1126 p_prefix =>l_debug_prefix,
1127 p_msg_level=>fnd_log.level_statement);
1128 END IF;
1129 END IF;
1130
1131 -- swbhatna: fetch the next set of location information.
1132 FETCH cu_loc3ac BULK COLLECT
1133 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
1134 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
1135 LIMIT l_set_size;
1136
1137 -- swbhatna: exit the loop if we've processed all records
1138 IF cu_loc3ac%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
1139 time_put_line('Exiting because of NOTFOUND condition');
1140 time_put_line('Count=' || l_location_ids.COUNT);
1141 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1142 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1143 p_prefix=>'WARNING',
1144 p_msg_level=>fnd_log.level_exception);
1145 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1146 p_prefix=>'WARNING',
1147 p_msg_level=>fnd_log.level_exception);
1148 END IF;
1149 END IF;
1150 END IF; -- cu_loc3a/cu_loc3ac, check if p_country is null or not
1151
1152 ELSIF p_incremental = 'Y' THEN
1153 -- swbhatna: use cu_loc3b for updating standalone locations
1154 -- and in incremental mode.
1155
1156 IF(p_country IS NULL) THEN
1157 IF NOT cu_loc3b%ISOPEN THEN
1158 OPEN cu_loc3b (l_request_id);
1159 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1160 hz_utility_v2pub.debug(p_message=>'Opening cursor 3b with args:',
1161 p_prefix =>l_debug_prefix,
1162 p_msg_level=>fnd_log.level_statement);
1163 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1164 p_prefix =>l_debug_prefix,
1165 p_msg_level=>fnd_log.level_statement);
1166 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1167 p_prefix =>l_debug_prefix,
1168 p_msg_level=>fnd_log.level_statement);
1169 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1170 p_prefix =>l_debug_prefix,
1171 p_msg_level=>fnd_log.level_statement);
1172 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1173 p_prefix =>l_debug_prefix,
1174 p_msg_level=>fnd_log.level_statement);
1175 hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1176 p_prefix =>l_debug_prefix,
1177 p_msg_level=>fnd_log.level_statement);
1178 END IF;
1179 END IF;
1180
1181 -- swbhatna: fetch the next set of location information.
1182 FETCH cu_loc3b BULK COLLECT
1183 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
1184 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
1185 LIMIT l_set_size;
1186
1187 -- swbhatna: exit the loop if we've processed all records
1188 IF cu_loc3b%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
1189 time_put_line('Exiting because of NOTFOUND condition');
1190 time_put_line('Count=' || l_location_ids.COUNT);
1191 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1192 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1193 p_prefix=>'WARNING',
1194 p_msg_level=>fnd_log.level_exception);
1195 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1196 p_prefix=>'WARNING',
1197 p_msg_level=>fnd_log.level_exception);
1198 END IF;
1199 EXIT;
1200 END IF;
1201 ELSE -- cu_loc3b/cu_loc3bc, check if p_country is null or not
1202 IF NOT cu_loc3bc%ISOPEN THEN
1203 OPEN cu_loc3bc (l_request_id);
1204 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1205 hz_utility_v2pub.debug(p_message=>'Opening cursor 3bc with args:',
1206 p_prefix =>l_debug_prefix,
1207 p_msg_level=>fnd_log.level_statement);
1208 hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1209 p_prefix =>l_debug_prefix,
1210 p_msg_level=>fnd_log.level_statement);
1211 hz_utility_v2pub.debug(p_message=>'country=' || p_country,
1212 p_prefix =>l_debug_prefix,
1213 p_msg_level=>fnd_log.level_statement);
1214 hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1215 p_prefix =>l_debug_prefix,
1216 p_msg_level=>fnd_log.level_statement);
1220 hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1217 hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1218 p_prefix =>l_debug_prefix,
1219 p_msg_level=>fnd_log.level_statement);
1221 p_prefix =>l_debug_prefix,
1222 p_msg_level=>fnd_log.level_statement);
1223 hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1224 p_prefix =>l_debug_prefix,
1225 p_msg_level=>fnd_log.level_statement);
1226 END IF;
1227 END IF;
1228
1229 -- swbhatna: fetch the next set of location information.
1230 FETCH cu_loc3bc BULK COLLECT
1231 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
1232 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
1233 LIMIT l_set_size;
1234
1235 -- swbhatna: exit the loop if we've processed all records
1236 IF cu_loc3bc%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
1237 time_put_line('Exiting because of NOTFOUND condition');
1238 time_put_line('Count=' || l_location_ids.COUNT);
1239 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1240 hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1241 p_prefix=>'WARNING',
1242 p_msg_level=>fnd_log.level_exception);
1243 hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1244 p_prefix=>'WARNING',
1245 p_msg_level=>fnd_log.level_exception);
1246 END IF;
1247 END IF;
1248 END IF; -- cu_loc3b/cu_loc3bc, check if p_country is null or not
1249 END IF;
1250
1251 ELSE
1252 l_return_status := fnd_api.g_ret_sts_unexp_error;
1253 time_put_line('Unexpected mode encountered');
1254 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1255 hz_utility_v2pub.debug(p_message=>'Unexpected mode encountered',
1256 p_prefix=>'ERROR',
1257 p_msg_level=>fnd_log.level_error);
1258 END IF;
1259 END IF;
1260 -- J. del Callar: exit the loop if no records were fetched the first time
1261 IF l_location_ids.COUNT = 0 THEN
1262 time_put_line('Exiting because COUNT=0');
1263 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1264 hz_utility_v2pub.debug(p_message=>'Exiting because COUNT=0',
1265 p_prefix=>'WARNING',
1266 p_msg_level=>fnd_log.level_exception);
1267 END IF;
1268 EXIT;
1269 END IF;
1270
1271 -- J. del Callar: exit the loop if our update limit has been exceeded.
1272 IF p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update THEN
1273 time_put_line('Exiting because partial=' || p_all_partial);
1274 time_put_line('cpt_update=' || cpt_update);
1275 time_put_line('nb_row_update=' || l_nb_row_update);
1276 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1277 hz_utility_v2pub.debug(p_message=>'Exiting because partial=' || p_all_partial,
1278 p_prefix=>'WARNING',
1279 p_msg_level=>fnd_log.level_exception);
1280 hz_utility_v2pub.debug(p_message=>'cpt_update=' || cpt_update,
1281 p_prefix=>'WARNING',
1282 p_msg_level=>fnd_log.level_exception);
1283 hz_utility_v2pub.debug(p_message=>'nb_row_update=' || l_nb_row_update,
1284 p_prefix=>'WARNING',
1285 p_msg_level=>fnd_log.level_exception);
1286 END IF;
1287 EXIT;
1288 END IF;
1289
1290 -- J. del Callar: main update loop: process up to l_set_size records.
1291 FOR i IN l_location_ids.first..l_location_ids.last LOOP
1292 cpt_update := cpt_update + 1;
1293
1294 -- J. del Callar: copy the cursor values into a new location array rec
1295 l_array.EXTEND;
1296 cpt := cpt + 1;
1297 l_array(cpt).location_id := l_location_ids(i);
1298 l_array(cpt).address1 := l_address1s(i);
1299 l_array(cpt).address2 := l_address2s(i);
1300 l_array(cpt).address3 := l_address3s(i);
1301 l_array(cpt).address4 := l_address4s(i);
1302 l_array(cpt).city := l_cities(i);
1303 l_array(cpt).postal_code := l_postal_codes(i);
1304 l_array(cpt).state := l_states(i);
1305 l_array(cpt).country := l_countries(i);
1306 l_array(cpt).province := l_provinces(i);
1307 l_array(cpt).county := l_counties(i);
1308
1309 fnd_file.put_line(fnd_file.log,
1310 'Processing location '||l_array(cpt).location_id);
1311 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1312 hz_utility_v2pub.debug(p_message=>'Processing location ' || l_array(cpt).location_id,
1313 p_prefix =>l_debug_prefix,
1314 p_msg_level=>fnd_log.level_statement);
1315 END IF;
1316
1317 -- execute the synchronization routine every l_batch_size, or
1318 -- if we have reached the last record to be updated, or
1319 -- if we have exceeded our update limit.
1320 IF cpt >= l_batch_size
1321 OR i = l_location_ids.last
1322 OR (p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update)
1323 THEN
1324 -- Process the records in the array.
1325 hz_geocode_pkg.get_spatial_coords(
1326 p_loc_array => l_array,
1327 p_name => NULL,
1328 p_http_ad => l_http_ad,
1332 x_return_status => l_return_status,
1329 p_proxy => l_proxy,
1330 p_port => l_port,
1331 p_retry => l_nb_retries,
1333 x_msg_count => x_msg_count,
1334 x_msg_data => x_msg_data
1335 );
1336
1337 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1338 time_put_line('Unexpected error encountered');
1339 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1340 hz_utility_v2pub.debug(p_message=>'Unexpected error encountered',
1341 p_prefix=>'ERROR',
1342 p_msg_level=>fnd_log.level_error);
1343 END IF;
1344
1345 -- Close the open cursor (depends on the site use type and the
1346 -- update mode).
1347 IF p_loc_type = 'P' THEN
1348 IF p_site_use_type IS NOT NULL AND p_incremental = 'N' THEN
1349 -- J. del Callar: use cu_loc1a for non-null site uses
1350 -- and non-incremental mode.
1351 IF (p_country IS NULL) THEN
1352 CLOSE cu_loc1a;
1353 ELSE
1354 CLOSE cu_loc1ac;
1355 END IF;
1356 ELSIF p_site_use_type IS NOT NULL AND p_incremental = 'Y' THEN
1357 -- J. del Callar: use cu_loc1b for non-null site uses
1358 -- and incremental mode.
1359 IF (p_country IS NULL) THEN
1360 CLOSE cu_loc1b;
1361 ELSE
1362 CLOSE cu_loc1bc;
1363 END IF;
1364 ELSIF p_site_use_type IS NULL AND p_incremental = 'N' THEN
1365 -- J. del Callar: use cu_loc2a for null site uses
1366 -- and non-incremental mode.
1367 IF (p_country IS NULL) THEN
1368 CLOSE cu_loc2a;
1369 ELSE
1370 CLOSE cu_loc2ac;
1371 END IF;
1372 ELSIF p_site_use_type IS NULL AND p_incremental = 'Y' THEN
1373 -- J. del Callar use cu_loc2b for null site uses
1374 -- and incremental mode.
1375 IF (p_country IS NULL) THEN
1376 CLOSE cu_loc2b;
1377 ELSE
1378 CLOSE cu_loc2bc;
1379 END IF;
1380 END IF;
1381 ELSIF p_loc_type = 'S' THEN
1382 IF p_incremental = 'N' THEN
1383 -- swbhatna: use cu_loc3a for updating Standalone locations
1384 -- and in non-incremental mode.
1385 IF (p_country IS NULL) THEN
1386 CLOSE cu_loc3a;
1387 ELSE
1388 CLOSE cu_loc3ac;
1389 END IF;
1390 ELSIF p_incremental = 'Y' THEN
1391 -- swbhatna: use cu_loc3b for updating Standalone locations
1392 -- and in incremental mode.
1393 IF (p_country IS NULL) THEN
1394 CLOSE cu_loc3b;
1395 ELSE
1396 CLOSE cu_loc3bc;
1397 END IF;
1398 END IF;
1399 END IF;
1400
1401 status_handler(l_return_status, x_return_status);
1402
1403 l_array.DELETE;
1404 RAISE exchttp;
1405 END IF;
1406
1407 IF l_return_status = fnd_api.g_ret_sts_error THEN
1408 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1409 msg := SUBSTRB(fnd_msg_pub.get(j, fnd_api.g_false),1,256);
1410 trace_handler(msg);
1411 END LOOP;
1412 -- J. del Callar: Re-initialize the stack for the next set.
1413 fnd_msg_pub.initialize;
1414 status_handler(l_return_status, x_return_status);
1415 END IF;
1416
1417 FOR j IN 1..l_array.COUNT LOOP
1418 -- J. del Callar: update the geometry and status of each of the
1419 -- location records.
1420 update_geo_location(
1421 p_location_id => l_array(j).location_id,
1422 p_geo => l_array(j).geometry,
1423 p_geo_status => l_array(j).geometry_status_code,
1424 x_count => l_nb_update,
1425 x_return_status => l_return_status,
1426 x_msg_count => x_msg_count,
1427 x_msg_data => x_msg_data
1428 );
1429 END LOOP;
1430
1431 status_handler(l_return_status, x_return_status);
1432 l_array.DELETE;
1433 cpt := 0;
1434 END IF;
1435
1436 -- J. del Callar: exit the loop if our update limit has been exceeded.
1437 IF p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update THEN
1438 time_put_line('Exiting due to update limit.');
1439 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1440 hz_utility_v2pub.debug(p_message=>'Exiting due to update limit.',
1441 p_prefix=>'WARNING',
1442 p_msg_level=>fnd_log.level_exception);
1443
1444 END IF;
1445 EXIT;
1446 END IF;
1447 END LOOP;
1448
1449 -- J. del Callar: commit every l_set_size records, and clear all arrays.
1450 COMMIT;
1451
1452 -- Fix bug 3612034, close cursor and then reopen it again due to
1453 -- snapshot too old problem
1454 fnd_file.put_line(fnd_file.log,'Process committed');
1455
1456 IF p_loc_type = 'P' THEN
1457 IF p_site_use_type IS NOT NULL AND p_incremental = 'N' THEN
1458 IF (p_country IS NULL) THEN
1459 CLOSE cu_loc1a;
1460 OPEN cu_loc1a(l_request_id);
1461 ELSE
1462 CLOSE cu_loc1ac;
1463 OPEN cu_loc1ac(l_request_id);
1464 END IF;
1465 ELSIF p_site_use_type IS NOT NULL AND p_incremental = 'Y' THEN
1466 IF (p_country IS NULL) THEN
1467 CLOSE cu_loc1b;
1468 OPEN cu_loc1b(l_request_id);
1469 ELSE
1470 CLOSE cu_loc1bc;
1471 OPEN cu_loc1bc(l_request_id);
1472 END IF;
1473 ELSIF p_site_use_type IS NULL AND p_incremental = 'N' THEN
1474 IF (p_country IS NULL) THEN
1475 CLOSE cu_loc2a;
1476 OPEN cu_loc2a(l_request_id);
1477 ELSE
1478 CLOSE cu_loc2ac;
1479 OPEN cu_loc2ac(l_request_id);
1480 END IF;
1481 ELSIF p_site_use_type IS NULL AND p_incremental = 'Y' THEN
1482 IF (p_country IS NULL) THEN
1483 CLOSE cu_loc2b;
1484 OPEN cu_loc2b(l_request_id);
1485 ELSE
1486 CLOSE cu_loc2bc;
1487 OPEN cu_loc2bc(l_request_id);
1488 END IF;
1489 END IF;
1490 ELSIF p_loc_type = 'S' THEN
1491 IF p_incremental = 'N' THEN
1492 IF (p_country IS NULL) THEN
1493 CLOSE cu_loc3a;
1494 OPEN cu_loc3a(l_request_id);
1495 ELSE
1496 CLOSE cu_loc3ac;
1497 OPEN cu_loc3ac(l_request_id);
1498 END IF;
1499 ELSIF p_incremental = 'Y' THEN
1500 IF (p_country IS NULL) THEN
1501 CLOSE cu_loc3b;
1502 OPEN cu_loc3b(l_request_id);
1503 ELSE
1504 CLOSE cu_loc3bc;
1505 OPEN cu_loc3bc(l_request_id);
1506 END IF;
1507 END IF;
1508 END IF;
1509
1510 l_location_ids.DELETE;
1511 l_address1s.DELETE;
1512 l_address2s.DELETE;
1513 l_address3s.DELETE;
1514 l_address4s.DELETE;
1515 l_cities.DELETE;
1516 l_postal_codes.DELETE;
1517 l_counties.DELETE;
1518 l_states.DELETE;
1519 l_provinces.DELETE;
1520 l_countries.DELETE;
1521 END LOOP;
1522
1523 -- J. del Callar: close the cursor if it has not been closed by the
1524 -- commit statement.
1525 IF p_loc_type = 'P' THEN
1526 IF p_site_use_type IS NOT NULL AND p_incremental = 'N' THEN
1527 -- J. del Callar: use cu_loc1a for non-null site uses
1528 -- and non-incremental mode.
1529 IF(p_country IS NULL) THEN
1530 IF cu_loc1a%ISOPEN THEN
1531 CLOSE cu_loc1a;
1532 END IF;
1533 ELSE
1534 IF cu_loc1ac%ISOPEN THEN
1535 CLOSE cu_loc1ac;
1536 END IF;
1537 END IF;
1538 ELSIF p_site_use_type IS NOT NULL AND p_incremental = 'Y' THEN
1539 -- J. del Callar: use cu_loc1b for non-null site uses
1540 -- and incremental mode.
1541 IF(p_country IS NULL) THEN
1542 IF cu_loc1b%ISOPEN THEN
1543 CLOSE cu_loc1b;
1544 END IF;
1545 ELSE
1546 IF cu_loc1bc%ISOPEN THEN
1547 CLOSE cu_loc1bc;
1548 END IF;
1549 END IF;
1550 ELSIF p_site_use_type IS NULL AND p_incremental = 'N' THEN
1551 -- J. del Callar: use cu_loc2a for null site uses
1552 -- and non-incremental mode.
1553 IF(p_country IS NULL) THEN
1554 IF cu_loc2a%ISOPEN THEN
1555 CLOSE cu_loc2a;
1556 END IF;
1557 ELSE
1558 IF cu_loc2ac%ISOPEN THEN
1559 CLOSE cu_loc2ac;
1560 END IF;
1561 END IF;
1562 ELSIF p_site_use_type IS NULL AND p_incremental = 'Y' THEN
1563 -- J. del Callar use cu_loc2b for null site uses
1564 -- and incremental mode.
1565 IF(p_country IS NULL) THEN
1566 IF cu_loc2b%ISOPEN THEN
1567 CLOSE cu_loc2b;
1568 END IF;
1569 ELSE
1570 IF cu_loc2bc%ISOPEN THEN
1571 CLOSE cu_loc2bc;
1572 END IF;
1573 END IF;
1574 END IF;
1575 ELSIF p_loc_type = 'S' THEN
1576 IF p_incremental = 'N' THEN
1577 -- swbhatna: use cu_loc3a for updating Standalone locations
1578 -- and in non-incremental mode.
1579 IF(p_country IS NULL) THEN
1580 IF cu_loc3a%ISOPEN THEN
1581 CLOSE cu_loc3a;
1582 END IF;
1583 ELSE
1584 IF cu_loc3ac%ISOPEN THEN
1585 CLOSE cu_loc3ac;
1586 END IF;
1587 END IF;
1588 ELSIF p_incremental = 'Y' THEN
1589 -- swbhatna: use cu_loc3b for updating Standalone locations
1590 -- and in incremental mode.
1591 IF(p_country IS NULL) THEN
1592 IF cu_loc3b%ISOPEN THEN
1593 CLOSE cu_loc3b;
1594 END IF;
1595 ELSE
1596 IF cu_loc3bc%ISOPEN THEN
1597 CLOSE cu_loc3bc;
1598 END IF;
1599 END IF;
1600 END IF;
1601 END IF;
1602
1603 -- J. del Callar, bug 2252141: changed to always print out NOCOPY the message
1604 -- stack.
1605 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1606 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256)
1607 || fnd_global.local_chr(10);
1608 trace_handler(msg);
1609 END LOOP;
1610 fnd_message.clear;
1611
1612 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1613 retcode := '1';
1614 -- J. del Callar: instruct user to look at log if warnings are found.
1615 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1616 fnd_file.put_line(fnd_file.output, errbuf);
1617 END IF;
1618
1619 -- J. del Callar: reflect successful program termination in output and
1620 -- log files.
1621 fnd_file.put_line(fnd_file.output,
1622 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS: '));
1623 fnd_file.put_line(fnd_file.output,
1624 fnd_message.get_string('FND',
1625 'CONC-CP SUCCESSFUL TERMINATION'));
1626 fnd_file.put_line(fnd_file.log,
1627 fnd_message.get_string('FND',
1628 'CONC-CP SUCCESSFUL TERMINATION'));
1629
1630 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1631 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (-)',
1632 p_prefix=>l_debug_prefix,
1633 p_msg_level=>fnd_log.level_procedure);
1634 END IF;
1635
1636 --disable_debug;
1637
1638 EXCEPTION
1639 WHEN expect_http_ad THEN
1640 fnd_message.set_name('AR','HZ_MISSING_HTTP_SITE');
1641 fnd_msg_pub.add;
1642 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1643 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
1644 fnd_global.local_chr(10);
1645 trace_handler(msg);
1646 END LOOP;
1647 fnd_message.clear;
1648 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1649 hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry (- expect_http_ad)',
1650 p_prefix=>'ERROR',
1651 p_msg_level=>fnd_log.level_error);
1652 END IF;
1653 --disable_debug;
1654 retcode := '2';
1655 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1656
1657 WHEN port_number THEN
1658 fnd_message.set_name('AR','HZ_PORT_NUMBER_EXPECTED');
1659 fnd_message.set_token('PORT', l_port);
1660 fnd_msg_pub.add;
1661 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1662 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
1663 fnd_global.local_chr(10);
1664 trace_handler(msg);
1665 END LOOP;
1666 fnd_message.clear;
1667 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1668 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- port_number)',
1669 p_prefix=>'ERROR',
1670 p_msg_level=>fnd_log.level_error);
1671 END IF;
1672 --disable_debug;
1673 retcode := '2';
1674 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1675
1676 WHEN nlsnumexp THEN
1677 fnd_message.set_name('AR','HZ_NUMERIC_CHAR_SET');
1678 fnd_msg_pub.add;
1679 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1680 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
1681 fnd_global.local_chr(10);
1682 trace_handler(msg);
1683 END LOOP;
1684 fnd_message.clear;
1685 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1686 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- nlsnumexp)',
1687 p_prefix=>'ERROR',
1688 p_msg_level=>fnd_log.level_error);
1689 END IF;
1690 --disable_debug;
1691 retcode := '2';
1692 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1693
1694 WHEN atleastonerow THEN
1695 fnd_message.set_name('AR','HZ_AT_LEAST_ONE_ROW');
1696 fnd_msg_pub.add;
1697 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1698 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
1699 fnd_global.local_chr(10);
1700 trace_handler(msg);
1701 END LOOP;
1702 fnd_message.clear;
1703 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1704 hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry (- atleastonerow)',
1705 p_prefix=>'ERROR',
1706 p_msg_level=>fnd_log.level_error);
1707 END IF;
1708 --disable_debug;
1709 retcode := '2';
1710 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1711
1712 WHEN morethanmaxrow THEN
1713 fnd_message.set_name('AR','HZ_MAX_BATCH_SIZE_EXCEEDED');
1714 fnd_message.set_token('MAX', hz_geocode_pkg.g_max_rows);
1715 fnd_msg_pub.add;
1716 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1717 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
1718 fnd_global.local_chr(10);
1719 trace_handler(msg);
1720 END LOOP;
1721 fnd_message.clear;
1722 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1726 END IF;
1723 hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry (- morethanmaxrow)',
1724 p_prefix=>'ERROR',
1725 p_msg_level=>fnd_log.level_error);
1727 --disable_debug;
1728 retcode := '2';
1729 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1730
1731 WHEN exchttp THEN
1732 FOR j IN 1..fnd_msg_pub.count_msg LOOP
1733 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
1734 fnd_global.local_chr(10);
1735 trace_handler(msg);
1736 END LOOP;
1737 fnd_message.clear;
1738 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1739 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- exchttp)',
1740 p_prefix=>'ERROR',
1741 p_msg_level=>fnd_log.level_error);
1742 END IF;
1743 --disable_debug;
1744 retcode := '2';
1745 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1746
1747 WHEN OTHERS THEN
1748 fnd_file.put_line(fnd_file.log, SQLERRM);
1749 msg := SQLERRM;
1750 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1751 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- others)',
1752 p_prefix=>'SQL ERROR',
1753 p_msg_level=>fnd_log.level_error);
1754 hz_utility_v2pub.debug(p_message=>'msg='||SUBSTRB(msg, 1, 250),
1755 p_prefix=>'SQL ERROR',
1756 p_msg_level=>fnd_log.level_error);
1757 END IF;
1758 --disable_debug;
1759 retcode := '2';
1760 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1761
1762 */
1763 NULL;
1764 END update_geometry;
1765
1766 --------------------------------------
1767 -- PRIVATE PROCEDURE generate_log_file
1768 -- DESCRIPTION
1769 -- Generates log file after update_geometry_all call from
1770 -- Spatial Information for Locations Batch Update Concurrent program
1771 -- MODIFICATION HISTORY
1772 -- 29-11-2010 Sudhir Gokavarapu Created - ER10211310
1773
1774 PROCEDURE generate_log_file
1775 ( P_LOC_TYPE IN VARCHAR2,
1776 P_SITE_USE_TYPE IN VARCHAR2,
1777 P_COUNTRY IN VARCHAR2,
1778 P_IDEN_ADDR_ONLY IN VARCHAR2,
1779 P_INCREMENTAL IN VARCHAR2,
1780 P_ALL_PARTIAL IN VARCHAR2,
1781 P_NB_ROW_UPDATE IN VARCHAR2,
1782 P_STATE IN VARCHAR2,
1783 P_PROVINCE IN VARCHAR2,
1784 P_CITY IN VARCHAR2,
1785 P_GEOMETRY_ACCURACY IN NUMBER,
1786 P_GEOMETRY_SOURCE IN VARCHAR2,
1787 p_party_site_status IN VARCHAR2,
1788 P_MC_1 IN NUMBER,
1789 P_MC_2 IN NUMBER,
1790 P_MC_3 IN NUMBER,
1791 P_MC_4 IN NUMBER,
1792 P_MC_10 IN NUMBER,
1793 P_MC_11 IN NUMBER,
1794 P_MC_0 IN NUMBER
1795
1796 ) IS
1797 l_total_rec_processed NUMBER := P_MC_1+P_MC_2+P_MC_3+P_MC_4+P_MC_10+P_MC_11+P_MC_0;
1798 l_loc_type VARCHAR2(100) := 'NULL ';
1799 l_site_use_type VARCHAR2(100) := 'NULL ';
1800 l_country VARCHAR2(100) := 'NULL ';
1801 l_geometry_source VARCHAR2(100) := 'NULL ';
1802 l_geometry_accuracy VARCHAR2(100) := 'NULL ';
1803 l_party_site_status VARCHAR2(100) := 'NULL ';
1804
1805 CURSOR c_accuracy_mapping IS
1806 SELECT lookup_code,description,decode(lookup_code,0,2,1)
1807 FROM AR_LOOKUPS
1808 WHERE LOOKUP_TYPE = 'HZ_SPATIAL_ACCURACY_MAPPING'
1809 ORDER BY decode(lookup_code,0,2,1),TO_NUMBER(lookup_code);
1810
1811 r_accuracy_mapping c_accuracy_mapping%ROWTYPE;
1812
1813 BEGIN
1814
1815 IF p_site_use_type IS NOT NULL THEN
1816 SELECT meaning
1817 INTO l_site_use_type
1818 FROM AR_LOOKUPS
1819 WHERE LOOKUP_TYPE = 'PARTY_SITE_USE_CODE'
1820 AND LOOKUP_CODE = p_site_use_type;
1821 END IF;
1822
1823 IF p_loc_type IS NOT NULL THEN
1824 SELECT meaning
1825 INTO l_loc_type
1826 FROM AR_LOOKUPS
1827 WHERE LOOKUP_TYPE = 'HZ_ELOCATION_TYPE'
1828 AND LOOKUP_CODE = p_loc_type;
1829 END IF;
1830
1831 IF p_country IS NOT NULL THEN
1832 SELECT meaning
1833 INTO l_country
1834 FROM AR_LOOKUPS
1835 WHERE LOOKUP_TYPE = 'HZ_SPATIAL_VALID_COUNTRIES'
1836 AND LOOKUP_CODE = p_country;
1837 END IF;
1838
1839 IF p_geometry_source IS NOT NULL THEN
1840 SELECT meaning
1841 INTO l_geometry_source
1842 FROM AR_LOOKUPS
1843 WHERE LOOKUP_TYPE = 'HZ_GEOMETRY_SOURCE'
1844 AND LOOKUP_CODE = p_geometry_source;
1845 END IF;
1846
1847 IF p_geometry_accuracy IS NOT NULL THEN
1848 SELECT meaning
1849 INTO l_geometry_accuracy
1850 FROM AR_LOOKUPS
1851 WHERE LOOKUP_TYPE = 'HZ_SPATIAL_ACCURACY_MAPPING'
1852 AND LOOKUP_CODE = p_geometry_accuracy;
1853 END IF;
1854
1855 IF p_party_site_status IS NOT NULL THEN
1856 SELECT meaning
1857 INTO l_party_site_status
1858 FROM AR_LOOKUPS
1859 WHERE LOOKUP_TYPE = 'HZ_LOC_STATUS'
1860 AND LOOKUP_CODE = p_party_site_status;
1861 END IF;
1862
1863 fnd_file.put_line(fnd_file.log,'*---------------------------------------------------------------------------*');
1864 fnd_file.put_line(fnd_file.log,'');
1865 fnd_file.put_line(fnd_file.log,'PARAMETERS ENTERED: ');
1866 fnd_file.put_line(fnd_file.log,'Location Type: '||l_loc_type);
1867 fnd_file.put_line(fnd_file.log,'Site Use: '||l_site_use_type);
1868 fnd_file.put_line(fnd_file.log,'Identifying Addresses Only: '||NVL(P_IDEN_ADDR_ONLY,'NULL'));
1869 fnd_file.put_line(fnd_file.log,'Update Records for: '||NVL(p_all_partial,'NULL'));
1873 fnd_file.put_line(fnd_file.log,'State: '||NVL(p_state,'NULL'));
1870 fnd_file.put_line(fnd_file.log,'Only Unretrieved Locations: '||NVL(p_incremental,'NULL'));
1871 fnd_file.put_line(fnd_file.log,'Country: '||l_country);
1872 fnd_file.put_line(fnd_file.log,'Number of Records in Subset: '||NVL(p_nb_row_update,'NULL'));
1874 fnd_file.put_line(fnd_file.log,'Province: '||NVL(p_province,'NULL'));
1875 fnd_file.put_line(fnd_file.log,'City: '||NVL(p_city,'NULL'));
1876 fnd_file.put_line(fnd_file.log,'Accuracy Level: '||l_geometry_accuracy);
1877 fnd_file.put_line(fnd_file.log,'Source: '||l_geometry_source);
1878 fnd_file.put_line(fnd_file.log,'Location Status: '||l_party_site_status);
1879 fnd_file.put_line(fnd_file.log,'');
1880 fnd_file.put_line(fnd_file.log,'*---------------------------------------------------------------------------*');
1881 fnd_file.put_line(fnd_file.log,'TOTAL RECORDS PROCESSED: '||l_total_rec_processed);
1882 fnd_file.put_line(fnd_file.log,'*---------------------------------------------------------------------------*');
1883 fnd_file.put_line(fnd_file.log,'');
1884 fnd_file.put_line(fnd_file.log,'Accuracy level Location Count');
1885 fnd_file.put_line(fnd_file.log,' 1 '||P_MC_1);
1886 fnd_file.put_line(fnd_file.log,' 2 '||P_MC_2);
1887 fnd_file.put_line(fnd_file.log,' 3 '||P_MC_3);
1888 fnd_file.put_line(fnd_file.log,' 4 '||P_MC_4);
1889 fnd_file.put_line(fnd_file.log,' 10 '||P_MC_10);
1890 fnd_file.put_line(fnd_file.log,' 11 '||P_MC_11);
1891 fnd_file.put_line(fnd_file.log,' 0 '||P_MC_0);
1892 fnd_file.put_line(fnd_file.log,'');
1893 fnd_file.put_line(fnd_file.output,'');
1894 fnd_file.put_line(fnd_file.log,'*---------------------------------------------------------------------------*');
1895 fnd_file.put_line(fnd_file.output,'*---------------------------------------------------------------------------*');
1896 fnd_file.put_line(fnd_file.log,'');
1897 fnd_file.put_line(fnd_file.output,'');
1898 open c_accuracy_mapping;
1899 loop
1900 fetch c_accuracy_mapping into r_accuracy_mapping;
1901 exit when c_accuracy_mapping%NOTFOUND;
1902 fnd_file.put_line(fnd_file.log,'ACCURACY LEVEL '||rpad(r_accuracy_mapping.lookup_code,2)||' - '||r_accuracy_mapping.description);
1903 fnd_file.put_line(fnd_file.output,'ACCURACY LEVEL '||rpad(r_accuracy_mapping.lookup_code,2)||' - '||r_accuracy_mapping.description);
1904 end loop;
1905 close c_accuracy_mapping;
1906 fnd_file.put_line(fnd_file.log,'');
1907 fnd_file.put_line(fnd_file.output,'');
1908 fnd_file.put_line(fnd_file.log,'*---------------------------------------------------------------------------*');
1909 fnd_file.put_line(fnd_file.output,'*---------------------------------------------------------------------------*');
1910
1911 END generate_log_file;
1912
1913 --------------------------------------
1914 -- PUBLIC PROCEDURE update_geometry_all
1915 -- DESCRIPTION
1916 -- Synchronized geometry in hz_locations with latitude,longitude and accuracy from
1917 -- Oracle's eLocation service.
1918 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1919 -- hz_utility_v2pub
1920 -- fnd_message
1921 -- fnd_msg_pub
1922 -- MODIFICATION HISTORY
1923 -- 29-11-2010 Sudhir Gokavarapu Created - ER10211310
1924 -- Bug12386319 default value of p_nb_row is changed to 10.
1925 -- Bug12386226 - When Match Count is zero then
1926 -- Geometry (NULL value) should be removed from HZ_LOCATIONS Update.
1927
1928 PROCEDURE update_geometry_all (
1929 errbuf OUT NOCOPY VARCHAR2,
1930 retcode OUT NOCOPY VARCHAR2,
1931 p_loc_type IN VARCHAR2 DEFAULT 'P',
1932 p_site_use_type IN VARCHAR2 DEFAULT NULL,
1933 p_country IN VARCHAR2 DEFAULT NULL,
1934 p_iden_addr_only IN VARCHAR2 DEFAULT 'N',
1935 p_incremental IN VARCHAR2 DEFAULT 'N',
1936 p_all_partial IN VARCHAR2 DEFAULT 'ALL',
1937 p_nb_row_update IN VARCHAR2 DEFAULT 'ALL',
1938 p_state IN VARCHAR2 DEFAULT NULL,
1939 p_province IN VARCHAR2 DEFAULT NULL,
1940 p_city IN VARCHAR2 DEFAULT NULL,
1941 p_geometry_accuracy IN VARCHAR2 DEFAULT NULL,
1942 p_geometry_source IN VARCHAR2 DEFAULT 'ELOCATION',
1943 p_party_site_status IN VARCHAR2 DEFAULT NULL,
1944 p_nb_row IN NUMBER DEFAULT 10,
1945 p_nb_try IN NUMBER DEFAULT 3
1946
1947 ) IS
1948 TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
1949 TYPE addresslist IS TABLE OF hz_locations.address1%TYPE;
1950 TYPE citylist IS TABLE OF hz_locations.city%TYPE;
1951 TYPE pcodelist IS TABLE OF hz_locations.postal_code%TYPE;
1952 TYPE statelist IS TABLE OF hz_locations.state%TYPE;
1953 TYPE countrylist IS TABLE OF hz_locations.country%TYPE;
1954 TYPE countylist IS TABLE OF hz_locations.county%TYPE;
1955 TYPE provincelist IS TABLE OF hz_locations.province%TYPE;
1956 l_location_ids locationlist;
1957 l_address1s addresslist;
1958 l_address2s addresslist;
1959 l_address3s addresslist;
1960 l_address4s addresslist;
1961 l_cities citylist;
1962 l_postal_codes pcodelist;
1963 l_states statelist;
1964 l_countries countrylist;
1965 l_counties countylist;
1966 l_provinces provincelist;
1967
1968 l_array hz_geocode_pkg.loc_array := hz_geocode_pkg.loc_array();
1969 --l_array geo_loc_array := geo_loc_array();
1970 l_rec hz_location_v2pub.location_rec_type;
1971 l_http_ad VARCHAR2(200);
1972 l_proxy VARCHAR2(100);
1973 l_port VARCHAR2(10);
1977 x_msg_data VARCHAR2(2000);
1974 l_port_num NUMBER;
1975 x_return_status VARCHAR2(10);
1976 x_msg_count NUMBER;
1978 cpt NUMBER := 0;
1979 cpt_update NUMBER := 0;
1980 l_nb_row_update NUMBER DEFAULT NULL;
1981 l_nb_update NUMBER;
1982 l_str_exe VARCHAR2(500);
1983 expect_http_ad EXCEPTION;
1984 exchttp EXCEPTION;
1985 port_number EXCEPTION;
1986 nlsnumexp EXCEPTION;
1987 morethanmaxrow EXCEPTION;
1988 atleastonerow EXCEPTION;
1989 msg VARCHAR2(2000);
1990 l_return_status VARCHAR2(10);
1991 l_set_size NUMBER;
1992 l_request_id NUMBER := hz_utility_v2pub.request_id;
1993 i NUMBER;
1994 l_nb_retries NUMBER := NVL(p_nb_try, 3);
1995 l_batch_size NUMBER := NVL(p_nb_row, hz_geocode_pkg.g_max_rows);
1996 l_retcode VARCHAR2(10);
1997 l_errbuf VARCHAR2(4000);
1998 l_proxy_var VARCHAR2(240);
1999 l_port_var VARCHAR2(240);
2000 l_debug_prefix VARCHAR2(30) := '';
2001
2002 TYPE LogGeoCurTyp IS REF CURSOR;
2003 l_Loc_geo_cursor LogGeoCurTyp;
2004
2005 l_qry_str VARCHAR2(1000);
2006 l_mc_1 NUMBER := 0;
2007 l_mc_2 NUMBER := 0;
2008 l_mc_3 NUMBER := 0;
2009 l_mc_4 NUMBER := 0;
2010 l_mc_10 NUMBER := 0;
2011 l_mc_11 NUMBER := 0;
2012 l_mc_0 NUMBER := 0;
2013 l_processed_rec_cnt NUMBER:=0;
2014 l_cnt NUMBER := 0;
2015 l_latitude_str VARCHAR2(50);
2016 l_longitude_str VARCHAR2(50);
2017 l_rec_cnt NUMBER := 0;
2018
2019 BEGIN
2020
2021
2022 --enable_debug;
2023
2024 l_nb_update := 0;
2025 l_set_size := l_batch_size * g_sets_per_commit;
2026
2027 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2028 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (+)',
2029 p_prefix=>l_debug_prefix,
2030 p_msg_level=>fnd_log.level_procedure);
2031 END IF;
2032
2033 x_return_status := fnd_api.g_ret_sts_success;
2034 l_return_status := fnd_api.g_ret_sts_success;
2035 fnd_msg_pub.initialize;
2036
2037 retcode := '0';
2038 fnd_file.put_line(fnd_file.log,
2039 fnd_message.get_string('FND',
2040 'CONC-START PROGRAM EXECUTION'));
2041 fnd_file.put_line(fnd_file.log, '');
2042
2043 fnd_file.put_line(fnd_file.output,
2044 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS: '));
2045 fnd_file.put_line(fnd_file.output,
2046 fnd_message.get_string('FND',
2047 'CONC-START PROGRAM EXECUTION'));
2048
2049 IF hz_geocode_pkg.is_nls_num_char_pt_com <> 'Y' THEN
2050 l_str_exe := 'ALTER SESSION SET nls_numeric_characters = ''.,''';
2051 EXECUTE IMMEDIATE l_str_exe;
2052 END IF;
2053
2054 IF p_all_partial <> 'ALL' THEN
2055 IF p_nb_row_update IS NULL OR p_nb_row_update = 'ALL' THEN
2056 l_nb_row_update := 1000;
2057 ELSE
2058 l_nb_row_update := TO_NUMBER(p_nb_row_update);
2059 END IF;
2060 IF l_nb_row_update IS NULL OR l_nb_row_update <= 0 THEN
2061 time_put_line('At least one row error.');
2062 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2063 hz_utility_v2pub.debug(p_message=>'At least one row error.',
2064 p_prefix=>'ERROR',
2065 p_msg_level=>fnd_log.level_error);
2066 END IF;
2067 RAISE atleastonerow;
2068 END IF;
2069 END IF;
2070
2071 IF l_batch_size > hz_geocode_pkg.g_max_rows THEN
2072 RAISE morethanmaxrow;
2073 END IF;
2074
2075 -- Get the website we're supposed to access for geospatial information.
2076 fnd_profile.get('HZ_GEOCODE_WEBSITE', l_http_ad);
2077 IF l_http_ad IS NULL THEN
2078 time_put_line('HTTP address missing');
2079 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2080 hz_utility_v2pub.debug(p_message=>'HTTP address missing',
2081 p_prefix=>'ERROR',
2082 p_msg_level=>fnd_log.level_error);
2083 END IF;
2084 RAISE expect_http_ad;
2085 END IF;
2086
2087
2088 -- Only get the proxy server if we need it - check the proxy bypass list.
2089 IF hz_geocode_pkg.in_bypass_list(
2090 l_http_ad,
2091 fnd_profile.value('WEB_PROXY_BYPASS_DOMAINS')
2092 )
2093 THEN
2094 -- site is in the bypass list.
2095 l_proxy_var := 'Null proxy';
2096 l_port_var := 'Null port';
2097 l_proxy := NULL;
2098 l_port := NULL;
2099 ELSE
2100 -- site is not in the bypass list.
2101 -- First, attempt to get proxy value from FND. If the proxy name is not
2102 -- found, try the TCA values regardless of whether the port is found.
2103 l_proxy_var := 'WEB_PROXY_HOST';
2104 l_port_var := 'WEB_PROXY_PORT';
2105 l_proxy := fnd_profile.value(l_proxy_var);
2106 l_port := fnd_profile.value(l_port_var);
2107 END IF;
2108
2109 -- log the profile options that are being used to run this program.
2110 fnd_file.put_line(fnd_file.log,
2111 fnd_message.get_string('FND', 'PROFILES-VALUES'));
2112 fnd_file.put_line(fnd_file.log, 'HZ_GEOCODE_WEBSITE: ' || l_http_ad);
2113 fnd_file.put_line(fnd_file.log, l_proxy_var || ': ' || l_proxy);
2114 fnd_file.put_line(fnd_file.log, l_port_var || ': ' || l_port);
2115 fnd_file.put_line(fnd_file.log, '');
2116
2120 fnd_file.put_line(fnd_file.output, 'HZ_GEOCODE_WEBSITE: ' || l_http_ad);
2117 -- repeat in the output file.
2118 fnd_file.put_line(fnd_file.output,
2119 fnd_message.get_string('FND', 'PROFILES-VALUES'));
2121 fnd_file.put_line(fnd_file.output, l_proxy_var || ': ' || l_proxy);
2122 fnd_file.put_line(fnd_file.output, l_port_var || ': ' || l_port);
2123 fnd_file.put_line(fnd_file.output, '');
2124
2125 -- repeat in debug output.
2126 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2127 hz_utility_v2pub.debug(p_message=>'HZ_GEOCODE_WEBSITE: ' || l_http_ad||' '||l_proxy_var || ': ' || l_proxy||' '||
2128 l_port_var || ': ' || l_port,
2129 p_prefix =>l_debug_prefix,
2130 p_msg_level=>fnd_log.level_statement);
2131 END IF;
2132
2133 IF l_port IS NOT NULL THEN
2134 -- J. del Callar: set the port number and handle non-numeric values
2135 BEGIN
2136 l_port_num := TO_NUMBER(l_port);
2137 EXCEPTION
2138 WHEN OTHERS THEN
2139 RAISE port_number;
2140 END;
2141 ELSE
2142 l_port_num := NULL;
2143 END IF;
2144
2145 --Location Type is having invalid value.
2146 IF p_loc_type IS NOT NULL AND p_loc_type <> 'P' AND p_loc_type <> 'S' THEN
2147 l_return_status := fnd_api.g_ret_sts_unexp_error;
2148 time_put_line('Unexpected mode encountered');
2149 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2150 hz_utility_v2pub.debug(p_message=>'Unexpected mode encountered Invalid Location Type Value',
2151 p_prefix=>'ERROR',
2152 p_msg_level=>fnd_log.level_error);
2153 END IF;
2154 END IF;
2155 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2156 hz_utility_v2pub.debug(p_message=>'p_nb_row :'||p_nb_row||' p_nb_try :'||p_nb_try||' p_nb_row_update :'||p_nb_row_update||' p_country :'||p_country
2157 ||' p_state :'||p_state||' p_province :'||p_province||' p_city :'||p_city||' p_geometry_accuracy :'||p_geometry_accuracy
2158 ||' p_geometry_source :'||p_geometry_source||' p_incremental :'||p_incremental
2159 ||' p_loc_type :'||p_loc_type||' p_site_use_type :'||p_site_use_type,
2160 p_prefix=>l_debug_prefix,
2161 p_msg_level=>fnd_log.level_procedure);
2162 END IF;
2163
2164 -- Dynamic SQL statement
2165 l_qry_str := 'SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4, hl.city, ';
2166 l_qry_str := l_qry_str ||'hl.postal_code, hl.state, hl.country, hl.county, hl.province ';
2167 l_qry_str := l_qry_str ||'FROM hz_locations hl ,ar_lookups arl ';
2168 l_qry_str := l_qry_str ||'WHERE arl.lookup_type = '|| '''' ||'HZ_SPATIAL_VALID_COUNTRIES'|| '''' ;
2169 l_qry_str := l_qry_str ||' AND hl.country = arl.lookup_code ';
2170
2171
2172 IF p_country IS NOT NULL THEN
2173 l_qry_str := l_qry_str ||' AND hl.country = '||''''||p_country||'''' ;
2174 END IF;
2175
2176
2177 IF p_state IS NOT NULL THEN
2178 l_qry_str := l_qry_str ||' AND UPPER(hl.state) = UPPER('||''''||p_state||''''||')' ;
2179 END IF;
2180
2181
2182 IF p_province IS NOT NULL THEN
2183 l_qry_str := l_qry_str ||' AND UPPER(hl.province) = '||''''||upper(p_province)||'''' ;
2184 END IF;
2185
2186
2187 IF p_city IS NOT NULL THEN
2188 l_qry_str := l_qry_str ||' AND UPPER(hl.city) = UPPER('||''''||p_city||''''||')' ;
2189 END IF;
2190
2191 IF p_geometry_accuracy IS NOT NULL THEN
2192 l_qry_str := l_qry_str ||' AND hl.geometry_accuracy = '||p_geometry_accuracy ;
2193 END IF;
2194
2195 IF p_geometry_source IS NOT NULL AND p_geometry_source <> 'ANY_SOURCE' THEN
2196 -- l_qry_str := l_qry_str ||' AND hl.geometry_source = '||''''||p_geometry_source||'''';
2197 l_qry_str := l_qry_str ||' AND NVL(hl.geometry_source,'||''''||'ELOCATION'||''''||') = '||''''||p_geometry_source||'''';
2198 END IF;
2199
2200 IF p_incremental = 'Y' THEN
2201 l_qry_str := l_qry_str ||' AND (hl.geometry_status_code = '||''''||'DIRTY'||''''||' OR hl.geometry_status_code IS NULL)' ;
2202 END IF;
2203
2204 IF p_loc_type <> 'P' THEN
2205 l_qry_str := l_qry_str ||' AND NOT EXISTS (SELECT 1 FROM hz_party_sites hps WHERE hps.location_id = hl.location_id ) ' ;
2206 END IF;
2207
2208 IF p_loc_type = 'P' AND p_site_use_type IS NULL THEN
2209 l_qry_str := l_qry_str ||' AND EXISTS ( SELECT 1 FROM hz_party_sites hps WHERE hps.location_id = hl.location_id ' ;
2210 l_qry_str := l_qry_str ||' AND hps.identifying_address_flag = DECODE('||''''||p_iden_addr_only||''''||', '||''''||'Y'||'''';
2211 l_qry_str := l_qry_str ||','||''''|| 'Y'||''''||', hps.identifying_address_flag) ' ;
2212 l_qry_str := l_qry_str ||' AND hps.status = DECODE('||''''||p_party_site_status||''''||','||''''||'ALL'||'''';
2213 l_qry_str := l_qry_str ||',hps.status,NULL,hps.status,'||''''||p_party_site_status||''''||')) ';
2214 END IF;
2215
2216
2217 IF p_loc_type = 'P' AND p_site_use_type IS NOT NULL THEN
2218 l_qry_str := l_qry_str ||' AND EXISTS ( SELECT 1 FROM hz_party_sites hps,hz_party_site_uses hpsu WHERE hps.location_id = hl.location_id ' ;
2219 l_qry_str := l_qry_str ||' AND hps.identifying_address_flag = DECODE('||''''||p_iden_addr_only||''''||', '||''''||'Y'||'''';
2220 l_qry_str := l_qry_str ||','||''''|| 'Y'||''''||', hps.identifying_address_flag) ' ;
2221 l_qry_str := l_qry_str ||' AND hps.status = DECODE('||''''||p_party_site_status||''''||','||''''||'ALL'||'''';
2222 l_qry_str := l_qry_str ||',hps.status,NULL,hps.status,'||''''||p_party_site_status||''''||') ';
2223 l_qry_str := l_qry_str ||' AND hpsu.party_site_id = hps.party_site_id ';
2227
2224 l_qry_str := l_qry_str ||' AND hpsu.site_use_type = '||''''||p_site_use_type||''''||')' ;
2225
2226 END IF;
2228 l_qry_str := l_qry_str ||' ORDER BY hl.location_id ';
2229
2230 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2231 hz_utility_v2pub.debug(p_message=>'Dynamic Query 1 :'||l_qry_str,
2232 p_prefix=>l_debug_prefix,
2233 p_msg_level=>fnd_log.level_procedure);
2234 END IF;
2235 --fnd_file.put_line(fnd_file.output,'Query '||l_qry_str);
2236 --fnd_file.put_line(fnd_file.log,'Query '||l_qry_str);
2237
2238 -- Open cursor
2239 OPEN l_Loc_geo_cursor FOR l_qry_str;
2240 LOOP
2241
2242 -- Fetch rows from result set one at a time:
2243 FETCH l_Loc_geo_cursor BULK COLLECT
2244 INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
2245 l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
2246 LIMIT l_set_size;
2247 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2248 hz_utility_v2pub.debug(p_message=>'Record Count ' || l_location_ids.COUNT,
2249 p_prefix =>l_debug_prefix,
2250 p_msg_level=>fnd_log.level_statement);
2251 END IF;
2252
2253 IF l_rec_cnt = 0 THEN
2254 l_rec_cnt := l_location_ids.COUNT;
2255 END IF;
2256
2257 -- Exit the loop if no records were fetched the first time
2258 IF l_rec_cnt = 0 THEN
2259 time_put_line('Exiting because Record Count is ZERO');
2260 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2261 hz_utility_v2pub.debug(p_message=>'Exiting because COUNT=0',
2262 p_prefix=>'WARNING',
2263 p_msg_level=>fnd_log.level_exception);
2264 END IF;
2265 END IF;
2266
2267 -- Exit the loop if no records were fetched
2268 IF l_location_ids.COUNT = 0 THEN
2269 EXIT;
2270 END IF;
2271
2272 -- Exit the loop if our update limit has been exceeded.
2273 IF p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update THEN
2274 time_put_line('Exiting because partial=' || p_all_partial);
2275 time_put_line('cpt_update=' || cpt_update);
2276 time_put_line('nb_row_update=' || l_nb_row_update);
2277 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2278 hz_utility_v2pub.debug(p_message=>'Exiting because partial=' || p_all_partial||' cpt_update=' || cpt_update
2279 ||' nb_row_update=' || l_nb_row_update,
2280 p_prefix=>'WARNING',
2281 p_msg_level=>fnd_log.level_exception);
2282 END IF;
2283 EXIT;
2284 END IF;
2285
2286 -- Main loop: process up to l_set_size records.
2287 FOR i IN l_location_ids.first..l_location_ids.last LOOP
2288 cpt_update := cpt_update + 1;
2289
2290 -- J. del Callar: copy the cursor values into a new location array rec
2291 l_array.EXTEND;
2292 cpt := cpt + 1;
2293 l_array(cpt).location_id := l_location_ids(i);
2294 l_array(cpt).address1 := l_address1s(i);
2295 l_array(cpt).address2 := l_address2s(i);
2296 l_array(cpt).address3 := l_address3s(i);
2297 l_array(cpt).address4 := l_address4s(i);
2298 l_array(cpt).city := l_cities(i);
2299 l_array(cpt).postal_code := l_postal_codes(i);
2300 l_array(cpt).state := l_states(i);
2301 l_array(cpt).country := l_countries(i);
2302 l_array(cpt).province := l_provinces(i);
2303 l_array(cpt).county := l_counties(i);
2304 --Commented as log file format is changed.
2305 -- fnd_file.put_line(fnd_file.log,'Processing location '||l_array(cpt).location_id);
2306 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2307 hz_utility_v2pub.debug(p_message=>'Processing location ' || l_array(cpt).location_id,
2308 p_prefix =>l_debug_prefix,
2309 p_msg_level=>fnd_log.level_statement);
2310 END IF;
2311
2312 -- execute the synchronization routine every l_batch_size, or
2313 -- if we have reached the last record to be updated, or
2314 -- if we have exceeded our update limit.
2315 IF cpt >= l_batch_size
2316 OR i = l_location_ids.last
2317 OR (p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update)
2318 THEN
2319 -- Process the records in the array.
2320 hz_geocode_pkg.get_spatial_coords(
2321 p_loc_array => l_array,
2322 p_name => NULL,
2323 p_http_ad => l_http_ad,
2324 p_proxy => l_proxy,
2325 p_port => l_port,
2326 p_retry => l_nb_retries,
2327 x_return_status => l_return_status,
2328 x_msg_count => x_msg_count,
2329 x_msg_data => x_msg_data
2330 );
2331 -- When Unexpected Error Occurs
2332 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2333 time_put_line('Unexpected error encountered');
2334 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2335 hz_utility_v2pub.debug(p_message=>'Unexpected error encountered',
2336 p_prefix=>'ERROR',
2337 p_msg_level=>fnd_log.level_error);
2338 END IF;
2339 -- Close cursor:
2340 CLOSE l_Loc_geo_cursor;
2341 status_handler(l_return_status, x_return_status);
2342
2343 l_array.DELETE;
2344 RAISE exchttp;
2345 END IF;
2346
2350 trace_handler(msg);
2347 IF l_return_status = fnd_api.g_ret_sts_error THEN
2348 FOR j IN 1..fnd_msg_pub.count_msg LOOP
2349 msg := SUBSTRB(fnd_msg_pub.get(j, fnd_api.g_false),1,256);
2351 END LOOP;
2352 -- J. del Callar: Re-initialize the stack for the next set.
2353 fnd_msg_pub.initialize;
2354 status_handler(l_return_status, x_return_status);
2355 END IF;
2356
2357 FOR j IN 1..l_array.COUNT LOOP
2358 -- Update the geometry and status of each of the
2359 -- location records.
2360 /* update_geo_location(
2361 p_location_id => l_array(j).location_id,
2362 p_geo => l_array(j).geometry,
2363 p_geo_status => l_array(j).geometry_status_code,
2364 x_count => l_nb_update,
2365 x_return_status => l_return_status,
2366 x_msg_count => x_msg_count,
2367 x_msg_data => x_msg_data
2368 );
2369 */
2370 -- Instead of calling Table Handler changed to direct update statement.
2371 IF l_array(j).geometry_accuracy = 0 THEN
2372 UPDATE HZ_LOCATIONS
2373 SET geometry = NULL,
2374 geometry_status_code = l_array(j).geometry_status_code,
2375 geometry_source = 'ELOCATION',
2376 geometry_accuracy = l_array(j).geometry_accuracy,
2377 last_update_date = hz_utility_v2pub.last_update_date,
2378 last_updated_by = hz_utility_v2pub.last_updated_by,
2379 last_update_login = hz_utility_v2pub.last_update_login,
2380 request_id = l_request_id,
2381 program_application_id = hz_utility_v2pub.program_application_id,
2382 program_id = hz_utility_v2pub.program_id,
2383 program_update_date = hz_utility_v2pub.program_update_date
2384 WHERE location_id = l_array(j).location_id;
2385 ELSE
2386 UPDATE HZ_LOCATIONS
2387 SET geometry = l_array(j).geometry,
2388 geometry_status_code = l_array(j).geometry_status_code,
2389 geometry_source = 'ELOCATION',
2390 geometry_accuracy = l_array(j).geometry_accuracy,
2391 last_update_date = hz_utility_v2pub.last_update_date,
2392 last_updated_by = hz_utility_v2pub.last_updated_by,
2393 last_update_login = hz_utility_v2pub.last_update_login,
2394 request_id = l_request_id,
2395 program_application_id = hz_utility_v2pub.program_application_id,
2396 program_id = hz_utility_v2pub.program_id,
2397 program_update_date = hz_utility_v2pub.program_update_date
2398 WHERE location_id = l_array(j).location_id;
2399 END IF;
2400 --Increment Accuracy count varaibles these values will be used
2401 --in Log file summary generation.
2402 CASE l_array(j).geometry_accuracy
2403 WHEN 1 THEN l_mc_1 := l_mc_1 + 1;
2404 WHEN 2 THEN l_mc_2 := l_mc_2 + 1;
2405 WHEN 3 THEN l_mc_3 := l_mc_3 + 1;
2406 WHEN 4 THEN l_mc_4 := l_mc_4 + 1;
2407 WHEN 10 THEN l_mc_10 := l_mc_10 + 1;
2408 WHEN 11 THEN l_mc_11 := l_mc_11 + 1;
2409 WHEN 0 THEN l_mc_0 := l_mc_0 + 1;
2410 ELSE NULL;
2411 END CASE;
2412
2413 IF l_array(j).geometry.SDO_POINT.Y <> 0 THEN
2414 l_latitude_str := ' Latitude: '||l_array(j).geometry.SDO_POINT.Y;
2415 ELSE
2416 l_latitude_str := '';
2417 END IF;
2418 IF l_array(j).geometry.SDO_POINT.x <> 0 THEN
2419 l_longitude_str := ' Longitude: '||l_array(j).geometry.SDO_POINT.x;
2420 ELSE
2421 l_longitude_str := '';
2422 END IF;
2423
2424
2425 --Bug12377426
2426 --'Geometry Accuracy' changed to 'Accuracy Level'
2427 IF l_processed_rec_cnt <= 1000 THEN
2428 fnd_file.put_line(fnd_file.output, 'Processed location Id: '||l_array(j).location_id
2429 ||l_longitude_str ||l_latitude_str
2430 ||' Accuracy Level: '||l_array(j).geometry_accuracy
2431 --||' Status: '||l_array(j).geometry_status_code
2432 );
2433 l_processed_rec_cnt := l_processed_rec_cnt + 1;
2434 ELSIF l_processed_rec_cnt = 1001 THEN
2435 fnd_file.put_line(fnd_file.output,'Output is generated for only first 1000 processed records.');
2436 l_processed_rec_cnt := l_processed_rec_cnt + 1;
2437 END IF;
2438 END LOOP;
2439 status_handler(l_return_status, x_return_status);
2440 l_array.DELETE;
2441 cpt := 0;
2442 END IF;
2443
2444 -- Exit the loop if our update limit has been exceeded.
2445 IF p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update THEN
2446 time_put_line('Exiting due to update limit.');
2447 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2448 hz_utility_v2pub.debug(p_message=>'Exiting due to update limit.',
2449 p_prefix=>'WARNING',
2450 p_msg_level=>fnd_log.level_exception);
2451
2452 END IF;
2453 EXIT;
2454 END IF;
2455 END LOOP;
2456
2457
2458 COMMIT;
2459
2460 l_location_ids.DELETE;
2461 l_address1s.DELETE;
2462 l_address2s.DELETE;
2463 l_address3s.DELETE;
2464 l_address4s.DELETE;
2465 l_cities.DELETE;
2466 l_postal_codes.DELETE;
2467 l_counties.DELETE;
2468 l_states.DELETE;
2469 l_provinces.DELETE;
2473 l_mc_4 ||' 10-'||l_mc_10 ||' 11-'||l_mc_11 ||' 0-'||l_mc_0,
2470 l_countries.DELETE;
2471 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2472 hz_utility_v2pub.debug(p_message=>'Processed Record Count Accuracy 1-'||l_mc_1||' 2-'||l_mc_2||' 3-'||l_mc_3 ||' 4-'||
2474 p_prefix=>l_debug_prefix,
2475 p_msg_level=>fnd_log.level_exception);
2476
2477 END IF;
2478
2479 END LOOP;
2480
2481 COMMIT;
2482 --fnd_file.put_line(fnd_file.log,'Process committed');
2483 -- Close cursor:
2484 IF l_Loc_geo_cursor%ISOPEN THEN
2485 CLOSE l_Loc_geo_cursor;
2486 END IF;
2487
2488 --Generating log file
2489 generate_log_file
2490 (p_loc_type => p_loc_type
2491 ,p_site_use_type => p_site_use_type
2492 ,p_country => p_country
2493 ,p_iden_addr_only => p_iden_addr_only
2494 ,p_incremental => p_incremental
2495 ,p_all_partial => p_all_partial
2496 ,p_nb_row_update => p_nb_row_update
2497 ,p_state => p_state
2498 ,p_province => p_province
2499 ,p_city => p_city
2500 ,p_geometry_accuracy => p_geometry_accuracy
2501 ,p_geometry_source => p_geometry_source
2502 ,p_party_site_status => p_party_site_status
2503 ,p_mc_1 => l_mc_1
2504 ,p_mc_2 => l_mc_2
2505 ,p_mc_3 => l_mc_3
2506 ,p_mc_4 => l_mc_4
2507 ,p_mc_10 => l_mc_10
2508 ,p_mc_11 => l_mc_11
2509 ,p_mc_0 => l_mc_0
2510 );
2511 /* Commented in part of ER#10211310
2512 --bug 2252141: changed to always print out NOCOPY the message
2513 -- stack.
2514 FOR j IN 1..fnd_msg_pub.count_msg LOOP
2515 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256)
2516 || fnd_global.local_chr(10);
2517 trace_handler(msg);
2518 END LOOP;
2519 fnd_message.clear;
2520 */
2521 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2522 retcode := '1';
2523 -- Instruct user to look at log if warnings are found.
2524 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2525 fnd_file.put_line(fnd_file.output, errbuf);
2526 END IF;
2527
2528 -- Reflect successful program termination in output and
2529 -- log files.
2530 fnd_file.put_line(fnd_file.output,
2531 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS: '));
2532 fnd_file.put_line(fnd_file.output,
2533 fnd_message.get_string('FND',
2534 'CONC-CP SUCCESSFUL TERMINATION'));
2535 fnd_file.put_line(fnd_file.log,
2536 fnd_message.get_string('FND',
2537 'CONC-CP SUCCESSFUL TERMINATION'));
2538
2539 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2540 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (-)',
2541 p_prefix=>l_debug_prefix,
2542 p_msg_level=>fnd_log.level_procedure);
2543 END IF;
2544
2545 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2546 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (-)',
2547 p_prefix=>l_debug_prefix,
2548 p_msg_level=>fnd_log.level_procedure);
2549 END IF;
2550
2551 EXCEPTION
2552 WHEN expect_http_ad THEN
2553 fnd_message.set_name('AR','HZ_MISSING_HTTP_SITE');
2554 fnd_msg_pub.add;
2555 FOR j IN 1..fnd_msg_pub.count_msg LOOP
2556 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
2557 fnd_global.local_chr(10);
2558 trace_handler(msg);
2559 END LOOP;
2560 fnd_message.clear;
2561 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2562 hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry_all (- expect_http_ad)',
2563 p_prefix=>'ERROR',
2564 p_msg_level=>fnd_log.level_error);
2565 END IF;
2566 --disable_debug;
2567 retcode := '2';
2568 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2569
2570 WHEN port_number THEN
2571 fnd_message.set_name('AR','HZ_PORT_NUMBER_EXPECTED');
2572 fnd_message.set_token('PORT', l_port);
2573 fnd_msg_pub.add;
2574 FOR j IN 1..fnd_msg_pub.count_msg LOOP
2575 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
2576 fnd_global.local_chr(10);
2577 trace_handler(msg);
2578 END LOOP;
2579 fnd_message.clear;
2580 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2581 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (- port_number)',
2582 p_prefix=>'ERROR',
2583 p_msg_level=>fnd_log.level_error);
2584 END IF;
2585 --disable_debug;
2586 retcode := '2';
2587 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2588
2589 WHEN nlsnumexp THEN
2590 fnd_message.set_name('AR','HZ_NUMERIC_CHAR_SET');
2591 fnd_msg_pub.add;
2592 FOR j IN 1..fnd_msg_pub.count_msg LOOP
2593 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
2594 fnd_global.local_chr(10);
2595 trace_handler(msg);
2596 END LOOP;
2597 fnd_message.clear;
2598 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2599 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (- nlsnumexp)',
2600 p_prefix=>'ERROR',
2601 p_msg_level=>fnd_log.level_error);
2602 END IF;
2603 --disable_debug;
2604 retcode := '2';
2605 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2606
2607 WHEN atleastonerow THEN
2608 fnd_message.set_name('AR','HZ_AT_LEAST_ONE_ROW');
2609 fnd_msg_pub.add;
2610 FOR j IN 1..fnd_msg_pub.count_msg LOOP
2611 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
2612 fnd_global.local_chr(10);
2613 trace_handler(msg);
2614 END LOOP;
2615 fnd_message.clear;
2616 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2617 hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry_all (- atleastonerow)',
2618 p_prefix=>'ERROR',
2619 p_msg_level=>fnd_log.level_error);
2620 END IF;
2621 --disable_debug;
2622 retcode := '2';
2623 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2624
2625 WHEN morethanmaxrow THEN
2626 fnd_message.set_name('AR','HZ_MAX_BATCH_SIZE_EXCEEDED');
2627 fnd_message.set_token('MAX', hz_geocode_pkg.g_max_rows);
2628 fnd_msg_pub.add;
2629 FOR j IN 1..fnd_msg_pub.count_msg LOOP
2630 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
2631 fnd_global.local_chr(10);
2632 trace_handler(msg);
2633 END LOOP;
2634 fnd_message.clear;
2635 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2636 hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry_all (- morethanmaxrow)',
2637 p_prefix=>'ERROR',
2638 p_msg_level=>fnd_log.level_error);
2639 END IF;
2640 --disable_debug;
2641 retcode := '2';
2642 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2643
2644 WHEN exchttp THEN
2645 FOR j IN 1..fnd_msg_pub.count_msg LOOP
2646 msg := SUBSTRB(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,256) ||
2647 fnd_global.local_chr(10);
2648 trace_handler(msg);
2649 END LOOP;
2650 fnd_message.clear;
2651 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2652 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (- exchttp)',
2653 p_prefix=>'ERROR',
2654 p_msg_level=>fnd_log.level_error);
2655 END IF;
2656 --disable_debug;
2657 retcode := '2';
2658 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2659
2660 WHEN OTHERS THEN
2661 fnd_file.put_line(fnd_file.log, SQLERRM);
2662 msg := SQLERRM;
2663 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2664 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (- others)',
2665 p_prefix=>'SQL ERROR',
2666 p_msg_level=>fnd_log.level_error);
2667 hz_utility_v2pub.debug(p_message=>'msg='||SUBSTRB(msg, 1, 250),
2668 p_prefix=>'SQL ERROR',
2669 p_msg_level=>fnd_log.level_error);
2670 END IF;
2671 --disable_debug;
2672 retcode := '2';
2673 errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2674
2675 END update_geometry_all;
2676
2677
2678 --------------------------------------
2679 -- PUBLIC PROCEDURE rebuild_location_index
2680 -- DESCRIPTION
2681 -- Rebuilds the spatial index on HZ_LOCATIONS.GEOMETRY. Rebuilding the
2682 -- spatial index is required so that the index performs adequately and
2683 -- that queries can accurately extract the spatial data.
2684 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2685 -- hz_utility_v2pub
2686 -- fnd_message
2687 -- fnd_msg_pub
2688 -- ARGUMENTS
2689 -- IN:
2690 -- p_concurrent_mode Set to 'Y' if the rebuild is running
2691 -- as its own concurrent program. Set to
2692 -- 'N' to indicate that the rebuild was
2693 -- called from another PL/SQL program.
2694 -- OUT:
2695 -- errbuf Standard AOL concurrent program error
2696 -- buffer.
2697 -- retcode Standard AOL concurrent program return
2698 -- code. If the rebuild is not being run
2699 -- independently, the calling program
2700 -- should check the value of this return.
2701 -- MODIFICATION HISTORY
2702 -- 03-20-2002 J. del Callar Created.
2703 -- 24-SEP-02 P.Suresh Bug No : 2685781. Added code to
2704 -- recreate the spatial index.
2705 -- 15-SEP-04 Arnold Ng Bug No : 3872778. Put enable policy
2706 -- function at exception block.
2707 -- 11-NOV-2008 Nishant Singhai Bug 7262437 : changed insert statement
2708 -- from view user_sdo_geom_metadata to
2709 -- table MDSYS.SDO_GEOM_METADATA_TABLE
2710 -- in insert proper table owner for HZ_LOCATIONS
2711 -- when setup data is created from conc. program.
2712 --------------------------------------
2713 PROCEDURE rebuild_location_index (
2714 errbuf OUT NOCOPY VARCHAR2,
2715 retcode OUT NOCOPY VARCHAR2,
2716 p_concurrent_mode IN VARCHAR2 DEFAULT 'Y'
2717 ) IS
2718 -- Bug 12916969 (remove hard coded schema name)
2719 /* l_rebuild_string VARCHAR2(100) := 'ALTER INDEX ' || g_index_owner ||
2720 '.' || g_index_name || ' REBUILD ' ||
2721 'PARAMETERS(''sdo_commit_interval=' ||
2722 g_commit_interval || ''')';
2723 */
2724 l_rebuild_string VARCHAR2(100) ;
2725
2726 CURSOR c_index (x_index_owner IN VARCHAR2) IS
2727 SELECT status, domidx_opstatus
2728 FROM sys.all_indexes
2729 WHERE index_name = 'HZ_LOCATIONS_N15' and owner = x_index_owner;
2730 /* Fix perf bug 4956727
2731 CURSOR c_policies IS
2732 SELECT object_owner, object_name, policy_name
2733 FROM sys.all_policies
2734 WHERE enable LIKE 'Y%'
2735 AND object_name = 'HZ_LOCATIONS';
2736 */
2737 CURSOR c_policies(l_schema VARCHAR2) IS
2738 SELECT object_owner, object_name, policy_name
2739 FROM sys.dba_policies
2740 WHERE enable = 'YES'
2741 AND object_owner = l_schema
2742 AND object_name = 'HZ_LOCATIONS';
2743
2744 TYPE t_namelist IS TABLE OF VARCHAR2(30);
2745 l_owners t_namelist;
2746 l_objects t_namelist;
2747 l_policies t_namelist;
2748 str varchar2(1000);
2749 l_status sys.all_indexes.status%type;
2750 l_domidx_opstatus sys.all_indexes.DOMIDX_OPSTATUS%type;
2751 x_dummy BOOLEAN;
2752 x_status varchar2(30);
2753 x_ind varchar2(30);
2754 x_index_owner varchar2(50);
2755 x_drop_index varchar2(255);
2756 x_del_meta varchar2(255);
2757 x_ins_meta varchar2(2000);
2758 l_debug_prefix VARCHAR2(30) := '';
2759
2760 BEGIN
2761 --enable_debug;
2762 x_dummy := fnd_installation.GET_APP_INFO('AR',x_status,x_ind,x_index_owner);
2763 OPEN c_policies(x_index_owner);
2764 FETCH c_policies BULK COLLECT INTO l_owners, l_objects, l_policies;
2765 CLOSE c_policies;
2766 -- disable currently enabled policies
2767 FOR l_count IN 1..l_owners.COUNT LOOP
2768 dbms_rls.enable_policy(l_owners(l_count) , l_objects(l_count),
2769 l_policies(l_count), FALSE);
2770 END LOOP;
2771 OPEN c_index(x_index_owner);
2772 FETCH c_index into l_status,l_domidx_opstatus;
2773 IF c_index%NOTFOUND THEN /* Index is Missing */
2774 -- Delete Meta Data
2775 -- Bug 7262437 : Changed deleting from view to directly from table
2776 /* x_del_meta := 'Delete user_sdo_geom_metadata
2777 Where table_name = ''HZ_LOCATIONS''
2778 And column_name= ''GEOMETRY''';
2779 */
2780 x_del_meta := 'Delete MDSYS.SDO_GEOM_METADATA_TABLE
2784 EXECUTE IMMEDIATE x_del_meta;
2781 Where sdo_table_name = ''HZ_LOCATIONS''
2782 AND sdo_column_name= ''GEOMETRY''
2783 AND sdo_owner = '''||x_index_owner||'''';
2785 -- Create Meta Data
2786 -- Bug 7262437 : Changed inserting directly into table insteda of view
2787 -- to avoid default user name (APPS) getting inserted in sdo_owner column
2788 /* x_ins_meta := 'INSERT INTO user_sdo_geom_metadata (
2789 table_name, column_name, diminfo, srid ) VALUES (
2790 ''HZ_LOCATIONS'', ''GEOMETRY'',
2791 mdsys.sdo_dim_array(
2792 mdsys.sdo_dim_element(''longitude'', -180, 180, 0.00005),
2793 mdsys.sdo_dim_element(''latitude'', -90, 90, 0.00005)), 8307 )';
2794 */
2795 x_ins_meta := 'INSERT INTO MDSYS.SDO_GEOM_METADATA_TABLE (
2796 sdo_owner, sdo_table_name, sdo_column_name, sdo_diminfo, sdo_srid ) VALUES ('''
2797 ||x_index_owner||''', '||
2798 '''HZ_LOCATIONS'', ''GEOMETRY'',
2799 mdsys.sdo_dim_array(
2800 mdsys.sdo_dim_element(''longitude'', -180, 180, 0.00005),
2801 mdsys.sdo_dim_element(''latitude'', -90, 90, 0.00005)), 8307 )';
2802
2803 EXECUTE IMMEDIATE x_ins_meta;
2804 -- Create Index
2805 Create_Index;
2806 ELSIF c_index%FOUND THEN
2807 IF l_status <> 'VALID' OR l_domidx_opstatus <> 'VALID' THEN
2808 /* Index Is Invalid */
2809 -- Drop Index
2810 x_drop_index := 'drop index '||x_index_owner||'.'|| 'HZ_LOCATIONS_N15 force';
2811 EXECUTE IMMEDIATE x_drop_index;
2812 -- Create Index
2813 Create_Index;
2814 ELSE /* Index Exists and Valid */
2815 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2816 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.rebuild_location_index (+)',
2817 p_prefix=>l_debug_prefix,
2818 p_msg_level=>fnd_log.level_procedure);
2819 END IF;
2820 -- Initialize the return code only if we are running as an independent
2821 -- concurrent program. We do not want to change the value of the return
2822 -- code if it has been initialized by the calling program.
2823 IF p_concurrent_mode = 'Y' THEN
2824 retcode := '0';
2825 END IF;
2826
2827 -- Bug 12916969 (Moved Rebuild Index script here from top)
2828 l_rebuild_string := 'ALTER INDEX ' || x_index_owner ||'.' ||'HZ_LOCATIONS_N15 REBUILD ' ||
2829 'PARAMETERS(''sdo_commit_interval=' ||g_commit_interval || ''')';
2830
2831 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2832 hz_utility_v2pub.debug(p_message=>'rebuilding with:' || l_rebuild_string,
2833 p_prefix =>l_debug_prefix,
2834 p_msg_level=>fnd_log.level_statement);
2835 END IF;
2836 EXECUTE IMMEDIATE l_rebuild_string;
2837 END IF; /* Index Is Invalid */
2838 END IF; /* Index is Missing */
2839
2840 -- notify the user that the spatial index was successfully rebuilt, and of
2841 -- successful concurrent program termination only if we are running as a
2842 -- concurrent program.
2843 IF p_concurrent_mode = 'Y' THEN
2844 fnd_file.put_line(fnd_file.log,
2845 fnd_message.get_string('AR', 'HZ_GEO_INDEX_REBUILT'));
2846 fnd_file.put_line(fnd_file.output,
2847 fnd_message.get_string('AR', 'HZ_GEO_INDEX_REBUILT'));
2848 fnd_file.put_line(fnd_file.log,
2849 fnd_message.get_string('FND', 'CONC-CP SUCCESSFUL TERMINATION'));
2850 fnd_file.put_line(fnd_file.output,
2851 fnd_message.get_string('FND', 'CONC-CP SUCCESSFUL TERMINATION'));
2852 ELSE
2853 -- otherwise, just push the error onto the stack.
2854 fnd_message.set_name('AR', 'HZ_GEO_INDEX_REBUILT');
2855 fnd_msg_pub.add;
2856 END IF;
2857 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2858 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.rebuild_location_index (-)',
2859 p_prefix=>l_debug_prefix,
2860 p_msg_level=>fnd_log.level_procedure);
2861 END IF;
2862 Close c_index;
2863 --disable_debug;
2864 -- restore previous state: re-enable previously enabled policies
2865 FOR l_count IN 1..l_owners.COUNT LOOP
2866 dbms_rls.enable_policy(l_owners(l_count), l_objects(l_count),
2867 l_policies(l_count), TRUE);
2868 END LOOP;
2869 -- clean up.
2870 l_owners.DELETE;
2871 l_objects.DELETE;
2872 l_policies.DELETE;
2873 EXCEPTION
2874 WHEN OTHERS THEN
2875 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2876 fnd_message.set_token('ERROR', SQLERRM);
2877
2878 -- log the error only if we are running as a concurrent program.
2879 -- otherwise, push the error onto the stack.
2880 IF p_concurrent_mode = 'Y' THEN
2881 fnd_file.put_line(fnd_file.log, fnd_message.get);
2882 fnd_file.put_line(fnd_file.output,
2883 fnd_message.get_string('FND',
2884 'CONC-CHECK LOG FOR DETAILS'));
2885 ELSE
2886 fnd_msg_pub.add;
2887 END IF;
2888 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2889 hz_utility_v2pub.debug(p_message=>g_pkg_name||' Error:',
2890 p_prefix=>'SQL ERROR',
2891 p_msg_level=>fnd_log.level_error);
2892 hz_utility_v2pub.debug(p_message=>SQLERRM,
2893 p_prefix=>'SQL ERROR',
2894 p_msg_level=>fnd_log.level_error);
2895 END IF;
2896 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2897 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.rebuild_location_index (-)',
2898 p_prefix=>l_debug_prefix,
2899 p_msg_level=>fnd_log.level_procedure);
2900 END IF;
2901 -- bug fix 3872778 enable policy function
2902 FOR l_count IN 1..l_owners.COUNT LOOP
2903 dbms_rls.enable_policy(l_owners(l_count), l_objects(l_count),
2904 l_policies(l_count), TRUE);
2905 END LOOP;
2906 --disable_debug;
2907 retcode := '2';
2908 errbuf := SQLERRM;
2909 END rebuild_location_index;
2910
2911 PROCEDURE Create_Index
2912 IS
2913 object_exists EXCEPTION;
2914 column_not_found EXCEPTION;
2915 domainobj_exists EXCEPTION;
2916 no_metadata_found EXCEPTION;
2917
2918 PRAGMA EXCEPTION_INIT(object_exists, -955);
2919 PRAGMA EXCEPTION_INIT(column_not_found, -904);
2920 PRAGMA EXCEPTION_INIT(domainobj_exists, -29879);
2921 PRAGMA EXCEPTION_INIT(no_metadata_found, -13203);
2922
2923 l_exec_string VARCHAR2(1000) ;
2924 x_dummy BOOLEAN;
2925 x_status varchar2(30);
2926 x_ind varchar2(30);
2927 x_index_owner varchar2(50);
2928 check_tspace_exist varchar2(100); --Bug 3299301
2929 physical_tspace_name varchar2(100); --Bug 3299301
2930 BEGIN
2931 x_dummy := fnd_installation.GET_APP_INFO('AR',x_status,x_ind,x_index_owner);
2932 AD_TSPACE_UTIL.get_tablespace_name('AR','TRANSACTION_INDEXES','Y',check_tspace_exist,physical_tspace_name);
2933 l_exec_string := 'CREATE INDEX '||x_index_owner||'.'|| 'hz_locations_n15 ON '||x_index_owner||'.'||
2934 'hz_locations(geometry) INDEXTYPE IS mdsys.spatial_index parameters(''TABLESPACE='||
2935 physical_tspace_name||''')'; --Bug 3299301
2936 -- create the index
2937 BEGIN
2938 if(check_tspace_exist = 'Y') THEN --Bug 3299301
2939 EXECUTE IMMEDIATE l_exec_string;
2940 end if;
2941 EXCEPTION
2942 WHEN column_not_found THEN
2943 NULL;
2944 WHEN object_exists THEN
2945 NULL;
2946 WHEN domainobj_exists THEN
2947 NULL;
2948 WHEN no_metadata_found THEN
2949 NULL;
2950 END;
2951
2952 END Create_Index;
2953
2954 END hz_elocation_pkg;
[Home] [Help]