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