[Home] [Help]
PACKAGE BODY: APPS.ICX_ADMIN_UTILS
Source
1 package body icx_admin_utils as
2 -- $Header: ICXADUTB.pls 120.1 2005/10/07 13:17:08 gjimenez noship $
3
4 procedure displayList(a_1 in varchar2 default null,
5 c_1 in varchar2 default null,
6 i_1 in varchar2 default null,
7 a_2 in varchar2 default null,
8 c_2 in varchar2 default null,
9 i_2 in varchar2 default null,
10 a_3 in varchar2 default null,
11 c_3 in varchar2 default null,
12 i_3 in varchar2 default null,
13 a_4 in varchar2 default null,
14 c_4 in varchar2 default null,
15 i_4 in varchar2 default null,
16 a_5 in varchar2 default null,
17 c_5 in varchar2 default null,
18 i_5 in varchar2 default null,
19 p_hidden in varchar2 default null,
20 p_start_row in number default 1,
21 p_end_row in number default null,
22 p_where in varchar2 default null) is
23
24 l_hidden varchar2(2000);
25 l_parameters icx_on_utilities.v240_table;
26 l_key_value_ids icx_on_utilities.number_table;
27 l_key_attributes icx_on_utilities.v30_table;
28 l_link_value_id number;
29 l_link_attribute varchar2(30);
30 l_function_code varchar2(30);
31 l_region_application_id number;
32 l_region_code varchar2(30);
33 l_find_proc varchar2(240);
34 l_list_proc varchar2(240);
35 l_new_proc varchar2(240);
36 l_update_proc varchar2(240);
37 l_delete_proc varchar2(240);
38 l_help_file varchar2(240);
39
40 l_web_user_id number;
41 l_language_code varchar2(30);
42 l_new_prompt varchar2(50);
43 l_help_url varchar2(2000);
44 l_where_clause varchar2(2000);
45 l_total_rows number;
46 l_end_row number;
47 l_query_size number;
48 l_encrypted_where number;
49
50 l_multirow_color varchar2(30);
51 l_resp_id number;
52 l_user_id number;
53 l_values_table icx_util.char240_table;
54
55 l_count number;
56 l_X varchar2(240);
57 l_procedure_call varchar2(2000);
58 l_call integer;
59 l_dummy integer;
60
61 l_err_num number;
62 l_message varchar2(2000);
63 l_err_mesg varchar2(240);
64
65 begin
66
67 l_hidden := icx_call.decrypt2(p_hidden);
68 icx_on_utilities.unpack_parameters(l_hidden,l_parameters);
69
70 for i in l_parameters.count..21 loop
71 l_parameters(i) := '';
72 end loop;
73
74 /*
75 for i in 1..l_parameters.count loop
76 htp.p(i||' = '||l_parameters(i));htp.nl;
77 end loop;
78 */
79
80 l_function_code := l_parameters(1);
81 l_region_application_id := l_parameters(2);
82 l_region_code := l_parameters(3);
83 l_find_proc := l_parameters(4);
84 l_new_proc := l_parameters(5);
85 l_update_proc := l_parameters(6);
86 l_list_proc := 'icx_admin_utils.displayList';
87 l_delete_proc := l_parameters(7);
88 l_help_file := l_parameters(8);
89 l_link_attribute := l_parameters(9);
90 l_key_attributes(1) := l_parameters(10);
91 l_key_attributes(2) := l_parameters(11);
92 l_key_attributes(3) := l_parameters(12);
93 l_key_attributes(4) := l_parameters(13);
94 l_key_attributes(5) := l_parameters(14);
95 l_key_attributes(6) := l_parameters(15);
96 l_key_attributes(7) := l_parameters(16);
97 l_key_attributes(8) := l_parameters(17);
98 l_key_attributes(9) := l_parameters(18);
99 l_key_attributes(10) := l_parameters(19);
100
101 if icx_sec.validateSession(l_function_code)
102 then
103
104 l_web_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
105 l_language_code := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
106 l_new_prompt := icx_util.getPrompt(601,'ICX_REGP_DISPLAY',178,'ICX_NEW');
107 l_help_url := '/OA_HTML/'||l_language_code||'/'||l_help_file;
108
109 if p_where is not null
110 then
111 l_where_clause := icx_call.decrypt2(p_where);
112 else
113 l_where_clause := icx_on_utilities.whereSegment(a_1,c_1,i_1,a_2,c_2,i_2,a_3,c_3,i_3,a_4,c_4,i_4,a_5,c_5,i_5);
114 end if;
115
116 l_encrypted_where := icx_call.encrypt2(l_where_clause);
117
118 l_resp_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
119 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
120
121 select QUERY_SET
122 into l_query_size
123 from ICX_PARAMETERS;
124
125 if p_end_row is null then
126 l_end_row := l_query_size;
127 else
128 l_end_row := p_end_row;
129 end if;
130
131 ak_query_pkg.exec_query(
132 p_parent_region_appl_id => l_region_application_id,
133 p_parent_region_code => l_region_code,
134 p_where_clause => l_where_clause,
135 p_responsibility_id => l_resp_id,
136 p_user_id => l_user_id,
137 p_return_parents => 'T',
138 p_return_children => 'F',
139 p_range_low => p_start_row,
140 p_range_high => l_end_row);
141
142
143 -- icx_on_utilities2.printPLSQLtables;
144
145 /* get number of total rows returned by lov to be used to determine if
146 we need to display the next/previous buttons */
147
148 l_total_rows := ak_query_pkg.g_regions_table(0).total_result_count;
149
150 if l_total_rows = 0 then
151
152 htp.htmlOpen;
153 htp.headOpen;
154 icx_util.copyright;
155 js.scriptOpen;
156 icx_admin_sig.help_win_script(l_help_url, l_language_code);
157 js.scriptClose;
158 htp.title(ak_query_pkg.g_regions_table(0).name);
159 htp.headClose;
160
161 icx_admin_sig.toolbar(language_code => l_language_code,
162 disp_find => l_find_proc);
163
164 fnd_message.set_name('ICX','ICX_NO_RECORDS_FOUND');
165 fnd_message.set_token('NAME_OF_REGION_TOKEN',ak_query_pkg.g_regions_table(0).name);
166 htp.br;
167 htp.tableOpen('BORDER=0');
168 htp.tableRowOpen;
169 htp.tableData(cvalue => '<B><FONT size=+1>'||fnd_message.get||'</FONT></B>',cattributes => 'VALIGN="MIDDLE"');
170
171 htp.p('<TD>');
172 icx_util.DynamicButton(P_ButtonText => l_new_prompt,
173 P_ImageFileName => 'FNDBNEW',
174 P_OnMouseOverText => l_new_prompt,
175 P_HyperTextCall => l_new_proc,
176 P_LanguageCode => l_language_code,
177 P_JavaScriptFlag => FALSE);
178 htp.p('</TD>');
179 htp.tableClose;
180 htp.br;
181 icx_admin_sig.footer;
182
183 elsif l_total_rows = 1 then
184
185 l_count := 0;
186 while l_key_attributes(l_count+1) is not null loop
187 for i in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
188 if ak_query_pkg.g_items_table(i).attribute_code = l_key_attributes(l_count+1)
189 then
190 l_key_value_ids(l_count+1) := ak_query_pkg.g_items_table(i).value_id;
191 end if;
192 end loop;
193 l_count := l_count + 1;
194 end loop;
195 icx_util.transfer_Row_To_Column(ak_query_pkg.g_results_table(0),l_values_table);
196
197 for i in 1..l_count loop
198 l_X := l_X||l_values_table(l_key_value_ids(i))||'*';
199 end loop;
200 l_X := l_X||'*]';
201
202 l_procedure_call := l_update_proc||'('||icx_call.encrypt2(l_X)||')';
203
204 l_call := dbms_sql.open_cursor;
205 dbms_sql.parse(l_call,'begin '||l_procedure_call||'; end;',dbms_sql.native);
206 l_dummy := dbms_sql.execute(l_call);
207 dbms_sql.close_cursor(l_call);
208
209 else
210 htp.htmlOpen;
211 htp.headOpen;
212 icx_util.copyright;
213 js.scriptOpen;
214 icx_admin_sig.help_win_script(l_help_url, l_language_code);
215
216 fnd_message.set_name('ICX', 'ICX_DELETE');
217 htp.p('function delete_function(delete_name, X) {
218 if (confirm("'||icx_util.replace_quotes(fnd_message.get)||' "+delete_name+"?")) {
219 parent.location="'||l_delete_proc||'?X=" + X
220 }
221 }');
222
223 js.scriptClose;
224 htp.title(ak_query_pkg.g_regions_table(0).name);
225 htp.headClose;
226
227 icx_admin_sig.toolbar(language_code => l_language_code,
228 disp_find => l_find_proc);
229
230 if l_end_row > l_total_rows then
231 l_end_row := l_total_rows;
232 end if;
233
234 htp.formOpen('');
235
236 htp.tableOpen('BORDER=0');
237 htp.tableRowOpen;
238 htp.tableData(cvalue => '<B><FONT size=+2>'||ak_query_pkg.g_regions_table(0).name||'</FONT></B>',cattributes => 'VALIGN="MIDDLE"');
239
240 htp.p('<TD>');
241 icx_util.DynamicButton(P_ButtonText => l_new_prompt,
242 P_ImageFileName => 'FNDBNEW',
243 P_OnMouseOverText => l_new_prompt,
244 P_HyperTextCall => l_new_proc,
245 P_LanguageCode => l_language_code,
246 P_JavaScriptFlag => FALSE);
247 htp.p('</TD>');
248 htp.tableRowClose;
249 htp.tableClose;
250 htp.br;
251
252 icx_on_utilities2.displaySetIcons(l_language_code,l_list_proc,p_start_row,l_end_row,l_encrypted_where,l_query_size,l_total_rows,TRUE,'',p_hidden);
253
254 l_count := 0;
255 while l_key_attributes(l_count+1) is not null loop
256 for i in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
257 if ak_query_pkg.g_items_table(i).attribute_code = l_key_attributes(l_count+1)
258 then
259 l_key_value_ids(l_count+1) := ak_query_pkg.g_items_table(i).value_id;
260 end if;
261 end loop;
262 l_count := l_count + 1;
263 end loop;
264
265 htp.tableOpen('BORDER=4');
266 htp.p('<TR BGColor="#'||icx_util.get_color('TABLE_HEADER')||'">');
267 for i in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
268 if ak_query_pkg.g_items_table(i).secured_column = 'F'
269 and ak_query_pkg.g_items_table(i).node_display_flag = 'Y'
270 then
271 htp.tableData(ak_query_pkg.g_items_table(i).attribute_label_long);
272 if l_link_attribute = ak_query_pkg.g_items_table(i).attribute_code
273 then
274 l_link_value_id := ak_query_pkg.g_items_table(i).value_id;
275 end if;
276 end if;
277 end loop;
278 htp.tableData('');
279 htp.tableRowClose;
280
281 l_multirow_color := icx_util.get_color('TABLE_DATA_MULTIROW');
282
283 for r in 0..ak_query_pkg.g_results_table.COUNT-1 loop
284
285 icx_util.transfer_Row_To_Column(ak_query_pkg.g_results_table(r),l_values_table);
286
287 l_X := '';
288 for i in 1..l_count loop
289 l_X := l_X||l_values_table(l_key_value_ids(i))||'*';
290 end loop;
291
292 htp.tableRowOpen;
293 for i in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
294
295 if ak_query_pkg.g_items_table(i).secured_column = 'F'
296 and ak_query_pkg.g_items_table(i).node_display_flag = 'Y'
297 then
298 if ak_query_pkg.g_items_table(i).attribute_code = l_link_attribute
299 then
300 htp.tableData(htf.anchor(l_update_proc||'?X='||icx_call.encrypt2(l_X||'*]'),l_values_table(l_link_value_id),'','onMouseOver="return true"'));
301 elsif ak_query_pkg.g_items_table(i).value_id is null
302 then
303 htp.tableData('');
304 elsif ak_query_pkg.g_items_table(i).item_style = 'CHECKBOX'
305 and (l_values_table(ak_query_pkg.g_items_table(i).value_id) = 'T'
306 or l_values_table(ak_query_pkg.g_items_table(i).value_id) = 'Y')
307 then
308 htp.tableData('<img src="/OA_MEDIA/'||l_language_code||'/FNDICHEK.gif" ALT="T" border=0 width=17 height=16>','CENTER');
309 elsif ak_query_pkg.g_items_table(i).item_style = 'CHECKBOX'
310 then
311 htp.tableData('');
312 else
313 htp.tableData(l_values_table(ak_query_pkg.g_items_table(i).value_id));
314 end if;
315 end if;
316
317 end loop; -- items
318 l_X := 'DISPLAY'||'*'||l_X||p_hidden||'*'||p_start_row||'*'||l_end_row||'*'||l_encrypted_where||'**]';
319 htp.tableData(htf.anchor('javascript:delete_function('''||icx_util.replace_onMouseOver_quotes(l_values_table(l_link_value_id))||''','''||icx_call.encrypt2(l_X)||''')',
320 htf.img('/OA_MEDIA/'||l_language_code||'/FNDIDELR.gif','CENTER','','','border=no width=17 height=16'),'','onMouseOver="return true"'));
321 htp.tableRowClose;
322 end loop; -- Results
323
324 htp.tableClose;
325
326 icx_on_utilities2.displaySetIcons(l_language_code,l_list_proc,p_start_row,l_end_row,l_encrypted_where,l_query_size,l_total_rows,TRUE,'',p_hidden);
327
328 htp.formClose;
329 icx_admin_sig.footer;
330
331 end if;
332
333 end if;
334
335 exception
336 when others then
337 l_err_num := SQLCODE;
338 l_message := SQLERRM;
339 select substr(l_message,12,512) into l_err_mesg from dual;
340
341 icx_util.add_error(l_err_mesg);
342 icx_admin_sig.error_screen(l_err_mesg);
343
344 end;
345
346 procedure LISTScript is
347 begin
348 htp.p('function add_to_right() {
349 document.LISTform.left_right_flag.value = "Y"
350 document.LISTform.submit();
351 }');
352 htp.p('function remove_to_left() {
353 document.LISTform.left_right_flag.value = "N"
354 document.LISTform.submit();
355 }');
356
357 end LISTScript;
358
359
360 procedure selectList( p_left_region_appl_id in number,
361 p_left_region_code in varchar2,
362 p_left_where in varchar2 default null,
363 p_right_region_appl_id in number,
364 p_right_region_code in varchar2,
365 p_right_where in varchar2 default null,
366 p_hidden_name in varchar2 default null,
367 p_hidden_value in varchar2 default null,
368 p_modify_url in varchar2,
369 p_primary_key_size in number) is
370
371 l_responsibility_id number := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
372 l_user_id number := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
373
374 left_regions_table ak_query_pkg.regions_table_type;
375 left_items_table ak_query_pkg.items_table_type;
376 left_results_table ak_query_pkg.results_table_type;
377
378 right_regions_table ak_query_pkg.regions_table_type;
379 right_items_table ak_query_pkg.items_table_type;
380 right_results_table ak_query_pkg.results_table_type;
381
382 begin
383
384 -- query the rows for both selection lists --
385 ak_query_pkg.exec_query(
386 p_parent_region_appl_id => p_left_region_appl_id,
387 p_parent_region_code => p_left_region_code,
388 p_where_clause => p_left_where,
389 p_responsibility_id => l_responsibility_id,
390 p_user_id => l_user_id,
391 p_return_parents => 'T',
392 p_return_children => 'F');
393
394 left_regions_table := ak_query_pkg.g_regions_table;
395 left_items_table := ak_query_pkg.g_items_table;
396 left_results_table := ak_query_pkg.g_results_table;
397
398 ak_query_pkg.exec_query(
399 p_parent_region_appl_id => p_right_region_appl_id,
400 p_parent_region_code => p_right_region_code,
401 p_where_clause => p_right_where,
402 p_responsibility_id => l_responsibility_id,
403 p_user_id => l_user_id,
404 p_return_parents => 'T',
405 p_return_children => 'F');
406
407 right_regions_table := ak_query_pkg.g_regions_table;
408 right_items_table := ak_query_pkg.g_items_table;
409 right_results_table := ak_query_pkg.g_results_table;
410
411 htp.formOpen(p_modify_url, 'POST', '','','NAME ="LISTform"');
412
413 -- customized hidden field --
414 htp.formHidden(p_hidden_name, p_hidden_value);
415
416 -- pass on the where_clause --
417 htp.formHidden('inherit_where', p_left_where);
418
419 -- dummy left_list, and right_list fields to avoid errors on submitting --
420 htp.formHidden('left_list', 'None');
421 htp.formHidden('right_list', 'None');
422
423 -- flag to indicate if the submit is an add or removal --
424 htp.formHidden('left_right_flag', '');
425
426 htp.tableOpen('BORDER=0','','','','cellpadding=8 cellspacing=0');
427 htp.tableRowOpen;
428 htp.p('<td align=center valign=top>');
429
430 htp.formSelectOpen('left_list','',10,'MULTIPLE');
431
432 if p_primary_key_size = 2
433 then
434 -- when primary key consists of 2 columns --
435 for i in 0..left_results_table.count-1 loop
436
437 htp.p('<option value = "'||left_results_table(i).value2||'-'||left_results_table(i).value3||'">'||left_results_table(i).value1);
438 end loop;
439 else
440 -- when primary key consistes of only 1 column --
441 for i in 0..left_results_table.count-1 loop
442
443 htp.p('<option value = "'||left_results_table(i).value2||'">'||left_results_table(i).value1);
444 end loop;
445 end if;
446
447
448 htp.formSelectClose;
449 htp.p('</td>');
450 htp.p('<td align=center><INPUT type="button" value="Add >>" onClick="add_to_right()">');
451 htp.br;
452
453 htp.br;
454 htp.p('<INPUT type="button" value="<< Remove" onClick="remove_to_left()"></td>');
455 htp.p('<td align=center valign=top>');
456 htp.formSelectOpen('right_list','',10,'MULTIPLE');
457
458
459 -- construct the list item value according to --
460 -- the primary key size --
461
462 if p_primary_key_size = 2
463 then
464 -- when primary key consists of 2 columns --
465 for i in 0..right_results_table.count-1 loop
466 htp.p('<option value = "'||right_results_table(i).value2||'-'||right_results_table(i).value3||'">'||right_results_table(i).value1);
467 end loop;
468 else
469 -- when primary key consistes of only 1 column --
470 for i in 0..right_results_table.count-1 loop
471
472 htp.p('<option value = "'||right_results_table(i).value2||'">'||right_results_table(i).value1);
473 end loop;
474 end if;
475
476 htp.formSelectClose;
477
478 htp.p('</td>');
479 htp.tableRowClose;
480 htp.tableClose;
481
482 htp.formClose;
483
484 end selectList;
485
486 end icx_admin_utils;