[Home] [Help]
PACKAGE BODY: APPS.MSC_NETCHANGE_PKG
Source
1 PACKAGE BODY Msc_NETCHANGE_PKG AS
2 /* $Header: MSCNETCB.pls 120.2 2005/07/01 08:41:01 eychen noship $ */
3
4 TYPE number_arr IS TABLE OF number;
5 TYPE date_arr IS TABLE OF date;
6 TYPE char_arr IS TABLE OF varchar2(250);
7 TYPE long_char_arr IS TABLE OF varchar2(500);
8
9 NOT_COMPARED CONSTANT INTEGER :=1;
10 IN_PROGRESS CONSTANT INTEGER :=2;
11 NEED_RECOMPARE CONSTANT INTEGER :=3;
12 AVAILABLE INTEGER :=4;
13
14 g_options_query_id number;
15 g_excp_query_id number;
16 g_from_plan number;
17 g_to_plan number;
18 g_cat_set number;
19 g_cat_set_name varchar2(30);
20 g_yes varchar2(10);
21 g_no varchar2(10);
22 g_misc char_arr;
23 g_need_insert_temp boolean;
24 g_long_query boolean;
25
26 Procedure compare_plans(from_plan number,
27 to_plan number,
28 options_flag number,
29 p_folder_id number,
30 exception_list varchar2,
31 p_criteria_id number,
32 option_query_id out nocopy number,
33 exception_query_id out nocopy number
34 ) IS
35 item_where_clause varchar2(32000);
36 res_where_clause varchar2(32000);
37 p_all_excp_list varchar2(500):=',1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,';
38 p_excp_list varchar2(500);
39 p_group_by_id number;
40 p_options_flag number := 1;
41 one_rec varchar2(400);
42 p_operator varchar2(30);
43
44 CURSOR criteria_c IS
45 select mnc.group_by_id,
46 mnc.exception_type,
47 mnc.options_flag
48 from msc_net_change_criteria mnc
49 where mnc.criteria_id = p_criteria_id
50 and mnc.user_id = fnd_global.user_id;
51
52 CURSOR filter_c IS
53 select msc.field_name,
54 msc.hidden_from_field,
55 msc.condition,
56 msc.from_field,
57 msc.to_field,
58 msc.field_type,
59 mc.data_set,
60 msc.folder_object
61 from msc_selection_criteria msc,
62 msc_criteria mc
63 where msc.folder_id = p_folder_id
64 and msc.folder_object in ('MSC_NET_ITEM','MSC_NET_RESOURCE')
65 and msc.folder_object = mc.folder_object
66 and msc.field_name = mc.field_name;
67
68 item_rec filter_c%ROWTYPE;
69 a number;
70 BEGIN
71 option_query_id :=0;
72 exception_query_id :=0;
73 g_from_plan := from_plan;
74 g_to_plan := to_plan;
75
76 if options_flag is null then
77 OPEN criteria_c;
78 FETCH criteria_c INTO p_group_by_id, p_excp_list, p_options_flag;
79 CLOSE criteria_c;
80
81 else
82 p_options_flag := options_flag;
83 p_excp_list := exception_list;
84 end if;
85 g_need_insert_temp := false;
86 g_long_query := false;
87
88 if p_excp_list is null then
89 p_excp_list := p_all_excp_list;
90 end if;
91
92 if length(p_excp_list) < length(p_all_excp_list) then
93 g_need_insert_temp := true;
94 end if;
95
96 if p_folder_id is not null then
97 OPEN filter_c;
98 LOOP
99 FETCH filter_c INTO item_rec;
100 EXIT WHEN filter_c%NOTFOUND;
101 if item_rec.field_name not in
102 ('ITEM_NAME','ITEM_NAME2','PLANNER_CODE') then
103 g_long_query := true;
104 end if;
105 if item_rec.field_name in ('ITEM_NAME','ITEM_NAME2') then
106 item_rec.field_name := 'med.char1';
107 elsif item_rec.field_name = 'PLANNER_CODE' then
108 item_rec.field_name := 'med.char2';
109 elsif item_rec.field_name = 'DEPARTMENT_CODE' then
110 item_rec.field_name := 'medv.DEPARTMENT_LINE_CODE';
111 else
112 item_rec.field_name := 'medv.'||item_rec.field_name;
113 end if;
114
115 if item_rec.condition = 1 then
116 if item_rec.data_set is not null and
117 item_rec.hidden_from_field is not null and
118 item_rec.field_name not in ('med.char1','med.char2') then
119 one_rec := 'medv.'||item_rec.data_set ||' = '||
120 item_rec.hidden_from_field;
121 else
122 one_rec := item_rec.field_name ||' = '||''''||
123 item_rec.from_field||'''';
124 end if;
125 elsif item_rec.condition in (2,3,4,5,6) then
126 p_operator := convert_condition(item_rec.condition);
127 one_rec := item_rec.field_name || p_operator ||''''||
128 item_rec.from_field||'''';
129 elsif item_rec.condition in (9,10) then
130 p_operator := convert_condition(item_rec.condition);
131 one_rec := item_rec.field_name || p_operator;
132 elsif item_rec.condition in (7,8) then
133 p_operator := convert_condition(item_rec.condition);
134 one_rec := item_rec.field_name || p_operator ||''''||
135 item_rec.from_field || ''''||' AND '||''''||
136 item_rec.to_field||'''';
137 end if;
138 if item_rec.folder_object = 'MSC_NET_ITEM' then
139 item_where_clause := item_where_clause || ' AND '||
140 one_rec;
141 else
142 res_where_clause := res_where_clause || ' AND '||
143 one_rec;
144 end if;
145 END LOOP;
146 CLOSE filter_c;
147
148 end if;
149
150 if p_options_flag = 1 then
151
152 select substr(meaning,1,10)
153 into g_yes
154 from mfg_lookups
155 where lookup_type = 'SYS_YES_NO'
156 and lookup_code = 1;
157
158 select substr(meaning,1,10)
159 into g_no
160 from mfg_lookups
161 where lookup_type = 'SYS_YES_NO'
162 and lookup_code = 2;
163
164 select substr(meaning,1,35)
165 bulk collect into g_misc
166 from mfg_lookups
167 where lookup_type = 'MSC_NC_MISC_PROMPTS'
168 order by lookup_code;
169
170 select msc_form_query_s.nextval
171 into g_options_query_id
172 from dual;
173
174 option_query_id :=g_options_query_id;
175 compare_options;
176 compare_aggregate;
177 compare_optimize;
178 compare_goalprog;
179 compare_constraints;
180 compare_orgs;
181 compare_schedules;
182 end if;
183
184 if p_excp_list is not null then
185
186 compare_exceptions(
187 p_excp_list,
188 item_where_clause,
189 res_where_clause);
190 exception_query_id :=g_excp_query_id;
191 end if;
192
193 END compare_plans;
194
195 Function convert_condition(operator number) RETURN varchar2 IS
196 translated_op varchar2(30);
197 BEGIN
198 IF operator = 1 THEN
199 translated_op := ' = ';
200 ELSIF operator = 2 THEN
201 translated_op := ' <> ';
202 ELSIF operator = 3 THEN
203 translated_op := ' < ';
204 ELSIF operator = 4 THEN
205 translated_op := ' <= ';
206 ELSIF operator = 5 THEN
207 translated_op := ' >= ';
208 ELSIF operator = 6 THEN
209 translated_op := ' > ';
210 ELSIF operator = 7 THEN
211 translated_op := ' BETWEEN ';
212 ELSIF operator = 8 THEN
213 translated_op := ' NOT BETWEEN ';
214 ELSIF operator = 9 THEN
215 translated_op := ' IS NULL ';
216 ELSIF operator = 10 THEN
217 translated_op := ' IS NOT NULL ';
218 END IF;
219 return translated_op;
220
221 END convert_condition;
222
223 function calculate_start_date (p_org_id IN NUMBER,
224 p_sr_instance_id IN NUMBER,
225 p_plan_start_date IN DATE,
226 p_daily_cutoff_bucket IN NUMBER,
227 p_weekly_cutoff_bucket IN NUMBER,
228 p_period_cutoff_bucket IN NUMBER)
229 return varchar2 is
230 l_daily_start_date DATE;
231 l_weekly_start_date DATE;
232 l_period_start_date DATE;
233 l_curr_cutoff_date DATE;
234
235 l_retval varchar2(150);
236 begin
237 msc_snapshot_pk.calculate_start_date(p_org_id,
238 p_sr_instance_id,
239 p_plan_start_date,
240 p_daily_cutoff_bucket,
241 p_weekly_cutoff_bucket,
242 p_period_cutoff_bucket,
243 l_daily_start_date,
244 l_weekly_start_date,
245 l_period_start_date,
246 l_curr_cutoff_date);
247 l_retval := fnd_date.date_to_chardate(l_daily_start_date) ||' Days, '||
248 fnd_date.date_to_chardate(l_weekly_start_date) ||' Weeks, '||
249 fnd_date.date_to_chardate(l_period_start_date) ||' Periods ';
250 return l_retval;
251 exception
252 when others then
253 return null;
254 end ;
255
256
257 Procedure compare_options IS
258 p_plan_id number;
259
260 cursor option_c is
261 select ml.meaning, --0 plan_type
262 decode(mp.CURR_PART_INCLUDE_TYPE, --1 Planned items
263 1, g_misc(5), -- 'All planned items',
264 2, g_misc(6), -- 'Demand scheduled items only',
265 3, g_misc(7), -- 'Supply scheduled items only',
266 4, g_misc(8)), -- 'Demand and Supply scheduled items'),
267 MAS.ASSIGNMENT_SET_NAME, --2
268 decode(mp.CURR_OPERATION_SCHEDULE_TYPE, --3 Material Scheduling Method
269 1, g_misc(9), --'Operation Start Date',
270 2, g_misc(10)), -- 'Order Start Date'),
271 msc_get_name.dmd_priority_rule(mp.CURR_DEM_PRIORITY_RULE_ID), --4
272 mp.SUBSTITUTION_DESIGNATOR, --5
273 decode(mp.CURR_OVERWRITE_OPTION, --6
274 1, g_misc(13), --'All',
275 2, g_misc(14), -- 'Outside planning time fence',
276 3, g_misc(4)), -- 'None'),
277 msc_get_name.demand_class(mp.sr_instance_id,
278 mp.organization_id,
279 mp.compile_designator),--7
280 nvl(decode(mp.CURR_DEMAND_TIME_FENCE_FLAG,1,g_yes,g_no),g_no), --8
281 nvl(decode(mp.CURR_APPEND_PLANNED_ORDERS,1,g_yes,g_no),g_no), --9
282 nvl(decode(mp.CURR_PLANNING_TIME_FENCE_FLAG,1,g_yes,g_no),g_no), --10
283 nvl(decode(mp.plan_inventory_point,1,g_yes,g_no),g_no), --11
284 nvl(decode(mp.lot_for_lot,1,g_yes,g_no),g_no), --12
285 null, -- 13 Default Forecast Consumption Days '
286 mp.curr_backward_days, --14
287 mp.curr_forward_days, --15
288 nvl(decode(mp.CURR_FULL_PEGGING,1,g_yes,g_no),g_no), --16 enable pegging
289 nvl(decode(mp.curr_closest_qty_peg,1,g_yes,g_no),g_no), --17 peg to closest qty
290 decode(mp.CURR_RESERVATION_LEVEL, --18 reservation level
291 1, g_misc(1), -- 'Planning Group',
292 2, g_misc(2), --'Project',
293 3, g_misc(3), -- 'Project-Task',
294 4, g_misc(4)), --'None'),
295 nvl(decode(mp.curr_priority_pegging,1,g_yes,g_no),g_no), --19
296 decode(mp.CURR_HARD_PEGGING_LEVEL, --3
297 1,g_misc(2), -- 'Project',
298 2, g_misc(3), --'Project-Task',
299 3, g_misc(4)) --'None')
300 from msc_plans mp,
301 mfg_lookups ml,
302 msc_assignment_sets mas
303 where mp.plan_id = p_plan_id
304 and ml.lookup_type = 'MRP_PLAN_TYPE'
305 and ml.lookup_code = mp.curr_plan_type
306 and mas.assignment_set_id (+) = mp.CURR_ASSIGNMENT_SET_ID;
307
308 TYPE CharTab IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
309 plan_a_rec CharTab;
310 plan_b_rec CharTab;
311 a number;
312 Begin
313
314 p_plan_id := g_from_plan;
315 OPEN option_c;
316 FETCH option_c INTO plan_a_rec(0),
317 plan_a_rec(1),
318 plan_a_rec(2),
319 plan_a_rec(3),
320 plan_a_rec(4),
321 plan_a_rec(5),
322 plan_a_rec(6),
323 plan_a_rec(7),
324 plan_a_rec(8),
325 plan_a_rec(9),
326 plan_a_rec(10),
327 plan_a_rec(11),
328 plan_a_rec(12),
329 plan_a_rec(13),
330 plan_a_rec(14),
331 plan_a_rec(15),
332 plan_a_rec(16),
333 plan_a_rec(17),
334 plan_a_rec(18),
335 plan_a_rec(19),
336 plan_a_rec(20);
337 CLOSE option_c;
338
339 p_plan_id := g_to_plan;
340 OPEN option_c;
341 FETCH option_c INTO plan_b_rec(0),
342 plan_b_rec(1),
343 plan_b_rec(2),
344 plan_b_rec(3),
345 plan_b_rec(4),
346 plan_b_rec(5),
347 plan_b_rec(6),
348 plan_b_rec(7),
349 plan_b_rec(8),
350 plan_b_rec(9),
351 plan_b_rec(10),
352 plan_b_rec(11),
353 plan_b_rec(12),
354 plan_b_rec(13),
355 plan_b_rec(14),
356 plan_b_rec(15),
357 plan_b_rec(16),
358 plan_b_rec(17),
359 plan_b_rec(18),
360 plan_b_rec(19),
361 plan_b_rec(20);
362 CLOSE option_c;
363
364 for a in 0 .. plan_a_rec.count-1 loop
365 /*
366 if plan_a_rec(a) <> plan_b_rec(a) or
367 (plan_a_rec(a) is null and plan_b_rec(a) is not null) or
368 (plan_a_rec(a) is not null and plan_b_rec(a) is null) then
369 */
370 insert into msc_form_query
371 (QUERY_ID,
372 LAST_UPDATE_DATE,
373 LAST_UPDATED_BY,
374 CREATION_DATE,
375 CREATED_BY,
376 LAST_UPDATE_LOGIN,
377 NUMBER1,
378 NUMBER2,
379 char1,
380 char2)
381 select
385 sysdate,
382 g_options_query_id,
383 sysdate,
384 -1,
386 -1,
387 -1,
388 1, -- options
389 a,
390 plan_a_rec(a),
391 plan_b_rec(a)
392 from dual;
393 -- end if;
394 end loop;
395
396 End compare_options;
397
398 Procedure compare_aggregate IS
399
400 p_plan_id number;
401
402 cursor option_c is
403 select
404 mp.CURR_START_DATE, -- start aggregate
405 mp.CURR_CUTOFF_DATE,
406 calculate_start_date(mp.organization_id,
407 mp.sr_instance_id,
408 mp.CURR_START_DATE,
409 mp.DAILY_CUTOFF_BUCKET,
410 mp.WEEKLY_CUTOFF_BUCKET,
411 mp.PERIOD_CUTOFF_BUCKET),
412 mp.DAILY_CUTOFF_BUCKET ||' '||g_misc(15)||', '||--' days, '||
413 mp.WEEKLY_CUTOFF_BUCKET ||' '||g_misc(16)||', '||--' weeks, '||
414 mp.PERIOD_CUTOFF_BUCKET ||' '||g_misc(17),--' periods ',
415 decode(mp.DAILY_ITEM_AGGREGATION_LEVEL,
416 1, g_misc(18), --'Items',
417 2, g_misc(19))||', '|| --'Product Family') || ','||
418 decode(mp.WEEKLY_ITEM_AGGREGATION_LEVEL,
419 1, g_misc(18), --'Items',
420 2, g_misc(19))||', '|| --'Product Family') ||','||
421 decode(mp.PERIOD_ITEM_AGGREGATION_LEVEL,
422 1, g_misc(18), --'Items',
423 2, g_misc(19)), --'Product Family'),
424 decode(mp.DAILY_RES_AGGREGATION_LEVEL,
425 1, g_misc(20), --'Individual',
426 2, g_misc(21))||', '|| --'Aggregate')||','||
427 decode(mp.WEEKLY_RES_AGGREGATION_LEVEL,
428 1, g_misc(20), --'Individual',
429 2, g_misc(21))||', '|| --'Aggregate') ||','||
430 decode(mp.PERIOD_RES_AGGREGATION_LEVEL,
431 1, g_misc(20), --'Individual',
432 2, g_misc(21)), --'Aggregate'),
433 decode(mp.DAILY_RTG_AGGREGATION_LEVEL,
434 1,g_misc(22), --'Routings',
435 2, g_misc(22))||', '|| --'BOR') ||','||
436 decode(mp.WEEKLY_RTG_AGGREGATION_LEVEL,
437 1,g_misc(22), --'Routings',
438 2,g_misc(22))||', '|| -- 'BOR') ||','||
439 decode(mp.PERIOD_RTG_AGGREGATION_LEVEL,
440 1,g_misc(22), --'Routings',
441 2,g_misc(22)) -- 'BOR')
442 from msc_plans mp
443 where mp.plan_id = p_plan_id;
444
445 TYPE CharTab IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
446 plan_a_rec CharTab;
447 plan_b_rec CharTab;
448 a number;
449
450 Begin
451
452 p_plan_id := g_from_plan;
453 OPEN option_c;
454 FETCH option_c INTO plan_a_rec(1),
455 plan_a_rec(2),
456 plan_a_rec(3),
457 plan_a_rec(4),
458 plan_a_rec(5),
459 plan_a_rec(6),
460 plan_a_rec(7);
461 CLOSE option_c;
462
463 p_plan_id := g_to_plan;
464 OPEN option_c;
465 FETCH option_c INTO plan_b_rec(1),
466 plan_b_rec(2),
467 plan_b_rec(3),
468 plan_b_rec(4),
469 plan_b_rec(5),
470 plan_b_rec(6),
471 plan_b_rec(7);
472 CLOSE option_c;
473
474 for a in 1 .. plan_a_rec.count loop
475 insert into msc_form_query
476 (QUERY_ID,
477 LAST_UPDATE_DATE,
478 LAST_UPDATED_BY,
479 CREATION_DATE,
480 CREATED_BY,
481 LAST_UPDATE_LOGIN,
482 NUMBER1,
483 NUMBER2,
484 char1,
485 char2)
486 select
487 g_options_query_id,
488 sysdate,
489 -1,
490 sysdate,
491 -1,
492 -1,
493 2, -- aggregate
494 a,
495 plan_a_rec(a),
496 plan_b_rec(a)
497 from dual;
498 end loop;
499
500 End compare_aggregate;
501
502 Procedure compare_optimize IS
503 p_plan_id number;
504
505 cursor option_c is
506 select
507 decode(mp.OPTIMIZE_FLAG,1, g_yes, g_no), -- start optimize
508 decode(mp.CURR_ENFORCE_SRC_CONSTRAINTS,1, g_yes, g_no),
509 null, -- dummy field for objective
510 mp.OBJECTIVE_WEIGHT_1,
514 mp.SUPPLIER_CAP_OVER_UTIL_COST,
511 mp.OBJECTIVE_WEIGHT_2,
512 mp.OBJECTIVE_WEIGHT_4,
513 null, -- dummy field for plan level defaults
515 mp.TRANSPORT_CAP_OVER_UTIL_COST,
516 mp.RESOURCE_OVER_UTIL_COST,
517 mp.DMD_LATENESS_PENALTY_COST -- end optimize
518 from msc_plans mp
519 where mp.plan_id = p_plan_id;
520
521
522 TYPE CharTab IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
523 plan_a_rec CharTab;
524 plan_b_rec CharTab;
525 a number;
526 Begin
527 p_plan_id := g_from_plan;
528 OPEN option_c;
529 FETCH option_c INTO plan_a_rec(1),
530 plan_a_rec(2),
531 plan_a_rec(3),
532 plan_a_rec(4),
533 plan_a_rec(5),
534 plan_a_rec(6),
535 plan_a_rec(7),
536 plan_a_rec(8),
537 plan_a_rec(9),
538 plan_a_rec(10),
539 plan_a_rec(11);
540 CLOSE option_c;
541
542 p_plan_id := g_to_plan;
543 OPEN option_c;
544 FETCH option_c INTO plan_b_rec(1),
545 plan_b_rec(2),
546 plan_b_rec(3),
547 plan_b_rec(4),
548 plan_b_rec(5),
549 plan_b_rec(6),
550 plan_b_rec(7),
551 plan_b_rec(8),
552 plan_b_rec(9),
553 plan_b_rec(10),
554 plan_b_rec(11);
555 CLOSE option_c;
556
557 for a in 1 .. plan_a_rec.count loop
558
559 insert into msc_form_query
560 (QUERY_ID,
561 LAST_UPDATE_DATE,
562 LAST_UPDATED_BY,
563 CREATION_DATE,
564 CREATED_BY,
565 LAST_UPDATE_LOGIN,
566 NUMBER1,
567 NUMBER2,
568 char1,
569 char2)
570 select
571 g_options_query_id,
572 sysdate,
573 -1,
574 sysdate,
575 -1,
576 -1,
577 3, -- optimize
578 a,
579 plan_a_rec(a),
580 plan_b_rec(a)
581 from dual;
582
583 end loop;
584
585 End compare_optimize;
586
587 Procedure compare_constraints IS
588 p_plan_id number;
589
590 cursor option_c is
591 select
592 mp.CURR_START_DATE, -- start aggregate
593 mp.CURR_CUTOFF_DATE,
594 decode(nvl(mp.DAILY_RESOURCE_CONSTRAINTS, 2)||
595 nvl(mp.WEEKLY_RESOURCE_CONSTRAINTS,2)||
596 nvl(mp.PERIOD_RESOURCE_CONSTRAINTS,2)||
597 nvl(mp.DAILY_MATERIAL_CONSTRAINTS, 2)||
598 nvl(mp.WEEKLY_MATERIAL_CONSTRAINTS,2)||
599 nvl(mp.PERIOD_MATERIAL_CONSTRAINTS,2),'222222',g_no,g_yes),
600 nvl(decode(mp.CURR_ENFORCE_DEM_DUE_DATES,1,g_yes,g_no),g_no),
601 nvl(decode(mp.CURR_ENFORCE_CAP_CONSTRAINTS,1,g_yes,g_no),g_no),
602 calculate_start_date(mp.organization_id,
603 mp.sr_instance_id,
604 mp.CURR_START_DATE,
605 mp.DAILY_CUTOFF_BUCKET,
606 mp.WEEKLY_CUTOFF_BUCKET,
607 mp.PERIOD_CUTOFF_BUCKET),
608 mp.DAILY_CUTOFF_BUCKET ||' '||g_misc(15)||', '||--' days, '||
609 mp.WEEKLY_CUTOFF_BUCKET ||' '||g_misc(16)||', '||--' weeks, '||
610 mp.PERIOD_CUTOFF_BUCKET ||' '||g_misc(17),--' periods ',
611 nvl(
612 decode(mp.DAILY_RESOURCE_CONSTRAINTS, 1, g_misc(15)) ||
613 decode(mp.WEEKLY_RESOURCE_CONSTRAINTS, 1, ', '||g_misc(16))||
614 decode(mp.PERIOD_RESOURCE_CONSTRAINTS, 1, ', '||g_misc(17)),g_misc(4)),
615 nvl(
616 decode(mp.DAILY_MATERIAL_CONSTRAINTS, 1, g_misc(15)) ||
617 decode(mp.WEEKLY_MATERIAL_CONSTRAINTS, 1, ', '||g_misc(16))||
618 decode(mp.PERIOD_MATERIAL_CONSTRAINTS, 1, ', '||g_misc(17)),g_misc(4)),
619 --decode(mp.SCHEDULE_FLAG,1, g_yes, g_no),
620 null, --scheduling prompt
621 mp.MIN_CUTOFF_BUCKET,
622 mp.HOUR_CUTOFF_BUCKET,
623 null, --days cutoff bucket
624 decode(mp.CURR_PLAN_CAPACITY_FLAG,1, g_yes, g_no),
625 decode(mp.CURR_PLANNED_RESOURCES,
626 1, g_misc(11), -- 'All Resources',
627 2, g_misc(12)), --'Bottleneck Resources'),
628 mp.CURR_BOTTLENECK_RES_GROUP
629 from msc_plans mp
630 where mp.plan_id = p_plan_id;
631 TYPE CharTab IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
632 plan_a_rec CharTab;
633 plan_b_rec CharTab;
634 a number;
635 Begin
636 p_plan_id := g_from_plan;
637 OPEN option_c;
641 plan_a_rec(4),
638 FETCH option_c INTO plan_a_rec(1),
639 plan_a_rec(2),
640 plan_a_rec(3),
642 plan_a_rec(5),
643 plan_a_rec(6),
644 plan_a_rec(7),
645 plan_a_rec(8),
646 plan_a_rec(9),
647 plan_a_rec(10),
648 plan_a_rec(11),
649 plan_a_rec(12),
650 plan_a_rec(13),
651 plan_a_rec(14),
652 plan_a_rec(15),
653 plan_a_rec(16);
654
655 CLOSE option_c;
656
657 p_plan_id := g_to_plan;
658 OPEN option_c;
659 FETCH option_c INTO plan_b_rec(1),
660 plan_b_rec(2),
661 plan_b_rec(3),
662 plan_b_rec(4),
663 plan_b_rec(5),
664 plan_b_rec(6),
665 plan_b_rec(7),
666 plan_b_rec(8),
667 plan_b_rec(9),
668 plan_b_rec(10),
669 plan_b_rec(11),
670 plan_b_rec(12),
671 plan_b_rec(13),
672 plan_b_rec(14),
673 plan_b_rec(15),
674 plan_b_rec(16);
675 CLOSE option_c;
676
677 for a in 1 .. plan_a_rec.count loop
678
679
680 insert into msc_form_query
681 (QUERY_ID,
682 LAST_UPDATE_DATE,
683 LAST_UPDATED_BY,
684 CREATION_DATE,
685 CREATED_BY,
686 LAST_UPDATE_LOGIN,
687 NUMBER1,
688 NUMBER2,
689 char1,
690 char2)
691 select
692 g_options_query_id,
693 sysdate,
694 -1,
695 sysdate,
696 -1,
697 -1,
698 7, -- constraints
699 a,
700 plan_a_rec(a),
701 plan_b_rec(a)
702 from dual;
703
704 end loop;
705
706 end compare_constraints;
707
708 Procedure compare_goalprog IS
709 p_plan_id number;
710
711 cursor option_c is
712 select nvl(decode(mp.USE_END_ITEM_SUBSTITUTIONS,1,g_yes,g_no),g_no),
713 nvl(decode(mp.USE_ALTERNATE_RESOURCES,1,g_yes,g_no),g_no),
714 nvl(decode(mp.USE_SUBSTITUTE_COMPONENTS,1,g_yes,g_no),g_no),
715 nvl(decode(mp.USE_ALTERNATE_BOM_ROUTING,1,g_yes,g_no),g_no),
716 nvl(decode(mp.USE_ALTERNATE_SOURCES,1,g_yes,g_no),g_no)
717 from msc_plans mp
718 where mp.plan_id = p_plan_id;
719
720 TYPE CharTab IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
721 plan_a_rec CharTab;
722 plan_b_rec CharTab;
723 a number;
724 Begin
725 p_plan_id := g_from_plan;
726 OPEN option_c;
727 FETCH option_c INTO plan_a_rec(1),
728 plan_a_rec(2),
729 plan_a_rec(3),
730 plan_a_rec(4),
731 plan_a_rec(5);
732 CLOSE option_c;
733
734 p_plan_id := g_to_plan;
735 OPEN option_c;
736 FETCH option_c INTO plan_b_rec(1),
737 plan_b_rec(2),
738 plan_b_rec(3),
739 plan_b_rec(4),
740 plan_b_rec(5);
741 CLOSE option_c;
742
743 for a in 1 .. plan_a_rec.count loop
744
745 insert into msc_form_query
746 (QUERY_ID,
747 LAST_UPDATE_DATE,
748 LAST_UPDATED_BY,
749 CREATION_DATE,
750 CREATED_BY,
751 LAST_UPDATE_LOGIN,
752 NUMBER1,
753 NUMBER2,
754 char1,
755 char2)
756 select
757 g_options_query_id,
758 sysdate,
759 -1,
760 sysdate,
761 -1,
762 -1,
763 8, -- optimize
764 a,
765 plan_a_rec(a),
766 plan_b_rec(a)
767 from dual;
768 end loop;
769
770 end compare_goalprog;
771
772 Procedure compare_orgs IS
773
774 Begin
775 insert into msc_form_query
776 (QUERY_ID,
777 LAST_UPDATE_DATE,
778 LAST_UPDATED_BY,
779 CREATION_DATE,
780 CREATED_BY,
781 LAST_UPDATE_LOGIN,
785 number1,
782 char1,
783 char2,
784 char3,
786 char6,
787 char7,
788 char8,
789 char9,
790 char4,
791 char5,
792 char10)
793 select
794 g_options_query_id,
795 sysdate,
796 -1,
797 sysdate,
798 -1,
799 -1,
800 mp.compile_designator,
801 PT.ORGANIZATION_CODE
802 , PT.PARTNER_NAME
803 , 4
804 , nvl(decode(MPO.NET_WIP,1,g_yes,g_no),g_no)
805 , nvl(decode(MPO.NET_RESERVATIONS,1,g_yes,g_no),g_no)
806 , nvl(decode(MPO.NET_PURCHASING,1,g_yes,g_no),g_no)
807 , nvl(decode(MPO.PLAN_SAFETY_STOCK,1,g_yes,g_no),g_no)
808 , MPO.SIMULATION_SET
809 , MPO.BILL_OF_RESOURCES
810 , nvl(decode(MPO.INCLUDE_SALESORDER,1,g_yes,g_no),g_no)
811 FROM
812 MSC_TRADING_PARTNERS PT,
813 MSC_PLAN_ORGANIZATIONS MPO,
814 msc_plans mp
815 where MPO.SR_INSTANCE_ID = PT.SR_INSTANCE_ID
816 AND MPO.ORGANIZATION_ID = PT.SR_TP_ID
817 AND PT.partner_type =3
818 and mp.plan_id = mpo.plan_id
819 and mpo.plan_id = g_from_plan
820 and not exists ( select 1
821 from MSC_PLAN_ORGANIZATIONS MPO2
822 where MPO2.plan_id = g_to_plan
823 and MPO2.organization_id = MPO.organization_id
824 and MPO2.sr_instance_id = MPO.sr_instance_id
825 and nvl(MPO2.NET_WIP,0) = nvl(MPO.NET_WIP,0)
826 and nvl(MPO2.NET_RESERVATIONS,0) = nvl(MPO.NET_RESERVATIONS,0)
827 and nvl(MPO2.NET_PURCHASING,0) = nvl(MPO.NET_PURCHASING,0)
828 and nvl(MPO2.PLAN_SAFETY_STOCK,0) = nvl(MPO.PLAN_SAFETY_STOCK,0)
829 and nvl(MPO2.SIMULATION_SET,'0') = nvl(MPO.SIMULATION_SET,'0')
830 and nvl(MPO2.BILL_OF_RESOURCES,'0') = nvl(MPO.BILL_OF_RESOURCES,'0')
831 and nvl(MPO2.INCLUDE_SALESORDER,0) = nvl(MPO.INCLUDE_SALESORDER,0))
832 ;
833
834 insert into msc_form_query
835 (QUERY_ID,
836 LAST_UPDATE_DATE,
837 LAST_UPDATED_BY,
838 CREATION_DATE,
839 CREATED_BY,
840 LAST_UPDATE_LOGIN,
841 char1,
842 char2,
843 char3,
844 number1,
845 char6,
846 char7,
847 char8,
848 char9,
849 char4,
850 char5,
851 char10)
852 select
853 g_options_query_id,
854 sysdate,
855 -1,
856 sysdate,
857 -1,
858 -1,
859 mp.compile_designator,
860 PT.ORGANIZATION_CODE
861 , PT.PARTNER_NAME
862 , 4 -- org
863 , nvl(decode(MPO.NET_WIP,1,g_yes,g_no),g_no)
864 , nvl(decode(MPO.NET_RESERVATIONS,1,g_yes,g_no),g_no)
865 , nvl(decode(MPO.NET_PURCHASING,1,g_yes,g_no),g_no)
866 , nvl(decode(MPO.PLAN_SAFETY_STOCK,1,g_yes,g_no),g_no)
867 , MPO.SIMULATION_SET
868 , MPO.BILL_OF_RESOURCES
869 , nvl(decode(MPO.INCLUDE_SALESORDER,1,g_yes,g_no),g_no)
870 FROM
871 MSC_TRADING_PARTNERS PT,
872 MSC_PLAN_ORGANIZATIONS MPO,
873 msc_plans mp
874 where MPO.SR_INSTANCE_ID = PT.SR_INSTANCE_ID
875 AND MPO.ORGANIZATION_ID = PT.SR_TP_ID
876 AND PT.partner_type =3
877 and mpo.plan_id = g_to_plan
878 and mp.plan_id = mpo.plan_id
879 and not exists ( select 1
880 from MSC_PLAN_ORGANIZATIONS MPO2
881 where MPO2.plan_id = g_from_plan
882 and MPO2.organization_id = MPO.organization_id
883 and MPO2.sr_instance_id = MPO.sr_instance_id
884 and nvl(MPO2.NET_WIP,0) = nvl(MPO.NET_WIP,0)
885 and nvl(MPO2.NET_RESERVATIONS,0) = nvl(MPO.NET_RESERVATIONS,0)
886 and nvl(MPO2.NET_PURCHASING,0) = nvl(MPO.NET_PURCHASING,0)
887 and nvl(MPO2.PLAN_SAFETY_STOCK,0) = nvl(MPO.PLAN_SAFETY_STOCK,0)
888 and nvl(MPO2.SIMULATION_SET,'0') = nvl(MPO.SIMULATION_SET,'0')
889 and nvl(MPO2.BILL_OF_RESOURCES,'0') = nvl(MPO.BILL_OF_RESOURCES,'0')
890 and nvl(MPO2.INCLUDE_SALESORDER,0) = nvl(MPO.INCLUDE_SALESORDER,0))
891 ;
892
893 insert into msc_form_query
894 (QUERY_ID,
895 LAST_UPDATE_DATE,
896 LAST_UPDATED_BY,
897 CREATION_DATE,
898 CREATED_BY,
899 LAST_UPDATE_LOGIN,
900 char1,
901 char2,
902 char3,
903 number1,
904 char6,
905 char7,
906 char8,
907 char9,
908 char4,
909 char5,
910 char10)
911 select
912 g_options_query_id,
913 sysdate,
914 -1,
915 sysdate,
916 -1,
917 -1,
918 mp.compile_designator||'&'||mp2.compile_designator,
919 PT.ORGANIZATION_CODE
920 , PT.PARTNER_NAME
921 , 4 -- org
922 , nvl(decode(MPO.NET_WIP,1,g_yes,g_no),g_no)
926 , MPO.SIMULATION_SET
923 , nvl(decode(MPO.NET_RESERVATIONS,1,g_yes,g_no),g_no)
924 , nvl(decode(MPO.NET_PURCHASING,1,g_yes,g_no),g_no)
925 , nvl(decode(MPO.PLAN_SAFETY_STOCK,1,g_yes,g_no),g_no)
927 , MPO.BILL_OF_RESOURCES
928 , nvl(decode(MPO.INCLUDE_SALESORDER,1,g_yes,g_no),g_no)
929 FROM
930 MSC_TRADING_PARTNERS PT,
931 MSC_PLAN_ORGANIZATIONS MPO,
932 MSC_PLAN_ORGANIZATIONS MPO2,
933 msc_plans mp,
934 msc_plans mp2
935 where MPO.SR_INSTANCE_ID = PT.SR_INSTANCE_ID
936 AND MPO.ORGANIZATION_ID = PT.SR_TP_ID
937 AND PT.partner_type =3
938 and mpo.plan_id = g_to_plan
939 and mp.plan_id = mpo.plan_id
940 and mp2.plan_id = mpo2.plan_id
941 and MPO2.plan_id = g_from_plan
942 and MPO2.organization_id = MPO.organization_id
943 and MPO2.sr_instance_id = MPO.sr_instance_id
944 and nvl(MPO2.NET_WIP,0) = nvl(MPO.NET_WIP,0)
945 and nvl(MPO2.NET_RESERVATIONS,0) = nvl(MPO.NET_RESERVATIONS,0)
946 and nvl(MPO2.NET_PURCHASING,0) = nvl(MPO.NET_PURCHASING,0)
947 and nvl(MPO2.PLAN_SAFETY_STOCK,0) = nvl(MPO.PLAN_SAFETY_STOCK,0)
948 and nvl(MPO2.SIMULATION_SET,'0') = nvl(MPO.SIMULATION_SET,'0')
949 and nvl(MPO2.BILL_OF_RESOURCES,'0') = nvl(MPO.BILL_OF_RESOURCES,'0')
950 and nvl(MPO2.INCLUDE_SALESORDER,0) = nvl(MPO.INCLUDE_SALESORDER,0)
951 ;
952
953
954 End compare_orgs;
955
956 Procedure compare_schedules IS
957
958 Begin
959
960 insert into msc_form_query
961 (QUERY_ID,
962 LAST_UPDATE_DATE,
963 LAST_UPDATED_BY,
964 CREATION_DATE,
965 CREATED_BY,
966 LAST_UPDATE_LOGIN,
967 char1,
968 char2,
969 char3,
970 number1,
971 number2,
972 char5,
973 number4,
974 number5,
975 number6,
976 char4)
977 select
978 g_options_query_id,
979 sysdate,
980 -1,
981 sysdate,
982 -1,
983 -1,
984 PT.ORGANIZATION_CODE
985 , DESIG.DESIGNATOR
986 , DESIG.DESCRIPTION
987 , 5 -- schedule
988 , MPS.DESIGNATOR_TYPE
989 , nvl(decode(MPS.INTERPLANT_DEMAND_FLAG,1,g_yes,g_no),g_no)
990 , MPS.SCENARIO_SET
991 , MPS.PROBABILITY
992 , MPS.input_type
993 , mp.compile_designator
994 FROM
995 MSC_TRADING_PARTNERS PT,
996 MSC_DESIGNATORS DESIG,
997 MSC_PLAN_SCHEDULES MPS,
998 msc_plans mp
999 WHERE MPS.INPUT_SCHEDULE_ID = DESIG.DESIGNATOR_ID
1000 and mp.plan_id = mps.plan_id
1001 AND MPS.DESIGNATOR_TYPE <> 7
1002 and MPS.SR_INSTANCE_ID = PT.SR_INSTANCE_ID
1003 AND MPS.ORGANIZATION_ID = PT.SR_TP_ID
1004 AND PT.partner_type =3
1005 and mps.plan_id = g_from_plan
1006 and not exists ( select 1
1007 from MSC_PLAN_SCHEDULES MPS2
1008 where MPS2.plan_id = g_to_plan
1009 and MPS2.organization_id = MPS.organization_id
1010 and MPS2.sr_instance_id = MPS.sr_instance_id
1011 and mps2.input_type = mps.input_type
1012 and mps2.INPUT_SCHEDULE_ID = mps.INPUT_SCHEDULE_ID
1013 and nvl(mps2.INTERPLANT_DEMAND_FLAG,2) =
1014 nvl(mps.INTERPLANT_DEMAND_FLAG,2)
1015 and nvl(MPS2.SCENARIO_SET,0) =nvl(MPS.SCENARIO_SET,0)
1016 and nvl(MPS2.PROBABILITY,0) = nvl(MPS.PROBABILITY,0)
1017 )
1018 ;
1019
1020 insert into msc_form_query
1021 (QUERY_ID,
1022 LAST_UPDATE_DATE,
1023 LAST_UPDATED_BY,
1024 CREATION_DATE,
1025 CREATED_BY,
1026 LAST_UPDATE_LOGIN,
1027 char1,
1028 char2,
1029 char3,
1030 number1,
1031 number2,
1032 char5,
1033 number4,
1034 number5,
1035 number6,
1036 char4)
1037 select
1038 g_options_query_id,
1039 sysdate,
1040 -1,
1041 sysdate,
1042 -1,
1043 -1,
1044 PT.ORGANIZATION_CODE
1045 , DESIG.DESIGNATOR
1046 , DESIG.DESCRIPTION
1047 , 5 -- schedule
1048 , MPS.DESIGNATOR_TYPE
1049 , nvl(decode(MPS.INTERPLANT_DEMAND_FLAG,1,g_yes,g_no),g_no)
1050 , MPS.SCENARIO_SET
1051 , MPS.PROBABILITY
1052 , MPS.input_type
1053 , mp.compile_designator
1054 FROM
1055 MSC_TRADING_PARTNERS PT,
1056 MSC_DESIGNATORS DESIG,
1057 MSC_PLAN_SCHEDULES MPS,
1058 msc_plans mp
1059 WHERE MPS.INPUT_SCHEDULE_ID = DESIG.DESIGNATOR_ID
1060 and mp.plan_id = mps.plan_id
1061 AND MPS.DESIGNATOR_TYPE <> 7
1062 and MPS.SR_INSTANCE_ID = PT.SR_INSTANCE_ID
1063 AND MPS.ORGANIZATION_ID = PT.SR_TP_ID
1064 AND PT.partner_type =3
1065 and mps.plan_id = g_to_plan
1066 and not exists ( select 1
1067 from MSC_PLAN_SCHEDULES MPS2
1068 where MPS2.plan_id = g_from_plan
1069 and MPS2.organization_id = MPS.organization_id
1070 and MPS2.sr_instance_id = MPS.sr_instance_id
1071 and mps2.input_type = mps.input_type
1075 and nvl(MPS2.SCENARIO_SET,0) =nvl(MPS.SCENARIO_SET,0)
1072 and mps2.INPUT_SCHEDULE_ID = mps.INPUT_SCHEDULE_ID
1073 and nvl(mps2.INTERPLANT_DEMAND_FLAG,2) =
1074 nvl(mps.INTERPLANT_DEMAND_FLAG,2)
1076 and nvl(MPS2.PROBABILITY,0) = nvl(MPS.PROBABILITY,0)
1077 )
1078 ;
1079
1080 insert into msc_form_query
1081 (QUERY_ID,
1082 LAST_UPDATE_DATE,
1083 LAST_UPDATED_BY,
1084 CREATION_DATE,
1085 CREATED_BY,
1086 LAST_UPDATE_LOGIN,
1087 char1,
1088 char2,
1089 char3,
1090 number1,
1091 number2,
1092 char5,
1093 number4,
1094 number5,
1095 number6,
1096 char4)
1097 select
1098 g_options_query_id,
1099 sysdate,
1100 -1,
1101 sysdate,
1102 -1,
1103 -1,
1104 PT.ORGANIZATION_CODE
1105 , DESIG.DESIGNATOR
1106 , DESIG.DESCRIPTION
1107 , 5 -- schedule
1108 , MPS.DESIGNATOR_TYPE
1109 , nvl(decode(MPS.INTERPLANT_DEMAND_FLAG,1,g_yes,g_no),g_no)
1110 , MPS.SCENARIO_SET
1111 , MPS.PROBABILITY
1112 , MPS.input_type
1113 , mp.compile_designator||'&'||mp2.compile_designator
1114 FROM
1115 MSC_TRADING_PARTNERS PT,
1116 MSC_DESIGNATORS DESIG,
1117 MSC_PLAN_SCHEDULES MPS,
1118 MSC_PLAN_SCHEDULES MPS2,
1119 msc_plans mp,
1120 msc_plans mp2
1121 WHERE MPS.INPUT_SCHEDULE_ID = DESIG.DESIGNATOR_ID
1122 and mp.plan_id = mps.plan_id
1123 AND MPS.DESIGNATOR_TYPE <> 7
1124 and MPS.SR_INSTANCE_ID = PT.SR_INSTANCE_ID
1125 AND MPS.ORGANIZATION_ID = PT.SR_TP_ID
1126 AND PT.partner_type =3
1127 and mps.plan_id = g_to_plan
1128 and mp2.plan_id = mps2.plan_id
1129 and MPS2.plan_id = g_from_plan
1130 and MPS2.organization_id = MPS.organization_id
1131 and MPS2.sr_instance_id = MPS.sr_instance_id
1132 and mps2.input_type = mps.input_type
1133 and mps2.INPUT_SCHEDULE_ID = mps.INPUT_SCHEDULE_ID
1134 and nvl(mps2.INTERPLANT_DEMAND_FLAG,2) =
1135 nvl(mps.INTERPLANT_DEMAND_FLAG,2)
1136 and nvl(MPS2.SCENARIO_SET,0) =nvl(MPS.SCENARIO_SET,0)
1137 and nvl(MPS2.PROBABILITY,0) = nvl(MPS.PROBABILITY,0)
1138 ;
1139
1140 End compare_schedules;
1141
1142 Procedure compare_exceptions(
1143 exception_list varchar2,
1144 item_where_clause varchar2,
1145 resource_where_clause varchar2) IS
1146
1147 p_exc_type number;
1148 sql_statement varchar2(2000);
1149 where_clause varchar2(32000);
1150 i number :=1;
1151 v_len number;
1152 one_len number;
1153
1154 CURSOR cat_set_name(v_category_set_id number) IS
1155 SELECT category_set_name
1156 FROM msc_category_sets
1157 where category_set_id = v_category_set_id;
1158
1159
1160 cursor plan_type_c(v_plan_id number) is
1161 select curr_plan_type
1162 from msc_plans
1163 where plan_id = v_plan_id;
1164
1165 p_plan_status number;
1166 p_report_id number;
1167 l_plan_type number;
1168 l_def_pref_id number;
1169
1170 Begin
1171
1172 open plan_type_c(g_from_plan);
1173 fetch plan_type_c into l_plan_type;
1174 close plan_type_c;
1175
1176 l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
1177 g_cat_set:= msc_get_name.GET_preference('CATEGORY_SET_ID',l_def_pref_id, l_plan_type);
1178
1179 OPEN cat_set_name(g_cat_set);
1180 FETCH cat_set_name INTO g_cat_set_name;
1181 CLOSE cat_set_name;
1182
1183 checkPlanStatus(g_from_plan, g_to_plan,p_plan_status,p_report_id);
1184
1185 if g_need_insert_temp then
1186 select msc_form_query_s.nextval
1187 into g_excp_query_id
1188 from dual;
1189 else
1190 g_excp_query_id := p_report_id;
1191 end if;
1192
1193 -- parse the exception_list, the format is ',1,2,3,14,'
1194 v_len := length(exception_list);
1195 while v_len > 1 loop
1196 one_len := instr(exception_list,',',1,i+1)-
1197 instr(exception_list,',',1,i)-1;
1198 p_exc_type := to_number(
1199 substr(exception_list,
1200 instr(exception_list,',',1,i)+1,one_len));
1201
1202 if p_exc_type in (21,22,23,35,36,38,39,40,45,46,50,51) then
1203 where_clause := resource_where_clause;
1204 else
1205 if item_where_clause is not null then
1206 if g_long_query then
1207 where_clause := item_where_clause||' and medv.category_set_id='
1208 || g_cat_set;
1209 else
1210 where_clause := item_where_clause;
1211 end if;
1212 end if;
1213 end if;
1214 if g_need_insert_temp then
1215 filter_data(p_report_id, p_exc_type, where_clause);
1216 end if;
1217
1218 i := i+1;
1219 v_len := v_len - one_len-1;
1220
1221 END LOOP;
1222
1226 insert into msc_nec_exc_dtl_temp(
1223 if g_need_insert_temp then
1224
1225 -- insert summary rows which are grouped by exc_type
1227 query_id,
1228 status,
1229 plan_id,
1230 exception_type,
1231 exception_group,
1232 from_plan,
1233 match_id, -- to store exception count
1234 LAST_UPDATE_DATE,
1235 LAST_UPDATED_BY,
1236 CREATION_DATE,
1237 CREATED_BY,
1238 LAST_UPDATE_LOGIN)
1239 select query_id,
1240 status,
1241 plan_id,
1242 exception_type,
1243 exception_group,
1244 from_plan,
1245 count(*),
1246 trunc(sysdate),
1247 -1,
1248 trunc(sysdate),
1249 -1,
1250 -1
1251 from msc_nec_exc_dtl_temp
1252 where query_id = g_excp_query_id
1253 and exception_detail_id is not null
1254 group by query_id,
1255 status,
1256 plan_id,
1257 exception_type,
1258 exception_group,
1259 from_plan,
1260 trunc(sysdate),
1261 -1,
1262 trunc(sysdate),
1263 -1,
1264 -1 ;
1265
1266 -- insert summary rows which are grouped by exc_group
1267 insert into msc_nec_exc_dtl_temp(
1268 query_id,
1269 status,
1270 plan_id,
1271 exception_group,
1272 from_plan,
1273 match_id, -- to store exception count
1274 LAST_UPDATE_DATE,
1275 LAST_UPDATED_BY,
1276 CREATION_DATE,
1277 CREATED_BY,
1278 LAST_UPDATE_LOGIN)
1279 select query_id,
1280 status,
1281 plan_id,
1282 exception_group,
1283 from_plan,
1284 sum(match_id),
1285 trunc(sysdate),
1286 -1,
1287 trunc(sysdate),
1288 -1,
1289 -1
1290 from msc_nec_exc_dtl_temp
1291 where query_id = g_excp_query_id
1292 and exception_detail_id is null
1293 group by query_id,
1294 status,
1295 plan_id,
1296 exception_group,
1297 from_plan,
1298 trunc(sysdate),
1299 -1,
1300 trunc(sysdate),
1301 -1,
1302 -1 ;
1303 end if;
1304
1305 End compare_exceptions;
1306
1307 Procedure populate_all_exceptions(p_plan_id number,p_report_id number) IS
1308 p_mask varchar2(20) :='MM/DD/RR HH24:MI';
1309 Begin
1310
1311 -- over commit, rep var, no act, neg on hand
1312 -- item with exc/shortage, below safety, supplier cap.
1313 -- sourcing split violation
1314 insert into msc_nec_exc_dtl_compare(
1315 report_id,
1316 status,
1317 exception_detail_id,
1318 plan_id,
1319 exception_type,
1320 exception_group,
1321 from_plan,
1322 id_key,
1323 compare_key,
1324 char1,
1325 char2,
1326 LAST_UPDATE_DATE,
1327 LAST_UPDATED_BY,
1328 CREATION_DATE,
1329 CREATED_BY,
1330 LAST_UPDATE_LOGIN)
1331 select p_report_id,
1332 0,
1333 med.exception_detail_id,
1334 med.plan_id,
1335 med.exception_type,
1336 decode(med.exception_type,
1337 2, 3, 3, 3, 20, 3,
1338 28, 6,
1339 17, 8, 18, 8,1) ,
1340 decode(med.plan_id,g_from_plan,1,2),
1341 med.organization_id||':'||med.sr_instance_id||':'||
1342 med.inventory_item_id||':'||med.supplier_id||':'||
1343 med.supplier_site_id||':'||
1344 decode(med.exception_type,
1345 17, med.number1||':'||med.number2, -- project_id/task_id
1346 18, med.number1||':'||med.number2,
1350 decode(med.exception_type,48,med.number1), -- actual %
1347 48, med.number2), -- supplier or source org
1348 to_char(med.date1, p_mask)||':'||to_char(med.date2, p_mask)||':'||
1349 med.quantity ||':'||
1351 msi.item_name,
1352 msi.planner_code,
1353 trunc(sysdate),
1354 -1,
1355 trunc(sysdate),
1356 -1,
1357 -1
1358 from msc_exception_details med,
1359 msc_system_items msi
1360 where med.plan_id = p_plan_id
1361 and med.exception_type in (1,2,3,4,5,11,17,18,20,28,29,30,48)
1362 and msi.plan_id = med.plan_id
1363 and msi.organization_id = med.organization_id
1364 and msi.sr_instance_id = med.sr_instance_id
1365 and msi.inventory_item_id = med.inventory_item_id
1366 ;
1367
1368 -- reschedule in/out, cancel, past due, compress, expired lot
1369 -- schedule to next inventory point
1370 insert into msc_nec_exc_dtl_compare(
1371 report_id,
1372 status,
1373 exception_detail_id,
1374 plan_id,
1375 exception_type,
1376 exception_group,
1377 from_plan,
1378 id_key,
1379 compare_key,
1380 char1,
1381 char2,
1382 LAST_UPDATE_DATE,
1383 LAST_UPDATED_BY,
1384 CREATION_DATE,
1385 CREATED_BY,
1386 LAST_UPDATE_LOGIN)
1387 select p_report_id,
1388 0,
1389 med.exception_detail_id,
1390 med.plan_id,
1391 med.exception_type,
1392 decode(med.exception_type, 12, 1, 4),
1393 decode(med.plan_id,g_from_plan,1,2),
1394 med.organization_id||':'||med.sr_instance_id||':'||
1395 med.inventory_item_id||':'||
1396 decode(ms.order_type,
1397 5,ms.new_schedule_date ||':'||ms.new_order_quantity ||':'||
1398 ms.supplier_id||':'||ms.supplier_site_id||':'||
1399 ms.source_organization_id ||':'||ms.source_sr_instance_id,
1400 ms.order_number||':'||ms.purch_line_num),
1401 decode(med.exception_type,
1402 9, to_char(ms.schedule_compress_days),
1403 12, ms.lot_number,
1404 47, to_char(med.number2), -- planned inventory point
1405 to_char(ms.reschedule_days)),
1406 msi.item_name,
1407 msi.planner_code,
1408 trunc(sysdate),
1409 -1,
1410 trunc(sysdate),
1411 -1,
1412 -1
1413 from msc_exception_details med,
1414 msc_system_items msi,
1415 msc_supplies ms
1416 where med.plan_id = p_plan_id
1417 and med.exception_type in (6,7,8,9,10,12,47)
1418 and ms.plan_id=med.plan_id
1419 and ms.transaction_id=med.number1
1420 and ms.sr_instance_id=med.sr_instance_id
1421 and msi.plan_id = med.plan_id
1422 and msi.organization_id = med.organization_id
1423 and msi.sr_instance_id = med.sr_instance_id
1424 and msi.inventory_item_id = med.inventory_item_id;
1425
1426 -- alternate
1427 insert into msc_nec_exc_dtl_compare(
1428 report_id,
1429 status,
1430 exception_detail_id,
1431 plan_id,
1432 exception_type,
1433 exception_group,
1434 from_plan,
1435 id_key,
1436 compare_key,
1437 char1,
1438 char2,
1439 LAST_UPDATE_DATE,
1440 LAST_UPDATED_BY,
1441 CREATION_DATE,
1442 CREATED_BY,
1443 LAST_UPDATE_LOGIN)
1444 select p_report_id,
1445 0,
1446 med.exception_detail_id,
1447 med.plan_id,
1448 med.exception_type,
1449 2,
1450 decode(med.plan_id,g_from_plan,1,2),
1451 med.organization_id||':'||med.sr_instance_id||':'||
1452 med.inventory_item_id||':'||
1453 decode(ms.order_type,
1454 5,ms.new_schedule_date ||':'||ms.new_order_quantity ||':'||
1455 ms.supplier_id||':'||ms.supplier_site_id||':'||
1456 ms.source_organization_id ||':'||ms.source_sr_instance_id,
1457 ms.order_number||':'||ms.purch_line_num),
1458 decode(med.exception_type,
1459 34, med.department_id ||':'||med.resource_id,
1460 43, med.number2 ||':'|| med.number3 ,
1461 44, med.supplier_id,
1462 med.number2),
1463 msi.item_name,
1464 msi.planner_code,
1465 trunc(sysdate),
1466 -1,
1467 trunc(sysdate),
1468 -1,
1469 -1
1470 from msc_exception_details med,
1471 msc_system_items msi,
1472 msc_supplies ms
1473 where med.plan_id = p_plan_id
1474 and med.exception_type in (31,32,33,34,43,44)
1475 and ms.plan_id=med.plan_id
1476 and ms.transaction_id=med.number1
1477 and ms.sr_instance_id=med.sr_instance_id
1478 and msi.plan_id = ms.plan_id
1479 and msi.organization_id = ms.organization_id
1480 and msi.sr_instance_id = ms.sr_instance_id
1481 and msi.inventory_item_id = ms.inventory_item_id;
1482
1483 -- resource/material constraint
1487 exception_detail_id,
1484 insert into msc_nec_exc_dtl_compare(
1485 report_id,
1486 status,
1488 plan_id,
1489 exception_type,
1490 exception_group,
1491 from_plan,
1492 id_key,
1493 compare_key,
1494 char1,
1495 char2,
1496 LAST_UPDATE_DATE,
1497 LAST_UPDATED_BY,
1498 CREATION_DATE,
1499 CREATED_BY,
1500 LAST_UPDATE_LOGIN)
1501 select p_report_id,
1502 0,
1503 med.exception_detail_id,
1504 med.plan_id,
1505 med.exception_type,
1506 decode(med.exception_type,36, 6, 37, 6, 40,7, 61,7,
1507 62, 4,63, 4, 64, 4, 65, 4, 66, 4, 71, 4, 11),
1508 decode(med.plan_id,g_from_plan,1,2),
1509 med.organization_id||':'||med.sr_instance_id||':'||
1510 med.inventory_item_id||':'||med.department_id ||':'||
1511 med.resource_id ||':'||
1512 decode(ms.order_type,
1513 5,ms.new_schedule_date ||':'||ms.new_order_quantity ||':'||
1514 ms.supplier_id||':'||ms.supplier_site_id||':'||
1515 ms.source_organization_id ||':'||ms.source_sr_instance_id||':'||
1516 ms.ship_method,
1517 ms.order_number||':'||ms.purch_line_num)||':'||
1518 decode(med.exception_type, 36, med.number2||';'||med.number3,
1519 53, med.number2||';'||med.number3,
1520 58, med.number2||';'||med.number3,
1521 60, med.number2||';'||med.number3,
1522 63, med.number3||';'||med.number4,
1523 65, med.number3||';'||med.number4),
1524 to_char(med.date1, p_mask)||':'||
1525 to_char(med.date2, p_mask)||':'||med.quantity,
1526 msi.item_name,
1527 msi.planner_code,
1528 trunc(sysdate),
1529 -1,
1530 trunc(sysdate),
1531 -1,
1532 -1
1533 from msc_exception_details med,
1534 msc_system_items msi,
1535 msc_supplies ms
1536 where med.plan_id = p_plan_id
1537 and med.exception_type in (36,37,40,53,54,55,56,57,58,59,60,61,62,63,64,65,66,71,72)
1538 and ms.plan_id=med.plan_id
1539 and ms.transaction_id=med.number1
1540 and ms.sr_instance_id=med.sr_instance_id
1541 and msi.plan_id = ms.plan_id
1542 and msi.organization_id = ms.organization_id
1543 and msi.sr_instance_id = ms.sr_instance_id
1544 and msi.inventory_item_id = ms.inventory_item_id;
1545
1546 -- late/early, past due sales order/forecast
1547 -- SO/FC at risk, demand qty not satisfied, SO overcommit
1548 -- SO changes, alt ship method used
1549 insert into msc_nec_exc_dtl_compare(
1550 report_id,
1551 status,
1552 exception_detail_id,
1553 plan_id,
1554 exception_type,
1555 exception_group,
1556 from_plan,
1557 id_key,
1558 compare_key,
1559 char1,
1560 char2,
1561 LAST_UPDATE_DATE,
1562 LAST_UPDATED_BY,
1563 CREATION_DATE,
1564 CREATED_BY,
1565 LAST_UPDATE_LOGIN)
1566 select p_report_id,
1567 0,
1568 med.exception_detail_id,
1569 med.plan_id,
1570 med.exception_type,
1571 decode(med.exception_type, 67, 11, 70, 4, 71, 4, 5),
1572 decode(med.plan_id,g_from_plan,1,2),
1573 med.organization_id||':'||med.sr_instance_id||':'||
1574 med.inventory_item_id||':'||md.using_assembly_demand_date||':'||
1575 md.using_requirement_quantity ||':'|| md.customer_id ||':'||
1576 md.customer_site_id ||':'||md.demand_class||':'||
1577 md.order_number||':'||md.sales_order_line_id,
1578 decode(med.exception_type, 52, null,
1579 67,med.quantity,
1580 to_char(md.dmd_satisfied_date, p_mask)),
1581 msi.item_name,
1582 msi.planner_code,
1583 trunc(sysdate),
1584 -1,
1585 trunc(sysdate),
1586 -1,
1587 -1
1588 from msc_exception_details med,
1589 msc_system_items msi,
1590 msc_demands md
1591 where med.plan_id = p_plan_id
1592 and med.exception_type in (13,14,24,25,26,27,52,67,68,70,71)
1593 and md.plan_id=med.plan_id
1594 and md.demand_id=med.number1
1595 and md.sr_instance_id=med.sr_instance_id
1596 and msi.plan_id = md.plan_id
1597 and msi.organization_id = md.organization_id
1598 and msi.sr_instance_id = md.sr_instance_id
1599 and msi.inventory_item_id = md.inventory_item_id;
1600
1601 --- demand using item substitute
1602 insert into msc_nec_exc_dtl_compare(
1603 report_id,
1604 status,
1605 exception_detail_id,
1606 plan_id,
1607 exception_type,
1608 exception_group,
1609 from_plan,
1610 id_key,
1611 compare_key,
1612 char1,
1613 char2,
1614 LAST_UPDATE_DATE,
1615 LAST_UPDATED_BY,
1616 CREATION_DATE,
1617 CREATED_BY,
1621 med.exception_detail_id,
1618 LAST_UPDATE_LOGIN)
1619 select p_report_id,
1620 0,
1622 med.plan_id,
1623 med.exception_type,
1624 2,
1625 decode(med.plan_id,g_from_plan,1,2),
1626 med.organization_id||':'||med.sr_instance_id||':'||
1627 med.inventory_item_id||':'||md.using_assembly_demand_date||':'||
1628 md.using_requirement_quantity ||':'|| md.customer_id ||':'||
1629 md.customer_site_id ||':'||md.demand_class||':'||
1630 md.order_number||':'||md.sales_order_line_id,
1631 med.number1||':'|| -- substitute item
1632 med.number2||':'|| -- substitute org
1633 med.quantity, -- substitute qty
1634 msi.item_name,
1635 msi.planner_code,
1636 trunc(sysdate),
1637 -1,
1638 trunc(sysdate),
1639 -1,
1640 -1
1641 from msc_exception_details med,
1642 msc_system_items msi,
1643 msc_demands md
1644 where med.plan_id = p_plan_id
1645 and med.exception_type =49
1646 and md.plan_id=med.plan_id
1647 and md.demand_id=med.supplier_id
1648 and md.sr_instance_id=med.sr_instance_id
1649 and msi.plan_id = md.plan_id
1650 and msi.organization_id = md.organization_id
1651 and msi.sr_instance_id = md.sr_instance_id
1652 and msi.inventory_item_id = md.inventory_item_id;
1653
1654 -- late supply pegged to so/forecast
1655 -- order at risk due to res/mat shortage/demand affected by res/mat constraint
1656 -- Late Replenishment for DRP/MPS Demands
1657 insert into msc_nec_exc_dtl_compare(
1658 report_id,
1659 status,
1660 exception_detail_id,
1661 plan_id,
1662 exception_type,
1663 exception_group,
1664 from_plan,
1665 id_key,
1666 compare_key,
1667 char1,
1668 char2,
1669 LAST_UPDATE_DATE,
1670 LAST_UPDATED_BY,
1671 CREATION_DATE,
1672 CREATED_BY,
1673 LAST_UPDATE_LOGIN)
1674 select p_report_id,
1675 0,
1676 med.exception_detail_id,
1677 med.plan_id,
1678 med.exception_type,
1679 5,
1680 decode(med.plan_id,g_from_plan,1,2),
1681 med.organization_id||':'||med.sr_instance_id||':'||
1682 med.inventory_item_id||':'||md.using_assembly_demand_date||':'||
1683 md.using_requirement_quantity ||':'|| md.customer_id ||':'||
1684 md.customer_site_id ||':'||md.demand_class||':'||
1685 md.order_number||':'||md.sales_order_line_id,
1686 to_char(med.date1, p_mask)||':'||
1687 to_char(med.date2, p_mask)||':'||med.quantity,
1688 msi.item_name,
1689 msi.planner_code,
1690 trunc(sysdate),
1691 -1,
1692 trunc(sysdate),
1693 -1,
1694 -1
1695 from msc_exception_details med,
1696 msc_system_items msi,
1697 msc_demands md,
1698 msc_full_pegging mfp
1699 where med.plan_id = p_plan_id
1700 and med.exception_type in (15,16,23,35,41,42,69)
1701 and md.plan_id=med.plan_id
1702 and mfp.pegging_id=med.number2
1703 and md.sr_instance_id=med.sr_instance_id
1704 and md.plan_id=mfp.plan_id
1705 and md.demand_id=mfp.demand_id
1706 and md.sr_instance_id=mfp.sr_instance_id
1707 and msi.plan_id = md.plan_id
1708 and msi.organization_id = md.organization_id
1709 and msi.sr_instance_id = md.sr_instance_id
1710 and msi.inventory_item_id = md.inventory_item_id;
1711
1712 -- cross project
1713 insert into msc_nec_exc_dtl_compare(
1714 report_id,
1715 status,
1716 exception_detail_id,
1717 plan_id,
1718 exception_type,
1719 exception_group,
1720 from_plan,
1721 id_key,
1722 compare_key,
1723 char1,
1724 char2,
1725 LAST_UPDATE_DATE,
1726 LAST_UPDATED_BY,
1727 CREATION_DATE,
1728 CREATED_BY,
1729 LAST_UPDATE_LOGIN)
1730 select p_report_id,
1731 0,
1732 med.exception_detail_id,
1733 med.plan_id,
1734 med.exception_type,
1735 8,
1736 decode(med.plan_id,g_from_plan,1,2),
1737 med.organization_id||':'||med.sr_instance_id||':'||
1738 med.inventory_item_id||':'||
1739 ms.project_id||':'||ms.task_id||':'||
1740 md.project_id||':'||md.task_id,
1741 to_char(med.date1, p_mask)||':'||
1742 to_char(med.date2, p_mask)||':'||med.quantity,
1743 msi.item_name,
1744 msi.planner_code,
1745 trunc(sysdate),
1746 -1,
1747 trunc(sysdate),
1748 -1,
1749 -1
1750 from msc_exception_details med,
1751 msc_system_items msi,
1752 msc_demands md,
1753 msc_supplies ms,
1754 msc_full_pegging mfp
1755 where med.plan_id = p_plan_id
1756 and med.exception_type = 19
1757 and md.plan_id=med.plan_id
1758 and mfp.pegging_id=med.number2
1762 and md.sr_instance_id=mfp.sr_instance_id
1759 and md.sr_instance_id=med.sr_instance_id
1760 and md.plan_id=mfp.plan_id
1761 and md.demand_id=mfp.demand_id
1763 and ms.plan_id=med.plan_id
1764 and ms.transaction_id=med.number1
1765 and ms.sr_instance_id=med.sr_instance_id
1766 and msi.plan_id = ms.plan_id
1767 and msi.organization_id = ms.organization_id
1768 and msi.sr_instance_id = ms.sr_instance_id
1769 and msi.inventory_item_id = ms.inventory_item_id;
1770
1771 -- (trans) res over/under, max/min batch
1772 insert into msc_nec_exc_dtl_compare(
1773 report_id,
1774 status,
1775 exception_detail_id,
1776 plan_id,
1777 exception_type,
1778 exception_group,
1779 from_plan,
1780 id_key,
1781 compare_key,
1782 LAST_UPDATE_DATE,
1783 LAST_UPDATED_BY,
1784 CREATION_DATE,
1785 CREATED_BY,
1786 LAST_UPDATE_LOGIN)
1787 select p_report_id,
1788 0,
1789 med.exception_detail_id,
1790 med.plan_id,
1791 med.exception_type,
1792 decode(med.exception_type, 38, 7, 39, 7, 50, 7, 51, 7, 6),
1793 decode(med.plan_id,g_from_plan,1,2),
1794 med.organization_id||':'||med.sr_instance_id||':'||
1795 med.department_id||':'||med.resource_id,
1796 to_char(med.date1, p_mask)||':'||to_char(med.date2, p_mask)||':'||
1797 med.quantity,
1798 trunc(sysdate),
1799 -1,
1800 trunc(sysdate),
1801 -1,
1802 -1
1803 from msc_exception_details med
1804 where med.plan_id = p_plan_id
1805 and med.exception_type in (21,22,38,39,45,46,50,51);
1806
1807 END populate_all_exceptions;
1808
1809 Procedure checkPlanStatus(p_from_plan in number,
1810 p_to_plan in number,
1811 p_status out nocopy number,
1812 p_report_id out nocopy number) is
1813
1814 v_dummy number;
1815 v_need_recompare boolean;
1816 v_start date;
1817 v_end date;
1818 p_excp_id number;
1819
1820 cursor compare_exist is
1821 select compare_completion_date,
1822 compare_start_date,
1823 report_id
1824 from msc_nec_compare_plans
1825 where ((from_plan = p_from_plan and to_plan = p_to_plan) or
1826 (from_plan = p_to_plan and to_plan = p_from_plan));
1827
1828 CURSOR exc_c(p_plan_id number) is
1829 select exception_detail_id
1830 from MSC_NEC_EXC_DTL_COMPARE
1831 where plan_id = p_plan_id
1832 and report_id = p_report_id
1833 and rownum =1
1834 and exception_detail_id is not null;
1835
1836 CURSOR need_recompare_c(p_plan_id number) is
1837 select 1
1838 from msc_exception_details med
1839 where med.plan_id = p_plan_id
1840 and med.exception_detail_id = p_excp_id;
1841
1842 begin
1843
1844
1845 OPEN compare_exist;
1846 FETCH compare_exist INTO v_start, v_end, p_report_id;
1847 CLOSE compare_exist;
1848
1849 IF p_report_id is null then
1850 p_status := NOT_COMPARED;
1851 elsIF v_start is null then
1852 p_status := NEED_RECOMPARE;
1853 elsif v_start is not null and v_end is null then
1854 p_status := IN_PROGRESS;
1855 else
1856
1857 OPEN exc_c(p_from_plan);
1858 FETCH exc_c INTO p_excp_id;
1859 CLOSE exc_c;
1860
1861 if p_excp_id is null then
1862 p_status := NEED_RECOMPARE;
1863 return;
1864 else
1865
1866 OPEN need_recompare_c(p_from_plan);
1867 FETCH need_recompare_c INTO v_dummy;
1868 CLOSE need_recompare_c;
1869
1870 if v_dummy is null then
1871 p_status := NEED_RECOMPARE;
1872 return;
1873 end if;
1874 end if;
1875
1876 p_excp_id :=null;
1877 v_dummy := null;
1878
1879 OPEN exc_c(p_to_plan);
1880 FETCH exc_c INTO p_excp_id;
1881 CLOSE exc_c;
1882
1883 if p_excp_id is null then
1884 p_status := NEED_RECOMPARE;
1885 return;
1886 else
1887
1888 OPEN need_recompare_c(p_to_plan);
1889 FETCH need_recompare_c INTO v_dummy;
1890 CLOSE need_recompare_c;
1891
1892 if v_dummy is null then
1893 p_status := NEED_RECOMPARE;
1894 return;
1895 end if;
1896 end if;
1897
1898 p_status := AVAILABLE;
1899
1900 end if;
1901
1902 end checkPlanStatus;
1903
1904 Procedure compare_all_exceptions(errbuf OUT NOCOPY VARCHAR2,
1905 retcode OUT NOCOPY NUMBER,
1906 p_from_plan IN NUMBER,
1907 p_to_plan IN NUMBER) is
1908 p_report_id number;
1909 p_plan_status number;
1910 p_plan_id number;
1911 begin
1912 g_from_plan := p_from_plan;
1916 select msc_nec_compare_plans_s.nextval
1913 g_to_plan := p_to_plan;
1914 checkPlanStatus(p_from_plan, p_to_plan,p_plan_status,p_report_id);
1915 if p_plan_status = NOT_COMPARED then
1917 into p_report_id
1918 from dual;
1919
1920 insert into msc_nec_compare_plans
1921 (report_id,
1922 from_plan,
1923 to_plan,
1924 compare_start_date,
1925 LAST_UPDATE_DATE,
1926 LAST_UPDATED_BY,
1927 CREATION_DATE,
1928 CREATED_BY,
1929 LAST_UPDATE_LOGIN)
1930 values(p_report_id,
1931 p_from_plan,
1932 p_to_plan,
1933 sysdate,
1934 sysdate,
1935 -1,
1936 sysdate,
1937 -1,
1938 -1);
1939 commit;
1940 elsif p_plan_status = IN_PROGRESS then
1941 -- some one else is running compare plans now
1942 FND_FILE.PUT_LINE(FND_FILE.LOG,'Someone else is running the plan comparsion reports for the same plans.');
1943 return;
1944 elsif p_plan_status = AVAILABLE then
1945 FND_FILE.PUT_LINE(FND_FILE.LOG,
1946 'No need to recompare plans.');
1947 return;
1948 elsif p_plan_status = NEED_RECOMPARE then
1949 update msc_nec_compare_plans
1950 set compare_completion_date = to_date(null),
1951 compare_start_date = sysdate
1952 where report_id = p_report_id;
1953 commit;
1954 end if;
1955
1956 delete from msc_nec_exc_dtl_compare
1957 where report_id = p_report_id;
1958
1959 populate_all_exceptions(p_from_plan,p_report_id);
1960 populate_all_exceptions(p_to_plan,p_report_id);
1961
1962 compare_each_exception(p_report_id);
1963
1964 -- insert summary rows which are grouped by exc_type
1965 insert into msc_nec_exc_dtl_compare(
1966 report_id,
1967 status,
1968 plan_id,
1969 exception_type,
1970 exception_group,
1971 from_plan,
1972 match_id, -- to store exception count
1973 LAST_UPDATE_DATE,
1974 LAST_UPDATED_BY,
1975 CREATION_DATE,
1976 CREATED_BY,
1977 LAST_UPDATE_LOGIN)
1978 select report_id,
1979 status,
1980 plan_id,
1981 exception_type,
1982 exception_group,
1983 from_plan,
1984 count(*),
1985 trunc(sysdate),
1986 -1,
1987 trunc(sysdate),
1988 -1,
1989 -1
1990 from msc_nec_exc_dtl_compare
1991 where report_id = p_report_id
1992 and exception_detail_id is not null
1993 group by report_id,
1994 status,
1995 plan_id,
1996 exception_type,
1997 exception_group,
1998 from_plan,
1999 trunc(sysdate),
2000 -1,
2001 trunc(sysdate),
2002 -1,
2003 -1 ;
2004
2005 -- insert summary rows which are grouped by exc_group
2006 insert into msc_nec_exc_dtl_compare(
2007 report_id,
2008 status,
2009 plan_id,
2010 exception_group,
2011 from_plan,
2012 match_id, -- to store exception count
2013 LAST_UPDATE_DATE,
2014 LAST_UPDATED_BY,
2015 CREATION_DATE,
2016 CREATED_BY,
2017 LAST_UPDATE_LOGIN)
2018 select report_id,
2019 status,
2020 plan_id,
2021 exception_group,
2022 from_plan,
2023 sum(match_id),
2024 trunc(sysdate),
2025 -1,
2026 trunc(sysdate),
2027 -1,
2028 -1
2029 from msc_nec_exc_dtl_compare
2030 where report_id = p_report_id
2031 and exception_detail_id is null
2032 group by report_id,
2033 status,
2034 plan_id,
2035 exception_group,
2039 trunc(sysdate),
2036 from_plan,
2037 trunc(sysdate),
2038 -1,
2040 -1,
2041 -1 ;
2042
2043 update msc_nec_compare_plans
2044 set compare_completion_date = sysdate
2045 where report_id = p_report_id;
2046
2047 commit;
2048
2049 fnd_stats.gather_table_stats(ownname=>'MSC',
2050 tabname=>'MSC_NEC_EXC_DTL_COMPARE');
2051
2052 END compare_all_exceptions;
2053
2054 Procedure compare_each_exception(p_report_id number) IS
2055 a_excp_id number;
2056 a_exc_type number;
2057 a_id varchar2(500);
2058 a_compare varchar2(500);
2059 b_excp_id number;
2060
2061 p_from_plan number := 1;
2062 p_to_plan number :=2;
2063 p_from_plan_id number := g_from_plan;
2064 p_to_plan_id number :=g_to_plan;
2065
2066 CURSOR from_plan_c IS
2067 SELECT
2068 exception_detail_id,
2069 exception_type,
2070 id_key,
2071 compare_key
2072 FROM msc_nec_exc_dtl_compare
2073 WHERE from_plan = p_from_plan
2074 AND report_id = p_report_id
2075 AND status =0;
2076
2077 CURSOR same_exc_c IS
2078 SELECT
2079 exception_detail_id
2080 FROM msc_nec_exc_dtl_compare
2081 WHERE from_plan = p_to_plan
2082 AND report_id = p_report_id
2083 AND status =0
2084 and exception_type = a_exc_type
2085 and id_key = a_id
2086 and compare_key = a_compare;
2087
2088 CURSOR change_exc_c IS
2089 SELECT
2090 exception_detail_id
2091 FROM msc_nec_exc_dtl_compare
2092 WHERE from_plan = p_to_plan
2093 AND report_id = p_report_id
2094 AND status =0
2095 and exception_type = a_exc_type
2096 and id_key = a_id;
2097
2098 from_plan_count number;
2099 to_plan_count number;
2100 Begin
2101
2102 select sum(decode(from_plan,1,1,0)), sum(decode(from_plan,2,1,0))
2103 into from_plan_count, to_plan_count
2104 from msc_nec_exc_dtl_compare
2105 where report_id = p_report_id
2106 and status =0;
2107
2108 if from_plan_count > to_plan_count then
2109 p_from_plan := 2;
2110 p_to_plan :=1;
2111 p_from_plan_id := g_to_plan;
2112 p_to_plan_id :=g_from_plan;
2113 end if;
2114
2115 OPEN from_plan_c;
2116 LOOP
2117 FETCH from_plan_c INTO
2118 a_excp_id,
2119 a_exc_type,
2120 a_id,
2121 a_compare;
2122 EXIT WHEN from_plan_c%NOTFOUND;
2123
2124 b_excp_id := null;
2125 OPEN same_exc_c;
2126 FETCH same_exc_c INTO
2127 b_excp_id;
2128 CLOSE same_exc_c;
2129
2130 if b_excp_id is not null then -- found the same excp
2131 update msc_nec_exc_dtl_compare
2132 set status = 1,
2133 match_id = b_excp_id
2134 where plan_id = p_from_plan_id
2135 and exception_detail_id = a_excp_id
2136 and report_id = p_report_id;
2137
2138 update msc_nec_exc_dtl_compare
2139 set status = 1,
2140 match_id = a_excp_id
2141 where plan_id = p_to_plan_id
2142 and exception_detail_id = b_excp_id
2143 and report_id = p_report_id;
2144 end if;
2145 end loop;
2146 CLOSE from_plan_c;
2147
2148 OPEN from_plan_c;
2149 LOOP
2150 FETCH from_plan_c INTO
2151 a_excp_id,
2152 a_exc_type,
2153 a_id,
2154 a_compare;
2155 EXIT WHEN from_plan_c%NOTFOUND;
2156 b_excp_id := null;
2157 OPEN change_exc_c;
2158 FETCH change_exc_c INTO
2159 b_excp_id;
2160 CLOSE change_exc_c;
2161 if b_excp_id is not null then -- found the change excp
2162 update msc_nec_exc_dtl_compare
2163 set status = 2,
2164 match_id = b_excp_id
2165 where plan_id = p_from_plan_id
2166 and exception_detail_id = a_excp_id
2167 and report_id = p_report_id;
2168
2169 update msc_nec_exc_dtl_compare
2170 set status = 2,
2171 match_id = a_excp_id
2172 where plan_id = p_to_plan_id
2173 and exception_detail_id = b_excp_id
2174 and report_id = p_report_id;
2175 end if;
2176 end loop;
2177
2178 CLOSE from_plan_c;
2179
2180 End compare_each_exception;
2181
2182 Procedure filter_data(p_report_id number,
2183 p_excp_type number,
2184 where_clause varchar2) is
2185 sql_statement varchar2(32000);
2186 begin
2187
2188 sql_statement :=
2189 ' insert into msc_nec_exc_dtl_temp( '||
2190 ' query_id, '||
2191 ' status, '||
2192 ' exception_detail_id,' ||
2193 ' plan_id,' ||
2194 ' exception_group,'||
2195 ' exception_type,'||
2196 ' from_plan,'||
2197 ' match_id,'||
2198 ' char1, '||
2199 ' char2,' ||
2200 ' LAST_UPDATE_DATE,'||
2201 ' LAST_UPDATED_BY, '||
2202 ' CREATION_DATE, '||
2203 ' CREATED_BY, '||
2204 ' LAST_UPDATE_LOGIN) '||
2205 ' select :query_id, '||
2206 ' med.status,'||
2207 ' med.exception_detail_id, '||
2208 ' med.plan_id, '||
2209 ' med.exception_group,'||
2210 ' med.exception_type, '||
2211 ' med.from_plan, '||
2212 ' med.match_id,'||
2213 ' med.char1, '||
2214 ' med.char2, '||
2215 ' trunc(sysdate), '||
2216 ' -1, '||
2217 ' trunc(sysdate), '||
2218 ' -1, '||
2219 ' -1 ';
2220 if g_long_query or
2221 (where_clause is not null and p_excp_type in
2222 (21,22,23,35,36,38,39,40,45,46,50,51)) then
2223 sql_statement := sql_statement ||
2224 ' from msc_nec_exc_dtl_compare med, '||
2225 ' msc_exception_details_v medv '||
2226 ' where med.report_id = :p_report_id '||
2227 ' and med.exception_type = :p_excp '||
2228 ' and med.exception_detail_id is not null '||
2229 ' and med.plan_id = medv.plan_id ' ||
2230 ' and med.exception_detail_id = '||
2231 ' medv.exception_id ' ||
2232 where_clause;
2233 else
2234 sql_statement := sql_statement ||
2235 ' from msc_nec_exc_dtl_compare med '||
2236 ' where med.report_id = :p_report_id '||
2237 ' and med.exception_type = :p_excp '||
2238 ' and med.exception_detail_id is not null '||
2239 where_clause;
2240 end if;
2241 EXECUTE IMMEDIATE sql_statement USING g_excp_query_id,
2242 p_report_id,
2243 p_excp_type;
2244
2245 end filter_data;
2246
2247 Procedure compare_plan_need_refresh(p_plan_id number) is
2248 v_report_id number_arr;
2249 begin
2250 select report_id
2251 bulk collect into v_report_id
2252 from msc_nec_compare_plans
2253 where from_plan = p_plan_id or
2254 to_plan = p_plan_id
2255 for update of compare_completion_date nowait;
2256
2257 forall a in 1..v_report_id.count
2258 update msc_nec_compare_plans
2259 set compare_completion_date = to_date(null),
2260 compare_start_date = to_date(null)
2261 where report_id = v_report_id(a);
2262
2263 commit;
2264 exception when no_data_found then
2265 null;
2266 when app_exception.record_lock_exception then
2267 MSC_UTIL.msc_debug('can not lock msc_nec_compare_plans table for update');
2268 END compare_plan_need_refresh;
2269
2270 Function category_name(p_org_id number, p_instance_id number,
2271 p_item_id number,
2272 p_plan_id number) return varchar2 is
2273
2274 v_cat_name varchar2(250);
2275 l_plan_type number;
2276 l_def_pref_id number;
2277
2278 cursor plan_type_c(v_plan_id number) is
2279 select curr_plan_type
2280 from msc_plans
2281 where plan_id = v_plan_id;
2282
2283 CURSOR cat_name_cur(v_category_set_id number) IS
2284 SELECT category_set_name
2285 FROM msc_category_sets
2286 where category_set_id = v_category_set_id;
2287 begin
2288
2289 if p_item_id is null then
2290 return null;
2291 end if;
2292
2293 open plan_type_c(p_plan_id);
2294 fetch plan_type_c into l_plan_type;
2295 close plan_type_c;
2296
2297 l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
2298 g_cat_set:= msc_get_name.GET_preference('CATEGORY_SET_ID',l_def_pref_id, l_plan_type);
2299
2300 OPEN cat_name_cur(g_cat_set);
2301 FETCH cat_name_cur INTO v_cat_name;
2302 CLOSE cat_name_cur;
2303
2304 return v_cat_name;
2305
2306 end category_name;
2307
2308 Procedure purge_plan(errbuf OUT NOCOPY VARCHAR2,
2309 retcode OUT NOCOPY NUMBER,
2310 p_plan_id IN NUMBER) is
2311
2312 cursor compare_c is
2313 select report_id
2314 from msc_nec_compare_plans
2315 where from_plan = p_plan_id or
2316 to_plan = p_plan_id;
2317 p_report_id number;
2318 begin
2319 retcode :=0;
2320
2321 open compare_c;
2322 loop
2323 fetch compare_c INTO p_report_id;
2324 exit when compare_c%NOTFOUND;
2325
2326 delete msc_nec_compare_plans
2327 where report_id = p_report_id;
2328
2329 delete msc_nec_exc_dtl_compare
2330 where report_id = p_report_id;
2331 end loop;
2332
2333 close compare_c;
2334
2335 commit;
2336 exception when others then
2337 null;
2338 end purge_plan;
2339
2340 END Msc_Netchange_PKG;