[Home] [Help]
PACKAGE BODY: APPS.POS_ASN
Source
1 PACKAGE BODY pos_asn AS
2 /* $Header: POSASNEB.pls 115.16 2001/10/19 16:19:10 pkm ship $ */
3
4 FUNCTION set_session_info RETURN BOOLEAN is
5 BEGIN
6
7 IF NOT icx_sec.validatesession THEN
8 RETURN FALSE;
9 END IF;
10
11 l_org_id := icx_sec.getID(icx_sec.PV_ORG_ID);
12 l_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
13 l_script_name := owa_util.get_cgi_env('SCRIPT_NAME');
14 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
15 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
16 l_responsibility_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
17 l_date_format := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
18
19 RETURN TRUE;
20
21 END set_session_info;
22
23 PROCEDURE button(src1 IN varchar2,
24 txt1 IN varchar2,
25 src2 IN varchar2,
26 txt2 IN varchar2) IS
27 BEGIN
28
29 htp.p('
30 <table cellpadding=0 cellspacing=0 border=0>
31 <tr>
32 <td rowspan=5><img src=/OA_MEDIA/FNDBRNDL.gif ></td>
33 <td bgcolor=#333333><img src=/OA_MEDIA/FNDPX3.gif ></td>
34 <td rowspan=5><img src=/OA_MEDIA/FNDBSQRR.gif ></td>
35 <td width=15 rowspan=5></td>
36 <td rowspan=5><img src=/OA_MEDIA/FNDBSQRL.gif ></td>
37 <td bgcolor=#333333><img src=/OA_MEDIA/FNDPX3.gif ></td>
38 <td rowspan=5><img src=/OA_MEDIA/FNDBRNDR.gif ></td>
39 </tr>
40 <tr>
41 <td bgcolor=#ffffff><img src=/OA_MEDIA/FNDPX6.gif></td>
42 <td bgcolor=#ffffff><img src=/OA_MEDIA/FNDPX6.gif></td>
43 </tr>
44 <tr>
45 <td bgcolor=#cccccc height=20 nowrap><a
46 href="' || src1 || '"><font class=button>'|| txt1 || '</font></a></td>
47 <td bgcolor=#cccccc height=20 nowrap><a
48 href="' || src2 || '"><font class=button>'|| txt2 || '</font></a></td>
49 </tr>
50 <tr>
51 <td bgcolor=#666666><img src=/OA_MEDIA/FNDPX3.gif></td>
52 <td bgcolor=#666666><img src=/OA_MEDIA/FNDPX3.gif></td>
53 </tr>
54 <tr>
55 <td bgcolor=#333333><img src=/OA_MEDIA/FNDPX3.gif></td>
56 <td bgcolor=#333333><img src=/OA_MEDIA/FNDPX3.gif></td>
57 </tr>
58 </table>
59 ');
60
61 END button;
62
63 FUNCTION po_num(seg1 in varchar2) RETURN VARCHAR2 IS
64 p_rowid VARCHAR2(2000);
65 l_param VARCHAR2(2000);
66 Y VARCHAR2(2000);
67 header_id NUMBER;
68 BEGIN
69
70 fnd_client_info.set_org_context(l_org_id);
71
72 select rowidtochar(ROWID)
73 into p_rowid
74 from AK_FLOW_REGION_RELATIONS
75 where FROM_REGION_CODE = 'ICX_PO_HEADERS_D'
76 and FROM_REGION_APPL_ID = 178
77 and FROM_PAGE_CODE = 'ICX_PO_HEADERS_D'
78 and FROM_PAGE_APPL_ID = 178
79 and TO_PAGE_CODE = 'ICX_PO_HEADERS_DTL_D'
80 and TO_PAGE_APPL_ID = 178
81 and FLOW_CODE = 'ICX_INQUIRIES'
82 and FLOW_APPLICATION_ID = 178;
83
84 select po_header_id
85 into header_id
86 from po_headers
87 where TYPE_LOOKUP_CODE IN ('STANDARD','PLANNED', 'BLANKET','CONTRACT') and
88 segment1 = decode(instrb(seg1,'-'), 0, seg1, substr(seg1, 1, (instrb(seg1,'-')-1)));
89
90 l_param := icx_on_utilities.buildOracleONstring(p_rowid => p_rowid,
91 p_primary_key => 'ICX_PO_SUPPLIER_ORDERS_PK',
92 p1 => to_char(header_id));
93
94 Y := icx_call.encrypt2(l_param,l_session_id);
95
96 return l_script_name || '/OracleOn.IC?Y=' || Y;
97
98 END po_num;
99
100 FUNCTION item_reqd(l_index in number) RETURN VARCHAR2 IS
101 BEGIN
102 if ak_query_pkg.g_items_table(l_index).required_flag = 'Y' then
103 return '<IMG src=/OA_MEDIA/FNDIREQD.gif border=no>';
104 else
105 return '';
106 end if;
107 END item_reqd;
108
109 FUNCTION item_halign(l_index in number) RETURN VARCHAR2 IS
110 BEGIN
111
112 RETURN ' align=' ||
113 ak_query_pkg.g_items_table(l_index).horizontal_alignment;
114
115 END item_halign;
116
117 FUNCTION item_valign(l_index in number) RETURN VARCHAR2 IS
118 BEGIN
119
120 RETURN ' valign=' ||
121 ak_query_pkg.g_items_table(l_index).vertical_alignment;
122
123 END item_valign;
124
125 FUNCTION item_name(l_index in number) RETURN VARCHAR2 IS
126 BEGIN
127
128 RETURN ak_query_pkg.g_items_table(l_index).attribute_label_long;
129
130 END item_name;
131
132 FUNCTION item_code(l_index in number) RETURN VARCHAR2 IS
133 BEGIN
134
135 RETURN ak_query_pkg.g_items_table(l_index).attribute_code;
136
137 END item_code;
138
139 FUNCTION item_style(l_index in number) RETURN VARCHAR2 IS
140 BEGIN
141
142 RETURN ak_query_pkg.g_items_table(l_index).item_style;
143
144 END item_style;
145
146 FUNCTION item_displayed(l_index in number) RETURN BOOLEAN IS
147 BEGIN
148
149 RETURN (ak_query_pkg.g_items_table(l_index).node_display_flag = 'Y');
150
151 END item_displayed;
152
153 FUNCTION item_updateable(l_index in number) RETURN BOOLEAN IS
154 BEGIN
155
156 RETURN (ak_query_pkg.g_items_table(l_index).update_flag = 'Y');
157
158 END item_updateable;
159
160 FUNCTION item_size (l_index in number) RETURN VARCHAR2 IS
161 BEGIN
162
163 RETURN ' size=' || to_char(ak_query_pkg.g_items_table(l_index).display_value_length);
164
165 END item_size;
166
167 FUNCTION item_maxlength (l_index in number) RETURN VARCHAR2 IS
168 BEGIN
169
170 RETURN ' maxlength=' || to_char(ak_query_pkg.g_items_table(l_index).attribute_value_length);
171
172 END item_maxlength;
173
174 FUNCTION item_lov(l_index in number) RETURN VARCHAR2 IS
175 BEGIN
176
177 IF (ak_query_pkg.g_items_table(l_index).lov_region_code IS NOT NULL AND
178 ak_query_pkg.g_items_table(l_index).lov_attribute_code IS NOT NULL)
179 THEN
180 return '<A HREF="javascript:call_lov('''||
181 item_code(l_index) || ''')"' ||
182 '><IMG SRC="/OA_MEDIA/FNDLSTOV.gif" BORDER=0 WIDTH=23 ' ||
183 'HEIGHT=21 border=no align=absmiddle></A>';
184 ELSE
185 return '';
186 END IF;
187
188 END item_lov;
189
190 FUNCTION item_lov_multi(l_index in number, l_row in number, l_wip_row in number) RETURN VARCHAR2 IS
191 BEGIN
192
193 IF (ak_query_pkg.g_items_table(l_index).lov_region_code IS NOT NULL AND
194 ak_query_pkg.g_items_table(l_index).lov_attribute_code IS NOT NULL)
195 THEN
196 return '<A HREF="javascript:call_LOV('''||
197 item_code(l_index) || '''' || ',' || '''' || to_char(l_row-1) ||
198 '''' || ',' || '''' || l_script_name || '''' || ',' ||
199 '''' || to_char(l_wip_row-1) ||
200 ''')"' ||
201 '><IMG SRC="/OA_MEDIA/FNDLSTOV.gif" BORDER=0 WIDTH=23 ' ||
202 'HEIGHT=21 border=no align=absmiddle></A>';
203 ELSE
204 return '';
205 END IF;
206
207 END item_lov_multi;
208
209 FUNCTION item_wrap(l_index in number) RETURN VARCHAR2 IS
210 BEGIN
211
212 IF item_code(l_index) = 'POS_ITEM_DESCRIPTION' THEN
213 RETURN ' nowrap ';
214 ELSE
215 RETURN '';
216 END IF;
217
218 END item_wrap;
219
220 PROCEDURE hidden_label(l_index in number) IS
221 BEGIN
222
223 htp.p('<!-- ' || item_code(l_index) ||
224 ' - ' || item_style(l_index) || ' -->');
225
226 END hidden_label;
227
228 PROCEDURE hidden_field(l_index in number,
229 l_res_index in number,
230 l_col in number) IS
231 BEGIN
232
233 htp.p('<input name="' || item_code(l_index) ||
234 '" type="HIDDEN" VALUE="' || get_result_value(l_res_index, l_col) ||
235 '">');
236
237 END hidden_field;
238
239 PROCEDURE single_row_label(l_index in number) IS
240 BEGIN
241
242 htp.p('<td bgcolor=#cccccc' ||
243 item_halign(l_index) ||
244 item_valign(l_index) ||
245 '>' || item_reqd(l_index) ||
246 '<font class=promptblack>' || item_name(l_index) ||
247 '</font>' ||
248 '</td>');
249
250 END single_row_label;
251
252 PROCEDURE multi_row_label (l_index in number) IS
253 BEGIN
254
255 htp.p('<td bgcolor=#336699' ||
256 item_halign(l_index) ||
257 item_valign(l_index) ||
258 '>' ||
259 item_reqd(l_index)
260 );
261
262 IF item_code(l_index) = 'POS_SELECT' THEN
263 htp.p('<a href="javascript:check_all()" >');
264 END IF;
265
266 htp.p('<font class=promptwhite>' || item_name(l_index) || '</font>');
267
268 IF item_code(l_index) = 'POS_SELECT' THEN
269 htp.p('</a>');
270 END IF;
271
272 htp.p('</td>');
273
274 END multi_row_label;
275
276 PROCEDURE non_updateable(l_index in number,
277 l_res_index in number,
278 l_col in number) IS
279 BEGIN
280
281 htp.p('<td ' || item_wrap(l_index) ||
282 item_halign(l_index) ||
283 item_valign(l_index) ||
284 '>');
285
286 IF item_code(l_index) = 'POS_PO_NUM' THEN
287
288 htp.p('<a target="PONUM" href="' || po_num(get_result_value(l_res_index, l_col)) ||
289 '">' ||
290 '<font class=tabledata>' ||
291 nvl(get_result_value(l_res_index, l_col), ' ') ||
292 '</font></a>');
293 ELSE
294
295 htp.p('<font class=tabledata>' ||
296 nvl(get_result_value(l_res_index, l_col), ' ') ||
297 '</font>');
298 END IF;
299
300 htp.p('</td>');
301
302 END non_updateable;
303
304 PROCEDURE updateable(l_index in number,
305 l_res_index in number,
306 l_col in number,
307 l_row in number default null,
308 l_wip_row in number default null) IS
309 x_value varchar2(2000);
310 BEGIN
311
312 x_value := ltrim(get_result_value(l_res_index, l_col));
313
314 IF ship_date_error AND item_code(l_index) = 'POS_SHIP_DATE' THEN
315 x_value := x_ship_date;
316 END IF;
317
318 IF receipt_date_error AND item_code(l_index) = 'POS_EXPECTED_RECEIPT_DATE' THEN
319 x_value := x_receipt_date;
320 END IF;
321
322 htp.p('<td nowrap' ||
323 item_halign(l_index) ||
324 item_valign(l_index) ||
325 '>' ||
326 '<font class=datablack>'||
327 '<input type=text ' || item_size(l_index) || item_maxlength(l_index) ||
328 ' name="' || item_code(l_index) || '"' ||
329 ' value="' ||
330 x_value ||
331 '" ></font>');
332
333 IF l_row is null THEN
334 htp.p('</td><td width=23>');
335 htp.p(item_lov(l_index));
336 htp.p('</td>');
337 ELSE
338 htp.p(item_lov_multi(l_index,l_row,l_wip_row));
339 END IF;
340
341 htp.p('</td>');
342
343 END updateable;
344
345 PROCEDURE image(l_index in number,
346 href in varchar2) IS
347 BEGIN
348
349 htp.p('<td nowrap ' ||
350 item_halign(l_index) ||
351 item_valign(l_index) ||
352 '>' ||
353 '<a href=' || href ||
354 ' target="_self"><IMG NAME="' ||
355 item_code(l_index) ||
356 '" src=/OA_MEDIA/FNDIITMD.gif border=no></a></td>');
357
358 END image;
359
360 PROCEDURE buyer_notify IS
361 l_ItemType VARCHAR2(100) := 'POSASNNT';
362 l_ItemKey VARCHAR2(100);
363 i NUMBER := 0;
364 BEGIN
365
366 FOR c_rec in c_buyer LOOP
367
368 i := i + 1;
369 l_ItemKey := 'POS_CREATE_ASN' || to_char(l_header_id) || '-' || to_char(i);
370
371 wf_engine.createProcess(ItemType => l_ItemType,
372 ItemKey => l_ItemKey,
373 Process => 'BUYER_NOTIFICATION');
374
375 wf_engine.SetItemAttrNumber(itemtype => l_ItemType,
376 itemkey => l_ItemKey,
377 aname => 'BUYER_USER_ID',
378 avalue => c_rec.buyer_id);
379
380 wf_engine.SetItemAttrText(itemtype => l_ItemType,
381 itemkey => l_ItemKey,
382 aname => 'SHIPMENT_NUM',
383 avalue => c_rec.shipment_num);
384
385 wf_engine.SetItemAttrDate(itemtype => l_ItemType,
386 itemkey => l_ItemKey,
387 aname => 'SHIP_DATE',
388 avalue => c_rec.ship_date);
389
390 wf_engine.SetItemAttrDate(itemtype => l_ItemType,
391 itemkey => l_ItemKey,
392 aname => 'EXPECTED_RECEIPT_DATE',
393 avalue => c_rec.expected_receipt_date);
394
395 wf_engine.SetItemAttrNumber(itemtype => l_ItemType,
396 itemkey => l_ItemKey,
397 aname => 'SUPPLIER_ID',
398 avalue => c_rec.supplier_id);
399
400 wf_engine.SetItemAttrText(itemtype => l_ItemType,
401 itemkey => l_ItemKey,
402 aname => 'SUPPLIER',
403 avalue => c_rec.supplier);
404
405 wf_engine.StartProcess(ItemType => l_ItemType,
406 ItemKey => l_ItemKey );
407 END LOOP;
408
409 END buyer_notify;
410
411 PROCEDURE init_page IS
412
413 BEGIN
414
415 htp.htmlOpen;
416 htp.headOpen;
417 htp.linkRel('STYLESHEET', '/OA_HTML/US/POSSTYLE.css');
418
419 END init_page;
420
421 PROCEDURE init_body IS
422 BEGIN
423
424 htp.headClose;
425 htp.bodyOpen(null,'bgcolor=#cccccc link=blue vlink=blue alink=#ff0000');
426
427 END init_body;
428
429 PROCEDURE close_page IS
430 BEGIN
431
432 htp.bodyClose;
433 htp.htmlClose;
434
435 END close_page;
436
437 PROCEDURE show_edit_page IS
438 BEGIN
439
440 init_page;
441
442 htp.headClose;
443
444 htp.p(' <script src="/OA_HTML/POSCUTIL.js" language="JavaScript">');
445 htp.p(' </script>');
446 htp.p(' <script src="/OA_HTML/POSWUTIL.js" language="JavaScript">');
447 htp.p(' </script>');
448 htp.p(' <script src="/OA_HTML/POSEVENT.js" language="JavaScript">');
449 htp.p(' </script>');
450
451
452 htp.p('<frameset rows = "30%, 12%, 53%, 5%" border=0>');
453
454 htp.p('<frame src="' || l_script_name ||
455 '/pos_asn.show_edit_header"' ||
456 ' name=header' ||
457 ' marginwidth=0' ||
458 ' marginheight=0' ||
459 ' scrolling=auto>');
460
461 htp.p('<frame src="' || l_script_name ||
462 '/pos_asn.show_shipment_help"' ||
463 ' name=shiphelp' ||
464 ' marginwidth=0' ||
465 ' marginheight=0' ||
466 ' scrolling=no>');
467
468 htp.p('<frame src="' || l_script_name ||
469 '/pos_asn.show_edit_shipments"' ||
470 ' name=shipments' ||
471 ' marginwidth=5' ||
472 ' marginheight=0' ||
473 ' scrolling=auto>');
474
475 htp.p('<frame src="' || l_script_name ||
476 '/pos_asn.show_delete_frame"' ||
477 ' name=delete' ||
478 ' marginwidth=5' ||
479 ' marginheight=0' ||
480 ' scrolling=no>');
481
482 htp.p('</frameset>');
483
484 htp.htmlClose;
485
486 END show_edit_page;
487
488 PROCEDURE show_edit_header IS
489 BEGIN
490
491 init_page;
492
493 js.scriptOpen;
494 icx_util.LOVscript;
495 js.scriptClose;
496
497 htp.p(' <script src="/OA_HTML/POSASNED.js" language="JavaScript">');
498 htp.p(' </script>');
499
500 htp.p('<body bgcolor=#cccccc onLoad="javascript:LoadPage(' ||
501 '''' || sub_state || '''' || ',' ||
502 '''' || error_message || '''' || ',' ||
503 '''' || but1 || '''' || ',' ||
504 '''' || but2 || '''' || ',' ||
505 '''' || but3 || '''' ||
506 ')"' || 'link=blue vlink=blue alink=#ff0000>');
507
508 htp.p('<form name="POS_ASN_HEADER" action="' || l_script_name ||
509 '/pos_asn.update_header" target="header" method="GET">');
510
511 print_edit_header;
512
513 htp.p('</form>');
514
515 close_page;
516
517 END show_edit_header;
518
519 PROCEDURE show_shipment_help IS
520 v_messageText1 VARCHAR2(2000);
521 v_messageText2 VARCHAR2(2000);
522 v_messageText3 VARCHAR2(2000);
523 BEGIN
524
525 v_messageText1 := fnd_message.get_string('ICX', 'ICX_POS_ASN_EDIT_SHIPMENT_HT1');
526 v_messageText2 := fnd_message.get_string('ICX', 'ICX_POS_ASN_EDIT_SHIPMENT_HT2');
527 v_messageText3 := fnd_message.get_string('ICX', 'ICX_POS_ASN_EDIT_SHIPMENT_HT3');
528
529 init_page;
530 init_body;
531
532 htp.p('<table width=100% cellpadding=0 cellspacing=0 border=0>');
533 htp.p('<!-- This row contains the help text -->');
534
535 htp.p('<tr>
536 <td height=1 bgcolor=black><img src=/OA_MEDIA/FNDPX1.gif></td>
537 </tr>');
538
539 htp.p('<tr bgcolor=#cccccc>');
540 htp.p('<td valign=top>' ||
541 '<font class=helptext> ' ||
542 v_messageText1 ||
543 '</font></td>');
544 htp.p('</tr>');
545
546 htp.p('<tr bgcolor=#cccccc>');
547 htp.p('<td valign=top>' ||
548 '<font class=helptext> ' ||
549 v_messageText2 ||
550 '</font></td>');
551 htp.p('</tr>');
552
553 htp.p('<tr bgcolor=#cccccc>');
554 htp.p('<td valign=top>' ||
555 '<font class=helptext> ' ||
556 v_messageText3 ||
557 '</font></td>');
558 htp.p('</tr>');
559
560 htp.p('</table>');
561
562 close_page;
563
564 END show_shipment_help;
565
566 PROCEDURE show_edit_shipments IS
567 BEGIN
568
569 init_page;
570
571 js.scriptOpen;
572 icx_util.LOVscript;
573 js.scriptClose;
574
575 htp.p(' <script src="/OA_HTML/POSASNED.js" language="JavaScript">');
576 htp.p(' </script>');
577
578 htp.headClose;
579 htp.p('<body bgcolor=#cccccc onLoad="javascript:LoadPage(' ||
580 '''' || sub_state || '''' || ',' ||
581 '''' || error_message || '''' || ',' ||
582 '''' || but1 || '''' || ',' ||
583 '''' || but2 || '''' || ',' ||
584 '''' || but3 || '''' ||
585 ')"' || 'link=blue vlink=blue alink=#ff0000>');
586
587 -- Create a dummy form to allow for multi row lovs (--,--)
588
589 htp.p('<form name="POS_ASN">' ||
590 '<input name="POS_UNIT_OF_MEASURE" type="HIDDEN" VALUE="">' ||
591 '<input name="POS_UOM_CLASS" type="HIDDEN" VALUE="">' ||
592 '<input name="POS_PO_DISTRIBUTION_ID" type="HIDDEN" VALUE="">' ||
593 '<input name="POS_ASN_WIP_JOB" type="HIDDEN" VALUE="">' ||
594 '<input name="POS_WIP_ENTITY_ID" type="HIDDEN" VALUE="">' ||
595 '<input name="POS_WIP_OPERATION_SEQ_NUM" type="HIDDEN" VALUE="">' ||
596 '<input name="POS_WIP_LINE_ID" type="HIDDEN" VALUE="">' ||
597 '<input name="POS_ITEM_ID" type="HIDDEN" VALUE="">' ||
598 '<input name="POS_ITEM_DESCRIPTION" type="HIDDEN" VALUE="">' ||
599 '</form>');
600
601 htp.p('<form name="POS_ASN_SHIPMENTS" ACTION="' || l_script_name ||
602 '/pos_asn.update_shipments" target="shipments" method="GET">');
603
604 print_edit_shipments;
605
606 htp.p('</form>');
607
608 close_page;
609
610 END show_edit_shipments;
611
612 PROCEDURE show_delete_frame IS
613 v_messageText1 VARCHAR2(2000);
614 v_messageText2 VARCHAR2(2000);
615 BEGIN
616
617 v_messageText1 := fnd_message.get_string('ICX', 'ICX_POS_ASN_EDIT_COPY_BUT');
618 v_messageText2 := fnd_message.get_string('ICX', 'ICX_POS_ASN_EDIT_DELETE_BUT');
619
620 init_page;
621 init_body;
622
623 button('javascript:parent.shipments.delt()', v_messageText2,
624 'javascript:parent.shipments.explode()', v_messageText1);
625
626 close_page;
627
628 END show_delete_frame;
629
630 PROCEDURE err_htp(msg IN VARCHAR2) IS
631 BEGIN
632 IF (nvl(length(error_message), 0) + length(msg)) < MAX_ERROR_LEN then
633 error_message := error_message || msg;
634 END IF;
635 END err_htp;
636
637 PROCEDURE show_error_page IS
638 title VARCHAR2(2000);
639 BEGIN
640
641 title := fnd_message.get_string('ICX', 'ICX_POS_ASN_EDIT_MESS_WIN_T2');
642
643 err_htp(htf.htmlOpen);
644 err_htp('<TITLE>' || title || '</TITLE>');
645 err_htp(htf.headOpen);
646 err_htp('<LINK REL=STYLESHEET HREF=/OA_HTML/US/POSSTYLE.css>');
647
648 err_htp(htf.headClose);
649 err_htp('<body bgcolor=#cccccc >');
650
651 IF header_error THEN
652 print_simple_head_err_page;
653 ELSE
654 print_error_page;
655 END if;
656
657 END show_error_page;
658
659 PROCEDURE show_ok_page IS
660 v_messageText1 VARCHAR2(2000);
661 title VARCHAR2(2000);
662 BEGIN
663
664 v_messageText1 := fnd_message.get_string('ICX', 'ICX_POS_ASN_CREATED');
665 title := fnd_message.get_string('ICX', 'ICX_POS_ASN_EDIT_MESS_WIN_T1');
666
667 err_htp(htf.htmlOpen);
668 err_htp('<TITLE>' || title || '</TITLE>');
669 err_htp(htf.headOpen);
670 err_htp('<LINK REL=STYLESHEET HREF=/OA_HTML/US/POSSTYLE.css>');
671
672 err_htp(htf.headClose);
673 err_htp('<body bgcolor=#cccccc >');
674
675 err_htp('<B><font class=datablack>' || v_messageText1 || '</font></B>');
676
677 END show_ok_page;
678
679 PROCEDURE print_edit_header IS
680
681 v_messageText1 VARCHAR2(2000);
682 v_messageText2 VARCHAR2(2000);
683 l_attribute_index NUMBER;
684 l_result_index NUMBER;
685 l_current_col NUMBER;
686 l_col NUMBER;
687 l_current_row NUMBER;
688 l_where_clause VARCHAR2(2000) := 'SESSION_ID = ' || to_char(l_session_id);
689
690 BEGIN
691
692 fnd_client_info.set_org_context(l_org_id);
693 fnd_global.apps_initialize(l_user_id, l_responsibility_id, 178);
694
695 htp.p('<table width=100% cellpadding=0 cellspacing=0 border=0>');
696
697 v_messageText1 := fnd_message.get_string('ICX', 'ICX_POS_ASN_EDIT_HEADER_HT1');
698 v_messageText2 := fnd_message.get_string('ICX', 'ICX_POS_REQUIRED_FIELD');
699
700 htp.p('<!-- This row contains the help text -->');
701 htp.p('<tr bgcolor=#cccccc>');
702 htp.p('<td valign=top>' ||
703 '<font class=helptext> ' ||
704 v_messageText1 ||
705 '<IMG SRC="/OA_MEDIA/FNDIREQD.gif" border=no align=top>' ||
706 v_messageText2 ||
707 '</font></td>');
708 htp.p('</tr>');
709 htp.p('</table>');
710
711
712 ak_query_pkg.exec_query(p_parent_region_appl_id => 178,
713 p_parent_region_code => 'POS_ASN_HEADERS',
714 p_where_clause => l_where_clause,
715 p_responsibility_id => l_responsibility_id,
716 p_user_id => l_user_id,
717 p_return_parents => 'T',
718 p_return_children => 'F');
719
720 l_attribute_index := ak_query_pkg.g_items_table.FIRST;
721 l_result_index := ak_query_pkg.g_results_table.FIRST;
722
723 htp.p('<table width=100% cellpadding=2 cellspacing=0 border=0>');
724
725 htp.p('<tr bgcolor=#cccccc>');
726
727 l_current_col := 0;
728 l_col := 0;
729
730 WHILE (l_attribute_index IS NOT NULL) LOOP
731
732 l_current_col := l_current_col + 1;
733
734 IF (item_style(l_attribute_index) = 'HIDDEN') THEN
735
736 hidden_label(l_attribute_index);
737 hidden_field(l_attribute_index,l_result_index, l_current_col);
738
739 ELSIF item_displayed(l_attribute_index) THEN
740 IF (item_style(l_attribute_index) = 'TEXT') THEN
741 IF item_updateable(l_attribute_index) THEN
742
743 single_row_label(l_attribute_index);
744 updateable(l_attribute_index, l_result_index, l_current_col);
745
746 ELSE
747
748 single_row_label(l_attribute_index);
749 non_updateable(l_attribute_index, l_result_index, l_current_col);
750
751 END IF;
752
753 l_col := l_col + 1;
754
755 ELSIF (item_style(l_attribute_index) = 'IMAGE') THEN
756 IF (item_code(l_attribute_index) = 'POS_ASBN') AND
757 (FND_FUNCTION.TEST('ICX_DISABLE_ASBN')) THEN
758
759 l_col := l_col + 1;
760
761 single_row_label(l_attribute_index);
762 image(l_attribute_index,'"javascript:top.ASBNClicked()"');
763 htp.p('<td width=23></td>');
764
765 END IF;
766 l_current_col := l_current_col -1;
767 END IF;
768 END IF;
769
770 l_attribute_index := ak_query_pkg.g_items_table.NEXT(l_attribute_index);
771
772 if ((l_col mod 3) = 0) THEN
773 htp.p('</tr>');
774 htp.p('<tr bgcolor=#cccccc>');
775 end if;
776
777 END LOOP;
778
779 htp.p('</tr>');
780 htp.p('</table>');
781
782 END print_edit_header;
783
784 PROCEDURE print_edit_shipments IS
785 l_attribute_index NUMBER;
786 l_result_index NUMBER;
787 l_current_col NUMBER;
788 l_current_row NUMBER;
789 l_wip_row NUMBER;
790 l_where_clause VARCHAR2(2000) := 'SESSION_ID = ' || to_char(l_session_id);
791
792 BEGIN
793
794 fnd_client_info.set_org_context(l_org_id);
795
796 ak_query_pkg.exec_query(p_parent_region_appl_id => 178,
797 p_parent_region_code => 'POS_ASN_SHIPMENTS',
798 p_where_clause => l_where_clause,
799 p_responsibility_id => l_responsibility_id,
800 p_user_id => l_user_id,
801 p_return_parents => 'T',
802 p_return_children => 'F');
803
804 l_attribute_index := ak_query_pkg.g_items_table.FIRST;
805
806
807 htp.p('<table width=96% bgcolor=#999999 cellpadding=2 cellspacing=0 border=0>');
808 htp.p('<tr><td>');
809
810 htp.p('<table align=center bgcolor=#999999 cellpadding=2 cellspacing=1 border=0>');
811
812 htp.p('<input name="POS_SUBMIT" type="HIDDEN" value="SUBMIT">');
813
814 /* ---- Print the table heading --- */
815
816 htp.p('<tr>');
817
818 WHILE (l_attribute_index IS NOT NULL) LOOP
819
820 IF (item_style(l_attribute_index) = 'HIDDEN') THEN
821
822 hidden_label(l_attribute_index);
823
824 ELSIF item_displayed(l_attribute_index) THEN
825
826 multi_row_label(l_attribute_index);
827
828 END IF;
829
830 l_attribute_index := ak_query_pkg.g_items_table.NEXT(l_attribute_index);
831
832 END LOOP;
833
834 htp.p('</tr>');
835
836 /* ----- end print table heading ----*/
837
838
839 /* ----- print contents -----------*/
840
841 IF ak_query_pkg.g_results_table.count > 0 THEN
842
843 l_result_index := ak_query_pkg.g_results_table.FIRST;
844
845 l_current_row := 0;
846 l_wip_row := 0;
847
848 WHILE (l_result_index IS NOT NULL) LOOP
849
850 l_current_row := l_current_row + 1;
851
852 if ((l_current_row mod 2) = 0) THEN
853 htp.p('<tr BGCOLOR=''#ffffff'' >');
854 else
855 htp.p('<tr BGCOLOR=''#99ccff'' >');
856 end if;
857
858 l_attribute_index := ak_query_pkg.g_items_table.FIRST;
859
860 l_current_col := 0;
861
862 WHILE (l_attribute_index IS NOT NULL) LOOP
863
864 l_current_col := l_current_col + 1;
865
866 IF (item_style(l_attribute_index) = 'HIDDEN') THEN
867
868 hidden_field(l_attribute_index,l_result_index, l_current_col);
869
870 ELSE
871 IF item_displayed(l_attribute_index) THEN
872 IF (item_style(l_attribute_index) = 'TEXT') THEN
873 IF item_updateable(l_attribute_index) THEN
874
875 IF item_code(l_attribute_index) = 'POS_ASN_WIP_JOB' AND
876 substrb(get_result_value(l_result_index, l_current_col), 1, 1) <> ' ' THEN
877
878 non_updateable(l_attribute_index, l_result_index, l_current_col);
879
880 ELSE
881
882 IF item_code(l_attribute_index) = 'POS_ASN_WIP_JOB' THEN
883 l_wip_row := l_wip_row + 1;
884 END IF;
885
886 updateable(l_attribute_index,l_result_index,
887 l_current_col, l_current_row, l_wip_row);
888 END IF;
889
890 ELSE
891
892 non_updateable(l_attribute_index, l_result_index, l_current_col);
893
894 END IF;
895 ELSIF (item_style(l_attribute_index) = 'CHECKBOX') THEN
896 l_current_col := l_current_col -1;
897 htp.p('<td nowrap ' ||
898 item_halign(l_attribute_index) ||
899 item_valign(l_attribute_index) ||
900 '>' ||
901 '<B><font class=datablack>' ||
902 '<input type="checkbox" name="' ||
903 item_code(l_attribute_index) || '"' ||
904 ' value="' || to_char(l_current_row) ||
905 '" ></font></B>' ||
906 '</td>');
907
908 ELSIF (item_style(l_attribute_index) = 'IMAGE') THEN
909
910 l_current_col := l_current_col -1;
911 image(l_attribute_index,'"javascript:details(' ||
912 to_char(l_current_row-1) || ')"');
913
914 END IF;
915 END IF;
916 END IF;
917
918 l_attribute_index := ak_query_pkg.g_items_table.NEXT(l_attribute_index);
919
920 END LOOP;
921
922 htp.p('</tr>');
923
924 l_result_index := ak_query_pkg.g_results_table.NEXT(l_result_index);
925
926 END LOOP;
927
928 END IF;
929
930 htp.p('</table>');
931 htp.p('</td></tr></table>');
932
933 END print_edit_shipments;
934
935 PROCEDURE print_simple_head_err_page IS
936 v_messageText1 VARCHAR2(2000);
937 BEGIN
938
939 but3 := fnd_message.get_string('ICX', 'ICX_POS_ASN_EDIT_OK_BUT');
940
941 IF ship_date_error OR receipt_date_error THEN
942 v_messageText1 := fnd_message.get_string('ICX', 'ICX_POS_DATE_FORMAT') ||
943 l_date_format;
944 END IF;
945
946 err_htp('<font class=datablack BGCOLOR=#ffffff>' || v_messageText1 || '</font>');
947
948 END print_simple_head_err_page;
949
950 PROCEDURE print_error_page IS
951 l_count NUMBER;
952 v_messageText1 VARCHAR2(2000);
953 v_messageText2 VARCHAR2(2000);
954 BEGIN
955
956 v_messageText1 := fnd_message.get_string('ICX', 'ICX_POS_ASN_EDIT_HEADER_E1');
957 v_messageText2 := fnd_message.get_string('ICX', 'ICX_POS_ASN_EDIT_LINES_E1');
958
959 l_count := 0;
960
961 FOR c_rec in c_header_err LOOP
962
963 if l_count = 0 then
964 err_htp('<B><font class=datablack>' || v_messageText1 || '</font></B>');
965 err_htp('<table align=center bgcolor=#999999 cellpadding=2 cellspacing=1 border=0>');
966 end if;
967
968 l_count := l_count + 1;
969 if ((l_count mod 2) = 0) THEN
970 err_htp('<tr BGCOLOR=#ffffff>');
971 else
972 err_htp('<tr BGCOLOR=#99ccff>');
973 end if;
974
975 err_htp('<td>');
976 err_htp(c_rec.error_message);
977 err_htp('</td></tr>');
978
979 END LOOP;
980
981 err_htp('</table>');
982
983 l_count := 0;
984
985 FOR c_rec in c_shipment_err LOOP
986
987 if l_count = 0 then
988 err_htp('<B><font class=datablack>' || v_messageText2 || '</font></B>');
989 err_htp('<table align=center bgcolor=#999999 cellpadding=2 cellspacing=1 border=0>');
990 end if;
991
992 l_count := l_count + 1;
993 if ((l_count mod 2) = 0) THEN
994 err_htp('<tr BGCOLOR=#ffffff>');
995 else
996 err_htp('<tr BGCOLOR=#99ccff>');
997 end if;
998
999 err_htp('<td>');
1000 err_htp(c_rec.error_seq);
1001 err_htp('</td>');
1002 err_htp('<td>');
1003 err_htp(c_rec.error_message);
1004 err_htp('</td>');
1005 err_htp('</tr>');
1006
1007 END LOOP;
1008
1009 err_htp('</table>');
1010
1011 END print_error_page;
1012
1013 PROCEDURE create_rcv_header IS
1014 BEGIN
1015
1016 SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL INTO l_request_id from dual;
1017
1018 SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL INTO l_header_id from dual;
1019
1020 insert into rcv_headers_interface
1021 (HEADER_INTERFACE_ID ,
1022 GROUP_ID ,
1023 PROCESSING_STATUS_CODE ,
1024 PROCESSING_REQUEST_ID ,
1025 RECEIPT_SOURCE_CODE ,
1026 TRANSACTION_TYPE ,
1027 LAST_UPDATE_DATE ,
1028 LAST_UPDATED_BY ,
1029 LAST_UPDATE_LOGIN ,
1030 CREATION_DATE ,
1031 CREATED_BY ,
1032 LOCATION_ID ,
1033 SHIP_TO_ORGANIZATION_ID ,
1034 VENDOR_ID ,
1035 VENDOR_SITE_ID ,
1036 SHIPPED_DATE ,
1037 ASN_TYPE ,
1038 SHIPMENT_NUM ,
1039 EXPECTED_RECEIPT_DATE ,
1040 PACKING_SLIP ,
1041 WAYBILL_AIRBILL_NUM ,
1042 BILL_OF_LADING ,
1043 FREIGHT_CARRIER_CODE ,
1044 FREIGHT_TERMS ,
1045 NUM_OF_CONTAINERS ,
1046 COMMENTS ,
1047 CARRIER_METHOD ,
1048 CARRIER_EQUIPMENT ,
1049 FREIGHT_BILL_NUMBER ,
1050 GROSS_WEIGHT ,
1051 GROSS_WEIGHT_UOM_CODE ,
1052 NET_WEIGHT ,
1053 NET_WEIGHT_UOM_CODE ,
1054 TAR_WEIGHT ,
1055 TAR_WEIGHT_UOM_CODE ,
1056 PACKAGING_CODE ,
1057 SPECIAL_HANDLING_CODE ,
1058 INVOICE_NUM ,
1059 INVOICE_DATE ,
1060 TOTAL_INVOICE_AMOUNT ,
1061 FREIGHT_AMOUNT ,
1062 TAX_NAME ,
1063 TAX_AMOUNT ,
1064 CURRENCY_CODE ,
1065 CONVERSION_RATE_TYPE ,
1066 CONVERSION_RATE ,
1067 CONVERSION_RATE_DATE ,
1068 VALIDATION_FLAG
1069 )
1070 select
1071 l_header_id ,
1072 l_request_id ,
1073 'RUNNING' ,
1074 l_request_id ,
1075 'VENDOR' ,
1076 'NEW' ,
1077 LAST_UPDATE_DATE ,
1078 LAST_UPDATED_BY ,
1079 LAST_UPDATE_LOGIN ,
1080 CREATION_DATE ,
1081 CREATED_BY ,
1082 SHIP_TO_LOCATION_ID ,
1083 SHIP_TO_ORGANIZATION_ID ,
1084 VENDOR_ID ,
1085 VENDOR_SITE_ID ,
1086 SHIP_DATE ,
1087 decode(INVOICE_NUM, null, 'ASN', 'ASBN'),
1088 SHIPMENT_NUM ,
1089 EXPECTED_RECEIPT_DATE ,
1090 PACKING_SLIP ,
1091 WAYBILL_AIRBILL_NUM ,
1092 BILL_OF_LADING ,
1093 FREIGHT_CARRIER_CODE ,
1094 FREIGHT_TERMS ,
1095 NUM_OF_CONTAINERS ,
1096 COMMENTS ,
1097 CARRIER_METHOD ,
1098 CARRIER_EQUIPMENT ,
1099 FREIGHT_BILL_NUMBER ,
1100 GROSS_WEIGHT ,
1101 GROSS_WEIGHT_UOM_CODE ,
1102 NET_WEIGHT ,
1103 NET_WEIGHT_UOM_CODE ,
1104 TAR_WEIGHT ,
1105 TAR_WEIGHT_UOM_CODE ,
1106 PACKAGING_CODE ,
1107 SPECIAL_HANDLING_CODE ,
1108 INVOICE_NUM ,
1109 INVOICE_DATE ,
1110 TOTAL_INVOICE_AMOUNT ,
1111 FREIGHT_AMOUNT ,
1112 null , /* TAX_NAME */
1113 null , /* TAX_AMOUNT */
1114 CURRENCY_CODE ,
1115 CURRENCY_CONVERSION_TYPE ,
1116 CURRENCY_CONVERSION_RATE ,
1117 CURRENCY_CONVERSION_DATE ,
1118 'Y'
1119 from pos_asn_shop_cart_headers
1120 where session_id = l_session_id;
1121
1122 END create_rcv_header;
1123
1124 PROCEDURE create_rcv_transaction IS
1125 x_deliver_to_location_id NUMBER;
1126 BEGIN
1127
1128 FOR c_rec in c_lines LOOP
1129
1130 select RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL into l_line_id from dual;
1131
1132 insert into rcv_transactions_interface
1133 ( INTERFACE_TRANSACTION_ID ,
1134 HEADER_INTERFACE_ID ,
1135 GROUP_ID ,
1136 TRANSACTION_TYPE ,
1137 TRANSACTION_DATE ,
1138 PROCESSING_STATUS_CODE ,
1139 PROCESSING_MODE_CODE ,
1140 TRANSACTION_STATUS_CODE ,
1141 AUTO_TRANSACT_CODE ,
1142 RECEIPT_SOURCE_CODE ,
1143 SOURCE_DOCUMENT_CODE ,
1144 PO_HEADER_ID ,
1145 PO_LINE_ID ,
1146 PO_LINE_LOCATION_ID ,
1147 QUANTITY ,
1148 UNIT_OF_MEASURE ,
1149 UOM_CODE ,
1150 LAST_UPDATE_DATE ,
1151 LAST_UPDATED_BY ,
1152 LAST_UPDATE_LOGIN ,
1153 CREATION_DATE ,
1154 CREATED_BY ,
1155 ITEM_ID ,
1156 EXPECTED_RECEIPT_DATE ,
1157 COMMENTS ,
1158 WAYBILL_AIRBILL_NUM ,
1159 BARCODE_LABEL ,
1160 BILL_OF_LADING ,
1161 CONTAINER_NUM ,
1162 COUNTRY_OF_ORIGIN_CODE ,
1163 VENDOR_CUM_SHIPPED_QTY ,
1164 FREIGHT_CARRIER_CODE ,
1165 VENDOR_LOT_NUM ,
1166 TRUCK_NUM ,
1167 NUM_OF_CONTAINERS ,
1168 PACKING_SLIP ,
1169 REASON_ID ,
1170 ACTUAL_COST ,
1171 TRANSFER_COST ,
1172 TRANSPORTATION_COST ,
1173 RMA_REFERENCE ,
1174 VALIDATION_FLAG ,
1175 WIP_ENTITY_ID ,
1176 WIP_LINE_ID ,
1177 WIP_OPERATION_SEQ_NUM ,
1178 PO_DISTRIBUTION_ID ,
1179 QUANTITY_INVOICED
1180 )
1181 values
1182 ( l_line_id ,
1183 c_rec.HEADER_ID ,
1184 c_rec.GROUP_ID ,
1185 c_rec.TRANSACTION_TYPE ,
1186 c_rec.TRANSACTION_DATE ,
1187 c_rec.PROCESSING_STATUS_CODE ,
1188 c_rec.PROCESSING_MODE_CODE ,
1189 c_rec.TRANSACTION_STATUS_CODE,
1190 c_rec.AUTO_TRANSACT_CODE ,
1191 c_rec.RECEIPT_SOURCE_CODE ,
1192 c_rec.SOURCE_DOCUMENT_CODE ,
1193 c_rec.PO_HEADER_ID ,
1194 c_rec.PO_LINE_ID ,
1195 c_rec.PO_LINE_LOCATION_ID ,
1196 c_rec.QUANTITY ,
1197 c_rec.UNIT_OF_MEASURE ,
1198 c_rec.UOM_CODE ,
1199 c_rec.LAST_UPDATE_DATE ,
1200 c_rec.LAST_UPDATED_BY ,
1201 c_rec.LAST_UPDATE_LOGIN ,
1202 c_rec.CREATION_DATE ,
1203 c_rec.CREATED_BY ,
1204 c_rec.ITEM_ID ,
1205 c_rec.EXPECTED_RECEIPT_DATE ,
1206 c_rec.COMMENTS ,
1207 c_rec.WAYBILL_AIRBILL_NUM ,
1208 c_rec.BARCODE_LABEL ,
1209 c_rec.BILL_OF_LADING ,
1210 c_rec.CONTAINER_NUM ,
1211 c_rec.COUNTRY_OF_ORIGIN_CODE ,
1212 c_rec.VENDOR_CUM_SHIPPED_QTY ,
1213 c_rec.FREIGHT_CARRIER_CODE ,
1214 c_rec.VENDOR_LOT_NUM ,
1215 c_rec.TRUCK_NUM ,
1216 c_rec.NUM_OF_CONTAINERS ,
1217 c_rec.PACKING_SLIP ,
1218 c_rec.REASON_ID ,
1219 c_rec.ACTUAL_COST ,
1220 c_rec.TRANSFER_COST ,
1221 c_rec.TRANSPORTATION_COST ,
1222 c_rec.RMA_REFERENCE ,
1223 c_rec.VALIDATION_FLAG ,
1224 c_rec.WIP_ENTITY_ID ,
1225 c_rec.WIP_LINE_ID ,
1226 c_rec.WIP_OPERATION_SEQ_NUM ,
1227 c_rec.PO_DISTRIBUTION_ID ,
1228 c_rec.QUANTITY_INVOICED
1229 );
1230
1231 IF c_rec.WIP_ENTITY_ID is not null THEN
1232
1233 select deliver_to_location_id
1234 into x_deliver_to_location_id
1235 from po_distributions_all
1236 where po_distribution_id = c_rec.PO_DISTRIBUTION_ID;
1237
1238 update rcv_transactions_interface
1239 set deliver_to_location_id = x_deliver_to_location_id
1240 where INTERFACE_TRANSACTION_ID = l_line_id;
1241
1242 IF x_deliver_to_location_id is null THEN
1243
1244 update rcv_transactions_interface
1245 set deliver_to_location_id =
1246 (select ship_to_location_id
1247 from po_line_locations_all
1248 where line_location_id = c_rec.PO_LINE_LOCATION_ID)
1249 where INTERFACE_TRANSACTION_ID = l_line_id;
1250
1251 END IF;
1252
1253 END IF;
1254
1255 update pos_asn_shop_cart_details asnd
1256 set asnd.INTERFACE_TRANSACTION_ID = l_line_id,
1257 asnd.HEADER_INTERFACE_ID = c_rec.HEADER_ID
1258 where asnd.session_id = l_session_id and
1259 asnd.asn_line_id = c_rec.asn_line_id and
1260 asnd.asn_line_split_id = c_rec.asn_line_split_id;
1261
1262 -- Bug 1345768. Document_line_num is required by the pre-processor but is
1263 -- not available from pos_asn_shop_cart_details.
1264 update rcv_transactions_interface
1265 set DOCUMENT_LINE_NUM =
1266 (select LINE_NUM
1267 from po_lines_all
1268 where po_line_id = c_rec.PO_LINE_ID)
1269 where INTERFACE_TRANSACTION_ID = l_line_id;
1270
1271
1272 update rcv_transactions_interface
1273 set DOCUMENT_SHIPMENT_LINE_NUM =
1274 (select SHIPMENT_NUM
1275 from po_line_locations_all
1276 where line_location_id = c_rec.PO_LINE_LOCATION_ID)
1277 where INTERFACE_TRANSACTION_ID = l_line_id;
1278
1279 update rcv_transactions_interface
1280 set po_release_id =
1281 (select po_release_id
1282 from po_line_locations_all
1283 where line_location_id = c_rec.PO_LINE_LOCATION_ID)
1284 where INTERFACE_TRANSACTION_ID = l_line_id;
1285
1286 -- Dest subinventory is required by the pre-processor but is
1287 -- not available from pos_asn_shop_cart_details.
1288 update rcv_transactions_interface
1289 set SUBINVENTORY =
1290 (select destination_subinventory
1291 from po_distributions_all
1292 where po_distribution_id = c_rec.PO_DISTRIBUTION_ID)
1293 where INTERFACE_TRANSACTION_ID = l_line_id;
1294 END LOOP;
1295
1296 END create_rcv_transaction;
1297
1298 PROCEDURE call_wip_api IS
1299 BEGIN
1300
1301 FOR c_rec in c_wip LOOP
1302 /* the wip workflow needs to be called only for wip jobs */
1303 IF c_rec.p_wip_entity_id is null THEN
1304 null;
1305 ELSE
1306 wip_osp_shp_i_wf.StartWFProcToAnotherSupplier
1307 ( c_rec.p_po_distribution_id ,
1308 c_rec.p_shipped_qty ,
1309 c_rec.p_shipped_uom ,
1310 c_rec.p_shipped_date ,
1311 c_rec.p_expected_receipt_date ,
1312 c_rec.p_packing_slip ,
1313 c_rec.p_airbill_waybill ,
1314 c_rec.p_bill_of_lading ,
1315 c_rec.p_packaging_code ,
1316 c_rec.p_num_of_container ,
1317 c_rec.p_gross_weight ,
1318 c_rec.p_gross_weight_uom ,
1319 c_rec.p_net_weight ,
1320 c_rec.p_net_weight_uom ,
1321 c_rec.p_tar_weight ,
1322 c_rec.p_tar_weight_uom ,
1323 null, /* c_rec.p_hazard_class */
1324 null, /* c_rec.p_hazard_code */
1325 null, /* c_rec.p_hazard_desc */
1326 c_rec.p_special_handling_code ,
1327 c_rec.p_freight_carrier ,
1328 c_rec.p_freight_carrier_terms ,
1329 c_rec.p_carrier_equip ,
1330 c_rec.p_carrier_method ,
1331 c_rec.p_freight_bill_num ,
1332 null, /*c_rec.p_receipt_num */
1333 null /* c_rec.p_ussgl_txn_code */
1334 );
1335 END IF;
1336 END LOOP;
1337
1338 END call_wip_api;
1339
1340 FUNCTION VALID_ASN RETURN BOOLEAN IS
1341 error_count number;
1342 BEGIN
1343
1344 fnd_client_info.set_org_context(l_org_id);
1345
1346 rcv_shipment_object_sv.create_object(l_request_id);
1347
1348 select count(*) into error_count from
1349 po_interface_errors where Interface_Header_ID = l_header_id;
1350
1351 IF error_count > 0 then
1352 RETURN FALSE;
1353 END IF;
1354
1355 RETURN TRUE;
1356
1357 END valid_asn;
1358
1359 PROCEDURE submit IS
1360 BEGIN
1361
1362 but1 := fnd_message.get_string('ICX', 'ICX_POS_ASN_EDIT_NEW_BUT');
1363 but2 := fnd_message.get_string('ICX', 'ICX_POS_ASN_EDIT_EXIT_BUT');
1364 but3 := fnd_message.get_string('ICX', 'ICX_POS_ASN_EDIT_OK_BUT');
1365
1366 create_rcv_header;
1367 create_rcv_transaction;
1368
1369 IF valid_asn THEN
1370
1371 call_wip_api;
1372
1373 buyer_notify;
1374
1375 delete from pos_asn_shop_cart_headers where session_id = l_session_id;
1376 delete from pos_asn_shop_cart_details where session_id = l_session_id;
1377
1378 -- The pre-processor has already created the header record, also we do not want the
1379 -- pre-processor to be run again, so delete the header in the interface. If there is
1380 -- no header record in the interface table, the pre-processor is not run. The status
1381 -- need to be PENDING for trx procss to run.
1382 /*
1383 * dreddy - instead of deleting from the header set the processing status_code
1384 * to success so that the pre-processor does not run again. This is because the
1385 * the data in this table is needed by the receiving transaction processor.
1386 */
1387 -- delete from rcv_headers_interface where header_interface_id = l_header_id;
1388 update rcv_headers_interface set
1389 validation_flag = 'N',
1390 processing_status_code = 'SUCCESS'
1391 where header_interface_id = l_header_id;
1392
1393 update rcv_transactions_interface set
1394 PROCESSING_STATUS_CODE = 'PENDING',
1395 TRANSACTION_STATUS_CODE = 'PENDING'
1396 where header_interface_id = l_header_id;
1397
1398 COMMIT;
1399
1400 show_ok_page;
1401 sub_state := 'OK';
1402
1403 ELSE
1404
1405 show_error_page;
1406 sub_state := 'ERROR';
1407
1408 ROLLBACK;
1409
1410 END IF;
1411
1412 END submit;
1413
1414 PROCEDURE update_shipments(pos_quantity_shipped IN t_text_table DEFAULT g_dummy,
1415 pos_select IN t_text_table DEFAULT g_dummy,
1416 pos_unit_of_measure IN t_text_table DEFAULT g_dummy,
1417 pos_comments IN t_text_table DEFAULT g_dummy,
1418 pos_asn_line_id IN t_text_table DEFAULT g_dummy,
1419 pos_asn_line_split_id IN t_text_table DEFAULT g_dummy,
1420 pos_po_line_location_id IN t_text_table DEFAULT g_dummy,
1421 pos_po_distribution_id IN t_text_table DEFAULT g_dummy,
1422 pos_asn_wip_job IN t_text_table DEFAULT g_dummy,
1423 pos_wip_entity_id IN t_text_table DEFAULT g_dummy,
1424 pos_wip_line_id IN t_text_table DEFAULT g_dummy,
1425 pos_wip_operation_seq_num IN t_text_table DEFAULT g_dummy,
1426 pos_item_id IN t_text_table DEFAULT g_dummy,
1427 pos_uom_class IN t_text_table DEFAULT g_dummy,
1428 pos_po_header_id IN t_text_table DEFAULT g_dummy,
1429 pos_submit IN VARCHAR2 DEFAULT NULL) IS
1430 d_count number;
1431 BEGIN
1432
1433 -- Update all the info ---
1434
1435 FOR l_counter IN 1..pos_asn_line_id.count LOOP
1436
1437 update pos_asn_shop_cart_details set
1438 quantity_shipped = fnd_number.canonical_to_number(nvl(rtrim(ltrim(pos_quantity_shipped(l_counter))), 0)),
1439 unit_of_measure = pos_unit_of_measure(l_counter),
1440 comments = pos_comments(l_counter),
1441 wip_job_info = pos_osp_job.get_wip_info(pos_po_distribution_id(l_counter)),
1442 po_distribution_id = pos_po_distribution_id(l_counter),
1443 wip_entity_id = pos_wip_entity_id(l_counter),
1444 wip_operation_seq_num = pos_wip_operation_seq_num(l_counter),
1445 wip_line_id = pos_wip_line_id(l_counter),
1446 item_id = nvl(pos_item_id(l_counter), item_id)
1447 where session_id = l_session_id and
1448 asn_line_id = pos_asn_line_id(l_counter) and
1449 asn_line_split_id = pos_asn_line_split_id(l_counter);
1450
1451 END LOOP;
1452
1453 -- Delete if any --
1454
1455 IF pos_submit = 'DELETE' AND pos_select.count > 0 THEN
1456
1457 FOR l_counter IN 1..pos_select.count LOOP
1458
1459 delete from pos_asn_shop_cart_details
1460 where session_id = l_session_id and
1461 asn_line_id = pos_asn_line_id(to_number(pos_select(l_counter))) and
1462 asn_line_split_id = pos_asn_line_split_id(to_number(pos_select(l_counter)));
1463
1464 END LOOP;
1465
1466 select count(*)
1467 into d_count
1468 from pos_asn_shop_cart_details
1469 where session_id = l_session_id;
1470
1471 IF d_count = 0 THEN
1472 delete from pos_asn_shop_cart_headers where session_id = l_session_id;
1473 END IF;
1474
1475 END IF;
1476
1477 -- Split the one --
1478
1479 IF pos_submit = 'EXPLODE' AND pos_select.count > 0 THEN
1480
1481 FOR l_counter IN 1..pos_select.count LOOP
1482
1483 update pos_asn_shop_cart_details set
1484 asn_line_split_id = asn_line_split_id + 1
1485 where session_id = l_session_id and
1486 asn_line_id = pos_asn_line_id(to_number(pos_select(l_counter))) and
1487 asn_line_split_id > pos_asn_line_split_id(to_number(pos_select(l_counter)));
1488
1489
1490 insert into pos_asn_shop_cart_details
1491 (session_id,
1492 asn_line_id,
1493 asn_line_split_id,
1494 po_header_id,
1495 po_line_id,
1496 po_line_location_id,
1497 ship_to_organization_id,
1498 last_update_date,
1499 last_updated_by,
1500 unit_of_measure,
1501 item_id )
1502 select
1503 session_id,
1504 asn_line_id,
1505 asn_line_split_id + 1,
1506 po_header_id,
1507 po_line_id,
1508 po_line_location_id,
1509 ship_to_organization_id,
1510 last_update_date,
1511 last_updated_by,
1512 unit_of_measure,
1513 item_id
1514 from pos_asn_shop_cart_details
1515 where session_id = l_session_id and
1516 asn_line_id = pos_asn_line_id(to_number(pos_select(l_counter))) and
1517 asn_line_split_id = pos_asn_line_split_id(to_number(pos_select(l_counter)));
1518
1519 END LOOP;
1520
1521 END IF;
1522
1523 COMMIT;
1524
1525 IF pos_submit = 'SUBMIT' THEN
1526
1527 submit;
1528
1529 END IF;
1530
1531 IF pos_submit = 'NEXT' THEN
1532
1533 -- Show review page
1534 pos_asn_review_pkg.review_page;
1535
1536 ELSIF substr(pos_submit, 1, 4) = 'BACK' THEN
1537
1538 -- Show search page
1539 pos_asn_search_pkg.search_page(p_query => 'N',
1540 p_start_row => to_number(substr(pos_submit, 5,
1541 length(pos_submit)-4))
1542 );
1543 ELSE
1544
1545 -- Repaint
1546 show_edit_shipments;
1547
1548 END IF;
1549
1550 END update_shipments;
1551
1552 PROCEDURE update_header ( pos_asn_shipment_num IN VARCHAR2 DEFAULT null,
1553 pos_bill_of_lading IN VARCHAR2 DEFAULT null,
1554 pos_waybill_airbill_num IN VARCHAR2 DEFAULT null,
1555 pos_ship_date IN VARCHAR2 DEFAULT null,
1556 pos_expected_receipt_date IN VARCHAR2 DEFAULT null,
1557 pos_num_of_containers IN VARCHAR2 DEFAULT null,
1558 pos_comments IN VARCHAR2 DEFAULT null,
1559 pos_packing_slip IN VARCHAR2 DEFAULT null,
1560 pos_freight_carrier IN VARCHAR2 DEFAULT null,
1561 pos_freight_carrier_code IN VARCHAR2 DEFAULT null,
1562 pos_freight_term IN VARCHAR2 DEFAULT null,
1563 pos_freight_term_code IN VARCHAR2 DEFAULT null,
1564 pos_freight_bill_num IN VARCHAR2 DEFAULT null,
1565 pos_carrier_method IN VARCHAR2 DEFAULT null,
1566 pos_carrier_equipment IN VARCHAR2 DEFAULT null,
1567 pos_gross_weight IN VARCHAR2 DEFAULT null,
1568 pos_gross_weight_uom IN VARCHAR2 DEFAULT null,
1569 pos_gross_weight_uom_code IN VARCHAR2 DEFAULT null,
1570 pos_net_weight IN VARCHAR2 DEFAULT null,
1571 pos_net_weight_uom IN VARCHAR2 DEFAULT null,
1572 pos_net_weight_uom_code IN VARCHAR2 DEFAULT null,
1573 pos_tar_weight IN VARCHAR2 DEFAULT null,
1574 pos_tar_weight_uom IN VARCHAR2 DEFAULT null,
1575 pos_tar_weight_uom_code IN VARCHAR2 DEFAULT null,
1576 pos_packaging_code IN VARCHAR2 DEFAULT null,
1577 pos_special_handling_code IN VARCHAR2 DEFAULT null,
1578 pos_ship_to_organization_id IN VARCHAR2 DEFAULT null ) IS
1579 l_ship_date varchar2(200);
1580 l_receipt_date varchar2(200);
1581 BEGIN
1582
1583 sub_state := 'SUBMIT';
1584
1585 -- Need to validate ship date, so as not to fire pre-processor if there is error,
1586 -- prevent submit of shipments forms
1587 begin
1588 x_ship_date := pos_ship_date;
1589 l_ship_date := to_date(pos_ship_date, l_date_format);
1590 EXCEPTION
1591 WHEN OTHERS THEN
1592 sub_state := 'ERROR';
1593 header_error := true;
1594 ship_date_error := true;
1595 end;
1596
1597 -- Need to validate expected receipt date,so as not to fire pre-processor
1598 -- if there is error, prevent submit of shipments forms
1599 begin
1600 x_receipt_date := pos_expected_receipt_date;
1601 l_receipt_date := to_date(pos_expected_receipt_date, l_date_format);
1602 EXCEPTION
1603 WHEN OTHERS THEN
1604 sub_state := 'ERROR';
1605 header_error := true;
1606 receipt_date_error := true;
1607 end;
1608
1609 update pos_asn_shop_cart_headers set
1610 asn_type = 'NEW',
1611 shipment_num = pos_asn_shipment_num,
1612 bill_of_lading = pos_bill_of_lading,
1613 waybill_airbill_num = pos_waybill_airbill_num,
1614 ship_date = l_ship_date,
1615 expected_receipt_date = l_receipt_date,
1616 num_of_containers = fnd_number.canonical_to_number(rtrim(ltrim(pos_num_of_containers))),
1617 comments = pos_comments,
1618 packing_slip = pos_packing_slip,
1619 freight_carrier_code = pos_freight_carrier_code,
1620 freight_terms = pos_freight_term_code,
1621 freight_bill_number = pos_freight_bill_num,
1622 carrier_method = pos_carrier_method,
1623 carrier_equipment = pos_carrier_equipment,
1624 gross_weight = fnd_number.canonical_to_number(rtrim(ltrim(pos_gross_weight))),
1625 gross_weight_uom_code = pos_gross_weight_uom_code,
1626 net_weight = fnd_number.canonical_to_number(rtrim(ltrim(pos_net_weight))),
1627 net_weight_uom_code = pos_net_weight_uom_code,
1628 tar_weight = fnd_number.canonical_to_number(rtrim(ltrim(pos_tar_weight))),
1629 tar_weight_uom_code = pos_tar_weight_uom_code,
1630 packaging_code = pos_packaging_code,
1631 special_handling_code = pos_special_handling_code
1632 where session_id = l_session_id;
1633
1634 COMMIT;
1635
1636 IF sub_state = 'ERROR' then
1637 show_error_page;
1638 END IF;
1639 show_edit_header;
1640
1641 END update_header;
1642
1643 function get_result_value(p_index in number, p_col in number) return varchar2 is
1644 sql_statement VARCHAR2(300);
1645 l_cursor INTEGER;
1646 l_execute INTEGER;
1647 l_result VARCHAR2(2000);
1648 BEGIN
1649
1650 IF ak_query_pkg.g_results_table.count > 0 THEN
1651
1652 sql_statement := 'begin ' ||
1653 ':l_result := ak_query_pkg.g_results_table(:p_index).value' ||
1654 to_char(p_col) || '; ' ||
1655 ' end;';
1656
1657 l_cursor := dbms_sql.open_cursor;
1658 dbms_sql.parse(l_cursor, sql_statement, dbms_sql.v7);
1659 dbms_sql.bind_variable(l_cursor, 'l_result', l_result, 2000);
1660 dbms_sql.bind_variable(l_cursor, 'p_index', p_index);
1661
1662 l_execute := dbms_sql.execute(l_cursor);
1663 dbms_sql.variable_value(l_cursor, 'l_result', l_result);
1664 dbms_sql.close_cursor(l_cursor);
1665 return l_result;
1666
1667 ELSE
1668
1669 return null;
1670
1671 END IF;
1672
1673 END get_result_value;
1674
1675
1676 /* Initialize the session info only once per session */
1677 BEGIN
1678
1679 IF NOT set_session_info THEN
1680 RETURN;
1681 END IF;
1682
1683 END pos_asn;