[Home] [Help]
PACKAGE BODY: APPS.MSC_ANALYSIS_SAFETY_STOCK
Source
1 PACKAGE BODY MSC_ANALYSIS_SAFETY_STOCK AS
2 /* $Header: MSCASSB.pls 120.0 2005/05/25 17:33:45 appldev noship $ */
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_TARGET_SL_VIEWBY_ITEM CONSTANT NUMBER := 2;
12 RECORD_TARGET_SL_VIEWBY_CUST CONSTANT NUMBER := 3;
13
14 UNDEFINED_CUSTOMER_CODE CONSTANT VARCHAR2(30) := '_MISC'; -- Used in Java
15 UNDEFINED_CUSTOMER_ID CONSTANT NUMBER := -1;
16
17 do_debug BOOLEAN := FALSE;
18
19 TYPE Bucket IS RECORD
20 (
21 -- item key
22 plan_id NUMBER := 0,
23 instance_id NUMBER := 0,
24 org_id NUMBER := 0,
25 item_id NUMBER := 0,
26 sr_category_id NUMBER := 0,
27 category_name VARCHAR2(255) := NULL,
28 plan_name VARCHAR2(255) := NULL,
29 org_name VARCHAR2(255) := NULL,
30 item_name VARCHAR2(255) := NULL,
31 -- bucket data
32 bkt_start_date DATE := NULL,
33 bkt_end_date DATE := NULL,
34 week_nr NUMBER := 0,
35 period_start_date DATE := NULL,
36 period_nr NUMBER := 0,
37 bucket_type NUMBER := 0,
38 last_week_of_period VARCHAR2(1) := 'N',
39 -- budget measures
40 achieved_ss_qty NUMBER := 0,
41 achieved_ss_dollars NUMBER := 0,
42 achieved_ss_days NUMBER := 0,
43 target_ss_qty NUMBER := 0,
44 target_ss_dollars NUMBER := 0,
45 target_ss_days NUMBER := 0,
46 userdef_ss_qty NUMBER := 0,
47 userdef_ss_dollars NUMBER := 0,
48 userdef_ss_days NUMBER := 0,
49 num_safety_stock NUMBER := 0,
50 -- service level measures
51 delivered_quantity NUMBER := 0, -- Achieved Service Level = delivered / required
52 required_quantity NUMBER := 0, -- These are kept separate for UI aggregation
53 target_service_level NUMBER := 0,
54 num_target_service_level NUMBER := 0, -- weight for UI aggregation
55 partner_id NUMBER := NULL,
56 partner_name VARCHAR2(255) := NULL,
57 customer_class_code VARCHAR2(30) := NULL,
58 -- service level or safety stock record
59 record_type NUMBER := NULL
60 );
61
62 TYPE Schedule IS TABLE OF Bucket INDEX BY BINARY_INTEGER;
63 TYPE PlanList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
64
65 PROCEDURE parse_planlist(p_plans VARCHAR2, p_planlist IN OUT NOCOPY PlanList)
66 IS
67 occurrence NUMBER := 1;
68 stringstart NUMBER := 1;
69 stringend NUMBER := 1;
70 planfound NUMBER := 1;
71 pos NUMBER;
72 token NUMBER;
73 BEGIN
74
75 LOOP
76 pos := INSTR(p_plans, ',', 1, occurrence);
77 occurrence := occurrence + 1;
78 IF pos = 0 THEN
79 stringend := LENGTH(p_plans);
80 token := to_number(SUBSTR(p_plans, stringstart, stringend - stringstart + 1));
81 IF token IS NOT NULL THEN
82 p_planlist(planfound) := token;
83 planfound := planfound + 1;
84 END IF;
85 EXIT;
86 END IF;
87 stringend := pos - 1;
88 token := to_number(SUBSTR(p_plans, stringstart, stringend - stringstart + 1));
89 IF token IS NOT NULL THEN
90 p_planlist(planfound) := token;
91 planfound := planfound + 1;
92 END IF;
93 stringstart := stringend + 2;
94 END LOOP;
95
96 END;
97
98 FUNCTION get_customer_target_sl(customer_id NUMBER)
99 RETURN NUMBER
100 IS
101 l_target_service_level NUMBER;
102 BEGIN
103 SELECT service_level
104 INTO l_target_service_level
105 FROM msc_trading_partners
106 WHERE partner_type = 2
107 AND partner_id = customer_id;
108 RETURN l_target_service_level;
109 EXCEPTION WHEN OTHERS THEN RETURN NULL;
110 END;
111
112 FUNCTION get_cat_set_id(arg_plan_id number) RETURN NUMBER is
113 l_cat_set_id number;
114 l_def_pref_id number;
115 l_plan_type number;
116 cursor plan_type_c(v_plan_id number) is
117 select curr_plan_type
118 from msc_plans
119 where plan_id = v_plan_id;
120 BEGIN
121 open plan_type_c(arg_plan_id);
122 fetch plan_type_c into l_plan_type;
123 close plan_type_c;
124
125 l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
126 l_cat_set_id:= msc_get_name.GET_preference('CATEGORY_SET_ID',l_def_pref_id, l_plan_type);
127 return l_cat_set_id;
128 END get_cat_set_id;
129 PROCEDURE schedule_create(mainschedule IN OUT NOCOPY Schedule, p_plan_id NUMBER)
130 IS
131
132 CURSOR c1(p_plan_id NUMBER) IS
133 SELECT mp.plan_id, mp.compile_designator, mtp.calendar_code, mpb.bkt_start_date, mpb.bkt_end_date,
134 mpsd.period_start_date, mpb.bucket_type
135 FROM msc_plans mp, msc_trading_partners mtp, msc_plan_buckets mpb, msc_period_start_dates mpsd
136 WHERE mp.plan_id = p_plan_id
137 AND mtp.sr_tp_id = mp.organization_id
138 AND mtp.sr_instance_id = mp.sr_instance_id
139 AND mtp.partner_type = 3
140 AND mp.plan_id = mpb.plan_id
141 AND mp.sr_instance_id = mpb.sr_instance_id
142 AND mp.organization_id = mpb.organization_id
143 AND mtp.sr_Instance_id = mpsd.sr_instance_id
144 AND mtp.calendar_code = mpsd.calendar_code
145 AND mpb.bucket_type in (2,3)
146 AND mpb.sr_instance_id = mpsd.sr_instance_id
147 AND mpsd.exception_set_id = mtp.calendar_exception_set_id
148 AND mpsd.calendar_code = mtp.calendar_code
149 AND (mpb.bkt_start_date >= mpsd.period_start_date AND mpb.bkt_start_date < mpsd.next_date)
150 ORDER BY mpb.bkt_start_date;
151
152 currentBucket Bucket;
153 currentPosition BINARY_INTEGER := 1;
154 c1Rec c1%ROWTYPE;
155 previous_period_start_date DATE := NULL;
156 current_week_nr NUMBER := 1;
157 current_period_nr NUMBER := 1;
158
159 BEGIN
160
161 OPEN c1(p_plan_id);
162 LOOP
163 FETCH c1 INTO c1Rec;
164 IF c1%NOTFOUND THEN
165 IF current_week_nr > 1 THEN
166 mainschedule(current_week_nr - 1).last_week_of_period := 'Y';
167 END IF;
168 EXIT;
169 END IF;
170
171 currentBucket.bkt_start_date := c1Rec.bkt_start_date;
172 currentBucket.bkt_end_date := c1Rec.bkt_end_date;
173 currentBucket.period_start_date := c1Rec.period_start_date;
174 currentBucket.week_nr := current_week_nr;
175 currentBucket.bucket_type := c1Rec.bucket_type;
176 IF currentBucket.period_start_date <> previous_period_start_date THEN
177 current_period_nr := current_period_nr + 1;
178 -- Need the last week of the period for collapsing ss across weeks
179 IF current_week_nr > 1 THEN
180 mainschedule(current_week_nr - 1).last_week_of_period := 'Y';
181 END IF;
182 END IF;
183 currentBucket.period_nr := current_period_nr;
184
185 mainschedule(current_week_nr) := currentBucket;
186
187 previous_period_start_date := currentBucket.period_start_date;
188 current_week_nr := current_week_nr + 1;
189 END LOOP;
190 CLOSE c1;
191
192 END;
193
194 PROCEDURE schedule_dump_header
195 IS
196 BEGIN
197 IF do_debug = FALSE THEN
198 RETURN;
199 END IF;
200 dbms_output.put('plan_id,inst_id,org_id,item_id,cat_id,record,lwop,start,end,period_start,period_nr,weeknr,achieved_ss,');
201 dbms_output.put('achieved_ss_dl,target_ss,target_ss_dl,target_ss_ds,userdef_ss,userdef_ss_dl,userdef_ss_ds,num_ss,delivered,');
202 dbms_output.put_line('required,target_sl,num_target_sl,customer_id');
203 END;
204
205 PROCEDURE schedule_dump_record(aRecord BUCKET)
206 IS
207 BEGIN
208
209 IF do_debug = FALSE THEN
210 RETURN;
211 END IF;
212 dbms_output.put('"'||aRecord.plan_id || '","' || aRecord.instance_id || '",');
213 dbms_output.put('"'|| aRecord.org_id || '","' || aRecord.item_id || '",');
214 dbms_output.put('"' || aRecord.sr_category_id || '",');
215 IF aRecord.record_type = RECORD_SERVICE_LEVEL THEN
216 DBMS_OUTPUT.put('"SL",');
217 ELSIF aRecord.record_type = RECORD_SAFETY_STOCK THEN
218 DBMS_OUTPUT.put('"SS",');
219 ELSIF aRecord.record_type = RECORD_TARGET_SL_VIEWBY_ITEM THEN
220 DBMS_OUTPUT.put('"TLI",');
221 ELSIF aRecord.record_type = RECORD_TARGET_SL_VIEWBY_CUST THEN
222 DBMS_OUTPUT.put('"TLC",');
223 END IF;
224 dbms_output.put('"' || aRecord.last_week_of_period || '",');
225 dbms_output.put('"' || aRecord.bkt_start_date || '","' || aRecord.bkt_end_date || '",');
226 dbms_output.put('"' || aRecord.period_start_date || '",');
227 dbms_output.put('"' || aRecord.week_nr || '","' || aRecord.period_nr || '",');
228 dbms_output.put('"' || aRecord.achieved_ss_qty || '",');
229 dbms_output.put('"' || aRecord.achieved_ss_dollars || '",');
230 dbms_output.put('"' || aRecord.target_ss_qty || '",');
231 dbms_output.put('"' || aRecord.target_ss_dollars || '",');
232 dbms_output.put('"' || aRecord.target_ss_days || '",');
233 dbms_output.put('"' || aRecord.userdef_ss_qty || '",');
234 dbms_output.put('"' || aRecord.userdef_ss_dollars || '",');
235 dbms_output.put('"' || aRecord.userdef_ss_days || '",');
236 dbms_output.put('"' || aRecord.num_safety_stock || '",');
237 dbms_output.put('"' || aRecord.delivered_quantity || '",');
238 dbms_output.put('"' || aRecord.required_quantity || '",');
239 dbms_output.put('"' || aRecord.target_service_level || '",');
240 dbms_output.put('"' || aRecord.num_target_service_level || '",');
241 dbms_output.put_line('"' || aRecord.partner_id || '"');
242 END;
243
244 PROCEDURE schedule_initialize(mainschedule IN OUT NOCOPY Schedule, aRecord Bucket)
245 IS
246 l_target_service_level NUMBER;
247 BEGIN
248
249 FOR i IN mainschedule.FIRST..mainschedule.LAST LOOP
250 mainschedule(i).achieved_ss_qty := 0;
251 mainschedule(i).achieved_ss_dollars := 0;
252 mainschedule(i).achieved_ss_days := 0;
253 mainschedule(i).target_ss_qty := 0;
254 mainschedule(i).target_ss_dollars := 0;
255 mainschedule(i).target_ss_days := 0;
256 mainschedule(i).userdef_ss_qty := 0;
257 mainschedule(i).userdef_ss_dollars := 0;
258 mainschedule(i).userdef_ss_days := 0;
259 mainschedule(i).delivered_quantity := 0;
260 mainschedule(i).required_quantity := 0;
261 mainschedule(i).target_service_level := 0;
262 mainschedule(i).num_target_service_level := 0;
263 mainschedule(i).num_safety_stock := 0;
264
265 mainschedule(i).target_service_level := NULL;
266 mainschedule(i).num_target_service_level := 1;
267
268 mainschedule(i).plan_id := aRecord.plan_id;
269 mainschedule(i).plan_name := aRecord.plan_name;
270 mainschedule(i).instance_id := aRecord.instance_id;
271 mainschedule(i).org_id := aRecord.org_id;
272 mainschedule(i).org_name := aRecord.org_name;
273 mainschedule(i).sr_category_id := aRecord.sr_category_id;
274 mainschedule(i).category_name := aRecord.category_name;
275 mainschedule(i).item_id := aRecord.item_id;
276 mainschedule(i).item_name := aRecord.item_name;
277 mainschedule(i).partner_id := nvl(aRecord.partner_id, UNDEFINED_CUSTOMER_ID);
278 mainschedule(i).partner_name := aRecord.partner_name;
279 mainschedule(i).record_type := aRecord.record_type;
280 mainschedule(i).sr_category_id := aRecord.sr_category_id;
281 mainschedule(i).category_name := aRecord.category_name;
282 mainschedule(i).customer_class_code := nvl(aRecord.customer_class_code, UNDEFINED_CUSTOMER_CODE);
283 END LOOP;
284
285 END;
286
287 PROCEDURE schedule_ss_fill_gaps(mainschedule IN OUT NOCOPY Schedule)
288 IS
289 l_prev_userdef_ss_qty NUMBER;
290 l_prev_userdef_ss_days NUMBER;
291 l_prev_userdef_ss_dollars NUMBER;
292 l_prev_achieved_ss_qty NUMBER;
293 l_prev_achieved_ss_days NUMBER;
294 l_prev_achieved_ss_dollars NUMBER;
295 l_prev_target_ss_qty NUMBER;
296 l_prev_target_ss_days NUMBER;
297 l_prev_target_ss_dollars NUMBER;
298 l_prev_num_safety_stock NUMBER := NULL;
299
300 BEGIN
301 FOR currentIndex IN mainschedule.FIRST..mainschedule.LAST LOOP
302 IF mainschedule(currentIndex).num_safety_stock IS NULL AND l_prev_num_safety_stock IS NOT NULL THEN
303 mainschedule(currentIndex).userdef_ss_qty := l_prev_userdef_ss_qty;
304 mainschedule(currentIndex).userdef_ss_days := l_prev_userdef_ss_days;
305 mainschedule(currentIndex).userdef_ss_dollars := l_prev_userdef_ss_dollars;
306 mainschedule(currentIndex).achieved_ss_qty := l_prev_achieved_ss_qty;
307 mainschedule(currentIndex).achieved_ss_days := l_prev_achieved_ss_days;
308 mainschedule(currentIndex).achieved_ss_dollars := l_prev_achieved_ss_dollars;
309 mainschedule(currentIndex).target_ss_qty := l_prev_target_ss_qty;
310 mainschedule(currentIndex).target_ss_days := l_prev_target_ss_days;
311 mainschedule(currentIndex).target_ss_dollars := l_prev_target_ss_dollars;
312 mainschedule(currentIndex).num_safety_stock := l_prev_num_safety_stock;
313 END IF;
314 l_prev_userdef_ss_qty := mainschedule(currentIndex).userdef_ss_qty;
315 l_prev_userdef_ss_days := mainschedule(currentIndex).userdef_ss_days;
316 l_prev_userdef_ss_dollars := mainschedule(currentIndex).userdef_ss_dollars;
317 l_prev_achieved_ss_qty := mainschedule(currentIndex).achieved_ss_qty;
318 l_prev_achieved_ss_days := mainschedule(currentIndex).achieved_ss_days;
319 l_prev_achieved_ss_dollars := mainschedule(currentIndex).achieved_ss_dollars;
320 l_prev_target_ss_qty := mainschedule(currentIndex).target_ss_qty;
321 l_prev_target_ss_days := mainschedule(currentIndex).target_ss_days;
322 l_prev_target_ss_dollars := mainschedule(currentIndex).target_ss_dollars;
323 l_prev_num_safety_stock := mainschedule(currentIndex).num_safety_stock;
324 END LOOP;
325 END schedule_ss_fill_gaps;
326
327 PROCEDURE schedule_output_record(query_id NUMBER, aRecord Bucket)
328 IS
329 BEGIN
330 INSERT INTO msc_form_query
331 (query_id, last_update_date, creation_date, created_by,
332 NUMBER1,NUMBER2,NUMBER3,
333 NUMBER4,NUMBER5,CHAR1,
334 CHAR2, CHAR3, CHAR4,
335 DATE1,DATE2,NUMBER6,
336 DATE3,NUMBER7,NUMBER8,
337 NUMBER9, NUMBER10, NUMBER11,
338 NUMBER12,NUMBER13,NUMBER14,
339 NUMBER15,NUMBER16,PROGRAM_ID,
340 PROGRAM_APPLICATION_ID, REQUEST_ID,
341 LAST_UPDATE_LOGIN, LAST_UPDATED_BY,
342 CHAR7, CHAR5,
343 CHAR6, CHAR8,
344 CHAR9)
345 VALUES
346 (query_id, SYSDATE, SYSDATE, aRecord.record_type,
347 aRecord.plan_id, aRecord.instance_id, aRecord.org_id,
348 aRecord.item_id, aRecord.sr_category_id, aRecord.category_name,
349 aRecord.plan_name, aRecord.org_name, aRecord.item_name,
350 aRecord.bkt_start_date,aRecord.bkt_end_date,aRecord.week_nr,
351 aRecord.period_start_date,aRecord.period_nr,aRecord.bucket_type,
355 aRecord.delivered_quantity,aRecord.required_quantity,
352 aRecord.achieved_ss_qty,aRecord.achieved_ss_dollars,aRecord.achieved_ss_days,
353 aRecord.target_ss_qty,aRecord.target_ss_dollars,aRecord.target_ss_days,
354 aRecord.userdef_ss_qty,aRecord.userdef_ss_dollars,aRecord.userdef_ss_days,
356 aRecord.target_service_level,aRecord.num_target_service_level,
357 aRecord.partner_id, aRecord.partner_name,
358 aRecord.customer_class_code, aRecord.num_safety_stock,
359 aRecord.last_week_of_period);
360
361 END;
362
363 PROCEDURE schedule_flush(mainschedule Schedule, query_id NUMBER)
364 IS
365 BEGIN
366
367 schedule_dump_header;
368 FOR i IN mainschedule.FIRST..mainschedule.LAST LOOP
369 schedule_output_record(query_id, mainschedule(i));
370 schedule_dump_record(mainschedule(i));
371 END LOOP;
372 END;
373
374 FUNCTION is_bucket_match(aBucket Bucket, aRecord Bucket)
375 RETURN BOOLEAN
376 IS
377 BEGIN
378 IF aRecord.bkt_start_date >= aBucket.bkt_start_date AND
379 aRecord.bkt_start_date <= aBucket.bkt_end_date
380 THEN
381 RETURN TRUE;
382 END IF;
383
384 RETURN FALSE;
385 END;
386
387 PROCEDURE bucket_service_level_record(mainschedule IN OUT NOCOPY Schedule, aRecord Bucket, end_date DATE)
388 IS
389 BEGIN
390
391 FOR i IN mainschedule.FIRST..mainschedule.LAST LOOP
392 IF is_bucket_match(mainschedule(i), aRecord) THEN
393 mainschedule(i).delivered_quantity := mainschedule(i).delivered_quantity + aRecord.delivered_quantity;
394 mainschedule(i).required_quantity := mainschedule(i).required_quantity + aRecord.required_quantity;
395 END IF;
396 END LOOP;
397
398 END;
399
400 PROCEDURE bucket_safety_stock_record(mainschedule IN OUT NOCOPY Schedule, aRecord Bucket, end_date DATE)
401 IS
402 BEGIN
403
404 FOR i IN mainschedule.FIRST..mainschedule.LAST LOOP
405 IF is_bucket_match(mainschedule(i), aRecord) THEN
406 IF aRecord.userdef_ss_qty IS NOT NULL OR aRecord.userdef_ss_days IS NOT NULL THEN
407 mainschedule(i).userdef_ss_qty := aRecord.userdef_ss_qty;
408 mainschedule(i).userdef_ss_days := aRecord.userdef_ss_days;
409 mainschedule(i).userdef_ss_dollars := aRecord.userdef_ss_dollars;
410 END IF;
411
412 IF aRecord.achieved_ss_qty IS NOT NULL THEN
413 mainschedule(i).achieved_ss_qty := aRecord.achieved_ss_qty;
414 mainschedule(i).achieved_ss_days := aRecord.achieved_ss_days;
415 mainschedule(i).achieved_ss_dollars := aRecord.achieved_ss_dollars;
416 END IF;
417
418 IF aRecord.target_ss_qty IS NOT NULL THEN
419 mainschedule(i).target_ss_qty := aRecord.target_ss_qty;
420 mainschedule(i).target_ss_days := aRecord.target_ss_days;
421 mainschedule(i).target_ss_dollars := aRecord.target_ss_dollars;
422 END IF;
423
424 mainschedule(i).num_safety_stock := 1; -- Weight for DOS
425 END IF;
426 END LOOP;
427 END;
428
429 FUNCTION is_service_level_key_changed(currRecord IN Bucket, prevRecord IN Bucket)
430 RETURN BOOLEAN
431 IS
432 BEGIN
433 IF currRecord.plan_id <> prevRecord.plan_id OR
434 currRecord.org_id <> prevRecord.org_id OR
435 currRecord.instance_id <> prevRecord.instance_id OR
436 currRecord.item_id <> prevRecord.item_id OR
437 nvl(currRecord.partner_id, -1) <> nvl(prevRecord.partner_id, -1)
438 THEN
439 RETURN TRUE;
440 ELSE
441 RETURN FALSE;
442 END IF;
443 END;
444
445 FUNCTION is_safety_stock_key_changed(currRecord IN Bucket, prevRecord IN Bucket)
446 RETURN BOOLEAN
447 IS
448 BEGIN
449 IF currRecord.plan_id <> prevRecord.plan_id OR
450 currRecord.org_id <> prevRecord.org_id OR
451 currRecord.instance_id <> prevRecord.instance_id OR
452 currRecord.item_id <> prevRecord.item_id
453 THEN
454 RETURN TRUE;
455 ELSE
456 RETURN FALSE;
457 END IF;
458 END;
459
460 PROCEDURE schedule_target_service_level(
461 mainschedule IN OUT NOCOPY Schedule, p_plan_id NUMBER, p_sr_instance_id NUMBER,
462 p_sr_tp_id NUMBER, p_sr_cat_instance_id NUMBER, p_sr_cat_id NUMBER,
463 p_item_id NUMBER, p_partner_id NUMBER, p_customer_class_code VARCHAR2,
464 query_id NUMBER)
465 IS
466 l_default_category_set_id NUMBER;
467
468 CURSOR c1 IS
469 SELECT plan_id, instance_id, org_id,
470 item_id,
471 sr_category_id, category_name,
472 plan_name,
473 org_name,
474 item_name,
475 avg(nvl(get_customer_target_sl(partner_id) ,
476 msc_get_bis_values.service_target(plan_id,instance_id,org_id,item_id))) AS target_sl
477 FROM
478 (
479 SELECT DISTINCT msi.plan_id AS plan_id, msi.sr_instance_id AS instance_id, msi.organization_id AS org_id,
480 msi.inventory_item_id AS item_id,
481 mic.sr_category_id AS sr_category_id, mic.category_name AS category_name,
482 mp.compile_designator AS plan_name,
486 msc_safety_stocks mss,
483 mtp.organization_code AS org_name,
484 msi.item_name AS item_name, cust.partner_id
485 FROM msc_plans mp, msc_system_items msi, msc_item_categories mic, msc_trading_partners mtp, msc_trading_partners cust,
487 (SELECT demands.plan_id, demands.sr_instance_id, demands.organization_id, demands.using_assembly_item_id,
488 demands.customer_id, demands.demand_id
489 FROM msc_demands demands
490 WHERE demands.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)) msd
491 WHERE mp.plan_id = p_plan_id
492 AND mp.plan_id = msi.plan_id
493 AND msi.organization_id = mic.organization_id
494 AND msi.sr_instance_id = mic.sr_instance_id
495 AND msi.inventory_item_id = mic.inventory_item_id
496 AND mic.category_set_id = l_default_category_set_id
497 AND mic.sr_instance_id = nvl(p_sr_cat_instance_id, mic.sr_instance_id)
498 AND mic.sr_category_id = nvl(p_sr_cat_id, mic.sr_category_id)
499 AND msi.organization_id = mtp.sr_tp_id
500 AND msi.inventory_item_id = NVL(p_item_id, msi.inventory_item_id)
501 AND msi.sr_instance_id = mtp.sr_instance_id
502 AND mtp.partner_type = 3
503 AND mtp.sr_instance_id = nvl(p_sr_instance_id, mtp.sr_instance_id)
504 AND mtp.sr_tp_id = nvl(p_sr_tp_id, mtp.sr_tp_id)
505 AND msi.sr_instance_id = mss.sr_instance_id (+)
506 AND msi.plan_id = mss.plan_id (+)
507 AND msi.organization_id = mss.organization_id (+)
508 AND msi.inventory_item_id = mss.inventory_item_id (+)
509 AND msi.plan_id = msd.plan_id (+)
510 AND msi.sr_instance_id = msd.sr_instance_id (+)
511 AND msi.organization_id = msd.organization_id (+)
512 AND msi.inventory_item_id = msd.using_assembly_item_id (+)
513 AND nvl(msd.customer_id, -1) = nvl(p_partner_id, nvl(msd.customer_id, -1))
514 AND msd.customer_id = cust.partner_id (+)
515 AND nvl(cust.customer_class_code, 'XXXXX') = nvl(p_customer_class_code, nvl(cust.customer_class_code, 'XXXXX'))
516 AND (mss.safety_stock_quantity IS NOT NULL OR msd.demand_id IS NOT NULL))
517 GROUP BY plan_id, instance_id, org_id,
518 item_id,
519 sr_category_id, category_name,
520 plan_name,
521 org_name,
522 item_name;
523
524 -- view by customer service levels
525 CURSOR c2 IS
526 SELECT plan_id, nvl(partner_id, UNDEFINED_CUSTOMER_ID), partner_name, nvl(customer_class_code, UNDEFINED_CUSTOMER_CODE),
527 avg(nvl(get_customer_target_sl(partner_id) ,
528 msc_get_bis_values.service_target(plan_id,instance_id,org_id,item_id))) AS target_sl
529 FROM
530 (SELECT DISTINCT msi.plan_id AS plan_id, msi.sr_instance_id AS instance_id, msi.organization_id AS org_id,
531 msi.inventory_item_id AS item_id,
532 mic.sr_category_id AS sr_category_id, mic.category_name AS category_name,
533 mp.compile_designator AS plan_name,
534 mtp.organization_code AS org_name,
535 cust.partner_id, cust.partner_name, cust.customer_class_code, msi.item_name AS item_name
536 FROM msc_plans mp, msc_system_items msi, msc_item_categories mic, msc_trading_partners mtp, msc_trading_partners cust,
537 (SELECT demands.plan_id, demands.sr_instance_id, demands.organization_id, demands.using_assembly_item_id,
538 demands.customer_id, demands.demand_id
539 FROM msc_demands demands
540 WHERE demands.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)) msd
541 WHERE mp.plan_id = p_plan_id
542 AND mp.plan_id = msi.plan_id
543 AND msi.inventory_item_id = NVL(p_item_id, msi.inventory_item_id)
544 AND msi.organization_id = mic.organization_id
545 AND msi.sr_instance_id = mic.sr_instance_id
546 AND msi.inventory_item_id = mic.inventory_item_id
547 AND mic.category_set_id = l_default_category_set_id
548 AND mic.sr_instance_id = nvl(p_sr_cat_instance_id, mic.sr_instance_id)
549 AND mic.sr_category_id = nvl(p_sr_cat_id, mic.sr_category_id)
550 AND msi.organization_id = mtp.sr_tp_id
551 AND msi.sr_instance_id = mtp.sr_instance_id
552 AND mtp.partner_type = 3
553 AND mtp.sr_instance_id = nvl(p_sr_instance_id, mtp.sr_instance_id)
554 AND mtp.sr_tp_id = nvl(p_sr_tp_id, mtp.sr_tp_id)
555 AND msi.plan_id = msd.plan_id
556 AND msi.sr_instance_id = msd.sr_instance_id
557 AND msi.organization_id = msd.organization_id
558 AND msi.inventory_item_id = msd.using_assembly_item_id
559 AND msd.customer_id = cust.partner_id (+)
560 AND nvl(msd.customer_id, -1) = nvl(p_partner_id, nvl(msd.customer_id, -1))
561 AND nvl(cust.customer_class_code, 'XXXXX') = nvl(p_customer_class_code, nvl(cust.customer_class_code, 'XXXXX')))
562 GROUP BY plan_id, partner_id, partner_name, customer_class_code;
563
564 currRecord Bucket;
565 l_target_service_level NUMBER;
566 BEGIN
567 -- Get default category set
568 l_default_category_set_id := get_cat_set_id(p_plan_id);
569
570 -- item,org,inst,plan service levels
571 schedule_dump_header;
572 OPEN c1;
573 LOOP
574 FETCH c1 INTO currRecord.plan_id,currRecord.instance_id,currRecord.org_id,
575 currRecord.item_id, currRecord.sr_category_id,currRecord.category_name,
576 currRecord.plan_name,currRecord.org_name,currRecord.item_name,
577 currRecord.target_service_level;
578 EXIT WHEN c1%NOTFOUND;
579
580 currRecord.num_target_service_level := 1;
581 currRecord.record_type := RECORD_TARGET_SL_VIEWBY_ITEM;
582
583 schedule_output_record(query_id, currRecord);
584 schedule_dump_record(currRecord);
585 END LOOP;
586 CLOSE c1;
587
588 -- customer_id service levels
592 FETCH c2 INTO currRecord.plan_id, currRecord.partner_id,
589 schedule_dump_header;
590 OPEN c2;
591 LOOP
593 currRecord.partner_name,currRecord.customer_class_code, currRecord.target_service_level;
594 EXIT WHEN c2%NOTFOUND;
595
596 currRecord.num_target_service_level := 1;
597 currRecord.record_type := RECORD_TARGET_SL_VIEWBY_CUST;
598
599 schedule_output_record(query_id, currRecord);
600 schedule_dump_record(currRecord);
601 END LOOP;
602 CLOSE c2;
603 END;
604
605 PROCEDURE schedule_bucket_service_level(mainschedule IN OUT NOCOPY Schedule, p_plan_id NUMBER, p_sr_instance_id NUMBER,
606 p_sr_tp_id NUMBER, p_sr_cat_instance_id NUMBER, p_sr_cat_id NUMBER,
607 p_item_id NUMBER, p_partner_id NUMBER, p_customer_class_code VARCHAR2,
608 query_id NUMBER)
609 IS
610 currRecord Bucket;
611 prevRecord Bucket;
612
613 l_default_category_set_id NUMBER;
614
615 CURSOR c1 IS
616 SELECT msd.plan_id AS plan_id,
617 msd.sr_instance_id AS instance_id,
618 msd.organization_id AS org_id,
619 msd.using_assembly_item_id AS item_id,
620 mic.sr_category_id AS sr_category_id,
621 mic.category_name AS category_name,
622 mp.compile_designator AS plan_name,
623 mpo.organization_code AS org_name,
624 msi.item_name AS item_name,
625 trunc(msd.using_assembly_demand_date) AS week_start_date,
626 NULL AS week_next_date,
627 NULL AS week_nr,
628 NULL AS period_start_date,
629 NULL AS period_nr,
630 NULL AS bucket_type,
631 'N' AS last_week_of_period,
632 NULL AS achieved_ss_qty,
633 NULL AS achieved_ss_dollars,
634 NULL AS achieved_ss_days,
635 NULL AS target_ss_qty,
636 NULL AS target_ss_dollars,
637 NULL AS target_ss_days,
638 NULL AS userdef_ss_qty,
639 NULL AS userdef_ss_dollars,
640 NULL AS userdef_ss_days,
641 0 AS num_safety_stock,
642 nvl(msd.old_demand_quantity,0) * nvl(msd.probability,1) AS delivered_quantity,
643 msd.using_requirement_quantity * nvl(msd.probability,1) AS required_quantity,
644 NULL AS target_service_level,
645 0 AS num_target_service_level,
646 msd.customer_id AS partner_id,
647 cust.partner_name AS partner_name,
648 cust.customer_class_code AS customer_class_code,
649 RECORD_SERVICE_LEVEL AS record_type
650 FROM msc_demands msd, msc_system_items msi,
651 msc_item_categories mic, msc_plans mp, msc_plan_organizations mpo,
652 msc_trading_partners cust
653 WHERE mp.plan_id = p_plan_id
654 AND msd.plan_id = msi.plan_id
655 AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
656 AND msd.sr_instance_id = msi.sr_instance_id
657 AND msd.organization_id = msi.organization_id
658 AND msd.using_assembly_item_id = msi.inventory_item_id
659 AND msd.plan_id = mp.plan_id
660 AND msi.inventory_item_id = NVL(p_item_id, msi.inventory_item_id)
661 AND msi.organization_id = mic.organization_id
662 AND msi.sr_instance_id = mic.sr_instance_id
663 AND mic.category_set_id = l_default_category_set_id
664 AND msi.inventory_item_id = mic.inventory_item_id
665 AND mp.plan_id = mpo.plan_id
666 AND msd.organization_id = mpo.organization_id
667 AND msd.sr_instance_id = mpo.sr_instance_id
668 AND mpo.sr_instance_id = nvl(p_sr_instance_id, mpo.sr_instance_id)
669 AND mpo.organization_id = nvl(p_sr_tp_id, mpo.organization_id)
670 AND mic.sr_instance_id = nvl(p_sr_cat_instance_id, mic.sr_instance_id)
671 AND mic.sr_category_id = nvl(p_sr_cat_id, mic.sr_category_id)
672 AND nvl(msd.customer_id, -1) = nvl(p_partner_id, nvl(msd.customer_id, -1))
673 AND nvl(cust.customer_class_code, 'XXXXX') = nvl(p_customer_class_code, nvl(cust.customer_class_code, 'XXXXX'))
674 AND msd.customer_id = cust.partner_id (+)
675 ORDER BY msd.plan_id,msd.sr_instance_id,msd.organization_id,msd.using_assembly_item_id,msd.customer_id;
676
677 BEGIN
678 -- Get default category set
679 l_default_category_set_id := get_cat_set_id(p_plan_id);
680
681 prevRecord.bkt_start_date := NULL; -- Used to test if the previous record was assigned
682 OPEN c1;
683 LOOP
684 FETCH c1 INTO currRecord;
685 IF c1%FOUND THEN
686 IF prevRecord.bkt_start_date IS NOT NULL THEN -- prev record exists
687 IF is_service_level_key_changed(currRecord, prevRecord) THEN
688 bucket_service_level_record(mainschedule, prevRecord, mainschedule(mainschedule.LAST).bkt_start_date + 1);
689 schedule_flush(mainschedule, query_id);
690 schedule_initialize(mainschedule, currRecord);
691 ELSE
692 bucket_service_level_record(mainschedule, prevRecord, currRecord.bkt_start_date);
693 END IF;
694 ELSE
695 schedule_initialize(mainschedule, currRecord);
696 END IF;
697 prevRecord := currRecord;
698 ELSE
699 IF prevRecord.bkt_start_date IS NOT NULL THEN
700 bucket_service_level_record(mainschedule, prevRecord, mainschedule(mainschedule.LAST).bkt_start_date + 1);
701 schedule_flush(mainschedule, query_id);
702 END IF;
706 CLOSE c1;
703 EXIT;
704 END IF;
705 END LOOP;
707 END;
708
709 PROCEDURE schedule_bucket_safety_stock(mainschedule IN OUT NOCOPY Schedule, p_plan_id NUMBER, p_sr_instance_id NUMBER,
710 p_sr_tp_id NUMBER, p_sr_cat_instance_id NUMBER, p_sr_cat_id NUMBER,
711 p_item_id NUMBER, query_id NUMBER)
712 IS
713 currRecord Bucket;
714 prevRecord Bucket;
715
716 l_default_category_set_id NUMBER;
717
718 CURSOR c1 IS
719 SELECT mss.plan_id AS plan_id, mss.sr_instance_id AS instance_id, mss.organization_id AS org_id,
720 mss.inventory_item_id AS item_id,
721 mic.sr_category_id AS sr_category_id, mic.category_name AS category_name,
722 mp.compile_designator AS plan_name,
723 mpo.organization_code AS org_name,
724 msi.item_name,
725 mss.period_start_date AS week_start_date,
726 NULL AS week_next_date,
727 NULL AS week_nr, NULL AS period_start_date, NULL AS period_nr,
728 NULL AS bucket_type,
729 'N' AS last_week_of_period,
730 mss.safety_stock_quantity AS achieved_ss_qty,
731 mss.safety_stock_quantity*msi.standard_cost
732 AS achieved_ss_dollars,
733 mss.achieved_days_of_supply AS achieved_ss_days,
734 mss.target_safety_stock AS target_ss_qty,
735 mss.target_safety_stock * msi.standard_cost
736 AS target_ss_dollars,
737 mss.target_days_of_supply AS target_ss_days,
738 mss.user_defined_safety_stocks AS userdef_ss_qty,
739 mss.user_defined_safety_stocks * msi.standard_cost
740 AS userdef_ss_dollars,
741 mss.user_defined_dos AS userdef_ss_days,
742 0 AS num_safety_stock,
743 NULL AS delivered_quantity,
744 NULL AS required_quantity,
745 NULL AS target_service_level,
746 0 AS num_target_service_level,
747 NULL AS partner_id,
748 NULL AS partner_name,
749 NULL AS customer_class_code,
750 RECORD_SAFETY_STOCK AS record_type
751 FROM msc_safety_stocks mss, msc_system_items msi, msc_item_categories mic,
752 msc_plans mp, msc_plan_organizations mpo
753 WHERE mss.sr_instance_id = msi.sr_instance_id
754 AND mss.plan_id = msi.plan_id
755 AND mss.organization_id = msi.organization_id
756 AND mss.inventory_item_id = msi.inventory_item_id
757 AND msi.inventory_item_id = NVL(p_item_id, msi.inventory_item_id)
758 AND msi.sr_instance_id = mic.sr_instance_id
759 AND msi.inventory_item_id = mic.inventory_item_id
760 AND msi.organization_id = mic.organization_id
761 AND mic.category_set_id = l_default_category_set_id
762 AND mss.plan_id = p_plan_id
763 AND mss.plan_id = mp.plan_id
764 AND mp.plan_id = mpo.plan_id
765 AND mss.organization_id = mpo.organization_id
766 AND mss.sr_instance_id = mpo.sr_instance_id
767 AND mpo.sr_instance_id = nvl(p_sr_instance_id, mpo.sr_instance_id)
768 AND mpo.organization_id = nvl(p_sr_tp_id, mpo.organization_id)
769 AND mic.sr_instance_id = nvl(p_sr_cat_instance_id, mic.sr_instance_id)
770 AND mic.sr_category_id = nvl(p_sr_cat_id, mic.sr_category_id)
771 ORDER BY 1,2,3,4;
772
773 BEGIN
774 -- Get default category set
775 l_default_category_set_id := get_cat_set_id(p_plan_id);
776
777 prevRecord.bkt_start_date := NULL; -- Used to test if the previous record was assigned
778 OPEN c1;
779 LOOP
780 FETCH c1 INTO currRecord;
781 IF c1%FOUND THEN
782 IF prevRecord.bkt_start_date IS NOT NULL THEN -- prev record exists
783 IF is_safety_stock_key_changed(currRecord, prevRecord) THEN
784 bucket_safety_stock_record(mainschedule, prevRecord, mainschedule(mainschedule.LAST).bkt_start_date + 1);
785 schedule_ss_fill_gaps(mainschedule);
786 schedule_flush(mainschedule, query_id);
787 schedule_initialize(mainschedule, currRecord);
788 ELSE
789 bucket_safety_stock_record(mainschedule, prevRecord, currRecord.bkt_start_date);
790 END IF;
791 ELSE
792 schedule_initialize(mainschedule, currRecord);
793 END IF;
794 prevRecord := currRecord;
795 ELSE
796 IF prevRecord.bkt_start_date IS NOT NULL THEN
797 bucket_safety_stock_record(mainschedule, prevRecord, mainschedule(mainschedule.LAST).bkt_start_date + 1);
798 schedule_ss_fill_gaps(mainschedule);
799 schedule_flush(mainschedule, query_id);
800 END IF;
801 EXIT;
802 END IF;
803 END LOOP;
804 CLOSE c1;
805 END;
806
807 FUNCTION validate_plan(p_plan_id NUMBER)
808 RETURN BOOLEAN
809 IS
810 CURSOR c1(p_plan_id NUMBER) IS
811 SELECT 1
812 FROM msc_plans_tree_v mpt, msc_plans mp
813 WHERE mpt.curr_plan_type = 4
814 AND mpt.plan_id = mp.plan_id
815 AND mp.plan_start_date IS NOT NULL
816 AND mp.plan_id = p_plan_id;
817
818 l_result NUMBER := NULL;
819 BEGIN
820
821 OPEN c1(p_plan_id);
822 FETCH c1 INTO l_result;
823 CLOSE c1;
824
825 IF l_result = 1 THEN
826 RETURN TRUE;
827 ELSE
828 RETURN FALSE;
829 END IF;
830
831 EXCEPTION
832 WHEN OTHERS THEN
833 RETURN FALSE;
834 END;
835
836 FUNCTION get_sr_cat_id_from_cat_key(org_id VARCHAR2)
837 RETURN NUMBER
838 IS
839 dash NUMBER;
840 BEGIN
841 IF org_id IS NULL THEN
842 RETURN NULL;
843 END IF;
844
845 dash := INSTR(org_id, '-');
846
847 IF dash = 0 OR dash = 1 THEN
848 RETURN NULL;
849 END IF;
850
851 RETURN SUBSTR(org_id, dash + 1);
852 END;
853
854 FUNCTION get_inst_id_from_org_key(org_id VARCHAR2)
855 RETURN NUMBER
856 IS
857 dash NUMBER;
858 BEGIN
859 IF org_id IS NULL THEN
860 RETURN NULL;
861 END IF;
862
863 dash := INSTR(org_id, '-');
864
865 IF dash = 0 OR dash = 1 THEN
866 RETURN NULL;
867 END IF;
868
869 RETURN SUBSTR(org_id, 1, dash - 1);
870 END;
871
872 FUNCTION get_sr_tp_id_from_org_key(cat_id VARCHAR2)
873 RETURN NUMBER
874 IS
875 dash NUMBER;
876 BEGIN
877 IF cat_id IS NULL THEN
878 RETURN NULL;
879 END IF;
880
881 dash := INSTR(cat_id, '-');
882
883 IF dash = 0 OR dash = 1 THEN
884 RETURN NULL;
885 END IF;
886
890 FUNCTION get_inst_id_from_category_key(cat_id VARCHAR2)
887 RETURN SUBSTR(cat_id, dash + 1);
888 END;
889
891 RETURN NUMBER
892 IS
893 dash NUMBER;
894 BEGIN
895 IF cat_id IS NULL THEN
896 RETURN NULL;
897 END IF;
898
899 dash := INSTR(cat_id, '-');
900
901 IF dash = 0 OR dash = 1 THEN
902 RETURN NULL;
903 END IF;
904
905 RETURN SUBSTR(cat_id, 1, dash - 1);
906 END;
907
908 PROCEDURE schedule_retrieve(query_id OUT NOCOPY NUMBER, plan_id IN VARCHAR2, org_id IN VARCHAR2, cat_id IN VARCHAR2,
909 item_id IN NUMBER, customer_id IN NUMBER, customer_class_code IN VARCHAR2)
910 IS
911 mainschedule Schedule;
912 sr_instance_id NUMBER;
913 sr_tp_id NUMBER;
914 sr_cat_instance_id NUMBER;
915 sr_cat_id NUMBER;
916 l_planlist PlanList;
917 l_index BINARY_INTEGER;
918 BEGIN
919
920 SELECT msc_form_query_s.nextval
921 INTO query_id
922 FROM dual;
923
924 IF org_id IS NOT NULL THEN
925 sr_instance_id := get_inst_id_from_org_key(org_id);
926 sr_tp_id := get_sr_tp_id_from_org_key(org_id);
927 END IF;
928
929 IF cat_id IS NOT NULL THEN
930 sr_cat_instance_id := get_inst_id_from_category_key(cat_id);
931 sr_cat_id := get_sr_cat_id_from_cat_key(cat_id);
932 END IF;
933
934 parse_planlist(plan_id, l_planlist);
935 IF l_planlist.COUNT = 0 THEN
936 query_id := -1;
937 RETURN;
938 END IF;
939
940 FOR l_index IN l_planlist.FIRST..l_planlist.LAST LOOP
941 IF validate_plan(l_planlist(l_index)) = FALSE THEN
942 query_id := -1;
943 RETURN;
944 END IF;
945
946 schedule_create(mainschedule, l_planlist(l_index));
947
948 IF customer_id IS NULL AND customer_class_code IS NULL THEN
949 schedule_bucket_safety_stock(mainschedule, l_planlist(l_index), sr_instance_id, sr_tp_id,
950 sr_cat_instance_id, sr_cat_id, item_id, query_Id);
951 END IF;
952
953 schedule_bucket_service_level(mainschedule, l_planlist(l_index), sr_instance_id, sr_tp_id,
954 sr_cat_instance_id, sr_cat_id, item_id, customer_id, customer_class_code,query_Id);
955
956 schedule_target_service_level(mainschedule, l_planlist(l_index), sr_instance_id, sr_tp_id,
957 sr_cat_instance_id, sr_cat_id, item_id, customer_id, customer_class_code,query_Id);
958
959 END LOOP;
960 END;
961
962 END;