[Home] [Help]
PACKAGE BODY: APPS.WF_EVENT_SUBSCRIPTIONS_PKG
Source
1 package body WF_EVENT_SUBSCRIPTIONS_PKG as
2 /* $Header: WFEVSUBB.pls 120.6 2005/09/02 15:19:13 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
364 end if;
365 elsif X_CUSTOMIZATION_LEVEL = 'L' then
366 if WF_EVENTS_PKG.g_Mode = 'UPGRADE' then
367 -- Limit events can have only a status change..
368 -- When the loader is loading the events the
369 -- users changes must be preserved. Update all
370 -- fields EXCEPT the status field.
371 update wf_event_subscriptions set
372 system_guid = X_SYSTEM_GUID,
373 source_type = X_SOURCE_TYPE,
374 source_agent_guid = X_SOURCE_AGENT_GUID,
375 event_filter_guid = X_EVENT_FILTER_GUID,
376 phase = X_PHASE,
377 rule_data = X_RULE_DATA,
378 out_agent_guid = X_OUT_AGENT_GUID,
379 to_agent_guid = X_TO_AGENT_GUID,
380 priority = X_PRIORITY,
381 rule_function = l_rule_func,
382 wf_process_type = X_WF_PROCESS_TYPE,
383 wf_process_name = X_WF_PROCESS_NAME,
384 parameters = X_PARAMETERS,
385 owner_name = X_OWNER_NAME,
386 owner_tag = X_OWNER_TAG,
387 description = X_DESCRIPTION,
388 customization_level = X_CUSTOMIZATION_LEVEL,
389 licensed_flag = l_licensed_flag,
390 expression = X_EXPRESSION,
391 action_code = X_ACTION_CODE,
392 on_error_code = X_ON_ERROR_CODE,
393 java_rule_func = X_JAVA_RULE_FUNC,
394 map_code = X_MAP_CODE,
395 standard_code = X_STANDARD_CODE,
396 standard_type = X_STANDARD_TYPE
397 where guid = X_GUID;
398
399 l_raise_event_flag := 'Y';
400
401 else -- Caller of the Update is UI
402 -- Limit events can have only a status change..
403 -- When the user is updating the event using the UI
404 -- Updates are allowed ONLY to the status field.
405 update wf_event_subscriptions set
406 status = X_STATUS,
407 licensed_flag = l_licensed_flag
408 where guid = X_GUID;
409
410 l_raise_event_flag := 'Y';
411
412 end if;
413
414 elsif X_CUSTOMIZATION_LEVEL = 'U' then
415 -- Here are the updates allowed for extensible and User defined events
416 -- only when the caller is the UI
417
418 if WF_EVENTS_PKG.g_Mode = 'CUSTOM' then
419 update wf_event_subscriptions set
420 system_guid = X_SYSTEM_GUID,
421 source_type = X_SOURCE_TYPE,
422 source_agent_guid = X_SOURCE_AGENT_GUID,
423 event_filter_guid = X_EVENT_FILTER_GUID,
424 phase = X_PHASE,
425 status = X_STATUS,
426 rule_data = X_RULE_DATA,
427 out_agent_guid = X_OUT_AGENT_GUID,
428 to_agent_guid = X_TO_AGENT_GUID,
429 priority = X_PRIORITY,
430 rule_function = l_rule_func,
431 wf_process_type = X_WF_PROCESS_TYPE,
432 wf_process_name = X_WF_PROCESS_NAME,
433 parameters = X_PARAMETERS,
434 owner_name = X_OWNER_NAME,
435 owner_tag = X_OWNER_TAG,
436 description = X_DESCRIPTION,
437 customization_level = X_CUSTOMIZATION_LEVEL,
438 licensed_flag = l_licensed_flag,
439 expression = X_EXPRESSION,
440 action_code = X_ACTION_CODE,
441 on_error_code = X_ON_ERROR_CODE,
442 java_rule_func = X_JAVA_RULE_FUNC,
443 map_code = X_MAP_CODE,
444 standard_code = X_STANDARD_CODE,
445 standard_type = X_STANDARD_TYPE
446 where guid = X_GUID;
447
448 l_raise_event_flag := 'Y';
449 else
450 -- The caller is Loader and the only way of
451 -- Uploading the data is in FORCE mode
452 null;
453 end if;
454 else
455 -- Raise error..
456 Wf_Core.Token('REASON','Invalid Customization Level:' ||
457 l_custom_level);
458 Wf_Core.Raise('WFSQL_INTERNAL');
459 end if;
460
461 -- Only raise if all if no raise_event_flag is set to 'Y'
462 -- fetch_custom_level will raise no_data_found if the subscription is not found
463 if (l_raise_event_flag = 'Y') then
464 wf_event.raise('oracle.apps.wf.event.subscription.update', X_GUID);
465 end if;
466
467 end if;
468
469 end UPDATE_ROW;
470 -----------------------------------------------------------------------------
471 procedure LOAD_ROW (
472 X_GUID in raw,
473 X_SYSTEM_GUID in raw,
474 X_SOURCE_TYPE in varchar2,
475 X_SOURCE_AGENT_GUID in raw,
476 X_EVENT_FILTER_GUID in raw,
477 X_PHASE in number,
478 X_STATUS in varchar2,
479 X_RULE_DATA in varchar2,
480 X_OUT_AGENT_GUID in raw,
481 X_TO_AGENT_GUID in raw,
482 X_PRIORITY in number,
483 X_RULE_FUNCTION in varchar2,
484 X_WF_PROCESS_TYPE in varchar2,
485 X_WF_PROCESS_NAME in varchar2,
486 X_PARAMETERS in varchar2,
487 X_OWNER_NAME in varchar2,
488 X_OWNER_TAG in varchar2,
489 X_CUSTOMIZATION_LEVEL in varchar2,
490 X_LICENSED_FLAG in varchar2,
491 X_DESCRIPTION in varchar2,
492 X_EXPRESSION in varchar2,
493 X_ACTION_CODE in varchar2,
494 X_ON_ERROR_CODE in varchar2,
495 X_JAVA_RULE_FUNC in varchar2,
496 X_MAP_CODE in varchar2,
497 X_STANDARD_CODE in varchar2,
498 X_STANDARD_TYPE in varchar2
499 ) is
500 row_id varchar2(64);
501 begin
502 begin
503 WF_EVENT_SUBSCRIPTIONS_PKG.UPDATE_ROW (
504 X_GUID => X_GUID,
505 X_SYSTEM_GUID => X_SYSTEM_GUID,
506 X_SOURCE_TYPE => X_SOURCE_TYPE,
507 X_SOURCE_AGENT_GUID => X_SOURCE_AGENT_GUID,
508 X_EVENT_FILTER_GUID => X_EVENT_FILTER_GUID,
509 X_PHASE => X_PHASE,
510 X_STATUS => X_STATUS,
511 X_RULE_DATA => X_RULE_DATA,
512 X_OUT_AGENT_GUID => X_OUT_AGENT_GUID,
513 X_TO_AGENT_GUID => X_TO_AGENT_GUID,
514 X_PRIORITY => X_PRIORITY,
515 X_RULE_FUNCTION => X_RULE_FUNCTION,
516 X_WF_PROCESS_TYPE => X_WF_PROCESS_TYPE,
517 X_WF_PROCESS_NAME => X_WF_PROCESS_NAME,
518 X_PARAMETERS => X_PARAMETERS,
519 X_OWNER_NAME => X_OWNER_NAME,
520 X_OWNER_TAG => X_OWNER_TAG,
521 X_CUSTOMIZATION_LEVEL => X_CUSTOMIZATION_LEVEL,
522 X_LICENSED_FLAG => X_LICENSED_FLAG,
523 X_DESCRIPTION => X_DESCRIPTION,
524 X_EXPRESSION => X_EXPRESSION,
525 X_ACTION_CODE => X_ACTION_CODE,
526 X_ON_ERROR_CODE => X_ON_ERROR_CODE,
527 X_JAVA_RULE_FUNC => X_JAVA_RULE_FUNC,
528 X_MAP_CODE => X_MAP_CODE,
529 X_STANDARD_CODE => X_STANDARD_CODE,
530 X_STANDARD_TYPE => X_STANDARD_TYPE
531 );
532 exception
533 when no_data_found then
534 WF_EVENT_SUBSCRIPTIONS_PKG.INSERT_ROW(
535 X_ROWID => row_id,
536 X_GUID => X_GUID,
537 X_SYSTEM_GUID => X_SYSTEM_GUID,
538 X_SOURCE_TYPE => X_SOURCE_TYPE,
539 X_SOURCE_AGENT_GUID => X_SOURCE_AGENT_GUID,
540 X_EVENT_FILTER_GUID => X_EVENT_FILTER_GUID,
541 X_PHASE => X_PHASE,
542 X_STATUS => X_STATUS,
543 X_RULE_DATA => X_RULE_DATA,
544 X_OUT_AGENT_GUID => X_OUT_AGENT_GUID,
545 X_TO_AGENT_GUID => X_TO_AGENT_GUID,
546 X_PRIORITY => X_PRIORITY,
547 X_RULE_FUNCTION => X_RULE_FUNCTION,
548 X_WF_PROCESS_TYPE => X_WF_PROCESS_TYPE,
549 X_WF_PROCESS_NAME => X_WF_PROCESS_NAME,
550 X_PARAMETERS => X_PARAMETERS,
551 X_OWNER_NAME => X_OWNER_NAME,
552 X_OWNER_TAG => X_OWNER_TAG,
553 X_CUSTOMIZATION_LEVEL => X_CUSTOMIZATION_LEVEL,
554 X_LICENSED_FLAG => X_LICENSED_FLAG,
555 X_DESCRIPTION => X_DESCRIPTION,
556 X_EXPRESSION => X_EXPRESSION,
557 X_ACTION_CODE => X_ACTION_CODE,
558 X_ON_ERROR_CODE => X_ON_ERROR_CODE,
559 X_JAVA_RULE_FUNC => X_JAVA_RULE_FUNC,
560 X_MAP_CODE => X_MAP_CODE,
561 X_STANDARD_CODE => X_STANDARD_CODE,
562 X_STANDARD_TYPE => X_STANDARD_TYPE
563 );
564 end;
565
566 -- Invalidate cache
567 wf_bes_cache.SetMetaDataUploaded();
568 exception
569 when others then
570 wf_core.context('Wf_Event_Subscriptions_Pkg', 'Load_Row', x_guid,
571 x_source_type, X_SOURCE_AGENT_GUID);
572 raise;
573 end LOAD_ROW;
574 -----------------------------------------------------------------------------
575 procedure DELETE_ROW (X_GUID in raw) is
576 begin
577 wf_event.raise('oracle.apps.wf.event.subscription.delete',x_guid);
578
579 delete from wf_event_subscriptions
580 where guid = X_GUID;
581
582 if (sql%notfound) then
583 raise no_data_found;
584 end if;
585
586 -- Invalidate cache
587 wf_bes_cache.SetMetaDataUploaded();
588 exception
589 when others then
590 wf_core.context('Wf_Events_Subscriptions_Pkg', 'Delete_Row', x_guid);
591 raise;
592 end DELETE_ROW;
593 ----------------------------------------------------------------------------
594 procedure DELETE_SET (
595 X_SYSTEM_GUID in raw,
596 X_SOURCE_TYPE in varchar2,
597 X_SOURCE_AGENT_GUID in raw,
598 X_EVENT_FILTER_GUID in raw,
599 X_PHASE in number,
600 X_STATUS in varchar2,
601 X_RULE_DATA in varchar2,
602 X_OUT_AGENT_GUID in raw,
603 X_TO_AGENT_GUID in raw,
604 X_PRIORITY in number,
605 X_RULE_FUNCTION in varchar2,
606 X_WF_PROCESS_TYPE in varchar2,
607 X_WF_PROCESS_NAME in varchar2,
608 X_PARAMETERS in varchar2,
609 X_OWNER_NAME in varchar2,
610 X_OWNER_TAG in varchar2,
611 X_DESCRIPTION in varchar2,
612 X_EXPRESSION in varchar2,
613 X_ACTION_CODE in varchar2,
614 X_ON_ERROR_CODE in varchar2,
615 X_JAVA_RULE_FUNC in varchar2,
616 X_MAP_CODE in varchar2,
617 X_STANDARD_CODE in varchar2,
618 X_STANDARD_TYPE in varchar2
619 ) is
620 begin
621 delete from wf_event_subscriptions
622 where (X_SYSTEM_GUID is null or (X_SYSTEM_GUID is not null
623 and system_guid like X_SYSTEM_GUID))
624 and (X_SOURCE_TYPE is null or (X_SOURCE_TYPE is not null
625 and source_type like X_SOURCE_TYPE))
626 and (X_SOURCE_AGENT_GUID is null or (X_SOURCE_AGENT_GUID is not null
627 and source_agent_guid like X_SOURCE_AGENT_GUID))
628 and (X_EVENT_FILTER_GUID is null or (X_EVENT_FILTER_GUID is not null
629 and event_filter_guid like X_EVENT_FILTER_GUID))
630 and (X_PHASE is null or (X_PHASE is not null
631 and phase like X_PHASE))
632 and (X_STATUS is null or (X_STATUS is not null
633 and status like X_STATUS))
634 and (X_RULE_DATA is null or (X_RULE_DATA is not null
635 and rule_data like X_RULE_DATA))
636 and (X_OUT_AGENT_GUID is null or (X_OUT_AGENT_GUID is not null
637 and out_agent_guid like X_OUT_AGENT_GUID))
638 and (X_TO_AGENT_GUID is null or (X_TO_AGENT_GUID is not null
639 and to_agent_guid like X_TO_AGENT_GUID))
640 and (X_PRIORITY is null or (X_PRIORITY is not null
641 and priority like X_PRIORITY))
642 and (X_RULE_FUNCTION is null or (X_RULE_FUNCTION is not null
643 and rule_function like X_RULE_FUNCTION))
644 and (X_WF_PROCESS_TYPE is null or (X_WF_PROCESS_TYPE is not null
645 and wf_process_type like X_WF_PROCESS_TYPE))
646 and (X_WF_PROCESS_NAME is null or (X_WF_PROCESS_NAME is not null
647 and wf_process_name like X_WF_PROCESS_NAME))
648 and (X_PARAMETERS is null or (X_PARAMETERS is not null
649 and parameters like X_PARAMETERS))
650 and (X_OWNER_NAME is null or (X_OWNER_NAME is not null
651 and owner_name like X_OWNER_NAME))
652 and (X_OWNER_TAG is null or (X_OWNER_TAG is not null
653 and owner_tag like X_OWNER_TAG))
654 and (X_DESCRIPTION is null or (X_DESCRIPTION is not null
655 and description like X_DESCRIPTION))
656 and (X_EXPRESSION is null or (X_EXPRESSION is not null
657 and expression like X_EXPRESSION))
658 and (X_ACTION_CODE is null or (X_ACTION_CODE is not null
659 and action_code like X_ACTION_CODE))
660 and (X_ON_ERROR_CODE is null or (X_ON_ERROR_CODE is not null
661 and on_error_code like X_ON_ERROR_CODE))
662 and (X_JAVA_RULE_FUNC is null or (X_JAVA_RULE_FUNC is not null
663 and java_rule_func like X_JAVA_RULE_FUNC))
664 and (X_MAP_CODE is null or (X_MAP_CODE is not null
665 and map_code like X_MAP_CODE))
666 and (X_STANDARD_CODE is null or (X_STANDARD_CODE is not null
667 and standard_code like X_STANDARD_CODE))
668 and (X_STANDARD_TYPE is null or (X_STANDARD_TYPE is not null
669 and standard_type like X_STANDARD_TYPE));
670
671 -- Invalidate cache
672 wf_bes_cache.SetMetaDataUploaded();
673 exception
674 when others then
675 wf_core.context('Wf_Events_Subscriptions_Pkg', 'Delete_Set',
676 x_system_guid, X_source_type, X_Event_Filter_GUID);
677 raise;
678 end DELETE_SET;
679 ----------------------------------------------------------------------------
680 function GENERATE (
681 X_GUID in raw
682 ) return varchar2 is
683 buf varchar2(32000);
684 l_doc xmldom.DOMDocument;
685 l_element xmldom.DOMElement;
686 l_root xmldom.DOMNode;
687 l_node xmldom.DOMNode;
688 l_header xmldom.DOMNode;
689
690 l_guid raw(16);
691 l_system_guid raw(16);
692 l_source_type varchar2(80);
693 l_source_agent_guid raw(16);
694 l_event_filter_name varchar2(240);
695 l_phase number;
696 l_status varchar2(8);
697 l_rule_data varchar2(8);
698 l_out_agent_guid raw(16);
699 l_to_agent_guid raw(16);
700 l_priority number;
701 l_rule_function varchar2(240);
702 l_wf_process_type varchar2(30);
703 l_wf_process_name varchar2(30);
704 l_parameters varchar2(4000);
705 l_owner_name varchar2(30);
706 l_owner_tag varchar2(30);
707 l_customization_level varchar2(1);
708 l_licensed_flag varchar2(1);
709 l_description varchar2(240);
710 l_version varchar2(80);
711 l_expression varchar2(4000);
712
713 --Bug 3328673
714 --JBES Support for loader
715 l_standardtype varchar2(30);
716 l_standardcode varchar2(30);
717 l_javarulefunc varchar2(240);
718 l_onerror varchar2(30);
719 l_actioncode varchar2(30);
720 begin
721
722 select s.system_guid, s.source_type, s.source_agent_guid,
723 e.name, s.phase, s.status, s.rule_data,
724 s.out_agent_guid, s.to_agent_guid, s.priority,
725 s.rule_function, s.wf_process_type, s.wf_process_name,
726 s.parameters, s.owner_name, s.owner_tag, s.description, s.expression,
727 nvl(s.customization_level, 'L'), nvl(s.licensed_flag, 'Y'),
728 s.standard_type , s.standard_code , s.java_rule_func , s.on_error_code,
729 s.action_code
730 into l_system_guid, l_source_type, l_source_agent_guid,
731 l_event_filter_name, l_phase, l_status, l_rule_data,
732 l_out_agent_guid, l_to_agent_guid, l_priority,
733 l_rule_function, l_wf_process_type, l_wf_process_name,
734 l_parameters, l_owner_name, l_owner_tag, l_description, l_expression,
735 l_customization_level, l_licensed_flag,l_standardtype,l_standardcode,
736 l_javarulefunc,l_onerror , l_actioncode
737 from wf_event_subscriptions s, wf_events e
738 where s.guid = x_guid
739 and e.guid = s.event_filter_guid;
740
741 l_doc := xmldom.newDOMDocument;
742 l_root := xmldom.makeNode(l_doc);
743 l_root := wf_event_xml.newtag (l_doc, l_root, wf_event_xml.masterTagName);
744 l_header := wf_event_xml.newtag(l_doc, l_root, m_table_name);
745 l_node := wf_event_xml.newtag(l_doc, l_header, wf_event_xml.versionTagName,
746 m_package_version);
747 l_node := wf_event_xml.newtag(l_doc, l_header, 'GUID',
748 rawtohex(x_guid));
749 l_node := wf_event_xml.newtag(l_doc, l_header, 'SYSTEM_GUID',
750 rawtohex(l_system_guid));
751 l_node := wf_event_xml.newtag(l_doc, l_header, 'SOURCE_TYPE',
752 l_source_type);
753 l_node := wf_event_xml.newtag(l_doc, l_header, 'SOURCE_AGENT_GUID',
754 rawtohex(l_source_agent_guid));
755 l_node := wf_event_xml.newtag(l_doc, l_header, 'EVENT_FILTER_GUID', l_event_filter_name);
756
757 l_node := wf_event_xml.newtag(l_doc, l_header, 'PHASE',
758 l_phase);
759 l_node := wf_event_xml.newtag(l_doc, l_header, 'STATUS',
760 l_status);
761 l_node := wf_event_xml.newtag(l_doc, l_header, 'RULE_DATA',
762 l_rule_data);
763 l_node := wf_event_xml.newtag(l_doc, l_header, 'OUT_AGENT_GUID',
764 rawtohex(l_out_agent_guid));
765 l_node := wf_event_xml.newtag(l_doc, l_header, 'TO_AGENT_GUID',
766 rawtohex(l_to_agent_guid));
767 l_node := wf_event_xml.newtag(l_doc, l_header, 'PRIORITY',
768 l_PRIORITY);
769 l_node := wf_event_xml.newtag(l_doc, l_header, 'RULE_FUNCTION',
770 l_RULE_FUNCTION);
771 --Bug 3328673
772 --Add new tags for JBES support
773 l_node := wf_event_xml.newtag(l_doc, l_header, 'JAVA_RULE_FUNC',
774 l_javarulefunc);
775 l_node := wf_event_xml.newtag(l_doc, l_header, 'STANDARD_TYPE',
776 l_standardtype);
777 l_node := wf_event_xml.newtag(l_doc, l_header, 'STANDARD_CODE',
778 l_standardcode);
779 l_node := wf_event_xml.newtag(l_doc, l_header, 'ON_ERROR_CODE',
780 l_onerror);
781 l_node := wf_event_xml.newtag(l_doc, l_header, 'ACTION_CODE',
782 l_actioncode);
783
784 l_node := wf_event_xml.newtag(l_doc, l_header, 'WF_PROCESS_TYPE',
785 l_wf_process_type);
786 l_node := wf_event_xml.newtag(l_doc, l_header, 'WF_PROCESS_NAME',
787 l_wf_process_name);
788 l_node := wf_event_xml.newtag(l_doc, l_header, 'PARAMETERS',
789 l_PARAMETERS);
790 l_node := wf_event_xml.newtag(l_doc, l_header, 'OWNER_NAME',
791 l_OWNER_NAME);
792 l_node := wf_event_xml.newtag(l_doc, l_header, 'OWNER_TAG',
793 l_OWNER_TAG);
794 l_node := wf_event_xml.newtag(l_doc, l_header, 'CUSTOMIZATION_LEVEL',
795 NVL(l_customization_level, 'L'));
796 l_node := wf_event_xml.newtag(l_doc, l_header, 'LICENSED_FLAG',
797 NVL(l_licensed_flag, 'Y'));
798 l_node := wf_event_xml.newtag(l_doc, l_header, 'DESCRIPTION',
799 l_DESCRIPTION);
800 l_node := wf_event_xml.newtag(l_doc, l_header, 'EXPRESSION',
801 l_EXPRESSION);
802 xmldom.writeToBuffer(l_root, buf);
803
804 return buf;
805 exception
806 when others then
807 wf_core.context('Wf_Events_Subscriptions_Pkg', 'Generate', x_guid);
808 raise;
809 end GENERATE;
810 -----------------------------------------------------------------------------
811 procedure RECEIVE (
812 X_MESSAGE in varchar2
813 ) is
814 l_guid raw(16);
815 l_system_guid raw(16);
816 l_source_type varchar2(80);
817 l_source_agent_guid raw(16);
818 l_event_filter_guid raw(16);
819 l_phase number;
820 l_status varchar2(8);
821 l_rule_data varchar2(8);
822 l_out_agent_guid raw(16);
823 l_to_agent_guid raw(16);
824 l_priority number;
825 l_rule_function varchar2(240);
826 l_wf_process_type varchar2(30);
827 l_wf_process_name varchar2(30);
828 l_parameters varchar2(4000);
829 l_owner_name varchar2(30);
830 l_owner_tag varchar2(30);
831 l_description varchar2(240);
832 l_version varchar2(80);
833 l_message varchar2(32000);
834 l_customization_level varchar2(1) := 'L';
835 l_licensed_flag varchar2(1) := 'Y';
836 l_subscription_guid varchar2(32);
837 l_expression varchar2(4000);
838
839 l_node_name varchar2(255);
840 l_node xmldom.DOMNode;
841 l_child xmldom.DOMNode;
842 l_value varchar2(32000);
843 l_length integer;
844 l_node_list xmldom.DOMNodeList;
845
846 l_num number;
847 --Bug 3328673
848 --JBES Support for loader
849 l_standardtype varchar2(30);
850 l_standardcode varchar2(30);
851 l_javarulefunc varchar2(240);
852 l_onerror varchar2(30);
853 l_actioncode varchar2(30);
854
855 -- Identical Row checks from this procedure are now moved to Find_Subscription
856 begin
857
858 l_message := x_message;
859 --l_message := WF_EVENT_SYNCHRONIZE_PKG.SetGUID(l_message); -- update #NEW
860 l_message := WF_EVENT_SYNCHRONIZE_PKG.SetSYSTEMGUID(l_message); -- update #LOCAL
861 l_message := WF_EVENT_SYNCHRONIZE_PKG.SetSID(l_message); -- update #SID
862 --Bug 3191978
863 --Replace agent names by their GUIDs
864 l_message := WF_EVENT_SYNCHRONIZE_PKG.SetAgent2('<SOURCE_AGENT_GUID>','</SOURCE_AGENT_GUID>',l_message); -- update #WF_IN, #WF_OUT, #WF_ERROR
865 l_message := WF_EVENT_SYNCHRONIZE_PKG.SetAgent2('<TO_AGENT_GUID>','</TO_AGENT_GUID>',l_message);
866 l_message := WF_EVENT_SYNCHRONIZE_PKG.SetAgent2('<OUT_AGENT_GUID>','</OUT_AGENT_GUID>',l_message);
867 l_node_list := wf_event_xml.findTable(l_message, m_table_name);
868 l_length := xmldom.getLength(l_node_list);
869
870 -- loop through elements that we received.
871 for i in 0..l_length-1 loop
872 l_node := xmldom.item(l_node_list, i);
873 l_node_name := xmldom.getNodeName(l_node);
874 if xmldom.hasChildNodes(l_node) then
875 l_child := xmldom.GetFirstChild(l_node);
876 l_value := xmldom.getNodevalue(l_child);
877 else
878 l_value := NULL;
879 end if;
880
881 if(l_node_name = 'GUID') then
882 --l_guid := l_value;
883 l_subscription_guid := l_value;
884 elsif(l_node_name = 'SYSTEM_GUID') then
885 l_SYSTEM_GUID := l_value;
886 elsif(l_node_name = 'SOURCE_TYPE') then
887 l_source_type := l_value;
888 elsif(l_node_name = 'SOURCE_AGENT_GUID') then
889 l_source_agent_guid := l_value;
890 elsif(l_node_name = 'EVENT_FILTER_GUID') then
891 -- Check if the value is event name, get the GUID
892 begin
893 SELECT guid
894 INTO l_event_filter_guid
895 FROM wf_events
896 WHERE name = l_value;
897 exception
898 when no_data_found then
899 -- Value is a event GUID (older wfx files)
900 l_event_filter_guid := l_value;
901 end;
902 elsif(l_node_name = 'PHASE') then
903 l_phase := to_number(l_value);
904 elsif(l_node_name = 'STATUS') then
905 l_status := l_value;
906 elsif(l_node_name = 'RULE_DATA') then
907 l_rule_data := l_value;
908 elsif(l_node_name = 'OUT_AGENT_GUID') then
909 l_out_agent_guid := l_value;
910 elsif(l_node_name = 'TO_AGENT_GUID') then
911 l_to_agent_guid := l_value;
912 elsif(l_node_name = 'PRIORITY') then
913 l_priority := to_number(l_value);
914 elsif(l_node_name = 'RULE_FUNCTION') then
915 l_rule_function := l_value;
916 elsif(l_node_name = 'WF_PROCESS_TYPE') then
917 l_wf_process_type := l_value;
918 elsif(l_node_name = 'WF_PROCESS_NAME') then
919 l_wf_process_name := l_value;
920 elsif(l_node_name = 'PARAMETERS') then
921 l_parameters := l_value;
922 elsif(l_node_name = 'OWNER_NAME') then
923 l_owner_name := l_value;
924 elsif(l_node_name = 'OWNER_TAG') then
925 l_owner_tag := l_value;
926 elsif(l_node_name = 'CUSTOMIZATION_LEVEL') then
927 l_customization_level := l_value;
928 elsif(l_node_name = 'LICENSED_FLAG') then
929 l_licensed_flag := l_value;
930 elsif(l_node_name = 'DESCRIPTION') then
931 l_description := l_value;
932 elsif(l_node_name = 'VERSION') then
933 l_version := l_value;
934 elsif(l_node_name = 'EXPRESSION') then
935 l_expression := l_value;
936 --Bug 3328673
937 --JBES Support for loader
938 elsif(l_node_name = 'JAVA_RULE_FUNC') then
939 l_javarulefunc := l_value;
940 elsif(l_node_name = 'STANDARD_TYPE') then
941 l_standardtype := l_value;
942 elsif(l_node_name = 'STANDARD_CODE') then
943 l_standardcode := l_value;
944 elsif(l_node_name = 'ON_ERROR_CODE') then
945 l_onerror := l_value;
946 elsif(l_node_name = 'ACTION_CODE') then
947 l_actioncode := l_value;
948 else
949 Wf_Core.Token('REASON', 'Invalid column name found:' ||
950 l_node_name || ' with value:'||l_value);
951 Wf_Core.Raise('WFSQL_INTERNAL');
952 end if;
953 end loop;
954
955 -- Validate Subscription
956 -- Phase must not be null
957 if L_PHASE is null then
958 -- For backward compatibility of the WFXLoad do not raise any errors when
959 -- the caller is the Loader. Throw a warning only
960 if WF_EVENTS_PKG.g_Mode <> 'UPGRADE' then
961 Wf_Core.Token('REASON','Subscription Phase cannot be null');
962 Wf_Core.Raise('WFSQL_INTERNAL');
963 else
964 wf_core.context('Wf_Events_Subscriptions_Pkg', 'Receive',
965 'WARNING! WARNING! Subscription Phase CANNOT be null for Event GUID '
966 || l_event_filter_guid || ' defaulting to 50');
967 l_Phase := 50;
968 end if;
969 end if;
970
971 -- Validate Subscription
972 -- Owner Name and Owner Tag must not be null
973 if (L_OWNER_NAME is null)
974 or (L_OWNER_TAG is null) then
975
976 -- For backward compatibility of the WFXLoad do not raise any errors when
977 -- the caller is the Loader. Throw a warning only
978 if WF_EVENTS_PKG.g_Mode <> 'UPGRADE' then
979 Wf_Core.Token('REASON','Subscription Owner Name and Owner Tag cannot be null');
980 Wf_Core.Raise('WFSQL_INTERNAL');
981 else
982 wf_core.context('Wf_Events_Subscriptions_Pkg', 'Receive',
983 'WARNING! WARNING! Subscription OWNER_NAME/OWNER_TAG CANNOT be null for Event GUID '
984 || l_event_filter_guid);
985 end if;
986 end if;
987
988 -- Check if the subscription is duplicate. If there is one already, use the GUID
989 -- of the existing subscription
990 l_guid := Find_Subscription(x_subscription_guid => l_subscription_guid,
991 x_system_guid => l_system_guid,
992 x_source_type => l_source_type,
993 x_source_agent_guid => l_source_agent_guid,
994 x_event_filter_guid => l_event_filter_guid,
995 x_phase => l_phase,
996 x_rule_data => l_rule_data,
997 x_priority => l_priority,
998 x_rule_function => l_rule_function,
999 x_wf_process_type => l_wf_process_type,
1000 x_wf_process_name => l_wf_process_name,
1001 x_parameters => l_parameters,
1002 x_owner_name => l_owner_name,
1003 x_owner_tag => l_owner_tag);
1004
1005 wf_event_subscriptions_pkg.load_row(
1006 X_GUID => l_guid,
1007 X_SYSTEM_GUID => l_system_guid,
1008 X_SOURCE_TYPE => l_source_type,
1009 X_SOURCE_AGENT_GUID => l_source_agent_guid,
1010 X_EVENT_FILTER_GUID => l_event_filter_guid,
1011 X_PHASE => l_phase,
1012 X_STATUS => l_status,
1013 X_RULE_DATA => l_rule_data,
1014 X_OUT_AGENT_GUID => l_out_agent_guid,
1015 X_TO_AGENT_GUID => l_to_agent_guid,
1016 X_PRIORITY => l_priority,
1017 X_RULE_FUNCTION => l_rule_function,
1018 X_WF_PROCESS_TYPE => l_wf_process_type,
1019 X_WF_PROCESS_NAME => l_wf_process_name,
1020 X_PARAMETERS => l_parameters,
1021 X_OWNER_NAME => l_owner_name,
1022 X_OWNER_TAG => l_owner_tag,
1023 X_CUSTOMIZATION_LEVEL => l_customization_level,
1024 X_LICENSED_FLAG => l_licensed_flag,
1025 X_DESCRIPTION => l_description,
1026 X_EXPRESSION => l_expression,
1027 X_ACTION_CODE => l_actioncode,
1028 X_ON_ERROR_CODE => l_onerror ,
1029 X_JAVA_RULE_FUNC => l_javarulefunc,
1030 X_STANDARD_CODE => l_standardcode,
1031 X_STANDARD_TYPE => l_standardtype
1032 );
1033
1034 exception
1035 when others then
1036 wf_core.context('Wf_Events_Subscriptions_Pkg', 'Receive', x_message);
1037 raise;
1038 end RECEIVE;
1039 -----------------------------------------------------------------------------
1040 procedure validate_subscription (X_EVENT_FILTER_GUID in raw,
1041 X_CUSTOMIZATION_LEVEL in varchar2,
1042 X_STATUS in varchar2) -- Bug 2756800
1043 is
1044
1045 cursor c_geteventcustom is
1046 select customization_level
1047 from wf_events
1048 where guid = X_EVENT_FILTER_GUID;
1049
1050 l_custom_level varchar2(1);
1051 l_trns1 varchar2(4000);
1052 l_trns2 varchar2(4000);
1053 e_invalid_sub exception;
1054 begin
1055
1056 for v_getcustom in c_geteventcustom loop
1057 l_custom_level := v_getcustom.customization_level;
1058 end loop;
1059
1060 -- Subscription Validity Matrix
1061
1062 if X_CUSTOMIZATION_LEVEL in ('C', 'L') then
1063 if l_custom_level in ('X', 'U') then
1064 raise e_invalid_sub;
1065 end if;
1066 if X_CUSTOMIZATION_LEVEL = 'C' and l_custom_level = 'L' and
1067 X_STATUS <> 'DISABLED' -- Bug 2756800
1068 then
1069 raise e_invalid_sub;
1070 end if;
1071 elsif X_CUSTOMIZATION_LEVEL = 'X' then
1072 if l_custom_level = 'U' then
1073 raise e_invalid_sub;
1074 end if;
1075 end if;
1076 exception
1077 when e_invalid_sub then
1078 l_trns1 := wf_core.translate('WFE_CUSTOM_LEVEL_' || X_CUSTOMIZATION_LEVEL);
1079 l_trns2 := wf_core.translate('WFE_CUSTOM_LEVEL_' || l_custom_level);
1080 wf_core.token('SUB_CUSTOM_LEVEL', l_trns1);
1081 wf_core.token('EVT_CUSTOM_LEVEL', l_trns2);
1082 wf_core.raise('WFE_INVALID_SUBSCRIPTION');
1083 end validate_subscription;
1084
1085 procedure fetch_custom_level(X_GUID in raw,
1086 X_CUSTOMIZATION_LEVEL out nocopy varchar2)
1087 is
1088 cursor c_getCustomLevel is
1089 select CUSTOMIZATION_LEVEL from
1090 WF_EVENT_SUBSCRIPTIONS
1091 where guid = X_GUID;
1092
1093 l_found varchar2(1) := 'N';
1094 begin
1095 for v_customlevel in c_getCustomLevel loop
1096 X_CUSTOMIZATION_LEVEL := v_customlevel.customization_level;
1097 l_found := 'Y';
1098 end loop;
1099
1100 if l_found = 'N' then
1101 -- The subscription was not found...
1102 raise no_data_found;
1103 end if;
1104
1105 end fetch_custom_level;
1106
1107 -- Find_Subscription
1108 -- Function to check if there is a duplicate subscription. The logic in this procedure
1109 -- is moved from Receive procedure to be used from Insert_Row and Update_Row
1110 function Find_Subscription(x_subscription_guid in varchar2,
1111 x_system_guid in raw,
1112 x_source_type in varchar2,
1113 x_source_agent_guid in raw,
1114 x_event_filter_guid in raw,
1115 x_phase in number,
1116 x_rule_data in varchar2,
1117 x_priority in number,
1118 x_rule_function in varchar2,
1119 x_wf_process_type in varchar2,
1120 x_wf_process_name in varchar2,
1121 x_parameters in varchar2,
1122 x_owner_name in varchar2,
1123 x_owner_tag in varchar2)
1124 return raw
1125 is
1126 -- Identical Row Cursor
1127 -- A row is considered identical if it for the same system same event,
1128 -- same source type, same phase, same owner name same owner tag. We also
1129 -- need to take care of cases where both values are null.
1130
1131 CURSOR identical_row1 IS
1132 SELECT guid
1133 FROM wf_event_subscriptions
1134 WHERE system_guid = x_system_guid
1135 AND source_type = x_source_type
1136 AND event_filter_guid = x_event_filter_guid
1137 AND (((source_agent_guid is null) AND (x_source_agent_guid is null))
1138 OR source_agent_guid = x_source_agent_guid)
1139 AND (((phase is null) AND (x_phase is null))
1140 OR phase = x_phase)
1141 AND (((owner_name is null) AND (x_owner_name is null))
1142 OR owner_name = x_owner_name)
1143 AND (((owner_tag is null) AND (x_owner_tag is null))
1144 OR owner_tag = x_owner_tag);
1145
1146 CURSOR identical_row2 IS
1147 SELECT guid
1148 FROM wf_event_subscriptions
1149 WHERE system_guid = x_system_guid
1150 AND source_type = x_source_type
1151 AND event_filter_guid = x_event_filter_guid
1152 AND (phase is null OR phase = x_phase)
1153 AND owner_name is null
1154 AND owner_tag is null
1155 AND rule_data = x_rule_data
1156 AND priority = x_priority
1157 AND (((rule_function is null) AND (x_rule_function is null))
1158 OR rule_function = x_rule_function)
1159 AND (((wf_process_type is null) AND (x_wf_process_type is null))
1160 OR wf_process_type = x_wf_process_type)
1161 AND (((wf_process_name is null) AND (x_wf_process_name is null))
1162 OR wf_process_name = x_wf_process_name)
1163 AND (((parameters is null) AND (x_parameters is null))
1164 OR parameters = x_parameters);
1165
1166 l_guid raw(16);
1167 begin
1168 -- A row is considered identical if it for the same system
1169 -- same event, same source type, same phase, same owner name
1170 -- same owner tag
1171
1172 open identical_row1;
1173 fetch identical_row1 into l_guid;
1174 if (identical_row1%notfound) then
1175 -- An additional check in case the original row did not have the phase
1176 -- and/or owner name and owner tag fields defined
1177 -- Note: identical_row2 will not return any rows if only ONE
1178 -- of the 2 columns owner_name, owner_tag is null and the files contains
1179 -- a not null values
1180
1181 open identical_row2;
1182 fetch identical_row2 into l_guid;
1183 if (identical_row2%notfound) then
1184 if (x_subscription_guid = '#NEW') then
1185 l_guid := sys_guid();
1186 else
1187 l_guid := x_subscription_guid;
1188 end if;
1189 end if;
1190 close identical_row2;
1191 end if;
1192 close identical_row1;
1193 return l_guid;
1194 end Find_Subscription;
1195
1196 end WF_EVENT_SUBSCRIPTIONS_PKG;