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