[Home] [Help]
PACKAGE BODY: APPS.QP_PURGE_PRICING_REQUESTS
Source
1 PACKAGE BODY QP_PURGE_PRICING_REQUESTS AS
2 /* $Header: QPXDLDBB.pls 120.1 2005/06/09 23:56:24 appldev $ */
3
4 PROCEDURE Purge
5 (err_buff out NOCOPY /* file.sql.39 change */ VARCHAR2,
6 retcode out NOCOPY /* file.sql.39 change */ NUMBER,
7 x_no_of_days in NUMBER,
8 x_request_name in VARCHAR2) IS
9
10 l_request_name varchar2(240);
11 l_no_of_days number;
12 l_count number := 0;
13 l_qp_schema VARCHAR2(30);
14 l_stmt VARCHAR2(200);
15 l_status VARCHAR2(30);
16 l_industry VARCHAR2(30);
17
18 BEGIN
19
20 l_no_of_days := x_no_of_days;
21
22 IF (l_no_of_days is not null) and (x_request_name is not null) THEN
23 l_request_name := x_request_name || '%';
24
25 SELECT COUNT(*) INTO l_count FROM QP_DEBUG_REQ
26 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
27 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY') AND
28 request_name like l_request_name;
29
30 IF l_count = 0 THEN
31 RAISE NO_DATA_FOUND;
32 END IF;
33
34
35 LOOP
36 DELETE QP_DEBUG_REQ_LINES WHERE request_id in
37 (SELECT request_id FROM qp_debug_req
38 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
39 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY') AND
40 request_name like l_request_name
41 ) AND rownum <= 500;
42
43 IF SQL%NOTFOUND THEN
44 EXIT;
45 END IF;
46 COMMIT;
47 END LOOP;
48
49 LOOP
50 DELETE QP_DEBUG_REQ_LDETS WHERE request_id in
51 (SELECT request_id FROM qp_debug_req
52 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
53 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY') AND
54 request_name like l_request_name
55 ) AND rownum <= 500;
56
57 IF SQL%NOTFOUND THEN
58 EXIT;
59 END IF;
60 COMMIT;
61 END LOOP;
62
63 LOOP
64 DELETE QP_DEBUG_REQ_LINE_ATTRS WHERE request_id in
65 (SELECT request_id FROM qp_debug_req
66 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
67 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY') AND
68 request_name like l_request_name
69 ) AND rownum <= 500;
70
71 IF SQL%NOTFOUND THEN
72 EXIT;
73 END IF;
74 COMMIT;
75 END LOOP;
76
77 LOOP
78 DELETE QP_DEBUG_REQ_RLTD_LINES WHERE request_id in
79 (SELECT request_id FROM qp_debug_req
80 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
81 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY') AND
82 request_name like l_request_name
83 ) AND rownum <= 500;
84
85 IF SQL%NOTFOUND THEN
86 EXIT;
87 END IF;
88 COMMIT;
89 END LOOP;
90
91 LOOP
92 DELETE QP_DEBUG_FORMULA_STEP_VALUES WHERE request_id in
93 (SELECT request_id FROM qp_debug_req
94 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
95 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY') AND
96 request_name like l_request_name
97 ) AND rownum <= 500;
98
99 IF SQL%NOTFOUND THEN
100 EXIT;
101 END IF;
102 COMMIT;
103 END LOOP;
104
105 LOOP
106 DELETE QP_DEBUG_TEXT WHERE request_id in
107 (SELECT request_id FROM qp_debug_req
108 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
109 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY') AND
110 request_name like l_request_name
111 ) AND rownum <= 500;
112
113 IF SQL%NOTFOUND THEN
114 EXIT;
115 END IF;
116 COMMIT;
117 END LOOP;
118
119 LOOP
120 DELETE QP_DEBUG_REQ
121 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
122 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY') AND
123 request_name like l_request_name AND
124 rownum <= 500;
125
126 IF SQL%NOTFOUND THEN
127 EXIT;
128 END IF;
129 COMMIT;
130 END LOOP;
131
132
133 ELSIF (l_no_of_days is not null) and (x_request_name is null) THEN
134
135
136 SELECT COUNT(*) INTO l_count FROM QP_DEBUG_REQ
137 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
138 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY');
139
140 IF l_count = 0 THEN
141 RAISE NO_DATA_FOUND;
142 END IF;
143
144 LOOP
145 DELETE QP_DEBUG_REQ_LINES WHERE request_id in
146 (SELECT request_id FROM qp_debug_req
147 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
148 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY')
149 ) AND rownum <= 500;
150
151 IF SQL%NOTFOUND THEN
152 EXIT;
153 END IF;
154 COMMIT;
155 END LOOP;
156
157 LOOP
158 DELETE QP_DEBUG_REQ_LDETS WHERE request_id in
159 (SELECT request_id FROM qp_debug_req
160 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
161 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY')
162 ) AND rownum <= 500;
163
164 IF SQL%NOTFOUND THEN
165 EXIT;
166 END IF;
167 COMMIT;
168 END LOOP;
169
170 LOOP
171 DELETE QP_DEBUG_REQ_LINE_ATTRS WHERE request_id in
172 (SELECT request_id FROM qp_debug_req
173 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
174 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY')
175 ) AND rownum <= 500;
176
177 IF SQL%NOTFOUND THEN
178 EXIT;
179 END IF;
180 COMMIT;
181 END LOOP;
182
183 LOOP
184 DELETE QP_DEBUG_REQ_RLTD_LINES WHERE request_id in
185 (SELECT request_id FROM qp_debug_req
186 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
187 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY')
188 ) AND rownum <= 500;
189
190 IF SQL%NOTFOUND THEN
191 EXIT;
192 END IF;
193 COMMIT;
194 END LOOP;
195
196 LOOP
197 DELETE QP_DEBUG_FORMULA_STEP_VALUES WHERE request_id in
198 (SELECT request_id FROM qp_debug_req
199 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
200 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY')
201 ) AND rownum <= 500;
202
203 IF SQL%NOTFOUND THEN
204 EXIT;
205 END IF;
206 COMMIT;
207 END LOOP;
208
209 LOOP
210 DELETE QP_DEBUG_TEXT WHERE request_id in
211 (SELECT request_id FROM qp_debug_req
212 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
213 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY')
214 ) AND rownum <= 500;
215
216 IF SQL%NOTFOUND THEN
217 EXIT;
218 END IF;
219 COMMIT;
220 END LOOP;
221
222 LOOP
223 DELETE QP_DEBUG_REQ
224 WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
225 TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY') AND
226 rownum <= 500;
227
228 IF SQL%NOTFOUND THEN
229 EXIT;
230 END IF;
231 COMMIT;
232 END LOOP;
233
234
235 ELSIF (l_no_of_days is null) and (x_request_name is not null) THEN
236
237 l_request_name := x_request_name || '%';
238
239
240 SELECT COUNT(*) INTO l_count FROM QP_DEBUG_REQ
241 WHERE request_name like l_request_name;
242
243 IF l_count = 0 THEN
244 RAISE NO_DATA_FOUND;
245 END IF;
246
247 LOOP
248 DELETE QP_DEBUG_REQ_LINES WHERE request_id in
249 (SELECT request_id FROM qp_debug_req
250 WHERE request_name like l_request_name
251 ) AND rownum <= 500;
252
253 IF SQL%NOTFOUND THEN
254 EXIT;
255 END IF;
256 COMMIT;
257 END LOOP;
258
259 LOOP
260 DELETE QP_DEBUG_REQ_LDETS WHERE request_id in
261 (SELECT request_id FROM qp_debug_req
262 WHERE request_name like l_request_name
263 ) AND rownum <= 500;
264
265 IF SQL%NOTFOUND THEN
266 EXIT;
267 END IF;
268 COMMIT;
269 END LOOP;
270
271 LOOP
272 DELETE QP_DEBUG_REQ_LINE_ATTRS WHERE request_id in
273 (SELECT request_id FROM qp_debug_req
274 WHERE request_name like l_request_name
275 ) AND rownum <= 500;
276
277 IF SQL%NOTFOUND THEN
278 EXIT;
279 END IF;
280 COMMIT;
281 END LOOP;
282
283 LOOP
284 DELETE QP_DEBUG_REQ_RLTD_LINES WHERE request_id in
285 (SELECT request_id FROM qp_debug_req
286 WHERE request_name like l_request_name
287 ) AND rownum <= 500;
288
289 IF SQL%NOTFOUND THEN
290 EXIT;
291 END IF;
292 COMMIT;
293 END LOOP;
294
295 LOOP
296 DELETE QP_DEBUG_FORMULA_STEP_VALUES WHERE request_id in
297 (SELECT request_id FROM qp_debug_req
298 WHERE request_name like l_request_name
299 ) AND rownum <= 500;
300
301 IF SQL%NOTFOUND THEN
302 EXIT;
303 END IF;
304 COMMIT;
305 END LOOP;
306
307 LOOP
308 DELETE QP_DEBUG_TEXT WHERE request_id in
309 (SELECT request_id FROM qp_debug_req
310 WHERE request_name like l_request_name
311 ) AND rownum <= 500;
312
313 IF SQL%NOTFOUND THEN
314 EXIT;
315 END IF;
316 COMMIT;
317 END LOOP;
318
319 LOOP
320 DELETE QP_DEBUG_REQ
321 WHERE request_name like l_request_name AND
322 rownum <= 500;
323
324 IF SQL%NOTFOUND THEN
325 EXIT;
326 END IF;
327 COMMIT;
328 END LOOP;
329
330
331 ELSIF (l_no_of_days is null) and (x_request_name is null) THEN
332
333 SELECT COUNT(*) INTO l_count FROM QP_DEBUG_REQ;
334
335 IF l_count = 0 THEN
336 RAISE NO_DATA_FOUND;
337 END IF;
338
339 IF (FND_INSTALLATION.GET_APP_INFO('QP', l_status, l_industry, l_qp_schema)) THEN
340
341 l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.QP_DEBUG_REQ_LINES';
342 EXECUTE IMMEDIATE l_stmt;
343
344 l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.QP_DEBUG_REQ_LDETS';
345 EXECUTE IMMEDIATE l_stmt;
346
347 l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.QP_DEBUG_REQ_LINE_ATTRS';
348 EXECUTE IMMEDIATE l_stmt;
349
350 l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.QP_DEBUG_REQ_RLTD_LINES';
351 EXECUTE IMMEDIATE l_stmt;
352
353 l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.QP_DEBUG_FORMULA_STEP_VALUES';
354 EXECUTE IMMEDIATE l_stmt;
355
356 l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.QP_DEBUG_TEXT';
357 EXECUTE IMMEDIATE l_stmt;
358
359 l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.QP_DEBUG_REQ';
360 EXECUTE IMMEDIATE l_stmt;
361
362 END IF;
363
364 END IF;
365
366 COMMIT;
367 fnd_file.put_line(FND_FILE.LOG,'Purging Pricing Engine Requests completed successfully');
368 err_buff := 'Purging Pricing Engine Requests completed successfully';
369 retcode := 0;
370
371
372 EXCEPTION
373 WHEN NO_DATA_FOUND THEN
374 retcode := 1;
375 --fnd_file.put_line(FND_FILE.LOG,substr(sqlerrm,1,300));
376 --fnd_file.put_line(FND_FILE.LOG,sqlcode);
377 --err_buff := substr(sqlerrm,1,240);
378 fnd_file.put_line(FND_FILE.LOG,'No Data Found - 0 Records Deleted');
379 err_buff := 'No Data Found - 0 Records Deleted';
380
381 WHEN OTHERS THEN
382 retcode := 2;
383 fnd_file.put_line(FND_FILE.LOG,substr(sqlerrm,1,300));
384 fnd_file.put_line(FND_FILE.LOG,sqlcode);
385 err_buff := substr(sqlerrm,1,240);
386 RAISE;
387
388 END Purge;
389
390 END QP_PURGE_PRICING_REQUESTS;