[Home] [Help]
PACKAGE BODY: APPS.WF_EVENT_SUBSCRIPTIONS_PKG
Source
1 package body WF_EVENT_SUBSCRIPTIONS_PKG as
2 /* $Header: WFEVSUBB.pls 120.8 2011/04/26 19:36:59 vshanmug ship $ */
3 m_table_name varchar2(255) := 'WF_EVENT_SUBSCRIPTIONS';
4 m_package_version varchar2(30) := '1.0';
5
6
7 procedure validate_subscription (X_EVENT_FILTER_GUID in raw,
8 X_CUSTOMIZATION_LEVEL in varchar2,
9 X_STATUS in varchar2); -- Bug 2756800
10
11 procedure fetch_custom_level(X_GUID in raw,
12 X_CUSTOMIZATION_LEVEL out nocopy varchar2);
13
14 function find_subscription(x_subscription_guid in varchar2,
15 x_system_guid in raw,
16 x_source_type in varchar2,
17 x_source_agent_guid in raw,
18 x_event_filter_guid in raw,
19 x_phase in number,
20 x_rule_data in varchar2,
21 x_priority in number,
22 x_rule_function in varchar2,
23 x_wf_process_type in varchar2,
24 x_wf_process_name in varchar2,
25 x_parameters in varchar2,
26 x_owner_name in varchar2,
27 x_owner_tag in varchar2) return raw;
28
29 ----------------------------------------------------------------------------
30 procedure INSERT_ROW (
31 X_ROWID in out nocopy varchar2,
32 X_GUID in raw,
33 X_SYSTEM_GUID in raw,
34 X_SOURCE_TYPE in varchar2,
35 X_SOURCE_AGENT_GUID in raw,
36 X_EVENT_FILTER_GUID in raw,
37 X_PHASE in number,
38 X_STATUS in varchar2,
39 X_RULE_DATA in varchar2,
40 X_OUT_AGENT_GUID in raw,
41 X_TO_AGENT_GUID in raw,
42 X_PRIORITY in number,
43 X_RULE_FUNCTION in varchar2,
44 X_WF_PROCESS_TYPE in varchar2,
45 X_WF_PROCESS_NAME in varchar2,
46 X_PARAMETERS in varchar2,
47 X_OWNER_NAME in varchar2,
48 X_OWNER_TAG in varchar2,
49 X_CUSTOMIZATION_LEVEL in varchar2,
50 X_LICENSED_FLAG in varchar2,
51 X_DESCRIPTION in varchar2,
52 X_EXPRESSION in varchar2,
53 X_ACTION_CODE in varchar2,
54 X_ON_ERROR_CODE in varchar2,
55 X_JAVA_RULE_FUNC in varchar2,
56 X_MAP_CODE in varchar2,
57 X_STANDARD_CODE in varchar2,
58 X_STANDARD_TYPE in varchar2
59 ) is
60
61 l_guid raw(16);
62 l_event_name varchar2(240);
63 cursor C is select ROWID from wf_event_subscriptions where guid = x_guid;
64 l_licensed_flag varchar2(1);
65 l_rule_func varchar2(240);
66 begin
67 validate_subscription (X_EVENT_FILTER_GUID,
68 X_CUSTOMIZATION_LEVEL,
69 X_STATUS); -- Bug 2756800
70
71 l_licensed_flag := WF_EVENTS_PKG.is_product_licensed (X_OWNER_TAG);
72 if (X_RULE_FUNCTION is null and X_JAVA_RULE_FUNC is null) then
73 l_rule_func := 'WF_RULE.DEFAULT_RULE';
74 elsif (x_rule_function is not null) then
75 l_rule_func := x_rule_function;
76 end if;
77
78 -- Get the GUID of the subscription if one is already there with the same information
79 l_guid := Find_Subscription(x_subscription_guid => insert_row.x_guid,
80 x_system_guid => insert_row.x_system_guid,
81 x_source_type => insert_row.x_source_type,
82 x_source_agent_guid => insert_row.x_source_agent_guid,
83 x_event_filter_guid => insert_row.x_event_filter_guid,
84 x_phase => insert_row.x_phase,
85 x_rule_data => insert_row.x_rule_data,
86 x_priority => insert_row.x_priority,
87 x_rule_function => insert_row.x_rule_function,
88 x_wf_process_type => insert_row.x_wf_process_type,
89 x_wf_process_name => insert_row.x_wf_process_name,
90 x_parameters => insert_row.x_parameters,
91 x_owner_name => insert_row.x_owner_name,
92 x_owner_tag => insert_row.x_owner_tag);
93
94 if (l_guid <> x_guid) then
95 -- If l_guid is not same as x_guid, we already have a subscription with same information.
96 -- Throw an error to the UI.
97 begin
98 SELECT name
99 INTO l_event_name
100 FROM wf_events
101 WHERE guid = x_event_filter_guid;
102 exception
103 when no_data_found then
104 null;
105 end;
106 Wf_Core.Token('EVENT', l_event_name);
107 Wf_Core.Token('SOURCE', x_source_type);
108 Wf_Core.Token('PHASE', x_phase);
109 Wf_Core.Token('OWNERNAME', x_owner_name);
110 Wf_Core.Token('OWNERTAG', x_owner_tag);
111 Wf_Core.Raise('WFE_DUPLICATE_SUB');
112 else
113 insert into wf_event_subscriptions (
114 guid,
115 system_guid,
116 source_type,
117 source_agent_guid,
118 event_filter_guid,
119 phase,
120 status,
121 rule_data,
122 out_agent_guid,
123 to_agent_guid,
124 priority,
125 rule_function,
126 wf_process_type,
127 wf_process_name,
128 parameters,
129 owner_name,
130 owner_tag,
131 customization_level,
132 licensed_flag,
133 description,
134 expression,
135 action_code,
136 on_error_code,
137 java_rule_func,
138 map_code,
139 standard_code,
140 standard_type
141 ) select X_GUID,
142 X_SYSTEM_GUID,
143 X_SOURCE_TYPE,
144 X_SOURCE_AGENT_GUID,
145 X_EVENT_FILTER_GUID,
146 X_PHASE,
147 X_STATUS,
148 X_RULE_DATA,
149 X_OUT_AGENT_GUID,
150 X_TO_AGENT_GUID,
151 X_PRIORITY,
152 l_rule_func,
153 X_WF_PROCESS_TYPE,
154 X_WF_PROCESS_NAME,
155 X_PARAMETERS,
156 X_OWNER_NAME,
157 X_OWNER_TAG,
158 X_CUSTOMIZATION_LEVEL,
159 l_licensed_flag,
160 X_DESCRIPTION,
161 X_EXPRESSION,
162 X_ACTION_CODE,
163 X_ON_ERROR_CODE,
164 X_JAVA_RULE_FUNC,
165 X_MAP_CODE,
166 X_STANDARD_CODE,
167 X_STANDARD_TYPE
168 from dual where not exists (
169 select 'duplicate'
170 from wf_event_subscriptions
171 where guid = X_GUID);
172 end if;
173
174 open c;
175 fetch c into X_ROWID;
176 if (c%notfound) then
177 close c;
178 raise no_data_found;
179 else
180 wf_event.raise('oracle.apps.wf.event.subscription.create', x_guid);
181 end if;
182 close c;
183
184 exception
185 when others then
186 wf_core.context('Wf_Events_Subscriptions_Pkg', 'Insert_Row', x_guid,
187 x_system_guid, X_SOURCE_TYPE, X_SOURCE_AGENT_GUID);
188 raise;
189
190 end INSERT_ROW;
191 ----------------------------------------------------------------------------
192 procedure UPDATE_ROW (
193 X_GUID in raw,
194 X_SYSTEM_GUID in raw,
195 X_SOURCE_TYPE in varchar2,
196 X_SOURCE_AGENT_GUID in raw,
197 X_EVENT_FILTER_GUID in raw,
198 X_PHASE in number,
199 X_STATUS in varchar2,
200 X_RULE_DATA in varchar2,
201 X_OUT_AGENT_GUID in raw,
202 X_TO_AGENT_GUID in raw,
203 X_PRIORITY in number,
204 X_RULE_FUNCTION in varchar2,
205 X_WF_PROCESS_TYPE in varchar2,
206 X_WF_PROCESS_NAME in varchar2,
207 X_PARAMETERS in varchar2,
208 X_OWNER_NAME in varchar2,
209 X_OWNER_TAG in varchar2,
210 X_CUSTOMIZATION_LEVEL in varchar2,
211 X_LICENSED_FLAG in varchar2,
212 X_DESCRIPTION in varchar2,
213 X_EXPRESSION in varchar2,
214 X_ACTION_CODE in varchar2,
215 X_ON_ERROR_CODE in varchar2,
216 X_JAVA_RULE_FUNC in varchar2,
217 X_MAP_CODE in varchar2,
218 X_STANDARD_CODE in varchar2,
219 X_STANDARD_TYPE in varchar2
220 ) is
221 l_custom_level varchar2(1);
222 l_update_allowed varchar2(1) := 'Y';
223 l_licensed_flag varchar2(1) := 'N';
224 l_raise_event_flag varchar2(1) := 'N';
225 l_guid raw(16);
226 l_event_name varchar2(240);
227 l_rule_func VARCHAR2(240);
228 begin
229 validate_subscription (X_EVENT_FILTER_GUID,
230 X_CUSTOMIZATION_LEVEL,
231 X_STATUS); -- Bug 2756800
232
233 l_licensed_flag := WF_EVENTS_PKG.is_product_licensed (X_OWNER_TAG);
234 if (X_RULE_FUNCTION is null and X_JAVA_RULE_FUNC is null) then
235 l_rule_func := 'WF_RULE.DEFAULT_RULE';
236 elsif (x_rule_function is not null) then
237 l_rule_func := x_rule_function;
238 end if;
239
240 -- Check if the subscription is duplicate.
241 l_guid := Find_Subscription(x_subscription_guid => update_row.x_guid,
242 x_system_guid => update_row.x_system_guid,
243 x_source_type => update_row.x_source_type,
244 x_source_agent_guid => update_row.x_source_agent_guid,
245 x_event_filter_guid => update_row.x_event_filter_guid,
246 x_phase => update_row.x_phase,
247 x_rule_data => update_row.x_rule_data,
248 x_priority => update_row.x_priority,
249 x_rule_function => update_row.x_rule_function,
250 x_wf_process_type => update_row.x_wf_process_type,
251 x_wf_process_name => update_row.x_wf_process_name,
252 x_parameters => update_row.x_parameters,
253 x_owner_name => update_row.x_owner_name,
254 x_owner_tag => update_row.x_owner_tag);
255
256 if (l_guid <> x_guid) then
257 -- If l_guid is not same as x_guid, we already have a subscription with same information.
258 -- Throw an error to the UI.
259 begin
260 SELECT name
261 INTO l_event_name
262 FROM wf_events
263 WHERE guid = x_event_filter_guid;
264 exception
265 when no_data_found then
266 null;
267 end;
268 Wf_Core.Context('Wf_Event_Subscriptions_Pkg', 'Update_Row');
269 Wf_Core.Token('EVENT', l_event_name);
270 Wf_Core.Token('SOURCE', x_source_type);
271 Wf_Core.Token('PHASE', x_phase);
272 Wf_Core.Token('OWNERNAME', x_owner_name);
273 Wf_Core.Token('OWNERTAG', x_owner_tag);
274 Wf_Core.Raise('WFE_DUPLICATE_SUB');
275 end if;
276
277 if WF_EVENTS_PKG.g_Mode = 'FORCE' then
278 update wf_event_subscriptions set
279 system_guid = X_SYSTEM_GUID,
280 source_type = X_SOURCE_TYPE,
281 source_agent_guid = X_SOURCE_AGENT_GUID,
282 event_filter_guid = X_EVENT_FILTER_GUID,
283 phase = X_PHASE,
284 status = X_STATUS,
285 rule_data = X_RULE_DATA,
286 out_agent_guid = X_OUT_AGENT_GUID,
287 to_agent_guid = X_TO_AGENT_GUID,
288 priority = X_PRIORITY,
289 rule_function = l_rule_func,
290 wf_process_type = X_WF_PROCESS_TYPE,
291 wf_process_name = X_WF_PROCESS_NAME,
292 parameters = X_PARAMETERS,
293 owner_name = X_OWNER_NAME,
294 owner_tag = X_OWNER_TAG,
295 description = X_DESCRIPTION,
296 customization_level = X_CUSTOMIZATION_LEVEL,
297 licensed_flag = l_licensed_flag,
298 expression = X_EXPRESSION,
299 action_code = X_ACTION_CODE,
300 on_error_code = X_ON_ERROR_CODE,
301 java_rule_func = X_JAVA_RULE_FUNC,
302 map_code = X_MAP_CODE,
303 standard_code = X_STANDARD_CODE,
304 standard_type = X_STANDARD_TYPE
305 where guid = X_GUID;
306
307 if (sql%notfound) then
308 raise no_data_found;
309 else
310 wf_event.raise('oracle.apps.wf.event.subscription.update', X_GUID);
311 end if;
312
313 else
314 -- User logged in is not seed
315 fetch_custom_level(X_GUID, l_custom_level);
316 l_update_allowed := WF_EVENTS_PKG.is_update_allowed(X_CUSTOMIZATION_LEVEL, l_custom_level);
317
318 if l_update_allowed = 'N' then
319 -- Set up the Error Stack
320 wf_core.context('WF_EVENT_SUBSCRIPTIONS_PKG','UPDATE_ROW',
321 X_EVENT_FILTER_GUID,
322 l_custom_level,
323 X_CUSTOMIZATION_LEVEL);
324 return;
325 end if;
326
327 if X_CUSTOMIZATION_LEVEL = 'C'then
328 if WF_EVENTS_PKG.g_Mode = 'UPGRADE' then
329 -- Here are the updates allowed when the caller is the Loader
330 update wf_event_subscriptions set
331 system_guid = X_SYSTEM_GUID,
332 source_type = X_SOURCE_TYPE,
333 source_agent_guid = X_SOURCE_AGENT_GUID,
334 event_filter_guid = X_EVENT_FILTER_GUID,
335 phase = X_PHASE,
336 status = X_STATUS,
337 rule_data = X_RULE_DATA,
338 out_agent_guid = X_OUT_AGENT_GUID,
339 to_agent_guid = X_TO_AGENT_GUID,
340 priority = X_PRIORITY,
341 rule_function = l_rule_func,
342 wf_process_type = X_WF_PROCESS_TYPE,
343 wf_process_name = X_WF_PROCESS_NAME,
344 parameters = X_PARAMETERS,
345 owner_name = X_OWNER_NAME,
346 owner_tag = X_OWNER_TAG,
347 description = X_DESCRIPTION,
348 customization_level = X_CUSTOMIZATION_LEVEL,
349 licensed_flag = l_licensed_flag,
350 expression = X_EXPRESSION,
351 action_code = X_ACTION_CODE,
352 on_error_code = X_ON_ERROR_CODE,
353 java_rule_func = X_JAVA_RULE_FUNC,
354 map_code = X_MAP_CODE,
355 standard_code = X_STANDARD_CODE,
356 standard_type = X_STANDARD_TYPE
357 where guid = X_GUID;
358
359 l_raise_event_flag := 'Y';
360 else
361 -- UI users cannot update Core events
362 null;
363 -- vshanmug - Expression is temporarily used to store custom ws-security settings
364 -- expression column is not used in BES
365 update wf_event_subscriptions
366 set expression = X_EXPRESSION
367 where guid = X_GUID;
368
369 l_raise_event_flag := 'Y';
370
371 end if;
372 elsif X_CUSTOMIZATION_LEVEL = 'L' then
373 if WF_EVENTS_PKG.g_Mode = 'UPGRADE' then
374 -- Limit events can have only a status change..
375 -- When the loader is loading the events the
376 -- users changes must be preserved. Update all
377 -- fields EXCEPT the status field.
378 update wf_event_subscriptions set
379 system_guid = X_SYSTEM_GUID,
380 source_type = X_SOURCE_TYPE,
381 source_agent_guid = X_SOURCE_AGENT_GUID,
382 event_filter_guid = X_EVENT_FILTER_GUID,
383 phase = X_PHASE,
384 rule_data = X_RULE_DATA,
385 out_agent_guid = X_OUT_AGENT_GUID,
386 to_agent_guid = X_TO_AGENT_GUID,
387 priority = X_PRIORITY,
388 rule_function = l_rule_func,
389 wf_process_type = X_WF_PROCESS_TYPE,
390 wf_process_name = X_WF_PROCESS_NAME,
391 parameters = X_PARAMETERS,
392 owner_name = X_OWNER_NAME,
393 owner_tag = X_OWNER_TAG,
394 description = X_DESCRIPTION,
395 customization_level = X_CUSTOMIZATION_LEVEL,
396 licensed_flag = l_licensed_flag,
397 expression = X_EXPRESSION,
398 action_code = X_ACTION_CODE,
399 on_error_code = X_ON_ERROR_CODE,
400 java_rule_func = X_JAVA_RULE_FUNC,
401 map_code = X_MAP_CODE,
402 standard_code = X_STANDARD_CODE,
403 standard_type = X_STANDARD_TYPE
404 where guid = X_GUID;
405
406 l_raise_event_flag := 'Y';
407
408 else -- Caller of the Update is UI
409 -- Limit events can have only a status change..
410 -- When the user is updating the event using the UI
411 -- Updates are allowed ONLY to the status field.
412
413 -- vshanmug - Expression is temporarily used to store custom ws-security settings
414 -- expression column is not used in BES
415 update wf_event_subscriptions set
416 status = X_STATUS,
417 licensed_flag = l_licensed_flag,
418 expression = X_EXPRESSION
419 where guid = X_GUID;
420
421 l_raise_event_flag := 'Y';
422
423 end if;
424
425 elsif X_CUSTOMIZATION_LEVEL = 'U' then
426 -- Here are the updates allowed for extensible and User defined events
427 -- only when the caller is the UI
428
429 if WF_EVENTS_PKG.g_Mode = 'CUSTOM' then
430 update wf_event_subscriptions set
431 system_guid = X_SYSTEM_GUID,
432 source_type = X_SOURCE_TYPE,
433 source_agent_guid = X_SOURCE_AGENT_GUID,
434 event_filter_guid = X_EVENT_FILTER_GUID,
435 phase = X_PHASE,
436 status = X_STATUS,
437 rule_data = X_RULE_DATA,
438 out_agent_guid = X_OUT_AGENT_GUID,
439 to_agent_guid = X_TO_AGENT_GUID,
440 priority = X_PRIORITY,
441 rule_function = l_rule_func,
442 wf_process_type = X_WF_PROCESS_TYPE,
443 wf_process_name = X_WF_PROCESS_NAME,
444 parameters = X_PARAMETERS,
445 owner_name = X_OWNER_NAME,
446 owner_tag = X_OWNER_TAG,
447 description = X_DESCRIPTION,
448 customization_level = X_CUSTOMIZATION_LEVEL,
449 licensed_flag = l_licensed_flag,
450 expression = X_EXPRESSION,
451 action_code = X_ACTION_CODE,
452 on_error_code = X_ON_ERROR_CODE,
453 java_rule_func = X_JAVA_RULE_FUNC,
454 map_code = X_MAP_CODE,
455 standard_code = X_STANDARD_CODE,
456 standard_type = X_STANDARD_TYPE
457 where guid = X_GUID;
458
459 l_raise_event_flag := 'Y';
460 else
461 -- The caller is Loader and the only way of
462 -- Uploading the data is in FORCE mode
463 null;
464 end if;
465 else
466 -- Raise error..
467 Wf_Core.Token('REASON','Invalid Customization Level:' ||
468 l_custom_level);
469 Wf_Core.Raise('WFSQL_INTERNAL');
470 end if;
471
472 -- Only raise if all if no raise_event_flag is set to 'Y'
473 -- fetch_custom_level will raise no_data_found if the subscription is not found
474 if (l_raise_event_flag = 'Y') then
475 wf_event.raise('oracle.apps.wf.event.subscription.update', X_GUID);
476 end if;
477
478 end if;
479
480 -- Invalidate cache
481 wf_bes_cache.SetMetaDataUploaded();
482 end UPDATE_ROW;
483 -----------------------------------------------------------------------------
484 procedure LOAD_ROW (
485 X_GUID in raw,
486 X_SYSTEM_GUID in raw,
487 X_SOURCE_TYPE in varchar2,
488 X_SOURCE_AGENT_GUID in raw,
489 X_EVENT_FILTER_GUID in raw,
490 X_PHASE in number,
491 X_STATUS in varchar2,
492 X_RULE_DATA in varchar2,
493 X_OUT_AGENT_GUID in raw,
494 X_TO_AGENT_GUID in raw,
495 X_PRIORITY in number,
496 X_RULE_FUNCTION in varchar2,
497 X_WF_PROCESS_TYPE in varchar2,
498 X_WF_PROCESS_NAME in varchar2,
499 X_PARAMETERS in varchar2,
500 X_OWNER_NAME in varchar2,
501 X_OWNER_TAG in varchar2,
502 X_CUSTOMIZATION_LEVEL in varchar2,
503 X_LICENSED_FLAG in varchar2,
504 X_DESCRIPTION in varchar2,
505 X_EXPRESSION in varchar2,
506 X_ACTION_CODE in varchar2,
507 X_ON_ERROR_CODE in varchar2,
508 X_JAVA_RULE_FUNC in varchar2,
509 X_MAP_CODE in varchar2,
510 X_STANDARD_CODE in varchar2,
511 X_STANDARD_TYPE in varchar2
512 ) is
513 row_id varchar2(64);
514 begin
515 begin
516 WF_EVENT_SUBSCRIPTIONS_PKG.UPDATE_ROW (
517 X_GUID => X_GUID,
518 X_SYSTEM_GUID => X_SYSTEM_GUID,
519 X_SOURCE_TYPE => X_SOURCE_TYPE,
520 X_SOURCE_AGENT_GUID => X_SOURCE_AGENT_GUID,
521 X_EVENT_FILTER_GUID => X_EVENT_FILTER_GUID,
522 X_PHASE => X_PHASE,
523 X_STATUS => X_STATUS,
524 X_RULE_DATA => X_RULE_DATA,
525 X_OUT_AGENT_GUID => X_OUT_AGENT_GUID,
526 X_TO_AGENT_GUID => X_TO_AGENT_GUID,
527 X_PRIORITY => X_PRIORITY,
528 X_RULE_FUNCTION => X_RULE_FUNCTION,
529 X_WF_PROCESS_TYPE => X_WF_PROCESS_TYPE,
530 X_WF_PROCESS_NAME => X_WF_PROCESS_NAME,
531 X_PARAMETERS => X_PARAMETERS,
532 X_OWNER_NAME => X_OWNER_NAME,
533 X_OWNER_TAG => X_OWNER_TAG,
534 X_CUSTOMIZATION_LEVEL => X_CUSTOMIZATION_LEVEL,
535 X_LICENSED_FLAG => X_LICENSED_FLAG,
536 X_DESCRIPTION => X_DESCRIPTION,
537 X_EXPRESSION => X_EXPRESSION,
538 X_ACTION_CODE => X_ACTION_CODE,
539 X_ON_ERROR_CODE => X_ON_ERROR_CODE,
540 X_JAVA_RULE_FUNC => X_JAVA_RULE_FUNC,
541 X_MAP_CODE => X_MAP_CODE,
542 X_STANDARD_CODE => X_STANDARD_CODE,
543 X_STANDARD_TYPE => X_STANDARD_TYPE
544 );
545 exception
546 when no_data_found then
547 WF_EVENT_SUBSCRIPTIONS_PKG.INSERT_ROW(
548 X_ROWID => row_id,
549 X_GUID => X_GUID,
550 X_SYSTEM_GUID => X_SYSTEM_GUID,
551 X_SOURCE_TYPE => X_SOURCE_TYPE,
552 X_SOURCE_AGENT_GUID => X_SOURCE_AGENT_GUID,
553 X_EVENT_FILTER_GUID => X_EVENT_FILTER_GUID,
554 X_PHASE => X_PHASE,
555 X_STATUS => X_STATUS,
556 X_RULE_DATA => X_RULE_DATA,
557 X_OUT_AGENT_GUID => X_OUT_AGENT_GUID,
558 X_TO_AGENT_GUID => X_TO_AGENT_GUID,
559 X_PRIORITY => X_PRIORITY,
560 X_RULE_FUNCTION => X_RULE_FUNCTION,
561 X_WF_PROCESS_TYPE => X_WF_PROCESS_TYPE,
562 X_WF_PROCESS_NAME => X_WF_PROCESS_NAME,
563 X_PARAMETERS => X_PARAMETERS,
564 X_OWNER_NAME => X_OWNER_NAME,
565 X_OWNER_TAG => X_OWNER_TAG,
566 X_CUSTOMIZATION_LEVEL => X_CUSTOMIZATION_LEVEL,
567 X_LICENSED_FLAG => X_LICENSED_FLAG,
568 X_DESCRIPTION => X_DESCRIPTION,
569 X_EXPRESSION => X_EXPRESSION,
570 X_ACTION_CODE => X_ACTION_CODE,
571 X_ON_ERROR_CODE => X_ON_ERROR_CODE,
572 X_JAVA_RULE_FUNC => X_JAVA_RULE_FUNC,
573 X_MAP_CODE => X_MAP_CODE,
574 X_STANDARD_CODE => X_STANDARD_CODE,
575 X_STANDARD_TYPE => X_STANDARD_TYPE
576 );
577 end;
578
579 -- Invalidate cache
580 wf_bes_cache.SetMetaDataUploaded();
581 exception
582 when others then
583 wf_core.context('Wf_Event_Subscriptions_Pkg', 'Load_Row', x_guid,
584 x_source_type, X_SOURCE_AGENT_GUID);
585 raise;
586 end LOAD_ROW;
587 -----------------------------------------------------------------------------
588 procedure DELETE_ROW (X_GUID in raw) is
589 begin
590 wf_event.raise('oracle.apps.wf.event.subscription.delete',x_guid);
591
592 delete from wf_event_subscriptions
593 where guid = X_GUID;
594
595 if (sql%notfound) then
596 raise no_data_found;
597 end if;
598
599 -- Invalidate cache
600 wf_bes_cache.SetMetaDataUploaded();
601 exception
602 when others then
603 wf_core.context('Wf_Events_Subscriptions_Pkg', 'Delete_Row', x_guid);
604 raise;
605 end DELETE_ROW;
606 ----------------------------------------------------------------------------
607 procedure DELETE_SET (
608 X_SYSTEM_GUID in raw,
609 X_SOURCE_TYPE in varchar2,
610 X_SOURCE_AGENT_GUID in raw,
611 X_EVENT_FILTER_GUID in raw,
612 X_PHASE in number,
613 X_STATUS in varchar2,
614 X_RULE_DATA in varchar2,
615 X_OUT_AGENT_GUID in raw,
616 X_TO_AGENT_GUID in raw,
617 X_PRIORITY in number,
618 X_RULE_FUNCTION in varchar2,
619 X_WF_PROCESS_TYPE in varchar2,
620 X_WF_PROCESS_NAME in varchar2,
621 X_PARAMETERS in varchar2,
622 X_OWNER_NAME in varchar2,
623 X_OWNER_TAG in varchar2,
624 X_DESCRIPTION in varchar2,
625 X_EXPRESSION in varchar2,
626 X_ACTION_CODE in varchar2,
627 X_ON_ERROR_CODE in varchar2,
628 X_JAVA_RULE_FUNC in varchar2,
629 X_MAP_CODE in varchar2,
630 X_STANDARD_CODE in varchar2,
631 X_STANDARD_TYPE in varchar2
632 ) is
633 begin
634 delete from wf_event_subscriptions
635 where (X_SYSTEM_GUID is null or (X_SYSTEM_GUID is not null
636 and system_guid like X_SYSTEM_GUID))
637 and (X_SOURCE_TYPE is null or (X_SOURCE_TYPE is not null
638 and source_type like X_SOURCE_TYPE))
639 and (X_SOURCE_AGENT_GUID is null or (X_SOURCE_AGENT_GUID is not null
640 and source_agent_guid like X_SOURCE_AGENT_GUID))
641 and (X_EVENT_FILTER_GUID is null or (X_EVENT_FILTER_GUID is not null
642 and event_filter_guid like X_EVENT_FILTER_GUID))
643 and (X_PHASE is null or (X_PHASE is not null
644 and phase like X_PHASE))
645 and (X_STATUS is null or (X_STATUS is not null
646 and status like X_STATUS))
647 and (X_RULE_DATA is null or (X_RULE_DATA is not null
648 and rule_data like X_RULE_DATA))
649 and (X_OUT_AGENT_GUID is null or (X_OUT_AGENT_GUID is not null
650 and out_agent_guid like X_OUT_AGENT_GUID))
651 and (X_TO_AGENT_GUID is null or (X_TO_AGENT_GUID is not null
652 and to_agent_guid like X_TO_AGENT_GUID))
653 and (X_PRIORITY is null or (X_PRIORITY is not null
654 and priority like X_PRIORITY))
655 and (X_RULE_FUNCTION is null or (X_RULE_FUNCTION is not null
656 and rule_function like X_RULE_FUNCTION))
657 and (X_WF_PROCESS_TYPE is null or (X_WF_PROCESS_TYPE is not null
658 and wf_process_type like X_WF_PROCESS_TYPE))
659 and (X_WF_PROCESS_NAME is null or (X_WF_PROCESS_NAME is not null
660 and wf_process_name like X_WF_PROCESS_NAME))
661 and (X_PARAMETERS is null or (X_PARAMETERS is not null
662 and parameters like X_PARAMETERS))
663 and (X_OWNER_NAME is null or (X_OWNER_NAME is not null
664 and owner_name like X_OWNER_NAME))
665 and (X_OWNER_TAG is null or (X_OWNER_TAG is not null
666 and owner_tag like X_OWNER_TAG))
667 and (X_DESCRIPTION is null or (X_DESCRIPTION is not null
668 and description like X_DESCRIPTION))
669 and (X_EXPRESSION is null or (X_EXPRESSION is not null
670 and expression like X_EXPRESSION))
671 and (X_ACTION_CODE is null or (X_ACTION_CODE is not null
672 and action_code like X_ACTION_CODE))
673 and (X_ON_ERROR_CODE is null or (X_ON_ERROR_CODE is not null
674 and on_error_code like X_ON_ERROR_CODE))
675 and (X_JAVA_RULE_FUNC is null or (X_JAVA_RULE_FUNC is not null
676 and java_rule_func like X_JAVA_RULE_FUNC))
677 and (X_MAP_CODE is null or (X_MAP_CODE is not null
678 and map_code like X_MAP_CODE))
679 and (X_STANDARD_CODE is null or (X_STANDARD_CODE is not null
680 and standard_code like X_STANDARD_CODE))
681 and (X_STANDARD_TYPE is null or (X_STANDARD_TYPE is not null
682 and standard_type like X_STANDARD_TYPE));
683
684 -- Invalidate cache
685 wf_bes_cache.SetMetaDataUploaded();
686 exception
687 when others then
688 wf_core.context('Wf_Events_Subscriptions_Pkg', 'Delete_Set',
689 x_system_guid, X_source_type, X_Event_Filter_GUID);
690 raise;
691 end DELETE_SET;
692 ----------------------------------------------------------------------------
693 function GENERATE (
694 X_GUID in raw
695 ) return varchar2 is
696 buf varchar2(32000);
697 l_doc xmldom.DOMDocument;
698 l_element xmldom.DOMElement;
699 l_root xmldom.DOMNode;
700 l_node xmldom.DOMNode;
701 l_header xmldom.DOMNode;
702
703 l_guid raw(16);
704 l_system_guid raw(16);
705 l_source_type varchar2(80);
706 l_source_agent_guid raw(16);
707 l_event_filter_name varchar2(240);
708 l_phase number;
709 l_status varchar2(8);
710 l_rule_data varchar2(8);
711 l_out_agent_guid raw(16);
712 l_to_agent_guid raw(16);
713 l_priority number;
714 l_rule_function varchar2(240);
715 l_wf_process_type varchar2(30);
716 l_wf_process_name varchar2(30);
717 l_parameters varchar2(4000);
718 l_owner_name varchar2(30);
719 l_owner_tag varchar2(30);
720 l_customization_level varchar2(1);
721 l_licensed_flag varchar2(1);
722 l_description varchar2(240);
723 l_version varchar2(80);
724 l_expression varchar2(4000);
725
726 --Bug 3328673
727 --JBES Support for loader
728 l_standardtype varchar2(30);
729 l_standardcode varchar2(30);
730 l_javarulefunc varchar2(240);
731 l_onerror varchar2(30);
732 l_actioncode varchar2(30);
733 begin
734
735 select s.system_guid, s.source_type, s.source_agent_guid,
736 e.name, s.phase, s.status, s.rule_data,
737 s.out_agent_guid, s.to_agent_guid, s.priority,
738 s.rule_function, s.wf_process_type, s.wf_process_name,
739 s.parameters, s.owner_name, s.owner_tag, s.description, s.expression,
740 nvl(s.customization_level, 'L'), nvl(s.licensed_flag, 'Y'),
741 s.standard_type , s.standard_code , s.java_rule_func , s.on_error_code,
742 s.action_code
743 into l_system_guid, l_source_type, l_source_agent_guid,
744 l_event_filter_name, l_phase, l_status, l_rule_data,
745 l_out_agent_guid, l_to_agent_guid, l_priority,
746 l_rule_function, l_wf_process_type, l_wf_process_name,
747 l_parameters, l_owner_name, l_owner_tag, l_description, l_expression,
748 l_customization_level, l_licensed_flag,l_standardtype,l_standardcode,
749 l_javarulefunc,l_onerror , l_actioncode
750 from wf_event_subscriptions s, wf_events e
751 where s.guid = x_guid
752 and e.guid = s.event_filter_guid;
753
754 l_doc := xmldom.newDOMDocument;
755 l_root := xmldom.makeNode(l_doc);
756 l_root := wf_event_xml.newtag (l_doc, l_root, wf_event_xml.masterTagName);
757 l_header := wf_event_xml.newtag(l_doc, l_root, m_table_name);
758 l_node := wf_event_xml.newtag(l_doc, l_header, wf_event_xml.versionTagName,
759 m_package_version);
760 l_node := wf_event_xml.newtag(l_doc, l_header, 'GUID',
761 rawtohex(x_guid));
762 l_node := wf_event_xml.newtag(l_doc, l_header, 'SYSTEM_GUID',
763 rawtohex(l_system_guid));
764 l_node := wf_event_xml.newtag(l_doc, l_header, 'SOURCE_TYPE',
765 l_source_type);
766 l_node := wf_event_xml.newtag(l_doc, l_header, 'SOURCE_AGENT_GUID',
767 rawtohex(l_source_agent_guid));
768 l_node := wf_event_xml.newtag(l_doc, l_header, 'EVENT_FILTER_GUID', l_event_filter_name);
769
770 l_node := wf_event_xml.newtag(l_doc, l_header, 'PHASE',
771 l_phase);
772 l_node := wf_event_xml.newtag(l_doc, l_header, 'STATUS',
773 l_status);
774 l_node := wf_event_xml.newtag(l_doc, l_header, 'RULE_DATA',
775 l_rule_data);
776 l_node := wf_event_xml.newtag(l_doc, l_header, 'OUT_AGENT_GUID',
777 rawtohex(l_out_agent_guid));
778 l_node := wf_event_xml.newtag(l_doc, l_header, 'TO_AGENT_GUID',
779 rawtohex(l_to_agent_guid));
780 l_node := wf_event_xml.newtag(l_doc, l_header, 'PRIORITY',
781 l_PRIORITY);
782 l_node := wf_event_xml.newtag(l_doc, l_header, 'RULE_FUNCTION',
783 l_RULE_FUNCTION);
784 --Bug 3328673
785 --Add new tags for JBES support
786 l_node := wf_event_xml.newtag(l_doc, l_header, 'JAVA_RULE_FUNC',
787 l_javarulefunc);
788 l_node := wf_event_xml.newtag(l_doc, l_header, 'STANDARD_TYPE',
789 l_standardtype);
790 l_node := wf_event_xml.newtag(l_doc, l_header, 'STANDARD_CODE',
791 l_standardcode);
792 l_node := wf_event_xml.newtag(l_doc, l_header, 'ON_ERROR_CODE',
793 l_onerror);
794 l_node := wf_event_xml.newtag(l_doc, l_header, 'ACTION_CODE',
795 l_actioncode);
796
797 l_node := wf_event_xml.newtag(l_doc, l_header, 'WF_PROCESS_TYPE',
798 l_wf_process_type);
799 l_node := wf_event_xml.newtag(l_doc, l_header, 'WF_PROCESS_NAME',
800 l_wf_process_name);
801 l_node := wf_event_xml.newtag(l_doc, l_header, 'PARAMETERS',
802 l_PARAMETERS);
803 l_node := wf_event_xml.newtag(l_doc, l_header, 'OWNER_NAME',
804 l_OWNER_NAME);
805 l_node := wf_event_xml.newtag(l_doc, l_header, 'OWNER_TAG',
806 l_OWNER_TAG);
807 l_node := wf_event_xml.newtag(l_doc, l_header, 'CUSTOMIZATION_LEVEL',
808 NVL(l_customization_level, 'L'));
809 l_node := wf_event_xml.newtag(l_doc, l_header, 'LICENSED_FLAG',
810 NVL(l_licensed_flag, 'Y'));
811 l_node := wf_event_xml.newtag(l_doc, l_header, 'DESCRIPTION',
812 l_DESCRIPTION);
813 l_node := wf_event_xml.newtag(l_doc, l_header, 'EXPRESSION',
814 l_EXPRESSION);
815 xmldom.writeToBuffer(l_root, buf);
816
817 return buf;
818 exception
819 when others then
820 wf_core.context('Wf_Events_Subscriptions_Pkg', 'Generate', x_guid);
821 raise;
822 end GENERATE;
823 -----------------------------------------------------------------------------
824 procedure RECEIVE (
825 X_MESSAGE in varchar2
826 ) is
827 l_guid raw(16);
828 l_system_guid raw(16);
829 l_source_type varchar2(80);
830 l_source_agent_guid raw(16);
831 l_event_filter_guid raw(16);
832 l_phase number;
833 l_status varchar2(8);
834 l_rule_data varchar2(8);
835 l_out_agent_guid raw(16);
836 l_to_agent_guid raw(16);
837 l_priority number;
838 l_rule_function varchar2(240);
839 l_wf_process_type varchar2(30);
840 l_wf_process_name varchar2(30);
841 l_parameters varchar2(4000);
842 l_owner_name varchar2(30);
843 l_owner_tag varchar2(30);
844 l_description varchar2(240);
845 l_version varchar2(80);
846 l_message varchar2(32000);
847 l_customization_level varchar2(1) := 'L';
848 l_licensed_flag varchar2(1) := 'Y';
849 l_subscription_guid varchar2(32);
850 l_expression varchar2(4000);
851
852 l_node_name varchar2(255);
853 l_node xmldom.DOMNode;
854 l_child xmldom.DOMNode;
855 l_value varchar2(32000);
856 l_length integer;
857 l_node_list xmldom.DOMNodeList;
858
859 l_num number;
860 --Bug 3328673
861 --JBES Support for loader
862 l_standardtype varchar2(30);
863 l_standardcode varchar2(30);
864 l_javarulefunc varchar2(240);
865 l_onerror varchar2(30);
866 l_actioncode varchar2(30);
867
868 -- Identical Row checks from this procedure are now moved to Find_Subscription
869 begin
870
871 l_message := x_message;
872 --l_message := WF_EVENT_SYNCHRONIZE_PKG.SetGUID(l_message); -- update #NEW
873 l_message := WF_EVENT_SYNCHRONIZE_PKG.SetSYSTEMGUID(l_message); -- update #LOCAL
874 l_message := WF_EVENT_SYNCHRONIZE_PKG.SetSID(l_message); -- update #SID
875 --Bug 3191978
876 --Replace agent names by their GUIDs
877 l_message := WF_EVENT_SYNCHRONIZE_PKG.SetAgent2('<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>',l_message); -- update #WF_IN, #WF_OUT, #WF_ERROR
878 l_message := WF_EVENT_SYNCHRONIZE_PKG.SetAgent2('<TO_AGENT_GUID>','</TO_AGENT_GUID>',l_message);
879 l_message := WF_EVENT_SYNCHRONIZE_PKG.SetAgent2('<OUT_AGENT_GUID>','</OUT_AGENT_GUID>',l_message);
880 l_node_list := wf_event_xml.findTable(l_message, m_table_name);
881 l_length := xmldom.getLength(l_node_list);
882
883 -- loop through elements that we received.
884 for i in 0..l_length-1 loop
885 l_node := xmldom.item(l_node_list, i);
886 l_node_name := xmldom.getNodeName(l_node);
887 if xmldom.hasChildNodes(l_node) then
888 l_child := xmldom.GetFirstChild(l_node);
889 l_value := xmldom.getNodevalue(l_child);
890 else
891 l_value := NULL;
892 end if;
893
894 if(l_node_name = 'GUID') then
895 --l_guid := l_value;
896 l_subscription_guid := l_value;
897 elsif(l_node_name = 'SYSTEM_GUID') then
898 l_SYSTEM_GUID := l_value;
899 elsif(l_node_name = 'SOURCE_TYPE') then
900 l_source_type := l_value;
901 elsif(l_node_name = 'SOURCE_AGENT_GUID') then
902 l_source_agent_guid := l_value;
903 elsif(l_node_name = 'EVENT_FILTER_GUID') then
904 -- Check if the value is event name, get the GUID
905 begin
906 SELECT guid
907 INTO l_event_filter_guid
908 FROM wf_events
909 WHERE name = l_value;
910 exception
911 when no_data_found then
912 -- Value is a event GUID (older wfx files)
913 l_event_filter_guid := l_value;
914 end;
915 elsif(l_node_name = 'PHASE') then
916 l_phase := to_number(l_value);
917 elsif(l_node_name = 'STATUS') then
918 l_status := l_value;
919 elsif(l_node_name = 'RULE_DATA') then
920 l_rule_data := l_value;
921 elsif(l_node_name = 'OUT_AGENT_GUID') then
922 l_out_agent_guid := l_value;
923 elsif(l_node_name = 'TO_AGENT_GUID') then
924 l_to_agent_guid := l_value;
925 elsif(l_node_name = 'PRIORITY') then
926 l_priority := to_number(l_value);
927 elsif(l_node_name = 'RULE_FUNCTION') then
928 l_rule_function := l_value;
929 elsif(l_node_name = 'WF_PROCESS_TYPE') then
930 l_wf_process_type := l_value;
931 elsif(l_node_name = 'WF_PROCESS_NAME') then
932 l_wf_process_name := l_value;
933 elsif(l_node_name = 'PARAMETERS') then
934 l_parameters := l_value;
935 elsif(l_node_name = 'OWNER_NAME') then
936 l_owner_name := l_value;
937 elsif(l_node_name = 'OWNER_TAG') then
938 l_owner_tag := l_value;
939 elsif(l_node_name = 'CUSTOMIZATION_LEVEL') then
940 l_customization_level := l_value;
941 elsif(l_node_name = 'LICENSED_FLAG') then
942 l_licensed_flag := l_value;
943 elsif(l_node_name = 'DESCRIPTION') then
944 l_description := l_value;
945 elsif(l_node_name = 'VERSION') then
946 l_version := l_value;
947 elsif(l_node_name = 'EXPRESSION') then
948 l_expression := l_value;
949 --Bug 3328673
950 --JBES Support for loader
951 elsif(l_node_name = 'JAVA_RULE_FUNC') then
952 l_javarulefunc := l_value;
953 elsif(l_node_name = 'STANDARD_TYPE') then
954 l_standardtype := l_value;
955 elsif(l_node_name = 'STANDARD_CODE') then
956 l_standardcode := l_value;
957 elsif(l_node_name = 'ON_ERROR_CODE') then
958 l_onerror := l_value;
959 elsif(l_node_name = 'ACTION_CODE') then
960 l_actioncode := l_value;
961 else
962 Wf_Core.Token('REASON', 'Invalid column name found:' ||
963 l_node_name || ' with value:'||l_value);
964 Wf_Core.Raise('WFSQL_INTERNAL');
965 end if;
966 end loop;
967
968 -- Validate Subscription
969 -- Phase must not be null
970 if L_PHASE is null then
971 -- For backward compatibility of the WFXLoad do not raise any errors when
972 -- the caller is the Loader. Throw a warning only
973 if WF_EVENTS_PKG.g_Mode <> 'UPGRADE' then
974 Wf_Core.Token('REASON','Subscription Phase cannot be null');
975 Wf_Core.Raise('WFSQL_INTERNAL');
976 else
977 wf_core.context('Wf_Events_Subscriptions_Pkg', 'Receive',
978 'WARNING! WARNING! Subscription Phase CANNOT be null for Event GUID '
979 || l_event_filter_guid || ' defaulting to 50');
980 l_Phase := 50;
981 end if;
982 end if;
983
984 -- Validate Subscription
985 -- Owner Name and Owner Tag must not be null
986 if (L_OWNER_NAME is null)
987 or (L_OWNER_TAG is null) then
988
989 -- For backward compatibility of the WFXLoad do not raise any errors when
990 -- the caller is the Loader. Throw a warning only
991 if WF_EVENTS_PKG.g_Mode <> 'UPGRADE' then
992 Wf_Core.Token('REASON','Subscription Owner Name and Owner Tag cannot be null');
993 Wf_Core.Raise('WFSQL_INTERNAL');
994 else
995 wf_core.context('Wf_Events_Subscriptions_Pkg', 'Receive',
996 'WARNING! WARNING! Subscription OWNER_NAME/OWNER_TAG CANNOT be null for Event GUID '
997 || l_event_filter_guid);
998 end if;
999 end if;
1000
1001 -- Check if the subscription is duplicate. If there is one already, use the GUID
1002 -- of the existing subscription
1003 l_guid := Find_Subscription(x_subscription_guid => l_subscription_guid,
1004 x_system_guid => l_system_guid,
1005 x_source_type => l_source_type,
1006 x_source_agent_guid => l_source_agent_guid,
1007 x_event_filter_guid => l_event_filter_guid,
1008 x_phase => l_phase,
1009 x_rule_data => l_rule_data,
1010 x_priority => l_priority,
1011 x_rule_function => l_rule_function,
1012 x_wf_process_type => l_wf_process_type,
1013 x_wf_process_name => l_wf_process_name,
1014 x_parameters => l_parameters,
1015 x_owner_name => l_owner_name,
1016 x_owner_tag => l_owner_tag);
1017
1018 wf_event_subscriptions_pkg.load_row(
1019 X_GUID => l_guid,
1020 X_SYSTEM_GUID => l_system_guid,
1021 X_SOURCE_TYPE => l_source_type,
1022 X_SOURCE_AGENT_GUID => l_source_agent_guid,
1023 X_EVENT_FILTER_GUID => l_event_filter_guid,
1024 X_PHASE => l_phase,
1025 X_STATUS => l_status,
1026 X_RULE_DATA => l_rule_data,
1027 X_OUT_AGENT_GUID => l_out_agent_guid,
1028 X_TO_AGENT_GUID => l_to_agent_guid,
1029 X_PRIORITY => l_priority,
1030 X_RULE_FUNCTION => l_rule_function,
1031 X_WF_PROCESS_TYPE => l_wf_process_type,
1032 X_WF_PROCESS_NAME => l_wf_process_name,
1033 X_PARAMETERS => l_parameters,
1034 X_OWNER_NAME => l_owner_name,
1035 X_OWNER_TAG => l_owner_tag,
1036 X_CUSTOMIZATION_LEVEL => l_customization_level,
1037 X_LICENSED_FLAG => l_licensed_flag,
1038 X_DESCRIPTION => l_description,
1039 X_EXPRESSION => l_expression,
1040 X_ACTION_CODE => l_actioncode,
1041 X_ON_ERROR_CODE => l_onerror ,
1042 X_JAVA_RULE_FUNC => l_javarulefunc,
1043 X_STANDARD_CODE => l_standardcode,
1044 X_STANDARD_TYPE => l_standardtype
1045 );
1046
1047 exception
1048 when others then
1049 wf_core.context('Wf_Events_Subscriptions_Pkg', 'Receive', x_message);
1050 raise;
1051 end RECEIVE;
1052 -----------------------------------------------------------------------------
1053 procedure validate_subscription (X_EVENT_FILTER_GUID in raw,
1054 X_CUSTOMIZATION_LEVEL in varchar2,
1055 X_STATUS in varchar2) -- Bug 2756800
1056 is
1057
1058 cursor c_geteventcustom is
1059 select customization_level
1060 from wf_events
1061 where guid = X_EVENT_FILTER_GUID;
1062
1063 l_custom_level varchar2(1);
1064 l_trns1 varchar2(4000);
1065 l_trns2 varchar2(4000);
1066 e_invalid_sub exception;
1067 begin
1068
1069 for v_getcustom in c_geteventcustom loop
1070 l_custom_level := v_getcustom.customization_level;
1071 end loop;
1072
1073 -- Subscription Validity Matrix
1074
1075 if X_CUSTOMIZATION_LEVEL in ('C', 'L') then
1076 if l_custom_level in ('X', 'U') then
1077 raise e_invalid_sub;
1078 end if;
1079 if X_CUSTOMIZATION_LEVEL = 'C' and l_custom_level = 'L' and
1080 X_STATUS <> 'DISABLED' -- Bug 2756800
1081 then
1082 raise e_invalid_sub;
1083 end if;
1084 elsif X_CUSTOMIZATION_LEVEL = 'X' then
1085 if l_custom_level = 'U' then
1086 raise e_invalid_sub;
1087 end if;
1088 end if;
1089 exception
1090 when e_invalid_sub then
1091 l_trns1 := wf_core.translate('WFE_CUSTOM_LEVEL_' || X_CUSTOMIZATION_LEVEL);
1092 l_trns2 := wf_core.translate('WFE_CUSTOM_LEVEL_' || l_custom_level);
1093 wf_core.token('SUB_CUSTOM_LEVEL', l_trns1);
1094 wf_core.token('EVT_CUSTOM_LEVEL', l_trns2);
1095 wf_core.raise('WFE_INVALID_SUBSCRIPTION');
1096 end validate_subscription;
1097
1098 procedure fetch_custom_level(X_GUID in raw,
1099 X_CUSTOMIZATION_LEVEL out nocopy varchar2)
1100 is
1101 cursor c_getCustomLevel is
1102 select CUSTOMIZATION_LEVEL from
1103 WF_EVENT_SUBSCRIPTIONS
1104 where guid = X_GUID;
1105
1106 l_found varchar2(1) := 'N';
1107 begin
1108 for v_customlevel in c_getCustomLevel loop
1109 X_CUSTOMIZATION_LEVEL := v_customlevel.customization_level;
1110 l_found := 'Y';
1111 end loop;
1112
1113 if l_found = 'N' then
1114 -- The subscription was not found...
1115 raise no_data_found;
1116 end if;
1117
1118 end fetch_custom_level;
1119
1120 -- Find_Subscription
1121 -- Function to check if there is a duplicate subscription. The logic in this procedure
1122 -- is moved from Receive procedure to be used from Insert_Row and Update_Row
1123 function Find_Subscription(x_subscription_guid in varchar2,
1124 x_system_guid in raw,
1125 x_source_type in varchar2,
1126 x_source_agent_guid in raw,
1127 x_event_filter_guid in raw,
1128 x_phase in number,
1129 x_rule_data in varchar2,
1130 x_priority in number,
1131 x_rule_function in varchar2,
1132 x_wf_process_type in varchar2,
1133 x_wf_process_name in varchar2,
1134 x_parameters in varchar2,
1135 x_owner_name in varchar2,
1136 x_owner_tag in varchar2)
1137 return raw
1138 is
1139 -- Identical Row Cursor
1140 -- A row is considered identical if it for the same system same event,
1141 -- same source type, same phase, same owner name same owner tag. We also
1142 -- need to take care of cases where both values are null.
1143
1144 CURSOR identical_row1 IS
1145 SELECT guid
1146 FROM wf_event_subscriptions
1147 WHERE system_guid = x_system_guid
1148 AND source_type = x_source_type
1149 AND event_filter_guid = x_event_filter_guid
1150 AND (((source_agent_guid is null) AND (x_source_agent_guid is null))
1151 OR source_agent_guid = x_source_agent_guid)
1152 AND (((phase is null) AND (x_phase is null))
1153 OR phase = x_phase)
1154 AND (((owner_name is null) AND (x_owner_name is null))
1155 OR owner_name = x_owner_name)
1156 AND (((owner_tag is null) AND (x_owner_tag is null))
1157 OR owner_tag = x_owner_tag);
1158
1159 CURSOR identical_row2 IS
1160 SELECT guid
1161 FROM wf_event_subscriptions
1162 WHERE system_guid = x_system_guid
1163 AND source_type = x_source_type
1164 AND event_filter_guid = x_event_filter_guid
1165 AND (phase is null OR phase = x_phase)
1166 AND owner_name is null
1167 AND owner_tag is null
1168 AND rule_data = x_rule_data
1169 AND priority = x_priority
1170 AND (((rule_function is null) AND (x_rule_function is null))
1171 OR rule_function = x_rule_function)
1172 AND (((wf_process_type is null) AND (x_wf_process_type is null))
1173 OR wf_process_type = x_wf_process_type)
1174 AND (((wf_process_name is null) AND (x_wf_process_name is null))
1175 OR wf_process_name = x_wf_process_name)
1176 AND (((parameters is null) AND (x_parameters is null))
1177 OR parameters = x_parameters);
1178
1179 l_guid raw(16);
1180 begin
1181 -- A row is considered identical if it for the same system
1182 -- same event, same source type, same phase, same owner name
1183 -- same owner tag
1184
1185 open identical_row1;
1186 fetch identical_row1 into l_guid;
1187 if (identical_row1%notfound) then
1188 -- An additional check in case the original row did not have the phase
1189 -- and/or owner name and owner tag fields defined
1190 -- Note: identical_row2 will not return any rows if only ONE
1191 -- of the 2 columns owner_name, owner_tag is null and the files contains
1192 -- a not null values
1193
1194 open identical_row2;
1195 fetch identical_row2 into l_guid;
1196 if (identical_row2%notfound) then
1197 if (x_subscription_guid = '#NEW') then
1198 l_guid := sys_guid();
1199 else
1200 l_guid := x_subscription_guid;
1201 end if;
1202 end if;
1203 close identical_row2;
1204 end if;
1205 close identical_row1;
1206 return l_guid;
1207 end Find_Subscription;
1208
1209 end WF_EVENT_SUBSCRIPTIONS_PKG;