[Home] [Help]
PACKAGE BODY: APPS.ICX_UTIL
Source
1 package body icx_util as
2 /* $Header: ICXUTILB.pls 120.0 2005/10/07 12:21:06 gjimenez noship $ */
3
4 -- The following is used as storage unit for creating the standard error page
5 -- for all of web apps. It uses a PL/SQL table to store all the error columns.
6 -- It then wil return a formated error page to the user.
7 -- The TYPE definition for the PL/SQL table
8 TYPE char2000table IS TABLE OF VARCHAR2(2000)
9 INDEX BY BINARY_INTEGER;
10 -- An empty table to use during the reset of the table
11 empty_char2000table char2000table;
12 -- Define the table and a counter for it
13 error_table char2000table;
14 TOTAL_ERRORS BINARY_INTEGER := 0;
15
16
17
18 -- Global variables for the LOV
19 -- These variable are global to prevent the overhead of passing large
20 -- table, and the fact that strings can not be passed via html
21 -- LOV Region variables
22 g_LOV_region_id number;
23 g_LOV_region varchar2(30);
24
25
26
27 -- first call from login page to create frameset with configurable
28 -- homepage in the top, main frame and a bottom, hidden frame where
29 -- utilities can be preloaded.
30
31 /* No longer used
32 procedure oracle(i_1 in varchar2,
33 i_2 in varchar2,
34 agent in varchar2,
35 dbHost in varchar2) is
36 begin
37
38 htp.p('<FRAMESET rows="*,1" BORDER="0">
39 <FRAME name="main" src="/OA_JAVA_SERV/oracle.apps.icx.myPage.MainMenu?i_1='||i_1||'&i_2='||i_2||'&agent='||agent||'&dbHost='||dbHost||'" NORESIZE>
40 <FRAME name="tail" src="'||agent||'icx_util.preload" NORESIZE SCROLLING="NO">
41 </FRAMESET>');
42
43
44 end;
45 */
46
47 /* No longer used
48 -- loads the lov applet and possibly other utilities in a hidden frame
49 -- on login to the configurable home page.
50
51 procedure preload is
52
53 begin
54 htp.p('<form>');
55 htp.p('<input type="hidden" name="PRELOAD" value="">');
56
57 wf_lov.lovapplet(doc_name=>'PRELOAD',
58 column_names=>NULL,
59 query_params=>null,
60 query_plsql=>'icx_util.preload_query',
61 longlist=>'Y',
62 width=>'1',
63 height=>'1');
64
65 htp.p('</form>');
66
67 end;
68
69 -- returns dummy values to a lov during preload of the applet
70 procedure preload_query(p_titles_only in varchar2,
71 p_find_criteria in varchar2) is
72 begin
73 htp.p('TEST LOV');
74 htp.p('1'); -- columns
75 htp.p('1'); -- rows
76 htp.p('X'); -- header
77 htp.p('100'); -- header size
78 if p_titles_only <> 'Y' then
79 htp.p('X');
80 end if;
81 end;
82 */
83
84 -- Used to write the LOV javascript function into html pages
85 -- that utilize web lov functionality
86
87 procedure LOVScript is
88
89 c_amp varchar2(1) := '&';
90 c_agent varchar2(100) := owa_util.get_cgi_env('SCRIPT_NAME');
91 c_browser varchar2(400) := owa_util.get_cgi_env('HTTP_USER_AGENT');
92 l_user_id varchar2(100);
93 l_lov_type varchar2(100) := 'HTML';
94 l_profile_defined boolean;
95
96 begin
97 if icx_sec.validateSession then
98
99 l_user_id := icx_sec.getID(icx_sec.PV_USER_ID);
100
101 fnd_profile.get_specific(name_z => 'ICX_LOV_TYPE',
102 user_id_z => l_user_id,
103 val_z => l_lov_type,
104 defined_z => l_profile_defined);
105
106 if not l_profile_defined then
107 l_lov_type := 'HTML';
108 end if;
109
110 if l_lov_type = 'JAVA' then
111 -- Java LOV
112
113 -- call wf_lov package for most of the modal lov window javascript
114 wf_lov.OpenLovWinHtml('N');
115
116 -- htp.p('<SCRIPT LANGUAGE="JavaScript"> <!-- hide the script''s contents from feeble browsers');
117
118 htp.p('// additional code for OSSWA modal lov');
119 htp.p('function LOV(c_attribute_app_id, c_attribute_code, c_region_app_id, c_region_code, c_form_name, c_frame_name, c_where_clause,c_js_where_clause) {
120 FNDLOVwindow.win = window.open("'||c_agent||'/icx_util.LOV?c_attribute_app_id=" + c_attribute_app_id + "'||c_amp||'c_attribute_code=" + c_attribute_code + "'||c_amp||'c_region_app_id=" + c_region_app_id + "'
121 ||c_amp||'c_region_code=" + c_region_code + "'||c_amp||'c_form_name=" + c_form_name + "'||c_amp||'c_frame_name=" + c_frame_name + "'||c_amp||'c_where_clause=" + c_where_clause + "'
122 ||c_amp||'c_js_where_clause=" + c_js_where_clause,"LOV","resizable=yes,menubar=yes,scrollbars=yes,toolbar=no,width=780,height=300");');
123
124 htp.p('FNDLOVwindow.win.focus()');
125 htp.p('FNDLOVwindow.open =true');
126
127 if (instr(c_browser, 'MSIE') = 0) then
128 htp.p('FNDLOVwindow.win.opener = self;');
129 end if;
130
131 htp.p('}
132 // end OSSWA modal lov code');
133
134 else
135 -- html LOV
136 htp.p('function LOV(c_attribute_app_id, c_attribute_code, c_region_app_id, c_region_code, c_form_name, c_frame_name, c_where_clause,c_js_where_clause) {
137 lov_win = window.open("'||c_agent||'/icx_util.LOV?c_attribute_app_id=" + c_attribute_app_id + "'||c_amp||'c_attribute_code=" + c_attribute_code + "'||c_amp||'c_region_app_id=" + c_region_app_id + "'
138 ||c_amp||'c_region_code=" + c_region_code + "'||c_amp||'c_form_name=" + c_form_name + "'||c_amp||'c_frame_name=" + c_frame_name + "'||c_amp||'c_where_clause=" + c_where_clause + "'
139 ||c_amp||'c_js_where_clause=" + c_js_where_clause,"LOV","resizable=yes,menubar=yes,scrollbars=yes,width=780,height=300");');
140 if (instr(c_browser, 'MSIE') = 0) then
141 htp.p(' lov_win.opener = self;');
142 end if;
143 htp.p('}');
144 end if;
145
146 end if;
147 end;
148
149
150
151 -- Used to write the LOV button onto html pages
152 -- that utilize web lov functionality
153
154 function LOVButton (c_attribute_app_id in number,
155 c_attribute_code in varchar2,
156 c_region_app_id in number,
157 c_region_code in varchar2,
158 c_form_name in varchar2,
159 c_frame_name in varchar2,
160 c_where_clause in varchar2,
161 c_image_align in varchar2)
162 return varchar2 is
163
164 temp varchar2(2000);
165 --c_language varchar2(30);
166 c_title varchar2(80);
167 c_prompts g_prompts_table;
168 l_where_clause varchar2(2000);
169
170 begin
171 /* remove the following commented line if the oa media stuff works fine*/
172 -- c_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
173
174 getPrompts(601,'ICX_LOV',c_title,c_prompts);
175 l_where_clause := icx_call.encrypt2(c_where_clause);
176 temp := htf.anchor('javascript:LOV('''||c_attribute_app_id||''','''||c_attribute_code||''','''||c_region_app_id||''','''||c_region_code||''','''||c_form_name||''','''||c_frame_name||''','''||l_where_clause||''','''')',
177 htf.img('/OA_MEDIA/FNDILOV.gif',c_image_align,icx_util.replace_alt_quotes(c_title),'','BORDER=0 WIDTH=23 HEIGHT=21'),'','onMouseOver="window.status='''||icx_util.replace_onMouseOver_quotes(c_title)||''';return true"');
178 return temp;
179 end;
180
181
182 -- Base procedure that calls LOVHeader and LOVValues in seperate frames
183 -- to produce the web lov functionality
184
185 procedure LOV (c_attribute_app_id in number,
186 c_attribute_code in varchar2,
187 c_region_app_id in number,
188 c_region_code in varchar2,
189 c_form_name in varchar2,
190 c_frame_name in varchar2,
191 c_where_clause in varchar2,
192 c_js_where_clause in varchar2) IS
193
194
195 type lov_out_table is table of number
196 index by binary_integer;
197
198 cursor lov_out_attributes (lov_reg in varchar2, lov_reg_id in number, lov_f_key_name in varchar2) is
199 select decode(ari1.ATTRIBUTE_CODE,null,'NULL',ari1.ATTRIBUTE_CODE),
200 ari2.DISPLAY_SEQUENCE
201 from AK_REGION_ITEMS_VL ari1, AK_REGION_ITEMS_VL ari2
202 where ari1.REGION_APPLICATION_ID(+) = c_region_app_id
203 and ari1.REGION_CODE(+) = c_region_code
204 and ari1.LOV_REGION_APPLICATION_ID(+) = lov_reg_id
205 and ari1.LOV_REGION_CODE(+) = lov_reg
206 and ari1.LOV_FOREIGN_KEY_NAME(+) = lov_f_key_name
207 and (ari1.REGION_DEFAULTING_API_PKG is null
208 or ari1.REGION_DEFAULTING_API_PKG <> 'JS')
209 and ari2.REGION_APPLICATION_ID = lov_reg_id
210 and ari2.REGION_CODE = lov_reg
211 and ari2.ATTRIBUTE_CODE = ari1.LOV_ATTRIBUTE_CODE(+)
212 order by ari2.DISPLAY_SEQUENCE;
213
214 cursor js_out_attributes (lov_reg in varchar2, lov_reg_id in number, lov_f_key_name in varchar2) is
215 select ari1.REGION_DEFAULTING_API_PROC, ari2.DISPLAY_SEQUENCE
216 from AK_REGION_ITEMS_VL ari1, AK_REGION_ITEMS_VL ari2
217 where ari1.REGION_APPLICATION_ID = c_region_app_id
218 and ari1.REGION_CODE = c_region_code
219 and ari1.LOV_REGION_APPLICATION_ID = lov_reg_id
220 and ari1.LOV_REGION_CODE = lov_reg
221 and ari1.LOV_FOREIGN_KEY_NAME = lov_f_key_name
222 and ari1.REGION_DEFAULTING_API_PROC is not null
223 and ari2.REGION_APPLICATION_ID = lov_reg_id
224 and ari2.REGION_CODE = lov_reg
225 and ari2.ATTRIBUTE_CODE = ari1.LOV_ATTRIBUTE_CODE
226 order by ari1.DISPLAY_SEQUENCE;
227
228
229 c_agent varchar2(100) := owa_util.get_cgi_env('SCRIPT_NAME');
230 c_amp varchar2(1) := '&';
231 i number;
232 j number;
233 err_num number;
234 err_mesg varchar2(512);
235 temp_text varchar2(2000);
236 temp_message varchar2(2000);
237 LOV_title varchar2(80);
238 temp_LOV_region_id number;
239 temp_LOV_region varchar2(30);
240 temp_LOV_foreign_key_name varchar2(30);
241 l_where_clause varchar2(2000);
242 base_region_attr varchar2(30);
243 LOV_region_attr varchar2(30);
244 disp_sequence number;
245 l_js_proc_name varchar2(30);
246 l_callback_name varchar2(256);
247 l_callback_columns varchar2(2000);
248 l_num_region_items number;
249 l_col_num number;
250 l_col_found boolean;
251 out_columns varchar2(2000);
252 l_doc_name varchar2(2000);
253 l_lov_out_table lov_out_table;
254 l_init_find varchar2(2000);
255 l_lov_type varchar2(100) := 'HTML';
256 l_user_id varchar2(100);
257 l_profile_defined boolean;
258
259 begin
260 if icx_sec.validateSession then
261
262 select attribute_label_long into LOV_title
263 from ak_region_items_vl
264 where region_application_id = c_region_app_id
265 and region_code = c_region_code
266 and attribute_application_id = c_attribute_app_id
267 and attribute_code = c_attribute_code;
268
269 -- Look up the LOV region being called
270 select LOV_FOREIGN_KEY_NAME, LOV_REGION_APPLICATION_ID, LOV_REGION_CODE
271 into temp_LOV_foreign_key_name, temp_LOV_region_id, temp_LOV_region
272 from AK_REGION_ITEMS
273 where REGION_APPLICATION_ID = c_region_app_id
274 and REGION_CODE = c_region_code
275 and ATTRIBUTE_APPLICATION_ID = c_attribute_app_id
276 and ATTRIBUTE_CODE = c_attribute_code;
277
278 g_LOV_region_id := temp_LOV_region_id;
279 g_LOV_region := temp_LOV_region;
280
281
282 -- Combine two where clauses
283 if c_where_clause is not null then
284 if c_js_where_clause is not null then
285 l_where_clause := icx_call.encrypt2(icx_call.decrypt2(c_where_clause)||' and '||replace(c_js_where_clause,'^@~^',' '));
286 else
287 l_where_clause := c_where_clause;
288 end if;
289 else
290 if c_js_where_clause is not null then
291 l_where_clause := icx_call.encrypt2(replace(c_js_where_clause,'^@~^',' '));
292 end if;
293 end if;
294
295 l_user_id := icx_sec.getID(icx_sec.PV_USER_ID);
296
297 fnd_profile.get_specific(name_z => 'ICX_LOV_TYPE',
298 user_id_z => l_user_id,
299 val_z => l_lov_type,
300 defined_z => l_profile_defined);
301
302 if not l_profile_defined then
303 l_lov_type := 'HTML';
304 end if;
305
306 if l_lov_type = 'JAVA' then
307 -- java LOV
308
309 -- construct doc and initial-field names
310 if c_frame_name is not null then
311 l_doc_name := 'parent.opener.parent.'||c_frame_name||'.document.'||c_form_name;
312 l_init_find := 'parent.opener.parent.'||c_frame_name||'.document.'||c_form_name||'.'||c_attribute_code||'.value';
313 else
314 l_doc_name := 'parent.opener.parent.document.'||c_form_name;
315 l_init_find := 'parent.opener.parent.document.'||c_form_name||'.'||c_attribute_code||'.value';
316 end if;
317
318
319 -- get out column names
320 i := 0;
321 open lov_out_attributes(temp_LOV_region, temp_LOV_region_id, temp_LOV_foreign_key_name);
322 loop
323 fetch lov_out_attributes into base_region_attr, disp_sequence;
324 exit when lov_out_attributes%NOTFOUND;
325 l_lov_out_table(i) := disp_sequence;
326 if (i = 0) then
327 out_columns := base_region_attr;
328 else
329 out_columns := out_columns||','||base_region_attr;
330 end if;
331 i := i + 1;
332 end loop;
333 close lov_out_attributes;
334
335
336 -- get out javascript procedure name and columns
337 i := 0;
338 open js_out_attributes(temp_LOV_region, temp_LOV_region_id, temp_LOV_foreign_key_name);
339 loop
340 fetch js_out_attributes into l_js_proc_name, disp_sequence;
341 exit when js_out_attributes%NOTFOUND;
342 l_col_found := false;
343 for j in l_lov_out_table.FIRST..l_lov_out_table.LAST loop
344 if l_lov_out_table(j) = disp_sequence then
345 l_col_num := j;
346 l_col_found := true;
347 exit;
348 end if;
349 end loop;
350 if (i=0) then
351 if c_frame_name is not null then
352 l_callback_name := 'parent.opener.parent.'||c_frame_name||'.'||l_js_proc_name;
353 else
354 l_callback_name := 'parent.opener.parent.'||l_js_proc_name;
355 end if;
356 if (l_col_found) then
357 l_callback_columns := l_col_num;
358 end if;
359 else
360 if (l_col_found) then
361 l_callback_columns := l_callback_columns||','||l_col_num;
362 end if;
363 end if;
364 i := i + 1;
365 end loop;
366 close js_out_attributes;
367
368
369 -- call wf procedure to create lov page with applet
370 wf_lov.lovapplet(doc_name => l_doc_name,
371 column_names => out_columns,
372 query_params => 'p_LOV_region_id='||temp_LOV_region_id||'&p_LOV_region='||temp_LOV_region||'&p_where_clause='||l_where_clause,
373 query_plsql => 'icx_util.icx_ak_lov',
374 callback => l_callback_name,
375 callback_params => l_callback_columns,
376 longlist => 'Y',
377 initial_find => l_init_find,
378 width => '550',
379 height => '200',
380 window_title => LOV_title);
381
382 else
383 -- html LOV
384
385 htp.htmlOpen;
386
387 htp.headOpen;
388 icx_util.copyright;
389 htp.title(LOV_title);
390 htp.headClose;
391 htp.p('<FRAMESET rows="70,*">
392 <FRAME name="LOVHeader" src="ICX_UTIL.LOVHeader?c_attribute_code='||c_attribute_code||c_amp||'p_LOV_foreign_key_name='||temp_LOV_foreign_key_name
393 ||c_amp||'p_LOV_region_id='||temp_LOV_region_id||c_amp||'p_LOV_region='||temp_LOV_region||c_amp||'c_form_name='||c_form_name||c_amp||'c_frame_name='||c_frame_name||'" >
394 <FRAME name="LOVValues" src="ICX_UTIL.LOVValues?p_LOV_foreign_key_name='||temp_LOV_foreign_key_name||c_amp||'p_LOV_region_id='||temp_LOV_region_id||c_amp||'p_LOV_region='||temp_LOV_region||
395 c_amp||'p_attribute_app_id='||c_attribute_app_id||c_amp||'p_attribute_code='||c_attribute_code||c_amp||'p_region_app_id='||c_region_app_id||c_amp||'p_region_code='||c_region_code||
396 c_amp||'c_form_name='||c_form_name||c_amp||'c_frame_name='||c_frame_name||c_amp||'c_where_clause='||l_where_clause||'">
397 </FRAMESET>');
398
399
400 htp.p('<NOFRAMESET>');
401 htp.p('A browser supporting Frames and JavaScript is required.');
402 htp.p('</NOFRAMESET>');
403
404 htp.htmlClose;
405
406 end if;
407
408 end if; -- validateSession
409
410 exception
411 when no_data_found then
412 fnd_message.set_name('ICX','ICX_LOV_IS_NOT_DEFINED');
413 icx_util.add_error(fnd_message.get);
414 icx_util.error_page_print;
415
416 when others then
417 err_num := SQLCODE;
418 temp_text := SQLERRM;
419 select substr(temp_text,12,512) into err_mesg from dual;
420 temp_message := err_mesg;
421 icx_util.add_error(temp_message);
422 icx_util.error_page_print;
423
424 end;
425
426 procedure ICX_AK_LOV(p_titles_only in varchar2,
427 p_find_criteria in varchar2,
428 p_LOV_region_id in varchar2,
429 p_LOV_region in varchar2,
430 p_where_clause in varchar2) is
431
432 cursor lov_query_columns is
433 select d.COLUMN_NAME
434 from AK_ATTRIBUTES a,
435 AK_REGION_ITEMS_VL b,
436 AK_REGIONS c,
437 AK_OBJECT_ATTRIBUTES d
438 where b.REGION_APPLICATION_ID = p_LOV_region_id
439 and b.REGION_CODE = p_LOV_region
440 and b.NODE_QUERY_FLAG = 'Y'
441 and b.ATTRIBUTE_APPLICATION_ID = d.ATTRIBUTE_APPLICATION_ID
442 and b.ATTRIBUTE_CODE = d.ATTRIBUTE_CODE
443 and b.REGION_APPLICATION_ID = c.REGION_APPLICATION_ID
444 and b.REGION_CODE = c.REGION_CODE
445 and c.DATABASE_OBJECT_NAME = d.DATABASE_OBJECT_NAME
446 and d.ATTRIBUTE_APPLICATION_ID = b.ATTRIBUTE_APPLICATION_ID
447 and d.ATTRIBUTE_CODE = b.ATTRIBUTE_CODE
448 and d.ATTRIBUTE_APPLICATION_ID = a.ATTRIBUTE_APPLICATION_ID
449 and d.ATTRIBUTE_CODE = a.ATTRIBUTE_CODE
450 order by b.DISPLAY_SEQUENCE;
451
452
453 LOV_title varchar2(80);
454 l_find_column varchar2(200);
455 l_where_clause varchar2(2000);
456 l_find_where_clause varchar2(2000);
457 l_order_by_clause varchar2(2000);
458 l_responsibility_id number;
459 l_user_id number;
460 i number;
461 j number;
462 l_total_width number;
463 l_result_row_table icx_util.char240_table;
464 l_sess_id number := icx_sec.getID(icx_sec.PV_SESSION_ID);
465
466 begin
467 if icx_sec.validateSession then
468
469 l_responsibility_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
470 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
471
472 -- get LOV window title
473 LOV_title := icx_util.getPrompt(601,'ICX_LOV',178,'ICX_LIST_OF_VALUES');
474 htp.p(LOV_title);
475
476
477 -- combine where clause from form and find criteria
478 open lov_query_columns;
479 fetch lov_query_columns into l_find_column;
480 close lov_query_columns;
481
482 if p_find_criteria is not null then
483 l_find_where_clause := 'UPPER('||l_find_column||') like '''||
484 UPPER(p_find_criteria)||'%'' and ('||
485 l_find_column||' like '''||LOWER(SUBSTR(p_find_criteria,1,2))||'%'' or '||
486 l_find_column||' like '''||LOWER(SUBSTR(p_find_criteria, 1, 1))||UPPER(SUBSTR(p_find_criteria, 2, 1))||'%'' or '||
487 l_find_column||' like '''||INITCAP(SUBSTR(p_find_criteria, 1, 2))||'%'' or '||
488 l_find_column||' like '''||UPPER(SUBSTR(p_find_criteria, 1, 2))||'%'')';
489
490
491 if p_where_clause is not null then
492 l_where_clause := l_find_where_clause||' and '||icx_call.decrypt2(p_where_clause);
493 else
494 l_where_clause := l_find_where_clause;
495 end if;
496 else
497 if p_where_clause is not null then
498 l_where_clause := icx_call.decrypt2(p_where_clause);
499 end if;
500 --htp.p(l_where_clause||'-'||l_sess_id);
501 end if;
502
503 -- create order by clause
504 l_order_by_clause := '1';
505
506 -- Call to Object Navigator to execute query and return data
507 -- as well as object and region structures
508 ak_query_pkg.exec_query (
509 P_PARENT_REGION_APPL_ID => p_LOV_region_id ,
510 P_PARENT_REGION_CODE => p_LOV_region ,
511 P_WHERE_CLAUSE => l_where_clause ,
512 P_ORDER_BY_CLAUSE => l_order_by_clause ,
513 P_RESPONSIBILITY_ID => l_responsibility_id ,
514 P_USER_ID => l_user_id ,
515 P_RETURN_PARENTS => 'T' ,
516 P_RETURN_CHILDREN => 'F');
517
518
519 -- write out the number of columns returned by the ak query
520 htp.p(ak_query_pkg.g_items_table.COUNT);
521
522 -- write out the number of rows returned by the ak query
523 htp.p(ak_query_pkg.g_results_table.COUNT);
524
525
526 -- write out the column header information
527 l_total_width := 0;
528 if ak_query_pkg.g_items_table.COUNT > 0 then
529 for i in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
530 if ak_query_pkg.g_items_table(i).secured_column = 'F' and
531 ak_query_pkg.g_items_table(i).node_display_flag = 'Y' then
532 l_total_width := l_total_width + ak_query_pkg.g_items_table(i).display_value_length;
533 end if;
534 end loop;
535
536
537 for i in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
538 if ak_query_pkg.g_items_table(i).secured_column = 'F' then
539 htp.p(ak_query_pkg.g_items_table(i).attribute_label_long);
540 if ak_query_pkg.g_items_table(i).node_display_flag = 'Y' then
541 htp.p(round((ak_query_pkg.g_items_table(i).display_value_length/l_total_width) * 100));
542 else
543 htp.p('0');
544 end if;
545 end if;
546 end loop;
547 end if;
548
549
550 -- write out the row data
551 if p_titles_only <> 'Y' then
552 if ak_query_pkg.g_results_table.COUNT > 0 then
553 for i in ak_query_pkg.g_results_table.FIRST..ak_query_pkg.g_results_table.LAST loop
554 -- load data for current row into temp pl/sql table
555 transfer_Row_To_Column(ak_query_pkg.g_results_table(i), l_result_row_table);
556 for j in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
557 htp.p(l_result_row_table(ak_query_pkg.g_items_table(j).value_id));
558 end loop;
559 end loop;
560 end if;
561 end if;
562
563 end if;
564
565 end;
566
567 /* No longer used
568 procedure ICX_PRE_LOV(p_titles_only in varchar2,
569 p_find_criteria in varchar2) is
570
571 begin
572 htp.p('PRELOAD LOV');
573 htp.p('1'); -- columns
574 htp.p('1'); -- rows
575 htp.p('X'); -- header
576 htp.p('100'); -- header size
577 end;
578 */
579
580 /*
581 ** creates the next/previons set buttons for the lov
582 ** also displays the current count location within the list
583 */
584
585 procedure lovrecordbuttons(p_language_code in varchar2,
586 p_packproc in varchar2,
587 p_start_row in number,
588 p_stop_row in number,
589 p_encrypted_where in number,
590 p_query_set in number,
591 p_row_count in number,
592 p_top in boolean,
593 p_jsproc in varchar2,
594 p_hidden in varchar2,
595 p_update in boolean,
596 p_target in varchar2,
597 p_list_count in boolean,
598 P_OBJECT_DISP_NAME in varchar2) is
599 l_target varchar2(240);
600 l_title varchar2(80);
601 l_prompts icx_util.g_prompts_table;
602 l_message varchar2(2000);
603 l_parameter varchar2(2000);
604 l_start_row number;
605 l_stop_row number;
606 begin
607
608 icx_util.getPrompts(601,'ICX_WEB_ON',l_title,l_prompts);
609
610 /*
611 ** If the request is just meant to show the row count then
612 ** do so here
613 */
614 IF (p_list_count = TRUE) THEN
615
616 htp.tableOpen(cborder => 'BORDER=0', cattributes => 'WIDTH="100%"');
617 htp.tableRowOpen;
618 fnd_message.set_name('ICX','ICX_RECORDS_RANGE');
619 fnd_message.set_token('FROM_ROW_TOKEN',p_start_row);
620 fnd_message.set_token('TO_ROW_TOKEN',p_stop_row);
621 fnd_message.set_token('TOTAL_ROW_TOKEN','<font color="ff0000">'||
622 p_row_count||'</font>');
623 l_message := fnd_message.get;
624 htp.p('<font size=2>'||l_message||'<font>');
625 htp.tableRowClose;
626 htp.tableClose;
627
628 /*
629 ** Otherwise check to see if you should create the previous / next set
630 ** buttons
631 */
632 ELSE
633
634 htp.tableOpen(calign=>'CENTER', cborder => 'BORDER=0');
635 htp.tableRowOpen;
636
637 /*
638 ** Check to see if you should create the PREVIOUS button
639 */
640 IF (p_start_row > 1) THEN
641
642 htp.p('<TD>');
643
644 /*
645 ** Make sure that your not going to go back past the first
646 ** record. Otherwise subtract the query set from the start
647 */
648 IF (p_start_row - p_query_set < 1) THEN
649
650 l_start_row := 1;
651
652 ELSE
653
654 l_start_row := p_start_row - p_query_set;
655
656 END IF;
657
658
659 /*
660 ** Make sure that your not going to go back past the last
661 ** record
662 */
663 IF (l_start_row + p_query_set > p_row_count) THEN
664
665 l_stop_row := p_row_count;
666
667 ELSE
668
669 l_stop_row := l_start_row + p_query_set - 1;
670
671 END IF;
672
673 htp.p('<A HREF="javascript:'||p_jsproc||'('||''''||
674 to_char(l_start_row)||
675 ''''||','||''''||
676 to_char(l_stop_row)||''''||')">');
677
678 htp.p('<IMG SRC="/OA_MEDIA/FNDIPRVB.gif" border=0></A>');
679
680 fnd_message.set_name('ICX','ICX_PREVIOUS');
681
682 htp.p('<font class=button>'||fnd_message.get||'</font>');
683
684 htp.p('</TD>');
685
686 END IF;
687
688 /*
689 ** Check to see if you should create the NEXT button
690 */
691 IF (p_stop_row < p_row_count) THEN
692
693 htp.p('<TD>');
694
695 IF (p_start_row + p_query_set > p_row_count) THEN
696
697 l_start_row := p_row_count;
698
699 ELSE
700
701 l_start_row := p_start_row + p_query_set;
702
703 END IF;
704
705 IF (p_stop_row + p_query_set > p_row_count) THEN
706
707 l_stop_row := p_row_count;
708
709 ELSE
710
711 l_stop_row := p_stop_row + p_query_set;
712
713 END IF;
714
715
716 htp.p('<A HREF="javascript:'||p_jsproc||'('||''''||
717 to_char(l_start_row)||''''||','||''''||to_char(l_stop_row)||
718 ''''||')">');
719
720 fnd_message.set_name('ICX','ICX_NEXT');
721
722 htp.p('<font class=button>'||fnd_message.get||'</font>');
723
724 htp.p('<IMG SRC="/OA_MEDIA/FNDINXTB.gif" border=0></A>');
725
726 htp.p('</TD>');
727
728 END IF;
729
730 htp.tableRowClose;
731 htp.tableclose;
732
733 end if;
734
735 end lovrecordbuttons;
736
737
738
739 procedure LOVHeader (c_attribute_code in varchar2,
740 p_LOV_foreign_key_name in varchar2,
741 p_LOV_region_id in number,
742 p_LOV_region in varchar2,
743 c_form_name in varchar2,
744 c_frame_name in varchar2,
745 c_lines in number,
746 x in number,
747 a_1 in varchar2,
748 c_1 in varchar2,
749 i_1 in varchar2,
750 a_2 in varchar2,
751 c_2 in varchar2,
752 i_2 in varchar2,
753 a_3 in varchar2,
754 c_3 in varchar2,
755 i_3 in varchar2,
756 a_4 in varchar2,
757 c_4 in varchar2,
758 i_4 in varchar2,
759 a_5 in varchar2,
760 c_5 in varchar2,
761 i_5 in varchar2) is
762
763
764 cursor lov_query_columns is
765
766 select d.COLUMN_NAME,b.ATTRIBUTE_LABEL_LONG,
767 substr(a.DATA_TYPE,1,1)
768 from AK_ATTRIBUTES a,
769 AK_REGION_ITEMS_VL b,
770 AK_REGIONS c,
771 AK_OBJECT_ATTRIBUTES d
772 where b.REGION_APPLICATION_ID = p_LOV_region_id
773 and b.REGION_CODE = p_LOV_region
774 and b.NODE_QUERY_FLAG = 'Y'
775 and b.ATTRIBUTE_APPLICATION_ID = d.ATTRIBUTE_APPLICATION_ID
776 and b.ATTRIBUTE_CODE = d.ATTRIBUTE_CODE
777 and b.REGION_APPLICATION_ID = c.REGION_APPLICATION_ID
778 and b.REGION_CODE = c.REGION_CODE
779 and c.DATABASE_OBJECT_NAME = d.DATABASE_OBJECT_NAME
780 and d.ATTRIBUTE_APPLICATION_ID = b.ATTRIBUTE_APPLICATION_ID
781 and d.ATTRIBUTE_CODE = b.ATTRIBUTE_CODE
782 and d.ATTRIBUTE_APPLICATION_ID = a.ATTRIBUTE_APPLICATION_ID
783 and d.ATTRIBUTE_CODE = a.ATTRIBUTE_CODE
784 order by b.DISPLAY_SEQUENCE;
785
786 c_browser varchar2(400) := owa_util.get_cgi_env('HTTP_USER_AGENT');
787 c_agent varchar2(100) := owa_util.get_cgi_env('SCRIPT_NAME');
788 c_amp varchar2(1) := '&';
789 temp_text varchar2(2000);
790 i number;
791 c_language varchar2(30);
792 l_lookup_codes g_lookup_code_table;
793 l_lookup_meanings g_lookup_meaning_table;
794 LOV_title varchar2(80);
795 LOV_prompts g_prompts_table;
796 temp_column varchar2(30);
797 temp_attribute varchar2(50);
798 temp_type varchar2(1);
799 num_attributes number;
800 err_num number;
801 err_mesg varchar2(512);
802 temp_message varchar2(2000);
803 l_query_attr_cnt number;
804 l_attributes2 varchar2(2000);
805 l_conditions2 varchar2(2000);
806 l_display_line varchar2(2000);
807 l_icx_custom_call varchar2(30);
808 l_message varchar2(2000);
809 l_matchcase_lov varchar2(10);
810
811 begin
812
813 l_matchcase_lov := fnd_profile.value('ICX_MATCHCASE_LOV');
814
815 if l_matchcase_lov is null
816 then
817 l_matchcase_lov := 'Checked';
818 end if;
819
820 if icx_sec.validateSession then
821 c_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
822 getPrompts(601,'ICX_LOV',LOV_title,LOV_prompts);
823
824
825 htp.headOpen;
826 icx_util.copyright;
827
828 -- htp.p('<LINK REL=STYLESHEET HREF="/OA_HTML/US/PORSTYLE.css" TYPE="text/css">');
829
830 htp.title(LOV_title);
831 js.scriptOpen;
832
833 htp.p('var search = "Y"');
834
835 htp.p('function queryText() {');
836 if c_frame_name is null then
837 htp.p('if (parent.opener.parent.document.'||c_form_name||'.'||c_attribute_code||'.value != "") {
838 document.LOVHeader.i_1.value = parent.opener.parent.document.'||c_form_name||'.'||c_attribute_code||'.value');
839 else
840 htp.p('if (parent.opener.parent.'||c_frame_name||'.document.'||c_form_name||'.'||c_attribute_code||'.value != "") {
841 document.LOVHeader.i_1.value = parent.opener.parent.'||c_frame_name||'.document.'||c_form_name||'.'||c_attribute_code||'.value');
842 end if;
843 htp.p('}
844 }');
845
846 htp.p('function Header_submit(line) {
847 open('''||c_agent||'/ICX_UTIL.LOVHeader?c_attribute_code='||c_attribute_code||c_amp||'p_LOV_foreign_key_name='||p_LOV_foreign_key_name||c_amp||'p_LOV_region_id='||p_LOV_region_id
848 ||c_amp||'p_LOV_region='||p_LOV_region||c_amp||'c_form_name='||c_form_name||c_amp||'c_frame_name='||c_frame_name||c_amp||'c_lines='' + line + '''||c_amp||'x=2'',''LOVHeader'')
849 }');
850
851 select icx_custom_call
852 into l_icx_custom_call
853 from ak_regions
854 where REGION_APPLICATION_ID = p_LOV_region_id
855 and REGION_CODE = p_LOV_region;
856
857 if instr(l_icx_custom_call,'LONG') > 0
858 then
859 fnd_message.set_name('ICX','ICX_OPEN_QUERY2');
860 l_message := icx_util.replace_quotes(fnd_message.get);
861
862 htp.p('function LOV_check(attributes) {
863 if (search == "Y") {
864 if (document.LOVHeader.i_1.value == "") {
865 alert("'||l_message||'");
866 document.LOVHeader.i_1.focus();
867 } else {
868 case_check(attributes);
869 }
870 }
871 }');
872 else
873 fnd_message.set_name('ICX','ICX_OPEN_QUERY');
874 l_message := icx_util.replace_quotes(fnd_message.get);
875
876 htp.p('function LOV_check(attributes) {
877 if (search == "Y") {
878 if (document.LOVHeader.i_1.value == "") {
879 if (confirm("'||l_message||'")) {
880 LOV_submit(attributes);
881 } else {
882 document.LOVHeader.i_1.focus();
883 }
884 } else {
885 case_check(attributes);
886 }
887 }
888 }');
889 end if;
890
891 ----------------------------------------------- 1550749
892 if l_matchcase_lov = 'Hidden'
893 then
894 htp.p('function case_check(attributes) {
895 LOV_submit(attributes);
896 }');
897 else
898 -- javascript to pop alert box if case-sensitive is
899 fnd_message.set_name('ICX','ICX_CASE_QUERY');
900 l_message := icx_util.replace_quotes(fnd_message.get);
901 htp.p('function case_check(attributes) {
902 if (!(document.LOVHeader.case_sensitive.checked)) {
903 if (confirm("'||l_message||'")) {
904 LOV_submit(attributes);
905 } else {
906 document.LOVHeader.i_1.focus();
907 }
908 } else {
909 LOV_submit(attributes);
910 }
911 }');
912 end if;
913 ---------------------------------------------------------------- 1557049
914 htp.p('function LOV_submit(attributes) {
915 if (attributes > 1) {
916 parent.LOVValues.document.LOVValues.x.value = 1
917 parent.LOVValues.document.LOVValues.start_row.value = 1
918 parent.LOVValues.document.LOVValues.p_end_row.value = ""
919 parent.LOVValues.document.LOVValues.a_1.value = document.LOVHeader.a_1.options[document.LOVHeader.a_1.selectedIndex].value
920 parent.LOVValues.document.LOVValues.c_1.value = document.LOVHeader.c_1.options[document.LOVHeader.c_1.selectedIndex].value
921 parent.LOVValues.document.LOVValues.i_1.value = document.LOVHeader.i_1.value
922 } else {
923 parent.LOVValues.document.LOVValues.x.value = 1
924 parent.LOVValues.document.LOVValues.start_row.value = 1
925 parent.LOVValues.document.LOVValues.p_end_row.value = ""
926 parent.LOVValues.document.LOVValues.a_1.value = document.LOVHeader.a_1.value
927 parent.LOVValues.document.LOVValues.c_1.value = document.LOVHeader.c_1.options[document.LOVHeader.c_1.selectedIndex].value
928 parent.LOVValues.document.LOVValues.i_1.value = document.LOVHeader.i_1.value}');
929
930 if l_matchcase_lov = 'Hidden'
931 then
932 htp.p('parent.LOVValues.document.LOVValues.case_sensitive.value = "on"');
933 else
934
935 htp.p('if (document.LOVHeader.case_sensitive.checked) {
936 parent.LOVValues.document.LOVValues.case_sensitive.value = document.LOVHeader.case_sensitive.value
937 } else {
938 parent.LOVValues.document.LOVValues.case_sensitive.value = null
939 }');
940 end if; --- 1557049
941 if c_lines <> 1 then
942 htp.p('parent.LOVValues.document.LOVValues.a_2.value = document.LOVHeader.a_2.options[document.LOVHeader.a_2.selectedIndex].value
943 parent.LOVValues.document.LOVValues.c_2.value = document.LOVHeader.c_2.options[document.LOVHeader.c_2.selectedIndex].value
944 parent.LOVValues.document.LOVValues.i_2.value = document.LOVHeader.i_2.value
945 parent.LOVValues.document.LOVValues.a_3.value = document.LOVHeader.a_3.options[document.LOVHeader.a_3.selectedIndex].value
946 parent.LOVValues.document.LOVValues.c_3.value = document.LOVHeader.c_3.options[document.LOVHeader.c_3.selectedIndex].value
947 parent.LOVValues.document.LOVValues.i_3.value = document.LOVHeader.i_3.value
948 parent.LOVValues.document.LOVValues.a_4.value = document.LOVHeader.a_4.options[document.LOVHeader.a_4.selectedIndex].value
949 parent.LOVValues.document.LOVValues.c_4.value = document.LOVHeader.c_4.options[document.LOVHeader.c_4.selectedIndex].value
950 parent.LOVValues.document.LOVValues.i_4.value = document.LOVHeader.i_4.value
951 parent.LOVValues.document.LOVValues.a_5.value = document.LOVHeader.a_5.options[document.LOVHeader.a_5.selectedIndex].value
952 parent.LOVValues.document.LOVValues.c_5.value = document.LOVHeader.c_5.options[document.LOVHeader.c_5.selectedIndex].value
953 parent.LOVValues.document.LOVValues.i_5.value = document.LOVHeader.i_5.value');
954 else
955 htp.p('parent.LOVValues.document.LOVValues.a_2.value = ""
956 parent.LOVValues.document.LOVValues.c_2.value = ""
957 parent.LOVValues.document.LOVValues.i_2.value = ""
958 parent.LOVValues.document.LOVValues.a_3.value = ""
959 parent.LOVValues.document.LOVValues.c_3.value = ""
960 parent.LOVValues.document.LOVValues.i_3.value = ""
961 parent.LOVValues.document.LOVValues.a_4.value = ""
962 parent.LOVValues.document.LOVValues.c_4.value = ""
963 parent.LOVValues.document.LOVValues.i_4.value = ""
964 parent.LOVValues.document.LOVValues.a_5.value = ""
965 parent.LOVValues.document.LOVValues.c_5.value = ""
966 parent.LOVValues.document.LOVValues.i_5.value = ""');
967 end if;
968 htp.p('parent.LOVValues.document.LOVValues.submit()
969 search="X" }');
970
971 htp.p('function clearField() {
972 document.LOVHeader.reset()
973 search = "Y"
974 }');
975
976 js.scriptClose;
977 htp.headClose;
978
979 htp.p('<BODY bgcolor="#cccccc">');
980
981 select count(*)
982 into l_query_attr_cnt
983 from AK_ATTRIBUTES a,
984 AK_REGION_ITEMS_VL b,
985 AK_REGIONS c,
986 AK_OBJECT_ATTRIBUTES d
987 where b.REGION_APPLICATION_ID = p_LOV_region_id
988 and b.REGION_CODE = p_LOV_region
989 and b.NODE_QUERY_FLAG = 'Y'
990 and b.ATTRIBUTE_APPLICATION_ID = d.ATTRIBUTE_APPLICATION_ID
991 and b.ATTRIBUTE_CODE = d.ATTRIBUTE_CODE
992 and b.REGION_APPLICATION_ID = c.REGION_APPLICATION_ID
993 and b.REGION_CODE = c.REGION_CODE
994 and c.DATABASE_OBJECT_NAME = d.DATABASE_OBJECT_NAME
995 and d.ATTRIBUTE_APPLICATION_ID = b.ATTRIBUTE_APPLICATION_ID
996 and d.ATTRIBUTE_CODE = b.ATTRIBUTE_CODE
997 and d.ATTRIBUTE_APPLICATION_ID = a.ATTRIBUTE_APPLICATION_ID
998 and d.ATTRIBUTE_CODE = a.ATTRIBUTE_CODE;
999
1000 htp.formOpen('javascript:LOV_check('||to_char(l_query_attr_cnt)||')',
1001 'POST','','','NAME="LOVHeader"');
1002
1003 htp.p('<CENTER>');
1004 -- htp.tableOpen('BORDER=0','','','','WIDTH=100%'); --bug 2318801
1005 htp.p('<table cellpadding=4 cellspacing=0 border=0>');
1006 htp.tableRowOpen;
1007
1008 htp.formHidden('c_attribute_code',c_attribute_code);
1009 htp.formHidden('p_LOV_foreign_key_name',p_LOV_foreign_key_name);
1010 htp.formHidden('p_LOV_region_id',p_LOV_region_id);
1011 htp.formHidden('p_LOV_region',p_LOV_region );
1012 htp.formHidden('c_frame_name',c_frame_name);
1013 htp.formHidden('c_form_name',c_form_name);
1014 htp.formHidden('c_lines',c_lines);
1015 htp.formHidden('x',x);
1016
1017 -- Build select list of query attributes.
1018 temp_text := '';
1019 l_attributes2 := htf.formSelectOption('','','VALUE=""');
1020 open lov_query_columns;
1021 num_attributes := 0;
1022 loop
1023 fetch lov_query_columns into temp_column, temp_attribute, temp_type;
1024 exit when lov_query_columns%NOTFOUND;
1025 num_attributes := num_attributes + 1;
1026 if num_attributes = 1 then
1027 temp_text := htf.formSelectOption(temp_attribute,'SELECTED','VALUE="'||temp_type||temp_column||'"');
1028 l_attributes2 := l_attributes2||htf.formSelectOption(temp_attribute,'','VALUE="'||temp_type||temp_column||'"');
1029 else
1030 temp_text := temp_text||htf.formSelectOption(temp_attribute,'','VALUE="'||temp_type||temp_column||'"');
1031 l_attributes2 := l_attributes2||htf.formSelectOption(temp_attribute,'','VALUE="'||temp_type||temp_column||'"');
1032 end if;
1033 end loop;
1034 close lov_query_columns;
1035 temp_text := temp_text||htf.formSelectClose;
1036 l_attributes2 := l_attributes2||htf.formSelectClose;
1037
1038 -- if only one attribute, print as text (not pop list)
1039 if num_attributes = 1 then
1040
1041 --bug 2318801
1042 htp.formHidden('a_1',temp_type||temp_column);
1043 htp.p('<TD ALIGN="CENTER" WIDTH="25%">');
1044 htp.p('<table cellpadding=0 cellspacing=0 border=0>');
1045 htp.p('<tr>');
1046 htp.tableData(htf.nobr(temp_attribute));
1047 htp.tableClose;
1048
1049 else
1050 htp.tableData(htf.formSelectOpen('a_1')||temp_text);
1051 end if;
1052 -- end of attributes select list
1053
1054
1055 -- build select list of conditions
1056 temp_text := '';
1057 l_conditions2 := htf.formSelectOption('','SELECTED','VALUE=""');
1058 getLookups('ICX_CONDITIONS',l_lookup_codes, l_lookup_meanings);
1059 for i in 1..to_number(l_lookup_codes(0)) loop
1060 if l_lookup_codes(i) = 'DSTART' then
1061 temp_text := temp_text||htf.formSelectOption(l_lookup_meanings(i),'SELECTED','VALUE="'||l_lookup_codes(i)||'"');
1062 l_conditions2 := l_conditions2||htf.formSelectOption(l_lookup_meanings(i),'','VALUE="'||l_lookup_codes(i)||'"');
1063 elsif instr(l_icx_custom_call,'NOCONTAIN') > 0
1064 and l_lookup_codes(i) = 'CCONTAIN'
1065 then
1066 temp_text := temp_text;
1067 else
1068 temp_text := temp_text||htf.formSelectOption(l_lookup_meanings(i),'','VALUE="'||l_lookup_codes(i)||'"');
1069 l_conditions2 := l_conditions2||htf.formSelectOption(l_lookup_meanings(i),'','VALUE="'||l_lookup_codes(i)||'"');
1070 end if;
1071 end loop;
1072 temp_text := temp_text||htf.formSelectClose;
1073 l_conditions2 := l_conditions2||htf.formSelectClose;
1074 htp.tableData(htf.formSelectOpen('c_1')||temp_text);
1075 -- end of conditions select list
1076
1077
1078 -- print text field
1079 htp.tableData(htf.formText('i_1',20,20));
1080 if x = 1 then
1081 htp.p('<SCRIPT LANGUAGE="JavaScript">');
1082 htp.p('queryText()');
1083 htp.p('</SCRIPT>');
1084 end if;
1085
1086 htp.p('<!-- This is a button table containing 3 buttons. The first row defines the edges and tops-->');
1087 htp.p('<TD ALIGN="LEFT" WIDTH="100%">');
1088 htp.p('<table cellpadding=0 cellspacing=0 border=0>');
1089 htp.p('<tr>');
1090 htp.p('<!-- left hand button, round left side and square right side-->');
1091 htp.p('<td rowspan=5><img src=/OA_MEDIA/FNDBRNDL.gif></td>');
1092 -- bug 1235659
1093 -- htp.p('<td bgcolor=#333333><img src=/OA_MEDIA/FNDPX3.gif></td>');
1094 htp.p('<td bgcolor=#cccccc></td>'); --add
1095 htp.p('<td rowspan=5><img src=/OA_MEDIA/FNDBSQRR.gif></td>');
1096 htp.p('<!-- standard spacer between square button images--> ');
1097 htp.p('<td width=2 rowspan=5></td>');
1098
1099 if num_attributes > 1 then
1100
1101 htp.p('<!-- middle button with squared ends on both left and right--> ');
1102 htp.p('<td rowspan=5><img src=/OA_MEDIA/FNDBSQRL.gif></td>');
1103 -- bug 1235659
1104 -- htp.p('<td bgcolor=#333333><img src=/OA_MEDIA/FNDPX3.gif></td>');
1105 htp.p('<td bgcolor=#cccccc></td>');
1106 htp.p('<td rowspan=5><img src=/OA_MEDIA/FNDBSQRR.gif></td>');
1107 htp.p('<!-- standard spacer between square button images--> ');
1108 htp.p('<td width=2 rowspan=5></td>');
1109
1110 end if;
1111
1112 htp.p('<!-- right hand button, square left side and round right side--> ');
1113 htp.p('<td rowspan=5><img src=/OA_MEDIA/FNDBSQRL.gif></td>');
1114 -- bug 1235659
1115 -- htp.p('<td bgcolor=#333333><img src=/OA_MEDIA/FNDPX3.gif></td>');
1116 htp.p('<td bgcolor=#cccccc></td>');
1117 htp.p('<td rowspan=5><img src=/OA_MEDIA/FNDBRNDR.gif></td>');
1118 htp.p('</tr>');
1119 htp.p('<tr>');
1120 htp.p('<!-- one cell of this type required for every button -->');
1121 htp.p('<td bgcolor=#ffffff><img src=/OA_MEDIA/FNDPX6.gif></td>');
1122 htp.p('<td bgcolor=#ffffff><img src=/OA_MEDIA/FNDPX6.gif></td>');
1123 htp.p('<td bgcolor=#ffffff><img src=/OA_MEDIA/FNDPX6.gif></td>');
1124
1125 htp.p('</tr>');
1126 htp.p('<tr>');
1127 htp.p('<!-- Text and links for each button are listed here-->');
1128 htp.p('<td bgcolor=#cccccc height=20 nowrap><a href="javascript:LOV_check('||num_attributes||')"><font class=button>'||LOV_prompts(1));
1129 htp.p('</FONT></TD>');
1130 htp.p('<TD bgcolor=#cccccc height=20 nowrap><A href="javascript:clearField()"><FONT class=button>'||LOV_prompts(2));
1131 htp.p('</FONT></TD>');
1132
1133 if num_attributes > 1 then
1134 if c_lines = 1 then
1135 htp.p('<TD bgcolor=#cccccc height=20 nowrap><A href="javascript:Header_submit(5)"><FONT class=button>'||LOV_prompts(8));
1136 else
1137 htp.p('<TD bgcolor=#cccccc height=20 nowrap><A href="javascript:Header_submit(1)"><FONT class=button>'||LOV_prompts(9));
1138 end if;
1139 end if;
1140
1141 htp.p('</FONT></A></TD>');
1142 htp.p('</TR>');
1143
1144 htp.p('<TR>');
1145 htp.p('<TD bgcolor=#666666><IMG src=/OA_MEDIA/FNDPX3.gif></TD>');
1146 htp.p('<TD bgcolor=#666666><IMG src=/OA_MEDIA/FNDPX3.gif></TD>');
1147 htp.p('<TD bgcolor=#666666><IMG src=/OA_MEDIA/FNDPX3.gif></TD>');
1148 htp.p('</TR>');
1149 htp.p('<TR>');
1150 -- bug 1235659
1151 -- htp.p('<TD bgcolor=#333333><IMG src=/OA_MEDIA/FNDPX3.gif></TD>');
1152 -- htp.p('<TD bgcolor=#333333><IMG src=/OA_MEDIA/FNDPX3.gif></TD>');
1153 -- htp.p('<TD bgcolor=#333333><IMG src=/OA_MEDIA/FNDPX3.gif></TD>');
1154 htp.p('<TD bgcolor=#cccccc></TD>');
1155 htp.p('<TD bgcolor=#cccccc></TD>');
1156 htp.p('<TD bgcolor=#cccccc></TD>');
1157
1158 htp.p('</TR>');
1159 htp.p('</TABLE>');
1160 htp.p('</TD>');
1161 htp.p('</TR>');
1162
1163 -- Create extra query lines if necessary
1164 if c_lines <> 1 then
1165 for i in 2..to_number(c_lines) loop
1166 l_display_line := '<TR><TD>'||htf.formSelectOpen('a_'||i)||
1167 l_attributes2||htf.formSelectClose||'</TD>'||
1168 '<TD>'||htf.formSelectOpen('c_'||i)||
1169 l_conditions2||htf.formSelectClose||'</TD>'||
1170 '<TD>'||htf.formText('i_'||i,20)||'</TD></TR>';
1171 htp.p(l_display_line);
1172 end loop;
1173 end if;
1174
1175 htp.tableRowClose;
1176
1177 -- add case-sensitive check box
1178 if l_matchcase_lov = 'Checked' -- 1550749
1179 then
1180 htp.tableRowOpen;
1181 htp.tableData(htf.formCheckBox('case_sensitive','','CHECKED')||LOV_prompts(12));
1182 htp.tableRowClose;
1183 htp.tableClose;
1184 end if;
1185
1186 if l_matchcase_lov = 'Hidden'
1187 then
1188
1189 fnd_message.set_name('ICX','ICX_MATCHCASE_LOV');
1190 l_message := fnd_message.get;
1191
1192 htp.tableRowOpen;
1193 htp.tableData(htf.italic(l_message),NULL,NULL,NULL,NULL,3,NULL);
1194 htp.tableData(htf.formHidden('case_sensitive', 'on'));
1195 htp.tableRowClose;
1196 htp.tableClose;
1197 end if;
1198
1199 if l_matchcase_lov = 'Unchecked'
1200 then
1201 htp.tableRowOpen;
1202 htp.tableData(htf.formCheckBox('case_sensitive')||LOV_prompts(12));
1203 htp.tableRowClose;
1204 htp.tableClose;
1205 end if;
1206
1207 --- end of 1550749
1208
1209
1210 htp.p('</CENTER>');
1211 htp.formClose;
1212 htp.bodyClose;
1213
1214 end if; -- ValidateSession
1215
1216 exception
1217 when others then
1218 err_num := SQLCODE;
1219 temp_text := SQLERRM;
1220 select substr(temp_text,12,512) into err_mesg from dual;
1221 temp_message := err_mesg;
1222 icx_util.add_error(temp_message);
1223 icx_util.error_page_print;
1224
1225 end; -- LOVHeader
1226
1227
1228
1229
1230 procedure LOVValues (p_LOV_foreign_key_name in varchar2,
1231 p_LOV_region_id in number,
1232 p_LOV_region in varchar2,
1233 p_attribute_app_id in number,
1234 p_attribute_code in varchar2,
1235 p_region_app_id in number,
1236 p_region_code in varchar2,
1237 c_form_name in varchar2,
1238 c_frame_name in varchar2,
1239 c_where_clause in varchar2,
1240 x in number,
1241 start_row in number,
1242 p_end_row in number,
1243 a_1 in varchar2,
1244 c_1 in varchar2,
1245 i_1 in varchar2,
1246 a_2 in varchar2,
1247 c_2 in varchar2,
1248 i_2 in varchar2,
1249 a_3 in varchar2,
1250 c_3 in varchar2,
1251 i_3 in varchar2,
1252 a_4 in varchar2,
1253 c_4 in varchar2,
1254 i_4 in varchar2,
1255 a_5 in varchar2,
1256 c_5 in varchar2,
1257 i_5 in varchar2,
1258 case_sensitive in varchar2) is
1259
1260 cursor lov_out_attributes is
1261 select ATTRIBUTE_CODE, LOV_ATTRIBUTE_CODE
1262 from AK_REGION_ITEMS_VL
1263 where REGION_APPLICATION_ID = p_region_app_id
1264 and REGION_CODE = p_region_code
1265 and LOV_REGION_APPLICATION_ID = p_LOV_region_id
1266 and LOV_REGION_CODE = p_LOV_region
1267 and LOV_FOREIGN_KEY_NAME = p_LOV_foreign_key_name
1268 order by DISPLAY_SEQUENCE;
1269
1270 cursor js_out_attributes is
1271 select ATTRIBUTE_CODE, LOV_ATTRIBUTE_CODE, REGION_DEFAULTING_API_PROC
1272 from AK_REGION_ITEMS_VL
1273 where REGION_APPLICATION_ID = p_region_app_id
1274 and REGION_CODE = p_region_code
1275 and LOV_REGION_APPLICATION_ID = p_LOV_region_id
1276 and LOV_REGION_CODE = p_LOV_region
1277 and LOV_FOREIGN_KEY_NAME = p_LOV_foreign_key_name
1278 and REGION_DEFAULTING_API_PROC is not null
1279 order by DISPLAY_SEQUENCE;
1280
1281 cursor lov_query_columns is
1282 select d.COLUMN_NAME
1283 from AK_ATTRIBUTES a,
1284 AK_REGION_ITEMS_VL b,
1285 AK_REGIONS c,
1286 AK_OBJECT_ATTRIBUTES d
1287 where b.REGION_APPLICATION_ID = p_LOV_region_id
1288 and b.REGION_CODE = p_LOV_region
1289 and b.ATTRIBUTE_APPLICATION_ID = d.ATTRIBUTE_APPLICATION_ID
1290 and b.ATTRIBUTE_CODE = d.ATTRIBUTE_CODE
1291 and b.REGION_APPLICATION_ID = c.REGION_APPLICATION_ID
1292 and b.REGION_CODE = c.REGION_CODE
1293 and c.DATABASE_OBJECT_NAME = d.DATABASE_OBJECT_NAME
1294 and d.ATTRIBUTE_APPLICATION_ID = b.ATTRIBUTE_APPLICATION_ID
1295 and d.ATTRIBUTE_CODE = b.ATTRIBUTE_CODE
1296 and d.ATTRIBUTE_APPLICATION_ID = a.ATTRIBUTE_APPLICATION_ID
1297 and d.ATTRIBUTE_CODE = a.ATTRIBUTE_CODE
1298 order by b.DISPLAY_SEQUENCE;
1299
1300 c_agent varchar2(100) := owa_util.get_cgi_env('SCRIPT_NAME');
1301 c_language varchar2(30);
1302 LOV_title varchar2(80);
1303 LOV_prompts g_prompts_table;
1304 err_num number;
1305 err_mesg varchar2(512);
1306 temp_message varchar2(2000);
1307 temp_text varchar2(2000);
1308 where_clause varchar2(2000);
1309 where_bind_vals VARCHAR2(2000);--mputman
1310 order_clause varchar2(2000);
1311 l_responsibility_id number;
1312 l_user_id number;
1313 i number;
1314 j number;
1315 k number;
1316 l number;
1317 base_region_attr varchar2(30);
1318 LOV_region_attr varchar2(30);
1319 js_proc_name varchar2(30);
1320 temp_a_1 varchar2(51);
1321 temp_column varchar2(30);
1322 clicked_columns varchar2(2000);
1323 end_row number;
1324 total_rows number;
1325 l_query_size number;
1326 l_max_rows number;
1327 l_result_row_table icx_util.char240_table;
1328 l_clicked_vars varchar2(2000);
1329 l_attribute_name varchar2(240);
1330 l_js_proc_text varchar2(2000);
1331 l_where_clause varchar2(2000);
1332 l_query_binds ak_query_pkg.bind_tab;
1333 l_error boolean;
1334 l_where_temp VARCHAR2(2000); -- mputman added
1335 c_where_bind_vals VARCHAR2(2000); -- mputman added
1336 l_query_binds_index NUMBER;
1337
1338 begin
1339 if icx_sec.validateSession then
1340
1341 l_responsibility_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
1342 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
1343
1344 c_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
1345 getPrompts(601,'ICX_LOV',LOV_title,LOV_prompts);
1346
1347 l_error := FALSE;
1348
1349 -- x = 1 or 2 indicates that Object Navigator should be called to
1350 -- perform query and display data
1351 if x = 1 or x = 2 then
1352
1353 -- Perform Object Navigator query
1354
1355 -- Call whereSegment to construct where clause
1356 if case_sensitive = 'on' then
1357 where_clause := icx_on_utilities.whereSegment
1358 (a_1 => a_1,
1359 c_1 => c_1,
1360 i_1 => i_1,
1361 a_2 => a_2,
1362 c_2 => c_2,
1363 i_2 => i_2,
1364 a_3 => a_3,
1365 c_3 => c_3,
1366 i_3 => i_3,
1367 a_4 => a_4,
1368 c_4 => c_4,
1369 i_4 => i_4,
1370 a_5 => a_5,
1371 c_5 => c_5,
1372 i_5 => i_5,
1373 m => case_sensitive);
1374 else
1375 where_clause := icx_on_utilities.whereSegment
1376 (a_1 => a_1,
1377 c_1 => c_1,
1378 i_1 => i_1,
1379 a_2 => a_2,
1380 c_2 => c_2,
1381 i_2 => i_2,
1382 a_3 => a_3,
1383 c_3 => c_3,
1384 i_3 => i_3,
1385 a_4 => a_4,
1386 c_4 => c_4,
1387 i_4 => i_4,
1388 a_5 => a_5,
1389 c_5 => c_5,
1390 i_5 => i_5);
1391 end if;
1392
1393
1394
1395 -- unpack where clause to use bind variables
1396 icx_on_utilities.unpack_whereSegment(where_clause,l_where_clause,l_query_binds);
1397
1398 l_where_temp:= icx_call.decrypt2(c_where_clause); --mputman added
1399
1400 IF substrb(l_where_temp,1,2)='@@'THEN
1401 c_where_bind_vals := substrb(l_where_temp,(instrb(l_where_temp,'@@',1,2)+2),length(l_where_temp));
1402 l_where_temp := substrb(l_where_temp,3,(instrb(l_where_temp,'@@',1,2)-3));
1403 END IF;
1404
1405 -- Add where clause LOV parameter to generated where clause
1406 if c_where_clause is not null then
1407 if l_where_clause is null then
1408 l_where_clause := l_where_temp;
1409 ELSE --l_where_clause is NOT null
1410 l_where_clause := l_where_clause||' and '||l_where_temp;
1411 end if; -- l_where_clause
1412 /*
1413 c_where_bind_vals is an encrypted '*' delimited string terminated by '**]' that
1414 contains the values associated to the binds in c_where_clause (encrypted using
1415 icx_call.encrypt2()). If c_where_bind_vals is null then we assume that c_where_clause
1416 does not contain any bind variables/values. If it is not null, we exepect the
1417 decrypted value to provide the bind values for the bind variables to be named
1418 ':ICXBIND_W(n)' starting with '0' and incrementing by one for each bind variable.
1419 */
1420 end if; --c_where_clause
1421
1422 IF c_where_bind_vals IS NOT NULL THEN
1423 l_query_binds_index:=l_query_binds.COUNT;
1424 icx_on_utilities.unpack_whereSegment(c_where_bind_vals,l_query_binds,l_query_binds_index);
1425 END IF;
1426
1427
1428
1429 -- Create order clause
1430 open lov_query_columns;
1431 i := 0;
1432 loop
1433 fetch lov_query_columns into temp_column;
1434 exit when lov_query_columns%NOTFOUND;
1435 i := i + 1;
1436 if substr(a_1,2,31) = temp_column then
1437 order_clause := i;
1438 exit;
1439 end if;
1440 end loop;
1441 close lov_query_columns;
1442
1443 -- Look up the number of rows to display
1444 select QUERY_SET, MAX_ROWS
1445 into l_query_size, l_max_rows
1446 from ICX_PARAMETERS;
1447
1448 -- figure end row value to display */
1449 if p_end_row is null then
1450 end_row := l_query_size;
1451 else
1452 end_row := p_end_row;
1453 end if;
1454
1455 -- Call to Object Navigator to execute query and return data
1456 -- as well as object and region structures
1457 ak_query_pkg.exec_query (
1458 P_PARENT_REGION_APPL_ID => p_LOV_region_id ,
1459 P_PARENT_REGION_CODE => p_LOV_region ,
1460 P_WHERE_CLAUSE => l_where_clause ,
1461 P_WHERE_BINDS => l_query_binds ,
1462 P_ORDER_BY_CLAUSE => order_clause ,
1463 P_RESPONSIBILITY_ID => l_responsibility_id ,
1464 P_USER_ID => l_user_id ,
1465 P_RETURN_PARENTS => 'T' ,
1466 P_RETURN_CHILDREN => 'F' ,
1467 P_RANGE_LOW => start_row ,
1468 P_RANGE_HIGH => end_row ,
1469 P_MAX_ROWS => l_max_rows);
1470
1471 if ak_query_pkg.g_regions_table(0).total_result_count = l_max_rows then
1472 l_error := TRUE;
1473 end if;
1474
1475 end if; -- Do Object Navigator query
1476
1477
1478 htp.htmlOpen;
1479 htp.headOpen;
1480 -- htp.p('<LINK REL=STYLESHEET HREF="/OA_HTML/US/PORSTYLE.css" TYPE="text/css">');
1481
1482 js.scriptOpen;
1483
1484 -- If this is the initial LOV call, we need to write the autoquery
1485 -- Javascript function to perform autoquery if necessary
1486 if x = 0 then
1487
1488 -- Need to fetch the value of the base LOV attribute
1489 -- column name from the LOV object to be used in the autoquery
1490 select substr(a.DATA_TYPE,1,1)||d.COLUMN_NAME
1491 into temp_a_1
1492 from AK_ATTRIBUTES a,
1493 AK_REGION_ITEMS_VL b,
1494 AK_REGIONS c,
1495 AK_OBJECT_ATTRIBUTES d
1496 where b.REGION_APPLICATION_ID = p_LOV_region_id
1497 and b.REGION_CODE = p_LOV_region
1498 and b.NODE_QUERY_FLAG = 'Y'
1499 and b.ATTRIBUTE_APPLICATION_ID = d.ATTRIBUTE_APPLICATION_ID
1500 and b.ATTRIBUTE_CODE = d.ATTRIBUTE_CODE
1501 and b.REGION_APPLICATION_ID = c.REGION_APPLICATION_ID
1502 and b.REGION_CODE = c.REGION_CODE
1503 and c.DATABASE_OBJECT_NAME = d.DATABASE_OBJECT_NAME
1504 and d.ATTRIBUTE_APPLICATION_ID = b.ATTRIBUTE_APPLICATION_ID
1505 and d.ATTRIBUTE_CODE = b.ATTRIBUTE_CODE
1506 and d.ATTRIBUTE_APPLICATION_ID = a.ATTRIBUTE_APPLICATION_ID
1507 and d.ATTRIBUTE_CODE = a.ATTRIBUTE_CODE
1508 and b.DISPLAY_SEQUENCE = (
1509 select min(e.DISPLAY_SEQUENCE)
1510 from AK_REGION_ITEMS_VL e
1511 where e.REGION_APPLICATION_ID = p_LOV_region_id
1512 and e.REGION_CODE = p_LOV_region);
1513
1514 -- Write autoquery function
1515 htp.p('function autoquery() {');
1516 if c_frame_name is null then
1517 htp.p('if (parent.opener.parent.document.'||c_form_name||'.'||p_attribute_code||'.value != "") {
1518 document.LOVValues.i_1.value = parent.opener.parent.document.'||c_form_name||'.'||p_attribute_code||'.value');
1519 else
1520 htp.p('if (parent.opener.parent.'||c_frame_name||'.document.'||c_form_name||'.'||p_attribute_code||'.value != "") {
1521 document.LOVValues.i_1.value = parent.opener.parent.'||c_frame_name||'.document.'||c_form_name||'.'||p_attribute_code||'.value');
1522 end if;
1523 fnd_message.set_name('ICX','ICX_LOV_HINT');
1524 htp.p('document.LOVValues.a_1.value = "'||temp_a_1||'"
1525 document.LOVValues.submit()
1526 } else {
1527 document.write("<CENTER>")
1528 document.write("<H3>'||icx_util.replace_quotes(fnd_message.get)||'</H3>")
1529 document.write("</CENTER>")
1530 }
1531 }');
1532
1533 end if;
1534
1535
1536 -- If this is not the initial LOV call, we need to write the clicked
1537 -- and LOV_rows javascript functions into the header of the page
1538 if x <> 0 then
1539
1540 -- build list of parameters for clicked javascript function
1541 -- first the direct out values
1542 open lov_out_attributes;
1543 i := 0;
1544 loop
1545 fetch lov_out_attributes into base_region_attr, LOV_region_attr;
1546 exit when lov_out_attributes%NOTFOUND;
1547 i := i + 1;
1548 if (i = 1) then
1549 clicked_columns := 'l_'||LOV_region_attr;
1550 else
1551 clicked_columns := clicked_columns||',l_'||LOV_region_attr;
1552 end if;
1553 end loop;
1554 close lov_out_attributes;
1555
1556 -- and then the javascript procedure out values
1557 open js_out_attributes;
1558 loop
1559 fetch js_out_attributes into base_region_attr, LOV_region_attr, js_proc_name;
1560 exit when js_out_attributes%NOTFOUND;
1561 i := i + 1;
1562 if (i = 1) then
1563 clicked_columns := 'ljs_'||LOV_region_attr;
1564 else
1565 clicked_columns := clicked_columns||',ljs_'||LOV_region_attr;
1566 end if;
1567 end loop;
1568 close js_out_attributes;
1569
1570
1571 -- write clicked javascript function in the html header
1572 -- first the direct out values
1573 htp.p('function clicked('||clicked_columns||') {');
1574 open lov_out_attributes;
1575 loop
1576 fetch lov_out_attributes into base_region_attr, LOV_region_attr;
1577 exit when lov_out_attributes%NOTFOUND;
1578 if c_frame_name is not null then
1579 htp.p('parent.opener.parent.'||c_frame_name||'.document.'||c_form_name||'.'||base_region_attr||'.value = l_'||LOV_region_attr);
1580 else
1581 htp.p('parent.opener.parent.document.'||c_form_name||'.'||base_region_attr||'.value = l_'||LOV_region_attr);
1582 end if;
1583 end loop;
1584 close lov_out_attributes;
1585
1586 -- and then the javascript procedure out values
1587 i := 0;
1588 open js_out_attributes;
1589 loop
1590 fetch js_out_attributes into base_region_attr, LOV_region_attr, js_proc_name;
1591 exit when js_out_attributes%NOTFOUND;
1592 i := i + 1;
1593 if (i = 1) then
1594 if c_frame_name is not null then
1595 l_js_proc_text := 'parent.opener.parent.'||c_frame_name||'.'||js_proc_name||'(ljs_'||LOV_region_attr;
1596 else
1597 l_js_proc_text := 'parent.opener.parent.'||js_proc_name||'(ljs_'||LOV_region_attr;
1598 end if;
1599 else
1600 l_js_proc_text := l_js_proc_text||',ljs_'||LOV_region_attr;
1601 end if;
1602 end loop;
1603 if i > 0 then
1604 l_js_proc_text := l_js_proc_text||')';
1605 end if;
1606 close js_out_attributes;
1607 htp.p(l_js_proc_text);
1608
1609 htp.p('parent.self.close()
1610 }');
1611
1612
1613 -- Javascript function to handle CD buttons
1614 htp.p('function LOV_rows(start_num, end_num) {
1615 document.LOVValues.start_row.value = start_num
1616 document.LOVValues.p_end_row.value = end_num
1617 document.LOVValues.x.value = 2
1618 document.LOVValues.submit()
1619 }');
1620
1621
1622 end if;
1623
1624 js.scriptClose;
1625 htp.headClose;
1626
1627 htp.p('<BODY bgcolor="#cccccc" onload="parent.LOVHeader.search = ''Y''">');
1628
1629 htp.formOpen(c_agent||'/icx_util.LOVValues','POST','','','NAME="LOVValues"');
1630
1631 htp.formHidden('p_LOV_foreign_key_name',p_LOV_foreign_key_name);
1632 htp.formHidden('p_LOV_region_id',p_LOV_region_id);
1633 htp.formHidden('p_LOV_region',p_LOV_region );
1634 htp.formHidden('p_attribute_app_id',p_attribute_app_id);
1635 htp.formHidden('p_attribute_code',p_attribute_code);
1636 htp.formHidden('p_region_app_id',p_region_app_id);
1637 htp.formHidden('p_region_code',p_region_code);
1638 htp.formHidden('c_frame_name',c_frame_name);
1639 htp.formHidden('c_form_name',c_form_name);
1640 htp.formHidden('c_where_clause',c_where_clause);
1641 htp.formHidden('x','1');
1642 htp.formHidden('start_row',start_row);
1643 htp.formHidden('p_end_row',p_end_row);
1644 htp.formHidden('a_1',a_1);
1645 htp.formHidden('c_1',c_1);
1646 htp.formHidden('i_1',i_1);
1647 htp.formHidden('a_2',a_2);
1648 htp.formHidden('c_2',c_2);
1649 htp.formHidden('i_2',i_2);
1650 htp.formHidden('a_3',a_3);
1651 htp.formHidden('c_3',c_3);
1652 htp.formHidden('i_3',i_3);
1653 htp.formHidden('a_4',a_4);
1654 htp.formHidden('c_4',c_4);
1655 htp.formHidden('i_4',i_4);
1656 htp.formHidden('a_5',a_5);
1657 htp.formHidden('c_5',c_5);
1658 htp.formHidden('i_5',i_5);
1659 htp.formHidden('case_sensitive',case_sensitive);
1660 --htp.formHidden('c_where_bind_vals',c_where_bind_vals);
1661
1662 -- check if error messages were generated by the ak query
1663 if not l_error then
1664
1665 if x = 0 then
1666
1667 -- x = 0 indicates the initial call to the LOV
1668 -- Autoquery or Display hint about selection criteria
1669 htp.p('<SCRIPT LANGUAGE="JavaScript">');
1670 htp.p('autoquery()');
1671 htp.p('</SCRIPT>');
1672
1673 end if;
1674
1675
1676 -- Display results
1677 if x = 1 or x = 2 then
1678
1679 -- Look up the number of rows to display
1680 select QUERY_SET into l_query_size
1681 from ICX_PARAMETERS;
1682
1683
1684 -- get number of total rows returned by lov to be used to
1685 -- determine if we need to display the next/previous buttons
1686 total_rows := ak_query_pkg.g_regions_table(0).total_result_count;
1687
1688 if end_row > total_rows then
1689 end_row := total_rows;
1690 end if;
1691
1692
1693 -- display LOV data and CD buttons if necessary
1694 j := 0;
1695 for i in 1..ak_query_pkg.g_results_table.COUNT loop
1696 j := j + 1;
1697
1698 -- If this is the first iteration of the loop then
1699 -- display next/previous set buttons if list of values returns
1700 -- more than the standard query size and also display
1701 -- the table header
1702 if j = 1 then
1703 lovrecordbuttons (
1704 P_LANGUAGE_CODE => c_language,
1705 P_PACKPROC => 'JS',
1706 P_START_ROW => start_row,
1707 P_STOP_ROW => end_row,
1708 P_ENCRYPTED_WHERE => '1',
1709 P_QUERY_SET => l_query_size,
1710 P_ROW_COUNT => total_rows,
1711 p_top => TRUE,
1712 P_JSPROC => 'LOV_rows',
1713 p_hidden => '',
1714 p_update => FALSE,
1715 p_target => '',
1716 P_LIST_COUNT => TRUE,
1717 P_OBJECT_DISP_NAME => ak_query_pkg.g_items_table(1).attribute_label_long);
1718
1719 -- display table header of LOV
1720 htp.p('<table width=98% bgcolor=#999999 cellpadding=2 cellspacing=0 border=0>');
1721 htp.p('<tr><td>');
1722 htp.p('<table width=100% cellpadding=2 cellspacing=1 border=0>');
1723 htp.p('<TR BGColor="336699">');
1724 for k in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
1725 if ak_query_pkg.g_items_table(k).secured_column = 'F' and
1726 ak_query_pkg.g_items_table(k).node_display_flag = 'Y' then
1727
1728 /*
1729 htp.p('<TD align=center valign=bottom bgcolor="336699">'||
1730 '<FONT class=promptwhite>'||
1731 ak_query_pkg.g_items_table(k).attribute_label_long||
1732 '</TH>');
1733 */
1734 htp.p('<TD align=center valign=bottom bgcolor="336699">'||
1735 '<FONT color=#FFFFFF>'||
1736 ak_query_pkg.g_items_table(k).attribute_label_long||
1737 '</TH>');
1738
1739
1740 end if;
1741 end loop;
1742 htp.tableRowClose;
1743
1744 end if; -- CD Buttons and table header
1745
1746 -- start bug 1853315
1747 htp.tableRowOpen;
1748 htp.p('<font Color=#000000>');
1749 htp.tableRowClose;
1750 -- end bug 1853315
1751
1752
1753 -- load data for current row into temp pl/sql table
1754 transfer_Row_To_Column(ak_query_pkg.g_results_table(i-1), l_result_row_table);
1755
1756
1757 -- build variables to send to clicked javascript function
1758 -- regular out variables
1759 open lov_out_attributes;
1760 k := 0;
1761 loop
1762 fetch lov_out_attributes into base_region_attr, LOV_region_attr;
1763 exit when lov_out_attributes%NOTFOUND;
1764 k := k + 1;
1765 for l in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
1766 if LOV_region_attr = ak_query_pkg.g_items_table(l).attribute_code then
1767 if (k = 1) then
1768 l_clicked_vars := ''''||replace_onMouseOver_quotes(l_result_row_table(ak_query_pkg.g_items_table(l).value_id))||'''';
1769 else
1770 l_clicked_vars := l_clicked_vars||','''||replace_onMouseOver_quotes(l_result_row_table(ak_query_pkg.g_items_table(l).value_id))||'''';
1771 end if;
1772 end if;
1773 end loop;
1774 end loop;
1775 close lov_out_attributes;
1776
1777 -- and the javascript procedure out values
1778 open js_out_attributes;
1779 loop
1780 fetch js_out_attributes into base_region_attr, LOV_region_attr, js_proc_name;
1781 exit when js_out_attributes%NOTFOUND;
1782 k := k + 1;
1783 for l in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
1784 if LOV_region_attr = ak_query_pkg.g_items_table(l).attribute_code then
1785 if (k = 1) then
1786 l_clicked_vars := ''''||replace_onMouseOver_quotes(l_result_row_table(ak_query_pkg.g_items_table(l).value_id))||'''';
1787 else
1788 l_clicked_vars := l_clicked_vars||','''||replace_onMouseOver_quotes(l_result_row_table(ak_query_pkg.g_items_table(l).value_id))||'''';
1789 end if;
1790 end if;
1791 end loop;
1792 end loop;
1793 close js_out_attributes;
1794
1795
1796 -- display one row of data
1797 if (round(j/2) = j/2) then
1798 htp.p('<TR BGColor="ffffff">');
1799 else
1800 htp.p('<TR BGColor="99ccff">');
1801 end if;
1802
1803 l := 0;
1804 for k in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
1805 if ak_query_pkg.g_items_table(k).secured_column = 'F' and
1806 ak_query_pkg.g_items_table(k).node_display_flag = 'Y' then
1807 l := l + 1;
1808 if l = 1 then
1809 htp.p(icx_on_utilities.formatData(htf.anchor('javascript:clicked('||l_clicked_vars||')',
1810 icx_on_utilities.formatText(l_result_row_table(ak_query_pkg.g_items_table(k).value_id),
1811 ak_query_pkg.g_items_table(k).bold,ak_query_pkg.g_items_table(k).italic)),ak_query_pkg.g_items_table(k).horizontal_alignment,ak_query_pkg.g_items_table(k).vertical_alignment));
1812 else
1813 htp.p(icx_on_utilities.formatData(icx_on_utilities.formatText(l_result_row_table(ak_query_pkg.g_items_table(k).value_id),
1814 ak_query_pkg.g_items_table(k).bold,ak_query_pkg.g_items_table(k).italic),ak_query_pkg.g_items_table(k).horizontal_alignment,ak_query_pkg.g_items_table(k).vertical_alignment));
1815 end if;
1816 end if;
1817 end loop;
1818
1819 htp.tableRowClose;
1820
1821 end loop; -- LOV data
1822
1823 htp.tableClose;
1824 htp.p('</TD>');
1825 htp.p('</TR>');
1826 htp.p('</TABLE>');
1827
1828
1829 -- print button set if appropriate
1830 if (total_rows > l_query_size) and not
1831 (start_row = 1 and end_row = total_rows) then
1832
1833 /*
1834 ** Show next and previous buttons
1835 */
1836 lovrecordbuttons(
1837 P_LANGUAGE_CODE => c_language,
1838 P_PACKPROC => 'JS',
1839 P_START_ROW => start_row,
1840 P_STOP_ROW => end_row,
1841 P_ENCRYPTED_WHERE => '1',
1842 P_QUERY_SET => l_query_size,
1843 P_ROW_COUNT => total_rows,
1844 p_top => TRUE,
1845 P_JSPROC => 'LOV_rows',
1846 p_hidden => '',
1847 p_update => FALSE,
1848 p_target => '',
1849 P_LIST_COUNT => FALSE,
1850 P_OBJECT_DISP_NAME => ak_query_pkg.g_items_table(1).attribute_label_long);
1851
1852 end if;
1853
1854
1855 -- display message if no rows were returned by query
1856 if j = 0 then
1857 select attribute_label_long into l_attribute_name
1858 from ak_region_items_vl
1859 where region_application_id = p_region_app_id
1860 and region_code = p_region_code
1861 and attribute_application_id = p_attribute_app_id
1862 and attribute_code = p_attribute_code;
1863 fnd_message.set_name('ICX','ICX_NO_RECORDS_FOUND');
1864 fnd_message.set_token('NAME_OF_REGION_TOKEN',l_attribute_name);
1865 htp.p('<H3>'||fnd_message.get||'</H3>');
1866 end if;
1867
1868 end if; -- Display results
1869
1870 else -- ak_query generated an error
1871 fnd_message.set_name('ICX','ICX_MAX_ROWS');
1872 htp.p('<CENTER>');
1873 htp.p('<H3>'||icx_util.replace_quotes(fnd_message.get)||'</H3>');
1874 htp.p('</CENTER>');
1875 end if; -- check if error messages were generated by the ak query
1876
1877 htp.formClose;
1878 htp.bodyClose;
1879 htp.htmlClose;
1880
1881 end if; -- validateSession
1882
1883 exception
1884 when others then
1885 err_num := SQLCODE;
1886 temp_text := SQLERRM;
1887 select substr(temp_text,12,512) into err_mesg from dual;
1888 temp_message := err_mesg;
1889 icx_util.add_error(temp_message);
1890 icx_util.error_page_print;
1891
1892 end; -- LOVValues
1893
1894
1895
1896
1897 procedure copyright is
1898 begin
1899 htp.p('<!-- Copyright ' || '&' || '#169; 2002 Oracle Corporation, All rights reserved. -->');
1900 end copyright;
1901
1902 procedure getPrompts( p_region_application_id in number,
1903 p_region_code in varchar2,
1904 p_title out NOCOPY varchar2,
1905 p_prompts out NOCOPY g_prompts_table) is
1906
1907 l_count number;
1908
1909 cursor items is -- removed select for ari.attribute_code since we didnt use it. mputman 1574527
1910 select arit.attribute_label_long
1911 from ak_region_items_tl arit,
1912 ak_region_items ari
1913 where
1914 arit.region_application_id = ari.region_application_id
1915 and arit.region_code = ari.region_code
1916 and arit.attribute_application_id = ari.attribute_application_id
1917 and arit.attribute_code = ari.attribute_code
1918 and arit.language = userenv('LANG')
1919 and ari.region_application_id = p_region_application_id
1920 and ari.region_code = p_region_code
1921 order by display_sequence;
1922
1923 cursor items_base is
1924 select a.ATTRIBUTE_LABEL_LONG,a.ATTRIBUTE_CODE
1925 from AK_REGION_ITEMS_TL a,
1926 AK_REGION_ITEMS b,
1927 FND_LANGUAGES c
1928 where b.REGION_APPLICATION_ID = p_region_application_id
1929 and b.REGION_CODE = p_region_code
1930 and b.ATTRIBUTE_APPLICATION_ID = a.ATTRIBUTE_APPLICATION_ID
1931 and b.ATTRIBUTE_CODE = a.ATTRIBUTE_CODE
1932 and a.LANGUAGE = c.LANGUAGE_CODE
1933 and c.INSTALLED_FLAG = 'B'
1934 order by b.DISPLAY_SEQUENCE;
1935
1936 begin
1937
1938 select NAME
1939 into p_title
1940 from AK_REGIONS_VL
1941 where REGION_APPLICATION_ID = p_region_application_id
1942 and REGION_CODE = p_region_code;
1943
1944 /*
1945 l_count := 0;
1946 for i in items loop
1947 l_count := l_count + 1;
1948 p_prompts(l_count) := i.ATTRIBUTE_LABEL_LONG;
1949 end loop;
1950 */--changed to support bulk fetch 1574527 mputman
1951 OPEN items;
1952 FETCH items BULK COLLECT INTO p_prompts;
1953 CLOSE items;
1954
1955
1956 l_count:= p_prompts.last;
1957
1958 p_prompts(0) := l_count;
1959 p_prompts(l_count + 1) := '';
1960
1961 exception
1962 when NO_DATA_FOUND then
1963 begin
1964 select NAME
1965 into p_title
1966 from AK_REGIONS_TL a,
1967 FND_LANGUAGES b
1968 where REGION_APPLICATION_ID = p_region_application_id
1969 and REGION_CODE = p_region_code
1970 and a.LANGUAGE = b.LANGUAGE_CODE
1971 and b.INSTALLED_FLAG = 'B' ;
1972
1973 l_count := 0;
1974 for i in items_base loop
1975 l_count := l_count + 1;
1976 p_prompts(l_count) := i.ATTRIBUTE_LABEL_LONG;
1977 end loop;
1978 p_prompts(0) := l_count;
1979 p_prompts(l_count + 1) := '';
1980 exception
1981 when NO_DATA_FOUND then
1982 p_title := '';
1983 p_prompts(0) := 0;
1984 end;
1985 end;
1986
1987 function getPrompt( p_region_application_id in number,
1988 p_region_code in varchar2,
1989 p_attribute_application_id in number,
1990 p_attribute_code in varchar2)
1991 return varchar2 is
1992 l_prompt varchar2(80);
1993 begin
1994
1995 select ATTRIBUTE_LABEL_LONG
1996 into l_prompt
1997 from AK_REGION_ITEMS_VL
1998 where REGION_APPLICATION_ID = p_region_application_id
1999 and REGION_CODE = p_region_code
2000 and ATTRIBUTE_APPLICATION_ID = p_attribute_application_id
2001 and ATTRIBUTE_CODE = p_attribute_code;
2002
2003 return l_prompt;
2004
2005 exception
2006 when NO_DATA_FOUND then
2007 begin
2008 select ATTRIBUTE_LABEL_LONG
2009 into l_prompt
2010 from AK_REGION_ITEMS_TL a,
2011 FND_LANGUAGES b
2012 where REGION_APPLICATION_ID = p_region_application_id
2013 and REGION_CODE = p_region_code
2014 and ATTRIBUTE_APPLICATION_ID = p_attribute_application_id
2015 and ATTRIBUTE_CODE = p_attribute_code
2016 and a.LANGUAGE = b.LANGUAGE_CODE
2017 and b.INSTALLED_FLAG = 'B' ;
2018
2019 return l_prompt;
2020 exception
2021 when NO_DATA_FOUND then
2022 l_prompt := '';
2023 return l_prompt;
2024 end;
2025 end;
2026
2027 procedure getLookups( p_lookup_type in varchar2,
2028 p_lookup_codes out NOCOPY g_lookup_code_table,
2029 p_lookup_meanings out NOCOPY g_lookup_meaning_table) is
2030
2031 cursor lookups is
2032 select LOOKUP_CODE,MEANING
2033 from FND_LOOKUPS
2034 where LOOKUP_TYPE = p_lookup_type
2035 and ENABLED_FLAG = 'Y'
2036 and sysdate >= nvl(START_DATE_ACTIVE,sysdate)
2037 and sysdate <= nvl(END_DATE_ACTIVE,sysdate)
2038 order by LOOKUP_CODE;
2039
2040 l_count number;
2041
2042 begin
2043
2044 l_count := 0;
2045 for l in lookups loop
2046 l_count := l_count + 1;
2047 p_lookup_codes(l_count) := l.LOOKUP_CODE;
2048 p_lookup_meanings(l_count) := l.MEANING;
2049 end loop;
2050 p_lookup_codes(0) := l_count;
2051 p_lookup_codes(l_count + 1) := '';
2052
2053 end;
2054
2055 procedure getLookup( p_lookup_type in varchar2,
2056 p_lookup_code in varchar2,
2057 p_meaning out NOCOPY varchar2) is
2058
2059 l_count number;
2060
2061 cursor lookups is
2062 select MEANING
2063 from FND_LOOKUPS
2064 where LOOKUP_TYPE = p_lookup_type
2065 and LOOKUP_CODE = p_lookup_code;
2066
2067 begin
2068
2069 l_count := 0;
2070 for l in lookups loop
2071 l_count := l_count + 1;
2072 p_meaning := l.MEANING;
2073 end loop;
2074
2075 end;
2076
2077 ----------------------------------------------------------------------------
2078 procedure error_page_setup is
2079 ----------------------------------------------------------------------------
2080
2081 begin
2082 error_table := empty_char2000table;
2083 TOTAL_ERRORS := 0;
2084 end error_page_setup;
2085
2086 ----------------------------------------------------------------------------
2087 procedure add_error(V_ERROR_IN varchar2) is
2088 ----------------------------------------------------------------------------
2089
2090 begin
2091 TOTAL_ERRORS := TOTAL_ERRORS + 1;
2092 error_table(TOTAL_ERRORS) := V_ERROR_IN;
2093 end add_error;
2094
2095
2096 ----------------------------------------------------------------------------
2097 function error_count
2098 return number is
2099 ----------------------------------------------------------------------------
2100
2101 begin
2102 return(TOTAL_ERRORS);
2103 end error_count;
2104
2105 ----------------------------------------------------------------------------
2106 procedure error_page_print is
2107 ----------------------------------------------------------------------------
2108
2109 j BINARY_INTEGER;
2110 begin
2111
2112 htp.htmlOpen;
2113 htp.bodyOpen(icx_admin_sig.background);
2114 FND_MESSAGE.SET_NAME('ICX', 'ICX_ERROR');
2115 htp.p('<H2>'||FND_MESSAGE.GET || '</H2>');
2116 htp.p('<ul>');
2117 for j in 1..TOTAL_ERRORS loop
2118 htp.p('<li>'||error_table(j));
2119 end loop;
2120 htp.p('</ul>');
2121 htp.bodyClose;
2122 htp.htmlClose;
2123
2124 end error_page_print;
2125
2126 ----------------------------------------------------------------------------
2127 procedure no_html_error_page_print is
2128 ----------------------------------------------------------------------------
2129
2130 j BINARY_INTEGER;
2131 begin
2132 FND_MESSAGE.SET_NAME('ICX', 'ICX_ERROR');
2133 htp.p(FND_MESSAGE.GET);
2134 htp.p('---------------------------------------------------------');
2135 for j in 1..TOTAL_ERRORS loop
2136 htp.p(error_table(j));
2137 end loop;
2138 end;
2139
2140 ----------------------------------------------------------------------------
2141 function get_color(v_name in varchar2)
2142 return varchar2 is
2143 ----------------------------------------------------------------------------
2144
2145 cursor getColor(v_color varchar2) is
2146 select COLOR_VALUE
2147 from ICX_COLORS
2148 where NAME = v_color;
2149
2150 v_value varchar2(30);
2151
2152 begin
2153 open getColor(v_name);
2154 fetch getColor into v_value;
2155 close getColor;
2156
2157 return(v_value);
2158
2159 end get_color;
2160
2161
2162
2163 procedure parse_string (
2164 in_str in varchar2,
2165 delimiter in varchar2,
2166 str_part1 out NOCOPY varchar2,
2167 str_part2 out NOCOPY varchar2) is
2168 first_str varchar2(100);
2169 pos number;
2170 begin
2171 pos := instrb(in_str, delimiter);
2172 if pos = 0 then
2173 str_part1 := in_str;
2174 str_part2 := null;
2175 end if;
2176 str_part1 := substrb(in_str, 1, pos-1);
2177 first_str := substrb(in_str, pos+1);
2178 str_part2 := ltrim(first_str, ' ');
2179 end parse_string;
2180
2181
2182
2183 function item_flex_seg (
2184 ri in rowid)
2185 return varchar2 is
2186 ret_val varchar2(2000) := NULL;
2187 begin
2188 if (ri is null) then
2189 return (null);
2190 else
2191 /*
2192 select msi.concatenated_segments
2193 into ret_val
2194 from mtl_system_items_kfv msi
2195 where rowid = ri;
2196 */
2197 return(ret_val);
2198 end if;
2199 end item_flex_seg;
2200
2201 function category_flex_seg (
2202 cat_id in number)
2203 return varchar2 is
2204 ret_val varchar2(2000) := NULL;
2205 begin
2206 if (cat_id is null) then
2207 return (null);
2208 else
2209 /*
2210 select mc.concatenated_segments
2211 into ret_val
2212 from mtl_categories_kfv mc
2213 where category_id = cat_id;
2214 */
2215 return(ret_val);
2216 end if;
2217 end category_flex_seg;
2218
2219
2220 -- The transfer_Row_To_Column utility takes one record returned by an
2221 -- Object Navigator query and changes the record into a pl/sql table
2222 procedure transfer_Row_To_Column(result_record in ak_query_pkg.result_rec,
2223 result_table out NOCOPY icx_util.char240_table) is
2224
2225 begin
2226
2227 result_table(1) := substr(result_record.value1,1,240);
2228 result_table(2) := substr(result_record.value2,1,240);
2229 result_table(3) := substr(result_record.value3,1,240);
2230 result_table(4) := substr(result_record.value4,1,240);
2231 result_table(5) := substr(result_record.value5,1,240);
2232 result_table(6) := substr(result_record.value6,1,240);
2233 result_table(7) := substr(result_record.value7,1,240);
2234 result_table(8) := substr(result_record.value8,1,240);
2235 result_table(9) := substr(result_record.value9,1,240);
2236 result_table(10) := substr(result_record.value10,1,240);
2237 result_table(11) := substr(result_record.value11,1,240);
2238 result_table(12) := substr(result_record.value12,1,240);
2239 result_table(13) := substr(result_record.value13,1,240);
2240 result_table(14) := substr(result_record.value14,1,240);
2241 result_table(15) := substr(result_record.value15,1,240);
2242 result_table(16) := substr(result_record.value16,1,240);
2243 result_table(17) := substr(result_record.value17,1,240);
2244 result_table(18) := substr(result_record.value18,1,240);
2245 result_table(19) := substr(result_record.value19,1,240);
2246 result_table(20) := substr(result_record.value20,1,240);
2247 result_table(21) := substr(result_record.value21,1,240);
2248 result_table(22) := substr(result_record.value22,1,240);
2249 result_table(23) := substr(result_record.value23,1,240);
2250 result_table(24) := substr(result_record.value24,1,240);
2251 result_table(25) := substr(result_record.value25,1,240);
2252 result_table(26) := substr(result_record.value26,1,240);
2253 result_table(27) := substr(result_record.value27,1,240);
2254 result_table(28) := substr(result_record.value28,1,240);
2255 result_table(29) := substr(result_record.value29,1,240);
2256 result_table(30) := substr(result_record.value30,1,240);
2257 result_table(31) := substr(result_record.value31,1,240);
2258 result_table(32) := substr(result_record.value32,1,240);
2259 result_table(33) := substr(result_record.value33,1,240);
2260 result_table(34) := substr(result_record.value34,1,240);
2261 result_table(35) := substr(result_record.value35,1,240);
2262 result_table(36) := substr(result_record.value36,1,240);
2263 result_table(37) := substr(result_record.value37,1,240);
2264 result_table(38) := substr(result_record.value38,1,240);
2265 result_table(39) := substr(result_record.value39,1,240);
2266 result_table(40) := substr(result_record.value40,1,240);
2267 result_table(41) := substr(result_record.value41,1,240);
2268 result_table(42) := substr(result_record.value42,1,240);
2269 result_table(43) := substr(result_record.value43,1,240);
2270 result_table(44) := substr(result_record.value44,1,240);
2271 result_table(45) := substr(result_record.value45,1,240);
2272 result_table(46) := substr(result_record.value46,1,240);
2273 result_table(47) := substr(result_record.value47,1,240);
2274 result_table(48) := substr(result_record.value48,1,240);
2275 result_table(49) := substr(result_record.value49,1,240);
2276 result_table(50) := substr(result_record.value50,1,240);
2277 result_table(51) := substr(result_record.value51,1,240);
2278 result_table(52) := substr(result_record.value52,1,240);
2279 result_table(53) := substr(result_record.value53,1,240);
2280 result_table(54) := substr(result_record.value54,1,240);
2281 result_table(55) := substr(result_record.value55,1,240);
2282 result_table(56) := substr(result_record.value56,1,240);
2283 result_table(57) := substr(result_record.value57,1,240);
2284 result_table(58) := substr(result_record.value58,1,240);
2285 result_table(59) := substr(result_record.value59,1,240);
2286 result_table(60) := substr(result_record.value60,1,240);
2287 result_table(61) := substr(result_record.value61,1,240);
2288 result_table(62) := substr(result_record.value62,1,240);
2289 result_table(63) := substr(result_record.value63,1,240);
2290 result_table(64) := substr(result_record.value64,1,240);
2291 result_table(65) := substr(result_record.value65,1,240);
2292 result_table(66) := substr(result_record.value66,1,240);
2293 result_table(67) := substr(result_record.value67,1,240);
2294 result_table(68) := substr(result_record.value68,1,240);
2295 result_table(69) := substr(result_record.value69,1,240);
2296 result_table(70) := substr(result_record.value70,1,240);
2297 result_table(71) := substr(result_record.value71,1,240);
2298 result_table(72) := substr(result_record.value72,1,240);
2299 result_table(73) := substr(result_record.value73,1,240);
2300 result_table(74) := substr(result_record.value74,1,240);
2301 result_table(75) := substr(result_record.value75,1,240);
2302 result_table(76) := substr(result_record.value76,1,240);
2303 result_table(77) := substr(result_record.value77,1,240);
2304 result_table(78) := substr(result_record.value78,1,240);
2305 result_table(79) := substr(result_record.value79,1,240);
2306 result_table(80) := substr(result_record.value80,1,240);
2307 result_table(81) := substr(result_record.value81,1,240);
2308 result_table(82) := substr(result_record.value82,1,240);
2309 result_table(83) := substr(result_record.value83,1,240);
2310 result_table(84) := substr(result_record.value84,1,240);
2311 result_table(85) := substr(result_record.value85,1,240);
2312 result_table(86) := substr(result_record.value86,1,240);
2313 result_table(87) := substr(result_record.value87,1,240);
2314 result_table(88) := substr(result_record.value88,1,240);
2315 result_table(89) := substr(result_record.value89,1,240);
2316 result_table(90) := substr(result_record.value90,1,240);
2317 result_table(91) := substr(result_record.value91,1,240);
2318 result_table(92) := substr(result_record.value92,1,240);
2319 result_table(93) := substr(result_record.value93,1,240);
2320 result_table(94) := substr(result_record.value94,1,240);
2321 result_table(95) := substr(result_record.value95,1,240);
2322 result_table(96) := substr(result_record.value96,1,240);
2323 result_table(97) := substr(result_record.value97,1,240);
2324 result_table(98) := substr(result_record.value98,1,240);
2325 result_table(99) := substr(result_record.value99,1,240);
2326 result_table(100) := substr(result_record.value100,1,240);
2327
2328 end transfer_Row_To_Column;
2329
2330 procedure transfer_Row_To_Column(result_record in ak_query_pkg.result_rec,
2331 result_table out NOCOPY icx_util.char4000_table) is
2332
2333 begin
2334 result_table(1) := result_record.value1;
2335 result_table(2) := result_record.value2;
2336 result_table(3) := result_record.value3;
2337 result_table(4) := result_record.value4;
2338 result_table(5) := result_record.value5;
2339 result_table(6) := result_record.value6;
2340 result_table(7) := result_record.value7;
2341 result_table(8) := result_record.value8;
2342 result_table(9) := result_record.value9;
2343 result_table(10) := result_record.value10;
2344 result_table(11) := result_record.value11;
2345 result_table(12) := result_record.value12;
2346 result_table(13) := result_record.value13;
2347 result_table(14) := result_record.value14;
2348 result_table(15) := result_record.value15;
2349 result_table(16) := result_record.value16;
2350 result_table(17) := result_record.value17;
2351 result_table(18) := result_record.value18;
2352 result_table(19) := result_record.value19;
2353 result_table(20) := result_record.value20;
2354 result_table(21) := result_record.value21;
2355 result_table(22) := result_record.value22;
2356 result_table(23) := result_record.value23;
2357 result_table(24) := result_record.value24;
2358 result_table(25) := result_record.value25;
2359 result_table(26) := result_record.value26;
2360 result_table(27) := result_record.value27;
2361 result_table(28) := result_record.value28;
2362 result_table(29) := result_record.value29;
2363 result_table(30) := result_record.value30;
2364 result_table(31) := result_record.value31;
2365 result_table(32) := result_record.value32;
2366 result_table(33) := result_record.value33;
2367 result_table(34) := result_record.value34;
2368 result_table(35) := result_record.value35;
2369 result_table(36) := result_record.value36;
2370 result_table(37) := result_record.value37;
2371 result_table(38) := result_record.value38;
2372 result_table(39) := result_record.value39;
2373 result_table(40) := result_record.value40;
2374 result_table(41) := result_record.value41;
2375 result_table(42) := result_record.value42;
2376 result_table(43) := result_record.value43;
2377 result_table(44) := result_record.value44;
2378 result_table(45) := result_record.value45;
2379 result_table(46) := result_record.value46;
2380 result_table(47) := result_record.value47;
2381 result_table(48) := result_record.value48;
2382 result_table(49) := result_record.value49;
2383 result_table(50) := result_record.value50;
2384 result_table(51) := result_record.value51;
2385 result_table(52) := result_record.value52;
2386 result_table(53) := result_record.value53;
2387 result_table(54) := result_record.value54;
2388 result_table(55) := result_record.value55;
2389 result_table(56) := result_record.value56;
2390 result_table(57) := result_record.value57;
2391 result_table(58) := result_record.value58;
2392 result_table(59) := result_record.value59;
2393 result_table(60) := result_record.value60;
2394 result_table(61) := result_record.value61;
2395 result_table(62) := result_record.value62;
2396 result_table(63) := result_record.value63;
2397 result_table(64) := result_record.value64;
2398 result_table(65) := result_record.value65;
2399 result_table(66) := result_record.value66;
2400 result_table(67) := result_record.value67;
2401 result_table(68) := result_record.value68;
2402 result_table(69) := result_record.value69;
2403 result_table(70) := result_record.value70;
2404 result_table(71) := result_record.value71;
2405 result_table(72) := result_record.value72;
2406 result_table(73) := result_record.value73;
2407 result_table(74) := result_record.value74;
2408 result_table(75) := result_record.value75;
2409 result_table(76) := result_record.value76;
2410 result_table(77) := result_record.value77;
2411 result_table(78) := result_record.value78;
2412 result_table(79) := result_record.value79;
2413 result_table(80) := result_record.value80;
2414 result_table(81) := result_record.value81;
2415 result_table(82) := result_record.value82;
2416 result_table(83) := result_record.value83;
2417 result_table(84) := result_record.value84;
2418 result_table(85) := result_record.value85;
2419 result_table(86) := result_record.value86;
2420 result_table(87) := result_record.value87;
2421 result_table(88) := result_record.value88;
2422 result_table(89) := result_record.value89;
2423 result_table(90) := result_record.value90;
2424 result_table(91) := result_record.value91;
2425 result_table(92) := result_record.value92;
2426 result_table(93) := result_record.value93;
2427 result_table(94) := result_record.value94;
2428 result_table(95) := result_record.value95;
2429 result_table(96) := result_record.value96;
2430 result_table(97) := result_record.value97;
2431 result_table(98) := result_record.value98;
2432 result_table(99) := result_record.value99;
2433 result_table(100) := result_record.value100;
2434
2435 end transfer_Row_To_Column;
2436
2437 ---------------------------------------------------------------------
2438 -- DESCRIPTION:
2439 -- DynamicButton generates JavaScript and HTML code that renders
2440 -- a button image using an HTML table, multiple smaller images, and
2441 -- text. Button text is passed as an argument to the procedure
2442 -- and an illusion is achieved of the text being superimposed on the
2443 -- button.
2444 --
2445 -- PRECONDITION:
2446 -- Should be used by client browsers supporting cell background
2447 -- colors (eg <TD BGCOLOR="#CCCCCC">), such as Netscape 3.0 and
2448 -- MSIE 3.0, but not required. Center of button will be same as body
2449 -- background if BGCOLOR not supported. Recommend testing browser
2450 -- version before calling.
2451 --
2452 -- USAGE:
2453 -- Called with the following arguments.
2454 -- P_ButtonText - Button label text. (Optional)
2455 -- P_ImageFileName - Filename of the icon displayed with the text
2456 -- P_OnMouseOverText - Text appearing in status bar when mouse
2457 -- scrolls over HyperText links
2458 -- P_HyperTextCall - Call appearing in HREF tag
2459 -- P_LanguageCode - Language of client (get with getID)
2460 -- P_JavaScriptFlag - HTML tags are embedded in
2461 -- document.write and double quotes are
2462 -- replaced with backslash quote when TRUE,
2463 -- otherwise raw HTML is returned to client (FALSE).
2464 -- P_DisabledFlag - prints label in disabled color when TRUE
2465 --
2466 --
2467 -- EXAMPLE:
2468 -- DynamicButton('First',
2469 -- 'FNDBFRST.gif',
2470 -- 'First Receipt',
2471 -- 'javascript:parent.FirstReceipt(this.form)',
2472 -- v_lang,
2473 -- TRUE);
2474 --
2475 ---------------------------------------------------------------------
2476 PROCEDURE DynamicButton(P_ButtonText varchar2,
2477 P_ImageFileName varchar2,
2478 P_OnMouseOverText varchar2,
2479 P_HyperTextCall varchar2,
2480 P_LanguageCode varchar2,
2481 P_JavaScriptFlag boolean,
2482 P_DisabledFlag boolean) IS
2483
2484 l_ImagePath varchar2(240) := '/OA_MEDIA/';
2485 l_DisabledColor varchar2(7) := '#999999';
2486 c_browser varchar2(400) := owa_util.get_cgi_env('HTTP_USER_AGENT');
2487 l_ImageFileName varchar2(240);
2488
2489 PROCEDURE spool (P_text varchar2) IS
2490 BEGIN
2491 --
2492 -- Embed HTML tags within document.write and replace quotes with
2493 -- backslash quotes if P_JavaScriptFlag = TRUE
2494 --
2495 if (P_JavaScriptFlag) then
2496 htp.p('document.write("'||replace(P_text,'"','\"')||'");');
2497 else
2498 htp.p(P_text);
2499 end if;
2500 END;
2501
2502 BEGIN
2503 if instr(P_ImageFileName,'.gif') > 0
2504 then
2505 l_ImageFileName := P_ImageFileName;
2506 else
2507 l_ImageFileName := P_ImageFileName||'.gif';
2508 end if;
2509
2510 spool('<table border=0 cellpadding=0 cellspacing=0 valign=TOP align=left>');
2511
2512 if (P_ButtonText is not null) then
2513 spool('<tr><td height=28 width=29 rowspan=5>');
2514 else
2515 spool('<tr><td height=28 width=5>');
2516 end if;
2517
2518 spool('<!--href is button action, and gif is button graphic-->');
2519 if P_HyperTextCall is not null then
2520 spool('<a href="'||P_HyperTextCall||'"');
2521 spool('onMouseOver="window.status='''||
2522 replace_onMouseOver_quotes(P_OnMouseOverText)||''' ; return true">');
2523 spool('<img src="'||l_ImagePath||l_ImageFileName||
2524 '" align=CENTER height=28 width=29 border=0 alt="'||
2525 replace_alt_quotes(P_OnMouseOverText)||'"></a></td>');
2526 else
2527 spool('<img src="'||l_ImagePath||l_ImageFileName||
2528 '" align=CENTER height=28 width=29 border=0 alt="'||
2529 replace_alt_quotes(P_OnMouseOverText)||'"></td>');
2530 end if;
2531
2532 if (P_ButtonText is not null) then
2533 spool('<td height=1 bgcolor=#CCCCCC><img height=1 width=1 src="'||l_ImagePath||'FNDDBPXC.gif" alt="'||replace_alt_quotes(P_OnMouseOverText)||'"></td>');
2534 end if;
2535
2536 if (P_ButtonText is not null) then
2537 spool('<td height=28 width=29 rowspan=5>');
2538 else
2539 spool('<td height=28 width=5>');
2540 end if;
2541
2542 if P_HyperTextCall is not null then
2543 spool('<a href="'||P_HyperTextCall||'" ');
2544 spool('onMouseOver="window.status='''||
2545 replace_onMouseOver_quotes(P_OnMouseOverText)||''' ; return true">');
2546 spool('<img src="'||l_ImagePath||
2547 'FNDDBEND.gif" border=0 height=28 width=7 align=CENTER alt="'||replace_alt_quotes(P_OnMouseOverText)||
2548 '" ></a></td></tr>');
2549 else
2550 spool('<img src="'||l_ImagePath|| 'FNDDBEND.gif" border=0 height=28 width=7 align=CENTER alt="'||replace_alt_quotes(P_OnMouseOverText)||
2551 '" ></td></tr>');
2552 end if;
2553
2554 if (P_ButtonText is not null) then
2555 spool('<tr><td height=1 bgcolor=#FFFFFF>');
2556 spool('<img width=1 height=1 src="'|| l_ImagePath||
2557 'FNDDBPXW.gif" alt="'||
2558 replace_alt_quotes(P_OnMouseOverText)||'"></td></tr>');
2559 spool('<tr align=CENTER valign=MIDDLE><td height=24 valign=MIDDLE bgcolor=#cccccc nowrap>');
2560 spool('<!--href is button action, and cell text appears on the button-->');
2561 if P_HyperTextCall is not null then
2562 spool('<a href="'||P_HyperTextCall||'" valign=MIDDLE ');
2563 spool('onMouseOver="window.status='''||
2564 replace_onMouseOver_quotes(P_OnMouseOverText)||''' ; return true">');
2565 end if;
2566
2567 if (P_DisabledFlag) then
2568 spool('<font color=' || l_DisabledColor || '>'||P_ButtonText||
2569 '</font>');
2570 if P_HyperTextCall is not null then
2571 spool('</a></td></tr>');
2572 else
2573 spool('</td></tr>');
2574 end if;
2575 else
2576 spool('<font color=#000000>'||P_ButtonText||
2577 '</font>');
2578 if P_HyperTextCall is not null then
2579 spool('</a></td></tr>');
2580 else
2581 spool('</td></tr>');
2582 end if;
2583 end if;
2584 spool('<tr><td height=1 bgcolor=#999999>');
2585 spool('<img width=1 height=1 src="'||
2586 l_ImagePath||'FNDDBPX9.gif" alt="'||
2587 replace_alt_quotes(P_OnMouseOverText)||'"></td></tr>');
2588 spool('<tr><td height=1 bgcolor=#000000>');
2589 spool('<img width=1 height=1 src="'||
2590 l_ImagePath||'FNDDBPXB.gif" alt="'||
2591 replace_alt_quotes(P_OnMouseOverText)||'"></td></tr>');
2592 end if;
2593
2594 spool('</table>');
2595 END;
2596
2597 PROCEDURE paintDynamicButton(P_ButtonText varchar2,
2598 P_ImageFileName varchar2,
2599 P_OnMouseOverText varchar2,
2600 P_HyperTextCall varchar2,
2601 P_LanguageCode varchar2,
2602 P_DisabledFlag boolean) is
2603 l_DisabledFlag varchar2(30);
2604 l_ImageFileName varchar2(240);
2605
2606 begin
2607 if instr(P_ImageFileName,'.gif') > 0
2608 then
2609 l_ImageFileName := P_ImageFileName;
2610 else
2611 l_ImageFileName := P_ImageFileName||'.gif';
2612 end if;
2613
2614 if P_DisabledFlag then
2615 l_DisabledFlag := 'TRUE';
2616 else
2617 l_DisabledFlag := 'FALSE';
2618 end if;
2619
2620 htp.p('<SCRIPT LANGUAGE="JavaScript">');
2621 htp.p('<!-- Hide from old browsers');
2622 -- dynamicButton(p_text,p_alt,p_over,p_language,p_image,p_url,p_flag);
2623 htp.p('dynamicButton("'||replace_alt_quotes(P_ButtonText)||'","'||replace_alt_quotes(P_OnMouseOverText)||'","'
2624 ||replace(replace_onMouseOver_quotes(P_OnMouseOverText),'\''','\\''')||'","'||P_LanguageCode||'","'||l_ImageFileName||'","'||P_HyperTextCall||'","'||l_DisabledFlag||'")');
2625 htp.p('// -->');
2626 htp.p('</SCRIPT>');
2627
2628 end;
2629
2630 PROCEDURE DynamicButtonIn(P_ButtonText varchar2,
2631 P_ImageFileName varchar2,
2632 P_OnMouseOverText varchar2,
2633 P_HyperTextCall varchar2,
2634 P_LanguageCode varchar2,
2635 P_JavaScriptFlag boolean,
2636 P_DisabledFlag boolean) IS
2637
2638 l_ImagePath varchar2(240) := '/OA_MEDIA/';
2639 l_DisabledColor varchar2(7) := '#999999';
2640 l_ImageFileName varchar2(240);
2641
2642 PROCEDURE spool (P_text varchar2) IS
2643 BEGIN
2644 --
2645 -- Embed HTML tags within document.write and replace quotes with
2646 -- backslash quotes if P_JavaScriptFlag = TRUE
2647
2648 --
2649 if (P_JavaScriptFlag) then
2650 htp.p('document.write("'||replace(P_text,'"','\"')||'");');
2651 else
2652 htp.p(P_text);
2653 end if;
2654 END;
2655
2656 BEGIN
2657 if instr(P_ImageFileName,'.gif') > 0
2658 then
2659 l_ImageFileName := P_ImageFileName;
2660 else
2661 l_ImageFileName := P_ImageFileName||'.gif';
2662 end if;
2663
2664 spool('<table border=0 cellpadding=0 cellspacing=0 align=left>');
2665
2666 if (P_ButtonText is not null) then
2667 spool('<tr><td height=28 width=29 rowspan=5>');
2668 else
2669 spool('<tr><td height=28 width=5>');
2670 end if;
2671
2672 spool('<!--href is button action, and gif is button graphic-->');
2673 if P_HyperTextCall is not null then
2674 spool('<a href="'||P_HyperTextCall||'" ');
2675 spool('onMouseOver="window.status='||'&'||'quot;'||
2676 P_OnMouseOverText||'&'||'quot; ; return true">');
2677 spool('<img src="'||l_ImagePath||l_ImageFileName||
2678 '" height=28 width=29 border=0></a></td>');
2679 else
2680 spool('<img src="'||l_ImagePath||l_ImageFileName||
2681 '" height=28 width=29 border=0></td>');
2682 end if;
2683
2684
2685 if (P_ButtonText is not null) then
2686 spool('<td height=1 bgcolor=#000000><img height=1 width=1 src="'||
2687 l_ImagePath||'FNDDBPXB.gif"></td>');
2688 end if;
2689
2690 if (P_ButtonText is not null) then
2691 spool('<td height=28 width=29 rowspan=5>');
2692 else
2693 spool('<td height=28 width=5>');
2694 end if;
2695
2696 if P_HyperTextCall is not null then
2697 spool('<a href="'||P_HyperTextCall||'" ');
2698 spool('onMouseOver="window.status='||'&'||'quot;'||
2699 P_OnMouseOverText||'&'||'quot; ; return true">');
2700 spool('<img src="'||l_ImagePath||
2701 'FNDDBENI.gif" border=0 height=28 width=7></a></td></tr>');
2702 else
2703 spool('<img src="'||l_ImagePath||
2704 'FNDDBENI.gif" border=0 height=28 width=7></td></tr>');
2705 end if;
2706
2707 if (P_ButtonText is not null) then
2708 spool('<tr><td height=1 bgcolor=#999999>');
2709 spool('<img width=1 height=1 src="'||
2710 l_ImagePath||'FNDDBPX9.gif"></td></tr>');
2711 spool('<tr><td height=24 align=center valign=center'||
2712 ' bgcolor=#cccccc nowrap>');
2713 spool('<!--href is button action, and cell text appears on the button-->');
2714 if P_HyperTextCall is not null then
2715 spool('<a href="'||P_HyperTextCall||'" ');
2716 spool('onMouseOver="window.status='||'&'||'quot;'||
2717 P_OnMouseOverText||'&'||'quot; ; return true">');
2718 end if;
2719
2720 if (P_DisabledFlag) then
2721 spool('<font color=' || l_DisabledColor || '>'||P_ButtonText||
2722 '</font>');
2723 if P_HyperTextCall is not null then
2724 spool('</a></td></tr>');
2725 else
2726 spool('</td></tr>');
2727 end if;
2728 else
2729 spool('<font color=#000000>'||P_ButtonText||
2730 '</font>');
2731 if P_HyperTextCall is not null then
2732 spool('</a></td></tr>');
2733 else
2734 spool('</td></tr>');
2735 end if;
2736 end if;
2737 spool('<tr><td height=1 bgcolor=#FFFFFF>');
2738 spool('<img width=1 height=1 src="'||
2739 l_ImagePath||'FNDDBPXW.gif"></td></tr>');
2740 spool('<tr><td height=1 bgcolor=#E1E1E1>');
2741 spool('<img width=1 height=1 src="'||
2742 l_ImagePath||'FNDDBPXE.gif"></td></tr>');
2743 end if;
2744 spool('</table>');
2745 END dynamicbuttonin;
2746
2747
2748
2749 function replace_jsdw_quotes(p_string in varchar2) return varchar2 is
2750
2751 temp_string varchar2(2000);
2752
2753 begin
2754
2755 -- replace single quotes
2756 temp_string := replace(p_string,'''','\''');
2757
2758 -- replace double quotes
2759 -- temp_string := replace(temp_string,'"','"');
2760 temp_string := replace(temp_string,'"','\"');
2761
2762 return temp_string;
2763
2764 end replace_jsdw_quotes;
2765
2766
2767
2768 function replace_quotes(p_string in varchar2) return varchar2 is
2769
2770 temp_string varchar2(2000);
2771
2772 begin
2773
2774 -- replace single quotes
2775 temp_string := replace(p_string,'''','\''');
2776
2777 -- replace double quotes
2778 -- temp_string := replace(temp_string,'"','"');
2779 temp_string := replace(temp_string,'"','\"');
2780
2781 -- check for double escapes
2782 temp_string := replace(temp_string,'\\','\');
2783
2784 return temp_string;
2785
2786 end replace_quotes;
2787
2788
2789
2790
2791 function replace_onMouseOver_quotes(p_string in varchar2) return varchar2 is
2792
2793 temp_string varchar2(2000);
2794 c_browser varchar2(400) := owa_util.get_cgi_env('HTTP_USER_AGENT');
2795
2796 begin
2797
2798 -- replace single quotes
2799 temp_string := replace(p_string,'''','\''');
2800
2801 /*
2802 -- replace double quotes
2803 if (instr(c_browser, 'MSIE') <> 0) then
2804 temp_string := replace(temp_string,'"','\''');
2805 else
2806 temp_string := replace(temp_string,'"','"');
2807 end if;
2808 */
2809 temp_string := replace(temp_string,'"','"');
2810
2811 -- check for double escapes
2812 temp_string := replace(temp_string,'\\','\');
2813
2814 return temp_string;
2815
2816 end replace_onMouseOver_quotes;
2817
2818 function replace_alt_quotes(p_string in varchar2) return varchar2 is
2819
2820 temp_string varchar2(2000);
2821
2822 begin
2823
2824 -- replace double quotes
2825 temp_string := replace(p_string,'"','"');
2826
2827 -- check for double escapes
2828 temp_string := replace(temp_string,'\\','\');
2829
2830 return temp_string;
2831
2832 end replace_alt_quotes;
2833
2834 end icx_util;