[Home] [Help]
PACKAGE BODY: APPS.HZ_GEO_UI_UTIL_PUB
Source
1 PACKAGE BODY HZ_GEO_UI_UTIL_PUB AS
2 /* $Header: ARHGEOUB.pls 120.10 2008/03/06 13:20:32 nshinde ship $ */
3 function check_dup_geo ( p_parent_id in NUMBER,
4 p_geo_name in VARCHAR2,
5 p_geo_code in VARCHAR2,
6 p_geo_type in VARCHAR2)
7 RETURN VARCHAR2 IS
8
9
10
11 l_return varchar2(10);
12
13 cursor c_dup( l_parent_id in number
14 , l_geo_type in varchar2
15 , l_geo_name in varchar2
16 , l_geo_code in varchar2)
17 is
18 select hr.child_id,
19 geo.geography_name
20 from hz_hierarchy_nodes hr
21 , hz_geographies geo
22 where hr.hierarchy_type = 'MASTER_REF'
23 and hr.parent_id = l_parent_id
24 and hr.parent_table_name = 'HZ_GEOGRAPHIES'
25 and hr.child_table_name = 'HZ_GEOGRAPHIES'
26 and hr.child_object_type = l_geo_type
27 and hr.status = 'A'
28 and hr.effective_end_date > sysdate
29 and geo.geography_id = hr.child_id
30 and nvl(geo.end_date , sysdate)
31 > sysdate
32 and geo.geography_type = hr.child_object_type
33 and (
34 UPPER(geo.geography_name) = UPPER(l_geo_name)
35 OR
36 UPPER(geo.geography_code) = UPPER(l_geo_code)
37 );
38
39
40 r_dup c_dup%rowtype;
41
42
43
44 BEGIN
45 l_return := 'N';
46
47 open c_dup(p_parent_id, p_geo_type, p_geo_name, p_geo_code);
48 fetch c_dup into r_dup;
49 if (c_dup%FOUND)
50 THEN
51 l_return := 'Y';
52 END IF;
53 close c_dup;
54
55 return l_return;
56
57 END check_dup_geo;
58
59 FUNCTION check_geo_tax_valid( p_map_id in NUMBER,
60 p_geo_type in VARCHAR2,
61 p_geo_tax in VARCHAR2)
62 RETURN VARCHAR2
63 IS
64
65 l_return varchar2(10);
66 cursor c_val_chk(l_map_id in number,
67 l_geo_type in varchar2,
68 l_geo_tax in varchar2)
69 is
70 select dtl.usage_dtl_id
71 from Hz_address_usages usg,
72 Hz_address_usage_dtls dtl
73 where usg.map_id = l_map_id
74 and usg.usage_code = l_geo_tax
75 and usg.status_flag = 'A'
76 and dtl.usage_id = usg.usage_id
77 and dtl.geography_type = l_geo_type;
78
79 l_dtl_id NUMBER;
80
81 BEGIN
82 l_return := 'N';
83
84 open c_val_chk( p_map_id,
85 p_geo_type,
86 p_geo_tax);
87 fetch c_val_chk into l_dtl_id;
88 if(c_val_chk%found)
89 then
90 l_return := 'Y';
91 end if;
92 close c_val_chk;
93
94 return l_return;
95
96 END check_geo_tax_valid;
97
98 PROCEDURE update_map_usages(p_map_id IN NUMBER,
99 p_tax_tbl IN HZ_GEO_UI_UTIL_PUB.tax_geo_tbl_type,
100 p_geo_tbl IN HZ_GEO_UI_UTIL_PUB.tax_geo_tbl_type,
101 p_init_msg_list IN VARCHAR2 ,
102 x_return_status OUT NOCOPY VARCHAR2,
103 x_msg_count OUT NOCOPY NUMBER,
104 x_msg_data OUT NOCOPY VARCHAR2,
105 x_show_gnr OUT NOCOPY VARCHAR2
106 )
107 IS
108
109 cursor c_loc_tbl(l_map_id in number)
110 is
111 select st.loc_tbl_name,
112 geo.geography_id
113 from Hz_geo_struct_map st,
114 hz_geographies geo
115 where map_id = l_map_id
116 AND st.country_code = geo.country_code
117 AND geo.geography_type = 'COUNTRY';
118
119 l_loc_tbl varchar2(2000);
120 l_geography_id number;
121 l_show_gnr varchar2(10);
122
123 cursor c_get_usage(l_map_id in number)
124 is
125 select usg.usage_id,
126 usg.map_id,
127 usg.usage_code
128 from hz_address_usages usg
129 where map_id = l_map_id
130 and status_flag = 'A';
131
132 r_get_usage c_get_usage%rowtype;
133
134 cursor c_get_dtls(l_usage_id in number,
135 l_geo_type in varchar2)
136 is
137 select usg.usage_dtl_id,
138 usg.geography_type
139 from hz_address_usage_dtls usg
140 where usg.usage_id = l_usage_id
141 and usg.geography_type = l_geo_type;
142
143 r_get_dtls c_get_dtls%rowtype;
144
145
146 TYPE val_rec_type IS RECORD (
147 usage_id NUMBER,
148 usage_dtl_id NUMBER,
149 geography_type VARCHAR2(360)
150 );
151
152 TYPE val_tbl_type IS TABLE OF val_rec_type
153 INDEX BY BINARY_INTEGER;
154
155 tax_tbl val_tbl_type;
156 geo_tbl val_tbl_type;
157
158 l_tax_usage_id number := 0;
159 l_geo_usage_id number := 0;
160 i number;
161 j number;
162 k number := 1;
163 l number := 0;
164
165 l_address_usages_rec HZ_ADDRESS_USAGES_PUB.address_usages_rec_type;
166 l_address_usage_dtls_tbl HZ_ADDRESS_USAGES_PUB.address_usage_dtls_tbl_type;
167 l_address_usage_dtls_del_tbl HZ_ADDRESS_USAGES_PUB.address_usage_dtls_tbl_type;
168
169 l_return_status VARCHAR2(10);
170 l_msg_data VARCHAR2(2000);
171 l_msg_count NUMBER;
172 l_usg_dtl_id NUMBER;
173
174 l_tax_id_ret NUMBER;
175 l_geo_id_ret NUMBER;
176
177 del_usg boolean := false;
178 del_all_usg boolean := true;
179
180 BEGIN
181 -- Initialize message list if p_init_msg_list is set to TRUE.
182 IF FND_API.to_Boolean(p_init_msg_list) THEN
183 FND_MSG_PUB.initialize;
184 END IF;
185
186 -- Initialize API return status to success.
187 x_return_status := FND_API.G_RET_STS_SUCCESS;
188
189 -- set gnr regenration message to N initially
190 x_show_gnr := 'N';
191 l_show_gnr := 'N';
192 IF(p_map_id > 0)
193 THEN
194
195 -- Fetch values for the usage header for both Tax and Geo if it exists
196 open c_get_usage(p_map_id);
197 fetch c_get_usage into r_get_usage;
198 while(c_get_usage%found)
199 loop
200 if(r_get_usage.usage_code = 'TAX')
201 then
202 l_tax_usage_id := r_get_usage.usage_id;
203 elsif( r_get_usage.usage_code = 'GEOGRAPHY')
204 then
205 l_geo_usage_id := r_get_usage.usage_id;
206 end if;
207 fetch c_get_usage into r_get_usage;
208 end loop;
209 close c_get_usage;
210
211
212 -- Fetch the loc_tbl_name
213 open c_loc_tbl(p_map_id);
214 fetch c_loc_tbl into l_loc_tbl, l_geography_id;
215 close c_loc_tbl;
216
217 -- check to see if GNR Results data exists
218 l_show_gnr := get_geo_ref(p_geography_id => l_geography_id,
219 p_loc_table_name => l_loc_tbl);
220
221 -- if tax table is not null and l_tax_usage_id is 0, then insert the tax record
222 if((p_tax_tbl.count > 0) AND ( l_tax_usage_id = 0))
223 then
224
225 -- insert new usage
226 l_address_usages_rec.map_id := p_map_id;
227 l_address_usages_rec.usage_code := 'TAX';
228 l_address_usages_rec.status_flag := 'A';
229 l_address_usages_rec.created_by_module := 'HZ_GEO_HIERARCHY';
230 l_address_usages_rec.application_id := 222;
231
232 l_address_usage_dtls_tbl.delete;
233
234 j := p_tax_tbl.first;
235 FOR i in p_tax_tbl.first..p_tax_tbl.last
236 LOOP
237 if(p_tax_tbl(i).tax_geo_valid = 'Y')
238 then
239 l_address_usage_dtls_tbl(j).geography_type := p_tax_tbl(i).geography_type;
240 l_address_usage_dtls_tbl(j).created_by_module := 'HZ_GEO_HIERARCHY';
241 l_address_usage_dtls_tbl(j).application_id := 222;
242 j := j+1;
243 end if;
244 END LOOP;
245 if(l_address_usage_dtls_tbl.count > 0)
246 then
247 hz_address_usages_pub.create_address_usages(p_address_usages_rec => l_address_usages_rec,
248 p_address_usage_dtls_tbl => l_address_usage_dtls_tbl,
249 p_init_msg_list => FND_API.G_FALSE,
250 x_usage_id => l_tax_id_ret,
251 x_return_status => l_return_status,
252 x_msg_count => l_msg_count,
253 x_msg_data => l_msg_data);
254
255 if(l_return_status <> 'S')
256 then
257 RAISE FND_API.G_EXC_ERROR ;
258 end if;
259 if(nvl(l_show_gnr, 'N') = 'Y')
260 then
261 x_show_gnr := 'Y';
262 end if;
263
264 end if;
265
266 elsif((p_tax_tbl.count > 0) AND ( l_tax_usage_id <> 0))
267 then
268
269
270 -- insert new usages where it does not exist, delete those that have been removed
271 FOR i in p_tax_tbl.first..p_tax_tbl.last
272 LOOP
273 open c_get_dtls(l_tax_usage_id, p_tax_tbl(i).geography_type);
274 fetch c_get_dtls into r_get_dtls;
275 if((p_tax_tbl(i).tax_geo_valid = 'Y') and (c_get_dtls%found))
276 then
277 --dbms_output.put_line('Sudar...2 tx usg');
278 null;
279 elsif((p_tax_tbl(i).tax_geo_valid = 'Y') and (c_get_dtls%notfound))
280 then
281 --dbms_output.put_line('Sudar...3 tx usg');
282 l_address_usage_dtls_tbl.delete;
283 l_address_usage_dtls_tbl(1).geography_type := p_tax_tbl(i).geography_type;
284 l_address_usage_dtls_tbl(1).created_by_module := 'HZ_GEO_HIERARCHY';
285 l_address_usage_dtls_tbl(1).application_id := 222;
286 -- insert dtl record
287 hz_address_usages_pub.create_address_usage_dtls(
288 p_usage_id => l_tax_usage_id,
289 p_address_usage_dtls_tbl => l_address_usage_dtls_tbl,
290 x_usage_dtl_id => l_tax_id_ret,
291 p_init_msg_list => FND_API.G_FALSE,
292 x_return_status => l_return_status,
293 x_msg_count => l_msg_count,
294 x_msg_data => l_msg_data);
295 if(l_return_status <> 'S')
296 then
297 RAISE FND_API.G_EXC_ERROR ;
298 end if;
299
300 if(nvl(l_show_gnr, 'N') = 'Y')
301 then
302 x_show_gnr := 'Y';
303 end if;
304 -- Bug 4726672 : Modified code to call delete_address_usages with all usage records to
305 -- be deleted as a table instead of calling it individually.
306 -- Called delete_address_usages with p_address_usage_dtls_tbl = null
307 -- when all usages are to be deleted.
308
309 elsif((p_tax_tbl(i).tax_geo_valid = 'N')) then
310 l := l +1;
311 if c_get_dtls%found then
312 del_usg := true;
313 --dbms_output.put_line('Sudar...4 tx usg');
314 -- delete detail
315 -- l_address_usage_dtls_tbl.delete;
316 l_address_usage_dtls_del_tbl(k).geography_type := p_tax_tbl(i).geography_type;
317 l_address_usage_dtls_del_tbl(k).created_by_module := 'HZ_GEO_HIERARCHY';
318 l_address_usage_dtls_del_tbl(k).application_id := 222;
319 k := k +1;
320
321 if i <> l then
322 del_all_usg := false;
323 end if;
324
325 end if;
326 end if;
327 close c_get_dtls;
328 END LOOP;
329
330 if del_usg = true and (del_all_usg = false or l <> p_tax_tbl.last)
331 then
332 hz_address_usages_pub.delete_address_usages(p_usage_id => l_tax_usage_id,
333 p_address_usage_dtls_tbl => l_address_usage_dtls_del_tbl,
334 p_init_msg_list => FND_API.G_FALSE,
335 x_return_status => l_return_status,
336 x_msg_count => l_msg_count,
337 x_msg_data => l_msg_data);
338 if(nvl(l_show_gnr, 'N') = 'Y')
339 then
340 x_show_gnr := 'Y';
341 end if;
342
343 elsif del_usg = true and (del_all_usg = true and (l = p_tax_tbl.last ))
344 then
345 l_address_usage_dtls_del_tbl.delete;
346 hz_address_usages_pub.delete_address_usages(p_usage_id => l_tax_usage_id,
347 p_address_usage_dtls_tbl => l_address_usage_dtls_del_tbl,
348 p_init_msg_list => FND_API.G_FALSE,
349 x_return_status => l_return_status,
350 x_msg_count => l_msg_count,
351 x_msg_data => l_msg_data);
352
353 if(nvl(l_show_gnr, 'N') = 'Y')
354 then
355 x_show_gnr := 'Y';
356 end if;
357
358 end if;
359
360 if(l_return_status <> 'S')
361 then
362 RAISE FND_API.G_EXC_ERROR ;
363 end if;
364
365 end if; -- end of p_tax_tbl.count > 0 check
366
367
368 del_usg := false;
369 del_all_usg := true;
370 l := 0;
371 k := 1;
372 l_address_usage_dtls_del_tbl.delete;
373
374
375 -- if geo table is not null and l_geo_usage_id is 0, then insert the geo record
376 -- if tax table is not null and l_tax_usage_id is 0, then insert the tax record
377 if((p_geo_tbl.count > 0) AND ( l_geo_usage_id = 0))
378 then
379
380 -- insert new usage
381 l_address_usages_rec := null;
382 l_address_usages_rec.map_id := p_map_id;
383 l_address_usages_rec.usage_code := 'GEOGRAPHY';
384 l_address_usages_rec.status_flag := 'A';
385 l_address_usages_rec.created_by_module := 'HZ_GEO_HIERARCHY';
386 l_address_usages_rec.application_id := 222;
387
388 l_address_usage_dtls_tbl.delete;
389 j := p_geo_tbl.first;
390 FOR i in p_geo_tbl.first..p_geo_tbl.last
391 LOOP
392 if(p_geo_tbl(i).tax_geo_valid = 'Y')
393 then
394 l_address_usage_dtls_tbl(j).geography_type := p_geo_tbl(i).geography_type;
395 l_address_usage_dtls_tbl(j).created_by_module := 'HZ_GEO_HIERARCHY';
396 l_address_usage_dtls_tbl(j).application_id := 222;
397 j := j + 1;
398 end if;
399 END LOOP;
400 if(l_address_usage_dtls_tbl.count > 0)
401 then
402 hz_address_usages_pub.create_address_usages(p_address_usages_rec => l_address_usages_rec,
403 p_address_usage_dtls_tbl => l_address_usage_dtls_tbl,
404 p_init_msg_list => FND_API.G_FALSE,
405 x_usage_id => l_geo_id_ret,
406 x_return_status => l_return_status,
407 x_msg_count => l_msg_count,
408 x_msg_data => l_msg_data);
409 if(l_return_status <> 'S')
410 then
411 RAISE FND_API.G_EXC_ERROR ;
412 end if;
413 if(nvl(l_show_gnr, 'N') = 'Y')
414 then
415 x_show_gnr := 'Y';
416 end if;
417
418 end if;
419 --dbms_output.put_line('Sudar...4 geo usg'||to_char(l_tax_usage_id)||'..'||l_return_status);
420
421 elsif((p_geo_tbl.count > 0) AND ( l_geo_usage_id <> 0))
422 then
423 -- check for GNR data exists
424
425 -- insert new usages where it does not exist, delete those that have been removed
426 FOR i in p_geo_tbl.first..p_geo_tbl.last
427 LOOP
428 open c_get_dtls(l_geo_usage_id, p_geo_tbl(i).geography_type);
429 fetch c_get_dtls into r_get_dtls;
430 if((p_geo_tbl(i).tax_geo_valid = 'Y') and (c_get_dtls%found))
431 then
432 null;
433 elsif((p_geo_tbl(i).tax_geo_valid = 'Y') and (c_get_dtls%notfound))
434 then
435 l_address_usage_dtls_tbl.delete;
436 l_address_usage_dtls_tbl(1).geography_type := p_geo_tbl(i).geography_type;
437 l_address_usage_dtls_tbl(1).created_by_module := 'HZ_GEO_HIERARCHY';
438 l_address_usage_dtls_tbl(1).application_id := 222;
439 -- insert dtl record
440 hz_address_usages_pub.create_address_usage_dtls(
441 p_usage_id => l_geo_usage_id,
442 p_address_usage_dtls_tbl => l_address_usage_dtls_tbl,
443 x_usage_dtl_id => l_geo_id_ret,
444 p_init_msg_list => FND_API.G_FALSE,
445 x_return_status => l_return_status,
446 x_msg_count => l_msg_count,
447 x_msg_data => l_msg_data);
448 if(l_return_status <> 'S')
449 then
450 RAISE FND_API.G_EXC_ERROR ;
451 end if;
452
453 if(nvl(l_show_gnr, 'N') = 'Y')
454 then
455 x_show_gnr := 'Y';
456 end if;
457
458 -- Bug 4726672 : Modified code to call delete_address_usages with all usage records to
459 -- be deleted as a table instead of calling it individually.
460 -- Called delete_address_usages with p_address_usage_dtl_tbl = null
461 -- when all usages are to be deleted.
462
463 elsif p_geo_tbl(i).tax_geo_valid = 'N' then
464 l := l +1;
465 if c_get_dtls%found then -- delete detail
466 del_usg := true;
467 -- l_address_usage_dtls_tbl.delete;
468 l_address_usage_dtls_del_tbl(k).geography_type := p_geo_tbl(i).geography_type;
469 l_address_usage_dtls_del_tbl(k).created_by_module := 'HZ_GEO_HIERARCHY';
470 l_address_usage_dtls_del_tbl(k).application_id := 222;
471 k := k + 1;
472
473 if i <> l then
474 del_all_usg := false;
475 end if;
476
477 end if;
478 end if;
479 close c_get_dtls;
480 END LOOP;
481
482 if del_usg = true and (del_all_usg = false or l <> p_geo_tbl.last)
483 then
484 hz_address_usages_pub.delete_address_usages(p_usage_id => l_geo_usage_id,
485 p_address_usage_dtls_tbl => l_address_usage_dtls_del_tbl,
486 p_init_msg_list => FND_API.G_FALSE,
487 x_return_status => l_return_status,
488 x_msg_count => l_msg_count,
489 x_msg_data => l_msg_data);
490 if(nvl(l_show_gnr, 'N') = 'Y')
491 then
492 x_show_gnr := 'Y';
493 end if;
494
495 elsif del_usg = true and (del_all_usg = true and (l = p_geo_tbl.last ))
496 then
497 l_address_usage_dtls_del_tbl.delete;
498 hz_address_usages_pub.delete_address_usages(p_usage_id => l_geo_usage_id,
499 p_address_usage_dtls_tbl => l_address_usage_dtls_del_tbl,
500 p_init_msg_list => FND_API.G_FALSE,
501 x_return_status => l_return_status,
502 x_msg_count => l_msg_count,
503 x_msg_data => l_msg_data);
504 if(nvl(l_show_gnr, 'N') = 'Y')
505 then
506 x_show_gnr := 'Y';
507 end if;
508 end if;
509 if(l_return_status <> 'S')
510 then
511 RAISE FND_API.G_EXC_ERROR ;
512 end if;
513 end if; -- end of p_tax_tbl.count > 0 check
514 END IF; -- END OF CHECK FOR p_usage_tbl.COUNT > 0
515
516
517 EXCEPTION
518 WHEN FND_API.G_EXC_ERROR THEN
519
520 x_return_status := FND_API.G_RET_STS_ERROR;
521 FND_MSG_PUB.Count_And_Get(
522 p_encoded => FND_API.G_FALSE,
523 p_count => x_msg_count,
524 p_data => x_msg_data);
525 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
526
527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
528 FND_MSG_PUB.Count_And_Get(
529 p_encoded => FND_API.G_FALSE,
530 p_count => x_msg_count,
531 p_data => x_msg_data);
532
533 WHEN OTHERS THEN
534
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
536 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
537 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
538 FND_MSG_PUB.ADD;
539 FND_MSG_PUB.Count_And_Get(
540 p_encoded => FND_API.G_FALSE,
541 p_count => x_msg_count,
542 p_data => x_msg_data);
543 END update_map_usages;
544
545 FUNCTION get_geo_ref(p_geography_id IN NUMBER,
546 p_loc_table_name IN VARCHAR2)
547 RETURN VARCHAR2
548 IS
549
550 cursor geo_cur(l_geography_id IN number)
551 is
552 select country_code
553 from hz_geographies
554 where geography_id = l_geography_id
555 and geography_type = 'COUNTRY';
556
557 l_country_code varchar2(30);
558
559 cursor hz_cur(ll_country_code in varchar2)
560 is
561 select 'Y'
562 from hz_geo_name_reference_log geo
563 where exists ( select 'Y'
564 from hz_locations loc
565 where loc.country = ll_country_code
566 AND geo.location_id = loc.location_id)
567 and location_table_name = 'HZ_LOCATIONS'
568 and rownum = 1; --bug 6870808
569
570 cursor hr_cur(ll_country_code in varchar2)
571 is
572 select 'Y'
573 from hz_geo_name_reference_log geo
574 where exists ( select 'Y'
575 from hr_locations_all loc
576 where loc.country = ll_country_code
577 AND geo.location_id = loc.location_id)
578 and location_table_name = 'HR_LOCATIONS_ALL'
579 and rownum = 1; --bug 6870808
580
581 l_return VARCHAR2(10);
582 l_value varchar(1);
583 BEGIN
584 l_value := 'N';
585 l_return := 'N';
586 -- get country code from hz_geographies
587 open geo_cur(p_geography_id);
588 fetch geo_cur into l_country_code;
589 close geo_cur;
590 if( l_country_code is not null)
591 then
592 -- check if gnr data exists
593 if(p_loc_table_name = 'HZ_LOCATIONS')
594 then
595 open hz_cur(l_country_code);
596 fetch hz_cur into l_value;
597 close hz_cur;
598 else
599 open hr_cur(l_country_code);
600 fetch hr_cur into l_value;
601 close hr_cur;
602 end if;
603
604 if ( nvl(l_value, 'N') = 'Y')
605 then
606 l_return := 'Y';
607 else
608 l_return := 'N';
609 end if;
610
611 end if; -- end of country code null check
612 return l_return;
613
614
615 END get_geo_ref;
616
617 FUNCTION get_country_name(p_geography_id IN NUMBER)
618 RETURN VARCHAR2
619 IS
620 l_return varchar2(2000);
621
622 cursor get_ctry(l_geog_id varchar2)
623 is
624 select g2.geography_name
625 from hz_geographies g1,
626 hz_geographies g2
627 where g1.geography_id = l_geog_id
628 and g2.geography_id = g1.geography_element1_id
629 and g2.geography_type = 'COUNTRY';
630
631 BEGIN
632
633 open get_ctry(p_geography_id);
634 fetch get_ctry into l_return;
635 close get_ctry;
636
637 return l_return;
638 END get_country_name;
639
640 END HZ_GEO_UI_UTIL_PUB;