[Home] [Help]
PACKAGE BODY: APPS.ICX_REQ_SEARCH
Source
1 PACKAGE BODY ICX_REQ_SEARCH as
2 /* $Header: ICXSRCHB.pls 115.7 99/07/17 03:25:05 porting sh $ */
3
4
5 ------------------------------------------------------------------------
6 procedure find_form_head_region(p_lines_now number) is
7 ------------------------------------------------------------------------
8
9 l_message varchar2(1000);
10
11 begin
12
13 fnd_message.set_name('ICX','ICX_OPEN_QUERY');
14 l_message := icx_util.replace_quotes(fnd_message.get);
15
16 if p_lines_now = 1
17 then
18 htp.p('function submitFunction() {
19 if (document.findForm.i_1.value == "") {
20 if (confirm("'||l_message||'")) {
21 document.findForm.submit();
22 }
23 } else {
24 document.findForm.submit();
25 }
26 }');
27 else
28 htp.p('function submitFunction() {
29 if (document.findForm.i_1.value == ""
30 '||'&'||'&'||' document.findForm.i_2.value == ""
31 '||'&'||'&'||' document.findForm.i_3.value == ""
32 '||'&'||'&'||' document.findForm.i_4.value == ""
33 '||'&'||'&'||' document.findForm.i_5.value == "") {
34 if (confirm("'||l_message||'")) {
35 document.findForm.submit();
36 }
37 } else {
38 document.findForm.submit();
39 }
40 };');
41 end if;
42
43 htp.p('function resetFunction() {
44 document.findForm.reset();
45 document.findForm.i_1.value = "";');
46 if p_lines_now > 1
47 then
48 htp.p('document.findForm.i_2.value = "";
49 document.findForm.i_3.value = "";
50 document.findForm.i_4.value = "";
51 document.findForm.i_5.value = "";');
52 end if;
53 htp.p('};');
54
55
56 end;
57
58 ------------------------------------------------------------------------
59 function chk_exclude_on(v_attribute_code IN varchar2)
60 return varchar2 is
61 ------------------------------------------------------------------------
62 cursor RespExclAttrs(resp_id number,appl_id number,attr_code varchar2) is
63 select attribute_code
64 from ak_excluded_items
65 where responsibility_id = resp_id
66 and resp_application_id = appl_id
67 and attribute_code = attr_code;
68
69 v_resp_id number;
70 v_attr_code varchar2(80);
71
72 begin
73
74 v_resp_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
75 if v_resp_id is not NULL then
76
77 open RespExclAttrs(v_resp_id,178,v_attribute_code);
78 fetch RespExclAttrs into v_attr_code;
79 if RespExclAttrs%NOTFOUND then
80 close RespExclAttrs;
81 return 'N';
82 else
83 close RespExclAttrs;
84 return 'Y';
85 end if;
86 else
87 return 'N';
88 end if;
89 end;
90
91 -------------------------------------------------------------------
92 procedure findIcons( p_submit in varchar2,
93 p_clear in varchar2,
94 p_one in varchar2,
95 p_more in varchar2,
96 p_lines_next in number,
97 p_lines_now in number,
98 p_url in varchar2,
99 p_language_code in varchar2) is
100 -------------------------------------------------------------------
101 l_browser varchar2(400) := owa_util.get_cgi_env('HTTP_USER_AGENT');
102
103 begin
104
105 htp.p('<TD>');
106 icx_util.DynamicButton(P_ButtonText => p_submit,
107 P_ImageFileName => 'FNDBSBMT',
108 P_OnMouseOverText => p_submit,
109 P_HyperTextCall => 'javascript:submitFunction()',
110 P_LanguageCode => p_language_code,
111 P_JavaScriptFlag => FALSE);
112 htp.p('</TD>');
113 if (instr(l_browser,'MSIE')=0)
114 then
115 htp.p('<TD>');
116 icx_util.DynamicButton(P_ButtonText => p_clear,
117 P_ImageFileName => 'FNDBCLR',
118 P_OnMouseOverText => p_clear,
119 P_HyperTextCall => 'javascript:resetFunction()',
120 P_LanguageCode => p_language_code,
121 P_JavaScriptFlag => FALSE);
122 htp.p('</TD>');
123 end if;
124 if p_lines_next is not null
125 then
126 htp.p('<TD>');
127 if p_lines_next > p_lines_now
128 then
129 icx_util.DynamicButton(P_ButtonText => p_more,
130 P_ImageFileName => 'FNDBMORC',
131 P_OnMouseOverText => p_more,
132 P_HyperTextCall => p_url,
133 P_LanguageCode => p_language_code,
134 P_JavaScriptFlag => FALSE);
135 else
136 icx_util.DynamicButton(P_ButtonText => p_one,
137 P_ImageFileName => 'FNDBONEC',
138 P_OnMouseOverText => p_one,
139 P_HyperTextCall => p_url,
140 P_LanguageCode => p_language_code,
141 P_JavaScriptFlag => FALSE);
142 end if;
143 htp.p('</TD>');
144 end if;
145
146 exception
147 when others then
148 htp.p(SQLERRM);
149 end;
150
151 -------------------------------------------------------------------
152 procedure findForm(p_region_appl_id in number,
153 p_region_code in varchar2,
154 p_goto_url in varchar2 default null,
155 p_goto_target in varchar2 default null,
156 p_lines_now in number default 1,
157 p_lines_url in varchar2 default null,
158 p_lines_target in varchar2 default null,
159 p_lines_next in number default 5,
160 p_hidden_name in varchar2 default null,
161 p_hidden_value in varchar2 default null,
162 p_help_url in varchar2 default null,
163 p_new_url in varchar2 default null,
164 p_LOV_mode in varchar2 default 'N',
165 p_default_title in varchar2 default 'Y',
166 p_values in number default null) is
167 -------------------------------------------------------------------
168 l_language_code varchar2(30) := icx_sec.getID(icx_sec.pv_language_code);
169 l_responsibility_id number := icx_sec.getID(icx_sec.pv_responsibility_id);
170
171 l_message varchar2(240);
172 l_page_title varchar2(30);
173
174 c_title varchar2(50);
175 c_prompts icx_util.g_prompts_table;
176 l_lookup_codes icx_util.g_lookup_code_table;
177 l_lookup_meanings icx_util.g_lookup_meaning_table;
178
179 c_count number;
180 l_data_type varchar2(1);
181 c_attributes icx_on_utilities.v2000_table;
182 c_condition icx_on_utilities.v2000_table;
183 c_input icx_on_utilities.v80_table;
184 l_categories varchar2(2000);
185 c_url varchar2(2000);
186 c_buttons varchar2(2000);
187
188 cursor FindAttributes is
189 select d.COLUMN_NAME,b.DATA_TYPE,a.ATTRIBUTE_LABEL_LONG
190 from AK_ATTRIBUTES b,
191 AK_REGIONS c,
192 AK_OBJECT_ATTRIBUTES d,
193 AK_REGION_ITEMS_VL a
194 where a.REGION_APPLICATION_ID = p_region_appl_id
195 and a.REGION_CODE = p_region_code
196 and a.NODE_QUERY_FLAG = 'Y'
197 and a.REGION_APPLICATION_ID = c.REGION_APPLICATION_ID
198 and a.REGION_CODE = c.REGION_CODE
199 and c.DATABASE_OBJECT_NAME = d.DATABASE_OBJECT_NAME
200 and a.ATTRIBUTE_APPLICATION_ID = d.ATTRIBUTE_APPLICATION_ID
201 and a.ATTRIBUTE_CODE = d.ATTRIBUTE_CODE
202 and a.ATTRIBUTE_APPLICATION_ID = b.ATTRIBUTE_APPLICATION_ID
203 and a.ATTRIBUTE_CODE = b.ATTRIBUTE_CODE
204 and not exists (select 'X'
205 from AK_EXCLUDED_ITEMS
206 where RESPONSIBILITY_ID = l_responsibility_id
207 and ATTRIBUTE_CODE = a.ATTRIBUTE_CODE
208 and ATTRIBUTE_APPLICATION_ID = a.ATTRIBUTE_APPLICATION_ID)
209 order by a.DISPLAY_SEQUENCE;
210
211 cursor cat_set is
212 select category_set_id,
213 validate_flag
214 from mtl_default_sets_view
215 WHERE functional_area_id = 2;
216
217 where_clause varchar2(2000);
218 v_category_set_id number;
219 v_validate_flag varchar2(1);
220 l_category_id number;
221 l_category_name number;
222 l_values icx_util.char240_table;
223 l_parameters icx_on_utilities.v240_table;
224 l_starts_with number;
225
226 begin
227
228 /* XXXXXXXXXXXXXXXXGet condition title, prompts and conditions */
229 icx_util.getPrompts(178,'ICX_WEB_ON_QUERY',c_title,c_prompts);
230
231 if p_values is not null then
232 icx_on_utilities.unpack_parameters(icx_call.decrypt2(p_values), l_parameters);
233 for i in l_parameters.COUNT..20 loop
234 l_parameters(i) := '';
235 end loop;
236 else
237 for i in 1..20 loop
238 l_parameters(i) := '';
239 end loop;
240 end if;
241
242 c_url := p_lines_url||icx_call.encrypt2(p_region_appl_id||'*'||p_region_code||'*'||p_goto_url||'*'||p_goto_target||'*'||p_lines_next||'*'||
243 p_lines_url||'*'||p_lines_target||'*'||p_lines_now||'*'||p_hidden_name||'*'||p_hidden_value||'*'||p_help_url||'*'||p_new_url||'*'||p_LOV_mode||'*'||p_default_title||'**] NAME="'||p_lines_target||'"');
244
245 icx_util.getLookups('ICX_CONDITIONS',l_lookup_codes,l_lookup_meanings);
246
247 /* Create queryable attribute select list */
248
249 for i in 1..p_lines_now loop
250 if i = 1
251 then
252 c_attributes(i) := '';
253 else
254 c_attributes(i) := htf.formSelectOption(' ');
255 end if;
256 for f in FindAttributes loop
257 if f.DATA_TYPE = 'DATETIME'
258 then
259 l_data_type := 'T';
260 else
261 l_data_type := substr(f.DATA_TYPE,1,1);
262 end if;
263 if l_parameters(1 + ((i-1) * 3)) = l_data_type||f.COLUMN_NAME
264 then
265 c_attributes(i) := c_attributes(i)||'<OPTION SELECTED VALUE='||l_data_type||f.COLUMN_NAME||'>'||f.ATTRIBUTE_LABEL_LONG;
266 else
267 c_attributes(i) := c_attributes(i)||'<OPTION VALUE='||l_data_type||f.COLUMN_NAME||'>'||f.ATTRIBUTE_LABEL_LONG;
268 end if;
269 end loop;
270 c_attributes(i) := c_attributes(i)||htf.formSelectClose;
271 end loop;
272
273 for x in 1..p_lines_now loop
274 if x = 1
275 then
276 c_condition(x) := '';
277 else
278 c_condition(x) := htf.formSelectOption(' ');
279 end if;
280 for i in 1..to_number(l_lookup_codes(0)) loop
281 if l_lookup_codes(i) = 'DSTART'
282 then
283 l_starts_with := x;
284 end if;
285 if l_parameters(2 + ((x-1) * 3)) = l_lookup_codes(i)
286 then
287 c_condition(x) := c_condition(x)||'<OPTION SELECTED VALUE='||l_lookup_codes(i)||'>'||l_lookup_meanings(i);
288 else
289 if x = 1
290 and l_lookup_codes(i) = 'DSTART'
291 and l_parameters(2 + ((i-1) * 3)) is null
292 then
293 c_condition(x) := c_condition(x)||'<OPTION SELECTED VALUE='||l_lookup_codes(i)||'>'||l_lookup_meanings(i);
294 else
295 c_condition(x) := c_condition(x)||'<OPTION VALUE='||l_lookup_codes(i)||'>'||l_lookup_meanings(i);
296 end if;
297 end if;
298 end loop;
299 c_condition(x) := c_condition(x)||htf.formSelectClose;
300 end loop;
301
302 for i in 1..p_lines_now loop
303 if l_parameters(3 + ((i-1) * 3)) is not null
304 then
305 c_input(i) := l_parameters(3 + ((i-1) * 3));
306 else
307 c_input(i) := '';
308 end if;
309 end loop;
310
311 if p_goto_url is null
312 then
313 htp.formOpen('OracleON.IC','POST','','','NAME="findForm"');
314 else
315 htp.formOpen(p_goto_url,'POST',p_goto_target,'','NAME="findForm"');
316 end if;
317
318 if p_default_title = 'Y'
319 then
320
321 select NAME
322 into l_page_title
323 from AK_REGIONS_VL
324 where REGION_CODE = p_region_code
325 and REGION_APPLICATION_ID = p_region_appl_id;
326
327 fnd_message.set_name('ICX','ICX_FIND_TEXT');
328 fnd_message.set_token('REGION_TOKEN',l_page_title);
329 l_message := fnd_message.get;
330
331 htp.tableOpen('BORDER=0');
332 htp.tableRowOpen;
333
334 htp.tableData(cvalue => '<B><FONT size=+2>'||l_message||'</FONT></B>', cattributes => 'VALIGN="MIDDLE"');
335
336 if p_new_url is not null
337 then
338 htp.p('<TD>');
339 icx_util.DynamicButton(P_ButtonText => c_prompts(5),
340 P_ImageFileName => 'FNDBNEW',
341 P_OnMouseOverText => c_prompts(5),
342 P_HyperTextCall => p_new_url,
343 P_LanguageCode => l_language_code,
344 P_JavaScriptFlag => FALSE);
345 htp.p('</TD>');
346 end if;
347
348 htp.tableRowClose;
349 htp.tableClose;
350 htp.nl;
351
352 end if; -- p_default_title = 'Y'
353
354 if 175 = 178 then
355
356 ak_query_pkg.exec_query(P_PARENT_REGION_APPL_ID => 178,
357 P_PARENT_REGION_CODE => 'ICX_REQ_CATEGORIES',
358 P_RESPONSIBILITY_ID => icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
359 P_USER_ID => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
360 P_WHERE_CLAUSE => ' FUNCTIONAL_AREA_ID = 2',
361
362 P_RETURN_PARENTS => 'T',
363 P_RETURN_CHILDREN => 'F');
364
365 if ak_query_pkg.g_results_table.count > 0 then
366 for i in 0 .. ak_query_pkg.g_items_table.LAST loop
367 if ak_query_pkg.g_items_table(i).attribute_code = 'ICX_CATEGORY_ID'
368 then
369 l_category_id := ak_query_pkg.g_items_table(i).value_id;
370 end if;
371 if ak_query_pkg.g_items_table(i).attribute_code = 'ICX_CATEGORY_NAME'
372 then
373 l_category_name := ak_query_pkg.g_items_table(i).value_id;
374 end if;
375 if ak_query_pkg.g_items_table(i).attribute_code = 'ICX_RELATED_CATEGORY'
376 then
377 l_category_name := ak_query_pkg.g_items_table(i).value_id;
378 end if;
379 end loop;
380
381 htp.p('Item Category is');
382 l_categories := htf.formSelectOpen('p_cat');
383 if l_parameters(16) is null then
384 l_categories := l_categories||'<OPTION SELECTED VALUE="">All';
385 else
386 l_categories := l_categories||'<OPTION VALUE="">All';
387 end if;
388 for i in 0 .. ak_query_pkg.g_results_table.last loop
389 icx_util.transfer_Row_To_Column(ak_query_pkg.g_results_table(i),l_values);
390 if l_parameters(16) = l_values(l_category_id) then
391 l_categories := l_categories||'<OPTION SELECTED VALUE='||l_values(l_category_id)||'>'||l_values(l_category_name);
392 else
393 l_categories := l_categories||'<OPTION VALUE='||l_values(l_category_id)||'>'||l_values(l_category_name);
394 end if;
395 end loop;
396 l_categories := l_categories||htf.formSelectClose;
397 htp.p(l_categories);
398 end if; -- no rows
399 end if; -- 175
400
401 for i in 1..p_lines_now loop
402
403 htp.tableOpen('BORDER=0');
404 htp.tableRowOpen;
405 htp.tableData(htf.formSelectOpen('a_'||i)||c_attributes(i));
406 htp.tableData(htf.formSelectOpen('c_'||i)||c_condition(i));
407 htp.tableData(htf.formText('i_'||i,20,80,c_input(i)));
408 htp.tableRowClose;
409 htp.tableClose;
410
411 end loop;
412
413 if p_hidden_name is not null
414 then
415 htp.formHidden(p_hidden_name,p_hidden_value);
416 end if;
417
418 if p_LOV_mode = 'N'
419 then
420 htp.nl;
421 htp.tableOpen('BORDER=0');
422 htp.tableRowOpen;
423 findIcons(c_prompts(1),c_prompts(2),c_prompts(4),c_prompts(3),p_lines_next,p_lines_now,c_url,l_language_code);
424 htp.tableRowClose;
425 htp.tableClose;
426 end if;
427
428 htp.p('</FORM>');
429
430 exception
431 when others then
432 htp.p(SQLERRM);
433 end;
434
435 -------------------------------------------------------------------
436 procedure itemsearch( n_org number) is
437 -------------------------------------------------------------------
438
439
440 v_dcdName varchar2(1000);
441 v_lang varchar2(5);
442
443 begin
444
445 v_dcdName := owa_util.get_cgi_env('SCRIPT_NAME');
446
447 -- get lang code
448 v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
449
450
451 -- We need to split into 2 frames
452
453 js.scriptOpen;
454 htp.p('function openButWin(start_row, end_row, total_row, where, context) {
455
456 var result = "' || v_dcdName ||
457 '/ICX_REQ_SEARCH.itemsearch_buttons?p_start_row=" + start_row + "&p_end_row=" + end_row + "&p_total_rows=" +
458 total_row + "&p_where=" + where + "&p_context=" + context;
459 open(result, ''k_buttons'');
460 }
461 ');
462
463 js.scriptClose;
464
465 htp.p('<FRAMESET ROWS="*,40" BORDER=0>');
466 htp.p('<FRAME SRC="' || v_dcdName ||
467 '/ICX_REQ_SEARCH.itemsearch_display' ||
468 /*?searchX=' ||
469 searchX || '¶mX=' || paramX || */
470 '" NAME="data" FRAMEBORDER=NO MARGINWIDTH=10 MARGINHEIGHT=0 NORESIZE>');
471
472 htp.p('<FRAME NAME="k_buttons" SRC="/OA_HTML/' ||
473 v_lang || '/ICXBLUE.htm" FRAMEBORDER=NO MARGINWIDTH=0 MARGINHEIGHT=0 NORESIZE SCROLLING="NO">');
474 htp.p('</FRAMESET>');
475
476 exception
477 when others then
478 htp.p(SQLERRM);
479
480 end;
481
482 -------------------------------------------------------------------
483 procedure itemsearch_buttons(p_start_row in number default 1,
484 p_end_row in number default null,
485 p_total_rows in number,
486 p_where in number,
487 p_context in varchar2 default 'Y') is
488 -------------------------------------------------------------------
489
490 v_lang varchar2(30);
491 c_query_size number;
492
493 begin
494
495 SELECT QUERY_SET INTO c_query_size FROM ICX_PARAMETERS;
496
497 v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
498 htp.p('<BODY BGCOLOR="#CCCCFF">');
499
500 htp.p('<TABLE BORDER=0>');
501 htp.p('<TD>');
502
503 if (p_context = 'N') then
504 icx_on_utilities2.displaySetIcons(p_language_code => v_lang,
505 p_packproc => 'ICX_REQ_SEARCH.displayItem',
506 p_start_row => p_start_row,
507 p_stop_row => p_end_row,
508 p_encrypted_where => p_where,
509 p_query_set => c_query_size,
510 p_target => 'parent.frames[0]',
511 p_row_count => p_total_rows,
512 p_hidden => 'N');
513 else
514 icx_on_utilities2.displaySetIcons(p_language_code => v_lang,
515 p_packproc => 'ICX_REQ_SEARCH.displayItem',
516 p_start_row => p_start_row,
517 p_stop_row => p_end_row,
518 p_encrypted_where => p_where,
519 p_query_set => c_query_size,
520 p_target => 'parent.frames[0]',
521 p_row_count => p_total_rows);
522 end if;
523
524 -- htp.p('Debug 0');
525 htp.p('</TD>');
526 htp.p('<TD width=1000></TD><TD>');
527 FND_MESSAGE.SET_NAME('ICX','ICX_ADD_TO_ORDER');
528 icx_util.DynamicButton(P_ButtonText => FND_MESSAGE.GET,
529 P_ImageFileName => 'FNDBNEW.gif',
530 P_OnMouseOverText => FND_MESSAGE.GET,
531 P_HyperTextCall => 'javascript:parent.frames[0].submit()',
532 P_LanguageCode => v_lang,
533 P_JavaScriptFlag => FALSE);
534
535 htp.p('</TD></TABLE>');
536 htp.p('</BODY>');
537
538 exception
539 when others then
540 htp.p(SQLERRM);
541
542
543 end;
544
545
546 -------------------------------------------------------------------
547 procedure itemsearch_display( searchX in varchar2 default null,
548 paramX in varchar2 default null ) is
549 -------------------------------------------------------------------
550
551 v_lines_url varchar2(256);
552 l_search icx_on_utilities.v240_table;
553 l_param icx_on_utilities.v240_table;
554 v_lines_now number;
555 v_lines_next number;
556 l_paramX number;
557 l_language varchar2(30);
558
559 begin
560 -- dbms_session.set_sql_trace(TRUE);
561
562
563 --Check if session is valid
564 if (icx_sec.validatesession('ICX_REQS')) then
565 l_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
566
567 l_paramX := paramX;
568 v_lines_url := 'ICX_REQ_SEARCH.itemsearch_display?searchX=';
569 if paramX is not null then
570 icx_on_utilities.unpack_parameters(icx_call.decrypt2(paramX), l_param);
571 if l_param(4) is null
572 and l_param(7) is null
573 and l_param(10) is null
574 and l_param(13) is null then
575 v_lines_now := 1;
576 v_lines_next := 5;
577 else
578 v_lines_now := 5;
579 v_lines_next := 1;
580 end if;
581 else
582 v_lines_now := 1;
583 v_lines_next := 5;
584 end if;
585 if searchX is not null then
586 icx_on_utilities.unpack_parameters(icx_call.decrypt2(searchX), l_search);
587 v_lines_now := l_search(5); -- p_lines_next
588 v_lines_next := l_search(8); -- p_lines_now
589 l_paramX := l_search(10); -- p_hidden_value
590 end if;
591 htp.htmlOpen;
592 htp.headOpen;
593 icx_util.copyright;
594 js.scriptOpen;
595 find_form_head_region(v_lines_now);
596 js.scriptClose;
597 htp.headClose;
598 htp.bodyOpen('', 'BGCOLOR="#CCCCFF" onLoad="parent.parent.winOpen(''nav'', ''item_search''); open(''/OA_HTML/' || l_language || '/ICXBLUE.htm'', ''k_buttons'')"' );
599
600 fnd_message.set_name('ICX','ICX_REQS_FIND');
601 htp.p(htf.bold(FND_MESSAGE.GET));
602 icx_on_utilities.findForm(p_region_appl_id => 601,
603 p_region_code => 'ICX_PO_SUPPL_SEARCH_ITEMS_R',
604 p_goto_url => 'ICX_REQ_SEARCH.displayItem',
605 p_goto_target => 'data',
606 p_lines_now => v_lines_now,
607 p_lines_url => v_lines_url,
608 p_lines_target => 'data',
609 p_lines_next => v_lines_next,
610 p_hidden_name => 'p_values',
611 p_hidden_value => paramX,
612 p_default_title => 'N');
613 -- p_values => l_paramX );
614 htp.bodyClose;
615 htp.htmlClose;
616 end if;
617
618 -- dbms_session.set_sql_trace(FALSE);
619
620
621 exception
622 when OTHERS then
623 htp.p('searchX = ' || searchX || '<BR>');
624 htp.p('paramX = ' || paramX || '<BR>');
625 htp.p(SQLERRM);
626 end;
627
628
629 -------------------------------------------------------------------
630 procedure total_page(l_cart_id number,
631 l_dest_org_id number,
632 l_rows_added number default 0,
633 l_rows_updated number default 0,
634 a_1 in varchar2 default null,
635 c_1 in varchar2 default null,
636 i_1 in varchar2 default null,
637 a_2 in varchar2 default null,
638 c_2 in varchar2 default null,
639 i_2 in varchar2 default null,
640 a_3 in varchar2 default null,
641 c_3 in varchar2 default null,
642 i_3 in varchar2 default null,
643 a_4 in varchar2 default null,
644 c_4 in varchar2 default null,
645 i_4 in varchar2 default null,
646 a_5 in varchar2 default null,
647 c_5 in varchar2 default null,
648 i_5 in varchar2 default null,
649 p_start_row IN NUMBER default 1,
650 p_end_row IN NUMBER default null,
651 p_where IN varchar2,
652 p_hidden IN varchar2 default null,
653 end_row in number default null,
654 p_query_set in number default null,
655 p_row_count in number default null) is
656 -------------------------------------------------------------------
657 l_message varchar2(2000);
658 l_messg varchar2(2000);
659 l_href1 varchar2(2000);
660 l_href2 varchar2(2000);
661 next_start_row number;
662 next_end_row number;
663 v_dcdName varchar2(1000) := owa_util.get_cgi_env('SCRIPT_NAME');
664
665 l_return_to_next_message varchar2(200);
666
667 l_total_price number;
668 l_currency varchar2(30);
669 l_fmt_mask varchar2(30);
670 l_money_precision number;
671
672 cursor get_current_total(v_cart_id number) is
673 select sum(quantity * unit_price) total_price
674 from icx_shopping_cart_lines
675 where cart_id = v_cart_id;
676
677 begin
678 l_total_price := 0;
679 if l_cart_id is not NULL then
680 open get_current_total(l_cart_id);
681 fetch get_current_total into l_total_price;
682 close get_current_total;
683 end if;
684
685 FND_MESSAGE.SET_NAME('ICX','ICX_ITEM_ADD_NEW');
686 FND_MESSAGE.SET_TOKEN('ITEM_QUANTITY',l_rows_added);
687 l_message := FND_MESSAGE.GET;
688 if l_rows_updated > 0 then
689 FND_MESSAGE.SET_NAME('ICX','ICX_ITEM_ADD_UPDATE');
690 FND_MESSAGE.SET_TOKEN('ITEM_QUANTITY',l_rows_updated);
691 if l_rows_added > 0 then
692 l_message := l_message || '<BR>' || FND_MESSAGE.GET;
693 else
694 l_message := FND_MESSAGE.GET;
695 end if;
696 end if;
697
698 if l_total_price > 0 then
699 ICX_REQ_NAVIGATION.get_currency(l_dest_org_id,l_currency,l_money_precision,l_fmt_mask);
700 FND_MESSAGE.SET_NAME('ICX','ICX_ITEM_ADD_TOTAL');
701 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE',l_currency);
702 FND_MESSAGE.SET_TOKEN('REQUISITION_TOTAL',to_char(l_total_price,fnd_currency.get_format_mask(l_currency,30)));
703 l_message := l_message || '<BR><BR>' || FND_MESSAGE.GET;
704
705 FND_MESSAGE.SET_NAME('ICX','ICX_ITEM_ADD_RETURN_CURRENT');
706 l_href1 := FND_MESSAGE.GET;
707 FND_MESSAGE.SET_NAME('ICX','ICX_ITEM_ADD_RETURN_NEXT');
708 l_href2 := FND_MESSAGE.GET;
709 l_messg := '<TABLE BORDER=0><TR><TD><BR></TD><TD><BR></TD><TD><BR></TD><TD NOWRAP>' || '<B><A HREF="' || v_dcdName || '/ICX_REQ_SEARCH.displayItem?a_1=' || a_1 ||
710 '&c_1=' || c_1 || '&i_1=' || i_1 ||
711 '&a_2=' || a_2 || '&c_2=' || c_2 ||'&i_2=' || i_2 ||
712 '&a_3=' || a_3 || '&c_3=' || c_3 ||'&i_3=' || i_3 ||
713 '&a_4=' || a_4 || '&c_4=' || c_4 ||'&i_4=' || i_4 ||
714 '&a_5=' || a_5 || '&c_5=' || c_5 ||'&i_5=' || i_5 ||
715 '&p_start_row=' || p_start_row || '&p_end_row=' || p_end_row || '&p_where=' || p_where || '&p_hidden=' || p_hidden || '">' || l_href1 || '</A></B></TD></TR>';
716
717 /* find next set start row and next set end row */
718 if end_row < p_row_count
719 and p_query_set is not NULL then
720
721 next_start_row := end_row+1;
722 if end_row+p_query_set > p_row_count then
723 next_end_row := p_row_count;
724 else
725 next_end_row := end_row+p_query_set;
726 end if;
727
728 l_messg := l_messg || '<TR><TD><BR></TD><TD><BR></TD><TD><BR></TD><TD NOWRAP>' || '<B><A HREF="' || v_dcdName || '/ICX_REQ_SEARCH.displayItem?a_1=' || a_1 ||
729 '&c_1=' || c_1 || '&i_1=' || i_1 ||
730 '&a_2=' || a_2 || '&c_2=' || c_2 ||'&i_2=' || i_2 ||
731 '&a_3=' || a_3 || '&c_3=' || c_3 ||'&i_3=' || i_3 ||
732 '&a_4=' || a_4 || '&c_4=' || c_4 ||'&i_4=' || i_4 ||
733 '&a_5=' || a_5 || '&c_5=' || c_5 ||'&i_5=' || i_5 ||
734 '&p_start_row=' || next_start_row || '&p_end_row=' || next_end_row || '&p_where=' || p_where || '&p_hidden=' || p_hidden || '">' || l_href2 || '</A></B></TD></TR>';
735
736 end if;
737
738 -- MESSAGE NEEDS TO BE SWITCHED TO REVIEW MY ORDER
739 FND_MESSAGE.SET_NAME('ICX','ICX_REVIEW_ORDER');
740 l_return_to_next_message := FND_MESSAGE.GET;
741 l_messg := l_messg || '<TR><TD><BR></TD><TD><BR></TD><TD><BR></TD><TD NOWRAP>' || '<B><A HREF="javascript:parent.parent.parent.switchFrames(''my_order'')" >' || l_return_to_next_message || '</A></B></TD></TR>';
742
743
744 l_messg := l_messg || '</TABLE>';
745 l_message := l_message || l_messg;
746 end if;
747
748 ICX_REQ_SEARCH.itemsearch_display;
749
750 htp.bodyOpen('', 'BGCOLOR="#CCCCFF" onLoad="parent.parent.winOpen(''nav'', ''item_search'')" target="data"' );
751 htp.br;
752 htp.p('<H3>'|| l_message ||'</H3>');
753 htp.bodyclose;
754 end;
755
756 --------------------------------------------------------------
757 procedure submit_items (cartId IN NUMBER,
758 p_emergency in number default null,
759 a_1 in varchar2 default null,
760 c_1 in varchar2 default null,
761 i_1 in varchar2 default null,
762 a_2 in varchar2 default null,
763 c_2 in varchar2 default null,
764 i_2 in varchar2 default null,
765 a_3 in varchar2 default null,
766 c_3 in varchar2 default null,
767 i_3 in varchar2 default null,
768 a_4 in varchar2 default null,
769 c_4 in varchar2 default null,
770 i_4 in varchar2 default null,
771 a_5 in varchar2 default null,
772 c_5 in varchar2 default null,
773 i_5 in varchar2 default null,
774 p_start_row IN NUMBER default 1,
775 p_end_row IN NUMBER default null,
776 p_where IN varchar2,
777 p_hidden IN varchar2 default null,
778 end_row IN number default null,
779 p_query_set IN number default null,
780 p_row_count IN number default null,
781 Quantity IN ICX_OWA_PARMS.ARRAY default ICX_OWA_PARMS.empty,
782 Line_Id IN ICX_OWA_PARMS.ARRAY default ICX_OWA_PARMS.empty) is
783 --------------------------------------------------------------
784
785 l_line_id number;
786 l_num_rows number;
787 l_cart_line_id number;
788 l_shopper_id number;
789 l_org_id number;
790 params icx_on_utilities.v80_table;
791 l_qty number;
792 l_error_id NUMBER;
793 l_err_num NUMBER;
794 l_error_message VARCHAR2(2000);
795 l_err_mesg VARCHAR2(240);
796 l_need_by_date date;
797 l_deliver_to_location_id number;
798 l_deliver_to_location varchar2(240);
799 l_dest_org_id number;
800 l_rows_added number;
801 l_rows_updated number;
802 l_cart_id number;
803 l_emergency varchar2(10);
804 l_cart_line_number number;
805 l_dummy number;
806 l_pad number;
807
808 cursor check_cart_line_exists(v_cart_id number,v_line_id varchar2,v_org_id number) is
809 select cart_line_id
810 from icx_shopping_cart_lines
811 where cart_id = v_cart_id
812 and line_id = v_line_id
813 and nvl(org_id, -9999) = nvl(v_org_id,-9999);
814
815 cursor get_cart_header_info(v_cart_id number) is
816 select need_by_date,
817 deliver_to_requestor_id,
818 deliver_to_location_id,
819 destination_organization_id,
820 deliver_to_location,
821 org_id
822 from icx_shopping_carts
823 where cart_id = v_cart_id;
824 -- and nvl(org_id,-9999) = nvl(v_org_id,-9999);
825
826
827 cursor get_max_line_number(v_cart_id number) is
828 select max(cart_line_number)
829 from icx_shopping_cart_lines
830 where cart_id = v_cart_id;
831
832 l_emp_id number;
833 l_account_id NUMBER := NULL;
834 l_account_num VARCHAR2(2000) := NULL;
835 l_segments fnd_flex_ext.SegmentArray;
836
837 begin
838
839 if icx_sec.validatesession then
840
841 l_num_rows := Quantity.COUNT;
842 l_shopper_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
843 -- l_org_id := icx_sec.getId(icx_sec.PV_ORG_ID);
844 -- if p_where is not NULL then
845 -- icx_on_utilities.unpack_parameters(icx_call.decrypt2(p_where),params);
846 -- l_org_id := params(2);
847 -- end if;
848 l_cart_id := icx_call.decrypt2(cartId);
849 l_emergency := icx_call.decrypt2(p_emergency);
850 l_rows_added := 0;
851 l_rows_updated := 0;
852
853 if l_cart_id is not NULL then
854 open get_cart_header_info(l_cart_id);
855 fetch get_cart_header_info into l_need_by_date, l_emp_id,
856 l_deliver_to_location_id,l_dest_org_id,l_deliver_to_location,l_org_id;
857 close get_cart_header_info;
858
859 end if;
860
861 for i in Quantity.FIRST .. Quantity.LAST loop
862
863 l_pad := instr(Quantity(i),'.',1,2);
864 if (l_pad > 2) then
865 l_qty := substr(Quantity(i),1,l_pad - 1);
866 elsif (l_pad > 0) then
867 l_qty := 0;
868 else
869 l_qty := Quantity(i);
870 end if;
871
872 if Quantity(i) is NOT NULL and l_qty > 0 then
873
874 l_cart_line_id := NULL;
875 open check_cart_line_exists(l_cart_id,Line_Id(i),l_org_id);
876 fetch check_cart_line_exists into l_cart_line_id;
877 close check_cart_line_exists;
878
879 if l_cart_line_id is NULL then
880
881 l_line_id := Line_Id(i);
882
883 /* semaphore for getting max line number */
884 select 1 into l_dummy
885 from icx_shopping_carts
886 where cart_id = l_cart_id
887 for update;
888
889 l_cart_line_number := NULL;
890 open get_max_line_number(l_cart_id);
891 fetch get_max_line_number into l_cart_line_number;
892 close get_max_line_number;
893
894 if l_cart_line_number is NULL then
895 l_cart_line_number := 1;
896 else
897 l_cart_line_number := l_cart_line_number + 1;
898 end if;
899
900 update icx_shopping_carts
901 set last_update_date = sysdate
902 where cart_id = l_cart_id;
903
904 commit;
905 /* release semaphore */
906 --changed by alex for attachment
907 -- select icx_shopping_cart_lines_s.nextval into l_cart_line_id from dual;
908 --new code:
909 select PO_REQUISITION_LINES_S.nextval into l_cart_line_id from dual;
910
911 insert into icx_shopping_cart_lines(cart_line_id,cart_id,cart_line_number,creation_date,created_by,quantity,line_id,item_id,item_revision,unit_of_measure,
912 unit_price,category_id,line_type_id,item_description,destination_organization_id,deliver_to_location_id,deliver_to_location,
913 suggested_buyer_id,suggested_vendor_name,suggested_vendor_site,
914 need_by_date,suggested_vendor_contact,
915 suggested_vendor_item_num,item_number,last_update_date,last_updated_by,org_id,custom_defaulted, autosource_doc_header_id, autosource_doc_line_num)
916 select l_cart_line_id,l_cart_id,l_cart_line_number,sysdate,l_shopper_id,l_qty,Line_Id(i),a.item_id,a.item_revision,a.line_uom,
917 a.price,a.category_id,a.line_type_id,a.item_description,l_dest_org_id,
918 /* l_deliver_to_location_id,l_deliver_to_location,a.vendor_id,a.vendor_name,a.vendor_site_code, **/
919 l_deliver_to_location_id,l_deliver_to_location,a.agent_id,a.vendor_name,a.vendor_site_code,
920 l_need_by_date,a.vendor_contact_name,
921 a.vendor_product_num,a.item_number,sysdate,l_shopper_id,l_org_id,'N',
922 a.po_header_id, a.line_num
923 from icx_po_suppl_catalog_items_v a
924 where a.po_line_id = l_line_id;
925
926 -- Get the default accounts and update distributions
927 icx_req_acct2.get_default_account(l_cart_id,l_cart_line_id,
928 l_emp_id,l_org_id,l_account_id,l_account_num);
929
930 commit;
931
932
933 l_rows_added := l_rows_added + 1;
934
935 else
936
937 l_qty := to_number(Quantity(i));
938
939 update icx_shopping_cart_lines
940 set quantity = quantity + l_qty
941 where cart_id = l_cart_id
942 and cart_line_id = l_cart_line_id
943 and nvl(org_id, -9999) = nvl(l_org_id,-9999);
944
945 commit;
946
947 l_rows_updated := l_rows_updated + 1;
948
949 end if;
950
951 end if;
952
953 end loop;
954
955 /* call user custom default lines */
956 if l_emergency is not NULL and l_emergency = 'YES' then
957 icx_req_custom.reqs_default_lines(l_emergency,l_cart_id);
958 else
959 icx_req_custom.reqs_default_lines('NO',l_cart_id);
960 end if;
961
962 total_page(l_cart_id,l_dest_org_id,l_rows_added,l_rows_updated,a_1,c_1,
963 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,
964 p_start_row,p_end_row,p_where,p_hidden, end_row, p_query_set,p_row_count);
965 /*
966 displayItem(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,
967 p_start_row,p_end_row,p_where,l_rows_added);
968 */
969
970 end if;
971
972 exception
973 when others then
974 l_err_num := SQLCODE;
975 l_error_message := SQLERRM;
976
977 select substr(l_error_message,12,512) into l_err_mesg from dual;
978 icx_util.add_error(l_err_mesg);
979 icx_util.error_page_print;
980
981
982 end;
983
984 -----------------------------------------------------------
985 procedure displayItem(a_1 in varchar2 default null,
986 c_1 in varchar2 default null,
987 i_1 in varchar2 default null,
988 a_2 in varchar2 default null,
989 c_2 in varchar2 default null,
990 i_2 in varchar2 default null,
991 a_3 in varchar2 default null,
992 c_3 in varchar2 default null,
993 i_3 in varchar2 default null,
994 a_4 in varchar2 default null,
995 c_4 in varchar2 default null,
996 i_4 in varchar2 default null,
997 a_5 in varchar2 default null,
998 c_5 in varchar2 default null,
999 i_5 in varchar2 default null,
1000 p_start_row in number default 1,
1001 p_end_row in number default null,
1002 p_where in varchar2 default null,
1003 p_cat in varchar2 default null,
1004 p_values in number default null,
1005 m in varchar2 default null ,
1006 o in varchar2 default null,
1007 p_hidden in varchar2 default null) is
1008 -----------------------------------------------------------
1009 c_language varchar2(30);
1010 c_title varchar2(80) := '';
1011 c_prompts icx_util.g_prompts_table;
1012 where_clause varchar2(2000);
1013 total_rows number;
1014 end_row number;
1015 temp_table icx_admin_sig.pp_table;
1016 empty_table icx_admin_sig.pp_table;
1017 c_query_size number;
1018 i number := 0;
1019 j number := 0;
1020 display_text varchar2(5000);
1021 shopper_id number;
1022 v_location_id number;
1023 v_location_code varchar2(20);
1024 v_org_id number;
1025 v_org_code varchar2(3);
1026 employee_id number;
1027 shopper_name varchar(240);
1028 v_line_id varchar2(65);
1029 v_line_id_ind number;
1030 v_item_url varchar2(150);
1031 v_supplier_url varchar2(150);
1032 v_return_status varchar2(20);
1033 v_num_table icx_sec.g_num_tbl_type;
1034 counter number := 0;
1035 v_quantity_length number :=10;
1036 v_temp varchar2(240);
1037 v_qty_flag boolean := false;
1038 y_table icx_util.char240_table;
1039 parameters_pass varchar2(20);
1040 a_1_code varchar2(80);
1041 a_2_code varchar2(80);
1042 a_3_code varchar2(80);
1043 a_4_code varchar2(80);
1044 a_5_code varchar2(80);
1045 c_1_code varchar2(80);
1046 c_2_code varchar2(80);
1047 c_3_code varchar2(80);
1048 c_4_code varchar2(80);
1049 c_5_code varchar2(80);
1050 i_1_code varchar2(80);
1051 i_2_code varchar2(80);
1052 i_3_code varchar2(80);
1053 i_4_code varchar2(80);
1054 i_5_code varchar2(80);
1055 l_cat number;
1056 Y varchar2(2000);
1057 params icx_on_utilities.v80_table;
1058 c_currency varchar2(15);
1059 c_money_precision number;
1060 c_money_fmt_mask varchar2(32);
1061
1062 v_supplier_url_ind number;
1063 v_supplier_item_url_ind number;
1064 v_item_url_ind number;
1065 v_supplier_item_url varchar2(150);
1066 v_dcdName varchar2(1000);
1067 g_reg_ind number;
1068 l_pos number := 0;
1069 l_spin_pos number := 0;
1070
1071 v_lines_now number;
1072 v_lines_next number;
1073 v_lines_url varchar2(100);
1074
1075 v_use_context_search CHAR(1) := 'N';
1076
1077 l_where_clause varchar2(2000);
1078 where_clause_binds ak_query_pkg.bind_tab;
1079 v_index NUMBER;
1080
1081 begin
1082 -- dbms_session.set_sql_trace(TRUE);
1083
1084 if icx_sec.validateSession('ICX_REQS') then
1085
1086 c_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
1087 shopper_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
1088 v_dcdName := owa_util.get_cgi_env('SCRIPT_NAME');
1089
1090 employee_id := icx_sec.getID(icx_sec.PV_INT_CONTACT_ID);
1091
1092 --Get the org id from shopper_id
1093 icx_req_navigation.shopper_info(employee_id, shopper_name, v_location_id, v_location_code, v_org_id, v_org_code);
1094
1095 ICX_REQ_NAVIGATION.get_currency(v_org_id, c_currency, c_money_precision, c_money_fmt_mask);
1096
1097 icx_util.getPrompts(601,'ICX_PO_SUPPL_SEARCH_ITEMS_R',c_title,c_prompts);
1098 icx_util.error_page_setup;
1099 if p_where is not null then
1100 Y:=icx_call.decrypt2(p_where);
1101 icx_on_utilities.unpack_parameters(Y,params);
1102 a_1_code := params(1);
1103 c_1_code := params(2);
1104 i_1_code := params(3);
1105 a_2_code := params(4);
1106 c_2_code := params(5);
1107 i_2_code := params(6);
1108 a_3_code := params(7);
1109 c_3_code := params(8);
1110 i_3_code := params(9);
1111 a_4_code := params(10);
1112 c_4_code := params(11);
1113 i_4_code := params(12);
1114 a_5_code := params(13);
1115 c_5_code := params(14);
1116 i_5_code := params(15);
1117 l_cat := params(16);
1118 where_clause := icx_on_utilities.whereSegment(a_1_code,c_1_code,i_1_code,
1119 a_2_code,c_2_code,i_2_code,
1120 a_3_code,c_3_code,i_3_code,
1121 a_4_code,c_4_code,i_4_code,
1122 a_5_code,c_5_code,i_5_code,
1123 m,o);
1124 parameters_pass := icx_call.encrypt2(a_1_code||'*'||c_1_code||'*'||i_1_code||'*'||
1125 a_2_code||'*'||c_2_code||'*'||i_2_code||'*'||
1126 a_3_code||'*'||c_3_code||'*'||i_3_code||'*'||
1127 a_4_code||'*'||c_4_code||'*'||i_4_code||'*'||
1128 a_5_code||'*'||c_5_code||'*'||i_5_code||'*'||l_cat||'**]');
1129
1130 else
1131 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,m,o);
1132 l_cat := p_cat;
1133 parameters_pass := icx_call.encrypt2(a_1||'*'||c_1||'*'||i_1||'*'||
1134 a_2||'*'||c_2||'*'||i_2||'*'||
1135 a_3||'*'||c_3||'*'||i_3||'*'||
1136 a_4||'*'||c_4||'*'||i_4||'*'||
1137 a_5||'*'||c_5||'*'||i_5||'*'||l_cat||'**]');
1138
1139 end if;
1140
1141 v_lines_url := 'ICX_REQ_SEARCH.itemsearch_display?searchX=';
1142
1143 if a_2 is null
1144 and a_3 is null
1145 and a_4 is null
1146 and a_5 is null then
1147 v_lines_now := 1;
1148 v_lines_next := 5;
1149 else
1150 v_lines_now := 5;
1151 v_lines_next := 1;
1152 end if;
1153
1154
1155 htp.htmlOpen;
1156 htp.headOpen;
1157 icx_util.copyright;
1158 js.scriptOpen;
1159 find_form_head_region(v_lines_now);
1160
1161 icx_on_utilities.unpack_whereSegment(where_clause,l_where_clause,where_clause_binds);
1162
1163 if (l_where_clause is not null ) then
1164 -- l_where_clause := l_where_clause || ' AND ';
1165 if (o is null and p_hidden is null) then
1166 v_use_context_search := 'Y';
1167 end if;
1168 end if;
1169 if l_cat is not null then
1170 -- where_clause := where_clause || 'CATEGORY_ID = '|| l_cat ||' AND ';
1171 l_where_clause := l_where_clause || ' AND CATEGORY_ID = :cat_id';
1172 v_index := where_clause_binds.COUNT;
1173 where_clause_binds(v_index).name := 'cat_id';
1174 where_clause_binds(v_index).value := l_cat;
1175 end if;
1176 /* commented out the code to take care of bug 724529 ***/
1177 -- where_clause := where_clause || 'ORGANIZATION_ID = ' || v_org_id;
1178 -- l_where_clause := l_where_clause || ' AND ORGANIZATION_ID = :org_id';
1179 -- v_index := where_clause_binds.COUNT;
1180 -- where_clause_binds(v_index).name := 'org_id';
1181 -- where_clause_binds(v_index).value := v_org_id;
1182
1183 --get number of rows to display
1184 select QUERY_SET into c_query_size from ICX_PARAMETERS;
1185 --set up end rows to display, since end rows
1186 if p_end_row is null then
1187 end_row := c_query_size;
1188 else
1189 end_row := p_end_row;
1190 end if;
1191
1192 /* added an extra if condition to use old region if the where clause
1193 is null(v_use_context_search = 'N'). This will prevent context from
1194 returning huge number of rows when a blind query is entered */
1195 IF (v_use_context_search = 'N' ) THEN
1196
1197 ak_query_pkg.exec_query(P_PARENT_REGION_APPL_ID => 601,
1198 P_PARENT_REGION_CODE => 'ICX_PO_SUPPL_CATALOG_ITEMS_R',
1199 P_WHERE_CLAUSE => l_where_clause,
1200 P_WHERE_BINDS => where_clause_binds,
1201 P_RESPONSIBILITY_ID => icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
1202 P_USER_ID => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
1203 P_RETURN_PARENTS => 'T',
1204 P_RETURN_CHILDREN => 'F',
1205 P_RANGE_LOW => p_start_row,
1206 P_RANGE_HIGH => end_row );
1207 ELSE
1208 -- where condition is used; use the context region
1209
1210 ak_query_pkg.exec_query(P_PARENT_REGION_APPL_ID => 601,
1211 P_PARENT_REGION_CODE => 'ICX_PO_SUPPL_SEARCH_ITEMS_R',
1212 P_WHERE_CLAUSE => l_where_clause,
1213 P_WHERE_BINDS => where_clause_binds,
1214 P_RESPONSIBILITY_ID => icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
1215 P_USER_ID => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
1216 P_RETURN_PARENTS => 'T',
1217 P_RETURN_CHILDREN => 'F',
1218 P_RANGE_LOW => p_start_row,
1219 P_RANGE_HIGH => end_row );
1220
1221 END IF; /* if (where_caluse is null) */
1222
1223 --get number of rows to display
1224 g_reg_ind := ak_query_pkg.g_regions_table.FIRST;
1225 total_rows := ak_query_pkg.g_regions_table(g_reg_ind).total_result_count;
1226 if end_row > total_rows then
1227 end_row := total_rows;
1228 end if;
1229 htp.p( 'tr=' || total_rows);
1230
1231 if ak_query_pkg.g_results_table.COUNT = 0 then
1232 js.scriptClose;
1233 htp.bodyOpen('', 'BGCOLOR="#CCCCFF" onLoad="parent.parent.winOpen(''nav'', ''item_search''); open(''/OA_HTML/'
1234 || c_language || '/ICXBLUE.htm'', ''k_buttons'')" target="data"' );
1235
1236 fnd_message.set_name('ICX','ICX_REQS_FIND');
1237 htp.p(htf.bold(FND_MESSAGE.GET));
1238 -- htp.p('Debug findForm');
1239 icx_on_utilities.findForm(p_region_appl_id => 601,
1240 p_region_code => 'ICX_PO_SUPPL_SEARCH_ITEMS_R',
1241 p_goto_url => 'ICX_REQ_SEARCH.displayItem',
1242 p_goto_target => 'data',
1243 p_lines_now => v_lines_now,
1244 p_lines_url => v_lines_url,
1245 p_lines_target => 'data',
1246 p_lines_next => v_lines_next,
1247 p_hidden_name => 'p_values',
1248 p_hidden_value => parameters_pass,
1249 p_default_title => 'N');
1250 -- p_values => parameters_pass);
1251 htp.br;
1252 fnd_message.set_name('ICX','ICX_NO_RECORDS_FOUND');
1253 fnd_message.set_token('NAME_OF_REGION_TOKEN',c_title);
1254 htp.p('<H3>'||fnd_message.get||'</H3>');
1255 --parent.frames[1].src = ''/OA_HTML/'
1256 -- || c_language || '/ICXBLUE.htm'' htp.bodyclose;
1257 return;
1258 end if;
1259
1260 icx_admin_sig.help_win_script;
1261 htp.p('function submit() {
1262 open("/OA_HTML/' || c_language ||
1263 '/ICXBLUE.htm", "k_buttons");
1264 document.catalog_items.cartId.value = parent.parent.cartId;
1265 document.catalog_items.p_emergency.value = parent.parent.emergency;
1266 document.catalog_items.submit();
1267 }');
1268
1269 htp.p ('function get_parent_values(cartid,emerg) {
1270 cartid.value=parent.parent.cartId;
1271 emerg.value=parent.parent.emergency;
1272 }');
1273
1274
1275 htp.p('function item_rows(start_num, end_num, param_id) {
1276 document.DISPLAY_ITEM.start_row.value = start_num
1277 document.DISPLAY_ITEM.c_end_row.value = end_num
1278 document.DISPLAY_ITEM.parameters_id.value = param_id
1279 document.DISPLAY_ITEM.submit()
1280 }
1281 ');
1282
1283 temp_table := empty_table;
1284 -- counter := 0;
1285 counter := 1;
1286
1287
1288 js.scriptClose;
1289 htp.headClose;
1290
1291 htp.bodyOpen('', 'BGCOLOR="#CCCCFF" onLoad="parent.parent.winOpen(''nav'', ''item_search'');parent.openButWin(' || p_start_row || ',' ||
1292 end_row || ',' || total_rows || ',' || parameters_pass || ',''' || v_use_context_search || ''')"');
1293
1294 fnd_message.set_name('ICX','ICX_REQS_FIND');
1295 htp.p(htf.bold(FND_MESSAGE.GET));
1296
1297 icx_on_utilities.findForm(p_region_appl_id => 601,
1298 p_region_code => 'ICX_PO_SUPPL_SEARCH_ITEMS_R',
1299 p_goto_url => 'ICX_REQ_SEARCH.displayItem',
1300 p_goto_target => 'data',
1301 p_lines_now => v_lines_now,
1302 p_lines_url => v_lines_url,
1303 p_lines_target => 'data',
1304 p_lines_next => v_lines_next,
1305 p_hidden_name => 'p_values',
1306 p_hidden_value => parameters_pass,
1307 p_default_title => 'N');
1308 -- p_values => parameters_pass);
1309 htp.br;
1310 v_qty_flag := true;
1311
1312 -- counter := 0;
1313 counter := 1;
1314
1315 htp.p('<FORM ACTION="' || v_dcdName || '/ICX_REQ_SEARCH.submit_items" METHOD="POST" NAME="catalog_items">');
1316
1317 htp.formHidden('cartId','');
1318 htp.formHidden('p_emergency','');
1319 js.scriptOpen;
1320 htp.p('get_parent_values(document.catalog_items.cartId,document.catalog_items.p_emergency)');
1321 js.scriptClose;
1322
1323 htp.formHidden('a_1',a_1, 'cols="60" rows="10"');
1324 htp.formHidden('c_1',c_1, 'cols="60" rows="10"');
1325 htp.formHidden('i_1',i_1, 'cols="60" rows="10"');
1326 htp.formHidden('a_2',a_2, 'cols="60" rows="10"');
1327 htp.formHidden('c_2',c_2, 'cols="60" rows="10"');
1328 htp.formHidden('i_2',i_2, 'cols="60" rows="10"');
1329 htp.formHidden('a_3',a_3, 'cols="60" rows="10"');
1330 htp.formHidden('c_3',c_3, 'cols="60" rows="10"');
1331 htp.formHidden('i_3',i_3, 'cols="60" rows="10"');
1332 htp.formHidden('a_4',a_4, 'cols="60" rows="10"');
1333 htp.formHidden('c_4',c_4, 'cols="60" rows="10"');
1334 htp.formHidden('i_4',i_4, 'cols="60" rows="10"');
1335 htp.formHidden('a_5',a_5, 'cols="60" rows="10"');
1336 htp.formHidden('c_5',c_5, 'cols="60" rows="10"');
1337 htp.formHidden('i_5',i_5, 'cols="60" rows="10"');
1338 htp.formHidden('p_start_row',p_start_row,'cols="60" rows = "10"');
1339 htp.formHidden('p_end_row',p_end_row,'cols="60" rows ="10"');
1340 htp.formHidden('p_where',p_where,'cols="60" rows = "10"');
1341 htp.formHidden('end_row',end_row,'cols="60" rows ="10"');
1342 htp.formHidden('p_query_set',c_query_size,'cols="60" rows = "10"');
1343 htp.formHidden('p_row_count',total_rows,'cols="60" rows="10"');
1344
1345 if (v_use_context_search = 'N') then
1346 htp.formHidden('p_hidden', 'N', 'cols="60" rows="10"');
1347 else
1348 htp.formHidden('p_hidden', null, 'cols="60" rows="10"');
1349 end if;
1350
1351 l_pos := l_pos + 24;
1352
1353 htp.tableOpen('BORDER');
1354 htp.p('<TR BGColor="#'||icx_util.get_color('TABLE_HEADER_TABS')||'">');
1355
1356 --Print the column headings
1357 for i in ak_query_pkg.g_items_table.FIRST .. ak_query_pkg.g_items_table.LAST loop
1358
1359 if (ak_query_pkg.g_items_table(i).node_display_flag = 'Y' and
1360 ak_query_pkg.g_items_table(i).secured_column <> 'T' and
1361 ak_query_pkg.g_items_table(i).item_style <> 'HIDDEN') or
1362 ak_query_pkg.g_items_table(i).attribute_code = 'ICX_QTY' then
1363
1364 if ak_query_pkg.g_items_table(i).attribute_code = 'ICX_QTY' then
1365 --print quantity heading WITH COLSPAN=2
1366 htp.tableData(ak_query_pkg.g_items_table(i).attribute_label_long,'CENTER','','','','2');
1367 elsif ak_query_pkg.g_items_table(i).attribute_code = 'ICX_UNIT_PRICE' then
1368 htp.tableData(ak_query_pkg.g_items_table(i).attribute_label_long || ' (' || c_currency || ')',
1369 'CENTER','','','','width=80');
1370 else
1371 htp.tableData(ak_query_pkg.g_items_table(i).attribute_label_long,'CENTER');
1372 end if;
1373 end if;
1374
1375 -- find line id, urls value id
1376 if ak_query_pkg.g_items_table(i).value_id is not null then
1377
1378 --need line_id to call javascript function down() and up()
1379 if (ak_query_pkg.g_items_table(i).attribute_code = 'ICX_LINE_ID') then
1380 v_line_id_ind := ak_query_pkg.g_items_table(i).value_id;
1381 end if;
1382 -- find item_url and supplier_item_url
1383 if (ak_query_pkg.g_items_table(i).attribute_code = 'ICX_ITEM_URL') then
1384 v_item_url_ind := ak_query_pkg.g_items_table(i).value_id;
1385 end if;
1386 if (ak_query_pkg.g_items_table(i).attribute_code = 'ICX_SUPPLIER_URL') then
1387 v_supplier_url_ind := ak_query_pkg.g_items_table(i).value_id;
1388 end if;
1389 if (ak_query_pkg.g_items_table(i).attribute_code = 'ICX_SUPPLIER_ITEM_URL') then
1390 v_supplier_item_url_ind := ak_query_pkg.g_items_table(i).value_id;
1391 end if;
1392 end if;
1393
1394 end loop;
1395
1396 htp.tableData('');
1397 htp.tableRowClose;
1398
1399 -- for j in p_start_row-1 .. end_row-1 loop
1400 for j in ak_query_pkg.g_results_table.FIRST .. ak_query_pkg.g_results_table.LAST loop
1401
1402 temp_table(0) := '<TR BGColor="#'||icx_util.get_color('TABLE_DATA_MULTIROW')||'">';
1403 icx_util.transfer_Row_To_Column( ak_query_pkg.g_results_table(j), y_table);
1404
1405 for i in ak_query_pkg.g_items_table.first .. ak_query_pkg.g_items_table.last loop
1406
1407 --print quantity input text box and up button if v_qty_flag is set
1408 if v_qty_flag and ak_query_pkg.g_items_table(i).attribute_code = 'ICX_QTY' then
1409 display_text := '<TD ROWSPAN=2><CENTER> <INPUT TYPE=''text'' NAME=''Quantity'' '
1410 || ' SIZE=' || to_char(V_QUANTITY_LENGTH) || ' onChange=''if(!parent.parent.checkNumber(this)){this.focus();this.value="";}''></CENTER></TD>';
1411
1412 --@@show the quantity in the box filled in in the previous record set
1413
1414 l_spin_pos := l_pos;
1415 display_text := display_text
1416 || '<TD WIDTH=18 valign=bottom> <a href="javascript:parent.parent.up(document.catalog_items.elements['
1417 || l_spin_pos
1418 || '])" onMouseOver="window.status=''Add Quantity'';return true"><IMG SRC=/OA_MEDIA/'
1419 || c_language
1420 || '/FNDISPNU.gif border=0></a></TD>';
1421 l_pos := l_pos + 1;
1422
1423 temp_table(0) := temp_table(0) || display_text;
1424 end if;
1425
1426 if ak_query_pkg.g_items_table(i).attribute_code = 'ICX_LINE_ID' then
1427 display_text := '<INPUT TYPE="HIDDEN" NAME="Line_Id" VALUE ='|| y_table(ak_query_pkg.g_items_table(i).value_id) || '>';
1428
1429 l_pos := l_pos + 1;
1430 temp_table(0) := temp_table(0) || display_text;
1431 end if;
1432
1433
1434 if ak_query_pkg.g_items_table(i).value_id is not null
1435 and ak_query_pkg.g_items_table(i).node_display_flag = 'Y'
1436 and ak_query_pkg.g_items_table(i).secured_column <> 'T'
1437 and ak_query_pkg.g_items_table(i).item_style <> 'HIDDEN' then
1438
1439 /* Ref Bug #640289 : Changed By Suri. The Standard Requisitions/Emergency Requisitions Unit Price field should allow more than two decimal places. ***/
1440
1441 IF ak_query_pkg.g_items_table(i).attribute_code = 'ICX_UNIT_PRICE' THEN
1442 -- display_text := to_char(to_number(y_table(ak_query_pkg.g_items_table(i).value_id)), c_money_fmt_mask);
1443 display_text := to_char(to_number(y_table(ak_query_pkg.g_items_table(i).value_id)));
1444 /* End Change Bug #640289 By Suri ***/
1445 else
1446 display_text := y_table(ak_query_pkg.g_items_table(i).value_id);
1447 end if;
1448
1449 --Display item_description as a link and a tabledata
1450 if display_text is not NULL then
1451 if (ak_query_pkg.g_items_table(i).attribute_code = 'ICX_ITEM_DESCRIPTION') then
1452 v_item_url := y_table(v_item_url_ind);
1453 display_text := ICX_REQ_NAVIGATION.addURL(v_item_url, display_text);
1454 end if;
1455 --Display source_name as a link
1456 if ak_query_pkg.g_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_NAME' then
1457 v_supplier_url := y_table(v_supplier_url_ind);
1458 display_text := ICX_REQ_NAVIGATION.addURL(v_supplier_url, display_text);
1459 end if;
1460 --Display supplier item number as a link
1461 if ak_query_pkg.g_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_ITEM_NUM' then
1462 v_supplier_item_url := y_table(v_supplier_item_url_ind);
1463 display_text := ICX_REQ_NAVIGATION.addURL(v_supplier_item_url, display_text);
1464 end if;
1465 end if;
1466
1467 if display_text is null then
1468 display_text := htf.br;
1469 end if;
1470 if display_text = '-' then
1471 display_text := htf.br;
1472 end if;
1473
1474 if ak_query_pkg.g_items_table(i).bold = 'Y' then
1475 display_text := htf.bold(display_text);
1476 end if;
1477 --Italics
1478 if ak_query_pkg.g_items_table(i).italic = 'Y' then
1479 display_text := htf.italic(display_text);
1480 end if;
1481 temp_table(0) := temp_table(0) ||
1482 htf.tableData( cvalue => display_text,
1483 calign => ak_query_pkg.g_items_table(i).horizontal_alignment,
1484 crowspan => '2',
1485 cattributes => ' VALIGN=' || ak_query_pkg.g_items_table(i).vertical_alignment
1486 );
1487 end if;
1488 end loop; -- for i
1489
1490 --close the table row
1491 temp_table(0) := temp_table(0) || htf.tableRowClose;
1492 if v_qty_flag then
1493 --print the down button
1494 display_text := htf.tableRowOpen( cattributes => 'BGColor="#'||icx_util.get_color('TABLE_DATA_MULTIROW')||'"');
1495
1496 display_text := display_text
1497 || '<TD WIDTH=18 valign=top><a href="javascript:parent.parent.down(document.catalog_items.elements['
1498 || l_spin_pos
1499 || '])" onMouseOver="window.status=''Reduce Quantity'';return true"><IMG SRC=/OA_MEDIA/' || c_language
1500 || '/FNDISPND.gif BORDER=0></a>';
1501 display_text := display_text || '</TD>';
1502
1503 display_text := display_text || htf.tableRowClose;
1504 temp_table(0) := temp_table(0) || display_text;
1505 end if;
1506 htp.p(temp_table(0));
1507 counter := counter + 1;
1508 end loop; -- for j in p_start_row-1 .. end_row-1 loop
1509
1510
1511 htp.tableClose;
1512 htp.p('</FORM>');
1513
1514 htp.formOpen('ICX_REQ_SEARCH.displayItem','POST','','','NAME="DISPLAY_ITEM"');
1515 htp.formHidden('start_row',p_start_row);
1516 htp.formHidden('c_end_row',p_end_row);
1517 htp.formHidden('parameters_id',parameters_pass);
1518 htp.formClose;
1519
1520 icx_admin_sig.footer;
1521
1522 end if;
1523
1524 -- dbms_session.set_sql_trace(FALSE);
1525 exception
1526 when others then
1527 htp.p('In display item');
1528
1529 htp.p(SQLERRM);
1530
1531 end displayItem;
1532
1533
1534 end ICX_REQ_SEARCH;