[Home] [Help]
PACKAGE BODY: APPS.ICX_CLEAN
Source
1 package body icx_clean as
2 /* $Header: ICXADCLB.pls 120.0 2005/10/07 11:47:16 gjimenez noship $ */
3
4 procedure tempTables is
5
6 begin
7
8 /* Delete rows older than 4 hours */
9
10 delete icx_text
11 where to_number(to_char(TIMESTAMP,'SSSSS'))
12 < to_number(to_char(SYSDATE,'SSSSS')) - (4 * 60 * 60);
13 commit;
14
15 /* Delete rows older than 12 hours */
16
17 delete icx_session_attributes
18 where session_id in
19 (select session_id
20 from icx_sessions
21 where to_number(to_char(CREATION_DATE,'SSSSS'))
22 < to_number(to_char(SYSDATE,'SSSSS')) - (12 * 60 * 60));
23 commit;
24
25 delete icx_sessions
26 where to_number(to_char(CREATION_DATE,'SSSSS'))
27 < to_number(to_char(SYSDATE,'SSSSS')) - (12 * 60 * 60);
28 commit;
29
30 delete icx_session_attributes
31 where session_id not in
32 (select session_id
33 from icx_sessions);
34 commit;
35
36 /* Delete rows older than 12 hours */
37
38 delete icx_transactions
39 where to_number(to_char(CREATION_DATE,'SSSSS'))
40 < to_number(to_char(SYSDATE,'SSSSS')) - (12 * 60 * 60);
41
42 /* Delete rows older than 30 days */
43
44 delete icx_failures
45 where to_number(to_char(CREATION_DATE,'J'))
46 < to_number(to_char(SYSDATE,'J')) - 30;
47 commit;
48
49 /* Delete rows older than 4 hours */
50
51 delete icx_context_results_temp
52 where to_number(to_char(DATESTAMP,'SSSSS'))
53 < to_number(to_char(SYSDATE,'SSSSS')) - (1 * 60 * 60);
54 commit;
55
56 /* Delete rows older than 12 hours. FND_SESSION_VALUES table should be
57 purged when ICX_SESSIONS table is purged. */
58
59 delete fnd_session_values
60 where to_number(to_char(TIMESTAMP,'SSSSS'))
61 < to_number(to_char(SYSDATE,'SSSSS')) - (12 * 60 * 60);
62 commit;
63
64 /* Uncomment this when DATESTAMP is added to cs_incidents_ctx_results
65 delete cs_incidents_ctx_results
66 where to_number(to_char(DATESTAMP,'SSSSS'))
67 < to_number(to_char(SYSDATE,'SSSSS')) - (1 * 60 * 60);
68 commit;
69 */
70
71 end;
72
73 end icx_clean;