[Home] [Help]
PACKAGE BODY: APPS.IEU_WR_CON_PVT
Source
1 PACKAGE BODY IEU_WR_CON_PVT AS
2 /* $Header: IEUVWRCB.pls 120.6 2006/04/19 21:45:03 msathyan noship $ */
3
4 PROCEDURE PURGE_WR_DATA
5 (
6 ERRBUF OUT NOCOPY VARCHAR2,
7 RETCODE OUT NOCOPY VARCHAR2,
8 P_WS_CODE IN VARCHAR2 DEFAULT NULL,
9 P_LAST_UPDATE_DATE IN DATE DEFAULT NULL
10 )
11 IS
12 --** This cursor represents the Associate work items that are closed and purged **--
13 --** Here work items whose parents are not open are picked **--
14 /*Cursor c1_purge_assct_wi(p_ws_id IN NUMBER,p_parent_ws_id IN NUMBER) is
15 Select workitem_pk_id, workitem_obj_code, source_object_id, source_object_type_code,
16 owner_id, owner_type, assignee_id, assignee_type, status_id, priority_id, due_date,
17 reschedule_time, distribution_status_id,work_item_number, work_item_id
18 from ieu_uwqm_items child
19 where ws_id = p_ws_id
20 and status_id in (3, 4)
21 and ( to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') )
22 and not exists
23 (select 1
24 from ieu_uwqm_items parent
25 where ws_id = p_parent_ws_id
26 and status_id in (0,5)
27 and parent.workitem_pk_id = child.source_object_id
28 and parent.workitem_obj_code = child.source_object_type_code);
29 */
30
31 --** This cursor represents the Associate work items that are closed and have open parent work items **--
32 --** Here work items which have parents in open status are picked **--
33 Cursor C1_open_parent_assct_Wi(p_ws_id IN NUMBER,p_parent_ws_id IN NUMBER) is
34 Select workitem_pk_id, workitem_obj_code, source_object_id, source_object_type_code
35 from ieu_uwqm_items
36 where ws_id = p_ws_id
37 and status_id in (3, 4)
38 and ( to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') )
39 and (source_object_id, source_object_type_code) IN
40 (select workitem_pk_id, workitem_obj_code
41 from ieu_uwqm_items
42 where ws_id = p_parent_ws_id
43 and status_id in (0,5) );
44
45
46 --** This cursor represents Primary Work items that are closed and purged **--
47 --** The parent records are purged if the child records are not open **--
48 /*Cursor C2_purge_primary_wi(p_ws_id IN NUMBER,p_child_ws_id IN NUMBER) is
49 Select workitem_pk_id, workitem_obj_code, source_object_id, source_object_type_code,
50 owner_id, owner_type, assignee_id, assignee_type, status_id, priority_id, due_date,
51 reschedule_time, distribution_status_id,work_item_number,work_item_id
52 from ieu_uwqm_items parent
53 where ws_id = p_ws_id
54 and status_id in (3, 4)
55 and ( to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') )
56 and not exists
57 (select source_object_id, source_object_type_code
58 from ieu_uwqm_items child
59 where ws_id = p_child_ws_id
60 and status_id in (0,5)
61 and child.workitem_pk_id = parent.source_object_id
62 and child.workitem_obj_code = parent.source_object_type_code);
63 */
64 --** This cursor represents the Primary work items that are closed and have open child work items **--
65 Cursor C2_open_child_primary_wi(p_ws_id IN NUMBER,p_child_ws_id IN NUMBER) is
66 /*Select workitem_pk_id, workitem_obj_code, source_object_id, source_object_type_code
67 from ieu_uwqm_items
68 where ws_id = p_ws_id
69 and status_id in (3, 4)
70 and ( to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') )
71 and (workitem_pk_id, workitem_obj_code) in
72 (select source_object_id, source_object_type_code
73 from ieu_uwqm_items
74 where ws_id = p_child_ws_id
75 and status_id in (0,5) );*/
76
77 Select parent.workitem_pk_id, parent.workitem_obj_code, child.workitem_pk_id, child.workitem_obj_code
78 from ieu_uwqm_items parent , ieu_uwqm_items child
79 where parent.ws_id = p_ws_id
80 and parent.status_id in (3, 4)
81 and ( to_date(trunc(parent.last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') )
82 and parent.workitem_pk_id = child.source_object_id
83 and parent.workitem_obj_code = child.source_object_type_code
84 and child.ws_id = p_child_ws_id
85 and child.status_id in (0,5) ;
86
87 --** This cursor represents all remaining closed work items **--
88 /*Cursor C3_purge_wi(p_ws_id IN NUMBER) is
89 Select workitem_pk_id, workitem_obj_code, source_object_id, source_object_type_code,
90 owner_id, owner_type, assignee_id, assignee_type, status_id, priority_id, due_date,
91 reschedule_time, distribution_status_id,work_item_number,work_item_id
92 from ieu_uwqm_items
93 where status_id in(3,4)
94 and ws_id = p_ws_id
95 and to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') ;
96 */
97
98 TYPE NUMBER_TBL is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
99 TYPE VARCHAR2_TBL is TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
100 TYPE task_details_rec IS RECORD
101 (
102 l_workitem_pk_id_list VARCHAR2_TBL,
103 l_workitem_obj_code_list VARCHAR2_TBL,
104 l_source_object_id_list VARCHAR2_TBL,
105 l_source_object_type_code_list VARCHAR2_TBL,
106 l_owner_id_list VARCHAR2_TBL,
107 l_owner_type_list VARCHAR2_TBL,
108 l_assignee_id_list VARCHAR2_TBL,
109 l_assignee_type_list VARCHAR2_TBL,
110 l_status_id_list VARCHAR2_TBL,
111 l_priority_id_list VARCHAR2_TBL,
112 l_due_date_list VARCHAR2_TBL,
113 l_reschedule_time_list VARCHAR2_TBL,
114 l_distribution_status_id_list VARCHAR2_TBL,
115 l_work_item_number_list VARCHAR2_TBL,
116 l_work_item_id_list VARCHAR2_TBL
117 );
118
119 l_task_det_rec task_details_rec;
120
121 l_wi_exists NUMBER := 0;
122 l_ws_type VARCHAR2(50) := null;
123 l_ws_id NUMBER := null;
124 l_dist_st_based_on_parent VARCHAR2(10):= null;
125 l_assct_ws_id NUMBER := null;
126 l_parent_ws_id NUMBER := null;
127 l_child_ws_id NUMBER := null;
128 l_parent_wi_pk_id NUMBER := -9999;
129 l_parent_wi_obj_code VARCHAR2(30):= null;
130 l_child_wi_pk_id NUMBER := -9999;
131 l_child_wi_obj_code VARCHAR2(30) := null;
132 l_message VARCHAR2(4000);
133 l_parent_ws_code VARCHAR2(30);
134 l_api_name VARCHAR2(30);
135 l_audit_trail_rec SYSTEM.WR_AUDIT_TRAIL_NST;
136 l_audit_log_val VARCHAR2(100);
137 l_msg_count NUMBER;
138 l_msg_data VARCHAR2(2000);
139 l_return_status VARCHAR2(5);
140 l_temp_count number;
141 x_msg_count NUMBER;
142 x_msg_data VARCHAR2(4000);
143
144
145 --Audit
146 l_action_key VARCHAR2(2000);
147 l_event_key VARCHAR2(2000);
148 l_module VARCHAR2(2000);
149 l_ieu_comment_code1 VARCHAR2(2000);
150 l_ieu_comment_code2 VARCHAR2(2000);
151 l_ieu_comment_code3 VARCHAR2(2000);
152 l_ieu_comment_code4 VARCHAR2(2000);
153 l_ieu_comment_code5 VARCHAR2(2000);
154 l_workitem_comment_code1 VARCHAR2(2000);
155 l_workitem_comment_code2 VARCHAR2(2000);
156 l_workitem_comment_code3 VARCHAR2(2000);
157 l_workitem_comment_code4 VARCHAR2(2000);
158 l_workitem_comment_code5 VARCHAR2(2000);
159 l_closed_item_exists NUMBER;
160
161 dml_errors EXCEPTION;
162 PRAGMA exception_init(dml_errors, -24381);
163
164
165 BEGIN
166
167 l_api_name := 'PURGE_WR_ITEM';
168 l_audit_log_val := FND_PROFILE.VALUE('IEU_WR_DIST_AUDIT_LOG');
169 FND_MESSAGE.SET_NAME('IEU', 'IEU_WR_PURGE_INP_PARAMS');
170 FND_MESSAGE.SET_TOKEN('WS_CODE', p_ws_code);
171 FND_MESSAGE.SET_TOKEN('LU_DATE', p_last_update_date);
172 l_message := FND_MESSAGE.GET;
173 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
174
175 BEGIN
176
177 SELECT WS_B.WS_ID, WS_B.WS_TYPE, WS_A.DIST_ST_BASED_ON_PARENT_FLAG,
178 WS_A.PARENT_WS_ID, WS_A.CHILD_WS_ID
179 INTO l_ws_id, l_ws_type, l_dist_st_based_on_parent, l_parent_ws_id, l_child_ws_id
180 FROM IEU_UWQM_WORK_SOURCES_B WS_B, IEU_UWQM_WS_ASSCT_PROPS WS_A
181 WHERE ws_b.ws_code = p_ws_code
182 AND ws_b.not_valid_flag = 'N'
183 AND ws_b.ws_id = ws_a.ws_id(+);
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 l_ws_type := null;
188 l_ws_id := null;
189 l_dist_st_based_on_parent := null;
190 l_assct_ws_id := null;
191 l_parent_ws_id := null;
192 l_child_ws_id := null;
193 END;
194
195
196 --** Purge Association Work Items **--
197 --** Check if all the parent work items are purged. **--
198 --** If the parent work items are still present, give appropriate message.**--
199 --** If parent work items are purged, then purge association work items. **--
200 if (l_ws_type = 'ASSOCIATION')
201 then
202
203
204
205 --** Check if any closed Parent Work Item exists with last update date <= p_last_update date **--
206 BEGIN
207 l_closed_item_exists := 0;
208 select count(*)
209 into l_closed_item_exists
210 from ieu_uwqm_items
211 where ws_id = l_parent_ws_id
212 and status_id in (3, 4)
213 and to_date(trunc(last_update_date), 'dd-mm-rrrr')
214 <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') ;
215 EXCEPTION
216 WHEN OTHERS THEN
217 l_closed_item_exists := 0;
218 END;
219
220 l_wi_exists := 0;
221 --** if closed parent work items exist check for closed child work items **--
222 IF l_closed_item_exists > 0 then
223 BEGIN
224
225
226 select count(*)
227 into l_wi_exists
228 from ieu_uwqm_items main
229 where status_id in (3,4)
230 and ws_id = l_parent_ws_id
231 and ( workitem_pk_id, workitem_obj_code) in
232 (Select source_object_id,source_object_type_code
233 from ieu_uwqm_items
234 where status_id in (3, 4)
235 and to_date(trunc(last_update_date), 'dd-mm-rrrr')
236 <= to_date(trunc(sysdate), 'dd-mm-rrrr') )
237 and not exists (select 1 from ieu_uwqm_items
238 where status_id in (0,5)
239 and source_object_id = main.workitem_pk_id
240 and source_object_type_code = main.workitem_obj_code
241 and to_date(trunc(last_update_date), 'dd-mm-rrrr')
242 <= to_date(trunc(sysdate), 'dd-mm-rrrr')) ;
243
244
245 EXCEPTION
246 WHEN OTHERS THEN
247 l_wi_exists := 0;
248 END;
249
250 END IF; --l_closed_item_exists check
251
252
253 --** if there are closed child work items for closed parent, then it means that the parent work items **--
254 --** are not purged. **--
255 --** Give the message to purge parent work items **--
256 IF (l_wi_exists > 0)
257 THEN
258 RETCODE := 2;
259 BEGIN
260 select ws_code
261 into l_parent_ws_code
262 from ieu_uwqm_work_sources_b
263 where ws_id = l_parent_ws_id;
264 EXCEPTION
265 WHEN OTHERS THEN
266 l_parent_ws_code := null;
267 END;
268
269 FND_MESSAGE.SET_NAME('IEU', 'IEU_WR_PURGE_PARENT_WI');
270 FND_MESSAGE.SET_TOKEN('WS_CODE', l_parent_ws_code);
271 l_message := FND_MESSAGE.GET;
272 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
273
274 RAISE fnd_api.g_exc_error;
275 END IF;
276
277
278 --** If parents work items have been purged, then purge child work items **--
279 --** This code has been commented as audit logs are not required -- bug #4638378 **--
280 IF (l_wi_exists = 0) THEN
281
282 /* OPEN c1_purge_assct_wi(l_ws_id,l_parent_ws_id);
283 FETCH c1_purge_assct_wi BULK COLLECT INTO
284 l_task_det_rec.l_workitem_pk_id_list,
285 l_task_det_rec.l_workitem_obj_code_list,
286 l_task_det_rec.l_source_object_id_list,
287 l_task_det_rec.l_source_object_type_code_list,
288 l_task_det_rec.l_owner_id_list ,
289 l_task_det_rec.l_owner_type_list,
290 l_task_det_rec.l_assignee_id_list,
291 l_task_det_rec.l_assignee_type_list,
292 l_task_det_rec.l_status_id_list ,
293 l_task_det_rec.l_priority_id_list ,
294 l_task_det_rec.l_due_date_list ,
295 l_task_det_rec.l_reschedule_time_list,
296 l_task_det_rec.l_distribution_status_id_list,
297 l_task_det_rec.l_work_item_number_list,
298 l_task_det_rec.l_work_item_id_list;
299 CLOSE c1_purge_assct_wi; */
300 /* FOR i IN 1..l_task_det_rec.l_workitem_pk_id_list.COUNT loop
301 FND_MESSAGE.SET_NAME('IEU', 'IEU_WR_PURGED_WI_DETAILS');
302 FND_MESSAGE.SET_TOKEN('WI_PK_ID', l_task_det_rec.l_workitem_pk_id_list(i));
303 l_message := FND_MESSAGE.GET;
304 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
305 END LOOP;*/
306 /* --** Audit logging starts here ** --
307 l_audit_log_val := FND_PROFILE.VALUE('IEU_WR_DIST_AUDIT_LOG');
308 l_action_key := 'WORKITEM_PURGE';
309 l_event_key := 'PURGE_WR_ITEM';
310 l_module := 'IEU_WR_PUB.PURGE_WR_ITEM';
311 IF (l_audit_log_val = 'DETAILED')
312 THEN
313 l_workitem_comment_code1 := 'WORKITEM_PURGE';
314 l_workitem_comment_code2 := null;
315 l_workitem_comment_code3 := null;
316 l_workitem_comment_code4 := null;
317 l_workitem_comment_code5 := null;
318 ELSE
319 l_workitem_comment_code1 := null;
320 l_workitem_comment_code2 := null;
321 l_workitem_comment_code3 := null;
322 l_workitem_comment_code4 := null;
323 l_workitem_comment_code5 := null;
324 END IF;
325 IF (l_audit_log_val = 'MINIMAL')
326 THEN
327 l_event_key := null;
328 END IF;
329 IF ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') )
330 THEN
331 l_event_key := 'PURGE_WR_ITEM';
332 END IF;
333
334
335 IF ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') ) OR
336 ( (l_audit_log_val = 'MINIMAL') AND ( (l_action_key is NULL)
337 OR (l_action_key = 'WORKITEM_PURGE')) ) THEN
338 BEGIN
339 FORALL i in 1..l_task_det_rec.l_work_item_id_list.count SAVE EXCEPTIONS
340 insert into IEU_UWQM_AUDIT_LOG
341 (AUDIT_LOG_ID,
342 OBJECT_VERSION_NUMBER,
343 CREATED_BY,
344 CREATION_DATE,
345 LAST_UPDATED_BY,
346 LAST_UPDATE_DATE,
347 LAST_UPDATE_LOGIN,
348 ACTION_KEY,
349 EVENT_KEY,
350 MODULE,
351 WS_CODE,
352 APPLICATION_ID,
353 WORKITEM_PK_ID,
354 WORKITEM_OBJ_CODE,
355 WORK_ITEM_NUMBER,
356 WORKITEM_STATUS_ID_PREV,
357 WORKITEM_STATUS_ID_CURR,
358 OWNER_ID_PREV,
359 OWNER_ID_CURR,
360 OWNER_TYPE_PREV,
361 OWNER_TYPE_CURR,
362 ASSIGNEE_ID_PREV,
363 ASSIGNEE_ID_CURR,
364 ASSIGNEE_TYPE_PREV,
365 ASSIGNEE_TYPE_CURR,
366 SOURCE_OBJECT_ID_PREV,
367 SOURCE_OBJECT_ID_CURR,
368 SOURCE_OBJECT_TYPE_CODE_PREV,
369 SOURCE_OBJECT_TYPE_CODE_CURR,
370 PARENT_WORKITEM_STATUS_ID_PREV,
371 PARENT_WORKITEM_STATUS_ID_CURR,
372 PARENT_DIST_STATUS_ID_PREV,
373 PARENT_DIST_STATUS_ID_CURR,
374 WORKITEM_DIST_STATUS_ID_PREV,
375 WORKITEM_DIST_STATUS_ID_CURR,
376 PRIORITY_ID_PREV,
377 PRIORITY_ID_CURR,
378 DUE_DATE_PREV,
379 DUE_DATE_CURR,
380 RESCHEDULE_TIME_PREV,
381 RESCHEDULE_TIME_CURR,
382 IEU_COMMENT_CODE1,
383 IEU_COMMENT_CODE2,
384 IEU_COMMENT_CODE3,
385 IEU_COMMENT_CODE4,
386 IEU_COMMENT_CODE5,
387 WORKITEM_COMMENT_CODE1,
388 WORKITEM_COMMENT_CODE2,
389 WORKITEM_COMMENT_CODE3,
390 WORKITEM_COMMENT_CODE4,
391 WORKITEM_COMMENT_CODE5,
392 RETURN_STATUS,
393 ERROR_CODE,
394 LOGGING_LEVEL)
395 values
396 (
397 IEU_UWQM_AUDIT_LOG_S1.NEXTVAL,
398 1,
399 FND_GLOBAL.USER_ID,
400 SYSDATE,
401 FND_GLOBAL.USER_ID,
402 SYSDATE,
406 l_module,
403 FND_GLOBAL.LOGIN_ID,
404 l_action_key,
405 l_event_key,
407 P_WS_CODE,
408 690,
409 l_task_det_rec.l_workitem_pk_id_list(i),
410 l_task_det_rec.l_workitem_obj_code_list(i),
411 l_task_det_rec.l_work_item_number_list(i),
412 l_task_det_rec.l_status_id_list(i),
413 l_task_det_rec.l_status_id_list(i),
414 l_task_det_rec.l_owner_id_list(i),
415 l_task_det_rec.l_owner_id_list(i),
416 l_task_det_rec.l_owner_type_list(i),
417 l_task_det_rec.l_owner_type_list(i),
418 l_task_det_rec.l_assignee_id_list(i),
419 l_task_det_rec.l_assignee_id_list(i),
420 l_task_det_rec.l_assignee_type_list(i),
421 l_task_det_rec.l_assignee_type_list(i),
422 l_task_det_rec.l_source_object_id_list(i),
423 l_task_det_rec.l_source_object_id_list(i),
424 l_task_det_rec.l_source_object_type_code_list(i),
425 l_task_det_rec.l_source_object_type_code_list(i),
426 NULL,
427 NULL,
428 NULL,
429 NULL,
430 l_task_det_rec.l_distribution_status_id_list(i),
431 l_task_det_rec.l_distribution_status_id_list(i),
432 l_task_det_rec.l_priority_id_list(i),
433 l_task_det_rec.l_priority_id_list(i),
434 l_task_det_rec.l_due_date_list(i),
435 l_task_det_rec.l_due_date_list(i),
436 l_task_det_rec.l_reschedule_time_list(i),
437 l_task_det_rec.l_reschedule_time_list(i),
438 NULL,
439 NULL,
440 NULL,
441 NULL,
442 NULL,
443 l_workitem_comment_code1,
444 l_workitem_comment_code2,
445 l_workitem_comment_code3,
446 l_workitem_comment_code4,
447 l_workitem_comment_code5,
448 fnd_api.g_ret_sts_success,
449 NULL,
450 FND_PROFILE.VALUE('IEU_WR_DIST_AUDIT_LOG'));
451 EXCEPTION
452 WHEN dml_errors THEN
453 null;
454 END;
455
456 END IF;
457 */ -- bug #4638378
458
459
460 --** Open parents work items existing for this work item **--
461 OPEN c1_open_parent_assct_Wi(l_ws_id,l_parent_ws_id);
462 FETCH c1_open_parent_assct_Wi BULK COLLECT INTO
463 l_task_det_rec.l_workitem_pk_id_list,
464 l_task_det_rec.l_workitem_obj_code_list,
465 l_task_det_rec.l_source_object_id_list,
466 l_task_det_rec.l_source_object_type_code_list;
467 CLOSE c1_open_parent_assct_Wi;
468 FOR i IN 1..l_task_det_rec.l_workitem_pk_id_list.COUNT LOOP
469 FND_MESSAGE.SET_NAME('IEU', 'IEU_WR_NOT_PURGED_PARENT_WI');
470 FND_MESSAGE.SET_TOKEN('WI_PK_ID', l_task_det_rec.l_workitem_pk_id_list(i));
471 FND_MESSAGE.SET_TOKEN('PARENT_WI_PK_ID', l_task_det_rec.l_source_object_id_list(i));
472 FND_MESSAGE.SET_TOKEN('PARENT_WI_OBJ_CODE',l_task_det_rec.l_source_object_type_code_list(i));
473 l_message := FND_MESSAGE.GET;
474 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
475 END LOOP;
476
477 --** delete all associate work items where parent work items are closed **--
478 delete
479 from ieu_uwqm_items child
480 where ws_id = l_ws_id
481 and status_id in (3, 4)
482 and to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr')
483 and not exists
484 (select 1
485 from ieu_uwqm_items parent
486 where ws_id = l_parent_ws_id
487 and status_id in (0,5)
488 and parent.workitem_pk_id = child.source_object_id
489 and parent.workitem_obj_code = child.source_object_type_code);
490
491
492
493
494
495 END IF; /* l_wi_exists <> 0 */
496
497 END IF; /*** l_ws_type = 'ASSOCIATION'***/
498
499 IF (l_ws_type = 'PRIMARY')
500 THEN
501
502 BEGIN
503 l_child_ws_id := null;
504 SELECT WS_A.WS_ASSOCIATION_PROP_ID,WS_A.WS_ID
505 INTO l_assct_ws_id,l_child_ws_id
506 FROM IEU_UWQM_WS_ASSCT_PROPS WS_A
507 WHERE parent_ws_id = l_ws_id;
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 l_assct_ws_id := null;
512 l_child_ws_id := null;
513 END;
514 --** Open child Work Item does not exist. Hence the parent work item should be purged. **--
515 --** This code has been commented as audit logs are not required **--
516 IF l_assct_ws_id is not null THEN
517 /* OPEN c2_purge_primary_wi(l_ws_id,l_child_ws_id);
518 FETCH c2_purge_primary_wi BULK COLLECT INTO
519 l_task_det_rec.l_workitem_pk_id_list,
520 l_task_det_rec.l_workitem_obj_code_list,
521 l_task_det_rec.l_source_object_id_list,
522 l_task_det_rec.l_source_object_type_code_list,
523 l_task_det_rec.l_owner_id_list ,
524 l_task_det_rec.l_owner_type_list,
525 l_task_det_rec.l_assignee_id_list,
526 l_task_det_rec.l_assignee_type_list,
527 l_task_det_rec.l_status_id_list ,
528 l_task_det_rec.l_priority_id_list ,
529 l_task_det_rec.l_due_date_list ,
530 l_task_det_rec.l_reschedule_time_list,
531 l_task_det_rec.l_distribution_status_id_list,
532 l_task_det_rec.l_work_item_number_list,
533 l_task_det_rec.l_work_item_id_list;
534 CLOSE c2_purge_primary_wi; */
535
536 /* FOR i IN 1..l_task_det_rec.l_workitem_pk_id_list.COUNT LOOP
537
541 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
538 FND_MESSAGE.SET_NAME('IEU', 'IEU_WR_PURGED_WI_DETAILS');
539 FND_MESSAGE.SET_TOKEN('WI_PK_ID', l_task_det_rec.l_workitem_pk_id_list(i));
540 l_message := FND_MESSAGE.GET;
542
543 END LOOP;*/
544
545 /* --** Audit logging starts here ** --
546 l_audit_log_val := FND_PROFILE.VALUE('IEU_WR_DIST_AUDIT_LOG');
547 l_action_key := 'WORKITEM_PURGE';
548 l_event_key := 'PURGE_WR_ITEM';
549 l_module := 'IEU_WR_PUB.PURGE_WR_ITEM';
550 IF (l_audit_log_val = 'DETAILED')
551 THEN
552 l_workitem_comment_code1 := 'WORKITEM_PURGE';
553 l_workitem_comment_code2 := null;
554 l_workitem_comment_code3 := null;
555 l_workitem_comment_code4 := null;
556 l_workitem_comment_code5 := null;
557 ELSE
558 l_workitem_comment_code1 := null;
559 l_workitem_comment_code2 := null;
560 l_workitem_comment_code3 := null;
561 l_workitem_comment_code4 := null;
562 l_workitem_comment_code5 := null;
563 END IF;
564 IF (l_audit_log_val = 'MINIMAL')
565 THEN
566 l_event_key := null;
567 END IF;
568 IF ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') )
569 THEN
570 l_event_key := 'PURGE_WR_ITEM';
571 END IF;
572 IF ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') ) OR
573 ( (l_audit_log_val = 'MINIMAL') AND ( (l_action_key is NULL)
574 OR (l_action_key = 'WORKITEM_PURGE')) ) THEN
575 BEGIN
576 FORALL i in 1..l_task_det_rec.l_work_item_id_list.count SAVE EXCEPTIONS
577 insert into IEU_UWQM_AUDIT_LOG
578 (AUDIT_LOG_ID,
579 OBJECT_VERSION_NUMBER,
580 CREATED_BY,
581 CREATION_DATE,
582 LAST_UPDATED_BY,
583 LAST_UPDATE_DATE,
584 LAST_UPDATE_LOGIN,
585 ACTION_KEY,
586 EVENT_KEY,
587 MODULE,
588 WS_CODE,
589 APPLICATION_ID,
590 WORKITEM_PK_ID,
591 WORKITEM_OBJ_CODE,
592 WORK_ITEM_NUMBER,
593 WORKITEM_STATUS_ID_PREV,
594 WORKITEM_STATUS_ID_CURR,
595 OWNER_ID_PREV,
596 OWNER_ID_CURR,
597 OWNER_TYPE_PREV,
598 OWNER_TYPE_CURR,
599 ASSIGNEE_ID_PREV,
600 ASSIGNEE_ID_CURR,
601 ASSIGNEE_TYPE_PREV,
602 ASSIGNEE_TYPE_CURR,
603 SOURCE_OBJECT_ID_PREV,
604 SOURCE_OBJECT_ID_CURR,
605 SOURCE_OBJECT_TYPE_CODE_PREV,
606 SOURCE_OBJECT_TYPE_CODE_CURR,
607 PARENT_WORKITEM_STATUS_ID_PREV,
608 PARENT_WORKITEM_STATUS_ID_CURR,
609 PARENT_DIST_STATUS_ID_PREV,
610 PARENT_DIST_STATUS_ID_CURR,
611 WORKITEM_DIST_STATUS_ID_PREV,
612 WORKITEM_DIST_STATUS_ID_CURR,
613 PRIORITY_ID_PREV,
614 PRIORITY_ID_CURR,
615 DUE_DATE_PREV,
616 DUE_DATE_CURR,
617 RESCHEDULE_TIME_PREV,
618 RESCHEDULE_TIME_CURR,
619 IEU_COMMENT_CODE1,
620 IEU_COMMENT_CODE2,
621 IEU_COMMENT_CODE3,
622 IEU_COMMENT_CODE4,
623 IEU_COMMENT_CODE5,
624 WORKITEM_COMMENT_CODE1,
625 WORKITEM_COMMENT_CODE2,
626 WORKITEM_COMMENT_CODE3,
627 WORKITEM_COMMENT_CODE4,
628 WORKITEM_COMMENT_CODE5,
629 RETURN_STATUS,
630 ERROR_CODE,
631 LOGGING_LEVEL)
632 values
633 (
634 IEU_UWQM_AUDIT_LOG_S1.NEXTVAL,
635 1,
636 FND_GLOBAL.USER_ID,
637 SYSDATE,
638 FND_GLOBAL.USER_ID,
639 SYSDATE,
640 FND_GLOBAL.LOGIN_ID,
641 l_action_key,
642 l_event_key,
643 l_module,
644 P_WS_CODE,
645 690,
646 l_task_det_rec.l_workitem_pk_id_list(i),
647 l_task_det_rec.l_workitem_obj_code_list(i),
648 l_task_det_rec.l_work_item_number_list(i),
649 l_task_det_rec.l_status_id_list(i),
650 l_task_det_rec.l_status_id_list(i),
651 l_task_det_rec.l_owner_id_list(i),
652 l_task_det_rec.l_owner_id_list(i),
653 l_task_det_rec.l_owner_type_list(i),
654 l_task_det_rec.l_owner_type_list(i),
655 l_task_det_rec.l_assignee_id_list(i),
656 l_task_det_rec.l_assignee_id_list(i),
657 l_task_det_rec.l_assignee_type_list(i),
658 l_task_det_rec.l_assignee_type_list(i),
659 l_task_det_rec.l_source_object_id_list(i),
660 l_task_det_rec.l_source_object_id_list(i),
661 l_task_det_rec.l_source_object_type_code_list(i),
662 l_task_det_rec.l_source_object_type_code_list(i),
663 NULL,
664 NULL,
665 NULL,
666 NULL,
667 l_task_det_rec.l_distribution_status_id_list(i),
668 l_task_det_rec.l_distribution_status_id_list(i),
669 l_task_det_rec.l_priority_id_list(i),
670 l_task_det_rec.l_priority_id_list(i),
671 l_task_det_rec.l_due_date_list(i),
672 l_task_det_rec.l_due_date_list(i),
673 l_task_det_rec.l_reschedule_time_list(i),
674 l_task_det_rec.l_reschedule_time_list(i),
675 NULL,
679 NULL,
676 NULL,
677 NULL,
678 NULL,
680 l_workitem_comment_code1,
681 l_workitem_comment_code2,
682 l_workitem_comment_code3,
683 l_workitem_comment_code4,
684 l_workitem_comment_code5,
685 fnd_api.g_ret_sts_success,
686 NULL,
687 FND_PROFILE.VALUE('IEU_WR_DIST_AUDIT_LOG'));
688 EXCEPTION
689 WHEN dml_errors THEN
690 null;
691 END;
692
693 END IF; */
694
695 --** Open child Work Item exist. Hence the parent work item should not be purged. **--
696 OPEN c2_open_child_primary_wi(l_ws_id,l_child_ws_id);
697 FETCH c2_open_child_primary_wi BULK COLLECT INTO
698 l_task_det_rec.l_workitem_pk_id_list,
699 l_task_det_rec.l_workitem_obj_code_list,
700 l_task_det_rec.l_source_object_id_list,
701 l_task_det_rec.l_source_object_type_code_list;
702 close c2_open_child_primary_wi;
703
704
705 FOR i IN 1..l_task_det_rec.l_workitem_pk_id_list.COUNT LOOP
706 FND_MESSAGE.SET_NAME('IEU', 'IEU_WR_NOT_PURGED_CHILD_WI');
707 FND_MESSAGE.SET_TOKEN('WI_PK_ID', l_task_det_rec.l_workitem_pk_id_list(i));
708 FND_MESSAGE.SET_TOKEN('CHILD_WI_PK_ID', l_task_det_rec.l_source_object_id_list(i));
709 FND_MESSAGE.SET_TOKEN('CHILD_WI_OBJ_CODE', l_task_det_rec.l_source_object_type_Code_list(i));
710 l_message := FND_MESSAGE.GET;
711 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
712 END LOOP;
713 END IF; -- l_assct_ws_id
714
715 IF (l_assct_ws_id is not null)
716 THEN
717 --** Open child Work Item does not exist. Hence the parent work item should be purged. **--
718 delete
719 from ieu_uwqm_items parent
720 where ws_id = l_ws_id
721 and status_id in (3, 4)
722 and to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr')
723 and not exists
724 (select 1
725 from ieu_uwqm_items child
726 where ws_id = l_child_ws_id
727 and status_id in (0,5)
728 and child.source_object_id = parent.workitem_pk_id
729 and child.source_object_type_code = parent.workitem_obj_code );
730
731
732
733 ELSE
734
735 /* OPEN c3_purge_wi(l_ws_id);
736 FETCH c3_purge_wi BULK COLLECT INTO
737 l_task_det_rec.l_workitem_pk_id_list,
738 l_task_det_rec.l_workitem_obj_code_list,
739 l_task_det_rec.l_source_object_id_list,
740 l_task_det_rec.l_source_object_type_code_list,
741 l_task_det_rec.l_owner_id_list ,
742 l_task_det_rec.l_owner_type_list,
743 l_task_det_rec.l_assignee_id_list,
744 l_task_det_rec.l_assignee_type_list,
745 l_task_det_rec.l_status_id_list ,
746 l_task_det_rec.l_priority_id_list ,
747 l_task_det_rec.l_due_date_list ,
748 l_task_det_rec.l_reschedule_time_list,
749 l_task_det_rec.l_distribution_status_id_list,
750 l_task_det_rec.l_work_item_number_list,
751 l_task_det_rec.l_work_item_id_list;
752 CLOSE c3_purge_wi; */ -- bug #4438378
753
754
755 /* FOR i in 1..l_task_det_rec.l_workitem_pk_id_list.COUNT LOOP
756
757 FND_MESSAGE.SET_NAME('IEU', 'IEU_WR_PURGED_WI_DETAILS');
758 FND_MESSAGE.SET_TOKEN('WI_PK_ID', l_task_det_rec.l_workitem_pk_id_list(i));
759 l_message := FND_MESSAGE.GET;
760 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
761 END LOOP;*/
762
763 /* --** Audit logging starts here ** --
764 l_audit_log_val := FND_PROFILE.VALUE('IEU_WR_DIST_AUDIT_LOG');
765 l_action_key := 'WORKITEM_PURGE';
766 l_event_key := 'PURGE_WR_ITEM';
767 l_module := 'IEU_WR_PUB.PURGE_WR_ITEM';
768 IF (l_audit_log_val = 'DETAILED')
769 THEN
770 l_workitem_comment_code1 := 'WORKITEM_PURGE';
771 l_workitem_comment_code2 := null;
772 l_workitem_comment_code3 := null;
773 l_workitem_comment_code4 := null;
774 l_workitem_comment_code5 := null;
775 ELSE
776 l_workitem_comment_code1 := null;
777 l_workitem_comment_code2 := null;
778 l_workitem_comment_code3 := null;
779 l_workitem_comment_code4 := null;
780 l_workitem_comment_code5 := null;
781 END IF;
782 IF (l_audit_log_val = 'MINIMAL')
783 THEN
784 l_event_key := null;
785 END IF;
786 IF ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') )
787 THEN
788 l_event_key := 'PURGE_WR_ITEM';
789 END IF;
790
791 IF ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') ) OR
792 ( (l_audit_log_val = 'MINIMAL') AND ( (l_action_key is NULL)
793 OR (l_action_key = 'WORKITEM_PURGE')) ) THEN
794 BEGIN
795 FORALL i in 1..l_task_det_rec.l_work_item_id_list.count SAVE EXCEPTIONS
796 insert into IEU_UWQM_AUDIT_LOG
797 (AUDIT_LOG_ID,
798 OBJECT_VERSION_NUMBER,
799 CREATED_BY,
800 CREATION_DATE,
801 LAST_UPDATED_BY,
802 LAST_UPDATE_DATE,
803 LAST_UPDATE_LOGIN,
804 ACTION_KEY,
805 EVENT_KEY,
806 MODULE,
807 WS_CODE,
808 APPLICATION_ID,
809 WORKITEM_PK_ID,
810 WORKITEM_OBJ_CODE,
814 OWNER_ID_PREV,
811 WORK_ITEM_NUMBER,
812 WORKITEM_STATUS_ID_PREV,
813 WORKITEM_STATUS_ID_CURR,
815 OWNER_ID_CURR,
816 OWNER_TYPE_PREV,
817 OWNER_TYPE_CURR,
818 ASSIGNEE_ID_PREV,
819 ASSIGNEE_ID_CURR,
820 ASSIGNEE_TYPE_PREV,
821 ASSIGNEE_TYPE_CURR,
822 SOURCE_OBJECT_ID_PREV,
823 SOURCE_OBJECT_ID_CURR,
824 SOURCE_OBJECT_TYPE_CODE_PREV,
825 SOURCE_OBJECT_TYPE_CODE_CURR,
826 PARENT_WORKITEM_STATUS_ID_PREV,
827 PARENT_WORKITEM_STATUS_ID_CURR,
828 PARENT_DIST_STATUS_ID_PREV,
829 PARENT_DIST_STATUS_ID_CURR,
830 WORKITEM_DIST_STATUS_ID_PREV,
831 WORKITEM_DIST_STATUS_ID_CURR,
832 PRIORITY_ID_PREV,
833 PRIORITY_ID_CURR,
834 DUE_DATE_PREV,
835 DUE_DATE_CURR,
836 RESCHEDULE_TIME_PREV,
837 RESCHEDULE_TIME_CURR,
838 IEU_COMMENT_CODE1,
839 IEU_COMMENT_CODE2,
840 IEU_COMMENT_CODE3,
841 IEU_COMMENT_CODE4,
842 IEU_COMMENT_CODE5,
843 WORKITEM_COMMENT_CODE1,
844 WORKITEM_COMMENT_CODE2,
845 WORKITEM_COMMENT_CODE3,
846 WORKITEM_COMMENT_CODE4,
847 WORKITEM_COMMENT_CODE5,
848 RETURN_STATUS,
849 ERROR_CODE,
850 LOGGING_LEVEL)
851 values
852 (
853 IEU_UWQM_AUDIT_LOG_S1.NEXTVAL,
854 1,
855 FND_GLOBAL.USER_ID,
856 SYSDATE,
857 FND_GLOBAL.USER_ID,
858 SYSDATE,
859 FND_GLOBAL.LOGIN_ID,
860 l_action_key,
861 l_event_key,
862 l_module,
863 P_WS_CODE,
864 690,
865 l_task_det_rec.l_workitem_pk_id_list(i),
866 l_task_det_rec.l_workitem_obj_code_list(i),
867 l_task_det_rec.l_work_item_number_list(i),
868 l_task_det_rec.l_status_id_list(i),
869 l_task_det_rec.l_status_id_list(i),
870 l_task_det_rec.l_owner_id_list(i),
871 l_task_det_rec.l_owner_id_list(i),
872 l_task_det_rec.l_owner_type_list(i),
873 l_task_det_rec.l_owner_type_list(i),
874 l_task_det_rec.l_assignee_id_list(i),
875 l_task_det_rec.l_assignee_id_list(i),
876 l_task_det_rec.l_assignee_type_list(i),
877 l_task_det_rec.l_assignee_type_list(i),
878 l_task_det_rec.l_source_object_id_list(i),
879 l_task_det_rec.l_source_object_id_list(i),
880 l_task_det_rec.l_source_object_type_code_list(i),
881 l_task_det_rec.l_source_object_type_code_list(i),
882 NULL,
883 NULL,
884 NULL,
885 NULL,
886 l_task_det_rec.l_distribution_status_id_list(i),
887 l_task_det_rec.l_distribution_status_id_list(i),
888 l_task_det_rec.l_priority_id_list(i),
889 l_task_det_rec.l_priority_id_list(i),
890 l_task_det_rec.l_due_date_list(i),
891 l_task_det_rec.l_due_date_list(i),
892 l_task_det_rec.l_reschedule_time_list(i),
893 l_task_det_rec.l_reschedule_time_list(i),
894 NULL,
895 NULL,
896 NULL,
897 NULL,
898 NULL,
899 l_workitem_comment_code1,
900 l_workitem_comment_code2,
901 l_workitem_comment_code3,
902 l_workitem_comment_code4,
903 l_workitem_comment_code5,
904 fnd_api.g_ret_sts_success,
905 NULL,
906 FND_PROFILE.VALUE('IEU_WR_DIST_AUDIT_LOG'));
907 EXCEPTION
908 WHEN dml_errors THEN
909 null;
910 END;
911
912 END IF; */
913
914
915
916 delete from ieu_uwqm_items
917 where status_id in (3,4)
918 and ws_id = l_ws_id
919 and ( to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') );
920
921 END IF; /* l_assct_ws_id is not null */
922
923 END IF; /*** l_ws_type = 'PRIMARY' ***/
924 commit;
925
926 EXCEPTION
927 WHEN fnd_api.g_exc_error THEN
928
929 retcode := 2;
930 fnd_msg_pub.Count_and_Get
931 (
932 p_count => x_msg_count,
933 p_data => x_msg_data
934 );
935 errbuf :=x_msg_Data;
936
937
938 WHEN OTHERS THEN
939 errbuf := sqlcode||' '||sqlerrm;
940 l_message := sqlcode||' '||sqlerrm;
941 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
942 retcode := 2;
943
944 END PURGE_WR_DATA;
945
946 PROCEDURE PURGE_WR_AUDIT_DATA
947 (
948 ERRBUF OUT NOCOPY VARCHAR2,
949 RETCODE OUT NOCOPY VARCHAR2,
950 P_OBJ_CODE IN VARCHAR2 DEFAULT NULL,
951 P_CREATION_DATE IN DATE DEFAULT NULL
952 )
953 IS
954
955 l_true NUMBER := 0;
956 l_message VARCHAR2(4000);
957
958 BEGIN
959
960 FND_MESSAGE.SET_NAME('IEU', 'IEU_WR_AUDIT_PURGE_INP_PARAMS');
961 FND_MESSAGE.SET_TOKEN('OBJECT_CODE', p_obj_code);
962 FND_MESSAGE.SET_TOKEN('CR_DATE', p_creation_date);
963 l_message := FND_MESSAGE.GET;
964 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
965
966 IF ( (p_creation_date IS not null) AND (p_obj_code IS not null) )
967 THEN
968
969 delete
970 from ieu_uwqm_audit_log
971 where workitem_pk_id in (select workitem_pk_id
972 from ieu_uwqm_audit_log
973 where trunc(to_date(creation_date, 'dd-mm-rrrr')) <=
974 trunc(to_date(p_creation_date, 'dd-mm-rrrr'))
975 and ((workitem_status_id_curr = 3) or (workitem_status_id_curr = 4)))
976 and workitem_obj_code = p_obj_code;
977 commit;
978 END IF;
979
980
981
982 EXCEPTION
983 WHEN OTHERS THEN
984
985 errbuf := sqlcode||' '||sqlerrm;
986 retcode := 2;
987 l_message := sqlcode || ' '||sqlerrm;
988 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
989
990 END PURGE_WR_AUDIT_DATA;
991
992 END IEU_WR_CON_PVT;
993