1 package body fnd_soa_util as
2 /* $Header: FNDSOAUB.pls 120.1 2008/05/15 06:15:09 rajarram noship $ */
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 ) is
9 v_strt_date DATE;
10 v_end_date DATE;
11 deleted_requests_count NUMBER;
12 deleted_response_count NUMBER;
13 deleted_method_count NUMBER;
14 deleted_att_count NUMBER;
15
16
17 begin
18
19 v_strt_date := trunc(FND_CONC_DATE.STRING_TO_DATE(start_date),'DD');
20 v_end_date :=trunc( FND_CONC_DATE.STRING_TO_DATE(end_date),'DD');
21 IF(v_strt_date is NULL) then
22 errbuf := 'Unexpected error converting character string to date:'
23 ||start_date;
24 retcode := '2';
25 FND_FILE.put_line(FND_FILE.log,errbuf);
26 RETURN;
27 END IF;
28 IF(v_end_date is NULL) then
29 errbuf := 'Unexpected error converting character string to date:'
30 ||end_date;
31 retcode := '2';
32 FND_FILE.put_line(FND_FILE.log,errbuf);
33 RETURN;
34 END IF;
35
36 /* Purge Function to delete all requests Data */
37 deleted_requests_count :=
38 delete_requests_by_date_range(v_strt_date,v_end_date);
39 /* Purge Function to delete all response Data */
40 deleted_response_count :=
41 delete_responses_by_date_range(v_strt_date,v_end_date);
42 /* Purge Function to delete all method Data */
43 deleted_method_count := delete_method_by_date_range(v_strt_date,v_end_date);
44 /* Purge Function to delete all attachment Data */
45 deleted_att_count := delete_att_by_date_range(v_strt_date,v_end_date);
46 /* Purge Function to delete all attachment body Data */
47 deleted_att_count := delete_body_by_date_range(v_strt_date,v_end_date);
48
49
50 end delete_by_date_cp;
51
52
53 /* Purge Function to delete all requests Data */
54 FUNCTION delete_requests_by_date_range(
55 x_start_date IN DATE,
56 x_end_date IN DATE) return NUMBER is
57 rowcount number := 0;
58 temp_rowcount number := 0;
59 BEGIN
60
61 LOOP
62 BEGIN
63
64 DELETE
65 FROM FND_SOA_REQUEST
66 WHERE message_id IN
67 (SELECT WR.message_id
68 FROM
69 FND_SOA_REQUEST WR
70 WHERE
71 (x_start_date IS NOT NULL and x_end_date IS NOT NULL)
72 AND x_start_date<= trunc(WR.request_timestamp,'DD')
73 AND x_end_date>= trunc(WR.request_timestamp,'DD')
74 )
75 AND rownum <= 1000;
76 temp_rowcount := SQL%ROWCOUNT;
77 COMMIT;
78 rowcount := rowcount + temp_rowcount;
79 EXIT WHEN (temp_rowcount = 0);
80 EXCEPTION
81 WHEN NO_DATA_FOUND THEN
82 NULL; /* Should never happen */
83 WHEN OTHERS THEN
84 IF ((SQLCODE = 60) or (SQLCODE = 4020)) then
85 NULL; /* Ignore rows that are deadlocked */
86 ELSE
87 RAISE;
88 END IF;
89 END;
90
91 END LOOP;
92 fnd_file.put_line(fnd_file.output,'Deleted '|| rowcount ||' rows from
93 FND_SOA_REQUEST ');
94 RETURN rowcount;
95 END;
96
97
98
99 /* Purge Function to delete all response Data */
100 FUNCTION delete_responses_by_date_range(
101 x_start_date IN DATE,
102 x_end_date IN DATE) return NUMBER is
103 rowcount number := 0;
104 temp_rowcount number := 0;
105 BEGIN
106 LOOP
107 BEGIN
108
109 DELETE
110 FROM FND_SOA_RESPONSE
111 WHERE message_id IN
112 (SELECT WR.message_id
113 FROM
114 FND_SOA_RESPONSE WR
115 WHERE
116 (x_start_date IS NOT NULL and x_end_date IS NOT NULL)
117 AND x_start_date <= trunc(WR.response_timestamp,'DD')
118 AND x_end_date >= trunc(WR.response_timestamp,'DD')
119 )
120 AND rownum <= 1000;
121
122
123 temp_rowcount := SQL%ROWCOUNT;
124 COMMIT;
125 rowcount := rowcount + temp_rowcount;
126 EXIT WHEN (temp_rowcount = 0);
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 NULL; /* Should never happen */
130 WHEN OTHERS THEN
131 IF ((SQLCODE = 60) or (SQLCODE = 4020)) then
132 NULL; /* Ignore rows that are deadlocked */
133 ELSE
134 RAISE;
135 END IF;
136 END;
137
138 END LOOP;
139 fnd_file.put_line(fnd_file.output,'Deleted '|| rowcount ||' rows from
140 FND_SOA_RESPONSE ');
141 RETURN rowcount;
142 END;
143
144 /* Purge Function to delete all method Data */
145 FUNCTION delete_method_by_date_range(
146 x_start_date IN DATE,
147 x_end_date IN DATE) return NUMBER is
148 rowcount number := 0;
149 temp_rowcount number := 0;
150 BEGIN
151 LOOP
152 BEGIN
153
154 DELETE
155 FROM FND_SOA_RESPONSE_METHOD
156 WHERE
157 message_id NOT IN
158 (SELECT message_id
159 FROM
160 FND_SOA_RESPONSE)
161 AND rownum <= 1000;
162
163 temp_rowcount := SQL%ROWCOUNT;
164 COMMIT;
165 rowcount := rowcount + temp_rowcount;
166 EXIT WHEN (temp_rowcount = 0);
167 EXCEPTION
168 WHEN NO_DATA_FOUND THEN
169 NULL; /* Should never happen */
170 WHEN OTHERS THEN
171 IF ((SQLCODE = 60) or (SQLCODE = 4020)) then
172 NULL; /* Ignore rows that are deadlocked */
173 ELSE
174 RAISE;
175 END IF;
176 END;
177
178 END LOOP;
179 fnd_file.put_line(fnd_file.output,
180 'Deleted '|| rowcount ||' rows from
181 FND_SOA_RESPONSE_METHOD ');
182 RETURN rowcount;
183 END;
184
185 /* Purge Function to delete all attachment Data */
186 FUNCTION delete_att_by_date_range(
187 x_start_date IN DATE,
188 x_end_date IN DATE) return NUMBER is
189 rowcount number := 0;
190 temp_rowcount number := 0;
191 BEGIN
192 LOOP
193 BEGIN
194
195 DELETE
196 FROM FND_SOA_ATTACHMENT WA
197 WHERE
198 ((WA.BELONGS_TO='REQUEST' AND message_id NOT IN
199 (SELECT message_id
200 FROM
201 FND_SOA_REQUEST))
202 OR
203 (WA.BELONGS_TO='RESPONSE' AND message_id NOT IN
204 (SELECT message_id
205 FROM
206 FND_SOA_RESPONSE)))
207 AND rownum <= 1000;
208
209 temp_rowcount := SQL%ROWCOUNT;
210 COMMIT;
211 rowcount := rowcount + temp_rowcount;
212 EXIT WHEN (temp_rowcount = 0);
213 EXCEPTION
214 WHEN NO_DATA_FOUND THEN
215 NULL; /* Should never happen */
216 WHEN OTHERS THEN
217 IF ((SQLCODE = 60) or (SQLCODE = 4020)) then
218 NULL; /* Ignore rows that are deadlocked */
219 ELSE
220 RAISE;
221 END IF;
222 END;
223
224 END LOOP;
225 fnd_file.put_line(fnd_file.output,
226 'Deleted '|| rowcount ||' rows from FND_SOA_ATTACHMENT
227 ');
228 RETURN rowcount;
229 END;
230
231
232 /* Purge Function to delete all attachment body Data */
233 FUNCTION delete_body_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
243 FROM FND_SOA_BODY_PIECE WP
244 WHERE
245 ((WP.BELONGS_TO='REQUEST' AND message_id NOT IN
246 (SELECT message_id
247 FROM
248 FND_SOA_REQUEST))
249 OR
250 (WP.BELONGS_TO='RESPONSE' AND message_id NOT IN
251 (SELECT message_id
252 FROM
253 FND_SOA_RESPONSE)))
254 AND rownum <= 1000;
255
256 temp_rowcount := SQL%ROWCOUNT;
257 COMMIT;
258 rowcount := rowcount + temp_rowcount;
259 EXIT WHEN (temp_rowcount = 0);
260 EXCEPTION
261 WHEN NO_DATA_FOUND THEN
262 NULL; /* Should never happen */
263 WHEN OTHERS THEN
264 IF ((SQLCODE = 60) or (SQLCODE = 4020)) then
265 NULL; /* Ignore rows that are deadlocked */
266 ELSE
267 RAISE;
268 END IF;
269 END;
270
271 END LOOP;
272 fnd_file.put_line(fnd_file.output,
273 'Deleted '|| rowcount ||' rows from FND_SOA_BODY_PIECE
274 ');
275 RETURN rowcount;
276 END;
277
278
279
280 end fnd_soa_util;
[Home] [Help]