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