eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.WF_PURGE dependencies on WF_ITEM

Line 9: -- Move wf_item_activity_status rows for particular itemtype/key from

5: l_docommit boolean;
6: l_runtimeonly boolean;
7:
8: -- procedure Move_To_History
9: -- Move wf_item_activity_status rows for particular itemtype/key from
10: -- main table to history table.
11: -- IN:
12: -- itemtype - Item type to move, or null for all itemtypes
13: -- itemkey - Item key to move, or null for all itemkeys

Line 25: -- Delete from wf_item_activity_statuses and wf_item_activity_statuses_h

21: end Move_To_History;
22:
23: --
24: -- procedure Item_Activity_Statuses
25: -- Delete from wf_item_activity_statuses and wf_item_activity_statuses_h
26: -- where end_date before argument.
27: -- IN:
28: -- itemtype - Item type to delete, or null for all itemtypes
29: -- itemkey - Item key to delete, or null for all itemkeys

Line 78: from WF_ITEMS WI

74: -- partial values for either item type or item key.
75:
76: cursor item2purge is
77: select WI.END_DATE, WI.ITEM_TYPE, WI.ITEM_KEY
78: from WF_ITEMS WI
79: where WI.ITEM_TYPE = itemtype
80: and WI.END_DATE <= enddate
81: and WI.END_DATE > xenddate
82: and exists

Line 84: from WF_ITEM_TYPES WIT

80: and WI.END_DATE <= enddate
81: and WI.END_DATE > xenddate
82: and exists
83: (select null
84: from WF_ITEM_TYPES WIT
85: where WI.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
86: and WI.ITEM_TYPE = WIT.NAME
87: and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
88: and not exists

Line 90: from WF_ITEMS WI2

86: and WI.ITEM_TYPE = WIT.NAME
87: and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
88: and not exists
89: (select null
90: from WF_ITEMS WI2
91: WHERE WI2.END_DATE IS NULL
92: START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
93: AND WI2.ITEM_KEY = WI.ITEM_KEY
94: CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE

Line 98: from WF_ITEMS WI2

94: CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE
95: AND PRIOR WI2.ITEM_KEY = WI2.PARENT_ITEM_KEY
96: UNION ALL
97: select null
98: from WF_ITEMS WI2
99: WHERE WI2.END_DATE IS NULL
100: START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
101: AND WI2.ITEM_KEY = WI.ITEM_KEY
102: CONNECT BY PRIOR WI2.PARENT_ITEM_TYPE = WI2.ITEM_TYPE

Line 109: from WF_ITEMS WI

105:
106: -- JWSMITH bug 2070056 - add new cursor for performance
107: cursor item3purge is
108: select WI.END_DATE, WI.ITEM_TYPE, WI.ITEM_KEY
109: from WF_ITEMS WI
110: where WI.ITEM_TYPE = itemtype
111: and WI.ITEM_KEY = itemkey
112: and WI.end_date <= enddate
113: and exists

Line 115: from WF_ITEM_TYPES WIT

111: and WI.ITEM_KEY = itemkey
112: and WI.end_date <= enddate
113: and exists
114: (select null
115: from WF_ITEM_TYPES WIT
116: where WI.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
117: and WI.ITEM_TYPE = WIT.NAME
118: and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
119: and not exists

Line 121: from WF_ITEMS WI2

117: and WI.ITEM_TYPE = WIT.NAME
118: and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
119: and not exists
120: (select null
121: from WF_ITEMS WI2
122: WHERE WI2.END_DATE IS NULL
123: START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
124: AND WI2.ITEM_KEY = WI.ITEM_KEY
125: CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE

Line 129: from WF_ITEMS WI2

125: CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE
126: AND PRIOR WI2.ITEM_KEY = WI2.PARENT_ITEM_KEY
127: UNION ALL
128: select null
129: from WF_ITEMS WI2
130: WHERE WI2.END_DATE IS NULL
131: START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
132: AND WI2.ITEM_KEY = WI.ITEM_KEY
133: CONNECT BY PRIOR WI2.PARENT_ITEM_TYPE = WI2.ITEM_TYPE

Line 140: from WF_ITEMS WI

136: -- CTILLEY bug 2642057 - added new cursor for use when FORCE
137: -- arg is TRUE.
138: cursor item4purge is
139: select WI.END_DATE, WI.ITEM_TYPE, WI.ITEM_KEY
140: from WF_ITEMS WI
141: where WI.ITEM_KEY = itemkey
142: and WI.ITEM_TYPE = itemtype
143: and WI.end_date <= enddate;
144:

Line 150: select /+ first_rows index(WI,WF_ITEMS_N3) /

146: -- of using like condition. We will no longer support passing partial values
147: -- for item type or item key.
148:
149: cursor item5purge is
150: select /*+ first_rows index(WI,WF_ITEMS_N3) */
151: WI.END_DATE, WI.ITEM_TYPE, WI.ITEM_KEY
152: from WF_ITEMS WI
153: where WI.END_DATE <= enddate
154: and WI.END_DATE > xenddate

Line 152: from WF_ITEMS WI

148:
149: cursor item5purge is
150: select /*+ first_rows index(WI,WF_ITEMS_N3) */
151: WI.END_DATE, WI.ITEM_TYPE, WI.ITEM_KEY
152: from WF_ITEMS WI
153: where WI.END_DATE <= enddate
154: and WI.END_DATE > xenddate
155: and exists
156: (select null

Line 157: from WF_ITEM_TYPES WIT

153: where WI.END_DATE <= enddate
154: and WI.END_DATE > xenddate
155: and exists
156: (select null
157: from WF_ITEM_TYPES WIT
158: where WI.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
159: and WI.ITEM_TYPE = WIT.NAME
160: and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
161: and not exists

Line 163: from WF_ITEMS WI2

159: and WI.ITEM_TYPE = WIT.NAME
160: and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
161: and not exists
162: (select null
163: from WF_ITEMS WI2
164: WHERE WI2.END_DATE IS NULL
165: START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
166: AND WI2.ITEM_KEY = WI.ITEM_KEY
167: CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE

Line 171: from WF_ITEMS WI2

167: CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE
168: AND PRIOR WI2.ITEM_KEY = WI2.PARENT_ITEM_KEY
169: UNION ALL
170: select null
171: from WF_ITEMS WI2
172: WHERE WI2.END_DATE IS NULL
173: START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
174: AND WI2.ITEM_KEY = WI.ITEM_KEY
175: CONNECT BY PRIOR WI2.PARENT_ITEM_TYPE = WI2.ITEM_TYPE

Line 295: if ( not (wf_item.acquire_lock(l_itemtypeTAB(j), l_itemkeyTAB(j), FALSE))) then

291:
292: -- If we cannot lock current item, try to lock the last item on the index table.
293: -- If we cannot acquire lock on the last item, delete last item and get the
294: -- next last item on the index table until we can acquire a lock.
295: if ( not (wf_item.acquire_lock(l_itemtypeTAB(j), l_itemkeyTAB(j), FALSE))) then
296: xenddate := l_enddateTAB(j);
297: <>
298: loop
299: if (l_itemkeyTAB.COUNT < j ) then

Line 302: elsif (wf_item.acquire_lock(l_itemtypeTAB(l_itemtypeTAB.LAST),

298: loop
299: if (l_itemkeyTAB.COUNT < j ) then
300: exit outer_lock_loop;
301:
302: elsif (wf_item.acquire_lock(l_itemtypeTAB(l_itemtypeTAB.LAST),
303: l_itemkeyTAB(l_itemkeyTAB.LAST), FALSE)) then
304:
305: -- Once we acquired a lock on a last item, copy the last time to
306: -- the current index so that we have a dense index table.

Line 324: from WF_ITEM_ACTIVITY_STATUSES WIAS,

320: FOR j IN l_itemkeyTAB.FIRST..l_itemkeyTAB.LAST loop
321: begin
322: select WN.NOTIFICATION_ID
323: BULK COLLECT into l_tempListTAB
324: from WF_ITEM_ACTIVITY_STATUSES WIAS,
325: WF_NOTIFICATIONS WN
326: where WIAS.ITEM_TYPE = l_itemtypeTAB(j)
327: and WIAS.ITEM_KEY = l_itemkeyTAB(j)
328: and WIAS.NOTIFICATION_ID = WN.GROUP_ID

Line 337: from WF_ITEM_ACTIVITY_STATUSES_H WIASH,

333: where SIG_OBJ_TYPE = 'WF_NTF'
334: and SIG_OBJ_ID = WN.NOTIFICATION_ID))
335: union all
336: select WN.NOTIFICATION_ID
337: from WF_ITEM_ACTIVITY_STATUSES_H WIASH,
338: WF_NOTIFICATIONS WN
339: where WIASH.ITEM_TYPE = l_itemtypeTAB(j)
340: and WIASH.ITEM_KEY = l_itemkeyTAB(j)
341: and WIASH.NOTIFICATION_ID = WN.GROUP_ID

Line 416: delete from WF_ITEM_ACTIVITY_STATUSES_H

412:
413: begin
414: --delete all status history.
415: FORALL j IN l_itemkeyTAB.FIRST..l_itemkeyTAB.LAST
416: delete from WF_ITEM_ACTIVITY_STATUSES_H
417: where ITEM_TYPE = l_itemtypeTAB(j)
418: and ITEM_KEY = l_itemkeyTAB(j);
419:
420: --delete all statuses.

Line 422: delete from WF_ITEM_ACTIVITY_STATUSES

418: and ITEM_KEY = l_itemkeyTAB(j);
419:
420: --delete all statuses.
421: FORALL j IN l_itemkeyTAB.FIRST..l_itemkeyTAB.LAST
422: delete from WF_ITEM_ACTIVITY_STATUSES
423: where ITEM_TYPE = l_itemtypeTAB(j)
424: and ITEM_KEY = l_itemkeyTAB(j);
425:
426: --delete item attributes

Line 428: delete from WF_ITEM_ATTRIBUTE_VALUES

424: and ITEM_KEY = l_itemkeyTAB(j);
425:
426: --delete item attributes
427: FORALL j IN l_itemkeyTAB.FIRST..l_itemkeyTAB.LAST
428: delete from WF_ITEM_ATTRIBUTE_VALUES
429: where ITEM_TYPE = l_itemtypeTAB(j)
430: and ITEM_KEY = l_itemkeyTAB(j);
431:
432: -- 3966635 Workflwo Provisioning Project

Line 441: delete from WF_ITEMS

437: -- and source = 'WORKFLOW';
438: --
439: --finally delete the item itself.
440: FORALL j IN l_itemkeyTAB.FIRST..l_itemkeyTAB.LAST
441: delete from WF_ITEMS
442: where ITEM_TYPE = l_itemtypeTAB(j)
443: and ITEM_KEY = l_itemkeyTAB(j);
444:
445: exception

Line 518: Wf_Item.ClearCache;

514: close item5purge;
515: end if;
516:
517: -- Clear engine runtime cache for convenience
518: Wf_Item.ClearCache;
519:
520: exception
521: when others then
522: if (item2purge%ISOPEN) then

Line 623: from WF_ITEM_TYPES WIT

619: where WA.ITEM_TYPE = acttype
620: and WA.NAME = actname
621: and exists
622: (select null
623: from WF_ITEM_TYPES WIT
624: where WA.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
625: and WA.ITEM_TYPE = WIT.NAME
626: and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type);
627:

Line 632: from WF_ITEMS WI

628: --Use itemcurs1 if the rootname is 'ROOT'
629: cursor itemcurs1(begdate in date, enddate in date,
630: roottype in varchar2, rootname in varchar2) is
631: select WI.ITEM_TYPE, WI.ITEM_KEY, WI.ROOT_ACTIVITY, WI.BEGIN_DATE
632: from WF_ITEMS WI
633: where WI.BEGIN_DATE between begdate and nvl(enddate, WI.BEGIN_DATE)
634: and WI.ITEM_TYPE = roottype;
635:
636: --Use itemcurs2 for other rootnames

Line 640: from WF_ITEMS WI

636: --Use itemcurs2 for other rootnames
637: cursor itemcurs2(begdate in date, enddate in date,
638: roottype in varchar2, rootname in varchar2) is
639: select WI.ITEM_TYPE, WI.ITEM_KEY, WI.ROOT_ACTIVITY, WI.BEGIN_DATE
640: from WF_ITEMS WI
641: where WI.BEGIN_DATE between begdate and nvl(enddate, WI.BEGIN_DATE)
642: and WI.ITEM_TYPE = roottype
643: and WI.ROOT_ACTIVITY = rootname ;
644:

Line 1023: from WF_ITEM_ACTIVITY_STATUSES WIAS

1019: from WF_NOTIFICATIONS WN
1020: where WN.MESSAGE_TYPE = itemtype
1021: and not exists
1022: (select NULL
1023: from WF_ITEM_ACTIVITY_STATUSES WIAS
1024: where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
1025: and not exists
1026: (select NULL
1027: from WF_ITEM_ACTIVITY_STATUSES_H WIAS

Line 1027: from WF_ITEM_ACTIVITY_STATUSES_H WIAS

1023: from WF_ITEM_ACTIVITY_STATUSES WIAS
1024: where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
1025: and not exists
1026: (select NULL
1027: from WF_ITEM_ACTIVITY_STATUSES_H WIAS
1028: where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
1029: and (
1030: exists(
1031: select null

Line 1032: from WF_ITEM_TYPES WIT

1028: where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
1029: and (
1030: exists(
1031: select null
1032: from WF_ITEM_TYPES WIT
1033: where WN.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0) <= enddate
1034: and WN.MESSAGE_TYPE = WIT.NAME
1035: and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
1036: or not exists(

Line 1038: from WF_ITEM_TYPES WIT

1034: and WN.MESSAGE_TYPE = WIT.NAME
1035: and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
1036: or not exists(
1037: select null
1038: from WF_ITEM_TYPES WIT
1039: where WN.MESSAGE_TYPE = WIT.NAME))
1040: and(
1041: (purgesigs = 1)
1042: or not exists

Line 1053: from WF_ITEM_ACTIVITY_STATUSES WIAS

1049: select WN.NOTIFICATION_ID
1050: from WF_NOTIFICATIONS WN
1051: where not exists
1052: (select NULL
1053: from WF_ITEM_ACTIVITY_STATUSES WIAS
1054: where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
1055: and not exists
1056: (select NULL
1057: from WF_ITEM_ACTIVITY_STATUSES_H WIAS

Line 1057: from WF_ITEM_ACTIVITY_STATUSES_H WIAS

1053: from WF_ITEM_ACTIVITY_STATUSES WIAS
1054: where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
1055: and not exists
1056: (select NULL
1057: from WF_ITEM_ACTIVITY_STATUSES_H WIAS
1058: where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
1059: and (
1060: exists(
1061: select null

Line 1062: from WF_ITEM_TYPES WIT

1058: where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
1059: and (
1060: exists(
1061: select null
1062: from WF_ITEM_TYPES WIT
1063: where WN.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0) <= enddate
1064: and WN.MESSAGE_TYPE = WIT.NAME
1065: and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
1066: or not exists(

Line 1068: from WF_ITEM_TYPES WIT

1064: and WN.MESSAGE_TYPE = WIT.NAME
1065: and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
1066: or not exists(
1067: select null
1068: from WF_ITEM_TYPES WIT
1069: where WN.MESSAGE_TYPE = WIT.NAME))
1070: and(
1071: (purgesigs = 1)
1072: or not exists

Line 1106: FROM wf_item_activity_statuses wias

1102: UPDATE wf_notifications wn
1103: SET end_date = nvl(begin_date, to_date('2002/08/01','YYYY/MM/DD')) + 1
1104: WHERE NOT EXISTS
1105: (SELECT NULL
1106: FROM wf_item_activity_statuses wias
1107: WHERE wias.notification_id = wn.group_id)
1108: AND NOT EXISTS
1109: (SELECT NULL
1110: FROM wf_item_activity_statuses_h wiash

Line 1110: FROM wf_item_activity_statuses_h wiash

1106: FROM wf_item_activity_statuses wias
1107: WHERE wias.notification_id = wn.group_id)
1108: AND NOT EXISTS
1109: (SELECT NULL
1110: FROM wf_item_activity_statuses_h wiash
1111: WHERE wiash.notification_id = wn.group_id)
1112: AND wn.end_date is null
1113: AND wn.begin_date <= enddate
1114: AND rownum < Wf_Purge.Commit_Frequency;

Line 1318: Wf_Item.ClearCache;

1314: end if;
1315: end if;
1316:
1317: -- Clear engine runtime cache for convenience
1318: Wf_Item.ClearCache;
1319:
1320: -- Purge orphan notifications only if runtimeonly is false
1321: if (not nvl(runtimeonly, TRUE)) then
1322: --Bug 6759163 : 'docommit' parameter passed to Notifications() as well

Line 1516: from WF_ITEMS wi

1512: where wn.RECIPIENT_ROLE = local.NAME
1513: or wn.ORIGINAL_RECIPIENT = local.NAME)
1514: and not exists
1515: (select NULL
1516: from WF_ITEMS wi
1517: where wi.OWNER_ROLE = local.NAME);
1518:
1519: rcurs role_cursor%rowtype;
1520: rcurs2 orig_system_cursor%rowtype;

Line 1641: from WF_ITEMS WI

1637: l_purgeable number ;
1638: BEGIN
1639: --Get all records for the itemtype
1640: select Count(*) into l_purgeable
1641: from WF_ITEMS WI
1642: where WI.ITEM_TYPE = p_itemType
1643: and WI.END_DATE <= sysdate
1644: and exists
1645: (select null

Line 1646: from WF_ITEM_TYPES WIT

1642: where WI.ITEM_TYPE = p_itemType
1643: and WI.END_DATE <= sysdate
1644: and exists
1645: (select null
1646: from WF_ITEM_TYPES WIT
1647: where WI.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=sysdate
1648: and WI.ITEM_TYPE = WIT.NAME
1649: )
1650: and not exists

Line 1652: from WF_ITEMS WI2

1648: and WI.ITEM_TYPE = WIT.NAME
1649: )
1650: and not exists
1651: (select null
1652: from WF_ITEMS WI2
1653: WHERE WI2.END_DATE IS NULL
1654: START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
1655: AND WI2.ITEM_KEY = WI.ITEM_KEY
1656: CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE

Line 1660: from WF_ITEMS WI2

1656: CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE
1657: AND PRIOR WI2.ITEM_KEY = WI2.PARENT_ITEM_KEY
1658: UNION ALL
1659: select null
1660: from WF_ITEMS WI2
1661: WHERE WI2.END_DATE IS NULL
1662: START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
1663: AND WI2.ITEM_KEY = WI.ITEM_KEY
1664: CONNECT BY PRIOR WI2.PARENT_ITEM_TYPE = WI2.ITEM_TYPE

Line 1690: from wf_item_activity_statuses wias, wf_items wi

1686: c_item_key varchar2(240);
1687:
1688: cursor c_error1 is
1689: select wi.item_key
1690: from wf_item_activity_statuses wias, wf_items wi
1691: where wi.item_type = 'WFERROR'
1692: and parent_item_type = itemtype
1693: and wi.parent_item_type = wias.item_type
1694: and wi.parent_item_key = wias.item_key

Line 1700: from wf_item_activity_statuses wias, wf_items wi

1696: and wias.activity_status = 'COMPLETE';
1697:
1698: cursor c_error2 is
1699: select wi.item_key
1700: from wf_item_activity_statuses wias, wf_items wi
1701: where wi.item_type = 'WFERROR'
1702: and parent_item_type = itemtype
1703: and parent_item_key = itemkey
1704: and wi.parent_item_type = wias.item_type

Line 1711: from wf_item_activity_statuses wias, wf_items wi

1707: and wias.activity_status = 'COMPLETE';
1708:
1709: cursor c_error3 is
1710: select wi.item_key
1711: from wf_item_activity_statuses wias, wf_items wi
1712: where wi.item_type = 'WFERROR'
1713: and wi.parent_item_type = wias.item_type
1714: and wi.parent_item_key = wias.item_key
1715: and wi.parent_context = wias.process_activity