1 PACKAGE BODY HZ_GNR_UTIL_PKG AS
2 /*$Header: ARHGNRUB.pls 120.35 2011/05/05 07:40:25 rgokavar 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 --Bug 8241862
2141 IF map_exists ('POSTAL_CODE',p_map_dtls_tbl) = TRUE THEN
2142 IF(HZ_GNR_UTIL_PKG.postal_code_to_validate(x_loc_components_rec.COUNTRY,x_loc_components_rec.POSTAL_CODE)=getValue('POSTAL_CODE',p_map_dtls_tbl)) THEN
2143 NULL;
2144 ELSE
2145 x_loc_components_rec.POSTAL_CODE := getValue('POSTAL_CODE',p_map_dtls_tbl);
2146 END IF;
2147 END IF;
2148 IF map_exists ('STATE',p_map_dtls_tbl) = TRUE THEN
2149 x_loc_components_rec.STATE := getValue('STATE',p_map_dtls_tbl);
2150 END IF;
2151 IF map_exists ('PROVINCE',p_map_dtls_tbl) = TRUE THEN
2152 x_loc_components_rec.PROVINCE := getValue('PROVINCE',p_map_dtls_tbl);
2153 END IF;
2154 IF map_exists ('COUNTY',p_map_dtls_tbl) = TRUE THEN
2155 x_loc_components_rec.COUNTY := getValue('COUNTY',p_map_dtls_tbl);
2156 END IF;
2157 IF map_exists ('POSTAL_PLUS4_CODE',p_map_dtls_tbl) = TRUE THEN
2158 x_loc_components_rec.POSTAL_PLUS4_CODE := getValue('POSTAL_PLUS4_CODE',p_map_dtls_tbl);
2159 END IF;
2160 IF map_exists ('ATTRIBUTE1',p_map_dtls_tbl) = TRUE THEN
2161 x_loc_components_rec.ATTRIBUTE1 := getValue('ATTRIBUTE1',p_map_dtls_tbl);
2162 END IF;
2163 IF map_exists ('ATTRIBUTE2',p_map_dtls_tbl) = TRUE THEN
2164 x_loc_components_rec.ATTRIBUTE2 := getValue('ATTRIBUTE2',p_map_dtls_tbl);
2165 END IF;
2166 IF map_exists ('ATTRIBUTE3',p_map_dtls_tbl) = TRUE THEN
2167 x_loc_components_rec.ATTRIBUTE3 := getValue('ATTRIBUTE3',p_map_dtls_tbl);
2168 END IF;
2169 IF map_exists ('ATTRIBUTE4',p_map_dtls_tbl) = TRUE THEN
2170 x_loc_components_rec.ATTRIBUTE4 := getValue('ATTRIBUTE4',p_map_dtls_tbl);
2171 END IF;
2172 IF map_exists ('ATTRIBUTE5',p_map_dtls_tbl) = TRUE THEN
2173 x_loc_components_rec.ATTRIBUTE5 := getValue('ATTRIBUTE5',p_map_dtls_tbl);
2174 END IF;
2175 IF map_exists ('ATTRIBUTE6',p_map_dtls_tbl) = TRUE THEN
2176 x_loc_components_rec.ATTRIBUTE6 := getValue('ATTRIBUTE6',p_map_dtls_tbl);
2177 END IF;
2178 IF map_exists ('ATTRIBUTE7',p_map_dtls_tbl) = TRUE THEN
2179 x_loc_components_rec.ATTRIBUTE7 := getValue('ATTRIBUTE7',p_map_dtls_tbl);
2180 END IF;
2181 IF map_exists ('ATTRIBUTE8',p_map_dtls_tbl) = TRUE THEN
2182 x_loc_components_rec.ATTRIBUTE8 := getValue('ATTRIBUTE8',p_map_dtls_tbl);
2183 END IF;
2184 IF map_exists ('ATTRIBUTE9',p_map_dtls_tbl) = TRUE THEN
2185 x_loc_components_rec.ATTRIBUTE9 := getValue('ATTRIBUTE9',p_map_dtls_tbl);
2186 END IF;
2187 IF map_exists ('ATTRIBUTE10',p_map_dtls_tbl) = TRUE THEN
2188 x_loc_components_rec.ATTRIBUTE10 := getValue('ATTRIBUTE10',p_map_dtls_tbl);
2189 END IF;
2190 END putLocCompValues;
2191
2192 --------------------------------------
2193 PROCEDURE update_location (
2194 p_location_id IN number,
2195 p_loc_components_rec IN loc_components_rec_type,
2196 p_lock_flag IN varchar2,
2197 p_map_dtls_tbl IN maploc_rec_tbl_type,
2198 x_status OUT NOCOPY varchar2
2199 ) IS
2200
2201
2202 db_city VARCHAR2(60);
2203 db_state VARCHAR2(60);
2204 db_country VARCHAR2(60);
2205 db_county VARCHAR2(60);
2206 db_province VARCHAR2(60);
2207 db_postal_code VARCHAR2(60);
2208 db_postal_plus4_code VARCHAR2(60);
2209 db_attribute1 VARCHAR2(150);
2210 db_attribute2 VARCHAR2(150);
2211 db_attribute3 VARCHAR2(150);
2212 db_attribute4 VARCHAR2(150);
2213 db_attribute5 VARCHAR2(150);
2214 db_attribute6 VARCHAR2(150);
2215 db_attribute7 VARCHAR2(150);
2216 db_attribute8 VARCHAR2(150);
2217 db_attribute9 VARCHAR2(150);
2218 db_attribute10 VARCHAR2(150);
2219 db_wh_update_date DATE;
2220
2221 l_location_profile_rec hz_location_profile_pvt.location_profile_rec_type;
2222 l_loc_components_rec loc_components_rec_type;
2223 l_old_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
2224 l_new_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
2225 l_other_param_rec HZ_GNR_UTIL_PKG.loc_other_param_rec_type;
2226 l_wh_update_date DATE;
2227 l_address1 VARCHAR2(240);
2228 l_address2 VARCHAR2(240);
2229 l_address3 VARCHAR2(240);
2230 l_address4 VARCHAR2(240);
2231 l_actual_content_source VARCHAR2(30);
2232 l_address_key VARCHAR2(500);
2233 l_time_zone_id NUMBER;
2234 l_return_status VARCHAR2(30);
2235 l_msg_count NUMBER;
2236 l_msg_data VARCHAR2(2000);
2237 ll_return_status VARCHAR2(30);
2238 ll_msg_count NUMBER;
2239 ll_msg_data VARCHAR2(2000);
2240
2241 BEGIN
2242 l_loc_components_rec := p_loc_components_rec;
2243 -- if p_loc_flag is not true then the location has been locked already.
2244
2245 -- If the Location updation is allowed
2246 IF location_updation_allowed(p_location_id) then
2247
2248 IF p_lock_flag = 'T' OR p_lock_flag = FND_API.G_TRUE THEN
2249 -- get location components
2250 BEGIN
2251 SELECT COUNTRY, CITY, STATE, COUNTY, PROVINCE, POSTAL_CODE,POSTAL_PLUS4_CODE,
2252 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
2253 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
2254 WH_UPDATE_DATE, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, ACTUAL_CONTENT_SOURCE,
2255 TIMEZONE_ID, ADDRESS_KEY
2256 INTO l_loc_components_rec.country, l_loc_components_rec.city, l_loc_components_rec.state,
2257 l_loc_components_rec.county, l_loc_components_rec.province,
2258 l_loc_components_rec.postal_code, l_loc_components_rec.postal_plus4_code,
2259 l_loc_components_rec.attribute1,l_loc_components_rec.attribute2,
2260 l_loc_components_rec.attribute3,l_loc_components_rec.attribute4,
2261 l_loc_components_rec.attribute5, l_loc_components_rec.attribute6,
2262 l_loc_components_rec.attribute7,l_loc_components_rec.attribute8,
2263 l_loc_components_rec.attribute9,l_loc_components_rec.attribute10,
2264 l_wh_update_date, l_address1, l_address2, l_address3, l_address4
2265 , l_actual_content_source, l_time_zone_id, l_address_key
2266 FROM HZ_LOCATIONS
2267 WHERE LOCATION_ID = p_location_id
2268 FOR UPDATE OF LOCATION_ID NOWAIT;
2269 EXCEPTION
2270 WHEN NO_DATA_FOUND THEN
2271 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
2272 FND_MESSAGE.SET_TOKEN('RECORD', 'hz_locations');
2273 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_location_id));
2274 FND_MSG_PUB.ADD;
2275 x_status := FND_API.G_RET_STS_ERROR;
2276 END; -- end of SELECT
2277
2278 ELSE -- do not lock the location record
2279 -- get location components
2280 BEGIN
2281 SELECT COUNTRY, CITY, STATE, COUNTY, PROVINCE, POSTAL_CODE,POSTAL_PLUS4_CODE,
2282 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
2283 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
2284 WH_UPDATE_DATE, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, ACTUAL_CONTENT_SOURCE,
2285 TIMEZONE_ID, ADDRESS_KEY
2286 INTO l_loc_components_rec.country, l_loc_components_rec.city, l_loc_components_rec.state,
2287 l_loc_components_rec.county, l_loc_components_rec.province,
2288 l_loc_components_rec.postal_code, l_loc_components_rec.postal_plus4_code,
2289 l_loc_components_rec.attribute1,l_loc_components_rec.attribute2,
2290 l_loc_components_rec.attribute3,l_loc_components_rec.attribute4,
2291 l_loc_components_rec.attribute5, l_loc_components_rec.attribute6,
2292 l_loc_components_rec.attribute7,l_loc_components_rec.attribute8,
2293 l_loc_components_rec.attribute9,l_loc_components_rec.attribute10,
2294 l_wh_update_date, l_address1, l_address2, l_address3, l_address4
2295 , l_actual_content_source, l_time_zone_id, l_address_key
2296 FROM HZ_LOCATIONS
2297 WHERE LOCATION_ID = p_location_id;
2298 EXCEPTION
2299 WHEN NO_DATA_FOUND THEN
2300 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
2301 FND_MESSAGE.SET_TOKEN('RECORD', 'hz_locations');
2302 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_location_id));
2303 FND_MSG_PUB.ADD;
2304 x_status := FND_API.G_RET_STS_ERROR;
2305 END; -- end of SELECT ;
2306 END IF;
2307
2308 db_country := l_loc_components_rec.country;
2309 db_city := l_loc_components_rec.city ;
2310 db_state := l_loc_components_rec.state ;
2311 db_county := l_loc_components_rec.county ;
2312 db_province := l_loc_components_rec.province;
2313 db_postal_code := l_loc_components_rec.postal_code;
2314 db_postal_plus4_code := l_loc_components_rec.postal_plus4_code;
2315 db_attribute1 := l_loc_components_rec.attribute1;
2316 db_attribute2 := l_loc_components_rec.attribute2;
2317 db_attribute3 := l_loc_components_rec.attribute3;
2318 db_attribute4 := l_loc_components_rec.attribute4;
2319 db_attribute5 := l_loc_components_rec.attribute5;
2320 db_attribute6 := l_loc_components_rec.attribute6;
2321 db_attribute7 := l_loc_components_rec.attribute7;
2322 db_attribute8 := l_loc_components_rec.attribute8;
2323 db_attribute9 := l_loc_components_rec.attribute9;
2324 db_attribute10 := l_loc_components_rec.attribute10;
2325 db_wh_update_date := l_wh_update_date;
2326
2327 putLocCompValues(p_map_dtls_tbl,l_loc_components_rec);
2328
2329 -- Fix for Bug 5231893. Added UPPER to make case insensitive comaprison. It does not make sense to update
2330 -- location rec as part of GNR creation, just because fetched value from hz_locations is in different case
2331 -- than that derived from HZ_GEOGRAPHIES. It also improves performance by not updating that location rec
2332 -- and doing subsequent processes related to update_location. (18-MAY-2006 Nishant)
2333 IF NVL(UPPER(db_country),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.country),fnd_api.g_miss_char)
2334 AND NVL(UPPER(db_city),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.city),fnd_api.g_miss_char)
2335 AND NVL(UPPER(db_state),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.state),fnd_api.g_miss_char)
2336 AND NVL(UPPER(db_county),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.county),fnd_api.g_miss_char)
2337 AND NVL(UPPER(db_province),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.province),fnd_api.g_miss_char)
2338 AND NVL(UPPER(db_postal_code),fnd_api.g_miss_char)= NVL(UPPER(l_loc_components_rec.postal_code),fnd_api.g_miss_char)
2339 AND NVL(UPPER(db_attribute1),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute1),fnd_api.g_miss_char)
2340 AND NVL(UPPER(db_attribute2),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute2),fnd_api.g_miss_char)
2341 AND NVL(UPPER(db_attribute3),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute3),fnd_api.g_miss_char)
2342 AND NVL(UPPER(db_attribute4),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute4),fnd_api.g_miss_char)
2343 AND NVL(UPPER(db_attribute5),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute5),fnd_api.g_miss_char)
2344 AND NVL(UPPER(db_attribute6),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute6),fnd_api.g_miss_char)
2345 AND NVL(UPPER(db_attribute7),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute7),fnd_api.g_miss_char)
2346 AND NVL(UPPER(db_attribute8),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute8),fnd_api.g_miss_char)
2347 AND NVL(UPPER(db_attribute9),fnd_api.g_miss_char) = NVL(UPPER(l_loc_components_rec.attribute9),fnd_api.g_miss_char)
2348 AND NVL(UPPER(db_attribute10),fnd_api.g_miss_char)= NVL(UPPER(l_loc_components_rec.attribute10),fnd_api.g_miss_char)
2349 THEN
2350 NULL;
2351 ELSE
2352
2353 -- Old location record will have the data from database.
2354 l_old_location_rec.location_id := p_location_id;
2355 l_old_location_rec.country := db_country;
2356 l_old_location_rec.city := db_city;
2357 l_old_location_rec.state := db_state;
2358 l_old_location_rec.county := db_county;
2359 l_old_location_rec.province := db_province;
2360 l_old_location_rec.postal_code := db_postal_code;
2361 l_old_location_rec.postal_plus4_code := db_postal_plus4_code;
2362 l_old_location_rec.attribute1 := db_attribute1;
2363 l_old_location_rec.attribute2 := db_attribute2;
2364 l_old_location_rec.attribute3 := db_attribute3;
2365 l_old_location_rec.attribute4 := db_attribute4;
2366 l_old_location_rec.attribute5 := db_attribute5;
2367 l_old_location_rec.attribute6 := db_attribute6;
2368 l_old_location_rec.attribute7 := db_attribute7;
2369 l_old_location_rec.attribute8 := db_attribute8;
2370 l_old_location_rec.attribute9 := db_attribute9;
2371 l_old_location_rec.attribute10 := db_attribute10;
2372 l_old_location_rec.address1 := l_address1;
2373 l_old_location_rec.address2 := l_address2;
2374 l_old_location_rec.address3 := l_address3;
2375 l_old_location_rec.address4 := l_address4;
2376 l_old_location_rec.timezone_id := l_time_zone_id;
2377 l_old_location_rec.address_key := l_address_key;
2378
2379 -- Old location record will have the data derived by GNR.
2380 -- If country, city, state or postal_code is changed
2381 -- set the timezone_id is NULL so that pre_location_update
2382 -- will get the timezone based on new values.
2383 l_new_location_rec.location_id := p_location_id;
2384 l_new_location_rec.country := l_loc_components_rec.country;
2385 l_new_location_rec.city := l_loc_components_rec.city;
2386 l_new_location_rec.state := l_loc_components_rec.state;
2387 l_new_location_rec.county := l_loc_components_rec.county;
2388 l_new_location_rec.province := l_loc_components_rec.province;
2389 l_new_location_rec.postal_code := l_loc_components_rec.postal_code;
2390 l_new_location_rec.postal_plus4_code := l_loc_components_rec.postal_plus4_code;
2391 l_new_location_rec.attribute1 := l_loc_components_rec.attribute1;
2392 l_new_location_rec.attribute2 := l_loc_components_rec.attribute2;
2393 l_new_location_rec.attribute3 := l_loc_components_rec.attribute3;
2394 l_new_location_rec.attribute4 := l_loc_components_rec.attribute4;
2395 l_new_location_rec.attribute5 := l_loc_components_rec.attribute5;
2396 l_new_location_rec.attribute6 := l_loc_components_rec.attribute6;
2397 l_new_location_rec.attribute7 := l_loc_components_rec.attribute7;
2398 l_new_location_rec.attribute8 := l_loc_components_rec.attribute8;
2399 l_new_location_rec.attribute9 := l_loc_components_rec.attribute9;
2400 l_new_location_rec.attribute10 := l_loc_components_rec.attribute10;
2401 l_new_location_rec.address1 := l_address1;
2402 l_new_location_rec.address2 := l_address2;
2403 l_new_location_rec.address3 := l_address3;
2404 l_new_location_rec.address4 := l_address4;
2405 l_new_location_rec.address_key := l_address_key;
2406 IF (l_new_location_rec.country IS NOT NULL AND
2407 NVL(UPPER(l_old_location_rec.country), fnd_api.g_miss_char) <> UPPER(l_new_location_rec.country))
2408 OR
2409 (l_new_location_rec.city IS NOT NULL AND
2410 NVL(UPPER(l_old_location_rec.city), fnd_api.g_miss_char) <> UPPER(l_new_location_rec.city))
2411 OR
2412 (l_new_location_rec.state IS NOT NULL AND
2413 NVL(UPPER(l_old_location_rec.state), fnd_api.g_miss_char) <> UPPER(l_new_location_rec.state))
2414 OR
2415 (l_new_location_rec.postal_code IS NOT NULL AND
2416 NVL(UPPER(l_old_location_rec.postal_code), fnd_api.g_miss_char) <> UPPER(l_new_location_rec.postal_code))
2417 THEN
2418 l_new_location_rec.timezone_id := NULL;
2419 ELSE
2420 l_new_location_rec.timezone_id := l_time_zone_id;
2421 END IF;
2422
2423 -- Call pre_location_update to get timezone_id and address_key.
2424 begin
2425 pre_location_update( p_old_location_rec => l_old_location_rec,
2426 p_new_location_rec => l_new_location_rec,
2427 p_other_location_params => l_other_param_rec,
2428 x_return_status => ll_return_status,
2429 x_msg_count => ll_msg_count,
2430 x_msg_data => ll_msg_data );
2431 exception when others then
2432 null;
2433 end;
2434
2435 UPDATE HZ_LOCATIONS
2436 SET
2437 COUNTRY = l_loc_components_rec.country,
2438 CITY = l_loc_components_rec.city ,
2439 STATE = l_loc_components_rec.state ,
2440 COUNTY = l_loc_components_rec.county ,
2441 PROVINCE = l_loc_components_rec.province,
2442 POSTAL_CODE = l_loc_components_rec.postal_code,
2443 ATTRIBUTE1 = l_loc_components_rec.attribute1,
2444 ATTRIBUTE2 = l_loc_components_rec.attribute2,
2445 ATTRIBUTE3 = l_loc_components_rec.attribute3,
2446 ATTRIBUTE4 = l_loc_components_rec.attribute4,
2447 ATTRIBUTE5 = l_loc_components_rec.attribute5,
2448 ATTRIBUTE6 = l_loc_components_rec.attribute6,
2449 ATTRIBUTE7 = l_loc_components_rec.attribute7,
2450 ATTRIBUTE8 = l_loc_components_rec.attribute8,
2451 ATTRIBUTE9 = l_loc_components_rec.attribute9,
2452 ATTRIBUTE10 = l_loc_components_rec.attribute10,
2453 TIMEZONE_ID = l_new_location_rec.timezone_id,
2454 ADDRESS_KEY = l_new_location_rec.address_key
2455 WHERE LOCATION_ID = p_location_id;
2456
2457 -- fix for bug # 4169728. Set address_text to null in hz_cust_acct_sites_all table if,
2458 -- city, state, province or postal_code changes in hz_location table.
2459 -- address_text column in hz_cust_acct_sites_all is populated by the concurrent program,
2460 -- "Customer text data creation and indexing", if address_text is null.
2461 IF nvl(UPPER(db_city),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.city),fnd_api.g_miss_char)
2462 AND nvl(UPPER(db_state),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.state),fnd_api.g_miss_char)
2463 AND nvl(UPPER(db_province),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.province),fnd_api.g_miss_char)
2464 AND nvl(UPPER(db_postal_code),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.postal_code),fnd_api.g_miss_char)
2465 THEN
2466 NULL;
2467 ELSE
2468 UPDATE hz_cust_acct_sites_all cas
2469 SET cas.address_text = null
2470 WHERE cas.address_text IS NOT NULL
2471 AND EXISTS
2472 ( SELECT 1
2473 FROM HZ_PARTY_SITES ps
2474 WHERE ps.location_id = p_location_id
2475 AND cas.party_site_id = ps.party_site_id );
2476 END IF;
2477
2478 IF nvl(UPPER(db_country),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.country),fnd_api.g_miss_char)
2479 AND nvl(UPPER(db_city),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.city),fnd_api.g_miss_char)
2480 AND nvl(UPPER(db_state),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.state),fnd_api.g_miss_char)
2481 AND nvl(UPPER(db_county),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.county),fnd_api.g_miss_char)
2482 AND nvl(UPPER(db_province),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.province),fnd_api.g_miss_char)
2483 AND nvl(UPPER(db_postal_code),fnd_api.g_miss_char) = nvl(UPPER(l_loc_components_rec.postal_code),fnd_api.g_miss_char)
2484 THEN
2485 NULL;
2486 ELSE
2487 l_location_profile_rec.location_profile_id := NULL;
2488 l_location_profile_rec.location_id := p_location_id;
2489 l_location_profile_rec.actual_content_source := l_actual_content_source;
2490 l_location_profile_rec.effective_start_date := NULL;
2491 l_location_profile_rec.effective_end_date := NULL;
2492 l_location_profile_rec.date_validated := NULL;
2493 l_location_profile_rec.city := l_loc_components_rec.city;
2494 l_location_profile_rec.postal_code := l_loc_components_rec.postal_code;
2495 l_location_profile_rec.county := l_loc_components_rec.county;
2496 l_location_profile_rec.country := l_loc_components_rec.country;
2497 l_location_profile_rec.address1 := l_address1;
2498 l_location_profile_rec.address2 := l_address2;
2499 l_location_profile_rec.address3 := l_address3;
2500 l_location_profile_rec.address4 := l_address4;
2501
2502 IF(l_loc_components_rec.state IS NOT NULL) THEN
2503 l_location_profile_rec.prov_state_admin_code := l_loc_components_rec.state;
2504 ELSIF(l_loc_components_rec.province IS NOT NULL) THEN
2505 l_location_profile_rec.prov_state_admin_code := l_loc_components_rec.province;
2506 ELSE
2507 l_location_profile_rec.prov_state_admin_code := NULL;
2508 END IF;
2509
2510 l_return_status := FND_API.G_RET_STS_SUCCESS;
2511
2512 hz_location_profile_pvt.update_location_profile (
2513 p_location_profile_rec => l_location_profile_rec
2514 ,x_return_status => l_return_status
2515 ,x_msg_count => l_msg_count
2516 ,x_msg_data => l_msg_data );
2517
2518 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
2519 RAISE fnd_api.g_exc_error;
2520 ELSIF(l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2521 RAISE fnd_api.g_exc_unexpected_error;
2522 END IF;
2523 END IF;
2524
2525 -- Call post_location_update to do post update process.
2526 -- 1. Update de-normalized location components in HZ_PARTIES
2527 -- 2. Call to indicate location update to DQM(HZ_DQM_SYNC.sync_location).
2528 -- 3. 6. Invoke business event system.
2529 begin
2530 post_location_update( p_old_location_rec => l_old_location_rec,
2531 p_new_location_rec => l_new_location_rec,
2532 p_other_location_params => l_other_param_rec,
2533 x_return_status => ll_return_status,
2534 x_msg_count => ll_msg_count,
2535 x_msg_data => ll_msg_data );
2536 exception when others then
2537 null;
2538 end;
2539 END IF;
2540 END IF;
2541
2542 END update_location;
2543 --------------------------------------
2544 PROCEDURE create_gnr (
2545 p_location_id IN number,
2546 p_location_table_name IN varchar2,
2547 p_usage_code IN varchar2,
2548 p_map_status IN varchar2,
2549 p_loc_components_rec IN loc_components_rec_type,
2550 p_lock_flag IN varchar2,
2551 p_map_dtls_tbl IN maploc_rec_tbl_type,
2552 x_status OUT NOCOPY varchar2
2553 ) IS
2554 i number;
2555 l_module_prefix CONSTANT VARCHAR2(30) := 'HZ:ARHGNRUB:HZ_GNR_UTIL_PKG';
2556 l_module CONSTANT VARCHAR2(30) := 'ADDRESS_VALIDATION';
2557 l_debug_prefix VARCHAR2(30) := p_location_id;
2558 l_retain_flag VARCHAR2(10);
2559 l_map_dtls_tbl maploc_rec_tbl_type;
2560
2561 l_last_updated_by NUMBER; -- hz_utility_v2pub.last_updated_by;
2562 l_creation_date DATE; -- hz_utility_v2pub.creation_date;
2563 l_created_by NUMBER; -- hz_utility_v2pub.created_by;
2564 l_last_update_date DATE; -- hz_utility_v2pub.last_update_date;
2565 l_last_update_login NUMBER; -- hz_utility_v2pub.last_update_login;
2566 l_program_id NUMBER; -- hz_utility_v2pub.program_id;
2567 l_conc_login_id NUMBER; -- fnd_global.conc_login_id;
2568 l_program_application_id NUMBER; --hz_utility_v2pub.program_application_id;
2569 l_request_id NUMBER; -- NVL(hz_utility_v2pub.request_id, -1);
2570 l_api_purpose VARCHAR2(30);
2571
2572 -- Created below 2 cursors for performance reasons (perf team advice)
2573 -- to avoid DUP_VAL_ON_INDEX exception which is proving very costly
2574 -- during upgrade (Bug 5929771 : Nishant 16-APR-2007)
2575 l_gnr_log_exist VARCHAR2(10);
2576 l_gnr_exist VARCHAR2(10);
2577 l_gnr_deleted VARCHAR2(10);
2578
2579 CURSOR c_check_gnr_log_exist (p_location_id NUMBER,
2580 p_location_table_name VARCHAR2,
2581 p_usage_code VARCHAR2) IS
2582 SELECT 'Y'
2583 FROM hz_geo_name_reference_log
2584 WHERE location_id = p_location_id
2585 AND location_table_name = p_location_table_name
2586 AND usage_code = p_usage_code
2587 ;
2588
2589 CURSOR c_check_gnr_exist (p_location_id NUMBER,
2590 p_location_table_name VARCHAR2,
2591 p_geography_type VARCHAR2) IS
2592 SELECT 'Y'
2593 FROM hz_geo_name_references
2594 WHERE location_id = p_location_id
2595 AND location_table_name = p_location_table_name
2596 AND geography_type = p_geography_type
2597 ;
2598
2599 FUNCTION update_loc_yn(
2600 p_loc_components_rec IN loc_components_rec_type,
2601 p_map_dtls_tbl IN maploc_rec_tbl_type
2602 ) RETURN VARCHAR2 IS
2603 l_map_dtls_tbl maploc_rec_tbl_type;
2604 l_status varchar2(1);
2605 l_return varchar2(1);
2606 i number;
2607 j number;
2608 BEGIN
2609 l_return := 'N';
2610 l_map_dtls_tbl := p_map_dtls_tbl; -- This will populate all the derived Geography IDs
2611 getLocCompValues(
2612 P_loc_table => 'HZ_LOCATIONS',
2613 p_loc_components_rec => p_loc_components_rec,
2614 x_map_dtls_tbl => l_map_dtls_tbl,
2615 x_status => l_status);
2616
2617 IF p_map_dtls_tbl.COUNT > 0 THEN
2618 i := p_map_dtls_tbl.FIRST;
2619 LOOP
2620 IF p_map_dtls_tbl(i).GEOGRAPHY_ID IS NULL THEN
2621 EXIT;
2622 END IF;
2623
2624 IF l_map_dtls_tbl.COUNT > 0 THEN
2625 j := l_map_dtls_tbl.FIRST;
2626 LOOP
2627 IF p_map_dtls_tbl(i).GEOGRAPHY_ID = l_map_dtls_tbl(j).GEOGRAPHY_ID THEN
2628 -- Fix for Bug 5231893 (added UPPER to do case insensitive comaprison
2629 -- Added on 18-May-2006 Nishant)
2630 IF NVL(UPPER(p_map_dtls_tbl(i).LOC_COMPVAL),FND_API.G_MISS_CHAR) <>
2631 NVL(UPPER(l_map_dtls_tbl(j).LOC_COMPVAL),FND_API.G_MISS_CHAR) THEN
2632 l_return := 'Y';
2633 END IF;
2634 END IF;
2635 EXIT WHEN j = l_map_dtls_tbl.LAST;
2636 j := l_map_dtls_tbl.NEXT(j);
2637 END LOOP;
2638 END IF;
2639
2640 EXIT WHEN i = p_map_dtls_tbl.LAST;
2641 i := p_map_dtls_tbl.NEXT(i);
2642 END LOOP;
2643 END IF;
2644 RETURN l_return;
2645 END update_loc_yn;
2646
2647 FUNCTION retain_gnr_yn(
2648 p_location_id IN number,
2649 p_map_status IN varchar2,
2650 p_map_dtls_tbl IN maploc_rec_tbl_type
2651 ) RETURN VARCHAR2 IS
2652 l_map_dtls_tbl maploc_rec_tbl_type;
2653 l_old_map_status varchar2(1);
2654 l_geography_type varchar2(30);
2655 l_retain varchar2(1);
2656 i number;
2657 j number;
2658
2659 cursor get_old_map_status is
2660 select map_status
2661 from HZ_GEO_NAME_REFERENCE_LOG
2662 where location_id = p_location_id
2663 and map_status = 'S';
2664
2665 cursor get_gnr_superloc_rec is
2666 select geography_type
2667 from HZ_GEO_NAME_REFERENCES
2668 where location_id = p_location_id
2669 and geography_id in (-99,-98);
2670
2671 BEGIN
2672 l_retain := 'N';
2673 l_old_map_status := 'E';
2674
2675 OPEN get_old_map_status;
2676 FETCH get_old_map_status INTO l_old_map_status;
2677 IF get_old_map_status%NOTFOUND THEN
2678 l_old_map_status := 'E';
2679 END IF;
2680 CLOSE get_old_map_status;
2681
2682 -- If old status is Success and new is Error,
2683 -- Check for nay record in gnr table is a Super Loc
2684 -- If yes, retain the old one
2685 if l_old_map_status = 'S' and p_map_status = 'E' then
2686
2687 OPEN get_gnr_superloc_rec;
2688 LOOP
2689 FETCH get_gnr_superloc_rec INTO l_geography_type;
2690 EXIT WHEN get_gnr_superloc_rec%NOTFOUND;
2691 IF p_map_dtls_tbl.COUNT > 0 THEN
2692 j := l_map_dtls_tbl.FIRST;
2693 l_retain := 'Y';
2694 LOOP
2695 IF p_map_dtls_tbl(j).GEOGRAPHY_TYPE = l_geography_type THEN
2696 if ( nvl(p_map_dtls_tbl(j).GEOGRAPHY_CODE,'NOVALUE') = 'MISSING' OR
2697 nvl(p_map_dtls_tbl(j).GEOGRAPHY_CODE,'NOVALUE') = 'UNKNOWN' ) THEN
2698 l_retain := 'N';
2699 EXIT;
2700 end if;
2701 END IF;
2702 EXIT WHEN j = p_map_dtls_tbl.LAST;
2703 j := p_map_dtls_tbl.NEXT(j);
2704 END LOOP;
2705 IF l_retain = 'Y' THEN
2706 EXIT;
2707 END IF;
2708 END IF;
2709 END LOOP;
2710 CLOSE get_gnr_superloc_rec;
2711 else
2712 l_retain := 'N';
2713 end if;
2714
2715 RETURN l_retain;
2716 END retain_gnr_yn;
2717
2718 BEGIN
2719
2720 -- Initialize variables (perf improvement bug 5130993)
2721 l_last_updated_by := hz_utility_v2pub.last_updated_by;
2722 l_creation_date := hz_utility_v2pub.creation_date;
2723 l_created_by := hz_utility_v2pub.created_by;
2724 l_last_update_date := hz_utility_v2pub.last_update_date;
2725 l_last_update_login := hz_utility_v2pub.last_update_login;
2726 l_program_id := hz_utility_v2pub.program_id;
2727 l_conc_login_id := fnd_global.conc_login_id;
2728 l_program_application_id := hz_utility_v2pub.program_application_id;
2729 l_request_id := NVL(hz_utility_v2pub.request_id, -1);
2730 l_api_purpose := HZ_GNR_PKG.G_API_PURPOSE;
2731
2732 x_status := FND_API.g_ret_sts_success;
2733
2734 -- retain_gnr_yn will tell whether we have retain old GNR records or not.
2735 -- If l_retain_flag = 'Y' then exit without ceate/update of GNR and hz_location
2736
2737 -- Check if call is for R12UPGRADE then we do not want to do retain_gnr_check at all
2738 -- This Global variable is set in HZ_GNR_PKG during making call to create GNR for R12UPGRADE
2739 IF ( NVL(l_api_purpose,'xxya') = 'R12UPGRADE') THEN
2740 l_retain_flag := 'N';
2741 ELSE
2742 l_retain_flag := retain_gnr_yn(p_location_id, p_map_status, p_map_dtls_tbl);
2743 END IF;
2744
2745 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2746 hz_utility_v2pub.debug
2747 (p_message => 'Retain GNR Flag value l_retain_flag='||l_retain_flag,
2748 p_prefix => l_debug_prefix,
2749 p_msg_level => fnd_log.level_statement,
2750 p_module_prefix => l_module_prefix,
2751 p_module => l_module
2752 );
2753 END IF;
2754
2755 IF l_retain_flag = 'N' THEN -- Then only do the processing of GNR, otherwise keep old value
2756
2757 l_map_dtls_tbl := p_map_dtls_tbl;
2758
2759 BEGIN
2760
2761 -- Bug 5929771 : Check unique value existence before inserting to avoid
2762 -- expensive DUP_VAL_ON_INDEX exception (Nishant 16-APR-2007)
2763 OPEN c_check_gnr_log_exist(p_location_id,p_location_table_name,p_usage_code);
2764 FETCH c_check_gnr_log_exist INTO l_gnr_log_exist;
2765 l_gnr_log_exist := NVL(l_gnr_log_exist,'N');
2766 CLOSE c_check_gnr_log_exist;
2767
2768 IF (l_gnr_log_exist <> 'Y') THEN
2769
2770 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2771 hz_utility_v2pub.debug
2772 (p_message => 'Before inserting record into hz_geo_name_reference_log with map status '||p_map_status,
2773 p_prefix => l_debug_prefix,
2774 p_msg_level => fnd_log.level_statement,
2775 p_module_prefix => l_module_prefix,
2776 p_module => l_module
2777 );
2778 END IF;
2779
2780 INSERT INTO hz_geo_name_reference_log
2781 (location_id, location_table_name,usage_code,
2782 message_text,
2783 object_version_number, map_status,
2784 last_updated_by, creation_date,
2785 created_by, last_update_date,
2786 last_update_login, program_id,
2787 program_login_id,program_application_id,request_id)
2788 VALUES
2789 (p_location_id, p_location_table_name, p_usage_code, NULL, 1, p_map_status,
2790 l_last_updated_by, l_creation_date,
2791 l_created_by, l_last_update_date,
2792 l_last_update_login, l_program_id,
2793 l_conc_login_id, l_program_application_id, l_request_id);
2794
2795 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2796 hz_utility_v2pub.debug
2797 (p_message => 'After inserting record into hz_geo_name_reference_log ',
2798 p_prefix => l_debug_prefix,
2799 p_msg_level => fnd_log.level_statement,
2800 p_module_prefix => l_module_prefix,
2801 p_module => l_module
2802 );
2803 END IF;
2804
2805 ELSE -- GNR Log already exists, we will update it
2806
2807 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2808 hz_utility_v2pub.debug
2809 (p_message => 'Before updating record into hz_geo_name_reference_log with map status '||p_map_status,
2810 p_prefix => l_debug_prefix,
2811 p_msg_level => fnd_log.level_statement,
2812 p_module_prefix => l_module_prefix,
2813 p_module => l_module
2814 );
2815 END IF;
2816
2817 UPDATE hz_geo_name_reference_log
2818 SET map_status = p_map_status,
2819 object_version_number = object_version_number + 1,
2820 last_updated_by = l_last_updated_by,
2821 last_update_date = l_last_update_date,
2822 last_update_login = l_last_update_login
2823 WHERE location_id = p_location_id
2824 AND location_table_name = p_location_table_name
2825 AND usage_code = p_usage_code;
2826
2827 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2828 hz_utility_v2pub.debug
2829 (p_message => 'After updating record into hz_geo_name_reference_log ',
2830 p_prefix => l_debug_prefix,
2831 p_msg_level => fnd_log.level_statement,
2832 p_module_prefix => l_module_prefix,
2833 p_module => l_module
2834 );
2835 END IF;
2836
2837 -- delete the location id and table name combination from hz_geo_name_references
2838 -- This call is needed only if data exists in hz_geo_name_references table
2839 -- blind delete is bad for performance
2840 IF ( nvl(l_api_purpose,'xxya') <> 'R12UPGRADE') THEN
2841 delGNR(p_location_id,p_location_table_name,x_status);
2842 l_gnr_deleted := 'Y'; -- set it to 'Y' because we just deleted it. Later we can avoid again doing this check
2843 END IF;
2844
2845 END IF; -- end of check if l_gnr_log_exist
2846
2847 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- should not hit this error anymore (bug 5929771)
2848 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2849 hz_utility_v2pub.debug
2850 (p_message => 'EXCEPTION DUP_VAL_ON_INDEX during GNR Log insert/update for '||
2851 'Location Id:'||p_location_id||',usage_code:'||p_usage_code||
2852 ' -'||SUBSTR(SQLERRM,1,100),
2853 p_prefix => l_debug_prefix,
2854 p_msg_level => fnd_log.level_statement,
2855 p_module_prefix => l_module_prefix,
2856 p_module => l_module
2857 );
2858 END IF;
2859 END; -- END of GNR Log Insert BEGIN Stmt
2860
2861 -- Now Insert data in hz_geo_name_references table (Child table)
2862 IF p_map_dtls_tbl.COUNT > 0 THEN
2863
2864 i := p_map_dtls_tbl.FIRST;
2865
2866 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2867 hz_utility_v2pub.debug
2868 (p_message => 'Before inserting records into hz_geo_name_references ',
2869 p_prefix => l_debug_prefix,
2870 p_msg_level => fnd_log.level_statement,
2871 p_module_prefix => l_module_prefix,
2872 p_module => l_module
2873 );
2874 END IF;
2875
2876 LOOP
2877 -- intialize l_gnr_exist
2878 l_gnr_exist := NULL;
2879
2880 IF p_map_dtls_tbl(i).geography_id IS NOT NULL THEN
2881
2882 IF p_map_dtls_tbl(i).geography_code = 'MISSING' THEN
2883 l_map_dtls_tbl(i).geography_id := -99;
2884 l_map_dtls_tbl(i).loc_compval := NULL;
2885 ELSIF p_map_dtls_tbl(i).geography_code = 'UNKNOWN' THEN
2886 l_map_dtls_tbl(i).geography_id := -98;
2887 l_map_dtls_tbl(i).loc_compval := NULL;
2888 END IF;
2889
2890 BEGIN
2891 -- Bug 5929771 : Check unique value existence before inserting to avoid
2892 -- expensive DUP_VAL_ON_INDEX exception (Nishant 16-APR-2007)
2893 -- If it was existing before, we would have deleted it when we checked
2894 -- existence in GNR Log table (above).
2895 -- if data already deleted above then no need to perform existence check here.
2896 IF (l_gnr_deleted = 'Y' ) THEN
2897 l_gnr_exist := 'N';
2898 ELSE
2899 OPEN c_check_gnr_exist(p_location_id,p_location_table_name,p_map_dtls_tbl(i).geography_type);
2900 FETCH c_check_gnr_exist INTO l_gnr_exist;
2901 l_gnr_exist := NVL(l_gnr_exist,'N');
2902 CLOSE c_check_gnr_exist;
2903 END IF;
2904
2905 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2906 hz_utility_v2pub.debug
2907 (p_message => 'For Location Id:'||p_location_id||',geo type:'||
2908 p_map_dtls_tbl(i).geography_type||', GNR Exists :'||l_gnr_exist,
2909 p_prefix => l_debug_prefix,
2910 p_msg_level => fnd_log.level_statement,
2911 p_module_prefix => l_module_prefix,
2912 p_module => l_module
2913 );
2914 END IF;
2915
2916 IF (l_gnr_exist <> 'Y') THEN
2917 INSERT INTO hz_geo_name_references
2918 (location_id, geography_id, location_table_name,
2919 object_version_number, geography_type, last_updated_by,
2920 creation_date, created_by, last_update_date,
2921 last_update_login, program_id, program_login_id,
2922 program_application_id,request_id)
2923 VALUES
2924 (p_location_id, l_map_dtls_tbl(i).geography_id,p_location_table_name,
2925 1, p_map_dtls_tbl(i).geography_type, l_last_updated_by,
2926 l_creation_date, l_created_by,
2927 l_last_update_date, l_last_update_login,
2928 l_program_id, l_conc_login_id,
2929 l_program_application_id, l_request_id);
2930 END IF;
2931
2932 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- should not hit this error anymore (bug 5929771)
2933 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2934 hz_utility_v2pub.debug
2935 (p_message => 'EXCEPTION DUP_VAL_ON_INDEX during GNR insert/update for '||
2936 'Location Id:'||p_location_id||',geo_type:'||p_map_dtls_tbl(i).geography_type||
2937 ' -'||SUBSTR(SQLERRM,1,100),
2938 p_prefix => l_debug_prefix,
2939 p_msg_level => fnd_log.level_statement,
2940 p_module_prefix => l_module_prefix,
2941 p_module => l_module
2942 );
2943 END IF;
2944 END; -- End of BEGIN for inserting in GNR Log
2945
2946 ELSE -- geography_id = NULL
2947 EXIT;
2948 END IF; -- End of geography id is NOT NULL check
2949
2950 EXIT WHEN i = p_map_dtls_tbl.LAST;
2951 i := p_map_dtls_tbl.NEXT(i);
2952 END LOOP;
2953
2954 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2955 hz_utility_v2pub.debug
2956 (p_message => 'After inserting records into hz_geo_name_references ',
2957 p_prefix => l_debug_prefix,
2958 p_msg_level => fnd_log.level_statement,
2959 p_module_prefix => l_module_prefix,
2960 p_module => l_module
2961 );
2962 END IF;
2963
2964 END IF; -- END OF p_map_dtls_table.count > 0 check for inserting into GNR tables
2965
2966 -- Update Location not to be done for GNR calls, only for Online Validate API
2967 IF ((NVL(l_api_purpose,'xxya') NOT IN ('R12UPGRADE','GNR'))
2968 AND
2969 (p_location_table_name = 'HZ_LOCATIONS'
2970 AND
2971 update_loc_yn(p_loc_components_rec,l_map_dtls_tbl) = 'Y')) THEN
2972
2973 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2974 hz_utility_v2pub.debug
2975 (p_message => 'Before updating record into update_location ',
2976 p_prefix => l_debug_prefix,
2977 p_msg_level => fnd_log.level_statement,
2978 p_module_prefix => l_module_prefix,
2979 p_module => l_module
2980 );
2981 END IF;
2982
2983 update_location (p_location_id, p_loc_components_rec,p_lock_flag, l_map_dtls_tbl,x_status);
2984
2985 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2986 hz_utility_v2pub.debug
2987 (p_message => 'After updating record into update_location ',
2988 p_prefix => l_debug_prefix,
2989 p_msg_level => fnd_log.level_statement,
2990 p_module_prefix => l_module_prefix,
2991 p_module => l_module
2992 );
2993 END IF;
2994
2995 END IF; -- END of update_location
2996
2997 END IF; -- END of retain_gnr check
2998
2999 EXCEPTION WHEN OTHERS THEN
3000 x_status := fnd_api.g_ret_sts_unexp_error;
3001 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3002 hz_utility_v2pub.debug
3003 (p_message => 'EXCEPTION during create_gnr for '||
3004 'Location Id:'||p_location_id||',usage_code:'||p_usage_code||
3005 ' -'||SUBSTR(SQLERRM,1,100),
3006 p_prefix => l_debug_prefix,
3007 p_msg_level => fnd_log.level_exception,
3008 p_module_prefix => l_module_prefix,
3009 p_module => l_module
3010 );
3011 END IF;
3012 END create_gnr;
3013 --------------------------------------
3014 --------------------------------------
3015 FUNCTION check_GNR_For_Usage(
3016 p_location_id IN NUMBER,
3017 p_location_table_name IN VARCHAR2,
3018 p_usage_code IN VARCHAR2,
3019 p_mdu_tbl IN maploc_rec_tbl_type,
3020 x_status OUT NOCOPY varchar2
3021 ) RETURN BOOLEAN IS
3022
3023 CURSOR c_gnr(p_geography_type in varchar2) IS
3024 SELECT GEOGRAPHY_ID
3025 FROM HZ_GEO_NAME_REFERENCES
3026 WHERE LOCATION_ID = p_location_id
3027 AND GEOGRAPHY_TYPE = p_geography_type
3028 AND LOCATION_TABLE_NAME = p_location_table_name;
3029
3030 CURSOR c_gnr_log IS
3031 SELECT MAP_STATUS,USAGE_CODE
3032 FROm HZ_GEO_NAME_REFERENCE_LOG
3033 WHERE LOCATION_ID = p_location_id
3034 AND LOCATION_TABLE_NAME = p_location_table_name;
3035
3036 l_gnr_exists varchar2(1);
3037 l_success varchar2(1);
3038 l_usage_log_exists varchar2(1);
3039 l_geography_id number;
3040 i number;
3041
3042 l_last_updated_by NUMBER; -- hz_utility_v2pub.last_updated_by;
3043 l_creation_date DATE; -- hz_utility_v2pub.creation_date;
3044 l_created_by NUMBER; -- hz_utility_v2pub.created_by;
3045 l_last_update_date DATE; -- hz_utility_v2pub.last_update_date;
3046 l_last_update_login NUMBER; -- hz_utility_v2pub.last_update_login;
3047 l_program_id NUMBER; -- hz_utility_v2pub.program_id;
3048 l_conc_login_id NUMBER; -- fnd_global.conc_login_id;
3049 l_program_application_id NUMBER; --hz_utility_v2pub.program_application_id;
3050 l_request_id NUMBER; -- NVL(hz_utility_v2pub.request_id, -1);
3051
3052 BEGIN
3053
3054 -- Initialize variables (perf improvement bug 5130993)
3055 l_last_updated_by := hz_utility_v2pub.last_updated_by;
3056 l_creation_date := hz_utility_v2pub.creation_date;
3057 l_created_by := hz_utility_v2pub.created_by;
3058 l_last_update_date := hz_utility_v2pub.last_update_date;
3059 l_last_update_login := hz_utility_v2pub.last_update_login;
3060 l_program_id := hz_utility_v2pub.program_id;
3061 l_conc_login_id := fnd_global.conc_login_id;
3062 l_program_application_id := hz_utility_v2pub.program_application_id;
3063 l_request_id := NVL(hz_utility_v2pub.request_id, -1);
3064
3065 x_status := FND_API.g_ret_sts_success;
3066
3067 l_success := 'N';
3068 l_usage_log_exists := 'N';
3069 FOR l_c_gnr_log IN c_gnr_log LOOP
3070 l_gnr_exists := 'Y';
3071 IF l_c_gnr_log.USAGE_CODE = p_usage_code THEN
3072 IF l_c_gnr_log.MAP_STATUS = 'S' THEN
3073 RETURN TRUE;
3074 ELSE
3075 l_usage_log_exists := 'Y';
3076 END IF;
3077 END IF;
3078 END LOOP;
3079
3080 IF l_gnr_exists = 'Y' THEN
3081 l_success := 'Y';
3082 IF p_mdu_tbl.COUNT > 0 THEN
3083 i := p_mdu_tbl.FIRST;
3084 LOOP
3085
3086 OPEN c_gnr(p_mdu_tbl(i).GEOGRAPHY_TYPE);
3087 FETCH c_gnr INTO l_geography_id;
3088 IF c_gnr%NOTFOUND THEN
3089 l_success := 'N';
3090 END IF;
3091 CLOSE c_gnr;
3092 EXIT WHEN i = p_mdu_tbl.LAST;
3093 i := p_mdu_tbl.NEXT(i);
3094 END LOOP;
3095 END IF;
3096 END IF;
3097
3098 IF l_success = 'Y' THEN
3099 IF l_usage_log_exists = 'Y' THEN
3100 UPDATE hz_geo_name_reference_log
3101 SET map_status = 'S',
3102 object_version_number = object_version_number + 1,
3103 last_updated_by = l_last_updated_by,
3104 last_update_date = l_last_update_date,
3105 last_update_login = l_last_update_login
3106 WHERE location_id = p_location_id
3107 AND location_table_name = p_location_table_name
3108 AND usage_code = p_usage_code;
3109 ELSE
3110 INSERT INTO hz_geo_name_reference_log
3111 (location_id, location_table_name,usage_code,
3112 message_text,
3113 object_version_number, map_status,
3114 last_updated_by, creation_date,
3115 created_by, last_update_date,
3116 last_update_login, program_id,
3117 program_login_id,program_application_id,request_id)
3118 VALUES
3119 (p_location_id, p_location_table_name, p_usage_code, NULL, 1, 'S',
3120 l_last_updated_by,l_creation_date,
3121 l_created_by, l_last_update_date,
3122 l_last_update_login,l_program_id,
3123 l_conc_login_id, l_program_application_id,
3124 l_request_id);
3125 END IF;
3126 RETURN TRUE;
3127 ELSE
3128 RETURN FALSE;
3129 END IF;
3130
3131 RETURN FALSE;
3132 EXCEPTION WHEN OTHERS THEN
3133 x_status := fnd_api.g_ret_sts_unexp_error;
3134 RETURN FALSE;
3135 END check_GNR_For_Usage;
3136 --------------------------------------
3137 --------------------------------------
3138 FUNCTION fix_multiparent(
3139 p_geography_id IN NUMBER,
3140 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type
3141 ) RETURN BOOLEAN IS
3142 i number;
3143 l_parent_geography_id number;
3144 l_parent_geography_type varchar2(30);
3145 BEGIN
3146 IF x_map_dtls_tbl.COUNT > 0 THEN
3147 i:= x_map_dtls_tbl.FIRST;
3148 LOOP
3149 IF x_map_dtls_tbl(i).GEOGRAPHY_ID = p_geography_id THEN
3150 RETURN TRUE;
3151 END IF;
3152
3153 IF (x_map_dtls_tbl(i).GEOGRAPHY_ID IS NULL AND x_map_dtls_tbl(i).LOC_COMPVAL IS NULL) THEN
3154 -- This is a multiple parent case and user has not passed in a value to identify a unique record.
3155 -- MUST be done later. (two level missing, second level value passed. If we can identify the record, it will still fail)
3156 -- Need to think about a new logic.
3157 RETURN FALSE;
3158 END IF;
3159
3160 IF (x_map_dtls_tbl(i).GEOGRAPHY_ID IS NULL AND x_map_dtls_tbl(i).LOC_COMPVAL IS NOT NULL) THEN
3161 BEGIN
3162 SELECT g.GEOGRAPHY_ID
3163 INTO x_map_dtls_tbl(i).GEOGRAPHY_ID
3164 FROM HZ_GEOGRAPHIES g,HZ_HIERARCHY_NODES hn
3165 WHERE g.GEOGRAPHY_ID = hn.CHILD_ID
3166 AND g.GEOGRAPHY_TYPE = hn.CHILD_OBJECT_TYPE
3167 AND hn.CHILD_TABLE_NAME = 'HZ_GEOGRAPHIES'
3168 AND hn.HIERARCHY_TYPE = 'MASTER_REF'
3169 AND hn.PARENT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3170 AND hn.PARENT_ID = l_parent_geography_id
3171 AND hn.PARENT_OBJECT_TYPE = l_parent_geography_type
3172 AND SYSDATE between hn.EFFECTIVE_START_DATE AND hn.EFFECTIVE_END_DATE
3173 AND SYSDATE BETWEEN g.START_DATE AND g.END_DATE
3174 AND EXISTS (SELECT NULL
3175 FROM HZ_GEOGRAPHY_IDENTIFIERS gi
3176 WHERE g.GEOGRAPHY_ID = gi.GEOGRAPHY_ID
3177 AND gi.GEOGRAPHY_TYPE = g.GEOGRAPHY_TYPE
3178 AND gi.GEOGRAPHY_USE = 'MASTER_REF'
3179 AND upper(gi.IDENTIFIER_VALUE) = upper(x_map_dtls_tbl(i).LOC_COMPVAL));
3180 EXCEPTION WHEN OTHERS THEN
3181 RETURN FALSE;
3182 END;
3183 END IF;
3184
3185 l_parent_geography_id := x_map_dtls_tbl(i).GEOGRAPHY_ID;
3186 l_parent_geography_type := x_map_dtls_tbl(i).GEOGRAPHY_TYPE;
3187 EXIT WHEN i = x_map_dtls_tbl.LAST;
3188 i := x_map_dtls_tbl.NEXT(i);
3189 END LOOP;
3190 END IF;
3191 RETURN TRUE;
3192 END fix_multiparent;
3193 --------------------------------------
3194 -------------------------------------
3195 PROCEDURE fix_no_match(
3196 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type,
3197 x_status OUT NOCOPY VARCHAR2
3198 ) IS
3199 l_map_dtls_tbl maploc_rec_tbl_type;
3200 l_map_dtls_tbl_null maploc_rec_tbl_type;
3201
3202 PROCEDURE prcess_no_match(
3203 p_iteration IN NUMBER,
3204 p_country_geo_id IN NUMBER,
3205 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type,
3206 x_status OUT NOCOPY VARCHAR2
3207 ) IS
3208
3209 i number;
3210 l_child_geography_id number;
3211 l_geography_id number;
3212 l_geography_name varchar2(360);
3213 l_child_geography_type varchar2(30);
3214 l_country_geo_type varchar2(30);
3215
3216 BEGIN
3217 x_status := FND_API.g_ret_sts_success;
3218
3219 --hk_debugl('Iteration : '||to_char(p_iteration));
3220 l_country_geo_type := 'COUNTRY';
3221
3222 IF p_iteration = 1 THEN
3223 l_child_geography_id := NULL;
3224 l_child_geography_type := NULL;
3225 ELSE
3226 l_child_geography_id := x_map_dtls_tbl(p_iteration-1).GEOGRAPHY_ID;
3227 l_child_geography_type := x_map_dtls_tbl(p_iteration-1).GEOGRAPHY_TYPE;
3228 END IF;
3229
3230 IF x_map_dtls_tbl(p_iteration).LOC_COMPVAL IS NULL THEN
3231 IF l_child_geography_id IS NOT NULL THEN
3232 BEGIN
3233 SELECT g.GEOGRAPHY_ID,g.GEOGRAPHY_NAME
3234 INTO l_geography_id,l_geography_name
3235 FROM HZ_GEOGRAPHIES g,HZ_HIERARCHY_NODES hn
3236 WHERE g.GEOGRAPHY_ID = hn.PARENT_ID
3237 AND g.GEOGRAPHY_TYPE = hn.PARENT_OBJECT_TYPE
3238 AND hn.PARENT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3239 AND hn.HIERARCHY_TYPE = 'MASTER_REF'
3240 AND hn.level_number = 1
3241 AND hn.CHILD_TABLE_NAME = 'HZ_GEOGRAPHIES'
3242 AND hn.CHILD_ID = l_child_geography_id
3243 AND hn.CHILD_OBJECT_TYPE = l_child_geography_type
3244 AND SYSDATE between hn.EFFECTIVE_START_DATE AND hn.EFFECTIVE_END_DATE
3245 AND SYSDATE BETWEEN g.START_DATE AND g.END_DATE;
3246
3247 x_map_dtls_tbl(p_iteration).GEOGRAPHY_ID := l_geography_id;
3248 x_map_dtls_tbl(p_iteration).LOC_COMPVAL := l_geography_name;
3249
3250 --hk_debugl('Case 1');
3251 EXCEPTION WHEN OTHERS THEN
3252 --hk_debugl('Case 1 Exception');
3253 --hk_debugl('Child Geo : '||to_char(l_child_geography_id));
3254 --hk_debugl('Child Geo Type : '||l_child_geography_type);
3255 NULL;
3256 END;
3257 END IF;
3258 ELSE -- Location component value is not null
3259 IF l_child_geography_id IS NOT NULL THEN
3260 BEGIN
3261 SELECT g.GEOGRAPHY_ID,g.GEOGRAPHY_NAME
3262 INTO l_geography_id,l_geography_name
3263 FROM HZ_GEOGRAPHIES g,HZ_HIERARCHY_NODES hn
3264 -- Nishant
3265 --WHERE g.GEOGRAPHY_ID = hn.PARENT_ID
3266 WHERE g.GEOGRAPHY_ID = hn.PARENT_ID+0
3267 AND g.GEOGRAPHY_TYPE = hn.PARENT_OBJECT_TYPE
3268 AND hn.PARENT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3269 AND hn.HIERARCHY_TYPE = 'MASTER_REF'
3270 AND hn.level_number = 1
3271 AND hn.CHILD_TABLE_NAME = 'HZ_GEOGRAPHIES'
3272 AND hn.CHILD_ID = l_child_geography_id
3273 AND hn.CHILD_OBJECT_TYPE = l_child_geography_type
3274 AND SYSDATE BETWEEN hn.EFFECTIVE_START_DATE AND hn.EFFECTIVE_END_DATE
3275 AND SYSDATE BETWEEN g.START_DATE AND g.END_DATE
3276 AND EXISTS (SELECT NULL
3277 FROM HZ_GEOGRAPHY_IDENTIFIERS gi
3278 WHERE g.GEOGRAPHY_ID = gi.GEOGRAPHY_ID
3279 AND gi.GEOGRAPHY_TYPE = g.GEOGRAPHY_TYPE
3280 AND gi.geography_type = x_map_dtls_tbl(p_iteration).GEOGRAPHY_TYPE
3281 AND gi.GEOGRAPHY_USE = 'MASTER_REF'
3282 AND upper(gi.IDENTIFIER_VALUE) = upper(x_map_dtls_tbl(p_iteration).LOC_COMPVAL));
3283
3284 x_map_dtls_tbl(p_iteration).GEOGRAPHY_ID := l_geography_id;
3285 x_map_dtls_tbl(p_iteration).LOC_COMPVAL := l_geography_name;
3286
3287 --hk_debugl('Case 2');
3288 EXCEPTION WHEN OTHERS THEN
3289 --hk_debugl('Case 2 Exception');
3290 --hk_debugl('Child Geo : '||to_char(l_child_geography_id));
3291 --hk_debugl('Child Geo Type : '||l_child_geography_type);
3292 --hk_debugl('Geography Type : '||x_map_dtls_tbl(p_iteration).GEOGRAPHY_TYPE);
3293 --hk_debugl('Loc Comp Val : '||x_map_dtls_tbl(p_iteration).LOC_COMPVAL);
3294 NULL;
3295 END;
3296 ELSE -- Loc compoent value is not null and child_geo_id is null
3297 BEGIN
3298 SELECT g.GEOGRAPHY_ID,g.GEOGRAPHY_NAME
3299 INTO l_geography_id,l_geography_name
3300 FROM HZ_GEOGRAPHIES g,HZ_HIERARCHY_NODES hn
3301 WHERE g.GEOGRAPHY_ID = hn.CHILD_ID
3302 AND g.GEOGRAPHY_TYPE = hn.CHILD_OBJECT_TYPE
3303 AND hn.CHILD_TABLE_NAME = 'HZ_GEOGRAPHIES'
3304 AND hn.HIERARCHY_TYPE = 'MASTER_REF'
3305 AND hn.level_number = x_map_dtls_tbl(p_iteration).LOC_SEQ_NUM - 1
3306 AND hn.PARENT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3307 AND hn.PARENT_ID+0 = p_country_geo_id
3308 AND hn.PARENT_OBJECT_TYPE = l_country_geo_type
3309 AND SYSDATE BETWEEN hn.EFFECTIVE_START_DATE AND hn.EFFECTIVE_END_DATE
3310 AND SYSDATE BETWEEN g.START_DATE AND g.END_DATE
3311 AND EXISTS (SELECT NULL
3312 FROM HZ_GEOGRAPHY_IDENTIFIERS gi
3313 WHERE g.GEOGRAPHY_ID = gi.GEOGRAPHY_ID
3314 AND gi.GEOGRAPHY_TYPE = g.GEOGRAPHY_TYPE
3315 AND gi.GEOGRAPHY_USE = 'MASTER_REF'
3316 AND gi.geography_type = x_map_dtls_tbl(p_iteration).GEOGRAPHY_TYPE
3317 AND upper(gi.IDENTIFIER_VALUE) = upper(x_map_dtls_tbl(p_iteration).LOC_COMPVAL));
3318
3319 x_map_dtls_tbl(p_iteration).GEOGRAPHY_ID := l_geography_id;
3320 x_map_dtls_tbl(p_iteration).LOC_COMPVAL := l_geography_name;
3321
3322 --hk_debugl('Case 3');
3323 EXCEPTION WHEN OTHERS THEN
3324 --hk_debugl('Case 3 Exception');
3325 --hk_debugl('Country Geo : '||to_char(p_country_geo_id));
3326 --hk_debugl('Geography Type : '||x_map_dtls_tbl(p_iteration).GEOGRAPHY_TYPE);
3327 --hk_debugl('Loc Comp Val : '||x_map_dtls_tbl(p_iteration).LOC_COMPVAL);
3328 NULL;
3329 END;
3330 END IF;
3331 END IF;
3332
3333 IF p_iteration < x_map_dtls_tbl.COUNT - 1 THEN
3334 prcess_no_match(p_iteration+1,p_country_geo_id,x_map_dtls_tbl,x_status);
3335 END IF;
3336
3337 END prcess_no_match;
3338 BEGIN
3339 x_status := FND_API.g_ret_sts_success;
3340 IF x_map_dtls_tbl.COUNT > 0 THEN
3341 reverse_tbl(x_map_dtls_tbl,l_map_dtls_tbl);
3342 END IF;
3343
3344 prcess_no_match(1,x_map_dtls_tbl(1).GEOGRAPHY_ID,l_map_dtls_tbl,x_status);
3345
3346 x_map_dtls_tbl := l_map_dtls_tbl_null;
3347 reverse_tbl(l_map_dtls_tbl,x_map_dtls_tbl);
3348 END fix_no_match;
3349 --------------------------------------
3350 --------------------------------------
3351 PROCEDURE getMinValStatus(
3352 p_mdu_tbl IN maploc_rec_tbl_type,
3353 x_status IN OUT NOCOPY VARCHAR2
3354 ) IS
3355 i number;
3356 BEGIN
3357 -- If there is null component value found this API will return status "E'. If all components are not null
3358 -- Then success status will be returned. else it will return the status that came in.
3359 IF p_mdu_tbl.COUNT > 0 THEN
3360 i := p_mdu_tbl.FIRST;
3361 LOOP
3362 If p_mdu_tbl(i).LOC_COMPVAL IS NULL THEN
3363 x_status := FND_API.g_ret_sts_error;
3364 RETURN;
3365 END IF;
3366 EXIT WHEN i = p_mdu_tbl.LAST;
3367 i := p_mdu_tbl.NEXT(i);
3368 END LOOP;
3369 x_status := FND_API.g_ret_sts_success;
3370
3371 END IF;
3372 END getMinValStatus;
3373 --------------------------------------
3374 --------------------------------------
3375 FUNCTION fix_child(
3376 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type
3377 ) RETURN BOOLEAN IS
3378 i number;
3379 l_parent_geography_id number;
3380 l_parent_geography_type varchar2(30);
3381 BEGIN
3382 IF x_map_dtls_tbl.COUNT > 0 THEN
3383 i:= x_map_dtls_tbl.FIRST;
3384 LOOP
3385 IF (x_map_dtls_tbl(i).GEOGRAPHY_ID IS NULL AND x_map_dtls_tbl(i).LOC_COMPVAL IS NULL) THEN
3386 BEGIN
3387 SELECT hn.CHILD_ID
3388 INTO x_map_dtls_tbl(i).GEOGRAPHY_ID
3389 FROM HZ_HIERARCHY_NODES hn
3390 WHERE hn.CHILD_OBJECT_TYPE = x_map_dtls_tbl(i).GEOGRAPHY_TYPE
3391 AND hn.CHILD_TABLE_NAME = 'HZ_GEOGRAPHIES'
3392 AND hn.HIERARCHY_TYPE = 'MASTER_REF'
3393 AND hn.PARENT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3394 AND hn.PARENT_ID = l_parent_geography_id
3395 AND hn.PARENT_OBJECT_TYPE = l_parent_geography_type
3396 AND SYSDATE BETWEEN hn.EFFECTIVE_START_DATE AND hn.EFFECTIVE_END_DATE;
3397 EXCEPTION WHEN OTHERS THEN
3398 RETURN FALSE;
3399 END;
3400 END IF;
3401
3402 l_parent_geography_id := x_map_dtls_tbl(i).GEOGRAPHY_ID;
3403 l_parent_geography_type := x_map_dtls_tbl(i).GEOGRAPHY_TYPE;
3404 EXIT WHEN i = x_map_dtls_tbl.LAST;
3405 i := x_map_dtls_tbl.NEXT(i);
3406 END LOOP;
3407 END IF;
3408 RETURN TRUE;
3409 END fix_child;
3410 --------------------------------------
3411 --------------------------------------
3412 FUNCTION getLocCompCount(
3413 p_map_dtls_tbl IN maploc_rec_tbl_type) RETURN NUMBER IS
3414 i number;
3415 j number;
3416 BEGIN
3417 j := 0;
3418 IF p_map_dtls_tbl.COUNT > 1 THEN
3419 i:= p_map_dtls_tbl.FIRST;
3420 i:= p_map_dtls_tbl.NEXT(i);
3421 LOOP
3422 IF p_map_dtls_tbl(i).LOC_COMPVAL IS NOT NULL THEN
3423 j := j + 1;
3424 END IF;
3425 EXIT WHEN i = p_map_dtls_tbl.LAST;
3426 i := p_map_dtls_tbl.NEXT(i);
3427 END LOOP;
3428 END IF;
3429 RETURN j;
3430 END getLocCompCount;
3431 --------------------------------------
3432 --------------------------------------
3433 PROCEDURE getLocCompValues(
3434 P_loc_table IN VARCHAR2,
3435 p_loc_components_rec IN loc_components_rec_type,
3436 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type,
3437 x_status OUT NOCOPY VARCHAR2
3438 ) IS
3439
3440 TYPE NameValueRecType IS RECORD
3441 (Name varchar2(30),
3442 Value varchar2(150));
3443 TYPE NameValueTblType IS TABLE OF NameValueRecType
3444 INDEX BY BINARY_INTEGER;
3445
3446 l_name_value NameValueTblType;
3447 i number :=0;
3448
3449 FUNCTION getValue(p_name IN VARCHAR2) RETURN VARCHAR2 IS
3450 i number :=0;
3451 BEGIN
3452 IF l_name_value.COUNT > 0 THEN
3453 i := l_name_value.FIRST;
3454 LOOP
3455 IF l_name_value(i).name = p_name THEN
3456 RETURN l_name_value(i).value;
3457 END IF;
3458 EXIT WHEN i = l_name_value.LAST;
3459 i := l_name_value.NEXT(i);
3460 END LOOP;
3461 END IF;
3462 RETURN NULL;
3463 END;
3464
3465 BEGIN
3466
3467 x_status := FND_API.g_ret_sts_success;
3468 l_name_value(2).name := 'ADDRESS_STYLE';
3469 l_name_value(2).value := p_loc_components_rec.ADDRESS_STYLE;
3470 l_name_value(3).name := 'COUNTRY';
3471 l_name_value(3).value := p_loc_components_rec.COUNTRY;
3472 l_name_value(4).name := 'CITY';
3473 l_name_value(4).value := p_loc_components_rec.CITY;
3474 l_name_value(5).name := 'POSTAL_CODE';
3475 l_name_value(5).value := p_loc_components_rec.POSTAL_CODE;
3476 l_name_value(6).name := 'STATE';
3477 l_name_value(6).value := p_loc_components_rec.STATE;
3478 l_name_value(7).name := 'PROVINCE';
3479 l_name_value(7).value := p_loc_components_rec.PROVINCE;
3480 l_name_value(8).name := 'COUNTY';
3481 l_name_value(8).value := p_loc_components_rec.COUNTY;
3482 l_name_value(9).name := 'VALIDATE_COUNTRY_AGAINST';
3483 l_name_value(9).value := p_loc_components_rec.VALIDATE_COUNTRY_AGAINST;
3484 l_name_value(10).name := 'VALIDATE_STATE_AGAINST';
3485 l_name_value(10).value := p_loc_components_rec.VALIDATE_STATE_AGAINST;
3486 l_name_value(11).name := 'VALIDATE_PROVINCE_AGAINST';
3487 l_name_value(11).value := p_loc_components_rec.VALIDATE_PROVINCE_AGAINST;
3488 l_name_value(12).name := 'POSTAL_PLUS4_CODE';
3489 l_name_value(12).value := p_loc_components_rec.POSTAL_PLUS4_CODE;
3490 l_name_value(13).name := 'ATTRIBUTE1';
3491 l_name_value(13).value := p_loc_components_rec.ATTRIBUTE1;
3492 l_name_value(14).name := 'ATTRIBUTE2';
3493 l_name_value(14).value := p_loc_components_rec.ATTRIBUTE2;
3494 l_name_value(15).name := 'ATTRIBUTE3';
3495 l_name_value(15).value := p_loc_components_rec.ATTRIBUTE3;
3496 l_name_value(16).name := 'ATTRIBUTE4';
3497 l_name_value(16).value := p_loc_components_rec.ATTRIBUTE4;
3498 l_name_value(17).name := 'ATTRIBUTE5';
3499 l_name_value(17).value := p_loc_components_rec.ATTRIBUTE5;
3500 l_name_value(18).name := 'ATTRIBUTE6';
3501 l_name_value(18).value := p_loc_components_rec.ATTRIBUTE6;
3502 l_name_value(19).name := 'ATTRIBUTE7';
3503 l_name_value(19).value := p_loc_components_rec.ATTRIBUTE7;
3504 l_name_value(20).name := 'ATTRIBUTE8';
3505 l_name_value(20).value := p_loc_components_rec.ATTRIBUTE8;
3506 l_name_value(21).name := 'ATTRIBUTE9';
3507 l_name_value(21).value := p_loc_components_rec.ATTRIBUTE9;
3508 l_name_value(22).name := 'ATTRIBUTE10';
3509 l_name_value(22).value := p_loc_components_rec.ATTRIBUTE10;
3510
3511 IF x_map_dtls_tbl.COUNT > 0 THEN
3512 i := x_map_dtls_tbl.FIRST;
3513 LOOP
3514 x_map_dtls_tbl(i).LOC_COMPVAL := getValue(x_map_dtls_tbl(i).LOC_COMPONENT);
3515 EXIT WHEN i = x_map_dtls_tbl.LAST;
3516 i := x_map_dtls_tbl.NEXT(i);
3517 END LOOP;
3518 END IF;
3519 END;
3520
3521 --------------------------------------
3522 --------------------------------------
3523 --ER#7240974
3524 /**
3525 Function : postal_code_to_validate
3526
3527 DESCRIPTION :
3528 Based on profile(HZ_VAL_FIRST_5_DIGIT_US_ZIP) value,
3529 it will return the postal code that needs to be validated.
3530
3531 ARGUMENTS :
3532 IN p_country_code VARCHAR2
3533 IN p_postal_code VARCHAR2
3534
3535 RETURNS : VARCHAR2
3536 postal code that needs to be validated
3537
3538
3539 MODIFICATION HISTORY:
3540 17-DEC-2008 Sudhir Gokavarapu Created
3541
3542 **/
3543
3544 FUNCTION postal_code_to_validate(
3545 p_country_code IN VARCHAR2,
3546 p_postal_code IN VARCHAR2
3547 ) RETURN VARCHAR2 IS
3548 BEGIN
3549
3550 -- Debug info.
3551 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3552 hz_utility_v2pub.debug(p_message=>'In postal_code_to_validate Function ',
3553 p_prefix =>'INFO:',
3554 p_msg_level=>fnd_log.level_statement);
3555 END IF;
3556
3557 -- Debug info.
3558 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3559 hz_utility_v2pub.debug(p_message=>'Country Code : ' || p_country_code||' Postal Code : '||p_postal_code,
3560 p_prefix =>'INFO:',
3561 p_msg_level=>fnd_log.level_statement);
3562 END IF;
3563
3564 -- Debug info.
3565 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3566 hz_utility_v2pub.debug(p_message=>'Profile HZ_VAL_FIRST_5_DIGIT_US_ZIP Value : '||fnd_profile.value('HZ_VAL_FIRST_5_DIGIT_US_ZIP'),
3567 p_prefix =>'INFO:',
3568 p_msg_level=>fnd_log.level_statement);
3569 END IF;
3570
3571 IF p_postal_code IS NULL THEN
3572 RETURN NULL;
3573 END IF;
3574
3575 IF p_country_code = 'US' THEN
3576 IF fnd_profile.value('HZ_VAL_FIRST_5_DIGIT_US_ZIP') = 'N' THEN
3577 -- Debug info.
3578 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3579 hz_utility_v2pub.debug(p_message=>'Returned Postal Code : '||p_postal_code,
3580 p_prefix =>'INFO:',
3581 p_msg_level=>fnd_log.level_statement);
3582 END IF;
3583
3584 RETURN p_postal_code;
3585 ELSE
3586 -- Debug info.
3587 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3588 hz_utility_v2pub.debug(p_message=>'Returned Postal Code : '||SUBSTR(p_postal_code,1,5),
3589 p_prefix =>'INFO:',
3590 p_msg_level=>fnd_log.level_statement);
3591 END IF;
3592
3593 RETURN SUBSTR(p_postal_code,1,5);
3594 END IF;
3595 ELSE
3596 -- Debug info.
3597 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3598 hz_utility_v2pub.debug(p_message=>'Returned Postal Code : '||p_postal_code,
3599 p_prefix =>'INFO:',
3600 p_msg_level=>fnd_log.level_statement);
3601 END IF;
3602
3603 RETURN p_postal_code;
3604 END IF;
3605 END;
3606
3607 /**
3608 * PROCEDURE conc_gen_pkg
3609 *
3610 * DESCRIPTION
3611 * Concurrent program for generating
3612 * gnr packages for all the existing Map Ids
3613 *
3614 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3615 *
3616 * IN:
3617 * OUT:
3618 * errbuf Buffer for error message.
3619 * retcode Return code.
3620 *
3621 * MODIFICATION HISTORY
3622 *
3623 * 02-MAY-2011 Sudhir Gokavarapu o Created.
3624 */
3625
3626 PROCEDURE conc_gen_pkg (
3627 errbuf OUT NOCOPY VARCHAR2,
3628 retcode OUT NOCOPY VARCHAR2
3629 ) IS
3630 CURSOR c_map IS
3631 SELECT map_id
3632 FROM hz_geo_struct_map;
3633
3634 CURSOR c_usage(l_map_id IN NUMBER) IS
3635 SELECT count(*)
3636 FROM Hz_address_usages usg, Hz_address_usage_dtls dtl
3637 WHERE usg.map_id = l_map_id
3638 AND usg.status_flag = 'A'
3639 AND dtl.usage_id = usg.usage_id;
3640
3641 l_count number;
3642 l_pkgname varchar2(30);
3643 l_status varchar2(30);
3644 l_debug_prefix VARCHAR2(30) := '';
3645
3646 BEGIN
3647
3648 -- Debug info.
3649 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3650 hz_utility_v2pub.debug(p_message=>'conc_gen_pkg (+)',
3651 p_prefix =>l_debug_prefix,
3652 p_msg_level=>fnd_log.level_statement);
3653 END IF;
3654 -- standard start of API savepoint
3655 SAVEPOINT conc_gen_pkg;
3656
3657 FOR l_c_map IN c_map LOOP
3658 l_count := 0;
3659 OPEN c_usage(l_c_map.map_id);
3660 FETCH c_usage INTO l_count;
3661 CLOSE c_usage;
3662 -- Debug info.
3663 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3664 hz_utility_v2pub.debug(p_message=>'Usage Count : '||l_count||' for map Id : '||l_c_map.map_id,
3665 p_prefix =>l_debug_prefix,
3666 p_msg_level=>fnd_log.level_statement);
3667 END IF;
3668 if l_count > 0 then
3669 l_pkgname := NULL;
3670 hz_gnr_gen_pkg.genpkg(l_c_map.map_id,l_pkgname,l_status);
3671 end if;
3672 END LOOP;
3673 retcode := 0;
3674 -- Debug info.
3675 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3676 hz_utility_v2pub.debug(p_message=>'conc_gen_pkg (-)',
3677 p_prefix =>l_debug_prefix,
3678 p_msg_level=>fnd_log.level_statement);
3679 END IF;
3680 exception when others then
3681 ROLLBACK to conc_gen_pkg;
3682 retcode := 2;
3683 errbuf := SQLERRM;
3684 END conc_gen_pkg;
3685
3686
3687 END hz_gnr_util_pkg;
[Home] [Help]