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