1 PACKAGE Body WF_Repopulate_AQ AS
2 /* $Header: WFAQREPB.pls 120.1 2005/07/02 04:26:06 appldev ship $ */
3
4 --
5 -- Procedure
6 -- Repopulate_SMTP_Item
7 --
8 -- Purpose
9 -- Repopulates the smtp with actions related to a particular item
10 -- Note: It does not clear off existing actions already on the queues...
11 -- instead we rely on the runtime code to verify actions still need to
12 -- be done.
13 --
14 -- Arguments:
15 -- Item_Type -- set to null for all items.
16 -- Item_Key -- set to null for all items in type.
17 --
18 Procedure Repopulate_SMTP_Item( x_Item_Type in VARCHAR2,
19 x_Item_Key in VARCHAR2)
20
21 is
22 PRAGMA AUTONOMOUS_TRANSACTION;
23
24 Cursor CN IS
25 Select N.NOTIFICATION_ID
26 from WF_ITEM_ACTIVITY_STATUSES IAS, WF_NOTIFICATIONS N
27 where IAS.Item_Type = x_Item_Type
28 and IAS.Item_Key = x_Item_Key
29 and IAS.NOTIFICATION_ID = N.GROUP_ID
30 and N.status = 'OPEN'
31 and N.mail_status in ('MAIL', 'INVALID');
32
33 begin
34 For CN_REC in CN Loop
35 wf_xml.EnqueueNotification(CN_REC.NOTIFICATION_ID);
36 end loop;
37
38 commit;
39 end;
40
41 --
42 -- Procedure
43 -- Repopulate_Deferred_Item
44 --
45 -- Purpose
46 -- Repopulates the smtp with actions related to a particular item
47 -- Note: It does not clear off existing actions already on the queues...
48 -- instead we rely on the runtime code to verify actions still need to
49 -- be done.
50 --
51 -- Arguments:
52 -- Item_Type -- set to null for all items.
53 -- Item_Key -- set to null for all items in type.
54 --
55 Procedure Repopulate_Deferred_Item( x_Item_Type in VARCHAR2,
56 x_Item_Key in VARCHAR2)
57
58 is
59 PRAGMA AUTONOMOUS_TRANSACTION;
60
61 Cursor CD IS
62 select
63 CWIAS.ITEM_TYPE,
64 CWIAS.ITEM_KEY,
65 CWIAS.PROCESS_ACTIVITY,
66 greatest((CWIAS.BEGIN_DATE - sysdate)*86400,0) delay
67 from WF_ITEM_ACTIVITY_STATUSES CWIAS, WF_PROCESS_ACTIVITIES CWPA,
68 WF_PROCESS_ACTIVITIES PWPA, WF_ITEM_ACTIVITY_STATUSES PWIAS
69 where CWIAS.ACTIVITY_STATUS = 'DEFERRED'
70 and CWIAS.PROCESS_ACTIVITY = CWPA.INSTANCE_ID
71 and CWPA.ACTIVITY_ITEM_TYPE = x_Item_Type
72 and CWIAS.ITEM_TYPE = x_Item_Type
73 and CWIAS.ITEM_KEY = x_Item_Key
74 and CWPA.PROCESS_NAME = PWPA.ACTIVITY_NAME
75 and CWPA.PROCESS_ITEM_TYPE = PWPA.ACTIVITY_ITEM_TYPE
76 and PWPA.INSTANCE_ID = PWIAS.PROCESS_ACTIVITY
77 and PWIAS.ITEM_TYPE = CWIAS.ITEM_TYPE
78 and PWIAS.ITEM_KEY = CWIAS.ITEM_KEY
79 and PWIAS.ACTIVITY_STATUS <> 'SUSPEND';
80 msg_id raw(16);
81
82 begin
83 For CD_REC in CD Loop
84 wf_queue.enqueue_event
85 (queuename=>wf_queue.DeferredQueue,
86 itemtype=>CD_REC.item_type,
87 itemkey=>CD_REC.item_key,
88 actid=>CD_REC.process_activity,
89 delay=>CD_REC.delay,
90 message_handle=>msg_id);
91 -- dont need a message handle
92 msg_id := null;
93 end loop;
94
95 commit;
96 end;
97
98 --
99 -- Procedure
100 -- PopulateAQforItem
101 --
102 -- Purpose
103 -- Repopulates the smtp and/or deferred queue with actions related to a
104 -- particular item, item type, or all items. Note: It does not clear
105 -- off existing actions already on the queues...instead we rely on the
106 -- runtime code to verify actions still need to be done.
107 --
108 -- Arguments:
109 -- ItemType -- set to null for all items.
110 -- ItemKey -- set to null for all items in type.
111 -- SMTPFlag -- Y/N: repopulate smtp aq?
112 -- DeferredFlag -- Y/N: repopulate deferred aq?
113 --
114 Procedure PopulateAQforItem( ItemType in VARCHAR2,
115 ItemKey in VARCHAR2,
116 SMTPFlag in VARCHAR2 ,
117 DeferredFlag in VARCHAR2 )
118
119 is
120
121 -- Bug 2497815.
122 -- The query for the cursor is modified to refer to values in
123 -- the variables l_item_type and l_item_key.
124
125 l_item_type VARCHAR2(8) := nvl(ItemType, '%');
126 l_item_key VARCHAR2(240) := nvl(ItemKey, '%');
127
128 Cursor CI IS
129 Select Item_Type, Item_Key
130 from wf_items
131 where Item_Type like l_item_type
132 and Item_Key like l_item_key
133 order by 1,2;
134
135 begin
136
137 /* If both flags are 'N', the caller is an idiot, but let's not
138 make the customer wait */
139 if ((SmtpFlag = 'Y') OR (DeferredFlag = 'Y')) then
140 For CI_REC in CI Loop
141 if (SmtpFlag = 'Y') then
142 Repopulate_SMTP_Item(CI_REC.Item_Type, CI_REC.Item_Key);
143 end if;
144
145 if (DeferredFlag = 'Y') then
146 Repopulate_Deferred_Item(CI_REC.Item_Type, CI_REC.Item_Key);
147 end if;
148 end loop;
149 end if;
150 end;
151
152
153
154 END WF_Repopulate_AQ;
[Home] [Help]