[Home] [Help]
PACKAGE BODY: APPS.AS_UTILITY_PVT
Source
1 PACKAGE BODY AS_UTILITY_PVT as
2 /* $Header: asxvutlb.pls 120.2 2007/03/16 08:17:08 snsarava ship $ */
3 --
4 -- NAME
5 -- AS_UTILITY_PVT
6 --
7 -- HISTORY
8 -- 08/11/99 AWU CREATED(as AS_UTILITY)
9 -- 09/09/99 SOLIN UPDATED(change to JTF_PLSQL_API)
10 -- 04/09/00 SOLIN UPDATED(change back to AS_UTILITY)
11 -- 10/12/00 SOLIN ADD "p_encoded => FND_API.G_FALSE,"
12 -- in FND_MSG_PUB.Count_And_Get of
13 -- Handle_Exceptions()
14 -- 11/12/02 AXAVIER Bug#2659173 Changed the procedure Debug_Message.
15
16 G_PKG_NAME CONSTANT VARCHAR2(30):='AS_UTILITY_PVT';
17 G_FILE_NAME CONSTANT VARCHAR2(12):='asxvutlb.pls';
18
19 pg_file_name VARCHAR2(100) := NULL;
20 pg_path_name VARCHAR2(100) := NULL;
21 pg_fp utl_file.file_type;
22
23 TYPE indexRec IS Record(index_name dba_indexes.index_name%Type
24 ,index_owner dba_indexes.owner%Type
25 ,tbl_name dba_indexes.table_name%Type
26 ,tbl_owner dba_indexes.table_owner%Type
27 ,ts dba_indexes.tablespace_name%Type
28 ,ini_trans dba_indexes.ini_trans%Type
29 ,max_trans dba_indexes.max_trans%Type
30 ,pct_free dba_indexes.pct_free%Type
31 ,freelists dba_indexes.freelists%Type
32 ,int_ext number, next_ext number, min_ext number
33 ,max_ext number, pct number, degree number
34 ,indSql as_conc_request_messages.index_text%Type
35 ,processed boolean := false);
36
37 FUNCTION get_degree_parallelism RETURN NUMBER IS
38 BEGIN
39 RETURN to_number(nvl(fnd_profile.value('AS_DEGREE_PARALLELISM'),4));
40 EXCEPTION WHEN OTHERS THEN
41 RETURN 4;
42 END;
43
44 FUNCTION translate_log_level(p_old_level NUMBER) RETURN NUMBER IS
45 l_level NUMBER;
46 BEGIN
47 if p_old_level = FND_MSG_PUB.G_MSG_LVL_ERROR then
48 l_level := FND_LOG.LEVEL_ERROR;
49 elsif p_old_level = FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR then
50 l_level := FND_LOG.LEVEL_UNEXPECTED;
51 elsif p_old_level = FND_MSG_PUB.G_MSG_LVL_SUCCESS then
52 l_level := FND_LOG.LEVEL_EVENT;
53 else
54 l_level := FND_LOG.LEVEL_STATEMENT;
55 end if;
56
57 RETURN l_level;
58 END;
59
60 -- The following procedure have added for common logging enhancement
61 PROCEDURE SET_LOG(p_module VARCHAR2, p_level NUMBER) is
62 BEGIN
63 if p_level = FND_MSG_PUB.G_MSG_LVL_ERROR then
64 if FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL then
65 fnd_log.message(FND_LOG.LEVEL_ERROR,p_module,TRUE);
66 end if;
67 elsif p_level = FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR then
68 if FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL then
69 fnd_log.message(FND_LOG.LEVEL_UNEXPECTED,p_module,TRUE);
70 end if;
71 elsif p_level = FND_MSG_PUB.G_MSG_LVL_SUCCESS then
72 if FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL then
73 fnd_log.message(FND_LOG.LEVEL_EVENT,p_module,TRUE);
74 end if;
75 else
76 if FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL then
77 fnd_log.message(FND_LOG.LEVEL_STATEMENT,p_module,TRUE);
78 end if;
79 end if;
80 END SET_LOG;
81
82
83 PROCEDURE Start_API(
84 p_api_name IN VARCHAR2,
85 p_pkg_name IN VARCHAR2,
86 p_init_msg_list IN VARCHAR2,
87 p_l_api_version IN NUMBER,
88 p_api_version IN NUMBER,
89 p_api_type IN VARCHAR2,
90 x_return_status OUT NOCOPY VARCHAR2)
91 IS
92 BEGIN
93 NULL;
94 END Start_API;
95
96
97 PROCEDURE End_API(
98 x_msg_count OUT NOCOPY NUMBER,
99 x_msg_data OUT NOCOPY VARCHAR2)
100 IS
101 BEGIN
102 NULL;
103 END End_API;
104
105
106 PROCEDURE Handle_Exceptions(
107 P_API_NAME IN VARCHAR2,
108 P_PKG_NAME IN VARCHAR2,
109 P_EXCEPTION_LEVEL IN NUMBER := FND_API.G_MISS_NUM,
110 P_SQLCODE IN NUMBER DEFAULT NULL,
111 P_SQLERRM IN VARCHAR2 DEFAULT NULL,
112 P_PACKAGE_TYPE IN VARCHAR2,
113 P_ROLLBACK_FLAG IN VARCHAR2 := 'Y',
114 X_MSG_COUNT OUT NOCOPY NUMBER,
115 X_MSG_DATA OUT NOCOPY VARCHAR2,
116 X_RETURN_STATUS OUT NOCOPY VARCHAR2)
117 IS
118 BEGIN
119 Handle_Exceptions('as.plsql.utl.handle_ex',
120 P_API_NAME, P_PKG_NAME, P_EXCEPTION_LEVEL, P_SQLCODE, P_SQLERRM,
121 P_PACKAGE_TYPE, P_ROLLBACK_FLAG, X_MSG_COUNT, X_MSG_DATA,
122 X_RETURN_STATUS);
123 END Handle_Exceptions;
124
125 PROCEDURE Handle_Exceptions(
126 P_MODULE IN VARCHAR2,
127 P_API_NAME IN VARCHAR2,
128 P_PKG_NAME IN VARCHAR2,
129 P_EXCEPTION_LEVEL IN NUMBER := FND_API.G_MISS_NUM,
130 P_SQLCODE IN NUMBER DEFAULT NULL,
131 P_SQLERRM IN VARCHAR2 DEFAULT NULL,
132 P_PACKAGE_TYPE IN VARCHAR2,
133 P_ROLLBACK_FLAG IN VARCHAR2 := 'Y',
134 X_MSG_COUNT OUT NOCOPY NUMBER,
135 X_MSG_DATA OUT NOCOPY VARCHAR2,
136 X_RETURN_STATUS OUT NOCOPY VARCHAR2)
137 IS
138 l_api_name VARCHAR2(30);
139 l_log_level NUMBER;
140 BEGIN
141 l_api_name := UPPER(p_api_name);
142
143 IF p_rollback_flag = 'Y'
144 THEN
145 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name || p_package_type);
146 END IF;
147
148 IF p_exception_level = FND_MSG_PUB.G_MSG_LVL_ERROR
149 THEN
150 x_return_status := FND_API.G_RET_STS_ERROR;
151 FND_MSG_PUB.Count_And_Get(
152 p_encoded => FND_API.G_FALSE,
153 p_count => x_msg_count,
154 p_data => x_msg_data);
155 ELSIF p_exception_level = FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
156 THEN
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158 FND_MSG_PUB.Count_And_Get(
159 p_encoded => FND_API.G_FALSE,
160 p_count => x_msg_count,
161 p_data => x_msg_data);
162 ELSIF p_exception_level = G_EXC_OTHERS
163 THEN
164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
165
166 l_log_level := translate_log_level(p_exception_level);
167 IF l_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
168 FND_MESSAGE.Set_Name('AS', 'Error number ' || to_char(P_SQLCODE));
169 FND_MSG_PUB.Add;
170 FND_MESSAGE.Set_Name('AS', 'Error number ' || to_char(P_SQLCODE));
171 SET_LOG(p_module, p_exception_level);
172 END IF;
173
174 -- ffang 090902, bug 2552070, this line is causing the problem and
175 -- actually it's reduntant. FND_MSG_PUB.Add_Exc_Msg will do the work.
176 -- Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR, P_SQLERRM);
177 -- end ffang 090902, bug 2552070
178
179 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name, p_api_name);
180 FND_MSG_PUB.Count_And_Get(
181 p_encoded => FND_API.G_FALSE,
182 p_count => x_msg_count,
183 p_data => x_msg_data);
184 END IF;
185
186 END Handle_Exceptions;
187
188
189
190
191 FUNCTION get_subOrderBy(p_col_choice IN NUMBER, p_col_name IN VARCHAR2)
192 RETURN VARCHAR2 IS
193 l_col_name varchar2(30);
194 begin
195
196 if (p_col_choice is NULL and p_col_name is NOT NULL)
197 or (p_col_choice is NOT NULL and p_col_name is NULL)
198 then
199 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
200 then
201 fnd_message.set_name('AS', 'API_MISSING_ORDERBY_ELEMENT');
202 fnd_msg_pub.add;
203 fnd_message.set_name('AS', 'API_MISSING_ORDERBY_ELEMENT');
204 SET_LOG('as.plsql.utl.get_subOrderBy', fnd_msg_pub.g_msg_lvl_error);
205 end if;
206 raise fnd_api.g_exc_error;
207 end if;
208
209
210 if (nls_upper(p_col_name) = 'CUSTOMER_NAME')
211 then
212 l_col_name := ' nls_upper' ||'(' ||p_col_name|| ')';
213 else
214 l_col_name := p_col_name;
215 end if;
216
217 if (mod(p_col_choice, 10) = 1)
218 then
219 return(l_col_name || ' ASC, ');
220 elsif (mod(p_col_choice, 10) = 0)
221 then
222 return(l_col_name || ' DESC, ');
223 else
224 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
225 then
226 fnd_message.set_name('AS', 'API_INVALID_ORDERBY_CHOICE');
227 fnd_message.set_token('PARAM',p_col_choice, false);
228 fnd_msg_pub.add;
229 fnd_message.set_name('AS', 'API_INVALID_ORDERBY_CHOICE');
230 fnd_message.set_token('PARAM',p_col_choice, false);
231 set_log('as.plsql.utl.get_subOrderBy', fnd_msg_pub.g_msg_lvl_error);
232 end if;
233 raise fnd_api.g_exc_error;
234 return '';
235 end if;
236 end;
237
238 PROCEDURE Translate_OrderBy
239 ( p_api_version_number IN NUMBER,
240 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
241 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
242 p_order_by_tbl IN UTIL_ORDER_BY_TBL_TYPE,
243 x_order_by_clause OUT NOCOPY VARCHAR2,
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2
247 ) IS
248
249 TYPE OrderByTabTyp is TABLE of VARCHAR2(80) INDEX BY BINARY_INTEGER;
250 l_sortedOrderBy_tbl OrderByTabTyp;
251 i BINARY_INTEGER := 1;
252 j BINARY_INTEGER := 1;
253 l_order_by_clause VARCHAR2(2000) := NULL;
254 l_api_name CONSTANT VARCHAR2(30) := 'Translate_OrderBy';
255 l_api_version_number CONSTANT NUMBER := 1.0;
256 begin
257 -- Standard call to check for call compatibility.
258 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
259 p_api_version_number,
260 l_api_name,
261 G_PKG_NAME)
262 THEN
263 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
264 THEN
265 FND_MESSAGE.Set_Name('AS', 'API_UNEXP_ERROR_IN_PROCESSING');
266 FND_MESSAGE.Set_Token('ROW', 'TRANSLATE_ORDERBY', TRUE);
267 FND_MSG_PUB.ADD;
268 FND_MESSAGE.Set_Name('AS', 'API_UNEXP_ERROR_IN_PROCESSING');
269 FND_MESSAGE.Set_Token('ROW', 'TRANSLATE_ORDERBY', TRUE);
270 SET_LOG('as.plsql.utl.Translate_OrderBy', fnd_msg_pub.g_msg_lvl_error);
271 END IF;
272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
273 END IF;
274
275 -- Initialize message list if p_init_msg_list is set to TRUE.
276 IF FND_API.to_Boolean( p_init_msg_list )
277 THEN
278 FND_MSG_PUB.initialize;
279 END IF;
280
281 -- Initialize API return status to success
282 --
283 x_return_status := FND_API.G_RET_STS_SUCCESS;
284
285 --
286 -- API body
287 --
288
289 -- Validate Environment
290
291 IF FND_GLOBAL.User_Id IS NULL
292 THEN
293 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
294 THEN
295 FND_MESSAGE.Set_Name('AS', 'UT_CANNOT_GET_PROFILE_VALUE');
296 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
297 FND_MSG_PUB.ADD;
298 FND_MESSAGE.Set_Name('AS', 'UT_CANNOT_GET_PROFILE_VALUE');
299 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
300 SET_LOG('as.plsql.utl.Translate_OrderBy', fnd_msg_pub.g_msg_lvl_error);
301 END IF;
302 END IF;
303
304 -- initialize the table to ''.
305 for i in 1..p_order_by_tbl.count loop
306 l_sortedOrderBy_tbl(i) := '';
307 end loop;
308
309 -- We allow the choice seqence order such as 41, 20, 11, ...
310 -- So, we need to sort it first(put them into a table),
311 -- then loop through the whole table.
312
313 for j in 1..p_order_by_tbl.count loop
314 if (p_order_by_tbl(j).col_choice is NOT NULL)
315 then
316 l_sortedOrderBy_tbl(floor(p_order_by_tbl(j).col_choice/10)) :=
317 get_subOrderBy(p_order_by_tbl(j).col_choice,
318 p_order_by_tbl(j).col_name);
319 end if;
320 end loop;
321
322 for i in 1..p_order_by_tbl.count loop
323 l_order_by_clause := l_order_by_clause || l_sortedOrderBy_tbl(i);
324 end loop;
325 l_order_by_clause := rtrim(l_order_by_clause); -- trim ''
326 l_order_by_clause := rtrim(l_order_by_clause, ','); -- trim last ,
327 x_order_by_clause := l_order_by_clause;
328
329 EXCEPTION
330
331 WHEN FND_API.G_EXC_ERROR THEN
332
333 x_return_status := FND_API.G_RET_STS_ERROR ;
334
335 FND_MSG_PUB.Count_And_Get
336 ( p_count => x_msg_count,
337 p_data => x_msg_data
338 );
339
340
341 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
342
343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
344
345 FND_MSG_PUB.Count_And_Get
346 ( p_count => x_msg_count,
347 p_data => x_msg_data
348 );
349
350
354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
351 WHEN OTHERS THEN
352
353
355
356 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
357 THEN
358 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
359 END IF;
360
361 FND_MSG_PUB.Count_And_Get
362 ( p_count => x_msg_count,
363 p_data => x_msg_data
364 );
365
366 end Translate_OrderBy;
367
368
369 PROCEDURE Get_Messages (
370 p_message_count IN NUMBER,
371 x_msgs OUT NOCOPY VARCHAR2)
372 IS
373 l_msg_list VARCHAR2(5000) := '
374 ';
375 l_temp_msg VARCHAR2(2000);
376 l_appl_short_name VARCHAR2(50) ;
377 l_message_name VARCHAR2(30) ;
378
379 l_id NUMBER;
380 l_message_num NUMBER;
381
382 l_msg_count NUMBER;
383 l_msg_data VARCHAR2(2000);
384
385 Cursor Get_Appl_Id (x_short_name VARCHAR2) IS
386 SELECT application_id
387 FROM fnd_application_vl
388 WHERE application_short_name = x_short_name;
389
390 Cursor Get_Message_Num (x_msg VARCHAR2, x_id NUMBER, x_lang_id NUMBER) IS
391 SELECT msg.message_number
392 FROM fnd_new_messages msg, fnd_languages_vl lng
393 WHERE msg.message_name = x_msg
394 and msg.application_id = x_id
395 and lng.LANGUAGE_CODE = msg.language_code
396 and lng.language_id = x_lang_id;
397 BEGIN
398 FOR l_count in 1..p_message_count LOOP
399
400 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_true);
401 fnd_message.parse_encoded(l_temp_msg, l_appl_short_name, l_message_name);
402 OPEN Get_Appl_Id (l_appl_short_name);
403 FETCH Get_Appl_Id into l_id;
404 CLOSE Get_Appl_Id;
405
406 l_message_num := NULL;
407 IF l_id is not NULL
408 THEN
409 OPEN Get_Message_Num (l_message_name, l_id,
410 to_number(NVL(FND_PROFILE.Value('LANGUAGE'), '0')));
411 FETCH Get_Message_Num into l_message_num;
412 CLOSE Get_Message_Num;
413 END IF;
414
415 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_previous, fnd_api.g_true);
416
417 IF NVL(l_message_num, 0) <> 0
418 THEN
419 l_temp_msg := 'APP-' || to_char(l_message_num) || ': ';
420 ELSE
421 l_temp_msg := NULL;
422 END IF;
423
424 IF l_count = 1
425 THEN
426 l_msg_list := l_msg_list || l_temp_msg ||
427 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
428 ELSE
429 l_msg_list := l_msg_list || l_temp_msg ||
430 fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
431 END IF;
432
433 l_msg_list := l_msg_list || '
434 ';
435
436 END LOOP;
437
438 x_msgs := l_msg_list;
439
440 END Get_Messages;
441
442
443 PROCEDURE Debug_Message(
444 p_msg_level IN NUMBER,
445 -- p_app_name IN VARCHAR2 := 'AS',
446 p_msg IN VARCHAR2)
447 IS
448 BEGIN
449 Debug_Message('as.plsql.utl.debug_message', p_msg_level, p_msg);
450 END Debug_Message;
451
452 PROCEDURE Debug_Message(
453 p_module IN VARCHAR2,
454 p_msg_level IN NUMBER,
455 -- p_app_name IN VARCHAR2 := 'AS',
456 p_msg IN VARCHAR2)
457 IS
458 l_log_level NUMBER;
459 l_length NUMBER;
460 l_start NUMBER := 1;
461 l_substring VARCHAR2(50);
462 BEGIN
463 --IF FND_MSG_PUB.Check_Msg_Level(p_msg_level)
464 --THEN
465 /*
466 l_length := length(p_msg);
467
468 -- FND_MESSAGE doesn't allow message name to be over 30 chars
469 -- chop message name if length > 30
470 WHILE l_length > 30 LOOP
471 l_substring := substr(p_msg, l_start, 30);
472
473 FND_MESSAGE.Set_Name('AS', l_substring);
474 -- FND_MESSAGE.Set_Name(p_app_name, l_substring);
475 l_start := l_start + 30;
476 l_length := l_length - 30;
477 FND_MSG_PUB.Add;
478 FND_MESSAGE.Set_Name('AS', l_substring);
479 SET_LOG(p_msg_level);
480 END LOOP;
481
482 l_substring := substr(p_msg, l_start);
483 FND_MESSAGE.Set_Name('AS', l_substring);
484 -- dbms_output.put_line('l_substring: ' || l_substring);
485 -- FND_MESSAGE.Set_Name(p_app_name, p_msg);
486 SET_LOG(p_msg_level);
487 FND_MESSAGE.Set_Name('AS', l_substring);
488 FND_MSG_PUB.Add;
489
490 */
491 l_log_level := translate_log_level(p_msg_level);
492 IF l_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
493 l_length := length(p_msg);
494
495 -- FND_MESSAGE doesn't allow application name to be over 30 chars
496 -- chop message name if length > 30
497 IF l_length > 30
498 THEN
502 ELSE
499 l_substring := substr(p_msg, l_start, 30);
500 FND_MESSAGE.Set_Name('AS', l_substring);
501 -- FND_MESSAGE.Set_Name(l_substring, '');
503 FND_MESSAGE.Set_Name('AS', p_msg);
504 -- FND_MESSAGE.Set_Name(p_msg, '');
505 END IF;
506 FND_MSG_PUB.Add;
507 IF l_length > 30
508 THEN
509 l_substring := substr(p_msg, l_start, 30);
510 FND_MESSAGE.Set_Name('AS', l_substring);
511 ELSE
512 FND_MESSAGE.Set_Name('AS', p_msg);
513 END IF;
514 SET_LOG(p_module, p_msg_level);
515 END IF;
516 --END IF;
517 END Debug_Message;
518
519
520 PROCEDURE Set_Message(
521 p_msg_level IN NUMBER,
522 p_msg_name IN VARCHAR2
523 )
524 IS
525 BEGIN
526 Set_Message('as.plsql.utl.set_message', p_msg_level, p_msg_name);
527 END Set_Message;
528
529 PROCEDURE Set_Message(
530 p_module IN VARCHAR2,
531 p_msg_level IN NUMBER,
532 -- p_app_name IN VARCHAR2,
533 p_msg_name IN VARCHAR2
534 )
535 IS
536 BEGIN
537 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level)
538 THEN
539 FND_MESSAGE.Set_Name('AS', p_msg_name);
540 FND_MSG_PUB.Add;
541 FND_MESSAGE.Set_Name('AS', p_msg_name);
542 SET_LOG(p_module, p_msg_level);
543 END IF;
544 END Set_Message;
545
546
547 PROCEDURE Set_Message(
548 p_msg_level IN NUMBER,
549 p_msg_name IN VARCHAR2,
550 p_token1 IN VARCHAR2,
551 p_token1_value IN VARCHAR2
552 )
553 IS
554 BEGIN
555 Set_Message(
556 'as.plsql.utl.set_message', p_msg_level, p_msg_name,
557 p_token1, p_token1_value);
558 END Set_Message;
559
560 PROCEDURE Set_Message(
561 p_module IN VARCHAR2,
562 p_msg_level IN NUMBER,
563 -- p_app_name IN VARCHAR2,
564 p_msg_name IN VARCHAR2,
565 p_token1 IN VARCHAR2,
566 p_token1_value IN VARCHAR2
567 )
568 IS
569 BEGIN
570 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level)
571 THEN
572 FND_MESSAGE.Set_Name('AS', p_msg_name);
573 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
574 FND_MSG_PUB.Add;
575 FND_MESSAGE.Set_Name('AS', p_msg_name);
576 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
577 SET_LOG(p_module, p_msg_level);
578 END IF;
579 END Set_Message;
580
581 PROCEDURE Set_Message(
582 p_msg_level IN NUMBER,
583 p_msg_name IN VARCHAR2,
584 p_token1 IN VARCHAR2,
585 p_token1_value IN VARCHAR2,
586 p_token2 IN VARCHAR2,
587 p_token2_value IN VARCHAR2
588 )
589 IS
590 BEGIN
591 Set_Message(
592 'as.plsql.utl.set_message', p_msg_level, p_msg_name,
593 p_token1, p_token1_value, p_token2, p_token2_value);
594 END Set_Message;
595
596 PROCEDURE Set_Message(
597 p_module IN VARCHAR2,
598 p_msg_level IN NUMBER,
599 -- p_app_name IN VARCHAR2,
600 p_msg_name IN VARCHAR2,
601 p_token1 IN VARCHAR2,
602 p_token1_value IN VARCHAR2,
603 p_token2 IN VARCHAR2,
604 p_token2_value IN VARCHAR2
605 )
606 IS
607 BEGIN
608 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level)
609 THEN
610 FND_MESSAGE.Set_Name('AS', p_msg_name);
611 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
612 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
613 FND_MSG_PUB.Add;
614 FND_MESSAGE.Set_Name('AS', p_msg_name);
615 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
616 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
617 SET_LOG(p_module, p_msg_level);
618 END IF;
619 END Set_Message;
620
621 PROCEDURE Set_Message(
622 p_msg_level IN NUMBER,
623 p_msg_name IN VARCHAR2,
624 p_token1 IN VARCHAR2,
625 p_token1_value IN VARCHAR2,
626 p_token2 IN VARCHAR2,
627 p_token2_value IN VARCHAR2,
628 p_token3 IN VARCHAR2,
629 p_token3_value IN VARCHAR2
630 )
631 IS
632 BEGIN
633 Set_Message(
634 'as.plsql.utl.set_message', p_msg_level, p_msg_name,
635 p_token1, p_token1_value, p_token2, p_token2_value,
636 p_token3, p_token3_value);
637 END Set_Message;
638
639 PROCEDURE Set_Message(
640 p_module IN VARCHAR2,
641 p_msg_level IN NUMBER,
642 -- p_app_name IN VARCHAR2,
643 p_msg_name IN VARCHAR2,
644 p_token1 IN VARCHAR2,
645 p_token1_value IN VARCHAR2,
646 p_token2 IN VARCHAR2,
647 p_token2_value IN VARCHAR2,
648 p_token3 IN VARCHAR2,
649 p_token3_value IN VARCHAR2
650 )
651 IS
652 BEGIN
656 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
653 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level)
654 THEN
655 FND_MESSAGE.Set_Name('AS', p_msg_name);
657 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
658 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
659 FND_MSG_PUB.Add;
660 FND_MESSAGE.Set_Name('AS', p_msg_name);
661 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
662 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
663 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
664 SET_LOG(p_module, p_msg_level);
665 END IF;
666 END Set_Message;
667
668
669 PROCEDURE Set_Message(
670 p_msg_level IN NUMBER,
671 -- p_app_name IN VARCHAR2,
672 p_msg_name IN VARCHAR2,
673 p_token1 IN VARCHAR2,
674 p_token1_value IN VARCHAR2,
675 p_token2 IN VARCHAR2,
676 p_token2_value IN VARCHAR2,
677 p_token3 IN VARCHAR2,
678 p_token3_value IN VARCHAR2,
679 p_token4 IN VARCHAR2,
680 p_token4_value IN VARCHAR2,
681 p_token5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
682 p_token5_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
683 p_token6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
684 p_token6_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
685 p_token7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
686 p_token7_value IN VARCHAR2 := FND_API.G_MISS_CHAR
687 )
688 IS
689 BEGIN
690 Set_Message(
691 'as.plsql.utl.set_message', p_msg_level, p_msg_name,
692 p_token1, p_token1_value, p_token2, p_token2_value,
693 p_token3, p_token3_value, p_token4, p_token4_value,
694 p_token5, p_token5_value, p_token6, p_token6_value,
695 p_token7, p_token7_value);
696 END Set_Message;
697
698 PROCEDURE Set_Message(
699 p_module IN VARCHAR2,
700 p_msg_level IN NUMBER,
701 -- p_app_name IN VARCHAR2,
702 p_msg_name IN VARCHAR2,
703 p_token1 IN VARCHAR2,
704 p_token1_value IN VARCHAR2,
705 p_token2 IN VARCHAR2,
706 p_token2_value IN VARCHAR2,
707 p_token3 IN VARCHAR2,
708 p_token3_value IN VARCHAR2,
709 p_token4 IN VARCHAR2,
710 p_token4_value IN VARCHAR2,
711 p_token5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
712 p_token5_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
713 p_token6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
714 p_token6_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
715 p_token7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
716 p_token7_value IN VARCHAR2 := FND_API.G_MISS_CHAR
717 )
718 IS
719 BEGIN
720 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level)
721 THEN
722 FND_MESSAGE.Set_Name('AS', p_msg_name);
723 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
724 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
725 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
726 FND_MESSAGE.Set_Token(p_token4, p_token4_value);
727 FND_MESSAGE.Set_Token(p_token5, p_token5_value);
728 FND_MESSAGE.Set_Token(p_token6, p_token6_value);
729 FND_MESSAGE.Set_Token(p_token7, p_token7_value);
730 FND_MSG_PUB.Add;
731 FND_MESSAGE.Set_Name('AS', p_msg_name);
732 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
733 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
734 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
735 FND_MESSAGE.Set_Token(p_token4, p_token4_value);
736 FND_MESSAGE.Set_Token(p_token5, p_token5_value);
737 FND_MESSAGE.Set_Token(p_token6, p_token6_value);
738 FND_MESSAGE.Set_Token(p_token7, p_token7_value);
739 SET_LOG(p_module, p_msg_level);
740 END IF;
741 END Set_Message;
742
743 PROCEDURE Gen_Flexfield_Where(
744 p_flex_where_tbl_type IN AS_UTILITY_PVT.flex_where_tbl_type,
745 x_flex_where_clause OUT NOCOPY VARCHAR2) IS
746 l_flex_where_cl VARCHAR2(2000) := NULL;
747 BEGIN
748 Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
749 'AS_UTILITY_PVT Generate Flexfield Where: begin');
750
751 FOR i IN 1..p_flex_where_tbl_type.count LOOP
752 IF (p_flex_where_tbl_type(i).value IS NOT NULL
753 AND p_flex_where_tbl_type(i).value <> FND_API.G_MISS_CHAR) THEN
754 l_flex_where_cl := l_flex_where_cl||' AND '||p_flex_where_tbl_type(i).name
755 || ' = :p_ofso_flex_var'||i;
756 END IF;
757 END LOOP;
758 x_flex_where_clause := l_flex_where_cl;
759
760 Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
761 'AS_UTILITY_PVT Generate Flexfield Where: end');
762 END;
763
764 PROCEDURE Bind_Flexfield_Where(
765 p_cursor_id IN NUMBER,
766 p_flex_where_tbl_type IN AS_UTILITY_PVT.flex_where_tbl_type) IS
767 BEGIN
768 Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
769 'AS_UTILITY_PVT Bind Flexfield Where: begin');
770
771 FOR i IN 1..p_flex_where_tbl_type.count LOOP
772 IF (p_flex_where_tbl_type(i).value IS NOT NULL
773 AND p_flex_where_tbl_type(i).value <> FND_API.G_MISS_CHAR) THEN
774 DBMS_SQL.Bind_Variable(p_cursor_id, ':p_ofso_flex_var'|| i,
775 p_flex_where_tbl_type(i).value);
776 END IF;
777 END LOOP;
778
779 Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
780 'AS_UTILITY_PVT Bind Flexfield Where: end');
781 END;
782
783
784 PROCEDURE file_debug(line in varchar2) IS
785
786 BEGIN
787 if (pg_file_name is not null) then
788
789 -- dbms_output.put_line('pg_file_name ' || pg_file_name);
790 utl_file.put_line(pg_fp, line);
791 utl_file.fflush(pg_fp);
792 end if;
793 END file_debug;
794
795 PROCEDURE enable_file_debug(path_name in varchar2,
796 file_name in varchar2) IS
797
798 BEGIN
799
800 if (pg_file_name is null) then
801 pg_fp := utl_file.fopen(path_name, file_name, 'a');
802 pg_file_name := file_name;
803 pg_path_name := path_name;
804 end if;
805
806 EXCEPTION
807 when utl_file.invalid_path then
808 app_exception.raise_exception;
809 when utl_file.invalid_mode then
810 app_exception.raise_exception;
811
812 END;
813
814 PROCEDURE disable_file_debug is
815 BEGIN
816 if (pg_file_name is not null) THEN
817 utl_file.fclose(pg_fp);
818 end if;
819 END;
820
821 PROCEDURE static_sql(p_indRec IN OUT NOCOPY indexRec) IS
822 BEGIN
823 p_indRec.indSql := p_indRec.indSql||') '||'PCTFREE '||p_indRec.pct_free||' INITRANS '||
824 p_indRec.ini_trans||' MAXTRANS '||p_indRec.max_trans||
825 ' STORAGE (INITIAL '||p_indRec.int_ext||'M NEXT '||p_indRec.next_ext ||'M MINEXTENTS '||
826 p_indRec.min_ext||' MAXEXTENTS '||p_indRec.max_ext ||' PCTINCREASE '||p_indRec.pct||
827 ' FREELISTS '||p_indRec.freelists||') PARALLEL '||get_degree_parallelism||
828 ' TABLESPACE '||p_indRec.ts;
829 Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_UTILITY_PVT Processing index: '||p_indRec.index_name );
830
831 UPDATE as_conc_request_messages
832 SET index_text = p_indRec.indSql
833 WHERE index_name = p_indRec.index_name
834 AND index_owner = p_indRec.index_owner;
835
836 IF (sql%rowcount <= 0) THEN
837 INSERT INTO as_conc_request_messages
838 (conc_request_message_id, creation_date, created_by, table_name, table_owner, index_name, index_owner, index_text)
839 VALUES (AS_CONC_REQUEST_MESSAGES_S.nextval, sysdate, nvl(fnd_global.user_id,-1), p_indRec.tbl_name, p_indRec.tbl_owner, p_indRec.index_name,p_indRec.index_owner, p_indRec.indSql);
840 END IF;
841
842 END static_sql;
843
844 PROCEDURE in_loop (p_indRec IN OUT NOCOPY indexRec,
845 p_indname VARCHAR2,
846 p_owner VARCHAR2,
847 p_uniq VARCHAR2,
848 p_colname VARCHAR2,
849 p_cp VARCHAR2) IS
850 BEGIN
851 IF (p_indRec.index_name <> p_indname) THEN
852 static_sql(p_indRec);
853 END IF;
854
855 IF p_cp = 1 THEN
856 p_indRec.indSql := 'CREATE '||p_uniq||'INDEX '||p_owner||'.'||p_indname||' ON '||p_indRec.tbl_owner||'.' ||p_indRec.tbl_name|| '('||p_colname;
857 else
858 p_indRec.indSql := p_indRec.indSql||', '||p_colname;
859 END IF;
860
861 END in_loop;
862
863 PROCEDURE capture_index_definitions(errbuf OUT NOCOPY VARCHAR2,
864 retcode OUT NOCOPY VARCHAR2,
865 p_table_name VARCHAR2,
866 p_table_owner VARCHAR2) IS
867 l_indRec indexRec;
868
869 CURSOR normal_ind (p_tbl_name varchar2, p_tbl_owner varchar2) IS
870 SELECT col.column_position cp, decode(ind.uniqueness,'UNIQUE','UNIQUE ',null) uniq,
871 ind.owner, col.index_name indname, col.column_name colname,
872 ceil(ind.initial_extent/1048576) intext, ceil(nvl(ind.next_extent/1048576,1)) nextext,
873 ind.min_extents minext, ind.max_extents maxext, nvl(ind.pct_increase,0) pct,
874 nvl(ind.ini_trans,1) ini_trans, nvl(ind.max_trans,255) max_trans,
875 nvl(ind.pct_free,20) pct_free, nvl(ind.freelists,1) freelists, ind.tablespace_name ts, ind.degree
876 FROM dba_ind_columns col, dba_indexes ind
877 WHERE ind.table_owner = p_tbl_owner
878 AND ind.table_name = p_tbl_name
879 AND ind.index_type = 'NORMAL'
880 AND col.index_owner = ind.owner
881 AND col.index_name = ind.index_name
882 AND ind.status = 'VALID'
883 ORDER BY indname,cp;
884
885 CURSOR func_ind (p_tbl_name varchar2, p_tbl_owner varchar2) IS
886 SELECT exp.column_position cp, decode(ind.uniqueness,'UNIQUE','UNIQUE ',null) uniq,
887 ind.owner, exp.index_name indname, exp.column_expression colname,
888 ceil(ind.initial_extent/1048576) intext, ceil(nvl(ind.next_extent/1048576,1)) nextext,
889 ind.min_extents minext, ind.max_extents maxext, nvl(ind.pct_increase,0) pct,
890 nvl(ind.ini_trans,1) ini_trans, nvl(ind.max_trans,255) max_trans,
891 nvl(ind.pct_free,20) pct_free, nvl(ind.freelists,1) freelists, ind.tablespace_name ts, ind.degree
892 FROM dba_ind_expressions exp, dba_indexes ind
893 WHERE ind.table_owner = p_tbl_owner
894 AND ind.table_name = p_tbl_name
895 AND ind.index_type = 'FUNCTION-BASED NORMAL'
899 ORDER BY indname,cp;
896 AND exp.index_owner = ind.owner
897 AND exp.index_name = ind.index_name
898 AND ind.status = 'VALID'
900
901 BEGIN
902
903
904 l_indRec.tbl_name := p_table_name;
905 l_indRec.tbl_owner := p_table_owner;
906
907 /* Normal index */
908 FOR I in normal_ind(p_table_name, p_table_owner) LOOP
909 l_indRec.processed := TRUE;
910 in_loop (l_indRec, I.indname,I.owner,I.uniq,I.colname, I.cp);
911 l_indRec.index_name := I.indname; l_indRec.index_owner := I.owner;
912 l_indRec.int_ext := I.intext; l_indRec.next_ext := I.nextext; l_indRec.min_ext := I.minext;
913 l_indRec.max_ext := I.maxext; l_indRec.pct := I.pct; l_indRec.degree := get_degree_parallelism;
914 l_indRec.ts := I.ts; l_indRec.ini_trans := I.ini_trans; l_indRec.max_trans := I.max_trans;
915 l_indRec.pct_free := I.pct_free; l_indRec.freelists := I.freelists;
916 END LOOP;
917
918 /* Function based index */
919 FOR I in func_ind(p_table_name, p_table_owner) LOOP
920 l_indRec.processed := TRUE;
921 in_loop (l_indRec, I.indname,I.owner,I.uniq,I.colname, I.cp);
922 l_indRec.index_name := I.indname; l_indRec.index_owner := I.owner;
923 l_indRec.int_ext := I.intext; l_indRec.next_ext := I.nextext; l_indRec.min_ext := I.minext;
924 l_indRec.max_ext := I.maxext; l_indRec.pct := I.pct; l_indRec.degree := get_degree_parallelism;
925 l_indRec.ts := I.ts; l_indRec.ini_trans := I.ini_trans; l_indRec.max_trans := I.max_trans;
926 l_indRec.pct_free := I.pct_free; l_indRec.freelists := I.freelists;
927 END LOOP;
928
929 IF (l_indRec.processed) THEN static_sql(l_indRec); END IF;
930
931 retcode := 0;
932 EXCEPTION WHEN OTHERS THEN
933 errbuf := 'Error in capute_index_definitions processing table '||p_table_name||': '||substr(sqlerrm,1,255);
934 retcode := -1;
935 END capture_index_definitions;
936
937 PROCEDURE execute_ind(errbuf OUT NOCOPY VARCHAR2,
938 retcode OUT NOCOPY VARCHAR2,
939 p_mode VARCHAR2,
940 p_table_name VARCHAR2,
941 p_table_owner VARCHAR2) IS
942
943 CURSOR build_index (p_tbl_name varchar2, p_tbl_owner varchar2) IS
944 SELECT index_name indname, index_owner owner, index_text indSql
945 FROM as_conc_request_messages o
946 WHERE table_name = p_tbl_name
947 AND table_owner = p_tbl_owner
948 AND NOT EXISTS (SELECT 'e' FROM dba_indexes i
949 WHERE i.index_name = o.index_name
950 AND i.owner = o.index_owner);
951
952 CURSOR drop_index (p_tbl_name varchar2, p_tbl_owner varchar2) IS
953 SELECT index_name indname, index_owner owner, index_text indSql
954 FROM as_conc_request_messages o
955 WHERE table_name = p_tbl_name
956 AND table_owner = p_tbl_owner
957 AND EXISTS (SELECT 'e' FROM dba_indexes i
958 WHERE i.index_name = o.index_name
959 AND i.owner = o.index_owner);
960
961 l_indRec indexRec;
962 BEGIN
963 IF (p_mode <> 'BUILD' AND p_mode <> 'DROP') then
964 errbuf := 'Pls. use proper mode and Re-Try.';
965 retcode := -1;
966 Return;
967 END IF;
968 IF (p_mode = 'BUILD') THEN
969 FOR I in build_index (p_table_name, p_table_owner) LOOP
970 Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'execute_ind '||'Processing index:' ||I.indname||' with mode: '||p_mode);
971 EXECUTE IMMEDIATE I.indSql||CHR(0);
972 EXECUTE IMMEDIATE 'ALTER INDEX '||I.owner||'.'||I.indname||' LOGGING NOPARALLEL';
973 END LOOP;
974 --Code commented for performance bug#5802537-- by lester
975 --dbms_stats.gather_table_stats(p_table_owner,p_table_name, estimate_percent=>25, degree=>get_degree_parallelism, granularity=>'GLOBAL', cascade=>TRUE);
976 DELETE FROM as_conc_request_messages where table_name = p_table_name and table_owner = p_table_owner;
977 END IF;
978 IF (p_mode = 'DROP') THEN
979 FOR I in drop_index (p_table_name, p_table_owner) LOOP
980 Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'execute_ind '||'Processing index:' ||I.indname||' with mode: '||p_mode);
981 EXECUTE IMMEDIATE 'DROP INDEX '||I.owner||'.'||I.indname;
982 END LOOP;
983 END IF;
984 retcode := 0;
985 EXCEPTION WHEN OTHERS THEN
986 errbuf := 'Error in execute_ind processing table '||p_table_name||'with mode '||p_mode||': '||substr(sqlerrm,1,255);
987 retcode := -1;
988 END execute_ind;
989
990 END AS_UTILITY_PVT;