[Home] [Help]
PACKAGE BODY: APPS.MSC_ANALYSIS_BUDGET
Source
1 PACKAGE BODY MSC_ANALYSIS_BUDGET AS
2 /* $Header: MSCAIBB.pls 120.0 2005/05/25 18:45:05 appldev noship $ */
3 --
4 --
5 --
6 RECORD_BUDGET CONSTANT NUMBER := 0;
7 RECORD_SERVICE_LEVEL CONSTANT NUMBER := 1;
8 RECORD_TARGET_SERVICE_LEVEL CONSTANT NUMBER := 2;
9
10 UNDEFINED_CUSTOMER_CODE CONSTANT VARCHAR2(30) := '_MISC'; -- Used in Java
11 UNDEFINED_CUSTOMER_ID CONSTANT NUMBER := -1;
12
13 CALENDAR_TYPE_MFG CONSTANT NUMBER := 1;
14 CALENDAR_TYPE_BIS CONSTANT NUMBER := 0;
15
16 PLAN_BUCKET_WEEK CONSTANT NUMBER := 2;
17 PLAN_BUCKET_PERIOD CONSTANT NUMBER := 3;
18
19 DETAIL_LEVEL_WEEK CONSTANT NUMBER := 1;
20 DETAIL_LEVEL_PERIOD CONSTANT NUMBER := 0;
21
22 BUDGET_CONSTRAINED_OFF CONSTANT NUMBER := 2;
23 BUDGET_CONSTRAINED_ON CONSTANT NUMBER := 1;
24
25 do_debug BOOLEAN := FALSE;
26
27 TYPE Bucket IS RECORD
28 (
29 -- item key data
30 plan_id NUMBER := 0,
31 instance_id NUMBER := 0,
32 org_id NUMBER := 0,
33 org_name VARCHAR2(255) := NULL,
34 sr_category_id NUMBER := 0,
35 category_name VARCHAR2(255) := NULL,
36 plan_name VARCHAR2(255) := NULL,
37 -- bucket key data
38 bkt_start_date DATE := NULL,
39 bkt_end_date DATE := NULL,
40 period_nr NUMBER := 0,
41 week_nr NUMBER := 0,
42 period_start_date DATE := NULL,
43 bucket_type NUMBER := 0,
44 detail_level NUMBER := NULL,
45 -- budget measures
46 achieved_budget_usd NUMBER := 0,
47 -- service level measures
48 delivered_quantity NUMBER := 0,
49 required_quantity NUMBER := 0,
50 target_sl NUMBER := 0,
51 num_target_sl NUMBER := 0,
52 -- service level or safety stock record
53 record_type NUMBER := NULL
54 );
55
56 TYPE Schedule IS TABLE OF Bucket INDEX BY BINARY_INTEGER;
57 TYPE PlanList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
58
59 FUNCTION get_customer_target_sl(customer_id NUMBER)
60 RETURN NUMBER
61 IS
62 l_target_service_level NUMBER;
63 BEGIN
64 SELECT service_level
65 INTO l_target_service_level
66 FROM msc_trading_partners
67 WHERE partner_type = 2
68 AND partner_id = customer_id;
69 RETURN l_target_service_level;
70 EXCEPTION WHEN OTHERS THEN RETURN NULL;
71 END;
72
73 FUNCTION get_cat_set_id(arg_plan_id number) RETURN NUMBER is
74 l_cat_set_id number;
75 l_def_pref_id number;
76 l_plan_type number;
77 cursor plan_type_c(v_plan_id number) is
78 select curr_plan_type
79 from msc_plans
80 where plan_id = v_plan_id;
81 BEGIN
82 open plan_type_c(arg_plan_id);
83 fetch plan_type_c into l_plan_type;
84 close plan_type_c;
85
86 l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
87 l_cat_set_id:= msc_get_name.GET_preference('CATEGORY_SET_ID',l_def_pref_id, l_plan_type);
88 return l_cat_set_id;
89 END get_cat_set_id;
90
91
92 PROCEDURE schedule_create_bis(mainschedule IN OUT NOCOPY Schedule, p_plan_id NUMBER, calendar_type IN NUMBER)
93 IS
94 CURSOR c1(p_plan_id NUMBER) IS
95 SELECT pd.detail_date, mbp.START_DATE, mbp.end_date
96 FROM
97 (SELECT DISTINCT mbid.detail_date
98 FROM msc_bis_inv_detail mbid
99 WHERE mbid.plan_id = p_Plan_id
100 AND (mbid.period_type IS NULL OR mbid.period_type = 0)) pd,
101 msc_plans mp, msc_bis_periods mbp
102 WHERE mp.plan_id = p_plan_id
103 AND mp.sr_instance_id = mbp.sr_instance_id
104 AND mp.organization_id = mbp.organization_id
105 AND mbp.period_set_name = 'Accounting'
106 AND pd.detail_date = mbp.START_DATE
107 ORDER BY pd.detail_date;
108
109 c1Rec c1%ROWTYPE;
110
111 currentBucket Bucket;
112 current_period_nr NUMBER := 1;
113
114 BEGIN
115
116 OPEN c1(p_plan_id);
117 LOOP
118 FETCH c1 INTO c1Rec;
119 EXIT WHEN c1%NOTFOUND;
120
121 -- These three are only used for MFG calendar data
122 currentBucket.week_nr := NULL;
123 currentBucket.period_start_date := c1Rec.START_DATE;
124 currentBucket.bucket_type := PLAN_BUCKET_PERIOD;
125 -- These are for BIS calendar data
126 currentBucket.bkt_start_date := c1Rec.start_date;
127 currentBucket.bkt_end_date := c1Rec.end_date;
128
129 currentBucket.period_nr := current_period_nr;
130
131 mainschedule(current_period_nr) := currentBucket;
132 current_period_nr := current_period_nr + 1;
133 END LOOP;
134 CLOSE c1;
135
136 END;
137
138 PROCEDURE schedule_create_mfg(mainschedule IN OUT NOCOPY Schedule, p_plan_id NUMBER, calendar_type IN NUMBER)
139 IS
140 CURSOR c1(p_plan_id NUMBER) IS
141 SELECT mp.plan_id, mp.compile_designator, mtp.calendar_code, mpb.bkt_start_date, mpb.bkt_end_date,
142 mpsd.period_start_date, mpb.bucket_type
143 FROM msc_plans mp, msc_trading_partners mtp, msc_plan_buckets mpb, msc_period_start_dates mpsd
144 WHERE mp.plan_id = p_plan_id
145 AND mtp.sr_tp_id = mp.organization_id
146 AND mtp.sr_instance_id = mp.sr_instance_id
147 AND mtp.partner_type = 3
148 AND mp.plan_id = mpb.plan_id
149 AND mp.sr_instance_id = mpb.sr_instance_id
150 AND mp.organization_id = mpb.organization_id
151 AND mtp.sr_Instance_id = mpsd.sr_instance_id
152 AND mtp.calendar_code = mpsd.calendar_code
153 AND mpb.bucket_type in (2,3)
154 AND mpb.sr_instance_id = mpsd.sr_instance_id
155 AND mpsd.exception_set_id = mtp.calendar_exception_set_id
156 AND mpsd.calendar_code = mtp.calendar_code
157 AND (mpb.bkt_start_date >= mpsd.period_start_date AND mpb.bkt_start_date < mpsd.next_date)
158 ORDER BY mpb.bkt_start_date;
159
160 c1Rec c1%ROWTYPE;
161
162 currentBucket Bucket;
163 currentBucketNr NUMBER := 1;
164 current_period_nr NUMBER := 1;
165 previous_period_start_date DATE := NULL;
166 current_week_nr NUMBER := 1;
167
168 BEGIN
169
170 OPEN c1(p_plan_id);
171 LOOP
172 FETCH c1 INTO c1Rec;
173 EXIT WHEN c1%NOTFOUND;
174
175 -- These three are only used for MFG calendar data
176 IF c1Rec.bucket_type = PLAN_BUCKET_WEEK THEN
177 currentBucket.week_nr := current_week_nr;
178 current_week_nr := current_week_nr + 1;
179 ELSE
180 currentBucket.week_nr := NULL;
181 END IF;
182 currentBucket.period_start_date := c1Rec.period_start_date;
183 currentBucket.bucket_type := c1Rec.bucket_type;
184 -- These are for BIS calendar data
185 currentBucket.bkt_start_date := c1Rec.bkt_start_date;
186 currentBucket.bkt_end_date := c1Rec.bkt_end_date;
187
188 IF currentBucket.period_start_date <> previous_period_start_date THEN
189 current_period_nr := current_period_nr + 1;
190 END IF;
191 currentBucket.period_nr := current_period_nr;
192
193 previous_period_start_date := currentBucket.period_start_date;
194 mainschedule(currentBucketNr) := currentBucket;
195 currentBucketNr := currentBucketNr + 1;
196 END LOOP;
197 CLOSE c1;
198 END;
199
200 PROCEDURE schedule_output_record(query_id NUMBER, aRecord Bucket)
201 IS
202 BEGIN
203 INSERT INTO msc_form_query
204 (query_id, last_update_date, last_updated_by, creation_date, created_by,
205 NUMBER1, NUMBER2, NUMBER3,
206 CHAR1, NUMBER4, CHAR2,
207 CHAR3, DATE1, DATE2,
208 NUMBER5, NUMBER6, NUMBER7,
209 NUMBER8, NUMBER9, NUMBER10,
210 NUMBER11, NUMBER12, NUMBER13,
211 NUMBER14,DATE3)
212 VALUES
213 (query_id, SYSDATE, -1, SYSDATE, -1,
214 aRecord.plan_id, aRecord.instance_id, aRecord.org_id,
215 aRecord.org_name, aRecord.sr_category_id, aRecord.category_name,
216 aRecord.plan_name, aRecord.bkt_start_date, aRecord.bkt_end_date,
217 aRecord.period_nr, aRecord.achieved_budget_usd, aRecord.delivered_quantity,
218 aRecord.required_quantity, aRecord.target_sl, aRecord.num_target_sl,
219 aRecord.record_type, aRecord.week_nr, aRecord.bucket_type,
220 aRecord.detail_level,aRecord.period_start_date);
221
222 END;
223
224 PROCEDURE schedule_dump_header
225 IS
226 BEGIN
227 IF do_debug = FALSE THEN
228 RETURN;
229 END IF;
230 dbms_output.put('plan_id,instance_id,org_id,org_name,plan_name,cat_id,cat_name,bucket_type,');
231 dbms_output.put_line('detail_level,start_date,end_date,period_nr,achieved_budget_dollars,record_type,required_qty,delivered_qty,target_sl_qty,nr_target_sl,week_nr,period_start_date');
232 END;
233
234 PROCEDURE schedule_dump_record(aRecord BUCKET)
235 IS
236 BEGIN
237 IF do_debug = FALSE THEN
238 RETURN;
239 END IF;
240 dbms_output.put('"'||aRecord.plan_id || '","' || aRecord.instance_id || '",');
241 dbms_output.put('"'|| aRecord.org_id || '",');
242 DBMS_OUTPUT.put('"' || aRecord.org_name || '","' || aRecord.plan_name || '",');
243 dbms_output.put('"' || aRecord.sr_category_id || '","' || aRecord.category_name || '",');
244 DBMS_OUTPUT.put('"' || aRecord.bucket_type || '",');
245 DBMS_OUTPUT.put('"' || aRecord.detail_level || '",');
246 dbms_output.put('"' || aRecord.bkt_start_date || '",');
247 dbms_output.put('"' || aRecord.bkt_end_date || '",');
248 dbms_output.put('"' || aRecord.period_nr || '",');
249 dbms_output.put('"' || aRecord.achieved_budget_usd || '",');
250 IF aRecord.record_type = RECORD_BUDGET THEN
251 dbms_output.put('"BUDGET",');
252 ELSIF aRecord.record_type = RECORD_SERVICE_LEVEL THEN
253 dbms_output.put('"SERVICE_LEVEL",');
254 ELSIF aRecord.record_type = RECORD_TARGET_SERVICE_LEVEL THEN
255 dbms_output.put('"TARGET_SL",');
256 ELSE
257 dbms_output.put('"NULL",');
258 END IF;
259
260 dbms_output.put('"' || aRecord.required_quantity || '",');
261 dbms_output.put('"' || aRecord.delivered_quantity || '",');
262 dbms_output.put('"' || aRecord.target_sl || '",');
263 dbms_output.put('"' || aRecord.num_target_sl || '",');
264 dbms_output.put('"' || aRecord.week_nr || '",');
265 dbms_output.put_line('"' || aRecord.period_start_date || '"');
266 END;
267
268 PROCEDURE schedule_flush(mainschedule Schedule, query_id NUMBER)
269 IS
270 BEGIN
271 schedule_dump_header;
272 FOR i IN mainschedule.FIRST..mainschedule.LAST LOOP
273 schedule_output_record(query_id, mainschedule(i));
274 schedule_dump_record(mainschedule(i));
275 END LOOP;
276 END;
277
278 FUNCTION is_bucket_match(aBucket Bucket, aRecord Bucket)
279 RETURN BOOLEAN
280 IS
281 BEGIN
282 IF aRecord.bkt_start_date >= aBucket.bkt_start_date AND
283 aRecord.bkt_start_date <= aBucket.bkt_end_date
284 THEN
285 RETURN TRUE;
286 END IF;
287
288 RETURN FALSE;
289 END;
290
291 PROCEDURE bucket_budget_record(mainschedule IN OUT NOCOPY Schedule, aRecord Bucket, end_date DATE)
292 IS
293 BEGIN
294 FOR i IN mainschedule.FIRST..mainschedule.LAST LOOP
295 IF aRecord.detail_level = DETAIL_LEVEL_PERIOD THEN
296 IF mainschedule(i).period_start_date = aRecord.bkt_start_date THEN
297 mainschedule(i).achieved_budget_usd := mainschedule(i).achieved_budget_usd + aRecord.achieved_budget_usd;
298 EXIT;
299 END IF;
300 ELSIF is_bucket_match(mainschedule(i), aRecord) THEN
301 mainschedule(i).achieved_budget_usd := mainschedule(i).achieved_budget_usd + aRecord.achieved_budget_usd;
302 END IF;
303 END LOOP;
304 END;
305
306 PROCEDURE bucket_service_level_record(mainschedule IN OUT NOCOPY Schedule, aRecord Bucket)
307 IS
308 BEGIN
309
310 FOR i IN mainschedule.FIRST..mainschedule.LAST LOOP
311 IF is_bucket_match(mainschedule(i), aRecord) THEN
312 mainschedule(i).delivered_quantity := mainschedule(i).delivered_quantity + aRecord.delivered_quantity;
313 mainschedule(i).required_quantity := mainschedule(i).required_quantity + aRecord.required_quantity;
314 mainschedule(i).target_sl := mainschedule(i).target_sl + aRecord.target_sl;
315 mainschedule(i).num_target_sl := mainschedule(i).num_target_sl + aRecord.num_target_sl;
316 EXIT;
317 END IF;
318 END LOOP;
319 END;
320
321 PROCEDURE schedule_initialize(mainschedule IN OUT NOCOPY Schedule, aRecord Bucket)
322 IS
323 BEGIN
324
325 FOR i IN mainschedule.FIRST..mainschedule.LAST LOOP
326 mainschedule(i).achieved_budget_usd := 0;
327 mainschedule(i).delivered_quantity := 0;
328 mainschedule(i).required_quantity := 0;
329 mainschedule(i).target_sl := 0;
330 mainschedule(i).num_target_sl := 0;
331 mainschedule(i).achieved_budget_usd := 0;
332
333 mainschedule(i).plan_id := aRecord.plan_id;
334 mainschedule(i).instance_id := aRecord.instance_id;
335 mainschedule(i).org_id := aRecord.org_id;
336 mainschedule(i).sr_category_id := aRecord.sr_category_id;
337 mainschedule(i).category_name := aRecord.category_name;
338 mainschedule(i).plan_name := aRecord.plan_name;
339 mainschedule(i).org_name := aRecord.org_name;
340 mainschedule(i).detail_level := aRecord.detail_level;
341 mainschedule(i).record_type := aRecord.record_type;
342
343 END LOOP;
344
345 END;
346
347 FUNCTION is_key_changed(currRecord IN Bucket, prevRecord IN Bucket)
348 RETURN BOOLEAN
349 IS
350 BEGIN
351 IF currRecord.plan_id <> prevRecord.plan_id OR
352 currRecord.org_id <> prevRecord.org_id OR
353 currRecord.instance_id <> prevRecord.instance_id OR
354 currRecord.sr_category_id <> prevRecord.sr_category_id
355 THEN
356 RETURN TRUE;
357 ELSE
358 RETURN FALSE;
359 END IF;
360 END;
361
362 FUNCTION is_budget_key_changed(currRecord IN Bucket, prevRecord IN Bucket)
363 RETURN BOOLEAN
364 IS
365 BEGIN
366
367 IF is_key_changed(currRecord, prevRecord) OR
368 currRecord.detail_level <> prevRecord.detail_level
369 THEN
370 RETURN TRUE;
371 ELSE
372 RETURN FALSE;
373 END IF;
374 END;
375
376 PROCEDURE schedule_bucket_budget(mainschedule IN OUT NOCOPY Schedule, p_plan_id NUMBER, p_sr_instance_id NUMBER,
377 p_sr_tp_id NUMBER, p_sr_cat_instance_id NUMBER, p_sr_cat_id NUMBER,
378 query_id NUMBER, p_calendar_type NUMBER, p_abc_id VARCHAR2)
379 IS
380 currRecord Bucket;
381 prevRecord Bucket;
382
383 l_default_category_set_id NUMBER;
384
385 CURSOR c1 IS
386 SELECT mbid.plan_id, mbid.sr_instance_id AS instance_id, mbid.organization_id as org_id, mtp.organization_code AS org_name,
387 mic.sr_category_id, mic.category_name, mp.compile_designator AS plan_name, mbid.detail_date as bkt_start_date,
388 NULL AS bkt_end_date, NULL AS period_nr,
389 NULL AS week_nr, NULL AS period_start_date, NULL AS bucket_type, NVL(mbid.detail_level, DETAIL_LEVEL_PERIOD) AS detail_level,
390 SUM(nvl(mbid.inventory_value, 0)) AS achieved_budget_usd, NULL as delivered_quantity, 0 as required_quantity,
391 NULL AS target_sl, NULL AS num_target_sl, RECORD_BUDGET AS record_type
392 FROM msc_bis_inv_detail mbid, msc_system_items mis, msc_item_categories mic,
393 msc_trading_partners mtp, msc_plans mp
394 WHERE mbid.plan_id = p_plan_id
395 AND mbid.plan_id = mp.plan_id
396 AND mbid.plan_id = mis.plan_id
397 AND mbid.organization_id = mis.organization_id
398 AND mbid.sr_instance_id = mis.sr_instance_id
399 AND mbid.inventory_item_id = mis.inventory_item_id
400 AND mbid.sr_instance_id = nvl(p_sr_instance_id, mbid.sr_instance_id)
401 AND mbid.organization_id = nvl(p_sr_tp_id, mbid.organization_id)
402 AND NVL(mbid.period_type, CALENDAR_TYPE_BIS) = p_calendar_type
403 AND mis.organization_id = mic.organization_id
404 AND mis.sr_instance_id = mic.sr_instance_id
405 AND mis.inventory_item_id = mic.inventory_item_id
406 AND mic.category_set_id = l_default_category_set_id
407 AND mic.sr_instance_id = nvl(p_sr_cat_instance_id, mic.sr_instance_id)
408 AND mic.sr_category_id = nvl(p_sr_cat_id, mic.sr_category_id)
409 AND mis.organization_id = mtp.sr_tp_id
410 AND mis.sr_instance_id = mtp.sr_instance_id
411 AND mis.budget_constrained = BUDGET_CONSTRAINED_ON
412 AND mtp.partner_type = 3
413 AND nvl(mis.abc_class_name, 'XXXXX') = nvl(p_abc_id, nvl(mis.abc_class_name, 'XXXXX'))
414 GROUP BY mbid.plan_id, mbid.sr_instance_id, mbid.organization_id, mtp.organization_code,
415 mic.sr_category_id, mic.category_name, mp.compile_designator, mbid.detail_date, NVL(mbid.detail_level, DETAIL_LEVEL_PERIOD)
416 ORDER BY mbid.plan_id, mbid.organization_id, mbid.sr_instance_id, mtp.organization_code,
417 mic.sr_category_id, mic.category_name, NVL(mbid.detail_level, DETAIL_LEVEL_PERIOD), mbid.detail_date;
418
419 BEGIN
420
421 l_default_category_set_id := get_cat_set_id(p_plan_id);
422
423 prevRecord.bkt_start_date := NULL; -- Used to test if the previous record was assigned
424 OPEN c1;
425 LOOP
426 FETCH c1 INTO currRecord;
427 IF c1%FOUND THEN
428 IF prevRecord.bkt_start_date IS NOT NULL THEN -- prev record exists
429 IF is_budget_key_changed(currRecord, prevRecord) THEN
430 bucket_budget_record(mainschedule, prevRecord, mainschedule(mainschedule.LAST).bkt_start_date + 1);
431 schedule_flush(mainschedule, query_id);
432 schedule_initialize(mainschedule, currRecord);
433 ELSE
434 bucket_budget_record(mainschedule, prevRecord, currRecord.bkt_start_date);
435 END IF;
436 ELSE
437 schedule_initialize(mainschedule, currRecord);
438 END IF;
439 prevRecord := currRecord;
440 ELSE
441 IF prevRecord.bkt_start_date IS NOT NULL THEN
442 bucket_budget_record(mainschedule, prevRecord, mainschedule(mainschedule.LAST).bkt_start_date + 1);
443 schedule_flush(mainschedule, query_id);
444 END IF;
445 EXIT;
446 END IF;
447 END LOOP;
448 CLOSE c1;
449 END;
450
451 PROCEDURE schedule_bucket_servicelevel(mainschedule IN OUT NOCOPY Schedule, p_plan_id NUMBER, p_sr_instance_id NUMBER,
452 p_sr_tp_id NUMBER, p_sr_cat_instance_id NUMBER, p_sr_cat_id NUMBER,
453 p_abc_id VARCHAR2, query_id NUMBER)
454 IS
455 currRecord Bucket;
456 prevRecord Bucket;
457
458 l_default_category_set_id NUMBER;
459 CURSOR c1 IS
460 SELECT msd.plan_id AS plan_id,
461 msd.sr_instance_id AS instance_id,
462 msd.organization_id AS org_id,
463 mtp.organization_code AS org_name,
464 mic.sr_category_id AS sr_category_id,
465 mic.category_name AS category_name,
466 mp.compile_designator AS plan_name,
467 trunc(msd.using_assembly_demand_date) AS bkt_start_date,
468 NULL AS bkt_end_date,
469 NULL AS period_nr,
470 NULL AS week_nr, NULL AS period_start_date, NULL AS bucket_type, NULL AS detail_level,
471 0 AS achieved_budget_usd,
472 sum(nvl(msd.old_demand_quantity,0)*
473 nvl(msd.probability,1)) AS delivered_quantity,
474 sum(nvl(msd.using_requirement_quantity, 0)*
475 nvl(msd.probability,1)) AS required_quantity,
476 sum(nvl(msd.service_level, 50)) AS target_service_level,
477 count(*) AS num_target_service_level,
478 RECORD_SERVICE_LEVEL AS record_type
479 FROM msc_plans mp, msc_plan_organizations mpo, msc_demands msd, msc_system_items msi,
480 msc_item_categories mic, msc_trading_partners mtp
481 WHERE mp.plan_id = p_plan_id
482 AND mp.plan_id = mpo.plan_id
483 AND mpo.plan_id = msd.plan_id
484 AND mpo.sr_instance_id = msd.sr_instance_id
485 AND mpo.organization_id = msd.organization_id
486 AND msd.plan_id = msi.plan_id
487 AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
488 AND msd.sr_instance_id = msi.sr_instance_id
489 AND msd.organization_id = msi.organization_id
490 AND msd.using_assembly_item_id = msi.inventory_item_id
491 AND msd.plan_id = mp.plan_id
492 AND msi.organization_id = mic.organization_id
493 AND msi.sr_instance_id = mic.sr_instance_id
494 AND msi.budget_constrained = BUDGET_CONSTRAINED_ON
495 AND mic.category_set_id = l_default_category_set_id
496 AND msi.inventory_item_id = mic.inventory_item_id
497 AND msd.organization_id = mtp.sr_tp_id
498 AND msd.sr_instance_id = mtp.sr_instance_id
499 AND mtp.sr_instance_id = nvl(p_sr_instance_id, mtp.sr_instance_id)
500 AND mtp.sr_tp_id = nvl(p_sr_tp_id, mtp.sr_tp_id)
501 AND mic.sr_instance_id = nvl(p_sr_cat_instance_id, mic.sr_instance_id)
502 AND mic.sr_category_id = nvl(p_sr_cat_id, mic.sr_category_id)
503 AND nvl(msi.abc_class_name, 'XXXXX') = nvl(p_abc_id, nvl(msi.abc_class_name, 'XXXXX'))
504 GROUP BY msd.plan_id, msd.sr_instance_id, msd.organization_id, mtp.organization_code,
505 mic.sr_category_id, mic.category_name, mp.compile_designator, trunc(msd.using_assembly_demand_date)
506 ORDER BY msd.plan_id, msd.organization_id, msd.sr_instance_id, mtp.organization_code,
507 mic.sr_category_id, mic.category_name, trunc(msd.using_assembly_demand_date);
508 BEGIN
509
510 l_default_category_set_id := get_cat_set_id(p_plan_id);
511
512 prevRecord.bkt_start_date := NULL; -- Used to test if the previous record was assigned
513 OPEN c1;
514 LOOP
515 FETCH c1 INTO currRecord;
516 IF c1%FOUND THEN
517 IF prevRecord.bkt_start_date IS NOT NULL THEN -- prev record exists
518 bucket_service_level_record(mainschedule, prevRecord);
519 IF is_key_changed(currRecord, prevRecord) THEN
520 schedule_flush(mainschedule, query_id);
521 schedule_initialize(mainschedule, currRecord);
522 END IF;
523 ELSE
524 schedule_initialize(mainschedule, currRecord);
525 END IF;
526 prevRecord := currRecord;
527 ELSE
528 IF prevRecord.bkt_start_date IS NOT NULL THEN
529 bucket_service_level_record(mainschedule, prevRecord);
530 schedule_flush(mainschedule, query_id);
531 END IF;
532 EXIT;
533 END IF;
534 END LOOP;
535 CLOSE c1;
536 END;
537
538 PROCEDURE schedule_target_service_level(
539 mainschedule IN OUT NOCOPY Schedule, p_plan_id NUMBER, p_sr_instance_id NUMBER,
540 p_sr_tp_id NUMBER, p_sr_cat_instance_id NUMBER, p_sr_cat_id NUMBER,
541 p_abc_id VARCHAR2, p_calendar_type NUMBER, query_id NUMBER)
542 IS
543 l_default_category_set_id NUMBER;
544
545 CURSOR c1 IS
546 SELECT plan_id, instance_id, org_id, sr_category_id, category_name, plan_name, org_name, SUM(target_sl) AS target_sl, COUNT(*) AS num_target_sl
547 FROM
548 (
549 SELECT plan_id, instance_id, org_id, sr_category_id, category_name, plan_name, org_name, item_id, item_name, AVG(target_sl) AS target_sl
550 FROM
551 (
552 SELECT plan_id, instance_id, org_id,
553 sr_category_id, category_name,
554 plan_name,
555 org_name, item_id, item_name,
556 nvl(get_customer_target_sl(partner_id) ,
557 msc_get_bis_values.service_target(plan_id,instance_id,org_id,item_id)) AS target_sl
558 FROM
559 (
560 SELECT DISTINCT msi.plan_id AS plan_id, msi.sr_instance_id AS instance_id, msi.organization_id AS org_id,
561 msi.inventory_item_id AS item_id,
562 mic.sr_category_id AS sr_category_id, mic.category_name AS category_name,
563 mp.compile_designator AS plan_name,
564 mtp.organization_code AS org_name,
565 msi.item_name AS item_name, msd.customer_id AS partner_id
566 FROM msc_plans mp, msc_system_items msi, msc_item_categories mic, msc_trading_partners mtp,
567 (SELECT demands.plan_id, demands.sr_instance_id, demands.organization_id, demands.using_assembly_item_id,
568 demands.customer_id, demands.demand_id
569 FROM msc_demands demands
570 WHERE demands.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)) msd,
571 (SELECT kpi.inventory_item_id, kpi.sr_instance_id, kpi.organization_id, kpi.plan_id
572 FROM msc_bis_inv_detail kpi
573 WHERE NVL(kpi.period_type, CALENDAR_TYPE_BIS) = p_calendar_type
574 AND kpi.plan_id = p_plan_id) mbid
575 WHERE mp.plan_id = p_plan_id
576 AND mp.plan_id = msi.plan_id
577 AND msi.organization_id = mic.organization_id
578 AND msi.sr_instance_id = mic.sr_instance_id
579 AND msi.inventory_item_id = mic.inventory_item_id
580 AND mic.category_set_id = l_default_category_set_id
581 AND mic.sr_instance_id = nvl(p_sr_cat_instance_id, mic.sr_instance_id)
582 AND mic.sr_category_id = nvl(p_sr_cat_id, mic.sr_category_id)
583 AND msi.organization_id = mtp.sr_tp_id
584 AND msi.sr_instance_id = mtp.sr_instance_id
585 AND mtp.partner_type = 3
586 AND mtp.sr_instance_id = nvl(p_sr_instance_id, mtp.sr_instance_id)
587 AND mtp.sr_tp_id = nvl(p_sr_tp_id, mtp.sr_tp_id)
588 AND msi.inventory_item_id = mbid.inventory_item_id (+)
589 AND msi.organization_id = mbid.organization_id (+)
590 AND msi.sr_instance_id = mbid.sr_instance_id (+)
591 AND msi.inventory_item_id = mbid.inventory_item_id (+)
592 AND msi.plan_id = mbid.plan_id (+)
593 AND msi.sr_instance_id = nvl(p_sr_instance_id, msi.sr_instance_id)
594 AND msi.organization_id = nvl(p_sr_tp_id, msi.organization_id)
595 AND msi.plan_id = msd.plan_id (+)
596 AND msi.sr_instance_id = msd.sr_instance_id (+)
597 AND msi.organization_id = msd.organization_id (+)
598 AND msi.inventory_item_id = msd.using_assembly_item_id (+)
599 AND msi.budget_constrained = BUDGET_CONSTRAINED_ON
600 AND nvl(msi.abc_class_name, 'XXXXX') = nvl(p_abc_id, nvl(msi.abc_class_name, 'XXXXX'))
601 AND (mbid.plan_id IS NOT NULL OR msd.demand_id IS NOT NULL)
602 ))
603 GROUP BY plan_id, instance_id, org_id,
604 sr_category_id, category_name,
605 plan_name,
606 org_name, item_id, item_name)
607 GROUP BY plan_id, instance_id, org_id,
608 sr_category_id, category_name,
609 plan_name,
610 org_name;
611
612 currRecord Bucket;
613 l_target_service_level NUMBER;
614 BEGIN
615 -- Get default category set
616 l_default_category_set_id := get_cat_set_id(p_plan_id);
617
618 -- item,org,inst,plan service levels
619 schedule_dump_header;
620 OPEN c1;
621 LOOP
622 FETCH c1 INTO currRecord.plan_id,currRecord.instance_id,currRecord.org_id,
623 currRecord.sr_category_id,currRecord.category_name,
624 currRecord.plan_name,currRecord.org_name,
625 currRecord.target_sl, currRecord.num_target_sl;
626 EXIT WHEN c1%NOTFOUND;
627
628 currRecord.record_type := RECORD_TARGET_SERVICE_LEVEL;
629
630 schedule_output_record(query_id, currRecord);
631 schedule_dump_record(currRecord);
632 END LOOP;
633 CLOSE c1;
634
635 END;
636
637 FUNCTION validate_plan(p_plan_id NUMBER)
638 RETURN BOOLEAN
639 IS
640 CURSOR c1(p_plan_id NUMBER) IS
641 SELECT 1
642 FROM msc_plans_tree_v mpt, msc_plans mp
643 WHERE mpt.curr_plan_type = 4
644 AND mpt.plan_id = mp.plan_id
645 AND mp.plan_start_date IS NOT NULL
646 AND mp.plan_id = p_plan_id;
647
648 l_result NUMBER := NULL;
649 BEGIN
650
651 OPEN c1(p_plan_id);
652 FETCH c1 INTO l_result;
653 CLOSE c1;
654
655 IF l_result = 1 THEN
656 RETURN TRUE;
657 ELSE
658 RETURN FALSE;
659 END IF;
660
661 EXCEPTION
662 WHEN OTHERS THEN
663 RETURN FALSE;
664 END;
665
666 FUNCTION get_sr_tp_id_from_org_key(org_id VARCHAR2)
667 RETURN NUMBER
668 IS
669 dash NUMBER;
670 BEGIN
671 IF org_id IS NULL THEN
672 RETURN NULL;
673 END IF;
674
675 dash := INSTR(org_id, '-');
676
677 IF dash = 0 OR dash = 1 THEN
678 RETURN NULL;
679 END IF;
680
681 RETURN SUBSTR(org_id, dash + 1);
682 END;
683
684 FUNCTION get_inst_id_from_org_key(org_id VARCHAR2)
685 RETURN NUMBER
686 IS
687 dash NUMBER;
688 BEGIN
689 IF org_id IS NULL THEN
690 RETURN NULL;
691 END IF;
692
693 dash := INSTR(org_id, '-');
694
695 IF dash = 0 OR dash = 1 THEN
696 RETURN NULL;
697 END IF;
698
699 RETURN SUBSTR(org_id, 1, dash - 1);
700 END;
701
702 FUNCTION get_sr_cat_id_from_cat_key(cat_id VARCHAR2)
703 RETURN NUMBER
704 IS
705 dash NUMBER;
706 BEGIN
707 IF cat_id IS NULL THEN
708 RETURN NULL;
709 END IF;
710
711 dash := INSTR(cat_id, '-');
712
713 IF dash = 0 OR dash = 1 THEN
714 RETURN NULL;
715 END IF;
716
717 RETURN SUBSTR(cat_id, dash + 1);
718 END;
719
720 FUNCTION get_inst_id_from_cat_key(cat_id VARCHAR2)
721 RETURN NUMBER
722 IS
723 dash NUMBER;
724 BEGIN
725 IF cat_id IS NULL THEN
726 RETURN NULL;
727 END IF;
728
729 dash := INSTR(cat_id, '-');
730
731 IF dash = 0 OR dash = 1 THEN
732 RETURN NULL;
733 END IF;
734
735 RETURN SUBSTR(cat_id, 1, dash - 1);
736 END;
737
738 PROCEDURE parse_planlist(p_plans VARCHAR2, p_planlist IN OUT NOCOPY PlanList)
739 IS
740 occurrence NUMBER := 1;
741 stringstart NUMBER := 1;
742 stringend NUMBER := 1;
743 planfound NUMBER := 1;
744 pos NUMBER;
745 token NUMBER;
746 BEGIN
747
748 LOOP
749 pos := INSTR(p_plans, ',', 1, occurrence);
750 occurrence := occurrence + 1;
751 IF pos = 0 THEN
752 stringend := LENGTH(p_plans);
753 token := to_number(SUBSTR(p_plans, stringstart, stringend - stringstart + 1));
754 IF token IS NOT NULL THEN
755 p_planlist(planfound) := token;
756 planfound := planfound + 1;
757 END IF;
758 EXIT;
759 END IF;
760 stringend := pos - 1;
761 token := to_number(SUBSTR(p_plans, stringstart, stringend - stringstart + 1));
762 IF token IS NOT NULL THEN
763 p_planlist(planfound) := token;
764 planfound := planfound + 1;
765 END IF;
766 stringstart := stringend + 2;
767 END LOOP;
768
769 END;
770
771 PROCEDURE schedule_retrieve(query_id OUT NOCOPY NUMBER, plan_id IN VARCHAR2, org_id IN VARCHAR2, cat_id IN VARCHAR2,
772 abc_id IN VARCHAR2, calendar_type IN NUMBER)
773 IS
774 mainschedule Schedule;
775 sr_instance_id NUMBER;
776 sr_tp_id NUMBER;
777 sr_cat_instance_id NUMBER;
778 sr_cat_id NUMBER;
779 l_planlist PlanList;
780
781 BEGIN
782
783 SELECT msc_form_query_s.nextval
784 INTO query_id
785 FROM dual;
786
787 IF org_id IS NOT NULL THEN
788 sr_instance_id := get_inst_id_from_org_key(org_id);
789 sr_tp_id := get_sr_tp_id_from_org_key(org_id);
790 END IF;
791
792 IF cat_id IS NOT NULL THEN
793 sr_cat_instance_id := get_inst_id_from_cat_key(cat_id);
794 sr_cat_id := get_sr_cat_id_from_cat_key(cat_id);
795 END IF;
796
797 parse_planlist(plan_id, l_planlist);
798 IF l_planlist.COUNT = 0 THEN
799 query_id := -1;
800 RETURN;
801 END IF;
802
803 FOR l_index IN l_planlist.FIRST..l_planlist.LAST LOOP
804 IF validate_plan(l_planlist(l_index)) = FALSE THEN
805 query_id := -1;
806 RETURN;
807 END IF;
808
809 IF calendar_type = CALENDAR_TYPE_BIS THEN
810 schedule_create_bis(mainschedule, l_planlist(l_index), calendar_type);
811 ELSE
812 schedule_create_mfg(mainschedule, l_planlist(l_index), calendar_type);
813 END IF;
814
815 schedule_bucket_budget(mainschedule, l_planlist(l_index), sr_instance_id, sr_tp_id,
816 sr_cat_instance_id, sr_cat_id, query_Id, calendar_type, abc_id);
817
818 schedule_bucket_servicelevel(mainschedule, l_planlist(l_index), sr_instance_id, sr_tp_id,
819 sr_cat_instance_id, sr_cat_id, abc_id, query_Id);
820
821 schedule_target_service_level(mainschedule, l_planlist(l_index), sr_instance_id, sr_tp_id,
822 sr_cat_instance_id, sr_cat_id, abc_id, calendar_type, query_id);
823
824 END LOOP;
825 END;
826
827 END;