[Home] [Help]
PACKAGE BODY: APPS.MSC_X_CP_FLOW
Source
1 PACKAGE BODY MSC_X_CP_FLOW AS
2 /* $Header: MSCXCPFB.pls 120.1 2005/08/05 03:08:35 pragarwa noship $ */
3
4
5 PROCEDURE Start_SCEM_Engine_WF
6 IS
7 l_wf_type VARCHAR2(8) := 'MSCXSCEM';
8 l_wf_key VARCHAR2(240);
9 l_wf_process VARCHAR2(30);
10 l_sequence_id NUMBER;
11
12 BEGIN
13
14 SELECT 'CP_FLOW-' || TO_CHAR(msc_sup_dem_entries_s.nextval)
15 INTO l_wf_key FROM DUAL;
16
20
17 print_user_info('Workflow Key is: ' || l_wf_key);
18
19 l_wf_process := 'START_SCEM_ENGINE';
21 -- create a Workflow process for the (item/org/supplier)
22 wf_engine.CreateProcess
23 ( itemtype => l_wf_type
24 , itemkey => l_wf_key
25 , process => l_wf_process
26 );
27
28 -- start Workflow process
29 wf_engine.StartProcess
30 ( itemtype => l_wf_type
31 , itemkey => l_wf_key
32 );
33
34 EXCEPTION
35 WHEN OTHERS THEN
36 raise;
37 END Start_SCEM_Engine_WF;
38
39 -- This procedure will lanuch the SCEM engine
40 PROCEDURE Launch_SCEM_Engine
41 ( itemtype in varchar2
42 , itemkey in varchar2
43 , actid in number
44 , funcmode in varchar2
45 , resultout out nocopy varchar2
46 ) IS
47
48 l_result BOOLEAN;
49 l_request_id NUMBER;
50
51 BEGIN
52
53 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
54 l_result := FND_REQUEST.SET_MODE(TRUE);
55 l_request_id := NULL;
56 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
57 'MSC', -- application
58 'MSCXNETG',-- program
59 NULL, -- description
60 NULL, -- start_time
61 FALSE, -- sub_request
62 'N',
63 'N',
64 'N',
65 'N',
66 'N',
67 'Y',
68 'Y',
69 'N',
70 'N',
71 'N',
72 'N'
73 );
74
75 commit;
76
77 print_user_info('Supply Chain Event Manager (SCEM) engine launched with concurrent request id ' || l_request_id);
78 -- MSC_SCE_LOADS_PKG.LOG_MESSAGE('SCEM engine launched with concurrent request id ' || l_request_id);
79 /*
80 MSC_X_NETTING_PKG.LAUNCH_ENGINE (
81 p_errbuf => l_errbuf,
82 p_retcode => l_retcode,
83 p_early_order => 'N',
84 p_changed_order => 'N',
85 p_forecast_accuracy => 'N',
86 p_forecast_mismatch => 'N',
87 p_late_order => 'N',
88 p_material_excess => 'Y',
89 p_material_shortage => 'Y',
90 p_performance => 'N',
91 p_potential_late_order => 'N',
92 p_response_required => 'N',
93 p_custom_exception => 'N'
94 );
95 */
96 EXCEPTION
97 WHEN OTHERS THEN
98 raise;
99 END Launch_SCEM_Engine;
100
101 PROCEDURE Start_DP_Receive_Forecast_WF
102 ( p_customer_id in number
103 , p_horizon_start in date
104 , p_horizon_days in number
105 , p_resp_key IN varchar2
106 , p_message_to_tp IN VARCHAR2
107 , p_tp_name IN VARCHAR2
108 ) IS
109 l_wf_type VARCHAR2(8) := 'MSCXDPRF';
110 l_wf_key VARCHAR2(240);
111 l_wf_process VARCHAR2(30);
112 l_sequence_id NUMBER;
113
114 l_tp_role_name VARCHAR2(320);
115 l_tp_role_display_name VARCHAR2(360);
116 l_tp_role_existing NUMBER;
117
118 -- get the seller(supplier) name
119 CURSOR c_tp_name(
120 p_resp_key varchar2
121 ) IS
122 SELECT fu.user_name name
123 FROM fnd_responsibility resp
124 , fnd_user_resp_groups furg
125 , msc_company_users mcu
126 , fnd_user fu
127 WHERE resp.responsibility_key = p_resp_key
128 AND furg.responsibility_id = resp.responsibility_id
129 and furg.user_id = mcu.user_id
130 and mcu.user_id = fu.user_id
131 and mcu.company_id = 1
132 ;
133
134 -- check if Workflow role already exists
135 CURSOR c_wf_role_existing(
136 p_role_name IN VARCHAR2
137 ) IS
138 SELECT count(1)
139 FROM wf_local_roles
140 WHERE name = p_role_name
141 ;
142
143 BEGIN
144
145 SELECT 'CP_FLOW-' || TO_CHAR(msc_sup_dem_entries_s.nextval)
146 INTO l_wf_key FROM DUAL;
147
148 print_user_info('Workflow Key is: ' || l_wf_key);
149
150 l_wf_process := 'START_DP_RECEIVE_FORECAST';
151
152 -- create a Workflow process for the (item/org/supplier)
153 wf_engine.CreateProcess
154 ( itemtype => l_wf_type
155 , itemkey => l_wf_key
156 , process => l_wf_process
157 );
158
159 wf_engine.SetItemAttrNumber
160 ( itemtype => l_wf_type
161 , itemkey => l_wf_key
162 , aname => 'CUSTOMER_ID'
163 , avalue => p_customer_id
164 );
165 wf_engine.SetItemAttrDate
166 ( itemtype => l_wf_type
167 , itemkey => l_wf_key
168 , aname => 'START_DATE'
169 , avalue => p_horizon_start
170 );
171 wf_engine.SetItemAttrNumber
172 ( itemtype => l_wf_type
173 , itemkey => l_wf_key
174 , aname => 'HORIZON_DAYS'
175 , avalue => p_horizon_days
176 );
177
178 IF (p_resp_key IS NOT NULL) THEN
179 -- start of set up the Workflow role for TP
180 l_tp_role_name := 'MSCX_TP_WF_ROLE';
181 l_tp_role_display_name := 'Collaborative Planning trading partner role';
182 -- check if the Workflow role already exists
183 OPEN c_wf_role_existing(
184 l_tp_role_name
185 );
186 FETCH c_wf_role_existing INTO l_tp_role_existing;
187 CLOSE c_wf_role_existing;
188 IF (l_tp_role_existing <1) THEN -- Workflow role not exists
189 BEGIN
190 -- create a Ad Hoc Workflow role
191 WF_DIRECTORY.CreateAdHocRole(
192 role_name => l_tp_role_name
193 , role_display_name => l_tp_role_display_name
194 );
195 EXCEPTION
196 WHEN OTHERS THEN
197 print_user_info('Error when creating Workflow role: sqlerrm = ' || sqlerrm);
198 END;
199 END IF;
200
201 BEGIN
202 -- remove previous WF users from the WF role first
203 WF_DIRECTORY.RemoveUsersFromAdHocRole
204 ( role_name => l_tp_role_name
205 );
206 EXCEPTION
207 WHEN OTHERS THEN
208 print_user_info('Error when removing user from Workflow role: sqlerrm = ' || sqlerrm);
209 END;
210
211 -- add contact person name(s) of seller to the WF role
212 FOR tp_names IN c_tp_name(
213 p_resp_key
214 ) LOOP
215 IF (tp_names.name IS NOT NULL) THEN
216 WF_DIRECTORY.AddUsersToAdHocRole(
217 role_name => l_tp_role_name
218 , role_users => tp_names.name
219 );
220 END IF;
221 END LOOP;
222 -- end of set up the Workflow role for seller
223 wf_engine.SetItemAttrText
224 ( itemtype => l_wf_type
225 , itemkey => l_wf_key
226 , aname => 'RECIPIENT_ROLE'
227 , avalue => l_tp_role_name
228 );
229 /*
230 ELSIF (p_recipient_name IS NOT NULL) THEN
231 wf_engine.SetItemAttrText
232 ( itemtype => l_wf_type
233 , itemkey => l_wf_key
234 , aname => 'RECIPIENT_ROLE'
235 , avalue => p_recipient_name
236 );
237 */
238 END IF;
239
240 wf_engine.SetItemAttrText
241 ( itemtype => l_wf_type
242 , itemkey => l_wf_key
243 , aname => 'MESSAGE_TO_TP'
244 , avalue => p_message_to_tp
245 );
246 wf_engine.SetItemAttrText
247 ( itemtype => l_wf_type
248 , itemkey => l_wf_key
249 , aname => 'TP_NAME'
250 , avalue => p_tp_name
251 );
252
253 -- start Workflow process
254 wf_engine.StartProcess
255 ( itemtype => l_wf_type
256 , itemkey => l_wf_key
257 );
258
259 EXCEPTION
260 WHEN OTHERS THEN
261 raise;
262 END Start_DP_Receive_Forecast_WF;
263
264 -- This procedure will lanuch the SCEM engine
265 PROCEDURE DP_Receive_Forecast
266 ( itemtype in varchar2
267 , itemkey in varchar2
268 , actid in number
269 , funcmode in varchar2
270 , resultout out nocopy varchar2
271 ) IS
272
273 l_customer_id NUMBER := wf_engine.GetItemAttrNumber
274 ( itemtype
275 , itemkey
276 , aname => 'CUSTOMER_ID'
277 );
278 l_horizon_start DATE := wf_engine.GetItemAttrDate
279 ( itemtype
280 , itemkey
281 , aname => 'START_DATE'
282 );
283 l_horizon_days NUMBER := wf_engine.GetItemAttrNumber
284 ( itemtype
285 , itemkey
286 , aname => 'HORIZON_DAYS'
287 );
288
289 l_result BOOLEAN;
290 l_request_id NUMBER;
291
292 l_aps_customer_id NUMBER;
293
294 CURSOR c_aps_customer_id (p_customer_id IN NUMBER) IS
295 SELECT map.tp_key
296 FROM msc_trading_partner_maps map
297 , msc_company_relationships cr
298 WHERE map.map_type = 1 -- company level mapping
299 AND cr.object_id = p_customer_id
300 AND map.company_key = cr.relationship_id
301 AND cr.relationship_type = 1 -- customer
302 AND cr.subject_id = 1 -- OEM
303 ;
304
305 BEGIN
306
307 OPEN c_aps_customer_id (l_customer_id);
308 FETCH c_aps_customer_id INTO l_aps_customer_id;
309 CLOSE c_aps_customer_id;
310
311 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
312 l_result := FND_REQUEST.SET_MODE(TRUE);
313 l_request_id := NULL;
314 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
315 'MSD', -- application
316 'MSDXRCF',-- program
317 NULL, -- description
318 NULL, -- start_time
319 FALSE, -- sub_request
320 'CP_ORDER_FORECAST' -- p_designator in varchar2,
321 , 2 -- p_order_type in number,
322 , NULL -- , l_org_code -- in varchar2 default null,
323 , NULL -- , l_planner_code -- in varchar2 default null,
324 , NULL -- , l_item_id -- in number default null,
325 , l_aps_customer_id -- p_customer_id -- in number default null,
326 , NULL -- , l_customer_site_id -- in number default null,
327 , fnd_date.date_to_canonical(sysdate)-- l_horizon_start -- p_horizon_start -- in date default sysdate,
328 , 365 -- l_horizon_end - l_horizon_start + 1 -- p_horizon_days -- l_horizon_days -- in number default 365
329 );
330
331 commit;
332
333 print_user_info('Receive Forecast from Customer engine launched with concurrent request id ' || l_request_id);
334
335 wf_engine.SetItemAttrNumber
336 ( itemtype
337 , itemkey
338 , aname => 'REQUEST_ID'
339 , avalue => l_request_id
340 );
341
342 /*
343 MSD_SCE_RECEIVE_FORECAST_PKG.receive_customer_forecast (
344 p_errbuf => l_errbuf-- out varchar2,
345 , p_retcode => l_retcode -- out varchar2,
346 , p_designator => 'CP_ORDER_FORECAST' -- in varchar2,
347 , p_order_type => 2 -- in number,
348 -- , l_org_code -- in varchar2 default null,
349 -- , l_planner_code -- in varchar2 default null,
350 -- , l_item_id -- in number default null,
351 , p_customer_id => l_customer_id -- in number default null,
352 -- , l_customer_site_id -- in number default null,
353 , p_horizon_start => l_horizon_start -- in date default sysdate,
354 , p_horizon_days => l_horizon_end - l_horizon_start + 1 -- l_horizon_days -- in number default 365
355 );
356 */
357 EXCEPTION
358 WHEN OTHERS THEN
359 raise;
360 END DP_Receive_Forecast;
361
362 PROCEDURE Receive_Supplier_Capacity_WF
363 ( p_supplier_id IN Number
364 , p_horizon_start_date In date
365 , p_horizon_end_date In date
366 , p_resp_key IN varchar2
367 , p_message_to_tp IN VARCHAR2
368 , p_tp_name IN VARCHAR2
369 ) IS
370
371 l_wf_type VARCHAR2(8) := 'MSCXRCSC';
372 l_wf_key VARCHAR2(240);
373 l_wf_process VARCHAR2(30);
374 l_sequence_id NUMBER;
375
376 l_tp_role_name VARCHAR2(320);
377 l_tp_role_display_name VARCHAR2(360);
378 l_tp_role_existing NUMBER;
379
380 -- get the seller(supplier) name
381 CURSOR c_tp_name(
382 p_resp_key varchar2
383 ) IS
384 SELECT fu.user_name name
385 FROM fnd_responsibility resp
386 , fnd_user_resp_groups furg
387 , msc_company_users mcu
388 , fnd_user fu
389 WHERE resp.responsibility_key = p_resp_key
390 AND furg.responsibility_id = resp.responsibility_id
391 and furg.user_id = mcu.user_id
392 and mcu.user_id = fu.user_id
393 and mcu.company_id = 1
394 ;
395
396 -- check if Workflow role already exists
397 CURSOR c_wf_role_existing(
398 p_role_name IN VARCHAR2
399 ) IS
400 SELECT count(1)
401 FROM wf_local_roles
402 WHERE name = p_role_name
403 ;
404
405 BEGIN
406
407 SELECT 'CP_FLOW-' || TO_CHAR(msc_sup_dem_entries_s.nextval)
408 INTO l_wf_key FROM DUAL;
409
410 print_user_info('Workflow Key is: ' || l_wf_key);
411
412 l_wf_process := 'RECEIVE_SUPPLIER_CAPACITY';
413
414 -- create a Workflow process for the (item/org/supplier)
415 wf_engine.CreateProcess
416 ( itemtype => l_wf_type
417 , itemkey => l_wf_key
418 , process => l_wf_process
419 );
420
421 wf_engine.SetItemAttrNumber
422 ( itemtype => l_wf_type
423 , itemkey => l_wf_key
424 , aname => 'SUPPLIER_ID'
425 , avalue => p_supplier_id
426 );
427 wf_engine.SetItemAttrDate
428 ( itemtype => l_wf_type
429 , itemkey => l_wf_key
430 , aname => 'START_DATE'
431 , avalue => p_horizon_start_date
432 );
433 wf_engine.SetItemAttrDate
434 ( itemtype => l_wf_type
435 , itemkey => l_wf_key
436 , aname => 'END_DATE'
437 , avalue => p_horizon_end_date
438 );
439
440 IF (p_resp_key IS NOT NULL) THEN
441 -- start of set up the Workflow role for TP
442 l_tp_role_name := 'MSCX_TP_WF_ROLE';
443 l_tp_role_display_name := 'Collaborative Planning trading partner role';
444 -- check if the Workflow role already exists
445 OPEN c_wf_role_existing(
446 l_tp_role_name
447 );
448 FETCH c_wf_role_existing INTO l_tp_role_existing;
449 CLOSE c_wf_role_existing;
450 IF (l_tp_role_existing <1) THEN -- Workflow role not exists
451 BEGIN
452 -- create a Ad Hoc Workflow role
453 WF_DIRECTORY.CreateAdHocRole(
454 role_name => l_tp_role_name
455 , role_display_name => l_tp_role_display_name
456 );
457 EXCEPTION
458 WHEN OTHERS THEN
459 print_user_info('Error when creating Workflow role: sqlerrm = ' || sqlerrm);
460 END;
461 END IF;
462
463 BEGIN
464 -- remove previous WF users from the WF role first
465 WF_DIRECTORY.RemoveUsersFromAdHocRole
466 ( role_name => l_tp_role_name
467 );
468 EXCEPTION
469 WHEN OTHERS THEN
470 print_user_info('Error when removing user from Workflow role: sqlerrm = ' || sqlerrm);
471 END;
472
473 -- add contact person name(s) of seller to the WF role
474 FOR tp_names IN c_tp_name(
475 p_resp_key
476 ) LOOP
477 IF (tp_names.name IS NOT NULL) THEN
478 WF_DIRECTORY.AddUsersToAdHocRole(
479 role_name => l_tp_role_name
480 , role_users => tp_names.name
481 );
482 END IF;
483 END LOOP;
484 -- end of set up the Workflow role for seller
485 wf_engine.SetItemAttrText
486 ( itemtype => l_wf_type
487 , itemkey => l_wf_key
488 , aname => 'RECIPIENT_ROLE'
489 , avalue => l_tp_role_name
490 );
491 /*
492 ELSIF (p_recipient_name IS NOT NULL) THEN
493 wf_engine.SetItemAttrText
494 ( itemtype => l_wf_type
495 , itemkey => l_wf_key
496 , aname => 'RECIPIENT_ROLE'
497 , avalue => p_recipient_name
498 );
499 */
500 END IF;
501
502 wf_engine.SetItemAttrText
503 ( itemtype => l_wf_type
504 , itemkey => l_wf_key
505 , aname => 'MESSAGE_TO_TP'
506 , avalue => p_message_to_tp
507 );
508 wf_engine.SetItemAttrText
509 ( itemtype => l_wf_type
510 , itemkey => l_wf_key
511 , aname => 'TP_NAME'
512 , avalue => p_tp_name
513 );
514
515 -- start Workflow process
516 wf_engine.StartProcess
517 ( itemtype => l_wf_type
518 , itemkey => l_wf_key
519 );
520
521 EXCEPTION
522 WHEN OTHERS THEN
523 raise;
524 END Receive_Supplier_Capacity_WF;
525
526 -- This procedure will lanuch the SCEM engine
527 PROCEDURE Receive_Supplier_Capacity
528 ( itemtype in varchar2
529 , itemkey in varchar2
530 , actid in number
531 , funcmode in varchar2
532 , resultout out nocopy varchar2
533 ) IS
534
535 l_supplier_id NUMBER := wf_engine.GetItemAttrNumber
536 ( itemtype
537 , itemkey
538 , aname => 'SUPPLIER_ID'
539 );
540 l_horizon_start DATE := wf_engine.GetItemAttrDate
541 ( itemtype
542 , itemkey
543 , aname => 'START_DATE'
544 );
545 l_horizon_end DATE := wf_engine.GetItemAttrDate
546 ( itemtype
547 , itemkey
548 , aname => 'END_DATE'
549 );
550
551 l_result BOOLEAN;
552 l_request_id NUMBER;
553
554 l_aps_supplier_id NUMBER;
555 l_calendar_code msc_trading_partners.calendar_code%type;
556
557 CURSOR c_aps_supplier_id (p_supplier_id IN NUMBER) IS
558 SELECT map.tp_key
559 FROM msc_trading_partner_maps map
560 , msc_company_relationships cr
561 WHERE map.map_type = 1 -- company level mapping
562 AND cr.object_id = p_supplier_id
563 AND map.company_key = cr.relationship_id
564 AND cr.relationship_type = 2 -- supplier
565 AND cr.subject_id = 1 -- OEM
566 ;
567
568 BEGIN
569 begin
570 select distinct mtp.calendar_code
571 into l_calendar_code
572 from msc_designators des, msc_plans p, msc_trading_partners mtp
573 where des.designator = p.compile_designator and
574 des.sr_instance_id = p.sr_instance_id and
575 des.organization_id = p.organization_id and
576 des.production = 1 and
577 p.sr_instance_id = mtp.sr_instance_id and
578 p.organization_id = mtp.sr_tp_id and
579 mtp.partner_type = 3 and
580 des.sr_instance_id = mtp.sr_instance_id and
581 des.organization_id = mtp.sr_tp_id
582 and rownum = 1 order by mtp.calendar_code;
583 exception
584 when others then
585 l_calendar_code := null;
586 end;
587
588 OPEN c_aps_supplier_id (l_supplier_id);
589 FETCH c_aps_supplier_id INTO l_aps_supplier_id;
590 CLOSE c_aps_supplier_id;
591
592 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
593 l_result := FND_REQUEST.SET_MODE(TRUE);
594 l_request_id := NULL;
595 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
596 'MSC', -- application
597 'MSCXRCAP',-- program
598 NULL, -- description
599 NULL, -- start_time
600 FALSE, -- sub_request
601 -- NULL -- , p_sr_instance_id => NULL -- IN Number,
602 fnd_date.date_to_canonical(sysdate) -- l_horizon_start -- p_horizon_start_date -- In date,
603 ,fnd_date.date_to_canonical(sysdate+ 365) -- l_horizon_end -- p_horizon_end_date -- In date,
604 , NULL -- , p_abc_class -- In Varchar2,
605 , NULL -- p_item_id In Number,
606 , NULL -- p_planner -- In Varchar2,
607 , l_aps_supplier_id -- l_supplier_id -- p_supplir_id In number,
608 , NULL -- p_supplier_site_id -- In Number,
609 -- no more calendar code, l_calendar_code
610 , NULL -- p_mps_designator_id -- In Number
611 , 1 -- p_overwrite default 1
612 , 1 -- p_spread_capacity --- default 1 -- allow spreading
613
614 );
615
616 commit;
617
618 print_user_info('Receive Supplier Capacity engine launched with concurrent request id ' || l_request_id);
619
620 wf_engine.SetItemAttrNumber
621 ( itemtype
622 , itemkey
623 , aname => 'REQUEST_ID'
624 , avalue => l_request_id
625 );
626
627 /*
628 MSC_X_RECEIVE_CAPACITY_PKG.receive_capacity
629 ( p_errbuf => l_errbuf -- OUT VARCHAR2,
630 , p_retcode => l_retcode -- OUT VARCHAR2,
631 , p_sr_instance_id => NULL -- IN Number,
632 , p_horizon_start_date => l_horizon_start -- In date,
633 , p_horizon_end_date => l_horizon_end -- In date,
634 , p_abc_class => NULL -- In Varchar2,
635 , p_item_id => NULL -- In Number,
636 , p_planner => NULL -- In Varchar2,
637 , p_supplier_id => l_supplier_id -- In number,
638 , p_supplier_site_id => NULL -- In Number,
639 , p_mps_designator_id => NULL -- In Number
640 );
641 */
642 EXCEPTION
643 WHEN OTHERS THEN
644 raise;
645 END Receive_Supplier_Capacity;
646
647 PROCEDURE Start_ASCP_Engine_WF
648 ( p_constrained_plan_flag IN NUMBER
649 ) IS
650 l_wf_type VARCHAR2(8) := 'MSCXASCP';
651 l_wf_key VARCHAR2(240);
652 l_wf_process VARCHAR2(30);
653 l_sequence_id NUMBER;
654
655 BEGIN
656
657 SELECT 'CP_FLOW-' || TO_CHAR(msc_sup_dem_entries_s.nextval)
658 INTO l_wf_key FROM DUAL;
659
660 print_user_info('Workflow Key is: ' || l_wf_key);
661
662 l_wf_process := 'START_ASCP_ENGINE';
663
664 -- create a Workflow process
665 wf_engine.CreateProcess
666 ( itemtype => l_wf_type
667 , itemkey => l_wf_key
668 , process => l_wf_process
669 );
670
671 wf_engine.SetItemAttrNumber
672 ( itemtype => l_wf_type
673 , itemkey => l_wf_key
674 , aname => 'CONSTRAINED_PLAN_FLAG'
675 , avalue => p_constrained_plan_flag
676 );
677
678 -- start Workflow process
679 wf_engine.StartProcess
680 ( itemtype => l_wf_type
681 , itemkey => l_wf_key
682 );
683
684 EXCEPTION
685 WHEN OTHERS THEN
686 raise;
687 END Start_ASCP_Engine_WF;
688
689 -- This procedure will lanuch the ASCP engine
690 PROCEDURE Launch_ASCP_Engine
691 ( itemtype in varchar2
692 , itemkey in varchar2
693 , actid in number
694 , funcmode in varchar2
695 , resultout out nocopy varchar2
696 ) IS
697
698 l_plan_id NUMBER;
699 l_default_plan_name VARCHAR2(10);
700
701 l_constrained_plan_flag NUMBER := wf_engine.GetItemAttrNumber
702 ( itemtype
703 , itemkey
704 , aname => 'CONSTRAINED_PLAN_FLAG'
705 );
706
707 l_result BOOLEAN;
708 l_request_id NUMBER;
709
710 CURSOR c_plan_id (p_default_plan_name IN VARCHAR2) IS
711 SELECT plan_id
712 FROM msc_plans
713 WHERE compile_designator = p_default_plan_name
714 ;
715
716 BEGIN
717
718 IF (l_constrained_plan_flag = 1) THEN
719 l_default_plan_name := FND_PROFILE.VALUE('MSC_DEFAULT_CONST_PLAN');
720 ELSIF (l_constrained_plan_flag = 2) THEN
721 l_default_plan_name := FND_PROFILE.VALUE('MSC_DEFAULT_UNCONST_PLAN');
722 END IF;
723
724 l_plan_id := NULL;
725 OPEN c_plan_id(l_default_plan_name);
726 FETCH c_plan_id INTO l_plan_id;
727 CLOSE c_plan_id;
728
729 IF (l_plan_id IS NULL) THEN
730 print_user_info('Default plan name is not valid, please check the profile options for default ASCP plans');
731 ELSE
732 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
733 l_result := FND_REQUEST.SET_MODE(TRUE);
734 l_request_id := NULL;
735 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
736 'MSC', -- application
737 'MSCSLPPR5',-- program
738 NULL, -- description
739 NULL, -- start_time
740 FALSE, -- sub_request
741 l_default_plan_name, --IN VARCHAR2,
742 l_plan_id, -- IN NUMBER,
743 1, -- IN NUMBER,
747 );
744 1, -- IN NUMBER,
745 2, -- IN NUMBER,
746 fnd_date.date_to_chardate(SYSDATE) -- IN VARCHAR2
748
749 commit;
750
751 print_user_info('Launch Supply Chain Planning Process (ASCP) engine launched with concurrent request id ' || l_request_id);
752
753 /*
754 msc_launch_plan_pk.msc_launch_plan (
755 errbuf => l_errbuf -- OUT VARCHAR2,
756 , retcode => l_retcode -- OUT NUMBER,
757 , arg_plan_id => l_plan_id -- IN NUMBER,
758 , arg_launch_snapshot => 1 -- IN NUMBER,
759 , arg_launch_planner => 1 -- IN NUMBER,
760 , arg_netchange_mode => 2 -- IN NUMBER,
761 , arg_anchor_date => TO_CHAR(SYSDATE) -- IN VARCHAR2
762 );
763
764 */
765 END IF;
766
767 EXCEPTION
768 WHEN OTHERS THEN
769 raise;
770 END Launch_ASCP_Engine;
771
772
773 FUNCTION auto_scem_mode
774 RETURN NUMBER IS
775 l_return_result NUMBER;
776 l_auto_scem_mode NUMBER;
777 l_configuration NUMBER;
778
779 BEGIN
780 l_auto_scem_mode := FND_PROFILE.VALUE('MSC_X_AUTO_SCEM_MODE');
781 l_configuration := NVL(FND_PROFILE.VALUE('MSC_X_CONFIGURATION'), 1);
782
783 IF ( (l_auto_scem_mode = 1) -- LOAD
784 AND (l_configuration = 2 OR l_configuration = 3) -- APS+CP OR CP
785 )THEN
786 l_return_result := 1;
787 ELSIF ( (l_auto_scem_mode = 2) -- PUBLISH
788 AND (l_configuration = 2 OR l_configuration = 3) -- APS+CP OR CP
789 )THEN
790 l_return_result := 2;
791 ELSIF ( (l_auto_scem_mode = 3) -- ALL
792 AND (l_configuration = 2 OR l_configuration = 3) -- APS+CP OR CP
793 )THEN
794 l_return_result := 3;
795 END IF;
796
797 RETURN l_return_result;
798
799 EXCEPTION
800 WHEN OTHERS THEN
801 raise;
802 END auto_scem_mode;
803
804 PROCEDURE Publish_Supply_Commits_WF
805 ( p_plan_id in number
806 ) IS
807 l_wf_type VARCHAR2(8) := 'MSCXPBSC';
808 l_wf_key VARCHAR2(240);
809 l_wf_process VARCHAR2(30);
810 l_sequence_id NUMBER;
811
812 BEGIN
813
814 SELECT 'CP_FLOW-' || TO_CHAR(msc_sup_dem_entries_s.nextval)
815 INTO l_wf_key FROM DUAL;
816
817 print_user_info('Workflow Key is: ' || l_wf_key);
818
819 l_wf_process := 'PUBLISH_SUPPLY_COMMIT';
820
821 -- create a Workflow process for the (item/org/supplier)
822 wf_engine.CreateProcess
823 ( itemtype => l_wf_type
824 , itemkey => l_wf_key
825 , process => l_wf_process
826 );
827
828 wf_engine.SetItemAttrNumber
829 ( itemtype => l_wf_type
830 , itemkey => l_wf_key
831 , aname => 'PLAN_ID'
832 , avalue => p_plan_id
833 );
834
835 -- start Workflow process
836 wf_engine.StartProcess
837 ( itemtype => l_wf_type
838 , itemkey => l_wf_key
839 );
840
841 EXCEPTION
842 WHEN OTHERS THEN
843 raise;
844 END Publish_Supply_Commits_WF;
845
846 -- This procedure will lanuch the SCEM engine
847 PROCEDURE Publish_Supply_Commits
848 ( itemtype in varchar2
849 , itemkey in varchar2
850 , actid in number
851 , funcmode in varchar2
852 , resultout out nocopy varchar2
853 ) IS
854
855 l_plan_id NUMBER := wf_engine.GetItemAttrNumber
856 ( itemtype
857 , itemkey
858 , aname => 'PLAN_ID'
859 );
860
861 l_result BOOLEAN;
862 l_request_id NUMBER;
863
864 BEGIN
865 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
866 l_result := FND_REQUEST.SET_MODE(TRUE);
867 l_request_id := NULL;
868 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
869 'MSC', -- application
870 'MSCXSCP',-- program
871 NULL, -- description
872 NULL, -- start_time
873 FALSE, -- sub_request
874 l_plan_id, --p_plan_id --in number,
875 null, --p_org_code
876 null, --p_planner_code
877 null, --p_abc_class
878 null, --p_item_id
879 null, --p_planning_gp
880 null, --p_project_id
881 null, --p_task_id
882 null, --p_source_customer_id
883 null, --p_source_customer_site_id
884 fnd_date.date_to_chardate(SYSDATE) , --p_horizon_start
885 fnd_date.date_to_chardate(SYSDATE + 365) , --p_horizon_end
886 1, --p_auto_version default 1,
887 null, --p_version
888 2, --p_include_so_flag default 2
889 1 --p_overwrite default 1
890 );
891
892 commit;
893
894 print_user_info('Publish Supply Commits engine launched with concurrent request id ' || l_request_id);
895 /*
896 msc_sce_pub_supply_commit_pkg.publish_supply_commits (
897 p_errbuf => l_errbuf --out varchar2,
898 , p_retcode => l_retcode --out varchar2,
899 , p_plan_id => l_plan_id --in number,
900 -- , p_org_code => --in varchar2 default null,
901 -- , p_planner_code => --in varchar2 default null,
902 -- , p_abc_class => --in varchar2 default null,
903 -- , p_item_id => --in number default null,
904 -- , p_planning_gp => --in varchar2 default null,
905 -- , p_project_id => --in number default null,
906 -- , p_task_id => --in number default null,
907 -- , p_source_customer_id => --in number default null,
908 -- , p_source_customer_site_id => --in number default null,
909 -- , p_horizon_start => --in date default sysdate,
910 -- , p_horizon_end => --in date default sysdate+365,
911 -- , p_auto_version => --in number default 1,
912 -- , p_version => --in number default null
913 );
914 */
915 EXCEPTION
916 WHEN OTHERS THEN
917 raise;
918 END Publish_Supply_Commits;
919
920 PROCEDURE Publish_Order_Forecast_WF
921 ( p_plan_id in number
922 ) IS
923 l_wf_type VARCHAR2(8) := 'MSCXPBOF';
924 l_wf_key VARCHAR2(240);
925 l_wf_process VARCHAR2(30);
926 l_sequence_id NUMBER;
927
928 l_result BOOLEAN;
929 l_request_id NUMBER;
930
931 BEGIN
932
933 SELECT 'CP_FLOW-' || TO_CHAR(msc_sup_dem_entries_s.nextval)
934 INTO l_wf_key FROM DUAL;
935
936
937 print_user_info('Workflow Key is: ' || l_wf_key);
938
939 l_wf_process := 'START_PUBLISH_ORDER_FORECAST';
940
941 -- create a Workflow process for the (item/org/supplier)
942 wf_engine.CreateProcess
943 ( itemtype => l_wf_type
944 , itemkey => l_wf_key
945 , process => l_wf_process
946 );
947
948 wf_engine.SetItemAttrNumber
949 ( itemtype => l_wf_type
950 , itemkey => l_wf_key
951 , aname => 'PLAN_ID'
952 , avalue => p_plan_id
953 );
954
955 -- start Workflow process
956 wf_engine.StartProcess
957 ( itemtype => l_wf_type
958 , itemkey => l_wf_key
959 );
960
961 EXCEPTION
962 WHEN OTHERS THEN
963 raise;
964 END Publish_Order_Forecast_WF;
965
966 PROCEDURE Publish_Order_Forecast
967 ( itemtype in varchar2
968 , itemkey in varchar2
969 , actid in number
970 , funcmode in varchar2
971 , resultout out nocopy varchar2
972 ) IS
973
974 l_plan_id NUMBER := wf_engine.GetItemAttrNumber
975 ( itemtype
976 , itemkey
977 , aname => 'PLAN_ID'
978 );
979
980 l_result BOOLEAN;
981 l_request_id NUMBER;
982 BEGIN
983 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
984 l_result := FND_REQUEST.SET_MODE(TRUE);
985 l_request_id := NULL;
986 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
987 'MSC', -- application
988 'MSCXPO',-- program
989 NULL, -- description
990 NULL, -- start_time
991 FALSE, -- sub_request
992 l_plan_id, --p_plan_id --in number,
993 null, --p_org_code
994 null, --p_planner_code
995 null, --p_abc_class
996 null, --p_item_id
997 null, --p_item_list
998 null, --p_planning_gp
999 null, --p_project_id
1000 null, --p_task_id
1001 null, --p_source_supplier_id
1002 null, --p_source_supplier_site_id
1003 fnd_date.date_to_chardate(SYSDATE) , --p_horizon_start
1004 fnd_date.date_to_chardate(SYSDATE + 365) , --p_horizon_end
1005 1, -- p_auto_version default 1
1006 null, --p_version
1007 2, --p_purchase_order default 2
1008 2, --p_requisition default 2
1009 1 --p_overwrite default 1
1010 );
1011
1012 commit;
1013
1014 print_user_info('Publish Order Forecast engine launched with concurrent request id ' || l_request_id);
1015 /*
1016 msc_sce_publish_pkg.publish_plan_orders
1017 (
1018 p_errbuf => l_errbuf, -- out varchar2,
1019 p_retcode => l_retcode, -- out varchar2,
1020 p_plan_id => l_plan_id -- in number,
1021 p_org_code => -- in varchar2 default null,
1022 p_planner_code => -- in varchar2 default null,
1023 p_abc_class => -- in varchar2 default null,
1024 p_item_id => -- in number default null,
1025 p_planning_gp => -- in varchar2 default null,
1026 p_project_id => -- in number default null,
1027 p_task_id => -- in number default null,
1028 p_source_supplier_id => -- in number default null,
1029 p_source_supplier_site_id => -- in number default null,
1030 p_horizon_start => -- in date default sysdate,
1031 p_horizon_end => -- in date default sysdate+365,
1032 p_auto_version => -- in number default 1,
1033 p_version => -- in number default null,
1034 p_source_order_type => -- in number default 5
1035 );
1036 */
1037 EXCEPTION
1038 WHEN OTHERS THEN
1039 raise;
1040 END Publish_Order_Forecast;
1041
1042 -- This procesure prints out debug information
1043 PROCEDURE print_debug_info(
1044 p_debug_info IN VARCHAR2
1045 )IS
1046 BEGIN
1047 FND_FILE.PUT_LINE(FND_FILE.LOG, 'DEBUG: ' || p_debug_info);
1048 -- dbms_output.put_line(p_debug_info); --ut
1049 EXCEPTION
1050 WHEN OTHERS THEN
1051 RAISE;
1052 END print_debug_info;
1053
1054 -- This procesure prints out message to user
1055 PROCEDURE print_user_info(
1056 p_user_info IN VARCHAR2
1057 )IS
1058 BEGIN
1059 FND_FILE.PUT_LINE(FND_FILE.LOG, 'USER: ' || p_user_info);
1060 -- dbms_output.put_line(p_user_info); --ut
1061 EXCEPTION
1062 WHEN OTHERS THEN
1063 RAISE;
1064 END print_user_info;
1065
1066 END MSC_X_CP_FLOW;