[Home] [Help]
PACKAGE BODY: APPS.ICX_REQ_SUBMIT
Source
1 PACKAGE BODY ICX_REQ_SUBMIT as
2 /* $Header: ICXRQSMB.pls 115.5 99/07/17 03:23:22 porting sh $ */
3
4 -------------------------------------------------------------
5 procedure storeerror(v_cart_id IN NUMBER, v_message IN VARCHAR2,v_distribution_num IN NUMBER default NULL,v_cart_line_id IN NUMBER default NULL) is
6 -------------------------------------------------------------
7 l_error_id NUMBER;
8 l_err_num NUMBER;
9 l_error_message VARCHAR2(2000);
10 l_err_mesg VARCHAR2(240);
11
12 l_shopper_id NUMBER;
13
14 begin
15
16 l_shopper_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
17
18 select icx_req_cart_errors_s.nextval into l_error_id from dual;
19
20 /* append new message to the error table for redisplay */
21 insert into icx_req_cart_errors
22 (error_id,cart_id,distribution_num,cart_line_id,last_updated_by,last_update_date,last_update_login,creation_date,created_by,error_text)
23 values(l_error_id,v_cart_id,v_distribution_num,v_cart_line_id,null,sysdate,l_shopper_id,sysdate,l_shopper_id,v_message);
24
25
26 exception
27 when others then
28 l_err_num := SQLCODE;
29 l_error_message := SQLERRM;
30
31 select substr(l_error_message,12,512) into l_err_mesg from dual;
32 icx_util.add_error(l_err_mesg);
33 end;
34
35
36 ------------------------------------------------------------
37 procedure become_top(n_cart_id varchar2,
38 v_emergency varchar2,
39 user_action varchar2) is
40 ------------------------------------------------------------
41
42 begin
43 htp.p('<BODY onLoad="open(''ICX_REQ_SUBMIT.show_end?n_cart_id=' ||
44 n_cart_id || '&v_emergency=' || v_emergency ||
45 '&user_action=' || user_action || ''', ''_top'')">');
46 end;
47
48
49 ------------------------------------------------------------
50 procedure display_read_only_my_order( cart_id number,p_emergency varchar2 ) is
51 ------------------------------------------------------------
52
53 vvalue varchar2(240);
54 v_regions_table ak_query_pkg.regions_table_type;
55 v_items_table ak_query_pkg.items_table_type;
56 v_results_table ak_query_pkg.results_table_type;
57
58 shopper_id number;
59
60 v_where_clause varchar2(240);
61 y_table icx_util.char240_table;
62 l_value varchar2(240);
63 i number := 0;
64 j number := 0;
65 column_number number := 0;
66 v_order_total number := 0;
67 v_unit_price number := 0;
68 v_quantity number := 0;
69 v_header_region varchar2(100);
70 v_lines_region varchar2(100);
71 v_lang varchar2(5);
72
73 col_no number := 1;
74 v_ext_price_is_on boolean := FALSE;
75
76 v_total_h_align varchar2(100);
77 v_total_v_align varchar2(100);
78
79 v_location_id number;
80 v_location_code varchar2(20);
81 v_item_number varchar2(80) := null;
82 v_item_id number := null;
83 v_dest_org_id number := null;
84 v_org number;
85 v_requestor_id number := null;
86 v_requestor_name varchar2(240) := null;
87 v_currency varchar2(30);
88 v_precision number;
89 v_money_fmt_mask varchar2(32);
90
91 /* Change wrto Bug Fix to implement the Bind Vars **/
92 where_clause_binds ak_query_pkg.bind_tab;
93 where_clause_binds_empty ak_query_pkg.bind_tab;
94 v_index NUMBER;
95
96
97 cursor getLoccd(locid number) is
98 select hrl.location_code
99 from hr_locations hrl,
100 org_organization_definitions ood,
101 financials_system_parameters fsp
102 where hrl.location_id = locid
103 and ood.organization_id = nvl(hrl.inventory_organization_id,
104 fsp.inventory_organization_id)
105 and sysdate < nvl(hrl.inactive_date,sysdate + 1);
106
107 cursor requestor_name(v_employee_id number) is
108 select full_name
109 from HR_EMPLOYEES_CURRENT_V
110 where employee_id = v_employee_id;
111
112 cursor item_names(id number, org number) is
113 select concatenated_segments
114 from mtl_system_items_kfv
115 where INVENTORY_ITEM_ID = id
116 and ORGANIZATION_ID = org;
117
118 cursor getCurrency is
119 select gsob.CURRENCY_CODE,
120 fc.PRECISION
121 from gl_sets_of_books gsob,
122 FND_CURRENCIES fc,
123 org_organization_definitions ood
124 where ood.ORGANIZATION_ID = v_org
125 and fc.CURRENCY_CODE = gsob.CURRENCY_CODE
126 and ood.SET_OF_BOOKS_ID = gsob.SET_OF_BOOKS_ID;
127
128 v_cart_id number;
129
130 l_cart_line_id_value number;
131 v_vendor_LOV_flag varchar2(1);
132 v_location_LOV_flag varchar2(1);
133
134 begin
135
136 -- clean up errors
137 v_cart_id := cart_id;
138 v_index := 1;
139
140 delete icx_req_cart_errors
141 where cart_id = v_cart_id;
142
143 -- get shopper id
144 shopper_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
145 -- get lang
146 v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
147
148 if p_emergency = 'YES' then
149 v_header_region := 'ICX_SHOPPING_CART_HEADER_EMG_R';
150 v_lines_region := 'ICX_SHOPPING_CART_LINES_EMG_R';
151 else
152 v_header_region := 'ICX_SHOPPING_CART_HEADER_R';
153 v_lines_region := 'ICX_SHOPPING_CART_LINES_R';
154 end if;
155
156
157 -- Where clause
158 -- v_where_clause := 'SHOPPER_ID = ' || shopper_id || 'AND CART_ID = ' || cart_id;
159 v_where_clause := 'SHOPPER_ID = :shopper_id_bin AND CART_ID = :cart_id_bin';
160 /* added code to take care of Bind vars Bug **/
161 where_clause_binds(v_index).name := 'shopper_id_bin';
162 where_clause_binds(v_index).value := shopper_id;
163 v_index := v_index + 1;
164 where_clause_binds(v_index).name := 'cart_id_bin';
165 where_clause_binds(v_index).value := cart_id;
166 v_index := v_index + 1;
167
168
169
170 htp.hr;
171
172 -- Cart Header Related Object Navigator
173 -- ^^^^^^^^^^
174 --
175 ak_query_pkg.exec_query(P_PARENT_REGION_APPL_ID => 601,
176 P_PARENT_REGION_CODE => v_header_region,
177 P_RESPONSIBILITY_ID => icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
178 P_USER_ID => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
179 P_WHERE_CLAUSE => v_where_clause,
180 P_RETURN_PARENTS => 'T',
181 P_RETURN_CHILDREN => 'F',
182 P_WHERE_BINDS => where_clause_binds);
183
184 -- Draw cart header.
185 htp.tableOpen( 'border=0' );
186
187 icx_util.transfer_Row_To_Column( ak_query_pkg.g_results_table(ak_query_pkg.g_results_table.first), y_table) ;
188
189 /* get org ahead of time */
190 for k in ak_query_pkg.g_items_table.first .. ak_query_pkg.g_items_table.last loop
191 if ak_query_pkg.g_items_table(k).attribute_code = 'ICX_DEST_ORG_ID' then
192 v_org := y_table(ak_query_pkg.g_items_table(k).value_id);
193 exit;
194 end if;
195 end loop;
196
197 v_currency := NULL;
198 if v_org is not NULL then
199
200 ICX_REQ_NAVIGATION.get_currency(v_org, v_currency, v_precision, v_money_fmt_mask);
201 v_money_fmt_mask := FND_CURRENCY.GET_FORMAT_MASK(v_currency,30);
202 end if;
203
204
205 for i in ak_query_pkg.g_items_table.first .. ak_query_pkg.g_items_table.last loop
206
207
208 if ( ak_query_pkg.g_items_table(i).item_style <> 'HIDDEN' and
209 ak_query_pkg.g_items_table(i).node_display_flag = 'Y' ) OR
210 (ak_query_pkg.g_items_table(i).attribute_code = 'ICX_DELIVER_TO_LOCATION_ID' OR
211 ak_query_pkg.g_items_table(i).attribute_code = 'ICX_DELIVER_TO_REQUESTOR_ID' OR
212 ak_query_pkg.g_items_table(i).attribute_code = 'ICX_DEST_ORG_ID') then
213
214 if ak_query_pkg.g_items_table(i).value_id is not null then -- It is an object attribute
215
216 if(ak_query_pkg.g_items_table(i).attribute_code = 'ICX_DEST_ORG_ID') then
217 v_org := y_table(ak_query_pkg.g_items_table(i).value_id);
218 elsif(ak_query_pkg.g_items_table(i).attribute_code =
219 'ICX_DELIVER_TO_LOCATION_ID') then
220 -- get the location id to find the location code
221 v_location_id := y_table(ak_query_pkg.g_items_table(i).value_id);
222 elsif (ak_query_pkg.g_items_table(i).attribute_code =
223 'ICX_DELIVER_TO_REQUESTOR_ID') then
224 v_requestor_id := y_table(ak_query_pkg.g_items_table(i).value_id);
225 else
226 htp.tableRowOpen;
227 htp.tableData(cvalue => ak_query_pkg.g_items_table(i).attribute_label_long, calign => 'RIGHT', cattributes=>'VALIGN=CENTER');
228 htp.tableData(cvalue => ' ');
229 htp.p('<TD border=1 bgcolor=#FFFFFF>');
230 htp.p('<B>');
231 if ak_query_pkg.g_items_table(i).italic = 'Y' then
232 htp.p('<I>');
233 end if;
234 htp.p(y_table(ak_query_pkg.g_items_table(i).value_id));
235 if ak_query_pkg.g_items_table(i).italic = 'Y' then
236 htp.p('</I>');
237 end if;
238 htp.p('</B>');
239
240 htp.tableRowClose;
241 end if;
242
243 else
244 -- this is a regular attribute
245
246 if(ak_query_pkg.g_items_table(i).attribute_code =
247 'ICX_DELIVER_TO_LOCATION') then
248 open getLoccd(v_location_id);
249 fetch getLoccd into v_location_code;
250 close getLoccd;
251 htp.tableRowOpen;
252 htp.tableData(cvalue => ak_query_pkg.g_items_table(i).attribute_label_long, calign => 'RIGHT', cattributes=>'VALIGN=CENTER');
253 htp.tableData(cvalue => ' ');
254 htp.p('<TD border=1 bgcolor=#FFFFFF>');
255 if ak_query_pkg.g_items_table(i).bold = 'Y' then
256 htp.p('<B>');
257 end if;
258 if ak_query_pkg.g_items_table(i).italic = 'Y' then
259 htp.p('<I>');
260 end if;
261 htp.p(v_location_code);
262 if ak_query_pkg.g_items_table(i).italic = 'Y' then
263 htp.p('</I>');
264 end if;
265 if ak_query_pkg.g_items_table(i).bold = 'Y' then
266 htp.p('</B>');
267 end if;
268 htp.tableRowClose;
269 elsif(ak_query_pkg.g_items_table(i).attribute_code =
270 'ICX_DELIVER_TO_REQUESTOR') then
271 open requestor_name(v_requestor_id);
272 fetch requestor_name into v_requestor_name;
273 close requestor_name;
274 htp.tableRowOpen;
275 htp.tableData(cvalue => ak_query_pkg.g_items_table(i).attribute_label_long, calign => 'RIGHT', cattributes=>'VALIGN=CENTER');
276 htp.tableData(cvalue => ' ');
277 htp.p('<TD border=1 bgcolor=#FFFFFF>');
278 if ak_query_pkg.g_items_table(i).bold = 'Y' then
279 htp.p('<B>');
280 end if;
281 if ak_query_pkg.g_items_table(i).italic = 'Y' then
282 htp.p('<I>');
283 end if;
284 htp.p(v_requestor_name);
285 if ak_query_pkg.g_items_table(i).italic = 'Y' then
286 htp.p('</I>');
287 end if;
288 if ak_query_pkg.g_items_table(i).bold = 'Y' then
289 htp.p('</B>');
290 end if;
291 htp.tableRowClose;
292 end if;
293
294 end if;
295
296 end if;
297
298 end loop;
299
300 htp.tableClose;
301 htp.p('<BR>');
302
303 -- Cart Lines Related Object Navigator
304 -- ^^^^^^^^^^
305 --
306 ak_query_pkg.exec_query(P_PARENT_REGION_APPL_ID => 601,
307 P_PARENT_REGION_CODE => v_lines_region,
308 P_RESPONSIBILITY_ID => icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
309 P_USER_ID => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
310 P_WHERE_CLAUSE => v_where_clause,
311 P_RETURN_PARENTS => 'T',
312 P_RETURN_CHILDREN => 'F',
313 P_WHERE_BINDS => where_clause_binds);
314
315 -- Preprocess and mask the required flags for mandatory fields
316
317 -- ICX_REQ_ORDER.ak_mandatory_setup(l_cart_line_id_value,v_vendor_LOV_flag,v_location_LOV_flag);
318
319 -- Draw cart lines.
320
321 htp.tableOpen('BORDER=5', cattributes=> 'bgcolor="#F8F8F8"');
322
323 -- PrintHead
324 htp.p('<TR BGCOLOR="#' || icx_util.get_color('TABLE_HEADER') || '">');
325
326 col_no := 0;
327 for i in 0 .. ak_query_pkg.g_items_table.LAST loop
328 if ak_query_pkg.g_items_table(i).node_display_flag = 'Y'
329 and ak_query_pkg.g_items_table(i).secured_column = 'F'
330 then
331 if ak_query_pkg.g_items_table(i).item_style = 'HIDDEN'
332 or ak_query_pkg.g_items_table(i).item_style = 'IMAGE'
333 then
334 null;
335 else
336 if ak_query_pkg.g_items_table(i).attribute_code = 'ICX_UNIT_PRICE' or
337 ak_query_pkg.g_items_table(i).attribute_code = 'ICX_EXT_PRICE' then
338 htp.tableData(cvalue => ak_query_pkg.g_items_table(i).attribute_label_long || ' (' || v_currency || ')', calign => 'CENTER');
339 else
340 htp.tableData(cvalue => ak_query_pkg.g_items_table(i).attribute_label_long, calign => 'CENTER');
341 end if;
342
343 col_no := col_no + 1;
344 if ak_query_pkg.g_items_table(i).attribute_code = 'ICX_EXT_PRICE'
345 then
346 v_ext_price_is_on := TRUE;
347 column_number := col_no;
348 end if;
349 end if;
350 end if;
351 end loop;
352 htp.p('</TR><TR></TR><TR></TR><TR></TR>');
353 -- end PrintHead;
354
355 -- PrintItems
356 v_order_total := 0;
357 if ak_query_pkg.g_regions_table(0).total_result_count > 0 then
358 for j in 0 .. ak_query_pkg.g_results_table.last loop
359 icx_util.transfer_Row_To_Column( ak_query_pkg.g_results_table(j), y_table);
360
361 htp.tableRowOpen;
362 for i in 0 .. ak_query_pkg.g_items_table.LAST loop
363 if ak_query_pkg.g_items_table(i).node_display_flag = 'Y'
364 and ak_query_pkg.g_items_table(i).secured_column = 'F'
365 then
366 if ak_query_pkg.g_items_table(i).item_style = 'HIDDEN'
367 or ak_query_pkg.g_items_table(i).item_style = 'IMAGE'
368 then
369 null;
370 else
371 if ak_query_pkg.g_items_table(i).value_id is null
372 then
373 l_value := '';
374 else
375 l_value := y_table(ak_query_pkg.g_items_table(i).value_id);
376
377 /* compute price total */
378 if ak_query_pkg.g_items_table(i).attribute_code = 'ICX_EXT_PRICE'
379 then
380 v_order_total := v_order_total + to_number(nvl(l_value,0));
381 l_value := to_char(to_number(l_value),v_money_fmt_mask);
382 end if;
383 end if;
384
385 htp.tableData(cvalue => icx_on_utilities.formatText(l_value,ak_query_pkg.g_items_table(i).bold,ak_query_pkg.g_items_table(i).italic),
386 calign => ak_query_pkg.g_items_table(i).horizontal_alignment, cattributes => 'VALIGN="'||ak_query_pkg.g_items_table(i).vertical_alignment||'"');
387
388 end if;
389 end if;
390 end loop;
391
392 htp.tableRowClose;
393
394 end loop;
395 end if;
396
397 -- End PrintItems
398
399 if v_ext_price_is_on then
400 -- PrintTotal
401 --
402 -- Try to place total under extended price
403 --
404
405 htp.p('<TR></TR><TR></TR><TR></TR>');
406
407 htp.p('<TR>');
408 for i in 1 .. (column_number - 2) loop
409 htp.p('<TD></TD>');
410 end loop;
411
412 FND_MESSAGE.SET_NAME('MRP','EC_TOTAL');
413 htp.p('<TD ALIGN=RIGHT BGCOLOR="#' || icx_util.get_color('TABLE_HEADER') ||'" >' || FND_MESSAGE.GET || ' (' || v_currency || ') </TD>');
414 htp.p('<TD ALIGN="RIGHT">' || to_char(to_number(v_order_total),v_money_fmt_mask) || '</TD>' );
415
416 htp.p('</TR>');
417
418 -- End PrintTotal
419 end if;
420
421 htp.tableClose;
422
423 -- new order button
424 htp.tableOpen('border=0');
425 htp.tableRowOpen;
426 FND_MESSAGE.SET_NAME('ICX','ICX_REQ_AFTER_SUBMIT');
427 htp.p('<TD ALIGN=LEFT>' || FND_MESSAGE.GET || '</TD>');
428 htp.p('<TD>');
429 htp.p('</TR><TR>');
430 htp.p('<TD>');
431 FND_MESSAGE.SET_NAME('FND','YES');
432 icx_util.DynamicButton(P_ButtonText => FND_MESSAGE.GET,
433 P_ImageFileName => 'FNDBNEW.gif',
434 P_OnMouseOverText => FND_MESSAGE.GET,
435 P_HyperTextCall => 'ICX_REQ_NAVIGATION.ic_parent?cart_id=' || icx_call.encrypt2('0') || '&emergency=' || icx_call.encrypt2(p_emergency),
436 P_LanguageCode => v_lang,
437 P_JavaScriptFlag => FALSE);
438 htp.p('</TD>');
439
440 htp.p('<TD>');
441 FND_MESSAGE.SET_NAME('FND','NO');
442 icx_util.DynamicButton(P_ButtonText => FND_MESSAGE.GET,
443 P_ImageFileName => 'FNDBCNCL.gif',
444 P_OnMouseOverText => FND_MESSAGE.GET,
445 P_HyperTextCall => 'OracleApps.DMM',
446 P_LanguageCode => v_lang,
447 P_JavaScriptFlag => FALSE);
448 htp.p('</TD>');
449
450 htp.tableRowClose;
451 htp.tableClose;
452
453 end display_read_only_my_order;
454
455
456 --**********************************************************
457 -- END PROCEDURES RELATED TO READ ONLY CART/MY ORDER DISPLAY
458 --**********************************************************
459
460 ------------------------------------------------------------
461 procedure show_end(n_cart_id varchar2,
462 v_emergency varchar2,
463 user_action varchar2) is
464 ------------------------------------------------------------
465 v_lang varchar2(10);
466
467 v_cart_id number;
468 v_req_num VARCHAR2(30);
469
470
471 cursor get_req_num(l_cart_id number) is
472 select REQ_NUMBER_SEGMENT1
473 from icx_shopping_carts
474 where CART_ID = l_cart_id;
475 begin
476 if (icx_sec.validatesession()) then
477
478 -- get lang
479 v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
480 v_cart_id := icx_call.decrypt2(n_cart_id);
481
482 htp.htmlOpen;
483 htp.headOpen;
484 icx_admin_sig.toolbar(language_code => v_lang);
485 icx_util.copyright;
486 js.scriptOpen;
487
488 htp.p('function help_window() {
489 help_win = window.open(''/OA_DOC/' || v_lang || '/awe' || '/icxhlprq.htm'', "help_win","resizable=yes,scrollbars=yes,toolbar=yes,width=450,height=250");
490 help_win = window.open(''/OA_DOC/' || v_lang || '/awe' || '/icxhlprq.htm'', "help_win","resizable=yes,scrollbars=yes,toolbar=yes,width=450,height=250")}
491 ');
492 js.scriptClose;
493
494
495 htp.headClose;
496 htp.bodyOpen('/OA_MEDIA/' || v_lang || '/ICXBCKGR.jpg');
497
498 if (user_action = 'PO') then
499 open get_req_num(v_cart_id);
500 fetch get_req_num into v_req_num;
501 close get_req_num;
502 FND_MESSAGE.SET_NAME('ICX','ICX_REQ_SUBMITTED');
503 FND_MESSAGE.SET_TOKEN('REQ_TOKEN',v_req_num);
504 htp.p('<b><font size=+1><p></p>' || FND_MESSAGE.GET || '</font><p>');
505
506 display_read_only_my_order(v_cart_id,v_emergency);
507
508 -- call procedure to display read only cart
509 elsif (user_action = 'SAVE') then
510 FND_MESSAGE.SET_NAME('ICX','ICX_REQ_SAVED_FOR_LATER');
511 htp.p('<b><font size=+1><p></p>' || FND_MESSAGE.GET || '</font><p>');
512
513 -- call procedure to display read only cart
514 display_read_only_my_order(v_cart_id,v_emergency);
515
516 htp.comment(user_action);
517 elsif (user_action = 'CANCEL') then
518 FND_MESSAGE.SET_NAME('ICX','ICX_REQ_PREV_CANCEL');
519 htp.p('<b><font size=+1><p></p>' || FND_MESSAGE.GET || '</font><p>');
520
521 htp.tableOpen('border=0');
522 htp.tableRowOpen;
523 FND_MESSAGE.SET_NAME('ICX','ICX_REQ_AFTER_SUBMIT');
524 htp.p('<TD ALIGN=LEFT>' || FND_MESSAGE.GET || '</TD>');
525 htp.p('<TD>');
526 htp.p('</TR><TR>');
527 htp.p('<TD>');
528 FND_MESSAGE.SET_NAME('FND','YES');
529 icx_util.DynamicButton(P_ButtonText => FND_MESSAGE.GET,
530 P_ImageFileName => 'FNDBNEW.gif',
531 P_OnMouseOverText => FND_MESSAGE.GET,
532 P_HyperTextCall => 'ICX_REQ_NAVIGATION.ic_parent?cart_id=' || icx_call.encrypt2('0') || '&emergency=' || icx_call.encrypt2(v_emergency),
533 P_LanguageCode => v_lang,
534 P_JavaScriptFlag => FALSE);
535 htp.p('</TD>');
536 htp.p('<TD>');
537 FND_MESSAGE.SET_NAME('FND','NO');
538 icx_util.DynamicButton(P_ButtonText => FND_MESSAGE.GET,
539 P_ImageFileName => 'FNDBCNCL.gif',
540 P_OnMouseOverText => FND_MESSAGE.GET,
541 P_HyperTextCall => 'OracleApps.DMM',
542 P_LanguageCode => v_lang,
543 P_JavaScriptFlag => FALSE);
544 htp.p('</TD>');
545
546 htp.tableRowClose;
547 htp.tableClose;
548
549 else
550
551 FND_MESSAGE.SET_NAME('ICX', 'ICX_CART_EXIST');
552 htp.p('<BODY> ' || FND_MESSAGE.GET);
553 htp.p('</BODY>');
554
555
556
557 end if;
558
559 htp.bodyClose;
560 htp.htmlClose;
561 end if;
562
563 EXCEPTION
564 when others then
565 htp.p(SQLERRM);
566
567
568 end;
569
570
571 -------------------------------------------------------------
572 procedure finalSubmit(user_action varchar2,
573 icx_cart_id varchar2,
574 emergency IN varchar2 default NULL,
575 icx_approver_id varchar2 default FND_API.G_MISS_CHAR,
576 icx_approver_name varchar2 default FND_API.G_MISS_CHAR,
577 icx_deliver_to_location_id varchar2 default FND_API.G_MISS_CHAR,
578 icx_deliver_to_requestor_id varchar2 default FND_API.G_MISS_CHAR,
579 icx_dest_org_id varchar2 default FND_API.G_MISS_CHAR,
580 icx_shopper_id varchar2 default FND_API.G_MISS_CHAR,
581 icx_deliver_to_location varchar2 default FND_API.G_MISS_CHAR,
582 icx_deliver_to_requestor varchar2 default FND_API.G_MISS_CHAR,
583 icx_need_by_date varchar2 default FND_API.G_MISS_CHAR,
584 icx_note_to_approver varchar2 default FND_API.G_MISS_CHAR,
585 icx_note_to_buyer varchar2 default FND_API.G_MISS_CHAR,
586 icx_header_description varchar2 default FND_API.G_MISS_CHAR,
587 icx_req_org_id varchar2 default FND_API.G_MISS_CHAR,
588 icx_req_loc_id varchar2 default FND_API.G_MISS_CHAR,
589 icx_req_loc_cd varchar2 default FND_API.G_MISS_CHAR,
590 icx_header_attribute1 varchar2 default FND_API.G_MISS_CHAR,
591 icx_header_attribute2 varchar2 default FND_API.G_MISS_CHAR,
592 icx_header_attribute3 varchar2 default FND_API.G_MISS_CHAR,
593 icx_header_attribute4 varchar2 default FND_API.G_MISS_CHAR,
594 icx_header_attribute5 varchar2 default FND_API.G_MISS_CHAR,
595 icx_header_attribute6 varchar2 default FND_API.G_MISS_CHAR,
596 icx_header_attribute7 varchar2 default FND_API.G_MISS_CHAR,
597 icx_header_attribute8 varchar2 default FND_API.G_MISS_CHAR,
598 icx_header_attribute9 varchar2 default FND_API.G_MISS_CHAR,
599 icx_header_attribute10 varchar2 default FND_API.G_MISS_CHAR,
600 icx_header_attribute11 varchar2 default FND_API.G_MISS_CHAR,
601 icx_header_attribute12 varchar2 default FND_API.G_MISS_CHAR,
602 icx_header_attribute13 varchar2 default FND_API.G_MISS_CHAR,
603 icx_header_attribute14 varchar2 default FND_API.G_MISS_CHAR,
604 icx_header_attribute15 varchar2 default FND_API.G_MISS_CHAR,
605 icx_charge_acct_seg1 varchar2 default FND_API.G_MISS_CHAR,
606 icx_charge_acct_seg2 varchar2 default FND_API.G_MISS_CHAR,
607 icx_charge_acct_seg3 varchar2 default FND_API.G_MISS_CHAR,
608 icx_charge_acct_seg4 varchar2 default FND_API.G_MISS_CHAR,
609 icx_charge_acct_seg5 varchar2 default FND_API.G_MISS_CHAR,
610 icx_charge_acct_seg6 varchar2 default FND_API.G_MISS_CHAR,
611 icx_charge_acct_seg7 varchar2 default FND_API.G_MISS_CHAR,
612 icx_charge_acct_seg8 varchar2 default FND_API.G_MISS_CHAR,
613 icx_charge_acct_seg9 varchar2 default FND_API.G_MISS_CHAR,
614 icx_charge_acct_seg10 varchar2 default FND_API.G_MISS_CHAR,
615 icx_charge_acct_seg11 varchar2 default FND_API.G_MISS_CHAR,
616 icx_charge_acct_seg12 varchar2 default FND_API.G_MISS_CHAR,
617 icx_charge_acct_seg13 varchar2 default FND_API.G_MISS_CHAR,
618 icx_charge_acct_seg14 varchar2 default FND_API.G_MISS_CHAR,
619 icx_charge_acct_seg15 varchar2 default FND_API.G_MISS_CHAR,
620 icx_charge_acct_seg16 varchar2 default FND_API.G_MISS_CHAR,
621 icx_charge_acct_seg17 varchar2 default FND_API.G_MISS_CHAR,
622 icx_charge_acct_seg18 varchar2 default FND_API.G_MISS_CHAR,
623 icx_charge_acct_seg19 varchar2 default FND_API.G_MISS_CHAR,
624 icx_charge_acct_seg20 varchar2 default FND_API.G_MISS_CHAR,
625 icx_charge_acct_seg21 varchar2 default FND_API.G_MISS_CHAR,
626 icx_charge_acct_seg22 varchar2 default FND_API.G_MISS_CHAR,
627 icx_charge_acct_seg23 varchar2 default FND_API.G_MISS_CHAR,
628 icx_charge_acct_seg24 varchar2 default FND_API.G_MISS_CHAR,
629 icx_charge_acct_seg25 varchar2 default FND_API.G_MISS_CHAR,
630 icx_charge_acct_seg26 varchar2 default FND_API.G_MISS_CHAR,
631 icx_charge_acct_seg27 varchar2 default FND_API.G_MISS_CHAR,
632 icx_charge_acct_seg28 varchar2 default FND_API.G_MISS_CHAR,
633 icx_charge_acct_seg29 varchar2 default FND_API.G_MISS_CHAR,
634 icx_charge_acct_seg30 varchar2 default FND_API.G_MISS_CHAR,
635 icx_cart_line_ida IN defaultParamType default empty_table,
636 icx_category_ida IN defaultParamType default empty_table,
637 icx_category_namea IN defaultParamType default empty_table,
638 icx_item_ida IN defaultParamType default empty_table,
639 icx_item_reva IN defaultParamType default empty_table,
640 icx_need_by_datea IN defaultParamType default empty_table,
641 icx_item_descriptiona IN defaultParamType default empty_table,
642 icx_expend_item_datea IN defaultParamType default empty_table,
643 icx_expend_orga IN defaultParamType default empty_table,
644 icx_qty_va IN defaultParamType default empty_table,
645 icx_suggested_buyer_ida IN defaultParamType default empty_table,
646 icx_project_ida IN defaultParamType default empty_table,
647 icx_suggested_vendor_contacta IN defaultParamType default empty_table,
648 icx_suggested_vendor_item_numa IN defaultParamType default empty_table,
649 icx_suggested_vendor_namea IN defaultParamType default empty_table,
650 icx_suggested_vendor_phonea IN defaultParamType default empty_table,
651 icx_suggested_vendor_sitea IN defaultParamType default empty_table,
652 icx_task_ida IN defaultParamType default empty_table,
653 icx_unit_of_measurementa IN defaultParamType default empty_table,
654 icx_unit_pricea IN defaultParamType default empty_table,
655 icx_deliver_to_location_id_la IN defaultParamType default empty_table,
656 icx_dest_org_id_la IN defaultParamType default empty_table,
657 icx_deliver_to_location_la IN defaultParamType default empty_table,
658 icx_line_attribute_1a IN defaultParamType default empty_table,
659 icx_line_attribute_2a IN defaultParamType default empty_table,
660 icx_line_attribute_3a IN defaultParamType default empty_table,
661 icx_line_attribute_4a IN defaultParamType default empty_table,
662 icx_line_attribute_5a IN defaultParamType default empty_table,
663 icx_line_attribute_6a IN defaultParamType default empty_table,
664 icx_line_attribute_7a IN defaultParamType default empty_table,
665 icx_line_attribute_8a IN defaultParamType default empty_table,
666 icx_line_attribute_9a IN defaultParamType default empty_table,
667 icx_line_attribute_10a IN defaultParamType default empty_table,
668 icx_line_attribute_11a IN defaultParamType default empty_table,
669 icx_line_attribute_12a IN defaultParamType default empty_table,
670 icx_line_attribute_13a IN defaultParamType default empty_table,
671 icx_line_attribute_14a IN defaultParamType default empty_table,
672 icx_line_attribute_15a IN defaultParamType default empty_table,
673 icx_charge_acct_seg1a IN defaultParamType default empty_table,
674 icx_charge_acct_seg2a IN defaultParamType default empty_table,
675 icx_charge_acct_seg3a IN defaultParamType default empty_table,
676 icx_charge_acct_seg4a IN defaultParamType default empty_table,
677 icx_charge_acct_seg5a IN defaultParamType default empty_table,
678 icx_charge_acct_seg6a IN defaultParamType default empty_table,
679 icx_charge_acct_seg7a IN defaultParamType default empty_table,
680 icx_charge_acct_seg8a IN defaultParamType default empty_table,
681 icx_charge_acct_seg9a IN defaultParamType default empty_table,
682 icx_charge_acct_seg10a IN defaultParamType default empty_table,
683 icx_charge_acct_seg11a IN defaultParamType default empty_table,
684 icx_charge_acct_seg12a IN defaultParamType default empty_table,
685 icx_charge_acct_seg13a IN defaultParamType default empty_table,
686 icx_charge_acct_seg14a IN defaultParamType default empty_table,
687 icx_charge_acct_seg15a IN defaultParamType default empty_table,
688 icx_charge_acct_seg16a IN defaultParamType default empty_table,
689 icx_charge_acct_seg17a IN defaultParamType default empty_table,
690 icx_charge_acct_seg18a IN defaultParamType default empty_table,
691 icx_charge_acct_seg19a IN defaultParamType default empty_table,
692 icx_charge_acct_seg20a IN defaultParamType default empty_table,
693 icx_charge_acct_seg21a IN defaultParamType default empty_table,
694 icx_charge_acct_seg22a IN defaultParamType default empty_table,
695 icx_charge_acct_seg23a IN defaultParamType default empty_table,
696 icx_charge_acct_seg24a IN defaultParamType default empty_table,
697 icx_charge_acct_seg25a IN defaultParamType default empty_table,
698 icx_charge_acct_seg26a IN defaultParamType default empty_table,
699 icx_charge_acct_seg27a IN defaultParamType default empty_table,
700 icx_charge_acct_seg28a IN defaultParamType default empty_table,
701 icx_charge_acct_seg29a IN defaultParamType default empty_table,
702 icx_charge_acct_seg30a IN defaultParamType default empty_table,
703 entity_name IN varchar2 default NULL,
704 pk1 IN varchar2 default NULL,
705 pk2 IN varchar2 default NULL,
706 pk3 IN varchar2 default NULL,
707 from_url IN varchar2 default NULL,
708 query_only IN varchar2 default 'N'
709 ) is
710 -------------------------------------------------------------
711
712
713 n_pad number;
714 v_lang varchar2(5);
715 l_cart_submitted_flag varchar2(1);
716
717 plsql_bug defaultParamType;
718
719 -- Cart Header variables
720 --
721 v_cart_id number;
722 v_shopper_id number;
723 v_exist number;
724 v_structure number;
725 v_saved_flag number;
726
727 cart_exists exception;
728
729 v_emergency varchar2(10);
730
731
732 l_error_message VARCHAR2(2000);
733 l_err_num NUMBER;
734 l_err_mesg VARCHAR2(240);
735 l_err_loadinterface varchar2(1);
736 p_vendor_name varchar2(1000);
737 p_vendor_phone varchar2(80);
738 p_vendor_site varchar2(240);
739 p_vendor_contact varchar2(240);
740 p_deliver_to_location_id number;
741 p_deliver_to_location varchar2(300);
742 p_deliver_to_org_id number;
743 v_expend_date date;
744 v_errored boolean;
745
746 v_account_num varchar2(2000);
747 v_account_id number;
748 n_org_id number;
749 v_session_id number;
750 v_date_format varchar2(22);
751 n_emp_id number;
752 v_emp_id number;
753 v_incr number;
754 supp_count number;
755 v_error_message varchar2(1000);
756 v_preparer_org_id number;
757 requesterID number;
758 p_requester varchar2(300);
759
760 v_attribute1 number;
761 v_need_date date;
762 v_return_code varchar2(200);
763 v_expense_account number;
764 v_variance_acct_id number;
765 v_budget_acct_id number;
766 v_accrual_acct_id number;
767
768
769 cursor get_loc_org_id(loc varchar2) is
770 SELECT hrl.location_id,
771 nvl(hrl.inventory_organization_id,
772 fsp.inventory_organization_id) organization_id
773 from hr_locations hrl,
774 financials_system_parameters fsp
775 where sysdate < nvl(hrl.inactive_date, sysdate + 1)
776 and hrl.location_code = loc;
777
778 cursor requester_check(v_req_name varchar2,v_org_id number) is
779 SELECT employee_id
780 FROM HR_EMPLOYEES_CURRENT_V
781 WHERE full_name = v_req_name
782 and organization_id = v_org_id;
783
784 cursor get_preparer_org(v_preparer_id number) is
785 SELECT organization_id
786 FROM HR_EMPLOYEES_CURRENT_V
787 where employee_id = v_preparer_id;
788
789 cursor get_line_count(v_cart_id number) is
790 SELECT count(1)
791 FROM icx_shopping_cart_lines
792 WHERE cart_id = v_cart_id;
793
794 i BINARY_INTEGER;
795
796 cursor acctBuild(v_cart_id number,v_cart_line_id number, v_emp_id number,
797 v_oo_id number) is
798 select hecv.default_code_combination_id employee_default_account_id,
799 msi.expense_account
800 from hr_employees_current_v hecv,
801 mtl_system_items msi,
802 icx_shopping_carts isc,
803 icx_shopping_cart_lines iscl
804 where msi.INVENTORY_ITEM_ID (+) = iscl.ITEM_ID
805 and nvl(msi.ORGANIZATION_ID,
806 nvl(isc.DESTINATION_ORGANIZATION_ID,
807 iscl.DESTINATION_ORGANIZATION_ID)) =
808 nvl(isc.DESTINATION_ORGANIZATION_ID,
809 iscl.DESTINATION_ORGANIZATION_ID)
810 and hecv.EMPLOYEE_ID = v_emp_id
811 and nvl(isc.org_id, -9999) = nvl(v_oo_id, -9999)
812 and nvl(iscl.org_id, -9999) = nvl(v_oo_id, -9999);
813
814
815 cursor get_acct is
816 select CHART_OF_ACCOUNTS_ID
817 from gl_sets_of_books,
818 financials_system_parameters fsp
819 where gl_sets_of_books.SET_OF_BOOKS_ID = fsp.set_of_books_id;
820
821 cursor invalid_locations(l_cart_id number, v_oo_id number) is
822 select cart_line_number, DELIVER_TO_LOCATION
823 from icx_shopping_cart_lines
824 where DELIVER_TO_LOCATION_ID is null
825 and cart_id = v_cart_id
826 and nvl(org_id, -9999) = nvl(v_oo_id, -9999);
827
828 cursor employee_check(approver varchar2) is
829 SELECT employee_id
830 FROM HR_EMPLOYEES_CURRENT_V
831 where full_name = approver;
832
833 cursor line_ids(v_cart_id number) is
834 select cart_line_id from icx_shopping_cart_lines
835 where (quantity is null
836 OR quantity = 0);
837
838 v_action varchar2(100);
839
840 cursor check_cart_submitted(l_cart_id number, l_shopper number) is
841 select saved_flag
842 from icx_shopping_Carts
843 where cart_id = l_cart_id
844 and shopper_id = l_shopper;
845
846 cursor get_head_date(l_cart_id number) is
847 select need_by_date from icx_shopping_Carts
848 where cart_id = l_cart_id;
849
850 v_dcdName varchar2(100);
851
852 /** DAMN PL/SQL BUGS **/
853
854 c_cart_line_id number;
855 a_cart_line_id defaultParamType;
856 c_category_id number;
857 a_category_id defaultParamType;
858 c_category_name number;
859 a_category_name defaultParamType;
860 c_item_id number;
861 a_item_id defaultParamType;
862 c_item_revision number;
863 a_item_revision defaultParamType;
864 c_line_need_date number;
865 a_line_need_date defaultParamType;
866 c_item_description number;
867 a_item_description defaultParamType;
868 c_expend_item_date number;
869 a_expend_item_date defaultParamType;
870 c_expend_org number;
871 a_expend_org defaultParamType;
872 c_qty_va number;
873 a_qty_va defaultParamType;
874 c_suggested_buyer_id number;
875 a_suggested_buyer_id defaultParamType;
876 c_project_id number;
877 a_project_id defaultParamType;
878 c_suggested_vendor_contacta number;
879 a_suggested_vendor_contacta defaultParamType;
880 c_suggested_vendor_item_numa number;
881 a_suggested_vendor_item_numa defaultParamType;
882 c_suggested_vendor_namea number;
883 a_suggested_vendor_namea defaultParamType;
884 c_suggested_vendor_phonea number;
885 a_suggested_vendor_phonea defaultParamType;
886 c_suggested_vendor_sitea number;
887 a_suggested_vendor_sitea defaultParamType;
888 c_task_id number;
889 a_task_id defaultParamType;
890 c_unit_of_measurement number;
891 a_unit_of_measurement defaultParamType;
892 c_unit_price number;
893 a_unit_price defaultParamType;
894 c_deliver_to_location_id_l number;
895 a_deliver_to_location_id_l defaultParamType;
896 c_dest_org_id_l number;
897 a_dest_org_id_l defaultParamType;
898 c_deliver_to_location_l number;
899 a_deliver_to_location_l defaultParamType;
900 c_line_attribute_1a number;
901 a_line_attribute_1a defaultParamType;
902 c_line_attribute_2a number;
903 a_line_attribute_2a defaultParamType;
904 c_line_attribute_3a number;
905 a_line_attribute_3a defaultParamType;
906 c_line_attribute_4a number;
907 a_line_attribute_4a defaultParamType;
908 c_line_attribute_5a number;
909 a_line_attribute_5a defaultParamType;
910 c_line_attribute_6a number;
911 a_line_attribute_6a defaultParamType;
912 c_line_attribute_7a number;
913 a_line_attribute_7a defaultParamType;
914 c_line_attribute_8a number;
915 a_line_attribute_8a defaultParamType;
916 c_line_attribute_9a number;
917 a_line_attribute_9a defaultParamType;
918 c_line_attribute_10a number;
919 a_line_attribute_10a defaultParamType;
920 c_line_attribute_11a number;
921 a_line_attribute_11a defaultParamType;
922 c_line_attribute_12a number;
923 a_line_attribute_12a defaultParamType;
924 c_line_attribute_13a number;
925 a_line_attribute_13a defaultParamType;
926 c_line_attribute_14a number;
927 a_line_attribute_14a defaultParamType;
928 c_line_attribute_15a number;
929 a_line_attribute_15a defaultParamType;
930 c_charge_acct_seg1a number;
931 a_charge_acct_seg1a defaultParamType;
932 c_charge_acct_seg2a number;
933 a_charge_acct_seg2a defaultParamType;
934 c_charge_acct_seg3a number;
935 a_charge_acct_seg3a defaultParamType;
936 c_charge_acct_seg4a number;
937 a_charge_acct_seg4a defaultParamType;
938 c_charge_acct_seg5a number;
939 a_charge_acct_seg5a defaultParamType;
940 c_charge_acct_seg6a number;
941 a_charge_acct_seg6a defaultParamType;
942 c_charge_acct_seg7a number;
943 a_charge_acct_seg7a defaultParamType;
944 c_charge_acct_seg8a number;
945 a_charge_acct_seg8a defaultParamType;
946 c_charge_acct_seg9a number;
947 a_charge_acct_seg9a defaultParamType;
948 c_charge_acct_seg10a number;
949 a_charge_acct_seg10a defaultParamType;
950 c_charge_acct_seg11a number;
951 a_charge_acct_seg11a defaultParamType;
952 c_charge_acct_seg12a number;
953 a_charge_acct_seg12a defaultParamType;
954 c_charge_acct_seg13a number;
955 a_charge_acct_seg13a defaultParamType;
956 c_charge_acct_seg14a number;
957 a_charge_acct_seg14a defaultParamType;
958 c_charge_acct_seg15a number;
959 a_charge_acct_seg15a defaultParamType;
960 c_charge_acct_seg16a number;
961 a_charge_acct_seg16a defaultParamType;
962 c_charge_acct_seg17a number;
963 a_charge_acct_seg17a defaultParamType;
964 c_charge_acct_seg18a number;
965 a_charge_acct_seg18a defaultParamType;
966 a_charge_acct_seg19a defaultParamType;
967 c_charge_acct_seg19a number;
968 a_charge_acct_seg20a defaultParamType;
969 c_charge_acct_seg20a number;
970 a_charge_acct_seg21a defaultParamType;
971 c_charge_acct_seg21a number;
972 a_charge_acct_seg22a defaultParamType;
973 c_charge_acct_seg22a number;
974 a_charge_acct_seg23a defaultParamType;
975 c_charge_acct_seg23a number;
976 a_charge_acct_seg24a defaultParamType;
977 c_charge_acct_seg24a number;
978 a_charge_acct_seg25a defaultParamType;
979 c_charge_acct_seg25a number;
980 a_charge_acct_seg26a defaultParamType;
981 c_charge_acct_seg26a number;
982 a_charge_acct_seg27a defaultParamType;
983 c_charge_acct_seg27a number;
984 a_charge_acct_seg28a defaultParamType;
985 c_charge_acct_seg28a number;
986 a_charge_acct_seg29a defaultParamType;
987 c_charge_acct_seg29a number;
988 c_charge_acct_seg30a number;
989 a_charge_acct_seg30a defaultParamType;
990
991
992 cursor cart_lines(v_cart_id number) is
993 select cart_line_id from
994 icx_shopping_cart_lines
995 where cart_id = v_cart_id;
996
997 d_shopper_name varchar2(100);
998 d_location_id number;
999 d_location_code varchar2(100);
1000 d_org_id number;
1001 d_org_code varchar2(100);
1002
1003 n_number number;
1004
1005 l_po_number varchar2(1000);
1006
1007 cursor get_reserved_po_number(cartId number,shopperId number) is
1008 select reserved_po_num
1009 from icx_shopping_carts
1010 where cart_id = cartId
1011 and shopper_id = shopperId;
1012
1013
1014 CURSOR C3 IS SELECT to_char(current_max_unique_identifier + 1)
1015 FROM po_unique_identifier_control
1016 WHERE table_name = 'PO_HEADERS'
1017 FOR UPDATE OF current_max_unique_identifier;
1018
1019 begin
1020
1021
1022 if (icx_sec.validatesession()) then
1023
1024
1025
1026 -- initialize the error page
1027 icx_util.error_page_setup;
1028
1029 --get language code
1030 v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
1031
1032
1033 -- get shopper id and lang code
1034 v_shopper_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
1035
1036 v_cart_id := icx_call.decrypt2(icx_cart_id);
1037
1038
1039 -- clear out all possible cart errors
1040 delete icx_req_cart_errors
1041 where cart_id = v_cart_id;
1042
1043 n_org_id := icx_sec.getId(icx_sec.PV_ORG_ID);
1044 v_date_format := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
1045 n_emp_id := icx_sec.getID(icx_sec.PV_INT_CONTACT_ID);
1046 v_dcdName := owa_util.get_cgi_env('SCRIPT_NAME');
1047
1048 -- get shopper info for defaults
1049 ICX_REQ_NAVIGATION.shopper_info(n_emp_id,
1050 d_shopper_name, d_location_id,
1051 d_location_code, d_org_id, d_org_code);
1052
1053
1054 -- Check if cart is legal
1055 open check_cart_submitted(v_cart_id, v_shopper_id);
1056 fetch check_cart_submitted into v_saved_flag;
1057 if check_cart_submitted%NOTFOUND then
1058 raise cart_exists;
1059 end if;
1060 if v_saved_flag = 2 then
1061 raise cart_exists;
1062 end if;
1063
1064
1065 if (user_action <> 'CANCEL') then
1066
1067
1068 /***********************************************************************/
1069 /***** DO THE HEADER RECORD ********/
1070 /***********************************************************************/
1071 -- check if date is valid
1072 if (icx_need_by_date <> FND_API.G_MISS_CHAR) then
1073 begin
1074 v_need_date := to_date(icx_need_by_date, v_date_format);
1075 if v_need_date <= (sysdate - 1) then
1076 v_incr := icx_sec.getID(icx_sec.PV_USER_REQ_DAYS_NEEDED_BY);
1077 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_NEED_BY_DATE');
1078 v_error_message := FND_MESSAGE.GET;
1079 icx_util.add_error(v_error_message);
1080 storeerror(v_cart_id, v_error_message);
1081 v_need_date := sysdate + v_incr;
1082 end if;
1083 exception
1084 when others then
1085 --add error
1086 v_incr := icx_sec.getID(icx_sec.PV_USER_REQ_DAYS_NEEDED_BY);
1087 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_DATE');
1088 v_error_message := FND_MESSAGE.GET;
1089 icx_util.add_error(v_error_message);
1090 storeerror(v_cart_id, v_error_message);
1091 v_need_date := sysdate + v_incr;
1092 end;
1093 else
1094 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_DATE');
1095 v_error_message := FND_MESSAGE.GET;
1096 icx_util.add_error(v_error_message);
1097 storeerror(v_cart_id, v_error_message);
1098 v_incr := icx_sec.getID(icx_sec.PV_USER_REQ_DAYS_NEEDED_BY);
1099 v_need_date := sysdate + v_incr;
1100 end if;
1101
1102
1103 -- check if emergency reqs, reserve po number if not already has one
1104 -- do not waste PO number if not needed, so check first if already
1105 -- has one in table, then do the reserve po number
1106 l_po_number := NULL;
1107
1108 if emergency = 'YES' then
1109 open get_reserved_po_number(v_cart_id,v_shopper_id);
1110 fetch get_reserved_po_number into l_po_number;
1111 close get_reserved_po_number;
1112
1113 -- only get a reserve po number if user did not click on
1114 -- Apply Changes button
1115 if l_po_number is NULL and
1116 user_action <> 'MODIFY' and user_action <> 'ATTACHMENT' then
1117 open C3;
1118 fetch C3 into l_po_number;
1119 UPDATE po_unique_identifier_control
1120 SET current_max_unique_identifier =
1121 current_max_unique_identifier + 1
1122 WHERE CURRENT of C3;
1123 CLOSE C3;
1124 commit;
1125 end if;
1126 end if;
1127
1128 update ICX_SHOPPING_CARTS
1129 set APPROVER_ID = decode(icx_approver_id, FND_API.G_MISS_CHAR, APPROVER_ID,
1130 icx_approver_id),
1131 APPROVER_NAME = decode(icx_approver_name,
1132 FND_API.G_MISS_CHAR, APPROVER_NAME,
1133 icx_approver_name),
1134 DELIVER_TO_LOCATION_ID =
1135 decode(icx_deliver_to_location_id,
1136 FND_API.G_MISS_CHAR, DELIVER_TO_LOCATION_ID,
1137 icx_deliver_to_location_id),
1138 DELIVER_TO_LOCATION = decode(icx_deliver_to_location,
1139 FND_API.G_MISS_CHAR, DELIVER_TO_LOCATION,
1140 icx_deliver_to_location),
1141 DELIVER_TO_REQUESTOR_ID =
1142 decode(icx_deliver_to_requestor_id,
1143 FND_API.G_MISS_CHAR, DELIVER_TO_REQUESTOR_ID,
1144 icx_deliver_to_requestor_id),
1145 DELIVER_TO_REQUESTOR =
1146 decode(icx_deliver_to_requestor,
1147 FND_API.G_MISS_CHAR, DELIVER_TO_REQUESTOR,
1148 icx_deliver_to_requestor),
1149 DESTINATION_ORGANIZATION_ID =
1150 decode(icx_dest_org_id, FND_API.G_MISS_CHAR, DESTINATION_ORGANIZATION_ID,
1151 icx_dest_org_id),
1152 NEED_BY_DATE = decode(icx_need_by_date, FND_API.G_MISS_CHAR, NEED_BY_DATE,
1153 v_need_date),
1154 NOTE_TO_APPROVER = decode(icx_note_to_approver,
1155 FND_API.G_MISS_CHAR, NOTE_TO_APPROVER, icx_note_to_approver),
1156 NOTE_TO_BUYER = decode(icx_note_to_buyer, FND_API.G_MISS_CHAR, NOTE_TO_BUYER,
1157 icx_note_to_buyer),
1158 HEADER_DESCRIPTION = decode(icx_header_description,
1159 FND_API.G_MISS_CHAR, HEADER_DESCRIPTION,
1160 icx_header_description),
1161 HEADER_ATTRIBUTE1 = decode(icx_header_attribute1,
1162 FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE1,
1163 icx_header_attribute1),
1164 HEADER_ATTRIBUTE2 = decode(icx_header_attribute2,
1165 FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE2,
1166 icx_header_attribute2),
1167 HEADER_ATTRIBUTE3 = decode(icx_header_attribute3,
1168 FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE3,
1169 icx_header_attribute3),
1170 HEADER_ATTRIBUTE4 = decode(icx_header_attribute4,
1171 FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE4,
1172 icx_header_attribute4),
1173 HEADER_ATTRIBUTE5 = decode(icx_header_attribute5,
1174 FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE5,
1175 icx_header_attribute5),
1176 HEADER_ATTRIBUTE6 = decode(icx_header_attribute6,
1177 FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE6,
1178 icx_header_attribute6),
1179 HEADER_ATTRIBUTE7 = decode(icx_header_attribute7,
1180 FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE7,
1181 icx_header_attribute7),
1182 HEADER_ATTRIBUTE8 = decode(icx_header_attribute8,
1183 FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE8,
1184 icx_header_attribute8),
1185 HEADER_ATTRIBUTE9 = decode(icx_header_attribute9,
1186 FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE9,
1187 icx_header_attribute9),
1188 HEADER_ATTRIBUTE10 = decode(icx_header_attribute10,
1189 FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE10,
1190 icx_header_attribute10),
1191 HEADER_ATTRIBUTE11 = decode(icx_header_attribute11,
1192 FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE11,
1193 icx_header_attribute11),
1194 HEADER_ATTRIBUTE12 = decode(icx_header_attribute12,
1195 FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE12,
1196 icx_header_attribute12),
1197 HEADER_ATTRIBUTE13 = decode(icx_header_attribute13,
1198 FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE13,
1199 icx_header_attribute13),
1200 HEADER_ATTRIBUTE14 = decode(icx_header_attribute14,
1201 FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE14,
1202 icx_header_attribute14),
1203 HEADER_ATTRIBUTE15 = decode(icx_header_attribute15,
1204 FND_API.G_MISS_CHAR, HEADER_ATTRIBUTE15,
1205 icx_header_attribute15),
1206 RESERVED_PO_NUM = l_po_number,
1207 LAST_UPDATE_DATE = sysdate
1208 where CART_ID = v_cart_id
1209 and SHOPPER_ID = v_shopper_id;
1210
1211
1212 update icx_cart_distributions
1213 SET LAST_UPDATE_DATE = sysdate,
1214 CHARGE_ACCOUNT_SEGMENT1 = decode(icx_charge_acct_seg1,
1215 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT1,
1216 icx_charge_acct_seg1),
1217 CHARGE_ACCOUNT_SEGMENT2 = decode(icx_charge_acct_seg2,
1218 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT2,
1219 icx_charge_acct_seg2),
1220 CHARGE_ACCOUNT_SEGMENT3 = decode(icx_charge_acct_seg3,
1221 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT3,
1222 icx_charge_acct_seg3),
1223 CHARGE_ACCOUNT_SEGMENT4 = decode(icx_charge_acct_seg4,
1224 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT4,
1225 icx_charge_acct_seg4),
1226 CHARGE_ACCOUNT_SEGMENT5 = decode(icx_charge_acct_seg5,
1227 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT5,
1228 icx_charge_acct_seg5),
1229 CHARGE_ACCOUNT_SEGMENT6 = decode(icx_charge_acct_seg6,
1230 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT6,
1231 icx_charge_acct_seg6),
1232 CHARGE_ACCOUNT_SEGMENT7 = decode(icx_charge_acct_seg7,
1233 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT7,
1234 icx_charge_acct_seg7),
1235 CHARGE_ACCOUNT_SEGMENT8 = decode(icx_charge_acct_seg8,
1236 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT8,
1237 icx_charge_acct_seg8),
1238 CHARGE_ACCOUNT_SEGMENT9 = decode(icx_charge_acct_seg9,
1239 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT9,
1240 icx_charge_acct_seg9),
1241 CHARGE_ACCOUNT_SEGMENT10 = decode(icx_charge_acct_seg10,
1242 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT10,
1243 icx_charge_acct_seg10),
1244 CHARGE_ACCOUNT_SEGMENT11 = decode(icx_charge_acct_seg11,
1245 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT11,
1246 icx_charge_acct_seg11),
1247 CHARGE_ACCOUNT_SEGMENT12 = decode(icx_charge_acct_seg12,
1248 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT12,
1249 icx_charge_acct_seg12),
1250 CHARGE_ACCOUNT_SEGMENT13 = decode(icx_charge_acct_seg13,
1251 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT13,
1252 icx_charge_acct_seg13),
1253 CHARGE_ACCOUNT_SEGMENT14 = decode(icx_charge_acct_seg14,
1254 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT14,
1255 icx_charge_acct_seg14),
1256 CHARGE_ACCOUNT_SEGMENT15 = decode(icx_charge_acct_seg15,
1257 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT15,
1258 icx_charge_acct_seg15),
1259 CHARGE_ACCOUNT_SEGMENT16 = decode(icx_charge_acct_seg16,
1260 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT16,
1261 icx_charge_acct_seg16),
1262 CHARGE_ACCOUNT_SEGMENT17 = decode(icx_charge_acct_seg17,
1263 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT17,
1264 icx_charge_acct_seg17),
1265 CHARGE_ACCOUNT_SEGMENT18 = decode(icx_charge_acct_seg18,
1266 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT18,
1267 icx_charge_acct_seg18),
1268 CHARGE_ACCOUNT_SEGMENT19 = decode(icx_charge_acct_seg19,
1269 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT19,
1270 icx_charge_acct_seg19),
1271 CHARGE_ACCOUNT_SEGMENT20 = decode(icx_charge_acct_seg20,
1272 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT20,
1273 icx_charge_acct_seg20),
1274 CHARGE_ACCOUNT_SEGMENT21 = decode(icx_charge_acct_seg21,
1275 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT21,
1276 icx_charge_acct_seg21),
1277 CHARGE_ACCOUNT_SEGMENT22 = decode(icx_charge_acct_seg22,
1278 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT22,
1279 icx_charge_acct_seg22),
1280 CHARGE_ACCOUNT_SEGMENT23 = decode(icx_charge_acct_seg23,
1281 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT23,
1282 icx_charge_acct_seg23),
1283 CHARGE_ACCOUNT_SEGMENT24 = decode(icx_charge_acct_seg24,
1284 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT24,
1285 icx_charge_acct_seg24),
1286 CHARGE_ACCOUNT_SEGMENT25 = decode(icx_charge_acct_seg25,
1287 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT25,
1288 icx_charge_acct_seg25),
1289 CHARGE_ACCOUNT_SEGMENT26 = decode(icx_charge_acct_seg26,
1290 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT26,
1291 icx_charge_acct_seg26),
1292 CHARGE_ACCOUNT_SEGMENT27 = decode(icx_charge_acct_seg27,
1293 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT27,
1294 icx_charge_acct_seg27),
1295 CHARGE_ACCOUNT_SEGMENT28 = decode(icx_charge_acct_seg28,
1296 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT28,
1297 icx_charge_acct_seg28),
1298 CHARGE_ACCOUNT_SEGMENT29 = decode(icx_charge_acct_seg29,
1299 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT29,
1300 icx_charge_acct_seg29),
1301 CHARGE_ACCOUNT_SEGMENT30 = decode(icx_charge_acct_seg30,
1302 FND_API.G_MISS_CHAR, CHARGE_ACCOUNT_SEGMENT30,
1303 icx_charge_acct_seg30)
1304 where
1305 cart_id = v_cart_id;
1306
1307
1308
1309 /*********************************************************************/
1310 /********* Validate Head **********/
1311 /*********************************************************************/
1312
1313 -- user custum validation
1314 icx_req_custom.reqs_validate_head(emergency, v_cart_id);
1315
1316
1317
1318 -- Location Location Location
1319 if (icx_deliver_to_location <> FND_API.G_MISS_CHAR) then
1320 --deliver to location on at the Header level, make sure its correct
1321 if (icx_deliver_to_location_id is null) then
1322 -- the user typed the value into the field, we have to validate
1323 open get_loc_org_id(icx_deliver_to_location);
1324 fetch get_loc_org_id into p_deliver_to_location_id,
1325 p_deliver_to_org_id;
1326 if get_loc_org_id%NOTFOUND then
1327 -- woops they didn't type it in correctly
1328 v_error_message :=
1329 icx_util.getPrompt(601, 'ICX_SHOPPING_CART_HEADER_R',
1330 178, 'ICX_DELIVER_TO_LOCATION');
1331 FND_MESSAGE.SET_NAME('ICX','ICX_INVALID_ENTRY');
1332 FND_MESSAGE.SET_TOKEN('INVALID_TOKEN',v_error_message ||
1333 ' : ' || icx_deliver_to_location);
1334 v_error_message := FND_MESSAGE.GET;
1335 icx_util.add_error(v_error_message);
1336 storeerror(v_cart_id, v_error_message);
1337 p_deliver_to_location_id := d_location_id;
1338 p_deliver_to_org_id := d_org_id;
1339 p_deliver_to_location := d_location_code;
1340 else
1341 p_deliver_to_location := icx_deliver_to_location;
1342 end if;
1343 close get_loc_org_id;
1344 -- update icx_shopping_Carts
1345 -- set DELIVER_TO_LOCATION_ID = null,
1346 -- DESTINATION_ORGANIZATION_ID = d_org_id
1347 -- where CART_ID = v_cart_id
1348 -- and SHOPPER_ID = v_shopper_id;
1349
1350 --dc should user p_deliver_to_location_id etc instead
1351 update icx_shopping_carts
1352 set DELIVER_TO_LOCATION_ID = p_deliver_to_location_id,
1353 DELIVER_TO_LOCATIOn = p_deliver_to_location,
1354 DESTINATION_ORGANIZATION_ID = p_deliver_to_org_id
1355 where CART_ID = v_cart_id
1356 and SHOPPER_ID = v_shopper_id;
1357
1358 end if;
1359 elsif (icx_deliver_to_location is null) then
1360 v_error_message :=
1361 icx_util.getPrompt(601, 'ICX_SHOPPING_CART_HEADER_R',
1362 178, 'ICX_DELIVER_TO_LOCATION');
1363 FND_MESSAGE.SET_NAME('ICX','ICX_INVALID_ENTRY');
1364 FND_MESSAGE.SET_TOKEN('INVALID_TOKEN',v_error_message ||
1365 ' : ' || icx_deliver_to_location);
1366 v_error_message := FND_MESSAGE.GET;
1367 icx_util.add_error(v_error_message);
1368 storeerror(v_cart_id, v_error_message);
1369 update icx_shopping_Carts
1370 set DELIVER_TO_LOCATION_ID = null,
1371 DESTINATION_ORGANIZATION_ID = d_org_id,
1372 DELIVER_TO_LOCATION = null
1373 where CART_ID = v_cart_id
1374 and SHOPPER_ID = v_shopper_id;
1375 end if;
1376
1377 --validate requster
1378 if (icx_deliver_to_requestor <> FND_API.G_MISS_CHAR) then
1379 -- requster must be on in the region
1380 if (icx_deliver_to_requestor_id is null) then
1381 -- user type in value for requester
1382 if (icx_sec.getID(icx_sec.PV_USER_REQ_OVERRIDE_REQUESTOR) = 'ORG')
1383 then
1384 open get_preparer_org(n_emp_id);
1385 fetch get_preparer_org into v_preparer_org_id;
1386 close get_preparer_org;
1387
1388
1389 open requester_check(icx_deliver_to_requestor, v_preparer_org_id);
1390 fetch requester_check into requesterID;
1391 if requester_check%NOTFOUND then
1392 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_REQUESTER');
1393 v_error_message := FND_MESSAGE.GET;
1394 icx_util.add_error(v_error_message);
1395 storeerror(v_cart_id, v_error_message);
1396 requesterID := null;
1397 p_requester := null;
1398 else
1399 p_requester := icx_deliver_to_requestor;
1400 end if;
1401 else
1402 -- must overide att the ALL case
1403 open employee_check(icx_deliver_to_requestor);
1404 fetch employee_check into requesterID;
1405 if employee_check%FOUND then
1406 p_requester := icx_deliver_to_requestor;
1407 else
1408 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_REQUESTER');
1409 v_error_message := FND_MESSAGE.GET;
1410 icx_util.add_error(v_error_message);
1411 storeerror(v_cart_id, v_error_message);
1412 requesterID := -1;
1413 p_requester := null;
1414 end if;
1415 end if;
1416 update icx_shopping_carts
1417 set DELIVER_TO_REQUESTOR_ID = requesterID,
1418 DELIVER_TO_REQUESTOR = p_requester
1419 where CART_ID = v_cart_id
1420 and SHOPPER_ID = v_shopper_id;
1421 end if;
1422 elsif (icx_deliver_to_requestor is null) then
1423 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_REQUESTER');
1424 v_error_message := FND_MESSAGE.GET;
1425 icx_util.add_error(v_error_message);
1426 storeerror(v_cart_id, v_error_message);
1427 update icx_shopping_Carts
1428 set DELIVER_TO_REQUESTOR_ID = null,
1429 DELIVER_TO_REQUESTOR = null
1430 where CART_ID = v_cart_id
1431 and SHOPPER_ID = v_shopper_id;
1432 end if;
1433
1434 --validate approver
1435 if (icx_approver_name <> FND_API.G_MISS_CHAR) then
1436 -- approver must be in the region
1437 if (icx_approver_id is null) then
1438 -- user typed in an approver
1439 open employee_check(icx_approver_name);
1440 fetch employee_check into requesterID;
1441 if (employee_check%NOTFOUND) then
1442 --changed by alex
1443 /* v_error_message :=
1444 icx_util.getPrompt(601, 'ICX_SHOPPING_CART_HEADER_R',
1445 178, 'ICX_APPROVER_NAME');
1446 FND_MESSAGE.SET_NAME('ICX','ICX_INVALID_ENTRY');
1447 FND_MESSAGE.SET_TOKEN('INVALID_TOKEN',v_error_message);
1448 v_error_message := FND_MESSAGE.GET;
1449 icx_util.add_error(v_error_message);
1450 storeerror(v_cart_id,v_error_message);
1451 */
1452 p_requester := null;
1453 requesterID:= null;
1454 else
1455 p_requester := icx_approver_name;
1456 end if;
1457 update icx_shopping_carts
1458 set APPROVER_ID = requesterID,
1459 APPROVER_NAME = p_requester
1460 where cart_id = v_cart_id
1461 and shopper_id = v_shopper_id;
1462 end if;
1463 elsif (icx_approver_name is null) then
1464 --changed by alex
1465 /* v_error_message :=
1466 icx_util.getPrompt(601, 'ICX_SHOPPING_CART_HEADER_R',
1467 178, 'ICX_APPROVER_NAME');
1468 FND_MESSAGE.SET_NAME('ICX','ICX_INVALID_ENTRY');
1469 FND_MESSAGE.SET_TOKEN('INVALID_TOKEN',v_error_message);
1470 v_error_message := FND_MESSAGE.GET;
1471 icx_util.add_error(v_error_message);
1472 storeerror(v_cart_id,v_error_message);
1473 */
1474 update icx_shopping_carts
1475 set APPROVER_ID = null,
1476 APPROVER_NAME = null
1477 where cart_id = v_cart_id
1478 and shopper_id = v_shopper_id;
1479 end if;
1480
1481
1482 /***********************************************************************/
1483 /***** Now the LINES ********/
1484 /***********************************************************************/
1485
1486
1487 -- get the counts of each table
1488 if icx_cart_line_ida.COUNT > 0 then
1489
1490 c_cart_line_id := icx_cart_line_ida.COUNT;
1491
1492 -- We have the count, so we make a empty table of that many
1493 for i in 1..c_cart_line_id loop
1494 plsql_bug(i) := null;
1495 end loop;
1496 c_category_id := icx_category_ida.COUNT;
1497 if c_category_id = 0 then a_category_id := plsql_bug;
1498 else a_category_id := icx_category_ida;
1499 end if;
1500 c_category_name := icx_category_namea.COUNT;
1501 if c_category_name = 0 then a_category_name := plsql_bug;
1502 else a_category_name := icx_category_namea;
1503 end if;
1504 c_item_id := icx_item_ida.COUNT;
1505 if c_item_id = 0 then a_item_id := plsql_bug;
1506 else a_item_id := icx_item_ida;
1507 end if;
1508 c_item_revision := icx_item_reva.COUNT;
1509 if c_item_revision = 0 then a_item_revision := plsql_bug;
1510 else a_item_revision := icx_item_reva;
1511 end if;
1512 c_line_need_date := icx_need_by_datea.COUNT;
1513 if c_line_need_date = 0 then a_line_need_date := plsql_bug;
1514 else a_line_need_date := icx_need_by_datea;
1515 end if;
1516 c_item_description := icx_item_descriptiona.COUNT;
1517 if c_item_description = 0 then a_item_description := plsql_bug;
1518 else a_item_description := icx_item_descriptiona;
1519 end if;
1520 c_expend_item_date := icx_expend_item_datea.COUNT;
1521 if c_expend_item_date = 0 then a_expend_item_date := plsql_bug;
1522 else a_expend_item_date := icx_expend_item_datea;
1523 end if;
1524 c_expend_org := icx_expend_orga.COUNT;
1525 if c_expend_org = 0 then a_expend_org := plsql_bug;
1526 else a_expend_org := icx_expend_orga;
1527 end if;
1528 c_qty_va := icx_qty_va.COUNT;
1529 if c_qty_va = 0 then a_qty_va := plsql_bug;
1530 else a_qty_va := icx_qty_va;
1531 end if;
1532 c_suggested_buyer_id := icx_suggested_buyer_ida.COUNT;
1533 if c_suggested_buyer_id = 0 then a_suggested_buyer_id := plsql_bug;
1534 else a_suggested_buyer_id := icx_suggested_buyer_ida;
1535 end if;
1536 c_project_id := icx_project_ida.COUNT;
1537 if c_project_id = 0 then a_project_id := plsql_bug;
1538 else a_project_id := icx_project_ida;
1539 end if;
1540 c_suggested_vendor_contacta := icx_suggested_vendor_contacta.COUNT;
1541 if c_suggested_vendor_contacta = 0 then a_suggested_vendor_contacta := plsql_bug;
1542 else a_suggested_vendor_contacta := icx_suggested_vendor_contacta;
1543 end if;
1544 c_suggested_vendor_item_numa := icx_suggested_vendor_item_numa.COUNT;
1545 if c_suggested_vendor_item_numa = 0 then a_suggested_vendor_item_numa := plsql_bug;
1546 else a_suggested_vendor_item_numa := icx_suggested_vendor_item_numa;
1547 end if;
1548 c_suggested_vendor_namea := icx_suggested_vendor_namea.COUNT;
1549 if c_suggested_vendor_namea = 0 then a_suggested_vendor_namea := plsql_bug;
1550 else a_suggested_vendor_namea := icx_suggested_vendor_namea;
1551 end if;
1552 c_suggested_vendor_phonea := icx_suggested_vendor_phonea.COUNT;
1553 if c_suggested_vendor_phonea = 0 then a_suggested_vendor_phonea := plsql_bug;
1554 else a_suggested_vendor_phonea := icx_suggested_vendor_phonea;
1555 end if;
1556 c_suggested_vendor_sitea := icx_suggested_vendor_sitea.COUNT;
1557 if c_suggested_vendor_sitea = 0 then a_suggested_vendor_sitea := plsql_bug;
1558 else a_suggested_vendor_sitea := icx_suggested_vendor_sitea;
1559 end if;
1560 c_task_id := icx_task_ida.COUNT;
1561 if c_task_id = 0 then a_task_id := plsql_bug;
1562 else a_task_id := icx_task_ida;
1563 end if;
1564 c_unit_of_measurement := icx_unit_of_measurementa.COUNT;
1565 if c_unit_of_measurement = 0 then a_unit_of_measurement := plsql_bug;
1566 else a_unit_of_measurement := icx_unit_of_measurementa;
1567 end if;
1568 c_unit_price := icx_unit_pricea.COUNT;
1569 if c_unit_price = 0 then a_unit_price := plsql_bug;
1570 else a_unit_price := icx_unit_pricea;
1571 end if;
1572 c_deliver_to_location_id_l := icx_deliver_to_location_id_la.COUNT;
1573 if c_deliver_to_location_id_l = 0 then a_deliver_to_location_id_l := plsql_bug;
1574 else a_deliver_to_location_id_l := icx_deliver_to_location_id_la;
1575 end if;
1576 c_dest_org_id_l := icx_dest_org_id_la.COUNT;
1577 if c_dest_org_id_l = 0 then a_dest_org_id_l := plsql_bug;
1578 else a_dest_org_id_l := icx_dest_org_id_la;
1579 end if;
1580 c_deliver_to_location_l := icx_deliver_to_location_la.COUNT;
1581 if c_deliver_to_location_l = 0 then a_deliver_to_location_l := plsql_bug;
1582 else a_deliver_to_location_l := icx_deliver_to_location_la;
1583 end if;
1584 c_line_attribute_1a := icx_line_attribute_1a.COUNT;
1585 if c_line_attribute_1a = 0 then a_line_attribute_1a := plsql_bug;
1586 else a_line_attribute_1a := icx_line_attribute_1a;
1587 end if;
1588 c_line_attribute_2a := icx_line_attribute_2a.COUNT;
1589 if c_line_attribute_2a = 0 then a_line_attribute_2a := plsql_bug;
1590 else a_line_attribute_2a := icx_line_attribute_2a;
1591 end if;
1592 c_line_attribute_3a := icx_line_attribute_3a.COUNT;
1593 if c_line_attribute_3a = 0 then a_line_attribute_3a := plsql_bug;
1594 else a_line_attribute_3a := icx_line_attribute_3a;
1595 end if;
1596 c_line_attribute_4a := icx_line_attribute_4a.COUNT;
1597 if c_line_attribute_4a = 0 then a_line_attribute_4a := plsql_bug;
1598 else a_line_attribute_4a := icx_line_attribute_4a;
1599 end if;
1600 c_line_attribute_5a := icx_line_attribute_5a.COUNT;
1601 if c_line_attribute_5a = 0 then a_line_attribute_5a := plsql_bug;
1602 else a_line_attribute_5a := icx_line_attribute_5a;
1603 end if;
1604 c_line_attribute_6a := icx_line_attribute_6a.COUNT;
1605 if c_line_attribute_6a = 0 then a_line_attribute_6a := plsql_bug;
1606 else a_line_attribute_6a := icx_line_attribute_6a;
1607 end if;
1608 c_line_attribute_7a := icx_line_attribute_7a.COUNT;
1609 if c_line_attribute_7a = 0 then a_line_attribute_7a := plsql_bug;
1610 else a_line_attribute_7a := icx_line_attribute_7a;
1611 end if;
1612 c_line_attribute_8a := icx_line_attribute_8a.COUNT;
1613 if c_line_attribute_8a = 0 then a_line_attribute_8a := plsql_bug;
1614 else a_line_attribute_8a := icx_line_attribute_8a;
1615 end if;
1616 c_line_attribute_9a := icx_line_attribute_9a.COUNT;
1617 if c_line_attribute_9a = 0 then a_line_attribute_9a := plsql_bug;
1618 else a_line_attribute_9a := icx_line_attribute_9a;
1619 end if;
1620 c_line_attribute_10a := icx_line_attribute_10a.COUNT;
1621 if c_line_attribute_10a = 0 then a_line_attribute_10a := plsql_bug;
1622 else a_line_attribute_10a := icx_line_attribute_10a;
1623 end if;
1624 c_line_attribute_11a := icx_line_attribute_11a.COUNT;
1625 if c_line_attribute_11a = 0 then a_line_attribute_11a := plsql_bug;
1626 else a_line_attribute_11a := icx_line_attribute_11a;
1627 end if;
1628 c_line_attribute_12a := icx_line_attribute_12a.COUNT;
1629 if c_line_attribute_12a = 0 then a_line_attribute_12a := plsql_bug;
1630 else a_line_attribute_12a := icx_line_attribute_12a;
1631 end if;
1632 c_line_attribute_13a := icx_line_attribute_13a.COUNT;
1633 if c_line_attribute_13a = 0 then a_line_attribute_13a := plsql_bug;
1634 else a_line_attribute_13a := icx_line_attribute_13a;
1635 end if;
1636 c_line_attribute_14a := icx_line_attribute_14a.COUNT;
1637 if c_line_attribute_14a = 0 then a_line_attribute_14a := plsql_bug;
1638 else a_line_attribute_14a := icx_line_attribute_14a;
1639 end if;
1640 c_line_attribute_15a := icx_line_attribute_15a.COUNT;
1641 if c_line_attribute_15a = 0 then a_line_attribute_15a := plsql_bug;
1642 else a_line_attribute_15a := icx_line_attribute_15a;
1643 end if;
1644 c_charge_acct_seg1a := icx_charge_acct_seg1a.COUNT;
1645 if c_charge_acct_seg1a = 0 then a_charge_acct_seg1a := plsql_bug;
1646 else a_charge_acct_seg1a := icx_charge_acct_seg1a;
1647 end if;
1648 c_charge_acct_seg2a := icx_charge_acct_seg2a.COUNT;
1649 if c_charge_acct_seg2a = 0 then a_charge_acct_seg2a := plsql_bug;
1650 else a_charge_acct_seg2a := icx_charge_acct_seg2a;
1651 end if;
1652 c_charge_acct_seg3a := icx_charge_acct_seg3a.COUNT;
1653 if c_charge_acct_seg3a = 0 then a_charge_acct_seg3a := plsql_bug;
1654 else a_charge_acct_seg3a := icx_charge_acct_seg3a;
1655 end if;
1656 c_charge_acct_seg4a := icx_charge_acct_seg4a.COUNT;
1657 if c_charge_acct_seg4a = 0 then a_charge_acct_seg4a := plsql_bug;
1658 else a_charge_acct_seg4a := icx_charge_acct_seg4a;
1659 end if;
1660 c_charge_acct_seg5a := icx_charge_acct_seg5a.COUNT;
1661 if c_charge_acct_seg5a = 0 then a_charge_acct_seg5a := plsql_bug;
1662 else a_charge_acct_seg5a := icx_charge_acct_seg5a;
1663 end if;
1664 c_charge_acct_seg6a := icx_charge_acct_seg6a.COUNT;
1665 if c_charge_acct_seg6a = 0 then a_charge_acct_seg6a := plsql_bug;
1666 else a_charge_acct_seg6a := icx_charge_acct_seg6a;
1667 end if;
1668 c_charge_acct_seg7a := icx_charge_acct_seg7a.COUNT;
1669 if c_charge_acct_seg7a = 0 then a_charge_acct_seg7a := plsql_bug;
1670 else a_charge_acct_seg7a := icx_charge_acct_seg7a;
1671 end if;
1672 c_charge_acct_seg8a := icx_charge_acct_seg8a.COUNT;
1673 if c_charge_acct_seg8a = 0 then a_charge_acct_seg8a := plsql_bug;
1674 else a_charge_acct_seg8a := icx_charge_acct_seg8a;
1675 end if;
1676 c_charge_acct_seg9a := icx_charge_acct_seg9a.COUNT;
1677 if c_charge_acct_seg9a = 0 then a_charge_acct_seg9a := plsql_bug;
1678 else a_charge_acct_seg9a := icx_charge_acct_seg9a;
1679 end if;
1680 c_charge_acct_seg10a := icx_charge_acct_seg10a.COUNT;
1681 if c_charge_acct_seg10a = 0 then a_charge_acct_seg10a := plsql_bug;
1682 else a_charge_acct_seg10a := icx_charge_acct_seg10a;
1683 end if;
1684 c_charge_acct_seg11a := icx_charge_acct_seg11a.COUNT;
1685 if c_charge_acct_seg11a = 0 then a_charge_acct_seg11a := plsql_bug;
1686 else a_charge_acct_seg11a := icx_charge_acct_seg11a;
1687 end if;
1688 c_charge_acct_seg12a := icx_charge_acct_seg12a.COUNT;
1689 if c_charge_acct_seg12a = 0 then a_charge_acct_seg12a := plsql_bug;
1690 else a_charge_acct_seg12a := icx_charge_acct_seg12a;
1691 end if;
1692 c_charge_acct_seg13a := icx_charge_acct_seg13a.COUNT;
1693 if c_charge_acct_seg13a = 0 then a_charge_acct_seg13a := plsql_bug;
1694 else a_charge_acct_seg13a := icx_charge_acct_seg13a;
1695 end if;
1696 c_charge_acct_seg14a := icx_charge_acct_seg14a.COUNT;
1697 if c_charge_acct_seg14a = 0 then a_charge_acct_seg14a := plsql_bug;
1698 else a_charge_acct_seg14a := icx_charge_acct_seg14a;
1699 end if;
1700 c_charge_acct_seg15a := icx_charge_acct_seg15a.COUNT;
1701 if c_charge_acct_seg15a = 0 then a_charge_acct_seg15a := plsql_bug;
1702 else a_charge_acct_seg15a := icx_charge_acct_seg15a;
1703 end if;
1704 c_charge_acct_seg16a := icx_charge_acct_seg16a.COUNT;
1705 if c_charge_acct_seg16a = 0 then a_charge_acct_seg16a := plsql_bug;
1706 else a_charge_acct_seg16a := icx_charge_acct_seg16a;
1707 end if;
1708 c_charge_acct_seg17a := icx_charge_acct_seg17a.COUNT;
1709 if c_charge_acct_seg17a = 0 then a_charge_acct_seg17a := plsql_bug;
1710 else a_charge_acct_seg17a := icx_charge_acct_seg17a;
1711 end if;
1712 c_charge_acct_seg18a := icx_charge_acct_seg18a.COUNT;
1713 if c_charge_acct_seg18a = 0 then a_charge_acct_seg18a := plsql_bug;
1714 else a_charge_acct_seg18a := icx_charge_acct_seg18a;
1715 end if;
1716 c_charge_acct_seg19a := icx_charge_acct_seg19a.COUNT;
1717 if c_charge_acct_seg19a = 0 then a_charge_acct_seg19a := plsql_bug;
1718 else a_charge_acct_seg19a := icx_charge_acct_seg19a;
1719 end if;
1720 c_charge_acct_seg20a := icx_charge_acct_seg20a.COUNT;
1721 if c_charge_acct_seg20a = 0 then a_charge_acct_seg20a := plsql_bug;
1722 else a_charge_acct_seg20a := icx_charge_acct_seg20a;
1723 end if;
1724 c_charge_acct_seg21a := icx_charge_acct_seg21a.COUNT;
1725 if c_charge_acct_seg21a = 0 then a_charge_acct_seg21a := plsql_bug;
1726 else a_charge_acct_seg21a := icx_charge_acct_seg21a;
1727 end if;
1728 c_charge_acct_seg22a := icx_charge_acct_seg22a.COUNT;
1729 if c_charge_acct_seg22a = 0 then a_charge_acct_seg22a := plsql_bug;
1730 else a_charge_acct_seg22a := icx_charge_acct_seg22a;
1731 end if;
1732 c_charge_acct_seg23a := icx_charge_acct_seg23a.COUNT;
1733 if c_charge_acct_seg23a = 0 then a_charge_acct_seg23a := plsql_bug;
1734 else a_charge_acct_seg23a := icx_charge_acct_seg23a;
1735 end if;
1736 c_charge_acct_seg24a := icx_charge_acct_seg24a.COUNT;
1737 if c_charge_acct_seg24a = 0 then a_charge_acct_seg24a := plsql_bug;
1738 else a_charge_acct_seg24a := icx_charge_acct_seg24a;
1739 end if;
1740 c_charge_acct_seg25a := icx_charge_acct_seg25a.COUNT;
1741 if c_charge_acct_seg25a = 0 then a_charge_acct_seg25a := plsql_bug;
1742 else a_charge_acct_seg25a := icx_charge_acct_seg25a;
1743 end if;
1744 c_charge_acct_seg26a := icx_charge_acct_seg26a.COUNT;
1745 if c_charge_acct_seg26a = 0 then a_charge_acct_seg26a := plsql_bug;
1746 else a_charge_acct_seg26a := icx_charge_acct_seg26a;
1747 end if;
1748 c_charge_acct_seg27a := icx_charge_acct_seg27a.COUNT;
1749 if c_charge_acct_seg27a = 0 then a_charge_acct_seg27a := plsql_bug;
1750 else a_charge_acct_seg27a := icx_charge_acct_seg27a;
1751 end if;
1752 c_charge_acct_seg28a := icx_charge_acct_seg28a.COUNT;
1753 if c_charge_acct_seg28a = 0 then a_charge_acct_seg28a := plsql_bug;
1754 else a_charge_acct_seg28a := icx_charge_acct_seg28a;
1755 end if;
1756 c_charge_acct_seg29a := icx_charge_acct_seg29a.COUNT;
1757 if c_charge_acct_seg29a = 0 then a_charge_acct_seg29a := plsql_bug;
1758 else a_charge_acct_seg29a := icx_charge_acct_seg29a;
1759 end if;
1760 c_charge_acct_seg30a := icx_charge_acct_seg30a.COUNT;
1761 if c_charge_acct_seg30a = 0 then a_charge_acct_seg30a := plsql_bug;
1762 else a_charge_acct_seg30a := icx_charge_acct_seg30a;
1763 end if;
1764 i := icx_cart_line_ida.FIRST;
1765
1766 WHILE i is not null LOOP
1767
1768 if (icx_need_by_datea.COUNT >0) then
1769 begin
1770 v_need_date := to_date(icx_need_by_datea(i), v_date_format);
1771 if v_need_date <= (sysdate - 1) then
1772 v_incr := icx_sec.getID(icx_sec.PV_USER_REQ_DAYS_NEEDED_BY);
1773 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_NEED_BY_DATE');
1774 v_error_message := FND_MESSAGE.GET;
1775 FND_MESSAGE.SET_NAME('ICX','ICX_LINE_NUMBER');
1776 FND_MESSAGE.SET_TOKEN('LINE_NUM_TOKEN',to_char(i));
1777 v_error_message := FND_MESSAGE.GET || ': ' || v_error_message;
1778 icx_util.add_error(v_error_message);
1779 storeerror(v_cart_id, v_error_message);
1780 v_need_date := sysdate + v_incr;
1781 end if;
1782 exception
1783 when others then
1784 --add error
1785 v_incr := icx_sec.getID(icx_sec.PV_USER_REQ_DAYS_NEEDED_BY);
1786 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_DATE');
1787 v_error_message := FND_MESSAGE.GET;
1788 FND_MESSAGE.SET_NAME('ICX','ICX_LINE_NUMBER');
1789 FND_MESSAGE.SET_TOKEN('LINE_NUM_TOKEN',to_char(i));
1790 v_error_message := FND_MESSAGE.GET || ': ' || v_error_message;
1791 icx_util.add_error(v_error_message);
1792 storeerror(v_cart_id, v_error_message);
1793 v_need_date := sysdate + v_incr;
1794 end;
1795 if v_need_date is null then
1796 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_DATE');
1797 v_error_message := FND_MESSAGE.GET;
1798 FND_MESSAGE.SET_NAME('ICX','ICX_LINE_NUMBER');
1799 FND_MESSAGE.SET_TOKEN('LINE_NUM_TOKEN',to_char(i));
1800 v_error_message := FND_MESSAGE.GET || ': ' || v_error_message;
1801 icx_util.add_error(v_error_message);
1802 storeerror(v_cart_id, v_error_message);
1803 open get_head_date(v_cart_id);
1804 fetch get_head_date into v_need_date;
1805 close get_head_date;
1806 end if;
1807 end if;
1808
1809 n_pad := instr(a_qty_va(i), '.', 1, 2);
1810 if (n_pad > 2) then
1811 n_number := substr(a_qty_va(i), 1, n_pad-1);
1812 elsif (n_pad > 0) then
1813 n_number := 0;
1814 else
1815 n_number := a_qty_va(i);
1816 end if;
1817
1818 if (icx_expend_item_datea.COUNT > 0) then
1819 begin
1820 v_expend_date := to_date(icx_expend_item_datea(i), v_date_format);
1821 exception
1822 when others then
1823 -- add error
1824 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_DATE');
1825 v_error_message := FND_MESSAGE.GET;
1826 icx_util.add_error(v_error_message);
1827 storeerror(v_cart_id, v_error_message);
1828 v_expend_date := sysdate;
1829 end;
1830 end if;
1831
1832
1833
1834 update icx_shopping_cart_lines
1835 set LAST_UPDATE_DATE = sysdate,
1836 LAST_UPDATED_BY = v_shopper_id,
1837 CATEGORY_ID = decode(c_category_id, 0, CATEGORY_ID,
1838 a_category_id(i)),
1839 ITEM_REVISION = decode(c_item_revision, 0, ITEM_REVISION,
1840 a_item_revision(i)),
1841 NEED_BY_DATE = decode(c_line_need_date, 0, NEED_BY_DATE,
1842 v_need_date),
1843 ITEM_DESCRIPTION = decode(c_item_description,
1844 0 , ITEM_DESCRIPTION,
1845 a_item_description(i)),
1846 EXPENDITURE_ITEM_DATE = decode(c_expend_item_date,
1847 0, EXPENDITURE_ITEM_DATE, v_expend_date),
1848 EXPENDITURE_ORGANIZATION_ID = decode(c_expend_org, 0,
1849 EXPENDITURE_ORGANIZATION_ID, a_expend_org(i)),
1850 QUANTITY = decode(c_qty_va, 0, QUANTITY, n_number),
1851 SUGGESTED_BUYER_ID = decode(c_suggested_buyer_id,
1852 0, SUGGESTED_BUYER_ID, a_suggested_buyer_id(i)),
1853 PROJECT_ID = decode(c_project_id, 0, PROJECT_ID,
1854 a_project_id(i)),
1855 SUGGESTED_VENDOR_CONTACT = decode(c_suggested_vendor_contacta,
1856 0, SUGGESTED_VENDOR_CONTACT,
1857 a_suggested_vendor_contacta(i)),
1858 SUGGESTED_VENDOR_ITEM_NUM = decode(c_suggested_vendor_item_numa,
1859 0, SUGGESTED_VENDOR_ITEM_NUM,
1860 a_suggested_vendor_item_numa(i)),
1861 SUGGESTED_VENDOR_NAME = decode(c_suggested_vendor_namea,
1862 0, SUGGESTED_VENDOR_NAME,
1863 a_suggested_vendor_namea(i)),
1864 SUGGESTED_VENDOR_PHONE = decode(c_suggested_vendor_phonea,
1865 0, SUGGESTED_VENDOR_PHONE,
1866 a_suggested_vendor_phonea(i)),
1867 SUGGESTED_VENDOR_SITE = decode(c_suggested_vendor_sitea,
1868 0, SUGGESTED_VENDOR_SITE,
1869 a_suggested_vendor_sitea(i)),
1870 TASK_ID = decode(c_task_id, 0, TASK_ID, a_task_id(i)),
1871 UNIT_OF_MEASURE = decode(c_unit_of_measurement,
1872 0, UNIT_OF_MEASURE,
1873 a_unit_of_measurement(i)),
1874 UNIT_PRICE = decode(c_unit_price, 0, UNIT_PRICE,
1875 a_unit_price(i)),
1876 DELIVER_TO_LOCATION_ID = decode(c_deliver_to_location_id_l,
1877 0, DELIVER_TO_LOCATION_ID,
1878 a_deliver_to_location_id_l(i)),
1879 DESTINATION_ORGANIZATION_ID = decode(c_dest_org_id_l,
1880 0, DESTINATION_ORGANIZATION_ID,
1881 a_dest_org_id_l(i)),
1882 DELIVER_TO_LOCATION = decode(c_deliver_to_location_l,
1883 0, DELIVER_TO_LOCATION,
1884 a_deliver_to_location_l(i)),
1885 LINE_ATTRIBUTE1 = decode(c_line_attribute_1a,
1886 0, LINE_ATTRIBUTE1,
1887 a_line_attribute_1a(i)),
1888 LINE_ATTRIBUTE2 = decode(c_line_attribute_2a,
1889 0, LINE_ATTRIBUTE2,
1890 a_line_attribute_2a(i)),
1891 LINE_ATTRIBUTE3 = decode(c_line_attribute_3a,
1892 0, LINE_ATTRIBUTE3,
1893 a_line_attribute_3a(i)),
1894 LINE_ATTRIBUTE4 = decode(c_line_attribute_4a,
1895 0, LINE_ATTRIBUTE4,
1896 a_line_attribute_4a(i)),
1897 LINE_ATTRIBUTE5 = decode(c_line_attribute_5a,
1898 0, LINE_ATTRIBUTE5,
1899 a_line_attribute_5a(i)),
1900 LINE_ATTRIBUTE6 = decode(c_line_attribute_6a,
1901 0, LINE_ATTRIBUTE6,
1902 a_line_attribute_6a(i)),
1903 LINE_ATTRIBUTE7 = decode(c_line_attribute_7a,
1904 0, LINE_ATTRIBUTE7,
1905 a_line_attribute_7a(i)),
1906 LINE_ATTRIBUTE8 = decode(c_line_attribute_8a,
1907 0, LINE_ATTRIBUTE8,
1908 a_line_attribute_8a(i)),
1909 LINE_ATTRIBUTE9 = decode(c_line_attribute_9a,
1910 0, LINE_ATTRIBUTE9,
1911 a_line_attribute_9a(i)),
1912 LINE_ATTRIBUTE10 = decode(c_line_attribute_10a,
1913 0, LINE_ATTRIBUTE10,
1914 a_line_attribute_10a(i)),
1915 LINE_ATTRIBUTE11 = decode(c_line_attribute_11a,
1916 0, LINE_ATTRIBUTE11,
1917 a_line_attribute_11a(i)),
1918 LINE_ATTRIBUTE12 = decode(c_line_attribute_12a,
1919 0, LINE_ATTRIBUTE12,
1920 a_line_attribute_12a(i)),
1921 LINE_ATTRIBUTE13 = decode(c_line_attribute_13a,
1922 0, LINE_ATTRIBUTE13,
1923 a_line_attribute_13a(i)),
1924 LINE_ATTRIBUTE14 = decode(c_line_attribute_14a,
1925 0, LINE_ATTRIBUTE14,
1926 a_line_attribute_14a(i)),
1927 LINE_ATTRIBUTE15 = decode(c_line_attribute_15a,
1928 0, LINE_ATTRIBUTE15,
1929 a_line_attribute_15a(i))
1930 --bug 690784 command out the following line and add two lines
1931 -- where CART_LINE_ID = icx_cart_line_ida(i);
1932 where CART_ID = v_cart_id
1933 and CART_LINE_ID = icx_cart_line_ida(i);
1934 --end modification
1935
1936 update icx_cart_line_distributions
1937 set LAST_UPDATE_DATE = sysdate,
1938 CHARGE_ACCOUNT_SEGMENT1 = decode(c_charge_acct_seg1a,
1939 0, CHARGE_ACCOUNT_SEGMENT1,
1940 a_charge_acct_seg1a(i)),
1941 CHARGE_ACCOUNT_SEGMENT2 = decode(c_charge_acct_seg2a,
1942 0, CHARGE_ACCOUNT_SEGMENT2,
1943 a_charge_acct_seg2a(i)),
1944 CHARGE_ACCOUNT_SEGMENT3 = decode(c_charge_acct_seg3a,
1945 0, CHARGE_ACCOUNT_SEGMENT3,
1946 a_charge_acct_seg3a(i)),
1947 CHARGE_ACCOUNT_SEGMENT4 = decode(c_charge_acct_seg4a,
1948 0, CHARGE_ACCOUNT_SEGMENT4,
1949 a_charge_acct_seg4a(i)),
1950 CHARGE_ACCOUNT_SEGMENT5 = decode(c_charge_acct_seg5a,
1951 0, CHARGE_ACCOUNT_SEGMENT5,
1952 a_charge_acct_seg5a(i)),
1953 CHARGE_ACCOUNT_SEGMENT6 = decode(c_charge_acct_seg6a,
1954 0, CHARGE_ACCOUNT_SEGMENT6,
1955 a_charge_acct_seg6a(i)),
1956 CHARGE_ACCOUNT_SEGMENT7 = decode(c_charge_acct_seg7a,
1957 0, CHARGE_ACCOUNT_SEGMENT7,
1958 a_charge_acct_seg7a(i)),
1959 CHARGE_ACCOUNT_SEGMENT8 = decode(c_charge_acct_seg8a,
1960 0, CHARGE_ACCOUNT_SEGMENT8,
1961 a_charge_acct_seg8a(i)),
1962 CHARGE_ACCOUNT_SEGMENT9 = decode(c_charge_acct_seg9a,
1963 0, CHARGE_ACCOUNT_SEGMENT9,
1964 a_charge_acct_seg9a(i)),
1965 CHARGE_ACCOUNT_SEGMENT10 = decode(c_charge_acct_seg10a,
1966 0, CHARGE_ACCOUNT_SEGMENT10,
1967 a_charge_acct_seg10a(i)),
1968 CHARGE_ACCOUNT_SEGMENT11 = decode(c_charge_acct_seg11a,
1969 0, CHARGE_ACCOUNT_SEGMENT11,
1970 a_charge_acct_seg11a(i)),
1971 CHARGE_ACCOUNT_SEGMENT12 = decode(c_charge_acct_seg12a,
1972 0, CHARGE_ACCOUNT_SEGMENT12,
1973 a_charge_acct_seg12a(i)),
1974 CHARGE_ACCOUNT_SEGMENT13 = decode(c_charge_acct_seg13a,
1975 0, CHARGE_ACCOUNT_SEGMENT13,
1976 a_charge_acct_seg13a(i)),
1977 CHARGE_ACCOUNT_SEGMENT14 = decode(c_charge_acct_seg14a,
1978 0, CHARGE_ACCOUNT_SEGMENT14,
1979 a_charge_acct_seg14a(i)),
1980 CHARGE_ACCOUNT_SEGMENT15 = decode(c_charge_acct_seg15a,
1981 0, CHARGE_ACCOUNT_SEGMENT15,
1982 a_charge_acct_seg15a(i)),
1983 CHARGE_ACCOUNT_SEGMENT16 = decode(c_charge_acct_seg16a,
1984 0, CHARGE_ACCOUNT_SEGMENT16,
1985 a_charge_acct_seg16a(i)),
1986 CHARGE_ACCOUNT_SEGMENT17 = decode(c_charge_acct_seg17a,
1987 0, CHARGE_ACCOUNT_SEGMENT17,
1988 a_charge_acct_seg17a(i)),
1989 CHARGE_ACCOUNT_SEGMENT18 = decode(c_charge_acct_seg18a,
1990 0, CHARGE_ACCOUNT_SEGMENT18,
1991 a_charge_acct_seg18a(i)),
1992 CHARGE_ACCOUNT_SEGMENT19 = decode(c_charge_acct_seg19a,
1993 0, CHARGE_ACCOUNT_SEGMENT19,
1994 a_charge_acct_seg19a(i)),
1995 CHARGE_ACCOUNT_SEGMENT20 = decode(c_charge_acct_seg20a,
1996 0, CHARGE_ACCOUNT_SEGMENT20,
1997 a_charge_acct_seg20a(i)),
1998 CHARGE_ACCOUNT_SEGMENT21 = decode(c_charge_acct_seg21a,
1999 0, CHARGE_ACCOUNT_SEGMENT21,
2000 a_charge_acct_seg21a(i)),
2001 CHARGE_ACCOUNT_SEGMENT22 = decode(c_charge_acct_seg22a,
2002 0, CHARGE_ACCOUNT_SEGMENT22,
2003 a_charge_acct_seg22a(i)),
2004 CHARGE_ACCOUNT_SEGMENT23 = decode(c_charge_acct_seg23a,
2005 0, CHARGE_ACCOUNT_SEGMENT23,
2006 a_charge_acct_seg23a(i)),
2007 CHARGE_ACCOUNT_SEGMENT24 = decode(c_charge_acct_seg24a,
2008 0, CHARGE_ACCOUNT_SEGMENT24,
2009 a_charge_acct_seg24a(i)),
2010 CHARGE_ACCOUNT_SEGMENT25 = decode(c_charge_acct_seg25a,
2011 0, CHARGE_ACCOUNT_SEGMENT25,
2012 a_charge_acct_seg25a(i)),
2013 CHARGE_ACCOUNT_SEGMENT26 = decode(c_charge_acct_seg26a,
2014 0, CHARGE_ACCOUNT_SEGMENT26,
2015 a_charge_acct_seg26a(i)),
2016 CHARGE_ACCOUNT_SEGMENT27 = decode(c_charge_acct_seg27a,
2017 0, CHARGE_ACCOUNT_SEGMENT27,
2018 a_charge_acct_seg27a(i)),
2019 CHARGE_ACCOUNT_SEGMENT28 = decode(c_charge_acct_seg28a,
2020 0, CHARGE_ACCOUNT_SEGMENT28,
2021 a_charge_acct_seg28a(i)),
2022 CHARGE_ACCOUNT_SEGMENT29 = decode(c_charge_acct_seg29a,
2023 0, CHARGE_ACCOUNT_SEGMENT29,
2024 a_charge_acct_seg29a(i)),
2025 CHARGE_ACCOUNT_SEGMENT30 = decode(c_charge_acct_seg30a,
2026 0, CHARGE_ACCOUNT_SEGMENT30,
2027 a_charge_acct_seg30a(i))
2028 where CART_LINE_ID = icx_cart_line_ida(i);
2029
2030
2031 -- dc default location id from header with id if location code is on
2032 -- default location id,code from header if location code is off at line
2033 -- LOcation Location Location
2034
2035 if (c_DELIVER_TO_LOCATION_L > 0) then
2036 --Location was on
2037 update icx_shopping_cart_lines
2038 set (DELIVER_TO_LOCATION_ID, DESTINATION_ORGANIZATION_ID) =
2039 (select hrl.location_id,
2040 nvl(hrl.inventory_organization_id,
2041 fsp.inventory_organization_id)
2042 from hr_locations hrl,
2043 financials_system_parameters fsp
2044 where sysdate < nvl(hrl.inactive_date, sysdate + 1)
2045 and hrl.location_code =
2046 icx_shopping_cart_lines.DELIVER_TO_LOCATION)
2047 where DELIVER_TO_LOCATION_ID is null
2048 and CART_ID = v_cart_id;
2049
2050 else
2051 -- if location on at parent copy org from the parent
2052 if (icx_deliver_to_location <> FND_API.G_MISS_CHAR) then
2053 update icx_shopping_cart_lines
2054 set (DELIVER_TO_LOCATION_ID, DESTINATION_ORGANIZATION_ID,
2055 DELIVER_TO_LOCATION) =
2056 (select DELIVER_TO_LOCATION_ID,
2057 DESTINATION_ORGANIZATION_ID,
2058 DELIVER_TO_LOCATION
2059 from icx_shopping_Carts
2060 where cart_id = v_cart_id)
2061 where cart_id = v_cart_id;
2062 end if;
2063 end if;
2064
2065 -- If supplier can be entered and the field is blank, set to the first line
2066
2067 if (c_suggested_vendor_namea > 0) then
2068 update icx_shopping_cart_lines
2069 set (SUGGESTED_VENDOR_CONTACT, SUGGESTED_VENDOR_PHONE,
2070 SUGGESTED_VENDOR_NAME, SUGGESTED_VENDOR_SITE) =
2071 (select SUGGESTED_VENDOR_CONTACT, SUGGESTED_VENDOR_PHONE,
2072 SUGGESTED_VENDOR_NAME, SUGGESTED_VENDOR_SITE
2073 from icx_shopping_cart_lines
2074 where cart_id = v_cart_id
2075 and cart_line_number = (select min(cart_line_number)
2076 from icx_shopping_cart_lines
2077 where cart_id = v_cart_id
2078 and suggested_vendor_name is not NULL))
2079
2080 where cart_id = v_cart_id
2081 and SUGGESTED_VENDOR_NAME is null;
2082 end if;
2083
2084 -- validate distribution account on R11
2085 icx_req_acct2.validate_charge_account(v_cart_id,
2086 icx_cart_line_ida(i));
2087
2088
2089 -- now get the account
2090 /*
2091 icx_req_custom.cart_custom_build_req_account(icx_cart_line_ida(i),
2092 v_account_num,
2093 v_account_id,
2094 v_return_code);
2095
2096 if ((v_account_num is null) and (v_account_id is null)) then
2097 -- get the default account
2098 open acctBuild(v_cart_id,icx_cart_line_ida(i), n_emp_id, n_org_id);
2099 fetch acctBuild into v_account_id, v_expense_account;
2100 close acctBuild;
2101 if v_account_id is null then
2102 v_account_id := v_expense_account;
2103 end if;
2104 end if;
2105 if ((v_account_num is null) and (v_account_id is null)) then
2106 --add error
2107 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
2108 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', a_item_description(i));
2109 v_error_message := FND_MESSAGE.GET;
2110 icx_util.add_error(v_error_message);
2111 storeerror(v_cart_id, v_error_message);
2112 else
2113 if (v_account_id is not null) then
2114 select count(*) into v_exist
2115 from gl_sets_of_books gsb,
2116 financials_system_parameters fsp,
2117 gl_code_combinations gl
2118 where gsb.SET_OF_BOOKS_ID = fsp.set_of_books_id
2119 and gsb.CHART_OF_ACCOUNTS_ID = gl.CHART_OF_ACCOUNTS_ID
2120 and gl.CODE_COMBINATION_ID = v_account_id;
2121 if (v_exist = 0) then
2122 --add error
2123 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
2124 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', a_item_description(i));
2125 v_error_message := FND_MESSAGE.GET;
2126 icx_util.add_error(v_error_message);
2127 storeerror(v_cart_id, v_error_message);
2128 -- set saved_flag to 4 (error exists)
2129 end if;
2130 else
2131
2132 open get_acct;
2133 fetch get_acct into v_structure;
2134 close get_acct;
2135 v_account_id := fnd_flex_ext.get_ccid('SQLGL', 'GL#',
2136 v_structure,
2137 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),
2138 v_account_num);
2139 if (v_account_id is null) or (v_account_id = 0) then
2140 --add error
2141 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
2142 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', a_item_description(i));
2143 v_error_message := FND_MESSAGE.GET;
2144 icx_util.add_error(v_error_message);
2145 storeerror(v_cart_id, v_error_message);
2146 v_account_id := null;
2147 end if;
2148 end if;
2149 end if;
2150
2151 icx_req_custom.cart_custom_build_req_account2(icx_cart_line_ida(i),
2152 v_variance_acct_id,
2153 v_budget_acct_id,
2154 v_accrual_acct_id,
2155 v_return_code);
2156
2157 update icx_cart_line_distributions
2158 set CHARGE_ACCOUNT_ID = v_account_id,
2159 ACCRUAL_ACCOUNT_ID = v_accrual_acct_id,
2160 VARIANCE_ACCOUNT_ID = v_variance_acct_id,
2161 BUDGET_ACCOUNT_ID = v_budget_acct_id
2162 where CART_LINE_ID = icx_cart_line_ida(i);
2163 */
2164
2165
2166 i := icx_cart_line_ida.NEXT(i);
2167 end LOOP;
2168
2169
2170 /*********************************************************************/
2171 /********* Validate LINES **********/
2172 /*********************************************************************/
2173
2174
2175 icx_req_custom.reqs_validate_line(emergency, v_cart_id);
2176
2177 --empty lines
2178 for prec in line_ids(v_cart_id) loop
2179 delete icx_cart_line_distributions
2180 where cart_line_id = prec.cart_line_id;
2181 end loop;
2182 delete icx_shopping_cart_lines
2183 where (quantity is null
2184 or quantity = 0)
2185 and cart_id = v_cart_id;
2186
2187 -- Validate LOcation Location Location
2188 if (c_DELIVER_TO_LOCATION_L > 0) then
2189
2190 --that just updated valid locations
2191
2192 v_errored := FALSE;
2193
2194 for prec in invalid_locations(v_cart_id, n_org_id) loop
2195 v_error_message :=
2196 icx_util.getPrompt(601, 'ICX_SHOPPING_CART_HEADER_R',
2197 178, 'ICX_DELIVER_TO_LOCATION');
2198
2199 FND_MESSAGE.SET_NAME('ICX','ICX_INVALID_ENTRY');
2200 FND_MESSAGE.SET_TOKEN('INVALID_TOKEN',v_error_message || ': ' ||
2201 prec.DELIVER_TO_LOCATION);
2202 v_error_message := FND_MESSAGE.GET;
2203
2204 FND_MESSAGE.SET_NAME('ICX','ICX_LINE_NUMBER');
2205 FND_MESSAGE.SET_TOKEN('LINE_NUM_TOKEN',to_char(prec.cart_line_number));
2206 v_error_message := FND_MESSAGE.GET || ': ' || v_error_message;
2207 icx_util.add_error(v_error_message);
2208 storeerror(v_cart_id,v_error_message);
2209 end loop;
2210 update icx_shopping_cart_lines
2211 set (DELIVER_TO_LOCATION_ID,
2212 DESTINATION_ORGANIZATION_ID) =
2213 (select null,
2214 DESTINATION_ORGANIZATION_ID
2215 from icx_shopping_carts
2216 where cart_id = v_cart_id)
2217 where cart_id = v_cart_id
2218 and DELIVER_TO_LOCATION_ID is null;
2219 end if;
2220
2221 if (emergency = 'YES') then
2222 -- in an emergency, you can only have one supplier
2223 SELECT count(distinct(SUGGESTED_VENDOR_NAME)) into supp_count
2224 FROM icx_shopping_cart_lines
2225 where cart_id = v_cart_id;
2226
2227 if (supp_count > 1) then
2228 FND_MESSAGE.SET_NAME('ICX','ICX_EMERGENCY_PO_VENDOR');
2229 v_error_message := FND_MESSAGE.GET;
2230 icx_util.add_error(v_error_message);
2231 storeerror(v_cart_id,v_error_message);
2232 elsif (supp_count = 0) then
2233 v_error_message := icx_util.getPrompt(601,'ICX_SHOPPING_CART_LINES_EMG_R',178,'ICX_SUGGESTED_VENDOR_NAME');
2234 FND_MESSAGE.SET_NAME('ICX','ICX_REQUIRED_FIELD');
2235 FND_MESSAGE.SET_TOKEN('FIELD_NAME_TOKEN',v_error_message);
2236 v_error_message := FND_MESSAGE.GET;
2237
2238 --htp.p(v_error_message);
2239 --return;
2240
2241 icx_util.add_error(v_error_message);
2242 storeerror(v_cart_id,v_error_message);
2243 end if;
2244 end if;
2245
2246
2247 -- Check to see how many lines there are
2248 open get_line_count(v_cart_id);
2249 fetch get_line_count into supp_count;
2250 close get_line_count;
2251
2252 end if; /* if icx_cart_line_ida.COUNT > 0 */
2253
2254
2255 if (icx_util.error_count = 0) then
2256 l_err_loadinterface := 'N';
2257 if user_action = 'SAVE' then
2258 update icx_shopping_Carts
2259 set saved_flag = 1
2260 where cart_id = v_cart_id
2261 and nvl(org_id,-9999) = nvl(n_org_id,-9999);
2262 elsif ((user_action = 'PLACE ORDER') and (supp_count > 0)) then
2263 -- Load the req interface table....
2264 -- in here
2265 update icx_shopping_Carts
2266 set saved_flag = 0
2267 where cart_id = v_cart_id
2268 and nvl(org_id, -9999) = nvl(n_org_id, -9999);
2269 begin
2270 icx_load_req_interface.load_shopcart_to_interface(v_cart_id);
2271 exception
2272 WHEN OTHERS THEN
2273 l_err_num := SQLCODE;
2274 l_error_message := SQLERRM;
2275 select substr(l_error_message,12,512) into l_err_mesg from dual;
2276 icx_util.add_error(l_err_mesg);
2277 storeerror(v_cart_id,l_err_mesg);
2278 l_err_loadinterface := 'Y';
2279
2280 end;
2281
2282 if l_err_loadinterface = 'N' then
2283 update icx_shopping_carts
2284 set saved_flag = '2'
2285 where saved_flag = '0'
2286 and cart_id = v_cart_id
2287 and nvl(org_id, -9999) = nvl(n_org_id, -9999);
2288 end if;
2289 end if;
2290 else
2291 -- set saved_flag to 4 (error exists)
2292 update icx_shopping_carts
2293 set saved_flag = '4'
2294 where cart_id = v_cart_id
2295 and nvl(org_id, -9999) = nvl(n_org_id, -9999);
2296
2297 end if;
2298
2299 else
2300 -- delete the cart
2301 delete icx_shopping_carts
2302 where cart_id = v_cart_id
2303 and shopper_id = v_shopper_id;
2304
2305 delete icx_cart_line_distributions
2306 where cart_id = v_cart_id;
2307
2308 delete icx_shopping_cart_lines
2309 where cart_id = v_cart_id;
2310
2311 delete icx_cart_distributions
2312 where cart_id = v_cart_id;
2313
2314
2315 end if;
2316
2317
2318 if (user_action = 'PLACE ORDER') then
2319 v_action := 'PO';
2320 else
2321 v_action := user_action;
2322 end if;
2323
2324 --changed by alex
2325
2326 if (icx_util.error_count = 0) and (user_action <> 'MODIFY') and
2327 (user_action <> 'GET_PO_MODIFY') and (user_action <> 'ATTACHMENT') and
2328 ((v_action <> 'PO') or (supp_count > 0)) then
2329 become_top(icx_cart_id, emergency, v_action);
2330 elsif (icx_util.error_count = 0) and (user_action = 'ATTACHMENT') then
2331
2332 --This call does not repaint the whole page under IE 3.0. alex
2333 /*
2334 fnd_webattch.Summary(function_name=>icx_call.encrypt2('ICX_REQS'),
2335 entity_name=>icx_call.encrypt2(entity_name),
2336 pk1_value=>icx_call.encrypt2(pk1),
2337 pk2_value=>icx_call.encrypt2(pk2),
2338 pk3_value=>icx_call.encrypt2(pk3),
2339 pk4_value=>icx_call.encrypt2( NULL),
2340 pk5_value=>icx_call.encrypt2(NULL),
2341 from_url=>icx_call.encrypt2(from_url),
2342 query_only=>icx_call.encrypt2(query_only));
2343 */
2344
2345 htp.p('<BODY onLoad="open(''fnd_webattch.Summary?function_name=' ||
2346 icx_call.encrypt2('ICX_REQS')||
2347 '&entity_name=' || icx_call.encrypt2(entity_name) ||
2348 '&pk1_value=' || icx_call.encrypt2(pk1) ||
2349 '&pk2_value=' || icx_call.encrypt2(pk2) ||
2350 '&pk3_value=' || icx_call.encrypt2(pk3) ||
2351 '&pk4_value=' || icx_call.encrypt2( NULL) ||
2352 '&pk5_value=' || icx_call.encrypt2( NULL) ||
2353 '&from_url=' || icx_call.encrypt2(from_url) ||
2354 '&query_only=' || icx_call.encrypt2(query_only) ||
2355 ''', ''_top'')">');
2356
2357 else
2358 ICX_REQ_ORDER.my_order(N_ORG => icx_call.encrypt2(d_org_id),
2359 N_EMERGENCY => icx_call.encrypt2(emergency),
2360 N_CART_ID => icx_cart_id);
2361 end if;
2362
2363
2364
2365 end if;
2366
2367
2368 exception
2369 when cart_exists then
2370 become_top(icx_cart_id, emergency, 'CE');
2371 when OTHERS then
2372 l_err_num := SQLCODE;
2373 l_error_message := SQLERRM;
2374 select substr(l_error_message,12,512) into l_err_mesg from dual;
2375 icx_util.add_error(l_err_mesg);
2376 storeerror(v_cart_id,l_err_mesg);
2377 icx_util.error_page_print;
2378 return;
2379 end;
2380
2381 end ICX_REQ_SUBMIT;