[Home] [Help]
PACKAGE BODY: APPS.ICX_ON_UTILITIES
Source
1 package body icx_on_utilities as
2 /* $Header: ICXONUB.pls 120.1 2005/10/07 13:45:07 gjimenez noship $ */
3
4 procedure findPage(p_flow_appl_id in number,
5 p_flow_code in varchar2,
6 p_page_appl_id in number,
7 p_page_code in varchar2,
8 p_region_appl_id in number,
9 p_region_code in varchar2,
10 p_goto_url in varchar2,
11 p_lines_now in number,
12 p_lines_url in varchar2,
13 p_lines_next in number,
14 p_hidden_name in varchar2,
15 p_hidden_value in varchar2,
16 p_help_url in varchar2,
17 p_new_url in varchar2) is
18
19 l_language_code varchar2(30);
20 l_page_title varchar2(240);
21 l_region_appl_id number;
22 l_region_code varchar2(30);
23 l_message varchar2(2000);
24
25 begin
26
27 l_language_code := icx_sec.getID(icx_sec.pv_language_code);
28
29 if p_page_code is not null
30 then
31 select NAME,PRIMARY_REGION_APPL_ID,PRIMARY_REGION_CODE
32 into l_page_title,l_region_appl_id,l_region_code
33 from AK_FLOW_PAGES_VL
34 where PAGE_CODE = p_page_code
35 and PAGE_APPLICATION_ID = p_page_appl_id
36 and FLOW_CODE = p_flow_code
37 and FLOW_APPLICATION_ID = p_flow_appl_id;
38 else
39
40 select NAME
41 into l_page_title
42 from AK_REGIONS_VL
43 where REGION_CODE = p_region_code
44 and REGION_APPLICATION_ID = p_region_appl_id;
45
46 l_region_appl_id := p_region_appl_id;
47 l_region_code := p_region_code;
48
49 end if;
50
51 htp.htmlOpen;
52 htp.headOpen;
53
54 icx_util.copyright;
55
56 htp.p('<SCRIPT LANGUAGE="JavaScript">');
57 htp.p('<!-- Hide from old browsers');
58
59 icx_admin_sig.help_win_script(p_help_url,l_language_code);
60
61 if p_lines_now is null or p_lines_now = 1
62 then
63 htp.p('document.cookie = "onquery=" + self.location.href + ";"');
64 end if;
65
66 htp.p('// -->');
67 htp.p('</SCRIPT>');
68
69 htp.title(l_page_title);
70 htp.headClose;
71
72 icx_admin_sig.toolbar(language_code => l_language_code);
73
74 findForm(p_flow_appl_id => p_flow_appl_id,
75 p_flow_code => p_flow_code,
76 p_page_appl_id => p_page_appl_id,
77 p_page_code => p_page_code,
78 p_region_appl_id => l_region_appl_id,
79 p_region_code => l_region_code,
80 p_goto_url => p_goto_url,
81 p_goto_target => '',
82 p_lines_now => p_lines_now,
83 p_lines_url => p_lines_url,
84 p_lines_target => '',
85 p_lines_next => p_lines_next,
86 p_hidden_name => p_hidden_name,
87 p_hidden_value => p_hidden_value,
88 p_help_url => p_help_url,
89 p_new_url => p_new_url);
90
91 icx_sig.footer;
92 htp.htmlClose;
93
94 end;
95
96 procedure findIcons( p_submit in varchar2,
97 p_clear in varchar2,
98 p_one in varchar2,
99 p_more in varchar2,
100 p_lines_next in number,
101 p_lines_now in number,
102 p_url in varchar2,
103 p_language_code in varchar2,
104 p_clear_button in varchar2,
105 p_advanced_button in varchar2) is
106
107 l_browser varchar2(400) := owa_util.get_cgi_env('HTTP_USER_AGENT');
108
109 begin
110
111 htp.p('<TD>');
112 icx_util.DynamicButton(P_ButtonText => p_submit,
113 P_ImageFileName => 'FNDBSBMT',
114 P_OnMouseOverText => p_submit,
115 P_HyperTextCall => 'javascript:submitFunction()',
116 P_LanguageCode => p_language_code,
117 P_JavaScriptFlag => FALSE);
118 htp.p('</TD>');
119 if (instr(l_browser,'MSIE 3')=0) and nvl(p_clear_button,'Y') = 'Y'
120 then
121 htp.p('<TD>');
122 icx_util.DynamicButton(P_ButtonText => p_clear,
123 P_ImageFileName => 'FNDBCLR',
124 P_OnMouseOverText => p_clear,
125 P_HyperTextCall => 'javascript:resetFunction()',
126 P_LanguageCode => p_language_code,
127 P_JavaScriptFlag => FALSE);
128 htp.p('</TD>');
129 end if;
130 if p_lines_next is not null and nvl(p_advanced_button,'Y') = 'Y'
131 then
132 htp.p('<TD WIDTH=50></TD>');
133 htp.p('<TD>');
134 if p_lines_next > p_lines_now
135 then
136 icx_util.DynamicButton(P_ButtonText => p_more,
137 P_ImageFileName => 'FNDBMORC',
138 P_OnMouseOverText => p_more,
139 P_HyperTextCall => p_url,
140 P_LanguageCode => p_language_code,
141 P_JavaScriptFlag => FALSE);
142 else
143 icx_util.DynamicButton(P_ButtonText => p_one,
144 P_ImageFileName => 'FNDBONEC',
145 P_OnMouseOverText => p_one,
146 P_HyperTextCall => 'javascript:history.back();',
147 P_LanguageCode => p_language_code,
148 P_JavaScriptFlag => FALSE);
149 end if;
150 htp.p('</TD>');
151 end if;
152
153 end;
154
155 procedure findForm(p_region_appl_id in number,
156 p_region_code in varchar2,
157 p_goto_url in varchar2,
158 p_goto_target in varchar2,
159 p_lines_now in number,
160 p_lines_url in varchar2,
161 p_lines_target in varchar2,
162 p_lines_next in number,
163 p_hidden_name in varchar2,
164 p_hidden_value in varchar2,
165 p_help_url in varchar2,
166 p_new_url in varchar2,
167 p_LOV_mode in varchar2,
168 p_default_title in varchar2,
169 p_flow_appl_id in number,
170 p_flow_code in varchar2,
171 p_page_appl_id in number,
172 p_page_code in varchar2,
173 p_clear_button in varchar2,
174 p_advanced_button in varchar2) is
175
176 l_language_code varchar2(30) := icx_sec.getID(icx_sec.pv_language_code);
177 l_responsibility_id number := icx_sec.getID(icx_sec.pv_responsibility_id);
178
179 l_message varchar2(240);
180 l_page_title varchar2(80);
181 l_page_description varchar2(2000);
182 l_region_title varchar2(80);
183 l_region_description varchar2(2000);
184 l_icx_custom_call varchar2(80); --1570530 mputman
185
186 c_title varchar2(80);
187 c_prompts icx_util.g_prompts_table;
188 l_lookup_codes icx_util.g_lookup_code_table;
189 l_lookup_meanings icx_util.g_lookup_meaning_table;
190
191 c_count number;
192 l_region_code varchar2(30);
193 l_data_type varchar2(1);
194 l_data_type1 varchar2(1);
195 l_column_name varchar2(30);
196 l_flex_definition icx_on_utilities.v80_table;
197 l_segment_column varchar2(30);
198 l_context_column varchar2(40);
199 c_attributes v2000_table;
200 c_condition v2000_table;
201 c_url varchar2(2000);
202 c_buttons varchar2(2000);
203
204 cursor FindAttributes is
205 select d.COLUMN_NAME,b.DATA_TYPE,a.ATTRIBUTE_LABEL_LONG,e.ICX_CUSTOM_CALL
206 from AK_ATTRIBUTES b,
207 AK_REGIONS c,
208 AK_OBJECT_ATTRIBUTES d,
209 AK_REGION_ITEMS e,
210 AK_REGION_ITEMS_VL a
211 where a.REGION_APPLICATION_ID = p_region_appl_id
212 and a.REGION_CODE = l_region_code
213 and a.NODE_QUERY_FLAG = 'Y'
214 and a.REGION_APPLICATION_ID = c.REGION_APPLICATION_ID
215 and a.REGION_CODE = c.REGION_CODE
216 and c.DATABASE_OBJECT_NAME = d.DATABASE_OBJECT_NAME
217 and a.ATTRIBUTE_APPLICATION_ID = d.ATTRIBUTE_APPLICATION_ID
218 and a.ATTRIBUTE_CODE = d.ATTRIBUTE_CODE
219 and a.ATTRIBUTE_APPLICATION_ID = b.ATTRIBUTE_APPLICATION_ID
220 and a.ATTRIBUTE_CODE = b.ATTRIBUTE_CODE
221 and a.REGION_APPLICATION_ID = e.REGION_APPLICATION_ID
222 and a.REGION_CODE = e.REGION_CODE
223 and a.ATTRIBUTE_APPLICATION_ID = e.ATTRIBUTE_APPLICATION_ID
224 and a.ATTRIBUTE_CODE = e.ATTRIBUTE_CODE
225 and not exists (select 'X'
226 from AK_EXCLUDED_ITEMS
227 where RESPONSIBILITY_ID = l_responsibility_id
228 and ATTRIBUTE_CODE = a.ATTRIBUTE_CODE
229 and ATTRIBUTE_APPLICATION_ID = a.ATTRIBUTE_APPLICATION_ID)
230 order by a.DISPLAY_SEQUENCE;
231
232 begin
233
234 /* Get condition title, prompts and conditions */
235
236 icx_util.getPrompts(601,'ICX_WEB_ON_QUERY',c_title,c_prompts);
237
238 if p_lines_now > 1
239 and p_region_code = 'ICX_WEBSTORE_SEARCH_ITEMS_R'
240 then
241 l_region_code := 'ICX_WEBSTORE_ITEMS';
242 elsif p_lines_now > 1
243 and p_region_code = 'ICX_PO_SUPPL_SEARCH_ITEMS_R'
244 then
245 l_region_code := 'ICX_PO_SUPPL_CATALOG_ITEMS_R';
246 else
247 l_region_code := p_region_code;
248 end if;
249
250 if p_lines_url is null
251 then
252 c_url := 'OracleON.Find?X='||icx_call.encrypt2(p_region_appl_id||'*'||l_region_code||'*'||p_goto_url||'*'||p_lines_next||'*'||p_lines_url||'*'||p_lines_now
253 ||'*'||p_hidden_name||'*'||p_hidden_value||'*'||p_help_url||'*'||p_new_url||'*'||p_LOV_mode||'*'||p_default_title
254 ||'*'||p_flow_appl_id||'*'||p_flow_code||'*'||p_page_appl_id||'*'||p_page_code||'**]');
255 else
256 c_url := p_lines_url||icx_call.encrypt2(p_region_appl_id||'*'||l_region_code||'*'||p_goto_url||'*'||p_goto_target||'*'||p_lines_next||'*'||p_lines_url||'*'||p_lines_target||'*'||p_lines_now
257 ||'*'||p_hidden_name||'*'||p_hidden_value||'*'||p_help_url||'*'||p_new_url||'*'||p_LOV_mode||'*'||p_default_title
258 ||'*'||p_flow_appl_id||'*'||p_flow_code||'*'||p_page_appl_id||'*'||p_page_code||'**] NAME="'||p_lines_target||'"');
259 end if;
260
261 -- Create description query for Reqs and Store Items search
262
263 if p_lines_now = 1
264 and l_region_code = 'ICX_WEBSTORE_SEARCH_ITEMS_R'
265 then
266 l_context_column := 'XICX_WEBSTORE_ITEM_DESC';
267 elsif p_lines_now = 1
268 and l_region_code = 'ICX_PO_SUPPL_SEARCH_ITEMS_R'
269 then
270 l_context_column := 'XICX_WEBREQS_ITEM_DESC';
271 else
272
273 -- Create queryable attribute select list for standard Flows
274
275 icx_util.getLookups('ICX_CONDITIONS',l_lookup_codes,l_lookup_meanings);
276
277 c_count := 0;
278 c_attributes(0) := htf.formSelectOption(' ');
279 c_attributes(1) := '';
280 for i in 0..1 loop
281 for f in FindAttributes loop
282 c_count := c_count + 1;
283 if f.DATA_TYPE = 'DATETIME'
284 then
285 l_data_type := 'T';
286 else
287 l_data_type := substr(f.DATA_TYPE,1,1);
288 end if;
289 if c_count = 1
290 then
291 l_data_type1 := l_data_type;
292 end if;
293 if substr(nvl(f.icx_custom_call,'XXXX'),1,4) = 'FLEX'
294 then
295 icx_on_utilities.unpack_parameters(f.icx_custom_call,l_flex_definition);
296 if fnd_flex_apis.get_segment_column
297 (x_application_id => l_flex_definition(2),
298 x_id_flex_code => l_flex_definition(3),
299 x_id_flex_num => l_flex_definition(4),
300 x_seg_attr_type => l_flex_definition(5),
301 x_app_column_name => l_segment_column)
302 then
303 l_column_name := l_segment_column;
304 else
305 l_column_name := f.COLUMN_NAME;
306 end if;
307 else
308 l_column_name := f.COLUMN_NAME;
309 end if;
310 if i = c_count
311 then
312 c_attributes(i) := '<OPTION VALUE='||l_data_type||l_column_name||' SELECTED>'||f.ATTRIBUTE_LABEL_LONG;
313 else
314 c_attributes(i) := c_attributes(i)||'<OPTION VALUE='||l_data_type||l_column_name||'>'||f.ATTRIBUTE_LABEL_LONG;
315 end if;
316 end loop;
317 c_attributes(i) := c_attributes(i)||htf.formSelectClose;
318 end loop;
319
320 c_condition(0) := htf.formSelectOption(' ');
321 c_condition(1) := '';
322 for x in 0..1 loop
323 for i in 1..to_number(l_lookup_codes(0)) loop
324 if x = 1 and l_data_type1 = 'V' and l_lookup_codes(i) = 'CCONTAIN'
325 then
326 c_condition(x) := c_condition(x)||'<OPTION SELECTED VALUE='||l_lookup_codes(i)||'>'||l_lookup_meanings(i);
327 elsif x = 1 and l_data_type1 <> 'V' and l_lookup_codes(i) = 'AIS'
328 then
329 c_condition(x) := c_condition(x)||'<OPTION SELECTED VALUE='||l_lookup_codes(i)||'>'||l_lookup_meanings(i);
330 else
331 c_condition(x) := c_condition(x)||'<OPTION VALUE='||l_lookup_codes(i)||'>'||l_lookup_meanings(i);
332 end if;
333 end loop;
334 c_condition(x) := c_condition(x)||htf.formSelectClose;
335 end loop;
336
337 end if; -- Store and Reqs item sreach
338
339 htp.p('<SCRIPT LANGUAGE="JavaScript">');
340 htp.p('<!-- Hide from old browsers');
341
342 select icx_custom_call
343 into l_icx_custom_call
344 from ak_regions
345 where REGION_APPLICATION_ID = p_region_appl_id
346 and REGION_CODE = l_region_code;
347
348 if instr(l_icx_custom_call,'LONG') > 0
349 then
350 fnd_message.set_name('ICX','ICX_OPEN_QUERY2');
351 l_message := icx_util.replace_quotes(fnd_message.get);
352
353 if p_lines_now = 1
354 then
355 --added findform0 and supporting lines for 1570530 mputman.
356 htp.p('function submitFunction() {
357 if (document.findForm.i_1.value == "") {
358 alert("'||l_message||'");
359 document.findForm.i_1.focus();
360 } else {
361 var temp=document.findForm.a_1.selectedIndex;
362 document.findForm0.a_1.value=document.findForm.a_1[temp].value;
363 var temp=document.findForm.c_1.selectedIndex;
364 document.findForm0.c_1.value=document.findForm.c_1[temp].value;
365 document.findForm0.i_1.value = document.findForm.i_1.value;
366 document.findForm0.m.value = document.findForm.m.value;');
367 if p_hidden_name is not null
368 THEN
369 htp.p('document.findForm0.'||p_hidden_name||'.value = document.findForm.'||p_hidden_name||'.value;');
370 END IF;
371 htp.p('
372 document.findForm0.submit();
373 }
374 }');
375 else -- advanced
376 htp.p('function submitFunction() {
377 if (document.findForm.i_1.value == ""
378 '||'&'||'&'||' document.findForm.i_2.value == ""
379 '||'&'||'&'||' document.findForm.i_3.value == ""
380 '||'&'||'&'||' document.findForm.i_4.value == ""
381 '||'&'||'&'||' document.findForm.i_5.value == "") {
382 alert("'||l_message||'");
383 document.findForm.i_1.focus();
384 } else {
385
386 var temp=document.findForm.a_1.selectedIndex;
387 document.findForm0.a_1.value=document.findForm.a_1[temp].value;
388 var temp=document.findForm.c_1.selectedIndex;
389 document.findForm0.c_1.value=document.findForm.c_1[temp].value;
390 document.findForm0.i_1.value = document.findForm.i_1.value;
391
392 var temp=document.findForm.a_2.selectedIndex;
393 document.findForm0.a_2.value=document.findForm.a_2[temp].value;
394 var temp=document.findForm.c_2.selectedIndex;
395 document.findForm0.c_2.value=document.findForm.c_2[temp].value;
396 document.findForm0.i_2.value = document.findForm.i_2.value;
397
398 var temp=document.findForm.a_3.selectedIndex;
399 document.findForm0.a_3.value=document.findForm.a_3[temp].value;
400 var temp=document.findForm.c_3.selectedIndex;
401 document.findForm0.c_3.value=document.findForm.c_3[temp].value;
402 document.findForm0.i_3.value = document.findForm.i_3.value;
403
404 var temp=document.findForm.a_4.selectedIndex;
405 document.findForm0.a_4.value=document.findForm.a_4[temp].value;
406 var temp=document.findForm.c_4.selectedIndex;
407 document.findForm0.c_4.value=document.findForm.c_4[temp].value;
408 document.findForm0.i_4.value = document.findForm.i_4.value;
409
410 var temp=document.findForm.a_5.selectedIndex;
411 document.findForm0.a_5.value=document.findForm.a_5[temp].value;
412 var temp=document.findForm.c_5.selectedIndex;
413 document.findForm0.c_5.value=document.findForm.c_5[temp].value;
414 document.findForm0.i_5.value = document.findForm.i_5.value;
415
416 document.findForm0.m.value = document.findForm.m.value;');
417 if p_hidden_name is not null
418 THEN
419 htp.p('document.findForm0.'||p_hidden_name||'.value = document.findForm.'||p_hidden_name||'.value;');
420 END IF;
421 htp.p('document.findForm0.o.value = document.findForm.o.value;
422 document.findForm0.submit();
423 }
424 }');
425 end if; -- lines
426 else -- not block blind
427 fnd_message.set_name('ICX','ICX_OPEN_QUERY');
428 l_message := icx_util.replace_quotes(fnd_message.get);
429
430 if p_lines_now = 1
431 then
432
433 htp.p('function submitFunction() {
434 if (document.findForm.i_1.value == "") {
435 if (confirm("'||l_message||'")) {
436 var temp=document.findForm.a_1.selectedIndex;
437 document.findForm0.a_1.value=document.findForm.a_1[temp].value;
438 var temp=document.findForm.c_1.selectedIndex;
439 document.findForm0.c_1.value=document.findForm.c_1[temp].value;
440 document.findForm0.i_1.value = document.findForm.i_1.value;
441 document.findForm0.m.value = document.findForm.m.value;');
442 if p_hidden_name is not null
443 THEN
444 htp.p('document.findForm0.'||p_hidden_name||'.value = document.findForm.'||p_hidden_name||'.value;');
445 END IF;
446 htp.p('
447 document.findForm0.submit();
448 }
449 } else {
450 var temp=document.findForm.a_1.selectedIndex;
451 document.findForm0.a_1.value=document.findForm.a_1[temp].value;
452 var temp=document.findForm.c_1.selectedIndex;
453 document.findForm0.c_1.value=document.findForm.c_1[temp].value;
454 document.findForm0.i_1.value = document.findForm.i_1.value;
455 document.findForm0.m.value = document.findForm.m.value;');
456 if p_hidden_name is not null
457 THEN
458 htp.p('document.findForm0.'||p_hidden_name||'.value = document.findForm.'||p_hidden_name||'.value;');
459 END IF;
460 htp.p('
461 document.findForm0.submit();
462 }
463 }');
464 else -- advanced
465 htp.p('function submitFunction() {
466 if (document.findForm.i_1.value == ""
467 '||'&'||'&'||' document.findForm.i_2.value == ""
468 '||'&'||'&'||' document.findForm.i_3.value == ""
469 '||'&'||'&'||' document.findForm.i_4.value == ""
470 '||'&'||'&'||' document.findForm.i_5.value == "") {
471 if (confirm("'||l_message||'")) {
472 var temp=document.findForm.a_1.selectedIndex;
473 document.findForm0.a_1.value=document.findForm.a_1[temp].value;
474 var temp=document.findForm.c_1.selectedIndex;
475 document.findForm0.c_1.value=document.findForm.c_1[temp].value;
476 document.findForm0.i_1.value = document.findForm.i_1.value;
477
478 var temp=document.findForm.a_2.selectedIndex;
479 document.findForm0.a_2.value=document.findForm.a_2[temp].value;
480 var temp=document.findForm.c_2.selectedIndex;
481 document.findForm0.c_2.value=document.findForm.c_2[temp].value;
482 document.findForm0.i_2.value = document.findForm.i_2.value;
483
484 var temp=document.findForm.a_3.selectedIndex;
485 document.findForm0.a_3.value=document.findForm.a_3[temp].value;
486 var temp=document.findForm.c_3.selectedIndex;
487 document.findForm0.c_3.value=document.findForm.c_3[temp].value;
488 document.findForm0.i_3.value = document.findForm.i_3.value;
489
490 var temp=document.findForm.a_4.selectedIndex;
491 document.findForm0.a_4.value=document.findForm.a_4[temp].value;
492 var temp=document.findForm.c_4.selectedIndex;
493 document.findForm0.c_4.value=document.findForm.c_4[temp].value;
494 document.findForm0.i_4.value = document.findForm.i_4.value;
495
496 var temp=document.findForm.a_5.selectedIndex;
497 document.findForm0.a_5.value=document.findForm.a_5[temp].value;
498 var temp=document.findForm.c_5.selectedIndex;
499 document.findForm0.c_5.value=document.findForm.c_5[temp].value;
500 document.findForm0.i_5.value = document.findForm.i_5.value;
501
502 document.findForm0.m.value = document.findForm.m.value;');
503 if p_hidden_name is not null
504 THEN
505 htp.p('document.findForm0.'||p_hidden_name||'.value = document.findForm.'||p_hidden_name||'.value;');
506 END IF;
507 htp.p('document.findForm0.o.value = document.findForm.o.value;
508 document.findForm0.submit();
509
510 }
511 } else {
512 var temp=document.findForm.a_1.selectedIndex;
513 document.findForm0.a_1.value=document.findForm.a_1[temp].value;
514 var temp=document.findForm.c_1.selectedIndex;
515 document.findForm0.c_1.value=document.findForm.c_1[temp].value;
516 document.findForm0.i_1.value = document.findForm.i_1.value;
517
518 var temp=document.findForm.a_2.selectedIndex;
519 document.findForm0.a_2.value=document.findForm.a_2[temp].value;
520 var temp=document.findForm.c_2.selectedIndex;
521 document.findForm0.c_2.value=document.findForm.c_2[temp].value;
522 document.findForm0.i_2.value = document.findForm.i_2.value;
523
524 var temp=document.findForm.a_3.selectedIndex;
525 document.findForm0.a_3.value=document.findForm.a_3[temp].value;
526 var temp=document.findForm.c_3.selectedIndex;
527 document.findForm0.c_3.value=document.findForm.c_3[temp].value;
528 document.findForm0.i_3.value = document.findForm.i_3.value;
529
530 var temp=document.findForm.a_4.selectedIndex;
531 document.findForm0.a_4.value=document.findForm.a_4[temp].value;
532 var temp=document.findForm.c_4.selectedIndex;
533 document.findForm0.c_4.value=document.findForm.c_4[temp].value;
534 document.findForm0.i_4.value = document.findForm.i_4.value;
535
536 var temp=document.findForm.a_5.selectedIndex;
537 document.findForm0.a_5.value=document.findForm.a_5[temp].value;
538 var temp=document.findForm.c_5.selectedIndex;
539 document.findForm0.c_5.value=document.findForm.c_5[temp].value;
540 document.findForm0.i_5.value = document.findForm.i_5.value;
541
542 document.findForm0.m.value = document.findForm.m.value;');
543 if p_hidden_name is not null
544 THEN
545 htp.p('document.findForm0.'||p_hidden_name||'.value = document.findForm.'||p_hidden_name||'.value;');
546 end if;
547 htp.p('document.findForm0.o.value = document.findForm.o.value;
548
549 document.findForm0.submit();
550 }
551 }');
552 end if; -- lines
553 end if; -- LONG
554
555 if p_lines_now = 1
556 then
557 htp.p('function resetFunction() {
558 document.findForm.reset();
559 document.findForm.i_1.value = "";
560 }');
561 else
562 htp.p('function resetFunction() {
563 document.findForm.reset();
564 document.findForm.i_1.value = "";
565 document.findForm.i_2.value = "";
566 document.findForm.i_3.value = "";
567 document.findForm.i_4.value = "";
568 document.findForm.i_5.value = "";
569 }');
570 end if;
571
572 htp.p('// -->');
573 htp.p('</SCRIPT>');
574
575 htp.p('<!-- Application ID '||p_region_appl_id||' Region '||l_region_code||' -->');
576
577 --add addtl hidden form to handle submit mputman
578 htp.formOpen('OracleON.IC','POST','','','NAME="findForm0"');
579 htp.formHidden('a_1','""');
580 htp.formHidden('c_1','""');
581 htp.formHidden('i_1','""');
582 htp.formHidden('a_2','""');
583 htp.formHidden('c_2','""');
584 htp.formHidden('i_2','""');
585 htp.formHidden('a_3','""');
586 htp.formHidden('c_3','""');
587 htp.formHidden('i_3','""');
588 htp.formHidden('a_4','""');
589 htp.formHidden('c_4','""');
590 htp.formHidden('i_4','""');
591 htp.formHidden('a_5','""');
592 htp.formHidden('c_5','""');
593 htp.formHidden('i_5','""');
594 htp.formHidden('m','""');
595 if p_hidden_name is not null
596 THEN
597 htp.formHidden(p_hidden_name,'""');
598 END IF;
599 htp.formHidden('o','""');
600
601 htp.formClose;
602
603 if p_goto_url is null
604 then
605 htp.formOpen('javascript:submitFunction()','POST','','','NAME="findForm"');
606 else
607 htp.formOpen(p_goto_url,'POST',p_goto_target,'','NAME="findForm"');
608 end if;
609
610 if p_default_title = 'Y'
611 then
612
613 if p_page_code is not null
614 then
615 select NAME,DESCRIPTION
616 into l_page_title,l_page_description
617 from AK_FLOW_PAGES_VL
618 where PAGE_CODE = p_page_code
619 and PAGE_APPLICATION_ID = p_page_appl_id
620 and FLOW_CODE = p_flow_code
621 and FLOW_APPLICATION_ID = p_flow_appl_id;
622
623 end if;
624
625 select NAME,DESCRIPTION
626 into l_region_title,l_region_description
627 from AK_REGIONS_VL
628 where REGION_CODE = l_region_code
629 and REGION_APPLICATION_ID = p_region_appl_id;
630
631 if l_data_type1 = 'V'
632 then
633 fnd_message.set_name('ICX','ICX_FIND_VARCHAR2');
634 l_message := fnd_message.get;
635 else
636 fnd_message.set_name('ICX','ICX_FIND_NUMBER');
637 l_message := fnd_message.get;
638 end if;
639
640 htp.tableOpen('BORDER=0');
641 htp.tableRowOpen;
642 htp.tableData(cvalue => htf.img(curl => '/OA_MEDIA/FNDIFIND.gif', calt => c_prompts(1)));
643
644 if p_page_code is not null
645 then
646 htp.tableData(cvalue => '<B><FONT SIZE=+2>'||c_prompts(1)||': '||l_page_title||' </FONT></B>'||l_page_description, cattributes => 'VALIGN="MIDDLE"');
647 else
648 htp.tableData(cvalue => '<B><FONT SIZE=+2>'||c_prompts(1)||': '||l_region_title||'</FONT></B>', cattributes => 'VALIGN="MIDDLE"');
649 end if;
650
651 if p_new_url is not null
652 then
653 htp.p('<TD>');
654 icx_util.DynamicButton(P_ButtonText => c_prompts(5),
655 P_ImageFileName => 'FNDBNEW',
656 P_OnMouseOverText => c_prompts(5),
657 P_HyperTextCall => p_new_url,
658 P_LanguageCode => l_language_code,
659 P_JavaScriptFlag => FALSE);
660 htp.p('</TD>');
661 end if;
662
663 htp.tableRowClose;
664 if p_page_code is not null and l_region_description is not null
665 then
666 htp.tableRowOpen;
667 htp.tableData('<BR>');
668 htp.tableData(l_region_description);
669 htp.tableRowClose;
670 end if;
671 htp.tableClose;
672 htp.nl;
673 htp.p(l_message);
674 htp.nl;
675 htp.nl;
676
677 end if; -- p_default_title = 'Y'
678
679 if p_lines_now = 1
680 and (l_region_code = 'ICX_WEBSTORE_SEARCH_ITEMS_R'
681 or l_region_code = 'ICX_PO_SUPPL_SEARCH_ITEMS_R')
682 then
683 htp.tableOpen(cborder => 'BORDER=0',
684 cattributes => 'CELLPADDING=0 CELLSPACING=0');
685 htp.tableRowOpen;
686 htp.tableData(htf.formHidden('a_1',l_context_column));
687 htp.tableData(htf.formHidden('c_1','CCONTAIN'));
688 htp.tableData(htf.formText('i_1',20), 'LEFT');
689 htp.tableData('<BR>');
690 htp.tableData('<BR>');
691 htp.tableData('<BR>');
692 if p_LOV_mode = 'Y'
693 then
694 findIcons(p_submit => c_prompts(1),
695 p_clear => c_prompts(2),
696 p_one => c_prompts(4),
697 p_more => c_prompts(3),
698 p_lines_next => p_lines_next,
699 p_lines_now => p_lines_now,
700 p_url => c_url,
701 p_language_code => l_language_code,
702 p_clear_button => p_clear_button,
703 p_advanced_button => p_advanced_button);
704 end if;
705 htp.tableRowClose;
706 htp.tableClose;
707 else
708
709 if p_lines_now > 1
710 then
711 fnd_message.set_name('ICX','ICX_FIND_AND_OR_TEXT');
712 htp.p(fnd_message.get);htp.nl;
713
714 fnd_message.set_name('ICX','ICX_FIND_AND');
715 htp.p(htf.formRadio('o','AND','CHECKED')||fnd_message.get);htp.nl;
716
717 fnd_message.set_name('ICX','ICX_FIND_OR');
718 htp.p(htf.formRadio('o','OR')||fnd_message.get);htp.nl;
719 end if;
720
721 for i in 1..p_lines_now loop
722
723 htp.tableOpen('BORDER=0');
724 htp.tableRowOpen;
725 if i = 1
726 then
727 htp.tableData(htf.formSelectOpen('a_'||i)||c_attributes(1));
728 htp.tableData(htf.formSelectOpen('c_'||i)||c_condition(1));
729 else
730 htp.tableData(htf.formSelectOpen('a_'||i)||c_attributes(0));
731 htp.tableData(htf.formSelectOpen('c_'||i)||c_condition(0));
732 end if;
733 htp.tableData(htf.formText('i_'||i,20));
734 if i = 1 and p_LOV_mode = 'Y'
735 then
736 findIcons(p_submit => c_prompts(1),
737 p_clear => c_prompts(2),
738 p_one => c_prompts(4),
739 p_more => c_prompts(3),
740 p_lines_next => p_lines_next,
741 p_lines_now => p_lines_now,
742 p_url => c_url,
743 p_language_code => l_language_code,
744 p_clear_button => p_clear_button,
745 p_advanced_button => p_advanced_button);
746 end if;
747 htp.tableRowClose;
748 htp.tableClose;
749
750 end loop;
751
752 htp.nl;
753 htp.p(htf.formCheckbox('m')||c_prompts(6));
754 htp.nl;
755
756 end if; -- Store and Reqs
757
758 if p_hidden_name is not null
759 then
760 htp.formHidden(p_hidden_name,p_hidden_value);
761 end if;
762
763 if p_LOV_mode = 'N'
764 then
765 htp.nl;
766 htp.tableOpen('BORDER=0');
767 htp.tableRowOpen;
768 findIcons(p_submit => c_prompts(1),
769 p_clear => c_prompts(2),
770 p_one => c_prompts(4),
771 p_more => c_prompts(3),
772 p_lines_next => p_lines_next,
773 p_lines_now => p_lines_now,
774 p_url => c_url,
775 p_language_code => l_language_code,
776 p_clear_button => p_clear_button,
777 p_advanced_button => p_advanced_button);
778 htp.tableRowClose;
779 htp.tableClose;
780 end if;
781
782 htp.p('</FORM>');
783
784 exception
785 when others then
786 htp.p(SQLERRM);
787 end;
788
789 procedure getRegions(p_where in varchar2) is
790 l_timer number;
791
792 c_type varchar2(30);
793 c_rowid rowid;
794 l_start number;
795 l_end number;
796 l_query_set number;
797 l_start_region varchar2(30);
798 c_encrypted_where number;
799 c_unique_key_name varchar2(30);
800 c_keys v80_table;
801
802 c_continue boolean;
803 c_table_count number;
804
805 c_inputs1 varchar2(240);
806 c_inputs2 varchar2(240);
807 c_inputs3 varchar2(240);
808 c_inputs4 varchar2(240);
809 c_inputs5 varchar2(240);
810 c_inputs6 varchar2(240);
811 c_inputs7 varchar2(240);
812 c_inputs8 varchar2(240);
813 c_inputs9 varchar2(240);
814 c_inputs10 varchar2(240);
815 c_outputs1 varchar2(240);
816 c_outputs2 varchar2(240);
817 c_outputs3 varchar2(240);
818 c_outputs4 varchar2(240);
819 c_outputs5 varchar2(240);
820 c_outputs6 varchar2(240);
821 c_outputs7 varchar2(240);
822 c_outputs8 varchar2(240);
823 c_outputs9 varchar2(240);
824 c_outputs10 varchar2(240);
825 c_call integer;
826 c_dummy integer;
827 l_row_count number;
828
829 l_flow_appl_id number(15);
830 l_flow_code varchar2(30);
831 c_from_page_appl_id number(15);
832 c_from_page_code varchar2(30);
833 c_from_region_appl_id number(15);
834 c_from_region_code varchar2(30);
835 c_to_page_appl_id number(15);
836 c_to_page_code varchar2(30);
837 c_to_region_appl_id number(15);
838 c_to_region_code varchar2(30);
839
840 l_responsibility_id number(15);
841 l_user_id number(15);
842 l_return_children varchar2(1);
843
844 l_range_low number;
845 l_range_high number;
846 l_where_clause varchar2(2000);
847 l_query_binds ak_query_pkg.bind_tab;
848 l_max_rows number;
849 e_max_rows exception;
850 l_err_mesg varchar2(240);
851
852 cursor regions is
853 select REGION_APPLICATION_ID,REGION_CODE,ICX_CUSTOM_CALL
854 from AK_FLOW_PAGE_REGIONS
855 where FLOW_CODE = l_flow_code
856 and FLOW_APPLICATION_ID = l_flow_appl_id
857 and PAGE_CODE = c_to_page_code
858 and PAGE_APPLICATION_ID = c_to_page_appl_id
859 order by DISPLAY_SEQUENCE;
860
861 begin
862
863 -- select HSECS into l_timer from v$timer;htp.p('begin getRegions @ '||l_timer);htp.nl;
864
865 /*
866 for i in 1..22 loop
867 htp.p(i||' = '||nvl(icx_on_utilities.g_on_parameters(i),'NULL'));
868 htp.nl;
869 end loop;
870 */
871
872 c_type := icx_on_utilities.g_on_parameters(1);
873
874 c_continue := TRUE;
875 c_table_count := 1;
876
877 if c_type = 'DQ'
878 then
879
880 l_flow_appl_id := icx_on_utilities.g_on_parameters(2);
881 l_flow_code := icx_on_utilities.g_on_parameters(3);
882 c_to_page_appl_id := icx_on_utilities.g_on_parameters(4);
883 c_to_page_code := icx_on_utilities.g_on_parameters(5);
884 l_start := nvl(icx_on_utilities.g_on_parameters(6),1);
885 l_end := icx_on_utilities.g_on_parameters(7);
886 l_start_region := icx_on_utilities.g_on_parameters(8);
887 c_encrypted_where := icx_on_utilities.g_on_parameters(9);
888
889 elsif c_type = 'W'
890 then
891
892 l_flow_appl_id := icx_on_utilities.g_on_parameters(2);
893 l_flow_code := icx_on_utilities.g_on_parameters(3);
894 c_to_page_appl_id := icx_on_utilities.g_on_parameters(4);
895 c_to_page_code := icx_on_utilities.g_on_parameters(5);
896 l_start := nvl(icx_on_utilities.g_on_parameters(6),1);
897 l_end := icx_on_utilities.g_on_parameters(7);
898 l_start_region := icx_on_utilities.g_on_parameters(8);
899 c_encrypted_where := icx_on_utilities.g_on_parameters(9);
900
901 else
902
903 l_start := nvl(icx_on_utilities.g_on_parameters(6),1);
904 l_end := icx_on_utilities.g_on_parameters(7);
905 l_start_region := icx_on_utilities.g_on_parameters(8);
906 c_rowid := icx_on_utilities.g_on_parameters(10);
907 c_unique_key_name := icx_on_utilities.g_on_parameters(11);
908 c_keys(1) := icx_on_utilities.g_on_parameters(12);
909 c_keys(2) := icx_on_utilities.g_on_parameters(13);
910 c_keys(3) := icx_on_utilities.g_on_parameters(14);
911 c_keys(4) := icx_on_utilities.g_on_parameters(15);
912 c_keys(5) := icx_on_utilities.g_on_parameters(16);
913 c_keys(6) := icx_on_utilities.g_on_parameters(17);
914 c_keys(7) := icx_on_utilities.g_on_parameters(18);
915 c_keys(8) := icx_on_utilities.g_on_parameters(19);
916 c_keys(9) := icx_on_utilities.g_on_parameters(20);
917 c_keys(10) := icx_on_utilities.g_on_parameters(21);
918
919 select FLOW_APPLICATION_ID,FLOW_CODE,
920 TO_PAGE_APPL_ID,TO_PAGE_CODE
921 into l_flow_appl_id,l_flow_code,
922 c_to_page_appl_id,c_to_page_code
923 from AK_FLOW_REGION_RELATIONS
924 where ROWID = c_rowid;
925
926 end if;
927
928 for r in regions loop
929
930 if r.ICX_CUSTOM_CALL is not null and c_continue = TRUE
931 then
932
933 -- ************* Start Custom Call *************************
934
935 c_inputs1 := c_keys(1);
936 c_inputs2 := c_keys(2);
937 c_inputs3 := c_keys(3);
938 c_inputs4 := c_keys(4);
939 c_inputs5 := c_keys(5);
940 c_inputs6 := c_keys(6);
941 c_inputs7 := c_keys(7);
942 c_inputs8 := c_keys(8);
943 c_inputs9 := c_keys(9);
944 c_inputs10 := c_keys(10);
945
946 c_outputs1 := '123456789012345678901234567890';
947 c_outputs2 := c_outputs1;
948 c_outputs3 := c_outputs1;
949 c_outputs4 := c_outputs1;
950 c_outputs5 := c_outputs1;
951 c_outputs6 := c_outputs1;
952 c_outputs7 := c_outputs1;
953 c_outputs8 := c_outputs1;
954 c_outputs9 := c_outputs1;
955 c_outputs10 := c_outputs1;
956
957 c_call := dbms_sql.open_cursor;
958
959 dbms_sql.parse(c_call,'begin '||r.ICX_CUSTOM_CALL||'(:c_inputs1,:c_inputs2,:c_inputs3,:c_inputs4,:c_inputs5,:c_inputs6,:c_inputs7,:c_inputs8,:c_inputs9,:c_inputs10,
960 :c_outputs1,:c_outputs2,:c_outputs3,:c_outputs4,:c_outputs5,:c_outputs6,:c_outputs7,:c_outputs8,:c_outputs9,:c_outputs10); end;',dbms_sql.native);
961
962 dbms_sql.bind_variable(c_call,'c_inputs1',c_inputs1);
963 dbms_sql.bind_variable(c_call,'c_inputs2',c_inputs2);
964 dbms_sql.bind_variable(c_call,'c_inputs3',c_inputs3);
965 dbms_sql.bind_variable(c_call,'c_inputs4',c_inputs4);
966 dbms_sql.bind_variable(c_call,'c_inputs5',c_inputs5);
967 dbms_sql.bind_variable(c_call,'c_inputs6',c_inputs6);
968 dbms_sql.bind_variable(c_call,'c_inputs7',c_inputs7);
969 dbms_sql.bind_variable(c_call,'c_inputs8',c_inputs8);
970 dbms_sql.bind_variable(c_call,'c_inputs9',c_inputs9);
971 dbms_sql.bind_variable(c_call,'c_inputs10',c_inputs10);
972 dbms_sql.bind_variable(c_call,'c_outputs1',c_outputs1);
973 dbms_sql.bind_variable(c_call,'c_outputs2',c_outputs2);
974 dbms_sql.bind_variable(c_call,'c_outputs3',c_outputs3);
975 dbms_sql.bind_variable(c_call,'c_outputs4',c_outputs4);
976 dbms_sql.bind_variable(c_call,'c_outputs5',c_outputs5);
977 dbms_sql.bind_variable(c_call,'c_outputs6',c_outputs6);
978 dbms_sql.bind_variable(c_call,'c_outputs7',c_outputs7);
979 dbms_sql.bind_variable(c_call,'c_outputs8',c_outputs8);
980 dbms_sql.bind_variable(c_call,'c_outputs9',c_outputs9);
981 dbms_sql.bind_variable(c_call,'c_outputs10',c_outputs10);
982
983 c_dummy := dbms_sql.execute(c_call);
984
985 dbms_sql.variable_value(c_call,'c_outputs1',c_outputs1);
986 dbms_sql.variable_value(c_call,'c_outputs2',c_outputs2);
987 dbms_sql.variable_value(c_call,'c_outputs3',c_outputs3);
988 dbms_sql.variable_value(c_call,'c_outputs4',c_outputs4);
989 dbms_sql.variable_value(c_call,'c_outputs5',c_outputs5);
990 dbms_sql.variable_value(c_call,'c_outputs6',c_outputs6);
991 dbms_sql.variable_value(c_call,'c_outputs7',c_outputs7);
992 dbms_sql.variable_value(c_call,'c_outputs8',c_outputs8);
993 dbms_sql.variable_value(c_call,'c_outputs9',c_outputs9);
994 dbms_sql.variable_value(c_call,'c_outputs10',c_outputs10);
995
996 dbms_sql.close_cursor(c_call);
997
998 c_from_region_appl_id := r.REGION_APPLICATION_ID;
999 c_from_region_code := r.REGION_CODE;
1000
1001 select count(*)
1002 into l_row_count
1003 from AK_FLOW_REGION_RELATIONS a,
1004 AK_FOREIGN_KEYS b
1005 where a.FROM_REGION_CODE = c_from_region_code
1006 and a.FROM_REGION_APPL_ID = c_from_region_appl_id
1007 and a.FROM_PAGE_CODE = c_to_page_code
1008 and a.FROM_PAGE_APPL_ID = c_to_page_appl_id
1009 and a.FLOW_CODE = l_flow_code
1010 and a.FLOW_APPLICATION_ID = l_flow_appl_id
1011 and a.FOREIGN_KEY_NAME = b.FOREIGN_KEY_NAME;
1012
1013 if l_row_count = 1
1014 then
1015
1016 select a.ROWID,b.UNIQUE_KEY_NAME,
1017 a.TO_PAGE_APPL_ID,a.TO_PAGE_CODE
1018 into c_rowid,c_unique_key_name,
1019 c_to_page_appl_id,c_to_page_code
1020 from AK_FLOW_REGION_RELATIONS a,
1021 AK_FOREIGN_KEYS b
1022 where a.FROM_REGION_CODE = c_from_region_code
1023 and a.FROM_REGION_APPL_ID = c_from_region_appl_id
1024 and a.FROM_PAGE_CODE = c_to_page_code
1025 and a.FROM_PAGE_APPL_ID = c_to_page_appl_id
1026 and a.FLOW_CODE = l_flow_code
1027 and a.FLOW_APPLICATION_ID = l_flow_appl_id
1028 and a.FOREIGN_KEY_NAME = b.FOREIGN_KEY_NAME;
1029
1030 c_type := 'X';
1031
1032 c_keys(1) := c_outputs1;
1033 c_keys(2) := c_outputs2;
1034 c_keys(3) := c_outputs3;
1035 c_keys(4) := c_outputs4;
1036 c_keys(5) := c_outputs5;
1037 c_keys(6) := c_outputs6;
1038 c_keys(7) := c_outputs7;
1039 c_keys(8) := c_outputs8;
1040 c_keys(9) := c_outputs9;
1041 c_keys(10) := c_outputs10;
1042
1043 else
1044
1045 c_type := 'Z';
1046 c_continue := FALSE;
1047 ak_query_pkg.g_regions_table(0).flow_application_id := -1;
1048
1049 end if;
1050
1051 -- ************* End Custom Call *************************
1052 end if;
1053
1054 if c_table_count = 1 and (c_type = 'DQ' or c_type = 'W')
1055 then
1056
1057 c_to_region_appl_id := r.REGION_APPLICATION_ID;
1058 c_to_region_code := r.REGION_CODE;
1059
1060 l_responsibility_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
1061 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
1062 if c_type = 'W'
1063 then
1064 l_return_children := 'T';
1065 else
1066 l_return_children := 'F';
1067 end if;
1068
1069 select QUERY_SET, MAX_ROWS
1070 into l_query_set, l_max_rows
1071 from ICX_PARAMETERS;
1072
1073 if l_end is null
1074 then
1075 l_end := l_start+l_query_set-1;
1076 end if;
1077
1078 -- select HSECS into l_timer from v$timer;htp.p('start exec_query LOV @ '||l_timer);htp.nl;
1079
1080 -- dbms_session.set_sql_trace(TRUE);
1081
1082 unpack_whereSegment(p_where,l_where_clause,l_query_binds);
1083
1084 /* DEBUG TRACE
1085 htp.p('p_where = '||p_where);htp.nl;
1086 htp.p('DEBUG MESSAGE ak_query_pkg.exec_query('''||l_flow_appl_id||''','''||l_flow_code||''','''||c_to_page_appl_id||''','''||c_to_page_code||''','||c_to_region_appl_id||','''||c_to_region_code
1087 ||''','''||''','''||''','''||''','''||''','''||''','''||''','''||''','''||''','''||''','''||''','''||''','''||
1088 c_to_page_appl_id||''','''||c_to_page_code||''','''||l_where_clause||''','''||''','||l_responsibility_id||','||l_user_id||',T,'||l_return_children||',F,F,'||l_range_low||','||l_range_high||')');htp.nl;
1089 if l_query_binds.COUNT > 0 then
1090 for i in l_query_binds.FIRST..l_query_binds.LAST loop
1091 htp.p(l_query_binds(i).name||' '||l_query_binds(i).value);htp.nl;
1092 end loop;
1093 end if;
1094 */
1095
1096 ak_query_pkg.exec_query(
1097 P_FLOW_APPL_ID => l_flow_appl_id,
1098 P_FLOW_CODE => l_flow_code,
1099 P_PARENT_PAGE_APPL_ID => c_to_page_appl_id,
1100 P_PARENT_PAGE_CODE => c_to_page_code,
1101 P_PARENT_REGION_APPL_ID => c_to_region_appl_id,
1102 P_PARENT_REGION_CODE => c_to_region_code,
1103 P_CHILD_PAGE_APPL_ID => c_to_page_appl_id,
1104 P_CHILD_PAGE_CODE => c_to_page_code,
1105 P_WHERE_CLAUSE => l_where_clause,
1106 P_WHERE_BINDS => l_query_binds,
1107 P_RESPONSIBILITY_ID => l_responsibility_id,
1108 P_USER_ID => l_user_id,
1109 P_RETURN_PARENTS => 'T',
1110 P_RETURN_CHILDREN => l_return_children,
1111 P_RETURN_NODE_DISPLAY_ONLY => 'T',
1112 P_RANGE_LOW => l_start,
1113 P_RANGE_HIGH => l_end,
1114 P_MAX_ROWS => l_max_rows);
1115
1116 if ak_query_pkg.g_regions_table(0).total_result_count = l_max_rows
1117 then
1118 raise e_max_rows;
1119 end if;
1120
1121 -- icx_on_utilities2.printPLSQLtables;
1122
1123 -- dbms_session.set_sql_trace(FALSE);
1124
1125 -- select HSECS into l_timer from v$timer;htp.p('end exec_query LOV @ '||l_timer);htp.nl;
1126
1127 c_table_count := 2;
1128 end if;
1129
1130 if c_table_count = 1 and (c_type = 'D' or c_type = 'X')
1131 then
1132
1133 select FROM_PAGE_APPL_ID,FROM_PAGE_CODE,
1134 FROM_REGION_APPL_ID,FROM_REGION_CODE
1135 into c_from_page_appl_id,c_from_page_code,
1136 c_from_region_appl_id,c_from_region_code
1137 from AK_FLOW_REGION_RELATIONS
1138 where ROWID = c_rowid;
1139
1140 l_responsibility_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
1141 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
1142
1143 /* DEBUG
1144 set serverout on size 200000;
1145 ALTER SESSION SET SQL_TRACE TRUE;
1146 execute
1147 htp.p('DEBUG MESSAGE ak_query_pkg.exec_query('||l_flow_appl_id||','''||l_flow_code||''','||c_from_page_appl_id||','''||c_from_page_code||''','||c_from_region_appl_id||','''||c_from_region_code
1148 ||''','''||c_unique_key_name||''','''||c_keys(1)||''','''||c_keys(2)||''','''||c_keys(3)||''','''||c_keys(4)||''','''||c_keys(5)||''','''||c_keys(6)||''','''||c_keys(7)||''','''||c_keys(8)||''','''||c_keys(9)||''','''||c_keys(10)
1149 ||''','''||c_to_page_appl_id||''','''||c_to_page_code||''','''||''','''||''','||l_responsibility_id||','||l_user_id||',F,T,F,F)');
1150 ALTER SESSION SET SQL_TRACE FALSE;
1151 */
1152
1153 -- select HSECS into l_timer from v$timer;htp.p('start exec_query PK @ '||l_timer);htp.nl;
1154
1155 -- dbms_session.set_sql_trace(TRUE);
1156
1157 ak_query_pkg.exec_query(
1158 P_FLOW_APPL_ID => l_flow_appl_id,
1159 P_FLOW_CODE => l_flow_code,
1160 P_PARENT_PAGE_APPL_ID => c_from_page_appl_id,
1161 P_PARENT_PAGE_CODE => c_from_page_code,
1162 P_PARENT_REGION_APPL_ID => c_from_region_appl_id,
1163 P_PARENT_REGION_CODE => c_from_region_code,
1164 P_PARENT_PRIMARY_KEY_NAME => c_unique_key_name,
1165 P_PARENT_KEY_VALUE1 => c_keys(1),
1166 P_PARENT_KEY_VALUE2 => c_keys(2),
1167 P_PARENT_KEY_VALUE3 => c_keys(3),
1168 P_PARENT_KEY_VALUE4 => c_keys(4),
1169 P_PARENT_KEY_VALUE5 => c_keys(5),
1170 P_PARENT_KEY_VALUE6 => c_keys(6),
1171 P_PARENT_KEY_VALUE7 => c_keys(7),
1172 P_PARENT_KEY_VALUE8 => c_keys(8),
1173 P_PARENT_KEY_VALUE9 => c_keys(9),
1174 P_PARENT_KEY_VALUE10 => c_keys(10),
1175 P_CHILD_PAGE_APPL_ID => c_to_page_appl_id,
1176 P_CHILD_PAGE_CODE => c_to_page_code,
1177 P_RESPONSIBILITY_ID => l_responsibility_id,
1178 P_USER_ID => l_user_id,
1179 P_RETURN_PARENTS => 'F',
1180 P_RETURN_CHILDREN => 'T',
1181 P_RETURN_NODE_DISPLAY_ONLY => 'T');
1182
1183 -- icx_on_utilities2.printPLSQLtables;
1184
1185 -- dbms_session.set_sql_trace(FALSE);
1186
1187 -- select HSECS into l_timer from v$timer;htp.p('start exec_query PK @ '||l_timer);htp.nl;
1188
1189 c_table_count := 2;
1190 end if;
1191
1192 end loop;
1193
1194 exception
1195 when e_max_rows then
1196 fnd_message.set_name('ICX','ICX_MAX_ROWS');
1197 l_err_mesg := fnd_message.get;
1198 icx_util.add_error(l_err_mesg);
1199 icx_admin_sig.error_screen(l_err_mesg);
1200 c_type := 'Z';
1201 c_continue := FALSE;
1202 ak_query_pkg.g_regions_table(0).flow_application_id := -1;
1203 end;
1204
1205 procedure displayPage is
1206
1207 l_timer number;
1208
1209 l_flow_appl_id number(15);
1210 l_flow_code varchar2(30);
1211 l_page_appl_id number(15);
1212 l_page_code varchar2(30);
1213
1214 l_region_count number;
1215 l_region_temp number;
1216 l_region_seq_temp number;
1217 l_region number_table;
1218 l_region_seq number_table;
1219 l_prompt varchar2(50);
1220 l_S varchar2(2000);
1221 l_language_code varchar2(30) := icx_sec.getID(icx_sec.pv_language_code);
1222 l_cookie owa_cookie.cookie;
1223 l_page_title varchar2(240);
1224 l_page_description varchar2(2000);
1225 l_region_description varchar2(2000);
1226 l_message varchar2(2000);
1227 l_status varchar2(240);
1228 l_continue boolean;
1229
1230 begin
1231
1232 -- select HSECS into l_timer from v$timer;htp.p('begin displayPage @ '||l_timer);htp.nl;
1233
1234 l_flow_appl_id := ak_query_pkg.g_regions_table(ak_query_pkg.g_regions_table.FIRST).flow_application_id;
1235 l_flow_code := ak_query_pkg.g_regions_table(ak_query_pkg.g_regions_table.FIRST).flow_code;
1236 l_page_appl_id := ak_query_pkg.g_regions_table(ak_query_pkg.g_regions_table.FIRST).page_application_id;
1237 l_page_code := ak_query_pkg.g_regions_table(ak_query_pkg.g_regions_table.FIRST).page_code;
1238
1239 select NAME,DESCRIPTION
1240 into l_page_title,l_page_description
1241 from AK_FLOW_PAGES_VL
1242 where PAGE_CODE = l_page_code
1243 and PAGE_APPLICATION_ID = l_page_appl_id
1244 and FLOW_CODE = l_flow_code
1245 and FLOW_APPLICATION_ID = l_flow_appl_id;
1246
1247 l_cookie := owa_cookie.get('onquery');
1248
1249 if icx_on_utilities.g_on_parameters(22) = 'Y'
1250 then
1251 htp.htmlOpen;
1252 htp.headOpen;
1253 icx_util.copyright;
1254
1255 htp.p('<SCRIPT LANGUAGE="JavaScript">');
1256 htp.p('<!-- Hide from old browsers');
1257 htp.p('function goto_button(X) {self.location=X; };');
1258
1259 icx_admin_sig.help_win_script('OracleON.IC?X='||icx_call.encrypt2(l_flow_appl_id||'*'||l_flow_code||'*'||l_page_appl_id||'*'||l_page_code||'*'||'ICX_HLP_INQUIRIES'||'**]'),l_language_code);
1260
1261 htp.p('// -->');
1262 htp.p('</SCRIPT>');
1263
1264 htp.title(l_page_title);
1265 htp.headClose;
1266 else
1267 htp.p('<SCRIPT LANGUAGE="JavaScript">');
1268 htp.p('<!-- Hide from old browsers');
1269 htp.p('function goto_button(X) {self.location=X; };');
1270 htp.p('// -->');
1271 htp.p('</SCRIPT>');
1272 end if;
1273
1274 l_S := icx_on_utilities.g_on_parameters(1)||'*'||icx_on_utilities.g_on_parameters(2)||'*'||icx_on_utilities.g_on_parameters(3)||'*'||icx_on_utilities.g_on_parameters(4)||'*'||icx_on_utilities.g_on_parameters(5)
1275 ||'*'||icx_on_utilities.g_on_parameters(6)||'*'||icx_on_utilities.g_on_parameters(7)||'*'||icx_on_utilities.g_on_parameters(8)||'*'||icx_on_utilities.g_on_parameters(9)||'*'||icx_on_utilities.g_on_parameters(10)
1276 ||'*'||icx_on_utilities.g_on_parameters(11)||'*'||icx_on_utilities.g_on_parameters(12)||'*'||icx_on_utilities.g_on_parameters(13)||'*'||icx_on_utilities.g_on_parameters(14)||'*'||icx_on_utilities.g_on_parameters(15)
1277 ||'*'||icx_on_utilities.g_on_parameters(16)||'*'||icx_on_utilities.g_on_parameters(17)||'*'||icx_on_utilities.g_on_parameters(18)||'*'||icx_on_utilities.g_on_parameters(19)||'*'||icx_on_utilities.g_on_parameters(20)
1278 ||'*'||icx_on_utilities.g_on_parameters(21)||'*'||icx_on_utilities.g_on_parameters(22)||'**]';
1279
1280 if icx_on_utilities.g_on_parameters(22) = 'Y'
1281 then
1282 if (l_cookie.num_vals > 0) then
1283 icx_admin_sig.toolbar(language_code => l_language_code,
1284 disp_export => l_S,
1285 disp_find => l_cookie.vals(l_cookie.num_vals));
1286 else
1287 icx_admin_sig.toolbar(language_code => l_language_code);
1288 end if;
1289 end if;
1290
1291 htp.p('<FONT SIZE=+2>'||l_page_title||' </FONT>'||l_page_description);
1292 htp.nl;
1293
1294 if ak_query_pkg.g_results_table.COUNT = 0
1295 then
1296 htp.p('<!-- Flow '||ak_query_pkg.g_regions_table(0).flow_code||' Page '||ak_query_pkg.g_regions_table(0).page_code||' Region '||ak_query_pkg.g_regions_table(0).region_code||' -->');
1297 fnd_message.set_name('ICX','ICX_NO_RECORDS_FOUND');
1298 fnd_message.set_token('NAME_OF_REGION_TOKEN',ak_query_pkg.g_regions_table(0).name);
1299 l_message := fnd_message.get;
1300
1301 htp.strong(l_message);htp.nl;
1302
1303 else
1304 l_continue := TRUE;
1305
1306 -- bubble sort for bug 625660
1307 l_region_count := ak_query_pkg.g_regions_table.COUNT-1;
1308
1309 if l_region_count > 0
1310 then
1311
1312 for x in 0..l_region_count loop
1313 l_region(x) := x;
1314 l_region_seq(x) := ak_query_pkg.g_regions_table(x).display_sequence;
1315 end loop;
1316
1317 for x in 0..l_region_count-1 loop
1318 for y in 0..l_region_count-1-x loop
1319 if l_region_seq(y) > l_region_seq(y+1)
1320 then
1321 l_region_seq_temp := l_region_seq(y);
1322 l_region_temp := l_region(y);
1323 l_region_seq(y) := l_region_seq(y+1);
1324 l_region(y) := l_region(y+1);
1325 l_region_seq(y+1) := l_region_seq_temp;
1326 l_region(y+1) := l_region_temp;
1327 end if;
1328 end loop;
1329 end loop;
1330
1331 for x in 0..l_region_count loop
1332 ak_query_pkg.g_regions_table(l_region(x)).display_sequence := x;
1333 end loop;
1334
1335 else
1336 ak_query_pkg.g_regions_table(0).display_sequence := 0;
1337 end if;
1338
1339 -- second region loop added for bug 591931
1340 for c in 0..l_region_count loop
1341 for r in 0..l_region_count loop
1342
1343 if r = ak_query_pkg.g_regions_table(c).display_sequence
1344 then
1345
1346 if l_continue
1347 then
1348
1349 select DESCRIPTION
1350 into l_region_description
1351 from AK_REGIONS_VL
1352 where REGION_APPLICATION_ID = ak_query_pkg.g_regions_table(r).region_application_id
1353 and REGION_CODE = ak_query_pkg.g_regions_table(r).region_code;
1354
1355 if l_region_description is not null
1356 then
1357 htp.p(l_region_description);
1358 end if;
1359
1360 htp.p('<!-- Flow '||ak_query_pkg.g_regions_table(r).flow_code||' Page '||ak_query_pkg.g_regions_table(r).page_code||' Region '||ak_query_pkg.g_regions_table(r).region_code||' -->');
1361
1362 if ak_query_pkg.g_regions_table(r).total_result_count = 0
1363 then
1364 fnd_message.set_name('ICX','ICX_NO_RECORDS_FOUND');
1365 fnd_message.set_token('NAME_OF_REGION_TOKEN',ak_query_pkg.g_regions_table(r).name);
1366 l_message := fnd_message.get;
1367
1368 htp.strong(l_message);htp.nl;
1369
1370 elsif ak_query_pkg.g_regions_table(r).region_style = 'FORM'
1371 and ak_query_pkg.g_regions_table(r).total_result_count > 1
1372 then
1373 fnd_message.set_name('ICX','ICX_LIMIT_ROWS_ONE');
1374 l_message := fnd_message.get;
1375
1376 htp.strong(l_message);
1377 l_continue := FALSE;
1378 else
1379 icx_on_utilities2.displayRegion(ak_query_pkg.g_regions_table(r).region_rec_id);
1380 end if; -- total_result_count = 0
1381 end if; -- l_continue
1382 end if; -- display_sequence
1383
1384 end loop; -- regions r
1385 end loop; -- regions c
1386
1387 end if; -- COUNT = 0
1388
1389 if icx_on_utilities.g_on_parameters(22) = 'Y'
1390 then
1391 icx_sig.footer;
1392 htp.htmlClose;
1393 end if;
1394
1395 -- select HSECS into l_timer from v$timer;htp.p('end displayPage @ '||l_timer);htp.nl;
1396
1397 end;
1398
1399 function formatText(c_text in varchar2,
1400 c_bold in varchar2,
1401 c_italic in varchar2) return varchar2 is
1402
1403 c_return_text varchar2(5000);
1404
1405 begin
1406
1407 if c_text is null
1408 then
1409 c_return_text := c_ampersand||'nbsp';
1410 else
1411 c_return_text := c_text;
1412
1413 if c_bold = 'Y'
1414 then
1415 c_return_text := '<B>'||c_return_text||'</B>';
1416 end if;
1417
1418 if c_italic = 'Y'
1419 then
1420 c_return_text := '<I>'||c_return_text||'</I>';
1421 end if;
1422 end if;
1423
1424 return c_return_text;
1425
1426 end;
1427
1428 function formatData(c_text in varchar2,
1429 c_halign in varchar2,
1430 c_valign in varchar2) return varchar2 is
1431
1432 c_return_text varchar2(2000);
1433
1434 begin
1435
1436 if c_text is null
1437 then
1438 c_return_text := '<TD>'||c_ampersand||'nbsp'||'</TD>';
1439 else
1440 c_return_text := '<TD ALIGN='||c_halign||' VALIGN='||c_valign||'>'||c_text||'</TD>';
1441 end if;
1442
1443 return c_return_text;
1444
1445 end;
1446
1447
1448 function whereSegment(a_1 in varchar2,
1449 c_1 in varchar2,
1450 i_1 in varchar2,
1451 a_2 in varchar2,
1452 c_2 in varchar2,
1453 i_2 in varchar2,
1454 a_3 in varchar2,
1455 c_3 in varchar2,
1456 i_3 in varchar2,
1457 a_4 in varchar2,
1458 c_4 in varchar2,
1459 i_4 in varchar2,
1460 a_5 in varchar2,
1461 c_5 in varchar2,
1462 i_5 in varchar2,
1463 m in varchar2,
1464 o in varchar2)
1465 return varchar2 is
1466
1467 c_attributes v80_table;
1468 c_conditions v80_table;
1469 c_inputs v80_table;
1470
1471 begin
1472
1473 c_attributes(1) := a_1;
1474 c_attributes(2) := a_2;
1475 c_attributes(3) := a_3;
1476 c_attributes(4) := a_4;
1477 c_attributes(5) := a_5;
1478 c_attributes(6) := '';
1479 c_conditions(1) := c_1;
1480 c_conditions(2) := c_2;
1481 c_conditions(3) := c_3;
1482 c_conditions(4) := c_4;
1483 c_conditions(5) := c_5;
1484 c_conditions(6) := '';
1485 c_inputs(1) := i_1;
1486 c_inputs(2) := i_2;
1487 c_inputs(3) := i_3;
1488 c_inputs(4) := i_4;
1489 c_inputs(5) := i_5;
1490 c_inputs(6) := '';
1491
1492 return whereSegment(c_attributes,c_conditions,c_inputs,m,o);
1493
1494 end;
1495
1496 function whereSegment(c_attributes in v80_table,
1497 c_conditions in v80_table,
1498 c_inputs in v80_table,
1499 p_match in varchar2,
1500 p_and_or in varchar2)
1501 return varchar2 is
1502
1503 c_where varchar2(2000);
1504 c_data_type varchar2(1);
1505 c_column_name varchar2(30);
1506 l_condition varchar2(30);
1507 l_input varchar2(240);
1508 l_lower varchar2(10);
1509 l_upper varchar2(10);
1510 c_and varchar2(1);
1511 c_number_input number;
1512 l_query_id number;
1513 l_context_count number;
1514 l_context_input varchar2(80);
1515 l_index number;
1516 l_values v240_table;
1517
1518 begin
1519
1520 c_and := 'N';
1521 c_where := '';
1522 l_index := 0;
1523 for i in 1..5 loop
1524 if c_attributes(i) is not null and c_conditions(i) is not null
1525 then
1526 c_data_type := substr(c_attributes(i),1,1);
1527 c_column_name := substr(c_attributes(i),2,31);
1528 l_condition := substr(c_conditions(i),2,31);
1529 l_input := c_inputs(i);
1530
1531 if c_data_type = 'X' and l_input is not null
1532 then
1533 l_context_input := translate(l_input,' ,|&;?$">:','~~~~~~~~~~');
1534 l_context_input := replace(l_input,' - ',' ~ ');
1535
1536 l_context_count := instr(l_input,'~');
1537
1538 -- bug 610969, handle decimal points
1539
1540 if l_context_count = 0
1541 then
1542 if instr(l_input,'.') = 0
1543 then
1544 if instr(l_input,'-') = 0
1545 then
1546 l_input := '%'||l_input||'%';
1547 else
1548 l_input := '{'||l_input||'}';
1549 end if;
1550 else
1551 l_input := l_input||'%';
1552 end if;
1553 else
1554 l_input := replace(l_input,' ','&');
1555 end if;
1556
1557 select ICX_CONTEXT_RESULTS_TEMP_S.nextval
1558 into l_query_id
1559 from sys.dual;
1560
1561 /* -- replace with intermedia !!!
1562 ctx_query.contains
1563 (POLICY_NAME => c_column_name,
1564 TEXT_QUERY => l_input,
1565 RESTAB => 'ICX_CONTEXT_RESULTS_TEMP',
1566 SHARELEVEL => 1,
1567 QUERY_ID => l_query_id);
1568 */
1569
1570 c_where := c_where||' CONID = '||l_query_id;
1571 c_and := 'Y';
1572 -- GK: Make sure this darn code doesnt get executed by adding 1=2
1573 -- mputman 1747066 undo GK change
1574 elsif c_data_type = 'V' and p_match is null and l_input is not null
1575 then
1576 l_input := upper(l_input);
1577 l_upper := substr(l_input,1,1);
1578 l_lower := lower(l_upper);
1579
1580 if c_and = 'Y' and p_and_or = 'OR'
1581 then
1582 c_where := c_where||' or ';
1583 elsif c_and = 'Y' and p_and_or = 'AND'
1584 then
1585 c_where := c_where||' and ';
1586 end if;
1587
1588 if l_condition = 'IS'
1589 then
1590 c_where := c_where||' upper('||c_column_name||') = :ICXBIND'||l_index;
1591 l_values(l_index) := l_input;
1592 l_index := l_index + 1;
1593 elsif l_condition = 'NOT'
1594 then
1595 c_where := c_where||' upper('||c_column_name||') <> :ICXBIND'||l_index;
1596 l_values(l_index) := l_input;
1597 l_index := l_index + 1;
1598 elsif l_condition = 'CONTAIN'
1599 then
1600 c_where := c_where||' upper('||c_column_name||') like :ICXBIND'||l_index;
1601 l_values(l_index) := '%'||l_input||'%';
1602 l_index := l_index + 1;
1603 elsif l_condition = 'START'
1604 then
1605 c_where := c_where||' upper('||c_column_name||') like :ICXBIND'||l_index||' and ('||c_column_name||' like :ICXBIND'||to_char(l_index+1)||' or '||c_column_name||' like :ICXBIND'||to_char(l_index+2)||')';
1606 l_values(l_index) := l_input||'%';
1607 l_values(l_index+1) := l_lower||'%';
1608 l_values(l_index+2) := l_upper||'%';
1609 l_index := l_index + 3;
1610 elsif l_condition = 'GREATER'
1611 or l_condition = 'AFTER'
1612 then
1613 c_where := c_where||' upper('||c_column_name||') > :ICXBIND'||l_index;
1614 l_values(l_index) := l_input;
1615 l_index := l_index + 1;
1616 elsif l_condition = 'END'
1617 then
1618 c_where := c_where||' upper('||c_column_name||') like :ICXBIND'||l_index;
1619 l_values(l_index) := '%'||l_input;
1620 l_index := l_index + 1;
1621 elsif l_condition = 'LESS'
1622 or l_condition = 'BEFORE'
1623 then
1624 c_where := c_where||' upper('||c_column_name||') < :ICXBIND'||l_index;
1625 l_values(l_index) := l_input;
1626 l_index := l_index + 1;
1627 else
1628 c_where := c_where||' upper('||c_column_name||') = :ICXBIND'||l_index;
1629 l_values(l_index) := l_input;
1630 l_index := l_index + 1;
1631 end if; -- l_condition
1632 c_and := 'Y';
1633 else
1634 if l_condition = 'IS' and l_input is null
1635 then
1636 if c_and = 'Y' then c_where := c_where||' and '; end if;
1637 c_where := c_where||' '||c_column_name||' is null';
1638 c_and := 'Y';
1639 elsif l_condition = 'IS'
1640 then
1641 if c_and = 'Y' then c_where := c_where||' and '; end if;
1642 if c_data_type = 'V'
1643 then
1644 c_where := c_where||' '||c_column_name||' = :ICXBIND'||l_index;
1645 l_values(l_index) := l_input;
1646 l_index := l_index + 1;
1647 elsif c_data_type = 'D'
1648 then
1649 l_input := upper(l_input);
1650 checkDate(l_input);
1651 c_where := c_where||' trunc('||c_column_name||') = to_date(:ICXBIND'||l_index||','|| icx_sec.g_date_format || ')';
1652 l_values(l_index) := l_input;
1653 l_index := l_index + 1;
1654 elsif c_data_type = 'N'
1655 then
1656 c_where := c_where||' '||c_column_name||' = :ICXBIND'||l_index;
1657 l_values(l_index) := l_input;
1658 l_index := l_index + 1;
1659 elsif c_data_type = 'T'
1660 then
1661 l_input := upper(l_input);
1662 c_where := c_where||' '||c_column_name||' = to_date(:ICXBIND'||l_index||','|| icx_sec.g_date_format || ')';
1663 l_values(l_index) := l_input;
1664 l_index := l_index + 1;
1665 else
1666 c_where := c_where||' '||c_column_name||' = :ICXBIND'||l_index;
1667 l_values(l_index) := l_input;
1668 l_index := l_index + 1;
1669 end if;
1670 c_and := 'Y';
1671 elsif l_condition = 'NOT' and l_input is null
1672 then
1673 if c_and = 'Y' then c_where := c_where||' and '; end if;
1674 c_where := c_where||' '||c_column_name||' is not null';
1675 c_and := 'Y';
1676 elsif l_condition = 'NOT'
1677 then
1678 if c_and = 'Y' then c_where := c_where||' and '; end if;
1679 if c_data_type = 'V'
1680 then
1681 c_where := c_where||' '||c_column_name||' <> :ICXBIND'||l_index;
1682 l_values(l_index) := l_input;
1683 l_index := l_index + 1;
1684 elsif c_data_type = 'D'
1685 then
1686 l_input := upper(l_input);
1687 checkDate(l_input);
1688 c_where := c_where||' trunc('||c_column_name||') <> to_date(:ICXBIND'||l_index||','|| icx_sec.g_date_format || ')';
1689 l_values(l_index) := l_input;
1690 l_index := l_index + 1;
1691 elsif c_data_type = 'N'
1692 then
1693 c_where := c_where||' '||c_column_name||' <> :ICXBIND'||l_index;
1694 l_values(l_index) := l_input;
1695 l_index := l_index + 1;
1696 elsif c_data_type = 'T'
1697 then
1698 l_input := upper(l_input);
1699 c_where := c_where||' '||c_column_name||' <> to_date(:ICXBIND'||l_index||','|| icx_sec.g_date_format || ')';
1700 l_values(l_index) := l_input;
1701 l_index := l_index + 1;
1702 else
1703 c_where := c_where||' '||c_column_name||' <> :ICXBIND'||l_index;
1704 l_values(l_index) := l_input;
1705 l_index := l_index + 1;
1706 end if;
1707 c_and := 'Y';
1708 elsif l_condition = 'CONTAIN'
1709 then
1710 if l_input is null
1711 then
1712 null;
1713 elsif c_data_type = 'D'
1714 then
1715 l_input := upper(l_input);
1716 if c_and = 'Y' then c_where := c_where||' and '; end if;
1717 c_where := c_where||' to_char('||c_column_name||') like :ICXBIND'||l_index;
1718 l_values(l_index) := '%'||l_input||'%';
1719 l_index := l_index + 1;
1720 c_and := 'Y';
1721 else
1722 if c_and = 'Y' then c_where := c_where||' and '; end if;
1723 c_where := c_where||' '||c_column_name||' like :ICXBIND'||l_index;
1724 l_values(l_index) := '%'||l_input||'%';
1725 l_index := l_index + 1;
1726 c_and := 'Y';
1727 end if;
1728 elsif l_condition = 'START'
1729 then
1730 if l_input is null
1731 then
1732 null;
1733 elsif c_data_type = 'V'
1734 then
1735 if c_and = 'Y' then c_where := c_where||' and '; end if;
1736 c_where := c_where||' '||c_column_name||' like :ICXBIND'||l_index;
1737 l_values(l_index) := l_input||'%';
1738 l_index := l_index + 1;
1739 c_and := 'Y';
1740 elsif c_data_type = 'D'
1741 then
1742 l_input := upper(l_input);
1743 if c_and = 'Y' then c_where := c_where||' and '; end if;
1744 checkDate(l_input);
1745 c_where := c_where||' trunc('||c_column_name||') >= to_date(:ICXBIND'||l_index||','|| icx_sec.g_date_format || ')';
1746 l_values(l_index) := l_input;
1747 l_index := l_index + 1;
1748 c_and := 'Y';
1749 elsif c_data_type = 'N'
1750 then
1751 if c_and = 'Y' then c_where := c_where||' and '; end if;
1752 c_where := c_where||' '||c_column_name||' >= :ICXBIND'||l_index;
1753 l_values(l_index) := l_input;
1754 l_index := l_index + 1;
1755 c_and := 'Y';
1756 elsif c_data_type = 'T'
1757 then
1758 l_input := upper(l_input);
1759 if c_and = 'Y' then c_where := c_where||' and '; end if;
1760 c_where := c_where||' '||c_column_name||' >= to_date(:ICXBIND'||l_index||','|| icx_sec.g_date_format || ')';
1761 l_values(l_index) := l_input;
1762 l_index := l_index + 1;
1763 c_and := 'Y';
1764 else
1765 if c_and = 'Y' then c_where := c_where||' and '; end if;
1766 c_where := c_where||' '||c_column_name||' like :ICXBIND'||l_index;
1767 l_values(l_index) := l_input;
1768 l_index := l_index + 1;
1769 c_and := 'Y';
1770 end if;
1771 elsif l_condition = 'GREATER'
1772 or l_condition = 'AFTER'
1773 then
1774 if l_input is null
1775 then
1776 null;
1777 elsif c_data_type = 'V'
1778 then
1779 if c_and = 'Y' then c_where := c_where||' and '; end if;
1780 c_where := c_where||' '||c_column_name||' > :ICXBIND'||l_index;
1781 l_values(l_index) := l_input;
1782 l_index := l_index + 1;
1783 c_and := 'Y';
1784 elsif c_data_type = 'D'
1785 then
1786 l_input := upper(l_input);
1787 if c_and = 'Y' then c_where := c_where||' and '; end if;
1788 checkDate(l_input);
1789 c_where := c_where||' trunc('||c_column_name||') > to_date(:ICXBIND'||l_index||','|| icx_sec.g_date_format || ')';
1790 l_values(l_index) := l_input;
1791 l_index := l_index + 1;
1792 c_and := 'Y';
1793 elsif c_data_type = 'N'
1794 then
1795 if c_and = 'Y' then c_where := c_where||' and '; end if;
1796 c_where := c_where||' '||c_column_name||' > :ICXBIND'||l_index;
1797 l_values(l_index) := l_input;
1798 l_index := l_index + 1;
1799 c_and := 'Y';
1800 elsif c_data_type = 'T'
1801 then
1802 l_input := upper(l_input);
1803 if c_and = 'Y' then c_where := c_where||' and '; end if;
1804 c_where := c_where||' '||c_column_name||' > to_date(:ICXBIND'||l_index||','|| icx_sec.g_date_format || ')';
1805 l_values(l_index) := l_input;
1806 l_index := l_index + 1;
1807 c_and := 'Y';
1808 else
1809 if c_and = 'Y' then c_where := c_where||' and '; end if;
1810 c_where := c_where||' '||c_column_name||' > :ICXBIND'||l_index;
1811 l_values(l_index) := l_input;
1812 l_index := l_index + 1;
1813 c_and := 'Y';
1814 end if;
1815 elsif l_condition = 'END'
1816 then
1817 if l_input is null
1818 then
1819 null;
1820 elsif c_data_type = 'V'
1821 then
1822 if c_and = 'Y' then c_where := c_where||' and '; end if;
1823 c_where := c_where||' '||c_column_name||' like :ICXBIND'||l_index;
1824 l_values(l_index) := '%'||l_input;
1825 l_index := l_index + 1;
1826 c_and := 'Y';
1827 elsif c_data_type = 'D'
1828 then
1829 l_input := upper(l_input);
1830 if c_and = 'Y' then c_where := c_where||' and '; end if;
1831 checkDate(l_input);
1832 c_where := c_where||' trunc('||c_column_name||') <= to_date(:ICXBIND'||l_index||','|| icx_sec.g_date_format || ')';
1833 l_values(l_index) := l_input;
1834 l_index := l_index + 1;
1835 c_and := 'Y';
1836 elsif c_data_type = 'N'
1837 then
1838 if c_and = 'Y' then c_where := c_where||' and '; end if;
1839 c_where := c_where||' '||c_column_name||' <= :ICXBIND'||l_index;
1840 l_values(l_index) := l_input;
1841 l_index := l_index + 1;
1842 c_and := 'Y';
1843 elsif c_data_type = 'T'
1844 then
1845 l_input := upper(l_input);
1846 if c_and = 'Y' then c_where := c_where||' and '; end if;
1847 c_where := c_where||' '||c_column_name||' <= to_date(:ICXBIND'||l_index||','|| icx_sec.g_date_format || ')';
1848 l_values(l_index) := l_input;
1849 l_index := l_index + 1;
1850 c_and := 'Y';
1851 else
1852 if c_and = 'Y' then c_where := c_where||' and '; end if;
1853 c_where := c_where||' '||c_column_name||' like :ICXBIND'||l_index;
1854 l_values(l_index) := '%'||l_input;
1855 l_index := l_index + 1;
1856 c_and := 'Y';
1857 end if;
1858 elsif l_condition = 'LESS'
1859 or l_condition = 'BEFORE'
1860 then
1861 if l_input is null
1862 then
1863 null;
1864 elsif c_data_type = 'V'
1865 then
1866 if c_and = 'Y' then c_where := c_where||' and '; end if;
1867 c_where := c_where||' '||c_column_name||' < :ICXBIND'||l_index;
1868 l_values(l_index) := l_input;
1869 l_index := l_index + 1;
1870 c_and := 'Y';
1871 elsif c_data_type = 'D'
1872 then
1873 l_input := upper(l_input);
1874 if c_and = 'Y' then c_where := c_where||' and '; end if;
1875 checkDate(l_input);
1876 c_where := c_where||' trunc('||c_column_name||') < to_date(:ICXBIND'||l_index||','|| icx_sec.g_date_format || ')';
1877 l_values(l_index) := l_input;
1878 l_index := l_index + 1;
1879 c_and := 'Y';
1880 elsif c_data_type = 'N'
1881 then
1882 if c_and = 'Y' then c_where := c_where||' and '; end if;
1883 c_where := c_where||' '||c_column_name||' < :ICXBIND'||l_index;
1884 l_values(l_index) := l_input;
1885 l_index := l_index + 1;
1886 c_and := 'Y';
1887 elsif c_data_type = 'T'
1888 then
1889 l_input := upper(l_input);
1890 if c_and = 'Y' then c_where := c_where||' and '; end if;
1891 c_where := c_where||' '||c_column_name||' < to_date(:ICXBIND'||l_index||','|| icx_sec.g_date_format || ')';
1892 l_values(l_index) := l_input;
1893 l_index := l_index + 1;
1894 c_and := 'Y';
1895 else
1896 if c_and = 'Y' then c_where := c_where||' and '; end if;
1897 c_where := c_where||' '||c_column_name||' < :ICXBIND'||l_index;
1898 l_values(l_index) := l_input;
1899 l_index := l_index + 1;
1900 c_and := 'Y';
1901 end if;
1902 else
1903 if c_and = 'Y' then c_where := c_where||' and '; end if;
1904 c_where := c_where||' '||c_column_name||' = :ICXBIND'||l_index;
1905 l_values(l_index) := l_input;
1906 l_index := l_index + 1;
1907 c_and := 'Y';
1908 end if; -- l_condition
1909 end if; -- p_match
1910 end if; -- not null
1911 end loop;
1912
1913 if l_values.COUNT > 0
1914 then
1915 for i in l_values.FIRST..l_values.LAST loop
1916 l_values(i) := replace(l_values(i),'*','@#$@');
1917 c_where := c_where||'*'||l_values(i);
1918 -- htp.p('DEBUG 1 = '||l_values(i));htp.nl;
1919 end loop;
1920 end if;
1921 c_where := c_where||'**]';
1922 -- htp.p('DEBUG 2 = '||c_where);htp.nl;
1923
1924 return c_where;
1925
1926 end;
1927
1928 procedure unpack_whereSegment(p_whereSegment in varchar2,
1929 p_where_clause out NOCOPY varchar2,
1930 p_query_binds out NOCOPY ak_query_pkg.bind_tab) is
1931 l_parameters v2000_table;
1932 l_index number;
1933 begin
1934
1935 l_index := 0;
1936 unpack_parameters(p_whereSegment,l_parameters);
1937 p_where_clause := l_parameters(1);
1938 if l_parameters.COUNT > 1
1939 then
1940 for i in 2..l_parameters.LAST loop
1941 if l_parameters(i) is not null
1942 then
1943 p_query_binds(l_index).name := 'ICXBIND'||l_index;
1944 p_query_binds(l_index).value := replace(l_parameters(i),'@#$@','*');
1945 l_index := l_index + 1;
1946
1947
1948 end if;
1949 end loop;
1950 end if;
1951 end;
1952
1953 procedure unpack_whereSegment (p_whereSegment in varchar2,
1954 p_query_binds IN out NOCOPY ak_query_pkg.bind_tab,
1955 p_query_binds_index IN NUMBER) is
1956 -- This version of unpack_whereSegment is used with an additional bound where
1957 -- clause being passed to ICX from the product teams.
1958 l_parameters v2000_table;
1959 l_index number;
1960 l_bind_index NUMBER;
1961 begin
1962
1963 l_index := 0; -- running index of next bind name
1964 l_bind_index:=p_query_binds_index; -- running index of next in line for plsql table
1965
1966 --turn the delimited string into a plsql table.
1967 unpack_parameters(p_whereSegment,l_parameters);
1968
1969 for i in 1..l_parameters.LAST loop
1970 if l_parameters(i) is not null
1971 then
1972
1973 p_query_binds(l_bind_index).name := 'ICXBIND_W'||l_index;
1974 p_query_binds(l_bind_index).value := replace(l_parameters(i),'@#$@','*');
1975 l_index := l_index + 1;
1976 l_bind_index:=l_bind_index +1;
1977 end if;
1978 end loop;
1979 end;
1980
1981
1982
1983 procedure unpack_parameters(Y in varchar2,
1984 c_parameters out NOCOPY v80_table) is
1985 c_param number(15);
1986 c_count number(15);
1987 c_char varchar2(4);
1988 c_word varchar2(240);
1989 l_length number(15);
1990 begin
1991
1992 l_length := length(Y) + 1;
1993 c_param := 1;
1994 c_count := 0;
1995 c_char := '';
1996 c_word := '';
1997
1998 while nvl(c_char,'x') <> ']' and c_count < l_length loop
1999 if nvl(c_char,'x') <> '*'
2000 then
2001 c_word := c_word||c_char;
2002 c_count := c_count + 1;
2003 c_char := substr(Y,c_count,1);
2004 else
2005 c_count := c_count + 1;
2006 c_char := substr(Y,c_count,1);
2007 c_word := replace(c_word,'~at~','*');
2008 c_word := replace(c_word,'~end~',']');
2009 c_parameters(c_param) := c_word;
2010 c_word := '';
2011 c_param := c_param + 1;
2012 end if;
2013 end loop;
2014
2015 end;
2016
2017 procedure unpack_parameters(Y in varchar2,
2018 c_parameters out NOCOPY v240_table) is
2019 c_param number(15);
2020 c_count number(15);
2021 c_char varchar2(4);
2022 c_word varchar2(240);
2023 l_length number(15);
2024 begin
2025
2026 l_length := length(Y) + 1;
2027 c_param := 1;
2028 c_count := 0;
2029 c_char := '';
2030 c_word := '';
2031
2032 while nvl(c_char,'x') <> ']' and c_count < l_length loop
2033 if nvl(c_char,'x') <> '*'
2034 then
2035 c_word := c_word||c_char;
2036 c_count := c_count + 1;
2037 c_char := substr(Y,c_count,1);
2038 else
2039 c_count := c_count + 1;
2040 c_char := substr(Y,c_count,1);
2041 c_word := replace(c_word,'~at~','*');
2042 c_word := replace(c_word,'~end~',']');
2043 c_parameters(c_param) := c_word;
2044 c_word := '';
2045 c_param := c_param + 1;
2046 end if;
2047 end loop;
2048
2049 end;
2050
2051 procedure unpack_parameters(Y in varchar2,
2052 c_parameters out NOCOPY v2000_table) is
2053 c_param number(15);
2054 c_count number(15);
2055 c_char varchar2(4);
2056 c_word varchar2(2000);
2057 l_length number(15);
2058 begin
2059
2060 l_length := length(Y) + 1;
2061 c_param := 1;
2062 c_count := 0;
2063 c_char := '';
2064 c_word := '';
2065
2066 while nvl(c_char,'x') <> ']' and c_count < l_length loop
2067 if nvl(c_char,'x') <> '*'
2068 then
2069 c_word := c_word||c_char;
2070 c_count := c_count + 1;
2071 c_char := substr(Y,c_count,1);
2072 else
2073 c_count := c_count + 1;
2074 c_char := substr(Y,c_count,1);
2075 c_word := replace(c_word,'~at~','*');
2076 c_word := replace(c_word,'~end~',']');
2077 c_parameters(c_param) := c_word;
2078 c_word := '';
2079 c_param := c_param + 1;
2080 end if;
2081 end loop;
2082
2083 end;
2084
2085 procedure checkDate(p_date in varchar2) is
2086
2087 l_dummy_date date;
2088 l_dummy_varchar2 varchar2(100);
2089 l_dummy_db_date VARCHAR2(100);--mputman 1618876
2090 C_date_format VARCHAR2(100); --mputman 1618876
2091
2092 begin
2093
2094 -- begin changes for 1618876 --mputman
2095 -- changed to be more flexible and to work better in stateful envs.
2096
2097 --select to_char(to_date(p_date))
2098 --into l_dummy_varchar2
2099 --from sys.dual;
2100 l_dummy_db_date:=icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT');
2101
2102 IF (nvl(icx_sec.g_date_format,'X') <> nvl(icx_sec.g_date_format_c,'Y')) or
2103 (nvl(icx_sec.g_date_format_c,'Y') <> l_dummy_db_date) THEN
2104
2105 c_date_format := ''''||icx_sec.g_date_format||'''';
2106 dbms_session.set_nls('NLS_DATE_FORMAT', c_date_format);
2107 icx_sec.g_date_format_c := icx_sec.g_date_format;
2108
2109 END IF;
2110
2111 select to_date(p_date,icx_sec.g_date_format)
2112 into l_dummy_date
2113 from sys.dual;
2114 -- end changes for 1618876
2115
2116 if p_date <> l_dummy_varchar2
2117 then
2118 select to_date(p_date,'XX-XXX-XXXX')
2119 into l_dummy_date
2120 from sys.dual;
2121 end if;
2122
2123 end;
2124
2125 function buildOracleONstring(p_rowid in varchar2,
2126 p_primary_key in varchar2,
2127 p1 in varchar2,
2128 p2 in varchar2,
2129 p3 in varchar2,
2130 p4 in varchar2,
2131 p5 in varchar2,
2132 p6 in varchar2,
2133 p7 in varchar2,
2134 p8 in varchar2,
2135 p9 in varchar2,
2136 p10 in varchar2)
2137 return varchar2 is
2138 l_parameter varchar2(2000);
2139
2140 begin
2141
2142 l_parameter := 'D*****1****'||p_rowid||'*'||p_primary_key||'*'
2143 ||p1||'*'
2144 ||p2||'*'
2145 ||p3||'*'
2146 ||p4||'*'
2147 ||p5||'*'
2148 ||p6||'*'
2149 ||p7||'*'
2150 ||p8||'*'
2151 ||p9||'*'
2152 ||p10||'**]';
2153
2154 return l_parameter;
2155
2156 end;
2157
2158 function buildOracleONstring2(p_flow_application_id in varchar2,
2159 p_flow_code in varchar2,
2160 p_page_application_id in varchar2,
2161 p_page_code in varchar2,
2162 p_where_segment in varchar2)
2163 return varchar2 is
2164 l_parameter varchar2(2000);
2165
2166 begin
2167
2168 l_parameter := 'W*'
2169 ||p_flow_application_id||'*'
2170 ||p_flow_code||'*'
2171 ||p_page_application_id||'*'
2172 ||p_page_code||'*'
2173 ||p_where_segment||'**]';
2174
2175 return l_parameter;
2176
2177 end;
2178
2179 procedure printRegions(p_rowid in varchar2,
2180 p_primary_key in varchar2,
2181 p1 in varchar2,
2182 p2 in varchar2,
2183 p3 in varchar2,
2184 p4 in varchar2,
2185 p5 in varchar2,
2186 p6 in varchar2,
2187 p7 in varchar2,
2188 p8 in varchar2,
2189 p9 in varchar2,
2190 p10 in varchar2) is
2191 l_dummy_table icx_on_utilities.v80_table;
2192 begin
2193
2194 icx_on_utilities.g_on_parameters(1) := 'X';
2195 icx_on_utilities.g_on_parameters(2) := '';
2196 icx_on_utilities.g_on_parameters(3) := '';
2197 icx_on_utilities.g_on_parameters(4) := '';
2198 icx_on_utilities.g_on_parameters(5) := '';
2199 icx_on_utilities.g_on_parameters(6) := '1';
2200 icx_on_utilities.g_on_parameters(7) := '';
2201 icx_on_utilities.g_on_parameters(8) := '';
2202 icx_on_utilities.g_on_parameters(9) := '';
2203 icx_on_utilities.g_on_parameters(10) := p_rowid;
2204 icx_on_utilities.g_on_parameters(11) := p_primary_key;
2205 icx_on_utilities.g_on_parameters(12) := p1;
2206 icx_on_utilities.g_on_parameters(13) := p2;
2207 icx_on_utilities.g_on_parameters(14) := p3;
2208 icx_on_utilities.g_on_parameters(15) := p4;
2209 icx_on_utilities.g_on_parameters(16) := p5;
2210 icx_on_utilities.g_on_parameters(17) := p6;
2211 icx_on_utilities.g_on_parameters(18) := p7;
2212 icx_on_utilities.g_on_parameters(19) := p8;
2213 icx_on_utilities.g_on_parameters(20) := p9;
2214 icx_on_utilities.g_on_parameters(21) := p10;
2215 icx_on_utilities.g_on_parameters(22) := 'N';
2216
2217 icx_on.get_page(l_dummy_table,l_dummy_table,l_dummy_table,'','');
2218
2219 end;
2220
2221 procedure printRegions2(p_flow_application_id in varchar2,
2222 p_flow_code in varchar2,
2223 p_page_application_id in varchar2,
2224 p_page_code in varchar2,
2225 p_where_segment in varchar2) is
2226 l_dummy_table icx_on_utilities.v80_table;
2227 begin
2228
2229 icx_on_utilities.g_on_parameters(1) := 'W';
2230 icx_on_utilities.g_on_parameters(2) := p_flow_application_id;
2231 icx_on_utilities.g_on_parameters(3) := p_flow_code;
2232 icx_on_utilities.g_on_parameters(4) := p_page_application_id;
2233 icx_on_utilities.g_on_parameters(5) := p_page_code;
2234 icx_on_utilities.g_on_parameters(6) := p_where_segment;
2235 icx_on_utilities.g_on_parameters(7) := '';
2236 icx_on_utilities.g_on_parameters(8) := '';
2237 icx_on_utilities.g_on_parameters(9) := '';
2238 icx_on_utilities.g_on_parameters(10) := '';
2239 icx_on_utilities.g_on_parameters(11) := '';
2240 icx_on_utilities.g_on_parameters(12) := '';
2241 icx_on_utilities.g_on_parameters(13) := '';
2242 icx_on_utilities.g_on_parameters(14) := '';
2243 icx_on_utilities.g_on_parameters(15) := '';
2244 icx_on_utilities.g_on_parameters(16) := '';
2245 icx_on_utilities.g_on_parameters(17) := '';
2246 icx_on_utilities.g_on_parameters(18) := '';
2247 icx_on_utilities.g_on_parameters(19) := '';
2248 icx_on_utilities.g_on_parameters(20) := '';
2249 icx_on_utilities.g_on_parameters(21) := '';
2250 icx_on_utilities.g_on_parameters(22) := 'N';
2251
2252 icx_on.get_page(l_dummy_table,l_dummy_table,l_dummy_table,'','');
2253
2254 end;
2255
2256
2257 end icx_on_utilities;