[Home] [Help]
PACKAGE BODY: APPS.FND_LOG_ADMIN
Source
1 package body fnd_log_admin as
2 /* $Header: AFUTLGAB.pls 120.2.12010000.2 2009/07/17 15:48:13 tshort 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 (X_START_DATE is null or flue.CREATION_DATE >= X_START_DATE)
848 and (X_END_DATE is null or 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 procedure DELETE_MESSAGES_INFO(p_logSeqList in LogSeqListTyp
858 , pRetCode out NOCOPY number, X_START_DATE IN DATE, X_END_DATE IN DATE)
859 IS
860 l_table VARCHAR2(25);
861 l_start NUMBER;
862 l_end NUMBER :=0;
863
864 BEGIN
865 fdebug('In:FND_LOG_ADMIN.DELETE_MESSAGES_INFO rec=' || p_logSeqList.count);
866 pRetCode := C_SUCCESS;
867
868 --Check input parameters
869 if (p_logSeqList is null) or (p_logSeqList.count < 1) then
870 return;
871 end if;
872
873 loop
874 l_start := l_end + 1;
875 l_end := getDeleteBlock(l_end, p_logSeqList.count);
876 exit when l_start > p_logSeqList.count;
877
878 begin
879
880 fdebug('Start Del FND_LOG_MESSAGES');
881 --still need date because transaction_context_id's aren't unique
882 FORALL ii IN l_start..l_end
883 delete from FND_LOG_MESSAGES flm where
884 flm.TRANSACTION_CONTEXT_ID=p_logSeqList(ii)
885 and (X_START_DATE is null or flm.TIMESTAMP >= X_START_DATE)
886 and (X_END_DATE is null or flm.TIMESTAMP <= X_END_DATE);
887
888 s_rows_deleted_fle := s_rows_deleted_fle + sql%rowcount;
889 doCommit;
890
891 EXCEPTION
892 when others then
893 fdebug('Failed in DELETE_MESSAGES_INFO');
894 raise;
895 pRetCode := C_WARNING;
896 end; ----begin
897
898 end loop;
899
900
901 fdebug('OUT:FND_LOG_ADMIN.DELETE_MESSAGES_INFO');
902
903 END DELETE_MESSAGES_INFO;
904
905 --------------------------------------------------------------------------------
906 --This function deletes those log messages whose transaction Context Id is
907 --doesn't exist in fnd_log_transaction_context but meets the input date range.
908
909 procedure DELETE_MESSAGES_INVALID_TRID(pRetCode out NOCOPY number
910 ,X_START_DATE IN DATE, X_END_DATE IN DATE )
911 IS
912 l_EXList LogSeqListTyp;
913 l_table VARCHAR2(25);
914 l_start NUMBER;
915 l_end NUMBER :=0;
916
917 BEGIN
918 fdebug('In:FND_LOG_ADMIN.DELETE_MESSAGES_INVALID_TRID');
919
920 pRetCode := C_SUCCESS;
921
922 --Check input parameters
923 select flm.TRANSACTION_CONTEXT_ID BULK COLLECT into l_EXList
924 from
925 FND_LOG_MESSAGES flm
926 where
927 not exists (select null from FND_LOG_TRANSACTION_CONTEXT fltc
928 where flm.TRANSACTION_CONTEXT_ID = fltc.TRANSACTION_CONTEXT_ID)
929 and (X_START_DATE is null or flm.TIMESTAMP >= X_START_DATE)
930 and (X_END_DATE is null or flm.TIMESTAMP <= X_END_DATE);
931
932 DELETE_MESSAGES_INFO(l_EXList ,pRetCode, X_START_DATE, X_END_DATE);
933 fdebug('Out:DELETE_MESSAGES_INVALID_TRID');
934
935 END DELETE_MESSAGES_INVALID_TRID;
936
937 --------------------------------------------------------------------------------
938 function DEL_METR_TRANS_INFO(p_TrList in TrnCtxIdListTyp
939 , pRetCode out NOCOPY number) return number
940 IS
941 l_table VARCHAR2(30);
942 l_start NUMBER;
943 l_end NUMBER :=0;
944 l_TrList TrnCtxIdListTyp;
945 l_retu NUMBER := 0;
946
947 BEGIN
948 fdebug('In:FND_BE_UTIL.DEL_METR_TRANS_INFO' || p_TrList.count);
949 pRetCode := C_SUCCESS;
950
951 --Check input parameters
952 if (p_TrList is null) or (p_TrList.count < 1) then
953 return l_retu;
954 end if;
955
956 loop
957 l_start := l_end + 1;
958 l_end := getDeleteBlock(l_end, p_TrList.count);
959 exit when l_start > p_TrList.count;
960
961 begin
962 fdebug('Start Del FND_LOG_METRICS');
963 l_table := 'FND_LOG_METRICS';
964
965 FORALL ii IN l_start..l_end
966 delete from FND_LOG_METRICS flm where flm.TRANSACTION_CONTEXT_ID=p_TrList(ii)
967 and NOT EXISTS
968 (select null from FND_LOG_EXCEPTIONS fle WHERE fle.TRANSACTION_CONTEXT_ID=flm.TRANSACTION_CONTEXT_ID);
969 s_rows_deleted_flmt := s_rows_deleted_flmt + sql%rowcount; --FND_LOG_METRICS
970 doCommit;
971
972
973 fdebug('Start Del FND_LOG_TRANSACTION_CONTEXT');
974 l_table := 'FND_LOG_TRANSACTION_CONTEXT';
975 FORALL ii IN l_start..l_end
976 delete from FND_LOG_TRANSACTION_CONTEXT where TRANSACTION_CONTEXT_ID=p_TrList(ii)
977 and NOT EXISTS
978 (select null from FND_LOG_EXCEPTIONS fle WHERE fle.TRANSACTION_CONTEXT_ID=p_TrList(ii))
979 and NOT EXISTS
980 (select null from FND_LOG_MESSAGES flm WHERE flm.TRANSACTION_CONTEXT_ID=p_TrList(ii));
981
982 l_retu := sql%rowcount;
983 s_rows_deleted_fltc := s_rows_deleted_fltc + sql%rowcount; --FND_LOG_TRANSACTION_CONTEXT
984 doCommit;
985 return l_retu;
986 fdebug('End Del FND_LOG_TRANSACTION_CONTEXT');
987
988 EXCEPTION
989 when others then
990 fdebug('Failed in DEL_METR_TRANS_INFO');
991 pRetCode := C_WARNING;
992 if ((sqlcode = 60) or (sqlcode = 4020)) then
993 null; /* Ignore rows that are deadlocked */
994 else
995 raise;
996 end if;
997
998 end; ----begin
999 end loop;
1000 fdebug('Out:FND_BE_UTIL.DEL_METR_TRANS_INFO');
1001 END DEL_METR_TRANS_INFO;
1002 --------------------------------------------------------------------------------
1003 function purgeTablesForLists(pLogSeqList in LogSeqListTyp, pUEXList in UExcIdListTyp
1004 , pTrnCtxIdList in TrnCtxIdListTyp, pRetCode out NOCOPY number) return NUMBER
1005 is
1006 l_retCode number;
1007 l_retu number := 0;
1008 begin
1009 fdebug('In purgeTablesForLists');
1010 fdebug('pTrnCtxIdList count=' || pTrnCtxIdList.count);
1011 fdebug('pLogSeqList count=' || pLogSeqList.count);
1012 fdebug('pUEXList count=' || pUEXList.count);
1013
1014 debugPrint(pTrnCtxIdList);
1015
1016 --Delete Messages and Exceptions
1017 --- debugPrint(l_LogSeqList);
1018 DELETE_EXCEPTIONS_INFO(pLogSeqList, l_retCode);
1019 upDateRetCode(pRetCode, l_retCode);
1020
1021 ---Delete UniqueExceptions
1022 --- debugPrint(l_UEXList);
1023 DELETE_UNIQUE_EXCEPTIONS_INFO(pUEXList, l_retCode);
1024 upDateRetCode(pRetCode, l_retCode);
1025
1026 ---Delete Transaction Info
1027 l_retu := DEL_METR_TRANS_INFO(pTrnCtxIdList, l_retCode);
1028 upDateRetCode(pRetCode, l_retCode);
1029 fdebug('Out purgeTablesForLists');
1030 return l_retu;
1031 end purgeTablesForLists;
1032
1033 function purge(pTrCursor in out NOCOPY GenCursor, pRetCode out NOCOPY number) return NUMBER is
1034 l_LogSeqList LogSeqListTyp;
1035 l_UEXList UExcIdListTyp;
1036 l_LogSeqList1 LogSeqListTyp;
1037 l_UEXList1 UExcIdListTyp;
1038
1039 l_LogSeqListNull LogSeqListTyp;
1040 l_UEXListNull UExcIdListTyp;
1041
1042 l_TrnCtxIdList TrnCtxIdListTyp;
1043 l_TrnCtxIdListNull TrnCtxIdListTyp;
1044 ii number;
1045 l_retCode number;
1046 l_retu number := 0;
1047 begin
1048 fdebug('In purge');
1049 pRetCode := C_SUCCESS;
1050 if ((pTrCursor is null) or (pTrCursor%ISOPEN = false)) then
1051 return l_retu;
1052 end if;
1053
1054 ii := 1;
1055 fdebug('Start creating log seq and exception list');
1056 LOOP
1057 FETCH pTrCursor INTO l_TrnCtxIdList(ii);
1058 EXIT WHEN pTrCursor%NOTFOUND;
1059 --fdebug('l_TrnCtxIdList(ii)=' || l_TrnCtxIdList(ii));
1060
1061
1062 --Collect l_UEXList
1063 l_UEXList1 := l_UEXListNull;
1064 select distinct flue.UNIQUE_EXCEPTION_ID BULK COLLECT into l_UEXList1
1065 from
1066 FND_LOG_EXCEPTIONS fle
1067 , FND_LOG_UNIQUE_EXCEPTIONS flue
1068 where
1069 fle.TRANSACTION_CONTEXT_ID = l_TrnCtxIdList(ii)
1070 and flue.UNIQUE_EXCEPTION_ID = fle.UNIQUE_EXCEPTION_ID ---
1071 and flue.STATUS = 'C';
1072
1073 ---fdebug('l_UEXList1.count=' || l_UEXList1.count);
1074 mergelist(l_UEXList, l_UEXList1);
1075
1076
1077 --Collect LogSeq
1078 l_LogSeqList1 := l_LogSeqListNull;
1079 select LOG_SEQUENCE BULK COLLECT into l_LogSeqList1
1080 from
1081 (
1082 select LOG_SEQUENCE from FND_LOG_MESSAGES flm
1083 where flm.TRANSACTION_CONTEXT_ID = l_TrnCtxIdList(ii)
1084 union
1085 select LOG_SEQUENCE from FND_LOG_EXCEPTIONS fle
1086 where fle.TRANSACTION_CONTEXT_ID = l_TrnCtxIdList(ii)
1087 and NOT EXISTS(select null from FND_LOG_MESSAGES flm1 where flm1.LOG_SEQUENCE=fle.LOG_SEQUENCE)
1088 );
1089 ---fdebug('l_LogSeqList1.count=' || l_LogSeqList1.count);
1090 mergelist(l_LogSeqList, l_LogSeqList1);
1091
1092 --Check if any list has more than MAX_LIST_COUNT Data. If Yes Delete All
1093 if (
1094 (l_LogSeqList.count >= MAX_LIST_COUNT)
1095 OR (l_UEXList.count >= MAX_LIST_COUNT)
1096 OR(l_TrnCtxIdList.count >= MAX_LIST_COUNT)
1097 ) then
1098 l_retu := l_retu + purgeTablesForLists(l_LogSeqList, l_UEXList, l_TrnCtxIdList, l_retCode);
1099 upDateRetCode(pRetCode, l_retCode);
1100 l_LogSeqList := l_LogSeqListNull;
1101 l_UEXList := l_UEXListNull;
1102 l_TrnCtxIdList := l_TrnCtxIdListNull;
1103 ii := 0;
1104 end if;
1105 ii := ii + 1;
1106 END LOOP;
1107 fdebug('ii=' || ii);
1108 fdebug('End creating log seq and exception list');
1109 l_retu := l_retu + purgeTablesForLists(l_LogSeqList, l_UEXList, l_TrnCtxIdList, l_retCode);
1110 upDateRetCode(pRetCode, l_retCode);
1111
1112 close pTrCursor;
1113 commit;
1114 fdebug('Out purge');
1115 return l_retu;
1116 end purge;
1117 --------------------------------------------------------------------------------
1118
1119 procedure getDebugTrType(pList in out NOCOPY VARCAHRSmallListTyp)
1120 is
1121 begin
1122 pList(1) := 'Request';
1123 pList(2) := 'Service';
1124 pList(3) := 'Form';
1125 pList(4) := 'ICX';
1126 pList(5) := 'Unknown';
1127 end;
1128
1129
1130
1131 --------------------------------------------------------------------------------
1132 --Specification APIS
1133 /******************************************************************************/
1134 /******************************************************************************/
1135 function delete_by_date_range(
1136 X_START_DATE IN DATE ,
1137 X_END_DATE IN DATE ) return NUMBER is
1138 rowcount number := 0;
1139 l_retCode number;
1140
1141 l_sqlList VARCAHRListTyp;
1142 l_debugList VARCAHRSmallListTyp;
1143 l_GenCur GenCursor;
1144
1145 l_criteria NUMBER;
1146 l_param1 DATE;
1147 l_sessionId NUMBER;
1148 begin
1149 fdebug ('In - delete_by_date_range -calling old');
1150 init;
1151 -- printCount;
1152 s_rows_deleted_flm := delete_by_date_range_pre1159(X_START_DATE, X_END_DATE);
1153
1154 if((X_START_DATE is NULL) and (X_END_DATE is NULL)) then
1155 return delete_all;
1156 end if;
1157
1158 if (X_START_DATE is NULL) then
1159 l_criteria := C_PURGE_CRITERIA_END_DATE;
1160 l_param1 := X_END_DATE;
1161 elsif (X_END_DATE is NULL) then
1162 l_criteria := C_PURGE_CRITERIA_START_DATE;
1163 l_param1 := X_START_DATE;
1164 else
1165 l_criteria := C_PURGE_CRITERIA_RANGE_DATE;
1166 end if;
1167
1168 l_sqlList(1) := getTrSQL(C_TR_REQUEST_TYPE, l_criteria);
1169 l_sqlList(2) := getTrSQL(C_TR_SERVICE_TYPE, l_criteria);
1170 l_sqlList(3) := getTrSQL(C_TR_FORM_TYPE, l_criteria);
1171 l_sqlList(4) := getTrSQL(C_TR_ICX_TYPE, l_criteria);
1172 l_sqlList(5) := getTrSQL(C_TR_UNKNOWN_TYPE, l_criteria);
1173
1174 getDebugTrType(l_debugList);
1175
1176 for ii in l_sqlList.FIRST..l_sqlList.LAST loop
1177 if (l_criteria = C_PURGE_CRITERIA_RANGE_DATE) then
1178 open l_GenCur for l_sqlList(ii) using X_START_DATE, X_END_DATE;
1179 else
1180 open l_GenCur for l_sqlList(ii) using l_param1;
1181 end if;
1182 fdebug ('Purging - ' || l_debugList(ii));
1183 rowcount := rowcount + purge(l_GenCur, l_retCode);
1184 fdebug ('total rows - ' || rowcount);
1185 end loop;
1186
1187 --Delete the exceptions whose transaction context id is null.
1188 DELETE_EXCEPTIONS_NULL_TRID(l_retCode, X_START_DATE, X_END_DATE);
1189 commit;
1190
1191 --Delete abondoned attachements
1192 delete from FND_LOG_ATTACHMENTS fla where NOT EXISTS
1193 (select null from FND_LOG_EXCEPTIONS fle where fle.LOG_SEQUENCE = fla.LOG_SEQUENCE)
1194 and NOT EXISTS (select null from FND_LOG_MESSAGES flm where flm.LOG_SEQUENCE = fla.LOG_SEQUENCE );
1195 commit;
1196
1197 --delete the abondoned unique exceptions;
1198 DELETE_UNIQUEA_EXCEPTIONS_INFO(l_retCode);
1199 commit;
1200
1201 --Delete the messages whose transaction context id doesn't exist in
1202 --fnd_log_transaction_context.
1203 DELETE_MESSAGES_INVALID_TRID(l_retCode, X_START_DATE, X_END_DATE);
1204 commit;
1205
1206 printCount;
1207 fdebug ('Out - delete_by_date_range');
1208 return rowcount;
1209 exception
1210 when others then
1211 fdebug ('in error delete_by_date_range: ' || SQLCODE);
1212 if l_GenCur %ISOPEN then close l_GenCur; end if;
1213 raise;
1214 end delete_by_date_range;
1215
1216 --------------------------------------------------------------------------------
1217 function delete_by_user(
1218 X_USER_ID IN VARCHAR2 ) return NUMBER is
1219 rowcount number := 0;
1220 l_retCode number;
1221
1222 l_sqlList VARCAHRListTyp;
1223 l_debugList VARCAHRSmallListTyp;
1224 l_GenCur GenCursor;
1225 l_userid NUMBER;
1226 begin
1227 fdebug ('In - delete_by_user');
1228 init;
1229 s_rows_deleted_flm := delete_by_user_pre1159(X_USER_ID);
1230 rowcount := 0;
1231
1232 if ( X_USER_ID is null ) then
1233 return rowcount;
1234 end if;
1235
1236 l_userid := to_number(X_USER_ID);
1237
1238 C_DEBUG := true;
1239 fdebug ('getPurgeSQLS - Request');
1240 l_sqlList(1) := getTrSQL(C_TR_REQUEST_TYPE, C_PURGE_CRITERIA_USER);
1241 l_sqlList(2) := getTrSQL(C_TR_SERVICE_TYPE, C_PURGE_CRITERIA_USER);
1242 l_sqlList(3) := getTrSQL(C_TR_FORM_TYPE, C_PURGE_CRITERIA_USER);
1243 l_sqlList(4) := getTrSQL(C_TR_ICX_TYPE, C_PURGE_CRITERIA_USER);
1244 l_sqlList(5) := getTrSQL(C_TR_UNKNOWN_TYPE, C_PURGE_CRITERIA_USER);
1245
1246 getDebugTrType(l_debugList);
1247
1248
1249 for ii in l_sqlList.FIRST..l_sqlList.LAST loop
1250 open l_GenCur for l_sqlList(ii) using l_userid;
1251 fdebug ('Purging - ' || l_debugList(ii));
1252 rowcount := rowcount + purge(l_GenCur, l_retCode);
1253 end loop;
1254
1255 printCount;
1256 fdebug ('Out - delete_by_user');
1257
1258 commit;
1259 return rowcount;
1260 exception
1261 when others then
1262 fdebug ('in error' || SQLCODE);
1263 if l_GenCur %ISOPEN then close l_GenCur; end if;
1264 raise;
1265 end delete_by_user;
1266
1267
1268 --------------------------------------------------------------------------------
1269 function delete_by_session(
1270 X_SESSION_ID IN VARCHAR2 ) return NUMBER is
1271 rowcount number := 0;
1272 l_retCode number;
1273
1274 l_sqlList VARCAHRListTyp;
1275 l_debugList VARCAHRSmallListTyp;
1276 l_GenCur GenCursor;
1277 l_sessionId NUMBER;
1278 begin
1279 fdebug ('In - delete_by_session');
1280 init;
1281 s_rows_deleted_flm := delete_by_session_pre1159(X_SESSION_ID);
1282 rowcount := 0;
1283
1284 if ( X_SESSION_ID is null ) then
1285 return rowcount;
1286 end if;
1287
1288 l_sessionId := to_number(X_SESSION_ID);
1289
1290 l_sqlList(1) := getTrSQL(C_TR_REQUEST_TYPE, C_PURGE_CRITERIA_SESSION);
1291 l_sqlList(2) := getTrSQL(C_TR_SERVICE_TYPE, C_PURGE_CRITERIA_SESSION);
1292 l_sqlList(3) := getTrSQL(C_TR_FORM_TYPE, C_PURGE_CRITERIA_SESSION);
1293 l_sqlList(4) := getTrSQL(C_TR_ICX_TYPE, C_PURGE_CRITERIA_SESSION);
1294 l_sqlList(5) := getTrSQL(C_TR_UNKNOWN_TYPE, C_PURGE_CRITERIA_SESSION);
1295
1296 getDebugTrType(l_debugList);
1297
1298 for ii in l_sqlList.FIRST..l_sqlList.LAST loop
1299 open l_GenCur for l_sqlList(ii) using l_sessionId;
1300 fdebug ('Purging - ' || l_debugList(ii));
1301 rowcount := rowcount + purge(l_GenCur, l_retCode);
1302 end loop;
1303
1304 printCount;
1305 fdebug ('Out - delete_by_session');
1306 commit;
1307 return rowcount;
1308 exception
1309 when others then
1310 fdebug ('in error delete_by_session: ' || SQLCODE);
1311 if l_GenCur %ISOPEN then close l_GenCur; end if;
1312 raise;
1313 end delete_by_session;
1314
1315 --------------------------------------------------------------------------------
1316 function delete_by_user_session(
1317 X_USER_ID IN VARCHAR2,
1318 X_SESSION_ID IN VARCHAR2 ) return NUMBER is
1319 rowcount number := 0;
1320 l_retCode number;
1321
1322 l_sqlList VARCAHRListTyp;
1323 l_debugList VARCAHRSmallListTyp;
1324 l_GenCur GenCursor;
1325
1326 l_userId NUMBER;
1327 l_sessionId NUMBER;
1328 begin
1329 fdebug ('In - delete_by_user_session');
1330 init;
1331 s_rows_deleted_flm := delete_by_user_session_pre1159(X_USER_ID, X_SESSION_ID);
1332 rowcount := 0;
1333
1334 if (X_USER_ID is null ) or ( X_SESSION_ID is null ) then
1335 return rowcount;
1336 end if;
1337
1338 l_userId := to_number(X_USER_ID);
1339 l_sessionId := to_number(X_SESSION_ID);
1340
1341 l_sqlList(1) := getTrSQL(C_TR_REQUEST_TYPE, C_PURGE_CRITERIA_USER_SESSION);
1342 l_sqlList(2) := getTrSQL(C_TR_SERVICE_TYPE, C_PURGE_CRITERIA_USER_SESSION);
1343 l_sqlList(3) := getTrSQL(C_TR_FORM_TYPE, C_PURGE_CRITERIA_USER_SESSION);
1344 l_sqlList(4) := getTrSQL(C_TR_ICX_TYPE, C_PURGE_CRITERIA_USER_SESSION);
1345 l_sqlList(5) := getTrSQL(C_TR_UNKNOWN_TYPE, C_PURGE_CRITERIA_USER_SESSION);
1346
1347 getDebugTrType(l_debugList);
1348
1349 for ii in l_sqlList.FIRST..l_sqlList.LAST loop
1350 open l_GenCur for l_sqlList(ii) using l_userId, l_sessionId;
1351 fdebug ('Purging - ' || l_debugList(ii));
1352 rowcount := rowcount + purge(l_GenCur, l_retCode);
1353 end loop;
1354
1355 printCount;
1356 fdebug ('Out - delete_by_user_session');
1357 commit;
1358 return rowcount;
1359 exception
1360 when others then
1361 fdebug ('in error delete_by_user_session: ' || SQLCODE);
1362 if l_GenCur %ISOPEN then close l_GenCur; end if;
1363 raise;
1364 end delete_by_user_session;
1365
1366 --------------------------------------------------------------------------------
1367 --function delete_by_max_level Use old API
1368 --------------------------------------------------------------------------------
1369 function delete_all return NUMBER is
1370 rowcount number := 0;
1371 l_retCode number;
1372
1373 l_sqlList VARCAHRListTyp;
1374 l_debugList VARCAHRSmallListTyp;
1375 l_GenCur GenCursor;
1376 begin
1377 fdebug ('In - delete_all');
1378 init;
1379 s_rows_deleted_flm := delete_all_pre1159;
1380 rowcount := 0;
1381
1382 l_sqlList(1) := getTrSQL(C_TR_REQUEST_TYPE, C_PURGE_CRITERIA_ALL);
1383 l_sqlList(2) := getTrSQL(C_TR_SERVICE_TYPE, C_PURGE_CRITERIA_ALL);
1384 l_sqlList(3) := getTrSQL(C_TR_FORM_TYPE, C_PURGE_CRITERIA_ALL);
1385 l_sqlList(4) := getTrSQL(C_TR_ICX_TYPE, C_PURGE_CRITERIA_ALL);
1386 l_sqlList(5) := getTrSQL(C_TR_UNKNOWN_TYPE, C_PURGE_CRITERIA_ALL);
1387
1388 getDebugTrType(l_debugList);
1389
1390 for ii in l_sqlList.FIRST..l_sqlList.LAST loop
1391 open l_GenCur for l_sqlList(ii);
1392 fdebug ('Purging - ' || l_debugList(ii));
1393 rowcount := rowcount + purge(l_GenCur, l_retCode);
1394 end loop;
1395
1396
1397 printCount;
1398 fdebug ('Out - delete_all');
1399 commit;
1400 return rowcount;
1401 exception
1402 when others then
1403 fdebug ('in error delete_all: ' || SQLCODE);
1404 if l_GenCur %ISOPEN then close l_GenCur; end if;
1405 raise;
1406 end delete_all;
1407 ---------------------------------------------------------------------------------------------------------
1408
1409 -------------------------------------------------------------------------
1410 /** Concurrent Program ********************************************************/
1411
1412 procedure delete_by_date_i( errbuf out NOCOPY varchar2,
1413 retcode out NOCOPY varchar2,
1414 last_date in varchar2 ) is
1415 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_BY_DATE_I';
1416 numrows NUMBER;
1417 msgbuf varchar2(2000);
1418 last_dt DATE;
1419 begin
1420
1421 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1422 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1423 c_log_head || l_api_name || '.begin',
1424 c_pkg_name || '.' ||l_api_name ||'(' ||
1425 'last_date=>'|| last_date||');');
1426 end if;
1427
1428 /* Convert character string to date */
1429 if(last_date is NULL) then
1430 last_dt := NULL; /* NULL means for all dates */
1431 else
1432 last_dt := FND_CONC_DATE.STRING_TO_DATE(last_date);
1433 if(last_dt is NULL) then
1434 errbuf := 'Unexpected error converting character string to date:'
1435 ||last_date;
1436 retcode := '2';
1437 FND_FILE.put_line(FND_FILE.log,errbuf);
1438 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1439 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1440 c_log_head || l_api_name || '.end_exception',
1441 'returning from delete_by_date with date exception. last_date='
1442 ||last_date);
1443 end if;
1444 return;
1445 end if;
1446 end if;
1447
1448 fnd_message.set_name('FND', 'PURGING_UP_TO_DATE');
1449 fnd_message.set_token('ENTITY', 'FND_LOG_MESSAGES');
1450 if (last_date is NULL) then
1451 fnd_message.set_token('DATE', 'WF_ALL', TRUE);
1452 else
1453 fnd_message.set_token('DATE', last_date);
1454 end if;
1455 msgbuf := fnd_message.get;
1456 FND_FILE.put_line(FND_FILE.log, msgbuf);
1457
1458 /* Delete from the date back in time */
1459 numrows := delete_by_date_range(NULL, last_dt);
1460
1461 fnd_message.set_name('FND', 'GENERIC_ROWS_PROCESSED');
1462 fnd_message.set_token('ROWS', numrows);
1463 msgbuf := fnd_message.get;
1464 FND_FILE.put_line(FND_FILE.log, msgbuf);
1465
1466 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1467 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1468 c_log_head || l_api_name || '.end',
1469 'returning from delete_by_date_i. numrows='||numrows);
1470 end if;
1471 exception
1472 when others then
1473 errbuf := sqlerrm;
1474 retcode := '2';
1475 FND_FILE.put_line(FND_FILE.log,errbuf);
1476 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1477 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1478 c_log_head || l_api_name || '.end_exception',
1479 'returning from delete_by_date with exception. numrows='||numrows);
1480 end if;
1481 raise;
1482 end delete_by_date_i;
1483
1484
1485
1486
1487 /* Delete data from fnd_log_messages only - Used by Java UI */
1488
1489 function delete_by_sequence(
1490 pLogSeqList IN VARCHAR2) return NUMBER is
1491 begin
1492 fdebug ('In - delete_by_Sequence');
1493
1494 if ( pLogSeqList is null ) then
1495 return 0;
1496 end if;
1497
1498 delete from FND_LOG_MESSAGES flm
1499 where
1500 flm.LOG_SEQUENCE = pLogSeqList;
1501 fdebug ('Out - delete_by_Sequence');
1502 commit;
1503 return 1;
1504 end delete_by_sequence;
1505
1506 ------------------------------------------------------------------------------------------------
1507 /* Delet data from fnd_log_messages only - Used by Java UI */
1508 function delete_by_seqarray(numArrayList IN FND_ARRAY_OF_NUMBER_25) return NUMBER is
1509 ii number := 0;
1510 begin
1511 fdebug ('In - delete_by_seqarray');
1512 for ii in numArrayList.FIRST..numArrayList.LAST loop
1513 delete from
1514 FND_LOG_MESSAGES flm where flm.LOG_SEQUENCE = numArrayList(ii);
1515 END LOOP;
1516 commit;
1517 fdebug ('Out - delete_by_seqarray, deleted ' || numArrayList.COUNT || ' rows');
1518 return numArrayList.COUNT;
1519 end delete_by_seqarray;
1520 --------------------------------------------------------------------------------------------
1521
1522 --------------------------------------------------------------------------------
1523 -- Initializes the apps context to SYSADMIN.
1524 --------------------------------------------------------------------------------
1525 procedure apps_initialize
1526 is
1527 l_user_id number;
1528 l_resp_id number;
1529 l_resp_appl_id number;
1530 begin
1531 select u.user_id
1532 into l_user_id
1533 from fnd_user u
1534 where u.user_name = 'SYSADMIN';
1535
1536 select r.application_id,
1537 r.responsibility_id
1538 into l_resp_appl_id,
1539 l_resp_id
1540 from fnd_application a,
1541 fnd_responsibility r
1542 where r.application_id = a.application_id
1543 and a.application_short_name = 'SYSADMIN'
1544 and r.responsibility_key = 'SYSTEM_ADMINISTRATOR';
1545
1546 fnd_global.apps_initialize(user_id => l_user_id,
1547 resp_id => l_resp_id,
1548 resp_appl_id => l_resp_appl_id);
1549 end apps_initialize;
1550
1551 /* Checks if the 'Purge Debug Log' CP is running, and submits
1552 it if its not. Called by aflogcustart.sql */
1553 procedure start_purge_cp is
1554 l_request_id number;
1555 l_phase varchar2(30);
1556 l_status varchar2(30);
1557 l_dev_phase varchar2(30);
1558 l_dev_status varchar2(30);
1559 l_message varchar2(2000);
1560 l_request_status_return boolean;
1561 l_repeat_options_return boolean;
1562 l_submit_request_return number;
1563
1564 l_cleanup_repeat_interval number;
1565 l_cleanup_app_short_name varchar2(50);
1566 l_cleanup_program varchar2(30);
1567 begin
1568 l_cleanup_repeat_interval := 1;
1569 l_cleanup_app_short_name := 'FND';
1570 l_cleanup_program := 'FNDLGPRG';
1571
1572 -- see if the cleanup process is already there
1573
1574 l_request_status_return := fnd_concurrent.get_request_status(
1575 request_id => l_request_id,
1576 appl_shortname => l_cleanup_app_short_name,
1577 program => l_cleanup_program,
1578 phase => l_phase,
1579 status => l_status,
1580 dev_phase => l_dev_phase,
1581 dev_status => l_dev_status,
1582 message => l_message);
1583
1584 if(l_request_id is null or l_status = 'Cancelled') then
1585 -- Submit the Request with repeating option
1586
1587 apps_initialize();
1588
1589 l_repeat_options_return := fnd_request.set_repeat_options(
1590 repeat_interval => l_cleanup_repeat_interval,
1591 increment_dates => 'Y');
1592
1593 l_submit_request_return := fnd_request.submit_request(
1594 application => l_cleanup_app_short_name,
1595 program => l_cleanup_program,
1596 argument1 => FND_DATE.date_to_canonical(sysdate-7));
1597 fdebug('Submitted id=' || l_submit_request_return);
1598 commit;
1599 else
1600 -- the cleanup request has already been submitted so no action is required
1601 fdebug('Already pending id=' || l_request_id ||
1602 '; status=' || l_status || '; dev_status=' || l_dev_status);
1603 end if;
1604 end start_purge_cp;
1605
1606 function self_test return varchar2 is
1607 rows number;
1608 result varchar2(2000) := '';
1609 test_date varchar2(255) := '25-'||'MAY-'||'1970';
1610 test_mask varchar2(255) := 'DD'||'-MON-'||'RRRR';
1611 begin
1612 result := result
1613 || 'If successful, the following will be a string of all 1s:';
1614
1615 delete from fnd_log_messages where user_id = 62202999;
1616
1617 insert into fnd_log_messages
1618 (module, log_level, message_text, session_id, user_id,
1619 timestamp, log_sequence)
1620 values
1621 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
1622 'This is a test log message', 62202999, 62202999,
1623 to_date(test_date, test_mask), 62202999);
1624
1625 rows := fnd_log_admin.delete_by_user(62202999);
1626 result := result || rows ;
1627
1628
1629
1630 delete from fnd_log_messages where user_id = 62202999;
1631
1632 insert into fnd_log_messages
1633 (module, log_level, message_text, session_id, user_id,
1634 timestamp, log_sequence)
1635 values
1636 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
1637 'This is a test log message', 62202999, 62202999,
1638 to_date(test_date, test_mask), 62202999);
1639
1640 rows := fnd_log_admin.delete_by_session(62202999);
1641 result := result || rows ;
1642
1643
1644
1645
1646 delete from fnd_log_messages where user_id = 62202999;
1647
1648 insert into fnd_log_messages
1649 (module, log_level, message_text, session_id, user_id,
1650 timestamp, log_sequence)
1651 values
1652 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
1653 'This is a test log message', 62202999, 62202999,
1654 to_date(SYSDATE+500, test_mask), 62202999);
1655
1656 /* Dangerous so not doing this test */
1657 -- rows := fnd_log_admin.delete_by_date_range(SYSDATE+499,NULL);
1658 -- result := result || rows ;
1659
1660
1661
1662
1663 delete from fnd_log_messages where user_id = 62202999;
1664
1665 insert into fnd_log_messages
1666 (module, log_level, message_text, session_id, user_id,
1667 timestamp, log_sequence)
1668 values
1669 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
1670 'This is a test log message', 62202999, 62202999,
1671 to_date(SYSDATE+500, test_mask), 62202999);
1672
1673 rows := fnd_log_admin.delete_by_date_range(SYSDATE+499, SYSDATE+501);
1674 result := result || rows ;
1675
1676
1677
1678
1679 delete from fnd_log_messages where user_id = 62202999;
1680
1681 insert into fnd_log_messages
1682 (module, log_level, message_text, session_id, user_id,
1683 timestamp, log_sequence)
1684 values
1685 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
1686 'This is a test log message', 62202999, 62202999,
1687 to_date(SYSDATE+500, test_mask), 62202999);
1688
1689 /* Not doing this test because it's destructive */
1690 -- rows := fnd_log_admin.delete_by_date_range(NULL,SYSDATE+501);
1691 -- result := result || rows ;
1692
1693
1694
1695
1696 delete from fnd_log_messages where user_id = 62202999;
1697
1698 insert into fnd_log_messages
1699 (module, log_level, message_text, session_id, user_id,
1700 timestamp, log_sequence)
1701 values
1702 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 1,
1703 'This is a test log message', 62202999, 62202999,
1704 to_date(test_date, test_mask), 62202999);
1705
1706 /* Not doing this test because it's destructive */
1707 -- rows := fnd_log_admin.delete_by_max_level(1);
1708 -- result := result || rows ;
1709
1710
1711
1712
1713 delete from fnd_log_messages where user_id = 62202999;
1714
1715 insert into fnd_log_messages
1716 (module, log_level, message_text, session_id, user_id,
1717 timestamp, log_sequence)
1718 values
1719 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
1720 'This is a test log message', 62202999, 62202999,
1721 to_date(test_date, test_mask), 62202999);
1722
1723 rows := fnd_log_admin.delete_by_user_session(62202999, 62202999);
1724 result := result || rows ;
1725
1726
1727
1728
1729 insert into fnd_log_messages
1730 (module, log_level, message_text, session_id, user_id,
1731 timestamp, log_sequence)
1732 values
1733 ('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
1734 'This is a test log message', 62202999, 62202999,
1735 to_date(test_date, test_mask), 62202999);
1736
1737 rows := fnd_log_admin.delete_by_module(
1738 'fnd.src.dict.afdict.afdwarn.tom_test_module');
1739 result := result || rows;
1740
1741
1742
1743 return result;
1744 end SELF_TEST;
1745
1746 end FND_LOG_ADMIN;