[Home] [Help]
PACKAGE BODY: APPS.IBW_PURGE_PVT
Source
1 PACKAGE BODY IBW_PURGE_PVT AS
2 /* $Header: IBWPURB.pls 120.7 2006/04/18 02:24 vekancha noship $*/
3
4
5 --
6 --
7 -- Start of Comments
8 --
9 -- NAME
10 -- IBW_PURGE_PVT
11 --
12 -- PURPOSE
13 -- Private API for purging transaction data
14 --
15 -- NOTES
16 -- Administrator uses this program to purge the transaction data. The tables which will be purged are IBW_PAGE_VIEWS,
17 -- IBW_SITE_VISITS, IBW_VISITORS, IBW_PAGE_INSTANCES.
18
19 -- HISTORY
20 -- 05/10/2005 VEKANCHA Created
21
22 -- **************************************************************************
23
24 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IBW_PURGE_PVT';
25 G_FILE_NAME CONSTANT VARCHAR2(12):= 'IBWPURB.pls';
26
27
28 -- ****************************************************************************
29 -- ****************************************************************************
30 -- PROCEDURES
31 -- 1. purge_data
32 -- 2. purge_statistics
33 -- ****************************************************************************
34 -- ****************************************************************************
35
36
37 -- ****************************************************************************
38 -- purges data
39 -- ****************************************************************************
40
41 PROCEDURE purge_data (
42 start_date IN DATE,
43 end_date IN DATE
44 )
45
46 IS
47
48 temp BOOLEAN;
49 visit_count NUMBER;
50 visitor_count NUMBER;
51 page_view_count NUMBER;
52
53 BEGIN
54
55 SELECT COUNT(distinct(visit_id)) into visit_count FROM ibw_site_visits
56 WHERE visit_start_time BETWEEN start_date AND end_date;
57
58 /** purge ibw_site_visits */
59 DELETE FROM ibw_site_visits
60 WHERE visit_start_time between start_date AND end_date;
61
62
63
64 SELECT count(page_view_id)into page_view_count FROM ibw_page_views pv
65 WHERE 0 = (SELECT COUNT(visit_id)
66 FROM ibw_site_visits v
67 WHERE v.visit_id=pv.visit_id);
68
69 /** purge ibw_page_views */
70 DELETE FROM ibw_page_views pv
71 WHERE 0 = (SELECT COUNT(visit_id)
72 FROM ibw_site_visits v
73 WHERE v.visit_id=pv.visit_id);
74
75 /** purge ibw_page_views_tmp */
76 DELETE FROM ibw_page_views_tmp pv
77 WHERE 0 = (SELECT COUNT(visit_id)
78 FROM ibw_site_visits v
79 WHERE v.visit_id=pv.visit_id);
80
81
82 SELECT count(visitor_id) into visitor_count FROM ibw_visitors visitors
83 WHERE 0 = (SELECT COUNT(visitor_id)
84 FROM ibw_site_visits visit
85 WHERE visit.visitor_id=visitors.visitor_id);
86
87 /** purge ibw_visitors */
88 DELETE FROM ibw_visitors visitors
89 WHERE 0 = (SELECT COUNT(visitor_id)
90 FROM ibw_site_visits visit
91 WHERE visit.visitor_id=visitors.visitor_id);
92
93 /** purge ibw_page_instances */
94 DELETE FROM ibw_page_instances pi
95 WHERE 0 = (SELECT COUNT(page_instance_id)
96 FROM ibw_page_views pv
97 WHERE pv.page_instance_id=pi.page_instance_id);
98
99
100
101 /** Store the end_date parameter of this purge request */
102 temp:=FND_PROFILE.SAVE('IBW_PURGE_LAST_ENDDATE',end_date,'SITE');
103
104 /** commit the changes */
105 COMMIT;
106 report_gen(visit_count, visitor_count, page_view_count,start_date, end_date, 'Y');
107
108 END purge_data;
109
110
111
112 PROCEDURE purge_statistics (
113 start_date IN DATE,
114 end_date IN DATE
115 )
116
117 IS
118 visit_count NUMBER;
119 visitor_count NUMBER;
120 page_view_count NUMBER;
121 page_instance_count NUMBER;
122
123 BEGIN
124
125 /** purge ibw_site_visits */
126 SELECT COUNT(distinct(site_visit_id)) cnt INTO visit_count
127 FROM ibw_site_visits
128 WHERE visit_start_time between start_date AND end_date;
129
130 /** purge ibw_page_views */
131 SELECT COUNT(page_view_id) INTO page_view_count
132 FROM ibw_page_views pv
133 WHERE visit_id IN (SELECT visit_id
134 FROM ibw_site_visits v
135 WHERE visit_start_time BETWEEN start_date AND end_date);
136
137 /** purge ibw_visitors */
138 SELECT COUNT(visitor_id) INTO visitor_count
139 FROM ibw_visitors visitors
140 WHERE 0= (SELECT count(visitor_id)
141 FROM ibw_site_visits visit
142 WHERE visit_start_time NOT BETWEEN start_date AND end_date);
143 report_gen(visit_count, visitor_count, page_view_count,start_date, end_date, 'N');
144
145 END purge_statistics;
146
147 PROCEDURE report_gen (
148 visit_count NUMBER,
149 visitor_count NUMBER,
150 page_view_count NUMBER,
151 start_date IN DATE,
152 end_date IN DATE,
153 execmode IN CHAR
154 )
155 IS
156 BEGIN
157
158 FND_MESSAGE.SET_NAME('IBW','IBW_TR_PUR_DATE');
159 FND_MESSAGE.SET_TOKEN('DATE', to_char(sysdate));
160 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
161
162 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
163
164 IF execmode = 'Y' THEN
165 FND_MESSAGE.SET_NAME('IBW','IBW_TR_PURGE_EXECMODE');
166 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
167 ELSE
168 FND_MESSAGE.SET_NAME('IBW','IBW_TR_PURGE_EVALMODE');
169 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
170 END IF;
171
172 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
173
174 FND_MESSAGE.SET_NAME('IBW','IBW_TR_PURGE_DATERANGE');
175 FND_MESSAGE.SET_TOKEN('START_DATE', to_char(start_date,'DD-MON-RRRR'));
176 FND_MESSAGE.SET_TOKEN('END_DATE', to_char(end_date,'DD-MON-RRRR'));
177 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
178
179 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
180
181 FND_MESSAGE.SET_NAME('IBW','IBW_TR_PURGE_REPORT');
182 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
183
184 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '===============================================');
185
186 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
187
188 FND_MESSAGE.SET_NAME('IBW','IBW_TR_PURGE_PAGEVIEWS');
189 FND_MESSAGE.SET_TOKEN('COUNT', to_char(page_view_count));
190 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
191
192 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
193
194 FND_MESSAGE.SET_NAME('IBW','IBW_TR_PURGE_VISITS');
195 FND_MESSAGE.SET_TOKEN('COUNT', to_char(visit_count));
196 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
197
198 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
199
200 FND_MESSAGE.SET_NAME('IBW','IBW_TR_PURGE_VISITORS');
201 FND_MESSAGE.SET_TOKEN('COUNT', to_char(visitor_count));
202 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
203 END report_gen;
204
205
206 PROCEDURE data_purge (
207 err_msg OUT NOCOPY VARCHAR2,
208 err_code OUT NOCOPY NUMBER,
209 start_date IN VARCHAR2,
210 end_date IN VARCHAR2,
211 exec_mode IN CHAR
212 )
213 IS
214
215 startDate VARCHAR2(100);
216 error_messages VARCHAR2(200);
217 sDate DATE;
218 eDate DATE;
219 INVALID_DATE EXCEPTION;
220
221 BEGIN
222
223 /** If start_date is null, then initialize it with the end_date of the previous run which is stored in
224 the profiles.
225 */
226 IF start_date=null THEN
227 FND_PROFILE.GET('IBW_PREVIOUS_PURGE_DATE',startDate);
228 ELSE
229 startDate:=start_date;
230 END IF;
231
232 /** If end_date is null, then exit the procedure
233 */
234 IF end_date=null THEN
235 FND_MESSAGE.SET_NAME('IBW','IBW_TR_PURGE_NULLDATE_ERR');
236 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
237 raise INVALID_DATE;
238 /** If start_date is greater than the end_date, then exit the procedure.
239 */
240 ELSIF startDate >= end_date THEN
241 FND_MESSAGE.SET_NAME('IBW','IBW_TR_PURGE_DATE_ERR');
242 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
243 raise INVALID_DATE;
244
245 ELSE
246 sDate := to_date(startDate, 'RRRR/MM/DD HH24:MI:SS');
247 eDate := to_date(end_date,'RRRR/MM/DD HH24:MI:SS');
248
249 IF exec_mode='Y' THEN
250 purge_data(sDate,eDate);
251 ELSE
252 purge_statistics(sDate, eDate);
253 END IF;
254 END IF;
255
256 EXCEPTION
257 WHEN OTHERS THEN
258 err_code := 2;
259 END data_purge;
260
261
262 PROCEDURE purge_oam (
263 start_date IN DATE,
264 end_date IN DATE
265 )
266 IS
267 cnt NUMBER;
268 temp_name varchar2(100);
269 startDate DATE;
270 error_messages VARCHAR2(100);
271
272 BEGIN
273
274 /** If start_date is null, then initialize it with the end_date of the previous run which is stored in
275 the profiles.
276 */
277 IF start_date=null THEN
278 FND_PROFILE.GET('IBW_PREVIOUS_PURGE_DATE',startDate);
279 ELSE
280 startDate:=start_date;
281 END IF;
282
283 /** If end_date is null, then exit the procedure
284 */
285 IF end_date=null THEN
286 error_messages := 'End date is null';
287
288
289 /** If start_date is greater than the end_date, then exit the procedure.
290 */
291 ELSIF startDate >= end_date THEN
292 error_messages := 'start date is greater than end date. ';
293
294 ELSE
295 /** purge ibw_site_visits */
296 SELECT COUNT(site_visit_id) INTO cnt
297 FROM ibw_site_visits
298 WHERE visit_start_time between startDate AND end_date;
299
300 temp_name := fnd_message.get_string('IBW', 'IBW_TR_PURGE_VISITS');
301 fnd_conc_summarizer.insert_row(temp_name, to_char(cnt));
302
303 /** purge ibw_page_views */
304 SELECT COUNT(page_view_id) INTO cnt
305 FROM ibw_page_views pv
306 WHERE 0 = (SELECT COUNT(visit_id)
307 FROM ibw_site_visits v
308 WHERE v.visit_id=pv.visit_id);
309
310 temp_name := fnd_message.get_string('IBW', 'IBW_TR_PURGE_PAGEVIEWS');
311 fnd_conc_summarizer.insert_row(temp_name, to_char(cnt));
312
313
314 /** purge ibw_visitors */
315 SELECT COUNT(visitor_id) INTO cnt
316 FROM ibw_visitors visitors
317 WHERE 0 = (SELECT COUNT(visitor_id)
318 FROM ibw_site_visits visit
319 WHERE visit.visitor_id=visitors.visitor_id);
320
321 temp_name := fnd_message.get_string('IBW', 'IBW_TR_PURGE_VISITORS');
322 fnd_conc_summarizer.insert_row(temp_name, to_char(cnt));
323
324
325 END IF;
326 END purge_oam;
327
328 END IBW_PURGE_PVT;