[Home] [Help]
PACKAGE BODY: APPS.ICX_QUESTIONS_ADMIN
Source
1 PACKAGE BODY icx_questions_admin AS
2 /* $Header: ICXQUADB.pls 115.2 1999/12/09 22:54:02 pkm ship $ */
3
4 /*
5 ** We need need to fetch URL prefix from WF_WEB_AGENT in wf_resources
6 ** since this function gets called from the forms environment
7 ** which doesn't know anything about the cgi variables.
8 */
9 dm_base_url varchar2(240) := wf_core.translate('WF_WEB_AGENT');
10
11 --
12 -- Error (PRIVATE)
13 -- Print a page with an error message.
14 -- Errors are retrieved from these sources in order:
15 -- 1. wf_core errors
16 -- 2. Oracle errors
17 -- 3. Unspecified INTERNAL error
18 --
19 procedure Error
20 as
21 error_name varchar2(30);
22 error_message varchar2(2000);
23 error_stack varchar2(32000);
24 begin
25 htp.htmlOpen;
26 htp.headOpen;
27 htp.title(wf_core.translate('ERROR'));
28 htp.headClose;
29
30 begin
31 wfa_sec.Header(background_only=>TRUE);
32 exception
33 when others then
34 htp.bodyOpen;
35 end;
36
37 htp.header(nsize=>1, cheader=>wf_core.translate('ERROR'));
38
39 wf_core.get_error(error_name, error_message, error_stack);
40
41 if (error_name is not null) then
42 htp.p(error_message);
43 else
44 htp.p(sqlerrm);
45 end if;
46
47 htp.hr;
48 htp.p(wf_core.translate('WFENG_ERRNAME')||': '||error_name);
49 htp.br;
50 htp.p(wf_core.translate('WFENG_ERRSTACK')||': '||
51 replace(error_stack,wf_core.newline,'<br>'));
52
53 wfa_sec.Footer;
54 htp.htmlClose;
55
56 end Error;
57
58 --
59 -- set_find_criteria (PRIVATE)
60 -- Set the concatenated find criteria that will be used to
61 -- get_display_syntax to redraw the questions page.
62 --
63 function set_find_criteria (
64 P_APPLICATION_SHORT_NAME IN VARCHAR2,
65 P_QUESTION_CODE IN VARCHAR2,
66 P_QUESTION IN VARCHAR2)
67 RETURN VARCHAR2
68
69 as
70
71 begin
72
73 return (wfa_html.conv_special_url_chars(P_APPLICATION_SHORT_NAME||
74 ':'||P_QUESTION_CODE||':'||P_QUESTION));
75
76 exception
77 when others then
78 raise;
79
80 END;
81
82
83 --
84 -- get_display_syntax (PRIVATE)
85 -- Parse the different components of the find criteria and build
86 -- the url to get back to the display questions page
87 --
88 function get_display_syntax (p_find_criteria IN VARCHAR2) RETURN VARCHAR2
89 as
90
91 l_application_id varchar2(80);
92 l_application_short_name varchar2(80);
93 l_question_code varchar2(30);
94 l_question varchar2(4000);
95 l_colon number;
96 l_temp_str varchar2(4000);
97
98 begin
99
100 -- Set the l_temp_str
101 l_temp_str := p_find_criteria;
102
103 -- Parse application short name from document information
104 l_colon := instr(l_temp_str, ':');
105
106 if ((l_colon <> 0) and (l_colon < 80)) then
107
108 l_application_short_name := substrb(l_temp_str, 1, l_colon-1);
109
110 -- get the document id and name off the rest of the string
111 l_temp_str := substrb(l_temp_str, l_colon+1);
112
113 end if;
114
115 -- Parse question_code from document information
116 l_colon := instr(l_temp_str, ':');
117
118 if ((l_colon <> 0) and (l_colon < 80)) then
119
120 l_question_code := substrb(l_temp_str, 1, l_colon-1);
121
122 -- get the document id and name off the rest of the string
123 l_temp_str := substrb(l_temp_str, l_colon+1);
124
125 end if;
126
127 -- Parse document id from document information
128 l_colon := instr(l_temp_str, ':');
129
130 l_question := substrb(l_temp_str, l_colon+1);
131
132
133 return (wfa_html.base_url||'/icx_questions_admin.display_questions'||
134 '?p_application_short_name='||wfa_html.conv_special_url_chars(l_application_short_name)||
135 '&p_question_code='||wfa_html.conv_special_url_chars(l_question_code)||
136 '&p_question='||wfa_html.conv_special_url_chars(l_question));
137
138 exception
139 when others then
140 raise;
141
142 END;
143
144 --
145 -- FIND_QUESTIONS
146 -- Search for questions
147 --
148 procedure FIND_QUESTIONS
149
150 IS
151
152 l_username varchar2(80);
153 l_media varchar2(240) := wfa_html.image_loc;
154 l_icon varchar2(30) := 'FNDILOV.gif';
155 l_text varchar2(30) := '';
156 l_onmouseover varchar2(30) := wf_core.translate ('WFPREF_LOV');
157 l_url varchar2(4000);
158 l_error_msg varchar2(240);
159
160 BEGIN
161
162 -- Check current user has admin authority
163 wfa_sec.GetSession(l_username);
164
165 -- Set page title
166 htp.htmlOpen;
167 htp.headOpen;
168 htp.title(wf_core.translate('ICX_FIND_QUESTIONS_TITLE'));
169 wfa_html.create_help_function('wf/links/dmr.htm?DMREP');
170 fnd_document_management.get_open_dm_display_window;
171 wf_lov.OpenLovWinHtml;
172
173 htp.headClose;
174
175 -- Page header
176 wfa_sec.Header(FALSE, '', wf_core.translate('ICX_FIND_QUESTIONS_TITLE'), TRUE);
177
178 htp.tableopen(calign=>'CENTER');
179
180 htp.p('<FORM NAME="ICX_FIND_QUESTIONS" ACTION="icx_questions_admin.display_questions" METHOD="POST">');
181
182 -- Application Name
183 htp.tableRowOpen;
184 htp.tableData(cvalue=>wf_core.translate('ICX_APPLICATION'),
185 calign=>'right');
186
187 -- add LOV here: Note:bottom is name of frame.
188 -- Note: The REPLACE function replaces all the space characters with
189 -- the proper escape sequence.
190 l_url := 'javascript:fnd_open_dm_display_window('||''''||
191 REPLACE('wf_lov.display_lov?p_lov_name='||'questions'||
192 '&p_display_name='||wf_core.translate('ICX_APPLICATION')||
193 '&p_validation_callback=icx_questions_admin.application_lov'||
194 '&p_dest_hidden_field=top.opener.document.ICX_FIND_QUESTIONS.p_application_short_name.value'||
195 '&p_current_value=top.opener.document.ICX_FIND_QUESTIONS.p_application_short_name.value'||
196 '&p_dest_display_field=top.opener.document.ICX_FIND_QUESTIONS.p_application_short_name.value',
197 ' ', '%20')||''''||',400,500)';
198
199 htp.tableData(htf.formText(cname=>'p_application_short_name', csize=>'25',
200 cvalue=>null, cmaxlength=>'50')||
201 '<A href='||l_url|| ' '||l_onmouseover||'>'||
202 '<IMG src="'||l_media||l_icon||
203 '" border=0></A>');
204
205 htp.tablerowclose;
206
207
208 -- Question Code
209 htp.tableRowOpen;
210 htp.tableData(cvalue=>wf_core.translate('ICX_QUESTION_CODE'),
211 calign=>'right');
212
213 htp.tableData(htf.formText(cname=>'p_question_code', csize=>'25',
214 cvalue=>null, cmaxlength=>'30'));
215
216 htp.tableRowClose;
217
218 -- Question
219 htp.tableRowOpen;
220 htp.tableData(cvalue=>wf_core.translate('ICX_QUESTION'),
221 calign=>'right');
222
223 htp.tableData(htf.formText(cname=>'p_question', csize=>'50',
224 cvalue=>null, cmaxlength=>'240'));
225
226 htp.tableRowClose;
227
228 htp.tableclose;
229
230 htp.br;
231
232 htp.tableopen(calign=>'CENTER');
233
234 --Submit Button
235
236 htp.tableRowOpen;
237
238 htp.p('<TD>');
239
240 wfa_html.create_reg_button ('javascript:document.ICX_FIND_QUESTIONS.submit()',
241 wf_core.translate ('FIND'),
242 wfa_html.image_loc,
243 'fndfind.gif',
244 wf_core.translate ('FIND'));
245
246 htp.p('</TD>');
247
248 htp.tableRowClose;
249
250 htp.tableclose;
251
252 htp.formClose;
253
254 wfa_sec.Footer;
255 htp.htmlClose;
256
257
258 exception
259 when others then
260 wf_core.context('icx_questions_admin', 'find_questions');
261 icx_questions_admin.error;
262
263 END find_questions;
264
265 --
266 -- DISPLAY_QUESTIONS
267 -- Display a list of existing questions based on the query criteria provided
268 --
269 procedure DISPLAY_QUESTIONS
270 (
271 P_APPLICATION_SHORT_NAME IN VARCHAR2 DEFAULT NULL,
272 P_QUESTION_CODE IN VARCHAR2 DEFAULT NULL,
273 P_QUESTION IN VARCHAR2 DEFAULT NULL
274 )
275 is
276
277 username varchar2(30); -- Username to query
278
279 l_error_msg varchar2(240);
280
281 l_url varchar2(240);
282 l_media varchar2(240) := wfa_html.image_loc;
283 l_icon varchar2(30);
284 l_text varchar2(30);
285 l_onmouseover varchar2(30);
286
287 l_find_criteria varchar2(4000);
288
289 cursor quest_cursor is
290 SELECT FND.APPLICATION_SHORT_NAME,
291 ICX.APPLICATION_ID,
292 ICX.QUESTION_CODE,
293 ICX.QUESTION
294 FROM ICX_QUESTIONS_VL ICX, FND_APPLICATION_VL FND
295 WHERE ICX.QUESTION_CODE LIKE UPPER(P_QUESTION_CODE) || '%'
296 AND ICX.QUESTION LIKE '%' ||P_QUESTION || '%'
297 AND ICX.TYPE = 'QUESTION'
298 AND (FND.APPLICATION_SHORT_NAME LIKE
299 UPPER(P_APPLICATION_SHORT_NAME)||'%' OR
300 P_APPLICATION_SHORT_NAME IS NULL)
301 AND FND.APPLICATION_ID = ICX.APPLICATION_ID
302 ORDER BY FND.APPLICATION_SHORT_NAME, ICX.QUESTION_CODE;
303
304 l_questions quest_cursor%rowtype;
305
306 rowcount number;
307
308 begin
309
310 /*
311 ** Set the find criteria field so you don't have to pass an unlimited
312 ** number of fields around to store the original search criteria
313 */
314 l_find_criteria :=
315 icx_questions_admin.set_find_criteria (
316 P_APPLICATION_SHORT_NAME, P_QUESTION_CODE, P_QUESTION);
317
318 -- Check current user has admin authority
319 wfa_sec.GetSession(username);
320
321 -- Set page title
322 htp.htmlOpen;
323 htp.headOpen;
324 htp.p('<BASE TARGET="_top">');
325 htp.title(wf_core.translate('ICX_QUESTIONS_TITLE'));
326 wfa_html.create_help_function('wf/links/dmr.htm?DMREP');
327 htp.headClose;
328 wfa_sec.Header(FALSE, 'icx_questions_admin.find_questions',wf_core.translate('ICX_QUESTIONS_TITLE'), FALSE);
329 htp.br;
330
331 -- Column headers
332 htp.tableOpen('border=1 cellpadding=3 bgcolor=white width="100%"');
333 htp.tableRowOpen(cattributes=>'bgcolor=#006699');
334
335 htp.tableHeader(cvalue=>'<font color=#FFFFFF>'||
336 wf_core.translate('ICX_APPLICATION')||'</font>',
337 calign=>'Center');
338 htp.tableHeader(cvalue=>'<font color=#FFFFFF>'||
339 wf_core.translate('ICX_QUESTION_CODE')||'</font>',
340 calign=>'Center');
341 htp.tableHeader(cvalue=>'<font color=#FFFFFF>'||
342 wf_core.translate('ICX_QUESTION')||'</font>',
343 calign=>'Center');
344 htp.tableHeader(cvalue=>'<font color=#FFFFFF>'||
345 wf_core.translate('ICX_EDIT_FUNCTIONS')||'</font>',
346 calign=>'Center');
347 htp.tableHeader(cvalue=>'<font color=#FFFFFF>'||
348 wf_core.translate('DELETE')||'</font>',
349 calign=>'Center');
350 htp.tableRowClose;
351 htp.tableRowOpen;
352 htp.tableRowClose;
353
354 -- Show all nodes
355 for questions in quest_cursor loop
356
357 htp.tableRowOpen(null, 'TOP');
358
359 htp.tableData(questions.application_short_name, 'left');
360
361 htp.tableData(htf.anchor2(
362 curl=>wfa_html.base_url||
363 '/icx_questions_admin.edit_question'||
364 '?p_application_id='||questions.application_id||
365 '&p_question_code='||wfa_html.conv_special_url_chars(questions.question_code)||
366 '&p_insert=FALSE'||
367 '&p_find_criteria='||l_find_criteria,
368 ctext=>questions.question_code, ctarget=>'_top'),
369 'Left');
370
371 htp.tableData(questions.question, 'left');
372
373 htp.tableData(htf.anchor2(curl=>wfa_html.base_url||
374 '/icx_questions_admin.display_functions'||
375 '?p_question_code='||wfa_html.conv_special_url_chars(questions.question_code)||
376 '&p_find_criteria='||l_find_criteria,
377 ctext=>'<IMG SRC="'||wfa_html.image_loc||'FNDJLFOK.gif" BORDER=0>'),
378 'center', cattributes=>'valign="MIDDLE"');
379
380 htp.tableData(htf.anchor2(curl=>wfa_html.base_url||
381 '/icx_questions_admin.question_confirm_delete'||
382 '?p_application_id='||questions.application_id||
383 '&p_question_code='||wfa_html.conv_special_url_chars(questions.question_code)||
384 '&p_find_criteria='||l_find_criteria,
385 ctext=>'<IMG SRC="'||wfa_html.image_loc||'FNDIDELR.gif" BORDER=0>'),
386 'center', cattributes=>'valign="MIDDLE"');
387
388 end loop;
389
390 htp.tableclose;
391
392 htp.br;
393
394 htp.tableopen(calign=>'CENTER');
395
396 --Add new node Button
397 htp.tableRowOpen;
398
399 l_url := wfa_html.base_url||'/icx_questions_admin.edit_question'||
400 '?p_insert=TRUE'||
401 '&p_find_criteria='||l_find_criteria;
402 l_icon := 'FNDADD11.gif';
403 l_text := wf_core.translate ('WFDM_CREATE');
404 l_onmouseover := wf_core.translate ('WFDM_CREATE');
405
406 htp.p('<TD>');
407
408 wf_pref.create_reg_button (l_url, l_onmouseover, l_media, l_icon, l_text);
409
410 htp.p('</TD>');
411
412 htp.tableRowClose;
413
414 htp.tableclose;
415
416 wfa_sec.Footer;
417 htp.htmlClose;
418
419 exception
420 when others then
421 wf_core.context('icx_questions_admin', 'DISPLAY_QUESTIONS');
422 icx_questions_admin.error;
423 end DISPLAY_QUESTIONS;
424
425 --
426 -- EDIT_QUESTION
427 -- Edit question content
428 --
429 procedure EDIT_QUESTION
430 (
431 P_APPLICATION_ID IN VARCHAR2 DEFAULT NULL,
432 P_APPLICATION_SHORT_NAME IN VARCHAR2 DEFAULT NULL,
433 P_QUESTION_CODE IN VARCHAR2 DEFAULT NULL,
434 P_ERROR_MESSAGE IN VARCHAR2 DEFAULT NULL,
435 P_QUESTION IN VARCHAR2 DEFAULT NULL,
436 P_FIND_CRITERIA IN VARCHAR2 DEFAULT NULL,
437 P_INSERT IN VARCHAR2 DEFAULT NULL
438 )
439 IS
440
441 L_APPLICATION_SHORT_NAME VARCHAR2(50) := NULL;
442 L_APPLICATION_ID NUMBER := NULL;
443 L_QUESTION_CODE VARCHAR2(30) := NULL;
444 L_QUESTION VARCHAR2(4000) := NULL;
445
446 l_username varchar2(80);
447 l_media varchar2(240) := wfa_html.image_loc;
448 l_icon varchar2(30) := 'FNDILOV.gif';
449 l_text varchar2(30) := '';
450 l_onmouseover varchar2(30) := wf_core.translate ('WFPREF_LOV');
451 l_url varchar2(4000);
452 l_error_msg varchar2(240);
453
454 BEGIN
455
456 -- Check current user has admin authority
457 wfa_sec.GetSession(l_username);
458
459 if (P_INSERT = 'FALSE' AND P_ERROR_MESSAGE IS NULL) THEN
460
461 SELECT FND.APPLICATION_SHORT_NAME,
462 ICX.APPLICATION_ID,
463 ICX.QUESTION_CODE,
464 ICX.QUESTION
465 INTO L_APPLICATION_SHORT_NAME,
466 L_APPLICATION_ID,
467 L_QUESTION_CODE,
471 AND ICX.TYPE = 'QUESTION'
468 L_QUESTION
469 FROM ICX_QUESTIONS_VL ICX, FND_APPLICATION_VL FND
470 WHERE ICX.QUESTION_CODE = P_QUESTION_CODE
472 AND ICX.APPLICATION_ID = P_APPLICATION_ID
473 AND FND.APPLICATION_ID = ICX.APPLICATION_ID;
474
475 elsif (P_ERROR_MESSAGE IS NOT NULL) THEN
476
477 L_APPLICATION_SHORT_NAME := P_APPLICATION_SHORT_NAME;
478 L_APPLICATION_ID := P_APPLICATION_ID;
479 L_QUESTION_CODE := P_QUESTION_CODE;
480 L_QUESTION := P_QUESTION;
481
482 end if;
483
484 -- Set page title
485 htp.htmlOpen;
486 htp.headOpen;
487 htp.title(wf_core.translate('ICX_EDIT_QUESTION_TITLE'));
488 wfa_html.create_help_function('wf/links/dmr.htm?DMREP');
489 fnd_document_management.get_open_dm_display_window;
490 wf_lov.OpenLovWinHtml;
491
492 htp.headClose;
493
494 -- Page header
495 wfa_sec.Header(FALSE, 'icx_questions_admin.find_questions', wf_core.translate('ICX_EDIT_QUESTION_TITLE'), TRUE);
496
497 -- Print the error message if there is one
498 if (P_ERROR_MESSAGE IS NOT NULL) THEN
499
500 htp.br;
501 htp.p('<B>'||wf_core.translate(P_ERROR_MESSAGE)||'</B>');
502 htp.br;
503
504 end if;
505
506 htp.tableopen(calign=>'CENTER');
507
508 if (P_INSERT = 'FALSE') THEN
509
510 htp.p('<FORM NAME="ICX_EDIT_QUESTION" ACTION="icx_questions_admin.update_question" METHOD="POST">');
511
512 else
513
514 htp.p('<FORM NAME="ICX_EDIT_QUESTION" ACTION="icx_questions_admin.insert_question" METHOD="POST">');
515
516 end if;
517
518 htp.formHidden(cname=>'p_application_id', cvalue=>l_application_id);
519 htp.formHidden(cname=>'p_find_criteria', cvalue=>p_find_criteria);
520
521 -- Application Name
522 htp.tableRowOpen;
523 htp.tableData(cvalue=>wf_core.translate('ICX_APPLICATION'),
524 calign=>'right');
525
526 -- add LOV here: Note:bottom is name of frame.
527 -- Note: The REPLACE function replaces all the space characters with
528 -- the proper escape sequence.
529 l_url := 'javascript:fnd_open_dm_display_window('||''''||
530 REPLACE('wf_lov.display_lov?p_lov_name='||'questions'||
531 '&p_display_name='||wf_core.translate('ICX_APPLICATION')||
532 '&p_validation_callback=icx_questions_admin.application_lov'||
533 '&p_dest_hidden_field=top.opener.document.ICX_EDIT_QUESTION.p_application_short_name.value'||
534 '&p_current_value=top.opener.document.ICX_EDIT_QUESTION.p_application_short_name.value'||
535 '&p_dest_display_field=top.opener.document.ICX_EDIT_QUESTION.p_application_short_name.value',
536 ' ', '%20')||''''||',400,500)';
537
538 htp.tableData(htf.formText(cname=>'p_application_short_name', csize=>'25',
539 cvalue=>l_application_short_name, cmaxlength=>'50')||
540 '<A href='||l_url|| ' '||l_onmouseover||'>'||
541 '<IMG src="'||l_media||l_icon||
542 '" border=0></A>');
543
544 htp.tablerowclose;
545
546
547 -- Question Code
548 htp.tableRowOpen;
549 htp.tableData(cvalue=>wf_core.translate('ICX_QUESTION_CODE'),
550 calign=>'right');
551
552 IF (P_INSERT = 'FALSE') THEN
553
554 htp.formHidden(cname=>'p_question_code', cvalue=>l_question_code);
555
556 htp.tableData(cvalue=>'<B>'||l_question_code||'</B>',
557 calign=>'left');
558
559 ELSE
560
561 htp.tableData(htf.formText(cname=>'p_question_code', csize=>'25',
562 cvalue=>l_question_code, cmaxlength=>'30'));
563
564 END IF;
565
566 htp.tableRowClose;
567
568 -- question
569 htp.tableRowOpen;
570 htp.tableData(cvalue=>wf_core.translate('ICX_QUESTION'),
571 calign=>'right', cattributes=>'VALIGN="TOP"');
572
573 htp.p ('<TD>');
574
575 htp.formTextareaOpen(cname=>'p_question', nrows=>'8',
576 ncolumns=>'50',
577 cattributes=>'WRAP="SOFT"');
578
579 htp.p (l_question);
580
581 htp.formTextareaClose;
582
583 htp.p ('</TD>');
584
585 htp.tableRowClose;
586
587 htp.tableclose;
588
589 htp.br;
590
591 htp.tableopen(calign=>'CENTER');
592
593 --Submit Button
594
595 htp.tableRowOpen;
596
597 l_url := 'javascript:document.ICX_EDIT_QUESTION.submit()';
598 l_icon := 'FNDJLFOK.gif';
599 l_text := wf_core.translate ('WFMON_OK');
600 l_onmouseover := wf_core.translate ('WFMON_OK');
601
602 htp.p('<TD>');
603
604 wf_pref.create_reg_button (l_url, l_onmouseover, l_media, l_icon, l_text);
605
606 htp.p('</TD>');
607
608 l_url := icx_questions_admin.get_display_syntax (p_find_criteria);
609 l_icon := 'FNDJLFCN.gif';
610 l_text := wf_core.translate ('CANCEL');
611 l_onmouseover := wf_core.translate ('CANCEL');
612
613 htp.p('<TD>');
614
615 wf_pref.create_reg_button (l_url, l_onmouseover, l_media, l_icon, l_text);
616
617 htp.p('</TD>');
618
619 htp.tableRowClose;
620
621 htp.tableclose;
622
623 htp.formClose;
624
625 wfa_sec.Footer;
626 htp.htmlClose;
627
628
629 exception
630 when others then
631 wf_core.context('icx_questions_admin', 'edit_question');
632 icx_questions_admin.error;
633
634 END edit_question;
638 -- Insert a new question
635
636 --
637 -- INSERT_QUESTION
639 --
640 procedure INSERT_QUESTION
641 (
642 P_APPLICATION_ID IN VARCHAR2 DEFAULT NULL,
643 P_QUESTION_CODE IN VARCHAR2 DEFAULT NULL,
644 P_APPLICATION_SHORT_NAME IN VARCHAR2 DEFAULT NULL,
645 P_QUESTION IN VARCHAR2 DEFAULT NULL,
646 P_FIND_CRITERIA IN VARCHAR2 DEFAULT NULL
647 )
648 IS
649
650 L_ROWID VARCHAR2(64);
651 L_APPLICATION_ID NUMBER;
652 L_COUNT NUMBER;
653 L_ERROR_MSG VARCHAR2(80);
654
655 BEGIN
656
657 SELECT COUNT(*)
658 INTO l_count
659 FROM FND_APPLICATION_VL FND
660 WHERE FND.APPLICATION_SHORT_NAME = UPPER(P_APPLICATION_SHORT_NAME);
661
662 if (l_count = 0) THEN
663
664 l_error_msg := 'ICX_INVALID_APPLICATION';
665
666 end if;
667
668 SELECT count(*)
669 INTO l_count
670 FROM ICX_QUESTIONS ICX
671 WHERE ICX.QUESTION_CODE = UPPER(P_QUESTION_CODE);
672
673 if (l_count > 0) THEN
674
675 l_error_msg := 'ICX_DUPLICATE_QUESTION_CODE';
676
677 end if;
678
679 if (P_QUESTION IS NULL OR P_QUESTION_CODE IS NULL) THEN
680
681 l_error_msg := 'ICX_ALL_FIELDS_REQUIRED';
682
683 end if;
684
685
686 if (l_error_msg IS NOT NULL) THEN
687
688 owa_util.redirect_url(curl=>wfa_html.base_url||'/'||'icx_questions_admin.edit_question'||
689 '?p_application_id='||p_application_id||
690 '&p_error_message='||l_error_msg||
691 '&p_application_short_name='||UPPER(p_application_short_name)||
692 '&p_question_code='||wfa_html.conv_special_url_chars(UPPER(p_question_code))||
693 '&p_insert=TRUE'||
694 '&p_find_criteria='||wfa_html.conv_special_url_chars(p_find_criteria)||
695 '&p_question='||wfa_html.conv_special_url_chars(p_question),
696 bclose_header=>TRUE);
697
698 else
699
700 SELECT APPLICATION_ID
701 INTO L_APPLICATION_ID
702 FROM FND_APPLICATION_VL FND
703 WHERE FND.APPLICATION_SHORT_NAME = UPPER(P_APPLICATION_SHORT_NAME);
704
705 ICX_QUESTIONS_PKG.INSERT_ROW (
706 L_ROWID,
707 UPPER(P_QUESTION_CODE),
708 L_APPLICATION_ID,
709 'QUESTION',
710 P_QUESTION,
711 sysdate,
712 1,
713 sysdate,
714 1,
715 1);
716
717 -- use owa_util.redirect_url to redirect the URL to the home page
718 owa_util.redirect_url(curl=>icx_questions_admin.get_display_syntax (p_find_criteria),
719 bclose_header=>TRUE);
720
721 END IF;
722
723
724
725 exception
726 when others then
727 wf_core.context('icx_questions_admin', 'insert_question');
728 icx_questions_admin.error;
729
730 END insert_question;
731
732 --
733 -- UPDATE_QUESTION
734 -- Update question content
735 --
736 procedure UPDATE_QUESTION
737 (
738 P_APPLICATION_ID IN VARCHAR2 DEFAULT NULL,
739 P_QUESTION_CODE IN VARCHAR2 DEFAULT NULL,
740 P_APPLICATION_SHORT_NAME IN VARCHAR2 DEFAULT NULL,
741 P_QUESTION IN VARCHAR2 DEFAULT NULL,
742 P_FIND_CRITERIA IN VARCHAR2 DEFAULT NULL
743 )
744 IS
745
746 L_COUNT NUMBER;
747 L_ERROR_MSG VARCHAR2(80);
748
749 BEGIN
750
751 SELECT COUNT(*)
752 INTO l_count
753 FROM FND_APPLICATION_VL FND
754 WHERE FND.APPLICATION_SHORT_NAME = UPPER(P_APPLICATION_SHORT_NAME);
755
756 if (l_count = 0) THEN
757
758 l_error_msg := 'ICX_INVALID_APPLICATION';
759
760 end if;
761
762 if (P_QUESTION IS NULL OR P_QUESTION_CODE IS NULL) THEN
763
764 l_error_msg := 'ICX_ALL_FIELDS_REQUIRED';
765
766 end if;
767
768
769 if (l_error_msg IS NOT NULL) THEN
770
771 owa_util.redirect_url(curl=>wfa_html.base_url||'/'||'icx_questions_admin.edit_question'||
772 '?p_application_id='||p_application_id||
773 '&p_error_message='||l_error_msg||
774 '&p_application_short_name='||UPPER(p_application_short_name)||
775 '&p_question_code='||wfa_html.conv_special_url_chars(p_question_code)||
776 '&p_insert=FALSE'||
777 '&p_find_criteria='||wfa_html.conv_special_url_chars(p_find_criteria)||
778 '&p_question='||wfa_html.conv_special_url_chars(p_question),
779 bclose_header=>TRUE);
780
781 else
782
783 ICX_QUESTIONS_PKG.UPDATE_ROW (
784 P_QUESTION_CODE,
785 P_APPLICATION_ID,
786 'QUESTION',
787 P_QUESTION,
788 sysdate,
789 1,
790 1);
791
792 -- use owa_util.redirect_url to redirect the URL to the home page
793 owa_util.redirect_url(curl=>icx_questions_admin.get_display_syntax (p_find_criteria),
794 bclose_header=>TRUE);
795
796 end if;
797
798 exception
799 when others then
800 wf_core.context('icx_questions_admin', 'update_question');
801 icx_questions_admin.error;
802
803 END update_question;
804
805 /*===========================================================================
806
807 Function question_confirm_delete
808
809 Purpose Provide a confirmation message to delete a message
810
814 p_question_code IN VARCHAR2 DEFAULT NULL,
811 ============================================================================*/
812 procedure question_confirm_delete
813 (p_application_id IN VARCHAR2 DEFAULT NULL,
815 p_find_criteria IN VARCHAR2 DEFAULT NULL
816 )
817 IS
818 username varchar2(240);
819 l_url varchar2(240);
820 l_media varchar2(240) := wfa_html.image_loc;
821 l_icon varchar2(30);
822 l_text varchar2(30);
823 l_onmouseover varchar2(30);
824 l_error_msg varchar2(2000);
825 s0 varchar2(2000);
826 BEGIN
827 -- Check current user has admin authority
828 wfa_sec.GetSession(username);
829
830 -- Set page title
831 htp.htmlOpen;
832 htp.headOpen;
833 htp.p('<BASE TARGET="_top">');
834 htp.title(wf_core.translate('ICX_CONFIRMTITLE'));
835 wfa_html.create_help_function('wf/links/dmn.htm?DMND');
836 htp.headClose;
837 wfa_sec.Header(FALSE, 'icx_questions_admin.find_questions',wf_core.translate('ICX_CONFIRMTITLE'), FALSE);
838 htp.br;
839
840 htp.bodyOpen(cattributes=>'bgcolor="#CCCCCC"');
841 htp.tableOpen(calign=>'CENTER');
842 htp.tableRowOpen;
843 htp.tabledata('<IMG SRC="'||wfa_html.image_loc||'prohibit.gif">');
844 htp.tabledata(wf_core.translate('ICX_CONFIRM_DELETE_MESSAGE') || ': ' ||
845 '<B>'||p_question_code||'</B>');
846 htp.tableRowClose;
847 htp.tableClose;
848 htp.br;
849 htp.tableopen(calign=>'CENTER');
850 --Submit Button
851 htp.tableRowOpen;
852 l_url := wfa_html.base_url||'/icx_questions_admin.delete_question'||
853 '?p_application_id='||p_application_id||
854 '&p_question_code='||wfa_html.conv_special_url_chars(p_question_code)||
855 '&p_find_criteria='||wfa_html.conv_special_url_chars(p_find_criteria);
856 l_icon := 'FNDJLFOK.gif';
857 l_text := wf_core.translate ('WFMON_OK');
858 l_onmouseover := wf_core.translate ('WFMON_OK');
859
860 htp.p('<TD>');
861 wf_pref.create_reg_button (l_url, l_onmouseover, l_media, l_icon, l_text);
862 htp.p('</TD>');
863
864 l_url := icx_questions_admin.get_display_syntax (p_find_criteria);
865 l_icon := 'FNDJLFCN.gif';
866 l_text := wf_core.translate ('CANCEL');
867 l_onmouseover := wf_core.translate ('CANCEL');
868
869 htp.p('<TD>');
870 wf_pref.create_reg_button (l_url, l_onmouseover, l_media, l_icon, l_text);
871 htp.p('</TD>');
872
873 htp.tableRowClose;
874 htp.tableclose;
875 htp.formClose;
876 wfa_sec.Footer;
877 htp.htmlClose;
878
879 exception
880 when others then
881 rollback;
882 wf_core.context('icx_questions_admin', 'question_confirm_delete');
883 icx_questions_admin.Error;
884 END Question_Confirm_Delete;
885
886
887 --
888 -- DELETE_QUESTION
889 -- Delete question content
890 --
891 procedure DELETE_QUESTION
892 (
893 P_APPLICATION_ID IN VARCHAR2 DEFAULT NULL,
894 P_QUESTION_CODE IN VARCHAR2 DEFAULT NULL,
895 P_FIND_CRITERIA IN VARCHAR2 DEFAULT NULL
896 )
897 IS
898
899 BEGIN
900
901 delete from icx_question_functions where question_code = p_question_code;
902 delete from icx_questions_tl where question_code = p_question_code;
903 delete from icx_questions where question_code = p_question_code;
904
905 -- use owa_util.redirect_url to redirect the URL to the home page
906 owa_util.redirect_url(curl=>icx_questions_admin.get_display_syntax (p_find_criteria),
907 bclose_header=>TRUE);
908
909 exception
910 when others then
911 wf_core.context('icx_questions_admin', 'delete_question');
912 icx_questions_admin.error;
913
914 END delete_question;
915
916 --
917 -- application_lov
918 -- Create the data for the applications list of values
919 --
920
921 procedure application_LOV (
922 p_mode in varchar2,
923 p_lov_name in varchar2,
924 p_start_row in number,
925 p_max_rows in number,
926 p_hidden_value in out varchar2,
927 p_display_value in out varchar2,
928 p_result out number)
929
930 IS
931
932 CURSOR c_application_lov (c_find_criteria IN VARCHAR2) IS
933 SELECT
934 application_id,
935 application_short_name,
936 application_name
937 FROM fnd_application_vl
938 WHERE application_short_name like UPPER(c_find_criteria)
939 ORDER BY application_short_name;
940
941 CURSOR c_application_display_value (c_id IN VARCHAR2) IS
942 SELECT
943 application_short_name
944 FROM fnd_application_vl
945 WHERE application_id = c_id;
946
947 ii NUMBER := 0;
948 nn NUMBER := 0;
949 l_total_rows NUMBER := 0;
950 l_id NUMBER;
951 l_name VARCHAR2 (30);
952 l_display_name VARCHAR2 (240);
953 l_result NUMBER := 1; -- This is the return value for each mode
954
955 BEGIN
956
957 if (p_mode = 'LOV') then
958
959 /*
960 ** Need to get a count on the number of rows that will meet the
961 ** criteria before actually executing the fetch to show the user
962 ** how many matches are available.
963 */
964 select count(*)
965 into l_total_rows
966 FROM fnd_application_vl
967 WHERE application_short_name like UPPER(p_display_value||'%');
968
969 wf_lov.g_define_rec.total_rows := l_total_rows;
970
971 wf_lov.g_define_rec.add_attr1_title := wf_core.translate ('ICX_APPLICATION_FULLNAME');
972
976
973 open c_application_lov (p_display_value||'%');
974
975 LOOP
977 FETCH c_application_lov INTO l_id, l_name, l_display_name;
978
979 EXIT WHEN c_application_lov%NOTFOUND OR nn >= p_max_rows;
980
981 ii := ii + 1;
982
983 IF (ii >= p_start_row) THEN
984
985 nn := nn + 1;
986
987 wf_lov.g_value_tbl(nn).hidden_key := l_name;
988 wf_lov.g_value_tbl(nn).display_value := l_name;
989 wf_lov.g_value_tbl(nn).add_attr1_value := l_display_name;
990
991 END IF;
992
993 END LOOP;
994
995 l_result := 1;
996
997 elsif (p_mode = 'GET_DISPLAY_VAL') THEN
998
999 l_result := 1;
1000
1001 elsif (p_mode = 'VALIDATE') THEN
1002
1003 l_result := 1;
1004
1005 end if;
1006
1007 p_result := l_result;
1008
1009 exception
1010 when others then
1011 rollback;
1012 wf_core.context('Wfa_Html', 'wf_user_val');
1013 raise;
1014 end application_lov;
1015
1016 --
1017 -- DISPLAY_FUNCTIONS
1018 -- Display a list of existing questions based on the query criteria provided
1019 --
1020 procedure DISPLAY_FUNCTIONS
1021 (
1022 P_QUESTION_CODE IN VARCHAR2 DEFAULT NULL,
1023 P_FIND_CRITERIA IN VARCHAR2 DEFAULT NULL
1024 )
1025 is
1026
1027 username varchar2(30); -- Username to query
1028
1029 l_error_msg varchar2(240);
1030
1031 l_url varchar2(240);
1032 l_media varchar2(240) := wfa_html.image_loc;
1033 l_icon varchar2(30);
1034 l_text varchar2(30);
1035 l_onmouseover varchar2(30);
1036
1037 cursor function_cursor is
1038 SELECT FND.FUNCTION_NAME,
1039 FND.FUNCTION_ID,
1040 FND.USER_FUNCTION_NAME
1041 FROM ICX_QUESTION_FUNCTIONS ICX, FND_FORM_FUNCTIONS_VL FND
1042 WHERE ICX.QUESTION_CODE = P_QUESTION_CODE
1043 AND ICX.FUNCTION_NAME = FND.FUNCTION_NAME
1044 ORDER BY FND.FUNCTION_NAME;
1045
1046 l_functions function_cursor%rowtype;
1047
1048 rowcount number;
1049
1050 begin
1051
1052 -- Check current user has admin authority
1053 wfa_sec.GetSession(username);
1054
1055 -- Set page title
1056 htp.htmlOpen;
1057 htp.headOpen;
1058 htp.p('<BASE TARGET="_top">');
1059 htp.title(wf_core.translate('ICX_FUNCTIONS_TITLE'));
1060 wfa_html.create_help_function('wf/links/dmr.htm?DMREP');
1061 htp.headClose;
1062 wfa_sec.Header(FALSE, 'icx_questions_admin.find_questions',wf_core.translate('ICX_FUNCTIONS_TITLE'), FALSE);
1063 htp.br;
1064
1065 -- Column headers
1066 htp.tableOpen('border=1 cellpadding=3 bgcolor=white width="100%"');
1067 htp.tableRowOpen(cattributes=>'bgcolor=#006699');
1068
1069 htp.tableHeader(cvalue=>'<font color=#FFFFFF>'||
1070 wf_core.translate('ICX_QUESTION')||'</font>',
1071 calign=>'Center');
1072 htp.tableHeader(cvalue=>'<font color=#FFFFFF>'||
1073 wf_core.translate('ICX_FUNCTION')||'</font>',
1074 calign=>'Center');
1075 htp.tableHeader(cvalue=>'<font color=#FFFFFF>'||
1076 wf_core.translate('ICX_USER_FUNCTION')||'</font>',
1077 calign=>'Center');
1078 htp.tableHeader(cvalue=>'<font color=#FFFFFF>'||
1079 wf_core.translate('DELETE')||'</font>',
1080 calign=>'Center');
1081 htp.tableRowClose;
1082 htp.tableRowOpen;
1083 htp.tableRowClose;
1084
1085 -- Show all nodes
1086 for functions in function_cursor loop
1087
1088 htp.tableRowOpen(null, 'TOP');
1089
1090 htp.tableData(p_question_code, 'left');
1091
1092 htp.tableData(htf.anchor2(
1093 curl=>wfa_html.base_url||
1094 '/icx_questions_admin.edit_function'||
1095 '?p_question_code='||wfa_html.conv_special_url_chars(p_question_code)||
1096 '&p_function_id='||functions.function_id||
1097 '&p_function_name='||wfa_html.conv_special_url_chars(functions.function_name)||
1098 '&p_insert=FALSE'||
1099 '&p_find_criteria='||wfa_html.conv_special_url_chars(p_find_criteria),
1100 ctext=>functions.function_name, ctarget=>'_top'),
1101 'Left');
1102
1103 htp.tableData(functions.user_function_name, 'left');
1104
1105 htp.tableData(htf.anchor2(curl=>wfa_html.base_url||
1106 '/icx_questions_admin.delete_function'||
1107 '?p_question_code='||wfa_html.conv_special_url_chars(p_question_code)||
1108 '&p_function_name='||wfa_html.conv_special_url_chars(functions.function_name)||
1109 '&p_find_criteria='||wfa_html.conv_special_url_chars(p_find_criteria),
1110 ctext=>'<IMG SRC="'||wfa_html.image_loc||'FNDIDELR.gif" BORDER=0>'),
1111 'center', cattributes=>'valign="MIDDLE"');
1112
1113 end loop;
1114
1115 htp.tableclose;
1116
1117 htp.br;
1118
1119 htp.tableopen(calign=>'CENTER');
1120
1121 --Add new node Button
1122 htp.tableRowOpen;
1123
1124 l_url := wfa_html.base_url||'/icx_questions_admin.edit_function'||
1125 '?p_question_code='||wfa_html.conv_special_url_chars(p_question_code)||
1126 '&p_insert=TRUE'||
1127 '&p_find_criteria='||wfa_html.conv_special_url_chars(p_find_criteria);
1128 l_icon := 'FNDADD11.gif';
1129 l_text := wf_core.translate ('WFDM_CREATE');
1130 l_onmouseover := wf_core.translate ('WFDM_CREATE');
1131
1132 htp.p('<TD>');
1133
1134 wf_pref.create_reg_button (l_url, l_onmouseover, l_media, l_icon, l_text);
1138 l_url := icx_questions_admin.get_display_syntax (p_find_criteria);
1135
1136 htp.p('</TD>');
1137
1139 l_icon := 'FNDJLFCN.gif';
1140 l_text := wf_core.translate ('ICX_RETURN_TO_QUESTIONS');
1141 l_onmouseover := wf_core.translate ('ICX_RETURN_TO_QUESTIONS');
1142
1143 htp.p('<TD>');
1144
1145 wf_pref.create_reg_button (l_url, l_onmouseover, l_media, l_icon, l_text);
1146
1147 htp.p('</TD>');
1148
1149 htp.tableRowClose;
1150
1151 htp.tableclose;
1152
1153 wfa_sec.Footer;
1154 htp.htmlClose;
1155
1156 exception
1157 when others then
1158 wf_core.context('icx_questions_admin', 'DISPLAY_FUNCTIONS');
1159 icx_questions_admin.error;
1160 end DISPLAY_FUNCTIONS;
1161
1162
1163 --
1164 -- EDIT_FUNCTION
1165 -- Edit function content
1166 --
1167 procedure EDIT_FUNCTION
1168 (
1169 P_QUESTION_CODE IN VARCHAR2 DEFAULT NULL,
1170 P_FUNCTION_ID IN VARCHAR2 DEFAULT NULL,
1171 P_FUNCTION_NAME IN VARCHAR2 DEFAULT NULL,
1172 P_INSERT IN VARCHAR2 DEFAULT NULL,
1173 P_ERROR_MESSAGE IN VARCHAR2 DEFAULT NULL,
1174 P_FIND_CRITERIA IN VARCHAR2 DEFAULT NULL
1175 )
1176 IS
1177
1178 L_FUNCTION_ID NUMBER := 0;
1179 L_FUNCTION_NAME VARCHAR2(30) := NULL;
1180 L_USER_FUNCTION_NAME VARCHAR2(80) := NULL;
1181
1182 l_username varchar2(80);
1183 l_media varchar2(240) := wfa_html.image_loc;
1184 l_icon varchar2(30) := 'FNDILOV.gif';
1185 l_text varchar2(30) := '';
1186 l_onmouseover varchar2(30) := wf_core.translate ('WFPREF_LOV');
1187 l_url varchar2(4000);
1188 l_error_msg varchar2(240);
1189
1190 BEGIN
1191
1192 -- Check current user has admin authority
1193 wfa_sec.GetSession(l_username);
1194
1195 if (P_INSERT = 'FALSE' AND P_ERROR_MESSAGE IS NULL) THEN
1196
1197 SELECT FND.FUNCTION_ID,
1198 FND.FUNCTION_NAME,
1199 FND.USER_FUNCTION_NAME
1200 INTO L_FUNCTION_ID,
1201 L_FUNCTION_NAME,
1202 L_USER_FUNCTION_NAME
1203 FROM FND_FORM_FUNCTIONS_VL FND
1204 WHERE FND.FUNCTION_ID = P_FUNCTION_ID;
1205
1206 elsif (P_ERROR_MESSAGE IS NOT NULL) THEN
1207
1208 L_FUNCTION_ID := P_FUNCTION_ID;
1209 L_FUNCTION_NAME := P_FUNCTION_NAME;
1210
1211 end if;
1212
1213 -- Set page title
1214 htp.htmlOpen;
1215 htp.headOpen;
1216 htp.title(wf_core.translate('ICX_EDIT_FUNCTION_TITLE'));
1217 wfa_html.create_help_function('wf/links/dmr.htm?DMREP');
1218 fnd_document_management.get_open_dm_display_window;
1219 wf_lov.OpenLovWinHtml;
1220
1221 htp.headClose;
1222
1223 -- Page header
1224 wfa_sec.Header(FALSE, 'icx_questions_admin.find_questions', wf_core.translate('ICX_EDIT_FUNCTION_TITLE'), TRUE);
1225
1226 -- Print the error message if there is one
1227 if (P_ERROR_MESSAGE IS NOT NULL) THEN
1228
1229 htp.br;
1230 htp.p('<B>'||wf_core.translate(P_ERROR_MESSAGE)||'</B>');
1231 htp.br;
1232
1233 end if;
1234
1235 htp.tableopen(calign=>'CENTER');
1236
1237 if (P_INSERT = 'FALSE') THEN
1238
1239 htp.p('<FORM NAME="ICX_EDIT_FUNCTION" ACTION="icx_questions_admin.update_function" METHOD="POST">');
1240
1241 else
1242
1243 htp.p('<FORM NAME="ICX_EDIT_FUNCTION" ACTION="icx_questions_admin.insert_function" METHOD="POST">');
1244
1245 end if;
1246
1247 htp.formHidden(cname=>'p_function_id', cvalue=>p_function_id);
1248 htp.formHidden(cname=>'p_old_function_name', cvalue=>p_function_name);
1249 htp.formHidden(cname=>'p_find_criteria', cvalue=>p_find_criteria);
1250
1251 -- Question Code
1252 htp.tableRowOpen;
1253 htp.tableData(cvalue=>wf_core.translate('ICX_QUESTION_CODE'),
1254 calign=>'right');
1255
1256 htp.formHidden(cname=>'p_question_code', cvalue=>p_question_code);
1257
1258 htp.tableData(cvalue=>'<B>'||p_question_code||'</B>',
1259 calign=>'left');
1260
1261
1262 htp.tableRowClose;
1263
1264 -- Function Name
1265 htp.tableRowOpen;
1266 htp.tableData(cvalue=>wf_core.translate('ICX_FUNCTION'),
1267 calign=>'right');
1268
1269 -- add LOV here: Note:bottom is name of frame.
1270 -- Note: The REPLACE function replaces all the space characters with
1271 -- the proper escape sequence.
1272 l_url := 'javascript:fnd_open_dm_display_window('||''''||
1273 REPLACE('wf_lov.display_lov?p_lov_name='||'functions'||
1274 '&p_display_name='||wf_core.translate('ICX_FUNCTION')||
1275 '&p_validation_callback=icx_questions_admin.function_lov'||
1276 '&p_dest_hidden_field=top.opener.document.ICX_EDIT_FUNCTION.p_function_name.value'||
1277 '&p_current_value=top.opener.document.ICX_EDIT_FUNCTION.p_function_name.value'||
1278 '&p_dest_display_field=top.opener.document.ICX_EDIT_FUNCTION.p_function_name.value',
1279 ' ', '%20')||''''||',400,500)';
1280
1281
1282 htp.tableData(htf.formText(cname=>'p_function_name', csize=>'25',
1283 cvalue=>l_function_name, cmaxlength=>'50')||
1284 '<A href='||l_url|| ' '||l_onmouseover||'>'||
1285 '<IMG src="'||l_media||l_icon||
1286 '" border=0></A>');
1287
1288 htp.tablerowclose;
1289
1290 -- Function Desciption
1291 htp.tableRowOpen;
1292 htp.tableData(cvalue=>wf_core.translate('ICX_FUNCTION_DESCRIPTION'),
1293 calign=>'right');
1297
1294
1295 htp.tableData(htf.formText(cname=>'p_user_function_name', csize=>'40',
1296 cvalue=>l_user_function_name, cmaxlength=>'240'));
1298 htp.tableRowClose;
1299
1300 htp.tableclose;
1301
1302 htp.br;
1303
1304 htp.tableopen(calign=>'CENTER');
1305
1306 --Submit Button
1307
1308 htp.tableRowOpen;
1309
1310 l_url := 'javascript:document.ICX_EDIT_FUNCTION.submit()';
1311 l_icon := 'FNDJLFOK.gif';
1312 l_text := wf_core.translate ('WFMON_OK');
1313 l_onmouseover := wf_core.translate ('WFMON_OK');
1314
1315 htp.p('<TD>');
1316
1317 wf_pref.create_reg_button (l_url, l_onmouseover, l_media, l_icon, l_text);
1318
1319 htp.p('</TD>');
1320
1321 l_url := wfa_html.base_url||'/'||'icx_questions_admin.display_functions'||
1322 '?p_question_code='||wfa_html.conv_special_url_chars(p_question_code)||
1323 '&p_find_criteria='||wfa_html.conv_special_url_chars(p_find_criteria);
1324 l_icon := 'FNDJLFCN.gif';
1325 l_text := wf_core.translate ('CANCEL');
1326 l_onmouseover := wf_core.translate ('CANCEL');
1327
1328 htp.p('<TD>');
1329
1330 wf_pref.create_reg_button (l_url, l_onmouseover, l_media, l_icon, l_text);
1331
1332 htp.p('</TD>');
1333
1334 htp.tableRowClose;
1335
1336 htp.tableclose;
1337
1338 htp.formClose;
1339
1340 wfa_sec.Footer;
1341 htp.htmlClose;
1342
1343
1344 exception
1345 when others then
1346 wf_core.context('icx_questions_admin', 'edit_function');
1347 icx_questions_admin.error;
1348
1349 END edit_function;
1350
1351
1352 --
1353 -- function_lov
1354 -- Create the data for the function list of values
1355 --
1356
1357 procedure function_lov (
1358 p_mode in varchar2,
1359 p_lov_name in varchar2,
1360 p_start_row in number,
1361 p_max_rows in number,
1362 p_hidden_value in out varchar2,
1363 p_display_value in out varchar2,
1364 p_result out number)
1365
1366 IS
1367
1368 CURSOR c_function_lov (c_find_criteria IN VARCHAR2) IS
1369 SELECT FND.FUNCTION_ID,
1370 FND.FUNCTION_NAME,
1371 FND.USER_FUNCTION_NAME
1372 FROM FND_FORM_FUNCTIONS_VL FND
1373 WHERE FND.FUNCTION_NAME like upper(c_find_criteria)
1374 ORDER BY fnd.function_name;
1375
1376 CURSOR c_function_display_value (c_id IN VARCHAR2) IS
1377 SELECT
1378 application_short_name
1379 FROM fnd_application_vl
1380 WHERE application_id = c_id;
1381
1382 ii NUMBER := 0;
1383 nn NUMBER := 0;
1384 l_total_rows NUMBER := 0;
1385 l_id NUMBER;
1386 l_name VARCHAR2 (240);
1387 l_display_name VARCHAR2 (2000);
1388 l_result NUMBER := 1; -- This is the return value for each mode
1389
1390 BEGIN
1391
1392 if (p_mode = 'LOV') then
1393
1394 /*
1395 ** Need to get a count on the number of rows that will meet the
1396 ** criteria before actually executing the fetch to show the user
1397 ** how many matches are available.
1398 */
1399 select count(*)
1400 into l_total_rows
1401 FROM FND_FORM_FUNCTIONS_VL FND
1402 WHERE FND.FUNCTION_NAME like upper(p_display_value||'%');
1403
1404 wf_lov.g_define_rec.total_rows := l_total_rows;
1405
1406 wf_lov.g_define_rec.add_attr1_title := wf_core.translate ('ICX_USER_FUNCTION');
1407
1408 open c_function_lov (p_display_value||'%');
1409
1410 LOOP
1411
1412 FETCH c_function_lov INTO l_id, l_name, l_display_name;
1413
1414 EXIT WHEN c_function_lov%NOTFOUND OR nn >= p_max_rows;
1415
1416 ii := ii + 1;
1417
1418 IF (ii >= p_start_row) THEN
1419
1420 nn := nn + 1;
1421
1422 wf_lov.g_value_tbl(nn).hidden_key := l_name;
1423 wf_lov.g_value_tbl(nn).display_value := l_name;
1424 wf_lov.g_value_tbl(nn).add_attr1_value := l_display_name;
1425
1426 END IF;
1427
1428 END LOOP;
1429
1430 l_result := 1;
1431
1432 elsif (p_mode = 'GET_DISPLAY_VAL') THEN
1433
1434 l_result := 1;
1435
1436 elsif (p_mode = 'VALIDATE') THEN
1437
1438 l_result := 1;
1439
1440 end if;
1441
1442 p_result := l_result;
1443
1444 exception
1445 when others then
1446 rollback;
1447 wf_core.context('Wfa_Html', 'wf_user_val');
1448 raise;
1449 end function_lov;
1450
1451
1452 --
1453 -- INSERT_FUNCTION
1454 -- Insert a new function
1455 --
1456 procedure INSERT_FUNCTION
1457 (
1458 P_FUNCTION_ID IN VARCHAR2 DEFAULT NULL,
1459 P_OLD_FUNCTION_NAME IN VARCHAR2 DEFAULT NULL,
1460 P_FUNCTION_NAME IN VARCHAR2 DEFAULT NULL,
1461 P_USER_FUNCTION_NAME IN VARCHAR2 DEFAULT NULL,
1462 P_QUESTION_CODE IN VARCHAR2 DEFAULT NULL,
1463 P_FIND_CRITERIA IN VARCHAR2 DEFAULT NULL
1464 )
1465 IS
1466
1467 L_ROWID VARCHAR2(64);
1468 L_COUNT NUMBER;
1469 L_ERROR_MSG VARCHAR2(80);
1470
1471 BEGIN
1472
1473 SELECT COUNT(*)
1474 INTO l_count
1475 FROM FND_FORM_FUNCTIONS_VL FND
1476 WHERE FND.FUNCTION_NAME = P_FUNCTION_NAME;
1477
1478 if (l_count = 0) THEN
1479
1480 l_error_msg := 'ICX_INVALID_FUNCTION';
1481
1482 end if;
1483
1484 SELECT COUNT(*)
1485 INTO l_count
1486 FROM ICX_QUESTION_FUNCTIONS ICX
1490 if (l_count > 0) THEN
1487 WHERE ICX.FUNCTION_NAME = P_FUNCTION_NAME
1488 AND ICX.QUESTION_CODE = P_QUESTION_CODE;
1489
1491
1492 l_error_msg := 'ICX_DUPLICATE_FUNCTION';
1493
1494 end if;
1495
1496
1497 if (l_error_msg IS NOT NULL) THEN
1498
1499 owa_util.redirect_url(curl=>wfa_html.base_url||'/'||'icx_questions_admin.edit_function'||
1500 '?p_question_code='||wfa_html.conv_special_url_chars(p_question_code)||
1501 '&p_function_id='||p_function_id||
1502 '&p_function_name='||wfa_html.conv_special_url_chars(p_function_name)||
1503 '&p_insert=TRUE'||
1504 '&p_error_message='||l_error_msg||
1505 '&p_find_criteria='||wfa_html.conv_special_url_chars(p_find_criteria),
1506 bclose_header=>TRUE);
1507
1508 else
1509
1510 INSERT INTO ICX_QUESTION_FUNCTIONS
1511 (QUESTION_CODE,
1512 FUNCTION_NAME,
1513 LAST_UPDATE_DATE,
1514 LAST_UPDATED_BY,
1515 CREATION_DATE,
1516 CREATED_BY
1517 )
1518 VALUES
1519 (P_QUESTION_CODE,
1520 P_FUNCTION_NAME,
1521 sysdate,
1522 1,
1523 sysdate,
1524 1
1525 );
1526
1527 -- use owa_util.redirect_url to redirect the URL to the home page
1528 owa_util.redirect_url(curl=>wfa_html.base_url||'/'||'icx_questions_admin.display_functions'||
1529 '?p_question_code='||wfa_html.conv_special_url_chars(p_question_code)||
1530 '&p_find_criteria='||wfa_html.conv_special_url_chars(p_find_criteria),
1531 bclose_header=>TRUE);
1532
1533 end if;
1534
1535 exception
1536 when others then
1537 wf_core.context('icx_questions_admin', 'insert_function');
1538 icx_questions_admin.error;
1539
1540 END insert_function;
1541
1542 --
1543 -- UPDATE_FUNCTION
1544 -- Update an existing function
1545 --
1546 procedure UPDATE_FUNCTION
1547 (
1548 P_FUNCTION_ID IN VARCHAR2 DEFAULT NULL,
1549 P_OLD_FUNCTION_NAME IN VARCHAR2 DEFAULT NULL,
1550 P_FUNCTION_NAME IN VARCHAR2 DEFAULT NULL,
1551 p_USER_FUNCTION_NAME IN VARCHAR2 DEFAULT NULL,
1552 P_QUESTION_CODE IN VARCHAR2 DEFAULT NULL,
1553 P_FIND_CRITERIA IN VARCHAR2 DEFAULT NULL
1554 )
1555 IS
1556
1557 L_ROWID VARCHAR2(64);
1558 L_COUNT NUMBER;
1559 L_ERROR_MSG VARCHAR2(80);
1560
1561 BEGIN
1562
1563 SELECT COUNT(*)
1564 INTO l_count
1565 FROM FND_FORM_FUNCTIONS_VL FND
1566 WHERE FND.FUNCTION_NAME = P_FUNCTION_NAME;
1567
1568 if (l_count = 0) THEN
1569
1570 l_error_msg := 'ICX_INVALID_FUNCTION';
1571
1572 end if;
1573
1574 SELECT COUNT(*)
1575 INTO l_count
1576 FROM ICX_QUESTION_FUNCTIONS ICX
1577 WHERE ICX.FUNCTION_NAME = P_FUNCTION_NAME
1578 AND ICX.QUESTION_CODE = P_QUESTION_CODE;
1579
1580 if (l_count > 0) THEN
1581
1582 l_error_msg := 'ICX_DUPLICATE_FUNCTION';
1583
1584 end if;
1585
1586 if (l_error_msg IS NOT NULL) THEN
1587
1588 owa_util.redirect_url(curl=>wfa_html.base_url||'/'||'icx_questions_admin.edit_function'||
1589 '?p_question_code='||wfa_html.conv_special_url_chars(p_question_code)||
1590 '&p_function_id='||p_function_id||
1591 '&p_function_name='||wfa_html.conv_special_url_chars(p_function_name)||
1592 '&p_insert=TRUE'||
1593 '&p_error_message='||l_error_msg||
1594 '&p_find_criteria='||wfa_html.conv_special_url_chars(p_find_criteria),
1595 bclose_header=>TRUE);
1596
1597 else
1598
1599 UPDATE ICX_QUESTION_FUNCTIONS
1600 SET FUNCTION_NAME = P_FUNCTION_NAME,
1601 LAST_UPDATE_DATE = sysdate,
1602 LAST_UPDATED_BY = 1,
1603 LAST_UPDATE_LOGIN = 1
1604 WHERE QUESTION_CODE = P_QUESTION_CODE
1605 AND FUNCTION_NAME = P_OLD_FUNCTION_NAME;
1606
1607 -- use owa_util.redirect_url to redirect the URL to the home page
1608
1609 owa_util.redirect_url(curl=>wfa_html.base_url||'/'||'icx_questions_admin.display_functions'||
1610 '?p_question_code='||wfa_html.conv_special_url_chars(p_question_code)||
1611 '&p_find_criteria='||wfa_html.conv_special_url_chars(p_find_criteria),
1612 bclose_header=>TRUE);
1613
1614 end if;
1615
1616 exception
1617 when others then
1618 wf_core.context('icx_questions_admin', 'update_function');
1619 icx_questions_admin.error;
1620
1621 END update_function;
1622
1623
1624 --
1625 -- DELETE_FUNCTION
1626 -- Delete an existing function
1627 --
1628 procedure DELETE_FUNCTION
1629 (
1630 P_FUNCTION_ID IN VARCHAR2 DEFAULT NULL,
1631 P_FUNCTION_NAME IN VARCHAR2 DEFAULT NULL,
1632 P_QUESTION_CODE IN VARCHAR2 DEFAULT NULL,
1633 P_FIND_CRITERIA IN VARCHAR2 DEFAULT NULL
1634 )
1635 IS
1636
1637 L_ROWID VARCHAR2(64);
1638
1639 BEGIN
1640
1641 DELETE FROM ICX_QUESTION_FUNCTIONS
1642 WHERE QUESTION_CODE = P_QUESTION_CODE
1643 AND FUNCTION_NAME = P_FUNCTION_NAME;
1644
1645 -- use owa_util.redirect_url to redirect the URL to the home page
1646 owa_util.redirect_url(curl=>wfa_html.base_url||'/'||'icx_questions_admin.display_functions'||
1647 '?p_question_code='||wfa_html.conv_special_url_chars(p_question_code)||
1648 '&p_find_criteria='||wfa_html.conv_special_url_chars(p_find_criteria),
1649 bclose_header=>TRUE);
1653 wf_core.context('icx_questions_admin', 'delete_function');
1650
1651 exception
1652 when others then
1654 icx_questions_admin.error;
1655
1656 END delete_function;
1657
1658 END icx_questions_admin;
1659