[Home] [Help]
PACKAGE BODY: APPS.MSC_LAUNCH_PLAN_PK
Source
1 PACKAGE BODY MSC_LAUNCH_PLAN_PK AS
2 /* $Header: MSCPLAPB.pls 120.9.12010000.2 2008/10/03 15:50:30 skakani ship $ */
3
4 FUNCTION LAUNCH_REFRESH_MV (p_plan_id in number) RETURN boolean
5 IS
6 lvs_request_id number;
7
8 l_call_status boolean;
9
10 l_phase varchar2(80);
11 l_status varchar2(80);
12 l_dev_phase varchar2(80);
13 l_dev_status varchar2(80);
14 l_message varchar2(2048);
15 BEGIN
16
17 lvs_request_id := FND_REQUEST.SUBMIT_REQUEST(
18 'MSC', -- application
19 'MSCREFSN', -- program
20 NULL, -- description
21 NULL, -- start time
22 FALSE, -- sub_request
23 p_plan_id);
24
25 COMMIT;
26
27 IF lvs_request_id=0 THEN
28 MSC_UTIL.msc_Debug('Launch Refresh MV MSCREFSN failed');
29 RETURN FALSE;
30 ELSE
31 MSC_UTIL.msc_Debug('Launched Program MSCREFSN Request:'|| to_char(lvs_request_id));
32 END IF;
33
34 LOOP
35 /* come out of function only when the MSCPDCP is complete - reqd for Collections incompatibility */
36
37 l_call_status:= FND_CONCURRENT.GET_REQUEST_STATUS
38 ( lvs_request_id,
39 NULL,
40 NULL,
41 l_phase,
42 l_status,
43 l_dev_phase,
44 l_dev_status,
45 l_message);
46
47 IF (l_call_status=FALSE) THEN
48 MSC_UTIL.msc_Debug('Failure in verifying request:'|| to_char(lvs_request_id));
49 RETURN FALSE;
50 END IF;
51
52 EXIT WHEN l_dev_phase = 'COMPLETE';
53
54 END LOOP;
55
56 RETURN TRUE;
57
58 EXCEPTION
59 WHEN OTHERS THEN
60 MSC_UTIL.msc_Debug('Failure in verifying request:'|| to_char(lvs_request_id));
61 RETURN FALSE;
62 END LAUNCH_REFRESH_MV;
63 -- Modification for bug 1863615 - removed the plan horizon date parameter
64 -- ************************* msc_launch_plan ******************************* --
65 PROCEDURE msc_launch_plan (
66 errbuf OUT NOCOPY VARCHAR2,
67 retcode OUT NOCOPY NUMBER,
68 arg_designator IN VARCHAR2,
69 arg_plan_id IN NUMBER,
70 arg_launch_snapshot IN NUMBER,
71 arg_launch_planner IN NUMBER,
72 arg_netchange_mode IN NUMBER,
73 arg_anchor_date IN VARCHAR2,
74 p_archive_flag IN number default 2,
75 p_plan_type_dummy IN VARCHAR2 default null,
76 p_24x7atp IN NUMBER default 2,
77 p_reschedule_dummy IN VARCHAR2 default null,
78 arg_release_reschedules IN NUMBER default 2,
79 p_snap_static_entities IN NUMBER default 1 ,
80 p_calculate_liability_dummy IN varchar2 default null ,
81 p_calculate_liabilty IN number default 2,
82 p_generate_fcst IN number default 2
83 )
84 IS
85
86 var_snapshot_req_id INTEGER;
87 var_planner_req_id INTEGER;
88 var_user_id INTEGER;
89 var_production1 INTEGER;
90 var_production2 INTEGER;
91 var_auto_release_id INTEGER;
92 months NUMBER;
93 -- Modification for bug 1863615
94 -- var_new_date DATE;
95
96 l_org_id NUMBER;
97 l_instance_id NUMBER;
98 l_platform_type NUMBER := 0;
99 l_enable_64b_snapshot NUMBER := 0;
100 l_call_status boolean;
101 l_phase varchar2(80);
102 l_status varchar2(80);
103 l_dev_phase varchar2(80);
104 l_dev_status varchar2(80);
105 l_message varchar2(2048);
106 l_industry VARCHAR2(30);
107 l_schema VARCHAR2(30);
108 var_desc VARCHAR2(50);
109 v_plan_id NUMBER;
110 v_new_plan_id NUMBER;
111 v_dummy NUMBER;
112 v_desig_id NUMBER;
113 var_temp_plan_id NUMBER;
114 var_temp_desig_id NUMBER;
115 v_completion_date date;
116 v_request_id number;
117 v_summary_flag number;
118 v_destination_notifications number;
119 var_plan_type number;
120 var_overwrite_all number;
121 var_desig_id number;
122 var_atp number;
123 var_production number;
124 var_inactive_on date;
125 var_organization_id number;
126 var_sr_instance_id number;
127 v_req_data number;
128 --V_Curr_req_id number:= FND_GLOBAL.CONC_REQUEST_ID;
129 v_lookup_name varchar2(100);
130 v_plan_completion_date date; --SNOP Change
131
132 CURSOR check_plan_id(p_plan_id IN Number,
133 p_designator IN VARCHAR2) IS
134 SELECT 1
135 FROM msc_plans mp
136 WHERE mp.plan_id = p_plan_Id
137 AND mp.compile_designator = p_designator;
138
139 CURSOR get_plan_id(p_designator IN VARCHAR2) IS
140 SELECT mp.plan_id
141 FROM msc_plans mp
142 WHERE mp.compile_designator = p_designator;
143
144 CURSOR C1(p_plan_id in number) IS
145 SELECT organization_id, sr_instance_id,
146 curr_Plan_Type, curr_Overwrite_Option,
147 request_id, plan_completion_date, summary_flag,
148 compile_designator
149 FROM msc_plans
150 WHERE plan_id = p_plan_id;
151
152 v_rec_c1 c1%rowtype;
153
154 cursor temp_plan_exist_cur(p_designator in varchar2,
155 p_org_id in number,
156 p_instance_id in number) is
157 SELECT plan_id, plan_completion_date,request_id, summary_flag
158 FROM msc_plans
159 WHERE compile_designator = p_designator
160 AND organization_id = p_org_id
161 AND sr_instance_id = p_instance_id;
162
163 cursor orig_desig_cur(p_plan_id in number) is
164 select desig.designator_id,
165 desig.description,
166 desig.inventory_atp_flag,
167 desig.launch_workflow_flag,
168 desig.production,
169 desig.disable_date,
170 desig.organization_id,
171 desig.sr_instance_id,
172 plans.curr_plan_type,
173 plans.curr_overwrite_option
174 from msc_designators desig, msc_plans plans
175 where desig.designator = plans.compile_designator
176 and plans.plan_id= p_plan_id
177 and plans.organization_id = desig.organization_id
178 and plans.sr_instance_id = desig.sr_instance_id;
179
180 cursor temp_desig_exists_cur(p_designator in varchar2,
181 p_org_id in number,
182 p_instance_id in number) is
183 SELECT designator_id
184 FROM msc_designators
185 WHERE designator = p_designator
186 AND organization_id = p_org_id
187 AND sr_instance_id = p_instance_id;
188
189 v_ex_error_plan_launch EXCEPTION;
190
191 BEGIN
192
193 OPEN check_plan_id(arg_plan_id, arg_designator );
194 FETCH check_plan_id INTO v_dummy;
195 IF check_plan_id%FOUND THEN
196 v_new_plan_id := arg_plan_id;
197 CLOSE check_plan_id;
198 ELSE
199 CLOSE check_plan_id;
200 msc_util.msc_debug('Plan Id changed.');
201 OPEN get_plan_id(arg_designator);
202 FETCH get_plan_id INTO v_new_plan_id;
203 CLOSE get_plan_id;
204 msc_util.msc_debug('New Plan Id is '||v_new_plan_id);
205 END IF;
206
207 --Additional code for release reschedules bug#2881012
208 update msc_plans
209 set release_reschedules = nvl(arg_release_reschedules,2),
210 calculate_liability = nvl(p_calculate_liabilty,2)
211 --where plan_id = arg_plan_id;
212 where plan_id = v_new_plan_id;
213
214 -- Modified (forward port) for the bug # 3021850
215 if p_24x7atp in (1,3) and arg_launch_snapshot = SYS_NO then
216 retcode := 5;
217 raise v_ex_error_plan_launch;
218 end if;
219 --v_plan_id := arg_plan_id;
220 v_plan_id := v_new_plan_id;
221 -- ---------------------------------------
222 -- Check for Cheild request.
223 -- ---------------------------------------
224 v_req_data := fnd_conc_global.request_data;
225 if v_req_data is null then
226 open c1(v_plan_id);
227 fetch c1 into v_rec_c1;
228 Close c1;
229
230 if v_rec_c1.request_id is not null then
231 -- -------------------------------------
232 -- Check if previous plan output exists.
233 -- if existing, check for the status of
234 -- of the plan output.
235 -- -------------------------------------
236 l_call_status:= FND_CONCURRENT.GET_REQUEST_STATUS
237 ( v_rec_c1.request_id,
238 NULL,
239 NULL,
240 l_phase,
241 l_status,
242 l_dev_phase,
243 l_dev_status,
244 l_message);
245 if v_rec_c1.plan_completion_date is not null then
246
247 if upper(l_dev_phase) not in ('COMPLETE','INACTIVE') then
248 if v_rec_c1.summary_flag = 2 then
249 v_lookup_name:= 'MSC_POST_PROCESS_RUNNING';
250 raise v_ex_error_plan_launch;
251 elsif v_rec_c1.summary_flag in (4,5) then
252 v_lookup_name:= 'MSC_SYNC_RUNNING';
253 raise v_ex_error_plan_launch;
254 else
255 v_lookup_name := 'MSC_PLAN_RUNNING';
256 raise v_ex_error_plan_launch;
257 end if;
258 elsif upper(l_dev_status)not in ('NORMAL','WARNING') and v_rec_c1.summary_flag = 2 then
259 v_lookup_name:= 'MSC_POST_PROCESS_FAIL';
260 raise v_ex_error_plan_launch;
261 end if;
262 -- ------------------------
263 -- If Output Exists then...
264 -- ------------------------
265 --Modified for the bug#2850632
266 open temp_plan_exist_cur(nvl(substr(to_char(v_plan_id),-10),v_plan_id),
267 v_rec_c1.organization_id,
268 v_rec_c1.sr_instance_id);
269 fetch temp_plan_exist_cur into var_temp_plan_id,v_completion_date, v_request_id, v_summary_flag;
270 -- ------------------------------------------------------
271 -- if Temp. plan exists, Check Plan/Sync. Process status.
272 -- ------------------------------------------------------
273 if temp_plan_exist_cur%found then
274 l_call_status:= FND_CONCURRENT.GET_REQUEST_STATUS
275 ( v_request_id,
276 NULL,
277 NULL,
278 l_phase,
279 l_status,
280 l_dev_phase,
281 l_dev_status,
282 l_message);
283 -- --------------------------------
284 -- If Completed then...
285 -- --------------------------------
286 if v_completion_date is not null then
287 if upper(l_dev_phase) <>'COMPLETE' then
288 if v_summary_flag = 2 then
289 v_lookup_name:= 'MSC_POST_PROCESS_RUNNING';
290 raise v_ex_error_plan_launch;
291 elsif v_summary_flag in (4,5 )then
292 v_lookup_name:= 'MSC_SYNC_RUNNING';
293 raise v_ex_error_plan_launch;
294 else
295 v_lookup_name := 'MSC_PLAN_RUNNING';
296 raise v_ex_error_plan_launch;
297 end if;
298 -- elsif upper(l_dev_status)not in ('NORMAL','WARNING') and v_summary_flag = 2 then
299 -- -------------------------------------
300 -- Plan run is completed unsuccessfully.
301 -- Re-launch plan.
302 -- -------------------------------------
303 end if;
304 -- ---------------------------------
305 -- if not completed then...
306 -- ---------------------------------
307 else
308 if upper(l_dev_phase) <>'COMPLETE' then
309 if v_summary_flag = 2 then
310 v_lookup_name:= 'MSC_POST_PROCESS_RUNNING';
311 raise v_ex_error_plan_launch;
312 elsif v_summary_flag in (4,5 )then
313 v_lookup_name:= 'MSC_SYNC_RUNNING';
314 raise v_ex_error_plan_launch;
315 else
316 v_lookup_name := 'MSC_PLAN_RUNNING';
317 raise v_ex_error_plan_launch;
318 end if;
319 -- elsif upper(l_dev_status)not in ('NORMAL','WARNING') and v_summary_flag = 2 then
320 -- --------------------------------------
321 -- Plan run is completed unsuccessfully.
322 -- re-launch the plan.
323 -- --------------------------------------
324 end if;
325 end if;
326 end if; -- End Temp. Plan exists Check .
327 else -- v_rec_c1.plan_completion_date is null then
328 -- ------------------------------------
329 -- if plan run is not complete then...
330 -- ------------------------------------
331 -- MSC_UTIL.msc_Debug('phase '||L_phase||' '||L_message);
332 IF upper(l_dev_phase) <>'COMPLETE' THEN
333 IF v_rec_c1.summary_flag = 2 THEN
334 v_lookup_name:= 'MSC_POST_PROCESS_RUNNING';
335 RAISE v_ex_error_plan_launch;
336 ELSE
337 v_lookup_name := 'MSC_PLAN_RUNNING';
338 RAISE v_ex_error_plan_launch;
339 END IF;
340 ELSIF upper(l_dev_status)not in ('NORMAL','WARNING') and v_rec_c1.summary_flag = 2 then
341 v_lookup_name:= 'MSC_POST_PROCESS_FAIL';
342 raise v_ex_error_plan_launch;
343 END IF;
344 end if; -- v_rec_c1.plan_completion_date check
345 End if; -- End Vrec_C1.request_id check
346 -- ------------------------
347 -- getting original desig.
348 -- ------------------------
349 open orig_desig_cur(v_plan_id);
350 fetch orig_desig_cur
351 into
352 var_desig_id, var_desc, var_atp, v_destination_notifications,
353 var_production, var_inactive_on, var_organization_id, var_sr_instance_id,
354 var_plan_type, var_overwrite_all;
355 close orig_desig_cur;
356
357 --Modified for the bug#2850632
358 open temp_desig_exists_cur(nvl(substr(to_char(v_plan_id),-10),v_plan_id),
359 v_rec_c1.organization_id,
360 v_rec_c1.sr_instance_id);
361
362 fetch temp_desig_exists_cur into var_temp_desig_id;
363 close temp_desig_exists_cur;
364 if not temp_plan_exist_cur%isopen then
365 --Modified for the bug#2850632
366 open temp_plan_exist_cur(nvl(substr(to_char(v_plan_id),-10),v_plan_id),
367 v_rec_c1.organization_id,
368 v_rec_c1.sr_instance_id);
369 fetch temp_plan_exist_cur into var_temp_plan_id,v_completion_date, v_request_id, v_summary_flag;
370 end if;
371
372 if nvl(var_temp_desig_id,0) > 0 then
373 -- Modified (forward port) for the bug # 3021850
374 -- ----------------------------------------------
375 -- Var_Temp_plan_id <>-1, Temp. plan exists.
376 -- Delete the temp. plan because, for any plan,
377 -- only one temp. plan can exist at any instance.
378 -- ----------------------------------------------
379 MSC_UTIL.msc_Debug('deleting temp_plan '||var_temp_plan_id);
380 msc_copy_plan_options.delete_temp_plan(errbuf, retcode, var_temp_desig_id, TRUE);
381 if retcode >0 then
382 raise v_ex_error_plan_launch;
383 end if;
384 Commit;
385 return;
386 end if;
387 end if; -- end checking req_data null
388 if var_desig_id is null then
389 -- ------------------------------
390 -- While child completes, and
391 -- the parent get re-launched,
392 -- var_desig_id will be null.
393 -- ------------------------------
394 open orig_desig_cur(v_plan_id);
395 fetch orig_desig_cur
396 Into
397 var_desig_id, var_desc, var_atp, v_destination_notifications,
398 var_production, var_inactive_on, var_organization_id, var_sr_instance_id,
399 var_plan_type, var_overwrite_all;
400 close orig_desig_cur;
401 end if;
402
403 -- Modified (forward port) for the bug # 3021850
404 if p_24x7atp in (1,3) then
405
406 -- ----------------------------------------------------
407 -- p_24x7atp -> Yes, purge current plan - 1
408 -- No - 2
409 -- Yes, do not purge current plan - 3
410 -- Set this flag to decide whether a 24x7 original plan
411 -- has to be purged.(Ref. : bug#3002550)
412
413 -- Copy Plan Options.
414 -- If running with 24X7mode then...
415 -- -----------------------------------------------------
416
417 update msc_designators
418 set purge_current_plan = decode(p_24x7atp,3,2,1)
419 where designator_id = var_desig_id;
420
421 msc_copy_plan_options.init_plan_id('N',NULL,NULL);
422 MSC_UTIL.msc_Debug('Copying plan options...' );
423 msc_copy_plan_options.copy_plan_options
424 ( v_plan_id , -- > p_source_plan_id
425 nvl(substr(to_char(v_plan_id),-10),v_plan_id), -- > p_dest_plan_name
426 var_desc, -- > p_dest_plan_desc
427 var_plan_type, -- > p_dest_plan_type
428 2 , -- > p_dest_atp
429 var_production, -- > p_dest_production
430 v_destination_notifications, -- > p_dest_notifications
431 var_inactive_on, -- > p_dest_inactive_on
432 var_organization_id , -- > p_organization_id
433 var_sr_instance_id); -- > p_sr_instance_id
434
435 msc_copy_plan_options.link_Plans
436 (errbuf, --> ERRBUF
437 retcode, --> RETCODE
438 v_plan_id, --> P_Src_plan_id
439 var_Desig_id, --> P_Src_Desg_id
440 var_Temp_plan_id, --> P_plan_id
441 var_temp_desig_id ); --> P_designator_id
442 if retcode > 0 then
443 raise v_ex_error_plan_launch;
444 end if;
445 msc_copy_plan_options.init_plan_id('N',NULL,NULL);
446
447 if var_overwrite_all<> 1 then
448 MSC_UTIL.msc_Debug('copying firm orders...' );
449 msc_copy_plan_options.copy_firm_orders(errbuf, retcode, v_plan_id, var_temp_plan_id);
450 if nvl(retcode,0) > 0 then
451 raise v_ex_error_plan_launch;
452 end if;
453 end if;
454 v_plan_id := var_temp_plan_id;
455 end if;
456 -- End of additional code for 24X7 ATP - 30-10-2002
457
458 /*----------------------------------------+
459 | Update msc_plans with plan horizon date |
460 +----------------------------------------*/
461 MSC_UTIL.msc_Debug('******About to Launch Plan******');
462 -- Modification for bug 1863615
463 -- IF arg_plan_horizon IS NULL THEN
464 -- months := fnd_profile.value('MRP_CUTOFF_DATE_OFFSET');
465
466 -- var_new_date := MSC_CALENDAR.NEXT_WORK_DAY(l_org_id,l_instance_id,1,
467 -- TO_DATE(TO_CHAR(add_months(sysdate, NVL(months, 12)),
468 -- 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS')) ;
469 -- UPDATE msc_plans
470 -- SET curr_cutoff_date = var_new_date ,
471 -- online_replan = NULL
472 -- WHERE plan_id = arg_plan_id;
473 --
474 -- COMMIT;
475
476 -- ELSE
477
478 -- var_new_date := MSC_CALENDAR.NEXT_WORK_DAY(l_org_id,l_instance_id,1,TO_DATE(arg_plan_horizon, 'YYYY/MM/DD HH24:MI:SS')) ;
479 -- UPDATE msc_plans
480 -- SET curr_cutoff_date = var_new_date,
481 -- online_replan = NULL
482 -- WHERE plan_id = arg_plan_id;
483 -- COMMIT;
484 -- END IF;
485
486 -- Bug 3478888 - Move this to MSCPOSTB.pls
487 /*
488 -- -------------------------------------
489 -- Reset the ATP_SYNCHRONIZATION_FLAG
490 -- to NULL for the orginal plan.
491 -- -------------------------------------
492 --Modified NULL to 0 for the bug#2797732
493 update msc_demands
494 set atp_synchronization_flag = 0
495 where plan_id = arg_plan_id;
496 */
497
498 /*---------------------------------------+
499 | Update msc_parameters with anchor date |
500 +---------------------------------------*/
501 UPDATE msc_parameters
502 SET repetitive_anchor_date = TO_DATE(arg_anchor_date, 'YYYY/MM/DD HH24:MI:SS')
503 WHERE (organization_id,sr_instance_id) IN (select organization_id, sr_instance_id
504 from msc_plan_organizations
505 Where plan_id = v_plan_id);
506 COMMIT;
507 /*-------------+
508 | Get user id |
509 +--------------*/
510
511 var_user_id := fnd_profile.value('USER_ID');
512 /*-----------------------------------------------+
513 | Insert subinventories into msc_sub_inventories |
514 | that are defined after options are defined |
515 +-----------------------------------------------*/
516 BEGIN
517
518 INSERT INTO MSC_SUB_INVENTORIES
519 (SUB_INVENTORY_CODE,
520 ORGANIZATION_ID,
521 SR_INSTANCE_ID,
522 PLAN_ID,
523 CREATION_DATE,
524 CREATED_BY,
525 LAST_UPDATE_LOGIN,
526 LAST_UPDATE_DATE,
527 LAST_UPDATED_BY,
528 NETTING_TYPE)
529 SELECT msi.sub_inventory_code,
530 mpo.organization_id,
531 mpo.sr_instance_id,
532 v_plan_id,
533 SYSDATE,
534 1,
535 -1,
536 SYSDATE,
537 1,
538 msi.netting_type
539 FROM msc_sub_inventories msi,
540 msc_plan_organizations mpo
541 WHERE NOT EXISTS
542 (SELECT NULL
543 FROM MSC_SUB_INVENTORIES SUB
544 WHERE SUB.ORGANIZATION_ID = mpo.organization_id
545 AND sub.sr_instance_id = mpo.sr_instance_id
546 AND SUB.plan_id = mpo.plan_id
547 AND SUB.sub_inventory_code = msi.sub_inventory_code)
548 AND msi.ORGANIZATION_ID = mpo.organization_id
549 AND msi.sr_instance_id = mpo.sr_instance_id
550 AND msi.plan_id = -1
551 AND mpo.plan_id = v_plan_id;
552
553 -- --------------------------------------------
554 -- If plan launch is in 24x7 mode, keep a copy
555 -- of sub inventories in original plan as well.
556 -- --------------------------------------------
557 --if v_plan_id <> arg_plan_id THEN
558 if v_plan_id <> v_new_plan_id THEN
559 INSERT INTO MSC_SUB_INVENTORIES
560 (SUB_INVENTORY_CODE,
561 ORGANIZATION_ID,
562 SR_INSTANCE_ID,
563 PLAN_ID,
564 CREATION_DATE,
565 CREATED_BY,
566 LAST_UPDATE_LOGIN,
567 LAST_UPDATE_DATE,
568 LAST_UPDATED_BY,
569 NETTING_TYPE)
570 SELECT
571 msi.sub_inventory_code,
572 mpo.organization_id,
573 mpo.sr_instance_id,
574 v_new_plan_id, --arg_plan_id,
575 SYSDATE,
576 1,
577 -1,
578 SYSDATE,
579 1,
580 msi.netting_type
581 FROM msc_sub_inventories msi,
582 msc_plan_organizations mpo
583 WHERE NOT EXISTS
584 (SELECT NULL
585 FROM msc_sub_inventories sub
586 WHERE sub.organization_id = mpo.organization_id
587 AND sub.sr_instance_id = mpo.sr_instance_id
588 AND sub.plan_id = mpo.plan_id
589 AND sub.sub_inventory_code = msi.sub_inventory_code)
590 AND msi.organization_id = mpo.organization_id
591 AND msi.sr_instance_id = mpo.sr_instance_id
592 AND msi.plan_id = -1
593 --AND mpo.plan_id = arg_plan_id;
594 AND mpo.plan_id = v_new_plan_id;
595 End if;
596
597 COMMIT;
598
599 EXCEPTION when no_data_found then
600 null;
601 END;
602
603
604 -- SNOP Change start
605
606 SELECT plan_start_date
607 INTO v_plan_completion_date
608 FROM msc_plans
609 WHERE plan_id = v_plan_id;
610
611 IF (arg_launch_snapshot = DP_SCN_ONLY_SNAPSHOT AND
612 v_plan_completion_date is not null ) --Not a first time plan launch
613 THEN
614 UPDATE msc_plans
615 SET planning_mode = DP_SCN_ONLY_SNP_MODE
616 WHERE plan_id = v_plan_id;
617 COMMIT;
618 ELSE
619 UPDATE msc_plans
620 SET planning_mode = NULL
621 WHERE plan_id = v_plan_id;
622 COMMIT;
623 END IF;
624
625 -- SNOP Change End
626 IF ( p_generate_fcst = 1 )
627 THEN
628 UPDATE msc_plans
629 SET GENERATE_INLINE_FORECAST= SYS_YES
630 WHERE plan_id = v_plan_id;
631 COMMIT;
632 ELSE
633 UPDATE msc_plans
634 SET GENERATE_INLINE_FORECAST= SYS_NO
635 WHERE plan_id = v_plan_id;
636 COMMIT;
637
638 END IF;
639
640
641 IF (arg_launch_snapshot = SYS_YES OR
642 (arg_launch_snapshot = DP_SCN_ONLY_SNAPSHOT AND --SNOP Change for first time plan launch
643 v_plan_completion_date is null ))
644 THEN
645
646 /* changes for launching 64 bit snapshot */
647 l_enable_64b_snapshot := fnd_profile.value('MSC_ENABLE_64BIT_SNAPSHOT');
648 IF l_enable_64b_snapshot IS NULL THEN
649 l_enable_64b_snapshot := 0;
650 END IF;
651
652 IF (l_enable_64b_snapshot > 0)
653 THEN
654 l_platform_type := fnd_profile.value('MSC_PLANNER_PLATFORM');
655 IF l_platform_type IS NULL THEN
656 l_platform_type := 0;
657 END IF;
658 ELSE
659 l_platform_type := 0;
660 END IF;
661
662 IF l_platform_type = 0 THEN
663 MSC_UTIL.msc_Debug('Launching Snapshot for 32 bit');
664 var_snapshot_req_id := NULL;
665 IF (arg_netchange_mode = SYS_NO) THEN
666 var_snapshot_req_id := FND_REQUEST.SUBMIT_REQUEST(
667 'MSC', -- application
668 'MSCNSP', -- program
669 NULL, -- description
670 NULL, -- start time
671 FALSE, -- sub_request
672 v_plan_id,
673 2, -- Launch_CRP_planner
674 0, -- snapshot_worker
675 0, -- monitor_pipe
676 0, -- monitor_request_id
677 1, -- Netchange_mode
678 p_snap_static_entities);
679
680 ELSE
681 var_snapshot_req_id := FND_REQUEST.SUBMIT_REQUEST(
682 'MSC', -- application
683 'MSCNSP', -- program
684 NULL, -- description
685 NULL, -- start time
686 FALSE, -- sub_request
687 v_plan_id, -- plan_id
688 2, -- Launch_CRP_planner
689 0, -- snapshot_worker
690 0, -- monitor_pipe
691 0, -- monitor_request_id
692 4, -- Netchange_mode
693 1 -- p_snap_static_entities
694 );
695 END IF;
696
697 ELSIF l_platform_type = 1 THEN
698 MSC_UTIL.msc_Debug('Launching Snapshot for 64 bit Sun');
699 var_snapshot_req_id := NULL;
700 IF (arg_netchange_mode = SYS_NO) THEN
701 var_snapshot_req_id := FND_REQUEST.SUBMIT_REQUEST(
702 'MSC', -- application
703 'MSCNSPWS64', -- program
704 NULL, -- description
705 NULL, -- start time
706 FALSE, -- sub_request
707 v_plan_id,
708 2, -- Launch_CRP_planner
709 0, -- snapshot_worker
710 0, -- monitor_pipe
711 0, -- monitor_request_id
712 1, -- Netchange_mode
713 p_snap_static_entities);
714
715 ELSE
716 var_snapshot_req_id := FND_REQUEST.SUBMIT_REQUEST(
717 'MSC', -- application
718 'MSCNSPWS64', -- program
719 NULL, -- description
720 NULL, -- start time
721 FALSE, -- sub_request
722 v_plan_id, -- plan_id
723 2, -- Launch_CRP_planner
724 0, -- snapshot_worker
725 0, -- monitor_pipe
726 0, -- monitor_request_id
727 4, -- Netchange_mode
728 1 -- p_snap_static_entities
729 );
730 END IF;
731 ELSIF l_platform_type = 2 THEN
732 MSC_UTIL.msc_Debug('Launching Snapshot for 64 bit HP');
733 var_snapshot_req_id := NULL;
734 IF (arg_netchange_mode = SYS_NO) THEN
735 var_snapshot_req_id := FND_REQUEST.SUBMIT_REQUEST(
736 'MSC', -- application
737 'MSCNSPWH64', -- program
738 NULL, -- description
739 NULL, -- start time
740 FALSE, -- sub_request
741 v_plan_id,
742 2, -- Launch_CRP_planner
743 0, -- snapshot_worker
744 0, -- monitor_pipe
745 0, -- monitor_request_id
746 1, -- Netchange_mode
747 p_snap_static_entities);
748
749 ELSE
750 var_snapshot_req_id := FND_REQUEST.SUBMIT_REQUEST(
751 'MSC', -- application
752 'MSCNSPWH64', -- program
753 NULL, -- description
754 NULL, -- start time
755 FALSE, -- sub_request
756 v_plan_id, -- plan_id
757 2, -- Launch_CRP_planner
758 0, -- snapshot_worker
759 0, -- monitor_pipe
760 0, -- monitor_request_id
761 4, -- Netchange_mode
762 1 -- p_snap_static_entities
763 );
764 END IF;
765 ELSIF l_platform_type = 3 THEN
766 MSC_UTIL.msc_Debug('Launching Snapshot for 64 bit AIX');
767 var_snapshot_req_id := NULL;
768 IF (arg_netchange_mode = SYS_NO) THEN
769 var_snapshot_req_id := FND_REQUEST.SUBMIT_REQUEST(
770 'MSC', -- application
771 'MSCNSPWA64', -- program
772 NULL, -- description
773 NULL, -- start time
774 FALSE, -- sub_request
775 v_plan_id,
776 2, -- Launch_CRP_planner
777 0, -- snapshot_worker
778 0, -- monitor_pipe
779 0, -- monitor_request_id
780 1, -- Netchange_mode
781 p_snap_static_entities);
782
783 ELSE
784 var_snapshot_req_id := FND_REQUEST.SUBMIT_REQUEST(
785 'MSC', -- application
786 'MSCNSPWA64', -- program
787 NULL, -- description
788 NULL, -- start time
789 FALSE, -- sub_request
790 v_plan_id, -- plan_id
791 2, -- Launch_CRP_planner
792 0, -- snapshot_worker
793 0, -- monitor_pipe
794 0, -- monitor_request_id
795 4, -- Netchange_mode
796 1 -- p_snap_static_entities
797 );
798 END IF;
799
800 ELSIF l_platform_type = 4 THEN
801 MSC_UTIL.msc_Debug('Launching Snapshot for Linux 64bit');
802 var_snapshot_req_id := NULL;
803 IF (arg_netchange_mode = SYS_NO) THEN
804 var_snapshot_req_id := FND_REQUEST.SUBMIT_REQUEST(
805 'MSC', -- application
806 'MSCNSPWL64', -- program
807 NULL, -- description
808 NULL, -- start time
809 FALSE, -- sub_request
810 v_plan_id,
811 2, -- Launch_CRP_planner
812 0, -- snapshot_worker
813 0, -- monitor_pipe
814 0, -- monitor_request_id
815 1, -- Netchange_mode
816 p_snap_static_entities);
817 ELSE
818 var_snapshot_req_id := FND_REQUEST.SUBMIT_REQUEST(
819 'MSC', -- application
820 'MSCNSPWL64', -- program
821 NULL, -- description
822 NULL, -- start time
823 FALSE, -- sub_request
824 v_plan_id, -- plan_id
825 2, -- Launch_CRP_planner
826 0, -- snapshot_worker
827 0, -- monitor_pipe
828 0, -- monitor_request_id
829 4, -- Netchange_mode
830 1 -- p_snap_static_entities
831 );
832 END IF;
833
834 ELSIF l_platform_type = 5 THEN
835 MSC_UTIL.msc_Debug('Launching Snapshot for HP Itanium 64bit');
836 var_snapshot_req_id := NULL;
837 IF (arg_netchange_mode = SYS_NO) THEN
838 var_snapshot_req_id := FND_REQUEST.SUBMIT_REQUEST(
839 'MSC', -- application
840 'MSCNSPWHPIA64', -- program
841 NULL, -- description
842 NULL, -- start time
843 FALSE, -- sub_request
844 v_plan_id,
845 2, -- Launch_CRP_planner
846 0, -- snapshot_worker
847 0, -- monitor_pipe
848 0, -- monitor_request_id
849 1, -- Netchange_mode
850 p_snap_static_entities);
851 ELSE
852 var_snapshot_req_id := FND_REQUEST.SUBMIT_REQUEST(
853 'MSC', -- application
854 'MSCNSPWHPIA64', -- program
855 NULL, -- description
856 NULL, -- start time
857 FALSE, -- sub_request
858 v_plan_id, -- plan_id
859 2, -- Launch_CRP_planner
860 0, -- snapshot_worker
861 0, -- monitor_pipe
862 0, -- monitor_request_id
863 4, -- Netchange_mode
864 1 -- p_snap_static_entities
865 );
866 END IF;
867
868 ELSE
869 retcode := 2;
870 errbuf := 'Invalid Platform Type'||to_char(l_platform_type);
871 return;
872 END IF;
873
874
875 UPDATE msc_plans /* for 24x7 ATP */
876 SET request_id = var_snapshot_req_id
877 WHERE plan_id = v_plan_id;
878
879 COMMIT;
880
881 MSC_UTIL.msc_Debug('Launched Snapshot:'||
882 to_char(var_snapshot_req_id));
883
884 END IF; /* if arg_launch_snapshot = SYS_YES */
885
886 IF ((arg_launch_planner = SYS_YES) AND
887 (arg_launch_snapshot = SYS_NO OR
888 (arg_launch_snapshot = DP_SCN_ONLY_SNAPSHOT AND
889 v_plan_completion_date is not null ))) /* SNOP Change */
890 THEN
891 var_planner_req_id := NULL;
892
893
894 /*-------------+
895 | Get platform |
896 +-------------*/
897 l_platform_type := fnd_profile.value('MSC_PLANNER_PLATFORM');
898 IF l_platform_type IS NULL THEN
899 l_platform_type := 0;
900 END IF;
901
902 IF fnd_installation.get_app_info('MSO',l_status,l_industry,l_schema) <> TRUE THEN
903 retcode := 2;
904 errbuf := 'Error checking installation status of MSO';
905 ELSE
906 IF l_status = 'I' THEN
907 IF l_platform_type = 0 THEN
908 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST(
909 'MSO', -- application
910 'MSONEW', -- program
911 NULL, -- description
912 NULL, -- start time
913 FALSE, -- sub_request
914 v_plan_id,
915 0);
916 ELSIF l_platform_type = 1 THEN
917 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST(
918 'MSO', -- application
919 'MSONWS64', -- program
920 NULL, -- description
921 NULL, -- start time
922 FALSE, -- sub_request
923 v_plan_id,
924 0);
925 ELSIF l_platform_type = 2 THEN
926 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST(
927 'MSO', -- application
928 'MSONWH64', -- program
929 NULL, -- description
930 NULL, -- start time
931 FALSE, -- sub_request
932 v_plan_id,
933 0);
934 ELSIF l_platform_type = 3 THEN
935 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST(
936 'MSO', -- application
937 'MSONWA64', -- program
938 NULL, -- description
939 NULL, -- start time
940 FALSE, -- sub_request
941 v_plan_id,
942 0);
943 ELSIF l_platform_type = 4 THEN
944 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST(
945 'MSO', -- application
946 'MSONWL64', -- program
947 NULL, -- description
948 NULL, -- start time
949 FALSE, -- sub_request
950 v_plan_id,
951 0);
952 ELSIF l_platform_type = 5 THEN
953 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST(
954 'MSO', -- application
955 'MSONWHPIA64', -- program
956 NULL, -- description
957 NULL, -- start time
958 FALSE, -- sub_request
959 v_plan_id,
960 0);
961
962 ELSE
963 retcode := 2;
964 errbuf := 'Invalid Platform Type'||to_char(l_platform_type);
965 return;
966 END IF;
967 ELSE
968 IF l_platform_type = 0 THEN
969 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST(
970 'MSC', -- application
971 'MSCNEW', -- program
972 NULL, -- description
973 NULL, -- start time
974 FALSE, -- sub_request
975 v_plan_id,
976 0);
977 ELSIF l_platform_type = 1 THEN
978 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST(
979 'MSC', -- application
980 'MSCNWS64', -- program
981 NULL, -- description
982 NULL, -- start time
983 FALSE, -- sub_request
984 v_plan_id,
985 0);
986 ELSIF l_platform_type = 2 THEN
987 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST(
988 'MSC', -- application
989 'MSCNWH64', -- program
990 NULL, -- description
991 NULL, -- start time
992 FALSE, -- sub_request
993 v_plan_id,
994 0);
995 ELSIF l_platform_type = 3 THEN
996 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST(
997 'MSC', -- application
998 'MSCNWA64', -- program
999 NULL, -- description
1000 NULL, -- start time
1001 FALSE, -- sub_request
1002 v_plan_id,
1003 0);
1004
1005 ELSIF l_platform_type = 4 THEN
1006 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST(
1007 'MSC', -- application
1008 'MSCNWL64', -- program
1009 NULL, -- description
1010 NULL, -- start time
1011 FALSE, -- sub_request
1012 v_plan_id,
1013 0);
1014 ELSIF l_platform_type = 5 THEN
1015 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST(
1016 'MSC', -- application
1017 'MSCNWHPIA64', -- program
1018 NULL, -- description
1019 NULL, -- start time
1020 FALSE, -- sub_request
1021 v_plan_id,
1022 0);
1023
1024
1025 ELSE
1026 retcode := 2;
1027 errbuf := 'Invalid Platform Type'||to_char(l_platform_type);
1028 return;
1029 END IF;
1030 END IF;
1031 END IF;
1032 UPDATE msc_plans /* for 24x7 ATP */
1033 SET request_id = var_snapshot_req_id
1034 WHERE plan_id = v_plan_id;
1035
1036 COMMIT;
1037 MSC_UTIL.msc_Debug('Launched Planner:'||
1038 to_char(var_planner_req_id));
1039 END IF;
1040
1041 begin
1042
1043 SELECT NVL(production, SYS_NO)
1044 INTO var_production1
1045 FROM msc_designators
1046 where organization_id = v_rec_c1.organization_id
1047 AND sr_instance_id = v_rec_c1.sr_instance_id
1048 AND (designator,designator_type) in
1049 (select compile_designator, plan_type
1050 from msc_plans
1051 Where plan_id = v_plan_id);
1052
1053 exception when no_data_found then
1054 null;
1055 end;
1056
1057 --pabram..phub
1058 MSC_UTIL.msc_Debug('phub archive_flag :'|| to_char(p_archive_flag));
1059 update msc_plans
1060 set archive_flag = p_archive_flag
1061 where plan_id = v_plan_id;
1062 commit;
1063 --pabram..phub ends
1064 MSC_UTIL.msc_Debug('Exiting with Success');
1065 retcode := 0;
1066 errbuf := NULL;
1067 return;
1068 EXCEPTION
1069 when v_ex_error_plan_launch then
1070 if temp_plan_exist_cur%isopen then
1071 Close temp_plan_exist_Cur;
1072 end if;
1073 retcode := 2;
1074 if v_lookup_name is not null then
1075 fnd_message.set_name('MSC',v_lookup_name);
1076 fnd_message.set_token('PLAN_NAME',v_rec_c1.compile_designator);
1077 errbuf := fnd_message.get;
1078 end if;
1079 when OTHERS THEN
1080 retcode := 2;
1081 errbuf := sqlerrm;
1082
1083 END msc_launch_plan;
1084
1085
1086 /*========================================================================+
1087 | DESCRIPTION : This procedure is called for switching plans - original |
1088 | and temporary, once synchronization is complete. |
1089 +========================================================================*/
1090
1091 PROCEDURE msc_switch_24_7_atp_plans(
1092 errbuf OUT NOCOPY VARCHAR2,
1093 retcode OUT NOCOPY NUMBER,
1094 P_Org_plan_id IN NUMBER,
1095 P_temp_plan_id IN NUMBER) IS
1096
1097 -- --------------------------------------------
1098 -- Modified for Bug#2748937. Fetching few more
1099 -- columns needed to be retained from the new
1100 -- plan, after restoring plan options.
1101 -- --------------------------------------------
1102
1103 Cursor Plan_Name_Cur(p_plan_id in number) Is
1104 Select Compile_Designator, Curr_Plan_Type,
1105 Organization_Id, Sr_Instance_Id,Copy_Plan_Id,
1106 Last_Update_Date, Last_Updated_By, Last_Update_Login,
1107 Append_Planned_Orders, Assignment_Set_Id, Attribute_Category,
1108 Attribute1, Attribute2, Attribute3, Attribute4,
1109 Attribute5, Attribute6, Attribute7, Attribute8,
1110 Attribute9, Attribute10, Attribute11, Attribute12,
1111 Attribute13, Attribute14, Attribute15, Backward_Days,
1112 Bill_Of_Resources, Bottleneck_Res_Group, Company_Agg_Level,
1113 Consider_Po, Consider_Reservations, Consider_Wip,
1114 Created_By, Creation_Date, Curr_Included_Items, Curr_Schedule_Designator,
1115 Curr_Schedule_Type, Curr_Snapshot_Lock, Curr_Split_Demands,
1116 Cutoff_Date, Daily_Trans_Constraints, Data_Completion_Date,
1117 Data_Start_Date, Dem_Priority_Rule_Id, Demand_Time_Fence_Flag,
1118 Enable_Closest_Qty_Pegging, Enable_Priority_Pegging,
1119 Enforce_Cap_Constraints, Enforce_Dem_Due_Dates,
1120 Enforce_Sl_Constraints, Enforce_Src_Constraints,
1121 Forward_Days, Full_Pegging, Hard_Pegging_Level, Included_Items,
1122 Kpi_Refresh, Lot_For_Lot, Max_Wf_Except_Id, Min_Wf_Except_Id,
1123 Monthly_Cutoff_Bucket, Objective_Weight_6,
1124 Objective_Weight_7, Objective_Weight_8, Objective_Weight_9,
1125 Objective_Weight_10, Online_Planner_Completion_Date,
1126 Online_Planner_Start_Date, Online_Replan, Operation_Schedule_Type,
1127 Organization_Selection, Overwrite_Option, Parent_Plan_Id,
1128 Part_Include_Type, Penalty_Cost_1, Penalty_Cost_2, Penalty_Cost_3,
1129 Penalty_Cost_4, Penalty_Cost_5, Penalty_Cost_6, Penalty_Cost_7,
1130 Penalty_Cost_8, Penalty_Cost_9, Penalty_Cost_10, Period_Trans_Constraints,
1131 Plan_Capacity_Flag, Plan_Completion_Date, Plan_Safety_Stock,
1132 Plan_Start_Date, Planned_Refreshes, Planned_Resources,
1133 Planning_Time_Fence_Flag, Product_Agg_Level, Production_Flag,
1134 Program_Application_Id, Program_Id, Program_Update_Date, Purge,
1135 Qtrly_Cutoff_Bucket, Refresh_Number, Request_Id, Reservation_Level,
1136 Revision, Schedule_Designator, Schedule_Type, Simulation_Set,
1137 Slack_Allowed_Flag_1, Slack_Allowed_Flag_2, Slack_Allowed_Flag_3,
1138 Slack_Allowed_Flag_4, Slack_Allowed_Flag_5, Slack_Allowed_Flag_6,
1139 Slack_Allowed_Flag_7, Slack_Allowed_Flag_8, Slack_Allowed_Flag_9,
1140 Slack_Allowed_Flag_10, Snapshot_Lock, Split_Demands,
1141 Start_Date, Status, Summary_Flag, Tp_Agg_Level,
1142 Weekly_Trans_Constraints,
1143 curr_start_date,
1144 curr_cutoff_date,
1145 release_reschedules
1146 From Msc_Plans
1147 Where Plan_Id = p_plan_id;
1148
1149 -- Modified (forward port) for the bug # 3021850
1150 Cursor Desig_Cur(p_plan_id in number) Is
1151 Select Designator_Id,description, Inventory_Atp_Flag, launch_workflow_flag,
1152 Production, Disable_Date,organization_id, sr_instance_id,
1153 organization_selection,last_update_date,last_updated_by,last_update_login,nvl(purge_current_plan,2) purge_current_plan
1154 From Msc_Designators
1155 Where (Designator, Organization_Id, Sr_Instance_Id) =
1156 ( Select Compile_Designator, Organization_Id, Sr_Instance_Id
1157 From Msc_Plans
1158 Where Plan_Id=p_plan_id);
1159
1160 cursor c_plan_name(p_designator in varchar2) is
1161 select designator
1162 from msc_designators
1163 where designator = p_designator;
1164
1165 v_org_Plan_Name_Cur Plan_Name_Cur%rowtype;
1166 v_temp_Plan_Name_Cur Plan_Name_Cur%rowtype;
1167 v_org_Desig_Cur Desig_Cur%rowtype;
1168 v_temp_Desig_Cur Desig_Cur%rowtype;
1169 v_req_data number;
1170 v_lookup_name varchar2(100);
1171 lv_plan_name varchar2(10);
1172
1173 ERROR_DELETION EXCEPTION;
1174 Begin
1175
1176 open plan_name_cur(p_org_plan_id);
1177 fetch plan_name_cur into v_org_plan_name_Cur;
1178 if plan_name_Cur%notfound then
1179 retcode := 2;
1180 fnd_message.set_name('MSC','MSC_X_API_INVALID_PLAN_NAME');
1181 errbuf := fnd_message.get;
1182 raise error_deletion;
1183 END IF;
1184 close plan_name_cur;
1185
1186 open desig_cur(p_org_plan_id);
1187 fetch desig_cur into v_org_desig_cur;
1188 close desig_cur;
1189
1190
1191 open plan_name_cur(p_temp_plan_id);
1192 fetch plan_name_cur into v_temp_plan_name_Cur;
1193 if plan_name_Cur%notfound or v_temp_plan_name_cur.plan_completion_date is null
1194 or p_org_plan_id <> v_temp_plan_name_cur.copy_plan_id then
1195 retcode := 2;
1196 fnd_message.set_name('MSC','MSC_X_API_INVALID_PLAN_NAME');
1197 errbuf := fnd_message.get;
1198 close plan_name_cur;
1199 raise error_deletion;
1200 end if;
1201 close plan_name_cur;
1202
1203 open desig_cur(p_temp_plan_id);
1204 fetch desig_cur into v_temp_desig_cur;
1205 if desig_cur%notfound then
1206 retcode:= 2;
1207 fnd_message.set_name('MSC','MSC_X_API_INVALID_PLAN_NAME');
1208 errbuf := fnd_message.get;
1209 close desig_cur;
1210 raise error_deletion;
1211 end if;
1212 close desig_cur;
1213
1214 -- ------------------------------------
1215 -- To delete the Plan OPTIONS to enable
1216 -- to copy fresh plan options.
1217 -- ------------------------------------
1218
1219 MSC_COPY_PLAN_OPTIONS.delete_plan_options
1220 (errbuf, retcode, p_temp_plan_id);
1221 if nvl(retcode,0) >0 then
1222 raise error_deletion;
1223 end if;
1224
1225 -- ------------------------------------
1226 -- Set the name of original plan to
1227 -- temp name, so that the temp plan
1228 -- can straight a way, be created with
1229 -- Original Plan's name.
1230 -- ------------------------------------
1231
1232 -- Modified (forward port) for the bug # 3021850
1233 if v_org_desig_cur.purge_current_plan = 2 THEN
1234
1235 loop
1236 select '#'||nvl(substr(to_char(msc_24x7_plan_name_s.nextval),-9),msc_24x7_plan_name_s.nextval)
1237 into lv_plan_name
1238 from dual;
1239
1240 open c_plan_name(lv_plan_name);
1241 fetch c_plan_name into lv_plan_name;
1242
1243 if c_plan_name%notfound then
1244 close c_plan_name;
1245 exit;
1246 end if;
1247 close c_plan_name;
1248 end loop;
1249 end if;
1250
1251 -------------------------------------------------------------------------------
1252 -- Modified for the bug#2850632
1253 -- Temp plan id is stored in the designator column to maintain the
1254 -- link. And in case if the purge program has failed, this link
1255 -- will be used to purge the plan during relaunch. Purge is needed
1256 -- for the following reasons:
1257 -- 1. Free the partition if the MSC: share partition is set to False
1258 -- 2. Multiple temp plans cannot be created with the same name which
1259 -- will violate the unique key(designator,sr_instance_id and organization_id)
1260 -------------------------------------------------------------------------------
1261
1262 update msc_plans
1263 set compile_designator = decode(v_org_desig_cur.purge_current_plan,2,lv_plan_name, nvl(substr(to_char(p_temp_plan_id),-10),p_temp_plan_id)),
1264 -- copy_plan_id=p_temp_plan_id,
1265 copy_plan_id=decode(v_org_desig_cur.purge_current_plan,2,-1,-2),
1266 plan_completion_date = decode(v_org_desig_cur.purge_current_plan,2,v_org_plan_name_Cur.plan_completion_date,null),
1267 request_id = decode(v_org_desig_cur.purge_current_plan,2,v_org_plan_name_Cur.request_id,null),
1268 data_completion_date = decode(v_org_desig_cur.purge_current_plan,2,v_org_plan_name_Cur.data_completion_date,null),
1269 summary_flag = decode(v_org_desig_cur.purge_current_plan,2,v_org_plan_name_Cur.summary_flag,null)
1270 where plan_id = p_org_plan_id;
1271
1272 -- Modified for the bug#2850632
1273 update msc_designators
1274 set designator = decode(v_org_desig_cur.purge_current_plan,2,lv_plan_name,
1275 nvl(substr(to_char(p_temp_plan_id),-10),p_temp_plan_id)),
1276 -- copy_designator_id = v_temp_desig_cur.designator_id
1277 copy_designator_id = decode(v_org_desig_cur.purge_current_plan,2,-1,-2),
1278 inventory_atp_flag = decode(v_org_desig_cur.purge_current_plan,2,2,inventory_atp_flag),
1279 description = decode(v_org_desig_cur.purge_current_plan,2,substr(description,1,39)||'-'||v_org_plan_name_Cur.compile_designator,description)
1280 where designator = v_org_plan_name_Cur.compile_designator
1281 and organization_id = v_org_desig_cur.organization_id
1282 and sr_instance_id = v_org_desig_cur.sr_instance_id;
1283
1284
1285 msc_copy_plan_options.init_plan_id('Y',p_temp_plan_id,v_temp_desig_cur.designator_id);
1286 msc_copy_plan_options.copy_plan_options
1287 ( p_org_plan_id, -- > p_source_plan_id
1288 v_org_plan_name_cur.compile_designator, -- > p_dest_plan_name
1289 v_org_desig_Cur.Description, -- > p_dest_plan_desc
1290 --v_org_Desig_Cur.organization_selection, -- > p_dest_org_selection
1291 v_org_plan_name_Cur.curr_Plan_Type, -- > p_dest_plan_type
1292 v_org_desig_Cur.Inventory_Atp_Flag, -- > p_dest_atp
1293 v_org_desig_Cur.production, -- > p_dest_production
1294 v_org_desig_Cur.launch_workflow_flag, -- > p_dest_notifications
1295 v_org_desig_Cur.disable_date, -- > p_dest_inactive_on
1296 v_org_desig_Cur.organization_id , -- > p_organization_id
1297 v_org_desig_Cur.sr_instance_id); -- > p_sr_instance_id
1298
1299 msc_copy_plan_options.init_plan_id('N',NULL,NULL);
1300 -- -----------------------------------------------
1301 -- Set the copy_plan_id to -1. Restore basic info.
1302 -- modified by planning engine.
1303 -- -----------------------------------------------
1304 -- --------------------------------------------
1305 -- Modified for Bug#2748937. Included few more
1306 -- columns needed to be retained from the new
1307 -- plan, after restoring plan options.
1308 -- ----------------------------------------------
1309 -- Bug#2761381 - restoring all the columns which
1310 -- will not be modified from Plan options UI.
1311 -- ----------------------------------------------
1312 Update msc_plans
1313 set copy_plan_id = -1,
1314 Last_Update_Date = v_temp_plan_name_cur.Last_Update_Date,
1315 Last_Updated_By = v_temp_plan_name_cur.Last_Updated_By,
1316 Last_Update_Login = v_temp_plan_name_cur.Last_Update_Login,
1317 Append_Planned_Orders = v_temp_plan_name_cur.Append_Planned_Orders,
1318 Assignment_Set_Id = v_temp_plan_name_cur.Assignment_Set_Id,
1319 Attribute_Category = v_temp_plan_name_cur.Attribute_Category,
1320 Attribute1 = v_temp_plan_name_cur.Attribute1,
1321 Attribute2 = v_temp_plan_name_cur.Attribute2,
1322 Attribute3 = v_temp_plan_name_cur.Attribute3,
1323 Attribute4 = v_temp_plan_name_cur.Attribute4,
1324 Attribute5 = v_temp_plan_name_cur.Attribute5,
1325 Attribute6 = v_temp_plan_name_cur.Attribute6,
1326 Attribute7 = v_temp_plan_name_cur.Attribute7,
1327 Attribute8 = v_temp_plan_name_cur.Attribute8,
1328 Attribute9 = v_temp_plan_name_cur.Attribute9,
1329 Attribute10 = v_temp_plan_name_cur.Attribute10,
1330 Attribute11 = v_temp_plan_name_cur.Attribute11,
1331 Attribute12 = v_temp_plan_name_cur.Attribute12,
1332 Attribute13 = v_temp_plan_name_cur.Attribute13,
1333 Attribute14 = v_temp_plan_name_cur.Attribute14,
1334 Attribute15 = v_temp_plan_name_cur.Attribute15,
1335 Backward_Days = v_temp_plan_name_cur.Backward_Days,
1336 Bill_Of_Resources = v_temp_plan_name_cur.Bill_Of_Resources,
1337 Bottleneck_Res_Group = v_temp_plan_name_cur.Bottleneck_Res_Group,
1338 Company_Agg_Level = v_temp_plan_name_cur.Company_Agg_Level,
1339 Consider_Po = v_temp_plan_name_cur.Consider_Po,
1340 Consider_Reservations = v_temp_plan_name_cur.Consider_Reservations,
1341 Consider_Wip = v_temp_plan_name_cur.Consider_Wip,
1342 Created_By = v_temp_plan_name_cur.Created_By,
1343 Creation_Date = v_temp_plan_name_cur.Creation_Date,
1344 Curr_Included_Items = v_temp_plan_name_cur.Curr_Included_Items,
1345 Curr_Schedule_Designator= v_temp_plan_name_cur.Curr_Schedule_Designator,
1346 Curr_Schedule_Type = v_temp_plan_name_cur.Curr_Schedule_Type,
1347 Curr_Snapshot_Lock = v_temp_plan_name_cur.Curr_Snapshot_Lock,
1348 Curr_Split_Demands = v_temp_plan_name_cur.Curr_Split_Demands,
1349 Cutoff_Date = v_temp_plan_name_cur.Cutoff_Date,
1350 Daily_Trans_Constraints = v_temp_plan_name_cur.Daily_Trans_Constraints,
1351 Data_Completion_Date = v_temp_plan_name_cur.Data_Completion_Date,
1352 Data_Start_Date = v_temp_plan_name_cur.Data_Start_Date,
1353 Dem_Priority_Rule_Id = v_temp_plan_name_cur.Dem_Priority_Rule_Id,
1354 Demand_Time_Fence_Flag = v_temp_plan_name_cur.Demand_Time_Fence_Flag,
1355 Enable_Closest_Qty_Pegging = v_temp_plan_name_cur.Enable_Closest_Qty_Pegging,
1356 Enable_Priority_Pegging = v_temp_plan_name_cur.Enable_Priority_Pegging,
1357 Enforce_Cap_Constraints = v_temp_plan_name_cur.Enforce_Cap_Constraints,
1358 Enforce_Dem_Due_Dates = v_temp_plan_name_cur.Enforce_Dem_Due_Dates,
1359 Enforce_Sl_Constraints = v_temp_plan_name_cur.Enforce_Sl_Constraints,
1360 Enforce_Src_Constraints = v_temp_plan_name_cur.Enforce_Src_Constraints,
1361 Forward_Days = v_temp_plan_name_cur.Forward_Days,
1362 Full_Pegging = v_temp_plan_name_cur.Full_Pegging,
1363 Hard_Pegging_Level = v_temp_plan_name_cur.Hard_Pegging_Level,
1364 Included_Items = v_temp_plan_name_cur.Included_Items,
1365 Kpi_Refresh = v_temp_plan_name_cur.Kpi_Refresh,
1366 Lot_For_Lot = v_temp_plan_name_cur.Lot_For_Lot,
1367 Max_Wf_Except_Id = v_temp_plan_name_cur.Max_Wf_Except_Id,
1368 Min_Wf_Except_Id = v_temp_plan_name_cur.Min_Wf_Except_Id,
1369 Monthly_Cutoff_Bucket = v_temp_plan_name_cur.Monthly_Cutoff_Bucket,
1370 Objective_Weight_6 = v_temp_plan_name_cur.Objective_Weight_6,
1371 Objective_Weight_7 = v_temp_plan_name_cur.Objective_Weight_7,
1372 Objective_Weight_8 = v_temp_plan_name_cur.Objective_Weight_8,
1373 Objective_Weight_9 = v_temp_plan_name_cur.Objective_Weight_9,
1374 Objective_Weight_10 = v_temp_plan_name_cur.Objective_Weight_10,
1375 Online_Planner_Completion_Date = v_temp_plan_name_cur.Online_Planner_Completion_Date,
1376 Online_Planner_Start_Date = v_temp_plan_name_cur.Online_Planner_Start_Date,
1377 Online_Replan = v_temp_plan_name_cur.Online_Replan,
1378 Operation_Schedule_Type = v_temp_plan_name_cur.Operation_Schedule_Type,
1379 Organization_Selection = v_temp_plan_name_cur.Organization_Selection,
1380 Overwrite_Option = v_temp_plan_name_cur.Overwrite_Option,
1381 Parent_Plan_Id = v_temp_plan_name_cur.Parent_Plan_Id,
1382 Part_Include_Type = v_temp_plan_name_cur.Part_Include_Type,
1383 Penalty_Cost_1 = v_temp_plan_name_cur.Penalty_Cost_1,
1384 Penalty_Cost_2 = v_temp_plan_name_cur.Penalty_Cost_2,
1385 Penalty_Cost_3 = v_temp_plan_name_cur.Penalty_Cost_3,
1386 Penalty_Cost_4 = v_temp_plan_name_cur.Penalty_Cost_4,
1387 Penalty_Cost_5 = v_temp_plan_name_cur.Penalty_Cost_5,
1388 Penalty_Cost_6 = v_temp_plan_name_cur.Penalty_Cost_6,
1389 Penalty_Cost_7 = v_temp_plan_name_cur.Penalty_Cost_7,
1390 Penalty_Cost_8 = v_temp_plan_name_cur.Penalty_Cost_8,
1391 Penalty_Cost_9 = v_temp_plan_name_cur.Penalty_Cost_9,
1392 Penalty_Cost_10 = v_temp_plan_name_cur.Penalty_Cost_10,
1393 Period_Trans_Constraints= v_temp_plan_name_cur.Period_Trans_Constraints,
1394 Plan_Capacity_Flag = v_temp_plan_name_cur.Plan_Capacity_Flag,
1395 Plan_Completion_Date = v_temp_plan_name_cur.Plan_Completion_Date,
1396 Plan_Safety_Stock = v_temp_plan_name_cur.Plan_Safety_Stock,
1397 Plan_Start_Date = v_temp_plan_name_cur.Plan_Start_Date,
1398 Planned_Refreshes = v_temp_plan_name_cur.Planned_Refreshes,
1399 Planned_Resources = v_temp_plan_name_cur.Planned_Resources,
1400 Planning_Time_Fence_Flag= v_temp_plan_name_cur.Planning_Time_Fence_Flag,
1401 Product_Agg_Level = v_temp_plan_name_cur.Product_Agg_Level,
1402 Production_Flag = v_temp_plan_name_cur.Production_Flag,
1403 Program_Application_Id = v_temp_plan_name_cur.Program_Application_Id,
1404 Program_Id = v_temp_plan_name_cur.Program_Id,
1405 Program_Update_Date = v_temp_plan_name_cur.Program_Update_Date,
1406 Purge = v_temp_plan_name_cur.Purge,
1407 Qtrly_Cutoff_Bucket = v_temp_plan_name_cur.Qtrly_Cutoff_Bucket,
1408 Refresh_Number = v_temp_plan_name_cur.Refresh_Number,
1409 Request_Id = v_temp_plan_name_cur.Request_Id,
1410 Reservation_Level = v_temp_plan_name_cur.Reservation_Level,
1411 Revision = v_temp_plan_name_cur.Revision,
1412 Schedule_Designator = v_temp_plan_name_cur.Schedule_Designator,
1413 Schedule_Type = v_temp_plan_name_cur.Schedule_Type,
1414 Simulation_Set = v_temp_plan_name_cur.Simulation_Set,
1415 Slack_Allowed_Flag_1 = v_temp_plan_name_cur.Slack_Allowed_Flag_1,
1416 Slack_Allowed_Flag_2 = v_temp_plan_name_cur.Slack_Allowed_Flag_2,
1417 Slack_Allowed_Flag_3 = v_temp_plan_name_cur.Slack_Allowed_Flag_3,
1418 Slack_Allowed_Flag_4 = v_temp_plan_name_cur.Slack_Allowed_Flag_4,
1419 Slack_Allowed_Flag_5 = v_temp_plan_name_cur.Slack_Allowed_Flag_5,
1420 Slack_Allowed_Flag_6 = v_temp_plan_name_cur.Slack_Allowed_Flag_6,
1421 Slack_Allowed_Flag_7 = v_temp_plan_name_cur.Slack_Allowed_Flag_7,
1422 Slack_Allowed_Flag_8 = v_temp_plan_name_cur.Slack_Allowed_Flag_8,
1423 Slack_Allowed_Flag_9 = v_temp_plan_name_cur.Slack_Allowed_Flag_9,
1424 Slack_Allowed_Flag_10 = v_temp_plan_name_cur.Slack_Allowed_Flag_10,
1425 Snapshot_Lock = v_temp_plan_name_cur.Snapshot_Lock,
1426 Split_Demands = v_temp_plan_name_cur.Split_Demands,
1427 Start_Date = v_temp_plan_name_cur.Start_Date,
1428 Status = v_temp_plan_name_cur.Status,
1429 Summary_Flag = v_temp_plan_name_cur.Summary_Flag,
1430 Tp_Agg_Level = v_temp_plan_name_cur.Tp_Agg_Level,
1431 Weekly_Trans_Constraints= v_temp_plan_name_cur.Weekly_Trans_Constraints,
1432 curr_start_date = v_temp_plan_name_cur.curr_start_date,
1433 curr_cutoff_date = v_temp_plan_name_cur.curr_cutoff_date,
1434 release_reschedules = v_temp_plan_name_cur.release_reschedules
1435 where plan_id = p_temp_plan_id;
1436
1437 -- --------------------------------
1438 -- Set Copy_designator_id to -1.
1439 -- --------------------------------
1440
1441 update msc_designators
1442 set copy_designator_id = -1,
1443 last_update_date = v_temp_plan_name_Cur.last_update_date,
1444 last_updated_by = v_temp_plan_name_Cur.last_updated_by,
1445 last_update_login = v_temp_plan_name_Cur.last_update_login,
1446 request_id = v_temp_plan_name_cur.request_id,
1447 program_application_id = v_temp_plan_name_cur.program_application_id,
1448 program_id = v_temp_plan_name_cur.program_id,
1449 program_update_date = v_temp_plan_name_cur.program_update_date
1450 where (designator,organization_id,sr_instance_id)
1451 = ( select compile_designator,organization_id,sr_instance_id
1452 from msc_plans
1453 where plan_id=p_temp_plan_id);
1454
1455 Update MSC_PLAN_ORGANIZATIONS
1456 set plan_completion_date = v_temp_plan_name_cur.Plan_Completion_Date,
1457 last_updated_by = v_temp_plan_name_cur.last_updated_by,
1458 last_update_date = v_temp_plan_name_cur.last_update_date,
1459 last_update_login = v_temp_plan_name_cur.last_update_login,
1460 request_id = v_temp_plan_name_cur.request_id,
1461 program_application_id = v_temp_plan_name_cur.program_application_id,
1462 program_id = v_temp_plan_name_cur.program_id,
1463 program_update_date = v_temp_plan_name_cur.program_update_date
1464 Where plan_id = p_temp_plan_id;
1465
1466 Update MSC_PLAN_SCHEDULES
1467 set last_updated_by = v_temp_plan_name_cur.last_updated_by,
1468 last_update_date = v_temp_plan_name_cur.last_update_date,
1469 last_update_login = v_temp_plan_name_cur.last_update_login,
1470 request_id = v_temp_plan_name_cur.request_id,
1471 program_application_id = v_temp_plan_name_cur.program_application_id,
1472 program_id = v_temp_plan_name_cur.program_id,
1473 program_update_date = v_temp_plan_name_cur.program_update_date
1474 Where plan_id = p_temp_plan_id;
1475
1476 Update MSC_SUB_INVENTORIES
1477 set last_updated_by = v_temp_plan_name_cur.last_updated_by,
1478 last_update_date = v_temp_plan_name_cur.last_update_date,
1479 last_update_login = v_temp_plan_name_cur.last_update_login,
1480 request_id = v_temp_plan_name_cur.request_id,
1481 program_application_id = v_temp_plan_name_cur.program_application_id,
1482 program_id = v_temp_plan_name_cur.program_id,
1483 program_update_date = v_temp_plan_name_cur.program_update_date
1484 Where plan_id = p_temp_plan_id;
1485
1486 DECLARE
1487 TYPE tab_type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
1488
1489 l_rowlist tab_type;
1490
1491 CURSOR c_pref(p_plan_id IN VARCHAR2) IS
1492 SELECT ROWID
1493 FROM MSC_USER_PREFERENCE_VALUES
1494 WHERE key IN ('ASCP_PLAN_ID','DRP_PLAN_ID')
1495 AND value = p_plan_id
1496 FOR UPDATE OF value NOWAIT;
1497
1498
1499 CURSOR c_schedules(p_designator_id IN NUMBER,
1500 p_organization_id IN NUMBER,
1501 p_sr_instance_id IN NUMBER) IS
1502 SELECT ROWID
1503 FROM MSC_PLAN_SCHEDULES
1504 WHERE input_schedule_id = p_designator_id
1505 AND organization_id = p_organization_id
1506 AND sr_instance_id = p_sr_instance_id
1507 FOR UPDATE OF input_schedule_id NOWAIT;
1508
1509 l_Counter NUMBER;
1510 l_max_tries CONSTANT Number := 100000;
1511 BEGIN
1512 savepoint BF_UPD_PREF;
1513 MSC_UTIL.msc_Debug('Updating preferences...');
1514 -- update w/b preferences
1515 l_Counter := 0;
1516 LOOP
1517 BEGIN
1518 EXIT WHEN l_Counter > l_max_tries;
1519 l_Counter := l_Counter + 1;
1520 OPEN c_pref(TO_CHAR(p_org_plan_id));
1521 FETCH c_pref BULK COLLECT INTO l_rowlist;
1522 CLOSE c_pref;
1523
1524 FORALL I IN 1..l_rowlist.count
1525 UPDATE MSC_USER_PREFERENCE_VALUES
1526 SET VALUE=p_temp_plan_id
1527 WHERE ROWID=l_rowlist(I);
1528 EXIT;
1529 EXCEPTION
1530 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
1531 IF c_pref%ISOPEN THEN
1532 CLOSE c_pref;
1533 END IF;
1534 IF l_Counter > l_max_tries THEN
1535 MSC_UTIL.msc_Debug('Unable to lock preference rows.');
1536 --RAISE;
1537 rollback to BF_UPD_PREF;
1538 EXIT;
1539 END IF;
1540 IF TRUNC(l_Counter/100)*100 = l_Counter THEN
1541 MSC_UTIL.msc_Debug('Unable to lock preference rows: ('||l_Counter||') Tries...');
1542 END IF;
1543 END;
1544 END LOOP;
1545
1546 savepoint BF_UPD_SCHD;
1547 MSC_UTIL.msc_Debug('Updating schedules...');
1548 -- update supply/demand schedules
1549 l_Counter := 0;
1550 l_rowlist.delete;
1551 LOOP
1552 BEGIN
1553
1554 l_Counter := l_Counter + 1;
1555
1556 OPEN c_schedules(v_org_desig_cur.designator_id,
1557 v_org_desig_cur.organization_id,
1558 v_org_desig_cur.sr_instance_id);
1559 FETCH c_schedules BULK COLLECT INTO l_rowlist;
1560 CLOSE c_schedules;
1561
1562 FORALL I IN 1..l_rowlist.count
1563 Update MSC_PLAN_SCHEDULES
1564 SET input_schedule_id = v_temp_desig_cur.designator_id
1565 WHERE ROWID=l_rowlist(I);
1566 EXIT;
1567 EXCEPTION
1568 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
1569 IF c_pref%ISOPEN THEN
1570 CLOSE c_pref;
1571 END IF;
1572 IF l_Counter > l_max_tries THEN
1573 MSC_UTIL.msc_Debug('Unable to lock schedules rows.');
1574 --RAISE;
1575 rollback to BF_UPD_SCHD;
1576 EXIT;
1577 END IF;
1578 IF TRUNC(l_Counter/100)*100 = l_Counter THEN
1579 MSC_UTIL.msc_Debug('Unable to lock schedules rows: ('||l_Counter||') Tries...');
1580 END IF;
1581 END;
1582 END LOOP;
1583
1584 EXCEPTION
1585 WHEN OTHERS THEN
1586 MSC_UTIL.msc_Debug('Error updating preferences and schedules.');
1587 RAISE;
1588 END;
1589 -- Bug#7449943
1590 Update msc_plans_other
1591 set ref_plan_id = p_temp_plan_id
1592 where ref_plan_id = p_org_plan_id;
1593
1594 commit;
1595
1596 -- ---------------------------------
1597 -- To delete the Original Plan,
1598 -- ---------------------------------
1599 -- Modified (forward port) for the bug # 3021850
1600 if nvl(v_org_desig_cur.purge_current_plan,1) = 1 THEN
1601 MSC_UTIL.msc_Debug('Purging Original plan...' );
1602 MSC_COPY_PLAN_OPTIONS.delete_temp_plan
1603 (errbuf, retcode, v_org_desig_cur.designator_id,FALSE);
1604
1605 if nvl(retcode,0) >0 then
1606 raise error_deletion;
1607 end if;
1608 commit;
1609 end if;
1610 errbuf := null;
1611 retcode := 0;
1612 EXCEPTION
1613 when error_deletion then
1614 rollback;
1615 WHEN OTHERS THEN
1616 rollback;
1617 retcode := 2;
1618 errbuf := errbuf||':'|| sqlerrm;
1619 END msc_switch_24_7_atp_plans;
1620
1621 -- ************************* msc_launch_schedule ******************************* --
1622 PROCEDURE msc_launch_schedule ( errbuf OUT NOCOPY VARCHAR2
1623 , retcode OUT NOCOPY NUMBER
1624 , arg_plan_id IN NUMBER
1625 , arg_launch_snapshot IN NUMBER
1626 , arg_launch_scheduler IN NUMBER
1627 , arg_ols_horizon_days IN NUMBER default null
1628 , arg_frozen_horizon_days IN NUMBER default null)
1629 IS
1630 var_snapshot_req_id INTEGER;
1631 var_planner_req_id INTEGER;
1632 l_platform_type NUMBER := 0;
1633 l_call_status BOOLEAN;
1634 l_phase VARCHAR2(80);
1635 l_status VARCHAR2(80);
1636 l_dev_phase VARCHAR2(80);
1637 l_dev_status VARCHAR2(80);
1638 l_message VARCHAR2(2048);
1639 l_industry VARCHAR2(30);
1640 l_schema VARCHAR2(30);
1641 v_plan_id NUMBER;
1642 v_lookup_name VARCHAR2(100);
1643
1644 l_executable_name VARCHAR2(30);
1645
1646 CURSOR C1(p_plan_id in number)
1647 IS
1648 SELECT request_id
1649 , compile_designator
1650 FROM msc_plans
1651 WHERE plan_id = p_plan_id;
1652
1653 v_rec_c1 c1%rowtype;
1654
1655 v_ex_error_plan_launch EXCEPTION;
1656 BEGIN
1657
1658 /*================================================================
1659 Update msc_plans.planning_mode with arg_launch_scheduler
1660 if arg_launch_scheduler == DS_EXP_ONLY (DS Exception only mode)
1661 then msc_plans.planning_mode will be set to 1.
1662 In all other cases msc_plans.planning_mode will be set to NULL
1663 ================================================================*/
1664 BEGIN
1665 update msc_plans
1666 set planning_mode = decode(arg_launch_scheduler, DS_EXP_ONLY, 1,
1667 DS_OLS_ONLY, DS_OLS_ONLY,
1668 NULL),
1669 curr_ols_horizon_days = arg_ols_horizon_days,
1670 ols_frozen_horizon_days = arg_frozen_horizon_days
1671 where plan_id = arg_plan_id;
1672
1673 COMMIT;
1674
1675 EXCEPTION WHEN OTHERS THEN
1676 MSC_UTIL.msc_Debug('Error while updating msc_plans.planning_mode. Plan_id = '||to_char(arg_plan_id));
1677 MSC_UTIL.msc_Debug(SQLERRM);
1678 END;
1679
1680 v_plan_id := arg_plan_id;
1681 open C1(v_plan_id);
1682 fetch C1 into v_rec_c1;
1683 Close C1;
1684 If v_rec_c1.request_id is not null then
1685 -- -------------------------------------
1686 -- Check if previous plan output exists.
1687 -- if existing, check for the status of
1688 -- of the plan output.
1689 -- -------------------------------------
1690 l_call_status:= FND_CONCURRENT.GET_REQUEST_STATUS ( v_rec_c1.request_id
1691 , NULL
1692 , NULL
1693 , l_phase
1694 , l_status
1695 , l_dev_phase
1696 , l_dev_status
1697 , l_message);
1698 if upper(l_dev_phase) <> 'COMPLETE' then
1699 v_lookup_name := 'MSC_PLAN_RUNNING'; -- this will be changed to MSC_SCHEDULE_RUNNING
1700 raise v_ex_error_plan_launch;
1701 end if;
1702 End if; -- End Vrec_C1.request_id check
1703
1704 /*-----------------------------------------------+
1705 | Insert subinventories into msc_sub_inventories |
1706 | that are defined after options are defined |
1707 +-----------------------------------------------*/
1708 if (arg_launch_scheduler <> DS_OLS_ONLY) then
1709 BEGIN
1710 INSERT INTO MSC_SUB_INVENTORIES
1711 ( SUB_INVENTORY_CODE
1712 , ORGANIZATION_ID
1713 , SR_INSTANCE_ID
1714 , PLAN_ID
1715 , CREATION_DATE
1716 , CREATED_BY
1717 , LAST_UPDATE_LOGIN
1718 , LAST_UPDATE_DATE
1719 , LAST_UPDATED_BY
1720 , NETTING_TYPE
1721 )
1722 (
1723 SELECT msi.sub_inventory_code
1724 , mpo.organization_id
1725 , mpo.sr_instance_id
1726 , v_plan_id
1727 , SYSDATE
1728 , 1
1729 , -1
1730 , SYSDATE
1731 , 1
1732 , msi.netting_type
1733 FROM msc_sub_inventories msi
1734 , msc_plan_organizations mpo
1735 WHERE NOT EXISTS (SELECT NULL
1736 FROM MSC_SUB_INVENTORIES SUB
1737 WHERE SUB.ORGANIZATION_ID = mpo.organization_id
1738 AND sub.sr_instance_id = mpo.sr_instance_id
1739 AND SUB.plan_id = mpo.plan_id
1740 AND SUB.sub_inventory_code = msi.sub_inventory_code)
1741 AND msi.ORGANIZATION_ID = mpo.organization_id
1742 AND msi.sr_instance_id = mpo.sr_instance_id
1743 AND msi.plan_id = -1
1744 AND mpo.plan_id = v_plan_id
1745 );
1746
1747 COMMIT;
1748
1749 EXCEPTION
1750 when no_data_found then
1751 null;
1752 END;
1753 end if;
1754
1755 IF (arg_launch_snapshot = SYS_YES) THEN
1756 var_snapshot_req_id := NULL;
1757 var_snapshot_req_id := FND_REQUEST.SUBMIT_REQUEST( 'MSC' -- application
1758 , 'MSCNSP' -- program
1759 , NULL -- description
1760 , NULL -- start time
1761 , FALSE -- sub_request
1762 , v_plan_id -- plan_id
1763 , 2 -- Launch_CRP_planner
1764 , 0 -- snapshot_worker
1765 , 0 -- monitor_pipe
1766 , 0 -- monitor_request_id
1767 , 1 -- Netchange_mode
1768 , 1 -- p_snap_static_entities
1769 );
1770 COMMIT;
1771 MSC_UTIL.msc_Debug('Launched Snapshot:'||to_char(var_snapshot_req_id));
1772 END IF; /* if arg_launch_snapshot = SYS_YES */
1773
1774 IF (((arg_launch_scheduler = SYS_YES) AND (arg_launch_snapshot = SYS_NO)) or
1775 (arg_launch_scheduler = DS_OLS_ONLY)
1776 ) THEN
1777 var_planner_req_id := NULL;
1778
1779 /*-------------+
1780 | Get platform |
1781 +-------------*/
1782 l_platform_type := fnd_profile.value('MSC_PLANNER_PLATFORM');
1783 IF l_platform_type IS NULL THEN
1784 l_platform_type := 0;
1785 END IF;
1786
1787 IF fnd_installation.get_app_info('MSO',l_status,l_industry,l_schema) <> TRUE THEN
1788 retcode := 2;
1789 errbuf := 'Error checking installation status of MSO';
1790 ELSE
1791 IF l_status = 'I' THEN
1792 IF (arg_launch_scheduler = DS_OLS_ONLY) then
1793 CASE l_platform_type
1794 WHEN 0 THEN l_executable_name := 'MSOOLS';
1795 WHEN 1 THEN l_executable_name := 'MSOOLSS64';
1796 WHEN 2 THEN l_executable_name := 'MSOOLSH64';
1797 WHEN 3 THEN l_executable_name := 'MSOOLSPA64';
1798 ELSE
1799 retcode := 2;
1800 errbuf := 'Invalid Platform Type: '||to_char(l_platform_type);
1801 return;
1802 END CASE;
1803
1804 MSC_UTIL.msc_Debug('Launched Program:'||l_executable_name);
1805
1806 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST('MSO' -- application
1807 , l_executable_name
1808 , NULL -- description
1809 , NULL -- start time
1810 , FALSE -- sub_request
1811 , v_plan_id
1812 , 0
1813 , 3 -- batch_process
1814 );
1815 ELSE
1816 CASE l_platform_type
1817 WHEN 0 THEN l_executable_name := 'MSONEW';
1818 WHEN 1 THEN l_executable_name := 'MSONWS64';
1819 WHEN 2 THEN l_executable_name := 'MSONWH64';
1820 WHEN 3 THEN l_executable_name := 'MSONWA64';
1821 ELSE
1822 retcode := 2;
1823 errbuf := 'Invalid Platform Type: '||to_char(l_platform_type);
1824 return;
1825 END CASE;
1826
1827 MSC_UTIL.msc_Debug('Launched Program:'||l_executable_name);
1828
1829 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST('MSO' -- application
1830 , l_executable_name
1831 , NULL -- description
1832 , NULL -- start time
1833 , FALSE -- sub_request
1834 , v_plan_id
1835 , 0
1836 );
1837 END IF;
1838
1839 ELSE
1840 IF l_platform_type = 0 THEN
1841 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST( 'MSC' -- application
1842 , 'MSCNEW' -- program
1843 , NULL -- description
1844 , NULL -- start time
1845 , FALSE -- sub_request
1846 , v_plan_id
1847 , 0
1848 );
1849 ELSIF l_platform_type = 1 THEN
1850 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST( 'MSC' -- application
1851 , 'MSCNWS64' -- program
1852 , NULL -- description
1853 , NULL -- start time
1854 , FALSE -- sub_request
1855 , v_plan_id
1856 , 0
1857 );
1858 ELSIF l_platform_type = 2 THEN
1859 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST( 'MSC' -- application
1860 , 'MSCNWH64' -- program
1861 , NULL -- description
1862 , NULL -- start time
1863 , FALSE -- sub_request
1864 , v_plan_id
1865 , 0);
1866 ELSIF l_platform_type = 3 THEN
1867 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST( 'MSC' -- application
1868 , 'MSCNWA64' -- program
1869 , NULL -- description
1870 , NULL -- start time
1871 , FALSE -- sub_request
1872 , v_plan_id
1873 , 0);
1874 ELSE
1875 retcode := 2;
1876 errbuf := 'Invalid Platform Type'||to_char(l_platform_type);
1877 return;
1878 END IF;
1879 END IF;
1880 COMMIT;
1881 END IF;
1882
1883 MSC_UTIL.msc_Debug('Launched Planner:'||to_char(var_planner_req_id));
1884 END IF;
1885
1886 MSC_UTIL.msc_Debug('Exiting with Success');
1887 retcode := 0;
1888 errbuf := NULL;
1889
1890 EXCEPTION
1891 when v_ex_error_plan_launch then
1892 retcode := 2;
1893 if v_lookup_name is not null then
1894 fnd_message.set_name('MSC',v_lookup_name);
1895 fnd_message.set_token('PLAN_NAME',v_rec_c1.compile_designator);
1896 errbuf := fnd_message.get;
1897 end if;
1898 when OTHERS THEN
1899 retcode := 2;
1900 errbuf := sqlerrm;
1901 END msc_launch_schedule;
1902
1903
1904 -- ************************* MSC_CHECK_PLAN_COMPLETION ******************************* --
1905 -- Procedure which checks for plan completion and returns the completion code.
1906 -- IF the plan is launched in DP_SCN_ONLY_SNP_MODE, check for Planner's completion
1907 -- ELSE check for teh completion of both Snapshot and planner.
1908
1909 PROCEDURE MSC_CHECK_PLAN_COMPLETION(
1910 launch_plan_request_id IN NUMBER,
1911 plan_id IN NUMBER,
1912 completion_code OUT NOCOPY NUMBER)
1913 IS
1914 l_snapshot_request_id NUMBER;
1915 l_planner_request_id NUMBER;
1916 l_request_id NUMBER;
1917 l_plan_id NUMBER;
1918 l_planning_mode NUMBER;
1919
1920 l_out_status NUMBER;
1921 l_max_wait_time NUMBER;
1922
1923 BEGIN
1924 l_snapshot_request_id := NULL_VALUE;
1925 l_planner_request_id := NULL_VALUE;
1926 l_request_id := NULL_VALUE;
1927 l_plan_id := plan_id;
1928
1929 ---- ***IMPORTANT -- Get the Max time verified
1930 l_max_wait_time := 999999;
1931
1932
1933 SELECT nvl(request_id, NULL_VALUE)
1934 INTO l_request_id
1935 FROM msc_plans
1936 WHERE plan_id = l_plan_id;
1937
1938
1939 -- Wait until the request_id is populated in msc_Plans.
1940 WHILE ( l_request_id < launch_plan_request_id)
1941 LOOP
1942 SELECT nvl(request_id, NULL_VALUE)
1943 INTO l_request_id
1944 FROM msc_plans
1945 WHERE plan_id = l_plan_id;
1946
1947 DBMS_LOCK.SLEEP(10);
1948 END LOOP;
1949
1950 -- Request_id is populated. Get the planning Mode
1951 SELECT planning_mode
1952 INTO l_planning_mode
1953 FROM msc_plans
1954 WHERE plan_id = l_plan_id;
1955
1956 IF (l_planning_mode is null ) -- Both snapshot and planner would be launched
1957 THEN
1958 l_snapshot_request_id := l_request_id;
1959
1960 -- Wait for Snapshot to complete
1961 MSC_WAIT_FOR_REQUEST(l_snapshot_request_id, l_max_wait_time, l_out_status);
1962
1963 IF ( l_out_status = FAILURE_OR_TIMEOUT )
1964 THEN
1965 completion_code := SNAPSHOT_FAILURE;
1966 RETURN;
1967 END IF;
1968 END IF;
1969
1970
1971 --- If there was a snapshot launched then get the plan_request_id.
1972 IF (l_snapshot_request_id <> NULL_VALUE )
1973 THEN
1974 SELECT nvl(request_id, NULL_VALUE)
1975 INTO l_request_id
1976 FROM msc_plans
1977 WHERE plan_id = l_plan_id;
1978
1979 -- Wait until the Plan_request_id is populated in msc_Plans.
1980 WHILE ( l_request_id < l_snapshot_request_id)
1981 LOOP
1982 SELECT nvl(request_id, NULL_VALUE)
1983 INTO l_request_id
1984 FROM msc_plans
1985 WHERE plan_id = l_plan_id;
1986
1987 DBMS_LOCK.SLEEP(10);
1988 END LOOP;
1989 END IF;
1990
1991 --- Planner Request_id is populated
1992 l_planner_request_id := l_request_id;
1993
1994 --- Wait for planner to complete
1995 MSC_WAIT_FOR_REQUEST(l_planner_request_id, l_max_wait_time, l_out_status);
1996
1997 IF ( l_out_status = FAILURE_OR_TIMEOUT )
1998 THEN
1999 completion_code := PLANNER_FAILURE;
2000 RETURN;
2001 END IF;
2002
2003 --- Request(s) completed sucessfully
2004 completion_code := SUCCESS;
2005 RETURN;
2006
2007 END MSC_CHECK_PLAN_COMPLETION;
2008
2009 -- ************************* MSC_WAIT_FOR_REQUEST ******************************* --
2010
2011 PROCEDURE MSC_WAIT_FOR_REQUEST(
2012 p_request_id IN number,
2013 p_timeout IN NUMBER,
2014 o_retcode OUT NOCOPY NUMBER)
2015 IS
2016
2017 l_refreshed_flag NUMBER;
2018 l_pending_timeout_flag NUMBER;
2019 l_start_time DATE;
2020
2021 ---------------- used for fnd_concurrent ---------
2022 l_call_status boolean;
2023 l_phase varchar2(80);
2024 l_status varchar2(80);
2025 l_dev_phase varchar2(80);
2026 l_dev_status varchar2(80);
2027 l_message varchar2(240);
2028 l_request_id number;
2029
2030 BEGIN
2031 l_request_id := p_request_id;
2032 l_start_time := SYSDATE;
2033
2034 LOOP
2035 << begin_loop >>
2036
2037 l_pending_timeout_flag := SIGN( SYSDATE - l_start_time - p_timeout/1440.0);
2038
2039 l_call_status:= FND_CONCURRENT.WAIT_FOR_REQUEST
2040 ( l_request_id,
2041 10,
2042 10,
2043 l_phase,
2044 l_status,
2045 l_dev_phase,
2046 l_dev_status,
2047 l_message);
2048
2049 EXIT WHEN l_call_status=FALSE;
2050
2051 IF l_dev_phase='PENDING' THEN
2052 EXIT WHEN l_pending_timeout_flag= 1;
2053
2054 ELSIF l_dev_phase='RUNNING' THEN
2055 GOTO begin_loop;
2056
2057 ELSIF l_dev_phase='COMPLETE' THEN
2058 IF l_dev_status = 'NORMAL' THEN
2059 o_retcode:= SYS_YES;
2060 RETURN;
2061 END IF;
2062 EXIT;
2063
2064 ELSIF l_dev_phase='INACTIVE' THEN
2065 EXIT WHEN l_pending_timeout_flag= 1;
2066 END IF;
2067
2068 DBMS_LOCK.SLEEP(10);
2069
2070 END LOOP;
2071
2072 o_retcode:= SYS_NO;
2073 RETURN;
2074 END MSC_WAIT_FOR_REQUEST;
2075
2076 Procedure purge_user_notes_data(p_plan_id number)
2077 IS
2078 BEGIN
2079 DELETE FROM msc_user_notes mun
2080 WHERE mun.plan_id = p_plan_id
2081 AND NOT exists (SELECT 1 -- sup.plan_id,sup.transaction_id
2082 FROM msc_supplies sup
2083 WHERE plan_id = mun.plan_id
2084 AND sup.sr_instance_id = mun.sr_instance_id
2085 AND sup.transaction_id = mun.transaction_id);
2086
2087 /*delete from msc_user_notes
2088 where plan_id = p_plan_id and
2089 (plan_id,transaction_id) not in (select plan_id,transaction_id
2090 from msc_supplies
2091 where plan_id = p_plan_id); */
2092 EXCEPTION
2093 WHEN OTHERS THEN
2094 NULL;
2095 END purge_user_notes_data;
2096
2097 END MSC_LAUNCH_PLAN_PK; -- package