APPS.WF_PURGE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 77

     select WI.END_DATE, WI.ITEM_TYPE, WI.ITEM_KEY
     from   WF_ITEMS WI
     where  WI.ITEM_TYPE = itemtype
     and    WI.END_DATE <= enddate
     and    WI.END_DATE > xenddate
      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)
      and not exists
         (select null
          from WF_ITEMS WI2
          WHERE WI2.END_DATE IS NULL
          START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
          AND WI2.ITEM_KEY = WI.ITEM_KEY
          CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE
          AND PRIOR WI2.ITEM_KEY = WI2.PARENT_ITEM_KEY
          UNION ALL
          select null
          from WF_ITEMS WI2
          WHERE WI2.END_DATE IS NULL
          START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
          AND WI2.ITEM_KEY = WI.ITEM_KEY
          CONNECT BY PRIOR WI2.PARENT_ITEM_TYPE = WI2.ITEM_TYPE
          AND PRIOR WI2.PARENT_ITEM_KEY = WI2.ITEM_KEY)
	order by WI.END_DATE;

Line: 108

     select WI.END_DATE, WI.ITEM_TYPE, WI.ITEM_KEY
     from   WF_ITEMS WI
     where  WI.ITEM_TYPE = itemtype
      and   WI.ITEM_KEY = itemkey
      and   WI.end_date <= enddate
       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)
       and not exists
          (select null
           from WF_ITEMS WI2
           WHERE WI2.END_DATE IS NULL
           START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
           AND WI2.ITEM_KEY = WI.ITEM_KEY
           CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE
           AND PRIOR WI2.ITEM_KEY = WI2.PARENT_ITEM_KEY
           UNION ALL
           select null
           from WF_ITEMS WI2
           WHERE WI2.END_DATE IS NULL
           START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
           AND WI2.ITEM_KEY = WI.ITEM_KEY
           CONNECT BY PRIOR WI2.PARENT_ITEM_TYPE = WI2.ITEM_TYPE
           AND PRIOR WI2.PARENT_ITEM_KEY = WI2.ITEM_KEY);

Line: 139

      select WI.END_DATE, WI.ITEM_TYPE, WI.ITEM_KEY
      from  WF_ITEMS WI
      where WI.ITEM_KEY = itemkey
      and   WI.ITEM_TYPE = itemtype
      and   WI.end_date <= enddate;

Line: 150

     select /*+ first_rows index(WI,WF_ITEMS_N3) */
            WI.END_DATE, WI.ITEM_TYPE, WI.ITEM_KEY
     from   WF_ITEMS WI
     where  WI.END_DATE <= enddate
     and    WI.END_DATE > xenddate
      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)
      and not exists
         (select null
          from WF_ITEMS WI2
          WHERE WI2.END_DATE IS NULL
          START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
          AND WI2.ITEM_KEY = WI.ITEM_KEY
          CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE
          AND PRIOR WI2.ITEM_KEY = WI2.PARENT_ITEM_KEY
          UNION ALL
          select null
          from WF_ITEMS WI2
          WHERE WI2.END_DATE IS NULL
          START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
          AND WI2.ITEM_KEY = WI.ITEM_KEY
          CONNECT BY PRIOR WI2.PARENT_ITEM_TYPE = WI2.ITEM_TYPE
          AND PRIOR WI2.PARENT_ITEM_KEY = WI2.ITEM_KEY);

Line: 308

            l_itemkeyTAB.DELETE(l_itemkeyTAB.LAST);

Line: 312

            l_itemkeyTAB.DELETE(l_itemkeyTAB.LAST);

Line: 322

          select   WN.NOTIFICATION_ID
	  BULK COLLECT into l_tempListTAB
	    from   WF_ITEM_ACTIVITY_STATUSES WIAS,
	           WF_NOTIFICATIONS WN
	    where  WIAS.ITEM_TYPE = l_itemtypeTAB(j)
	    and    WIAS.ITEM_KEY  = l_itemkeyTAB(j)
	    and    WIAS.NOTIFICATION_ID = WN.GROUP_ID
            and    ((purgesigs = 1)
		    or not exists
	            (select null
                     from   WF_DIG_SIGS WDS
                     where  SIG_OBJ_TYPE = 'WF_NTF'
                     and    SIG_OBJ_ID = WN.NOTIFICATION_ID))
	   union all
	   select WN.NOTIFICATION_ID
	    from   WF_ITEM_ACTIVITY_STATUSES_H WIASH,
	           WF_NOTIFICATIONS WN
	    where  WIASH.ITEM_TYPE = l_itemtypeTAB(j)
	    and    WIASH.ITEM_KEY  = l_itemkeyTAB(j)
	    and    WIASH.NOTIFICATION_ID = WN.GROUP_ID
            and    ((purgesigs = 1)
		    or not exists
	            (select null
                     from   WF_DIG_SIGS WDS
                     where  SIG_OBJ_TYPE = 'WF_NTF'
                     and    SIG_OBJ_ID = WN.NOTIFICATION_ID));

Line: 361

            delete from WF_NOTIFICATION_ATTRIBUTES WNA
            where WNA.NOTIFICATION_ID = l_nidListTAB(i);

Line: 390

            DELETE FROM wf_comments wc
            WHERE wc.notification_id = l_nidListTAB(i);

Line: 395

              delete from WF_NOTIFICATIONS WN
              where WN.NOTIFICATION_ID = l_nidListTAB(i);

Line: 405

           l_nidListTAB.DELETE(i);

Line: 416

         delete from WF_ITEM_ACTIVITY_STATUSES_H
           where ITEM_TYPE = l_itemtypeTAB(j)
           and   ITEM_KEY  = l_itemkeyTAB(j);

Line: 422

         delete from WF_ITEM_ACTIVITY_STATUSES
           where ITEM_TYPE = l_itemtypeTAB(j)
           and   ITEM_KEY  = l_itemkeyTAB(j);

Line: 428

         delete from  WF_ITEM_ATTRIBUTE_VALUES
           where ITEM_TYPE = l_itemtypeTAB(j)
           and   ITEM_KEY  = l_itemkeyTAB(j);

Line: 441

         delete from  WF_ITEMS
           where ITEM_TYPE = l_itemtypeTAB(j)
           and   ITEM_KEY  = l_itemkeyTAB(j);

Line: 569

    select distinct WA.ITEM_TYPE, WA.NAME
    from WF_ACTIVITIES WA
    where WA.ITEM_TYPE = itemtype;

Line: 574

    select distinct WA.ITEM_TYPE, WA.NAME
    from WF_ACTIVITIES WA
    where WA.ITEM_TYPE = itemtype
    and WA.NAME = name;

Line: 580

    select distinct WA.ITEM_TYPE, WA.NAME
    from WF_ACTIVITIES WA;

Line: 589

    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;

Line: 602

    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;

Line: 617

    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);

Line: 631

    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;

Line: 639

    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 ;

Line: 874

        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);

Line: 882

        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);

Line: 890

        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);

Line: 898

        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;

Line: 904

        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;

Line: 909

        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;

Line: 915

        delete from WF_ACTIVITIES_TL WAT
        where WAT.NAME = c_name
        and WAT.ITEM_TYPE = c_item_type
        and WAT.VERSION = ver.version;

Line: 920

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

Line: 930

    select count(1)
    into numvers
    from WF_ACTIVITIES WA
    where WA.NAME = c_name
    and WA.ITEM_TYPE = c_item_type;

Line: 937

      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;

Line: 1018

    select 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))
    and(
     (purgesigs = 1)
      or not exists
      (select null
      from   WF_DIG_SIGS WDS
      where  SIG_OBJ_TYPE = 'WF_NTF'
      and    SIG_OBJ_ID = WN.NOTIFICATION_ID));

Line: 1049

    select 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))
    and(
      (purgesigs = 1)
      or not exists
      (select null
      from   WF_DIG_SIGS WDS
      where  SIG_OBJ_TYPE = 'WF_NTF'
      and    SIG_OBJ_ID = WN.NOTIFICATION_ID));

Line: 1102

    UPDATE 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;

Line: 1149

       delete from WF_NOTIFICATION_ATTRIBUTES WNA
       where WNA.NOTIFICATION_ID = l_nidListTAB(j);

Line: 1172

       DELETE FROM wf_comments wc
       WHERE wc.notification_id = l_nidListTAB(j);

Line: 1177

       delete from WF_NOTIFICATIONS WN
       where WN.NOTIFICATION_ID = l_nidListTAB(j);

Line: 1473

    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_NOTIFICATIONS wn
             where wn.RECIPIENT_ROLE = local.NAME
                or wn.ORIGINAL_RECIPIENT = local.NAME);

Line: 1493

    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 = p_origSystem
     and   (p_endDate is NULL or EXPIRATION_DATE <= p_endDate)
     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)
     and not exists
           (select NULL
              from WF_ITEMS wi
             where wi.OWNER_ROLE = local.NAME);

Line: 1540

        delete from WF_LOCAL_USER_ROLES local
         where local.USER_NAME = rcurs2.name
           and local.USER_ORIG_SYSTEM = rcurs2.orig_system
           and local.USER_ORIG_SYSTEM_ID = rcurs2.orig_system_id;

Line: 1545

        delete from WF_USER_ROLE_ASSIGNMENTS local
         where local.USER_NAME = rcurs2.name
           and local.USER_ORIG_SYSTEM = rcurs2.orig_system
           and local.USER_ORIG_SYSTEM_ID = rcurs2.orig_system_id;

Line: 1550

        delete from WF_LOCAL_USER_ROLES local
         where local.ROLE_NAME = rcurs2.name
           and local.ROLE_ORIG_SYSTEM = rcurs2.orig_system
           and local.ROLE_ORIG_SYSTEM_ID = rcurs2.orig_system_id;

Line: 1555

        delete from WF_USER_ROLE_ASSIGNMENTS local
         where local.ROLE_NAME = rcurs2.name
           and local.ROLE_ORIG_SYSTEM = rcurs2.orig_system
           and local.ROLE_ORIG_SYSTEM_ID = rcurs2.orig_system_id;

Line: 1563

      delete from WF_LOCAL_ROLES local
       where local.NAME = rcurs2.name
         and local.ORIG_SYSTEM = rcurs2.orig_system
         and local.ORIG_SYSTEM_ID = rcurs2.orig_system_id;

Line: 1575

        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;

Line: 1580

        delete from WF_USER_ROLE_ASSIGNMENTS 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;

Line: 1585

        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;

Line: 1590

        delete from WF_USER_ROLE_ASSIGNMENTS 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;

Line: 1598

      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;

Line: 1640

     select Count(*) into l_purgeable
     from  WF_ITEMS WI
     where WI.ITEM_TYPE = p_itemType
     and   WI.END_DATE <= sysdate
     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 not exists
         (select null
          from WF_ITEMS WI2
          WHERE WI2.END_DATE IS NULL
          START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
          AND WI2.ITEM_KEY = WI.ITEM_KEY
          CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE
          AND PRIOR WI2.ITEM_KEY = WI2.PARENT_ITEM_KEY
          UNION ALL
          select null
          from WF_ITEMS WI2
          WHERE WI2.END_DATE IS NULL
          START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
          AND WI2.ITEM_KEY = WI.ITEM_KEY
          CONNECT BY PRIOR WI2.PARENT_ITEM_TYPE = WI2.ITEM_TYPE
          AND PRIOR WI2.PARENT_ITEM_KEY = WI2.ITEM_KEY);

Line: 1689

    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';

Line: 1699

    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';

Line: 1710

    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';