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