[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_PURGE_PVT
Source
1 PACKAGE BODY AMS_List_Purge_PVT AS
2 /* $Header: amsvimcb.pls 120.5 2006/04/05 06:12:05 bmuthukr ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_List_Purge_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvimcb.pls';
6
7
8
9 /*****************************************************************************/
10 -- Procedure
11 -- Purge_Expired_List_Headers
12 --
13 -- Purpose
14 -- Purge imported list headers which is expired or has usage as 0 or less
15 --
16 -- Note
17 --
18 -- History
19 -- 05/18/2001 yxliu created
20 -- 12/12/2001 yxliu add logic to purge ams_list_entries
21 -- add parameter force_purge_flag
22 -- 01/10/2002 yxliu add delete cancelled imp list headers
23 -------------------------------------------------------------------------------
24 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
25 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
26 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
27
28 PROCEDURE Purge_Expired_List_Headers
29 (
30 force_purge_flag IN VARCHAR2 := FND_API.g_false,
31 x_return_status OUT NOCOPY VARCHAR2,
32 x_msg_count OUT NOCOPY NUMBER,
33 x_msg_data OUT NOCOPY VARCHAR2
34 )
35 IS
36 l_api_name CONSTANT VARCHAR2(30) := 'Purge_Expired_List_Headers';
37
38 CURSOR c_all_imp_list_rec IS
39 SELECT import_list_header_id, object_version_number,
40 creation_date, expiry_date, usage
41 FROM ams_imp_list_headers_all
42 WHERE rented_list_flag = 'R'
43 and status_code <> 'PURGED';
44
45 -- l_all_imp_list_rec c_all_imp_list_rec%ROWTYPE;
46
47 l_import_list_header_id NUMBER;
48 l_object_version NUMBER;
49 l_creation_date DATE;
50 l_expiry_date DATE;
51 l_usage NUMBER;
52
53 l_grace_date DATE;
54 l_lookup_code VARCHAR2(30);
55 l_arc_log_used_by VARCHAR2(30) := 'IMPH';
56 l_upd_status_code VARCHAR2(30);
57
58 CURSOR c_list_entries_rec_int (c_imp_header_id NUMBER) IS
59 SELECT DISTINCT b.list_header_id
60 FROM ams_list_select_actions a, ams_list_headers_all b
61 WHERE a.incl_object_id = c_imp_header_id
62 AND a.arc_incl_object_from = 'IMPH'
63 AND a.action_used_by_id = b.list_header_id
64 AND (b.list_type <> 'TARGET' OR b.status_code <> 'LOCKED');
65
66 CURSOR c_list_entries_force_rec_int (c_imp_header_id NUMBER) IS
67 SELECT DISTINCT b.list_header_id
68 FROM ams_list_select_actions a, ams_list_headers_all b
69 WHERE a.incl_object_id = c_imp_header_id
70 AND a.arc_incl_object_from = 'IMPH'
71 AND a.action_used_by_id = b.list_header_id;
72
73 CURSOR c_list_entries_rec1 (c_imp_header_id NUMBER, c_list_header_id NUMBER) IS
74 SELECT a.list_header_id, a.list_entry_id
75 FROM ams_list_entries a, ams_imp_source_lines b
76 WHERE a.list_header_id = c_list_header_id
77 AND a.imp_source_line_id = b.import_source_line_id
78 AND b.import_list_header_id = c_imp_header_id
79 ORDER BY a.list_header_id;
80
81 l_list_entries_rec c_list_entries_rec1%ROWTYPE;
82
83 CURSOR c_list_entries_rec2 (c_list_header_id NUMBER, c_imp_header_id NUMBER, c_usage NUMBER) IS
84 SELECT a.list_header_id, a.list_entry_id
85 FROM ams_list_entries a, ams_imp_source_lines b
86 WHERE a.list_header_id = c_list_header_id
87 AND a.imp_source_line_id = b.import_source_line_id
88 AND b.import_list_header_id = c_imp_header_id
89 AND b.current_usage >= c_usage
90 ORDER BY a.list_header_id;
91
92 l_list_header_id_temp NUMBER := -1;
93 l_list_size_temp NUMBER := 0;
94
95 l_list_entry_tbl t_rec_table;
96 l_list_header_tbl t_rec_table;
97 l_list_size_tbl t_rec_table;
98 l_entry_iterator NUMBER := 1;
99 l_list_iterator NUMBER := 0;
100
101 l_force_purge_flag VARCHAR2(1) := 'N'; -- default not to force purge
102
103 CURSOR c_usage_rec ( p_imp_header_id NUMBER) IS
104 SELECT current_usage
105 FROM ams_imp_source_lines
106 WHERE import_list_header_id = p_imp_header_id;
107
108 CURSOR c_get_usr_status (p_lookup_code VARCHAR2) IS
109 SELECT user_status_id
110 FROM ams_user_statuses_b
111 WHERE system_status_code = p_lookup_code
112 AND system_status_type = 'AMS_IMPORT_STATUS';
113
114 l_current_usage NUMBER := null;
115 l_status_id NUMBER := 0;
116 l_list_header_id NUMBER;
117 BEGIN
118 IF (AMS_DEBUG_HIGH_ON) THEN
119
120 AMS_Utility_PVT.Debug_Message(l_api_name||': Start ');
121 END IF;
122
123 x_return_status := FND_API.G_RET_STS_SUCCESS;
124
125 l_lookup_code := 'PURGED';
126
127 IF force_purge_flag = 'Y' THEN
128 l_force_purge_flag := force_purge_flag;
129 END IF;
130
131 -- Get all import list headers
132 FOR l_all_imp_list_rec IN c_all_imp_list_rec
133 LOOP
134 l_import_list_header_id := l_all_imp_list_rec.import_list_header_id;
135 l_object_version := l_all_imp_list_rec.object_version_number;
136 l_creation_date := l_all_imp_list_rec.creation_date;
137 l_expiry_date := l_all_imp_list_rec.expiry_date;
138 l_usage := l_all_imp_list_rec.usage;
139
140 -- Create the Savepoint
141 SAVEPOINT Purge_Expired_List_Header;
142
143 IF (AMS_DEBUG_HIGH_ON) THEN
144 AMS_Utility_PVT.Debug_Message(l_api_name||': l_import_list_header_id:' || l_import_list_header_id );
145 AMS_Utility_PVT.Debug_Message(l_api_name||': l_expiry_date:' || l_expiry_date );
146 AMS_Utility_PVT.Debug_Message(l_api_name||': l_usage:' || l_usage );
147 AMS_Utility_PVT.Debug_Message(l_api_name||': l_force_purge_flag:' || l_force_purge_flag );
148 END IF;
149
150 IF l_expiry_date IS NULL AND l_usage IS NULL THEN
151 -- get grace period from profile, default 60
152 l_grace_date := l_creation_date + NVL(fnd_profile.VALUE('AMS_BUDGET_ADJ_GRACE_PERIOD'), 60);
153
154 IF l_grace_date <= SYSDATE THEN
155
156 IF (AMS_DEBUG_HIGH_ON) THEN
157 AMS_Utility_PVT.Debug_Message(l_api_name||': ' || l_import_list_header_id ||' grace period passed');
158 END IF;
159
160 -- Delete from ams_imp_source_lines
161 IF (AMS_DEBUG_HIGH_ON) THEN
162 AMS_Utility_PVT.debug_message(l_api_name||': delete from source lines');
163 END IF;
164 DELETE FROM ams_imp_source_lines
165 WHERE import_list_header_id = l_import_list_header_id;
166 END IF;
167 ELSIF l_expiry_date IS NOT NULL AND l_expiry_date <= SYSDATE THEN
168
169 IF (AMS_DEBUG_HIGH_ON) THEN
170 AMS_Utility_PVT.Debug_Message(l_api_name||': ' || l_import_list_header_id ||' expiry date passed');
171 END IF;
172
173 -- Add logic to delete from ams_list_entries
174 IF (AMS_DEBUG_HIGH_ON) THEN
175 AMS_Utility_PVT.debug_message(l_api_name||': delete from list entries');
176 END IF;
177
178 IF l_force_purge_flag <> 'Y' THEN
179 IF (AMS_DEBUG_HIGH_ON) THEN
180 AMS_Utility_PVT.debug_message(l_api_name||': non force purge');
181 END IF;
182 OPEN c_list_entries_rec_int(l_import_list_header_id);
183 LOOP
184 FETCH c_list_entries_rec_int INTO l_list_header_id;
185 EXIT WHEN c_list_entries_rec_int%NOTFOUND;
186
187 OPEN c_list_entries_rec1(l_import_list_header_id,l_list_header_id);
188 LOOP
189 FETCH c_list_entries_rec1 INTO l_list_entries_rec;
190 EXIT WHEN c_list_entries_rec1%NOTFOUND;
191
192 l_list_entry_tbl(l_entry_iterator) := l_list_entries_rec.list_entry_id;
193 l_entry_iterator := l_entry_iterator + 1;
194
195 IF l_list_header_id_temp = -1 THEN
196 l_list_header_id_temp := l_list_entries_rec.list_header_id;
197 l_list_size_temp := 1;
198 l_list_iterator := 1;
199 ELSIF l_list_entries_rec.list_header_id = l_list_header_id_temp THEN
200 l_list_size_temp := l_list_size_temp + 1;
201 ELSE
202 l_list_header_tbl(l_list_iterator) := l_list_header_id_temp;
203 l_list_size_tbl(l_list_iterator) := l_list_size_temp;
204 l_list_iterator := l_list_iterator + 1;
205 l_list_header_id_temp := l_list_entries_rec.list_header_id;
206 l_list_size_temp := 1;
207 END IF;
208 END LOOP;
209 CLOSE c_list_entries_rec1;
210 END LOOP;
211 CLOSE c_list_entries_rec_int;
212
213 IF l_list_header_id_temp <> -1 THEN
214 l_list_header_tbl(l_list_iterator) := l_list_header_id_temp;
215 l_list_size_tbl(l_list_iterator) := l_list_size_temp;
216 END IF;
217 ELSE -- force purge
218 IF (AMS_DEBUG_HIGH_ON) THEN
219 AMS_Utility_PVT.debug_message(l_api_name||': force purge expiry passed');
220 AMS_Utility_PVT.debug_message(l_api_name||': force purge expiry passed l_import_list_header_id:'||l_import_list_header_id);
221 END IF;
222
223 OPEN c_list_entries_force_rec_int(l_import_list_header_id);
224 LOOP
225 FETCH c_list_entries_force_rec_int INTO l_list_header_id;
226 EXIT WHEN c_list_entries_force_rec_int%NOTFOUND;
227
228 OPEN c_list_entries_rec1(l_import_list_header_id,l_list_header_id);
229 LOOP
230 FETCH c_list_entries_rec1 INTO l_list_entries_rec;
231 EXIT WHEN c_list_entries_rec1%NOTFOUND;
232
233 l_list_entry_tbl(l_entry_iterator) := l_list_entries_rec.list_entry_id;
234 l_entry_iterator := l_entry_iterator + 1;
235
236 IF l_list_header_id_temp = -1 THEN
237 l_list_header_id_temp := l_list_entries_rec.list_header_id;
238 l_list_size_temp := 1;
239 l_list_iterator := 1;
240 ELSIF l_list_entries_rec.list_header_id = l_list_header_id_temp THEN
241 l_list_size_temp := l_list_size_temp + 1;
242 ELSE
243 l_list_header_tbl(l_list_iterator) := l_list_header_id_temp;
244 l_list_size_tbl(l_list_iterator) := l_list_size_temp;
245 l_list_iterator := l_list_iterator + 1;
246 l_list_header_id_temp := l_list_entries_rec.list_header_id;
247 l_list_size_temp := 1;
248 END IF;
249 END LOOP;
250 CLOSE c_list_entries_rec1;
251 END LOOP;
252 CLOSE c_list_entries_force_rec_int;
253
254 IF l_list_header_id_temp <> -1 THEN
255 l_list_header_tbl(l_list_iterator) := l_list_header_id_temp;
256 l_list_size_tbl(l_list_iterator) := l_list_size_temp;
257 END IF;
258 END IF;
259
260 IF (AMS_DEBUG_HIGH_ON) THEN
261 AMS_Utility_PVT.debug_message(l_api_name||': bulk update on entries');
262 AMS_Utility_PVT.debug_message('After collecting the data l_entry_iterator:'||l_entry_iterator);
263 AMS_Utility_PVT.debug_message('After collecting the data l_list_entry_tbl.count:'||l_list_entry_tbl.count);
264 END IF;
265
266 -- Do bulk delete from list entries
267 IF l_entry_iterator > 1 THEN
268 FORALL i IN l_list_entry_tbl.first .. l_list_entry_tbl.last
269 DELETE FROM ams_list_entries
270 WHERE list_entry_id = l_list_entry_tbl(i);
271 END IF;
272
273 IF (AMS_DEBUG_HIGH_ON) THEN
274 AMS_Utility_PVT.debug_message(l_api_name||': bulk update on list headers');
275 END IF;
276 -- Do bulk update on list headers
277 --IF l_list_iterator > 0 THEN
278 IF l_list_size_tbl.last > 0
279 THEN
280 FORALL i IN l_list_size_tbl.first .. l_list_size_tbl.last
281 UPDATE ams_list_headers_all
282 SET (no_of_rows_in_list
283 , no_of_rows_active
284 , last_update_date )=(select count(1),
285 sum(decode(enabled_flag,'Y',1,0)),
286 sysdate
287 from ams_list_entries
288 where list_header_id = l_list_header_tbl(i) )
289 WHERE list_header_id = l_list_header_tbl(i);
290 END IF;
291
292 -- Delete from ams_imp_source_lines
293 DELETE FROM ams_imp_source_lines
294 WHERE import_list_header_id = l_import_list_header_id;
295 IF (AMS_DEBUG_HIGH_ON) THEN
296 AMS_Utility_PVT.debug_message(l_api_name||': force purge expiry_date passed count of ams_imp_source_lines_deleted:'||sql%rowcount);
297 END IF;
298
299 -- Delete from ams_party_sources
300 DELETE FROM ams_party_sources
301 WHERE import_list_header_id = l_import_list_header_id
302 AND used_flag = 'N';
303 IF (AMS_DEBUG_HIGH_ON) THEN
304 AMS_Utility_PVT.debug_message(l_api_name||': force purge expiry_date passed count of ams_party_sources:'||sql%rowcount);
305 END IF;
306 ELSIF l_usage IS NOT NULL THEN -- check usage
307 IF (AMS_DEBUG_HIGH_ON) THEN
308 AMS_Utility_PVT.Debug_Message(l_api_name||': ' || l_import_list_header_id || ': usage is ' ||l_usage);
309 END IF;
310
311 -- Add logic to delete from ams_list_entries
312 IF (AMS_DEBUG_HIGH_ON) THEN
313 AMS_Utility_PVT.debug_message(l_api_name||': delete from list entries:' || l_force_purge_flag);
314 END IF;
315
316 IF l_force_purge_flag <> 'Y' THEN
317 IF (AMS_DEBUG_HIGH_ON) THEN
318 AMS_Utility_PVT.debug_message(l_api_name||': non force purge');
319 END IF;
320
321 OPEN c_list_entries_rec_int(l_import_list_header_id);
322 LOOP
323 FETCH c_list_entries_rec_int INTO l_list_header_id;
324 EXIT WHEN c_list_entries_rec_int%NOTFOUND;
325 OPEN c_list_entries_rec2(l_list_header_id, l_import_list_header_id, l_usage);
326 LOOP
327 FETCH c_list_entries_rec2 INTO l_list_entries_rec;
328 EXIT WHEN c_list_entries_rec2%NOTFOUND;
329 l_list_entry_tbl(l_entry_iterator) := l_list_entries_rec.list_entry_id;
330 l_entry_iterator := l_entry_iterator + 1;
331
332 IF l_list_header_id_temp = -1 THEN
333 l_list_header_id_temp := l_list_entries_rec.list_header_id;
334 l_list_size_temp := 1;
335 l_list_iterator := 1;
336 ELSIF l_list_entries_rec.list_header_id = l_list_header_id_temp THEN
337 l_list_size_temp := l_list_size_temp + 1;
338 ELSE
339 l_list_header_tbl(l_list_iterator) := l_list_header_id_temp;
340 l_list_size_tbl(l_list_iterator) := l_list_size_temp;
341 l_list_iterator := l_list_iterator + 1;
342 l_list_header_id_temp := l_list_entries_rec.list_header_id;
343 l_list_size_temp := 1;
344 END IF;
345 END LOOP;
346 CLOSE c_list_entries_rec2;
347 END LOOP;
348 CLOSE c_list_entries_rec_int;
349
350 IF l_list_header_id_temp <> -1 THEN
351 l_list_header_tbl(l_list_iterator) := l_list_header_id_temp;
352 l_list_size_tbl(l_list_iterator) := l_list_size_temp;
353 END IF;
354 ELSE
355 IF (AMS_DEBUG_HIGH_ON) THEN
356 AMS_Utility_PVT.debug_message(l_api_name||': usage is not null and force purge');
357 END IF;
358
359 OPEN c_list_entries_force_rec_int(l_import_list_header_id);
360 LOOP
361 FETCH c_list_entries_force_rec_int INTO l_list_header_id;
362 EXIT WHEN c_list_entries_force_rec_int%NOTFOUND;
363
364 OPEN c_list_entries_rec2(l_list_header_id, l_import_list_header_id, l_usage);
365 LOOP
366 FETCH c_list_entries_rec2 INTO l_list_entries_rec;
367 EXIT WHEN c_list_entries_rec2%NOTFOUND;
368
369 l_list_entry_tbl(l_entry_iterator) := l_list_entries_rec.list_entry_id;
370 l_entry_iterator := l_entry_iterator + 1;
371
372 IF l_list_header_id_temp = -1 THEN
373 l_list_header_id_temp := l_list_entries_rec.list_header_id;
374 l_list_size_temp := 1;
375 ELSIF l_list_entries_rec.list_header_id = l_list_header_id_temp THEN
376 l_list_size_temp := l_list_size_temp + 1;
377 ELSE
378 l_list_header_tbl(l_list_iterator) := l_list_header_id_temp;
379 l_list_size_tbl(l_list_iterator) := l_list_size_temp;
380 l_list_iterator := l_list_iterator + 1;
381 l_list_header_id_temp := l_list_entries_rec.list_header_id;
382 l_list_size_temp := 1;
383 END IF;
384 END LOOP;
385 CLOSE c_list_entries_rec2;
386 END LOOP;
387 CLOSE c_list_entries_force_rec_int;
388
389 IF l_list_header_id_temp <> -1 THEN
390 l_list_header_tbl(l_list_iterator) := l_list_header_id_temp;
391 l_list_size_tbl(l_list_iterator) := l_list_size_temp;
392 END IF;
393 END IF;
394 -- Do bulk delete from list entries
395 IF (AMS_DEBUG_HIGH_ON) THEN
396 AMS_Utility_PVT.debug_message(l_api_name||': l_entry_iterator=' || l_entry_iterator);
397 AMS_Utility_PVT.debug_message(l_api_name||': l_list_entry_tbl.count=' || l_list_entry_tbl.count);
398 END IF;
399 IF l_entry_iterator > 1 THEN
400 FORALL i IN l_list_entry_tbl.first .. l_list_entry_tbl.last
401 DELETE FROM ams_list_entries
402 WHERE list_entry_id = l_list_entry_tbl(i);
403 END IF;
404 IF (AMS_DEBUG_HIGH_ON) THEN
405 AMS_Utility_PVT.debug_message(l_api_name||': bulk update on list headers');
406 END IF;
407 IF (AMS_DEBUG_HIGH_ON) THEN
408 AMS_Utility_PVT.debug_message(l_api_name||': l_list_size_tbl.last=' || l_list_size_tbl.last);
409 END IF;
410 -- Do bulk update on list headers
411 --IF l_list_iterator > 0 THEN
412 IF l_list_size_tbl.last > 0 THEN
413 FORALL i IN l_list_size_tbl.first .. l_list_size_tbl.last
414 UPDATE ams_list_headers_all
415 SET (no_of_rows_in_list
416 , no_of_rows_active
417 , last_update_date )=(select count(1),
418 sum(decode(enabled_flag,'Y',1,0)),
419 sysdate
420 from ams_list_entries
421 where list_header_id = l_list_header_tbl(i) )
422 WHERE list_header_id = l_list_header_tbl(i);
423 END IF;
424
425 IF (AMS_DEBUG_HIGH_ON) THEN
426 AMS_Utility_PVT.debug_message(l_api_name||': deleting from ams_party_sources');
427 END IF;
428
429 -- Delete from ams_party_sources
430 DELETE FROM ams_party_sources
431 WHERE used_flag = 'N'
432 AND import_source_line_id IN (
433 SELECT import_source_line_id
434 FROM ams_imp_source_lines
435 WHERE import_list_header_id = l_import_list_header_id
436 AND current_usage >= l_usage);
437 IF (AMS_DEBUG_HIGH_ON) THEN
438 AMS_Utility_PVT.debug_message(l_api_name||': count of ams_party_sources deleted:'||sql%rowcount);
439 END IF;
440
441 IF (AMS_DEBUG_HIGH_ON) THEN
442 AMS_Utility_PVT.debug_message(l_api_name||': deleting from ams_imp_source_lines');
443 END IF;
444
445 -- Delete from ams_imp_source_lines
446 DELETE FROM ams_imp_source_lines
447 WHERE import_list_header_id = l_import_list_header_id
448 AND current_usage >= l_usage;
449
450 END IF;
451
452 IF (AMS_DEBUG_HIGH_ON) THEN
453 AMS_Utility_PVT.debug_message(l_api_name||': count of ams_imp_source_lines deleted:'||sql%rowcount);
454 AMS_Utility_PVT.Debug_Message(l_api_name||': Delete log and update ams_imp_list_headers_all ');
455 END IF;
456
457 OPEN C_get_usr_status(l_lookup_code);
458 FETCH C_get_usr_status INTO l_status_id;
459 CLOSE C_get_usr_status;
460
461 l_upd_status_code := NULL;
462 -- Update ams_imp_list_headers_all, only when all the lines are purged.
463 UPDATE ams_imp_list_headers_all a
464 SET status_code = l_lookup_code,
465 user_status_id = l_status_id,
466 status_date = SYSDATE,
467 object_version_number = l_object_version + 1,
468 last_update_date = SYSDATE,
469 last_updated_by = FND_GLOBAL.user_id
470 WHERE a.import_list_header_id = l_import_list_header_id
471 AND a.object_version_number = l_object_version
472 AND NOT EXISTS (
473 SELECT import_source_line_id
474 FROM ams_imp_source_lines b
475 WHERE b.import_list_header_id = a.import_list_header_id)
476 RETURNING status_code INTO l_upd_status_code;
477
478 IF (AMS_DEBUG_HIGH_ON) THEN
479 AMS_Utility_PVT.Debug_Message(l_api_name||': l_upd_status_code=' || l_upd_status_code);
480 END IF;
481 IF l_upd_status_code = l_lookup_code
482 THEN
483 -- Delete from ams_act_logs
484 DELETE FROM ams_act_logs
485 WHERE arc_act_log_used_by = l_arc_log_used_by
486 AND act_log_used_by_id = l_import_list_header_id;
487 IF (AMS_DEBUG_HIGH_ON) THEN
488 AMS_Utility_PVT.debug_message(l_api_name||': count of ams_act_logs deleted:'||sql%rowcount);
489 END IF;
490 END IF;
491
492 COMMIT WORK; --commit after every import header purge
493
494 --Reset all temp vars/tabs
495 l_list_header_id_temp := -1;
496 l_list_size_temp := 0;
497
498 l_list_entry_tbl.delete;
499 l_list_header_tbl.delete;
500 l_list_size_tbl.delete;
501 l_entry_iterator := 1;
502 l_list_iterator := 0;
503
504 END LOOP;
505
506 IF (AMS_DEBUG_HIGH_ON) THEN
507
508
509
510 AMS_Utility_PVT.Debug_Message(l_api_name||': Delete cancelld imp list headers ');
511
512 END IF;
513 DELETE FROM ams_imp_list_headers_all
514 WHERE status_code = 'CANCELLED';
515
516 -- If no errors, commit the work
517 COMMIT WORK;
518 IF (AMS_DEBUG_HIGH_ON) THEN
519 AMS_Utility_PVT.Debug_Message(l_api_name||': End ');
520 END IF;
521
522 EXCEPTION
523
524 WHEN OTHERS THEN
525 IF (AMS_DEBUG_HIGH_ON) THEN
526
527 AMS_Utility_PVT.Debug_Message(l_api_name||' failed on ' ||l_import_list_header_id );
528 AMS_Utility_PVT.Debug_Message(l_api_name|| SQLERRM||'-'||SQLCODE);
529
530 END IF;
531 IF (c_all_imp_list_rec%ISOPEN) THEN
532 CLOSE c_all_imp_list_rec;
533 END IF;
534 ROLLBACK TO Purge_Expired_List_Header;
535 x_return_status := FND_API.g_ret_sts_unexp_error;
536 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
537 FND_MESSAGE.set_name ('AMS', 'AMS_IMP_ERR_PURGE');
538 FND_MSG_PUB.add;
539 END IF;
540
541 END Purge_Expired_List_Headers;
542
543 /*****************************************************************************/
544 -- Procedure
545 -- Purge_List_Import
546 --
547 -- Purpose
548 -- This procedure is created to as a concurrent program which
549 -- will call the Purge_Expired_List_Headers and will return errors if any
550 --
551 -- Notes
552 --
553 --
554 -- History
555 -- 05/18/2001 yxliu created
556 ------------------------------------------------------------------------------
557
558 PROCEDURE Purge_List_Import
559 (
560 errbuf OUT NOCOPY VARCHAR2,
561 retcode OUT NOCOPY NUMBER,
562 force_purge_flag in VARCHAR2 := FND_API.G_FALSE
563 )
564 IS
565 l_return_status VARCHAR2(1) ;
566 l_msg_count NUMBER ;
567 l_msg_data VARCHAR2(2000);
568 BEGIN
569
570 FND_MSG_PUB.initialize;
571 -- Call the procedure to purge expired list headers
572
573 Purge_Expired_List_Headers
574 ( force_purge_flag => force_purge_flag,
575 x_return_status => l_return_status,
576 x_msg_count => l_msg_count,
577 x_msg_data => l_msg_data);
578
579 -- Write_log ;
580 Ams_Utility_Pvt.Write_Conc_log ;
581 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
582 retcode := 0;
583 ELSE
584 retcode := 1;
585 errbuf := l_msg_data ;
586 END IF;
587 END Purge_List_Import;
588
589 /*****************************************************************************/
590 -- Procedure
591 -- Purge_Purged_Target
592 --
593 -- Purpose
594 -- Purge target group list headers which has purge_flag = Y and
595 -- send_out_date has passed
596 --
597 -- Note
598 --
599 -- History
600 -- 05/21/2001 yxliu created
601 -------------------------------------------------------------------------------
602 PROCEDURE Purge_Purged_Target
603 (
604 x_return_status OUT NOCOPY VARCHAR2,
605 x_msg_count OUT NOCOPY NUMBER,
606 x_msg_data OUT NOCOPY VARCHAR2
607 )
608 IS
609 l_api_name CONSTANT VARCHAR2(30) := 'Purge_Purged_Target';
610
611 CURSOR c_all_target_rec(p_list_type in VARCHAR2, p_list_status in VARCHAR2)
612 IS
613 SELECT list_header_id, status_code, object_version_number, purge_flag,
614 sent_out_date
615 FROM ams_list_headers_all
616 WHERE list_type = p_list_type
617 AND upper(status_code) <> p_list_status;
618
619
620 CURSOR c_get_user_status_id ( c_status_code in varchar2) IS
621 SELECT user_status_id
622 FROM ams_user_statuses_vl
623 WHERE system_status_type = 'AMS_LIST_STATUS'
624 AND system_status_code = c_status_code
625 AND enabled_flag = 'Y'
626 AND default_flag = 'Y';
627
628 l_all_target_rec c_all_target_rec%ROWTYPE;
629
630 l_list_header_id NUMBER;
631 l_purge_flag VARCHAR2(1);
632 l_sent_out_date DATE;
633 l_object_version NUMBER;
634 l_list_status VARCHAR2(30);
635 l_user_status_id NUMBER;
636
637 l_grace_date DATE;
638 l_lookup_type VARCHAR2(30);
639 l_lookup_status VARCHAR2(30);
640
641 l_return_status VARCHAR2(1);
642 l_msg_count NUMBER;
643 l_msg_data VARCHAR2(2000);
644
645 BEGIN
646 IF (AMS_DEBUG_HIGH_ON) THEN
647
648 AMS_Utility_PVT.Debug_Message(l_api_name||' Start ');
649 END IF;
650
651 x_return_status := FND_API.G_RET_STS_SUCCESS;
652
653 -- get lookup_code for type 'TARGET'
654 l_lookup_type := null;
655 SELECT lookup_code INTO l_lookup_type
656 FROM ams_lookups
657 WHERE lookup_type = 'AMS_LIST_TYPE'
658 AND lookup_code = 'TARGET';
659
660 -- get lookup_code for status 'ARCHIVED'
661 l_lookup_status := null;
662 SELECT lookup_code INTO l_lookup_status
663 FROM ams_lookups
664 WHERE lookup_type = 'AMS_LIST_STATUS'
665 AND lookup_code = 'ARCHIVED';
666
667
668
669 -- Get all target group list headers
670 OPEN c_all_target_rec(l_lookup_type, l_lookup_status);
671 LOOP
672 FETCH c_all_target_rec INTO l_all_target_rec;
673 EXIT WHEN c_all_target_rec%NOTFOUND;
674
675 l_list_header_id := l_all_target_rec.list_header_id;
676 l_list_status := l_all_target_rec.status_code;
677 l_purge_flag := l_all_target_rec.purge_flag;
678 l_sent_out_date := l_all_target_rec.sent_out_date;
679 l_object_version := l_all_target_rec.object_version_number;
680
681 -- Create the Savepoint
682 SAVEPOINT Purge_Purged_Target;
683
684 --IF l_purge_flag = 'Y' THEN -- No need to consider purge flag.
685
686 IF l_sent_out_date is NOT NULL THEN
687
688 -- get grace period from profile, default 30
689 l_grace_date := l_sent_out_date +
690 NVL(fnd_profile.VALUE('AMS_BUDGET_ADJ_GRACE_PERIOD'), 180); --should be defaulted to 180.
691 IF SYSDATE >= l_grace_date THEN
692 IF (AMS_DEBUG_HIGH_ON) THEN
693
694 AMS_Utility_PVT.Debug_Message(l_api_name||' Purge list header ID ' || l_list_header_id);
695 END IF;
696 -- Delete from ams_list_entries
697 DELETE FROM ams_list_entries
698 WHERE list_header_id = l_list_header_id;
699
700 -- initialize any default values
701
702 --Should set the values for the summary cols to zero. there wont be any entries in the list entries table.
703 /*
704 -- Update ams_list_headers_all
705 UPDATE ams_list_headers_all
706 SET (no_of_rows_in_list
707 , no_of_rows_active
708 , NO_OF_ROWS_DUPLICATES
709 , NO_OF_ROWS_INACTIVE
710 , last_update_date )=(select count(1),
711 sum(decode(enabled_flag,'Y',1,0)),
712 sum(decode(marked_as_duplicate_flag,'Y',1,0)),
713 sum(decode(enabled_flag,'Y',0,1)),
714 sysdate
715 from ams_list_entries
716 where list_header_id = l_list_header_id)
717
718 WHERE list_header_id = l_list_header_id;*/
719
720
721 OPEN c_get_user_status_id (l_lookup_status);
722 FETCH c_get_user_status_id INTO l_user_status_id;
723 CLOSE c_get_user_status_id ;
724
725 UPDATE ams_list_headers_all
726 SET no_of_rows_in_list = 0,
727 no_of_rows_active = 0,
728 no_of_rows_inactive = 0,
729 no_of_rows_in_ctrl_group = 0,
730 no_of_rows_random = 0,
731 no_of_rows_duplicates = 0,
732 no_of_rows_manually_entered = 0,
733 no_of_rows_suppressed = 0,
734 no_of_rows_fatigued = 0,
735 tca_failed_records = 0,
736 no_of_rows_initially_selected= 0,
737 object_version_number = l_object_version + 1,
738 status_code = l_lookup_status,
739 user_status_id = l_user_status_id,
740 status_date = SYSDATE,
741 archived_by = FND_GLOBAL.user_id,
742 archived_date = SYSDATE,
743 last_update_date = SYSDATE,
744 last_updated_by = FND_GLOBAL.user_id
745 WHERE list_header_id = l_list_header_id;
746 END IF; -- if sysdate >= l_grace_date
747 END IF; -- if l_sent_out_date is not null
748 -- END IF; -- if l_purged_flag = y
749
750 -- If no errors, commit the work
751 COMMIT WORK;
752
753 END LOOP;
754 CLOSE c_all_target_rec;
755
756 IF (AMS_DEBUG_HIGH_ON) THEN
757
758
759
760 AMS_Utility_PVT.Debug_Message(l_api_name||' End ');
761
762 END IF;
763
764 EXCEPTION
765 WHEN OTHERS THEN
766 IF (c_all_target_rec%ISOPEN) THEN
767 CLOSE c_all_target_rec;
768 END IF;
769 ROLLBACK TO Purge_Purged_Target;
770 x_return_status := FND_API.g_ret_sts_unexp_error;
771 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
772 FND_MESSAGE.set_name ('AMS', 'AMS_IMP_ERR_PURGE');
773 FND_MSG_PUB.add;
774 END IF;
775
776 END Purge_Purged_Target;
777
778 /*****************************************************************************/
779 -- Procedure
780 -- Purge_Target_Group
781 --
782 -- Purpose
783 -- This procedure is created to as a concurrent program which
784 -- will call the Purge_Purged_Target and will return errors if any
785 --
786 -- Notes
787 --
788 --
789 -- History
790 -- 05/21/2001 yxliu created
791 ------------------------------------------------------------------------------
792
793 PROCEDURE Purge_Target_Group
794 ( errbuf OUT NOCOPY VARCHAR2,
795 retcode OUT NOCOPY NUMBER
796 )
797 IS
798 l_return_status VARCHAR2(1) ;
799 l_msg_count NUMBER ;
800 l_msg_data VARCHAR2(2000);
801 BEGIN
802
803 FND_MSG_PUB.initialize;
804 -- Call the procedure to purge purged target
805
806 Purge_Purged_Target
807 ( x_return_status => l_return_status,
808 x_msg_count => l_msg_count,
809 x_msg_data => l_msg_data);
810
811 -- Write_log ;
812 Ams_Utility_Pvt.Write_Conc_log ;
813 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
814 retcode := 0;
815 ELSE
816 retcode := 1;
817 errbuf := l_msg_data ;
818 END IF;
819 END Purge_Target_Group;
820
821 /*****************************************************************************/
822 -- Procedure
823 -- Increase_Usage
824 --
825 -- Purpose
826 -- increase usage of related source lines by 1
827 --
828 -- Note
829 --
830 -- History
831 -- 12/13/2001 yxliu created
832 -------------------------------------------------------------------------------
833 PROCEDURE Increase_Usage
834 (
835 p_api_version IN NUMBER,
836 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
837 p_commit IN VARCHAR2 := FND_API.g_false,
838 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
839
840 x_return_status OUT NOCOPY VARCHAR2,
841 x_msg_count OUT NOCOPY NUMBER,
842 x_msg_data OUT NOCOPY VARCHAR2,
843 p_list_header_id IN NUMBER
844 )
845 IS
846
847 l_api_version CONSTANT NUMBER := 1.0;
848 l_api_name CONSTANT VARCHAR2(30) := 'Increase_Usage';
849 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
850
851
852 CURSOR c_source_line_rec(p_list_header_id IN NUMBER) IS
853 SELECT lines.import_source_line_id, lines.object_version_number,
854 lines.current_usage
855 FROM ams_imp_source_lines lines, ams_list_entries entries
856 WHERE entries.list_header_id = p_list_header_id
857 AND ENTRIES.enabled_flag = 'Y'
858 AND lines.import_source_line_id = entries.imp_source_line_id;
859
860 l_source_line_rec c_source_line_rec%ROWTYPE;
861
862 l_source_line_id_tbl t_rec_table;
863 l_current_usage_tbl t_rec_table;
864 l_object_version_tbl t_rec_table;
865 l_iterator NUMBER := 1;
866
867 BEGIN
868
869 SAVEPOINT increase_usage;
870
871 IF (AMS_DEBUG_HIGH_ON) THEN
872
873
874
875 AMS_Utility_PVT.Debug_Message(l_full_name||': Start ');
876
877 END IF;
878
879 IF FND_API.to_boolean(p_init_msg_list) THEN
880 FND_MSG_PUB.initialize;
881 END IF;
882
883 IF NOT FND_API.compatible_api_call(
884 l_api_version,
885 p_api_version,
886 l_api_name,
887 g_pkg_name
888 ) THEN
889 RAISE FND_API.g_exc_unexpected_error;
890 END IF;
891
892 x_return_status := FND_API.G_RET_STS_SUCCESS;
893
894 -- Get all related source line ids
895 OPEN c_source_line_rec(p_list_header_id);
896 LOOP
897 FETCH c_source_line_rec INTO l_source_line_rec;
898 EXIT WHEN c_source_line_rec%NOTFOUND;
899
900 l_source_line_id_tbl(l_iterator) := l_source_line_rec.import_source_line_id;
901 l_object_version_tbl(l_iterator) := l_source_line_rec.object_version_number + 1;
902 IF l_source_line_rec.current_usage IS NOT NULL THEN
903 l_current_usage_tbl(l_iterator) := l_source_line_rec.current_usage + 1;
904 ELSE
905 l_current_usage_tbl(l_iterator) := 1;
906 END IF;
907
908 l_iterator := l_iterator + 1;
909 END LOOP;
910 CLOSE c_source_line_rec;
911
912 IF l_iterator > 1 THEN
913 FORALL i IN l_source_line_id_tbl.first .. l_source_line_id_tbl.last
914 UPDATE ams_imp_source_lines SET
915 current_usage = l_current_usage_tbl(i)
916 , object_version_number = l_object_version_tbl(i)
917 , last_update_date = SYSDATE
918 , last_updated_by = FND_GLOBAL.user_id
919 , last_update_login = FND_GLOBAL.conc_login_id
920 WHERE import_source_line_id = l_source_line_id_tbl(i);
921 END IF;
922
923 IF FND_API.to_boolean(p_commit) THEN
924 COMMIT;
925 END IF;
926
927 FND_MSG_PUB.count_and_get(
928 p_encoded => FND_API.g_false,
929 p_count => x_msg_count,
930 p_data => x_msg_data
931 );
932
933 IF (AMS_DEBUG_HIGH_ON) THEN
934
935
936
937 AMS_Utility_PVT.debug_message(l_full_name ||': end');
938
939 END IF;
940
941 EXCEPTION
942 WHEN OTHERS THEN
943 IF (AMS_DEBUG_HIGH_ON) THEN
944
945 AMS_Utility_Pvt.Debug_Message('Error in increase usage '|| sqlerrm);
946 END IF;
947
948 IF(c_source_line_rec%ISOPEN)then
949 CLOSE c_source_line_rec;
950 END IF;
951
952 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
953
954 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
955 THEN
956 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
957 END IF;
958 FND_MSG_PUB.Count_AND_Get
959 ( p_count => x_msg_count,
960 p_data => x_msg_data,
961 p_encoded => FND_API.G_FALSE
962 );
963
964 END Increase_Usage;
965
966 PROCEDURE delete_list_info(p_id_tbl IN ams_list_purge_pvt.l_id_tbl%type,
967 x_return_status OUT NOCOPY VARCHAR2,
968 x_msg_count OUT NOCOPY NUMBER,
969 x_msg_data OUT NOCOPY VARCHAR2) is
970 BEGIN
971 x_return_status := FND_API.G_RET_STS_SUCCESS;
972
973 FORALL i in 1 .. p_id_tbl.count
974 DELETE from ams_act_logs
975 WHERE act_log_used_by_id = p_id_tbl(i);
976
977 FORALL i in 1 .. p_id_tbl.count
978 DELETE from ams_list_select_actions
979 WHERE action_used_by_id = p_id_tbl(i);
980
981 FORALL i in 1 .. p_id_tbl.count
982 DELETE from ams_list_rule_usages
983 WHERE list_header_id = p_id_tbl(i);
984
985 FORALL i in 1 .. p_id_tbl.count
986 DELETE from ams_query_condition_value value
987 WHERE EXISTS (SELECT 1
988 FROM ams_query_temp_inst_cond_assoc assoc,
989 ams_query_template_instance inst
990 WHERE assoc.template_instance_id = inst.template_instance_id
991 AND value.assoc_id = assoc.assoc_id
992 AND inst.instance_used_by_id = p_id_tbl(i));
993
994 FORALL i in 1 .. p_id_tbl.count
995 DELETE from AMS_QUERY_TEMP_INST_COND_ASSOC assoc
996 WHERE EXISTS (SELECT 1
997 FROM ams_query_template_instance inst
998 WHERE assoc.template_instance_id = inst.template_instance_id
999 AND inst.instance_used_by_id = p_id_tbl(i));
1000
1001 FORALL i in 1 .. p_id_tbl.count
1002 DELETE from ams_query_template_instance
1003 WHERE instance_used_by_id = p_id_tbl(i);
1004
1005 UPDATE ams_query_template_all qt
1006 SET in_use_flag = 'N'
1007 WHERE NOT EXISTS (SELECT 1
1008 FROM ams_query_template_instance inst
1009 WHERE qt.template_id = inst.template_id
1010 AND inst.instance_used_by_id is not null);
1011
1012
1013 UPDATE ams_query_condition cond
1014 SET condition_in_use_flag = 'N'
1015 WHERE NOT EXISTS (SELECT 1
1016 FROM ams_query_template_instance inst
1017 WHERE cond.template_id = inst.template_id
1018 AND inst.instance_used_by_id is not null);
1019
1020 FORALL i in 1 .. p_id_tbl.count
1021 DELETE from ams_list_headers_all
1022 WHERE list_header_id = p_id_tbl(i);
1023
1024 EXCEPTION
1025 WHEN others then
1026 x_return_status := 'E';
1027 x_msg_count := 1;
1028 x_msg_data := sqlcode||' '||sqlerrm;
1029 raise;
1030 END delete_list_info;
1031
1032 --bmuthukr added delete_list_manager and delete_list_worker procedures
1033 --for bug 5095777.
1034 PROCEDURE delete_list_manager (x_errbuf OUT NOCOPY VARCHAR2
1035 , x_retcode OUT NOCOPY VARCHAR2
1036 , p_list_header_id IN NUMBER
1037 , p_batch_size IN NUMBER DEFAULT 1000
1038 , p_num_workers IN NUMBER DEFAULT 3) IS
1039
1040 CURSOR c_get_list_header_status IS
1041 SELECT status_code
1042 FROM ams_list_headers_all
1043 WHERE list_header_id = p_list_header_id;
1044
1045 CURSOR c_is_used_in_sel IS
1046 SELECT list_header_id
1047 FROM ams_list_select_actions
1048 WHERE arc_incl_object_from='LIST'
1049 AND incl_object_id = p_list_header_id;
1050
1051 CURSOR c_is_used_in_act IS
1052 SELECT list_header_id
1053 FROM ams_act_lists
1054 WHERE list_act_type IN ('TARGET','LIST')
1055 AND list_header_id = p_list_header_id;
1056
1057 CURSOR c_get_list_headers(l_request_id number) IS
1058 SELECT list_header_id, list_name
1059 FROM ams_list_headers_vl
1060 WHERE request_id = l_request_id;
1061
1062 l_list_header_id number;
1063 l_list_header_status varchar2(100);
1064 x_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
1065 x_msg_data varchar2(2000);
1066 x_msg_count number;
1067 l_sel_id number := null;
1068 l_act_id number := null;
1069 l_errbuf varchar2(32767);
1070 l_retcode number;
1071 l_conc_request_id number;
1072 l_children_done boolean := false;
1073 --TYPE l_num_tbl IS table of number index by binary_integer;
1074 --l_header_id_tbl l_num_tbl;
1075 l_header_id_tbl ams_list_purge_pvt.l_id_tbl%type;
1076 type l_char_tbl is table of varchar2(1000) index by binary_integer;
1077 l_list_name_tbl l_char_tbl;
1078
1079 BEGIN
1080 fnd_file.put_line(fnd_file.log,'Execution of Delete List entries master concurrent program started.');
1081 l_conc_request_id := FND_GLOBAL.conc_request_id();
1082 fnd_file.put_line(fnd_file.log,'Concurrent request id is '||l_conc_request_id);
1083 if p_list_header_id is not null then
1084 fnd_file.put_line(fnd_file.log,'List header id is '||p_list_header_id);
1085 -- Do the in use checks here before updating
1086 -- if it fails go back...
1087 OPEN c_get_list_header_status;
1088 FETCH c_get_list_header_status INTO l_list_header_status;
1089 CLOSE c_get_list_header_status;
1090 if l_list_header_status = 'DELETED' then
1091 UPDATE ams_list_headers_all
1092 SET request_id = l_conc_request_id,user_status_id = 314
1093 WHERE list_header_id = p_list_header_id;
1094 fnd_file.put_line(fnd_file.log,'List header status is already DELETED');
1095 else
1096 fnd_file.put_line(fnd_file.log,'List header status is not DELETED. Checking if its in use');
1097 OPEN c_is_used_in_sel;
1098 FETCH c_is_used_in_sel into l_sel_id;
1099 CLOSE c_is_used_in_sel;
1100 OPEN c_is_used_in_act;
1101 FETCH c_is_used_in_act into l_act_id;
1102 CLOSE c_is_used_in_act;
1103 if ((l_sel_id is not null) or (l_act_id is not null)) then -- This list is in use. Dont proceed
1104 fnd_file.put_line(fnd_file.log,'This list is in use. Could not be deleted.');
1105 return;
1106 else
1107 fnd_file.put_line(fnd_file.log,'This list is not in use. Could be deleted.');
1108 UPDATE ams_list_headers_all
1109 SET request_id = l_conc_request_id, status_code = 'DELETED', user_status_id = 314
1110 WHERE list_header_id = p_list_header_id;
1111 end if;
1112 end if;
1113 else
1114 fnd_file.put_line(fnd_file.log,'No list header id passed. So all the entries for lists in DELETED status will be deleted');
1115 update ams_list_headers_all
1116 set request_id = l_conc_request_id
1117 where status_code = 'DELETED';
1118 end if;
1119 fnd_file.put_line(fnd_file.log,'Submitting sub requests');
1120 ad_conc_utils_pkg.submit_subrequests( x_errbuf => l_errbuf
1121 , x_retcode => l_retcode
1122 , x_workerconc_app_shortname => 'AMS'
1123 , x_workerconc_progname => 'AMSDEWKR'
1124 , x_batch_size => p_batch_size
1125 , x_num_workers => p_num_workers
1126 , x_argument4 => l_conc_request_id
1127 );
1128 if l_children_done then
1129 fnd_file.put_line(fnd_file.log,'children done');
1130 else
1131 fnd_file.put_line(fnd_file.log,'children not done');
1132 end if;
1133 l_children_done := FND_CONCURRENT.children_done ( parent_request_id => l_conc_request_id
1134 , recursive_flag => 'N'
1135 , interval => 15
1136 );
1137
1138 fnd_file.put_line(fnd_file.log,'Sub requests completed.');
1139
1140 fnd_file.put_line(fnd_file.log,'L ret code is '||l_retcode);
1141
1142 if l_children_done then
1143 fnd_file.put_line(fnd_file.log,'children done');
1144 else
1145 fnd_file.put_line(fnd_file.log,'children not done');
1146 end if;
1147
1148
1149 IF ((l_retcode <> ad_conc_utils_pkg.conc_fail) AND
1150 (l_children_done)) then
1151 fnd_file.put_line(fnd_file.log,'Entries deleted successfully. Need to delete from other tables.');
1152 OPEN c_get_list_headers(l_conc_request_id);
1153 LOOP
1154 fnd_file.put_line(fnd_file.log,'Entries deleted successfully. Need to delete from other tables. Total count of header rec is '||l_header_id_tbl.count);
1155 FETCH c_get_list_headers BULK COLLECT INTO l_header_id_tbl,l_list_name_tbl LIMIT 1000;
1156
1157 delete_list_info(p_id_tbl => l_header_id_tbl,
1158 x_return_status => x_return_status,
1159 x_msg_count => x_msg_count,
1160 x_msg_data => x_msg_data);
1161
1162 FOR i in 1 .. l_header_id_tbl.count
1163 LOOP
1164 fnd_file.put_line(fnd_file.log,'List '||l_list_name_tbl(i)||' is deleted');
1165 END LOOP;
1166
1167 COMMIT;
1168
1169 EXIT WHEN c_get_list_headers%NOTFOUND;
1170 END LOOP;
1171 CLOSE C_GET_LIST_HEADERS;
1172
1173 END IF;
1174 commit;
1175 fnd_file.put_line(fnd_file.log,'Delete Entries concurrent program executed successfully.');
1176 x_retcode := ad_conc_utils_pkg.conc_success;
1177 EXCEPTION
1178 WHEN OTHERS THEN
1179 fnd_file.put_line(fnd_file.log,'Error while executing Delete Entries concurrent program '||sqlerrm);
1180 x_retcode := ad_conc_utils_pkg.conc_fail;
1181 x_errbuf := SQLERRM;
1182 RAISE;
1183 END delete_list_manager;
1184
1185 PROCEDURE delete_list_worker( x_errbuf OUT NOCOPY VARCHAR2
1186 , x_retcode OUT NOCOPY VARCHAR2
1187 , x_batch_size IN NUMBER
1188 , x_worker_id IN NUMBER
1189 , x_num_workers IN NUMBER
1190 , x_argument4 IN VARCHAR2) IS
1191
1192 l_worker_id NUMBER;
1193 l_product VARCHAR2(30) := 'AMS';
1194 l_table_name VARCHAR2(30) := 'AMS_LIST_ENTRIES';
1195 l_update_name VARCHAR2(30);
1196 l_status VARCHAR2(30);
1197 l_industry VARCHAR2(30);
1198 l_restatus BOOLEAN;
1199 l_table_owner VARCHAR2(30);
1200 l_any_rows_to_process BOOLEAN;
1201 l_start_rowid ROWID;
1202 l_end_rowid ROWID;
1203 l_rows_processed NUMBER;
1204 BEGIN
1205
1206 l_restatus := fnd_installation.get_app_info ( l_product, l_status, l_industry, l_table_owner );
1207
1208 IF (( l_restatus = FALSE ) OR
1209 ( l_table_owner IS NULL))
1210 THEN
1211 RAISE_APPLICATION_ERROR(-20001, 'Cannot get schema name for product: '|| l_product );
1212 END IF;
1213
1214 FND_FILE.PUT_LINE( FND_FILE.LOG, 'X_Worker_Id: '|| x_worker_id );
1215 FND_FILE.PUT_LINE( FND_FILE.LOG, 'X_Num_Workers: '|| x_num_workers );
1216 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Concurrent request id is '|| x_argument4);
1217
1218
1219 l_update_name := x_argument4;
1220
1221 Begin
1222 ad_parallel_updates_pkg.initialize_rowid_range
1223 (
1224 ad_parallel_updates_pkg.ROWID_RANGE
1225 , l_table_owner
1226 , l_table_name
1227 , l_update_name
1228 , x_worker_id
1229 , x_num_workers
1230 , x_batch_size
1231 , 0
1232 );
1233
1234 ad_parallel_updates_pkg.get_rowid_range
1235 (
1236 l_start_rowid
1237 , l_end_rowid
1238 , l_any_rows_to_process
1239 , x_batch_size
1240 , TRUE
1241 );
1242
1243 WHILE ( l_any_rows_to_process = TRUE )
1244 LOOP
1245 DELETE /*+ rowid(entries) */
1246 AMS_LIST_ENTRIES entries
1247 WHERE list_header_id IN (select list_header_id from ams_list_headers_all
1248 where request_id = x_argument4)
1249 AND ROWID BETWEEN l_start_rowid AND l_end_rowid;
1250
1251 ad_parallel_updates_pkg.processed_rowid_range
1252 (
1253 l_rows_processed
1254 , l_end_rowid
1255 );
1256
1257 COMMIT;
1258
1259 ad_parallel_updates_pkg.get_rowid_range
1260 (
1261 l_start_rowid
1262 , l_end_rowid
1263 , l_any_rows_to_process
1264 , x_batch_size
1265 , FALSE
1266 );
1267 END LOOP;
1268 x_retcode := ad_conc_utils_pkg.conc_success;
1269 EXCEPTION
1270 WHEN OTHERS THEN
1271 x_retcode := ad_conc_utils_pkg.conc_fail;
1272 x_errbuf := SQLERRM;
1273 RAISE;
1274 END;
1275 EXCEPTION
1276 WHEN OTHERS THEN
1277 x_retcode := ad_conc_utils_pkg.conc_fail;
1278 x_errbuf := SQLERRM;
1279 RAISE;
1280 END delete_list_worker;
1281
1282 PROCEDURE delete_entries_soft (p_list_header_id_tbl IN AMS_LIST_PURGE_PVT.l_list_header_id_tbl%type,
1283 x_return_status OUT NOCOPY VARCHAR2,
1284 x_msg_count OUT NOCOPY NUMBER,
1285 x_msg_data OUT NOCOPY VARCHAR2) is
1286
1287 type num_tbl is table of number index by binary_integer;
1288 l_header_id_tbl num_tbl;
1289 l_request_id_tbl num_tbl;
1290
1291 BEGIN
1292 x_return_status := FND_API.G_RET_STS_SUCCESS;
1293
1294 FOR i in 1..p_list_header_id_tbl.count
1295 LOOP
1296 l_header_id_tbl(i) := p_list_header_id_tbl(i).l_list_header_id;
1297 END LOOP;
1298
1299 FORALL i in 1 .. l_header_id_tbl.count
1300 UPDATE ams_list_headers_all
1301 SET status_code = 'DELETED', user_status_id = 314
1302 WHERE list_header_id = l_header_id_tbl(i);
1303
1304 COMMIT;
1305
1306 FOR i in 1 .. p_list_header_id_tbl.count
1307 LOOP
1308 l_request_id_tbl(i) := FND_REQUEST.SUBMIT_REQUEST(
1309 application => 'AMS',
1310 program => 'AMSDEMGR',
1311 argument1 => p_list_header_id_tbl(i).l_LIST_HEADER_id,
1312 argument2 => 1000,
1313 argument3 => 3);
1314 END LOOP;
1315
1316 COMMIT;
1317
1318 EXCEPTION
1319 WHEN OTHERS THEN
1320 x_return_status := 'E';
1321 x_msg_data := sqlcode||' '||sqlerrm;
1322 raise;
1323 END delete_entries_soft;
1324
1325
1326 PROCEDURE delete_entries_online(p_list_header_id_tbl IN AMS_LIST_PURGE_PVT.l_list_header_id_tbl%type,
1327 x_return_status OUT NOCOPY VARCHAR2,
1328 x_msg_count OUT NOCOPY NUMBER,
1329 x_msg_data OUT NOCOPY VARCHAR2) is
1330
1331 l_header_id_tbl ams_list_purge_pvt.l_id_tbl%type;
1332
1333 BEGIN
1334 x_return_status := FND_API.G_RET_STS_SUCCESS;
1335
1336 FOR i in 1..p_list_header_id_tbl.count
1337 LOOP
1338 l_header_id_tbl(i) := p_list_header_id_tbl(i).l_list_header_id;
1339 END LOOP;
1340
1341 --l_header_id_tbl := p_list_header_id_tbl.l_list_header_id;
1342
1343 FORALL i in 1 .. l_header_id_tbl.count
1344 DELETE from ams_list_entries
1345 WHERE list_header_id = l_header_id_tbl(i);
1346
1347 delete_list_info(p_id_tbl => l_header_id_tbl,
1348 x_return_status => x_return_status,
1349 x_msg_count => x_msg_count,
1350 x_msg_data => x_msg_data);
1351
1352 COMMIT;
1353
1354 EXCEPTION
1355 WHEN OTHERS THEN
1356 x_return_status := 'E';
1357 x_msg_data := sqlcode||' '||sqlerrm;
1358 raise;
1359 END delete_entries_online;
1360
1361 PROCEDURE purge_entries_manager (x_errbuf OUT NOCOPY VARCHAR2
1362 , x_retcode OUT NOCOPY VARCHAR2
1363 , p_list_type IN VARCHAR2
1364 , p_cr_date_from IN VARCHAR2
1365 , p_cr_date_to IN VARCHAR2
1366 , p_batch_size IN NUMBER DEFAULT 1000
1367 , p_num_workers IN NUMBER DEFAULT 3) IS
1368
1369
1370
1371 CURSOR c_get_list_headers(l_request_id number) IS
1372 SELECT list_header_id, list_name
1373 FROM ams_list_headers_vl
1374 WHERE request_id = l_request_id;
1375
1376 l_list_header_id number;
1377 l_list_header_status varchar2(100);
1378 x_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
1379 x_msg_data varchar2(2000);
1380 x_msg_count number;
1381 l_errbuf varchar2(32767);
1382 l_retcode number;
1383 l_conc_request_id number;
1384 l_children_done boolean := false;
1385
1386 --TYPE l_num_tbl IS table of number index by binary_integer;
1387 --l_header_id_tbl l_num_tbl;
1388
1389 l_header_id_tbl ams_list_purge_pvt.l_id_tbl%type;
1390
1391 type l_char_tbl is table of varchar2(1000) index by binary_integer;
1392 l_list_name_tbl l_char_tbl;
1393 l_start_date date;
1394 l_end_date date;
1395
1396 BEGIN
1397
1398 fnd_file.put_line(fnd_file.log,'Execution of Purge list and target group entries master concurrent program started.');
1399 l_conc_request_id := FND_GLOBAL.conc_request_id();
1400
1401
1402 fnd_file.put_line(fnd_file.log,'Concurrent request id is '||l_conc_request_id);
1403 fnd_file.put_line(fnd_file.log,'Created from date is '||p_cr_date_from);
1404 fnd_file.put_line(fnd_file.log,'Created to date is '||p_cr_date_to);
1405
1406 l_start_date := to_date(p_cr_date_from,'YYYY/MM/DD HH24:MI:SS');
1407 l_end_date := to_date(p_cr_date_to, 'YYYY/MM/DD HH24:MI:SS');
1408
1409 if p_list_type is not null then
1410 fnd_file.put_line(fnd_file.log,'Type chosen is '||p_list_type);
1411 UPDATE ams_list_headers_all head
1412 SET request_id = l_conc_request_id,last_update_date = sysdate
1413 WHERE status_code in ('AVAILABLE','LOCKED')
1414 AND trunc(creation_date) between trunc(l_start_date) AND trunc(l_end_date)
1415 AND list_type = p_list_type
1416 AND not exists (SELECT 1
1417 FROM AMS_LIST_SRC_TYPES type
1418 WHERE head.list_source_type = type.source_type_code
1419 AND nvl(type.remote_flag,'N') = 'Y') ;
1420 -- AND nvl(remote_gen_flag,'N') = 'N';
1421 else
1422 fnd_file.put_line(fnd_file.log,'Type is not chosen. All the matching list and target group will be purged. ' );
1423 UPDATE ams_list_headers_all head
1424 SET request_id = l_conc_request_id,last_update_date = sysdate
1425 WHERE status_code in ('AVAILABLE','LOCKED')
1426 AND trunc(creation_date) between trunc(l_start_date) AND trunc(l_end_date)
1427 AND not exists (SELECT 1
1428 FROM AMS_LIST_SRC_TYPES type
1429 WHERE head.list_source_type = type.source_type_code
1430 AND nvl(type.remote_flag,'N') = 'Y') ;
1431 -- AND nvl(remote_gen_flag,'N') = 'N';
1432 end if;
1433
1434 fnd_file.put_line(fnd_file.log,'Submitting sub requests');
1435 ad_conc_utils_pkg.submit_subrequests( x_errbuf => l_errbuf
1436 , x_retcode => l_retcode
1437 , x_workerconc_app_shortname => 'AMS'
1438 , x_workerconc_progname => 'AMSPEWKR'
1439 , x_batch_size => p_batch_size
1440 , x_num_workers => p_num_workers
1441 , x_argument4 => l_conc_request_id
1442 );
1443
1444 if l_children_done then
1445 fnd_file.put_line(fnd_file.log,'children done');
1446 else
1447 fnd_file.put_line(fnd_file.log,'children not done');
1448 end if;
1449
1450 l_children_done := fnd_concurrent.children_done ( parent_request_id => l_conc_request_id
1451 , recursive_flag => 'N'
1452 , interval => 15
1453 );
1454
1455 fnd_file.put_line(fnd_file.log,'Sub requests submitted.');
1456
1457 fnd_file.put_line(fnd_file.log,'ret code is '||l_retcode);
1458
1459 if l_children_done then
1460 fnd_file.put_line(fnd_file.log,'children done');
1461 else
1462 fnd_file.put_line(fnd_file.log,'children not done');
1463 end if;
1464
1465
1466 IF ((l_retcode <> ad_conc_utils_pkg.conc_fail) AND
1467 (l_children_done)) then
1468
1469 fnd_file.put_line(fnd_file.log,'Entries purged successfully. Need to purge from ams_act_logs.');
1470
1471 OPEN c_get_list_headers(l_conc_request_id);
1472 LOOP
1473 FETCH c_get_list_headers BULK COLLECT INTO l_header_id_tbl, l_list_name_tbl LIMIT 1000;
1474
1475 FORALL i in 1 .. l_header_id_tbl.count
1476 DELETE from ams_act_logs
1477 WHERE act_log_used_by_id = l_header_id_tbl(i);
1478
1479 FORALL i in 1 .. l_header_id_tbl.count
1480 UPDATE ams_list_headers_all
1481 SET status_code = 'PURGED',
1482 user_status_id = 313,
1483 no_of_rows_in_list = 0,
1484 no_of_rows_active = 0,
1485 no_of_rows_inactive = 0,
1486 no_of_rows_in_ctrl_group = 0,
1487 no_of_rows_random = 0,
1488
1489 no_of_rows_duplicates = 0,
1490 no_of_rows_manually_entered = 0,
1491 no_of_rows_suppressed = 0,
1492 NO_OF_ROWS_FATIGUED = 0,
1493 TCA_FAILED_RECORDS = 0,
1494 no_of_rows_initially_selected= 0,
1495 object_version_number = object_version_number + 1,
1496 status_date = SYSDATE,
1497 archived_by = FND_GLOBAL.user_id,
1498 archived_date = SYSDATE,
1499 last_update_date = SYSDATE,
1500 last_updated_by = FND_GLOBAL.user_id
1501 WHERE list_header_id = l_header_id_tbl(i);
1502
1503 FOR i in 1 .. l_header_id_tbl.count
1504 LOOP
1505 fnd_file.put_line(fnd_file.log,'Entries for list/target group '||l_list_name_tbl(i)||' is deleted');
1506 END LOOP;
1507
1508 COMMIT;
1509
1510 EXIT WHEN c_get_list_headers%NOTFOUND;
1511 END LOOP;
1512 CLOSE c_get_list_headers;
1513
1514 END IF;
1515 commit;
1516
1517 fnd_file.put_line(fnd_file.log,'Purge List and Target group entries concurrent program executed successfully.');
1518
1519 x_retcode := ad_conc_utils_pkg.conc_success;
1520 EXCEPTION
1521 WHEN OTHERS THEN
1522 fnd_file.put_line(fnd_file.log,'Error while executing purge entries concurrent program '||sqlerrm);
1523 x_retcode := ad_conc_utils_pkg.conc_fail;
1524 x_errbuf := SQLERRM;
1525 RAISE;
1526 END purge_entries_manager;
1527
1528
1529 PROCEDURE purge_entries_worker ( x_errbuf OUT NOCOPY VARCHAR2
1530 , x_retcode OUT NOCOPY VARCHAR2
1531 , x_batch_size IN NUMBER
1532 , x_worker_id IN NUMBER
1533 , x_num_workers IN NUMBER
1534 , x_argument4 IN VARCHAR2) IS
1535
1536 l_worker_id NUMBER;
1537 l_product VARCHAR2(30) := 'AMS';
1538 l_table_name VARCHAR2(30) := 'AMS_LIST_ENTRIES';
1539 l_update_name VARCHAR2(30);
1540 l_status VARCHAR2(30);
1541 l_industry VARCHAR2(30);
1542 l_restatus BOOLEAN;
1543 l_table_owner VARCHAR2(30);
1544 l_any_rows_to_process BOOLEAN;
1545 l_start_rowid ROWID;
1546 l_end_rowid ROWID;
1547 l_rows_processed NUMBER;
1548 BEGIN
1549
1550 l_restatus := fnd_installation.get_app_info ( l_product, l_status, l_industry, l_table_owner );
1551
1552 IF (( l_restatus = FALSE ) OR
1553 ( l_table_owner IS NULL))
1554 THEN
1555 RAISE_APPLICATION_ERROR(-20001, 'Cannot get schema name for product: '|| l_product );
1556 END IF;
1557
1558 FND_FILE.PUT_LINE( FND_FILE.LOG, 'X_Worker_Id: '|| x_worker_id );
1559 FND_FILE.PUT_LINE( FND_FILE.LOG, 'X_Num_Workers: '|| x_num_workers );
1560 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Concurrent request id is '|| x_argument4);
1561
1562
1563 l_update_name := x_argument4;
1564
1565 Begin
1566 ad_parallel_updates_pkg.initialize_rowid_range
1567 (
1568 ad_parallel_updates_pkg.ROWID_RANGE
1569 , l_table_owner
1570 , l_table_name
1571 , l_update_name
1572 , x_worker_id
1573 , x_num_workers
1574 , x_batch_size
1575 , 0
1576 );
1577
1578 ad_parallel_updates_pkg.get_rowid_range
1579 (
1580 l_start_rowid
1581 , l_end_rowid
1582 , l_any_rows_to_process
1583 , x_batch_size
1584 , TRUE
1585 );
1586
1587 WHILE ( l_any_rows_to_process = TRUE )
1588 LOOP
1589 DELETE /*+ rowid(entries) */
1590 AMS_LIST_ENTRIES entries
1591 WHERE list_header_id IN (select list_header_id from ams_list_headers_all
1592 where request_id = x_argument4)
1593 AND ROWID BETWEEN l_start_rowid AND l_end_rowid;
1594
1595 ad_parallel_updates_pkg.processed_rowid_range
1596 (
1597 l_rows_processed
1598 , l_end_rowid
1599 );
1600
1601 COMMIT;
1602
1603 ad_parallel_updates_pkg.get_rowid_range
1604 (
1605 l_start_rowid
1606 , l_end_rowid
1607 , l_any_rows_to_process
1608 , x_batch_size
1609 , FALSE
1610 );
1611 END LOOP;
1612 x_retcode := ad_conc_utils_pkg.conc_success;
1613 EXCEPTION
1614 WHEN OTHERS THEN
1615 x_retcode := ad_conc_utils_pkg.conc_fail;
1616 x_errbuf := SQLERRM;
1617 RAISE;
1618 END;
1619 EXCEPTION
1620 WHEN OTHERS THEN
1621 x_retcode := ad_conc_utils_pkg.conc_fail;
1622 x_errbuf := SQLERRM;
1623 RAISE;
1624 END purge_entries_worker;
1625
1626 END AMS_List_Purge_PVT;