[Home] [Help]
PACKAGE BODY: APPS.MSC_ANALYSIS_SAFETY_STOCK_PERF
Source
1 PACKAGE BODY MSC_ANALYSIS_SAFETY_STOCK_PERF AS
2 /* $Header: MSCIORB.pls 120.14 2010/11/05 16:44:17 minduvad ship $ */
3 --
4 -- Preprocesses safety stock/service level data for subsequent digestion in
5 -- Inventory Optimization's Analysis View "Safety Stock and Service Level Report"
6 --
7 --
8
9 RECORD_SAFETY_STOCK CONSTANT NUMBER := 0;
10 RECORD_SERVICE_LEVEL CONSTANT NUMBER := 1;
11 RECORD_INVENTORY_VALUE CONSTANT NUMBER := 2;
12 RECORD_SERVICE_LEVEL_BRKDOWN CONSTANT NUMBER := 3;
13 RECORD_COST_BRKDOWN CONSTANT NUMBER := 4;
14
15
16 UNDEFINED_CUSTOMER_CODE CONSTANT VARCHAR2(30) := '_MISC'; -- Used in Java
17 UNDEFINED_CUSTOMER_ID CONSTANT NUMBER := -1;
18
19 DETAIL_LEVEL_WEEK CONSTANT NUMBER := 1;
20 DETAIL_LEVEL_PERIOD CONSTANT NUMBER := 0;
21
22 BUDGET_CONSTRAINED_OFF CONSTANT NUMBER := 2;
23 BUDGET_CONSTRAINED_ON CONSTANT NUMBER := 1;
24
25 CALENDAR_TYPE_MFG CONSTANT NUMBER := 1;
26 CALENDAR_TYPE_BIS CONSTANT NUMBER := 0;
27
28 g_user_id number;
29
30 do_debug BOOLEAN := TRUE;
31 g_perf_prof_on BOOLEAN := FALSE;
32 g_period_setname varchar2(250);
33
34 TYPE CurTyp IS REF CURSOR;
35
36
37 TYPE PlanList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
38
39 TYPE Bucket IS RECORD
40 (
41 -- item key
42 plan_id NUMBER := 0,
43 instance_id NUMBER := 0,
44 org_id NUMBER := 0,
45 item_id NUMBER := 0,
46 sr_category_inst_id NUMBER := 0,
47 sr_category_id NUMBER := 0,
48 category_name VARCHAR2(255) := NULL,
49 -- bucket data
50 bkt_start_date DATE := NULL,
51 bkt_end_date DATE := NULL,
52 period_start_date DATE := NULL,
53 last_week_of_period VARCHAR2(1) := 'N',
54 -- safety stock measures
55 achieved_ss_qty NUMBER := 0,
56 achieved_ss_dollars NUMBER := 0,
57 achieved_ss_days NUMBER := 0,
58 target_ss_qty NUMBER := 0,
59 target_ss_dollars NUMBER := 0,
60 target_ss_days NUMBER := 0,
61 userdef_ss_qty NUMBER := 0,
62 userdef_ss_dollars NUMBER := 0,
63 userdef_ss_days NUMBER := 0,
64 nr_ss_records NUMBER := 0,
65 inv_value_dollars NUMBER := 0,
66 period_type NUMBER := 0,
67 total_unpooled_safety_stock number := 0,
68 demand_var_ss_percent number := 0,
69 mfg_ltvar_ss_percent number := 0,
70 transit_ltvar_ss_percent number := 0,
71 sup_ltvar_ss_percent number := 0,
72 -- service level measures
73 delivered_quantity NUMBER := 0, -- Achieved Service Level = delivered / required
74 required_quantity NUMBER := 0,
75 target_service_level NUMBER := 0,
76 nr_sl_records NUMBER := 0, -- # demand records for service level
77 partner_id NUMBER := NULL,
78 customer_class_code VARCHAR2(30) := NULL,
79 -- service level or safety stock record
80 record_type NUMBER := NULL
81 );
82
83 TYPE Schedule IS TABLE OF Bucket INDEX BY BINARY_INTEGER;
84
85 PROCEDURE put_line (p_msg varchar2) IS
86 BEGIN
87 --insert into msc_test values (p_msg);
88 --commit;
89 --dbms_output.put_line(p_msg);
90 null;
91 END put_line;
92
93 PROCEDURE parse_planlist(p_plans VARCHAR2, p_planlist IN OUT NOCOPY PlanList)
94 IS
95 occurrence NUMBER := 1;
96 stringstart NUMBER := 1;
97 stringend NUMBER := 1;
98 planfound NUMBER := 1;
99 pos NUMBER;
100 token NUMBER;
101 BEGIN
102
103 LOOP
104 pos := INSTR(p_plans, ',', 1, occurrence);
105 occurrence := occurrence + 1;
106 IF pos = 0 THEN
107 stringend := LENGTH(p_plans);
108 token := to_number(SUBSTR(p_plans, stringstart, stringend - stringstart + 1));
109 IF token IS NOT NULL THEN
110 p_planlist(planfound) := token;
111 planfound := planfound + 1;
112 END IF;
113 EXIT;
114 END IF;
115 stringend := pos - 1;
116 token := to_number(SUBSTR(p_plans, stringstart, stringend - stringstart + 1));
117 IF token IS NOT NULL THEN
118 p_planlist(planfound) := token;
119 planfound := planfound + 1;
120 END IF;
121 stringstart := stringend + 2;
122 END LOOP;
123
124 END;
125
126 function get_plan_type(p_plan_id number) return number is
127 cursor c_plan (p_plan_id number) is
128 select plan_type
129 from msc_plans
130 where plan_id = p_plan_id;
131
132 l_plan_type number;
133 begin
134
135 open c_plan(p_plan_id);
136 fetch c_plan into l_plan_type;
137 close c_plan;
138
139 return l_plan_type;
140 end get_plan_type;
141
142
143 FUNCTION get_cat_set_id(p_plan_id number) RETURN NUMBER
144 IS
145 l_cat_set_id NUMBER;
146 l_def_pref_id number;
147 l_plan_type number;
148 BEGIN
149 -- Get category set from profile option for IO
150 -- SELECT fnd_profile.value('MSR_BUDGET_CATEGORY_SET') INTO l_cat_set_id FROM dual;
151 l_plan_type := get_plan_type(p_plan_id);
152 l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
153 l_cat_set_id:= msc_get_name.GET_preference('CATEGORY_SET_ID',l_def_pref_id, l_plan_type);
154 return l_cat_set_id;
155
156 END get_cat_set_id;
157
158 PROCEDURE schedule_create_weeks(mainschedule IN OUT NOCOPY Schedule, p_plan_id NUMBER)
159 IS
160 CURSOR c1(p_plan_id NUMBER) IS
161 SELECT mp.plan_id, mp.compile_designator, mtp.calendar_code, mpb.bkt_start_date, mpb.bkt_end_date,
162 mpsd.period_start_date
163 FROM msc_plans mp, msc_trading_partners mtp, msc_plan_buckets mpb, msc_period_start_dates mpsd
164 WHERE mp.plan_id = p_plan_id
165 AND mtp.sr_tp_id = mp.organization_id
166 AND mtp.sr_instance_id = mp.sr_instance_id
167 AND mtp.partner_type = 3
168 AND mp.plan_id = mpb.plan_id
169 AND mp.sr_instance_id = mpb.sr_instance_id
170 AND mp.organization_id = mpb.organization_id
171 AND mtp.sr_Instance_id = mpsd.sr_instance_id
172 AND mtp.calendar_code = mpsd.calendar_code
173 AND mpb.bucket_type = 2
174 AND mpb.sr_instance_id = mpsd.sr_instance_id
175 AND mpsd.exception_set_id = mtp.calendar_exception_set_id
176 AND mpsd.calendar_code = mtp.calendar_code
177 AND (mpb.bkt_start_date >= mpsd.period_start_date AND mpb.bkt_start_date < mpsd.next_date)
178 ORDER BY mpb.bkt_start_date;
179
180 currentBucket Bucket;
181 currentPosition BINARY_INTEGER := 1;
182 c1Rec c1%ROWTYPE;
183 previous_period_start_date DATE := NULL;
184 current_week_nr NUMBER := 1;
185 current_period_nr NUMBER := 1;
186
187 BEGIN
188
189 OPEN c1(p_plan_id);
190 LOOP
191 FETCH c1 INTO c1Rec;
192 IF c1%NOTFOUND THEN
193 IF current_week_nr > 1 THEN
194 mainschedule(current_week_nr - 1).last_week_of_period := 'Y';
195 END IF;
196 EXIT;
197 END IF;
198 currentBucket.bkt_start_date := c1Rec.bkt_start_date;
199 currentBucket.bkt_end_date := c1Rec.bkt_end_date;
200 currentBucket.period_start_date := c1Rec.period_start_date;
201 IF currentBucket.period_start_date <> previous_period_start_date THEN
202 -- Need the last week of the period for collapsing ss across weeks
203 IF current_week_nr > 1 THEN
204 mainschedule(current_week_nr - 1).last_week_of_period := 'Y';
205 END IF;
206 END IF;
207
208 mainschedule(current_week_nr) := currentBucket;
209
210 previous_period_start_date := currentBucket.period_start_date;
211 current_week_nr := current_week_nr + 1;
212 END LOOP;
213 CLOSE c1;
214
215 END;
216
217 PROCEDURE schedule_create_periods(mainschedule IN OUT NOCOPY Schedule, p_plan_id NUMBER)
218 IS
219 CURSOR c1(p_plan_id NUMBER) IS
220 SELECT mp.plan_id, mp.compile_designator, mtp.calendar_code, mpb.bkt_start_date, mpb.bkt_end_date,
221 mpsd.period_start_date, mpb.bucket_type
222 FROM msc_plans mp, msc_trading_partners mtp, msc_plan_buckets mpb, msc_period_start_dates mpsd
223 WHERE mp.plan_id = p_plan_id
224 AND mtp.sr_tp_id = mp.organization_id
225 AND mtp.sr_instance_id = mp.sr_instance_id
226 AND mtp.partner_type = 3
227 AND mp.plan_id = mpb.plan_id
228 AND mp.sr_instance_id = mpb.sr_instance_id
229 AND mp.organization_id = mpb.organization_id
230 AND mtp.sr_Instance_id = mpsd.sr_instance_id
231 AND mtp.calendar_code = mpsd.calendar_code
232 AND mpb.bucket_type IN (2,3)
233 AND mpb.sr_instance_id = mpsd.sr_instance_id
234 AND mpsd.exception_set_id = mtp.calendar_exception_set_id
235 AND mpsd.calendar_code = mtp.calendar_code
236 AND (mpb.bkt_start_date >= mpsd.period_start_date AND mpb.bkt_start_date < mpsd.next_date)
237 ORDER BY mpb.bkt_start_date;
238
239 currentBucket Bucket;
240 currentPosition BINARY_INTEGER := 1;
241 c1Rec c1%ROWTYPE;
242 previous_period_start_date DATE := NULL;
243 current_period_nr NUMBER := 0;
244
245 BEGIN
246
247 OPEN c1(p_plan_id);
248 LOOP
249 FETCH c1 INTO c1Rec;
250 EXIT WHEN c1%NOTFOUND;
251
252 IF c1Rec.period_start_date <> previous_period_start_date OR
253 previous_period_start_date IS NULL
254 THEN
255 current_period_nr := current_period_nr + 1;
256 currentBucket.bkt_start_date := c1Rec.bkt_start_date;
257 currentBucket.bkt_end_date := c1Rec.bkt_end_date;
258 currentBucket.period_start_date := c1Rec.period_start_date;
259 mainschedule(current_period_nr) := currentBucket;
260 END IF;
261
262 IF c1Rec.period_start_date = previous_period_start_date THEN
263 mainschedule(current_period_nr).bkt_end_date := c1Rec.bkt_end_date;
264 END IF;
265
266 previous_period_start_date := c1Rec.period_start_date;
267
268 END LOOP;
269 CLOSE c1;
270 END;
271
272
273 PROCEDURE schedule_initialize_bkt(target IN OUT NOCOPY Bucket, template Bucket)
274 IS
275 BEGIN
276 target.achieved_ss_qty := 0;
277 target.achieved_ss_dollars := 0;
278 target.achieved_ss_days := 0;
279 target.target_ss_qty := 0;
280 target.target_ss_dollars := 0;
281 target.target_ss_days := 0;
282 target.userdef_ss_qty := 0;
283 target.userdef_ss_dollars := 0;
284 target.userdef_ss_days := 0;
285 target.delivered_quantity := 0;
286 target.required_quantity := 0;
287 target.target_service_level := 0;
288 target.total_unpooled_safety_stock := 0;
289 target.demand_var_ss_percent := 0;
290 target.mfg_ltvar_ss_percent := 0;
291 target.transit_ltvar_ss_percent := 0;
292 target.sup_ltvar_ss_percent := 0;
293 target.nr_sl_records := 0;
294 target.nr_ss_records := 0;
295
296 target.plan_id := template.plan_id;
297 target.instance_id := template.instance_id;
298 target.org_id := template.org_id;
299 target.sr_category_inst_id := template.sr_category_inst_id;
300 target.sr_category_id := template.sr_category_id;
301 target.category_name := template.category_name;
302 target.item_id := template.item_id;
303 target.partner_id := template.partner_id;
304 target.record_type := template.record_type;
305 target.customer_class_code := template.customer_class_code;
306 target.period_type := template.period_type;
307 END;
308
309 PROCEDURE schedule_initialize(mainschedule IN OUT NOCOPY Schedule, aRecord Bucket)
310 IS
311 l_target_service_level NUMBER;
312 BEGIN
313 if (mainschedule.count = 0 ) then
314 return;
315 end if;
316 FOR i IN mainschedule.FIRST..mainschedule.LAST LOOP
317 schedule_initialize_bkt(mainschedule(i), aRecord);
318 END LOOP;
319 END;
320
321 PROCEDURE schedule_ss_fill_gaps(mainschedule IN OUT NOCOPY Schedule)
322 IS
323 l_prev_userdef_ss_qty NUMBER;
324 l_prev_userdef_ss_days NUMBER;
325 l_prev_userdef_ss_dollars NUMBER;
326 l_prev_achieved_ss_qty NUMBER;
327 l_prev_achieved_ss_days NUMBER;
328 l_prev_achieved_ss_dollars NUMBER;
329 l_prev_target_ss_qty NUMBER;
330 l_prev_target_ss_days NUMBER;
331 l_prev_target_ss_dollars NUMBER;
332 l_prev_total_unpooled_ss number;
333 l_prev_nr_ss_records NUMBER := NULL;
334
335 BEGIN
336 if (mainschedule.count = 0 ) then
337 return;
338 end if;
339 FOR currentIndex IN mainschedule.FIRST..mainschedule.LAST LOOP
340 IF mainschedule(currentIndex).nr_ss_records IS NULL AND l_prev_nr_ss_records IS NOT NULL THEN
341 mainschedule(currentIndex).userdef_ss_qty := l_prev_userdef_ss_qty;
342 mainschedule(currentIndex).userdef_ss_days := l_prev_userdef_ss_days;
343 mainschedule(currentIndex).userdef_ss_dollars := l_prev_userdef_ss_dollars;
344 mainschedule(currentIndex).achieved_ss_qty := l_prev_achieved_ss_qty;
345 mainschedule(currentIndex).achieved_ss_days := l_prev_achieved_ss_days;
346 mainschedule(currentIndex).achieved_ss_dollars := l_prev_achieved_ss_dollars;
347 mainschedule(currentIndex).target_ss_qty := l_prev_target_ss_qty;
348 mainschedule(currentIndex).target_ss_days := l_prev_target_ss_days;
349 mainschedule(currentIndex).target_ss_dollars := l_prev_target_ss_dollars;
350 mainschedule(currentIndex).total_unpooled_safety_stock := l_prev_total_unpooled_ss;
351 mainschedule(currentIndex).nr_ss_records := l_prev_nr_ss_records;
352 END IF;
353 l_prev_userdef_ss_qty := mainschedule(currentIndex).userdef_ss_qty;
354 l_prev_userdef_ss_days := mainschedule(currentIndex).userdef_ss_days;
355 l_prev_userdef_ss_dollars := mainschedule(currentIndex).userdef_ss_dollars;
356 l_prev_achieved_ss_qty := mainschedule(currentIndex).achieved_ss_qty;
357 l_prev_achieved_ss_days := mainschedule(currentIndex).achieved_ss_days;
358 l_prev_achieved_ss_dollars := mainschedule(currentIndex).achieved_ss_dollars;
359 l_prev_target_ss_qty := mainschedule(currentIndex).target_ss_qty;
360 l_prev_target_ss_days := mainschedule(currentIndex).target_ss_days;
361 l_prev_target_ss_dollars := mainschedule(currentIndex).target_ss_dollars;
362 l_prev_total_unpooled_ss := mainschedule(currentIndex).total_unpooled_safety_stock;
363 l_prev_nr_ss_records := mainschedule(currentIndex).nr_ss_records;
364 END LOOP;
365 END schedule_ss_fill_gaps;
366
367 PROCEDURE schedule_aggregate_output(aRecord Bucket, periodschedule BOOLEAN)
368 IS
369 l_achieved_service_level NUMBER;
370 l_target_service_level NUMBER;
371 l_week_start_date DATE;
372 l_week_nr NUMBER;
373 BEGIN
374
375 IF aRecord.record_type = RECORD_SAFETY_STOCK THEN
376 IF aRecord.nr_ss_records = 0 THEN
377 RETURN;
378 END IF;
379 END IF;
380
381 IF aRecord.record_type = RECORD_SERVICE_LEVEL THEN
382 IF aRecord.nr_sl_records = 0 THEN
383 RETURN;
384 ELSE
385 l_target_service_level := aRecord.target_service_level / aRecord.nr_sl_records;
386 END IF;
387 END IF;
388
389 IF nvl(aRecord.required_quantity, 0) = 0 THEN
390 l_achieved_service_level := NULL;
391 ELSE
392 l_achieved_service_level := aRecord.delivered_quantity * 100 / aRecord.required_quantity;
393 END IF;
394
395 IF periodschedule THEN
396 l_week_start_date := NULL;
397 ELSE
398 l_week_start_date := aRecord.bkt_start_date;
399 END IF;
400
401 INSERT INTO msc_analysis_aggregate
402 (
403 plan_id,
404 record_type,
405 safety_stock_qty,
406 safety_stock_dollars,
407 safety_stock_dos,
408 target_safety_stock_qty,
409 target_safety_stock_dollars,
410 target_safety_stock_dos,
411 userdef_safety_stock_qty,
412 userdef_safety_stock_dollars,
413 userdef_safety_stock_dos,
414 total_unpooled_safety_stock,
415 demand_var_ss_percent,
416 mfg_ltvar_ss_percent,
417 transit_ltvar_ss_percent,
418 sup_ltvar_ss_percent,
419 achieved_service_level,
420 target_service_level,
421 inventory_value_dollars,
422 period_type,
423 week_start_date,
424 period_start_date,
425 sr_instance_id,
426 organization_id,
427 sr_cat_instance_id,
428 sr_category_id,
429 category_name,
430 inventory_item_id,
431 last_update_date,
432 last_updated_by,
433 creation_date,
434 created_by,
435 last_update_login
436 )
437 VALUES
438 (
439 aRecord.plan_id,
440 aRecord.record_type,
441 aRecord.achieved_ss_qty,
442 aRecord.achieved_ss_dollars,
443 aRecord.achieved_ss_days,
444 aRecord.target_ss_qty,
445 aRecord.target_ss_dollars,
446 aRecord.target_ss_days,
447 aRecord.userdef_ss_qty,
448 aRecord.userdef_ss_dollars,
449 aRecord.userdef_ss_days,
450 aRecord.total_unpooled_safety_stock,
451 aRecord.demand_var_ss_percent,
452 aRecord.mfg_ltvar_ss_percent,
453 aRecord.transit_ltvar_ss_percent,
454 aRecord.sup_ltvar_ss_percent,
455 l_achieved_service_level,
456 l_target_service_level,
457 NULL,
458 CALENDAR_TYPE_MFG,
459 l_week_start_date,
460 aRecord.period_start_date,
461 aRecord.instance_id,
462 aRecord.org_id,
463 aRecord.sr_category_inst_id,
464 aRecord.sr_category_id,
465 aRecord.category_name,
466 aRecord.item_id,
467 sysdate,
468 g_user_id,
469 sysdate,
470 g_user_id,
471 to_number(null)
472 );
473
474 END;
475
476 PROCEDURE schedule_output_record(query_id NUMBER, aRecord IN OUT NOCOPY Bucket, periodschedule BOOLEAN DEFAULT FALSE)
477 IS
478 l_achieved_service_level NUMBER;
479 l_target_service_level NUMBER;
480 l_bkt_start_date DATE;
481 l_bkt_end_date DATE;
482 l_week_nr NUMBER;
483 BEGIN
484
485 IF aRecord.record_type = RECORD_SAFETY_STOCK THEN
486 IF aRecord.nr_ss_records = 0 THEN
487 RETURN;
488 END IF;
489 END IF;
490
491 IF aRecord.record_type = RECORD_SERVICE_LEVEL THEN
492 IF aRecord.nr_sl_records = 0 THEN
493 RETURN;
494 ELSE
495 aRecord.target_service_level := aRecord.target_service_level / aRecord.nr_sl_records;
496 aRecord.nr_sl_records := 1;
497 END IF;
498 END IF;
499
500 IF nvl(aRecord.required_quantity, 0) = 0 THEN
501 l_achieved_service_level := NULL;
502 ELSE
503 l_achieved_service_level := aRecord.delivered_quantity * 100 / aRecord.required_quantity;
504 END IF;
505
506 l_bkt_start_date := aRecord.bkt_start_date;
507 l_bkt_end_date := aRecord.bkt_end_date;
508
509 IF periodschedule THEN
510 l_bkt_start_date := NULL;
511 l_bkt_end_date := NULL;
512 END IF;
513
514 INSERT INTO msc_form_query
515 (query_id, last_update_date, last_updated_by,creation_date, created_by,
516 NUMBER1,NUMBER2,NUMBER3,
517 NUMBER4,NUMBER5,CHAR1,
518 DATE1,DATE2,DATE3,
519 NUMBER9, NUMBER10, NUMBER11,
520 NUMBER12,NUMBER13,NUMBER14,
521 NUMBER15,NUMBER16,PROGRAM_ID,
522 PROGRAM_APPLICATION_ID, REQUEST_ID,
523 LAST_UPDATE_LOGIN, NUMBER7,
524 CHAR7, CHAR6, char11, char12, char13, char14, char15)
525 VALUES
526 (query_id, SYSDATE, -1, SYSDATE, aRecord.record_type,
527 aRecord.plan_id, aRecord.instance_id, aRecord.org_id,
528 aRecord.item_id, aRecord.sr_category_id, aRecord.category_name,
529 l_bkt_start_date,l_bkt_end_date, aRecord.period_start_date,
530 aRecord.achieved_ss_qty,aRecord.achieved_ss_dollars,aRecord.achieved_ss_days,
531 aRecord.target_ss_qty,aRecord.target_ss_dollars,aRecord.target_ss_days,
532 aRecord.userdef_ss_qty,aRecord.userdef_ss_dollars,aRecord.userdef_ss_days,
533 l_achieved_service_level, aRecord.sr_category_inst_id,
534 aRecord.target_service_level, aRecord.period_type,
535 aRecord.partner_id, aRecord.customer_class_code,
536 aRecord.total_unpooled_safety_stock, aRecord.demand_var_ss_percent,
537 aRecord.mfg_ltvar_ss_percent, aRecord.transit_ltvar_ss_percent, aRecord.sup_ltvar_ss_percent);
538 END;
539
540 PROCEDURE schedule_flush(mainschedule IN OUT NOCOPY Schedule, query_id NUMBER, periodschedule BOOLEAN DEFAULT FALSE)
541 IS
542 BEGIN
543 if (mainschedule.count = 0 ) then
544 return;
545 end if;
546 FOR i IN mainschedule.FIRST..mainschedule.LAST LOOP
547 schedule_output_record(query_id, mainschedule(i), periodschedule);
548 -- schedule_dump_record(mainschedule(i));
549 END LOOP;
550 END;
551
552 PROCEDURE schedule_aggregate_flush(mainschedule Schedule, periodschedule BOOLEAN DEFAULT FALSE)
553 IS
554 BEGIN
555 if (mainschedule.count = 0 ) then
556 return;
557 end if;
558 FOR i IN mainschedule.FIRST..mainschedule.LAST LOOP
559 schedule_aggregate_output(mainschedule(i), periodschedule);
560 END LOOP;
561 END;
562
563 FUNCTION is_bucket_match(aBucket Bucket, aRecord Bucket)
564 RETURN BOOLEAN
565 IS
566 BEGIN
567 IF aRecord.bkt_start_date >= aBucket.bkt_start_date AND
568 aRecord.bkt_start_date <= aBucket.bkt_end_date
569 THEN
570 RETURN TRUE;
571 END IF;
572
573 RETURN FALSE;
574 END;
575
576 PROCEDURE bucket_service_level_record(mainschedule IN OUT NOCOPY Schedule, aRecord Bucket, end_date DATE)
577 IS
578 BEGIN
579 if (mainschedule.count = 0 ) then
580 return;
581 end if;
582 FOR i IN mainschedule.FIRST..mainschedule.LAST LOOP
583 IF is_bucket_match(mainschedule(i), aRecord) THEN
584 mainschedule(i).delivered_quantity := mainschedule(i).delivered_quantity + aRecord.delivered_quantity;
585 mainschedule(i).required_quantity := mainschedule(i).required_quantity + aRecord.required_quantity;
586 mainschedule(i).target_service_level := mainschedule(i).target_service_level + aRecord.target_service_level;
587 mainschedule(i).nr_sl_records := mainschedule(i).nr_sl_records + aRecord.nr_sl_records;
588 RETURN;
589 END IF;
590 END LOOP;
591 END bucket_service_level_record;
592
593
594 PROCEDURE bucket_ss_copy(target IN OUT NOCOPY Bucket, src Bucket)
595 IS
596 BEGIN
597 IF src.achieved_ss_qty IS NOT NULL THEN
598 target.achieved_ss_qty := src.achieved_ss_qty;
599 IF src.achieved_ss_days > target.achieved_ss_days THEN
600 target.achieved_ss_days := src.achieved_ss_days;
601 END IF;
602 target.achieved_ss_dollars := src.achieved_ss_dollars;
603 END IF;
604
605 IF src.target_ss_qty IS NOT NULL THEN
606 target.target_ss_qty := src.target_ss_qty;
607 IF src.target_ss_days > target.target_ss_days THEN
608 target.target_ss_days := src.target_ss_days;
609 END IF;
610 target.target_ss_dollars := src.target_ss_dollars;
611 END IF;
612
613 IF src.userdef_ss_qty IS NOT NULL OR src.userdef_ss_days IS NOT NULL THEN
614 target.userdef_ss_qty := src.userdef_ss_qty;
615 IF src.userdef_ss_days > target.userdef_ss_days THEN
616 target.userdef_ss_days := src.userdef_ss_days;
617 END IF;
618 target.userdef_ss_dollars := src.userdef_ss_dollars;
619 END IF;
620
621 --pabram1111
622 IF src.demand_var_ss_percent IS NOT NULL THEN
623 target.demand_var_ss_percent := src.demand_var_ss_percent;
624 end if;
625
626 IF src.mfg_ltvar_ss_percent IS NOT NULL THEN
627 target.mfg_ltvar_ss_percent := src.mfg_ltvar_ss_percent;
628 end if;
629
630 IF src.transit_ltvar_ss_percent IS NOT NULL THEN
631 target.transit_ltvar_ss_percent := src.transit_ltvar_ss_percent;
632 end if;
633 IF src.sup_ltvar_ss_percent IS NOT NULL THEN
634 target.sup_ltvar_ss_percent := src.sup_ltvar_ss_percent;
635 end if;
636
637 IF src.total_unpooled_safety_stock IS NOT NULL THEN
638 target.total_unpooled_safety_stock := src.total_unpooled_safety_stock;
639 end if;
640
641 target.nr_ss_records := target.nr_ss_records + 1; -- Weight for DOS
642 END bucket_ss_copy;
643
644 PROCEDURE bucket_safety_stock_record(mainschedule IN OUT NOCOPY Schedule, aRecord Bucket, end_date DATE)
645 IS
646 BEGIN
647 if (mainschedule.count = 0 ) then
648 return;
649 end if;
650 FOR i IN mainschedule.FIRST..mainschedule.LAST LOOP
651 IF is_bucket_match(mainschedule(i), aRecord) THEN
652 bucket_ss_copy(mainschedule(i), aRecord);
653 END IF;
654 END LOOP;
655 END bucket_safety_stock_record;
656
657 FUNCTION is_service_level_key_changed(currRecord IN Bucket, prevRecord IN Bucket)
658 RETURN BOOLEAN
659 IS
660 BEGIN
661 IF currRecord.plan_id <> prevRecord.plan_id OR
662 nvl(currRecord.org_id, 0) <> nvl(prevRecord.org_id, 0) OR
663 nvl(currRecord.instance_id, 0) <> nvl(prevRecord.instance_id, 0) OR
664 nvl(currRecord.item_id, 0) <> nvl(prevRecord.item_id, 0) OR
665 nvl(currRecord.sr_category_inst_id, -1) <> nvl(prevRecord.sr_category_inst_id, -1) OR
666 nvl(currRecord.sr_category_id, -1) <> nvl(prevRecord.sr_category_id, -1) OR
667 nvl(currRecord.partner_id, -2) <> nvl(prevRecord.partner_id, -2) OR
668 nvl(currRecord.customer_class_code, 'XXXX') <> nvl(prevRecord.customer_class_code, 'XXXX')
669 THEN
670 RETURN TRUE;
671 ELSE
672 RETURN FALSE;
673 END IF;
674 END is_service_level_key_changed;
675
676 FUNCTION is_safety_stock_key_changed(currRecord IN Bucket, prevRecord IN Bucket)
677 RETURN BOOLEAN
678 IS
679 BEGIN
680 IF currRecord.plan_id <> prevRecord.plan_id OR
681 NVL(currRecord.org_id, -1) <> NVL(prevRecord.org_id, -1) OR
682 NVL(currRecord.instance_id, -1) <> NVL(prevRecord.instance_id, -1) OR
683 NVL(currRecord.item_id, -1) <> NVL(prevRecord.item_id, -1) OR
684 NVL(currRecord.sr_category_inst_id, 0) <> NVL(prevRecord.sr_category_inst_id, 0) OR
685 NVL(currRecord.sr_category_id, 0) <> NVL(prevRecord.sr_category_id, 0)
686 THEN
687 RETURN TRUE;
688 ELSE
689 RETURN FALSE;
690 END IF;
691 END is_safety_stock_key_changed;
692
693
694 PROCEDURE schedule_bucket_service_level(weekschedule IN OUT NOCOPY Schedule, periodschedule IN OUT NOCOPY Schedule,
695 p_plan_id NUMBER, p_sr_instance_id NUMBER,
696 p_sr_tp_id NUMBER, p_sr_cat_instance_id NUMBER, p_sr_cat_id NUMBER,
697 p_item_id NUMBER, p_partner_id NUMBER, p_customer_class_code VARCHAR2,
698 p_abc_id VARCHAR2, query_id NUMBER)
699 IS
700 currRecord Bucket;
701 prevRecord Bucket;
702 l_start_date DATE;
703 l_end_date DATE;
704
705 l_default_category_set_id NUMBER;
706
707 CURSOR c1 IS
708 SELECT msd.plan_id AS plan_id,
709 msd.sr_instance_id AS instance_id,
710 msd.organization_id AS org_id,
711 msd.inventory_item_id AS item_id,
712 mic.sr_instance_id AS sr_category_inst_id,
713 mic.sr_category_id AS sr_category_id,
714 mic.category_name AS category_name,
715 trunc(msd.using_assembly_demand_date) AS week_start_date,
716 NULL AS week_next_date,
717 NULL AS period_start_date,
718 'N' AS last_week_of_period,
719 NULL AS achieved_ss_qty,
720 NULL AS achieved_ss_dollars,
721 NULL AS achieved_ss_days,
722 NULL AS target_ss_qty,
723 NULL AS target_ss_dollars,
724 NULL AS target_ss_days,
725 NULL AS userdef_ss_qty,
726 NULL AS userdef_ss_dollars,
727 NULL AS userdef_ss_days,
728 0 AS nr_ss_records,
729 NULL AS inv_value_dollars,
730 CALENDAR_TYPE_MFG AS period_type,
731 NULL AS total_unpooled_safety_stock,
732 NULL AS demand_var_ss_percent,
733 NULL AS mfg_ltvar_ss_percent,
734 NULL AS transit_ltvar_ss_percent,
735 NULL AS sup_ltvar_ss_percent,
736 sum(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) AS delivered_quantity,
737 sum(nvl(msd.using_requirement_quantity, 0) * nvl(msd.probability,1)) AS required_quantity,
738 sum(nvl(msd.service_level, 50)) AS target_service_level,
739 count(*) AS nr_sl_records,
740 decode(GROUPING(msd.customer_id), 1, NULL, nvl(msd.customer_id, UNDEFINED_CUSTOMER_ID)) AS partner_id,
741 decode(GROUPING(cust.customer_class_code), 1, NULL, NVL(cust.customer_class_code, UNDEFINED_CUSTOMER_CODE)) AS customer_class_code,
742 RECORD_SERVICE_LEVEL AS record_type
743 FROM msc_demands msd, msc_system_items msi,
744 msc_item_categories mic, msc_plans mp, msc_plan_organizations mpo, msc_trading_partners cust
745 WHERE mp.plan_id = p_plan_id
746 AND msd.plan_id = msi.plan_id
747 AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
748 AND trunc(msd.using_assembly_demand_date) between l_start_date AND l_end_date
749 AND msd.sr_instance_id = msi.sr_instance_id
750 AND msd.organization_id = msi.organization_id
751 AND msd.inventory_item_id = msi.inventory_item_id
752 AND msd.plan_id = mp.plan_id
753 AND msi.inventory_item_id = NVL(p_item_id, msi.inventory_item_id)
754 AND msi.organization_id = mic.organization_id
755 AND msi.sr_instance_id = mic.sr_instance_id
756 AND mpo.sr_instance_id = nvl(p_sr_instance_id, mpo.sr_instance_id)
757 AND mpo.organization_id = nvl(p_sr_tp_id, mpo.organization_id)
758 AND mic.sr_instance_id = nvl(p_sr_cat_instance_id, mic.sr_instance_id)
759 AND mic.sr_category_id = nvl(p_sr_cat_id, mic.sr_category_id)
760 AND mic.category_set_id = l_default_category_set_id
761 AND msi.inventory_item_id = mic.inventory_item_id
762 AND nvl(msi.abc_class_name, 'XXXXX') = nvl(p_abc_id, nvl(msi.abc_class_name, 'XXXXX'))
763 AND mp.plan_id = mpo.plan_id
764 AND msd.organization_id = mpo.organization_id
765 AND msd.sr_instance_id = mpo.sr_instance_id
766 AND msd.customer_id = cust.partner_id (+)
767 GROUP BY msd.plan_id, mp.compile_designator, trunc(msd.using_assembly_demand_date) ,
768 CUBE (msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name,
769 msd.customer_id, cust.customer_class_code)
770 HAVING (
771 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
772 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
773 AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(msd.customer_id) = 0 AND GROUPING(cust.customer_class_code) = 0 OR
774 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
775 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
776 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 0 OR
777 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
778 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
779 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 0 AND GROUPING(cust.customer_class_code) = 0 OR
780 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
781 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
782 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 1 OR
783 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
784 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
785 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 0 OR
786 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
787 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
788 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 1 OR
789 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
790 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
791 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 0 OR
792 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
793 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
794 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 0 AND GROUPING(cust.customer_class_code) = 0 OR
795 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
796 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
797 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 0 AND GROUPING(cust.customer_class_code) = 0 OR
798 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
799 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
800 AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 1 OR
801 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
802 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
803 AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 1 OR
804 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
805 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
806 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 1 OR
807 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
808 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
809 AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(msd.customer_id) = 1 AND GROUPING(cust.customer_class_code) = 0
810 )
811 ORDER BY 1,2,3,4, 5, 6, 28, 29, 8;
812
813 aNumber NUMBER;
814
815 CURSOR c2 IS
816 SELECT curr_start_date, curr_cutoff_date
817 FROM msc_plans
818 WHERE plan_id = p_plan_id;
819
820 c2Rec c2%ROWTYPE;
821
822 BEGIN
823 -- Get default category set
824 l_default_category_set_id := get_cat_set_id(p_plan_id);
825
826 OPEN c2;
827 FETCH c2 INTO c2Rec;
828
829 if (weekschedule.count = 0) then
830 l_start_date := c2Rec.curr_start_date;
831 else
832 l_start_date := weekschedule(weekschedule.FIRST).bkt_start_date;
833 end if;
834 if (periodschedule.count = 0) then
835 l_end_date := c2Rec.curr_cutoff_date;
836 else
837 l_end_date := periodschedule(periodschedule.LAST).bkt_end_date;
838 end if;
839 CLOSE c2;
840
841 prevRecord.record_type := NULL; -- Used to test if the previous record was assigned
842 OPEN c1;
843 LOOP
844 FETCH c1 INTO currRecord;
845 IF c1%FOUND THEN
846 IF prevRecord.record_type IS NOT NULL THEN -- prev record exists
847 IF is_service_level_key_changed(currRecord, prevRecord) THEN
848 IF (weekschedule.count = 0) THEN
849 null;
850 ELSE
851 bucket_service_level_record(weekschedule, prevRecord, weekschedule(weekschedule.LAST).bkt_start_date + 1);
852 END IF;
853 IF (periodschedule.count = 0) THEN
854 null;
855 ELSE
856 bucket_service_level_record(periodschedule, prevRecord, periodschedule(periodschedule.LAST).bkt_start_date + 1);
857 END IF;
858 schedule_flush(weekschedule, query_id);
859 schedule_flush(periodschedule, query_id, TRUE);
860 schedule_initialize(weekschedule, currRecord);
861 schedule_initialize(periodschedule, currRecord);
862 ELSE
863 bucket_service_level_record(weekschedule, prevRecord, currRecord.bkt_start_date);
864 bucket_service_level_record(periodschedule, prevRecord, currRecord.bkt_start_date);
865 END IF;
866 ELSE
867 schedule_initialize(weekschedule, currRecord);
868 schedule_initialize(periodschedule, currRecord);
869 END IF;
870 prevRecord := currRecord;
871 ELSE
872 IF prevRecord.record_type IS NOT NULL THEN
873 bucket_service_level_record(weekschedule, prevRecord, weekschedule(weekschedule.LAST).bkt_start_date + 1);
874 bucket_service_level_record(periodschedule, prevRecord, periodschedule(periodschedule.LAST).bkt_start_date + 1);
875 schedule_flush(weekschedule, query_id);
876 schedule_flush(periodschedule, query_id, TRUE);
877 END IF;
878 EXIT;
879 END IF;
880 END LOOP;
881 CLOSE c1;
882 END schedule_bucket_service_level;
883
884
885 PROCEDURE schedule_aggregate_sl_perf(p_plan_id NUMBER) IS
886 l_default_category_set_id NUMBER;
887 BEGIN
888 -- Get default category set
889 l_default_category_set_id := get_cat_set_id(p_plan_id);
890
891 -- Do it once for MFG calendar
892 INSERT INTO msc_analysis_aggregate
893 (
894 plan_id,
895 record_type,
896 achieved_service_level_qty1,
897 achieved_service_level_qty2,
898 target_service_level,
899 period_type,
900 week_start_date,
901 period_start_date,
902 sr_instance_id,
903 organization_id,
904 sr_cat_instance_id,
905 sr_category_id,
906 category_name,
907 inventory_item_id,
908 last_update_date,
909 last_updated_by,
910 creation_date,
911 created_by,
912 last_update_login
913 )
914 SELECT
915 msd.plan_id,
916 RECORD_SERVICE_LEVEL AS record_type,
917 sum(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 as achieved_service_level_qty1,
918 sum(nvl(msd.using_requirement_quantity, 0) * nvl(msd.probability,1)) AS achieved_service_level_qty2,
919 decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
920 CALENDAR_TYPE_MFG AS period_type,
921 mpb.bkt_start_date AS week_start_date,
922 mpsd.period_start_date,
923 msd.sr_instance_id AS instance_id,
924 msd.organization_id AS org_id,
925 mic.sr_instance_id AS sr_cat_instance_id,
926 mic.sr_category_id AS sr_category_id,
927 mic.category_name AS category_name,
928 msd.inventory_item_id AS item_id,
929 sysdate,
930 g_user_id,
931 sysdate,
932 g_user_id,
933 to_number(null)
934 FROM msc_plans mp,
935 msc_plan_organizations mpo,
936 msc_demands msd,
937 msc_plan_buckets mpb,
938 msc_system_items msi,
939 msc_item_categories mic,
940 msc_trading_partners owning,
941 msc_period_start_dates mpsd
942 WHERE mp.plan_id = p_plan_id
943 AND msd.plan_id = msi.plan_id
944 AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
945 AND msd.sr_instance_id = msi.sr_instance_id
946 AND msd.organization_id = msi.organization_id
947 AND msd.inventory_item_id = msi.inventory_item_id
948 AND msd.plan_id = mp.plan_id
949 AND mp.plan_id = mpb.plan_id
950 AND mpb.bucket_type IN (2,3)
951 AND trunc(msd.using_assembly_demand_date) BETWEEN mpb.bkt_start_date AND mpb.bkt_end_date
952 AND mp.sr_instance_id = owning.sr_instance_id
953 AND mp.organization_id = owning.sr_tp_id
954 AND owning.partner_type = 3
955 AND owning.calendar_code = mpsd.calendar_code
956 AND exception_set_id = mpsd.exception_set_id
957 AND mpb.sr_instance_id = mpsd.sr_instance_id
958 AND mpb.bkt_start_date >= mpsd.period_start_date AND mpb.bkt_start_date < mpsd.next_date
959 AND msi.organization_id = mic.organization_id
960 AND msi.sr_instance_id = mic.sr_instance_id
961 AND mic.category_set_id = l_default_category_set_id
962 AND msi.inventory_item_id = mic.inventory_item_id
963 AND mp.plan_id = mpo.plan_id
964 AND msd.organization_id = mpo.organization_id
965 AND msd.sr_instance_id = mpo.sr_instance_id
966 GROUP BY msd.plan_id, mpsd.period_start_date, mpb.bucket_type,
967 CUBE (mpb.bkt_start_date, msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
968 HAVING (
969 (
970 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
971 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
972 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 1 OR
973
974 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
975 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
976 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 0 OR
977
978 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
979 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
980 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 1 OR
981
982 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
983 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
984 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 0 OR
985
986 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
987 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
988 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 1 OR
989
990 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
991 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
992 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 0 OR
993
994 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
995 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
996 AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 1 OR
997
998 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
999 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
1000 AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 0
1001 )
1002 AND (mpb.bucket_type <> 3 OR GROUPING(mpb.bkt_start_date) <> 0)
1003 );
1004
1005 -- And once for the BIS calendar
1006 INSERT INTO msc_analysis_aggregate
1007 (
1008 plan_id,
1009 record_type,
1010 achieved_service_level_qty1,
1011 achieved_service_level_qty2,
1012 target_service_level,
1013 period_type,
1014 week_start_date,
1015 period_start_date,
1016 sr_instance_id,
1017 organization_id,
1018 sr_cat_instance_id,
1019 sr_category_id,
1020 category_name,
1021 inventory_item_id,
1022 last_update_date,
1023 last_updated_by,
1024 creation_date,
1025 created_by,
1026 last_update_login
1027 )
1028 SELECT
1029 msd.plan_id,
1030 RECORD_SERVICE_LEVEL AS record_type,
1031 sum(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 as achieved_service_level_qty1,
1032 sum(nvl(msd.using_requirement_quantity, 0) * nvl(msd.probability,1)) AS achieved_service_level_qty2,
1033 decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
1034 CALENDAR_TYPE_BIS AS period_type,
1035 NULL AS week_start_date,
1036 msbp.START_DATE AS period_start_date,
1037 msd.sr_instance_id AS instance_id,
1038 msd.organization_id AS org_id,
1039 mic.sr_instance_id AS sr_cat_instance_id,
1040 mic.sr_category_id AS sr_category_id,
1041 mic.category_name AS category_name,
1042 msd.inventory_item_id AS item_id,
1043 sysdate,
1044 g_user_id,
1045 sysdate,
1046 g_user_id,
1047 to_number(null)
1048 FROM msc_plans mp,
1049 msc_plan_organizations mpo,
1050 msc_demands msd,
1051 msc_system_items msi,
1052 msc_item_categories mic,
1053 msc_bis_periods msbp
1054 WHERE mp.plan_id = p_plan_id
1055 AND mp.plan_id = mpo.plan_id
1056 AND msd.organization_id = mpo.organization_id
1057 AND msd.sr_instance_id = mpo.sr_instance_id
1058 AND msd.plan_id = mpo.plan_id
1059 AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
1060 AND msd.plan_id = msi.plan_id
1061 AND msd.sr_instance_id = msi.sr_instance_id
1062 AND msd.organization_id = msi.organization_id
1063 AND msd.inventory_item_id = msi.inventory_item_id
1064 AND msi.organization_id = mic.organization_id
1065 AND msi.sr_instance_id = mic.sr_instance_id
1066 AND msi.inventory_item_id = mic.inventory_item_id
1067 AND mic.category_set_id = l_default_category_set_id
1068 AND mp.sr_instance_id = msbp.sr_instance_id
1069 AND mp.organization_id = msbp.organization_id
1070 AND msbp.period_set_name = g_period_setname
1071 AND TRUNC(msd.using_assembly_demand_date) >= msbp.START_DATE AND TRUNC(msd.using_assembly_demand_date) < msbp.end_date
1072 GROUP BY msd.plan_id, msbp.start_date,
1073 CUBE (msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
1074 HAVING (
1075 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msd.inventory_item_id) = 1 OR
1076 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msd.inventory_item_id) = 1 OR
1077 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msd.inventory_item_id) = 0 OR
1078 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msd.inventory_item_id) = 1
1079 );
1080 END schedule_aggregate_sl_perf;
1081
1082
1083 PROCEDURE schedule_aggregate_sl(p_plan_id NUMBER) IS
1084 l_default_category_set_id NUMBER;
1085 BEGIN
1086 if(g_perf_prof_on) then
1087 schedule_aggregate_sl_perf(p_plan_id);
1088 else
1089
1090 -- Get default category set
1091 l_default_category_set_id := get_cat_set_id(p_plan_id);
1092
1093 -- Do it once for MFG calendar
1094 INSERT INTO msc_analysis_aggregate
1095 (
1096 plan_id,
1097 record_type,
1098 achieved_service_level_qty1,
1099 achieved_service_level_qty2,
1100 target_service_level,
1101 period_type,
1102 week_start_date,
1103 period_start_date,
1104 sr_instance_id,
1105 organization_id,
1106 sr_cat_instance_id,
1107 sr_category_id,
1108 category_name,
1109 inventory_item_id,
1110 last_update_date,
1111 last_updated_by,
1112 creation_date,
1113 created_by,
1114 last_update_login
1115 )
1116 SELECT
1117 msd.plan_id,
1118 RECORD_SERVICE_LEVEL AS record_type,
1119 sum(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 as achieved_service_level_qty1,
1120 sum(DECODE(NVL(msd.using_requirement_quantity, 1) ,0 ,1 , NVL(msd.using_requirement_quantity, 1)) * nvl(msd.probability,1)) AS achieved_service_level_qty2,
1121 decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
1122 CALENDAR_TYPE_MFG AS period_type,
1123 mpb.bkt_start_date AS week_start_date,
1124 mpsd.period_start_date,
1125 msd.sr_instance_id AS instance_id,
1126 msd.organization_id AS org_id,
1127 mic.sr_instance_id AS sr_cat_instance_id,
1128 mic.sr_category_id AS sr_category_id,
1129 mic.category_name AS category_name,
1130 msd.inventory_item_id AS item_id,
1131 sysdate,
1132 g_user_id,
1133 sysdate,
1134 g_user_id,
1135 to_number(null)
1136 FROM msc_plans mp,
1137 msc_plan_organizations mpo,
1138 msc_demands msd,
1139 msc_plan_buckets mpb,
1140 msc_system_items msi,
1141 msc_item_categories mic,
1142 msc_trading_partners owning,
1143 msc_period_start_dates mpsd
1144 WHERE mp.plan_id = p_plan_id
1145 AND msd.plan_id = msi.plan_id
1146 AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
1147 AND msd.sr_instance_id = msi.sr_instance_id
1148 AND msd.organization_id = msi.organization_id
1149 AND msd.inventory_item_id = msi.inventory_item_id
1150 AND msd.plan_id = mp.plan_id
1151 AND mp.plan_id = mpb.plan_id
1152 AND mpb.bucket_type IN (2,3)
1153 AND trunc(msd.using_assembly_demand_date) BETWEEN mpb.bkt_start_date AND mpb.bkt_end_date
1154 AND mp.sr_instance_id = owning.sr_instance_id
1155 AND mp.organization_id = owning.sr_tp_id
1156 AND owning.partner_type = 3
1157 AND owning.calendar_code = mpsd.calendar_code
1158 AND exception_set_id = mpsd.exception_set_id
1159 AND mpb.sr_instance_id = mpsd.sr_instance_id
1160 AND mpb.bkt_start_date >= mpsd.period_start_date AND mpb.bkt_start_date < mpsd.next_date
1161 AND msi.organization_id = mic.organization_id
1162 AND msi.sr_instance_id = mic.sr_instance_id
1163 AND mic.category_set_id = l_default_category_set_id
1164 AND msi.inventory_item_id = mic.inventory_item_id
1165 AND mp.plan_id = mpo.plan_id
1166 AND msd.organization_id = mpo.organization_id
1167 AND msd.sr_instance_id = mpo.sr_instance_id
1168 GROUP BY msd.plan_id, mpsd.period_start_date, mpb.bucket_type,
1169 CUBE (mpb.bkt_start_date, msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
1170 HAVING (
1171 (
1172 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
1173 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
1174 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 1 OR
1175 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
1176 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
1177 AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 1 OR
1178 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
1179 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
1180 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 1 OR
1181 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
1182 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
1183 AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 1 OR
1184 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
1185 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
1186 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 1 OR
1187 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
1188 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
1189 AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 1 OR
1190 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
1191 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
1192 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 1 OR
1193 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
1194 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
1195 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 0 OR
1196 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
1197 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
1198 AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 0 OR
1199 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
1200 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
1201 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 0 OR
1202 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1
1203 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
1204 AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 0 OR
1205 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
1206 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1
1207 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 0 OR
1208 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
1209 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1
1210 AND GROUPING(mic.category_name) = 1 AND GROUPING(msd.inventory_item_id) = 0 AND GROUPING(mpb.bkt_start_date) = 0 OR
1211 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
1212 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
1213 AND GROUPING(msd.inventory_item_id) = 1 AND GROUPING(mpb.bkt_start_date) = 0
1214 )
1215 AND (mpb.bucket_type <> 3 OR GROUPING(mpb.bkt_start_date) <> 0)
1216 );
1217
1218 -- And once for the BIS calendar
1219 INSERT INTO msc_analysis_aggregate
1220 (
1221 plan_id,
1222 record_type,
1223 achieved_service_level_qty1,
1224 achieved_service_level_qty2,
1225 target_service_level,
1226 period_type,
1227 week_start_date,
1228 period_start_date,
1229 sr_instance_id,
1230 organization_id,
1231 sr_cat_instance_id,
1232 sr_category_id,
1233 category_name,
1234 inventory_item_id,
1235 last_update_date,
1236 last_updated_by,
1237 creation_date,
1238 created_by,
1239 last_update_login
1240 )
1241 SELECT
1242 msd.plan_id,
1243 RECORD_SERVICE_LEVEL AS record_type,
1244 sum(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 as achieved_service_level_qty1,
1245 sum(DECODE(NVL(msd.using_requirement_quantity, 1) ,0 ,1 , NVL(msd.using_requirement_quantity, 1)) * nvl(msd.probability,1)) AS achieved_service_level_qty2,
1246 decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
1247 CALENDAR_TYPE_BIS AS period_type,
1248 NULL AS week_start_date,
1249 msbp.START_DATE AS period_start_date,
1250 msd.sr_instance_id AS instance_id,
1251 msd.organization_id AS org_id,
1252 mic.sr_instance_id AS sr_cat_instance_id,
1253 mic.sr_category_id AS sr_category_id,
1254 mic.category_name AS category_name,
1255 msd.inventory_item_id AS item_id,
1256 sysdate,
1257 g_user_id,
1258 sysdate,
1259 g_user_id,
1260 to_number(null)
1261 FROM msc_plans mp,
1262 msc_plan_organizations mpo,
1263 msc_demands msd,
1264 msc_system_items msi,
1265 msc_item_categories mic,
1266 msc_bis_periods msbp
1267 WHERE mp.plan_id = p_plan_id
1268 AND mp.plan_id = mpo.plan_id
1269 AND msd.organization_id = mpo.organization_id
1270 AND msd.sr_instance_id = mpo.sr_instance_id
1271 AND msd.plan_id = mpo.plan_id
1272 AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
1273 AND msd.plan_id = msi.plan_id
1274 AND msd.sr_instance_id = msi.sr_instance_id
1275 AND msd.organization_id = msi.organization_id
1276 AND msd.inventory_item_id = msi.inventory_item_id
1277 AND msi.organization_id = mic.organization_id
1278 AND msi.sr_instance_id = mic.sr_instance_id
1279 AND msi.inventory_item_id = mic.inventory_item_id
1280 AND mic.category_set_id = l_default_category_set_id
1281 AND mp.sr_instance_id = msbp.sr_instance_id
1282 AND mp.organization_id = msbp.organization_id
1283 AND msbp.period_set_name = g_period_setname
1284 AND TRUNC(msd.using_assembly_demand_date) >= msbp.START_DATE AND TRUNC(msd.using_assembly_demand_date) < msbp.end_date
1285 GROUP BY msd.plan_id, msbp.start_date,
1286 CUBE (msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
1287 HAVING (
1288 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msd.inventory_item_id) = 1 OR
1289 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msd.inventory_item_id) = 0 OR
1290 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msd.inventory_item_id) = 1 OR
1291 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msd.inventory_item_id) = 0 OR
1292 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msd.inventory_item_id) = 1 OR
1293 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msd.inventory_item_id) = 0 OR
1294 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msd.inventory_item_id) = 1
1295 );
1296 end if;
1297 END schedule_aggregate_sl;
1298
1299 PROCEDURE schedule_aggregate_sl_brkdown(p_plan_id NUMBER) IS
1300 l_default_category_set_id NUMBER;
1301 BEGIN
1302 -- Get default category set
1303 l_default_category_set_id := get_cat_set_id(p_plan_id);
1304
1305 /*
1306 -- Do it once for MFG calendar
1307 INSERT INTO msc_analysis_aggregate
1308 (
1309 plan_id,
1310 record_type,
1311 achieved_service_level_qty1,
1312 achieved_service_level_qty2,
1313 target_service_level,
1314 period_type,
1315 week_start_date,
1316 period_start_date,
1317 sr_instance_id,
1318 organization_id,
1319 sr_cat_instance_id,
1320 sr_category_id,
1321 category_name,
1322 inventory_item_id,
1323 demand_class,
1324 category_set_id,
1325 last_update_date,
1326 last_updated_by,
1327 creation_date,
1328 created_by,
1329 last_update_login
1330 )
1331 SELECT
1332 msd.plan_id,
1333 RECORD_SERVICE_LEVEL_BRKDOWN AS record_type,
1334 SUM(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 AS achieved_service_level_qty1,
1335 SUM(DECODE(NVL(msd.using_requirement_quantity, 1) ,0 ,1 , NVL(msd.using_requirement_quantity, 1)) * nvl(msd.probability,1)) AS achieved_service_level_qty2,
1336 decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
1337 CALENDAR_TYPE_MFG AS period_type,
1338 mpb.bkt_start_date AS week_start_date,
1339 mpsd.period_start_date,
1340 msd.sr_instance_id AS instance_id,
1341 msd.organization_id AS org_id,
1342 mic.sr_instance_id AS sr_cat_instance_id,
1343 mic.sr_category_id AS sr_category_id,
1344 mic.category_name AS category_name,
1345 msd.inventory_item_id AS item_id,
1346 msd.demand_class as demand_class,
1347 mic.category_set_id,
1348 sysdate,
1349 g_user_id,
1350 sysdate,
1351 g_user_id,
1352 to_number(null)
1353 FROM msc_plans mp,
1354 msc_plan_organizations mpo,
1355 msc_demands msd,
1356 msc_plan_buckets mpb,
1357 msc_system_items msi,
1358 msc_item_categories mic,
1359 msc_trading_partners owning,
1360 msc_period_start_dates mpsd
1361 WHERE mp.plan_id = p_plan_id
1362 AND msd.plan_id = msi.plan_id
1363 AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
1364 AND msd.sr_instance_id = msi.sr_instance_id
1365 AND msd.organization_id = msi.organization_id
1366 AND msd.inventory_item_id = msi.inventory_item_id
1367 AND msd.plan_id = mp.plan_id
1368 AND mp.plan_id = mpb.plan_id
1369 AND mpb.bucket_type IN (2,3)
1370 AND trunc(msd.using_assembly_demand_date) BETWEEN mpb.bkt_start_date AND mpb.bkt_end_date
1371 AND mp.sr_instance_id = owning.sr_instance_id
1372 AND mp.organization_id = owning.sr_tp_id
1373 AND owning.partner_type = 3
1374 AND owning.calendar_code = mpsd.calendar_code
1375 AND exception_set_id = mpsd.exception_set_id
1376 AND mpb.sr_instance_id = mpsd.sr_instance_id
1377 AND mpb.bkt_start_date >= mpsd.period_start_date AND mpb.bkt_start_date < mpsd.next_date
1378 AND msi.organization_id = mic.organization_id
1379 AND msi.sr_instance_id = mic.sr_instance_id
1380 AND mic.category_set_id = l_default_category_set_id
1381 AND msi.inventory_item_id = mic.inventory_item_id
1382 AND mp.plan_id = mpo.plan_id
1383 AND msd.organization_id = mpo.organization_id
1384 AND msd.sr_instance_id = mpo.sr_instance_id
1385 GROUP BY msd.plan_id, mpsd.period_start_date, mpb.bucket_type, mic.category_set_id,
1386 CUBE (mpb.bkt_start_date, msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name,
1387 msd.demand_class)
1388 HAVING (
1389 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
1390 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
1391 AND GROUPING(msd.inventory_item_id) =0
1392 AND ( GROUPING(msd.demand_class) = 0)
1393 -- AND ( GROUPING(mpb.bkt_start_date) = 0)
1394 );
1395 */
1396
1397 -- Do it once for MFG calendar
1398 --insert the week data first
1399 INSERT INTO msc_analysis_aggregate
1400 (
1401 plan_id,
1402 record_type,
1403 achieved_service_level_qty1,
1404 achieved_service_level_qty2,
1405 target_service_level,
1406 period_type,
1407 week_start_date,
1408 period_start_date,
1409 sr_instance_id,
1410 organization_id,
1411 sr_cat_instance_id,
1412 sr_category_id,
1413 category_name,
1414 inventory_item_id,
1415 demand_class,
1416 category_set_id,
1417 last_update_date,
1418 last_updated_by,
1419 creation_date,
1420 created_by,
1421 last_update_login
1422 )
1423 SELECT
1424 msd.plan_id,
1425 RECORD_SERVICE_LEVEL_BRKDOWN AS record_type,
1426 SUM(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 AS achieved_service_level_qty1,
1427 SUM(DECODE(NVL(msd.using_requirement_quantity, 1) ,0 ,1 , NVL(msd.using_requirement_quantity, 1)) * nvl(msd.probability,1)) AS achieved_service_level_qty2,
1428 decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
1429 CALENDAR_TYPE_MFG AS period_type,
1430 mpb.bkt_start_date AS week_start_date,
1431 null as period_start_date,
1432 msd.sr_instance_id AS instance_id,
1433 msd.organization_id AS org_id,
1434 mic.sr_instance_id AS sr_cat_instance_id,
1435 mic.sr_category_id AS sr_category_id,
1436 mic.category_name AS category_name,
1437 msd.inventory_item_id AS item_id,
1438 msd.demand_class as demand_class,
1439 mic.category_set_id,
1440 sysdate,
1441 g_user_id,
1442 sysdate,
1443 g_user_id,
1444 to_number(null)
1445 FROM msc_plans mp,
1446 msc_plan_organizations mpo,
1447 msc_demands msd,
1448 msc_plan_buckets mpb,
1449 msc_system_items msi,
1450 msc_item_categories mic,
1451 msc_trading_partners owning
1452 --msc_period_start_dates mpsd
1453 WHERE mp.plan_id = p_plan_id
1454 AND msd.plan_id = msi.plan_id
1455 AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
1456 AND msd.sr_instance_id = msi.sr_instance_id
1457 AND msd.organization_id = msi.organization_id
1458 AND msd.inventory_item_id = msi.inventory_item_id
1459 AND msd.plan_id = mp.plan_id
1460 AND mp.plan_id = mpb.plan_id
1461 AND mpb.bucket_type IN (2)
1462 AND trunc(msd.using_assembly_demand_date) BETWEEN mpb.bkt_start_date AND mpb.bkt_end_date
1463 AND mp.sr_instance_id = owning.sr_instance_id
1464 AND mp.organization_id = owning.sr_tp_id
1465 AND owning.partner_type = 3
1466 -- AND owning.calendar_code = mpsd.calendar_code
1467 --AND exception_set_id = mpsd.exception_set_id
1468 --AND mpb.sr_instance_id = mpsd.sr_instance_id
1469 --AND mpb.bkt_start_date >= mpsd.period_start_date AND mpb.bkt_start_date < mpsd.next_date
1470 AND msi.organization_id = mic.organization_id
1471 AND msi.sr_instance_id = mic.sr_instance_id
1472 AND mic.category_set_id = l_default_category_set_id
1473 AND msi.inventory_item_id = mic.inventory_item_id
1474 AND mp.plan_id = mpo.plan_id
1475 AND msd.organization_id = mpo.organization_id
1476 AND msd.sr_instance_id = mpo.sr_instance_id
1477 and mpb.bkt_start_date between mp.curr_start_date and mp.curr_cutoff_date
1478 GROUP BY msd.plan_id, mpb.bkt_start_date, mpb.bucket_type, mic.category_set_id,
1479 msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name, msd.demand_class;
1480
1481 --insert the period data for MFG
1482 INSERT INTO msc_analysis_aggregate
1483 (
1484 plan_id,
1485 record_type,
1486 achieved_service_level_qty1,
1487 achieved_service_level_qty2,
1488 target_service_level,
1489 period_type,
1490 week_start_date,
1491 period_start_date,
1492 sr_instance_id,
1493 organization_id,
1494 sr_cat_instance_id,
1495 sr_category_id,
1496 category_name,
1497 inventory_item_id,
1498 demand_class,
1499 category_set_id,
1500 last_update_date,
1501 last_updated_by,
1502 creation_date,
1503 created_by,
1504 last_update_login
1505 )
1506 SELECT
1507 msd.plan_id,
1508 RECORD_SERVICE_LEVEL_BRKDOWN AS record_type,
1509 SUM(NVL(msd.old_demand_quantity,0) * NVL(msd.probability,1)) * 100 AS achieved_service_level_qty1,
1510 SUM(DECODE(NVL(msd.using_requirement_quantity, 1) ,0 ,1 , NVL(msd.using_requirement_quantity, 1)) * NVL(msd.probability,1)) AS achieved_service_level_qty2,
1511 DECODE(COUNT(*), 0, 50, SUM(NVL(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
1512 CALENDAR_TYPE_MFG AS period_type,
1513 NULL AS week_start_date,
1514 mpsd.period_start_date,
1515 msd.sr_instance_id AS instance_id,
1516 msd.organization_id AS org_id,
1517 mic.sr_instance_id AS sr_cat_instance_id,
1518 mic.sr_category_id AS sr_category_id,
1519 mic.category_name AS category_name,
1520 msd.inventory_item_id AS item_id,
1521 msd.demand_class AS demand_class,
1522 mic.category_set_id,
1523 sysdate,
1524 g_user_id,
1525 sysdate,
1526 g_user_id,
1527 to_number(null)
1528 FROM msc_plans mp,
1529 msc_trading_partners owning,
1530 msc_period_start_dates mpsd,
1531 msc_demands msd,
1532 msc_system_items msi,
1533 msc_item_categories mic
1534 WHERE mp.plan_id = p_plan_id
1535 AND mp.sr_instance_id = owning.sr_instance_id
1536 AND mp.organization_id = owning.sr_tp_id
1537 AND owning.partner_type = 3
1538 AND owning.calendar_code = mpsd.calendar_code
1539 -- AND exception_set_id = exception_set_id
1540 AND owning.sr_instance_id = mpsd.sr_instance_id
1541 AND mpsd.next_date > mp.curr_start_date
1542 AND mpsd.period_start_date <= mp.curr_cutoff_date
1543 AND msi.plan_id = mp.plan_id
1544 AND msi.organization_id = mic.organization_id
1545 AND msi.sr_instance_id = mic.sr_instance_id
1546 AND msi.inventory_item_id = mic.inventory_item_id
1547 AND mic.category_set_id = l_default_category_set_id
1548 AND msd.plan_id = msi.plan_id
1549 AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
1550 AND msd.sr_instance_id = msi.sr_instance_id
1551 AND msd.organization_id = msi.organization_id
1552 AND msd.inventory_item_id = msi.inventory_item_id
1553 AND TRUNC(msd.using_assembly_demand_date) BETWEEN mpsd.period_start_date AND mpsd.next_date
1554 GROUP BY msd.plan_id,mpsd.period_start_date, mic.category_set_id,
1555 msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name, msd.demand_class;
1556
1557 -- And once for the BIS calendar
1558 INSERT INTO msc_analysis_aggregate
1559 (
1560 plan_id,
1561 record_type,
1562 achieved_service_level_qty1,
1563 achieved_service_level_qty2,
1564 target_service_level,
1565 period_type,
1566 week_start_date,
1567 period_start_date,
1568 sr_instance_id,
1569 organization_id,
1570 sr_cat_instance_id,
1571 sr_category_id,
1572 category_name,
1573 inventory_item_id,
1574 demand_class,
1575 category_set_id,
1576 last_update_date,
1577 last_updated_by,
1578 creation_date,
1579 created_by,
1580 last_update_login
1581 )
1582 SELECT
1583 msd.plan_id,
1584 RECORD_SERVICE_LEVEL_BRKDOWN AS record_type,
1585 SUM(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 AS achieved_service_level_qty1,
1586 SUM(DECODE(NVL(msd.using_requirement_quantity, 1) ,0 ,1 , NVL(msd.using_requirement_quantity, 1)) * nvl(msd.probability,1)) AS achieved_service_level_qty2,
1587 decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
1588 CALENDAR_TYPE_BIS AS period_type,
1589 NULL AS week_start_date,
1590 msbp.START_DATE AS period_start_date,
1591 msd.sr_instance_id AS instance_id,
1592 msd.organization_id AS org_id,
1593 mic.sr_instance_id AS sr_cat_instance_id,
1594 mic.sr_category_id AS sr_category_id,
1595 mic.category_name AS category_name,
1596 msd.inventory_item_id AS item_id,
1597 msd.demand_class as demand_class,
1598 mic.category_set_id,
1599 sysdate,
1600 g_user_id,
1601 sysdate,
1602 g_user_id,
1603 to_number(null)
1604 FROM msc_plans mp,
1605 msc_plan_organizations mpo,
1606 msc_demands msd,
1607 msc_system_items msi,
1608 msc_item_categories mic,
1609 msc_bis_periods msbp
1610 WHERE mp.plan_id = p_plan_id
1611 AND mp.plan_id = mpo.plan_id
1612 AND msd.organization_id = mpo.organization_id
1613 AND msd.sr_instance_id = mpo.sr_instance_id
1614 AND msd.plan_id = mpo.plan_id
1615 AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
1616 AND msd.plan_id = msi.plan_id
1617 AND msd.sr_instance_id = msi.sr_instance_id
1618 AND msd.organization_id = msi.organization_id
1619 AND msd.inventory_item_id = msi.inventory_item_id
1620 AND msi.organization_id = mic.organization_id
1621 AND msi.sr_instance_id = mic.sr_instance_id
1622 AND msi.inventory_item_id = mic.inventory_item_id
1623 AND mic.category_set_id = l_default_category_set_id
1624 AND mp.sr_instance_id = msbp.sr_instance_id
1625 AND mp.organization_id = msbp.organization_id
1626 AND msbp.period_set_name = g_period_setname
1627 AND TRUNC(msd.using_assembly_demand_date) >= msbp.START_DATE AND TRUNC(msd.using_assembly_demand_date) < msbp.end_date
1628 GROUP BY msd.plan_id, msbp.start_date,mic.category_set_id,
1629 CUBE (msd.inventory_item_id, msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name, msd.demand_class)
1630 HAVING (
1631 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0
1632 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
1633 AND GROUPING(msd.inventory_item_id) =0
1634 AND GROUPING(msd.demand_class) = 0
1635 );
1636 END schedule_aggregate_sl_brkdown;
1637
1638 -- for search by plan and view by plan only
1639 PROCEDURE schedule_aggregate_cost(p_plan_id NUMBER) IS
1640 l_default_category_set_id NUMBER;
1641 BEGIN
1642 -- Get default category set
1643 l_default_category_set_id := get_cat_set_id(p_plan_id);
1644
1645 -- Do it once for MFG calendar
1646 INSERT INTO msc_analysis_aggregate
1647 (
1648 plan_id,
1649 plan_name,
1650 record_type,
1651 planned_production_cost,
1652 planned_carrying_cost,
1653 planned_purchasing_cost,
1654 planned_tp_cost,
1655 planned_total_cost,
1656 planned_revenue,
1657 period_type,
1658 detail_level,
1659 category_set_id,
1660 category_id,
1661 category_name,
1662 last_update_date,
1663 last_updated_by,
1664 creation_date,
1665 created_by,
1666 last_update_login
1667 )
1668 SELECT
1669 plan_id,
1670 plan_name,
1671 RECORD_COST_BRKDOWN AS record_type,
1672 sum(planned_production_cost),
1673 sum(planned_carrying_cost),
1674 sum(planned_purchasing_cost),
1675 0,
1676 sum(planned_total_cost),
1677 sum(planned_revenue),
1678 CALENDAR_TYPE_MFG AS period_type,
1679 detail_level,
1680 category_set_id,
1681 category_id,
1682 category_name,
1683 sysdate,
1684 g_user_id,
1685 sysdate,
1686 g_user_id,
1687 to_number(null)
1688 FROM msc_cost_breakdown_notpcost_v
1689 WHERE plan_id = p_plan_id
1690 and nvl(detail_level,0) = 0
1691 and nvl(period_type,0) = 1
1692 GROUP BY plan_id, plan_name, period_type,period_type, detail_level,category_set_id, category_id,category_name;
1693
1694 -- And once for the BIS calendar
1695 INSERT INTO msc_analysis_aggregate
1696 (
1697 plan_id,
1698 plan_name,
1699 record_type,
1700 planned_production_cost,
1701 planned_carrying_cost,
1702 planned_purchasing_cost,
1703 planned_tp_cost,
1704 planned_total_cost,
1705 planned_revenue,
1706 period_type,
1707 detail_level,
1708 category_set_id,
1709 category_id,
1710 category_name,
1711 last_update_date,
1712 last_updated_by,
1713 creation_date,
1714 created_by,
1715 last_update_login
1716 )
1717 SELECT
1718 plan_id,
1719 plan_name,
1720 RECORD_COST_BRKDOWN AS record_type,
1721 sum(planned_production_cost),
1722 sum(planned_carrying_cost),
1723 sum(planned_purchasing_cost),
1724 0,
1725 sum(planned_total_cost),
1726 sum(planned_revenue),
1727 CALENDAR_TYPE_BIS AS period_type,
1728 detail_level,
1729 category_set_id,
1730 category_id,
1731 category_name,
1732 sysdate,
1733 g_user_id,
1734 sysdate,
1735 g_user_id,
1736 to_number(null)
1737 FROM msc_cost_breakdown_notpcost_v
1738 WHERE plan_id = p_plan_id
1739 and nvl(detail_level,0) = 0
1740 and nvl(period_type,0) = 0
1741 GROUP BY plan_id, plan_name, period_type,period_type, detail_level,category_set_id,category_id,category_name;
1742
1743 END schedule_aggregate_cost;
1744
1745
1746
1747 PROCEDURE schedule_aggregate_iv(p_plan_id NUMBER) IS
1748 l_default_category_set_id NUMBER;
1749 BEGIN
1750 -- Get default category set
1751 l_default_category_set_id := get_cat_set_id(p_plan_id);
1752
1753 -- Do it once for the MFG calendar
1754 INSERT INTO msc_analysis_aggregate
1755 (
1756 plan_id,
1757 record_type,
1758 inventory_value_dollars,
1759 period_type,
1760 week_start_date,
1761 period_start_date,
1762 sr_instance_id,
1763 organization_id,
1764 sr_cat_instance_id,
1765 sr_category_id,
1766 category_name,
1767 inventory_item_id,
1768 last_update_date,
1769 last_updated_by,
1770 creation_date,
1771 created_by,
1772 last_update_login
1773 )
1774 SELECT
1775 mp.plan_id,
1776 RECORD_INVENTORY_VALUE AS record_type,
1777 SUM(nvl(mbid.inventory_value, 0)) AS inventory_value_dollars,
1778 CALENDAR_TYPE_MFG AS period_type,
1779 decode(NVL(mbid.detail_level, 0), 0, NULL, mbid.detail_date) AS week_start_date,
1780 mpsd.period_start_date,
1781 mpo.sr_instance_id AS instance_id,
1782 mpo.organization_id AS org_id,
1783 mic.sr_instance_id AS sr_cat_instance_id,
1784 mic.sr_category_id AS sr_category_id,
1785 mic.category_name AS category_name,
1786 NULL AS item_id,
1787 sysdate,
1788 g_user_id,
1789 sysdate,
1790 g_user_id,
1791 to_number(null)
1792 FROM msc_plans mp,
1793 msc_plan_organizations mpo,
1794 msc_bis_inv_detail mbid,
1795 msc_system_items mis,
1796 msc_item_categories mic,
1797 msc_trading_partners mtp,
1798 msc_period_start_dates mpsd,
1799 msc_plan_buckets mpb
1800 WHERE mp.plan_id = p_plan_id
1801 AND mp.plan_id = mpo.plan_id
1802 AND mpo.plan_id = mbid.plan_id
1803 AND mpo.organization_id = mbid.organization_id
1804 AND mpo.sr_instance_id = mbid.sr_instance_id
1805 AND mbid.period_type = CALENDAR_TYPE_MFG
1806 -- BEGIN FUNNY SECTION
1807 -- For some strange reason MSC_BIS_INV_DETAIL has week records for period buckets
1808 -- So we need to filter thsese out before we aggregate
1809 AND mpb.plan_id = mbid.plan_id
1810 AND (mpb.bucket_type = 2 OR nvl(detail_level, DETAIL_LEVEL_PERIOD) = DETAIL_LEVEL_PERIOD)
1811 AND mbid.detail_date BETWEEN mpb.bkt_start_date AND mpb.bkt_end_date
1812 -- END FUNNY SECTION
1813 AND mbid.plan_id = mis.plan_id
1814 AND mbid.organization_id = mis.organization_id
1815 AND mbid.sr_instance_id = mis.sr_instance_id
1816 AND mbid.inventory_item_id = mis.inventory_item_id
1817 AND mis.budget_constrained = BUDGET_CONSTRAINED_ON
1818 AND mis.organization_id = mic.organization_id
1819 AND mis.sr_instance_id = mic.sr_instance_id
1820 AND mis.inventory_item_id = mic.inventory_item_id
1821 AND mic.category_set_id = l_default_category_set_id
1822 AND mtp.sr_tp_id = mp.organization_id
1823 AND mtp.sr_instance_id = mp.sr_instance_id
1824 AND mtp.partner_type = 3
1825 AND mtp.sr_Instance_id = mpsd.sr_instance_id
1826 AND mtp.calendar_code = mpsd.calendar_code
1827 AND mtp.calendar_exception_set_id = mpsd.exception_set_id
1828 AND ((nvl(mbid.detail_level, DETAIL_LEVEL_PERIOD) = DETAIL_LEVEL_PERIOD AND mbid.detail_date = mpsd.period_start_date) OR
1829 (mbid.detail_level = DETAIL_LEVEL_WEEK AND mbid.detail_date >= mpsd.period_start_date AND mbid.detail_date < mpsd.next_date))
1830 GROUP BY mp.plan_id, mbid.period_type, mpsd.period_start_date, mbid.detail_date, NVL(mbid.detail_level, 0),
1831 CUBE(mpo.sr_instance_id, mpo.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
1832 HAVING (
1833 GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
1834 GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 OR
1835 GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
1836 GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
1837 );
1838
1839 -- Do it again for the BIS calendar
1840 INSERT INTO msc_analysis_aggregate
1841 (
1842 plan_id,
1843 record_type,
1844 inventory_value_dollars,
1845 period_type,
1846 week_start_date,
1847 period_start_date,
1848 sr_instance_id,
1849 organization_id,
1850 sr_cat_instance_id,
1851 sr_category_id,
1852 category_name,
1853 inventory_item_id,
1854 last_update_date,
1855 last_updated_by,
1856 creation_date,
1857 created_by,
1858 last_update_login
1859 )
1860 SELECT
1861 mp.plan_id,
1862 RECORD_INVENTORY_VALUE AS record_type,
1863 SUM(nvl(mbid.inventory_value, 0)) AS inventory_value_dollars,
1864 CALENDAR_TYPE_BIS AS period_type,
1865 NULL AS week_start_date,
1866 mpsd.start_date,
1867 mpo.sr_instance_id AS instance_id,
1868 mpo.organization_id AS org_id,
1869 mic.sr_instance_id AS sr_cat_instance_id,
1870 mic.sr_category_id AS sr_category_id,
1871 mic.category_name AS category_name,
1872 NULL AS item_id,
1873 sysdate,
1874 g_user_id,
1875 sysdate,
1876 g_user_id,
1877 to_number(null)
1878 FROM msc_plans mp,
1879 msc_plan_organizations mpo,
1880 msc_bis_inv_detail mbid,
1881 msc_system_items mis,
1882 msc_item_categories mic,
1883 msc_bis_periods mpsd
1884 WHERE mp.plan_id = p_plan_id
1885 AND mp.plan_id = mpo.plan_id
1886 AND mpo.plan_id = mbid.plan_id
1887 AND mpo.organization_id = mbid.organization_id
1888 AND mpo.sr_instance_id = mbid.sr_instance_id
1889 AND nvl(mbid.period_type, CALENDAR_TYPE_BIS) = CALENDAR_TYPE_BIS
1890 AND mbid.plan_id = mis.plan_id
1891 AND mbid.organization_id = mis.organization_id
1892 AND mbid.sr_instance_id = mis.sr_instance_id
1893 AND mbid.inventory_item_id = mis.inventory_item_id
1894 AND mis.budget_constrained = BUDGET_CONSTRAINED_ON
1895 AND mis.organization_id = mic.organization_id
1896 AND mis.sr_instance_id = mic.sr_instance_id
1897 AND mis.inventory_item_id = mic.inventory_item_id
1898 AND mic.category_set_id = l_default_category_set_id
1899 AND mp.sr_instance_id = mpsd.sr_instance_id
1900 AND mp.organization_id = mpsd.organization_id
1901 AND mpsd.period_set_name = g_period_setname
1902 AND mbid.detail_date = mpsd.START_DATE
1903 GROUP BY mp.plan_id, mbid.period_type, mpsd.start_date, mbid.detail_date, NVL(mbid.detail_level, 0),
1904 CUBE(mpo.sr_instance_id, mpo.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
1905 HAVING (
1906 GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
1907 GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 OR
1908 GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
1909 GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
1910 );
1911 END schedule_aggregate_iv;
1912
1913 PROCEDURE schedule_bucket_week_to_period(weekschedule IN OUT NOCOPY Schedule, periodschedule IN OUT NOCOPY Schedule)
1914 IS
1915 BEGIN
1916 if (weekschedule.count = 0 ) then
1917 return;
1918 end if;
1919 FOR i IN weekschedule.FIRST..weekschedule.LAST LOOP
1920 IF weekschedule(i).last_week_of_period = 'Y' THEN
1921 FOR j IN periodschedule.FIRST..periodschedule.LAST LOOP
1922 IF is_bucket_match(periodschedule(j), weekschedule(i)) THEN
1923 schedule_initialize_bkt(periodschedule(j), periodschedule(j));
1924 bucket_ss_copy(periodschedule(j), weekschedule(i));
1925 END IF;
1926 END LOOP;
1927 END IF;
1928 END LOOP;
1929 END schedule_bucket_week_to_period;
1930
1931 PROCEDURE schedule_aggregate_ss(weekschedule IN OUT NOCOPY Schedule,
1932 periodschedule IN OUT NOCOPY Schedule, p_plan_id NUMBER)
1933 IS
1934 currRecord Bucket;
1935 prevRecord Bucket;
1936
1937 l_default_category_set_id NUMBER;
1938
1939 CURSOR c1 IS
1940 SELECT mss.plan_id AS plan_id,
1941 mss.sr_instance_id AS instance_id,
1942 mss.organization_id AS org_id,
1943 msi.inventory_item_id AS item_id,
1944 mic.sr_instance_id AS sr_category_inst_id,
1945 mic.sr_category_id AS sr_category_id,
1946 mic.category_name AS category_name,
1947 trunc(mss.period_start_date) AS week_start_date,
1948 NULL AS week_next_date,
1949 NULL AS period_start_date,
1950 'N' AS last_week_of_period,
1951 sum(nvl(mss.safety_stock_quantity, 0)) AS achieved_ss_qty,
1952 sum(nvl(mss.safety_stock_quantity*msi.standard_cost, 0)) AS achieved_ss_dollars,
1953 sum(nvl(mss.achieved_days_of_supply, 0)) AS achieved_ss_days,
1954 sum(nvl(mss.target_safety_stock, 0)) AS target_ss_qty,
1955 sum(nvl(mss.target_safety_stock * msi.standard_cost, 0)) AS target_ss_dollars,
1956 sum(nvl(mss.target_days_of_supply, 0)) AS target_ss_days,
1957 sum(nvl(mss.user_defined_safety_stocks, 0)) AS userdef_ss_qty,
1958 sum(nvl(mss.user_defined_safety_stocks * msi.standard_cost, 0)) AS userdef_ss_dollars,
1959 sum(nvl(mss.user_defined_dos, 0)) AS userdef_ss_days,
1960 0 AS nr_ss_records,
1961 NULL AS inv_value_dollars,
1962 NULL AS period_type,
1963 sum(nvl(mss.total_unpooled_safety_stock, 0)) AS total_unpooled_safety_stock,
1964 decode( sum(mss.total_unpooled_safety_stock), 0, 0,
1965 sum((mss.demand_var_ss_percent *mss.total_unpooled_safety_stock)/100)
1966 / sum(mss.total_unpooled_safety_stock))*100 AS demand_var_ss_percent,
1967 decode( sum(mss.total_unpooled_safety_stock), 0, 0,
1968 sum((mss.mfg_ltvar_ss_percent *mss.total_unpooled_safety_stock)/100)
1969 / sum(mss.total_unpooled_safety_stock))*100 AS mfg_ltvar_ss_percent,
1970 decode( sum(mss.total_unpooled_safety_stock), 0, 0,
1971 sum((mss.transit_ltvar_ss_percent *mss.total_unpooled_safety_stock)/100)
1972 / sum(mss.total_unpooled_safety_stock))*100 AS transit_ltvar_ss_percent,
1973 decode( sum(mss.total_unpooled_safety_stock), 0, 0,
1974 sum((mss.sup_ltvar_ss_percent *mss.total_unpooled_safety_stock)/100)
1975 / sum(mss.total_unpooled_safety_stock))*100 AS sup_ltvar_ss_percent,
1976 NULL AS delivered_quantity,
1977 NULL AS required_quantity,
1978 NULL AS target_service_level,
1979 0 AS nr_sl_records,
1980 NULL AS partner_id,
1981 NULL AS customer_class_code,
1982 RECORD_SAFETY_STOCK AS record_type
1983 FROM msc_plan_organizations mpo, msc_safety_stocks mss,
1984 msc_system_items msi, msc_item_categories mic
1985 WHERE mss.sr_instance_id = msi.sr_instance_id
1986 AND mss.plan_id = msi.plan_id
1987 AND mss.organization_id = msi.organization_id
1988 AND mss.inventory_item_id = msi.inventory_item_id
1989 AND msi.sr_instance_id = mic.sr_instance_id
1990 AND msi.inventory_item_id = mic.inventory_item_id
1991 AND msi.organization_id = mic.organization_id
1992 AND mic.category_set_id = l_default_category_set_id
1993 AND mss.plan_id = p_plan_id
1994 AND mss.plan_id = mpo.plan_id
1995 AND mss.organization_id = mpo.organization_id
1996 AND mss.sr_instance_id = mpo.sr_instance_id
1997 GROUP BY mss.plan_id, TRUNC(mss.period_start_date),
1998 CUBE (msi.inventory_item_id, mss.sr_instance_id, mss.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
1999 HAVING
2000 GROUPING (mss.sr_instance_id) = 1 AND GROUPING (mss.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msi.inventory_item_id) = 1 OR
2001 GROUPING (mss.sr_instance_id) = 0 AND GROUPING (mss.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msi.inventory_item_id) = 1 OR
2002 GROUPING (mss.sr_instance_id) = 0 AND GROUPING (mss.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msi.inventory_item_id) = 1 OR
2003 GROUPING (mss.sr_instance_id) = 1 AND GROUPING (mss.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msi.inventory_item_id) = 1 OR
2004 GROUPING (mss.sr_instance_id) = 1 AND GROUPING (mss.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msi.inventory_item_id) = 0 OR
2005 GROUPING (mss.sr_instance_id) = 1 AND GROUPING (mss.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msi.inventory_item_id) = 0 OR
2006 GROUPING (mss.sr_instance_id) = 0 AND GROUPING (mss.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msi.inventory_item_id) = 0
2007 ORDER BY 1,2,3,4, 5, 6, 11;
2008
2009 CURSOR cperf IS
2010 SELECT mss.plan_id AS plan_id,
2011 mss.sr_instance_id AS instance_id,
2012 mss.organization_id AS org_id,
2013 msi.inventory_item_id AS item_id,
2014 mic.sr_instance_id AS sr_category_inst_id,
2015 mic.sr_category_id AS sr_category_id,
2016 mic.category_name AS category_name,
2017 --trunc(mss.period_start_date) AS week_start_date,
2018 trunc(mpb.bkt_start_date) AS week_start_date,
2019 NULL AS week_next_date,
2020 NULL AS period_start_date,
2021 'N' AS last_week_of_period,
2022 sum(nvl(mss.safety_stock_quantity, 0)) AS achieved_ss_qty,
2023 sum(nvl(mss.safety_stock_quantity*msi.standard_cost, 0)) AS achieved_ss_dollars,
2024 sum(nvl(mss.achieved_days_of_supply, 0)) AS achieved_ss_days,
2025 sum(nvl(mss.target_safety_stock, 0)) AS target_ss_qty,
2026 sum(nvl(mss.target_safety_stock * msi.standard_cost, 0)) AS target_ss_dollars,
2027 sum(nvl(mss.target_days_of_supply, 0)) AS target_ss_days,
2028 sum(nvl(mss.user_defined_safety_stocks, 0)) AS userdef_ss_qty,
2029 sum(nvl(mss.user_defined_safety_stocks * msi.standard_cost, 0)) AS userdef_ss_dollars,
2030 sum(nvl(mss.user_defined_dos, 0)) AS userdef_ss_days,
2031 0 AS total_unpooled_safety_stock,
2032 0 AS demand_var_ss_percent,
2033 0 AS mfg_ltvar_ss_percent,
2034 0 AS transit_ltvar_ss_percent,
2035 0 AS sup_ltvar_ss_percent,
2036 0 AS nr_ss_records,
2037 NULL AS inv_value_dollars,
2038 NULL AS period_type,
2039 NULL AS delivered_quantity,
2040 NULL AS required_quantity,
2041 NULL AS target_service_level,
2042 0 AS nr_sl_records,
2043 NULL AS partner_id,
2044 NULL AS customer_class_code,
2045 RECORD_SAFETY_STOCK AS record_type
2046 FROM msc_plan_organizations mpo, msc_safety_stocks mss,
2047 msc_system_items msi, msc_item_categories mic,
2048 msc_plan_buckets mpb
2049 WHERE mss.sr_instance_id = msi.sr_instance_id
2050 AND mss.plan_id = msi.plan_id
2051 AND mss.organization_id = msi.organization_id
2052 AND mss.inventory_item_id = msi.inventory_item_id
2053 AND msi.sr_instance_id = mic.sr_instance_id
2054 AND msi.inventory_item_id = mic.inventory_item_id
2055 AND msi.organization_id = mic.organization_id
2056 AND mic.category_set_id = l_default_category_set_id
2057 AND mss.plan_id = p_plan_id
2058 AND mss.plan_id = mpo.plan_id
2059 AND mss.plan_id = mpb.plan_id
2060 AND mss.organization_id = mpo.organization_id
2061 AND mss.sr_instance_id = mpo.sr_instance_id
2062 AND mss.period_start_date between mpb.bkt_start_date and mpb.bkt_end_date
2063 GROUP BY mss.plan_id, TRUNC(mpb.bkt_start_date),
2064 CUBE (msi.inventory_item_id, mss.sr_instance_id, mss.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
2065 HAVING
2066 GROUPING (mss.sr_instance_id) = 1 AND GROUPING (mss.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msi.inventory_item_id) = 1 OR
2067 GROUPING (mss.sr_instance_id) = 0 AND GROUPING (mss.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 AND GROUPING(msi.inventory_item_id) = 1 OR
2068 GROUPING (mss.sr_instance_id) = 0 AND GROUPING (mss.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msi.inventory_item_id) = 1 OR
2069 GROUPING (mss.sr_instance_id) = 0 AND GROUPING (mss.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 AND GROUPING(msi.inventory_item_id) = 0
2070 ORDER BY 1,2,3,4, 5, 6, 11;
2071
2072 curFound Boolean := FALSE;
2073
2074 TYPE curType IS REF CURSOR;
2075 l_cursor curType;
2076
2077 cFound BOOLEAN;
2078
2079 BEGIN
2080 -- Get default category set
2081 l_default_category_set_id := get_cat_set_id(p_plan_id);
2082
2083 prevRecord.bkt_start_date := NULL; -- Used to test if the previous record was assigned
2084
2085 if(g_perf_prof_on) then
2086 open cperf;
2087 else
2088 open c1;
2089 end if;
2090 LOOP
2091 if(g_perf_prof_on) then
2092 FETCH cperf INTO currRecord;
2093 IF cperf%FOUND THEN
2094 cFound := TRUE;
2095 END IF;
2096 else
2097 FETCH c1 INTO currRecord;
2098 IF c1%FOUND THEN
2099 cFound := TRUE;
2100 END IF;
2101 end if;
2102 IF (cFound) THEN
2103 IF prevRecord.bkt_start_date IS NOT NULL THEN -- prev record exists
2104 IF is_safety_stock_key_changed(currRecord, prevRecord) THEN
2105 if (weekschedule.count = 0) then
2106 null;
2107 else
2108 bucket_safety_stock_record(weekschedule, prevRecord, weekschedule(weekschedule.LAST).bkt_start_date + 1);
2109 END IF;
2110 if (periodschedule.count = 0) then
2111 null;
2112 else
2113 bucket_safety_stock_record(periodschedule, prevRecord, periodschedule(periodschedule.LAST).bkt_start_date + 1);
2114 END IF;
2115 schedule_bucket_week_to_period(weekschedule, periodschedule);
2116 schedule_ss_fill_gaps(weekschedule);
2117 schedule_ss_fill_gaps(periodschedule);
2118 schedule_aggregate_flush(weekschedule);
2119 schedule_aggregate_flush(periodschedule, TRUE);
2120 schedule_initialize(weekschedule, currRecord);
2121 schedule_initialize(periodschedule, currRecord);
2122 ELSE
2123
2124 bucket_safety_stock_record(weekschedule, prevRecord, currRecord.bkt_start_date);
2125 if (periodschedule.count = 0) then
2126 null;
2127 else
2128 bucket_safety_stock_record(periodschedule, prevRecord, periodschedule(periodschedule.LAST).bkt_start_date + 1);
2129 end if;
2130 END IF;
2131 ELSE
2132 schedule_initialize(weekschedule, currRecord);
2133 schedule_initialize(periodschedule, currRecord);
2134 END IF;
2135 prevRecord := currRecord;
2136 ELSE
2137 IF prevRecord.bkt_start_date IS NOT NULL THEN
2138 if (weekschedule.count = 0) then
2139 null;
2140 else
2141 bucket_safety_stock_record(weekschedule, prevRecord, weekschedule(weekschedule.LAST).bkt_start_date + 1);
2142 end if;
2143 if (weekschedule.count = 0) then
2144 null;
2145 else
2146 bucket_safety_stock_record(periodschedule, prevRecord, periodschedule(periodschedule.LAST).bkt_start_date + 1);
2147 end if;
2148 schedule_bucket_week_to_period(weekschedule, periodschedule);
2149 schedule_ss_fill_gaps(weekschedule);
2150 schedule_ss_fill_gaps(periodschedule);
2151 schedule_aggregate_flush(weekschedule);
2152 schedule_aggregate_flush(periodschedule, TRUE);
2153 END IF;
2154 EXIT;
2155 END IF;
2156 cFound := FALSE;
2157 END LOOP;
2158 if(g_perf_prof_on) then
2159 CLOSE cperf;
2160 else
2161 CLOSE c1;
2162 end if;
2163 END schedule_aggregate_ss;
2164
2165 PROCEDURE schedule_bucket_safety_stock(weekschedule IN OUT NOCOPY Schedule, periodschedule IN OUT NOCOPY Schedule,
2166 p_plan_id NUMBER, p_sr_instance_id NUMBER,
2167 p_sr_tp_id NUMBER, p_sr_cat_instance_id NUMBER, p_sr_cat_id NUMBER,
2168 p_item_id NUMBER, query_id NUMBER)
2169 IS
2170 currRecord Bucket;
2171 prevRecord Bucket;
2172 l_start_date DATE;
2173 l_end_date DATE;
2174
2175 l_default_category_set_id NUMBER;
2176
2177 CURSOR c1 IS
2178 SELECT mss.plan_id AS plan_id, mss.sr_instance_id AS instance_id, mss.organization_id AS org_id,
2179 mss.inventory_item_id AS item_id,
2180 mic.sr_instance_id AS sr_category_inst_id,
2181 mic.sr_category_id AS sr_category_id, mic.category_name AS category_name,
2182 mss.period_start_date AS week_start_date,
2183 NULL AS week_next_date,
2184 NULL AS period_start_date,
2185 'N' AS last_week_of_period,
2186 mss.safety_stock_quantity AS achieved_ss_qty,
2187 mss.safety_stock_quantity*msi.standard_cost
2188 AS achieved_ss_dollars,
2189 mss.achieved_days_of_supply AS achieved_ss_days,
2190 mss.target_safety_stock AS target_ss_qty,
2191 mss.target_safety_stock * msi.standard_cost
2192 AS target_ss_dollars,
2193 mss.target_days_of_supply AS target_ss_days,
2194 mss.user_defined_safety_stocks AS userdef_ss_qty,
2195 mss.user_defined_safety_stocks * msi.standard_cost
2196 AS userdef_ss_dollars,
2197 mss.user_defined_dos AS userdef_ss_days,
2198 0 AS nr_ss_records,
2199 NULL AS inv_value_dollars,
2200 CALENDAR_TYPE_MFG AS period_type,
2201 nvl(mss.total_unpooled_safety_stock, 0) AS total_unpooled_safety_stock,
2202 nvl(mss.demand_var_ss_percent, 0) AS demand_var_ss_percent,
2203 nvl(mss.mfg_ltvar_ss_percent, 0) AS mfg_ltvar_ss_percent,
2204 nvl(mss.transit_ltvar_ss_percent, 0) AS transit_ltvar_ss_percent,
2205 nvl(mss.sup_ltvar_ss_percent, 0) AS sup_ltvar_ss_percent,
2206 NULL AS delivered_quantity,
2207 NULL AS required_quantity,
2208 NULL AS target_service_level,
2209 0 AS nr_sl_records,
2210 NULL AS partner_id,
2211 NULL AS customer_class_code,
2212 RECORD_SAFETY_STOCK AS record_type
2213 FROM msc_safety_stocks mss, msc_system_items msi, msc_item_categories mic,
2214 msc_plans mp, msc_plan_organizations mpo
2215 WHERE mss.sr_instance_id = msi.sr_instance_id
2216 AND mss.plan_id = msi.plan_id
2217 AND mss.organization_id = msi.organization_id
2218 AND mss.inventory_item_id = msi.inventory_item_id
2219 AND msi.inventory_item_id = NVL(p_item_id, msi.inventory_item_id)
2220 AND msi.sr_instance_id = mic.sr_instance_id
2221 AND msi.inventory_item_id = mic.inventory_item_id
2222 AND msi.organization_id = mic.organization_id
2223 AND mic.category_set_id = l_default_category_set_id
2224 AND mss.plan_id = p_plan_id
2225 AND mss.plan_id = mp.plan_id
2226 AND mp.plan_id = mpo.plan_id
2227 AND mss.organization_id = mpo.organization_id
2228 AND mss.sr_instance_id = mpo.sr_instance_id
2229 AND mss.period_start_date BETWEEN l_start_date AND l_end_date
2230 AND mpo.sr_instance_id = nvl(p_sr_instance_id, mpo.sr_instance_id)
2231 AND mpo.organization_id = nvl(p_sr_tp_id, mpo.organization_id)
2232 AND mic.sr_instance_id = nvl(p_sr_cat_instance_id, mic.sr_instance_id)
2233 AND mic.sr_category_id = nvl(p_sr_cat_id, mic.sr_category_id)
2234 ORDER BY 2,3,4,5,6;
2235
2236 CURSOR c2 IS
2237 SELECT curr_start_date, curr_cutoff_date
2238 FROM msc_plans
2239 WHERE plan_id = p_plan_id;
2240
2241 c2Rec c2%ROWTYPE;
2242
2243
2244 BEGIN
2245 -- Get default category set
2246 l_default_category_set_id := get_cat_set_id(p_plan_id);
2247
2248 OPEN c2;
2249 FETCH c2 INTO c2Rec;
2250
2251 if(weekschedule.count = 0) then
2252 l_start_date := c2Rec.curr_start_date;
2253 else
2254 l_start_date := weekschedule(weekschedule.FIRST).bkt_start_date;
2255 end if;
2256
2257 if (periodschedule.count = 0) then
2258 l_end_date := c2Rec.curr_cutoff_date;
2259 else
2260 l_end_date := periodschedule(periodschedule.LAST).bkt_end_date;
2261 end if;
2262
2263 CLOSE c2;
2264
2265
2266 prevRecord.bkt_start_date := NULL; -- Used to test if the previous record was assigned
2267 OPEN c1;
2268 LOOP
2269 FETCH c1 INTO currRecord;
2270 IF c1%FOUND THEN
2271 IF prevRecord.bkt_start_date IS NOT NULL THEN -- prev record exists
2272 IF is_safety_stock_key_changed(currRecord, prevRecord) THEN
2273
2274 if (weekschedule.count = 0) then
2275 null;
2276 else
2277 bucket_safety_stock_record(weekschedule, prevRecord, weekschedule(weekschedule.LAST).bkt_start_date + 1);
2278 end if;
2279 if (periodschedule.count = 0) then
2280 null;
2281 else
2282 bucket_safety_stock_record(periodschedule, prevRecord, periodschedule(periodschedule.LAST).bkt_start_date + 1);
2283 end if;
2284 schedule_bucket_week_to_period(weekschedule, periodschedule);
2285 schedule_ss_fill_gaps(weekschedule);
2286 schedule_ss_fill_gaps(periodschedule);
2287 schedule_flush(weekschedule, query_id);
2288 schedule_flush(periodschedule, query_id, TRUE);
2289 schedule_initialize(weekschedule, currRecord);
2290 schedule_initialize(periodschedule, currRecord);
2291 ELSE
2292 bucket_safety_stock_record(weekschedule, prevRecord, currRecord.bkt_start_date);
2293 if (periodschedule.count = 0) then
2294 null;
2295 else
2296 bucket_safety_stock_record(periodschedule, prevRecord, periodschedule(periodschedule.LAST).bkt_start_date + 1);
2297 end if;
2298 END IF;
2299 ELSE
2300 schedule_initialize(weekschedule, currRecord);
2301 schedule_initialize(periodschedule, currRecord);
2302 END IF;
2303 prevRecord := currRecord;
2304 ELSE
2305 IF prevRecord.bkt_start_date IS NOT NULL THEN
2306 if (weekschedule.count = 0) then
2307 null;
2308 else
2309 bucket_safety_stock_record(weekschedule, prevRecord, weekschedule(weekschedule.LAST).bkt_start_date + 1);
2310 end if;
2311 if (periodschedule.count = 0) then
2312 null;
2313 else
2314 bucket_safety_stock_record(periodschedule, prevRecord, periodschedule(periodschedule.LAST).bkt_start_date + 1);
2315 end if;
2316 schedule_bucket_week_to_period(weekschedule, periodschedule);
2317 schedule_ss_fill_gaps(weekschedule);
2318 schedule_ss_fill_gaps(periodschedule);
2319 schedule_flush(weekschedule, query_id);
2320 schedule_flush(periodschedule, query_id, TRUE);
2321 END IF;
2322 EXIT;
2323 END IF;
2324 END LOOP;
2325 CLOSE c1;
2326 END schedule_bucket_safety_stock;
2327
2328 FUNCTION validate_plan(p_plan_id NUMBER)
2329 RETURN BOOLEAN
2330 IS
2331 CURSOR c1(p_plan_id NUMBER) IS
2332 SELECT 1
2333 FROM msc_plans mp
2334 WHERE mp.curr_plan_type = 4 OR mp.curr_plan_type = 9
2335 AND mp.curr_start_date IS NOT NULL
2336 AND mp.plan_id = p_plan_id;
2337
2338 l_result NUMBER := NULL;
2339 BEGIN
2340
2341 OPEN c1(p_plan_id);
2342 FETCH c1 INTO l_result;
2343 CLOSE c1;
2344
2345 IF l_result = 1 THEN
2346 RETURN TRUE;
2347 ELSE
2348 RETURN FALSE;
2349 END IF;
2350
2351 EXCEPTION
2352 WHEN OTHERS THEN
2353 RETURN FALSE;
2354 END validate_plan;
2355
2356 FUNCTION get_sr_cat_id_from_cat_key(org_id VARCHAR2)
2357 RETURN NUMBER
2358 IS
2359 dash NUMBER;
2360 BEGIN
2361 IF org_id IS NULL THEN
2362 RETURN NULL;
2363 END IF;
2364
2365 dash := INSTR(org_id, '-');
2366
2367 IF dash = 0 OR dash = 1 THEN
2368 RETURN NULL;
2369 END IF;
2370
2371 RETURN SUBSTR(org_id, dash + 1);
2372 END get_sr_cat_id_from_cat_key;
2373
2374 FUNCTION get_inst_id_from_org_key(org_id VARCHAR2)
2375 RETURN NUMBER
2376 IS
2377 dash NUMBER;
2378 BEGIN
2379 IF org_id IS NULL THEN
2380 RETURN NULL;
2381 END IF;
2382
2383 dash := INSTR(org_id, '-');
2384
2385 IF dash = 0 OR dash = 1 THEN
2386 RETURN NULL;
2387 END IF;
2388
2389 RETURN SUBSTR(org_id, 1, dash - 1);
2390 END get_inst_id_from_org_key;
2391
2392 FUNCTION get_sr_tp_id_from_org_key(cat_id VARCHAR2)
2393 RETURN NUMBER
2394 IS
2395 dash NUMBER;
2396 BEGIN
2397 IF cat_id IS NULL THEN
2398 RETURN NULL;
2399 END IF;
2400
2401 dash := INSTR(cat_id, '-');
2402
2403 IF dash = 0 OR dash = 1 THEN
2404 RETURN NULL;
2405 END IF;
2406
2407 RETURN SUBSTR(cat_id, dash + 1);
2408 END get_sr_tp_id_from_org_key;
2409
2410 FUNCTION get_inst_id_from_category_key(cat_id VARCHAR2)
2411 RETURN NUMBER
2412 IS
2413 dash NUMBER;
2414 BEGIN
2415 IF cat_id IS NULL THEN
2416 RETURN NULL;
2417 END IF;
2418
2419 dash := INSTR(cat_id, '-');
2420
2421 IF dash = 0 OR dash = 1 THEN
2422 RETURN NULL;
2423 END IF;
2424
2425 RETURN SUBSTR(cat_id, 1, dash - 1);
2426 END get_inst_id_from_category_key;
2427
2428 PROCEDURE schedule_details_iv(p_query_id OUT NOCOPY NUMBER, p_period_type IN NUMBER,
2429 p_plan_id IN VARCHAR2, org_id IN VARCHAR2, cat_id IN VARCHAR2, p_abc_id IN VARCHAR2)
2430 IS
2431 weekschedule Schedule;
2432 periodschedule Schedule;
2433 l_sr_instance_id NUMBER;
2434 l_sr_tp_id NUMBER;
2435 l_sr_cat_instance_id NUMBER;
2436 l_sr_cat_id NUMBER;
2437 l_planlist PlanList;
2438 l_index BINARY_INTEGER;
2439 l_default_category_set_id NUMBER;
2440
2441 BEGIN
2442
2443 -- Get default category set
2444 l_default_category_set_id := get_cat_set_id(p_plan_id);
2445
2446 SELECT msc_form_query_s.nextval
2447 INTO p_query_id
2448 FROM dual;
2449
2450 IF org_id IS NOT NULL THEN
2451 l_sr_instance_id := get_inst_id_from_org_key(org_id);
2452 l_sr_tp_id := get_sr_tp_id_from_org_key(org_id);
2453 END IF;
2454
2455 IF cat_id IS NOT NULL THEN
2456 l_sr_cat_instance_id := get_inst_id_from_category_key(cat_id);
2457 l_sr_cat_id := get_sr_cat_id_from_cat_key(cat_id);
2458 END IF;
2459
2460 parse_planlist(p_plan_id, l_planlist);
2461 IF l_planlist.COUNT = 0 THEN
2462 p_query_id := -1;
2463 RETURN;
2464 END IF;
2465
2466 FOR l_index IN l_planlist.FIRST..l_planlist.LAST LOOP
2467 IF validate_plan(l_planlist(l_index)) = FALSE THEN
2468 p_query_id := -1;
2469 RETURN;
2470 END IF;
2471
2472 IF p_period_type = CALENDAR_TYPE_MFG THEN
2473
2474 INSERT INTO msc_form_query
2475 (
2476 query_id,
2477 NUMBER1, -- PLAN_ID
2478 CREATED_BY, -- RECORD_TYPE
2479 NUMBER9, -- achieved_ss_qty
2480 NUMBER10,
2481 NUMBER11,
2482 NUMBER12,
2483 NUMBER13,
2484 NUMBER14,
2485 NUMBER15,
2486 NUMBER16,
2487 PROGRAM_ID,
2488 PROGRAM_APPLICATION_ID, -- achieved_sl
2489 LAST_UPDATE_LOGIN, -- target_sl
2490 NUMBER6, -- inventory_value_dollars
2491 NUMBER7, -- period_type
2492 DATE1, -- week_start_date
2493 DATE3, -- period_start_date
2494 NUMBER2, -- sr_instance_id
2495 NUMBER3, -- org_id
2496 REQUEST_ID, -- sr_cat_instance_id
2497 NUMBER5, -- sr_category_id
2498 CHAR1, -- category_name
2499 NUMBER4, -- inventory_item_id
2500 LAST_UPDATE_DATE,
2501 LAST_UPDATED_BY,
2502 CREATION_DATE
2503 )
2504 SELECT
2505 p_query_id,
2506 mp.plan_id,
2507 RECORD_INVENTORY_VALUE AS record_type,
2508 NULL AS achieved_ss_qty,
2509 NULL AS achieved_ss_dollars,
2510 NULL AS achieved_ss_days,
2511 NULL AS target_ss_qty,
2512 NULL AS target_ss_dollars,
2513 NULL AS target_ss_days,
2514 NULL AS userdef_ss_qty,
2515 NULL AS userdef_ss_dollars,
2516 NULL AS userdef_ss_days,
2517 NULL AS achieved_sl,
2518 NULL AS target_service_level,
2519 SUM(nvl(mbid.inventory_value, 0)) AS inventory_value_dollars,
2520 CALENDAR_TYPE_MFG AS period_type,
2521 decode(NVL(mbid.detail_level, 0), 0, NULL, mbid.detail_date) AS week_start_date,
2522 mpsd.period_start_date,
2523 mpo.sr_instance_id AS instance_id,
2524 mpo.organization_id AS org_id,
2525 mic.sr_instance_id AS sr_cat_instance_id,
2526 mic.sr_category_id AS sr_category_id,
2527 mic.category_name AS category_name,
2528 NULL AS item_id,
2529 SYSDATE AS last_update_date,
2530 -1 AS last_updated_by,
2531 SYSDATE AS CREATION_DATE
2532 FROM msc_plans mp, msc_plan_organizations mpo, msc_bis_inv_detail mbid, msc_system_items mis, msc_item_categories mic,
2533 msc_trading_partners mtp, msc_period_start_dates mpsd, msc_plan_buckets mpb
2534 WHERE mp.plan_id = l_planlist(l_index)
2535 AND mp.plan_id = mpo.plan_id
2536 AND mpo.plan_id = mbid.plan_id
2537 AND mpo.organization_id = mbid.organization_id
2538 AND mpo.sr_instance_id = mbid.sr_instance_id
2539 AND mpo.sr_instance_id = nvl(l_sr_instance_id, mpo.sr_instance_id)
2540 AND mpo.organization_id = nvl(l_sr_tp_id, mpo.organization_id)
2541 AND mbid.period_type = CALENDAR_TYPE_MFG
2542 -- BEGIN FUNNY SECTION
2543 -- For some strange reason MSC_BIS_INV_DETAIL has week records for period buckets
2544 -- So we need to filter these out before we aggregate
2545 AND mpb.plan_id = mbid.plan_id
2546 AND (mpb.bucket_type = 2 OR nvl(detail_level, DETAIL_LEVEL_PERIOD) = DETAIL_LEVEL_PERIOD)
2547 AND mbid.detail_date BETWEEN mpb.bkt_start_date AND mpb.bkt_end_date
2548 -- END FUNNY SECTION
2549 AND mbid.plan_id = mis.plan_id
2550 AND mbid.organization_id = mis.organization_id
2551 AND mbid.sr_instance_id = mis.sr_instance_id
2552 AND mbid.inventory_item_id = mis.inventory_item_id
2553 AND nvl(mis.abc_class_name, 'XXXXX') = nvl(p_abc_id, nvl(mis.abc_class_name, 'XXXXX'))
2554 AND mis.budget_constrained = BUDGET_CONSTRAINED_ON
2555 AND mis.organization_id = mic.organization_id
2556 AND mis.sr_instance_id = mic.sr_instance_id
2557 AND mis.inventory_item_id = mic.inventory_item_id
2558 AND mic.sr_instance_id = nvl(l_sr_cat_instance_id, mic.sr_instance_id)
2559 AND mic.sr_category_id = nvl(l_sr_cat_id, mic.sr_category_id)
2560 AND mic.category_set_id = l_default_category_set_id
2561 AND mtp.sr_tp_id = mp.organization_id
2562 AND mtp.sr_instance_id = mp.sr_instance_id
2563 AND mtp.partner_type = 3
2564 AND mtp.sr_Instance_id = mpsd.sr_instance_id
2565 AND mtp.calendar_code = mpsd.calendar_code
2566 AND mtp.calendar_exception_set_id = mpsd.exception_set_id
2567 AND ((nvl(mbid.detail_level, DETAIL_LEVEL_PERIOD) = DETAIL_LEVEL_PERIOD AND mbid.detail_date = mpsd.period_start_date) OR
2568 (mbid.detail_level = DETAIL_LEVEL_WEEK AND mbid.detail_date >= mpsd.period_start_date AND mbid.detail_date < mpsd.next_date))
2569 GROUP BY mp.plan_id, mbid.period_type, mpsd.period_start_date, mbid.detail_date, NVL(mbid.detail_level, 0),
2570 CUBE(mpo.sr_instance_id, mpo.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
2571 HAVING (
2572 GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
2573 GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 OR
2574 GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
2575 GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
2576 );
2577
2578 ELSIF p_period_type = CALENDAR_TYPE_BIS THEN
2579
2580 INSERT INTO msc_form_query
2581 (
2582 query_id,
2583 NUMBER1, -- PLAN_ID
2584 CREATED_BY, -- RECORD_TYPE
2585 NUMBER9, -- achieved_ss_qty
2586 NUMBER10,
2587 NUMBER11,
2588 NUMBER12,
2589 NUMBER13,
2590 NUMBER14,
2591 NUMBER15,
2592 NUMBER16,
2593 PROGRAM_ID,
2594 PROGRAM_APPLICATION_ID, -- achieved_sl
2595 LAST_UPDATE_LOGIN, -- target_sl
2596 NUMBER6, -- inventory_value_dollars
2597 NUMBER7, -- period_type
2598 DATE1, -- week_start_date
2599 DATE3, -- period_start_date
2600 NUMBER2, -- sr_instance_id
2601 NUMBER3, -- org_id
2602 REQUEST_ID, -- sr_cat_instance_id
2603 NUMBER5, -- sr_category_id
2604 CHAR1, -- category_name
2605 NUMBER4, -- inventory_item_id
2606 LAST_UPDATE_DATE,
2607 LAST_UPDATED_BY,
2608 CREATION_DATE
2609 )
2610 SELECT
2611 p_query_id,
2612 mp.plan_id,
2613 RECORD_INVENTORY_VALUE AS record_type,
2614 NULL AS achieved_ss_qty,
2615 NULL AS achieved_ss_dollars,
2616 NULL AS achieved_ss_days,
2617 NULL AS target_ss_qty,
2618 NULL AS target_ss_dollars,
2619 NULL AS target_ss_days,
2620 NULL AS userdef_ss_qty,
2621 NULL AS userdef_ss_dollars,
2622 NULL AS userdef_ss_days,
2623 NULL AS achieved_sl,
2624 NULL AS target_service_level,
2625 SUM(nvl(mbid.inventory_value, 0)) AS inventory_value_dollars,
2626 CALENDAR_TYPE_BIS AS period_type,
2627 NULL AS week_start_date,
2628 mpsd.start_date,
2629 mpo.sr_instance_id AS instance_id,
2630 mpo.organization_id AS org_id,
2631 mic.sr_instance_id AS sr_cat_instance_id,
2632 mic.sr_category_id AS sr_category_id,
2633 mic.category_name AS category_name,
2634 NULL AS item_id,
2635 SYSDATE AS last_update_date,
2636 -1 AS last_updated_by,
2637 SYSDATE AS CREATION_DATE
2638 FROM msc_plans mp, msc_plan_organizations mpo, msc_bis_inv_detail mbid, msc_system_items mis, msc_item_categories mic,
2639 msc_bis_periods mpsd
2640 WHERE mp.plan_id = l_planlist(l_index)
2641 AND mp.plan_id = mpo.plan_id
2642 AND mpo.plan_id = mbid.plan_id
2643 AND mpo.organization_id = mbid.organization_id
2644 AND mpo.sr_instance_id = mbid.sr_instance_id
2645 AND mpo.sr_instance_id = nvl(l_sr_instance_id, mpo.sr_instance_id)
2646 AND mpo.organization_id = nvl(l_sr_tp_id, mpo.organization_id)
2647 AND nvl(mbid.period_type, CALENDAR_TYPE_BIS) = CALENDAR_TYPE_BIS
2648 AND mbid.plan_id = mis.plan_id
2649 AND mbid.organization_id = mis.organization_id
2650 AND mbid.sr_instance_id = mis.sr_instance_id
2651 AND mbid.inventory_item_id = mis.inventory_item_id
2652 AND nvl(mis.abc_class_name, 'XXXXX') = nvl(p_abc_id, nvl(mis.abc_class_name, 'XXXXX'))
2653 AND mis.budget_constrained = BUDGET_CONSTRAINED_ON
2654 AND mis.organization_id = mic.organization_id
2655 AND mis.sr_instance_id = mic.sr_instance_id
2656 AND mis.inventory_item_id = mic.inventory_item_id
2657 AND mic.category_set_id = l_default_category_set_id
2658 AND mic.sr_instance_id = nvl(l_sr_cat_instance_id, mic.sr_instance_id)
2659 AND mic.sr_category_id = nvl(l_sr_cat_id, mic.sr_category_id)
2660 AND mp.sr_instance_id = mpsd.sr_instance_id
2661 AND mp.organization_id = mpsd.organization_id
2662 AND mpsd.period_set_name = g_period_setname
2663 AND mbid.detail_date = mpsd.START_DATE
2664 GROUP BY mp.plan_id, mbid.period_type, mpsd.start_date, mbid.detail_date, NVL(mbid.detail_level, 0),
2665 CUBE(mpo.sr_instance_id, mpo.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
2666 HAVING (
2667 GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
2668 GROUPING(mpo.sr_instance_id) = 1 AND GROUPING(mpo.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 OR
2669 GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
2670 GROUPING(mpo.sr_instance_id) = 0 AND GROUPING(mpo.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
2671 );
2672 END IF;
2673
2674 END LOOP;
2675
2676 END schedule_details_iv;
2677
2678 PROCEDURE schedule_details_ss(query_id OUT NOCOPY NUMBER, plan_id IN VARCHAR2, org_id IN VARCHAR2, cat_id IN VARCHAR2,
2679 item_id IN NUMBER)
2680 IS
2681 weekschedule Schedule;
2682 periodschedule Schedule;
2683 sr_instance_id NUMBER;
2684 sr_tp_id NUMBER;
2685 sr_cat_instance_id NUMBER;
2686 sr_cat_id NUMBER;
2687 l_planlist PlanList;
2688 l_index BINARY_INTEGER;
2689 BEGIN
2690
2691 SELECT msc_form_query_s.nextval
2692 INTO query_id
2693 FROM dual;
2694
2695 IF org_id IS NOT NULL THEN
2696 sr_instance_id := get_inst_id_from_org_key(org_id);
2697 sr_tp_id := get_sr_tp_id_from_org_key(org_id);
2698 END IF;
2699
2700 IF cat_id IS NOT NULL THEN
2701 sr_cat_instance_id := get_inst_id_from_category_key(cat_id);
2702 sr_cat_id := get_sr_cat_id_from_cat_key(cat_id);
2703 END IF;
2704
2705 parse_planlist(plan_id, l_planlist);
2706 IF l_planlist.COUNT = 0 THEN
2707 query_id := -1;
2708 RETURN;
2709 END IF;
2710
2711 FOR l_index IN l_planlist.FIRST..l_planlist.LAST LOOP
2712 IF validate_plan(l_planlist(l_index)) = FALSE THEN
2713 query_id := -1;
2714 RETURN;
2715 END IF;
2716
2717 schedule_create_weeks(weekschedule, l_planlist(l_index));
2718 schedule_create_periods(periodschedule, l_planlist(l_index));
2719
2720 schedule_bucket_safety_stock(weekschedule, periodschedule, l_planlist(l_index),
2721 sr_instance_id, sr_tp_id, sr_cat_instance_id, sr_cat_id, item_id, query_Id);
2722 END LOOP;
2723 END schedule_details_ss;
2724
2725 PROCEDURE schedule_details_sl(p_query_id OUT NOCOPY NUMBER, p_period_type NUMBER, plan_id IN VARCHAR2,
2726 org_id IN VARCHAR2, cat_id IN VARCHAR2, p_item_id IN NUMBER, customer_id IN NUMBER,
2727 customer_class_code IN VARCHAR2, p_abc_id IN VARCHAR2)
2728 IS
2729 weekschedule Schedule;
2730 periodschedule Schedule;
2731 l_sr_instance_id NUMBER;
2732 l_sr_tp_id NUMBER;
2733 l_sr_cat_instance_id NUMBER;
2734 l_sr_cat_id NUMBER;
2735 l_planlist PlanList;
2736 l_index BINARY_INTEGER;
2737 l_default_category_set_id NUMBER;
2738
2739 BEGIN
2740
2741 -- Get default category set
2742 l_default_category_set_id := get_cat_set_id(plan_id);
2743
2744 SELECT msc_form_query_s.nextval
2745 INTO p_query_id
2746 FROM dual;
2747
2748 IF org_id IS NOT NULL THEN
2749 l_sr_instance_id := get_inst_id_from_org_key(org_id);
2750 l_sr_tp_id := get_sr_tp_id_from_org_key(org_id);
2751 END IF;
2752
2753 IF cat_id IS NOT NULL THEN
2754 l_sr_cat_instance_id := get_inst_id_from_category_key(cat_id);
2755 l_sr_cat_id := get_sr_cat_id_from_cat_key(cat_id);
2756 END IF;
2757
2758 parse_planlist(plan_id, l_planlist);
2759 IF l_planlist.COUNT = 0 THEN
2760 p_query_id := -1;
2761 RETURN;
2762 END IF;
2763
2764 FOR l_index IN l_planlist.FIRST..l_planlist.LAST LOOP
2765 IF validate_plan(l_planlist(l_index)) = FALSE THEN
2766 p_query_id := -1;
2767 RETURN;
2768 END IF;
2769
2770 IF p_period_type = CALENDAR_TYPE_MFG THEN
2771
2772 schedule_create_weeks(weekschedule, l_planlist(l_index));
2773 schedule_create_periods(periodschedule, l_planlist(l_index));
2774
2775 schedule_bucket_service_level(weekschedule, periodschedule, l_planlist(l_index),
2776 l_sr_instance_id, l_sr_tp_id,
2777 l_sr_cat_instance_id, l_sr_cat_id, p_item_id, customer_id, customer_class_code,p_abc_id,p_query_Id);
2778 ELSIF p_period_type = CALENDAR_TYPE_BIS THEN
2779 -- BIS implies Inventory Budget report: No items, no customer/customer class
2780 INSERT INTO msc_form_query
2781 (
2782 query_id,
2783 NUMBER1, -- PLAN_ID
2784 CREATED_BY, -- RECORD_TYPE
2785 NUMBER9, -- achieved_ss_qty
2786 NUMBER10,
2787 NUMBER11,
2788 NUMBER12,
2789 NUMBER13,
2790 NUMBER14,
2791 NUMBER15,
2792 NUMBER16,
2793 PROGRAM_ID,
2794 PROGRAM_APPLICATION_ID, -- achieved_sl --_qty2
2795 LAST_UPDATE_LOGIN, -- target_sl
2796 NUMBER6, -- inventory_value_dollars
2797 NUMBER7, -- period_type
2798 DATE1, -- week_start_date
2799 DATE3, -- period_start_date
2800 NUMBER2, -- sr_instance_id
2801 NUMBER3, -- org_id
2802 REQUEST_ID, -- sr_cat_instance_id
2803 NUMBER5, -- sr_category_id
2804 CHAR1,
2805 LAST_UPDATE_DATE,
2806 LAST_UPDATED_BY,
2807 CREATION_DATE
2808 )
2809 SELECT
2810 p_query_id,
2811 msd.plan_id,
2812 RECORD_SERVICE_LEVEL AS record_type,
2813 NULL AS achieved_ss_qty,
2814 NULL AS achieved_ss_dollars,
2815 NULL AS achieved_ss_days,
2816 NULL AS target_ss_qty,
2817 NULL AS target_ss_dollars,
2818 NULL AS target_ss_days,
2819 NULL AS userdef_ss_qty,
2820 NULL AS userdef_ss_dollars,
2821 NULL AS userdef_ss_days,
2822 sum(nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1)) * 100 /
2823 sum(nvl(msd.using_requirement_quantity, 0) * nvl(msd.probability,1)) AS achieved_sl,
2824 decode(count(*), 0, 50, sum(nvl(msd.service_level, 50)) / COUNT(*)) AS target_service_level,
2825 NULL AS inventory_value_dollars,
2826 CALENDAR_TYPE_BIS AS period_type,
2827 NULL AS week_start_date,
2828 msbp.START_DATE AS period_start_date,
2829 msd.sr_instance_id AS instance_id,
2830 msd.organization_id AS org_id,
2831 mic.sr_instance_id AS sr_cat_instance_id,
2832 mic.sr_category_id AS sr_category_id,
2833 mic.category_name AS category_name,
2834 SYSDATE,
2835 -1,
2836 SYSDATE
2837 FROM msc_plans mp, msc_plan_organizations mpo, msc_demands msd, msc_system_items msi,
2838 msc_item_categories mic, msc_bis_periods msbp
2839 WHERE mp.plan_id = l_planlist(l_index)
2840 AND mp.plan_id = mpo.plan_id
2841 AND msd.organization_id = mpo.organization_id
2842 AND msd.sr_instance_id = mpo.sr_instance_id
2843 AND msd.plan_id = mpo.plan_id
2844 AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
2845 AND msd.plan_id = msi.plan_id
2846 AND msd.sr_instance_id = msi.sr_instance_id
2847 AND msd.organization_id = msi.organization_id
2848 AND msd.inventory_item_id = msi.inventory_item_id
2849 AND nvl(msi.abc_class_name, 'XXXXX') = nvl(p_abc_id, nvl(msi.abc_class_name, 'XXXXX'))
2850 AND msi.inventory_item_id = NVL(p_item_id, msi.inventory_item_id)
2851 AND msi.organization_id = mic.organization_id
2852 AND msi.sr_instance_id = mic.sr_instance_id
2853 AND msi.inventory_item_id = mic.inventory_item_id
2854 AND mic.category_set_id = l_default_category_set_id
2855 AND mpo.sr_instance_id = nvl(l_sr_instance_id, mpo.sr_instance_id)
2856 AND mpo.organization_id = nvl(l_sr_tp_id, mpo.organization_id)
2857 AND mic.sr_instance_id = nvl(l_sr_cat_instance_id, mic.sr_instance_id)
2858 AND mic.sr_category_id = nvl(l_sr_cat_id, mic.sr_category_id)
2859 AND mp.sr_instance_id = msbp.sr_instance_id
2860 AND mp.organization_id = msbp.organization_id
2861 AND msbp.period_set_name = g_period_setname
2862 AND TRUNC(msd.using_assembly_demand_date) BETWEEN msbp.START_DATE AND msbp.end_date
2863 GROUP BY msd.plan_id, msbp.start_date,
2864 CUBE (msd.sr_instance_id, msd.organization_id, mic.sr_instance_id, mic.sr_category_id, mic.category_name)
2865 HAVING (
2866 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
2867 GROUPING (msd.sr_instance_id) = 1 AND GROUPING (msd.organization_id) = 1 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0 OR
2868 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 1 AND GROUPING(mic.sr_category_id) = 1 AND GROUPING(mic.category_name) = 1 OR
2869 GROUPING (msd.sr_instance_id) = 0 AND GROUPING (msd.organization_id) = 0 AND GROUPING(mic.sr_instance_id) = 0 AND GROUPING(mic.sr_category_id) = 0 AND GROUPING(mic.category_name) = 0
2870 );
2871 END IF;
2872 END LOOP;
2873
2874 END schedule_details_sl;
2875
2876 function manage_partitions(p_plan_id number, p_part_mode number) return number is
2877 l_partitioned_table varchar2(100);
2878 l_partition_name varchar2(300);
2879 sql_stmt varchar2(300);
2880 dummy1 varchar2(50);
2881 dummy2 varchar2(50);
2882 l_applsys_schema varchar2(100);
2883 lv_msc_schema varchar2(32);
2884 errbuf varchar2(2000);
2885 retcode number := 0;
2886 lv_retval boolean;
2887
2888 cursor c_check is
2889 select count(*)
2890 from all_tab_partitions
2891 where TABLE_NAME = 'MSC_ANALYSIS_AGGREGATE'
2892 and table_owner = lv_msc_schema
2893 and partition_name = l_partition_name;
2894 l_temp number;
2895
2896 begin
2897 IF (fND_INSTALLATION.GET_APP_INFO('FND',dummy1,dummy2,l_applsys_schema) = FALSE) then
2898 retcode := FND_API.G_RET_STS_ERROR;
2899 fnd_message.set_name('MSC','MSC_PART_UNDEFINED_SCHEMA');
2900 errbuf := fnd_message.get;
2901 put_line(errbuf);
2902 return retcode;
2903 end if;
2904
2905 lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'MSC', dummy1, dummy2, lv_msc_schema);
2906
2907 l_partitioned_table :='MSC_ANALYSIS_AGGREGATE';
2908 l_partition_name := substr(l_partitioned_table,5)||'_'||to_char(p_plan_id);
2909
2910 open c_check;
2911 fetch c_check into l_temp;
2912 close c_check;
2913
2914 if (p_part_mode = 1) then
2915 -- create partitions
2916 sql_stmt := 'alter table ' || l_partitioned_table || ' add partition '
2917 || l_partition_name || ' values less than ('|| to_char(p_plan_id+1) || ')';
2918 if (l_temp = 0) then
2919 put_line(sql_stmt);
2920 ad_ddl.do_ddl(l_applsys_schema,'MSC', ad_ddl.alter_table,sql_stmt,l_partitioned_table);
2921 end if;
2922 elsif (p_part_mode = 2) then
2923 --drop partitions
2924 sql_stmt := 'alter table ' || l_partitioned_table || ' drop partition '|| l_partition_name ;
2925 put_line(sql_stmt);
2926 ad_ddl.do_ddl(l_applsys_schema,'MSC', ad_ddl.alter_table,sql_stmt,l_partitioned_table);
2927 end if;
2928
2929
2930 return retcode;
2931 exception
2932 when others then
2933 retcode := 1;
2934 errbuf := 'Error in manage_partitions api '||SQLCODE||' -ERROR- '||SQLERRM;
2935 put_line(errbuf);
2936 return retcode;
2937 end manage_partitions;
2938
2939
2940 PROCEDURE schedule_aggregate(p_plan_id IN NUMBER)
2941 IS
2942 weekschedule Schedule;
2943 periodschedule Schedule; l_part_retval NUMBER;
2944 l_temp number;
2945 lv_share_partition varchar2(2);
2946 perf_profile varchar2(2);
2947
2948
2949 BEGIN
2950 IF validate_plan(p_plan_id) = FALSE THEN
2951 RETURN;
2952 END IF;
2953
2954 g_user_id := fnd_global.user_id;
2955
2956 g_period_setname := fnd_profile.value('MSC_IO_UI_PERSETNAME');
2957 --if profile not set default to Accounting as before
2958 if(g_period_setname is null) then
2959 g_period_setname := 'Accounting';
2960 end if;
2961
2962 perf_profile := fnd_profile.value('MSC_IO_UI_PERF_TUNE');
2963 if(perf_profile is null OR perf_profile ='N') then
2964 g_perf_prof_on := false;
2965 else
2966 g_perf_prof_on := true;
2967 end if;
2968
2969 select count(*) into l_temp from sys.all_tab_partitions where table_name = 'MSC_ANALYSIS_AGGREGATE'
2970 AND PARTITION_NAME = 'ANALYSIS_AGGREGATE_'||P_PLAN_ID;
2971 lv_share_partition := fnd_profile.value('MSC_SHARE_PARTITIONS');
2972
2973 if (l_temp = 0 and lv_share_partition <> 'Y')then --create partition
2974 l_part_retval := manage_partitions(p_plan_id, 1);
2975 if (l_part_retval <> 0) then
2976 put_line('Error while creating partitions ');
2977 end if;
2978 put_line('partition created '|| l_part_retval);
2979 --dbms_output.put_line('partition created '|| l_part_retval);
2980 else
2981 DELETE FROM msc_analysis_aggregate WHERE plan_id = p_plan_id;
2982 end if;
2983
2984 COMMIT;
2985
2986
2987 schedule_aggregate_sl(p_plan_id);
2988 COMMIT;
2989 --dbms_output.put_line('schedule aggregate sl');
2990
2991 schedule_aggregate_iv(p_plan_id);
2992 COMMIT;
2993
2994 schedule_aggregate_sl_brkdown(p_plan_id);
2995 COMMIT;
2996 --dbms_output.put_line('schedule aggregate sl brkdown');
2997
2998 schedule_create_weeks(weekschedule, p_plan_id);
2999 schedule_create_periods(periodschedule, p_plan_id);
3000 schedule_aggregate_ss(weekschedule, periodschedule, p_plan_id);
3001 --dbms_output.put_line('schedule aggregate ss');
3002 COMMIT;
3003
3004 schedule_aggregate_cost(p_plan_id);
3005 COMMIT;
3006 END schedule_aggregate;
3007
3008 END MSC_ANALYSIS_SAFETY_STOCK_PERF;