Oracle EBS Workflow: Purge WFERROR (System: Error) Workflow Items

針對一些未被Purge Obsolete Workflow Runtime Data清除的WFERROR (System: Error) workflow item, 可用script來處理,但要小心, 最好先查看是什麼問題造成error.

select *
  from wf_items
 where item_type = 'WFERROR'
   and parent_item_type is null
   and end_date is null;

select ac.name Activity,
         ias.activity_result_code Result,
         ias.error_name ERROR_NAME,
         ias.error_message ERROR_MESSAGE,
         ias.error_stack ERROR_STACK
 from wf_item_activity_statuses ias,
        wf_process_activities pa,         wf_activities ac,         wf_activities ap,         wf_items i
where ias.item_type = '&Item_Type_From_b1'    and ias.item_key = '&Item_Key_From_b1'
   and ias.activity_status = 'ERROR'
   and ias.process_activity = pa.instance_id
   and pa.activity_name = ac.name
   and pa.activity_item_type = ac.item_type
   and pa.process_name = ap.name
   and pa.process_item_type = ap.item_type
   and pa.process_version = ap.version
   and i.item_type = '&Item_Type_From_b1'
   and i.item_key = ias.item_key
   and i.begin_date >= ac.begin_date
   and i.begin_date < nvl(ac.end_date, i.begin_date+1)
order by ias.begin_date, ias.execution_time;

用SQL_1可放棄workflow項目, 接著再執行Purge Obsolete Workflow Runtime Data, 使用參數:

   Item Type: System: Error
   Age: 0
   Persistence Type: Temporary
   Core Workflows Only: N
   Commit Frequency: 500
   Signed Notifications: N

測試結果,table中的筆數大約少了2.2% .

--SQL_1
Declare
 counter number;
 Cursor abort_wf is
   select item_type, item_key
     from wf_items
  where item_type = 'WFERROR'
     and parent_item_type is null
     and end_date is null;

Begin
  counter := 1 ;
  For wf In abort_wf Loop
    Begin
      WF_ENGINE.AbortProcess('WFERROR', wf.item_key);
      counter := counter + 1 ;
      if counter > 1000 then
         counter := 1 ;
         commit;
      end if;
    End;
  End loop;
  commit;
End;

REF: How to Purge WFERROR (System: Error) Workflow Items? (Doc ID 804622.1)