[Home] [Help]
PACKAGE BODY: APPS.JTY_TERR_ENGINE_GEN2_PVT
Source
1 Package Body JTY_TERR_ENGINE_GEN2_PVT AS
2 /* $Header: jtfytseb.pls 120.26.12020000.3 2012/09/24 10:32:08 swpoddar ship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTY_TERR_ENGINE_GEN_PVT
6 -- ---------------------------------------------------
7 -- PURPOSE
8 -- This package is used to generate the real time matching SQL
9 --
10 -- Procedures:
11 -- (see below for specification)
12 --
13 -- NOTES
14 -- This package is available for private use only
15 --
16 -- HISTORY
17 -- 07/11/05 ACHANDA Created
18 --
19 -- End of Comments
20 --
21
22 G_USER_ID NUMBER := FND_GLOBAL.USER_ID();
23 G_SYSDATE DATE := SYSDATE;
24
25 TYPE g_qual_usg_id_tbl_type IS TABLE OF jtf_qual_usgs_all.qual_usg_id%TYPE;
26 TYPE g_cp_tbl_type IS TABLE OF jtf_qual_usgs_all.comparison_operator%TYPE;
27 TYPE g_lvc_id_tbl_type IS TABLE OF jtf_qual_usgs_all.low_value_char_id%TYPE;
28 TYPE g_lvc_tbl_type IS TABLE OF jtf_qual_usgs_all.low_value_char%TYPE;
29 TYPE g_hvc_tbl_type IS TABLE OF jtf_qual_usgs_all.high_value_char%TYPE;
30 TYPE g_lvn_tbl_type IS TABLE OF jtf_qual_usgs_all.low_value_number%TYPE;
31 TYPE g_hvn_tbl_type IS TABLE OF jtf_qual_usgs_all.high_value_number%TYPE;
32 TYPE g_it_id_tbl_type IS TABLE OF jtf_qual_usgs_all.interest_type_id%TYPE;
33 TYPE g_pic_id_tbl_type IS TABLE OF jtf_qual_usgs_all.primary_interest_code_id%TYPE;
34 TYPE g_sic_id_tbl_type IS TABLE OF jtf_qual_usgs_all.secondary_interest_code_id%TYPE;
35 TYPE g_curr_tbl_type IS TABLE OF jtf_qual_usgs_all.currency_code%TYPE;
36 TYPE g_value1_id_tbl_type IS TABLE OF jtf_qual_usgs_all.value1_id%TYPE;
37 TYPE g_value2_id_tbl_type IS TABLE OF jtf_qual_usgs_all.value2_id%TYPE;
38 TYPE g_value3_id_tbl_type IS TABLE OF jtf_qual_usgs_all.value3_id%TYPE;
39 TYPE g_value4_id_tbl_type IS TABLE OF jtf_qual_usgs_all.value4_id%TYPE;
40 TYPE g_fc_tbl_type IS TABLE OF jtf_qual_usgs_all.first_char%TYPE;
41
42 PROCEDURE jty_log(p_log_level IN NUMBER
43 ,p_module IN VARCHAR2
44 ,p_message IN VARCHAR2)
45 IS
46 pragma autonomous_transaction;
47 BEGIN
48 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
49 FND_LOG.string(p_log_level, p_module, p_message);
50 commit;
51 END IF;
52 END;
53
54 PROCEDURE populate_index_info (
55 p_source_id IN NUMBER,
56 p_trans_id IN NUMBER,
57 p_mode IN VARCHAR2,
58 p_qual_usg_id_tbl IN g_qual_usg_id_tbl_type,
59 p_cp_tbl IN g_cp_tbl_type,
60 p_lvc_id_tbl IN g_lvc_id_tbl_type,
61 p_lvc_tbl IN g_lvc_tbl_type,
62 p_hvc_tbl IN g_hvc_tbl_type,
63 p_lvn_tbl IN g_lvn_tbl_type,
64 p_hvn_tbl IN g_hvn_tbl_type,
65 p_it_id_tbl IN g_it_id_tbl_type,
66 p_pic_id_tbl IN g_pic_id_tbl_type,
67 p_sic_id_tbl IN g_sic_id_tbl_type,
68 p_curr_tbl IN g_curr_tbl_type,
69 p_value1_id_tbl IN g_value1_id_tbl_type,
70 p_value2_id_tbl IN g_value2_id_tbl_type,
71 p_value3_id_tbl IN g_value3_id_tbl_type,
72 p_value4_id_tbl IN g_value4_id_tbl_type,
73 p_fc_tbl IN g_fc_tbl_type,
74 errbuf OUT NOCOPY VARCHAR2,
75 retcode OUT NOCOPY VARCHAR2
76 )
77 AS
78 l_no_of_records NUMBER;
79 l_header_seq NUMBER;
80
81 l_qual_type_usg_id NUMBER;
82 l_index_name varchar2(30);
83
84 BEGIN
85 -- debug message
86 jty_log(FND_LOG.LEVEL_PROCEDURE,
87 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.populate_index_info.start',
88 'Start of the procedure JTY_TERR_ENGINE_GEN2_PVT.populate_index_info ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
89
90 IF (p_qual_usg_id_tbl.COUNT > 0) THEN
91
92 SELECT qual_type_usg_id
93 INTO l_qual_type_usg_id
94 FROM jtf_qual_type_usgs_all
95 WHERE source_id = p_source_id
96 AND qual_type_id = p_trans_id;
97
98 FOR i IN p_qual_usg_id_tbl.FIRST .. p_qual_usg_id_tbl.LAST LOOP
99 l_no_of_records := 0;
100
101 SELECT count(*)
102 INTO l_no_of_records
103 FROM jty_terr_values_idx_header
104 WHERE source_id = p_source_id
105 AND qual_usg_id = p_qual_usg_id_tbl(i);
106
107 IF (l_no_of_records = 0) THEN
108
109 SELECT jty_terr_values_idx_header_s.nextval
110 INTO l_header_seq
111 FROM dual;
112
113 SELECT 'JTY_DNM_ATTR_VAL_' || abs(l_qual_type_usg_id) || '_RN' ||
114 (nvl(max(to_number(substr(index_name, instr(index_name, '_RN')+3))), 0) + 1)
115 INTO l_index_name
116 FROM jty_terr_values_idx_header
117 WHERE index_name like 'JTY_DNM_ATTR_VAL_' || abs(l_qual_type_usg_id) || '_RN%';
118
119 INSERT INTO jty_terr_values_idx_header (
120 terr_values_idx_header_id
121 ,source_id
122 ,last_update_date
123 ,last_updated_by
124 ,creation_date
125 ,created_by
126 ,last_update_login
127 ,qual_usg_id
128 ,index_name
129 ,build_index_flag
130 ,delete_flag )
131 VALUES (
132 l_header_seq
133 ,p_source_id
134 ,G_SYSDATE
135 ,G_USER_ID
136 ,G_SYSDATE
137 ,G_USER_ID
138 ,G_USER_ID
139 ,p_qual_usg_id_tbl(i)
140 ,l_index_name
141 ,'Y'
142 ,'N');
143
144 IF (p_cp_tbl(i) IS NOT NULL) THEN
145 INSERT INTO jty_terr_values_idx_details (
146 terr_values_idx_details_id
147 ,terr_values_idx_header_id
148 ,last_update_date
149 ,last_updated_by
150 ,creation_date
151 ,created_by
152 ,last_update_login
153 ,values_col_map
154 ,input_selectivity
155 ,input_ordinal_selectivity )
156 VALUES (
157 jty_terr_values_idx_details_s.nextval
158 ,l_header_seq
159 ,G_SYSDATE
160 ,G_USER_ID
161 ,G_SYSDATE
162 ,G_USER_ID
163 ,G_USER_ID
164 ,p_cp_tbl(i)
165 ,null
166 ,null);
167 END IF;
168
169 IF (p_lvc_id_tbl(i) IS NOT NULL) THEN
170 INSERT INTO jty_terr_values_idx_details (
171 terr_values_idx_details_id
172 ,terr_values_idx_header_id
173 ,last_update_date
174 ,last_updated_by
175 ,creation_date
176 ,created_by
177 ,last_update_login
178 ,values_col_map
179 ,input_selectivity
180 ,input_ordinal_selectivity )
181 VALUES (
182 jty_terr_values_idx_details_s.nextval
183 ,l_header_seq
184 ,G_SYSDATE
185 ,G_USER_ID
186 ,G_SYSDATE
187 ,G_USER_ID
188 ,G_USER_ID
189 ,p_lvc_id_tbl(i)
190 ,null
191 ,null);
192 END IF;
193
194 IF (p_lvc_tbl(i) IS NOT NULL) THEN
195 INSERT INTO jty_terr_values_idx_details (
196 terr_values_idx_details_id
197 ,terr_values_idx_header_id
198 ,last_update_date
199 ,last_updated_by
200 ,creation_date
201 ,created_by
202 ,last_update_login
203 ,values_col_map
204 ,input_selectivity
205 ,input_ordinal_selectivity )
206 VALUES (
207 jty_terr_values_idx_details_s.nextval
208 ,l_header_seq
209 ,G_SYSDATE
210 ,G_USER_ID
211 ,G_SYSDATE
212 ,G_USER_ID
213 ,G_USER_ID
214 ,p_lvc_tbl(i)
215 ,null
216 ,null);
217 END IF;
218
219 IF (p_hvc_tbl(i) IS NOT NULL) THEN
220 INSERT INTO jty_terr_values_idx_details (
221 terr_values_idx_details_id
222 ,terr_values_idx_header_id
223 ,last_update_date
224 ,last_updated_by
225 ,creation_date
226 ,created_by
227 ,last_update_login
228 ,values_col_map
229 ,input_selectivity
230 ,input_ordinal_selectivity )
231 VALUES (
232 jty_terr_values_idx_details_s.nextval
233 ,l_header_seq
234 ,G_SYSDATE
235 ,G_USER_ID
236 ,G_SYSDATE
237 ,G_USER_ID
238 ,G_USER_ID
239 ,p_hvc_tbl(i)
240 ,null
241 ,null);
242 END IF;
243
244 IF (p_lvn_tbl(i) IS NOT NULL) THEN
245 INSERT INTO jty_terr_values_idx_details (
246 terr_values_idx_details_id
247 ,terr_values_idx_header_id
248 ,last_update_date
249 ,last_updated_by
250 ,creation_date
251 ,created_by
252 ,last_update_login
253 ,values_col_map
254 ,input_selectivity
255 ,input_ordinal_selectivity )
256 VALUES (
257 jty_terr_values_idx_details_s.nextval
258 ,l_header_seq
259 ,G_SYSDATE
260 ,G_USER_ID
261 ,G_SYSDATE
262 ,G_USER_ID
263 ,G_USER_ID
264 ,p_lvn_tbl(i)
265 ,null
266 ,null);
267 END IF;
268
269 IF (p_hvn_tbl(i) IS NOT NULL) THEN
270 INSERT INTO jty_terr_values_idx_details (
271 terr_values_idx_details_id
272 ,terr_values_idx_header_id
273 ,last_update_date
274 ,last_updated_by
275 ,creation_date
276 ,created_by
277 ,last_update_login
278 ,values_col_map
279 ,input_selectivity
280 ,input_ordinal_selectivity )
281 VALUES (
282 jty_terr_values_idx_details_s.nextval
283 ,l_header_seq
284 ,G_SYSDATE
285 ,G_USER_ID
286 ,G_SYSDATE
287 ,G_USER_ID
288 ,G_USER_ID
289 ,p_hvn_tbl(i)
290 ,null
291 ,null);
292 END IF;
293
294 IF (p_it_id_tbl(i) IS NOT NULL) THEN
295 INSERT INTO jty_terr_values_idx_details (
296 terr_values_idx_details_id
297 ,terr_values_idx_header_id
298 ,last_update_date
299 ,last_updated_by
300 ,creation_date
301 ,created_by
302 ,last_update_login
303 ,values_col_map
304 ,input_selectivity
305 ,input_ordinal_selectivity )
306 VALUES (
307 jty_terr_values_idx_details_s.nextval
308 ,l_header_seq
309 ,G_SYSDATE
310 ,G_USER_ID
311 ,G_SYSDATE
312 ,G_USER_ID
313 ,G_USER_ID
314 ,p_it_id_tbl(i)
315 ,null
316 ,null);
317 END IF;
318
319 IF (p_pic_id_tbl(i) IS NOT NULL) THEN
320 INSERT INTO jty_terr_values_idx_details (
321 terr_values_idx_details_id
322 ,terr_values_idx_header_id
323 ,last_update_date
324 ,last_updated_by
325 ,creation_date
326 ,created_by
327 ,last_update_login
328 ,values_col_map
329 ,input_selectivity
330 ,input_ordinal_selectivity )
331 VALUES (
332 jty_terr_values_idx_details_s.nextval
333 ,l_header_seq
334 ,G_SYSDATE
335 ,G_USER_ID
336 ,G_SYSDATE
337 ,G_USER_ID
338 ,G_USER_ID
339 ,p_pic_id_tbl(i)
340 ,null
341 ,null);
342 END IF;
343
344 IF (p_sic_id_tbl(i) IS NOT NULL) THEN
345 INSERT INTO jty_terr_values_idx_details (
346 terr_values_idx_details_id
347 ,terr_values_idx_header_id
348 ,last_update_date
349 ,last_updated_by
350 ,creation_date
351 ,created_by
352 ,last_update_login
353 ,values_col_map
354 ,input_selectivity
355 ,input_ordinal_selectivity )
356 VALUES (
357 jty_terr_values_idx_details_s.nextval
358 ,l_header_seq
359 ,G_SYSDATE
360 ,G_USER_ID
361 ,G_SYSDATE
362 ,G_USER_ID
363 ,G_USER_ID
364 ,p_sic_id_tbl(i)
365 ,null
366 ,null);
367 END IF;
368
369 IF (p_curr_tbl(i) IS NOT NULL) THEN
370 INSERT INTO jty_terr_values_idx_details (
371 terr_values_idx_details_id
372 ,terr_values_idx_header_id
373 ,last_update_date
374 ,last_updated_by
375 ,creation_date
376 ,created_by
377 ,last_update_login
378 ,values_col_map
379 ,input_selectivity
380 ,input_ordinal_selectivity )
381 VALUES (
382 jty_terr_values_idx_details_s.nextval
383 ,l_header_seq
384 ,G_SYSDATE
385 ,G_USER_ID
386 ,G_SYSDATE
387 ,G_USER_ID
388 ,G_USER_ID
389 ,p_curr_tbl(i)
390 ,null
391 ,null);
392 END IF;
393
394 IF (p_value1_id_tbl(i) IS NOT NULL) THEN
395 INSERT INTO jty_terr_values_idx_details (
396 terr_values_idx_details_id
397 ,terr_values_idx_header_id
398 ,last_update_date
399 ,last_updated_by
400 ,creation_date
401 ,created_by
402 ,last_update_login
403 ,values_col_map
404 ,input_selectivity
405 ,input_ordinal_selectivity )
406 VALUES (
407 jty_terr_values_idx_details_s.nextval
408 ,l_header_seq
409 ,G_SYSDATE
410 ,G_USER_ID
411 ,G_SYSDATE
412 ,G_USER_ID
413 ,G_USER_ID
414 ,p_value1_id_tbl(i)
415 ,null
416 ,null);
417 END IF;
418
419 IF (p_value2_id_tbl(i) IS NOT NULL) THEN
420 INSERT INTO jty_terr_values_idx_details (
421 terr_values_idx_details_id
422 ,terr_values_idx_header_id
423 ,last_update_date
424 ,last_updated_by
425 ,creation_date
426 ,created_by
427 ,last_update_login
428 ,values_col_map
429 ,input_selectivity
430 ,input_ordinal_selectivity )
431 VALUES (
432 jty_terr_values_idx_details_s.nextval
433 ,l_header_seq
434 ,G_SYSDATE
435 ,G_USER_ID
436 ,G_SYSDATE
437 ,G_USER_ID
438 ,G_USER_ID
439 ,p_value2_id_tbl(i)
440 ,null
441 ,null);
442 END IF;
443
444 IF (p_value3_id_tbl(i) IS NOT NULL) THEN
445 INSERT INTO jty_terr_values_idx_details (
446 terr_values_idx_details_id
447 ,terr_values_idx_header_id
448 ,last_update_date
449 ,last_updated_by
450 ,creation_date
451 ,created_by
452 ,last_update_login
453 ,values_col_map
454 ,input_selectivity
455 ,input_ordinal_selectivity )
456 VALUES (
457 jty_terr_values_idx_details_s.nextval
458 ,l_header_seq
459 ,G_SYSDATE
460 ,G_USER_ID
461 ,G_SYSDATE
462 ,G_USER_ID
463 ,G_USER_ID
464 ,p_value3_id_tbl(i)
465 ,null
466 ,null);
467 END IF;
468
469 IF (p_value4_id_tbl(i) IS NOT NULL) THEN
470 INSERT INTO jty_terr_values_idx_details (
471 terr_values_idx_details_id
472 ,terr_values_idx_header_id
473 ,last_update_date
474 ,last_updated_by
475 ,creation_date
476 ,created_by
477 ,last_update_login
478 ,values_col_map
479 ,input_selectivity
480 ,input_ordinal_selectivity )
481 VALUES (
482 jty_terr_values_idx_details_s.nextval
483 ,l_header_seq
484 ,G_SYSDATE
485 ,G_USER_ID
486 ,G_SYSDATE
487 ,G_USER_ID
488 ,G_USER_ID
489 ,p_value4_id_tbl(i)
490 ,null
491 ,null);
492 END IF;
493
494 IF (p_fc_tbl(i) IS NOT NULL) THEN
495 INSERT INTO jty_terr_values_idx_details (
496 terr_values_idx_details_id
497 ,terr_values_idx_header_id
498 ,last_update_date
499 ,last_updated_by
500 ,creation_date
501 ,created_by
502 ,last_update_login
503 ,values_col_map
504 ,input_selectivity
505 ,input_ordinal_selectivity )
506 VALUES (
507 jty_terr_values_idx_details_s.nextval
508 ,l_header_seq
509 ,G_SYSDATE
510 ,G_USER_ID
511 ,G_SYSDATE
512 ,G_USER_ID
513 ,G_USER_ID
514 ,p_fc_tbl(i)
515 ,null
516 ,null);
517 END IF;
518
519 ELSE
520
521 /* in incremental mode , if the qualifier is alreday present */
522 /* then mark it as being used by active territory */
523 IF (p_mode = 'INCREMENTAL') THEN
524 UPDATE jty_terr_values_idx_header
525 SET delete_flag = 'N'
526 WHERE source_id = p_source_id
527 AND qual_usg_id = p_qual_usg_id_tbl(i);
528 END IF; -- END IF (p_mode = 'INCREMENTAL')
529
530 END IF; -- END IF (l_no_of_records = 0)
531 END LOOP; -- END FOR i IN p_qual_usg_id_tbl.FIRST .. p_qual_usg_id_tbl.LAST
532
533 END IF; -- IF (p_qual_usg_id_tbl.COUNT > 0)
534
535 -- debug message
536 jty_log(FND_LOG.LEVEL_PROCEDURE,
537 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.populate_index_info.end',
538 'End of the procedure JTY_TERR_ENGINE_GEN2_PVT.populate_index_info ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
539
540 retcode := 0;
541 errbuf := null;
542
543 EXCEPTION
544 WHEN OTHERS THEN
545 RETCODE := 2;
546 ERRBUF := SQLCODE || ' : ' || SQLERRM;
547 jty_log(FND_LOG.LEVEL_EXCEPTION,
548 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.populate_index_info.others',
549 substr(errbuf, 1, 4000));
550
551 END populate_index_info;
552
553 PROCEDURE populate_dea_index_info (
554 p_source_id IN NUMBER,
555 p_trans_id IN NUMBER,
556 p_mode IN VARCHAR2,
557 p_qual_usg_id_tbl IN g_qual_usg_id_tbl_type,
558 p_cp_tbl IN g_cp_tbl_type,
559 p_lvc_id_tbl IN g_lvc_id_tbl_type,
560 p_lvc_tbl IN g_lvc_tbl_type,
561 p_hvc_tbl IN g_hvc_tbl_type,
562 p_lvn_tbl IN g_lvn_tbl_type,
563 p_hvn_tbl IN g_hvn_tbl_type,
564 p_it_id_tbl IN g_it_id_tbl_type,
565 p_pic_id_tbl IN g_pic_id_tbl_type,
566 p_sic_id_tbl IN g_sic_id_tbl_type,
567 p_curr_tbl IN g_curr_tbl_type,
568 p_value1_id_tbl IN g_value1_id_tbl_type,
569 p_value2_id_tbl IN g_value2_id_tbl_type,
570 p_value3_id_tbl IN g_value3_id_tbl_type,
571 p_value4_id_tbl IN g_value4_id_tbl_type,
572 p_fc_tbl IN g_fc_tbl_type,
573 errbuf OUT NOCOPY VARCHAR2,
574 retcode OUT NOCOPY VARCHAR2
575 )
576 AS
577 l_no_of_records NUMBER;
578 l_header_seq NUMBER;
579
580 l_qual_type_usg_id NUMBER;
581
582 BEGIN
583 -- debug message
584 jty_log(FND_LOG.LEVEL_PROCEDURE,
585 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.populate_dea_index_info.start',
586 'Start of the procedure JTY_TERR_ENGINE_GEN2_PVT.populate_dea_index_info ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
587
588 IF (p_qual_usg_id_tbl.COUNT > 0) THEN
589
590 SELECT qual_type_usg_id
591 INTO l_qual_type_usg_id
592 FROM jtf_qual_type_usgs_all
593 WHERE source_id = p_source_id
594 AND qual_type_id = p_trans_id;
595
596 FOR i IN p_qual_usg_id_tbl.FIRST .. p_qual_usg_id_tbl.LAST LOOP
597 l_no_of_records := 0;
598
599 SELECT count(*)
600 INTO l_no_of_records
601 FROM jty_dea_values_idx_header
602 WHERE source_id = p_source_id
603 AND qual_usg_id = p_qual_usg_id_tbl(i);
604
605 IF (l_no_of_records = 0) THEN
606
607 SELECT jty_dea_values_idx_header_s.nextval
608 INTO l_header_seq
609 FROM dual;
610
611 INSERT INTO jty_dea_values_idx_header (
612 dea_values_idx_header_id
613 ,source_id
614 ,last_update_date
615 ,last_updated_by
616 ,creation_date
617 ,created_by
618 ,last_update_login
619 ,qual_usg_id
620 ,index_name
621 ,build_index_flag
622 ,delete_flag)
623 VALUES (
624 l_header_seq
625 ,p_source_id
626 ,G_SYSDATE
627 ,G_USER_ID
628 ,G_SYSDATE
629 ,G_USER_ID
630 ,G_USER_ID
631 ,p_qual_usg_id_tbl(i)
632 ,'JTY_DEA_ATTR_VAL_' || abs(l_qual_type_usg_id) || '_RN' || i
633 ,'Y'
634 ,'N');
635
636 IF (p_cp_tbl(i) IS NOT NULL) THEN
637 INSERT INTO jty_dea_values_idx_details (
638 dea_values_idx_details_id
639 ,dea_values_idx_header_id
640 ,last_update_date
641 ,last_updated_by
642 ,creation_date
643 ,created_by
644 ,last_update_login
645 ,values_col_map
646 ,input_selectivity
647 ,input_ordinal_selectivity )
648 VALUES (
649 jty_dea_values_idx_details_s.nextval
650 ,l_header_seq
651 ,G_SYSDATE
652 ,G_USER_ID
653 ,G_SYSDATE
654 ,G_USER_ID
655 ,G_USER_ID
656 ,p_cp_tbl(i)
657 ,null
658 ,null);
659 END IF;
660
661 IF (p_lvc_id_tbl(i) IS NOT NULL) THEN
662 INSERT INTO jty_dea_values_idx_details (
663 dea_values_idx_details_id
664 ,dea_values_idx_header_id
665 ,last_update_date
666 ,last_updated_by
667 ,creation_date
668 ,created_by
669 ,last_update_login
670 ,values_col_map
671 ,input_selectivity
672 ,input_ordinal_selectivity )
673 VALUES (
674 jty_dea_values_idx_details_s.nextval
675 ,l_header_seq
676 ,G_SYSDATE
677 ,G_USER_ID
678 ,G_SYSDATE
679 ,G_USER_ID
680 ,G_USER_ID
681 ,p_lvc_id_tbl(i)
682 ,null
683 ,null);
684 END IF;
685
686 IF (p_lvc_tbl(i) IS NOT NULL) THEN
687 INSERT INTO jty_dea_values_idx_details (
688 dea_values_idx_details_id
689 ,dea_values_idx_header_id
690 ,last_update_date
691 ,last_updated_by
692 ,creation_date
693 ,created_by
694 ,last_update_login
695 ,values_col_map
696 ,input_selectivity
697 ,input_ordinal_selectivity )
698 VALUES (
699 jty_dea_values_idx_details_s.nextval
700 ,l_header_seq
701 ,G_SYSDATE
702 ,G_USER_ID
703 ,G_SYSDATE
704 ,G_USER_ID
705 ,G_USER_ID
706 ,p_lvc_tbl(i)
707 ,null
708 ,null);
709 END IF;
710
711 IF (p_hvc_tbl(i) IS NOT NULL) THEN
712 INSERT INTO jty_dea_values_idx_details (
713 dea_values_idx_details_id
714 ,dea_values_idx_header_id
715 ,last_update_date
716 ,last_updated_by
717 ,creation_date
718 ,created_by
719 ,last_update_login
720 ,values_col_map
721 ,input_selectivity
722 ,input_ordinal_selectivity )
723 VALUES (
724 jty_dea_values_idx_details_s.nextval
725 ,l_header_seq
726 ,G_SYSDATE
727 ,G_USER_ID
728 ,G_SYSDATE
729 ,G_USER_ID
730 ,G_USER_ID
731 ,p_hvc_tbl(i)
732 ,null
733 ,null);
734 END IF;
735
736 IF (p_lvn_tbl(i) IS NOT NULL) THEN
737 INSERT INTO jty_dea_values_idx_details (
738 dea_values_idx_details_id
739 ,dea_values_idx_header_id
740 ,last_update_date
741 ,last_updated_by
742 ,creation_date
743 ,created_by
744 ,last_update_login
745 ,values_col_map
746 ,input_selectivity
747 ,input_ordinal_selectivity )
748 VALUES (
749 jty_dea_values_idx_details_s.nextval
750 ,l_header_seq
751 ,G_SYSDATE
752 ,G_USER_ID
753 ,G_SYSDATE
754 ,G_USER_ID
755 ,G_USER_ID
756 ,p_lvn_tbl(i)
757 ,null
758 ,null);
759 END IF;
760
761 IF (p_hvn_tbl(i) IS NOT NULL) THEN
762 INSERT INTO jty_dea_values_idx_details (
763 dea_values_idx_details_id
764 ,dea_values_idx_header_id
765 ,last_update_date
766 ,last_updated_by
767 ,creation_date
768 ,created_by
769 ,last_update_login
770 ,values_col_map
771 ,input_selectivity
772 ,input_ordinal_selectivity )
773 VALUES (
774 jty_dea_values_idx_details_s.nextval
775 ,l_header_seq
776 ,G_SYSDATE
777 ,G_USER_ID
778 ,G_SYSDATE
779 ,G_USER_ID
780 ,G_USER_ID
781 ,p_hvn_tbl(i)
782 ,null
783 ,null);
784 END IF;
785
786 IF (p_it_id_tbl(i) IS NOT NULL) THEN
787 INSERT INTO jty_dea_values_idx_details (
788 dea_values_idx_details_id
789 ,dea_values_idx_header_id
790 ,last_update_date
791 ,last_updated_by
792 ,creation_date
793 ,created_by
794 ,last_update_login
795 ,values_col_map
796 ,input_selectivity
797 ,input_ordinal_selectivity )
798 VALUES (
799 jty_dea_values_idx_details_s.nextval
800 ,l_header_seq
801 ,G_SYSDATE
802 ,G_USER_ID
803 ,G_SYSDATE
804 ,G_USER_ID
805 ,G_USER_ID
806 ,p_it_id_tbl(i)
807 ,null
808 ,null);
809 END IF;
810
811 IF (p_pic_id_tbl(i) IS NOT NULL) THEN
812 INSERT INTO jty_dea_values_idx_details (
813 dea_values_idx_details_id
814 ,dea_values_idx_header_id
815 ,last_update_date
816 ,last_updated_by
817 ,creation_date
818 ,created_by
819 ,last_update_login
820 ,values_col_map
821 ,input_selectivity
822 ,input_ordinal_selectivity )
823 VALUES (
824 jty_dea_values_idx_details_s.nextval
825 ,l_header_seq
826 ,G_SYSDATE
827 ,G_USER_ID
828 ,G_SYSDATE
829 ,G_USER_ID
830 ,G_USER_ID
831 ,p_pic_id_tbl(i)
832 ,null
833 ,null);
834 END IF;
835
836 IF (p_sic_id_tbl(i) IS NOT NULL) THEN
837 INSERT INTO jty_dea_values_idx_details (
838 dea_values_idx_details_id
839 ,dea_values_idx_header_id
840 ,last_update_date
841 ,last_updated_by
842 ,creation_date
843 ,created_by
844 ,last_update_login
845 ,values_col_map
846 ,input_selectivity
847 ,input_ordinal_selectivity )
848 VALUES (
849 jty_dea_values_idx_details_s.nextval
850 ,l_header_seq
851 ,G_SYSDATE
852 ,G_USER_ID
853 ,G_SYSDATE
854 ,G_USER_ID
855 ,G_USER_ID
856 ,p_sic_id_tbl(i)
857 ,null
858 ,null);
859 END IF;
860
861 IF (p_curr_tbl(i) IS NOT NULL) THEN
862 INSERT INTO jty_dea_values_idx_details (
863 dea_values_idx_details_id
864 ,dea_values_idx_header_id
865 ,last_update_date
866 ,last_updated_by
867 ,creation_date
868 ,created_by
869 ,last_update_login
870 ,values_col_map
871 ,input_selectivity
872 ,input_ordinal_selectivity )
873 VALUES (
874 jty_dea_values_idx_details_s.nextval
875 ,l_header_seq
876 ,G_SYSDATE
877 ,G_USER_ID
878 ,G_SYSDATE
879 ,G_USER_ID
880 ,G_USER_ID
881 ,p_curr_tbl(i)
882 ,null
883 ,null);
884 END IF;
885
886 IF (p_value1_id_tbl(i) IS NOT NULL) THEN
887 INSERT INTO jty_dea_values_idx_details (
888 dea_values_idx_details_id
889 ,dea_values_idx_header_id
890 ,last_update_date
891 ,last_updated_by
892 ,creation_date
893 ,created_by
894 ,last_update_login
895 ,values_col_map
896 ,input_selectivity
897 ,input_ordinal_selectivity )
898 VALUES (
899 jty_dea_values_idx_details_s.nextval
900 ,l_header_seq
901 ,G_SYSDATE
902 ,G_USER_ID
903 ,G_SYSDATE
904 ,G_USER_ID
905 ,G_USER_ID
906 ,p_value1_id_tbl(i)
907 ,null
908 ,null);
909 END IF;
910
911 IF (p_value2_id_tbl(i) IS NOT NULL) THEN
912 INSERT INTO jty_dea_values_idx_details (
913 dea_values_idx_details_id
914 ,dea_values_idx_header_id
915 ,last_update_date
916 ,last_updated_by
917 ,creation_date
918 ,created_by
919 ,last_update_login
920 ,values_col_map
921 ,input_selectivity
922 ,input_ordinal_selectivity )
923 VALUES (
924 jty_dea_values_idx_details_s.nextval
925 ,l_header_seq
926 ,G_SYSDATE
927 ,G_USER_ID
928 ,G_SYSDATE
929 ,G_USER_ID
930 ,G_USER_ID
931 ,p_value2_id_tbl(i)
932 ,null
933 ,null);
934 END IF;
935
936 IF (p_value3_id_tbl(i) IS NOT NULL) THEN
937 INSERT INTO jty_dea_values_idx_details (
938 dea_values_idx_details_id
939 ,dea_values_idx_header_id
940 ,last_update_date
941 ,last_updated_by
942 ,creation_date
943 ,created_by
944 ,last_update_login
945 ,values_col_map
946 ,input_selectivity
947 ,input_ordinal_selectivity )
948 VALUES (
949 jty_dea_values_idx_details_s.nextval
950 ,l_header_seq
951 ,G_SYSDATE
952 ,G_USER_ID
953 ,G_SYSDATE
954 ,G_USER_ID
955 ,G_USER_ID
956 ,p_value3_id_tbl(i)
957 ,null
958 ,null);
959 END IF;
960
961 IF (p_value4_id_tbl(i) IS NOT NULL) THEN
962 INSERT INTO jty_dea_values_idx_details (
963 dea_values_idx_details_id
964 ,dea_values_idx_header_id
965 ,last_update_date
966 ,last_updated_by
967 ,creation_date
968 ,created_by
969 ,last_update_login
970 ,values_col_map
971 ,input_selectivity
972 ,input_ordinal_selectivity )
973 VALUES (
974 jty_dea_values_idx_details_s.nextval
975 ,l_header_seq
976 ,G_SYSDATE
977 ,G_USER_ID
978 ,G_SYSDATE
979 ,G_USER_ID
980 ,G_USER_ID
981 ,p_value4_id_tbl(i)
982 ,null
983 ,null);
984 END IF;
985
986 IF (p_fc_tbl(i) IS NOT NULL) THEN
987 INSERT INTO jty_dea_values_idx_details (
988 dea_values_idx_details_id
989 ,dea_values_idx_header_id
990 ,last_update_date
991 ,last_updated_by
992 ,creation_date
993 ,created_by
994 ,last_update_login
995 ,values_col_map
996 ,input_selectivity
997 ,input_ordinal_selectivity )
998 VALUES (
999 jty_dea_values_idx_details_s.nextval
1000 ,l_header_seq
1001 ,G_SYSDATE
1002 ,G_USER_ID
1003 ,G_SYSDATE
1004 ,G_USER_ID
1005 ,G_USER_ID
1006 ,p_fc_tbl(i)
1007 ,null
1008 ,null);
1009 END IF;
1010 ELSE
1011
1012 /* in incremental mode , if the qualifier is alreday present */
1013 /* then mark it as being used by active territory */
1014 IF (p_mode = 'DEA INCREMENTAL') THEN
1015 UPDATE jty_dea_values_idx_header
1016 SET delete_flag = 'N'
1017 WHERE source_id = p_source_id
1018 AND qual_usg_id = p_qual_usg_id_tbl(i);
1019 END IF; -- END IF (p_mode = 'DEA INCREMENTAL')
1020 END IF; -- IF (l_no_of_records = 0)
1021 END LOOP; -- FOR i IN p_qual_usg_id_tbl.FIRST .. p_qual_usg_id_tbl.LAST
1022
1023 END IF; -- IF (p_qual_usg_id_tbl.COUNT > 0)
1024
1025 -- debug message
1026 jty_log(FND_LOG.LEVEL_PROCEDURE,
1027 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.populate_dea_index_info.end',
1028 'End of the procedure JTY_TERR_ENGINE_GEN2_PVT.populate_dea_index_info ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1029
1030 retcode := 0;
1031 errbuf := null;
1032
1033 EXCEPTION
1034 WHEN OTHERS THEN
1035 RETCODE := 2;
1036 ERRBUF := SQLCODE || ' : ' || SQLERRM;
1037 jty_log(FND_LOG.LEVEL_EXCEPTION,
1038 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.populate_dea_index_info.others',
1039 substr(errbuf, 1, 4000));
1040
1041 END populate_dea_index_info;
1042
1043 /* this procedure generates the real time matching SQL for a transaction type */
1044 PROCEDURE gen_terr_rules_recurse (
1045 p_source_id IN NUMBER,
1046 p_trans_id IN NUMBER,
1047 p_mode IN VARCHAR2,
1048 p_start_date IN DATE,
1049 p_end_date IN DATE,
1050 errbuf OUT NOCOPY VARCHAR2,
1051 retcode OUT NOCOPY VARCHAR2
1052 )
1053 AS
1054 CURSOR c_terr_qual( lp_source_id NUMBER
1055 ,lp_trans_id NUMBER
1056 ,lp_start_date DATE
1057 ,lp_end_date DATE) IS
1058 SELECT jqu.qual_usg_id
1059 ,jqu.real_time_select
1060 ,jqu.real_time_from
1061 ,jqu.real_time_where
1062 ,jqu.comparison_operator
1063 ,jqu.low_value_char_id
1064 ,jqu.low_value_char
1065 ,jqu.high_value_char
1066 ,jqu.low_value_number
1067 ,jqu.high_value_number
1068 ,jqu.interest_type_id
1069 ,jqu.primary_interest_code_id
1070 ,jqu.secondary_interest_code_id
1071 ,jqu.currency_code
1072 ,jqu.value1_id
1073 ,jqu.value2_id
1074 ,jqu.value3_id
1075 ,jqu.value4_id
1076 ,jqu.first_char
1077 FROM jtf_qual_usgs_all jqu
1078 ,jtf_qual_type_usgs jqtu
1079 ,jtf_qual_type_denorm_v v
1080 WHERE jqu.org_id = -3113
1081 AND jqu.qual_type_usg_id = jqtu.qual_type_usg_id
1082 AND jqtu.source_id = lp_source_id
1083 AND jqtu.qual_type_id = v.related_id
1084 AND jqu.real_time_select IS NOT NULL
1085 AND v.qual_type_id = lp_trans_id
1086 AND EXISTS ( SELECT jtq.terr_id
1087 FROM jtf_terr_qtype_usgs_all jtqu
1088 ,jtf_terr_all jt
1089 ,jtf_terr_qual_all jtq
1090 ,jtf_qual_type_usgs jqtu
1091 WHERE jt.end_date_active >= lp_start_date
1092 AND jt.start_date_active <= lp_end_date
1093 AND jtqu.terr_id = jt.terr_id
1094 AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
1095 AND jqtu.qual_type_id = lp_trans_id
1096 AND jtqu.terr_id = jtq.terr_id
1097 AND jtq.qual_usg_id = jqu.qual_usg_id);
1098
1099 CURSOR c_trans_details( lp_source_id NUMBER
1100 ,lp_trans_id NUMBER) IS
1101 SELECT program_name
1102 ,real_time_trans_table_name
1103 FROM jty_trans_usg_pgm_details
1104 WHERE source_id = lp_source_id
1105 AND trans_type_id = lp_trans_id;
1106
1107 TYPE l_rts_tbl_type IS TABLE OF jtf_qual_usgs_all.real_time_select%TYPE;
1108 TYPE l_rtf_tbl_type IS TABLE OF jtf_qual_usgs_all.real_time_from%TYPE;
1109 TYPE l_rtw_tbl_type IS TABLE OF jtf_qual_usgs_all.real_time_where%TYPE;
1110
1111 TYPE l_pgm_name_tbl_type IS TABLE OF jty_trans_usg_pgm_details.program_name%TYPE;
1112 TYPE l_trans_name_tbl_type IS TABLE OF jty_trans_usg_pgm_details.real_time_trans_table_name%TYPE;
1113
1114 l_qual_usg_id_tbl g_qual_usg_id_tbl_type;
1115 l_rts_tbl l_rts_tbl_type;
1116 l_rtf_tbl l_rtf_tbl_type;
1117 l_rtw_tbl l_rtw_tbl_type;
1118 l_cp_tbl g_cp_tbl_type;
1119 l_lvc_id_tbl g_lvc_id_tbl_type;
1120 l_lvc_tbl g_lvc_tbl_type;
1121 l_hvc_tbl g_hvc_tbl_type;
1122 l_lvn_tbl g_lvn_tbl_type;
1123 l_hvn_tbl g_hvn_tbl_type;
1124 l_it_id_tbl g_it_id_tbl_type;
1125 l_pic_id_tbl g_pic_id_tbl_type;
1126 l_sic_id_tbl g_sic_id_tbl_type;
1127 l_curr_tbl g_curr_tbl_type;
1128 l_value1_id_tbl g_value1_id_tbl_type;
1129 l_value2_id_tbl g_value2_id_tbl_type;
1130 l_value3_id_tbl g_value3_id_tbl_type;
1131 l_value4_id_tbl g_value4_id_tbl_type;
1132 l_fc_tbl g_fc_tbl_type;
1133
1134 l_pgm_name_tbl l_pgm_name_tbl_type;
1135 l_trans_name_tbl l_trans_name_tbl_type;
1136
1137 l_table_name VARCHAR2(30);
1138 l_insert_stmt CLOB; --VARCHAR2(32767);
1139 l_qual_rules CLOB; --VARCHAR2(32767);
1140 l_group_by CLOB; --VARCHAR2(32767);
1141 l_counter NUMBER;
1142 l_realtime_sql CLOB;
1143
1144 l_newline VARCHAR2(2);
1145
1146 BEGIN
1147 -- debug message
1148 jty_log(FND_LOG.LEVEL_PROCEDURE,
1149 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_terr_rules_recurse.start',
1150 'Start of the procedure JTY_TERR_ENGINE_GEN2_PVT.gen_terr_rules_recurse ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1151
1152 l_newline := FND_GLOBAL.Local_Chr(10); /* newline character */
1153
1154 IF ((p_mode = 'DATE EFFECTIVE') or (p_mode = 'DEA INCREMENTAL')) THEN
1155 SELECT denorm_dea_value_table_name
1156 INTO l_table_name
1157 FROM jtf_sources_all
1158 WHERE source_id = p_source_id;
1159 ELSE
1160 SELECT denorm_value_table_name
1161 INTO l_table_name
1162 FROM jtf_sources_all
1163 WHERE source_id = p_source_id;
1164 END IF; /* end IF (p_mode = 'DATE EFFECTIVE') */
1165
1166 /* get all the qualifiers and its real time rules, used by the active territories */
1167 OPEN c_terr_qual(p_source_id, p_trans_id, p_start_date, p_end_date);
1168 FETCH c_terr_qual BULK COLLECT INTO
1169 l_qual_usg_id_tbl
1170 ,l_rts_tbl
1171 ,l_rtf_tbl
1172 ,l_rtw_tbl
1173 ,l_cp_tbl
1174 ,l_lvc_id_tbl
1175 ,l_lvc_tbl
1176 ,l_hvc_tbl
1177 ,l_lvn_tbl
1178 ,l_hvn_tbl
1179 ,l_it_id_tbl
1180 ,l_pic_id_tbl
1181 ,l_sic_id_tbl
1182 ,l_curr_tbl
1183 ,l_value1_id_tbl
1184 ,l_value2_id_tbl
1185 ,l_value3_id_tbl
1186 ,l_value4_id_tbl
1187 ,l_fc_tbl;
1188 CLOSE c_terr_qual;
1189
1190 -- debug message
1191 jty_log(FND_LOG.LEVEL_STATEMENT,
1192 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_terr_rules_recurse.num_qual',
1193 'Number of qualifiers used by valid territories : ' || l_qual_usg_id_tbl.COUNT);
1194
1195 /* get all the program name and its corresponding real time trans table for the usage and txn type */
1196 OPEN c_trans_details(p_source_id, p_trans_id);
1197 FETCH c_trans_details BULK COLLECT INTO
1198 l_pgm_name_tbl
1199 ,l_trans_name_tbl;
1200 CLOSE c_trans_details;
1201
1202 -- debug message
1203 jty_log(FND_LOG.LEVEL_STATEMENT,
1204 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_terr_rules_recurse.num_program',
1205 'Number of programs for the usage and transaction type : ' || l_pgm_name_tbl.COUNT);
1206
1207 /* generic insert statement */
1208 l_insert_stmt :=
1209 'INSERT INTO jtf_terr_results_GT_MT jtr ' ||
1210 '( ' ||
1211 ' trans_id ' ||
1212 ' ,source_id ' ||
1213 ' ,qual_type_id ' ||
1214 ' ,trans_object_id ' ||
1215 ' ,trans_detail_object_id ' ||
1216 ' ,txn_date ' ||
1217 ' ,terr_id ' ||
1218 ' ,absolute_rank ' ||
1219 ' ,top_level_terr_id ' ||
1220 ' ,num_winners ' ||
1221 ' ,worker_id ' ||
1222 ') ' ||
1223 'SELECT ' ||
1224 ' ' || p_trans_id || ' ' ||
1225 ' ,' || p_source_id || ' ' ||
1226 ' ,' || p_trans_id || ' ' ||
1227 ' ,ILV.trans_object_id ' ||
1228 ' ,ILV.trans_detail_object_id ' ||
1229 ' ,ILV.txn_date ' ||
1230 ' ,ILV.terr_id ' ||
1231 ' ,ILV.absolute_rank ' ||
1232 ' ,ILV.top_level_terr_id ' ||
1233 ' ,ILV.num_winners ' ||
1234 ' ,1 ' ||
1235 'FROM ( ';
1236
1237 /* generic group by clause */
1238 l_group_by :=
1239 ' ) ILV ' ||
1240 'GROUP BY ilv.trans_object_id, ilv.trans_detail_object_id, ilv.txn_date, ' ||
1241 'ilv.terr_id, ilv.absolute_rank, ilv.top_level_terr_id, ilv.num_winners ' ||
1242 'HAVING (ILV.terr_id, COUNT(*)) IN ( ' ||
1243 ' SELECT ' ||
1244 ' jua.terr_id ' ||
1245 ' ,jua.num_qual ' ||
1246 ' FROM jtf_terr_qtype_usgs_all jua ' ||
1247 ' ,jtf_qual_type_usgs_all jqa ' ||
1248 ' WHERE jqa.source_id = ' || p_source_id || ' ' ||
1249 ' AND jqa.qual_type_id = ' || p_trans_id || ' ' ||
1250 ' AND jua.qual_type_usg_id = jqa.qual_type_usg_id ' ||
1251 ' AND jua.terr_id = ilv.terr_id ) ';
1252
1253 IF (l_pgm_name_tbl.COUNT > 0) THEN
1254
1255 /* repeat for each program name */
1256 FOR i IN l_pgm_name_tbl.FIRST .. l_pgm_name_tbl.LAST LOOP
1257 IF (l_qual_usg_id_tbl.COUNT > 0) THEN
1258
1259 l_counter := 1;
1260 l_qual_rules := null;
1261
1262 /* repeat for each qualifier */
1263 FOR j IN l_qual_usg_id_tbl.FIRST .. l_qual_usg_id_tbl.LAST LOOP
1264
1265 -- City -1040
1266 -- Postal Code -1041
1267 -- State -1042
1268 -- County -1044
1269 -- Request Type -1048
1270 -- Inventory Item -1096
1271 -- Code changes done to the above qualifiers . Bug 7368422.
1272
1273
1274 -- Country -1038
1275 -- Task Status -1061
1276 -- Task Type -1060
1277 -- Area Code -1043
1278 -- Request Creation Channel -1095
1279 -- Problem Code -1051
1280 -- Request Urgency -1050
1281 -- Customer Name -1037
1282 -- Code changes done to the above qualifiers . Bug 8317860.
1283
1284 -- Product Category/ Product -1210
1285 -- Customer Name Range -1045
1286 -- Code changes done to the above qualifiers. Bug 9032760
1287
1288 -- System Id -1206
1289 -- SR Language -1213
1290 -- Account Code -1039
1291 -- Product -1218
1292
1293 -- Code changes done to the following qualifiers for bug 13917233,14539146
1294 -- Time of Day -1744
1295 -- Day of Week -1734
1296 -- Province -1046
1297
1298
1299 -- debug message
1300 jty_log(FND_LOG.LEVEL_STATEMENT,
1301 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_terr_rules_recurse loop qualifier - length', length(l_qual_rules));
1302
1303 IF l_qual_usg_id_tbl(j) in ( '-1040','-1041','-1042','-1044','-1048','-1744','-1734','-1096','-1039','-1213', '-1218',
1304 '-1037','-1038','-1043','-1046','-1050','-1051','-1060','-1061','-1095', '-1210', '-1045', '-1206') THEN
1305
1306 IF (l_counter > 1) THEN
1307 l_qual_rules := l_qual_rules || l_newline || ' UNION ALL ' || l_newline;
1308 END IF;
1309 l_qual_rules := l_qual_rules || l_rts_tbl(j) || l_newline || ' FROM ' || l_trans_name_tbl(i) || ' A ';
1310 /* add the denorm value table name */
1311 l_qual_rules := l_qual_rules || l_newline || ' , jtf_terr_values_all jtv, jtf_terr_denorm_rules_all B, jtf_terr_qual_all jtq ';
1312 IF (l_rtf_tbl(j) IS NOT NULL) THEN
1313 l_qual_rules := l_qual_rules || l_newline || ' ,' || l_rtf_tbl(j) || ' ';
1314 END IF;
1315 l_qual_rules := l_qual_rules || l_newline || l_rtw_tbl(j) || l_newline || ' ';
1316
1317 ELSE
1318
1319 IF (l_counter > 1) THEN
1320 l_qual_rules := l_qual_rules || l_newline || ' UNION ALL ' || l_newline;
1321 END IF;
1322 l_qual_rules := l_qual_rules || l_rts_tbl(j) || l_newline || ' FROM ' || l_trans_name_tbl(i) || ' A ';
1323 /* add the denorm value table name */
1324 l_qual_rules := l_qual_rules || l_newline || ' ,' || l_table_name || ' B ';
1325 IF (l_rtf_tbl(j) IS NOT NULL) THEN
1326 l_qual_rules := l_qual_rules || l_newline || ' ,' || l_rtf_tbl(j) || ' ';
1327 END IF;
1328 l_qual_rules := l_qual_rules || l_newline || l_rtw_tbl(j) || l_newline || ' AND B.trans_type_id = ' || to_char(p_trans_id);
1329
1330 END IF;
1331 l_counter := l_counter + 1;
1332
1333 END LOOP; /* end loop FOR j IN l_qual_usg_id_tbl.FIRST .. l_qual_usg_id_tbl.LAST */
1334 END IF; /* end IF (l_qual_usg_id_tbl.COUNT > 0) */
1335
1336 l_realtime_sql := l_insert_stmt || l_qual_rules || l_group_by;
1337
1338 /* if mode is date effective, update the column real_time_match_dea_sql */
1339 IF ((p_mode = 'DATE EFFECTIVE') or (p_mode = 'DEA INCREMENTAL')) THEN
1340 UPDATE jty_trans_usg_pgm_details
1341 SET real_time_match_dea_sql = l_realtime_sql,
1342 last_update_date = sysdate
1343 WHERE source_id = p_source_id
1344 AND trans_type_id = p_trans_id
1345 AND program_name = l_pgm_name_tbl(i);
1346 ELSE
1347 /* if mode is total or incremental, update the column real_time_match_sql */
1348 UPDATE jty_trans_usg_pgm_details
1349 SET real_time_match_sql = l_realtime_sql,
1350 last_update_date = sysdate
1351 WHERE source_id = p_source_id
1352 AND trans_type_id = p_trans_id
1353 AND program_name = l_pgm_name_tbl(i);
1354 END IF;
1355
1356 END LOOP; /* end loop FOR i IN l_pgm_name_tbl.FIRST .. l_pgm_name_tbl.LAST */
1357 END IF; /* end IF (l_pgm_name_tbl.COUNT > 0) */
1358
1359
1360 /* Populate the index informations for all the qualifiers */
1361 IF ((p_mode = 'TOTAL') OR (p_mode = 'INCREMENTAL')) THEN
1362 populate_index_info (
1363 p_source_id => p_source_id,
1364 p_trans_id => p_trans_id,
1365 p_mode => p_mode,
1366 p_qual_usg_id_tbl => l_qual_usg_id_tbl,
1367 p_cp_tbl => l_cp_tbl,
1368 p_lvc_id_tbl => l_lvc_id_tbl,
1369 p_lvc_tbl => l_lvc_tbl,
1370 p_hvc_tbl => l_hvc_tbl,
1371 p_lvn_tbl => l_lvn_tbl,
1372 p_hvn_tbl => l_hvn_tbl,
1373 p_it_id_tbl => l_it_id_tbl,
1374 p_pic_id_tbl => l_pic_id_tbl,
1375 p_sic_id_tbl => l_sic_id_tbl,
1376 p_curr_tbl => l_curr_tbl,
1377 p_value1_id_tbl => l_value1_id_tbl,
1378 p_value2_id_tbl => l_value2_id_tbl,
1379 p_value3_id_tbl => l_value3_id_tbl,
1380 p_value4_id_tbl => l_value4_id_tbl,
1381 p_fc_tbl => l_fc_tbl,
1382 errbuf => errbuf,
1383 retcode => retcode
1384 );
1385 ELSIF ((p_mode = 'DATE EFFECTIVE') or (p_mode = 'DEA INCREMENTAL')) THEN
1386 populate_dea_index_info (
1387 p_source_id => p_source_id,
1388 p_trans_id => p_trans_id,
1389 p_mode => p_mode,
1390 p_qual_usg_id_tbl => l_qual_usg_id_tbl,
1391 p_cp_tbl => l_cp_tbl,
1392 p_lvc_id_tbl => l_lvc_id_tbl,
1393 p_lvc_tbl => l_lvc_tbl,
1394 p_hvc_tbl => l_hvc_tbl,
1395 p_lvn_tbl => l_lvn_tbl,
1396 p_hvn_tbl => l_hvn_tbl,
1397 p_it_id_tbl => l_it_id_tbl,
1398 p_pic_id_tbl => l_pic_id_tbl,
1399 p_sic_id_tbl => l_sic_id_tbl,
1400 p_curr_tbl => l_curr_tbl,
1401 p_value1_id_tbl => l_value1_id_tbl,
1402 p_value2_id_tbl => l_value2_id_tbl,
1403 p_value3_id_tbl => l_value3_id_tbl,
1404 p_value4_id_tbl => l_value4_id_tbl,
1405 p_fc_tbl => l_fc_tbl,
1406 errbuf => errbuf,
1407 retcode => retcode
1408 );
1409 END IF;
1410
1411 IF (retcode <> 0) THEN
1412 -- debug message
1413 jty_log(FND_LOG.LEVEL_EXCEPTION,
1414 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_terr_rules_recurse.populate_index_info',
1415 'populate_index_info API has failed');
1416
1417 RAISE FND_API.G_EXC_ERROR;
1418 END IF;
1419
1420 -- debug message
1421 jty_log(FND_LOG.LEVEL_PROCEDURE,
1422 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_terr_rules_recurse.end',
1423 'End of the procedure JTY_TERR_ENGINE_GEN2_PVT.gen_terr_rules_recurse ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1424
1425 EXCEPTION
1426 WHEN FND_API.G_EXC_ERROR THEN
1427 jty_log(FND_LOG.LEVEL_EXCEPTION,
1428 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_terr_rules_recurse.g_exc_error',
1429 'API JTY_TERR_ENGINE_GEN2_PVT.gen_terr_rules_recurse has failed with FND_API.G_EXC_ERROR exception');
1430
1431 WHEN OTHERS THEN
1432 RETCODE := 2;
1433 ERRBUF := SQLCODE || ' : ' || SQLERRM;
1434 jty_log(FND_LOG.LEVEL_EXCEPTION,
1435 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_terr_rules_recurse.others',
1436 substr(errbuf, 1, 4000));
1437
1438 END gen_terr_rules_recurse;
1439
1440
1441 /* entry point of this package to generate the real time matching SQL */
1442 PROCEDURE gen_real_time_sql (
1443 p_source_id IN NUMBER,
1444 p_trans_id IN NUMBER,
1445 p_mode IN VARCHAR2,
1446 p_start_date IN DATE,
1447 p_end_date IN DATE,
1448 errbuf OUT NOCOPY VARCHAR2,
1449 retcode OUT NOCOPY VARCHAR2
1450 )
1451 AS
1452 l_num_of_terr NUMBER;
1453 l_start_date DATE;
1454 l_end_date DATE;
1455
1456 BEGIN
1457 -- debug message
1458 jty_log(FND_LOG.LEVEL_PROCEDURE,
1459 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_real_time_sql.start',
1460 'Start of the procedure JTY_TERR_ENGINE_GEN2_PVT.gen_real_time_sql ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1461
1462 -- debug message
1463 jty_log(FND_LOG.LEVEL_STATEMENT,
1464 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_real_time_sql.parameters',
1465 'p_source_id : ' || p_source_id || ' p_trans_id : ' || p_trans_id || ' p_mode : ' || p_mode ||
1466 ' p_start_date : ' || p_start_date || ' p_end_date : ' || p_end_date);
1467
1468 /* if mode is date effective consider the territories active between p_start_date and p_end_date */
1469 /* else if mode is total or incremental consider the territories active as of sysdate */
1470 IF (p_mode = 'DATE EFFECTIVE') THEN
1471 l_start_date := p_start_date;
1472 l_end_date := p_end_date;
1473 ELSIF (p_mode = 'DEA INCREMENTAL') THEN
1474 l_start_date := p_start_date;
1475 l_end_date := p_end_date;
1476 ELSE
1477 l_start_date := sysdate;
1478 l_end_date := sysdate;
1479 END IF;
1480
1481 /* Check for the number of territories for this usage and transaction type */
1482 IF (p_mode = 'DATE EFFECTIVE' or p_mode = 'DEA INCREMENTAL') THEN
1483 SELECT COUNT (jt1.terr_id)
1484 INTO l_num_of_terr
1485 FROM jtf_terr_qtype_usgs_all jtqu
1486 , jtf_terr_all jt1
1487 , jtf_qual_type_usgs jqtu
1488 WHERE jtqu.terr_id = jt1.terr_id
1489 AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
1490 AND jqtu.qual_type_id = p_trans_id
1491 AND jqtu.source_id = p_source_id
1492 AND jt1.end_date_active >= l_start_date
1493 AND jt1.start_date_active <= l_end_date
1494 AND EXISTS (
1495 SELECT 1
1496 FROM jtf_terr_rsc_all jtr,
1497 jtf_terr_rsc_access_all jtra,
1498 jtf_qual_types_all jqta
1499 WHERE jtr.terr_id = jt1.terr_id
1500 AND jtr.end_date_active >= l_start_date
1501 AND jtr.start_date_active <= l_end_date
1502 AND jtr.resource_type <> 'RS_ROLE'
1503 AND jtr.terr_rsc_id = jtra.terr_rsc_id
1504 AND jtra.access_type = jqta.name
1505 AND jqta.qual_type_id = p_trans_id
1506 AND jtra.trans_access_code <> 'NONE')
1507 AND EXISTS (
1508 SELECT 1
1509 FROM jty_denorm_dea_rules_all jtdr
1510 WHERE jtdr.terr_id = jt1.terr_id
1511 AND jtdr.terr_id = jtdr.related_terr_id)
1512 AND jqtu.qual_type_id <> -1001
1513 AND rownum < 2;
1514 ELSE
1515 SELECT COUNT(jt1.terr_id)
1516 INTO l_num_of_terr
1517 FROM jtf_terr_qtype_usgs_all jtqu
1518 , jtf_terr_all jt1
1519 , jtf_qual_type_usgs jqtu
1520 WHERE jtqu.terr_id = jt1.terr_id
1521 AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
1522 AND jqtu.qual_type_id = p_trans_id
1523 AND jqtu.source_id = p_source_id
1524 AND jt1.end_date_active >= l_start_date
1525 AND jt1.start_date_active <= l_end_date
1526 AND EXISTS (
1527 SELECT 1
1528 FROM jtf_terr_rsc_all jtr,
1529 jtf_terr_rsc_access_all jtra,
1530 jtf_qual_types_all jqta
1531 WHERE jtr.terr_id = jt1.terr_id
1532 AND jtr.end_date_active >= l_start_date
1533 AND jtr.start_date_active <= l_end_date
1534 AND jtr.resource_type <> 'RS_ROLE'
1535 AND jtr.terr_rsc_id = jtra.terr_rsc_id
1536 AND jtra.access_type = jqta.name
1537 AND jqta.qual_type_id = p_trans_id
1538 AND jtra.trans_access_code <> 'NONE')
1539 AND EXISTS (
1540 SELECT 1
1541 FROM jtf_terr_denorm_rules_all jtdr
1542 WHERE jtdr.terr_id = jt1.terr_id
1543 AND jtdr.terr_id = jtdr.related_terr_id)
1544 AND jqtu.qual_type_id <> -1001
1545 AND rownum < 2;
1546 END IF;
1547
1548 -- debug message
1549 jty_log(FND_LOG.LEVEL_STATEMENT,
1550 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_real_time_sql.num_terr',
1551 'Number of territories for this usage and transaction type : ' || l_num_of_terr);
1552
1553 /* territories exist for this USAGE/TRANSACTION TYPE combination */
1554 IF (l_num_of_terr > 0) THEN
1555
1556 /* generate real time matching sql */
1557 gen_terr_rules_recurse (
1558 p_source_id => p_source_id,
1559 p_trans_id => p_trans_id,
1560 p_mode => p_mode,
1561 p_start_date => l_start_date,
1562 p_end_date => l_end_date,
1563 errbuf => errbuf,
1564 retcode => retcode);
1565
1566 IF (retcode <> 0) THEN
1567 -- debug message
1568 jty_log(FND_LOG.LEVEL_EXCEPTION,
1569 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_real_time_sql.gen_terr_rules_recurse',
1570 'gen_terr_rules_recurse API has failed');
1571
1572 RAISE FND_API.G_EXC_ERROR;
1573 END IF;
1574
1575 ELSE
1576 -- debug message
1577 jty_log(FND_LOG.LEVEL_EXCEPTION,
1578 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_real_time_sql.no_real_time_sql',
1579 'No valid territories for this usage and transaction type');
1580
1581 END IF; /* end if(num_of_terr > 0) */
1582
1583 -- debug message
1584 jty_log(FND_LOG.LEVEL_PROCEDURE,
1585 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_real_time_sql.end',
1586 'End of the procedure JTY_TERR_ENGINE_GEN2_PVT.gen_real_time_sql ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1587
1588 retcode := 0;
1589 errbuf := null;
1590
1591 EXCEPTION
1592 WHEN FND_API.G_EXC_ERROR THEN
1593 jty_log(FND_LOG.LEVEL_EXCEPTION,
1594 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_real_time_sql.g_exc_error',
1595 'API JTY_TERR_ENGINE_GEN2_PVT.gen_real_time_sql has failed with FND_API.G_EXC_ERROR exception');
1596
1597 WHEN OTHERS THEN
1598 RETCODE := 2;
1599 ERRBUF := SQLCODE || ' : ' || SQLERRM;
1600 jty_log(FND_LOG.LEVEL_EXCEPTION,
1601 'jtf.plsql.JTY_TERR_ENGINE_GEN2_PVT.gen_real_time_sql.others',
1602 substr(errbuf, 1, 4000));
1603
1604 END gen_real_time_sql;
1605
1606
1607 END JTY_TERR_ENGINE_GEN2_PVT;