[Home] [Help]
PACKAGE BODY: APPS.ICX_REQ_NAVIGATION
Source
1 PACKAGE BODY ICX_REQ_NAVIGATION as
2 /* $Header: ICXREQSB.pls 115.2 99/07/17 03:22:07 porting ship $ */
3 /*----------------BEGIN Welcome Page--------------- */
4 /* Welcome page for Requisitions */
5 ------------------------------------------------------
6 procedure reqs_welcome_page is
7 ------------------------------------------------------
8 v_lang varchar2(5);
9 c_title varchar2(80);
10 c_prompts icx_util.g_prompts_table;
11
12 v_dcdName varchar2(1000);
13
14 v_message_caption varchar2(200);
15 v_message_text varchar2(1000);
16
17 v_0_encrypt varchar2(100);
18 begin
19
20 -- Check if session is valid
21 if (icx_sec.validatesession('ICX_REQS')) then
22
23 -- get dcd name
24 v_dcdName := owa_util.get_cgi_env('SCRIPT_NAME');
25
26 -- set lang code
27 v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
28
29 -- encrypt 0
30 v_0_encrypt := icx_call.encrypt2('0');
31
32 -- Create the Intro Page
33
34
35 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_INTRO_TITLE');
36 c_title := FND_MESSAGE.GET;
37
38 htp.htmlOpen;
39 htp.title(c_title);
40 htp.bodyOpen;
41
42 htp.headOpen;
43
44 icx_util.copyright;
45
46 js.scriptOpen;
47 htp.p('function help_window(){
48 help_win = window.open(''/OA_DOC/' || v_lang || '/awe' || '/icxhlprq.htm'', "help_win","resizable=yes,scrollbars=yes,toolbar=yes,width=450,height=250");
49 help_win = window.open(''/OA_DOC/' || v_lang || '/awe' || '/icxhlprq.htm'', "help_win","resizable=yes,scrollbars=yes,toolbar=yes,width=450,height=250")
50 }
51 ');
52
53 js.scriptClose;
54 htp.headClose;
55 -- htp.p('<BODY BACKGROUND="/OA_MEDIA/' || v_lang || '/ICXBCKGR.jpg">');
56
57
58 -- TOOLBAR
59 icx_admin_sig.toolbar(language_code => v_lang);
60
61 htp.p('<table border=0 cellpadding=0><tr>');
62 htp.p('<td width=2000 bgcolor=#0000ff height=4><img src=/OA_MEDIA/'||
63 v_lang || '/FNDDBPX6.gif height=1 width=1></td></tr></table>');
64
65 htp.p('<table cellspacing=8 cellpadding=0 border=0>');
66 htp.p('<tr><td colspan=3>');
67 -- The top intro line of the page
68 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_INTRO_TXT');
69 htp.p(FND_MESSAGE.GET || '<p>');
70 htp.p('</font></td></tr><tr><td colspan=3>');
71
72 -- The First line of the intro
73 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_SELECT_ITEMS_TTL');
74 v_message_caption := FND_MESSAGE.GET;
75 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_SELECT_ITEMS_TXT');
76 v_message_text := FND_MESSAGE.GET;
77 htp.p('<table border=0 cellpadding=0>');
78 htp.p('<tr>');
79 htp.p('<td rowspan=2><a href=' || v_dcdName ||
80 '/ICX_REQ_NAVIGATION.ic_parent?cart_id=' || v_0_encrypt ||
81 '>' || '<img src=/OA_MEDIA/' || v_lang ||
82 '/FNDISEL.gif border=no height=75 width=75 align=absmiddle></a></td>');
83 htp.p('<td colspan=2 height=4><img src=/OA_MEDIA/' || v_lang ||
84 '/FNDIBLBR.gif width=500 height=4></td></tr><tr>');
85 htp.p('<td width=50 align=center valign=top><font size=7 color=#0000ff>' ||
86 '<b>1</td>');
87 htp.p('<td width=1000 valign=top><b><font size=+1 color=#0000ff>' ||
88 v_message_caption || '</b></font><br>' || v_message_text ||
89 '</td></td></tr></table>');
90
91 -- Second line
92 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_CHK_ORDER_TTL');
93 v_message_caption := FND_MESSAGE.GET;
94 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_CHK_ORDER_TXT');
95 v_message_text := FND_MESSAGE.GET;
96 htp.p('</td></tr><tr>');
97 htp.p('<td rowspan=2><font size=7> </td>');
98 htp.p('<td colspan=2>');
99 htp.p('<table border=0 cellpadding=0>');
100 htp.p('<tr>');
101 htp.p('<td rowspan=2><img src=/OA_MEDIA/' || v_lang ||
102 '/FNDICKO.gif height=75 width=75 align = absmiddle></td>');
103 htp.p('<td colspan=2 height=4><img src=/OA_MEDIA/' || v_lang ||
104 '/FNDIRDBR.gif width=500 height=4></td><tr>');
105 htp.p('<td width=50 align=center valign=top><font size=7 color=#cc0000>' ||
106 '<b>2</td>');
107 htp.p('<td width=1000 valign=top><b><font size=+1 color="#CC0000">' ||
108 v_message_caption || '</b></font><br>' || v_message_text || '</td>' ||
109 '</tr></table>');
110
111 -- Third line
112 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_PLACE_ORDER_TTL');
113 v_message_caption := FND_MESSAGE.GET;
114 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_PLACE_ORDER_TXT');
115 v_message_text := FND_MESSAGE.GET;
116 htp.p('</td></tr><tr>');
117 htp.p('<td rowspan=2><font size=7> </td>');
118 htp.p('<td colspan=1>');
119 htp.p('<table border=0 cellpadding=0>');
120 htp.p('<tr>');
121 htp.p('<td rowspan=2><img src=/OA_MEDIA/' || v_lang ||
122 '/FNDIPLO.gif height=75 width=75 align = absmiddle></td>');
123 htp.p('<td colspan=2 height=4><img src=/OA_MEDIA/' || v_lang ||
124 '/FNDIGRBR.gif width=500 height=4></td><tr>');
125 htp.p('<td width=50 align=center valign=top><font size=7 color=#006666>' ||
126 '<b>3</td>');
127 htp.p('<td width=1000 valign=top><b><font size=+1 color="#006666">' ||
128 v_message_caption || '</b></font><br>' || v_message_text ||
129 '<br></td>' || '</tr></table>');
130
131 htp.p('</td></tr></table>');
132
133 htp.p('<center>');
134 htp.anchor(v_dcdName || '/ICX_REQ_NAVIGATION.ic_parent?cart_id=' ||
135 v_0_encrypt, htf.img('/OA_MEDIA/' || v_lang ||
136 '/FNDISELS.gif', cattributes => 'BORDER = NO align=absmiddle' ));
137 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_PROCEED_TTL');
138 htp.p('<FONT SIZE=+1>');
139 htp.anchor(v_dcdName || '/ICX_REQ_NAVIGATION.ic_parent?cart_id=' ||
140 v_0_encrypt, FND_MESSAGE.GET);
141 htp.p('</FONT>');
142 htp.p('</center>');
143
144 htp.bodyClose;
145 htp.htmlClose;
146
147 end if;
148
149 end reqs_welcome_page;
150
151 /*----------------END Welcome Page--------------- */
152
153
154 ------------------------------------------------------------------------
155 procedure chk_vendor_on(v_on OUT varchar2) is
156 ------------------------------------------------------------------------
157
158 v_vendor_on_flag varchar2(1);
159 begin
160
161 v_on := 'N';
162 v_vendor_on_flag := 'N';
163 for i in ak_query_pkg.g_items_table.first .. ak_query_pkg.g_items_table.last loop
164 if (ak_query_pkg.g_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_NAME' or
165 ak_query_pkg.g_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_CONTACT' or
166 ak_query_pkg.g_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_PHONE' or
167 ak_query_pkg.g_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_SITE') and
168 ak_query_pkg.g_items_table(i).node_display_flag = 'Y' and
169 ak_query_pkg.g_items_table(i).update_flag = 'Y' and
170 ak_query_pkg.g_items_table(i).secured_column <> 'T' and
171 ak_query_pkg.g_items_table(i).item_style <> 'HIDDEN' then
172
173 v_vendor_on_flag := 'Y';
174 exit;
175 end if;
176 end loop;
177
178 v_on := v_vendor_on_flag;
179 end chk_vendor_on;
180 ------------------------------------------------------------
181 procedure replaceQuotes is
182 ------------------------------------------------------------
183 begin
184 --
185 -- Do not use split and join function because it is provided
186 -- only by Netscape Navigator at this time (12/96).
187 -- Microsoft IE does not provide that.
188 --
189 --
190 -- FIX THIS
191 --
192 -- Fixed by dchu at 1/8/97
193 htp.p('function getFront(str,searchStr) {
194 var str2 = "" + str;
195 var offset = str2.indexOf(searchStr);
196 if (offset == -1) {
197 return null;
198 }
199 return str2.substring(0,offset);
200 }');
201
202 htp.p('function getEnd(str,searchStr) {
203 var str2 = "" + str;
204 var offset = str2.indexOf(searchStr);
205 if (offset == -1) {
206 return null;
207 }
208 return str2.substring(offset+searchStr.length,str2.length);
209 }');
210
211 htp.p('function replaceString(qstr,searchStr,repStr) {
212 var newstr = qstr;
213 var retstr = "";
214 var frontStr = getFront(newstr,searchStr);
215 var endStr = getEnd(newstr,searchStr);
216 if (frontStr == null) {
217 return qstr;
218 }
219 if (endStr == null) {
220 return frontStr + repStr;
221 }
222 while (endStr <> null) {
223 retstr = retstr + frontStr + repStr;
224 newstr = endStr;
225 frontStr = getFront(newstr,searchStr);
226 endStr = getEnd(newstr,searchStr);
227 if (frontStr == null && newstr <> null) {
228 retstr = retstr + newstr;
229 }
230 }
231 return retstr;
232 }');
233
234 htp.p('function replaceQuotes(qstr) {
235
236 var newstr = "" + qstr;
237 newstr = replaceString(newstr,' || '"''"' || ',"*' || '");
238 newstr = replaceString(newstr,''"'',''"'');
239 return newstr;
240
241 }');
242
243 end replaceQuotes;
244
245
246 ------------------------------------------------------------
247 procedure shopper_info(v_shopper_id IN number,
248 v_shopper_name OUT VARCHAR2,
249 v_location_id OUT number,
250 v_location_code OUT VARCHAR2,
251 v_org_id OUT NUMBER,
252 v_org_code OUT VARCHAR2) is
253 ------------------------------------------------------------
254
255 cursor shopper(v_shop_id number) is
256 select hrev.full_name,
257 hrl.location_id,
258 hrl.location_code,
259 ood.organization_id,
260 ood.organization_code
261 from hr_locations hrl,
262 hr_employees_current_v hrev,
263 org_organization_definitions ood,
264 financials_system_parameters fsp
265 where hrev.employee_id = v_shop_id
266 and hrev.location_id = hrl.location_id
267 and ood.organization_id = nvl(hrl.inventory_organization_id,
268 fsp.inventory_organization_id)
269 and sysdate < nvl(hrl.inactive_date, sysdate + 1);
270
271 begin
272
273 open shopper(v_shopper_id);
274 fetch shopper into v_shopper_name, v_location_id, v_location_code, v_org_id
275 , v_org_code;
276 close shopper;
277
278 end shopper_info;
279
280 --**********************************************************
281 -- BEGIN JS PROCEDURES RELATED TO MULTILEVEL HIERARCHY
282 --**********************************************************
283
284 ------------------------------------------------------------
285 procedure create_multilevel_js_functions(v_lang varchar2) is
286 ------------------------------------------------------------
287 v_str_errors varchar2(1000);
288
289 begin
290
291 FND_MESSAGE.SET_NAME('ICX','ICX_JS_STRING_ERROR');
292 v_str_errors := FND_MESSAGE.GET;
293 FND_MESSAGE.SET_NAME('ICX','ICX_CONTACT_WEBMASTER');
294 v_str_errors := v_str_errors || FND_MESSAGE.GET;
295
296
297 htp.p('
298 // BEGIN JS PROCEDURES RELATED TO MULTILEVEL HIERARCHY
299
300 function node(nId, nName, nChildrenLoaded, nLink, p_where)
301 {
302 this.nodeId = nId;
303 this.nodeName = nName;
304 this.nodeLink = nLink;
305 this.node_p_where = p_where;
306 this.children = new MakeArray(0);
307 this.childrenLoaded = nChildrenLoaded;
308 this.nodeOpen = false;
309
310 // Setup myself in global array
311 ALLNODES.length +=1;
312 ALLNODES[ALLNODES.length] = this;
313 this.arrayIndex = ALLNODES.length;
314
315 // Object Methods
316 this.drawNode = drawNode;
317 this.addChild = addChild;
318 }
319
320 function addChild( node )
321 {
322 this.children.length += 1;
323 this.children[this.children.length] = node;
324 }
325
326 function addChildren( nodeId,field,fieldIndex )
327 {
328 var str = "" + field.value;
329 var nodeindex = "" + fieldIndex.value;
330 var mySelf = findNode( nodeId,nodeindex );
331
332 if (mySelf == null)
333 return;
334
335 while ( str <> "" )
336 {
337 var index;
338
339 // node id
340 index = str.indexOf("~~");
341 if (index == -1) { alert ("' || icx_util.replace_quotes(v_str_errors) ||
342 '"); return; }
343 var nId = str.substring(0,index);
344 str = str.substring(index+2,str.length);
345
346 // node name
347 index = str.indexOf("~~");
348 if (index == -1) { alert ("' || icx_util.replace_quotes(v_str_errors) ||
349 '"); return; }
350
351 var nodeName = str.substring(0,index);
352 str = str.substring(index+2,str.length);
353
354 // No of children
355 index = str.indexOf("~~");
356 if (index == -1) { alert ("' || icx_util.replace_quotes(v_str_errors) ||
357 '"); return; }
358
359 var nChildren = eval(str.substring(0,index));
360 var nChildrenLoaded = false;
361 if ( nChildren < 1){
362 nChildrenLoaded = true;
363 }
364 str = str.substring(index+2,str.length);
365
366 // Link
367 index = str.indexOf("~~");
368 if (index == -1) { alert ("' || icx_util.replace_quotes(v_str_errors) ||
369 '"); return; }
370
371 var nLink = str.substring(0,index);
372 str = str.substring(index+2,str.length);
373
374
375 // p_where
376 index = str.indexOf("~~");
377 if (index == -1) { alert ("' || icx_util.replace_quotes(v_str_errors) ||
378 '"); return; }
379
380 var p_where = str.substring(0,index);
381 str = str.substring(index+2,str.length);
382
383 mySelf.addChild( new node(nId, nodeName, nChildrenLoaded, nLink, p_where));
384 }
385
386 mySelf.nodeOpen = true;
387 mySelf.childrenLoaded = true;
388 field.value = "";
389 redraw(); // Redraw the frame
390 }
391
392
393 function openTemplate( nodeId,field,fieldIndex )
394 {
395 var str = "" + field.value;
396 var nodeIndex = "" + fieldIndex.value;
397 var mySelf = findNode( nodeId,nodeIndex );
398
399 if (mySelf == null)
400 return;
401
402 while ( str <> "" )
403 {
404 var index;
405
406 // node id
407 index = str.indexOf("~~");
408 if (index == -1) { alert ("' || icx_util.replace_quotes(v_str_errors) ||
409 '"); return;}
410 var nId = str.substring(0,index);
411 str = str.substring(index+2,str.length);
412
413 // node name
414 index = str.indexOf("~~");
415 if (index == -1) { alert ("' || icx_util.replace_quotes(v_str_errors) ||
416 '"); return;}
417 var nodeName = str.substring(0,index);
418 str = str.substring(index+2,str.length);
419
420 // No of children
421 index = str.indexOf("~~");
422 if (index == -1) { alert ("' || icx_util.replace_quotes(v_str_errors) ||
423 '"); return;}
424 var nChildren = eval(str.substring(0,index));
425 var nChildrenLoaded = false;
426 if ( nChildren < 1){
427 nChildrenLoaded = true;
428 }
429 str = str.substring(index+2,str.length);
430
431 // Link
432 index = str.indexOf("~~");
433 if (index == -1) { alert ("' || icx_util.replace_quotes(v_str_errors) ||
434 '"); return;}
435 var nLink = str.substring(0,index);
436 str = str.substring(index+2,str.length);
437
438
439 // p_where
440 index = str.indexOf("~~");
441 if (index == -1) { alert ("' || icx_util.replace_quotes(v_str_errors) ||
442 '"); return;}
443 var p_where = str.substring(0,index);
444 str = str.substring(index+2,str.length);
445
446 mySelf.addChild( new node(nId, nodeName, nChildrenLoaded, nLink, p_where));
447 }
448
449 mySelf.nodeOpen = false;
450 mySelf.childrenLoaded = true;
451 field.value = "";
452 redraw(); // Redraw the frame
453 }
454
455
456 function findNode(nId, nodeIndex){
457 if (nodeIndex == "") {
458 for (var i=1; i<= ALLNODES.length; i++){
459 if ((""+nId) == ("" + ALLNODES[i].nodeId))
460 return ALLNODES[i];
461 }
462 } else {
463 for(var i=1; i<= ALLNODES.length; i++){
464 if (((""+nId) == ("" + ALLNODES[i].nodeId)) && ((""+nodeIndex) == ("" + ALLNODES[i].arrayIndex)))
465 return ALLNODES[i];
466 }
467 }
468 return null;
469 }
470 function redraw() {
471 top.frames["navigation"].frames["left_frame"].location.href =
472 top.frames["navigation"].frames["left_frame"].location.href;
473
474 }
475
476
477 function open_node (arrayIndex) {
478 if (ALLNODES[arrayIndex].childrenLoaded == false ) {
479
480 // Load the children from the server.
481 top.frames["navigation"].frames["dummy"].document.GetChildren.nodeId.value = ALLNODES[arrayIndex].nodeId;
482 top.frames["navigation"].frames["dummy"].document.GetChildren.p_where.value = ALLNODES[arrayIndex].node_p_where;
483 top.frames["navigation"].frames["dummy"].document.GetChildren.nodeIndex.value = arrayIndex;
484 top.frames["navigation"].frames["dummy"].document.GetChildren.submit();
485 ALLNODES[arrayIndex].nodeOpen = false;
486 } else {
487 ALLNODES[arrayIndex].nodeOpen = true;
488 }
489 redraw(); // Redraw the frame
490 }
491
492
493
494 function close_node (arrayIndex) {
495 ALLNODES[arrayIndex].nodeOpen = false;
496 redraw(); // Redraw the frame
497 }
498
499
500 function print(str) {
501 top.frames["navigation"].frames["left_frame"].document.write(str);
502 }
503
504 function drawNode()
505 {
506
507 var bold = false;
508
509 var atext = "<nobr><A NAME =" + this.arrayIndex + " TARGET=\"right_frame\" HREF=" + this.nodeLink +
510 " onClick=\"imClicked(''" + replaceQuotes(this.nodeId) + "'',''" + this.node_p_where + "'');\">" + replaceQuotes(this.nodeName) + "</A></nobr>";
511
512
513 if ( "" + this.nodeId == "" + lastCatalog.node_id || "" + this.nodeId == "" + lastTemplate.node_id )
514 bold = true;
515
516 if ( bold ) {
517 atext = "<B>" + atext + "</B>";
518 }
519
520 var str = "<dt NOWRAP>";
521 if (this.nodeOpen) {
522 if (this.children.length <> 0) {
523 //Children
524 str += "<A NAME =" + this.arrayIndex + " HREF=\"javascript:top.close_node(" + this.arrayIndex + ")\">";
525
526 str += "<IMG SRC=\"/OA_MEDIA/' || v_lang || '/FNDIMNUS.gif\" HEIGHT = 18 WIDTH = 28 BORDER = 0></A>";
527
528 str += atext;
529
530 str += "<dl NOWRAP>";
531 for(var i=1; i<=this.children.length; i++)
532 str += this.children[i].drawNode();
533 str += "</dl>";
534 } else {
535 //No Children
536 // Dummy image for alignment
537 str += "<A><IMG SRC=\"/OA_MEDIA/' || v_lang || '/FNDSPACE.gif\" HEIGHT = 18 WIDTH = 28 BORDER = 0></A>";
538
539 // display the name as a link
540 str += atext;
541 }
542 } else { // Node is closed
543 if (this.childrenLoaded && this.children.length == 0) {
544 //No Children -- Dummy image for alignment
545 str += "<A><IMG SRC=\"/OA_MEDIA/' || v_lang || '/FNDSPACE.gif\" HEIGHT = 18 WIDTH = 28 BORDER = 0></A>"
546
547 } else {
548 str += "<A NAME =" + this.arrayIndex + " HREF =\"javascript:top.open_node(" + this.arrayIndex + ")\">";
549 str += "<IMG SRC=\"/OA_MEDIA/' || v_lang || '/FNDIPLUS.gif\" HEIGHT = 18 WIDTH = 28 BORDER = 0></A>";
550 }
551
552
553 str += atext;
554 }
555 str += "</dt>";
556 return str;
557 }
558
559
560 function setparenttab(tabName)
561 {
562 parent.tabSynch.tabName = tabName;
563 }
564
565 // Global variables.
566 ALLNODES = new MakeArray(0);
567 SELECTED_ARRAY_ID = 0;
568
569 // END JS PROCEDURES RELATED TO MULTILEVEL HIERARCHY
570
571 ');
572
573 end create_multilevel_js_functions;
574
575
576
577 ------------------------------------------------------------
578 procedure synchObject is
579 ------------------------------------------------------------
580 begin
581 htp.p('function synchObject(original_frame) {
582 this.tabName = original_frame;
583 }
584
585 tabSynch = new synchObject("template");
586 ');
587
588 end synchObject;
589
590 ------------------------------------------------------------
591 procedure popWindow is
592 ------------------------------------------------------------
593 begin
594 htp.p('function popWindow(sourceURL) {
595 win = window.open(sourceURL, "drillDown", "resizable=yes,scrollbars=yes,width=750,height=300");
596 win = window.open(sourceURL, "drillDown", "resizable=yes,scrollbars=yes,width=750,height=300");
597 }
598 ');
599
600 end popWindow;
601
602 ------------------------------------------------------------
603 procedure spin_box is
604 ------------------------------------------------------------
605 begin
606
607 htp.p('function up(field) {
608 var emptyCheck = "" + field.value;
609 if (emptyCheck == "") {
610 field.value = 1;
611 } else {
612 var numThere = parseFloat(field.value);
613 numThere = eval(numThere + 1);
614 field.value = numThere;
615 }
616 }
617 ');
618
619 htp.p('function down(field) {
620
621
622 var emptyCheck = "" + field.value;
623 if (emptyCheck <> "") {
624 var numThere = parseFloat(field.value);
625 if (numThere <= 1) {
626 field.value = "";
627 } else {
628 numThere = eval(numThere - 1);
629 field.value = numThere;
630 }
631 }
632 }
633 ');
634
635 end spin_box;
636
637 ------------------------------------------------------------
638 procedure giveWarning is
639 ------------------------------------------------------------
640 begin
641
642 FND_MESSAGE.SET_NAME('ICX', 'ICX_CART_RMV_ALL');
643 htp.p('function giveWarning() {
644 if (confirm(''' || icx_util.replace_quotes(FND_MESSAGE.GET) || ''')) {
645 return true;
646 } else {
647 return false;
648 }
649 }
650 ');
651
652 end giveWarning;
653 ------------------------------------------------------------
654 function get_default_template( v_emergency varchar2 )
655 return varchar2 is
656 ------------------------------------------------------------
657
658 v_return_template varchar2(25);
659 v_test number;
660
661 begin
662
663 -- get default template
664 v_return_template := icx_sec.getID(icx_sec.PV_USER_REQ_TEMPLATE);
665
666 v_test := 0;
667
668 if v_emergency = 'YES' then
669 select count(-1) into v_test
670 from po_reqexpress_headers
671 where express_name = v_return_template
672 and (reserve_po_number = 'YES' OR reserve_po_number = 'OPTIONAL');
673 else
674 select count(-1) into v_test
675 from po_reqexpress_headers
676 where express_name = v_return_template
677 and (reserve_po_number = 'NO' OR reserve_po_number = 'OPTIONAL' OR reserve_po_number is null);
678 end if;
679
680 if v_test = 0 then
681 v_return_template := null;
682 end if;
683
684 return v_return_template;
685
686 end get_default_template;
687
688 ------------------------------------------------------------
689 procedure reqNavigator(v_org_id number,
690 v_cart_id number default -1,
691 emergency varchar2 default NULL,
692 v_dcdName varchar2,
693 v_lang varchar2,
694 v_shopper_id number) is
695 ------------------------------------------------------------
696
697 v_template varchar2(25);
698 v_emergency varchar2(10);
699
700 begin
701 if emergency is null then
702 v_emergency := 'NO';
703 else
704 v_emergency := emergency;
705 end if;
706
707 htp.framesetOpen('106,*','','BORDER=0');
708
709 if (v_cart_id = -1) then
710
711 -- get default template
712 v_template := get_default_template( emergency );
713 if (v_template is null) then
714 v_template := 'none';
715 end if;
716
717 htp.frame(v_dcdName ||
718 '/ICX_REQ_NAVIGATION.top_frame?tab_name=template&emergency=' ||
719 v_emergency,'tabs','0','0','no','NORESIZE', 'FRAMEBORDER=NO');
720
721 htp.frame(v_dcdName || '/ICX_REQ_TEMPLATES.templates?p_where=' ||
722 icx_call.encrypt2( '(NEW)' ||
723 icx_util.replace_quotes(v_template) || '*' || v_org_id ||
724 '**]'), 'navigation','0','0','auto','NORESIZE','FRAMEBORDER=NO');
725
726 else
727
728 htp.frame(v_dcdName ||
729 '/ICX_REQ_NAVIGATION.top_frame?tab_name=my_order&emergency=' ||
730 v_emergency,'tabs','0','0','no','NORESIZE', 'FRAMEBORDER=NO');
731 htp.frame(v_dcdName || '/ICX_REQ_ORDER.my_order?n_org=' ||
732 icx_call.encrypt2(v_org_id) || '&n_emergency=' ||
733 icx_call.encrypt2(v_emergency) || '&n_cart_id=' ||
734 icx_call.encrypt2(v_cart_id) ,'navigation', '0','0', 'auto',
735 'NORESIZE', 'FRAMEBORDER=NO');
736
737 end if;
738
739 htp.framesetClose;
740
741 end reqNavigator;
742
743
744 ------------------------------------------------------------
745 procedure synch(v_org_id number,
746 v_emergency varchar2,
747 v_cart_id number,
748 v_lang varchar2,
749 v_shopper number,
750 v_dcdName varchar2) is
751 ------------------------------------------------------------
752
753 v_template varchar2(100);
754 l_encrypt2_org_id number;
755
756 begin
757
758 -- now we need to have a way to store the last items list gone to
759 -- to accomplish this, we will store the last place gone in a javascript
760 -- object
761 htp.p('function itemStorage(p_node_id, p_start_row, p_end_row, v_where){
762 this.node_id = p_node_id;
763 this.start_row = p_start_row;
764 this.end_row = p_end_row;
765 this.p_where = v_where;
766 }
767 ');
768
769 -- Now we declare one for the templates and the catalogs
770 htp.p('lastCatalog = new itemStorage("", 1, -1,' ||
771 icx_call.encrypt2( '-1' || '*' || v_org_id || '**]') || ');' );
772
773
774 -- See if there is a default Template
775 v_template := get_default_template( v_emergency );
776 if v_template is null then
777 v_template := 'none';
778 end if;
779 htp.p('lastTemplate = new itemStorage("' || v_template || '",1,-1,' ||
780 icx_call.encrypt2( v_template || '*' || v_org_id || '**]') || ');');
781
782 -- Now create a function to switch to the correct stuff
783 l_encrypt2_org_id := icx_call.encrypt2(to_char(v_org_id));
784 htp.p('function switchFrames(tabName) {
785 var lastPlace = "";
786 tabSynch.tabName = tabName;
787 open("' || v_dcdName ||
788 '/ICX_REQ_NAVIGATION.top_frame?tab_name="+ tabName+"&emergency=' ||
789 v_emergency ||'", ''tabs'');
790
791 if (tabName == "template" ) {
792 lastPlace = "' || v_dcdName || '/ICX_REQ_TEMPLATES.templates?p_where=";
793 lastPlace += lastTemplate.p_where;
794 lastPlace += "&start_row=" + lastTemplate.start_row;
795 if (lastTemplate.end_row <> -1)
796 lastPlace += "&c_end_row=" + lastTemplate.end_row;
797 open(lastPlace, ''navigation'');
798 } else if (tabName == "catalog" ) {
799 lastPlace = "' || v_dcdName || '/ICX_REQ_CATEGORIES.categories?p_where=";
800 lastPlace += lastCatalog.p_where;
801 lastPlace += "&start_row=" + lastCatalog.start_row;
802 if (lastCatalog.end_row <> -1)
803 lastPlace += "&c_end_row=" + lastCatalog.end_row;
804 open(lastPlace, ''navigation'');
805 } else if (tabName == "item_search" ) {
806 open("' || v_dcdName || '/ICX_REQ_SEARCH.itemSearch?n_org='||
807 l_encrypt2_org_id || '", ''navigation'');
808 } else if (tabName == "special_order" ) {
809 open("' || v_dcdName || '/ICX_REQ_SPECIAL_ORD.special_order?n_org=' ||
810 l_encrypt2_org_id || '", ''navigation'');
811
812 } else if (tabName == "my_order" ) {
813 if (account_dist == "Y") {
814
815 open("' || v_dcdName || '/ICX_REQ_ORDER.my_order?n_org=' ||
816 l_encrypt2_org_id || '&n_emergency=' ||
817 icx_call.encrypt2(nvl(v_emergency, 'NO')) || '&n_cart_id=' ||
818 icx_call.encrypt2(v_cart_id) || '&n_cart_line_id="+cartLineId+"&n_account_dist=Y' || '", ''navigation'');
819
820 } else {
821
822 open("' || v_dcdName || '/ICX_REQ_ORDER.my_order?n_org=' ||
823 l_encrypt2_org_id || '&n_emergency=' ||
824 icx_call.encrypt2(nvl(v_emergency, 'NO')) || '&n_cart_id=' ||
825 icx_call.encrypt2(v_cart_id) ||'", ''navigation'');
826 }
827 }
828 }
829 ');
830
831
832 htp.p('function winOpen(locon, tabName) {
833 if (parent.tabSynch.tabName <> tabName) {
834 parent.tabSynch.tabName = tabName;
835 if (locon == ''nav'') {
836 open("' || v_dcdName ||
837 '/ICX_REQ_NAVIGATION.top_frame?tab_name="+ tabName+"&emergency='
838 || v_emergency ||'", ''tabs'');
839 } else {
840 switchFrames( tabName );
841 }
842 }
843 }
844 ');
845
846 end synch;
847
848 ------------------------------------------------------------
849 procedure sysadmin_error is
850 ------------------------------------------------------------
851 v_lang varchar2(5);
852
853 begin
854 -- set lang code
855 v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
856
857 htp.htmlOpen;
858 htp.headOpen;
859 icx_admin_sig.toolbar(language_code => v_lang);
860 icx_util.copyright;
861 js.scriptOpen;
862
863 htp.p('function help_window() {
864 help_win = window.open(''/OA_DOC/' || v_lang || '/awe' ||
865 '/icxhlprq.htm'', "help_win","resizable=yes,scrollbars=yes,toolbar=yes,width=450,height=250"
866 );
867 help_win = window.open(''/OA_DOC/' || v_lang || '/awe' ||
868 '/icxhlprq.htm'', "help_win","resizable=yes,scrollbars=yes,toolbar=yes,width=450,height=250"
869 )}
870 ');
871 js.scriptClose;
872
873
874 htp.headClose;
875 htp.bodyOpen('/OA_MEDIA/' || v_lang || '/ICXBCKGR.jpg');
876
877 FND_MESSAGE.SET_NAME('ICX', 'ICX_DATA_INCORRECT');
878 icx_util.add_error(FND_MESSAGE.GET);
879 icx_util.error_page_print;
880
881
882 htp.bodyClose;
883
884 end sysadmin_error ;
885
886 ------------------------------------------------------------
887 procedure top_frame( tab_name varchar2,
888 emergency varchar2 default NULL) is
889 ------------------------------------------------------------
890 v_lang varchar2(5);
891 v_image_name varchar2(32);
892 begin
893
894 if (icx_sec.validatesession('ICX_REQS')) then
895
896 -- get lang code
897 v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
898
899 htp.htmlOpen;
900 htp.headOpen;
901
902 js.scriptOpen;
903
904 htp.p('
905 function help_window(){
906 help_win = window.open(''/OA_DOC/' || v_lang || '/awe' ||
907 '/icxhlprq.htm'', "help_win","resizable=yes,scrollbars=yes,toolbar=yes,width=450,height=250"
908 );
909 help_win = window.open(''/OA_DOC/' || v_lang || '/awe' ||
910 '/icxhlprq.htm'', "help_win","resizable=yes,scrollbars=yes,toolbar=yes,width=450,height=250"
911 )}
912 ');
913
914 js.scriptClose;
915 htp.headClose;
916
917
918 htp.bodyOpen( icx_admin_sig.background(v_lang),
919 cattributes => 'onLoad="parent.winOpen(''tabs'', ''' ||
920 tab_name || ''')"' );
921
922 icx_admin_sig.toolbar (language_code => v_lang );
923
924 htp.mapOpen('tabsi');
925
926 if ((emergency is null) or (emergency = 'NO')) then
927 if (tab_name = 'template' ) then
928 v_image_name := '/FNDTREQ1.gif';
929 else
930 htp.area('17,3,133,20','rect',
931 'javascript:parent.switchFrames(''template'')');
932 end if;
933
934 if (tab_name = 'catalog' ) then
935 v_image_name := '/FNDTREQ2.gif';
936 else
937 htp.area('141,3,256,20', 'rect',
938 'javascript:parent.switchFrames(''catalog'')');
939 end if;
940
941 if (tab_name = 'item_search' ) then
942 v_image_name := '/FNDTREQ3.gif';
943 else
944 htp.area('264,3,380,20', 'rect',
945 'javascript:parent.switchFrames(''item_search'')');
946 end if;
947
948 if (tab_name = 'special_order' ) then
949 v_image_name := '/FNDTREQ4.gif';
950 else
951 htp.area('388,3,504,20', 'rect',
952 'javascript:parent.switchFrames(''special_order'')');
953 end if;
954
955 if (tab_name = 'my_order' ) then
956 v_image_name := '/FNDTREQ5.gif';
957 else
958 htp.area('512,3,628,20', 'rect',
959 'javascript:parent.switchFrames(''my_order'')');
960 end if;
961 else
962 if (tab_name = 'template' ) then
963 v_image_name := '/FNDTREQ6.gif'; -- New template image
964 else
965 htp.area('17,3,133,20','rect',
966 'javascript:parent.switchFrames(''template'')');
967 end if;
968
969 if (tab_name = 'my_order' ) then
970 v_image_name := '/FNDTREQ7.gif'; -- New My order image
971 else
972 htp.area('141,3,256,20', 'rect',
973 'javascript:parent.switchFrames(''my_order'')');
974 end if;
975 end if;
976
977 htp.mapClose;
978
979
980 htp.img2('/OA_MEDIA/' || v_lang ||
981 v_image_name , cusemap=> '#tabsi', cattributes=> 'BORDER=0' );
982
983 htp.bodyClose;
984 htp.htmlClose;
985
986 end if;
987
988 end top_frame;
989
990
991 ------------------------------------------------
992 procedure get_po is
993 ------------------------------------------------
994 begin
995
996 FND_MESSAGE.SET_NAME('ICX', 'ICX_ONE_PO_PER_REQUISITION');
997 htp.p('//Reserve a po number.
998 function get_po(){
999 open(top.dcd + "/ICX_REQ_NAVIGATION.get_emergency_po_num?n_org=" + top.org_id, ''navigation'');
1000
1001 }
1002
1003 ');
1004 end get_po;
1005
1006 ------------------------------------------------------------
1007 procedure ic_parent(cart_id in varchar2,
1008 emergency in varchar2 default NULL ) is
1009 ------------------------------------------------------------
1010
1011 cursor DETERMINE_CART(v_cart_id number, v_shopper_id number) is
1012 select count(-1)
1013 from icx_shopping_carts_v
1014 where cart_id = v_cart_id
1015 and shopper_id = v_shopper_id;
1016
1017 --change by alex for attachment
1018 -- cursor getCartId is
1019 -- select icx_shopping_Carts_s.nextval from sys.dual;
1020 --new code:
1021 cursor getCartId is
1022 select PO_REQUISITION_HEADERS_S.nextval from sys.dual;
1023
1024
1025 cursor getDate(increment number) is
1026 SELECT sysdate+increment from sys.dual;
1027
1028 v_cart_id number;
1029 v_language varchar2(30);
1030
1031 v_money_precision number;
1032
1033 v_function_code varchar2(20) := 'ICX_REQS';
1034
1035 c_title varchar2(80);
1036 c_prompts icx_util.g_prompts_table;
1037
1038 v_dcdName varchar2(1000);
1039
1040 v_emergency varchar2(10);
1041
1042 v_cart_there number;
1043 v_sysdate date;
1044 v_oo_id number;
1045 newCart Boolean := FALSE;
1046 v_dist_id number;
1047 shopper_id number;
1048 employee_id number;
1049 shopper_name varchar2(250);
1050 v_org_id number;
1051 v_req_num number;
1052 v_location_id number;
1053 v_need_by_date date;
1054 v_location_code varchar2(240);
1055 v_org_code varchar2(30);
1056 l_emer varchar2(1);
1057
1058 l_timer_begin number;
1059 l_timer number;
1060
1061 Begin
1062
1063
1064 -- Check if session is valid
1065 if (icx_sec.validatesession(v_function_code)) then
1066
1067 -- decrypt parameters
1068 begin
1069 v_cart_id := icx_call.decrypt2(cart_id);
1070 v_emergency := icx_call.decrypt2(emergency);
1071 exception
1072 when others then
1073 v_cart_id := -1;
1074 end;
1075
1076 -- Get dcd
1077 v_dcdName := owa_util.get_cgi_env('SCRIPT_NAME');
1078 -- get language
1079 v_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
1080 -- get shopper id
1081 shopper_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
1082 -- get employee_id ( Internal Contect ID )
1083 employee_id := icx_sec.getID(icx_sec.PV_INT_CONTACT_ID);
1084 -- get org id
1085 v_oo_id := icx_sec.getId(icx_sec.PV_ORG_ID);
1086
1087 if ((v_cart_id = -1)) then
1088 v_function_code := '-1';
1089 end if;
1090
1091 if v_emergency = 'YES' then
1092 v_function_code := 'ICX_EMG_REQS';
1093 else
1094 v_function_code := 'ICX_REQS';
1095 end if;
1096
1097 -- icx_util.getPrompts(178,'ICX_PARENT_TEMPLATE',c_title,c_prompts);
1098 icx_util.getPrompts(601,'ICX_PARENT_TEMPLATE',c_title,c_prompts);
1099
1100 -- Get the org id from shopper_id
1101 ICX_REQ_NAVIGATION.shopper_info(employee_id, shopper_name, v_location_id, v_location_code, v_org_id, v_org_code);
1102
1103 if ((employee_id is null) or (v_org_id is null))then
1104 sysadmin_error;
1105 return;
1106 end if;
1107
1108 --
1109 -- Determined if the cart is saved cart
1110 OPEN DETERMINE_CART(v_cart_id, shopper_id);
1111 FETCH DETERMINE_CART INTO v_cart_there;
1112 CLOSE DETERMINE_CART;
1113 if v_cart_there = 0 then
1114 newCart := TRUE;
1115 -- the cart cart does not exist create it
1116 open getCartId;
1117 fetch getCartId into v_cart_id;
1118 close getCartId;
1119
1120 -- get a requisition number
1121
1122 SELECT to_char(current_max_unique_identifier +1),
1123 sysdate,
1124 icx_cart_distributions_s.nextval
1125 INTO v_req_num, v_sysdate, v_dist_id
1126 FROM po_unique_identifier_control
1127 WHERE table_name = 'PO_REQUISITION_HEADERS'
1128 FOR UPDATE OF current_max_unique_identifier;
1129
1130 UPDATE po_unique_identifier_control
1131 SET current_max_unique_identifier =
1132 current_max_unique_identifier+1
1133 WHERE table_name = 'PO_REQUISITION_HEADERS';
1134
1135 commit;
1136
1137 OPEN getDate(nvl(icx_sec.getID(icx_sec.PV_USER_REQ_DAYS_NEEDED_BY), 0));
1138 FETCH getDate into v_need_by_date;
1139 CLOSE getDate;
1140
1141 if v_emergency is null or v_emergency = 'NO' then
1142 l_emer := 'N';
1143 else
1144 l_emer := 'Y';
1145 end if;
1146
1147 insert into icx_shopping_carts (
1148 cart_id,
1149 last_update_date,
1150 last_updated_by,
1151 creation_date,
1152 created_by,
1153 shopper_id,
1154 saved_flag,
1155 approver_id,
1156 approver_name,
1157 deliver_to_requestor_id,
1158 deliver_to_requestor,
1159 need_by_date,
1160 destination_type_code,
1161 destination_organization_id,
1162 deliver_to_location_id,
1163 deliver_to_location,
1164 req_number_segment1,
1165 emergency_flag,
1166 org_id
1167 ) values (
1168 v_cart_id,
1169 v_sysdate,
1170 shopper_id,
1171 v_sysdate,
1172 shopper_id,
1173 shopper_id,
1174 1,
1175 NULL,
1176 NULL,
1177 employee_id,
1178 shopper_name,
1179 v_need_by_date,
1180 'EXPENSE',
1181 v_org_id,
1182 v_location_id,
1183 v_location_code,
1184 v_req_num,
1185 l_emer,
1186 v_oo_id);
1187
1188 insert into icx_cart_distributions (
1189 cart_id,
1190 DISTRIBUTION_ID,
1191 LAST_UPDATED_BY,
1192 LAST_UPDATE_DATE,
1193 LAST_UPDATE_LOGIN,
1194 CREATION_DATE,
1195 CREATED_BY,
1196 ORG_ID) values (
1197 v_cart_id,
1198 v_dist_id,
1199 shopper_id,
1200 v_sysdate,
1201 shopper_id,
1202 v_sysdate,
1203 shopper_id,
1204 v_oo_id);
1205
1206 -- Call user custum defualt for the head
1207 -- icx_req_custom.reqs_validate_head(v_emergency, v_cart_id);
1208 -- Validate changed to default. Sai 8/6/97
1209 icx_req_custom.reqs_default_head(v_emergency, v_cart_id);
1210
1211 end if;
1212
1213 htp.htmlOpen;
1214 htp.headOpen;
1215 icx_util.copyright;
1216 htp.title(c_title);
1217
1218
1219
1220 js.scriptOpen;
1221
1222 ICX_REQ_NAVIGATION.create_multilevel_js_functions(v_language);
1223 js.arrayCreate;
1224 replaceQuotes;
1225
1226 htp.p('// GLOBALS
1227 org_id = "' || icx_call.encrypt2(to_char(v_org_id)) || '";
1228 ');
1229
1230 ICX_REQ_NAVIGATION.synchObject;
1231 js.checkNumber;
1232 js.checkValuePos;
1233 ICX_REQ_NAVIGATION.spin_box;
1234 ICX_REQ_NAVIGATION.giveWarning;
1235 ICX_REQ_NAVIGATION.popWindow;
1236
1237 htp.p('cartId = "' || icx_call.encrypt2(to_char(v_cart_id)) || '";');
1238 htp.p('emergency = "' || icx_call.encrypt2(v_emergency) || '";');
1239 htp.p('account_dist= "";');
1240 htp.p('cartLineId = "";');
1241
1242 ICX_REQ_NAVIGATION.synch(v_org_id,v_emergency,v_cart_id,v_language,shopper_id,v_dcdName);
1243 ICX_REQ_CATEGORIES.GetCategoryTop(v_org_id);
1244 ICX_REQ_TEMPLATES.GetTemplateTop(v_org_id, v_emergency);
1245 js.scriptClose;
1246
1247
1248 htp.headClose;
1249
1250 if (newCart) then
1251 reqNavigator(v_org_id, emergency=> v_emergency, v_dcdName => v_dcdName, v_lang => v_language, v_shopper_id => shopper_id);
1252 else
1253
1254 js.scriptOpen;
1255 htp.p('setparenttab("my_order");');
1256 js.scriptClose;
1257 reqNavigator(v_org_id, v_cart_id, emergency=> v_emergency, v_dcdName => v_dcdName, v_lang => v_language, v_shopper_id => shopper_id);
1258 end if;
1259
1260
1261 htp.htmlClose;
1262
1263 end if;
1264
1265 exception
1266 when others then
1267 htp.p(SQLERRM);
1268
1269 end ic_parent;
1270
1271 ------------------------------------------------------------
1272 function addURL(URL varchar2,
1273 display_text varchar2)
1274 return varchar2 is
1275 ------------------------------------------------------------
1276 v_return varchar2(2000);
1277
1278 begin
1279 if URL is null then
1280 v_return := display_text;
1281 else
1282 v_return := htf.anchor('javascript:top.popWindow(''' || URL || ''')', display_text);
1283 end if;
1284
1285 return v_return;
1286
1287 end addURL;
1288
1289
1290 ------------------------------------------------------------
1291 procedure Copy_Req_to_Cart(p_req_header_id varchar2) is
1292 ------------------------------------------------------------
1293
1294 v_req_header_id number;
1295 v_req_num varchar2(50);
1296 v_dlvr_loc_id number;
1297 v_req_id number;
1298 v_dest_org_id number;
1299 v_dest_code varchar2(25);
1300 v_buyer_note varchar2(240);
1301 v_preparer_id number;
1302 v_req_status varchar2(30);
1303 v_web_user_id number;
1304 v_cart_id number;
1305 v_cart_line_id number;
1306 v_line_dist_id number;
1307 v_emergency varchar2(10);
1308 v_org_id number;
1309 v_dist_num number;
1310
1311 cursor emergency_check(reqheader number) is
1312 -- select header_attribute7
1313 -- from po_requisitions_interface
1314 -- where requisition_header_id = reqheader;
1315 select attribute7
1316 from po_requisition_headers
1317 where requisition_header_id = reqheader;
1318
1319 cursor reqlines(reqheader number) is
1320 select requisition_line_id
1321 from po_requisition_lines
1322 where requisition_header_id = reqheader;
1323
1324 cursor get_item_number(v_cart_id number,v_cart_line_id number) is
1325 select concatenated_segments
1326 from mtl_system_items_kfv a,
1327 icx_shopping_cart_lines b
1328 where a.inventory_item_id = b.item_id
1329 and a.organization_id = b.destination_organization_id
1330 and b.cart_line_id = v_cart_line_id
1331 and b.cart_id = v_cart_id;
1332
1333 cursor reqdistributions(v_cart_id number, v_cart_line_id number) IS
1334 SELECT distribution_id, charge_account_id
1335 FROM icx_cart_line_distributions
1336 WHERE cart_id = v_cart_id
1337 AND cart_line_id = v_cart_line_id;
1338
1339 l_item_number varchar2(80);
1340 l_emer varchar(10);
1341 l_cart_line_number number;
1342 l_shopper_name varchar2(1000);
1343 l_location_id number;
1344 l_location_code varchar2(1000);
1345 l_org_id number;
1346 l_org_code varchar2(1000);
1347 l_dist_num number;
1348 v_activity_name varchar2(1000);
1349
1350 cursor getactname(req_header_id varchar2) is
1351 select pa.instance_label
1352 from wf_item_activity_statuses ias,
1353 wf_process_activities pa,
1354 wf_activities_vl ac,
1355 wf_activities_vl ap,
1356 wf_items i
1357 where ias.item_type = 'POREQ'
1358 and ias.item_key = req_header_id
1359 and ias.process_activity = pa.instance_id
1360 and pa.activity_name = ac.name
1361 and pa.activity_item_type = ac.item_type
1362 and pa.process_name = ap.name
1363 and pa.process_item_type = ap.item_type
1364 and pa.process_version = ap.version
1365 and i.item_type = 'POREQ'
1366 and i.item_key = ias.item_key
1367 and i.begin_date between ac.begin_date and nvl(ac.end_date, i.begin_date)
1368 and ias.activity_status = 'NOTIFIED'
1369 order by ias.execution_time;
1370
1371
1372 begin
1373
1374 -- decrypt parameters
1375 v_req_header_id := icx_call.decrypt(p_req_header_id);
1376
1377 v_org_id := icx_sec.getId(icx_sec.PV_ORG_ID);
1378
1379 -- get the preparer,
1380 select preparer_id, authorization_status
1381 into v_preparer_id, v_req_status
1382 from po_requisition_headers
1383 where requisition_header_id = v_req_header_id;
1384
1385 ICX_REQ_NAVIGATION.shopper_info(v_preparer_id,l_shopper_name,l_location_id,l_location_code,l_org_id,l_org_code);
1386
1387 if (v_req_status = 'CANCELLED') then
1388 icx_util.error_page_setup;
1389 fnd_message.set_name('ICX', 'ICX_REQ_PREV_CANCEL');
1390 icx_util.add_error(fnd_message.get);
1391 htp.htmlOpen;
1392 icx_util.error_page_print;
1393 htp.htmlClose;
1394 else
1395 /* 812757
1396 should add code to call ICX_PO_REQS_CANCEL_SV.update_web_reqs_status
1397 directly instead of poreqwf.doCancel... will implement later..
1398 -- cancel the current req
1399 poreqwf.doCancel('POREQ', v_req_header_id);
1400
1401 v_activity_name := NULL;
1402
1403 for prec in getactname(v_req_header_id) loop
1404 v_activity_name := prec.instance_label;
1405 end loop;
1406
1407 WF_ENGINE.COMPLETEACTIVITY(itemtype => 'POREQ',
1408 itemkey => to_char(v_req_header_id),
1409 activity => v_activity_name,
1410 -- activity => 'PO_REQ_NTF_REJECTED',
1411 -- activity => '14',
1412 result => 'CANCEL');
1413 */
1414 /* this only works in wkflow 2.0
1415 wf_engine.HandleError(itemtype => 'POREQ',
1416 itemkey => to_char(v_req_header_id),
1417 activity => 'PO_RAP',
1418 command => 'SKIP',
1419 result => 'CANCELLED');
1420 */
1421
1422 -- get a new requisition number
1423
1424 select to_char(current_max_unique_identifier +1)
1425 into v_req_num
1426 from po_unique_identifier_control
1427 WHERE table_name = 'PO_REQUISITION_HEADERS'
1428 FOR UPDATE OF current_max_unique_identifier;
1429
1430 UPDATE po_unique_identifier_control
1431 SET current_max_unique_identifier = current_max_unique_identifier+1
1432 WHERE table_name = 'PO_REQUISITION_HEADERS';
1433
1434 commit;
1435
1436 -- get some req line info
1437 -- get info for 1 record only, since current functionality has the
1438 -- same deliver to, dest type, etc. for all lines
1439 select deliver_to_location_id, destination_type_code,
1440 destination_organization_id, note_to_agent
1441 into v_dlvr_loc_id, v_dest_code, v_dest_org_id, v_buyer_note
1442 from po_requisition_lines
1443 where requisition_header_id = v_req_header_id
1444 and rownum = 1;
1445
1446 -- get the web user based on who the preparer was
1447 --USE ICX_SEC.getID API's here.
1448 -- select web_user_id
1449 -- into v_web_user_id
1450 -- from icx_web_users
1451 -- where internal_contact_id = v_preparer_id;
1452
1453 v_web_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
1454
1455 -- check if this is an emergency po
1456 open emergency_check(v_req_header_id);
1457 fetch emergency_check into v_emergency;
1458 close emergency_check;
1459
1460 if v_emergency is NOT NULL then
1461 v_emergency := 'YES';
1462 l_emer := 'Y';
1463 else
1464 v_emergency := NULL;
1465 l_emer := 'N';
1466 end if;
1467
1468 --changed by alex for attachment
1469 -- select icx_shopping_carts_s.nextval
1470 -- into v_cart_id
1471 -- from sys.dual;
1472 --new code:
1473 select PO_REQUISITION_HEADERS_S.nextval
1474 into v_cart_id
1475 from sys.dual;
1476
1477
1478 insert into icx_shopping_carts (
1479 cart_id,
1480 last_update_date,
1481 last_updated_by,
1482 creation_date,
1483 created_by,
1484 shopper_id,
1485 deliver_to_requestor_id,
1486 need_by_date,
1487 destination_type_code,
1488 destination_organization_id,
1489 deliver_to_location_id,
1490 note_to_approver,
1491 note_to_buyer,
1492 saved_flag,
1493 req_number_segment1,
1494 -- approver_id,
1495 -- approver_name,
1496 header_description,
1497 header_attribute_category,
1498 reserved_po_num,
1499 header_attribute1,
1500 header_attribute2,
1501 header_attribute3,
1502 header_attribute4,
1503 header_attribute5,
1504 header_attribute6,
1505 header_attribute7,
1506 header_attribute8,
1507 header_attribute9,
1508 header_attribute10,
1509 header_attribute11,
1510 header_attribute12,
1511 header_attribute13,
1512 header_attribute14,
1513 header_attribute15,
1514 emergency_flag,
1515 deliver_to_location,
1516 deliver_to_requestor,
1517 org_id
1518 ) select
1519 v_cart_id,
1520 sysdate,
1521 rh.last_updated_by,
1522 sysdate,
1523 rh.created_by,
1524 v_web_user_id,
1525 v_preparer_id,
1526 sysdate,
1527 v_dest_code,
1528 v_dest_org_id,
1529 v_dlvr_loc_id,
1530 rh.note_to_authorizer,
1531 v_buyer_note,
1532 3,
1533 v_req_num,
1534 -- approver_id,
1535 -- approver_name,
1536 description,
1537 attribute_category,
1538 attribute7,
1539 attribute1,
1540 attribute2,
1541 attribute3,
1542 attribute4,
1543 attribute5,
1544 attribute6,
1545 attribute7,
1546 attribute8,
1547 attribute9,
1548 attribute10,
1549 attribute11,
1550 attribute12,
1551 attribute13,
1552 attribute14,
1553 attribute15,
1554 l_emer,
1555 l_location_code,
1556 l_shopper_name,
1557 v_org_id
1558 from po_requisition_headers rh
1559 where requisition_header_id = v_req_header_id;
1560
1561 --add by alex
1562 --copy attachment for the header
1563 fnd_attached_documents2_pkg.copy_attachments('PO_REQUISITION_HEADERS',
1564 v_req_header_id,
1565 '',
1566 '',
1567 '',
1568 '',
1569 'PO_REQUISITION_HEADERS',
1570 v_cart_id,
1571 '',
1572 '',
1573 '',
1574 '',
1575 '',
1576 '',
1577 '',
1578 '',
1579 '');
1580
1581 -- insert a default req distribtuion line
1582 insert into icx_cart_distributions
1583 (cart_id,
1584 distribution_id,
1585 last_updated_by,
1586 last_update_date,
1587 last_update_login,
1588 creation_date,
1589 created_by,
1590 org_id)
1591 select
1592 v_cart_id,
1593 icx_cart_distributions_s.nextval,
1594 rh.last_updated_by,
1595 sysdate,
1596 rh.created_by,
1597 sysdate,
1598 rh.created_by,
1599 v_org_id
1600 from po_requisition_headers rh
1601 where requisition_header_id = v_req_header_id;
1602
1603
1604 -- for line_id, insert po_lines.po_line_id if possible; otherwise,
1605 -- insert -999 instead of inserting just null
1606 --
1607 l_cart_line_number := 0;
1608 l_dist_num := 0;
1609 for prec in reqlines(v_req_header_id) loop
1610
1611 l_cart_line_number := l_cart_line_number + 1;
1612
1613 --changed by alex for attachment
1614 -- select icx_shopping_cart_lines_s.nextval into v_cart_line_id from dual;
1615 -- new code:
1616 select PO_REQUISITION_LINES_S.nextval into v_cart_line_id from dual;
1617
1618
1619 insert into icx_shopping_cart_lines (
1620 cart_line_id,
1621 cart_line_number,
1622 last_update_date,
1623 last_updated_by,
1624 creation_date,
1625 created_by,
1626 cart_id,
1627 item_id,
1628 item_revision,
1629 unit_of_measure,
1630 quantity,
1631 unit_price,
1632 suggested_vendor_item_num,
1633 category_id,
1634 line_type_id,
1635 item_description,
1636 suggested_vendor_name,
1637 suggested_vendor_site,
1638 destination_organization_id,
1639 deliver_to_location_id,
1640 autosource_doc_header_id,
1641 autosource_doc_line_num,
1642 -- status_flag,
1643 -- acct_id,
1644 -- acct_num,
1645 line_id,
1646 line_attribute_category,
1647 line_attribute1,
1648 line_attribute2,
1649 line_attribute3,
1650 line_attribute4,
1651 line_attribute5,
1652 line_attribute6,
1653 line_attribute7,
1654 line_attribute8,
1655 line_attribute9,
1656 line_attribute10,
1657 line_attribute11,
1658 line_attribute12,
1659 line_attribute13,
1660 line_attribute14,
1661 line_attribute15,
1662 custom_defaulted,
1663 deliver_to_location,
1664 org_id
1665 ) select
1666 v_cart_line_id,
1667 l_cart_line_number,
1668 sysdate,
1669 rl.last_updated_by,
1670 sysdate,
1671 rl.created_by,
1672 v_cart_id,
1673 rl.item_id,
1674 rl.item_revision,
1675 rl.unit_meas_lookup_code,
1676 rl.quantity,
1677 rl.unit_price,
1678 rl.suggested_vendor_product_code,
1679 rl.category_id,
1680 rl.line_type_id,
1681 rl.item_description,
1682 rl.suggested_vendor_name,
1683 rl.suggested_vendor_location,
1684 rl.destination_organization_id,
1685 rl.deliver_to_location_id,
1686 rl.blanket_po_header_id,
1687 rl.blanket_po_line_num,
1688 -- decode(pl.po_line_id, null, -999, pl.po_line_id),
1689 -999,
1690 rl.attribute_category,
1691 rl.attribute1,
1692 rl.attribute2,
1693 rl.attribute3,
1694 rl.attribute4,
1695 rl.attribute5,
1696 rl.attribute6,
1697 rl.attribute7,
1698 rl.attribute8,
1699 rl.attribute9,
1700 rl.attribute10,
1701 rl.attribute11,
1702 rl.attribute12,
1703 rl.attribute13,
1704 rl.attribute14,
1705 rl.attribute15,
1706 'N',
1707 l_location_code,
1708 v_org_id
1709 from po_requisition_lines rl
1710 where rl.requisition_header_id = v_req_header_id
1711 and rl.requisition_line_id = prec.requisition_line_id;
1712
1713
1714 --add by alex
1715 --copy attachment for the header
1716 fnd_attached_documents2_pkg.copy_attachments('PO_REQUISITION_LINES',
1717 v_req_header_id,
1718 prec.requisition_line_id,
1719 '',
1720 '',
1721 '',
1722 'PO_REQUISITION_LINES',
1723 v_cart_id,
1724 v_cart_line_id,
1725 '',
1726 '',
1727 '',
1728 '',
1729 '',
1730 '',
1731 '',
1732 '');
1733
1734
1735 l_item_number := NULL;
1736 open get_item_number(v_cart_id,v_cart_line_id);
1737 fetch get_item_number into l_item_number;
1738 close get_item_number;
1739
1740 if l_item_number is not NULL then
1741
1742 update icx_shopping_cart_lines
1743 set item_number = l_item_number
1744 where cart_id = v_cart_id
1745 and cart_line_id = v_cart_line_id;
1746
1747 end if;
1748
1749 select icx_cart_line_distributions_s.nextval into v_line_dist_id from dual;
1750 l_dist_num := l_dist_num + 1;
1751
1752 insert into icx_cart_line_distributions
1753 (cart_line_id,
1754 cart_id,
1755 distribution_id,
1756 last_updated_by,
1757 last_update_date,
1758 last_update_login,
1759 creation_date,
1760 created_by,
1761 charge_account_id,
1762 accrual_account_id,
1763 variance_account_id,
1764 budget_account_id,
1765 distribution_num,
1766 allocation_type,
1767 allocation_value,
1768 org_id)
1769 select v_cart_line_id,
1770 v_cart_id,
1771 v_line_dist_id,
1772 rd.last_updated_by,
1773 sysdate,
1774 rd.last_update_login,
1775 sysdate,
1776 rd.created_by,
1777 rd.code_combination_id,
1778 rd.accrual_account_id,
1779 rd.variance_account_id,
1780 rd.budget_account_id,
1781 l_dist_num,
1782 rd.allocation_type,
1783 rd.allocation_value,
1784 v_org_id
1785 from po_req_distributions rd,
1786 po_requisition_lines rl
1787 -- po_lines pl
1788 -- where rd.requisition_line_id = rl.requisition_line_id
1789 where rd.requisition_line_id = prec.requisition_line_id
1790 and rl.requisition_header_id = v_req_header_id
1791 -- and rl.blanket_po_header_id = pl.po_header_id(+)
1792 and rl.requisition_line_id = rd.requisition_line_id;
1793 -- and rl.blanket_po_line_num = pl.line_num(+);
1794
1795 -- call to update charge acount segments based on inserted account id
1796 icx_req_acct2.update_account_by_id(v_cart_id,v_cart_line_id,v_org_id,v_line_dist_id,l_dist_num);
1797
1798
1799 -- Update the distribution num column in distributions table.
1800 -- This is required as the view of ICX_SHOPPING_CART_LINES_V has
1801 -- join condition as DISTRIBUTION_NUM = 1.
1802 -- The reqs from sources other than web reqs may not have populated
1803 -- the distribuion number.
1804
1805 v_dist_num := 1;
1806
1807 FOR distribution IN reqdistributions(v_cart_id, v_cart_line_id) LOOP
1808
1809 UPDATE icx_cart_line_distributions
1810 SET distribution_num = v_dist_num
1811 WHERE cart_id = v_cart_id
1812 AND cart_line_id = v_cart_line_id
1813 AND distribution_id = distribution.distribution_id;
1814
1815 -- Update the invidual segments from the account id.
1816 -- This need to done because the invidual segments are not
1817 -- available from po_req_distributions table.
1818 icx_req_acct2.update_account_by_id( v_cart_id => v_cart_id,
1819 v_cart_line_id => v_cart_line_id,
1820 v_oo_id => v_org_id,
1821 v_distribution_id => distribution.distribution_id,
1822 v_line_number => v_dist_num);
1823
1824 v_dist_num := v_dist_num + 1;
1825
1826 END LOOP; /* FOR distribution */
1827
1828 end loop;
1829
1830 -- fill in cart id here. I suggest using a new one
1831 -- Show the page, displaying this new shopping cart
1832 if v_emergency is NULL then
1833 ICX_REQ_NAVIGATION.ic_parent(icx_call.encrypt2(to_char(v_cart_id)));
1834 else
1835 ICX_REQ_NAVIGATION.ic_parent(icx_call.encrypt2(to_char(v_cart_id)),icx_call.encrypt2(v_emergency));
1836 end if;
1837
1838 end if; -- v_req_status
1839
1840 end Copy_Req_to_Cart;
1841
1842 ------------------------------------------------------------
1843 procedure get_currency(v_org in number,
1844 v_currency out varchar2,
1845 v_precision out number,
1846 v_fmt_mask out varchar2) is
1847 ------------------------------------------------------------
1848 cursor getCurrency is
1849 select gsob.CURRENCY_CODE,
1850 fc.PRECISION
1851 from gl_sets_of_books gsob,
1852 FND_CURRENCIES fc,
1853 org_organization_definitions ood
1854 where ood.ORGANIZATION_ID = v_org
1855 and fc.CURRENCY_CODE = gsob.CURRENCY_CODE
1856 and ood.SET_OF_BOOKS_ID = gsob.SET_OF_BOOKS_ID;
1857
1858 i number := 0;
1859 v_return varchar2(32);
1860 begin
1861
1862 open getCurrency;
1863 fetch getCurrency into v_currency, v_precision;
1864 close getCurrency;
1865
1866
1867 v_return := '999999999D';
1868 for i in 1 .. v_precision loop
1869 v_return := v_return || '9';
1870 end loop;
1871 v_fmt_mask := v_return;
1872
1873 end get_currency;
1874
1875
1876
1877 end ICX_REQ_NAVIGATION;