[Home] [Help]
PACKAGE BODY: APPS.MSC_GET_BIS_VALUES
Source
1 PACKAGE BODY Msc_Get_Bis_Values AS
2 /* $Header: MSCBISUB.pls 120.7.12010000.3 2008/09/10 20:04:44 pabram ship $ */
3
4 g_plan_start_date DATE;
5 g_period_zero_date date;
6 g_plan_end_date DATE;
7 j binary_integer;
8 g_param varchar2(3) :=':';
9
10 g_use_old_demand_qty constant number := -1;
11
12 INVENTORY_TURNS CONSTANT NUMBER :=1;
13 ONTIME_DELIVERY CONSTANT NUMBER :=2;
14 MARGIN_PERCENT CONSTANT NUMBER :=3;
15 UTILIZATION CONSTANT NUMBER :=4;
16 MARGIN_NUMBER CONSTANT NUMBER :=5;
17 COST_BREAKDOWN CONSTANT NUMBER :=6;
18 SERVICE_LEVEL CONSTANT NUMBER :=7;
19 INVENTORY_VALUE CONSTANT NUMBER :=8;
20 UTILIZATION2 CONSTANT NUMBER := 9;
21
22 TYPE GlPeriodRecTyp IS RECORD (
23 period_name VARCHAR2(15),
24 start_date DATE,
25 end_date DATE);
26
27 TYPE GlPeriodTabTyp IS TABLE OF GlPeriodRecTyp INDEX BY BINARY_INTEGER;
28 g_period_name GlPeriodTabTyp;
29
30 TYPE KPICurTyp IS REF CURSOR;
31 g_org_id number;
32 g_instance_id number;
33 g_category_id number;
34 g_category_name varchar2(250);
35 g_category_set_id number;
36 g_product_family_id number;
37 g_item_id number;
38 g_project_id number;
39 g_task_id number;
40 g_dept_id number;
41 g_res_id number;
42 g_res_instance_id number; --ds Enhancement
43 g_res_inst_serial_number varchar2(255); --ds Enhancement
44 g_dept_class varchar2(10);
45 g_res_group varchar2(30);
46 g_start_date date;
47 g_end_date date;
48 g_sup_id number; --new
49 g_sup_site_id number; -- new
50
51 sql_statement varchar2(30000);
52
53 CURSOR MARGIN_ORG_PF_CURSOR(l_plan_id number,
54 l_org_id number,
55 l_instance_id number,
56 l_product_family_id number) IS
57 SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
58 sum(nvl(production_cost,0)),
59 sum(nvl(purchasing_cost,0)),
60 sum(nvl(demand_penalty_cost,0)+nvl(supplier_overcap_cost,0)),
61 sum(nvl(carrying_cost,0))
62 FROM msc_bis_inv_detail mbis
63 WHERE mbis.organization_id = l_org_id
64 AND mbis.sr_instance_id = l_instance_id
65 AND mbis.plan_id = l_plan_id
66 AND nvl(mbis.period_type,0) = 0 --mbis.mfg period changes
67 and exists ( select 1
68 from msc_bom_components mbc
69 where mbc.organization_id = mbis.organization_id
70 AND mbc.sr_instance_id = mbis.sr_instance_id
71 AND mbc.plan_id = mbis.plan_id
72 and mbc.inventory_item_id = mbis.inventory_item_id
73 and mbc.using_assembly_id = l_product_family_id);
74
75 CURSOR MARGIN_PF_CURSOR(l_plan_id number,
76 l_product_family_id number) IS
77 SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
78 sum(nvl(production_cost,0)),
79 sum(nvl(purchasing_cost,0)),
80 sum(nvl(demand_penalty_cost,0)+nvl(supplier_overcap_cost,0)),
81 sum(nvl(carrying_cost,0))
82 FROM msc_bis_inv_detail mbis
83 where mbis.plan_id = l_plan_id
84 AND nvl(mbis.period_type,0) = 0 --mbis.mfg period changes
85 and exists ( select 1
86 from msc_bom_components mbc
87 where mbc.organization_id = mbis.organization_id
88 AND mbc.sr_instance_id = mbis.sr_instance_id
89 AND mbc.plan_id = mbis.plan_id
90 and mbc.inventory_item_id = mbis.inventory_item_id
91 and mbc.using_assembly_id = l_product_family_id);
92
93 CURSOR MARGIN_ORG_PF_DATE_CURSOR(l_plan_id number,
94 l_org_id number,
95 l_instance_id number,
96 l_product_family_id number,
97 v_start_date DATE ,
98 v_end_date DATE) IS
99 SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
100 sum(nvl(production_cost,0)),
101 sum(nvl(purchasing_cost,0)),
102 sum(nvl(demand_penalty_cost,0)+nvl(supplier_overcap_cost,0)),
103 sum(nvl(carrying_cost,0))
104 FROM msc_bis_inv_detail mbis
105 WHERE mbis.organization_id = l_org_id
106 AND mbis.sr_instance_id = l_instance_id
107 AND nvl(mbis.period_type,0) = 0 --mbis.mfg period changes
108 and mbis.detail_date between v_start_date and v_end_date
109 AND mbis.plan_id = l_plan_id
110 and exists ( select 1
111 from msc_bom_components mbc
112 where mbc.organization_id = mbis.organization_id
113 AND mbc.sr_instance_id = mbis.sr_instance_id
114 AND mbc.plan_id = mbis.plan_id
115 and mbc.inventory_item_id = mbis.inventory_item_id
116 and mbc.using_assembly_id = l_product_family_id);
117
118 CURSOR MARGIN_PF_DATE_CURSOR(l_plan_id number,
119 l_product_family_id number,
120 v_start_date DATE ,
121 v_end_date DATE) IS
122 SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
123 sum(nvl(production_cost,0)),
124 sum(nvl(purchasing_cost,0)),
125 sum(nvl(demand_penalty_cost,0)+nvl(supplier_overcap_cost,0)),
126 sum(nvl(carrying_cost,0))
127 FROM msc_bis_inv_detail mbis
128 where mbis.detail_date between v_start_date and v_end_date
129 AND mbis.plan_id = l_plan_id
130 AND nvl(mbis.period_type,0) = 0 --mbis.mfg period changes
131 and exists ( select 1
132 from msc_bom_components mbc
133 where mbc.organization_id = mbis.organization_id
134 AND mbc.sr_instance_id = mbis.sr_instance_id
135 AND mbc.plan_id = mbis.plan_id
136 and mbc.inventory_item_id = mbis.inventory_item_id
137 and mbc.using_assembly_id = l_product_family_id);
138
139
140 CURSOR MARGIN_ORG_CURSOR(l_plan_id number,
141 l_org_id number,
142 l_instance_id number) IS
143 SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
144 sum(nvl(production_cost,0)),
145 sum(nvl(purchasing_cost,0)),
146 sum(nvl(demand_penalty_cost,0)),
147 sum(nvl(carrying_cost,0))
148 FROM msc_bis_inv_date_mv_tab
149 WHERE organization_id = l_org_id
150 AND sr_instance_id = l_instance_id
151 AND plan_id = l_plan_id;
152
153 CURSOR MARGIN_CURSOR(l_plan_id number) IS
154 SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
155 sum(nvl(production_cost,0)),
156 sum(nvl(purchasing_cost,0)),
157 sum(nvl(demand_penalty_cost,0)),
158 sum(nvl(carrying_cost,0))
159 FROM msc_bis_inv_date_mv_tab
160 WHERE plan_id = l_plan_id;
161
162 CURSOR MARGIN_ORG_DATE_CURSOR(v_plan_id number,
163 v_org_id number,
164 v_instance_id number,
165 v_start_date DATE ,
166 v_end_date DATE) IS
167 SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
168 sum(nvl(production_cost,0)),
169 sum(nvl(purchasing_cost,0)),
170 sum(nvl(demand_penalty_cost,0)),
171 sum(nvl(carrying_cost,0))
172 FROM msc_bis_inv_date_mv_tab
173 WHERE organization_id = v_org_id
174 AND sr_instance_id = v_instance_id
175 and detail_date between v_start_date and v_end_date
176 AND plan_id = v_plan_id;
177
178 CURSOR MARGIN_DATE_CURSOR(v_plan_id number,
179 v_start_date DATE ,
180 v_end_date DATE) IS
181 SELECT SUM(NVL(mds_price,0)), SUM(NVL(mds_cost,0)),
182 sum(nvl(production_cost,0)),
183 sum(nvl(purchasing_cost,0)),
184 sum(nvl(demand_penalty_cost,0)),
185 sum(nvl(carrying_cost,0))
186 FROM msc_bis_inv_date_mv_tab
187 WHERE detail_date between v_start_date and v_end_date
188 AND plan_id = v_plan_id;
189
190 CURSOR INV_VAL_CURSOR(v_plan_id number,
191 v_item_id number,
192 v_start_date DATE ,
193 v_end_date DATE) IS
194 SELECT SUM(nvl(mbi.inventory_value,0)),
195 SUM(NVL(mbi.mds_price,0)), SUM(NVL(mbi.mds_cost,0))
196 FROM msc_bis_inv_detail mbi
197 WHERE mbi.plan_id = v_plan_id
198 AND nvl(mbi.period_type,0) = 0 --bis.mfg period changes
199 and mbi.inventory_item_id = nvl(v_item_id,mbi.inventory_item_id)
200 and mbi.detail_date between nvl(v_start_date, mbi.detail_date-1) and
201 nvl(v_end_date, mbi.detail_date+1)
202 ;
203
204 CURSOR INV_VAL_ORG_CURSOR(v_plan_id number,
205 v_org_id number,
206 v_instance_id number,
207 v_item_id number,
208 v_start_date DATE ,
209 v_end_date DATE) IS
210 SELECT SUM(nvl(mbi.inventory_value,0)),
211 SUM(NVL(mbi.mds_price,0)), SUM(NVL(mbi.mds_cost,0))
212 FROM msc_bis_inv_detail mbi
213 WHERE mbi.plan_id = v_plan_id
214 AND mbi.organization_id = v_org_id
215 AND mbi.sr_instance_id = v_instance_id
216 AND nvl(mbi.period_type,0) = 0 --bis.mfg period changes
217 and mbi.inventory_item_id = nvl(v_item_id, mbi.inventory_item_id)
218 and mbi.detail_date between nvl(v_start_date, mbi.detail_date-1) and
219 nvl(v_end_date, mbi.detail_date+1)
220 ;
221
222 cursor c_plan_orgs (l_plan_id number) is
223 select sr_instance_id, organization_id
224 from msc_plan_organizations
225 where plan_id = l_plan_id;
226
227
228
229 FUNCTION get_inventory_value(p_plan_id IN NUMBER,
230 p_instance_id IN NUMBER,
231 p_organization_id IN NUMBER,
232 p_item_id IN NUMBER) return number IS
233 p_out1 number;
234 v_dummy number;
235 BEGIN
236 if p_organization_id is not null then
237 OPEN INV_VAL_ORG_CURSOR(p_plan_id, p_organization_id,
238 p_instance_id, p_item_id,null,null);
239 FETCH INV_VAL_ORG_CURSOR into p_out1, v_dummy, v_dummy;
240 CLOSE INV_VAL_ORG_CURSOR;
241 else
242 OPEN INV_VAL_CURSOR(p_plan_id,p_item_id,null,null);
243 FETCH INV_VAL_CURSOR into p_out1, v_dummy, v_dummy;
244 CLOSE INV_VAL_CURSOR;
245 end if;
246
247 return p_out1/g_period_name.LAST;
248 END get_inventory_value;
249
250 FUNCTION check_periods(p_plan_id IN NUMBER) RETURN NUMBER IS
251 v_period_count NUMBER;
252
253 CURSOR PERIOD_CURSOR IS
254 SELECT mbp.period_name, mbp.start_date, mbp.end_date
255 FROM msc_bis_periods mbp,
256 msc_plans mp
257 WHERE mbp.organization_id = mp.organization_id
258 and mbp.sr_instance_id = mp.sr_instance_id
259 and ((mbp.start_date between nvl(mp.data_start_date, sysdate)
260 and mp.cutoff_date
261 or mbp.end_date between nvl(mp.data_start_date,sysdate)
262 and mp.cutoff_date) or
263 (mp.data_start_date between mbp.start_date and mbp.end_date))
264 and mp.plan_id = p_plan_id
265 and mbp.adjustment_period_flag ='N'
266 order by mbp.start_date;
267
268 BEGIN
269
270 j:=1;
271
272 OPEN PERIOD_CURSOR;
273 LOOP
274 FETCH PERIOD_CURSOR into g_period_name(j);
275 EXIT WHEN PERIOD_CURSOR%NOTFOUND;
276 j := j+1;
277 END LOOP;
278
279 CLOSE PERIOD_CURSOR;
280 IF j = 1 THEN
281 v_period_count:=0;
282 ELSE
283 v_period_count:=g_period_name.last;
284 END IF;
285
286 RETURN v_period_count;
287 END check_periods;
288
289
290
291 FUNCTION inventory_value_trend(p_plan_id IN NUMBER,
292 p_instance_id IN NUMBER,
293 p_organization_id IN NUMBER,
294 p_item_id IN NUMBER
295 ) return VARCHAR2 IS
296 p_out1 NUMBER;
297 l_start_date DATE;
298 l_end_date DATE;
299 p_list varchar2(3000);
300 v_dummy number;
301 BEGIN
302
303 FOR j in 1 .. g_period_name.LAST LOOP
304 l_start_date := g_period_name(j).start_date;
305 l_end_date := g_period_name(j).end_date;
306
307 if p_organization_id is not null then
308 OPEN INV_VAL_ORG_CURSOR(p_plan_id, p_organization_id,
309 p_instance_id, p_item_id,l_start_date,l_end_date);
310 FETCH INV_VAL_ORG_CURSOR into p_out1, v_dummy, v_dummy;
311 CLOSE INV_VAL_ORG_CURSOR;
312 else
313 OPEN INV_VAL_CURSOR(p_plan_id,p_item_id,l_start_date,l_end_date);
314 FETCH INV_VAL_CURSOR into p_out1, v_dummy, v_dummy;
315 CLOSE INV_VAL_CURSOR;
316 end if;
317
318 p_list := p_list ||g_param||
319 fnd_number.number_to_canonical(nvl(p_out1,0));
320
321 END LOOP;
322 return p_list;
323 END inventory_value_trend;
324
325 PROCEDURE get_item_margin(p_plan_id IN NUMBER,
326 p_instance_id IN NUMBER,
327 p_organization_id IN NUMBER,
328 p_item_id IN NUMBER,
329 p_out1 OUT NOCOPY NUMBER,
330 p_out2 OUT NOCOPY NUMBER,
331 p_out3 OUT NOCOPY NUMBER) IS
332 v_revenue number;
333 v_cost number;
334 dummy number;
335 BEGIN
336 if p_organization_id is not null then
337 OPEN INV_VAL_ORG_CURSOR(p_plan_id, p_organization_id,
338 p_instance_id, p_item_id,null,null);
339 FETCH INV_VAL_ORG_CURSOR into dummy, v_revenue, v_cost;
340 CLOSE INV_VAL_ORG_CURSOR;
341 else
342 OPEN INV_VAL_CURSOR(p_plan_id,p_item_id,null,null);
343 FETCH INV_VAL_CURSOR into dummy, v_revenue, v_cost;
344 CLOSE INV_VAL_CURSOR;
345 end if;
346 p_out1 := v_revenue;
347 p_out2 := v_cost;
348 p_out3 := nvl(v_revenue,0) - nvl(v_cost,0);
349 END get_item_margin;
350
351 PROCEDURE get_item_margin_trend(p_plan_id IN NUMBER,
352 p_instance_id IN NUMBER,
353 p_organization_id IN NUMBER,
354 p_item_id IN NUMBER,
355 p_out1 OUT NOCOPY VARCHAR2,
356 p_out2 OUT NOCOPY VARCHAR2,
357 p_out3 OUT NOCOPY VARCHAR2) IS
358 v_revenue number;
359 v_cost number;
360 v_profit number;
361 dummy number;
362 l_start_date date;
363 l_end_date date;
364 BEGIN
365 FOR j in 1 .. g_period_name.LAST LOOP
366 l_start_date := g_period_name(j).start_date;
367 l_end_date := g_period_name(j).end_date;
368 if p_organization_id is not null then
369 OPEN INV_VAL_ORG_CURSOR(p_plan_id, p_organization_id,
370 p_instance_id, p_item_id,l_start_date,l_end_date);
371 FETCH INV_VAL_ORG_CURSOR into dummy, v_revenue, v_cost;
372 CLOSE INV_VAL_ORG_CURSOR;
373 else
374 OPEN INV_VAL_CURSOR(p_plan_id,p_item_id,l_start_date,l_end_date);
375 FETCH INV_VAL_CURSOR into dummy, v_revenue, v_cost;
376 CLOSE INV_VAL_CURSOR;
377 end if;
378
379 v_revenue := nvl(v_revenue,0);
380 v_cost := nvl(v_cost,0);
381 v_profit := v_revenue - v_cost;
382 p_out1 := p_out1 ||g_param||
383 fnd_number.number_to_canonical(v_revenue);
384 p_out2 := p_out2 ||g_param||
385 fnd_number.number_to_canonical(v_cost);
386 p_out3 := p_out3 ||g_param||
387 fnd_number.number_to_canonical(v_profit);
388
389 END LOOP;
390 END get_item_margin_trend;
391
392 PROCEDURE get_margin(p_plan_id IN NUMBER,
393 p_instance_id IN NUMBER,
394 p_organization_id IN NUMBER,
395 p_product_family_id IN NUMBER,
396 p_chart IN NUMBER,
397 p_out1 OUT NOCOPY NUMBER,
398 p_out2 OUT NOCOPY NUMBER,
399 p_out3 OUT NOCOPY NUMBER,
400 p_out4 OUT NOCOPY NUMBER,
401 p_out5 OUT NOCOPY NUMBER) IS
402
403 v_revenue number;
404 v_cost number;
405 v_production number;
406 v_purchasing number;
407 v_service number;
408 v_over_cost number;
409 v_penalty number;
410 v_inventory number;
411 v_tp_cost number;
412 v_exist boolean;
413
414 CURSOR RES_ORG_CUR IS
415 SELECT sum(nvl(overutilization_cost,0))
416 FROM msc_bis_res_summary
417 WHERE plan_id = p_plan_id
418 AND nvl(period_type,0) = 0
419 AND organization_id = p_organization_id;
420
421 CURSOR RES_CUR IS
422 SELECT sum(nvl(overutilization_cost,0))
423 FROM msc_bis_res_summary
424 WHERE plan_id = p_plan_id
425 AND nvl(period_type,0) = 0;
426
427
428 CURSOR CB_ORG_CURSOR(l_plan_id number,
429 l_org_id number,
430 l_instance_id number,
431 l_item_id number) IS
432
433 SELECT sum(nvl(production_cost,0)),
434 sum(nvl(purchasing_cost,0)),
435 sum(nvl(carrying_cost,0))
436 FROM msc_bis_inv_detail
437 WHERE organization_id = l_org_id
438 AND sr_instance_id = l_instance_id
439 AND plan_id = l_plan_id
440 AND nvl(period_type,0) = 0 --bis.mfg period changes
441 and inventory_item_id = l_item_id;
442
443 CURSOR CB_CURSOR(l_plan_id number,
444 l_item_id number) IS
445 SELECT sum(nvl(production_cost,0)),
446 sum(nvl(purchasing_cost,0)),
447 sum(nvl(carrying_cost,0))
448 FROM msc_bis_inv_detail
449 where plan_id = l_plan_id
450 AND nvl(period_type,0) = 0 --bis.mfg period changes
451 and inventory_item_id = l_item_id;
452
453 BEGIN
454
455 if p_chart = SERVICE_LEVEL then
456 v_service :=get_service_level(p_plan_id,
457 p_instance_id,
458 p_organization_id,
459 p_product_family_id, null, null, g_use_old_demand_qty);
460 else
461 if p_organization_id is not null then
462 if p_product_family_id is not null then
463 if p_chart = COST_BREAKDOWN then
464 OPEN CB_ORG_CURSOR(p_plan_id, p_organization_id,
465 p_instance_id, p_product_family_id);
466 FETCH CB_ORG_CURSOR into
467 v_production,
468 v_purchasing,
469 v_inventory;
470 CLOSE CB_ORG_CURSOR;
471 else
472 OPEN MARGIN_ORG_PF_CURSOR(p_plan_id, p_organization_id,
473 p_instance_id, p_product_family_id);
474 FETCH MARGIN_ORG_PF_CURSOR into v_revenue, v_cost,
475 v_production,
476 v_purchasing,
477 v_penalty,
478 v_inventory;
479 CLOSE MARGIN_ORG_PF_CURSOR;
480 end if;
481 else -- org is not null, item is null
482 OPEN MARGIN_ORG_CURSOR(p_plan_id, p_organization_id, p_instance_id);
483 FETCH MARGIN_ORG_CURSOR into v_revenue, v_cost,
484 v_production,
485 v_purchasing,
486 v_penalty,
487 v_inventory;
488 CLOSE MARGIN_ORG_CURSOR;
489 if p_chart = COST_BREAKDOWN then
490 OPEN RES_ORG_CUR;
491 FETCH RES_ORG_CUR INTO v_over_cost;
492 CLOSE RES_ORG_CUR;
493 v_penalty := nvl(v_penalty,0) + nvl(v_over_cost,0);
494 end if;
495 end if;
496 else -- org is null
497 if p_product_family_id is not null then
498 if p_chart = COST_BREAKDOWN then
499 OPEN CB_CURSOR(p_plan_id, p_product_family_id);
500 FETCH CB_CURSOR into
501 v_production,
502 v_purchasing,
503 v_inventory;
504 CLOSE CB_CURSOR;
505 else
506 OPEN MARGIN_PF_CURSOR(p_plan_id, p_product_family_id);
507 FETCH MARGIN_PF_CURSOR into v_revenue, v_cost,
508 v_production,
509 v_purchasing,
510 v_penalty,
511 v_inventory;
512 CLOSE MARGIN_PF_CURSOR;
513 end if;
514 else -- org is null and item is null
515 OPEN MARGIN_CURSOR(p_plan_id);
516 FETCH MARGIN_CURSOR into v_revenue, v_cost,
517 v_production,
518 v_purchasing,
519 v_penalty,
520 v_inventory;
521 CLOSE MARGIN_CURSOR;
522
523 if p_chart = COST_BREAKDOWN then
524 OPEN RES_CUR;
525 FETCH RES_CUR INTO v_over_cost;
526 CLOSE RES_CUR;
527 v_penalty := nvl(v_penalty,0) + nvl(v_over_cost,0);
528 end if;
529 end if;
530 end if;
531 end if;
532 if p_chart = COST_BREAKDOWN then
533 p_out1 := v_production;
534 p_out2 := v_purchasing;
535 p_out3 := v_penalty;
536 p_out4 := v_inventory;
537 v_tp_cost := msc_get_bis_values.get_tp_cost(p_plan_id,
538 p_instance_id, p_organization_id, p_product_family_id, null, null);
539 p_out5 := v_tp_cost;
540 elsif p_chart = SERVICE_LEVEL then
541 p_out1 := v_service;
542
543 else
544 p_out1 := v_revenue;
545 p_out2 := v_cost;
546 p_out3 := nvl(v_revenue,0) - nvl(v_cost,0);
547 end if;
548 END;
549
550 PROCEDURE get_margin_trend(p_plan_id IN NUMBER,
551 p_instance_id IN NUMBER,
552 p_organization_id IN NUMBER,
553 p_product_family_id IN NUMBER,
554 p_chart IN NUMBER,
555 p_out1 OUT NOCOPY VARCHAR2,
556 p_out2 OUT NOCOPY VARCHAR2,
557 p_out3 OUT NOCOPY VARCHAR2,
558 p_out4 OUT NOCOPY VARCHAR2,
559 p_out5 OUT NOCOPY VARCHAR2
560 ) IS
561
562 v_revenue NUMBER;
563 v_cost NUMBER;
564 v_profit NUMBER;
565 v_production number;
566 v_purchasing number;
567 v_service number;
568 v_over_cost number;
569 v_tp_cost number;
570 v_penalty number;
571 v_inventory number;
572 l_start_date DATE;
573 l_end_date DATE;
574 dummy number;
575 v_exist boolean;
576
577 CURSOR RES_ORG_DATE_CUR IS
578 SELECT sum(nvl(overutilization_cost,0))
579 FROM msc_bis_res_summary
580 WHERE plan_id = p_plan_id
581 AND organization_id = p_organization_id
582 AND nvl(period_type,0) = 0
583 AND resource_date between l_start_date and l_end_Date;
584
585 CURSOR RES_DATE_CUR IS
586 SELECT sum(nvl(overutilization_cost,0))
587 FROM msc_bis_res_summary
588 WHERE plan_id = p_plan_id
589 AND nvl(period_type,0) = 0
590 AND resource_date between l_start_date and l_end_Date;
591
592 CURSOR CB_ORG_CURSOR(l_plan_id number,
593 l_org_id number,
594 l_instance_id number,
595 l_item_id number) IS
596
597 SELECT sum(nvl(production_cost,0)),
598 sum(nvl(purchasing_cost,0)),
599 sum(nvl(carrying_cost,0))
600 FROM msc_bis_inv_detail
601 WHERE organization_id = l_org_id
602 AND sr_instance_id = l_instance_id
603 AND plan_id = l_plan_id
604 AND nvl(period_type,0) = 0 --bis.mfg period changes
605 and inventory_item_id = l_item_id
606 and detail_date between l_start_date and l_end_date;
607
608 CURSOR CB_CURSOR(l_plan_id number,
609 l_item_id number) IS
610 SELECT sum(nvl(production_cost,0)),
611 sum(nvl(purchasing_cost,0)),
612 sum(nvl(carrying_cost,0))
613 FROM msc_bis_inv_detail
614 where plan_id = l_plan_id
615 and inventory_item_id = l_item_id
616 AND nvl(period_type,0) = 0 --bis.mfg period changes
617 and detail_date between l_start_date and l_end_date;
618
619 BEGIN
620
621 FOR j in 1 .. g_period_name.LAST LOOP
622 l_start_date := g_period_name(j).start_date;
623 l_end_date := g_period_name(j).end_date;
624
625 if p_chart = SERVICE_LEVEL then
626 v_service :=get_service_level(p_plan_id,
627 p_instance_id,
628 p_organization_id,
629 p_product_family_id,
630 l_start_date,
631 l_end_date, g_use_old_demand_qty);
632 else
633 if p_chart = COST_BREAKDOWN then
634 v_tp_cost := msc_get_bis_values.get_tp_cost(p_plan_id,
635 p_instance_id, p_organization_id, p_product_family_id, l_start_date, l_end_date);
636 end if;
637 if p_organization_id is not null then
638 if p_product_family_id is not null then
639 if p_chart = COST_BREAKDOWN then
640 OPEN CB_ORG_CURSOR(p_plan_id, p_organization_id,
641 p_instance_id, p_product_family_id);
642 FETCH CB_ORG_CURSOR into
643 v_production,
644 v_purchasing,
645 v_inventory;
646 CLOSE CB_ORG_CURSOR;
647 else
648 OPEN MARGIN_ORG_PF_DATE_CURSOR(p_plan_id, p_organization_id,
649 p_instance_id, p_product_family_id,
650 l_start_date, l_end_date);
651 FETCH MARGIN_ORG_PF_DATE_CURSOR into v_revenue, v_cost,
652 v_production,
653 v_purchasing,
654 v_penalty,
655 v_inventory;
656 CLOSE MARGIN_ORG_PF_DATE_CURSOR;
657 end if;
658 else -- org is not null, item is null
659 OPEN MARGIN_ORG_DATE_CURSOR(p_plan_id,p_organization_id, p_instance_id,
660 l_start_date, l_end_date);
661 FETCH MARGIN_ORG_DATE_CURSOR into v_revenue, v_cost,
662 v_production,
663 v_purchasing,
664 v_penalty,
665 v_inventory;
666 CLOSE MARGIN_ORG_DATE_CURSOR;
667
668 if p_chart = COST_BREAKDOWN then
669 OPEN RES_ORG_DATE_CUR;
670 FETCH RES_ORG_DATE_CUR INTO v_over_cost;
671 CLOSE RES_ORG_DATE_CUR;
672 v_penalty := nvl(v_penalty,0) + nvl(v_over_cost,0);
673 end if;
674 end if;
675 else -- org is null
676 if p_product_family_id is not null then
677 if p_chart = COST_BREAKDOWN then
678 OPEN CB_CURSOR(p_plan_id, p_product_family_id);
679 FETCH CB_CURSOR into
680 v_production,
681 v_purchasing,
682 v_inventory;
683 CLOSE CB_CURSOR;
684 else
685 OPEN MARGIN_PF_DATE_CURSOR(p_plan_id, p_product_family_id,
686 l_start_date, l_end_date);
687 FETCH MARGIN_PF_DATE_CURSOR into v_revenue, v_cost,
688 v_production,
689 v_purchasing,
690 v_penalty,
691 v_inventory;
692 CLOSE MARGIN_PF_DATE_CURSOR;
693 end if;
694 else -- org is null, item is null
695 OPEN MARGIN_DATE_CURSOR(p_plan_id,l_start_date, l_end_date);
696 FETCH MARGIN_DATE_CURSOR into v_revenue, v_cost,
697 v_production,
698 v_purchasing,
699 v_penalty,
700 v_inventory;
701 CLOSE MARGIN_DATE_CURSOR;
702
703 if p_chart = COST_BREAKDOWN then
704 OPEN RES_DATE_CUR;
705 FETCH RES_DATE_CUR INTO v_over_cost;
706 CLOSE RES_DATE_CUR;
707 v_penalty := nvl(v_penalty,0) + nvl(v_over_cost,0);
708 end if;
709 end if;
710 end if;
711 end if;
712 if p_chart = MARGIN_NUMBER then
713 v_revenue := nvl(v_revenue,0);
714 v_cost := nvl(v_cost,0);
715 v_profit := v_revenue - v_cost;
716
717 p_out1 := p_out1 ||g_param||
718 fnd_number.number_to_canonical(v_revenue);
719 p_out2 := p_out2 ||g_param||
720 fnd_number.number_to_canonical(v_cost);
721 p_out3 := p_out3 ||g_param||
722 fnd_number.number_to_canonical(v_profit);
723 elsif p_chart =COST_BREAKDOWN then
724 p_out1 := p_out1 ||g_param||
725 fnd_number.number_to_canonical(nvl(v_production,0));
726 p_out2 := p_out2 ||g_param||
727 fnd_number.number_to_canonical(nvl(v_purchasing,0));
728 p_out3 := p_out3 ||g_param||
729 fnd_number.number_to_canonical(nvl(v_penalty,0));
730 p_out4 := p_out4 ||g_param||
731 fnd_number.number_to_canonical(nvl(v_inventory,0));
732 p_out5 := p_out5 ||g_param||
733 fnd_number.number_to_canonical(nvl(v_tp_cost,0));
734 elsif p_chart =SERVICE_LEVEL then
735 p_out1 := p_out1 ||g_param||
736 fnd_number.number_to_canonical(nvl(v_service,0));
737 end if;
738 END LOOP;
739
740 END;
741
742
743 PROCEDURE get_margin_by_org(p_plan_id IN NUMBER,
744 p_row_count OUT NOCOPY NUMBER,
745 p_org OUT NOCOPY VARCHAR2,
746 p_margin OUT NOCOPY VARCHAR2) IS
747
748 CURSOR MARGIN_ORG_CURSOR IS
749 SELECT msc_get_name.org_code(profit.organization_id,
750 profit.sr_instance_id),
751 SUM(NVL(profit.mds_price,0)),
752 SUM(NVL(profit.mds_cost,0))
753 FROM msc_bis_inv_detail profit
754 WHERE profit.plan_id = p_plan_id
755 AND nvl(profit.period_type,0) = 0 --bis.mfg period changes
756 GROUP BY 1;
757
758 revenue number;
759 cost number;
760 margin number;
761 org varchar2(200);
762
763 BEGIN
764 p_row_count :=0;
765 OPEN MARGIN_ORG_CURSOR;
766 LOOP
767 FETCH MARGIN_ORG_CURSOR into org, revenue, cost;
768 EXIT WHEN MARGIN_ORG_CURSOR%NOTFOUND;
769 p_row_count := p_row_count+1;
770 revenue := nvl(revenue,0);
771 cost :=nvl(cost,0);
772 if revenue = 0 Then
773 margin :=0;
774 else
775 margin := (revenue-cost)/revenue;
776 end if;
777 p_margin := p_margin || g_param ||
778 fnd_number.number_to_canonical(margin);
779 p_org := p_org ||g_param || org;
780
781 END LOOP;
782 CLOSE MARGIN_ORG_CURSOR;
783
784 END;
785
786 -- ==============================================================
787 -- Function to obtain number of sales orders
788 -- that are late for a plan
789 -- ==============================================================
790 FUNCTION late_orders(arg_plan_id IN NUMBER,
791 arg_instance_id IN NUMBER,
792 arg_organization_id IN NUMBER,
793 arg_start_date IN DATE,
794 arg_end_date IN DATE,
795 arg_inventory_item_Id IN NUMBER DEFAULT NULL,
796 arg_project_id IN NUMBER DEFAULT NULL,
797 arg_task_id IN NUMBER DEFAULT NULL,
798 arg_category_id IN NUMBER DEFAULT NULL,
799 arg_category_name IN VARCHAR2 DEFAULT NULL,
800 arg_category_set_id IN NUMBER DEFAULT NULL,
801 arg_product_family_id IN NUMBER DEFAULT NULL)
802 RETURN NUMBER IS
803 TYPE CurTyp IS REF CURSOR;
804 late_order_cursor CurTyp;
805 late_count NUMBER;
806 sql_statement varchar2(30000);
807
808 BEGIN
809
810 if arg_start_date is null and
811 arg_end_date is null and
812 arg_inventory_item_id is null and
813 arg_project_id is null and
814 arg_task_id is null and
815 arg_category_id is null and
816 arg_category_name is null and
817 arg_product_family_id is null then
818
819 sql_statement := ' SELECT sum(mbis.late_order_count) ' ||
820 ' FROM msc_late_order_mv_tab mbis' ||
821 ' WHERE mbis.plan_id = :1 ';
822 else
823 sql_statement := ' SELECT count(distinct mbis.number1) ' ||
824 ' FROM msc_exception_details mbis' ||
825 ' WHERE mbis.plan_id = :1 '||
826 ' AND mbis.exception_type in (13,14,24,26) '||
827 ' AND mbis.number1 is not null ';
828 end if;
829 sql_statement := sql_statement ||
830 construct_bis_where(true,arg_organization_id, arg_instance_id,
831 arg_inventory_item_id, arg_project_id,
832 arg_task_id, arg_category_id,arg_category_name,
833 arg_category_set_id, arg_product_family_id,
834 arg_start_date, arg_end_date);
835 OPEN late_order_cursor FOR sql_statement USING arg_plan_id,
836 g_org_id, g_instance_id, g_item_id,
837 g_project_id,g_task_id, g_category_id,
838 g_category_set_id, g_category_name,g_product_family_id,
839 g_start_date,g_end_date;
840
841
842 FETCH late_order_cursor INTO late_count;
843 CLOSE late_order_cursor;
844
845 if late_count is null then
846 late_count :=0;
847 end if;
848
849 return late_count;
850 END late_orders;
851
852 Procedure populate_plan_date(p_plan_id IN NUMBER) IS
853
854 CURSOR PERIOD_CURSOR IS
855 SELECT mbp.period_name, mbp.start_date, mbp.end_date
856 FROM msc_bis_periods mbp,
857 msc_plans mp
858 WHERE mbp.organization_id = mp.organization_id
859 and mbp.sr_instance_id = mp.sr_instance_id
860 and ((mbp.start_date between nvl(mp.data_start_date, sysdate)
861 and mp.cutoff_date
862 or mbp.end_date between nvl(mp.data_start_date,sysdate)
863 and mp.cutoff_date) or
864 (mp.data_start_date between mbp.start_date and mbp.end_date))
865 and mp.plan_id = p_plan_id
866 and mbp.adjustment_period_flag ='N'
867 order by mbp.start_date;
868
869 CURSOR PERIOD_ZERO_CURSOR IS
870 SELECT mbp.start_date
871 FROM msc_bis_periods mbp,
872 msc_plans mp
873 WHERE mbp.organization_id = mp.organization_id
874 and mbp.sr_instance_id = mp.sr_instance_id
875 and mbp.start_date < g_plan_start_date
876 and mp.plan_id = p_plan_id
877 and mbp.adjustment_period_flag ='N'
878 order by mbp.start_date desc;
879
880 BEGIN
881
882 if p_plan_id <> -1 then
883 j :=1;
884
885 OPEN PERIOD_CURSOR;
886 LOOP
887 FETCH PERIOD_CURSOR into g_period_name(j);
888 EXIT WHEN PERIOD_CURSOR%NOTFOUND;
889 j := j+1;
890 END LOOP;
891 CLOSE PERIOD_CURSOR;
892
893 g_plan_start_date := g_period_name(1).start_date;
894 g_plan_end_date := g_period_name(j-1).start_date;
895
896
897 else
898 select nvl(mp.data_start_date,sysdate), mp.cutoff_date
899 into g_plan_start_date, g_plan_end_date
900 from msc_plans mp
901 where plan_id =-1;
902
903 g_period_name(1).start_date := g_plan_start_date;
904 g_period_name(1).end_date := g_plan_end_date-1;
905 g_period_name(1).period_name := to_char(g_plan_start_date,'MON-RR');
906
907 end if;
908
909 OPEN PERIOD_ZERO_CURSOR;
910 FETCH PERIOD_ZERO_CURSOR into g_period_zero_date;
911 CLOSE PERIOD_ZERO_CURSOR;
912 END;
913
914 PROCEDURE get_period_name (p_period_list OUT NOCOPY VARCHAR2,
915 p_period_count OUT NOCOPY NUMBER) IS
916 BEGIN
917 p_period_list :=null;
918
919
920 For j in 1 .. g_period_name.last LOOP
921 p_period_list := p_period_list || g_param ||
922 g_period_name(j).period_name;
923 END LOOP;
924
925 p_period_count := g_period_name.last;
926
927
928 END;
929
930 -- ======================================================================
931 -- Function to get actual plan values for the Enterprise Plan Performance
932 -- Summary and Organization reports. If organization_id is passed in as
933 -- NULL then results for all orgs are returned
934 -- ======================================================================
935 FUNCTION get_actuals(p_plan_id IN NUMBER,
936 p_instance_id IN NUMBER,
937 p_organization_id IN NUMBER,
938 i IN NUMBER,
939 p_inventory_item_id IN NUMBER DEFAULT NULL,
940 p_project_id IN NUMBER DEFAULT NULL,
941 p_task_id IN NUMBER DEFAULT NULL,
942 p_dept_id IN NUMBER DEFAULT NULL,
943 p_res_id IN NUMBER DEFAULT NULL,
944 p_dept_class IN VARCHAR2 DEFAULT NULL,
945 p_res_group IN VARCHAR2 DEFAULT NULL,
946 p_category_id IN NUMBER DEFAULT NULL,
947 p_category_name IN VARCHAR2 DEFAULT NULL,
948 p_category_set_id IN NUMBER DEFAULT NULL,
949 p_product_family_id IN NUMBER DEFAULT NULL,
950 p_sup_id IN NUMBER DEFAULT NULL,
951 p_sup_site_id IN NUMBER DEFAULT NULL,
952 p_res_instance_id IN NUMBER DEFAULT NULL ,
953 p_res_inst_serial_number IN varchar2 DEFAULT NULL) --ds enhancement
954 RETURN NUMBER IS
955
956 kpi_cursor KPICurTyp;
957
958
959 l_value1 NUMBER := 0;
960 l_value2 NUMBER := 0;
961 l_value3 NUMBER := 0;
962 l_days number;
963 dummy number;
964 l_stat varchar2(255);
965 v_cat_name varchar2(300);
966 BEGIN
967 IF i=1 THEN
968 if p_inventory_item_id is null and
969 p_project_id is null and
970 p_task_id is null and
971 p_product_family_id is null then
972
973 if p_category_id is null and p_category_name is null then
974 sql_statement := ' SELECT '||
975 ' SUM(nvl(mbis.mds_cost,0)) '||
976 ' FROM msc_bis_inv_date_mv_tab mbis' ||
977 ' WHERE mbis.plan_id = :1 ';
978 sql_statement := sql_statement ||
979 construct_bis_where(false,p_organization_id, p_instance_id,
980 p_inventory_item_id, p_project_id, p_task_id,
981 null, null, p_category_set_id,
982 p_product_family_id);
983 else
984 if p_category_name is not null then
985 v_cat_name := '-1:'||p_category_name;
986 else
987 v_cat_name := null;
988 end if;
989 sql_statement := ' SELECT '||
990 ' SUM(nvl(mbis.mds_cost,0)) '||
991 ' FROM msc_bis_inv_cat_mv_tab mbis' ||
992 ' WHERE mbis.plan_id = :1 ';
993 sql_statement := sql_statement ||
994 construct_bis_where(false,p_organization_id, p_instance_id,
995 p_inventory_item_id, p_project_id, p_task_id,
996 -1*p_category_id,v_cat_name,
997 p_category_set_id,p_product_family_id);
998 end if;
999 else
1000
1001 sql_statement := ' SELECT '||
1002 ' SUM(nvl(mbis.mds_cost,0)) '||
1003 ' FROM msc_bis_inv_detail mbis' ||
1004 ' WHERE mbis.plan_id = :1 and nvl(mbis.period_type,0) = 0';
1005 sql_statement := sql_statement ||
1006 construct_bis_where(false,p_organization_id, p_instance_id,
1007 p_inventory_item_id, p_project_id, p_task_id,
1008 p_category_id, p_category_name,p_category_set_id,
1009 p_product_family_id);
1010 end if;
1011
1012 OPEN kpi_cursor FOR sql_statement USING
1013 p_plan_id,
1014 g_org_id, g_instance_id, g_item_id,
1015 g_project_id,g_task_id, g_category_id,
1016 g_category_set_id,g_category_name,g_product_family_id;
1017
1018 FETCH kpi_cursor INTO l_value1;
1019 CLOSE kpi_cursor;
1020
1021 IF l_value1 = 0 or l_value1 is null THEN
1022 RETURN 0;
1023
1024 ELSE
1025
1026 if p_inventory_item_id is null and
1027 p_project_id is null and
1028 p_task_id is null and
1029 p_product_family_id is null then
1030
1031 if p_category_id is null and p_category_name is null then
1032 sql_statement := ' SELECT '||
1033 ' SUM(nvl(mbis.inventory_cost,0)) '||
1034 ' FROM msc_bis_inv_date_mv_tab mbis ' ||
1035 ' WHERE mbis.plan_id = :1 '||
1036 ' AND mbis.detail_date = :7 ';
1037 sql_statement := sql_statement ||
1038 construct_bis_where(false,p_organization_id, p_instance_id,
1039 p_inventory_item_id, p_project_id, p_task_id,
1040 null, null, p_category_set_id,
1041 p_product_family_id);
1042 else
1043 sql_statement := ' SELECT '||
1044 ' SUM(nvl(mbis.inventory_cost,0)) '||
1045 ' FROM msc_bis_inv_cat_mv_tab mbis ' ||
1046 ' WHERE mbis.plan_id = :1 '||
1047 ' AND mbis.detail_date = :7 ';
1048 if p_category_name is not null then
1049 v_cat_name := '-1:'||p_category_name;
1050 else
1051 v_cat_name := null;
1052 end if;
1053
1054 sql_statement := sql_statement ||
1055 construct_bis_where(false,p_organization_id, p_instance_id,
1056 p_inventory_item_id, p_project_id, p_task_id,
1057 -1*p_category_id, v_cat_name,
1058 p_category_set_id,
1059 p_product_family_id);
1060 end if;
1061
1062
1063 else
1064 sql_statement := ' SELECT '||
1065 ' SUM(nvl(mbis.inventory_cost,0)) '||
1066 ' FROM msc_bis_inv_detail mbis ' ||
1067 ' WHERE mbis.plan_id = :1 and nvl(mbis.period_type,0) = 0 '||
1068 ' AND mbis.detail_date = :7 ';
1069
1070 sql_statement := sql_statement ||
1071 construct_bis_where(false,p_organization_id, p_instance_id,
1072 p_inventory_item_id, p_project_id, p_task_id,
1073 p_category_id, p_category_name,p_category_set_id,
1074 p_product_family_id);
1075 end if;
1076
1077
1078 OPEN kpi_cursor FOR sql_statement USING
1079 p_plan_id, g_period_zero_date, -- g_plan_start_date,
1080 g_org_id, g_instance_id, g_item_id,
1081 g_project_id,g_task_id, g_category_id,
1082 g_category_set_id, g_category_name, g_product_family_id;
1083 FETCH kpi_cursor INTO l_value2;
1084 CLOSE kpi_cursor;
1085
1086 IF l_value2 is null then
1087 l_value2 :=0;
1088 END IF;
1089
1090 OPEN kpi_cursor FOR sql_statement USING
1091 p_plan_id, g_plan_end_date,
1092 g_org_id, g_instance_id, g_item_id,
1093 g_project_id,g_task_id, g_category_id,
1094 g_category_set_id, g_category_name, g_product_family_id;
1095 FETCH kpi_cursor INTO l_value3;
1096 CLOSE kpi_cursor;
1097
1098 IF l_value3 is null then
1099 l_value3 :=0;
1100 END IF;
1101
1102 IF (l_value2+l_value3)/2 = 0 THEN
1103 RETURN 999999;
1104 ELSE
1105 l_days := g_plan_end_date - g_plan_start_date +1;
1106 if l_days = 0 then
1107 l_days :=1;
1108 end if;
1109 RETURN round(l_value1/((l_value2+l_value3)/2)*
1110 365/l_days,6);
1111 END IF;
1112 END IF;
1113
1114 ELSIF i = 2 THEN
1115
1116 if p_inventory_item_id is null and
1117 p_project_id is null and
1118 p_task_id is null and
1119 p_category_id is null and
1120 p_category_name is null and
1121 p_product_family_id is null then
1122
1123 -- refer to the materialized view directly
1124 sql_statement := ' SELECT sum(demand_count) '||
1125 ' FROM msc_demand_mv_tab mbis'||
1126 ' WHERE mbis.plan_id = :1 ';
1127 else
1128 sql_statement := ' SELECT count(*) '||
1129 ' FROM msc_demands_mv_v mbis'||
1130 ' WHERE mbis.plan_id = :1 ';
1131 end if;
1132 sql_statement := sql_statement ||
1133 construct_bis_where(false,p_organization_id, p_instance_id,
1134 p_inventory_item_id, p_project_id, p_task_id,
1135 p_category_id, p_category_name, p_category_set_id,
1136 p_product_family_id);
1137
1138 OPEN kpi_cursor FOR sql_statement USING p_plan_id,
1139 g_org_id, g_instance_id, g_item_id,
1140 g_project_id,g_task_id,g_category_id,
1141 g_category_set_id , g_category_name, g_product_family_id;
1142 FETCH kpi_cursor INTO l_value2;
1143 CLOSE kpi_cursor;
1144
1145 if l_value2 = 0 or l_value2 is null then
1146
1147 return 100;
1148 else
1149
1150 l_value1 := msc_get_bis_values.late_orders(p_plan_id,
1151 p_instance_id,p_organization_id,NULL,NULL,
1152 p_inventory_item_id, p_project_id, p_task_id,
1153 p_category_id,p_category_name,p_category_set_id,
1154 p_product_family_id);
1155
1156 IF l_value1 = 0 or l_value1 is null THEN
1157
1158 RETURN 100;
1159 ELSE
1160
1161 return (l_value2-l_value1)/l_value2*100;
1162 END IF;
1163
1164 end if;
1165 ELSIF i = 3 THEN
1166 if p_inventory_item_id is null then
1167 if p_organization_id is not null then
1168 if p_product_family_id is not null then
1169 OPEN MARGIN_ORG_PF_CURSOR(p_plan_id, p_organization_id,
1170 p_instance_id, p_product_family_id);
1171 FETCH MARGIN_ORG_PF_CURSOR into l_value1, l_value2,
1172 dummy,dummy, dummy, dummy;
1173 CLOSE MARGIN_ORG_PF_CURSOR;
1174 else
1175 OPEN MARGIN_ORG_CURSOR(p_plan_id, p_organization_id, p_instance_id);
1176 FETCH MARGIN_ORG_CURSOR INTO l_value1, l_value2,
1177 dummy,dummy, dummy, dummy;
1178 CLOSE MARGIN_ORG_CURSOR;
1179 end if;
1180 else
1181 if p_product_family_id is not null then
1182 OPEN MARGIN_PF_CURSOR(p_plan_id, p_product_family_id);
1183 FETCH MARGIN_PF_CURSOR into l_value1, l_value2,
1184 dummy,dummy, dummy, dummy;
1185 CLOSE MARGIN_PF_CURSOR;
1186 else
1187 OPEN MARGIN_CURSOR(p_plan_id);
1188 FETCH MARGIN_CURSOR INTO l_value1, l_value2,
1189 dummy,dummy, dummy, dummy;
1190 CLOSE MARGIN_CURSOR;
1191 end if;
1192 end if;
1193 else -- item_id is not null
1194 if p_organization_id is not null then
1195 OPEN INV_VAL_ORG_CURSOR(p_plan_id, p_organization_id,
1196 p_instance_id, p_inventory_item_id,null,null);
1197 FETCH INV_VAL_ORG_CURSOR into dummy, l_value1, l_value2;
1198 CLOSE INV_VAL_ORG_CURSOR;
1199 else
1200 OPEN INV_VAL_CURSOR(p_plan_id,p_inventory_item_id,null,null);
1201 FETCH INV_VAL_CURSOR into dummy, l_value1, l_value2;
1202 CLOSE INV_VAL_CURSOR;
1203 end if;
1204 end if;
1205 IF l_value1 = 0 THEN
1206 RETURN 0;
1207 ELSE
1208 RETURN ((l_value1-l_value2)/l_value1)*100;
1209 END IF;
1210
1211 ELSIF i = UTILIZATION or i = UTILIZATION2 THEN
1212
1213 if p_res_instance_id = -111 then
1214 if p_sup_id is null then
1215 if i = UTILIZATION then
1216 sql_statement := ' SELECT avg(nvl(res.utilization,0)) ';
1217 else
1218 sql_statement := ' SELECT sum(nvl(res.UTIL_BY_WT_VOL,0)*nvl(res.batch_count,0)) /sum(nvl(res.batch_count,1))';
1219 end if;
1220 if p_dept_class is null and
1221 p_res_group is null then
1222 sql_statement := sql_statement ||
1223 ' FROM msc_bis_res_summary res ' ||
1224 ' WHERE res.plan_id = :1 AND nvl(res.period_type,0) = 0 ';
1225 else
1226 sql_statement := sql_statement ||
1227 ' FROM msc_department_resources mdr, '||
1228 ' msc_bis_res_summary res ' ||
1229 ' WHERE res.plan_id = :1 AND nvl(res.period_type,0) = 0 ' ||
1230 ' AND mdr.department_id = res.department_id ' ||
1231 ' AND mdr.resource_id = res.resource_id ' ||
1232 ' AND mdr.plan_id = res.plan_id ' ||
1233 ' AND mdr.sr_instance_id = res.sr_instance_id ' ||
1234 ' AND mdr.organization_id = res.organization_id ';
1235 end if;
1236 if i = UTILIZATION2 then
1237 sql_statement := sql_statement || 'AND nvl(res.UTIL_BY_WT_VOL,0) > 0 ';
1238 end if;
1239 sql_statement := sql_statement ||
1240 construct_res_where(p_organization_id, p_instance_id, p_dept_id,
1241 p_res_id, p_res_group, p_dept_class);
1242
1243 OPEN kpi_cursor FOR sql_statement USING p_plan_id,
1244 g_org_id, g_instance_id,
1245 g_dept_id,g_res_id, g_dept_class, g_res_group ;
1246
1247 FETCH kpi_cursor INTO l_value1;
1248 CLOSE kpi_cursor;
1249
1250 RETURN (l_value1*100);
1251
1252 else
1253 sql_statement := ' SELECT avg(nvl(sup.utilization,0)) '||
1254 ' FROM msc_bis_supplier_summary sup ' ||
1255 ' WHERE sup.plan_id = :1 ';
1256
1257 sql_statement := sql_statement ||
1258 construct_sup_where(p_organization_id,
1259 p_instance_id,
1260 p_inventory_item_id,
1261 p_sup_id,
1262 p_sup_site_id);
1263 -- for i in 1..7 loop
1264 -- dbms_output.put_line(' '|| substr(sql_statement,200*(i-1)+1,200*i));
1265 -- end loop;
1266
1267 OPEN kpi_cursor FOR sql_statement USING p_plan_id,
1268 -- g_org_id,
1269 g_instance_id,
1270 g_item_id,g_sup_id, g_sup_site_id;
1271
1272 FETCH kpi_cursor INTO l_value1;
1273 CLOSE kpi_cursor;
1274
1275 RETURN (l_value1*100);
1276 end if;
1277 elsif p_res_instance_id <> -111 then
1278 if p_sup_id is null then
1279 if i = UTILIZATION then
1280 sql_statement := ' SELECT avg(nvl(res.utilization,0)) ';
1281 else
1282 -- sql_statement := ' SELECT avg(nvl(res.UTIL_BY_WT_VOL,0)) ';
1283 sql_statement := ' SELECT sum(nvl(res.UTIL_BY_WT_VOL,0)*nvl(res.batch_count,0)) /sum(nvl(res.batch_count,1))';
1284 end if;
1285
1286 if p_dept_class is null and
1287 p_res_group is null then
1288 sql_statement := sql_statement ||
1289 ' FROM msc_bis_res_inst_summary res' ||
1290 ' WHERE res.plan_id = :1 AND nvl(res.period_type,0) = 0 ';
1291 else
1292 sql_statement := sql_statement ||
1293 ' FROM msc_dept_res_instances mdr, '||
1294 ' msc_bis_res_inst_summary res ' ||
1295 ' WHERE res.plan_id = :1 AND nvl(res.period_type,0) = 0 ' ||
1296 ' AND mdr.department_id = res.department_id ' ||
1297 ' AND mdr.resource_id = res.resource_id ' ||
1298 ' AND mdr.plan_id = res.plan_id ' ||
1299 ' AND mdr.sr_instance_id = res.sr_instance_id ' ||
1300 ' AND nvl(mdr.serial_number , '||''''||'-111'||''''||') = nvl(res.serial_number ,'||''''||'-111'||''''||') '||
1301 ' AND mdr.RES_INSTANCE_ID = res.RES_INSTANCE_ID '||
1302 ' AND mdr.organization_id = res.organization_id ';
1303 end if;
1304 if i = UTILIZATION2 then
1305 sql_statement := sql_statement || 'AND nvl(res.UTIL_BY_WT_VOL,0) > 0 ';
1306 end if;
1307 sql_statement := sql_statement ||
1308 construct_res_instance_where(p_organization_id, p_instance_id, p_dept_id,
1309 p_res_id, p_res_group, p_dept_class ,
1310 p_res_instance_id => p_res_instance_id ,
1311 p_res_inst_serial_number => p_res_inst_serial_number);
1312
1313 OPEN kpi_cursor FOR sql_statement USING p_plan_id,
1314 g_org_id, g_instance_id,
1315 g_dept_id,g_res_id, g_res_instance_id ,g_res_inst_serial_number ;
1316
1317 FETCH kpi_cursor INTO l_value1;
1318 CLOSE kpi_cursor;
1319
1320 RETURN (l_value1*100);
1321
1322 else
1323 sql_statement := ' SELECT avg(nvl(sup.utilization,0)) '||
1324 ' FROM msc_bis_supplier_summary sup ' ||
1325 ' WHERE sup.plan_id = :1 ';
1326
1327 sql_statement := sql_statement ||
1328 construct_sup_where(p_organization_id,
1329 p_instance_id,
1330 p_inventory_item_id,
1331 p_sup_id,
1332 p_sup_site_id);
1333 -- for i in 1..7 loop
1334 -- dbms_output.put_line(' '|| substr(sql_statement,200*(i-1)+1,200*i));
1335 -- end loop;
1336
1337 OPEN kpi_cursor FOR sql_statement USING p_plan_id,
1338 -- g_org_id,
1339 g_instance_id,
1340 g_item_id,g_sup_id, g_sup_site_id;
1341
1342 FETCH kpi_cursor INTO l_value1;
1343 CLOSE kpi_cursor;
1344
1345 RETURN (l_value1*100);
1346 end if;
1347 end if;
1348 END IF;
1349 END get_actuals;
1350
1351 -- ======================================================================
1352 -- Function to get actual trend values for the Enterprise Plan Performance
1353 -- Summary and Organization reports. If organization_id is passed in as
1354 -- NULL then results for all orgs are returned
1355 -- ======================================================================
1356 PROCEDURE get_trend_actuals(p_plan_id IN NUMBER,
1357 p_instance_id IN NUMBER,
1358 p_org_id IN NUMBER,
1359 i IN NUMBER,
1360 p_inventory_item_id IN NUMBER DEFAULT NULL,
1361 p_project_id IN NUMBER DEFAULT NULL,
1362 p_task_id IN NUMBER DEFAULT NULL,
1363 p_dept_id IN NUMBER DEFAULT NULL,
1364 p_res_id IN NUMBER DEFAULT NULL,
1365 p_dept_class IN VARCHAR2 DEFAULT NULL,
1366 p_res_group IN VARCHAR2 DEFAULT NULL,
1367 p_category_id IN NUMBER DEFAULT NULL,
1368 p_category_name IN VARCHAR2 DEFAULT NULL,
1369 p_category_set_id IN NUMBER DEFAULT NULL,
1370 p_product_family_id IN NUMBER DEFAULT NULL,
1371 p_sup_id IN NUMBER DEFAULT NULL,
1372 p_sup_site_id IN NUMBER DEFAULT NULL,
1373 p_value_string OUT NOCOPY VARCHAR2 ,
1374 p_res_instance_id IN NUMBER DEFAULT NULL ,
1375 p_res_inst_serial_number IN varchar2 DEFAULT NULL --ds enhancement
1376 ) IS
1377 kpi_cursor KPICurTyp;
1378
1379 l_value1 NUMBER := 0;
1380 l_value2 NUMBER := 0;
1381 l_value3 NUMBER := 0;
1382 dummy number;
1383 l_start_date DATE;
1384 l_end_date DATE;
1385 l_begin_inv_date DATE;
1386
1387 l_value NUMBER;
1388 inv_statement varchar2(1000);
1389
1390 BEGIN
1391
1392 IF i = 1 THEN
1393
1394 sql_statement := ' SELECT '||
1395 ' SUM(nvl(mds_cost,0)) '||
1396 ' FROM msc_bis_inv_detail mbis' ||
1397 ' WHERE mbis.plan_id = :1 '||
1398 ' AND mbis.detail_date between :7 AND :8 and nvl(mbis.period_type,0) = 0 ';
1399
1400 sql_statement := sql_statement ||
1401 construct_bis_where(false,p_org_id, p_instance_id,
1402 p_inventory_item_id, p_project_id, p_task_id,
1403 p_category_id,p_category_name, p_category_set_id,
1404 p_product_family_id);
1405 ELSIF i =2 THEN
1406 sql_statement := ' SELECT count(*) ' ||
1407 ' FROM msc_demands_mv_v mbis' ||
1408 ' WHERE mbis.plan_id = :1 ' ||
1409 ' AND mbis.using_assembly_demand_date '||
1410 ' BETWEEN :7 AND :8 ';
1411
1412 sql_statement := sql_statement ||
1413 construct_bis_where(false, p_org_id, p_instance_id,
1414 p_inventory_item_id,
1415 p_project_id, p_task_id,
1416 p_category_id, p_category_name,p_category_set_id,
1417 p_product_family_id);
1418
1419 ELSIF i= UTILIZATION or i = UTILIZATION2 THEN
1420 if p_res_instance_id = -111 then
1421 if (p_sup_id is null ) then
1422 if i = UTILIZATION then
1423 sql_statement := ' SELECT avg(nvl(res.utilization,0)) ';
1424 else
1425 sql_statement := ' SELECT sum(nvl(res.UTIL_BY_WT_VOL,0)*nvl(res.batch_count,0)) /sum(nvl(res.batch_count,1))';
1426 end if;
1427 if p_dept_class is null and
1428 p_res_group is null then
1429 sql_statement := sql_statement ||
1430 ' FROM msc_bis_res_summary res ' ||
1431 ' WHERE res.plan_id = :1 AND nvl(res.period_type,0) = 0 '||
1432 ' AND res.resource_date '||
1433 ' between :8 and :9 ';
1434 else
1435 sql_statement := sql_statement ||
1436 ' FROM msc_department_resources mdr, '||
1437 ' msc_bis_res_summary res ' ||
1438 ' WHERE res.plan_id = :1 AND nvl(res.period_type,0) = 0 ' ||
1439 ' AND res.resource_date '||
1440 ' between :8 and :9 ' ||
1441 ' AND mdr.department_id = res.department_id ' ||
1442 ' AND mdr.resource_id = res.resource_id ' ||
1443 ' AND mdr.plan_id = res.plan_id ' ||
1444 ' AND mdr.sr_instance_id = res.sr_instance_id ' ||
1445 ' AND mdr.organization_id = res.organization_id ';
1446 end if;
1447 if i = UTILIZATION2 then
1448 sql_statement := sql_statement || 'AND nvl(res.UTIL_BY_WT_VOL,0) > 0 ';
1449 end if;
1450 sql_statement := sql_statement ||
1451 construct_res_where(p_org_id, p_instance_id, p_dept_id,
1452 p_res_id, p_res_group, p_dept_class);
1453
1454 else
1455 sql_statement := ' SELECT avg(nvl(sup.utilization,0)) '||
1456 ' FROM msc_bis_supplier_summary sup ' ||
1457 ' WHERE sup.plan_id = :1 ' ||
1458 ' AND sup.detail_date ' ||
1459 ' between :8 and :9 ' ;
1460
1461 -- dbms_output.put_line(substr(sql_statement,1,200));
1462 -- dbms_output.put_line(substr(sql_statement,201,400));
1463 sql_statement := sql_statement ||
1464 construct_sup_where(p_org_id,
1465 p_instance_id,
1466 p_inventory_item_id,
1467 p_sup_id,
1468 p_sup_site_id);
1469 end if;
1470 elsif p_res_instance_id <> -111 then
1471 if (p_sup_id is null ) then
1472 if i = UTILIZATION then
1473 sql_statement := ' SELECT avg(nvl(res.utilization,0)) ';
1474 else
1475 sql_statement := ' SELECT sum(nvl(res.UTIL_BY_WT_VOL,0)*nvl(res.batch_count,0)) /sum(nvl(res.batch_count,1))';
1476 end if;
1477 if p_dept_class is null and
1478 p_res_group is null then
1479 sql_statement := sql_statement ||
1480 ' FROM msc_bis_res_inst_summary res ' ||
1481 ' WHERE res.plan_id = :1 AND nvl(res.period_type,0) = 0 '||
1482 ' AND res.resource_inst_date '||
1483 ' between :8 and :9 ';
1484 else
1485 sql_statement := sql_statement ||
1486 ' msc_dept_res_instances mdr, '||
1487 ' msc_bis_res_inst_summary res ' ||
1488 ' WHERE res.plan_id = :1 AND nvl(res.period_type,0) = 0 ' ||
1489 ' AND res.resource_inst_date '||
1490 ' between :8 and :9 ' ||
1491 ' AND mdr.department_id = res.department_id ' ||
1492 ' AND mdr.resource_id = res.resource_id ' ||
1493 ' AND mdr.plan_id = res.plan_id ' ||
1494 ' AND mdr.sr_instance_id = res.sr_instance_id ' ||
1495 ' AND nvl(mdr.serial_number , '||''''||'-111'||''''||') = nvl(res.serial_number ,'||''''||'-111'||''''||') '||
1496 ' AND mdr.RES_INSTANCE_ID = res.RES_INSTANCE_ID ' ||
1497 ' AND mdr.organization_id = res.organization_id ';
1498 end if;
1499
1500 if i = UTILIZATION2 then
1501 sql_statement := sql_statement || 'AND nvl(res.UTIL_BY_WT_VOL,0) > 0 ';
1502 end if;
1503 sql_statement := sql_statement ||
1504 construct_res_instance_where(p_org_id, p_instance_id, p_dept_id,
1505 p_res_id, p_res_group, p_dept_class
1506 ,p_res_instance_id => p_res_instance_id ,
1507 p_res_inst_serial_number => p_res_inst_serial_number);
1508
1509 else
1510 sql_statement := ' SELECT avg(nvl(sup.utilization,0)) '||
1511 ' FROM msc_bis_supplier_summary sup ' ||
1512 ' WHERE sup.plan_id = :1 ' ||
1513 ' AND sup.detail_date ' ||
1514 ' between :8 and :9 ' ;
1515
1516 -- dbms_output.put_line(substr(sql_statement,1,200));
1517 -- dbms_output.put_line(substr(sql_statement,201,400));
1518 sql_statement := sql_statement ||
1519 construct_sup_where(p_org_id,
1520 p_instance_id,
1521 p_inventory_item_id,
1522 p_sup_id,
1523 p_sup_site_id);
1524
1525 end if;
1526 end if;
1527 END IF;
1528
1529 For j in 1..g_period_name.LAST LOOP
1530 --dbms_output.put_line('in for loop');
1531
1532 l_start_date := g_period_name(j).start_date;
1533 l_end_date := g_period_name(j).end_date;
1534 --dbms_output.put_line(to_char(l_start_date));
1535 --dbms_output.put_line(to_char(l_end_date));
1536 IF i =1 THEN
1537
1538 OPEN kpi_cursor FOR sql_statement USING
1539 p_plan_id, l_start_date, l_end_date,
1540 g_org_id, g_instance_id, g_item_id,
1541 g_project_id,g_task_id, g_category_id,
1542 g_category_set_id,g_category_name, g_product_family_id;
1543
1544 FETCH kpi_cursor INTO l_value1;
1545 CLOSE kpi_cursor;
1546
1547 IF l_value1 = 0 OR l_value1 is null THEN
1548 l_value := 0;
1549
1550 ELSE
1551
1552
1553 inv_statement := ' SELECT '||
1554 ' SUM(nvl(mbis.inventory_cost,0)) '||
1555 ' FROM msc_bis_inv_detail mbis' ||
1556 ' WHERE mbis.plan_id = :1 '||
1557 ' AND mbis.detail_date =:7 and nvl(mbis.period_type,0) = 0 ';
1558
1559 inv_statement := inv_statement ||
1560 construct_bis_where(false,p_org_id, p_instance_id,
1561 p_inventory_item_id, p_project_id, p_task_id,
1562 p_category_id, p_category_name,p_category_set_id,
1563 p_product_family_id);
1564 if j=1 THEN
1565 l_begin_inv_date := g_period_zero_date;
1566 else
1567 l_begin_inv_date :=g_period_name(j-1).start_date;
1568 end if;
1569
1570 OPEN kpi_cursor FOR inv_statement USING
1571 p_plan_id, l_begin_inv_date,
1572 g_org_id, g_instance_id, g_item_id,
1573 g_project_id,g_task_id,g_category_id,
1574 g_category_set_id, g_category_name,g_product_family_id;
1575
1576 FETCH kpi_cursor INTO l_value2;
1577 CLOSE kpi_cursor;
1578
1579 IF l_value2 is null THEN
1580 l_value2 :=0;
1581 END IF;
1582
1583 OPEN kpi_cursor FOR inv_statement USING
1584 p_plan_id, l_start_date,
1585 g_org_id, g_instance_id, g_item_id,
1586 g_project_id,g_task_id,g_category_id,
1587 g_category_set_id, g_category_name, g_product_family_id;
1588
1589 FETCH kpi_cursor INTO l_value3;
1590 CLOSE kpi_cursor;
1591
1592 IF l_value3 is null THEN
1593 l_value3 :=0;
1594 END IF;
1595
1596 IF ((l_value2+l_value3)/2) = 0 THEN
1597 l_value := 999999;
1598 ELSE
1599 l_value :=
1600 round(l_value1/((l_value2+l_value3)/2)
1601 *365/(l_end_date -l_start_date +1),6);
1602 END IF;
1603 END IF;
1604
1605
1606 ELSIF i = 2 THEN
1607
1608 OPEN kpi_cursor FOR sql_statement USING p_plan_id,
1609 l_start_date, l_end_date,
1610 g_org_id, g_instance_id, g_item_id,
1611 g_project_id,g_task_id,g_category_id,
1612 g_category_set_id, g_category_name, g_product_family_id;
1613
1614 FETCH kpi_cursor INTO l_value2;
1615 CLOSE kpi_cursor;
1616
1617 if l_value2 = 0 or l_value2 is null then
1618 l_value :=100;
1619
1620 else
1621 l_value1 :=msc_get_bis_values.late_orders(
1622 p_plan_id,p_instance_id,p_org_id,
1623 l_start_date,l_end_date,
1624 p_inventory_item_id, p_project_id, p_task_id,
1625 p_category_id,p_category_name,
1626 p_category_set_id,p_product_family_id);
1627
1628 IF l_value1 = 0 THEN
1629 l_value := 100;
1630 ELSE
1631 l_value :=((l_value2-l_value1)/l_value2 * 100);
1632 END IF;
1633 end if;
1634
1635 ELSIF i = 3 THEN
1636 if p_inventory_item_id is null then
1637 if p_org_id is not null then
1638 if p_product_family_id is not null then
1639 OPEN MARGIN_ORG_PF_DATE_CURSOR(p_plan_id, p_org_id,
1640 p_instance_id, p_product_family_id,
1641 l_start_date, l_end_date);
1642 FETCH MARGIN_ORG_PF_DATE_CURSOR into l_value1, l_value2,
1643 dummy,dummy, dummy, dummy;
1644 CLOSE MARGIN_ORG_PF_DATE_CURSOR;
1645 else
1646 OPEN MARGIN_ORG_DATE_CURSOR(p_plan_id,p_org_id, p_instance_id,
1647 l_start_date, l_end_date);
1648 FETCH MARGIN_ORG_DATE_CURSOR INTO l_value1, l_value2,
1649 dummy,dummy, dummy, dummy;
1650 CLOSE MARGIN_ORG_DATE_CURSOR;
1651 end if;
1652 else
1653 if p_product_family_id is not null then
1654 OPEN MARGIN_PF_DATE_CURSOR(p_plan_id, p_product_family_id,
1655 l_start_date, l_end_date);
1656 FETCH MARGIN_PF_DATE_CURSOR into l_value1, l_value2,
1657 dummy,dummy, dummy, dummy;
1658 CLOSE MARGIN_PF_DATE_CURSOR;
1659 else
1660 OPEN MARGIN_DATE_CURSOR(p_plan_id,l_start_date, l_end_date);
1661 FETCH MARGIN_DATE_CURSOR INTO l_value1, l_value2,
1662 dummy,dummy, dummy, dummy;
1663 CLOSE MARGIN_DATE_CURSOR;
1664 end if;
1665 end if;
1666 else -- item is not null
1667 if p_org_id is not null then
1668 OPEN INV_VAL_ORG_CURSOR(p_plan_id, p_org_id,
1669 p_instance_id, p_inventory_item_id,
1670 l_start_date, l_end_date);
1671 FETCH INV_VAL_ORG_CURSOR into dummy, l_value1, l_value2;
1672 CLOSE INV_VAL_ORG_CURSOR;
1673 else
1674 OPEN INV_VAL_CURSOR(p_plan_id,p_inventory_item_id,
1675 l_start_date, l_end_date);
1676 FETCH INV_VAL_CURSOR into dummy, l_value1, l_value2;
1677 CLOSE INV_VAL_CURSOR;
1678 end if;
1679 end if;
1680 l_value1 := nvl(l_value1,0);
1681 l_value2 := nvl(l_value2,0);
1682 IF l_value1 = 0 THEN
1683 l_value := 0;
1684 ELSE
1685 l_value := ((l_value1-l_value2)/l_value1)*100;
1686 END IF;
1687
1688 ELSIF i = 4 or i = 9 THEN
1689
1690 if (p_sup_id is null) then
1691 OPEN kpi_cursor FOR sql_statement USING p_plan_id,
1692 l_start_date, l_end_date,g_org_id, g_instance_id,
1693 -- g_dept_id,g_res_id, g_res_instance_id , g_res_inst_serial_number ;
1694 -- updated for bug 6046690
1695 g_dept_id,g_res_id, g_dept_class , g_res_group ;
1696 else
1697 --dbms_output.put_line(g_item_id|| ' ' ||g_sup_id || ' ' ||g_sup_site_id);
1698 OPEN kpi_cursor FOR sql_statement USING p_plan_id,
1699 l_start_date, l_end_date, --g_org_id,
1700 g_instance_id,
1701 g_item_id,g_sup_id, g_sup_site_id;
1702 --dbms_output.put_line('after open');
1703 end if;
1704
1705 FETCH kpi_cursor INTO l_value1;
1706 CLOSE kpi_cursor;
1707
1708 IF l_value1 is null THEN
1709 l_value1 :=0;
1710 END IF;
1711
1712 l_value := l_value1 * 100;
1713
1714 END IF;
1715
1716 l_value :=round(l_value,6);
1717 p_value_string := p_value_string ||g_param ||
1718 fnd_number.number_to_canonical(l_value);
1719
1720 END LOOP;
1721 --exception
1722 --when others then
1723 --dbms_output.put_line(sqlerrm);
1724
1725
1726 END get_trend_actuals;
1727
1728 FUNCTION get_targets(p_chart_type IN NUMBER, p_instance_id IN NUMBER,
1729 p_org_id IN NUMBER,
1730 p_time_level varchar2 DEFAULT NULL) RETURN NUMBER IS
1731
1732 l_target NUMBER;
1733 v_measure varchar2(10);
1734 v_target_level varchar2(10);
1735
1736 CURSOR C1 IS
1737 SELECT t.target
1738 FROM msc_bis_targets t,
1739 msc_bis_target_levels tl,
1740 msc_bis_performance_measures m,
1741 msc_bis_business_plans mbp
1742 WHERE t.target_level_id = tl.target_level_id
1743 and t.sr_instance_id = p_instance_id
1744 and tl.sr_instance_id = p_instance_id
1745 and m.sr_instance_id = p_instance_id
1746 and mbp.sr_instance_id = p_instance_id
1747 AND m.measure_id = tl.measure_id
1748 AND m.measure_short_name = v_measure
1749 and mbp.short_name = 'STANDARD'
1750 AND t.business_plan_id = mbp.business_plan_id
1751 and tl.target_level_short_name = v_target_level
1752 AND t.org_level_value_id = decode(t.org_level_value_id,-1,-1,p_org_id)
1753 AND t.time_level_value_id = nvl(p_time_level, t.time_level_value_id);
1754
1755 BEGIN
1756
1757 IF p_chart_type = INVENTORY_TURNS then
1758 v_measure := 'MRPEPPIT';
1759 IF p_org_id is null THEN
1760 v_target_level := 'MRPITALL';
1761 ELSE
1762 v_target_level := 'MRPITORG';
1763 END IF;
1764 ELSIF p_chart_type = ONTIME_DELIVERY then
1765 v_measure := 'MRPEPPOT';
1766 IF p_org_id is null THEN
1767 v_target_level := 'MRPOTALL';
1768 ELSE
1769 v_target_level := 'MRPOTORG';
1770 END IF;
1771 ELSIF p_chart_type = MARGIN_PERCENT then
1772 v_measure := 'MRPEPPGM';
1773 IF p_org_id is null THEN
1774 v_target_level := 'MRPGMALL';
1775 ELSE
1776 v_target_level := 'MRPGMORG';
1777 END IF;
1778 ELSIF p_chart_type = UTILIZATION or p_chart_type = UTILIZATION2 then
1779 v_measure := 'MRPEPPPU';
1780 IF p_org_id is null THEN
1781 v_target_level := 'MRPPUALL';
1782 ELSE
1783 v_target_level := 'MRPPUORG';
1784 END IF;
1785 END IF;
1786
1787 OPEN C1;
1788 FETCH C1 INTO l_target;
1789 CLOSE C1;
1790
1791 RETURN l_target;
1792
1793 EXCEPTION
1794
1795 WHEN NO_DATA_FOUND THEN
1796
1797 return 0;
1798
1799 END get_targets;
1800
1801 FUNCTION get_targets_trend(p_chart_type IN NUMBER, p_instance_id IN NUMBER,
1802 p_org_id IN NUMBER) RETURN VARCHAR2 IS
1803
1804 l_target NUMBER;
1805 v_time_level varchar2(30);
1806 l_target_list varchar2(500);
1807
1808 BEGIN
1809
1810 FOR j in 1 .. g_period_name.LAST LOOP
1811 v_time_level := 'Accounting+'||g_period_name(j).period_name;
1812 l_target :=
1813 get_targets(p_chart_type, p_instance_id,p_org_id,v_time_level);
1814
1815 l_target_list := l_target_list ||g_param||
1816 fnd_number.number_to_canonical(nvl(l_target,0));
1817 END LOOP;
1818
1819 return l_target_list;
1820 EXCEPTION
1821
1822 WHEN NO_DATA_FOUND THEN
1823
1824 return 0;
1825
1826 END get_targets_trend;
1827
1828
1829 FUNCTION construct_res_where(p_organization_id number,
1830 p_instance_id number,
1831 p_dept_id number,
1832 p_res_id number,
1833 p_res_group varchar2,
1834 p_dept_class varchar2,
1835 p_start_date date default null,
1836 p_end_date date default null) RETURN varchar2 IS
1837 where_stat varchar2(1000);
1838
1839 BEGIN
1840 if p_organization_id is not null then
1841 where_stat :=
1842 ' AND res.organization_id = :2 ' ||
1843 ' AND res.sr_instance_id = :3 ';
1844 g_org_id := p_organization_id;
1845 g_instance_id := p_instance_id;
1846 else
1847 where_stat := where_stat ||
1848 ' AND :2 = :3 ';
1849 g_org_id := -1;
1850 g_instance_id := -1;
1851 end if;
1852
1853 if p_dept_id is not null then
1854 where_stat := where_stat ||
1855 ' AND res.department_id = :4 ';
1856 g_dept_id := p_dept_id;
1857 else
1858 where_stat := where_stat ||
1859 ' AND :4 = -1 ';
1860 g_dept_id := -1;
1861 end if;
1862
1863 if p_res_id is not null then
1864 where_stat := where_stat ||
1865 ' AND res.resource_id = :5 ';
1866 g_res_id := p_res_id;
1867 else
1868 where_stat := where_stat ||
1869 ' AND :5 = -1 ';
1870 g_res_id := -1;
1871 end if;
1872
1873 if p_dept_class = '@@@' then
1874 where_stat := where_stat ||
1875 ' AND mdr.department_class is null '||
1876 ' AND res.resource_id <> -1' ||
1877 ' AND :a = ''-1'' ';
1878 g_dept_class := '-1';
1879
1880 elsif p_dept_class is not null then
1881 where_stat := where_stat ||
1882 ' AND mdr.department_class = :a ';
1883 g_dept_class := p_dept_class;
1884 else
1885
1886 where_stat := where_stat ||
1887 ' AND :a = ''-1'' ';
1888 g_dept_class := '-1';
1889 end if;
1890
1891 if p_res_group = '@@@' then
1892 where_stat := where_stat ||
1893 ' AND mdr.resource_group_name is null '||
1894 ' AND res.resource_id <> -1 ' ||
1895 ' AND :b = ''-1'' ';
1896 g_res_group := '-1';
1897 elsif p_res_group is not null then
1898 where_stat := where_stat ||
1899 ' AND mdr.resource_group_name = :b ';
1900 g_res_group := p_res_group;
1901 else
1902 where_stat := where_stat ||
1903 ' AND :b = ''-1'' ';
1904 g_res_group := '-1';
1905 end if;
1906 return where_stat;
1907
1908 END;
1909
1910 /*satyagi ds enhancement :--------------------------------------------------------------------------------*/
1911
1912 FUNCTION construct_res_instance_where(p_organization_id number,
1913 p_instance_id number,
1914 p_dept_id number,
1915 p_res_id number,
1916 p_res_group varchar2 ,
1917 p_dept_class varchar2 ,
1918 p_start_date date default null,
1919 p_end_date date default null ,
1920 p_res_instance_id number ,
1921 p_res_inst_serial_number varchar2) RETURN varchar2 IS
1922 where_stat varchar2(1000);
1923 BEGIN
1924 if p_organization_id is not null then
1925 where_stat :=
1926 ' AND res.organization_id = :2 ' ||
1927 ' AND res.sr_instance_id = :3 ';
1928 g_org_id := p_organization_id;
1929 g_instance_id := p_instance_id;
1930 else
1931 where_stat := where_stat ||
1932 ' AND :2 = :3 ';
1933 g_org_id := -1;
1934 g_instance_id := -1;
1935 end if;
1936
1937 if p_dept_id is not null then
1938 where_stat := where_stat ||
1939 ' AND res.department_id = :4 ';
1940 g_dept_id := p_dept_id;
1941 else
1942 where_stat := where_stat ||
1943 ' AND :4 = -1 ';
1944 g_dept_id := -1;
1945 end if;
1946
1947 if p_res_id is not null then
1948 where_stat := where_stat ||
1949 ' AND res.resource_id = :5 ';
1950 g_res_id := p_res_id;
1951 else
1952 where_stat := where_stat ||
1953 ' AND :5 = -1 ';
1954 g_res_id := -1;
1955 end if;
1956
1957 if p_res_instance_id is not null then
1958 where_stat := where_stat ||
1959 ' AND res.res_instance_id = :8 ';
1960 g_res_instance_id := p_res_instance_id;
1961 else
1962 where_stat := where_stat ||
1963 ' AND :8 = -1 ';
1964 g_res_instance_id := -1;
1965 end if;
1966
1967 if p_res_inst_serial_number = '-111' then
1968 where_stat := where_stat ||
1969 ' AND nvl(res.serial_number ,'||''''||'-111'||''''||')'||' = :9 ';
1970 g_res_inst_serial_number := p_res_inst_serial_number ;
1971 else
1972 where_stat := where_stat ||
1973 ' AND res.serial_number = :9 ';
1974 g_res_inst_serial_number := p_res_inst_serial_number ;
1975 end if;
1976
1977 return where_stat;
1978
1979 END;
1980
1981 /*--------------------------------------------------------------------------------satyagi ds enhancement :*/
1982
1983 FUNCTION construct_bis_where(p_date boolean,
1984 p_organization_id number,
1985 p_instance_id number,
1986 p_inventory_item_id number,
1987 p_project_id number,
1988 p_task_id number,
1989 p_category_id number,
1990 p_category_name varchar2,
1991 p_category_set_id number,
1992 p_product_family_id number,
1993 p_start_date date default null,
1994 p_end_date date default null)
1995 RETURN varchar2 IS
1996 where_stat varchar2(2000);
1997
1998 BEGIN
1999 if p_organization_id is not null then
2000 where_stat :=
2001 ' AND mbis.organization_id = :2 ' ||
2002 ' AND mbis.sr_instance_id = :3 ';
2003 g_org_id := p_organization_id;
2004 g_instance_id := p_instance_id;
2005 else
2006 where_stat := where_stat ||
2007 ' AND :2 = :3 ';
2008 g_org_id := -1;
2009 g_instance_id := -1;
2010 end if;
2011
2012 if p_inventory_item_id is not null then
2013 where_stat := where_stat ||
2014 ' AND mbis.inventory_item_id = :4 ';
2015 g_item_id := p_inventory_item_id;
2016 else
2017 where_stat := where_stat ||
2018 ' AND :4=-1 ';
2019 g_item_id :=-1;
2020 end if;
2021
2022 if p_project_id is not null then
2023 if p_date then -- from late_orders
2024 if p_task_id is null then
2025 where_stat := where_stat ||
2026 ' AND exists (select 1 '||
2027 ' from msc_demands md '||
2028 ' where md.plan_id = mbis.plan_id '||
2029 ' and md.demand_id = mbis.number1 '||
2030 ' and md.project_id = :5)';
2031 end if;
2032 else
2033 where_stat := where_stat ||
2034 ' AND mbis.project_id = :5';
2035 end if;
2036 g_project_id := p_project_id;
2037 else
2038 where_stat := where_stat ||
2039 ' AND :5=-1 ';
2040 g_project_id :=-1;
2041 end if;
2042
2043 if p_task_id is not null then
2044 if p_date then -- from late_orders
2045 where_stat := where_stat ||
2046 ' AND exists (select 1 '||
2047 ' from msc_demands md '||
2048 ' where md.plan_id = mbis.plan_id '||
2049 ' and md.demand_id = mbis.number1 '||
2050 ' and md.project_id = :5 '||
2051 ' and md.task_id = :6)';
2052 else
2053 where_stat := where_stat ||
2054 ' AND mbis.task_id = :6';
2055 end if;
2056 g_task_id := p_task_id;
2057 else
2058 where_stat := where_stat ||
2059 ' AND :6=-1 ';
2060 g_task_id :=-1;
2061 end if;
2062
2063 if p_category_name is not null then
2064 if subStr(p_category_name,1,3) = '-1:' then
2065 -- come from msc_bis_inv_cat_mv_tab
2066 where_stat := where_stat ||
2067 ' and -1 = :9 '||
2068 ' and mbis.category_set_id = :10 ' ||
2069 ' and mbis.category_name = :11 ';
2070 g_category_id := -1;
2071 g_category_name := subStr(p_category_name,4);
2072 else
2073 where_stat := where_stat ||
2074 ' AND EXISTS '||
2075 ' (select 1 '||
2076 ' from msc_item_categories mit ' ||
2077 ' where mit.organization_id = mbis.organization_id '||
2078 ' and mit.sr_instance_id = mbis.sr_instance_id '||
2079 ' and mit.inventory_item_id = mbis.inventory_item_id '||
2080 ' and -1 = :9 '||
2081 ' and mit.category_set_id = :10 '||
2082 ' and mit.category_name = :11 )';
2083 g_category_id := -1;
2084 g_category_name := p_category_name;
2085 end if;
2086 g_category_set_id := p_category_set_id;
2087
2088 elsif p_category_id is not null then
2089 if p_category_id < 0 then -- come from msc_bis_inv_cat_mv_tab
2090 where_stat := where_stat ||
2091 ' and mbis.sr_category_id = :9 '||
2092 ' and mbis.category_set_id = :10 '||
2093 ' and ''-1'' = :11 ';
2094 g_category_id := p_category_id*-1;
2095 g_category_name := '-1';
2096 else
2097 where_stat := where_stat ||
2098 ' AND EXISTS '||
2099 ' (select 1 '||
2100 ' from msc_item_categories mit ' ||
2101 ' where mit.organization_id = mbis.organization_id '||
2102 ' and mit.sr_instance_id = mbis.sr_instance_id '||
2103 ' and mit.inventory_item_id = mbis.inventory_item_id '||
2104 ' and mit.sr_category_id = :9 '||
2105 ' and mit.category_set_id = :10 ' ||
2106 ' and ''-1'' = :11 )';
2107 g_category_id := p_category_id;
2108 g_category_name := '-1';
2109 end if;
2110 g_category_set_id := p_category_set_id;
2111 else
2112 where_stat := where_stat ||
2113 ' AND :9=:10 '||
2114 ' and ''-1'' = :11 ';
2115 g_category_id :=-1;
2116 g_category_set_id :=-1;
2117 g_category_name := '-1';
2118 end if;
2119
2120 if p_product_family_id is not null then
2121 where_stat := where_stat ||
2122 ' AND EXISTS '||
2123 ' (select 1 '||
2124 ' from msc_bom_components mbc ' ||
2125 ' where mbc.organization_id = mbis.organization_id '||
2126 ' and mbc.sr_instance_id = mbis.sr_instance_id '||
2127 ' and mbc.plan_id = mbis.plan_id ' ||
2128 ' and mbc.inventory_item_id = mbis.inventory_item_id '||
2129 ' and mbc.using_assembly_id = :11 )';
2130 g_product_family_id := p_product_family_id;
2131 else
2132 where_stat := where_stat ||
2133 ' AND :11=-1 ';
2134 g_product_family_id :=-1;
2135 end if;
2136
2137 if p_date then
2138 if p_start_date is not null then
2139 where_stat := where_stat ||
2140 ' AND exists '||
2141 ' (select 1 '||
2142 ' from msc_demands md '||
2143 ' where md.plan_id = mbis.plan_id '||
2144 ' and md.demand_id = mbis.number1 '||
2145 ' and trunc(nvl(md.assembly_demand_comp_date,md.using_assembly_demand_date)) between :7 and :8)';
2146 g_start_date := trunc(p_start_date);
2147 g_end_date := trunc(p_end_date);
2148 else
2149 where_stat := where_stat ||
2150 ' AND :7=:8 ';
2151 g_start_date := sysdate;
2152 g_end_date := sysdate;
2153 end if;
2154 end if;
2155
2156
2157 return where_stat;
2158 END;
2159
2160 FUNCTION get_service_level(p_plan_id IN NUMBER,
2161 p_instance_id IN NUMBER,
2162 p_organization_id IN NUMBER,
2163 p_item_id IN NUMBER,
2164 p_start_date date default null,
2165 p_end_date date default null,
2166 p_use_old_demand_qty number default null) RETURN NUMBER IS
2167 the_cursor KPICurTyp;
2168 sql_stat varchar2(3000);
2169 where_stat varchar2(2000);
2170 v_org_id number;
2171 v_instance_id number;
2172 v_item_id number;
2173 v_start date;
2174 v_end date;
2175 v_qty number;
2176 v_qty2 number;
2177 v_service number;
2178 v_constraint number;
2179 v_plan_type number;
2180
2181 l_category_set_id number;
2182 l_category_set_str varchar2(100);
2183
2184 v_run_qty number;
2185 v_run_qty2 number;
2186 from_plan_node boolean := false;
2187
2188 BEGIN
2189 if ( p_instance_id is null and p_organization_id is null and p_item_id is null) then
2190 from_plan_node := true;
2191 end if;
2192
2193 l_category_set_id := MSC_ANALYSIS_PKG.get_cat_set_id(p_plan_id);
2194 if (l_category_set_id is null) then
2195 l_category_set_str := '';
2196 else
2197 l_category_set_str := ' AND mic.category_set_id = '||l_category_set_id;
2198 end if;
2199
2200
2201 select nvl(DAILY_RESOURCE_CONSTRAINTS,0)+
2202 nvl(WEEKLY_RESOURCE_CONSTRAINTS,0)+
2203 nvl(PERIOD_RESOURCE_CONSTRAINTS,0),
2204 plan_type
2205 into v_constraint, v_plan_type
2206 from msc_plans
2207 where plan_id = p_plan_id;
2208
2209 if v_plan_type <>4 and v_constraint = 0 then
2210 -- unconstrained plan is always 100%
2211 return 100;
2212 end if;
2213
2214 where_stat := 'WHERE md.plan_id = :1 ' ||
2215 ' AND md.sr_instance_id = mic.sr_instance_id '||
2216 ' AND md.organization_id = mic.organization_id '||
2217 ' AND md.inventory_item_id = mic.inventory_item_id '||
2218 l_category_set_str||
2219 ' AND md.origination_type in (6,7,8,9,11,15,22,29,30) ';
2220 if p_instance_id is not null or from_plan_node then
2221 where_stat := where_stat ||
2222 ' AND md.organization_id = :2 '||
2223 ' AND md.sr_instance_id = :3 ';
2224 v_org_id := p_organization_id;
2225 v_instance_id := p_instance_id;
2226 else
2227 where_stat := where_stat ||
2228 ' AND :2 = :3 ';
2229 v_org_id := -1;
2230 v_instance_id := -1;
2231 end if;
2232
2233 if p_item_id is not null then
2234 where_stat := where_stat ||
2235 ' AND md.inventory_item_id = :4 ';
2236 v_item_id := p_item_id;
2237 else
2238 where_stat := where_stat ||
2239 ' AND -1 = :4 ';
2240 v_item_id := -1;
2241 end if;
2242
2243 if p_start_date is not null then
2244 where_stat := where_stat ||
2245 ' AND trunc(md.USING_ASSEMBLY_DEMAND_DATE) BETWEEN :5 AND :6 ';
2246 v_start := trunc(p_start_date);
2247 v_end := trunc(p_end_date);
2248 else
2249 where_stat := where_stat ||
2250 ' AND :5 = :6 ';
2251 v_start := sysdate;
2252 v_end := sysdate;
2253 end if;
2254
2255 --if (p_use_old_demand_qty is null) then
2256 if v_plan_type <> 4 then
2257 sql_stat := 'SELECT sum(nvl(md.quantity_by_due_date,0)*nvl(md.probability,1)), '||
2258 ' sum(md.USING_REQUIREMENT_QUANTITY*nvl(md.probability,1)) ' ||
2259 ' FROM msc_demands md, ' ||
2260 ' msc_item_categories mic ' ||
2261 where_stat;
2262 --elsif (p_use_old_demand_qty = -1) then
2263 elsif v_plan_type = 4 then
2264 sql_stat := 'SELECT sum(nvl(md.old_demand_quantity,0)*nvl(md.probability,1)), '||
2265 ' sum(md.USING_REQUIREMENT_QUANTITY*nvl(md.probability,1)) ' ||
2266 ' FROM msc_demands md, ' ||
2267 ' msc_item_categories mic ' ||
2268 where_stat;
2269 end if;
2270
2271 if ( not(from_plan_node) ) then
2272 OPEN the_cursor FOR sql_stat USING p_plan_id,v_org_id,
2273 v_instance_id, v_item_id,
2274 v_start, v_end;
2275 FETCH the_cursor INTO v_qty, v_qty2;
2276 CLOSE the_cursor;
2277 else
2278 v_qty := 0;
2279 v_qty2 := 0;
2280
2281 open c_plan_orgs(p_plan_id);
2282 loop
2283 fetch c_plan_orgs into v_instance_id, v_org_id;
2284 exit when c_plan_orgs%notfound;
2285
2286 OPEN the_cursor FOR sql_stat USING p_plan_id,v_org_id,
2287 v_instance_id, v_item_id, v_start, v_end;
2288 FETCH the_cursor INTO v_run_qty, v_run_qty2;
2289 CLOSE the_cursor;
2290
2291 v_qty := v_qty + nvl(v_run_qty,0);
2292 v_qty2 := v_qty2 + nvl(v_run_qty2,0);
2293 end loop;
2294 close c_plan_orgs;
2295 end if;
2296
2297 if nvl(v_qty2,0) =0 then -- there is no demand, will show 100%
2298 v_service := 100;
2299 elsif nvl(v_qty,0)=0 then
2300 v_service := 0;
2301 else
2302 v_service := round(v_qty/v_qty2*100,6);
2303 end if;
2304
2305 return v_service;
2306
2307 END get_service_level;
2308
2309 FUNCTION get_tp_cost(p_plan_id IN NUMBER,
2310 p_instance_id IN NUMBER,
2311 p_organization_id IN NUMBER,
2312 p_item_id IN NUMBER,
2313 p_start_date date default null,
2314 p_end_date date default null,
2315 p_planner_code varchar2 default null) RETURN NUMBER IS
2316 the_cursor KPICurTyp;
2317 sql_stat varchar2(5000);
2318 where_stat varchar2(3000);
2319 v_org_id number;
2320 v_instance_id number;
2321 v_item_id number;
2322 v_start date;
2323 v_end date;
2324 v_cost number;
2325 v_planner_code varchar2(50);
2326
2327 l_category_set_id number;
2328 l_category_set_str varchar2(100);
2329
2330 v_run_qty number;
2331 from_plan_node boolean := false;
2332
2333 BEGIN
2334 if ( p_instance_id is null and p_organization_id is null and p_item_id is null) then
2335 from_plan_node := true;
2336 end if;
2337
2338 l_category_set_id := MSC_ANALYSIS_PKG.get_cat_set_id(p_plan_id);
2339 if (l_category_set_id is null) then
2340 l_category_set_str := '';
2341 else
2342 l_category_set_str := ' AND mic.category_set_id = '||l_category_set_id;
2343 end if;
2344
2345 where_stat := ' WHERE ms.plan_id = :1 ' ||
2346 ' AND ms.sr_instance_id = mic.sr_instance_id '||
2347 ' AND ms.organization_id = mic.organization_id '||
2348 ' AND ms.inventory_item_id = mic.inventory_item_id '||
2349 l_category_set_str||
2350 ' and ms.organization_id != ms.source_organization_id '||
2351 ' and ms.order_type in (5,11) '||
2352 ' and ms.plan_id = msi.plan_id '||
2353 ' and ms.organization_id = msi.organization_id '||
2354 ' and ms.sr_instance_id = msi.sr_instance_id '||
2355 ' and ms.inventory_item_id = msi.inventory_item_id '||
2356 ' and ms.plan_id = mism.plan_id '||
2357 ' and ms.organization_id = mism.to_organization_id '||
2358 ' and ms.sr_instance_id = mism.sr_instance_id '||
2359 ' and ms.source_organization_id = mism.from_organization_id '||
2360 ' and ms.source_sr_instance_id = mism.sr_instance_id2'||
2361 ' and ms.ship_method = mism.ship_method ';
2362 if p_instance_id is not null or from_plan_node then
2363 where_stat := where_stat ||
2364 ' AND ms.organization_id = :2 '||
2365 ' AND ms.sr_instance_id = :3 ';
2366 v_org_id := p_organization_id;
2367 v_instance_id := p_instance_id;
2368 else
2369 where_stat := where_stat ||' AND :2 = :3 ';
2370 v_org_id := -1;
2371 v_instance_id := -1;
2372 end if;
2373
2374 if p_item_id is not null then
2375 where_stat := where_stat ||
2376 ' AND ms.inventory_item_id = :4 ';
2377 v_item_id := p_item_id;
2378 else
2379 where_stat := where_stat ||' AND -1 = :4 ';
2380 v_item_id := -1;
2381 end if;
2382
2383
2384
2385 if p_start_date is not null then
2386 where_stat := where_stat ||
2387 ' AND trunc(ms.new_dock_date) BETWEEN :5 AND :6 ';
2388 v_start := trunc(p_start_date);
2389 v_end := trunc(p_end_date);
2390 else
2391 where_stat := where_stat ||
2392 ' AND :5 = :6 ';
2393 v_start := sysdate;
2394 v_end := sysdate;
2395 end if;
2396
2397 if p_planner_code is not null then
2398 where_stat := where_stat ||
2399 ' AND msi.planner_code = :7 ';
2400 v_planner_code := p_planner_code;
2401 else
2402 where_stat := where_stat ||' AND ''-1'' = :4 ';
2403 v_planner_code := '''-1''';
2404 end if;
2405
2406 sql_stat := ' select round(sum(nvl(((ms.new_order_quantity * '||
2407 ' msi.unit_weight) '||
2408 ' * mism.cost_per_weight_unit),0)),6) '||
2409 ' from msc_supplies ms, '||
2410 ' msc_system_items msi, '||
2411 ' msc_item_categories mic, '||
2412 ' msc_interorg_ship_methods mism '|| where_stat;
2413 if ( not(from_plan_node) ) then
2414 OPEN the_cursor FOR sql_stat USING p_plan_id,v_org_id,
2415 v_instance_id, v_item_id,
2416 v_start, v_end, p_planner_code;
2417 FETCH the_cursor INTO v_cost;
2418 CLOSE the_cursor;
2419 else
2420 v_cost := 0;
2421 open c_plan_orgs(p_plan_id);
2422 loop
2423 fetch c_plan_orgs into v_instance_id, v_org_id;
2424 exit when c_plan_orgs%notfound;
2425
2426 OPEN the_cursor FOR sql_stat USING p_plan_id,v_org_id,
2427 v_instance_id, v_item_id, v_start, v_end, p_planner_code;
2428 FETCH the_cursor INTO v_run_qty;
2429 CLOSE the_cursor;
2430
2431 v_cost := v_cost + nvl(v_run_qty,0);
2432 end loop;
2433 close c_plan_orgs;
2434 end if;
2435
2436
2437 return v_cost;
2438
2439 END get_tp_cost;
2440
2441 FUNCTION get_target_service_level(p_plan_id IN NUMBER,
2442 p_instance_id IN NUMBER,
2443 p_organization_id IN NUMBER,
2444 p_item_id IN NUMBER,
2445 p_start_date date default null,
2446 p_end_date date default null) RETURN NUMBER IS
2447 the_cursor KPICurTyp;
2448 sql_stat varchar2(3000);
2449 where_stat varchar2(2000);
2450 v_org_id number;
2451 v_instance_id number;
2452 v_item_id number;
2453 v_start date;
2454 v_end date;
2455 v_qty number;
2456 v_count number;
2457
2458 l_category_set_id number;
2459 l_category_set_str varchar2(100);
2460
2461 v_run_qty number;
2462 v_dmd_count number := 0;
2463 from_plan_node boolean := false;
2464 BEGIN
2465
2466 if ( p_instance_id is null and p_organization_id is null and p_item_id is null) then
2467 from_plan_node := true;
2468 end if;
2469
2470 l_category_set_id := MSC_ANALYSIS_PKG.get_cat_set_id(p_plan_id);
2471 if (l_category_set_id is null) then
2472 l_category_set_str := '';
2473 else
2474 l_category_set_str := ' AND mic.category_set_id = '||l_category_set_id;
2475 end if;
2476
2477
2478 where_stat := 'WHERE md.plan_id = :1 ' ||
2479 ' AND md.sr_instance_id = mic.sr_instance_id '||
2480 ' AND md.organization_id = mic.organization_id '||
2481 ' AND md.inventory_item_id = mic.inventory_item_id '||
2482 l_category_set_str||
2483 ' AND md.origination_type in (6,7,8,9,11,15,22,29,30) ';
2484 if p_instance_id is not null or from_plan_node then
2485 where_stat := where_stat ||
2486 ' AND md.organization_id = :2 '||
2487 ' AND md.sr_instance_id = :3 ';
2488 v_org_id := p_organization_id;
2489 v_instance_id := p_instance_id;
2490 else
2491 where_stat := where_stat ||
2492 ' AND :2 = :3 ';
2493 v_org_id := -1;
2494 v_instance_id := -1;
2495 end if;
2496
2497 if p_item_id is not null then
2498 where_stat := where_stat ||
2499 ' AND md.inventory_item_id = :4 ';
2500 v_item_id := p_item_id;
2501 else
2502 where_stat := where_stat ||
2503 ' AND -1 = :4 ';
2504 v_item_id := -1;
2505 end if;
2506
2507 if p_start_date is not null then
2508 where_stat := where_stat ||
2509 ' AND trunc(md.USING_ASSEMBLY_DEMAND_DATE) BETWEEN :5 AND :6 ';
2510 v_start := trunc(p_start_date);
2511 v_end := trunc(p_end_date);
2512 else
2513 where_stat := where_stat ||
2514 ' AND :5 = :6 ';
2515 v_start := sysdate;
2516 v_end := sysdate;
2517 end if;
2518
2519 sql_stat := 'SELECT avg(md.service_level), count(*) '||
2520 ' FROM msc_demands md, ' ||
2521 ' msc_item_categories mic ' ||
2522 where_stat;
2523 if ( not(from_plan_node) ) then
2524 OPEN the_cursor FOR sql_stat USING p_plan_id,v_org_id,
2525 v_instance_id, v_item_id,
2526 v_start, v_end;
2527 FETCH the_cursor INTO v_qty, v_count;
2528 CLOSE the_cursor;
2529 else
2530 v_qty := 0;
2531 open c_plan_orgs(p_plan_id);
2532 loop
2533 fetch c_plan_orgs into v_instance_id, v_org_id;
2534 exit when c_plan_orgs%notfound;
2535 OPEN the_cursor FOR sql_stat USING p_plan_id,v_org_id,
2536 v_instance_id, v_item_id, v_start, v_end;
2537 FETCH the_cursor INTO v_run_qty, v_count;
2538 CLOSE the_cursor;
2539 v_qty := v_qty + (nvl(v_run_qty, 0) * nvl(v_count,0));
2540 v_dmd_count := v_dmd_count + nvl(v_count,0);
2541 end loop;
2542 close c_plan_orgs;
2543
2544 if ( nvl(v_dmd_count,0) = 0 ) then
2545 v_qty := 0;
2546 else
2547 v_qty := v_qty / v_dmd_count;
2548 end if;
2549 end if;
2550
2551 return v_qty;
2552
2553 END get_target_service_level;
2554
2555
2556 FUNCTION service_data_exist(p_plan_id IN NUMBER,
2557 p_instance_id IN NUMBER,
2558 p_organization_id IN NUMBER,
2559 p_item_id IN NUMBER) RETURN BOOLEAN IS
2560 the_cursor KPICurTyp;
2561 sql_stat varchar2(1000);
2562 where_stat varchar2(1000);
2563 v_org_id number;
2564 v_instance_id number;
2565 v_item_id number;
2566 v_temp number;
2567 BEGIN
2568
2569 sql_stat := ' SELECT 1 ' ||
2570 ' FROM msc_demands ';
2571
2572 where_stat := 'WHERE plan_id = :1 '||
2573 ' AND origination_type in (29) ';
2574 if p_instance_id is not null then
2575 where_stat := where_stat ||
2576 ' AND organization_id = :2 '||
2577 ' AND sr_instance_id = :3 ';
2578 v_org_id := p_organization_id;
2579 v_instance_id := p_instance_id;
2580 else
2581 where_stat := where_stat ||
2582 ' AND :2 = :3 ';
2583 v_org_id := -1;
2584 v_instance_id := -1;
2585 end if;
2586
2587 if p_item_id is not null then
2588 where_stat := where_stat ||
2589 ' AND inventory_item_id = :4 ';
2590 v_item_id := p_item_id;
2591 else
2592 where_stat := where_stat ||
2593 ' AND -1 = :4 ';
2594 v_item_id := -1;
2595 end if;
2596
2597 sql_stat := sql_stat || where_stat||
2598 ' and quantity_by_due_date is not null ' ||
2599 ' and rownum = 1 ';
2600
2601 OPEN the_cursor FOR sql_stat USING p_plan_id,v_org_id,
2602 v_instance_id, v_item_id;
2603 FETCH the_cursor INTO v_temp;
2604 CLOSE the_cursor;
2605
2606 if v_temp = 1 then
2607 return true;
2608 else
2609 return false;
2610 end if;
2611
2612 END service_data_exist;
2613
2614 FUNCTION service_target(p_plan IN NUMBER, p_instance_id IN NUMBER,
2615 p_org_id IN NUMBER, p_item_id IN NUMBER) RETURN NUMBER IS
2616 l_target NUMBER;
2617
2618 CURSOR plan_target IS
2619 SELECT service_level
2620 FROM msc_plans
2621 WHERE plan_id = p_plan;
2622
2623 CURSOR org_target IS
2624 SELECT service_level
2625 FROM msc_trading_partners
2626 WHERE sr_instance_id = p_instance_id
2627 AND sr_tp_id=p_org_id;
2628
2629 CURSOR item_target IS
2630 SELECT service_level
2631 FROM msc_system_items
2632 WHERE plan_id = p_plan
2633 AND sr_instance_id = p_instance_id
2634 AND organization_id= p_org_id
2635 AND inventory_item_id = p_item_id;
2636
2637
2638 BEGIN
2639 if p_item_id is not null then
2640 OPEN item_target;
2641 FETCH item_target INTO l_target;
2642 CLOSE item_target;
2643 if l_target is null then
2644 if p_org_id is not null then
2645 OPEN org_target;
2646 FETCH org_target INTO l_target;
2647 CLOSE org_target;
2648 if l_target is null then
2649 OPEN plan_target;
2650 FETCH plan_target INTO l_target;
2651 CLOSE plan_target;
2652 end if;
2653 else
2654 OPEN plan_target;
2655 FETCH plan_target INTO l_target;
2656 CLOSE plan_target;
2657 end if;
2658 end if;
2659 elsif p_org_id is not null then
2660 OPEN org_target;
2661 FETCH org_target INTO l_target;
2662 CLOSE org_target;
2663 if l_target is null then
2664 OPEN plan_target;
2665 FETCH plan_target INTO l_target;
2666 CLOSE plan_target;
2667 end if;
2668 else
2669 OPEN plan_target;
2670 FETCH plan_target INTO l_target;
2671 CLOSE plan_target;
2672 end if;
2673 return nvl(l_target,0);
2674 END service_target;
2675
2676 FUNCTION service_target_trend(p_plan_id IN NUMBER, p_instance_id IN NUMBER,
2677 p_org_id IN NUMBER, p_item_id IN NUMBER) RETURN VARCHAR2 IS
2678 l_target NUMBER;
2679 l_target_list varchar2(500);
2680 BEGIN
2681
2682 l_target := service_target(p_plan_id, p_instance_id, p_org_id, p_item_id);
2683
2684 FOR j in 1 .. g_period_name.LAST LOOP
2685
2686 l_target_list := l_target_list ||g_param||
2687 fnd_number.number_to_canonical(nvl(l_target,0));
2688 END LOOP;
2689
2690
2691 return l_target_list;
2692 END service_target_trend;
2693
2694 FUNCTION construct_sup_where(p_organization_id number,
2695 p_instance_id number,
2696 p_item_id number,
2697 p_sup_id number,
2698 p_sup_site_id number) RETURN varchar2 IS
2699 where_stat varchar2(1000);
2700
2701 BEGIN
2702 if p_instance_id is not null then
2703 where_stat :=
2704 -- ' AND sup.organization_id = :2 ' ||
2705 ' AND sup.sr_instance_id = :3 ';
2706 g_org_id := p_organization_id;
2707 g_instance_id := p_instance_id;
2708 else
2709 where_stat := where_stat ||
2710 ' AND :3 = -1 ';
2711 g_org_id := -1;
2712 g_instance_id := -1;
2713 end if;
2714
2715 if p_item_id is not null then
2716 where_stat := where_stat ||
2717 ' AND sup.inventory_item_id = :4 ';
2718 g_item_id := p_item_id;
2719 else
2720 where_stat := where_stat ||
2721 ' AND :4 = -1 ';
2722 g_item_id := -1;
2723 end if;
2724
2725 if p_sup_id is not null then
2726 where_stat := where_stat ||
2727 ' AND sup.supplier_id = :5 ';
2728 g_sup_id := p_sup_id;
2729 else
2730 where_stat := where_stat ||
2731 ' AND :5 = -1 ';
2732 g_sup_id := -1;
2733 end if;
2734
2735 if p_sup_site_id is not null then
2736 where_stat := where_stat ||
2737 ' AND sup.supplier_site_id = :6';
2738 g_sup_site_id := p_sup_site_id;
2739 else
2740 where_stat := where_stat ||
2741 ' AND :6 = -1';
2742 g_sup_site_id:= -1;
2743 end if;
2744
2745 --dbms_output.put_line(where_stat);
2746 return where_stat;
2747
2748 END;
2749 --Procedure call_get_actuals IS
2750 --l_var varchar2(2000);
2751 --BEGIN
2752 --get_trend_actuals(2157,201,207,4,14661,null,
2753 -- null, null, null,null, null,null,
2754 -- 243, null, 12271, 7023,l_var);
2755 --exception
2756 -- when others then
2757 --dbms_output.put_line(sqlerrm);
2758 --END;
2759
2760 Procedure refresh_kpi_data(p_plan_id number) IS
2761 l_err_buf VARCHAR2(4000);
2762 l_ret_code NUMBER;
2763
2764 cursor show_kpi is
2765 select display_kpi, curr_plan_type
2766 from msc_plans
2767 where plan_id = p_plan_id;
2768
2769 v_show_kpi number;
2770 v_plan_type number;
2771 BEGIN
2772 OPEN show_kpi;
2773 FETCH show_kpi INTO v_show_kpi,v_plan_type;
2774 CLOSE show_kpi;
2775
2776 if nvl(v_show_kpi,1) = 1 then
2777 FND_FILE.PUT_LINE(FND_FILE.LOG,'--- refreshing summary data for kpi ---');
2778 msc_get_bis_values.refresh_data(l_err_buf, l_ret_code,p_plan_id, v_plan_type);
2779 else -- set kpi status as not refresh
2780 msc_get_bis_values.set_kpi_refresh_status(p_plan_id,'NOT REFRESH');
2781 end if;
2782 exception when others then
2783 FND_FILE.PUT_LINE(FND_FILE.LOG,'refreshing kpi summary data fails');
2784 END refresh_kpi_data;
2785
2786
2787
2788 Procedure refresh_data(errbuf OUT NOCOPY VARCHAR2,
2789 retcode OUT NOCOPY NUMBER,
2790 p_plan_id number) is
2791 l_plan_type number;
2792 cursor c_plan_type is
2793 select plan_type from msc_plans where plan_id =p_plan_id;
2794
2795
2796 BEGIN
2797
2798 open c_plan_type;
2799 fetch c_plan_type into l_plan_type;
2800 close c_plan_type;
2801
2802 refresh_data(errbuf,retcode,p_plan_id,l_plan_type);
2803
2804 end;
2805
2806
2807
2808 Procedure refresh_data(errbuf OUT NOCOPY VARCHAR2,
2809 retcode OUT NOCOPY NUMBER,
2810 p_plan_id number,
2811 p_plan_type number) IS
2812 p_request_id number;
2813 BEGIN
2814 FND_FILE.PUT_LINE(FND_FILE.LOG,'start refreshing');
2815 -- set kpi as refreshing
2816 set_kpi_refresh_status(p_plan_id,'REFRESHING');
2817
2818 for a in 1..5 loop
2819
2820 p_request_id := fnd_request.submit_request(
2821 'MSC',
2822 'MSCKPIREF',
2823 null,
2824 null,
2825 false,
2826 p_plan_id,
2827 a,
2828 p_plan_type);
2829 FND_FILE.PUT_LINE(FND_FILE.LOG,'request id is ='||p_request_id);
2830 end loop;
2831
2832 commit;
2833
2834 exception when others then
2835 set_kpi_refresh_status(p_plan_id,'NOT REFRESH');
2836 END refresh_data;
2837
2838 Function IsKPIAvail(p_plan_id number) return number is
2839 cursor check_kpi is
2840 select kpi_refresh
2841 from msc_plans
2842 where plan_id = p_plan_id;
2843
2844 v_kpi_refresh number;
2845 begin
2846
2847 OPEN check_kpi;
2848 FETCH check_kpi INTO v_kpi_refresh;
2849 CLOSE check_kpi;
2850
2851 if v_kpi_refresh = 0 then -- REFRESHING
2852 return 2;
2853 elsif v_kpi_refresh = 5 then -- REFRESHE DONE
2854 return 1;
2855 else
2856 return 0; -- NOT REFRESH
2857 end if;
2858
2859 end IsKPIAvail;
2860
2861
2862 PROCEDURE set_kpi_refresh_status(p_plan_id number,p_status varchar2) is
2863 v_kpi_refresh number;
2864 v_status number;
2865 begin
2866 select kpi_refresh
2867 into v_kpi_refresh
2868 from msc_plans
2869 where plan_id = p_plan_id;
2870
2871 if p_status = 'NOT REFRESH' then
2872 v_status := -1;
2873 elsif p_status = 'REFRESHING' then
2874 v_status :=0;
2875 elsif p_status = 'ONE_DONE' then
2876 v_status := v_kpi_refresh + 1;
2877 end if;
2878
2879 MSC_UTIL.MSC_DEBUG('v_status='||v_status);
2880 update msc_plans
2881 set kpi_refresh = decode(v_status,0,0,-1,-1,kpi_refresh+1)
2882 where plan_id = p_plan_id;
2883
2884 commit;
2885 end set_kpi_refresh_status;
2886
2887 Procedure refresh_one_table(errbuf OUT NOCOPY VARCHAR2,
2888 retcode OUT NOCOPY NUMBER,
2889 p_plan_id number,
2890 p_kpi_table number,
2891 p_plan_type number) IS
2892 BEGIN
2893 if p_kpi_table =1 then
2894
2895 MSC_UTIL.MSC_DEBUG('refreshing MSC_BIS_INV_DATE_MV_TAB table for plan id '||p_plan_id);
2896
2897 delete from msc_bis_inv_date_mv_tab
2898 where plan_id = p_plan_id;
2899 if p_plan_type = 8 then -- should change to srp
2900
2901 insert into msc_bis_inv_date_mv_tab(
2902 LAST_UPDATE_DATE,
2903 LAST_UPDATED_BY,
2904 CREATION_DATE,
2905 CREATED_BY,
2906 LAST_UPDATE_LOGIN,
2907 mds_price,
2908 mds_cost,
2909 inventory_cost,
2910 production_cost,
2911 purchasing_cost,
2912 demand_penalty_cost,
2913 carrying_cost,
2914 plan_id,
2915 organization_id,
2916 sr_instance_id,
2917 detail_date,
2918 inventory_value,
2919 planner_code)
2920 select
2921 sysdate,
2922 -1,
2923 sysdate,
2924 -1,
2925 -1,
2926 sum(nvl(mbid.mds_price,0)),
2927 sum(nvl(mbid.mds_cost,0)),
2928 sum(nvl(mbid.inventory_cost,0)),
2929 sum(nvl(mbid.production_cost,0)),
2930 sum(nvl(mbid.purchasing_cost,0)),
2931 sum(nvl(mbid.demand_penalty_cost,0)+
2932 nvl(mbid.supplier_overcap_cost,0)),
2933 sum(nvl(mbid.carrying_cost,0)),
2934 mbid.plan_id,
2935 mbid.organization_id,
2936 mbid.sr_instance_id,
2937 mbid.detail_date,
2938 sum(nvl(mbid.inventory_value,0)),
2939 msi.planner_code
2940 from msc_bis_inv_detail mbid,
2941 msc_system_items msi
2942 where mbid.plan_id = p_plan_id
2943 and nvl(mbid.period_type,0) = 0
2944 and mbid.organization_id = msi.organization_id
2945 and mbid.sr_instance_id = msi.sr_instance_id
2946 and mbid.plan_id = msi.plan_id
2947 and mbid.inventory_item_id = msi.inventory_item_id
2948 group by mbid.plan_id,
2949 mbid.organization_id,
2950 mbid.sr_instance_id,
2951 mbid.detail_date,
2952 msi.planner_code;
2953
2954 else
2955 insert into msc_bis_inv_date_mv_tab(
2956 LAST_UPDATE_DATE,
2957 LAST_UPDATED_BY,
2958 CREATION_DATE,
2959 CREATED_BY,
2960 LAST_UPDATE_LOGIN,
2961 mds_price,
2962 mds_cost,
2963 inventory_cost,
2964 production_cost,
2965 purchasing_cost,
2966 demand_penalty_cost,
2967 carrying_cost,
2968 plan_id,
2969 organization_id,
2970 sr_instance_id,
2971 detail_date)
2972 select
2973 sysdate,
2974 -1,
2975 sysdate,
2976 -1,
2977 -1,
2978 sum(nvl(mds_price,0)),
2979 sum(nvl(mds_cost,0)),
2980 sum(nvl(inventory_cost,0)),
2981 sum(nvl(production_cost,0)),
2982 sum(nvl(purchasing_cost,0)),
2983 sum(nvl(demand_penalty_cost,0)+
2984 nvl(supplier_overcap_cost,0)),
2985 sum(nvl(carrying_cost,0)),
2986 plan_id,
2987 organization_id,
2988 sr_instance_id,
2989 detail_date
2990 from msc_bis_inv_detail
2991 where plan_id = p_plan_id
2992 and nvl(period_type,0) = 0
2993 group by plan_id,
2994 organization_id,
2995 sr_instance_id,
2996 detail_date;
2997 end if; -- if p_plan_type =
2998 elsif p_kpi_table =2 then
2999 MSC_UTIL.MSC_DEBUG('refreshing MSC_BIS_INV_CAT_MV_TAB table for plan id '||p_plan_id);
3000 delete from msc_bis_inv_cat_mv_tab
3001 where plan_id = p_plan_id;
3002
3003 insert into msc_bis_inv_cat_mv_tab(
3004 LAST_UPDATE_DATE,
3005 LAST_UPDATED_BY,
3006 CREATION_DATE,
3007 CREATED_BY,
3008 LAST_UPDATE_LOGIN,
3009 mds_price,
3010 mds_cost,
3011 inventory_cost,
3012 production_cost,
3013 purchasing_cost,
3014 demand_penalty_cost,
3015 carrying_cost,
3016 plan_id,
3017 organization_id,
3018 sr_instance_id,
3019 sr_category_id,
3020 category_name,
3021 category_set_id,
3022 detail_date)
3023 select
3024 sysdate,
3025 -1,
3026 sysdate,
3027 -1,
3028 -1,
3029 sum(nvl(mbis.mds_price,0)),
3030 sum(nvl(mbis.mds_cost,0)),
3031 sum(nvl(mbis.inventory_cost,0)),
3032 sum(nvl(mbis.production_cost,0)),
3033 sum(nvl(mbis.purchasing_cost,0)),
3034 sum(nvl(mbis.demand_penalty_cost,0)+
3035 nvl(mbis.supplier_overcap_cost,0)),
3036 sum(nvl(mbis.carrying_cost,0)),
3037 mbis.plan_id,
3038 mbis.organization_id,
3039 mbis.sr_instance_id,
3040 mit.sr_category_id,
3041 mit.category_name,
3042 mit.category_set_id,
3043 mbis.detail_date
3044 from msc_bis_inv_detail mbis,
3045 msc_item_categories mit
3046 where mbis.plan_id = p_plan_id
3047 and mit.organization_id = mbis.organization_id
3048 and mit.sr_instance_id = mbis.sr_instance_id
3049 and mit.inventory_item_id = mbis.inventory_item_id
3050 and nvl(mbis.period_type,0) = 0
3051 group by mbis.plan_id,
3052 mbis.organization_id,
3053 mbis.sr_instance_id,
3054 mit.sr_category_id,
3055 mit.category_name,
3056 mit.category_set_id,
3057 mbis.detail_date;
3058
3059 elsif p_kpi_table = 3 then
3060 MSC_UTIL.MSC_DEBUG('refreshing MSC_DEMAND_MV_TAB table for plan id '||p_plan_id);
3061 delete from msc_demand_mv_tab
3062 where plan_id = p_plan_id;
3063
3064 insert into msc_demand_mv_tab(
3065 LAST_UPDATE_DATE,
3066 LAST_UPDATED_BY,
3067 CREATION_DATE,
3068 CREATED_BY,
3069 LAST_UPDATE_LOGIN,
3070 plan_id,
3071 organization_id,
3072 sr_instance_id,
3073 demand_count)
3074 select
3075 sysdate,
3076 -1,
3077 sysdate,
3078 -1,
3079 -1,
3080 plan_id,
3081 organization_id,
3082 sr_instance_id,
3083 count(*)
3084 from msc_demands
3085 where origination_type in (6,7,8,9,10,11,12,15,22,24,27,29,30)
3086 and plan_id = p_plan_id
3087 group by plan_id,
3088 organization_id,
3089 sr_instance_id;
3090
3091 elsif p_kpi_table = 4 then
3092 MSC_UTIL.MSC_DEBUG('refreshing MSC_LATE_ORDER_MV_TAB table for plan id '||p_plan_id);
3093 delete from msc_late_order_mv_tab
3094 where plan_id = p_plan_id;
3095
3096 insert into msc_late_order_mv_tab(
3097 LAST_UPDATE_DATE,
3098 LAST_UPDATED_BY,
3099 CREATION_DATE,
3100 CREATED_BY,
3101 LAST_UPDATE_LOGIN,
3102 plan_id,
3103 organization_id,
3104 sr_instance_id,
3105 late_order_count)
3106 select
3107 sysdate,
3108 -1,
3109 sysdate,
3110 -1,
3111 -1,
3112 plan_id,
3113 organization_id,
3114 sr_instance_id,
3115 count(distinct number1)
3116 from msc_exception_details
3117 where exception_type in (13,14,24,26)
3118 and plan_id = p_plan_id
3119 group by plan_id,
3120 organization_id,
3121 sr_instance_id;
3122
3123 elsif p_kpi_table = 5 then
3124 MSC_UTIL.MSC_DEBUG('refreshing MSC_BIS_RES_DATE_MV_TAB table for plan id '||p_plan_id);
3125 delete from msc_bis_res_date_mv_tab
3126 where plan_id = p_plan_id;
3127
3128 insert into msc_bis_res_date_mv_tab(
3129 LAST_UPDATE_DATE,
3130 LAST_UPDATED_BY,
3131 CREATION_DATE,
3132 CREATED_BY,
3133 LAST_UPDATE_LOGIN,
3134 plan_id,
3135 organization_id,
3136 sr_instance_id,
3137 resource_date,
3138 utilization,
3139 util_count,
3140 util_sum)
3141 select
3142 sysdate,
3143 -1,
3144 sysdate,
3145 -1,
3146 -1,
3147 res.plan_id,
3148 res.organization_id,
3149 res.sr_instance_id,
3150 res.resource_date,
3151 avg(nvl(res.utilization,0)),
3152 count(nvl(res.utilization,0)),
3153 sum(nvl(res.utilization,0))
3154 from msc_department_resources mdr,
3155 msc_bis_res_summary res
3156 where mdr.department_id = res.department_id
3157 AND mdr.resource_id = res.resource_id
3158 AND mdr.plan_id = res.plan_id
3159 AND mdr.sr_instance_id = res.sr_instance_id
3160 AND mdr.organization_id = res.organization_id
3161 and mdr.plan_id = p_plan_id
3162 AND nvl(res.period_type,0) = 0
3163 group by res.plan_id,
3164 res.organization_id,
3165 res.sr_instance_id,
3166 res.resource_date;
3167 end if;
3168 commit;
3169 msc_get_bis_values.set_kpi_refresh_status(p_plan_id,'ONE_DONE');
3170 exception when no_data_found then
3171 msc_get_bis_values.set_kpi_refresh_status(p_plan_id,'ONE_DONE');
3172 END refresh_one_table;
3173
3174 Procedure ui_post_plan(errbuf OUT NOCOPY VARCHAR2,
3175 retcode OUT NOCOPY NUMBER,
3176 p_plan_id IN number) IS
3177
3178 lv_msc_schema VARCHAR2(30);
3179 v_tree_exist number;
3180
3181 Cursor msc_schema IS
3182 SELECT a.oracle_username
3183 FROM FND_ORACLE_USERID a, FND_PRODUCT_INSTALLATIONS b
3184 WHERE a.oracle_id = b.oracle_id
3185 AND b.application_id= 724;
3186
3187 Cursor tree_snap IS
3188 SELECT 1
3189 FROM all_objects
3190 WHERE object_name = 'MSC_SUPPLIER_TREE_MV'
3191 AND owner = lv_msc_schema;
3192
3193 Cursor plan_c is
3194 select plan_type
3195 from msc_plans
3196 where plan_id = p_plan_id;
3197
3198 v_plan_type number;
3199
3200 cursor c_plan_archive is
3201 select nvl(archive_flag,2)
3202 from msc_plans
3203 where plan_id = p_plan_id;
3204 l_archive_flag number;
3205 l_req_id number;
3206 Begin
3207
3208 if v_tree_exist =1 then
3209 MSC_UTIL.msc_debug('---- refreshing tree mv----');
3210 DBMS_SNAPSHOT.REFRESH( lv_msc_schema||'.MSC_SUPPLIER_TREE_MV');
3211 end if;
3212
3213 msc_get_bis_values.refresh_kpi_data(p_plan_id);
3214
3215 msc_launch_plan_pk.purge_user_notes_data(p_plan_id);
3216
3217 msc_netchange_pkg.compare_plan_need_refresh(p_plan_id);
3218
3219 MSC_pers_queries.purge_plan(p_plan_id);
3220
3221 MSC_ANALYSIS_SAFETY_STOCK_PERF.schedule_aggregate(p_plan_id);
3222
3223 --msd_liability.run_liability_flow_ascp(errbuf,retcode,p_plan_id);
3224 OPEN plan_c;
3225 FETCH plan_c INTO v_plan_type;
3226 CLOSE plan_c;
3227
3228 if v_plan_type in (8,9) then -- srp plan
3229 msc_drp_util.retrieve_exp_version(p_plan_id);
3230 end if;
3231 if v_plan_type = 8 then -- srp plan
3232 MSC_PQ_UTILS.execute_plan_worklists(errbuf, retcode,
3233 p_plan_id);
3234 end if;
3235
3236 if v_plan_type = 8 then -- srp plan
3237 null; --donot call mschuba for srp plan per qa/dev
3238 else
3239 --pabram..phub
3240 open c_plan_archive;
3241 fetch c_plan_archive into l_archive_flag;
3242 close c_plan_archive;
3243 l_req_id := fnd_request.submit_request('MSC','MSCHUBA',NULL, NULL, FALSE, p_plan_id, null, l_archive_flag);
3244 commit;
3245 --pabram..phub ends
3246 end if;
3247
3248 End ui_post_plan;
3249
3250
3251 END Msc_Get_Bis_Values;