[Home] [Help]
PACKAGE BODY: APPS.ICX_REQ_COPY_REQ
Source
1 PACKAGE BODY icx_req_copy_req AS
2 /* $Header: ICXRQCPB.pls 115.3 99/07/17 03:23:02 porting shi $ */
3
4 ------------------------------------------------------
5 PROCEDURE welcome_page IS
6
7 v_lang varchar2(5);
8 c_title varchar2(80);
9 c_prompts icx_util.g_prompts_table;
10 v_dcdName varchar2(1000);
11 v_message_caption varchar2(200);
12 v_message_text varchar2(1000);
13 v_0_encrypt varchar2(100);
14
15 BEGIN
16
17 -- Check if session is valid
18 IF (icx_sec.validatesession('ICX_REQS_COPY_REQ')) THEN
19
20 -- get dcd name
21 v_dcdName := owa_util.get_cgi_env('SCRIPT_NAME');
22
23 -- set lang code
24 v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
25
26 -- encrypt 0
27 v_0_encrypt := icx_call.encrypt2('0');
28
29 -- Create the Intro Page
30
31
32 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_INTRO_TITLE');
33 c_title := FND_MESSAGE.GET;
34
35 htp.htmlOpen;
36 htp.title(c_title);
37 htp.bodyOpen;
38
39 htp.headOpen;
40
41 icx_util.copyright;
42
43 js.scriptOpen;
44
45 htp.p('function help_window(){
46 help_win = window.open(''/OA_DOC/' || v_lang || '/awe' || '/icxhlprq.htm'', "help_win","resizable=yes,scrollbars=yes,toolbar=yes,width=450,height=250");
47 help_win = window.open(''/OA_DOC/' || v_lang || '/awe' || '/icxhlprq.htm'', "help_win","resizable=yes,scrollbars=yes,toolbar=yes,width=450,height=250")
48 }
49 ');
50
51 js.scriptClose;
52 htp.headClose;
53
54 -- TOOLBAR
55 icx_admin_sig.toolbar(language_code => v_lang);
56
57 htp.p('<table border=0 cellpadding=0><tr>');
58 htp.p('<td width=2000 bgcolor=#0000ff height=4><img src=/OA_MEDIA/'||
59 v_lang || '/FNDDBPX6.gif height=1 width=1></td></tr></table>');
60
61 htp.p('<table cellspacing=8 cellpadding=0 border=0>');
62 htp.p('<tr><td colspan=3>');
63
64 -- The top intro line of the page
65 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_INTRO_TXT');
66 htp.p(FND_MESSAGE.GET || '<p>');
67 htp.p('</font></td></tr><tr><td colspan=3>');
68
69 -- The First line of the intro
70 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_COPY_ITEMS_TTL');
71 v_message_caption := FND_MESSAGE.GET;
72 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_COPY_ITEMS_TXT');
73 v_message_text := FND_MESSAGE.GET;
74 htp.p('<table border=0 cellpadding=0>');
75 htp.p('<tr>');
76 htp.p('<td rowspan=2><a href=' || v_dcdName ||
77 '/ICX_REQ_COPY_REQ.find_reqs' ||
78 '>' || '<img src=/OA_MEDIA/' || v_lang ||
79 '/FNDICPY.gif border=no height=75 width=75 align=absmiddle></a></td>');
80 htp.p('<td colspan=2 height=4><img src=/OA_MEDIA/' || v_lang ||
81 '/FNDIBLBR.gif width=500 height=4></td></tr><tr>');
82 htp.p('<td width=50 align=center valign=top><font size=7 color=#0000ff>' ||
83 '<b>1</td>');
84 htp.p('<td width=1000 valign=top><b><font size=+1 color=#0000ff>' ||
85 v_message_caption || '</b></font><br>' || v_message_text ||
86 '</td></td></tr></table>');
87
88 -- Second line
89 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_CHK_ORDER_TTL');
90 v_message_caption := FND_MESSAGE.GET;
91 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_CHK_ORDER_TXT');
92 v_message_text := FND_MESSAGE.GET;
93 htp.p('</td></tr><tr>');
94 htp.p('<td rowspan=2><font size=7> </td>');
95 htp.p('<td colspan=2>');
96 htp.p('<table border=0 cellpadding=0>');
97 htp.p('<tr>');
98 htp.p('<td rowspan=2><img src=/OA_MEDIA/' || v_lang ||
99 '/FNDICKO.gif height=75 width=75 align = absmiddle></td>');
100 htp.p('<td colspan=2 height=4><img src=/OA_MEDIA/' || v_lang ||
101 '/FNDIRDBR.gif width=500 height=4></td><tr>');
102 htp.p('<td width=50 align=center valign=top><font size=7 color=#cc0000>' ||
103 '<b>2</td>');
104 htp.p('<td width=1000 valign=top><b><font size=+1 color="#CC0000">' ||
105 v_message_caption || '</b></font><br>' || v_message_text || '</td>' ||
106 '</tr></table>');
107
108 -- Third line
109 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_PLACE_ORDER_TTL');
110 v_message_caption := FND_MESSAGE.GET;
111 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_PLACE_ORDER_TXT');
112 v_message_text := FND_MESSAGE.GET;
113 htp.p('</td></tr><tr>');
114 htp.p('<td rowspan=2><font size=7> </td>');
115 htp.p('<td colspan=1>');
116 htp.p('<table border=0 cellpadding=0>');
117 htp.p('<tr>');
118 htp.p('<td rowspan=2><img src=/OA_MEDIA/' || v_lang ||
119 '/FNDIPLO.gif height=75 width=75 align = absmiddle></td>');
120 htp.p('<td colspan=2 height=4><img src=/OA_MEDIA/' || v_lang ||
121 '/FNDIGRBR.gif width=500 height=4></td><tr>');
122 htp.p('<td width=50 align=center valign=top><font size=7 color=#006666>' ||
123 '<b>3</td>');
124 htp.p('<td width=1000 valign=top><b><font size=+1 color="#006666">' ||
125 v_message_caption || '</b></font><br>' || v_message_text ||
126 '<br></td>' || '</tr></table>');
127
128 htp.p('</td></tr></table>');
129
130 htp.p('<center>');
131 htp.anchor(v_dcdName || '/ICX_REQ_COPY_REQ.find_reqs',
132 htf.img('/OA_MEDIA/' || v_lang ||
133 '/FNDICPYS.gif', cattributes => 'BORDER = NO align=absmiddle' ));
134 FND_MESSAGE.SET_NAME('ICX', 'ICX_RQS_PROCEED_TO_COPY');
135 htp.p('<FONT SIZE=+1>');
136 htp.anchor(v_dcdName || '/ICX_REQ_COPY_REQ.find_reqs', FND_MESSAGE.GET);
137 htp.p('</FONT>');
138 htp.p('</center>');
139
140 htp.bodyClose;
141 htp.htmlClose;
142
143 END IF; /* validate session */
144
145 END welcome_page;
146
147 -------------------------------------------------
148 PROCEDURE find_reqs IS
149
150 v_help_url VARCHAR2(2000) := NULL;
151 v_language_code VARCHAR2(30) := NULL;
152
153 BEGIN
154 IF icx_sec.validateSession('ICX_REQS_COPY_REQ') THEN
155
156 v_language_code := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
157 v_help_url := '/OA_DOC/' || v_language_code ||'/awe' || '/icxhlprq.htm';
158
159 -- Call the Findpage function to paint the find page. (AK flow)
160
161 icx_on_utilities.FindPage(p_flow_appl_id => '',
162 p_flow_code => '',
163 p_page_appl_id => '',
164 p_page_code => '',
165 p_region_appl_id => 178,
166 p_region_code => 'ICX_PO_REQ_HEAD_SUM_R',
167 p_goto_url => 'icx_req_copy_req.display_reqs',
168 p_lines_now => 1,
169 p_lines_url => '',
170 p_lines_next => 5,
171 p_hidden_name => '',
172 p_hidden_value => '',
173 p_help_url => v_help_url,
174 p_new_url => '');
175
176
177
178 END IF; /* validate session */
179
180 END find_reqs;
181
182
183 ----------------------------------------------------
184 PROCEDURE display_reqs (a_1 IN VARCHAR2 DEFAULT NULL,
185 c_1 IN VARCHAR2 DEFAULT NULL,
186 i_1 IN VARCHAR2 DEFAULT NULL,
187 a_2 IN VARCHAR2 DEFAULT NULL,
188 c_2 IN VARCHAR2 DEFAULT NULL,
189 i_2 IN VARCHAR2 DEFAULT NULL,
190 a_3 IN VARCHAR2 DEFAULT NULL,
191 c_3 IN VARCHAR2 DEFAULT NULL,
192 i_3 IN VARCHAR2 DEFAULT NULL,
193 a_4 IN VARCHAR2 DEFAULT NULL,
194 c_4 IN VARCHAR2 DEFAULT NULL,
195 i_4 IN VARCHAR2 DEFAULT NULL,
196 a_5 IN VARCHAR2 DEFAULT NULL,
197 c_5 IN VARCHAR2 DEFAULT NULL,
198 i_5 IN VARCHAR2 DEFAULT NULL,
199 m IN VARCHAR2 DEFAULT NULL,
200 o IN VARCHAR2 DEFAULT 'AND',
201 p_start_row IN NUMBER DEFAULT 1,
202 p_end_row IN NUMBER DEFAULT NULL,
203 p_where IN NUMBER DEFAULT NULL) IS
204
205 v_req_header_id NUMBER := NULL;
206 v_dcd_name VARCHAR2(200) := NULL;
207 v_where_clause VARCHAR2(2000) := NULL;
208 v_total_rows NUMBER := 0;
209 v_query_size NUMBER := 0;
210 v_help_url VARCHAR2(2000) := NULL;
211 v_language_code VARCHAR2(30) := NULL;
212 v_session_id NUMBER := NULL;
213 v_end_row NUMBER := 0;
214 v_encrypted_where NUMBER := NULL;
215 v_param VARCHAR2(240) := NULL;
216 y_table icx_util.char240_table;
217 v_row_id VARCHAR2(25) := NULL;
218
219 l_message VARCHAR2(2000) := NULL;
220 l_err_num NUMBER := 0;
221 l_err_mesg VARCHAR2(240) := NULL;
222 l_web_user_date_format VARCHAR2(240) := NULL;
223
224 /* New vars to use the Bind vars logic **/
225 l_where_binds ak_query_pkg.bind_tab;
226 l_where_clause varchar2(2000);
227
228
229 BEGIN
230
231 IF icx_sec.validateSession('ICX_REQS_COPY_REQ') THEN
232
233 v_dcd_name := owa_util.get_cgi_env('SCRIPT_NAME');
234 v_language_code := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
235 v_help_url := '/OA_DOC/' || v_language_code || '/awe' || '/icxhlprq.htm';
236 v_session_id := to_number(icx_sec.getID(icx_sec.PV_SESSION_ID));
237
238 select rowidtochar(ROWID)
239 into v_row_id
240 from AK_FLOW_REGION_RELATIONS
241 where FROM_REGION_CODE = 'ICX_PO_REQ_HEAD_SUM_R'
242 and FROM_REGION_APPL_ID = 178
243 and FROM_PAGE_CODE = 'ICX_REQS_HEADER'
244 and FROM_PAGE_APPL_ID = 178
245 and TO_PAGE_CODE = 'ICX_REQS_DETAILS'
246 and TO_PAGE_APPL_ID = 178
247 and FLOW_CODE = 'ICX_COPY_REQS'
248 and FLOW_APPLICATION_ID = 178;
249
250 if p_where IS NOT NULL THEN
251 v_where_clause := icx_call.decrypt2(p_where);
252 ELSE
253 -- generate the where clause
254 v_where_clause := icx_on_utilities.whereSegment(a_1, c_1, i_1,
255 a_2, c_2, i_2,
256 a_3, c_3, i_3,
257 a_4, c_4, i_4,
258 a_5, c_5, i_5,
259 m,o);
260
261
262
263 END IF; /* IF p_where */
264
265
266
267 v_encrypted_where := icx_call.encrypt2(v_where_clause);
268
269 -- get number of rows to display
270 SELECT query_set
271 INTO v_query_size
272 FROM icx_parameters;
273
274 -- Find the end rows for display
275 IF p_end_row IS NULL THEN
276 v_end_row := v_query_size;
277 ELSE
278 v_end_row := p_end_row;
279 END IF;
280
281 /* added to take care of Bind vars Bug **/
282
283 icx_on_utilities.unpack_whereSegment(v_where_clause, l_where_clause, l_where_binds);
284
285 ak_query_pkg.exec_query(P_PARENT_REGION_APPL_ID => 178,
286 P_PARENT_REGION_CODE => 'ICX_PO_REQ_HEAD_SUM_R',
287 P_RESPONSIBILITY_ID => icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
288 P_USER_ID => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
289 P_WHERE_CLAUSE => l_where_clause,
290 P_RETURN_PARENTS => 'T',
291 P_RETURN_CHILDREN => 'F',
292 P_WHERE_BINDS => l_where_binds );
293
294 -- test - dump the plsql tables for debug purpose only!
295 -- icx_on_utilities2.printPLSQLtables;
296
297 -- get the total number of rows
298 v_total_rows := ak_query_pkg.g_results_table.count;
299
300 IF v_end_row > v_total_rows THEN
301 v_end_row := v_total_rows;
302 END IF;
303
304 IF v_total_rows = 0 THEN
305 htp.htmlOpen;
306 htp.headOpen;
307 icx_util.copyright;
308 js.scriptOpen;
309 icx_admin_sig.help_win_script(v_help_url, v_language_code);
310 js.scriptClose;
311 htp.title(ak_query_pkg.g_regions_table(0).name);
312 htp.headClose;
313
314 icx_admin_sig.toolbar(language_code => v_language_code,
315 disp_find => 'icx_req_copy_req.find_reqs');
316
317 fnd_message.set_name('ICX','ICX_NO_RECORDS_FOUND');
318 fnd_message.set_token('NAME_OF_REGION_TOKEN',ak_query_pkg.g_regions_table(0).name);
319 htp.br;
320 htp.tableOpen('BORDER=0');
321 htp.tableRowOpen;
322 htp.tableData(cvalue => '<B><FONT size=+1>'||fnd_message.get||'</FONT></B>',cattributes => 'VALIGN="MIDDLE"');
323 htp.tableClose;
324 htp.br;
325 icx_admin_sig.footer;
326
327 ELSE
328 htp.htmlOpen;
329 htp.headOpen;
330 icx_util.copyright;
331
332 js.scriptOpen;
333 icx_admin_sig.help_win_script(v_help_url, v_language_code);
334 js.scriptClose;
335
336 htp.title(ak_query_pkg.g_regions_table(0).name);
337 htp.headClose;
338
339 icx_admin_sig.toolbar(language_code => v_language_code,
340 disp_find => 'icx_req_copy_req.find_reqs');
341
342
343 htp.p('<FORM ACTION="' || v_dcd_name || '/icx_req_copy_req.copy_req" NAME="DISPLAY_COPY_REQS" METHOD="POST">');
344
345
346 -- Display Heading
347 htp.tableOpen('BORDER=0');
348 htp.tableRowOpen;
349 htp.tableData(cvalue => '<B><FONT size=+2>'
350 || ak_query_pkg.g_regions_table(0).name
351 || '</FONT></B>',cattributes => 'VALIGN="MIDDLE"');
352 htp.tableRowClose;
353 htp.tableClose;
354 htp.br;
355
356 IF(v_total_rows > 5)
357 THEN
358 icx_on_utilities2.displaySetIcons(v_language_code,
359 'icx_req_copy_req.display_reqs',
360 p_start_row,
361 v_end_row,
362 v_encrypted_where,
363 v_query_size,
364 v_total_rows);
365 htp.br;
366 END IF; /* (v_total_rows > 5) */
367
368 -- Print table header
369 htp.tableOpen('BORDER=2','','','', 'CELLPADDING=2');
370 htp.p('<TR BGColor="#'||icx_util.get_color('TABLE_HEADER')||'">');
371
372 FOR i IN ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST LOOP
373 IF ak_query_pkg.g_items_table(i).secured_column = 'F'
374 AND ak_query_pkg.g_items_table(i).node_display_flag = 'Y' THEN
375
376 htp.p('<TH><FONT COLOR=''#' || icx_util.get_color('TABLE_HEADER_TEXT') ||'''>' || ak_query_pkg.g_items_table(i).attribute_label_long);
377 htp.p('</FONT></TH>');
378
379 END IF; /* if ak_query_pkg.... */
380
381 END LOOP;
382 htp.tableRowClose;
383 htp.tableData(''); -- ?
384
385 -- FOR r IN ak_query_pkg.g_results_table.FIRST..ak_query_pkg.g_results_table.LAST LOOP
386 FOR r IN p_start_row -1..v_end_row - 1 LOOP
387 icx_util.transfer_Row_To_Column(ak_query_pkg.g_results_table(r),y_table);
388
389 htp.tableRowOpen;
390
391 FOR i IN ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST LOOP
392 IF (ak_query_pkg.g_items_table(i).secured_column = 'F'
393 AND ak_query_pkg.g_items_table(i).node_display_flag = 'Y')
394 OR (ak_query_pkg.g_items_table(i).item_style = 'HIDDEN'
395 AND ak_query_pkg.g_items_table(i).attribute_code = 'ICX_REQ_HEADER_ID') THEN
396 IF ak_query_pkg.g_items_table(i).attribute_code = 'ICX_REQ_HEADER_ID' THEN
397 v_req_header_id := y_table(ak_query_pkg.g_items_table(i).value_id);
398
399 -- Build the parameter to jump into AK flow from here
400 v_param := 'D*****1****' || v_row_id || '*ICX_PO_REQ_HEADER_PK*' || v_req_header_id || '**]';
401 ELSIF ak_query_pkg.g_items_table(i).attribute_code = 'ICX_REQ_NUM' THEN
402 htp.tableData(htf.anchor('oracleON.IC?Y='|| icx_call.encrypt2(v_param), y_table(ak_query_pkg.g_items_table(i).value_id),'','onMouseOver="return true"'));
403
404 ELSIF ak_query_pkg.g_items_table(i).attribute_code = 'ICX_COPY' THEN
405
406 htp.tableData(htf.anchor('icx_req_copy_req.copy_req?v_req_header_id='|| icx_call.encrypt2(v_req_header_id), htf.img('/OA_MEDIA/' || v_language_code || '/FNDISPLT.gif', 'CENTER', '', '', 'BORDER=NO WIDTH=20 HEIGHT=20')));
407
408 ELSE
409 IF (y_table(ak_query_pkg.g_items_table(i).value_id)) IS NULL THEN
410 htp.tableData(' ');
411 ELSE
412 htp.tableData(y_table(ak_query_pkg.g_items_table(i).value_id));
413 END IF;
414 END IF; /* ... ICX_REQ_NUM */
415 END IF; /* secured_column = 'F'... */
416
417 END LOOP;
418
419 htp.tableRowClose;
420
421 END LOOP;
422
423 htp.tableClose;
424
425 htp.br;
426
427 IF(v_total_rows > 5)
428 THEN
429 icx_on_utilities2.displaySetIcons(v_language_code,
430 'icx_req_copy_req.display_reqs',
431 p_start_row,
432 v_end_row,
433 v_encrypted_where,
434 v_query_size,
435 v_total_rows);
436 END IF; /* (v_total_rows > 5) */
437
438 htp.formClose;
439 icx_admin_sig.footer;
440
441 END IF; /* v_total_rows = 0 */
442
443 END IF; /* validate session */
444
445 EXCEPTION
446 WHEN OTHERS THEN
447 -- htp.p('Error in display reqs ' || substr(SQLERRM, 1, 512));
448 -- icx_util.add_error(substr(SQLERRM, 12, 512));
449 -- icx_util.error_page_print;
450
451 l_err_num := SQLCODE;
452 l_message := SQLERRM;
453 select substr(l_message,12,512) into l_err_mesg from dual;
454 if (abs(l_err_num) between 1800 and 1899)
455 then
456 fnd_message.set_name('ICX','ICX_USE_DATE_FORMAT');
457 l_web_user_date_format := icx_sec.getID(icx_sec.pv_date_format);
458 fnd_message.set_token('FORMAT_MASK_TOKEN',nvl(l_web_user_date_format,'DD-MON-YYYY'));
459 l_message := l_err_mesg||'<br>'||fnd_message.get;
460 icx_util.add_error(l_message) ;
461 icx_admin_sig.error_screen(l_err_mesg);
462 else
463 icx_util.add_error(l_err_mesg);
464 icx_admin_sig.error_screen(l_err_mesg);
465 end if;
466
467 END display_reqs;
468
469 ------------------------------------------------------
470 PROCEDURE copy_req (v_req_header_id IN NUMBER) IS
471
472 l_req_header_id NUMBER := NULL;
473 v_org_id NUMBER := NULL;
474 v_shopper_id NUMBER := NULL;
475 v_int_contact_name VARCHAR2(250) := NULL;
476 v_int_contact_id NUMBER := NULL;
477 v_destination_org_id NUMBER := NULL;
478 v_org_code VARCHAR2(30) := NULL;
479 v_deliver_to_loc_id NUMBER := NULL;
480 v_deliver_to_location VARCHAR2(500) := NULL;
481 v_dest_type_code VARCHAR2(30) := NULL;
482 v_note_to_buyer VARCHAR2(240) := NULL;
483 v_need_by_date DATE := NULL;
484 v_requisition_num NUMBER := NULL;
485 v_cart_id NUMBER := NULL;
486 v_distribution_id NUMBER := NULL;
487 v_cart_line_id NUMBER := NULL;
488 v_cart_line_number NUMBER := NULL;
489 v_dist_num NUMBER := NULL;
490
491 v_dcd_name VARCHAR2(1000) := NULL;
492 v_language VARCHAR2(30) := NULL;
493
494 --add by alex
495 pk1 VARCHAR2(30);
496 pk2 VARCHAR2(30);
497 --
498
499 CURSOR getDate(increment NUMBER) IS
500 SELECT SYSDATE + increment
501 FROM SYS.DUAL;
502
503 cursor reqlines(reqheader number) IS
504 SELECT requisition_line_id
505 FROM po_requisition_lines
506 WHERE requisition_header_id = reqheader
507 ORDER BY line_num;
508
509 cursor reqdistributions(l_cart_id number, l_cart_line_id number) IS
510 SELECT distribution_id, charge_account_id
511 FROM icx_cart_line_distributions
512 WHERE cart_id = l_cart_id
513 AND cart_line_id = l_cart_line_id;
514
515
516 BEGIN
517 IF (icx_sec.validatesession('ICX_REQS_COPY_REQ')) THEN
518
519 -- Get dcd path
520 v_dcd_name := owa_util.get_cgi_env('SCRIPT_NAME');
521 -- Get language code
522 v_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
523
524 l_req_header_id := icx_call.decrypt2(v_req_header_id);
525
526 -- Get organization id
527 v_org_id := icx_sec.getId(icx_sec.PV_ORG_ID);
528 -- get shopper id
529 v_shopper_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
530 -- get employee_id ( Internal Contect ID )
531 v_int_contact_id := icx_sec.getID(icx_sec.PV_INT_CONTACT_ID);
532
533 -- Get deliver to location etc.,
534 ICX_REQ_NAVIGATION.shopper_info(v_int_contact_id, v_int_contact_name,
535 v_deliver_to_loc_id, v_deliver_to_location,
536 v_destination_org_id, v_org_code);
537
538 OPEN getDate (nvl(icx_sec.getID(icx_sec.PV_USER_REQ_DAYS_NEEDED_BY), 0));
539 FETCH getDate into v_need_by_date;
540 CLOSE getDate;
541
542 SELECT note_to_agent
543 INTO v_note_to_buyer
544 FROM po_requisition_lines
545 WHERE requisition_header_id = l_req_header_id
546 AND rownum = 1;
547
548
549 -- Get a new Requisition number
550 SELECT to_char(current_max_unique_identifier + 1)
551 INTO v_requisition_num
552 FROM po_unique_identifier_control
553 WHERE table_name = 'PO_REQUISITION_HEADERS'
554 FOR UPDATE OF current_max_unique_identifier;
555
556 UPDATE po_unique_identifier_control
557 SET current_max_unique_identifier = current_max_unique_identifier + 1
558 WHERE table_name = 'PO_REQUISITION_HEADERS';
559
560 COMMIT; /* avoid locking problem (?!) */
561
562 --changed by alex for attachment
563 -- SELECT icx_shopping_carts_s.NEXTVAL
564 -- INTO v_cart_id
565 -- FROM SYS.DUAL;
566 --new code:
567 SELECT PO_REQUISITION_HEADERS_S.NEXTVAL
568 INTO v_cart_id
569 FROM SYS.DUAL;
570
571 -- The reserve_po_num column is now updated to NULL from the earlier
572 -- value of attribute7.
573 INSERT INTO icx_shopping_carts (
574 cart_id,
575 last_update_date,
576 last_updated_by,
577 creation_date,
578 created_by,
579 shopper_id,
580 deliver_to_requestor_id,
581 need_by_date,
582 destination_type_code,
583 destination_organization_id,
584 deliver_to_location_id,
585 note_to_approver,
586 note_to_buyer,
587 saved_flag,
588 req_number_segment1,
589 approver_id,
590 approver_name,
591 header_description,
592 header_attribute_category,
593 reserved_po_num,
594 header_attribute1,
595 header_attribute2,
596 header_attribute3,
597 header_attribute4,
598 header_attribute5,
599 header_attribute6,
600 header_attribute7,
601 header_attribute8,
602 header_attribute9,
603 header_attribute10,
604 header_attribute11,
605 header_attribute12,
606 header_attribute13,
607 header_attribute14,
608 header_attribute15,
609 deliver_to_location,
610 deliver_to_requestor,
611 org_id
612 )
613 SELECT
614 v_cart_id,
615 sysdate,
616 v_shopper_id,
617 sysdate,
618 v_shopper_id,
619 v_shopper_id,
620 v_int_contact_id,
621 v_need_by_date,
622 'EXPENSE',
623 v_destination_org_id,
624 v_deliver_to_loc_id,
625 note_to_authorizer,
626 v_note_to_buyer,
627 1,
628 v_requisition_num,
629 NULL,
630 NULL,
631 description,
632 attribute_category,
633 NULL,
634 attribute1,
635 attribute2,
636 attribute3,
637 attribute4,
638 attribute5,
639 attribute6,
640 attribute7,
641 attribute8,
642 attribute9,
643 attribute10,
644 attribute11,
645 attribute12,
646 attribute13,
647 attribute14,
648 attribute15,
649 v_deliver_to_location,
650 v_int_contact_name,
651 v_org_id
652 FROM po_requisition_headers
653 WHERE requisition_header_id = l_req_header_id;
654
655 --add by alex
656 --copy attachment for the header
657 fnd_attached_documents2_pkg.copy_attachments('REQ_HEADERS',
658 l_req_header_id,
659 '',
660 '',
661 '',
662 '',
663 'REQ_HEADERS',
664 v_cart_id,
665 '',
666 '',
667 '',
668 '',
669 '',
670 '',
671 '',
672 '',
673 '');
674
675 -- Create cart distributions
676 SELECT icx_cart_distributions_s.nextval
677 INTO v_distribution_id
678 FROM SYS.DUAL;
679
680 INSERT INTO icx_cart_distributions (
681 cart_id,
682 distribution_id,
683 last_updated_by,
684 last_update_date,
685 last_update_login,
686 creation_date,
687 created_by,
688 org_id)
689 VALUES (
690 v_cart_id,
691 v_distribution_id,
692 v_shopper_id,
693 sysdate,
694 v_shopper_id,
695 sysdate,
696 v_shopper_id,
697 v_org_id);
698
699
700 v_cart_line_number := 0;
701
702 FOR prec IN reqlines(l_req_header_id) LOOP
703
704 v_cart_line_number := v_cart_line_number + 1;
705
706 --changed by alex for attachment
707 -- SELECT icx_shopping_cart_lines_s.NEXTVAL
708 -- INTO v_cart_line_id
709 -- FROM DUAL;
710 --new code:
711 SELECT PO_REQUISITION_LINES_S.NEXTVAL
712 INTO v_cart_line_id
713 FROM DUAL;
714
715
716 INSERT INTO icx_shopping_cart_lines (
717 cart_line_id,
718 cart_line_number,
719 last_update_date,
720 last_updated_by,
721 creation_date,
722 created_by,
723 cart_id,
724 item_id,
725 item_revision,
726 unit_of_measure,
727 quantity,
728 unit_price,
729 suggested_vendor_item_num,
730 category_id,
731 line_type_id,
732 item_description,
733 suggested_vendor_name,
734 suggested_vendor_site,
735 destination_organization_id,
736 deliver_to_location_id,
737 autosource_doc_header_id,
738 autosource_doc_line_num,
739 line_id,
740 line_attribute_category,
741 line_attribute1,
742 line_attribute2,
743 line_attribute3,
744 line_attribute4,
745 line_attribute5,
746 line_attribute6,
747 line_attribute7,
748 line_attribute8,
749 line_attribute9,
750 line_attribute10,
751 line_attribute11,
752 line_attribute12,
753 line_attribute13,
754 line_attribute14,
755 line_attribute15,
756 need_by_date,
757 custom_defaulted,
758 deliver_to_location,
759 org_id
760 ) select
761 v_cart_line_id,
762 v_cart_line_number,
763 sysdate,
764 v_shopper_id,
765 sysdate,
766 v_shopper_id,
767 v_cart_id,
768 rl.item_id,
769 rl.item_revision,
770 rl.unit_meas_lookup_code,
771 rl.quantity,
772 rl.unit_price,
773 rl.suggested_vendor_product_code,
774 rl.category_id,
775 rl.line_type_id,
776 rl.item_description,
777 rl.suggested_vendor_name,
778 rl.suggested_vendor_location,
779 v_destination_org_id,
780 v_deliver_to_loc_id,
781 rl.blanket_po_header_id,
782 rl.blanket_po_line_num,
783 -999,
784 rl.attribute_category,
785 rl.attribute1,
786 rl.attribute2,
787 rl.attribute3,
788 rl.attribute4,
789 rl.attribute5,
790 rl.attribute6,
791 rl.attribute7,
792 rl.attribute8,
793 rl.attribute9,
794 rl.attribute10,
795 rl.attribute11,
796 rl.attribute12,
797 rl.attribute13,
798 rl.attribute14,
799 rl.attribute15,
800 v_need_by_date,
801 'N',
802 v_deliver_to_location,
803 v_org_id
804 FROM po_requisition_lines rl
805 WHERE rl.requisition_header_id = l_req_header_id
806 AND rl.requisition_line_id = prec.requisition_line_id;
807
808 --add by alex
809 --copy attachment for the header
810 fnd_attached_documents2_pkg.copy_attachments('REQ_LINES',
811 prec.requisition_line_id,
812 '',
813 '',
814 '',
815 '',
816 'REQ_LINES',
817 v_cart_line_id,
818 '',
819 '',
820 '',
821 '',
822 '',
823 '',
824 '',
825 '',
826 '');
827
828
829 -- Create multiple line distributions
830 INSERT INTO icx_cart_line_distributions
831 (cart_line_id,
832 cart_id,
833 distribution_id,
834 last_updated_by,
835 last_update_date,
836 last_update_login,
837 creation_date,
838 created_by,
839 charge_account_id,
840 accrual_account_id,
841 variance_account_id,
842 budget_account_id,
843 org_id,
844 allocation_type,
845 allocation_value,
846 allocation_quantity)
847 SELECT v_cart_line_id,
848 v_cart_id,
849 icx_cart_line_distributions_s.nextval,
850 v_shopper_id,
851 sysdate,
852 v_shopper_id,
853 sysdate,
854 v_shopper_id,
855 rd.code_combination_id,
856 rd.accrual_account_id,
857 rd.variance_account_id,
858 rd.budget_account_id,
859 v_org_id,
860 nvl(rd.allocation_type, 'PERCENT'),
861 nvl(rd.allocation_value, round(rd.req_line_quantity / rl.quantity * 100, 5)),
862 rd.req_line_quantity
863 FROM po_req_distributions rd,
864 po_requisition_lines rl
865 WHERE rd.requisition_line_id = prec.requisition_line_id
866 AND rl.requisition_header_id = l_req_header_id
867 AND rl.requisition_line_id = rd.requisition_line_id;
868
869
870 -- Update the distribution num column in distributions table.
871 -- This is required as the view of ICX_SHOPPING_CART_LINES_V has
872 -- join condition as DISTRIBUTION_NUM = 1.
873 -- The reqs from sources other than web reqs may not have populated
874 -- the distribuion number.
875
876 v_dist_num := 1;
877
878 FOR distribution IN reqdistributions(v_cart_id, v_cart_line_id) LOOP
879
880 UPDATE icx_cart_line_distributions
881 SET distribution_num = v_dist_num
882 WHERE cart_id = v_cart_id
883 AND cart_line_id = v_cart_line_id
884 AND distribution_id = distribution.distribution_id;
885
886 -- Update the invidual segments from the account id.
887 -- This need to done because the invidual segments are not
888 -- available from po_req_distributions table.
889 icx_req_acct2.update_account_by_id( v_cart_id => v_cart_id,
890 v_cart_line_id => v_cart_line_id,
891 v_oo_id => v_org_id,
892 v_distribution_id => distribution.distribution_id,
893 v_line_number => v_dist_num);
894
895 v_dist_num := v_dist_num + 1;
896
897 END LOOP; /* FOR distribution */
898
899 END LOOP; /* FOR prec ... */
900
901 UPDATE icx_shopping_cart_lines b
902 SET item_number = ( SELECT a.concatenated_segments
903 FROM mtl_system_items_kfv a
904 WHERE a.inventory_item_id = b.item_id
905 AND a.organization_id = b.destination_organization_id
906 AND b.cart_id = v_cart_id
907 AND b.item_id IS NOT NULL)
908 WHERE cart_id = v_cart_id;
909
910
911 /* Call custom defaults */
912 icx_req_custom.reqs_default_lines('NO', v_cart_id);
913
914 COMMIT; /* release locks */
915
916 /* display the req in my order page */
917 icx_req_navigation.ic_parent(icx_call.encrypt2(to_char(v_cart_id)));
918
919 END IF; /* validate session */
920
921
922 EXCEPTION
923 WHEN OTHERS THEN
924 -- htp.p('Error in copy req ' || substr(SQLERRM, 1, 512));
925 icx_util.add_error(substr(SQLERRM, 12, 512));
926 icx_util.error_page_print;
927
928 END copy_req;
929
930 END icx_req_copy_req;