1 PACKAGE BODY HZ_GNR_UTIL_PKG AS
2 /*$Header: ARHGNRUB.pls 120.33 2007/11/15 01:42:50 nsinghai ship $ */
3
4
5 --------------------------------------
6 -- declaration of private global varibles
7 --------------------------------------
8
9 g_debug_count NUMBER := 0;
10 --g_debug BOOLEAN := FALSE;
11
12 --------------------------------------
13 -- declaration of private procedures and functions
14 --------------------------------------
15
16 /*PROCEDURE enable_debug;
17
18 PROCEDURE disable_debug;
19 */
20
21 --------------------------------------
22 -- private procedures and functions
23 --------------------------------------
24
25 --------------------------------------
26 /*PROCEDURE enable_debug IS
27 BEGIN
28 g_debug_count := g_debug_count + 1;
29
30 IF g_debug_count = 1 THEN
31 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
32 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
33 THEN
34 hz_utility_v2pub.enable_debug;
35 g_debug := TRUE;
36 END IF;
37 END IF;
38 END enable_debug; -- end procedure
39 */
40 --------------------------------------
41 --------------------------------------
42 /*PROCEDURE disable_debug IS
43 BEGIN
44
45 IF g_debug THEN
46 g_debug_count := g_debug_count - 1;
47 IF g_debug_count = 0 THEN
48 hz_utility_v2pub.disable_debug;
49 g_debug := FALSE;
50 END IF;
51 END IF;
52
53 END disable_debug;
54 */
55 --------------------------------------
56 /**
57 Function : gnr_exists
58
59 DESCRIPTION :
60 Function to tell if the GNR already processed for a given
61 location record or not.
62
63 ARGUMENTS :
64 IN p_location_id NUMBER
65 IN p_location_table_name VARCHAR2
66
67 RETURNS : BOOLEAN
68 TRUE : If GNR exists
69 FALSE : If GNR does not exists
70
71 MODIFICATION HISTORY:
72 17-FEB-2006 Baiju.nair Created
73
74 **/
75 FUNCTION gnr_exists(p_location_id IN NUMBER,
76 p_location_table_name IN VARCHAR2) RETURN BOOLEAN IS
77
78 CURSOR c_gnr IS
79 SELECT MAP_STATUS
80 FROM HZ_GEO_NAME_REFERENCE_LOG
81 WHERE LOCATION_TABLE_NAME = p_location_table_name
82 AND LOCATION_ID = p_location_id;
83
84 l_address_status VARCHAR2(30);
85 l_return_value BOOLEAN;
86
87 BEGIN
88
89 OPEN c_gnr;
90 FETCH c_gnr INTO l_address_status;
91 IF c_gnr%NOTFOUND THEN
92 l_return_value := FALSE;
93 ELSE
94 l_return_value := TRUE;
95 END IF;
96 CLOSE c_gnr;
97
98 RETURN l_return_value;
99
100 END gnr_exists;
101
102 --------------------------------------
103
104 /**
105 Function : location_updation_allowed
106
107 DESCRIPTION :
108 Function to tell if the location can be updated or not. It directly calls
109 ARH_ADDR_PKG.check_tran_for_all_accts to do this validation. This function is
110 just a wrapper for ease of use in GNR code
111
112 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED :
113 ARH_ADDR_PKG
114
115 ARGUMENTS :
116 IN p_location_id NUMBER
117
118 RETURNS : BOOLEAN
119 TRUE : Location updation is allowed
120 FALSE : Location updation is not allowed
121
122 MODIFICATION HISTORY:
123 16-FEB-2006 Nishant Singhai Created
124
125 **/
126 FUNCTION location_updation_allowed(p_location_id IN NUMBER) RETURN BOOLEAN IS
127 BEGIN
128 -- Tax location Validation
129 IF ARH_ADDR_PKG.check_tran_for_all_accts(p_location_id) THEN
130 -- Transaction exists
131 RETURN FALSE;
132 ELSE
133 -- Transaction does not exists. OK to update location.
134 RETURN TRUE;
135 END IF;
136 END location_updation_allowed;
137
138 /**
139 Procedure : pre_location_update
140
141 DESCRIPTION :
142 Procedure to do pre-update processing for a given location record. This will
143 be used in GNR program, where it updates the location components.
144
145 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED :
146 HZ_LOCATION_V2PUB
147 hz_fuzzy_pub
148 hz_timezone_pub
149
150 ARGUMENTS :
151 IN p_old_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
152
153 IN OUT p_new_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
154 IN OUT p_other_location_params HZ_GNR_UTIL_PKG.location_other_param_rec_type
155 (extendible - for future use)
156
157 MODIFICATION HISTORY:
158 16-FEB-2006 Nishant Singhai Created
159 **/
160 PROCEDURE pre_location_update (
161 p_old_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
162 p_new_location_rec IN OUT NOCOPY HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
163 p_other_location_params IN OUT NOCOPY HZ_GNR_UTIL_PKG.loc_other_param_rec_type,
164 x_return_status OUT NOCOPY VARCHAR2,
165 x_msg_count OUT NOCOPY NUMBER,
166 x_msg_data OUT NOCOPY VARCHAR2
167 ) IS
168
169 l_changed_flag VARCHAR2(10);
170 l_message_count NUMBER;
171 l_msg_count NUMBER;
172 l_msg_data VARCHAR2(1000);
173 l_return_status VARCHAR2(30);
174
175 BEGIN
176 x_return_status := FND_API.G_RET_STS_SUCCESS;
177
178 -- get timezone id for changed location, if timezone_id is NULL
179 -- Same code as in HZ_LOCATION_V2PUB.UPDATE_LOCATION (to make it modular)
180 IF (p_new_location_rec.country IS NOT NULL AND
181 NVL(UPPER(p_old_location_rec.country), fnd_api.g_miss_char) <> UPPER(p_new_location_rec.country))
182 OR (p_new_location_rec.city IS NOT NULL AND
183 NVL(UPPER(p_old_location_rec.city), fnd_api.g_miss_char) <> UPPER(p_new_location_rec.city))
184 OR (p_new_location_rec.state IS NOT NULL AND
185 NVL(UPPER(p_old_location_rec.state), fnd_api.g_miss_char)<> UPPER(p_new_location_rec.state))
186 OR (p_new_location_rec.postal_code IS NOT NULL AND
187 NVL(UPPER(p_old_location_rec.postal_code), fnd_api.g_miss_char) <> UPPER(p_new_location_rec.postal_code))
188 THEN
189 l_changed_flag := 'Y';
190 END IF;
191
192 IF ((l_changed_flag = 'Y') AND
193 (p_new_location_rec.timezone_id IS NULL
194 OR p_new_location_rec.timezone_id = fnd_api.g_miss_num)
195 )
196 THEN
197
198 l_message_count := fnd_msg_pub.count_msg();
199 hz_timezone_pub.get_timezone_id(
200 p_api_version => 1.0,
201 p_init_msg_list => FND_API.G_FALSE,
202 p_postal_code => p_new_location_rec.postal_code,
203 p_city => p_new_location_rec.city,
204 p_state => p_new_location_rec.state,
205 p_country => p_new_location_rec.country,
206 x_timezone_id => p_new_location_rec.timezone_id,
207 x_return_status => l_return_status ,
208 x_msg_count => l_msg_count ,
209 x_msg_data => l_msg_data);
210
211 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
212 -- we don't raise error
213 p_new_location_rec.timezone_id := fnd_api.g_miss_num;
214 FOR i IN 1..(l_msg_count - l_message_count) LOOP
215 fnd_msg_pub.delete_msg(l_msg_count - l_message_count + 1 - i);
216 END LOOP;
217 l_return_status := FND_API.G_RET_STS_SUCCESS;
218 END IF;
219 END IF;
220
221 -- call address key generation program
222 p_new_location_rec.address_key := hz_fuzzy_pub.generate_key (
223 'ADDRESS',
224 NULL,
225 p_new_location_rec.address1,
226 p_new_location_rec.address2,
227 p_new_location_rec.address3,
228 p_new_location_rec.address4,
229 p_new_location_rec.postal_code,
230 NULL,
231 NULL
232 );
233
234 -- x_return_status to be ignored as it it be always success
235 x_return_status := l_return_status;
236
237 EXCEPTION WHEN OTHERS THEN
238 NULL;
239 END pre_location_update;
240
241 /**
242 Procedure : post_location_update
243
244 DESCRIPTION :
245 Procedure to do post-update processing for a given location record. This will
246 be used in GNR program, where it updates the location components.
247
248 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED :
249 HZ_LOCATION_V2PUB
250 HZ_UTILITY_V2PUB
251 HZ_DQM_SYNC
252 HZ_BUSINESS_EVENT_V2PVT
253 HZ_POPULATE_BOT_PKG
254
255 ARGUMENTS :
256 IN p_old_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
257
258 IN OUT p_new_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
259 IN OUT p_other_location_params HZ_GNR_UTIL_PKG.location_other_param_rec_type
260 (extendible - for future use)
261
262 MODIFICATION HISTORY:
263 16-FEB-2006 Nishant Singhai Created
264 **/
265 PROCEDURE post_location_update (
266 p_old_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
267 p_new_location_rec IN OUT NOCOPY HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
268 p_other_location_params IN OUT NOCOPY HZ_GNR_UTIL_PKG.loc_other_param_rec_type,
269 x_return_status OUT NOCOPY VARCHAR2,
270 x_msg_count OUT NOCOPY NUMBER,
271 x_msg_data OUT NOCOPY VARCHAR2
272 ) IS
273
274 l_last_updated_by NUMBER; -- hz_utility_v2pub.last_updated_by;
275 l_creation_date DATE; -- hz_utility_v2pub.creation_date;
276 l_created_by NUMBER; -- hz_utility_v2pub.created_by;
277 l_last_update_date DATE; -- hz_utility_v2pub.last_update_date;
278 l_last_update_login NUMBER; -- hz_utility_v2pub.last_update_login;
279 l_program_id NUMBER; -- hz_utility_v2pub.program_id;
280 l_conc_login_id NUMBER; -- fnd_global.conc_login_id;
281 l_program_application_id NUMBER; --hz_utility_v2pub.program_application_id;
282 l_request_id NUMBER; -- NVL(hz_utility_v2pub.request_id, -1);
283 l_program_update_date DATE; -- hz_utility_v2pub.program_update_date;
284
285 BEGIN
286
287 -- Initialize variables (perf improvement bug 5130993)
288 l_last_updated_by := hz_utility_v2pub.last_updated_by;
289 l_creation_date := hz_utility_v2pub.creation_date;
290 l_created_by := hz_utility_v2pub.created_by;
291 l_last_update_date := hz_utility_v2pub.last_update_date;
292 l_last_update_login := hz_utility_v2pub.last_update_login;
293 l_program_id := hz_utility_v2pub.program_id;
294 l_conc_login_id := fnd_global.conc_login_id;
295 l_program_application_id := hz_utility_v2pub.program_application_id;
296 l_request_id := NVL(hz_utility_v2pub.request_id, -1);
297 l_program_update_date := hz_utility_v2pub.program_update_date;
298
299 -- update de-normalized location components in HZ_PARTIES for parties
300 -- having this location as an identifying location. There can be multiple
301 -- such parties.
302 DECLARE
303 l_party_id NUMBER;
304
305 CURSOR c1 IS
306 SELECT hps.party_id
307 FROM hz_party_sites hps
308 WHERE hps.location_id = p_new_location_rec.location_id
309 AND hps.identifying_address_flag = 'Y';
310 BEGIN
311 IF (p_new_location_rec.country IS NOT NULL AND
312 NVL(UPPER(p_old_location_rec.country), fnd_api.g_miss_char) <> UPPER(p_new_location_rec.country))
313 OR (p_new_location_rec.address1 IS NOT NULL AND
314 NVL(UPPER(p_old_location_rec.address1),fnd_api.g_miss_char) <> UPPER(p_new_location_rec.address1))
315 OR (p_new_location_rec.address2 IS NOT NULL AND
316 NVL(UPPER(p_old_location_rec.address2),fnd_api.g_miss_char) <> UPPER(p_new_location_rec.address2))
317 OR (p_new_location_rec.address3 IS NOT NULL AND
318 NVL(UPPER(p_old_location_rec.address3),fnd_api.g_miss_char) <> UPPER(p_new_location_rec.address3))
319 OR (p_new_location_rec.address4 IS NOT NULL AND
320 NVL(UPPER(p_old_location_rec.address4),fnd_api.g_miss_char) <> UPPER(p_new_location_rec.address4))
321 OR (p_new_location_rec.city IS NOT NULL AND
322 NVL(UPPER(p_old_location_rec.city), fnd_api.g_miss_char) <> UPPER(p_new_location_rec.city))
323 OR (p_new_location_rec.postal_code IS NOT NULL AND
324 NVL(UPPER(p_old_location_rec.postal_code), fnd_api.g_miss_char) <> UPPER(p_new_location_rec.postal_code))
325 OR (p_new_location_rec.state IS NOT NULL AND
326 NVL(UPPER(p_old_location_rec.state), fnd_api.g_miss_char) <> UPPER(p_new_location_rec.state))
327 OR (p_new_location_rec.province IS NOT NULL AND
328 NVL(UPPER(p_old_location_rec.province),fnd_api.g_miss_char) <> UPPER(p_new_location_rec.province))
329 OR (p_new_location_rec.county IS NOT NULL AND
330 NVL(UPPER(p_old_location_rec.county), fnd_api.g_miss_char) <> UPPER(p_new_location_rec.county))
331 THEN
332 BEGIN
333 OPEN c1;
334 LOOP
335 FETCH c1 INTO l_party_id;
336 EXIT WHEN c1%NOTFOUND;
337
338 -- Debug info.
339 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
340 hz_utility_v2pub.debug(p_message=>'Denormalizing party with ID: ' ||
341 l_party_id,
342 p_prefix =>'INFO:',
343 p_msg_level=>fnd_log.level_statement);
344 END IF;
345
346 -- Bug 2246041: Denormalization should not be done for Remit To
347 -- Addresses.
348
349 IF l_party_id <> -1 THEN
350 SELECT party_id
351 INTO l_party_id
352 FROM hz_parties
353 WHERE party_id = l_party_id
354 FOR UPDATE NOWAIT;
355
356 UPDATE hz_parties
357 SET country = p_new_location_rec.country,
358 address1 = p_new_location_rec.address1,
359 address2 = p_new_location_rec.address2,
360 address3 = p_new_location_rec.address3,
361 address4 = p_new_location_rec.address4,
362 city = p_new_location_rec.city,
363 postal_code = p_new_location_rec.postal_code,
364 state = p_new_location_rec.state,
365 province = p_new_location_rec.province,
366 county = p_new_location_rec.county,
367 last_update_date = l_last_update_date,
368 last_updated_by = l_last_updated_by,
369 last_update_login = l_last_update_login,
370 request_id = l_request_id,
371 program_id = l_program_id,
372 program_application_id = l_program_application_id,
373 program_update_date = l_program_update_date
374 WHERE party_id = l_party_id;
375
376 END IF; -- Only if address is not a Remit to.
377 END LOOP;
378 CLOSE c1;
379
380 EXCEPTION
381 WHEN OTHERS THEN
382 /*
383 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
384 fnd_message.set_token('TABLE', 'HZ_PARTIES');
385 fnd_msg_pub.add;
386 */
387 CLOSE c1;
388 -- RAISE fnd_api.g_exc_error;
389 END;
390 END IF; -- location components have been modified
391 END;
392
393 -- Call to indicate location update to DQM
394 HZ_DQM_SYNC.sync_location(p_new_location_rec.location_id,'U');
395
396 -- Invoke business event system.
397
398 -- SSM SST Integration and Extension
399 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
400 -- There is no need to check if the data-source is selected.
401 -- l_old_location_rec.orig_system := p_location_rec.orig_system;
402 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
403 HZ_BUSINESS_EVENT_V2PVT.update_location_event (
404 p_new_location_rec,
405 p_old_location_rec);
406 END IF;
407
408 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
409 -- populate function for integration service
410 HZ_POPULATE_BOT_PKG.pop_hz_locations(
411 p_operation => 'U',
412 p_location_id => p_new_location_rec.location_id );
413 END IF;
414
415 EXCEPTION WHEN OTHERS THEN
416 NULL;
417 END post_location_update;
418 ---------------------------------------
419
420 /**
421 * procedure delGNR
422 *
423 * DESCRIPTION
424 * This is to delete the rows from GNR table
425 * for a given combination of location id and table
426 *
427 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
428 * None. Only gnrins and gnrl in this package
429 * procedure use this.
430 * ARGUMENTS
431 * IN:
432 *
433 * p_locId Location Identifier
434 * p_locTbl Location Table
435 *
436 * OUT:
437 * x_status Y in case of success, otherwise error message name
438 *
439 * NOTES
440 *
441 *
442 * MODIFICATION HISTORY
443 *
444 *
445 */
446 --------------------------------------
447 PROCEDURE delGNR (
448 p_locid IN number,
449 p_loctbl IN varchar2,
450 x_status OUT NOCOPY varchar2
451 ) IS
452
453 BEGIN
454
455 -- initializing the return status
456 x_status := fnd_api.g_ret_sts_success;
457
458 -- delete thing the location id and table name combination from gnr
459
460 DELETE FROM hz_geo_name_references
461 WHERE location_id = p_locid AND
462 location_table_name = p_loctbl;
463 EXCEPTION
464 WHEN others THEN
465 --dbms_output.put_line('error in del gnr' ||sqlerrm);
466 x_status := fnd_api.g_ret_sts_unexp_error;
467 END delGNR;
468 --------------------------------------
469 -- procedures and functions
470 --------------------------------------
471 /**
472 * PROCEDURE getMapId
473 *
474 * DESCRIPTION
475 * This private procedure is used to gets the
476 * map identifier for a given location id, loc table name
477 *
478 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
479 *
480 * ARGUMENTS
481 * IN:
482 *
483 * p_locId Location Identifier
484 * p_locTbl Location Table
485 *
486 * OUT:
487 * x_mapId map identifier
488 * x_status Y in case of success, otherwise error message name
489 *
490 * NOTES
491 *
492 *
493 * MODIFICATION HISTORY
494 *
495 *
496 */
497 ----------------------------------------------
498 PROCEDURE getMapId(
499 p_locId IN NUMBER,
500 p_locTbl IN VARCHAR2,
501 x_cntry OUT NOCOPY VARCHAR2,
502 x_mapId OUT NOCOPY NUMBER,
503 x_status OUT NOCOPY VARCHAR2
504 ) IS
505
506 l_addrstyle varchar2(30);
507 l_sql1 varchar2(1000);
508 l_eflag varchar2(2);
509
510 BEGIN
511 x_status := fnd_api.g_ret_sts_success; -- defaulting the sucess status.
512 /* flow:
513 1. getcntrystyle()
514 2. if both addrstyle and country are not null,
515 get mapId using both else use country
516 */
517 --dbms_output.put_line('***bfr getcntrystyle');
518 hz_gnr_util_pkg.getcntrystyle(p_locid,p_loctbl,x_cntry,l_addrstyle,x_status);
519
520 IF (x_status <> fnd_api.g_ret_sts_success) THEN
521 -- there is some error in getcntrystyle(), hence exit
522 RAISE FND_API.G_EXC_ERROR;
523 END IF;
524 --dbms_output.put_line('***after getcntrystyle');
525
526 -- as getcntrystyle() validates the country - in this procedure
527 -- there is no need to validate it again.
528
529 /* if performance mandates, the following execute immediate stmts against
530 the map tbl can be converted into cursor stmts later.- srikanth
531 */
532
533 -- no data found can come from any of the three sqls in this procedure.
534 -- hence, to identify them appropriately to give user friedly mesg
535 -- l_eflag is used.
536
537 IF (l_addrstyle IS NULL) THEN
538 l_sql1 := 'select map_id from hz_geo_struct_map where loc_tbl_name =:tbl and
539 country_code = :cntry and address_style is null';
540 l_eflag := 'AN'; -- address style null case
541
542 execute IMMEDIATE l_sql1 INTO x_mapId USING IN p_loctbl, x_cntry;
543 ELSE
544 l_sql1 := 'select map_id from hz_geo_struct_map where loc_tbl_name =:tbl and
545 country_code = :cntry and address_style = :style';
546 l_eflag := 'A'; -- address style case
547
548 execute IMMEDIATE l_sql1 INTO x_mapId USING IN p_loctbl, x_cntry, l_addrstyle;
549 END IF;
550
551 EXCEPTION
552 WHEN no_data_found THEN
553
554 IF (l_eflag IS NULL) THEN
555 -- no map details for given map record
556 x_status := fnd_api.G_RET_STS_ERROR;
557 --dbms_output.put_line('***bfr mesglog() in exception');
558 mesglog(p_locid,p_loctbl,'HZ_GEO_NO_MAP_DTL', 'COUNTRY_CODE', x_cntry, NULL, NULL);
559 RAISE FND_API.G_EXC_ERROR;
560 ELSIF (l_eflag = 'AN') THEN
561 -- address style case is null case
562 x_status := fnd_api.G_RET_STS_ERROR;
563 --dbms_output.put_line('***bfr mesglog() when address style is null');
564 mesglog(p_locid,p_loctbl,'HZ_GEO_NOMAP', 'COUNTRY', x_cntry, NULL, NULL);
565 RAISE FND_API.G_EXC_ERROR;
566 ELSIF (l_eflag = 'A') THEN
567 -- address style and country code combinatio does not exist
568 x_status := fnd_api.G_RET_STS_ERROR;
569 --dbms_output.put_line('***bfr mesglog() when address style is not null');
570 mesglog(p_locid,p_loctbl,'HZ_GEO_NOMAP_ASTYLE', 'COUNTRY', x_cntry, 'STYLE', l_addrstyle);
571 RAISE FND_API.G_EXC_ERROR;
572 ELSE
573 RAISE FND_API.G_EXC_ERROR;
574 END IF;
575 END getMapId;
576
577 -----------------------------------------
578
579 --------------------------------------
580 /**
581 * procedure getmaprec
582 *
583 * description
584 * this private procedure is used to gets
585 * 1. the map record for a given location.
586 * 2. populates component values from loc rec
587 *
588 * external procedures/functions accessed
589 *
590 * arguments
591 * in:
592 *
593 * p_locid location identifier
594 * p_loctbl location table
595 *
596 * out:
597 *
598 * x_mltbl table of records that has
599 * geo element, type and loc components and their values
600 * x_status y in case of success, otherwise error message name
601 * x_mapId map identifier
602 * x_cntry country code
603 *
604 * notes
605 * following are the exceptions raised
606 * hz_geo_nomap
607 * hz_geo_nomap_astyle
608 * HZ_GEO_NO_MAP_DTL
609 * modification history
610 *
611 *
612 */
613 PROCEDURE getmaprec(
614 p_locid IN number,
615 p_loctbl IN varchar2,
616 x_mltbl OUT NOCOPY maploc_rec_tbl_type,
617 x_mapId OUT NOCOPY NUMBER,
618 x_cntry OUT NOCOPY varchar2,
619 x_status OUT NOCOPY varchar2
620 )IS
621
622 -- temp variable declaration
623 -- l_cntry varchar2(2);
624 l_addrstyle varchar2(30);
625 l_sql1 varchar2(1000);
626 l_mapdtl hz_geo_struct_map_dtl%ROWTYPE;
627 i number;
628 l_eflag varchar2(2);
629 l_debug_prefix VARCHAR2(30) := '';
630
631 CURSOR c1 (cp_map_id number) IS
632 SELECT loc_seq_num, loc_component,
633 geography_type, geo_element_col
634 FROM hz_geo_struct_map_dtl
635 WHERE map_id = cp_map_id
636 ORDER BY loc_seq_num ASC;
637
638 BEGIN
639 x_status := fnd_api.g_ret_sts_success; -- defaulting the sucess status.
640 -- Check if API is called in debug mode. If yes, enable debug.
641 --enable_debug;
642
643 /* flow:
644 1. given a mapid , gte map details
645 2. if both addrstyle and country are not null, get map rec using both
646 else use country to fetch map rec
647 3. getlocrec() is called to get the component details.
648 */
649
650 -- Debug info.
651 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
652 hz_utility_v2pub.debug(p_message=>'bfr getMapId()',
653 p_prefix =>l_debug_prefix,
654 p_msg_level=>fnd_log.level_statement);
655 END IF;
656
657 hz_gnr_util_pkg.getMapId(p_locid, p_loctbl, x_cntry, x_mapId, x_status );
658
659 -- given a mapid, get the details of the map
660 IF (x_status <> fnd_api.g_ret_sts_success) THEN
661 -- Debug info.
662 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
663 hz_utility_v2pub.debug(p_message=>'getMapId() failed',
664 p_prefix=>'ERROR',
665 p_msg_level=>fnd_log.level_error);
666 END IF;
667
668 -- there is some error in getcntrystyle(), hence exit
669 RAISE FND_API.G_EXC_ERROR;
670 END IF;
671
672 i := 1;
673 FOR c1rec IN c1(x_mapId)
674 LOOP
675 x_mltbl(i).loc_seq_num := c1rec.loc_seq_num;
676 x_mltbl(i).loc_component := c1rec.loc_component;
677 x_mltbl(i).geography_type := c1rec.geography_type;
678 x_mltbl(i).geo_element_col := c1rec.geo_element_col;
679 i := i+1;
680 END LOOP;
681 --disable_debug;
682 EXCEPTION
683 WHEN no_data_found THEN
684
685 -- Debug info.
686 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
687 hz_utility_v2pub.debug(p_message=>'in getMapRec Exception blk. No data found',
688 p_prefix=>'ERROR',
689 p_msg_level=>fnd_log.level_error);
690 END IF;
691
692 RAISE FND_API.G_EXC_ERROR;
693 END getmaprec;
694 -----------------------------------------
695 /**
696 * procedure getlocrec
697 *
698 * description
699 * this private procedure is used to get
700 * the location record. in addition, this would return
701 * country code address style of the location records
702 * in separate variables.
703 *
704 * external procedures/functions accessed
705 *
706 * arguments
707 * in:
708 *
709 * p_locid location identifier
710 * p_loctbl location table
711 *
712 * out:
713 *
714 * x_cntry country code
715 * x_addrstyle address style
716 * x_hrla rec type for hr_locations_all
717 * x_hzl rec type for hz_locations
718 * x_po rec type for po_vendor_sites_all
719 *
720 * exceptions raised
721 *
722 * HZ_GEO_LOC_TABLE_INVALID
723 * hz_geo_invalid_country
724 * hz_geo_no_loc_rec
725 *
726 * notes
727 *
728 *
729 * modification history
730 *
731 *
732 */
733 PROCEDURE getlocrec (
734 p_locid IN number,
735 p_loctbl IN varchar2,
736 x_mltbl IN OUT NOCOPY maploc_rec_tbl_type,
737 x_status OUT NOCOPY varchar2
738 ) IS
739
740 l_sql_1 varchar2(1000);
741 l_len number;
742 l_debug_prefix VARCHAR2(30) := '';
743
744 BEGIN
745 x_status := fnd_api.g_ret_sts_success; -- defaulting the sucess status.
746
747 l_sql_1 := 'select ';
748 FOR i IN x_mltbl.first..x_mltbl.last
749 LOOP
750 l_sql_1 := l_sql_1||x_mltbl(i).loc_component||',';
751 END LOOP;
752 l_sql_1 := SUBSTRB(l_sql_1, 1, LENGTHB(l_sql_1)-1);
753 l_sql_1 := l_sql_1||' from '|| p_loctbl||' where ';
754
755 -- Removed PO_VENDOR_SITES_ALL from the below if condition. Bug # 4584465
756 --IF p_loctbl = 'PO_VENDOR_SITES_ALL' THEN
757 -- check to see if theer are multiple rows for the same id.
758 -- l_sql_1 := l_sql_1||' vendor_site_id = :id and rownum =1 ';
759 --ELSE
760 -- l_sql_1 := l_sql_1||' location_id = :id and rownum =1 ';
761 --END IF;
762 l_sql_1 := l_sql_1||' location_id = :id and rownum =1 ';
763
764 l_len := x_mltbl.count;
765
766 IF (l_len = 1) THEN
767 execute IMMEDIATE l_sql_1
768 INTO x_mltbl(1).loc_compval USING IN p_locid;
769 ELSIF (l_len = 2) THEN
770 execute IMMEDIATE l_sql_1
771 INTO x_mltbl(1).loc_compval, x_mltbl(2).loc_compval
772 USING IN p_locid;
773 ELSIF (l_len = 3) THEN
774 execute IMMEDIATE l_sql_1
775 INTO x_mltbl(1).loc_compval, x_mltbl(2).loc_compval,x_mltbl(3).loc_compval
776 USING IN p_locid;
777 ELSIF (l_len = 4) THEN
778 execute IMMEDIATE l_sql_1
779 INTO x_mltbl(1).loc_compval, x_mltbl(2).loc_compval,x_mltbl(3).loc_compval,
780 x_mltbl(4).loc_compval
781 USING IN p_locid;
782 ELSIF (l_len = 5) THEN
783 execute IMMEDIATE l_sql_1
784 INTO x_mltbl(1).loc_compval, x_mltbl(2).loc_compval,x_mltbl(3).loc_compval,
785 x_mltbl(4).loc_compval, x_mltbl(5).loc_compval
786 USING IN p_locid;
787 ELSIF (l_len = 6) THEN
788 execute IMMEDIATE l_sql_1
789 INTO x_mltbl(1).loc_compval, x_mltbl(2).loc_compval,x_mltbl(3).loc_compval,
790 x_mltbl(4).loc_compval, x_mltbl(5).loc_compval, x_mltbl(6).loc_compval
791 USING IN p_locid;
792 ELSIF (l_len = 7) THEN
793 execute IMMEDIATE l_sql_1
794 INTO x_mltbl(1).loc_compval, x_mltbl(2).loc_compval,x_mltbl(3).loc_compval,
795 x_mltbl(4).loc_compval, x_mltbl(5).loc_compval, x_mltbl(6).loc_compval,
796 x_mltbl(7).loc_compval
797 USING IN p_locid;
798 ELSIF (l_len = 8) THEN
799 execute IMMEDIATE l_sql_1
800 INTO x_mltbl(1).loc_compval, x_mltbl(2).loc_compval,x_mltbl(3).loc_compval,
801 x_mltbl(4).loc_compval, x_mltbl(5).loc_compval, x_mltbl(6).loc_compval,
802 x_mltbl(7).loc_compval,x_mltbl(8).loc_compval
803 USING IN p_locid;
804 ELSIF (l_len = 9) THEN
805 execute IMMEDIATE l_sql_1
806 INTO x_mltbl(1).loc_compval, x_mltbl(2).loc_compval,x_mltbl(3).loc_compval,
807 x_mltbl(4).loc_compval, x_mltbl(5).loc_compval, x_mltbl(6).loc_compval,
808 x_mltbl(7).loc_compval,x_mltbl(8).loc_compval,x_mltbl(9).loc_compval
809 USING IN p_locid;
810 ELSIF (l_len = 10) THEN
811 execute IMMEDIATE l_sql_1
812 INTO x_mltbl(1).loc_compval, x_mltbl(2).loc_compval,x_mltbl(3).loc_compval,
813 x_mltbl(4).loc_compval, x_mltbl(5).loc_compval, x_mltbl(6).loc_compval,
814 x_mltbl(7).loc_compval,x_mltbl(8).loc_compval,x_mltbl(9).loc_compval
815 ,x_mltbl(10).loc_compval
816 USING IN p_locid;
817 ELSE
818 x_status := fnd_api.G_RET_STS_ERROR;
819 mesglog(p_locid,p_loctbl,'HZ_GEO_TOO_MANY_MAP_DTLS', NULL, NULL, NULL, NULL);
820 RAISE FND_API.G_EXC_ERROR;
821 END IF;
822
823 EXCEPTION
824 WHEN no_data_found THEN
825 x_status := fnd_api.G_RET_STS_ERROR;
826 mesglog(p_locid,p_loctbl,'HZ_GEO_NO_LOC_REC', 'LOC_ID',p_locid, 'TABLE_NAME', p_loctbl);
827 RAISE FND_API.G_EXC_ERROR;
828 END getlocrec;
829 -------------------------------------------
830 /**
831 * procedure getcntrystyle
832 *
833 * description
834 * this private procedure is used to get the country code address style for a location.
835 *
836 * external procedures/functions accessed
837 *
838 * arguments
839 * in:
840 *
841 * p_locid location identifier
842 * p_loctbl location table
843 *
844 * out:
845 *
846 * x_cntry country code
847 * x_addrstyle address style
848 *
849 * exceptions raised
850 *
851 * HZ_GEO_LOC_TABLE_INVALID
852 * hz_geo_invalid_country
853 * hz_geo_no_loc_rec
854 *
855 * notes
856 *
857 *
858 * modification history
859 *
860 *
861 */
862
863 PROCEDURE getcntrystyle (
864 p_locid IN number,
865 p_loctbl IN varchar2,
866 x_cntry OUT NOCOPY varchar2,
867 x_addrstyle OUT NOCOPY varchar2,
868 x_status OUT NOCOPY varchar2
869 ) IS
870
871 l_sql_1 varchar2(500);
872 l_sql_2 varchar2(500);
873 l_sql_3 varchar2(500);
874 l_sql_4 varchar2(300);
875 l_tmp number;
876 l_len number;
877 l_debug_prefix VARCHAR2(30) := '';
878
879 BEGIN
880 x_status := fnd_api.g_ret_sts_success; -- defaulting the sucess status.
881 -- Check if API is called in debug mode. If yes, enable debug.
882 --enable_debug;
883 -- three sql statements are necessary depending for three location tables.
884 -- the three tables are:
885 --
886 l_sql_3 := 'select country, style from hr_locations_all where location_id = :id and rownum = 1 ';
887 l_sql_2 := 'select country, address_style from hz_locations where location_id = :id and rownum =1 ';
888 -- l_sql_1 := 'select country, address_style from po_vendor_sites_all where vendor_site_id = :id and rownum =1 ';
889
890 l_sql_4 := 'select 1 from fnd_territories f where f.territory_code = :code';
891
892 -- Removed PO_VENDOR_SITES_ALL from the below if condition. Bug # 4584465
893 --IF p_loctbl = 'PO_VENDOR_SITES_ALL' THEN
894 -- Debug info.
895 -- IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
896 -- hz_utility_v2pub.debug(p_message=>'***sql for po_vendor_sites:'||l_sql_1,
897 -- p_prefix =>l_debug_prefix,
898 -- p_msg_level=>fnd_log.level_statement);
899 -- END IF;
900 -- execute IMMEDIATE l_sql_1 INTO x_cntry, x_addrstyle USING IN p_locid;
901 IF p_loctbl = 'HZ_LOCATIONS' THEN
902 -- Debug info.
903 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
904 hz_utility_v2pub.debug(p_message=>'***sql for hz_locations'||l_sql_2,
905 p_prefix =>l_debug_prefix,
906 p_msg_level=>fnd_log.level_statement);
907 END IF;
908 execute IMMEDIATE l_sql_2 INTO x_cntry, x_addrstyle USING IN p_locid;
909 ELSIF p_loctbl = 'HR_LOCATIONS_ALL' THEN
910 -- Debug info.
911 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
912 hz_utility_v2pub.debug(p_message=>'***sql for HR_LOCATIONS_ALL'||l_sql_3,
913 p_prefix =>l_debug_prefix,
914 p_msg_level=>fnd_log.level_statement);
915 END IF;
916 execute IMMEDIATE l_sql_3 INTO x_cntry, x_addrstyle USING IN p_locid;
917 ELSE
918 -- this means that the supplied table name is not supported by gnr in hz.k
919 x_status := fnd_api.G_RET_STS_ERROR;
920 -- Debug info.
921 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
922 hz_utility_v2pub.debug(p_message=>'***bfr mesglog() when tbl name is invalid',
923 p_prefix=>'ERROR',
924 p_msg_level=>fnd_log.level_error);
925 END IF;
926 mesglog(p_locid,p_loctbl,'HZ_GEO_LOC_TABLE_INVALID', NULL, NULL, NULL, NULL);
927 RAISE FND_API.G_EXC_ERROR;
928 END IF;
929 IF (x_cntry IS NULL) THEN
930 -- Debug info.
931 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
932 hz_utility_v2pub.debug(p_message=>'country code is null',
933 p_prefix=>'ERROR',
934 p_msg_level=>fnd_log.level_error);
935 END IF;
936 x_status := fnd_api.G_RET_STS_ERROR;
937 mesglog(p_locid,p_loctbl,'HZ_GEO_INVALID_COUNTRY', 'LOC_ID',p_locid, 'TABLE_NAME', p_loctbl);
938 RAISE FND_API.G_EXC_ERROR;
939 ELSE
940 l_len := LENGTHB(x_cntry);
941 IF l_len = 2 THEN
942 execute IMMEDIATE l_sql_4 INTO l_tmp USING IN x_cntry;
943 IF (l_tmp <> 1) THEN
944 x_status := fnd_api.G_RET_STS_ERROR;
945 mesglog(p_locid,p_loctbl,'HZ_GEO_INVALID_COUNTRY', 'LOC_ID',p_locid, 'TABLE_NAME', p_loctbl);
946 RAISE FND_API.G_EXC_ERROR;
947 END IF;
948 ELSE
949 x_status := fnd_api.G_RET_STS_ERROR;
950 mesglog(p_locid,p_loctbl,'HZ_GEO_INVALID_COUNTRY', 'LOC_ID',p_locid, 'TABLE_NAME', p_loctbl);
951 RAISE FND_API.G_EXC_ERROR;
952 END IF;
953 END IF;
954 --disable_debug;
955 EXCEPTION
956 WHEN no_data_found THEN
957 x_status := fnd_api.G_RET_STS_ERROR;
958 -- Debug info.
959 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
960 hz_utility_v2pub.debug(p_message=>'***bfr mesglog() in no data found',
961 p_prefix=>'ERROR',
962 p_msg_level=>fnd_log.level_error);
963 END IF;
964 mesglog(p_locid,p_loctbl,'HZ_GEO_NO_LOC_REC', 'LOC_ID',p_locid, 'TABLE_NAME', p_loctbl);
965 RAISE FND_API.G_EXC_ERROR;
966
967 END getcntrystyle;
968 ----------------------------------------------------
969
970 /**
971 * procedure gnrins
972 *
973 * description
974 * this private procedure is used to insert or update the
975 * gnr table.
976 * this procedure will update if the same location id and
977 * geography id combination is existing otherwise this will insert.
978 *
979 * external procedures/functions accessed
980 *
981 * arguments
982 * in:
983 *
984 * p_locid location identifier
985 * p_loctbl location table
986 *
987 * p_maptbl table of records that has location sequence number,
988 * geo element, type and loc components and their values
989 *
990 * out:
991 *
992 * x_status procedure status
993 *
994 *
995 * exceptions raised
996 *
997 *
998 * notes
999 *
1000 *
1001 * modification history
1002 *
1003 *
1004 */
1005 -----------------------------------------------------------
1006 PROCEDURE gnrins (
1007 p_locid IN number,
1008 p_loctbl IN varchar2,
1009 p_maptbl IN maploc_rec_tbl_type,
1010 x_status OUT NOCOPY varchar2
1011 ) IS
1012 l_debug_prefix VARCHAR2(30) := '';
1013
1014 l_last_updated_by NUMBER; -- hz_utility_v2pub.last_updated_by;
1015 l_creation_date DATE; -- hz_utility_v2pub.creation_date;
1016 l_created_by NUMBER; -- hz_utility_v2pub.created_by;
1017 l_last_update_date DATE; -- hz_utility_v2pub.last_update_date;
1018 l_last_update_login NUMBER; -- hz_utility_v2pub.last_update_login;
1019 l_program_id NUMBER; -- hz_utility_v2pub.program_id;
1020 l_conc_login_id NUMBER; -- fnd_global.conc_login_id;
1021 l_program_application_id NUMBER; --hz_utility_v2pub.program_application_id;
1022 l_request_id NUMBER; -- NVL(hz_utility_v2pub.request_id, -1);
1023
1024 BEGIN
1025
1026 /*
1027 flow:
1028 1. delete the locId and locTbl combination
1029 2. loop through the table of records and insert
1030 */
1031
1032 -- Initialize variables (perf improvement bug 5130993)
1033 l_last_updated_by := hz_utility_v2pub.last_updated_by;
1034 l_creation_date := hz_utility_v2pub.creation_date;
1035 l_created_by := hz_utility_v2pub.created_by;
1036 l_last_update_date := hz_utility_v2pub.last_update_date;
1037 l_last_update_login := hz_utility_v2pub.last_update_login;
1038 l_program_id := hz_utility_v2pub.program_id;
1039 l_conc_login_id := fnd_global.conc_login_id;
1040 l_program_application_id := hz_utility_v2pub.program_application_id;
1041 l_request_id := NVL(hz_utility_v2pub.request_id, -1);
1042
1043 -- initializing the return status
1044 x_status := fnd_api.g_ret_sts_success;
1045
1046 -- Check if API is called in debug mode. If yes, enable debug.
1047 --enable_debug;
1048
1049
1050 -- Debug info.
1051 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1052 hz_utility_v2pub.debug(p_message=>'deleting loc id, table name combo from gnr',
1053 p_prefix =>l_debug_prefix,
1054 p_msg_level=>fnd_log.level_statement);
1055 hz_utility_v2pub.debug(p_message=>'bfr delgnr()',
1056 p_prefix =>l_debug_prefix,
1057 p_msg_level=>fnd_log.level_statement);
1058 END IF;
1059
1060 -- delete thing the location id and table name combination from gnr
1061 delgnr(p_locid,p_loctbl,x_status);
1062
1063 -- Debug info.
1064 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1065 hz_utility_v2pub.debug(p_message=>'aft delGnr()',
1066 p_prefix =>l_debug_prefix,
1067 p_msg_level=>fnd_log.level_statement);
1068 END IF;
1069
1070 IF x_status = FND_API.G_RET_STS_SUCCESS THEN
1071 FOR i IN p_maptbl.first .. p_maptbl.last
1072 LOOP
1073 IF (x_status = FND_API.G_RET_STS_SUCCESS) AND
1074 (p_maptbl(i).geography_id IS NOT NULL) THEN
1075 BEGIN
1076 INSERT INTO hz_geo_name_references
1077 (location_id, geography_id, location_table_name,
1078 object_version_number, geography_type, last_updated_by,
1079 creation_date, created_by, last_update_date,
1080 last_update_login, program_id, program_login_id,
1081 program_application_id,request_id)
1082 VALUES
1083 (p_locid, p_maptbl(i).geography_id,p_loctbl,
1084 1, p_maptbl(i).geography_type, l_last_updated_by,
1085 l_creation_date, l_created_by,
1086 l_last_update_date, l_last_update_login,
1087 l_program_id, l_conc_login_id,
1088 l_program_application_id, l_request_id);
1089 --disable_debug;
1090 EXCEPTION
1091 WHEN others THEN
1092
1093 -- Debug info.
1094 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1095 hz_utility_v2pub.debug(p_message=>'error in ins gnr rec',
1096 p_prefix=>'ERROR',
1097 p_msg_level=>fnd_log.level_error);
1098 END IF;
1099 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1100 hz_utility_v2pub.debug(p_message=>'loc id:'||p_locid,
1101 p_prefix =>l_debug_prefix,
1102 p_msg_level=>fnd_log.level_statement);
1103 hz_utility_v2pub.debug(p_message=>'loc tbl:'||p_loctbl,
1104 p_prefix =>l_debug_prefix,
1105 p_msg_level=>fnd_log.level_statement);
1106 hz_utility_v2pub.debug(p_message=>'loc comp type:'||p_maptbl(i).geography_type,
1107 p_prefix =>l_debug_prefix,
1108 p_msg_level=>fnd_log.level_statement);
1109 hz_utility_v2pub.debug(p_message=>'loc comp geo id:'||p_maptbl(i).geography_id,
1110 p_prefix =>l_debug_prefix,
1111 p_msg_level=>fnd_log.level_statement);
1112 END IF;
1113
1114 x_status := fnd_api.g_ret_sts_unexp_error;
1115 END;
1116 END IF;
1117 END LOOP;
1118 ELSE
1119 x_status := fnd_api.g_ret_sts_unexp_error;
1120 END IF;
1121 --disable_debug;
1122 EXCEPTION
1123 WHEN others THEN
1124
1125 -- Debug info.
1126 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1127 hz_utility_v2pub.debug(p_message=>'in gnrIns() excep blk',
1128 p_prefix =>l_debug_prefix,
1129 p_msg_level=>fnd_log.level_statement);
1130 END IF;
1131 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1132 hz_utility_v2pub.debug(p_message=>'sqlerrm:'||sqlerrm,
1133 p_prefix=>'ERROR',
1134 p_msg_level=>fnd_log.level_error);
1135 END IF;
1136
1137 x_status := fnd_api.g_ret_sts_unexp_error;
1138 END gnrins;
1139 -----------------------------------------------------------
1140 /**
1141 * procedure gnrl
1142 *
1143 * description
1144 * this private procedure is used to insert or update the
1145 * gnr log table. this log table will be updated irrespective
1146 * of whether the gnring of a location record is sucessfull or not.
1147 *
1148 * external procedures/functions accessed
1149 *
1150 * arguments
1151 * in:
1152 * p_locid location identifier
1153 * p_loctbl location table
1154 * p_mapstatus sucess, error or warning
1155 * in out:
1156 * x_status procedure return status/message name
1157 * that must be logged along with map status.
1158 *
1159 *
1160 * exceptions raised
1161 *
1162 *
1163 * notes
1164 *
1165 *
1166 * modification history
1167 *
1168 *
1169 */
1170
1171 PROCEDURE gnrl (
1172 p_locid IN number,
1173 p_loctbl IN varchar2,
1174 p_mapStatus IN varchar2,
1175 p_mesg IN varchar2
1176 ) IS
1177
1178 l_status varchar2(1);
1179 l_debug_prefix VARCHAR2(30) := '';
1180
1181 l_last_updated_by NUMBER; -- hz_utility_v2pub.last_updated_by;
1182 l_creation_date DATE; -- hz_utility_v2pub.creation_date;
1183 l_created_by NUMBER; -- hz_utility_v2pub.created_by;
1184 l_last_update_date DATE; -- hz_utility_v2pub.last_update_date;
1185 l_last_update_login NUMBER; -- hz_utility_v2pub.last_update_login;
1186 l_program_id NUMBER; -- hz_utility_v2pub.program_id;
1187 l_conc_login_id NUMBER; -- fnd_global.conc_login_id;
1188 l_program_application_id NUMBER; --hz_utility_v2pub.program_application_id;
1189 l_request_id NUMBER; -- NVL(hz_utility_v2pub.request_id, -1);
1190
1191 BEGIN
1192 -- Check if API is called in debug mode. If yes, enable debug.
1193 --enable_debug;
1194 -- flow:
1195 -- 1. delete the records with (locId, locTbl) combination
1196 --
1197 -- 2. in case of map status being error, delete records from gnr
1198 -- table also.
1199 --
1200 -- 3. insert the record in the GNRL
1201
1202 -- Initialize variables (perf improvement bug 5130993)
1203 l_last_updated_by := hz_utility_v2pub.last_updated_by;
1204 l_creation_date := hz_utility_v2pub.creation_date;
1205 l_created_by := hz_utility_v2pub.created_by;
1206 l_last_update_date := hz_utility_v2pub.last_update_date;
1207 l_last_update_login := hz_utility_v2pub.last_update_login;
1208 l_program_id := hz_utility_v2pub.program_id;
1209 l_conc_login_id := fnd_global.conc_login_id;
1210 l_program_application_id := hz_utility_v2pub.program_application_id;
1211 l_request_id := NVL(hz_utility_v2pub.request_id, -1);
1212
1213 -- initializing the return status
1214 l_status := fnd_api.g_ret_sts_success;
1215
1216
1217 -- Debug info.
1218 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1219 hz_utility_v2pub.debug(p_message=>'in gnrl(), bfr deleting locid, tbl nm combo',
1220 p_prefix =>l_debug_prefix,
1221 p_msg_level=>fnd_log.level_statement);
1222 END IF;
1223
1224 -- delete thing the location id and table name combination from gnr
1225 BEGIN
1226 DELETE FROM hz_geo_name_reference_log
1227 WHERE location_id = p_locid
1228 AND location_table_name = p_loctbl;
1229 EXCEPTION
1230 WHEN others THEN
1231
1232 -- Debug info.
1233 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1234 hz_utility_v2pub.debug(p_message=>'excep when deleting locid, tbl nm combo frm gnrl tbl',
1235 p_prefix =>l_debug_prefix,
1236 p_msg_level=>fnd_log.level_statement);
1237 END IF;
1238
1239 RAISE FND_API.G_EXC_ERROR;
1240 -- x_status := fnd_api.g_ret_sts_unexp_error;
1241 END;
1242
1243
1244 -- This is to make the GNRL and GNR table in sync.
1245 -- In the case of GNR being sucess - gnrins() function
1246 -- deletes and re-creates rows for locationId and table
1247 -- combinations. Only in the case of Error, as gnrins() will not be called
1248 -- when ever there is a need to provide the previously GNRed
1249 -- location id and table combination but still need to write the
1250 -- result of the latest run in GNRL table comment the following
1251 -- delete statement.
1252
1253 IF (p_mapStatus = FND_API.G_RET_STS_ERROR) THEN
1254
1255 -- Debug info.
1256 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1257 hz_utility_v2pub.debug(p_message=>'bfr delGNR()',
1258 p_prefix =>l_debug_prefix,
1259 p_msg_level=>fnd_log.level_statement);
1260 END IF;
1261
1262 delGNR(p_locid,p_loctbl, l_status);
1263
1264 -- Debug info.
1265 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1266 hz_utility_v2pub.debug(p_message=>'aft delGNR()',
1267 p_prefix =>l_debug_prefix,
1268 p_msg_level=>fnd_log.level_statement);
1269 END IF;
1270
1271 END IF;
1272
1273 IF (l_status = fnd_api.g_ret_sts_success) THEN
1274 INSERT INTO hz_geo_name_reference_log
1275 (location_id, location_table_name,
1276 message_text,
1277 object_version_number, map_status,
1278 last_updated_by, creation_date,
1279 created_by, last_update_date,
1280 last_update_login, program_id,
1281 program_login_id,program_application_id,request_id)
1282 VALUES
1283 (p_locid, p_loctbl, p_mesg, 1, p_mapStatus,
1284 l_last_updated_by, l_creation_date,
1285 l_created_by, l_last_update_date,
1286 l_last_update_login, l_program_id,
1287 l_conc_login_id, l_program_application_id, l_request_id);
1288
1289 ELSE
1290
1291 -- Debug info.
1292 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1293 hz_utility_v2pub.debug(p_message=>'unable to insert into GNRL tbl',
1294 p_prefix=>'ERROR',
1295 p_msg_level=>fnd_log.level_error);
1296 hz_utility_v2pub.debug(p_message=>'sqlerrm:'||sqlerrm,
1297 p_prefix=>'ERROR',
1298 p_msg_level=>fnd_log.level_error);
1299 END IF;
1300
1301 RAISE FND_API.G_EXC_ERROR;
1302 END IF;
1303
1304 --disable_debug;
1305 -- x_status := fnd_api.g_ret_sts_success;
1306 EXCEPTION
1307 WHEN others THEN
1308
1309 -- Debug info.
1310 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1311 hz_utility_v2pub.debug(p_message=>'in gnrl excep blk',
1312 p_prefix =>l_debug_prefix,
1313 p_msg_level=>fnd_log.level_statement);
1314 END IF;
1315 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1316 hz_utility_v2pub.debug(p_message=>'sqlerrm:'||sqlerrm,
1317 p_prefix=>'ERROR',
1318 p_msg_level=>fnd_log.level_error);
1319 END IF;
1320
1321 RAISE FND_API.G_EXC_ERROR;
1322 -- x_status := fnd_api.g_ret_sts_unexp_error;
1323 END gnrl;
1324 ----------------------------------------------
1325 /**
1326 * Procedure to write a message to the out file
1327 **/
1328 ----------------------------------------------
1329 PROCEDURE out(
1330 message IN VARCHAR2,
1331 newline IN BOOLEAN DEFAULT TRUE) IS
1332 BEGIN
1333 IF message = 'NEWLINE' THEN
1334 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1335 ELSIF (newline) THEN
1336 FND_FILE.put_line(fnd_file.output,message);
1337 ELSE
1338 FND_FILE.put(fnd_file.output,message);
1339 END IF;
1340 END out;
1341 ----------------------------------------------
1342 /**
1343 * Procedure to write a message to the log file
1344 **/
1345 ----------------------------------------------
1346 PROCEDURE log(
1347 message IN VARCHAR2,
1348 newline IN BOOLEAN DEFAULT TRUE) IS
1349 BEGIN
1350 IF message = 'NEWLINE' THEN
1351 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1352 ELSIF (newline) THEN
1353 FND_FILE.put_line(fnd_file.log,message);
1354 ELSE
1355 FND_FILE.put_line(fnd_file.log,message);
1356 END IF;
1357 END log;
1358 ----------------------------------------------
1359 /**
1360 * Procedure to write a message to the out and log files
1361 **/
1362 ----------------------------------------------
1363 PROCEDURE outandlog(
1364 message IN VARCHAR2,
1365 newline IN BOOLEAN DEFAULT TRUE) IS
1366 BEGIN
1367 out(message, newline);
1368 log(message);
1369 END outandlog;
1370 ----------------------------------------------
1371 /**
1372 * procedure to fetch messages of the stack and log the error
1373 **/
1374 ----------------------------------------------
1375
1376 PROCEDURE logerr IS
1377 l_msg_data VARCHAR2(2000);
1378 BEGIN
1379 FND_MSG_PUB.Reset;
1380 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1381 log(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1382 END LOOP;
1383 -- FND_MSG_PUB.Delete_Msg;
1384 END logerr;
1385 ----------------------------------------------
1386 /**
1387 * Function to fetch messages of the stack and log the error
1388 * Also returns the error
1389 **/
1390 ----------------------------------------------
1391 FUNCTION logerror RETURN VARCHAR2 IS
1392 l_msg_data VARCHAR2(2000);
1393 BEGIN
1394 FND_MSG_PUB.Reset;
1395
1396 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1397 l_msg_data := l_msg_data || ' ' || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
1398 END LOOP;
1399 log(l_msg_data);
1400 RETURN l_msg_data;
1401 END logerror;
1402 ----------------------------------------------
1403 /*
1404 this procedure takes a message_name and enters into the message stack
1405 and writes into the log file also.
1406 */
1407 ----------------------------------------------
1408 PROCEDURE mesglog(
1409 p_locId IN NUMBER,
1410 p_locTbl IN VARCHAR2,
1411 p_message IN VARCHAR2,
1412 p_tkn1_name IN VARCHAR2,
1413 p_tkn1_val IN VARCHAR2,
1414 p_tkn2_name IN VARCHAR2,
1415 p_tkn2_val IN VARCHAR2
1416 ) IS
1417 BEGIN
1418
1419 FND_MESSAGE.SET_NAME('AR', p_message);
1420 IF (p_tkn1_name IS NOT NULL) THEN
1421 fnd_message.set_token(p_tkn1_name, p_tkn1_val);
1422 END IF;
1423 IF (p_tkn2_name IS NOT NULL) THEN
1424 fnd_message.set_token(p_tkn2_name, p_tkn2_val);
1425 END IF;
1426 FND_MSG_PUB.ADD;
1427 FND_MSG_PUB.Reset;
1428
1429 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1430 log(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1431 END LOOP;
1432 --hz_gnr_util_pkg.gnrl(p_locid,p_loctbl,G_ERROR,FND_MSG_PUB.Get(p_encoded => FND_API.G_TRUE));
1433 hz_gnr_util_pkg.gnrl(p_locid,p_loctbl,FND_API.G_RET_STS_ERROR,FND_MSG_PUB.Get(p_encoded => FND_API.G_TRUE));
1434 END mesglog;
1435 ----------------------------------------------
1436 FUNCTION getQuery(
1437 p_map_dtls_tbl IN maploc_rec_tbl_type,
1438 p_mdu_tbl IN maploc_rec_tbl_type,
1439 x_status OUT NOCOPY VARCHAR2
1440 ) RETURN VARCHAR2 IS
1441
1442 -- Bug 6507596 : Changed length of query variables to handle more than 5 geography elements
1443 -- In such cases, for example, for 6 parameters, length of l_where itself becomes 1844
1444 -- causing l_query to be > 2000
1445 l_query varchar2(9000); -- (2000)
1446 l_select varchar2(1000); -- (2000)
1447 l_where varchar2(7000); -- (2000)
1448 l_from varchar2(1000); -- (2000)
1449 i number;
1450 l_lowest_value_index number;
1451
1452 BEGIN
1453
1454 x_status := FND_API.g_ret_sts_success;
1455
1456 IF P_MDU_TBL.COUNT = 1 THEN
1457 RETURN NULL;
1458 END IF;
1459
1460 l_select := 'SELECT g.GEOGRAPHY_ID,g.MULTIPLE_PARENT_FLAG';
1461 l_select := l_select||',GEOGRAPHY_ELEMENT1_ID';
1462 l_from := ' FROM HZ_GEOGRAPHIES g ';
1463 l_where := ' WHERE g.GEOGRAPHY_USE = ''MASTER_REF''';
1464 l_where := l_where||' AND g.COUNTRY_CODE = :country_code';
1465 -- Added +0 in the below line to fix the performance bug # 4642581
1466 l_where := l_where||' AND g.GEOGRAPHY_ELEMENT1_ID+0 = :id1';
1467 l_where := l_where||' AND SYSDATE BETWEEN g.START_DATE AND g.END_DATE ';
1468
1469
1470 i := 0;
1471 IF P_MAP_DTLS_TBL.COUNT > 1 THEN
1472 i := P_MAP_DTLS_TBL.FIRST;
1473 i := P_MAP_DTLS_TBL.NEXT(i);
1474 LOOP
1475 l_select := l_select||','||P_MAP_DTLS_TBL(i).GEO_ELEMENT_COL||'_ID';
1476 EXIT WHEN i = P_MAP_DTLS_TBL.LAST;
1477 i := P_MAP_DTLS_TBL.NEXT(i);
1478 END LOOP;
1479 END IF;
1480
1481 -- Derive the index of the lowest value passed.
1482 i := 0;
1483 IF P_MDU_TBL.COUNT > 1 THEN
1484 i := P_MDU_TBL.FIRST;
1485 i := P_MDU_TBL.NEXT(i);
1486 LOOP
1487 IF P_MDU_TBL(i).LOC_COMPVAL IS NOT NULL THEN
1488 l_lowest_value_index := i;
1489 END IF;
1490 EXIT WHEN i = P_MDU_TBL.LAST;
1491 i := P_MDU_TBL.NEXT(i);
1492 END LOOP;
1493 END IF;
1494
1495 i := 0;
1496 IF P_MDU_TBL.COUNT > 1 THEN
1497 i := P_MDU_TBL.FIRST;
1498 i := P_MDU_TBL.NEXT(i);
1499 LOOP
1500 IF P_MDU_TBL(i).LOC_COMPVAL IS NOT NULL THEN
1501 IF i = l_lowest_value_index THEN
1502 -- lowest passed in value will not be null in element column
1503 -- So the OR clause to deal the multiparent element column null value is not required.
1504 l_where := l_where||' AND EXISTS( SELECT NULL FROM HZ_GEOGRAPHY_IDENTIFIERS i'||i;
1505 l_where := l_where||' WHERE i'||i||'.GEOGRAPHY_TYPE = :l_type'||i;
1506 l_where := l_where||' AND i'||i||'.GEOGRAPHY_USE = ''MASTER_REF''';
1507 l_where := l_where||' AND g.'||P_MDU_TBL(i).GEO_ELEMENT_COL||'_ID = i'||i||'.GEOGRAPHY_ID ';
1508 l_where := l_where||' AND upper(i'||i||'.IDENTIFIER_VALUE) = upper(:l_val'||i||'))';
1509 ELSE
1510 l_where := l_where||' AND (EXISTS( SELECT /*+ index(i'||i||',HZ_GEOGRAPHY_IDENTIFIERS_U1) */ NULL FROM HZ_GEOGRAPHY_IDENTIFIERS i'||i;
1511 l_where := l_where||' WHERE i'||i||'.GEOGRAPHY_TYPE = :l_type'||i;
1512 l_where := l_where||' AND i'||i||'.GEOGRAPHY_USE = ''MASTER_REF''';
1513 l_where := l_where||' AND g.'||P_MDU_TBL(i).GEO_ELEMENT_COL||'_ID = i'||i||'.GEOGRAPHY_ID ';
1514 l_where := l_where||' AND upper(i'||i||'.IDENTIFIER_VALUE) = upper(:l_val'||i||'))';
1515 l_where := l_where||' OR (g.multiple_parent_flag = ''Y'' AND g.'||P_MDU_TBL(i).GEO_ELEMENT_COL||'_ID IS NULL))';
1516 END IF;
1517 ELSE
1518 l_where := l_where||' AND :l_type'||i||' = ''X'' AND :l_val'||i||' =''X''';
1519 END IF;
1520
1521 IF i = P_MDU_TBL.LAST THEN
1522 l_where := l_where || ' AND g.GEOGRAPHY_TYPE = :geography_type';
1523 l_where := l_where || ' AND rownum < 3';
1524 EXIT;
1525 END IF;
1526
1527 i := P_MDU_TBL.NEXT(i);
1528 END LOOP;
1529 END IF;
1530 l_query := l_select || l_from || l_where;
1531 RETURN l_query;
1532 END getQuery;
1533 --------------------------------------
1534 --------------------------------------
1535 -- Below function is for creating the query when there is a cause MULTIPLE_MATCH
1536 -- In this case the query is same as the query created by getQuery function except that
1537 -- this also add the check to verify the identifier_type is NAME
1538 -- Fix for bug #
1539 FUNCTION getQueryforMultiMatch(
1540 p_map_dtls_tbl IN maploc_rec_tbl_type,
1541 p_mdu_tbl IN maploc_rec_tbl_type,
1542 x_status OUT NOCOPY VARCHAR2
1543 ) RETURN VARCHAR2 IS
1544
1545 -- Bug 6507596 : Changed length of query variables to handle more than 5 geography elements
1546 -- In such cases, for example, for 6 parameters, length of l_where itself becomes 1844
1547 -- causing l_query to be > 2000
1548 l_query varchar2(9000); -- (2000)
1549 l_select varchar2(1000); -- (2000)
1550 l_where varchar2(7000); -- (2000)
1551 l_from varchar2(1000); -- (2000)
1552 i number;
1553 l_lowest_value_index number;
1554
1555 BEGIN
1556
1557 x_status := FND_API.g_ret_sts_success;
1558
1559 IF P_MDU_TBL.COUNT = 1 THEN
1560 RETURN NULL;
1561 END IF;
1562
1563 l_select := 'SELECT g.GEOGRAPHY_ID,g.MULTIPLE_PARENT_FLAG';
1564 l_select := l_select||',GEOGRAPHY_ELEMENT1_ID';
1565 l_from := ' FROM HZ_GEOGRAPHIES g ';
1566 l_where := ' WHERE g.GEOGRAPHY_USE = ''MASTER_REF''';
1567 l_where := l_where||' AND g.COUNTRY_CODE = :country_code';
1568 -- Added +0 in the below line to fix the performance bug # 4642581
1569 l_where := l_where||' AND g.GEOGRAPHY_ELEMENT1_ID+0 = :id1';
1570 l_where := l_where||' AND SYSDATE BETWEEN g.START_DATE AND g.END_DATE ';
1571
1572
1573 i := 0;
1574 IF P_MAP_DTLS_TBL.COUNT > 1 THEN
1575 i := P_MAP_DTLS_TBL.FIRST;
1576 i := P_MAP_DTLS_TBL.NEXT(i);
1577 LOOP
1578 l_select := l_select||','||P_MAP_DTLS_TBL(i).GEO_ELEMENT_COL||'_ID';
1579 EXIT WHEN i = P_MAP_DTLS_TBL.LAST;
1580 i := P_MAP_DTLS_TBL.NEXT(i);
1581 END LOOP;
1582 END IF;
1583
1584 -- Derive the index of the lowest value passed.
1585 i := 0;
1586 IF P_MDU_TBL.COUNT > 1 THEN
1587 i := P_MDU_TBL.FIRST;
1588 i := P_MDU_TBL.NEXT(i);
1589 LOOP
1590 IF P_MDU_TBL(i).LOC_COMPVAL IS NOT NULL THEN
1591 l_lowest_value_index := i;
1592 END IF;
1593 EXIT WHEN i = P_MDU_TBL.LAST;
1594 i := P_MDU_TBL.NEXT(i);
1595 END LOOP;
1596 END IF;
1597
1598 i := 0;
1599 IF P_MDU_TBL.COUNT > 1 THEN
1600 i := P_MDU_TBL.FIRST;
1601 i := P_MDU_TBL.NEXT(i);
1602 LOOP
1603 IF P_MDU_TBL(i).LOC_COMPVAL IS NOT NULL THEN
1604 IF i = l_lowest_value_index THEN
1605 -- lowest passed in value will not be null in element column
1606 -- So the OR clause to deal the multiparent element column null value is not required.
1607 l_where := l_where||' AND EXISTS( SELECT NULL FROM HZ_GEOGRAPHY_IDENTIFIERS i'||i;
1608 l_where := l_where||' WHERE i'||i||'.GEOGRAPHY_TYPE = :l_type'||i;
1609 l_where := l_where||' AND i'||i||'.GEOGRAPHY_USE = ''MASTER_REF''';
1610 l_where := l_where||' AND g.'||P_MDU_TBL(i).GEO_ELEMENT_COL||'_ID = i'||i||'.GEOGRAPHY_ID ';
1611 l_where := l_where||' AND upper(i'||i||'.IDENTIFIER_VALUE) = upper(:l_val'||i||')';
1612 l_where := l_where||' AND i'||i||'.IDENTIFIER_TYPE = ''NAME''';
1613 l_where := l_where||' AND i'||i||'.PRIMARY_FLAG = ''Y'')';
1614 ELSE
1615 l_where := l_where||' AND (EXISTS( SELECT /*+ index(i'||i||',HZ_GEOGRAPHY_IDENTIFIERS_U1) */ NULL FROM HZ_GEOGRAPHY_IDENTIFIERS i'||i;
1616 l_where := l_where||' WHERE i'||i||'.GEOGRAPHY_TYPE = :l_type'||i;
1617 l_where := l_where||' AND i'||i||'.GEOGRAPHY_USE = ''MASTER_REF''';
1618 l_where := l_where||' AND g.'||P_MDU_TBL(i).GEO_ELEMENT_COL||'_ID = i'||i||'.GEOGRAPHY_ID ';
1619 l_where := l_where||' AND upper(i'||i||'.IDENTIFIER_VALUE) = upper(:l_val'||i||')';
1620 l_where := l_where||' AND i'||i||'.IDENTIFIER_TYPE = ''NAME''';
1621 l_where := l_where||' AND i'||i||'.PRIMARY_FLAG = ''Y'')';
1622 l_where := l_where||' OR (g.multiple_parent_flag = ''Y'' AND g.'||P_MDU_TBL(i).GEO_ELEMENT_COL||'_ID IS NULL))';
1623 END IF;
1624 ELSE
1625 l_where := l_where||' AND :l_type'||i||' = ''X'' AND :l_val'||i||' =''X''';
1626 END IF;
1627
1628 IF i = P_MDU_TBL.LAST THEN
1629 l_where := l_where || ' AND g.GEOGRAPHY_TYPE = :geography_type';
1630 l_where := l_where || ' AND rownum < 3';
1631 EXIT;
1632 END IF;
1633
1634 i := P_MDU_TBL.NEXT(i);
1635 END LOOP;
1636 END IF;
1637 l_query := l_select || l_from || l_where;
1638 RETURN l_query;
1639 END getQueryforMultiMatch;
1640 --------------------------------------
1641 --------------------------------------
1642 PROCEDURE reverse_tbl(P_MAP_DTLS_TBL IN HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE,
1643 X_MAP_DTLS_TBL IN OUT NOCOPY HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE) IS
1644
1645 l_map_dtls_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
1646 i number;
1647 j number;
1648 BEGIN
1649 l_map_dtls_tbl := P_MAP_DTLS_TBL;
1650 IF l_map_dtls_tbl.COUNT > 0 THEN
1651 i := l_map_dtls_tbl.FIRST;
1652 LOOP
1653 IF i = l_map_dtls_tbl.LAST THEN
1654 IF X_MAP_DTLS_TBL.COUNT > 0 THEN
1655 j := X_MAP_DTLS_TBL.LAST + 1;
1656 ELSE
1657 j := 1;
1658 END IF;
1659 X_MAP_DTLS_TBL(j).LOC_SEQ_NUM := l_map_dtls_tbl(i).LOC_SEQ_NUM;
1660 X_MAP_DTLS_TBL(j).LOC_COMPONENT := l_map_dtls_tbl(i).LOC_COMPONENT;
1661 X_MAP_DTLS_TBL(j).GEOGRAPHY_TYPE := l_map_dtls_tbl(i).GEOGRAPHY_TYPE;
1662 X_MAP_DTLS_TBL(j).GEO_ELEMENT_COL := l_map_dtls_tbl(i).GEO_ELEMENT_COL;
1663 X_MAP_DTLS_TBL(j).LOC_COMPVAL := l_map_dtls_tbl(i).LOC_COMPVAL;
1664 X_MAP_DTLS_TBL(j).GEOGRAPHY_ID := l_map_dtls_tbl(i).GEOGRAPHY_ID;
1665 l_map_dtls_tbl.DELETE(i);
1666 reverse_tbl(l_map_dtls_tbl,X_MAP_DTLS_TBL);
1667 EXIT;
1668 END IF;
1669 i := l_map_dtls_tbl.NEXT(i);
1670 END LOOP;
1671 END IF;
1672 END reverse_tbl;
1673 --------------------------------------
1674 --------------------------------------
1675 FUNCTION get_geo_id(p_geography_type IN VARCHAR2,p_mdtl_derived_tbl IN maploc_rec_tbl_type) RETURN NUMBER IS
1676 i number;
1677 BEGIN
1678 IF p_mdtl_derived_tbl.COUNT > 0 THEN
1679 i := p_mdtl_derived_tbl.FIRST;
1680 LOOP
1681 IF p_mdtl_derived_tbl(i).GEOGRAPHY_TYPE = p_geography_type AND p_mdtl_derived_tbl(i).GEOGRAPHY_ID IS NOT NULL THEN
1682 RETURN p_mdtl_derived_tbl(i).GEOGRAPHY_ID;
1683 END IF;
1684 EXIT WHEN i = p_mdtl_derived_tbl.LAST;
1685 i := p_mdtl_derived_tbl.NEXT(i);
1686 END LOOP;
1687 END IF;
1688 RETURN NULL;
1689 END get_geo_id;
1690 --------------------------------------
1691 --------------------------------------
1692 FUNCTION get_usage_val_status(p_map_dtls_tbl IN maploc_rec_tbl_type,p_mdu_tbl IN maploc_rec_tbl_type) RETURN VARCHAR2 IS
1693 i number;
1694 BEGIN
1695 IF p_mdu_tbl.COUNT > 0 THEN
1696 i := p_mdu_tbl.FIRST;
1697 LOOP
1698 IF get_geo_id(p_mdu_tbl(i).GEOGRAPHY_TYPE,p_map_dtls_tbl) IS NULL THEN
1699 RETURN FND_API.G_RET_STS_ERROR; -- Usage level validation has to go through.
1700 END IF;
1701 EXIT WHEN i = p_mdu_tbl.LAST;
1702 i := p_mdu_tbl.NEXT(i);
1703 END LOOP;
1704 END IF;
1705 RETURN FND_API.G_RET_STS_SUCCESS;
1706 END get_usage_val_status;
1707 --------------------------------------
1708 -- The below finction is to get the address validation status
1709 -- depends on the map status and address validation level
1710 --------------------------------------
1711 FUNCTION getAddrValStatus(
1712 p_map_dtls_tbl IN maploc_rec_tbl_type,
1713 p_mdu_tbl IN maploc_rec_tbl_type,
1714 p_called_from IN VARCHAR2,
1715 p_addr_val_level IN VARCHAR2,
1716 x_addr_warn_msg OUT NOCOPY VARCHAR2,
1717 x_map_status IN VARCHAR2,
1718 x_status IN OUT NOCOPY VARCHAR2
1719 ) RETURN VARCHAR2 IS
1720 l_addr_val_status VARCHAR2(2);
1721 i NUMBER;
1722 l_missing_elements VARCHAR2(2000);
1723 l_invalid_elements VARCHAR2(2000);
1724 l_addr_warn_msg VARCHAR2(2000);
1725
1726 l_module_prefix CONSTANT VARCHAR2(30) := 'HZ:ARHGNRUB:HZ_GNR_UTIL_PKG';
1727 l_module CONSTANT VARCHAR2(30) := 'ADDRESS_VALIDATION';
1728 l_debug_prefix VARCHAR2(30);
1729 BEGIN
1730
1731 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1732 hz_utility_v2pub.debug
1733 (p_message => 'Begin of getAddrValStatus procedure',
1734 p_prefix => l_debug_prefix,
1735 p_msg_level => fnd_log.level_procedure,
1736 p_module_prefix => l_module_prefix,
1737 p_module => l_module
1738 );
1739 END IF;
1740
1741 l_addr_val_status := fnd_api.g_ret_sts_success;
1742 l_missing_elements := NULL;
1743 -- If the map status = 'S' returm addr_val_status as 'S'
1744 IF x_map_status = 'S' then
1745 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1746 hz_utility_v2pub.debug
1747 (p_message => ' Map status is S. So, return address validation status as S ',
1748 p_prefix => l_debug_prefix,
1749 p_msg_level => fnd_log.level_statement,
1750 p_module_prefix => l_module_prefix,
1751 p_module => l_module
1752 );
1753 END IF;
1754 RETURN l_addr_val_status;
1755 -- If the called from is GNR returm addr_val_status as x_return_status
1756 ELSIF p_called_from = 'GNR' then
1757 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1758 hz_utility_v2pub.debug
1759 (p_message => ' Called from is GNR. So, return address validation status and return status as S ',
1760 p_prefix => l_debug_prefix,
1761 p_msg_level => fnd_log.level_statement,
1762 p_module_prefix => l_module_prefix,
1763 p_module => l_module
1764 );
1765 END IF;
1766 l_addr_val_status := x_status;
1767 RETURN l_addr_val_status;
1768 -- If the map status is 'E' and called from is <> GNR
1769 ELSE
1770 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1771 hz_utility_v2pub.debug
1772 (p_message => ' Map status is E. Check for address validation level.',
1773 p_prefix => l_debug_prefix,
1774 p_msg_level => fnd_log.level_statement,
1775 p_module_prefix => l_module_prefix,
1776 p_module => l_module
1777 );
1778 END IF;
1779 -- If validation level is NONE, return addr_val_status as 'S'
1780 IF p_addr_val_level = 'NONE' then
1781 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1782 hz_utility_v2pub.debug
1783 (p_message => ' Address validation level is NONE. So, return address validation status and return status as S',
1784 p_prefix => l_debug_prefix,
1785 p_msg_level => fnd_log.level_statement,
1786 p_module_prefix => l_module_prefix,
1787 p_module => l_module
1788 );
1789 END IF;
1790 -- If the addr_val_level is NONE, both return_status and addr_val_status
1791 -- will be set to success even if the validation fails.
1792 x_status := l_addr_val_status;
1793 RETURN l_addr_val_status;
1794 -- If validation level is ERROR, return addr_val_status as 'E'
1795 -- also set the message with the missing parameters.
1796 ELSIF p_addr_val_level = 'ERROR' then
1797 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1798 hz_utility_v2pub.debug
1799 (p_message => ' Address validation level is ERROR.',
1800 p_prefix => l_debug_prefix,
1801 p_msg_level => fnd_log.level_statement,
1802 p_module_prefix => l_module_prefix,
1803 p_module => l_module
1804 );
1805 END IF;
1806 l_addr_val_status := fnd_api.g_ret_sts_error;
1807 IF p_mdu_tbl.COUNT > 0 THEN
1808 i := p_mdu_tbl.FIRST;
1809 LOOP
1810 IF p_map_dtls_tbl(i).GEOGRAPHY_ID IS NULL THEN
1811 l_addr_val_status := fnd_api.g_ret_sts_error;
1812 IF l_missing_elements is NULL then
1813 l_missing_elements := p_map_dtls_tbl(i).GEOGRAPHY_TYPE;
1814 ELSE
1815 l_missing_elements := l_missing_elements ||', ' || p_map_dtls_tbl(i).GEOGRAPHY_TYPE;
1816 END IF;
1817 END IF;
1818 EXIT WHEN i = p_mdu_tbl.LAST;
1819 i := p_mdu_tbl.NEXT(i);
1820 END LOOP;
1821 END IF;
1822 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1823 hz_utility_v2pub.debug
1824 (p_message => 'Please enter valid address elements : '||l_missing_elements,
1825 p_prefix => l_debug_prefix,
1826 p_msg_level => fnd_log.level_statement,
1827 p_module_prefix => l_module_prefix,
1828 p_module => l_module
1829 );
1830 END IF;
1831 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_ADDRESS');
1832 FND_MESSAGE.SET_TOKEN('P_MISSING_ELEMENTS', l_missing_elements);
1833 FND_MSG_PUB.ADD;
1834 RETURN l_addr_val_status;
1835 -- If validation level is WARNING, check the minimum parameters passed.
1836 -- If yes, set the addr_val_status to 'W' and return status to 'S'
1837 -- If not, set the message with the missing parameters
1838 ELSIF p_addr_val_level = 'WARNING' then
1839 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1840 hz_utility_v2pub.debug
1841 (p_message => ' Address validation level is WARNING.',
1842 p_prefix => l_debug_prefix,
1843 p_msg_level => fnd_log.level_statement,
1844 p_module_prefix => l_module_prefix,
1845 p_module => l_module
1846 );
1847 END IF;
1848 l_addr_val_status := 'W';
1849 x_status := fnd_api.g_ret_sts_success;
1850 IF p_mdu_tbl.COUNT > 0 THEN
1851 i := p_mdu_tbl.FIRST;
1852 LOOP
1853 -- Remove the code the find out the missing elements in case of WARNING
1854 -- This is to allow null values for Warning address validation level. Bug # 5011366
1855 -- IF p_map_dtls_tbl(i).LOC_COMPVAL IS NULL THEN
1856 -- l_addr_val_status := fnd_api.g_ret_sts_error;
1857 -- IF l_missing_elements is NULL then
1858 -- l_missing_elements := p_map_dtls_tbl(i).GEOGRAPHY_TYPE;
1859 -- ELSE
1860 -- l_missing_elements := l_missing_elements ||', ' || p_map_dtls_tbl(i).GEOGRAPHY_TYPE;
1861 -- END IF;
1862 -- ELSE
1863 IF p_map_dtls_tbl(i).GEOGRAPHY_ID IS NULL THEN
1864 IF l_invalid_elements is NULL then
1865 l_invalid_elements := p_map_dtls_tbl(i).GEOGRAPHY_TYPE;
1866 ELSE
1867 l_invalid_elements := l_invalid_elements ||', ' || p_map_dtls_tbl(i).GEOGRAPHY_TYPE;
1868 END IF;
1869 END IF;
1870 -- END IF;
1871 EXIT WHEN i = p_mdu_tbl.LAST;
1872 i := p_mdu_tbl.NEXT(i);
1873 END LOOP;
1874 -- IF l_missing_elements is NULL then
1875 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_WARN_ADDRESS');
1876 FND_MESSAGE.SET_TOKEN('P_ALL_ELEMENTS', l_invalid_elements);
1877 x_addr_warn_msg := FND_MESSAGE.get;
1878 x_status := fnd_api.g_ret_sts_success;
1879 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1880 hz_utility_v2pub.debug
1881 (p_message => 'These address elements are not validated : '||l_invalid_elements,
1882 p_prefix => l_debug_prefix,
1883 p_msg_level => fnd_log.level_statement,
1884 p_module_prefix => l_module_prefix,
1885 p_module => l_module
1886 );
1887 END IF;
1888 -- ELSE
1889 -- FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NULL_ADDRESS');
1890 -- FND_MESSAGE.SET_TOKEN('P_MISSING_ELEMENTS', l_missing_elements);
1891 -- FND_MSG_PUB.ADD;
1892 -- x_status := l_addr_val_status;
1893 -- IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1894 -- hz_utility_v2pub.debug
1895 -- (p_message => 'Please enter required address elements : '||l_missing_elements,
1896 -- p_prefix => l_debug_prefix,
1897 -- p_msg_level => fnd_log.level_statement,
1898 -- p_module_prefix => l_module_prefix,
1899 -- p_module => l_module
1900 -- );
1901 -- END IF;
1902 -- END IF;
1903 RETURN l_addr_val_status;
1904 END IF;
1905
1906 l_addr_val_status := 'W';
1907 RETURN l_addr_val_status;
1908 -- If validation level is MINIMUM, check the minimum parameters passed.
1909 -- If yes, set the addr_val_status to 'S' and return status to 'S'
1910 -- If not, set the message with the missing parameters
1911 ELSIF p_addr_val_level = 'MINIMUM' then
1912 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1913 hz_utility_v2pub.debug
1914 (p_message => ' Address validation level is MINIMUM.',
1915 p_prefix => l_debug_prefix,
1916 p_msg_level => fnd_log.level_statement,
1917 p_module_prefix => l_module_prefix,
1918 p_module => l_module
1919 );
1920 END IF;
1921 IF p_mdu_tbl.COUNT > 0 THEN
1922 i := p_mdu_tbl.FIRST;
1923 LOOP
1924 IF p_map_dtls_tbl(i).LOC_COMPVAL IS NULL THEN
1925 l_addr_val_status := fnd_api.g_ret_sts_error;
1926 IF l_missing_elements is NULL then
1927 l_missing_elements := p_map_dtls_tbl(i).GEOGRAPHY_TYPE;
1928 ELSE
1929 l_missing_elements := l_missing_elements ||', ' || p_map_dtls_tbl(i).GEOGRAPHY_TYPE;
1930 END IF;
1931 END IF;
1932 EXIT WHEN i = p_mdu_tbl.LAST;
1933 i := p_mdu_tbl.NEXT(i);
1934 END LOOP;
1935
1936 IF l_missing_elements is NULL then
1937 x_status := fnd_api.g_ret_sts_success;
1938 ELSE
1939 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NULL_ADDRESS');
1940 FND_MESSAGE.SET_TOKEN('P_MISSING_ELEMENTS', l_missing_elements);
1941 FND_MSG_PUB.ADD;
1942 -- hk_debugl('Please enter required address elements: '|| l_missing_elements);
1943 -- hk_debugl('l_addr_val_status : '|| l_addr_val_status);
1944 x_status := l_addr_val_status;
1945 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1946 hz_utility_v2pub.debug
1947 (p_message => 'Please enter required address elements : '||l_missing_elements,
1948 p_prefix => l_debug_prefix,
1949 p_msg_level => fnd_log.level_statement,
1950 p_module_prefix => l_module_prefix,
1951 p_module => l_module
1952 );
1953 END IF;
1954 END IF;
1955
1956 RETURN l_addr_val_status;
1957 END IF;
1958 END IF;
1959 END IF;
1960 END;
1961 --------------------------------------
1962 --------------------------------------
1963 -- This function will be called only if the validation for entire map is a failure.
1964 FUNCTION do_usage_val(
1965 p_cause IN VARCHAR2,
1966 p_map_dtls_tbl IN maploc_rec_tbl_type,
1967 p_mdu_tbl IN maploc_rec_tbl_type,
1968 x_mdtl_derived_tbl IN OUT NOCOPY maploc_rec_tbl_type,
1969 x_status OUT NOCOPY varchar2
1970 ) RETURN BOOLEAN IS
1971 l_mapped_value_count number;
1972 l_usage_value_count number;
1973 i number;
1974
1975 BEGIN
1976 x_status := FND_API.g_ret_sts_success;
1977
1978 IF p_map_dtls_tbl.COUNT = p_mdu_tbl.COUNT THEN
1979 -- Validation for the entire map is already performed. IF the counts are equal, the mapping for the usage is same
1980 -- as the total mapping. So no need to repeat the same validation.
1981 IF (p_cause <> 'MISSING_CHILD' AND p_cause <> 'NO_MATCH') THEN
1982 x_mdtl_derived_tbl := p_map_dtls_tbl;
1983 END IF;
1984 RETURN FALSE;
1985 ELSE
1986 l_mapped_value_count := 0;
1987 l_usage_value_count := 0;
1988
1989 i:=0;
1990 IF p_map_dtls_tbl.COUNT > 0 THEN
1991 i := p_map_dtls_tbl.FIRST;
1992 LOOP
1993 IF p_map_dtls_tbl(i).LOC_COMPVAL IS NOT NULL THEN
1994 l_mapped_value_count := l_mapped_value_count + 1;
1995 END IF;
1996 EXIT WHEN i = p_map_dtls_tbl.LAST;
1997 i := p_map_dtls_tbl.NEXT(i);
1998 END LOOP;
1999 END IF;
2000
2001 i:=0;
2002 IF p_mdu_tbl.COUNT > 0 THEN
2003 i := p_mdu_tbl.FIRST;
2004 LOOP
2005 IF p_mdu_tbl(i).LOC_COMPVAL IS NOT NULL THEN
2006 l_usage_value_count := l_usage_value_count + 1;
2007 END IF;
2008 EXIT WHEN i = p_mdu_tbl.LAST;
2009 i := p_mdu_tbl.NEXT(i);
2010 END LOOP;
2011 END IF;
2012
2013 IF l_mapped_value_count = l_usage_value_count THEN
2014 -- Even if the mapping for a usage is a subset of total mapping, if the passed in values are the same
2015 -- We will not be able to derive additional information from Geography model
2016 -- So no need to repeat the same validation.
2017 IF (p_cause <> 'MISSING_CHILD' AND p_cause <> 'NO_MATCH' and p_cause <> 'MULTIPLE_PARENT') THEN
2018 x_mdtl_derived_tbl := p_map_dtls_tbl;
2019 END IF;
2020 RETURN FALSE;
2021 END IF;
2022 END IF;
2023
2024 IF p_cause = 'MISSING_CHILD' THEN
2025 i:=0;
2026 IF p_mdu_tbl.COUNT > 0 THEN
2027 i := p_mdu_tbl.FIRST;
2028 LOOP
2029 IF get_geo_id(p_mdu_tbl(i).GEOGRAPHY_TYPE,x_mdtl_derived_tbl) IS NULL THEN
2030 RETURN TRUE; -- Usage level validation has to go through.
2031 END IF;
2032 EXIT WHEN i = p_mdu_tbl.LAST;
2033 i := p_mdu_tbl.NEXT(i);
2034 END LOOP;
2035 END IF;
2036 RETURN FALSE; -- All the Usage level mapped columns were derived. So no need to repeat the validation.
2037 END IF;
2038
2039 RETURN TRUE; -- Usage level validation has to go through.
2040 END do_usage_val;
2041 --------------------------------------
2042 --------------------------------------
2043 PROCEDURE fill_values(
2044 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type
2045 ) IS
2046 i number;
2047 l_module_prefix CONSTANT VARCHAR2(30) := 'HZ:ARHGNRUB:HZ_GNR_UTIL_PKG';
2048 l_module CONSTANT VARCHAR2(30) := 'ADDRESS_VALIDATION';
2049 l_debug_prefix VARCHAR2(30);
2050 BEGIN
2051
2052 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2053 hz_utility_v2pub.debug
2054 (p_message => 'Begin of fill_values procedure',
2055 p_prefix => l_debug_prefix,
2056 p_msg_level => fnd_log.level_procedure,
2057 p_module_prefix => l_module_prefix,
2058 p_module => l_module
2059 );
2060 END IF;
2061 IF x_map_dtls_tbl.COUNT > 0 THEN
2062 i:= x_map_dtls_tbl.FIRST;
2063 -- Added the below line to skip the first record from the table (COUNTRY)
2064 i := i+1;
2065 LOOP
2066 IF (x_map_dtls_tbl(i).GEOGRAPHY_ID IS NOT NULL) THEN
2067 -- IF (x_map_dtls_tbl(i).GEOGRAPHY_ID IS NOT NULL AND x_map_dtls_tbl(i).LOC_COMPVAL IS NULL) THEN
2068 SELECT GEOGRAPHY_NAME, GEOGRAPHY_CODE
2069 INTO x_map_dtls_tbl(i).LOC_COMPVAL, x_map_dtls_tbl(i).GEOGRAPHY_CODE
2070 FROM HZ_GEOGRAPHIES
2071 WHERE GEOGRAPHY_ID = x_map_dtls_tbl(i).GEOGRAPHY_ID;
2072 END IF;
2073 EXIT WHEN i = x_map_dtls_tbl.LAST;
2074 i := x_map_dtls_tbl.NEXT(i);
2075 END LOOP;
2076 END IF;
2077 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2078 hz_utility_v2pub.debug
2079 (p_message => 'End of fill_values procedure',
2080 p_prefix => l_debug_prefix,
2081 p_msg_level => fnd_log.level_procedure,
2082 p_module_prefix => l_module_prefix,
2083 p_module => l_module
2084 );
2085 END IF;
2086 END fill_values;
2087 --------------------------------------
2088 --------------------------------------
2089 PROCEDURE putLocCompValues(
2090 p_map_dtls_tbl IN maploc_rec_tbl_type,
2091 x_loc_components_rec IN OUT NOCOPY loc_components_rec_type
2092 ) IS
2093
2094 FUNCTION map_exists(p_location_component IN VARCHAR2,
2095 p_map_dtls_tbl IN maploc_rec_tbl_type
2096 ) RETURN BOOLEAN IS
2097 i number;
2098 BEGIN
2099 IF p_map_dtls_tbl.COUNT > 0 THEN
2100 i:= p_map_dtls_tbl.FIRST;
2101 LOOP
2102 IF p_map_dtls_tbl(i).GEOGRAPHY_ID IS NOT NULL AND p_map_dtls_tbl(i).LOC_COMPONENT = p_location_component THEN
2103 RETURN TRUE;
2104 END IF;
2105 EXIT WHEN i = p_map_dtls_tbl.LAST;
2106 i := p_map_dtls_tbl.NEXT(i);
2107 END LOOP;
2108 END IF;
2109 RETURN FALSE;
2110 END map_exists;
2111
2112 FUNCTION getValue(p_location_component IN VARCHAR2,
2113 p_map_dtls_tbl IN maploc_rec_tbl_type
2114 ) RETURN VARCHAR2 IS
2115 i number :=0;
2116 BEGIN
2117 IF p_map_dtls_tbl.COUNT > 0 THEN
2118 i:= p_map_dtls_tbl.FIRST;
2119 LOOP
2120 IF p_map_dtls_tbl(i).LOC_COMPONENT = p_location_component THEN
2121 RETURN p_map_dtls_tbl(i).LOC_COMPVAL;
2122 END IF;
2123 EXIT WHEN i = p_map_dtls_tbl.LAST;
2124 i := p_map_dtls_tbl.NEXT(i);
2125 END LOOP;
2126 END IF;
2127 RETURN NULL;
2128 END getValue;
2129
2130 BEGIN
2131
2132 -- This API will overwrite the location component, only if that component is mapped and which has a valid geography id
2133 -- in the map details table.
2134 IF map_exists ('COUNTRY',p_map_dtls_tbl) = TRUE THEN
2135 x_loc_components_rec.COUNTRY := getValue('COUNTRY',p_map_dtls_tbl);
2136 END IF;
2137 IF map_exists ('CITY',p_map_dtls_tbl) = TRUE THEN
2138 x_loc_components_rec.CITY := getValue('CITY',p_map_dtls_tbl);
2139 END IF;
2140 IF map_exists ('POSTAL_CODE',p_map_dtls_tbl) = TRUE THEN
2141 x_loc_components_rec.POSTAL_CODE := getValue('POSTAL_CODE',p_map_dtls_tbl);
2142 END IF;
2143 IF map_exists ('STATE',p_map_dtls_tbl) = TRUE THEN
2144 x_loc_components_rec.STATE := getValue('STATE',p_map_dtls_tbl);
2145 END IF;
2146 IF map_exists ('PROVINCE',p_map_dtls_tbl) = TRUE THEN
2147 x_loc_components_rec.PROVINCE := getValue('PROVINCE',p_map_dtls_tbl);
2148 END IF;
2149 IF map_exists ('COUNTY',p_map_dtls_tbl) = TRUE THEN
2150 x_loc_components_rec.COUNTY := getValue('COUNTY',p_map_dtls_tbl);
2151 END IF;
2152 IF map_exists ('POSTAL_PLUS4_CODE',p_map_dtls_tbl) = TRUE THEN
2153 x_loc_components_rec.POSTAL_PLUS4_CODE := getValue('POSTAL_PLUS4_CODE',p_map_dtls_tbl);
2154 END IF;
2155 IF map_exists ('ATTRIBUTE1',p_map_dtls_tbl) = TRUE THEN
2156 x_loc_components_rec.ATTRIBUTE1 := getValue('ATTRIBUTE1',p_map_dtls_tbl);
2157 END IF;
2158 IF map_exists ('ATTRIBUTE2',p_map_dtls_tbl) = TRUE THEN
2159 x_loc_components_rec.ATTRIBUTE2 := getValue('ATTRIBUTE2',p_map_dtls_tbl);
2160 END IF;
2161 IF map_exists ('ATTRIBUTE3',p_map_dtls_tbl) = TRUE THEN
2162 x_loc_components_rec.ATTRIBUTE3 := getValue('ATTRIBUTE3',p_map_dtls_tbl);
2163 END IF;
2164 IF map_exists ('ATTRIBUTE4',p_map_dtls_tbl) = TRUE THEN
2165 x_loc_components_rec.ATTRIBUTE4 := getValue('ATTRIBUTE4',p_map_dtls_tbl);
2166 END IF;
2167 IF map_exists ('ATTRIBUTE5',p_map_dtls_tbl) = TRUE THEN
2168 x_loc_components_rec.ATTRIBUTE5 := getValue('ATTRIBUTE5',p_map_dtls_tbl);
2169 END IF;
2170 IF map_exists ('ATTRIBUTE6',p_map_dtls_tbl) = TRUE THEN
2171 x_loc_components_rec.ATTRIBUTE6 := getValue('ATTRIBUTE6',p_map_dtls_tbl);
2172 END IF;
2173 IF map_exists ('ATTRIBUTE7',p_map_dtls_tbl) = TRUE THEN
2174 x_loc_components_rec.ATTRIBUTE7 := getValue('ATTRIBUTE7',p_map_dtls_tbl);
2175 END IF;
2176 IF map_exists ('ATTRIBUTE8',p_map_dtls_tbl) = TRUE THEN
2177 x_loc_components_rec.ATTRIBUTE8 := getValue('ATTRIBUTE8',p_map_dtls_tbl);
2178 END IF;
2179 IF map_exists ('ATTRIBUTE9',p_map_dtls_tbl) = TRUE THEN
2180 x_loc_components_rec.ATTRIBUTE9 := getValue('ATTRIBUTE9',p_map_dtls_tbl);
2181 END IF;
2182 IF map_exists ('ATTRIBUTE10',p_map_dtls_tbl) = TRUE THEN
2183 x_loc_components_rec.ATTRIBUTE10 := getValue('ATTRIBUTE10',p_map_dtls_tbl);
2184 END IF;
2185 END putLocCompValues;
2186
2187 --------------------------------------
2188 PROCEDURE update_location (
2189 p_location_id IN number,
2190 p_loc_components_rec IN loc_components_rec_type,
2191 p_lock_flag IN varchar2,
2192 p_map_dtls_tbl IN maploc_rec_tbl_type,
2193 x_status OUT NOCOPY varchar2
2194 ) IS
2195
2196
2197 db_city VARCHAR2(60);
2198 db_state VARCHAR2(60);
2199 db_country VARCHAR2(60);
2200 db_county VARCHAR2(60);
2201 db_province VARCHAR2(60);
2202 db_postal_code VARCHAR2(60);
2203 db_postal_plus4_code VARCHAR2(60);
2204 db_attribute1 VARCHAR2(150);
2205 db_attribute2 VARCHAR2(150);
2206 db_attribute3 VARCHAR2(150);
2207 db_attribute4 VARCHAR2(150);
2208 db_attribute5 VARCHAR2(150);
2209 db_attribute6 VARCHAR2(150);
2210 db_attribute7 VARCHAR2(150);
2211 db_attribute8 VARCHAR2(150);
2212 db_attribute9 VARCHAR2(150);
2213 db_attribute10 VARCHAR2(150);
2214 db_wh_update_date DATE;
2215
2216 l_location_profile_rec hz_location_profile_pvt.location_profile_rec_type;
2217 l_loc_components_rec loc_components_rec_type;
2218 l_old_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
2219 l_new_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
2220 l_other_param_rec HZ_GNR_UTIL_PKG.loc_other_param_rec_type;
2221 l_wh_update_date DATE;
2222 l_address1 VARCHAR2(240);
2223 l_address2 VARCHAR2(240);
2224 l_address3 VARCHAR2(240);
2225 l_address4 VARCHAR2(240);
2226 l_actual_content_source VARCHAR2(30);
2227 l_address_key VARCHAR2(500);
2228 l_time_zone_id NUMBER;
2229 l_return_status VARCHAR2(30);
2230 l_msg_count NUMBER;
2231 l_msg_data VARCHAR2(2000);
2232 ll_return_status VARCHAR2(30);
2233 ll_msg_count NUMBER;
2234 ll_msg_data VARCHAR2(2000);
2235
2236 BEGIN
2237 l_loc_components_rec := p_loc_components_rec;
2238 -- if p_loc_flag is not true then the location has been locked already.
2239
2240 -- If the Location updation is allowed
2241 IF location_updation_allowed(p_location_id) then
2242
2243 IF p_lock_flag = 'T' OR p_lock_flag = FND_API.G_TRUE THEN
2244 -- get location components
2245 BEGIN
2246 SELECT COUNTRY, CITY, STATE, COUNTY, PROVINCE, POSTAL_CODE,POSTAL_PLUS4_CODE,
2247 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
2248 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
2249 WH_UPDATE_DATE, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, ACTUAL_CONTENT_SOURCE,
2250 TIMEZONE_ID, ADDRESS_KEY
2251 INTO l_loc_components_rec.country, l_loc_components_rec.city, l_loc_components_rec.state,
2252 l_loc_components_rec.county, l_loc_components_rec.province,
2253 l_loc_components_rec.postal_code, l_loc_components_rec.postal_plus4_code,
2254 l_loc_components_rec.attribute1,l_loc_components_rec.attribute2,
2255 l_loc_components_rec.attribute3,l_loc_components_rec.attribute4,
2256 l_loc_components_rec.attribute5, l_loc_components_rec.attribute6,
2257 l_loc_components_rec.attribute7,l_loc_components_rec.attribute8,
2258 l_loc_components_rec.attribute9,l_loc_components_rec.attribute10,
2259 l_wh_update_date, l_address1, l_address2, l_address3, l_address4
2260 , l_actual_content_source, l_time_zone_id, l_address_key
2261 FROM HZ_LOCATIONS
2262 WHERE LOCATION_ID = p_location_id
2263 FOR UPDATE OF LOCATION_ID NOWAIT;
2264 EXCEPTION
2265 WHEN NO_DATA_FOUND THEN
2266 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
2267 FND_MESSAGE.SET_TOKEN('RECORD', 'hz_locations');
2268 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_location_id));
2269 FND_MSG_PUB.ADD;
2270 x_status := FND_API.G_RET_STS_ERROR;
2271 END; -- end of SELECT
2272
2273 ELSE -- do not lock the location record
2274 -- get location components
2275 BEGIN
2276 SELECT COUNTRY, CITY, STATE, COUNTY, PROVINCE, POSTAL_CODE,POSTAL_PLUS4_CODE,
2277 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
2278 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
2279 WH_UPDATE_DATE, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, ACTUAL_CONTENT_SOURCE,
2280 TIMEZONE_ID, ADDRESS_KEY
2281 INTO l_loc_components_rec.country, l_loc_components_rec.city, l_loc_components_rec.state,
2282 l_loc_components_rec.county, l_loc_components_rec.province,
2283 l_loc_components_rec.postal_code, l_loc_components_rec.postal_plus4_code,
2284 l_loc_components_rec.attribute1,l_loc_components_rec.attribute2,
2285 l_loc_components_rec.attribute3,l_loc_components_rec.attribute4,
2286 l_loc_components_rec.attribute5, l_loc_components_rec.attribute6,
2287 l_loc_components_rec.attribute7,l_loc_components_rec.attribute8,
2288 l_loc_components_rec.attribute9,l_loc_components_rec.attribute10,
2289 l_wh_update_date, l_address1, l_address2, l_address3, l_address4
2290 , l_actual_content_source, l_time_zone_id, l_address_key
2291 FROM HZ_LOCATIONS
2292 WHERE LOCATION_ID = p_location_id;
2293 EXCEPTION
2294 WHEN NO_DATA_FOUND THEN
2295 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
2296 FND_MESSAGE.SET_TOKEN('RECORD', 'hz_locations');
2297 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_location_id));
2298 FND_MSG_PUB.ADD;
2299 x_status := FND_API.G_RET_STS_ERROR;
2300 END; -- end of SELECT ;
2301 END IF;
2302
2303 db_country := l_loc_components_rec.country;
2304 db_city := l_loc_components_rec.city ;
2305 db_state := l_loc_components_rec.state ;
2306 db_county := l_loc_components_rec.county ;
2307 db_province := l_loc_components_rec.province;
2308 db_postal_code := l_loc_components_rec.postal_code;
2309 db_postal_plus4_code := l_loc_components_rec.postal_plus4_code;
2310 db_attribute1 := l_loc_components_rec.attribute1;
2311 db_attribute2 := l_loc_components_rec.attribute2;
2312 db_attribute3 := l_loc_components_rec.attribute3;
2313 db_attribute4 := l_loc_components_rec.attribute4;
2314 db_attribute5 := l_loc_components_rec.attribute5;
2315 db_attribute6 := l_loc_components_rec.attribute6;
2316 db_attribute7 := l_loc_components_rec.attribute7;
2317 db_attribute8 := l_loc_components_rec.attribute8;
2318 db_attribute9 := l_loc_components_rec.attribute9;
2319 db_attribute10 := l_loc_components_rec.attribute10;
2320 db_wh_update_date := l_wh_update_date;
2321
2322 putLocCompValues(p_map_dtls_tbl,l_loc_components_rec);
2323
2324 -- Fix for Bug 5231893. Added UPPER to make case insensitive comaprison. It does not make sense to update
2325 -- location rec as part of GNR creation, just because fetched value from hz_locations is in different case
2326 -- than that derived from HZ_GEOGRAPHIES. It also improves performance by not updating that location rec
2327 -- and doing subsequent processes related to update_location. (18-MAY-2006 Nishant)
2328 IF NVL(UPPER(db_country),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.country),fnd_api.g_miss_char)
2329 AND NVL(UPPER(db_city),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.city),fnd_api.g_miss_char)
2330 AND NVL(UPPER(db_state),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.state),fnd_api.g_miss_char)
2331 AND NVL(UPPER(db_county),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.county),fnd_api.g_miss_char)
2332 AND NVL(UPPER(db_province),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.province),fnd_api.g_miss_char)
2333 AND NVL(UPPER(db_postal_code),fnd_api.g_miss_char)= NVL(UPPER(l_loc_components_rec.postal_code),fnd_api.g_miss_char)
2334 AND NVL(UPPER(db_attribute1),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute1),fnd_api.g_miss_char)
2335 AND NVL(UPPER(db_attribute2),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute2),fnd_api.g_miss_char)
2336 AND NVL(UPPER(db_attribute3),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute3),fnd_api.g_miss_char)
2337 AND NVL(UPPER(db_attribute4),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute4),fnd_api.g_miss_char)
2338 AND NVL(UPPER(db_attribute5),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute5),fnd_api.g_miss_char)
2339 AND NVL(UPPER(db_attribute6),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute6),fnd_api.g_miss_char)
2340 AND NVL(UPPER(db_attribute7),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute7),fnd_api.g_miss_char)
2341 AND NVL(UPPER(db_attribute8),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute8),fnd_api.g_miss_char)
2342 AND NVL(UPPER(db_attribute9),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute9),fnd_api.g_miss_char)
2343 AND NVL(UPPER(db_attribute10),fnd_api.g_miss_char)= NVL(UPPER(l_loc_components_rec.attribute10),fnd_api.g_miss_char)
2344 THEN
2345 NULL;
2346 ELSE
2347
2348 -- Old location record will have the data from database.
2349 l_old_location_rec.location_id := p_location_id;
2350 l_old_location_rec.country := db_country;
2351 l_old_location_rec.city := db_city;
2352 l_old_location_rec.state := db_state;
2353 l_old_location_rec.county := db_county;
2354 l_old_location_rec.province := db_province;
2355 l_old_location_rec.postal_code := db_postal_code;
2356 l_old_location_rec.postal_plus4_code := db_postal_plus4_code;
2357 l_old_location_rec.attribute1 := db_attribute1;
2358 l_old_location_rec.attribute2 := db_attribute2;
2359 l_old_location_rec.attribute3 := db_attribute3;
2360 l_old_location_rec.attribute4 := db_attribute4;
2361 l_old_location_rec.attribute5 := db_attribute5;
2362 l_old_location_rec.attribute6 := db_attribute6;
2363 l_old_location_rec.attribute7 := db_attribute7;
2364 l_old_location_rec.attribute8 := db_attribute8;
2365 l_old_location_rec.attribute9 := db_attribute9;
2366 l_old_location_rec.attribute10 := db_attribute10;
2367 l_old_location_rec.address1 := l_address1;
2368 l_old_location_rec.address2 := l_address2;
2369 l_old_location_rec.address3 := l_address3;
2370 l_old_location_rec.address4 := l_address4;
2371 l_old_location_rec.timezone_id := l_time_zone_id;
2372 l_old_location_rec.address_key := l_address_key;
2373
2374 -- Old location record will have the data derived by GNR.
2375 -- If country, city, state or postal_code is changed
2376 -- set the timezone_id is NULL so that pre_location_update
2377 -- will get the timezone based on new values.
2378 l_new_location_rec.location_id := p_location_id;
2379 l_new_location_rec.country := l_loc_components_rec.country;
2380 l_new_location_rec.city := l_loc_components_rec.city;
2381 l_new_location_rec.state := l_loc_components_rec.state;
2382 l_new_location_rec.county := l_loc_components_rec.county;
2383 l_new_location_rec.province := l_loc_components_rec.province;
2384 l_new_location_rec.postal_code := l_loc_components_rec.postal_code;
2385 l_new_location_rec.postal_plus4_code := l_loc_components_rec.postal_plus4_code;
2386 l_new_location_rec.attribute1 := l_loc_components_rec.attribute1;
2387 l_new_location_rec.attribute2 := l_loc_components_rec.attribute2;
2388 l_new_location_rec.attribute3 := l_loc_components_rec.attribute3;
2389 l_new_location_rec.attribute4 := l_loc_components_rec.attribute4;
2390 l_new_location_rec.attribute5 := l_loc_components_rec.attribute5;
2391 l_new_location_rec.attribute6 := l_loc_components_rec.attribute6;
2392 l_new_location_rec.attribute7 := l_loc_components_rec.attribute7;
2393 l_new_location_rec.attribute8 := l_loc_components_rec.attribute8;
2394 l_new_location_rec.attribute9 := l_loc_components_rec.attribute9;
2395 l_new_location_rec.attribute10 := l_loc_components_rec.attribute10;
2396 l_new_location_rec.address1 := l_address1;
2397 l_new_location_rec.address2 := l_address2;
2398 l_new_location_rec.address3 := l_address3;
2399 l_new_location_rec.address4 := l_address4;
2400 l_new_location_rec.address_key := l_address_key;
2401 IF (l_new_location_rec.country IS NOT NULL AND
2402 NVL(UPPER(l_old_location_rec.country), fnd_api.g_miss_char) <> UPPER(l_new_location_rec.country))
2403 OR
2404 (l_new_location_rec.city IS NOT NULL AND
2405 NVL(UPPER(l_old_location_rec.city), fnd_api.g_miss_char) <> UPPER(l_new_location_rec.city))
2406 OR
2407 (l_new_location_rec.state IS NOT NULL AND
2408 NVL(UPPER(l_old_location_rec.state), fnd_api.g_miss_char) <> UPPER(l_new_location_rec.state))
2409 OR
2410 (l_new_location_rec.postal_code IS NOT NULL AND
2411 NVL(UPPER(l_old_location_rec.postal_code), fnd_api.g_miss_char) <> UPPER(l_new_location_rec.postal_code))
2412 THEN
2413 l_new_location_rec.timezone_id := NULL;
2414 ELSE
2415 l_new_location_rec.timezone_id := l_time_zone_id;
2416 END IF;
2417
2418 -- Call pre_location_update to get timezone_id and address_key.
2419 begin
2420 pre_location_update( p_old_location_rec => l_old_location_rec,
2421 p_new_location_rec => l_new_location_rec,
2422 p_other_location_params => l_other_param_rec,
2423 x_return_status => ll_return_status,
2424 x_msg_count => ll_msg_count,
2425 x_msg_data => ll_msg_data );
2426 exception when others then
2427 null;
2428 end;
2429
2430 UPDATE HZ_LOCATIONS
2431 SET
2432 COUNTRY = l_loc_components_rec.country,
2433 CITY = l_loc_components_rec.city ,
2434 STATE = l_loc_components_rec.state ,
2435 COUNTY = l_loc_components_rec.county ,
2436 PROVINCE = l_loc_components_rec.province,
2437 POSTAL_CODE = l_loc_components_rec.postal_code,
2438 ATTRIBUTE1 = l_loc_components_rec.attribute1,
2439 ATTRIBUTE2 = l_loc_components_rec.attribute2,
2440 ATTRIBUTE3 = l_loc_components_rec.attribute3,
2441 ATTRIBUTE4 = l_loc_components_rec.attribute4,
2442 ATTRIBUTE5 = l_loc_components_rec.attribute5,
2443 ATTRIBUTE6 = l_loc_components_rec.attribute6,
2444 ATTRIBUTE7 = l_loc_components_rec.attribute7,
2445 ATTRIBUTE8 = l_loc_components_rec.attribute8,
2446 ATTRIBUTE9 = l_loc_components_rec.attribute9,
2447 ATTRIBUTE10 = l_loc_components_rec.attribute10,
2448 TIMEZONE_ID = l_new_location_rec.timezone_id,
2449 ADDRESS_KEY = l_new_location_rec.address_key
2450 WHERE LOCATION_ID = p_location_id;
2451
2452 -- fix for bug # 4169728. Set address_text to null in hz_cust_acct_sites_all table if,
2453 -- city, state, province or postal_code changes in hz_location table.
2454 -- address_text column in hz_cust_acct_sites_all is populated by the concurrent program,
2455 -- "Customer text data creation and indexing", if address_text is null.
2456 IF nvl(UPPER(db_city),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.city),fnd_api.g_miss_char)
2457 AND nvl(UPPER(db_state),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.state),fnd_api.g_miss_char)
2458 AND nvl(UPPER(db_province),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.province),fnd_api.g_miss_char)
2459 AND nvl(UPPER(db_postal_code),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.postal_code),fnd_api.g_miss_char)
2460 THEN
2461 NULL;
2462 ELSE
2463 UPDATE hz_cust_acct_sites_all cas
2464 SET cas.address_text = null
2465 WHERE cas.address_text IS NOT NULL
2466 AND EXISTS
2467 ( SELECT 1
2468 FROM HZ_PARTY_SITES ps
2469 WHERE ps.location_id = p_location_id
2470 AND cas.party_site_id = ps.party_site_id );
2471 END IF;
2472
2473 IF nvl(UPPER(db_country),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.country),fnd_api.g_miss_char)
2474 AND nvl(UPPER(db_city),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.city),fnd_api.g_miss_char)
2475 AND nvl(UPPER(db_state),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.state),fnd_api.g_miss_char)
2476 AND nvl(UPPER(db_county),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.county),fnd_api.g_miss_char)
2477 AND nvl(UPPER(db_province),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.province),fnd_api.g_miss_char)
2478 AND nvl(UPPER(db_postal_code),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.postal_code),fnd_api.g_miss_char)
2479 THEN
2480 NULL;
2481 ELSE
2482 l_location_profile_rec.location_profile_id := NULL;
2483 l_location_profile_rec.location_id := p_location_id;
2484 l_location_profile_rec.actual_content_source := l_actual_content_source;
2485 l_location_profile_rec.effective_start_date := NULL;
2486 l_location_profile_rec.effective_end_date := NULL;
2487 l_location_profile_rec.date_validated := NULL;
2488 l_location_profile_rec.city := l_loc_components_rec.city;
2489 l_location_profile_rec.postal_code := l_loc_components_rec.postal_code;
2490 l_location_profile_rec.county := l_loc_components_rec.county;
2491 l_location_profile_rec.country := l_loc_components_rec.country;
2492 l_location_profile_rec.address1 := l_address1;
2493 l_location_profile_rec.address2 := l_address2;
2494 l_location_profile_rec.address3 := l_address3;
2495 l_location_profile_rec.address4 := l_address4;
2496
2497 IF(l_loc_components_rec.state IS NOT NULL) THEN
2498 l_location_profile_rec.prov_state_admin_code := l_loc_components_rec.state;
2499 ELSIF(l_loc_components_rec.province IS NOT NULL) THEN
2500 l_location_profile_rec.prov_state_admin_code := l_loc_components_rec.province;
2501 ELSE
2502 l_location_profile_rec.prov_state_admin_code := NULL;
2503 END IF;
2504
2505 l_return_status := FND_API.G_RET_STS_SUCCESS;
2506
2507 hz_location_profile_pvt.update_location_profile (
2508 p_location_profile_rec => l_location_profile_rec
2509 ,x_return_status => l_return_status
2510 ,x_msg_count => l_msg_count
2511 ,x_msg_data => l_msg_data );
2512
2513 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
2514 RAISE fnd_api.g_exc_error;
2515 ELSIF(l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2516 RAISE fnd_api.g_exc_unexpected_error;
2517 END IF;
2518 END IF;
2519
2520 -- Call post_location_update to do post update process.
2521 -- 1. Update de-normalized location components in HZ_PARTIES
2522 -- 2. Call to indicate location update to DQM(HZ_DQM_SYNC.sync_location).
2523 -- 3. 6. Invoke business event system.
2524 begin
2525 post_location_update( p_old_location_rec => l_old_location_rec,
2526 p_new_location_rec => l_new_location_rec,
2527 p_other_location_params => l_other_param_rec,
2528 x_return_status => ll_return_status,
2529 x_msg_count => ll_msg_count,
2530 x_msg_data => ll_msg_data );
2531 exception when others then
2532 null;
2533 end;
2534 END IF;
2535 END IF;
2536
2537 END update_location;
2538 --------------------------------------
2539 PROCEDURE create_gnr (
2540 p_location_id IN number,
2541 p_location_table_name IN varchar2,
2542 p_usage_code IN varchar2,
2543 p_map_status IN varchar2,
2544 p_loc_components_rec IN loc_components_rec_type,
2545 p_lock_flag IN varchar2,
2546 p_map_dtls_tbl IN maploc_rec_tbl_type,
2547 x_status OUT NOCOPY varchar2
2548 ) IS
2549 i number;
2550 l_module_prefix CONSTANT VARCHAR2(30) := 'HZ:ARHGNRUB:HZ_GNR_UTIL_PKG';
2551 l_module CONSTANT VARCHAR2(30) := 'ADDRESS_VALIDATION';
2552 l_debug_prefix VARCHAR2(30) := p_location_id;
2553 l_retain_flag VARCHAR2(10);
2554 l_map_dtls_tbl maploc_rec_tbl_type;
2555
2556 l_last_updated_by NUMBER; -- hz_utility_v2pub.last_updated_by;
2557 l_creation_date DATE; -- hz_utility_v2pub.creation_date;
2558 l_created_by NUMBER; -- hz_utility_v2pub.created_by;
2559 l_last_update_date DATE; -- hz_utility_v2pub.last_update_date;
2560 l_last_update_login NUMBER; -- hz_utility_v2pub.last_update_login;
2561 l_program_id NUMBER; -- hz_utility_v2pub.program_id;
2562 l_conc_login_id NUMBER; -- fnd_global.conc_login_id;
2563 l_program_application_id NUMBER; --hz_utility_v2pub.program_application_id;
2564 l_request_id NUMBER; -- NVL(hz_utility_v2pub.request_id, -1);
2565 l_api_purpose VARCHAR2(30);
2566
2567 -- Created below 2 cursors for performance reasons (perf team advice)
2568 -- to avoid DUP_VAL_ON_INDEX exception which is proving very costly
2569 -- during upgrade (Bug 5929771 : Nishant 16-APR-2007)
2570 l_gnr_log_exist VARCHAR2(10);
2571 l_gnr_exist VARCHAR2(10);
2572 l_gnr_deleted VARCHAR2(10);
2573
2574 CURSOR c_check_gnr_log_exist (p_location_id NUMBER,
2575 p_location_table_name VARCHAR2,
2576 p_usage_code VARCHAR2) IS
2577 SELECT 'Y'
2578 FROM hz_geo_name_reference_log
2579 WHERE location_id = p_location_id
2580 AND location_table_name = p_location_table_name
2581 AND usage_code = p_usage_code
2582 ;
2583
2584 CURSOR c_check_gnr_exist (p_location_id NUMBER,
2585 p_location_table_name VARCHAR2,
2586 p_geography_type VARCHAR2) IS
2587 SELECT 'Y'
2588 FROM hz_geo_name_references
2589 WHERE location_id = p_location_id
2590 AND location_table_name = p_location_table_name
2591 AND geography_type = p_geography_type
2592 ;
2593
2594 FUNCTION update_loc_yn(
2595 p_loc_components_rec IN loc_components_rec_type,
2596 p_map_dtls_tbl IN maploc_rec_tbl_type
2597 ) RETURN VARCHAR2 IS
2598 l_map_dtls_tbl maploc_rec_tbl_type;
2599 l_status varchar2(1);
2600 l_return varchar2(1);
2601 i number;
2602 j number;
2603 BEGIN
2604 l_return := 'N';
2605 l_map_dtls_tbl := p_map_dtls_tbl; -- This will populate all the derived Geography IDs
2606 getLocCompValues(
2607 P_loc_table => 'HZ_LOCATIONS',
2608 p_loc_components_rec => p_loc_components_rec,
2609 x_map_dtls_tbl => l_map_dtls_tbl,
2610 x_status => l_status);
2611
2612 IF p_map_dtls_tbl.COUNT > 0 THEN
2613 i := p_map_dtls_tbl.FIRST;
2614 LOOP
2615 IF p_map_dtls_tbl(i).GEOGRAPHY_ID IS NULL THEN
2616 EXIT;
2617 END IF;
2618
2619 IF l_map_dtls_tbl.COUNT > 0 THEN
2620 j := l_map_dtls_tbl.FIRST;
2621 LOOP
2622 IF p_map_dtls_tbl(i).GEOGRAPHY_ID = l_map_dtls_tbl(j).GEOGRAPHY_ID THEN
2623 -- Fix for Bug 5231893 (added UPPER to do case insensitive comaprison
2624 -- Added on 18-May-2006 Nishant)
2625 IF NVL(UPPER(p_map_dtls_tbl(i).LOC_COMPVAL),FND_API.G_MISS_CHAR) <>
2626 NVL(UPPER(l_map_dtls_tbl(j).LOC_COMPVAL),FND_API.G_MISS_CHAR) THEN
2627 l_return := 'Y';
2628 END IF;
2629 END IF;
2630 EXIT WHEN j = l_map_dtls_tbl.LAST;
2631 j := l_map_dtls_tbl.NEXT(j);
2632 END LOOP;
2633 END IF;
2634
2635 EXIT WHEN i = p_map_dtls_tbl.LAST;
2636 i := p_map_dtls_tbl.NEXT(i);
2637 END LOOP;
2638 END IF;
2639 RETURN l_return;
2640 END update_loc_yn;
2641
2642 FUNCTION retain_gnr_yn(
2643 p_location_id IN number,
2644 p_map_status IN varchar2,
2645 p_map_dtls_tbl IN maploc_rec_tbl_type
2646 ) RETURN VARCHAR2 IS
2647 l_map_dtls_tbl maploc_rec_tbl_type;
2648 l_old_map_status varchar2(1);
2649 l_geography_type varchar2(30);
2650 l_retain varchar2(1);
2651 i number;
2652 j number;
2653
2654 cursor get_old_map_status is
2655 select map_status
2656 from HZ_GEO_NAME_REFERENCE_LOG
2657 where location_id = p_location_id
2658 and map_status = 'S';
2659
2660 cursor get_gnr_superloc_rec is
2661 select geography_type
2662 from HZ_GEO_NAME_REFERENCES
2663 where location_id = p_location_id
2664 and geography_id in (-99,-98);
2665
2666 BEGIN
2667 l_retain := 'N';
2668 l_old_map_status := 'E';
2669
2670 OPEN get_old_map_status;
2671 FETCH get_old_map_status INTO l_old_map_status;
2672 IF get_old_map_status%NOTFOUND THEN
2673 l_old_map_status := 'E';
2674 END IF;
2675 CLOSE get_old_map_status;
2676
2677 -- If old status is Success and new is Error,
2678 -- Check for nay record in gnr table is a Super Loc
2679 -- If yes, retain the old one
2680 if l_old_map_status = 'S' and p_map_status = 'E' then
2681
2682 OPEN get_gnr_superloc_rec;
2683 LOOP
2684 FETCH get_gnr_superloc_rec INTO l_geography_type;
2685 EXIT WHEN get_gnr_superloc_rec%NOTFOUND;
2686 IF p_map_dtls_tbl.COUNT > 0 THEN
2687 j := l_map_dtls_tbl.FIRST;
2688 l_retain := 'Y';
2689 LOOP
2690 IF p_map_dtls_tbl(j).GEOGRAPHY_TYPE = l_geography_type THEN
2691 if ( nvl(p_map_dtls_tbl(j).GEOGRAPHY_CODE,'NOVALUE') = 'MISSING' OR
2692 nvl(p_map_dtls_tbl(j).GEOGRAPHY_CODE,'NOVALUE') = 'UNKNOWN' ) THEN
2693 l_retain := 'N';
2694 EXIT;
2695 end if;
2696 END IF;
2697 EXIT WHEN j = p_map_dtls_tbl.LAST;
2698 j := p_map_dtls_tbl.NEXT(j);
2699 END LOOP;
2700 IF l_retain = 'Y' THEN
2701 EXIT;
2702 END IF;
2703 END IF;
2704 END LOOP;
2705 CLOSE get_gnr_superloc_rec;
2706 else
2707 l_retain := 'N';
2708 end if;
2709
2710 RETURN l_retain;
2711 END retain_gnr_yn;
2712
2713 BEGIN
2714
2715 -- Initialize variables (perf improvement bug 5130993)
2716 l_last_updated_by := hz_utility_v2pub.last_updated_by;
2717 l_creation_date := hz_utility_v2pub.creation_date;
2718 l_created_by := hz_utility_v2pub.created_by;
2719 l_last_update_date := hz_utility_v2pub.last_update_date;
2720 l_last_update_login := hz_utility_v2pub.last_update_login;
2721 l_program_id := hz_utility_v2pub.program_id;
2722 l_conc_login_id := fnd_global.conc_login_id;
2723 l_program_application_id := hz_utility_v2pub.program_application_id;
2724 l_request_id := NVL(hz_utility_v2pub.request_id, -1);
2725 l_api_purpose := HZ_GNR_PKG.G_API_PURPOSE;
2726
2727 x_status := FND_API.g_ret_sts_success;
2728
2729 -- retain_gnr_yn will tell whether we have retain old GNR records or not.
2730 -- If l_retain_flag = 'Y' then exit without ceate/update of GNR and hz_location
2731
2732 -- Check if call is for R12UPGRADE then we do not want to do retain_gnr_check at all
2733 -- This Global variable is set in HZ_GNR_PKG during making call to create GNR for R12UPGRADE
2734 IF ( NVL(l_api_purpose,'xxya') = 'R12UPGRADE') THEN
2735 l_retain_flag := 'N';
2736 ELSE
2737 l_retain_flag := retain_gnr_yn(p_location_id, p_map_status, p_map_dtls_tbl);
2738 END IF;
2739
2740 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2741 hz_utility_v2pub.debug
2742 (p_message => 'Retain GNR Flag value l_retain_flag='||l_retain_flag,
2743 p_prefix => l_debug_prefix,
2744 p_msg_level => fnd_log.level_statement,
2745 p_module_prefix => l_module_prefix,
2746 p_module => l_module
2747 );
2748 END IF;
2749
2750 IF l_retain_flag = 'N' THEN -- Then only do the processing of GNR, otherwise keep old value
2751
2752 l_map_dtls_tbl := p_map_dtls_tbl;
2753
2754 BEGIN
2755
2756 -- Bug 5929771 : Check unique value existence before inserting to avoid
2757 -- expensive DUP_VAL_ON_INDEX exception (Nishant 16-APR-2007)
2758 OPEN c_check_gnr_log_exist(p_location_id,p_location_table_name,p_usage_code);
2759 FETCH c_check_gnr_log_exist INTO l_gnr_log_exist;
2760 l_gnr_log_exist := NVL(l_gnr_log_exist,'N');
2761 CLOSE c_check_gnr_log_exist;
2762
2763 IF (l_gnr_log_exist <> 'Y') THEN
2764
2765 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2766 hz_utility_v2pub.debug
2767 (p_message => 'Before inserting record into hz_geo_name_reference_log with map status '||p_map_status,
2768 p_prefix => l_debug_prefix,
2769 p_msg_level => fnd_log.level_statement,
2770 p_module_prefix => l_module_prefix,
2771 p_module => l_module
2772 );
2773 END IF;
2774
2775 INSERT INTO hz_geo_name_reference_log
2776 (location_id, location_table_name,usage_code,
2777 message_text,
2778 object_version_number, map_status,
2779 last_updated_by, creation_date,
2780 created_by, last_update_date,
2781 last_update_login, program_id,
2782 program_login_id,program_application_id,request_id)
2783 VALUES
2784 (p_location_id, p_location_table_name, p_usage_code, NULL, 1, p_map_status,
2785 l_last_updated_by, l_creation_date,
2786 l_created_by, l_last_update_date,
2787 l_last_update_login, l_program_id,
2788 l_conc_login_id, l_program_application_id, l_request_id);
2789
2790 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2791 hz_utility_v2pub.debug
2792 (p_message => 'After inserting record into hz_geo_name_reference_log ',
2793 p_prefix => l_debug_prefix,
2794 p_msg_level => fnd_log.level_statement,
2795 p_module_prefix => l_module_prefix,
2796 p_module => l_module
2797 );
2798 END IF;
2799
2800 ELSE -- GNR Log already exists, we will update it
2801
2802 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2803 hz_utility_v2pub.debug
2804 (p_message => 'Before updating record into hz_geo_name_reference_log with map status '||p_map_status,
2805 p_prefix => l_debug_prefix,
2806 p_msg_level => fnd_log.level_statement,
2807 p_module_prefix => l_module_prefix,
2808 p_module => l_module
2809 );
2810 END IF;
2811
2812 UPDATE hz_geo_name_reference_log
2813 SET map_status = p_map_status,
2814 object_version_number = object_version_number + 1,
2815 last_updated_by = l_last_updated_by,
2816 last_update_date = l_last_update_date,
2817 last_update_login = l_last_update_login
2818 WHERE location_id = p_location_id
2819 AND location_table_name = p_location_table_name
2820 AND usage_code = p_usage_code;
2821
2822 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2823 hz_utility_v2pub.debug
2824 (p_message => 'After updating record into hz_geo_name_reference_log ',
2825 p_prefix => l_debug_prefix,
2826 p_msg_level => fnd_log.level_statement,
2827 p_module_prefix => l_module_prefix,
2828 p_module => l_module
2829 );
2830 END IF;
2831
2832 -- delete the location id and table name combination from hz_geo_name_references
2833 -- This call is needed only if data exists in hz_geo_name_references table
2834 -- blind delete is bad for performance
2835 IF ( nvl(l_api_purpose,'xxya') <> 'R12UPGRADE') THEN
2836 delGNR(p_location_id,p_location_table_name,x_status);
2837 l_gnr_deleted := 'Y'; -- set it to 'Y' because we just deleted it. Later we can avoid again doing this check
2838 END IF;
2839
2840 END IF; -- end of check if l_gnr_log_exist
2841
2842 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- should not hit this error anymore (bug 5929771)
2843 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2844 hz_utility_v2pub.debug
2845 (p_message => 'EXCEPTION DUP_VAL_ON_INDEX during GNR Log insert/update for '||
2846 'Location Id:'||p_location_id||',usage_code:'||p_usage_code||
2847 ' -'||SUBSTR(SQLERRM,1,100),
2848 p_prefix => l_debug_prefix,
2849 p_msg_level => fnd_log.level_statement,
2850 p_module_prefix => l_module_prefix,
2851 p_module => l_module
2852 );
2853 END IF;
2854 END; -- END of GNR Log Insert BEGIN Stmt
2855
2856 -- Now Insert data in hz_geo_name_references table (Child table)
2857 IF p_map_dtls_tbl.COUNT > 0 THEN
2858
2859 i := p_map_dtls_tbl.FIRST;
2860
2861 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2862 hz_utility_v2pub.debug
2863 (p_message => 'Before inserting records into hz_geo_name_references ',
2864 p_prefix => l_debug_prefix,
2865 p_msg_level => fnd_log.level_statement,
2866 p_module_prefix => l_module_prefix,
2867 p_module => l_module
2868 );
2869 END IF;
2870
2871 LOOP
2872 -- intialize l_gnr_exist
2873 l_gnr_exist := NULL;
2874
2875 IF p_map_dtls_tbl(i).geography_id IS NOT NULL THEN
2876
2877 IF p_map_dtls_tbl(i).geography_code = 'MISSING' THEN
2878 l_map_dtls_tbl(i).geography_id := -99;
2879 l_map_dtls_tbl(i).loc_compval := NULL;
2880 ELSIF p_map_dtls_tbl(i).geography_code = 'UNKNOWN' THEN
2881 l_map_dtls_tbl(i).geography_id := -98;
2882 l_map_dtls_tbl(i).loc_compval := NULL;
2883 END IF;
2884
2885 BEGIN
2886 -- Bug 5929771 : Check unique value existence before inserting to avoid
2887 -- expensive DUP_VAL_ON_INDEX exception (Nishant 16-APR-2007)
2888 -- If it was existing before, we would have deleted it when we checked
2889 -- existence in GNR Log table (above).
2890 -- if data already deleted above then no need to perform existence check here.
2891 IF (l_gnr_deleted = 'Y' ) THEN
2892 l_gnr_exist := 'N';
2893 ELSE
2894 OPEN c_check_gnr_exist(p_location_id,p_location_table_name,p_map_dtls_tbl(i).geography_type);
2895 FETCH c_check_gnr_exist INTO l_gnr_exist;
2896 l_gnr_exist := NVL(l_gnr_exist,'N');
2897 CLOSE c_check_gnr_exist;
2898 END IF;
2899
2900 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2901 hz_utility_v2pub.debug
2902 (p_message => 'For Location Id:'||p_location_id||',geo type:'||
2903 p_map_dtls_tbl(i).geography_type||', GNR Exists :'||l_gnr_exist,
2904 p_prefix => l_debug_prefix,
2905 p_msg_level => fnd_log.level_statement,
2906 p_module_prefix => l_module_prefix,
2907 p_module => l_module
2908 );
2909 END IF;
2910
2911 IF (l_gnr_exist <> 'Y') THEN
2912 INSERT INTO hz_geo_name_references
2913 (location_id, geography_id, location_table_name,
2914 object_version_number, geography_type, last_updated_by,
2915 creation_date, created_by, last_update_date,
2916 last_update_login, program_id, program_login_id,
2917 program_application_id,request_id)
2918 VALUES
2919 (p_location_id, l_map_dtls_tbl(i).geography_id,p_location_table_name,
2920 1, p_map_dtls_tbl(i).geography_type, l_last_updated_by,
2921 l_creation_date, l_created_by,
2922 l_last_update_date, l_last_update_login,
2923 l_program_id, l_conc_login_id,
2924 l_program_application_id, l_request_id);
2925 END IF;
2926
2927 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- should not hit this error anymore (bug 5929771)
2928 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2929 hz_utility_v2pub.debug
2930 (p_message => 'EXCEPTION DUP_VAL_ON_INDEX during GNR insert/update for '||
2931 'Location Id:'||p_location_id||',geo_type:'||p_map_dtls_tbl(i).geography_type||
2932 ' -'||SUBSTR(SQLERRM,1,100),
2933 p_prefix => l_debug_prefix,
2934 p_msg_level => fnd_log.level_statement,
2935 p_module_prefix => l_module_prefix,
2936 p_module => l_module
2937 );
2938 END IF;
2939 END; -- End of BEGIN for inserting in GNR Log
2940
2941 ELSE -- geography_id = NULL
2942 EXIT;
2943 END IF; -- End of geography id is NOT NULL check
2944
2945 EXIT WHEN i = p_map_dtls_tbl.LAST;
2946 i := p_map_dtls_tbl.NEXT(i);
2947 END LOOP;
2948
2949 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2950 hz_utility_v2pub.debug
2951 (p_message => 'After inserting records into hz_geo_name_references ',
2952 p_prefix => l_debug_prefix,
2953 p_msg_level => fnd_log.level_statement,
2954 p_module_prefix => l_module_prefix,
2955 p_module => l_module
2956 );
2957 END IF;
2958
2959 END IF; -- END OF p_map_dtls_table.count > 0 check for inserting into GNR tables
2960
2961 -- Update Location not to be done for GNR calls, only for Online Validate API
2962 IF ((NVL(l_api_purpose,'xxya') NOT IN ('R12UPGRADE','GNR'))
2963 AND
2964 (p_location_table_name = 'HZ_LOCATIONS'
2965 AND
2966 update_loc_yn(p_loc_components_rec,l_map_dtls_tbl) = 'Y')) THEN
2967
2968 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2969 hz_utility_v2pub.debug
2970 (p_message => 'Before updating record into update_location ',
2971 p_prefix => l_debug_prefix,
2972 p_msg_level => fnd_log.level_statement,
2973 p_module_prefix => l_module_prefix,
2974 p_module => l_module
2975 );
2976 END IF;
2977
2978 update_location (p_location_id, p_loc_components_rec,p_lock_flag, l_map_dtls_tbl,x_status);
2979
2980 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2981 hz_utility_v2pub.debug
2982 (p_message => 'After updating record into update_location ',
2983 p_prefix => l_debug_prefix,
2984 p_msg_level => fnd_log.level_statement,
2985 p_module_prefix => l_module_prefix,
2986 p_module => l_module
2987 );
2988 END IF;
2989
2990 END IF; -- END of update_location
2991
2992 END IF; -- END of retain_gnr check
2993
2994 EXCEPTION WHEN OTHERS THEN
2995 x_status := fnd_api.g_ret_sts_unexp_error;
2996 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
2997 hz_utility_v2pub.debug
2998 (p_message => 'EXCEPTION during create_gnr for '||
2999 'Location Id:'||p_location_id||',usage_code:'||p_usage_code||
3000 ' -'||SUBSTR(SQLERRM,1,100),
3001 p_prefix => l_debug_prefix,
3002 p_msg_level => fnd_log.level_exception,
3003 p_module_prefix => l_module_prefix,
3004 p_module => l_module
3005 );
3006 END IF;
3007 END create_gnr;
3008 --------------------------------------
3009 --------------------------------------
3010 FUNCTION check_GNR_For_Usage(
3011 p_location_id IN NUMBER,
3012 p_location_table_name IN VARCHAR2,
3013 p_usage_code IN VARCHAR2,
3014 p_mdu_tbl IN maploc_rec_tbl_type,
3015 x_status OUT NOCOPY varchar2
3016 ) RETURN BOOLEAN IS
3017
3018 CURSOR c_gnr(p_geography_type in varchar2) IS
3019 SELECT GEOGRAPHY_ID
3020 FROM HZ_GEO_NAME_REFERENCES
3021 WHERE LOCATION_ID = p_location_id
3022 AND GEOGRAPHY_TYPE = p_geography_type
3023 AND LOCATION_TABLE_NAME = p_location_table_name;
3024
3025 CURSOR c_gnr_log IS
3026 SELECT MAP_STATUS,USAGE_CODE
3027 FROm HZ_GEO_NAME_REFERENCE_LOG
3028 WHERE LOCATION_ID = p_location_id
3029 AND LOCATION_TABLE_NAME = p_location_table_name;
3030
3031 l_gnr_exists varchar2(1);
3032 l_success varchar2(1);
3033 l_usage_log_exists varchar2(1);
3034 l_geography_id number;
3035 i number;
3036
3037 l_last_updated_by NUMBER; -- hz_utility_v2pub.last_updated_by;
3038 l_creation_date DATE; -- hz_utility_v2pub.creation_date;
3039 l_created_by NUMBER; -- hz_utility_v2pub.created_by;
3040 l_last_update_date DATE; -- hz_utility_v2pub.last_update_date;
3041 l_last_update_login NUMBER; -- hz_utility_v2pub.last_update_login;
3042 l_program_id NUMBER; -- hz_utility_v2pub.program_id;
3043 l_conc_login_id NUMBER; -- fnd_global.conc_login_id;
3044 l_program_application_id NUMBER; --hz_utility_v2pub.program_application_id;
3045 l_request_id NUMBER; -- NVL(hz_utility_v2pub.request_id, -1);
3046
3047 BEGIN
3048
3049 -- Initialize variables (perf improvement bug 5130993)
3050 l_last_updated_by := hz_utility_v2pub.last_updated_by;
3051 l_creation_date := hz_utility_v2pub.creation_date;
3052 l_created_by := hz_utility_v2pub.created_by;
3053 l_last_update_date := hz_utility_v2pub.last_update_date;
3054 l_last_update_login := hz_utility_v2pub.last_update_login;
3055 l_program_id := hz_utility_v2pub.program_id;
3056 l_conc_login_id := fnd_global.conc_login_id;
3057 l_program_application_id := hz_utility_v2pub.program_application_id;
3058 l_request_id := NVL(hz_utility_v2pub.request_id, -1);
3059
3060 x_status := FND_API.g_ret_sts_success;
3061
3062 l_success := 'N';
3063 l_usage_log_exists := 'N';
3064 FOR l_c_gnr_log IN c_gnr_log LOOP
3065 l_gnr_exists := 'Y';
3066 IF l_c_gnr_log.USAGE_CODE = p_usage_code THEN
3067 IF l_c_gnr_log.MAP_STATUS = 'S' THEN
3068 RETURN TRUE;
3069 ELSE
3070 l_usage_log_exists := 'Y';
3071 END IF;
3072 END IF;
3073 END LOOP;
3074
3075 IF l_gnr_exists = 'Y' THEN
3076 l_success := 'Y';
3077 IF p_mdu_tbl.COUNT > 0 THEN
3078 i := p_mdu_tbl.FIRST;
3079 LOOP
3080
3081 OPEN c_gnr(p_mdu_tbl(i).GEOGRAPHY_TYPE);
3082 FETCH c_gnr INTO l_geography_id;
3083 IF c_gnr%NOTFOUND THEN
3084 l_success := 'N';
3085 END IF;
3086 CLOSE c_gnr;
3087 EXIT WHEN i = p_mdu_tbl.LAST;
3088 i := p_mdu_tbl.NEXT(i);
3089 END LOOP;
3090 END IF;
3091 END IF;
3092
3093 IF l_success = 'Y' THEN
3094 IF l_usage_log_exists = 'Y' THEN
3095 UPDATE hz_geo_name_reference_log
3096 SET map_status = 'S',
3097 object_version_number = object_version_number + 1,
3098 last_updated_by = l_last_updated_by,
3099 last_update_date = l_last_update_date,
3100 last_update_login = l_last_update_login
3101 WHERE location_id = p_location_id
3102 AND location_table_name = p_location_table_name
3103 AND usage_code = p_usage_code;
3104 ELSE
3105 INSERT INTO hz_geo_name_reference_log
3106 (location_id, location_table_name,usage_code,
3107 message_text,
3108 object_version_number, map_status,
3109 last_updated_by, creation_date,
3110 created_by, last_update_date,
3111 last_update_login, program_id,
3112 program_login_id,program_application_id,request_id)
3113 VALUES
3114 (p_location_id, p_location_table_name, p_usage_code, NULL, 1, 'S',
3115 l_last_updated_by,l_creation_date,
3116 l_created_by, l_last_update_date,
3117 l_last_update_login,l_program_id,
3118 l_conc_login_id, l_program_application_id,
3119 l_request_id);
3120 END IF;
3121 RETURN TRUE;
3122 ELSE
3123 RETURN FALSE;
3124 END IF;
3125
3126 RETURN FALSE;
3127 EXCEPTION WHEN OTHERS THEN
3128 x_status := fnd_api.g_ret_sts_unexp_error;
3129 RETURN FALSE;
3130 END check_GNR_For_Usage;
3131 --------------------------------------
3132 --------------------------------------
3133 FUNCTION fix_multiparent(
3134 p_geography_id IN NUMBER,
3135 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type
3136 ) RETURN BOOLEAN IS
3137 i number;
3138 l_parent_geography_id number;
3139 l_parent_geography_type varchar2(30);
3140 BEGIN
3141 IF x_map_dtls_tbl.COUNT > 0 THEN
3142 i:= x_map_dtls_tbl.FIRST;
3143 LOOP
3144 IF x_map_dtls_tbl(i).GEOGRAPHY_ID = p_geography_id THEN
3145 RETURN TRUE;
3146 END IF;
3147
3148 IF (x_map_dtls_tbl(i).GEOGRAPHY_ID IS NULL AND x_map_dtls_tbl(i).LOC_COMPVAL IS NULL) THEN
3149 -- This is a multiple parent case and user has not passed in a value to identify a unique record.
3150 -- MUST be done later. (two level missing, second level value passed. If we can identify the record, it will still fail)
3151 -- Need to think about a new logic.
3152 RETURN FALSE;
3153 END IF;
3154
3155 IF (x_map_dtls_tbl(i).GEOGRAPHY_ID IS NULL AND x_map_dtls_tbl(i).LOC_COMPVAL IS NOT NULL) THEN
3156 BEGIN
3157 SELECT g.GEOGRAPHY_ID
3158 INTO x_map_dtls_tbl(i).GEOGRAPHY_ID
3159 FROM HZ_GEOGRAPHIES g,HZ_HIERARCHY_NODES hn
3160 WHERE g.GEOGRAPHY_ID = hn.CHILD_ID
3161 AND g.GEOGRAPHY_TYPE = hn.CHILD_OBJECT_TYPE
3162 AND hn.CHILD_TABLE_NAME = 'HZ_GEOGRAPHIES'
3163 AND hn.HIERARCHY_TYPE = 'MASTER_REF'
3164 AND hn.PARENT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3165 AND hn.PARENT_ID = l_parent_geography_id
3166 AND hn.PARENT_OBJECT_TYPE = l_parent_geography_type
3167 AND SYSDATE between hn.EFFECTIVE_START_DATE AND hn.EFFECTIVE_END_DATE
3168 AND SYSDATE BETWEEN g.START_DATE AND g.END_DATE
3169 AND EXISTS (SELECT NULL
3170 FROM HZ_GEOGRAPHY_IDENTIFIERS gi
3171 WHERE g.GEOGRAPHY_ID = gi.GEOGRAPHY_ID
3172 AND gi.GEOGRAPHY_TYPE = g.GEOGRAPHY_TYPE
3173 AND gi.GEOGRAPHY_USE = 'MASTER_REF'
3174 AND upper(gi.IDENTIFIER_VALUE) = upper(x_map_dtls_tbl(i).LOC_COMPVAL));
3175 EXCEPTION WHEN OTHERS THEN
3176 RETURN FALSE;
3177 END;
3178 END IF;
3179
3180 l_parent_geography_id := x_map_dtls_tbl(i).GEOGRAPHY_ID;
3181 l_parent_geography_type := x_map_dtls_tbl(i).GEOGRAPHY_TYPE;
3182 EXIT WHEN i = x_map_dtls_tbl.LAST;
3183 i := x_map_dtls_tbl.NEXT(i);
3184 END LOOP;
3185 END IF;
3186 RETURN TRUE;
3187 END fix_multiparent;
3188 --------------------------------------
3189 -------------------------------------
3190 PROCEDURE fix_no_match(
3191 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type,
3192 x_status OUT NOCOPY VARCHAR2
3193 ) IS
3194 l_map_dtls_tbl maploc_rec_tbl_type;
3195 l_map_dtls_tbl_null maploc_rec_tbl_type;
3196
3197 PROCEDURE prcess_no_match(
3198 p_iteration IN NUMBER,
3199 p_country_geo_id IN NUMBER,
3200 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type,
3201 x_status OUT NOCOPY VARCHAR2
3202 ) IS
3203
3204 i number;
3205 l_child_geography_id number;
3206 l_geography_id number;
3207 l_geography_name varchar2(360);
3208 l_child_geography_type varchar2(30);
3209 l_country_geo_type varchar2(30);
3210
3211 BEGIN
3212 x_status := FND_API.g_ret_sts_success;
3213
3214 --hk_debugl('Iteration : '||to_char(p_iteration));
3215 l_country_geo_type := 'COUNTRY';
3216
3217 IF p_iteration = 1 THEN
3218 l_child_geography_id := NULL;
3219 l_child_geography_type := NULL;
3220 ELSE
3221 l_child_geography_id := x_map_dtls_tbl(p_iteration-1).GEOGRAPHY_ID;
3222 l_child_geography_type := x_map_dtls_tbl(p_iteration-1).GEOGRAPHY_TYPE;
3223 END IF;
3224
3225 IF x_map_dtls_tbl(p_iteration).LOC_COMPVAL IS NULL THEN
3226 IF l_child_geography_id IS NOT NULL THEN
3227 BEGIN
3228 SELECT g.GEOGRAPHY_ID,g.GEOGRAPHY_NAME
3229 INTO l_geography_id,l_geography_name
3230 FROM HZ_GEOGRAPHIES g,HZ_HIERARCHY_NODES hn
3231 WHERE g.GEOGRAPHY_ID = hn.PARENT_ID
3232 AND g.GEOGRAPHY_TYPE = hn.PARENT_OBJECT_TYPE
3233 AND hn.PARENT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3234 AND hn.HIERARCHY_TYPE = 'MASTER_REF'
3235 AND hn.level_number = 1
3236 AND hn.CHILD_TABLE_NAME = 'HZ_GEOGRAPHIES'
3237 AND hn.CHILD_ID = l_child_geography_id
3238 AND hn.CHILD_OBJECT_TYPE = l_child_geography_type
3239 AND SYSDATE between hn.EFFECTIVE_START_DATE AND hn.EFFECTIVE_END_DATE
3240 AND SYSDATE BETWEEN g.START_DATE AND g.END_DATE;
3241
3242 x_map_dtls_tbl(p_iteration).GEOGRAPHY_ID := l_geography_id;
3243 x_map_dtls_tbl(p_iteration).LOC_COMPVAL := l_geography_name;
3244
3245 --hk_debugl('Case 1');
3246 EXCEPTION WHEN OTHERS THEN
3247 --hk_debugl('Case 1 Exception');
3248 --hk_debugl('Child Geo : '||to_char(l_child_geography_id));
3249 --hk_debugl('Child Geo Type : '||l_child_geography_type);
3250 NULL;
3251 END;
3252 END IF;
3253 ELSE -- Location component value is not null
3254 IF l_child_geography_id IS NOT NULL THEN
3255 BEGIN
3256 SELECT g.GEOGRAPHY_ID,g.GEOGRAPHY_NAME
3257 INTO l_geography_id,l_geography_name
3258 FROM HZ_GEOGRAPHIES g,HZ_HIERARCHY_NODES hn
3259 -- Nishant
3260 --WHERE g.GEOGRAPHY_ID = hn.PARENT_ID
3261 WHERE g.GEOGRAPHY_ID = hn.PARENT_ID+0
3262 AND g.GEOGRAPHY_TYPE = hn.PARENT_OBJECT_TYPE
3263 AND hn.PARENT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3264 AND hn.HIERARCHY_TYPE = 'MASTER_REF'
3265 AND hn.level_number = 1
3266 AND hn.CHILD_TABLE_NAME = 'HZ_GEOGRAPHIES'
3267 AND hn.CHILD_ID = l_child_geography_id
3268 AND hn.CHILD_OBJECT_TYPE = l_child_geography_type
3269 AND SYSDATE BETWEEN hn.EFFECTIVE_START_DATE AND hn.EFFECTIVE_END_DATE
3270 AND SYSDATE BETWEEN g.START_DATE AND g.END_DATE
3271 AND EXISTS (SELECT NULL
3272 FROM HZ_GEOGRAPHY_IDENTIFIERS gi
3273 WHERE g.GEOGRAPHY_ID = gi.GEOGRAPHY_ID
3274 AND gi.GEOGRAPHY_TYPE = g.GEOGRAPHY_TYPE
3275 AND gi.geography_type = x_map_dtls_tbl(p_iteration).GEOGRAPHY_TYPE
3276 AND gi.GEOGRAPHY_USE = 'MASTER_REF'
3277 AND upper(gi.IDENTIFIER_VALUE) = upper(x_map_dtls_tbl(p_iteration).LOC_COMPVAL));
3278
3279 x_map_dtls_tbl(p_iteration).GEOGRAPHY_ID := l_geography_id;
3280 x_map_dtls_tbl(p_iteration).LOC_COMPVAL := l_geography_name;
3281
3282 --hk_debugl('Case 2');
3283 EXCEPTION WHEN OTHERS THEN
3284 --hk_debugl('Case 2 Exception');
3285 --hk_debugl('Child Geo : '||to_char(l_child_geography_id));
3286 --hk_debugl('Child Geo Type : '||l_child_geography_type);
3287 --hk_debugl('Geography Type : '||x_map_dtls_tbl(p_iteration).GEOGRAPHY_TYPE);
3288 --hk_debugl('Loc Comp Val : '||x_map_dtls_tbl(p_iteration).LOC_COMPVAL);
3289 NULL;
3290 END;
3291 ELSE -- Loc compoent value is not null and child_geo_id is null
3292 BEGIN
3293 SELECT g.GEOGRAPHY_ID,g.GEOGRAPHY_NAME
3294 INTO l_geography_id,l_geography_name
3295 FROM HZ_GEOGRAPHIES g,HZ_HIERARCHY_NODES hn
3296 WHERE g.GEOGRAPHY_ID = hn.CHILD_ID
3297 AND g.GEOGRAPHY_TYPE = hn.CHILD_OBJECT_TYPE
3298 AND hn.CHILD_TABLE_NAME = 'HZ_GEOGRAPHIES'
3299 AND hn.HIERARCHY_TYPE = 'MASTER_REF'
3300 AND hn.level_number = x_map_dtls_tbl(p_iteration).LOC_SEQ_NUM - 1
3301 AND hn.PARENT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3302 AND hn.PARENT_ID+0 = p_country_geo_id
3303 AND hn.PARENT_OBJECT_TYPE = l_country_geo_type
3304 AND SYSDATE BETWEEN hn.EFFECTIVE_START_DATE AND hn.EFFECTIVE_END_DATE
3305 AND SYSDATE BETWEEN g.START_DATE AND g.END_DATE
3306 AND EXISTS (SELECT NULL
3307 FROM HZ_GEOGRAPHY_IDENTIFIERS gi
3308 WHERE g.GEOGRAPHY_ID = gi.GEOGRAPHY_ID
3309 AND gi.GEOGRAPHY_TYPE = g.GEOGRAPHY_TYPE
3310 AND gi.GEOGRAPHY_USE = 'MASTER_REF'
3311 AND gi.geography_type = x_map_dtls_tbl(p_iteration).GEOGRAPHY_TYPE
3312 AND upper(gi.IDENTIFIER_VALUE) = upper(x_map_dtls_tbl(p_iteration).LOC_COMPVAL));
3313
3314 x_map_dtls_tbl(p_iteration).GEOGRAPHY_ID := l_geography_id;
3315 x_map_dtls_tbl(p_iteration).LOC_COMPVAL := l_geography_name;
3316
3317 --hk_debugl('Case 3');
3318 EXCEPTION WHEN OTHERS THEN
3319 --hk_debugl('Case 3 Exception');
3320 --hk_debugl('Country Geo : '||to_char(p_country_geo_id));
3321 --hk_debugl('Geography Type : '||x_map_dtls_tbl(p_iteration).GEOGRAPHY_TYPE);
3322 --hk_debugl('Loc Comp Val : '||x_map_dtls_tbl(p_iteration).LOC_COMPVAL);
3323 NULL;
3324 END;
3325 END IF;
3326 END IF;
3327
3328 IF p_iteration < x_map_dtls_tbl.COUNT - 1 THEN
3329 prcess_no_match(p_iteration+1,p_country_geo_id,x_map_dtls_tbl,x_status);
3330 END IF;
3331
3332 END prcess_no_match;
3333 BEGIN
3334 x_status := FND_API.g_ret_sts_success;
3335 IF x_map_dtls_tbl.COUNT > 0 THEN
3336 reverse_tbl(x_map_dtls_tbl,l_map_dtls_tbl);
3337 END IF;
3338
3339 prcess_no_match(1,x_map_dtls_tbl(1).GEOGRAPHY_ID,l_map_dtls_tbl,x_status);
3340
3341 x_map_dtls_tbl := l_map_dtls_tbl_null;
3342 reverse_tbl(l_map_dtls_tbl,x_map_dtls_tbl);
3343 END fix_no_match;
3344 --------------------------------------
3345 --------------------------------------
3346 PROCEDURE getMinValStatus(
3347 p_mdu_tbl IN maploc_rec_tbl_type,
3348 x_status IN OUT NOCOPY VARCHAR2
3349 ) IS
3350 i number;
3351 BEGIN
3352 -- If there is null component value found this API will return status "E'. If all components are not null
3353 -- Then success status will be returned. else it will return the status that came in.
3354 IF p_mdu_tbl.COUNT > 0 THEN
3355 i := p_mdu_tbl.FIRST;
3356 LOOP
3357 If p_mdu_tbl(i).LOC_COMPVAL IS NULL THEN
3358 x_status := FND_API.g_ret_sts_error;
3359 RETURN;
3360 END IF;
3361 EXIT WHEN i = p_mdu_tbl.LAST;
3362 i := p_mdu_tbl.NEXT(i);
3363 END LOOP;
3364 x_status := FND_API.g_ret_sts_success;
3365
3366 END IF;
3367 END getMinValStatus;
3368 --------------------------------------
3369 --------------------------------------
3370 FUNCTION fix_child(
3371 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type
3372 ) RETURN BOOLEAN IS
3373 i number;
3374 l_parent_geography_id number;
3375 l_parent_geography_type varchar2(30);
3376 BEGIN
3377 IF x_map_dtls_tbl.COUNT > 0 THEN
3378 i:= x_map_dtls_tbl.FIRST;
3379 LOOP
3380 IF (x_map_dtls_tbl(i).GEOGRAPHY_ID IS NULL AND x_map_dtls_tbl(i).LOC_COMPVAL IS NULL) THEN
3381 BEGIN
3382 SELECT hn.CHILD_ID
3383 INTO x_map_dtls_tbl(i).GEOGRAPHY_ID
3384 FROM HZ_HIERARCHY_NODES hn
3385 WHERE hn.CHILD_OBJECT_TYPE = x_map_dtls_tbl(i).GEOGRAPHY_TYPE
3386 AND hn.CHILD_TABLE_NAME = 'HZ_GEOGRAPHIES'
3387 AND hn.HIERARCHY_TYPE = 'MASTER_REF'
3388 AND hn.PARENT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3389 AND hn.PARENT_ID = l_parent_geography_id
3390 AND hn.PARENT_OBJECT_TYPE = l_parent_geography_type
3391 AND SYSDATE BETWEEN hn.EFFECTIVE_START_DATE AND hn.EFFECTIVE_END_DATE;
3392 EXCEPTION WHEN OTHERS THEN
3393 RETURN FALSE;
3394 END;
3395 END IF;
3396
3397 l_parent_geography_id := x_map_dtls_tbl(i).GEOGRAPHY_ID;
3398 l_parent_geography_type := x_map_dtls_tbl(i).GEOGRAPHY_TYPE;
3399 EXIT WHEN i = x_map_dtls_tbl.LAST;
3400 i := x_map_dtls_tbl.NEXT(i);
3401 END LOOP;
3402 END IF;
3403 RETURN TRUE;
3404 END fix_child;
3405 --------------------------------------
3406 --------------------------------------
3407 FUNCTION getLocCompCount(
3408 p_map_dtls_tbl IN maploc_rec_tbl_type) RETURN NUMBER IS
3409 i number;
3410 j number;
3411 BEGIN
3412 j := 0;
3413 IF p_map_dtls_tbl.COUNT > 1 THEN
3414 i:= p_map_dtls_tbl.FIRST;
3415 i:= p_map_dtls_tbl.NEXT(i);
3416 LOOP
3417 IF p_map_dtls_tbl(i).LOC_COMPVAL IS NOT NULL THEN
3418 j := j + 1;
3419 END IF;
3420 EXIT WHEN i = p_map_dtls_tbl.LAST;
3421 i := p_map_dtls_tbl.NEXT(i);
3422 END LOOP;
3423 END IF;
3424 RETURN j;
3425 END getLocCompCount;
3426 --------------------------------------
3427 --------------------------------------
3428 PROCEDURE getLocCompValues(
3429 P_loc_table IN VARCHAR2,
3430 p_loc_components_rec IN loc_components_rec_type,
3431 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type,
3432 x_status OUT NOCOPY VARCHAR2
3433 ) IS
3434
3435 TYPE NameValueRecType IS RECORD
3436 (Name varchar2(30),
3437 Value varchar2(150));
3438 TYPE NameValueTblType IS TABLE OF NameValueRecType
3439 INDEX BY BINARY_INTEGER;
3440
3441 l_name_value NameValueTblType;
3442 i number :=0;
3443
3444 FUNCTION getValue(p_name IN VARCHAR2) RETURN VARCHAR2 IS
3445 i number :=0;
3446 BEGIN
3447 IF l_name_value.COUNT > 0 THEN
3448 i := l_name_value.FIRST;
3449 LOOP
3450 IF l_name_value(i).name = p_name THEN
3451 RETURN l_name_value(i).value;
3452 END IF;
3453 EXIT WHEN i = l_name_value.LAST;
3454 i := l_name_value.NEXT(i);
3455 END LOOP;
3456 END IF;
3457 RETURN NULL;
3458 END;
3459
3460 BEGIN
3461
3462 x_status := FND_API.g_ret_sts_success;
3463 l_name_value(2).name := 'ADDRESS_STYLE';
3464 l_name_value(2).value := p_loc_components_rec.ADDRESS_STYLE;
3465 l_name_value(3).name := 'COUNTRY';
3466 l_name_value(3).value := p_loc_components_rec.COUNTRY;
3467 l_name_value(4).name := 'CITY';
3468 l_name_value(4).value := p_loc_components_rec.CITY;
3469 l_name_value(5).name := 'POSTAL_CODE';
3470 l_name_value(5).value := p_loc_components_rec.POSTAL_CODE;
3471 l_name_value(6).name := 'STATE';
3472 l_name_value(6).value := p_loc_components_rec.STATE;
3473 l_name_value(7).name := 'PROVINCE';
3474 l_name_value(7).value := p_loc_components_rec.PROVINCE;
3475 l_name_value(8).name := 'COUNTY';
3476 l_name_value(8).value := p_loc_components_rec.COUNTY;
3477 l_name_value(9).name := 'VALIDATE_COUNTRY_AGAINST';
3478 l_name_value(9).value := p_loc_components_rec.VALIDATE_COUNTRY_AGAINST;
3479 l_name_value(10).name := 'VALIDATE_STATE_AGAINST';
3480 l_name_value(10).value := p_loc_components_rec.VALIDATE_STATE_AGAINST;
3481 l_name_value(11).name := 'VALIDATE_PROVINCE_AGAINST';
3482 l_name_value(11).value := p_loc_components_rec.VALIDATE_PROVINCE_AGAINST;
3483 l_name_value(12).name := 'POSTAL_PLUS4_CODE';
3484 l_name_value(12).value := p_loc_components_rec.POSTAL_PLUS4_CODE;
3485 l_name_value(13).name := 'ATTRIBUTE1';
3486 l_name_value(13).value := p_loc_components_rec.ATTRIBUTE1;
3487 l_name_value(14).name := 'ATTRIBUTE2';
3488 l_name_value(14).value := p_loc_components_rec.ATTRIBUTE2;
3489 l_name_value(15).name := 'ATTRIBUTE3';
3490 l_name_value(15).value := p_loc_components_rec.ATTRIBUTE3;
3491 l_name_value(16).name := 'ATTRIBUTE4';
3492 l_name_value(16).value := p_loc_components_rec.ATTRIBUTE4;
3493 l_name_value(17).name := 'ATTRIBUTE5';
3494 l_name_value(17).value := p_loc_components_rec.ATTRIBUTE5;
3495 l_name_value(18).name := 'ATTRIBUTE6';
3496 l_name_value(18).value := p_loc_components_rec.ATTRIBUTE6;
3497 l_name_value(19).name := 'ATTRIBUTE7';
3498 l_name_value(19).value := p_loc_components_rec.ATTRIBUTE7;
3499 l_name_value(20).name := 'ATTRIBUTE8';
3500 l_name_value(20).value := p_loc_components_rec.ATTRIBUTE8;
3501 l_name_value(21).name := 'ATTRIBUTE9';
3502 l_name_value(21).value := p_loc_components_rec.ATTRIBUTE9;
3503 l_name_value(22).name := 'ATTRIBUTE10';
3504 l_name_value(22).value := p_loc_components_rec.ATTRIBUTE10;
3505
3506 IF x_map_dtls_tbl.COUNT > 0 THEN
3507 i := x_map_dtls_tbl.FIRST;
3508 LOOP
3509 x_map_dtls_tbl(i).LOC_COMPVAL := getValue(x_map_dtls_tbl(i).LOC_COMPONENT);
3510 EXIT WHEN i = x_map_dtls_tbl.LAST;
3511 i := x_map_dtls_tbl.NEXT(i);
3512 END LOOP;
3513 END IF;
3514 END;
3515
3516 END hz_gnr_util_pkg;
[Home] [Help]