[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_DENORM_RULES_PVT
Source
1 PACKAGE BODY JTF_TERR_DENORM_RULES_PVT AS
2 /* $Header: jtfvtdrb.pls 120.0 2005/06/02 18:22:42 appldev ship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTF_TERR_DENORM_RULES_PVT
6 -- ---------------------------------------------------
7 -- PURPOSE
8 -- Joint task force core territory manager public api's.
9 -- This packe is used to denormalise the complete territory
10 -- rules based on tha data setup in the JTF territory tables
11 --
12 -- Procedures:
13 -- (see below for specification)
14 --
15 -- NOTES
16 -- This package is publicly available for use
17 --
18 -- HISTORY
19 -- 12/13/00 JDOCHERT CREATED
20 -- 04/05/01 JDOCHERT Removed restriction that resource needs
21 -- to be attached to territory record before
22 -- it will be inserted in JTF_TERR_DENORM_RULES_ALL
23 -- 03/04/02 JDOCHERT bug#2250830
24
25 --
26 -- End of Comments
27 --
28 -- Changes by Hari starts.
29 FUNCTION get_level_from_root(p_terr_id IN number) RETURN NUMBER IS
30
31 l_level NUMBER := 0;
32 l_terr_id NUMBER := p_terr_id;
33
34 CURSOR c_parent_terr_id (p_terr_id IN NUMBER) IS
35 SELECT j.parent_territory_id
36 FROM jtf_terr_all j
37 WHERE j.terr_id = p_terr_id;
38
39 BEGIN
40
41 IF (p_terr_id = 1) THEN
42 RETURN 1;
43 END IF;
44
45 LOOP
46 OPEN c_parent_terr_id(l_terr_id);
47 FETCH c_parent_terr_id into l_terr_id;
48 CLOSE c_parent_terr_id;
49
50 l_level := l_level+1;
51
52 EXIT WHEN l_terr_id = 1;
53 END LOOP;
54
55 RETURN (l_level+1);
56
57 END get_level_from_root;
58 -- Changes by Hari ends.
59
60
61 PROCEDURE Populate_API(
62 P_ERROR_CODE OUT NOCOPY NUMBER
63 , P_ERROR_MSG OUT NOCOPY VARCHAR2
64 , P_SOURCE_ID IN NUMBER
65 , p_qual_type_id IN NUMBER ) IS
66
67 CURSOR csr_get_terr ( lp_source_id NUMBER
68 , lp_qual_type_id NUMBER
69 , lp_sysdate DATE ) IS
70 SELECT jt1.terr_id
71 , NVL(jt1.rank, 999999999)
72 , jt1.num_winners
73 , jt1.parent_territory_id
74 , jt2.num_winners parent_num_winners
75 , jt1.org_id
76 FROM jtf_terr_qtype_usgs_all jtqu
77 , jtf_terr_usgs_all jtu
78 , jtf_terr_all jt2
79 , jtf_terr_all jt1
80 , jtf_qual_type_usgs jqtu
81 WHERE jtqu.terr_id = jt1.terr_id
82 AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
83 AND jqtu.qual_type_id = lp_qual_type_id
84 AND jtu.source_id = lp_source_id
85 AND jtu.terr_id = jt1.terr_id
86 AND jt2.terr_id = jt1.parent_territory_id
87
88 /* ARPATEL: 10/01/03: bug#3171141 fix. */
89 AND ( jt1.org_id = jt2.org_id OR
90 (jt1.org_id IS NULL AND jt2.org_id IS NULL) )
91
92 AND jt1.terr_id <> 1
93 AND NVL(jt1.end_date_active, lp_sysdate + 1) > lp_sysdate
94 AND NVL(jt1.start_date_active, lp_sysdate -1) < lp_sysdate
95 AND jt1.parent_territory_id IS NOT NULL
96
97 --
98 -- Test data
99 --AND jt1.terr_id = 19279
100 --
101
102 --
103 -- JDOCHERT: 11/25/03: Not required as
104 -- records are always deleted at the
105 -- start of DENORM Process
106 --
107 -- AND NOT EXISTS(
108 -- SELECT jtdr.terr_id
109 -- FROM jtf_terr_denorm_rules_all jtdr
110 -- WHERE jtdr.terr_id = jt1.terr_id
111 -- AND jtdr.source_id = lp_source_id
112 -- AND jtdr.qual_type_id = lp_qual_type_id )
113 --
114
115 --
116 -- JDOCHERT: 10/25/03: only need records
117 -- for territories with resources
118 -- JDOCHERT: 11/05/03: removed as it breaks multiple
119 -- level number of winners processing
120 --AND EXISTS
121 -- ( SELECT jtr.terr_id
122 -- FROM jtf_terr_rsc_all jtr
123 -- WHERE (jtr.end_date_active IS NULL OR jtr.end_date_active >= SYSDATE)
124 -- AND (jtr.start_date_active IS NULL OR jtr.start_date_active <= SYSDATE)
125 -- AND jtr.terr_id = jt1.terr_id )
126 --
127
128 AND NOT EXISTS (
129 SELECT jt.terr_id
130 FROM jtf_terr_all jt
131 WHERE ( (NVL(jt.end_date_active, lp_sysdate + 1) < lp_sysdate) OR
132 (NVL(jt.start_date_active, lp_sysdate - 1) > lp_sysdate) )
133 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
134 START WITH jt.terr_id = jt1.terr_id );
135
136 CURSOR csr_get_SALES_terr ( lp_source_id NUMBER
137 , lp_sysdate DATE ) IS
138 SELECT jt1.terr_id
139 , NVL(jt1.rank, 999999999)
140 , jt1.num_winners
141 , jt1.parent_territory_id
142 , jt2.num_winners parent_num_winners
143 , jt1.org_id
144 FROM jtf_terr_usgs_all jtu
145 , jtf_terr_all jt2
146 , jtf_terr_all jt1
147
148 WHERE
149 jtu.source_id = lp_source_id
150 AND jtu.terr_id = jt1.terr_id
151 AND jt2.terr_id = jt1.parent_territory_id
152
153 /* ARPATEL: 10/01/03: bug#3171141 fix. */
154 AND ( jt1.org_id = jt2.org_id OR
155 (jt1.org_id IS NULL AND jt2.org_id IS NULL) )
156
157 AND jt1.terr_id <> 1
158 AND NVL(jt1.end_date_active, lp_sysdate + 1) > lp_sysdate
159 AND NVL(jt1.start_date_active, lp_sysdate -1) < lp_sysdate
160 AND jt1.parent_territory_id IS NOT NULL
161 AND NOT EXISTS (
162 SELECT jt.terr_id
163 FROM jtf_terr_all jt
164 WHERE ( (NVL(jt.end_date_active, lp_sysdate + 1) < lp_sysdate) OR
165 (NVL(jt.start_date_active, lp_sysdate - 1) > lp_sysdate) )
166 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
167 START WITH jt.terr_id = jt1.terr_id );
168
169 l_status varchar2(10);
170 l_industry varchar2(10);
171 l_applsys_schema varchar2(30);
172 l_result boolean;
173
174 L_REQUEST_ID NUMBER := FND_GLOBAL.CONC_REQUEST_ID();
175 L_PROGRAM_APPL_ID NUMBER := FND_GLOBAL.PROG_APPL_ID();
176 L_PROGRAM_ID NUMBER := FND_GLOBAL.CONC_PROGRAM_ID();
177 L_USER_ID NUMBER := FND_GLOBAL.USER_ID();
178
179 l_sysdate DATE := SYSDATE;
180 l_root_terr_id CONSTANT NUMBER := 1;
181 l_new_parent_territory_id NUMBER;
182
183 /* JDOCHERT: 06/30/03: bug#3020630 */
184 l_new_parent_num_winners NUMBER;
185
186 l_leaf_flag VARCHAR2(1);
187 l_level_from_parent NUMBER := 0;
188 l_num_rows_read INTEGER := 0;
189 l_num_rows_inserted INTEGER := 0;
190
191 l_terr_id NUMBER;
192 l_parent_territory_id NUMBER;
193 l_terr_rank NUMBER;
194 l_org_id NUMBER;
195
196 l_num_qual NUMBER;
197 l_level_from_root NUMBER;
198 l_top_level_terr_id NUMBER;
199 l_num_winners NUMBER;
200 l_relative_rank NUMBER;
201 l_absolute_rank NUMBER;
202 l_max_rank NUMBER;
203
204 l_terr_id_tbl jtf_terr_number_list := jtf_terr_number_list(null);
205 l_parent_territory_id_tbl jtf_terr_number_list := jtf_terr_number_list(null);
206 l_terr_rank_tbl jtf_terr_number_list := jtf_terr_number_list(null);
207 l_org_id_tbl jtf_terr_number_list := jtf_terr_number_list(null);
208 l_num_winners_tbl jtf_terr_number_list := jtf_terr_number_list(null);
209 l_num_qual_tbl jtf_terr_number_list := jtf_terr_number_list(null);
210 l_level_from_root_tbl jtf_terr_number_list := jtf_terr_number_list(null);
211 l_level_from_parent_tbl jtf_terr_number_list := jtf_terr_number_list(null);
212 l_top_level_terr_id_tbl jtf_terr_number_list := jtf_terr_number_list(null);
213 l_relative_rank_tbl jtf_terr_number_list := jtf_terr_number_list(null);
214
215 l_parent_num_winners_tbl jtf_terr_number_list := jtf_terr_number_list(null);
216
217 BEGIN
218
219
220 BEGIN
221
222 /* delete old records */
223 /* ARPATEL: 12/03: for ORacle Sales denorm records are no longer striped by TX type */
224 if p_source_id = -1001
225 then
226 DELETE FROM jtf_terr_denorm_rules_all jtdr
227 WHERE jtdr.source_id = p_source_id;
228 else
229 DELETE FROM jtf_terr_denorm_rules_all jtdr
230 WHERE jtdr.source_id = p_source_id
231 AND jtdr.qual_type_id = p_qual_type_id;
232 end if;
233
234 --AND ( jtdr.changed_parent_flag = 'Y' OR
235 -- jtdr.changed_parent_flag IS NULL );
236
237 --dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows from JTF_TERR_DENORM_RULES for ' ||
238 -- p_source_id || '/' || p_qual_type_id);
239
240 EXCEPTION
241 WHEN NO_DATA_FOUND THEN
242 NULL;
243 END;
244
245 BEGIN
246
247 SELECT /*+ ORDERED */ MAX(j2.rank)
248 INTO l_max_rank
249 FROM jtf_qual_type_usgs j1
250 , jtf_terr_all j2
251 , jtf_terr_qtype_usgs_all j4
252 WHERE ( j2.start_date_active <= l_sysdate AND
253 NVL(j2.end_date_active, l_sysdate) >= l_sysdate)
254 AND j2.terr_id <> 1
255 -- EIHSU: 09/27/02: bug#2590004
256 --AND j2.parent_territory_id = 1
257 AND j4.terr_id = j2.terr_id
258 AND j4.qual_type_usg_id = j1.qual_type_usg_id
259
260 ---
261 -- JDOCHERT: 03/03/02: bug#2250830
262 --AND j1.qual_type_id = p_qual_type_id
263 --
264
265 AND j1.source_id = p_source_id;
266
267 --dbms_output.put_line('Value of l_max_rank='||TO_CHAR(l_max_rank));
268
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 l_max_rank := 9999999999;
272 END;
273
274
275 /* Process each territory */
276 --ARPATEL: 12/03/2003 for Oracle Sales only process 1 denorm record per territory
277 if p_source_id = -1001
278 then
279 OPEN csr_get_SALES_terr(p_source_id, SYSDATE);
280 FETCH csr_get_SALES_terr BULK COLLECT INTO l_terr_id_tbl
281 , l_terr_rank_tbl
282 , l_num_winners_tbl /* JDOCHERT: 06/30/03: bug#3020630 */
283 , l_parent_territory_id_tbl
284 , l_parent_num_winners_tbl
285 , l_org_id_tbl ;
286 CLOSE csr_get_SALES_terr;
287 else
288 OPEN csr_get_terr(p_source_id, p_qual_type_id, SYSDATE);
289 FETCH csr_get_terr BULK COLLECT INTO l_terr_id_tbl
290 , l_terr_rank_tbl
291 , l_num_winners_tbl /* JDOCHERT: 06/30/03: bug#3020630 */
292 , l_parent_territory_id_tbl
293 , l_parent_num_winners_tbl
294 , l_org_id_tbl ;
295 CLOSE csr_get_terr;
296 end if;
297
298 --dbms_output.put_line('Value of l_terr_id_tbl.LAST[1]='||TO_CHAR(l_terr_id_tbl.LAST));
299
300 IF (l_terr_id_tbl.COUNT > 0) THEN
301 FOR i IN l_terr_id_tbl.FIRST..l_terr_id_tbl.LAST
302 LOOP
303
304 l_num_rows_read := l_num_rows_read + 1 ;
305
306 l_level_from_parent_tbl.EXTEND;
307 l_level_from_parent_tbl(i) := 0;
308
309 l_num_qual_tbl.EXTEND;
310
311 /* TOTAL number of qualifiers */
312 /* ARPATEL: 12/03/2003: For Oracle Sales num_qual is now stored in jtf_terr_qtype_usgs_all */
313 if p_source_id = -1001
314 then
315 l_num_qual_tbl(i) := 0;
316 else
317 SELECT count(*)
318 INTO l_num_qual_tbl(i)
319 FROM jtf_terr_qual_all jtq
320 , jtf_qual_usgs_all jqu
321 , jtf_qual_type_usgs jqtu
322 , jtf_qual_type_denorm_v v
323 WHERE jtq.qual_usg_id = jqu.qual_usg_id
324 AND ( (jtq.org_id = jqu.org_id) OR
325 (jtq.org_id IS NULL AND jqu.org_ID IS NULL)
326 )
327 AND jqu.qual_type_usg_id = jqtu.qual_type_usg_id
328 AND jqtu.qual_type_id <> -1001
329 AND jqtu.source_id = p_source_id
330 AND jqtu.qual_type_id = v.related_id
331 AND v.qual_type_id = p_qual_type_id
332 AND jtq.terr_id IN
333 ( SELECT jt.terr_id
334 FROM jtf_terr_all jt
335 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
336 START WITH jt.terr_id = l_terr_id_tbl(i) );
337
338 end if; --p_source_id = -1001
339
340 l_level_from_root_tbl.EXTEND;
341 -- Added By Hari Starts
342 l_level_from_root_tbl(i) := get_level_from_root(l_terr_id_tbl(i));
343 -- Added By Hari Ends
344
345 l_top_level_terr_id_tbl.EXTEND;
346
347 --l_num_winners_tbl.EXTEND;
348
349 /* top level terr_id + num_winners */
350 /* JDOCHERT: 06/30/03: bug#3020630: */
351 /* Sales territories now suppport Multiple Winners
352 ** at Multiple Levels so do not default value from
353 ** top-level territory
354 **
355 ** JDOCHERT: 07/07/03: bug#3088766
356 ** If it is a top-level Sales territory and Number of
357 ** Winners is not explicitly, then default value to 1.
358 */
359 IF ( p_source_id = -1001 AND
360 l_parent_territory_id_tbl(i) = 1 AND
361 l_num_winners_tbl(i) IS NULL ) THEN
362
363
364 l_num_winners_tbl(i) := 1;
365
366 END IF;
367
368
369 IF ( p_source_id <> -1001 ) THEN
370
371 SELECT jt.terr_id, NVL(jt.num_winners, 1)
372 INTO l_top_level_terr_id_tbl(i), l_num_winners_tbl(i)
373 FROM jtf_terr_all jt
374 WHERE jt.parent_territory_id = 1
375 AND (jt.org_id <> -3114 OR jt.org_id IS NULL)
376 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
377 START WITH jt.terr_id = l_terr_id_tbl(i);
378
379 END IF;
380
381 /* RELATIVE RANK */
382 l_relative_rank_tbl.EXTEND;
383 l_relative_rank_tbl(i) := 1/(l_terr_rank_tbl(i) * POWER(l_max_rank, l_level_from_root_tbl(i)));
384
385 --dbms_output.put_line('l_terr_id = ' || TO_CHAR(l_terr_id_tbl(i)) ||
386 -- ' / l_relative_rank = ' || TO_CHAR(l_relative_rank_tbl(i)) );
387 --dbms_output.put_line('Value of l_terr_rank='||TO_CHAR(l_terr_rank));
388 --dbms_output.put_line('Value of l_level_from_root='||TO_CHAR(l_level_from_root));
389
390
391 END LOOP; /* FOR i IN l_terr_id_tbl.FIRST..l_terr_id_tbl.LAST */
392 END IF;
393
394 FORALL i IN l_terr_id_tbl.FIRST..l_terr_id_tbl.LAST
395 INSERT INTO jtf_terr_denorm_rules_all(
396 source_id
397 , qual_type_id
398 , terr_id
399 , absolute_rank
400 , relative_rank
401 , num_qual
402 , rank
403 , level_from_root
404 , level_from_parent
408 , immediate_parent_flag
405 , related_terr_id
406 , top_level_terr_id
407 , num_winners
409 , root_flag
410 , leaf_flag
411 , LAST_UPDATE_DATE
412 , LAST_UPDATED_BY
413 , CREATION_DATE
414 , CREATED_BY
415 , LAST_UPDATE_LOGIN
416 , REQUEST_ID
417 , PROGRAM_APPLICATION_ID
418 , PROGRAM_ID
419 , PROGRAM_UPDATE_DATE
420 --, CHANGED_PARENT_FLAG
421 , ORG_ID
422 , QUAL_RELATION_PRODUCT
423 , RESOURCE_EXISTS_FLAG
424 )
425 VALUES (
426 p_source_id
427 , NVL(p_qual_type_id, -1)
428 , l_terr_id_tbl(i)
429 , 9999 /* absolute rank */
430 , l_relative_rank_tbl(i)
431 , l_num_qual_tbl(i)
432 , l_terr_rank_tbl(i)
433 , l_level_from_root_tbl(i)
434 , l_level_from_parent_tbl(i)
435 , l_terr_id_tbl(i) /* related_territory_id */
436 , l_top_level_terr_id_tbl(i)
437 , l_num_winners_tbl(i)
438 , 'N' /* immediate parent flag */
439 , NULL --'N' /* root flag */
440 , NULL /* leaf flag */
441 , L_SYSDATE
442 , L_USER_ID
443 , L_SYSDATE
444 , L_USER_ID
445 , L_USER_ID
446 , L_REQUEST_ID
447 , L_PROGRAM_APPL_ID
448 , L_PROGRAM_ID
449 , L_SYSDATE
450 --, 'N'
451 , l_org_id_tbl(i)
452 , 1
453 , 'N'
454 );
455
456 L_NUM_ROWS_INSERTED := l_terr_id_tbl.LAST;
457
458 --dbms_output.put_line('OTHERS Value of L_NUM_ROWS_INSERTED='||TO_CHAR(L_NUM_ROWS_INSERTED));
459 --dbms_output.put_line('Value of P_ERROR_MSG='||sqlerrm);
460
461 --dbms_output.put_line( ' l_terr_id = '||TO_CHAR(l_terr_id) ||
462 -- ' l_num_qual = ' || TO_CHAR(l_num_qual) ||
463 -- ' L_related_terr_id = ' || TO_CHAR(L_PARENT_TERRitory_ID) ||
464 -- ' l_top_level_terr_id = ' || TO_CHAR(l_top_level_terr_id) ||
465 -- ' l_num_winners = ' || TO_CHAR(l_num_winners) );
466
467 IF (l_terr_id_tbl.COUNT > 0) THEN
468 FOR i IN l_terr_id_tbl.FIRST..l_terr_id_tbl.LAST
469 LOOP
470
471 IF ( l_terr_id_tbl(i) <> l_root_terr_id AND
472 l_parent_territory_id_tbl(i) <> 1 ) THEN
473
474 /* Insert immediate parent details */
475 BEGIN
476
477 l_level_from_parent_tbl(i) := l_level_from_parent_tbl(i) + 1;
478 l_level_from_root_tbl(i) := l_level_from_root_tbl(i) - 1;
479
480 INSERT INTO jtf_terr_denorm_rules_all (
481 source_id
482 , qual_type_id
483 , terr_id
484 , absolute_rank
485 , relative_rank
486 , num_qual
487 , rank
488 , level_from_root
489 , level_from_parent
490 , related_terr_id
491 , top_level_terr_id
492 , num_winners
493 , immediate_parent_flag
494 , root_flag
495 , leaf_flag
496 , LAST_UPDATE_DATE
497 , LAST_UPDATED_BY
498 , CREATION_DATE
499 , CREATED_BY
500 , LAST_UPDATE_LOGIN
501 , REQUEST_ID
502 , PROGRAM_APPLICATION_ID
503 , PROGRAM_ID
504 , PROGRAM_UPDATE_DATE
505 --, CHANGED_PARENT_FLAG
506 , ORG_ID
507 , QUAL_RELATION_PRODUCT
508 )
509 VALUES (
510 p_source_id
511 , NVL(p_qual_type_id, -1)
512 , l_terr_id_tbl(i)
513 , 0 /* absolute_rank */
514 , 0 /* relative_rank */
515 , 0 /* num_qual */
516 , l_terr_rank_tbl(i)
517 , l_level_from_root_tbl(i)
518 , l_level_from_parent_tbl(i)
519 , l_parent_territory_id_tbl(i) /* related_territory_id */
520 , l_top_level_terr_id_tbl(i)
521 , l_parent_num_winners_tbl(i)
522 , 'Y' /* immediate parent flag */
523 , NULL -- DECODE(l_parent_territory_id, l_root_terr_id, 'Y', 'N') /* root flag */
527 , L_SYSDATE
524 , NULL /* leaf flag */
525 , L_SYSDATE
526 , L_USER_ID
528 , L_USER_ID
529 , L_USER_ID
530 , L_REQUEST_ID
531 , L_PROGRAM_APPL_ID
532 , L_PROGRAM_ID
533 , L_SYSDATE
534 --, 'N'
535 , l_org_id_tbl(i)
536 , 1
537 );
538
539 L_NUM_ROWS_INSERTED := L_NUM_ROWS_INSERTED + 1;
540
541 END; -- Immediate parent
542
543
544 LOOP
545
546 /* Check for the ancestors */
547 /* JDOCHERT: 06/30/03: bug#3020630 */
548 SELECT DISTINCT TR1.PARENT_TERRITORY_ID, TR2.NUM_WINNERS
549 INTO l_new_parent_territory_id, l_new_parent_num_winners
550 FROM jtf_terr_all TR1, jtf_terr_all TR2
551 WHERE TR2.terr_id = TR1.parent_territory_id
552 AND TR1.TERR_ID <> 1
553 AND TR1.TERR_ID = l_parent_territory_id_tbl(i);
554
555 EXIT WHEN ( l_parent_territory_id_tbl(i) = l_root_terr_id OR
556 l_new_parent_territory_id = 1 );
557
558 /* Insert the ancestor details */
559 l_level_from_parent_tbl(i) := l_level_from_parent_tbl(i) + 1;
560 l_level_from_root_tbl(i) := l_level_from_root_tbl(i) - 1;
561
562
563 --dbms_output.put_line('parent_terr_id='||
564 --TO_CHAR(l_new_parent_territory_id));
565 --dbms_output.put_line('parent_num_winners='||
566 --TO_CHAR(l_new_parent_num_winners));
567
568 INSERT INTO JTF_TERR_DENORM_RULES_ALL (
569 source_id
570 , qual_type_id
571 , terr_id
572 , absolute_rank
573 , relative_rank
574 , num_qual
575 , rank
576 , level_from_root
577 , level_from_parent
578 , related_terr_id
579 , top_level_terr_id
580 , num_winners
581 , immediate_parent_flag
582 , root_flag
583 , leaf_flag
584 , LAST_UPDATE_DATE
585 , LAST_UPDATED_BY
586 , CREATION_DATE
587 , CREATED_BY
588 , LAST_UPDATE_LOGIN
589 , REQUEST_ID
590 , PROGRAM_APPLICATION_ID
591 , PROGRAM_ID
592 , PROGRAM_UPDATE_DATE
593 --, CHANGED_PARENT_FLAG
594 , ORG_ID
595 , QUAL_RELATION_PRODUCT
596 )
597 VALUES ( p_source_id
598 , NVL(p_qual_type_id, -1)
599 , l_terr_id_tbl(i)
600 , 0 /* absolute_rank */
601 , 0 /* relative_rank */
602 , 0 /* num_qual */
603 , l_terr_rank_tbl(i)
604 , l_level_from_root_tbl(i)
605 , l_level_from_parent_tbl(i)
606 , l_new_parent_territory_id /* related_territory_id */
607 , l_top_level_terr_id_tbl(i)
608 , l_new_parent_num_winners /* JDOCHERT: 06/30/03: bug#3020630 */
609 , 'N' /* immediate parent flag */
610 , NULL -- DECODE(l_new_parent_territory_id, l_root_terr_id, 'Y', 'N') /* root flag */
611 , NULL -- 'N' /* leaf flag */
612 , L_SYSDATE
613 , L_USER_ID
614 , L_SYSDATE
615 , L_USER_ID
616 , L_USER_ID
617 , L_REQUEST_ID
618 , L_PROGRAM_APPL_ID
619 , L_PROGRAM_ID
620 , L_SYSDATE
621 --, 'N'
622 , l_org_id_tbl(i)
623 , 1
624 );
625
626 L_NUM_ROWS_INSERTED := L_NUM_ROWS_INSERTED + 1;
627
628 l_parent_territory_id_tbl(i) := l_new_parent_territory_id;
629
630
631 END LOOP; /* Ancestors */
632
633 END IF; -- END OF IF L_related_terr_id IS NOT NULL
634
635 END LOOP;
636
637 END IF; -- end if l_terr_id_tbl.count > 0
638
639 --
640 -- START of code added for bug#2054644
641 --
642 BEGIN
643
644
645 IF (p_source_id <> -1001) THEN
646
647 UPDATE /*+ INDEX (o jtf_terr_values_n1) */
648 jtf_terr_values_all o
649 SET o.first_char = SUBSTR(o.low_value_char, 1, 1)
650 WHERE o.terr_qual_id IN (
651 SELECT /*+ INDEX (i2 jtf_qual_usgs_n1) */
652 i1.terr_qual_id
653 FROM jtf_terr_qual_all i1, jtf_qual_usgs_all i2, jtf_qual_type_usgs_all i3
654 WHERE i1.qual_usg_id = i2.qual_usg_id
655 AND i2.display_type = 'CHAR'
656 AND i2.lov_sql IS NULL
657 AND i2.org_id = -3113
658 AND i2.qual_type_usg_id = i3.qual_type_usg_id
659 AND i3.source_id = p_source_id
660 AND i3.qual_type_id in (SELECT related_id
661 FROM jtf_qual_type_denorm_v
662 WHERE qual_type_id = p_qual_type_id) );
663
664 END IF;
665
666 EXCEPTION
667 WHEN OTHERS THEN
668 NULL;
669 END;
670 --
671 -- END of code added for bug#2054644
672 --
673
674
675 --DBMS_OUTPUT.PUT_LINE('ROWS READ : ' || L_NUM_ROWS_READ);
676 --DBMS_OUTPUT.PUT_LINE('ROWS INSERTED: ' || L_NUM_ROWS_INSERTED);
677
678 EXCEPTION
679
680 WHEN OTHERS THEN
681
682 P_ERROR_CODE := sqlcode;
683 P_ERROR_MSG := sqlerrm;
684 ROLLBACK;
685 --dbms_output.put_line('Value of P_ERROR_MSG='||P_ERROR_MSG);
686
687 END Populate_API;
688
689 END JTF_TERR_DENORM_RULES_PVT;