[Home] [Help]
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: -- ---------------------------------------------------
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
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;
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:
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
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
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;
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;
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:
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
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:
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;
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;
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
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
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
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
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
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 ;
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;
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;
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:
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;
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;
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;
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;
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
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
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
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 ;
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,
415: 'jtf.plsql.jty_tae_index_creation_pvt.dea_selectivity.others',
416: substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
417: END IF;
418:
419: END DEA_SELECTIVITY;
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;
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:
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;
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;
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;
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;
626:
627: -- debug message
628: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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
627: -- debug message
628: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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
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
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 ;
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;
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;
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:
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
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,
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;
754:
755: EXCEPTION
748: -- debug message
749: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
750: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
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;
754:
755: EXCEPTION
756: WHEN L_SCHEMA_NOTFOUND THEN
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
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;
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;
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:
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 */
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:
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;
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;
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:
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',
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:
1117:
1118: -- debug message
1119: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1120: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1121: 'jtf.plsql.jty_tae_index_creation_pvt.create_index.index_creation',
1122: '1Index created with the statement : ' || v_statement);
1123: END IF;
1124:
1125: EXECUTE IMMEDIATE v_statement;
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;
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;
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;
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;
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;
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
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;
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;
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;
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
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;
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;
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;
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;
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
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;
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;
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
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:
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
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:
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
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
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
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 ;
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
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 ;
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;
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;
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:
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
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
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
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;
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;
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:
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
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
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;
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;
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:
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;
1885:
1886: EXCEPTION
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;
1885:
1886: EXCEPTION
1887: WHEN L_SCHEMA_NOTFOUND THEN
1887: WHEN L_SCHEMA_NOTFOUND THEN
1888: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
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
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;
1902:
1903: END ANALYZE_TABLE_INDEX;
1904:
1905:
1906: END jty_tae_index_creation_pvt;