1 package body ECX_PURGE as
2 /* $Header: ECXPRGB.pls 120.4.12010000.3 2008/10/08 21:12:12 cpeixoto ship $*/
3 -- procedure PURGE
4 -- Delete records from ecx_outbound_logs which don't have item_type, item_key
5 -- (To delete records which don't have an entry in ecx_doclogs)
6 -- IN:
7 -- transaction_type - transaction type to delete, or null for all transaction type
8 -- transaction_subtype - transaction subtype to delete, or null for all transaction subtype
9 -- party_id - party id to delete, or null for all party id
10 -- party_site_id - party site id to delete, or null for all party site id
11 -- fromdate - from Date or null to start from begining
12 -- todate - end Date or null to delete till latest record
13 -- commitFlag- Do not commit if set to false
14 --
15 procedure PURGE_OUTBOUND(transaction_type in varchar2,
16 transaction_subtype in varchar2,
17 party_id in varchar2,
18 party_site_id in varchar2,
19 fromDate in date,
20 toDate in date,
21 commitFlag in boolean) IS
22
23 TYPE t_trigger_id_tl is TABLE of ecx_outbound_logs.trigger_id%type;
24 TYPE t_error_id_tl is TABLE of ecx_msg_logs.error_id%type;
25
26 v_trigger_id_tl t_trigger_id_tl := t_trigger_id_tl();
27 v_error_id_tl t_error_id_tl;
28
29 l_CursorID number;
30 l_result NUMBER;
31 l_Select VARCHAR2(2400);
32 l_trigger_ID ecx_outbound_logs.trigger_id%type;
33
34 cursor get_out_error_id (p_trigger_id in ecx_outbound_logs.TRIGGER_ID%type) is
35 select error_id from ecx_msg_logs where trigger_id = p_trigger_id;
36
37 bulk_delete_cycles pls_integer;
38 bulk_delete_first pls_integer;
39 bulk_delete_last pls_integer;
40
41 begin
42
43 l_Select:= 'select trigger_id from ecx_outbound_logs where 1=1 ';
44
45
46 if transaction_type is not null then
47 l_Select := l_Select || 'and transaction_type = :transaction_type ';
48 end if;
49 if transaction_subtype is not null then
50 l_Select := l_Select || 'and transaction_subtype = :transaction_subtype ';
51 end if;
52 if party_id is not null then
53 l_Select := l_Select || 'and party_id = :party_id ';
54 end if;
55 if party_site_id is not null then
56 l_Select := l_Select || 'and party_site_id = :party_site_id ';
57 end if;
58 if fromDate is not null then
59 l_Select := l_Select || 'and time_stamp >= :fromDate ';
60 end if;
61 if toDate is not null then
62 l_Select := l_Select || 'and time_stamp <= :toDate ';
63 end if;
64
65 l_Select := l_Select || ' for update nowait ';
66
67 l_CursorID := DBMS_SQL.OPEN_CURSOR;
68 DBMS_SQL.parse(l_CursorID, l_Select, DBMS_SQL.V7);
69
70 DBMS_SQL.define_column(l_CursorID, 1, l_trigger_id );
71
72 if transaction_type is not null then
73 DBMS_SQL.bind_variable(l_CursorID , ':transaction_type' , transaction_type);
74 end if;
75 if transaction_subtype is not null then
76 DBMS_SQL.bind_variable(l_CursorID , ':transaction_subtype' , transaction_subtype);
77 end if;
78 if party_id is not null then
79 DBMS_SQL.bind_variable(l_CursorID , ':party_id' , party_id);
80 end if;
81 if party_site_id is not null then
82 DBMS_SQL.bind_variable(l_CursorID , ':party_site_id' , party_site_id);
83 end if;
84 if fromDate is not null then
85 DBMS_SQL.bind_variable(l_CursorID , ':fromDate' , fromDate);
86 end if;
87 if toDate is not null then
88 DBMS_SQL.bind_variable(l_CursorID , ':toDate' , toDate);
89 end if;
90
91 l_result := DBMS_SQL.EXECUTE(l_CursorID);
92
93 loop
94
95 if dbms_sql.fetch_rows( l_CursorID ) > 0 then
96
97 DBMS_SQL.column_value(l_CursorID, 1, l_trigger_id );
98
99 v_trigger_id_tl.extend;
100
101 v_trigger_id_tl( v_trigger_id_tl.last ) := l_trigger_id;
102 else
103 exit;
104 end if;
105 end loop;
106
107 for i IN 1..v_trigger_id_tl.count loop
108
109 open get_out_error_id(v_trigger_id_tl(i));
110 fetch get_out_error_id bulk collect into v_error_id_tl ;
111 close get_out_error_id;
112
113
114 bulk_delete_cycles := round(v_error_id_tl.count/commit_frequency_ecx + 0.5);
115 -- dbms_output.put_line('commit_frequency = '|| to_char(commit_frequency_ecx));
116 if(v_error_id_tl.count > 0) then
117 -- dbms_output.put_line('Purge_Outbound.v_error_id_tl.count = '|| to_char(v_error_id_tl.count));
118 For i IN 1..bulk_delete_cycles loop
119 bulk_delete_first := ((i-1) * commit_frequency_ecx) + 1;
120 bulk_delete_last := bulk_delete_first + commit_frequency_ecx;
121 IF (bulk_delete_last > v_error_id_tl.count) THEN
122 bulk_delete_last := v_error_id_tl.count;
123 END IF;
124 FORALL j IN bulk_delete_first..bulk_delete_last
125 delete from ecx_error_msgs where error_id = v_error_id_tl(j);
126 IF (commitFlag) THEN
127 -- dbms_output.put_line('v_error_id_tl.count Purge_Outbound commiting ecx_error_msgs i = '|| to_char(i));
128 commit;
129 END IF;
130 FORALL j IN bulk_delete_first..bulk_delete_last
131 delete from ecx_msg_logs where error_id = v_error_id_tl(j);
132 IF (commitFlag) THEN
133 -- dbms_output.put_line('v_error_id_tl.count Purge_Outbound commiting ecx_msg_logs i = '|| to_char(i));
134 commit;
135 END IF;
136 end loop;
137 end if;
138
139 if (v_trigger_id_tl.count > 0) then
140 bulk_delete_cycles := round(v_trigger_id_tl.count/commit_frequency_ecx + 0.5);
141 For i IN 1..bulk_delete_cycles loop
142 bulk_delete_first := ((i-1) * commit_frequency_ecx) + 1;
143 bulk_delete_last := bulk_delete_first + commit_frequency_ecx;
144 IF (bulk_delete_last > v_trigger_id_tl.count) THEN
145 bulk_delete_last := v_trigger_id_tl.count;
146 END IF;
147 FORALL j IN bulk_delete_first..bulk_delete_last
148 delete from ecx_outbound_logs WHERE trigger_id = v_trigger_id_tl(j);
149 IF (commitFlag) THEN
150 commit;
151 END IF;
152 end loop;
153 end if;
154 END LOOP;
155 exception
156 WHEN others THEN
157 Wf_Core.Context('ECX_Purge', 'Purge_Outbound', transaction_type, transaction_subtype,party_id,party_site_id,to_char(fromDate),to_char(toDate));
158 raise;
159 end PURGE_OUTBOUND;
160 --
161 -- procedure PURGE
162 -- Delete ecx log from given criteria.
163 -- IN:
164 -- itemtype - Item type to delete, or null for all itemtypes
165 -- itemkey - Item key to delete, or null for all itemkeys
166 -- transaction_type - transaction type to delete, or null for all transaction type
167 -- transaction_subtype - transaction subtype to delete, or null for all transaction subtype
168 -- party_id - party id to delete, or null for all party id
169 -- party_site_id - party site id to delete, or null for all party site id
170 -- fromdate - from Date or null to start from begining
171 -- todate - end Date or null to delete till latest record
172 -- docommit- Do not commit if set to false
173 -- runtimeonly - Delete data which is associated with workflow, if set to true
174 --
175 procedure PURGE(item_type in varchar2,
176 item_key in varchar2,
177 transaction_type in varchar2,
178 transaction_subtype in varchar2,
179 party_id in varchar2,
180 party_site_id in varchar2,
181 fromDate in date,
182 toDate in date,
183 commitFlag in boolean,
184 runtimeonly in boolean) IS
185 l_msgId RAW(16);
186 l_item_type ecx_doclogs.item_type%type;
187 l_item_key ecx_doclogs.item_key%type;
188 l_errId number(16);
189 delCounter number(4);
190 l_commitFlag boolean := commitFlag;
191 l_runtimeonly boolean := runtimeonly;
192 l_CursorID number;
193 l_result NUMBER;
194 l_Select VARCHAR2(2400);
195
196
197
198 cursor get_in_trigger_id (p_msgid in ecx_doclogs.msgid%type) is
199 select trigger_id from ecx_inbound_logs where msgid is not null and msgid = hextoraw(p_msgid);
200 cursor get_out_trigger_id (p_msgid in ecx_doclogs.msgid%type) is
201 select trigger_id from ecx_outbound_logs where out_msgid is not null and out_msgid = hextoraw(p_msgid);
202 cursor get_ext_log_error_id (p_msgid in ecx_doclogs.msgid%type) is
203 select error_id from ecx_external_logs where out_msgid is not null and out_msgid = hextoraw(p_msgid);
204 cursor get_ext_ret_error_id (p_msgid in ecx_doclogs.msgid%type) is
205 select error_id from ecx_external_retry where msgid = p_msgid;
206 cursor get_in_error_id (p_trigger_id in ecx_msg_logs.trigger_id%type) is
207 select error_id from ecx_msg_logs where trigger_id = p_trigger_id;
208 cursor get_out_error_id (p_trigger_id in ecx_msg_logs.ERROR_ID%type) is
209 select error_id from ecx_msg_logs where trigger_id = p_trigger_id;
210
211 TYPE t_message_id_tl is TABLE of ecx_doclogs.msgid%type;
212 TYPE t_itemtype_id_tl is TABLE of ecx_doclogs.item_type%type;
213 TYPE t_itemkey_id_tl is TABLE of ecx_doclogs.item_key%type;
214 TYPE t_out_error_id_tl is TABLE of ecx_outbound_logs.error_id%type;
215 TYPE t_ext_log_error_id_tl is TABLE of ecx_external_logs.error_id%type;
216 TYPE t_ext_ret_error_id_tl is TABLE of ecx_external_retry.error_id%type;
217 TYPE t_in_trigger_id_tl is TABLE of ecx_msg_logs.trigger_id%type;
218 TYPE t_out_trigger_id_tl is TABLE of ecx_msg_logs.trigger_id%type;
219 TYPE t_in_error_id_tl is TABLE of ecx_msg_logs.error_id%type;
220
221 v_message_id_tl t_message_id_tl:= t_message_id_tl();
222 v_itemtype_tl t_itemtype_id_tl := t_itemtype_id_tl();
223 v_itemkey_tl t_itemkey_id_tl := t_itemkey_id_tl();
224 v_out_trigger_id_tl t_out_trigger_id_tl;
225 v_out_error_id_tl t_out_error_id_tl;
226 v_ext_log_error_id_tl t_ext_log_error_id_tl;
227 v_ext_ret_error_id_tl t_ext_ret_error_id_tl;
228 v_in_trigger_id_tl t_in_trigger_id_tl;
229 v_in_error_id_tl t_in_error_id_tl;
230
231 bulk_delete_first pls_integer;
232 bulk_delete_last pls_integer;
233
234 status varchar2(200);
235 result varchar2(200);
236 purgable boolean := true;
237 is_bulk_delete_cycle boolean := false;
238 begin
239 IF (commitFlag is null) THEN
240 l_commitFlag := false;
241 END IF;
242 IF (runtimeonly is null) THEN
243 l_runtimeonly := false;
244 END IF;
245
246
247 l_Select:= 'select msgid, item_type, item_key from ecx_doclogs where 1 = 1 ' ;
248
249 if item_type is not null then
250 l_Select := l_Select || 'and item_type = :item_type ';
251 end if;
252 if item_key is not null then
253 l_Select := l_Select || 'and item_key = :item_key ';
254 end if;
255 if transaction_type is not null then
256 l_Select := l_Select || 'and transaction_type = :transaction_type ';
257 end if;
258 if transaction_subtype is not null then
259 l_Select := l_Select || 'and transaction_subtype = :transaction_subtype ';
260 end if;
261 if party_id is not null then
262 l_Select := l_Select || 'and party_id = :party_id ';
263 end if;
264 if party_site_id is not null then
265 l_Select := l_Select || 'and party_site_id = :party_site_id ';
266 end if;
267 if fromDate is not null then
268 l_Select := l_Select || 'and time_stamp >= :fromDate ';
269 end if;
270 if toDate is not null then
271 l_Select := l_Select || 'and time_stamp <= :toDate ';
272 end if;
273
274 l_Select := l_Select || ' for update nowait ';
275
276 l_CursorID := DBMS_SQL.OPEN_CURSOR;
277 DBMS_SQL.parse(l_CursorID, l_Select, DBMS_SQL.V7);
278
279 DBMS_SQL.define_column_raw(l_CursorID, 1, l_msgId , 16);
280 DBMS_SQL.define_column(l_CursorID, 2, l_item_type, 8);
281 DBMS_SQL.define_column(l_CursorID, 3, l_item_key , 240);
282
283 if item_type is not null then
284 DBMS_SQL.bind_variable(l_CursorID , ':item_type' , item_type);
285 end if;
286 if item_key is not null then
287 DBMS_SQL.bind_variable(l_CursorID , ':item_key' , item_key);
288 end if;
289 if transaction_type is not null then
290 DBMS_SQL.bind_variable(l_CursorID , ':transaction_type' , transaction_type);
291 end if;
292 if transaction_subtype is not null then
293 DBMS_SQL.bind_variable(l_CursorID , ':transaction_subtype' , transaction_subtype);
294 end if;
295 if party_id is not null then
296 DBMS_SQL.bind_variable(l_CursorID , ':party_id' , party_id);
297 end if;
298 if party_site_id is not null then
299 DBMS_SQL.bind_variable(l_CursorID , ':party_site_id' , party_site_id);
300 end if;
301 if fromDate is not null then
302 DBMS_SQL.bind_variable(l_CursorID , ':fromDate' , fromDate);
303 end if;
304 if toDate is not null then
305 DBMS_SQL.bind_variable(l_CursorID , ':toDate' , toDate);
306 end if;
307
308 l_result := DBMS_SQL.EXECUTE(l_CursorID);
309
310 loop
311
312
313 if dbms_sql.fetch_rows( l_CursorID ) > 0 then
314
315 DBMS_SQL.column_value_raw(l_CursorID, 1, l_msgId );
316 DBMS_SQL.column_value(l_CursorID, 2, l_item_type );
317 DBMS_SQL.column_value(l_CursorID, 3, l_item_key );
318
319 v_message_id_tl.extend;
320 v_itemtype_tl.extend;
321 v_itemkey_tl.extend;
322
323
324 v_message_id_tl( v_message_id_tl.last ) := l_msgId;
325 v_itemtype_tl( v_itemtype_tl.last ) := l_item_type;
326 v_itemkey_tl( v_itemkey_tl.last ) := l_item_key;
327
328 else
329 exit;
330 end if;
331 end loop;
332
333
334
335 for i IN 1..v_message_id_tl.count loop
336 purgable := true;
337 status := '';
338 if(purgable) then
339 open get_in_trigger_id(v_message_id_tl(i));
340 fetch get_in_trigger_id bulk collect into v_in_trigger_id_tl ;
341 FOR i IN 1..v_in_trigger_id_tl.count
342 LOOP
343 open get_in_error_id (v_in_trigger_id_tl(i));
344 fetch get_in_error_id bulk collect into v_in_error_id_tl ;
345 FORALL i IN 1..v_in_error_id_tl.count
346 delete from ecx_error_msgs where error_id =v_in_error_id_tl(i);
347 FORALL i IN 1..v_in_error_id_tl.count
348 delete from ecx_msg_logs where error_id =v_in_error_id_tl(i);
349 close get_in_error_id;
350 END LOOP;
351 close get_in_trigger_id;
352
353 open get_out_trigger_id(v_message_id_tl(i));
354 fetch get_out_trigger_id bulk collect into v_out_trigger_id_tl ;
355 FOR i IN 1..v_out_trigger_id_tl.count
356 LOOP
357 open get_out_error_id (v_out_trigger_id_tl(i));
358 fetch get_out_error_id bulk collect into v_out_error_id_tl ;
359 FORALL i IN 1..v_out_error_id_tl.count
360 delete from ecx_error_msgs where error_id =v_out_error_id_tl(i);
361 FORALL i IN 1..v_out_error_id_tl.count
362 delete from ecx_msg_logs where error_id =v_out_error_id_tl(i);
363 close get_out_error_id;
364 END LOOP;
365 close get_out_trigger_id;
366
367
368 open get_ext_log_error_id(v_message_id_tl(i));
369 fetch get_ext_log_error_id bulk collect into v_ext_log_error_id_tl ;
370 close get_ext_log_error_id;
371
372 open get_ext_ret_error_id(v_message_id_tl(i));
373 fetch get_ext_ret_error_id bulk collect into v_ext_ret_error_id_tl;
374 close get_ext_ret_error_id;
375
376
377 FORALL i IN 1..v_ext_log_error_id_tl.count
378 delete from ecx_error_msgs where error_id = v_ext_log_error_id_tl(i);
379
380 FORALL i IN 1..v_ext_log_error_id_tl.count
381 delete from ecx_msg_logs where error_id = v_ext_log_error_id_tl(i);
382
383 FORALL i IN 1..v_ext_ret_error_id_tl.count
384 delete from ecx_error_msgs where error_id = v_ext_ret_error_id_tl(i);
385
386 FORALL i IN 1..v_ext_ret_error_id_tl.count
387 delete from ecx_msg_logs where error_id = v_ext_ret_error_id_tl(i);
388
389 delete from ecx_external_retry WHERE msgid =v_message_id_tl(i);
390 delete from ecx_inbound_logs WHERE msgid =v_message_id_tl(i);
391 delete from ecx_outbound_logs WHERE out_msgid =v_message_id_tl(i);
392 delete from ecx_external_logs WHERE out_msgid =v_message_id_tl(i);
393 delete from ecx_oxta_logmsg WHERE receipt_message_id =v_message_id_tl(i)
394 OR sender_message_id =v_message_id_tl(i);
395 delete from ecx_doclogs WHERE msgid = hextoraw(v_message_id_tl(i));
396
397 IF (l_commitFlag and (mod(i,commit_frequency_ecx) = 0 or (i = v_message_id_tl.count))) THEN
398 -- dbms_output.put_line('COMMITING i = '|| to_char(i));
399 commit;
400 END IF;
401
402 end if;
403 END LOOP ;
404
405 IF NOT l_runtimeonly THEN
406 PURGE_OUTBOUND(transaction_type, transaction_subtype, party_id, party_site_id, fromDate, toDate, l_commitFlag);
407 END IF;
408 exception
409 WHEN others THEN
410 if(get_in_error_id%ISOPEN) then
411 close get_in_error_id;
412 end if;
413 if(get_out_error_id%ISOPEN) then
414 close get_out_error_id;
415 end if;
416 if(get_in_trigger_id%ISOPEN) then
417 close get_in_trigger_id;
418 end if;
419 if(get_out_trigger_id%ISOPEN) then
420 close get_out_trigger_id;
421 end if;
422 if(get_ext_log_error_id%ISOPEN) then
423 close get_ext_log_error_id;
424 end if;
425 if(get_ext_ret_error_id%ISOPEN) then
426 close get_ext_ret_error_id;
427 end if;
428 Wf_Core.Context('ECX_Purge', 'Purge', item_type, item_key,transaction_type, transaction_subtype,party_id,party_site_id,to_char(fromDate),to_char(toDate));
429 raise;
430 end PURGE;
431
432 --
433 -- procedure Items
434 -- Delete items with end_time before argument.
435 -- IN:
436 -- itemtype - Item type to delete, or null for all itemtypes
437 -- itemkey - Item key to delete, or null for all itemkeys
438 -- enddate - Date to obsolete to
439 -- docommit- Do not commit if set to false
440 -- runtimeonly - Delete data which is associated with workflow, if set to true
441
442 procedure PURGE_ITEMS(itemType in varchar2,
443 itemKey in varchar2,
444 endDate in date,
445 docommit in boolean,
446 runtimeonly in boolean) IS
447
448 l_msgId RAW(16);
449 l_errId number(16);
450 delCounter number(4);
451 l_commitFlag boolean :=true;
452 l_runtimeonly boolean := false;
453
454 /** l_int_trans_id ecx_transactions.transaction_id%type;
455 l_ext_trans_type ecx_ext_processes.ext_type%type;
456 l_ext_trans_subtype ecx_ext_processes.ext_subtype%type;
457
458 cursor get_itrans is
459 select transaction_id from ecx_transactions;
460
461 cursor get_ext_trans(p_trans_id in ecx_transactions.transaction_id%type) is
462 select ext_type, ext_subtype from ecx_ext_processes ecxextpc where ecxextpc.transaction_id = p_trans_id;
463
464 TYPE t_int_trans_id_tl is TABLE of ecx_transactions.transaction_id%type;
465 v_int_trans_id_tl t_int_trans_id_tl;**/
466
467
468 /**TYPE t_msg_id_tl is TABLE of ecx_doclogs.msgid%type;
469 v_msg_id_tl t_msg_id_tl;**/
470
471 cursor get_in_trigger_id (p_msgid in ecx_doclogs.msgid%type) is
472 select trigger_id from ecx_inbound_logs where msgid is not null and msgid = hextoraw(p_msgid);
476 select error_id from ecx_external_logs where out_msgid is not null and out_msgid = hextoraw(p_msgid);
473 cursor get_out_trigger_id (p_msgid in ecx_doclogs.msgid%type) is
474 select trigger_id from ecx_outbound_logs where out_msgid is not null and out_msgid = hextoraw(p_msgid);
475 cursor get_ext_log_error_id (p_msgid in ecx_doclogs.msgid%type) is
477 cursor get_ext_ret_error_id (p_msgid in ecx_doclogs.msgid%type) is
478 select error_id from ecx_external_retry where msgid = p_msgid;
479 cursor get_in_error_id (p_trigger_id in ecx_msg_logs.trigger_id%type) is
480 select error_id from ecx_msg_logs where trigger_id = p_trigger_id;
481 cursor get_out_error_id (p_trigger_id in ecx_msg_logs.ERROR_ID%type) is
482 select error_id from ecx_msg_logs where trigger_id = p_trigger_id;
483
484
485 TYPE t_message_id_tl is TABLE of ecx_doclogs.msgid%type;
486 TYPE t_itemtype_id_tl is TABLE of ecx_doclogs.item_type%type;
487 TYPE t_itemkey_id_tl is TABLE of ecx_doclogs.item_key%type;
488 TYPE t_out_error_id_tl is TABLE of ecx_outbound_logs.error_id%type;
489 TYPE t_ext_log_error_id_tl is TABLE of ecx_external_logs.error_id%type;
490 TYPE t_ext_ret_error_id_tl is TABLE of ecx_external_retry.error_id%type;
491 TYPE t_in_trigger_id_tl is TABLE of ecx_msg_logs.trigger_id%type;
492 TYPE t_out_trigger_id_tl is TABLE of ecx_msg_logs.trigger_id%type;
493 TYPE t_in_error_id_tl is TABLE of ecx_msg_logs.error_id%type;
494
495 v_msgid ecx_doclogs.msgid%type;
496 v_message_id_tl t_message_id_tl;
497 v_itemtype_tl t_itemtype_id_tl;
498 v_itemkey_tl t_itemkey_id_tl;
499 v_out_trigger_id_tl t_out_trigger_id_tl;
500 v_out_error_id_tl t_out_error_id_tl;
501 v_ext_log_error_id_tl t_ext_log_error_id_tl;
502 v_ext_ret_error_id_tl t_ext_ret_error_id_tl;
503 v_in_trigger_id_tl t_in_trigger_id_tl;
504 v_in_error_id_tl t_in_error_id_tl;
505 bulk_delete_first pls_integer;
506 bulk_delete_last pls_integer;
507
508 status varchar2(200);
509 result varchar2(200);
510 purgable boolean := true;
511 is_bulk_delete_cycle boolean := false;
512 begin
513 /** open get_itrans;
514 fetch get_itrans bulk collect into v_int_trans_id_tl;
515 close get_itrans;
516
517 for i in 1..v_int_trans_id_tl.count loop
518 l_int_trans_id := v_int_trans_id_tl(i);
519 -- dbms_output.put_line('l_int_trans_id = '||to_char(l_int_trans_id));
520 for ext_trans in get_ext_trans(l_int_trans_id) loop
521 l_ext_trans_type := ext_trans.ext_type;
522 l_ext_trans_subtype := ext_trans.ext_subtype;
523 -- dbms_output.put_line(' ext trans type = '||l_ext_trans_type || ', ext trans subtype = '||l_ext_trans_subtype);
524 PURGE(item_type => itemType, item_key => itemKey,
525 toDate => endDate, commitFlag => docommit, runtimeonly => runtimeonly,
526 transaction_type => l_ext_trans_type, transaction_subtype => l_ext_trans_subtype);
527 end loop;
528 end loop; **/
529
530 /* fix for bug 5852521 */
531 if nvl(fnd_profile.value('ECX_PURGE_WF'),'Y') = 'N' then
532 return;
533 end if;
534
535 FOR i in 1..WF_PURGE.l_itemtypeTAB.count loop
536
537 select msgid into v_msgid from ecx_doclogs where
538 (item_type = WF_PURGE.l_itemtypeTAB(i)) and (item_key =WF_PURGE.l_itemkeyTAB(i) ) ;
539 purgable := true;
540 status := '';
541 if(purgable) then
542 open get_in_trigger_id(v_msgid);
543 fetch get_in_trigger_id bulk collect into v_in_trigger_id_tl ;
544 FOR i IN 1..v_in_trigger_id_tl.count
545 LOOP
546 open get_in_error_id (v_in_trigger_id_tl(i));
547 fetch get_in_error_id bulk collect into v_in_error_id_tl ;
548 FORALL i IN 1..v_in_error_id_tl.count
549 delete from ecx_error_msgs where error_id =v_in_error_id_tl(i);
550 FORALL i IN 1..v_in_error_id_tl.count
551 delete from ecx_msg_logs where error_id =v_in_error_id_tl(i);
552 close get_in_error_id;
553 END LOOP;
554 close get_in_trigger_id;
555
556 open get_out_trigger_id(v_msgid);
557 fetch get_out_trigger_id bulk collect into v_out_trigger_id_tl ;
558 FOR i IN 1..v_out_trigger_id_tl.count
559 LOOP
560 open get_out_error_id (v_out_trigger_id_tl(i));
561 fetch get_out_error_id bulk collect into v_out_error_id_tl ;
562 FORALL i IN 1..v_out_error_id_tl.count
563 delete from ecx_error_msgs where error_id =v_out_error_id_tl(i);
564 FORALL i IN 1..v_out_error_id_tl.count
565 delete from ecx_msg_logs where error_id =v_out_error_id_tl(i);
566 close get_out_error_id;
567 END LOOP;
568 close get_out_trigger_id;
569
570
571 open get_ext_log_error_id(v_msgid);
572 fetch get_ext_log_error_id bulk collect into v_ext_log_error_id_tl ;
573 close get_ext_log_error_id;
574
575 open get_ext_ret_error_id(v_msgid);
576 fetch get_ext_ret_error_id bulk collect into v_ext_ret_error_id_tl;
577 close get_ext_ret_error_id;
578
579
580 FORALL i IN 1..v_ext_log_error_id_tl.count
581 delete from ecx_error_msgs where error_id = v_ext_log_error_id_tl(i);
582
583 FORALL i IN 1..v_ext_log_error_id_tl.count
584 delete from ecx_msg_logs where error_id = v_ext_log_error_id_tl(i);
585
586 FORALL i IN 1..v_ext_ret_error_id_tl.count
587 delete from ecx_error_msgs where error_id = v_ext_ret_error_id_tl(i);
588
589 FORALL i IN 1..v_ext_ret_error_id_tl.count
590 delete from ecx_msg_logs where error_id = v_ext_ret_error_id_tl(i);
591
592 delete from ecx_external_retry WHERE msgid =v_msgid;
593 delete from ecx_inbound_logs WHERE msgid =v_msgid;
594 delete from ecx_outbound_logs WHERE out_msgid =v_msgid;
595 delete from ecx_external_logs WHERE out_msgid =v_msgid;
596 delete from ecx_oxta_logmsg WHERE receipt_message_id =v_msgid
597 OR sender_message_id =v_msgid;
598 delete from ecx_doclogs WHERE msgid = hextoraw(v_msgid);
599
600 /** IF (l_commitFlag and (mod(i,commit_frequency_ecx) = 0 or (i = v_msg_id_tl.count))) THEN
601 -- dbms_output.put_line('COMMITING i = '|| to_char(i));
602 commit;
603 END IF;**/
604
605 end if;
606
607 END LOOP;
608 commit;
609 EXCEPTION
610 WHEN others THEN
611 /** if(get_itrans%ISOPEN) then
612 close get_itrans;
613 end if;
614 if(get_ext_trans%ISOPEN) then
615 close get_ext_trans;
616 end if;**/
617 if(get_in_error_id%ISOPEN) then
618 close get_in_error_id;
619 end if;
620 if(get_out_error_id%ISOPEN) then
621 close get_out_error_id;
622 end if;
623 if(get_in_trigger_id%ISOPEN) then
624 close get_in_trigger_id;
625 end if;
626 if(get_out_trigger_id%ISOPEN) then
627 close get_out_trigger_id;
628 end if;
629 if(get_ext_log_error_id%ISOPEN) then
630 close get_ext_log_error_id;
631 end if;
632 if(get_ext_ret_error_id%ISOPEN) then
633 close get_ext_ret_error_id;
634 end if;
635
636 Wf_Core.Context('ECX_Purge', 'Purge_Items', itemType, itemKey, to_char(endDate));
637 -- raise;
638 END PURGE_ITEMS;
639 -- procedure Purge_Transactions
640 --This procedure has been incorporated to make the CP for purging obsolete ECX data.
641 --Delete log details wihin the stipulated date range.
642 -- IN:
643 -- transaction_type - Transaction type to delete, or null for all transaction types
644 -- transaction_subtype - Transaction subtype to delete, or null for all subtypes
645 -- fromdate - Date from which the data to delete.
646 -- todate - Date upto which data has to delete.
647 -- docommit- Do not commit if set to false.
648 procedure PURGE_TRANSACTIONS(
649 transaction_type in varchar2 default null,
650 transaction_subtype in varchar2 default null,
651 fromdate in date default null,
652 todate in date default null,
653 docommit in boolean default true
654 ) IS
655
656 l_int_trans_id ecx_transactions.transaction_id%type;
657 l_ext_trans_type ecx_ext_processes.ext_type%type;
658 l_ext_trans_subtype ecx_ext_processes.ext_subtype%type;
659
660 cursor get_itrans_ts(p_int_trans_type ecx_transactions.transaction_type%type,
661 p_int_trans_subtype in ecx_transactions.transaction_subtype%type) is
662 select transaction_id
663 from ecx_transactions
664 WHERE transaction_type = p_int_trans_type AND
665 transaction_subtype = p_int_trans_subtype;
666 cursor get_itrans_t(p_int_trans_type ecx_transactions.transaction_type%type) is
667 select transaction_id
668 from ecx_transactions
669 WHERE transaction_type = p_int_trans_type;
670 cursor get_itrans_s(p_int_trans_subtype in ecx_transactions.transaction_subtype%type) is
671 select transaction_id
672 from ecx_transactions
673 WHERE transaction_subtype = p_int_trans_subtype;
674 Cursor get_itrans is
675 select transaction_id from ecx_transactions;
676 cursor get_ext_trans(p_trans_id in ecx_transactions.transaction_id%type) is
677 select ext_type, ext_subtype from ecx_ext_processes ecxextpc where ecxextpc.transaction_id = p_trans_id;
678
679 TYPE t_int_trans_id_tl is TABLE of ecx_transactions.transaction_id%type;
680 v_int_trans_id_tl t_int_trans_id_tl;
681
682 BEGIN
683 if (transaction_type is not null and transaction_subtype is not null) then
684 open get_itrans_ts(transaction_type, transaction_subtype);
685 fetch get_itrans_ts bulk collect into v_int_trans_id_tl;
686 close get_itrans_ts;
687 elsif (transaction_type is null and transaction_subtype is not null) then
688 open get_itrans_s(transaction_subtype);
689 fetch get_itrans_s bulk collect into v_int_trans_id_tl;
690 close get_itrans_s;
691 elsif (transaction_type is not null and transaction_subtype is null) then
692 open get_itrans_t(transaction_type);
693 fetch get_itrans_t bulk collect into v_int_trans_id_tl;
694 close get_itrans_t;
695 elsif (transaction_type is null and transaction_subtype is null) then
696 open get_itrans;
697 fetch get_itrans bulk collect into v_int_trans_id_tl;
698 close get_itrans;
699 end if;
700 for i in 1..v_int_trans_id_tl.count loop
701 l_int_trans_id := v_int_trans_id_tl(i);
702 -- dbms_output.put_line('l_int_trans_id = '||to_char(l_int_trans_id));
703 for ext_trans in get_ext_trans(l_int_trans_id) loop
704 l_ext_trans_type := ext_trans.ext_type;
705 l_ext_trans_subtype := ext_trans.ext_subtype;
706 -- dbms_output.put_line(' ext trans type = '||l_ext_trans_type || ', ext trans subtype = '||l_ext_trans_subtype);
707 PURGE(transaction_type => l_ext_trans_type, transaction_subtype => l_ext_trans_subtype,
708 fromdate =>fromdate,todate => todate);
709 end loop;
710 end loop;
711 EXCEPTION
712 WHEN others THEN
713 if(get_itrans%ISOPEN) then
714 close get_itrans;
715 end if;
716 if(get_itrans_ts%ISOPEN) then
717 close get_itrans_ts;
718 end if;
719 if(get_itrans_t%ISOPEN) then
720 close get_itrans_t;
721 end if;
722 if(get_itrans_s%ISOPEN) then
723 close get_itrans_s;
724 end if;
725 if(get_ext_trans%ISOPEN) then
726 close get_ext_trans;
727 end if;
728 Wf_Core.Context('ECX_Purge', 'Purge_Transactions', transaction_Type, transaction_subtype, to_char(toDate));
729 raise;
730 END PURGE_TRANSACTIONS;
731 --
732
733 --Procedure TotalConcurrent
734 -- This wil be called from CP to purge obsolete ECX data.
735 -- IN:
736 -- errbuf - CPM error message
737 -- retcode - CPM return code (0 = success, 1 = warning, 2 = error)
738 -- transactiontype - Transaction type to delete, or null for all transactiontype
739 -- transactionsubtype - Transaction subtype to delete, or null for all transaction subtype.
740 -- fromdate - Date from which the data to delete.
741 -- todate - Date upto which data has to delete.
742 -- x_commit_frequency - The freq. at which commit will take place during deletion.
743 procedure TotalConcurrent(
744 errbuf out NOCOPY varchar2 ,
745 retcode out NOCOPY varchar2,
746 transaction_type in varchar2 default null,
747 transaction_subtype in varchar2 default null,
748 fromdate in date default null,
749 todate in date default null,
750 x_commit_frequency in number )
751 is
752 errname varchar2(30);
753 errmsg varchar2(2000);
754 errstack varchar2(2000);
755 --l_transactiontype ecx_outbound_logs.transaction_type%type:=transactiontype;
756 -- l_transactionsubtype ecx_outbound_logs.transaction_subtype%type:=transactionsubtype;
757 l_fromdate date:=fromdate;
758 l_todate date:=todate;
759 docommit boolean := TRUE;
760 l_sql varchar2(500);
761 l_msg_inst varchar2(200);
762 begin
763
764 ecx_purge.commit_frequency_ecx := x_commit_frequency;
765 ecx_purge.purge_transactions(transaction_type=>transaction_type,
766 transaction_subtype=>transaction_subtype,
767 fromdate=>l_fromdate,
768 todate=>l_todate);
769
770 -- Return 0 for successful completion.
771 errbuf := '';
772 retcode := '0';
773 -- wf_purge.persistence_type := 'TEMP'; -- reset to the default value
774 --wf_purge.commit_frequency := 500; -- reset to the default value
775
776 exception
777 when others then
778 -- Retrieve error message into errbuf
779 wf_core.get_error(errname, errmsg, errstack);
780 if (errmsg is not null) then
781 errbuf := errmsg;
782 else
783 errbuf := sqlerrm;
784 end if;
785
786 -- Return 2 for error.
787 retcode := '2';
788
789 -- Reset persistence type to the default value
790 -- wf_purge.persistence_type := 'TEMP';
791 end TotalConcurrent;
792
793 --show errors package body ECX_PURGE
794 --select to_date('SQLERROR') from user_errors
795 --where type = 'PACKAGE BODY'
796 --and name = 'ECX_PURGE'
797 END ecx_purge;
[Home] [Help]