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