REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
REM dbdrv: checkfile:~PROD:~PATH:~FILE
/*=======================================================================+
 |  Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
 |                            All rights reserved.                       |
 +=======================================================================+
 | FILENAME
 |   wfprgb.pls
 | DESCRIPTION
 |   PL/SQL body for package:  WF_PURGE
 | NOTES
 |   Routines to purge obsolete runtime data
 | MODIFICATION LOG:
 |   12/01 JWSMITH BUG2070056 - Performance fix in procedure Items
 |   04/02 JWSMITH BUG2208127 - Performance fix in procedure Activities
 |   12/02 CTILLEY BUG2642057 - Added FORCE parameter to procedure Items
 |                              to allow for child items to be deleted
 |                              even if parent is not complete.
 |   02/11 CTILLEY BUG2755885 - Added rowid to item2purge in procedure
 |                              Items to resolve infinite loop
 *=======================================================================*/

SET VERIFY OFF;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;

create or replace package body WF_PURGE as
/* $Header: wfprgb.pls 26.46 2004/12/13 06:24:38 averma ship $ */

-- procedure Move_To_History
--   Move wf_item_activity_status rows for particular itemtype/key from
--   main table to history table.
-- IN:
--   itemtype - Item type to move, or null for all itemtypes
--   itemkey - Item key to move, or null for all itemkeys
--
procedure Move_To_History(
  itemtype in varchar2,
  itemkey in varchar2)
is
 
begin


 -- Bug 3228475 Disallow partial values being passed for itemtype
 -- or itemkey
 if ((itemkey is not null and (instr(itemkey,'%')>0 or itemtype is null))
         or (instr(itemtype,'%')>0)) then
      Wf_Core.Raise('WFSQL_ARGS');
 end if;
 
 if (itemtype is not null and itemkey is null) then
  -- Insert into history table
  insert into WF_ITEM_ACTIVITY_STATUSES_H (
    ITEM_TYPE,
    ITEM_KEY,
    PROCESS_ACTIVITY,
    ACTIVITY_STATUS,
    ACTIVITY_RESULT_CODE,
    ASSIGNED_USER,
    NOTIFICATION_ID,
    BEGIN_DATE,
    END_DATE,
    EXECUTION_TIME,
    ERROR_NAME,
    ERROR_MESSAGE,
    ERROR_STACK
  ) select
    ITEM_TYPE,
    ITEM_KEY,
    PROCESS_ACTIVITY,
    ACTIVITY_STATUS,
    ACTIVITY_RESULT_CODE,
    ASSIGNED_USER,
    NOTIFICATION_ID,
    BEGIN_DATE,
    END_DATE,
    EXECUTION_TIME,
    ERROR_NAME,
    ERROR_MESSAGE,
    ERROR_STACK
  from WF_ITEM_ACTIVITY_STATUSES
  where item_type = itemtype;

  -- Delete from main table
  delete from WF_ITEM_ACTIVITY_STATUSES
  where ITEM_TYPE =  itemtype;  
  
  elsif (itemtype is not null) then
   insert into WF_ITEM_ACTIVITY_STATUSES_H (
    ITEM_TYPE,
    ITEM_KEY,
    PROCESS_ACTIVITY,
    ACTIVITY_STATUS,
    ACTIVITY_RESULT_CODE,
    ASSIGNED_USER,
    NOTIFICATION_ID,
    BEGIN_DATE,
    END_DATE,
    EXECUTION_TIME,
    ERROR_NAME,
    ERROR_MESSAGE,
    ERROR_STACK
  ) select
    ITEM_TYPE,
    ITEM_KEY,
    PROCESS_ACTIVITY,
    ACTIVITY_STATUS,
    ACTIVITY_RESULT_CODE,
    ASSIGNED_USER,
    NOTIFICATION_ID,
    BEGIN_DATE,
    END_DATE,
    EXECUTION_TIME,
    ERROR_NAME,
    ERROR_MESSAGE,
    ERROR_STACK
  from WF_ITEM_ACTIVITY_STATUSES
  where item_type = itemtype
  and item_key = itemkey;

  -- Delete from main table
  delete from WF_ITEM_ACTIVITY_STATUSES
  where ITEM_TYPE =  itemtype
  and ITEM_KEY =  itemkey;  

  else   
    insert into WF_ITEM_ACTIVITY_STATUSES_H (
    ITEM_TYPE,
    ITEM_KEY,
    PROCESS_ACTIVITY,
    ACTIVITY_STATUS,
    ACTIVITY_RESULT_CODE,
    ASSIGNED_USER,
    NOTIFICATION_ID,
    BEGIN_DATE,
    END_DATE,
    EXECUTION_TIME,
    ERROR_NAME,
    ERROR_MESSAGE,
    ERROR_STACK
  ) select
    ITEM_TYPE,
    ITEM_KEY,
    PROCESS_ACTIVITY,
    ACTIVITY_STATUS,
    ACTIVITY_RESULT_CODE,
    ASSIGNED_USER,
    NOTIFICATION_ID,
    BEGIN_DATE,
    END_DATE,
    EXECUTION_TIME,
    ERROR_NAME,
    ERROR_MESSAGE,
    ERROR_STACK
  from WF_ITEM_ACTIVITY_STATUSES;


  -- Delete from main table
   execute immediate 'truncate table WF_ITEM_ACTIVITY_STATUSES';
  end if;

exception
  when others then
    Wf_Core.Context('Wf_Purge', 'Move_To_History', itemtype, itemkey);
    raise;
end Move_To_History;

--
-- procedure Item_Activity_Statuses
--   Delete from wf_item_activity_statuses and wf_item_activity_statuses_h
--   where end_date before argument.
-- IN:
--   itemtype - Item type to delete, or null for all itemtypes
--   itemkey - Item key to delete, or null for all itemkeys
--   enddate - Date to obsolete to
--
procedure Item_Activity_Statuses(
  itemtype in varchar2,
  itemkey in varchar2,
  enddate in date)
is
  
begin
 
  -- Disallow future date in enddate
  -- Avoid something being purged before its defined persistence period.
  if (enddate > sysdate) then
    Wf_Core.Token('SYSDATE', to_char(sysdate));
    Wf_Core.Raise('WF_FUTURE_END_DATE');
  end if;

  
  -- Bug 3228475 Disallow partial values being passed for itemtype 
  -- or itemkey
  if ((itemkey is not null and (instr(itemkey,'%')>0 or itemtype is null))
       or (instr(itemtype,'%')>0)) then
      Wf_Core.Raise('WFSQL_ARGS');
  end if;

  if (itemtype is not null and itemkey is null) then
  -- Delete from _H history table
    delete from WF_ITEM_ACTIVITY_STATUSES_H WIAS
    where WIAS.ITEM_TYPE = itemtype
    and not exists
      (select null
      from WF_ITEMS WI
      where WI.ITEM_TYPE = WIAS.ITEM_TYPE
      and WI.ITEM_KEY = WIAS.ITEM_KEY
      and WI.END_DATE is null)
    and exists
      (select null
      from WF_ITEM_TYPES WIT
      where WIAS.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
      and WIAS.ITEM_TYPE = WIT.NAME
    and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type);

    -- Delete from main table.
    delete from WF_ITEM_ACTIVITY_STATUSES WIAS
    where WIAS.ITEM_TYPE = itemtype
    and not exists
      (select null
      from WF_ITEMS WI
      where WI.ITEM_TYPE = WIAS.ITEM_TYPE
      and WI.ITEM_KEY = WIAS.ITEM_KEY
      and WI.END_DATE is null)
    and exists
      (select null
      from WF_ITEM_TYPES WIT
      where WIAS.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
      and WIAS.ITEM_TYPE = WIT.NAME
    and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type);

 
 elsif (itemtype is not null) then
  -- Delete from _H history table.
    delete from WF_ITEM_ACTIVITY_STATUSES_H WIAS
    where ITEM_TYPE = itemtype
    and ITEM_KEY = itemkey
    and not exists
      (select null
      from WF_ITEMS WI
      where WI.ITEM_TYPE = WIAS.ITEM_TYPE
      and WI.ITEM_KEY = WIAS.ITEM_KEY
      and WI.END_DATE is null)
    and exists
      (select null
      from WF_ITEM_TYPES WIT
      where WIAS.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
      and WIAS.ITEM_TYPE = WIT.NAME
    and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type);

    -- Delete from main table.
    delete from WF_ITEM_ACTIVITY_STATUSES WIAS
    where ITEM_TYPE = itemtype
    and ITEM_KEY = itemkey
    and not exists
      (select null
      from WF_ITEMS WI
      where WI.ITEM_TYPE = WIAS.ITEM_TYPE
      and WI.ITEM_KEY = WIAS.ITEM_KEY
      and WI.END_DATE is null)
    and exists
      (select null
      from WF_ITEM_TYPES WIT
      where WIAS.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
      and WIAS.ITEM_TYPE = WIT.NAME
    and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type);

  
  else
  
    -- Delete from _H history table.
    delete from WF_ITEM_ACTIVITY_STATUSES_H WIAS
    where not exists
      (select null
      from WF_ITEMS WI
      where WI.ITEM_TYPE = WIAS.ITEM_TYPE
      and WI.ITEM_KEY = WIAS.ITEM_KEY
      and WI.END_DATE is null)
    and exists
      (select null
      from WF_ITEM_TYPES WIT
      where WIAS.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
      and WIAS.ITEM_TYPE = WIT.NAME
    and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type);

    -- Delete from main table.
    delete from WF_ITEM_ACTIVITY_STATUSES WIAS
    where not exists
      (select null
      from WF_ITEMS WI
      where WI.ITEM_TYPE = WIAS.ITEM_TYPE
      and WI.ITEM_KEY = WIAS.ITEM_KEY
      and WI.END_DATE is null)
    and exists
      (select null
      from WF_ITEM_TYPES WIT
      where WIAS.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
      and WIAS.ITEM_TYPE = WIT.NAME
    and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type);

  end if;

exception
  when others then
    Wf_Core.Context('Wf_Purge', 'Item_Activity_Statuses', itemtype,
                    itemkey, to_char(enddate));
    raise;
end Item_Activity_Statuses;

--
-- procedure Items
--   Delete items with end_time before argument.
-- IN:
--   itemtype - Item type to delete, or null for all itemtypes
--   itemkey - Item key to delete, or null for all itemkeys
--   enddate - Date to obsolete to
--   docommit- Do not commit if set to false
--
procedure Items(
  itemtype in varchar2,
  itemkey in varchar2,
  enddate in date,
  docommit in boolean,
  force in boolean)
is
  dummy     varchar2(240);
  c_item_type varchar2(8);
  c_item_key varchar2(240);
  keep  number;                      -- Bug 1895332 

  xrowid varchar2(80);  -- Bug 2755885 keep track of rowid 

 -- CTILLEY bug 2755885 include rowid in where clause to resolve 
 -- infinite loop 
 
 -- CTILLEY bug 3228475 - remove like item type and item key to
 -- performance violation.  Added item5purge to query records
 -- where itemtype is passed but no itemkey.  No longer supporting
 -- partial values for either item type or item key.

   cursor item2purge is
      select WI.ROWID, WI.ITEM_TYPE, WI.ITEM_KEY
          from  WF_ITEMS WI
          where WI.ITEM_TYPE = itemtype
           and  WI.ROWID > xrowid
           and exists
              (select null
               from WF_ITEM_TYPES WIT
               where WI.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
               and WI.ITEM_TYPE = WIT.NAME
               and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
     order by WI.ROWID;

  -- JWSMITH bug 2070056 - add new cursor for performance
   cursor item3purge is
     select /*+ FIRST_ROWS */ WI.ITEM_TYPE, WI.ITEM_KEY
     from  WF_ITEMS WI
     where WI.ITEM_KEY = itemkey
      and  WI.ITEM_TYPE = itemtype
      and exists
         (select null
          from WF_ITEM_TYPES WIT
          where WI.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
          and WI.ITEM_TYPE = WIT.NAME
          and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type);
 
    -- CTILLEY bug 2642057 - added new cursor for use when FORCE
    -- arg is TRUE.
    cursor item4purge is
       select /*+ FIRST_ROWS */ WI.ITEM_TYPE, WI.ITEM_KEY
       from  WF_ITEMS WI
       where WI.ITEM_KEY = itemkey
       and  WI.ITEM_TYPE = itemtype;
 
    -- CTILLEY bug 3228475 - Added new cursor to resolve performance violation
    -- of using like condition.  We will no longer support passing partial values
    -- for item type or item key.
    
   cursor item5purge is
     select WI.ROWID, WI.ITEM_TYPE, WI.ITEM_KEY
     from  WF_ITEMS WI
     where  WI.ROWID > xrowid
      and exists
         (select null
          from WF_ITEM_TYPES WIT
          where WI.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
          and WI.ITEM_TYPE = WIT.NAME
          and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
      order by WI.ROWID;     
   
 
   cursor nid_loop (p_item_type varchar2, p_item_key varchar2) is
        select notification_id,
               rowid,
               'S' tab
        from   WF_ITEM_ACTIVITY_STATUSES
        where  item_type = p_item_type
        and    item_key  = p_item_key
        and    notification_id is not null
        union all
        select notification_id,
               rowid,
               'H'
        from   WF_ITEM_ACTIVITY_STATUSES_H
        where  item_type = p_item_type
        and    item_key  = p_item_key
        and    notification_id is not null;

  no_lock exception;
  pragma EXCEPTION_INIT(no_lock, -54);

  -- ora-2292 = integrity constraint (%s.%s) violated - child record found
  in_other_status_table exception;
  pragma EXCEPTION_INIT(in_other_status_table, -02292);

  -- CTILLEY 2755885
  -- ora-1436 CONNECT BY loop in user data - parent_item_type = item_type
  connect_by_loop exception;
  pragma EXCEPTION_INIT(connect_by_loop,-1436);

  -- <rwunderl:3751558>
  invalid_component exception;
  pragma EXCEPTION_INIT(invalid_component, -302);
  
  invalid_identifier exception;
  pragma EXCEPTION_INIT(invalid_identifier, -201);
  -- </rwunderl:3751558>
begin
 

  -- CTILLEY bug2755885
  xrowid := '0';

  -- Disallow future date in enddate
  -- Avoid something being purged before its defined persistence period.
  if (enddate > sysdate) then
    Wf_Core.Token('SYSDATE', to_char(sysdate));
    Wf_Core.Raise('WF_FUTURE_END_DATE');
  end if;

  if ((itemkey is not null and (instr(itemkey,'%')>0 or itemtype is null))
      or (instr(itemtype,'%')>0)) then
        wf_core.raise('WFSQL_ARGS');
  end if;

  --for all items that are valid to purge

  -- outer loop for purging items.

  <<outer_purge>>
  loop
  
    if (force) then   -- CTILLEY bug 2642057
      open item4purge;
    -- JWSMITH bug 2070056
    -- CTILLEY bug 3228475 - no longer supporting partial values for itemtype
    -- and itemkey.  
    elsif (itemtype is not null and itemkey is null) then
        open item2purge;
    elsif (itemtype is not null) then
        open item3purge;
    else
        open item5purge;
    end if;
    
    -- Inner loop
    <<item_purge_loop>>
    loop

       -- CTILLEY bug 2642057
       if (force) then
          fetch item4purge into c_item_type, c_item_key;
           if (item4purge%notfound) then
              exit outer_purge;
           end if;
       -- JWSMITH bug 2070056
       elsif (itemtype is not null and itemkey is null) then
          fetch item2purge into xrowid, c_item_type, c_item_key;
           if (item2purge%notfound) then
              exit outer_purge;
           end if;
       -- CTILLEY bug 3228475     
       elsif (itemtype is not null) then
          fetch item3purge into c_item_type, c_item_key;
           if (item3purge%notfound) then
   	           exit outer_purge;
           end if;
       else
          fetch item5purge into xrowid, c_item_type, c_item_key;
           if (item5purge%notfound) then
              exit outer_purge;
           end if;
       end if;

    -- Bug 1895332 
    -- Delete the detail work item only if the master work item has 
    -- completed 
 
    keep := 0;
  
    -- CTILLEY Bug 2642057 - adding FORCE mode parameter to resolve issues
    -- in OM where they have committed child records that need to be deleted
    -- although the parent flow has not completed.

    if (NOT force) then

/*   CTILLEY 2755885 use new hierarchial query to ensure no parent
     or child records exist.
    -- Check if any parent that is still active exists for this
    -- itemtype and itemkey    
    SELECT     count(1) 
    INTO       keep 
    FROM       WF_ITEMS WIC, WF_ITEMS WIP 
    WHERE      WIC.ITEM_TYPE   = c_item_type 
    AND        WIC.ITEM_KEY    = c_item_key 
    AND        WIC.PARENT_ITEM_TYPE is not null
    AND        WIP.ITEM_TYPE   = WIC.PARENT_ITEM_TYPE 
    AND        WIP.ITEM_KEY    = WIC.PARENT_ITEM_KEY 
    AND        WIP.END_DATE is null ;

*/
    -- CTILLEY bug 2755885 - change the check from only checking the parent
    -- to checking for any open record in the heirarchy (child, parent, etc)

      begin

	SELECT  1
	INTO    keep
	FROM    SYS.DUAL 
	WHERE EXISTS 
	    (SELECT null
	     FROM WF_ITEMS WI
	     WHERE END_DATE IS NULL
	     START WITH WI.ITEM_TYPE = c_item_type
	     AND WI.ITEM_KEY = c_item_key
	     CONNECT BY PRIOR WI.ITEM_TYPE = WI.PARENT_ITEM_TYPE
	     AND PRIOR WI.ITEM_KEY = WI.PARENT_ITEM_KEY);

      exception 
	 when no_data_found then
	   begin
	     SELECT  1
	     INTO    keep
	     FROM    SYS.DUAL
	     WHERE EXISTS
		(SELECT null  /* no parent flow */
		 FROM WF_ITEMS WI
		 WHERE END_DATE IS NULL
		 START WITH WI.ITEM_TYPE = c_item_type
		 AND WI.ITEM_KEY = c_item_key
		 CONNECT BY PRIOR WI.PARENT_ITEM_TYPE = WI.ITEM_TYPE
		 AND PRIOR WI.PARENT_ITEM_KEY = WI.ITEM_KEY);
	    exception 
	       when no_data_found then
		  null;
	       when connect_by_loop then
		  null;
	    end;
	  when connect_by_loop then
	      null;
      end;

    end if;

    --If active parent exists for the item then keep is 1.
    if (keep = 0) then                -- Bug 1895332 
      begin
        --set row lock on item
        select   item_key
        into     dummy
        from     wf_items
        where    item_type = c_item_type
        and      item_key  = c_item_key
        for update nowait;
        --if lock is not obtainable then proceed to next one

        begin
         --retrieve all status info, including notification_id
 
         for nid in nid_loop(c_item_type, c_item_key) loop

            -- now delete each notification

            /** Clear reference in WIAS/_H first to avoid foreign key
                problems.
                Use the tab created in the cursor to find out which table to
                access for the select rowid
            **/
            if nid.tab = 'S' then
               update WF_ITEM_ACTIVITY_STATUSES
               set    notification_id = null
               where  rowid = nid.rowid;
            elsif nid.tab = 'H' then
               update WF_ITEM_ACTIVITY_STATUSES_H
               set    notification_id = null
               where  rowid = nid.rowid;
            end if;

            delete from WF_NOTIFICATION_ATTRIBUTES WNA
            where WNA.NOTIFICATION_ID in
            (select WN.NOTIFICATION_ID
            from WF_NOTIFICATIONS WN
            where WN.GROUP_ID = nid.notification_id);

            --<rwunderl:3751558>
            -- Delete the signatures related to this notification
            begin
              execute IMMEDIATE 'begin Wf_Digital_Security_Private.Purge_Signature_By_Obj_Id(''WF_NTF'', :1); end;' 
                        using to_char(nid.notification_id);
            exception
              when invalid_identifier then
                null;
                
              when invalid_component then
                null;
            end;
            --</rwunderl:3751558>

            -- Delete notification comments
            DELETE FROM wf_comments wc
            WHERE wc.notification_id IN
            (SELECT wn.notification_id
             FROM   wf_notifications wn
             WHERE  wn.group_id = nid.notification_id);

            begin
              delete from WF_NOTIFICATIONS WN
              where WN.GROUP_ID = nid.notification_id;
            exception when in_other_status_table then
              -- happens when nid is also in history table
              -- we will delete this later in the loop
              null;
            end;
         end loop;

         --delete all status history.
         delete from WF_ITEM_ACTIVITY_STATUSES_H
           where ITEM_TYPE = c_item_type
           and   ITEM_KEY  = c_item_key;

         --delete all statuses.
         delete from WF_ITEM_ACTIVITY_STATUSES
           where ITEM_TYPE = c_item_type
           and   ITEM_KEY  = c_item_key;

         --delete item attributes
         delete from  WF_ITEM_ATTRIBUTE_VALUES
           where ITEM_TYPE = c_item_type
           and   ITEM_KEY  = c_item_key;

         --finally delete the item itself.
         delete from  WF_ITEMS
           where ITEM_TYPE = c_item_type
           and   ITEM_KEY  = c_item_key;


      exception
          when others then
          rollback;
      end;
     exception
       -- another purge may be locking it
       when no_lock  then null;
       -- or it could already be deleted
       when no_data_found  then
       null;
     end;

    end if;  -- Bug 1895332  

    -- CTILLEY bug 2642057
    if (force) then
       exit item_purge_loop when
          ( docommit and item4purge%rowcount = wf_purge.commit_frequency);
    -- JWSMITH bug 2070056
    elsif (itemtype is not null and itemkey is null) then
       exit item_purge_loop when
         ( docommit and item2purge%rowcount = wf_purge.commit_frequency);    
    elsif (itemtype is not null) then
       exit item_purge_loop when
         ( docommit and item3purge%rowcount = wf_purge.commit_frequency);
    else
       exit item_purge_loop when
         ( docommit and item5purge%rowcount = wf_purge.commit_frequency);
    end if;

    end loop item_purge_loop; -- item2purge or item3purge loop

    if (item2purge%ISOPEN) then
       close item2purge;
    end if;

    if (item3purge%ISOPEN) then
       close item3purge;
    end if;

    if (item4purge%ISOPEN) then
       close item4purge;
    end if;
    
    -- CTILLEY bug 3228475
    if (item5purge%ISOPEN) then
       close item5purge;
    end if;


    if ( docommit ) then
        commit;
        fnd_concurrent.set_preferred_rbs;
    end if;
    
  end loop outer_purge;

  if ( docommit ) then
    commit;
    fnd_concurrent.set_preferred_rbs;
  end if;

  if (item2purge%ISOPEN) then
    close item2purge;
  end if;

  if (item3purge%ISOPEN) then
    close item3purge;
  end if;

  if (item4purge%ISOPEN) then
       close item4purge;
  end if;
  
  -- CTILLEY bug 3228475
  if (item5purge%ISOPEN) then
         close item5purge;
  end if;


  -- Clear engine runtime cache for convenience
  Wf_Item.ClearCache;

exception
  when others then
    if (item2purge%ISOPEN) then
      close item2purge;
    end if;

    if (item3purge%ISOPEN) then
      close item3purge;
    end if;

    if (item4purge%ISOPEN) then
       close item4purge;
    end if;
  
    -- CTILLEY bug 3228475
    if (item5purge%ISOPEN) then
           close item5purge;
    end if;


    Wf_Core.Context('Wf_Purge', 'Items', itemtype, itemkey, to_char(enddate));
    raise;
end Items;

--
-- procedure Activities
--   Delete old activity versions with end_time before argument,
--   and that are not referenced by an existing item.
-- IN:
--   itemtype - Item type to delete, or null for all itemtypes
--   name - Activity to delete, or null for all activities
--   enddate - Date to obsolete to
-- NOTE:
--   It is recommended to purge Items before purging Activities to avoid
--   obsolete item references preventing obsolete activities from being
--   deleted.
--
procedure Activities(
  itemtype in varchar2,
  name in varchar2,
  enddate in date)
is
  type typetab is table of varchar2(8) index by binary_integer;
  type nametab is table of varchar2(30) index by binary_integer;

  -- Select all activities of this itemtype
  

  cursor actcurs1 is
    select distinct WA.ITEM_TYPE, WA.NAME
    from WF_ACTIVITIES WA
    where WA.ITEM_TYPE = itemtype;
    
  cursor actcurs2 is
    select distinct WA.ITEM_TYPE, WA.NAME
    from WF_ACTIVITIES WA
    where WA.ITEM_TYPE = itemtype
    and WA.NAME = name;    
     
  cursor actcurs3 is
    select distinct WA.ITEM_TYPE, WA.NAME
    from WF_ACTIVITIES WA;
    
    
  /* JWSMITH BUG 2208127 - took out decode statment as this was   */
  /* preventing stats from being used and causing performance */ 
  /* issues.  Instead have two cursors and if logic to decide     */
  /* between them.  See bug for more details.                     */
  cursor parentcurs1(acttype in varchar2, actname in varchar2) is
    select WPA.PROCESS_ITEM_TYPE, WPA.PROCESS_NAME
    from WF_PROCESS_ACTIVITIES WPA
    where WPA.ACTIVITY_ITEM_TYPE = acttype
    and WPA.ACTIVITY_NAME = actname
    union
    select WA.ITEM_TYPE PROCESS_ITEM_TYPE, WA.NAME PROCESS_NAME
    from WF_ACTIVITIES WA
    where WA.ERROR_ITEM_TYPE = acttype
    and WA.ERROR_PROCESS IS NOT NULL;
 
  -- Select processes using an activity in any version,
  -- or referencing this activity as an error process
  cursor parentcurs2(acttype in varchar2, actname in varchar2) is
    select WPA.PROCESS_ITEM_TYPE, WPA.PROCESS_NAME
    from WF_PROCESS_ACTIVITIES WPA
    where WPA.ACTIVITY_ITEM_TYPE = acttype
    and WPA.ACTIVITY_NAME = actname
    union
    select WA.ITEM_TYPE PROCESS_ITEM_TYPE, WA.NAME PROCESS_NAME
    from WF_ACTIVITIES WA
    where WA.ERROR_ITEM_TYPE = acttype
    and WA.ERROR_PROCESS = actname;

parent parentcurs1%rowtype;
parent1 parentcurs2%rowtype;

  -- Select all versions of an activity before given enddate
  cursor vercurs(acttype in varchar2, actname in varchar2) is
    select WA.BEGIN_DATE, WA.END_DATE, WA.VERSION
    from WF_ACTIVITIES WA
    where WA.ITEM_TYPE = acttype
    and WA.NAME = actname
    and exists
      (select null
      from WF_ITEM_TYPES WIT
      where WA.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
      and WA.ITEM_TYPE = WIT.NAME
      and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type);

  --Use itemcurs1 if the rootname is 'ROOT'
  cursor itemcurs1(begdate in date, enddate in date,
                   roottype in varchar2, rootname in varchar2) is
    select WI.ITEM_TYPE, WI.ITEM_KEY, WI.ROOT_ACTIVITY, WI.BEGIN_DATE
    from WF_ITEMS WI
    where WI.BEGIN_DATE between begdate and  nvl(enddate, WI.BEGIN_DATE)
    and WI.ITEM_TYPE     = roottype;

  --Use itemcurs2 for other rootnames
  cursor itemcurs2(begdate in date, enddate in date,
                  roottype in varchar2, rootname in varchar2) is
    select WI.ITEM_TYPE, WI.ITEM_KEY, WI.ROOT_ACTIVITY, WI.BEGIN_DATE
    from WF_ITEMS WI
    where WI.BEGIN_DATE between begdate and  nvl(enddate, WI.BEGIN_DATE)
    and WI.ITEM_TYPE     = roottype
    and WI.ROOT_ACTIVITY = rootname ;

   item itemcurs1%rowtype;
   item1 itemcurs2%rowtype;

  -- List of processes/activities yet to be checked
  checkcur  pls_integer;
  checkmax  pls_integer;
  checktype typetab;
  checkname nametab;

  -- List of processes already checked
  foundmax  pls_integer;
  foundtype typetab;
  foundname nametab;

  -- List of possible root processes found
  rootmax  pls_integer;
  roottype typetab;
  rootname nametab;

  found boolean;

  rootid pls_integer;
  refflag boolean;

  numvers pls_integer;
  
  c_item_type varchar2(8);
  c_name varchar2(30);

begin
 

  -- Disallow future date in enddate
  -- Avoid something being purged before its defined persistence period.
  if (enddate > sysdate) then
    Wf_Core.Token('SYSDATE', to_char(sysdate));
    Wf_Core.Raise('WF_FUTURE_END_DATE');
  end if;

   -- Bug 3228475 Disallow partial values for itemtype and activity name
   if ((itemtype is not null and (instr(name,'%')>0 or itemtype is null))
       or (instr(itemtype,'%')>0)) then
      Wf_Core.Raise('WFSQL_ARGS');
   end if;
<<outer_actloop>>
loop 
  if (itemtype is not null and name is null) then  
        open actcurs1;
  elsif (itemtype is not null) then
        open actcurs2;
  else
        open actcurs3;
  end if;

   <<actloop>>
   loop

    if (itemtype is not null and name is null) then
       fetch actcurs1 into  c_item_type, c_name;
         if (actcurs1%notfound) then
             exit outer_actloop;
         end if; 
    elsif (itemtype is not null) then
          fetch actcurs2 into  c_item_type, c_name;
  	  if (actcurs2%notfound) then
             exit outer_actloop;
          end if;
    else
          fetch actcurs3 into  c_item_type, c_name;
  	  if (actcurs3%notfound) then
             exit outer_actloop;
          end if;
    end if;
  

    -- Build table of all possible root processes referencing this activity.
    -- Start with only this activity on the check list,
    -- and the found and root lists empty.
    checkcur := 1;
    checkmax := 1;
    checktype(1) := c_item_type;
    checkname(1) := c_name;
    foundmax := 0;
    rootmax := 0;

    -- While processes left to check...
    <<checkloop>>
    while (checkcur <= checkmax) loop
      -- JWSMITH BUG 2208127, Determine which cursor to use.
      if (checkname(checkcur) = 'ROOT') then
         -- If this node is already a root, add it to the list immediately.
         rootmax := rootmax + 1;
         roottype(rootmax) := checktype(checkcur);
         rootname(rootmax) := checkname(checkcur);

         open parentcurs1(checktype(checkcur),checkname(checkcur));
      else
         open parentcurs2(checktype(checkcur),checkname(checkcur));
      end if;

      -- Get all processes referencing this activity
      <<parentloop>>
      
      -- JWSMITH BUG 2208127, Determine which cursor to use.
      loop

        if (checkname(checkcur) = 'ROOT') then
           fetch parentcurs1 into parent;
           EXIT WHEN parentcurs1%NOTFOUND;
        else
           fetch parentcurs2 into parent1;
           EXIT WHEN parentcurs2%NOTFOUND;
           parent := parent1;
        end if;
        if (parent.process_name = 'ROOT') then
           -- If we have reached a root node, add this to this list
           -- of root processes to check.
           rootmax := rootmax + 1;
           roottype(rootmax) := checktype(checkcur);
           rootname(rootmax) := checkname(checkcur);
        else
           -- Otherwise, check if we have already found this activity
           -- (Found means it has either already been checked or is
           -- currently in the check queue.)
           found := FALSE;
           <<foundloop>>
           for i in 1 .. foundmax loop
              if ((foundtype(i) = parent.process_item_type) and
                 (foundname(i) = parent.process_name)) then
                  found := TRUE;
                  exit foundloop;
              end if;
           end loop foundloop;
           -- This is the first time this process has been found.
           -- Add it to the found list and the list of further
           -- processes to check.
           if (not found) then
              -- Add to list of processes found
              foundmax := foundmax + 1;
              foundtype(foundmax) := parent.process_item_type;
              foundname(foundmax) := parent.process_name;

              -- Add parent process to list to check
              checkmax := checkmax + 1;
              checktype(checkmax) := parent.process_item_type;
              checkname(checkmax) := parent.process_name;
           end if;
        end if;  -- end not root child
      end loop parentloop;

      if (parentcurs1%ISOPEN) then
         CLOSE parentcurs1;
      elsif (parentcurs2%ISOPEN) then
         CLOSE parentcurs2;
      end if;

      checkcur := checkcur + 1;
    end loop checkloop;

    -- All versions of this activity ...
    <<verloop>>
    for ver in vercurs(c_item_type, c_name) loop
      refflag := FALSE;
      -- All root processes in reference table ...
      <<rootloop>>
      for i in 1 .. rootmax loop
        -- All items in this version using this root process
        if (rootname(i) = 'ROOT') then
         open itemcurs1(ver.begin_date, ver.end_date,roottype(i),rootname(i));
        else
         open itemcurs2(ver.begin_date, ver.end_date,roottype(i),rootname(i));
        end if;

        /** 
         ** Use the itemcur1/itemcur2 depending on rootname
         ** ROOT or not 
         for item in itemcurs(ver.begin_date, ver.end_date,
            roottype(i), rootname(i)) loop
         **/
         <<itemloop>>

         loop

         if (rootname(i)= 'ROOT') then
           fetch itemcurs1 into item;
           EXIT WHEN itemcurs1%NOTFOUND;
         else
           fetch itemcurs2 into item1;
           EXIT WHEN itemcurs2%NOTFOUND;
           item := item1;
         end if;
 
        begin
          -- Search tree for a reference
          rootid := Wf_Process_Activity.RootInstanceId(item.item_type,
                        item.item_key, item.root_activity);
          if (Wf_Process_Activity.IsChild(rootid, c_item_type,
                  c_name, item.begin_date)) then
            refflag := TRUE;
            exit rootloop;
          end if;
          exception
            when others then
              -- If any errors occur while searching the item, then the
              -- item process has been somehow corrupted.
              -- To be on the safe side, assume it might have a reference
              -- and do not purge this act/version.
              refflag := TRUE;
              exit rootloop;
          end;
        end loop itemloop;
        if (itemcurs1%ISOPEN) then
          CLOSE itemcurs1;
        elsif (itemcurs2%ISOPEN) then
          CLOSE itemcurs2;
        end if;
      end loop rootloop;

      if (itemcurs1%ISOPEN) then
        CLOSE itemcurs1;
      elsif (itemcurs2%ISOPEN) then
        CLOSE itemcurs2;
      end if;

      if (not refflag) then
        -- Purge the activity if no reference found

        -- Delete any process activities and
        -- their attribute values and transitions
        delete from WF_ACTIVITY_ATTR_VALUES WAAV
        where WAAV.PROCESS_ACTIVITY_ID in
          (select WPA.INSTANCE_ID
          from WF_PROCESS_ACTIVITIES WPA
          where WPA.PROCESS_NAME = c_name
          and WPA.PROCESS_ITEM_TYPE = c_item_type
          and WPA.PROCESS_VERSION = ver.version);

        delete from WF_ACTIVITY_TRANSITIONS WAT
        where WAT.TO_PROCESS_ACTIVITY in
          (select WPA.INSTANCE_ID
          from WF_PROCESS_ACTIVITIES WPA
          where WPA.PROCESS_NAME = c_name
          and WPA.PROCESS_ITEM_TYPE = c_item_type
          and WPA.PROCESS_VERSION = ver.version);

        delete from WF_ACTIVITY_TRANSITIONS WAT
        where WAT.FROM_PROCESS_ACTIVITY in
          (select WPA.INSTANCE_ID
          from WF_PROCESS_ACTIVITIES WPA
          where WPA.PROCESS_NAME = c_name
          and WPA.PROCESS_ITEM_TYPE = c_item_type
          and WPA.PROCESS_VERSION = ver.version);

        delete from WF_PROCESS_ACTIVITIES WPA
        where WPA.PROCESS_NAME = c_name
        and WPA.PROCESS_ITEM_TYPE = c_item_type
        and WPA.PROCESS_VERSION = ver.version;

        -- Delete activity attributes and _tl
        delete from WF_ACTIVITY_ATTRIBUTES_TL WAAT
        where WAAT.ACTIVITY_NAME = c_name
        and WAAT.ACTIVITY_ITEM_TYPE = c_item_type
        and WAAT.ACTIVITY_VERSION = ver.version;

        delete from WF_ACTIVITY_ATTRIBUTES WAA
        where WAA.ACTIVITY_NAME = c_name
        and WAA.ACTIVITY_ITEM_TYPE = c_item_type
        and WAA.ACTIVITY_VERSION = ver.version;

        -- Delete from activities table and _tl table.
        delete from WF_ACTIVITIES_TL WAT
        where WAT.NAME = c_name
        and WAT.ITEM_TYPE = c_item_type
        and WAT.VERSION = ver.version;

        delete from WF_ACTIVITIES WA
        where WA.NAME = c_name
        and WA.ITEM_TYPE = c_item_type
        and WA.VERSION = ver.version;
      end if;

    end loop verloop;

    -- If no versions of activity are left, clear any references to
    -- this activity in ROOT folders
    select count(1)
    into numvers
    from WF_ACTIVITIES WA
    where WA.NAME = c_name
    and WA.ITEM_TYPE = c_item_type;

    if (numvers = 0) then
      delete from WF_PROCESS_ACTIVITIES WPA
      where WPA.PROCESS_ITEM_TYPE = c_item_type
      and WPA.PROCESS_NAME = 'ROOT'
      and WPA.ACTIVITY_ITEM_TYPE = c_item_type
      and WPA.ACTIVITY_NAME = c_name;
    end if;

  end loop actloop;
  
      
  if (actcurs1%ISOPEN) then
      close actcurs1;
  end if;
   
  if (actcurs2%ISOPEN) then
      close actcurs2;
  end if;
  
  if (actcurs3%ISOPEN) then
      close actcurs3;
  end if;
  
  end loop outer_actloop;
  
  if (actcurs1%ISOPEN) then
      close actcurs1;
  end if;
   
  if (actcurs2%ISOPEN) then
      close actcurs2;
  end if;
  
  if (actcurs3%ISOPEN) then
      close actcurs3;
  end if;

  -- Clear engine runtime cache for convenience
  Wf_Activity.ClearCache;

exception
  when others then
  if (actcurs1%ISOPEN) then
      close actcurs1;
  end if;
   
  if (actcurs2%ISOPEN) then
      close actcurs2;
  end if;
  
  if (actcurs3%ISOPEN) then
      close actcurs3;
  end if;  
    Wf_Core.Context('Wf_Purge', 'Activities', itemtype, to_char(enddate));
    raise;
end Activities;

--
-- procedure Notifications
--   Delete old notifications with end_time before argument,
--   and that are not referenced by an existing item.
-- IN:
--   itemtype - Item type to delete, or null for all itemtypes
--   enddate - Date to obsolete to
--   docommit- Do not commit if set to false
-- NOTE:
--   It is recommended to purge Items before purging Notifications to avoid
--   obsolete item references preventing obsolete notifications from being
--   deleted.
--
procedure Notifications(
  itemtype in varchar2,
  enddate in date,
  docommit in boolean)
is
  c_notification_id number;

  -- Cursor to delete all enddated notifications. If the design info for the item
  -- is available check for the persistence, if not just delete
  cursor c1 is
    select /*+ parallel(WN) */ WN.NOTIFICATION_ID
    from WF_NOTIFICATIONS WN
    where WN.MESSAGE_TYPE = itemtype
    and not exists
      (select NULL
      from WF_ITEM_ACTIVITY_STATUSES WIAS
      where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
    and not exists
      (select NULL
      from WF_ITEM_ACTIVITY_STATUSES_H WIAS
      where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
    and (
     exists(
      select null
      from WF_ITEM_TYPES WIT
      where WN.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0) <= enddate
      and WN.MESSAGE_TYPE = WIT.NAME
      and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
     or not exists(
      select null
      from   WF_ITEM_TYPES WIT
      where  WN.MESSAGE_TYPE = WIT.NAME));

   cursor c2 is
    select /*+ parallel(WN) */ WN.NOTIFICATION_ID
    from WF_NOTIFICATIONS WN
    where not exists
      (select NULL
      from WF_ITEM_ACTIVITY_STATUSES WIAS
      where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
    and not exists
      (select NULL
      from WF_ITEM_ACTIVITY_STATUSES_H WIAS
      where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
    and (
     exists(
      select null
      from WF_ITEM_TYPES WIT
      where WN.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0) <= enddate
      and WN.MESSAGE_TYPE = WIT.NAME
      and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
     or not exists(
      select null
      from   WF_ITEM_TYPES WIT
      where  WN.MESSAGE_TYPE = WIT.NAME));

  -- <rwunderl:3751558>
  invalid_component exception;
  pragma EXCEPTION_INIT(invalid_component, -302);
  
  invalid_identifier exception;
  pragma EXCEPTION_INIT(invalid_identifier, -201);
  -- </rwunderl:3751558>
begin

  -- Disallow future date in enddate
  -- Avoid something being purged before its defined persistence period.
  if (enddate > sysdate) then
    Wf_Core.Token('SYSDATE', to_char(sysdate));
    Wf_Core.Raise('WF_FUTURE_END_DATE');
  end if;

  -- Bug 3228475 Disallow partial values being passed for itemtype
  if (instr(itemtype,'%')>0) then
      Wf_Core.Raise('WFSQL_ARGS');
  end if;

  -- End date all the orphan notifications. This end dates all the notifications
  -- generated before the enddate parameter value.
  loop
    UPDATE /*+ parallel (WN) */  wf_notifications wn
    SET end_date = nvl(begin_date, to_date('2002/08/01','YYYY/MM/DD')) + 1
    WHERE NOT EXISTS
      (SELECT NULL
       FROM  wf_item_activity_statuses wias
       WHERE  wias.notification_id = wn.group_id)
    AND NOT EXISTS
      (SELECT NULL
       FROM  wf_item_activity_statuses_h wiash
       WHERE  wiash.notification_id = wn.group_id)
    AND wn.end_date is null
    AND wn.begin_date <= enddate
    AND rownum < Wf_Purge.Commit_Frequency;

    commit;

    exit when (sql%NOTFOUND);
  end loop;

  <<outer_ntf_loop>>
  loop
    
    if (itemtype is not null) then
      open c1;
    else
      open c2;
    end if;

    <<inner_ntf_loop>>
    loop

     if (itemtype is not null) then
       fetch c1 into c_notification_id;
         if (c1%notfound) then
           exit outer_ntf_loop;
         end if;
     else
       fetch c2 into c_notification_id;
         if (c2%notfound) then
             exit outer_ntf_loop;
         end if;
      end if;

     -- Delete notification attributes
     delete from WF_NOTIFICATION_ATTRIBUTES WNA
     where WNA.NOTIFICATION_ID = c_notification_id;

     --<rwunderl:3751558>
     -- Delete the signatures related to this notification
     begin
       execute IMMEDIATE 'begin Wf_Digital_Security_Private.Purge_Signature_By_Obj_Id(''WF_NTF'', :1); end;' 
                 using to_char(c_notification_id);
     exception
       when invalid_identifier then
         null;
         
       when invalid_component then
         null;
     end;
     --</rwunderl:3751558>


     -- Delete Notification comments
     DELETE FROM wf_comments wc
     WHERE wc.notification_id = c_notification_id;

     -- Delete notifications
     delete from WF_NOTIFICATIONS WN
     where WN.NOTIFICATION_ID = c_notification_id;

     if (itemtype is not null) then
         exit inner_ntf_loop when
         ( docommit and (c1%rowcount = wf_purge.commit_frequency));
     else
         exit inner_ntf_loop when
         ( docommit and (c2%rowcount = wf_purge.commit_frequency));
     end if;

    end loop inner_ntf_loop;

    if (c1%ISOPEN) then
       close c1;
    elsif (c2%ISOPEN) then
       close c2;
    end if;

    -- Commit Decision
    if (docommit) then
      commit;
      Fnd_Concurrent.Set_Preferred_RBS;
    end if;
  end loop outer_ntf_loop;

  if (c1%ISOPEN) then
     close c1;
  elsif (c2%ISOPEN) then
     close c2;
  end if;

  -- Commit Decision
  if (docommit) then
    commit;
    Fnd_Concurrent.Set_Preferred_RBS;
  end if;

  -- Purge AdHoc Users/Roles/User_Roles
  Wf_Purge.AdHocDirectory(enddate);

exception
  when others then
    if (c1%ISOPEN) then
       close c1;
    elsif (c2%ISOPEN) then
       close c2;
    end if;
 
    Wf_Core.Context('Wf_Purge', 'Notifications', itemtype, to_char(enddate));
    raise;
end Notifications;

--
-- procedure Item_Notifications
--   Delete notifications sent by a particular item with end_time
--   before argument.
-- IN:
--   itemtype - Item type to delete, or null for all itemtypes
--   itemkey - Item key to delete, or null for all itemkeys
--   enddate - Date to obsolete to
--   docommit- Do not commit if set to false
--
procedure Item_Notifications(
  itemtype in varchar2,
  itemkey in varchar2,
  enddate in date,
  docommit in boolean)
is
 
  c_notification_id number;

  cursor c1 is
    select WIAS.NOTIFICATION_ID
    from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_ITEMS WI
    where WIAS.ITEM_TYPE = itemtype
    and WIAS.NOTIFICATION_ID is not null
    and WI.ITEM_TYPE = WIAS.ITEM_TYPE
    and WI.ITEM_KEY = WIAS.ITEM_KEY
    and WI.END_DATE is not null
    and exists
      (select null
      from WF_ITEM_TYPES WIT
      where WIAS.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
      and WIAS.ITEM_TYPE = WIT.NAME
      and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
    union all
    select WIAS.NOTIFICATION_ID
    from WF_ITEM_ACTIVITY_STATUSES_H WIAS, WF_ITEMS WI
    where WIAS.ITEM_TYPE = itemtype
    and WIAS.NOTIFICATION_ID is not null
    and WI.ITEM_TYPE = WIAS.ITEM_TYPE
    and WI.ITEM_KEY = WIAS.ITEM_KEY
    and WI.END_DATE is not null
    and exists
      (select null
      from WF_ITEM_TYPES WIT
      where WIAS.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
      and WIAS.ITEM_TYPE = WIT.NAME
      and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type);
      
  cursor c2 is
    select WIAS.NOTIFICATION_ID
    from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_ITEMS WI
    where WIAS.ITEM_TYPE = itemtype
    and WIAS.ITEM_KEY = itemkey
    and WIAS.NOTIFICATION_ID is not null
    and WI.ITEM_TYPE = WIAS.ITEM_TYPE
    and WI.ITEM_KEY = WIAS.ITEM_KEY
    and WI.END_DATE is not null
    and exists
      (select null
      from WF_ITEM_TYPES WIT
      where WIAS.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
      and WIAS.ITEM_TYPE = WIT.NAME
      and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
    union all
    select WIAS.NOTIFICATION_ID
    from WF_ITEM_ACTIVITY_STATUSES_H WIAS, WF_ITEMS WI
    where WIAS.ITEM_TYPE = itemtype
    and  WIAS.ITEM_KEY = itemkey
    and WIAS.NOTIFICATION_ID is not null
    and WI.ITEM_TYPE = WIAS.ITEM_TYPE
    and WI.ITEM_KEY = WIAS.ITEM_KEY
    and WI.END_DATE is not null
    and exists
      (select null
      from WF_ITEM_TYPES WIT
      where WIAS.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
      and WIAS.ITEM_TYPE = WIT.NAME
      and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type);
      
  cursor c3 is
    select WIAS.NOTIFICATION_ID
    from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_ITEMS WI
    where WIAS.NOTIFICATION_ID is not null
    and WI.ITEM_TYPE = WIAS.ITEM_TYPE
    and WI.ITEM_KEY = WIAS.ITEM_KEY
    and WI.END_DATE is not null
    and exists
      (select null
      from WF_ITEM_TYPES WIT
      where WIAS.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
      and WIAS.ITEM_TYPE = WIT.NAME
      and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
    union all
    select WIAS.NOTIFICATION_ID
    from WF_ITEM_ACTIVITY_STATUSES_H WIAS, WF_ITEMS WI
    where WIAS.NOTIFICATION_ID is not null
    and WI.ITEM_TYPE = WIAS.ITEM_TYPE
    and WI.ITEM_KEY = WIAS.ITEM_KEY
    and WI.END_DATE is not null
    and exists
      (select null
      from WF_ITEM_TYPES WIT
      where WIAS.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
      and WIAS.ITEM_TYPE = WIT.NAME
      and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type);      

begin


  -- Disallow future date in enddate
  -- Avoid something being purged before its defined persistence period.
  if (enddate > sysdate) then
    Wf_Core.Token('SYSDATE', to_char(sysdate));
    Wf_Core.Raise('WF_FUTURE_END_DATE');
  end if;

  -- Bug 3228475 Disallow partial values being passed for itemtype and itemkey
  if ((itemkey is not null and (instr(itemkey,'%')>0 or itemtype is null))
       or (instr(itemtype,'%')>0)) then
      Wf_Core.Raise('WFSQL_ARGS');
  end if;

  <<outer_loop>>
  loop
   if (itemtype is not null and itemkey is null) then
     open c1;
   elsif (itemkey is not null) then
     open c2;
   else
     open c3;
   end if;

    <<inner_loop>>
    loop

    if (itemtype is not null and itemkey is null) then
      fetch c1 into c_notification_id;
        if (c1%notfound) then
          exit outer_loop;
        end if;
    elsif (itemtype is not null) then
      fetch c2 into c_notification_id;
        if (c2%notfound) then
          exit outer_loop;
        end if;
    else
       fetch c3 into c_notification_id;
         if (c3%notfound) then
           exit outer_loop;
        end if;
     end if;

    -- Clear reference in WIAS/_H first to avoid foreign key problems
   if (itemtype is not null and itemkey is null) then
    
      update WF_ITEM_ACTIVITY_STATUSES WIAS set
        WIAS.NOTIFICATION_ID = null
      where WIAS.ITEM_TYPE = itemtype
      and WIAS.NOTIFICATION_ID = c_notification_id;
    
  
      update WF_ITEM_ACTIVITY_STATUSES_H WIAS set
        WIAS.NOTIFICATION_ID = null
      where WIAS.ITEM_TYPE = itemtype
      and WIAS.NOTIFICATION_ID = c_notification_id;
     
   elsif (itemtype is not null) then
    
      update WF_ITEM_ACTIVITY_STATUSES WIAS set
        WIAS.NOTIFICATION_ID = null
      where WIAS.ITEM_TYPE = itemtype
      and WIAS.ITEM_KEY = itemkey
      and WIAS.NOTIFICATION_ID = c_notification_id;
    
  
      update WF_ITEM_ACTIVITY_STATUSES_H WIAS set
        WIAS.NOTIFICATION_ID = null
      where WIAS.ITEM_TYPE = itemtype
      and WIAS.ITEM_KEY = itemkey
    and WIAS.NOTIFICATION_ID = c_notification_id; 
    
   else
    
      update WF_ITEM_ACTIVITY_STATUSES WIAS set
        WIAS.NOTIFICATION_ID = null
      where WIAS.NOTIFICATION_ID = c_notification_id;
  
      update WF_ITEM_ACTIVITY_STATUSES_H WIAS set
        WIAS.NOTIFICATION_ID = null
      where WIAS.NOTIFICATION_ID = c_notification_id;
    
   end if;
    
   
   -- Delete notification attributes
    delete from WF_NOTIFICATION_ATTRIBUTES WNA
    where WNA.NOTIFICATION_ID in
      (select WN.NOTIFICATION_ID
      from WF_NOTIFICATIONS WN
      where WN.GROUP_ID = c_notification_id);

    -- Delete notifications
    delete from WF_NOTIFICATIONS WN
    where WN.GROUP_ID = c_notification_id;

    if (itemtype is not null and itemkey is null) then
       exit inner_loop when
         ( docommit and (c1%rowcount = wf_purge.commit_frequency));
    elsif (itemkey is not null) then
       exit inner_loop when
         ( docommit and (c2%rowcount = wf_purge.commit_frequency));
    else
      exit inner_loop when
         ( docommit and (c3%rowcount = wf_purge.commit_frequency));
    end if;

    end loop inner_loop;

    if (c1%ISOPEN) then
      close c1;
    end if;
      
    if (c2%ISOPEN) then
      close c2;
    end if;

    if (c3%ISOPEN) then
      close c3;
    end if;
    
    --Commit Decision
    if (docommit) then
      commit;
      Fnd_Concurrent.Set_Preferred_RBS;
    end if;

  end loop outer_loop;
  
    if (c1%ISOPEN) then
      close c1;
    end if;
      
    if (c2%ISOPEN) then
      close c2;
    end if;

    if (c3%ISOPEN) then
      close c3;
    end if;

exception
  when others then
    if (c1%ISOPEN) then
      close c1;
    end if;
      
    if (c2%ISOPEN) then
      close c2;
    end if;

    if (c3%ISOPEN) then
      close c3;
    end if;
    Wf_Core.Context('Wf_Purge', 'Item_Notifications', itemtype,
        itemkey, to_char(enddate));
    raise;
end Item_Notifications;

--
-- Total
--   Delete all obsolete runtime data with end_time before argument.
-- IN:
--   itemtype - Item type to delete, or null for all itemtypes
--   itemkey - Item key to delete, or null for all itemkeys
--   enddate - Date to obsolete to
--   docommit- Commit or no commit after each purge of entitiy
--   runtimeonly - If true only runtime /transaction data purged
--             if false both runtime and design data purged.
--
procedure Total(
  itemtype in varchar2,
  itemkey in varchar2,
  enddate in date,
  docommit in boolean,
  runtimeonly  in boolean
)
is
  -- <rwunderl:3751558>
  l_sql varchar2(2000);
  invalid_component exception;
  pragma EXCEPTION_INIT(invalid_component, -302);
  
  invalid_identifier exception;
  pragma EXCEPTION_INIT(invalid_identifier, -201);
  -- </rwunderl:3751558>

begin

  -- Disallow future date in enddate
  -- Avoid something being purged before its defined persistence period.
  if (enddate > sysdate) then
    Wf_Core.Token('SYSDATE', to_char(sysdate));
    Wf_Core.Raise('WF_FUTURE_END_DATE');
  end if;
 
  -- Bug 3228475  Disallow partial values from being passed
  if ((itemkey is not null and (instr(itemkey,'%')>0 or itemtype is null))
       or (instr(itemtype,'%')>0)) then
      Wf_Core.Raise('WFSQL_ARGS');
  end if;
 
 -- Bug 1636510
 -- Before attempting to purge items we will abort any error process
 -- if the activity that launched it is now complete.
  wf_purge.abortErrorProcess(itemtype, itemkey);

  wf_purge.items(itemtype, itemkey, enddate, docommit);
  if (docommit) then
    commit;
    Fnd_Concurrent.Set_Preferred_RBS;
  end if;

  --Purge design data only if runtime is
  --set to false
  if (not runtimeonly) then
    --also purge design data from activities
    wf_purge.activities(itemtype=>itemtype, enddate=>enddate);
    if (docommit) then
      commit;
      Fnd_Concurrent.Set_Preferred_RBS;
    end if;

    --Directory Information
    Wf_Purge.Directory(enddate);
    if (docommit) then
      commit; 
      Fnd_Concurrent.Set_Preferred_RBS;
    end if;
  end if;

  -- Clear engine runtime cache for convenience
  Wf_Item.ClearCache;

  -- Purge orphan notifications only if runtimeonly is false
  if (not runtimeonly) then
    Notifications(itemtype, enddate);
  end if;

  -- Call ECX Purge
  --<rwunderl:3751558>
  begin
    l_sql := 'begin ECX_PURGE.Purge_Items(:1, :2, :3, ';
    if (docommit) then
      l_sql := l_sql||'TRUE';
    else
      l_sql := l_sql||'FALSE';
    end if;
    
    if (runtimeonly) then
      l_sql := l_sql||', TRUE';
    else
      l_sql := l_sql||', FALSE';
    end if;
    
    l_sql := l_sql||'); end;';
    
    execute immediate l_sql using  itemtype, itemkey, to_char(enddate);
  exception
    when invalid_identifier then
      null;
      
    when invalid_component then
      null;
  end;
  --</rwunderl:3751558>
exception
  when others then
    Wf_Core.Context('Wf_Purge', 'Total', itemtype, itemkey, to_char(enddate));
    raise;
end Total;

--
-- TotalPERM
--   Delete all obsolete runtime data that is of persistence type 'PERM'
--   and with end_time before argument.
-- IN:
--   itemtype - Item type to delete, or null for all itemtypes
--   itemkey - Item key to delete, or null for all itemkeys
--   enddate - Date to obsolete to
--   docommit- Commit or no commit after each purge of entitiy
--   runtimeonly - Delete runtime data alone if set to true
--             else delete both design and runtime data
--
procedure TotalPERM(
  itemtype in varchar2,
  itemkey in varchar2,
  enddate in date,
  docommit in boolean,
  runtimeonly in boolean)
is
l_runtimeonly    boolean := FALSE;
begin
  wf_purge.persistence_type := 'PERM';

  if runtimeonly then
     l_runtimeonly := TRUE;
  end if;
  -- Call Total with new args
  Wf_Purge.Total(
    itemtype,
    itemkey,
    enddate,
    docommit,
    l_runtimeonly);

  -- Reset persistence type to the default value
  wf_purge.persistence_type := 'TEMP';

exception
  when others then
    -- Reset persistence type to the default value
    wf_purge.persistence_type := 'TEMP';
    Wf_Core.Context('Wf_Purge', 'TotalPERM', itemtype, itemkey,
                    to_char(enddate));
    raise;
end TotalPERM;


--
-- TotalConcurrent
--   Concurrent Program version of Total
-- IN:
--   errbuf - CPM error message
--   retcode - CPM return code (0 = success, 1 = warning, 2 = error)
--   itemtype - Item type to delete, or null for all itemtypes
--   itemkey - Item key to delete, or null for all itemkeys
--   age - Minimum age of data to purge (in days)
--   x_persistence_type - Persistence Type to be purged: 'TEMP' or 'PERM'
--   runtimeonly - If 'Y' then purge runtime data alone
--
procedure TotalConcurrent(
  errbuf out NOCOPY varchar2,
  retcode out NOCOPY varchar2,
  itemtype in varchar2,
  itemkey in varchar2,
  age in varchar2,
  x_persistence_type in varchar2,
  runtimeonly  in varchar2,
  x_commit_frequency in number
  )
is
  enddate date;
  errname varchar2(30);
  errmsg varchar2(2000);
  errstack varchar2(2000);

  l_runtimeonly boolean := FALSE;
begin
  -- Convert arguments from varchar2 to real type.
  enddate := sysdate - to_number(age);

  wf_purge.persistence_type := x_persistence_type;
  wf_purge.commit_frequency := x_commit_frequency;

  --If runtimeonly 'N' then purge both runtime 
  --and design data 
  if (upper(runtimeonly) = 'Y') then
     l_runtimeonly := TRUE;
  end if;

   -- Call Total with new args
   Wf_Purge.Total(
    itemtype,
    itemkey,
    enddate,
    true,
    l_runtimeonly);


  -- Return 0 for successful completion.
  errbuf := '';
  retcode := '0';
  wf_purge.persistence_type := 'TEMP';  -- reset to the default value
  wf_purge.commit_frequency := 500; -- reset to the default value

exception
  when others then
    -- Retrieve error message into errbuf
    wf_core.get_error(errname, errmsg, errstack);
    if (errmsg is not null) then
      errbuf := errmsg;
    else
      errbuf := sqlerrm;
    end if;

    -- Return 2 for error.
    retcode := '2';

    -- Reset persistence type to the default value
    wf_purge.persistence_type := 'TEMP';
end TotalConcurrent;

--
-- Directory
--   Purge all WF_LOCAL_* tables based on expiration date
-- IN:
--   end_date - Date to purge to
--
procedure Directory(
  end_date in date)
is
  --
  -- All roles that past the end_date and with no outstanding notification
  --
  -- Need to check ORIGINAL_RECIPIENT as well as the RECIPIENT_ROLE just
  -- in case a 'Delegate' happened, we won't purge the orginal owner.
  --
  cursor role_cursor is
    select local.NAME, local.ORIG_SYSTEM, local.ORIG_SYSTEM_ID,
           local.USER_FLAG
     from  WF_LOCAL_ROLES local
     where PARTITION_ID = 0
     and   ORIG_SYSTEM in ('WF_LOCAL_ROLES', 'WF_LOCAL_USERS')
     and   EXPIRATION_DATE <= end_date
     and not exists
           (select NULL
            from   WF_ROLE_HIERARCHIES
            where  SUPER_NAME = local.NAME
            or     SUB_NAME   = local.NAME)
     and not exists
           (select NULL
            from   WF_USER_ROLE_ASSIGNMENTS
            where  USER_NAME = local.NAME
            and     RELATIONSHIP_ID <> -1)
     and not exists
           (select NULL
              from WF_NOTIFICATIONS wn
             where wn.RECIPIENT_ROLE = local.NAME
                or wn.ORIGINAL_RECIPIENT = local.NAME);

  rcurs role_cursor%rowtype;
begin
  -- Disallow future date in enddate
  -- Avoid something being purged before its expiration date.
  if (end_date > sysdate) then
    Wf_Core.Token('SYSDATE', to_char(sysdate));
    Wf_Core.Raise('WF_FUTURE_END_DATE');
  end if;

  --
  -- Purge Roles
  -- Delete rows in wf_local_user_roles by role_name and wf_local_roles
  -- by name as determined
  for rcurs in role_cursor loop
    if (rcurs.user_flag = 'Y') then
      delete from WF_LOCAL_USER_ROLES local
       where local.USER_NAME = rcurs.name
         and local.USER_ORIG_SYSTEM = rcurs.orig_system
         and local.USER_ORIG_SYSTEM_ID = rcurs.orig_system_id;
    else
      delete from WF_LOCAL_USER_ROLES local
       where local.ROLE_NAME = rcurs.name
         and local.ROLE_ORIG_SYSTEM = rcurs.orig_system
         and local.ROLE_ORIG_SYSTEM_ID = rcurs.orig_system_id;
    end if;

    --Remove only the direct assignments.	 
    delete from WF_USER_ROLE_ASSIGNMENTS wura	 
    where  wura.USER_NAME = rcurs.name	 
    and    wura.ROLE_NAME = rcurs.name;

    delete from WF_LOCAL_ROLES local
     where local.NAME = rcurs.name
       and local.ORIG_SYSTEM = rcurs.orig_system
       and local.ORIG_SYSTEM_ID = rcurs.orig_system_id;
  end loop;

exception
  when others then
    if (role_cursor%isopen) then
      close role_cursor;
    end if;
    wf_core.context('WF_PURGE', 'Directory', end_date);
    raise;
end Directory;

--
-- AdHocDirectory
--   Purge all WF_LOCAL_* tables based on expiration date
-- IN:
--   end_date - Date to purge to
--
procedure AdHocDirectory(
  end_date in date)
is
begin
  Wf_Purge.Directory(end_date);
exception
  when OTHERS then
    wf_core.context('WF_PURGE', 'AdHocDirectory', end_date);
    raise;
end AdHocDirectory;

 --
 -- GetPurgeableCount
 --   Returns the count of purgeable items for a specific itemType.
 -- IN:
 --   p_itemType  in VARCHAR2
 --
 FUNCTION GetPurgeableCount (p_itemType in varchar2) return number is

 l_purgeable number := 0;
 l_purge   varchar2(100);

 cursor item2purge (xitemtype varchar2) is
    select /*+ FIRST_ROWS */ WI.ITEM_TYPE, WI.ITEM_KEY
    from   WF_ITEMS WI
    where WI.ITEM_TYPE = xitemtype
    and exists
        (select null
         from WF_ITEM_TYPES WIT
         where WI.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=sysdate
         and WI.ITEM_TYPE = WIT.NAME
         and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
    and WI.END_DATE is NOT NULL;

 connect_by_loop exception;
 pragma EXCEPTION_INIT(connect_by_loop,-1436);
BEGIN
  --Get all records for thye itemtype
  for purge_rec in item2purge (p_itemType) loop
   BEGIN

     SELECT  'VALID'
     INTO    l_purge
     FROM    SYS.DUAL
     WHERE   EXISTS  /* any active child item */
         (SELECT null
          FROM WF_ITEMS WI
          WHERE END_DATE IS NULL
          START WITH WI.ITEM_TYPE = purge_rec.item_type
          AND WI.ITEM_KEY         = purge_rec.item_key
          CONNECT BY PRIOR WI.ITEM_TYPE = WI.PARENT_ITEM_TYPE
          AND PRIOR WI.ITEM_KEY = WI.PARENT_ITEM_KEY);

   EXCEPTION
    when no_data_found then
    --If we crossed this condition also
    --we need to check that no parent flow exists
    BEGIN
     SELECT  'VALID'
     INTO    l_purge
     FROM    SYS.DUAL
     WHERE   EXISTS  /* no parent flow */
          (SELECT null
           FROM WF_ITEMS WI
           WHERE END_DATE IS NULL
           START WITH WI.ITEM_TYPE = purge_rec.item_type
           AND WI.ITEM_KEY         = purge_rec.item_key
           CONNECT BY PRIOR  WI.PARENT_ITEM_TYPE = WI.ITEM_TYPE
           AND PRIOR  WI.PARENT_ITEM_KEY = WI.ITEM_KEY );
     EXCEPTION
       when no_data_found then
         l_purgeable := l_purgeable +1;
       when connect_by_loop then
         l_purgeable := l_purgeable +1;
     END;
    WHEN CONNECT_BY_LOOP then
      l_purgeable := l_purgeable +1;
   END;
   end loop;
   return l_purgeable;
exception
  when OTHERS then
    wf_core.context('WF_PURGE', 'GetPurgeableCount', p_itemtype);
    raise;

end;


 --
 -- AbortErrorProcess
 --   Aborts the Error process for an errored activity if the activity is
 --   now COMPLETE.  
 -- IN:
 --   itemtype  in VARCHAR2
 --   itemkey   in VARCHAR2
 --
 procedure AbortErrorProcess (itemtype varchar2,
                              itemkey varchar2)
 is

  c_item_key varchar2(240);

  cursor c_error1 is
    select wi.item_key
    from wf_item_activity_statuses wias, wf_items wi
    where wi.item_type = 'WFERROR'
    and parent_item_type = itemtype
    and wi.parent_item_type = wias.item_type
    and wi.parent_item_key = wias.item_key
    and wi.parent_context = wias.process_activity
    and wias.activity_status = 'COMPLETE';

 cursor c_error2 is
    select wi.item_key
    from wf_item_activity_statuses wias, wf_items wi
    where wi.item_type = 'WFERROR'
    and parent_item_type = itemtype
    and parent_item_key = itemkey
    and wi.parent_item_type = wias.item_type
    and wi.parent_item_key = wias.item_key
    and wi.parent_context = wias.process_activity
    and wias.activity_status = 'COMPLETE';

 cursor c_error3 is
    select wi.item_key
    from wf_item_activity_statuses wias, wf_items wi
    where wi.item_type = 'WFERROR'
    and wi.parent_item_type = wias.item_type
    and wi.parent_item_key = wias.item_key
    and wi.parent_context = wias.process_activity
    and wias.activity_status = 'COMPLETE';


 begin

   if ((itemkey is not null and (instr(itemkey,'%')>0 or itemtype is null))
      or (instr(itemtype,'%')>0)) then
          Wf_Core.Raise('WFSQL_ARGS');
   end if;

   -- Outer loop
   <<outer_abort>>
   loop
    
      if (itemtype is not null and itemkey is null) then
         open c_error1;
      elsif (itemtype is not null) then
         open c_error2;
      else 
         open c_error3;
      end if;

     -- Inner loop
     <<abort_loop>>
     loop

        if (itemtype is not null and itemkey is null) then
           fetch c_error1 into  c_item_key; 
           if (c_error1%notfound) then
              exit outer_abort;
           end if;
        elsif (itemtype is not null) then
           fetch c_error2 into c_item_key; 
           if (c_error2%notfound) then
              exit outer_abort;
           end if;
        else
           fetch c_error3 into c_item_key; 
           if (c_error3%notfound) then
              exit outer_abort;
           end if;
        end if;

        -- Abort the error process since the activity is now COMPLETE
        begin

           wf_engine.abortprocess('WFERROR', c_item_key);

        exception when others then
           null;
        end;
    
     end loop abort_loop;

      if (c_error1%ISOPEN) then
         close c_error1;
      end if;
     
      if (c_error2%ISOPEN) then
         close c_error2;
      end if;
    
      if (c_error3%ISOPEN) then
         close c_error3;
      end if;
     
   end loop outer_abort;
  
      if (c_error1%ISOPEN) then
         close c_error1;
      end if;
     
      if (c_error2%ISOPEN) then
         close c_error2;
      end if;
    
      if (c_error3%ISOPEN) then
         close c_error3;
      end if;
     
  EXCEPTION WHEN OTHERS THEN
      if (c_error1%ISOPEN) then
         close c_error1;
      end if;
     
      if (c_error2%ISOPEN) then
         close c_error2;
      end if;
    
      if (c_error3%ISOPEN) then
         close c_error3;
      end if;

      Wf_Core.Context('Wf_Purge', 'AbortErrorProcess', itemtype, itemkey);
      raise;
    
 end AbortErrorProcess;



end WF_PURGE;
/
--show errors package body WF_PURGE
--select to_date('SQLERROR') from user_errors
--where type = 'PACKAGE BODY'
--and name = 'WF_PURGE'
--/
REM ================================================================
commit;
exit;