[Home] [Help]
PACKAGE BODY: APPS.WF_SETUP
Source
1 package body WF_SETUP as
2 /* $Header: wfevsetb.pls 115.17 2004/06/02 17:41:54 dlam ship $ */
3
4 function GetLocalSystemGUID
5 return raw
6 is
7 lguid varchar2(32);
8 begin
9 select substr(TEXT, 1, 32) into lguid
10 from WF_RESOURCES
11 where NAME = 'WF_SYSTEM_GUID'
12 and LANGUAGE = userenv('LANG');
13
14 return hextoraw(lguid);
15 exception
16 when no_data_found then
17 wf_core.raise('WFE_NO_SYSTEM');
18 when OTHERS then
19 wf_core.context('WF_SETUP', 'GetLocalSystemGUID');
20 raise;
21 end GetLocalSystemGUID;
22
23 function GetLocalSystem
24 return varchar2
25 is
26 lsys varchar2(30);
27 begin
28 select S.NAME into lsys
29 from WF_SYSTEMS S, WF_RESOURCES R
30 where R.NAME = 'WF_SYSTEM_GUID'
31 and R.LANGUAGE = userenv('LANG')
32 and S.GUID = hextoraw(R.TEXT);
33
34 return (lsys);
35 exception
36 when OTHERS then
37 wf_core.context('WF_SETUP', 'GetLocalSystem');
38 raise;
39 end GetLocalSystem;
40
41 procedure Check_InitParameters
42 is
43 cursor pcurs is
44 select NAME, VALUE
45 from v$parameter
46 where NAME in ('aq_tm_processes', 'job_queue_processes',
47 'job_queue_interval')
48 order by NAME;
49
50 hTab wfe_html_util.headerTabType;
51 dTab wfe_html_util.dataTabType;
52 i pls_integer;
53 vTab wfe_html_util.tmpTabType;
54
55 begin
56 -- set the recommended values
57 i := 1;
58 vTab(i) := '1'; -- for aq_tm_processes
59 i := i+1;
60 vTab(i) := '5'; -- for job_queue_interval
61 i := i+1;
62 vTab(i) := '2'; -- for job_queue_processes
63
64 i := 0;
65 for pr in pcurs loop
66 i := i+1;
67 dTab(i).col01 := pr.name;
68 dTab(i).col02 := pr.value;
69 if (i <= 3) then
70 dTab(i).col03 := vTab(i);
71 end if;
72
73 dTab(i).selectable := FALSE;
74 dTab(i).deletable := FALSE;
75 dTab(i).hasdetail := FALSE;
76 end loop;
77
78 -- popluate the header table
79 i := 1;
80 hTab(i).def_type := 'TITLE';
81 hTab(i).value := wf_core.translate('NAME');
82 hTab(i).attr := 'id="t_name"';
83 i := i+1;
84 hTab(i).def_type := 'TITLE';
85 hTab(i).value := wf_core.translate('VALUE');
86 hTab(i).attr := 'ALIGN=RIGHT id="t_value"';
87 i := i+1;
88 hTab(i).def_type := 'TITLE';
89 hTab(i).value := wf_core.translate('VALUE_RECOMMENDED');
90 hTab(i).attr := 'ALIGN=RIGHT id="t_value_rec"';
91
92 htp.p('<p><b>'||wf_core.translate('WFE_INITPARAMS')||'</b>');
93 -- ### Database Init.ora Parameters
94
95 -- render table
96 Wfe_Html_Util.Simple_Table(hTab, dTab);
97
98 exception
99 when OTHERS then
100 wf_core.context('WF_SETUP', 'Check_InitParameters');
101 wfe_html_util.Error;
102 end Check_InitParameters;
103
104 procedure Check_Dblinks(
105 localsguid raw
106 )
107 is
108 -- all db link
109 cursor dblcurs is
110 select distinct substr(ADDRESS, instr(ADDRESS, '@')+1) NAME
111 from WF_AGENTS
112 where PROTOCOL = 'SQLNET'
113 and DIRECTION = 'IN'
114 and SYSTEM_GUID <> localsguid;
115
116 hTab wfe_html_util.headerTabType;
117 dTab wfe_html_util.dataTabType;
118 i pls_integer;
119
120 begin
121 -- populate the data table
122 i := 0;
123 for dblr in dblcurs loop
124 i := i+1;
125 dTab(i).guid := null;
126 dTab(i).col01:= dblr.name;
127
128 -- find out if such dblr.name exists
129 begin
130 select 'EXIST' into dTab(i).col02
131 from sys.dual
132 where upper(dblr.name) in (
133 select DB_LINK from USER_DB_LINKS
134 union all
135 select DB_LINK from ALL_DB_LINKS
136 where OWNER = 'PUBLIC');
137 exception
138 when NO_DATA_FOUND then
139 dTab(i).col02 := 'NOT_EXIST';
140
141 when OTHERS then
142 raise;
143 end;
144 dTab(i).col02 := wf_core.translate(dTab(i).col02);
145
146 dTab(i).selectable := FALSE;
147 dTab(i).deletable := FALSE;
148 dTab(i).hasdetail := FALSE;
149 end loop;
150
151 -- popluate the header table
152 i := 1;
153 hTab(i).def_type := 'TITLE';
154 hTab(i).value := wf_core.translate('NAME');
155 hTab(i).attr := 'id="t_name"';
156 i := i+1;
157 hTab(i).def_type := 'TITLE';
158 hTab(i).value := wf_core.translate('STATUS');
159 hTab(i).attr := 'id="t_status"';
160
161 htp.p('<p><b>'||wf_core.translate('WFE_DBLINKS')||'</b>');
162 -- ### Database Links
163
164 -- render table
165 Wfe_Html_Util.Simple_Table(hTab, dTab);
166
167 exception
168 when OTHERS then
169 rollback;
170 wf_core.context('WF_SETUP', 'Check_Dblinks');
171 wfe_html_util.Error;
172 end Check_Dblinks;
173
174
175 procedure Check_Queues(
176 localsguid raw
177 )
178 is
179 cursor lquecurs is
180 select A.GUID, A.NAME, A.DIRECTION, A.QUEUE_NAME
181 from WF_AGENTS A
182 where A.SYSTEM_GUID = localsguid
183 and A.PROTOCOL = 'SQLNET';
184
185 hTab wfe_html_util.headerTabType;
186 dTab wfe_html_util.dataTabType;
187 i pls_integer;
188
189 include_create boolean := FALSE;
190 creatable boolean;
191 l_qid number;
192 begin
193 null;
194 -- populate the data table
195 i := 0;
196 for lquer in lquecurs loop
197 i := i+1;
198 dTab(i).guid := lquer.guid;
199 dTab(i).col01:= lquer.name;
200 if (lquer.direction is null) then
201 dTab(i).col02:= ' ';
202 else
203 dTab(i).col02:= wf_core.translate(lquer.direction);
204 end if;
205 dTab(i).col03:= nvl(lquer.queue_name, ' ');
206
207 -- check existence
208 creatable := FALSE;
209 begin
210 select DQ.QID
211 into l_qid
212 from ALL_QUEUES DQ
213 where lquer.queue_name = DQ.OWNER||'.'||DQ.NAME
214 and QUEUE_TYPE = 'NORMAL_QUEUE';
215
216 dTab(i).col04 := 'YES';
217 exception
218 when NO_DATA_FOUND then
219 dTab(i).col04 := 'NO';
220 creatable := TRUE;
221
222 when OTHERS then
223 raise;
224 end;
225 dTab(i).col04 := wf_core.translate(dTab(i).col04);
226
227 -- if queue not exist, do not count message
228 if (creatable) then
229
230 dTab(i).col05 := '-';
231 dTab(i).col06 := '-';
232
233 -- find out the message count
234 else
235 begin
236 select to_char(v.ready), to_char(v.waiting)
237 into dTab(i).col05, dTab(i).col06
238 from gv$aq v
239 where v.qid = l_qid;
240
241 exception
242 when NO_DATA_FOUND then
243 dTab(i).col05 := '-';
244 dTab(i).col06 := '-';
245 end;
246 end if;
247
248 dTab(i).selectable := FALSE;
249 dTab(i).deletable := FALSE;
250 dTab(i).hasdetail := FALSE;
251 end loop;
252
253 -- popluate the header table
254 i := 1;
255 hTab(i).def_type := 'TITLE';
256 hTab(i).value := wf_core.translate('AGENT');
257 hTab(i).attr := 'id="t_agent"';
258 i := i+1;
259 hTab(i).def_type := 'TITLE';
260 hTab(i).value := wf_core.translate('DIRECTION');
261 hTab(i).attr := 'id="t_direction"';
262 i := i+1;
263 hTab(i).def_type := 'TITLE';
264 hTab(i).value := wf_core.translate('QUEUE_NAME');
265 hTab(i).attr := 'id="t_queue"';
266 i := i + 1;
267 hTab(i).def_type := 'TITLE';
268 hTab(i).value := wf_core.translate('QUEUE_CREATED');
269 hTab(i).attr := 'id="t_queue_created"';
270 i := i + 1;
271 hTab(i).def_type := 'TITLE';
272 hTab(i).value := wf_core.translate('MESSAGE_READY');
273 hTab(i).attr := 'ALIGN=RIGHT id="t_message_ready"';
274 i := i + 1;
275 hTab(i).def_type := 'TITLE';
276 hTab(i).value := wf_core.translate('MESSAGE_WAIT');
277 hTab(i).attr := 'ALIGN=RIGHT id="t_message_wait"';
278
279 htp.p('<p><b>'||wf_core.translate('WFE_LOCAL_QUEUES')||'</b>');
280 -- ### Local Agents
281
282 -- render table
283 Wfe_Html_Util.Simple_Table(hTab, dTab);
284
285 exception
286 when OTHERS then
287 rollback;
288 wf_core.context('WF_SETUP', 'Check_Queues');
289 wfe_html_util.Error;
290 end Check_Queues;
291
292
293 procedure Check_Listeners(
294 localsguid raw
295 )is
296 -- find all local queues that have direction of IN, ANY or undefined
297 cursor lqcurs is
298 select A.GUID, A.NAME
299 from WF_AGENTS A
300 where A.SYSTEM_GUID = localsguid
301 and A.PROTOCOL = 'SQLNET'
302 and A.STATUS = 'ENABLED'
303 and (A.DIRECTION is null or A.DIRECTION in ('IN', 'ANY'));
304
305 hTab wfe_html_util.headerTabType;
306 dTab wfe_html_util.dataTabType;
307 i pls_integer;
308
309 creatable boolean;
310 jobnum number;
311
312 begin
313 null;
314 -- populate the data table
315 i := 0;
316 for lqr in lqcurs loop
317 i := i + 1;
318 dTab(i).guid := lqr.guid;
319 dTab(i).col01:= lqr.name;
320
321 -- check existence
322 creatable := FALSE;
323 begin
324 -- lqr.name came from WF_AGENTS.NAME
325 -- BINDVAR_SCAN_IGNORE[6]
326 select 'YES'
327 into dTab(i).col02
328 from WF_ALL_JOBS
329 where upper(substr(WHAT, 1, 60))
330 like 'WF_EVENT.LISTEN('''||lqr.name||''')%'
331 and rownum < 2;
332 exception
333 when NO_DATA_FOUND then
334 dTab(i).col02 := 'NO';
335 creatable := TRUE;
336
337 when OTHERS then
338 raise;
339 end;
340 dTab(i).col02 := wf_core.translate(dTab(i).col02);
341
342 -- append creation function
343 if (creatable) then
344 dTab(i).col03 := dTab(i).col03||
345 '<a href='||wfa_html.base_url||'/wf_setup.edit_listener?aguid='||
346 rawtohex(lqr.guid)||'>'||
347 wf_core.translate('CREATE')||
348 '</a>';
349 else
350 dTab(i).col03 := dTab(i).col03||
351 '<a href='||wfa_html.base_url||'/wf_setup.list_listener?aguid='||
352 rawtohex(lqr.guid)||'>'||
353 wf_core.translate('EDIT')||
354 '</a>';
355 end if;
356
357 dTab(i).selectable := FALSE;
358 dTab(i).deletable := FALSE;
359 dTab(i).hasdetail := FALSE;
360 end loop;
361
362 -- popluate the header table
363 i := 1;
364 hTab(i).def_type := 'TITLE';
365 hTab(i).value := wf_core.translate('AGENT');
366 hTab(i).attr := 'id="t_agent"';
367 i := i+1;
368 hTab(i).def_type := 'TITLE';
369 hTab(i).value := wf_core.translate('SCHEDULED');
370 hTab(i).attr := 'id="t_scheduled"';
371 i := i+1;
372 hTab(i).def_type := 'TITLE';
373 hTab(i).value := wf_core.translate('ACTION');
374 hTab(i).attr := 'id="t_action"';
375
376 htp.p('<p><b>'||wf_core.translate('WFE_LISTENERS')||'</b>');
377 -- ### Listeners for local queues.
378
379 -- render table
380 Wfe_Html_Util.Simple_Table(hTab, dTab);
381
382 exception
383 when OTHERS then
384 rollback;
385 wf_core.context('WF_SETUP', 'Check_Listeners');
386 wfe_html_util.Error;
387 end Check_Listeners;
388
389
390 procedure Check_Propagations(
391 localsguid raw
392 )is
393
394 -- propagation for local system
395 cursor ppgcurs is
396 select OA.GUID OGUID,
397 OA.NAME,
398 OA.QUEUE_NAME OQUEUE,
399 upper(substr(TA.ADDRESS, instr(TA.ADDRESS, '@')+1)) TOSYSTEM
400 from WF_AGENTS OA, WF_AGENTS TA
401 where OA.SYSTEM_GUID = localsguid
402 and OA.PROTOCOL = 'SQLNET'
403 and OA.DIRECTION = 'OUT'
404 and TA.SYSTEM_GUID <> localsguid
405 and TA.PROTOCOL = 'SQLNET'
406 and TA.DIRECTION = 'IN'
407 and TA.ADDRESS IS NOT NULL
408 and TA.NAME <> 'WF_ERROR'
409 and TA.SYSTEM_GUID in (select GUID from WF_SYSTEMS)
410 union
411 -- propgation to a local queue
412 select A.GUID OGUID,
413 A.NAME,
414 A.QUEUE_NAME OQUEUE,
415 NULL TOSYSTEM
416 from WF_AGENTS A
417 where A.SYSTEM_GUID = localsguid
418 and A.PROTOCOL = 'SQLNET'
419 and A.DIRECTION = 'OUT'
420 order by TOSYSTEM;
421
422 hTab wfe_html_util.headerTabType;
423 dTab wfe_html_util.dataTabType;
424 i pls_integer;
425
426 creatable boolean;
427
428 begin
429 -- populate the data table
430 i := 0;
431 for ppgr in ppgcurs loop
432 i := i + 1;
433 dTab(i).guid := ppgr.oguid;
434 dTab(i).col01:= ppgr.name;
435 dTab(i).col02:= nvl(ppgr.tosystem,wf_core.translate('LOCAL')); -- in reality it is a db link
436 --dTab(i).col02:= nvl(ppgr.tosystem, ' '); -- in reality it is a db link
437
438 -- check out queue
439 begin
440 select NAME
441 into dTab(i).col03
442 from WF_AGENTS
443 where GUID = ppgr.oguid
444 and (PROTOCOL <> 'SQLNET'
445 or (DIRECTION is not null and DIRECTION = 'IN'));
446
447 wf_core.token('NAME', dTab(i).col03);
448 dTab(i).col03 := wf_core.translate('WFE_NOT_OUTAGENT');
449 exception
450 when NO_DATA_FOUND then
451 null;
452 end;
453
454 -- check system (dblink) exist
455 -- ### maybe in the future. Assume it exists for now.
456
457 -- check existence
458 creatable := FALSE;
459 if (dTab(i).col03 is null) then
460 begin
461 select null
462 into dTab(i).col03
463 from sys.dual
464 where exists (
465 select NULL
466 from DBA_QUEUE_SCHEDULES QS
467 where QS.DESTINATION = nvl(ppgr.tosystem, 'AQ$_LOCAL')
468 and QS.SCHEMA||'.'||QS.QNAME = ppgr.oqueue);
469 exception
470 when NO_DATA_FOUND then
471 creatable := TRUE;
472 end;
473
474 --if ppgr.tosystem = wf_core.translate('LOCAL') then
475 -- ppgr.tosystem := null;
476 --end if;
477
478 if (creatable) then
479 dTab(i).col03 :=
480 '<a href='||wfa_html.base_url||
481 '/wf_setup.edit_propagation?oqueue='||ppgr.oqueue
482 ||'&tosystem='||ppgr.tosystem
483 ||'&edit=N'
484 ||'>'||
485 wf_core.translate('CREATE')||
486 '</a>';
487 else
488 dTab(i).col03 :=
489 '<a href='||wfa_html.base_url||
490 '/wf_setup.edit_propagation?oqueue='||ppgr.oqueue
491 ||'&tosystem='||ppgr.tosystem
492 ||'&edit=Y'
493 ||'>'||
494 wf_core.translate('EDIT')||
495 '</a>';
496 end if;
497 end if;
498
499 dTab(i).selectable := FALSE;
500 dTab(i).deletable := FALSE;
501 dTab(i).hasdetail := FALSE;
502 end loop;
503
504 -- popluate the header table
505 i := 1;
506 hTab(i).def_type := 'TITLE';
507 hTab(i).value := wf_core.translate('OUT_AGENT');
508 hTab(i).attr := 'id="t_out_agent"';
509 i := i + 1;
510 hTab(i).def_type := 'TITLE';
511 hTab(i).value := wf_core.translate('WFE_DBLINK');
512 hTab(i).attr := 'id="t_dblink"';
513 i := i + 1;
514 hTab(i).def_type := 'TITLE';
515 hTab(i).value := wf_core.translate('SCHEDULE');
516 hTab(i).attr := 'id="t_schedule"';
517
518 htp.p('<p><b>'||wf_core.translate('WFE_PROPAGATIONS')||'</b>');
519 -- ### Propagations for local out agents.
520
521 -- render table
522 Wfe_Html_Util.Simple_Table(hTab, dTab);
523
524 exception
525 when OTHERS then
526 rollback;
527 wf_core.context('WF_SETUP', 'Check_Propagations');
528 wfe_html_util.Error;
529 end Check_Propagations;
530
531 procedure Check_All
532 is
533 username varchar2(320); -- Username to query
534 admin_role varchar2(320); -- Role for admin mode
535 l_systems number;
536
537 lguid raw(16);
538 lsys varchar2(30);
539
540 begin
541 -- Check session and current user
542 wfa_sec.GetSession(username);
543 username := upper(username);
544
545 -- Check Admin Priviledge
546 admin_role := wf_core.translate('WF_ADMIN_ROLE');
547 if (admin_role = '*' or
548 Wf_Directory.IsPerformer(username, admin_role)) then
549 -- Have admin privledge, do nothing.
550 null;
551 else
552 wf_core.raise('WF_NOTADMIN');
553 end if;
554
555 -- Check if Accessible
556 wf_event_html.isAccessible('SYSTEM');
557
558 lguid := Wf_Setup.GetLocalSystemGUID;
559 lsys := Wf_Setup.GetLocalSystem;
560
561 -- Render page
562 htp.htmlOpen;
563
564 -- Set page title
565 htp.headOpen;
566
567 -- list does not get updated after edit, so we add the
568 -- following tag to force the reload of page.
569 htp.p('<META HTTP-EQUIV=expires CONTENT="no-cache">');
570
571 htp.title(wf_core.translate('WFE_CHECK_ALL_TITLE')||'('||lsys||')');
572 wfa_html.create_help_function('wf/links/evt.htm?'||'EVTSETUP');
573 fnd_document_management.get_open_dm_display_window;
574 Wfe_Html_Util.generate_confirm;
575
576 htp.headClose;
577
578 -- Page header
579 wfa_sec.Header(FALSE,
580 NULL,
581 wf_core.translate('WFE_CHECK_ALL_TITLE')||'('||lsys||')',
582 TRUE);
583
584 htp.br; -- add some space between header and table
585
586 Wf_Setup.Check_InitParameters;
587 htp.br;
588
589 Wf_Setup.Check_Dblinks(lguid);
590 htp.br;
591
592 Wf_Setup.Check_Queues(lguid);
593 htp.br;
594
595 Wf_Setup.Check_Listeners(lguid);
596 htp.br;
597
598 Wf_Setup.Check_Propagations(lguid);
599 htp.br;
600
601 wfa_sec.Footer;
602 htp.htmlClose;
603
604 exception
605 when OTHERS then
606 rollback;
607 wf_core.context('WF_SETUP', 'Check_All');
608 wfe_html_util.Error;
609 end Check_All;
610
611 -- ###
612 -- Create_Queue is not used for now
613 --
614 procedure Create_Queue(
615 aguid in raw
616 )is
617 lguid raw(16);
618 sguid raw(16);
619 qname varchar2(30);
620 qtable varchar2(30);
621
622 l_msg varchar2(4000);
623
624 username varchar2(320); -- Username to query
625 admin_role varchar2(320); -- Role for admin mode
626
627 begin
628 -- Check session and current user
629 wfa_sec.GetSession(username);
630 username := upper(username);
631
632 -- Check Admin Priviledge
633 admin_role := wf_core.translate('WF_ADMIN_ROLE');
634 if (admin_role = '*' or
635 Wf_Directory.IsPerformer(username, admin_role)) then
636 -- Have admin privledge, do nothing.
637 null;
638 else
639 wf_core.raise('WF_NOTADMIN');
640 end if;
641
642 -- check system is local
643 lguid := Wf_Setup.GetLocalSystemGUID;
644
645 begin
646 select SYSTEM_GUID, substr(QUEUE_NAME,1,30)
647 into sguid, qname
648 from WF_AGENTS
649 where GUID = aguid;
650 exception
651 when OTHERS then
652 wf_core.raise('WFE_AGENT_NOTEXIST');
653 end;
654
655 qtable := substr(qname,1,24)||'_TABLE';
656
657 if (lguid <> sguid) then
658 wf_core.token('ENTITY', 'QUEUE');
659 wf_core.raise('WFE_SYSTEM_NOTLOCAL');
660 end if;
661
662 -- create queue table
663 dbms_aqadm.create_queue_table
664 (
665 queue_table => qtable,
666 queue_payload_type => wf_core.translate('WF_SCHEMA')||'.WF_EVENT_T',
667 sort_list => 'ENQ_TIME',
668 comment => 'Workflow event system default queue',
669 compatible => '8.1',
670 multiple_consumers => TRUE
671 );
672
673 -- create queue
674 dbms_aqadm.create_queue
675 (
676 queue_name => qname,
677 queue_table => qtable
678 );
679
680 -- start queue
681 dbms_aqadm.start_queue
682 (
683 queue_name => qname
684 );
685
686 commit;
687
688 -- go back to check_all
689 Wfe_Html_Util.gotoURL(p_url=>wfa_html.base_url||'/Wf_Setup.Check_All');
690
691 exception
692 when OTHERS then
693 rollback;
694 wf_core.context('WF_SETUP', 'Create_Queue', rawtohex(aguid),
695 qname, qtable);
696 wfe_html_util.Error;
697 end Create_Queue;
698
699 --
700 -- List_Listener
701 -- List the content of DBMS_JOB for a local agent
702 --
703 procedure List_Listener(
704 aguid in raw
705 ) is
706 -- nm came from WF_AGENTS.NAME
707 -- BINDVAR_SCAN_IGNORE[4]
708 cursor jobc(nm varchar2) is
709 select JOB, WHAT, upper(INTERVAL) interval
710 from WF_ALL_JOBS
711 where upper(WHAT) like 'WF_EVENT.LISTEN('''||nm||''');';
712
713 username varchar2(320); -- Username to query
714 admin_role varchar2(320); -- Role for admin mode
715
716 aname varchar2(30);
717 lguid raw(16);
718
719 hTab wfe_html_util.headerTabType;
720 dTab wfe_html_util.dataTabType;
721 i pls_integer;
722
723 l_url varchar2(2000);
724
725 l_aguid raw(16);
726 cookie owa_cookie.cookie;
727 -- bad_cookie exception; -- Syntax error in cookie
728
729 begin
730 -- Check session and current user
731 wfa_sec.GetSession(username);
732 username := upper(username);
733
734 -- Check Admin Priviledge
735 admin_role := wf_core.translate('WF_ADMIN_ROLE');
736 if (admin_role = '*' or
737 Wf_Directory.IsPerformer(username, admin_role)) then
738 -- Have admin privledge, do nothing.
739 null;
740 else
741 wf_core.raise('WF_NOTADMIN');
742 end if;
743
744 -- get it from the cookie if aguid is not set
745 if (aguid is null) then
746 cookie := owa_cookie.get('WF_AGENT_GUID');
747 if (cookie.num_vals <> 1) then
748 wf_core.raise('WFE_NO_COOKIE');
749 end if;
750 l_aguid := hextoraw(cookie.vals(1));
751 else
752 l_aguid := aguid;
753
754 -- Send parameter values back to cookie.
755 owa_util.mime_header('text/html', FALSE);
756 owa_cookie.send('WF_AGENT_GUID', l_aguid);
757 owa_util.http_header_close;
758 end if;
759
760 -- get local system
761 lguid := Wf_Setup.GetLocalSystemGUID;
762
763 -- check agent is local
764 begin
765 select NAME
766 into aname
767 from WF_AGENTS
768 where GUID = l_aguid
769 and SYSTEM_GUID = lguid;
770 exception
771 when OTHERS then
772 wf_core.raise('WFE_AGENT_NOTEXIST');
773 end;
774
775 i := 0;
776 for jobr in jobc(aname) loop
777 i := i+1;
778 dTab(i).guid := hextoraw(to_char(jobr.JOB));
779 dTab(i).col01 := jobr.WHAT;
780 dTab(i).col02 := jobr.INTERVAL;
781
782 dTab(i).selectable := FALSE;
783 dTab(i).deletable := TRUE;
784 dTab(i).hasdetail := FALSE;
785 end loop;
786
787 i := 1;
788 hTab(i).def_type := 'FUNCTION';
789 hTab(i).value := 'Wf_Setup.DeleteJob?h_url='||
790 wfa_html.base_url||'/Wf_Setup.List_Listener&h_job=';
791 i := i+1;
792 hTab(i).def_type := 'FUNCTION';
793 hTab(i).value := null;
794 i := i+1;
795 hTab(i).def_type := 'FUNCTION';
796 hTab(i).value := 'Wf_Setup.Edit_Listener?aguid='||rawtohex(l_aguid)
797 ||'&url='
798 ||wfa_html.base_url||'/Wf_Setup.List_Listener&jobnum=';
799 i := i+1;
800 hTab(i).def_type := 'TITLE';
801 hTab(i).value := null; -- no detail title
802 i := i+1;
803 hTab(i).def_type := 'TITLE';
804 hTab(i).value := wf_core.translate('EDIT');
805 hTab(i).attr := 'id="t_edit"';
806 i := i+1;
807 hTab(i).def_type := 'TITLE';
808 hTab(i).value := wf_core.translate('WHAT');
809 hTab(i).attr := 'id="t_what"';
810 i := i+1;
811 hTab(i).def_type := 'TITLE';
812 hTab(i).value := wf_core.translate('INTERVAL');
813 hTab(i).attr := 'id="t_interval"';
814
815 -- Render page
816 htp.htmlOpen;
817
818 -- Set page title
819 htp.headOpen;
820
821 -- List does not get updated after edit, so we add the
822 -- following tag to force the reload of page.
823 -- Note that we do not expire the first page where the cookie is set.
824 -- Setting cookie will refresh the page upon next visit.
825 if (aguid is null) then
826 htp.p('<META HTTP-EQUIV=expires CONTENT="no-cache">');
827 end if;
828
829 htp.title(wf_core.translate('WFE_LIST_LISTENERS_TITLE'));
830 wfa_html.create_help_function('wf/links/def.htm?'||'DEFLSNR');
831 fnd_document_management.get_open_dm_display_window;
832
833 Wfe_Html_Util.generate_confirm;
834
835 htp.headClose;
836
837 -- Page header
838 wfa_sec.Header(FALSE,
839 NULL,
840 wf_core.translate('WFE_LIST_LISTENERS_TITLE'),
841 TRUE);
842
843 htp.br; -- add some space between header and table
844
845 Wfe_Html_Util.Simple_Table(headerTab=>hTab, dataTab=>dTab);
846
847 htp.tableopen (calign=>'CENTER summary=""');
848 htp.tableRowOpen;
849 htp.p('<TD id="">');
850
851 -- construct the url for adding listener
852 l_url := wfa_html.base_url||'/Wf_Setup.Edit_Listener?aguid='
853 ||rawtohex(l_aguid)
854 ||'&url='||wfa_html.base_url||'/Wf_Setup.List_Listener';
855
856 wfa_html.create_reg_button (l_url,
857 wf_core.translate('ADD'),
858 wfa_html.image_loc,
859 null,
860 wf_core.translate('ADD'));
861 htp.p('</TD>');
862
863 -- Cancel button
864 htp.p('<TD id="">');
865 wfa_html.create_reg_button ('javascript:window.history.back()',
866 wf_core.translate('CANCEL'),
867 wfa_html.image_loc,
868 'FNDJLFCN.gif',
869 wf_core.translate('CANCEL'));
870 htp.p('</TD>');
871
872 htp.tableRowClose;
873 htp.tableClose;
874
875 wfa_sec.Footer;
876 htp.htmlClose;
877
878 exception
879 when OTHERS then
880 wf_core.context('WF_SETUP', 'List_Listener', rawtohex(l_aguid));
881 wfe_html_util.Error;
882 end List_Listener;
883
884 --
885 -- Edit_Listener
886 -- Edit/Create a listener for agent provided
887 -- if jobnum is not null, it is editing an existing job.
888 -- if url is provided, return to the url specified, otherwise, to check_all.
889 --
890 procedure Edit_Listener(
891 aguid in raw,
892 jobnum in pls_integer ,
893 url in varchar2
894 )is
895 username varchar2(320); -- Username to query
896 admin_role varchar2(320); -- Role for admin mode
897
898 aname varchar2(30);
899 lguid raw(16);
900
901 l_curpos pls_integer;
902 l_nxtpos pls_integer;
903
904 l_url varchar2(2000);
905 l_interval varchar2(2000);
906
907 cursor jobc(x_jobnum pls_integer) is
908 select NEXT_DATE, upper(INTERVAL) interval
909 from WF_ALL_JOBS
910 where job = x_jobnum;
911
912 begin
913 -- Check session and current user
914 wfa_sec.GetSession(username);
915 username := upper(username);
916
917 -- Check Admin Priviledge
918 admin_role := wf_core.translate('WF_ADMIN_ROLE');
919 if (admin_role = '*' or
920 Wf_Directory.IsPerformer(username, admin_role)) then
921 -- Have admin privledge, do nothing.
922 null;
923 else
924 wf_core.raise('WF_NOTADMIN');
925 end if;
926
927 -- get local system
928 lguid := Wf_Setup.GetLocalSystemGUID;
929
930 -- check agent is local
931 begin
932 select NAME
933 into aname
934 from WF_AGENTS
935 where GUID = aguid
936 and SYSTEM_GUID = lguid;
937 exception
938 when OTHERS then
939 wf_core.raise('WFE_AGENT_NOTEXIST');
940 end;
941
942 -- Render page
943 htp.htmlOpen;
944
945 -- Set page title
946 htp.headOpen;
947
948 htp.title(wf_core.translate('WFE_EDIT_LISTENER_TITLE'));
949 wfa_html.create_help_function('wf/links/t_d.htm?'||'T_DEFLSNR');
950
951 fnd_document_management.get_open_dm_display_window;
952
953 htp.headClose;
954
955 -- Page header
956 wfa_sec.Header(FALSE, null, wf_core.translate('WFE_EDIT_LISTENER_TITLE'),
957 TRUE);
958
959 -- Form
960 l_url := 'Wf_Setup.SubmitListener';
961 htp.formOpen(curl=>owa_util.get_owa_service_path||l_url,
962 cmethod=>'Post',
963 cattributes=>'TARGET="_top" NAME="WF_LSNR_EDIT"');
964
965 htp.tableOpen(calign=>'CENTER', cattributes=>'border=0 summary="' ||
966 WF_CORE.Translate('WFE_EDIT_LISTENER_TITLE') || '"');
967
968 -- Agent Name (non-editable)
969 htp.tableRowOpen;
970 htp.tableData(cvalue=>wf_core.translate('AGENT'),
971 calign=>'right', cattributes=>'id=""');
972 htp.tableData(cvalue=>'<b>'||aname||'</b>', calign=>'left',
973 cattributes=>'id=""');
974 if (jobnum < 0) then
975 htp.formHidden('h_job', null);
976 else
977 htp.formHidden('h_job', to_char(jobnum));
978 end if;
979 htp.formHidden('h_name', aname);
980 htp.tableRowClose;
981
982 -- Run Date (default sysdate when left blank)
983 htp.tableRowOpen;
984 htp.tableData(cvalue=>'<LABEL FOR="i_rundate">' ||
985 wf_core.translate('RUN_DATE') ||
986 '</LABEL>', calign=>'right', cattributes=>'id=""');
987
988 htp.tableData(cvalue=>htf.formText('h_rundate',19,
989 cattributes=>'id="i_rundate"')||' ('||
990 wf_engine.date_format||')', calign=>'left',
991 cattributes=>'id=""');
992 htp.tableRowClose;
993
994 -- Interval (default 0 day 0 hour 15 min 0 sec)
995 htp.tableRowOpen;
996 htp.tableData(cvalue=>wf_core.translate('RUN_EVERY'),
997 calign=>'right', cattributes=>'id=""');
998 htp.tableData(cvalue=>'<LABEL>' || htf.formText('h_day',3 )
999 ||' '||wf_core.translate('DAYS') || '</LABEL>'
1000 , calign=>'left', cattributes=>'id=""');
1001 htp.tableRowClose;
1002 htp.tableRowOpen;
1003 htp.tableData(cvalue=>' ', calign=>'right', cattributes=>'id=""');
1004 htp.tableData(cvalue=>'<LABEL>' || htf.formText('h_hour',3)
1005 ||' '||wf_core.translate('HOURS') || '</LABEL>'
1006 , calign=>'left', cattributes=>'id=""');
1007 htp.tableRowClose;
1008 htp.tableRowOpen;
1009 htp.tableData(cvalue=>' ', calign=>'right', cattributes=>'id=""');
1010 htp.tableData(cvalue=>'<LABEL>' || htf.formText('h_minute',3)
1011 ||' '||wf_core.translate('MINUTES') || '</LABEL>'
1012 , calign=>'left', cattributes=>'id=""');
1013 htp.tableRowClose;
1014 htp.tableRowOpen;
1015 htp.tableData(cvalue=>' ', calign=>'right', cattributes=>'id=""');
1016 htp.tableData(cvalue=>'<LABEL>' || htf.formText('h_sec',3)
1017 ||' '||wf_core.translate('SECONDS') || '</LABEL>'
1018 , calign=>'left', cattributes=>'id=""');
1019 htp.tableRowClose;
1020
1021 htp.tableClose;
1022
1023 -- go back to the specified URL
1024 -- if url is null, go back to check_all
1025 if (url is null) then
1026 htp.formHidden('h_url',wfa_html.base_url||'/Wf_Setup.Check_All');
1027 else
1028 htp.formHidden('h_url',url);
1029 end if;
1030
1031 htp.formClose;
1032
1033 -- figure out the interval if we are editing
1034 htp.p('<SCRIPT>');
1035 for jobr in jobc(jobnum) loop
1036 htp.p(' document.WF_LSNR_EDIT.h_rundate.value="'
1037 ||to_char(jobr.next_date, wf_engine.date_format)||'"');
1038
1039 if (instr(jobr.interval,'JOBNEXTRUNDATE(') <> 0) then
1040 l_curpos := instr(jobr.interval,',')+1;
1041 l_nxtpos := instr(jobr.interval,')');
1042 l_interval := substr(jobr.interval,l_curpos,l_nxtpos-l_curpos);
1043 end if;
1044 end loop;
1045
1046 -- l_interval looks like 'DD,HH,MI,SS'
1047 if (l_interval is not null) then
1048 l_curpos := 1;
1049 l_nxtpos := instr(l_interval,',');
1050 htp.p(' document.WF_LSNR_EDIT.h_day.value="'||
1051 substr(l_interval,l_curpos,l_nxtpos-l_curpos)||'"');
1052 l_curpos := l_nxtpos+1;
1053 l_nxtpos := instr(l_interval,',',1,2);
1054 htp.p(' document.WF_LSNR_EDIT.h_hour.value="'||
1055 substr(l_interval,l_curpos,l_nxtpos-l_curpos)||'"');
1056 l_curpos := l_nxtpos+1;
1057 l_nxtpos := instr(l_interval,',',1,3);
1058 htp.p(' document.WF_LSNR_EDIT.h_minute.value="'||
1059 substr(l_interval,l_curpos,l_nxtpos-l_curpos)||'"');
1060 l_curpos := l_nxtpos+1;
1061 htp.p(' document.WF_LSNR_EDIT.h_sec.value="'||
1062 substr(l_interval,l_curpos)||'"');
1063 end if;
1064
1065 htp.p('</SCRIPT>');
1066
1067 htp.p('<NOSCRIPT>' || WF_CORE.Translate('WFA_NOSCRIPT') || '</NOSCRIPT>');
1068
1069 htp.tableopen (calign=>'CENTER', cattributes=>'summary=""');
1070 htp.tableRowOpen;
1071
1072 -- Submit button
1073 htp.p('<TD id="">');
1074 wfa_html.create_reg_button ('javascript:document.WF_LSNR_EDIT.submit()',
1075 wf_core.translate ('SUBMIT'),
1076 wfa_html.image_loc,
1077 null,
1078 wf_core.translate ('SUBMIT'));
1079 htp.p('</TD>');
1080
1081 -- Cancel button
1082 htp.p('<TD id="">');
1083 wfa_html.create_reg_button ('javascript:window.history.back()',
1084 wf_core.translate('CANCEL'),
1085 wfa_html.image_loc,
1086 'FNDJLFCN.gif',
1087 wf_core.translate('CANCEL'));
1088 htp.p('</TD>');
1089
1090 htp.tableRowClose;
1091 htp.tableClose;
1092
1093 wfa_sec.Footer;
1094 htp.htmlClose;
1095
1096 commit;
1097 exception
1098 when OTHERS then
1099 rollback;
1100 wf_core.context('WF_SETUP', 'Edit_Listener', rawtohex(aguid));
1101 wfe_html_util.Error;
1102 end Edit_Listener;
1103
1104 procedure Edit_Propagation(
1105 oqueue in varchar2,
1106 tosystem in varchar2,
1107 edit in varchar2 ,
1108 url in varchar2
1109 )is
1110 username varchar2(320); -- Username to query
1111 admin_role varchar2(320); -- Role for admin mode
1112
1113 qname varchar2(30);
1114 sname varchar2(80);
1115 l_url varchar2(2000);
1116 l_nexttime varchar2(2000);
1117
1118 cursor ppgnc(x_qname varchar2, x_system varchar2) is
1119 select PROPAGATION_WINDOW, upper(NEXT_TIME) NEXT_TIME, LATENCY
1120 from DBA_QUEUE_SCHEDULES
1121 where DESTINATION = nvl(x_system, 'AQ$_LOCAL')
1122 and SCHEMA||'.'||QNAME = x_qname;
1123
1124 l_pos pls_integer;
1125
1126 begin
1127 -- Check session and current user
1128 wfa_sec.GetSession(username);
1129 username := upper(username);
1130
1131 -- Check Admin Priviledge
1132 admin_role := wf_core.translate('WF_ADMIN_ROLE');
1133 if (admin_role = '*' or
1134 Wf_Directory.IsPerformer(username, admin_role)) then
1135 -- Have admin privledge, do nothing.
1136 null;
1137 else
1138 wf_core.raise('WF_NOTADMIN');
1139 end if;
1140
1141 -- Render page
1142 htp.htmlOpen;
1143
1144 -- Set page title
1145 htp.headOpen;
1146
1147 htp.title(wf_core.translate('WFE_EDIT_PROPAGATION_TITLE'));
1148 wfa_html.create_help_function('wf/links/t_e.htm?'||'T_EPPGN');
1149
1150 fnd_document_management.get_open_dm_display_window;
1151
1152
1153 -- verify function
1154 -- having the (1 * value) to force the values to be compared as numbers
1155 htp.p('<SCRIPT LANGUAGE="JavaScript">');
1156 htp.p('<!-- Hide from old browsers');
1157 htp.p('function verify(msg) {
1158 if (document.WF_PPGN_EDIT.h_interval.value == "" ||
1159 document.WF_PPGN_EDIT.h_duration.value == "" ||
1160 ((1 * document.WF_PPGN_EDIT.h_interval.value) >
1161 (1 * document.WF_PPGN_EDIT.h_duration.value))
1162 ) {
1163 document.WF_PPGN_EDIT.submit();
1164 } else {
1165 window.alert(msg);
1166 }
1167 }');
1168 htp.p('<!-- done hiding from old browsers -->');
1169 htp.p('</SCRIPT>');
1170
1171 htp.p('<NOSCRIPT>' || WF_CORE.Translate('WFA_NOSCRIPT') || '</NOSCRIPT>');
1172
1173
1174 htp.headClose;
1175
1176 -- Page header
1177 wfa_sec.Header(FALSE, null, wf_core.translate('WFE_EDIT_PROPAGATION_TITLE'),
1178 TRUE);
1179
1180 -- Form
1181 l_url := 'Wf_Setup.SubmitPropagation';
1182 htp.formOpen(curl=>owa_util.get_owa_service_path||l_url,
1183 cmethod=>'Post',
1184 cattributes=>'TARGET="_top" NAME="WF_PPGN_EDIT"');
1185
1186 htp.tableOpen(calign=>'CENTER',
1187 cattributes=>'border=0 summary="' ||
1188 WF_CORE.Translate('WFE_EDIT_PROPAGATION_TITLE') ||
1189 '"');
1190
1191 -- Queue Name (non-editable)
1192 htp.tableRowOpen;
1193 htp.tableData(cvalue=>wf_core.translate('QUEUE'), calign=>'right',
1194 cattributes=>'id=""');
1195 htp.tableData(cvalue=>'<b>'||oqueue||'</b>', calign=>'left',
1196 cattributes=>'id=""');
1197 htp.formHidden('h_qname', oqueue);
1198 htp.tableRowClose;
1199
1200 -- To Database Link Name (non-editable)
1201 htp.tableRowOpen;
1202 htp.tableData(cvalue=>wf_core.translate('WFE_DBLINK'), calign=>'right',
1203 cattributes=>'id=""');
1204 htp.tableData(cvalue=>'<b>'||nvl(tosystem,wf_core.translate('LOCAL_SYSTEM'))
1205 ||'</b>', calign=>'left', cattributes=>'id=""');
1206 htp.formHidden('h_system', tosystem);
1207 htp.tableRowClose;
1208
1209 -- Duration (default null)
1210 htp.tableRowOpen;
1211 htp.tableData(cvalue=>wf_core.translate('DURATION'), calign=>'right', cattributes=>'id=""');
1212 htp.tableData(cvalue=>'<LABEL>' || htf.formText('h_duration',4)||' '
1213 ||wf_core.translate('SECONDS') || '</LABEL>',
1214 calign=>'left');
1215 htp.tableRowClose;
1216
1217 -- Interval (default null)
1218 htp.tableRowOpen;
1219 htp.tableData(cvalue=>wf_core.translate('RUN_EVERY'), calign=>'right',
1220 cattributes=>'id=""');
1221 htp.tableData(cvalue=>'<LABEL>' || htf.formText('h_interval',4)||' '
1222 ||wf_core.translate('SECONDS') || '</LABEL>',
1223 calign=>'left', cattributes=>'id=""');
1224 htp.tableRowClose;
1225
1226 -- Latency (default 60sec)
1227 htp.tableRowOpen;
1228 htp.tableData(cvalue=>wf_core.translate('LATENCY'), calign=>'right',
1229 cattributes=>'id=""');
1230 htp.tableData(cvalue=>'<LABEL>' || htf.formText('h_latency',4)||' '
1231 ||wf_core.translate('SECONDS') || '</LABEL>',
1232 calign=>'left');
1233 htp.tableRowClose;
1234
1235 htp.tableClose;
1236
1237 htp.formHidden('h_edit',edit);
1238
1239 -- go back to the specified URL
1240 -- if url is null, go back to check_all
1241 if (url is null) then
1242 htp.formHidden('h_url',wfa_html.base_url||'/Wf_Setup.Check_All');
1243 else
1244 htp.formHidden('h_url',url);
1245 end if;
1246
1247 -- action: DELETE/NULL
1248 htp.formHidden('h_action');
1249
1250 htp.formClose;
1251
1252 -- figure out the interval if we are editing
1253 if (edit = 'Y') then
1254 htp.p('<SCRIPT>');
1255 for ppgnr in ppgnc(oqueue, tosystem) loop
1256 htp.p(' document.WF_PPGN_EDIT.h_duration.value="'
1257 ||to_char(ppgnr.propagation_window)||'"');
1258
1259 -- assume we are parsing these standard formats
1260 -- SYSDATE + (interval/86400) - (duration/86400)
1261 -- SYSDATE + (interval/86400)
1262 -- SYSDATE - (duration/86400)
1263
1264 l_pos := instr(ppgnr.next_time, 'SYSDATE');
1265 if (l_pos <> 0) then
1266 l_pos := instr(ppgnr.next_time, '+');
1267 if (l_pos <> 0) then
1268 l_nexttime := substr(ppgnr.next_time, instr(ppgnr.next_time, '(')+1);
1269 l_pos := instr(l_nexttime, '/');
1270 htp.p(' document.WF_PPGN_EDIT.h_interval.value="'
1271 ||substr(l_nexttime,1,l_pos-1)||'"');
1272 end if;
1273 end if;
1274
1275 htp.p(' document.WF_PPGN_EDIT.h_latency.value="'
1276 ||to_char(ppgnr.latency)||'"');
1277 end loop;
1278
1279 htp.p('</SCRIPT>');
1280 htp.p('<NOSCRIPT>' || WF_CORE.Translate('WFA_NOSCRIPT') || '</NOSCRIPT>');
1281
1282 end if;
1283
1284 htp.tableopen (calign=>'CENTER', cattributes=>'WIDTH=100% summary=""');
1285 htp.tableRowOpen;
1286
1287 -- Delete button
1288 htp.p('<TD WIDTH=50% id="">');
1289 if (edit = 'Y') then
1290 htp.tableOpen(calign=>'CENTER', cattributes=>'summary=""');
1291 htp.tableRowOpen;
1292 htp.p('<TD id="">');
1293 wfa_html.create_reg_button (
1294 'javascript:document.WF_PPGN_EDIT.h_action.value=''DELETE'';'||
1295 'document.WF_PPGN_EDIT.submit()',
1296 wf_core.translate ('DELETE'),
1297 wfa_html.image_loc,
1298 null,
1299 wf_core.translate ('DELETE'));
1300 htp.p('</TD>');
1301 htp.tableRowClose;
1302 htp.tableClose;
1303 else
1304 htp.p(' ');
1305 end if;
1306 htp.p('</TD>');
1307
1308 -- Submit button
1309 htp.p('<TD id="">');
1310
1311 htp.tableOpen;
1312 htp.tableRowOpen;
1313 htp.p('<TD id="">');
1314 wfa_html.create_reg_button (
1315 'javascript:verify('||''''||wf_core.translate('WFE_INTERVAL_ERROR')
1316 ||''')',
1317 wf_core.translate ('SUBMIT'),
1318 wfa_html.image_loc,
1319 null,
1320 wf_core.translate ('SUBMIT'));
1321 htp.p('</TD>');
1322 htp.p('<TD id="">');
1323 -- Cancel button
1324 wfa_html.create_reg_button ('javascript:window.history.back()',
1325 wf_core.translate('CANCEL'),
1326 wfa_html.image_loc,
1327 'FNDJLFCN.gif',
1328 wf_core.translate('CANCEL'));
1329 htp.p('</TD>');
1330 htp.tableRowClose;
1331 htp.tableClose;
1332 htp.p('</TD>');
1333
1334 htp.tableRowClose;
1335 htp.tableClose;
1336
1337 wfa_sec.Footer;
1338 htp.htmlClose;
1339
1340 commit;
1341
1342 exception
1343 when OTHERS then
1344 rollback;
1345 wf_core.context('WF_SETUP', 'Edit_Propagation', oqueue, tosystem, url);
1346 wfe_html_util.Error;
1347 end Edit_Propagation;
1348
1349
1350 --
1351 -- SubmitListener
1352 -- Put in the change to the DBMS_JOB for Wf_Event.Listen().
1353 --
1354 procedure SubmitListener(
1355 h_job in varchar2,
1356 h_name in varchar2,
1357 h_rundate in varchar2,
1358 h_day in varchar2,
1359 h_hour in varchar2,
1360 h_minute in varchar2,
1361 h_sec in varchar2,
1362 h_url in varchar2
1363 ) is
1364 l_rundate date;
1365 l_job pls_integer;
1366 l_day pls_integer;
1367 l_hour pls_integer;
1368 l_minute pls_integer;
1369 l_sec pls_integer;
1370 l_name varchar2(30);
1371 begin
1372 -- resolve the job number
1373 begin
1374 l_job := to_number(h_job);
1375 exception
1376 when INVALID_NUMBER then
1377 l_job := to_number(NULL);
1378 end;
1379
1380 -- resolve the current rundate
1381 begin
1382 l_rundate := to_date(h_rundate, wf_engine.date_format);
1383 exception
1384 when VALUE_ERROR then
1385 l_rundate := to_date(null);
1386 end;
1387
1388 -- resolve the interval specified
1389 begin
1390 l_day := nvl(to_number(h_day), 0);
1391 exception
1392 when INVALID_NUMBER then
1393 l_day := 0;
1394 end;
1395 begin
1396 l_hour := nvl(to_number(h_hour), 0);
1397 exception
1398 when INVALID_NUMBER then
1399 l_hour := 0;
1400 end;
1401 begin
1402 l_minute := nvl(to_number(h_minute), 0);
1403 exception
1404 when INVALID_NUMBER then
1405 l_minute := 0;
1406 end;
1407 begin
1408 l_sec := nvl(to_number(h_sec), 0);
1409 exception
1410 when INVALID_NUMBER then
1411 l_sec := 0;
1412 end;
1413
1414 if (l_job is null) then
1415
1416 -- Bug 3372981 Validating agent name. If valid submit the job
1417 -- otherwise raise error.
1418 begin
1419 select name
1420 into l_name
1421 from wf_agents
1422 where name = upper(h_name);
1423
1424 DBMS_JOB.Submit(
1425 job=>l_job,
1426 what=>'Wf_Event.Listen('''||h_name||''');',
1427 next_date=>nvl(l_rundate, sysdate),
1428 interval=>to_date(null)
1429 );
1430 exception when no_data_found then
1431 -- Invalid Agent so raising error.
1432 wf_core.raise('WFE_AGENT_NOTEXIST');
1433 end;
1434 else
1435 DBMS_JOB.Next_Date(
1436 job=>l_job,
1437 next_date=>nvl(l_rundate, sysdate)
1438 );
1439 end if;
1440
1441 -- next rundate should be future date
1442 if (l_rundate is not null) then
1443 if (Wf_Setup.JobNextRunDate(l_job,l_day,l_hour,l_minute,l_sec)
1444 <= sysdate) then
1445 wf_core.raise('WFE_LATER_INTERVAL');
1446 end if;
1447 end if;
1448
1449 DBMS_JOB.Interval(
1450 job=>l_job,
1451 interval=>'Wf_Setup.JobNextRunDate('||to_char(l_job)||','||
1452 to_char(l_day)||','||
1453 to_char(l_hour)||','||
1454 to_char(l_minute)||','||
1455 to_char(l_sec)||')'
1456 );
1457
1458 -- force it to run the first time
1459 if (l_rundate is null) then
1460 DBMS_JOB.Run(
1461 job=>l_job
1462 );
1463 end if;
1464
1465 commit;
1466
1467 -- go back to the specified URL
1468 Wfe_Html_Util.gotoURL(h_url);
1469
1470 exception
1471 when OTHERS then
1472 rollback;
1473 wf_core.context('WF_SETUP', 'SubmitListener', h_name, h_rundate,
1474 '('||h_day||','||h_hour||','||h_minute||','||h_sec||')');
1475 wfe_html_util.Error;
1476 end SubmitListener;
1477
1478
1479 --
1480 -- SubmitPropagation
1481 -- Put in the change to the DBMS_AQADM.Schedule_Propagation.
1482 --
1483 procedure SubmitPropagation(
1484 h_qname in varchar2,
1485 h_system in varchar2,
1486 h_duration in varchar2,
1487 h_interval in varchar2,
1488 h_latency in varchar2,
1489 h_url in varchar2,
1490 h_action in varchar2,
1491 h_edit in varchar2
1492 )is
1493 l_duration pls_integer;
1494 l_interval pls_integer;
1495 l_latency pls_integer;
1496 l_nextrun varchar2(2000);
1497 begin
1498 -- remove the old schedule and return
1499 if (h_action = 'DELETE') then
1500 Wf_Setup.DeletePropagation(h_qname, h_system);
1501
1502 -- go back to the specified URL
1503 Wfe_Html_Util.gotoURL(h_url);
1504 return;
1505 end if;
1506
1507 -- resolve duration
1508 begin
1509 l_duration := to_number(h_duration);
1510 exception
1511 when INVALID_NUMBER then
1512 l_duration := to_number(NULL);
1513 end;
1514
1515 -- resolve interval
1516 begin
1517 l_interval := to_number(h_interval);
1518 exception
1519 when INVALID_NUMBER then
1520 l_interval := to_number(NULL);
1521 end;
1522
1523 -- resolve latency
1524 begin
1525 l_latency := nvl(to_number(h_latency), 60);
1526 exception
1527 when INVALID_NUMBER then
1528 l_latency := to_number(60);
1529 end;
1530
1531 -- remove the old schedule first
1532 if (h_edit = 'Y') then
1533 Wf_Setup.DeletePropagation(h_qname, h_system);
1534 end if;
1535
1536 -- calculate the nextrun function
1537 if (l_interval is not null) then
1538 l_nextrun := 'SYSDATE + ('||to_char(l_interval)||'/86400)';
1539
1540 -- include duration in the function only when there is an interval
1541 if (l_duration is not null) then
1542 l_nextrun := l_nextrun||' - ('||to_char(l_duration)||'/86400)';
1543 end if;
1544 end if;
1545
1546 -- schedule propagation
1547 DBMS_AQADM.Schedule_Propagation(
1548 queue_name=>h_qname,
1549 destination=>h_system,
1550 duration=>l_duration,
1551 next_time=>l_nextrun,
1552 latency=>l_latency
1553 );
1554
1555 -- go back to the specified URL
1556 Wfe_Html_Util.gotoURL(h_url);
1557
1558 exception
1559 when OTHERS then
1560 rollback;
1561 wf_core.context('WF_SETUP', 'SubmitPropagation', h_qname, h_system,
1562 h_duration, h_interval, h_latency);
1563 wfe_html_util.Error;
1564 end SubmitPropagation;
1565
1566
1567 --
1568 -- DeleteJob
1569 --
1570 procedure DeleteJob(
1571 h_job pls_integer,
1572 h_url varchar2
1573 ) is
1574 username varchar2(320); -- Username to query
1575 admin_role varchar2(320); -- Role for admin mode
1576 begin
1577 -- Check session and current user
1578 wfa_sec.GetSession(username);
1579 username := upper(username);
1580
1581 -- Check Admin Priviledge
1582 admin_role := wf_core.translate('WF_ADMIN_ROLE');
1583 if (admin_role = '*' or
1584 Wf_Directory.IsPerformer(username, admin_role)) then
1585 -- Have admin privledge, do nothing.
1586 null;
1587 else
1588 wf_core.raise('WF_NOTADMIN');
1589 end if;
1590
1591 DBMS_JOB.Remove(h_job);
1592
1593 -- go back to the specified URL
1594 Wfe_Html_Util.gotoURL(h_url);
1595 exception
1596 when OTHERS then
1597 wf_core.context('WF_SETUP', 'DeleteJob', h_job, h_url);
1598 raise;
1599 end DeleteJob;
1600
1601 --
1602 -- DeletePropagation
1603 --
1604 procedure DeletePropagation(
1605 h_qname in varchar2,
1606 h_system in varchar2
1607 )is
1608 username varchar2(320); -- Username to query
1609 admin_role varchar2(320); -- Role for admin mode
1610 begin
1611 -- Check session and current user
1612 wfa_sec.GetSession(username);
1613 username := upper(username);
1614
1615 -- Check Admin Priviledge
1616 admin_role := wf_core.translate('WF_ADMIN_ROLE');
1617 if (admin_role = '*' or
1618 Wf_Directory.IsPerformer(username, admin_role)) then
1619 -- Have admin privledge, do nothing.
1620 null;
1621 else
1622 wf_core.raise('WF_NOTADMIN');
1623 end if;
1624
1625 dbms_aqadm.UnSchedule_Propagation(
1626 queue_name=>h_qname,
1627 destination=>h_system
1628 );
1629 exception
1630 when OTHERS then
1631 wf_core.context('WF_SETUP', 'DeletePropagation', h_qname, h_system);
1632 raise;
1633 end DeletePropagation;
1634
1635 --
1636 -- JobNextRunDate (Private)
1637 -- Return the next run date for DBMS_JOB
1638 --
1639 function JobNextRunDate(
1640 jobnum in pls_integer,
1641 mday in number ,
1642 mhour in number ,
1643 mmin in number ,
1644 msec in number
1645 ) return date
1646 is
1647 nDate date;
1648 begin
1649 begin
1650 select NEXT_DATE into nDate
1651 from WF_ALL_JOBS
1652 where job = jobnum;
1653 exception
1654 when NO_DATA_FOUND then
1655 return to_date(NULL);
1656 end;
1657
1658 -- when nothing is specify, no next run date
1659 if (mDay = 0 and mDay = 0 and mHour = 0 and mMin = 0 and mSec = 0) then
1660 return(to_date(NULL));
1661 end if;
1662
1663 return(nDate + mDay + (mHour/24) + (mMin/1440) + (mSec/86400));
1664 end JobNextRunDate;
1665 --
1666 -- SubmitPropagation
1667 -- For eBusiness Suite: Scheduling Propagation from Concurrent Manager
1668 --
1669 procedure SubmitPropagation(
1670 errbuf out nocopy varchar2,
1671 retcode out nocopy varchar2,
1672 h_qname in varchar2,
1673 h_system in varchar2,
1674 h_duration in varchar2,
1675 h_latency in varchar2
1676 )is
1677 l_duration pls_integer;
1678 l_interval pls_integer;
1679 l_latency pls_integer;
1680 begin
1681 -- Resolve Duration
1682 begin
1683 l_duration := to_number(h_duration);
1684 exception
1685 when INVALID_NUMBER then
1686 l_duration := to_number(NULL);
1687 end;
1688
1689 -- resolve latency
1690 begin
1691 l_latency := nvl(to_number(h_latency), 60);
1692 exception
1693 when INVALID_NUMBER then
1694 l_latency := to_number(60);
1695 end;
1696
1697 -- In case an existing propagation schedule exists
1698 Wf_Setup.DeletePropagation(h_qname, h_system);
1699
1700 -- schedule propagation
1701 DBMS_AQADM.Schedule_Propagation(
1702 queue_name=>h_qname,
1703 destination=>h_system,
1704 duration=>l_duration,
1705 latency=>l_latency
1706 );
1707
1708 exception
1709 when OTHERS then
1710 rollback;
1711 wf_core.context('WF_SETUP', 'SubmitPropagation', h_qname, h_system,
1712 h_duration,h_latency);
1713 raise;
1714 end SubmitPropagation;
1715 end WF_SETUP;