1 package body fnd_oam_ws_util as
2 /* $Header: AFOAMWSUTILB.pls 120.1 2005/08/21 12:14:54 ssuprasa 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 := FND_CONC_DATE.STRING_TO_DATE(start_date);
20 v_end_date := FND_CONC_DATE.STRING_TO_DATE(end_date);
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 := delete_requests_by_date_range(v_strt_date,v_end_date);
38 /* Purge Function to delete all response Data */
39 deleted_response_count := delete_responses_by_date_range(v_strt_date,v_end_date);
40 /* Purge Function to delete all method Data */
41 deleted_method_count := delete_method_by_date_range(v_strt_date,v_end_date);
42 /* Purge Function to delete all attachment Data */
43 deleted_att_count := delete_att_by_date_range(v_strt_date,v_end_date);
44 /* Purge Function to delete all attachment body Data */
45 deleted_att_count := delete_body_by_date_range(v_strt_date,v_end_date);
46
47
48 end delete_by_date_cp;
49
50
51 /* Purge Function to delete all requests Data */
52 FUNCTION delete_requests_by_date_range(
53 x_start_date IN DATE,
54 x_end_date IN DATE) return NUMBER is
55 rowcount number := 0;
56 temp_rowcount number := 0;
57 BEGIN
58
59 LOOP
60 BEGIN
61
62 DELETE
63 FROM OAM_PAT_WS_REQUEST
64 WHERE message_id IN
65 (SELECT WR.message_id
66 FROM
67 OAM_PAT_WS_REQUEST WR
68 WHERE
69 (x_start_date IS NOT NULL and x_end_date IS NOT NULL)
70 AND nvl(x_start_date, WR.request_timestamp)<= WR.request_timestamp
71 AND nvl(x_end_date, WR.request_timestamp)>= WR.request_timestamp
72 )
73 AND rownum <= 1000;
74 temp_rowcount := SQL%ROWCOUNT;
75 COMMIT;
76 rowcount := rowcount + temp_rowcount;
77 EXIT WHEN (temp_rowcount = 0);
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
80 NULL; /* Should never happen */
81 WHEN OTHERS THEN
82 IF ((SQLCODE = 60) or (SQLCODE = 4020)) then
83 NULL; /* Ignore rows that are deadlocked */
84 ELSE
85 RAISE;
86 END IF;
87 END;
88
89 END LOOP;
90 fnd_file.put_line(fnd_file.output,'Deleted '|| rowcount ||' rows from OAM_PAT_WS_REQUEST ');
91 RETURN rowcount;
92 END;
93
94
95
96 /* Purge Function to delete all response Data */
97 FUNCTION delete_responses_by_date_range(
98 x_start_date IN DATE,
99 x_end_date IN DATE) return NUMBER is
100 rowcount number := 0;
101 temp_rowcount number := 0;
102 BEGIN
103 LOOP
104 BEGIN
105
106
107
108
109 DELETE
110 FROM OAM_PAT_WS_RESPONSE
111 WHERE message_id IN
112 (SELECT WR.message_id
113 FROM
114 OAM_PAT_WS_RESPONSE WR
115 WHERE
116 (x_start_date IS NOT NULL and x_end_date IS NOT NULL)
117 AND nvl(x_start_date, WR.response_timestamp)<= WR.response_timestamp
118 AND nvl(x_end_date, WR.response_timestamp)>= WR.response_timestamp
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 FND_OAM_WS_RESPONSE ');
140 RETURN rowcount;
141 END;
142
143 /* Purge Function to delete all method Data */
144 FUNCTION delete_method_by_date_range(
145 x_start_date IN DATE,
146 x_end_date IN DATE) return NUMBER is
147 rowcount number := 0;
148 temp_rowcount number := 0;
149 BEGIN
150 LOOP
151 BEGIN
152
153 DELETE
154 FROM OAM_PAT_WS_RESPONSE_METHOD
155 WHERE
156 message_id NOT IN
157 (SELECT message_id
158 FROM
159 OAM_PAT_WS_RESPONSE)
160 AND rownum <= 1000;
161
162 temp_rowcount := SQL%ROWCOUNT;
163 COMMIT;
164 rowcount := rowcount + temp_rowcount;
165 EXIT WHEN (temp_rowcount = 0);
166 EXCEPTION
167 WHEN NO_DATA_FOUND THEN
168 NULL; /* Should never happen */
169 WHEN OTHERS THEN
170 IF ((SQLCODE = 60) or (SQLCODE = 4020)) then
171 NULL; /* Ignore rows that are deadlocked */
172 ELSE
173 RAISE;
174 END IF;
175 END;
176
177 END LOOP;
178 fnd_file.put_line(fnd_file.output,
179 'Deleted '|| rowcount ||' rows from OAM_PAT_WS_RESPONSE_METHOD ');
180 RETURN rowcount;
181 END;
182
183 /* Purge Function to delete all attachment Data */
184 FUNCTION delete_att_by_date_range(
185 x_start_date IN DATE,
186 x_end_date IN DATE) return NUMBER is
187 rowcount number := 0;
188 temp_rowcount number := 0;
189 BEGIN
190 LOOP
191 BEGIN
192
193 DELETE
194 FROM OAM_PAT_WS_ATTACHMENT WA
195 WHERE
196 ((WA.BELONGS_TO='REQUEST' AND message_id NOT IN
197 (SELECT message_id
198 FROM
199 OAM_PAT_WS_REQUEST))
200 OR
201 (WA.BELONGS_TO='RESPONSE' AND message_id NOT IN
202 (SELECT message_id
203 FROM
204 OAM_PAT_WS_RESPONSE)))
205 AND rownum <= 1000;
206
207 temp_rowcount := SQL%ROWCOUNT;
208 COMMIT;
209 rowcount := rowcount + temp_rowcount;
210 EXIT WHEN (temp_rowcount = 0);
211 EXCEPTION
212 WHEN NO_DATA_FOUND THEN
213 NULL; /* Should never happen */
214 WHEN OTHERS THEN
215 IF ((SQLCODE = 60) or (SQLCODE = 4020)) then
216 NULL; /* Ignore rows that are deadlocked */
217 ELSE
218 RAISE;
219 END IF;
220 END;
221
222 END LOOP;
223 fnd_file.put_line(fnd_file.output,
224 'Deleted '|| rowcount ||' rows from OAM_PAT_WS_ATTACHMENT ');
225 RETURN rowcount;
226 END;
227
228
229 /* Purge Function to delete all attachment body Data */
230 FUNCTION delete_body_by_date_range(
231 x_start_date IN DATE,
232 x_end_date IN DATE) return NUMBER is
233 rowcount number := 0;
234 temp_rowcount number := 0;
235 BEGIN
236 LOOP
237 BEGIN
238
239 DELETE
240 FROM OAM_PAT_WS_BODY_PIECE WP
241 WHERE
242 ((WP.BELONGS_TO='REQUEST' AND message_id NOT IN
243 (SELECT message_id
244 FROM
245 OAM_PAT_WS_REQUEST))
246 OR
247 (WP.BELONGS_TO='RESPONSE' AND message_id NOT IN
248 (SELECT message_id
249 FROM
250 OAM_PAT_WS_RESPONSE)))
251 AND rownum <= 1000;
252
253 temp_rowcount := SQL%ROWCOUNT;
254 COMMIT;
255 rowcount := rowcount + temp_rowcount;
256 EXIT WHEN (temp_rowcount = 0);
257 EXCEPTION
258 WHEN NO_DATA_FOUND THEN
259 NULL; /* Should never happen */
260 WHEN OTHERS THEN
261 IF ((SQLCODE = 60) or (SQLCODE = 4020)) then
262 NULL; /* Ignore rows that are deadlocked */
263 ELSE
264 RAISE;
265 END IF;
266 END;
267
268 END LOOP;
269 fnd_file.put_line(fnd_file.output,
270 'Deleted '|| rowcount ||' rows from OAM_PAT_WS_BODY_PIECE ');
271 RETURN rowcount;
272 END;
273
274
275
276 end fnd_oam_ws_util;
[Home] [Help]