1 package body WF_EVENT_QH as
2 /* $Header: wfquhndb.pls 120.3.12020000.4 2013/04/09 17:08:19 skandepu ship $ */
3 ------------------------------------------------------------------------------
4 PROCEDURE dequeue(p_agent_guid in raw,
5 p_event out nocopy wf_event_t,
6 p_wait in binary_integer default dbms_aq.no_wait)
7 is
8 x_queue_name varchar2(80);
9 x_agent_name varchar2(30);
10 x_dequeue_options dbms_aq.dequeue_options_t;
11 x_message_properties dbms_aq.message_properties_t;
12 x_msgid RAW(16);
13 no_messages exception;
14 pragma exception_init (no_messages, -25228);
15 --Define the snapshot too old error
16 snap_too_old exception;
17 pragma exception_init(snap_too_old, -1555);
18 l_corrId_list varchar2(4000) := null;
19
20 begin
21 select upper(queue_name), upper(name)
22 into x_queue_name, x_agent_name
23 from wf_agents
24 where guid = p_agent_guid;
25
26 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
27 wf_log_pkg.string(wf_log_pkg.level_procedure,
28 'wf.plsql.WF_EVENT_QH.dequeue.Begin',
29 'Dequeuing '||x_queue_name||' on '||x_agent_name);
30 end if;
31
32 -- Set correlation Id for dequeue if only available and not '%'
33 if (wf_event.g_correlation is not null and wf_event.g_correlation <> '%') then
34 -- Seeded agent with this queue handler
35 if (x_agent_name like 'WF_%') then
36 if (wf_event.account_name is null) then
37 wf_event.SetAccountName;
38 end if;
39 x_dequeue_options.correlation := wf_event.account_name || ':' || wf_event.g_correlation;
40 else
41 x_dequeue_options.correlation := wf_event.g_correlation;
42 end if;
43
44 -- ER 16593551: If standard component is dequeuing the messages, set the dequeue condition
45 -- as a PLSQL function that evaluates the condition based on the message corrId and dedicated
46 -- components correlation Id list and returns 0 or 1
47 elsif (x_agent_name = 'WF_DEFERRED') then
48
49 if (wf_event.account_name is null) then
50 wf_event.SetAccountName;
51 end if;
52
53 -- Get the dedicated components correlation Id list
54 l_corrId_list := WF_CORE.getDedicatedComponentsCorrIds(x_agent_name, wf_event.account_name);
55
56 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
57 wf_log_pkg.string(wf_log_pkg.level_statement,
58 'wf.plsql.WF_EVENT_QH.dequeue.corr',
59 'The dedicated components correlation ids list is:' || l_corrId_list);
60 end if;
61
62 x_dequeue_options.correlation := null;
63 if(l_corrId_list is not null) then
64 -- Set the dequeue condition if the dedicated components correlation Id list is not null
65 x_dequeue_options.deq_condition := 'WF_CORE.matchCorrId(corrid, '''|| l_corrId_list ||''') = 1';
66
67 end if;
68
69 end if;
70
71 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
72 if (wf_event.g_correlation is not null) then
73 wf_log_pkg.string(wf_log_pkg.level_procedure,
74 'wf.plsql.WF_EVENT_QH.dequeue.corrid',
75 'Dequeuing with Correlation:'|| x_dequeue_options.correlation);
76 else
77 wf_log_pkg.string(wf_log_pkg.level_procedure,
78 'wf.plsql.WF_EVENT_QH.dequeue.corrid',
79 'Dequeuing with dequeue condition:'||x_dequeue_options.deq_condition);
80 end if;
81 end if;
82
83 if ((WF_EVENT.g_queueType is NULL) or
84 (WF_EVENT.g_queueType <> 'EXCEPTION_QUEUE')) then
85 x_dequeue_options.consumer_name := x_agent_name;
86 end if;
87
88 -- This functionality is dependent on 9i, so it cannot be uncommented in this
89 -- file until 9i is the minimum rdbms on both e-business suite and iAS.
90 --
91 -- if (WF_EVENT.g_deq_condition is not NULL) then
92 -- x_dequeue_options.deq_condition := WF_EVENT.g_deq_condition;
93 --
94 -- end if;
95 --
96
97 x_dequeue_options.wait := p_wait;
98 x_dequeue_options.navigation := wf_event.getQueueNavigation;
99
100
101 BEGIN
102 DBMS_AQ.DEQUEUE(queue_name => x_queue_name,
103 dequeue_options => x_dequeue_options,
104 message_properties => x_message_properties, /* OUT */
105 payload => p_event, /* OUT */
106 msgid => x_msgid); /* OUT */
107
108 -- wf_event.navigation := dbms_aq.next_message;
109 EXCEPTION
110 when no_messages then
111 if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then
112 wf_log_pkg.string(wf_log_pkg.level_event,
113 'wf.plsql.WF_EVENT_QH.dequeue.queue_empty',
114 'No more messages in dequeue.');
115 end if;
116
117 -- reset navigation
118 wf_event.resetNavigationParams;
119 p_event := NULL;
120 return;
121 --Capture the snapshot too old error
122 when snap_too_old then
123 -- reset navigation
124 wf_event.resetNavigationParams;
125 x_dequeue_options.navigation := wf_event.getQueueNavigation;
126 DBMS_AQ.DEQUEUE(queue_name => x_queue_name,
127 dequeue_options => x_dequeue_options,
128 message_properties => x_message_properties, /* OUT */
129 payload => p_event, /* OUT */
130 msgid => x_msgid); /* OUT */
131
132
133 when others then
134 raise;
135 END;
136
137 -- Set the Receive Date
138 p_event.SetReceiveDate(sysdate);
139 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
140 wf_log_pkg.string(wf_log_pkg.level_procedure,
141 'wf.plsql.WF_EVENT_QH.dequeue.End',
142 'Finished');
143 end if;
144 exception
145 when others then
146 Wf_Core.Context('Wf_Event_QH', 'Dequeue', x_queue_name,
147 'SQL err is '||substr(sqlerrm,1,200));
148 raise;
149 end dequeue;
150 ------------------------------------------------------------------------------
151 PROCEDURE enqueue(p_event in wf_event_t,
152 p_out_agent_override in wf_agent_t default null)
153 is
154 x_out_agent_name varchar2(30);
155 x_out_system_name varchar2(30);
156 x_to_agent_name varchar2(30);
157 x_to_system_name varchar2(30);
158 x_out_queue varchar2(80);
159 x_to_queue varchar2(80);
160 x_enqueue_options dbms_aq.enqueue_options_t;
161 x_message_properties dbms_aq.message_properties_t;
162 x_msgid RAW(16);
163 x_name varchar2(30);
164 x_address varchar2(1024);
165 x_protocol varchar2(30);
166 x_protocol_num number := 0;
167 delay number := 0;
168
169 l_q_correlation_id varchar2(240);
170
171 --Bug 2676549
172 --Cursor to select the to_agents for the recipient list
173 CURSOR recipients(agent_name varchar2,system_name varchar2) is
174 select agt2.name ,agt2.address, agt2.protocol, agt2.queue_name
175 from wf_agent_groups agp ,
176 wf_agents agt1 ,
177 wf_agents agt2 ,
178 wf_systems sys
179 where agt1.name = agent_name
180 and agp.group_guid = agt1.guid
181 and agt1.type = 'GROUP'
182 and agt1.status = 'ENABLED'
183 and agt2.guid = agp.member_guid
184 and sys.name = system_name
185 and sys.guid = agt2.system_guid;
186
187 i number := 1;
188 x_type varchar2(8);
189 begin
190 -- Determine the out queue --
191 if (p_out_agent_override is not null) then
192 x_out_agent_name := p_out_agent_override.GetName();
193 x_out_system_name := p_out_agent_override.GetSystem();
194 else
195 x_out_agent_name := p_event.From_Agent.Name;
196 x_out_system_name := p_event.From_Agent.System;
197 end if;
198
199 -- Get Out Agent details --
200 select agt.queue_name into x_out_queue
201 from wf_agents agt,
202 wf_systems sys
203 where agt.name = x_out_agent_name
204 and sys.name = x_out_system_name
205 and sys.guid = agt.system_guid;
206
207 -- Determine the to queue (if set) --
208 -- If there is a to queue, need to set recipient list address --
209 if (p_event.To_Agent is not null) AND
210 (x_out_agent_name <> 'WF_DEFERRED') then
211 WF_EVENT.Set_Recipient_List(p_event,
212 x_out_agent_name ,
213 x_out_system_name,
214 x_message_properties);
215 end if;
216
217 /*
218 ** Set the Priority
219 */
220 x_message_properties.priority := p_event.Priority;
221
222 /*
223 ** Set the Delay if required, also used for Deferred Agent
224 */
225 if (p_event.Send_Date > sysdate) then
226 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
227 wf_log_pkg.string(wf_log_pkg.level_statement,
228 'wf.plsql.WF_EVENT_QH.enqueue.delay',
229 'Delay Detected');
230 end if;
231
232 delay := (p_event.Send_Date - sysdate) *24*60*60;
233
234 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
235 wf_log_pkg.string(wf_log_pkg.level_statement,
236 'wf.plsql.WF_EVENT_QH.enqueue.delay_time',
237 'Delay ='||to_char(delay));
238 end if;
239
240 if delay > 1 then
241 -- message_properties.delay is BINARY_INTEGER, so check if delay is
242 -- too big, and set the max delay to be (2**31)-1.
243 if (delay >= power(2,31)) then
244 x_message_properties.delay := power(2,31)-1;
245 else
246 x_message_properties.delay := delay;
247 end if;
248 end if;
249 end if;
250
251 /*
252 ** if we are enqueuing for an internal agent, must set the account name
253 ** into the correlation id
254 */
255 if (x_out_agent_name like 'WF_%'
256 or x_to_agent_name like 'WF_%') then
257 if wf_event.account_name is null then
258 wf_event.SetAccountName;
259 end if;
260 x_message_properties.correlation := wf_event.account_name;
261 end if;
262 if (x_out_agent_name = 'WF_DEFERRED'
263 or x_to_agent_name = 'WF_DEFERRED') then
264 --Bug 2505492
265 --Append the event name to the correlation id for DEFERRED/ERROR agent.
266 --We have a separate queue handler for WF_ERROR
267 l_q_correlation_id := p_event.event_name;
268 else
269 --Bug 3992967
270 --For application agents (agents other than DEFERRED or ERROR),
271 --correlation id should be extracted from Q_CORRELATION_ID
272 l_q_correlation_id := p_event.getValueForParameter('Q_CORRELATION_ID');
273
274 end if;
275
276 IF (l_q_correlation_id IS NOT NULL) THEN
277 -- If account name is set, append account name in front of correlation id.
278 if (x_message_properties.correlation is not null) then
279 x_message_properties.correlation := x_message_properties.correlation ||
280 ':' || l_q_correlation_id;
281 else
282 x_message_properties.correlation := l_q_correlation_id;
283 end if;
284 END IF;
285 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
286 wf_log_pkg.string(wf_log_pkg.level_statement,
287 'wf.plsql.WF_EVENT_QH.enqueue.dbms_aq',
288 'calling dbms_aq.enqueue');
289 end if;
290
291 DBMS_AQ.ENQUEUE(
292 queue_name => x_out_queue,
293 enqueue_options => x_enqueue_options,
294 message_properties => x_message_properties,
295 payload => p_event,
296 msgid => x_msgid); /* OUT*/
297
298 --<rwunderl:2699059> Storing the msgid.
299 WF_EVENT.g_msgid := x_msgid;
300
301 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
302 wf_log_pkg.string(wf_log_pkg.level_procedure,
303 'wf.plsql.WF_EVENT_QH.enqueue.End',
304 'finished calling dbms_aq.enqueue');
305 end if;
306
307 exception
308 when others then
309 Wf_Core.Context('Wf_Event_QH', 'Enqueue', x_out_queue,
310 'SQL err is '||substr(sqlerrm,1,200));
311 raise;
312 end enqueue;
313 ------------------------------------------------------------------------------
314 end WF_EVENT_QH;