1 package body WFA_HTML_UTIL as
2 /* $Header: wfhtmb.pls 120.5.12010000.4 2009/01/14 09:14:16 sudchakr ship $ */
3
4
5 --
6 -- Package Globals
7 --
8 result_button_threshold pls_integer := 3; -- Max number of submit buttons
9
10 --
11 -- GetUrl (PRIVATE)
12 -- Produce URL link in response portion
13 -- IN
14 -- nid -
15 -- description - instructions
16 -- value - url string not token substituted
17 procedure GetUrl(
18 nid in number,
19 description in varchar2,
20 value in varchar2)
21 as
22 urlstring varchar2(1950);
23 begin
24 -- Ignore if no URL provided
25 if (value is null) then
26 return;
27 end if;
28
29 htp.tableRowOpen;
30
31 -- Include description if needed.
32 -- NOTE: Description are printed here instead of in the prompt link
33 -- as for other fields, because the prompt is already used for the
34 -- URL itself.
35 if (description is not null) then
36 htp.tableData(description, 'right', cattributes=>'id=""');
37 else
38 htp.tableData(htf.br, cattributes=>'id=""');
39 end if;
40
41 -- Print URL
42 urlstring:=wf_notification.GetURLText(value, nid);
43
44 -- Bug 4634849
45 urlstring := wfa_html.encode_url(urlstring);
46 htp.p('<td id=""> <a href="'||urlstring||'">'||urlstring||'</a></td>');
47
48 htp.tableRowClose;
49
50 exception
51 when others then
52 wf_core.context('Wfa_Html_Util', 'GetUrl', value, description, to_char(nid));
53 raise;
54 end GetUrl;
55
56 --
57 -- GetField (PRIVATE)
58 -- Produce a varchar2/number/date response field
59 -- IN
60 -- name - field name
61 -- type - field type (VARCHAR2, NUMBER, DATE)
62 -- format - format mask
63 -- dvalue - default value
64 -- index - the attribute element number in the attribute list
65 --
66 procedure GetField(
67 name in varchar2,
68 type in varchar2,
69 format in varchar2,
70 dvalue in varchar2,
71 index_num in number,
72 nid in number,
73 nkey in varchar2)
74 is
75 len pls_integer;
76 l_url varchar2(1000);
77 l_media varchar2(240) := wfa_html.image_loc;
78 l_icon varchar2(30) := 'FNDILOV.gif';
79 l_username varchar2(320); -- Username to query
80 l_document_name Varchar2(240) := NULL;
81 l_callback_URL varchar2(4000);
82 l_attach_URL varchar2(4000);
83 l_document_attributes fnd_document_management.fnd_document_attributes;
84 l_message varchar2(400) := NULL;
85
86 slash pls_integer;
87 wfsession varchar2(240);
88
89 begin
90 -- Figure field len
91 if (type = 'VARCHAR2') then
92 len := nvl(to_number(format), 2000);
93 else
94 len := 62;
95 end if;
96
97 -- Draw field
98 htp.formHidden('h_fnames', name||'#'||type||'#'||format);
99
100 /* Need to create a hidden document name field if this is not a document
101 ** resond attrubute, otherwise the index will reference index elements
102 ** that don't exist because the element lists are out of sync
103 */
104 if (type NOT IN ('DOCUMENT' , 'ROLE')) THEN
105 htp.formHidden('h_fdocnames', '');
106 end if;
107
108 if (len <= 80) then
109
110 /*
111 ** If this is a document field then hide the actual h_fvalue field
112 ** Since this will store the document info that gets passed to the
113 ** document attribute. You then need to create a name field so the
114 ** user can see what they've selected.
115 */
116
117 if (type = 'DOCUMENT') THEN
118
119 htp.formhidden ('h_fvalues', dvalue);
120
121 -- Set the destination field name for the document id
122 fnd_document_management.set_document_id_html (
123 'bottom',
124 'WFNOTRESP',
125 'h_fvalues['||index_num||']',
126 'h_fdocnames['||index_num||']',
127 l_callback_url);
128
129 -- Check session and current user
130 if (nkey is null) then
131 wfa_sec.GetSession(l_username);
132 else
133 -- copy this from wfa_html.authenticate
134 slash := instr(nkey, '/');
135 if (slash <> 0) then
136 wfsession := to_char(nid)||'/'||substr(nkey, slash+1);
137 else
138 wfsession := to_char(nid)||'/'||nkey;
139 end if;
140
141 l_username := Wf_Notification.AccessCheck(wfsession);
142 if (l_username is null) then
143 wf_core.raise('WFNTF_ACCESS_KEY');
144 end if;
145
146 end if;
147 l_username := upper(l_username);
148
149 fnd_document_management.get_launch_attach_url (
150 l_username,
151 l_callback_url,
152 TRUE,
153 l_attach_url);
154
155 /*
156 ** If the default value is a dm document then go get the
157 ** title from the DM system and place it in the field. If
158 ** its a plsql doc then just put the default value in the field
159 */
160 IF (SUBSTR(dvalue, 1, 3) = 'DM:') THEN
161
162 /*
163 ** get the document name
164 */
165 fnd_document_management.get_document_attributes(l_username,
166 dvalue,
167 l_document_attributes);
168
169
170 l_document_name := l_document_attributes.document_name;
171
172 ELSE
173
174 l_document_name := dvalue;
175
176 END IF;
177
178 -- document field
179 htp.tableData(
180 cvalue=>htf.formText(cname=>'h_fdocnames', csize=>len-30,
181 cmaxlength=>len, cvalue=>l_document_name)||
182 '   '||
183 l_attach_URL,
184 calign=>'Left', cattributes=>'id=""');
185
186 else
187 -- single line field
188 -- htp.tableData(
189 -- cvalue=>htf.formText(cname=>'h_fvalues', csize=>len,
190 -- cmaxlength=>len, cvalue=>dvalue),
191 -- calign=>'Left');
192 htp.p('<TD ALIGN="LEFT" id="">');
193
194 if (type <> 'ROLE') then
195 htp.formText(cname=>'h_fvalues', csize=>len,
196 cmaxlength=>len, cvalue=>dvalue);
197 else
198
199 l_message := wf_core.translate ('WFPREF_LOV');
200
201 htp.formhidden ('h_fvalues', dvalue);
202
203 -- add LOV here: Note:bottom is name of frame.
204 -- Note: The REPLACE function replaces all the space characters with
205 -- the proper escape sequence.
206 l_url := 'javascript:fnd_open_dm_display_window('||''''||
207 REPLACE('wf_lov.display_lov?p_lov_name='||'owner'||
208 '&p_display_name='||'WFA_FIND_USER'||
209 '&p_validation_callback=wfa_html.wf_user_val'||
210 '&p_dest_hidden_field=top.opener.parent.bottom.document.WFNOTRESP.h_fvalues['||to_char(index_num)||'].value'||
211 '&p_current_value=top.opener.parent.bottom.document.WFNOTRESP.h_fdocnames['||to_char(index_num)||'].value'||
212 '&p_display_key='||'Y'||
213 '&p_dest_display_field=top.opener.parent.bottom.document.WFNOTRESP.h_fdocnames['||to_char(index_num)||'].value',
214 ' ', '%20')||''''||',500,500)';
215
216 -- print everything together so ther is no gap.
217 htp.p(htf.formText(cname=>'h_fdocnames', csize=>len, cmaxlength=>240,
218 cvalue=>dvalue)||
219 '<A href='||l_url||'>'||
220 '<IMG src="'||l_media||l_icon||'" border=0 alt="'||
221 l_message||'" onmouseover="window.status='||''''||
222 l_message||''''||';return true"></A>');
223 end if;
224 htp.p('</TD>');
225
226 end if;
227
228 else
229 -- multi line field
230 htp.tableData(
231 cvalue=>htf.formTextareaOpen2(
232 cname=>'h_fvalues',
233 nrows=>2,
234 ncolumns=>65,
235 cwrap=>'SOFT',
236 cattributes=>'maxlength='||to_char(len))||
237 dvalue||
238 htf.formTextareaClose,
239 calign=>'Left', cattributes=>'id=""');
240 end if;
241 exception
242 when others then
243 wf_core.context('Wfa_Html_Util', 'GetField', name, type, format, dvalue);
244 raise;
245 end GetField;
246
247 --
248 -- GetLookup (PRIVATE)
249 -- Produce a lookup response field
250 -- IN
251 -- name - field name
252 -- value - default value (lookup code)
253 -- format - lookup type
254 -- submit - flag include a submit button for result field
255 --
256 procedure GetLookup(
257 name in varchar2,
258 value in varchar2,
259 format in varchar2,
260 submit in boolean)
261 as
262 cursor lookup_codes(p_lookup_type varchar2) is
263 select MEANING, LOOKUP_CODE
264 from WF_LOOKUPS
265 where LOOKUP_TYPE = p_lookup_type
266 order by MEANING;
267
268 template varchar2(4000);
269
270 begin
271 -- Create hidden field and select list
272 template := htf.formHidden('h_fnames', name||'#LOOKUP#'||format)||
273 wf_core.newline||
274 htf.formSelectOpen('h_fvalues');
275
276
277 -- Add all lookups to select list
278 for i in lookup_codes(format) loop
279 if (i.lookup_code = value) then
280 template := template||wf_core.newline||
281 htf.formSelectOption(i.meaning, 'SELECTED','VALUE="'||i.lookup_code||'"');
282 else
283 template := template||wf_core.newline||
284 htf.formSelectOption(i.meaning,null,'VALUE="'||i.lookup_code||'"');
285 end if;
286 end loop;
287 template := template||wf_core.newline||htf.formSelectClose;
288
289 if (not submit) then
290 -- Draw a normal field
291 htp.tableData(template, 'left', cattributes=>'id=""');
292 htp.formHidden('h_fdocnames', '');
293
294 else
295 -- Draw a submit-style field for the result.
296 -- Leave TableData open so reassign button can be added to same cell.
297
298 htp.p('<TD ALIGN=left id="">'||wf_core.newline||template||'</TD>');
299
300 htp.p('<TD id="">');
301 wfa_html.create_reg_button ('javascript:document.WFNOTRESP.submit()',
302 wf_core.translate ('SUBMIT'),
303 wfa_html.image_loc,
304 null,
305 wf_core.translate ('SUBMIT'));
306
307 htp.p('</TD>');
308
309
310 end if;
311
312 exception
313 when others then
314 wf_core.context('Wfa_Html_Util', 'GetLookup', name, value, format);
315 raise;
316 end GetLookup;
317
318 --
319 -- GetButtons (PRIVATE)
320 -- Produce a response field as submit buttons
321 -- IN
322 -- value - default value
323 -- format - lookup type
324 --
325 procedure GetButtons(
326 value in varchar2,
327 format in varchar2,
328 otherattr in number)
329 as
330 respcnt pls_integer;
331 indexer number := otherattr;
332
333 cursor lookup_codes(p_lookup_type varchar2) is
334 select MEANING, LOOKUP_CODE
335 from WF_LOOKUPS
336 where LOOKUP_TYPE = p_lookup_type
337 order by MEANING;
338 begin
339 -- Check number of response values.
340 begin
341 select count(1)
342 into respcnt
343 from WF_LOOKUPS
344 where LOOKUP_TYPE = format;
345 exception
346 when no_data_found then
347 return;
348 end;
349
350 if (respcnt > wfa_html_util.result_button_threshold) then
351 -- If number of responses over threshold use a select list instead.
352 wfa_html_util.GetLookup('RESULT', value, format, TRUE);
353 else
354 -- Use buttons.
355
356 -- Add a hidden field for the result field name
357 htp.formHidden('h_fnames', 'RESULT#LOOKUP#'||format);
358 htp.formHidden('h_fvalues', null);
359
360 -- Add a button for every lookup
361 for i in lookup_codes(format) loop
362 htp.p('<TD id="">');
363 wfa_html.create_reg_button ('javascript:document.WFNOTRESP.h_fvalues['||
364 TO_CHAR(indexer)||'].value='||
365 ''''||i.lookup_code||''''||';document.WFNOTRESP.submit()',
366 i.meaning,
367 wfa_html.image_loc,
368 null,
369 i.meaning);
370 htp.p('</TD>');
371
372 /*htp.formSubmit('h_fvalues', i.meaning, 'NOBORDER');*/
373 end loop;
374
375 end if;
376
377 exception
378 when others then
379 wf_core.context('Wfa_Html_Util', 'GetButtons', value, format);
380 raise;
381 end GetButtons;
382
383 --
384 -- SetAttribute (PRIVATE)
385 -- Set response attributes when processing a response.
386 -- IN
387 -- nid - notification id
388 -- attr_name_type - attribute name#type#format
389 -- attr_value - attribute value
390 --
391 procedure SetAttribute(
392 nid in number,
393 attr_name_type in varchar2,
394 attr_value in varchar2,
395 doc_name in varchar2)
396 as
397 first pls_integer;
398 second pls_integer;
399 attr_type varchar2(8);
400 attr_name varchar2(30);
401 attr_fmt varchar2(240);
402 lovcode varchar2(30);
403 l_attr_value varchar2(30);
404
405 begin
406 -- Parse out name#type#format
407 first := instr(attr_name_type, '#', 1);
408 second := instr(attr_name_type, '#', 1, 2);
409 attr_name := substr(attr_name_type, 1, first-1);
410 attr_type := substr(attr_name_type, first+1, second-first-1);
411 attr_fmt := substr(attr_name_type, second+1,
412 length(attr_name_type)-second);
413
414 if (attr_type = 'DATE') then
415 if (attr_fmt is not null) then
416 wf_notification.SetAttrDate(nid, attr_name,
417 to_date(attr_value, attr_fmt));
418 else
419 wf_notification.SetAttrDate(nid, attr_name,
420 to_date(attr_value,SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')));
421 end if;
422 elsif (attr_type = 'NUMBER') then
423 if (attr_fmt is not null) then
424 wf_notification.SetAttrNumber(nid, attr_name,
425 to_number(attr_value, attr_fmt));
426 else
427 wf_notification.SetAttrNumber(nid, attr_name,
428 to_number(attr_value));
429 end if;
430 elsif (attr_type = 'ROLE') then
431
432 /*
433 ** If this is a role then try to get the unique role name for the
434 ** user that was selected. Since this could be a display name
435 ** or an internal name, make sure to get the unique internal name
436 */
437 l_attr_value := attr_value;
438
439 wfa_html.validate_display_name (doc_name, l_attr_value);
440
441 --
442 -- Set the role to upper
443 --
444 wf_notification.SetAttrText(nid, attr_name, l_attr_value);
445
446 elsif (attr_type = 'DOCUMENT') then
447 -- if PLSQL then use the display value into which the user typed
448 if upper(substr(doc_name,1, 5)) = 'PLSQL' then
449
450 wf_notification.SetAttrText(nid, attr_name, doc_name);
451 -- use the hidden field populated by doc lov
452 else
453
454 wf_notification.SetAttrText(nid, attr_name, attr_value);
455
456 end if;
457
458 else
459 -- VARCHAR2 or LOOKUP or misc value
460 wf_notification.SetAttrText(nid, attr_name, attr_value);
461 end if;
462
463 exception
464 when others then
465 wf_core.context('Wfa_Html_Util', 'SetAttribute',
466 to_char(nid), attr_name_type, attr_value);
467 raise;
468 end SetAttribute;
469
470 --
471 -- GetLookupMeaning (PRIVATE)
472 -- Retrieve displayed value of lookup
473 -- IN
474 -- ltype - lookup type
475 -- lcode - lookup code
476 -- RETURNS
477 -- Displayed meaning of lookup code
478 --
479 function GetLookupMeaning(
480 ltype in varchar2,
481 lcode in varchar2)
482 return varchar2
483 is
484 meaning varchar2(80);
485 begin
486 select WL.MEANING
487 into meaning
488 from WF_LOOKUPS WL
489 where WL.LOOKUP_TYPE = GetLookupMeaning.ltype
490 and WL.LOOKUP_CODE = GetLookupMeaning.lcode;
491
492 return(meaning);
493 exception
494 when no_data_found then
495 return(lcode);
496 when others then
497 wf_core.context('Wfa_Html_Util', 'GetLookupMeaning', ltype, lcode);
498 raise;
499 end GetLookupMeaning;
500
501 --
502 -- GetUrlCount (PRIVATE)
503 -- IN
504 -- nid - notification id
505 -- OUT
506 -- urlcnt - number of urls as reponse attributes
507 -- urlstrg - one of the urls if it exist
508 -- this is generally discarded unless there is only one
509 procedure GetUrlCount(
510 nid in number,
511 urlcnt out nocopy number,
512 urlstrg out nocopy varchar2)
513 is
514 buf pls_integer;
515 begin
516 select count(1),max(NA.TEXT_VALUE)
517 into urlcnt, urlstrg
518 from WF_NOTIFICATION_ATTRIBUTES NA,
519 WF_MESSAGE_ATTRIBUTES MA,
520 WF_NOTIFICATIONS N
521 where N.NOTIFICATION_ID = nid
522 and NA.NOTIFICATION_ID = N.NOTIFICATION_ID
523 and MA.MESSAGE_NAME = N.MESSAGE_NAME
524 and MA.MESSAGE_TYPE = N.MESSAGE_TYPE
525 and MA.NAME = NA.NAME
526 and MA.SUBTYPE = 'RESPOND'
527 and MA.TYPE = 'URL';
528
529 exception
530 when others then
531 wf_core.context('Wfa_Html_Util', 'GetUrlCount', to_char(nid));
532 raise;
533 end GetUrlCount;
534
535 --
536 -- GetResponseUrl (PRIVATE)
537 -- Return single response url.
538 -- NOTE: this assumes there is exactly one response url attribute.
539 -- IN
540 -- nid - notification id
541 -- RETURNS
542 -- Response url
543 --
544 function GetResponseUrl(
545 nid in number)
546 return varchar2
547 is
548 buf varchar2(4000);
549 begin
550 select text_value
551 into buf
552 from WF_NOTIFICATION_ATTRIBUTES NA,
553 WF_MESSAGE_ATTRIBUTES_VL MA,
554 WF_NOTIFICATIONS N
555 where N.NOTIFICATION_ID = nid
556 and NA.NOTIFICATION_ID = N.NOTIFICATION_ID
557 and MA.MESSAGE_NAME = N.MESSAGE_NAME
558 and MA.MESSAGE_TYPE = N.MESSAGE_TYPE
559 and MA.NAME = NA.NAME
560 and MA.SUBTYPE = 'RESPOND'
561 and MA.TYPE = 'URL'
562 and ROWNUM = 1;
563
564 return(buf);
565
566 exception
567 when others then
568 wf_core.context('Wfa_Html_Util', 'GetResponseUrl', to_char(nid));
569 raise;
570 end GetResponseUrl;
571
572 --
573 -- GetDisplayValue (PRIVATE)
574 -- Get displayed value of a response field
575 -- IN
576 -- type - field type (VARCHAR2, NUMBER, DATE, LOOKUP, URL)
577 -- format - field format (depends on type)
578 -- tvalue - text value
579 -- nvalue - number value
580 -- dvalue - date value
581 -- RETURNS
582 -- Displayed value
583 --
584 function GetDisplayValue(
585 type in varchar2,
586 format in varchar2,
587 tvalue in varchar2,
588 nvalue in number,
589 dvalue in date)
590 return varchar2
591 is
592 s0 varchar2(2000);
593 value varchar2(4000);
594 begin
595 if (type = 'VARCHAR2') then
596 value := tvalue;
597 elsif (type = 'NUMBER') then
598 if (format is null) then
599 value := to_char(nvalue);
600 else
601 value := to_char(nvalue, format);
602 end if;
603 elsif (type = 'DATE') then
604 -- <bug 7514495>
605 value := wf_notification_util.GetCalendarDate( wf_notification_util.getCurrentNID() , dvalue, format);
606 elsif (type = 'LOOKUP') then
607 value := wfa_html_util.GetLookupMeaning(format, tvalue);
608 elsif (type = 'URL') then
609 value := tvalue;
610 elsif (type = 'ROLE') then
611 wf_directory.GetRoleInfo(tvalue, value, s0, s0, s0, s0);
612 else
613 -- Default to return text value unchanged
614 value := tvalue;
615 end if;
616
617 return(value);
618
619 exception
620 when others then
621 wf_core.context('Wfa_Html_Util', 'GetDisplayWindow', type, format,
622 tvalue, to_char(nvalue), to_char(dvalue));
623 raise;
624 end GetDisplayValue;
625
626 --
627 -- GetDenormalizedValues
628 -- Populate WF_NOTIFICATIONS with the needed values with supplied langcode.
629 -- Then returns those values via the out variables.
630 -- IN:
631 -- nid - notification id
632 -- langcode - language code
633 -- OUT:
634 -- from_user - display name of from role
635 -- to_user - display name of recipient_role
636 -- subject - subject of the notification
637 --
638 procedure GetDenormalizedValues(nid in number,
639 langcode in varchar2,
640 from_user out nocopy varchar2,
641 to_user out nocopy varchar2,
642 subject out nocopy varchar2)
643 is
644 begin
645 Wf_Notification.Denormalize_Notification(nid=>nid,langcode=>langcode);
646
647 begin
648 select FROM_USER, TO_USER, SUBJECT
649 into from_user, to_user, subject
650 from WF_NOTIFICATIONS
651 where NOTIFICATION_ID = nid;
652 exception
653 when OTHERS then
654 from_user := null;
655 to_user := null;
656 subject := null;
657 end;
658
659 exception
660 when OTHERS then
661 wf_core.context('Wfa_Html_Util', 'GetDenormalizedValues',
662 to_char(nid), langcode);
663 raise;
664 end GetDenormalizedValues;
665
666 end WFA_HTML_UTIL;