[Home] [Help]
PACKAGE BODY: APPS.JTF_TAE_INDEX_CREATION_PVT
Source
1 PACKAGE BODY jtf_tae_index_creation_pvt AS
2 /*$Header: jtftaeib.pls 120.3.12020000.2 2012/12/04 05:41:24 swpoddar ship $*/
3 /* -- ---------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TAE_INDEX_CREATION_PVT
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- This package will contain package body for calculating selectivity,
10 -- and for creating indices on columns
11 --
12 -- PROCEDURES:
13 -- (see below for specification)
14 --
15 -- NOTES
16 -- This package is private available for use
17 --
18 -- HISTORY
19 -- 05/02/2002 SHLI Created
20 -- 06/02/2002 EIHSU Additional Indexes created when first column is CNR
21 -- with FIRST_CHAR column used.
22 -- 08/14/2002 EIHSU Add special static index creation logic
23 -- 11/05/2002 JDOCHERT FIX FOR BUG#2589890
24 -- 06/10/2003 EIHSU added worker_id
25 -- 09/07/2004 ACHANDA Fix Bug# 3872853
26 -- 12/07/2004 ACHANDA Fix Bug # 4048033 : remove default parameters while calling to fnd_stats
27 -- 05/17/2005 ACHANDA Fix Bug # 4385668 : addded a new procedure create_index_wo_worker_id for new mode
28 -- 08/17/2006 SOLIN Port 11.5.10 bugs to R12, bug 5470771
29 --
30 -- End of Comments
31 -- */
32
33 first_char_col_name varchar2(50) := 'SQUAL_FC01';
34
35 /**
36 * Procedure : Groupstddev
37 * Type : Private
38 * Pre_reqs :
39 * Description : standard deviation on group
40 * Parameters :
41 * input parameters
42 * column_name : column name
43 * output parameters
44 * standard deviation
45 */
46 FUNCTION Groupstddev( p_table_name IN varchar2, p_column_name IN varchar2 ) return number IS
47 v_statement varchar2(250);
48 g_stddev number;
49
50 errbuf varchar2(3000);
51
52 l_dop NUMBER;
53
54 BEGIN
55
56 /* get default Degree of Parallelism */
57 SELECT MIN(TO_NUMBER(v.value))
58 INTO l_dop
59 FROM v$parameter v
60 WHERE v.name = 'parallel_max_servers'
61 OR v.name = 'cpu_count';
62
63 v_statement := 'SELECT /*+ PARALLEL(JTF_TAE_TRANS_OBJS_GROUP, ' || l_dop ||') */ stddev(x) ';
64 v_statement := v_statement || ' FROM ( SELECT /*+ PARALLEL(' || p_table_name || ' ,'|| l_dop ||') */ COUNT(*) x ';
65 v_statement := v_statement || ' FROM ' ||p_table_name ;
66 v_statement := v_statement || ' GROUP BY ' || p_column_name;
67 v_statement := v_statement || ') JTF_TAE_TRANS_OBJS_GROUP ';
68
69 EXECUTE IMMEDIATE v_statement into g_stddev;
70
71 return g_stddev;
72
73 EXCEPTION
74 WHEN NO_DATA_FOUND THEN
75
76 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.GROUPSTDDEV: [END] NO_DATA_FOUND: ' ||
77 SQLERRM;
78
79 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
80 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
81 End If;
82
83 RETURN -1;
84
85 WHEN OTHERS THEN
86
87 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.GROUPSTDDEV: [END] OTHERS: ' ||
88 SQLERRM;
89
90 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
91 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
92 END IF;
93
94 RETURN -1;
95
96 END Groupstddev;
97
98
99
100
101 /**
102 * Procedure : JTF_TAE_SORT
103 * Type : Private
104 * Pre_reqs :
105 * Description : sort
106 * Parameters :
107 * input parameters
108 * v_sele : array of data to be sort
109 * v_name : array of name associated with data
110 * numcol : number of data in array.
111 * output parameters
112 * v_sele : array of sorted data
113 * v_name : array of name associated with sorted data
114 */
115 PROCEDURE Bubble_SORT ( v_sele IN OUT NOCOPY value_varray,
116 v_name IN OUT NOCOPY name_varray,
117 std_dev IN OUT NOCOPY value_varray,
118 flag IN OUT NOCOPY value_varray,
119 numcol IN NUMBER) IS
120 temp number;
121 i integer;
122 k integer;
123 ch varchar2(25);
124
125 BEGIN
126 -- bubble sort
127 for j in 1..numcol loop
128 for i in 1..numcol-1 loop
129 if v_sele(i) > v_sele(i+1) then
130 temp:= v_sele(i);
131 v_sele(i) := v_sele(i+1);
132 v_sele(i+1) := temp;
133 ch:= v_name(i);
134 v_name(i) := v_name(i+1);
135 v_name(i+1) := ch;
136 k:= std_dev(i);
137 std_dev(i) := std_dev(i+1);
138 std_dev(i+1) :=k;
139 k:= flag(i);
140 flag(i) := flag(i+1);
141 flag(i+1) :=k;
142 end if;
143 end loop;
144 end loop;
145
146 END Bubble_SORT;
147
148
149
150
151
152 /**
153 * Procedure : CAL_SELECTIVITY
154 * Type : Private
155 * Pre_reqs :
156 * Parameters :
157 * input
158 * table_name
159 * v_colname : array of column name
160 * numcol : number of name
161 * output
162 * v_colname : array of sorted column name by selectivity
163 * o_sel : array of odinal selectivity
164 * std_dev : array of standard deviation
165 */
166 FUNCTION CAL_SELECTIVITY( p_table_name IN varchar2,
167 v_colname IN OUT NOCOPY name_varray,
168 o_sel IN OUT NOCOPY value_varray,
169 std_dev IN OUT NOCOPY value_varray,
170 flag IN OUT NOCOPY value_varray,
171 numcol IN integer) return number IS
172
173 v_cardinality number;
174 v_statement varchar2(250);
175 ch varchar2(25);
176 stddev0 integer;
177 stddev1 integer;
178 i integer;
179 j integer;
180 n integer;
181
182 errbuf varchar2(3000);
183
184 l_dop NUMBER;
185
186 BEGIN
187
188 /* get default Degree of Parallelism */
189 SELECT MIN(TO_NUMBER(v.value))
190 INTO l_dop
191 FROM v$parameter v
192 WHERE v.name = 'parallel_max_servers'
193 OR v.name = 'cpu_count';
194
195 -- cardinality
196 /* JDOCHERT: 04/10/03: bug#2607186 */
197 v_statement := ' SELECT NVL(dt.NUM_ROWS,1) FROM dba_tables dt ' ||
198 ' WHERE dt.owner = UPPER(:b_schema) ' ||
199 ' AND dt.table_name = UPPER(:b_table_name) ';
200 EXECUTE IMMEDIATE v_statement
201 INTO v_cardinality
202 USING 'JTF', p_table_name;
203
204 if v_cardinality = 0 then return 0;
205 end if;
206
207 /* JDOCHERT: 04/10/03: bug#2607186 */
208 FOR i IN 1 ..numcol LOOP
209
210 v_statement :=
211 ' SELECT 100 - (NVL(dtc.num_distinct,1)*100/:b_cardinality) ' ||
212 ' FROM dba_tab_columns dtc, user_synonyms syn ' ||
213 ' WHERE syn.synonym_name = UPPER(:b_table_name) ' ||
214 ' AND dtc.owner = syn.table_owner ' ||
215 ' AND dtc.table_name = syn.table_name ' ||
216 ' AND dtc.column_name = UPPER(:b_col_name) ';
217
218 IF flag(i) = 1 THEN
219 EXECUTE IMMEDIATE v_statement
220 INTO o_sel(i)
221 USING v_cardinality, 'JTF', p_table_name, v_colname(i);
222 END IF;
223
224 END LOOP;
225
226
227 -- sort
228 Bubble_SORT(o_sel, v_colname, std_dev ,flag, numcol);
229
230 -- calculate standard deviation
231 /* JDOCHERT: 04/10/03: bug#2607186 */
232 /*for i IN 1..numcol-1 loop
233 if o_sel(i)*1.02 >= o_sel(i+1) then
234 -- for two similar selectivity
235 -- compare and reverse
236 if flag(i)=1 or std_dev(i)is null
237 then n := Groupstddev(p_table_name, v_colname(i) );
238 if n=-1 then
239
240 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.CAL_SELECTIVITY: [1]: Call to GROUPSTDDEV failed.';
241
242 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
243 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
244 end if;
245 return 0;
246 end if;
247 std_dev(i) :=n;
248 end if;
249 if flag(i+1)=1 or std_dev(i+1)is null
250 then n := Groupstddev(p_table_name, v_colname(i+1) );
251 -- -1 : error
252 if n=-1 then
253
254 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.CAL_SELECTIVITY: [2]: Call to GROUPSTDDEV failed.';
255
256 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
257 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
258 end if;
259
260 return 0;
261
262 end if;
263 std_dev(i+1) :=n;
264 end if;
265 if std_dev(i) > std_dev(i+1) then
266 ch := v_colname(i);
267 v_colname(i) := v_colname(i+1);
268 v_colname(i+1) :=ch;
269 j:= o_sel(i);
270 o_sel(i) := o_sel(i+1);
271 o_sel(i+1) :=j;
272 j:= std_dev(i);
273 std_dev(i) := std_dev(i+1);
274 std_dev(i+1) :=j;
275 j:= flag(i);
276 flag(i) := flag(i+1);
277 flag(i+1) :=j;
278 end if;
279 end if;
280 end loop;
281 */ /* JDOCHERT: 04/10/03: bug#2607186 */
282
283 RETURN 1;
284
285 EXCEPTION
286 WHEN NO_DATA_FOUND THEN
287
288 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.CAL_SELECTIVITY: [END] NO_DATA_FOUND: ' ||
289 SQLERRM;
290
291 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
292 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
293 End If;
294
295 RETURN 0;
296
297 WHEN OTHERS THEN
298
299 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.CAL_SELECTIVITY: [END] OTHERS: ' ||
300 SQLERRM;
301
302 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
303 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
304 END IF;
305
306 RETURN 0;
307
308
309 END CAL_SELECTIVITY;
310
311
312 /**
313 * Procedure : SELECTIVITY
314 * Type : Private
315 * Pre_reqs :
316 * Description :
317 * Parameters :
318 * input JTF_TAE_QUAL_FACTORS.SQUAL_ALIAS
319 * outout JTF_TAE_QUAL_FACTORS.INPUT_SELECTIVITY is populated with selectivity order
320 JTF_TAE_QUAL_FACTORS.INPUT_ORDINAL_SELECTIVITY is populated with ordinal_selectivity
321 JTF_TAE_QUAL_FACTORS.INPUT_DEVIATION is populated with standard deviation
322 * return
323 * 0: no data in JTF_TAE_QUAL_FACTORS
324 * 1: success
325 */
326
327 FUNCTION SELECTIVITY(p_TABLE_NAME IN VARCHAR2) return number IS
328
329 errbuf varchar2(3000);
330
331 col_name name_varray := name_varray(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
332 o_sel value_varray:= value_varray(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
333 std_dev value_varray:= value_varray(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
334 sele value_varray:= value_varray(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
335 flag value_varray:= value_varray(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
336 i integer;
337 j integer;
338
339 Cursor getColumnName IS
340 SELECT
341 DISTINCT A.TAE_COL_MAP sqname,
342 A.INPUT_DEVIATION dev,
343 A.INPUT_ORDINAL_SELECTIVITY ord_sele,
344 A.INPUT_SELECTIVITY sele,
345 decode(A.UPDATE_SELECTIVITY_FLAG,'Y',1,0) flag
346 FROM JTF_TAE_QUAL_FACTORS A
347 WHERE A.USE_TAE_COL_IN_INDEX_FLAG = 'Y'
348 AND A.TAE_COL_MAP is not null ;
349
350
351 BEGIN
352
353 i:=1;
354
355 -- get all distinct column name
356 j :=0;
357 for qual_info in getColumnName LOOP
358 col_name(i) := qual_info.sqname;
359 o_sel(i) := qual_info.ord_sele;
360 std_dev(i) := qual_info.dev;
361 flag(i) := qual_info.flag;
362 if flag(i) = 1 then j:=1; end if;
363 i := i + 1;
364 end loop;
365
366 -- no valid column name, or all flag = No, return 1
367 if i=1 or j=0 then return 1;
368 end if;
369
370
371 -- calculate selectivity
372 if CAL_SELECTIVITY(p_table_name, col_name, o_sel, std_dev, flag, i-1) = 0
373 then return 0;
374 end if;
375
376 -- update JTF_TAE_QUAL_FACTORS
377 for i IN 1..col_name.count loop
378 UPDATE JTF_TAE_QUAL_FACTORS
379 SET INPUT_SELECTIVITY = i,
380 INPUT_ORDINAL_SELECTIVITY = o_sel(i),
381 INPUT_DEVIATION = std_dev(i)
382 WHERE TAE_COL_MAP = col_name(i);
383 end loop;
384
385 commit;
386 RETURN 1;
387
388 EXCEPTION
389 WHEN NO_DATA_FOUND THEN
390
391 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.SELECTIVITY: [END] NO_DATA_FOUND: ' ||
392 SQLERRM;
393
394 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
395 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
396 End If;
397
398 RETURN 0;
399
400 WHEN OTHERS THEN
401
402 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.SELECTIVITY: [END] OTHERS: ' ||
403 SQLERRM;
404
405 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
406 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
407 END IF;
408
409 RETURN 0;
410
411 END SELECTIVITY;
412
413
414 /**
415 * Procedure : CREATE_INDEX_FOR_NM
416 * Type : private
417 * Pre_reqs :
418 * Description : index creation on TRANS table for new mode
419 * output : indices created on JTF_TAE_OBJECT_INPUT
420 * return 0: failure
421 * 1: success
422 */
423 procedure CREATE_INDEX_WO_WORKER_ID ( p_table_name IN VARCHAR2,
424 p_s_statement IN VARCHAR2,
425 x_Return_Status OUT NOCOPY VARCHAR2 ) IS
426
427 CURSOR index_name_c (p_table_name VARCHAR2, p_owner VARCHAR2) IS
428 select din.index_name, din.owner
429 from dba_indexes din
430 where din.table_name = p_table_name
431 and din.table_owner = p_owner
432 and exists (
433 select 1
434 from dba_ind_columns dic
435 where din.index_name = dic.index_name
436 and dic.index_owner = p_owner
437 and dic.column_name = 'WORKER_ID'
438 and dic.column_position = 1 );
439
440 CURSOR column_name_c (p_index_name VARCHAR2, p_owner VARCHAR2) IS
441 select column_name, column_position
442 from dba_ind_columns
443 where index_name = p_index_name
444 and index_owner = p_owner
445 and column_position <> 1
446 order by column_position;
447
448 TYPE l_index_name_type IS TABLE OF dba_indexes.index_name%TYPE;
449 TYPE l_owner_type IS TABLE OF dba_indexes.owner%TYPE;
450 TYPE l_column_name_type IS TABLE OF dba_ind_columns.column_name%TYPE;
451 TYPE l_column_position_type IS TABLE OF dba_ind_columns.column_position%TYPE;
452
453 l_index_name l_index_name_type;
454 l_owner l_owner_type;
455 l_column_name l_column_name_type;
456 l_column_position l_column_position_type;
457
458 l_status VARCHAR2(30);
459 l_industry VARCHAR2(30);
460 l_jtf_schema VARCHAR2(30);
461 l_statement VARCHAR2(2000);
462 alter_statement VARCHAR2(2000);
463 l_new_index_name VARCHAR2(30);
464 errbuf varchar2(3000);
465
466 l_no_of_indexes NUMBER;
467 l_no_of_columns NUMBER;
468
469 L_SCHEMA_NOTFOUND EXCEPTION;
470
471 BEGIN
472
473 /* Get the JTF schema name */
474 IF(FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_jtf_schema)) THEN
475 NULL;
476 END IF;
477
478 IF (l_jtf_schema IS NULL) THEN
479 RAISE L_SCHEMA_NOTFOUND;
480 END IF;
481
482 /* Get all the index name having worker_id as the first column */
483 OPEN index_name_c(p_table_name, l_jtf_schema);
484
485 FETCH index_name_c BULK COLLECT INTO
486 l_index_name
487 ,l_owner;
488
489 CLOSE index_name_c;
490
491 l_no_of_indexes := l_index_name.COUNT;
492
493 if (l_no_of_indexes > 0) then
494 FOR i IN l_index_name.FIRST .. l_index_name.LAST LOOP
495 if (length(l_index_name(i)) >= 30) then
496 jtf_tae_control_pvt.write_log(2, 'New Mode index corresponding to index ' || l_index_name(i) || ' is not created.');
497
498 else
499 /* Get all the column names for the index except worker_id */
500 OPEN column_name_c(l_index_name(i), l_owner(i));
501
502 FETCH column_name_c BULK COLLECT INTO
503 l_column_name, l_column_position;
504
505 CLOSE column_name_c;
506
507 l_no_of_columns := l_column_name.COUNT;
508
509 if (l_no_of_columns > 0) then
510 l_new_index_name := l_index_name(i) || 'I';
511 l_statement := 'CREATE INDEX ' || l_owner(i) ||'.' || l_new_index_name || ' ON ' || p_table_name || ' ( ';
512
513 FOR j IN l_column_name.FIRST .. l_column_name.LAST LOOP
514 if (l_column_name(j) = l_column_name(1)) then
515 l_statement := l_statement || l_column_name(j);
516 else
517 l_statement := l_statement || ',' || l_column_name(j);
518 end if;
519 END LOOP;
520
521 l_statement := l_statement || ' ) ' || p_s_statement;
522
523 execute immediate l_statement;
524
525 alter_statement := 'ALTER INDEX ' || l_owner(i) || '.' || l_new_index_name ||' NOPARALLEL';
526
527 execute immediate alter_statement;
528
529 l_column_name.TRIM(l_no_of_columns);
530 l_column_position.TRIM(l_no_of_columns);
531 end if;
532 end if;
533 END LOOP;
534 l_index_name.TRIM(l_no_of_indexes);
535 l_owner.TRIM(l_no_of_indexes);
536 end if;
537
538 x_return_status := FND_API.G_RET_STS_SUCCESS;
539
540 EXCEPTION
541 WHEN L_SCHEMA_NOTFOUND THEN
542
543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
544
545 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX_WO_WORKER_ID: [END] SCHEMA NAME FOUND CORRESPONDING TO JTF APPLICATION. ';
546
547 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
548 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
549 End If;
550
551 WHEN OTHERS THEN
552
553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
554
555 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX_WO_WORKER_ID: [END] OTHERS: ' ||
556 p_table_name || ': ' || SQLERRM;
557
558 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
559 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
560 End If;
561
562 END CREATE_INDEX_WO_WORKER_ID;
563
564
565 /**
566 * Procedure : CREATE_INDEX
567 * Type : private
568 * Pre_reqs :
569 * Description : index creation
570 * input : table JTF_TAE_QUAL_PRODUCTS
571 * : table JTF_TAE_QUAL_FACTORS
572 * output : indices created on JTF_TAE_OBJECT_INPUT
573 * return 0: failure
574 * 1: success
575 */
576 procedure CREATE_INDEX ( p_table_name IN VARCHAR2,
577 p_trans_object_type_id IN NUMBER,
578 p_source_id IN NUMBER,
579 x_Return_Status OUT NOCOPY VARCHAR2,
580 p_run_mode IN VARCHAR2 := 'TAP' ) IS
581
582 errbuf varchar2(3000);
583
584 i integer;
585 j integer;
586 pid number;
587
588 v_statement varchar2(2000);
589 s_statement varchar2(2000);
590 /* ARPATEL Bug#3597884 05/10/2004 */
591 alter_statement varchar2(2000);
592
593 l_table_tablespace varchar2(100);
594 l_idx_tablespace varchar2(100);
595 l_ora_username varchar2(100);
596 l_app_short_name varchar2(20) := 'JTF';
597
598 l_trans_idx_name varchar2(30);
599 l_matches_idx_name varchar2(30);
600 l_winners_idx_name varchar2(30);
601
602 lx_4841_idx_created varchar2(1) := 'N';
603 lx_324347_idx_created varchar2(1) := 'N';
604 prd_nulltae_mul_of_4841 varchar2(1) := 'N';
605 prd_nulltae_mul_of_324347 varchar2(1) := 'N';
606
607 lx_rev_4841_idx_created varchar2(1) := 'N';
608 lx_rev_324347_idx_created varchar2(1) := 'N';
609 prd_rev_nulltae_mul_of_4841 varchar2(1) := 'N';
610 prd_rev_nulltae_mul_of_324347 varchar2(1) := 'N';
611
612 l_create_index_flag varchar2(1):= 'Y';
613
614 /* JDOCHERT: 04/18/02: Modified to
615 ** get count of number of territories
616 ** for each qualifier combination
617 *
618 */
619 Cursor getProductList IS
620 SELECT P.qual_product_id qual_product_id,
621
622 /* JDOCHERT: 08/04/02: Added RELATION_PRODUCT to Cursor */
623 p.RELATION_PRODUCT RELATION_PRODUCT,
624
625 MAX(p.index_name) index_name,
626 MAX(p.first_char_flag) first_char_flag,
627 COUNT(r.qual_factor_id) cartesian_terr_X_factors
628 FROM jtf_terr_denorm_rules_all d
629 ,jtf_terr_qtype_usgs_all jtqu
630 ,jtf_qual_type_usgs_all jqtu
631 ,JTF_TAE_QUAL_PRODUCTS P
632 , JTF_TAE_QUAL_PROD_FACTORS R
633 WHERE /* ARPATEL 01/06/2004 bug#3337382: use qual_relation_factor in jtf_terr_qtype_usgs_all */
634 --d.qual_relation_product = p.relation_product
635 jtqu.qual_relation_product = p.relation_product
636 AND jqtu.source_id = d.source_id
637 AND jqtu.qual_type_id = p.trans_object_type_id
638 AND d.terr_id = jtqu.terr_id
639 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
640 --and d.terr_id = d.related_terr_id
641 /* ARPATEL: 12/09/2003 denorm_rules_all is no longer striped by TX id for Oracle Sales */
642 --AND d.qual_type_id = p.trans_object_type_id
643 AND d.source_id = p.source_id
644 AND P.qual_product_id = R.qual_product_id
645 AND P.BUILD_INDEX_FLAG = 'Y'
646 AND p.TRANS_OBJECT_TYPE_ID = p_trans_object_type_id
647 AND P.SOURCE_ID = p_source_id
648 GROUP BY P.qual_product_id, p.RELATION_PRODUCT
649 ORDER BY cartesian_terr_X_factors DESC, first_char_flag;
650
651
652 Cursor getFactorList IS
653 SELECT distinct TAE_COL_MAP, J2.INPUT_SELECTIVITY
654 FROM JTF_TAE_QUAL_PRODUCTS J3,
655 JTF_TAE_QUAL_FACTORS J2,
656 JTF_TAE_QUAL_PROD_FACTORS J1
657 WHERE J1.qual_product_id = pid
658 and J1.qual_product_id = J3.qual_product_id
659 and J1.qual_factor_id = J2.qual_factor_id
660 -- and J1.qual_usg_id = J2.qual_usg_id
661 and J2.USE_TAE_COL_IN_INDEX_FLAG = 'Y'
662 ORDER BY J2.INPUT_SELECTIVITY;
663
664
665 Cursor extraIndexCandidates IS
666 SELECT P.qual_product_id qual_product_id,
667
668 /* JDOCHERT: 08/04/02: Added RELATION_PRODUCT to Cursor */
669 p.RELATION_PRODUCT RELATION_PRODUCT,
670
671 p.index_name index_name,
672 p.first_char_flag first_char_flag,
673 rownum index_counter
674 FROM JTF_TAE_QUAL_PRODUCTS P
675 WHERE P.BUILD_INDEX_FLAG = 'Y'
676 AND P.FIRST_CHAR_FLAG = 'Y'
677 AND p.TRANS_OBJECT_TYPE_ID = p_trans_object_type_id
678 AND P.SOURCE_ID = p_source_id;
679
680 Cursor getReverseFactorList IS
681 select f.tae_col_map, qual_usg_id, input_selectivity
682 from jtf_tae_qual_prod_factors pf,
683 jtf_tae_qual_factors f
684 where qual_product_id = pid
685 and f.qual_factor_id = pf.qual_factor_id
686 and f.tae_col_map is not null
687 order by input_selectivity desc;
688
689 Cursor verifyProdNonNullTAEColMaps(cp_product NUMBER, cp_non_null_tae_col_maps NUMBER) IS
690 -- this cursor should only return one row since
691 -- it fetches one product and its details
692 select NON_NULL_TAE_COL_MAPS, RELATION_PRODUCT
693 from (
694 select count(*) NON_NULL_TAE_COL_MAPS,
695 p.qual_product_id QUAL_PRODUCT_ID,
696 p.relation_product RELATION_PRODUCT
697 from JTF_TAE_QUAL_products p,
698 JTF_TAE_QUAL_prod_factors pf,
699 JTF_TAE_QUAL_FACTORS f
700 where p.qual_product_id = pf.qual_product_id
701 and pf.qual_factor_id = f.qual_factor_id
702 and p.source_id = p_source_id
703 and p.trans_object_type_id = p_trans_object_type_id
704 and p.relation_product > 0
705 and tae_col_map is not null
706 and p.relation_product = cp_product
707 group by p.qual_product_id, p.relation_product
708 )
709 where NON_NULL_TAE_COL_MAPS = cp_non_null_tae_col_maps;
710
711 l_dop NUMBER;
712
713 BEGIN
714
715 /* get default Degree of Parallelism */
716 SELECT MIN(TO_NUMBER(v.value))
717 INTO l_dop
718 FROM v$parameter v
719 WHERE v.name = 'parallel_max_servers'
720 OR v.name = 'cpu_count';
721
722 SELECT i.tablespace, i.index_tablespace, u.oracle_username
723 INTO l_table_tablespace, l_idx_tablespace, l_ora_username
724 FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
725 WHERE a.application_short_name = 'JTF'
726 AND a.application_id = i.application_id
727 AND u.oracle_id = i.oracle_id;
728
729 /* JDOCHERT: 08/17/02: INTERNAL'S HARDCODED TABLESPACE FOR TAP */
730 -- l_idx_tablespace :='TAP';
731 --
732 -- default INDEX STORAGE parameters
733 s_statement := s_statement || ' TABLESPACE ' || l_idx_tablespace ;
734 s_statement := s_statement || ' STORAGE(INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED ';
735 s_statement := s_statement || ' PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT) ';
736 s_statement := s_statement || ' PCTFREE 10 INITRANS 10 MAXTRANS 255 ';
737
738 /* JDOCHERT: 04/10/03: bug#2896552 */
739 s_statement := s_statement || ' COMPUTE STATISTICS ';
740
741 s_statement := s_statement || ' NOLOGGING PARALLEL ' || l_dop;
742
743 x_return_status := FND_API.G_RET_STS_SUCCESS;
744
745
746 -- indexes for TRANS table
747 IF ( UPPER(p_table_name) LIKE 'JTF_TAE%_TRANS') THEN
748 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'Creating index for TRANS table');
749
750 /* CREATE Static UNIQUE KEY Index */
751 -- OIC processing
752 IF p_run_mode = 'OIC_TAP' THEN
753 l_trans_idx_name := 'JTF_TAE_TN' || ABS(p_trans_object_type_id) || '_UK_NDSC';
754 -- NEW_MODE_PROCESSING
755 ELSIF p_run_mode = 'NEW_MODE_TAP' THEN
756 l_trans_idx_name := 'JTF_TAE_TN' || ABS(p_trans_object_type_id) || '_UK_NDW';
757 ELSE
758 l_trans_idx_name := 'JTF_TAE_TN' || ABS(p_trans_object_type_id) || '_UK_ND';
759 END IF;
760
761 /* ARPATEL 04/26/2004 GSCC error for hardcoded schema name */
762 --v_statement := 'CREATE INDEX JTF.' || l_trans_idx_name || ' ON ' || p_table_name;
763 v_statement := 'CREATE INDEX ' || l_ora_username ||'.' || l_trans_idx_name || ' ON ' || p_table_name;
764
765 -- EIHSU: added worker_id 06/12/2003
766 v_statement := v_statement || ' ( ' || 'WORKER_ID, ' ||'TRANS_OBJECT_ID, TRANS_DETAIL_OBJECT_ID ) ';
767 v_statement := v_statement || s_statement;
768
769 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'Creating index on trans table with statement : ' || v_statement);
770 EXECUTE IMMEDIATE v_statement;
771 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'Done creating index');
772
773 /* ARPATEL Bug#3597884 05/10/2004 */
774 alter_statement := 'ALTER INDEX ' || l_ora_username || '.' || l_trans_idx_name || ' NOPARALLEL';
775 EXECUTE IMMEDIATE alter_statement;
776
777
778 /* Create Qualifier Combination Dynamic Indexes */
779 FOR prd IN getProductList LOOP
780 l_create_index_flag := 'Y';
781 -- EIHSU 08/14/02: Set flags for determining if relation_prods
782 -- are NULL TAE_COL_MAP multiples of the specific QCombinations
783 -- as described by JDOCHERT 08/04/02
784
785 /* INDEX CREATION LOGIC PREPROCESSING */
786 prd_nulltae_mul_of_4841 := 'N';
787 prd_nulltae_mul_of_324347 := 'N';
788
789 /* EIHSU:
790 VERY VERY VERY IMPORTANT:
791 Because some of these special products are actually
792 MULTIPLES of other special prodcuts, it is IMPERATIVE
793 THAT the special products with LARGER NUMBER OF FACTORS BE LISTED
794 FIRST IN THE FOLLOWING LOGIC. If this is not clear, consult me
795 before modifying.
796 */
797
798 /* ARPATEL 04/15/2004
799 ** Added 353393 below as this qual_relation_product uses Customer Name Range GROUP
800 ** Therefore this combination needs to use the same index as 324347
801 */
802 IF mod(prd.RELATION_PRODUCT, 324347) = 0 OR mod(prd.RELATION_PRODUCT, 353393) = 0 THEN
803 for verifiedProd in verifyProdNonNullTAEColMaps(prd.RELATION_PRODUCT, 3) loop
804 prd_nulltae_mul_of_324347 := 'Y';
805 end loop;
806 ELSIF mod(prd.RELATION_PRODUCT, 4841) = 0 THEN
807 for verifiedProd in verifyProdNonNullTAEColMaps(prd.RELATION_PRODUCT, 2) loop
808 prd_nulltae_mul_of_4841 := 'Y';
809 end loop;
810 END IF;
811
812 --dbms_output.put_line('processing ' || prd.relation_product);
813 --dbms_output.put_line(' prd_nulltae_mul_of_4841 =' || prd_nulltae_mul_of_4841);
814 --dbms_output.put_line(' prd_nulltae_mul_of_324347 = ' || prd_nulltae_mul_of_324347);
815 /* INDEX CREATION METHOD LOGIC */
816 IF (prd_nulltae_mul_of_4841 = 'Y') THEN
817 /* JDOCHERT: 08/04/02:
818 ** Get Static STANDARD INDEX Definition
819 ** for specific Qualifier Combinations:
820 ** 4841 = Postal Code + Country
821 */
822 --dbms_output.put_line(' rel product ' || prd.relation_product || ' special name');
823 IF (lx_4841_idx_created = 'N') THEN
824 --dbms_output.put_line(' generating 4841 index ');
825
826 JTF_TAE_SQL_LIB_PVT.get_qual_comb_index (
827 p_rel_prod => 4841,
828 p_reverse_flag => 'N',
829 p_source_id => p_source_id,
830 p_trans_object_type_id => p_trans_object_type_id,
831 p_table_name => p_table_name,
832 p_run_mode => p_run_mode, --ARPATEL 09/09/2003
833 x_statement => V_STATEMENT,
834 alter_statement => ALTER_STATEMENT );
835
836 lx_4841_idx_created := 'Y';
837 ELSE
838 l_create_index_flag := 'N';
839 END IF;
840
841 ELSIF (prd_nulltae_mul_of_324347 = 'Y') THEN
842 --dbms_output.put_line(' rel product ' || prd.relation_product || ' special name');
843
844 IF (lx_324347_idx_created = 'N') THEN
845 /* JDOCHERT: 08/04/02:
846 ** Get Static STANDARD INDEX Definition
847 ** for specific Qualifier Combinations:
848 ** 324347 = Customer Name Range + Postal Code + Country Combination
849 */
850 --dbms_output.put_line(' generating 324347 index N');
851 JTF_TAE_SQL_LIB_PVT.get_qual_comb_index (
852 p_rel_prod => 324347,
853 p_reverse_flag => 'N',
854 p_source_id => p_source_id,
855 p_trans_object_type_id => p_trans_object_type_id,
856 p_table_name => p_table_name,
857 p_run_mode => p_run_mode, --ARPATEL 09/09/2003
858 x_statement => V_STATEMENT,
859 alter_statement => ALTER_STATEMENT );
860
861 lx_324347_idx_created := 'Y';
862 ELSE
863 l_create_index_flag := 'N';
864 END IF;
865
866 ELSE
867 --dbms_output.put_line(' rel product ' || prd.relation_product || ' standard name');
868 -- OIC processing
869 IF p_run_mode = 'OIC_TAP' THEN
870 /* ARPATEL 04/26/2004 GSCC error for hardcoded schema name */
871 --v_statement := 'CREATE INDEX JTF.' || prd.index_name || 'SC' || ' ON ' || p_table_name || '( ';
872 v_statement := 'CREATE INDEX '|| l_ora_username ||'.' || prd.index_name || 'SC' || ' ON ' || p_table_name || '( ';
873 -- NEW_MODE_PROCESSING (MISSING INDEX FOR N4)
874 ELSIF p_run_mode = 'NEW_MODE_TAP' THEN
875 --v_statement := 'CREATE INDEX JTF.' || prd.index_name || 'W' || ' ON ' || p_table_name || '( ';
876 v_statement := 'CREATE INDEX ' || l_ora_username || '.' || prd.index_name || 'W' || ' ON ' || p_table_name || '( ';
877 ELSE
878 /* ARPATEL 04/26/2004 GSCC error for hardcoded schema name */
879 --v_statement := 'CREATE INDEX JTF.' || prd.index_name ||' ON ' || p_table_name || '( ';
880 v_statement := 'CREATE INDEX ' || l_ora_username || '.' || prd.index_name ||' ON ' || p_table_name || '( ';
881 END IF;
882
883 -- EIHSU: 06/12/2003: add worker_id
884 v_statement := v_statement || 'WORKER_ID, ';
885 if prd.first_char_flag = 'Y'
886 then v_statement := v_statement || first_char_col_name || ',';
887 end if;
888 pid := prd.qual_product_id;
889 j:=1;
890
891 -- for each factor of product
892 for factor IN getFactorList loop
893 if j<>1 then v_statement := v_statement || ',' ;
894 end if;
895 v_statement := v_statement || factor.TAE_COL_MAP;
896 j:=j+1;
897 end loop;
898
899 v_statement := v_statement || ') ';
900
901 END IF;
902
903 /* Append Storage Parameter Information to Index Definition */
904 v_statement := v_statement || s_statement;
905
906 IF l_create_index_flag = 'Y' THEN
907 --dbms_output.put_line(' building index');
908 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'Creating index on trans table with statement : ' || v_statement);
909 EXECUTE IMMEDIATE v_statement;
910 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'Done creating index');
911
912 /* ARPATEL Bug#3597884 05/10/2004 */
913 IF prd_nulltae_mul_of_4841 = 'Y' OR prd_nulltae_mul_of_324347 = 'Y'
914 THEN
915 EXECUTE IMMEDIATE alter_statement;
916 ELSE
917 IF p_run_mode = 'OIC_TAP' THEN
918 alter_statement := 'ALTER INDEX ' || l_ora_username || '.' || prd.index_name || 'SC' ||' NOPARALLEL';
919 ELSIF p_run_mode = 'NEW_MODE_TAP' THEN
920 alter_statement := 'ALTER INDEX ' || l_ora_username || '.' || prd.index_name || 'W' || ' NOPARALLEL';
921 ELSE
922 alter_statement := 'ALTER INDEX ' || l_ora_username || '.' || prd.index_name || ' NOPARALLEL';
923 END IF;
924 EXECUTE IMMEDIATE alter_statement;
925 END IF;
926 END IF;
927
928 END LOOP;
929
930 -- Additional Indexes for TRANS table - eihsu 03/06/2002
931 FOR idxCand in extraIndexCandidates LOOP
932 l_create_index_flag := 'Y';
933
934 /* JDOCHERT: 08/04/02:
935 ** Get Static REVERSE INDEX Definition
936 ** for specific Qualifier Combinations:
937 ** 324347 = Customer Name Range + Postal Code + Country Combination
938 */
939 /* NOTE: REVERSE INDEXES ARE ONLY NECCESSARY WHERE THERE ARE
940 ** CUSTOMER NAME RANGE LIKE '%ABC' values defined.
941 ** This is because leading '%' means that the STANDARD INDEX
942 ** on SQUAL_FC01, SQUAL_CHAR01,... will never be used.
943 */
944
945 -- EIHSU 08/14/02: Set flags for determining if relation_prods
946 -- are NULL TAE_COL_MAP multiples of the specific QCombinations
947 -- as described by JDOCHERT 08/04/02
948
949 /* INDEX CREATION LOGIC PREPROCESSING */
950 prd_rev_nulltae_mul_of_324347 := 'N';
951
952 /* EIHSU:
953 VERY VERY VERY IMPORTANT:
954 Because some of these special products are actually
955 MULTIPLES of other special prodcuts, it is IMPERATIVE
956 THAT the special products with LARGER NUMBER OF FACTORS BE LISTED
957 FIRST IN THE FOLLOWING LOGIC. If this is not clear, consult me
958 before modifying.
959 */
960
961 /* ARPATEL 04/15/2004
962 ** Added 353393 below as this qual_relation_product uses Customer Name Range GROUP
963 ** Therefore this combination needs to use the same index as 324347
964 */
965 IF mod(idxCand.RELATION_PRODUCT, 324347) = 0 OR mod(idxCand.RELATION_PRODUCT, 353393) = 0
966 THEN
967 for verifiedProd in verifyProdNonNullTAEColMaps(idxCand.RELATION_PRODUCT, 3) loop
968 prd_rev_nulltae_mul_of_324347 := 'Y';
969 end loop;
970 END IF;
971
972 --dbms_output.put_line('[R]processing ' || idxCand.relation_product);
973 --dbms_output.put_line(' [R]prd_nulltae_mul_of_4841 =' || prd_rev_nulltae_mul_of_4841);
974 --dbms_output.put_line(' [R]prd_nulltae_mul_of_324347 = ' || prd_rev_nulltae_mul_of_324347);
975
976 /* REV INDEX CREATION METHOD LOGIC */
977 IF (prd_rev_nulltae_mul_of_324347 = 'Y') THEN
978 --dbms_output.put_line(' [R]rel product ' || idxCand.relation_product || ' special name');
979
980 IF (lx_rev_324347_idx_created = 'N') THEN
981 /* JDOCHERT: 08/04/02:
982 ** Get Static STANDARD INDEX Definition
983 ** for specific Qualifier Combinations:
984 ** 324347 = Customer Name Range + Postal Code + Country Combination
985 */
986 --dbms_output.put_line(' [R]generating 324347 index Y');
987 JTF_TAE_SQL_LIB_PVT.get_qual_comb_index (
988 p_rel_prod => 324347,
989 p_reverse_flag => 'Y',
990 p_source_id => p_source_id,
991 p_trans_object_type_id => p_trans_object_type_id,
992 p_table_name => p_table_name,
993 p_run_mode => p_run_mode, --ARPATEL 09/09/2003
994 x_statement => V_STATEMENT,
995 alter_statement => ALTER_STATEMENT );
996
997 lx_rev_324347_idx_created := 'Y';
998 ELSE
999 l_create_index_flag := 'N';
1000 END IF;
1001
1002 ELSE
1003 -- OIC processing
1004 /* ARPATEL BUG#3659444 06/15/2004 */
1005 IF p_run_mode = 'OIC_TAP' THEN
1006 v_statement := 'CREATE INDEX ' || l_ora_username || '.' || idxCand.index_name || 'XS' ||
1007 ' ON ' || p_table_name || '( ';
1008 -- NEW_MODE_PROCESSING
1009 ELSIF p_run_mode = 'NEW_MODE_TAP' THEN
1010 v_statement := 'CREATE INDEX ' || l_ora_username || '.' || idxCand.index_name || 'XW' ||
1011 ' ON ' || p_table_name || '( ';
1012 ELSE
1013 v_statement := 'CREATE INDEX ' || l_ora_username || '.' || idxCand.index_name || 'X' ||
1014 ' ON ' || p_table_name || '( ';
1015 END IF;
1016
1017
1018 -- EIHSU: 06/12/2003: add worker_id
1019 v_statement := v_statement || 'WORKER_ID, ';
1020
1021 pid := idxCand.qual_product_id;
1022 j:=1;
1023
1024 -- for each factor of product
1025 for xFactor IN getReverseFactorList loop
1026 if j<>1 then v_statement := v_statement || ',' ;
1027 end if;
1028 v_statement := v_statement || xFactor.TAE_COL_MAP;
1029 j:=j+1;
1030 end loop;
1031
1032 v_statement := v_statement || ') ';
1033
1034 END IF;
1035
1036 v_statement := v_statement || s_statement;
1037
1038 IF l_create_index_flag = 'Y' THEN
1039 --dbms_output.put_line(' building rev_ index');
1040 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'Creating index on trans table with statement : ' || v_statement);
1041 EXECUTE IMMEDIATE v_statement;
1042 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'Done creating index');
1043
1044 /* ARPATEL Bug#3597884 05/10/2004 */
1045 IF prd_rev_nulltae_mul_of_324347 = 'Y'
1046 THEN
1047 EXECUTE IMMEDIATE alter_statement;
1048 ELSE
1049 IF p_run_mode = 'OIC_TAP' THEN
1050 alter_statement := 'ALTER INDEX ' || l_ora_username || '.' || idxCand.index_name || 'XS' ||' NOPARALLEL';
1051 ELSIF p_run_mode = 'NEW_MODE_TAP' THEN
1052 alter_statement := 'ALTER INDEX ' || l_ora_username || '.' || idxCand.index_name || 'XW' || ' NOPARALLEL';
1053 ELSE
1054 alter_statement := 'ALTER INDEX ' || l_ora_username || '.' || idxCand.index_name || 'X' || ' NOPARALLEL';
1055 END IF;
1056 EXECUTE IMMEDIATE alter_statement;
1057 END IF;
1058
1059
1060 END IF;
1061
1062 END LOOP;
1063
1064 /* Create the indexes without worker_id column for new mode */
1065 IF (p_table_name IN ('JTF_TAE_1001_ACCOUNT_TRANS', 'JTF_TAE_1001_LEAD_TRANS', 'JTF_TAE_1001_OPPOR_TRANS')) THEN
1066 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'Creating index on trans table with no worker_id');
1067 CREATE_INDEX_WO_WORKER_ID ( p_table_name
1068 ,s_statement
1069 ,x_Return_Status);
1070 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'Done creating index');
1071 END IF;
1072
1073 /*
1074 -- index for MATCHES table
1075 ELSIF ( UPPER(p_table_name) LIKE 'JTF_TAE%_MATCHES') THEN
1076
1077 l_matches_idx_name := REPLACE( UPPER(p_table_name),'ES',null) || '_ND';
1078
1079 v_statement := 'CREATE INDEX ' || l_ora_username || '.' || l_matches_idx_name || ' ON ' || p_table_name;
1080 -- EIHSU: 06/12/2003: add worker_id
1081 v_statement := v_statement || ' ( WORKER_ID, TRANS_OBJECT_ID, TRANS_DETAIL_OBJECT_ID ) ';
1082 v_statement := v_statement || s_statement;
1083
1084 EXECUTE IMMEDIATE v_statement;
1085 */
1086 -- index for WINNERS table
1087 ELSIF ( UPPER(p_table_name) LIKE 'JTF_TAE%_WINNERS') THEN
1088
1089 l_winners_idx_name := REPLACE( UPPER(p_table_name),'NERS',null) || '_ND';
1090
1091 v_statement := 'CREATE INDEX ' || l_ora_username ||'.' || l_winners_idx_name || ' ON ' || p_table_name;
1092
1093 /* ARPATEL: 02/19/04 BUG#3447689 */
1094 IF p_run_mode = 'OIC_TAP' THEN
1095 v_statement := v_statement || ' ( TRANS_OBJECT_ID, RESOURCE_ID, GROUP_ID ) ';
1096 ELSE
1097 v_statement := v_statement || ' ( TRANS_OBJECT_ID, RESOURCE_ID, GROUP_ID ) LOCAL';
1098 END IF;
1099
1100 v_statement := v_statement || s_statement;
1101
1102 EXECUTE IMMEDIATE v_statement;
1103
1104 /*
1105 -- index for MATCHES table
1106 ELSIF ( UPPER(p_table_name) LIKE 'JTF_TAE%_L1' OR
1107 UPPER(p_table_name) LIKE 'JTF_TAE%_L2' OR
1108 UPPER(p_table_name) LIKE 'JTF_TAE%_L3' OR
1109 UPPER(p_table_name) LIKE 'JTF_TAE%_L4' OR
1110 UPPER(p_table_name) LIKE 'JTF_TAE%_L5' OR
1111 UPPER(p_table_name) LIKE 'JTF_TAE%_WT' ) THEN
1112
1113 l_matches_idx_name := UPPER(p_table_name) || '_ND';
1114
1115 v_statement := 'CREATE INDEX ' ||l_ora_username || '.' || l_matches_idx_name || ' ON ' || p_table_name;
1116 -- EIHSU: 06/12/2003: add worker_id
1117 v_statement := v_statement || ' ( WORKER_ID, TRANS_OBJECT_ID, TRANS_DETAIL_OBJECT_ID ) ';
1118 v_statement := v_statement || s_statement;
1119
1120 EXECUTE IMMEDIATE v_statement;
1121 */
1122 END IF;
1123
1124 EXCEPTION
1125 WHEN NO_DATA_FOUND THEN
1126
1127 x_return_status := FND_API.G_RET_STS_ERROR ;
1128
1129 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX: [END] NO_DATA_FOUND: ' ||
1130 p_table_name || ': ' || SQLERRM;
1131
1132 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
1133 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1134 End If;
1135
1136 WHEN OTHERS THEN
1137
1138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1139
1140 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX: [END] OTHERS: ' ||
1141 p_table_name || ': ' || SQLERRM;
1142
1143 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
1144 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1145 End If;
1146
1147 END CREATE_INDEX;
1148
1149 PROCEDURE DROP_TABLE_INDEXES( p_table_name IN VARCHAR2
1150 , x_return_status OUT NOCOPY VARCHAR2 ) IS
1151
1152 retcode VARCHAR2(100);
1153 errbuf varchar2(3000);
1154 v_statement varchar2(800);
1155
1156 l_status VARCHAR2(30);
1157 l_industry VARCHAR2(30);
1158 l_jtf_schema VARCHAR2(30);
1159
1160 /* JDOCHERT: 5/28/02: Modified to use:
1161 ** 1. ALL_INDEXES (instead of SYS.DBA_INDEXES)
1162 ** 2. aidx.table_owner (instead of aidx.owner)
1163 ** 3. return aidx.owner in SELECT */
1164 Cursor getIndexList(p_jtf_schema varchar2) IS
1165 SELECT aidx.owner, aidx.INDEX_NAME
1166 FROM DBA_INDEXES aidx
1167 WHERE aidx.table_name = p_table_name
1168 AND aidx.table_owner = p_jtf_schema
1169 AND aidx.index_name not in ('JTF_TAE_TN1002_CASE_N1W', 'JTF_TAE_TN1003_CASE_N1W', 'JTF_TAE_TN1004_CASE_N1W');
1170
1171 L_SCHEMA_NOTFOUND EXCEPTION;
1172 BEGIN
1173
1174 IF(FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_jtf_schema)) THEN
1175 NULL;
1176 END IF;
1177
1178 IF (l_jtf_schema IS NULL) THEN
1179 RAISE L_SCHEMA_NOTFOUND;
1180 END IF;
1181
1182 -- for each index
1183 FOR idx IN getIndexList(l_jtf_schema) LOOP
1184
1185 v_statement := 'DROP INDEX ' || idx.owner || '.' || idx.index_name;
1186
1187 EXECUTE IMMEDIATE v_statement;
1188
1189 END LOOP;
1190
1191 x_return_status := FND_API.G_RET_STS_SUCCESS;
1192
1193 EXCEPTION
1194 WHEN L_SCHEMA_NOTFOUND THEN
1195
1196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1197 RETCODE := 2;
1198 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES: [END] SCHEMA NAME FOUND CORRESPONDING TO JTF APPLICATION. ';
1199
1200 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
1201 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1202 End If;
1203
1204 WHEN OTHERS THEN
1205
1206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1207 RETCODE := 2;
1208 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES: [END] OTHERS: ' ||
1209 p_table_name || ': ' || SQLERRM;
1210
1211 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
1212 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1213 End If;
1214
1215
1216 END DROP_TABLE_INDEXES;
1217
1218
1219 PROCEDURE TRUNCATE_TABLE( p_TABLE_NAME IN VARCHAR2,
1220 x_return_status OUT NOCOPY VARCHAR2 ) IS
1221
1222 retcode VARCHAR2(100);
1223 errbuf varchar2(3000);
1224 v_statement varchar2(2000);
1225 l_ora_username varchar2(100);
1226
1227 BEGIN
1228 SELECT u.oracle_username
1229 INTO l_ora_username
1230 FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
1231 WHERE a.application_short_name = 'JTF'
1232 AND a.application_id = i.application_id
1233 AND u.oracle_id = i.oracle_id;
1234
1235 v_statement := 'TRUNCATE TABLE ' || l_ora_username || '.' || p_TABLE_NAME || ' DROP STORAGE';
1236 EXECUTE IMMEDIATE v_statement;
1237 commit;
1238
1239 --dbms_output.put_line('TAE_INDEX_CREATION .truncate_tablee: v_statement = ' || v_statement);
1240 x_return_status := FND_API.G_RET_STS_SUCCESS;
1241
1242 EXCEPTION
1243 WHEN OTHERS THEN
1244
1245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1246 RETCODE := 2;
1247 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE: [END] OTHERS: ' ||
1248 p_table_name || ': ' || SQLERRM;
1249
1250 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
1251 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1252 End If;
1253
1254 END TRUNCATE_TABLE;
1255
1256
1257 /* JDOCHERT: 11/05/02: FIX FOR BUG#2589890 */
1258 PROCEDURE ANALYZE_TABLE_INDEX( p_TABLE_NAME IN VARCHAR2,
1259 P_PERCENT IN NUMBER,
1260 x_return_status OUT NOCOPY VARCHAR2 ) IS
1261
1262 retcode VARCHAR2(100);
1263 errbuf varchar2(3000);
1264
1265 l_dop NUMBER;
1266
1267 BEGIN
1268
1269 /* get default Degree of Parallelism */
1270 SELECT MIN(TO_NUMBER(v.value))
1271 INTO l_dop
1272 FROM v$parameter v
1273 WHERE v.name = 'parallel_max_servers'
1274 OR v.name = 'cpu_count';
1275
1276 --dbms_output.put_line('Degree of Parallelism = '||TO_CHAR(l_dop));
1277
1278 FND_STATS.GATHER_TABLE_STATS(
1279 ownname => 'JTF',
1280 tabname => P_TABLE_NAME,
1281 percent => P_PERCENT,
1282 degree => null,
1283 partname => null,
1284 backup_flag => null,
1285 cascade => null,
1286 granularity =>'DEFAULT',
1287 hmode => 'FULL'
1288
1289 );
1290
1291 x_return_status := FND_API.G_RET_STS_SUCCESS;
1292
1293 EXCEPTION
1294 WHEN OTHERS THEN
1295
1296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1297 RETCODE := 2;
1298 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX: [END] OTHERS: ' ||
1299 p_table_name || ': ' || SQLERRM;
1300
1301 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
1302 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1303 End If;
1304
1305 END ANALYZE_TABLE_INDEX;
1306
1307
1308 END JTF_TAE_INDEX_CREATION_PVT;