[Home] [Help]
PACKAGE BODY: APPS.JTF_TTY_OVERLAP_WEBADI_PKG
Source
1 PACKAGE BODY JTF_TTY_OVERLAP_WEBADI_PKG AS
2 /* $Header: jtftyovb.pls 120.7 2008/07/09 10:14:56 gmarwah ship $ */
3 -- ===========================================================================+
4 -- | Copyright (c) 1999 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +===========================================================================
8 -- Start of Comments
9 -- ---------------------------------------------------
10 -- PURPOSE
11 --
12 -- Population of interface table for Overlapping Territory Group Report
13 --
14 -- Procedures:
15 -- (see below for specification)
16 --
17 -- This package is publicly available for use
18 --
19 -- HISTORY
20 -- 08/13/2003 ARPATEL Created
21 -- 07/08/2008 Gmarwah Modified to handle 20
22 -- overlapping territories
23 -- End of Comments
24 --
25 -- *******************************************************
26 -- Start of Comments
27 -- *******************************************************
28
29 procedure POPULATE_INTERFACE( p_named_account_id in varchar2,
30 p_terr_group_id in varchar2,
31 p_DUNS in varchar2,
32 p_userid in varchar2,
33 x_seq out NOCOPY varchar2
34 ) IS
35
36
37 l_seq number;
38 l_TG1 VARCHAR2(360);
39 l_PARENT1 VARCHAR2(360);
40 l_TG2 VARCHAR2(360);
41 l_PARENT2 VARCHAR2(360);
42 l_TG3 VARCHAR2(360);
43 l_PARENT3 VARCHAR2(360);
44 l_TG4 VARCHAR2(360);
45 l_PARENT4 VARCHAR2(360);
46 l_TG5 VARCHAR2(360);
47 l_PARENT5 VARCHAR2(360);
48 l_TG6 VARCHAR2(360);
49 l_PARENT6 VARCHAR2(360);
50 l_TG7 VARCHAR2(360);
51 l_PARENT7 VARCHAR2(360);
52 l_TG8 VARCHAR2(360);
53 l_PARENT8 VARCHAR2(360);
54 l_TG9 VARCHAR2(360);
55 l_PARENT9 VARCHAR2(360);
56 l_TG10 VARCHAR2(360);
57 l_PARENT10 VARCHAR2(360);
58 l_TG11 VARCHAR2(360);
59 l_PARENT11 VARCHAR2(360);
60 l_TG12 VARCHAR2(360);
61 l_PARENT12 VARCHAR2(360);
62 l_TG13 VARCHAR2(360);
63 l_PARENT13 VARCHAR2(360);
64 l_TG14 VARCHAR2(360);
65 l_PARENT14 VARCHAR2(360);
66 l_TG15 VARCHAR2(360);
67 l_PARENT15 VARCHAR2(360);
68 l_TG16 VARCHAR2(360);
69 l_PARENT16 VARCHAR2(360);
70 l_TG17 VARCHAR2(360);
71 l_PARENT17 VARCHAR2(360);
72 l_TG18 VARCHAR2(360);
73 l_PARENT18 VARCHAR2(360);
74 l_TG19 VARCHAR2(360);
75 l_PARENT19 VARCHAR2(360);
76 l_TG20 VARCHAR2(360);
77 l_PARENT20 VARCHAR2(360);
78
79 cursor c_NA_in_TG is
80 select distinct named_account_id
81 from jtf_tty_terr_grp_accts
82 where terr_group_id = p_terr_group_id;
83
84 cursor c_conflicting_TG(c_named_account_id VARCHAR2) is
85 select distinct TG.terr_group_name
86 , TER.name parent_territory
87 from jtf_tty_terr_grp_accts TGA
88 ,jtf_tty_terr_groups TG
89 ,jtf_terr_all TER
90 where TGA.named_account_id = c_named_account_id
91 and TGA.terr_group_id = TG.terr_group_id
92 and TG.parent_terr_id = TER.terr_id
93 and trunc(TG.active_from_date) <= trunc(sysdate)
94 and trunc(nvl(TG.active_to_date, sysdate)) >= trunc(sysdate) ;
95
96 cursor c_NA_for_DUNS is
97 select distinct named_account_id
98 from jtf_tty_named_accts NA
99 ,hz_parties HZP
100 where HZP.party_id = NA.party_id
101 and HZP.duns_number_c = p_DUNS;
102
103 J NUMBER;
104 BEGIN
105
106 -- remove existing old data for this userid
107 delete from JTF_TTY_WEBADI_INTERFACES
108 where user_id = to_number(p_userid)
109 and sysdate - creation_date >2;
110
111 select jtf_tty_interface_s.nextval into l_seq from dual;
112 x_seq := l_seq;
113
114 --process if territory group chosen
115 if p_terr_group_id is not null
116 then
117 --find all named accounts in this territory group
118 FOR NA_rec in c_NA_in_TG
119 LOOP
120 J := 0;
121 --populate local variables to show conflicting TG denormalised for each NA
122 FOR conflict_TG_rec in c_conflicting_TG(NA_rec.named_Account_id)
123 LOOP
124 J := J+1;
125 --maximum of 20 conflicting TG's allowed in this report
126 if J=1 then
127 l_TG1 := conflict_TG_rec.terr_group_name;
128 l_PARENT1 := conflict_TG_rec.parent_territory;
129 end if;
130
131 if J=2 then
132 l_TG2 := conflict_TG_rec.terr_group_name;
133 l_PARENT2 := conflict_TG_rec.parent_territory;
134 end if;
135
136 if J=3 then
137 l_TG3 := conflict_TG_rec.terr_group_name;
141 if J=4 then
138 l_PARENT3 := conflict_TG_rec.parent_territory;
139 end if;
140
142 l_TG4 := conflict_TG_rec.terr_group_name;
143 l_PARENT4 := conflict_TG_rec.parent_territory;
144 end if;
145
146 if J=5 then
147 l_TG5 := conflict_TG_rec.terr_group_name;
148 l_PARENT5 := conflict_TG_rec.parent_territory;
149 end if;
150
151 if J=6 then
152 l_TG6 := conflict_TG_rec.terr_group_name;
153 l_PARENT6 := conflict_TG_rec.parent_territory;
154 end if;
155
156 if J=7 then
157 l_TG7 := conflict_TG_rec.terr_group_name;
158 l_PARENT7 := conflict_TG_rec.parent_territory;
159 end if;
160
161 if J=8 then
162 l_TG8 := conflict_TG_rec.terr_group_name;
163 l_PARENT8 := conflict_TG_rec.parent_territory;
164 end if;
165
166 if J=9 then
167 l_TG9 := conflict_TG_rec.terr_group_name;
168 l_PARENT9 := conflict_TG_rec.parent_territory;
169 end if;
170
171 if J=10 then
172 l_TG10 := conflict_TG_rec.terr_group_name;
173 l_PARENT10 := conflict_TG_rec.parent_territory;
174 end if;
175
176 if J=11 then
177 l_TG11 := conflict_TG_rec.terr_group_name;
178 l_PARENT11 := conflict_TG_rec.parent_territory;
179 end if;
180
181 if J=12 then
182 l_TG12 := conflict_TG_rec.terr_group_name;
183 l_PARENT12 := conflict_TG_rec.parent_territory;
184 end if;
185
186 if J=13 then
190
187 l_TG13 := conflict_TG_rec.terr_group_name;
188 l_PARENT13 := conflict_TG_rec.parent_territory;
189 end if;
194 end if;
191 if J=14 then
192 l_TG14 := conflict_TG_rec.terr_group_name;
193 l_PARENT14 := conflict_TG_rec.parent_territory;
195
196 if J=15 then
197 l_TG15 := conflict_TG_rec.terr_group_name;
198 l_PARENT15 := conflict_TG_rec.parent_territory;
199 end if;
200
201 if J=16 then
202 l_TG16 := conflict_TG_rec.terr_group_name;
203 l_PARENT16 := conflict_TG_rec.parent_territory;
204 end if;
205
206 if J=17 then
207 l_TG17 := conflict_TG_rec.terr_group_name;
208 l_PARENT17 := conflict_TG_rec.parent_territory;
209 end if;
210
211 if J=18 then
212 l_TG18 := conflict_TG_rec.terr_group_name;
213 l_PARENT18 := conflict_TG_rec.parent_territory;
214 end if;
215
216 if J=19 then
217 l_TG19 := conflict_TG_rec.terr_group_name;
218 l_PARENT19 := conflict_TG_rec.parent_territory;
219 end if;
220
221 if J=20 then
222 l_TG20 := conflict_TG_rec.terr_group_name;
223 l_PARENT20 := conflict_TG_rec.parent_territory;
224 end if;
225
226 if J=20 then
227 EXIT;
228 end if;
229
230 END LOOP;
231
232 --insert record into table, if there the named accountbelongs to more than one territory group
233 if J>1 then
234
235 insert into JTF_TTY_WEBADI_INTERFACES
236 ( user_sequence
237 ,user_id
238 ,attribute1
239 ,attribute2
240 ,attribute3
241 ,attribute4
242 ,attribute5
243 ,attribute6
244 ,attribute7
245 ,attribute8
246 ,attribute9
247 ,attribute10
248 ,attribute11
249 ,attribute12
250 ,attribute13
251 ,attribute14
252 ,attribute15
253 ,attribute16
254 ,attribute17
255 ,attribute18
256 ,attribute19
257 ,attribute20
258 ,attribute21
259 ,attribute22
260 ,created_by
261 ,creation_date
262 ,last_updated_by
263 ,last_update_date
264 ,ATTRIBUTE25
265 ,ATTRIBUTE26
266 ,ATTRIBUTE27
267 ,ATTRIBUTE28
268 ,ATTRIBUTE29
269 ,ATTRIBUTE30
270 ,ATTRIBUTE31
271 ,ATTRIBUTE32
272 ,ATTRIBUTE33
273 ,ATTRIBUTE34
274 ,ATTRIBUTE35
275 ,ATTRIBUTE36
276 ,ATTRIBUTE37
277 ,ATTRIBUTE38
278 ,ATTRIBUTE39
279 ,ATTRIBUTE40
280 ,ATTRIBUTE41
281 ,ATTRIBUTE42
282 ,ATTRIBUTE43
283 ,ATTRIBUTE44
284 ,ATTRIBUTE45
285 ,ATTRIBUTE46
286 ,ATTRIBUTE47
287 ,ATTRIBUTE48
288 ,ATTRIBUTE49
289 ,ATTRIBUTE50
290 ,ATTRIBUTE51
291 ,ATTRIBUTE52
292 ,ATTRIBUTE53
293 ,ATTRIBUTE54
294 ,ATTRIBUTE55
295
296 ) select
297 l_seq
298 , p_userid
299 , HZP.party_name
300 , LKP.meaning site_type
301 , HZP.party_number
302 , HZP.duns_number_c site_duns
303 , HZP.known_as trade_name
304 , null
305 , null
306 , null
307 , null
308 , HZP.city
309 , HZP.state
310 , HZP.postal_code
311 , l_TG1
312 , l_PARENT1
313 , l_TG2
314 , l_PARENT2
315 , l_TG3
316 , l_PARENT3
317 , l_TG4
318 , l_PARENT4
319 , l_TG5
320 , l_PARENT5
321 , G_USER
322 , SYSDATE
323 , G_USER
324 , SYSDATE
325 , NA.PARTY_SITE_ID
326 , l_TG6
327 , l_PARENT6
328 , l_TG7
329 , l_PARENT7
330 , l_TG8
331 , l_PARENT8
332 , l_TG9
333 , l_PARENT9
334 , l_TG10
335 , l_PARENT10
336 , l_TG11
337 , l_PARENT11
338 , l_TG12
339 , l_PARENT12
340 , l_TG13
341 , l_PARENT13
342 , l_TG14
343 , l_PARENT14
344 , l_TG15
345 , l_PARENT15
346 , l_TG16
347 , l_PARENT16
348 , l_TG17
349 , l_PARENT17
350 , l_TG18
351 , l_PARENT18
352 , l_TG19
353 , l_PARENT19
354 , l_TG20
355 , l_PARENT20
356 from
357 jtf_tty_named_Accts NA
358 ,hz_parties HZP
359 ,fnd_lookups LKP
360 where NA.named_account_id = NA_rec.named_Account_id
361 and NA.party_id = HZP.party_id
362 and NA.site_type_code = LKP.lookup_code
363 and LKP.lookup_type = 'JTF_TTY_SITE_TYPE_CODE';
364
365 end if;
366 l_TG1 :=null;
367 l_TG2 :=null;
368 l_TG3 :=null;
369 l_TG4 :=null;
370 l_TG5 :=null;
371 l_TG6 :=null;
372 l_TG7 :=null;
373 l_TG8 :=null;
374 l_TG9 :=null;
375 l_TG10 :=null;
376 l_TG11 :=null;
377 l_TG12 :=null;
381 l_TG16 :=null;
378 l_TG13 :=null;
379 l_TG14 :=null;
380 l_TG15 :=null;
382 l_TG17 :=null;
383 l_TG18 :=null;
384 l_TG19 :=null;
385 l_TG20 :=null;
386
387 l_PARENT1 :=null;
388 l_PARENT2 :=null;
389 l_PARENT3 :=null;
390 l_PARENT4 :=null;
391 l_PARENT5 :=null;
392 l_PARENT6 :=null;
393 l_PARENT7 :=null;
394 l_PARENT8 :=null;
395 l_PARENT9 :=null;
396 l_PARENT10 :=null;
397 l_PARENT11 :=null;
398 l_PARENT12 :=null;
399 l_PARENT13 :=null;
400 l_PARENT14 :=null;
401 l_PARENT15 :=null;
402 l_PARENT16 :=null;
403 l_PARENT17 :=null;
404 l_PARENT18 :=null;
405 l_PARENT19 :=null;
406 l_PARENT20 :=null;
407
408 END LOOP;
409
410 elsif p_named_account_id is not null
411 then
412 J := 0;
413 --populate local variables to show conflicting TG denormalised for each NA
414 FOR conflict_TG_rec in c_conflicting_TG(p_named_account_id)
415 LOOP
416 J := J+1;
417 --maximum of 5 conflicting TG's allowed in this report
418 if J=1 then
419 l_TG1 := conflict_TG_rec.terr_group_name;
420 l_PARENT1 := conflict_TG_rec.parent_territory;
421 end if;
422
423 if J=2 then
424 l_TG2 := conflict_TG_rec.terr_group_name;
425 l_PARENT2 := conflict_TG_rec.parent_territory;
426 end if;
427
428 if J=3 then
429 l_TG3 := conflict_TG_rec.terr_group_name;
430 l_PARENT3 := conflict_TG_rec.parent_territory;
431 end if;
432
433 if J=4 then
434 l_TG4 := conflict_TG_rec.terr_group_name;
435 l_PARENT4 := conflict_TG_rec.parent_territory;
436 end if;
437
438 if J=5 then
439 l_TG5 := conflict_TG_rec.terr_group_name;
440 l_PARENT5 := conflict_TG_rec.parent_territory;
441 end if;
442
443 if J=6 then
444 l_TG6 := conflict_TG_rec.terr_group_name;
445 l_PARENT6 := conflict_TG_rec.parent_territory;
446 end if;
447
448 if J=7 then
449 l_TG7 := conflict_TG_rec.terr_group_name;
450 l_PARENT7 := conflict_TG_rec.parent_territory;
451 end if;
452
453 if J=8 then
454 l_TG8 := conflict_TG_rec.terr_group_name;
455 l_PARENT8 := conflict_TG_rec.parent_territory;
456 end if;
457
458 if J=9 then
459 l_TG9 := conflict_TG_rec.terr_group_name;
460 l_PARENT9 := conflict_TG_rec.parent_territory;
461 end if;
462
463 if J=10 then
464 l_TG10 := conflict_TG_rec.terr_group_name;
465 l_PARENT10 := conflict_TG_rec.parent_territory;
466 end if;
467
468 if J=11 then
469 l_TG11 := conflict_TG_rec.terr_group_name;
470 l_PARENT11 := conflict_TG_rec.parent_territory;
471 end if;
472
473 if J=12 then
474 l_TG12 := conflict_TG_rec.terr_group_name;
475 l_PARENT12 := conflict_TG_rec.parent_territory;
476 end if;
477
478 if J=13 then
479 l_TG13 := conflict_TG_rec.terr_group_name;
480 l_PARENT13 := conflict_TG_rec.parent_territory;
481 end if;
482
483 if J=14 then
484 l_TG14 := conflict_TG_rec.terr_group_name;
485 l_PARENT14 := conflict_TG_rec.parent_territory;
486 end if;
487
488 if J=15 then
489 l_TG15 := conflict_TG_rec.terr_group_name;
490 l_PARENT15 := conflict_TG_rec.parent_territory;
491 end if;
492
493 if J=16 then
494 l_TG16 := conflict_TG_rec.terr_group_name;
495 l_PARENT16 := conflict_TG_rec.parent_territory;
496 end if;
497
498 if J=17 then
499 l_TG17 := conflict_TG_rec.terr_group_name;
500 l_PARENT17 := conflict_TG_rec.parent_territory;
501 end if;
502
503 if J=18 then
504 l_TG18 := conflict_TG_rec.terr_group_name;
505 l_PARENT18 := conflict_TG_rec.parent_territory;
506 end if;
507
508 if J=19 then
509 l_TG19 := conflict_TG_rec.terr_group_name;
510 l_PARENT19 := conflict_TG_rec.parent_territory;
511 end if;
512
513 if J=20 then
514 l_TG20 := conflict_TG_rec.terr_group_name;
515 l_PARENT20 := conflict_TG_rec.parent_territory;
516 end if;
517
518 if J=20 then
519 EXIT;
520 end if;
521
522 END LOOP;
523
524 --insert record into table, if there the named accountbelongs to more than one territory group
525 if J>1 then
526
527 insert into JTF_TTY_WEBADI_INTERFACES
528 ( user_sequence
529 ,user_id
530 ,attribute1
531 ,attribute2
532 ,attribute3
533 ,attribute4
534 ,attribute5
535 ,attribute6
536 ,attribute7
537 ,attribute8
538 ,attribute9
539 ,attribute10
540 ,attribute11
541 ,attribute12
542 ,attribute13
543 ,attribute14
544 ,attribute15
545 ,attribute16
546 ,attribute17
547 ,attribute18
551 ,attribute22
548 ,attribute19
549 ,attribute20
550 ,attribute21
552 ,created_by
553 ,creation_date
554 ,last_updated_by
555 ,last_update_date
556 ,ATTRIBUTE25
557 ,ATTRIBUTE26
558 ,ATTRIBUTE27
559 ,ATTRIBUTE28
560 ,ATTRIBUTE29
561 ,ATTRIBUTE30
562 ,ATTRIBUTE31
563 ,ATTRIBUTE32
564 ,ATTRIBUTE33
565 ,ATTRIBUTE34
566 ,ATTRIBUTE35
567 ,ATTRIBUTE36
568 ,ATTRIBUTE37
569 ,ATTRIBUTE38
570 ,ATTRIBUTE39
571 ,ATTRIBUTE40
572 ,ATTRIBUTE41
573 ,ATTRIBUTE42
574 ,ATTRIBUTE43
575 ,ATTRIBUTE44
576 ,ATTRIBUTE45
577 ,ATTRIBUTE46
578 ,ATTRIBUTE47
579 ,ATTRIBUTE48
580 ,ATTRIBUTE49
581 ,ATTRIBUTE50
582 ,ATTRIBUTE51
583 ,ATTRIBUTE52
584 ,ATTRIBUTE53
585 ,ATTRIBUTE54
586 ,ATTRIBUTE55
587
588 ) select
589 l_seq
590 , p_userid
591 , HZP.party_name
592 , LKP.meaning site_type
593 , HZP.party_number
594 , HZP.duns_number_c site_duns
595 , HZP.known_as trade_name
596 , null
597 , null
598 , null
599 , null
600 , HZP.city
601 , HZP.state
602 , HZP.postal_code
603 , l_TG1
604 , l_PARENT1
605 , l_TG2
606 , l_PARENT2
607 , l_TG3
608 , l_PARENT3
609 , l_TG4
610 , l_PARENT4
611 , l_TG5
612 , l_PARENT5
613 , G_USER
614 , SYSDATE
615 , G_USER
616 , SYSDATE
617 , NA.PARTY_SITE_ID
618 , l_TG6
619 , l_PARENT6
620 , l_TG7
621 , l_PARENT7
622 , l_TG8
623 , l_PARENT8
624 , l_TG9
625 , l_PARENT9
626 , l_TG10
627 , l_PARENT10
628 , l_TG11
629 , l_PARENT11
630 , l_TG12
631 , l_PARENT12
632 , l_TG13
633 , l_PARENT13
634 , l_TG14
635 , l_PARENT14
636 , l_TG15
637 , l_PARENT15
638 , l_TG16
639 , l_PARENT16
640 , l_TG17
641 , l_PARENT17
642 , l_TG18
643 , l_PARENT18
644 , l_TG19
645 , l_PARENT19
646 , l_TG20
647 , l_PARENT20
648 from
649 jtf_tty_named_Accts NA
650 ,hz_parties HZP
651 ,fnd_lookups LKP
652 where NA.named_account_id = p_named_account_id
653 and NA.party_id = HZP.party_id
654 and NA.site_type_code = LKP.lookup_code
655 and LKP.lookup_type = 'JTF_TTY_SITE_TYPE_CODE';
656
657 end if;
658
659 elsif p_DUNS is not null
660 then
661 --find NAID's from DUNS
662 FOR DUNS_NA_rec in c_NA_for_DUNS
663 LOOP
664
665 J := 0;
666 --populate local variables to show conflicting TG denormalised for each NA
667 FOR conflict_TG_rec in c_conflicting_TG(DUNS_NA_rec.named_account_id)
668 LOOP
669 J := J+1;
670 --maximum of 5 conflicting TG's allowed in this report
671 if J=1 then
672 l_TG1 := conflict_TG_rec.terr_group_name;
673 l_PARENT1 := conflict_TG_rec.parent_territory;
674 end if;
675
676 if J=2 then
677 l_TG2 := conflict_TG_rec.terr_group_name;
678 l_PARENT2 := conflict_TG_rec.parent_territory;
679 end if;
680
681 if J=3 then
682 l_TG3 := conflict_TG_rec.terr_group_name;
683 l_PARENT3 := conflict_TG_rec.parent_territory;
684 end if;
685
686 if J=4 then
687 l_TG4 := conflict_TG_rec.terr_group_name;
688 l_PARENT4 := conflict_TG_rec.parent_territory;
689 end if;
690
691 if J=5 then
692 l_TG5 := conflict_TG_rec.terr_group_name;
693 l_PARENT5 := conflict_TG_rec.parent_territory;
694 end if;
695
696 if J=6 then
697 l_TG6 := conflict_TG_rec.terr_group_name;
698 l_PARENT6 := conflict_TG_rec.parent_territory;
699 end if;
700
701 if J=7 then
702 l_TG7 := conflict_TG_rec.terr_group_name;
703 l_PARENT7 := conflict_TG_rec.parent_territory;
704 end if;
705
706 if J=8 then
707 l_TG8 := conflict_TG_rec.terr_group_name;
708 l_PARENT8 := conflict_TG_rec.parent_territory;
709 end if;
710
711 if J=9 then
712 l_TG9 := conflict_TG_rec.terr_group_name;
713 l_PARENT9 := conflict_TG_rec.parent_territory;
714 end if;
715
716 if J=10 then
717 l_TG10 := conflict_TG_rec.terr_group_name;
718 l_PARENT10 := conflict_TG_rec.parent_territory;
719 end if;
720
721 if J=11 then
722 l_TG11 := conflict_TG_rec.terr_group_name;
723 l_PARENT11 := conflict_TG_rec.parent_territory;
724 end if;
725
726 if J=12 then
730
727 l_TG12 := conflict_TG_rec.terr_group_name;
728 l_PARENT12 := conflict_TG_rec.parent_territory;
729 end if;
731 if J=13 then
732 l_TG13 := conflict_TG_rec.terr_group_name;
733 l_PARENT13 := conflict_TG_rec.parent_territory;
734 end if;
735
736 if J=14 then
737 l_TG14 := conflict_TG_rec.terr_group_name;
738 l_PARENT14 := conflict_TG_rec.parent_territory;
739 end if;
740
741 if J=15 then
742 l_TG15 := conflict_TG_rec.terr_group_name;
743 l_PARENT15 := conflict_TG_rec.parent_territory;
744 end if;
745
746 if J=16 then
747 l_TG16 := conflict_TG_rec.terr_group_name;
748 l_PARENT16 := conflict_TG_rec.parent_territory;
749 end if;
750
751 if J=17 then
752 l_TG17 := conflict_TG_rec.terr_group_name;
753 l_PARENT17 := conflict_TG_rec.parent_territory;
754 end if;
755
756 if J=18 then
757 l_TG18 := conflict_TG_rec.terr_group_name;
758 l_PARENT18 := conflict_TG_rec.parent_territory;
759 end if;
760
761 if J=19 then
762 l_TG19 := conflict_TG_rec.terr_group_name;
763 l_PARENT19 := conflict_TG_rec.parent_territory;
764 end if;
765
766 if J=20 then
767 l_TG20 := conflict_TG_rec.terr_group_name;
768 l_PARENT20 := conflict_TG_rec.parent_territory;
769 end if;
770
771 if J=20 then
772 EXIT;
773 end if;
774
775 END LOOP;
776
777 --insert record into table, if there the named accountbelongs to more than one territory group
778 if J>1 then
779
780 insert into JTF_TTY_WEBADI_INTERFACES
781 ( user_sequence
782 ,user_id
783 ,attribute1
784 ,attribute2
785 ,attribute3
786 ,attribute4
787 ,attribute5
788 ,attribute6
789 ,attribute7
790 ,attribute8
791 ,attribute9
792 ,attribute10
793 ,attribute11
794 ,attribute12
795 ,attribute13
796 ,attribute14
797 ,attribute15
798 ,attribute16
799 ,attribute17
800 ,attribute18
801 ,attribute19
802 ,attribute20
803 ,attribute21
804 ,attribute22
805 ,created_by
806 ,creation_date
807 ,last_updated_by
808 ,last_update_date
809 ,ATTRIBUTE25
810 ,ATTRIBUTE26
811 ,ATTRIBUTE27
812 ,ATTRIBUTE28
813 ,ATTRIBUTE29
814 ,ATTRIBUTE30
815 ,ATTRIBUTE31
816 ,ATTRIBUTE32
817 ,ATTRIBUTE33
818 ,ATTRIBUTE34
819 ,ATTRIBUTE35
820 ,ATTRIBUTE36
821 ,ATTRIBUTE37
822 ,ATTRIBUTE38
823 ,ATTRIBUTE39
824 ,ATTRIBUTE40
825 ,ATTRIBUTE41
826 ,ATTRIBUTE42
827 ,ATTRIBUTE43
828 ,ATTRIBUTE44
829 ,ATTRIBUTE45
830 ,ATTRIBUTE46
831 ,ATTRIBUTE47
832 ,ATTRIBUTE48
833 ,ATTRIBUTE49
834 ,ATTRIBUTE50
835 ,ATTRIBUTE51
836 ,ATTRIBUTE52
837 ,ATTRIBUTE53
838 ,ATTRIBUTE54
839 ,ATTRIBUTE55
840
841 ) select
842 l_seq
843 , p_userid
844 , HZP.party_name
845 , LKP.meaning site_type
846 , HZP.party_number
847 , HZP.duns_number_c site_duns
848 , HZP.known_as trade_name
849 , null
850 , null
851 , null
852 , null
853 , HZP.city
854 , HZP.state
855 , HZP.postal_code
856 , l_TG1
857 , l_PARENT1
858 , l_TG2
859 , l_PARENT2
860 , l_TG3
861 , l_PARENT3
862 , l_TG4
863 , l_PARENT4
864 , l_TG5
865 , l_PARENT5
866 , G_USER
867 , SYSDATE
868 , G_USER
869 , SYSDATE
870 ,NA.PARTY_SITE_ID
871 , l_TG6
872 , l_PARENT6
873 , l_TG7
874 , l_PARENT7
875 , l_TG8
876 , l_PARENT8
877 , l_TG9
878 , l_PARENT9
879 , l_TG10
880 , l_PARENT10
881 , l_TG11
882 , l_PARENT11
883 , l_TG12
884 , l_PARENT12
885 , l_TG13
886 , l_PARENT13
887 , l_TG14
888 , l_PARENT14
889 , l_TG15
890 , l_PARENT15
891 , l_TG16
892 , l_PARENT16
893 , l_TG17
894 , l_PARENT17
895 , l_TG18
896 , l_PARENT18
897 , l_TG19
898 , l_PARENT19
899 , l_TG20
900 , l_PARENT20
901
902 from
903 jtf_tty_named_Accts NA
904 ,hz_parties HZP
905 ,fnd_lookups LKP
906 where NA.named_account_id = DUNS_NA_rec.named_account_id
907 and NA.party_id = HZP.party_id
908 and NA.site_type_code = LKP.lookup_code
909 and LKP.lookup_type = 'JTF_TTY_SITE_TYPE_CODE';
910
911 end if;
912 l_TG1 :=null;
913 l_TG2 :=null;
914 l_TG3 :=null;
915 l_TG4 :=null;
916 l_TG5 :=null;
917 l_TG6 :=null;
918 l_TG7 :=null;
919 l_TG8 :=null;
920 l_TG9 :=null;
921 l_TG10 :=null;
922 l_TG11 :=null;
923 l_TG12 :=null;
924 l_TG13 :=null;
925 l_TG14 :=null;
926 l_TG15 :=null;
927 l_TG16 :=null;
928 l_TG17 :=null;
929 l_TG18 :=null;
930 l_TG19 :=null;
931 l_TG20 :=null;
932
933 l_PARENT1 :=null;
934 l_PARENT2 :=null;
935 l_PARENT3 :=null;
936 l_PARENT4 :=null;
937 l_PARENT5 :=null;
938 l_PARENT6 :=null;
939 l_PARENT7 :=null;
940 l_PARENT8 :=null;
941 l_PARENT9 :=null;
942 l_PARENT10 :=null;
943 l_PARENT11 :=null;
944 l_PARENT12 :=null;
945 l_PARENT13 :=null;
946 l_PARENT14 :=null;
947 l_PARENT15 :=null;
948 l_PARENT16 :=null;
949 l_PARENT17 :=null;
950 l_PARENT18 :=null;
951 l_PARENT19 :=null;
952 l_PARENT20 :=null;
953
954
955 END LOOP; --c_NA_for_DUNS
956
957 end if; --territory group chosen
958
959 COMMIT;
960
961 END;
962
963 END JTF_TTY_OVERLAP_WEBADI_PKG;