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