[Home] [Help]
PACKAGE BODY: APPS.MSC_PLAN_RELEASE
Source
1 PACKAGE BODY MSC_PLAN_RELEASE AS
2 -- $Header: MSCPLRLB.pls 120.0.12020000.2 2012/10/30 21:37:59 cnazarma noship $
3
4 PROCEDURE validate_release(pid IN NUMBER,
5 p_return_status IN OUT NOCOPY number,
6 p_return_msg IN OUT NOCOPY varchar) IS
7
8 p_plan_id number := pid;
9 p_plan_inst_id number;
10 p_plan_org_id number;
11 p_compile_designator varchar2(80);
12 collection_status number;
13
14 cursor cur_plan_info is
15 select organization_id,sr_instance_id,compile_designator
16 from msc_plans
17 where plan_id=p_plan_id;
18
19 cursor check_coll_cur is
20 select nvl(mai.st_status,0)
21 from msc_apps_instances mai,
22 msc_coll_parameters mcp
23 where mai.instance_id = p_plan_inst_id
24 and mai.instance_id = mcp.instance_id
25 and nvl(mai.st_status,0) > 0
26 and ( mai.lrtype in ('C','P')
27 or ( mai.lrtype in ('T')
28 and ( ( (nvl(mcp.item,0) = 1 and nvl(mcp.item_sn_flag,0) in (1,4))
29 or ( nvl(mcp.customer,0) = 1 or nvl(mcp.supplier,0) = 1 ) )))) ;
30
31
32 begin
33 if pid is not null then
34
35 open cur_plan_info;
36 fetch cur_plan_info into p_plan_org_id,p_plan_inst_id, p_compile_designator;
37 if cur_plan_info%notfound then
38 p_return_status := 2;
39 p_return_msg := 'Could not find plan info in validate_release api ';
40 return;
41 end if;
42 close cur_plan_info;
43
44 open check_coll_cur;
45 fetch check_coll_cur into collection_status;
46 close check_coll_cur;
47
48 if nvl(collection_status,0) >0 then
49 fnd_message.set_name('MSC', 'MSC_COLL_RUN_WARN');
50 p_return_msg := fnd_message.get;
51 p_return_status := 2;
52 return;
53 else
54 p_return_status := 1; -- success
55 end if;
56
57 else
58 p_return_status := 2;
59 p_return_msg := 'Plan_id is null in validate_release plan_id = ' || p_plan_id || 'pid = ' || pid;
60 end if;
61
62
63
64 end validate_release;
65
66 PROCEDURE do_release_test(pid IN Number,
67 psid in number,
68 p_user_id in number,
69 p_resp_id in number,
70 p_appl_id in number,
71 p_query_id IN OUT nocopy number,
72 p_return_msg IN OUT nocopy varchar,
73 p_return_status IN OUT nocopy number) IS
74
75 l_query_id number ;
76 begin
77 p_return_status := 2;
78 fnd_message.set_name('MSC', 'MSC_COLL_RUN_WARN');
79 p_return_msg := fnd_message.get;
80 SELECT msc_form_query_s.nextval
81 INTO l_query_id
82 FROM dual;
83
84 /*
85 INSERT INTO msc_form_query(
86 query_id,
87 last_update_date,
88 last_updated_by,
89 creation_date,
90 created_by,
91 last_update_login,
92 number1,
93 number2,
94 number3)
95 VALUES(
96 l_query_id,
97 sysdate,
98 1,
99 sysdate,
100 1,
101 1,
102 102,
103 103,
104 104);
105
106 */
107 p_query_id := l_query_id;
108
109
110 end do_release_test;
111
112 PROCEDURE do_release(pid IN Number,psid in number,
113 p_user_id in number,
114 p_resp_id in number,
115 p_appl_id in number,
116 p_query_id IN OUT nocopy number,
117 p_return_msg IN OUT nocopy varchar,
118 p_return_status IN OUT nocopy number) IS
119
120 l_query_id NUMBER;
121 loaded_jobs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
122 loaded_reqs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
123 loaded_scheds MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
124 resched_jobs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
125 resched_reqs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
126 wip_group_id NUMBER;
127 wip_req_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
128 po_req_load_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
129 po_req_resched_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
130 release_instance MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
131 loaded_lot_jobs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
132 resched_lot_jobs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
133 osfm_req_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
134 eam_resched_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
135 eam_req_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
136 loaded_int_reqs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
137 resched_int_reqs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
138 int_req_load_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
139 int_req_resched_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
140 loaded_int_repair_orders MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
141 int_repair_orders_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
142 loaded_ext_repair_orders MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
143 ext_repair_orders_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
144 p_po_res_id msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
145 p_released_inst msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
146 p_po_res_count msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
147 p_po_pwb_count msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
148 p_so_rel_id msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
149 p_so_released_inst msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
150 p_so_rel_count msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
151 p_so_pwb_count msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
152 resched_cmro_jobs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
153 resched_cmro_jobs_req_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
154 cmro_loaded_jobs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
155 cmro_loaded_jobs_req_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
156 eam_loaded_jobs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
157
158 eam_loaded_jobs_req_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
159
160 user_id NUMBER :=nvl(p_user_id,1);
161 req_load_group_by NUMBER;
162 req_batch_number NUMBER;
163
164
165 i NUMBER;
166 v_res_po_count number :=0;
167 v_rel_so_count number :=0;
168 v_other_count number :=0;
169 collection_status NUMBER := 0;
170
171
172 request_id number;
173
174
175 p_doc_num NUMBER := 0;
176 p_doc_line_num NUMBER := 0;
177 p_doc_shipment_num NUMBER := 0;
178 x_return_status varchar2(1) := 'E';
179 tmpindex NUMBER := 0;
180 p_ret_msg varchar2(200) := null;
181 p_release_by_user varchar2(3):=null ;
182 -- nvl(FND_PROFILE.value('MSC_RELEASED_BY_USER_ONLY'),'N');
183
184
185 p_plan_id number := pid;
186 p_plan_org_id number;
187 p_plan_inst_id number;
188
189 p_org_id number ;
190 p_inst_id number;
191 p_compile_designator varchar2(20);
192
193 l_sr_instance_id number;
194
195 cursor cur_plan_info is
196 select organization_id,sr_instance_id,compile_designator
197 from msc_plans
198 where plan_id=p_plan_id;
199
200 cursor check_coll_cur is
201 select nvl(mai.st_status,0)
202 from msc_apps_instances mai,
203 msc_coll_parameters mcp
204 where mai.instance_id = p_plan_inst_id
205 and mai.instance_id = mcp.instance_id
206 and nvl(mai.st_status,0) > 0
207 and ( mai.lrtype in ('C','P')
208 or ( mai.lrtype in ('T')
209 and ( ( (nvl(mcp.item,0) = 1 and nvl(mcp.item_sn_flag,0) in
210 (1,4))
211 or ( nvl(mcp.customer,0) = 1 or nvl(mcp.supplier,0) = 1 ) ))
212 )) ;
213
214
215
216
217 CURSOR cur_supply_order IS
218 SELECT sum(decode(ms.load_type,20,0,1)),
219 sum(decode(ms.load_type,20,1,0))
220 FROM msc_supplies ms,
221 msc_apps_instances mai
222 where ms.plan_id = pid
223 and ms.release_errors is null
224 and ms.load_type is not null
225 and ms.sr_instance_id = mai.instance_id
226 and ms.last_updated_by = decode(p_release_by_user,'Y', user_id,
227 ms.last_updated_by)
228 and ms.status = 0
229 and ms.applied =2;
230
231 CURSOR cur_demand_order IS
232 -- SELECT 1
233 -- FROM msc_demands md
234 -- where md.plan_id = p_plan_id
235 -- and md.release_errors is null
236 -- and md.load_type =30
237 -- and MD.ORIGINATION_TYPE = 30
238 -- and md.last_updated_by = decode(p_release_by_user,'Y', user_id,
239 -- md.last_updated_by)
240 -- and md.status = 0
241 -- and md.applied =2;
242
243 select 1 from dual
244 where exists ( SELECT /*+ first_rows */ 1
245 FROM msc_demands md
246 where md.plan_id = p_plan_id
247 and md.release_errors is null
248 and md.load_type =30
249 and MD.ORIGINATION_TYPE = 30
250 and md.last_updated_by = decode(p_release_by_user,'Y', user_id,
251 md.last_updated_by)
252 and md.status = 0
253 and md.applied =2
254 );
255
256 CURSOR cur_instance IS
257 SELECT unique sr_instance_id
258 from MSC_PLAN_ORGANIZATIONS
259 where plan_id = p_plan_id;
260
261
262 BEGIN
263 -- before we have SSO enable, we temp hardcode the user id
264 fnd_global.apps_initialize(p_user_id, p_resp_id, p_appl_id);
265
266
267 -- msc_rel_wf.init_db('OPERATIONS');
268 -- get plan info
269 -----------------------------------------------------
270 open cur_plan_info;
271 fetch cur_plan_info into p_plan_org_id,p_plan_inst_id ,p_compile_designator;
272 if cur_plan_info%notfound then
273 return;
274
275 end if;
276 close cur_plan_info;
277
278
279
280 p_release_by_user :=
281 nvl(GET_RP_PLAN_PROFILE_VALUE(p_plan_id,'MSC_RELEASED_BY_USER_ONLY'),'N');
282
283 req_load_group_by := to_number(GET_RP_PLAN_PROFILE_VALUE(p_plan_id,'MRP_LOAD_REQ_GROUP_BY'));
284 if (req_load_group_by is null) then
285 msc_rel_wf.get_profile_value(p_profile_name => 'MRP_LOAD_REQ_GROUP_BY',
286 p_instance_id => p_inst_id,
287 p_profile_value => req_load_group_by);
288
289 end if;
290 -- print_debug('done getting profile MRP_LOAD_REQ_GROUP_BY='|| req_load_group_by );
291 req_load_group_by :=nvl(req_load_group_by,1);
292
293 /*
294 open check_coll_cur;
295 fetch check_coll_cur into collection_status;
296 close check_coll_cur;
297
298 if nvl(collection_status,0) >0 then
299 -- fnd_message.set_name('MSC', 'MSC_COLL_RUN_WARN');
300 p_return_status := 2;
301 return;
302 end if;
303
304 */
305
306
307 OPEN cur_supply_order;
308 FETCH cur_supply_order INTO
309 v_other_count,
310 v_res_po_count;
311 CLOSE cur_supply_order;
312
313 OPEN cur_demand_order;
314 FETCH cur_demand_order INTO v_rel_so_count;
315 CLOSE cur_demand_order;
316
317 v_rel_so_count := nvl(v_rel_so_count,0); --- rescheduled sales order
318 v_other_count := nvl(v_other_count,0); -- planned order, work order, schedule, po requsition
319 v_res_po_count := nvl(v_res_po_count,0); -- rescheduled/cancel po
320
321 print_debug('so=' || v_rel_so_count || ' other=' || v_other_count || ' po=' || v_res_po_count);
322
323
324 --- we should not release supplies while collection is running
325 --- we can check this before we call web service
326 --- move this part of code out
327
328
329
330 IF v_other_count >0 then
331 print_debug('releasing planed orders, etc ...');
332 MSC_Rel_Plan_PUB.msc_release_plan_sc
333 (pid,
334 p_plan_org_id, --- p_org_id,use to release one org only, if it is same as p_plan_org_id, then release all org
335 p_plan_inst_id, -- release by instance. if it is same as plan instance, then release all
336 p_plan_org_id,
337 p_plan_inst_id,
338 p_compile_designator,
339 user_id,
340 req_load_group_by,
341 req_batch_number,
342 wip_group_id,
343 loaded_jobs, --- load wip job
344 loaded_reqs, -- loaded purchasing req count
345 loaded_scheds, -- rescheduled purchasing req count
346 resched_jobs, -- reschedule job
347 resched_reqs, -- reschedule purchasing req
348 wip_req_id, -- wip request id
349 po_req_load_id, -- new purchasing req
350 po_req_resched_id, -- reschedule purchasing req
351 release_instance, -- instance id
352 null,
353 null,
354 loaded_lot_jobs, --loaded lots job
355 resched_lot_jobs, -- reschedule lots job
356 osfm_req_id, -- lots cp id
357 eam_resched_id,
358 eam_req_id,
359 loaded_int_reqs, --loaded internal req count
360 resched_int_reqs, --rescheduled internal req count
361 int_req_load_id, --- new internal requisition
362 int_req_resched_id, --- reschedule interna requision
363 loaded_int_repair_orders, --loaded int repair
364 int_repair_orders_id, -- int repair cp id
365 loaded_ext_repair_orders,
366 ext_repair_orders_id,
367 eam_loaded_jobs,
368 eam_loaded_jobs_req_id,
369 resched_cmro_jobs,
370 resched_cmro_jobs_req_id,
371 cmro_loaded_jobs,
372 cmro_loaded_jobs_req_id);
373
374
375
376 -- print_debug('Done releasing planed orders, etc for instance:' || p_plan_inst_id );
377 end if;
378
379
380 --- release purchasing order, which includes cancel and reschdule po
381 --- msc_rel_wf.reschedule_purchase_orders needs to be updated to report
382 --- request id and progress
383
384 if v_res_po_count > 0 then
385
386 print_debug('Releasing purchasing orders ...');
387 msc_rel_wf.reschedule_purchase_orders(
388 p_plan_id,
389 p_plan_org_id, -- p_org_id release all org order,
390 p_plan_inst_id, --p_inst_id release all instance order,
391 p_plan_org_id,
392 p_plan_inst_id,
393 v_res_po_count,
394 p_released_inst,
395 p_po_res_id,
396 p_po_res_count,
397 p_po_pwb_count);
398
399 -- print_debug('Done releasing purchasing orders ...');
400 end if;
401
402
403
404
405 ------release sales order
406 ---------------------------------------------------------------------
407 if v_rel_so_count > 0 then
408 -- print_debug('Releasing sales orders ...');
409 msc_rel_wf.release_sales_orders(
410 p_plan_id,
411 p_plan_org_id,
412 p_plan_inst_id,
413 p_plan_org_id,
414 p_plan_inst_id,
415 p_so_released_inst, -- instance count
416 p_so_rel_id, -- request id
417 p_so_rel_count, -- release order count
418 p_so_pwb_count); -- total order which reqire release
419
420 -- print_debug('Done release sales orders ...');
421 end if;
422
423
424
425 -- need to insert data into msc_form_query table so it
426 -- can be displayed to a user
427 if v_res_po_count + v_other_count + v_rel_so_count> 0 then
428
429 -- ----------------------------------------
430 -- Set all the field on the release results
431 -- window and go there.
432 -- ----------------------------------------
433 SELECT msc_form_query_s.nextval
434 INTO l_query_id
435 FROM dual;
436
437
438 if v_other_count > 0 then
439 FOR i in 1..loaded_jobs.COUNT LOOP
440 INSERT INTO msc_form_query(
441 query_id,
442 last_update_date,
443 last_updated_by,
444 creation_date,
445 created_by,
446 last_update_login,
447 number1,
448 number2,
449 number3,
450 number4,
451 number5,
452 number6,
453 number7,
454 number8,
455 number9,
456 number12,
457 char1,
458 char2,
459 char3,
460 char4,
461 char5,
462 char6,
463 char7,
464 char8,
465 number17,
466 number18,
467 number19,
468 number20,
469 char10,
470 char11)
471 VALUES(
472 l_query_id,
473 sysdate,
474 1,
475 sysdate,
476 1,
477 1,
478 loaded_jobs(i) + loaded_lot_jobs(i),
479 loaded_reqs(i),
480 loaded_scheds(i),
481 resched_jobs(i) + resched_lot_jobs(i),
482 resched_reqs(i),
483 wip_req_id(i),
484 po_req_load_id(i),
485 po_req_resched_id(i),
486 release_instance(i),
487 osfm_req_id(i),
488 loaded_int_reqs(i),
489 resched_int_reqs(i),
490 int_req_load_id(i),
491 int_req_resched_id(i),
492 loaded_int_repair_orders(i),
493 int_repair_orders_id(i),
494 loaded_ext_repair_orders(i),
495 ext_repair_orders_id(i),
496 eam_loaded_jobs(i),
497 eam_loaded_jobs_req_id(i),
498 resched_cmro_jobs(i),
499 resched_cmro_jobs_req_id(i),
500 cmro_loaded_jobs(i),
501 cmro_loaded_jobs_req_id(i));
502
503
504 END LOOP;
505 end if;
506
507
508
509 if v_res_po_count > 0 then
510 FOR i in 1..v_res_po_count LOOP
511 INSERT INTO msc_form_query(
512 query_id,
513 last_update_date,
514 last_updated_by,
515 creation_date,
516 created_by,
517 last_update_login,
518 number13,
519 number11,
520 number10,
521 number9)
522 VALUES(
523 l_query_id,
524 sysdate,
525 1,
526 sysdate,
527 1,
528 1,
529 p_po_pwb_count(i),
530 p_po_res_count(i),
531 p_po_res_id(i),
532 p_released_inst(i));
533
534 END LOOP;
535 end if;
536
537
538
539
540 if v_rel_so_count > 0 then
541 FOR i in 1..p_so_rel_id.count -1 LOOP
542 INSERT INTO msc_form_query(
543 query_id,
544 last_update_date,
545 last_updated_by,
546 creation_date,
547 created_by,
548 last_update_login,
549 number16,
550 number15,
551 number14,
552 number9)
553 VALUES(
554 l_query_id,
555 sysdate,
556 1,
557 sysdate,
558 1,
559 1,
560 p_so_pwb_count(i),
561 p_so_rel_count(i),
562 p_so_rel_id(i),
563 p_so_released_inst(i));
564 END LOOP;
565 end if;
566
567 else -- no data is selected for release
568 p_return_status := 3; -- no data is selected for release
569 p_return_msg := 'No data has been selected for release, nothing to release';
570 END IF;
571
572 p_query_id := l_query_id;
573
574 -- print_debug('All done ...');
575 EXCEPTION
576 WHEN OTHERS THEN
577
578 fnd_message.set_name('MSC', 'MSC_REL_SETUP_ERR');
579 fnd_message.set_token('ERROR_MESSAGE', sqlerrm);
580 raise_application_error(-20001,sqlerrm);
581 p_return_status := 2;
582 p_return_msg := fnd_message.get;
583 -- commit;
584 END do_release;
585
586
587
588
589 FUNCTION get_instance_release_status(p_sr_instance_id in number) return number IS
590
591
592 l_allow_release_flag number;
593 l_instance_code varchar2(10);
594 cursor cur_release_flag is
595 select decode(apps_ver,3,nvl(allow_release_flag ,2)
596 ,4,nvl(allow_release_flag ,2)
597 ,1) allow_release_flag,
598 instance_code
599 from msc_apps_instances
600 where instance_id = nvl(p_sr_instance_id,-1);
601 begin
602 open cur_release_flag;
603 fetch cur_release_flag
604 into l_allow_release_flag,l_instance_code;
605 if cur_release_flag%notfound then
606 l_allow_release_flag := 2;
607 end if;
608 close cur_release_flag;
609 return( l_allow_release_flag) ;
610 end get_instance_release_status;
611
612
613 FUNCTION get_implement_dock_date(p_plan_id in number,
614 p_inst_id in number,
615 p_org_id in number,
616 p_item_id in number,
617 p_receiving_calendar in varchar2,
618 p_implement_date in date) return date IS
619 ln_pp_lead_time NUMBER;
620 lv_date DATE;
621 CURSOR CPPL IS
622 SELECT nvl(postprocessing_lead_time, 0)
623 FROM msc_system_items
624 WHERE plan_id = p_plan_id
625 AND sr_instance_id = p_inst_id
626 AND ORGANIZATION_ID = p_org_id
627 AND INVENTORY_ITEM_ID = p_item_id;
628
629 BEGIN
630
631 OPEN CPPL;
632 FETCH CPPL INTO ln_pp_lead_time;
633 CLOSE CPPL;
634 -- preserve the time stamps and calculate
635 -- the date after offsetting
636 -- the lead time from the implement_date
637 lv_date := p_implement_date -
638 (trunc(p_implement_Date)-
639 trunc(msc_calendar.date_offset(p_org_id,p_inst_id,1,p_implement_Date,
640 -1 * ln_pp_lead_time)));
641
642 --if the Receiving calendar is not null
643 --validate the date against the Receiving Calendar
644 --else validate the date against the
645 -- Org Manufacturing Calendar
646 if p_receiving_calendar is not null then
647 lv_date := lv_date - (trunc(lv_date) - trunc(msc_calendar.PREV_WORK_DAY(p_receiving_calendar,p_inst_id, trunc(lv_date))));
648 else
649 lv_date := msc_calendar.PREV_WORK_DAY(p_org_id,p_inst_id,1,trunc(lv_date));
650 end if;
651 return greatest(trunc(sysdate),lv_date);
652 exception when others then
653 return null;
654 END get_implement_dock_date;
655
656
657
658
659 FUNCTION get_implement_ship_date(p_plan_id in number,
660 p_inst_id in number,
661 p_org_id in number,
662 p_order_type in number,
663 p_source_sr_instance_id in number,
664 p_source_org_id in number,
665 p_sourcre_vendor_site_id in number,
666 p_ship_method in varchar2,
667 p_intransit_calendar in varchar2,
668 p_ship_calendar in varchar2,
669 p_implement_dock_date in date,
670 p_source_table in varchar2) return date
671 IS
672 ln_intransit_lead_time NUMBER := 0;
673 ln_tp_arranged_by NUMBER := 1;
674 lv_date DATE;
675 x_intransit_lead_time NUMBER;
676 x_return_status VARCHAR2(30);
677
678
679 l_session_id NUMBER := 0;
680 l_src_org_id NUMBER := p_source_org_id;
681 l_src_vendor_site_id NUMBER := p_sourcre_vendor_site_id;
682 l_src_sr_inst_id NUMBER := p_source_sr_instance_id;
683 l_org_id NUMBER := p_org_id;
684 l_sr_inst_id NUMBER := p_inst_id;
685 l_ship_method varchar2(30) := p_ship_method;
686
687 BEGIN
688 IF (p_order_type in (1,2,5)
689 AND p_SOURCE_TABLE = 'MSC_SUPPLIES') THEN
690
691 --- get the unique number identifying the current session
692 SELECT MRP.mrp_atp_schedule_temp_s.nextval
693 INTO l_session_id
694 FROM dual;
695
696 -- Call ATP method to get the intransit lead time value in
697 -- the out parameter x_intransit_lead_time
698 MSC_ATP_PROC.ATP_Intransit_LT(
699 2, l_session_id,
700 l_src_org_id,
701 null,
702 l_src_vendor_site_id,
703 l_src_sr_inst_id,
704 l_org_id,
705 null,
706 null,
707 l_sr_inst_id,
708 l_ship_method,
709 x_intransit_lead_time,
710 x_return_status);
711 -- preserve the time stamps and calculate the date
712 -- after offsetting the lead time from the
713 --implement_dock_date
714 lv_date := p_implement_Dock_Date-
715 (trunc(p_implement_dock_date)-
716 trunc(msc_calendar.date_offset(p_org_id,p_inst_id,1,
717 p_implement_dock_date,-1 * x_intransit_lead_time)));
718 -- if intransit calendar is not null then validate the
719 -- date against the intransit calendar
720 -- else validate the date against the shipping calendar.
721 if p_intransit_calendar is not null then
722 lv_date := lv_date - (trunc(lv_date) -
723 trunc(msc_calendar.PREV_WORK_DAY(p_intransit_calendar, p_inst_id, trunc(lv_date))));
724 else
725 lv_date := lv_date - (trunc(lv_date) -
726 trunc(msc_calendar.PREV_WORK_DAY(p_ship_calendar, p_inst_id, trunc(lv_date))));
727 end if;
728 return greatest(trunc(sysdate),lv_date);
729 else
730 return null;
731 END IF;
732 exception when others then
733 return null;
734 END get_implement_ship_date;
735
736
737 FUNCTION GET_WIP_JOB_PREFIX(p_instance_id in number) return varchar2 is
738
739 l_seq_num number;
740 lv_job_prefix varchar2(40);
741
742 begin
743 l_seq_num := msc_rel_wf.get_job_seq_from_source(p_instance_id);
744
745
746
747 msc_rel_wf.get_profile_value(p_profile_name => 'WIP_JOB_PREFIX',
748 p_instance_id =>p_instance_id,
749 p_profile_value => lv_job_prefix);
750 return lv_job_prefix||to_char(l_seq_num);
751 END GET_WIP_JOB_PREFIX;
752
753
754
755
756 Function get_Imp_Employee_id(
757 p_plan_id in number,
758 p_org_id in number,
759 p_inst_id in number,
760 p_item_id in number,
761 p_planner_code in varchar2) return number is
762
763
764
765
766 CURSOR C1 IS
767 SELECT employee_id
768 FROM msc_planners
769 WHERE planner_code = p_planner_code
770 AND organization_id = p_org_id
771 AND sr_instance_id = p_inst_id
772 AND current_employee_flag = 1;
773
774 CURSOR C2 IS
775 SELECT employee_id
776 FROM msc_planners mp,
777 msc_system_items msi
778 WHERE mp.planner_code = msi.planner_code
779 AND mp.organization_id = msi.organization_id
780 AND mp.sr_instance_id = msi.sr_instance_id
781 AND mp.current_employee_flag = 1
782 AND msi.plan_id = p_plan_id
783 AND msi.organization_id = p_org_id
784 AND msi.sr_instance_id = p_inst_id
785 AND msi.inventory_item_id = p_item_id;
786
787 l_employee_id NUMBER;
788
789 BEGIN
790 if p_planner_code is not null then
791
792
793 OPEN C1;
794
795 FETCH C1 INTO l_employee_id;
796 CLOSE C1;
797 else
798 OPEN C2;
799
800 FETCH C2 INTO l_employee_id;
801 CLOSE C2;
802
803 END IF;
804 return l_employee_id;
805
806 end get_Imp_Employee_id;
807
808
809
810 FUNCTION Check_Source_Supp_Org (
811 p_inst_id in number,
812 p_org_id in number) return Number is
813
814 l_count NUMBER;
815
816 CURSOR C1 IS
817 SELECT count(1)
818 FROM msc_trading_partners
819 WHERE sr_tp_id = p_org_id
820 AND sr_instance_id = p_inst_id
821 AND (modeled_customer_id is not null
822 OR modeled_supplier_id is not null);
823
824 BEGIN
825
826 OPEN C1;
827 FETCH C1 INTO l_count;
828 CLOSE C1;
829
830 if (l_count = 0) then
831
832 RETURN 2; -- false;
833 else
834 RETURN 1; --- true;
835 end if;
836
837 End Check_Source_Supp_Org;
838
839
840 FUNCTION validate_order_for_release(
841 p_plan_id in number,
842 p_inst_id in number,
843 p_org_id in number,
844 p_org_code in varchar2,
845 p_item_id in number,
846 p_vmi in number,
847 p_source_Table in varchar2,
848 p_transaction_id in number,
849 p_order_type in number,
850 p_source_org_id in number,
851 P_bom_item_type in number,
852 p_release_time_fence_code in number,
853 p_in_source_plan in number,
854 p_build_in_wip_flag in number,
855 p_purchasing_enabled_flag in number,
856 p_planning_make_buy_code in number,
857 p_planner_code in varchar2,
858 p_implement_alternate_routing in varchar2,
859 p_user_id in number,
860 p_resp_id in number,
861 p_appl_id in number
862 ) return varchar2 IS
863
864
865
866
867 p_valid number;
868 p_rel_schd_OK varchar2(3);
869 p_rel_error varchar2(1024);
870 l_instance_Code varchar2(10);
871 l_def_pref_id number;
872 l_pref_release_vmi varchar2(10);
873 l_include_so varchar2(10);
874 l_temp Number;
875
876 l_user_id number := nvl(FND_PROFILE.VALUE('USER_ID'),1);
877 l_plan_type number :=1 ; -- temp set as ASCP plan
878
879 CURSOR empl_C IS
880 SELECT mp.employee_id
881 FROM msc_planners mp
882 WHERE mp.planner_code = p_planner_code
883 AND mp.organization_id = p_org_id
884 AND mp.sr_instance_id = p_inst_id
885 AND mp.current_employee_flag = 1;
886
887 CURSOR loc_C IS
888 select mtps.sr_tp_site_id
889 from msc_trading_partners mtp,
890 msc_trading_partner_sites mtps
891 where mtp.sr_tp_id = p_org_id
892 AND mtp.sr_instance_id = p_inst_id
893 AND mtp.partner_type =3
894 AND mtps.partner_id = mtp.partner_id;
895
896 cursor is_so_line_split_c is
897 select nvl(sales_order_line_split, 2) from msc_demands dem
898 where dem.demand_id=p_transaction_id
899 and p_source_table='MSC_DEMANDS'
900 and dem.plan_id=p_plan_id
901 and dem.sr_instance_id=p_inst_id
902 and dem.organization_id=P_org_id;
903
904 l_so_line number :=0;
905
906 BEGIN
907 /* we should not release vmi items*/
908 fnd_global.apps_initialize(p_user_id, p_resp_id, p_appl_id);
909 -- msc_rel_wf.init_db('OPERATIONS');
910 l_def_pref_id := msc_get_name.get_default_pref_id(l_user_id);
911 l_pref_release_vmi:= msc_get_name.GET_preference('ORDERS_RELEASE_VMI_ITEMS', l_def_pref_id, l_plan_type);
912 -- l_include_so:= msc_get_name.GET_preference('INCLUDE_SO', l_def_pref_id, l_plan_type);
913 l_include_so:= 'Y';
914
915 IF l_pref_release_vmi = 'N' and nvl(p_vmi,2) = 1 then
916 p_rel_error :='Cannot release this as the preference Release VMI Items is set to No';
917 return p_rel_error;
918 END IF;
919
920
921
922 /* if it is supply and order type not in (1,2,3,5,13) */
923 if (p_source_table='MSC_SUPPLIES' AND
924 p_order_type NOT IN (1, 2, 3, 5, 13) )then
925 p_rel_error :=FND_MESSAGE.GET_STRING('MSC', 'MSC_WB_RELEASE_3');
926 return p_rel_error;
927 END IF;
928
929
930
931 /* if the instance is not release enabled */
932 if (get_instance_release_status(p_inst_id) = 2) then
933 l_instance_code := substr(p_org_code,1,instr(p_org_code,':',-1)-1);
934 fnd_message.set_name('MSC','MSC_RP_ALLOW_RELEASE_INSTANCE');
935 fnd_message.set_token('INSTANCE',l_instance_code);
936 p_rel_error :=fnd_message.get;
937 return p_rel_error;
938 end if;
939
940
941 /* if source db is not running */
942 if not (msc_rel_wf.is_source_db_up(p_inst_id)) then
943 p_rel_error :=FND_MESSAGE.GET_STRING('MSC', 'MSC_SOURCE_IS_INVALID');
944 return p_rel_error;
945 end if;
946
947
948 /* if plan option does not allow to release sales order */
949 if (p_order_type=30) and (l_include_so='N') then
950 p_rel_error :=FND_MESSAGE.GET_STRING('MSC', 'MSC_WB_RELEASE1');
951 return p_rel_error;
952 end if;
953
954
955 /* model/option classes can not be released */
956 if (p_bom_item_type in (1, 2, 3, 5) )then
957 p_rel_error := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_MODEL');
958 return p_rel_error;
959 end if;
960
961
962 /* Kanban item can not be released */
963 if (p_order_type=5 and p_source_table='MSC_SUPPLIES' and p_release_time_fence_code = 6) then
964 p_rel_error := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_KANBAN');
965 return p_rel_error;
966 end if;
967
968 /* can not release planned order as flow schedule */
969 /* what if it is new planned order where
970 /* engine has not set cfm flag yet ??? */
971 if ( p_order_type = 5) then
972
973 l_temp := msc_get_name.check_cfm(
974 p_plan_id,
975 p_org_id,
976 p_inst_id,
977 p_item_id,
978 p_transaction_id,
979 p_implement_alternate_routing);
980 if l_temp = 1 then
981 p_rel_error := FND_MESSAGE.GET_STRING('MSC', 'MSC_NO_FLOW_SCHEDULE');
982 return p_rel_error;
983 end if;
984 end if;
985
986
987
988 /* not able to release sales order */
989 if (p_source_table ='MSC_DEMANDS' and p_order_type=30) then
990 p_rel_error := msc_rel_wf.verify_so_release(p_plan_id,p_transaction_id,p_inst_id);
991 if (p_rel_error is not null) then
992 return p_rel_error;
993 end if;
994
995 end if;
996
997
998
999
1000
1001 if p_in_source_plan = 1 then
1002 p_rel_schd_OK := nvl(fnd_profile.value('MSC_DRP_RELEASE_FROM_MRP'),'N');
1003
1004 if (p_rel_schd_OK = 'N') then
1005
1006 p_rel_error := FND_MESSAGE.GET_STRING('MRP', 'MSC_IN_SOURCE_PLAN');
1007 return p_rel_error;
1008 end if;
1009 end if; -- if p_in_source_plan = 1 then
1010
1011
1012
1013
1014
1015 /* -- 4417550, make planned order can be released only when
1016 -- child supply are on hand or make planned order
1017 */
1018
1019 p_valid := null;
1020 if (nvl(FND_PROFILE.VALUE('MSC_REL_ONLY_ONHAND_SUPPLY'),'N') = 'Y'
1021 and p_order_type = 5 and p_org_id = p_source_org_id) then
1022 p_valid := MSC_SELECT_ALL_FOR_RELEASE_PUB.child_supplies_onhand(
1023 p_plan_id, p_transaction_id);
1024 if p_valid > 0 then
1025 p_rel_error := FND_MESSAGE.GET_STRING('MSC', 'MSC_REL_ONLY_ONHAND_WARN2');
1026 return p_rel_error;
1027 end if;
1028
1029 end if;
1030
1031 -- additional check to ensure for so line split, release is not allowed.
1032 if p_source_table='MSC_DEMANDS' and p_order_type=30 then
1033 open is_so_line_split_c;
1034 fetch is_so_line_split_c into l_so_line;
1035 close is_so_line_split_c;
1036 if l_so_line = 1 then
1037 p_rel_error := 'Sales order line split cannot be released';
1038 end if;
1039 end if;
1040 return p_rel_error;
1041
1042 END validate_order_for_release;
1043
1044
1045 FUNCTION GET_ACTION (arg_source_table IN VARCHAR2,
1046 arg_plan_id IN NUMBER ,
1047 arg_sr_instance_id in number,
1048 arg_org_id in number,
1049 arg_item_id in number,
1050 arg_bom_item_type IN NUMBER ,
1051 arg_base_item_id IN NUMBER,
1052 arg_wip_supply_type IN NUMBER ,
1053 arg_order_type IN NUMBER ,
1054 arg_rescheduled_flag IN NUMBER,
1055 arg_disposition_status_type IN NUMBER ,
1056 arg_new_due_date IN DATE ,
1057 arg_old_due_date IN DATE ,
1058 arg_implemented_quantity IN NUMBER ,
1059 arg_quantity_in_process IN NUMBER ,
1060 arg_quantity_rate IN NUMBER ,
1061 arg_release_time_fence_code IN NUMBER ,
1062 arg_reschedule_days IN NUMBER ,
1063 arg_firm_quantity IN NUMBER ,
1064 arg_mrp_planning_code IN NUMBER,
1065 arg_lots_exist IN NUMBER
1066 ) RETURN varchar2 is
1067
1068
1069 cursor crit_component IS
1070 select critical_component_flag
1071 from msc_system_items
1072 where plan_id = arg_plan_id
1073 and inventory_item_id = arg_item_id
1074 and organization_id = arg_org_id
1075 and sr_instance_id = arg_sr_instance_id;
1076
1077 l_critical_component number;
1078 l_action VARCHAR2(20);
1079 BEGIN
1080 open crit_component;
1081 fetch crit_component into l_critical_component;
1082 close crit_component;
1083 l_action := msc_get_name.action(arg_source_table,
1084 arg_bom_item_type,
1085 arg_base_item_id,
1086 arg_wip_supply_type,
1087 arg_order_type,
1088 arg_rescheduled_flag,
1089 arg_disposition_status_type,
1090 arg_new_due_date,
1091 arg_old_due_date,
1092 arg_implemented_quantity,
1093 arg_quantity_in_process,
1094 arg_quantity_rate,
1095 arg_release_time_fence_code,
1096 arg_reschedule_days,
1097 arg_firm_quantity,
1098 arg_plan_id,
1099 nvl(l_critical_component,2),
1100 arg_mrp_planning_code,
1101 arg_lots_exist);
1102 return l_action;
1103
1104 END GET_ACTION;
1105
1106 FUNCTION get_Implement_Location_Id(p_inst_id in number,
1107 p_org_id in number) return number is
1108
1109
1110
1111 CURSOR C1 IS
1112 SELECT partner_id
1113 FROM msc_trading_partners
1114 WHERE partner_type = 3
1115 AND sr_tp_id = p_org_id
1116 AND sr_instance_id = p_inst_id;
1117
1118 l_partner_id NUMBER;
1119
1120 CURSOR C2 IS
1121 SELECT s.sr_tp_site_id, s.location
1122 FROM msc_trading_partner_sites s
1123 where s.partner_id = l_partner_id;
1124
1125 l_loc_id NUMBER;
1126 l_loc_code varchar2(80);
1127
1128 BEGIN
1129
1130 OPEN C1;
1131 FETCH C1 INTO l_partner_id;
1132 CLOSE C1;
1133
1134 OPEN C2;
1135 FETCH C2 INTO l_loc_id, l_loc_code;
1136
1137 return l_loc_id;
1138
1139 END get_Implement_Location_Id;
1140
1141
1142 FUNCTION GET_IMPLEMENT_WIP_CLASS_CODE(
1143 p_plan_id in number,
1144 p_instance_id in number,
1145 p_org_id in number,
1146 p_item_id in number,
1147 p_transaction_id in number,
1148 p_order_type in number,
1149 p_project_id in number,
1150 p_implement_project_id in number,
1151 p_implement_as in number,
1152 p_implement_alternate_routing in varchar2) return varchar2 is
1153
1154
1155 l_def_pref_id Number;
1156 l_option_job_code varchar2(20):=null;
1157 v_temp number;
1158 v_sr_item number;
1159 l_wip_class_code varchar2(20);
1160 l_plan_type number:=1; --- temp set as ASCP plan
1161 v_project_id number;
1162 CURSOR sr_item_cur IS
1163 select sr_inventory_item_id
1164 from msc_system_items
1165 where plan_id = p_plan_id
1166 and organization_id = p_org_id
1167 and sr_instance_id = p_instance_id
1168 and inventory_item_id = p_item_id;
1169
1170 BEGIN
1171
1172
1173 if (p_order_type=5 and p_implement_as=3) then
1174 -- in rp there is no user preference value
1175 -- l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
1176 --l_option_job_code:= msc_get_name.GET_preference('ORDERS_JOB_CLASS_CODE', l_def_pref_id, l_plan_type);
1177
1178 if (l_option_job_code is not null) then
1179 l_wip_class_code :=l_option_job_code;
1180 else
1181 v_temp := msc_get_name.check_cfm(
1182 p_plan_id,
1183 p_org_id,
1184 p_instance_id,
1185 p_item_id,
1186 p_transaction_id,
1187 p_implement_alternate_routing);
1188 if nvl(v_temp,-1) <> 3 then
1189 if p_implement_project_id is NULL THEN
1190 v_project_id := p_project_id;
1191 ELSE
1192 v_project_id := p_implement_project_id;
1193 END IF;
1194
1195 OPEN sr_item_cur;
1196 FETCH sr_item_cur INTO v_sr_item;
1197 CLOSE sr_item_cur;
1198
1199 l_wip_class_code := msc_rel_wf.get_acc_class_from_source(
1200 p_org_id,
1201 v_sr_item,
1202 v_project_id,
1203 p_instance_id);
1204
1205 END IF;
1206
1207 END IF;
1208 END IF;
1209 return l_wip_class_code ;
1210 END GET_IMPLEMENT_WIP_CLASS_CODE;
1211
1212 PROCEDURE PRINT_DEBUG(MSG IN VARCHAR2) IS
1213
1214 BEGIN
1215 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1216 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1217 'MSC_PLAN_RELEASE', MSG);
1218 ELSE
1219 --dbms_output.PUT_LINE(MSG);
1220 --dbms_output.new_line();
1221 null;
1222 END If;
1223
1224 END PRINT_DEBUG;
1225
1226 procedure validate_icx_session(p_icx_cookie in varchar2,p_function in varchar2 DEFAULT NULL) is
1227 l_function varchar2(100) :=nvl(p_function,'SIM_WORKBENCH');
1228 SECURITY_CONTEXT_INVALID exception;
1229
1230 begin
1231 app_session.validate_icx_session(p_icx_cookie);
1232 if fnd_function.test(l_function) then --- SIM_WORKBENCH
1233 return;
1234 else
1235 raise SECURITY_CONTEXT_INVALID;
1236 end if;
1237 exception
1238 when others then
1239 raise SECURITY_CONTEXT_INVALID;
1240 end validate_icx_session;
1241
1242 FUNCTION GET_RP_PLAN_PROFILE_VALUE(P_PLAN_ID IN NUMBER,
1243 P_PROFILE_CODE IN VARCHAR2) RETURN VARCHAR2
1244
1245 IS
1246
1247 cursor cur_plan_profile is
1248 select profile_value
1249 from msc_plan_profiles
1250 where plan_id=p_plan_id
1251 and profile_code=p_profile_code;
1252
1253 x_profile_value varchar2(240):=null;
1254
1255 BEGIN
1256
1257 open cur_plan_profile;
1258 fetch cur_plan_profile into x_profile_value;
1259 if cur_plan_profile%notfound then
1260 return null;
1261
1262 end if;
1263 close cur_plan_profile;
1264 return x_profile_value;
1265
1266 END GET_RP_PLAN_PROFILE_VALUE;
1267
1268 Function GET_REQUEST_STATUS (
1269 request_id IN OUT nocopy number,
1270 application IN varchar2 default NULL,
1271 program IN varchar2 default NULL,
1272 phase OUT nocopy varchar2 ,
1273 status OUT nocopy varchar2 ,
1274 dev_phase OUT nocopy varchar2,
1275 dev_status OUT nocopy varchar2,
1276 message OUT nocopy varchar2) return number
1277 IS
1278 l_ret_st Number;
1279 begin
1280 if (FND_CONCURRENT.GET_REQUEST_STATUS(request_id,
1281 application ,
1282 program ,
1283 phase ,
1284 status ,
1285 dev_phase ,
1286 dev_status ,
1287 message ) ) then
1288 return 0;
1289 else
1290 return -1;
1291 end if;
1292 end GET_REQUEST_STATUS;
1293
1294
1295 Function test_permission(pname in varchar2) return number is
1296 SECURITY_CONTEXT_INVALID exception;
1297
1298 begin
1299 if fnd_function.test(pname) then
1300 return 1;
1301 else
1302 return 2 ;
1303 end if;
1304 exception
1305 when others then
1306 return 2;
1307 end test_permission;
1308
1309
1310
1311 Function save_user_profile(name in varchar2, value in varchar2) return number
1312 is
1313 begin
1314 if fnd_profile.save_user(name,value) then
1315 return 1;
1316 else
1317 return 2 ;
1318 end if;
1319 exception
1320 when others then
1321 return 2;
1322 end save_user_profile;
1323
1324 procedure get_exec_name(MSO_INSTALLED in varchar2, BATCH_REPLAN in varchar2, p_exec_name out nocopy varchar2) IS
1325 l_prof_value number;
1326 begin
1327 l_prof_value := to_number(fnd_profile.value('MSC_PLANNER_PLATFORM'));
1328 if l_prof_value is null then
1329 -- profile value not defined -> use old executables
1330 if MSO_INSTALLED ='Y' and BATCH_REPLAN = 'Y' then
1331 p_exec_name := 'MSONCP';
1332 elsif MSO_INSTALLED ='N' and BATCH_REPLAN = 'Y' then
1333 p_exec_name := 'MSCNCP';
1334 end if;
1335 else
1336 if MSO_INSTALLED ='Y' then
1337 if BATCH_REPLAN = 'Y' then
1338 if l_prof_value = 1 then
1339 p_exec_name := 'MSONCPS64';
1340 elsif l_prof_value = 2 then
1341 p_exec_name := 'MSONCPH64';
1342 elsif l_prof_value = 3 then
1343 p_exec_name := 'MSONCPA64';
1344 elsif l_prof_value = 4 then
1345 p_exec_name := 'MSONCPL64';
1346 elsif l_prof_value = 5 then
1347 p_exec_name := 'MSONCPHPIA64';
1348 end if;
1349 end if;
1350 else
1351 if BATCH_REPLAN = 'Y' then
1352 if l_prof_value = 1 then
1353 p_exec_name := 'MSCNCPS64';
1354 elsif l_prof_value = 2 then
1355 p_exec_name := 'MSCNCPH64';
1356 elsif l_prof_value = 3 then
1357 p_exec_name := 'MSCNCPA64';
1358 elsif l_prof_value = 4 then
1359 p_exec_name := 'MSCNCPL64';
1360 elsif l_prof_value = 5 then
1361 p_exec_name := 'MSCNCPHPIA64';
1362 end if;
1363 end if;
1364 end if;
1365 end if;
1366 end get_exec_name;
1367
1368 PROCEDURE batch_replan_launch(p_plan_id varchar2, l_request_id out nocopy varchar2) is
1369
1370 l_executable_name varchar2(30);
1371 l_mso_status boolean;
1372 l_status VARCHAR2(30);
1373 l_industry VARCHAR2(30);
1374 l_schema VARCHAR2(30);
1375 BEGIN
1376 l_request_id := -1;
1377 l_mso_status := fnd_installation.get_app_info('MSO',l_status,l_industry,l_schema);
1378 if l_mso_status <> true then
1379 l_request_id := -1;
1380 return;
1381 end if;
1382
1383 if l_status = 'I' then
1384 get_exec_name('Y','Y', l_executable_name);
1385 if l_executable_name = '-1' then
1386 l_request_id := -1;
1387 return;
1388 end if;
1389
1390 l_request_id := fnd_request.submit_request('MSO', l_executable_name, '', '', FALSE, p_plan_id, '0', '2','2', chr(0) );
1391 commit;
1392
1393 if (l_request_id = 0) then
1394 l_request_id := -1;
1395 end if;
1396 return;
1397 else --if l_status = 'I' then
1398 get_exec_name('N','Y', l_executable_name);
1399 if l_executable_name = '-1' then
1400 l_request_id := -1;
1401 return;
1402 end if;
1403
1404 l_request_id := fnd_request.submit_request('MSC', l_executable_name, '', '', FALSE, p_plan_id, '0', '2','2', chr(0) );
1405 commit;
1406
1407 if (l_request_id = 0) then
1408 l_request_id := -1;
1409 end if;
1410 return;
1411 end if; --if l_status = 'I' then
1412 END batch_replan_launch;
1413
1414 END MSC_PLAN_RELEASE;