1 PACKAGE BODY CS_KB_TRACKING_PKG AS
2 /* $Header: cskbtkb.pls 115.2 2003/11/18 01:04:38 allau noship $ */
3
4 PROCEDURE PURGE_TRACKING_HISTORY (ERRBUF OUT NOCOPY VARCHAR2,
5 RETCODE OUT NOCOPY VARCHAR2) IS
6
7
8
9 cursor get_session_attr_count is
10 select count(*)
11 from cs_kb_session_attrs b
12 where b.session_id in (
13 select a.session_id
14 from cs_kb_sessions a
15 where a.source_object_code like 'KMT%'
16 and (a.source_object_id not in
17 (select session_id from icx_sessions
18 where nvl(disabled_flag,'N') = 'N')));
19
20 cursor get_session_count is
21 select count(*)
22 from cs_kb_sessions a
23 where a.source_object_code like 'KMT%'
24 and (a.source_object_id not in
25 (select session_id from icx_sessions
26 where nvl(disabled_flag,'N') = 'N'));
27
28
29 l_session_attr_count number;
30 l_session_count number;
31
32 BEGIN
33
34 --ERRBUF = err messages
35 --RETCODE = 0=success, 1=warning, 2=error
36
37 -- FND_FILE.PUT_LINE(FND_FILE.LOG,
38 -- 'Starting Concurrent Program to purge tracking history at: '|| to_char(sysdate,'DD-MON-YY HH24:MI:SS'));
39
40 -- "Tracking history of invalid ICX session will be deleted."
41 FND_FILE.PUT_LINE(FND_FILE.LOG,
42 fnd_message.get_string('CS', 'CS_KB_DEL_TRACKING_DATA'));
43
44 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
45
46 -- LOGIC START
47
48 open get_session_attr_count;
49 fetch get_session_attr_count into l_session_attr_count;
50 close get_session_attr_count;
51
52 open get_session_count;
53 fetch get_session_count into l_session_count;
54 close get_session_count;
55
56 -- "Number of session attributes to be deleted:"
57 FND_FILE.PUT_LINE(FND_FILE.LOG,
58 fnd_message.get_string('CS', 'CS_KB_NUM_SES_ATTR_DEL') || l_session_attr_count);
59
60 -- "Number of sessions to be deleted:"
61 FND_FILE.PUT_LINE(FND_FILE.LOG,
62 fnd_message.get_string('CS', 'CS_KB_NUM_SES_DEL') || l_session_count);
63
64 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Purging data...');
65
66 delete cs_kb_session_attrs b
67 where b.session_id in (
68 select a.session_id
69 from cs_kb_sessions a
70 where a.source_object_code like 'KMT%'
71 and (a.source_object_id not in
72 (select session_id from icx_sessions
73 where nvl(disabled_flag,'N') = 'N')));
74
75 delete cs_kb_sessions a
76 where a.source_object_code like 'KMT%'
77 and (a.source_object_id not in
78 (select session_id from icx_sessions
79 where nvl(disabled_flag,'N') = 'N'));
80
81 open get_session_attr_count;
82 fetch get_session_attr_count into l_session_attr_count;
83 close get_session_attr_count;
84
85 open get_session_count;
86 fetch get_session_count into l_session_count;
87 close get_session_count;
88
89 -- "Number of session attributes to be deleted:"
90 FND_FILE.PUT_LINE(FND_FILE.LOG,
91 fnd_message.get_string('CS', 'CS_KB_NUM_SES_ATTR_DEL') || l_session_attr_count);
92
93 -- "Number of sessions to be deleted:"
94 FND_FILE.PUT_LINE(FND_FILE.LOG,
95 fnd_message.get_string('CS', 'CS_KB_NUM_SES_DEL') || l_session_count);
96
97 -- LOGIC END
98
99
100 -- FND_FILE.PUT_LINE(FND_FILE.LOG,
101 -- 'Finished Concurrent Program to purge tracking history at: '|| to_char(sysdate,'DD-MON-YY HH24:MI:SS'));
102
103 COMMIT;
104
105 ERRBUF := 'Success';
106 RETCODE := 0;
107
108 EXCEPTION
109
110 WHEN OTHERS THEN
111 RETCODE := 2;
112
113 -- "Execution failed due to unexpected error."
114 ERRBUF := fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||SQLERRM;
115 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
116
117 END PURGE_TRACKING_HISTORY;
118
119 END CS_KB_TRACKING_PKG;
[Home] [Help]