[Home] [Help]
PACKAGE BODY: APPS.ICX_REQ_SPECIAL_ORD
Source
1 PACKAGE BODY icx_req_special_ord AS
2 /* $Header: ICXRQSPB.pls 115.3 99/07/17 03:23:29 porting sh $ */
3
4 ------------------------------------------------------------
5 PROCEDURE special_order(n_org VARCHAR2,
6 v_special_order_rec IN special_order_record
7 DEFAULT v_empty_special_order_rec,
8 v_error_flag IN VARCHAR2 DEFAULT NULL,
9 v_error_text IN VARCHAR2 DEFAULT NULL,
10 v_rows_inserted IN VARCHAR2 DEFAULT NULL,
11 v_order_total_message IN VARCHAR2 DEFAULT NULL) IS
12 ------------------------------------------------------------
13 v_dcdName varchar2(1000);
14
15 begin
16
17 -- Get the execution environment
18 v_dcdName := owa_util.get_cgi_env('SCRIPT_NAME');
19
20
21 -- We need to split into 2 frames
22 htp.p('<FRAMESET ROWS="*,40" BORDER=0>');
23 htp.p('<FRAME SRC="' || v_dcdName ||
24 '/ICX_REQ_special_ord.special_order_display?n_org=' ||
25 n_org ||
26 '" NAME="data" FRAMEBORDER=NO MARGINWIDTH=0 MARGINHEIGHT=0 NORESIZE>');
27
28 htp.p('<FRAME NAME="k_buttons" SRC="' || v_dcdName ||
29 '/ICX_REQ_special_ord.special_order_buttons" MARGINWIDTH=0 MARGINHEIGHT=0 FRAMEBORDER=NO NORESIZE SCROLLING="NO">');
30 htp.p('</FRAMESET>');
31
32 exception
33 when others then
34 htp.p(SQLERRM);
35 end;
36
37
38 ------------------------------------------------------------
39 PROCEDURE special_order_buttons is
40 ------------------------------------------------------------
41
42 v_lang varchar2(5);
43
44 begin
45 -- get lang code
46 v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
47
48
49 htp.p('<BODY BGCOLOR="#FFCCFF">');
50
51 htp.p('<TABLE BORDER=0>');
52 htp.p('<TD>');
53 htp.p('<TD width=1000></TD><TD>');
54 FND_MESSAGE.SET_NAME('ICX','ICX_ADD_TO_ORDER');
55 icx_util.DynamicButton(P_ButtonText => FND_MESSAGE.GET,
56 P_ImageFileName => 'FNDBNEW.gif',
57 P_OnMouseOverText => FND_MESSAGE.GET,
58 P_HyperTextCall => 'javascript:parent.frames[0].imClicked()',
59 P_LanguageCode => v_lang,
60 P_JavaScriptFlag => FALSE);
61
62 htp.p('</TD></TABLE>');
63 htp.p('</BODY>');
64 end;
65
66
67 ------------------------------------------------------------
68 PROCEDURE special_order_display(n_org VARCHAR2,
69 v_special_order_rec IN special_order_record
70 DEFAULT v_empty_special_order_rec,
71 v_error_flag IN VARCHAR2 DEFAULT NULL,
72 v_error_text IN VARCHAR2 DEFAULT NULL,
73 v_rows_inserted IN VARCHAR2 DEFAULT NULL,
74 v_order_total_message IN VARCHAR2 DEFAULT NULL) IS
75 ------------------------------------------------------------
76 /* No defaults set at this point
77 CURSOR defaults IS
78 SELECT plt.category_id,
79 nvl(mck.DESCRIPTION, mck.concatenated_segments) category_name,
80 plt.unit_of_measure,
81 psp.line_type_id
82 FROM po_line_types plt,
83 po_system_parameters psp,
84 mtl_categories_kfv mck
85 WHERE plt.line_type_id = psp.line_type_id
86 AND plt.category_id = mck.category_id (+);
87 */
88
89 CURSOR c_uom IS
90 SELECT unit_of_measure, uom_code
91 FROM mtl_units_of_measure
92 WHERE NVL( disable_date, SYSDATE+1) > SYSDATE
93 ORDER BY unit_of_measure;
94
95 CURSOR cat_set IS
96 SELECT category_set_id,
97 validate_flag
98 FROM mtl_default_sets_view
99 WHERE functional_area_id = 2;
100
101 where_clause VARCHAR2(2000) := NULL;
102 v_default_cat_id number;
103 v_default_cat_name varchar2(50);
104 v_default_uom varchar2(25);
105 v_default_line_type number;
106 v_org number;
107 v_lang varchar2(30);
108 c_title varchar2(80);
109 c_prompts icx_util.g_prompts_table;
110
111 v_value varchar2(240);
112 v_attribute varchar2(240);
113 v_table_attribute varchar2(240);
114
115 -- (MC) removed local variables v_results_table and v_regions_table to
116 -- save space. Using global variables in ak_query_pkg instead.
117
118 v_items_table ak_query_pkg.items_table_type;
119
120
121 v_vendor_on_flag varchar2(1);
122 v_dcdName VARCHAR2(1000);
123 -- v_select_text VARCHAR2 (20000) := NULL;
124 -- Fix for bug 526274
125 v_select_text LONG := NULL;
126 v_category_pop_list varchar2(1) := 'N';
127 v_uom_pop_list varchar2(1) := 'N';
128 v_category_set_id NUMBER := NULL;
129 v_validate_flag VARCHAR2(1) := NULL;
130 v_category_id VARCHAR2(240) := NULL;
131 v_category_name VARCHAR2(240) := NULL;
132 l_print_message VARCHAR2(240) := NULL;
133
134
135 BEGIN
136
137 -- Check if session is valid
138 IF (icx_sec.validatesession('ICX_REQS')) THEN
139 -- Decrypt parameters
140 v_org := icx_call.decrypt2(n_org);
141
142 -- Get prompts
143 -- icx_util.getPrompts(178,'ICX_ONE_TIME',c_title,c_prompts);
144 icx_util.getPrompts(601,'ICX_ONE_TIME',c_title,c_prompts);
145
146 -- Get language
147 v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
148
149 -- Get the execution environment
150 v_dcdName := owa_util.get_cgi_env('SCRIPT_NAME');
151
152 -- Special Order Related Object Navigator
153 -- Just get the structure, NO DATA i.e. P_RETURN_PARENTS => 'F'
154
155 -- (MC) Use exec_query instead of execute_query to improve performance
156 ak_query_pkg.exec_query(P_PARENT_REGION_APPL_ID => 601,
157 P_PARENT_REGION_CODE => 'ICX_REQ_SPECIAL_ORDER',
158 P_RESPONSIBILITY_ID => icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
159 P_USER_ID => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
160 P_RETURN_PARENTS => 'F',
161 P_RETURN_CHILDREN => 'F');
162
163
164 -- Make a copy of v_items_table b/c it will be overwritten by the second
165 -- exec_query;
166 v_items_table := ak_query_pkg.g_items_table;
167
168 htp.htmlOpen;
169 htp.headOpen;
170 icx_util.copyright;
171 htp.title(c_title);
172
173 js.scriptOpen;
174
175 htp.p('function check_number(field) {
176 if (!parent.parent.checkNumber(field)) {
177 field.focus();
178 field.value = "";
179 }
180 }
181 function set_category_null() {
182 // set the hidden field category to null
183 document.one_time.ICX_CATEGORY_ID.value = "";
184 }
185 ');
186
187 -- Get the message prompt for 'Fields Required'
188 FND_MESSAGE.SET_NAME('ICX', 'ICX_REQUIRED');
189
190 icx_util.LOVScript;
191
192 htp.p('function imClicked() {
193
194 var check_desc = false;
195 var check_quan = false;
196 var check_price = false;
197 var check_uom = false;
198 var check_category = false;
199
200 if (document.one_time.ICX_ITEM_DESCRIPTION.value <> "")
201 check_desc = true;
202 if (document.one_time.ICX_QTY_V.value <> "")
203 if (parseFloat(document.one_time.ICX_QTY_V.value) >= 0 )
204 check_quan = true;
205 if (document.one_time.ICX_UNIT_PRICE.value <> "")
206 check_price = true;
207 if (document.one_time.ICX_UNIT_OF_MEASUREMENT.value <> "")
208 check_uom = true;
209 if (document.one_time.ICX_CATEGORY_NAME.value <> "")
210 check_category = true;
211
212 if ((check_desc) && (check_quan) && (check_price) && (check_uom) && (check_category)) {
213 document.one_time.cartId.value = parent.parent.cartId;
214 // document.one_time.cartId.value = 9999;
215 document.one_time.submit();
216 } else {
217 alert("' || icx_util.replace_quotes(FND_MESSAGE.GET) || '");
218 }
219 }
220 ');
221
222
223 chk_vendor_on(v_items_table,v_vendor_on_flag);
224
225 -- FND_MESSAGE.SET_NAME('ICX', 'ICX_REQUIRED');
226 js.scriptClose;
227
228 htp.bodyOpen('','BGCOLOR="#FFCCFF" onLoad="parent.parent.winOpen(''nav'', ''special_order'')"');
229
230 /* Table approach is used here to create a blank space at the beginning
231 of the text. Otherwise the text will run into the left end of the
232 browser. All the prompt will have tables associated with it.
233 */
234
235 /* Print the help text on the top */
236 FND_MESSAGE.SET_NAME('ICX', 'ICX_SELECT_CATG_ENT');
237 htp.tableOpen;
238 htp.tableRowOpen('BORDER = 0');
239 htp.tableData(cvalue => ' ');
240 htp.p('<TD ALIGN=LEFT VALIGN=CENTER > ' || FND_MESSAGE.GET || '</TD>');
241 htp.tableRowClose;
242 htp.tableClose;
243
244 /* Print error message text if there are errors in the order */
245 IF v_error_flag = 'Y' THEN
246 htp.tableOpen;
247 htp.tableRowOpen('BORDER = 0');
248 htp.tableData(cvalue => ' ');
249 htp.p('<TD ALIGN=LEFT VALIGN=CENTER >' || htf.bold(v_error_text)|| '</TD>');
250 htp.tableRowClose;
251 htp.tableClose;
252 END IF;
253
254 /* Print items added and order total amount */
255 IF (to_number(v_rows_inserted) > 0 ) THEN
256 FND_MESSAGE.SET_NAME('ICX','ICX_ITEM_ADD_NEW');
257 FND_MESSAGE.SET_TOKEN('ITEM_QUANTITY', v_rows_inserted);
258 l_print_message := FND_MESSAGE.GET;
259
260 htp.tableOpen('BORDER = 0');
261 htp.tableRowOpen;
262 htp.tableData(cvalue => ' ');
263 htp.p('<TD ALIGN=LEFT VALIGN=CENTER > ' || htf.bold(l_print_message) || '</TD>');
264 htp.tableRowClose;
265 htp.tableRowOpen;
266 htp.tableData(cvalue => ' ');
267 htp.p('<TD ALIGN=LEFT VALIGN=CENTER > ' || htf.bold(v_order_total_message) || '</TD>');
268 htp.tableRowClose;
269 htp.tableClose;
270 END IF;
271
272 htp.p('<FORM ACTION="'|| v_dcdName || '/ICX_REQ_SPECIAL_ORD.add_item_to_cart " METHOD="POST" NAME="one_time" onSubmit="return(false)">');
273
274 htp.formHidden('n_org', n_org);
275 htp.formHidden('cartId', '');
276
277 htp.tableOpen('BORDER=0');
278
279 IF v_items_table.count > 0 THEN
280 FOR i IN v_items_table.FIRST .. v_items_table.LAST LOOP
281
282 v_value := '';
283 v_attribute := '';
284 v_table_attribute := ' COLSPAN=2 ';
285 v_select_text := '';
286
287 /* If error display the page with the values entered */
288
289 IF v_error_flag = 'Y' THEN
290 IF v_items_table(i).attribute_code = 'ICX_CATEGORY_NAME' THEN
291 v_value := v_special_order_rec.category_name;
292 ELSIF v_items_table(i).attribute_code = 'ICX_ITEM_DESCRIPTION' THEN
293 v_value := v_special_order_rec.item_description;
294 ELSIF v_items_table(i).attribute_code = 'ICX_UNIT_OF_MEASUREMENT' THEN
295 v_value := v_special_order_rec.unit_of_measurement;
296 ELSIF v_items_table(i).attribute_code = 'ICX_QTY_V' THEN
297 v_value := v_special_order_rec.qty_v;
298 ELSIF v_items_table(i).attribute_code = 'ICX_UNIT_PRICE' THEN
299 v_value := v_special_order_rec.unit_price;
300 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_TYPE_ID' THEN
301 v_value := v_special_order_rec.line_type_id;
302 ELSIF v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_ITEM_NUM'
303 THEN
304 v_value := v_special_order_rec.suggested_vendor_item_num;
305 ELSIF v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_NAME' THEN
306 v_value := v_special_order_rec.suggested_vendor_name;
307 ELSIF v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_PHONE' THEN
308 v_value := v_special_order_rec.suggested_vendor_phone;
309 ELSIF v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_SITE' THEN
310 v_value := v_special_order_rec.suggested_vendor_site;
311 ELSIF v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_CONTACT' THEN
312 v_value := v_special_order_rec.suggested_vendor_contact;
313 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_ATTRIBUTE_1' THEN
314 v_value := v_special_order_rec.line_attribute_1;
315 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_ATTRIBUTE_2' THEN
316 v_value := v_special_order_rec.line_attribute_2;
317 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_ATTRIBUTE_3' THEN
318 v_value := v_special_order_rec.line_attribute_3;
319 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_ATTRIBUTE_4' THEN
320 v_value := v_special_order_rec.line_attribute_4;
321 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_ATTRIBUTE_5' THEN
322 v_value := v_special_order_rec.line_attribute_5;
323 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_ATTRIBUTE_6' THEN
324 v_value := v_special_order_rec.line_attribute_6;
325 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_ATTRIBUTE_7' THEN
326 v_value := v_special_order_rec.line_attribute_7;
327 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_ATTRIBUTE_8' THEN
328 v_value := v_special_order_rec.line_attribute_8;
329 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_ATTRIBUTE_9' THEN
330 v_value := v_special_order_rec.line_attribute_9;
331 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_ATTRIBUTE_10' THEN
332 v_value := v_special_order_rec.line_attribute_10;
333 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_ATTRIBUTE_11' THEN
334 v_value := v_special_order_rec.line_attribute_11;
335 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_ATTRIBUTE_12' THEN
336 v_value := v_special_order_rec.line_attribute_12;
337 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_ATTRIBUTE_13' THEN
338 v_value := v_special_order_rec.line_attribute_13;
339 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_ATTRIBUTE_14' THEN
340 v_value := v_special_order_rec.line_attribute_14;
341 ELSIF v_items_table(i).attribute_code = 'ICX_LINE_ATTRIBUTE_15' THEN
342 v_value := v_special_order_rec.line_attribute_15;
343 END IF;
344 END IF; /* v_error_flag = 'Y' */
345
346 IF (v_items_table(i).node_display_flag = 'Y' AND
347 v_items_table(i).secured_column <> 'T') OR
348 v_items_table(i).attribute_code = 'ICX_CATEGORY_ID' OR
349 v_items_table(i).attribute_code = 'ICX_CATEGORY_NAME' OR
350 v_items_table(i).attribute_code = 'ICX_UNIT_OF_MEASUREMENT' OR
351 v_items_table(i).attribute_code = 'ICX_ITEM_DESCRIPTION' OR
352 v_items_table(i).attribute_code = 'ICX_UNIT_PRICE' OR
353 v_items_table(i).attribute_code = 'ICX_QTY_V' OR
354 v_items_table(i).attribute_code = 'ICX_LINE_TYPE_ID' OR
355 (v_vendor_on_flag = 'Y' AND
356 (v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_NAME' or
357 v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_PHONE' or
358 v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_SITE' or
359 v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_CONTACT')
360 ) THEN
361
362 htp.tableRowOpen;
363
364 --Special handling to load up default values.
365 IF v_items_table(i).attribute_code = 'ICX_CATEGORY_ID' THEN
366 v_value := v_default_cat_id;
367 ELSIF v_items_table(i).attribute_code = 'ICX_CATEGORY_NAME' THEN
368 -- v_value := v_default_cat_name;
369 v_table_attribute := ' COLSPAN=1 ';
370 v_attribute := 'onChange="set_category_null()"';
371 ELSIF v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_NAME' THEN
372 v_table_attribute := ' COLSPAN=1 ';
373 --ELSIF v_items_table(i).attribute_code = 'ICX_UNIT_OF_MEASUREMENT' THEN
374 -- v_value := v_default_uom;
375 v_table_attribute := ' COLSPAN=1 ';
376 ELSIF v_items_table(i).attribute_code = 'ICX_UNIT_PRICE' THEN
377 v_attribute := 'onChange="check_number(this)"';
378 -- ELSIF v_items_table(i).attribute_code = 'ICX_LINE_TYPE_ID' THEN
379 -- v_value := v_default_line_type;
380 ELSIF v_items_table(i).attribute_code = 'ICX_QTY_V' THEN
381 v_attribute := 'onChange="check_number(this)"';
382 ELSIF v_items_table(i).lov_attribute_code IS NOT NULL AND
383 v_items_table(i).lov_region_code IS NOT NULL AND
384 v_items_table(i).attribute_code <> 'ICX_SUGGESTED_VENDOR_PHONE' AND
385 v_items_table(i).attribute_code <> 'ICX_SUGGESTED_VENDOR_CONTACT' AND
386 v_items_table(i).attribute_code <> 'ICX_SUGGESTED_VENDOR_SITE' AND
387 v_items_table(i).attribute_code <> 'ICX_SUGGESTED_VENDOR_NAME' THEN
388 v_table_attribute := ' COLSPAN=1 ';
389 END IF;
390
391
392 IF (v_items_table(i).attribute_code <> 'ICX_ITEM_DESCRIPTION' AND
393 v_items_table(i).attribute_code <> 'ICX_QTY_V' AND
394 v_items_table(i).attribute_code <> 'ICX_UNIT_OF_MEASUREMENT' AND
395 v_items_table(i).attribute_code <> 'ICX_UNIT_PRICE' AND
396 v_items_table(i).attribute_code <> 'ICX_CATEGORY_NAME' AND
397 ((v_items_table(i).item_style = 'HIDDEN' AND
398 v_items_table(i).attribute_code <> 'ICX_SUGGESTED_VENDOR_NAME') OR
399 (v_items_table(i).item_style = 'HIDDEN' AND
400 v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_NAME' AND
401 v_vendor_on_flag <> 'Y'))) OR
402 v_items_table(i).attribute_code = 'ICX_CATEGORY_ID' OR
403 v_items_table(i).attribute_code = 'ICX_LINE_TYPE_ID' THEN
404 -- htp.p('<TD></TD>');
405 htp.p('<INPUT TYPE=''hidden'' NAME=''' || v_items_table(i).attribute_code || ''' SIZE=' || v_items_table(i).display_value_length || ' VALUE = "' || v_value || '" >' );
406
407 ELSIF v_items_table(i).update_flag = 'Y' OR
408 v_items_table(i).attribute_code = 'ICX_ITEM_DESCRIPTION' or
409 v_items_table(i).attribute_code = 'ICX_QTY_V' or
410 v_items_table(i).attribute_code = 'ICX_UNIT_OF_MEASUREMENT' or
411 v_items_table(i).attribute_code = 'ICX_UNIT_PRICE' or
412 v_items_table(i).attribute_code = 'ICX_CATEGORY_NAME' or
413 (v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_NAME'
414 AND v_vendor_on_flag = 'Y') THEN
415
416 htp.p('<TD ALIGN=RIGHT VALIGN=CENTER WIDTH=200> ' || v_items_table(i).attribute_label_long || '</TD>');
417
418 /* IF Category or Unit of measure is a pop list, display
419 SELECT list box */
420 IF v_items_table(i).attribute_code = 'ICX_CATEGORY_NAME' AND
421 UPPER(v_items_table(i).item_style) = 'POPLIST' THEN
422
423 -- No Hierarchy setup; show regular categories
424
425 ak_query_pkg.exec_query(P_PARENT_REGION_APPL_ID => 178,
426 P_PARENT_REGION_CODE => 'ICX_REQ_CATEGORIES',
427 P_RESPONSIBILITY_ID => icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
428 P_USER_ID => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
429 P_WHERE_CLAUSE => ' FUNCTIONAL_AREA_ID = 2',
430
431 P_RETURN_PARENTS => 'T',
432 P_RETURN_CHILDREN => 'F');
433
434 IF ak_query_pkg.g_results_table.count > 0 THEN
435 FOR i IN ak_query_pkg.g_results_table.FIRST .. ak_query_pkg.g_results_table.LAST LOOP
436 -- Category Id
437 v_category_id := ak_query_pkg.g_results_table(i).value3;
438 -- Category Name
439 v_category_name :=ak_query_pkg.g_results_table(i).value1;
440 v_select_text := v_select_text || '<OPTION VALUE = ' || v_category_id || '>' || v_category_name;
441 END LOOP; /* for i */
442 END IF; /* ak_query_pkg... .count > 0 */
443 htp.tableData((htf.formSelectOpen(v_items_table(i).attribute_code) || v_select_text || htf.formSelectClose), 'LEFT');
444 v_category_pop_list := 'Y';
445
446 ELSIF (v_items_table(i).attribute_code = 'ICX_UNIT_OF_MEASUREMENT'
447 AND UPPER(v_items_table(i).item_style) = 'POPLIST') THEN
448 FOR uom_rec IN c_uom LOOP
449
450 /* if there is error, set the selected unit of measure in the
451 select list */
452 IF (v_error_flag = 'Y') AND
453 (uom_rec.unit_of_measure = v_special_order_rec.unit_of_measurement) THEN
454 v_select_text := v_select_text || '<OPTION VALUE = "' || uom_rec.unit_of_measure|| '" SELECTED>' || uom_rec.unit_of_measure;
455 ELSE
456 v_select_text := v_select_text || '<OPTION VALUE = "' || uom_rec.unit_of_measure|| '" >' || uom_rec.unit_of_measure;
457 END IF;
458
459 END LOOP;
460 htp.tableData((htf.formSelectOpen(v_items_table(i).attribute_code) || v_select_text || htf.formSelectClose), 'LEFT');
461 v_uom_pop_list := 'Y';
462
463 ELSE
464 htp.p('<TD ALIGN=' || v_items_table(i).horizontal_alignment || ' VALIGN=' || v_items_table(i).vertical_alignment || v_table_attribute || '>');
465 htp.p('<INPUT TYPE=''text'' NAME=''' || v_items_table(i).attribute_code || ''' SIZE=' || v_items_table(i).display_value_length ||
466 ' MAXLENGTH=' || v_items_table(i).attribute_value_length || ' VALUE = "' || v_value || '" ' || v_attribute || '>' );
467 htp.p('</TD>');
468 END IF;
469 END IF;
470
471 IF (v_items_table(i).attribute_code = 'ICX_CATEGORY_NAME' AND
472 UPPER(v_items_table(i).item_style) = 'TEXT') THEN
473 htp.tableData(icx_util.LOVButton(178,'ICX_CATEGORY_NAME', 601, 'ICX_REQ_SPECIAL_ORDER', 'one_time', 'data','FUNCTIONAL_AREA_ID = 2'), CATTRIBUTES => 'ALIGN="LEFT" width=200');
474 ELSIF (v_items_table(i).attribute_code = 'ICX_UNIT_OF_MEASUREMENT'
475 AND UPPER(v_items_table(i).item_style) = 'TEXT') THEN
476 htp.tableData(icx_util.LOVButton(178,'ICX_UNIT_OF_MEASUREMENT', 601, 'ICX_REQ_SPECIAL_ORDER', 'one_time', 'data'), CATTRIBUTES => 'ALIGN="LEFT" width=200');
477 ELSIF v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_NAME'
478 AND v_vendor_on_flag = 'Y' THEN
479 htp.tableData(icx_util.LOVButton(178,'ICX_SUGGESTED_VENDOR_NAME', 601, 'ICX_REQ_SPECIAL_ORDER', 'one_time', 'data'), CATTRIBUTES => 'ALIGN="LEFT" width=200');
480 ELSIF v_items_table(i).lov_attribute_code is not NULL AND
481 v_items_table(i).lov_region_code is not NULL AND
482 v_items_table(i).attribute_code <> 'ICX_SUGGESTED_VENDOR_PHONE' AND
483 v_items_table(i).attribute_code <> 'ICX_SUGGESTED_VENDOR_CONTACT' AND
484 v_items_table(i).attribute_code <> 'ICX_SUGGESTED_VENDOR_SITE' AND
485 v_items_table(i).attribute_code <> 'ICX_SUGGESTED_VENDOR_NAME' AND
486 v_items_table(i).node_display_flag = 'Y' AND
487 v_items_table(i).update_flag = 'Y' AND
488 v_items_table(i).item_style <> 'HIDDEN' AND
489 v_items_table(i).item_style <> 'POPLIST' THEN
490 htp.tableData(icx_util.LOVButton(178,v_items_table(i).attribute_code, 601, 'ICX_REQ_SPECIAL_ORDER','one_time','data'), CATTRIBUTES => 'ALIGN="LEFT" width=200');
491
492 END IF;
493 htp.tableRowClose;
494 END IF;
495
496 END LOOP;
497 END IF; /* table count > 0 */
498
499
500 htp.tableRowOpen;
501 htp.tableRowClose;
502
503 htp.formHidden('v_category_pop_list', v_category_pop_list);
504 htp.formHidden('v_uom_pop_list', v_uom_pop_list);
505 /*
506 htp.tableRowOpen;
507 htp.tableData;
508 htp.p('<TD>');
509 FND_MESSAGE.SET_NAME('BOM','ADD');
510 icx_util.DynamicButton(P_ButtonText => c_prompts(9),
511 P_ImageFileName => 'FNDBNEW.gif',
512 P_OnMouseOverText => FND_MESSAGE.GET,
513 P_HyperTextCall => 'javascript:imClicked()',
514 P_LanguageCode => v_lang,
515 P_JavaScriptFlag => FALSE);
516
517
518 htp.p('</TD>');
519 htp.tableRowClose;
520 */
521 htp.tableClose;
522
523 htp.p('</FORM>');
524
525 htp.bodyClose;
526 htp.htmlClose;
527
528 END IF; /* if validate session */
529 EXCEPTION
530
531 WHEN OTHERS THEN
532 htp.p('Error in Special Order ' || substr(SQLERRM, 1, 512));
533 -- icx_util.add_error(substr(SQLERRM, 12, 512));
534 -- icx_util.error_page_print;
535
536
537 END ;
538
539
540 PROCEDURE add_item_to_cart (n_org IN VARCHAR2,
541 cartId in VARCHAR2,
542 icx_category_id IN VARCHAR2 DEFAULT NULL,
543 icx_category_name IN VARCHAR2 DEFAULT NULL,
544 icx_item_description IN VARCHAR2 DEFAULT NULL,
545 icx_qty_v IN VARCHAR2 DEFAULT NULL,
546 icx_unit_of_measurement IN VARCHAR2 DEFAULT NULL,
547 icx_unit_price IN VARCHAR2 DEFAULT NULL,
548 icx_suggested_vendor_item_num IN VARCHAR2 DEFAULT NULL,
549 icx_suggested_vendor_name IN VARCHAR2 DEFAULT NULL,
550 icx_suggested_vendor_site IN VARCHAR2 DEFAULT NULL,
551 icx_suggested_vendor_contact IN VARCHAR2 DEFAULT NULL,
552 icx_suggested_vendor_phone IN VARCHAR2 DEFAULT NULL,
553 icx_line_attribute_1 IN VARCHAR2 DEFAULT NULL,
554 icx_line_attribute_2 IN VARCHAR2 DEFAULT NULL,
555 icx_line_attribute_3 IN VARCHAR2 DEFAULT NULL,
556 icx_line_attribute_4 IN VARCHAR2 DEFAULT NULL,
557 icx_line_attribute_5 IN VARCHAR2 DEFAULT NULL,
558 icx_line_attribute_6 IN VARCHAR2 DEFAULT NULL,
559 icx_line_attribute_7 IN VARCHAR2 DEFAULT NULL,
560 icx_line_attribute_8 IN VARCHAR2 DEFAULT NULL,
561 icx_line_attribute_9 IN VARCHAR2 DEFAULT NULL,
562 icx_line_attribute_10 IN VARCHAR2 DEFAULT NULL,
563 icx_line_attribute_11 IN VARCHAR2 DEFAULT NULL,
564 icx_line_attribute_12 IN VARCHAR2 DEFAULT NULL,
565 icx_line_attribute_13 IN VARCHAR2 DEFAULT NULL,
566 icx_line_attribute_14 IN VARCHAR2 DEFAULT NULL,
567 icx_line_attribute_15 IN VARCHAR2 DEFAULT NULL,
568 icx_line_type_id IN VARCHAR2 DEFAULT NULL,
569 v_category_pop_list IN VARCHAR2 DEFAULT NULL,
570 v_uom_pop_list IN VARCHAR2 DEFAULT NULL)
571
572 IS
573
574 order_has_error BOOLEAN := FALSE;
575 v_error_text VARCHAR2(1000) := NULL;
576 v_special_order_rec special_order_record;
577 v_error_flag VARCHAR2(2) := NULL;
578 v_rows_inserted VARCHAR2(3) := NULL;
579 v_qty_inserted VARCHAR2(3) := NULL;
580 l_order_total_message varchar2(300) := NULL;
581 l_pad number := NULL;
582 l_qty number:= NULL;
583 l_price number:= NULL;
584
585 CURSOR getCatId(catname VARCHAR2) IS
586 SELECT category_id
587 FROM mtl_categories_kfv
588 WHERE concatenated_segments = catname;
589
590 CURSOR get_uom(v_uom VARCHAR2) IS
591 SELECT unit_of_measure
592 FROM mtl_units_of_measure
593 WHERE unit_of_measure = v_uom;
594
595 BEGIN
596
597 IF (icx_sec.validatesession('ICX_REQS')) THEN
598
599 icx_util.error_page_setup;
600
601 /* Uncomment for debugging -- Debug code
602 htp.p('Cart Id: ' || cartId);
603 htp.p('Decrypted cart id : ' || icx_call.decrypt2(cartId)); htp.br;
604 htp.p('Category Id: ' || icx_category_id);
605 htp.p('Catergory Name : ' || icx_category_name);
606 htp.p('Unit of measurement : ' || icx_unit_of_measurement);
607 htp.p('Suggested vendor name: ' || icx_suggested_vendor_name );
608 htp.p('Suggested vendor site: ' || icx_suggested_vendor_site );
609 htp.p('Suggested vendor contact: ' || icx_suggested_vendor_contact );
610 htp.p('Suggested vendor phone: ' || icx_suggested_vendor_phone );
611 htp.p('v_category_pop_list : ' || v_category_pop_list );
612 htp.p('v_uom_pop_list : ' || v_uom_pop_list );
613 htp.br;
614 */
615
616
617 IF (icx_category_id IS NULL) AND (v_category_pop_list <> 'Y') THEN
618 -- validate category
619 OPEN getCatId(ICX_CATEGORY_NAME);
620 FETCH getCatId INTO v_special_order_rec.category_id;
621 IF getCatId%NOTFOUND THEN
622 v_error_flag := 'Y';
623 FND_MESSAGE.SET_NAME('MRP','EC_CAT');
624 v_error_text := FND_MESSAGE.GET;
625 FND_MESSAGE.SET_NAME('ICX','ICX_INVALID_ENTRY');
626 FND_MESSAGE.SET_TOKEN('INVALID_TOKEN',v_error_text);
627 v_error_text := FND_MESSAGE.GET || '<BR>';
628 END IF;
629 CLOSE getCatId;
630 ELSIF (icx_category_id IS NULL) AND (v_category_pop_list = 'Y') THEN
631 /* if category is pop list, the category id is returned in the name */
632 v_special_order_rec.category_id := icx_category_name;
633 END IF; /* category_id is NULL */
634
635 IF v_uom_pop_list <> 'Y' THEN
636 -- Validate Unit of measurement
637 OPEN get_uom(ICX_UNIT_OF_MEASUREMENT);
638 FETCH get_uom into v_special_order_rec.unit_of_measurement;
639 IF get_uom%NOTFOUND THEN
640 v_error_flag := 'Y';
641 FND_MESSAGE.SET_NAME('CS','CS_ALL_INVALID_UOM_CODE');
642 FND_MESSAGE.SET_TOKEN('UOM_CODE',ICX_UNIT_OF_MEASUREMENT);
643 v_error_text := v_error_text || FND_MESSAGE.GET || '<BR>';
644 END IF;
645 CLOSE get_uom;
646 ELSE
647 v_special_order_rec.unit_of_measurement := icx_unit_of_measurement;
648 END IF; /* Unit of measurement is not null */
649
650 /* Quantity cannot be zero. */
651
652 l_pad := instr(icx_qty_v,'.',1,2);
653 if (l_pad > 2) then
654 l_qty := substr(icx_qty_v,1,l_pad - 1);
655 elsif (l_pad > 0) then
656 l_qty := 0;
657 else
658 l_qty := icx_qty_v;
659 end if;
660
661 l_pad := instr(icx_unit_price,'.',1,2);
662 if (l_pad > 2) then
663 l_price := substr(icx_unit_price,1,l_pad - 1);
664 elsif (l_pad > 0) then
665 l_price := 0;
666 else
667 l_price := icx_unit_price;
668 end if;
669
670 IF (l_qty = 0) THEN
671 v_error_flag := 'Y';
672 FND_MESSAGE.SET_NAME('ICX','ICX_QTY_IS_ZERO');
673 v_error_text := v_error_text || FND_MESSAGE.GET;
674 END IF;
675
676
677 /* Build the special_order_record */
678
679 v_special_order_rec.cart_id := cartId;
680 -- v_special_order_rec.category_id := icx_category_id;
681 v_special_order_rec.category_name := icx_category_name;
682 v_special_order_rec.item_description := icx_item_description;
683 v_special_order_rec.qty_v := l_qty;
684 -- v_special_order_rec.unit_of_measurement := icx_unit_of_measurement;
685 v_special_order_rec.unit_price := l_price;
686 -- v_special_order_rec.unit_price := icx_unit_price;
687 v_special_order_rec.suggested_vendor_item_num := icx_suggested_vendor_item_num;
688 v_special_order_rec.suggested_vendor_name := icx_suggested_vendor_name;
689 v_special_order_rec.suggested_vendor_site := icx_suggested_vendor_site;
690 v_special_order_rec.suggested_vendor_contact := icx_suggested_vendor_contact;
691 v_special_order_rec.suggested_vendor_phone := icx_suggested_vendor_phone;
692 v_special_order_rec.line_attribute_1 := icx_line_attribute_1;
693 v_special_order_rec.line_attribute_2 := icx_line_attribute_2;
694 v_special_order_rec.line_attribute_3 := icx_line_attribute_3;
695 v_special_order_rec.line_attribute_4 := icx_line_attribute_4;
696 v_special_order_rec.line_attribute_5 := icx_line_attribute_5;
697 v_special_order_rec.line_attribute_6 := icx_line_attribute_6;
698 v_special_order_rec.line_attribute_7 := icx_line_attribute_7;
699 v_special_order_rec.line_attribute_8 := icx_line_attribute_8;
700 v_special_order_rec.line_attribute_9 := icx_line_attribute_9;
701 v_special_order_rec.line_attribute_10 := icx_line_attribute_10;
702 v_special_order_rec.line_attribute_11 := icx_line_attribute_11;
703 v_special_order_rec.line_attribute_12 := icx_line_attribute_12;
704 v_special_order_rec.line_attribute_13 := icx_line_attribute_13;
705 v_special_order_rec.line_attribute_14 := icx_line_attribute_14;
706 v_special_order_rec.line_attribute_15 := icx_line_attribute_15;
707 v_special_order_rec.line_type_id := icx_line_type_id;
708
709 IF v_error_flag = 'Y' THEN
710 v_special_order_rec.category_id := icx_category_id;
711 v_special_order_rec.unit_of_measurement := icx_unit_of_measurement;
712 special_order_display(n_org, v_special_order_rec, v_error_flag, v_error_text);
713 ELSE
714 insert_order_to_cart_line (v_special_order_rec, l_order_total_message);
715 -- v_qty_inserted := icx_qty_v; /* Number of items added */
716 v_rows_inserted := '1'; /* Number of lines added; one in this case */
717 special_order_display(n_org, v_rows_inserted => v_rows_inserted,
718 v_order_total_message => l_order_total_message);
719 END IF;
720 END IF; /* Validate session */
721
722 EXCEPTION
723
724 WHEN OTHERS THEN
725 -- htp.p('Error in add to item ' || substr(SQLERRM, 1, 512));
726 v_error_flag := 'Y';
727 v_error_text := v_error_text || substr(SQLERRM, 12, 512);
728 v_special_order_rec.category_id := icx_category_id;
729 v_special_order_rec.unit_of_measurement := icx_unit_of_measurement;
730 special_order_display(n_org, v_special_order_rec, v_error_flag, v_error_text);
731 -- icx_util.add_error(substr(SQLERRM, 12, 512));
732 -- icx_util.error_page_print;
733
734 END add_item_to_cart;
735
736 PROCEDURE insert_order_to_cart_line (v_special_order_rec IN
737 special_order_record,
738 l_order_total_message OUT VARCHAR2) IS
739
740 v_cart_header_rec icx_shopping_carts%ROWTYPE;
741 v_line_type_id NUMBER := NULL;
742 v_cart_id NUMBER := NULL;
743 l_cart_line_id NUMBER := NULL;
744
745 l_currency varchar2(15);
746 l_precision NUMBER(1);
747 l_fmt_mask varchar2(32);
748 l_order_total NUMBER := NULL;
749 v_order_total varchar2(30) := NULL;
750 v_cart_line_number NUMBER := NULL;
751
752 v_error_text VARCHAR2(1000) := NULL;
753 v_error_flag VARCHAR2(2) := NULL;
754
755 /* The following variables added for default accounting */
756 l_emp_id number;
757 l_account_id NUMBER := NULL;
758 l_account_num VARCHAR2(2000) := NULL;
759 l_segments fnd_flex_ext.SegmentArray;
760
761 CURSOR cart_header_details(v_cart_id VARCHAR2) IS
762 SELECT * FROM icx_shopping_carts
763 WHERE CART_ID = to_number(v_cart_id)
764 FOR UPDATE;
765
766 BEGIN
767
768 icx_util.error_page_setup;
769
770 SELECT line_type_id INTO v_line_type_id
771 FROM po_system_parameters
772 WHERE rownum < 2;
773
774 v_cart_id := icx_call.decrypt2(v_special_order_rec.cart_id);
775 OPEN cart_header_details(v_cart_id);
776 FETCH cart_header_details INTO v_cart_header_rec;
777 CLOSE cart_header_details;
778
779 --changed by alex for attachment
780 -- SELECT icx_shopping_cart_lines_s.nextval INTO l_cart_line_id
781 -- FROM DUAL;
782 --new code:
783 SELECT PO_REQUISITION_LINES_S.nextval INTO l_cart_line_id
784 FROM DUAL;
785
786
787 /* Select the max of the cart_line_number for ordering */
788 SELECT max(cart_line_number) + 1 into v_cart_line_number
789 FROM icx_shopping_cart_lines
790 WHERE cart_id = v_cart_id;
791
792 IF v_cart_line_number IS NULL THEN
793 /* This is the first one */
794 v_cart_line_number := 1;
795 END IF;
796
797 INSERT INTO icx_shopping_cart_lines
798 (
799 CART_LINE_ID,
800 LAST_UPDATE_DATE,
801 LAST_UPDATED_BY,
802 CREATION_DATE,
803 CREATED_BY,
804 CART_ID,
805 ITEM_DESCRIPTION,
806 QUANTITY,
807 UNIT_PRICE,
808 CATEGORY_ID,
809 UNIT_OF_MEASURE,
810 LINE_TYPE_ID,
811 DESTINATION_ORGANIZATION_ID,
812 DELIVER_TO_LOCATION_ID,
813 SUGGESTED_VENDOR_NAME,
814 SUGGESTED_VENDOR_SITE,
815 LINE_ATTRIBUTE1,
816 LINE_ATTRIBUTE2,
817 LINE_ATTRIBUTE3,
818 LINE_ATTRIBUTE4,
819 LINE_ATTRIBUTE5,
820 LINE_ATTRIBUTE6,
821 LINE_ATTRIBUTE7,
822 LINE_ATTRIBUTE8,
823 LINE_ATTRIBUTE9,
824 LINE_ATTRIBUTE10,
825 LINE_ATTRIBUTE11,
826 LINE_ATTRIBUTE12,
827 LINE_ATTRIBUTE13,
828 LINE_ATTRIBUTE14,
829 LINE_ATTRIBUTE15,
830 NEED_BY_DATE,
831 SUGGESTED_VENDOR_CONTACT,
832 SUGGESTED_VENDOR_PHONE,
833 SUGGESTED_VENDOR_ITEM_NUM,
834 SUPPLIER_ITEM_NUM,
835 ORG_ID,
836 DELIVER_TO_LOCATION,
837 CUSTOM_DEFAULTED,
838 CART_LINE_NUMBER
839 )
840 VALUES (
841 l_cart_line_id,
842 sysdate,
843 v_cart_header_rec.created_by,
844 sysdate,
845 v_cart_header_rec.created_by,
846 v_cart_id,
847 v_special_order_rec.item_description,
848 to_number(v_special_order_rec.qty_v),
849 to_number(v_special_order_rec.unit_price),
850 to_number(v_special_order_rec.category_id),
851 v_special_order_rec.unit_of_measurement,
852 v_line_type_id,
853 v_cart_header_rec.destination_organization_id,
854 v_cart_header_rec.deliver_to_location_id,
855 v_special_order_rec.suggested_vendor_name,
856 v_special_order_rec.suggested_vendor_site,
857 v_special_order_rec.line_attribute_1,
858 v_special_order_rec.line_attribute_2,
859 v_special_order_rec.line_attribute_3,
860 v_special_order_rec.line_attribute_4,
861 v_special_order_rec.line_attribute_5,
862 v_special_order_rec.line_attribute_6,
863 v_special_order_rec.line_attribute_7,
864 v_special_order_rec.line_attribute_8,
865 v_special_order_rec.line_attribute_9,
866 v_special_order_rec.line_attribute_10,
867 v_special_order_rec.line_attribute_11,
868 v_special_order_rec.line_attribute_12,
869 v_special_order_rec.line_attribute_13,
870 v_special_order_rec.line_attribute_14,
871 v_special_order_rec.line_attribute_15,
872 v_cart_header_rec.need_by_date,
873 v_special_order_rec.suggested_vendor_contact,
874 v_special_order_rec.suggested_vendor_phone,
875 v_special_order_rec.suggested_vendor_item_num,
876 v_special_order_rec.suggested_vendor_item_num,
877 v_cart_header_rec.org_id,
878 v_cart_header_rec.deliver_to_location,
879 'N',
880 v_cart_line_number
881 );
882
883 -- Get the default accounts and update distributions
884 icx_req_acct2.get_default_account(v_cart_id,
885 l_cart_line_id,
886 v_cart_header_rec.deliver_to_requestor_id,
887 v_cart_header_rec.org_id,
888 l_account_id,
889 l_account_num);
890
891 /* Call custom default and validation for the line */
892 icx_req_custom.reqs_default_lines('NO',v_cart_id);
893
894 COMMIT;
895
896 /* get the order total; do this after custom defaults as it clould
897 modify the price or quantity */
898 SELECT SUM(quantity * unit_price) INTO l_order_total
899 FROM icx_shopping_cart_lines
900 WHERE cart_id = v_cart_id;
901
902 icx_req_navigation.get_currency(v_cart_header_rec.destination_organization_id,
903 l_currency, l_precision, l_fmt_mask);
904
905 /* Build the new order total message */
906 FND_MESSAGE.SET_NAME('ICX','ICX_ITEM_ADD_TOTAL');
907 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE', l_currency);
908 v_order_total := to_char(to_number(l_order_total), fnd_currency.get_format_mask(l_currency, 30));
909 FND_MESSAGE.SET_TOKEN('REQUISITION_TOTAL', v_order_total);
910 l_order_total_message := FND_MESSAGE.GET;
911
912 EXCEPTION
913
914 WHEN OTHERS THEN
915 -- htp.p('Error in insert line' || substr(SQLERRM, 1, 512));
916 icx_util.add_error(substr(SQLERRM, 12, 512));
917 icx_util.error_page_print;
918
919 END insert_order_to_cart_line;
920
921
922 -- to remove later the following procedure
923 ------------------------------------------------------------------------
924 procedure chk_vendor_on(v_items_table IN ak_query_pkg.items_table_type,
925 v_on OUT varchar2) is
926 ------------------------------------------------------------------------
927
928 v_vendor_on_flag varchar2(1);
929 begin
930
931 v_on := 'N';
932 v_vendor_on_flag := 'N';
933 for i in v_items_table.first .. v_items_table.last loop
934 if (v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_NAME' or
935 v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_CONTACT' or
936 v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_PHONE' or
937 v_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_SITE') AND
938 v_items_table(i).node_display_flag = 'Y' AND
939 v_items_table(i).update_flag = 'Y' AND
940 v_items_table(i).secured_column <> 'T' AND
941 v_items_table(i).item_style <> 'HIDDEN' then
942
943 v_vendor_on_flag := 'Y';
944 exit;
945 end if;
946 end loop;
947
948 v_on := v_vendor_on_flag;
949 end chk_vendor_on;
950 END icx_req_special_ord;