1 package body fnd_soa_util as
2 /* $Header: FNDSOAUB.pls 120.4.12020000.5 2013/06/08 14:53:59 vshanmug ship $ */
3
4 /* Purge Function to delete all Data */
5 procedure delete_by_date_cp(errbuf out NOCOPY varchar2,
6 retcode out NOCOPY varchar2,
7 start_date in varchar2,
8 end_date in varchar2,
9 module in varchar2) is
10 v_strt_date DATE;
11 v_end_date DATE;
12 deleted_requests_count NUMBER;
13 deleted_response_count NUMBER;
14 deleted_method_count NUMBER;
15 deleted_att_count NUMBER;
16 deleted_error_count NUMBER;
17 deleted_log_count NUMBER;
18 v_direction varchar2(15);
19 begin
20 FND_FILE.put_line(FND_FILE.log, 'Start Date: '||start_date);
21 FND_FILE.put_line(FND_FILE.log, 'End Date: '||end_date);
22 FND_FILE.put_line(FND_FILE.log, 'Module: '||module);
23
24 v_strt_date := trunc(FND_CONC_DATE.STRING_TO_DATE(start_date),'DD');
25 v_end_date :=trunc( FND_CONC_DATE.STRING_TO_DATE(end_date),'DD');
26 v_end_date := v_end_date +1;
27 IF(v_strt_date is NULL) then
28 errbuf := 'Unexpected error converting character string to date:'
29 ||start_date;
30 retcode := '2';
31 FND_FILE.put_line(FND_FILE.log,errbuf);
32 RETURN;
33 END IF;
34 IF(v_end_date is NULL) then
35 errbuf := 'Unexpected error converting character string to date:'
36 ||end_date;
37 retcode := '2';
38 FND_FILE.put_line(FND_FILE.log,errbuf);
39 RETURN;
40 END IF;
41
42 if (module = 'INVOKER') then
43 v_direction := 'OUTBOUND';
44 elsif (module is null or module = 'PROVIDER') then
45 v_direction := 'INBOUND';
46 end if;
47
48 /* Purge Function to delete obsoleted Log Data */
49 deleted_log_count := delete_log_by_date_range(v_strt_date,v_end_date, v_direction);
50 FND_FILE.put_line(FND_FILE.log, 'Records purged in FND_LOG_MESSAGES - '||deleted_log_count);
51
52 /* Purge Function to delete all requests Data */
53 deleted_requests_count := delete_requests_by_date_range(v_direction,v_strt_date,v_end_date);
54 FND_FILE.put_line(FND_FILE.log, 'Records purged in FND_SOA_REQUEST - '||deleted_requests_count);
55
56 /* Purge Function to delete all method Data */
57 deleted_method_count := delete_method_by_date_range(v_strt_date,v_end_date);
58 FND_FILE.put_line(FND_FILE.log, 'Records purged in FND_SOA_RESPONSE_METHOD - '||deleted_method_count);
59
60 /* Purge Function to delete all attachment Data */
61 deleted_att_count := delete_att_by_date_range(v_strt_date,v_end_date);
62 FND_FILE.put_line(FND_FILE.log, 'Records purged in FND_SOA_ATTACHMENT - '||deleted_att_count);
63
64 /* Purge Function to delete all attachment body Data */
65 deleted_att_count := delete_body_by_date_range(v_strt_date,v_end_date);
66 FND_FILE.put_line(FND_FILE.log, 'Records purged in FND_SOA_BODY_PIECE - '||deleted_att_count);
67
68 /* Purge Function to delete all Error Data */
69 deleted_error_count := delete_error_by_date_range(v_strt_date,v_end_date);
70 FND_FILE.put_line(FND_FILE.log, 'Records purged in FND_SOA_RUNTIME_ERROR - '||deleted_error_count);
71
72 errbuf := '';
73 retcode := '0';
74
75 end delete_by_date_cp;
76
77
78 /* Purge Function to delete all requests Data */
79 FUNCTION delete_requests_by_date_range(
80 x_direction IN VARCHAR2,
81 x_start_date IN DATE,
82 x_end_date IN DATE) return NUMBER is
83 rowcount number := 0;
84 temp_rowcount number := 0;
85 BEGIN
86 LOOP
87 BEGIN
88
89 DELETE FROM FND_SOA_REQUEST
90 WHERE message_id IN
91 (SELECT WR.message_id
92 FROM FND_SOA_REQUEST WR
93 WHERE (x_start_date IS NOT NULL and x_end_date IS NOT NULL)
94 AND x_start_date <= WR.request_timestamp
95 AND x_end_date >= WR.request_timestamp)
96 AND nvl(direction, 'INBOUND') = x_direction
97 AND request_status <> 'IN_PROCESS'
98 AND rownum <= 20000;
99
100 temp_rowcount := SQL%ROWCOUNT;
101 COMMIT;
102 rowcount := rowcount + temp_rowcount;
103 EXIT WHEN (temp_rowcount = 0);
104
105 EXCEPTION
106 WHEN NO_DATA_FOUND THEN
107 NULL; /* Should never happen */
108 WHEN OTHERS THEN
109 IF ((SQLCODE = 60) or (SQLCODE = 4020)) then
110 NULL; /* Ignore rows that are deadlocked */
111 ELSE
112 RAISE;
113 END IF;
114 END;
115 END LOOP;
116 fnd_file.put_line(fnd_file.output,'Deleted '|| rowcount ||' rows from FND_SOA_REQUEST ');
117 RETURN rowcount;
118
119 END;
120
121 /* Purge Function to delete all method Data */
122 FUNCTION delete_method_by_date_range(
123 x_start_date IN DATE,
124 x_end_date IN DATE) return NUMBER is
125 rowcount number := 0;
126 temp_rowcount number := 0;
127 BEGIN
128 LOOP
129 BEGIN
130
131 DELETE FROM FND_SOA_RESPONSE_METHOD
132 WHERE message_id NOT IN
133 (SELECT message_id FROM FND_SOA_REQUEST)
134 AND rownum <= 20000;
135
136 temp_rowcount := SQL%ROWCOUNT;
137 COMMIT;
138 rowcount := rowcount + temp_rowcount;
139 EXIT WHEN (temp_rowcount = 0);
140
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 NULL; /* Should never happen */
144 WHEN OTHERS THEN
145 IF ((SQLCODE = 60) or (SQLCODE = 4020)) then
146 NULL; /* Ignore rows that are deadlocked */
147 ELSE
148 RAISE;
149 END IF;
150 END;
151 END LOOP;
152 fnd_file.put_line(fnd_file.output,
153 'Deleted '|| rowcount ||' rows from FND_SOA_RESPONSE_METHOD ');
154 RETURN rowcount;
155 END;
156
157
158 /* Purge Function to delete all attachment Data */
159 FUNCTION delete_att_by_date_range(
160 x_start_date IN DATE,
161 x_end_date IN DATE) return NUMBER is
162 rowcount number := 0;
163 temp_rowcount number := 0;
164 BEGIN
165 LOOP
166 BEGIN
167
168 DELETE FROM FND_SOA_ATTACHMENT WA
169 WHERE message_id NOT IN
170 (SELECT message_id FROM FND_SOA_REQUEST)
171 AND rownum <= 20000;
172
173 temp_rowcount := SQL%ROWCOUNT;
174 COMMIT;
175 rowcount := rowcount + temp_rowcount;
176 EXIT WHEN (temp_rowcount = 0);
177 EXCEPTION
178 WHEN NO_DATA_FOUND THEN
179 NULL; /* Should never happen */
180 WHEN OTHERS THEN
181 IF ((SQLCODE = 60) or (SQLCODE = 4020)) then
182 NULL; /* Ignore rows that are deadlocked */
183 ELSE
184 RAISE;
185 END IF;
186 END;
187 END LOOP;
188 fnd_file.put_line(fnd_file.output,
189 'Deleted '|| rowcount ||' rows from FND_SOA_ATTACHMENT');
190 RETURN rowcount;
191 END;
192
193
194
195 /* Purge Function to delete all attachment body Data */
196 FUNCTION delete_body_by_date_range(
197 x_start_date IN DATE,
198 x_end_date IN DATE) return NUMBER is
199 rowcount number := 0;
200 temp_rowcount number := 0;
201 BEGIN
202 LOOP
203 BEGIN
204
205 DELETE
206 FROM FND_SOA_BODY_PIECE WP
207 WHERE (message_id NOT IN
208 (SELECT message_id FROM FND_SOA_REQUEST))
209 AND rownum <= 20000;
210
211 temp_rowcount := SQL%ROWCOUNT;
212 COMMIT;
213 rowcount := rowcount + temp_rowcount;
214 EXIT WHEN (temp_rowcount = 0);
215
216 EXCEPTION
217 WHEN NO_DATA_FOUND THEN
218 NULL; /* Should never happen */
219 WHEN OTHERS THEN
220 IF ((SQLCODE = 60) or (SQLCODE = 4020)) then
221 NULL; /* Ignore rows that are deadlocked */
222 ELSE
223 RAISE;
224 END IF;
225 END;
226 END LOOP;
227 fnd_file.put_line(fnd_file.output,
228 'Deleted '|| rowcount ||' rows from FND_SOA_BODY_PIECE');
229 RETURN rowcount;
230 END;
231
232 /* Purge function to delete all Error Data */
233 FUNCTION delete_error_by_date_range(
234 x_start_date IN DATE,
235 x_end_date IN DATE) return NUMBER is
236 rowcount number := 0;
237 temp_rowcount number := 0;
238 BEGIN
239 LOOP
240 BEGIN
241
242 DELETE FROM FND_SOA_RUNTIME_ERROR RE
243 WHERE (message_id NOT IN
244 (SELECT message_id FROM FND_SOA_REQUEST))
245 AND rownum <= 20000;
246
247 temp_rowcount := SQL%ROWCOUNT;
248 COMMIT;
249 rowcount := rowcount + temp_rowcount;
250 EXIT WHEN (temp_rowcount = 0);
251
252 EXCEPTION
253 WHEN NO_DATA_FOUND THEN
254 NULL; /* Should never happen */
255 WHEN OTHERS THEN
256 IF ((SQLCODE = 60) or (SQLCODE = 4020)) then
257 NULL; /* Ignore rows that are deadlocked */
258 ELSE
259 RAISE;
260 END IF;
261 END;
262 END LOOP;
263 fnd_file.put_line(fnd_file.output,
264 'Deleted '|| rowcount ||' rows from FND_SOA_RUNTIME_ERROR');
265 RETURN rowcount;
266 END;
267
268 /* Purge function to delete all Log Data */
269 FUNCTION delete_log_by_date_range(
270 x_start_date IN DATE,
271 x_end_date IN DATE,
272 x_direction in varchar2) return NUMBER is
273 rowcount number := 0;
274 temp_rowcount number := 0;
275 BEGIN
276 LOOP
277 BEGIN
278
279 DELETE from fnd_log_messages
280 where transaction_context_id in
281 (select transaction_context_id
282 from fnd_log_transaction_context
283 where transaction_type = 'SOA_INSTANCE'
284 and transaction_id in
285 (select message_id
286 from fnd_soa_request
287 where REQUEST_STATUS = 'SUCCESS'
288 and x_start_date <= REQUEST_TIMESTAMP
289 AND x_end_date >= REQUEST_TIMESTAMP
290 AND direction = x_direction))
291 AND rownum <= 20000;
292
293 temp_rowcount := SQL%ROWCOUNT;
294 COMMIT;
295
296 rowcount := rowcount + temp_rowcount;
297 EXIT WHEN (temp_rowcount = 0);
298
299 EXCEPTION
300 WHEN OTHERS THEN
301 IF ((SQLCODE = 60) or (SQLCODE = 4020) OR (SQLCODE = 1722)) then
302 NULL; /* Ignore rows that are deadlocked */
303 ELSE
304 RAISE;
305 END IF;
306 END;
307 END LOOP;
308 fnd_file.put_line(fnd_file.output,
309 'Deleted '|| rowcount ||' rows from FND_LOG_MESSAGES');
310 RETURN rowcount;
311 END;
312
313 /* Purge Function to Delete Log Details by Message ID */
314 FUNCTION delete_log_by_instance_id(
315 x_instance_id IN NUMBER) return NUMBER is
316 rowcount number := 0;
317 temp_rowcount number := 0;
318 BEGIN
319 LOOP
320 BEGIN
321
322 DELETE
323 from fnd_log_messages
324 where transaction_context_id in
325 (select transaction_context_id
326 from fnd_log_transaction_context
327 where transaction_type = 'SOA_INSTANCE'
328 and transaction_id = x_instance_id) and rownum <= 20000;
329 temp_rowcount := SQL%ROWCOUNT;
330 COMMIT;
331 rowcount := rowcount + temp_rowcount;
332 EXIT WHEN (temp_rowcount = 0);
333
334 EXCEPTION
335 WHEN NO_DATA_FOUND THEN
336 NULL; /* Should never happen */
337 WHEN OTHERS THEN
338 IF ((SQLCODE = 60) or (SQLCODE = 4020)) then
339 NULL; /* Ignore rows that are deadlocked */
340 ELSE
341 RAISE;
342 END IF;
343 END;
344 END LOOP;
345 RETURN rowcount;
346 END;
347
348
349 end fnd_soa_util;
[Home] [Help]