[Home] [Help]
PACKAGE BODY: APPS.BOM_COPY_CALENDAR
Source
1 PACKAGE BODY BOM_COPY_CALENDAR as
2 /* $Header: BOMPCPCB.pls 115.1 99/07/16 05:14:55 porting ship $ */
3
4
5
6 /* Declare private subprograms */
7
8 PROCEDURE Copy_Cal_Workday_Patterns
9 ( x_calendar_code_from VARCHAR2,
10 x_calendar_code_to VARCHAR2,
11 x_userid NUMBER );
12
13 PROCEDURE Copy_Shift_Workday_Patterns
14 ( x_calendar_code_from VARCHAR2,
15 x_calendar_code_to VARCHAR2,
16 x_shift_num_from NUMBER,
17 x_shift_num_to NUMBER,
18 x_userid NUMBER );
19
20 PROCEDURE Copy_Cal_Exceptions
21 ( x_calendar_code_from VARCHAR2,
22 x_calendar_code_to VARCHAR2,
23 x_start_date DATE,
24 x_end_date DATE,
25 x_userid NUMBER );
26
27 PROCEDURE Copy_Cal_All_Shifts
28 ( x_calendar_code_from VARCHAR2,
29 x_calendar_code_to VARCHAR2,
30 x_shift_num_from NUMBER,
31 x_shift_num_to NUMBER,
32 x_start_date DATE,
33 x_end_date DATE,
34 x_userid NUMBER );
35
36 PROCEDURE Copy_Cal_Spec_Shift
37 ( x_calendar_code_from VARCHAR2,
38 x_calendar_code_to VARCHAR2,
39 x_shift_num_from NUMBER,
40 x_start_date DATE,
41 x_end_date DATE,
42 x_userid NUMBER );
43
44 PROCEDURE Copy_Shift_Times
45 ( x_calendar_code_from VARCHAR2,
46 x_calendar_code_to VARCHAR2,
47 x_shift_num_from NUMBER,
48 x_shift_num_to NUMBER,
49 x_userid NUMBER );
50
51 PROCEDURE Copy_Shift_Exceptions
52 ( x_calendar_code_from VARCHAR2,
53 x_calendar_code_to VARCHAR2,
54 x_shift_num_from NUMBER,
55 x_shift_num_to NUMBER,
56 x_start_date DATE,
57 x_end_date DATE,
58 x_userid NUMBER );
59
60 PROCEDURE Copy_Exceptions_Cal
61 ( x_calendar_code_to VARCHAR2,
62 x_exception_set_name VARCHAR2,
63 x_start_date DATE,
64 x_end_date DATE,
65 x_userid NUMBER );
66
67 PROCEDURE Copy_Shift_Exceptions_Cal
68 ( x_calendar_code_from VARCHAR2,
69 x_calendar_code_to VARCHAR2,
70 x_shift_num_from NUMBER,
71 x_start_date DATE,
72 x_end_date DATE,
73 x_userid NUMBER );
74
75 PROCEDURE Copy_Exceptions_Shift
76 ( x_calendar_code_to VARCHAR2,
77 x_shift_num_to NUMBER,
78 x_exception_set_name VARCHAR2,
79 x_start_date DATE,
80 x_end_date DATE,
81 x_userid NUMBER );
82
83 PROCEDURE Copy_Cal_Exceptions_Shift
84 ( x_calendar_code_from VARCHAR2,
85 x_calendar_code_to VARCHAR2,
86 x_shift_num_to NUMBER,
87 x_start_date DATE,
88 x_end_date DATE,
89 x_userid NUMBER );
90
91
92 /* End private subprogram declarations */
93
94
95
96 PROCEDURE Drop_Cal_Cancelled_Excepts IS
97 BEGIN
98 delete from BOM_CALENDAR_EXCEPTIONS
99 where created_by = '-99999';
100
101 if (SQL%NOTFOUND) then
102 null;
103 end if;
104 END Drop_Cal_Cancelled_Excepts;
105
106
107
108 PROCEDURE Drop_Shift_Cancelled_Excepts IS
109 BEGIN
110 delete from BOM_SHIFT_EXCEPTIONS
111 where created_by = '-99999';
112
113 if (SQL%NOTFOUND) then
114 null;
115 end if;
116 END Drop_Shift_Cancelled_Excepts;
117
118
119
120 PROCEDURE Copy_Calendar ( copy_type IN NUMBER,
121 x_calendar_code_from IN VARCHAR2,
122 x_calendar_code_to IN VARCHAR2,
123 x_shift_num_from IN NUMBER,
124 x_shift_num_to IN NUMBER,
125 x_exception_set_name IN VARCHAR2,
126 x_start_date IN DATE,
127 x_end_date IN DATE,
128 x_userid IN NUMBER ) IS
129
130 BEGIN
131
132 /* ********************************************************************** */
133 /* copy_type in (1, 2, 3) */
134 /* 1 => copies all shifts */
135 /* 3 => copies specific shift */
136 /* copies information from one calendar to another, including */
137 /* calendar workday patterns, calendar exceptions, and calendar shifts */
138 /* ********************************************************************** */
139
140 if copy_type in (1, 2, 3) then
141 Copy_Cal_Workday_Patterns (x_calendar_code_from, x_calendar_code_to,
142 x_userid);
143 Copy_Cal_Exceptions (x_calendar_code_from, x_calendar_code_to,
144 x_start_date, x_end_date, x_userid);
145 if copy_type = 1 then
146 Copy_Cal_All_Shifts (x_calendar_code_from, x_calendar_code_to,
147 x_shift_num_from, x_shift_num_to,
148 x_start_date, x_end_date, x_userid);
149 elsif copy_type = 3 then
150 Copy_Cal_Spec_Shift (x_calendar_code_from, x_calendar_code_to,
151 x_shift_num_from, x_start_date, x_end_date,
152 x_userid);
153 end if;
154
155
156 /* ********************************************************************** */
157 /* copy_type = 4 */
158 /* copies information from one shift to another, including */
159 /* shift times, shift exceptions, and shift workday patterns */
160 /* ********************************************************************** */
161
162 elsif copy_type = 4 then
163 Copy_Shift_Times (x_calendar_code_from, x_calendar_code_to,
164 x_shift_num_from, x_shift_num_to, x_userid);
165 Copy_Shift_Exceptions (x_calendar_code_from, x_calendar_code_to,
166 x_shift_num_from, x_shift_num_to,
167 x_start_date, x_end_date, x_userid);
168 Copy_Shift_Workday_Patterns (x_calendar_code_from, x_calendar_code_to,
169 x_shift_num_from, x_shift_num_to, x_userid);
170
171
172 /* ********************************************************************** */
173 /* copy_type = 5 */
174 /* copies exceptions from an exception set to a calendar */
175 /* from BOM_EXCEPTION_SETS into BOM_CALENDAR_EXCEPTIONS */
176 /* ********************************************************************** */
177
178 elsif copy_type = 5 then
179 Copy_Exceptions_Cal (x_calendar_code_to, x_exception_set_name,
180 x_start_date, x_end_date, x_userid);
181
182
183 /* ********************************************************************** */
184 /* copy_type = 6 */
185 /* copies exceptions from one calendar to another */
186 /* from BOM_CALENDAR_EXCEPTIONS into BOM_CALENDAR_EXCEPTIONS */
187 /* ********************************************************************** */
188
189 elsif copy_type = 6 then
190 Copy_Cal_Exceptions (x_calendar_code_from, x_calendar_code_to,
191 x_start_date, x_end_date, x_userid);
192
193
194 /* ********************************************************************** */
195 /* copy_type = 7 */
196 /* copies exceptions from a shift to a calendar */
197 /* from BOM_SHIFT_EXCEPTIONS into BOM_CALENDAR_EXCEPTIONS */
198 /* ********************************************************************** */
199
200 elsif copy_type = 7 then
201 Copy_Shift_Exceptions_Cal (x_calendar_code_from, x_calendar_code_to,
202 x_shift_num_from, x_start_date, x_end_date, x_userid);
203
204
205 /* ********************************************************************** */
206 /* copy_type = 8 */
207 /* copies exceptions from an exception set to a shift */
208 /* from BOM_EXCEPTION_SETS into BOM_SHIFT_EXCEPTIONS */
209 /* ********************************************************************** */
210
211 elsif copy_type = 8 then
212 Copy_Exceptions_Shift (x_calendar_code_to, x_shift_num_to,
213 x_exception_set_name, x_start_date, x_end_date, x_userid);
214
215
216 /* ********************************************************************** */
217 /* copy_type = 9 */
218 /* copies exceptions from a calendar to a shift */
219 /* from BOM_CALENDAR_EXCEPTIONS into BOM_SHIFT_EXCEPTIONS */
220 /* ********************************************************************** */
221
222 elsif copy_type = 9 then
223 Copy_Cal_Exceptions_Shift (x_calendar_code_from, x_calendar_code_to,
224 x_shift_num_to, x_start_date, x_end_date, x_userid);
225
226
227 /* ********************************************************************** */
228 /* copy_type = 10 */
229 /* copies exceptions from one shift to another */
230 /* from BOM_SHIFT_EXCEPTIONS into BOM_SHIFT_EXCEPTIONS */
231 /* ********************************************************************** */
232
233 elsif copy_type = 10 then
234 Copy_Shift_Exceptions (x_calendar_code_from, x_calendar_code_to,
235 x_shift_num_from, x_shift_num_to,
236 x_start_date, x_end_date, x_userid);
237
238 end if;
239
240 END Copy_Calendar;
241
242
243
244
245 /* Define local procedures, available only in package */
246
247
248 PROCEDURE Copy_Cal_Workday_Patterns (x_calendar_code_from VARCHAR2,
249 x_calendar_code_to VARCHAR2,
250 x_userid NUMBER) IS
251 v_max_seq_num BOM_WORKDAY_PATTERNS.seq_num%TYPE;
252 BEGIN
253
254 select NVL(MAX(seq_num),0)
255 into v_max_seq_num
256 from BOM_WORKDAY_PATTERNS bwp
257 where bwp.calendar_code = x_calendar_code_to
258 and shift_num is null;
259
260 insert into BOM_WORKDAY_PATTERNS
261 ( calendar_code,
262 seq_num,
263 last_update_date,
264 last_updated_by,
265 creation_date,
266 created_by,
267 last_update_login,
268 days_on,
269 days_off,
270 description,
271 attribute_category,
272 attribute1,
273 attribute2,
274 attribute3,
275 attribute4,
276 attribute5,
277 attribute6,
278 attribute7,
279 attribute8,
280 attribute9,
281 attribute10,
282 attribute11,
283 attribute12,
284 attribute13,
285 attribute14,
286 attribute15 )
287 select
288 x_calendar_code_to,
289 v_max_seq_num + bwp.seq_num,
290 sysdate,
291 x_userid,
292 sysdate,
293 x_userid,
294 x_userid,
295 bwp.days_on,
296 bwp.days_off,
297 bwp.description,
298 bwp.attribute_category,
299 bwp.attribute1,
300 bwp.attribute2,
301 bwp.attribute3,
302 bwp.attribute4,
303 bwp.attribute5,
304 bwp.attribute6,
305 bwp.attribute7,
306 bwp.attribute8,
307 bwp.attribute9,
308 bwp.attribute10,
309 bwp.attribute11,
310 bwp.attribute12,
311 bwp.attribute13,
312 bwp.attribute14,
313 bwp.attribute15
314 from BOM_WORKDAY_PATTERNS bwp
315 where bwp.calendar_code = x_calendar_code_from
316 and bwp.shift_num is null;
317
318 END Copy_Cal_Workday_Patterns;
319
320
321 PROCEDURE Copy_Shift_Workday_Patterns (x_calendar_code_from VARCHAR2,
322 x_calendar_code_to VARCHAR2,
323 x_shift_num_from NUMBER,
324 x_shift_num_to NUMBER,
325 x_userid NUMBER) IS
326
327 v_max_seq_num BOM_WORKDAY_PATTERNS.seq_num%TYPE;
328
329 BEGIN
330
331 select NVL(MAX(seq_num),0)
332 into v_max_seq_num
333 from BOM_WORKDAY_PATTERNS bwp
334 where bwp.calendar_code = x_calendar_code_to
335 and shift_num is not null;
336
337 insert into BOM_WORKDAY_PATTERNS
338 ( calendar_code,
339 shift_num,
340 seq_num,
341 last_update_date,
342 last_updated_by,
343 creation_date,
344 created_by,
345 last_update_login,
346 days_on,
347 days_off,
348 description,
349 attribute_category,
350 attribute1,
351 attribute2,
352 attribute3,
353 attribute4,
354 attribute5,
355 attribute6,
356 attribute7,
357 attribute8,
358 attribute9,
359 attribute10,
360 attribute11,
361 attribute12,
362 attribute13,
363 attribute14,
364 attribute15 )
365 select
366 x_calendar_code_to,
367 x_shift_num_to,
368 v_max_seq_num + bwp1.seq_num,
369 sysdate,
370 x_userid,
371 sysdate,
372 x_userid,
373 x_userid,
374 bwp1.days_on,
375 bwp1.days_off,
376 bwp1.description,
377 bwp1.attribute_category,
378 bwp1.attribute1,
379 bwp1.attribute2,
380 bwp1.attribute3,
381 bwp1.attribute4,
382 bwp1.attribute5,
383 bwp1.attribute6,
384 bwp1.attribute7,
385 bwp1.attribute8,
386 bwp1.attribute9,
387 bwp1.attribute10,
388 bwp1.attribute11,
389 bwp1.attribute12,
390 bwp1.attribute13,
391 bwp1.attribute14,
392 bwp1.attribute15
393 from BOM_WORKDAY_PATTERNS bwp1
394 where bwp1.calendar_code = x_calendar_code_from
395 and bwp1.shift_num = x_shift_num_from;
396
397 END Copy_Shift_Workday_Patterns;
398
399
400
401 PROCEDURE Copy_Cal_Exceptions (x_calendar_code_from VARCHAR2,
402 x_calendar_code_to VARCHAR2,
403 x_start_date DATE,
404 x_end_date DATE,
405 x_userid NUMBER) IS
406
407 BEGIN
408
409 insert into BOM_CALENDAR_EXCEPTIONS
410 ( calendar_code,
411 exception_set_id,
412 exception_date,
413 last_update_date,
414 last_updated_by,
415 last_update_login,
416 creation_date,
417 created_by,
418 exception_type,
419 attribute_category,
420 attribute1,
421 attribute2,
422 attribute3,
423 attribute4,
424 attribute5,
425 attribute6,
426 attribute7,
427 attribute8,
428 attribute9,
429 attribute10,
430 attribute11,
431 attribute12,
432 attribute13,
433 attribute14,
434 attribute15 )
435 select
436 x_calendar_code_to,
437 -1,
438 bce.exception_date,
439 sysdate,
440 x_userid,
441 x_userid,
442 sysdate,
443 x_userid,
444 -- '-99999',
445 bce.exception_type,
446 bce.attribute_category,
447 bce.attribute1,
448 bce.attribute2,
449 bce.attribute3,
450 bce.attribute4,
451 bce.attribute5,
452 bce.attribute6,
453 bce.attribute7,
454 bce.attribute8,
455 bce.attribute9,
456 bce.attribute10,
457 bce.attribute11,
458 bce.attribute12,
459 bce.attribute13,
460 bce.attribute14,
461 bce.attribute15
462 from BOM_CALENDAR_EXCEPTIONS bce
463 where bce.calendar_code = x_calendar_code_from
464 and bce.exception_date not in
465 (select bce1.exception_date
466 from BOM_CALENDAR_EXCEPTIONS bce1
467 where bce1.calendar_code = x_calendar_code_to)
468 and bce.exception_date >= x_start_date
469 and bce.exception_date <= x_end_date;
470 END Copy_Cal_Exceptions;
471
472
473 PROCEDURE Copy_Cal_All_Shifts (x_calendar_code_from VARCHAR2,
474 x_calendar_code_to VARCHAR2,
475 x_shift_num_from NUMBER,
476 x_shift_num_to NUMBER,
477 x_start_date DATE,
478 x_end_date DATE,
479 x_userid NUMBER) IS
480
481 v_max_seq_num BOM_WORKDAY_PATTERNS.seq_num%TYPE;
482 BEGIN
483
484 insert into BOM_CALENDAR_SHIFTS
485 ( calendar_code,
486 shift_num,
487 last_update_date,
488 last_updated_by,
489 last_update_login,
490 creation_date,
491 created_by,
492 days_on,
493 days_off,
494 description,
495 attribute_category,
496 attribute1,
497 attribute2,
498 attribute3,
499 attribute4,
500 attribute5,
501 attribute6,
502 attribute7,
503 attribute8,
504 attribute9,
505 attribute10,
506 attribute11,
507 attribute12,
508 attribute13,
509 attribute14,
510 attribute15 )
511 select
512 x_calendar_code_to,
513 bcs.shift_num,
514 sysdate,
515 '-99999',
516 x_userid,
517 sysdate,
521 bcs.description,
518 x_userid,
519 bcs.days_on,
520 bcs.days_off,
522 bcs.attribute_category,
523 bcs.attribute1,
524 bcs.attribute2,
525 bcs.attribute3,
526 bcs.attribute4,
527 bcs.attribute5,
528 bcs.attribute6,
529 bcs.attribute7,
530 bcs.attribute8,
531 bcs.attribute9,
532 bcs.attribute10,
533 bcs.attribute11,
534 bcs.attribute12,
535 bcs.attribute13,
536 bcs.attribute14,
537 bcs.attribute15
538 from BOM_CALENDAR_SHIFTS bcs
539 where bcs.calendar_code = x_calendar_code_from
540 and bcs.shift_num not in
541 ( select bcs1.shift_num
542 from BOM_CALENDAR_SHIFTS bcs1
543 where bcs1.calendar_code = x_calendar_code_to );
544
545 insert into BOM_SHIFT_TIMES
546 ( calendar_code,
547 shift_num,
548 from_time,
549 to_time,
550 last_update_date,
551 last_updated_by,
552 last_update_login,
553 creation_date,
554 created_by,
555 attribute_category,
556 attribute1,
557 attribute2,
558 attribute3,
559 attribute4,
560 attribute5,
561 attribute6,
562 attribute7,
563 attribute8,
564 attribute9,
565 attribute10,
566 attribute11,
567 attribute12,
568 attribute13,
569 attribute14,
570 attribute15 )
571 select
572 x_calendar_code_to,
573 bst1.shift_num,
574 bst1.from_time,
575 bst1.to_time,
576 sysdate,
577 x_userid,
578 x_userid,
579 sysdate,
580 x_userid,
581 bst1.attribute_category,
582 bst1.attribute1,
583 bst1.attribute2,
584 bst1.attribute3,
585 bst1.attribute4,
586 bst1.attribute5,
587 bst1.attribute6,
588 bst1.attribute7,
589 bst1.attribute8,
590 bst1.attribute9,
591 bst1.attribute10,
592 bst1.attribute11,
593 bst1.attribute12,
594 bst1.attribute13,
595 bst1.attribute14,
596 bst1.attribute15
597 from BOM_SHIFT_TIMES bst1, BOM_CALENDAR_SHIFTS bcs5
598 where bcs5.last_updated_by = '-99999'
599 and bcs5.calendar_code = x_calendar_code_to
600 and bcs5.shift_num = bst1.shift_num
601 and bst1.calendar_code = x_calendar_code_from;
602
603
604 select NVL(MAX(seq_num),0)
605 into v_max_seq_num
606 from BOM_WORKDAY_PATTERNS bwp
607 where bwp.calendar_code = x_calendar_code_to
608 and shift_num is not null;
609
610 insert into BOM_WORKDAY_PATTERNS
611 ( calendar_code,
612 shift_num,
613 seq_num,
614 last_update_date,
615 last_updated_by,
616 creation_date,
617 created_by,
618 last_update_login,
619 days_on,
620 days_off,
621 description,
622 attribute_category,
623 attribute1,
624 attribute2,
625 attribute3,
626 attribute4,
627 attribute5,
628 attribute6,
629 attribute7,
630 attribute8,
631 attribute9,
632 attribute10,
633 attribute11,
634 attribute12,
635 attribute13,
636 attribute14,
637 attribute15 )
638 select
639 x_calendar_code_to,
640 bwp1.shift_num,
641 v_max_seq_num + bwp1.seq_num,
642 sysdate,
643 x_userid,
644 sysdate,
645 x_userid,
646 x_userid,
647 bwp1.days_on,
648 bwp1.days_off,
649 bwp1.description,
650 bwp1.attribute_category,
651 bwp1.attribute1,
652 bwp1.attribute2,
653 bwp1.attribute3,
654 bwp1.attribute4,
655 bwp1.attribute5,
656 bwp1.attribute6,
657 bwp1.attribute7,
658 bwp1.attribute8,
659 bwp1.attribute9,
660 bwp1.attribute10,
661 bwp1.attribute11,
662 bwp1.attribute12,
663 bwp1.attribute13,
664 bwp1.attribute14,
665 bwp1.attribute15
666 from BOM_WORKDAY_PATTERNS bwp1, BOM_CALENDAR_SHIFTS bcs5
667 where bcs5.last_updated_by = '-99999'
668 and bcs5.calendar_code = x_calendar_code_to
669 and bcs5.shift_num = bwp1.shift_num
670 and bwp1.calendar_code = x_calendar_code_from;
671
672 insert into BOM_SHIFT_EXCEPTIONS
673 ( calendar_code,
674 shift_num,
675 exception_set_id,
676 exception_date,
677 exception_type,
678 last_update_date,
679 last_updated_by,
680 last_update_login,
681 creation_date,
682 created_by,
683 attribute_category,
684 attribute1,
685 attribute2,
686 attribute3,
687 attribute4,
688 attribute5,
689 attribute6,
690 attribute7,
691 attribute8,
692 attribute9,
693 attribute10,
694 attribute11,
695 attribute12,
696 attribute13,
697 attribute14,
701 bse1.shift_num,
698 attribute15 )
699 select
700 x_calendar_code_to,
702 -1,
703 bse1.exception_date,
704 bse1.exception_type,
705 sysdate,
706 x_userid,
707 x_userid,
708 sysdate,
709 x_userid,
710 bse1.attribute_category,
711 bse1.attribute1,
712 bse1.attribute2,
713 bse1.attribute3,
714 bse1.attribute4,
715 bse1.attribute5,
716 bse1.attribute6,
717 bse1.attribute7,
718 bse1.attribute8,
719 bse1.attribute9,
720 bse1.attribute10,
721 bse1.attribute11,
722 bse1.attribute12,
723 bse1.attribute13,
724 bse1.attribute14,
725 bse1.attribute15
726 from BOM_SHIFT_EXCEPTIONS bse1, BOM_CALENDAR_SHIFTS bcs5
727 where bcs5.last_updated_by = '-99999'
728 and bcs5.calendar_code = x_calendar_code_to
729 and bcs5.shift_num = bse1.shift_num
730 and bse1.calendar_code = x_calendar_code_from
731 and bse1.exception_date >= x_start_date
732 and bse1.exception_date <= x_end_date;
733
734 update BOM_CALENDAR_SHIFTS set
735 last_updated_by = x_userid
736 where calendar_code = x_calendar_code_to
737 and last_updated_by = '-99999';
738
739 END Copy_Cal_All_Shifts;
740
741
742
743 PROCEDURE Copy_Cal_Spec_Shift (x_calendar_code_from VARCHAR2,
744 x_calendar_code_to VARCHAR2,
745 x_shift_num_from NUMBER,
746 x_start_date DATE,
747 x_end_date DATE,
748 x_userid NUMBER) IS
749
750 v_max_seq_num BOM_WORKDAY_PATTERNS.seq_num%TYPE;
751
752
753 BEGIN
754
755 insert into BOM_CALENDAR_SHIFTS
756 ( calendar_code,
757 shift_num,
758 last_update_date,
759 last_updated_by,
760 last_update_login,
761 creation_date,
762 created_by,
763 days_on,
764 days_off,
765 description,
766 attribute_category,
767 attribute1,
768 attribute2,
769 attribute3,
770 attribute4,
771 attribute5,
772 attribute6,
773 attribute7,
774 attribute8,
775 attribute9,
776 attribute10,
777 attribute11,
778 attribute12,
779 attribute13,
780 attribute14,
781 attribute15 )
782 select
783 x_calendar_code_to,
784 x_shift_num_from,
785 sysdate,
786 x_userid,
787 x_userid,
788 sysdate,
789 x_userid,
790 bcs.days_on,
791 bcs.days_off,
792 bcs.description,
793 bcs.attribute_category,
794 bcs.attribute1,
795 bcs.attribute2,
796 bcs.attribute3,
797 bcs.attribute4,
798 bcs.attribute5,
799 bcs.attribute6,
800 bcs.attribute7,
801 bcs.attribute8,
802 bcs.attribute9,
803 bcs.attribute10,
804 bcs.attribute11,
805 bcs.attribute12,
806 bcs.attribute13,
807 bcs.attribute14,
808 bcs.attribute15
809 from BOM_CALENDAR_SHIFTS bcs
810 where bcs.calendar_code = x_calendar_code_from
811 and bcs.shift_num = x_shift_num_from;
812
813 insert into BOM_SHIFT_TIMES
814 ( calendar_code,
815 shift_num,
816 from_time,
817 to_time,
818 last_update_date,
819 last_updated_by,
820 last_update_login,
821 creation_date,
822 created_by,
823 attribute_category,
824 attribute1,
825 attribute2,
826 attribute3,
827 attribute4,
828 attribute5,
829 attribute6,
830 attribute7,
831 attribute8,
832 attribute9,
833 attribute10,
834 attribute11,
835 attribute12,
836 attribute13,
837 attribute14,
838 attribute15 )
839 select
840 x_calendar_code_to,
841 x_shift_num_from,
842 bst4.from_time,
843 bst4.to_time,
844 sysdate,
845 x_userid,
846 x_userid,
847 sysdate,
848 x_userid,
849 bst4.attribute_category,
850 bst4.attribute1,
851 bst4.attribute2,
852 bst4.attribute3,
853 bst4.attribute4,
854 bst4.attribute5,
855 bst4.attribute6,
856 bst4.attribute7,
857 bst4.attribute8,
858 bst4.attribute9,
859 bst4.attribute10,
860 bst4.attribute11,
861 bst4.attribute12,
862 bst4.attribute13,
863 bst4.attribute14,
864 bst4.attribute15
865 from BOM_SHIFT_TIMES bst4
866 where bst4.calendar_code = x_calendar_code_from
867 and bst4.shift_num = x_shift_num_from;
868
869
870 select NVL(MAX(seq_num),0)
871 into v_max_seq_num
872 from BOM_WORKDAY_PATTERNS bwp
873 where bwp.calendar_code = x_calendar_code_to;
874
875 insert into BOM_WORKDAY_PATTERNS
876 ( calendar_code,
877 shift_num,
878 seq_num,
879 last_update_date,
880 last_updated_by,
881 creation_date,
882 created_by,
883 last_update_login,
884 days_on,
885 days_off,
886 description,
887 attribute_category,
888 attribute1,
889 attribute2,
890 attribute3,
891 attribute4,
892 attribute5,
896 attribute9,
893 attribute6,
894 attribute7,
895 attribute8,
897 attribute10,
898 attribute11,
899 attribute12,
900 attribute13,
901 attribute14,
902 attribute15 )
903 select
904 x_calendar_code_to,
905 x_shift_num_from,
906 v_max_seq_num + bwp7.seq_num,
907 sysdate,
908 x_userid,
909 sysdate,
910 x_userid,
911 x_userid,
912 bwp7.days_on,
913 bwp7.days_off,
914 bwp7.description,
915 bwp7.attribute_category,
916 bwp7.attribute1,
917 bwp7.attribute2,
918 bwp7.attribute3,
919 bwp7.attribute4,
920 bwp7.attribute5,
921 bwp7.attribute6,
922 bwp7.attribute7,
923 bwp7.attribute8,
924 bwp7.attribute9,
925 bwp7.attribute10,
926 bwp7.attribute11,
927 bwp7.attribute12,
928 bwp7.attribute13,
929 bwp7.attribute14,
930 bwp7.attribute15
931 from BOM_WORKDAY_PATTERNS bwp7
932 where bwp7.calendar_code = x_calendar_code_from
933 and bwp7.shift_num = x_shift_num_from;
934
935 insert into BOM_SHIFT_EXCEPTIONS
936 ( calendar_code,
937 shift_num,
938 exception_set_id,
939 exception_date,
940 exception_type,
941 last_update_date,
942 last_updated_by,
943 last_update_login,
944 creation_date,
945 created_by,
946 attribute_category,
947 attribute1,
948 attribute2,
949 attribute3,
950 attribute4,
951 attribute5,
952 attribute6,
953 attribute7,
954 attribute8,
955 attribute9,
956 attribute10,
957 attribute11,
958 attribute12,
959 attribute13,
960 attribute14,
961 attribute15 )
962 select
963 x_calendar_code_to,
964 x_shift_num_from,
965 -1,
966 bse1.exception_date,
967 bse1.exception_type,
968 sysdate,
969 x_userid,
970 x_userid,
971 sysdate,
972 x_userid,
973 bse1.attribute_category,
974 bse1.attribute1,
975 bse1.attribute2,
976 bse1.attribute3,
977 bse1.attribute4,
978 bse1.attribute5,
979 bse1.attribute6,
980 bse1.attribute7,
981 bse1.attribute8,
982 bse1.attribute9,
983 bse1.attribute10,
984 bse1.attribute11,
985 bse1.attribute12,
986 bse1.attribute13,
987 bse1.attribute14,
988 bse1.attribute15
989 from BOM_SHIFT_EXCEPTIONS bse1
990 where bse1.calendar_code = x_calendar_code_from
991 and bse1.shift_num = x_shift_num_from
992 and bse1.exception_date >= x_start_date
993 and bse1.exception_date <= x_end_date;
994
995 END Copy_Cal_Spec_Shift;
996
997
998
999 PROCEDURE Copy_Shift_Times (x_calendar_code_from VARCHAR2,
1000 x_calendar_code_to VARCHAR2,
1001 x_shift_num_from NUMBER,
1002 x_shift_num_to NUMBER,
1003 x_userid NUMBER) IS
1004
1005 BEGIN
1006
1007 insert into BOM_SHIFT_TIMES
1008 ( calendar_code,
1009 shift_num,
1010 from_time,
1011 to_time,
1012 last_update_date,
1013 last_updated_by,
1014 last_update_login,
1015 creation_date,
1016 created_by,
1017 attribute_category,
1018 attribute1,
1019 attribute2,
1020 attribute3,
1021 attribute4,
1022 attribute5,
1023 attribute6,
1024 attribute7,
1025 attribute8,
1026 attribute9,
1027 attribute10,
1028 attribute11,
1029 attribute12,
1030 attribute13,
1031 attribute14,
1032 attribute15 )
1033 select
1034 x_calendar_code_to,
1035 x_shift_num_to,
1036 bst.from_time,
1037 bst.to_time,
1038 sysdate,
1039 x_userid,
1040 x_userid,
1041 sysdate,
1042 x_userid,
1043 bst.attribute_category,
1044 bst.attribute1,
1045 bst.attribute2,
1046 bst.attribute3,
1047 bst.attribute4,
1048 bst.attribute5,
1049 bst.attribute6,
1050 bst.attribute7,
1051 bst.attribute8,
1052 bst.attribute9,
1053 bst.attribute10,
1054 bst.attribute11,
1055 bst.attribute12,
1056 bst.attribute13,
1057 bst.attribute14,
1058 bst.attribute15
1059 from BOM_SHIFT_TIMES bst
1060 where bst.calendar_code = x_calendar_code_from
1061 and bst.shift_num = x_shift_num_from
1062 and not exists ( select 1
1063 from BOM_SHIFT_TIMES bst1
1064 where bst1.calendar_code = x_calendar_code_to
1065 and bst1.shift_num = x_shift_num_to
1066 and bst1.from_time = bst.from_time
1067 and bst1.to_time = bst.to_time );
1068
1069 END Copy_Shift_Times;
1070
1071
1075 x_shift_num_from NUMBER,
1072
1073 PROCEDURE Copy_Shift_Exceptions (x_calendar_code_from VARCHAR2,
1074 x_calendar_code_to VARCHAR2,
1076 x_shift_num_to NUMBER,
1077 x_start_date DATE,
1078 x_end_date DATE,
1079 x_userid NUMBER) IS
1080
1081 BEGIN
1082
1083 insert into BOM_SHIFT_EXCEPTIONS
1084 ( calendar_code,
1085 shift_num,
1086 exception_set_id,
1087 exception_date,
1088 exception_type,
1089 last_update_date,
1090 last_updated_by,
1091 last_update_login,
1092 creation_date,
1093 created_by,
1094 attribute_category,
1095 attribute1,
1096 attribute2,
1097 attribute3,
1098 attribute4,
1099 attribute5,
1100 attribute6,
1101 attribute7,
1102 attribute8,
1103 attribute9,
1104 attribute10,
1105 attribute11,
1106 attribute12,
1107 attribute13,
1108 attribute14,
1109 attribute15 )
1110 select
1111 x_calendar_code_to,
1112 x_shift_num_to,
1113 -1,
1114 bse.exception_date,
1115 bse.exception_type,
1116 sysdate,
1117 x_userid,
1118 x_userid,
1119 sysdate,
1120 '-99999',
1121 bse.attribute_category,
1122 bse.attribute1,
1123 bse.attribute2,
1124 bse.attribute3,
1125 bse.attribute4,
1126 bse.attribute5,
1127 bse.attribute6,
1128 bse.attribute7,
1129 bse.attribute8,
1130 bse.attribute9,
1131 bse.attribute10,
1132 bse.attribute11,
1133 bse.attribute12,
1134 bse.attribute13,
1135 bse.attribute14,
1136 bse.attribute15
1137 from BOM_SHIFT_EXCEPTIONS bse
1138 where bse.calendar_code = x_calendar_code_from
1139 and bse.shift_num = x_shift_num_from
1140 and bse.exception_date not in
1141 (select bse1.exception_date
1142 from BOM_SHIFT_EXCEPTIONS bse1
1143 where bse1.calendar_code = x_calendar_code_to
1144 and bse1.shift_num = x_shift_num_to)
1145 and bse.exception_date >= x_start_date
1146 and bse.exception_date <= x_end_date;
1147
1148 END Copy_Shift_Exceptions;
1149
1150
1151
1152 PROCEDURE Copy_Exceptions_Cal (x_calendar_code_to VARCHAR2,
1153 x_exception_set_name VARCHAR2,
1154 x_start_date DATE,
1155 x_end_date DATE,
1156 x_userid NUMBER) IS
1157
1158 BEGIN
1159
1160 insert into BOM_CALENDAR_EXCEPTIONS
1161 ( calendar_code,
1162 exception_set_id,
1163 exception_date,
1164 exception_type,
1165 last_update_date,
1166 last_updated_by,
1167 last_update_login,
1168 creation_date,
1169 created_by,
1170 attribute_category,
1171 attribute1,
1172 attribute2,
1173 attribute3,
1174 attribute4,
1175 attribute5,
1176 attribute6,
1177 attribute7,
1178 attribute8,
1179 attribute9,
1180 attribute10,
1181 attribute11,
1182 attribute12,
1183 attribute13,
1184 attribute14,
1185 attribute15 )
1186 select
1187 x_calendar_code_to,
1188 -1,
1189 besd.exception_date,
1190 besd.exception_type,
1191 sysdate,
1192 x_userid,
1193 x_userid,
1194 sysdate,
1195 '-99999',
1196 besd.attribute_category,
1197 besd.attribute1,
1198 besd.attribute2,
1199 besd.attribute3,
1200 besd.attribute4,
1201 besd.attribute5,
1202 besd.attribute6,
1203 besd.attribute7,
1204 besd.attribute8,
1205 besd.attribute9,
1206 besd.attribute10,
1207 besd.attribute11,
1208 besd.attribute12,
1209 besd.attribute13,
1210 besd.attribute14,
1211 besd.attribute15
1212 from BOM_EXCEPTION_SET_DATES besd, BOM_EXCEPTION_SETS bes
1213 where bes.exception_set_name = x_exception_set_name
1214 and bes.exception_set_id = besd.exception_set_id
1215 and trunc(besd.exception_date) not in (
1216 select trunc(bce.exception_date)
1217 from BOM_CALENDAR_EXCEPTIONS bce
1218 where bce.calendar_code = x_calendar_code_to )
1219 and trunc(besd.exception_date) >= x_start_date
1220 and trunc(besd.exception_date) <= x_end_date;
1221
1222 END Copy_Exceptions_Cal;
1223
1224
1225
1226 Procedure Copy_Shift_Exceptions_Cal (x_calendar_code_from VARCHAR2,
1227 x_calendar_code_to VARCHAR2,
1228 x_shift_num_from NUMBER,
1229 x_start_date DATE,
1230 x_end_date DATE,
1231 x_userid NUMBER) IS
1232
1233 BEGIN
1234
1235 insert into BOM_CALENDAR_EXCEPTIONS
1236 ( calendar_code,
1237 exception_set_id,
1238 exception_date,
1239 last_update_date,
1240 last_updated_by,
1241 last_update_login,
1242 creation_date,
1243 created_by,
1244 exception_type,
1245 attribute_category,
1246 attribute1,
1247 attribute2,
1248 attribute3,
1249 attribute4,
1250 attribute5,
1251 attribute6,
1252 attribute7,
1253 attribute8,
1254 attribute9,
1255 attribute10,
1256 attribute11,
1257 attribute12,
1258 attribute13,
1259 attribute14,
1260 attribute15 )
1261 select
1262 x_calendar_code_to,
1263 -1,
1264 bse9.exception_date,
1268 sysdate,
1265 sysdate,
1266 x_userid,
1267 x_userid,
1269 '-99999',
1270 bse9.exception_type,
1271 bse9.attribute_category,
1272 bse9.attribute1,
1273 bse9.attribute2,
1274 bse9.attribute3,
1275 bse9.attribute4,
1276 bse9.attribute5,
1277 bse9.attribute6,
1278 bse9.attribute7,
1279 bse9.attribute8,
1280 bse9.attribute9,
1281 bse9.attribute10,
1282 bse9.attribute11,
1283 bse9.attribute12,
1284 bse9.attribute13,
1285 bse9.attribute14,
1286 bse9.attribute15
1287 from BOM_SHIFT_EXCEPTIONS bse9
1288 where bse9.calendar_code = x_calendar_code_from
1289 and bse9.shift_num = x_shift_num_from
1290 and bse9.exception_date not in
1291 (select bce9.exception_date
1292 from BOM_CALENDAR_EXCEPTIONS bce9
1293 where bce9.calendar_code = x_calendar_code_to)
1294 and bse9.exception_date >= x_start_date
1295 and bse9.exception_date <= x_end_date;
1296
1297 END Copy_Shift_Exceptions_Cal;
1298
1299
1300
1301 PROCEDURE Copy_Exceptions_Shift (x_calendar_code_to VARCHAR2,
1302 x_shift_num_to NUMBER,
1303 x_exception_set_name VARCHAR2,
1304 x_start_date DATE,
1305 x_end_date DATE,
1306 x_userid NUMBER) IS
1307
1308 BEGIN
1309
1310 insert into BOM_SHIFT_EXCEPTIONS
1311 ( calendar_code,
1312 shift_num,
1313 exception_set_id,
1314 exception_date,
1315 exception_type,
1316 last_update_date,
1317 last_updated_by,
1318 last_update_login,
1319 creation_date,
1320 created_by,
1321 attribute_category,
1322 attribute1,
1323 attribute2,
1324 attribute3,
1325 attribute4,
1326 attribute5,
1327 attribute6,
1328 attribute7,
1329 attribute8,
1330 attribute9,
1331 attribute10,
1332 attribute11,
1333 attribute12,
1334 attribute13,
1335 attribute14,
1336 attribute15 )
1337 select
1338 x_calendar_code_to,
1339 x_shift_num_to,
1340 -1,
1341 besd.exception_date,
1342 besd.exception_type,
1343 sysdate,
1344 x_userid,
1345 x_userid,
1346 sysdate,
1347 '-99999',
1348 besd.attribute_category,
1349 besd.attribute1,
1350 besd.attribute2,
1351 besd.attribute3,
1352 besd.attribute4,
1353 besd.attribute5,
1354 besd.attribute6,
1355 besd.attribute7,
1356 besd.attribute8,
1357 besd.attribute9,
1358 besd.attribute10,
1359 besd.attribute11,
1360 besd.attribute12,
1361 besd.attribute13,
1362 besd.attribute14,
1363 besd.attribute15
1364 from BOM_EXCEPTION_SET_DATES besd, BOM_EXCEPTION_SETS bes
1365 where bes.exception_set_name = x_exception_set_name
1366 and bes.exception_set_id = besd.exception_set_id
1367 and trunc(besd.exception_date) not in (
1368 select trunc(bse.exception_date)
1369 from BOM_SHIFT_EXCEPTIONS bse
1370 where bse.calendar_code = x_calendar_code_to
1371 and bse.shift_num = x_shift_num_to )
1372 and trunc(besd.exception_date) >= x_start_date
1373 and trunc(besd.exception_date) <= x_end_date ;
1374
1375 END Copy_Exceptions_Shift;
1376
1377
1378 PROCEDURE Copy_Cal_Exceptions_Shift (x_calendar_code_from VARCHAR2,
1379 x_calendar_code_to VARCHAR2,
1380 x_shift_num_to NUMBER,
1381 x_start_date DATE,
1382 x_end_date DATE,
1383 x_userid NUMBER) IS
1384
1385 BEGIN
1386
1387 insert into BOM_SHIFT_EXCEPTIONS
1388 ( calendar_code,
1389 shift_num,
1390 exception_set_id,
1391 exception_date,
1392 exception_type,
1393 last_update_date,
1394 last_updated_by,
1395 last_update_login,
1396 creation_date,
1397 created_by,
1398 attribute_category,
1399 attribute1,
1400 attribute2,
1401 attribute3,
1402 attribute4,
1403 attribute5,
1404 attribute6,
1405 attribute7,
1406 attribute8,
1407 attribute9,
1408 attribute10,
1409 attribute11,
1410 attribute12,
1411 attribute13,
1412 attribute14,
1413 attribute15 )
1414 select
1415 x_calendar_code_to,
1416 x_shift_num_to,
1417 -1,
1418 bce9.exception_date,
1419 bce9.exception_type,
1420 sysdate,
1421 x_userid,
1422 x_userid,
1423 sysdate,
1424 '-99999',
1425 bce9.attribute_category,
1426 bce9.attribute1,
1427 bce9.attribute2,
1428 bce9.attribute3,
1429 bce9.attribute4,
1430 bce9.attribute5,
1431 bce9.attribute6,
1432 bce9.attribute7,
1433 bce9.attribute8,
1434 bce9.attribute9,
1435 bce9.attribute10,
1436 bce9.attribute11,
1437 bce9.attribute12,
1438 bce9.attribute13,
1439 bce9.attribute14,
1440 bce9.attribute15
1441 from BOM_CALENDAR_EXCEPTIONS bce9
1442 where bce9.calendar_code = x_calendar_code_from
1443 and bce9.exception_date not in
1444 (select bse9.exception_date
1445 from BOM_SHIFT_EXCEPTIONS bse9
1446 where bse9.calendar_code = x_calendar_code_to
1447 and bse9.shift_num = x_shift_num_to)
1448 and bce9.exception_date >= x_start_date
1449 and bce9.exception_date <= x_end_date;
1450
1451 END Copy_Cal_Exceptions_Shift;
1452
1453
1454
1455 END BOM_COPY_CALENDAR;