[Home] [Help]
PACKAGE BODY: APPS.JTY_TAE_CONTROL_PVT
Source
1 PACKAGE BODY JTY_TAE_CONTROL_PVT AS
2 /* $Header: jtfyaecb.pls 120.4 2006/05/15 23:04:06 solin noship $ */
3 -- ---------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTY_TAE_CONTROL_PVT
7 -- ---------------------------------------------------
8 -- PURPOSE
9 --
10 -- Classify territories before mass assignment
11 --
12 -- Procedures:
13 -- (see below for specification)
14 --
15 -- NOTES
16 -- This package is for public use
17 --
18 -- HISTORY
19 -- 07/10/2005 ACHANDA Created.
20 --
21
22 G_USER_ID NUMBER := FND_GLOBAL.USER_ID();
23 G_SYSDATE DATE := SYSDATE;
24
25 PROCEDURE delete_combinations
26 ( p_source_id IN NUMBER,
27 p_trans_id IN NUMBER,
28 p_mode IN VARCHAR2,
29 x_Return_Status OUT NOCOPY VARCHAR2,
30 x_Msg_Count OUT NOCOPY NUMBER,
31 x_Msg_Data OUT NOCOPY VARCHAR2,
32 ERRBUF OUT NOCOPY VARCHAR2,
33 RETCODE OUT NOCOPY VARCHAR2 )
34 IS
35
36 TYPE l_qual_prd_id_tbl_type IS TABLE OF jtf_tae_qual_products.qual_product_id%TYPE;
37 TYPE l_rel_prd_tbl_type IS TABLE OF jtf_tae_qual_products.relation_product%TYPE;
38
39 l_qual_prd_id_tbl l_qual_prd_id_tbl_type;
40 l_rel_prd_tbl l_rel_prd_tbl_type;
41 BEGIN
42 -- debug message
43 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
44 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
45 'jtf.plsql.JTY_TAE_CONTROL_PVT.delete_combinations.begin',
46 'Start of the procedure JTY_TAE_CONTROL_PVT.delete_combinations');
47 END IF;
48
49 x_return_status := FND_API.G_RET_STS_SUCCESS;
50
51 IF (p_mode = 'TOTAL') THEN
52 BEGIN
53
54 DELETE FROM jtf_tae_qual_prod_factors
55 WHERE qual_product_id IN
56 ( SELECT qual_product_id
57 FROM jtf_tae_qual_products
58 WHERE source_id = p_source_id
59 AND trans_object_type_id = p_trans_id);
60
61 DELETE FROM jtf_tae_qual_products
62 WHERE source_id = p_source_id
63 AND trans_object_type_id = p_trans_id;
64
65 DELETE FROM jtf_tae_qual_factors o
66 WHERE NOT EXISTS
67 ( SELECT NULL
68 FROM jtf_tae_qual_products i
69 WHERE MOD(i.relation_product, o.relation_factor) = 0 );
70
71 DELETE FROM jty_tae_attr_products_sql
72 WHERE source_id = p_source_id
73 AND trans_type_id = p_trans_id
74 AND keep_flag <> 'Y';
75
76 EXCEPTION
77 WHEN OTHERS THEN
78 x_msg_data := SQLCODE || ' : ' || SQLERRM;
79 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
80 END;
81 ELSIF (p_mode = 'DATE EFFECTIVE') THEN
82 BEGIN
83
84 DELETE FROM jty_dea_attr_prod_factors
85 WHERE dea_attr_products_id IN
86 ( SELECT dea_attr_products_id
87 FROM jty_dea_attr_products
88 WHERE source_id = p_source_id
89 AND trans_type_id = p_trans_id);
90
91 DELETE FROM jty_dea_attr_products
92 WHERE source_id = p_source_id
93 AND trans_type_id = p_trans_id;
94
95 DELETE FROM jty_dea_attr_factors o
96 WHERE NOT EXISTS
97 ( SELECT NULL
98 FROM jty_dea_attr_products i
99 WHERE MOD(i.attr_relation_product, o.relation_factor) = 0 );
100
101 DELETE FROM jty_dea_attr_products_sql
102 WHERE source_id = p_source_id
103 AND trans_type_id = p_trans_id
104 AND keep_flag <> 'Y';
105
106 EXCEPTION
107
108 WHEN OTHERS THEN
109 x_msg_data := SQLCODE || ' : ' || SQLERRM;
110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
111 END;
112 ELSIF (p_mode = 'INCREMENTAL') THEN
113 BEGIN
114
115 DELETE FROM jtf_tae_qual_products
116 WHERE source_id = p_source_id
117 AND trans_object_type_id = p_trans_id
118 AND relation_product not in (
119 SELECT qual_relation_product
120 FROM jtf_terr_qtype_usgs_all a,
121 jtf_qual_type_usgs_all b
122 WHERE a.qual_type_usg_id = b.qual_type_usg_id
123 AND b.source_id = p_source_id
124 AND b.qual_type_id = p_trans_id)
125 RETURNING qual_product_id, relation_product BULK COLLECT INTO l_qual_prd_id_tbl, l_rel_prd_tbl;
126
127 IF (l_qual_prd_id_tbl.COUNT > 0) THEN
128 FORALL i IN l_qual_prd_id_tbl.FIRST .. l_qual_prd_id_tbl.LAST
129 DELETE FROM jtf_tae_qual_prod_factors
130 WHERE qual_product_id = l_qual_prd_id_tbl(i);
131 END IF;
132
133 DELETE FROM jtf_tae_qual_factors o
134 WHERE NOT EXISTS
135 ( SELECT NULL
136 FROM jtf_tae_qual_products i
137 WHERE MOD(i.relation_product, o.relation_factor) = 0 );
138
139 IF (l_rel_prd_tbl.COUNT > 0) THEN
140 FORALL i IN l_rel_prd_tbl.FIRST .. l_rel_prd_tbl.LAST
141 DELETE FROM jty_tae_attr_products_sql
142 WHERE source_id = p_source_id
143 AND trans_type_id = p_trans_id
144 AND attr_relation_product = l_rel_prd_tbl(i)
145 AND keep_flag <> 'Y';
146 END IF;
147
148 EXCEPTION
149 WHEN OTHERS THEN
150 x_msg_data := SQLCODE || ' : ' || SQLERRM;
151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
152 END;
153 END IF; /* end IF (p_mode = 'TOTAL') */
154
155 -- debug message
156 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
157 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
158 'jtf.plsql.JTY_TAE_CONTROL_PVT.delete_combinations.end',
159 'End of the procedure JTY_TAE_CONTROL_PVT.delete_combinations');
160 END IF;
161
162 EXCEPTION
163 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
165 x_msg_count := 1;
166 RETCODE := 2;
167 ERRBUF := x_msg_data;
168 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
169 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
170 'jtf.plsql.jtf_tae_cpntrol_pvt.delete_combinations.g_exc_unexpected_error',
171 x_msg_data);
172 END IF;
173
174 WHEN OTHERS THEN
175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
176 x_msg_data := SQLCODE || ' : ' || SQLERRM;
177 x_msg_count := 1;
178 RETCODE := 2;
179 ERRBUF := x_msg_data;
180 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
181 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
182 'jtf.plsql.JTY_TAE_CONTROL_PVT.delete_combinations.other',
183 substr(x_msg_data, 1, 4000));
184 END IF;
185
186 END delete_combinations;
187
188 PROCEDURE Classify_Territories
189 ( p_source_id IN NUMBER,
190 p_trans_id IN NUMBER,
191 p_mode IN VARCHAR2,
192 p_qual_prd_tbl IN JTY_TERR_ENGINE_GEN_PVT.qual_prd_tbl_type,
193 x_Return_Status OUT NOCOPY VARCHAR2,
194 x_Msg_Count OUT NOCOPY NUMBER,
195 x_Msg_Data OUT NOCOPY VARCHAR2,
196 ERRBUF OUT NOCOPY VARCHAR2,
197 RETCODE OUT NOCOPY VARCHAR2 )
198 IS
199
200 l_terr_analyze_id number;
201 l_qual_factor_id number;
202 l_qual_product_id number;
203 l_qual_prod_factor_id number;
204 l_counter number;
205 l_exist_qual_detail_count number;
206 l_qual_type_usg_id number;
207
208 l_no_of_records NUMBER;
209 l_header_seq NUMBER;
210 l_index_name varchar2(30);
211
212 cursor quals_used(cl_qual_relation_product number) is
213 select qual_usg_id
214 from jtf_qual_usgs_all jqua
215 where mod(cl_qual_relation_product, jqua.qual_relation_factor) = 0
216 and org_id = -3113;
217
218 cursor qual_details(cl_qual_usg_id number) is
219 select * from jtf_qual_usgs_all
220 where qual_usg_id = cl_qual_usg_id
221 and org_id = -3113;
222
223 BEGIN
224 -- debug message
225 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
226 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
227 'jtf.plsql.JTY_TAE_CONTROL_PVT.classify_territories.begin',
228 'Start of the procedure JTY_TAE_CONTROL_PVT.classify_territories');
229 END IF;
230
231 x_return_status := FND_API.G_RET_STS_SUCCESS;
232
233 BEGIN
234 SELECT qual_type_usg_id
235 INTO l_qual_type_usg_id
236 FROM jtf_qual_type_usgs_all
237 WHERE source_id = p_source_id
238 AND qual_type_id = p_trans_id;
239 EXCEPTION
240 WHEN NO_DATA_FOUND THEN
241 x_msg_data := 'No row in table jtf_qual_type_usgs_all corresponding to source : ' || p_source_id || ' and transaction : ' ||
242 p_trans_id;
243 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
244 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
245 'jtf.plsql.JTY_TAE_CONTROL_PVT.classify_territories.no_qual_type_usg_id',
246 x_msg_data);
247 END IF;
248 RAISE;
249 END;
250
251 BEGIN
252 SELECT max(to_number(substr(index_name, instr(index_name, '_N') +2)))
253 INTO l_counter
254 FROM jtf_tae_qual_products
255 WHERE source_id = p_source_id
256 AND trans_object_type_id = p_trans_id;
257
258 IF (l_counter IS NULL) THEN
259 l_counter := 0;
260 END IF;
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 l_counter := 0;
264 WHEN OTHERS THEN
265 RAISE;
266 END;
267
268 /* Create Combinations in Product and Factor Tables */
269 SELECT JTF_TAE_ANALYZE_TERR_S.NEXTVAL
270 INTO l_terr_analyze_id
271 FROM dual;
272
273 IF (p_qual_prd_tbl.COUNT > 0) THEN
274 FOR i IN p_qual_prd_tbl.FIRST .. p_qual_prd_tbl.LAST LOOP
275 l_counter := l_counter + 1;
276
277 SELECT JTF_TAE_QUAL_PRODUCTS_S.NEXTVAL
278 INTO l_qual_product_id
279 FROM dual;
280
281 -- POPULATE PRODUCTS
282 IF ((p_qual_prd_tbl(i) <> 1) AND (p_qual_prd_tbl(i) IS NOT NULL)) THEN
283
284 BEGIN
285
286 INSERT INTO JTF_TAE_QUAL_products
287 ( QUAL_PRODUCT_ID,
288 RELATION_PRODUCT,
289 SOURCE_ID,
290 TRANS_OBJECT_TYPE_ID,
291 INDEX_NAME,
292 FIRST_CHAR_FLAG,
293 BUILD_INDEX_FLAG,
294 LAST_UPDATE_DATE,
295 LAST_UPDATED_BY,
296 CREATION_DATE,
297 CREATED_BY,
298 LAST_UPDATE_LOGIN,
299 TERR_ANALYZE_ID
300 )
301 VALUES
302 ( l_qual_product_id, --QUAL_PRODUCT_ID,
303 p_qual_prd_tbl(i), --RELATION_PRODUCT,
304 p_source_id, --SOURCE_ID,
305 p_trans_id, --TRANS_OBJECT_TYPE_ID,
306 'JTF_TAE_TN' || TO_CHAR(ABS(l_qual_type_usg_id)) || '_N'|| TO_CHAR(l_counter), --INDEX_NAME,
307 'N', --FIRST_CHAR,
308 'Y', --BUILD_INDEX_FLAG,
309 sysdate, --LAST_UPDATE_DATE,
310 1, --LAST_UPDATED_BY,
311 sysdate, --CREATION_DATE,
312 1, --CREATED_BY,
313 1, --LAST_UPDATE_LOGIN)
314 l_terr_analyze_id --TERR_ANALYZE_ID,
315 );
316
317 EXCEPTION
318 WHEN OTHERS THEN
319 x_msg_data := SQLCODE || ' : ' || SQLERRM;
320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
321 END;
322
323 l_no_of_records := 0;
324
325 SELECT count(*)
326 INTO l_no_of_records
327 FROM jty_terr_values_idx_header
328 WHERE source_id = p_source_id
329 AND relation_product = p_qual_prd_tbl(i);
330
331 IF (l_no_of_records = 0) THEN
332
333 SELECT jty_terr_values_idx_header_s.nextval
334 INTO l_header_seq
335 FROM dual;
336
337 SELECT 'JTY_DNM_ATTR_VAL_' || abs(l_qual_type_usg_id) || '_BN' ||
338 (nvl(max(to_number(substr(index_name, instr(index_name, '_BN')+3))), 0) + 1)
339 INTO l_index_name
340 FROM jty_terr_values_idx_header
341 WHERE index_name like 'JTY_DNM_ATTR_VAL_' || abs(l_qual_type_usg_id) || '_BN%';
342
343 INSERT INTO jty_terr_values_idx_header (
344 terr_values_idx_header_id
345 ,source_id
346 ,last_update_date
347 ,last_updated_by
348 ,creation_date
349 ,created_by
350 ,last_update_login
351 ,relation_product
352 ,index_name
353 ,build_index_flag
354 ,delete_flag )
355 VALUES (
356 l_header_seq
357 ,p_source_id
358 ,G_SYSDATE
359 ,G_USER_ID
363 ,p_qual_prd_tbl(i)
360 ,G_SYSDATE
361 ,G_USER_ID
362 ,G_USER_ID
364 ,l_index_name
365 ,'Y'
366 ,'N');
367
368 END IF; /* end IF (l_no_of_records = 0) */
369
370 FOR qual_name in quals_used(p_qual_prd_tbl(i)) LOOP
371
372 FOR q_detail in qual_details(qual_name.qual_usg_id) LOOP
373
374 SELECT count(*)
375 INTO l_exist_qual_detail_count
376 FROM JTF_TAE_QUAL_factors
377 WHERE qual_usg_id = q_detail.qual_usg_id;
378
379 IF l_exist_qual_detail_count = 0 THEN
380
381 BEGIN
382
383 SELECT JTF_TAE_QUAL_factors_s.NEXTVAL
384 INTO l_qual_factor_id
385 FROM dual;
386
387 INSERT INTO JTF_TAE_QUAL_factors
388 ( QUAL_FACTOR_ID,
389 RELATION_FACTOR,
390 QUAL_USG_ID,
391 LAST_UPDATED_BY,
392 LAST_UPDATE_DATE,
393 CREATED_BY,
394 CREATION_DATE,
395 LAST_UPDATE_LOGIN,
396 TERR_ANALYZE_ID,
397 TAE_COL_MAP,
398 TAE_REC_MAP,
399 USE_TAE_COL_IN_INDEX_FLAG,
400 UPDATE_SELECTIVITY_FLAG,
401 INPUT_SELECTIVITY,
402 INPUT_ORDINAL_SELECTIVITY,
403 INPUT_DEVIATION,
404 ORG_ID,
405 OBJECT_VERSION_NUMBER
406 )
407 VALUES
408 ( l_qual_factor_id, -- QUAL_FACTOR_ID
409 q_detail.qual_relation_factor, -- RELATION_FACTOR
410 q_detail.qual_usg_id, -- QUAL_USG_ID
411 0, -- LAST_UPDATED_BY
412 sysdate, -- LAST_UPDATE_DATE
413 0, -- CREATED_BY
414 sysdate, -- CREATION_DATE
415 0, -- LAST_UPDATE_LOGIN
416 l_terr_analyze_id, -- TERR_ANALYZE_ID
417 q_detail.qual_col1, -- TAE_COL_MAP
418 q_detail.qual_col1_alias, -- TAE_REC_MAP
419 'Y', -- USE_TAE_COL_IN_INDEX_FLAG
420 'Y', -- UPDATE_SELECTIVITY_FLAG
421 null, -- INPUT_SELECTIVITY
422 null, -- INPUT_ORDINAL_SELECTIVITY
423 null, -- INPUT_DEVIATION
424 null, -- ORG_ID
425 null -- OBJECT_VERSION_NUMBER
426 );
427
428 COMMIT;
429
430 EXCEPTION
431 WHEN OTHERS THEN
432 x_msg_data := SQLCODE || ' : ' || SQLERRM;
433 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
434 END;
435
436 END IF; /* end IF l_exist_qual_detail_count = 0 */
437
438 IF (l_no_of_records = 0) THEN
439 IF (q_detail.comparison_operator IS NOT NULL) THEN
440 INSERT INTO jty_terr_values_idx_details (
441 terr_values_idx_details_id
442 ,terr_values_idx_header_id
443 ,last_update_date
444 ,last_updated_by
445 ,creation_date
446 ,created_by
447 ,last_update_login
448 ,values_col_map
449 ,input_selectivity
450 ,input_ordinal_selectivity )
451 VALUES (
452 jty_terr_values_idx_details_s.nextval
453 ,l_header_seq
454 ,G_SYSDATE
455 ,G_USER_ID
456 ,G_SYSDATE
457 ,G_USER_ID
458 ,G_USER_ID
459 ,q_detail.comparison_operator
460 ,null
461 ,null);
462 END IF;
463
464 IF (q_detail.low_value_char_id IS NOT NULL) THEN
465 INSERT INTO jty_terr_values_idx_details (
466 terr_values_idx_details_id
467 ,terr_values_idx_header_id
468 ,last_update_date
469 ,last_updated_by
470 ,creation_date
471 ,created_by
472 ,last_update_login
473 ,values_col_map
474 ,input_selectivity
475 ,input_ordinal_selectivity )
476 VALUES (
477 jty_terr_values_idx_details_s.nextval
478 ,l_header_seq
479 ,G_SYSDATE
480 ,G_USER_ID
481 ,G_SYSDATE
482 ,G_USER_ID
483 ,G_USER_ID
484 ,q_detail.low_value_char_id
485 ,null
486 ,null);
487 END IF;
488
489 IF (q_detail.low_value_char IS NOT NULL) THEN
490 INSERT INTO jty_terr_values_idx_details (
491 terr_values_idx_details_id
492 ,terr_values_idx_header_id
493 ,last_update_date
494 ,last_updated_by
495 ,creation_date
496 ,created_by
500 ,input_ordinal_selectivity )
497 ,last_update_login
498 ,values_col_map
499 ,input_selectivity
501 VALUES (
502 jty_terr_values_idx_details_s.nextval
503 ,l_header_seq
504 ,G_SYSDATE
505 ,G_USER_ID
506 ,G_SYSDATE
507 ,G_USER_ID
508 ,G_USER_ID
509 ,q_detail.low_value_char
510 ,null
511 ,null);
512 END IF;
513
514 IF (q_detail.high_value_char IS NOT NULL) THEN
515 INSERT INTO jty_terr_values_idx_details (
516 terr_values_idx_details_id
517 ,terr_values_idx_header_id
518 ,last_update_date
519 ,last_updated_by
520 ,creation_date
521 ,created_by
522 ,last_update_login
523 ,values_col_map
524 ,input_selectivity
525 ,input_ordinal_selectivity )
526 VALUES (
527 jty_terr_values_idx_details_s.nextval
528 ,l_header_seq
529 ,G_SYSDATE
530 ,G_USER_ID
531 ,G_SYSDATE
532 ,G_USER_ID
533 ,G_USER_ID
534 ,q_detail.high_value_char
535 ,null
536 ,null);
537 END IF;
538
539 IF (q_detail.low_value_number IS NOT NULL) THEN
540 INSERT INTO jty_terr_values_idx_details (
541 terr_values_idx_details_id
542 ,terr_values_idx_header_id
543 ,last_update_date
544 ,last_updated_by
545 ,creation_date
546 ,created_by
547 ,last_update_login
548 ,values_col_map
549 ,input_selectivity
550 ,input_ordinal_selectivity )
551 VALUES (
552 jty_terr_values_idx_details_s.nextval
553 ,l_header_seq
554 ,G_SYSDATE
555 ,G_USER_ID
556 ,G_SYSDATE
557 ,G_USER_ID
558 ,G_USER_ID
559 ,q_detail.low_value_number
560 ,null
561 ,null);
562 END IF;
563
564 IF (q_detail.high_value_number IS NOT NULL) THEN
565 INSERT INTO jty_terr_values_idx_details (
566 terr_values_idx_details_id
567 ,terr_values_idx_header_id
568 ,last_update_date
569 ,last_updated_by
570 ,creation_date
571 ,created_by
572 ,last_update_login
573 ,values_col_map
574 ,input_selectivity
575 ,input_ordinal_selectivity )
576 VALUES (
577 jty_terr_values_idx_details_s.nextval
578 ,l_header_seq
579 ,G_SYSDATE
580 ,G_USER_ID
581 ,G_SYSDATE
582 ,G_USER_ID
583 ,G_USER_ID
584 ,q_detail.high_value_number
585 ,null
586 ,null);
587 END IF;
588
589 IF (q_detail.interest_type_id IS NOT NULL) THEN
590 INSERT INTO jty_terr_values_idx_details (
591 terr_values_idx_details_id
592 ,terr_values_idx_header_id
593 ,last_update_date
594 ,last_updated_by
595 ,creation_date
596 ,created_by
597 ,last_update_login
598 ,values_col_map
599 ,input_selectivity
600 ,input_ordinal_selectivity )
601 VALUES (
602 jty_terr_values_idx_details_s.nextval
603 ,l_header_seq
604 ,G_SYSDATE
605 ,G_USER_ID
606 ,G_SYSDATE
607 ,G_USER_ID
608 ,G_USER_ID
609 ,q_detail.interest_type_id
610 ,null
611 ,null);
612 END IF;
613
614 IF (q_detail.primary_interest_code_id IS NOT NULL) THEN
615 INSERT INTO jty_terr_values_idx_details (
616 terr_values_idx_details_id
617 ,terr_values_idx_header_id
618 ,last_update_date
619 ,last_updated_by
620 ,creation_date
621 ,created_by
622 ,last_update_login
623 ,values_col_map
624 ,input_selectivity
625 ,input_ordinal_selectivity )
626 VALUES (
627 jty_terr_values_idx_details_s.nextval
628 ,l_header_seq
629 ,G_SYSDATE
630 ,G_USER_ID
631 ,G_SYSDATE
632 ,G_USER_ID
633 ,G_USER_ID
634 ,q_detail.primary_interest_code_id
635 ,null
636 ,null);
637 END IF;
638
639 IF (q_detail.secondary_interest_code_id IS NOT NULL) THEN
640 INSERT INTO jty_terr_values_idx_details (
641 terr_values_idx_details_id
645 ,creation_date
642 ,terr_values_idx_header_id
643 ,last_update_date
644 ,last_updated_by
646 ,created_by
647 ,last_update_login
648 ,values_col_map
649 ,input_selectivity
650 ,input_ordinal_selectivity )
651 VALUES (
652 jty_terr_values_idx_details_s.nextval
653 ,l_header_seq
654 ,G_SYSDATE
655 ,G_USER_ID
656 ,G_SYSDATE
657 ,G_USER_ID
658 ,G_USER_ID
659 ,q_detail.secondary_interest_code_id
660 ,null
661 ,null);
662 END IF;
663
664 IF (q_detail.currency_code IS NOT NULL) THEN
665 INSERT INTO jty_terr_values_idx_details (
666 terr_values_idx_details_id
667 ,terr_values_idx_header_id
668 ,last_update_date
669 ,last_updated_by
670 ,creation_date
671 ,created_by
672 ,last_update_login
673 ,values_col_map
674 ,input_selectivity
675 ,input_ordinal_selectivity )
676 VALUES (
677 jty_terr_values_idx_details_s.nextval
678 ,l_header_seq
679 ,G_SYSDATE
680 ,G_USER_ID
681 ,G_SYSDATE
682 ,G_USER_ID
683 ,G_USER_ID
684 ,q_detail.currency_code
685 ,null
686 ,null);
687 END IF;
688
689 IF (q_detail.value1_id IS NOT NULL) THEN
690 INSERT INTO jty_terr_values_idx_details (
691 terr_values_idx_details_id
692 ,terr_values_idx_header_id
693 ,last_update_date
694 ,last_updated_by
695 ,creation_date
696 ,created_by
697 ,last_update_login
698 ,values_col_map
699 ,input_selectivity
700 ,input_ordinal_selectivity )
701 VALUES (
702 jty_terr_values_idx_details_s.nextval
703 ,l_header_seq
704 ,G_SYSDATE
705 ,G_USER_ID
706 ,G_SYSDATE
707 ,G_USER_ID
708 ,G_USER_ID
709 ,q_detail.value1_id
710 ,null
711 ,null);
712 END IF;
713
714 IF (q_detail.value2_id IS NOT NULL) THEN
715 INSERT INTO jty_terr_values_idx_details (
716 terr_values_idx_details_id
717 ,terr_values_idx_header_id
718 ,last_update_date
719 ,last_updated_by
720 ,creation_date
721 ,created_by
722 ,last_update_login
723 ,values_col_map
724 ,input_selectivity
725 ,input_ordinal_selectivity )
726 VALUES (
727 jty_terr_values_idx_details_s.nextval
728 ,l_header_seq
729 ,G_SYSDATE
730 ,G_USER_ID
731 ,G_SYSDATE
732 ,G_USER_ID
733 ,G_USER_ID
734 ,q_detail.value2_id
735 ,null
736 ,null);
737 END IF;
738
739 IF (q_detail.value3_id IS NOT NULL) THEN
740 INSERT INTO jty_terr_values_idx_details (
741 terr_values_idx_details_id
742 ,terr_values_idx_header_id
743 ,last_update_date
744 ,last_updated_by
745 ,creation_date
746 ,created_by
747 ,last_update_login
748 ,values_col_map
749 ,input_selectivity
750 ,input_ordinal_selectivity )
751 VALUES (
752 jty_terr_values_idx_details_s.nextval
753 ,l_header_seq
754 ,G_SYSDATE
755 ,G_USER_ID
756 ,G_SYSDATE
757 ,G_USER_ID
758 ,G_USER_ID
759 ,q_detail.value3_id
760 ,null
761 ,null);
762 END IF;
763
764 IF (q_detail.value4_id IS NOT NULL) THEN
765 INSERT INTO jty_terr_values_idx_details (
766 terr_values_idx_details_id
767 ,terr_values_idx_header_id
768 ,last_update_date
769 ,last_updated_by
770 ,creation_date
771 ,created_by
772 ,last_update_login
773 ,values_col_map
774 ,input_selectivity
775 ,input_ordinal_selectivity )
776 VALUES (
777 jty_terr_values_idx_details_s.nextval
778 ,l_header_seq
779 ,G_SYSDATE
780 ,G_USER_ID
781 ,G_SYSDATE
782 ,G_USER_ID
783 ,G_USER_ID
784 ,q_detail.value4_id
785 ,null
786 ,null);
787 END IF;
788
792 ,terr_values_idx_header_id
789 IF (q_detail.first_char IS NOT NULL) THEN
790 INSERT INTO jty_terr_values_idx_details (
791 terr_values_idx_details_id
793 ,last_update_date
794 ,last_updated_by
795 ,creation_date
796 ,created_by
797 ,last_update_login
798 ,values_col_map
799 ,input_selectivity
800 ,input_ordinal_selectivity )
801 VALUES (
802 jty_terr_values_idx_details_s.nextval
803 ,l_header_seq
804 ,G_SYSDATE
805 ,G_USER_ID
806 ,G_SYSDATE
807 ,G_USER_ID
808 ,G_USER_ID
809 ,q_detail.first_char
810 ,null
811 ,null);
812 END IF;
813 END IF; /* end IF (l_no_of_records = 0) */
814
815 END LOOP; /* end loop FOR q_detail in qual_details(qual_name.qual_usg_id) */
816 END LOOP; /* end loop FOR qual_name in quals_used(rel_set.qual_relation_product) */
817
818 FOR qual_name in quals_used(p_qual_prd_tbl(i)) LOOP
819
820 BEGIN
821
822 SELECT qual_factor_id
823 INTO l_qual_factor_id
824 FROM JTF_TAE_QUAL_factors
825 WHERE qual_usg_id = qual_name.qual_usg_id
826 AND rownum < 2;
827
828 SELECT JTF_TAE_QUAL_PROD_FACTORS_S.NEXTVAL
829 INTO l_qual_prod_factor_id
830 FROM dual;
831
832 INSERT INTO JTF_TAE_QUAL_prod_factors
833 ( QUAL_PROD_FACTOR_ID,
834 QUAL_PRODUCT_ID,
835 QUAL_FACTOR_ID,
836 LAST_UPDATE_DATE,
837 LAST_UPDATED_BY,
838 CREATION_DATE,
839 CREATED_BY,
840 LAST_UPDATE_LOGIN,
841 TERR_ANALYZE_ID,
842 ORG_ID,
843 OBJECT_VERSION_NUMBER
844 )
845 VALUES
846 ( l_qual_prod_factor_id, --QUAL_PROD_FACTOR_ID,
847 l_qual_product_id, --QUAL_PRODUCT_ID,
848 l_qual_factor_id, --QUAL_FACTOR_ID
849 sysdate, --LAST_UPDATE_DATE,
850 0, --LAST_UPDATED_BY,
851 sysdate, --CREATION_DATE,
852 0, --CREATED_BY,
853 0, --LAST_UPDATE_LOGIN,
854 l_terr_analyze_id, --TERR_ANALYZE_ID,
855 null, --ORG_ID,
856 null --OBJECT_VERSION_NUMBER
857 );
858
859 EXCEPTION
860 WHEN NO_DATA_FOUND THEN
861 x_msg_data := 'Populating JTF_TAE_QUAL_PROD_FACTORS table : Error no_data_found.';
862 RAISE FND_API.G_EXC_ERROR;
863
864 WHEN OTHERS THEN
865 x_msg_data := SQLCODE || ' : ' || SQLERRM;
866 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
867 END;
868
869 END LOOP; /* end loop FOR qual_name in quals_used(rel_set.qual_relation_product) */
870 END IF; /* end IF ((p_qual_prd_tbl(i) <> 1) AND (p_qual_prd_tbl(i) IS NOT NULL)) */
871 END LOOP; /* end loop FOR i IN p_qual_prd_tbl.FIRST .. p_qual_prd_tbl.LAST */
872 END IF; /* end IF (p_qual_prd_tbl.COUNT > 0) */
873
874 /* no need to build the index for the qualifiers with no column mapped to TRANS table */
875 update JTF_TAE_QUAL_factors
876 set UPDATE_SELECTIVITY_FLAG = 'N',
877 USE_TAE_COL_IN_INDEX_FLAG = 'N'
878 where TAE_COL_MAP is null;
879
880 -- debug message
881 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
882 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
883 'jtf.plsql.JTY_TAE_CONTROL_PVT.classify_territories.end',
884 'End of the procedure JTY_TAE_CONTROL_PVT.classify_territories');
885 END IF;
886
887 EXCEPTION
888 WHEN NO_DATA_FOUND THEN
889 x_return_status := FND_API.G_RET_STS_ERROR;
890 x_msg_count := 1;
891 RETCODE := 2;
892 ERRBUF := x_msg_data;
893 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
894 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
895 'jtf.plsql.JTY_TAE_CONTROL_PVT.classify_territories.no_data_found',
896 x_msg_data);
897 END IF;
898
899 WHEN FND_API.G_EXC_ERROR THEN
900 x_return_status := FND_API.G_RET_STS_ERROR;
901 x_msg_count := 1;
902 RETCODE := 2;
903 ERRBUF := x_msg_data;
904 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
905 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
906 'jtf.plsql.JTY_TAE_CONTROL_PVT.classify_territories.g_exc_error',
907 x_msg_data);
908 END IF;
909
910 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
912 x_msg_count := 1;
913 RETCODE := 2;
914 ERRBUF := x_msg_data;
915 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
916 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
917 'jtf.plsql.jtf_tae_cpntrol_pvt.classify_territories.g_exc_unexpected_error',
918 x_msg_data);
919 END IF;
920
921 WHEN OTHERS THEN
922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
923 x_msg_data := SQLCODE || ' : ' || SQLERRM;
924 x_msg_count := 1;
925 RETCODE := 2;
929 'jtf.plsql.JTY_TAE_CONTROL_PVT.classify_territories.other',
926 ERRBUF := x_msg_data;
927 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
928 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
930 substr(x_msg_data, 1, 4000));
931 END IF;
932
933 END Classify_Territories;
934
935 PROCEDURE Classify_dea_Territories
936 ( p_source_id IN NUMBER,
937 p_trans_id IN NUMBER,
938 p_qual_prd_tbl IN JTY_TERR_ENGINE_GEN_PVT.qual_prd_tbl_type,
939 x_Return_Status OUT NOCOPY VARCHAR2,
940 x_Msg_Count OUT NOCOPY NUMBER,
941 x_Msg_Data OUT NOCOPY VARCHAR2,
942 ERRBUF OUT NOCOPY VARCHAR2,
943 RETCODE OUT NOCOPY VARCHAR2 )
944 IS
945
946 l_terr_analyze_id number;
947 l_qual_factor_id number;
948 l_qual_product_id number;
949 l_qual_prod_factor_id number;
950 l_counter number;
951 l_exist_qual_detail_count number;
952 l_qual_type_usg_id number;
953
954 l_no_of_records NUMBER;
955 l_header_seq NUMBER;
956
957 cursor quals_used(cl_qual_relation_product number) is
958 select qual_usg_id
959 from jtf_qual_usgs_all jqua
960 where mod(cl_qual_relation_product, jqua.qual_relation_factor) = 0
961 and org_id = -3113;
962
963 cursor qual_details(cl_qual_usg_id number) is
964 select * from jtf_qual_usgs_all
965 where qual_usg_id = cl_qual_usg_id
966 and org_id = -3113;
967
968 BEGIN
969 -- debug message
970 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
971 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
972 'jtf.plsql.JTY_TAE_CONTROL_PVT.classify_dea_territories.begin',
973 'Start of the procedure JTY_TAE_CONTROL_PVT.classify_dea_territories');
974 END IF;
975
976 x_return_status := FND_API.G_RET_STS_SUCCESS;
977
978 BEGIN
979 SELECT qual_type_usg_id
980 INTO l_qual_type_usg_id
981 FROM jtf_qual_type_usgs_all
982 WHERE source_id = p_source_id
983 AND qual_type_id = p_trans_id;
984 EXCEPTION
985 WHEN NO_DATA_FOUND THEN
986 x_msg_data := 'No row in table jtf_qual_type_usgs_all corresponding to source : ' || p_source_id || ' and transaction : ' ||
987 p_trans_id;
988 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
989 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
990 'jtf.plsql.JTY_TAE_CONTROL_PVT.classify_dea_territories.no_qual_type_usg_id',
991 x_msg_data);
992 END IF;
993 RAISE;
994 END;
995
996 /* Create Combinations in Product and Factor Tables */
997 SELECT JTF_TAE_ANALYZE_TERR_S.NEXTVAL
998 INTO l_terr_analyze_id
999 FROM dual;
1000
1001 l_counter := 0;
1002
1003 IF (p_qual_prd_tbl.COUNT > 0) THEN
1004 FOR i IN p_qual_prd_tbl.FIRST .. p_qual_prd_tbl.LAST LOOP
1005 l_counter := l_counter + 1;
1006
1007 SELECT JTY_DEA_ATTR_PRODUCTS_S.NEXTVAL
1008 INTO l_qual_product_id
1009 FROM dual;
1010
1011 -- POPULATE PRODUCTS
1012 IF ((p_qual_prd_tbl(i) <> 1) AND (p_qual_prd_tbl(i) IS NOT NULL)) THEN
1013
1014 BEGIN
1015
1016 INSERT INTO JTY_DEA_ATTR_PRODUCTS
1017 ( DEA_ATTR_PRODUCTS_ID,
1018 ATTR_RELATION_PRODUCT,
1019 SOURCE_ID,
1020 TRANS_TYPE_ID,
1021 INDEX_NAME,
1022 FIRST_CHAR_FLAG,
1023 BUILD_INDEX_FLAG,
1024 LAST_UPDATE_DATE,
1025 LAST_UPDATED_BY,
1026 CREATION_DATE,
1027 CREATED_BY,
1028 LAST_UPDATE_LOGIN,
1029 TERR_ANALYZE_ID
1030 )
1031 VALUES
1032 ( l_qual_product_id, --QUAL_PRODUCT_ID,
1033 p_qual_prd_tbl(i), --RELATION_PRODUCT,
1034 p_source_id, --SOURCE_ID,
1035 p_trans_id, --TRANS_OBJECT_TYPE_ID,
1036 'JTF_TAE_DE' || TO_CHAR(ABS(l_qual_type_usg_id)) || '_N'|| TO_CHAR(l_counter) || '_', --INDEX_NAME,
1037 'N', --FIRST_CHAR,
1038 'Y', --BUILD_INDEX_FLAG,
1039 sysdate, --LAST_UPDATE_DATE,
1040 1, --LAST_UPDATED_BY,
1041 sysdate, --CREATION_DATE,
1042 1, --CREATED_BY,
1043 1, --LAST_UPDATE_LOGIN)
1044 l_terr_analyze_id --TERR_ANALYZE_ID,
1045 );
1046
1047 EXCEPTION
1048 WHEN OTHERS THEN
1049 x_msg_data := SQLCODE || ' : ' || SQLERRM;
1050 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1051 END;
1052
1053 l_no_of_records := 0;
1054
1055 SELECT count(*)
1056 INTO l_no_of_records
1057 FROM jty_dea_values_idx_header
1058 WHERE source_id = p_source_id
1059 AND relation_product = p_qual_prd_tbl(i);
1060
1061 IF (l_no_of_records = 0) THEN
1062
1063 SELECT jty_dea_values_idx_header_s.nextval
1064 INTO l_header_seq
1065 FROM dual;
1066
1067 INSERT INTO jty_dea_values_idx_header (
1068 dea_values_idx_header_id
1069 ,source_id
1070 ,last_update_date
1074 ,last_update_login
1071 ,last_updated_by
1072 ,creation_date
1073 ,created_by
1075 ,relation_product
1076 ,index_name
1077 ,build_index_flag )
1078 VALUES (
1079 l_header_seq
1080 ,p_source_id
1081 ,G_SYSDATE
1082 ,G_USER_ID
1083 ,G_SYSDATE
1084 ,G_USER_ID
1085 ,G_USER_ID
1086 ,p_qual_prd_tbl(i)
1087 ,'JTY_DEA_ATTR_VAL_' || abs(l_qual_type_usg_id) || '_BN' || i
1088 ,'Y');
1089
1090 END IF; /* end IF (l_no_of_records = 0) */
1091
1092 FOR qual_name in quals_used(p_qual_prd_tbl(i)) LOOP
1093
1094 FOR q_detail in qual_details(qual_name.qual_usg_id) LOOP
1095
1096 SELECT count(*)
1097 INTO l_exist_qual_detail_count
1098 FROM jty_dea_attr_factors
1099 WHERE qual_usg_id = q_detail.qual_usg_id;
1100
1101 IF l_exist_qual_detail_count = 0 THEN
1102
1103 BEGIN
1104
1105 SELECT JTY_DEA_ATTR_FACTORS_S.NEXTVAL
1106 INTO l_qual_factor_id
1107 FROM dual;
1108
1109 INSERT INTO JTY_DEA_ATTR_FACTORS
1110 ( DEA_ATTR_FACTORS_ID,
1111 RELATION_FACTOR,
1112 QUAL_USG_ID,
1113 LAST_UPDATED_BY,
1114 LAST_UPDATE_DATE,
1115 CREATED_BY,
1116 CREATION_DATE,
1117 LAST_UPDATE_LOGIN,
1118 TERR_ANALYZE_ID,
1119 TAE_COL_MAP,
1120 TAE_REC_MAP,
1121 USE_TAE_COL_IN_INDEX_FLAG,
1122 UPDATE_SELECTIVITY_FLAG,
1123 INPUT_SELECTIVITY,
1124 INPUT_ORDINAL_SELECTIVITY,
1125 INPUT_DEVIATION,
1126 OBJECT_VERSION_NUMBER
1127 )
1128 VALUES
1129 ( l_qual_factor_id, -- QUAL_FACTOR_ID
1130 q_detail.qual_relation_factor, -- RELATION_FACTOR
1131 q_detail.qual_usg_id, -- QUAL_USG_ID
1132 0, -- LAST_UPDATED_BY
1133 sysdate, -- LAST_UPDATE_DATE
1134 0, -- CREATED_BY
1135 sysdate, -- CREATION_DATE
1136 0, -- LAST_UPDATE_LOGIN
1137 l_terr_analyze_id, -- TERR_ANALYZE_ID
1138 q_detail.qual_col1, -- TAE_COL_MAP
1139 q_detail.qual_col1_alias, -- TAE_REC_MAP
1140 'Y', -- USE_TAE_COL_IN_INDEX_FLAG
1141 'Y', -- UPDATE_SELECTIVITY_FLAG
1142 null, -- INPUT_SELECTIVITY
1143 null, -- INPUT_ORDINAL_SELECTIVITY
1144 null, -- INPUT_DEVIATION
1145 null -- OBJECT_VERSION_NUMBER
1146 );
1147
1148 COMMIT;
1149
1150 EXCEPTION
1151 WHEN OTHERS THEN
1152 x_msg_data := SQLCODE || ' : ' || SQLERRM;
1153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1154 END;
1155
1156 END IF; /* end IF l_exist_qual_detail_count = 0 */
1157
1158 IF (l_no_of_records = 0) THEN
1159 IF (q_detail.comparison_operator IS NOT NULL) THEN
1160 INSERT INTO jty_dea_values_idx_details (
1161 dea_values_idx_details_id
1162 ,dea_values_idx_header_id
1163 ,last_update_date
1164 ,last_updated_by
1165 ,creation_date
1166 ,created_by
1167 ,last_update_login
1168 ,values_col_map
1169 ,input_selectivity
1170 ,input_ordinal_selectivity )
1171 VALUES (
1172 jty_dea_values_idx_details_s.nextval
1173 ,l_header_seq
1174 ,G_SYSDATE
1175 ,G_USER_ID
1176 ,G_SYSDATE
1177 ,G_USER_ID
1178 ,G_USER_ID
1179 ,q_detail.comparison_operator
1180 ,null
1181 ,null);
1182 END IF;
1183
1184 IF (q_detail.low_value_char_id IS NOT NULL) THEN
1185 INSERT INTO jty_dea_values_idx_details (
1186 dea_values_idx_details_id
1187 ,dea_values_idx_header_id
1188 ,last_update_date
1189 ,last_updated_by
1190 ,creation_date
1191 ,created_by
1192 ,last_update_login
1193 ,values_col_map
1194 ,input_selectivity
1195 ,input_ordinal_selectivity )
1196 VALUES (
1197 jty_dea_values_idx_details_s.nextval
1198 ,l_header_seq
1199 ,G_SYSDATE
1200 ,G_USER_ID
1201 ,G_SYSDATE
1202 ,G_USER_ID
1203 ,G_USER_ID
1204 ,q_detail.low_value_char_id
1205 ,null
1206 ,null);
1207 END IF;
1208
1209 IF (q_detail.low_value_char IS NOT NULL) THEN
1213 ,last_update_date
1210 INSERT INTO jty_dea_values_idx_details (
1211 dea_values_idx_details_id
1212 ,dea_values_idx_header_id
1214 ,last_updated_by
1215 ,creation_date
1216 ,created_by
1217 ,last_update_login
1218 ,values_col_map
1219 ,input_selectivity
1220 ,input_ordinal_selectivity )
1221 VALUES (
1222 jty_dea_values_idx_details_s.nextval
1223 ,l_header_seq
1224 ,G_SYSDATE
1225 ,G_USER_ID
1226 ,G_SYSDATE
1227 ,G_USER_ID
1228 ,G_USER_ID
1229 ,q_detail.low_value_char
1230 ,null
1231 ,null);
1232 END IF;
1233
1234 IF (q_detail.high_value_char IS NOT NULL) THEN
1235 INSERT INTO jty_dea_values_idx_details (
1236 dea_values_idx_details_id
1237 ,dea_values_idx_header_id
1238 ,last_update_date
1239 ,last_updated_by
1240 ,creation_date
1241 ,created_by
1242 ,last_update_login
1243 ,values_col_map
1244 ,input_selectivity
1245 ,input_ordinal_selectivity )
1246 VALUES (
1247 jty_dea_values_idx_details_s.nextval
1248 ,l_header_seq
1249 ,G_SYSDATE
1250 ,G_USER_ID
1251 ,G_SYSDATE
1252 ,G_USER_ID
1253 ,G_USER_ID
1254 ,q_detail.high_value_char
1255 ,null
1256 ,null);
1257 END IF;
1258
1259 IF (q_detail.low_value_number IS NOT NULL) THEN
1260 INSERT INTO jty_dea_values_idx_details (
1261 dea_values_idx_details_id
1262 ,dea_values_idx_header_id
1263 ,last_update_date
1264 ,last_updated_by
1265 ,creation_date
1266 ,created_by
1267 ,last_update_login
1268 ,values_col_map
1269 ,input_selectivity
1270 ,input_ordinal_selectivity )
1271 VALUES (
1272 jty_dea_values_idx_details_s.nextval
1273 ,l_header_seq
1274 ,G_SYSDATE
1275 ,G_USER_ID
1276 ,G_SYSDATE
1277 ,G_USER_ID
1278 ,G_USER_ID
1279 ,q_detail.low_value_number
1280 ,null
1281 ,null);
1282 END IF;
1283
1284 IF (q_detail.high_value_number IS NOT NULL) THEN
1285 INSERT INTO jty_dea_values_idx_details (
1286 dea_values_idx_details_id
1287 ,dea_values_idx_header_id
1288 ,last_update_date
1289 ,last_updated_by
1290 ,creation_date
1291 ,created_by
1292 ,last_update_login
1293 ,values_col_map
1294 ,input_selectivity
1295 ,input_ordinal_selectivity )
1296 VALUES (
1297 jty_dea_values_idx_details_s.nextval
1298 ,l_header_seq
1299 ,G_SYSDATE
1300 ,G_USER_ID
1301 ,G_SYSDATE
1302 ,G_USER_ID
1303 ,G_USER_ID
1304 ,q_detail.high_value_number
1305 ,null
1306 ,null);
1307 END IF;
1308
1309 IF (q_detail.interest_type_id IS NOT NULL) THEN
1310 INSERT INTO jty_dea_values_idx_details (
1311 dea_values_idx_details_id
1312 ,dea_values_idx_header_id
1313 ,last_update_date
1314 ,last_updated_by
1315 ,creation_date
1316 ,created_by
1317 ,last_update_login
1318 ,values_col_map
1319 ,input_selectivity
1320 ,input_ordinal_selectivity )
1321 VALUES (
1322 jty_dea_values_idx_details_s.nextval
1323 ,l_header_seq
1324 ,G_SYSDATE
1325 ,G_USER_ID
1326 ,G_SYSDATE
1327 ,G_USER_ID
1328 ,G_USER_ID
1329 ,q_detail.interest_type_id
1330 ,null
1331 ,null);
1332 END IF;
1333
1334 IF (q_detail.primary_interest_code_id IS NOT NULL) THEN
1335 INSERT INTO jty_dea_values_idx_details (
1336 dea_values_idx_details_id
1337 ,dea_values_idx_header_id
1338 ,last_update_date
1339 ,last_updated_by
1340 ,creation_date
1341 ,created_by
1342 ,last_update_login
1343 ,values_col_map
1344 ,input_selectivity
1345 ,input_ordinal_selectivity )
1346 VALUES (
1347 jty_dea_values_idx_details_s.nextval
1348 ,l_header_seq
1349 ,G_SYSDATE
1350 ,G_USER_ID
1351 ,G_SYSDATE
1352 ,G_USER_ID
1353 ,G_USER_ID
1354 ,q_detail.primary_interest_code_id
1358
1355 ,null
1356 ,null);
1357 END IF;
1359 IF (q_detail.secondary_interest_code_id IS NOT NULL) THEN
1360 INSERT INTO jty_dea_values_idx_details (
1361 dea_values_idx_details_id
1362 ,dea_values_idx_header_id
1363 ,last_update_date
1364 ,last_updated_by
1365 ,creation_date
1366 ,created_by
1367 ,last_update_login
1368 ,values_col_map
1369 ,input_selectivity
1370 ,input_ordinal_selectivity )
1371 VALUES (
1372 jty_dea_values_idx_details_s.nextval
1373 ,l_header_seq
1374 ,G_SYSDATE
1375 ,G_USER_ID
1376 ,G_SYSDATE
1377 ,G_USER_ID
1378 ,G_USER_ID
1379 ,q_detail.secondary_interest_code_id
1380 ,null
1381 ,null);
1382 END IF;
1383
1384 IF (q_detail.currency_code IS NOT NULL) THEN
1385 INSERT INTO jty_dea_values_idx_details (
1386 dea_values_idx_details_id
1387 ,dea_values_idx_header_id
1388 ,last_update_date
1389 ,last_updated_by
1390 ,creation_date
1391 ,created_by
1392 ,last_update_login
1393 ,values_col_map
1394 ,input_selectivity
1395 ,input_ordinal_selectivity )
1396 VALUES (
1397 jty_dea_values_idx_details_s.nextval
1398 ,l_header_seq
1399 ,G_SYSDATE
1400 ,G_USER_ID
1401 ,G_SYSDATE
1402 ,G_USER_ID
1403 ,G_USER_ID
1404 ,q_detail.currency_code
1405 ,null
1406 ,null);
1407 END IF;
1408
1409 IF (q_detail.value1_id IS NOT NULL) THEN
1410 INSERT INTO jty_dea_values_idx_details (
1411 dea_values_idx_details_id
1412 ,dea_values_idx_header_id
1413 ,last_update_date
1414 ,last_updated_by
1415 ,creation_date
1416 ,created_by
1417 ,last_update_login
1418 ,values_col_map
1419 ,input_selectivity
1420 ,input_ordinal_selectivity )
1421 VALUES (
1422 jty_dea_values_idx_details_s.nextval
1423 ,l_header_seq
1424 ,G_SYSDATE
1425 ,G_USER_ID
1426 ,G_SYSDATE
1427 ,G_USER_ID
1428 ,G_USER_ID
1429 ,q_detail.value1_id
1430 ,null
1431 ,null);
1432 END IF;
1433
1434 IF (q_detail.value2_id IS NOT NULL) THEN
1435 INSERT INTO jty_dea_values_idx_details (
1436 dea_values_idx_details_id
1437 ,dea_values_idx_header_id
1438 ,last_update_date
1439 ,last_updated_by
1440 ,creation_date
1441 ,created_by
1442 ,last_update_login
1443 ,values_col_map
1444 ,input_selectivity
1445 ,input_ordinal_selectivity )
1446 VALUES (
1447 jty_dea_values_idx_details_s.nextval
1448 ,l_header_seq
1449 ,G_SYSDATE
1450 ,G_USER_ID
1451 ,G_SYSDATE
1452 ,G_USER_ID
1453 ,G_USER_ID
1454 ,q_detail.value2_id
1455 ,null
1456 ,null);
1457 END IF;
1458
1459 IF (q_detail.value3_id IS NOT NULL) THEN
1460 INSERT INTO jty_dea_values_idx_details (
1461 dea_values_idx_details_id
1462 ,dea_values_idx_header_id
1463 ,last_update_date
1464 ,last_updated_by
1465 ,creation_date
1466 ,created_by
1467 ,last_update_login
1468 ,values_col_map
1469 ,input_selectivity
1470 ,input_ordinal_selectivity )
1471 VALUES (
1472 jty_dea_values_idx_details_s.nextval
1473 ,l_header_seq
1474 ,G_SYSDATE
1475 ,G_USER_ID
1476 ,G_SYSDATE
1477 ,G_USER_ID
1478 ,G_USER_ID
1479 ,q_detail.value3_id
1480 ,null
1481 ,null);
1482 END IF;
1483
1484 IF (q_detail.value4_id IS NOT NULL) THEN
1485 INSERT INTO jty_dea_values_idx_details (
1486 dea_values_idx_details_id
1487 ,dea_values_idx_header_id
1488 ,last_update_date
1489 ,last_updated_by
1490 ,creation_date
1491 ,created_by
1492 ,last_update_login
1493 ,values_col_map
1494 ,input_selectivity
1495 ,input_ordinal_selectivity )
1496 VALUES (
1497 jty_dea_values_idx_details_s.nextval
1498 ,l_header_seq
1499 ,G_SYSDATE
1500 ,G_USER_ID
1504 ,q_detail.value4_id
1501 ,G_SYSDATE
1502 ,G_USER_ID
1503 ,G_USER_ID
1505 ,null
1506 ,null);
1507 END IF;
1508
1509 IF (q_detail.first_char IS NOT NULL) THEN
1510 INSERT INTO jty_dea_values_idx_details (
1511 dea_values_idx_details_id
1512 ,dea_values_idx_header_id
1513 ,last_update_date
1514 ,last_updated_by
1515 ,creation_date
1516 ,created_by
1517 ,last_update_login
1518 ,values_col_map
1519 ,input_selectivity
1520 ,input_ordinal_selectivity )
1521 VALUES (
1522 jty_dea_values_idx_details_s.nextval
1523 ,l_header_seq
1524 ,G_SYSDATE
1525 ,G_USER_ID
1526 ,G_SYSDATE
1527 ,G_USER_ID
1528 ,G_USER_ID
1529 ,q_detail.first_char
1530 ,null
1531 ,null);
1532 END IF;
1533 END IF; /* end IF (l_no_of_records = 0) */
1534
1535 END LOOP; /* end loop FOR q_detail in qual_details(qual_name.qual_usg_id) */
1536 END LOOP; /* end loop FOR qual_name in quals_used(rel_set.qual_relation_product) */
1537
1538 FOR qual_name in quals_used(p_qual_prd_tbl(i)) LOOP
1539
1540 BEGIN
1541
1542 SELECT dea_attr_factors_id
1543 INTO l_qual_factor_id
1544 FROM jty_dea_attr_factors
1545 WHERE qual_usg_id = qual_name.qual_usg_id
1546 AND rownum < 2;
1547
1548 SELECT JTY_DEA_ATTR_PROD_FACTORS_S.NEXTVAL
1549 INTO l_qual_prod_factor_id
1550 FROM dual;
1551
1552 INSERT INTO JTY_DEA_ATTR_PROD_FACTORS
1553 ( DEA_ATTR_PROD_FACTORS_ID,
1554 DEA_ATTR_PRODUCTS_ID,
1555 DEA_ATTR_FACTORS_ID,
1556 LAST_UPDATE_DATE,
1557 LAST_UPDATED_BY,
1558 CREATION_DATE,
1559 CREATED_BY,
1560 LAST_UPDATE_LOGIN,
1561 TERR_ANALYZE_ID,
1562 OBJECT_VERSION_NUMBER
1563 )
1564 VALUES
1565 ( l_qual_prod_factor_id, --QUAL_PROD_FACTOR_ID,
1566 l_qual_product_id, --QUAL_PRODUCT_ID,
1567 l_qual_factor_id, --QUAL_FACTOR_ID
1568 sysdate, --LAST_UPDATE_DATE,
1569 0, --LAST_UPDATED_BY,
1570 sysdate, --CREATION_DATE,
1571 0, --CREATED_BY,
1572 0, --LAST_UPDATE_LOGIN,
1573 l_terr_analyze_id, --TERR_ANALYZE_ID,
1574 null --OBJECT_VERSION_NUMBER
1575 );
1576
1577 EXCEPTION
1578 WHEN NO_DATA_FOUND THEN
1579 x_msg_data := 'Populating JTY_DEA_ATTR_PROD_FACTORS table : Error no_data_found.';
1580 RAISE FND_API.G_EXC_ERROR;
1581
1582 WHEN OTHERS THEN
1583 x_msg_data := SQLCODE || ' : ' || SQLERRM;
1584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1585 END;
1586
1587 END LOOP; /* end loop FOR qual_name in quals_used(rel_set.qual_relation_product) */
1588 END IF; /* end IF ((p_qual_prd_tbl(i) <> 1) AND (p_qual_prd_tbl(i) IS NOT NULL)) */
1589 END LOOP; /* end loop FOR i IN p_qual_prd_tbl.FIRST .. p_qual_prd_tbl.LAST */
1590 END IF; /* end IF (p_qual_prd_tbl.COUNT > 0) */
1591
1592 /* no need to build the index for the qualifiers with no column mapped to TRANS table */
1593 update JTY_DEA_ATTR_FACTORS
1594 set UPDATE_SELECTIVITY_FLAG = 'N',
1595 USE_TAE_COL_IN_INDEX_FLAG = 'N'
1596 where TAE_COL_MAP is null;
1597
1598 -- debug message
1599 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1600 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1601 'jtf.plsql.JTY_TAE_CONTROL_PVT.classify_dea_territories.end',
1602 'End of the procedure JTY_TAE_CONTROL_PVT.classify_dea_territories');
1603 END IF;
1604
1605 EXCEPTION
1606 WHEN NO_DATA_FOUND THEN
1607 x_return_status := FND_API.G_RET_STS_ERROR;
1608 x_msg_count := 1;
1609 RETCODE := 2;
1610 ERRBUF := x_msg_data;
1611 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1612 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1613 'jtf.plsql.JTY_TAE_CONTROL_PVT.classify_dea_territories.no_data_found',
1614 x_msg_data);
1615 END IF;
1616
1617 WHEN FND_API.G_EXC_ERROR THEN
1618 x_return_status := FND_API.G_RET_STS_ERROR;
1619 x_msg_count := 1;
1620 RETCODE := 2;
1621 ERRBUF := x_msg_data;
1622 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1623 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1624 'jtf.plsql.JTY_TAE_CONTROL_PVT.classify_dea_territories.g_exc_error',
1625 x_msg_data);
1626 END IF;
1627
1628 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1630 x_msg_count := 1;
1631 RETCODE := 2;
1632 ERRBUF := x_msg_data;
1633 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1634 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1635 'jtf.plsql.jtf_tae_cpntrol_pvt.classify_dea_territories.g_exc_unexpected_error',
1636 x_msg_data);
1637 END IF;
1638
1642 x_msg_count := 1;
1639 WHEN OTHERS THEN
1640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1641 x_msg_data := SQLCODE || ' : ' || SQLERRM;
1643 RETCODE := 2;
1644 ERRBUF := x_msg_data;
1645 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1646 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1647 'jtf.plsql.JTY_TAE_CONTROL_PVT.classify_dea_territories.other',
1648 substr(x_msg_data, 1, 4000));
1649 END IF;
1650
1651 END Classify_dea_Territories;
1652
1653 PROCEDURE reduce_deaval_idx_set
1654 ( p_source_id IN NUMBER,
1655 x_Return_Status OUT NOCOPY VARCHAR2)
1656 IS
1657
1658 S_element_ord_subset_L_count NUMBER;
1659 S_subset_L VARCHAR2(1);
1660
1661 CURSOR all_sets(cl_source_id number) is
1662 SELECT A.dea_values_idx_header_id, count(*) num_components
1663 FROM jty_dea_values_idx_header A,
1664 jty_dea_values_idx_details B
1665 WHERE A.source_id = cl_source_id
1666 AND A.dea_values_idx_header_id = B.dea_values_idx_header_id
1667 AND B.values_col_map is not null
1668 GROUP BY A.dea_values_idx_header_id
1669 ORDER BY 2;
1670
1671 CURSOR larger_or_eq_sets( cl_size IN NUMBER
1672 , cl_source_id IN NUMBER
1673 , cl_dea_values_idx_header_id IN NUMBER) is
1674 SELECT * FROM (
1675 SELECT A.dea_values_idx_header_id, count(*) num_components
1676 FROM jty_dea_values_idx_header A,
1677 jty_dea_values_idx_details B
1678 WHERE A.source_id = cl_source_id
1679 AND A.dea_values_idx_header_id <> cl_dea_values_idx_header_id
1680 AND A.dea_values_idx_header_id = B.dea_values_idx_header_id
1681 AND B.values_col_map is not null
1682 AND A.build_index_flag = 'Y'
1683 GROUP BY A.dea_values_idx_header_id )
1684 WHERE num_components >= cl_size
1685 ORDER BY 2 DESC;
1686
1687
1688 BEGIN
1689 -- debug message
1690 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1691 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1692 'jtf.plsql.JTY_TAE_CONTROL_PVT.reduce_deaval_idx_set.start',
1693 'Start of the procedure JTY_TAE_CONTROL_PVT.reduce_deaval_idx_set');
1694 END IF;
1695
1696 x_return_status := FND_API.G_RET_STS_SUCCESS;
1697 S_element_ord_subset_L_count := 0;
1698 S_subset_L := 'N';
1699
1700 FOR cl_set_S in all_sets( p_source_id ) LOOP
1701 S_subset_L := 'N';
1702
1703 FOR cl_set_L IN larger_or_eq_sets( cl_set_S.num_components
1704 , p_source_id
1705 , cl_set_s.dea_values_idx_header_id) LOOP
1706
1707 SELECT COUNT(*)
1708 INTO S_element_ord_subset_L_count
1709 FROM (
1710 SELECT rownum row_count, values_col_map, input_selectivity
1711 FROM (
1712 SELECT B.values_col_map, B.input_selectivity
1713 FROM jty_dea_values_idx_details B
1714 WHERE B.dea_values_idx_header_id = cl_set_S.dea_values_idx_header_id
1715 AND B.values_col_map IS NOT NULL
1716 ORDER BY B.input_selectivity )) S,
1717 (
1718 SELECT rownum row_count, values_col_map, input_selectivity
1719 FROM (
1720 SELECT B.values_col_map, B.input_selectivity
1721 FROM jty_dea_values_idx_details B
1722 WHERE B.dea_values_idx_header_id = cl_set_L.dea_values_idx_header_id
1723 AND B.values_col_map IS NOT NULL
1724 ORDER BY B.input_selectivity )) L
1725 WHERE S.values_col_map = L.values_col_map
1726 AND S.row_count = L.row_count;
1727
1728 IF S_element_ord_subset_L_count = cl_set_S.num_components THEN
1729 S_subset_L := 'Y';
1730 exit;
1731 ELSE
1732 S_subset_L := 'N';
1733 END IF;
1734
1735 END LOOP; /* end loop FOR cl_set_L IN larger_or_eq_sets */
1736
1737 IF S_subset_L = 'Y' THEN
1738 UPDATE jty_dea_values_idx_header
1739 SET BUILD_INDEX_FLAG = 'N'
1740 WHERE dea_values_idx_header_id = cl_set_S.dea_values_idx_header_id;
1741
1742 END IF;
1743
1744 END LOOP; /* end loop FOR cl_set_S in all_sets */
1745
1746 COMMIT;
1747
1748 -- debug message
1749 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1750 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1751 'jtf.plsql.JTY_TAE_CONTROL_PVT.reduce_deaval_idx_set.end',
1752 'End of the procedure JTY_TAE_CONTROL_PVT.reduce_deaval_idx_set');
1753 END IF;
1754
1755 EXCEPTION
1756
1757 WHEN OTHERS THEN
1758 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1759 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1760 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1761 'jtf.plsql.JTY_TAE_CONTROL_PVT.reduce_deaval_idx_set.other',
1762 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
1763 END IF;
1764
1765 end reduce_deaval_idx_set;
1766
1767 PROCEDURE reduce_dnmval_idx_set
1768 ( p_source_id IN NUMBER,
1769 p_mode IN VARCHAR2,
1770 x_Return_Status OUT NOCOPY VARCHAR2)
1771 IS
1772
1773 S_element_ord_subset_L_count NUMBER;
1774 S_subset_L VARCHAR2(1);
1775
1776 CURSOR all_sets(cl_source_id number) is
1777 SELECT A.terr_values_idx_header_id, count(*) num_components
1778 FROM jty_terr_values_idx_header A,
1779 jty_terr_values_idx_details B
1780 WHERE A.source_id = cl_source_id
1781 AND A.build_index_flag = 'Y'
1785 ORDER BY 2;
1782 AND A.terr_values_idx_header_id = B.terr_values_idx_header_id
1783 AND B.values_col_map is not null
1784 GROUP BY A.terr_values_idx_header_id
1786
1787 CURSOR larger_or_eq_sets( cl_size IN NUMBER
1788 , cl_source_id IN NUMBER
1789 , cl_terr_values_idx_header_id IN NUMBER) is
1790 SELECT * FROM (
1791 SELECT A.terr_values_idx_header_id, count(*) num_components
1792 FROM jty_terr_values_idx_header A,
1793 jty_terr_values_idx_details B
1794 WHERE A.source_id = cl_source_id
1795 AND A.terr_values_idx_header_id <> cl_terr_values_idx_header_id
1796 AND A.terr_values_idx_header_id = B.terr_values_idx_header_id
1797 AND B.values_col_map is not null
1798 AND A.build_index_flag = 'Y'
1799 GROUP BY A.terr_values_idx_header_id )
1800 WHERE num_components >= cl_size
1801 ORDER BY 2 DESC;
1802
1803
1804 BEGIN
1805 -- debug message
1806 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1807 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1808 'jtf.plsql.JTY_TAE_CONTROL_PVT.reduce_dnmval_idx_set.start',
1809 'Start of the procedure JTY_TAE_CONTROL_PVT.reduce_dnmval_idx_set');
1810 END IF;
1811
1812 x_return_status := FND_API.G_RET_STS_SUCCESS;
1813 S_element_ord_subset_L_count := 0;
1814 S_subset_L := 'N';
1815
1816 /* mark the indexes as obsolete for qualifers and qualifier combinations */
1817 /* that have beeen marked deleted in incremental mode */
1818 IF (p_mode = 'INCREMENTAL') THEN
1819 UPDATE jty_terr_values_idx_header
1820 SET build_index_flag = 'N'
1821 WHERE source_id = p_source_id
1822 AND delete_flag = 'Y';
1823 END IF;
1824
1825 FOR cl_set_S in all_sets( p_source_id ) LOOP
1826 S_subset_L := 'N';
1827
1828 FOR cl_set_L IN larger_or_eq_sets( cl_set_S.num_components
1829 , p_source_id
1830 , cl_set_s.terr_values_idx_header_id) LOOP
1831
1832 SELECT COUNT(*)
1833 INTO S_element_ord_subset_L_count
1834 FROM (
1835 SELECT rownum row_count, values_col_map, input_selectivity
1836 FROM (
1837 SELECT B.values_col_map, B.input_selectivity
1838 FROM jty_terr_values_idx_details B
1839 WHERE B.terr_values_idx_header_id = cl_set_S.terr_values_idx_header_id
1840 AND B.values_col_map IS NOT NULL
1841 ORDER BY B.input_selectivity )) S,
1842 (
1843 SELECT rownum row_count, values_col_map, input_selectivity
1844 FROM (
1845 SELECT B.values_col_map, B.input_selectivity
1846 FROM jty_terr_values_idx_details B
1847 WHERE B.terr_values_idx_header_id = cl_set_L.terr_values_idx_header_id
1848 AND B.values_col_map IS NOT NULL
1849 ORDER BY B.input_selectivity )) L
1850 WHERE S.values_col_map = L.values_col_map
1851 AND S.row_count = L.row_count;
1852
1853 IF S_element_ord_subset_L_count = cl_set_S.num_components THEN
1854 S_subset_L := 'Y';
1855 exit;
1856 ELSE
1857 S_subset_L := 'N';
1858 END IF;
1859
1860 END LOOP; /* end loop FOR cl_set_L IN larger_or_eq_sets */
1861
1862 IF S_subset_L = 'Y' THEN
1863 UPDATE jty_terr_values_idx_header
1864 SET BUILD_INDEX_FLAG = 'N'
1865 WHERE terr_values_idx_header_id = cl_set_S.terr_values_idx_header_id;
1866
1867 END IF;
1868
1869 END LOOP; /* end loop FOR cl_set_S in all_sets */
1870
1871 COMMIT;
1872
1873 -- debug message
1874 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1875 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1876 'jtf.plsql.JTY_TAE_CONTROL_PVT.reduce_dnmval_idx_set.end',
1877 'End of the procedure JTY_TAE_CONTROL_PVT.reduce_dnmval_idx_set');
1878 END IF;
1879
1880 EXCEPTION
1881
1882 WHEN OTHERS THEN
1883 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1884 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1885 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1886 'jtf.plsql.JTY_TAE_CONTROL_PVT.reduce_dnmval_idx_set.other',
1887 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
1888 END IF;
1889
1890 end reduce_dnmval_idx_set;
1891
1892
1893 PROCEDURE Reduce_TX_OIN_Index_Set
1894 ( p_Api_Version_Number IN NUMBER,
1895 p_Init_Msg_List IN VARCHAR2,
1896 p_source_id IN NUMBER,
1897 p_trans_id IN NUMBER,
1898 x_Return_Status OUT NOCOPY VARCHAR2,
1899 x_Msg_Count OUT NOCOPY NUMBER,
1900 x_Msg_Data OUT NOCOPY VARCHAR2)
1901 IS
1902
1903 l_first_char_flag VARCHAR2(1);
1904 S_element_ord_subset_L_count NUMBER;
1905 S_subset_L VARCHAR2(1);
1906 l_first_char_flag_count NUMBER;
1907
1908 CURSOR all_sets(cl_source_id number, cl_trans_id number) is
1909 SELECT p.trans_object_type_id, count(*) num_components, p.qual_product_id qual_product_id, p.relation_product
1910 FROM JTF_TAE_QUAL_products p,
1911 JTF_TAE_QUAL_prod_factors pf,
1912 JTF_TAE_QUAL_factors f
1913 WHERE p.qual_product_id = pf.qual_product_id
1914 AND pf.qual_factor_id = f.qual_factor_id
1915 AND f.tae_col_map is not null
1916 AND p.source_id = cl_source_id
1917 AND p.trans_object_type_id = cl_trans_id
1918 GROUP BY p.trans_object_type_id, p.qual_product_id, p.relation_product
1919 ORDER BY p.relation_product;
1920
1921 CURSOR larger_or_eq_sets( cl_size NUMBER
1925 SELECT * FROM (
1922 , cl_relation_product NUMBER
1923 , cl_source_id NUMBER
1924 , cl_trans_id NUMBER ) is
1926 SELECT count(*) num_components, p.qual_product_id qual_product_id, p.relation_product
1927 FROM JTF_TAE_QUAL_products p,
1928 JTF_TAE_QUAL_prod_factors pf
1929 WHERE p.qual_product_id = pf.qual_product_id
1930 AND p.source_id = cl_source_id
1931 AND p.trans_object_type_id = cl_trans_id
1932 GROUP BY p.qual_product_id, p.relation_product )
1933 WHERE num_components >= cl_size
1934 AND relation_product > cl_relation_product
1935 ORDER BY 1 DESC, qual_product_id ASC;
1936
1937 CURSOR all_empty_column_indexes(cl_source_id number, cl_trans_id number) is
1938 SELECT p.trans_object_type_id, p.qual_product_id, p.relation_product
1939 FROM JTF_TAE_QUAL_products p
1940 WHERE NOT EXISTS (SELECT *
1941 FROM JTF_TAE_QUAL_products ip,
1942 JTF_TAE_QUAL_prod_factors ipf,
1943 JTF_TAE_QUAL_factors ifc
1944 WHERE use_tae_col_in_index_flag = 'Y'
1945 AND ip.qual_product_id = ipf.qual_product_id
1946 AND ipf.qual_factor_id = ifc.qual_factor_id
1947 AND ip.qual_product_id = p.qual_product_id)
1948 AND p.source_id = cl_source_id
1949 AND p.trans_object_type_id = cl_trans_id;
1950
1951
1952 BEGIN
1953 -- debug message
1954 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1955 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1956 'jtf.plsql.JTY_TAE_CONTROL_PVT.Reduce_TX_OIN_Index_Set.start',
1957 'Start of the procedure JTY_TAE_CONTROL_PVT.Reduce_TX_OIN_Index_Set');
1958 END IF;
1959
1960 x_return_status := FND_API.G_RET_STS_SUCCESS;
1961 l_first_char_flag := 'N';
1962 S_element_ord_subset_L_count := 0;
1963 S_subset_L := 'N';
1964
1965 FOR cl_set_S in all_sets( p_source_id
1966 , p_trans_id ) LOOP
1967 S_subset_L := 'N';
1968
1969 FOR cl_set_L IN larger_or_eq_sets( cl_set_S.num_components
1970 , cl_set_S.relation_product
1971 , p_source_id
1972 , p_trans_id ) LOOP
1973
1974 SELECT COUNT(*)
1975 INTO S_element_ord_subset_L_count
1976 FROM (
1977 SELECT rownum row_count, tae_col_map, input_selectivity
1978 FROM (
1979 SELECT DISTINCT p.relation_product, f.tae_col_map, f.input_selectivity
1980 FROM JTF_TAE_QUAL_products p,
1981 JTF_TAE_QUAL_prod_factors pf,
1982 JTF_TAE_QUAL_factors f
1983 WHERE f.qual_factor_id = pf.qual_factor_id
1984 AND pf.qual_product_id = p.qual_product_id
1985 AND p.relation_product = cl_set_S.relation_product
1986 AND f.tae_col_map is not null
1987 AND p.source_id = p_source_id
1988 AND p.trans_object_type_id = p_trans_id
1989 ORDER BY input_selectivity )) S,
1990 (
1991 SELECT rownum row_count, tae_col_map, input_selectivity
1992 FROM (
1993 SELECT DISTINCT p.relation_product, f.tae_col_map, f.input_selectivity
1994 FROM JTF_TAE_QUAL_products p,
1995 JTF_TAE_QUAL_prod_factors pf,
1996 JTF_TAE_QUAL_factors f
1997 WHERE f.qual_factor_id = pf.qual_factor_id
1998 AND pf.qual_product_id = p.qual_product_id
1999 AND p.relation_product = cl_set_L.relation_product
2000 AND f.tae_col_map is not null
2001 AND p.source_id = p_source_id
2002 AND p.trans_object_type_id = p_trans_id
2003 ORDER BY input_selectivity)) L
2004 WHERE S.tae_col_map = L.tae_col_map
2005 AND S.row_count = L.row_count;
2006
2007 IF S_element_ord_subset_L_count = cl_set_S.num_components THEN
2008 S_subset_L := 'Y';
2009 exit;
2010 ELSE
2011 S_subset_L := 'N';
2012 END IF;
2013
2014 END LOOP; /* end loop FOR cl_set_L IN larger_or_eq_sets */
2015
2016 -- set FIRST_CHAR_FLAG for created index
2017 SELECT count(*)
2018 INTO l_first_char_flag_count
2019 FROM (
2020 SELECT qual_usg_id, tae_col_map, rownum row_count
2021 FROM (
2022 SELECT f.qual_usg_id, f.relation_factor, f.tae_col_map
2023 FROM JTF_TAE_QUAL_prod_factors pf,
2024 JTF_TAE_QUAL_factors f
2025 WHERE pf.qual_factor_id = f.qual_factor_id
2026 AND pf.qual_product_id = cl_set_S.qual_product_id
2027 ORDER BY f.input_selectivity)) ilv1,
2028 (
2029 SELECT qual_usg_id, 1 row_count
2030 FROM jtf_qual_usgs_all
2031 WHERE org_id = -3113
2032 AND seeded_qual_id = -1012) ilv2
2033 WHERE ilv1.qual_usg_id = ilv2.qual_usg_id
2034 AND ilv1.row_count = ilv2.row_count;
2035
2036 IF l_first_char_flag_count > 0 THEN
2037 l_first_char_flag := 'Y';
2038 ELSE
2039 l_first_char_flag := 'N';
2040 END IF;
2041
2042 IF S_subset_L = 'Y' THEN
2043 UPDATE JTF_TAE_QUAL_PRODUCTS
2044 SET BUILD_INDEX_FLAG = 'N', FIRST_CHAR_FLAG = l_first_char_flag
2045 WHERE qual_product_id = cl_set_S.qual_product_id
2046 AND RELATION_PRODUCT NOT IN (4841, 324347);
2047
2048 UPDATE JTF_TAE_QUAL_PRODUCTS
2049 SET FIRST_CHAR_FLAG = l_first_char_flag
2050 WHERE qual_product_id = cl_set_S.qual_product_id
2051 AND RELATION_PRODUCT IN (4841, 324347);
2052 ELSE
2053 UPDATE JTF_TAE_QUAL_PRODUCTS
2057
2054 SET BUILD_INDEX_FLAG = 'Y', FIRST_CHAR_FLAG = l_first_char_flag
2055 WHERE qual_product_id = cl_set_S.qual_product_id;
2056 END IF;
2058 UPDATE JTF_TAE_QUAL_PRODUCTS
2059 SET FIRST_CHAR_FLAG = 'Y'
2060 WHERE qual_product_id = cl_set_S.qual_product_id
2061 AND RELATION_PRODUCT = 353393;
2062
2063 END LOOP; /* end loop FOR cl_set_S in all_sets */
2064
2065 -- Set reduction complete
2066 -- Set build_index_flag = 'N' for all empty column indexes combinations
2067 FOR empty_column_index in all_empty_column_indexes(p_source_id, p_trans_id) LOOP
2068 UPDATE JTF_TAE_QUAL_PRODUCTS p
2069 SET BUILD_INDEX_FLAG = 'N'
2070 WHERE p.qual_product_id = empty_column_index.qual_product_id;
2071 END LOOP;
2072
2073 COMMIT;
2074
2075 -- debug message
2076 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2077 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2078 'jtf.plsql.JTY_TAE_CONTROL_PVT.Reduce_TX_OIN_Index_Set.end',
2079 'End of the procedure JTY_TAE_CONTROL_PVT.Reduce_TX_OIN_Index_Set');
2080 END IF;
2081
2082 EXCEPTION
2083
2084 WHEN OTHERS THEN
2085 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2086 x_msg_data := SQLCODE || ' : ' || SQLERRM;
2087 x_msg_count := 1;
2088 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2089 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2090 'jtf.plsql.JTY_TAE_CONTROL_PVT.Reduce_TX_OIN_Index_Set.other',
2091 substr(x_msg_data, 1, 4000));
2092 END IF;
2093
2094 end Reduce_TX_OIN_Index_Set;
2095
2096 PROCEDURE dea_Reduce_TX_OIN_Index_Set
2097 ( p_Api_Version_Number IN NUMBER,
2098 p_Init_Msg_List IN VARCHAR2,
2099 p_source_id IN NUMBER,
2100 p_trans_id IN NUMBER,
2101 x_Return_Status OUT NOCOPY VARCHAR2,
2102 x_Msg_Count OUT NOCOPY NUMBER,
2103 x_Msg_Data OUT NOCOPY VARCHAR2)
2104 IS
2105
2106 l_first_char_flag VARCHAR2(1);
2107 S_element_ord_subset_L_count NUMBER;
2108 S_subset_L VARCHAR2(1);
2109 l_first_char_flag_count NUMBER;
2110
2111 CURSOR all_sets(cl_source_id number, cl_trans_id number) is
2112 SELECT p.trans_type_id, count(*) num_components, p.dea_attr_products_id dea_attr_products_id, p.attr_relation_product
2113 FROM jty_dea_attr_products p,
2114 jty_dea_attr_prod_factors pf,
2115 jty_dea_attr_factors f
2116 WHERE p.dea_attr_products_id = pf.dea_attr_products_id
2117 AND pf.dea_attr_factors_id = f.dea_attr_factors_id
2118 AND f.tae_col_map is not null
2119 AND p.source_id = cl_source_id
2120 AND p.trans_type_id = cl_trans_id
2121 GROUP BY p.trans_type_id, p.dea_attr_products_id, p.attr_relation_product
2122 ORDER BY p.attr_relation_product;
2123
2124 CURSOR larger_or_eq_sets( cl_size NUMBER
2125 , cl_relation_product NUMBER
2126 , cl_source_id NUMBER
2127 , cl_trans_id NUMBER ) is
2128 SELECT * FROM (
2129 SELECT count(*) num_components, p.dea_attr_products_id dea_attr_products_id, p.attr_relation_product
2130 FROM jty_dea_attr_products p,
2131 jty_dea_attr_prod_factors pf
2132 WHERE p.dea_attr_products_id = pf.dea_attr_products_id
2133 AND p.source_id = cl_source_id
2134 AND p.trans_type_id = cl_trans_id
2135 GROUP BY p.dea_attr_products_id, p.attr_relation_product )
2136 WHERE num_components >= cl_size
2137 AND attr_relation_product > cl_relation_product
2138 ORDER BY 1 DESC, dea_attr_products_id ASC;
2139
2140 CURSOR all_empty_column_indexes(cl_source_id number, cl_trans_id number) is
2141 SELECT p.trans_type_id, p.dea_attr_products_id, p.attr_relation_product
2142 FROM jty_dea_attr_products p
2143 WHERE NOT EXISTS (SELECT *
2144 FROM jty_dea_attr_products ip,
2145 jty_dea_attr_prod_factors ipf,
2146 jty_dea_attr_factors ifc
2147 WHERE use_tae_col_in_index_flag = 'Y'
2148 AND ip.dea_attr_products_id = ipf.dea_attr_products_id
2149 AND ipf.dea_attr_factors_id = ifc.dea_attr_factors_id
2150 AND ip.dea_attr_products_id = p.dea_attr_products_id)
2151 AND p.source_id = cl_source_id
2152 AND p.trans_type_id = cl_trans_id;
2153
2154 BEGIN
2155 -- debug message
2156 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2157 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2158 'jtf.plsql.JTY_TAE_CONTROL_PVT.dea_Reduce_TX_OIN_Index_Set.start',
2159 'Start of the procedure JTY_TAE_CONTROL_PVT.dea_Reduce_TX_OIN_Index_Set');
2160 END IF;
2161
2162 x_return_status := FND_API.G_RET_STS_SUCCESS;
2163 l_first_char_flag := 'N';
2164 S_element_ord_subset_L_count := 0;
2165 S_subset_L := 'N';
2166
2167 FOR cl_set_S in all_sets( p_source_id
2168 , p_trans_id ) LOOP
2169 S_subset_L := 'N';
2170
2171 FOR cl_set_L IN larger_or_eq_sets( cl_set_S.num_components
2172 , cl_set_S.attr_relation_product
2173 , p_source_id
2174 , p_trans_id ) LOOP
2175
2176 SELECT COUNT(*)
2177 INTO S_element_ord_subset_L_count
2178 FROM (
2179 SELECT rownum row_count, tae_col_map, input_selectivity
2180 FROM (
2181 SELECT DISTINCT p.attr_relation_product, f.tae_col_map, f.input_selectivity
2182 FROM jty_dea_attr_products p,
2183 jty_dea_attr_prod_factors pf,
2184 jty_dea_attr_factors f
2185 WHERE f.dea_attr_factors_id = pf.dea_attr_factors_id
2189 AND p.source_id = p_source_id
2186 AND pf.dea_attr_products_id = p.dea_attr_products_id
2187 AND p.attr_relation_product = cl_set_S.attr_relation_product
2188 AND f.tae_col_map is not null
2190 AND p.trans_type_id = p_trans_id
2191 ORDER BY input_selectivity )) S,
2192 (
2193 SELECT rownum row_count, tae_col_map, input_selectivity
2194 FROM (
2195 SELECT DISTINCT p.attr_relation_product, f.tae_col_map, f.input_selectivity
2196 FROM jty_dea_attr_products p,
2197 jty_dea_attr_prod_factors pf,
2198 jty_dea_attr_factors f
2199 WHERE f.dea_attr_factors_id = pf.dea_attr_factors_id
2200 AND pf.dea_attr_products_id = p.dea_attr_products_id
2201 AND p.attr_relation_product = cl_set_L.attr_relation_product
2202 AND f.tae_col_map is not null
2203 AND p.source_id = p_source_id
2204 AND p.trans_type_id = p_trans_id
2205 ORDER BY input_selectivity)) L
2206 WHERE S.tae_col_map = L.tae_col_map
2207 AND S.row_count = L.row_count;
2208
2209 IF S_element_ord_subset_L_count = cl_set_S.num_components THEN
2210 S_subset_L := 'Y';
2211 exit;
2212 ELSE
2213 S_subset_L := 'N';
2214 END IF;
2215
2216 END LOOP; /* end loop FOR cl_set_L IN larger_or_eq_sets */
2217
2218 -- set FIRST_CHAR_FLAG for created index
2219 SELECT count(*)
2220 INTO l_first_char_flag_count
2221 FROM (
2222 SELECT qual_usg_id, tae_col_map, rownum row_count
2223 FROM (
2224 SELECT f.qual_usg_id, f.relation_factor, f.tae_col_map
2225 FROM jty_dea_attr_prod_factors pf,
2226 jty_dea_attr_factors f
2227 WHERE pf.dea_attr_factors_id = f.dea_attr_factors_id
2228 AND pf.dea_attr_products_id = cl_set_S.dea_attr_products_id
2229 ORDER BY f.input_selectivity)) ilv1,
2230 (
2231 SELECT qual_usg_id, 1 row_count
2232 FROM jtf_qual_usgs_all
2233 WHERE org_id = -3113
2234 AND seeded_qual_id = -1012) ilv2
2235 WHERE ilv1.qual_usg_id = ilv2.qual_usg_id
2236 AND ilv1.row_count = ilv2.row_count;
2237
2238 IF l_first_char_flag_count > 0 THEN
2239 l_first_char_flag := 'Y';
2240 ELSE
2241 l_first_char_flag := 'N';
2242 END IF;
2243
2244 IF S_subset_L = 'Y' THEN
2245 UPDATE JTY_DEA_ATTR_PRODUCTS
2246 SET BUILD_INDEX_FLAG = 'N', FIRST_CHAR_FLAG = l_first_char_flag
2247 WHERE dea_attr_products_id = cl_set_S.dea_attr_products_id
2248 AND attr_relation_product NOT IN (4841, 324347);
2249
2250 UPDATE JTY_DEA_ATTR_PRODUCTS
2251 SET FIRST_CHAR_FLAG = l_first_char_flag
2252 WHERE dea_attr_products_id = cl_set_S.dea_attr_products_id
2253 AND attr_relation_product IN (4841, 324347);
2254 ELSE
2255 UPDATE JTY_DEA_ATTR_PRODUCTS
2256 SET BUILD_INDEX_FLAG = 'Y', FIRST_CHAR_FLAG = l_first_char_flag
2257 WHERE dea_attr_products_id = cl_set_S.dea_attr_products_id;
2258 END IF;
2259
2260 UPDATE JTY_DEA_ATTR_PRODUCTS
2261 SET FIRST_CHAR_FLAG = 'Y'
2262 WHERE dea_attr_products_id = cl_set_S.dea_attr_products_id
2263 AND attr_relation_product = 353393;
2264
2265 END LOOP; /* end loop FOR cl_set_S in all_sets */
2266
2267 -- Set reduction complete
2268 -- Set build_index_flag = 'N' for all empty column indexes combinations
2269 FOR empty_column_index in all_empty_column_indexes(p_source_id, p_trans_id) LOOP
2270 UPDATE JTY_DEA_ATTR_PRODUCTS p
2271 SET BUILD_INDEX_FLAG = 'N'
2272 WHERE p.dea_attr_products_id = empty_column_index.dea_attr_products_id;
2273 END LOOP;
2274
2275 COMMIT;
2276
2277 -- debug message
2278 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2279 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2280 'jtf.plsql.JTY_TAE_CONTROL_PVT.dea_Reduce_TX_OIN_Index_Set.end',
2281 'End of the procedure JTY_TAE_CONTROL_PVT.dea_Reduce_TX_OIN_Index_Set');
2282 END IF;
2283
2284 EXCEPTION
2285 WHEN OTHERS THEN
2286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2287 x_msg_data := SQLCODE || ' : ' || SQLERRM;
2288 x_msg_count := 1;
2289 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2290 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2291 'jtf.plsql.JTY_TAE_CONTROL_PVT.dea_Reduce_TX_OIN_Index_Set.other',
2292 substr(x_msg_data, 1, 4000));
2293 END IF;
2294
2295 end dea_Reduce_TX_OIN_Index_Set;
2296
2297 PROCEDURE Decompose_Terr_Defns
2298 ( p_Api_Version_Number IN NUMBER,
2299 p_Init_Msg_List IN VARCHAR2,
2300 p_trans_target IN VARCHAR2,
2301 p_classify_terr_comb IN VARCHAR2,
2302 p_process_tx_oin_sel IN VARCHAR2,
2303 p_generate_indexes IN VARCHAR2,
2304 p_source_id IN NUMBER,
2305 p_trans_id IN NUMBER,
2306 p_program_name IN VARCHAR2,
2307 p_mode IN VARCHAR2,
2308 x_Return_Status OUT NOCOPY VARCHAR2,
2309 x_Msg_Count OUT NOCOPY NUMBER,
2310 x_Msg_Data OUT NOCOPY VARCHAR2,
2311 ERRBUF OUT NOCOPY VARCHAR2,
2312 RETCODE OUT NOCOPY VARCHAR2 )
2313
2314 IS
2315 l_selectivity_return_val NUMBER;
2316
2317 BEGIN
2318 -- debug message
2319 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2320 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2324
2321 'jtf.plsql.JTY_TAE_CONTROL_PVT.decompose_terr_defns.begin',
2322 'Start of the procedure JTY_TAE_CONTROL_PVT.decompose_terr_defns');
2323 END IF;
2325 x_return_status := FND_API.G_RET_STS_SUCCESS;
2326
2327 -- ANALYSIS OF TERRITORY DEFINITION FOR DYN PACKAGE GENERATION
2328 IF (p_classify_terr_comb = 'Y') THEN
2329 NULL;
2330 END IF;
2331
2332 -- OPTIMIZATION OF DATABASE OBJECTS
2333 IF ((p_process_tx_oin_sel = 'Y') OR (p_process_tx_oin_sel = 'R')) THEN
2334 IF (p_process_tx_oin_sel = 'Y') THEN
2335 -- Analyze Selectivity and Get ordinals
2336 IF (p_mode = 'DATE EFFECTIVE') THEN
2337 jty_tae_index_creation_pvt.dea_selectivity(p_trans_target, x_return_status);
2338
2339 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2340 x_msg_data := 'API jty_tae_index_creation_pvt.dea_selectivity has failed';
2341 RAISE FND_API.G_EXC_ERROR;
2342 END IF;
2343 ELSE
2344 jty_tae_index_creation_pvt.selectivity(p_trans_target, p_mode, null, x_return_status);
2345
2346 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2347 x_msg_data := 'API jty_tae_index_creation_pvt.selectivity has failed';
2348 RAISE FND_API.G_EXC_ERROR;
2349 END IF;
2350 END IF; /* end IF (p_mode = 'DATE EFFECTIVE') */
2351
2352 END IF; /* end IF (p_process_tx_oin_sel = 'Y') */
2353
2354 IF (p_mode = 'DATE EFFECTIVE') THEN
2355 -- Reduce Sets
2356 dea_Reduce_TX_OIN_Index_Set
2357 ( p_Api_Version_Number => 1.0,
2358 p_Init_Msg_List => FND_API.G_FALSE,
2359 p_source_id => p_source_id,
2360 p_trans_id => p_trans_id,
2361 x_Return_Status => x_return_status,
2362 x_Msg_Count => x_msg_count,
2363 x_Msg_Data => x_msg_data
2364 );
2365
2366 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2367 x_msg_data := 'CALL to JTY_TAE_CONTROL_PVT.dea_Reduce_TX_OIN_Index_Set API has failed.';
2368 RAISE FND_API.G_EXC_ERROR;
2369 END IF;
2370 ELSE
2371 -- Reduce Sets
2372 Reduce_TX_OIN_Index_Set
2373 ( p_Api_Version_Number => 1.0,
2374 p_Init_Msg_List => FND_API.G_FALSE,
2375 p_source_id => p_source_id,
2376 p_trans_id => p_trans_id,
2377 x_Return_Status => x_return_status,
2378 x_Msg_Count => x_msg_count,
2379 x_Msg_Data => x_msg_data
2380 );
2381
2382 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2383 x_msg_data := 'CALL to JTY_TAE_CONTROL_PVT.Reduce_TX_OIN_Index_Set API has failed.';
2384 RAISE FND_API.G_EXC_ERROR;
2385 END IF;
2386 END IF; /* end IF (p_mode = 'DATE EFFECTIVE') */
2387
2388 -- debug message
2389 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2390 FND_LOG.string(FND_LOG.LEVEL_EVENT,
2391 'jtf.plsql.JTY_TAE_CONTROL_PVT.decompose_terr_defns.reduce_tx_oin_index_set',
2392 'API JTY_TAE_CONTROL_PVT.reduce_tx_oin_index_set ended with success');
2393 END IF;
2394 END IF; /* end IF ((p_process_tx_oin_sel = 'Y') OR (p_process_tx_oin_sel = 'R')) */
2395
2396 IF (p_generate_indexes = 'Y') THEN
2397 jty_tae_index_creation_pvt.drop_table_indexes(
2398 p_table_name => p_trans_target
2399 , x_return_status => x_return_status);
2400
2401 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2402 x_msg_data := 'CALL to JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed.';
2403 RAISE FND_API.G_EXC_ERROR;
2404 END IF;
2405
2406 -- Build Indexes
2407 IF (p_mode = 'DATE EFFECTIVE') THEN
2408 jty_tae_index_creation_pvt.create_index( p_trans_target
2409 , p_trans_id
2410 , p_source_id
2411 , p_program_name
2412 , p_mode
2413 , x_return_status
2414 , 'DEA_TRANS');
2415 ELSE
2416 jty_tae_index_creation_pvt.create_index( p_trans_target
2417 , p_trans_id
2418 , p_source_id
2419 , p_program_name
2420 , p_mode
2421 , x_return_status
2422 , 'TRANS');
2423 END IF;
2424
2425 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2426 x_msg_data := 'CALL to JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed.';
2427 RAISE FND_API.G_EXC_ERROR;
2428 END IF;
2429
2430 -- debug message
2431 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2432 FND_LOG.string(FND_LOG.LEVEL_EVENT,
2433 'jtf.plsql.JTY_TAE_CONTROL_PVT.decompose_terr_defns.create_index',
2434 'API jty_tae_index_creation_pvt.create_index ended with success');
2435 END IF;
2436
2437 END IF; /* end IF (p_generate_indexes = 'Y') */
2438
2439 -- debug message
2440 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2441 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2442 'jtf.plsql.JTY_TAE_CONTROL_PVT.decompose_terr_defns.end',
2443 'End of the procedure JTY_TAE_CONTROL_PVT.decompose_terr_defns');
2444 END IF;
2445
2446 EXCEPTION
2447 WHEN FND_API.G_EXC_ERROR THEN
2448 x_return_status := FND_API.G_RET_STS_ERROR;
2449 x_msg_count := 1;
2450 RETCODE := 2;
2454 'jtf.plsql.JTY_TAE_CONTROL_PVT.decompose_terr_defns.g_exc_error',
2451 ERRBUF := x_msg_data;
2452 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2453 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2455 x_msg_data);
2456 END IF;
2457
2458 WHEN OTHERS THEN
2459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2460 x_msg_data := SQLCODE || ' : ' || SQLERRM;
2461 x_msg_count := 1;
2462 RETCODE := 2;
2463 ERRBUF := x_msg_data;
2464 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2465 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2466 'jtf.plsql.JTY_TAE_CONTROL_PVT.decompose_terr_defns.other',
2467 substr(x_msg_data, 1, 4000));
2468 END IF;
2469
2470 END Decompose_Terr_Defns;
2471
2472 PROCEDURE set_table_nologging( p_table_name VARCHAR2 ) AS
2473
2474 l_status VARCHAR2(30);
2475 l_industry VARCHAR2(30);
2476 l_jtf_schema VARCHAR2(30);
2477
2478 v_statement varchar2(800);
2479
2480 L_SCHEMA_NOTFOUND EXCEPTION;
2481
2482 BEGIN
2483
2484 IF(FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_jtf_schema)) THEN
2485 NULL;
2486 END IF;
2487
2488 IF (l_jtf_schema IS NULL) THEN
2489 RAISE L_SCHEMA_NOTFOUND;
2490 END IF;
2491
2492 v_statement := 'ALTER TABLE ' || l_jtf_schema || '.' || p_table_name || ' NOLOGGING ';
2493 EXECUTE IMMEDIATE v_statement;
2494
2495 EXCEPTION
2496 WHEN L_SCHEMA_NOTFOUND THEN
2497 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2498 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2499 'jtf.plsql.JTY_TAE_CONTROL_PVT.set_table_nologging.l_schema_notfound',
2500 'Schema name corresponding to JTF application not found');
2501 END IF;
2502
2503 WHEN OTHERS THEN
2504 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2505 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2506 'jtf.plsql.JTY_TAE_CONTROL_PVT.set_table_nologging.others',
2507 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
2508 END IF;
2509 END set_table_nologging;
2510
2511 END JTY_TAE_CONTROL_PVT;