[Home] [Help]
PACKAGE BODY: APPS.JTY_TAE_INDEX_CREATION_PVT
Source
1 PACKAGE BODY jty_tae_index_creation_pvt AS
2 /*$Header: jtfyaeib.pls 120.9.12010000.2 2008/11/25 13:14:27 gmarwah ship $*/
3 /* -- ---------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: jty_tae_index_creation_pvt
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- This package has public api to do the following :
10 -- a) return a list of column in order of selectivity
11 -- b) create index on interface tables
12 -- c) drop indexes on a table
13 -- d) analyze a table
14 -- e) truncate a table
15 --
16 -- PROCEDURES:
17 -- (see below for specification)
18 --
19 -- NOTES
20 -- This package is private available for use
21 --
22 -- HISTORY
23 -- 06/13/2005 ACHANDA Created
24 --
25 -- End of Comments
26 -- */
27
28 first_char_col_name varchar2(50) := 'SQUAL_FC01';
29
30 /**
31 * Procedure : Bubble_SORT
32 * Type : Private
33 * Pre_reqs :
34 * Description : sort
35 * Parameters :
36 * input parameters
37 * v_sele : array of data to be sort
38 * v_name : array of name associated with data
39 * numcol : number of data in array.
40 * output parameters
41 * v_sele : array of sorted data
42 * v_name : array of name associated with sorted data
43 */
44 PROCEDURE Bubble_SORT ( v_sele IN OUT NOCOPY value_varray,
45 v_name IN OUT NOCOPY name_varray,
46 std_dev IN OUT NOCOPY value_varray,
47 flag IN OUT NOCOPY value_varray,
48 numcol IN NUMBER,
49 x_return_status OUT NOCOPY varchar2)
50 IS
51
52 temp NUMBER;
53 i INTEGER;
54 k INTEGER;
55 ch VARCHAR2(30);
56
57 BEGIN
58 -- debug message
59 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
60 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
61 'jtf.plsql.jty_tae_index_creation_pvt.bubble_sort.start',
62 'Start of the procedure jty_tae_index_creation_pvt.bubble_sort');
63 END IF;
64
65 x_return_status := FND_API.G_RET_STS_SUCCESS;
66
67 -- bubble sort
68 FOR j in 1..numcol LOOP
69 FOR i in 1..numcol-1 LOOP
70 IF (v_sele(i) > v_sele(i+1)) THEN
71 temp:= v_sele(i);
72 v_sele(i) := v_sele(i+1);
73 v_sele(i+1) := temp;
74 ch:= v_name(i);
75 v_name(i) := v_name(i+1);
76 v_name(i+1) := ch;
77 k:= std_dev(i);
78 std_dev(i) := std_dev(i+1);
79 std_dev(i+1) :=k;
80 k:= flag(i);
81 flag(i) := flag(i+1);
82 flag(i+1) :=k;
83 END IF;
84 END LOOP;
85 END LOOP;
86
87 -- debug message
88 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
89 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
90 'jtf.plsql.jty_tae_index_creation_pvt.bubble_sort.end',
91 'End of the procedure jty_tae_index_creation_pvt.bubble_sort');
92 END IF;
93
94 EXCEPTION
95 WHEN OTHERS THEN
96 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
97 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
98 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
99 'jtf.plsql.jty_tae_index_creation_pvt.bubble_sort.others',
100 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
101 END IF;
102
103 END Bubble_SORT;
104
105
106 /**
107 * Procedure : CAL_SELECTIVITY
108 * Type : Private
109 * Pre_reqs :
110 * Parameters :
111 * input
112 * p_table_name
113 * v_colname : array of column name
114 * numcol : number of name
115 * output
116 * v_colname : array of sorted column name by selectivity
117 * o_sel : array of odinal selectivity
118 * std_dev : array of standard deviation
119 */
120 PROCEDURE CAL_SELECTIVITY( p_table_name IN varchar2,
121 v_colname IN OUT NOCOPY name_varray,
122 o_sel IN OUT NOCOPY value_varray,
123 std_dev IN OUT NOCOPY value_varray,
124 flag IN OUT NOCOPY value_varray,
125 numcol IN integer,
126 x_return_status OUT NOCOPY varchar2)
127 IS
128
129 l_status VARCHAR2(30);
130 l_industry VARCHAR2(30);
131 l_jtf_schema VARCHAR2(30);
132
133 L_SCHEMA_NOTFOUND EXCEPTION;
134
135 v_cardinality number;
136 i integer;
137
138 BEGIN
139 -- debug message
140 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
141 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
142 'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.start',
143 'Start of the procedure jty_tae_index_creation_pvt.cal_selectivity');
144 END IF;
145
146 x_return_status := FND_API.G_RET_STS_SUCCESS;
147
148 IF(FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_jtf_schema)) THEN
149 NULL;
150 END IF;
151
152 IF (l_jtf_schema IS NULL) THEN
153 RAISE L_SCHEMA_NOTFOUND;
154 END IF;
155
156 /* Get the cardinality */
157 SELECT NVL(dt.NUM_ROWS,1)
158 INTO v_cardinality
159 FROM dba_tables dt
160 WHERE dt.owner = l_jtf_schema
161 AND dt.table_name = p_table_name;
162
163 -- debug message
164 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
165 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
166 'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.cardinality',
167 'Number of rows for table ' || p_table_name || ' : ' || v_cardinality);
168 END IF;
169
170 IF v_cardinality = 0 THEN
171 -- debug message
172 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
173 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
174 'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.cardinality',
175 'API jty_tae_index_creation_pvt.cal_selectivity has failed as the number of rows in the table ' ||
176 p_table_name || ' is 0');
177 END IF;
178
179 RAISE FND_API.G_EXC_ERROR;
180 END IF;
181
182 FOR i IN 1 ..numcol LOOP
183
184 IF flag(i) = 1 THEN
185 BEGIN
186 SELECT 100 - (NVL(dtc.num_distinct,1)*100/v_cardinality)
187 INTO o_sel(i)
188 FROM dba_tab_columns dtc
189 WHERE dtc.owner = l_jtf_schema
190 AND dtc.table_name = UPPER(p_table_name)
191 AND dtc.column_name = UPPER(v_colname(i));
192 EXCEPTION
193 WHEN NO_DATA_FOUND THEN
194 NUll;
195 END;
196 END IF;
197
198 END LOOP;
199
200 -- sort
201 Bubble_SORT(o_sel, v_colname, std_dev ,flag, numcol, x_return_status);
202
203 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
204 -- debug message
205 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
206 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
207 'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.bubble_sort',
208 'API jty_tae_index_creation_pvt.bubble_sort has failed');
209 END IF;
210
211 RAISE FND_API.G_EXC_ERROR;
212 END IF;
213
214 -- debug message
215 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
216 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
217 'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.end',
218 'End of the procedure jty_tae_index_creation_pvt.cal_selectivity');
219 END IF;
220
221 EXCEPTION
222 WHEN L_SCHEMA_NOTFOUND THEN
223 x_return_status := FND_API.G_RET_STS_ERROR ;
224 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
225 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
226 'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.l_schema_notfound',
227 'Schema name corresponding to JTF application not found');
228 END IF;
229
230 WHEN NO_DATA_FOUND THEN
231 x_return_status := FND_API.G_RET_STS_ERROR ;
232 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
233 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
234 'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.no_data_found',
235 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
236 END IF;
237
238 WHEN FND_API.G_EXC_ERROR THEN
239 x_return_status := FND_API.G_RET_STS_ERROR ;
240 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
241 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
242 'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.g_exc_error',
243 'jty_tae_index_creation_pvt.cal_selectivity has failed with G_EXC_ERROR exception');
244 END IF;
245
246 WHEN OTHERS THEN
247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
248 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
249 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
250 'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.others',
251 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
252 END IF;
253
254 END CAL_SELECTIVITY;
255
256 /**
257 * Procedure : DEA_SELECTIVITY
258 * Type : Private
259 * Pre_reqs :
260 * Description :
261 * Parameters :
262 * input JTY_DEA_ATTR_FACTORS.SQUAL_ALIAS
263 * outout JTY_DEA_ATTR_FACTORS.INPUT_SELECTIVITY is populated with selectivity order
264 JTY_DEA_ATTR_FACTORS.INPUT_ORDINAL_SELECTIVITY is populated with ordinal_selectivity
265 JTY_DEA_ATTR_FACTORS.INPUT_DEVIATION is populated with standard deviation
266 */
267
268 PROCEDURE DEA_SELECTIVITY(p_TABLE_NAME IN VARCHAR2,
269 x_return_status OUT NOCOPY VARCHAR2)
270 IS
271
272 -- SOLIN, Bug 5893926
273 -- extend to 300 elements
274 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,
275 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,0,0,0,0,0,0,0,0,0,0,
276 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,0,0,0,0,0,0,0,0,0,0,
277 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,0,0,0,0,0,0,0,0,0,0,
278 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,0,0,0,0,0,0,0,0,0,0,
279 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,0,0,0,0,0,0,0,0,0,0,
280 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,0,0,0,0,0,0,0,0,0,0,
281 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,0,0,0,0,0,0,0,0,0,0);
282 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,
283 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,0,0,0,0,0,0,0,0,0,0,
284 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,0,0,0,0,0,0,0,0,0,0,
285 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,0,0,0,0,0,0,0,0,0,0,
286 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,0,0,0,0,0,0,0,0,0,0,
287 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,0,0,0,0,0,0,0,0,0,0,
288 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,0,0,0,0,0,0,0,0,0,0,
289 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,0,0,0,0,0,0,0,0,0,0);
290 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,
291 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,0,0,0,0,0,0,0,0,0,0,
292 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,0,0,0,0,0,0,0,0,0,0,
293 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,0,0,0,0,0,0,0,0,0,0,
294 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,0,0,0,0,0,0,0,0,0,0,
295 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,0,0,0,0,0,0,0,0,0,0,
296 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,0,0,0,0,0,0,0,0,0,0,
297 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,0,0,0,0,0,0,0,0,0,0);
298 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,
299 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,0,0,0,0,0,0,0,0,0,0,
300 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,0,0,0,0,0,0,0,0,0,0,
301 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,0,0,0,0,0,0,0,0,0,0,
302 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,0,0,0,0,0,0,0,0,0,0,
303 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,0,0,0,0,0,0,0,0,0,0,
304 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,0,0,0,0,0,0,0,0,0,0,
308 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,0,0,0,0,0,0,0,0,0,0,
305 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,0,0,0,0,0,0,0,0,0,0);
306 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,
307 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,0,0,0,0,0,0,0,0,0,0,
309 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,0,0,0,0,0,0,0,0,0,0,
310 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,0,0,0,0,0,0,0,0,0,0,
311 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,0,0,0,0,0,0,0,0,0,0,
312 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,0,0,0,0,0,0,0,0,0,0,
313 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,0,0,0,0,0,0,0,0,0,0);
314 i integer;
315 j integer;
316
317 CURSOR getColumnName IS
318 SELECT DISTINCT
319 A.TAE_COL_MAP sqname,
320 A.INPUT_DEVIATION dev,
321 A.INPUT_ORDINAL_SELECTIVITY ord_sele,
322 A.INPUT_SELECTIVITY sele,
323 decode(A.UPDATE_SELECTIVITY_FLAG,'Y',1,0) flag
324 FROM JTY_DEA_ATTR_FACTORS A
325 WHERE A.USE_TAE_COL_IN_INDEX_FLAG = 'Y'
326 AND A.TAE_COL_MAP is not null ;
327
328
329 BEGIN
330
331 -- debug message
332 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
333 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
334 'jtf.plsql.jty_tae_index_creation_pvt.dea_selectivity.start',
335 'Start of the procedure jty_tae_index_creation_pvt.dea_selectivity');
336 END IF;
337
338 x_return_status := FND_API.G_RET_STS_SUCCESS;
339
340 i := 1;
341 j := 0;
342
343 FOR qual_info in getColumnName LOOP
344 col_name(i) := qual_info.sqname;
345 o_sel(i) := qual_info.ord_sele;
346 std_dev(i) := qual_info.dev;
347 flag(i) := qual_info.flag;
348 IF flag(i) = 1 THEN j:=1; END IF;
349 i := i + 1;
350 END LOOP;
351
352 -- no valid column name, or all flag = No, return 1
353 IF (i=1 or j=0) THEN
354 -- debug message
355 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
356 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
357 'jtf.plsql.jty_tae_index_creation_pvt.dea_selectivity.col_name',
358 'API jty_tae_index_creation_pvt.dea_selectivity has failed as there is no valid column name, or all flag = No');
359 END IF;
360
361 RAISE FND_API.G_EXC_ERROR;
362 END IF;
363
364 CAL_SELECTIVITY(
365 p_table_name => p_table_name,
366 v_colname => col_name,
367 o_sel => o_sel,
368 std_dev => std_dev,
369 flag => flag,
370 numcol => i-1,
371 x_return_status => x_return_status);
372
373 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
374 -- debug message
375 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
376 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
377 'jtf.plsql.jty_tae_index_creation_pvt.dea_selectivity.cal_selectivity',
378 'API jty_tae_index_creation_pvt.cal_selectivity has failed');
379 END IF;
380
381 RAISE FND_API.G_EXC_ERROR;
382 END IF;
383
384 -- update JTY_DEA_ATTR_FACTORS
385 FOR i IN 1..col_name.count LOOP
386 UPDATE JTY_DEA_ATTR_FACTORS
387 SET INPUT_SELECTIVITY = i,
388 INPUT_ORDINAL_SELECTIVITY = o_sel(i),
389 INPUT_DEVIATION = std_dev(i)
390 WHERE TAE_COL_MAP = col_name(i);
391 END LOOP;
392
393 COMMIT;
394
395 -- debug message
396 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
397 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
398 'jtf.plsql.jty_tae_index_creation_pvt.dea_selectivity.end',
399 'End of the procedure jty_tae_index_creation_pvt.dea_selectivity');
400 END IF;
401
402 EXCEPTION
403 WHEN FND_API.G_EXC_ERROR THEN
404 x_return_status := FND_API.G_RET_STS_ERROR ;
405 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
406 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
407 'jtf.plsql.jty_tae_index_creation_pvt.dea_selectivity.g_exc_error',
408 'jty_tae_index_creation_pvt.dea_selectivity has failed with G_EXC_ERROR exception');
409 END IF;
410
411 WHEN OTHERS THEN
412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
413 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
414 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
418
415 'jtf.plsql.jty_tae_index_creation_pvt.dea_selectivity.others',
416 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
417 END IF;
419 END DEA_SELECTIVITY;
420
421
422 /**
423 * Procedure : SELECTIVITY
424 * Type : Private
425 * Pre_reqs :
426 * Description :
427 * Parameters :
428 * input JTF_TAE_QUAL_FACTORS.SQUAL_ALIAS
429 * outout JTF_TAE_QUAL_FACTORS.INPUT_SELECTIVITY is populated with selectivity order
430 JTF_TAE_QUAL_FACTORS.INPUT_ORDINAL_SELECTIVITY is populated with ordinal_selectivity
431 JTF_TAE_QUAL_FACTORS.INPUT_DEVIATION is populated with standard deviation
432 */
433
434 PROCEDURE SELECTIVITY(p_TABLE_NAME IN VARCHAR2,
435 p_mode IN VARCHAR2,
436 p_source_id IN NUMBER,
437 x_return_status OUT NOCOPY VARCHAR2)
438 IS
439
440 col_name name_varray := name_varray(
441 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,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,
442 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,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,
443 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,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,
444 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,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,
445 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,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,
446 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,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,
447 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,0,0,0,0,0,0,0,0,0,0);
448 o_sel value_varray := value_varray(
449 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,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,
450 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,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,
451 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,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,
452 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,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,
453 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,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,
454 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,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,
455 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,0,0,0,0,0,0,0,0,0,0);
456 std_dev value_varray := value_varray(
457 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,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,
458 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,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,
459 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,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,
460 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,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,
461 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,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,
462 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,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,
463 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,0,0,0,0,0,0,0,0,0,0);
464 sele value_varray := value_varray(
465 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,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,
466 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,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,
467 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,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,
468 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,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,
469 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,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,
470 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,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,
471 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,0,0,0,0,0,0,0,0,0,0);
472 flag value_varray := value_varray(
473 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,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,
474 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,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,
475 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,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,
476 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,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,
477 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,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,
478 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,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,
479 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,0,0,0,0,0,0,0,0,0,0);
480
481 i integer;
482 j integer;
483
484 CURSOR getColumnName IS
485 SELECT DISTINCT
486 A.TAE_COL_MAP sqname,
487 A.INPUT_DEVIATION dev,
488 A.INPUT_ORDINAL_SELECTIVITY ord_sele,
489 A.INPUT_SELECTIVITY sele,
493 AND A.TAE_COL_MAP is not null ;
490 decode(A.UPDATE_SELECTIVITY_FLAG,'Y',1,0) flag
491 FROM JTF_TAE_QUAL_FACTORS A
492 WHERE A.USE_TAE_COL_IN_INDEX_FLAG = 'Y'
494
495 CURSOR getColumnName_dnmval(cl_source_id in number) IS
496 SELECT DISTINCT
497 A.VALUES_COL_MAP sqname,
498 NULL dev,
499 A.INPUT_ORDINAL_SELECTIVITY ord_sele,
500 A.INPUT_SELECTIVITY sele,
501 1 flag
502 FROM jty_terr_values_idx_details A,
503 jty_terr_values_idx_header B
504 WHERE A.VALUES_COL_MAP is not null
505 AND B.delete_flag = 'N'
506 AND A.terr_values_idx_header_id = B.terr_values_idx_header_id
507 AND B.source_id = cl_source_id;
508
509 CURSOR getColumnName_deaval(cl_source_id in number) IS
510 SELECT DISTINCT
511 A.VALUES_COL_MAP sqname,
512 NULL dev,
513 A.INPUT_ORDINAL_SELECTIVITY ord_sele,
514 A.INPUT_SELECTIVITY sele,
515 1 flag
516 FROM jty_dea_values_idx_details A,
517 jty_dea_values_idx_header B
518 WHERE A.VALUES_COL_MAP is not null
519 AND A.dea_values_idx_header_id = B.dea_values_idx_header_id
520 AND B.source_id = cl_source_id;
521
522 BEGIN
523 -- debug message
524 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
525 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
526 'jtf.plsql.jty_tae_index_creation_pvt.selectivity.start',
527 'Start of the procedure jty_tae_index_creation_pvt.selectivity');
528 END IF;
529
530 x_return_status := FND_API.G_RET_STS_SUCCESS;
531
532 i := 1;
533 j := 0;
534
535 IF (p_source_id IS NULL) THEN
536 FOR qual_info in getColumnName LOOP
537 col_name(i) := qual_info.sqname;
538 o_sel(i) := qual_info.ord_sele;
539 std_dev(i) := qual_info.dev;
540 flag(i) := qual_info.flag;
541 IF flag(i) = 1 THEN j:=1; END IF;
542 i := i + 1;
543 END LOOP;
544 ELSE
545 IF (p_mode = 'DATE EFFECTIVE') THEN
546 FOR qual_info in getColumnName_deaval(p_source_id) LOOP
547 col_name(i) := qual_info.sqname;
548 o_sel(i) := qual_info.ord_sele;
549 std_dev(i) := qual_info.dev;
550 flag(i) := qual_info.flag;
551 IF flag(i) = 1 THEN j:=1; END IF;
552 i := i + 1;
553 END LOOP;
554 ELSE
555 FOR qual_info in getColumnName_dnmval(p_source_id) LOOP
556 col_name(i) := qual_info.sqname;
557 o_sel(i) := qual_info.ord_sele;
558 std_dev(i) := qual_info.dev;
559 flag(i) := qual_info.flag;
560 IF flag(i) = 1 THEN j:=1; END IF;
561 i := i + 1;
562 END LOOP;
563 END IF; /* end IF (p_mode = 'DATE EFFECTIVE') */
564 END IF; /* end IF (p_source_id IS NULL) */
565
566 -- no valid column name, or all flag = No, return 1
567 IF (i=1 or j=0) THEN
568 -- debug message
569 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
570 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
571 'jtf.plsql.jty_tae_index_creation_pvt.selectivity.col_name',
572 'API jty_tae_index_creation_pvt.selectivity has failed as there is no valid column name, or all flag = No');
573 END IF;
574
575 RAISE FND_API.G_EXC_ERROR;
576 END IF;
577
578 CAL_SELECTIVITY(
579 p_table_name => p_table_name,
580 v_colname => col_name,
581 o_sel => o_sel,
582 std_dev => std_dev,
583 flag => flag,
584 numcol => i-1,
585 x_return_status => x_return_status);
586
587 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
588 -- debug message
589 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
590 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
591 'jtf.plsql.jty_tae_index_creation_pvt.selectivity.cal_selectivity',
592 'API jty_tae_index_creation_pvt.selectivity has failed');
593 END IF;
594
595 RAISE FND_API.G_EXC_ERROR;
596 END IF;
597
598 IF (p_source_id IS NULL) THEN
599 -- update JTF_TAE_QUAL_FACTORS
600 FOR i IN 1..col_name.count LOOP
601 UPDATE JTF_TAE_QUAL_FACTORS
602 SET INPUT_SELECTIVITY = i,
603 INPUT_ORDINAL_SELECTIVITY = o_sel(i),
604 INPUT_DEVIATION = std_dev(i)
605 WHERE TAE_COL_MAP = col_name(i);
606 END LOOP;
607 ELSE
608 IF (p_mode = 'DATE EFFECTIVE') THEN
609 FOR i IN 1..col_name.count LOOP
610 UPDATE jty_dea_values_idx_details
611 SET INPUT_SELECTIVITY = i,
612 INPUT_ORDINAL_SELECTIVITY = o_sel(i)
613 WHERE VALUES_COL_MAP = col_name(i);
614 END LOOP;
615 ELSE
616 FOR i IN 1..col_name.count LOOP
617 UPDATE jty_terr_values_idx_details
618 SET INPUT_SELECTIVITY = i,
619 INPUT_ORDINAL_SELECTIVITY = o_sel(i)
620 WHERE VALUES_COL_MAP = col_name(i);
621 END LOOP;
622 END IF; /* end IF (p_mode = 'DATE EFFECTIVE') */
623 END IF; /* end IF (p_source_id IS NULL) */
624
628 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
625 COMMIT;
626
627 -- debug message
629 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
630 'jtf.plsql.jty_tae_index_creation_pvt.selectivity.end',
631 'End of the procedure jty_tae_index_creation_pvt.selectivity');
632 END IF;
633
634 EXCEPTION
635 WHEN FND_API.G_EXC_ERROR THEN
636 x_return_status := FND_API.G_RET_STS_ERROR ;
637 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
638 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
639 'jtf.plsql.jty_tae_index_creation_pvt.selectivity.g_exc_error',
640 'jty_tae_index_creation_pvt.selectivity has failed with G_EXC_ERROR exception');
641 END IF;
642
643 WHEN OTHERS THEN
644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
645 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
646 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
647 'jtf.plsql.jty_tae_index_creation_pvt.selectivity.others',
648 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
649 END IF;
650
651 END SELECTIVITY;
652
653
654 PROCEDURE get_qual_comb_index (
655 p_rel_prod IN NUMBER,
656 p_reverse_flag IN VARCHAR2,
657 p_qual_type_usg_id IN NUMBER,
658 p_table_name IN VARCHAR2,
659 p_index_extn IN VARCHAR2,
660 p_run_mode IN VARCHAR2,
661 p_mode IN VARCHAR2,
662 x_return_status OUT NOCOPY VARCHAR2,
663 x_statement OUT NOCOPY VARCHAR2,
664 alter_statement OUT NOCOPY VARCHAR2)
665 AS
666
667 l_trans_idx_name VARCHAR2(30);
668 l_status VARCHAR2(30);
669 l_industry VARCHAR2(30);
670 l_jtf_schema VARCHAR2(30);
671
672 L_SCHEMA_NOTFOUND EXCEPTION;
673 BEGIN
674
675 -- debug message
676 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
677 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
678 'jtf.plsql.jty_tae_index_creation_pvt.get_qual_comb_index.begin',
679 'Start of the procedure jty_tae_index_creation_pvt.get_qual_comb_index');
680 END IF;
681
682 x_return_status := FND_API.G_RET_STS_SUCCESS;
683
684 IF(FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_jtf_schema)) THEN
685 NULL;
686 END IF;
687
688 IF (l_jtf_schema IS NULL) THEN
689 RAISE L_SCHEMA_NOTFOUND;
690 END IF;
691
692 IF (p_run_mode = 'DEA_TRANS') THEN
693 l_trans_idx_name := 'JTF_TAE_DE' || TO_CHAR(ABS(p_qual_type_usg_id)) || '_' || TO_CHAR(p_rel_prod) || '_' || p_index_extn;
694 ELSE
695 IF (p_mode = 'TOTAL') THEN
696 l_trans_idx_name := 'JTF_TAE_TN' || TO_CHAR(ABS(p_qual_type_usg_id)) || '_' || TO_CHAR(p_rel_prod) || '_' || p_index_extn || 'T';
697 ELSE
698 l_trans_idx_name := 'JTF_TAE_TN' || TO_CHAR(ABS(p_qual_type_usg_id)) || '_' || TO_CHAR(p_rel_prod) || '_' || p_index_extn || 'I';
699 END IF;
700 END IF;
701
702 IF (p_reverse_flag = 'Y') THEN
703 l_trans_idx_name := l_trans_idx_name || 'X';
704 END IF;
705
706 /* Postal Code + Country Combination */
707 IF ( p_rel_prod = 4841 AND p_reverse_flag = 'N' ) THEN
708
709 x_statement := 'CREATE INDEX ' || l_jtf_schema || '.' || l_trans_idx_name || ' ON ' || p_table_name;
710 x_statement := x_statement || ' ( SQUAL_CHAR07, SQUAL_CHAR06 ) ';
711 IF (p_mode <> 'INCREMENTAL') THEN
712 x_statement := x_statement || ' LOCAL ';
713 END IF;
714
715 alter_statement := 'ALTER INDEX ' || l_jtf_schema || '.' || l_trans_idx_name || ' NOPARALLEL';
716
717 /* Customer Name Range + Postal Code + Country Combination */
718 ELSIF ( p_rel_prod = 324347 AND p_reverse_flag = 'N' ) THEN
719
720 x_statement := 'CREATE INDEX '|| l_jtf_schema ||'.' || l_trans_idx_name || ' ON ' || p_table_name;
721 x_statement := x_statement || ' ( SQUAL_FC01, SQUAL_CHAR01, SQUAL_CHAR06, SQUAL_CHAR07 ) ';
722 IF (p_mode <> 'INCREMENTAL') THEN
723 x_statement := x_statement || ' LOCAL ';
724 END IF;
725
726 alter_statement := 'ALTER INDEX ' || l_jtf_schema || '.' || l_trans_idx_name || ' NOPARALLEL';
727
728 /* REVERSE: Customer Name Range + Postal Code + Country Combination */
729 ELSIF ( p_rel_prod = 324347 AND p_reverse_flag = 'Y' ) THEN
730
731 x_statement := 'CREATE INDEX ' || l_jtf_schema || '.' || l_trans_idx_name || ' ON ' || p_table_name;
732 x_statement := x_statement || ' ( SQUAL_CHAR07, SQUAL_CHAR06, SQUAL_CHAR01 ) ';
733 IF (p_mode <> 'INCREMENTAL') THEN
734 x_statement := x_statement || ' LOCAL ';
735 END IF;
736
737 alter_statement := 'ALTER INDEX ' || l_jtf_schema || '.' || l_trans_idx_name || ' NOPARALLEL';
738
739 END IF;
740
741 -- debug message
742 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
743 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
744 'jtf.plsql.jty_tae_index_creation_pvt.get_qual_comb_index.index_stmt',
745 'x_statement : ' || x_statement || ' alter_statement : ' || alter_statement);
746 END IF;
747
748 -- debug message
749 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
750 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
754
751 'jtf.plsql.jty_tae_index_creation_pvt.get_qual_comb_index.end',
752 'End of the procedure jty_tae_index_creation_pvt.get_qual_comb_index');
753 END IF;
755 EXCEPTION
756 WHEN L_SCHEMA_NOTFOUND THEN
757 x_return_status := FND_API.G_RET_STS_ERROR ;
758 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
759 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
760 'jtf.plsql.jty_tae_index_creation_pvt.get_qual_comb_index.l_schema_notfound',
761 'Schema name corresponding to JTF application not found');
762 END IF;
763
764 WHEN OTHERS THEN
765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
766 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
767 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
768 'jtf.plsql.jty_tae_index_creation_pvt.get_qual_comb_index.others',
769 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
770 END IF;
771
772 END get_qual_comb_index;
773
774
775 /**
776 * Procedure : CREATE_INDEX
777 * Type : private
778 * Pre_reqs :
779 * Description : index creation
780 * input : table JTF_TAE_QUAL_PRODUCTS
781 * : table JTF_TAE_QUAL_FACTORS
782 * output : indices created on JTF_TAE_OBJECT_INPUT
783 * return 0: failure
784 * 1: success
785 */
786 procedure CREATE_INDEX ( p_table_name IN VARCHAR2,
787 p_trans_id IN NUMBER,
788 p_source_id IN NUMBER,
789 p_program_name IN VARCHAR2,
790 p_mode IN VARCHAR2,
791 x_Return_Status OUT NOCOPY VARCHAR2,
792 p_run_mode IN VARCHAR2)
793 IS
794
795 i integer;
796 j integer;
797
798 v_statement varchar2(2000);
799 s_statement varchar2(2000);
800 alter_statement varchar2(2000);
801
802 l_table_tablespace varchar2(100);
803 l_idx_tablespace varchar2(100);
804 l_ora_username varchar2(100);
805
806 l_trans_idx_name varchar2(30);
807 l_matches_idx_name varchar2(30);
808 l_winners_idx_name varchar2(30);
809
810 lx_4841_idx_created varchar2(1);
811 lx_324347_idx_created varchar2(1);
812 prd_nulltae_mul_of_4841 varchar2(1);
813 prd_nulltae_mul_of_324347 varchar2(1);
814
815 lx_rev_4841_idx_created varchar2(1);
816 lx_rev_324347_idx_created varchar2(1);
817 prd_rev_nulltae_mul_of_4841 varchar2(1);
818 prd_rev_nulltae_mul_of_324347 varchar2(1);
819
820 l_create_index_flag varchar2(1);
821
822 l_dop NUMBER;
823 l_index_extn VARCHAR2(2);
824 l_qual_type_usg_id NUMBER;
825
826 l_tae_col_map VARCHAR2(30);
827 Cursor getProductList(cl_source_id number, cl_trans_id number) IS
828 SELECT P.qual_product_id qual_product_id,
829 p.RELATION_PRODUCT RELATION_PRODUCT,
830 MAX(p.index_name) index_name,
831 MAX(p.first_char_flag) first_char_flag,
832 COUNT(r.qual_factor_id) cartesian_terr_X_factors
833 FROM jtf_terr_denorm_rules_all d
834 ,jtf_terr_qtype_usgs_all jtqu
835 ,jtf_qual_type_usgs_all jqtu
836 ,JTF_TAE_QUAL_PRODUCTS P
837 , JTF_TAE_QUAL_PROD_FACTORS R
838 WHERE jtqu.qual_relation_product = p.relation_product
839 AND jqtu.source_id = d.source_id
840 AND jqtu.qual_type_id = p.trans_object_type_id
841 AND d.terr_id = jtqu.terr_id
842 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
843 AND d.source_id = p.source_id
844 AND P.qual_product_id = R.qual_product_id
845 AND P.BUILD_INDEX_FLAG = 'Y'
846 AND p.TRANS_OBJECT_TYPE_ID = cl_trans_id
847 AND P.SOURCE_ID = cl_source_id
848 GROUP BY P.qual_product_id, p.RELATION_PRODUCT
849 ORDER BY cartesian_terr_X_factors DESC, first_char_flag;
850
851 Cursor getFactorList(c_pid number) IS
852 SELECT DISTINCT TAE_COL_MAP, J2.INPUT_SELECTIVITY
853 FROM JTF_TAE_QUAL_PRODUCTS J3,
854 JTF_TAE_QUAL_FACTORS J2,
855 JTF_TAE_QUAL_PROD_FACTORS J1
856 WHERE J1.qual_product_id = c_pid
857 AND J1.qual_product_id = J3.qual_product_id
858 AND J1.qual_factor_id = J2.qual_factor_id
859 AND J2.USE_TAE_COL_IN_INDEX_FLAG = 'Y'
860 AND J2.TAE_COL_MAP is NOT NULL
861 ORDER BY J2.INPUT_SELECTIVITY;
862
863 Cursor extraIndexCandidates(cl_source_id number, cl_trans_id number) IS
864 SELECT P.qual_product_id qual_product_id,
865 p.RELATION_PRODUCT RELATION_PRODUCT,
866 p.index_name index_name,
867 p.first_char_flag first_char_flag,
868 rownum index_counter
869 FROM JTF_TAE_QUAL_PRODUCTS P
870 WHERE P.BUILD_INDEX_FLAG = 'Y'
871 AND P.FIRST_CHAR_FLAG = 'Y'
872 AND p.TRANS_OBJECT_TYPE_ID = cl_trans_id
873 AND P.SOURCE_ID = cl_source_id;
874
875 Cursor getReverseFactorList(cl_pid number) IS
876 select f.tae_col_map, qual_usg_id, input_selectivity
877 from jtf_tae_qual_prod_factors pf,
878 jtf_tae_qual_factors f
879 where pf.qual_product_id = cl_pid
880 and f.qual_factor_id = pf.qual_factor_id
881 and f.tae_col_map is not null
885 select NON_NULL_TAE_COL_MAPS, RELATION_PRODUCT
882 order by input_selectivity desc;
883
884 Cursor verifyProdNonNullTAEColMaps(cl_source_id number, cl_trans_id number, cp_product NUMBER, cp_non_null_tae_col_maps NUMBER) IS
886 from (
887 select count(*) NON_NULL_TAE_COL_MAPS,
888 p.qual_product_id QUAL_PRODUCT_ID,
889 p.relation_product RELATION_PRODUCT
890 from JTF_TAE_QUAL_products p,
891 JTF_TAE_QUAL_prod_factors pf,
892 JTF_TAE_QUAL_FACTORS f
893 where p.qual_product_id = pf.qual_product_id
894 and pf.qual_factor_id = f.qual_factor_id
895 and p.source_id = cl_source_id
896 and p.trans_object_type_id = cl_trans_id
897 and p.relation_product > 0
898 and tae_col_map is not null
899 and p.relation_product = cp_product
900 group by p.qual_product_id, p.relation_product
901 )
902 where NON_NULL_TAE_COL_MAPS = cp_non_null_tae_col_maps;
903
904 Cursor dea_getProductList(cl_source_id number, cl_trans_id number) IS
905 SELECT P.dea_attr_products_id dea_attr_products_id,
906 p.attr_relation_product attr_relation_product,
907 MAX(p.index_name) index_name,
908 MAX(p.first_char_flag) first_char_flag,
909 COUNT(r.dea_attr_factors_id) cartesian_terr_X_factors
910 FROM jty_denorm_dea_rules_all d
911 ,jtf_terr_qtype_usgs_all jtqu
912 ,jtf_qual_type_usgs_all jqtu
913 ,jty_dea_attr_products P
914 ,jty_dea_attr_prod_factors R
915 WHERE jtqu.qual_relation_product = p.attr_relation_product
916 AND jqtu.source_id = d.source_id
917 AND jqtu.qual_type_id = p.trans_type_id
918 AND d.terr_id = jtqu.terr_id
919 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
920 AND d.source_id = p.source_id
921 AND P.dea_attr_products_id = R.dea_attr_products_id
922 AND P.build_index_flag = 'Y'
923 AND p.trans_type_id = cl_trans_id
924 AND P.source_id = cl_source_id
925 GROUP BY P.dea_attr_products_id, p.attr_relation_product
926 ORDER BY cartesian_terr_X_factors DESC, first_char_flag;
927
928 Cursor dea_getFactorList(c_pid number) IS
929 SELECT DISTINCT TAE_COL_MAP, J2.INPUT_SELECTIVITY
930 FROM jty_dea_attr_products J3,
931 jty_dea_attr_factors J2,
932 jty_dea_attr_prod_factors J1
933 WHERE J1.dea_attr_products_id = c_pid
934 AND J1.dea_attr_products_id = J3.dea_attr_products_id
935 AND J1.dea_attr_factors_id = J2.dea_attr_factors_id
936 AND J2.USE_TAE_COL_IN_INDEX_FLAG = 'Y'
937 AND J2.TAE_COL_MAP is NOT NULL
938 ORDER BY J2.INPUT_SELECTIVITY;
939
940 Cursor dea_extraIndexCandidates(cl_source_id number, cl_trans_id number) IS
941 SELECT P.dea_attr_products_id dea_attr_products_id,
942 p.attr_relation_product attr_relation_product,
943 p.index_name index_name,
944 p.first_char_flag first_char_flag,
945 rownum index_counter
946 FROM jty_dea_attr_products P
947 WHERE P.BUILD_INDEX_FLAG = 'Y'
948 AND P.FIRST_CHAR_FLAG = 'Y'
949 AND p.trans_type_id = cl_trans_id
950 AND P.source_id = cl_source_id;
951
952 Cursor dea_getReverseFactorList(cl_pid number) IS
953 select f.tae_col_map, qual_usg_id, input_selectivity
954 from jty_dea_attr_prod_factors pf,
955 jty_dea_attr_factors f
956 where pf.dea_attr_products_id = cl_pid
957 and f.dea_attr_factors_id = pf.dea_attr_factors_id
958 and f.tae_col_map is not null
959 order by input_selectivity desc;
960
961 Cursor dea_ProdNonNullTAEColMaps(cl_source_id number, cl_trans_id number, cp_product NUMBER, cp_non_null_tae_col_maps NUMBER) IS
962 select NON_NULL_TAE_COL_MAPS, attr_relation_product
963 from (
964 select count(*) NON_NULL_TAE_COL_MAPS,
965 p.dea_attr_products_id dea_attr_products_id,
966 p.attr_relation_product attr_relation_product
967 from jty_dea_attr_products p,
968 jty_dea_attr_prod_factors pf,
969 jty_dea_attr_factors f
970 where p.dea_attr_products_id = pf.dea_attr_products_id
971 and pf.dea_attr_factors_id = f.dea_attr_factors_id
972 and p.source_id = cl_source_id
973 and p.trans_type_id = cl_trans_id
974 and p.attr_relation_product > 0
975 and tae_col_map is not null
976 and p.attr_relation_product = cp_product
977 group by p.dea_attr_products_id, p.attr_relation_product
978 )
979 where NON_NULL_TAE_COL_MAPS = cp_non_null_tae_col_maps;
980
981 Cursor c_all_indexes(cl_table_name varchar2, cl_owner varchar2) IS
982 select index_name
983 from all_indexes
984 where table_name = cl_table_name
985 and table_owner = cl_owner;
986
987
988 BEGIN
989 -- debug message
990 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
991 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
992 'jtf.plsql.jty_tae_index_creation_pvt.create_index.begin',
993 'Start of the procedure jty_tae_index_creation_pvt.create_index');
994 END IF;
995
996 x_return_status := FND_API.G_RET_STS_SUCCESS;
997
998 /* Initialize the flags */
999 lx_4841_idx_created := 'N';
1003 lx_rev_4841_idx_created := 'N';
1000 lx_324347_idx_created := 'N';
1001 prd_nulltae_mul_of_4841 := 'N';
1002 prd_nulltae_mul_of_324347 := 'N';
1004 lx_rev_324347_idx_created := 'N';
1005 prd_rev_nulltae_mul_of_4841 := 'N';
1006 prd_rev_nulltae_mul_of_324347 := 'N';
1007 l_create_index_flag := 'Y';
1008
1009 /* get default Degree of Parallelism */
1010 SELECT MIN(TO_NUMBER(v.value))
1011 INTO l_dop
1012 FROM v$parameter v
1013 WHERE v.name = 'parallel_max_servers'
1014 OR v.name = 'cpu_count';
1015
1016 -- debug message
1017 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1018 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1019 'jtf.plsql.jty_tae_index_creation_pvt.create_index.l_dop',
1020 'Default degree of parallelism : ' || l_dop);
1021 END IF;
1022
1023 /* get tablespace information */
1024 SELECT i.tablespace, i.index_tablespace, u.oracle_username
1025 INTO l_table_tablespace, l_idx_tablespace, l_ora_username
1026 FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
1027 WHERE a.application_short_name = 'JTF'
1028 AND a.application_id = i.application_id
1029 AND u.oracle_id = i.oracle_id;
1030
1031 -- debug message
1032 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1033 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1034 'jtf.plsql.jty_tae_index_creation_pvt.create_index.tablespace',
1035 'Table tablespace : ' || l_table_tablespace || ' Index tablespace : ' || l_idx_tablespace ||
1036 ' Schema Name : ' || l_ora_username);
1037 END IF;
1038
1039 -- default INDEX STORAGE parameters
1040 s_statement := s_statement || ' TABLESPACE ' || l_idx_tablespace ;
1041 s_statement := s_statement || ' STORAGE(INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED ';
1042 s_statement := s_statement || ' PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT) ';
1043 s_statement := s_statement || ' PCTFREE 10 INITRANS 10 MAXTRANS 255 ';
1044 s_statement := s_statement || ' COMPUTE STATISTICS ';
1045 s_statement := s_statement || ' NOLOGGING PARALLEL ' || l_dop;
1046
1047 BEGIN
1048 SELECT index_extn
1049 INTO l_index_extn
1050 FROM jty_trans_usg_pgm_details
1051 WHERE source_id = p_source_id
1052 AND trans_type_id = p_trans_id
1053 AND program_name = p_program_name;
1054 EXCEPTION
1055 WHEN NO_DATA_FOUND THEN
1056 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1057 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1058 'jtf.plsql.jty_tae_index_creation_pvt.create_index.no_index_extn',
1059 'No row in table jty_trans_usg_pgm_details corresponding to source : ' || p_source_id || ' transaction : ' ||
1060 p_trans_id || ' program name : ' || p_program_name);
1061 END IF;
1062 RAISE;
1063 END;
1064
1065 BEGIN
1066 SELECT qual_type_usg_id
1067 INTO l_qual_type_usg_id
1068 FROM jtf_qual_type_usgs_all
1069 WHERE source_id = p_source_id
1070 AND qual_type_id = p_trans_id;
1071 EXCEPTION
1072 WHEN NO_DATA_FOUND THEN
1073 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1074 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1075 'jtf.plsql.jty_tae_index_creation_pvt.create_index.no_qual_type_usg_id',
1076 'No row in table jtf_qual_type_usgs_all corresponding to source : ' || p_source_id || ' and transaction : ' ||
1077 p_trans_id);
1078 END IF;
1079 RAISE;
1080 END;
1081
1082 -- debug message
1083 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1084 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1085 'jtf.plsql.jty_tae_index_creation_pvt.create_index.l_index_extn',
1086 'Index extension for the usage : ' || l_index_extn ||
1087 ' qual_type-usg_id : ' || l_qual_type_usg_id);
1088 END IF;
1089
1090 -- indexes for TRANS table
1091 IF (p_run_mode = 'TRANS') THEN
1092
1093 -- debug message
1094 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1095 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1096 'jtf.plsql.jty_tae_index_creation_pvt.create_index.existing_index',
1097 'Existing Indexes');
1098 FOR c1 IN c_all_indexes(p_table_name, l_ora_username) LOOP
1099 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1100 'jtf.plsql.jty_tae_index_creation_pvt.create_index.existing_index',
1101 'Index Name : ' || c1.index_name);
1102 END LOOP;
1103 END IF;
1104
1105 IF (p_mode = 'TOTAL') THEN
1106 l_trans_idx_name := 'JTF_TAE_TN' || ABS(l_qual_type_usg_id) || '_UK_' || l_index_extn || 'T';
1107 ELSE
1108 l_trans_idx_name := 'JTF_TAE_TN' || ABS(l_qual_type_usg_id) || '_UK_' || l_index_extn || 'I';
1109 END IF;
1110
1111 v_statement := 'CREATE INDEX ' || l_ora_username ||'.' || l_trans_idx_name || ' ON ' || p_table_name;
1112 v_statement := v_statement || ' ( TRANS_OBJECT_ID, TRANS_DETAIL_OBJECT_ID ) ';
1113 IF (p_mode <> 'INCREMENTAL') THEN
1114 v_statement := v_statement || ' LOCAL ';
1115 END IF;
1116 v_statement := v_statement || s_statement;
1117
1121 'jtf.plsql.jty_tae_index_creation_pvt.create_index.index_creation',
1118 -- debug message
1119 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1120 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1122 '1Index created with the statement : ' || v_statement);
1123 END IF;
1124
1125 EXECUTE IMMEDIATE v_statement;
1126
1127 alter_statement := 'ALTER INDEX ' || l_ora_username || '.' || l_trans_idx_name || ' NOPARALLEL';
1128 EXECUTE IMMEDIATE alter_statement;
1129
1130 /* Create Qualifier Combination Dynamic Indexes */
1131 FOR prd IN getProductList(p_source_id, p_trans_id) LOOP
1132 l_create_index_flag := 'Y';
1133 -- EIHSU 08/14/02: Set flags for determining if relation_prods
1134 -- are NULL TAE_COL_MAP multiples of the specific QCombinations
1135 -- as described by JDOCHERT 08/04/02
1136
1137 /* INDEX CREATION LOGIC PREPROCESSING */
1138 prd_nulltae_mul_of_4841 := 'N';
1139 prd_nulltae_mul_of_324347 := 'N';
1140
1141 IF mod(prd.RELATION_PRODUCT, 324347) = 0 OR mod(prd.RELATION_PRODUCT, 353393) = 0 THEN
1142 for verifiedProd in verifyProdNonNullTAEColMaps(p_source_id, p_trans_id, prd.RELATION_PRODUCT, 3) loop
1143 prd_nulltae_mul_of_324347 := 'Y';
1144 end loop;
1145 ELSIF mod(prd.RELATION_PRODUCT, 4841) = 0 THEN
1146 for verifiedProd in verifyProdNonNullTAEColMaps(p_source_id, p_trans_id, prd.RELATION_PRODUCT, 2) loop
1147 prd_nulltae_mul_of_4841 := 'Y';
1148 end loop;
1149 END IF;
1150
1151 /* INDEX CREATION METHOD LOGIC */
1152 IF (prd_nulltae_mul_of_4841 = 'Y') THEN
1153 IF (lx_4841_idx_created = 'N') THEN
1154 get_qual_comb_index (
1155 p_rel_prod => 4841,
1156 p_reverse_flag => 'N',
1157 p_qual_type_usg_id => l_qual_type_usg_id,
1158 p_table_name => p_table_name,
1159 p_index_extn => l_index_extn,
1160 p_run_mode => p_run_mode,
1161 p_mode => p_mode,
1162 x_return_status => x_return_status,
1163 x_statement => V_STATEMENT,
1164 alter_statement => ALTER_STATEMENT );
1165
1166 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1167 -- debug message
1168 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1169 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1170 'jtf.plsql.jty_tae_index_creation_pvt.create_index.4841',
1171 'API jty_tae_index_creation_pvt.get_qual_comb_index has failed for qualifier comb 4841');
1172 END IF;
1173
1174 RAISE FND_API.G_EXC_ERROR;
1175 END IF;
1176
1177 lx_4841_idx_created := 'Y';
1178 ELSE
1179 l_create_index_flag := 'N';
1180 END IF;
1181
1182 ELSIF (prd_nulltae_mul_of_324347 = 'Y') THEN
1183 IF (lx_324347_idx_created = 'N') THEN
1184 get_qual_comb_index (
1185 p_rel_prod => 324347,
1186 p_reverse_flag => 'N',
1187 p_qual_type_usg_id => l_qual_type_usg_id,
1188 p_table_name => p_table_name,
1189 p_index_extn => l_index_extn,
1190 p_run_mode => p_run_mode,
1191 p_mode => p_mode,
1192 x_return_status => x_return_status,
1193 x_statement => V_STATEMENT,
1194 alter_statement => ALTER_STATEMENT );
1195
1196 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1197 -- debug message
1198 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1199 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1200 'jtf.plsql.jty_tae_index_creation_pvt.create_index.324347',
1201 'API jty_tae_index_creation_pvt.get_qual_comb_index has failed for qualifier comb 324347');
1202 END IF;
1203
1204 RAISE FND_API.G_EXC_ERROR;
1205 END IF;
1206
1207 lx_324347_idx_created := 'Y';
1208 ELSE
1209 l_create_index_flag := 'N';
1210 END IF;
1211
1212 ELSE
1213 IF (p_mode = 'TOTAL') THEN
1214 l_trans_idx_name := prd.index_name || l_index_extn || 'T';
1215 ELSE
1216 l_trans_idx_name := prd.index_name || l_index_extn || 'I';
1217 END IF;
1218
1219 v_statement := 'CREATE INDEX '|| l_ora_username ||'.' || l_trans_idx_name || ' ON ' || p_table_name || '( ';
1220 IF prd.first_char_flag = 'Y' THEN
1221 v_statement := v_statement || first_char_col_name || ',';
1222 END IF;
1223
1224 j:=1;
1225
1226 -- for each factor of product
1227 FOR factor IN getFactorList(prd.qual_product_id) LOOP
1228 IF j<>1 THEN
1229 v_statement := v_statement || ',' ;
1230 END IF;
1231 v_statement := v_statement || factor.TAE_COL_MAP;
1232 j:=j+1;
1233 END LOOP;
1234
1235 v_statement := v_statement || ') ';
1236 IF (p_mode <> 'INCREMENTAL') THEN
1237 v_statement := v_statement || ' LOCAL ';
1238 END IF;
1239
1240 IF (j <= 1) THEN
1244 END IF;
1241 l_create_index_flag := 'N';
1242 END IF;
1243
1245
1246 /* Append Storage Parameter Information to Index Definition */
1247 v_statement := v_statement || s_statement;
1248
1249 IF l_create_index_flag = 'Y' THEN
1250 -- debug message
1251 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1252 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1253 'jtf.plsql.jty_tae_index_creation_pvt.create_index.index_creation',
1254 '2Index created with the statement : ' || prd.qual_product_id || v_statement);
1255 END IF;
1256
1257 EXECUTE IMMEDIATE v_statement;
1258
1259 IF prd_nulltae_mul_of_4841 = 'Y' OR prd_nulltae_mul_of_324347 = 'Y'
1260 THEN
1261 EXECUTE IMMEDIATE alter_statement;
1262 ELSE
1263 alter_statement := 'ALTER INDEX ' || l_ora_username || '.' || l_trans_idx_name || ' NOPARALLEL';
1264 EXECUTE IMMEDIATE alter_statement;
1265 END IF;
1266 END IF;
1267
1268 END LOOP; /* end loop FOR prd IN getProductList */
1269
1270 -- debug message
1271 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1272 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1273 'jtf.plsql.jty_tae_index_creation_pvt.create_index.trans_index',
1274 'Done creating index for TRANS table');
1275 END IF;
1276
1277 -- Additional Indexes for TRANS table - eihsu 03/06/2002
1278 FOR idxCand in extraIndexCandidates(p_source_id, p_trans_id) LOOP
1279 l_create_index_flag := 'Y';
1280
1281 /* INDEX CREATION LOGIC PREPROCESSING */
1282 prd_rev_nulltae_mul_of_324347 := 'N';
1283
1284 IF (mod(idxCand.RELATION_PRODUCT, 324347) = 0 OR mod(idxCand.RELATION_PRODUCT, 353393) = 0) THEN
1285 for verifiedProd in verifyProdNonNullTAEColMaps(p_source_id, p_trans_id, idxCand.RELATION_PRODUCT, 3) loop
1286 prd_rev_nulltae_mul_of_324347 := 'Y';
1287 end loop;
1288 END IF;
1289
1290 /* REV INDEX CREATION METHOD LOGIC */
1291 IF (prd_rev_nulltae_mul_of_324347 = 'Y') THEN
1292 IF (lx_rev_324347_idx_created = 'N') THEN
1293 get_qual_comb_index (
1294 p_rel_prod => 324347,
1295 p_reverse_flag => 'Y',
1296 p_qual_type_usg_id => l_qual_type_usg_id,
1297 p_table_name => p_table_name,
1298 p_index_extn => l_index_extn,
1299 p_run_mode => p_run_mode,
1300 p_mode => p_mode,
1301 x_return_status => x_return_status,
1302 x_statement => V_STATEMENT,
1303 alter_statement => ALTER_STATEMENT );
1304
1305 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1306 -- debug message
1307 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1308 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1309 'jtf.plsql.jty_tae_index_creation_pvt.create_index.324347_reverse',
1310 'API jty_tae_index_creation_pvt.get_qual_comb_index has failed for qualifier comb 324347_reverse');
1311 END IF;
1312
1313 RAISE FND_API.G_EXC_ERROR;
1314 END IF;
1315
1316 lx_rev_324347_idx_created := 'Y';
1317 ELSE
1318 l_create_index_flag := 'N';
1319 END IF;
1320
1321 ELSE
1322 IF (p_mode = 'TOTAL') THEN
1323 l_trans_idx_name := idxCand.index_name || l_index_extn || 'XT';
1324 ELSE
1325 l_trans_idx_name := idxCand.index_name || l_index_extn || 'XI';
1326 END IF;
1327
1328 v_statement := 'CREATE INDEX '|| l_ora_username ||'.' || l_trans_idx_name || ' ON ' || p_table_name || '( ';
1329
1330 j:=1;
1331 l_tae_col_map := 'ABC';
1332
1333 -- for each factor of product
1334 for xFactor IN getReverseFactorList(idxCand.qual_product_id) loop
1335 IF l_tae_col_map <> xFactor.TAE_COL_MAP
1336 THEN
1337 if j<>1 then
1338 v_statement := v_statement || ',' ;
1339 end if;
1340 v_statement := v_statement || xFactor.TAE_COL_MAP;
1341 j:=j+1;
1342 END IF;
1343 l_tae_col_map := xFactor.TAE_COL_MAP;
1344 end loop;
1345
1346 IF (j <= 1) THEN
1347 l_create_index_flag := 'N';
1348 END IF;
1349
1350 v_statement := v_statement || ') ';
1351 IF (p_mode <> 'INCREMENTAL') THEN
1352 v_statement := v_statement || ' LOCAL ';
1353 END IF;
1354
1355 END IF; /* end IF (prd_rev_nulltae_mul_of_324347 = 'Y') */
1356
1357 v_statement := v_statement || s_statement;
1358
1359 IF l_create_index_flag = 'Y' THEN
1360 -- debug message
1361 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1362 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1363 'jtf.plsql.jty_tae_index_creation_pvt.create_index.index_creation',
1364 '3Index created with the statement : ' || idxCand.qual_product_id || v_statement);
1365 END IF;
1366
1367 EXECUTE IMMEDIATE v_statement;
1368
1369 IF prd_rev_nulltae_mul_of_324347 = 'Y' THEN
1373 EXECUTE IMMEDIATE alter_statement;
1370 EXECUTE IMMEDIATE alter_statement;
1371 ELSE
1372 alter_statement := 'ALTER INDEX ' || l_ora_username || '.' || l_trans_idx_name || ' NOPARALLEL';
1374 END IF;
1375
1376 END IF; /* end IF l_create_index_flag = 'Y' */
1377
1378 END LOOP; /* end loop FOR idxCand in extraIndexCandidates */
1379
1380 -- debug message
1381 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1382 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1383 'jtf.plsql.jty_tae_index_creation_pvt.create_index.trans_reverse_index',
1384 'Done creating reverse index for TRANS table');
1385 END IF;
1386
1387 -- indexes for DEA_TRANS table
1388 ELSIF (p_run_mode = 'DEA_TRANS') THEN
1389
1390 l_trans_idx_name := 'JTF_TAE_DE' || ABS(l_qual_type_usg_id) || '_UK_' || l_index_extn;
1391
1392 v_statement := 'CREATE INDEX ' || l_ora_username ||'.' || l_trans_idx_name || ' ON ' || p_table_name;
1393 v_statement := v_statement || ' ( TRANS_OBJECT_ID, TRANS_DETAIL_OBJECT_ID ) LOCAL ';
1394 v_statement := v_statement || s_statement;
1395
1396 EXECUTE IMMEDIATE v_statement;
1397
1398 alter_statement := 'ALTER INDEX ' || l_ora_username || '.' || l_trans_idx_name || ' NOPARALLEL';
1399 EXECUTE IMMEDIATE alter_statement;
1400
1401 /* Create Qualifier Combination Dynamic Indexes */
1402 FOR prd IN dea_getProductList(p_source_id, p_trans_id) LOOP
1403 l_create_index_flag := 'Y';
1404 -- EIHSU 08/14/02: Set flags for determining if relation_prods
1405 -- are NULL TAE_COL_MAP multiples of the specific QCombinations
1406 -- as described by JDOCHERT 08/04/02
1407
1408 /* INDEX CREATION LOGIC PREPROCESSING */
1409 prd_nulltae_mul_of_4841 := 'N';
1410 prd_nulltae_mul_of_324347 := 'N';
1411
1412 IF mod(prd.ATTR_RELATION_PRODUCT, 324347) = 0 OR mod(prd.ATTR_RELATION_PRODUCT, 353393) = 0 THEN
1413 for verifiedProd in dea_ProdNonNullTAEColMaps(p_source_id, p_trans_id, prd.ATTR_RELATION_PRODUCT, 3) loop
1414 prd_nulltae_mul_of_324347 := 'Y';
1415 end loop;
1416 ELSIF mod(prd.ATTR_RELATION_PRODUCT, 4841) = 0 THEN
1417 for verifiedProd in verifyProdNonNullTAEColMaps(p_source_id, p_trans_id, prd.ATTR_RELATION_PRODUCT, 2) loop
1418 prd_nulltae_mul_of_4841 := 'Y';
1419 end loop;
1420 END IF;
1421
1422 /* INDEX CREATION METHOD LOGIC */
1423 IF (prd_nulltae_mul_of_4841 = 'Y') THEN
1424 IF (lx_4841_idx_created = 'N') THEN
1425 get_qual_comb_index (
1426 p_rel_prod => 4841,
1427 p_reverse_flag => 'N',
1428 p_qual_type_usg_id => l_qual_type_usg_id,
1429 p_table_name => p_table_name,
1430 p_index_extn => l_index_extn,
1431 p_run_mode => p_run_mode,
1432 p_mode => p_mode,
1433 x_return_status => x_return_status,
1434 x_statement => V_STATEMENT,
1435 alter_statement => ALTER_STATEMENT );
1436
1437 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1438 -- debug message
1439 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1440 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1441 'jtf.plsql.jty_tae_index_creation_pvt.create_index.dea_4841',
1442 'API jty_tae_index_creation_pvt.get_qual_comb_index has failed for qualifier comb dea_4841');
1443 END IF;
1444
1445 RAISE FND_API.G_EXC_ERROR;
1446 END IF;
1447
1448 lx_4841_idx_created := 'Y';
1449 ELSE
1450 l_create_index_flag := 'N';
1451 END IF;
1452
1453 ELSIF (prd_nulltae_mul_of_324347 = 'Y') THEN
1454 IF (lx_324347_idx_created = 'N') THEN
1455 get_qual_comb_index (
1456 p_rel_prod => 324347,
1457 p_reverse_flag => 'N',
1458 p_qual_type_usg_id => l_qual_type_usg_id,
1459 p_table_name => p_table_name,
1460 p_index_extn => l_index_extn,
1461 p_run_mode => p_run_mode,
1462 p_mode => p_mode,
1463 x_return_status => x_return_status,
1464 x_statement => V_STATEMENT,
1465 alter_statement => ALTER_STATEMENT );
1466
1467 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1468 -- debug message
1469 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1470 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1471 'jtf.plsql.jty_tae_index_creation_pvt.create_index.dea_324347',
1472 'API jty_tae_index_creation_pvt.get_qual_comb_index has failed for qualifier comb dea_324347');
1473 END IF;
1474
1475 RAISE FND_API.G_EXC_ERROR;
1476 END IF;
1477
1478 lx_324347_idx_created := 'Y';
1479 ELSE
1480 l_create_index_flag := 'N';
1481 END IF;
1482
1483 ELSE
1484 v_statement := 'CREATE INDEX '|| l_ora_username ||'.' || prd.index_name || l_index_extn || 'D ON ' || p_table_name || '( ';
1485
1486 IF prd.first_char_flag = 'Y' THEN
1487 v_statement := v_statement || first_char_col_name || ',';
1488 END IF;
1489
1490 j:=1;
1491
1492 -- for each factor of product
1496 END IF;
1493 FOR factor IN dea_getFactorList(prd.dea_attr_products_id) LOOP
1494 IF j<>1 THEN
1495 v_statement := v_statement || ',' ;
1497 v_statement := v_statement || factor.TAE_COL_MAP;
1498 j:=j+1;
1499 END LOOP;
1500
1501 v_statement := v_statement || ') LOCAL ';
1502
1503 IF (j <= 1) THEN
1504 l_create_index_flag := 'N';
1505 END IF;
1506
1507 END IF;
1508
1509 /* Append Storage Parameter Information to Index Definition */
1510 v_statement := v_statement || s_statement;
1511
1512 IF l_create_index_flag = 'Y' THEN
1513 EXECUTE IMMEDIATE v_statement;
1514
1515 IF prd_nulltae_mul_of_4841 = 'Y' OR prd_nulltae_mul_of_324347 = 'Y'
1516 THEN
1517 EXECUTE IMMEDIATE alter_statement;
1518 ELSE
1519 alter_statement := 'ALTER INDEX ' || l_ora_username || '.' || prd.index_name || l_index_extn || 'D NOPARALLEL';
1520 EXECUTE IMMEDIATE alter_statement;
1521 END IF;
1522 END IF;
1523
1524 END LOOP; /* end loop FOR prd IN getProductList */
1525
1526 -- debug message
1527 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1528 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1529 'jtf.plsql.jty_tae_index_creation_pvt.create_index.dea_trans_index',
1530 'Done creating index for DEA_TRANS table');
1531 END IF;
1532
1533 FOR idxCand in dea_extraIndexCandidates(p_source_id, p_trans_id) LOOP
1534 l_create_index_flag := 'Y';
1535
1536 /* INDEX CREATION LOGIC PREPROCESSING */
1537 prd_rev_nulltae_mul_of_324347 := 'N';
1538
1539 IF (mod(idxCand.ATTR_RELATION_PRODUCT, 324347) = 0 OR mod(idxCand.ATTR_RELATION_PRODUCT, 353393) = 0) THEN
1540 for verifiedProd in dea_ProdNonNullTAEColMaps(p_source_id, p_trans_id, idxCand.ATTR_RELATION_PRODUCT, 3) loop
1541 prd_rev_nulltae_mul_of_324347 := 'Y';
1542 end loop;
1543 END IF;
1544
1545 /* REV INDEX CREATION METHOD LOGIC */
1546 IF (prd_rev_nulltae_mul_of_324347 = 'Y') THEN
1547 IF (lx_rev_324347_idx_created = 'N') THEN
1548 get_qual_comb_index (
1549 p_rel_prod => 324347,
1550 p_reverse_flag => 'Y',
1551 p_qual_type_usg_id => l_qual_type_usg_id,
1552 p_table_name => p_table_name,
1553 p_index_extn => l_index_extn,
1554 p_run_mode => p_run_mode,
1555 p_mode => p_mode,
1556 x_return_status => x_return_status,
1557 x_statement => V_STATEMENT,
1558 alter_statement => ALTER_STATEMENT );
1559
1560 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1561 -- debug message
1562 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1563 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1564 'jtf.plsql.jty_tae_index_creation_pvt.create_index.dea_324347_reverse',
1565 'API jty_tae_index_creation_pvt.get_qual_comb_index has failed for qualifier comb dea_324347_reverse');
1566 END IF;
1567
1568 RAISE FND_API.G_EXC_ERROR;
1569 END IF;
1570
1571 lx_rev_324347_idx_created := 'Y';
1572 ELSE
1573 l_create_index_flag := 'N';
1574 END IF;
1575
1576 ELSE
1577 v_statement := 'CREATE INDEX ' || l_ora_username || '.' || idxCand.index_name || l_index_extn || 'XD' || ' ON ' ||
1578 p_table_name || '( ';
1579
1580 j:=1;
1581
1582 -- for each factor of product
1583 for xFactor IN dea_getReverseFactorList(idxCand.dea_attr_products_id) loop
1584 if j<>1 then
1585 v_statement := v_statement || ',' ;
1586 end if;
1587 v_statement := v_statement || xFactor.TAE_COL_MAP;
1588 j:=j+1;
1589 end loop;
1590
1591 v_statement := v_statement || ') LOCAL ';
1592
1593 IF (j <= 1) THEN
1594 l_create_index_flag := 'N';
1595 END IF;
1596
1597 END IF; /* end IF (prd_rev_nulltae_mul_of_324347 = 'Y') */
1598
1599 v_statement := v_statement || s_statement;
1600
1601 IF l_create_index_flag = 'Y' THEN
1602 EXECUTE IMMEDIATE v_statement;
1603
1604 IF prd_rev_nulltae_mul_of_324347 = 'Y' THEN
1605 EXECUTE IMMEDIATE alter_statement;
1606 ELSE
1607 alter_statement := 'ALTER INDEX ' || l_ora_username || '.' || idxCand.index_name || l_index_extn ||'XD NOPARALLEL';
1608 EXECUTE IMMEDIATE alter_statement;
1609 END IF;
1610
1611 END IF; /* end IF l_create_index_flag = 'Y' */
1612
1613 END LOOP; /* end loop FOR idxCand in extraIndexCandidates */
1614
1615 -- debug message
1616 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1617 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1618 'jtf.plsql.jty_tae_index_creation_pvt.create_index.dea_trans_reverse_index',
1619 'Done creating reverse index for DEA_TRANS table');
1620 END IF;
1621
1622 /* no index is created on match table as the tables is always accessed with full table scan
1623 -- index for MATCHES table
1624 ELSIF (p_run_mode = 'MATCH') THEN
1628 v_statement := v_statement || ' ( TRANS_OBJECT_ID, TRANS_DETAIL_OBJECT_ID ) LOCAL ';
1625 l_matches_idx_name := substr(p_table_name, 1, 27) || '_ND';
1626
1627 v_statement := 'CREATE INDEX ' || l_ora_username || '.' || l_matches_idx_name || ' ON ' || p_table_name;
1629 v_statement := v_statement || s_statement;
1630
1631 EXECUTE IMMEDIATE v_statement;
1632
1633 -- debug message
1634 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1635 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1636 'jtf.plsql.jty_tae_index_creation_pvt.create_index.match',
1637 'Done creating index for MATCH table');
1638 END IF;
1639 */
1640
1641 -- index for WINNERS table
1642 ELSIF (p_run_mode = 'WINNER') THEN
1643 l_winners_idx_name := substr(p_table_name, 1, 27) || '_ND';
1644
1645 v_statement := 'CREATE INDEX ' || l_ora_username ||'.' || l_winners_idx_name || ' ON ' || p_table_name;
1646 v_statement := v_statement || ' ( TRANS_OBJECT_ID, RESOURCE_ID, GROUP_ID ) LOCAL ';
1647 v_statement := v_statement || s_statement;
1648
1649 EXECUTE IMMEDIATE v_statement;
1650
1651 -- debug message
1652 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1653 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1654 'jtf.plsql.jty_tae_index_creation_pvt.create_index.winner',
1655 'Done creating index for WINNER table');
1656 END IF;
1657
1658 /* no index is created on l1->5 and wt tables as these tables are always accessed with full table scan
1659 -- index for TEMP WINNER table
1660 ELSIF (p_run_mode = 'TEMP_WINNER') THEN
1661 l_matches_idx_name := substr(p_table_name, 1, 27) || '_ND';
1662
1663 v_statement := 'CREATE INDEX ' ||l_ora_username || '.' || l_matches_idx_name || ' ON ' || p_table_name;
1664 v_statement := v_statement || ' ( TRANS_OBJECT_ID, TRANS_DETAIL_OBJECT_ID ) LOCAL ';
1665 v_statement := v_statement || s_statement;
1666
1667 EXECUTE IMMEDIATE v_statement;
1668
1669 -- debug message
1670 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1671 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1672 'jtf.plsql.jty_tae_index_creation_pvt.create_index.temp_winner',
1673 'Done creating index for TEMP WINNER table');
1674 END IF;
1675 */
1676
1677 END IF; /* end IF (p_run_mode = 'TRANS') */
1678
1679 -- debug message
1680 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1681 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1682 'jtf.plsql.jty_tae_index_creation_pvt.create_index.end',
1683 'End of the procedure jty_tae_index_creation_pvt.create_index');
1684 END IF;
1685
1686 EXCEPTION
1687 WHEN NO_DATA_FOUND THEN
1688 x_return_status := FND_API.G_RET_STS_ERROR ;
1689 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1690 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1691 'jtf.plsql.jty_tae_index_creation_pvt.create_index.no_data_found',
1692 'API jty_tae_index_creation_pvt.create_index has failed with no_data_found');
1693 END IF;
1694
1695 WHEN FND_API.G_EXC_ERROR THEN
1696 x_return_status := FND_API.G_RET_STS_ERROR ;
1697 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1698 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1699 'jtf.plsql.jty_tae_index_creation_pvt.create_index.g_exc_error',
1700 'jty_tae_index_creation_pvt.create_index has failed with G_EXC_ERROR exception');
1701 END IF;
1702
1703 WHEN OTHERS THEN
1704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1705 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1706 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1707 'jtf.plsql.jty_tae_index_creation_pvt.create_index.others',
1708 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
1709 END IF;
1710
1711 END CREATE_INDEX;
1712
1713 PROCEDURE DROP_TABLE_INDEXES( p_table_name IN VARCHAR2
1714 , x_return_status OUT NOCOPY VARCHAR2 ) IS
1715
1716 v_statement varchar2(800);
1717
1718 l_status VARCHAR2(30);
1719 l_industry VARCHAR2(30);
1720 l_jtf_schema VARCHAR2(30);
1721
1722 Cursor getIndexList(cl_table_name varchar2, cl_jtf_schema varchar2) IS
1723 SELECT aidx.owner, aidx.INDEX_NAME
1724 FROM DBA_INDEXES aidx
1725 WHERE aidx.table_name = cl_table_name
1726 AND aidx.table_owner = cl_jtf_schema
1727 AND aidx.index_name not in ('JTF_TAE_TN1002_CASE_N1W', 'JTF_TAE_TN1003_CASE_N1W', 'JTF_TAE_TN1004_CASE_N1W', 'JTF_TAE_TN1105_CASE_N1W', 'JTF_TAE_TN1106_CASE_N1W');
1728
1729 L_SCHEMA_NOTFOUND EXCEPTION;
1730 BEGIN
1731 -- debug message
1732 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1733 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1734 'jtf.plsql.jty_tae_index_creation_pvt.drop_table_indexes.begin',
1735 'Start of the procedure jty_tae_index_creation_pvt.drop_table_indexes');
1736 END IF;
1737
1738 x_return_status := FND_API.G_RET_STS_SUCCESS;
1739
1740 IF(FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_jtf_schema)) THEN
1741 NULL;
1742 END IF;
1743
1744 IF (l_jtf_schema IS NULL) THEN
1748 -- for each index
1745 RAISE L_SCHEMA_NOTFOUND;
1746 END IF;
1747
1749 FOR idx IN getIndexList(p_table_name, l_jtf_schema) LOOP
1750
1751 v_statement := 'DROP INDEX ' || idx.owner || '.' || idx.index_name;
1752
1753 EXECUTE IMMEDIATE v_statement;
1754
1755 END LOOP;
1756
1757 -- debug message
1758 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1759 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1760 'jtf.plsql.jty_tae_index_creation_pvt.drop_table_indexes.end',
1761 'End of the procedure jty_tae_index_creation_pvt.drop_table_indexes');
1762 END IF;
1763
1764 EXCEPTION
1765 WHEN L_SCHEMA_NOTFOUND THEN
1766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1767 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1768 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1769 'jtf.plsql.jty_tae_index_creation_pvt.drop_table_indexes.l_schema_notfound',
1770 'Schema name corresponding to JTF application not found');
1771 END IF;
1772
1773 WHEN OTHERS THEN
1774 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1775 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1776 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1777 'jtf.plsql.jty_tae_index_creation_pvt.drop_table_indexes.others',
1778 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
1779 END IF;
1780
1781 END DROP_TABLE_INDEXES;
1782
1783
1784 PROCEDURE TRUNCATE_TABLE( p_TABLE_NAME IN VARCHAR2,
1785 x_return_status OUT NOCOPY VARCHAR2 )
1786 IS
1787
1788 l_status VARCHAR2(30);
1789 l_industry VARCHAR2(30);
1790 l_jtf_schema VARCHAR2(30);
1791
1792 v_statement varchar2(200);
1793
1794 L_SCHEMA_NOTFOUND EXCEPTION;
1795
1796 BEGIN
1797
1798 -- debug message
1799 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1800 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1801 'jtf.plsql.jty_tae_index_creation_pvt.truncate_table.start',
1802 'Start of the procedure jty_tae_index_creation_pvt.truncate_table');
1803 END IF;
1804
1805 x_return_status := FND_API.G_RET_STS_SUCCESS;
1806
1807 IF(FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_jtf_schema)) THEN
1808 NULL;
1809 END IF;
1810
1811 IF (l_jtf_schema IS NULL) THEN
1812 RAISE L_SCHEMA_NOTFOUND;
1813 END IF;
1814
1815 v_statement := 'TRUNCATE TABLE ' || l_jtf_schema || '.' || p_TABLE_NAME || ' DROP STORAGE';
1816 EXECUTE IMMEDIATE v_statement;
1817
1818 -- debug message
1819 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1820 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1821 'jtf.plsql.jty_tae_index_creation_pvt.truncate_table.end',
1822 'End of the procedure jty_tae_index_creation_pvt.truncate_table');
1823 END IF;
1824
1825 EXCEPTION
1826 WHEN OTHERS THEN
1827 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1828 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1829 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1830 'jtf.plsql.jty_tae_index_creation_pvt.truncate_table.other',
1831 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
1832 END IF;
1833
1834 END TRUNCATE_TABLE;
1835
1836
1837 PROCEDURE ANALYZE_TABLE_INDEX( p_table_name IN VARCHAR2,
1838 p_percent IN NUMBER,
1839 x_return_status OUT NOCOPY VARCHAR2 )
1840 IS
1841
1842 l_status VARCHAR2(30);
1843 l_industry VARCHAR2(30);
1844 l_jtf_schema VARCHAR2(30);
1845
1846 L_SCHEMA_NOTFOUND EXCEPTION;
1847
1848 BEGIN
1849
1850 -- debug message
1851 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1852 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1853 'jtf.plsql.jty_tae_index_creation_pvt.analyze_table_index.start',
1854 'Start of the procedure jty_tae_index_creation_pvt.analyze_table_index');
1855 END IF;
1856
1857 x_return_status := FND_API.G_RET_STS_SUCCESS;
1858
1859 IF(FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_jtf_schema)) THEN
1860 NULL;
1861 END IF;
1862
1863 IF (l_jtf_schema IS NULL) THEN
1864 RAISE L_SCHEMA_NOTFOUND;
1865 END IF;
1866
1867 FND_STATS.GATHER_TABLE_STATS(
1868 ownname => l_jtf_schema,
1869 tabname => P_TABLE_NAME,
1870 percent => P_PERCENT,
1871 degree => null,
1872 partname => null,
1873 backup_flag => null,
1874 cascade => null,
1875 granularity =>'DEFAULT',
1876 hmode => 'FULL'
1877 );
1878
1879 -- debug message
1880 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1881 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1882 'jtf.plsql.jty_tae_index_creation_pvt.analyze_table_index.end',
1883 'End of the procedure jty_tae_index_creation_pvt.analyze_table_index');
1884 END IF;
1888 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1885
1886 EXCEPTION
1887 WHEN L_SCHEMA_NOTFOUND THEN
1889 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1890 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1891 'jtf.plsql.jty_tae_index_creation_pvt.analyze_table_index.l_schema_notfound',
1892 'Schema name corresponding to JTF application not found');
1893 END IF;
1894
1895 WHEN OTHERS THEN
1896 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1897 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1898 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1899 'jtf.plsql.jty_tae_index_creation_pvt.analyze_table_index.other',
1900 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
1901 END IF;
1902
1903 END ANALYZE_TABLE_INDEX;
1904
1905
1906 END jty_tae_index_creation_pvt;