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