[Home] [Help]
PACKAGE BODY: APPS.FND_LOG_ADMIN
Source
1 package body fnd_log_admin as
2 /* $Header: AFUTLGAB.pls 120.1.12000000.2 2007/07/27 13:42:23 rlandows ship $ */
3
4 C_PKG_NAME CONSTANT VARCHAR2(30) := 'FND_FUNCTION';
5 C_LOG_HEAD CONSTANT VARCHAR2(30) := 'fnd.plsql.FND_LOG_ADMIN.';
6
7
8 /******************************************************************************/
9 /***Constants for Changes due to system Log ***********************************/
10 TYPE GenCursor IS REF CURSOR;
11 TYPE TransxCurTyp IS REF CURSOR RETURN FND_LOG_TRANSACTION_CONTEXT%ROWTYPE;
12 TYPE UExcIdListTyp IS TABLE OF FND_LOG_UNIQUE_EXCEPTIONS.UNIQUE_EXCEPTION_ID%TYPE INDEX BY BINARY_INTEGER;
13 TYPE LogSeqListTyp IS TABLE OF FND_LOG_EXCEPTIONS.LOG_SEQUENCE%TYPE INDEX BY BINARY_INTEGER;
14 TYPE TrnCtxIdListTyp IS TABLE OF FND_LOG_TRANSACTION_CONTEXT.TRANSACTION_CONTEXT_ID%TYPE INDEX BY BINARY_INTEGER;
15 TYPE VARCAHRListTyp IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
16 TYPE VARCAHRSmallListTyp IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
17
18
19 C_SUCCESS CONSTANT NUMBER := 0;
20 C_WARNING CONSTANT NUMBER := 1;
21 C_ERROR CONSTANT NUMBER := 2;
22
23 C_BE_PURGE_INTERVAL CONSTANT NUMBER := 5;
24 COUNT_COMMIT CONSTANT NUMBER := 500;
25 DELETE_BLOCK CONSTANT NUMBER := 1000;
26 MAX_LIST_COUNT CONSTANT NUMBER := 1000;
27
28 s_rows_deleted_flm NUMBER := 0; --FND_LOG_MESSAGES;
29 s_rows_deleted_fen NUMBER := 0; --FND_EXCEPTION_NOTES
30 s_rows_deleted_fle NUMBER := 0; --FND_LOG_EXCEPTIONS
31 s_rows_deleted_flmt NUMBER := 0; --FND_LOG_METRICS
32 s_rows_deleted_flue NUMBER := 0; --FND_LOG_UNIQUE_EXCEPTIONS
33 s_rows_deleted_fobsn NUMBER := 0; --FND_OAM_BIZEX_SENT_NOTIF
34 s_rows_deleted_fltc NUMBER := 0; --FND_LOG_TRANSACTION_CONTEXT
35
36
37 ---Constants used for dynamic SQL
38 --List
39 C_TrnCtxIdListTyp CONSTANT NUMBER := 0;
40 C_UExcIdListTyp CONSTANT NUMBER := 1;
41 C_LogSeqListTyp CONSTANT NUMBER := 2;
42
43 --Tr Type
44 C_TR_REQUEST_TYPE CONSTANT NUMBER := 0;
45 C_TR_SERVICE_TYPE CONSTANT NUMBER := 1;
46 C_TR_FORM_TYPE CONSTANT NUMBER := 2;
47 C_TR_ICX_TYPE CONSTANT NUMBER := 3;
48 C_TR_UNKNOWN_TYPE CONSTANT NUMBER := 4;
49
50 --Criteria
51 C_PURGE_CRITERIA_ALL NUMBER := 0;
52 C_PURGE_CRITERIA_START_DATE NUMBER := 1;
53 C_PURGE_CRITERIA_END_DATE NUMBER := 2;
54 C_PURGE_CRITERIA_RANGE_DATE NUMBER := 3;
55
56 C_PURGE_CRITERIA_USER NUMBER := 4;
57 C_PURGE_CRITERIA_SESSION NUMBER := 5;
58 C_PURGE_CRITERIA_USER_SESSION NUMBER := 6;
59
60 C_PURGE_CRITERIA_MODULE NUMBER := 7;
61 C_PURGE_CRITERIA_LEVEL NUMBER := 8;
62
63 C_DEBUG BOOLEAN := TRUE;
64
65
66 ---Start pre 1150 methods ------------------------------------------------------
67
68
69 --------------------------------------------------------------------------------
70 --Functions pre 1159 release. These are useful for those customers who will
71 --migrate to 1159 and have some log data before migrations.
72
73 function delete_by_user_pre1159(
74 X_USER_ID IN VARCHAR2 ) return NUMBER is
75 rowcount number := 0;
76 temp_rowcount number := 0;
77 begin
78 loop
79 begin
80 delete from fnd_log_messages
81 where rownum <= 1000
82 and USER_ID = X_USER_ID
83 and TRANSACTION_CONTEXT_ID is null;
84 temp_rowcount := sql%rowcount;
85 commit;
86 rowcount := rowcount + temp_rowcount;
87 exit when (temp_rowcount = 0);
88 exception
89 when no_data_found then
90 null; /* Should never happen */
91 when others then
92 if ((sqlcode = 60) or (sqlcode = 4020)) then
93 null; /* Ignore rows that are deadlocked */
94 else
95 raise;
96 end if;
97 end;
98
99 end loop;
100 return rowcount;
101 end;
102
103
104
105 function delete_by_session_pre1159(
106 X_SESSION_ID IN VARCHAR2 ) return NUMBER is
107 rowcount number := 0;
108 temp_rowcount number := 0;
109 begin
110 loop
111 begin
112 delete from fnd_log_messages
113 where rownum <= 1000
114 and SESSION_ID = X_SESSION_ID
115 and TRANSACTION_CONTEXT_ID is null;
116 temp_rowcount := sql%rowcount;
117 commit;
118 rowcount := rowcount + temp_rowcount;
119 exit when (temp_rowcount = 0);
120 exception
121 when no_data_found then
122 null; /* Should never happen */
123 when others then
124 if ((sqlcode = 60) or (sqlcode = 4020)) then
125 null; /* Ignore rows that are deadlocked */
126 else
127 raise;
128 end if;
129 end;
130
131
132 end loop;
133 return rowcount;
134 end;
135
136 function delete_by_user_session_pre1159(
137 X_USER_ID IN VARCHAR2 ,
138 X_SESSION_ID IN VARCHAR2 ) return NUMBER is
139 rowcount number := 0;
140 temp_rowcount number := 0;
141 begin
142 loop
143 begin
144 delete from fnd_log_messages
145 where rownum <= 1000
146 and USER_ID = X_USER_ID
147 and SESSION_ID = X_SESSION_ID
148 and TRANSACTION_CONTEXT_ID is null;
149 temp_rowcount := sql%rowcount;
150 commit;
151 rowcount := rowcount + temp_rowcount;
152 exit when (temp_rowcount = 0);
153 exception
154 when no_data_found then
155 null; /* Should never happen */
156 when others then
157 if ((sqlcode = 60) or (sqlcode = 4020)) then
158 null; /* Ignore rows that are deadlocked */
159 else
160 raise;
161 end if;
162 end;
163
164
165 end loop;
166 return rowcount;
167 end;
168
169
170 function delete_by_module(
171 X_MODULE IN VARCHAR2 ) return NUMBER is
172 rowcount number := 0;
173 temp_rowcount number := 0;
174 begin
175 loop
176 begin
177 delete from fnd_log_messages
178 where rownum <= 1000
179 and module like X_MODULE
180 and TRANSACTION_CONTEXT_ID is null;
181 temp_rowcount := sql%rowcount;
182 commit;
183 rowcount := rowcount + temp_rowcount;
184 exit when (temp_rowcount = 0);
185 exception
186 when no_data_found then
187 null; /* Should never happen */
188 when others then
189 if ((sqlcode = 60) or (sqlcode = 4020)) then
190 null; /* Ignore rows that are deadlocked */
191 else
192 raise;
193 end if;
194 end;
195
196
197 end loop;
198 return rowcount;
199 end;
200
201
202 function delete_by_date_range_pre1159(
203 X_START_DATE IN DATE ,
204 X_END_DATE IN DATE ) return NUMBER is
205 rowcount number := 0;
206 temp_rowcount number := 0;
207 begin
208 if((X_START_DATE is NULL) and (X_END_DATE is NULL)) then
209 return delete_all;
210 end if;
211 loop
212 begin
213 if (X_START_DATE is NULL) then
214 delete from fnd_log_messages
215 where rownum <= 1000
216 and timestamp <= X_END_DATE
217 and TRANSACTION_CONTEXT_ID is null;
218 elsif (X_END_DATE is NULL) then
219 delete from fnd_log_messages
220 where rownum <= 1000
221 and timestamp >= X_START_DATE
222 and TRANSACTION_CONTEXT_ID is null;
223 elsif ((X_START_DATE is NOT NULL) and (X_END_DATE is NOT NULL)) then
224 delete from fnd_log_messages
225 where rownum <= 1000
226 and timestamp >= X_START_DATE
227 and timestamp <= X_END_DATE
228 and TRANSACTION_CONTEXT_ID is null;
229 else
230 return -1; /* should never happen */
231 end if;
232 -- Store in temp_rowcount as commit will reset
233 temp_rowcount := sql%rowcount;
234 commit;
235 rowcount := rowcount + temp_rowcount;
236 exit when (temp_rowcount = 0);
237 exception
238 when no_data_found then
239 null; /* Should never happen */
240 when others then
241 if ((sqlcode = 60) or (sqlcode = 4020)) then
242 null; /* Ignore rows that are deadlocked */
243 else
244 raise;
245 end if;
246 end;
247 end loop;
248 fnd_file.put_line(fnd_file.log, dbms_utility.get_time || ' delete_by_date_range_pre1159: ' ||
249 'Deleted rows from fnd_log_messages ' || rowcount);
250 return rowcount;
251 end;
252
253
254
255 /* Deletes messages at level and all levels below.*/
256 function delete_by_max_level(
257 X_LEVEL IN NUMBER) return NUMBER is
258 rowcount number := 0;
259 temp_rowcount number := 0;
260 begin
261 /* For performance just delete all if we would anyway*/
262 if (X_LEVEL <= 1) then
263 return delete_all;
264 end if;
265 loop
266 begin
267 delete from fnd_log_messages
268 where rownum <= 1000
269 and level <= X_LEVEL
270 and TRANSACTION_CONTEXT_ID is null;
271 temp_rowcount := sql%rowcount;
272 commit;
273 rowcount := rowcount + temp_rowcount;
274 exit when (temp_rowcount = 0);
275 exception
276 when no_data_found then
277 null; /* Should never happen */
278 when others then
279 if ((sqlcode = 60) or (sqlcode = 4020)) then
280 null; /* Ignore rows that are deadlocked */
281 else
282 raise;
283 end if;
284 end;
285
286
287 end loop;
288 return rowcount;
289 end;
290
291
292 function delete_all_pre1159 return NUMBER is
293 rowcount number := 0;
294 temp_rowcount number := 0;
295 begin
296 loop
297 begin
298 delete from fnd_log_messages
299 where rownum <= 1000
300 and TRANSACTION_CONTEXT_ID is null;
301 temp_rowcount := sql%rowcount;
302 commit;
303 rowcount := rowcount + temp_rowcount;
304 exit when (temp_rowcount = 0);
305 exception
306 when no_data_found then
307 null; /* Should never happen */
308 when others then
309 if ((sqlcode = 60) or (sqlcode = 4020)) then
310 null; /* Ignore rows that are deadlocked */
311 else
312 raise;
313 end if;
314 end;
315
316
317 end loop;
318 return rowcount;
319 end;
320
321
322
323
324
325
326
327
328
329 ---End Pre 1159 methods---------------------------------------------------------
330
331
332 --Start Methods for System Alert -----------------------------------------------
333 -------------DEBUG METHODS
334 --------------------------------------------------------------------------------
335 procedure fdebug(msg in varchar2)
336 IS
337 l_msg VARCHAR2(1);
338 BEGIN
339 if (C_DEBUG) then
340 --- xdbms_xoutput.xput_line(dbms_utility.get_time || ' ' || msg);
341 fnd_file.put_line( fnd_file.log, dbms_utility.get_time || ' ' || msg);
342 l_msg := 'm';
343 end if;
344 END fdebug;
345
346 procedure init
347 IS
348 BEGIN
349 s_rows_deleted_flm := 0; --FND_LOG_MESSAGES;
350 s_rows_deleted_fen := 0; --FND_EXCEPTION_NOTES
351 s_rows_deleted_fle := 0; --FND_LOG_EXCEPTIONS
352 s_rows_deleted_flmt := 0; --FND_LOG_METRICS
353 s_rows_deleted_flue := 0; --FND_LOG_UNIQUE_EXCEPTIONS
354 s_rows_deleted_fobsn := 0; --FND_OAM_BIZEX_SENT_NOTIF
355 s_rows_deleted_fltc := 0; --FND_LOG_TRANSACTION_CONTEXT
356 END init;
357
358 procedure printCount
359 is
360 l_count NUMBER;
361 begin
362 l_count := 0;
363
364 /*
365 select count(*) into l_count from FND_LOG_MESSAGES;
366 fnd_file.put_line( fnd_file.log,'Rows in FND_LOG_MESSAGES=' || l_count);
367 select count(*) into l_count from FND_EXCEPTION_NOTES;
368 fnd_file.put_line( fnd_file.log,'Rows in FND_EXCEPTION_NOTES=' || l_count);
369 select count(*) into l_count from FND_LOG_EXCEPTIONS;
370 fnd_file.put_line( fnd_file.log,'Rows in FND_LOG_EXCEPTIONS=' || l_count);
371 select count(*) into l_count from FND_LOG_METRICS;
372 fnd_file.put_line( fnd_file.log,'Rows in FND_LOG_METRICS=' || l_count);
373 select count(*) into l_count from FND_LOG_UNIQUE_EXCEPTIONS;
374 fnd_file.put_line( fnd_file.log,'Rows in FND_LOG_UNIQUE_EXCEPTIONS=' || l_count);
375 select count(*) into l_count from FND_OAM_BIZEX_SENT_NOTIF;
376 fnd_file.put_line( fnd_file.log,'Rows in FND_OAM_BIZEX_SENT_NOTIF=' || l_count);
377 select count(*) into l_count from FND_LOG_TRANSACTION_CONTEXT;
378 fnd_file.put_line( fnd_file.log,'Rows in FND_LOG_TRANSACTION_CONTEXT=' || l_count);
379 select count(*) into l_count from FND_LOG_ATTACHMENTS;
380 fnd_file.put_line( fnd_file.log,'Rows in FND_LOG_ATTACHMENTS=' || l_count);
381 */
382 end printCount;
383
384
385
386 procedure debugPrint(list in TrnCtxIdListTyp)
387 IS
388 BEGIN
389 if ((list is null) or (list.count = 0)) then
390 fdebug(' Null List ..TrnCtxIdListTyp');
391 return;
392 end if;
393
394 --- fdebug(' Start Printing ..TrnCtxIdListTyp');
395 fdebug(' Count ..TrnCtxIdListTyp=' || list.count);
396
397 --- for ii in list.FIRST..list.LAST loop
398 -- fdebug(ii || '...' || list(ii));
399 -- end loop;
400 --- fdebug(' End Printing ..TrnCtxIdListTyp');
401
402 END debugPrint;
403
404 procedure debugPrint(list in LogSeqListTyp)
405 IS
406 BEGIN
407 if ((list is null) or (list.count = 0)) then
408 --- fdebug(' Null List ..LogSeqListTyp');
409 return;
410 end if;
411
412 --- fdebug(' Start Printing ..LogSeqListTyp');
413 --- fdebug(' Count ..LogSeqListTyp=' || list.count);
414
415
416 -- fdebug(' Start Printing ..LogSeqListTyp');
417 -- for ii in list.FIRST..list.LAST loop
418 -- fdebug(ii || '...' || list(ii));
419 -- end loop;
420 -- fdebug(' End Printing ..LogSeqListTyp');
421
422 END debugPrint;
423
424 procedure debugPrint(list in UExcIdListTyp)
425 IS
426 BEGIN
427 if ((list is null) or (list.count = 0)) then
428 fdebug(' Null List ..UExcIdListTyp');
429 return;
430 end if;
431
432 -- fdebug(' Start Printing ..UExcIdListTyp');
433 fdebug(' Count ..UExcIdListTyp=' || list.count);
434 -- for ii in list.FIRST..list.LAST loop
435 -- fdebug(ii || '...' || list(ii));
436 -- end loop;
437 -- fdebug(' End Printing ..UExcIdListTyp');
438
439 END debugPrint;
440
441 --------------------------------------------------------------------------------
442 procedure mergelist(listFinal in out NOCOPY LogSeqListTyp, listSub in LogSeqListTyp)
443 IS
444 indx number := 1;
445 BEGIN
446 if ((listSub is null) or (listSub.count = 0)) then
447 return;
448 end if;
449
450 if (listFinal is not null) then
451 indx := listFinal.count + 1;
452 end if;
453
454 for ii in listSub.FIRST..listSub.LAST loop
455 listFinal(indx) := listSub(ii);
456 indx := indx+1;
457 end loop;
458 END mergelist;
459
460
461 procedure mergelist(listFinal in out NOCOPY UExcIdListTyp, listSub in UExcIdListTyp)
462 IS
463 indx number := 1;
464 BEGIN
465 --- fdebug('Start merge final list');
466 -- debugprint(listFinal);
467 if (listSub.count = 0) then
468 --- fdebug('Nothing to merge listSub is null');
469 null;
470 return;
471 end if;
472
473 if (listFinal is not null) then
474 indx := listFinal.count + 1;
475 end if;
476
477 for ii in listSub.FIRST..listSub.LAST loop
478 listFinal(indx) := listSub(ii);
479 indx := indx+1;
480 end loop;
481
482 --- fdebug('end merge final');
483 --- debugprint(listFinal);
484 --- fdebug('Sublist');
485 --- debugprint(listSub);
486 END mergelist;
487
488
489 procedure mergelist(listFinal in out NOCOPY TrnCtxIdListTyp, listSub in TrnCtxIdListTyp)
490 IS
491 indx number := 1;
492 BEGIN
493 if ((listSub is null) or (listSub.count = 0)) then
494 return;
495 end if;
496
497 if (listFinal is not null) then
498 indx := listFinal.count + 1;
499 end if;
500
501 for ii in listSub.FIRST..listSub.LAST loop
502 listFinal(indx) := listSub(ii);
503 indx := indx+1;
504 end loop;
505 END mergelist;
506
507
508 --------------------------------------------------------------------------------
509 --Methods for dynamic sql
510
511 function getTrSQLCriteria(pCriteria in number) return VARCHAR2
512 IS
513 l_retu VARCHAR2(200);
514 BEGIN
515 if (pCriteria = C_PURGE_CRITERIA_ALL ) then
516 l_retu := '';
517 elsif (pCriteria = C_PURGE_CRITERIA_START_DATE) then
518 l_retu := ' and fltc.CREATION_DATE >= :1 ';
519 elsif(pCriteria = C_PURGE_CRITERIA_END_DATE ) then
520 l_retu := ' and fltc.CREATION_DATE <= :1 ';
521 elsif(pCriteria = C_PURGE_CRITERIA_RANGE_DATE ) then
522 l_retu := ' and fltc.CREATION_DATE >= :1 '
523 || ' and fltc.CREATION_DATE <= :2 ';
524 elsif(pCriteria = C_PURGE_CRITERIA_USER ) then
525 l_retu := ' and fltc.USER_ID = :1 ';
526 elsif(pCriteria = C_PURGE_CRITERIA_SESSION ) then
527 l_retu := ' and fltc.SESSION_ID = :1 ';
528 elsif(pCriteria = C_PURGE_CRITERIA_USER_SESSION) then
529 l_retu := ' and fltc.USER_ID = :1 '
530 || ' and fltc.SESSION_ID = :2 ';
531 end if;
532
533 --- fdebug('getTrSQLCriteria:' || l_retu);
534 return l_retu;
535 END getTrSQLCriteria;
536
537
538
539 function getTrSQL(pTrType in number, pCriteria in number) return VARCHAR2
540 IS
541 l_retu VARCHAR2(2000);
542 l_part1 VARCHAR2(250);
543 l_part2 VARCHAR2(250);
544 l_part3 VARCHAR2(250);
545 l_part4 VARCHAR2(250);
546 BEGIN
547 fdebug('In getTrSQL');
548 l_part3 :='';
549 l_part4 :='';
550
551 l_retu := 'select distinct fltc.TRANSACTION_CONTEXT_ID '
552 || ' from FND_LOG_TRANSACTION_CONTEXT fltc ';
553
554
555 if (pTrType = C_TR_REQUEST_TYPE) then
556 l_part1 := ' where fltc.TRANSACTION_TYPE = ''REQUEST'' ';
557 l_part2 := ' and not exists (select null from FND_CONCURRENT_REQUESTS fcr ';
558 l_part3 := ' where fcr.REQUEST_ID = fltc.TRANSACTION_ID and fcr.phase_code <> ''C'')';
559 elsif (pTrType = C_TR_SERVICE_TYPE) then
560 l_part1 := ' where fltc.TRANSACTION_TYPE = ''SERVICE'' ';
561 l_part2 := ' and not exists (select null from FND_CONCURRENT_PROCESSES fcp ';
562 --5688407, added "U" below
563 l_part3 := ' where fcp.CONCURRENT_PROCESS_ID = fltc.TRANSACTION_ID and fcp.PROCESS_STATUS_CODE not in ( ''S'', ''K'', ''U'' ))';
564 elsif(pTrType = C_TR_FORM_TYPE) then
565 l_part1 := ' where fltc.TRANSACTION_TYPE = ''FORM'' ';
566 l_part2 := ' and NOT EXISTS ';
567 l_part3 := ' (select NULL from GV$SESSION where AUDSID= fltc.TRANSACTION_ID )';
568 elsif(pTrType = C_TR_ICX_TYPE) then
569 l_part1 := ' where fltc.TRANSACTION_TYPE = ''ICX'' and((exists'
570 || ' (select null from ICX_TRANSACTIONS it where it.TRANSACTION_ID=fltc.TRANSACTION_ID'
571 || ' and SYSDATE-1 > it.LAST_CONNECT ))';
572 l_part2 := ' or(sysdate-1 > ';
573 l_part3 := ' (select it1.LAST_CONNECT from ICX_SESSIONS it1 where it1.SESSION_ID=fltc.SESSION_ID))';
574 l_part4 := ' or (NOT EXISTS ( SELECT null FROM ICX_SESSIONS it1 where it1.SESSION_ID=fltc.SESSION_ID)'
575 || ' ))'
576 ;
577 elsif(pTrType = C_TR_UNKNOWN_TYPE) then
578 l_part1 := ' where fltc.TRANSACTION_TYPE NOT IN ';
579 l_part2 := ' (''REQUEST'', ''SERVICE'', ''FORM'', ''ICX'') ';
580 end if;
581
582 fdebug('SQL1:' || l_retu);
583 fdebug(l_part1);
584 fdebug(l_part2);
585 fdebug(l_part3);
586 fdebug(l_part4);
587 fdebug(getTrSQLCriteria(pCriteria));
588
589 l_retu := l_retu || l_part1 || l_part2 || l_part3 || l_part4
590 || getTrSQLCriteria(pCriteria);
591 fdebug('Out getTrSQL');
592 return l_retu;
593 END getTrSQL;
594
595
596
597
598
599 --------------------------------------------------------------------------------
600 procedure upDateRetCode(pCodeExist in out NOCOPY number, pCodeNew in number)
601 IS
602 BEGIN
603 if (pCodeExist < pCodeNew) then
604 pCodeExist := pCodeNew;
605 end if;
606 END upDateRetCode;
607 --------------------------------------------------------------------------------
608 procedure doCommit
609 IS
610 BEGIN
611 commit;
612 --s_rows_deleted := s_rows_deleted + sql%rowcount;
613 --fdebug('s_rows_deleted=' || s_rows_deleted);
614 --if (s_rows_deleted >= COUNT_COMMIT) then
615 -- commit;
616 -- s_rows_deleted := 0;
617 -- fdebug('Commit');
618 -- end if;
619 END doCommit;
620
621 --------------------------------------------------------------------------------
622 function getDeleteBlock(pCurrentIndex in number, pTotalSize in number) return number
623 IS
624 l_retu NUMBER;
625 l_start NUMBER;
626 BEGIN
627 if (pCurrentIndex < 1) then
628 l_retu := DELETE_BLOCK;
629 else
630 l_retu := pCurrentIndex + DELETE_BLOCK;
631 end if;
632
633 if (l_retu > pTotalSize) then
634 l_retu := pTotalSize;
635 end if;
636
637 return l_retu;
638 END getDeleteBlock;
639
640 /******************************************************************************/
641 procedure DELETE_EXCEPTIONS_INFO(p_logSeqList in LogSeqListTyp
642 , pRetCode out NOCOPY number)
643 IS
644 l_table VARCHAR2(25);
645 l_start NUMBER;
646 l_end NUMBER :=0;
647
648 BEGIN
649 fdebug('In:FND_BE_UTIL.DELETE_EXCEPTIONS_INFO rec=' || p_logSeqList.count);
650 pRetCode := C_SUCCESS;
651
652 --Check input parameters
653 if (p_logSeqList is null) or (p_logSeqList.count < 1) then
654 return;
655 end if;
656
657 loop
658 l_start := l_end + 1;
659 l_end := getDeleteBlock(l_end, p_logSeqList.count);
660 exit when l_start > p_logSeqList.count;
661
662 begin
663
664 fdebug('Start Del FND_LOG_EXCEPTIONS');
665 fdebug('l_start = ' || l_start || ' l_end = ' || l_end);
666
667 l_table := 'FND_LOG_EXCEPTIONS';
668 FORALL ii IN l_start..l_end
669 delete from FND_LOG_EXCEPTIONS flem where flem.LOG_SEQUENCE=p_logSeqList(ii)
670 and NOT EXISTS
671 (select null from FND_LOG_EXCEPTIONS fle, FND_LOG_UNIQUE_EXCEPTIONS flue where
672 fle.LOG_SEQUENCE = flem.LOG_SEQUENCE
673 and flue.UNIQUE_EXCEPTION_ID = fle.UNIQUE_EXCEPTION_ID
674 and flue.STATUS <> 'C');
675
676 s_rows_deleted_fle := s_rows_deleted_fle + sql%rowcount; --FND_LOG_EXCEPTIONS
677 doCommit;
678
679 fdebug('Start Del FND_LOG_MESSAGES');
680 l_table := 'FND_LOG_MESSAGES';
681
682 FORALL ii IN l_start..l_end
683 delete from FND_LOG_MESSAGES flm where flm.LOG_SEQUENCE=p_logSeqList(ii)
684 and NOT EXISTS
685 (select null from FND_LOG_EXCEPTIONS fle where
686 fle.LOG_SEQUENCE = flm.LOG_SEQUENCE
687 );
688
689 s_rows_deleted_flm := s_rows_deleted_flm + sql%rowcount; --FND_LOG_MESSAGES;
690 doCommit;
691
692
693 l_table := 'FND_LOG_ATTACHMENTS';
694 FORALL ii IN l_start..l_end
695 delete from FND_LOG_ATTACHMENTS fla where fla.LOG_SEQUENCE=p_logSeqList(ii)
696 and NOT EXISTS
697 (select null from FND_LOG_EXCEPTIONS fle where fle.LOG_SEQUENCE = p_logSeqList(ii))
698 and NOT EXISTS
699 (select null from FND_LOG_MESSAGES flm where flm.LOG_SEQUENCE = p_logSeqList(ii));
700
701
702
703 EXCEPTION
704 when others then
705 fdebug('Failed in DELETE_EXCEPTIONS_INFO');
706 raise;
707 pRetCode := C_WARNING;
708 -- if ((sqlcode = 60) or (sqlcode = 4020)) then
709 -- null; /* Ignore rows that are deadlocked */
710 -- else
711 -- raise;
712 -- end if;
713 end; ----begin
714
715 end loop;
716
717
718 fdebug('OUT:FND_BE_UTIL.DELETE_EXCEPTIONS_INFO');
719
720 END DELETE_EXCEPTIONS_INFO;
721 --------------------------------------------------------------------------------
722 procedure DELETE_UNIQUE_EXCEPTIONS_INFO(p_UEXList UExcIdListTyp
723 , pRetCode out NOCOPY number)
724 IS
725 l_table VARCHAR2(25);
726 l_start NUMBER;
727 l_end NUMBER :=0;
728
729 BEGIN
730 fdebug('In:FND_BE_UTIL.DELETE_UNIQUE_EXCEPTIONS_INFO rec:' || p_UEXList.count);
731 pRetCode := C_SUCCESS;
732
733 --Check input parameters
734 if (p_UEXList is null) or (p_UEXList.count < 1) then
735 return;
736 end if;
737
738 loop
739 l_start := l_end + 1;
740 l_end := getDeleteBlock(l_end, p_UEXList.count);
741 exit when l_start > p_UEXList.count;
742
743 begin
744 fdebug('Start Del FND_EXCEPTION_NOTES');
745 l_table := 'FND_LOG_MESSAGES';
746
747 FORALL ii IN l_start..l_end
748 DELETE FROM FND_EXCEPTION_NOTES fen
749 WHERE fen.UNIQUE_EXCEPTION_ID = p_UEXList(ii)
750 and NOT EXISTS
751 (SELECT null from FND_LOG_EXCEPTIONS fle
752 where fle.UNIQUE_EXCEPTION_ID = fen.UNIQUE_EXCEPTION_ID
753 );
754 s_rows_deleted_fen := s_rows_deleted_fen + sql%rowcount; --FND_EXCEPTION_NOTES
755 doCommit;
756
757 fdebug('Start Del FND_OAM_BIZEX_SENT_NOTIF');
758 l_table := 'FND_OAM_BIZEX_SENT_NOTIF';
759 FORALL ii IN l_start..l_end
760 DELETE FROM FND_OAM_BIZEX_SENT_NOTIF fobsf
761 WHERE fobsf.UNIQUE_EXCEPTION_ID = p_UEXList(ii)
762 and NOT EXISTS
763 (SELECT null from FND_LOG_EXCEPTIONS fle
764 where fle.UNIQUE_EXCEPTION_ID = fobsf.UNIQUE_EXCEPTION_ID
765 );
766 s_rows_deleted_fobsn := s_rows_deleted_fobsn + sql%rowcount; --FND_OAM_BIZEX_SENT_NOTIF
767 doCommit;
768
769 fdebug('Start Del FND_LOG_UNIQUE_EXCEPTIONS');
770 l_table := 'FND_LOG_UNIQUE_EXCEPTIONS';
771 FORALL ii IN l_start..l_end
772 DELETE FROM FND_LOG_UNIQUE_EXCEPTIONS flue
773 WHERE flue.UNIQUE_EXCEPTION_ID = p_UEXList(ii)
774 and NOT EXISTS
775 (SELECT null from FND_LOG_EXCEPTIONS fle
776 where fle.UNIQUE_EXCEPTION_ID = flue.UNIQUE_EXCEPTION_ID
777 );
778 s_rows_deleted_flue := s_rows_deleted_flue + sql%rowcount; --FND_LOG_UNIQUE_EXCEPTIONS
779 doCommit;
780
781 EXCEPTION
782 when others then
783 fdebug('Failed in DELETE_UNIQUE_EXCEPTIONS_INFO');
784 pRetCode := C_WARNING;
785 if ((sqlcode = 60) or (sqlcode = 4020)) then
786 null; /* Ignore rows that are deadlocked */
787 else
788 raise;
789 end if;
790 end; ----begin
791 end loop;
792 fdebug('Out:FND_BE_UTIL.DELETE_UNIQUE_EXCEPTIONS_INFO');
793
794 END DELETE_UNIQUE_EXCEPTIONS_INFO;
795
796 --------------------------------------------------------------------------------
797 --This function deletes those unique exceptions for which there is no infor
798 -- in fnd_log_exceptions
799
800 procedure DELETE_UNIQUEA_EXCEPTIONS_INFO(pRetCode out NOCOPY number)
801 IS
802 l_table VARCHAR2(25);
803 l_start NUMBER;
804 l_end NUMBER :=0;
805 l_UEXList UExcIdListTyp;
806
807 BEGIN
808 fdebug('In:FND_BE_UTIL.DELETE_UNIQUEA_EXCEPTIONS_INFO ');
809
810 --Check input parameters
811 select flue.UNIQUE_EXCEPTION_ID BULK COLLECT into l_UEXList
812 from
813 FND_LOG_UNIQUE_EXCEPTIONS flue
814 where
815 NOT EXISTS
816 (SELECT null from FND_LOG_EXCEPTIONS fle
817 where fle.UNIQUE_EXCEPTION_ID = flue.UNIQUE_EXCEPTION_ID
818 );
819
820 DELETE_UNIQUE_EXCEPTIONS_INFO(l_UEXList,pRetCode);
821
822 fdebug('Out:FND_BE_UTIL.DELETE_UNIQUEA_EXCEPTIONS_INFO');
823
824 END DELETE_UNIQUEA_EXCEPTIONS_INFO;
825
826 --------------------------------------------------------------------------------
827 --This function deletes those exceptions whose transaction Context Id is null.
828 -- but meets the input date range.
829
830 procedure DELETE_EXCEPTIONS_NULL_TRID(pRetCode out NOCOPY number
831 ,X_START_DATE IN DATE, X_END_DATE IN DATE )
832 IS
833 l_EXList LogSeqListTyp;
834
835 BEGIN
836 fdebug('In:FND_BE_UTIL.DELETE_EXCEPTIONS_NULL_TRID');
837
838 --Check input parameters
839 select fle.LOG_SEQUENCE BULK COLLECT into l_EXList
840 from
841 FND_LOG_EXCEPTIONS fle
842 ,FND_LOG_UNIQUE_EXCEPTIONS flue
843 where
844 fle.UNIQUE_EXCEPTION_ID = flue.UNIQUE_EXCEPTION_ID
845 and fle.TRANSACTION_CONTEXT_ID is null
846 and flue.STATUS = 'C'
847 and flue.CREATION_DATE >= X_START_DATE
848 and flue.CREATION_DATE <= X_END_DATE;
849
850 DELETE_EXCEPTIONS_INFO(l_EXList ,pRetCode);
851
852 fdebug('Out:DELETE_EXCEPTIONS_NULL_TRID');
853
854 END DELETE_EXCEPTIONS_NULL_TRID;
855
856
857
858 --------------------------------------------------------------------------------
859 function DEL_METR_TRANS_INFO(p_TrList in TrnCtxIdListTyp
860 , pRetCode out NOCOPY number) return number
861 IS
862 l_table VARCHAR2(30);
863 l_start NUMBER;
864 l_end NUMBER :=0;
865 l_TrList TrnCtxIdListTyp;
866 l_retu NUMBER := 0;
867
868 BEGIN
869 fdebug('In:FND_BE_UTIL.DEL_METR_TRANS_INFO' || p_TrList.count);
870 pRetCode := C_SUCCESS;
871
872 --Check input parameters
873 if (p_TrList is null) or (p_TrList.count < 1) then
874 return l_retu;
875 end if;
876
877 loop
878 l_start := l_end + 1;
879 l_end := getDeleteBlock(l_end, p_TrList.count);
880 exit when l_start > p_TrList.count;
881
882 begin
883 fdebug('Start Del FND_LOG_METRICS');
884 l_table := 'FND_LOG_METRICS';
885
886 FORALL ii IN l_start..l_end
887 delete from FND_LOG_METRICS flm where flm.TRANSACTION_CONTEXT_ID=p_TrList(ii)
888 and NOT EXISTS
889 (select null from FND_LOG_EXCEPTIONS fle WHERE fle.TRANSACTION_CONTEXT_ID=flm.TRANSACTION_CONTEXT_ID);
890 s_rows_deleted_flmt := s_rows_deleted_flmt + sql%rowcount; --FND_LOG_METRICS
891 doCommit;
892
893
894 fdebug('Start Del FND_LOG_TRANSACTION_CONTEXT');
895 l_table := 'FND_LOG_TRANSACTION_CONTEXT';
896 FORALL ii IN l_start..l_end
897 delete from FND_LOG_TRANSACTION_CONTEXT where TRANSACTION_CONTEXT_ID=p_TrList(ii)
898 and NOT EXISTS
899 (select null from FND_LOG_EXCEPTIONS fle WHERE fle.TRANSACTION_CONTEXT_ID=p_TrList(ii))
900 and NOT EXISTS
901 (select null from FND_LOG_MESSAGES flm WHERE flm.TRANSACTION_CONTEXT_ID=p_TrList(ii));
902
903 l_retu := sql%rowcount;
904 s_rows_deleted_fltc := s_rows_deleted_fltc + sql%rowcount; --FND_LOG_TRANSACTION_CONTEXT
905 doCommit;
906 return l_retu;
907 fdebug('End Del FND_LOG_TRANSACTION_CONTEXT');
908
909 EXCEPTION
910 when others then
911 fdebug('Failed in DEL_METR_TRANS_INFO');
912 pRetCode := C_WARNING;
913 if ((sqlcode = 60) or (sqlcode = 4020)) then
914 null; /* Ignore rows that are deadlocked */
915 else
916 raise;
917 end if;
918
919 end; ----begin
920 end loop;
921 fdebug('Out:FND_BE_UTIL.DEL_METR_TRANS_INFO');
922 END DEL_METR_TRANS_INFO;
923 --------------------------------------------------------------------------------
924 function purgeTablesForLists(pLogSeqList in LogSeqListTyp, pUEXList in UExcIdListTyp
925 , pTrnCtxIdList in TrnCtxIdListTyp, pRetCode out NOCOPY number) return NUMBER
926 is
927 l_retCode number;
928 l_retu number := 0;
929 begin
930 fdebug('In purgeTablesForLists');
931 fdebug('pTrnCtxIdList count=' || pTrnCtxIdList.count);
932 fdebug('pLogSeqList count=' || pLogSeqList.count);
933 fdebug('pUEXList count=' || pUEXList.count);
934
935 debugPrint(pTrnCtxIdList);
936
937 --Delete Messages and Exceptions
938 --- debugPrint(l_LogSeqList);
939 DELETE_EXCEPTIONS_INFO(pLogSeqList, l_retCode);
940 upDateRetCode(pRetCode, l_retCode);
941
942 ---Delete UniqueExceptions
943 --- debugPrint(l_UEXList);
944 DELETE_UNIQUE_EXCEPTIONS_INFO(pUEXList, l_retCode);
945 upDateRetCode(pRetCode, l_retCode);
946
947 ---Delete Transaction Info
948 l_retu := DEL_METR_TRANS_INFO(pTrnCtxIdList, l_retCode);
949 upDateRetCode(pRetCode, l_retCode);
950 fdebug('Out purgeTablesForLists');
951 return l_retu;
952 end purgeTablesForLists;
953
954 function purge(pTrCursor in out NOCOPY GenCursor, pRetCode out NOCOPY number) return NUMBER is
955 l_LogSeqList LogSeqListTyp;
956 l_UEXList UExcIdListTyp;
957 l_LogSeqList1 LogSeqListTyp;
958 l_UEXList1 UExcIdListTyp;
959
960 l_LogSeqListNull LogSeqListTyp;
961 l_UEXListNull UExcIdListTyp;
962
963 l_TrnCtxIdList TrnCtxIdListTyp;
964 l_TrnCtxIdListNull TrnCtxIdListTyp;
965 ii number;
966 l_retCode number;
967 l_retu number := 0;
968 begin
969 fdebug('In purge');
970 pRetCode := C_SUCCESS;
971 if ((pTrCursor is null) or (pTrCursor%ISOPEN = false)) then
972 return l_retu;
973 end if;
974
975 ii := 1;
976 fdebug('Start creating log seq and exception list');
977 LOOP
978 FETCH pTrCursor INTO l_TrnCtxIdList(ii);
979 EXIT WHEN pTrCursor%NOTFOUND;
980 --fdebug('l_TrnCtxIdList(ii)=' || l_TrnCtxIdList(ii));
981
982
983 --Collect l_UEXList
984 l_UEXList1 := l_UEXListNull;
985 select distinct flue.UNIQUE_EXCEPTION_ID BULK COLLECT into l_UEXList1
986 from
987 FND_LOG_EXCEPTIONS fle
988 , FND_LOG_UNIQUE_EXCEPTIONS flue
989 where
990 fle.TRANSACTION_CONTEXT_ID = l_TrnCtxIdList(ii)
991 and flue.UNIQUE_EXCEPTION_ID = fle.UNIQUE_EXCEPTION_ID ---
992 and flue.STATUS = 'C';
993
994 ---fdebug('l_UEXList1.count=' || l_UEXList1.count);
995 mergelist(l_UEXList, l_UEXList1);
996
997
998 --Collect LogSeq
999 l_LogSeqList1 := l_LogSeqListNull;
1000 select LOG_SEQUENCE BULK COLLECT into l_LogSeqList1
1001 from
1002 (
1003 select LOG_SEQUENCE from FND_LOG_MESSAGES flm
1004 where flm.TRANSACTION_CONTEXT_ID = l_TrnCtxIdList(ii)
1005 union
1006 select LOG_SEQUENCE from FND_LOG_EXCEPTIONS fle
1007 where fle.TRANSACTION_CONTEXT_ID = l_TrnCtxIdList(ii)
1008 and NOT EXISTS(select null from FND_LOG_MESSAGES flm1 where flm1.LOG_SEQUENCE=fle.LOG_SEQUENCE)
1009 );
1010 ---fdebug('l_LogSeqList1.count=' || l_LogSeqList1.count);
1011 mergelist(l_LogSeqList, l_LogSeqList1);
1012
1013 --Check if any list has more than MAX_LIST_COUNT Data. If Yes Delete All
1014 if (
1015 (l_LogSeqList.count >= MAX_LIST_COUNT)
1016 OR (l_UEXList.count >= MAX_LIST_COUNT)
1017 OR(l_TrnCtxIdList.count >= MAX_LIST_COUNT)
1018 ) then
1019 l_retu := l_retu + purgeTablesForLists(l_LogSeqList, l_UEXList, l_TrnCtxIdList, l_retCode);
1020 upDateRetCode(pRetCode, l_retCode);
1021 l_LogSeqList := l_LogSeqListNull;
1022 l_UEXList := l_UEXListNull;
1023 l_TrnCtxIdList := l_TrnCtxIdListNull;
1024 ii := 0;
1025 end if;
1026 ii := ii + 1;
1027 END LOOP;
1028 fdebug('ii=' || ii);
1029 fdebug('End creating log seq and exception list');
1030 l_retu := l_retu + purgeTablesForLists(l_LogSeqList, l_UEXList, l_TrnCtxIdList, l_retCode);
1031 upDateRetCode(pRetCode, l_retCode);
1032
1033 close pTrCursor;
1034 commit;
1035 fdebug('Out purge');
1036 return l_retu;
1037 end purge;
1038 --------------------------------------------------------------------------------
1039
1040 procedure getDebugTrType(pList in out NOCOPY VARCAHRSmallListTyp)
1041 is
1042 begin
1043 pList(1) := 'Request';
1044 pList(2) := 'Service';
1045 pList(3) := 'Form';
1046 pList(4) := 'ICX';
1047 pList(5) := 'Unknown';
1048 end;
1049
1050
1051
1052 --------------------------------------------------------------------------------
1053 --Specification APIS
1054 /******************************************************************************/
1055 /******************************************************************************/
1056 function delete_by_date_range(
1057 X_START_DATE IN DATE ,
1058 X_END_DATE IN DATE ) return NUMBER is
1059 rowcount number := 0;
1060 l_retCode number;
1061
1062 l_sqlList VARCAHRListTyp;
1063 l_debugList VARCAHRSmallListTyp;
1064 l_GenCur GenCursor;
1065
1066 l_criteria NUMBER;
1067 l_param1 DATE;
1068 l_sessionId NUMBER;
1069 begin
1070 fdebug ('In - delete_by_date_range -calling old');
1071 init;
1072 -- printCount;
1073 s_rows_deleted_flm := delete_by_date_range_pre1159(X_START_DATE, X_END_DATE);
1074
1075 if((X_START_DATE is NULL) and (X_END_DATE is NULL)) then
1076 return delete_all;
1077 end if;
1078
1079 if (X_START_DATE is NULL) then
1080 l_criteria := C_PURGE_CRITERIA_END_DATE;
1081 l_param1 := X_END_DATE;
1082 elsif (X_END_DATE is NULL) then
1083 l_criteria := C_PURGE_CRITERIA_START_DATE;
1084 l_param1 := X_START_DATE;
1085 else
1086 l_criteria := C_PURGE_CRITERIA_RANGE_DATE;
1087 end if;
1088
1089 l_sqlList(1) := getTrSQL(C_TR_REQUEST_TYPE, l_criteria);
1090 l_sqlList(2) := getTrSQL(C_TR_SERVICE_TYPE, l_criteria);
1091 l_sqlList(3) := getTrSQL(C_TR_FORM_TYPE, l_criteria);
1092 l_sqlList(4) := getTrSQL(C_TR_ICX_TYPE, l_criteria);
1093 l_sqlList(5) := getTrSQL(C_TR_UNKNOWN_TYPE, l_criteria);
1094
1095 getDebugTrType(l_debugList);
1096
1097 for ii in l_sqlList.FIRST..l_sqlList.LAST loop
1098 if (l_criteria = C_PURGE_CRITERIA_RANGE_DATE) then
1099 open l_GenCur for l_sqlList(ii) using X_START_DATE, X_END_DATE;
1100 else
1101 open l_GenCur for l_sqlList(ii) using l_param1;
1102 end if;
1103 fdebug ('Purging - ' || l_debugList(ii));
1104 rowcount := rowcount + purge(l_GenCur, l_retCode);
1105 fdebug ('total rows - ' || rowcount);
1106 end loop;
1107
1108 --Delete the exceptions whose transaction context id is null.
1109 DELETE_EXCEPTIONS_NULL_TRID(l_retCode, X_START_DATE, X_END_DATE);
1110 commit;
1111
1112 --Delete abondoned attachements
1113 delete from FND_LOG_ATTACHMENTS fla where NOT EXISTS
1114 (select null from FND_LOG_EXCEPTIONS fle where fle.LOG_SEQUENCE = fla.LOG_SEQUENCE)
1115 and NOT EXISTS (select null from FND_LOG_MESSAGES flm where flm.LOG_SEQUENCE = fla.LOG_SEQUENCE );
1116 commit;
1117
1118 --delete the abondoned unque exceptions;
1119 DELETE_UNIQUEA_EXCEPTIONS_INFO(l_retCode);
1120
1121 commit;
1122 printCount;
1123 fdebug ('Out - delete_by_date_range');
1124 return rowcount;
1125 exception
1126 when others then
1127 fdebug ('in error delete_by_date_range: ' || SQLCODE);
1128 if l_GenCur %ISOPEN then close l_GenCur; end if;
1129 raise;
1130 end delete_by_date_range;
1131
1132 --------------------------------------------------------------------------------
1133 function delete_by_user(
1134 X_USER_ID IN VARCHAR2 ) return NUMBER is
1135 rowcount number := 0;
1136 l_retCode number;
1137
1138 l_sqlList VARCAHRListTyp;
1139 l_debugList VARCAHRSmallListTyp;
1140 l_GenCur GenCursor;
1141 l_userid NUMBER;
1142 begin
1143 fdebug ('In - delete_by_user');
1144 init;
1145 s_rows_deleted_flm := delete_by_user_pre1159(X_USER_ID);
1146 rowcount := 0;
1147
1148 if ( X_USER_ID is null ) then
1149 return rowcount;
1150 end if;
1151
1152 l_userid := to_number(X_USER_ID);
1153
1154 C_DEBUG := true;
1155 fdebug ('getPurgeSQLS - Request');
1156 l_sqlList(1) := getTrSQL(C_TR_REQUEST_TYPE, C_PURGE_CRITERIA_USER);
1157 l_sqlList(2) := getTrSQL(C_TR_SERVICE_TYPE, C_PURGE_CRITERIA_USER);
1158 l_sqlList(3) := getTrSQL(C_TR_FORM_TYPE, C_PURGE_CRITERIA_USER);
1159 l_sqlList(4) := getTrSQL(C_TR_ICX_TYPE, C_PURGE_CRITERIA_USER);
1160 l_sqlList(5) := getTrSQL(C_TR_UNKNOWN_TYPE, C_PURGE_CRITERIA_USER);
1161
1162 getDebugTrType(l_debugList);
1163
1164
1165 for ii in l_sqlList.FIRST..l_sqlList.LAST loop
1166 open l_GenCur for l_sqlList(ii) using l_userid;
1167 fdebug ('Purging - ' || l_debugList(ii));
1168 rowcount := rowcount + purge(l_GenCur, l_retCode);
1169 end loop;
1170
1171 printCount;
1172 fdebug ('Out - delete_by_user');
1173
1174 commit;
1175 return rowcount;
1176 exception
1177 when others then
1178 fdebug ('in error' || SQLCODE);
1179 if l_GenCur %ISOPEN then close l_GenCur; end if;
1180 raise;
1181 end delete_by_user;
1182
1183
1184 --------------------------------------------------------------------------------
1185 function delete_by_session(
1186 X_SESSION_ID IN VARCHAR2 ) return NUMBER is
1187 rowcount number := 0;
1188 l_retCode number;
1189
1190 l_sqlList VARCAHRListTyp;
1191 l_debugList VARCAHRSmallListTyp;
1192 l_GenCur GenCursor;
1193 l_sessionId NUMBER;
1194 begin
1195 fdebug ('In - delete_by_session');
1196 init;
1197 s_rows_deleted_flm := delete_by_session_pre1159(X_SESSION_ID);
1198 rowcount := 0;
1199
1200 if ( X_SESSION_ID is null ) then
1201 return rowcount;
1202 end if;
1203
1204 l_sessionId := to_number(X_SESSION_ID);
1205
1206 l_sqlList(1) := getTrSQL(C_TR_REQUEST_TYPE, C_PURGE_CRITERIA_SESSION);
1207 l_sqlList(2) := getTrSQL(C_TR_SERVICE_TYPE, C_PURGE_CRITERIA_SESSION);
1208 l_sqlList(3) := getTrSQL(C_TR_FORM_TYPE, C_PURGE_CRITERIA_SESSION);
1209 l_sqlList(4) := getTrSQL(C_TR_ICX_TYPE, C_PURGE_CRITERIA_SESSION);
1210 l_sqlList(5) := getTrSQL(C_TR_UNKNOWN_TYPE, C_PURGE_CRITERIA_SESSION);
1211
1212 getDebugTrType(l_debugList);
1213
1214 for ii in l_sqlList.FIRST..l_sqlList.LAST loop
1215 open l_GenCur for l_sqlList(ii) using l_sessionId;
1216 fdebug ('Purging - ' || l_debugList(ii));
1217 rowcount := rowcount + purge(l_GenCur, l_retCode);
1218 end loop;
1219
1220 printCount;
1221 fdebug ('Out - delete_by_session');
1222 commit;
1223 return rowcount;
1224 exception
1225 when others then
1226 fdebug ('in error delete_by_session: ' || SQLCODE);
1227 if l_GenCur %ISOPEN then close l_GenCur; end if;
1228 raise;
1229 end delete_by_session;
1230
1231 --------------------------------------------------------------------------------
1232 function delete_by_user_session(
1233 X_USER_ID IN VARCHAR2,
1234 X_SESSION_ID IN VARCHAR2 ) return NUMBER is
1235 rowcount number := 0;
1236 l_retCode number;
1237
1238 l_sqlList VARCAHRListTyp;
1239 l_debugList VARCAHRSmallListTyp;
1240 l_GenCur GenCursor;
1241
1242 l_userId NUMBER;
1243 l_sessionId NUMBER;
1244 begin
1245 fdebug ('In - delete_by_user_session');
1246 init;
1247 s_rows_deleted_flm := delete_by_user_session_pre1159(X_USER_ID, X_SESSION_ID);
1248 rowcount := 0;
1249
1250 if (X_USER_ID is null ) or ( X_SESSION_ID is null ) then
1251 return rowcount;
1252 end if;
1253
1254 l_userId := to_number(X_USER_ID);
1255 l_sessionId := to_number(X_SESSION_ID);
1256
1257 l_sqlList(1) := getTrSQL(C_TR_REQUEST_TYPE, C_PURGE_CRITERIA_USER_SESSION);
1258 l_sqlList(2) := getTrSQL(C_TR_SERVICE_TYPE, C_PURGE_CRITERIA_USER_SESSION);
1259 l_sqlList(3) := getTrSQL(C_TR_FORM_TYPE, C_PURGE_CRITERIA_USER_SESSION);
1260 l_sqlList(4) := getTrSQL(C_TR_ICX_TYPE, C_PURGE_CRITERIA_USER_SESSION);
1261 l_sqlList(5) := getTrSQL(C_TR_UNKNOWN_TYPE, C_PURGE_CRITERIA_USER_SESSION);
1262
1263 getDebugTrType(l_debugList);
1264
1265 for ii in l_sqlList.FIRST..l_sqlList.LAST loop
1266 open l_GenCur for l_sqlList(ii) using l_userId, l_sessionId;
1267 fdebug ('Purging - ' || l_debugList(ii));
1268 rowcount := rowcount + purge(l_GenCur, l_retCode);
1269 end loop;
1270
1271 printCount;
1272 fdebug ('Out - delete_by_user_session');
1273 commit;
1274 return rowcount;
1275 exception
1276 when others then
1277 fdebug ('in error delete_by_user_session: ' || SQLCODE);
1278 if l_GenCur %ISOPEN then close l_GenCur; end if;
1279 raise;
1280 end delete_by_user_session;
1281
1282 --------------------------------------------------------------------------------
1283 --function delete_by_max_level Use old API
1284 --------------------------------------------------------------------------------
1285 function delete_all return NUMBER is
1286 rowcount number := 0;
1287 l_retCode number;
1288
1289 l_sqlList VARCAHRListTyp;
1290 l_debugList VARCAHRSmallListTyp;
1291 l_GenCur GenCursor;
1292 begin
1293 fdebug ('In - delete_all');
1294 init;
1295 s_rows_deleted_flm := delete_all_pre1159;
1296 rowcount := 0;
1297
1298 l_sqlList(1) := getTrSQL(C_TR_REQUEST_TYPE, C_PURGE_CRITERIA_ALL);
1299 l_sqlList(2) := getTrSQL(C_TR_SERVICE_TYPE, C_PURGE_CRITERIA_ALL);
1300 l_sqlList(3) := getTrSQL(C_TR_FORM_TYPE, C_PURGE_CRITERIA_ALL);
1301 l_sqlList(4) := getTrSQL(C_TR_ICX_TYPE, C_PURGE_CRITERIA_ALL);
1302 l_sqlList(5) := getTrSQL(C_TR_UNKNOWN_TYPE, C_PURGE_CRITERIA_ALL);
1303
1304 getDebugTrType(l_debugList);
1305
1306 for ii in l_sqlList.FIRST..l_sqlList.LAST loop
1307 open l_GenCur for l_sqlList(ii);
1308 fdebug ('Purging - ' || l_debugList(ii));
1309 rowcount := rowcount + purge(l_GenCur, l_retCode);
1310 end loop;
1311
1312
1313 printCount;
1314 fdebug ('Out - delete_all');
1315 commit;
1316 return rowcount;
1317 exception
1318 when others then
1319 fdebug ('in error delete_all: ' || SQLCODE);
1320 if l_GenCur %ISOPEN then close l_GenCur; end if;
1321 raise;
1322 end delete_all;
1323 ---------------------------------------------------------------------------------------------------------
1324
1325 -------------------------------------------------------------------------
1326 /** Concurrent Program ********************************************************/
1327
1328 procedure delete_by_date_i( errbuf out NOCOPY varchar2,
1329 retcode out NOCOPY varchar2,
1330 last_date in varchar2 ) is
1331 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_BY_DATE_I';
1332 numrows NUMBER;
1333 msgbuf varchar2(2000);
1334 last_dt DATE;
1335 begin
1336
1337 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1338 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1339 c_log_head || l_api_name || '.begin',
1340 c_pkg_name || '.' ||l_api_name ||'(' ||
1341 'last_date=>'|| last_date||');');
1342 end if;
1343
1344 /* Convert character string to date */
1345 if(last_date is NULL) then
1346 last_dt := NULL; /* NULL means for all dates */
1347 else
1348 last_dt := FND_CONC_DATE.STRING_TO_DATE(last_date);
1349 if(last_dt is NULL) then
1350 errbuf := 'Unexpected error converting character string to date:'
1351 ||last_date;
1352 retcode := '2';
1353 FND_FILE.put_line(FND_FILE.log,errbuf);
1354 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1355 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1356 c_log_head || l_api_name || '.end_exception',
1357 'returning from delete_by_date with date exception. last_date='
1358 ||last_date);
1359 end if;
1360 return;
1361 end if;
1362 end if;
1363
1364 fnd_message.set_name('FND', 'PURGING_UP_TO_DATE');
1365 fnd_message.set_token('ENTITY', 'FND_LOG_MESSAGES');
1366 if (last_date is NULL) then
1367 fnd_message.set_token('DATE', 'WF_ALL', TRUE);
1368 else
1369 fnd_message.set_token('DATE', last_date);
1370 end if;
1371 msgbuf := fnd_message.get;
1372 FND_FILE.put_line(FND_FILE.log, msgbuf);
1373
1374 /* Delete from the date back in time */
1375 numrows := delete_by_date_range(NULL, last_dt);
1376
1377 fnd_message.set_name('FND', 'GENERIC_ROWS_PROCESSED');
1378 fnd_message.set_token('ROWS', numrows);
1379 msgbuf := fnd_message.get;
1380 FND_FILE.put_line(FND_FILE.log, msgbuf);
1381
1382 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1383 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1384 c_log_head || l_api_name || '.end',
1385 'returning from delete_by_date_i. numrows='||numrows);
1386 end if;
1387 exception
1388 when others then
1389 errbuf := sqlerrm;
1390 retcode := '2';
1391 FND_FILE.put_line(FND_FILE.log,errbuf);
1392 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1393 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1394 c_log_head || l_api_name || '.end_exception',
1395 'returning from delete_by_date with exception. numrows='||numrows);
1396 end if;
1397 raise;
1398 end delete_by_date_i;
1399
1400
1401
1402
1403 /* Delete data from fnd_log_messages only - Used by Java UI */
1404
1405 function delete_by_sequence(
1406 pLogSeqList IN VARCHAR2) return NUMBER is
1407 begin
1408 fdebug ('In - delete_by_Sequence');
1409
1410 if ( pLogSeqList is null ) then
1411 return 0;
1412 end if;
1413
1414 delete from FND_LOG_MESSAGES flm
1415 where
1416 flm.LOG_SEQUENCE = pLogSeqList;
1417 fdebug ('Out - delete_by_Sequence');
1418 commit;
1419 return 1;
1420 end delete_by_sequence;
1421
1422 ------------------------------------------------------------------------------------------------
1423 /* Delet data from fnd_log_messages only - Used by Java UI */
1424 function delete_by_seqarray(numArrayList IN FND_ARRAY_OF_NUMBER_25) return NUMBER is
1425 ii number := 0;
1426 begin
1427 fdebug ('In - delete_by_seqarray');
1428 for ii in numArrayList.FIRST..numArrayList.LAST loop
1429 delete from
1430 FND_LOG_MESSAGES flm where flm.LOG_SEQUENCE = numArrayList(ii);
1431 END LOOP;
1432 commit;
1433 fdebug ('Out - delete_by_seqarray, deleted ' || numArrayList.COUNT || ' rows');
1434 return numArrayList.COUNT;
1435 end delete_by_seqarray;
1436 --------------------------------------------------------------------------------------------
1437
1438 --------------------------------------------------------------------------------
1439 -- Initializes the apps context to SYSADMIN.
1440 --------------------------------------------------------------------------------
1441 procedure apps_initialize
1442 is
1443 l_user_id number;
1444 l_resp_id number;
1445 l_resp_appl_id number;
1446 begin
1447 select u.user_id
1448 into l_user_id
1449 from fnd_user u
1450 where u.user_name = 'SYSADMIN';
1451
1452 select r.application_id,
1453 r.responsibility_id
1454 into l_resp_appl_id,
1455 l_resp_id
1456 from fnd_application a,
1457 fnd_responsibility r
1458 where r.application_id = a.application_id
1459 and a.application_short_name = 'SYSADMIN'
1460 and r.responsibility_key = 'SYSTEM_ADMINISTRATOR';
1461
1462 fnd_global.apps_initialize(user_id => l_user_id,
1463 resp_id => l_resp_id,
1464 resp_appl_id => l_resp_appl_id);
1465 end apps_initialize;
1466
1467 /* Checks if the 'Purge Debug Log' CP is running, and submits
1468 it if its not. Called by aflogcustart.sql */
1469 procedure start_purge_cp is
1470 l_request_id number;
1471 l_phase varchar2(30);
1472 l_status varchar2(30);
1473 l_dev_phase varchar2(30);
1474 l_dev_status varchar2(30);
1475 l_message varchar2(2000);
1476 l_request_status_return boolean;
1477 l_repeat_options_return boolean;
1478 l_submit_request_return number;
1479
1480 l_cleanup_repeat_interval number;
1481 l_cleanup_app_short_name varchar2(50);
1482 l_cleanup_program varchar2(30);
1483 begin
1484 l_cleanup_repeat_interval := 1;
1485 l_cleanup_app_short_name := 'FND';
1486 l_cleanup_program := 'FNDLGPRG';
1487
1488 -- see if the cleanup process is already there
1489
1490 l_request_status_return := fnd_concurrent.get_request_status(
1491 request_id => l_request_id,
1492 appl_shortname => l_cleanup_app_short_name,
1493 program => l_cleanup_program,
1494 phase => l_phase,
1495 status => l_status,
1496 dev_phase => l_dev_phase,
1497 dev_status => l_dev_status,
1498 message => l_message);
1499
1500 if(l_request_id is null or l_status = 'Cancelled') then
1501 -- Submit the Request with repeating option
1502
1503 apps_initialize();
1504
1505 l_repeat_options_return := fnd_request.set_repeat_options(
1506 repeat_interval => l_cleanup_repeat_interval,
1507 increment_dates => 'Y');
1508
1509 l_submit_request_return := fnd_request.submit_request(
1510 application => l_cleanup_app_short_name,
1511 program => l_cleanup_program,
1512 argument1 => FND_DATE.date_to_canonical(sysdate-7));
1513 fdebug('Submitted id=' || l_submit_request_return);
1514 commit;
1515 else
1516 -- the cleanup request has already been submitted so no action is required
1517 fdebug('Already pending id=' || l_request_id ||
1518 '; status=' || l_status || '; dev_status=' || l_dev_status);
1519 end if;
1520 end start_purge_cp;
1521
1522 function self_test return varchar2 is
1523 rows number;
1524 result varchar2(2000) := '';
1525 test_date varchar2(255) := '25-'||'MAY-'||'1970';
1526 test_mask varchar2(255) := 'DD'||'-MON-'||'RRRR';
1527 begin
1528 result := result
1529 || 'If successful, the following will be a string of all 1s:';
1530
1531 delete from fnd_log_messages where user_id = 62202999;
1532
1533 insert into fnd_log_messages
1534 (module, log_level, message_text, session_id, user_id,
1535 timestamp, log_sequence)
1536 values
1537 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
1538 'This is a test log message', 62202999, 62202999,
1539 to_date(test_date, test_mask), 62202999);
1540
1541 rows := fnd_log_admin.delete_by_user(62202999);
1542 result := result || rows ;
1543
1544
1545
1546 delete from fnd_log_messages where user_id = 62202999;
1547
1548 insert into fnd_log_messages
1549 (module, log_level, message_text, session_id, user_id,
1550 timestamp, log_sequence)
1551 values
1552 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
1553 'This is a test log message', 62202999, 62202999,
1554 to_date(test_date, test_mask), 62202999);
1555
1556 rows := fnd_log_admin.delete_by_session(62202999);
1557 result := result || rows ;
1558
1559
1560
1561
1562 delete from fnd_log_messages where user_id = 62202999;
1563
1564 insert into fnd_log_messages
1565 (module, log_level, message_text, session_id, user_id,
1566 timestamp, log_sequence)
1567 values
1568 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
1569 'This is a test log message', 62202999, 62202999,
1570 to_date(SYSDATE+500, test_mask), 62202999);
1571
1572 /* Dangerous so not doing this test */
1573 -- rows := fnd_log_admin.delete_by_date_range(SYSDATE+499,NULL);
1574 -- result := result || rows ;
1575
1576
1577
1578
1579 delete from fnd_log_messages where user_id = 62202999;
1580
1581 insert into fnd_log_messages
1582 (module, log_level, message_text, session_id, user_id,
1583 timestamp, log_sequence)
1584 values
1585 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
1586 'This is a test log message', 62202999, 62202999,
1587 to_date(SYSDATE+500, test_mask), 62202999);
1588
1589 rows := fnd_log_admin.delete_by_date_range(SYSDATE+499, SYSDATE+501);
1590 result := result || rows ;
1591
1592
1593
1594
1595 delete from fnd_log_messages where user_id = 62202999;
1596
1597 insert into fnd_log_messages
1598 (module, log_level, message_text, session_id, user_id,
1599 timestamp, log_sequence)
1600 values
1601 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
1602 'This is a test log message', 62202999, 62202999,
1603 to_date(SYSDATE+500, test_mask), 62202999);
1604
1605 /* Not doing this test because it's destructive */
1606 -- rows := fnd_log_admin.delete_by_date_range(NULL,SYSDATE+501);
1607 -- result := result || rows ;
1608
1609
1610
1611
1612 delete from fnd_log_messages where user_id = 62202999;
1613
1614 insert into fnd_log_messages
1615 (module, log_level, message_text, session_id, user_id,
1616 timestamp, log_sequence)
1617 values
1618 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 1,
1619 'This is a test log message', 62202999, 62202999,
1620 to_date(test_date, test_mask), 62202999);
1621
1622 /* Not doing this test because it's destructive */
1623 -- rows := fnd_log_admin.delete_by_max_level(1);
1624 -- result := result || rows ;
1625
1626
1627
1628
1629 delete from fnd_log_messages where user_id = 62202999;
1630
1631 insert into fnd_log_messages
1632 (module, log_level, message_text, session_id, user_id,
1633 timestamp, log_sequence)
1634 values
1635 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
1636 'This is a test log message', 62202999, 62202999,
1637 to_date(test_date, test_mask), 62202999);
1638
1639 rows := fnd_log_admin.delete_by_user_session(62202999, 62202999);
1640 result := result || rows ;
1641
1642
1643
1644
1645 insert into fnd_log_messages
1646 (module, log_level, message_text, session_id, user_id,
1647 timestamp, log_sequence)
1648 values
1649 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
1650 'This is a test log message', 62202999, 62202999,
1651 to_date(test_date, test_mask), 62202999);
1652
1653 rows := fnd_log_admin.delete_by_module(
1654 'fnd.src.dict.afdict.afdwarn.tom_test_module');
1655 result := result || rows;
1656
1657
1658
1659 return result;
1660 end SELF_TEST;
1661
1662 end FND_LOG_ADMIN;