[Home] [Help]
PACKAGE BODY: APPS.POS_ASN_SEARCH_PKG
Source
1 PACKAGE BODY pos_asn_search_pkg AS
2 /* $Header: POSASNSB.pls 115.8 2001/03/30 17:37:02 pkm ship $ */
3
4 TYPE t_attribute_record IS RECORD (
5 attribute_name VARCHAR2(30),
6 attribute_value VARCHAR2(1000)
7 );
8
9 TYPE t_attribute_table IS TABLE OF t_attribute_record INDEX BY BINARY_INTEGER;
10
11 g_attribute_table t_attribute_table;
12
13 FUNCTION GetSupplierID(p_user_id IN NUMBER) RETURN NUMBER;
14 FUNCTION GetSupplierSiteID(p_user_id IN NUMBER) RETURN NUMBER;
15 PROCEDURE ShowBasic( pos_vendor_site_id IN VARCHAR2 DEFAULT NULL,
16 pos_vendor_site_name IN VARCHAR2 DEFAULT NULL,
17 pos_ship_to_location_id IN VARCHAR2 DEFAULT NULL,
18 pos_ship_to_location IN VARCHAR2 DEFAULT NULL,
19 pos_supplier_item_number IN VARCHAR2 DEFAULT NULL,
20 pos_item_description IN VARCHAR2 DEFAULT NULL,
21 pos_po_number IN VARCHAR2 DEFAULT NULL,
22 pos_item_number IN VARCHAR2 DEFAULT NULL,
23 pos_date_start IN VARCHAR2 DEFAULT NULL,
24 pos_date_end IN VARCHAR2 DEFAULT NULL,
25 p_advance_flag IN VARCHAR2 DEFAULT 'N');
26 PROCEDURE ShowAdvanced;
27 PROCEDURE ShowResult(p_start_row IN NUMBER,
28 p_msg IN VARCHAR2 DEFAULT NULL);
29
30 PROCEDURE PrintResultHeadings;
31 PROCEDURE PrintAvailableShipment(p_result_index NUMBER,
32 p_current_row NUMBER);
33 PROCEDURE PrintSelectedShipment(p_result_index NUMBER,
34 p_current_row NUMBER);
35
36 procedure button(src IN varchar2,
37 txt IN varchar2);
38
39 function get_result_value(p_index in number, p_col in number) return varchar2;
40
41
42 PROCEDURE SetAttributeTable(pos_vendor_site_id IN VARCHAR2 DEFAULT NULL,
43 pos_vendor_site_name IN VARCHAR2 DEFAULT NULL,
44 pos_ship_to_location_id IN VARCHAR2 DEFAULT NULL,
45 pos_ship_to_location IN VARCHAR2 DEFAULT NULL,
46 pos_supplier_item_number IN VARCHAR2 DEFAULT NULL,
47 pos_item_description IN VARCHAR2 DEFAULT NULL,
48 pos_po_number IN VARCHAR2 DEFAULT NULL,
49 pos_item_number IN VARCHAR2 DEFAULT NULL,
50 pos_date_start IN VARCHAR2 DEFAULT NULL,
51 pos_date_end IN VARCHAR2 DEFAULT NULL);
52
53 FUNCTION GetAttributeValue(p_attribute_name IN VARCHAR2,
54 p_start_index IN NUMBER) RETURN VARCHAR2;
55
56 function GetRequiredFlag(p_attribute_code IN VARCHAR2) return varchar2;
57
58 PROCEDURE UpdateResultSet(p_where_clause IN VARCHAR2 DEFAULT NULL,
59 p_session_id IN NUMBER);
60
61
62 -- Body
63
64 PROCEDURE search_page(p_query IN VARCHAR2 DEFAULT 'N',
65 p_msg IN VARCHAR2 DEFAULT NULL,
66 p_start_row IN NUMBER DEFAULT 0)
67 IS
68
69 l_language VARCHAR2(5);
70 l_script_name VARCHAR2(240);
71 l_org_id NUMBER;
72 l_user_id NUMBER;
73 l_session_id NUMBER;
74
75 BEGIN
76
77 IF NOT icx_sec.validatesession THEN
78 RETURN;
79 END IF;
80
81 l_org_id := icx_sec.getID(icx_sec.PV_ORG_ID);
82 l_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
83 l_script_name := owa_util.get_cgi_env('SCRIPT_NAME');
84 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
85 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
86
87
88 if p_start_row = 0 then
89
90 delete pos_asn_search_result where session_id = l_session_id;
91 commit;
92
93 end if;
94
95 htp.htmlOpen;
96 htp.headOpen;
97 icx_util.copyright;
98 htp.linkRel('STYLESHEET', '/OA_HTML/' || l_language || '/POSSTYLE.css');
99 js.scriptOpen;
100 icx_util.LOVscript;
101 js.scriptClose;
102 htp.p('<script src="/OA_HTML/POSASNSR.js" language="JavaScript"></script>');
103 htp.headClose;
104
105 htp.p('
106
107 <FRAMESET ROWS="195, 32, *, 38" BORDER=0>
108
109 <FRAME SRC="' || l_script_name || '/POS_ASN_SEARCH_PKG.CRITERIA_FRAME"
110 NAME="criteria" MARGINWIDTH="0" MARGINHEIGHT="0" NORESIZE FRAMEBORDER=NO>
111
112 <FRAME SRC="' || l_script_name || '/POS_ASN_SEARCH_PKG.COUNTER_FRAME"
113 NAME="counter" MARGINWIDTH="0" MARGINHEIGHT="0" SCROLLING=NO NORESIZE FRAMEBORDER=NO>
114
115 <FRAME SRC="' || l_script_name || '/POS_ASN_SEARCH_PKG.RESULT_FRAME?p_query=' ||
116 p_query || '`&p_msg=' || p_msg || '`&p_start_row=' || p_start_row || '"
117 NAME="result" MARGINWIDTH="5" MARGINHEIGHT="0" NORESIZE FRAMEBORDER=NO>
118
119 <FRAME SRC="' || l_script_name || '/POS_ASN_SEARCH_PKG.ADD_FRAME"
120 NAME="add" MARGINWIDTH="5" MARGINHEIGHT="10" SCROLLING=NO NORESIZE FRAMEBORDER=NO>
121
122 </FRAMESET>
123
124 ');
125
126 htp.htmlClose;
127
128 END search_page;
129
130 PROCEDURE criteria_frame(pos_vendor_site_id IN VARCHAR2 DEFAULT NULL,
131 pos_vendor_site_name IN VARCHAR2 DEFAULT NULL,
132 pos_ship_to_location_id IN VARCHAR2 DEFAULT NULL,
133 pos_ship_to_location IN VARCHAR2 DEFAULT NULL,
134 pos_supplier_item_number IN VARCHAR2 DEFAULT NULL,
135 pos_item_description IN VARCHAR2 DEFAULT NULL,
136 pos_po_number IN VARCHAR2 DEFAULT NULL,
137 pos_item_number IN VARCHAR2 DEFAULT NULL,
138 pos_date_start IN VARCHAR2 DEFAULT NULL,
139 pos_date_end IN VARCHAR2 DEFAULT NULL,
140 p_advance_flag IN VARCHAR2 DEFAULT 'N'
141 ) IS
142
143 l_language VARCHAR2(5);
144 l_script_name VARCHAR2(240);
145 l_org_id NUMBER;
146 l_user_id NUMBER;
147 l_session_id NUMBER;
148 l_responsibility_id NUMBER;
149
150 l_empty_cart VARCHAR2(1);
151
152 l_supplier_site_id NUMBER;
153 l_supplier_site VARCHAR2(15);
154 l_ship_to_loc_id NUMBER;
155 l_ship_to_location VARCHAR2(20);
156
157 BEGIN
158
159 IF NOT icx_sec.validatesession THEN
160 RETURN;
161 END IF;
162
163 l_org_id := icx_sec.getID(icx_sec.PV_ORG_ID);
164 l_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
165 l_script_name := owa_util.get_cgi_env('SCRIPT_NAME');
166 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
167 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
168 l_responsibility_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
169
170 htp.htmlOpen;
171 htp.headOpen;
172 htp.linkRel('STYLESHEET', '/OA_HTML/' || l_language || '/POSSTYLE.css');
173 js.scriptOpen;
174 icx_util.LOVscript;
175 js.scriptClose;
176 htp.p('<script src="/OA_HTML/POSASNSR.js" language="JavaScript"></script>');
177 htp.headClose;
178
179 -- Check if the shopping cart is empty
180
181 select decode(count(1), 0, 'Y', 'N')
182 into l_empty_cart
183 from POS_ASN_SHOP_CART_DETAILS
184 where session_id = l_session_id;
185
186 if l_empty_cart = 'N' then
187
188 -- If the shopping cart is not empty, default the fixed information
189 -- (supplier site and ship to location) to the criteria fields.
190
191 select ct.vendor_site_id,
192 vs.vendor_site_code,
193 ct.ship_to_location_id,
194 hrl.location_code
195 into l_supplier_site_id,
196 l_supplier_site,
197 l_ship_to_loc_id,
198 l_ship_to_location
199 from POS_ASN_SHOP_CART_HEADERS ct,
200 PO_VENDOR_SITES vs,
201 HR_LOCATIONS hrl
202 where ct.session_id = l_session_id
203 and ct.vendor_site_id = vs.vendor_site_id
204 and ct.SHIP_TO_LOCATION_ID = hrl.LOCATION_ID;
205
206 else
207
208 -- Default the supplier site info if the user is secured by supplier site.
209
210 begin
211 select ak.NUMBER_VALUE,
212 vs.VENDOR_SITE_CODE
213 into l_supplier_site_id,
214 l_supplier_site
215 from AK_WEB_USER_SEC_ATTR_VALUES ak,
216 PO_VENDOR_SITES vs
217 where ATTRIBUTE_CODE = 'ICX_SUPPLIER_SITE_ID'
218 and ak.NUMBER_VALUE = vs.VENDOR_SITE_ID
219 and WEB_USER_ID = l_user_id
220 and exists (select 1
221 from ak_resp_security_attributes
222 where attribute_code = 'ICX_SUPPLIER_SITE_ID'
223 and responsibility_id = l_responsibility_id);
224
225 exception
226 when others then
227 l_supplier_site_id := NULL;
228 l_supplier_site := NULL;
229 end;
230
231 end if;
232
233 SetAttributeTable(l_supplier_site_id,
234 l_supplier_site,
235 l_ship_to_loc_id,
236 l_ship_to_location,
237 pos_supplier_item_number,
238 pos_item_description,
239 pos_po_number,
240 pos_item_number,
241 pos_date_start,
242 pos_date_end);
243
244 htp.p('<body bgcolor=#cccccc onLoad="javascript:setCriteria(''' || l_empty_cart|| ''', ''' || p_advance_flag || ''')">');
245
246 htp.p('<table width=100% bgcolor=#CCCCCC cellpadding=0 cellspacing=0 border=0>');
247
248 htp.p('<TR><TD VALIGN=MIDDLE ALIGN=LEFT BGCOLOR=#CCCCCC NOWRAP><FONT CLASS=helptext>&`nbsp;' ||
249 fnd_message.get_string('ICX','ICX_POS_ASN_ENTER_CRITERIA') ||
250 '` ` ` <img src=/OA_MEDIA/FNDIREQD.gif align=top>' ||
251 fnd_message.get_string('ICX','ICX_POS_REQUIRED_FIELD') || '</FONT></TD></TR>');
252
253 htp.p('</TABLE>');
254
255 htp.p('<FORM NAME="POS_ASN_SEARCH_R" ACTION="' || l_script_name ||
256 '/POS_ASN_SEARCH_PKG.RESULT_FRAME" TARGET="result" METHOD="GET">');
257
258 htp.p('<table bgcolor=#CCCCCC cellpadding=0 cellspacing=0 border=0>');
259
260 htp.p('<tr bgcolor=#cccccc>');
261
262 ShowBasic(pos_vendor_site_id,
263 pos_vendor_site_name,
264 pos_ship_to_location_id,
265 pos_ship_to_location,
266 pos_supplier_item_number,
267 pos_item_description,
268 pos_po_number,
269 pos_item_number,
270 pos_date_start,
271 pos_date_end,
272 p_advance_flag);
273
274 if p_advance_flag = 'Y' then
275 ShowAdvanced;
276 end if;
277
278 htp.p('</TABLE>');
279 htp.p('</FORM>');
280
281 htp.bodyClose;
282 htp.htmlClose;
283
284 END criteria_frame;
285
286 PROCEDURE counter_frame(p_first IN NUMBER DEFAULT 0,
287 p_last IN NUMBER DEFAULT 0,
288 p_total IN NUMBER DEFAULT 0,
289 p_msg IN VARCHAR2 DEFAULT NULL) IS
290
291 l_language VARCHAR2(5);
292 l_script_name VARCHAR2(240);
293 l_org_id NUMBER;
294 l_user_id NUMBER;
295 l_session_id NUMBER;
296
297 l_msg VARCHAR2(200);
298
299 BEGIN
300
301 IF NOT icx_sec.validatesession THEN
302 RETURN;
303 END IF;
304
305 l_org_id := icx_sec.getID(icx_sec.PV_ORG_ID);
306 l_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
307 l_script_name := owa_util.get_cgi_env('SCRIPT_NAME');
308 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
309 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
310
311 htp.htmlOpen;
312 htp.headOpen;
313 htp.linkRel('STYLESHEET', '/OA_HTML/' || l_language || '/POSSTYLE.css');
314 htp.headClose;
315
316 htp.p('<body bgcolor=#cccccc>');
317
318 htp.p('<table width=100% bgcolor=#CCCCCC cellpadding=0 cellspacing=0 border=0>');
319
320 htp.p('<tr bgcolor=#cccccc><td colspan=2 height=5><img src=/OA_MEDIA/FNDPXG5.gif></td></tr>');
321 htp.p('<tr><td colspan=4 height=1 bgcolor=black><img src=/OA_MEDIA/FNDPX1.gif></td></tr>');
322 htp.p('<tr bgcolor=#cccccc><td colspan=2 height=3><img src=/OA_MEDIA/FNDPXG5.gif></td></tr>');
323
324 if p_total > 0 then
325
326 htp.p('<tr bgcolor=#cccccc>');
327
328 htp.p('<TD VALIGN=MIDDLE ALIGN=LEFT BGCOLOR=#CCCCCC NOWRAP><FONT CLASS=helptext>` ' ||
329 fnd_message.get_string('ICX','ICX_POS_ASN_SELECT_RESULT') || '</FONT></TD>');
330
331 l_msg := fnd_message.get_string('ICX','ICX_POS_ASN_RESULT_COUNTER');
332
333 l_msg := replace(l_msg, '`&TOTAL', to_char(p_total));
334 l_msg := replace(l_msg, '`&FROM', to_char(p_first));
335 l_msg := replace(l_msg, '`&TO', to_char(p_last));
336
337 htp.p('<TD VALIGN=MIDDLE ALIGN=RIGHT BGCOLOR=#CCCCCC NOWRAP><FONT CLASS=promptblack>' ||
338 l_msg || '` </FONT></TD></TR>');
339
340 else
341
342 htp.p('<TD VALIGN=MIDDLE ALIGN=LEFT BGCOLOR=#CCCCCC NOWRAP><FONT CLASS=datablack>` ' ||
343 p_msg || '</FONT></TD>');
344
345 end if;
346
347 htp.p('</TABLE>');
348
349 htp.bodyClose;
350
351 htp.htmlClose;
352
353 END;
354
355 PROCEDURE result_frame(pos_vendor_site_id IN VARCHAR2 DEFAULT NULL,
356 pos_vendor_site_name IN VARCHAR2 DEFAULT NULL,
357 pos_ship_to_location_id IN VARCHAR2 DEFAULT NULL,
358 pos_ship_to_location IN VARCHAR2 DEFAULT NULL,
359 pos_supplier_item_number IN VARCHAR2 DEFAULT NULL,
360 pos_item_description IN VARCHAR2 DEFAULT NULL,
361 pos_po_number IN VARCHAR2 DEFAULT NULL,
362 pos_item_number IN VARCHAR2 DEFAULT NULL,
363 pos_date_start IN VARCHAR2 DEFAULT NULL,
364 pos_date_end IN VARCHAR2 DEFAULT NULL,
365 p_query IN VARCHAR2 DEFAULT 'Y',
366 p_msg IN VARCHAR2 DEFAULT NULL,
367 p_start_row IN NUMBER DEFAULT 1
368 ) IS
369
370 l_language VARCHAR2(5);
371 l_script_name VARCHAR2(240);
372 l_org_id NUMBER;
373 l_user_id NUMBER;
374 l_session_id NUMBER;
375
376 l_attribute_index NUMBER;
377 l_result_index NUMBER;
378
379 l_where_clause VARCHAR2(2000) := '1 = 1 ';
380
381 l_start_date DATE;
382 l_end_date DATE;
383
384 l_rows_returned NUMBER := 0;
385
386 l_progress VARCHAR2(1000) := '000';
387
388 l_supplier_attr NUMBER := NULL;
389 l_supplier_site_attr NUMBER := NULL;
390
391 l_required_flag VARCHAR2(1);
392 l_label VARCHAR2(50);
393
394 l_format_mask icx_sessions.DATE_FORMAT_MASK%TYPE;
395
396 BEGIN
397
398 IF NOT icx_sec.validatesession THEN
399 RETURN;
400 END IF;
401
402 l_org_id := icx_sec.getID(icx_sec.PV_ORG_ID);
403 l_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
404 l_script_name := owa_util.get_cgi_env('SCRIPT_NAME');
405 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
406 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
407
408 fnd_client_info.set_org_context(l_org_id);
409
410 IF p_query = 'N' THEN
411 ShowResult(p_start_row, p_msg);
412 return;
413 END IF;
414
415 -- Search Criteria of Supplier Site
416
417 l_required_flag := GetRequiredFlag('POS_VENDOR_SITE_NAME');
418
419 IF pos_vendor_site_name IS NULL and l_required_flag = 'Y' THEN
420 ShowResult(p_start_row, fnd_message.get_string('ICX','ICX_POS_ASN_MISSING_FIELD'));
421 return;
422 ELSIF pos_vendor_site_id IS NOT NULL THEN
423 l_where_clause := l_where_clause || ' and supplier_site_id = ' || pos_vendor_site_id;
424 ELSIF pos_vendor_site_name IS NOT NULL THEN
425 l_where_clause := l_where_clause || ' and supplier_site_code = ''' || pos_vendor_site_name || '''';
426 END IF;
427
428 -- Search Criteria for Location
429
430 l_required_flag := GetRequiredFlag('POS_SHIP_TO_LOCATION');
431
432 IF pos_ship_to_location IS NULL and l_required_flag = 'Y' THEN
433 ShowResult(p_start_row, fnd_message.get_string('ICX','ICX_POS_ASN_MISSING_FIELD'));
434 return;
435 ELSIF pos_ship_to_location_id IS NOT NULL THEN
436 l_where_clause := l_where_clause || ' and ship_to_location_id = ' || pos_ship_to_location_id;
437 ELSIF pos_ship_to_location IS NOT NULL THEN
438 l_where_clause := l_where_clause || ' and ship_to_location_code = ''' || pos_ship_to_location || '''';
439 END IF;
440
441 -- Search Criteria for other fields
442
443 l_required_flag := GetRequiredFlag('POS_SUPPLIER_ITEM_NUMBER');
444
445 IF pos_supplier_item_number IS NULL and l_required_flag = 'Y' THEN
446 ShowResult(p_start_row, fnd_message.get_string('ICX','ICX_POS_ASN_MISSING_FIELD'));
447 return;
448 ELSIF pos_supplier_item_number is not null then
449 l_where_clause := l_where_clause || ' and supplier_item_number like ''' || pos_supplier_item_number || '''';
450 end if;
451
452 l_required_flag := GetRequiredFlag('POS_ITEM_DESCRIPTION');
453
454 IF pos_item_description IS NULL and l_required_flag = 'Y' THEN
455 ShowResult(p_start_row, fnd_message.get_string('ICX','ICX_POS_ASN_MISSING_FIELD'));
456 return;
457 ELSIF pos_item_description is not null then
458 l_where_clause := l_where_clause || ' and item_description like ''' || pos_item_description || '''';
459 end if;
460
461 l_required_flag := GetRequiredFlag('POS_PO_NUMBER');
462
463 -- Bug# 1696725. Changed the po_number to po_num_search
464 -- while building l_where_clause to improve performance.
465
466 IF pos_po_number IS NULL and l_required_flag = 'Y' THEN
467 ShowResult(p_start_row, fnd_message.get_string('ICX','ICX_POS_ASN_MISSING_FIELD'));
468 return;
469 ELSIF pos_po_number is not null then
470 l_where_clause := l_where_clause || ' and po_num_search like ''' || pos_po_number || '''';
471 end if;
472
473 -- Search Criteria for advance field
474
475 IF pos_item_number is not null then
476 l_where_clause := l_where_clause || ' and item_number like ''' || pos_item_number || '''';
477 end if;
478
479 IF pos_date_start is not null or pos_date_end is not null then
480
481 -- Bug 1196968
482
483 select date_format_mask
484 into l_format_mask
485 from icx_sessions
486 where session_id = l_session_id;
487
488 fnd_date.initialize(l_format_mask);
489
490 if pos_date_start is null then
491
492 l_start_date := sysdate;
493
494 else
495
496 l_start_date := fnd_date.chardate_to_date(pos_date_start);
497
498 end if;
499
500 if pos_date_end is null then
501
502 l_end_date := sysdate;
503
504 else
505
506 l_end_date := fnd_date.chardate_to_date(pos_date_end);
507
508 end if;
509
510 l_where_clause := l_where_clause || ' and due_date between ''' ||
511 fnd_date.date_to_chardate(l_start_date) ||
512 ''' and ''' ||
513 fnd_date.date_to_chardate(l_end_date) || '''';
514
515 end if;
516
517 UpdateResultSet(l_where_clause, l_session_id);
518
519 ShowResult(p_start_row);
520
521 EXCEPTION
522
523 when others then
524
525 delete pos_asn_search_result where session_id = l_session_id;
526 commit;
527
528 ShowResult(p_start_row, fnd_message.get_string('ICX','ICX_POS_NO_RECORDS'));
529
530 END result_frame;
531
532 procedure add_frame IS
533
534 l_language VARCHAR2(5);
535 l_script_name VARCHAR2(240);
536 l_org_id NUMBER;
537 l_user_id NUMBER;
538 l_session_id NUMBER;
539
540 l_attribute_index NUMBER;
541 l_result_index NUMBER;
542
543 l_where_clause VARCHAR2(2000);
544
545 l_num_shipments NUMBER;
546 l_num_results NUMBER;
547
548 BEGIN
549
550 IF NOT icx_sec.validatesession THEN
551 RETURN;
552 END IF;
553
554 l_org_id := icx_sec.getID(icx_sec.PV_ORG_ID);
555 l_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
556 l_script_name := owa_util.get_cgi_env('SCRIPT_NAME');
557 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
558 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
559
560 l_where_clause := 'SESSION_ID = ' || to_char(l_session_id);
561
562 fnd_client_info.set_org_context(l_org_id);
563
564 htp.htmlOpen;
565 htp.headOpen;
566 htp.linkRel('STYLESHEET', '/OA_HTML/' || l_language || '/POSSTYLE.css');
567 js.scriptOpen;
568 icx_util.LOVscript;
569 js.scriptClose;
570 htp.p('<script src="/OA_HTML/POSASNSR.js" language="JavaScript"></script>');
571 htp.headClose;
572
573 htp.p('<body bgcolor=#cccccc>');
574
575 select count(1)
576 into l_num_shipments
577 from pos_asn_shop_cart_details
578 where session_id = l_session_id;
579
580 ak_query_pkg.exec_query(p_parent_region_appl_id=>178,
581 p_parent_region_code=>'POS_ASN_RESULT_R',
582 p_where_clause=>l_where_clause,
583 p_responsibility_id=>icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
584 p_user_id=>icx_sec.getID(icx_sec.PV_WEB_USER_ID),
585 p_return_parents=>'T',
586 p_return_children=>'F');
587
588 l_num_results := ak_query_pkg.g_results_table.count;
589
590 htp.p('<table width=100% cellpadding=0 cellspacing=0 border=0>');
591 htp.p('<TR><TD>');
592
593 if l_num_results > 0 then
594
595 button('javascript:parent.result.document.POS_ASN_RESULT_R.submit()',
596 fnd_message.get_string('ICX','ICX_POS_ASN_ADD'));
597
598 end if;
599
600 htp.p('</TD><TD align=right><FONT class=promptblack>');
601
602 if l_num_shipments > 0 then
603 htp.p('Total PO shipments added: ' || to_char(l_num_shipments));
604 end if;
605
606 htp.p('</FONT></TD>');
607 htp.p('</TABLE>');
608
609 -- debug
610 htp.p('<!-- ' || to_char(l_session_id) || '-->');
611
612 htp.bodyClose;
613
614 htp.htmlClose;
615
616 END add_frame;
617
618 PROCEDURE add_shipments_to_cart(pos_po_shipment_id IN t_text_table DEFAULT g_dummy,
619 pos_select IN t_text_table DEFAULT g_dummy,
620 pos_start_row IN VARCHAR2 DEFAULT '1',
621 pos_submit IN VARCHAR2 DEFAULT 'STAY') IS
622
623 l_language VARCHAR2(5);
624 l_script_name VARCHAR2(240);
625 l_org_id NUMBER;
626 l_user_id NUMBER;
627 l_session_id NUMBER;
628
629 l_header_count NUMBER;
630 l_asn_line_id NUMBER;
631
632
633 l_ship_to_org_id NUMBER;
634 l_ship_to_loc_id NUMBER;
635 l_vendor_id NUMBER;
636 l_vendor_site_id NUMBER;
637
638 l_po_header_id NUMBER;
639 l_po_line_id NUMBER;
640 l_po_shipment_id NUMBER;
641
642 l_unit_meas_lookup_code VARCHAR2(25);
643
644 l_num_row NUMBER := 0;
645
646 l_first_org_id NUMBER := -1;
647 l_first_loc_id NUMBER := -1;
648
649 BEGIN
650
651 IF NOT icx_sec.validatesession THEN
652 RETURN;
653 END IF;
654
655 l_org_id := icx_sec.getID(icx_sec.PV_ORG_ID);
656 l_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
657 l_script_name := owa_util.get_cgi_env('SCRIPT_NAME');
658 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
659 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
660
661 fnd_client_info.set_org_context(l_org_id);
662
663 -- Check whether multiple ship to org are selected.
664 -- error out if yes.
665
666 begin
667 select ship_to_organization_id,
668 ship_to_location_id
669 into l_first_org_id,
670 l_first_loc_id
671 from pos_asn_shop_cart_headers
672 where session_id = l_session_id;
673 exception
674 when others then
675 null;
676 end;
677
678 FOR l_counter IN 1..pos_select.count LOOP
679
680 l_po_shipment_id := to_number(pos_po_shipment_id(to_number(pos_select(l_counter))));
681
682 select SHIP_TO_ORGANIZATION_ID,
683 ship_to_location_id
684 into l_ship_to_org_id,
685 l_ship_to_loc_id
686 from po_line_locations
687 where line_location_id = l_po_shipment_id;
688
689 if l_first_org_id = -1 then
690 l_first_org_id := l_ship_to_org_id;
691 end if;
692
693 if l_first_loc_id = -1 then
694 l_first_loc_id := l_ship_to_loc_id;
695 end if;
696
697 if l_first_org_id <> l_ship_to_org_id then
698 if pos_submit = 'NEXT' then
699 pos_asn_search_pkg.search_page('N', 'ICX_POS_ASN_DIFF_ORG', pos_start_row);
700 else
701 ShowResult(to_number(pos_start_row), 'ICX_POS_ASN_DIFF_ORG');
702 end if;
703 return;
704 end if;
705
706 if l_first_loc_id <> l_ship_to_loc_id then
707 if pos_submit = 'NEXT' then
708 pos_asn_search_pkg.search_page('N', 'ICX_POS_ASN_DIFF_LOC', pos_start_row);
709 else
710 ShowResult(to_number(pos_start_row), 'ICX_POS_ASN_DIFF_LOC');
711 end if;
712 return;
713 end if;
714
715
716 END LOOP;
717
718 -- Check if existing ASN header for this session exists.
719 -- if no, create a new ASN header.
720
721 select count(1)
722 into l_header_count
723 from pos_asn_shop_cart_headers
724 where session_id = l_session_id;
725
726 if l_header_count = 0 and pos_select.count > 0 then
727
728 l_po_shipment_id := to_number(pos_po_shipment_id(to_number(pos_select(1))));
729
730 select poll.ship_to_organization_id,
731 poll.ship_to_location_id,
732 poh.vendor_id,
733 poh.vendor_site_id
734 into l_ship_to_org_id,
735 l_ship_to_loc_id,
736 l_vendor_id,
737 l_vendor_site_id
738 from po_line_locations poll,
739 po_headers poh
740 where poh.po_header_id = poll.po_header_id
741 and poll.line_location_id = l_po_shipment_id;
742
743 insert into pos_asn_shop_cart_headers
744 (
745 SESSION_ID,
746 SHIP_TO_ORGANIZATION_ID,
747 SHIP_TO_LOCATION_ID,
748 VENDOR_ID,
749 VENDOR_SITE_ID,
750 LAST_UPDATE_DATE,
751 LAST_UPDATED_BY,
752 LAST_UPDATE_LOGIN,
753 CREATION_DATE,
754 CREATED_BY
755 )
756 values
757 (
758 l_session_id,
759 l_ship_to_org_id,
760 l_ship_to_loc_id,
761 l_vendor_id,
762 l_vendor_site_id,
763 sysdate,
764 fnd_global.user_id,
765 fnd_global.user_id,
766 sysdate,
767 fnd_global.user_id
768 );
769
770 end if;
771
772 FOR l_counter IN 1..pos_select.count LOOP
773
774 select nvl(max(asn_line_id), 0) + 1
775 into l_asn_line_id
776 from pos_asn_shop_cart_details
777 where session_id = l_session_id;
778
779 l_po_shipment_id := to_number(pos_po_shipment_id(to_number(pos_select(l_counter))));
780
781 select poll.po_header_id,
782 poll.po_line_id,
783 poll.ship_to_organization_id,
784 pol.unit_meas_lookup_code
785 into l_po_header_id,
786 l_po_line_id,
787 l_ship_to_org_id,
788 l_unit_meas_lookup_code
789 from po_line_locations poll,
790 po_lines pol
791 where poll.line_location_id = l_po_shipment_id
792 and poll.po_line_id = pol.po_line_id;
793
794 insert into pos_asn_shop_cart_details
795 (
796 SESSION_ID,
797 ASN_LINE_ID,
798 PO_LINE_LOCATION_ID,
799 PO_HEADER_ID,
800 PO_LINE_ID,
801 SHIP_TO_ORGANIZATION_ID,
802 UNIT_OF_MEASURE,
803 LAST_UPDATE_DATE,
804 LAST_UPDATED_BY,
805 LAST_UPDATE_LOGIN,
806 CREATION_DATE,
807 CREATED_BY
808 )
809 values
810 (
811 l_session_id,
812 l_asn_line_id,
813 l_po_shipment_id,
814 l_po_header_id,
815 l_po_line_id,
816 l_ship_to_org_id,
817 l_unit_meas_lookup_code,
818 sysdate,
819 fnd_global.user_id,
820 fnd_global.user_id,
821 sysdate,
822 fnd_global.user_id
823 );
824
825 l_num_row := l_num_row + 1;
826
827 END LOOP;
828
829 commit;
830
831 -- If pos_submit = 'NEXT', means go to next page.
832
833 if pos_submit = 'NEXT' then
834 pos_asn.show_edit_page;
835 else
836 ShowResult(to_number(pos_start_row));
837 end if;
838
839 end;
840
841 PROCEDURE SwitchResultPage(p_start_row IN VARCHAR2 DEFAULT '1') IS
842 BEGIN
843 ShowResult(to_number(p_start_row));
844 END SwitchResultPage;
845
846 PROCEDURE UpdateResultSet(p_where_clause IN VARCHAR2 DEFAULT NULL,
847 p_session_id IN NUMBER) IS
848
849 v_stmt VARCHAR2(2000);
850 v_cursor_id NUMBER;
851 result NUMBER;
852
853 BEGIN
854
855 delete pos_asn_search_result where session_id = p_session_id;
856
857 v_cursor_id := DBMS_SQL.open_cursor;
858
859 v_stmt :=
860 'insert into pos_asn_search_result select ' || to_char(p_session_id) || ',
861 PO_HEADER_ID,
862 PO_NUMBER,
863 PO_RELEASE_ID,
864 PO_LINE_ID,
865 LINE_NUMBER,
866 PO_SHIPMENT_ID,
867 SHIPMENT_NUMBER,
868 SHIP_TO_LOCATION_ID,
869 SHIP_TO_LOCATION_CODE,
870 SUPPLIER_ITEM_NUMBER,
871 ITEM_DESCRIPTION,
872 QUANTITY_ORDERED,
873 UNIT_OF_MEASURE_CODE,
874 DUE_DATE,
875 SUPPLIER_ID,
876 SUPPLIER_NAME,
877 SUPPLIER_SITE_ID,
878 SUPPLIER_SITE_CODE,
879 SHIP_TO_ORGANIZATION_ID,
880 SHIP_TO_ORGANIZATION_CODE,
881 SHIP_TO_ORGANIZATION_NAME,
882 ITEM_ID,
883 ITEM_NUMBER,
884 ITEM_REVISION,
885 CATEGORY_ID,
886 CATEGORY
887 from POS_ASN_PO_SHIPMENTS_V
888 where ' || p_where_clause;
889
890 DBMS_SQL.parse(v_cursor_id, v_stmt, dbms_sql.native);
891
892 result := DBMS_SQL.execute(v_cursor_id);
893
894 DBMS_SQL.close_cursor(v_cursor_id);
895
896 commit;
897
898 END UpdateResultSet;
899
900
901 --- Private function
902
903 FUNCTION GetSupplierID(p_user_id IN NUMBER) RETURN NUMBER IS
904
905 l_supplier_id NUMBER;
906
907 BEGIN
908
909 select NUMBER_VALUE
910 into l_supplier_id
911 from AK_WEB_USER_SEC_ATTR_VALUES
912 where ATTRIBUTE_CODE = 'ICX_SUPPLIER_ORG_ID'
913 and WEB_USER_ID = p_user_id;
914
915 /*
916 select vs.vendor_id
917 into l_supplier_id
918 from po_vendor_sites vs,
919 po_vendor_contacts vc,
920 fnd_user fu
921 where fu.user_id = p_user_id
922 and fu.supplier_id = vc.vendor_contact_id
923 and vc.vendor_site_id = vs.vendor_site_id;
924 */
925 return(l_supplier_id);
926
927 EXCEPTION
928 when others then
929 return(0);
930
931 END;
932
933 FUNCTION GetSupplierSiteID(p_user_id IN NUMBER) RETURN NUMBER IS
934
935 l_supplier_site_id NUMBER;
936
937 BEGIN
938
939 select NUMBER_VALUE
940 into l_supplier_site_id
941 from AK_WEB_USER_SEC_ATTR_VALUES
942 where ATTRIBUTE_CODE = 'ICX_SUPPLIER_SITE_ID'
943 and WEB_USER_ID = p_user_id;
944
945 return(l_supplier_site_id);
946
947 EXCEPTION
948 when others then
949 return(0);
950
951 END;
952
953 PROCEDURE ShowBasic(pos_vendor_site_id IN VARCHAR2 DEFAULT NULL,
954 pos_vendor_site_name IN VARCHAR2 DEFAULT NULL,
955 pos_ship_to_location_id IN VARCHAR2 DEFAULT NULL,
956 pos_ship_to_location IN VARCHAR2 DEFAULT NULL,
957 pos_supplier_item_number IN VARCHAR2 DEFAULT NULL,
958 pos_item_description IN VARCHAR2 DEFAULT NULL,
959 pos_po_number IN VARCHAR2 DEFAULT NULL,
960 pos_item_number IN VARCHAR2 DEFAULT NULL,
961 pos_date_start IN VARCHAR2 DEFAULT NULL,
962 pos_date_end IN VARCHAR2 DEFAULT NULL,
963 p_advance_flag IN VARCHAR2 DEFAULT 'N'
964 ) IS
965
966 l_index NUMBER;
967 l_count NUMBER;
968 l_row NUMBER;
969
970 l_star VARCHAR2(100);
971 l_fix VARCHAR2(100);
972
973 BEGIN
974
975 ak_query_pkg.exec_query(p_parent_region_appl_id=>178,
976 p_parent_region_code=>'POS_ASN_SEARCH_R',
977 p_responsibility_id=>icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
978 p_user_id=>icx_sec.getID(icx_sec.PV_WEB_USER_ID),
979 p_return_parents=>'F',
980 p_return_children=>'F');
981
982 l_count := 0;
983 l_index := ak_query_pkg.g_items_table.FIRST;
984 l_row := 0;
985
986 WHILE (l_index IS NOT NULL) LOOP
987
988 l_count := l_count + 1;
989
990 IF (ak_query_pkg.g_items_table(l_index).node_display_flag = 'Y') AND
991 (ak_query_pkg.g_items_table(l_index).secured_column = 'F') THEN
992
993 IF (ak_query_pkg.g_items_table(l_index).item_style = 'HIDDEN') THEN
994
995 htp.p('<INPUT NAME="'||
996 ak_query_pkg.g_items_table(l_index).attribute_code||
997 '" TYPE="hidden" VALUE="' ||
998 GetAttributeValue(p_attribute_name=>ak_query_pkg.g_items_table(l_index).attribute_code,
999 p_start_index=>l_count) || '">');
1000
1001 ELSIF (ak_query_pkg.g_items_table(l_index).item_style = 'TEXT') THEN
1002
1003 l_row := l_row + 1;
1004
1005 if ak_query_pkg.g_items_table(l_index).required_flag = 'Y' then
1006
1007 l_star := '<IMG src=/OA_MEDIA/FNDIREQD.gif border=no>';
1008
1009 else
1010
1011 l_star := '';
1012
1013 end if;
1014
1015 if ak_query_pkg.g_items_table(l_index).attribute_code in ('POS_VENDOR_SITE_NAME',
1016 'POS_SHIP_TO_LOCATION') then
1017
1018 l_fix := ' onfocus="javascript:checkBlur(this)" ';
1019
1020 else
1021
1022 l_fix := '';
1023
1024 end if;
1025
1026 htp.p('<TR>');
1027 htp.p('<TD VALIGN=MIDDLE ALIGN=RIGHT WIDTH=175 BGCOLOR=#CCCCCC>'|| l_star ||
1028 '<FONT CLASS=promptblack>'||
1029 ak_query_pkg.g_items_table(l_index).attribute_label_long||
1030 '</FONT>` </TD>');
1031 htp.p('<TD VALIGN=MIDDLE ALIGN=LEFT WIDTH=350 BGCOLOR=#CCCCCC>'||
1032 '<FONT CLASS=datablack>');
1033 htp.p('<INPUT NAME="'||ak_query_pkg.g_items_table(l_index).attribute_code ||'" TYPE="text"' ||
1034 ' VALUE="'||
1035 GetAttributeValue(p_attribute_name=>ak_query_pkg.g_items_table(l_index).attribute_code,
1036 p_start_index=>l_count) ||
1037 '" SIZE='||ak_query_pkg.g_items_table(l_index).display_value_length ||
1038 ' MAXLENGTH='||ak_query_pkg.g_items_table(l_index).attribute_value_length ||
1039 ' onChange="javascript:reset_hidden(''' || ak_query_pkg.g_items_table(l_index).attribute_code || ''')" ' ||
1040 l_fix ||'></FONT>');
1041
1042 IF (ak_query_pkg.g_items_table(l_index).lov_region_code IS NOT NULL AND
1043 ak_query_pkg.g_items_table(l_index).lov_attribute_code IS NOT NULL) THEN
1044
1045 htp.p('<A HREF="javascript:call_LOV('''||
1046 ak_query_pkg.g_items_table(l_index).attribute_code || ''')"' ||
1047 '><IMG SRC="/OA_MEDIA/FNDLSTOV.gif" BORDER=0 WIDTH=23 HEIGHT=21 border=no align=absmiddle></A></TD>');
1048 END IF;
1049
1050 htp.p('</TD>');
1051
1052 IF l_row = 1 THEN
1053
1054 -- Search Button
1055
1056 htp.p('<TD VALIGN=MIDDLE ALIGN=LEFT WIDTH=100 BGCOLOR=#CCCCCC>');
1057
1058 button('javascript:searchShipments()', fnd_message.get_string('ICX', 'ICX_POS_SEARCH'));
1059
1060 htp.p('</TD>');
1061
1062 -- Clear Button
1063
1064 htp.p('<TD VALIGN=MIDDLE ALIGN=LEFT WIDTH=100 BGCOLOR=#CCCCCC>');
1065
1066 button('javascript:clearFields()', fnd_message.get_string('ICX', 'ICX_POS_CLEAR'));
1067
1068 htp.p('</TD>');
1069
1070 ELSIF l_row = 3 THEN
1071
1072 htp.p('<TD VALIGN=MIDDLE ALIGN=LEFT colspan=2 BGCOLOR=#CCCCCC>');
1073
1074 htp.p('<a href="javascript:SwitchSearch()">');
1075
1076 if (p_advance_flag = 'Y') then
1077
1078 htp.p(fnd_message.get_string('ICX','ICX_POS_SIM_SEARCH'));
1079 htp.p('</font></a>` <img src=/OA_MEDIA/FNDWADVS.gif border=no>');
1080 else
1081
1082 htp.p(fnd_message.get_string('ICX','ICX_POS_ADV_SEARCH'));
1083 htp.p('</font></a>` <img src=/OA_MEDIA/FNDWADVS.gif border=no>');
1084 end if;
1085
1086 htp.p('</TD>');
1087
1088 ELSE
1089
1090 htp.p('<TD VALIGN=MIDDLE ALIGN=LEFT colspan=2 BGCOLOR=#CCCCCC>');
1091 htp.p('` ');
1092 htp.p('</TD>');
1093
1094 END IF;
1095
1096 htp.p('</TR>');
1097
1098 ELSE
1099
1100 htp.p('<!-- '||ak_query_pkg.g_items_table(l_index).attribute_code||
1101 ' - '||ak_query_pkg.g_items_table(l_index).item_style||' -->');
1102 END IF;
1103
1104 END IF;
1105
1106 l_index := ak_query_pkg.g_items_table.NEXT(l_index);
1107
1108 END LOOP;
1109
1110 END ShowBasic;
1111
1112
1113 PROCEDURE ShowAdvanced IS
1114
1115 l_index NUMBER;
1116 l_count NUMBER;
1117
1118 l_star VARCHAR2(100);
1119 l_fix VARCHAR2(100);
1120
1121 BEGIN
1122
1123 ak_query_pkg.exec_query(p_parent_region_appl_id=>178,
1124 p_parent_region_code=>'POS_ASN_ADV_SEARCH_R',
1125 p_responsibility_id=>icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
1126 p_user_id=>icx_sec.getID(icx_sec.PV_WEB_USER_ID),
1127 p_return_parents=>'F',
1128 p_return_children=>'F');
1129
1130 l_count := 0;
1131 l_index := ak_query_pkg.g_items_table.FIRST;
1132
1133 WHILE (l_index IS NOT NULL) LOOP
1134
1135 l_count := l_count + 1;
1136
1137 IF (ak_query_pkg.g_items_table(l_index).node_display_flag = 'Y') AND
1138 (ak_query_pkg.g_items_table(l_index).secured_column = 'F') THEN
1139
1140 IF (ak_query_pkg.g_items_table(l_index).item_style = 'HIDDEN') THEN
1141
1142 htp.p('<INPUT NAME="'||
1143 ak_query_pkg.g_items_table(l_index).attribute_code||
1144 '" TYPE="hidden" VALUE="' ||
1145 GetAttributeValue(p_attribute_name=>ak_query_pkg.g_items_table(l_index).attribute_code,
1146 p_start_index=>l_count) || '">');
1147
1148 ELSIF (ak_query_pkg.g_items_table(l_index).item_style = 'TEXT') THEN
1149
1150 if ak_query_pkg.g_items_table(l_index).required_flag = 'Y' then
1151
1152 l_star := '<IMG src=/OA_MEDIA/FNDIREQD.gif border=no>';
1153
1154 else
1155
1156 l_star := '';
1157
1158 end if;
1159
1160 if ak_query_pkg.g_items_table(l_index).attribute_code in ('POS_VENDOR_SITE_NAME',
1161 'POS_SHIP_TO_LOCATION') then
1162
1163 l_fix := ' onfocus="javascript:checkBlur(this)" ';
1164
1165 else
1166
1167 l_fix := '';
1168
1169 end if;
1170
1171 htp.p('<TR>');
1172 htp.p('<TD VALIGN=MIDDLE ALIGN=RIGHT WIDTH=175 BGCOLOR=#CCCCCC>'|| l_star ||
1173 '<FONT CLASS=promptblack>'||
1174 ak_query_pkg.g_items_table(l_index).attribute_label_long||
1175 '</FONT>` </TD>');
1176 htp.p('<TD VALIGN=MIDDLE ALIGN=LEFT WIDTH=350 BGCOLOR=#CCCCCC>'||
1177 '<FONT CLASS=datablack>');
1178 htp.p('<INPUT NAME="'||ak_query_pkg.g_items_table(l_index).attribute_code ||'" TYPE="text"' ||
1179 ' VALUE="'||
1180 GetAttributeValue(p_attribute_name=>ak_query_pkg.g_items_table(l_index).attribute_code,
1181 p_start_index=>l_count) ||
1182 '" SIZE='||ak_query_pkg.g_items_table(l_index).display_value_length ||
1183 ' MAXLENGTH='||ak_query_pkg.g_items_table(l_index).attribute_value_length ||
1184 ' onChange="javascript:reset_hidden(''' || ak_query_pkg.g_items_table(l_index).attribute_code || ''')" ' ||
1185 l_fix ||'></FONT>');
1186
1187 IF (ak_query_pkg.g_items_table(l_index).lov_region_code IS NOT NULL AND
1188 ak_query_pkg.g_items_table(l_index).lov_attribute_code IS NOT NULL) THEN
1189
1190 htp.p('<A HREF="javascript:call_LOV('''||
1191 ak_query_pkg.g_items_table(l_index).attribute_code || ''')"' ||
1192 '><IMG SRC="/OA_MEDIA/FNDLSTOV.gif" BORDER=0 WIDTH=23 HEIGHT=21 border=no align=absmiddle></A></TD>');
1193 END IF;
1194
1195 IF ak_query_pkg.g_items_table(l_index).attribute_code = 'POS_DATE_START' THEN
1196
1197 l_index := ak_query_pkg.g_items_table.NEXT(l_index);
1198
1199 l_count := l_count + 1;
1200
1201 htp.p('<FONT CLASS=promptblack>' || ak_query_pkg.g_items_table(l_index).attribute_label_long ||
1202 '</FONT>` <FONT CLASS=datablack>');
1203
1204 htp.p('<INPUT NAME="'||ak_query_pkg.g_items_table(l_index).attribute_code ||'" TYPE="text"' ||
1205 ' VALUE="'||
1206 GetAttributeValue(p_attribute_name=>ak_query_pkg.g_items_table(l_index).attribute_code,
1207 p_start_index=>l_count) ||
1208 '" SIZE='||ak_query_pkg.g_items_table(l_index).display_value_length ||
1209 ' MAXLENGTH='||ak_query_pkg.g_items_table(l_index).attribute_value_length ||
1210 ' onChange="javascript:reset_hidden(''' || ak_query_pkg.g_items_table(l_index).attribute_code || ''')" ' ||
1211 l_fix ||'></FONT>');
1212
1213 END IF;
1214
1215 htp.p('</TD>');
1216
1217 htp.p('<TD VALIGN=MIDDLE ALIGN=LEFT colspan=2 BGCOLOR=#CCCCCC>');
1218 htp.p('` ');
1219 htp.p('</TD>');
1220
1221 htp.p('</TR>');
1222
1223 ELSE
1224
1225 htp.p('<!-- '||ak_query_pkg.g_items_table(l_index).attribute_code||
1226 ' - '||ak_query_pkg.g_items_table(l_index).item_style||' -->');
1227
1228 END IF;
1229
1230 END IF;
1231
1232 l_index := ak_query_pkg.g_items_table.NEXT(l_index);
1233
1234 END LOOP;
1235
1236 END ShowAdvanced;
1237
1238 PROCEDURE ShowResult(p_start_row IN NUMBER,
1239 p_msg IN VARCHAR2 DEFAULT NULL) IS
1240
1241 l_result_index NUMBER;
1242
1243 l_current_col NUMBER := 0;
1244 l_current_row NUMBER := 0;
1245 l_total_rows NUMBER := 0;
1246
1247 l_session_id NUMBER := icx_sec.getID(icx_sec.PV_SESSION_ID);
1248 l_script_name VARCHAR2(240) := owa_util.get_cgi_env('SCRIPT_NAME');
1249 l_language VARCHAR2(5) := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
1250
1251 l_where_clause VARCHAR2(2000) := 'SESSION_ID = ' || to_char(l_session_id);
1252
1253 l_empty_cart VARCHAR2(1);
1254 l_in_cart NUMBER;
1255 l_shipment_id NUMBER;
1256
1257 l_msg VARCHAR2(200);
1258
1259 l_pagesize NUMBER;
1260
1261 BEGIN
1262
1263 -- Get the page size from icx_parameters
1264
1265 select nvl(query_set, 25)
1266 into l_pagesize
1267 from icx_parameters;
1268
1269 htp.htmlOpen;
1270 htp.headOpen;
1271 htp.linkRel('STYLESHEET', '/OA_HTML/' || l_language || '/POSSTYLE.css');
1272 js.scriptOpen;
1273 icx_util.LOVscript;
1274 js.scriptClose;
1275 htp.p('<script src="/OA_HTML/POSASNSR.js" language="JavaScript"></script>');
1276 htp.headClose;
1277
1278 if p_start_row = 0 then
1279
1280 htp.p('<BODY bgcolor=#cccccc>');
1281 htp.p('<FORM NAME="POS_ASN_RESULT_R" ACTION="' || l_script_name || '/POS_ASN_SEARCH_PKG.ADD_SHIPMENTS_TO_CART" TARGET="result" METHOD=GET">');
1282
1283 htp.p('<INPUT NAME="pos_start_row" TYPE="HIDDEN" VALUE="0">');
1284 htp.p('<INPUT NAME="pos_submit" TYPE="HIDDEN" VALUE="STAY">');
1285 htp.p('</FORM>');
1286
1287 htp.p('<FORM NAME="RESULT_INFO">');
1288 htp.p('<INPUT NAME="p_start" TYPE="HIDDEN" VALUE="">');
1289 htp.p('<INPUT NAME="p_stop" TYPE="HIDDEN" VALUE="">');
1290 htp.p('<INPUT NAME="p_total" TYPE="HIDDEN" VALUE="">');
1291 htp.p('<INPUT NAME="select_all" TYPE="HIDDEN" VALUE="Y">');
1292 htp.p('</FORM>');
1293
1294 htp.bodyClose;
1295 htp.htmlClose;
1296 return;
1297
1298 end if;
1299
1300 select decode(count(1), 0, 'Y', 'N')
1301 into l_empty_cart
1302 from POS_ASN_SHOP_CART_DETAILS
1303 where session_id = l_session_id;
1304
1305 ak_query_pkg.exec_query(p_parent_region_appl_id=>178,
1306 p_parent_region_code=>'POS_ASN_RESULT_R',
1307 p_where_clause=>l_where_clause,
1308 p_responsibility_id=>icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
1309 p_user_id=>icx_sec.getID(icx_sec.PV_WEB_USER_ID),
1310 p_return_parents=>'T',
1311 p_return_children=>'F');
1312
1313 l_total_rows := ak_query_pkg.g_results_table.count;
1314
1315 l_msg := p_msg;
1316
1317 if l_msg is null and l_total_rows = 0 then
1318
1319 l_msg := fnd_message.get_string('ICX','ICX_POS_NO_RECORDS');
1320
1321 end if;
1322
1323 htp.p('<BODY bgcolor=#cccccc onLoad="javascript:LoadResult(''' || l_msg || ''', ''' || l_empty_cart || ''')">');
1324
1325 htp.p('<FORM NAME="POS_ASN_RESULT_R" ACTION="' || l_script_name || '/POS_ASN_SEARCH_PKG.ADD_SHIPMENTS_TO_CART" TARGET="result" METHOD=GET">');
1326
1327 htp.p('<INPUT NAME="pos_start_row" TYPE="HIDDEN" VALUE="' || p_start_row || '">');
1328 htp.p('<INPUT NAME="pos_submit" TYPE="HIDDEN" VALUE="STAY">');
1329
1330 htp.p('<table align=center width=96% bgcolor=#999999 cellpadding=2 cellspacing=0 border=0>');
1331 htp.p('<tr><td>');
1332
1333 htp.p('<TABLE align=center cellpadding=2 cellspacing=1 border=0>');
1334
1335 IF l_total_rows > 0 THEN
1336
1337 PrintResultHeadings;
1338
1339 l_result_index := ak_query_pkg.g_results_table.FIRST;
1340
1341 l_current_row := 0;
1342
1343 WHILE (l_result_index IS NOT NULL) LOOP
1344
1345 l_current_row := l_current_row + 1;
1346
1347 if (l_current_row >= p_start_row AND l_current_row < p_start_row + l_pagesize) OR
1348 p_start_row is null then
1349
1350 if (((l_current_row - p_start_row + 1)mod 2) = 0) THEN
1351 htp.p('<TR BGCOLOR=''#ffffff'' >');
1352 else
1353 htp.p('<TR BGCOLOR=''#99ccff'' >');
1354 end if;
1355
1356 l_shipment_id := to_number(get_result_value(l_result_index, 1));
1357
1358 select count(1)
1359 into l_in_cart
1360 from pos_asn_shop_cart_details
1361 where session_id = l_session_id
1362 and po_line_location_id = l_shipment_id;
1363
1364 if l_in_cart = 0 then
1365 PrintAvailableShipment(l_result_index, l_current_row - p_start_row + 1);
1366 else
1367 PrintSelectedShipment(l_result_index, l_current_row - p_start_row + 1);
1368 end if;
1369
1370 htp.p('</TR>');
1371
1372 END IF;
1373
1374 EXIT WHEN l_current_row >= p_start_row + l_pagesize - 1;
1375
1376 l_result_index := ak_query_pkg.g_results_table.NEXT(l_result_index);
1377
1378 END LOOP;
1379
1380 END IF;
1381
1382 htp.p('</TABLE>');
1383
1384 htp.p('</td></tr></table>');
1385
1386 htp.p('</FORM>');
1387
1388 htp.p('<FORM NAME="RESULT_INFO">');
1389
1390 htp.p('<INPUT NAME="p_start" TYPE="HIDDEN" VALUE="' ||
1391 to_char(p_start_row) || '">');
1392
1393 htp.p('<INPUT NAME="p_stop" TYPE="HIDDEN" VALUE="' || to_char(l_current_row) || '">');
1394
1395 htp.p('<INPUT NAME="p_total" TYPE="HIDDEN" VALUE="' || to_char(l_total_rows) || '">');
1396
1397 htp.p('<INPUT NAME="select_all" TYPE="HIDDEN" VALUE="Y">');
1398
1399 htp.p('</FORM>');
1400
1401 htp.p('<table align=right width=200 bgcolor=#CCCCCC cellpadding=0 cellspacing=0 border=0>');
1402
1403 htp.p('<TR>');
1404
1405 if p_start_row - 1 >= l_pagesize then
1406
1407 htp.p('<TR><TD VALIGN=MIDDLE ALIGN=RIGHT BGCOLOR=#CCCCCC NOWRAP>' ||
1408 '<a href="javascript:parent.result.SwitchPage(''' ||
1409 to_char(0 - l_pagesize) || ''')">' ||
1410 fnd_message.get_string('ICX','ICX_POS_BTN_PREVIOUS') || ' ' ||
1411 to_char(l_pagesize) ||
1412 '</a></TD>');
1413
1414 end if;
1415
1416 if l_total_rows - l_current_row > 0 then
1417
1418 htp.p('<TD VALIGN=MIDDLE ALIGN=RIGHT BGCOLOR=#CCCCCC NOWRAP>' ||
1419 '<a href="javascript:parent.result.SwitchPage(''' ||
1420 to_char(l_pagesize) || ''')">' ||
1421 fnd_message.get_string('ICX','ICX_POS_BTN_NEXT') || ' ' ||
1422 to_char(l_pagesize) || '</a></TD>');
1423
1424 end if;
1425
1426 htp.p('</TR>');
1427
1428 htp.p('</TABLE>');
1429
1430 htp.bodyClose;
1431 htp.htmlClose;
1432
1433 END ShowResult;
1434
1435 PROCEDURE PrintResultHeadings IS
1436
1437 l_attribute_index NUMBER := ak_query_pkg.g_items_table.FIRST;
1438
1439 BEGIN
1440
1441 htp.p('<TR>');
1442
1443 WHILE (l_attribute_index IS NOT NULL) LOOP
1444
1445 IF (ak_query_pkg.g_items_table(l_attribute_index).node_display_flag = 'Y') AND
1446 (ak_query_pkg.g_items_table(l_attribute_index).secured_column = 'F') THEN
1447
1448 IF (ak_query_pkg.g_items_table(l_attribute_index).item_style = 'TEXT') THEN
1449
1450 htp.p('</TD><TD bgcolor=#336699 align=' || ak_query_pkg.g_items_table(l_attribute_index).horizontal_alignment ||
1451 ' valign=bottom><font class=promptwhite>' ||
1452 ak_query_pkg.g_items_table(l_attribute_index).attribute_label_long || '</font></TD>');
1453
1454 ELSIF (ak_query_pkg.g_items_table(l_attribute_index).item_style = 'CHECKBOX') THEN
1455
1456 htp.p('</TD><TD bgcolor=#336699 align=' || ak_query_pkg.g_items_table(l_attribute_index).horizontal_alignment ||
1457 ' valign=bottom><a href="javascript:check_all()"><font class=promptwhite>' ||
1458 ak_query_pkg.g_items_table(l_attribute_index).attribute_label_long || '</A></font></TD>');
1459
1460 END IF;
1461
1462 END IF;
1463
1464 l_attribute_index := ak_query_pkg.g_items_table.NEXT(l_attribute_index);
1465
1466 END LOOP;
1467
1468 htp.p('</TR>');
1469
1470 END PrintResultHeadings;
1471
1472 PROCEDURE PrintAvailableShipment(p_result_index NUMBER,
1473 p_current_row NUMBER) IS
1474
1475 l_attribute_index NUMBER := ak_query_pkg.g_items_table.FIRST;
1476 l_current_col NUMBER := 0;
1477
1478 BEGIN
1479
1480 WHILE (l_attribute_index IS NOT NULL) LOOP
1481
1482 if (ak_query_pkg.g_items_table(l_attribute_index).node_query_flag = 'Y') then
1483
1484 l_current_col := l_current_col + 1;
1485
1486 end if;
1487
1488 IF (ak_query_pkg.g_items_table(l_attribute_index).node_display_flag = 'Y') AND
1489 (ak_query_pkg.g_items_table(l_attribute_index).secured_column = 'F') THEN
1490
1491 IF (ak_query_pkg.g_items_table(l_attribute_index).item_style = 'CHECKBOX') THEN
1492
1493 htp.p('</TD><TD align=center><input name="' || ak_query_pkg.g_items_table(l_attribute_index).attribute_code || '" type="checkbox" value="' || to_char(p_current_row) || '">');
1494
1495 ELSIF (ak_query_pkg.g_items_table(l_attribute_index).item_style = 'HIDDEN') THEN
1496
1497 null;
1498
1499 htp.p('<INPUT NAME="' || ak_query_pkg.g_items_table(l_attribute_index).attribute_code ||
1500 '" TYPE="HIDDEN" VALUE="' || get_result_value(p_result_index, l_current_col) || '">');
1501
1502 ELSIF (ak_query_pkg.g_items_table(l_attribute_index).item_style = 'TEXT') THEN
1503
1504 if ak_query_pkg.g_items_table(l_attribute_index).attribute_code = 'POS_ITEM_DESCRIPTION' then
1505
1506 htp.p('</TD><TD width=1000><font class=tabledata>' ||
1507 nvl(get_result_value(p_result_index, l_current_col), '` ') || '</font>');
1508
1509 else
1510
1511 htp.p('</TD><TD><font class=tabledata>' ||
1512 nvl(get_result_value(p_result_index, l_current_col), '` ') || '</font>');
1513
1514 end if;
1515
1516 END IF;
1517
1518 END IF;
1519
1520 l_attribute_index := ak_query_pkg.g_items_table.NEXT(l_attribute_index);
1521
1522 END LOOP;
1523
1524 END PrintAvailableShipment;
1525
1526 PROCEDURE PrintSelectedShipment(p_result_index NUMBER,
1527 p_current_row NUMBER) IS
1528
1529 l_attribute_index NUMBER := ak_query_pkg.g_items_table.FIRST;
1530 l_current_col NUMBER := 0;
1531
1532 BEGIN
1533
1534 WHILE (l_attribute_index IS NOT NULL) LOOP
1535
1536 if (ak_query_pkg.g_items_table(l_attribute_index).node_query_flag = 'Y') then
1537
1538 l_current_col := l_current_col + 1;
1539
1540 end if;
1541
1542 IF (ak_query_pkg.g_items_table(l_attribute_index).node_display_flag = 'Y') AND
1543 (ak_query_pkg.g_items_table(l_attribute_index).secured_column = 'F') THEN
1544
1545 IF (ak_query_pkg.g_items_table(l_attribute_index).item_style = 'CHECKBOX') THEN
1546
1547 htp.p('</TD><TD align=center valign=middle><img src=/OA_MEDIA/POSCHECK.gif align=center valign=middle>');
1548
1549 ELSIF (ak_query_pkg.g_items_table(l_attribute_index).item_style = 'HIDDEN') THEN
1550
1551 htp.p('<INPUT NAME="' || ak_query_pkg.g_items_table(l_attribute_index).attribute_code ||
1552 '" TYPE="HIDDEN" VALUE="' || get_result_value(p_result_index, l_current_col) || '">');
1553
1554 ELSIF (ak_query_pkg.g_items_table(l_attribute_index).item_style = 'TEXT') THEN
1555
1556 if ak_query_pkg.g_items_table(l_attribute_index).attribute_code = 'POS_ITEM_DESCRIPTION' then
1557
1558 htp.p('</TD><TD width=1000><font class=tabledata>' ||
1559 nvl(get_result_value(p_result_index, l_current_col), '` ') || '</font>');
1560
1561 else
1562
1563 htp.p('</TD><TD><font class=tabledata>' ||
1564 nvl(get_result_value(p_result_index, l_current_col), '` ') || '</font>');
1565
1566 end if;
1567
1568 END IF;
1569
1570 END IF;
1571
1572 l_attribute_index := ak_query_pkg.g_items_table.NEXT(l_attribute_index);
1573
1574 END LOOP;
1575
1576 htp.p('</TD>');
1577
1578 END PrintSelectedShipment;
1579
1580 procedure button(src IN varchar2,
1581 txt IN varchar2) IS
1582
1583 BEGIN
1584
1585 htp.p('
1586 <table cellpadding=0 cellspacing=0 border=0>
1587 <tr>
1588 <td rowspan=5><img src=/OA_MEDIA/FNDBRNDL.gif ></td>
1589 <td bgcolor=#333333><img src=/OA_MEDIA/FNDPX3.gif ></td>
1590 <td rowspan=5><img src=/OA_MEDIA/FNDBRNDR.gif ></td>
1591 </tr>
1592 <tr>
1593 <td bgcolor=#ffffff><img src=/OA_MEDIA/FNDPX6.gif></td>
1594 </tr>
1595 <tr>
1596 <td bgcolor=#cccccc height=20 nowrap><a
1597 href="' || src || '"><font class=button>'|| txt || '</font></a></td>
1598 </tr>
1599 <tr>
1600 <td bgcolor=#666666><img src=/OA_MEDIA/FNDPX3.gif></td>
1601 </tr>
1602 <tr>
1603 <td bgcolor=#333333><img src=/OA_MEDIA/FNDPX3.gif></td>
1604 </tr>
1605 </table>
1606 ');
1607
1608 END button;
1609
1610 function get_result_value(p_index in number, p_col in number) return varchar2 is
1611 sql_statement VARCHAR2(300);
1612 l_cursor NUMBER;
1613 l_execute NUMBER;
1614 l_result VARCHAR2(2000);
1615 BEGIN
1616
1617 IF ak_query_pkg.g_results_table.count > 0 THEN
1618
1619 sql_statement := 'begin ' ||
1620 ':l_result := ak_query_pkg.g_results_table(:p_index).value' ||
1621 to_char(p_col) || '; ' ||
1622 ' end;';
1623
1624 l_cursor := dbms_sql.open_cursor;
1625 dbms_sql.parse(l_cursor, sql_statement, dbms_sql.v7);
1626 dbms_sql.bind_variable(l_cursor, 'l_result', l_result, 2000);
1627 dbms_sql.bind_variable(l_cursor, 'p_index', p_index);
1628
1629 l_execute := dbms_sql.execute(l_cursor);
1630 dbms_sql.variable_value(l_cursor, 'l_result', l_result);
1631 dbms_sql.close_cursor(l_cursor);
1632 return l_result;
1633
1634 ELSE
1635
1636 return null;
1637
1638 END IF;
1639
1640 END get_result_value;
1641
1642 PROCEDURE SetAttributeTable(pos_vendor_site_id IN VARCHAR2 DEFAULT NULL,
1643 pos_vendor_site_name IN VARCHAR2 DEFAULT NULL,
1644 pos_ship_to_location_id IN VARCHAR2 DEFAULT NULL,
1645 pos_ship_to_location IN VARCHAR2 DEFAULT NULL,
1646 pos_supplier_item_number IN VARCHAR2 DEFAULT NULL,
1647 pos_item_description IN VARCHAR2 DEFAULT NULL,
1648 pos_po_number IN VARCHAR2 DEFAULT NULL,
1649 pos_item_number IN VARCHAR2 DEFAULT NULL,
1650 pos_date_start IN VARCHAR2 DEFAULT NULL,
1651 pos_date_end IN VARCHAR2 DEFAULT NULL
1652 ) IS
1653
1654 BEGIN
1655 IF (g_attribute_table.COUNT > 0) THEN
1656 g_attribute_table.DELETE;
1657 END IF;
1658 g_attribute_table(1).attribute_name := 'POS_VENDOR_SITE_ID';
1659 g_attribute_table(1).attribute_value := pos_vendor_site_id;
1660 g_attribute_table(2).attribute_name := 'POS_VENDOR_SITE_NAME';
1661 g_attribute_table(2).attribute_value := pos_vendor_site_name;
1662 g_attribute_table(3).attribute_name := 'POS_SHIP_TO_LOCATION_ID';
1663 g_attribute_table(3).attribute_value := pos_ship_to_location_id;
1664 g_attribute_table(4).attribute_name := 'POS_SHIP_TO_LOCATION';
1665 g_attribute_table(4).attribute_value := pos_ship_to_location;
1666 g_attribute_table(5).attribute_name := 'POS_SUPPLIER_ITEM_NUMBER';
1667 g_attribute_table(5).attribute_value := pos_supplier_item_number;
1668 g_attribute_table(6).attribute_name := 'POS_ITEM_DESCRIPTION';
1669 g_attribute_table(6).attribute_value := pos_item_description;
1670 g_attribute_table(7).attribute_name := 'POS_PO_NUMBER';
1671 g_attribute_table(7).attribute_value := pos_po_number;
1672 g_attribute_table(8).attribute_name := 'POS_ITEM_NUMBER';
1673 g_attribute_table(8).attribute_value := pos_item_number;
1674 g_attribute_table(9).attribute_name := 'POS_DATE_START';
1675 g_attribute_table(9).attribute_value := pos_date_start;
1676 g_attribute_table(10).attribute_name := 'POS_DATE_END';
1677 g_attribute_table(10).attribute_value := pos_date_end;
1678
1679 END SetAttributeTable;
1680
1681 FUNCTION GetAttributeValue(p_attribute_name IN VARCHAR2,
1682 p_start_index IN NUMBER) RETURN VARCHAR2 IS
1683 l_start_index NUMBER;
1684 l_index NUMBER;
1685 l_wrapped BOOLEAN;
1686 BEGIN
1687 IF (p_attribute_name IS NULL OR g_attribute_table.COUNT <= 0) THEN
1688 RETURN NULL;
1689 END IF;
1690 l_start_index := NVL(p_start_index, g_attribute_table.FIRST);
1691 IF (g_attribute_table.EXISTS(p_start_index) AND
1692 g_attribute_table(p_start_index).attribute_name = p_attribute_name) THEN
1693 RETURN g_attribute_table(p_start_index).attribute_value;
1694 END IF;
1695 l_index := g_attribute_table.NEXT(p_start_index);
1696 l_wrapped := FALSE;
1697 WHILE (l_index IS NOT NULL AND NOT (l_wrapped AND l_index >= p_start_index)) LOOP
1698 IF (g_attribute_table(l_index).attribute_name = p_attribute_name) THEN
1699 RETURN g_attribute_table(p_start_index).attribute_value;
1700 END IF;
1701
1702 l_index := g_attribute_table.NEXT(l_index);
1703 IF (l_index IS NULL) THEN
1704 l_index := g_attribute_table.FIRST;
1705 l_wrapped := TRUE;
1706 END IF;
1707 END LOOP;
1708
1709 RETURN NULL;
1710
1711 END GetAttributeValue;
1712
1713 function GetRequiredFlag(p_attribute_code IN VARCHAR2) return varchar2 is
1714
1715 l_required_flag varchar2(1);
1716
1717 begin
1718
1719 select REQUIRED_FLAG
1720 into l_required_flag
1721 from ak_region_items
1722 where REGION_CODE = 'POS_ASN_SEARCH_R'
1723 and ATTRIBUTE_CODE = p_attribute_code;
1724
1725 return(l_required_flag);
1726
1727 exception
1728
1729 when others then
1730 return('N');
1731
1732 end;
1733
1734 END pos_asn_search_pkg;