[Home] [Help]
PACKAGE BODY: APPS.MSC_GET_BIS_VALUES
Source
1 PACKAGE BODY Msc_Get_Bis_Values AS
2 /* $Header: MSCBISUB.pls 120.15.12020000.3 2012/10/17 12:12:23 wexia 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 If C1%NotFound Then
1790 If p_chart_type = INVENTORY_TURNS Then
1791 l_target := Fnd_Profile.Value('MSC_PERF_INVENTORY_TURNS');
1792 Elsif (p_chart_type = ONTIME_DELIVERY) Then
1793 l_target := Fnd_Profile.Value('MSC_PERF_ONTIME_DELIVERY');
1794 Elsif p_chart_type = MARGIN_PERCENT Then
1795 l_target := Fnd_Profile.Value('MSC_PERF_MARGIN_PERCENT');
1796 Elsif (p_chart_type in (UTILIZATION,UTILIZATION2)) Then
1797 l_target := Fnd_Profile.Value('MSC_PERF_UTILIZATION_PERCENT');
1798 End If;
1799 End if;
1800 CLOSE C1;
1801
1802 RETURN l_target;
1803
1804 EXCEPTION
1805
1806 WHEN NO_DATA_FOUND THEN
1807 return 0;
1808 WHEN others THEN
1809 return 0;
1810 END get_targets;
1811
1812 FUNCTION get_targets_trend(p_chart_type IN NUMBER, p_instance_id IN NUMBER,
1813 p_org_id IN NUMBER) RETURN VARCHAR2 IS
1814
1815 l_target NUMBER;
1816 v_time_level varchar2(30);
1817 l_target_list varchar2(500);
1818
1819 BEGIN
1820
1821 FOR j in 1 .. g_period_name.LAST LOOP
1822 v_time_level := 'Accounting+'||g_period_name(j).period_name;
1823 l_target :=
1824 get_targets(p_chart_type, p_instance_id,p_org_id,v_time_level);
1825
1826 l_target_list := l_target_list ||g_param||
1827 fnd_number.number_to_canonical(nvl(l_target,0));
1828 END LOOP;
1829
1830 return l_target_list;
1831 EXCEPTION
1832
1833 WHEN NO_DATA_FOUND THEN
1834
1835 return 0;
1836
1837 END get_targets_trend;
1838
1839
1840 FUNCTION construct_res_where(p_organization_id number,
1841 p_instance_id number,
1842 p_dept_id number,
1843 p_res_id number,
1844 p_res_group varchar2,
1845 p_dept_class varchar2,
1846 p_start_date date default null,
1847 p_end_date date default null) RETURN varchar2 IS
1848 where_stat varchar2(1000);
1849
1850 BEGIN
1851 if p_organization_id is not null then
1852 where_stat :=
1853 ' AND res.organization_id = :2 ' ||
1854 ' AND res.sr_instance_id = :3 ';
1855 g_org_id := p_organization_id;
1856 g_instance_id := p_instance_id;
1857 else
1858 where_stat := where_stat ||
1859 ' AND :2 = :3 ';
1860 g_org_id := -1;
1861 g_instance_id := -1;
1862 end if;
1863
1864 if p_dept_id is not null then
1865 where_stat := where_stat ||
1866 ' AND res.department_id = :4 ';
1867 g_dept_id := p_dept_id;
1868 else
1869 where_stat := where_stat ||
1870 ' AND :4 = -1 ';
1871 g_dept_id := -1;
1872 end if;
1873
1874 if p_res_id is not null then
1875 where_stat := where_stat ||
1876 ' AND res.resource_id = :5 ';
1877 g_res_id := p_res_id;
1878 else
1879 where_stat := where_stat ||
1880 ' AND :5 = -1 ';
1881 g_res_id := -1;
1882 end if;
1883
1884 if p_dept_class = '@@@' then
1885 where_stat := where_stat ||
1886 ' AND mdr.department_class is null '||
1887 ' AND res.resource_id <> -1' ||
1888 ' AND :a = ''-1'' ';
1889 g_dept_class := '-1';
1890
1891 elsif p_dept_class is not null then
1892 where_stat := where_stat ||
1893 ' AND mdr.department_class = :a ';
1894 g_dept_class := p_dept_class;
1895 else
1896
1897 where_stat := where_stat ||
1898 ' AND :a = ''-1'' ';
1899 g_dept_class := '-1';
1900 end if;
1901
1902 if p_res_group = '@@@' then
1903 where_stat := where_stat ||
1904 ' AND mdr.resource_group_name is null '||
1905 ' AND res.resource_id <> -1 ' ||
1906 ' AND :b = ''-1'' ';
1907 g_res_group := '-1';
1908 elsif p_res_group is not null then
1909 where_stat := where_stat ||
1910 ' AND mdr.resource_group_name = :b ';
1911 g_res_group := p_res_group;
1912 else
1913 where_stat := where_stat ||
1914 ' AND :b = ''-1'' ';
1915 g_res_group := '-1';
1916 end if;
1917 return where_stat;
1918
1919 END;
1920
1921 /*satyagi ds enhancement :--------------------------------------------------------------------------------*/
1922
1923 FUNCTION construct_res_instance_where(p_organization_id number,
1924 p_instance_id number,
1925 p_dept_id number,
1926 p_res_id number,
1927 p_res_group varchar2 ,
1928 p_dept_class varchar2 ,
1929 p_start_date date default null,
1930 p_end_date date default null ,
1931 p_res_instance_id number ,
1932 p_res_inst_serial_number varchar2) RETURN varchar2 IS
1933 where_stat varchar2(1000);
1934 BEGIN
1935 if p_organization_id is not null then
1936 where_stat :=
1937 ' AND res.organization_id = :2 ' ||
1938 ' AND res.sr_instance_id = :3 ';
1939 g_org_id := p_organization_id;
1940 g_instance_id := p_instance_id;
1941 else
1942 where_stat := where_stat ||
1943 ' AND :2 = :3 ';
1944 g_org_id := -1;
1945 g_instance_id := -1;
1946 end if;
1947
1948 if p_dept_id is not null then
1949 where_stat := where_stat ||
1950 ' AND res.department_id = :4 ';
1951 g_dept_id := p_dept_id;
1952 else
1953 where_stat := where_stat ||
1954 ' AND :4 = -1 ';
1955 g_dept_id := -1;
1956 end if;
1957
1958 if p_res_id is not null then
1959 where_stat := where_stat ||
1960 ' AND res.resource_id = :5 ';
1961 g_res_id := p_res_id;
1962 else
1963 where_stat := where_stat ||
1964 ' AND :5 = -1 ';
1965 g_res_id := -1;
1966 end if;
1967
1968 if p_res_instance_id is not null then
1969 where_stat := where_stat ||
1970 ' AND res.res_instance_id = :8 ';
1971 g_res_instance_id := p_res_instance_id;
1972 else
1973 where_stat := where_stat ||
1974 ' AND :8 = -1 ';
1975 g_res_instance_id := -1;
1976 end if;
1977
1978 if p_res_inst_serial_number = '-111' then
1979 where_stat := where_stat ||
1980 ' AND nvl(res.serial_number ,'||''''||'-111'||''''||')'||' = :9 ';
1981 g_res_inst_serial_number := p_res_inst_serial_number ;
1982 else
1983 where_stat := where_stat ||
1984 ' AND res.serial_number = :9 ';
1985 g_res_inst_serial_number := p_res_inst_serial_number ;
1986 end if;
1987
1988 return where_stat;
1989
1990 END;
1991
1992 /*--------------------------------------------------------------------------------satyagi ds enhancement :*/
1993
1994 FUNCTION construct_bis_where(p_date boolean,
1995 p_organization_id number,
1996 p_instance_id number,
1997 p_inventory_item_id number,
1998 p_project_id number,
1999 p_task_id number,
2000 p_category_id number,
2001 p_category_name varchar2,
2002 p_category_set_id number,
2003 p_product_family_id number,
2004 p_start_date date default null,
2005 p_end_date date default null)
2006 RETURN varchar2 IS
2007 where_stat varchar2(2000);
2008
2009 BEGIN
2010 if p_organization_id is not null then
2011 where_stat :=
2012 ' AND mbis.organization_id = :2 ' ||
2013 ' AND mbis.sr_instance_id = :3 ';
2014 g_org_id := p_organization_id;
2015 g_instance_id := p_instance_id;
2016 else
2017 where_stat := where_stat ||
2018 ' AND :2 = :3 ';
2019 g_org_id := -1;
2020 g_instance_id := -1;
2021 end if;
2022
2023 if p_inventory_item_id is not null then
2024 where_stat := where_stat ||
2025 ' AND mbis.inventory_item_id = :4 ';
2026 g_item_id := p_inventory_item_id;
2027 else
2028 where_stat := where_stat ||
2029 ' AND :4=-1 ';
2030 g_item_id :=-1;
2031 end if;
2032
2033 if p_project_id is not null then
2034 if p_date then -- from late_orders
2035 if p_task_id is null then
2036 where_stat := where_stat ||
2037 ' AND exists (select 1 '||
2038 ' from msc_demands md '||
2039 ' where md.plan_id = mbis.plan_id '||
2040 ' and md.demand_id = mbis.number1 '||
2041 ' and md.project_id = :5)';
2042 end if;
2043 else
2044 where_stat := where_stat ||
2045 ' AND mbis.project_id = :5';
2046 end if;
2047 g_project_id := p_project_id;
2048 else
2049 where_stat := where_stat ||
2050 ' AND :5=-1 ';
2051 g_project_id :=-1;
2052 end if;
2053
2054 if p_task_id is not null then
2055 if p_date then -- from late_orders
2056 where_stat := where_stat ||
2057 ' AND exists (select 1 '||
2058 ' from msc_demands md '||
2059 ' where md.plan_id = mbis.plan_id '||
2060 ' and md.demand_id = mbis.number1 '||
2061 ' and md.project_id = :5 '||
2062 ' and md.task_id = :6)';
2063 else
2064 where_stat := where_stat ||
2065 ' AND mbis.task_id = :6';
2066 end if;
2067 g_task_id := p_task_id;
2068 else
2069 where_stat := where_stat ||
2070 ' AND :6=-1 ';
2071 g_task_id :=-1;
2072 end if;
2073
2074 if p_category_name is not null then
2075 if subStr(p_category_name,1,3) = '-1:' then
2076 -- come from msc_bis_inv_cat_mv_tab
2077 where_stat := where_stat ||
2078 ' and -1 = :9 '||
2079 ' and mbis.category_set_id = :10 ' ||
2080 ' and mbis.category_name = :11 ';
2081 g_category_id := -1;
2082 g_category_name := subStr(p_category_name,4);
2083 else
2084 where_stat := where_stat ||
2085 ' AND EXISTS '||
2086 ' (select 1 '||
2087 ' from msc_item_categories mit ' ||
2088 ' where mit.organization_id = mbis.organization_id '||
2089 ' and mit.sr_instance_id = mbis.sr_instance_id '||
2090 ' and mit.inventory_item_id = mbis.inventory_item_id '||
2091 ' and -1 = :9 '||
2092 ' and mit.category_set_id = :10 '||
2093 ' and mit.category_name = :11 )';
2094 g_category_id := -1;
2095 g_category_name := p_category_name;
2096 end if;
2097 g_category_set_id := p_category_set_id;
2098
2099 elsif p_category_id is not null then
2100 if p_category_id < 0 then -- come from msc_bis_inv_cat_mv_tab
2101 where_stat := where_stat ||
2102 ' and mbis.sr_category_id = :9 '||
2103 ' and mbis.category_set_id = :10 '||
2104 ' and ''-1'' = :11 ';
2105 g_category_id := p_category_id*-1;
2106 g_category_name := '-1';
2107 else
2108 where_stat := where_stat ||
2109 ' AND EXISTS '||
2110 ' (select 1 '||
2111 ' from msc_item_categories mit ' ||
2112 ' where mit.organization_id = mbis.organization_id '||
2113 ' and mit.sr_instance_id = mbis.sr_instance_id '||
2114 ' and mit.inventory_item_id = mbis.inventory_item_id '||
2115 ' and mit.sr_category_id = :9 '||
2116 ' and mit.category_set_id = :10 ' ||
2117 ' and ''-1'' = :11 )';
2118 g_category_id := p_category_id;
2119 g_category_name := '-1';
2120 end if;
2121 g_category_set_id := p_category_set_id;
2122 else
2123 where_stat := where_stat ||
2124 ' AND :9=:10 '||
2125 ' and ''-1'' = :11 ';
2126 g_category_id :=-1;
2127 g_category_set_id :=-1;
2128 g_category_name := '-1';
2129 end if;
2130
2131 if p_product_family_id is not null then
2132 where_stat := where_stat ||
2133 ' AND EXISTS '||
2134 ' (select 1 '||
2135 ' from msc_bom_components mbc ' ||
2136 ' where mbc.organization_id = mbis.organization_id '||
2137 ' and mbc.sr_instance_id = mbis.sr_instance_id '||
2138 ' and mbc.plan_id = mbis.plan_id ' ||
2139 ' and mbc.inventory_item_id = mbis.inventory_item_id '||
2140 ' and mbc.using_assembly_id = :11 )';
2141 g_product_family_id := p_product_family_id;
2142 else
2143 where_stat := where_stat ||
2144 ' AND :11=-1 ';
2145 g_product_family_id :=-1;
2146 end if;
2147
2148 if p_date then
2149 if p_start_date is not null then
2150 where_stat := where_stat ||
2151 ' AND exists '||
2152 ' (select 1 '||
2153 ' from msc_demands md '||
2154 ' where md.plan_id = mbis.plan_id '||
2155 ' and md.demand_id = mbis.number1 '||
2156 ' and trunc(nvl(md.assembly_demand_comp_date,md.using_assembly_demand_date)) between :7 and :8)';
2157 g_start_date := trunc(p_start_date);
2158 g_end_date := trunc(p_end_date);
2159 else
2160 where_stat := where_stat ||
2161 ' AND :7=:8 ';
2162 g_start_date := sysdate;
2163 g_end_date := sysdate;
2164 end if;
2165 end if;
2166
2167
2168 return where_stat;
2169 END;
2170
2171 FUNCTION get_service_level(p_plan_id IN NUMBER,
2172 p_instance_id IN NUMBER,
2173 p_organization_id IN NUMBER,
2174 p_item_id IN NUMBER,
2175 p_start_date date default null,
2176 p_end_date date default null,
2177 p_use_old_demand_qty number default null) RETURN NUMBER IS
2178 the_cursor KPICurTyp;
2179 sql_stat varchar2(3000);
2180 where_stat varchar2(2000);
2181 v_org_id number;
2182 v_instance_id number;
2183 v_item_id number;
2184 v_start date;
2185 v_end date;
2186 v_qty number;
2187 v_qty2 number;
2188 v_service number;
2189 v_constraint number;
2190 v_plan_type number;
2191
2192 l_category_set_id number;
2193 l_category_set_str varchar2(100);
2194
2195 v_run_qty number;
2196 v_run_qty2 number;
2197 from_plan_node boolean := false;
2198
2199 BEGIN
2200 if ( p_instance_id is null and p_organization_id is null and p_item_id is null) then
2201 from_plan_node := true;
2202 end if;
2203
2204 l_category_set_id := MSC_ANALYSIS_PKG.get_cat_set_id(p_plan_id);
2205 if (l_category_set_id is null) then
2206 l_category_set_str := '';
2207 else
2208 l_category_set_str := ' AND mic.category_set_id = '||l_category_set_id;
2209 end if;
2210
2211
2212 select nvl(DAILY_RESOURCE_CONSTRAINTS,0)+
2213 nvl(WEEKLY_RESOURCE_CONSTRAINTS,0)+
2214 nvl(PERIOD_RESOURCE_CONSTRAINTS,0),
2215 plan_type
2216 into v_constraint, v_plan_type
2217 from msc_plans
2218 where plan_id = p_plan_id;
2219
2220 if v_plan_type <>4 and v_constraint = 0 then
2221 -- unconstrained plan is always 100%
2222 return 100;
2223 end if;
2224
2225 where_stat := 'WHERE md.plan_id = :1 ' ||
2226 ' AND md.sr_instance_id = mic.sr_instance_id '||
2227 ' AND md.organization_id = mic.organization_id '||
2228 ' AND md.inventory_item_id = mic.inventory_item_id '||
2229 l_category_set_str||
2230 ' AND md.origination_type in (6,7,8,9,11,15,22,29,30) ';
2231 if p_instance_id is not null or from_plan_node then
2232 where_stat := where_stat ||
2233 ' AND md.organization_id = :2 '||
2234 ' AND md.sr_instance_id = :3 ';
2235 v_org_id := p_organization_id;
2236 v_instance_id := p_instance_id;
2237 else
2238 where_stat := where_stat ||
2239 ' AND :2 = :3 ';
2240 v_org_id := -1;
2241 v_instance_id := -1;
2242 end if;
2243
2244 if p_item_id is not null then
2245 where_stat := where_stat ||
2246 ' AND md.inventory_item_id = :4 ';
2247 v_item_id := p_item_id;
2248 else
2249 where_stat := where_stat ||
2250 ' AND -1 = :4 ';
2251 v_item_id := -1;
2252 end if;
2253
2254 if p_start_date is not null then
2255 where_stat := where_stat ||
2256 ' AND trunc(md.USING_ASSEMBLY_DEMAND_DATE) BETWEEN :5 AND :6 ';
2257 v_start := trunc(p_start_date);
2258 v_end := trunc(p_end_date);
2259 else
2260 where_stat := where_stat ||
2261 ' AND :5 = :6 ';
2262 v_start := sysdate;
2263 v_end := sysdate;
2264 end if;
2265
2266 --if (p_use_old_demand_qty is null) then
2267 if v_plan_type <> 4 then
2268 sql_stat := 'SELECT sum(nvl(md.quantity_by_due_date,0)*nvl(md.probability,1)), '||
2269 ' sum(md.USING_REQUIREMENT_QUANTITY*nvl(md.probability,1)) ' ||
2270 ' FROM msc_demands md, ' ||
2271 ' msc_item_categories mic ' ||
2272 where_stat;
2273 --elsif (p_use_old_demand_qty = -1) then
2274 elsif v_plan_type = 4 then
2275 sql_stat := 'SELECT sum(nvl(md.old_demand_quantity,0)*nvl(md.probability,1)), '||
2276 ' sum(md.USING_REQUIREMENT_QUANTITY*nvl(md.probability,1)) ' ||
2277 ' FROM msc_demands md, ' ||
2278 ' msc_item_categories mic ' ||
2279 where_stat;
2280 end if;
2281
2282 if ( not(from_plan_node) ) then
2283 OPEN the_cursor FOR sql_stat USING p_plan_id,v_org_id,
2284 v_instance_id, v_item_id,
2285 v_start, v_end;
2286 FETCH the_cursor INTO v_qty, v_qty2;
2287 CLOSE the_cursor;
2288 else
2289 v_qty := 0;
2290 v_qty2 := 0;
2291
2292 open c_plan_orgs(p_plan_id);
2293 loop
2294 fetch c_plan_orgs into v_instance_id, v_org_id;
2295 exit when c_plan_orgs%notfound;
2296
2297 OPEN the_cursor FOR sql_stat USING p_plan_id,v_org_id,
2298 v_instance_id, v_item_id, v_start, v_end;
2299 FETCH the_cursor INTO v_run_qty, v_run_qty2;
2300 CLOSE the_cursor;
2301
2302 v_qty := v_qty + nvl(v_run_qty,0);
2303 v_qty2 := v_qty2 + nvl(v_run_qty2,0);
2304 end loop;
2305 close c_plan_orgs;
2306 end if;
2307
2308 if nvl(v_qty2,0) =0 then -- there is no demand, will show 100%
2309 v_service := 100;
2310 elsif nvl(v_qty,0)=0 then
2311 v_service := 0;
2312 else
2313 v_service := round(v_qty/v_qty2*100,6);
2314 end if;
2315
2316 return v_service;
2317
2318 END get_service_level;
2319
2320 FUNCTION get_tp_cost(p_plan_id IN NUMBER,
2321 p_instance_id IN NUMBER,
2322 p_organization_id IN NUMBER,
2323 p_item_id IN NUMBER,
2324 p_start_date date default null,
2325 p_end_date date default null,
2326 p_planner_code varchar2 default null) RETURN NUMBER IS
2327 the_cursor KPICurTyp;
2328 sql_stat varchar2(5000);
2329 where_stat varchar2(3000);
2330 v_org_id number;
2331 v_instance_id number;
2332 v_item_id number;
2333 v_start date;
2334 v_end date;
2335 v_cost number;
2336 v_planner_code varchar2(50);
2337
2338 l_category_set_id number;
2339 l_category_set_str varchar2(100);
2340
2341 v_run_qty number;
2342 from_plan_node boolean := false;
2343
2344 BEGIN
2345 if ( p_instance_id is null and p_organization_id is null and p_item_id is null) then
2346 from_plan_node := true;
2347 end if;
2348
2349 l_category_set_id := MSC_ANALYSIS_PKG.get_cat_set_id(p_plan_id);
2350 if (l_category_set_id is null) then
2351 l_category_set_str := '';
2352 else
2353 l_category_set_str := ' AND mic.category_set_id = '||l_category_set_id;
2354 end if;
2355
2356 where_stat := ' WHERE ms.plan_id = :1 ' ||
2357 ' AND ms.sr_instance_id = mic.sr_instance_id '||
2358 ' AND ms.organization_id = mic.organization_id '||
2359 ' AND ms.inventory_item_id = mic.inventory_item_id '||
2360 l_category_set_str||
2361 ' and ms.organization_id != ms.source_organization_id '||
2362 ' and ms.order_type in (5,11) '||
2363 ' and ms.plan_id = msi.plan_id '||
2364 ' and ms.organization_id = msi.organization_id '||
2365 ' and ms.sr_instance_id = msi.sr_instance_id '||
2366 ' and ms.inventory_item_id = msi.inventory_item_id '||
2367 ' and ms.plan_id = mism.plan_id '||
2368 ' and ms.organization_id = mism.to_organization_id '||
2369 ' and ms.sr_instance_id = mism.sr_instance_id '||
2370 ' and ms.source_organization_id = mism.from_organization_id '||
2371 ' and ms.source_sr_instance_id = mism.sr_instance_id2'||
2372 ' and ms.ship_method = mism.ship_method ';
2373 if p_instance_id is not null or from_plan_node then
2374 where_stat := where_stat ||
2375 ' AND ms.organization_id = :2 '||
2376 ' AND ms.sr_instance_id = :3 ';
2377 v_org_id := p_organization_id;
2378 v_instance_id := p_instance_id;
2379 else
2380 where_stat := where_stat ||' AND :2 = :3 ';
2381 v_org_id := -1;
2382 v_instance_id := -1;
2383 end if;
2384
2385 if p_item_id is not null then
2386 where_stat := where_stat ||
2387 ' AND ms.inventory_item_id = :4 ';
2388 v_item_id := p_item_id;
2389 else
2390 where_stat := where_stat ||' AND -1 = :4 ';
2391 v_item_id := -1;
2392 end if;
2393
2394
2395
2396 if p_start_date is not null then
2397 where_stat := where_stat ||
2398 ' AND trunc(ms.new_dock_date) BETWEEN :5 AND :6 ';
2399 v_start := trunc(p_start_date);
2400 v_end := trunc(p_end_date);
2401 else
2402 where_stat := where_stat ||
2403 ' AND :5 = :6 ';
2404 v_start := sysdate;
2405 v_end := sysdate;
2406 end if;
2407
2408 if p_planner_code is not null then
2409 where_stat := where_stat ||
2410 ' AND msi.planner_code = :7 ';
2411 v_planner_code := p_planner_code;
2412 else
2413 where_stat := where_stat ||' AND ''-1'' = :4 ';
2414 v_planner_code := '''-1''';
2415 end if;
2416
2417 sql_stat := ' select round(sum(nvl(((ms.new_order_quantity * '||
2418 ' msi.unit_weight) '||
2419 ' * mism.cost_per_weight_unit),0)),6) '||
2420 ' from msc_supplies ms, '||
2421 ' msc_system_items msi, '||
2422 ' msc_item_categories mic, '||
2423 ' msc_interorg_ship_methods mism '|| where_stat;
2424 if ( not(from_plan_node) ) then
2425 OPEN the_cursor FOR sql_stat USING p_plan_id,v_org_id,
2426 v_instance_id, v_item_id,
2427 v_start, v_end, p_planner_code;
2428 FETCH the_cursor INTO v_cost;
2429 CLOSE the_cursor;
2430 else
2431 v_cost := 0;
2432 open c_plan_orgs(p_plan_id);
2433 loop
2434 fetch c_plan_orgs into v_instance_id, v_org_id;
2435 exit when c_plan_orgs%notfound;
2436
2437 OPEN the_cursor FOR sql_stat USING p_plan_id,v_org_id,
2438 v_instance_id, v_item_id, v_start, v_end, p_planner_code;
2439 FETCH the_cursor INTO v_run_qty;
2440 CLOSE the_cursor;
2441
2442 v_cost := v_cost + nvl(v_run_qty,0);
2443 end loop;
2444 close c_plan_orgs;
2445 end if;
2446
2447
2448 return v_cost;
2449
2450 END get_tp_cost;
2451
2452 FUNCTION get_target_service_level(p_plan_id IN NUMBER,
2453 p_instance_id IN NUMBER,
2454 p_organization_id IN NUMBER,
2455 p_item_id IN NUMBER,
2456 p_start_date date default null,
2457 p_end_date date default null) RETURN NUMBER IS
2458 the_cursor KPICurTyp;
2459 sql_stat varchar2(3000);
2460 where_stat varchar2(2000);
2461 v_org_id number;
2462 v_instance_id number;
2463 v_item_id number;
2464 v_start date;
2465 v_end date;
2466 v_qty number;
2467 v_count number;
2468
2469 l_category_set_id number;
2470 l_category_set_str varchar2(100);
2471
2472 v_run_qty number;
2473 v_dmd_count number := 0;
2474 from_plan_node boolean := false;
2475 BEGIN
2476
2477 if ( p_instance_id is null and p_organization_id is null and p_item_id is null) then
2478 from_plan_node := true;
2479 end if;
2480
2481 l_category_set_id := MSC_ANALYSIS_PKG.get_cat_set_id(p_plan_id);
2482 if (l_category_set_id is null) then
2483 l_category_set_str := '';
2484 else
2485 l_category_set_str := ' AND mic.category_set_id = '||l_category_set_id;
2486 end if;
2487
2488
2489 where_stat := 'WHERE md.plan_id = :1 ' ||
2490 ' AND md.sr_instance_id = mic.sr_instance_id '||
2491 ' AND md.organization_id = mic.organization_id '||
2492 ' AND md.inventory_item_id = mic.inventory_item_id '||
2493 l_category_set_str||
2494 ' AND md.origination_type in (6,7,8,9,11,15,22,29,30) ';
2495 if p_instance_id is not null or from_plan_node then
2496 where_stat := where_stat ||
2497 ' AND md.organization_id = :2 '||
2498 ' AND md.sr_instance_id = :3 ';
2499 v_org_id := p_organization_id;
2500 v_instance_id := p_instance_id;
2501 else
2502 where_stat := where_stat ||
2503 ' AND :2 = :3 ';
2504 v_org_id := -1;
2505 v_instance_id := -1;
2506 end if;
2507
2508 if p_item_id is not null then
2509 where_stat := where_stat ||
2510 ' AND md.inventory_item_id = :4 ';
2511 v_item_id := p_item_id;
2512 else
2513 where_stat := where_stat ||
2514 ' AND -1 = :4 ';
2515 v_item_id := -1;
2516 end if;
2517
2518 if p_start_date is not null then
2519 where_stat := where_stat ||
2520 ' AND trunc(md.USING_ASSEMBLY_DEMAND_DATE) BETWEEN :5 AND :6 ';
2521 v_start := trunc(p_start_date);
2522 v_end := trunc(p_end_date);
2523 else
2524 where_stat := where_stat ||
2525 ' AND :5 = :6 ';
2526 v_start := sysdate;
2527 v_end := sysdate;
2528 end if;
2529
2530 sql_stat := 'SELECT avg(md.service_level), count(*) '||
2531 ' FROM msc_demands md, ' ||
2532 ' msc_item_categories mic ' ||
2533 where_stat;
2534 if ( not(from_plan_node) ) then
2535 OPEN the_cursor FOR sql_stat USING p_plan_id,v_org_id,
2536 v_instance_id, v_item_id,
2537 v_start, v_end;
2538 FETCH the_cursor INTO v_qty, v_count;
2539 CLOSE the_cursor;
2540 else
2541 v_qty := 0;
2542 open c_plan_orgs(p_plan_id);
2543 loop
2544 fetch c_plan_orgs into v_instance_id, v_org_id;
2545 exit when c_plan_orgs%notfound;
2546 OPEN the_cursor FOR sql_stat USING p_plan_id,v_org_id,
2547 v_instance_id, v_item_id, v_start, v_end;
2548 FETCH the_cursor INTO v_run_qty, v_count;
2549 CLOSE the_cursor;
2550 v_qty := v_qty + (nvl(v_run_qty, 0) * nvl(v_count,0));
2551 v_dmd_count := v_dmd_count + nvl(v_count,0);
2552 end loop;
2553 close c_plan_orgs;
2554
2555 if ( nvl(v_dmd_count,0) = 0 ) then
2556 v_qty := 0;
2557 else
2558 v_qty := v_qty / v_dmd_count;
2559 end if;
2560 end if;
2561
2562 return v_qty;
2563
2564 END get_target_service_level;
2565
2566
2567 FUNCTION service_data_exist(p_plan_id IN NUMBER,
2568 p_instance_id IN NUMBER,
2569 p_organization_id IN NUMBER,
2570 p_item_id IN NUMBER) RETURN BOOLEAN IS
2571 the_cursor KPICurTyp;
2572 sql_stat varchar2(1000);
2573 where_stat varchar2(1000);
2574 v_org_id number;
2575 v_instance_id number;
2576 v_item_id number;
2577 v_temp number;
2578 BEGIN
2579
2580 sql_stat := ' SELECT 1 ' ||
2581 ' FROM msc_demands ';
2582
2583 where_stat := 'WHERE plan_id = :1 '||
2584 ' AND origination_type in (29) ';
2585 if p_instance_id is not null then
2586 where_stat := where_stat ||
2587 ' AND organization_id = :2 '||
2588 ' AND sr_instance_id = :3 ';
2589 v_org_id := p_organization_id;
2590 v_instance_id := p_instance_id;
2591 else
2592 where_stat := where_stat ||
2593 ' AND :2 = :3 ';
2594 v_org_id := -1;
2595 v_instance_id := -1;
2596 end if;
2597
2598 if p_item_id is not null then
2599 where_stat := where_stat ||
2600 ' AND inventory_item_id = :4 ';
2601 v_item_id := p_item_id;
2602 else
2603 where_stat := where_stat ||
2604 ' AND -1 = :4 ';
2605 v_item_id := -1;
2606 end if;
2607
2608 sql_stat := sql_stat || where_stat||
2609 ' and quantity_by_due_date is not null ' ||
2610 ' and rownum = 1 ';
2611
2612 OPEN the_cursor FOR sql_stat USING p_plan_id,v_org_id,
2613 v_instance_id, v_item_id;
2614 FETCH the_cursor INTO v_temp;
2615 CLOSE the_cursor;
2616
2617 if v_temp = 1 then
2618 return true;
2619 else
2620 return false;
2621 end if;
2622
2623 END service_data_exist;
2624
2625 FUNCTION service_target(p_plan IN NUMBER, p_instance_id IN NUMBER,
2626 p_org_id IN NUMBER, p_item_id IN NUMBER) RETURN NUMBER IS
2627 l_target NUMBER;
2628
2629 CURSOR plan_target IS
2630 SELECT service_level
2631 FROM msc_plans
2632 WHERE plan_id = p_plan;
2633
2634 CURSOR org_target IS
2635 SELECT service_level
2636 FROM msc_trading_partners
2637 WHERE sr_instance_id = p_instance_id
2638 AND sr_tp_id=p_org_id;
2639
2640 CURSOR item_target IS
2641 SELECT service_level
2642 FROM msc_system_items
2643 WHERE plan_id = p_plan
2644 AND sr_instance_id = p_instance_id
2645 AND organization_id= p_org_id
2646 AND inventory_item_id = p_item_id;
2647
2648
2649 BEGIN
2650 if p_item_id is not null then
2651 OPEN item_target;
2652 FETCH item_target INTO l_target;
2653 CLOSE item_target;
2654 if l_target is null then
2655 if p_org_id is not null then
2656 OPEN org_target;
2657 FETCH org_target INTO l_target;
2658 CLOSE org_target;
2659 if l_target is null then
2660 OPEN plan_target;
2661 FETCH plan_target INTO l_target;
2662 CLOSE plan_target;
2663 end if;
2664 else
2665 OPEN plan_target;
2666 FETCH plan_target INTO l_target;
2667 CLOSE plan_target;
2668 end if;
2669 end if;
2670 elsif p_org_id is not null then
2671 OPEN org_target;
2672 FETCH org_target INTO l_target;
2673 CLOSE org_target;
2674 if l_target is null then
2675 OPEN plan_target;
2676 FETCH plan_target INTO l_target;
2677 CLOSE plan_target;
2678 end if;
2679 else
2680 OPEN plan_target;
2681 FETCH plan_target INTO l_target;
2682 CLOSE plan_target;
2683 end if;
2684 return nvl(l_target,0);
2685 END service_target;
2686
2687 FUNCTION service_target_trend(p_plan_id IN NUMBER, p_instance_id IN NUMBER,
2688 p_org_id IN NUMBER, p_item_id IN NUMBER) RETURN VARCHAR2 IS
2689 l_target NUMBER;
2690 l_target_list varchar2(500);
2691 BEGIN
2692
2693 l_target := service_target(p_plan_id, p_instance_id, p_org_id, p_item_id);
2694
2695 FOR j in 1 .. g_period_name.LAST LOOP
2696
2697 l_target_list := l_target_list ||g_param||
2698 fnd_number.number_to_canonical(nvl(l_target,0));
2699 END LOOP;
2700
2701
2702 return l_target_list;
2703 END service_target_trend;
2704
2705 FUNCTION construct_sup_where(p_organization_id number,
2706 p_instance_id number,
2707 p_item_id number,
2708 p_sup_id number,
2709 p_sup_site_id number) RETURN varchar2 IS
2710 where_stat varchar2(1000);
2711
2712 BEGIN
2713 if p_instance_id is not null then
2714 where_stat :=
2715 -- ' AND sup.organization_id = :2 ' ||
2716 ' AND sup.sr_instance_id = :3 ';
2717 g_org_id := p_organization_id;
2718 g_instance_id := p_instance_id;
2719 else
2720 where_stat := where_stat ||
2721 ' AND :3 = -1 ';
2722 g_org_id := -1;
2723 g_instance_id := -1;
2724 end if;
2725
2726 if p_item_id is not null then
2727 where_stat := where_stat ||
2728 ' AND sup.inventory_item_id = :4 ';
2729 g_item_id := p_item_id;
2730 else
2731 where_stat := where_stat ||
2732 ' AND :4 = -1 ';
2733 g_item_id := -1;
2734 end if;
2735
2736 if p_sup_id is not null then
2737 where_stat := where_stat ||
2738 ' AND sup.supplier_id = :5 ';
2739 g_sup_id := p_sup_id;
2740 else
2741 where_stat := where_stat ||
2742 ' AND :5 = -1 ';
2743 g_sup_id := -1;
2744 end if;
2745
2746 if p_sup_site_id is not null then
2747 where_stat := where_stat ||
2748 ' AND sup.supplier_site_id = :6';
2749 g_sup_site_id := p_sup_site_id;
2750 else
2751 where_stat := where_stat ||
2752 ' AND :6 = -1';
2753 g_sup_site_id:= -1;
2754 end if;
2755
2756 --dbms_output.put_line(where_stat);
2757 return where_stat;
2758
2759 END;
2760 --Procedure call_get_actuals IS
2761 --l_var varchar2(2000);
2762 --BEGIN
2763 --get_trend_actuals(2157,201,207,4,14661,null,
2764 -- null, null, null,null, null,null,
2765 -- 243, null, 12271, 7023,l_var);
2766 --exception
2767 -- when others then
2768 --dbms_output.put_line(sqlerrm);
2769 --END;
2770
2771 Procedure refresh_kpi_data(p_plan_id number) IS
2772 l_err_buf VARCHAR2(4000);
2773 l_ret_code NUMBER;
2774
2775 cursor show_kpi is
2776 select display_kpi, curr_plan_type
2777 from msc_plans
2778 where plan_id = p_plan_id;
2779
2780 v_show_kpi number;
2781 v_plan_type number;
2782 BEGIN
2783 OPEN show_kpi;
2784 FETCH show_kpi INTO v_show_kpi,v_plan_type;
2785 CLOSE show_kpi;
2786
2787 if nvl(v_show_kpi,1) = 1 then
2788 FND_FILE.PUT_LINE(FND_FILE.LOG,'--- refreshing summary data for kpi ---');
2789 msc_get_bis_values.refresh_data(l_err_buf, l_ret_code,p_plan_id, v_plan_type);
2790 else -- set kpi status as not refresh
2791 msc_get_bis_values.set_kpi_refresh_status(p_plan_id,'NOT REFRESH');
2792 end if;
2793 exception when others then
2794 FND_FILE.PUT_LINE(FND_FILE.LOG,'refreshing kpi summary data fails');
2795 END refresh_kpi_data;
2796
2797
2798
2799 Procedure refresh_data(errbuf OUT NOCOPY VARCHAR2,
2800 retcode OUT NOCOPY NUMBER,
2801 p_plan_id number) is
2802 l_plan_type number;
2803 cursor c_plan_type is
2804 select plan_type from msc_plans where plan_id =p_plan_id;
2805
2806
2807 BEGIN
2808
2809 open c_plan_type;
2810 fetch c_plan_type into l_plan_type;
2811 close c_plan_type;
2812
2813 refresh_data(errbuf,retcode,p_plan_id,l_plan_type);
2814
2815 end;
2816
2817
2818
2819 Procedure refresh_data(errbuf OUT NOCOPY VARCHAR2,
2820 retcode OUT NOCOPY NUMBER,
2821 p_plan_id number,
2822 p_plan_type number) IS
2823 p_request_id number;
2824 BEGIN
2825 FND_FILE.PUT_LINE(FND_FILE.LOG,'start refreshing');
2826 -- set kpi as refreshing
2827 set_kpi_refresh_status(p_plan_id,'REFRESHING');
2828
2829 for a in 1..5 loop
2830
2831 p_request_id := fnd_request.submit_request(
2832 'MSC',
2833 'MSCKPIREF',
2834 null,
2835 null,
2836 false,
2837 p_plan_id,
2838 a,
2839 p_plan_type);
2840 FND_FILE.PUT_LINE(FND_FILE.LOG,'request id is ='||p_request_id);
2841 end loop;
2842
2843 commit;
2844
2845 exception when others then
2846 set_kpi_refresh_status(p_plan_id,'NOT REFRESH');
2847 END refresh_data;
2848
2849 Function IsKPIAvail(p_plan_id number) return number is
2850 cursor check_kpi is
2851 select kpi_refresh
2852 from msc_plans
2853 where plan_id = p_plan_id;
2854
2855 v_kpi_refresh number;
2856 begin
2857
2858 OPEN check_kpi;
2859 FETCH check_kpi INTO v_kpi_refresh;
2860 CLOSE check_kpi;
2861
2862 if v_kpi_refresh = 0 then -- REFRESHING
2863 return 2;
2864 elsif v_kpi_refresh = 5 then -- REFRESHE DONE
2865 return 1;
2866 else
2867 return 0; -- NOT REFRESH
2868 end if;
2869
2870 end IsKPIAvail;
2871
2872
2873 PROCEDURE set_kpi_refresh_status(p_plan_id number,p_status varchar2) is
2874 v_kpi_refresh number;
2875 v_status number;
2876 begin
2877 select kpi_refresh
2878 into v_kpi_refresh
2879 from msc_plans
2880 where plan_id = p_plan_id;
2881
2882 if p_status = 'NOT REFRESH' then
2883 v_status := -1;
2884 elsif p_status = 'REFRESHING' then
2885 v_status :=0;
2886 elsif p_status = 'ONE_DONE' then
2887 v_status := v_kpi_refresh + 1;
2888 end if;
2889
2890 MSC_UTIL.MSC_DEBUG('v_status='||v_status);
2891 update msc_plans
2892 set kpi_refresh = decode(v_status,0,0,-1,-1,kpi_refresh+1)
2893 where plan_id = p_plan_id;
2894
2895 commit;
2896 end set_kpi_refresh_status;
2897
2898 Procedure refresh_one_table(errbuf OUT NOCOPY VARCHAR2,
2899 retcode OUT NOCOPY NUMBER,
2900 p_plan_id number,
2901 p_kpi_table number,
2902 p_plan_type number) IS
2903 BEGIN
2904 if p_kpi_table =1 then
2905
2906 MSC_UTIL.MSC_DEBUG('refreshing MSC_BIS_INV_DATE_MV_TAB table for plan id '||p_plan_id);
2907
2908 delete from msc_bis_inv_date_mv_tab
2909 where plan_id = p_plan_id;
2910 if p_plan_type = 8 then -- should change to srp
2911
2912 insert into msc_bis_inv_date_mv_tab(
2913 LAST_UPDATE_DATE,
2914 LAST_UPDATED_BY,
2915 CREATION_DATE,
2916 CREATED_BY,
2917 LAST_UPDATE_LOGIN,
2918 mds_price,
2919 mds_cost,
2920 inventory_cost,
2921 production_cost,
2922 purchasing_cost,
2923 demand_penalty_cost,
2924 carrying_cost,
2925 plan_id,
2926 organization_id,
2927 sr_instance_id,
2928 detail_date,
2929 inventory_value,
2930 planner_code)
2931 select
2932 sysdate,
2933 -1,
2934 sysdate,
2935 -1,
2936 -1,
2937 sum(nvl(mbid.mds_price,0)),
2938 sum(nvl(mbid.mds_cost,0)),
2939 sum(nvl(mbid.inventory_cost,0)),
2940 sum(nvl(mbid.production_cost,0)),
2941 sum(nvl(mbid.purchasing_cost,0)),
2942 sum(nvl(mbid.demand_penalty_cost,0)+
2943 nvl(mbid.supplier_overcap_cost,0)),
2944 sum(nvl(mbid.carrying_cost,0)),
2945 mbid.plan_id,
2946 mbid.organization_id,
2947 mbid.sr_instance_id,
2948 mbid.detail_date,
2949 sum(nvl(mbid.inventory_value,0)),
2950 msi.planner_code
2951 from msc_bis_inv_detail mbid,
2952 msc_system_items msi
2953 where mbid.plan_id = p_plan_id
2954 and nvl(mbid.period_type,0) = 0
2955 and mbid.organization_id = msi.organization_id
2956 and mbid.sr_instance_id = msi.sr_instance_id
2957 and mbid.plan_id = msi.plan_id
2958 and mbid.inventory_item_id = msi.inventory_item_id
2959 group by mbid.plan_id,
2960 mbid.organization_id,
2961 mbid.sr_instance_id,
2962 mbid.detail_date,
2963 msi.planner_code;
2964
2965 else
2966 insert into msc_bis_inv_date_mv_tab(
2967 LAST_UPDATE_DATE,
2968 LAST_UPDATED_BY,
2969 CREATION_DATE,
2970 CREATED_BY,
2971 LAST_UPDATE_LOGIN,
2972 mds_price,
2973 mds_cost,
2974 inventory_cost,
2975 production_cost,
2976 purchasing_cost,
2977 demand_penalty_cost,
2978 carrying_cost,
2979 plan_id,
2980 organization_id,
2981 sr_instance_id,
2982 detail_date)
2983 select
2984 sysdate,
2985 -1,
2986 sysdate,
2987 -1,
2988 -1,
2989 sum(nvl(mds_price,0)),
2990 sum(nvl(mds_cost,0)),
2991 sum(nvl(inventory_cost,0)),
2992 sum(nvl(production_cost,0)),
2993 sum(nvl(purchasing_cost,0)),
2994 sum(nvl(demand_penalty_cost,0)+
2995 nvl(supplier_overcap_cost,0)),
2996 sum(nvl(carrying_cost,0)),
2997 plan_id,
2998 organization_id,
2999 sr_instance_id,
3000 detail_date
3001 from msc_bis_inv_detail
3002 where plan_id = p_plan_id
3003 and nvl(period_type,0) = 0
3004 group by plan_id,
3005 organization_id,
3006 sr_instance_id,
3007 detail_date;
3008 end if; -- if p_plan_type =
3009 elsif p_kpi_table =2 then
3010 MSC_UTIL.MSC_DEBUG('refreshing MSC_BIS_INV_CAT_MV_TAB table for plan id '||p_plan_id);
3011 delete from msc_bis_inv_cat_mv_tab
3012 where plan_id = p_plan_id;
3013
3014 insert into msc_bis_inv_cat_mv_tab(
3015 LAST_UPDATE_DATE,
3016 LAST_UPDATED_BY,
3017 CREATION_DATE,
3018 CREATED_BY,
3019 LAST_UPDATE_LOGIN,
3020 mds_price,
3021 mds_cost,
3022 inventory_cost,
3023 production_cost,
3024 purchasing_cost,
3025 demand_penalty_cost,
3026 carrying_cost,
3027 plan_id,
3028 organization_id,
3029 sr_instance_id,
3030 sr_category_id,
3031 category_name,
3032 category_set_id,
3033 detail_date)
3034 select
3035 sysdate,
3036 -1,
3037 sysdate,
3038 -1,
3039 -1,
3040 sum(nvl(mbis.mds_price,0)),
3041 sum(nvl(mbis.mds_cost,0)),
3042 sum(nvl(mbis.inventory_cost,0)),
3043 sum(nvl(mbis.production_cost,0)),
3044 sum(nvl(mbis.purchasing_cost,0)),
3045 sum(nvl(mbis.demand_penalty_cost,0)+
3046 nvl(mbis.supplier_overcap_cost,0)),
3047 sum(nvl(mbis.carrying_cost,0)),
3048 mbis.plan_id,
3049 mbis.organization_id,
3050 mbis.sr_instance_id,
3051 mit.sr_category_id,
3052 mit.category_name,
3053 mit.category_set_id,
3054 mbis.detail_date
3055 from msc_bis_inv_detail mbis,
3056 msc_item_categories mit
3057 where mbis.plan_id = p_plan_id
3058 and mit.organization_id = mbis.organization_id
3059 and mit.sr_instance_id = mbis.sr_instance_id
3060 and mit.inventory_item_id = mbis.inventory_item_id
3061 and nvl(mbis.period_type,0) = 0
3062 group by mbis.plan_id,
3063 mbis.organization_id,
3064 mbis.sr_instance_id,
3065 mit.sr_category_id,
3066 mit.category_name,
3067 mit.category_set_id,
3068 mbis.detail_date;
3069
3070 elsif p_kpi_table = 3 then
3071 MSC_UTIL.MSC_DEBUG('refreshing MSC_DEMAND_MV_TAB table for plan id '||p_plan_id);
3072 delete from msc_demand_mv_tab
3073 where plan_id = p_plan_id;
3074
3075 insert into msc_demand_mv_tab(
3076 LAST_UPDATE_DATE,
3077 LAST_UPDATED_BY,
3078 CREATION_DATE,
3079 CREATED_BY,
3080 LAST_UPDATE_LOGIN,
3081 plan_id,
3082 organization_id,
3083 sr_instance_id,
3084 demand_count)
3085 select
3086 sysdate,
3087 -1,
3088 sysdate,
3089 -1,
3090 -1,
3091 plan_id,
3092 organization_id,
3093 sr_instance_id,
3094 count(*)
3095 from msc_demands
3096 where origination_type in (6,7,8,9,10,11,12,15,22,24,27,29,30)
3097 and plan_id = p_plan_id
3098 group by plan_id,
3099 organization_id,
3100 sr_instance_id;
3101
3102 elsif p_kpi_table = 4 then
3103 MSC_UTIL.MSC_DEBUG('refreshing MSC_LATE_ORDER_MV_TAB table for plan id '||p_plan_id);
3104 delete from msc_late_order_mv_tab
3105 where plan_id = p_plan_id;
3106
3107 insert into msc_late_order_mv_tab(
3108 LAST_UPDATE_DATE,
3109 LAST_UPDATED_BY,
3110 CREATION_DATE,
3111 CREATED_BY,
3112 LAST_UPDATE_LOGIN,
3113 plan_id,
3114 organization_id,
3115 sr_instance_id,
3116 late_order_count)
3117 select
3118 sysdate,
3119 -1,
3120 sysdate,
3121 -1,
3122 -1,
3123 plan_id,
3124 organization_id,
3125 sr_instance_id,
3126 count(distinct number1)
3127 from msc_exception_details
3128 where exception_type in (13,14,24,26)
3129 and plan_id = p_plan_id
3130 group by plan_id,
3131 organization_id,
3132 sr_instance_id;
3133
3134 elsif p_kpi_table = 5 then
3135 MSC_UTIL.MSC_DEBUG('refreshing MSC_BIS_RES_DATE_MV_TAB table for plan id '||p_plan_id);
3136 delete from msc_bis_res_date_mv_tab
3137 where plan_id = p_plan_id;
3138
3139 insert into msc_bis_res_date_mv_tab(
3140 LAST_UPDATE_DATE,
3141 LAST_UPDATED_BY,
3142 CREATION_DATE,
3143 CREATED_BY,
3144 LAST_UPDATE_LOGIN,
3145 plan_id,
3146 organization_id,
3147 sr_instance_id,
3148 resource_date,
3149 utilization,
3150 util_count,
3151 util_sum)
3152 select
3153 sysdate,
3154 -1,
3155 sysdate,
3156 -1,
3157 -1,
3158 res.plan_id,
3159 res.organization_id,
3160 res.sr_instance_id,
3161 res.resource_date,
3162 avg(nvl(res.utilization,0)),
3163 count(nvl(res.utilization,0)),
3164 sum(nvl(res.utilization,0))
3165 from msc_department_resources mdr,
3166 msc_bis_res_summary res
3167 where mdr.department_id = res.department_id
3168 AND mdr.resource_id = res.resource_id
3169 AND mdr.plan_id = res.plan_id
3170 AND mdr.sr_instance_id = res.sr_instance_id
3171 AND mdr.organization_id = res.organization_id
3172 and mdr.plan_id = p_plan_id
3173 AND nvl(res.period_type,0) = 0
3174 group by res.plan_id,
3175 res.organization_id,
3176 res.sr_instance_id,
3177 res.resource_date;
3178 end if;
3179 commit;
3180 msc_get_bis_values.set_kpi_refresh_status(p_plan_id,'ONE_DONE');
3181 exception when no_data_found then
3182 msc_get_bis_values.set_kpi_refresh_status(p_plan_id,'ONE_DONE');
3183 END refresh_one_table;
3184
3185 Procedure ui_post_plan(errbuf OUT NOCOPY VARCHAR2,
3186 retcode OUT NOCOPY NUMBER,
3187 p_plan_id IN number) IS
3188
3189 lv_msc_schema VARCHAR2(30);
3190 v_tree_exist number;
3191
3192 Cursor msc_schema IS
3193 SELECT a.oracle_username
3194 FROM FND_ORACLE_USERID a, FND_PRODUCT_INSTALLATIONS b
3195 WHERE a.oracle_id = b.oracle_id
3196 AND b.application_id= 724;
3197
3198 Cursor tree_snap IS
3199 SELECT 1
3200 FROM all_objects
3201 WHERE object_name = 'MSC_SUPPLIER_TREE_MV'
3202 AND owner = lv_msc_schema;
3203
3204 Cursor plan_c is
3205 select display_kpi, plan_type,AUTO_RELEASE_METHOD,generate_worksheet
3206 from msc_plans
3207 where plan_id = p_plan_id;
3208
3209 v_plan_type number;
3210 v_show_kpi number;
3211 l_autorelease number;
3212 l_generate_worksheet number;
3213
3214 cursor c_plan_archive is
3215 select nvl(archive_flag,2)
3216 from msc_plans
3217 where plan_id = p_plan_id;
3218 l_archive_flag number;
3219 l_req_id number;
3220 Begin
3221
3222 if v_tree_exist =1 then
3223 MSC_UTIL.msc_debug('---- refreshing tree mv----');
3224 DBMS_SNAPSHOT.REFRESH( lv_msc_schema||'.MSC_SUPPLIER_TREE_MV');
3225 end if;
3226 OPEN plan_c;
3227 FETCH plan_c INTO v_show_kpi, v_plan_type, l_autorelease,l_generate_worksheet;
3228 CLOSE plan_c;
3229
3230 msc_get_bis_values.refresh_kpi_data(p_plan_id);
3231
3232 msc_launch_plan_pk.purge_user_notes_data(p_plan_id);
3233
3234 msc_netchange_pkg.compare_plan_need_refresh(p_plan_id);
3235 if nvl(l_autorelease,1) in (1,2) then
3236 MSC_pers_queries.purge_plan(p_plan_id);
3237 end if;
3238 if v_plan_type in (4,9) then
3239 MSC_ANALYSIS_SAFETY_STOCK_PERF.schedule_aggregate(p_plan_id);
3240 end if ;
3241 --msd_liability.run_liability_flow_ascp(errbuf,retcode,p_plan_id);
3242
3243 if v_plan_type in (8,9) then -- srp plan
3244 msc_drp_util.retrieve_exp_version(p_plan_id);
3245 end if;
3246 if v_plan_type = 8 then -- srp plan
3247 MSC_PQ_UTILS.execute_plan_worklists(errbuf, retcode,
3248 p_plan_id);
3249 end if;
3250 if l_generate_worksheet = 1 then -- ASCP worksheets
3251 MSC_PQ_UTILS.execute_plan_worksheets(errbuf, retcode,
3252 p_plan_id);
3253 end if;
3254
3255 --pabram..phub
3256 if (nvl(v_show_kpi, 1)=1 and v_plan_type not in (5)
3257 and nvl(fnd_profile.value('MSC_APCC_AFTER_LAUNCH'),1) <> 2) then
3258 open c_plan_archive;
3259 fetch c_plan_archive into l_archive_flag;
3260 close c_plan_archive;
3261 l_req_id := fnd_request.submit_request('MSC','MSCHUBA',NULL, NULL, FALSE, p_plan_id, null, l_archive_flag);
3262 commit;
3263 else
3264 msc_util.msc_debug('MSCHUBA not invoked, v_show_kpi='||v_show_kpi||', v_plan_type='||v_plan_type);
3265 end if;
3266 --pabram..phub ends
3267
3268 -- Synchronize ASCP ADF UI data.
3269 if v_plan_type in (1) then
3270 msc_hp_util.sync_ui_data(p_plan_id, 2);
3271 end if;
3272
3273 End ui_post_plan;
3274
3275
3276 END Msc_Get_Bis_Values;