1 PACKAGE BODY QP_PURGE_ENTITY AS
2 /* $Header: QPXPURGB.pls 120.0 2005/06/02 00:48:06 appldev noship $ */
3
4 -- GLOBAL Constant holding the package name
5
6 --G_PKG_NAME CONSTANT VARCHAR2(30):='QP_PURGE_ENTITY'
7
8 /***************************************************************
9 * Procedure to insert records into the criteria tables *
10 ****************************************************************/
11
12 Procedure INSERT_CRITERIA
13 (
14 p_archive_name VARCHAR2,
15 p_entity_type VARCHAR2,
16 p_source_system_code VARCHAR2,
17 p_entity NUMBER,
18 p_archive_start_date VARCHAR2,
19 p_archive_end_date VARCHAR2,
20 p_user_id NUMBER,
21 p_conc_request_id NUMBER,
22 p_result_status VARCHAR2
23 )
24 IS
25 BEGIN
26 insert into QP_ARCH_CRITERIA_HEADERS
27 (request_id,
28 request_name,
29 request_type,
30 source_system,
31 creation_date,
32 created_by,
33 request_status,
34 purge_flag)
35 values
36 (p_conc_request_id,
37 NULL,
38 'PURGE',
39 p_source_system_code,
40 sysdate,
41 p_user_id,
42 p_result_status,
43 'N');
44
45 IF p_archive_name is NULL and p_entity_type is not null and p_entity is not null THEN
46 insert into QP_ARCH_CRITERIA_LINES
47 (request_id,
48 parameter_name,
49 parameter_value)
50 values
51 (p_conc_request_id,
52 'ENTITY_TYPE',
53 p_entity_type);
54
55 insert into QP_ARCH_CRITERIA_LINES
56 (request_id,
57 parameter_name,
58 parameter_value)
59 values(p_conc_request_id,
60 'ENTITY',
61 p_entity);
62
63 END IF;
64
65 IF (p_archive_start_date is not null and p_archive_end_date is not null) THEN
66 insert into QP_ARCH_CRITERIA_LINES
67 (request_id,
68 parameter_name,
69 parameter_value)
70 values
71 (p_conc_request_id,
72 'ARCHIVE_START_DATE',
73 fnd_date.canonical_to_date(p_archive_start_date));
74
75 insert into QP_ARCH_CRITERIA_LINES
76 (request_id,
77 parameter_name,
78 parameter_value)
79 values(p_conc_request_id,
80 'ARCHIVE_END_DATE',
81 fnd_date.canonical_to_date(p_archive_end_date));
82
83 END IF;
84
85 END INSERT_CRITERIA;
86
87
88 PROCEDURE Purge_Entity
89 (
90 errbuf OUT NOCOPY VARCHAR2,
91 retcode OUT NOCOPY NUMBER,
92 p_source_system_code IN VARCHAR2,
93 p_archive_name IN VARCHAR2,
94 p_entity_type IN VARCHAR2,
95 p_entity IN NUMBER,
96 p_archive_start_date IN VARCHAR2,
97 p_archive_end_date IN VARCHAR2
98 )
99 IS
100 l_conc_request_id NUMBER := -1;
101 l_user_id NUMBER := -1;
102 l_request_id NUMBER;
103 l_count NUMBER := 0;
104
105 BEGIN
106
107 l_conc_request_id := FND_GLOBAL.CONC_REQUEST_ID;
108 l_user_id := FND_GLOBAL.USER_ID;
109
110 --Check if the archive name is provided
111
112 IF p_archive_name is not NULL THEN
113
114 BEGIN
115
116
117 -- Get the request id from the archive name
118
119 SELECT request_id into l_request_id
120 FROM QP_ARCH_CRITERIA_HEADERS
121 WHERE nvl(request_name,'')=p_archive_name
122 and request_type = 'ARCHIVE'
123 and purge_flag = 'N';
124
125 EXCEPTION
126 WHEN NO_DATA_FOUND THEN
127 RAISE NO_DATA_FOUND;
128 END;
129
130 --Delete from QP_ARCH_LIST_HEADERS_TL
131
132 DELETE QP_ARCH_LIST_HEADERS_TL WHERE ARCH_PURG_REQUEST_ID = l_request_id;
133 IF SQL%FOUND THEN
134 COMMIT;
135 END IF;
136
137 --Delete from QP_ARCH_LIST_HEADERS_B
138
139 DELETE QP_ARCH_LIST_HEADERS_B WHERE ARCH_PURG_REQUEST_ID = l_request_id;
140 IF SQL%FOUND THEN
141 COMMIT;
142 END IF;
143
144 --Delete from QP_ARCH_LIST_LINES
145
146 DELETE QP_ARCH_LIST_LINES WHERE ARCH_PURG_REQUEST_ID = l_request_id;
147 IF SQL%FOUND THEN
148 COMMIT;
149 END IF;
150
151 --Delete from QP_ARCH_PRICING_ATTRIBUTES
152
153 DELETE QP_ARCH_PRICING_ATTRIBUTES WHERE ARCH_PURG_REQUEST_ID = l_request_id;
154 IF SQL%FOUND THEN
155 COMMIT;
156 END IF;
157
158 --Delete from QP_ARCH_RLTD_MODIFIERS
159
160 DELETE QP_ARCH_RLTD_MODIFIERS WHERE ARCH_PURG_REQUEST_ID = l_request_id;
161 IF SQL%FOUND THEN
162 COMMIT;
163 END IF;
164
165 --Delete from QP_ARCH_QUALIFIERS
166
167 DELETE QP_ARCH_QUALIFIERS WHERE ARCH_PURG_REQUEST_ID = l_request_id;
168 IF SQL%FOUND THEN
169 COMMIT;
170 END IF;
171
172 --Update the purge_flag in the QP_ARCH_CRITERIA_HEADERS table to Y
173
174 UPDATE QP_ARCH_CRITERIA_HEADERS set PURGE_FLAG = 'Y' where request_id =l_request_id;
175 IF SQL%FOUND THEN
176 COMMIT;
177 END IF;
178
179 ELSE -- Archive Name is null
180
181 --Check if entity type and entity is provided
182
183 IF (p_entity_type is not null and p_entity is not null) THEN
184 --Get the count of records matching the purge criteria
185
186 select count(*) into l_count
187 from QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
188 where c.request_id = a.request_id and
189 a.REQUEST_ID = b.REQUEST_ID
190 and c.purge_flag = 'N'
191 and c.REQUEST_TYPE = 'ARCHIVE'
192 And (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
193 and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity));
194
195 IF l_count = 0 THEN
196 RAISE NO_DATA_FOUND;
197 END IF;
198
199
200 --Delete from QP_ARCH_LIST_HEADERS_TL
201
202 LOOP
203 DELETE QP_ARCH_LIST_HEADERS_TL WHERE ARCH_PURG_REQUEST_ID in (select c.request_id from
204 QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
205 where c.request_id = a.request_id and
206 a.REQUEST_ID = b.REQUEST_ID
207 and c.purge_flag = 'N'
208 and c.REQUEST_TYPE = 'ARCHIVE'
209 and (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
210 and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity)))
211 AND rownum <= 500;
212
213 IF SQL%NOTFOUND THEN
214 EXIT;
215 END IF;
216 COMMIT;
217 END LOOP;
218
219 --Delete from QP_ARCH_LIST_HEADERS_B
220
221 LOOP
222 DELETE QP_ARCH_LIST_HEADERS_B WHERE ARCH_PURG_REQUEST_ID in (select c.request_id from
223 QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
224 where c.request_id = a.request_id and
225 a.REQUEST_ID = b.REQUEST_ID
226 and c.purge_flag = 'N'
227 and c.REQUEST_TYPE = 'ARCHIVE'
228 and (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
229 and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity)))
230 AND rownum <= 500;
231
232 IF SQL%NOTFOUND THEN
233 EXIT;
234 END IF;
235 COMMIT;
236 END LOOP;
237
238 --Delete from QP_ARCH_LIST_LINES
239
240 LOOP
241 DELETE QP_ARCH_LIST_LINES WHERE ARCH_PURG_REQUEST_ID in (select c.request_id from
242 QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
243 where c.request_id = a.request_id and
244 a.REQUEST_ID = b.REQUEST_ID
245 and c.purge_flag = 'N'
246 and c.REQUEST_TYPE = 'ARCHIVE'
247 and (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
248 and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity)))
249 AND rownum <= 500;
250
251 IF SQL%NOTFOUND THEN
252 EXIT;
253 END IF;
254 COMMIT;
255 END LOOP;
256
257 --Delete from QP_ARCH_PRICING_ATTRIBUTES
258
259 LOOP
260 DELETE QP_ARCH_PRICING_ATTRIBUTES WHERE ARCH_PURG_REQUEST_ID in (select c.request_id from
261 QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
262 where c.request_id = a.request_id and
263 a.REQUEST_ID = b.REQUEST_ID
264 and c.purge_flag = 'N'
265 and c.REQUEST_TYPE = 'ARCHIVE'
266 and (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
267 and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity)))
268 AND rownum <= 500;
269
270 IF SQL%NOTFOUND THEN
271 EXIT;
272 END IF;
273 COMMIT;
274 END LOOP;
275
276 --Delete from QP_ARCH_RLTD_MODIFIERS
277
278 LOOP
279 DELETE QP_ARCH_RLTD_MODIFIERS WHERE ARCH_PURG_REQUEST_ID in
280 (select c.request_id from
281 QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
282 where c.request_id = a.request_id and
283 a.REQUEST_ID = b.REQUEST_ID
284 and c.purge_flag = 'N'
285 and c.REQUEST_TYPE = 'ARCHIVE'
286 and (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
287 and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity)))
288 AND rownum <= 500;
289
290 IF SQL%NOTFOUND THEN
291 EXIT;
292 END IF;
293 COMMIT;
294 END LOOP;
295
296 --Delete from QP_ARCH_QUALIFIERS
297
298 LOOP
299
300 DELETE QP_ARCH_QUALIFIERS WHERE ARCH_PURG_REQUEST_ID in
301 (select c.request_id from
302 QP_ARCH_CRITERIA_LINES a ,
303 QP_ARCH_CRITERIA_LINES b ,
304 QP_ARCH_CRITERIA_HEADERS c
305 where c.request_id = a.request_id and
306 a.REQUEST_ID = b.REQUEST_ID
307 and c.purge_flag = 'N'
308 and c.REQUEST_TYPE = 'ARCHIVE'
309 and (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
310 and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity)))
311 AND rownum <= 500;
312
313 IF SQL%NOTFOUND THEN
314 EXIT;
315
316 END IF;
317 COMMIT;
318 END LOOP;
319
320 --Update the purge_flag in the QP_ARCH_CRITERIA_HEADERS table to Y
321
322 LOOP
323 update QP_ARCH_CRITERIA_HEADERS set PURGE_FLAG = 'Y' WHERE request_id in (select c.request_id from
324 QP_ARCH_CRITERIA_LINES a , QP_ARCH_CRITERIA_LINES b ,QP_ARCH_CRITERIA_HEADERS c
325 where c.request_id = a.request_id and
326 a.REQUEST_ID = b.REQUEST_ID
327 and c.purge_flag = 'N'
328 and c.REQUEST_TYPE = 'ARCHIVE'
329 and (a.parameter_name = 'ENTITY_TYPE' and a.parameter_value = p_entity_type)
330 and (b.parameter_name = 'ENTITY' and b.parameter_value = to_char(p_entity)))
331 AND rownum <= 500;
332
333 IF SQL%NOTFOUND THEN
334 EXIT;
335 END IF;
336 COMMIT;
337 END LOOP;
338
339
340 ELSE --entity_type and entity_id are null
341
342 --Check if start date and end date is provided
343
344
345 IF (p_archive_start_date is not null and p_archive_end_date is not null) THEN
346
347 --Get the count of records matching the purge criteria
348
349 SELECT count(*) into l_count FROM QP_ARCH_CRITERIA_HEADERS
350 WHERE trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date)) and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code;
351
352 IF l_count = 0 THEN
353 RAISE NO_DATA_FOUND;
354 END IF;
355
356 --Delete from QP_ARCH_LIST_HEADERS_TL
357
358 LOOP
359 DELETE QP_ARCH_LIST_HEADERS_TL
360 WHERE ARCH_PURG_REQUEST_ID in (select request_id from QP_ARCH_CRITERIA_HEADERS
361 where trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date))
362 and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code)
363 AND rownum <= 500;
364
365 IF SQL%NOTFOUND THEN
366 EXIT;
367 END IF;
368 COMMIT;
369 END LOOP;
370
371 --Delete from QP_ARCH_LIST_HEADERS_B
372
373 LOOP
374 DELETE QP_ARCH_LIST_HEADERS_B
375 WHERE ARCH_PURG_REQUEST_ID in (select request_id from QP_ARCH_CRITERIA_HEADERS
376 where trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date))
377 and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code)
378 AND rownum <= 500;
379
380 IF SQL%NOTFOUND THEN
381 EXIT;
382 END IF;
383 COMMIT;
384 END LOOP;
385
386 --Delete from QP_ARCH_LIST_LINES
387
388 LOOP
389 DELETE QP_ARCH_LIST_LINES
390 WHERE ARCH_PURG_REQUEST_ID in (select request_id from QP_ARCH_CRITERIA_HEADERS
391 where trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date))
392 and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code)
393 AND rownum <= 500;
394
395 IF SQL%NOTFOUND THEN
396 EXIT;
397 END IF;
398 COMMIT;
399 END LOOP;
400
401 --Delete from QP_ARCH_PRICING_ATTRIBUTES
402
403 LOOP
404 DELETE QP_ARCH_PRICING_ATTRIBUTES
405 WHERE ARCH_PURG_REQUEST_ID in (select request_id from QP_ARCH_CRITERIA_HEADERS
406 where trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date))
407 and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code)
408 AND rownum <= 500;
409
410 IF SQL%NOTFOUND THEN
411 EXIT;
412 END IF;
413 COMMIT;
414 END LOOP;
415
416 --Delete from QP_ARCH_RLTD_MODIFIERS
417
418 LOOP
419 DELETE QP_ARCH_RLTD_MODIFIERS
420 WHERE ARCH_PURG_REQUEST_ID in (select request_id from QP_ARCH_CRITERIA_HEADERS
421 where trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date))
422 and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code)
423 AND rownum <= 500;
424
425 IF SQL%NOTFOUND THEN
426 EXIT;
427 END IF;
428 COMMIT;
429 END LOOP;
430
431 --Delete from QP_ARCH_QUALIFIERS
432
433 LOOP
434 DELETE QP_ARCH_QUALIFIERS
435 WHERE ARCH_PURG_REQUEST_ID in (select request_id from QP_ARCH_CRITERIA_HEADERS
436 where trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date))
437 and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code)
438 AND rownum <= 500;
439
440 IF SQL%NOTFOUND THEN
441 EXIT;
442 END IF;
443 COMMIT;
444 END LOOP;
445
446 --Update the purge_flag in the QP_ARCH_CRITERIA_HEADERS table to Y
447
448 LOOP
449 update QP_ARCH_CRITERIA_HEADERS set PURGE_FLAG = 'Y'
450 WHERE request_id in (select request_id from QP_ARCH_CRITERIA_HEADERS
451 where trunc(creation_date) between trunc(fnd_date.canonical_to_date(p_archive_start_date))
452 and trunc(fnd_date.canonical_to_date(p_archive_end_date)) and purge_flag = 'N' and REQUEST_TYPE = 'ARCHIVE' and source_system= p_source_system_code)
453 AND rownum <= 500;
454
455 IF SQL%NOTFOUND THEN
456 EXIT;
457 END IF;
458 COMMIT;
459 END LOOP;
460
461
462 END IF; -- Dates
463 END IF; -- Entity Type and Entity
464 END IF; -- Archive name.
465
466
467 commit;
468
469 fnd_file.put_line(FND_FILE.LOG,'Purge completed successfully');
470
471 --Call INSERT_CRITERIA to insert records into QP_ARCH_CRITERIA_HEADERS and QP_ARCH_CRITERIA_LINES
472
473 INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,
474 p_archive_start_date,p_archive_end_date,
475 l_user_id,l_conc_request_id,'S');
476
477 errbuf := '';
478 retcode := 0;
479
480 EXCEPTION
481 WHEN NO_DATA_FOUND THEN
482
486 p_archive_start_date,p_archive_end_date,
483 --Call INSERT_CRITERIA to insert records into QP_ARCH_CRITERIA_HEADERS and QP_ARCH_CRITERIA_LINES
484
485 INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,
487 l_user_id,l_conc_request_id,'W');
488
489 fnd_file.put_line(FND_FILE.LOG,'No Data Found - 0 Records Deleted');
490 errbuf := 'No Data Found - 0 Records Deleted';
491 retcode := 1;
492
493 WHEN OTHERS THEN
494
495 --Call INSERT_CRITERIA to insert records into QP_ARCH_CRITERIA_HEADERS and QP_ARCH_CRITERIA_LINES
496
497 INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,
498 p_archive_start_date,p_archive_end_date,
499 l_user_id,l_conc_request_id,'F');
500
501 fnd_file.put_line(FND_FILE.LOG,'Error in Purge Entity Routine ');
502 fnd_file.put_line(FND_FILE.LOG,substr(sqlerrm,1,300));
503 retcode := 2;
504
505 END Purge_entity;
506
507 END QP_PURGE_ENTITY;
[Home] [Help]