[Home] [Help]
PACKAGE BODY: APPS.WF_PLUG
Source
1 package body WF_PLUG as
2 /* $Header: wfplugb.pls 120.2 2005/10/04 23:26:39 rtodi ship $ */
3
4
5 --
6 -- Package Globals
7 --
8 chr_newline varchar2(1) := '
9 ';
10 result_button_threshold pls_integer := 3; -- Max number of submit buttons
11
12 --
13 -- Error (PRIVATE)
14 -- Print a page with an error message.
15 -- Errors are retrieved from these sources in order:
16 -- 1. wf_core errors
17 -- 2. Oracle errors
18 -- 3. Unspecified INTERNAL error
19 --
20 procedure Error
21 as
22 error_name varchar2(30);
23 error_message varchar2(2000);
24 error_stack varchar2(32000);
25 begin
26 htp.htmlOpen;
27 htp.headOpen;
28 htp.title(wf_core.translate('ERROR'));
29 htp.headClose;
30
31 begin
32 wfa_sec.Header(TRUE);
33 exception
34 when others then
35 htp.bodyOpen;
36 end;
37
38 htp.header(nsize=>1, cheader=>wf_core.translate('ERROR'));
39
40 wf_core.get_error(error_name, error_message, error_stack);
41
42 if (error_name is not null) then
43 htp.p(error_message);
44 else
45 htp.p(sqlerrm);
46 end if;
47
48 htp.hr;
49 htp.p(wf_core.translate('WFENG_ERRNAME')||': '||error_name);
50 htp.br;
51 htp.p(wf_core.translate('WFENG_ERRSTACK')||': '||
52 replace(error_stack,wf_plug.chr_newline,'<br>'));
53
54 wfa_sec.Footer;
55 htp.htmlClose;
56 end Error;
57
58 --
59 -- GetPlugSession
60 --
61 procedure GetPlugSession(plug_id IN NUMBER, session_id IN NUMBER, user_name out NOCOPY varchar2)
62 is
63 l_user_name varchar2(320); -- used as out parameters cannot be read!!
64 res boolean;
65 begin
66
67 -- Check the ic cookie for a session
68 begin
69 res := ICX_SEC.ValidatePlugSession(plug_id, session_id );
70 exception
71 when others then
72 wf_core.token('SQLCODE', SQLCODE);
73 wf_core.token('SQLERRM', SQLERRM);
74 wf_core.raise('WFSEC_GET_SESSION');
75 end;
76
77 if (res = FALSE ) then
78 wf_core.raise('WFSEC_NO_SESSION');
79 end if;
80
81 l_user_name := ICX_SEC.GetID(99, null, session_id);
82
83 user_name := l_user_name;
84
85 exception
86 when others then
87 wf_core.context('Wf_plug', 'GetPlugSession');
88 raise;
89 end GetPlugSession;
90
91
92 /*===========================================================================
93 PROCEDURE NAME: get_plug_definition
94
95 DESCRIPTION: Selects the plug definition and sets the cookie
96
97 ============================================================================*/
98 PROCEDURE get_plug_definition (
99 p_plug_id IN NUMBER,
100 p_worklist_definition OUT NOCOPY wf_plug.wf_worklist_definition_record
101 ) IS
102
103 ii NUMBER := 0;
104 l_definition_exists VARCHAR2(1) := 'Y';
105
106 BEGIN
107
108 FOR ii IN 1..2 LOOP
109
110 l_definition_exists := 'Y';
111
112 BEGIN
113
114 SELECT ROWID ROW_ID,
115 PLUG_ID,
116 USERNAME,
117 DEFINITION_NAME,
118 WHERE_STATUS,
119 WHERE_FROM,
120 WHERE_ITEM_TYPE,
121 WHERE_NOTIF_TYPE,
122 WHERE_SUBJECT,
123 WHERE_SENT_START,
124 WHERE_SENT_END,
125 WHERE_DUE_START,
126 WHERE_DUE_END,
127 WHERE_PRIORITY,
128 WHERE_NOTIF_DEL_BY_ME,
129 ORDER_PRIMARY,
130 ORDER_ASC_DESC
131 INTO p_worklist_definition
132 FROM WF_WORKLIST_DEFINITIONS
133 WHERE PLUG_ID = p_plug_id;
134
135 EXCEPTION
136 WHEN NO_DATA_FOUND THEN
137 l_definition_exists := 'N';
138 WHEN OTHERS THEN
139 RAISE;
140 END;
141
142 -- If this definition does not exist then copy the definition
143 -- from the default
144 IF (l_definition_exists = 'N' AND p_plug_id IS NOT NULL) THEN
145
146 INSERT INTO WF_WORKLIST_DEFINITIONS
147 ( PLUG_ID,
148 USERNAME,
149 DEFINITION_NAME,
150 WHERE_STATUS,
151 WHERE_FROM,
152 WHERE_ITEM_TYPE,
153 WHERE_NOTIF_TYPE,
154 WHERE_SUBJECT,
155 WHERE_SENT_START,
156 WHERE_SENT_END,
157 WHERE_DUE_START,
158 WHERE_DUE_END,
159 WHERE_PRIORITY,
160 WHERE_NOTIF_DEL_BY_ME,
161 ORDER_PRIMARY,
162 ORDER_ASC_DESC
163 )
164 SELECT p_plug_id,
165 null,
166 null,
167 'OPEN',
168 '*',
169 '*',
170 '*',
171 null,
172 null,
173 null,
174 null,
175 null,
176 'HML',
177 '0',
178 'PRIORITY',
179 null
180 FROM SYS.DUAL
181 WHERE NOT EXISTS
182 (SELECT 1
183 FROM WF_WORKLIST_DEFINITIONS
184 WHERE PLUG_ID = p_plug_id);
185
186 INSERT INTO WF_WORKLIST_COL_DEFINITIONS
187 ( PLUG_ID ,
188 USERNAME ,
189 DEFINITION_NAME ,
190 COLUMN_NUMBER ,
191 COLUMN_NAME ,
192 COLUMN_SIZE
193 )
194 SELECT p_plug_id,
195 null,
196 null,
197 1,
198 'SUBJECT',
199 100
200 FROM SYS.DUAL
201 WHERE NOT EXISTS
202 (SELECT 1
203 FROM WF_WORKLIST_COL_DEFINITIONS
204 WHERE PLUG_ID = p_plug_id);
205
206 COMMIT;
207
208 ELSE
209
210 -- break out of loop since you found the definition
211 exit;
212
213 END IF;
214
215 END LOOP;
216
217
218 exception
219 when others then
220 rollback;
221 wf_core.context('Wf_Plug', 'get_plug_definition');
222 wf_plug.Error;
223 end get_plug_definition;
224
225
226 /*===========================================================================
227 PROCEDURE NAME: find_criteria
228
229 DESCRIPTION: Draws the find criteria on the HTML Page. This
230 function is shared by the main find routine and the
231 plug.
232
233 ============================================================================*/
234 PROCEDURE find_criteria (
235 username IN VARCHAR2 DEFAULT NULL,
236 status IN VARCHAR2 DEFAULT '*',
237 fromuser IN VARCHAR2 DEFAULT '*',
238 ittype IN VARCHAR2 DEFAULT '*',
239 msubject IN VARCHAR2 DEFAULT '*',
240 beg_sent IN DATE DEFAULT null,
241 end_sent IN DATE DEFAULT null,
242 beg_due IN DATE DEFAULT null,
243 end_due IN DATE DEFAULT null,
244 priority IN VARCHAR2 DEFAULT null,
245 delegated_by_me IN VARCHAR2 DEFAULT '0',
246 orderkey IN VARCHAR2 DEFAULT 'PRIORITY',
247 customize IN VARCHAR2 DEFAULT 'N'
248 ) IS
249
250 admin_role varchar2(320); -- Role for admin mode
251 lang_codeset varchar2(50); -- Language Codeset from environment
252 -- (e.g. WE8ISO8859P1)
253 realname varchar2(360); -- Display name of username
254 s0 varchar2(2000);
255 lchecked VARCHAR2(2);
256
257 lbeg_sent NUMBER;
258
259 cursor lkcurs(lktype in varchar2) is
260 select WL.MEANING, WL.LOOKUP_CODE
261 from WF_LOOKUPS WL
262 where WL.LOOKUP_TYPE = lktype
263 order by WL.MEANING;
264
265 -- Lookup for Item Type
266 cursor itcurs(role in varchar2) is
267 select unique WIT.DISPLAY_NAME, WN.MESSAGE_TYPE
268 from WF_NOTIFICATIONS WN, WF_ITEM_TYPES_VL WIT
269 where WN.MESSAGE_TYPE = WIT.NAME
270 and WN.RECIPIENT_ROLE = role
271 order by WIT.DISPLAY_NAME;
272
273 ittype_list itcurs%rowtype;
274
275 BEGIN
276
277 wf_directory.GetRoleInfo(username, realname, s0, s0, s0, s0);
278
279 -- Get language codeset
280 lang_codeset := substr(userenv('LANGUAGE'),instr(userenv('LANGUAGE'),'.')+1,
281 length(userenv('LANGUAGE')));
282
283
284 -- From User Field
285 htp.tableRowOpen;
286 htp.tableData(cvalue=>wf_core.translate('NOTIF_FROM'), calign=>'right');
287
288 IF (fromuser = '*') THEN
289
290 htp.tableData(htf.formText(cname=>'fromuser', csize=>'30',
291 cvalue=>'', cmaxlength=>'30'));
292
293 ELSE
294
295 htp.tableData(htf.formText(cname=>'fromuser', csize=>'30',
296 cvalue=>fromuser, cmaxlength=>'30'));
297
298 END IF;
299
300 htp.tableRowClose;
301
302
303 -- Type field
304 htp.tableRowOpen;
305 htp.tableData(cvalue=>wf_core.translate('NOTIF_TYPE'), calign=>'right');
306 htp.p('<TD>');
307 htp.formSelectOpen('ittype');
308
309 for ittype_list in itcurs(username) loop
310
311 IF (ittype_list.message_type = ittype) THEN
312
313 htp.formSelectOption(cvalue=>ittype_list.display_name,
314 cselected => 'SELECTED',
315 cattributes=>'value='||ittype_list.message_type);
316
317 ELSE
318
319 htp.formSelectOption(cvalue=>ittype_list.display_name,
320 cattributes=>'value='||ittype_list.message_type);
321
322 END IF;
323
324 end loop;
325
326 IF (ittype = '*') THEN
327
328 htp.formSelectOption(cvalue=>wf_core.translate('ALL'),
329 cselected => 'SELECTED',
330 cattributes=>'value=*');
331
332 ELSE
333
334 htp.formSelectOption(cvalue=>wf_core.translate('ALL'),
335 cattributes=>'value=*');
336
337 END IF;
338
339 htp.formSelectClose;
340 htp.p('</TD>');
341 htp.tableRowClose;
342
343 -- Sent in the last N days field
344 htp.tableRowOpen;
345 htp.tableData(cvalue=>wf_core.translate('NOTIF_SENT'), calign=>'right');
346
347 IF (beg_sent IS NOT NULL) THEN
348
349 lbeg_sent := TO_DATE(beg_sent, 'MM/DD/YYYY') - TO_DATE('01/01/2000', 'MM/DD/YYYY');
350
351 ELSE
352
353 lbeg_sent := null;
354
355 END IF;
356
357
358 htp.tableData(htf.formText(cname=>'beg_sent', csize=>'5',
359 cvalue=>lbeg_sent, cmaxlength=>'5'));
360
361
362 htp.tableRowClose;
363
364 -- Skip a line
365 htp.tableRowOpen;
366
367 htp.tableData(cvalue=>' ');
368
369 htp.tableRowClose;
370
371 -- High Priority Items
372 htp.tableRowOpen;
373
374 htp.tableData(cvalue=>' ');
375
376 IF (INSTR(priority, 'H') > 0) THEN
377
378 lchecked := 'Y';
379
380 ELSE
381
382 lchecked := null;
383
384 END IF;
385
386 htp.tableData(
387 cvalue=>htf.formcheckbox(
388 cname=>'HPRIORITY',
389 cvalue=>'H',
390 cchecked=>lchecked)||
391 ' '||wf_core.translate('HIGH_MESSAGES')||' ',
392 calign=>'left');
393
394 htp.tableRowClose;
395
396 -- Medium Priority Items
397 htp.tableRowOpen;
398
399 htp.tableData(cvalue=>' ');
400
401 IF (INSTR(priority, 'M') > 0) THEN
402
403 lchecked := 'Y';
404
405 ELSE
406
407 lchecked := null;
408
409 END IF;
410
411 htp.tableData(
412 cvalue=>htf.formcheckbox(
413 cname=>'MPRIORITY',
414 cvalue=>'M',
415 cchecked=>lchecked)||
416 ' '||wf_core.translate('MEDIUM_MESSAGES')||' ',
417 calign=>'left');
418
419 htp.tableRowClose;
420
421 -- Low Priority Items
422 htp.tableRowOpen;
423
424 htp.tableData(cvalue=>' ');
425
426 IF (INSTR(priority, 'L') > 0) THEN
427
428 lchecked := 'Y';
429
430 ELSE
431
432 lchecked := null;
433
434 END IF;
435
436 htp.tableData(
437 cvalue=>htf.formcheckbox(
438 cname=>'LPRIORITY',
439 cvalue=>'L',
440 cchecked=>lchecked)||
441 ' '||wf_core.translate('LOW_MESSAGES')||' ',
442 calign=>'left');
443
444 htp.tableRowClose;
445
446 htp.tableClose;
447
448
449 exception
450 when others then
451 Wf_Core.Context('wf_plug', 'find_criteria');
452 wf_plug.Error;
453
454 END find_criteria;
455
456
457 --
458 -- WorkList
459 -- Construct the worklist (summary page) for user.
460 -- IN
461 -- orderkey - Key to order by (default PRIORITY)
462 -- Valid values are PRIORITY, MESSAGE_TYPE, SUBJECT, BEGIN_DATE,
463 -- DUE_DATE, END_DATE, STATUS.
464 -- status - Status to query (default OPEN)
465 -- Valid values are OPEN, CLOSED, CANCELED, ERROR, *.
466 -- user - User to query notifications for. If null query user currently
467 -- logged in.
468 -- Note: Only a user in role WF_ADMIN_ROLE can query a user other
469 -- than the current user.
470 --
471 procedure WorkList(
472 plug_id in varchar2 default null,
473 session_id in varchar2 default null,
474 display_name in varchar2 default null
475 )
476 as
477 lorderkey varchar2(30);
478 lstatus varchar2(30);
479 luser varchar2(320);
480 lfromuser varchar2(320);
481 littype varchar2(8);
482 lsubject varchar2(80);
483 lbeg_sent date;
484 lend_sent date;
485 lbeg_due date;
486 lend_due date;
487 lpriority varchar2(8);
488 ldbm number;
489 nf_from_user varchar2(4000);
490 nf_subject varchar2(4000);
491 nf_to_user varchar2(4000);
492
493 high_bottom_value NUMBER;
494 high_top_value NUMBER;
495 low_bottom_value NUMBER;
496 low_top_value NUMBER;
497
498 username varchar2(320); -- Username to query
499 colon pls_integer; -- Magic orig_system decoder
500 uorig_system varchar2(30); -- User orig_system for indexes
501 uorig_system_id pls_integer; -- User orig_system_id for indexes
502 realname varchar2(360); -- Display name of username
503 admin_role varchar2(320); -- Role for admin mode
504 s0 varchar2(2000); -- Dummy
505 n_priority varchar2(80); -- priority icon
506 l_record_num number;
507 n_response varchar2(80); -- required response icon
508
509 cursor wl_cursor (p0 pls_integer, p1 pls_integer,
510 p2 pls_integer, p3 pls_integer) is
511 select WN.NOTIFICATION_ID nid,
512 WN.PRIORITY,
513 WIT.DISPLAY_NAME message_type,
514 WN.SUBJECT,
515 WN.BEGIN_DATE,
516 WN.DUE_DATE,
517 WN.END_DATE,
518 WL.MEANING display_status,
519 WN.STATUS,
520 WN.LANGUAGE
521 from WF_NOTIFICATIONS WN, WF_ITEM_TYPES_VL WIT, WF_LOOKUPS WL
522 where WN.MESSAGE_TYPE = decode(Worklist.littype, '*', WN.MESSAGE_TYPE,
523 Worklist.littype)
524 and WN.MESSAGE_TYPE = WIT.NAME
525 and WL.LOOKUP_TYPE = 'WF_NOTIFICATION_STATUS'
526 and WN.STATUS = WL.LOOKUP_CODE
527 and WN.RECIPIENT_ROLE in
528 (select WUR.ROLE_NAME
529 from WF_USER_ROLES WUR
530 where WUR.USER_ORIG_SYSTEM = Worklist.uorig_system
531 and WUR.USER_ORIG_SYSTEM_ID = Worklist.uorig_system_id
532 and WUR.USER_NAME = Worklist.username)
533 and ((Worklist.lfromuser = '*' ) or
534 (WN.ORIGINAL_RECIPIENT = upper(Worklist.lfromuser)))
535 and WN.STATUS = 'OPEN'
536 and ((WN.BEGIN_DATE is null)
537 or ((WN.BEGIN_DATE >= decode(Worklist.lbeg_sent, null, WN.BEGIN_DATE,
538 Worklist.lbeg_sent))
539 and (WN.BEGIN_DATE <= decode(Worklist.lend_sent, null, WN.BEGIN_DATE,
540 Worklist.lend_sent))))
541 and (PRIORITY between p0 and p1 or PRIORITY between p2 and p3)
542 order by decode(upper(Worklist.lorderkey),
543 'MESSAGE_TYPE', WIT.DISPLAY_NAME,
544 'SUBJECT', WN.SUBJECT,
545 'BEGIN_DATE', to_char(WN.BEGIN_DATE, 'J.SSSSS'),
546 'DUE_DATE', to_char(WN.DUE_DATE, 'J.SSSSS'),
547 'END_DATE', to_char(WN.END_DATE, 'J.SSSSS'),
548 'STATUS', WL.MEANING,
549 'RPRIORITY', to_char(100 - WN.PRIORITY, '00000000'),
550 to_char(WN.PRIORITY, '00000000'));
551
552 -- Notes: lower the number higher the priority!
553 -- Assumed priority has the range between 0 and 100
554
555 cursor attrs(mnid in number) is
556 select MA.NAME
557 from WF_NOTIFICATION_ATTRIBUTES NA,
558 WF_MESSAGE_ATTRIBUTES_VL MA,
559 WF_NOTIFICATIONS N
560 where N.NOTIFICATION_ID = mnid
561 and NA.NOTIFICATION_ID = N.NOTIFICATION_ID
562 and MA.MESSAGE_NAME = N.MESSAGE_NAME
563 and MA.MESSAGE_TYPE = N.MESSAGE_TYPE
564 and MA.NAME = NA.NAME
565 and MA.SUBTYPE = 'RESPOND'
566 and MA.TYPE <> 'FORM'
567 and MA.NAME = 'RESULT';
568
569 CURSOR c_fetch_col_def (c_plug_id IN NUMBER,
570 c_username IN VARCHAR2) IS
571 SELECT
572 ROWID ROW_ID,
573 PLUG_ID,
574 USERNAME,
575 COLUMN_NUMBER,
576 COLUMN_NAME,
577 COLUMN_SIZE
578 FROM WF_WORKLIST_COL_DEFINITIONS
579 WHERE (PLUG_ID = c_plug_id and c_plug_id IS NOT NULL)
580 OR (USERNAME = c_username and c_username IS NOT NULL and c_plug_id IS NULL)
581 ORDER BY COLUMN_NUMBER;
582
583 l_worklist_definition wf_plug.wf_worklist_definition_record;
584 l_worklist_col_definition wf_plug.wf_worklist_col_def_table;
585
586 notrec wl_cursor%ROWTYPE;
587 result attrs%rowtype;
588
589 begin
590
591 -- If this is being called as a plug then go get the plug definition
592 -- and set the cookie
593 IF (plug_id IS NOT NULL) THEN
594
595 -- Check plug session and get current user
596 GetPlugSession(TO_NUMBER(plug_id), TO_NUMBER(session_id),
597 username);
598
599 get_plug_definition(TO_NUMBER(plug_id), l_worklist_definition);
600
601 ELSE
602
603 return;
604
605 END IF;
606
607 lorderkey := l_worklist_definition.order_primary;
608 lstatus := l_worklist_definition.where_status;
609 lfromuser := l_worklist_definition.where_from;
610 littype := l_worklist_definition.where_item_type;
611 lsubject := l_worklist_definition.where_subject;
612
613 /*
614 ** This is a bit tricky but instead of having the user enter a range of
615 ** dates for the criteria they enter the number of days since it was Sent
616 ** We then store the start date as an offset from 01-JAN-2000 and get
617 ** the Sent in the last NOT days criteria and subtract it from sysdate to
618 ** get the correct start range for the search.
619 */
620 IF (l_worklist_definition.where_sent_start IS NOT NULL) THEN
621
622 lbeg_sent := sysdate - (l_worklist_definition.where_sent_start - TO_DATE('01/01/2000', 'MM/DD/YYYY'));
623
624 ELSE
625
626 lbeg_sent := null;
627
628 END IF;
629
630 lend_sent := l_worklist_definition.where_sent_end;
631 lbeg_due := l_worklist_definition.where_due_start;
632 lend_due := l_worklist_definition.where_due_end;
633 lpriority := l_worklist_definition.where_priority;
634 ldbm := TO_NUMBER(l_worklist_definition.where_notif_del_by_me);
635
636 -- See if user over-ride argument requested
637 if (luser is not null) then
638 -- Check that current user has WF_ADMIN_ROLE privileges
639 admin_role := wf_core.translate('WF_ADMIN_ROLE');
640 if (admin_role <> '*' and
641 not Wf_Directory.IsPerformer(username, admin_role)) then
642 Wf_Core.Token('UNAME', username);
643 Wf_Core.Token('ROLE', admin_role);
644 Wf_Core.Raise('WFMON_ADMIN_ROLE');
645 end if;
646 -- Over-ride current user with argument
647 username := luser;
648 end if;
649
650 OPEN c_fetch_col_def (TO_NUMBER(plug_id), username);
651
652 /*
653 ** Loop through all the lookup_code rows for the given lookup_type
654 ** filling in the p_wf_lookups_tbl
655 */
656 l_record_num := 0;
657
658 LOOP
659
660 l_record_num := l_record_num + 1;
661
662 FETCH c_fetch_col_def INTO
663 l_worklist_col_definition(l_record_num);
664
665 EXIT WHEN c_fetch_col_def%NOTFOUND;
666
667 END LOOP;
668
669 CLOSE c_fetch_col_def;
670
671 IF (l_record_num = 1) THEN
672
673 l_record_num := 0;
674
675 OPEN c_fetch_col_def (-1, '-1');
676
677 /*
678 ** Loop through all the lookup_code rows for the given lookup_type
679 ** filling in the p_wf_lookups_tbl
680 */
681 LOOP
682
683 l_record_num := l_record_num + 1;
684
685 FETCH c_fetch_col_def INTO
686 l_worklist_col_definition(l_record_num);
687
688 EXIT WHEN c_fetch_col_def%NOTFOUND;
689
690 END LOOP;
691
692 END IF;
693
694
695 username := upper(username);
696 wf_directory.GetRoleInfo(username, realname, s0, s0, s0, s0);
697
698 -- Fetch user orig_system_ids for indexes in main cursor
699 begin
700 colon := instr(username, ':');
701 if (colon = 0) then
702 select WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID
703 into uorig_system, uorig_system_id
704 from WF_ROLES WR
705 where WR.NAME = username
706 and WR.ORIG_SYSTEM not in ('HZ_PARTY','POS','ENG_LIST','AMV_CHN',
707 'HZ_GROUP','CUST_CONT');
708 else
709 select WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID
710 into uorig_system, uorig_system_id
711 from WF_ROLES WR
712 where WR.ORIG_SYSTEM = substr(username, 1, colon-1)
713 and WR.ORIG_SYSTEM_ID = substr(username, colon+1)
714 and WR.NAME = username;
715 end if;
716 exception
717 when no_data_found then
718 wf_core.token('ROLE', username);
719 wf_core.raise('WFNTF_ROLE');
720 end;
721
722 /*
723 ** This is a little confusing but its a pretty cool routine.
724 ** High priority = 0 - 33
725 ** Med priority = 34 - 66
726 ** Low priority = 67 - 100
727 **
728 ** Set the initial ranges for the select to be high and low priority items
729 ** If you medium is also selected then add the medium range to the top of
730 ** of the high priority range.
731 ** Then check for the other flags. If they are not turned on then remove
732 ** them from the priority ranges
733 */
734
735 high_bottom_value := 0;
736 high_top_value := 33;
737 low_bottom_value := 67;
738 low_top_value := 100;
739
740 IF (INSTR(lpriority, 'M') > 0) THEN
741
742 high_top_value := 66;
743
744 END IF;
745
746 IF (INSTR(lpriority, 'L') = 0) THEN
747
748 low_bottom_value := 0;
749 low_top_value := 0;
750
751 END IF;
752
753 IF (INSTR(lpriority, 'H') = 0) THEN
754
755 high_bottom_value := 34;
756
757 END IF;
758
759 -- If there are no notifications, display a message and exit
760 open wl_cursor (high_bottom_value, high_top_value,
761 low_bottom_value, low_top_value);
762
763 fetch wl_cursor into notrec;
764 if wl_cursor%NOTFOUND then
765 /*
766 htp.p(lorderkey||':'||lstatus||':'||luser
767 ||':'||lfromuser||':'||littype||':'||lsubject||':'||lbeg_sent
768 ||':'||lend_sent||':'||lbeg_due||':'||lend_due||':'||lpriority
769 ||':'||ldbm);
770 */
771 close wl_cursor;
772 htp.tableOpen('border=0 cellspacing=0 cellpadding=0 width=100%');
773 htp.tableRowOpen;
774 htp.p('<TD>');
775 IF (display_name IS NULL) THEN
776
777 icx_plug_utilities.plugbanner(wf_core.translate('WFA_WTITLE'),
778 owa_util.get_owa_service_path||'wf_plug.edit_worklist_definition?'||
779 'p_plug_id='||plug_id, 'FNDALERT.gif');
780
781 ELSE
782
783 icx_plug_utilities.plugbanner(display_name,
784 owa_util.get_owa_service_path||'wf_plug.edit_worklist_definition?'||
785 'p_plug_id='||plug_id, 'FNDALERT.gif');
786
787 END IF;
788 htp.p('</TD>');
789 htp.tableRowClose;
790 htp.tableClose;
791
792 htp.p(wf_core.translate('WFA_NO_HOME_NOTIFY'));
793 htp.br;
794
795 return;
796
797 end if;
798 /*
799 htp.p(lorderkey||':'||lstatus||':'||luser
800 ||':'||lfromuser||':'||littype||':'||lsubject||':'||lbeg_sent
801 ||':'||lend_sent||':'||lbeg_due||':'||lend_due||':'||lpriority
802 ||':'||ldbm);
803 */
804
805 -- If this is a plug then show title bar a customize reference
806 IF (plug_id IS NOT NULL) THEN
807
808 htp.tableOpen('border=0 cellspacing=0 cellpadding=0 width=100%');
809 htp.tableRowOpen;
810 htp.p('<TD>');
811 IF (display_name IS NULL) THEN
812
813 icx_plug_utilities.plugbanner(wf_core.translate('WFA_WTITLE'),
814 owa_util.get_owa_service_path||'wf_plug.edit_worklist_definition?'||
815 'p_plug_id='||plug_id, 'FNDALERT.gif');
816
817 ELSE
818
819 icx_plug_utilities.plugbanner(display_name,
820 owa_util.get_owa_service_path||'wf_plug.edit_worklist_definition?'||
821 'p_plug_id='||plug_id, 'FNDALERT.gif');
822 end if;
823
824 htp.p('</TD>');
825 htp.p('<tr><td><font size=-2><BR></font></td></tr>');
826 htp.tableRowClose;
827 htp.tableClose;
828
829 END IF;
830
831 -- There are some notifications for the user. Construct the page.
832 htp.tableOpen('border=0 cellpadding=1 cellspacing=0 width=100%');
833
834 IF (l_worklist_col_definition.count > 1) THEN
835
836 -- Column headers
837 htp.tableRowOpen(cattributes=>'bgcolor='||icx_plug_utilities.plugbgcolor);
838
839 htp.tabledata(cvalue=>' ');
840
841 FOR l_record_num IN 1..l_worklist_col_definition.count LOOP
842
843 IF (l_worklist_col_definition(l_record_num).column_name =
844 'MESSAGE_TYPE') THEN
845
846 htp.tableData('<font color=#000000 size=3>'||wf_core.translate('TYPE')||'</font>', 'Left');
847
848 END IF;
849
850 IF (l_worklist_col_definition(l_record_num).column_name =
851 'SUBJECT') THEN
852
853 htp.tableData('<font color=#000000 size=3>'||wf_core.translate('SUBJECT')||'</font>', 'Left');
854
855 END IF;
856
857 IF (l_worklist_col_definition(l_record_num).column_name =
858 'BEGIN_DATE') THEN
859
860 htp.tableData('<font color=#000000 size=3>'||wf_core.translate('BEGIN_DATE')||'</font>', 'Left');
861
862 END IF;
863
864 IF (l_worklist_col_definition(l_record_num).column_name =
865 'DUE_DATE') THEN
866
867 htp.tableData('<font color=#000000 size=3>'||wf_core.translate('DUE_DATE')||'</font>', 'Left');
868
869 END IF;
870
871 END LOOP;
872
873 htp.tableRowClose;
874
875 -- Print line
876 /*
877 htp.p('<TR height=1 bgcolor=' || icx_plug_utilities.plugbannercolor ||
878 '>');
879 htp.p('<TD height=1></TD><TD height=1 colspan=' ||
880 l_worklist_col_definition.count ||
881 '><IMG SRC=/OA_MEDIA/FND' || icx_plug_utilities.plugcolorscheme
882 || 'UDT.gif width=1 height=1></TD>');
883 htp.tableRowClose;
884 */
885
886 END IF;
887
888 -- Worklist
889 loop
890 -- Figure out the priority first
891
892 n_priority := null;
893
894 if (notrec.priority < 35) then
895 n_priority := '/OA_MEDIA/'||'high.gif';
896 else
897 if (notrec.priority > 65) then
898 n_priority := '/OA_MEDIA/'||'low.gif';
899 end if;
900 end if;
901
902 -- Displaying a row
903 htp.tableRowOpen(cvalign=>'TOP', cattributes=>'bgcolor=white');
904
905 IF (n_priority IS NULL) THEN
906
907 htp.tableData(cvalue=>' ');
908
909 ELSE
910
911 htp.tableData(cvalue=>htf.img(curl=>n_priority));
912
913 END IF;
914
915 FOR l_record_num IN 1..l_worklist_col_definition.count LOOP
916
917 IF (l_worklist_col_definition(l_record_num).column_name =
918 'MESSAGE_TYPE') THEN
919
920 htp.tableData(cvalue=>'<font color=#000000 size=3>'||
921 notrec.message_type, calign=>'left');
922
923 END IF;
924
925 IF (l_worklist_col_definition(l_record_num).column_name =
926 'SUBJECT') THEN
927
928 if (notrec.language is null or notrec.language <> userenv('LANG')) then
929
930 Wfa_Html_Util.GetDenormalizedValues(notrec.nid, userenv('LANG'),
931 nf_from_user, nf_to_user, nf_subject);
932
933 htp.tableData(cvalue=>'<font size=3>'||
934 htf.anchor(owa_util.get_owa_service_path||
935 Wfa_Sec.DetailURL(notrec.nid),
936 ctext=>nf_subject, cattributes=>'TARGET="_top"'),
937 calign=>'left');
938
939 else
940
941 htp.tableData( cvalue=>'<font size=3>'||
942 htf.anchor(owa_util.get_owa_service_path||
943 Wfa_Sec.DetailURL(notrec.nid),
944 ctext=>notrec.subject, cattributes=>'TARGET="_top"'),
945 calign=>'left');
946
947 end if;
948
949 END IF;
950
951 IF (l_worklist_col_definition(l_record_num).column_name =
952 'BEGIN_DATE') THEN
953
954 htp.tableData(cvalue=>'<font color=#000000 size=3>'||
955 to_char(notrec.begin_date ), calign=>'left',
956 cnowrap=>1);
957
958 END IF;
959
960 IF (l_worklist_col_definition(l_record_num).column_name =
961 'DUE_DATE') THEN
962
963 htp.tableData(cvalue=>'<font color=#000000 size=3>'||
964 nvl(to_char(notrec.due_date), '<BR>'),
965 calign=>'left', cnowrap=>1);
966
967 END IF;
968
969 END LOOP;
970
971 htp.tableRowClose;
972
973 <<skip_it>>
974 fetch wl_cursor into notrec;
975 exit when wl_cursor%NOTFOUND;
976 end loop;
977 close wl_cursor;
978
979 htp.tableClose;
980
981 exception
982 when others then
983 rollback;
984 if (wl_cursor%isopen) then
985 close wl_cursor;
986 end if;
987 if (attrs%isopen) then
988 close attrs;
989 end if;
990 wf_core.context('Wf_Plug','WorkList');
991 wf_plug.Error;
992 end Worklist;
993
994
995 /*===========================================================================
996 PROCEDURE NAME: edit_worklist_definition
997
998 DESCRIPTION: Allows you to modify the look and feel of your
999 worklist. This definition mechanism is used
1000 for both the standard Worklist UI as well as the
1001 plug UI.
1002
1003 If the p_plug_id = '0' then it assumes you are
1004 defining the default look and feel for the
1005 Worklist plug
1006
1007 If the p_username = '0' then it assumes you are
1008 defining the default look and feel for the
1009 standard Worklist UI.
1010
1011 ============================================================================*/
1012
1013 PROCEDURE edit_worklist_definition (p_plug_id IN VARCHAR2 DEFAULT null,
1014 p_username IN VARCHAR2 DEFAULT null,
1015 p_add_column IN VARCHAR2 DEFAULT '0') IS
1016
1017
1018 l_no_parameters_passed BOOLEAN := FALSE;
1019 l_definition_exists VARCHAR2(1) := 'Y';
1020 l_record_num NUMBER := 0;
1021 ii NUMBER := 0;
1022 l_columns_to_show NUMBER := 0;
1023 l_size NUMBER := 0;
1024 l_username varchar2(320); -- Username to query
1025 l_realname varchar2(360); -- Display name of username
1026 l_admin_role varchar2(320); -- Role for admin mode
1027 s0 varchar2(2000);
1028 lang_codeset varchar2(50); -- Language Codeset from environment
1029 -- (e.g. WE8ISO8859P1)
1030
1031 l_worklist_definition wf_plug.wf_worklist_definition_record;
1032 l_worklist_col_definition wf_plug.wf_worklist_col_def_table;
1033
1034
1035 CURSOR lkcurs(lktype in varchar2) IS
1036 SELECT WL.MEANING, WL.LOOKUP_CODE
1037 FROM WF_LOOKUPS WL
1038 WHERE WL.LOOKUP_TYPE = lktype
1039 AND WL.LOOKUP_CODE IN ('SUBJECT', 'DUE_DATE', 'BEGIN_DATE', 'MESSAGE_TYPE')
1040 ORDER BY WL.MEANING;
1041
1042 CURSOR lkcurs_order(lktype in varchar2) IS
1043 SELECT WL.MEANING, WL.LOOKUP_CODE
1044 FROM WF_LOOKUPS WL
1045 WHERE WL.LOOKUP_TYPE = lktype
1046 AND WL.LOOKUP_CODE IN ('SUBJECT', 'DUE_DATE', 'BEGIN_DATE', 'MESSAGE_TYPE', 'PRIORITY')
1047 ORDER BY WL.MEANING;
1048
1049 CURSOR c_fetch_col_def (c_plug_id IN NUMBER,
1050 c_username IN VARCHAR2) IS
1051 SELECT
1052 ROWID ROW_ID,
1053 PLUG_ID,
1054 USERNAME,
1055 COLUMN_NUMBER,
1056 COLUMN_NAME,
1057 COLUMN_SIZE
1058 FROM WF_WORKLIST_COL_DEFINITIONS
1059 WHERE (PLUG_ID = c_plug_id and c_plug_id IS NOT NULL)
1060 OR (USERNAME = c_username and c_username IS NOT NULL and c_plug_id IS NULL)
1061 ORDER BY COLUMN_NUMBER;
1062
1063
1064 BEGIN
1065
1066 -- Check session and current user
1067 wfa_sec.GetSession(l_username);
1068 l_username := upper(l_username);
1069 wf_directory.GetRoleInfo(l_username, l_realname, s0, s0, s0, s0);
1070
1071 -- Get language codeset
1072 lang_codeset := substr(userenv('LANGUAGE'),instr(userenv('LANGUAGE'),'.')+1,
1073 length(userenv('LANGUAGE')));
1074
1075 -- Make sure you have either the definition that the user created or the
1076 -- default definition
1077 get_plug_definition (p_plug_id, l_worklist_definition);
1078
1079 -- Get the plug definition if the plug_id is not null
1080 IF (p_plug_id IS NOT NULL OR p_username IS NOT NULL) THEN
1081
1082 BEGIN
1083
1084 SELECT ROWID ROW_ID,
1085 PLUG_ID,
1086 USERNAME,
1087 DEFINITION_NAME,
1088 WHERE_STATUS,
1089 WHERE_FROM,
1090 WHERE_ITEM_TYPE,
1091 WHERE_NOTIF_TYPE,
1092 WHERE_SUBJECT,
1093 WHERE_SENT_START,
1094 WHERE_SENT_END,
1095 WHERE_DUE_START,
1096 WHERE_DUE_END,
1097 WHERE_PRIORITY,
1098 WHERE_NOTIF_DEL_BY_ME,
1099 ORDER_PRIMARY,
1100 ORDER_ASC_DESC
1101 INTO l_worklist_definition
1102 FROM WF_WORKLIST_DEFINITIONS
1103 WHERE (PLUG_ID = p_plug_id AND p_plug_id IS NOT NULL)
1104 OR (USERNAME = p_username AND p_username IS NOT NULL and p_plug_id IS NULL);
1105
1106 EXCEPTION
1107 WHEN NO_DATA_FOUND THEN
1108 l_definition_exists := 'N';
1109 WHEN OTHERS THEN
1110 RAISE;
1111 END;
1112
1113 -- If the definition exists then go get the column definition information
1114 IF (l_definition_exists = 'Y') THEN
1115
1116 OPEN c_fetch_col_def (l_worklist_definition.plug_id,
1117 l_worklist_definition.username);
1118
1119 /*
1120 ** Loop through all the lookup_code rows for the given lookup_type
1121 ** filling in the p_wf_lookups_tbl
1122 */
1123 LOOP
1124
1125 l_record_num := l_record_num + 1;
1126
1127 FETCH c_fetch_col_def INTO
1128 l_worklist_col_definition(l_record_num);
1129
1130 EXIT WHEN c_fetch_col_def%NOTFOUND;
1131
1132 END LOOP;
1133
1134 CLOSE c_fetch_col_def;
1135
1136 END IF;
1137
1138 -- Must have either a plug_id or username passed in otherwise show an error
1139 ELSE
1140
1141 l_no_parameters_passed := TRUE;
1142 Wf_Core.Context('wf_plug', 'edit_worklist_definition',
1143 'missing parameter values');
1144 wf_plug.Error;
1145 return;
1146
1147 END IF;
1148
1149 -- Page title
1150 htp.htmlOpen;
1151 htp.headOpen;
1152 htp.title(ctitle=>wf_core.translate('WF_EDIT_WORKLIST_TITLE'));
1153
1154 htp.headClose;
1155 htp.p('<BODY bgcolor='||icx_plug_utilities.bgcolor||'>');
1156
1157 -- Open body and draw standard header
1158 if (p_plug_id =0) then
1159 icx_plug_utilities.toolbar(p_text => wf_core.translate('WF_EDIT_WORKLIST_TITLE'),
1160 p_disp_mainmenu => 'N',
1161 p_disp_menu => 'N');
1162 else
1163 icx_plug_utilities.toolbar(p_text => icx_plug_utilities.getPlugTitle(p_plug_id),
1164 p_disp_mainmenu => 'N',
1165 p_disp_menu => 'N');
1166 end if;
1167
1168 htp.tableopen;
1169
1170 htp.tablerowopen;
1171
1172 htp.p('<TD> </TD>');
1173
1174 htp.p('<TD>'||wf_core.translate('WF_EDIT_WORKLIST_HELP')||'</TD>');
1175
1176 htp.p('<TD> </TD>');
1177
1178 htp.tablerowclose;
1179
1180 htp.tableclose;
1181
1182 htp.tableopen(calign=>'CENTER');
1183
1184 htp.tablerowopen;
1185
1186 htp.p('<TD>');
1187
1188 -- wf_dsk.submit_dsk is the url(procedure) to which the contents
1189 -- of this form is sent
1190 htp.p('<FORM NAME="WFPLUG" ACTION="wf_plug.submit_worklist_definition" METHOD="POST">');
1191
1192 htp.formHidden(cname=>'plug_id', cvalue=>p_plug_id);
1193 htp.formHidden(cname=>'username', cvalue=>p_username);
1194 htp.formHidden(cname=>'definition_name', cvalue=>null);
1195
1196 htp.bold(wf_core.translate('WF_EDIT_WORKLIST_COLUMNS'));
1197 htp.br;
1198 htp.br;
1199
1200
1201 -- There are some notifications for the user. Construct the page.
1202 htp.tableOpen;
1203
1204 -- Column Titles
1205 htp.tableRowOpen;
1206
1207 htp.p('<TD>');
1208
1209 -- Create the column titles table inside the outer tablex
1210 htp.p('<TABLE border=1 cellpadding=2 bgcolor=white>');
1211
1212 htp.tableRowOpen(cattributes=>'bgcolor='||icx_plug_utilities.plugbannercolor);
1213
1214 -- If the p_add_columns to show has been passed in that means the user
1215 -- has asked to add a column to the list.
1216 l_columns_to_show := 4;
1217
1218 FOR l_record_num IN 1..l_columns_to_show LOOP
1219
1220 htp.p('<TH><font color=#000000>'||
1221 wf_core.translate('COLUMN')||' '||
1222 TO_CHAR(l_record_num)||'</font></TD>');
1223
1224 END LOOP;
1225
1226 htp.tableRowClose;
1227
1228 htp.tableRowOpen(cattributes=>'bgcolor='||icx_plug_utilities.plugbannercolor);
1229
1230 -- Display the LOV's for the column names that are in each column
1231 htp.tableRowOpen;
1232
1233 FOR l_record_num IN 1..l_columns_to_show LOOP
1234
1235 htp.p('<TD>');
1236
1237 htp.formSelectOpen(cname=>'COLUMN_NAME');
1238
1239 -- Loop on each of the possible column names that can be selected
1240 -- for a given column.
1241 -- Need to make this a table so you only select it once.
1242 FOR orderby IN lkcurs('WFSTD_WLORDERBY') LOOP
1243 IF (l_record_num <= l_worklist_col_definition.count AND
1244 l_worklist_col_definition(l_record_num).column_name =
1245 orderby.lookup_code) THEN
1246 htp.formSelectOption(cvalue=>orderby.meaning,
1247 cselected => 'SELECTED',
1248 cattributes=>'value='||orderby.lookup_code);
1249 ELSE
1250 htp.formSelectOption(cvalue=>orderby.meaning,
1251 cattributes=>'value='||orderby.lookup_code);
1252 END IF;
1253
1254 END LOOP;
1255
1256 IF (l_record_num > l_worklist_col_definition.count) THEN
1257
1258 htp.formSelectOption(cvalue=>wf_core.translate('BLANK'),
1259 cselected => 'SELECTED',
1260 cattributes=>'value=NULL');
1261
1262 ELSE
1263
1264 htp.formSelectOption(cvalue=>wf_core.translate('BLANK'),
1265 cattributes=>'value=NULL');
1266
1267 END IF;
1268
1269 htp.formSelectClose;
1270
1271 htp.p('</TD>');
1272
1273 END LOOP;
1274
1275 htp.tableRowClose;
1276
1277 htp.tableClose;
1278
1279 htp.p('<TD></TD>');
1280
1281 htp.p('<TD>');
1282
1283 -- Create the order column title table inside the outer tablex
1284 htp.p('<TABLE border=1 cellpadding=2 bgcolor=white>');
1285
1286 htp.tableRowOpen(cattributes=>'bgcolor='||icx_plug_utilities.plugbannercolor);
1287
1288 htp.p('<TH>'||wf_core.translate('ORDERBY')||'</TD>');
1289
1290 htp.tableRowClose;
1291
1292 htp.tableRowOpen;
1293
1294 htp.p('<TD>');
1295
1296 htp.formSelectOpen('orderkey');
1297 for orderby in lkcurs_order('WFSTD_WLORDERBY') loop
1298 if (orderby.lookup_code = l_worklist_definition.order_primary) then
1299 htp.formSelectOption(cvalue=>orderby.meaning,
1300 cselected => 'SELECTED',
1301 cattributes=>'value='||orderby.lookup_code);
1302 else
1303 htp.formSelectOption(cvalue=>orderby.meaning,
1304 cattributes=>'value='||orderby.lookup_code);
1305 end if;
1306 end loop;
1307
1308 htp.formSelectClose;
1309
1310 htp.p('</TD>');
1311
1312 htp.tableRowClose;
1313
1314 htp.tableClose; -- Worklist display
1315
1316 htp.tableClose; -- outer table
1317
1318 -- Place an extra space between the other search criteria and order by
1319 htp.br;
1320
1321 htp.bold(wf_core.translate('WF_EDIT_WORKLIST_CRITERIA'));
1322
1323 htp.tableOpen; -- Show me only
1324
1325 -- Add blank row
1326 htp.tableRowOpen;
1327 htp.tableData(htf.br);
1328 htp.tableRowClose;
1329
1330 find_criteria (username=>l_username,
1331 status=>l_worklist_definition.where_status,
1332 fromuser=>l_worklist_definition.where_from,
1333 ittype=>l_worklist_definition.where_item_type,
1334 msubject=>l_worklist_definition.where_subject,
1335 beg_sent=>l_worklist_definition.where_sent_start,
1336 end_sent=>l_worklist_definition.where_sent_end,
1337 beg_due=>l_worklist_definition.where_due_start,
1338 end_due=>l_worklist_definition.where_due_end,
1339 priority=>l_worklist_definition.where_priority,
1340 delegated_by_me=>l_worklist_definition.where_notif_del_by_me,
1341 orderkey=>l_worklist_definition.order_primary,
1342 customize=>'Y');
1343
1344 -- Add blank row
1345 htp.tableRowOpen;
1346 htp.tableData(htf.br);
1347 htp.tableRowClose;
1348
1349 htp.formHidden(cname=>'definition_exists', cvalue=>l_definition_exists);
1350 htp.tableClose; -- Show me only
1351
1352 htp.p('<CENTER>');
1353 htp.tableOpen; -- OK
1354 -- Add submit button
1355 htp.tableRowOpen;
1356
1357 htp.p('<TD width=50% align="right">');
1358
1359 icx_plug_utilities.buttonleft(wf_core.translate('WF_EDIT_LONG_OK'),'javascript:document.WFPLUG.submit()', 'FNDJLFOK.gif');
1360 -- htp.p('<A href=javascript:document.WFPLUG.submit()> OK </A>');
1361
1362 htp.p('</TD><TD width=50% align="left">');
1363
1364
1365 -- fixed bug for cancel button
1366 icx_plug_utilities.buttonright(wf_core.translate('CANCEL'),'javascript:history.back()', 'FNDJLFCN.gif');
1367 -- icx_plug_utilities.buttonright(wf_core.translate('CANCEL'),icx_plug_utilities.MainMenulink, 'FNDJLFCN.gif');
1368
1369 htp.p('</TD>');
1370
1371 htp.tableRowClose;
1372
1373 htp.tableClose; -- OK
1374 htp.p('</CENTER>');
1375
1376 htp.formClose;
1377
1378 htp.p('</TD>');
1379
1380 htp.tablerowclose;
1381
1382 htp.tableclose;
1383
1384 wfa_sec.footer;
1385 htp.htmlClose;
1386
1387 exception
1388 when others then
1389 Wf_Core.Context('wf_plug', 'edit_worklist_definition',
1390 p_plug_id, p_username);
1391 wf_plug.Error;
1392
1393 END edit_worklist_definition;
1394
1395 /*===========================================================================
1396 PROCEDURE NAME: submit_worklist_definition
1397
1398 DESCRIPTION: Saves the worklist definition in the database.
1399
1400 ============================================================================*/
1401 PROCEDURE submit_worklist_definition (
1402 plug_id IN VARCHAR2 DEFAULT NULL,
1403 username IN VARCHAR2 DEFAULT NULL,
1404 definition_name IN VARCHAR2 DEFAULT NULL,
1405 column_name IN column_name_array,
1406 status IN VARCHAR2 DEFAULT '*',
1407 fromuser IN VARCHAR2 DEFAULT '*',
1408 user IN VARCHAR2 DEFAULT NULL,
1409 ittype IN VARCHAR2 DEFAULT '*',
1410 msubject IN VARCHAR2 DEFAULT '*',
1411 beg_sent IN VARCHAR2 DEFAULT '*',
1412 end_sent IN VARCHAR2 DEFAULT '*',
1413 beg_due IN VARCHAR2 DEFAULT '*',
1414 end_due IN VARCHAR2 DEFAULT '*',
1415 hpriority IN VARCHAR2 DEFAULT null,
1416 mpriority IN VARCHAR2 DEFAULT null,
1417 lpriority IN VARCHAR2 DEFAULT null,
1418 delegated_by_me IN VARCHAR2 DEFAULT '0',
1419 orderkey IN VARCHAR2 DEFAULT 'PRIORITY',
1420 definition_exists IN VARCHAR2 DEFAULT 'N'
1421 )
1422 IS
1423
1424 l_plug_id VARCHAR2(30) := plug_id;
1425 l_username VARCHAR2(320) := username;
1426 l_definition_name VARCHAR2(30) := definition_name;
1427 l_status VARCHAR2(30) := NVL(status, '*');
1428 l_fromuser VARCHAR2(320) := NVL(fromuser, '*');
1429 l_user VARCHAR2(320) := user;
1430 l_ittype VARCHAR2(8) := NVL(ittype, '*');
1431 l_msubject VARCHAR2(240) := NVL(msubject, '*');
1432 l_beg_sent DATE;
1433 l_end_sent DATE;
1434 l_beg_due DATE;
1435 l_end_due DATE;
1436 l_priority VARCHAR2(10) := NVL(hpriority||mpriority||lpriority, '*');
1437 l_delegated_by_me VARCHAR2(1) := NVL(delegated_by_me, '0');
1438 l_orderkey VARCHAR2(30) := NVL(orderkey, 'PRIORITY');
1439 l_column_name VARCHAR2(30) := null;
1440 l_column_size NUMBER := 0;
1441 l_columns_to_show NUMBER := 0;
1442
1443 BEGIN
1444
1445 -- Check session and current user
1446 wfa_sec.GetSession(l_username);
1447 l_username := upper(l_username);
1448
1449 IF (beg_sent IS NOT NULL) THEN
1450
1451 l_beg_sent := TO_DATE('01/01/2000', 'MM/DD/YYYY') + TO_NUMBER(beg_sent);
1452
1453 END IF;
1454
1455
1456 IF (definition_exists = 'N') THEN
1457
1458 INSERT INTO WF_WORKLIST_DEFINITIONS
1459 ( PLUG_ID ,
1460 USERNAME ,
1461 DEFINITION_NAME ,
1462 WHERE_STATUS ,
1463 WHERE_FROM ,
1464 WHERE_ITEM_TYPE ,
1465 WHERE_SUBJECT ,
1466 WHERE_SENT_START ,
1467 WHERE_SENT_END ,
1468 WHERE_DUE_START ,
1469 WHERE_DUE_END ,
1470 WHERE_PRIORITY ,
1471 WHERE_NOTIF_DEL_BY_ME ,
1472 ORDER_PRIMARY
1473 )
1474 VALUES
1475 (
1476 TO_NUMBER(l_plug_id),
1477 l_username,
1478 l_definition_name,
1479 l_status,
1480 l_fromuser,
1481 l_ittype,
1482 l_msubject,
1483 l_beg_sent,
1484 l_end_sent,
1485 l_beg_due,
1486 l_end_due,
1487 l_priority,
1488 l_delegated_by_me,
1489 l_orderkey
1490 );
1491
1492 ELSE
1493
1494 UPDATE WF_WORKLIST_DEFINITIONS
1495 SET DEFINITION_NAME = l_definition_name,
1496 WHERE_STATUS = l_status,
1497 WHERE_FROM = l_fromuser,
1498 WHERE_ITEM_TYPE = l_ittype,
1499 WHERE_SUBJECT = l_msubject,
1500 WHERE_SENT_START = l_beg_sent,
1501 WHERE_SENT_END = l_end_sent,
1502 WHERE_DUE_START = l_beg_due,
1503 WHERE_DUE_END = l_end_due,
1504 WHERE_PRIORITY = l_priority,
1505 WHERE_NOTIF_DEL_BY_ME = l_delegated_by_me,
1506 ORDER_PRIMARY = l_orderkey
1507 WHERE PLUG_ID = TO_NUMBER(l_plug_id);
1508
1509 -- Delete all the old column definitions
1510 DELETE FROM WF_WORKLIST_COL_DEFINITIONS
1511 WHERE PLUG_ID = TO_NUMBER(l_plug_id);
1512
1513 END IF;
1514
1515 -- Insert the new column definitions
1516 FOR l_record_num IN 1..column_name.count LOOP
1517
1518 l_column_name := column_name(l_record_num);
1519
1520 -- The user deletes a column by selecting delete from the poplist.
1521 -- Since all the columns are already deleted you can just skip it
1522 -- here.
1523 IF (l_column_name <> 'NULL') THEN
1524
1525 INSERT INTO WF_WORKLIST_COL_DEFINITIONS
1526 ( PLUG_ID ,
1527 USERNAME ,
1528 DEFINITION_NAME ,
1529 COLUMN_NUMBER ,
1530 COLUMN_NAME ,
1531 COLUMN_SIZE
1532 )
1533 VALUES
1534 (
1535 TO_NUMBER(l_plug_id),
1536 l_username,
1537 l_definition_name,
1538 l_record_num,
1539 l_column_name,
1540 100
1541 );
1542
1543 END IF;
1544
1545 END LOOP;
1546
1547 icx_plug_utilities.gotomainmenu;
1548
1549 exception
1550 when others then
1551 Wf_Core.Context('wf_plug', 'submit_worklist_definition',
1552 plug_id, username);
1553 wf_plug.Error;
1554
1555 END submit_worklist_definition;
1556
1557 /*===========================================================================
1558 PROCEDURE NAME: worklist_plug
1559
1560 DESCRIPTION: creates the worklist plug for the ICX folks for
1561 the customizable home page
1562
1563 ============================================================================*/
1564 PROCEDURE worklist_plug (
1565 p_session_id IN VARCHAR2 DEFAULT NULL,
1566 p_plug_id IN VARCHAR2 DEFAULT NULL,
1567 p_display_name IN VARCHAR2 DEFAULT NULL,
1568 p_delete IN VARCHAR2 DEFAULT 'N') IS
1569
1570 BEGIN
1571
1572 IF (p_delete = 'Y') THEN
1573
1574 -- Delete all the old column definitions
1575 DELETE FROM WF_WORKLIST_COL_DEFINITIONS
1576 WHERE PLUG_ID = TO_NUMBER(p_plug_id) ;
1577
1578 -- Delete all the old plug definition
1579 DELETE FROM WF_WORKLIST_DEFINITIONS
1580 WHERE PLUG_ID = TO_NUMBER(p_plug_id) ;
1581
1582
1583 ELSE
1584
1585 wf_plug.worklist (plug_id=>p_plug_id, session_id=>p_session_id,
1586 display_name=>p_display_name);
1587
1588 END IF;
1589
1590 COMMIT;
1591
1592 exception
1593 when others then
1594 Wf_Core.Context('wf_plug', 'worklist_plug',
1595 p_session_id,p_plug_id,p_delete);
1596 wf_plug.Error;
1597
1598 END worklist_plug;
1599
1600 end WF_PLUG;