[Home] [Help]
PACKAGE BODY: APPS.POS_ASL_CAPACITY_PKG
Source
1 PACKAGE BODY POS_ASL_CAPACITY_PKG AS
2 /* $Header: POSSICPB.pls 115.5 2001/11/28 14:11:04 pkm ship $ */
3
4 /* Internal Procedures */
5
6 FUNCTION set_session_info RETURN BOOLEAN is
7 BEGIN
8
9 IF NOT icx_sec.validatesession THEN
10 RETURN FALSE;
11 END IF;
12
13 l_org_id := icx_sec.getID(icx_sec.PV_ORG_ID);
14 l_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
15 l_script_name := owa_util.get_cgi_env('SCRIPT_NAME');
16 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
17 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
18 l_responsibility_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
19 -- get current date format
20 l_date_format := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
21
22 fnd_client_info.set_org_context(l_org_id);
23
24 RETURN TRUE;
25
26 END set_session_info;
27
28 PROCEDURE button(src1 IN varchar2,
29 txt1 IN varchar2) IS
30 BEGIN
31
32 htp.p('
33 <table cellpadding=0 cellspacing=0 border=0>
34 <tr>
35 <td rowspan=5><img src=/OA_MEDIA/FNDBRNDL.gif ></td>
36 <td bgcolor=#333333><img src=/OA_MEDIA/FNDPX3.gif ></td>
37 <td rowspan=5><img src=/OA_MEDIA/FNDBRNDR.gif ></td>
38 <td width=15 rowspan=5></td>
39 </tr>
40 <tr>
41 <td bgcolor=#ffffff><img src=/OA_MEDIA/FNDPX6.gif></td>
42 </tr>
43 <tr>
44 <td bgcolor=#cccccc height=20 nowrap><a target="capacity"
45 href="' || src1 || '"><font class=button>'|| txt1 || '</font></a></td>
46 </tr>
47 <tr>
48 <td bgcolor=#666666><img src=/OA_MEDIA/FNDPX3.gif></td>
49 </tr>
50 <tr>
51 <td bgcolor=#333333><img src=/OA_MEDIA/FNDPX3.gif></td>
52 </tr>
53 </table>
54 ');
55
56 END button;
57
58
59 FUNCTION item_reqd(l_index in number) RETURN VARCHAR2 IS
60 BEGIN
61 if ak_query_pkg.g_items_table(l_index).required_flag = 'Y' then
62 return '<IMG src=/OA_MEDIA/FNDIREQD.gif border=no>';
63 else
64 return '';
65 end if;
66 END item_reqd;
67
68 FUNCTION item_halign(l_index in number) RETURN VARCHAR2 IS
69 BEGIN
70
71 RETURN ' align=' ||
72 ak_query_pkg.g_items_table(l_index).horizontal_alignment;
73
74 END item_halign;
75
76 FUNCTION item_valign(l_index in number) RETURN VARCHAR2 IS
77 BEGIN
78
79 RETURN ' valign=' ||
80 ak_query_pkg.g_items_table(l_index).vertical_alignment;
81
82 END item_valign;
83
84 FUNCTION item_name(l_index in number) RETURN VARCHAR2 IS
85 BEGIN
86
87 RETURN ak_query_pkg.g_items_table(l_index).attribute_label_long;
88
89 END item_name;
90
91 FUNCTION item_code(l_index in number) RETURN VARCHAR2 IS
92 BEGIN
93
94 RETURN ak_query_pkg.g_items_table(l_index).attribute_code;
95
96 END item_code;
97
98 FUNCTION item_style(l_index in number) RETURN VARCHAR2 IS
99 BEGIN
100
101 RETURN ak_query_pkg.g_items_table(l_index).item_style;
102
103 END item_style;
104
105 FUNCTION item_displayed(l_index in number) RETURN BOOLEAN IS
106 BEGIN
107
108 RETURN (ak_query_pkg.g_items_table(l_index).node_display_flag = 'Y');
109
110 END item_displayed;
111
112 FUNCTION item_updateable(l_index in number) RETURN BOOLEAN IS
113 BEGIN
114
115 RETURN (ak_query_pkg.g_items_table(l_index).update_flag = 'Y');
116
117 END item_updateable;
118
119 FUNCTION item_size (l_index in number) RETURN VARCHAR2 IS
120 BEGIN
121
122 RETURN ' size=' || to_char(ak_query_pkg.g_items_table(l_index).display_value_length);
123
124 END item_size;
125
126 FUNCTION item_lov(l_index in number) RETURN VARCHAR2 IS
127 BEGIN
128
129 IF (ak_query_pkg.g_items_table(l_index).lov_region_code IS NOT NULL AND
130 ak_query_pkg.g_items_table(l_index).lov_attribute_code IS NOT NULL)
131 THEN
132 return '<A HREF="javascript:call_LOV('''||
133 item_code(l_index) || ''')"' ||
134 '><IMG SRC="/OA_MEDIA/FNDLSTOV.gif" BORDER=0 WIDTH=23 ' ||
135 'HEIGHT=21 border=no align=absmiddle></A>';
136 ELSE
137 return '';
138 END IF;
139
140 END item_lov;
141
142 FUNCTION item_lov_multi(l_index in number, l_row in number) RETURN VARCHAR2 IS
143 BEGIN
144
145 IF (ak_query_pkg.g_items_table(l_index).lov_region_code IS NOT NULL AND
146 ak_query_pkg.g_items_table(l_index).lov_attribute_code IS NOT NULL)
147 THEN
148 return '<A HREF="javascript:call_LOV('''||
149 item_code(l_index) || '''' || ',' || '''' || to_char(l_row-1) ||
150 '''' || ',' || '''' || l_script_name ||
151 ''')"' ||
152 '><IMG SRC="/OA_MEDIA/FNDLSTOV.gif" BORDER=0 WIDTH=23 ' ||
153 'HEIGHT=21 border=no align=absmiddle></A>';
154 ELSE
155 return '';
156 END IF;
157
158 END item_lov_multi;
159
160 PROCEDURE init_page IS
161
162 BEGIN
163
164 htp.htmlOpen;
165 htp.headOpen;
166 htp.linkRel('STYLESHEET', '/OA_HTML/US/POSSTYLE.css');
167
168 END init_page;
169
170 PROCEDURE init_body IS
171 BEGIN
172
173 htp.headClose;
174 htp.bodyOpen(null,'bgcolor=#cccccc link=blue vlink=blue alink=#ff0000');
175
176 END init_body;
177
178 PROCEDURE close_page IS
179 BEGIN
180
181 htp.bodyClose;
182 htp.htmlClose;
183
184 END close_page;
185
186 function get_result_value(p_index in number, p_col in number) return varchar2 is
187 sql_statement VARCHAR2(300);
188 l_cursor INTEGER;
189 l_execute INTEGER;
190 l_result VARCHAR2(2000);
191 BEGIN
192
193 IF ak_query_pkg.g_results_table.count > 0 THEN
194
195 sql_statement := 'begin ' ||
196 ':l_result := ak_query_pkg.g_results_table(:p_index).value' ||
197 to_char(p_col) || '; ' ||
198 ' end;';
199
200 l_cursor := dbms_sql.open_cursor;
201 dbms_sql.parse(l_cursor, sql_statement, dbms_sql.v7);
202 dbms_sql.bind_variable(l_cursor, 'l_result', l_result, 2000);
203 dbms_sql.bind_variable(l_cursor, 'p_index', p_index);
204
205 l_execute := dbms_sql.execute(l_cursor);
206 dbms_sql.variable_value(l_cursor, 'l_result', l_result);
207 dbms_sql.close_cursor(l_cursor);
208 return l_result;
209
210 ELSE
211
212 return null;
213
214 END IF;
215
216 END get_result_value;
217
218
219 PROCEDURE show_capacity(p_option IN NUMBER,
220 p_where_clause IN VARCHAR2 DEFAULT NULL,
221 p_msg IN VARCHAR2 DEFAULT NULL,
222 p_asl_id IN VARCHAR2 DEFAULT NULL,
223 pos_sic_capacity_id IN t_text_table DEFAULT g_dummy,
224 pos_sic_from_date IN t_text_table DEFAULT g_dummy,
225 pos_sic_to_date IN t_text_table DEFAULT g_dummy,
226 pos_sic_capacity_per_day IN t_text_table DEFAULT g_dummy,
227 pos_sic_rows IN VARCHAR2 DEFAULT NULL,
228 pos_more_rows IN VARCHAR2 DEFAULT '5',
229 pos_error_row IN VARCHAR2 DEFAULT '0') IS
230
231 l_attribute_index NUMBER;
232 l_result_index NUMBER;
233 l_current_col NUMBER;
234 l_current_row NUMBER;
235 l_where_clause VARCHAR2(2000) := '' ;
236 l_value VARCHAR2(100);
237
238 l_rows NUMBER;
239 l_current_rows NUMBER;
240 l_cap VARCHAR2(100);
241 l_date_format VARCHAR2(100);
242
243 BEGIN
244 l_cap := fnd_message.get_string('ICX', 'ICX_POS_CAPACITY');
245 l_date_format := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
246 init_page;
247
248 js.scriptOpen;
249 icx_util.LOVscript;
250 js.scriptClose;
251
252 htp.p(' <script src="/OA_HTML/POSSICPJ.js" language="JavaScript">');
253 htp.p(' </script>');
254 htp.headClose;
255
256 if p_msg is not null then
257 htp.p('<BODY bgcolor=#cccccc onLoad="javascript:show_error(''' || p_msg || ''' , ''' || l_date_format || ''' )">');
258 elsif p_option = 3 then
259 htp.p('<BODY bgcolor=#cccccc onLoad="javascript:finish()">');
260 else
261 htp.p('<BODY bgcolor=#cccccc>');
262 end if;
263
264 htp.p('<form name="POS_SIC_UPDATE" ACTION="' || l_script_name ||
265 '/POS_ASL_CAPACITY_PKG.submit_capacity" target="capacity" method="GET">');
266
267 htp.p('<input name="POS_SIC_ACTION" type="HIDDEN" value="ERROR">');
268 htp.p('<input name="P_ASL_ID" type="HIDDEN" value="' || p_asl_id || '">');
269 htp.p('<input name="POS_ERROR_ROW" type="HIDDEN" value="0">');
270
271 if (p_option = -1 or pos_sic_rows = '-1') then
272 htp.p('<input name="POS_SIC_ROWS" type="HIDDEN" value="-1">');
273 htp.p('</FORM>');
274 htp.bodyClose;
275 htp.htmlClose;
276 return;
277 end if;
278
279 if p_where_clause is not null then
280 l_where_clause := p_where_clause;
281 end if;
282
283 ak_query_pkg.exec_query(p_parent_region_appl_id => 178,
284 p_parent_region_code => 'POS_SUPPLIER_ITEM_CAPACITY_R',
285 p_where_clause => l_where_clause,
286 p_responsibility_id => l_responsibility_id,
287 p_user_id => l_user_id,
288 p_return_parents => 'T',
289 p_return_children => 'F');
290
291 l_attribute_index := ak_query_pkg.g_items_table.FIRST;
292
293 htp.p('<table width=100% bgcolor=#cccccc cellpadding=2 cellspacing=0 border=2>');
294 htp.p('<tr><td>');
295
296 htp.p('<table align=center bgcolor=#cccccc cellpadding=2 cellspacing=1 border=0>');
297
298 htp.p('<CAPTION>' || l_cap || '</CAPTION>');
299
300
301 /* ---- Print the table heading --- */
302
303 htp.p('<tr>');
304
305 WHILE (l_attribute_index IS NOT NULL) LOOP
306
307 IF (item_style(l_attribute_index) = 'HIDDEN') THEN
308
309 htp.p('<!-- ' || item_code(l_attribute_index) ||
310 ' - ' || item_style(l_attribute_index) || ' -->' );
311
312 ELSIF item_displayed(l_attribute_index) THEN
313
314 htp.p('<td bgcolor=#336699' ||
315 item_halign(l_attribute_index) ||
316 item_valign(l_attribute_index) ||
317 '>' ||
318 item_reqd(l_attribute_index)
319 );
320
321
322 htp.p('<font class=promptwhite>' || item_name(l_attribute_index) || '</font>');
323
324 htp.p('</td>');
325
326 END IF;
327
328 l_attribute_index := ak_query_pkg.g_items_table.NEXT(l_attribute_index);
329
330 END LOOP;
331
332 htp.p('</tr>');
333
334 /* ----- end print table heading ----*/
335
336 if ((p_option = 1) or (p_option = 2) or (p_option = 3)) then
337 /* Add rows */ /* Print current content */
338
339 /* ----- print contents -----------*/
340 l_current_row := 0;
341
342 if (pos_sic_rows is not null) then
343
344 FOR l_counter IN 1..to_number(pos_sic_rows) LOOP
345
346 l_current_row := l_current_row + 1;
347
348 if (l_current_row = to_number(pos_error_row))THEN
349 htp.p('<tr BGCOLOR=''#cc033'' >');
350 elsif ((l_current_row mod 2) = 0) THEN
351 htp.p('<tr BGCOLOR=''#ffffff'' >');
352 else
353 htp.p('<tr BGCOLOR=''#99ccff'' >');
354 end if;
355
356 l_attribute_index := ak_query_pkg.g_items_table.FIRST;
357
358 l_current_col := 0;
359
360 WHILE (l_attribute_index IS NOT NULL) LOOP
361
362 l_current_col := l_current_col + 1;
363 if (item_code(l_attribute_index) = 'POS_SIC_CAPACITY_ID') then
364 if (l_counter <= pos_sic_capacity_id.count) then
365 l_value := pos_sic_capacity_id(l_counter);
366 end if;
367 elsif (item_code(l_attribute_index) = 'POS_SIC_FROM_DATE') then
368 l_value := pos_sic_from_date(l_counter);
369 elsif (item_code(l_attribute_index) = 'POS_SIC_TO_DATE') then
370 l_value := pos_sic_to_date(l_counter);
371 elsif (item_code(l_attribute_index) = 'POS_SIC_CAPACITY_PER_DAY') then
372 l_value := pos_sic_capacity_per_day(l_counter);
373 end if;
374
375 IF (item_style(l_attribute_index) = 'HIDDEN') THEN
376 if (item_code(l_attribute_index) = 'POS_SIC_CAPACITY_ID') then
377 if (l_counter <= pos_sic_capacity_id.count) then
378 htp.p('<input name="' || item_code(l_attribute_index) ||
379 '" type="HIDDEN" VALUE="' || l_value ||
380 '">');
381 end if;
382 else
383 htp.p('<input name="' || item_code(l_attribute_index) ||
384 '" type="HIDDEN" VALUE="' || l_value ||
385 '">');
386 end if;
387 ELSE
388 IF item_displayed(l_attribute_index) THEN
389 IF (item_style(l_attribute_index) = 'TEXT') THEN
390 IF item_updateable(l_attribute_index) THEN
391 htp.p('<td nowrap ' ||
392 item_halign(l_attribute_index) ||
393 item_valign(l_attribute_index) ||
394 '>' ||
395 '<font class=datablack>' ||
396 '<input type=text ' || item_size(l_attribute_index) ||
397 ' name="' || item_code(l_attribute_index) || '"' ||
398 ' value="' || l_value ||
399 '" ></font>' ||
400 item_lov_multi(l_attribute_index,l_current_row) ||
401 '</td>');
402
403 ELSE
404
405 htp.p('<td ' ||
406 item_halign(l_attribute_index) ||
407 item_valign(l_attribute_index) ||
408 '>');
409
410 htp.p('<font class=tabledata>' ||
411 l_value ||
412 '</font>');
413
414 htp.p('</td>');
415
416 END IF;
417
418 END IF;
419 END IF;
420 END IF;
421
422 l_attribute_index := ak_query_pkg.g_items_table.NEXT(l_attribute_index);
423
424 END LOOP;
425
426 htp.p('</tr>');
427
428 END LOOP;
429
430 end if; /* pos_sic_rows is not null */
431
432 if (p_option = 1) then
433
434 -- print extra rows
435 l_rows := to_number(pos_more_rows);
436 WHILE (l_rows > 0) LOOP
437
438 l_current_row := l_current_row + 1;
439
440 if ((l_current_row mod 2) = 0) THEN
441 htp.p('<tr BGCOLOR=''#ffffff'' >');
442 else
443 htp.p('<tr BGCOLOR=''#99ccff'' >');
444 end if;
445
446 l_attribute_index := ak_query_pkg.g_items_table.FIRST;
447
448 l_current_col := 0;
449
450 WHILE (l_attribute_index IS NOT NULL) LOOP
451
452 l_current_col := l_current_col + 1;
453
454 IF item_displayed(l_attribute_index) THEN
455 IF (item_style(l_attribute_index) = 'TEXT') THEN
456 IF item_updateable(l_attribute_index) THEN
457
458 htp.p('<td nowrap ' ||
459 item_halign(l_attribute_index) ||
460 item_valign(l_attribute_index) ||
461 '>' ||
462 '<font class=datablack>' ||
463 '<input type=text ' || item_size(l_attribute_index) ||
464 ' name="' || item_code(l_attribute_index) || '"' ||
465 ' value=""' ||
466 '></font>' || item_lov_multi(l_attribute_index,l_current_row) ||
467 '</td>');
468
469 END IF;
470
471 END IF;
472 END IF;
473
474 l_attribute_index := ak_query_pkg.g_items_table.NEXT(l_attribute_index);
475
476 END LOOP;
477
478 htp.p('</tr>');
479
480 l_rows := l_rows - 1;
481
482 END LOOP;
483
484 end if; /* p_option = 1 */
485
486
487 htp.p('</table>');
488
489 htp.p('<input name="POS_SIC_ROWS" type="HIDDEN" value="' ||
490 l_current_row || '">');
491
492 else
493
494 /* ----- print contents -----------*/
495
496 l_current_row := 0;
497
498 IF ak_query_pkg.g_results_table.count > 0 THEN
499
500 l_result_index := ak_query_pkg.g_results_table.FIRST;
501
502 WHILE (l_result_index IS NOT NULL) LOOP
503
504 l_current_row := l_current_row + 1;
505
506 if ((l_current_row mod 2) = 0) THEN
507 htp.p('<tr BGCOLOR=''#ffffff'' >');
508 else
509 htp.p('<tr BGCOLOR=''#99ccff'' >');
510 end if;
511
512 l_attribute_index := ak_query_pkg.g_items_table.FIRST;
513
514 l_current_col := 0;
515
516 WHILE (l_attribute_index IS NOT NULL) LOOP
517
518 l_current_col := l_current_col + 1;
519
520 IF (item_style(l_attribute_index) = 'HIDDEN') THEN
521
522 htp.p('<input name="' || item_code(l_attribute_index) ||
523 '" type="HIDDEN" VALUE="' ||
524 get_result_value(l_result_index, l_current_col) || '">');
525
526 ELSE
527 IF item_displayed(l_attribute_index) THEN
528 IF (item_style(l_attribute_index) = 'TEXT') THEN
529 IF item_updateable(l_attribute_index) THEN
530
531 htp.p('<td nowrap ' ||
532 item_halign(l_attribute_index) ||
533 item_valign(l_attribute_index) ||
534 '>' ||
535 '<font class=datablack>' ||
536 '<input type="text" ' || item_size(l_attribute_index) ||
537 ' name="' || item_code(l_attribute_index) || '"' ||
538 ' value="' || nvl(get_result_value(l_result_index, l_current_col),'') ||
539 '" ></font>' || item_lov_multi(l_attribute_index,l_current_row) ||
540 '</td>');
541
542 ELSE
543
544 htp.p('<td ' ||
545 item_halign(l_attribute_index) ||
546 item_valign(l_attribute_index) ||
547 '>');
548
549 htp.p('<font class=tabledata>' ||
550 nvl(get_result_value(l_result_index, l_current_col), ' ') ||
551 '</font>');
552
553 htp.p('</td>');
554
555 END IF;
556
557 END IF;
558 END IF;
559 END IF;
560
561 l_attribute_index := ak_query_pkg.g_items_table.NEXT(l_attribute_index);
562
563 END LOOP;
564
565 htp.p('</tr>');
566
567 l_result_index := ak_query_pkg.g_results_table.NEXT(l_result_index);
568
569 END LOOP;
570
571 END IF;
572
573 htp.p('<input name="POS_SIC_ROWS" type="HIDDEN" value="' ||
574 l_current_row || '">');
575
576 htp.p('</table>');
577
578 end if;
579
580 /* Print Add Button */
581 htp.p('<br>');
582 htp.p('<table align=center cellpadding=2 cellspacing=1 border=0>');
583
584 htp.p('<tr><td>');
585
586 button('javascript:addrows()',
587 fnd_message.get_string('ICX', 'ICX_POS_ADD_ROW'));
588
589 htp.p('</td><td>');
590
591 htp.p( '<font class=datablack>' ||
592 '<input type="text" size=1' ||
593 ' name="POS_MORE_ROWS" value="' || to_number(pos_more_rows) || '"></font> ');
594
595 htp.p('</td></tr>');
596
597 htp.p('</table>');
598
599 htp.p('</td></tr></table>');
600 htp.p('</form>');
601
602 close_page;
603
604 END show_capacity;
605
606 FUNCTION check_capacity(pos_sic_action IN VARCHAR2 DEFAULT NULL,
607 pos_sic_rows IN VARCHAR2 DEFAULT NULL,
608 p_asl_id IN VARCHAR2 DEFAULT NULL,
609 pos_error_row IN VARCHAR2 DEFAULT NULL,
610 pos_more_rows IN VARCHAR2 DEFAULT NULL,
611 pos_sic_capacity_id IN t_text_table DEFAULT g_dummy,
612 pos_sic_from_date IN t_text_table DEFAULT g_dummy,
613 pos_sic_to_date IN t_text_table DEFAULT g_dummy,
614 pos_sic_capacity_per_day IN t_text_table DEFAULT g_dummy
615 ) RETURN BOOLEAN IS
616
617 l_from_date DATE;
618 l_to_date DATE;
619 l_capacity_id NUMBER;
620 l_capacity_per_day NUMBER;
621
622 l_from_date_1 DATE;
623 l_to_date_1 DATE;
624 l_capacity_per_day_1 NUMBER;
625
626 l_msg1 VARCHAR2(100);
627 l_msg2 VARCHAR2(100);
628 l_msg3 VARCHAR2(100);
629
630 BEGIN
631 l_msg1 := fnd_message.get_string('ICX', 'ICX_POS_FUTURE_DATE');
632 l_msg2 := fnd_message.get_string('ICX', 'ICX_POS_OVERLAPP_DATE');
633 l_msg3 := fnd_message.get_string('ICX', 'ICX_POS_DATE_FORMAT');
634
635 if (pos_sic_rows is not null and pos_sic_rows <> '-1') then
636
637 /* Check date format, and is future or not */
638 FOR l_counter IN 1..to_number(pos_sic_rows) LOOP
639 BEGIN
640 l_from_date := to_date(pos_sic_from_date(l_counter),
641 l_date_format);
642 l_to_date := to_date(pos_sic_to_date(l_counter),
643 l_date_format);
644 EXCEPTION
645 WHEN OTHERS THEN
646 show_capacity(2, null, l_msg3, p_asl_id,
647 pos_sic_capacity_id, pos_sic_from_date,
648 pos_sic_to_date, pos_sic_capacity_per_day,
649 pos_sic_rows, pos_more_rows, l_counter);
650 return false;
651 END;
652
653 l_capacity_per_day := to_number(pos_sic_capacity_per_day(l_counter));
654
655 if not (l_from_date is null and l_to_date is null and
656 l_capacity_per_day is null) then
657 if (l_to_date is not null and
658 (l_from_date > l_to_date or l_to_date < SYSDATE)) then
659 show_capacity(2, null,
660 l_msg1,
661 p_asl_id, pos_sic_capacity_id, pos_sic_from_date,
662 pos_sic_to_date, pos_sic_capacity_per_day,
663 pos_sic_rows, pos_more_rows, l_counter);
664 return false;
665 end if;
666 end if;
667
668 END LOOP;
669
670 /* Check any overlapping date */
671
672 FOR l_counter IN 1..to_number(pos_sic_rows) LOOP
673 l_from_date := to_date(pos_sic_from_date(l_counter),
674 l_date_format);
675 l_to_date := to_date(pos_sic_to_date(l_counter),
676 l_date_format);
677 l_capacity_per_day := to_number(pos_sic_capacity_per_day(l_counter));
678
679 if not (l_from_date is null and l_to_date is null and
680 l_capacity_per_day is null) then
681 FOR k_counter IN (l_counter+1)..to_number(pos_sic_rows) LOOP
682 l_from_date_1 := to_date(pos_sic_from_date(k_counter),
683 l_date_format);
684 l_to_date_1 := to_date(pos_sic_to_date(k_counter),
685 l_date_format);
686 l_capacity_per_day_1 := to_number(pos_sic_capacity_per_day(k_counter));
687
688 if not (l_from_date_1 is null and l_to_date_1 is null and
689 l_capacity_per_day_1 is null) then
690 if (((l_from_date between l_from_date_1 and
691 nvl(l_to_date_1, l_from_date +1)) or
692 (l_to_date between l_from_date_1 and
693 nvl(l_to_date_1, l_to_date +1))) or
694 ((l_from_date_1 between l_from_date and
695 nvl(l_to_date, l_from_date_1 +1)) or
696 (l_to_date_1 between l_from_date and
697 nvl(l_to_date, l_to_date_1 +1)))) then
698
699 show_capacity(2, null, l_msg2,
700 p_asl_id, pos_sic_capacity_id, pos_sic_from_date,
701 pos_sic_to_date, pos_sic_capacity_per_day,
702 pos_sic_rows, pos_more_rows, l_counter );
703 return false;
704 end if;
705 end if;
706 END LOOP;
707
708 end if;
709
710 END LOOP;
711
712 end if;
713
714 return true;
715
716 END check_capacity;
717
718
719 /* Main Procedures */
720
721 PROCEDURE capacity_frame(pos_asl_id IN VARCHAR2 DEFAULT NULL
722 ) IS
723
724 l_where_clause VARCHAR2(2000) := NULL;
725
726 BEGIN
727
728 IF (pos_asl_id IS NULL) THEN
729 show_capacity(-1);
730 return;
731 END IF;
732
733 l_where_clause := 'asl_id = ' || pos_asl_id ;
734
735 show_capacity(0, l_where_clause, null, pos_asl_id);
736
737 EXCEPTION
738
739 when others then
740 show_capacity(-1, null, 'Exception Raised in Capacity_Frame');
741 null;
742
743 END capacity_frame;
744
745
746 PROCEDURE submit_capacity(pos_sic_action IN VARCHAR2 DEFAULT NULL,
747 pos_sic_rows IN VARCHAR2 DEFAULT NULL,
748 p_asl_id IN VARCHAR2 DEFAULT NULL,
749 pos_error_row IN VARCHAR2 DEFAULT NULL,
750 pos_more_rows IN VARCHAR2 DEFAULT NULL,
751 pos_sic_capacity_id IN t_text_table DEFAULT g_dummy,
752 pos_sic_from_date IN t_text_table DEFAULT g_dummy,
753 pos_sic_to_date IN t_text_table DEFAULT g_dummy,
754 pos_sic_capacity_per_day IN t_text_table DEFAULT g_dummy
755 ) IS
756
757 l_from_date DATE;
758 l_to_date DATE;
759 l_capacity_id NUMBER;
760 l_capacity_per_day NUMBER;
761
762 l_where_clause VARCHAR2(2000) := NULL;
763
764 BEGIN
765
766 if (pos_sic_action = 'ADDROWS') then
767 -- Add more rows ---
768 show_capacity(1, '1=0', null, p_asl_id, pos_sic_capacity_id,
769 pos_sic_from_date, pos_sic_to_date,
770 pos_sic_capacity_per_day,
771 pos_sic_rows, pos_more_rows);
772
773 elsif (pos_sic_action = 'ERROR') then
774 show_capacity(2, null, null, p_asl_id,
775 pos_sic_capacity_id, pos_sic_from_date,
776 pos_sic_to_date, pos_sic_capacity_per_day,
777 pos_sic_rows, pos_more_rows, pos_error_row);
778
779 elsif (pos_sic_action = 'CLEAR') then
780
781 show_capacity(-1);
782
783 elsif (pos_sic_action = 'CHECK') then
784
785 if (check_capacity(pos_sic_action, pos_sic_rows, p_asl_id,
786 pos_error_row, pos_more_rows, pos_sic_capacity_id,
787 pos_sic_from_date, pos_sic_to_date, pos_sic_capacity_per_day)) then
788 show_capacity(3, null, null, p_asl_id,
789 pos_sic_capacity_id, pos_sic_from_date,
790 pos_sic_to_date, pos_sic_capacity_per_day,
791 pos_sic_rows, pos_more_rows, pos_error_row);
792 end if;
793
794 elsif (pos_sic_action = 'SUBMIT') then
795 if (pos_sic_rows is not null and pos_sic_rows <> '-1') then
796
797 -- Update all the info ---
798
799 FOR l_counter IN 1..to_number(pos_sic_rows) LOOP
800 l_from_date := to_date(pos_sic_from_date(l_counter),
801 l_date_format);
802 l_to_date := to_date(pos_sic_to_date(l_counter),
803 l_date_format);
804
805 if ((pos_sic_capacity_id is not null) and
806 (l_counter <= pos_sic_capacity_id.count)) then
807 l_capacity_id := to_number(pos_sic_capacity_id(l_counter));
808 else
809 l_capacity_id := -1;
810 end if;
811
812 l_capacity_per_day := to_number(pos_sic_capacity_per_day(l_counter));
813
814 if not (l_from_date is null and l_to_date is null and
815 l_capacity_per_day is null) then
816
817 if ((l_capacity_id <> -1) and (l_from_date is not null)) then
818 update po_supplier_item_capacity
819 set from_date = l_from_date,
820 to_date = l_to_date,
821 capacity_per_day = nvl(l_capacity_per_day, 0),
822 last_update_date = sysdate,
823 last_updated_by = l_user_id
824 where capacity_id = l_capacity_id;
825 else
826 insert into po_supplier_item_capacity
827 (CAPACITY_ID,
828 ASL_ID,
829 USING_ORGANIZATION_ID,
830 FROM_DATE,
831 TO_DATE,
832 CAPACITY_PER_DAY,
833 LAST_UPDATE_DATE,
834 LAST_UPDATED_BY,
835 LAST_UPDATE_LOGIN,
836 CREATION_DATE,
837 CREATED_BY)
838 values (
839 po_supplier_item_capacity_s.nextval,
840 to_number(p_asl_id),
841 -1,
842 l_from_date,
843 l_to_date,
844 l_capacity_per_day,
845 sysdate,
846 l_user_id,
847 l_user_id,
848 sysdate,
849 l_user_id);
850
851 end if;
852
853 elsif (l_capacity_id <> -1) then
854
855 delete from po_supplier_item_capacity
856 where capacity_id = l_capacity_id;
857
858 end if; /* (l_from_date is null ) and (l_to_date is null ) and
859 (l_capacity_per_day is null) */
860
861 END LOOP;
862
863
864 COMMIT;
865
866 end if;
867
868 l_where_clause := 'asl_id = ' || p_asl_id ;
869 show_capacity(0, l_where_clause, null, p_asl_id);
870
871 end if;
872
873 END submit_capacity;
874
875
876
877 /* Initialize the session info only once per session */
878 BEGIN
879
880 IF NOT set_session_info THEN
881 RETURN;
882 END IF;
883
884 END POS_ASL_CAPACITY_PKG;