[Home] [Help]
PACKAGE BODY: APPS.MSC_EXCHANGE_BUCKETING
Source
1 PACKAGE BODY MSC_EXCHANGE_BUCKETING AS
2 /* $Header: MSCXBKB.pls 120.4 2006/02/13 04:12:43 pragarwa noship $ */
3 EQUAL CONSTANT INTEGER := 1;
4 NOT_EQUAL CONSTANT INTEGER := 2;
5 SYS_NO CONSTANT INTEGER := 2;
6 SYS_YES CONSTANT INTEGER := 1;
7 FIVE_DAY_WEEK CONSTANT INTEGER := 1;
8 SEVEN_DAY_WEEK CONSTANT INTEGER := 2;
9 MIXED CONSTANT INTEGER := 4;
10 DAY CONSTANT INTEGER := 1;
11 WEEK CONSTANT INTEGER := 2;
12 MONTH CONSTANT INTEGER := 3;
13 NONE CONSTANT INTEGER := -1;
14 SUPPLY_PLANNING CONSTANT INTEGER := 1;
15 DEMAND_PLANNING CONSTANT INTEGER := 2;
16
17 TYPE numberList is table of number;
18
19 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
20 IS
21 BEGIN
22 IF( g_msc_cp_debug= '1' OR g_msc_cp_debug = '2') THEN
23 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
24 END IF;
25 --dbms_output.put_line(pBUFF);
26 EXCEPTION
27 WHEN OTHERS THEN
28 RETURN;
29 END LOG_MESSAGE;
30
31 PROCEDURE ADD_TO_PLAN_BUCKETS(p_plan_id IN NUMBER,
32 p_org_id IN NUMBER,
33 p_sr_instance_id IN NUMBER,
34 p_bkt_index IN NUMBER,
35 p_supplier_id IN NUMBER,
36 p_supplier_site_id IN NUMBER,
37 p_customer_id IN NUMBER,
38 p_customer_site_id IN NUMBER,
39 p_inventory_item_id in number,
40 p_plan_type in number,
41 p_curr_flag IN NUMBER,
42 p_start_date IN NUMBER,
43 p_end_date IN NUMBER,
44 p_days_in_bkt IN NUMBER,
45 p_bkt_type IN NUMBER) IS
46 begin
47
48 if (p_plan_type = SUPPLY_PLANNING) then
49
50 insert into msc_cp_plan_buckets(
51 plan_id,
52 organization_id,
53 sr_instance_id,
54 bucket_index,
55 curr_flag,
56 bkt_start_date,
57 bkt_end_date,
58 days_in_bkt,
59 bucket_type,
60 supplier_id,
61 supplier_site_id,
62 customer_id,
63 customer_site_id,
64 inventory_item_id,
65 plan_type,
66 last_update_date,
67 last_updated_by,
68 creation_date,
69 created_by) values
70 (p_plan_id,
71 p_org_id,
72 p_sr_instance_id,
73 p_bkt_index,
74 p_curr_flag,
75 to_date(p_start_date, 'J'),
76 to_date(p_end_date, 'J'),
77 p_days_in_bkt,
78 p_bkt_type,
79 p_supplier_id,
80 p_supplier_site_id,
81 p_customer_id,
82 p_customer_site_id,
83 p_inventory_item_id ,
84 p_plan_type ,
85 sysdate,
86 -1,
87 sysdate,
88 -1);
89
90 elsif (p_plan_type = DEMAND_PLANNING) then
91
92 insert into msc_plan_buckets(
93 plan_id,
94 organization_id,
95 sr_instance_id,
96 bucket_index,
97 curr_flag,
98 bkt_start_date,
99 bkt_end_date,
100 days_in_bkt,
101 bucket_type,
102 last_update_date,
103 last_updated_by,
104 creation_date,
105 created_by) values
106 (p_plan_id,
107 p_org_id,
108 p_sr_instance_id,
109 p_bkt_index,
110 p_curr_flag,
111 to_date(p_start_date, 'J'),
112 to_date(p_end_date, 'J'),
113 p_days_in_bkt,
114 p_bkt_type,
115 sysdate,
116 -1,
117 sysdate,
118 -1);
119
120 end if;
121
122 commit;
123 end;
124
125 PROCEDURE ADD_TO_PLAN_BUCKETS(p_plan_id IN NUMBER,
126 p_org_id IN NUMBER,
127 p_sr_instance_id IN NUMBER,
128 p_bkt_index IN NUMBER,
129 p_curr_flag IN NUMBER,
130 p_start_date IN NUMBER,
131 p_end_date IN NUMBER,
132 p_days_in_bkt IN NUMBER,
133 p_bkt_type IN NUMBER) IS
134 begin
135
136 insert into msc_plan_buckets(
137 plan_id,
138 organization_id,
139 sr_instance_id,
140 bucket_index,
141 curr_flag,
142 bkt_start_date,
143 bkt_end_date,
144 days_in_bkt,
145 bucket_type,
146 last_update_date,
147 last_updated_by,
148 creation_date,
149 created_by) values
150 (p_plan_id,
151 p_org_id,
152 p_sr_instance_id,
153 p_bkt_index,
154 p_curr_flag,
155 to_date(p_start_date, 'J'),
156 to_date(p_end_date, 'J'),
157 p_days_in_bkt,
158 p_bkt_type,
159 sysdate,
160 -1,
161 sysdate,
162 -1);
163
164 commit;
165 end;
166
167 FUNCTION COMPARE_MONTHS(p_date1 IN NUMBER,
168 p_date2 IN NUMBER)
169 return NUMBER IS
170 month1 NUMBER;
171 month2 NUMBER;
172 begin
173
174 select to_number(to_char(to_date(p_date1, 'j'), 'MM'))
175 INTO
176 month1 from dual;
177
178
179 select to_number(to_char(to_date(p_date2, 'j'), 'MM'))
180 INTO
181 month2 from dual;
182
183
184 if(month1 <> month2)
185 then
186 return NOT_EQUAL;
187 else
188 return EQUAL;
189 end if;
190
191 end;
192
193
194
195
196 FUNCTION CHECK_DAY_OF_WEEK(p_date IN NUMBER)
197 return NUMBER IS
198 day_of_week NUMBER;
199 begin
200
201 select to_char(to_date(p_date, 'j'), 'D')
202 into
203 day_of_week from dual;
204
205
206 return day_of_week;
207 end;
208
209 PROCEDURE ASSIGN_MONTHLY_BUCKETS(p_plan_id IN NUMBER,
210 p_org_id IN NUMBER,
211 p_sr_instance_id IN NUMBER,
212 curr_date IN NUMBER,
213 p_cal_code IN NUMBER,
214 p_no_of_mths IN NUMBER,
215 p_bkt_index IN OUT NOCOPY NUMBER) IS
216 loop number;
217 the_date number;
218 last_day_of_mth number;
219 days_in_month number;
220 day_of_week number;
221 BEGIN
222
223
224 the_date := curr_date;
225
226 FOR loop in 1..p_no_of_mths loop
227
228 /*------------------------------------+
229 | Get the number of days in the month |
230 +-------------------------------------*/
231
232 select
233 to_number(to_char(last_day(to_date(the_date, 'J')), 'J'))
234 into
235 last_day_of_mth
236 from dual;
237
238 /*--------------------------------------------------+
239 | If last day of month falls on weekend, please |
240 | set it accordingly for 5-2 calendar |
241 +---------------------------------------------------*/
242
243 if(p_cal_code = FIVE_DAY_WEEK) then
244
245 day_of_week := check_day_of_week(last_day_of_mth);
246
247 if(day_of_week = 1)
248 then
249 last_day_of_mth := last_day_of_mth - 2;
250 elsif (day_of_week = 7)
251 then
252 last_day_of_mth := last_day_of_mth - 1;
253 end if;
254
255 end if;
256
257
258 days_in_month := last_day_of_mth - the_date;
259
260
261 add_to_plan_buckets(p_plan_id,
262 p_org_id,
263 p_sr_instance_id,
264 p_bkt_index,
265 1,
266 the_date,
267 last_day_of_mth,
268 days_in_month,
269 3);
270
271
272 p_bkt_index := p_bkt_index + 1;
273 the_date := last_day_of_mth + 1;
274
275 /*----------------------------------------------------+
276 | If the last day falls on Saturday or Sunday, advance|
277 | and continue for 5-2 calendar |
278 +-----------------------------------------------------*/
279
280 if(p_cal_code = FIVE_DAY_WEEK) then
281
282 day_of_week := check_day_of_week(the_date);
283
284 if(day_of_week = 7) then
285 the_date := the_date + 2;
286 elsif (day_of_week = 1) then
287 the_date := the_date + 1;
288 end if;
289
290 end if;
291
292 END LOOP;
293
294 -- added exception handler
295 EXCEPTION
296 WHEN OTHERS THEN
297 /* Bug # 4235511 */
298 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
299 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,'MSC_EXCHANGE_BUCKETING.ASSIGN_MONTHLY_BUCKETS',SQLERRM);
300 end if ;
301
302 END ASSIGN_MONTHLY_BUCKETS;
303
304
305 PROCEDURE ASSIGN_WEEKLY_BUCKETS(p_plan_id IN NUMBER,
306 p_org_id IN NUMBER,
307 p_sr_instance_id IN NUMBER,
308 p_start_date IN NUMBER,
309 p_no_of_days IN NUMBER,
310 p_cal_code IN NUMBER,
311 p_bkt_index IN OUT NOCOPY NUMBER,
312 p_curr_date IN OUT NOCOPY NUMBER) IS
313
314 the_date NUMBER;
315 day_of_week NUMBER;
316 end_date NUMBER;
317 month_end NUMBER;
318 loop number;
319 diff number;
320 days_in_bkt number;
321 BEGIN
322
323
324 /*------------------------------------------------+
325 | Starting with the first date keep entering |
326 | weekly buckets into msc_plan_buckets until |
327 | the weekly buckets are over. |
328 +-------------------------------------------------*/
329
330 the_date := p_start_date;
331
332 if(p_no_of_days = 0)
333 then
334 return;
335
336 end if;
337
338 /*------------------------------------------------+
339 | Make sure the first date is a Monday. This will |
340 | only not happen of daily bucketing is set to |
341 | zero. If daily buckets are set that procedure |
342 | makes sure that the_date is a Monday. |
343 +-------------------------------------------------*/
344
345
346 /*------------------------------------------------+
347 | If the first date is not Monday create a short |
348 | week starting from today and ending on |
349 | or Friday for a five day week calendar |
350 +-------------------------------------------------*/
351
352
353 if(p_cal_code = FIVE_DAY_WEEK) then
354
355 day_of_week := check_day_of_week(the_date);
356
357 if(day_of_week = 1) /* Sunday */ then
358 the_date := the_date + 1;
359 elsif (day_of_week = 7) /* Saturday */ then
360 the_date := the_date + 2;
361 else
362
363 if(day_of_week in (3, 4, 5, 6)) /* Tue to Friday */ then
364
365 add_to_plan_buckets(p_plan_id,
366 p_org_id,
367 p_sr_instance_id,
368 p_bkt_index,
369 1,
370 the_date,
371 the_date + 6 - day_of_week,
372 6 - day_of_week + 1,
373 2);
374
375 p_bkt_index := p_bkt_index + 1;
376
377 /*-----------------------------------------+
378 | Move the current date to the next Monday |
379 +------------------------------------------*/
380 the_date := the_date + 6 - day_of_week + 3;
381
382 end if;
383
384 end if;
385
386 end if;
387
388 day_of_week := check_day_of_week(the_date);
389
390 if(p_cal_code = FIVE_DAY_WEEK) then
391 days_in_bkt := 5;
392 else
393 days_in_bkt := 7;
394
395 end if;
396
397
398
399 FOR loop in 1..p_no_of_days loop
400
401
402 add_to_plan_buckets(p_plan_id,
403 p_org_id,
404 p_sr_instance_id,
405 p_bkt_index,
406 1,
407 the_date,
408 the_date + days_in_bkt - 1,
409 days_in_bkt,
410 2);
411
412
413 p_bkt_index := p_bkt_index + 1;
414 the_date := the_date + 7;
415
416
417 END LOOP;
418
419
420 if(p_cal_code = FIVE_DAY_WEEK) then
421
422
423 /*--------------------------------------------------+
424 | See if the month ends on the weekend. If so just |
425 | return. |
426 +---------------------------------------------------*/
427
428 if(compare_months(the_date - 7 ,the_date) = NOT_EQUAL)
429 then
430 p_curr_date := the_date;
431 return;
432
433 end if;
434
435
436
437 /*--------------------------------------------------+
438 | Create weekly buckets until the end of the month |
439 +---------------------------------------------------*/
440
441 month_end := SYS_NO;
442
443 LOOP
444
445 FOR LOOP IN 1..5 loop
446
447
448 /*----------------------------------------+
449 | Keep comparing the months in the days |
450 | until the next month is reached or the |
451 | week is over. |
452 +-----------------------------------------*/
453
454
455 if(compare_months(the_date, the_date + loop) = NOT_EQUAL)
456 then
457
458 month_end := SYS_YES;
459 end_date := the_date + loop;
460 diff := loop;
461 exit;
462 end if;
463
464
465 END LOOP;
466
467
468 if(month_end = SYS_NO)
469 then
470 /*-------------------------------------------------+
471 | If no month is found then create a new week and |
472 | continue. |
473 +--------------------------------------------------*/
474
475 add_to_plan_buckets(p_plan_id,
476 p_org_id,
477 p_sr_instance_id,
478 p_bkt_index,
479 1,
480 the_date,
481 the_date + 4,
482 5,
483 2);
484
485
486 p_bkt_index := p_bkt_index + 1;
487 the_date := the_date + 7;
488
489 /*-----------------------------------------------+
493
490 | If the month ends in the weekend just exit and |
491 | return. |
492 +------------------------------------------------*/
494 if(compare_months(the_date - 7, the_date) = NOT_EQUAL)
495 then
496 exit;
497
498 end if;
499 /*-----------------------------------------------+
500 | Add code here to check if the month begins on |
501 | the weekend. |
502 +------------------------------------------------*/
503 else
504 /*-----------------------------------------------+
505 | If month is found create a weekly bucket until |
506 | the end of the month. |
507 +------------------------------------------------*/
508
509
510 add_to_plan_buckets(p_plan_id,
511 p_org_id,
512 p_sr_instance_id,
513 p_bkt_index,
514 1,
515 the_date,
516 the_date + diff - 1,
517 diff,
518 2);
519
520
521 p_bkt_index := p_bkt_index + 1;
522 the_date := the_date + diff;
523 exit;
524 end if;
525 END LOOP;
526
527 end if;
528
529 p_curr_date := the_date;
530
531 return;
532
533 -- added exception handler
534 EXCEPTION WHEN OTHERS THEN
535 /* Bug # 4235511 */
536 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
537 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'MSC_EXCHANGE_BUCKETING.ASSIGN_WEEKLY_BUCKETS',SQLERRM);
538 end if ;
539
540 END ASSIGN_WEEKLY_BUCKETS;
541
542
543
544 PROCEDURE ASSIGN_DAILY_BUCKETS(p_plan_id IN NUMBER,
545 p_org_id IN NUMBER,
546 p_sr_instance_id IN NUMBER,
547 p_start_date in NUMBER,
548 p_no_of_days IN NUMBER,
549 p_cal_code IN NUMBER,
550 p_bkt_index IN OUT NOCOPY NUMBER,
551 p_curr_date IN OUT NOCOPY NUMBER) IS
552 the_date NUMBER;
553 day_of_week NUMBER;
554 days_rem NUMBER;
555 begin
556
557
558
559
560 the_date := p_start_date;
561
562 /*-------------------------------------------------------+
563 | Starting with the first date keep inserting buckets |
564 | into msc_plan_buckets until the daily buckets are over |
565 +--------------------------------------------------------*/
566
567
568 if(p_no_of_days = 0)
569 then
570 return;
571
572 end if;
573
574 FOR loop in 1..p_no_of_days loop
575
576 if(p_cal_code = FIVE_DAY_WEEK) then
577
578 day_of_week := check_day_of_week(the_date);
579
580 if(day_of_week = 7)
581 then
582 the_date := the_date + 2;
583 elsif (day_of_week = 1)
584 then
585 the_date := the_date + 1;
586 end if;
587
588 end if;
589
590 add_to_plan_buckets(p_plan_id,
591 p_org_id,
592 p_sr_instance_id,
593 p_bkt_index,
594 1,
595 the_date,
596 the_date,
597 1,
598 1);
599
600
601 p_bkt_index := p_bkt_index + 1;
602 the_date := the_date + 1;
603
604
605 END LOOP;
606
607 /*------------------------------------------------------+
608 | If the daily buckets do not end on Friday create more |
609 | daily buckets until Friday. |
610 +-------------------------------------------------------*/
611
612 if(p_cal_code = FIVE_DAY_WEEK) then
613 day_of_week := check_day_of_week(the_date);
614
615 if(day_of_week = 1) /* Sunday */
616 then
617 /* Just increment the date and return */
618 the_date := the_date + 1;
619 p_curr_date := the_date;
620
621 elsif(day_of_week = 7) /* Saturday */
622 then
623 the_date := the_date + 2;
624 p_curr_date := the_date;
625 else
626
627 null;
628
629
630 /*-------------------------------------------------------------+
631 | Get the number of days between the_date and Friday and create|
632 | more daily buckets. |
633 +--------------------------------------------------------------*/
634
635 days_rem := 6 - day_of_week + 1;
636
637
638 FOR loop in 1..days_rem loop
639
640 add_to_plan_buckets(p_plan_id,
641 p_org_id,
642 p_sr_instance_id,
643 p_bkt_index,
644 1,
645 the_date,
646 the_date,
647 1,
648 1);
649
650
651 p_bkt_index := p_bkt_index + 1;
652 the_date := the_date + 1;
653
654 p_curr_date := the_date + 2;
655
656
657 END LOOP;
658
659 end if;
660 else /* 7 day calendar */
661
662 p_curr_date := the_date;
663
664 end if;
665
666
667 return;
668
669 -- added exception handler
670 EXCEPTION WHEN OTHERS THEN
671 /* Bug # 4235511 */
672 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
673 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,'MSC_EXCHANGE_BUCKETING.ASSIGN_DAILY_BUCKETS',SQLERRM);
674 end if ;
675
676 end ASSIGN_DAILY_BUCKETS;
677
678 PROCEDURE calculate_plan_buckets(
679 p_plan_id IN NUMBER,
680 p_org_id IN NUMBER,
681 p_sr_instance_id IN NUMBER,
682 p_daily_cutoff_bucket IN number,
683 p_weekly_cutoff_bucket IN number,
684 p_mthly_cutoff_bucket IN number
685 ) IS
686 first_date date;
687 jul_first_date number;
688 curr_date number;
689 day_of_week number;
690 p_cal_code number;
691 bkt_index number := 1;
692 BEGIN
693
694 /*------------------------------------------------------+
695 | Delete the old buckets for the plan. |
696 +-------------------------------------------------------*/
697
698 --delete msc_plan_buckets
699 --where plan_id = p_plan_id;
700
701 /*------------------------------------------------------+
702 | Get today's date. This is the first date from which |
703 | the buckets are calculated. |
704 +-------------------------------------------------------*/
705
706 select sysdate
707 into first_date
708 from dual;
709
710 select to_number(to_char(first_date, 'J'))
711 into jul_first_date
712 from dual;
713
714 /*-------------------------------------------------------+
715 | Get calendar code from the table msc_plan_organizations|
716 | At present the code is defined at the plan level, so |
717 | get any row. |
718 +--------------------------------------------------------*/
719
720 select nvl(calendar_code, 1)
721 into p_cal_code
722 from msc_plan_organizations
723 where plan_id = p_plan_id
724 and rownum = 1;
725
726 /*------------------------------------------------------+
727 | If Sysdate is a Saturday or Sunday move it to the |
728 | next workday. |
729 +-------------------------------------------------------*/
730
731 if(p_cal_code = FIVE_DAY_WEEK) then
732
733 if(day_of_week = 1) /* Sunday */
734 then
735 jul_first_date := jul_first_date + 1;
736 elsif (day_of_week = 7) /* Saturday */
737 then
738 jul_first_date := jul_first_date + 2;
739 end if;
740
741 end if;
742
743
744 curr_date := jul_first_date;
745
746 assign_daily_buckets(p_plan_id,
747 p_org_id,
748 p_sr_instance_id,
749 jul_first_date,
750 p_daily_cutoff_bucket,
751 p_cal_code,
752 bkt_index,
753 curr_date
754 );
755
756
757 assign_weekly_buckets(p_plan_id,
758 p_org_id,
759 p_sr_instance_id,
760 curr_date,
761 p_weekly_cutoff_bucket,
762 p_cal_code,
763 bkt_index,
764 curr_date);
765
766
767 assign_monthly_buckets(p_plan_id,
768 p_org_id,
769 p_sr_instance_id,
770 curr_date,
771 p_cal_code,
772 p_mthly_cutoff_bucket,
773 bkt_index);
774 commit;
775
776 -- added exception handler
777 exception when others then
778 /* Bug # 4235511 */
779 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
780 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'MSC_EXCHANGE_BUCKETING.calculate_plan_buckets',SQLERRM);
781 end if ;
782 end calculate_plan_buckets;
783
784 PROCEDURE CALC_MONTHLY_NETTING_BKTS(p_cutoff_date IN NUMBER,
785 p_start_date IN NUMBER,
786 p_supplier_id in number,
787 p_supplier_site_id in number,
788 p_customer_id in number,
789 p_customer_site_id in number,
790 p_item_id in number,
791 p_plan_type in number,
792 p_calendar_code IN VARCHAR2,
793 p_sr_instance_id IN NUMBER)
794 IS
795 p_bkt_index NUMBER := 0;
796
797 cursor c1 is
798 select distinct to_number(to_char(PERIOD_START_DATE,'j')) PERIOD_START_DATE,
799 to_number(to_char(NEXT_DATE-1,'j')) period_end_date
800 from msc_period_start_dates
801 where CALENDAR_CODE = p_calendar_code
802 and SR_INSTANCE_ID = p_sr_instance_id
803 and EXCEPTION_SET_ID = -1
804 and ( ( PERIOD_START_DATE <= to_date(p_start_date,'j')
805 and NEXT_DATE-1 >= to_date(p_start_date,'j') )
806 or ( PERIOD_START_DATE >= to_date(p_start_date,'j')
807 and NEXT_DATE-1 <= to_date(p_cutoff_date,'j') )
808 or ( PERIOD_START_DATE <= to_date(p_cutoff_date,'j')
809 and NEXT_DATE-1 >= to_date(p_cutoff_date,'j') )
810 );
811 BEGIN
812
813 FOR c_rec in c1 LOOP
814
815 EXIT WHEN C1%NOTFOUND;
816 p_bkt_index := p_bkt_index + 1;
817
818 add_to_plan_buckets(-1,
819 -1,
820 -1,
821 p_bkt_index,
822 p_supplier_id,
823 p_supplier_site_id,
824 p_customer_id,
825 p_customer_site_id,
826 p_item_id,
827 p_plan_type,
828 1,
829 c_rec.PERIOD_START_DATE,
830 c_rec.period_end_date,
831 c_rec.period_end_date - c_rec.PERIOD_START_DATE + 1,
832 MONTH);
833
834 END LOOP;
835
836 END CALC_MONTHLY_NETTING_BKTS;
837
838 PROCEDURE CALC_WEEKLY_NETTING_BKTS(p_cutoff_date IN NUMBER,
839 p_start_date IN NUMBER,
840 p_supplier_id in number,
841 p_supplier_site_id in number,
842 p_customer_id in number,
843 p_customer_site_id in number,
844 p_item_id in number,
845 p_plan_type in number,
846 p_calendar_code IN VARCHAR2,
847 p_sr_instance_id IN NUMBER)
848 IS
849 p_bkt_index NUMBER := 0;
850 p_days_in_bkt NUMBER := 7;
851
852 cursor c1 is
853 select distinct to_number(to_char(WEEK_START_DATE,'j')) week_start_date,
854 to_number(to_char(NEXT_DATE-1,'j')) week_end_date
855 from msc_cal_week_start_dates
856 where CALENDAR_CODE = p_calendar_code
857 and SR_INSTANCE_ID = p_sr_instance_id
858 and EXCEPTION_SET_ID = -1
859 and ( (WEEK_START_DATE <= to_date(p_start_date,'j')
860 and NEXT_DATE-1 >= to_date(p_start_date,'j'))
861 or (WEEK_START_DATE >= to_date(p_start_date,'j')
862 and NEXT_DATE-1 <= to_date(p_cutoff_date,'j') )
863 or (WEEK_START_DATE <= to_date(p_cutoff_date,'j')
864 and NEXT_DATE-1 >= to_date(p_cutoff_date,'j') )
865 );
866
867 BEGIN
868
869 --log_message(' Entered CALC_WEEKLY_NETTING_BKTS :' || p_cutoff_date || '- ' || p_calendar_code || '-' || p_sr_instance_id);
870 --log_message('adding bucket : ' || c_rec.week_start_date ||' - '||c_rec.week_end_date);
871 FOR c_rec in c1 LOOP
872
873 exit when c1%NOTFOUND;
874 p_bkt_index := p_bkt_index + 1;
875
876 add_to_plan_buckets(-1,
877 -1,
878 -1,
879 p_bkt_index,
880 p_supplier_id,
881 p_supplier_site_id,
882 p_customer_id,
883 p_customer_site_id,
884 p_item_id,
885 p_plan_type,
886 1,
887 c_rec.week_start_date,
888 c_rec.week_end_date,
889 p_days_in_bkt,
890 WEEK);
891
892
893 END LOOP;
894
895 END CALC_WEEKLY_NETTING_BKTS;
896
897
898
899 PROCEDURE CALC_DAILY_NETTING_BKTS(p_cutoff_date IN NUMBER,
900 p_start_date IN NUMBER,
901 p_supplier_id in number,
902 p_supplier_site_id in number,
903 p_customer_id in number,
904 p_customer_site_id in number,
905 p_item_id in number,
906 p_plan_type in number,
907 p_calendar_code IN VARCHAR2,
908 p_sr_instance_id IN NUMBER)
909 IS
910 l_start_date NUMBER := p_start_date;
911 p_end_date NUMBER := 0;
912 p_bkt_index NUMBER := 0;
913 p_days_in_bkt NUMBER := 1;
914 BEGIN
915
916 LOOP
917 exit when (l_start_date > p_cutoff_date);
918
919 p_end_date := l_start_date;
920
921 p_bkt_index := p_bkt_index + 1;
922 add_to_plan_buckets(-1,
923 -1,
924 -1,
925 p_bkt_index,
926 p_supplier_id,
927 p_supplier_site_id,
928 p_customer_id,
929 p_customer_site_id,
930 p_item_id,
931 p_plan_type,
932 1,
933 l_start_date,
934 p_end_date,
935 p_days_in_bkt,
936 DAY);
937 l_start_date := l_start_date + 1;
938
939 END LOOP;
940
941
942 END CALC_DAILY_NETTING_BKTS;
943
944 /*
945 PROCEDURE calc_mixed_bucket_dates(p_sr_instance_id IN NUMBER,
946 p_customer_id IN NUMBER,
947 p_customer_site_id IN NUMBER,
948 p_item_id IN NUMBER,
949 p_supplier_id IN NUMBER,
950 p_supplier_site_id IN NUMBER,
951 p_plan_type IN NUMBER,
952 p_cutoff_date IN NUMBER) IS
953 l_start_date NUMBER;
954 l_curr_date NUMBER;
955 l_cust_bkt_type NUMBER;
956 l_supp_bkt_type NUMBER;
957 l_curr_bkt_type NUMBER;
958 l_bkt_index NUMBER := 0;
959 l_bkt_start_date NUMBER;
960 l_bkt_end_date NUMBER;
961 l_days_in_bkt NUMBER;
962 BEGIN
963
964
965
966 -----------------------------------------------------------------+
967 | Will not start with sysdate
968 | Will start with the begining of the month and keep
969 | comparing dates to find the next bucket
970 | user may see the past exception using this bucket info
971 +--------------------------------------------------------
972
973 made this change to resolve GSCC warnings
974 -- select to_number(to_char(to_date('1' ||'-' || to_char (sysdate ,'MON-YYYY')), 'j'))
975 select to_number(to_char(sysdate,'j') )
976 into l_start_date
977 from dual;
978
979 l_curr_date := l_start_date;
980
981 loop
982 if(l_curr_date > p_cutoff_date) then
983 return;
984 end if;
985
986
987 BEGIN
988 select nvl(max(bucket_type), NONE)
989 into l_cust_bkt_type
990 from
991 msc_sup_dem_entries sd
992 where plan_id = -1
993 and sd.sr_instance_id = p_sr_instance_id
994 and sd.publisher_id =p_customer_id
995 and sd.publisher_site_id = p_customer_site_id
996 and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
997 and sd.publisher_order_type = DECODE(p_plan_type,
998 SUPPLY_PLANNING, 2,
999 DEMAND_PLANNING, 1)
1000 and sd.supplier_id = p_supplier_id
1001 and sd.supplier_site_id = p_supplier_site_id
1002 and to_number(to_char(sd.key_date, 'j')) = l_curr_date;
1003 EXCEPTION WHEN NO_DATA_FOUND THEN
1004 l_cust_bkt_type := NONE;
1005 END;
1006
1007
1008 IF (p_plan_type = SUPPLY_PLANNING) THEN
1009 BEGIN
1010 select nvl(max(bucket_type), NONE)
1011 into l_supp_bkt_type
1012 from
1013 msc_sup_dem_entries sd
1014 where sd.plan_id = -1
1015 and sd.sr_instance_id = p_sr_instance_id
1016 and sd.publisher_id = p_supplier_id
1017 and sd.publisher_site_id = p_supplier_site_id
1018 and sd.customer_id = p_customer_id
1019 and sd.customer_site_id = p_customer_site_id
1020 and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
1021 and sd.publisher_order_type in (3,14)
1022 and to_number(to_char(sd.key_date, 'j')) = l_curr_date;
1023 EXCEPTION WHEN NO_DATA_FOUND THEN
1024 l_supp_bkt_type := NONE;
1025 END;
1026
1027 ELSIF (p_plan_type = DEMAND_PLANNING) THEN
1028 BEGIN
1029 select nvl(max(bucket_type), NONE)
1030 into l_supp_bkt_type
1031 from
1032 msc_sup_dem_entries sd
1033 where sd.plan_id = -1
1034 and sd.sr_instance_id = p_sr_instance_id
1035 and sd.publisher_id = p_supplier_id
1036 and sd.publisher_site_id = p_supplier_site_id
1037 and sd.customer_id = p_customer_id
1038 and sd.customer_site_id = p_customer_site_id
1039 and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
1040 and sd.publisher_order_type = 1
1041 and to_number(to_char(sd.key_date, 'j')) = l_curr_date;
1042 EXCEPTION WHEN NO_DATA_FOUND THEN
1043 l_supp_bkt_type := NONE;
1044 END;
1045 END IF;
1046
1047 if(l_cust_bkt_type > l_supp_bkt_type) then
1048 l_curr_bkt_type := l_cust_bkt_type;
1049 else
1050 l_curr_bkt_type := l_supp_bkt_type;
1051
1052 end if;
1053
1054
1055
1056 if (l_curr_bkt_type = NONE) then
1057 --------------------------+
1058 | No data exists. Move to |
1059 | next bucket. |
1060 +---------------------------
1061 l_curr_date := l_curr_date + 1;
1062
1063 elsif (l_curr_bkt_type = DAY) then
1064
1065 l_bkt_start_date := l_curr_date;
1066 l_bkt_end_date := l_curr_date;
1067
1068 ----------------------------------+
1069 | Bucket is day. Create daily bucket|
1070 | and move to next bucket. |
1071 +----------------------------------
1072
1073 l_bkt_index := l_bkt_index + 1;
1074 l_days_in_bkt := 1;
1075 add_to_plan_buckets(-1,
1076 -1,
1077 -1,
1078 l_bkt_index,
1079 1,
1080 l_bkt_start_date,
1081 l_bkt_end_date,
1082 l_days_in_bkt,
1083 DAY);
1084
1085 l_curr_date := l_curr_date + 1;
1086
1087
1088 elsif (l_curr_bkt_type = WEEK) then
1089
1090 l_bkt_start_date := l_curr_date;
1091 l_bkt_end_date := l_curr_date + 6;
1092
1093 ------------------------------------+
1094 | Bucket is Week. Create weekly bucket|
1095 | and move to next bucket. |
1096 +--------------------------------------
1097
1098 l_bkt_index := l_bkt_index + 1;
1099 l_days_in_bkt := 7;
1100 add_to_plan_buckets(-1,
1101 -1,
1102 -1,
1103 l_bkt_index,
1104 1,
1105 l_bkt_start_date,
1106 l_bkt_end_date,
1107 l_days_in_bkt,
1108 WEEK);
1109
1110 l_curr_date := l_curr_date + 7;
1111
1112
1113 ----------------------------------------+
1114 | Need to add a check here that if a month|
1115 | starts before the week ends, create a |
1116 | short bucket for this week and continue |
1117 + : SBALA |
1118 +-----------------------------------------
1119 elsif (l_curr_bkt_type = MONTH) then
1120
1121
1122 l_bkt_start_date := l_curr_date;
1123
1124 select to_number(to_char(last_day(to_date(l_curr_date, 'j')), 'j'))
1125 into
1126 l_bkt_end_date
1127 from dual;
1128
1129
1130 --------------------------------------+
1131 | Bucket is month. Create monthly bucket|
1132 | and move to next month . |
1133 +---------------------------------------
1134 l_bkt_index := l_bkt_index + 1;
1135
1136 add_to_plan_buckets(-1,
1137 -1,
1138 -1,
1139 l_bkt_index,
1140 1,
1141 l_bkt_start_date,
1142 l_bkt_end_date,
1143 l_bkt_end_date - l_bkt_start_date + 1,
1144 MONTH);
1145
1146 l_curr_date := l_bkt_end_date + 1;
1147 end if;
1148
1149 if(l_curr_date > p_cutoff_date) then
1150 return;
1151
1152 end if;
1153
1154
1155 end loop;
1156
1157 end;
1158 */
1159 FUNCTION data_exists (p_sr_instance_id IN NUMBER,
1160 p_customer_id IN NUMBER,
1161 p_customer_site_id IN NUMBER,
1162 p_item_id IN NUMBER,
1163 p_supplier_id IN NUMBER,
1164 p_supplier_site_id IN NUMBER,
1165 p_plan_type IN NUMBER,
1166 p_bucket_type IN NUMBER,
1167 p_start_date IN NUMBER,
1168 p_end_date IN NUMBER)
1169 RETURN NUMBER IS
1170
1171 lv_data_exists number := 2;
1172
1173 CURSOR sup_planning IS
1174 select 1
1175 from msc_sup_dem_entries sd
1176 where sd.plan_id = -1
1177 and sd.sr_instance_id = p_sr_instance_id
1178 and sd.publisher_id = p_supplier_id
1179 and sd.publisher_site_id = p_supplier_site_id
1180 and sd.customer_id = p_customer_id
1181 and sd.customer_site_id = p_customer_site_id
1182 and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
1183 and sd.publisher_order_type in (G_SUPPLY_COMMIT,G_SALES_ORDER)
1184 and nvl(bucket_type,0) = p_bucket_type
1185 and to_number(to_char(sd.key_date, 'j')) between p_start_date and p_end_date
1186 and rownum = 1
1187 UNION
1188 select 1
1189 from msc_sup_dem_entries sd
1190 where sd.plan_id = -1
1191 and sd.sr_instance_id = p_sr_instance_id
1192 and sd.publisher_id = p_customer_id
1193 and sd.publisher_site_id = p_customer_site_id
1194 and sd.supplier_id = p_supplier_id
1195 and sd.supplier_site_id = p_supplier_site_id
1196 and nvl(sd.base_item_id,sd.inventory_item_id) = p_item_id
1197 and sd.publisher_order_type = G_ORDER_FORECAST
1198 and nvl(bucket_type,0) = p_bucket_type
1199 and to_number(to_char(sd.key_date, 'j')) between p_start_date and p_end_date
1200 and rownum = 1;
1201
1202 CURSOR dem_planning IS
1203 select 1
1204 from msc_sup_dem_entries sd
1205 where sd.plan_id = -1
1206 and sd.sr_instance_id = p_sr_instance_id
1207 and sd.publisher_id = p_supplier_id
1208 and sd.publisher_site_id = p_supplier_site_id
1209 and sd.customer_id = p_customer_id
1210 and sd.customer_site_id = p_customer_site_id
1211 and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
1212 and sd.publisher_order_type = G_SALES_FORECAST
1213 and nvl(bucket_type,0) = p_bucket_type
1214 and to_number(to_char(sd.key_date, 'j')) between p_start_date and p_end_date
1215 and rownum = 1
1216 UNION
1217 select 1
1218 from msc_sup_dem_entries sd
1219 where sd.plan_id = -1
1220 and sd.sr_instance_id = p_sr_instance_id
1221 and sd.publisher_id = p_customer_id
1222 and sd.publisher_site_id = p_customer_site_id
1223 and sd.supplier_id = p_supplier_id
1224 and sd.supplier_site_id = p_supplier_site_id
1225 and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
1226 and sd.publisher_order_type = G_SALES_FORECAST
1227 and nvl(bucket_type,0) = p_bucket_type
1228 and to_number(to_char(sd.key_date, 'j')) between p_start_date and p_end_date
1229 and rownum = 1;
1230
1231
1232 BEGIN
1233
1234 IF (p_plan_type = SUPPLY_PLANNING) THEN
1235
1236 BEGIN
1237 open sup_planning;
1238 fetch sup_planning into lv_data_exists;
1239 close sup_planning;
1240
1241 EXCEPTION
1242 WHEN NO_DATA_FOUND THEN
1243 lv_data_exists := 2;
1244 END;
1245
1246 ELSIF (p_plan_type = DEMAND_PLANNING) THEN
1247
1248 BEGIN
1249 open dem_planning;
1250 fetch dem_planning into lv_data_exists;
1251 close dem_planning;
1252
1253 EXCEPTION
1254 WHEN NO_DATA_FOUND THEN
1255 lv_data_exists := 2;
1256 END;
1257
1258 END IF; ---- plan_type
1259
1260 RETURN lv_data_exists;
1261
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 LOG_MESSAGE('An error occured in the function DATA_EXISTS in Bucketing : '||SQLERRM);
1265 RAISE;
1266
1267 END data_exists;
1268
1269
1270 PROCEDURE calc_calendar_mixed_buckets(p_sr_instance_id IN NUMBER,
1271 p_customer_id IN NUMBER,
1272 p_customer_site_id IN NUMBER,
1273 p_item_id IN NUMBER,
1274 p_supplier_id IN NUMBER,
1275 p_supplier_site_id IN NUMBER,
1276 p_plan_type IN NUMBER,
1277 p_cutoff_date IN NUMBER,
1278 p_calendar_code IN VARCHAR2,
1279 p_instance_id IN NUMBER) IS
1280
1281 l_start_date NUMBER;
1282 l_curr_date NUMBER;
1283 l_cust_bkt_type NUMBER;
1284 l_supp_bkt_type NUMBER;
1285 l_curr_bkt_type NUMBER;
1286 l_bkt_index NUMBER := 0;
1287 l_bkt_start_date NUMBER;
1288 l_bkt_end_date NUMBER;
1289 l_days_in_bkt NUMBER;
1290
1291 lv_m_start_date NUMBER;
1292 lv_m_end_date NUMBER;
1293 month_data_exists NUMBER;
1294
1295 lv_w_start_date NUMBER;
1296 lv_w_end_date NUMBER;
1297 week_data_exists NUMBER;
1298
1299 lv_d_start_date NUMBER;
1300 lv_d_end_date NUMBER;
1301 day_data_exists NUMBER;
1302
1303 week_in_month_data_exists NUMBER;
1304 day_in_month_data_exists NUMBER;
1305 day_in_week_data_exists NUMBER;
1306
1307 CURSOR c1(p_m_start_date in NUMBER,
1308 p_m_end_date in NUMBER,
1309 pCutoff_date in NUMBER,
1310 p_plan_type in NUMBER) is
1311 select to_number(to_char(sd.key_date, 'j')) key_date
1312 from msc_sup_dem_entries sd
1313 where sd.plan_id = -1
1314 and sd.sr_instance_id = p_sr_instance_id
1315 and sd.publisher_id = p_supplier_id
1316 and sd.publisher_site_id = p_supplier_site_id
1317 and sd.customer_id = p_customer_id
1318 and sd.customer_site_id = p_customer_site_id
1319 and nvl(sd.base_item_id,sd.inventory_item_id) = p_item_id
1320 and sd.publisher_order_type in (G_SUPPLY_COMMIT,G_SALES_ORDER)
1321 and nvl(bucket_type,0) = DAY
1322 and to_number(to_char(sd.key_date, 'j'))
1323 between p_m_start_date and p_m_end_date
1324 and to_number(to_char(sd.key_date, 'j')) <= pCutoff_date
1325 and p_plan_type = SUPPLY_PLANNING
1326 UNION
1327 select to_number(to_char(sd.key_date, 'j')) key_date
1328 from msc_sup_dem_entries sd
1329 where sd.plan_id = -1
1330 and sd.sr_instance_id = p_sr_instance_id
1331 and sd.publisher_id = p_customer_id
1332 and sd.publisher_site_id = p_customer_site_id
1333 and sd.supplier_id = p_supplier_id
1334 and sd.supplier_site_id = p_supplier_site_id
1335 and nvl(sd.base_item_id,sd.inventory_item_id) = p_item_id
1336 and sd.publisher_order_type = G_ORDER_FORECAST
1337 and nvl(bucket_type,0) = DAY
1338 and to_number(to_char(sd.key_date, 'j'))
1339 between p_m_start_date and p_m_end_date
1340 and to_number(to_char(sd.key_date, 'j')) <= pCutoff_date
1341 and p_plan_type = SUPPLY_PLANNING
1342 UNION
1343 select to_number(to_char(sd.key_date, 'j')) key_date
1344 from msc_sup_dem_entries sd
1345 where sd.plan_id = -1
1346 and sd.sr_instance_id = p_sr_instance_id
1347 and sd.publisher_id = p_supplier_id
1348 and sd.publisher_site_id = p_supplier_site_id
1349 and sd.customer_id = p_customer_id
1350 and sd.customer_site_id = p_customer_site_id
1351 and nvl(sd.base_item_id,sd.inventory_item_id) = p_item_id
1352 and sd.publisher_order_type = G_SALES_FORECAST
1353 and nvl(bucket_type,0) = DAY
1354 and to_number(to_char(sd.key_date, 'j'))
1355 between p_m_start_date and p_m_end_date
1356 and to_number(to_char(sd.key_date, 'j')) <= pCutoff_date
1357 and p_plan_type = DEMAND_PLANNING
1358 UNION
1359 select to_number(to_char(sd.key_date, 'j')) key_date
1360 from msc_sup_dem_entries sd
1361 where sd.plan_id = -1
1362 and sd.sr_instance_id = p_sr_instance_id
1363 and sd.publisher_id = p_customer_id
1364 and sd.publisher_site_id = p_customer_site_id
1365 and sd.supplier_id = p_supplier_id
1366 and sd.supplier_site_id = p_supplier_site_id
1367 and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
1368 and sd.publisher_order_type = G_SALES_FORECAST
1369 and nvl(bucket_type,0) = DAY
1370 and to_number(to_char(sd.key_date, 'j'))
1371 between p_m_start_date and p_m_end_date
1372 and to_number(to_char(sd.key_date, 'j')) <= pCutoff_date
1373 and p_plan_type = DEMAND_PLANNING
1374 ;
1375
1376
1377 BEGIN
1378
1379 --LOG_MESSAGE(' Calendar = :'||p_calendar_code);
1380 --LOG_MESSAGE(' Source Instance Id : '||p_instance_id);
1381
1382 /* start with the sysdate */
1383 l_curr_date := TO_NUMBER(TO_CHAR(sysdate,'J') );
1384
1385 LOOP
1386 /* if the cutoff date is reached; then exit the procedure */
1387 IF (l_curr_date > p_cutoff_date) then
1388 EXIT;
1389 ELSE
1390 l_curr_date := LEAST(l_curr_date,p_cutoff_date);
1391 END IF;
1392
1393 LOG_MESSAGE(' *********************************************************');
1394 LOG_MESSAGE(' Current Date : '||l_curr_date);
1395
1396 /* get the period start and end dates */
1397 select to_number(to_char(PERIOD_START_DATE,'J') ),
1398 to_number(to_char(NEXT_DATE-1,'J') )
1399 into lv_m_start_date ,
1400 lv_m_end_date
1401 from MSC_PERIOD_START_DATES
1402 where SR_INSTANCE_ID = p_instance_id
1403 and CALENDAR_CODE = p_calendar_code
1404 and EXCEPTION_SET_ID = -1
1405 and l_curr_date between to_number(to_char(PERIOD_START_DATE,'J'))
1406 and to_number(to_char(NEXT_DATE-1,'J'));
1407
1408 LOG_MESSAGE(' PERIOD start dates: '|| lv_m_start_date || '-' || lv_m_end_date );
1409
1410 /* check if the Monthly data exists between the period of l_curr_date */
1411 month_data_exists := data_exists(p_sr_instance_id ,
1412 p_customer_id ,
1413 p_customer_site_id ,
1414 p_item_id ,
1415 p_supplier_id ,
1416 p_supplier_site_id ,
1417 p_plan_type ,
1418 MONTH ,
1419 lv_m_start_date ,
1420 lv_m_end_date );
1421
1422 IF (month_data_exists = SYS_YES) THEN
1423
1424 /*--------------------------------------------------------------+
1425 | If monthly data exists then create the monthly bucket |
1426 +--------------------------------------------------------------*/
1427 LOG_MESSAGE(' ######## MONTHLY DATA EXISTS FOR : '
1428 || lv_m_start_date || '-' || lv_m_end_date);
1429
1430 l_bkt_start_date := lv_m_start_date;
1431 l_bkt_end_date := lv_m_end_date;
1432
1433 /*--------------------------------------------------------------+
1434 | Bucket is month. Create monthly bucket and move to next month |
1435 +--------------------------------------------------------------*/
1436 l_bkt_index := l_bkt_index + 1;
1437
1438 add_to_plan_buckets(-1,
1439 -1,
1440 -1,
1441 l_bkt_index,
1442 p_supplier_id,
1443 p_supplier_site_id,
1444 p_customer_id,
1445 p_customer_site_id,
1446 p_item_id,
1447 p_plan_type,
1448 1,
1449 l_bkt_start_date,
1450 l_bkt_end_date,
1451 l_bkt_end_date - l_bkt_start_date + 1,
1452 MONTH);
1453
1454 /* move to the next month */
1455 l_curr_date := l_bkt_end_date + 1;
1456
1457 ELSIF (month_data_exists = SYS_NO) THEN --- monthly data does not exists
1458
1459 LOG_MESSAGE(' ######## MONTHLY DATA DOES NOT EXISTS FOR : '
1460 || lv_m_start_date || '-' || lv_m_end_date);
1461
1462 /* check if weekly data exists in the month */
1463 week_in_month_data_exists := data_exists(p_sr_instance_id ,
1464 p_customer_id ,
1465 p_customer_site_id ,
1466 p_item_id ,
1467 p_supplier_id ,
1468 p_supplier_site_id ,
1469 p_plan_type ,
1470 WEEK ,
1471 lv_m_start_date ,
1472 lv_m_end_date );
1473
1474 if (week_in_month_data_exists = SYS_YES) then
1475 LOG_MESSAGE(' @@@@@@@ WEEKLY data EXISTS in Month : '
1476 || lv_m_start_date || '--' || lv_m_end_date);
1477 else
1478 LOG_MESSAGE(' @@@@@@@ WEEKLY data DOES NOT EXISTS in Month : '
1479 ||lv_m_start_date || '--' || lv_m_end_date);
1480 end if;
1481
1482 /* check if Daily data exists in the month */
1483 day_in_month_data_exists := data_exists(p_sr_instance_id ,
1484 p_customer_id ,
1485 p_customer_site_id ,
1486 p_item_id ,
1487 p_supplier_id ,
1488 p_supplier_site_id ,
1489 p_plan_type ,
1490 DAY,
1491 lv_m_start_date ,
1492 lv_m_end_date );
1493
1494 if (day_in_month_data_exists = SYS_YES) then
1495 LOG_MESSAGE(' %%%%%%% Daily data EXISTS in Month : '
1496 || lv_m_start_date || '--' || lv_m_end_date);
1497 else
1498 LOG_MESSAGE(' %%%%%%% DAILY data DOES NOT EXISTS in Month : '
1499 || lv_m_start_date || '--' || lv_m_end_date);
1500 end if;
1501
1502 IF (week_in_month_data_exists = SYS_YES) THEN
1503
1504 LOOP --- loop through the week within the month
1505
1506 -- if the cutoff date is reached then exit the program
1507 IF (l_curr_date > p_cutoff_date) then
1508 EXIT;
1509 ELSE
1510 l_curr_date := LEAST(l_curr_date,p_cutoff_date);
1511 END IF;
1512
1513 -- exit from the Weekly loop when next month is reached
1514 EXIT WHEN (l_curr_date > lv_m_end_date);
1515
1516 -- get the Week start and end dates
1517 select to_number(to_char(WEEK_START_DATE,'J') ) ,
1518 to_number(to_char(NEXT_DATE-1,'J') )
1519 into lv_w_start_date,
1520 lv_w_end_date
1521 from MSC_CAL_WEEK_START_DATES
1522 where SR_INSTANCE_ID = p_instance_id
1523 and CALENDAR_CODE = p_calendar_code
1524 and EXCEPTION_SET_ID = -1
1525 and l_curr_date between to_number(to_char(WEEK_START_DATE,'J'))
1526 and to_number(to_char(NEXT_DATE-1,'J'));
1527
1528 /* check if Weekly data exists in the week of l_curr_date */
1529 week_data_exists := data_exists(p_sr_instance_id ,
1530 p_customer_id ,
1531 p_customer_site_id ,
1532 p_item_id ,
1533 p_supplier_id ,
1534 p_supplier_site_id ,
1535 p_plan_type ,
1536 WEEK ,
1537 lv_w_start_date ,
1538 lv_w_end_date );
1539
1540 IF (week_data_exists = SYS_YES) THEN
1541
1542 LOG_MESSAGE(' Weekly data exists in Week : '
1543 || lv_w_start_date || '--' || lv_w_end_date);
1544 l_bkt_start_date := lv_w_start_date;
1545 l_bkt_end_date := lv_w_end_date;
1546
1547 /*------------------------------------------------------------------+
1548 | Bucket is week. Create Weekly bucket and then move to next week |
1549 +------------------------------------------------------------------*/
1550 l_bkt_index := l_bkt_index + 1;
1551 l_days_in_bkt := 7;
1552
1553 add_to_plan_buckets(-1,
1554 -1,
1555 -1,
1556 l_bkt_index,
1557 p_supplier_id,
1558 p_supplier_site_id,
1559 p_customer_id,
1560 p_customer_site_id,
1561 p_item_id,
1562 p_plan_type,
1563 1,
1564 l_bkt_start_date,
1565 l_bkt_end_date,
1566 l_days_in_bkt,
1567 WEEK);
1568
1569 l_curr_date := l_bkt_end_date + 1;
1570 ELSE
1571 /* there is no weekly data in the week of l_curr_date,
1572 check for daily data in the month -- this is added for performance */
1573 LOG_MESSAGE(' Weekly data DOES NOT exists in Week : '
1574 || lv_w_start_date || '--' || lv_w_end_date);
1575
1576 /* This check that day data exists in month is for performance */
1577 if (day_in_month_data_exists = SYS_YES) then
1578
1579 day_in_week_data_exists := data_exists(p_sr_instance_id ,
1580 p_customer_id ,
1581 p_customer_site_id ,
1582 p_item_id ,
1583 p_supplier_id ,
1584 p_supplier_site_id ,
1585 p_plan_type ,
1586 DAY ,
1587 lv_w_start_date ,
1588 lv_w_end_date );
1589
1590 /* check for daily data in the week */
1591 IF (day_in_week_data_exists = SYS_YES) then
1592
1593 LOG_MESSAGE(' Daily data exists in Week: '
1594 || lv_w_start_date || '--' || lv_w_end_date);
1595
1596 /* daily data exists in the week,
1597 loop through all the days in the week */
1598
1599 FOR c_rec in c1(lv_w_start_date, lv_w_end_date
1600 , least(p_cutoff_date,lv_w_end_date,lv_m_end_date)
1601 ,p_plan_type)
1602 LOOP
1603 /* loop through all the daily bucket data
1604 in the week and create daily buckets */
1605
1606 l_curr_date := c_rec.key_date;
1607
1608 LOG_MESSAGE('Daily bucket: '||l_curr_date);
1609
1610 l_bkt_start_date := l_curr_date;
1611 l_bkt_end_date := l_curr_date;
1612
1613 /*---------------------+
1614 | Create Daily bucket |
1615 +---------------------*/
1616 l_bkt_index := l_bkt_index + 1;
1617 l_days_in_bkt := 1;
1618
1619 add_to_plan_buckets(-1,
1620 -1,
1621 -1,
1622 l_bkt_index,
1623 p_supplier_id,
1624 p_supplier_site_id,
1625 p_customer_id,
1626 p_customer_site_id,
1627 p_item_id,
1628 p_plan_type,
1629 1,
1630 l_bkt_start_date,
1631 l_bkt_end_date,
1632 l_days_in_bkt,
1633 DAY);
1634
1635 END LOOP; --- loop for the days within the week
1636
1637 l_curr_date := l_curr_date + 7;
1638
1639 ELSE --- else part of day_in_week data exists
1640
1641 l_curr_date := l_curr_date + 7;
1642 LOG_MESSAGE('No daily data exists between week : '
1643 || lv_w_start_date ||'-'||lv_w_end_date);
1644
1645 END IF; --- day_in_week data exists
1646
1647 else
1648 /* No daily bucket data exists in the month -- so go to next week */
1649 LOG_MESSAGE('No daily data exists between month : ' ||
1650 lv_m_start_date ||'-'||lv_m_end_date ||
1651 '---- week' || lv_w_start_date ||'-'||lv_w_end_date);
1652 l_curr_date := l_curr_date + 7;
1653
1654 end if; ---- day_in_month data exists
1655
1656 END IF; --- week data exists
1657
1658 END LOOP; ---- loop for the week within a month
1659
1660
1661 ELSIF (day_in_month_data_exists = SYS_YES) THEN
1662
1663 /* only daily bucket data exists within the period */
1664
1665 lv_m_end_date := LEAST(lv_m_end_date,p_cutoff_date);
1666 LOG_MESSAGE('Month start date: ' || lv_m_start_date
1667 ||'- Month End date: '||lv_m_end_date);
1668
1669 FOR c_rec in c1(lv_m_start_date, lv_m_end_date
1670 , p_cutoff_date ,p_plan_type)
1671 LOOP
1672 /* loop through all the daily bucket data
1673 in the month and create daily buckets */
1674
1675 l_curr_date := c_rec.key_date;
1676
1677 LOG_MESSAGE('Daily bucket: '||l_curr_date);
1678
1679 l_bkt_start_date := l_curr_date;
1680 l_bkt_end_date := l_curr_date;
1681
1682 /*---------------------+
1683 | Create Daily bucket |
1684 +---------------------*/
1685 l_bkt_index := l_bkt_index + 1;
1686 l_days_in_bkt := 1;
1687
1688 add_to_plan_buckets(-1,
1689 -1,
1690 -1,
1691 l_bkt_index,
1692 p_supplier_id,
1693 p_supplier_site_id,
1694 p_customer_id,
1695 p_customer_site_id,
1696 p_item_id,
1697 p_plan_type,
1698 1,
1699 l_bkt_start_date,
1700 l_bkt_end_date,
1701 l_days_in_bkt,
1702 DAY);
1703
1704 END LOOP; --- loop for the days within the month
1705
1706 l_curr_date := lv_m_end_date + 1; --- go to next month
1707
1708 ELSE
1709 /* NO Weekly/daily bucket data exists within the period */
1710 l_curr_date := lv_m_end_date + 1; --- go to next month
1711 LOG_MESSAGE(' lv_m_end_date : '||lv_m_end_date);
1712 END IF;
1713
1714 END IF; --- month_data_exists or not
1715
1716 END LOOP; -- main loop for Months
1717
1718 EXCEPTION
1719 WHEN OTHERS THEN
1720 LOG_MESSAGE(SQLCODE);
1721 LOG_MESSAGE(SQLERRM);
1722 RAISE;
1723
1724 END calc_calendar_mixed_buckets;
1725
1726 PROCEDURE CALCULATE_NETTING_BUCKET(
1727 p_sr_instance_id IN NUMBER,
1728 p_customer_id IN NUMBER,
1729 p_customer_site_id IN NUMBER,
1730 p_supplier_id IN NUMBER,
1731 p_supplier_site_id IN NUMBER,
1732 p_item_id IN NUMBER,
1733 p_plan_type IN NUMBER,
1734 p_cutoff_ref_num IN OUT NOCOPY NUMBER)
1735
1736 IS
1737 l_cust_bucket_type NUMBER := 0;
1738 l_supp_bucket_type NUMBER := 0;
1739 l_max_receipt_cust date;
1740 l_max_receipt_supp date;
1741 l_max_cust NUMBER := 0;
1742 l_max_supp NUMBER := 0;
1743 l_max_ref_cust NUMBER := 0;
1744 l_max_ref_supp NUMBER := 0;
1745 p_cutoff_date NUMBER;
1746 p_start_date NUMBER;
1747
1748 lv_calendar_code varchar2(14);
1749 lv_instance_id number;
1750 l_min_receipt_cust date;
1751 l_min_receipt_supp date;
1752 l_min_cust NUMBER := 0;
1753 l_min_supp NUMBER := 0;
1754
1755 l_customer_name msc_companies.company_name%type;
1756 l_customer_site_name msc_company_sites.company_site_name%type;
1757 l_supplier_name msc_companies.company_name%type;
1758 l_supplier_site_name msc_company_sites.company_site_name%type;
1759
1760 BEGIN
1761 log_message(' p_sr_instance_id : ' || p_sr_instance_id);
1762 log_message(' p_customer_id : ' || p_customer_id);
1763 log_message(' p_customer_site_id : ' || p_customer_site_id);
1764 log_message(' p_supplier_id : ' || p_supplier_id);
1765 log_message(' p_supplier_site_id : ' || p_supplier_site_id);
1766 log_message(' p_item_id : ' || p_item_id);
1767 log_message(' p_cutoff_ref_num : ' || p_cutoff_ref_num);
1768
1769
1770
1771 /*-------------------------------------------------------------
1772 Print out the calendar to log for the exception.
1773 --------------------------------------------------------------*/
1774 /*
1775 select c1.company_name, c2.company_site_name
1776 into l_supplier_name, l_supplier_site_name
1777 from msc_companies c1, msc_company_sites c2
1778 where c1.company_id = p_supplier_id
1779 and c1.company_id = c2.company_id
1780 and c2.company_site_id = p_supplier_site_id;
1781
1782 select c1.company_name, c2.company_site_name
1783 into l_customer_name, l_customer_site_name
1784 from msc_companies c1, msc_company_sites c2
1785 where c1.company_id = p_customer_id
1786 and c1.company_id = c2.company_id
1787 and c2.company_site_id = p_customer_site_id;
1788
1789 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Calendar code ' || lv_calendar_code || ' for Supplier: ' || l_supplier_name || ' ' ||
1790 l_supplier_site_name || ' and Customer: ' || l_customer_name || ' ' || l_customer_site_name);
1791 */
1792 /*------------------------------------------------------+
1793 | Delete the previous set of data from msc_plan_buckets |
1794 +-------------------------------------------------------*/
1795
1796 --delete msc_plan_buckets
1797 --where plan_id = -1;
1798
1799
1800 /*----------------------------------------------+
1801 | Get the maximum value of the refresh number |
1802 | The refresh number will |
1803 | be used to ignore new data loaded after the |
1804 | bucketing has been done for this item/cust/ |
1805 | supplier combination |
1806 | The SQL is used different order types for |
1807 | supply and demand planning |
1808 +-----------------------------------------------*/
1809
1810 /*----------------------------------------------+
1811 | Also Get the maximum value of the receipt date|
1812 | The receipt date will be used to determine the
1813 | bucket end date
1814 +-----------------------------------------------*/
1815
1816 /* l_max_ref_cust := p_cust_max_rn;
1817 l_max_receipt_cust := p_cust_max_key_date;
1818 l_min_receipt_cust := p_cust_min_key_date;
1819
1820 l_max_ref_supp := p_supp_max_rn;
1821 l_max_receipt_supp := p_supp_max_key_date;
1822 l_min_receipt_supp := p_supp_min_key_date;
1823
1824 */
1825
1826 BEGIN
1827 select nvl(max(sd.last_refresh_number), -1), max(sd.key_date),min(sd.key_date)
1828 into l_max_ref_cust, l_max_receipt_cust, l_min_receipt_cust
1829 from
1830 msc_sup_dem_entries sd
1831 where sd.plan_id = -1
1832 and sd.sr_instance_id = p_sr_instance_id
1833 and sd.publisher_id =p_customer_id
1834 and sd.publisher_site_id = p_customer_site_id
1835 and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
1836 and sd.publisher_order_type = DECODE(p_plan_type,
1837 SUPPLY_PLANNING, 2,
1838 DEMAND_PLANNING, 1)
1839 and sd.supplier_id = p_supplier_id
1840 and sd.supplier_site_id = p_supplier_site_id;
1841 EXCEPTION WHEN NO_DATA_FOUND then
1842 l_max_ref_cust := -1;
1843 l_max_cust := 0;
1844 END;
1845
1846 /*
1847 log_message('l_max_ref_cust :' || l_max_ref_cust);
1848 log_message('l_max_cust :' || l_max_cust);
1849 log_message('l_max_receipt_cust :' || l_max_receipt_cust);
1850 */
1851
1852 IF (p_plan_type = SUPPLY_PLANNING) THEN
1853 BEGIN
1854 select nvl(max(sd.last_refresh_number), -1),
1855 max(sd.key_date),min(sd.key_date)
1856 into l_max_ref_supp, l_max_receipt_supp,l_min_receipt_supp
1857 from
1858 msc_sup_dem_entries sd
1859 where
1860 sd.plan_id = -1
1861 and sd.sr_instance_id = p_sr_instance_id
1862 and sd.publisher_id = p_supplier_id
1863 and sd.publisher_site_id = p_supplier_site_id
1864 and sd.customer_id = p_customer_id
1865 and sd.customer_site_id = p_customer_site_id
1866 and nvl(sd.base_item_id,sd.inventory_item_id) = p_item_id
1867 and sd.publisher_order_type in (3,14);
1868 EXCEPTION WHEN NO_DATA_FOUND THEN
1869 l_max_ref_supp := -1;
1870 l_max_supp := 0;
1871 END;
1872 ELSIF (p_plan_type = DEMAND_PLANNING) THEN
1873 delete msc_plan_buckets
1874 where plan_id = -1;
1875 BEGIN
1876 select nvl(max(sd.last_refresh_number), -1),
1877 max(sd.key_date),min(sd.key_date)
1878 into l_max_ref_supp, l_max_receipt_supp,l_min_receipt_supp
1879 from
1880 msc_sup_dem_entries sd
1881 where
1882 sd.plan_id = -1
1883 and sd.sr_instance_id = p_sr_instance_id
1884 and sd.publisher_id = p_supplier_id
1885 and sd.publisher_site_id = p_supplier_site_id
1886 and sd.customer_id = p_customer_id
1887 and sd.customer_site_id = p_customer_site_id
1888 and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
1889 and sd.publisher_order_type = 1;
1890 EXCEPTION WHEN NO_DATA_FOUND THEN
1891 l_max_ref_supp := -1;
1892 l_max_supp := 0;
1893 END;
1894 END IF;
1895
1896 /*
1897 log_message('l_max_ref_supp :' || l_max_ref_supp);
1898 log_message('l_max_receipt_supp :' || l_max_receipt_supp);
1899 log_message('l_max_supp :' || l_max_supp);
1900 */
1901
1902 if(l_max_ref_cust > l_max_ref_supp) then
1903 p_cutoff_ref_num := l_max_ref_cust;
1904 else
1905 p_cutoff_ref_num := l_max_ref_supp;
1906 end if;
1907
1908 /*-------------------------------------------------------------------
1909 | There is no bucket at all, return and the
1910 | cutoff_ref_num back to -1; therefore, no need to query
1911 | supply/demand in the netting engine
1912 -----------------------------------------------------------------*/
1913 IF (l_max_receipt_cust is null or l_max_receipt_supp is null) THEN
1914 p_cutoff_ref_num := -1;
1915 return;
1916 END IF;
1917
1918 /* Call the API to get the correct Calendar */
1919 msc_x_util.get_calendar_code(
1920 p_supplier_id,
1921 p_supplier_site_id,
1922 p_customer_id,
1923 p_customer_site_id,
1924 lv_calendar_code,
1925 lv_instance_id);
1926 --log_message(' Calendar used: ' || lv_calendar_code);
1927 log_message(' Source instance id : ' || lv_instance_id);
1928
1929 --bug# 2343118, need to initial the l_max_supp and l_max_cust
1930 l_max_supp := to_number(to_char(l_max_receipt_supp,'j'));
1931 l_max_cust := to_number(to_char(l_max_receipt_cust,'j'));
1932
1933 IF (l_max_receipt_cust is null) then
1934 l_max_cust := 0 ;
1935 end if;
1936 IF (l_max_receipt_supp is null) then
1937 l_max_supp := 0;
1938 end if;
1939
1940 IF (l_max_cust > l_max_supp) then
1941 p_cutoff_date := l_max_cust;
1942 ELSE
1943 p_cutoff_date := l_max_supp;
1944 END IF;
1945
1946 l_min_supp := to_number(to_char(nvl(l_min_receipt_supp,sysdate),'j'));
1947 l_min_cust := to_number(to_char(nvl(l_min_receipt_cust,sysdate),'j'));
1948
1949 IF (l_min_supp < l_min_cust) then
1950 p_start_date := l_min_supp;
1951 ELSE
1952 p_start_date := l_min_cust;
1953 END IF;
1954
1955 LOG_MESSAGE('START DATE = ' || p_start_date );
1956 LOG_MESSAGE('CUTOFF DATE = ' || p_cutoff_date );
1957
1958 /*----------------------------------------------+
1959 | Get the distinct bucket types for data |
1960 | posted by customer |
1961 | If more than one bucket type is present then |
1962 | this implies that the bucket type is MIXED. |
1963 +-----------------------------------------------*/
1964
1965 BEGIN
1966 select distinct bucket_type
1967 into l_cust_bucket_type
1968 from msc_sup_dem_entries sd
1969 where sd.plan_id = -1
1970 and sd.sr_instance_id = p_sr_instance_id
1971 and sd.publisher_id =p_customer_id
1972 and sd.publisher_site_id = p_customer_site_id
1973 and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
1974 and sd.publisher_order_type = DECODE(p_plan_type,
1975 SUPPLY_PLANNING, 2,
1976 DEMAND_PLANNING, 1)
1977 and sd.supplier_id = p_supplier_id
1978 and sd.supplier_site_id = p_supplier_site_id;
1979 EXCEPTION WHEN TOO_MANY_ROWS THEN
1980 l_cust_bucket_type := MIXED;
1981 WHEN NO_DATA_FOUND THEN
1982 l_cust_bucket_type := NONE;
1983 END;
1984
1985 log_message('l_cust_bucket_type : '||l_cust_bucket_type);
1986
1987 if(l_cust_bucket_type = MIXED) /* mixed bucket types */
1988 then
1989 calc_calendar_mixed_buckets( p_sr_instance_id,
1990 p_customer_id,
1991 p_customer_site_id,
1992 p_item_id,
1993 p_supplier_id,
1994 p_supplier_site_id,
1995 p_plan_type,
1996 p_cutoff_date,
1997 lv_calendar_code,
1998 lv_instance_id);
1999
2000 else
2001
2002 /*--------------------------------------+
2003 | Get the distinct bucket type for the |
2004 | suppliers data. The where clause for |
2005 | the select here depends on the plan |
2006 +---------------------------------------*/
2007 IF (p_plan_type = SUPPLY_PLANNING) THEN
2008
2009 BEGIN
2010 select distinct bucket_type into l_supp_bucket_type
2011 from msc_sup_dem_entries sd
2012 where sd.plan_id = -1
2013 and sd.sr_instance_id = p_sr_instance_id
2014 and sd.publisher_id = p_supplier_id
2015 and sd.publisher_site_id = p_supplier_site_id
2016 and sd.customer_id = p_customer_id
2017 and sd.customer_site_id = p_customer_site_id
2018 and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
2019 and sd.publisher_order_type in (3,14);
2020
2021 EXCEPTION WHEN TOO_MANY_ROWS THEN
2022 l_supp_bucket_type := MIXED;
2023 WHEN NO_DATA_FOUND THEN
2024 l_supp_bucket_type := NONE;
2025 END;
2026 ELSIF (p_plan_type = DEMAND_PLANNING) THEN
2027 BEGIN
2028 select distinct bucket_type into l_supp_bucket_type
2029 from msc_sup_dem_entries sd
2030 where sd.plan_id = -1
2031 and sd.sr_instance_id = p_sr_instance_id
2032 and sd.publisher_id = p_supplier_id
2033 and sd.publisher_site_id = p_supplier_site_id
2034 and sd.customer_id = p_customer_id
2035 and sd.customer_site_id = p_customer_site_id
2036 and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
2037 and sd.publisher_order_type = 1;
2038
2039 EXCEPTION WHEN TOO_MANY_ROWS THEN
2040 l_supp_bucket_type := MIXED;
2041 WHEN NO_DATA_FOUND THEN
2042 l_supp_bucket_type := NONE;
2043 END;
2044 END IF;
2045
2046 log_message('l_supp_bucket_type : '||l_supp_bucket_type);
2047 if(l_supp_bucket_type = MIXED) /* mixed bucket types */
2048 then
2049
2050 calc_calendar_mixed_buckets(
2051 p_sr_instance_id,
2052 p_customer_id,
2053 p_customer_site_id,
2054 p_item_id,
2055 p_supplier_id,
2056 p_supplier_site_id,
2057 p_plan_type,
2058 p_cutoff_date,
2059 lv_calendar_code,
2060 lv_instance_id);
2061
2062
2063 else
2064 /*-------------------------------------------------------+
2065 | If the customer and supplier have different bucket |
2066 | types this also implies that the data has mixed |
2067 | bucket types. The only exception to this case is when |
2068 | either customer or supplier data is not there |
2069 | In that case we generate using the other party's data|
2070 +-------------------------------------------------------*/
2071
2072 if(l_supp_bucket_type <> l_cust_bucket_type) then
2073
2074 if((l_supp_bucket_type <> NONE) and
2075 (l_cust_bucket_type <> NONE)) then
2076
2077 calc_calendar_mixed_buckets(
2078 p_sr_instance_id,
2079 p_customer_id,
2080 p_customer_site_id,
2081 p_item_id,
2082 p_supplier_id,
2083 p_supplier_site_id,
2084 p_plan_type,
2085 p_cutoff_date,
2086 lv_calendar_code,
2087 lv_instance_id);
2088
2089 elsif (l_supp_bucket_type = NONE) then
2090 if(l_cust_bucket_type = DAY) then
2091 calc_daily_netting_bkts(p_cutoff_date,
2092 p_start_date,
2093 p_supplier_id,
2094 p_supplier_site_id,
2095 p_customer_id,
2096 p_customer_site_id,
2097 p_item_id,
2098 p_plan_type,
2099 lv_calendar_code,
2100 lv_instance_id);
2101 elsif(l_cust_bucket_type = WEEK) then
2102 calc_weekly_netting_bkts(p_cutoff_date,
2103 p_start_date,
2104 p_supplier_id,
2105 p_supplier_site_id,
2106 p_customer_id,
2107 p_customer_site_id,
2108 p_item_id,
2109 p_plan_type,
2110 lv_calendar_code,
2111 lv_instance_id);
2112 elsif(l_cust_bucket_type = MONTH) then
2113 calc_monthly_netting_bkts(p_cutoff_date,
2114 p_start_date,
2115 p_supplier_id,
2116 p_supplier_site_id,
2117 p_customer_id,
2118 p_customer_site_id,
2119 p_item_id,
2120 p_plan_type,
2121 lv_calendar_code,
2122 lv_instance_id);
2123 end if;
2124 elsif (l_cust_bucket_type = NONE) then
2125 if(l_supp_bucket_type = DAY) then
2126 calc_daily_netting_bkts(p_cutoff_date,
2127 p_start_date,
2128 p_supplier_id,
2129 p_supplier_site_id,
2130 p_customer_id,
2131 p_customer_site_id,
2132 p_item_id,
2133 p_plan_type,
2134 lv_calendar_code,
2135 lv_instance_id);
2136 elsif(l_supp_bucket_type = WEEK) then
2137 calc_weekly_netting_bkts(p_cutoff_date,
2138 p_start_date,
2139 p_supplier_id,
2140 p_supplier_site_id,
2141 p_customer_id,
2142 p_customer_site_id,
2143 p_item_id,
2144 p_plan_type,
2145 lv_calendar_code,
2146 lv_instance_id);
2147 elsif(l_supp_bucket_type = MONTH) then
2148 calc_monthly_netting_bkts(p_cutoff_date,
2149 p_start_date,
2150 p_supplier_id,
2151 p_supplier_site_id,
2152 p_customer_id,
2153 p_customer_site_id,
2154 p_item_id,
2155 p_plan_type,
2156 lv_calendar_code,
2157 lv_instance_id);
2158 end if;
2159
2160 end if;
2161
2162
2163 else /* Bucket Types are equal and not null */
2164 if(l_supp_bucket_type = DAY) then
2165 calc_daily_netting_bkts(p_cutoff_date,
2166 p_start_date,
2167 p_supplier_id,
2168 p_supplier_site_id,
2169 p_customer_id,
2170 p_customer_site_id,
2171 p_item_id,
2172 p_plan_type,
2173 lv_calendar_code,
2174 lv_instance_id);
2175 elsif(l_supp_bucket_type = WEEK) then
2176 calc_weekly_netting_bkts(p_cutoff_date,
2177 p_start_date,
2178 p_supplier_id,
2179 p_supplier_site_id,
2180 p_customer_id,
2181 p_customer_site_id,
2182 p_item_id,
2183 p_plan_type,
2184 lv_calendar_code,
2185 lv_instance_id);
2186 elsif(l_supp_bucket_type = MONTH) then
2187 calc_monthly_netting_bkts(p_cutoff_date,
2188 p_start_date,
2189 p_supplier_id,
2190 p_supplier_site_id,
2191 p_customer_id,
2192 p_customer_site_id,
2193 p_item_id,
2194 p_plan_type,
2195 lv_calendar_code,
2196 lv_instance_id);
2197 end if;
2198
2199 end if; /* end if for bucket types not equal */
2200
2201
2202
2203 end if; /* end if for supp bucket type not mixed */
2204
2205 end if; /* end if for cust bucket type is mixed */
2206
2207 -- added exception handler
2208 EXCEPTION WHEN OTHERS THEN
2209 /* Bug 4235511 */
2210 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2211 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in MSC_EXCHANGE_BUCKETING.CALCULATE_NETTING_BUCKET ' || sqlerrm);
2212 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,'MSC_EXCHANGE_BUCKETING.CALCULATE_NETTING_BUCKET',SQLERRM);
2213 end if ;
2214
2215 END CALCULATE_NETTING_BUCKET;
2216
2217 PROCEDURE start_bucketing(
2218 p_refresh_number IN OUT NOCOPY NUMBER)
2219 IS
2220
2221 t_customer_of numberlist ;
2222 t_customer_site_of numberlist ;
2223 t_supplier_of numberlist ;
2224 t_supplier_site_of numberlist ;
2225 t_item_id_of numberlist ;
2226 t_max_ref_cust numberlist ;
2227 t_max_receipt_cust msc_sce_loads_pkg.receiptdateList ;
2228 t_min_receipt_cust msc_sce_loads_pkg.receiptdateList ;
2229
2230 t_customer_sc numberlist ;
2231 t_customer_site_sc numberlist ;
2232 t_supplier_sc numberlist ;
2233 t_supplier_site_sc numberlist ;
2234 t_item_id_sc numberlist ;
2235 t_max_ref_supp numberlist ;
2236 t_max_receipt_supp msc_sce_loads_pkg.receiptdateList ;
2237 t_min_receipt_supp msc_sce_loads_pkg.receiptdateList ;
2238 cursor c1 is
2239 SELECT distinct sd.customer_id,
2240 sd.customer_site_id,
2241 sd.publisher_id supplier_id,
2242 sd.publisher_site_id supplier_site_id,
2243 nvl(sd.base_item_id,sd.inventory_item_id) item_id
2244 FROM msc_sup_dem_entries sd
2245 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
2246 AND sd.publisher_order_type in (msc_x_netting_pkg.SUPPLY_COMMIT, msc_x_netting_pkg.SALES_ORDER)
2247 AND sd.last_refresh_number > p_refresh_number
2248 union
2249 SELECT distinct sd.publisher_id customer_id,
2250 sd.publisher_site_id customer_site_id,
2251 sd.supplier_id,
2252 sd.supplier_site_id,
2253 nvl(sd.base_item_id,sd.inventory_item_id) item_id
2254 FROM msc_sup_dem_entries sd
2255 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
2256 AND sd.publisher_order_type = msc_x_netting_pkg.ORDER_FORECAST
2257 AND sd.last_refresh_number> p_refresh_number;
2258
2259 /* These cursors c2 and c3 are not used at all .
2260 These were alternatives tried for performance gains */
2261 /*
2262 cursor c2 is
2263 select customer_id,customer_site_id,supplier_id,supplier_site_id,item_id,
2264 nvl(max(last_refresh_number), -1),
2265 nvl(max(key_date),sysdate),
2266 nvl(min(key_date),sysdate)
2267 from (
2268 select
2269 sd1.publisher_id customer_id,
2270 sd1.publisher_site_id customer_site_id,
2271 sd1.supplier_id,
2272 sd1.supplier_site_id,
2273 nvl(sd1.base_item_id,sd1.inventory_item_id) item_id,
2274 sd1.last_refresh_number,
2275 sd1.key_date
2276 from msc_sup_dem_entries sd1
2277 where sd1.publisher_order_type = 2
2278 and sd1.plan_id= -1
2279 and sd1.last_refresh_number > p_refresh_number
2280 and exists (select 1
2281 from msc_sup_dem_entries sd2
2282 where sd2.plan_id = sd1.plan_id
2283 and sd2.sr_instance_id = sd1.sr_instance_id
2284 and sd2.publisher_order_type = 3
2285 and sd2.customer_id = sd1.publisher_id
2286 and sd2.customer_site_id = sd1.publisher_site_id
2287 and sd2.publisher_id = sd1.supplier_id
2288 and sd2.publisher_site_id = sd1.supplier_site_id
2289 and nvl(sd2.base_item_id,sd2.inventory_item_id) = nvl(sd1.base_item_id,sd1.inventory_item_id)
2290 and sd2.last_refresh_number > p_refresh_number
2291 )
2292 union all
2293 select
2294 -- distinct
2295 sd1.publisher_id customer_id,
2296 sd1.publisher_site_id customer_site_id,
2297 sd1.supplier_id,
2298 sd1.supplier_site_id,
2299 nvl(sd1.base_item_id,sd1.inventory_item_id) item_id,
2300 sd1.last_refresh_number,
2301 sd1.key_date
2302 from msc_sup_dem_entries sd1
2303 where sd1.publisher_order_type = 2
2304 and sd1.plan_id= -1
2305 and sd1.last_refresh_number > p_refresh_number
2306 and exists (select 1
2307 from msc_sup_dem_entries sd2
2308 where sd2.plan_id = sd1.plan_id
2309 and sd2.sr_instance_id = sd1.sr_instance_id
2310 and sd2.publisher_order_type = 14
2311 and sd2.customer_id = sd1.publisher_id
2312 and sd2.customer_site_id = sd1.publisher_site_id
2313 and sd2.publisher_id = sd1.supplier_id
2314 and sd2.publisher_site_id = sd1.supplier_site_id
2315 and nvl(sd2.base_item_id,sd2.inventory_item_id) = nvl(sd1.base_item_id,sd1.inventory_item_id)
2316 and sd2.last_refresh_number > p_refresh_number
2317 )
2318 ) x
2319 group by x.customer_id,x.customer_site_id,x.supplier_id
2320 ,x.supplier_site_id ,x.item_id
2321 order by 1,2,3,4,5;
2322
2323 cursor c3 is
2324 select customer_id,customer_site_id,supplier_id,supplier_site_id,item_id,
2325 nvl(max(last_refresh_number), -1),
2326 nvl(max(key_date),sysdate),
2327 nvl(min(key_date),sysdate)
2328 from (
2329 select
2330 sd1.customer_id,
2331 sd1.customer_site_id,
2332 sd1.publisher_id supplier_id,
2333 sd1.publisher_site_id supplier_site_id,
2334 nvl(sd1.base_item_id,sd1.inventory_item_id) item_id,
2335 sd1.last_refresh_number,
2336 sd1.key_date
2337 from msc_sup_dem_entries sd1
2338 where sd1.publisher_order_type = 3
2339 and sd1.plan_id= -1
2340 and sd1.last_refresh_number > p_refresh_number
2341 and exists (select 1
2342 from msc_sup_dem_entries sd2
2343 where sd2.plan_id = sd1.plan_id
2344 and sd2.sr_instance_id = sd1.sr_instance_id
2345 and sd2.publisher_order_type = 2
2346 and sd2.publisher_id = sd1.customer_id
2347 and sd2.publisher_site_id = sd1.customer_site_id
2348 and sd2.supplier_id = sd1.publisher_id
2349 and sd2.supplier_site_id = sd1.publisher_site_id
2350 and nvl(sd2.base_item_id,sd2.inventory_item_id) = nvl(sd1.base_item_id,sd1.inventory_item_id)
2351 and sd2.last_refresh_number > p_refresh_number
2352 )
2353 union all
2354 select
2355 sd1.customer_id,
2356 sd1.customer_site_id,
2357 sd1.publisher_id supplier_id,
2358 sd1.publisher_site_id supplier_site_id,
2359 nvl(sd1.base_item_id,sd1.inventory_item_id) item_id,
2360 sd1.last_refresh_number,
2361 sd1.key_date
2362 from msc_sup_dem_entries sd1
2363 where sd1.publisher_order_type = 14
2364 and sd1.plan_id= -1
2365 and sd1.last_refresh_number > p_refresh_number
2366 and exists (select 1
2367 from msc_sup_dem_entries sd2
2368 where sd2.plan_id = sd1.plan_id
2369 and sd2.sr_instance_id = sd1.sr_instance_id
2370 and sd2.publisher_order_type = 2
2371 and sd2.publisher_id = sd1.customer_id
2372 and sd2.publisher_site_id = sd1.customer_site_id
2373 and sd2.supplier_id = sd1.publisher_id
2374 and sd2.supplier_site_id = sd1.publisher_site_id
2375 and nvl(sd2.base_item_id,sd2.inventory_item_id) = nvl(sd1.base_item_id,sd1.inventory_item_id)
2376 and sd2.last_refresh_number > p_refresh_number
2377 )
2378 ) x
2379 group by x.customer_id,x.customer_site_id,x.supplier_id
2380 ,x.supplier_site_id ,x.item_id
2381 order by 1,2,3,4,5;
2382 */
2383
2384 lv_cutoff_ref_num number;
2385 lv_max_rn number;
2386 lv_min_key_date date;
2387 lv_max_key_date date;
2388
2389 lv_sql_stmt varchar2(1000);
2390 begin
2391
2392 lv_sql_stmt := 'delete msc_cp_plan_buckets';
2393 execute immediate lv_sql_stmt;
2394 commit;
2395
2396 select nvl(max(last_refresh_number),0)
2397 into lv_cutoff_ref_num
2398 from msc_sup_dem_entries;
2399
2400
2401 /* These cursors c2 and c3 are not used at all .
2402 These were alternatives tried for performance gains */
2403 /*
2404 BEGIN
2405 OPEN c2;
2406 FETCH c2 BULK COLLECT INTO
2407 t_customer_of ,
2408 t_customer_site_of,
2409 t_supplier_of ,
2410 t_supplier_site_of,
2411 t_item_id_of ,
2412 t_max_ref_cust ,
2413 t_max_receipt_cust,
2414 t_min_receipt_cust;
2415 CLOSE c2;
2416 exception
2417 when others then
2418 LOG_MESSAGE('An error occured in the cursor c2 : '||SQLERRM);
2419
2420 BEGIN
2421 OPEN c3;
2422 FETCH c3 BULK COLLECT INTO
2423 t_customer_sc ,
2424 t_customer_site_sc ,
2425 t_supplier_sc ,
2426 t_supplier_site_sc ,
2427 t_item_id_sc ,
2428 t_max_ref_supp ,
2429 t_max_receipt_supp ,
2430 t_min_receipt_supp ;
2431 CLOSE c3;
2432 exception
2433 when others then
2434 LOG_MESSAGE('An error occured in the cursor c3 : '||SQLERRM);
2435 end;
2436
2437 IF (t_customer_of.COUNT > 0) and (t_customer_sc.COUNT > 0) THEN
2438
2439 LOG_MESSAGE('t_customer_of.COUNT : '||t_customer_of.COUNT);
2440 LOG_MESSAGE('t_customer_sc.count '||t_customer_sc.count);
2441
2442 FOR j in 1..t_customer_of.COUNT LOOP
2443
2444 if (t_customer_of(j) = t_customer_sc(j) and
2445 t_customer_site_of(j) = t_customer_site_sc(j) and
2446 t_supplier_of(j) = t_supplier_sc(j) and
2447 t_supplier_site_of(j) = t_supplier_site_sc(j) and
2448 t_item_id_of(j) = t_item_id_sc(j) ) then
2449
2450 CALCULATE_NETTING_BUCKET( -1,
2451 t_customer_sc(j) , -- p_customer_id IN NUMBER,
2452 t_customer_site_sc(j) , ---p_customer_site_id IN NUMBER,
2453 t_supplier_sc(j) , ---p_supplier_id IN NUMBER,
2454 t_supplier_site_sc(j) , ---p_supplier_site_id IN NUMBER,
2455 t_item_id_sc(j), ----p_item_id IN NUMBER,
2456 msc_x_netting_pkg.SUPPLY_PLANNING, ---.p_plan_type IN NUMBER,
2457 t_max_ref_supp(j),
2458 t_max_receipt_supp(j),
2459 t_min_receipt_supp(j),
2460 t_max_ref_cust(j),
2461 t_max_receipt_cust(j),
2462 t_min_receipt_cust(j),
2463 lv_cutoff_ref_num); ---p_cutoff_ref_num IN OUT NOCOPY NUMBER)
2464 end if;
2465
2466
2467 end loop;
2468
2469 end if;
2470 */
2471
2472 for c_rec in c1 loop
2473
2474 CALCULATE_NETTING_BUCKET(
2475 -1,
2476 c_rec.customer_id, --p_customer_id
2477 c_rec.customer_site_id, --p_customer_site_id
2478 c_rec.supplier_id, --p_supplier_id
2479 c_rec.supplier_site_id, --p_supplier_site_id
2480 c_rec.item_id, --p_item_id
2481 msc_x_netting_pkg.SUPPLY_PLANNING, --p_plan_type
2482 lv_cutoff_ref_num); --p_cutoff_ref_num
2483
2484
2485 exit when c1%NOTFOUND;
2486
2487 end loop;
2488
2489 EXCEPTION WHEN OTHERS THEN
2490 LOG_MESSAGE(SQLERRM);
2491 LOG_MESSAGE(SQLCODE);
2492 LOG_MESSAGE('Error bucketing code.');
2493 RAISE;
2494
2495 END start_bucketing;
2496
2497 END MSC_EXCHANGE_BUCKETING;