[Home] [Help]
PACKAGE BODY: APPS.MSC_ANALYSIS_PKG
Source
1 PACKAGE BODY MSC_ANALYSIS_PKG AS
2 /* $Header: MSCANLSB.pls 120.17 2007/12/21 22:20:41 minduvad ship $ */
3
4 g_plan_viewby CONSTANT NUMBER :=0;
5 g_org_viewby CONSTANT NUMBER :=1;
6 g_category_viewby CONSTANT NUMBER :=2;
7 g_cust_class_viewby CONSTANT NUMBER :=3;
8 g_item_viewby CONSTANT NUMBER :=4;
9 g_period_viewby CONSTANT NUMBER :=5;
10 g_week_viewby CONSTANT NUMBER :=6;
11 g_demand_class_viewby CONSTANT NUMBER :=7;
12 g_plan_org_cate_viewby CONSTANT NUMBER :=8;
13
14 --g_plan_type CONSTANT NUMBER :=4;
15
16 g_target_sl CONSTANT NUMBER := 1;
17 g_attained_sl CONSTANT NUMBER := 2;
18 g_tp_cost CONSTANT NUMBER := 3;
19
20 g_calendar_type_mfg CONSTANT NUMBER := 1;
21 g_calendar_type_bis CONSTANT NUMBER := 0;
22
23
24 TYPE CurTyp IS REF CURSOR;
25
26 cursor c_next_pr (p_plan in number, p_curr_pr_date in date,
27 p_period_type in number, p_detail_level in number ) is
28 select min(detail_date)-1
29 from msc_bis_inv_detail
30 where plan_id = p_plan
31 and nvl(period_type,0) = p_period_type
32 and nvl(detail_level,0) = p_detail_level
33 and detail_date > p_curr_pr_date;
34
35 cursor c_prev_pr (p_plan in number,
36 p_instance in number, p_org in number, p_item in number,
37 p_curr_pr_date in date,
38 p_period_type in number, p_detail_level in number ) is
39 select max(detail_date)+1
40 from msc_bis_inv_detail
41 where plan_id = p_plan
42 and sr_instance_id = p_instance
43 and organization_id = p_org
44 and inventory_item_id = p_item
45 and nvl(period_type,0) = p_period_type
46 and nvl(detail_level,0) = p_detail_level
47 and detail_date < p_curr_pr_date;
48
49 cursor c_planinfo (p_plan in number) is
50 select plan_start_date, curr_cutoff_date
51 from msc_plans
52 where plan_id = p_plan;
53
54
55 cursor c_plan_orgs (l_plan_id number) is
56 select sr_instance_id, organization_id
57 from msc_plan_organizations
58 where plan_id = l_plan_id;
59
60 PROCEDURE put_line (p_msg varchar2) IS
61 BEGIN
62 --insert into msc_test values (p_msg);
63 --commit;
64 --dbms_output.put_line(p_msg);
65 null;
66 END put_line;
67
68 procedure store_user_pref(p_plan_type varchar2) is
69 l_cat_set_id number;
70 l_def_pref_id number;
71 l_mfq_query_id constant number := -99;
72 l_plan_type number;
73 BEGIN
74 l_plan_type := p_plan_type;
75 l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
76 l_cat_set_id:= msc_get_name.GET_preference('CATEGORY_SET_ID',l_def_pref_id, l_plan_type);
77
78 update msc_form_query
79 set number1 = l_cat_set_id,
80 number2 = p_plan_type
81 where query_id = l_mfq_query_id;
82 if (sql%rowcount = 0) then
83 INSERT INTO msc_form_query (QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, NUMBER1, NUMBER2) values
84 (l_mfq_query_id, sysdate, -1, sysdate, -1, l_cat_set_id, p_plan_type);
85 end if;
86 end store_user_pref;
87
88
89 function get_plan_type(p_plan_id number) return number is
90 cursor c_plan (p_plan_id number) is
91 select plan_type
92 from msc_plans
93 where plan_id = p_plan_id;
94
95 l_plan_id number;
96 l_plan_type number;
97 begin
98
99 open c_plan(p_plan_id);
100 fetch c_plan into l_plan_type;
101 close c_plan;
102 return l_plan_type;
103 end get_plan_type;
104
105 function get_plan_type_for_planlist(p_plan_list varchar2) return number is
106 l_plan_id number;
107 begin
108 if ( instr(p_plan_list,',') = 0 ) then
109 l_plan_id := p_plan_list;
110 else
111 l_plan_id := substr(p_plan_list,1,instr(p_plan_list,',')-1);
112 end if;
113
114 return get_plan_type(l_plan_id);
115 end get_plan_type_for_planlist;
116
117 FUNCTION get_valid_rounding(arg_round number,
118 arg_plan_id number) return number is
119 l_round number;
120 l_def_pref_id number;
121 l_plan_type number;
122 begin
123
124 l_plan_type := get_plan_type(arg_plan_id);
125 l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
126 l_round := msc_get_name.GET_preference('SUMMARY_DECIMAL_PLACES',l_def_pref_id, l_plan_type);
127
128 if (l_round is null) then
129 return 6;
130 end if;
131 return l_round;
132
133 --if (arg_round >= 0 and arg_round <= 10) then
134 --return arg_round;
135 --end if;
136 --return 2;
137 end get_valid_rounding;
138
139 FUNCTION get_cat_set_id(arg_plan_list varchar2) RETURN NUMBER is
140 l_cat_set_id number;
141 l_def_pref_id number;
142 l_plan_type number;
143 BEGIN
144 l_plan_type := get_plan_type_for_planlist(arg_plan_list);
145 l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
146 l_cat_set_id:= msc_get_name.GET_preference('CATEGORY_SET_ID',l_def_pref_id, l_plan_type);
147
148 return l_cat_set_id;
149 END get_cat_set_id;
150
151 FUNCTION get_cat_list (p_cat_set_id number, p_cat_id number)
152 RETURN VARCHAR2 is
153
154 CURSOR c_cat (l_cat_set number, l_cat_id number) is
155 SELECT distinct sr_category_id
156 FROM msc_item_categories
157 WHERE category_set_id = l_cat_set
158 and category_name in (select category_name
159 from msc_item_categories
160 where category_set_id = l_cat_set
161 and sr_category_id = l_cat_id
162 and rownum = 1);
163
164 retval varchar2(100) := null;
165 l_temp number;
166 BEGIN
167 open c_cat(p_cat_set_id , p_cat_id);
168 loop
169 fetch c_cat into l_temp;
170 if (retval is null) then
171 retval := l_temp;
172 else
173 retval := retval||','||l_temp;
174 end if;
175 exit when c_cat%notfound;
176 end loop;
177 close c_cat;
178
179 put_line(retval);
180 return retval;
181 END get_cat_list;
182
183 FUNCTION get_form_seq_id RETURN NUMBER is
184 cursor c_seq is
185 select msc_form_query_s.nextval
186 from dual;
187
188 l_seq_id number;
189 BEGIN
190 open c_seq;
191 fetch c_seq into l_seq_id;
192 close c_seq;
193 return l_seq_id;
194 END get_form_seq_id;
195
196 -- get the total number of bis or mfg periods for a given plan_id and calendar_type
197 FUNCTION get_num_periods(p_plan_id IN NUMBER, p_calendar_type IN NUMBER)
198 RETURN NUMBER IS
199
200 CURSOR planCur(p_plan_id in NUMBER)
201 IS
202 SELECT sr_instance_id, organization_id
203 FROM msc_plans
204 WHERE plan_id = p_plan_id;
205
206 CURSOR totalMfgPerCur(p_plan_id in NUMBER, p_sr_instance_id in number, p_org_id in number)
207 IS
208 SELECT count(distinct mpsd.period_start_date)
209 FROM msc_trading_partners tp,
210 msc_period_start_dates mpsd,
211 msc_plans mp
212 WHERE mpsd.calendar_code = tp.calendar_code
213 and mpsd.sr_instance_id = tp.sr_instance_id
214 and mpsd.exception_set_id = tp.calendar_exception_set_id
215 and tp.sr_instance_id = p_sr_instance_id
216 and tp.sr_tp_id = p_org_id
217 and tp.partner_type =3
218 and mp.plan_id = p_plan_id
219 and mpsd.period_start_date between mp.data_start_date and mp.cutoff_date;
220 --or mpsd.next_date between mp.data_start_date and mp.cutoff_date);
221
222 CURSOR totalBisPerCur(p_plan_id in NUMBER)
223 IS
224 SELECT count(distinct mbp.period_name)
225 FROM msc_bis_periods mbp,
226 msc_plans mp
227 WHERE mbp.organization_id = mp.organization_id
228 and mbp.sr_instance_id = mp.sr_instance_id
229 and ((mbp.start_date between nvl(mp.curr_start_date, sysdate)
230 and mp.cutoff_date
231 or mbp.end_date between nvl(mp.curr_start_date,sysdate)
232 and mp.cutoff_date) or
233 (mp.curr_start_date between mbp.start_date and mbp.end_date))
234 and mp.plan_id = p_plan_id;
235
236 l_sr_instance_id NUMBER;
237 l_org_id NUMBER;
238 l_total_periods number;
239
240 BEGIN
241
242 open planCur(p_plan_id);
243 loop
244 fetch planCur into l_sr_instance_id, l_org_id;
245 exit when planCur%NOTFOUND;
246 end loop;
247 close planCur;
248
249 if(p_calendar_type = g_calendar_type_mfg) then
250 open totalMfgPerCur(p_plan_id, l_sr_instance_id, l_org_id);
251 loop
252 fetch totalMfgPerCur into l_total_periods;
253 exit when totalMfgPerCur%NOTFOUND;
254 end loop;
255 close totalMfgPerCur;
256 else
257 open totalBisPerCur(p_plan_id);
258 loop
259 fetch totalBisPerCur into l_total_periods;
260 exit when totalBisPerCur%NOTFOUND;
261 end loop;
262 close totalBisPerCur;
263 end if;
264
265 return l_total_periods;
266 END get_num_periods;
267
268
269 PROCEDURE populate_cost_savings(arg_ret_val IN OUT NOCOPY VARCHAR2,
270 arg_period_type IN VARCHAR2,
271 arg_detail_level IN VARCHAR2,
272 arg_viewby IN VARCHAR2,
273 arg_plan_list IN VARCHAR2,
274 arg_org_list IN VARCHAR2 DEFAULT NULL,
275 arg_category_list IN VARCHAR2 DEFAULT NULL,
276 arg_item_list IN VARCHAR2 DEFAULT NULL,
277 arg_date_list IN DATE DEFAULT NULL,
278 arg_round in NUMBER DEFAULT NULL) IS
279
280 l_seq_id number;
281 l_cat_set_id number;
282
283 l_sql_stmt varchar2(10000);
284 l_select varchar2(300);
285 l_insert varchar2(300);
286 l_where varchar2(300);
287 l_from varchar2(50);
288
289 l_mfq_sql_stmt varchar2(10000);
290 l_mfq_select varchar2(300);
291 l_mfq_insert varchar2(600);
292 l_mfq_where varchar2(300);
293 l_mfq_from varchar2(50);
294
295 l_plan_insert varchar2(300);
296 l_plan_groupby varchar2(50);
297
298 l_org_insert varchar2(300);
299 l_org_groupby varchar2(100);
300
301 l_cate_insert varchar2(300);
302 l_cate_groupby varchar2(100);
303
304 l_pr_insert varchar2(300);
305 l_pr_groupby varchar2(200);
306
307 l_plan_org_cate_insert varchar2(300);
308 l_plan_org_cate_groupby varchar2(200);
309
310 l_seq_id2 number;
311
312 l_mfq_plan_insert varchar2(300);
313 l_mfq_plan_groupby varchar2(50);
314
315 l_mfq_org_insert varchar2(300);
316 l_mfq_org_groupby varchar2(100);
317
318 l_mfq_cate_insert varchar2(300);
319 l_mfq_cate_groupby varchar2(100);
320
321 l_mfq_plan_org_cate_insert varchar2(300);
322 l_mfq_plan_org_cate_groupby varchar2(200);
323
324 l_pr_date varchar2(30);
325 l_next_pr_char varchar2(30);
326 l_next_pr_date date;
327
328 l_calendar_type number;
329
330 l_round number;
331 BEGIN
332 l_seq_id := get_form_seq_id;
333 l_seq_id2 := get_form_seq_id;
334
335 l_cat_set_id := get_cat_set_id(arg_plan_list);
336 if (l_cat_set_id is null) then
337 arg_ret_val := -1;
338 return;
339 end if;
340
341 -- 3967991 bug fix
342 --l_round := get_valid_rounding(arg_round);
343 l_round := 0;
344
345 if ( arg_period_type = 'NULL'
346 or arg_period_type is null or arg_period_type = 'FINANCIAL' ) then
347 l_calendar_type := g_calendar_type_bis;
348 elsif arg_period_type = 'MANUFACTURING' then
349 l_calendar_type := g_calendar_type_mfg;
350 end if;
351
352
353 l_select := ' INSERT INTO msc_form_query ( '||
354 ' QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, '||
355 ' NUMBER5, NUMBER6, NUMBER7, NUMBER8, NUMBER9, NUMBER10, '||
356 ' NUMBER1, CHAR1, NUMBER2, NUMBER3, CHAR2, NUMBER4, CHAR3, DATE1, CHAR4, '||
357 ' NUMBER11, NUMBER12, CHAR5) ';
358
359 l_insert := ' SELECT '||l_seq_id ||' , sysdate, -1, sysdate, -1, '||
360 ' round(sum(ss_cost_no_post), '|| l_round || ' ), '||
361 ' round(sum(ss_cost_post), '|| l_round ||'), '||
362 ' round(sum(ss_cost_savings), '|| l_round ||' ), '||
363 ' round(sum(ss_value_no_post), '|| l_round ||' ), '||
364 ' round(sum(ss_value_post), '|| l_round ||' ), '||
365 ' round(sum(ss_value_savings), '|| l_round ||'), ';
366
367 l_from := ' from msc_cost_savings_v ';
368
369 l_plan_insert := ' plan_id, plan_name, '||
370 ' to_number(null), to_number(null), to_char(null), '||
371 ' to_number(null), to_char(null), detail_date, plan_name, '||
372 ' to_number(null), to_number(null), to_char(null)';
373
374 l_org_insert := ' plan_id, plan_name, '||
375 ' sr_instance_id, organization_id, org_code, '||
376 ' to_number(null), to_char(null), detail_date, plan_name||'' - ''||org_code ,'||
377 ' to_number(null), to_number(null), to_char(null)';
378
379 l_cate_insert := ' plan_id, plan_name, '||
380 ' to_number(null), to_number(null), to_char(null), '||
381 ' category_id, category_name, detail_date, plan_name||'' - ''||category_name ,'||
382 ' to_number(null), to_number(null), to_char(null)';
383
384 l_pr_insert := ' plan_id, plan_name, '||
385 ' to_number(null), to_number(null), to_char(null), '||
386 ' to_number(null), to_char(null), detail_date, plan_name||'' - ''||detail_date ,'||
387 ' detail_level, period_type, period_type_url ';
388
389 l_plan_org_cate_insert := ' plan_id, plan_name, '||
390 ' sr_instance_id, organization_id, org_code, '||
391 ' category_id, category_name, detail_date, plan_name||''-''||org_code||''-''||category_name ,'||
392 ' to_number(null), to_number(null), to_char(null)';
396 l_cate_groupby := ' GROUP BY detail_date, plan_id, plan_name, category_id, category_name ';
393
394 l_plan_groupby := ' GROUP BY detail_date, plan_id, plan_name ';
395 l_org_groupby := ' GROUP BY detail_date, plan_id, plan_name, sr_instance_id, organization_id, org_code ';
397 l_pr_groupby := ' GROUP BY detail_date, detail_level, period_type, period_type_url,plan_id, plan_name ';
398 l_plan_org_cate_groupby := ' GROUP BY detail_date, plan_id, plan_name, '
399 ||' sr_instance_id, organization_id, org_code, category_id, category_name ';
400
401 --Form query to select from msc_form_query (used for view by plan, org, categ, plan-org-categ)
402 --Inventory value has to be sumed across categories, orgs but has to be averaged across periods
403 --To achieve this, we have 2 floow a 2 step process
404 --first insert into mfq inv values whuch are sumed for the view by (plan , org or categ)
405 --group by the period.
406 --second avg the the inv value inserted into mfq -using the total number of periods in the plan
407 --this calcualted avg in values are inserted into mfq to be used in the java layer.
408
409 l_mfq_select := ' INSERT INTO msc_form_query ( '||
410 ' QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, '||
411 ' NUMBER5, NUMBER6, NUMBER7, NUMBER8, NUMBER9, NUMBER10, '||
412 ' NUMBER1, CHAR1, NUMBER2, NUMBER3, CHAR2, NUMBER4, CHAR3, DATE1, CHAR4, '||
413 ' NUMBER11, NUMBER12, CHAR5 ) ';
414
415 l_mfq_insert := ' SELECT '||l_seq_id2 ||' , sysdate, -1, sysdate, -1, '||
416 ' round(sum(mfq1.NUMBER5), '|| l_round || ' ), '||
417 ' round(sum(mfq1.NUMBER6), '|| l_round ||'), '||
418 ' round(sum(mfq1.NUMBER7), '|| l_round ||' ), '||
419 ' round(sum(mfq1.NUMBER8)/MSC_ANALYSIS_PKG.get_num_periods(mfq1.NUMBER1, '||l_calendar_type||'), '|| l_round ||' ), '||
420 ' round(sum(mfq1.NUMBER9)/MSC_ANALYSIS_PKG.get_num_periods(mfq1.NUMBER1, '||l_calendar_type||'), '|| l_round ||' ), '||
421 ' round(sum(mfq1.NUMBER10)/MSC_ANALYSIS_PKG.get_num_periods(mfq1.NUMBER1, '||l_calendar_type||'), '|| l_round ||'), ';
422
423 l_mfq_from := ' from msc_form_query mfq1 ';
424
425 l_mfq_plan_insert := ' mfq1.number1, mfq1.char1, '||
426 ' to_number(null), to_number(null), to_char(null), '||
427 ' to_number(null), to_char(null), to_date(null), mfq1.char1, '||
428 ' to_number(null), to_number(null), to_char(null) ';
429
430 l_mfq_plan_groupby := ' GROUP BY mfq1.number1, mfq1.char1 ';
431
432 -- plan_id, plan_name, sr_instance_id, organization_id, org_code
433 l_mfq_org_insert := ' mfq1.number1, mfq1.char1, '||
434 ' mfq1.NUMBER2, mfq1.NUMBER3, mfq1.CHAR2, '||
435 ' to_number(null), to_char(null), to_date(null), mfq1.char1||'' - ''||mfq1.char2 ,'||
436 ' to_number(null), to_number(null), to_char(null)';
437
438 l_mfq_org_groupby := ' GROUP BY mfq1.number1, mfq1.char1, mfq1.NUMBER2, mfq1.NUMBER3, mfq1.CHAR2 ';
439
440 -- plan_id, plan_name, category_id, category_name
441 l_mfq_cate_insert := ' mfq1.number1, mfq1.char1, '||
442 ' to_number(null), to_number(null), to_char(null), '||
443 ' mfq1.NUMBER4, mfq1.CHAR3, to_date(null), mfq1.char1||'' - ''||mfq1.CHAR3 ,'||
444 ' to_number(null), to_number(null), to_char(null)';
445
446 l_mfq_cate_groupby := ' GROUP BY mfq1.number1, mfq1.char1, mfq1.NUMBER4, mfq1.CHAR3 ';
447
448 -- plan_id, plan_name, sr_instance_id, organization_id, org_code, category_id, category_name
449 l_mfq_plan_org_cate_insert := ' mfq1.number1, mfq1.char1, '||
450 ' mfq1.NUMBER2, mfq1.NUMBER3, mfq1.CHAR2, '||
451 ' mfq1.NUMBER4, mfq1.CHAR3, to_date(null), mfq1.char1||''-''||mfq1.char2||''-''||mfq1.CHAR3 ,'||
452 ' to_number(null), to_number(null), to_char(null)';
453
454 l_mfq_plan_org_cate_groupby := ' GROUP BY mfq1.number1, mfq1.char1, ' ||
455 ' mfq1.NUMBER2, mfq1.NUMBER3, mfq1.CHAR2, mfq1.NUMBER4, mfq1.CHAR3 ';
456
457 --where clause stmt begins
458 --mandatory parameter
459 if ( instr(arg_plan_list,',') = 0 ) then
460 l_where := ' where plan_id = '||arg_plan_list;
461 l_mfq_where := ' where mfq1.number1 = '||arg_plan_list || ' and query_id='|| l_seq_id;
462
463 else
464 l_where := l_where || ' where plan_id in ( '||arg_plan_list||') ';
465 l_mfq_where := l_mfq_where || ' where number1 in ( ' ||arg_plan_list || ') and query_id='||
466 l_seq_id;
467 end if;
468
469 --mandatory parameter
470 l_where := l_where || ' and category_set_id = '||l_cat_set_id;
471
472 --mandatory parameter calendar/periods
473 if ( arg_detail_level = 'NULL'
474 or arg_detail_level is null or arg_detail_level = '5' ) then
475 l_where := l_where || ' and nvl(detail_level,0) = 0 ';
476 elsif arg_detail_level = '6' then
477 l_where := l_where || ' and nvl(detail_level,0) = 1 ';
478 end if;
479
480 if ( l_calendar_type = g_calendar_type_bis ) then
481 l_where := l_where || ' and nvl(period_type,0) = 0 ';
482 elsif (l_calendar_type = g_calendar_type_mfg) then
483 l_where := l_where || ' and nvl(period_type,0) = 1 ';
484 end if;
485
486 if ( arg_date_list is not null ) then
487 l_pr_date := to_char(trunc(arg_date_list),'MM-DD-YYYY');
488 open c_next_pr (to_number(arg_plan_list), to_date(l_pr_date,'MM-DD-YYYY'),1, 0);
489 fetch c_next_pr into l_next_pr_date;
490 close c_next_pr;
491
492 if ( l_next_pr_date is not null) then
496 ' and to_date('''||l_next_pr_char ||''', ''MM-DD-YYYY'') ';
493 l_next_pr_char := to_char(trunc(l_next_pr_date),'MM-DD-YYYY');
494 l_where := l_where || ' and detail_date between '||
495 ' to_date('''|| l_pr_date ||''', ''MM-DD-YYYY'') '||
497 else
498 l_where := l_where || ' and detail_date >= '||
499 ' to_date('''|| l_pr_date ||''', ''MM-DD-YYYY'') ';
500 end if;
501 end if;
502
503 --Optional parameter
504 if ( arg_org_list <> 'NULL' ) then
505 l_where := l_where || ' and (sr_instance_id,organization_id) in ('||arg_org_list||' ) ';
506 end if;
507
508 --Optional parameter
509 if ( arg_category_list <> 'NULL' ) then
510 --l_where := l_where || ' and category_id in ('||get_cat_list(l_cat_set_id,arg_category_list) ||')';
511 l_where := l_where || ' and (sr_instance_id, category_id) in (' || arg_category_list || ')';
512 end if;
513 --where clause stmt ends
514
515
516 if (arg_viewby = g_plan_viewby) then
517 l_sql_stmt := l_select||l_insert||l_plan_insert||l_from||l_where||l_plan_groupby;
518 put_line('plan view -intermediate'||l_sql_stmt);
519 msc_get_name.execute_dsql(l_sql_stmt);
520 --arg_ret_val := l_seq_id;
521 l_mfq_sql_stmt := l_mfq_select||l_mfq_insert||l_mfq_plan_insert||l_mfq_from||l_mfq_where||l_mfq_plan_groupby;
522 put_line('plan view'||l_mfq_sql_stmt);
523 msc_get_name.execute_dsql(l_mfq_sql_stmt);
524 arg_ret_val := l_seq_id2;
525 return;
526
527 elsif (arg_viewby = g_org_viewby) then
528 l_sql_stmt := l_select||l_insert||l_org_insert||l_from||l_where||l_org_groupby;
529 put_line('org view-intermediate'||l_sql_stmt);
530 msc_get_name.execute_dsql(l_sql_stmt);
531 --arg_ret_val := l_seq_id;
532 --return;
533 l_mfq_sql_stmt := l_mfq_select||l_mfq_insert||l_mfq_org_insert||l_mfq_from||l_mfq_where||l_mfq_org_groupby;
534 put_line('org view'||l_mfq_sql_stmt);
535 msc_get_name.execute_dsql(l_mfq_sql_stmt);
536 arg_ret_val := l_seq_id2;
537 return;
538
539 elsif (arg_viewby = g_category_viewby) then
540 l_sql_stmt := l_select||l_insert||l_cate_insert||l_from||l_where||l_cate_groupby;
541 put_line('category view-intermediate'||l_sql_stmt);
542 msc_get_name.execute_dsql(l_sql_stmt);
543 --arg_ret_val := l_seq_id;
544 --return;
545 l_mfq_sql_stmt := l_mfq_select||l_mfq_insert||l_mfq_cate_insert||l_mfq_from||
546 l_mfq_where||l_mfq_cate_groupby;
547 put_line('category view'||l_mfq_sql_stmt);
548 msc_get_name.execute_dsql(l_mfq_sql_stmt);
549 arg_ret_val := l_seq_id2;
550 return;
551
552 elsif (arg_viewby = g_period_viewby) then
553 l_sql_stmt := l_select||l_insert||l_pr_insert||l_from||l_where||l_pr_groupby;
554 put_line('period view'||l_sql_stmt);
555 msc_get_name.execute_dsql(l_sql_stmt);
556 arg_ret_val := l_seq_id;
557 return;
558 elsif (arg_viewby = g_week_viewby) then
559 l_sql_stmt := l_select||l_insert||l_pr_insert||l_from||l_where||l_pr_groupby;
560 put_line('week view'||l_sql_stmt);
561 msc_get_name.execute_dsql(l_sql_stmt);
562 arg_ret_val := l_seq_id;
563 return;
564 elsif (arg_viewby = g_plan_org_cate_viewby) then
565 l_sql_stmt := l_select||l_insert||l_plan_org_cate_insert||l_from||l_where||l_plan_org_cate_groupby;
566 put_line('plan org cate view-intermediate'||l_sql_stmt);
567 msc_get_name.execute_dsql(l_sql_stmt);
568 --arg_ret_val := l_seq_id;
569 --return;
570 l_mfq_sql_stmt := l_mfq_select||l_mfq_insert||l_mfq_plan_org_cate_insert||
571 l_mfq_from||l_mfq_where||l_mfq_plan_org_cate_groupby;
572 put_line('plan org cate view'||l_mfq_sql_stmt);
573 msc_get_name.execute_dsql(l_mfq_sql_stmt);
574 arg_ret_val := l_seq_id2;
575 return;
576
577 end if;
578 arg_ret_val := -99;
579 return;
580 END populate_cost_savings;
581
582 function populate_bis_dates(arg_plan_list varchar2, arg_viewby number,
583 arg_period_type varchar2, arg_detail_level varchar2) return number is
584
585
586 TYPE curType IS REF CURSOR;
587 l_cursor curType;
588
589 l_mfq_query_id number;
590 l_sql_stmt varchar2(1000);
591 l_where varchar2(1000);
592 l_orderby varchar2(100);
593
594 l_plan_id number;
595 l_period_type number;
596 l_detail_level number;
597 l_date date;
598 l_start_date date;
599 l_end_date date;
600 l_cur_plan_id number;
601 l_temp_date date;
602 plan_st_date date;
603 plan_end_date date;
604
605
606 begin
607
608 l_mfq_query_id := get_form_seq_id;
609 l_cur_plan_id := -1;
610
611 l_sql_stmt := ' SELECT distinct plan_id, period_type, detail_level, detail_date '||
612 ' from msc_bis_inv_detail '||
613 ' where plan_id in ('|| arg_plan_list ||')';
614
615 if ( arg_detail_level = 'NULL'
616 or arg_detail_level is null or arg_detail_level = '5' ) then
617 l_where := l_where || ' and nvl(detail_level,0) = 0 ';
618 elsif arg_detail_level = '6' then
619 l_where := l_where || ' and nvl(detail_level,0) = 1 ';
620 end if;
621
622 if ( arg_period_type = 'NULL'
623 or arg_period_type is null or arg_period_type = 'FINANCIAL' ) then
627 end if;
624 l_where := l_where || ' and nvl(period_type,0) = 0 ';
625 elsif arg_period_type = 'MANUFACTURING' then
626 l_where := l_where || ' and nvl(period_type,0) = 1 ';
628
629
630 l_orderby := ' order by plan_id, period_type, detail_level, detail_date ';
631
632 l_sql_stmt := l_sql_stmt || l_where || l_orderby;
633
634 open l_cursor for l_sql_stmt;
635 loop
636 fetch l_cursor into l_plan_id, l_period_type, l_detail_level, l_date;
637 exit when l_cursor%notfound;
638
639 if (l_cur_plan_id = -1) then
640 l_cur_plan_id := l_plan_id;
641 end if;
642
643 open c_planinfo(l_plan_id);
644 fetch c_planinfo into plan_st_date, plan_end_date;
645 close c_planinfo;
646
647 --multiple plan selected, insert last record of first plan
648 if(l_cur_plan_id <> l_plan_id) and (l_end_date is not null) then
649
650 l_temp_date := l_end_date +1;
651 INSERT INTO msc_form_query (
652 QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
653 NUMBER1, number2, number3, date1, date2 )
654 values
655 ( l_mfq_query_id , sysdate, -1, sysdate, -1,
656 l_cur_plan_id, nvl(l_period_type,0), nvl(l_detail_level,0), l_temp_date,
657 plan_end_date);
658
659 l_cur_plan_id := l_plan_id;
660 end if;
661
662 if ( l_start_date is null) then
663 l_start_date := l_date;
664 else
665 l_end_date := l_date - 1;
666
667 INSERT INTO msc_form_query (
668 QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
669 NUMBER1, number2, number3, date1, date2 )
670 values
671 ( l_mfq_query_id , sysdate, -1, sysdate, -1,
672 l_plan_id, nvl(l_period_type,0), nvl(l_detail_level,0), l_start_date, l_end_date);
673
674 l_start_date := l_date;
675 end if;
676 end loop;
677
678 --to insert the last record for mbid on l_end_date
679 if(l_end_date is not null) then
680 l_end_date := l_end_date +1;
681
682 INSERT INTO msc_form_query (
683 QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
684 NUMBER1, number2, number3, date1, date2 )
685 values
686 ( l_mfq_query_id , sysdate, -1, sysdate, -1,
687 l_plan_id, nvl(l_period_type,0), nvl(l_detail_level,0), l_end_date, plan_end_date);
688 end if;
689
690 return l_mfq_query_id;
691
692 end populate_bis_dates;
693
694 PROCEDURE populate_srvlvl_breakdown(arg_ret_val IN OUT NOCOPY VARCHAR2,
695 arg_period_type IN VARCHAR2,
696 arg_detail_level IN VARCHAR2,
697 arg_viewby IN VARCHAR2,
698 arg_plan_list IN VARCHAR2,
699 arg_org_list IN VARCHAR2 DEFAULT NULL,
700 arg_category_list IN VARCHAR2 DEFAULT NULL,
701 arg_item_list IN VARCHAR2 DEFAULT NULL,
702 arg_demand_class_list IN VARCHAR2 DEFAULT NULL,
703 arg_year_from IN DATE DEFAULT NULL,
704 arg_year_to IN DATE DEFAULT NULL,
705 arg_date_list IN DATE DEFAULT NULL,
706 arg_round in NUMBER DEFAULT NULL) IS
707 l_seq_id number;
708 l_cat_set_id number;
709
710 l_sql_stmt varchar2(10000);
711 l_select varchar2(300);
712 l_insert varchar2(500);
713 l_where varchar2(300);
714 l_from varchar2(50);
715
716 l_plan_insert varchar2(300);
717 l_plan_groupby varchar2(200);
718 l_plan_dflt_insert varchar2(200);
719
720 l_org_insert varchar2(300);
721 l_org_groupby varchar2(200);
722 l_org_dflt_insert varchar2(200);
723
724 l_cate_insert varchar2(300);
725 l_cate_groupby varchar2(200);
726 l_cate_dflt_insert varchar2(200);
727
728 l_item_insert varchar2(300);
729 l_item_groupby varchar2(200);
730 l_item_dflt_insert varchar2(200);
731
732 l_demand_class_insert varchar2(300);
733 l_demand_class_groupby varchar2(200);
734 l_demand_class_dflt_insert varchar2(200);
735
736 l_pr_insert varchar2(300);
737 l_pr_groupby varchar2(200);
738 l_pr_dflt_insert varchar2(200);
739
740 l_temp_from_date varchar2(30);
741 l_temp_to_date varchar2(30);
742
743 l_pr_date varchar2(30);
744 l_next_pr_char varchar2(30);
745 l_next_pr_date date;
746
747 l_round number;
748 BEGIN
749 l_seq_id := get_form_seq_id;
750 l_cat_set_id := get_cat_set_id(arg_plan_list);
751 if (l_cat_set_id is null) then
752 arg_ret_val := -1;
753 return;
754 end if;
755 l_round := get_valid_rounding(arg_round, -23453);
756
757 l_select := ' INSERT INTO msc_form_query ( '||
758 ' QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, '||
759 ' NUMBER10, NUMBER11, NUMBER12,'||
760 ' NUMBER1, CHAR1, NUMBER2, NUMBER3, CHAR2, '||
761 ' NUMBER4, CHAR3, '||
762 ' NUMBER5, CHAR4, '||
763 ' CHAR5, DATE1, CHAR6, '||
764 ' number14, number15, char7 ) ';
765
766 l_insert := ' SELECT '||l_seq_id ||' , sysdate, -1, sysdate, -1, '||
767 ' round(
768 decode(sum(achieved_service_level_qty2), 0, 0,
769 sum(achieved_service_level_qty1)/sum(achieved_service_level_qty2))
770 ,'||l_round ||'),'||
771 ' round(avg(target_service_level),'||l_round ||'),';
772
773 l_from := ' from msc_srvlvl_breakdown_v ';
774
775 l_plan_insert := ' plan_id, plan_name, '||
776 ' to_number(null), to_number(null), to_char(null), '||
777 ' to_number(null), to_char(null), '||
778 ' to_number(null), to_char(null), '||
779 ' to_char(null), to_date(null) , plan_name, '||
780 ' to_number(null), to_number(null), to_char(null) ';
781
782 l_org_insert := ' plan_id, plan_name, '||
783 ' sr_instance_id, organization_id, org_code, '||
784 ' to_number(null), to_char(null), '||
785 ' to_number(null), to_char(null), '||
786 ' to_char(null), to_date(null) , plan_name||'' - ''||org_code, ' ||
787 ' to_number(null), to_number(null), to_char(null) ';
788
789 l_cate_insert := ' plan_id, plan_name,'||
790 ' to_number(null), to_number(null), to_char(null), '||
791 ' category_id, category_name, '||
792 ' to_number(null), to_char(null), '||
793 ' to_char(null), to_date(null) , plan_name||'' - ''||category_name, ' ||
794 ' to_number(null), to_number(null), to_char(null) ';
795
796 l_item_insert := ' plan_id, plan_name,'||
797 ' to_number(null), to_number(null), to_char(null), '||
798 ' to_number(null), to_char(null), '||
799 ' inventory_item_id, item_name, '||
800 ' to_char(null), to_date(null) , plan_name||'' - ''||item_name, ' ||
801 ' to_number(null), to_number(null), to_char(null) ';
802
803 l_demand_class_insert := ' plan_id, plan_name, '||
804 ' to_number(null), to_number(null), to_char(null), '||
805 ' to_number(null), to_char(null), '||
806 ' to_number(null), to_char(null), '||
807 ' demand_class, to_date(null) , '||
808 ' plan_name||'' - ''||demand_class, ' ||
809 ' to_number(null), to_number(null), to_char(null) ';
810
811 l_pr_insert := ' plan_id, plan_name,'||
812 ' to_number(null), to_number(null), to_char(null), '||
813 ' to_number(null), to_char(null), '||
814 ' to_number(null), to_char(null), '||
815 ' to_char(null), detail_date , plan_name||'' - ''||detail_date, ' ||
816 ' period_type, detail_level, period_type_url ';
817
818 l_plan_groupby := ' GROUP BY plan_id, plan_name ';
819 l_org_groupby := ' GROUP BY plan_id, plan_name, sr_instance_id, organization_id, org_code ';
820 l_cate_groupby := ' GROUP BY plan_id, plan_name, category_id, category_name ';
821 l_item_groupby := ' GROUP BY plan_id, plan_name, inventory_item_id, item_name ';
822 l_demand_class_groupby := ' GROUP BY plan_id, plan_name, demand_class ';
823 l_pr_groupby := ' GROUP BY detail_date, period_type, detail_level, period_type_url, plan_id, plan_name ';
824
825 l_plan_dflt_insert := ' msc_analysis_pkg.get_dflt_value(plan_id, null, 0), ';
826 l_org_dflt_insert := ' to_number(null), ';
827 l_cate_dflt_insert := ' msc_analysis_pkg.get_dflt_value(plan_id, null, 6, null, '||
828 ' null, null, null, null, null, category_id), ';
829 l_item_dflt_insert := ' to_number(null), ';
830 l_demand_class_dflt_insert := ' msc_analysis_pkg.get_dflt_value(plan_id, null, 4, null, null, '||
831 ' null, demand_class, null, null, null), ';
832 l_pr_dflt_insert := ' to_number(null), ';
833
834 --where clause stmt begins
835 --mandatory parameter
836 if ( instr(arg_plan_list,',') = 0 ) then
837 l_where := ' where plan_id = '||arg_plan_list;
838 else
839 l_where := l_where || ' where plan_id in ( '||arg_plan_list||') ';
840 end if;
841 put_line('8');
842 --mandatory parameter
843 l_where := l_where || ' and category_set_id = '||l_cat_set_id;
844
845 --mandatory parameter calendar/periods
846 if ( arg_period_type = 'NULL'
850 l_where := l_where || ' and nvl(period_type,0) = 1 ';
847 or arg_period_type is null or arg_period_type = 'FINANCIAL' ) then
848 l_where := l_where || ' and nvl(period_type,0) = 0 ';
849 elsif arg_period_type = 'MANUFACTURING' then
851 end if;
852
853 if arg_period_type = 'MANUFACTURING' then
854 if (arg_viewby = g_period_viewby) then
855 l_where := l_where || ' and nvl(detail_level,0) = 0 ';
856 else
857 l_where := l_where || ' and nvl(detail_level,0) = 1 ';
858 end if;
859 else
860 if ( arg_detail_level = 'NULL'
861 or arg_detail_level is null or arg_detail_level = '5' ) then
862 l_where := l_where || ' and nvl(detail_level,0) = 0 ';
863 elsif arg_detail_level = '6' then
864 l_where := l_where || ' and nvl(detail_level,0) = 1 ';
865 end if;
866 end if;
867
868 if ( arg_date_list is not null ) then
869 l_pr_date := to_char(trunc(arg_date_list),'MM-DD-YYYY');
870 open c_next_pr (to_number(arg_plan_list), to_date(l_pr_date,'MM-DD-YYYY'), 1, 0);
871 fetch c_next_pr into l_next_pr_date;
872 close c_next_pr;
873
874 if ( l_next_pr_date is not null) then
875 l_next_pr_char := to_char(trunc(l_next_pr_date),'MM-DD-YYYY');
876 l_where := l_where || ' and detail_date between '||
877 ' to_date('''|| l_pr_date ||''', ''MM-DD-YYYY'') '||
878 ' and to_date('''||l_next_pr_char ||''', ''MM-DD-YYYY'') ';
879 else
880 l_where := l_where || ' and detail_date >= '||
881 ' to_date('''|| l_pr_date ||''', ''MM-DD-YYYY'') ';
882 end if;
883 end if;
884
885 --Optional parameter
886 if ( arg_org_list <> 'NULL' ) then
887 l_where := l_where || ' and (sr_instance_id,organization_id) in ('||arg_org_list||' ) ';
888 end if;
889
890 --Optional parameter
891 if ( arg_category_list <> 'NULL' ) then
892 l_where := l_where || ' and (sr_instance_id, category_id) in (' || arg_category_list || ')';
893
894 --if (instr(arg_category_list,',') = 0) then
895 --l_where := l_where || ' and category_id = '||arg_category_list;
896 --else
897 --l_where := l_where || ' and category_id in ('||arg_category_list||')';
898 --end if;
899 end if;
900
901 --Optional parameter
902 if ( arg_demand_class_list <> 'NULL' ) then
903 if (instr(arg_demand_class_list,',') = 0) then
904 l_where := l_where || ' and demand_class = '''||arg_demand_class_list||'''';
905 else
906 l_where := l_where || ' and demand_class in ('||arg_demand_class_list||')';
907 end if;
908 end if;
909
910 --Optional parameter
911 if (( arg_year_from is not null ) and (arg_year_to is not null)) then
912 l_temp_from_date := to_char(trunc(arg_year_from),'MM-DD-YYYY');
913 l_temp_to_date := to_char(trunc(arg_year_to),'MM-DD-YYYY');
914 l_where := l_where || ' and detail_date between '||
915 ' to_date('''|| l_temp_from_date ||''', ''MM-DD-YYYY'') '||
916 ' and to_date('''||l_temp_to_date ||''', ''MM-DD-YYYY'') ';
917 elsif ( arg_year_to is not null) then
918 l_temp_to_date := to_char(trunc(arg_year_to),'MM-DD-YYYY');
919 l_where := l_where || ' and detail_date <= '||
920 ' to_date('''|| l_temp_to_date ||''', ''MM-DD-YYYY'') ';
921 elsif ( arg_year_from is not null ) then
922 l_temp_from_date := to_char(trunc(arg_year_from),'MM-DD-YYYY');
923 l_where := l_where || ' and detail_date >= '||
924 ' to_date('''|| l_temp_from_date ||''', ''MM-DD-YYYY'') ';
925 end if;
926
927 --where clause stmt ends
928 if (arg_viewby = g_plan_viewby) then
929 l_sql_stmt := l_select||l_insert||l_plan_dflt_insert||l_plan_insert||l_from||l_where||l_plan_groupby;
930 put_line(l_sql_stmt);
931 msc_get_name.execute_dsql(l_sql_stmt);
932 arg_ret_val := l_seq_id;
933 return;
934 elsif (arg_viewby = g_org_viewby) then
935 l_sql_stmt := l_select||l_insert||l_org_dflt_insert||l_org_insert||l_from||l_where||l_org_groupby;
936 put_line('plan view'||l_sql_stmt);
937 msc_get_name.execute_dsql(l_sql_stmt);
938 arg_ret_val := l_seq_id;
939
940 return;
941 elsif (arg_viewby = g_category_viewby) then
942 l_sql_stmt := l_select||l_insert||l_cate_dflt_insert||l_cate_insert||l_from||l_where||l_cate_groupby;
943 put_line('category view'||l_sql_stmt);
944 msc_get_name.execute_dsql(l_sql_stmt);
945 arg_ret_val := l_seq_id;
946 return;
947 elsif (arg_viewby = g_item_viewby) then
948 l_sql_stmt := l_select||l_insert||l_item_dflt_insert||l_item_insert||l_from||l_where||l_item_groupby;
949 put_line('item view'||l_sql_stmt);
950 msc_get_name.execute_dsql(l_sql_stmt);
951 arg_ret_val := l_seq_id;
952 return;
953 elsif (arg_viewby = g_demand_class_viewby) then
954 l_sql_stmt := l_select||l_insert||l_demand_class_dflt_insert
955 ||l_demand_class_insert||l_from||l_where||l_demand_class_groupby;
956 put_line('demand class'||l_sql_stmt);
957 msc_get_name.execute_dsql(l_sql_stmt);
958 arg_ret_val := l_seq_id;
959 return;
960 elsif (arg_viewby = g_period_viewby) then
961 l_sql_stmt := l_select||l_insert||l_pr_dflt_insert||l_pr_insert||l_from||l_where||l_pr_groupby;
962 put_line('period view '||l_sql_stmt);
963 msc_get_name.execute_dsql(l_sql_stmt);
964 arg_ret_val := l_seq_id;
965 return;
969 msc_get_name.execute_dsql(l_sql_stmt);
966 elsif (arg_viewby = g_week_viewby) then
967 l_sql_stmt := l_select||l_insert||l_pr_dflt_insert||l_pr_insert||l_from||l_where||l_pr_groupby;
968 put_line(' week view '||l_sql_stmt);
970 arg_ret_val := l_seq_id;
971 return;
972 end if;
973
974 arg_ret_val := -99;
975 return;
976 END populate_srvlvl_breakdown;
977
978 function get_tp_cost ( arg_period_type IN VARCHAR2, arg_detail_level IN VARCHAR2,
979 arg_plan in number, arg_instance in number, arg_org in number,
980 arg_item in number,
981 arg_detail_date date) return number IS
982
983 cursor c_next_pr_local (p_plan in number, p_curr_pr_date in date,
984 p_period_type in number, p_detail_level in number ) is
985 select min(detail_date)-1
986 from msc_bis_inv_detail
987 where plan_id = p_plan
988 and sr_instance_id = arg_instance
989 and organization_id = arg_org
990 and nvl(period_type,0) = p_period_type
991 and nvl(detail_level,0) = p_detail_level
992 and detail_date > p_curr_pr_date;
993
994 v_tp_cost number := 0;
995 l_prev_date date;
996 l_next_date date;
997
998 l_period_type number;
999 l_detail_level number;
1000
1001 plan_st_date date;
1002 plan_end_date date;
1003 begin
1004 l_period_type := nvl(arg_period_type,0);
1005 l_detail_level := nvl(arg_detail_level,0);
1006
1007 open c_planinfo(arg_plan);
1008 fetch c_planinfo into plan_st_date, plan_end_date;
1009 close c_planinfo;
1010
1011 --open c_prev_pr (arg_plan, arg_instance, arg_org, arg_item, arg_detail_date, l_period_type, l_detail_level);
1012 --fetch c_prev_pr into l_prev_date;
1013 --close c_prev_pr;
1014
1015 open c_next_pr_local (arg_plan, arg_detail_date, l_period_type, l_detail_level);
1016 fetch c_next_pr_local into l_next_date;
1017 close c_next_pr_local;
1018
1019
1020 l_prev_date := arg_detail_date;
1021 --l_next_date := arg_detail_date;
1022
1023 if (l_prev_date is null or trunc(l_prev_date) < trunc(plan_st_date)) then
1024 l_prev_date := plan_st_date;
1025 elsif (trunc(l_next_date) < trunc(plan_st_date)) then
1026 return 0;
1027 elsif (l_next_date is null or trunc(l_next_date) > trunc(plan_end_date) ) then
1028 l_next_date := plan_end_date;
1029 end if;
1030
1031 v_tp_cost := msc_analysis_pkg.get_plan_service_level(arg_plan, g_tp_cost, arg_instance, arg_org, arg_item, l_prev_date, l_next_date);
1032
1033 return round(v_tp_cost,2);
1034 end get_tp_cost;
1035
1036 PROCEDURE populate_cost_breakdown(arg_ret_val IN OUT NOCOPY VARCHAR2,
1037 arg_period_type IN VARCHAR2,
1038 arg_detail_level IN VARCHAR2,
1039 arg_viewby IN VARCHAR2,
1040 arg_plan_list IN VARCHAR2,
1041 arg_org_list IN VARCHAR2 DEFAULT NULL,
1042 arg_category_list IN VARCHAR2 DEFAULT NULL,
1043 arg_item_list IN VARCHAR2 DEFAULT NULL,
1044 arg_year_from IN DATE DEFAULT NULL,
1045 arg_year_to IN DATE DEFAULT NULL,
1046 arg_date_list IN DATE DEFAULT NULL,
1047 arg_round in NUMBER DEFAULT NULL) IS
1048 l_seq_id number;
1049 l_cat_set_id number;
1050
1051 l_sql_stmt varchar2(10000);
1052 l_select varchar2(500);
1053 l_insert varchar2(1500);
1054 l_where varchar2(400);
1055 l_from varchar2(50);
1056
1057 l_plan_insert varchar2(300);
1058 l_plan_groupby varchar2(200);
1059
1060 l_org_insert varchar2(300);
1061 l_org_groupby varchar2(200);
1062
1063 l_cate_insert varchar2(300);
1064 l_cate_groupby varchar2(200);
1065
1066 l_item_insert varchar2(300);
1067 l_item_groupby varchar2(200);
1068
1069 l_pr_insert varchar2(300);
1070 l_pr_groupby varchar2(200);
1071
1072 l_temp_from_date varchar2(30);
1073 l_temp_to_date varchar2(30);
1074
1075 l_pr_date varchar2(30);
1076 l_next_pr_char varchar2(30);
1077 l_next_pr_date date;
1078
1079 l_mfq_query_id number;
1080
1081 l_round number;
1082 l_plan_id number;
1083 BEGIN
1084 l_seq_id := get_form_seq_id;
1085 l_cat_set_id := get_cat_set_id(arg_plan_list);
1086 if (l_cat_set_id is null) then
1087 arg_ret_val := -1;
1088 return;
1089 end if;
1090 l_round := get_valid_rounding(arg_round, -23453);
1091
1092 l_mfq_query_id := populate_bis_dates(arg_plan_list, arg_viewby, arg_period_type, arg_detail_level);
1093
1094 l_select := ' insert into msc_form_query ( '||
1095 ' query_id, last_update_date, last_updated_by, creation_date, created_by, '||
1096 ' number6, number7, number8, number9, number10, number11, number12, number13, number16,'||
1097 ' number17, number18, number19, number20,'||
1098 ' number1, char1, number2, number3, char2, '||
1099 ' number4, char3, '||
1100 ' number5, char4, '||
1101 ' date1, char5, number14, number15, char6 ) ';
1102 l_insert := ' select '||l_seq_id ||' , sysdate, -1, sysdate, -1, '||
1103 ' round(sum(nvl(planned_production_cost,0)), '|| l_round ||' ), '||
1104 ' round(sum(nvl(planned_carrying_cost,0)), '|| l_round ||' ), '||
1105 ' round(sum(nvl(planned_purchasing_cost,0)), '||l_round ||' ), '||
1106 ' round(sum(nvl(planned_tp_cost,0)), '|| l_round ||' ), '||
1107 ' decode(sum(planned_total_cost),0,0, '||
1111 ' decode(sum(planned_total_cost),0,0, '||
1108 ' round(nvl(sum(planned_production_cost)/sum(planned_total_cost),0)* 100, '||l_round||' )), '||
1109 ' decode(sum(planned_total_cost),0,0, '||
1110 ' round(nvl(sum(planned_carrying_cost)/sum(planned_total_cost),0)* 100, '||l_round||' )), '||
1112 ' round(nvl(sum(planned_purchasing_cost)/sum(planned_total_cost),0)* 100, '||l_round||' )), '||
1113 ' decode(sum(planned_total_cost),0,0, '||
1114 ' round(nvl(sum(planned_tp_cost)/sum(planned_total_cost),0)* 100,'|| l_round ||' )), '||
1115 ' round(sum(nvl(planned_revenue,0)), '|| l_round ||' ), '||
1116 ' round(sum(nvl(int_repair_cost,0)), '|| l_round ||' ), '||
1117 ' round(sum(nvl(ext_repair_cost,0)), '|| l_round ||' ), '||
1118 ' decode(sum(planned_total_cost),0,0, '||
1119 ' round(nvl(sum(int_repair_cost)/sum(planned_total_cost),0)* 100,'|| l_round ||' )), '||
1120 ' decode(sum(planned_total_cost),0,0, '||
1121 ' round(nvl(sum(ext_repair_cost)/sum(planned_total_cost),0)* 100,'|| l_round ||' )), ';
1122
1123 l_from := ' from msc_cost_breakdown_v ';
1124
1125 l_plan_insert := ' plan_id, plan_name, '||
1126 ' to_number(null), to_number(null), to_char(null), '||
1127 ' to_number(null), to_char(null), '||
1128 ' to_number(null), to_char(null), '||
1129 ' to_date(null) , plan_name, '||
1130 ' to_number(null), to_number(null), to_char(null) ';
1131
1132 l_org_insert := ' plan_id, plan_name, '||
1133 ' sr_instance_id, organization_id, org_code, '||
1134 ' to_number(null), to_char(null), '||
1135 ' to_number(null), to_char(null), '||
1136 ' to_date(null) , plan_name||'' - ''||org_code, '||
1137 ' to_number(null), to_number(null), to_char(null) ';
1138
1139 l_cate_insert := ' plan_id, plan_name, '||
1140 ' to_number(null), to_number(null), to_char(null), '||
1141 ' category_id, category_name, '||
1142 ' to_number(null), to_char(null), '||
1143 ' to_date(null) , plan_name||'' - ''||category_name, '||
1144 ' to_number(null), to_number(null), to_char(null) ';
1145
1146 l_item_insert := ' plan_id, plan_name, '||
1147 ' to_number(null), to_number(null), to_char(null), '||
1148 ' to_number(null), to_char(null), '||
1149 ' inventory_item_id, item_name, '||
1150 ' to_date(null) , plan_name||'' - ''||item_name ,'||
1151 ' to_number(null), to_number(null), to_char(null) ';
1152
1153 l_pr_insert := ' plan_id, plan_name, '||
1154 ' to_number(null), to_number(null), to_char(null), '||
1155 ' to_number(null), to_char(null), '||
1156 ' to_number(null), to_char(null), '||
1157 ' detail_date , plan_name||'' - ''||detail_date ,'||
1158 ' detail_level, period_type, period_type_url ';
1159
1160 l_plan_groupby := ' GROUP BY plan_id, plan_name ';
1161 l_org_groupby := ' GROUP BY plan_id, plan_name, sr_instance_id, organization_id, org_code ';
1162 l_cate_groupby := ' GROUP BY plan_id, plan_name, category_id, category_name ';
1163 l_item_groupby := ' GROUP BY plan_id, plan_name, inventory_item_id, item_name ';
1164 l_pr_groupby := ' GROUP BY detail_date, detail_level, period_type, period_type_url, plan_id, plan_name';
1165
1166 --where clause stmt begins
1167
1168 --mandatory parameter
1169 if ( instr(arg_plan_list,',') = 0 ) then
1170 l_where := ' where plan_id = '||arg_plan_list;
1171 else
1172 l_where := l_where || ' where plan_id in ( '||arg_plan_list||') ';
1173 end if;
1174
1175 --mandatory parameter
1176 l_where := l_where || ' and category_set_id = '||l_cat_set_id;
1177 l_where := l_where || ' and query_id = '||l_mfq_query_id;
1178
1179 --mandatory parameter calendar/periods
1180
1181 if ( arg_detail_level = 'NULL'
1182 or arg_detail_level is null or arg_detail_level = '5' ) then
1183 l_where := l_where || ' and nvl(detail_level,0) = 0 ';
1184 elsif arg_detail_level = '6' then
1185 l_where := l_where || ' and nvl(detail_level,0) = 1 ';
1186 end if;
1187
1188 if ( arg_period_type = 'NULL'
1189 or arg_period_type is null or arg_period_type = 'FINANCIAL' ) then
1190 l_where := l_where || ' and nvl(period_type,0) = 0 ';
1191 elsif arg_period_type = 'MANUFACTURING' then
1192 l_where := l_where || ' and nvl(period_type,0) = 1 ';
1193 end if;
1194
1195
1196 if ( arg_date_list is not null ) then
1197 l_pr_date := to_char(trunc(arg_date_list),'MM-DD-YYYY');
1198 open c_next_pr (to_number(arg_plan_list), to_date(l_pr_date,'MM-DD-YYYY'), 1, 0);
1199 fetch c_next_pr into l_next_pr_date;
1200 close c_next_pr;
1201
1202 if ( l_next_pr_date is not null) then
1203 l_next_pr_char := to_char(trunc(l_next_pr_date),'MM-DD-YYYY');
1204 l_where := l_where || ' and detail_date between '||
1205 ' to_date('''|| l_pr_date ||''', ''MM-DD-YYYY'') '||
1206 ' and to_date('''||l_next_pr_char ||''', ''MM-DD-YYYY'') ';
1207 else
1208 l_where := l_where || ' and detail_date >= '||
1209 ' to_date('''|| l_pr_date ||''', ''MM-DD-YYYY'') ';
1210 end if;
1211 end if;
1212
1213
1214 --Optional parameter
1215 if ( arg_org_list <> 'NULL' ) then
1216 l_where := l_where || ' and (sr_instance_id,organization_id) in ('||arg_org_list||') ';
1217 end if;
1218
1219 --Optional parameter
1220 if ( arg_category_list <> 'NULL' ) then
1221 l_where := l_where || ' and (sr_instance_id, category_id) in (' || arg_category_list || ')';
1222
1223 --if (instr(arg_category_list,',') = 0) then
1227 --end if;
1224 --l_where := l_where || ' and category_id = '||arg_category_list;
1225 --else
1226 --l_where := l_where || ' and category_id in ('||arg_category_list||')';
1228 end if;
1229
1230 --Optional parameter
1231 if (( arg_year_from is not null ) and (arg_year_to is not null)) then
1232 l_temp_from_date := to_char(trunc(arg_year_from),'MM-DD-YYYY');
1233 l_temp_to_date := to_char(trunc(arg_year_to),'MM-DD-YYYY');
1234 l_where := l_where || ' and detail_date between '||
1235 ' to_date('''|| l_temp_from_date ||''', ''MM-DD-YYYY'') '||
1236 ' and to_date('''||l_temp_to_date ||''', ''MM-DD-YYYY'') ';
1237 elsif ( arg_year_to is not null) then
1238 l_temp_to_date := to_char(trunc(arg_year_to),'MM-DD-YYYY');
1239 l_where := l_where || ' and detail_date <= '||
1240 ' to_date('''|| l_temp_to_date ||''', ''MM-DD-YYYY'') ';
1241 elsif ( arg_year_from is not null ) then
1242 l_temp_from_date := to_char(trunc(arg_year_from),'MM-DD-YYYY');
1243 l_where := l_where || ' and detail_date >= '||
1244 ' to_date('''|| l_temp_from_date ||''', ''MM-DD-YYYY'') ';
1245 end if;
1246 --where clause stmt ends
1247
1248 if (arg_viewby = g_plan_viewby) then
1249 l_sql_stmt := l_select||l_insert||l_plan_insert||l_from||l_where||l_plan_groupby;
1250 put_line('plan view '||l_sql_stmt);
1251 msc_get_name.execute_dsql(l_sql_stmt);
1252 arg_ret_val := l_seq_id;
1253 return;
1254 elsif (arg_viewby = g_org_viewby) then
1255 l_sql_stmt := l_select||l_insert||l_org_insert||l_from||l_where||l_org_groupby;
1256 put_line('org view '||l_sql_stmt);
1257 msc_get_name.execute_dsql(l_sql_stmt);
1258 arg_ret_val := l_seq_id;
1259 return;
1260 elsif (arg_viewby = g_category_viewby) then
1261 l_sql_stmt := l_select||l_insert||l_cate_insert||l_from||l_where||l_cate_groupby;
1262 put_line('cat view '||l_sql_stmt);
1263 msc_get_name.execute_dsql(l_sql_stmt);
1264 arg_ret_val := l_seq_id;
1265 return;
1266 elsif (arg_viewby = g_item_viewby) then
1267 l_sql_stmt := l_select||l_insert||l_item_insert||l_from||l_where||l_item_groupby;
1268 put_line('item view '||l_sql_stmt);
1269 msc_get_name.execute_dsql(l_sql_stmt);
1270 arg_ret_val := l_seq_id;
1271 return;
1272 elsif (arg_viewby = g_period_viewby) then
1273 l_sql_stmt := l_select||l_insert||l_pr_insert||l_from||l_where||l_pr_groupby;
1274 put_line('period view '||l_sql_stmt);
1275 msc_get_name.execute_dsql(l_sql_stmt);
1276 arg_ret_val := l_seq_id;
1277 return;
1278 elsif (arg_viewby = g_week_viewby) then
1279 l_sql_stmt := l_select||l_insert||l_pr_insert||l_from||l_where||l_pr_groupby;
1280 put_line('week view '||l_sql_stmt);
1281 msc_get_name.execute_dsql(l_sql_stmt);
1282 arg_ret_val := l_seq_id;
1283 return;
1284 end if;
1285 arg_ret_val := -99;
1286 return;
1287 END populate_cost_breakdown;
1288
1289 PROCEDURE populate_srvlvl_profit(arg_ret_val IN OUT NOCOPY VARCHAR2,
1290 arg_period_type IN VARCHAR2,
1291 arg_viewby IN VARCHAR2,
1292 arg_plan_list IN VARCHAR2,
1293 arg_round in NUMBER DEFAULT NULL) IS
1294 l_seq_id number;
1295 l_cat_set_id number;
1296
1297 l_cursor varchar2(4000);
1298
1299 l_tp_cost number;
1300 l_attained number;
1301 l_target number;
1302 l_plan_name varchar2(20);
1303
1304 l_plnd_prod_cost number;
1305 l_plnd_carr_cost number;
1306 l_plnd_purc_cost number;
1307 l_plnd_tot_cost number;
1308 l_plnd_rev number;
1309 l_plnd_gross_profit number;
1310 l_plnd_gross_profit_pct number;
1311 l_inv_value number;
1312 l_plan_id number;
1313 l_ext_repair_cost number;
1314 l_int_repair_cost number;
1315
1316 l_plan_type number;
1317 l_otype1 number;
1318 l_otype2 number;
1319 l_otype3 number;
1320
1321 l_calc_gross_profit_pct number;
1322
1323 CURSOR PERIOD_CURSOR (p_plan_id in number) IS
1324 SELECT count(*)
1325 FROM msc_bis_periods mbp,
1326 msc_plans mp
1327 WHERE mbp.organization_id = mp.organization_id
1328 and mbp.sr_instance_id = mp.sr_instance_id
1329 and ((mbp.start_date between nvl(mp.data_start_date, sysdate)
1330 and mp.cutoff_date
1331 or mbp.end_date between nvl(mp.data_start_date,sysdate)
1332 and mp.cutoff_date) or
1333 (mp.data_start_date between mbp.start_date and mbp.end_date))
1334 and mp.plan_id = p_plan_id
1335 and mbp.adjustment_period_flag ='N'
1336 order by mbp.start_date;
1337 /*
1338 CURSOR c_tp_cost (p_plan_id in number, p_inst_id in number, p_org_id in number) IS
1339 select sum(nvl(MSC_ANALYSIS_PKG.get_tp_cost(mbid.period_type,
1340 mbid.detail_level, mbid.plan_id, mbid.sr_instance_id,
1341 mbid.organization_id, mbid.inventory_item_id, mbid.detail_date),0))
1342 FROM msc_bis_inv_detail mbid
1343 WHERE mbid.plan_id = p_plan_id
1344 and mbid.sr_instance_id = p_inst_id
1345 and mbid.organization_id = p_org_id
1346 and nvl(mbid.detail_level, 0) = 0
1347 and nvl(mbid.period_type, 0) = 0;
1348 */
1349 cursor c_tp_cost_new (p_plan_id in number, l_otype1 in number, l_otype2 in number, l_otype3 in number) is
1350 select round(sum(nvl(((ms.new_order_quantity * msi.unit_weight) * mism.cost_per_weight_unit),0)),6)
1351 from msc_supplies ms,
1352 msc_system_items msi,
1353 msc_interorg_ship_methods mism,
1357 and ms.order_type in (l_otype1, l_otype2, l_otype3)
1354 msc_plans mp
1355 WHERE ms.plan_id = p_plan_id
1356 and ms.organization_id <> ms.source_organization_id
1358 and ms.plan_id = msi.plan_id
1359 and ms.organization_id = msi.organization_id
1360 and ms.sr_instance_id = msi.sr_instance_id
1361 and ms.inventory_item_id = msi.inventory_item_id
1362 and ms.plan_id = mism.plan_id
1363 and ms.organization_id = mism.to_organization_id
1364 and ms.sr_instance_id = mism.sr_instance_id
1365 and ms.source_organization_id = mism.from_organization_id
1366 and ms.source_sr_instance_id = mism.sr_instance_id2
1367 and ms.ship_method = mism.ship_method
1368 and ms.plan_id = mp.plan_id
1369 and trunc(ms.new_dock_date) between mp.curr_start_date and mp.curr_cutoff_date ;
1370
1371 l_period_count number;
1372 c1 CurTyp;
1373
1374 l_round number;
1375
1376 l_inst_id number;
1377 l_org_id number;
1378 l_run_qty number;
1379
1380 l_cursor2 varchar2(100);
1381 c2 CurTyp;
1382 ll_plan_id number;
1383
1384 BEGIN
1385 arg_ret_val := -99;
1386
1387 l_seq_id := get_form_seq_id;
1388 l_cat_set_id := get_cat_set_id(arg_plan_list);
1389 if (l_cat_set_id is null) then
1390 arg_ret_val := -1;
1391 return;
1392 end if;
1393
1394 l_plan_type := get_plan_type_for_planlist(arg_plan_list);
1395 if(l_plan_type = 9) then
1396 l_otype1 := 51;
1397 l_otype2 := 77;
1398 l_otype3 := 78;
1399 else
1400 l_otype1 := 5;
1401 l_otype2 := 11;
1402 l_otype3 := -99;
1403 end if;
1404
1405
1406 put_line(' query id : '||l_seq_id);
1407
1408 l_cursor := ' select planned_production_cost '||
1409 ' ,planned_carrying_cost, planned_purchasing_cost '||
1410 ' ,planned_total_cost, planned_revenue '||
1411 ' ,planned_gross_profit, planned_gross_profit_pct '||
1412 ' ,inventory_value, plan_id '||
1413 ' ,ext_repair_cost, int_repair_cost '||
1414 ' from msc_srvlvl_profit_v '||
1415 ' where plan_id = :1 ';
1416 --' where plan_id in ('||arg_plan_list ||') ';
1417 --||' and category_set_id ='||l_cat_set_id;
1418
1419 l_cursor2 := ' select plan_id from msc_plans where plan_id in (' ||arg_plan_list ||') ';
1420 OPEN c2 FOR l_cursor2;
1421 loop
1422 FETCH c2 INTO ll_plan_id;
1423 EXIT WHEN c2%NOTFOUND;
1424
1425 OPEN c1 FOR l_cursor using ll_plan_id;
1426 LOOP
1427 FETCH c1 INTO l_plnd_prod_cost, l_plnd_carr_cost, l_plnd_purc_cost, l_plnd_tot_cost,
1428 l_plnd_rev, l_plnd_gross_profit, l_plnd_gross_profit_pct, l_inv_value, l_plan_id,
1429 l_ext_repair_cost, l_int_repair_cost;
1430 EXIT WHEN c1%NOTFOUND;
1431 l_plan_name := msc_get_name.plan_name(l_plan_id);
1432 -- l_attained := Msc_Get_Bis_Values.get_service_level(l_plan_id, null, null, null, null, null, -1);
1433 -- l_target := Msc_Get_Bis_Values.get_target_service_level(l_plan_id, null, null, null);
1434 --l_tp_cost := nvl(msc_get_bis_values.get_tp_cost(l_plan_id, null, null, null),0);
1435
1436 l_attained := msc_analysis_pkg.get_plan_service_level(l_plan_id, g_attained_sl);
1437 l_target := msc_analysis_pkg.get_plan_service_level(l_plan_id, g_target_sl);
1438
1439 l_tp_cost := 0;
1440
1441 open c_tp_cost_new(l_plan_id, l_otype1, l_otype2, l_otype3);
1442 fetch c_tp_cost_new into l_tp_cost;
1443 close c_tp_cost_new;
1444
1445 if (l_tp_cost is null) then
1446 l_tp_cost := 0;
1447 end if;
1448
1449 l_plnd_gross_profit := l_plnd_rev - (l_plnd_tot_cost+l_tp_cost);
1450 if (l_plnd_rev = 0) then
1451 l_calc_gross_profit_pct := 0;
1452 else
1453 l_calc_gross_profit_pct := round((l_plnd_rev -
1454 (l_plnd_tot_cost + l_tp_cost)) * 100 / l_plnd_rev, 2);
1455 end if;
1456
1457 open PERIOD_CURSOR(l_plan_id);
1458 fetch PERIOD_CURSOR into l_period_count;
1459 close PERIOD_CURSOR;
1460
1461 if (l_period_count <> 0 ) then
1462 l_inv_value := l_inv_value/ l_period_count;
1463 end if;
1464 l_round := get_valid_rounding(arg_round, -23453);
1465
1466 INSERT INTO MSC_FORM_QUERY
1467 (
1468 query_id,
1469 last_update_date,
1470 last_updated_by,
1471 creation_date,
1472 created_by,
1473 number1,
1474 char1,
1475 number2,
1476 number3,
1477 number4,
1478 number5,
1479 number6,
1480 number7,
1481 number8,
1482 number9,
1483 number10,
1484 number11,
1485 number12,
1486 number13,
1487 number14
1488 )
1489 VALUES
1490 (
1491 l_seq_id,
1492 sysdate,
1493 -1,
1494 sysdate,
1495 -1,
1496 l_plan_id,
1497 l_plan_name,
1498 round(nvl(l_attained,0), l_round),
1499 round(nvl(l_target,0), l_round),
1500 round(nvl(l_plnd_prod_cost,0), l_round),
1501 round(nvl(l_plnd_carr_cost,0), l_round),
1502 round(nvl(l_plnd_purc_cost,0), l_round),
1503 round(nvl(l_tp_cost,0), l_round),
1504 round(nvl(l_plnd_tot_cost + l_tp_cost,0), l_round),
1505 round(nvl(l_plnd_rev,0), l_round),
1506 round(nvl(l_plnd_gross_profit,0), l_round),
1507 round(nvl(l_calc_gross_profit_pct,0), l_round),
1508 round(nvl(l_inv_value,0), l_round),
1509 round(nvl(l_ext_repair_cost,0), l_round),
1510 round(nvl(l_int_repair_cost,0), l_round)
1511 );
1515 end loop;
1512 END LOOP;
1513 CLOSE c1;
1514
1516 CLOSE c2;
1517
1518 arg_ret_val := l_seq_id;
1519 return ;
1520 END populate_srvlvl_profit;
1521
1522 function get_plan_service_level(p_plan_id number, p_type number,
1523 p_instance_id in number default null, p_organization_id in number default null,
1524 p_item_id in number default null,
1525 p_start_date date default null, p_end_date date default null) return number is
1526
1527 the_cursor CurTyp;
1528 sql_stat varchar2(3000);
1529
1530 v_org_id number;
1531 v_instance_id number;
1532
1533 v_qty number;
1534 v_qty2 number;
1535 v_service number;
1536 v_constraint number;
1537 v_plan_type number;
1538
1539 v_run_qty number;
1540 v_run_qty2 number;
1541 v_dmd_count number;
1542 v_cost number;
1543
1544 v_dummy number;
1545
1546 cursor c_plan_orgs (l_plan_id number) is
1547 select sr_instance_id, organization_id
1548 from msc_plan_organizations
1549 where plan_id = l_plan_id;
1550
1551 l_plan_type number;
1552 l_order_type varchar2(20);
1553
1554 begin
1555
1556 if ( p_type = g_attained_sl ) then
1557 select nvl(DAILY_RESOURCE_CONSTRAINTS,0)+
1558 nvl(WEEKLY_RESOURCE_CONSTRAINTS,0)+
1559 nvl(PERIOD_RESOURCE_CONSTRAINTS,0),
1560 plan_type
1561 into v_constraint, v_plan_type
1562 from msc_plans
1563 where plan_id = p_plan_id;
1564
1565 -- unconstrained plan is always 100%
1566 if v_constraint = 0 then
1567 return 100;
1568 end if;
1569
1570 sql_stat := 'SELECT sum(nvl(md.old_demand_quantity,0)*nvl(md.probability,1)), '||
1571 ' sum(md.USING_REQUIREMENT_QUANTITY*nvl(md.probability,1)) ' ||
1572 ' FROM msc_demands md ' ||
1573 ' WHERE md.plan_id = :1 ' ||
1574 ' AND sr_instance_id = :2 ' ||
1575 ' AND organization_id = :3 ' ||
1576 ' AND md.origination_type in (6,7,8,9,11,15,22,29,30) ';
1577
1578 elsif (p_type = g_target_sl ) then
1579
1580 sql_stat := 'SELECT avg(md.service_level), count(*) '||
1581 ' FROM msc_demands md ' ||
1582 ' WHERE md.plan_id = :1 ' ||
1583 ' AND md.sr_instance_id = :2 ' ||
1584 ' AND md.organization_id = :3 ' ||
1585 ' AND md.origination_type in (6,7,8,9,11,15,22,29,30) ';
1586
1587 elsif (p_type = g_tp_cost ) then
1588
1589 l_plan_type := get_plan_type(p_plan_id);
1590 if(l_plan_type = 9) then
1591 l_order_type :='51,77,78';
1592 else
1593 l_order_type :='5,11';
1594 end if;
1595
1596
1597 sql_stat := ' select round(sum(nvl(((ms.new_order_quantity * '||
1598 ' msi.unit_weight) * mism.cost_per_weight_unit),0)),6), 0'||
1599 ' from msc_supplies ms, '||
1600 ' msc_system_items msi, '||
1601 ' msc_interorg_ship_methods mism '||
1602 ' WHERE ms.plan_id = :1 ' ||
1603 ' and ms.organization_id != ms.source_organization_id '||
1604 ' and ms.order_type in (' || l_order_type || ') '||
1605 --' and ms.order_type in (5,11) '||
1606 ' and ms.plan_id = msi.plan_id '||
1607 ' and ms.organization_id = msi.organization_id '||
1608 ' and ms.sr_instance_id = msi.sr_instance_id '||
1609 ' and ms.inventory_item_id = msi.inventory_item_id '||
1610 ' and ms.plan_id = mism.plan_id '||
1611 ' and ms.organization_id = mism.to_organization_id '||
1612 ' and ms.sr_instance_id = mism.sr_instance_id '||
1613 ' and ms.source_organization_id = mism.from_organization_id '||
1614 ' and ms.source_sr_instance_id = mism.sr_instance_id2'||
1615 ' and ms.ship_method = mism.ship_method ' ||
1616 ' AND ms.organization_id = :2 '||
1617 ' AND ms.sr_instance_id = :3 ' ||
1618 ' AND ms.inventory_item_id = :4 ' ||
1619 ' AND trunc(ms.new_dock_date) BETWEEN :5 AND :6 ';
1620
1621 v_cost := 0;
1622 --put_line('get_plan_service_level '|| sql_stat);
1623
1624 open the_cursor for sql_stat using p_plan_id, p_organization_id,
1625 p_instance_id, p_item_id, trunc(p_start_date), trunc(p_end_date);
1626 fetch the_cursor into v_cost,v_dummy; -- bug 4387200
1627 close the_cursor;
1628 return nvl(v_cost, 0);
1629 put_line('get_plan_service_level- after executing tp_cost qry');
1630
1631 end if;
1632
1633 v_qty := 0;
1634 v_qty2 := 0;
1635 v_dmd_count := 0;
1636
1637 open c_plan_orgs(p_plan_id);
1638 loop
1639 fetch c_plan_orgs into v_instance_id, v_org_id;
1640 exit when c_plan_orgs%notfound;
1641
1642 open the_cursor for sql_stat using p_plan_id, v_instance_id, v_org_id;
1643 fetch the_cursor into v_run_qty, v_run_qty2;
1644 close the_cursor;
1645
1646 if ( p_type = g_attained_sl ) then
1647 v_qty := v_qty + nvl(v_run_qty,0);
1648 v_qty2 := v_qty2 + nvl(v_run_qty2,0);
1649 elsif (p_type = g_target_sl ) then
1650 v_qty := v_qty + (nvl(v_run_qty, 0) * nvl(v_run_qty2,0));
1651 v_dmd_count := v_dmd_count + nvl(v_run_qty2,0);
1652 end if;
1653
1654 end loop;
1655 close c_plan_orgs;
1656
1657 if ( p_type = g_attained_sl ) then
1658 -- there is no demand, will show 100%
1662 v_service := 0;
1659 if nvl(v_qty2,0) =0 then
1660 v_service := 100;
1661 elsif nvl(v_qty,0)=0 then
1663 else
1664 v_service := round(v_qty/v_qty2*100,6);
1665 end if;
1666 return v_service;
1667 elsif (p_type = g_target_sl ) then
1668 if ( nvl(v_dmd_count,0) = 0 ) then
1669 v_qty := 0;
1670 else
1671 v_qty := v_qty / v_dmd_count;
1672 end if;
1673 return v_qty;
1674 end if;
1675
1676 END get_plan_service_level;
1677
1678 function get_plan_dflt_value(p_plan_id number) return number is
1679 cursor c_dflt is
1680 select nvl(demand_fulfillment_lt,0)
1681 from msc_plans
1682 where plan_id = p_plan_id;
1683
1684 l_dflt number;
1685 begin
1686 open c_dflt;
1687 fetch c_dflt into l_dflt;
1688 close c_dflt;
1689
1690 return l_dflt;
1691 end get_plan_dflt_value;
1692
1693 function get_dflt_value(p_plan_id number,
1694 p_cate_set_id number default null,
1695 p_definition_level number default null,
1696 p_inst_id number default null, p_org_id number default null,
1697 p_item_id number default null,
1698 p_demand_class varchar2 default null,
1699 p_customer_id number default null,
1700 p_customer_site_id number default null,
1701 p_cate_id number default null) return number is
1702
1703 l_cate_set_id number;
1704 l_dflt number;
1705 l_dflt_level number;
1706
1707 cursor c_dflt is
1708 select nvl(demand_fulfillment_lead_time,0)
1709 from msc_service_levels
1710 where plan_id = p_plan_id
1711 --and category_set_id = l_cate_set_id
1712 and definition_level = l_dflt_level
1713 and ( ( sr_instance_id is null and organization_id is null
1714 and p_inst_id is null and p_org_id is null)
1715 or (sr_instance_id = p_inst_id and organization_id = p_org_id))
1716 and ( ( inventory_item_id is null and p_item_id is null)
1717 or (inventory_item_id = p_item_id) )
1718 and ( ( demand_class is null and p_demand_class is null)
1719 or (demand_class = p_demand_class) )
1720 and ( ( customer_id is null and p_customer_id is null)
1721 or (customer_id = p_customer_id) )
1722 and ( ( customer_site_id is null and p_customer_site_id is null)
1723 or (customer_site_id = p_customer_site_id) )
1724 and ( ( sr_category_id is null and p_cate_id is null)
1725 or (sr_category_id = p_cate_id) );
1726
1727 cursor c_dflt_with_level is
1728 select nvl(demand_fulfillment_lead_time,0)
1729 from msc_service_levels
1730 where plan_id = p_plan_id
1731 --and category_set_id = l_cate_set_id
1732 and definition_level = nvl(p_definition_level, definition_level)
1733 --and nvl(sr_instance_id, -1) = nvl(p_inst_id, -1) --not req as they populate for every row
1734 and nvl(organization_id,-1) = nvl(p_org_id,-1)
1735 and nvl(inventory_item_id,-1) = nvl(p_item_id,-1)
1736 and nvl(demand_class,'-1') = nvl(p_demand_class,'-1')
1737 and nvl(customer_id,-1) = nvl(p_customer_id,-1)
1738 and nvl(customer_site_id,-1) = nvl(p_customer_site_id,-1)
1739 and nvl(sr_category_id,-1) = nvl(p_cate_id,-1)
1740 order by definition_level desc;
1741
1742 begin
1743 l_cate_set_id := msc_analysis_pkg.get_cat_set_id(p_plan_id);
1744
1745 if (p_definition_level = 0) then
1746 l_dflt := msc_analysis_pkg.get_plan_dflt_value(p_plan_id);
1747 else
1748 open c_dflt_with_level;
1749 fetch c_dflt_with_level into l_dflt;
1750 close c_dflt_with_level;
1751 end if;
1752
1753 return l_dflt;
1754
1755 -- ------------------------------------------------
1756 -- Label in drop down list Corresponding DB value
1757 -- ------------------------------------------------
1758 -- Item-Org -Demand Class 10
1759 -- Item-Category-Demand Class 9
1760 -- Item-Demand Class 8
1761 -- Item-Org 7
1762 -- Category 6
1763 -- Org-Demand Class 5
1764 -- Demand Class 4
1765 -- Customer site 3
1766 -- Customer 2
1767 -- Org 1
1768 -- Plan 0
1769 -- -------------------------------------------------
1770 /*
1771 if ( p_inst_id is null and p_org_id is null
1772 and p_item_id is null
1773 and p_demand_class is null
1774 and p_customer_id is null
1775 and p_customer_site_id is null
1776 and p_cate_id is null) then
1777 l_dflt_level := -23453;
1778 elsif ( p_inst_id is NOT null and p_org_id is NOT null
1779 and p_item_id is null
1780 and p_demand_class is null
1781 and p_customer_id is null
1782 and p_customer_site_id is null
1783 and p_cate_id is null) then
1784 l_dflt_level := 1;
1785 elsif ( p_inst_id is null and p_org_id is null
1786 and p_item_id is null
1787 and p_demand_class is null
1788 and p_customer_id is NOT null
1789 and p_customer_site_id is null
1790 and p_cate_id is null) then
1791 l_dflt_level := 2;
1792 elsif ( p_inst_id is null and p_org_id is null
1793 and p_item_id is null
1794 and p_demand_class is null
1795 and p_customer_id is null
1796 and p_customer_site_id is NOT null
1797 and p_cate_id is null) then
1798 l_dflt_level := 3;
1802 and p_customer_id is null
1799 elsif ( p_inst_id is null and p_org_id is null
1800 and p_item_id is null
1801 and p_demand_class is NOT null
1803 and p_customer_site_id is null
1804 and p_cate_id is null) then
1805 l_dflt_level := 4;
1806 elsif ( p_inst_id is NOT null and p_org_id is NOT null
1807 and p_item_id is null
1808 and p_demand_class is NOT null
1809 and p_customer_id is null
1810 and p_customer_site_id is null
1811 and p_cate_id is null) then
1812 l_dflt_level := 5;
1813 elsif ( p_inst_id is null and p_org_id is null
1814 and p_item_id is null
1815 and p_demand_class is null
1816 and p_customer_id is null
1817 and p_customer_site_id is null
1818 and p_cate_id is NOT null) then
1819 l_dflt_level := 6;
1820 elsif ( p_inst_id is NOT null and p_org_id is NOT null
1821 and p_item_id is NOT null
1822 and p_demand_class is null
1823 and p_customer_id is null
1824 and p_customer_site_id is null
1825 and p_cate_id is null) then
1826 l_dflt_level := 7;
1827 elsif ( p_inst_id is null and p_org_id is null
1828 and p_item_id is NOT null
1829 and p_demand_class is NOT null
1830 and p_customer_id is null
1831 and p_customer_site_id is null
1832 and p_cate_id is null) then
1833 l_dflt_level := 8;
1834 elsif ( p_inst_id is null and p_org_id is null
1835 and p_item_id is NOT null
1836 and p_demand_class is NOT null
1837 and p_customer_id is null
1838 and p_customer_site_id is null
1839 and p_cate_id is NOT null) then
1840 l_dflt_level := 9;
1841 elsif ( p_inst_id is NOT null and p_org_id is NOT null
1842 and p_item_id is NOT null
1843 and p_demand_class is NOT null
1844 and p_customer_id is null
1845 and p_customer_site_id is null
1846 and p_cate_id is null) then
1847 l_dflt_level := 10;
1848 end if;
1849
1850 open c_dflt;
1851 fetch c_dflt into l_dflt;
1852 close c_dflt;
1853 */
1854 end get_dflt_value;
1855
1856 END MSC_ANALYSIS_PKG;