4 -- G_SUCCESS varchar2(30) := 'SUCCESS';
1 PACKAGE BODY HZ_GNR_PKG AS
2 /*$Header: ARHGNRCB.pls 120.28 2008/03/11 08:36:57 nshinde ship $ */
3
5 -- G_ERROR varchar2(30) := 'ERROR';
6
7 ------------
8 -- New local procedure created to compile package for fixing bug 5521521
9 -- This will be very rarely called only when Admin changes Geography setup
10 -- and then tries to create address. (21-Sep-2006 Nishant)
11 PROCEDURE recompile_pkg (p_pkg_name IN VARCHAR2)IS
12 PRAGMA AUTONOMOUS_TRANSACTION;
13
14 l_module_prefix CONSTANT VARCHAR2(30) := 'HZ:ARHGNRCB:HZ_GNR_PKG';
15 l_module CONSTANT VARCHAR2(30) := 'ADDRESS_VALIDATION';
16 l_debug_prefix CONSTANT VARCHAR2(30) := '';
17
18 BEGIN
19 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
20 hz_utility_v2pub.debug
21 (p_message => 'Begin Compile Package '||p_pkg_name,
22 p_prefix => l_debug_prefix,
23 p_msg_level => fnd_log.level_statement,
24 p_module_prefix => l_module_prefix,
25 p_module => l_module
26 );
27 END IF;
28
29 EXECUTE IMMEDIATE 'ALTER PACKAGE '||p_pkg_name||' COMPILE';
30
31 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
32 hz_utility_v2pub.debug
33 (p_message => 'Completed Compiling Package '||p_pkg_name,
34 p_prefix => l_debug_prefix,
35 p_msg_level => fnd_log.level_statement,
36 p_module_prefix => l_module_prefix,
37 p_module => l_module
38 );
39 END IF;
40
41 COMMIT;
42 EXCEPTION WHEN OTHERS THEN
43
44 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
45 hz_utility_v2pub.debug
46 (p_message => 'EXCEPTION during compiling Package '||p_pkg_name||
47 ':'||SQLERRM,
48 p_prefix => l_debug_prefix,
49 p_msg_level => fnd_log.level_statement,
50 p_module_prefix => l_module_prefix,
51 p_module => l_module
52 );
53 END IF;
54 COMMIT;
55 END recompile_pkg;
56 ------------
57
58 FUNCTION get_map_id(p_country in varchar2,p_loc_table in varchar2,p_address_style in varchar2) RETURN NUMBER IS
59 l_map_id number;
60 CURSOR c_map IS
61 SELECT MAP_ID
62 FROM hz_geo_struct_map
63 WHERE COUNTRY_CODE = p_country
64 AND LOC_TBL_NAME = p_loc_table
65 AND NVL(ADDRESS_STYLE,'X_NOSTYLE_X') = NVL(p_address_style,'X_NOSTYLE_X');
66
67 CURSOR c_style_null_map IS
68 SELECT MAP_ID
69 FROM hz_geo_struct_map
70 WHERE COUNTRY_CODE = p_country
71 AND LOC_TBL_NAME = p_loc_table
72 AND ADDRESS_STYLE IS NULL;
73 BEGIN
74 OPEN c_map;
75 FETCH c_map INTO l_map_id;
76 IF c_map%NOTFOUND THEN
77 OPEN c_style_null_map;
78 FETCH c_style_null_map INTO l_map_id;
79 CLOSE c_style_null_map;
80 END IF;
81 CLOSE c_map;
82 RETURN l_map_id;
83 END get_map_id;
84
85 -- Added by Nishant on 24-Aug-2005 for fetching country code from
86 -- HZ_GEOGRAPHY_IDENTIFIER. If no country code found, it will return
87 -- back original passed in value.
88 FUNCTION get_country_code(pv_country IN VARCHAR2) RETURN VARCHAR2 IS
89 l_country_code VARCHAR2(10);
90
91 CURSOR c_country_code (l_country VARCHAR2)IS
92 SELECT hgo.identifier_value
93 FROM hz_geography_identifiers hgo
94 WHERE hgo.identifier_subtype = 'ISO_COUNTRY_CODE'
95 AND hgo.identifier_type = 'CODE'
96 AND hgo.geography_use = 'MASTER_REF'
97 AND hgo.geography_type = 'COUNTRY'
98 AND hgo.primary_flag = 'Y' -- fix for bug 5400607 (Nishant 20-Jul-2006)
99 AND EXISTS ( SELECT '1'
100 FROM hz_geography_identifiers hgi
101 WHERE hgi.geography_use = 'MASTER_REF'
102 AND hgi.geography_type = 'COUNTRY'
103 AND hgi.geography_id = hgo.geography_id
104 AND UPPER(hgi.identifier_value) = l_country
105 );
106
107 BEGIN
108
109 OPEN c_country_code (UPPER(pv_country));
110 FETCH c_country_code INTO l_country_code;
111 CLOSE c_country_code;
112
113 -- If found country code, return it.
114 -- Otherwise return back the passed in value.
115 IF (l_country_code IS NOT NULL) THEN
116 RETURN l_country_code;
117 ELSE
118 RETURN pv_country;
119 END IF;
120
121 END get_country_code;
122
123 /**
124 * Copyright (c) 2001 Oracle Corporation Redwood Shores, California, USA
125 * All rights reserved.
126 *=======================================================================
127 * FILE ARHGNRCB.pls
128 *
129 * PROCEDURE create_geo_name_ref
130 *
131 * DESCRIPTION
132 * This is the procedure which is called by the
133 * concurrent request. This will spawn off the workers
134 *
135 * RELATED PACKAGES
136 *
137 * PUBLIC VARIABLES
138 *
139 * PUBLIC FUNCTIONS
140 *
141 * NOTES
142 *
143 * MODIFICATION HISTORY
144 *
145 * 20-JAN-2003 Satyadeep o Created.
146 * Chandrashekar
147 * 11-Oct-2005 Nishant Singhai o Modified for bug 4645523. If no. of worker
148 * profile is set to 0 or less, then default it
149 * to 1.
150 * 11-Mar-2008 Neeraj Shinde o Modified for bug 6860045. Sub request should
151 * run immediately. Converted the scheduled time
152 * of sub request in 24 Hour format.
153 */
154
155 PROCEDURE create_geo_name_ref (
156 errbuf OUT NOCOPY VARCHAR2,
157 retcode OUT NOCOPY VARCHAR2,
158 p_location_table_name IN VARCHAR2,
159 p_run_type IN VARCHAR2,
160 p_usage_code IN VARCHAR2,
161 p_country_code IN VARCHAR2,
162 p_from_location_id IN VARCHAR2,
163 p_to_location_id IN VARCHAR2,
164 p_start_date IN VARCHAR2,
165 p_end_date IN VARCHAR2
166 ) IS
167
168 l_from_location_id NUMBER;
169 l_to_location_id NUMBER;
170 l_loc_tbl_name VARCHAR2(30);
171 l_start_date DATE;
172 l_end_date DATE;
173 l_num_of_workers NUMBER;
174 l_commit_size NUMBER;
178
175 l_usage_count NUMBER;
176 l_run_type_count NUMBER;
177 l_country_code_count NUMBER;
179 TYPE nTable IS TABLE OF NUMBER index by binary_integer;
180 l_sub_requests nTable;
181
182
183 BEGIN
184
185 retcode := 0;
186
187 HZ_GNR_UTIL_PKG.outandlog('Starting Concurrent Program ''Geo Name Referencing''');
188 HZ_GNR_UTIL_PKG.outandlog('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
189 HZ_GNR_UTIL_PKG.outandlog('NEWLINE');
190
191 -- Initialize return status and message stack
192 FND_MSG_PUB.initialize;
193
194 -- Validate location table name
195
196 IF p_location_table_name IS NULL THEN
197
201
198 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_LOC_TABLE_MAND');
199 FND_MSG_PUB.ADD;
200 RAISE FND_API.G_EXC_ERROR;
202 ELSIF p_location_table_name NOT IN ( 'HR_LOCATIONS_ALL',
203 'HZ_LOCATIONS') THEN
204
205 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_LOC_TABLE_INVALID');
206 FND_MSG_PUB.ADD;
207 RAISE FND_API.G_EXC_ERROR;
208
209 END IF;
210
211 l_from_location_id := to_number(p_from_location_id);
212 l_to_location_id := to_number(p_to_location_id);
213 l_start_date := fnd_date.canonical_to_date(p_start_date);
214 l_end_date := fnd_date.canonical_to_date(p_end_date);
215
216
217 -- Validate location range.
218 -- Both location id from and location id to should be not null
219 -- OR location id from can be passed location id to can be null
220 -- OR both location id from and location id to should be null
221
222 IF l_from_location_id IS NULL THEN
223 IF l_to_location_id IS NOT NULL THEN
224 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_TO_LOC_NOT_NULL');
225 FND_MSG_PUB.ADD;
226 RAISE FND_API.G_EXC_ERROR;
227
228 END IF;
229 ELSE
230 IF l_to_location_id IS NULL THEN
231 NULL;
232 ELSE
233 IF l_from_location_id > l_to_location_id THEN
234 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_FROM_LOC_HIGHER');
235 FND_MSG_PUB.ADD;
236 RAISE FND_API.G_EXC_ERROR;
237 END IF;
238 END IF;
239 END IF;
240
241 -- Validate Date parameters
242 -- Both Start date and End date should be not null
243 -- OR Start date can be passed End date can be null
244 -- OR both Start date and End date should be null
245
246 IF l_start_date IS NULL THEN
247 IF l_end_date IS NOT NULL THEN
248 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_TO_DATE_NOT_NULL');
249 FND_MSG_PUB.ADD;
250 RAISE FND_API.G_EXC_ERROR;
251
252 END IF;
253 ELSE
254 IF l_end_date IS NULL THEN
255 NULL;
256 ELSE
257 IF l_start_date > l_end_date THEN
258 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_START_DATE_HIGHER');
259 FND_MSG_PUB.ADD;
260 RAISE FND_API.G_EXC_ERROR;
261 END IF;
262 END IF;
263 END IF;
264
265 -- Validate Run Type parameter
266 -- 1. Run Type is Mandatory.
267 -- 2. It should be either 'ALL' or 'NEW' or 'ERROR' (seeded as lookup Type HZ_GEO_GNR_RUN_TYPE)
268 -- If new run type is added, message text will have to be changed.
269 IF (p_run_type IS NULL) THEN
270 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_RUN_TYPE');
271 FND_MSG_PUB.ADD;
272 RAISE FND_API.G_EXC_ERROR;
273 ELSE
274 SELECT COUNT(*)
275 INTO l_run_type_count
276 FROM ar_lookups
277 WHERE lookup_type = 'HZ_GEO_GNR_RUN_TYPE'
278 AND lookup_code = p_run_type
279 AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,SYSDATE) AND NVL(end_date_active, SYSDATE+1)
280 AND enabled_flag = 'Y';
281
282 IF (l_run_type_count = 0) THEN
283 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_RUN_TYPE');
284 FND_MSG_PUB.ADD;
285 RAISE FND_API.G_EXC_ERROR;
286 END IF;
287 END IF;
288
289 -- Validate USAGE Type parameter from seeded usage from ar_lookups for
290 -- lookup Type HZ_GEOGRAPHY_USAGE
291 IF (p_usage_code IS NOT NULL) THEN
292 SELECT COUNT(*)
293 INTO l_usage_count
294 FROM ar_lookups
295 WHERE lookup_type = 'HZ_GEOGRAPHY_USAGE'
296 AND lookup_code = p_usage_code
297 AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,SYSDATE) AND NVL(end_date_active, SYSDATE+1)
298 AND enabled_flag = 'Y';
299
300 IF (l_usage_count = 0) THEN
301 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_USAGE_CODE');
302 FND_MSG_PUB.ADD;
303 RAISE FND_API.G_EXC_ERROR;
304 END IF;
305 END IF;
306
307 -- Validate Country Code parameter from FND_TERRITORIES
308 IF (p_country_code IS NOT NULL) THEN
309 SELECT COUNT(*)
310 INTO l_country_code_count
311 FROM fnd_territories
312 WHERE territory_code = p_country_code
313 AND obsolete_flag = 'N';
314
315 IF (l_country_code_count = 0) THEN
316 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NOT_VALID_COUNTRY');
317 FND_MSG_PUB.ADD;
318 RAISE FND_API.G_EXC_ERROR;
319 END IF;
320 END IF;
321
322 l_num_of_workers := fnd_profile.VALUE('HZ_GNR_NUM_OF_WORKERS');
323
324 -- if profile is set to a value less than 1 i.e. 0 or -ve value, then
325 -- set number of workers to 1 (Fix for Bug 4645523 by Nishant on 11-Oct-2005)
326 IF (NVL(l_num_of_workers,0) < 1) THEN
327 l_num_of_workers := 1;
328 END IF;
329
330 HZ_GNR_UTIL_PKG.log('Spawning ' || to_char(l_num_of_workers) || ' Workers for Geo Name Referencing');
331
332 -- Submit requests as per profile HZ_GNR_NUM_OF_WORKERS
333
334 FOR I in 1..TO_NUMBER(l_num_of_workers) LOOP
335 l_sub_requests(i) := FND_REQUEST.SUBMIT_REQUEST('AR', 'ARHGNRW',
336 'Geo Name Referencing Worker ' || TO_CHAR(i),
337 to_char(sysdate,'DD-MON-YY HH24:MI:SS'),
338 FALSE, TO_CHAR(i),
339 p_location_table_name,
340 p_run_type,
341 p_usage_code,
342 p_country_code,
343 p_from_location_id,
344 p_to_location_id,
345 p_start_date,
346 p_end_date,
347 to_char(l_num_of_workers)
348 );
349 IF l_sub_requests(i) = 0 THEN
353 HZ_GNR_UTIL_PKG.log('Submitted request for Worker ' || TO_CHAR(i) );
350 HZ_GNR_UTIL_PKG.log('Error submitting worker ' || i);
351 HZ_GNR_UTIL_PKG.log(fnd_message.get);
352 ELSE
354 HZ_GNR_UTIL_PKG.log('Request ID : ' || l_sub_requests(i));
355 END IF;
356 EXIT when l_sub_requests(i) = 0;
357 END LOOP;
358
359 HZ_GNR_UTIL_PKG.outandlog('Concurrent Program Execution completed ');
360 HZ_GNR_UTIL_PKG.outandlog('End Time : '|| TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
361
362 EXCEPTION
363 WHEN FND_API.G_EXC_ERROR THEN
364 HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
365 retcode := 2;
366 errbuf := errbuf || HZ_GNR_UTIL_PKG.logerror;
367 FND_FILE.close;
368 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
369 HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
370 retcode := 2;
371 errbuf := errbuf || HZ_GNR_UTIL_PKG.logerror;
372 FND_FILE.close;
373 WHEN OTHERS THEN
374 HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
375 retcode := 2;
376 errbuf := errbuf || HZ_GNR_UTIL_PKG.logerror;
377 FND_FILE.close;
378 END;
379
380 /**
381 * Copyright (c) 2001 Oracle Corporation Redwood Shores, California, USA
382 * All rights reserved.
383 *=======================================================================
384 * FILE ARHGNRCB.pls
385 *
386 * PROCEDURE process_gnr_worker
387 *
388 * DESCRIPTION
389 * This is the worker procedure which would call the gnr api
390 * for the records selected from the respective table name
391 * The records selected by this worker will be determined by
392 * the worker number of this worker and total number of
393 * workers. MOD function will be used for this.
394 * mod(location_id, p_num_workers) = p_worker_number
395 * location id 200 to 210. then
396 * number of workers 5. Worker number = 1
397 * This worker will pick up 201 and 206
398 * number of workers 5. Worker number = 2
399 * This worker will pick up 202 and 207
400 * number of workers 5. Worker number = 3
401 * This worker will pick up 203 and 208
402 * number of workers 5. Worker number = 4
403 * This worker will pick up 204 and 209
404 * number of workers 5. Worker number=5 will be assigned worker number=0
405 * This worker will pick up 200 and 210
406 *
407 * RELATED PACKAGES
408 *
409 * PUBLIC VARIABLES
410 *
411 * PUBLIC FUNCTIONS
412 *
413 * NOTES
414 *
415 * MODIFICATION HISTORY
416 *
417 * 20-JAN-2003 Satyadeep o Created.
418 * Chandrashekar
419 * 29-JUN-2006 Nishant Singhai Bug 5257371
420 *
421 * 25-JAN-2008 Neeraj Shinde Bug 6750566
422 */
423
424 PROCEDURE process_gnr_worker (
425 errbuf OUT NOCOPY VARCHAR2,
426 retcode OUT NOCOPY VARCHAR2,
427 p_worker_number IN VARCHAR2,
428 p_location_table_name IN VARCHAR2,
429 p_run_type IN VARCHAR2,
430 p_usage_code IN VARCHAR2,
431 p_country_code IN VARCHAR2,
432 p_from_location_id IN VARCHAR2,
433 p_to_location_id IN VARCHAR2,
434 p_start_date IN VARCHAR2,
435 p_end_date IN VARCHAR2,
436 p_num_workers IN VARCHAR2
437 ) IS
438
439
440 l_return_status VARCHAR2(30);
441 l_addr_val_level VARCHAR2(30);
442 l_addr_warn_msg VARCHAR2(2000);
443 l_addr_val_status VARCHAR2(30);
444 l_msg_count NUMBER;
445 l_msg_data VARCHAR2(2000);
446 l_sqlerr VARCHAR2(2000);
447 l_num_workers NUMBER;
448 l_worker_number NUMBER;
449 l_from_location_id NUMBER;
450 l_to_location_id NUMBER;
451 l_loc_tbl_name VARCHAR2(30);
452 l_start_date DATE;
453 l_end_date DATE;
454 l_cur_location_id NUMBER;
455 l_commit_size NUMBER;
456 l_count_num_of_rec NUMBER;
457 l_num_locations NUMBER;
458 l_num_err_locations NUMBER;
459 l_num_success_locations NUMBER;
460 l_curr_loc_msg_prefix VARCHAR2(2000);
461 l_summ_loc_msg_prefix VARCHAR2(2000);
462 l_err_loc_msg_prefix VARCHAR2(2000);
463 l_success_loc_msg_prefix VARCHAR2(2000);
464
465 l_run_type VARCHAR2(100);
466 l_country_code VARCHAR2(360);
467 l_usage_code VARCHAR2(100);
468
469 CURSOR c_loc_hr_new(p_country_code IN VARCHAR2,
470 p_from_location_id IN NUMBER,
471 p_to_location_id IN NUMBER,
472 p_start_date IN DATE,
473 p_end_date IN DATE,
474 p_num_workers IN NUMBER,
475 p_worker_number IN NUMBER,
476 p_usage_code IN VARCHAR2,
477 p_table_name IN VARCHAR2
478 ) IS
479 SELECT loc.location_id
480 FROM hr_locations_all loc
481 WHERE (((p_from_location_id IS NOT NULL and p_to_location_id IS NOT NULL) and
482 (loc.location_id BETWEEN p_from_location_id and p_to_location_id )) or
483 ((p_from_location_id IS NOT NULL and p_to_location_id IS NULL) and
484 (loc.location_id >= p_from_location_id)) or
485 (p_from_location_id IS NULL and p_to_location_id IS NULL))
486 AND ((p_start_date IS NULL and p_end_date IS NULL) or
487 (p_start_date IS NOT NULL and p_end_date IS NULL and
488 trunc(loc.creation_date) >= p_start_date) or
492 ((p_country_code IS NOT NULL) AND
489 ((p_start_date IS NOT NULL and p_end_date IS NOT NULL) and
490 (trunc(loc.creation_date) BETWEEN p_start_date and p_end_date)))
491 AND ((p_country_code IS NULL) OR
493 (loc.country = UPPER(p_country_code))) OR
494 ((p_country_code IS NOT NULL) AND
495 (loc.country IN (SELECT hgo.identifier_value
496 FROM hz_geography_identifiers hgo
497 WHERE EXISTS ( SELECT '1'
498 FROM hz_geography_identifiers hgi
499 WHERE hgi.identifier_subtype = 'ISO_COUNTRY_CODE'
500 AND hgi.identifier_type = 'CODE'
501 AND hgi.geography_use = 'MASTER_REF'
502 AND hgi.geography_type = 'COUNTRY'
503 AND hgi.geography_id = hgo.geography_id
504 AND UPPER(hgi.identifier_value) = p_country_code
505 )
506 )
507 ))
508 )
509 AND NOT EXISTS (
510 SELECT '1'
511 FROM hz_geo_name_reference_log gnr
512 WHERE gnr.location_table_name = p_table_name
513 AND gnr.usage_code = DECODE(p_usage_code,'ALL',gnr.usage_code,p_usage_code)
514 AND loc.location_id = gnr.location_id
515 )
516 AND mod(loc.location_id, p_num_workers) = p_worker_number
517 ORDER BY loc.location_id;
518
519 CURSOR c_loc_hr_error(p_country_code IN VARCHAR2,
520 p_from_location_id IN NUMBER,
521 p_to_location_id IN NUMBER,
522 p_start_date IN DATE,
523 p_end_date IN DATE,
524 p_num_workers IN NUMBER,
525 p_worker_number IN NUMBER,
526 p_usage_code IN VARCHAR2,
527 p_table_name IN VARCHAR2
528 ) IS
529 SELECT loc.location_id
530 FROM hr_locations_all loc
531 WHERE (((p_from_location_id IS NOT NULL and p_to_location_id IS NOT NULL) and
532 (loc.location_id BETWEEN p_from_location_id and p_to_location_id )) or
533 ((p_from_location_id IS NOT NULL and p_to_location_id IS NULL) and
534 (loc.location_id >= p_from_location_id)) or
535 (p_from_location_id IS NULL and p_to_location_id IS NULL))
536 AND ((p_start_date IS NULL and p_end_date IS NULL) or
537 (p_start_date IS NOT NULL and p_end_date IS NULL and
538 trunc(loc.creation_date) >= p_start_date) or
539 ((p_start_date IS NOT NULL and p_end_date IS NOT NULL) and
540 (trunc(loc.creation_date) BETWEEN p_start_date and p_end_date)))
541 AND ((p_country_code IS NULL) OR
542 ((p_country_code IS NOT NULL) AND
543 (loc.country = UPPER(p_country_code))) OR
544 ((p_country_code IS NOT NULL) AND
545 (loc.country IN (SELECT hgo.identifier_value
546 FROM hz_geography_identifiers hgo
547 WHERE EXISTS ( SELECT '1'
548 FROM hz_geography_identifiers hgi
549 WHERE hgi.identifier_subtype = 'ISO_COUNTRY_CODE'
550 AND hgi.identifier_type = 'CODE'
551 AND hgi.geography_use = 'MASTER_REF'
552 AND hgi.geography_type = 'COUNTRY'
553 AND hgi.geography_id = hgo.geography_id
554 AND UPPER(hgi.identifier_value) = p_country_code
555 )
556 )
557 ))
558 )
559 AND EXISTS (
560 SELECT '1'
561 FROM hz_geo_name_reference_log gnr
562 WHERE gnr.location_table_name = p_table_name
563 AND gnr.usage_code = DECODE(p_usage_code,'ALL',gnr.usage_code,p_usage_code)
564 AND gnr.map_status = 'E'
565 AND loc.location_id = gnr.location_id
566 )
567 AND mod(loc.location_id, p_num_workers) = p_worker_number
568 ORDER BY loc.location_id;
569
570 CURSOR c_loc_hr_all(p_country_code IN VARCHAR2,
571 p_from_location_id IN NUMBER,
572 p_to_location_id IN NUMBER,
573 p_start_date IN DATE,
574 p_end_date IN DATE,
575 p_num_workers IN NUMBER,
576 p_worker_number IN NUMBER
577 ) IS
578 SELECT loc.location_id
579 FROM hr_locations_all loc
580 WHERE (((p_from_location_id IS NOT NULL and p_to_location_id IS NOT NULL) and
581 (loc.location_id BETWEEN p_from_location_id and p_to_location_id )) or
582 ((p_from_location_id IS NOT NULL and p_to_location_id IS NULL) and
583 (loc.location_id >= p_from_location_id)) or
584 (p_from_location_id IS NULL and p_to_location_id IS NULL))
585 AND ((p_start_date IS NULL and p_end_date IS NULL) or
586 (p_start_date IS NOT NULL and p_end_date IS NULL and
587 trunc(loc.creation_date) >= p_start_date) or
588 ((p_start_date IS NOT NULL and p_end_date IS NOT NULL) and
589 (trunc(loc.creation_date) BETWEEN p_start_date and p_end_date)))
590 AND ((p_country_code IS NULL) OR
591 ((p_country_code IS NOT NULL) AND
592 (loc.country = UPPER(p_country_code))) OR
593 ((p_country_code IS NOT NULL) AND
594 (loc.country IN (SELECT hgo.identifier_value
598 WHERE hgi.identifier_subtype = 'ISO_COUNTRY_CODE'
595 FROM hz_geography_identifiers hgo
596 WHERE EXISTS ( SELECT '1'
597 FROM hz_geography_identifiers hgi
599 AND hgi.identifier_type = 'CODE'
600 AND hgi.geography_use = 'MASTER_REF'
601 AND hgi.geography_type = 'COUNTRY'
602 AND hgi.geography_id = hgo.geography_id
603 AND UPPER(hgi.identifier_value) = p_country_code
604 )
605 )
606 ))
607 )
608 AND mod(loc.location_id, p_num_workers) = p_worker_number
609 ORDER BY loc.location_id;
610
611 CURSOR c_loc_hz_new(p_country_code IN VARCHAR2,
612 p_from_location_id IN NUMBER,
613 p_to_location_id IN NUMBER,
614 p_start_date IN DATE,
615 p_end_date IN DATE,
616 p_num_workers IN NUMBER,
617 p_worker_number IN NUMBER,
618 p_usage_code IN VARCHAR2,
619 p_table_name IN VARCHAR2) IS
620 SELECT LOCATION_ID,
621 ADDRESS_STYLE,
622 COUNTRY,
623 STATE,
624 PROVINCE,
625 COUNTY,
626 CITY,
627 POSTAL_CODE,
628 POSTAL_PLUS4_CODE,
629 ATTRIBUTE1,
630 ATTRIBUTE2,
631 ATTRIBUTE3,
632 ATTRIBUTE4,
633 ATTRIBUTE5,
634 ATTRIBUTE6,
635 ATTRIBUTE7,
636 ATTRIBUTE8,
637 ATTRIBUTE9,
638 ATTRIBUTE10
639 FROM hz_locations loc
640 WHERE (((p_from_location_id IS NOT NULL and p_to_location_id IS NOT NULL) and
641 (location_id BETWEEN p_from_location_id and p_to_location_id )) or
642 ((p_from_location_id IS NOT NULL and p_to_location_id IS NULL) and
643 ( location_id >= p_from_location_id)) or
644 (p_from_location_id IS NULL and p_to_location_id IS NULL))
645 AND ((p_start_date IS NULL and p_end_date IS NULL) or
646 (p_start_date IS NOT NULL and p_end_date IS NULL and
647 trunc(creation_date) >= p_start_date) or
648 ((p_start_date IS NOT NULL and p_end_date IS NOT NULL) and
649 (trunc(creation_date) BETWEEN p_start_date and p_end_date)))
650 AND ((p_country_code IS NULL) OR
651 ((p_country_code IS NOT NULL) AND
652 (country = p_country_code)) OR
653 ((p_country_code IS NOT NULL) AND
654 (country IN (SELECT hgo.identifier_value
655 FROM hz_geography_identifiers hgo
656 WHERE EXISTS ( SELECT '1'
657 FROM hz_geography_identifiers hgi
658 WHERE hgi.identifier_subtype = 'ISO_COUNTRY_CODE'
659 AND hgi.identifier_type = 'CODE'
660 AND hgi.geography_use = 'MASTER_REF'
661 AND hgi.geography_type = 'COUNTRY'
662 AND hgi.geography_id = hgo.geography_id
663 AND UPPER(hgi.identifier_value) = p_country_code
664 )
665 )
666 ))
667 )
668 AND NOT EXISTS (
669 SELECT '1'
670 FROM hz_geo_name_reference_log gnr
671 WHERE gnr.location_table_name = p_table_name
672 AND gnr.usage_code = DECODE(p_usage_code,'ALL',gnr.usage_code,p_usage_code)
673 AND loc.location_id = gnr.location_id
674 )
675 AND mod(location_id, p_num_workers) = p_worker_number
676 ORDER BY location_id;
677
678 CURSOR c_loc_hz_error(p_country_code IN VARCHAR2,
679 p_from_location_id IN NUMBER,
680 p_to_location_id IN NUMBER,
681 p_start_date IN DATE,
682 p_end_date IN DATE,
683 p_num_workers IN NUMBER,
684 p_worker_number IN NUMBER,
685 p_usage_code IN VARCHAR2,
686 p_table_name IN VARCHAR2) IS
687 SELECT LOCATION_ID,
688 ADDRESS_STYLE,
689 COUNTRY,
690 STATE,
691 PROVINCE,
692 COUNTY,
693 CITY,
694 POSTAL_CODE,
695 POSTAL_PLUS4_CODE,
696 ATTRIBUTE1,
697 ATTRIBUTE2,
698 ATTRIBUTE3,
699 ATTRIBUTE4,
700 ATTRIBUTE5,
701 ATTRIBUTE6,
702 ATTRIBUTE7,
703 ATTRIBUTE8,
704 ATTRIBUTE9,
705 ATTRIBUTE10
706 FROM hz_locations loc
707 WHERE (((p_from_location_id IS NOT NULL and p_to_location_id IS NOT NULL) and
708 (location_id BETWEEN p_from_location_id and p_to_location_id )) or
709 ((p_from_location_id IS NOT NULL and p_to_location_id IS NULL) and
710 ( location_id >= p_from_location_id)) or
711 (p_from_location_id IS NULL and p_to_location_id IS NULL))
712 AND ((p_start_date IS NULL and p_end_date IS NULL) or
713 (p_start_date IS NOT NULL and p_end_date IS NULL and
714 trunc(creation_date) >= p_start_date) or
715 ((p_start_date IS NOT NULL and p_end_date IS NOT NULL) and
716 (trunc(creation_date) BETWEEN p_start_date and p_end_date)))
717 AND ((p_country_code IS NULL) OR
718 ((p_country_code IS NOT NULL) AND
719 (country = p_country_code)) OR
720 ((p_country_code IS NOT NULL) AND
724 FROM hz_geography_identifiers hgi
721 (country IN (SELECT hgo.identifier_value
722 FROM hz_geography_identifiers hgo
723 WHERE EXISTS ( SELECT '1'
725 WHERE hgi.identifier_subtype = 'ISO_COUNTRY_CODE'
726 AND hgi.identifier_type = 'CODE'
727 AND hgi.geography_use = 'MASTER_REF'
728 AND hgi.geography_type = 'COUNTRY'
729 AND hgi.geography_id = hgo.geography_id
730 AND UPPER(hgi.identifier_value) = p_country_code
731 )
732 )
733 ))
734 )
735 AND EXISTS (
736 SELECT '1'
737 FROM hz_geo_name_reference_log gnr
738 WHERE gnr.location_table_name = p_table_name
739 AND gnr.usage_code = DECODE(p_usage_code,'ALL',gnr.usage_code,p_usage_code)
740 AND gnr.map_status = 'E'
741 AND loc.location_id = gnr.location_id
742 )
743 AND mod(location_id, p_num_workers) = p_worker_number
744 ORDER BY location_id;
745
746 CURSOR c_loc_hz_all(p_country_code IN VARCHAR2,
747 p_from_location_id IN NUMBER,
748 p_to_location_id IN NUMBER,
749 p_start_date IN DATE,
750 p_end_date IN DATE,
751 p_num_workers IN NUMBER,
752 p_worker_number IN NUMBER
753 ) IS
754 SELECT LOCATION_ID,
755 ADDRESS_STYLE,
756 COUNTRY,
757 STATE,
758 PROVINCE,
759 COUNTY,
760 CITY,
761 POSTAL_CODE,
762 POSTAL_PLUS4_CODE,
763 ATTRIBUTE1,
764 ATTRIBUTE2,
765 ATTRIBUTE3,
766 ATTRIBUTE4,
767 ATTRIBUTE5,
768 ATTRIBUTE6,
769 ATTRIBUTE7,
770 ATTRIBUTE8,
771 ATTRIBUTE9,
772 ATTRIBUTE10
773 FROM hz_locations loc
774 WHERE (((p_from_location_id IS NOT NULL and p_to_location_id IS NOT NULL) and
775 (location_id BETWEEN p_from_location_id and p_to_location_id )) or
776 ((p_from_location_id IS NOT NULL and p_to_location_id IS NULL) and
777 ( location_id >= p_from_location_id)) or
778 (p_from_location_id IS NULL and p_to_location_id IS NULL))
779 AND ((p_start_date IS NULL and p_end_date IS NULL) or
780 (p_start_date IS NOT NULL and p_end_date IS NULL and
781 trunc(creation_date) >= p_start_date) or
782 ((p_start_date IS NOT NULL and p_end_date IS NOT NULL) and
783 (trunc(creation_date) BETWEEN p_start_date and p_end_date)))
784 AND ((p_country_code IS NULL) OR
785 ((p_country_code IS NOT NULL) AND
786 (country = p_country_code)) OR
787 ((p_country_code IS NOT NULL) AND
788 (country IN (SELECT hgo.identifier_value
789 FROM hz_geography_identifiers hgo
790 WHERE EXISTS ( SELECT '1'
791 FROM hz_geography_identifiers hgi
792 WHERE hgi.identifier_subtype = 'ISO_COUNTRY_CODE'
793 AND hgi.identifier_type = 'CODE'
794 AND hgi.geography_use = 'MASTER_REF'
795 AND hgi.geography_type = 'COUNTRY'
796 AND hgi.geography_id = hgo.geography_id
797 AND UPPER(hgi.identifier_value) = p_country_code
798 )
799 )
800 ))
801 )
802 AND mod(location_id, p_num_workers) = p_worker_number
803 ORDER BY location_id;
804
805 -- R12 Upgrade cursors
806 CURSOR c_r12_upg (lp_num_workers NUMBER,
807 lp_worker_number NUMBER)
808 IS
809 SELECT loc.LOCATION_ID,
810 loc.ADDRESS_STYLE,
811 loc.COUNTRY,
812 loc.STATE,
813 loc.PROVINCE,
814 loc.COUNTY,
815 loc.CITY,
816 loc.POSTAL_CODE,
817 loc.POSTAL_PLUS4_CODE,
818 loc.ATTRIBUTE1,
819 loc.ATTRIBUTE2,
820 loc.ATTRIBUTE3,
821 loc.ATTRIBUTE4,
822 loc.ATTRIBUTE5,
823 loc.ATTRIBUTE6,
824 loc.ATTRIBUTE7,
825 loc.ATTRIBUTE8,
826 loc.ATTRIBUTE9,
827 loc.ATTRIBUTE10
828 FROM hz_locations loc
829 WHERE
830 -- Only locations which had Loc_assignments record before
831 /*Bug 6750566 Changes Start
832 EXISTS (
833 SELECT NULL FROM hz_loc_assignments_obs hlo
834 WHERE loc.location_id = hlo.location_id
835 )
836 */
837 EXISTS (
838 SELECT NULL FROM hz_party_sites hps,
839 hz_cust_acct_sites_all hcasa
840 WHERE loc.location_id = hps.location_id
841 AND hps.party_site_id = hcasa.party_site_id
842 )
843 --Bug 6750566 Changes End
844 AND MOD(loc.location_id, lp_num_workers) = lp_worker_number
845 --ORDER BY loc.location_id
846 ;
847
848 l_map_exist VARCHAR2(10);
849
850 CURSOR c_r12_upg_map_cnt IS
851 SELECT DISTINCT country_code
852 FROM hz_geo_struct_map
853 WHERE loc_tbl_name = 'HZ_LOCATIONS';
854
858 FUNCTION check_mapping_exist(l_country_code IN VARCHAR2) RETURN VARCHAR2
855 TYPE mapped_country_tbl_type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
856 mapped_country_list mapped_country_tbl_type;
857
859 IS
860 BEGIN
861 IF (mapped_country_list.COUNT > 0) THEN
862 FOR i IN 1..mapped_country_list.COUNT LOOP
863 IF (mapped_country_list(i) = l_country_code) THEN
864 RETURN 'Y';
865 END IF;
866 END LOOP;
867 -- no match for whole loop
868 RETURN 'N';
869 ELSE
870 RETURN 'N';
871 END IF;
872 END check_mapping_exist;
873
874 BEGIN
875 -- IF (TO_NUMBER(p_worker_number)= 1) THEN
876 -- execute IMMEDIATE 'ALTER SESSION SET TIMED_STATISTICS = TRUE';
877 -- execute IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = GNR_TRACE MAX_DUMP_FILE_SIZE = 50000000 EVENTS =''10046 TRACE NAME CONTEXT FOREVER, LEVEL 8''';
878 -- execute IMMEDIATE 'ALTER SESSION SET SQL_TRACE = TRUE';
879 -- END IF;
880
881 l_count_num_of_rec := 0;
882 l_num_locations := 0;
883 l_num_err_locations := 0;
884 l_num_success_locations := 0;
885
886 l_from_location_id := TO_NUMBER(p_from_location_id);
887 l_to_location_id := TO_NUMBER(p_to_location_id);
888 l_start_date := fnd_date.canonical_to_date(p_start_date);
889 l_end_date := fnd_date.canonical_to_date(p_end_date);
890 l_num_workers := TO_NUMBER(p_num_workers);
891 l_worker_number := TO_NUMBER(p_worker_number);
892 l_commit_size := NVL(fnd_profile.value('HZ_GNR_COMMIT_SIZE'),1000);
893 l_run_type := NVL(p_run_type,'ALL'); -- Complete refresh for passed location ids
894 l_country_code := UPPER(p_country_code);
895
896 retcode := 0;
897
898 -- Set l_usage_code. This depends on table name and run type.
899 -- 1. If table is HR_LOCATIONS_ALL, GNR for only TAX usage is generated. It will
900 -- ignore whatever usage code is passed for HZ_LOCATIONS_ALL.
901 -- 2. If table is HZ_LOCATIONS,
902 -- i. If run type is ALL, we delete GNR for all usages, so we will generate GNR for all usages.
903 -- ii.If run type is ERROR or NEW, we will generate GNR for specific usage passed.
904 -- If no usage is passed, we will generate for all usages
905 IF (p_location_table_name = 'HZ_LOCATIONS') THEN
906 IF (l_run_type IN ('ALL','R12UPGRADE')) THEN
907 l_usage_code := 'ALL';
908 ELSE
909 l_usage_code := NVL(p_usage_code,'ALL');
910 END IF;
911 ELSIF
912 (p_location_table_name = 'HR_LOCATIONS_ALL') THEN
913 l_usage_code := 'TAX';
914 ELSE
915 l_usage_code := NVL(p_usage_code,'ALL');
916 END IF;
917
918 IF l_worker_number = l_num_workers THEN
919 l_worker_number := 0;
920 END IF;
921
922 HZ_GNR_UTIL_PKG.log('Starting Concurrent Program ''Geo Name Referencing Worker: '||p_worker_number||'''');
923 HZ_GNR_UTIL_PKG.log('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
924 HZ_GNR_UTIL_PKG.log('NEWLINE');
925
926 -- Initialize Global Variable
927 -- This variable is checked in HZ_GNR_UTIL_PKG.create_gnr procedure
928 -- For any address validation call from GNR this variable will be set at
929 -- PROCEDURE level to 'GNR'
930 -- Nishant (Perf Bug 5881539 16-APR-2007)
931 HZ_GNR_PKG.G_API_PURPOSE := 'GNR';
932
933 -- Initialize return status and message stack
934 FND_MSG_PUB.initialize;
935
936 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_CUR_LOC_PREFIX');
937 l_curr_loc_msg_prefix := FND_MESSAGE.GET;
938
939 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_SUM_LOC_PREFIX');
940 l_summ_loc_msg_prefix := FND_MESSAGE.GET;
941
942 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_ERR_LOC_PREFIX');
943 l_err_loc_msg_prefix := FND_MESSAGE.GET;
944
945 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_SUCC_LOC_PREFIX');
946 l_success_loc_msg_prefix := FND_MESSAGE.GET;
947
948 -- Depending on the location table name passed
949 -- open the appropriate cursor and call the gnr api.
950 -- Commit as per commit size applicable to number of
951 -- source records. The log will print the location
952 -- record processed and in the end it will print the
953 -- total number of records processed.
954
955 -- Table : HR_LOCATIONS_ALL Run Type : ALL
956 IF ((p_location_table_name = 'HR_LOCATIONS_ALL') AND (l_run_type = 'ALL')) THEN
957
958 HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HR_LOCATIONS_ALL and Run Type : ALL');
959 HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
960 HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
961 HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
962 ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
963 HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
964 ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
965 HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
966 HZ_GNR_UTIL_PKG.outandlog(' ');
967
968 OPEN c_loc_hr_all(l_country_code,
969 l_from_location_id,
970 l_to_location_id,
971 l_start_date,
972 l_end_date,
973 l_num_workers,
974 l_worker_number
975 );
976 LOOP
977 FETCH c_loc_hr_all INTO l_cur_location_id;
978 EXIT WHEN c_loc_hr_all%NOTFOUND;
979
980 l_count_num_of_rec := l_count_num_of_rec + 1;
981 l_num_locations := l_num_locations + 1;
982
983 -- HZ_GNR_UTIL_PKG.log('Current location ' || l_cur_location_id);
984
985 BEGIN
989 );
986 HZ_GNR_PKG.delete_gnr(p_locId => l_cur_location_id,
987 p_locTbl => p_location_table_name,
988 x_status => l_return_status
990
991 HZ_GNR_PKG.validateHrLoc(l_cur_location_id,
992 l_return_status
993 );
994 EXCEPTION
995 WHEN FND_API.G_EXC_ERROR THEN
996 l_num_err_locations := l_num_err_locations + 1;
997 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
998 FND_MSG_PUB.Reset;
999 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1000 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1001 END LOOP;
1002 FND_MSG_PUB.Delete_Msg;
1003 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1004 l_num_err_locations := l_num_err_locations + 1;
1005 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1006 FND_MSG_PUB.Reset;
1007 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1008 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1009 END LOOP;
1010 FND_MSG_PUB.Delete_Msg;
1011 WHEN OTHERS THEN
1012 l_num_err_locations := l_num_err_locations + 1;
1013 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1014 FND_MSG_PUB.Reset;
1015 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1016 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1017 END LOOP;
1018 FND_MSG_PUB.Delete_Msg;
1019 END;
1020
1021 IF l_count_num_of_rec = l_commit_size THEN
1022 COMMIT;
1023 l_count_num_of_rec := 0;
1024 END IF;
1025
1026 END LOOP;
1027 CLOSE c_loc_hr_all;
1028 COMMIT;
1029
1030 l_num_success_locations := l_num_locations - l_num_err_locations;
1031
1032 HZ_GNR_UTIL_PKG.log(' ');
1033 HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1034 HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1035 HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1036
1037 HZ_GNR_UTIL_PKG.out(' ');
1038 HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1039 HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1040 HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1041
1042 HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1043
1044 -- Table : HR_LOCATIONS_ALL Run Type : NEW
1045 ELSIF ((p_location_table_name = 'HR_LOCATIONS_ALL') AND (l_run_type = 'NEW')) THEN
1046
1047 HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HR_LOCATIONS_ALL and Run Type : NEW');
1048 HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1049 HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1050 HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1051 ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1052 HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1053 ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1054 HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1055 HZ_GNR_UTIL_PKG.outandlog(' ');
1056
1057 OPEN c_loc_hr_new(l_country_code,
1058 l_from_location_id,
1059 l_to_location_id,
1060 l_start_date,
1061 l_end_date,
1062 l_num_workers,
1063 l_worker_number,
1064 l_usage_code,
1065 p_location_table_name);
1066 LOOP
1067 FETCH c_loc_hr_new INTO l_cur_location_id;
1068 EXIT WHEN c_loc_hr_new%NOTFOUND;
1069
1070 l_count_num_of_rec := l_count_num_of_rec + 1;
1071 l_num_locations := l_num_locations + 1;
1072
1073 -- HZ_GNR_UTIL_PKG.log('Current location ' || l_cur_location_id);
1074
1075 BEGIN
1076 HZ_GNR_PKG.validateHrLoc(l_cur_location_id,
1077 l_return_status
1078 );
1079 EXCEPTION
1080 WHEN FND_API.G_EXC_ERROR THEN
1081 l_num_err_locations := l_num_err_locations + 1;
1082 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1083 FND_MSG_PUB.Reset;
1084 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1085 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1086 END LOOP;
1087 FND_MSG_PUB.Delete_Msg;
1088 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1089 l_num_err_locations := l_num_err_locations + 1;
1090 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1091 FND_MSG_PUB.Reset;
1092 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1093 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1094 END LOOP;
1095 FND_MSG_PUB.Delete_Msg;
1096 WHEN OTHERS THEN
1097 l_num_err_locations := l_num_err_locations + 1;
1098 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1099 FND_MSG_PUB.Reset;
1100 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1101 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1102 END LOOP;
1103 FND_MSG_PUB.Delete_Msg;
1104 END;
1105
1106 IF l_count_num_of_rec = l_commit_size THEN
1107 COMMIT;
1108 l_count_num_of_rec := 0;
1109 END IF;
1110
1111 END LOOP;
1112 CLOSE c_loc_hr_new;
1113 COMMIT;
1114
1118 HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1115 l_num_success_locations := l_num_locations - l_num_err_locations;
1116
1117 HZ_GNR_UTIL_PKG.log(' ');
1119 HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1120 HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1121
1122 HZ_GNR_UTIL_PKG.out(' ');
1123 HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1124 HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1125 HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1126
1127 HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1128
1129 -- Table : HR_LOCATIONS_ALL Run Type : ERROR
1130 ELSIF ((p_location_table_name = 'HR_LOCATIONS_ALL') AND (l_run_type = 'ERROR')) THEN
1131
1132 HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HR_LOCATIONS_ALL and Run Type : ERROR');
1133 HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1134 HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1135 HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1136 ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1137 HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1138 ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1139 HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1140 HZ_GNR_UTIL_PKG.outandlog(' ');
1141
1142 OPEN c_loc_hr_error(l_country_code,
1143 l_from_location_id,
1144 l_to_location_id,
1145 l_start_date,
1146 l_end_date,
1147 l_num_workers,
1148 l_worker_number,
1149 l_usage_code,
1150 p_location_table_name);
1151 LOOP
1152 FETCH c_loc_hr_error INTO l_cur_location_id;
1153 EXIT WHEN c_loc_hr_error%NOTFOUND;
1154
1155 l_count_num_of_rec := l_count_num_of_rec + 1;
1156 l_num_locations := l_num_locations + 1;
1157
1158 -- HZ_GNR_UTIL_PKG.log('Current location ' || l_cur_location_id);
1159
1160 BEGIN
1161 HZ_GNR_PKG.validateHrLoc(l_cur_location_id,
1162 l_return_status
1163 );
1164 EXCEPTION
1165 WHEN FND_API.G_EXC_ERROR THEN
1166 l_num_err_locations := l_num_err_locations + 1;
1167 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1168 FND_MSG_PUB.Reset;
1169 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1170 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1171 END LOOP;
1172 FND_MSG_PUB.Delete_Msg;
1173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1174 l_num_err_locations := l_num_err_locations + 1;
1175 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1176 FND_MSG_PUB.Reset;
1177 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1178 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1179 END LOOP;
1180 FND_MSG_PUB.Delete_Msg;
1181 WHEN OTHERS THEN
1182 l_num_err_locations := l_num_err_locations + 1;
1183 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1184 FND_MSG_PUB.Reset;
1185 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1186 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1187 END LOOP;
1188 FND_MSG_PUB.Delete_Msg;
1189 END;
1190
1191 IF l_count_num_of_rec = l_commit_size THEN
1192 COMMIT;
1193 l_count_num_of_rec := 0;
1194 END IF;
1195
1196 END LOOP;
1197 CLOSE c_loc_hr_error;
1198 COMMIT;
1199
1200 l_num_success_locations := l_num_locations - l_num_err_locations;
1201
1202 HZ_GNR_UTIL_PKG.log(' ');
1203 HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1204 HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1205 HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1206
1207 HZ_GNR_UTIL_PKG.out(' ');
1208 HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1209 HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix ||l_num_success_locations);
1210 HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1211
1212 HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1213
1214 -- Table : HZ_LOCATIONS Run Type : ALL
1215 ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'ALL')) THEN
1216 HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : ALL');
1217 HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1218 HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1219 HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1220 ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1221 HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1222 ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1223 HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1224 HZ_GNR_UTIL_PKG.outandlog(' ');
1225
1226 FOR l_c_loc_hz IN c_loc_hz_all(l_country_code,
1227 l_from_location_id,
1228 l_to_location_id,
1229 l_start_date,
1230 l_end_date,
1231 l_num_workers,
1235 l_count_num_of_rec := l_count_num_of_rec + 1;
1232 l_worker_number
1233 )
1234 LOOP
1236 l_num_locations := l_num_locations + 1;
1237
1238 l_cur_location_id := l_c_loc_hz.LOCATION_ID;
1239 -- HZ_GNR_UTIL_PKG.log('Current location ' || l_cur_location_id);
1240
1241 BEGIN -- added to handle unexpected error from ARH package that is
1242 -- called inside location_updation_allowed check (Bug 5099223)
1243 -- Nishant (17-Mar-2006)
1244
1245 -- If the Location updation is allowed
1246 IF (HZ_GNR_UTIL_PKG.location_updation_allowed(l_cur_location_id) OR
1247 NOT(HZ_GNR_UTIL_PKG.gnr_exists(l_cur_location_id,p_location_table_name))) THEN
1248
1249 BEGIN
1250 -- delete for all usages (l_usage_code is set to ALL for runtype ALL)
1251 -- Commented the below code for bug to handle "DUMMY" locations. Bug # 5022121
1252 -- This delete gnr will take place inside create gnr.
1253 -- HZ_GNR_PKG.delete_gnr(p_locId => l_cur_location_id,
1254 -- p_locTbl => p_location_table_name,
1255 -- x_status => l_return_status
1256 -- );
1257
1258 HZ_GNR_PKG.validateLoc(
1259 P_LOCATION_ID => l_cur_location_id,
1260 P_USAGE_CODE => l_usage_code,
1261 P_ADDRESS_STYLE => l_c_loc_hz.ADDRESS_STYLE,
1262 P_COUNTRY => l_c_loc_hz.COUNTRY,
1263 P_STATE => l_c_loc_hz.STATE,
1264 P_PROVINCE => l_c_loc_hz.PROVINCE,
1265 P_COUNTY => l_c_loc_hz.COUNTY,
1266 P_CITY => l_c_loc_hz.CITY,
1267 P_POSTAL_CODE => l_c_loc_hz.POSTAL_CODE,
1268 P_POSTAL_PLUS4_CODE => l_c_loc_hz.POSTAL_PLUS4_CODE,
1269 P_ATTRIBUTE1 => l_c_loc_hz.ATTRIBUTE1,
1270 P_ATTRIBUTE2 => l_c_loc_hz.ATTRIBUTE2,
1271 P_ATTRIBUTE3 => l_c_loc_hz.ATTRIBUTE3,
1272 P_ATTRIBUTE4 => l_c_loc_hz.ATTRIBUTE4,
1273 P_ATTRIBUTE5 => l_c_loc_hz.ATTRIBUTE5,
1274 P_ATTRIBUTE6 => l_c_loc_hz.ATTRIBUTE6,
1275 P_ATTRIBUTE7 => l_c_loc_hz.ATTRIBUTE7,
1276 P_ATTRIBUTE8 => l_c_loc_hz.ATTRIBUTE8,
1277 P_ATTRIBUTE9 => l_c_loc_hz.ATTRIBUTE9,
1278 P_ATTRIBUTE10 => l_c_loc_hz.ATTRIBUTE10,
1279 P_CALLED_FROM => 'GNR',
1280 X_ADDR_VAL_LEVEL => l_addr_val_level,
1281 X_ADDR_WARN_MSG => l_addr_warn_msg,
1282 X_ADDR_VAL_STATUS => l_addr_val_status,
1283 X_STATUS => l_return_status);
1284 EXCEPTION
1285 WHEN FND_API.G_EXC_ERROR THEN
1286 l_num_err_locations := l_num_err_locations + 1;
1287 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1288 FND_MSG_PUB.Reset;
1289 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1290 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1291 END LOOP;
1292 FND_MSG_PUB.Delete_Msg;
1293 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1294 l_num_err_locations := l_num_err_locations + 1;
1295 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1296 FND_MSG_PUB.Reset;
1297 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1298 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1299 END LOOP;
1300 FND_MSG_PUB.Delete_Msg;
1301 WHEN OTHERS THEN
1302 l_num_err_locations := l_num_err_locations + 1;
1303 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1304 HZ_GNR_UTIL_PKG.out(SQLERRM);
1305 FND_MSG_PUB.Reset;
1306 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1307 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1308 END LOOP;
1309 FND_MSG_PUB.Delete_Msg;
1310 END;
1311
1312 ELSE
1313 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1314 HZ_GNR_UTIL_PKG.out('Skipped GNR - transaction exists for this location');
1315 END IF;
1316
1317 EXCEPTION WHEN OTHERS THEN
1318 l_num_err_locations := l_num_err_locations + 1;
1319 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1320 HZ_GNR_UTIL_PKG.out('Error: '||SQLERRM);
1321 FND_MSG_PUB.Reset;
1322 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1323 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1324 END LOOP;
1325 FND_MSG_PUB.Delete_Msg;
1326 END;
1327
1328 IF l_count_num_of_rec = l_commit_size THEN
1329 COMMIT;
1330 l_count_num_of_rec := 0;
1331 END IF;
1332
1333 END LOOP;
1334 COMMIT;
1335
1336 l_num_success_locations := l_num_locations - l_num_err_locations;
1337
1338 HZ_GNR_UTIL_PKG.log(' ');
1339 HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1340 HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1341 HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1342
1343 HZ_GNR_UTIL_PKG.out(' ');
1344 HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1345 HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1349
1346 HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1347
1348 HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1350 -- Table : HZ_LOCATIONS Run Type : NEW
1351 ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'NEW')) THEN
1352 HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : NEW');
1353 HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1354 HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1355 HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1356 ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1357 HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1358 ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1359 HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1360 HZ_GNR_UTIL_PKG.outandlog(' ');
1361
1362 FOR l_c_loc_hz IN c_loc_hz_new(l_country_code,
1363 l_from_location_id,
1364 l_to_location_id,
1365 l_start_date,
1366 l_end_date,
1367 l_num_workers,
1368 l_worker_number,
1369 l_usage_code,
1370 p_location_table_name)
1371 LOOP
1372 l_count_num_of_rec := l_count_num_of_rec + 1;
1373 l_num_locations := l_num_locations + 1;
1374
1375 l_cur_location_id := l_c_loc_hz.LOCATION_ID;
1376 -- HZ_GNR_UTIL_PKG.log('Current location ' || l_cur_location_id);
1377
1378 BEGIN
1379 HZ_GNR_PKG.validateLoc(
1380 P_LOCATION_ID => l_cur_location_id,
1381 P_USAGE_CODE => l_usage_code,
1382 P_ADDRESS_STYLE => l_c_loc_hz.ADDRESS_STYLE,
1383 P_COUNTRY => l_c_loc_hz.COUNTRY,
1384 P_STATE => l_c_loc_hz.STATE,
1385 P_PROVINCE => l_c_loc_hz.PROVINCE,
1386 P_COUNTY => l_c_loc_hz.COUNTY,
1387 P_CITY => l_c_loc_hz.CITY,
1388 P_POSTAL_CODE => l_c_loc_hz.POSTAL_CODE,
1389 P_POSTAL_PLUS4_CODE => l_c_loc_hz.POSTAL_PLUS4_CODE,
1390 P_ATTRIBUTE1 => l_c_loc_hz.ATTRIBUTE1,
1391 P_ATTRIBUTE2 => l_c_loc_hz.ATTRIBUTE2,
1392 P_ATTRIBUTE3 => l_c_loc_hz.ATTRIBUTE3,
1393 P_ATTRIBUTE4 => l_c_loc_hz.ATTRIBUTE4,
1394 P_ATTRIBUTE5 => l_c_loc_hz.ATTRIBUTE5,
1395 P_ATTRIBUTE6 => l_c_loc_hz.ATTRIBUTE6,
1396 P_ATTRIBUTE7 => l_c_loc_hz.ATTRIBUTE7,
1397 P_ATTRIBUTE8 => l_c_loc_hz.ATTRIBUTE8,
1398 P_ATTRIBUTE9 => l_c_loc_hz.ATTRIBUTE9,
1399 P_ATTRIBUTE10 => l_c_loc_hz.ATTRIBUTE10,
1400 P_CALLED_FROM => 'GNR',
1401 X_ADDR_VAL_LEVEL => l_addr_val_level,
1402 X_ADDR_WARN_MSG => l_addr_warn_msg,
1403 X_ADDR_VAL_STATUS => l_addr_val_status,
1404 X_STATUS => l_return_status);
1405 EXCEPTION
1406 WHEN FND_API.G_EXC_ERROR THEN
1407 l_num_err_locations := l_num_err_locations + 1;
1408 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1409 FND_MSG_PUB.Reset;
1410 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1411 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1412 END LOOP;
1413 FND_MSG_PUB.Delete_Msg;
1414 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1415 l_num_err_locations := l_num_err_locations + 1;
1416 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1417 FND_MSG_PUB.Reset;
1418 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1419 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1420 END LOOP;
1421 FND_MSG_PUB.Delete_Msg;
1422 WHEN OTHERS THEN
1423 l_num_err_locations := l_num_err_locations + 1;
1424 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1425 FND_MSG_PUB.Reset;
1426 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1427 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1428 END LOOP;
1429 FND_MSG_PUB.Delete_Msg;
1430 END;
1431
1432 IF l_count_num_of_rec = l_commit_size THEN
1433 COMMIT;
1434 l_count_num_of_rec := 0;
1435 END IF;
1436
1437 END LOOP;
1438 COMMIT;
1439
1440 l_num_success_locations := l_num_locations - l_num_err_locations;
1441
1442 HZ_GNR_UTIL_PKG.log(' ');
1443 HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1444 HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1445 HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1446
1447 HZ_GNR_UTIL_PKG.out(' ');
1448 HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1449 HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1450 HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1451
1452 HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1453
1454 -- Table : HZ_LOCATIONS Run Type : ERROR
1455 ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'ERROR')) THEN
1456 HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : ERROR');
1460 ||': To Location Id:'||NVL(TO_CHAR(l_to_location_id),'NULL'));
1457 HZ_GNR_UTIL_PKG.outandlog('Parameters: Country Code :'||NVL(l_country_code,'NULL'));
1458 HZ_GNR_UTIL_PKG.outandlog(' Usage Code :'||l_usage_code);
1459 HZ_GNR_UTIL_PKG.outandlog(' From Location Id :'||NVL(TO_CHAR(l_from_location_id),'NULL')
1461 HZ_GNR_UTIL_PKG.outandlog(' Start Date :'||NVL(TO_CHAR(l_start_date),'NULL')
1462 ||': End Date:'||NVL(TO_CHAR(l_end_date),'NULL'));
1463 HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1464 HZ_GNR_UTIL_PKG.outandlog(' ');
1465
1466 FOR l_c_loc_hz IN c_loc_hz_error(l_country_code,
1467 l_from_location_id,
1468 l_to_location_id,
1469 l_start_date,
1470 l_end_date,
1471 l_num_workers,
1472 l_worker_number,
1473 l_usage_code,
1474 p_location_table_name)
1475 LOOP
1476 l_count_num_of_rec := l_count_num_of_rec + 1;
1477 l_num_locations := l_num_locations + 1;
1478
1479 l_cur_location_id := l_c_loc_hz.LOCATION_ID;
1480 -- HZ_GNR_UTIL_PKG.log('Current location ' || l_cur_location_id);
1481
1482 BEGIN
1483 HZ_GNR_PKG.validateLoc(
1484 P_LOCATION_ID => l_cur_location_id,
1485 P_USAGE_CODE => l_usage_code,
1486 P_ADDRESS_STYLE => l_c_loc_hz.ADDRESS_STYLE,
1487 P_COUNTRY => l_c_loc_hz.COUNTRY,
1488 P_STATE => l_c_loc_hz.STATE,
1489 P_PROVINCE => l_c_loc_hz.PROVINCE,
1490 P_COUNTY => l_c_loc_hz.COUNTY,
1491 P_CITY => l_c_loc_hz.CITY,
1492 P_POSTAL_CODE => l_c_loc_hz.POSTAL_CODE,
1493 P_POSTAL_PLUS4_CODE => l_c_loc_hz.POSTAL_PLUS4_CODE,
1494 P_ATTRIBUTE1 => l_c_loc_hz.ATTRIBUTE1,
1495 P_ATTRIBUTE2 => l_c_loc_hz.ATTRIBUTE2,
1496 P_ATTRIBUTE3 => l_c_loc_hz.ATTRIBUTE3,
1497 P_ATTRIBUTE4 => l_c_loc_hz.ATTRIBUTE4,
1498 P_ATTRIBUTE5 => l_c_loc_hz.ATTRIBUTE5,
1499 P_ATTRIBUTE6 => l_c_loc_hz.ATTRIBUTE6,
1500 P_ATTRIBUTE7 => l_c_loc_hz.ATTRIBUTE7,
1501 P_ATTRIBUTE8 => l_c_loc_hz.ATTRIBUTE8,
1502 P_ATTRIBUTE9 => l_c_loc_hz.ATTRIBUTE9,
1503 P_ATTRIBUTE10 => l_c_loc_hz.ATTRIBUTE10,
1504 P_CALLED_FROM => 'GNR',
1505 X_ADDR_VAL_LEVEL => l_addr_val_level,
1506 X_ADDR_WARN_MSG => l_addr_warn_msg,
1507 X_ADDR_VAL_STATUS => l_addr_val_status,
1508 X_STATUS => l_return_status);
1509 EXCEPTION
1510 WHEN FND_API.G_EXC_ERROR THEN
1511 l_num_err_locations := l_num_err_locations + 1;
1512 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1513 FND_MSG_PUB.Reset;
1514 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1515 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1516 END LOOP;
1517 FND_MSG_PUB.Delete_Msg;
1518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1519 l_num_err_locations := l_num_err_locations + 1;
1520 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1521 FND_MSG_PUB.Reset;
1522 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1523 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1524 END LOOP;
1525 FND_MSG_PUB.Delete_Msg;
1526 WHEN OTHERS THEN
1527 l_num_err_locations := l_num_err_locations + 1;
1528 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1529 FND_MSG_PUB.Reset;
1530 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1531 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1532 END LOOP;
1533 FND_MSG_PUB.Delete_Msg;
1534 END;
1535
1536 IF l_count_num_of_rec = l_commit_size THEN
1537 COMMIT;
1538 l_count_num_of_rec := 0;
1539 END IF;
1540
1541 END LOOP;
1542 COMMIT;
1543
1544 l_num_success_locations := l_num_locations - l_num_err_locations;
1545
1546 HZ_GNR_UTIL_PKG.log(' ');
1547 HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1548 HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1549 HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1550
1551 HZ_GNR_UTIL_PKG.out(' ');
1552 HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1553 HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1554 HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1555
1556 HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1557
1558 -- Table : HZ_LOCATIONS Run Type : R12UPGRADE
1559 ELSIF ((p_location_table_name = 'HZ_LOCATIONS') AND (l_run_type = 'R12UPGRADE')) THEN
1560 HZ_GNR_UTIL_PKG.outandlog('Processing for Table : HZ_LOCATIONS and Run Type : R12UPGRADE');
1561 HZ_GNR_UTIL_PKG.outandlog('Parameters: Usage Code :'||l_usage_code);
1562 HZ_GNR_UTIL_PKG.outandlog(' Number of workers:'||TO_CHAR(l_num_workers));
1563 HZ_GNR_UTIL_PKG.outandlog('Records from HZ_LOC_ASSIGNMENTS with valid mapping are being migrated.');
1564 HZ_GNR_UTIL_PKG.outandlog(' ');
1565
1566 -- Initialize Global Variable so that later update location is avoided
1570 -- Replaced by HZ_GNR_PKG.G_API_PURPOSE
1567 -- This variable is checked in HZ_GNR_UTIL_PKG.create_gnr procedure
1568 -- Nishant (Perf Bug 5407103 4-AUG-2006)
1569 ------------------------------------------------------------------------
1571 -- Nishant (Perf Bug 5881539 16-APR-2007)
1572 ------------------------------------------------------------------------
1573 -- HZ_GNR_PVT.G_USER_ATTEMPT_COUNT := 'R12UPGRADE';
1574 HZ_GNR_PKG.G_API_PURPOSE := 'R12UPGRADE';
1575
1576 -- populate plsql table with mapped countries info
1577 FOR c_map_cnt_rec IN c_r12_upg_map_cnt LOOP
1578 mapped_country_list(mapped_country_list.COUNT+1) := c_map_cnt_rec.country_code;
1579 END LOOP;
1580
1581 FOR l_c_loc_hz IN c_r12_upg (l_num_workers, l_worker_number) -- will fetch only 1 row
1582 LOOP
1583
1584 l_map_exist := check_mapping_exist(l_c_loc_hz.COUNTRY);
1585
1586 -- Process only if mapping exist
1587 IF (l_map_exist = 'Y') THEN
1588
1589 l_count_num_of_rec := l_count_num_of_rec + 1;
1590 l_num_locations := l_num_locations + 1;
1591
1592 l_cur_location_id := l_c_loc_hz.LOCATION_ID;
1593 -- HZ_GNR_UTIL_PKG.log('Current location ' || l_cur_location_id);
1594
1595 BEGIN
1596
1597 HZ_GNR_PKG.validateLoc(
1598 P_LOCATION_ID => l_c_loc_hz.LOCATION_ID,
1599 P_USAGE_CODE => l_usage_code,
1600 P_ADDRESS_STYLE => l_c_loc_hz.ADDRESS_STYLE,
1601 P_COUNTRY => l_c_loc_hz.COUNTRY,
1602 P_STATE => l_c_loc_hz.STATE,
1603 P_PROVINCE => l_c_loc_hz.PROVINCE,
1604 P_COUNTY => l_c_loc_hz.COUNTY,
1605 P_CITY => l_c_loc_hz.CITY,
1606 P_POSTAL_CODE => l_c_loc_hz.POSTAL_CODE,
1607 P_POSTAL_PLUS4_CODE => l_c_loc_hz.POSTAL_PLUS4_CODE,
1608 P_ATTRIBUTE1 => l_c_loc_hz.ATTRIBUTE1,
1609 P_ATTRIBUTE2 => l_c_loc_hz.ATTRIBUTE2,
1610 P_ATTRIBUTE3 => l_c_loc_hz.ATTRIBUTE3,
1611 P_ATTRIBUTE4 => l_c_loc_hz.ATTRIBUTE4,
1612 P_ATTRIBUTE5 => l_c_loc_hz.ATTRIBUTE5,
1613 P_ATTRIBUTE6 => l_c_loc_hz.ATTRIBUTE6,
1614 P_ATTRIBUTE7 => l_c_loc_hz.ATTRIBUTE7,
1615 P_ATTRIBUTE8 => l_c_loc_hz.ATTRIBUTE8,
1616 P_ATTRIBUTE9 => l_c_loc_hz.ATTRIBUTE9,
1617 P_ATTRIBUTE10 => l_c_loc_hz.ATTRIBUTE10,
1618 P_CALLED_FROM => 'GNR',
1619 X_ADDR_VAL_LEVEL => l_addr_val_level,
1620 X_ADDR_WARN_MSG => l_addr_warn_msg,
1621 X_ADDR_VAL_STATUS => l_addr_val_status,
1622 X_STATUS => l_return_status);
1623 EXCEPTION
1624 WHEN FND_API.G_EXC_ERROR THEN
1625 l_num_err_locations := l_num_err_locations + 1;
1626 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1627 FND_MSG_PUB.Reset;
1628 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1629 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1630 END LOOP;
1631 FND_MSG_PUB.Delete_Msg;
1632 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1633 l_num_err_locations := l_num_err_locations + 1;
1634 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1635 FND_MSG_PUB.Reset;
1636 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1637 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1638 END LOOP;
1639 FND_MSG_PUB.Delete_Msg;
1640 WHEN OTHERS THEN
1641 l_num_err_locations := l_num_err_locations + 1;
1642 HZ_GNR_UTIL_PKG.out(l_curr_loc_msg_prefix ||' '|| l_cur_location_id);
1643 HZ_GNR_UTIL_PKG.out(SQLERRM);
1644 FND_MSG_PUB.Reset;
1645 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1646 HZ_GNR_UTIL_PKG.out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1647 END LOOP;
1648 FND_MSG_PUB.Delete_Msg;
1649 END;
1650
1651 IF l_count_num_of_rec = l_commit_size THEN
1652 COMMIT;
1653 l_count_num_of_rec := 0;
1654 END IF;
1655
1656 END IF; -- end of mapping exist check
1657 END LOOP;
1658 COMMIT;
1659
1660 l_num_success_locations := l_num_locations - l_num_err_locations;
1661
1662 HZ_GNR_UTIL_PKG.log(' ');
1663 HZ_GNR_UTIL_PKG.log('Total number of location records processed : '|| l_num_locations);
1664 HZ_GNR_UTIL_PKG.log('Number of records succeeded : '|| l_num_success_locations);
1665 HZ_GNR_UTIL_PKG.log('Number of records rejected : '|| l_num_err_locations);
1666
1667 HZ_GNR_UTIL_PKG.out(' ');
1668 HZ_GNR_UTIL_PKG.out(l_summ_loc_msg_prefix || l_num_locations);
1669 HZ_GNR_UTIL_PKG.out(l_success_loc_msg_prefix || l_num_success_locations);
1670 HZ_GNR_UTIL_PKG.out(l_err_loc_msg_prefix || l_num_err_locations);
1671
1672 HZ_GNR_UTIL_PKG.log('Geo Name Referencing process completed successfully');
1673 END IF;
1674
1675 -- Reset Global variable
1676 HZ_GNR_PKG.G_API_PURPOSE := NULL;
1677
1678 -- IF (TO_NUMBER(p_worker_number)= 1) THEN
1679 -- execute IMMEDIATE 'ALTER SESSION SET SQL_TRACE = FALSE';
1680 -- END IF;
1681
1682 EXCEPTION
1683 WHEN OTHERS THEN
1684 --IF (TO_NUMBER(p_worker_number)= 1) THEN
1685 -- execute IMMEDIATE 'ALTER SESSION SET SQL_TRACE = FALSE';
1686 --END IF;
1687
1688 -- Reset Global variable
1689 HZ_GNR_PKG.G_API_PURPOSE := NULL;
1690
1691 HZ_GNR_UTIL_PKG.outandlog('Unknown Error at Location ID: ' || l_cur_location_id || ' : ' || SQLERRM);
1695 END LOOP;
1692 HZ_GNR_UTIL_PKG.outandlog('Error: Aborting Geo Name Referencing');
1693 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1694 HZ_GNR_UTIL_PKG.outandlog(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1696
1697 retcode := 2;
1698 errbuf := 'Unexpected SQL Error at location id :' || l_cur_location_id;
1699 FND_FILE.close;
1700 END;
1701
1702 -------------------------------------------
1703 /**
1704 * PROCEDURE srchGeo
1705 *
1706 * DESCRIPTION
1707 * This private procedure is used to wrap the calls for all the
1708 * map specific procedure. This will call the various search
1709 * procedures depending on the component level in the hierarchy
1710 * for given location id and location table combination.
1711 *
1712 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1713 *
1714 * ARGUMENTS
1715 * IN:
1716 *
1717 * p_locId Location Identifier
1718 * p_locTbl Location Table
1719 * p_mapId Map Identifier
1720 *
1721 * IN OUT:
1722 * x_mapTbl Table of records that has location sequence number,
1723 * geo element, type and loc components and their values
1724 * OUT:
1725 * x_status indicates if the srchGeo was sucessfull or not.
1726 *
1727 * NOTES
1728 *
1729 *
1730 * MODIFICATION HISTORY
1731 *
1732 *
1733 */
1734
1735 PROCEDURE srchGeo(
1736 p_locId IN NUMBER,
1737 p_locTbl IN VARCHAR2,
1738 p_usage_code IN VARCHAR2,
1739 x_status OUT NOCOPY VARCHAR2
1740 ) IS
1741 BEGIN
1742 NULL;
1743 END;
1744
1745 PROCEDURE delete_gnr(
1746 p_locId IN NUMBER,
1747 p_locTbl IN VARCHAR2,
1748 x_status OUT NOCOPY VARCHAR2
1749 ) IS
1750 BEGIN
1751
1752 delete from hz_geo_name_references
1753 where location_table_name = p_locTbl
1754 and location_id = p_locId;
1755
1756 delete from hz_geo_name_reference_log
1757 where location_table_name = p_locTbl
1758 and location_id = p_locId;
1759 EXCEPTION WHEN OTHERS THEN
1760 x_status := FND_API.G_RET_STS_ERROR;
1761 RAISE FND_API.G_EXC_ERROR;
1762 END delete_gnr;
1763
1764 PROCEDURE validateHrLoc(
1765 P_LOCATION_ID IN NUMBER,
1766 X_STATUS OUT NOCOPY VARCHAR2) IS
1767 l_sql VARCHAR2(2000);
1768 l_status VARCHAR2(1);
1769 l_map_id number;
1770
1771 l_module_prefix CONSTANT VARCHAR2(30) := 'HZ:ARHGNRCB:HZ_GNR_PKG';
1772 l_module CONSTANT VARCHAR2(30) := 'ADDRESS_VALIDATION';
1773 l_debug_prefix VARCHAR2(30) := p_location_id;
1774
1775 CURSOR c_loc(p_loc_id in number) IS
1776 SELECT country,style
1777 FROM hr_locations_all
1778 WHERE location_id = p_loc_id;
1779 BEGIN
1780 x_status := fnd_api.g_ret_sts_success; -- defaulting the sucess status.
1781
1782 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1783 hz_utility_v2pub.debug
1784 (p_message => 'Begin of validation procedure validateHrLoc',
1785 p_prefix => l_debug_prefix,
1786 p_msg_level => fnd_log.level_procedure,
1787 p_module_prefix => l_module_prefix,
1788 p_module => l_module
1789 );
1790 END IF;
1791
1792 FOR l_c_loc in c_loc(P_LOCATION_ID) LOOP -- only one record will be featched.
1793 l_map_id := get_map_id(l_c_loc.country,'HR_LOCATIONS_ALL',l_c_loc.style);
1794 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1795 hz_utility_v2pub.debug
1796 (p_message => ' Map Id for the country : '||l_map_id,
1797 p_prefix => l_debug_prefix,
1798 p_msg_level => fnd_log.level_statement,
1799 p_module_prefix => l_module_prefix,
1800 p_module => l_module
1801 );
1802 END IF;
1803 IF l_map_id IS NULL THEN
1804 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1805 hz_utility_v2pub.debug
1806 (p_message => ' No mapping exists for the country. Raise error and exit.',
1807 p_prefix => l_debug_prefix,
1808 p_msg_level => fnd_log.level_statement,
1809 p_module_prefix => l_module_prefix,
1810 p_module => l_module
1811 );
1812 END IF;
1813 x_status := FND_API.G_RET_STS_ERROR;
1814 RAISE FND_API.G_EXC_ERROR;
1815 END IF;
1816 END LOOP;
1817
1818 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1819 hz_utility_v2pub.debug
1820 (p_message => 'Before calling HZ_GNR_MAP'||l_map_id||'.validateHrLoc',
1821 p_prefix => l_debug_prefix,
1822 p_msg_level => fnd_log.level_procedure,
1823 p_module_prefix => l_module_prefix,
1824 p_module => l_module
1825 );
1826 END IF;
1827
1828 l_sql := 'BEGIN HZ_GNR_MAP'||l_map_id||'.validateHrLoc(:location_id,:status); END;';
1829 BEGIN
1830 EXECUTE IMMEDIATE l_sql USING P_LOCATION_ID,OUT l_status;
1831 EXCEPTION WHEN OTHERS THEN
1832 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
1833 hz_utility_v2pub.debug
1834 (p_message => SUBSTR(' Exception when others in '||' HZ_GNR_MAP'||l_map_id||'.validateHrLoc ' ||SQLERRM,1,255),
1835 p_prefix => l_debug_prefix,
1839 );
1836 p_msg_level => fnd_log.level_exception,
1837 p_module_prefix => l_module_prefix,
1838 p_module => l_module
1840 END IF;
1841 x_status := FND_API.G_RET_STS_ERROR;
1842 RAISE FND_API.G_EXC_ERROR;
1843 END;
1844
1845 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1846 hz_utility_v2pub.debug
1847 (p_message => 'After calling HZ_GNR_MAP'||l_map_id||'.validateHrLoc Return status : '||l_status,
1848 p_prefix => l_debug_prefix,
1849 p_msg_level => fnd_log.level_procedure,
1850 p_module_prefix => l_module_prefix,
1851 p_module => l_module
1852 );
1853 END IF;
1854
1855 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1856 hz_utility_v2pub.debug
1857 (p_message => 'End of validation procedure validateHrLoc',
1858 p_prefix => l_debug_prefix,
1859 p_msg_level => fnd_log.level_procedure,
1860 p_module_prefix => l_module_prefix,
1861 p_module => l_module
1862 );
1863 END IF;
1864
1865 END validateHrLoc;
1866
1867 PROCEDURE validateLoc(
1868 P_LOCATION_ID IN NUMBER,
1869 P_USAGE_CODE IN VARCHAR2,
1870 P_ADDRESS_STYLE IN VARCHAR2,
1871 P_COUNTRY IN VARCHAR2,
1872 P_STATE IN VARCHAR2,
1873 P_PROVINCE IN VARCHAR2,
1874 P_COUNTY IN VARCHAR2,
1875 P_CITY IN VARCHAR2,
1876 P_POSTAL_CODE IN VARCHAR2,
1877 P_POSTAL_PLUS4_CODE IN VARCHAR2,
1878 P_ATTRIBUTE1 IN VARCHAR2,
1879 P_ATTRIBUTE2 IN VARCHAR2,
1880 P_ATTRIBUTE3 IN VARCHAR2,
1881 P_ATTRIBUTE4 IN VARCHAR2,
1882 P_ATTRIBUTE5 IN VARCHAR2,
1883 P_ATTRIBUTE6 IN VARCHAR2,
1884 P_ATTRIBUTE7 IN VARCHAR2,
1885 P_ATTRIBUTE8 IN VARCHAR2,
1886 P_ATTRIBUTE9 IN VARCHAR2,
1887 P_ATTRIBUTE10 IN VARCHAR2,
1888 P_CALLED_FROM IN VARCHAR2,
1889 P_LOCK_FLAG IN VARCHAR2,
1890 X_ADDR_VAL_LEVEL OUT NOCOPY VARCHAR2,
1891 X_ADDR_WARN_MSG OUT NOCOPY VARCHAR2,
1892 X_ADDR_VAL_STATUS OUT NOCOPY VARCHAR2,
1893 X_STATUS OUT NOCOPY VARCHAR2) IS
1894
1895 l_sql VARCHAR2(2000);
1896 l_usage_API VARCHAR2(30);
1897 l_call_map VARCHAR2(1);
1898 l_status VARCHAR2(1);
1899 l_addr_val_status VARCHAR2(1);
1900 l_mapId number;
1901 i number;
1902 l_cntry varchar2(2);
1903 l_addr_val_level varchar2(30);
1904 l_addr_warn_msg varchar2(2000);
1905 l_country_code VARCHAR2(10);
1906 l_usage_tbl HZ_GNR_UTIL_PKG.usage_tbl_type;
1907
1908 l_module_prefix CONSTANT VARCHAR2(30) := 'HZ:ARHGNRCB:HZ_GNR_PKG';
1909 l_module CONSTANT VARCHAR2(30) := 'ADDRESS_VALIDATION';
1910 l_debug_prefix VARCHAR2(30) := p_location_id;
1911
1912 --------
1913 ex_pkg_invalidated_state EXCEPTION;
1914 ex_pkg_altered EXCEPTION;
1915
1916 PRAGMA EXCEPTION_INIT(ex_pkg_invalidated_state, -04061);
1917 PRAGMA EXCEPTION_INIT(ex_pkg_altered, -04065);
1918
1919 --------
1920 CURSOR c_usage(p_map_id IN NUMBER) IS
1921 SELECT MAP_ID,USAGE_ID,USAGE_CODE
1922 FROM hz_address_usages
1923 WHERE map_id = p_map_id
1924 AND status_flag = 'A'
1925 ORDER BY usage_id;
1926
1927 FUNCTION get_usage_API(p_map_id in number,p_usage_code in varchar2) RETURN varchar2 IS
1928 l_usage_API VARCHAR2(30);
1929 l_sql VARCHAR2(200);
1930
1931 BEGIN
1932 l_sql := 'BEGIN :API_Name := HZ_GNR_MAP'||p_map_id||'.get_usage_API(:usage_code); END;';
1933 BEGIN
1934 BEGIN
1935 EXECUTE IMMEDIATE l_sql USING OUT l_usage_API,p_usage_code;
1936 EXCEPTION
1937 -- Fix for Bug 5521521 (If Geo Admin package is recompiled, in address page,
1938 -- while creating location, it see package as invalid (because of OA issue)
1939 -- of retaining connection pool. This is a workaround to trap the invalid package
1940 -- exception and recompile package (till OA provides fix)
1941 -- Fix done on 21-Sep-2006 (Nishant))
1942 WHEN ex_pkg_invalidated_state THEN
1943 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1944 hz_utility_v2pub.debug
1945 (p_message => ' Went in get_usage_API ex_pkg_invalidated_state EXCEPTION ',
1946 p_prefix => l_debug_prefix,
1947 p_msg_level => fnd_log.level_procedure,
1948 p_module_prefix => l_module_prefix,
1949 p_module => l_module
1950 );
1951 END IF;
1952 recompile_pkg('HZ_GNR_MAP'||l_mapId);
1953 EXECUTE IMMEDIATE l_sql USING OUT l_usage_API,p_usage_code;
1954
1955 WHEN ex_pkg_altered THEN
1956 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1957 hz_utility_v2pub.debug
1958 (p_message => ' Went in get_usage_API ex_pkg_altered EXCEPTION ',
1959 p_prefix => l_debug_prefix,
1960 p_msg_level => fnd_log.level_procedure,
1961 p_module_prefix => l_module_prefix,
1962 p_module => l_module
1963 );
1964 END IF;
1968
1965 recompile_pkg('HZ_GNR_MAP'||l_mapId);
1966 EXECUTE IMMEDIATE l_sql USING OUT l_usage_API,p_usage_code;
1967 END;
1969 EXCEPTION WHEN OTHERS THEN
1970 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
1971 hz_utility_v2pub.debug
1972 (p_message => SUBSTR(' Exception when others in get_usage_API : '||SQLERRM,1,255),
1973 p_prefix => l_debug_prefix,
1974 p_msg_level => fnd_log.level_exception,
1975 p_module_prefix => l_module_prefix,
1976 p_module => l_module
1977 );
1978 END IF;
1979 RETURN l_usage_API;
1980 END;
1981 RETURN l_usage_API;
1982 END get_usage_API;
1983
1984 BEGIN
1985
1986 x_status := fnd_api.g_ret_sts_success; -- defaulting the sucess status.
1987 x_addr_val_status := FND_api.g_ret_sts_success; -- defaulting the sucess status.
1988
1989 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1990 hz_utility_v2pub.debug
1991 (p_message => 'Begin of validation procedure validateLoc',
1992 p_prefix => l_debug_prefix,
1993 p_msg_level => fnd_log.level_procedure,
1994 p_module_prefix => l_module_prefix,
1995 p_module => l_module
1996 );
1997 END IF;
1998
1999 -- Get country code. It is possible in GNR that country name is passed.
2000 -- It does not matter what is passed, based on passed value, get country code
2001 l_country_code := get_country_code(P_COUNTRY);
2002
2003 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2004 hz_utility_v2pub.debug
2005 (p_message => ' Derived country code : '||l_country_code,
2006 p_prefix => l_debug_prefix,
2007 p_msg_level => fnd_log.level_statement,
2008 p_module_prefix => l_module_prefix,
2009 p_module => l_module
2010 );
2011 END IF;
2012
2013 -- get the mapId
2014 l_addr_val_level := HZ_GNR_PUB.GET_ADDR_VAL_LEVEL(l_country_code);
2015 x_addr_val_level := l_addr_val_level;
2016
2017 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2018 hz_utility_v2pub.debug
2019 (p_message => ' Derived address validation level : '||l_addr_val_level,
2020 p_prefix => l_debug_prefix,
2021 p_msg_level => fnd_log.level_statement,
2022 p_module_prefix => l_module_prefix,
2023 p_module => l_module
2024 );
2025 END IF;
2026
2027 l_mapid := get_map_id(l_country_code,'HZ_LOCATIONS',p_address_style);
2028
2029 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2030 hz_utility_v2pub.debug
2031 (p_message => ' Map Id for the above country : '||l_mapid,
2032 p_prefix => l_debug_prefix,
2033 p_msg_level => fnd_log.level_statement,
2034 p_module_prefix => l_module_prefix,
2035 p_module => l_module
2036 );
2037 END IF;
2038
2039 IF l_mapid IS NULL THEN
2040 IF l_addr_val_level <> 'NONE' OR p_called_from = 'GNR' THEN
2041 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2042 hz_utility_v2pub.debug
2043 (p_message => ' Validation level is not set to NONE or called from is GNR and no mapping for the country. ',
2044 p_prefix => l_debug_prefix,
2045 p_msg_level => fnd_log.level_statement,
2046 p_module_prefix => l_module_prefix,
2047 p_module => l_module
2048 );
2049 END IF;
2050 x_status := FND_API.G_RET_STS_ERROR;
2051 x_addr_val_status := FND_API.G_RET_STS_ERROR;
2052 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_MAP_FOR_COUNTRY');
2053 FND_MSG_PUB.ADD;
2054 RAISE FND_API.G_EXC_ERROR;
2055 ELSIF l_addr_val_level = 'NONE' THEN -- Validation level is set to none and no mapping for the country
2056 -- It is not required to do any further processing even for TAX.
2057 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2058 hz_utility_v2pub.debug
2059 (p_message => ' Validation level is set to NONE and no mapping for the country. It is not required to do any further processing even for TAX ',
2060 p_prefix => l_debug_prefix,
2061 p_msg_level => fnd_log.level_statement,
2062 p_module_prefix => l_module_prefix,
2063 p_module => l_module
2064 );
2065 END IF;
2066 RETURN;
2067 END IF;
2068 END IF;
2069
2070 IF p_called_from = 'GNR' AND p_usage_code = 'ALL' THEN
2071 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2072 hz_utility_v2pub.debug
2073 (p_message => ' p_called_from is GNR and usage code is ALL. So, get all valid usages into l_usage_tbl',
2074 p_prefix => l_debug_prefix,
2075 p_msg_level => fnd_log.level_statement,
2076 p_module_prefix => l_module_prefix,
2077 p_module => l_module
2078 );
2079 END IF;
2080 i := 0;
2081 FOR l_c_usage IN c_usage(l_mapid) LOOP
2082 i := i + 1;
2083 l_usage_tbl(i).USAGE_CODE := l_c_usage.USAGE_CODE;
2084 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2085 hz_utility_v2pub.debug
2089 p_module_prefix => l_module_prefix,
2086 (p_message => ' Usage code '|| i ||' : '||l_c_usage.USAGE_CODE,
2087 p_prefix => l_debug_prefix,
2088 p_msg_level => fnd_log.level_statement,
2090 p_module => l_module
2091 );
2092 END IF;
2093 END LOOP;
2094 ELSE
2095 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2096 hz_utility_v2pub.debug
2097 (p_message => ' p_called_from is not GNR and usage code is '||p_usage_code,
2098 p_prefix => l_debug_prefix,
2099 p_msg_level => fnd_log.level_statement,
2100 p_module_prefix => l_module_prefix,
2101 p_module => l_module
2102 );
2103 END IF;
2104 l_usage_tbl(1).USAGE_CODE := p_usage_code;
2105 END IF;
2106
2107 -- do processing only if there is any usage to be processed
2108 IF (l_usage_tbl.COUNT > 0) THEN
2109 FOR i in l_usage_tbl.FIRST .. l_usage_tbl.LAST LOOP
2110 l_usage_API := get_usage_API(l_mapid,l_usage_tbl(i).usage_code);
2111 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2112 hz_utility_v2pub.debug
2113 (p_message => ' Name of the usage API : '||l_usage_API,
2114 p_prefix => l_debug_prefix,
2115 p_msg_level => fnd_log.level_statement,
2116 p_module_prefix => l_module_prefix,
2117 p_module => l_module
2118 );
2119 END IF;
2120 IF l_usage_API IS NULL THEN
2121 IF l_addr_val_level <> 'NONE' OR p_called_from = 'GNR' THEN
2122 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2123 hz_utility_v2pub.debug
2124 (p_message => ' Raising the error because, validation level is other than NONE or it is called from GNR conc prog',
2125 p_prefix => l_debug_prefix,
2126 p_msg_level => fnd_log.level_statement,
2127 p_module_prefix => l_module_prefix,
2128 p_module => l_module
2129 );
2130 END IF;
2131 x_status := FND_API.G_RET_STS_ERROR;
2132 x_addr_val_status := FND_API.G_RET_STS_ERROR;
2133 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_USAGE_FOR_COUNTRY');
2134 FND_MSG_PUB.ADD;
2135 RAISE FND_API.G_EXC_ERROR;
2136 ELSIF l_addr_val_level = 'NONE' THEN -- Validation level is set to none and no usage defined for the country
2137 -- It is not required to do any further processing even for TAX.
2138 IF (l_usage_tbl(i).usage_code = 'TAX' OR i = l_usage_tbl.LAST) THEN
2139 --Even if validateion level is NONE then tax validation has to go through.
2140 --So record other than last happens to be of usage <> 'TAX' the tax will not go through.
2141 RETURN;
2142 END IF;
2143 END IF;
2144 END IF;
2145
2146 l_call_map := 'Y';
2147
2148 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2149 hz_utility_v2pub.debug
2150 (p_message => 'Before calling HZ_GNR_MAP'||l_mapId||'.'||l_usage_API,
2151 p_prefix => l_debug_prefix,
2152 p_msg_level => fnd_log.level_procedure,
2153 p_module_prefix => l_module_prefix,
2154 p_module => l_module
2155 );
2156 END IF;
2157
2158 l_sql := 'BEGIN HZ_GNR_MAP'||l_mapId||'.'||l_usage_API||' (';
2159 l_sql := l_sql || ':P_LOCATION_ID,';
2160 l_sql := l_sql || ':P_COUNTRY,';
2161 l_sql := l_sql || ':P_STATE,';
2162 l_sql := l_sql || ':P_PROVINCE,';
2163 l_sql := l_sql || ':P_COUNTY,';
2164 l_sql := l_sql || ':P_CITY,';
2165 l_sql := l_sql || ':P_POSTAL_CODE,';
2166 l_sql := l_sql || ':P_POSTAL_PLUS4_CODE,';
2167 l_sql := l_sql || ':P_ATTRIBUTE1,';
2168 l_sql := l_sql || ':P_ATTRIBUTE2,';
2169 l_sql := l_sql || ':P_ATTRIBUTE3,';
2170 l_sql := l_sql || ':P_ATTRIBUTE4,';
2171 l_sql := l_sql || ':P_ATTRIBUTE5,';
2172 l_sql := l_sql || ':P_ATTRIBUTE6,';
2173 l_sql := l_sql || ':P_ATTRIBUTE7,';
2174 l_sql := l_sql || ':P_ATTRIBUTE8,';
2175 l_sql := l_sql || ':P_ATTRIBUTE9,';
2176 l_sql := l_sql || ':P_ATTRIBUTE10,';
2177 l_sql := l_sql || ':P_LOCK_FLAG,';
2178 l_sql := l_sql || ':X_CALL_MAP,';
2179 l_sql := l_sql || ':P_CALLED_FROM,';
2180 l_sql := l_sql || ':P_ADDR_VAL_LEVEL,';
2181 l_sql := l_sql || ':X_ADDR_WARN_MSG,';
2182 l_sql := l_sql || ':X_ADDR_VAL_STATUS,';
2183 l_sql := l_sql || ':X_STATUS';
2184 l_sql := l_sql || '); END;';
2185
2186 BEGIN
2187 EXECUTE IMMEDIATE l_sql USING P_LOCATION_ID,
2188 P_COUNTRY,
2189 P_STATE,
2190 P_PROVINCE,
2191 P_COUNTY,
2192 P_CITY,
2193 P_POSTAL_CODE,
2194 P_POSTAL_PLUS4_CODE,
2195 P_ATTRIBUTE1,
2196 P_ATTRIBUTE2,
2197 P_ATTRIBUTE3,
2198 P_ATTRIBUTE4,
2199 P_ATTRIBUTE5,
2200 P_ATTRIBUTE6,
2201 P_ATTRIBUTE7,
2202 P_ATTRIBUTE8,
2203 P_ATTRIBUTE9,
2204 P_ATTRIBUTE10,
2205 P_LOCK_FLAG,
2209 OUT X_ADDR_WARN_MSG,
2206 IN OUT L_CALL_MAP,
2207 P_CALLED_FROM,
2208 l_addr_val_level,
2210 IN OUT X_ADDR_VAL_STATUS,
2211 IN OUT L_STATUS;
2212
2213 x_status := L_STATUS;
2214
2215 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2216 hz_utility_v2pub.debug
2217 (p_message => 'After calling HZ_GNR_MAP'||l_mapId||'.'||l_usage_API,
2218 p_prefix => l_debug_prefix,
2219 p_msg_level => fnd_log.level_procedure,
2220 p_module_prefix => l_module_prefix,
2221 p_module => l_module
2222 );
2223 END IF;
2224
2225 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2226 hz_utility_v2pub.debug
2227 (p_message => ' Return status : '||L_STATUS|| ' and address validation status : '||X_ADDR_VAL_STATUS,
2228 p_prefix => l_debug_prefix,
2229 p_msg_level => fnd_log.level_procedure,
2230 p_module_prefix => l_module_prefix,
2231 p_module => l_module
2232 );
2233 END IF;
2234
2235 EXCEPTION
2236 WHEN OTHERS THEN
2237 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
2238 hz_utility_v2pub.debug
2239 (p_message => SUBSTR(' Exception when others in '||' HZ_GNR_MAP'||l_mapId||'.'||l_usage_API ||' : ' ||SQLERRM,1,255),
2240 p_prefix => l_debug_prefix,
2241 p_msg_level => fnd_log.level_exception,
2242 p_module_prefix => l_module_prefix,
2243 p_module => l_module
2244 );
2245 END IF;
2246 x_status := FND_API.G_RET_STS_ERROR;
2247 END;
2248
2249 IF p_called_from = 'VALIDATE' and l_usage_tbl(i).usage_code <> 'TAX' and p_location_id IS NOT NULL THEN
2250 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2251 hz_utility_v2pub.debug
2252 (p_message => 'p_called_from is VALIDATE and usage code is not TAX and location_id IS NOT NULL. So, call tax validation.',
2253 p_prefix => l_debug_prefix,
2254 p_msg_level => fnd_log.level_statement,
2255 p_module_prefix => l_module_prefix,
2256 p_module => l_module
2257 );
2258 END IF;
2259 l_usage_API := get_usage_API(l_mapid,'TAX');
2260 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2261 hz_utility_v2pub.debug
2262 (p_message => ' Name of the usage API : '||l_usage_API,
2263 p_prefix => l_debug_prefix,
2264 p_msg_level => fnd_log.level_statement,
2265 p_module_prefix => l_module_prefix,
2266 p_module => l_module
2267 );
2268 END IF;
2269 IF l_usage_API IS NULL THEN -- there is no mapping for Tax and no GNR processing or TAX is required.
2270 RETURN;
2271 END IF;
2272
2273 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2274 hz_utility_v2pub.debug
2275 (p_message => 'Before calling HZ_GNR_MAP'||l_mapId||'.'||l_usage_API,
2276 p_prefix => l_debug_prefix,
2277 p_msg_level => fnd_log.level_procedure,
2278 p_module_prefix => l_module_prefix,
2279 p_module => l_module
2280 );
2281 END IF;
2282
2283 l_sql := 'BEGIN HZ_GNR_MAP'||l_mapId||'.'||l_usage_API||' (';
2284 l_sql := l_sql || ':P_LOCATION_ID,';
2285 l_sql := l_sql || ':P_COUNTRY,';
2286 l_sql := l_sql || ':P_STATE,';
2287 l_sql := l_sql || ':P_PROVINCE,';
2288 l_sql := l_sql || ':P_COUNTY,';
2289 l_sql := l_sql || ':P_CITY,';
2290 l_sql := l_sql || ':P_POSTAL_CODE,';
2291 l_sql := l_sql || ':P_POSTAL_PLUS4_CODE,';
2292 l_sql := l_sql || ':P_ATTRIBUTE1,';
2293 l_sql := l_sql || ':P_ATTRIBUTE2,';
2294 l_sql := l_sql || ':P_ATTRIBUTE3,';
2295 l_sql := l_sql || ':P_ATTRIBUTE4,';
2296 l_sql := l_sql || ':P_ATTRIBUTE5,';
2297 l_sql := l_sql || ':P_ATTRIBUTE6,';
2298 l_sql := l_sql || ':P_ATTRIBUTE7,';
2299 l_sql := l_sql || ':P_ATTRIBUTE8,';
2300 l_sql := l_sql || ':P_ATTRIBUTE9,';
2301 l_sql := l_sql || ':P_ATTRIBUTE10,';
2302 l_sql := l_sql || ':P_LOCK_FLAG,';
2303 l_sql := l_sql || ':X_CALL_MAP,';
2304 l_sql := l_sql || ':P_CALLED_FROM,';
2305 l_sql := l_sql || ':P_ADDR_VAL_LEVEL,';
2306 l_sql := l_sql || ':X_ADDR_WARN_MSG,';
2307 l_sql := l_sql || ':X_ADDR_VAL_STATUS,';
2308 l_sql := l_sql || ':X_STATUS';
2309 l_sql := l_sql || '); END;';
2310
2311
2312 BEGIN
2313 EXECUTE IMMEDIATE l_sql USING P_LOCATION_ID,
2314 P_COUNTRY,
2315 P_STATE,
2316 P_PROVINCE,
2317 P_COUNTY,
2318 P_CITY,
2319 P_POSTAL_CODE,
2320 P_POSTAL_PLUS4_CODE,
2321 P_ATTRIBUTE1,
2322 P_ATTRIBUTE2,
2323 P_ATTRIBUTE3,
2324 P_ATTRIBUTE4,
2328 P_ATTRIBUTE8,
2325 P_ATTRIBUTE5,
2326 P_ATTRIBUTE6,
2327 P_ATTRIBUTE7,
2329 P_ATTRIBUTE9,
2330 P_ATTRIBUTE10,
2331 P_LOCK_FLAG,
2332 IN OUT L_CALL_MAP,
2333 P_CALLED_FROM,
2334 l_addr_val_level,
2335 OUT L_ADDR_WARN_MSG,
2336 IN OUT L_ADDR_VAL_STATUS,
2337 IN OUT L_STATUS;
2338
2339 -- even if Tax validation fails no need to change the overall status x_status := L_STATUS;
2340
2341 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2342 hz_utility_v2pub.debug
2343 (p_message => 'After calling HZ_GNR_MAP'||l_mapId||'.'||l_usage_API,
2344 p_prefix => l_debug_prefix,
2345 p_msg_level => fnd_log.level_procedure,
2346 p_module_prefix => l_module_prefix,
2347 p_module => l_module
2348 );
2349 END IF;
2350
2351 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2352 hz_utility_v2pub.debug
2353 (p_message => ' Return status : '||L_STATUS|| ' and address validation status : '||L_ADDR_VAL_STATUS,
2354 p_prefix => l_debug_prefix,
2355 p_msg_level => fnd_log.level_procedure,
2356 p_module_prefix => l_module_prefix,
2357 p_module => l_module
2358 );
2359 END IF;
2360
2361 EXCEPTION WHEN OTHERS THEN
2362 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
2363 hz_utility_v2pub.debug
2364 (p_message => SUBSTR(' Exception when others in '||' HZ_GNR_MAP'||l_mapId||'.'||l_usage_API ||' : ' ||SQLERRM,1,255),
2365 p_prefix => l_debug_prefix,
2366 p_msg_level => fnd_log.level_exception,
2367 p_module_prefix => l_module_prefix,
2368 p_module => l_module
2369 );
2370 END IF;
2371 x_status := FND_API.G_RET_STS_ERROR;
2372 END;
2373 END IF;
2374 END LOOP;
2375 ELSE
2376 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2377 hz_utility_v2pub.debug
2378 (p_message => ' There is no active usage available for validation',
2379 p_prefix => l_debug_prefix,
2380 p_msg_level => fnd_log.level_statement,
2381 p_module_prefix => l_module_prefix,
2382 p_module => l_module
2383 );
2384 END IF;
2385 -- no active usage available to be checked
2386 -- we want to raise error message only if validation level is other than NONE
2387 -- If it is called from GNR conc prog, we will always try to create GNR
2388 -- irrespective of validation level.
2389 IF ((l_addr_val_level <> 'NONE') OR (p_called_from = 'GNR')) THEN
2390 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2391 hz_utility_v2pub.debug
2392 (p_message => ' Raising the error because, validation level is other than NONE or it is called from GNR conc prog',
2393 p_prefix => l_debug_prefix,
2394 p_msg_level => fnd_log.level_statement,
2395 p_module_prefix => l_module_prefix,
2396 p_module => l_module
2397 );
2398 END IF;
2399 x_status := FND_API.G_RET_STS_ERROR;
2400 x_addr_val_status := FND_API.G_RET_STS_ERROR;
2401 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_USAGE_FOR_COUNTRY');
2402 FND_MSG_PUB.ADD;
2403 RAISE FND_API.G_EXC_ERROR;
2404 ELSE
2405 NULL;
2406 END IF;
2407 END IF;
2408 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2409 hz_utility_v2pub.debug
2413 p_module_prefix => l_module_prefix,
2410 (p_message => 'End of validation procedure validateLoc',
2411 p_prefix => l_debug_prefix,
2412 p_msg_level => fnd_log.level_procedure,
2414 p_module => l_module
2415 );
2416 END IF;
2417 END validateLoc;
2418 END;
[Home] [Help]