1 PACKAGE BODY MSC_WS_SCENARIO_MANAGEMENT AS
2 /* $Header: MSCWSMAB.pls 120.14 2009/12/11 16:45:44 bnaghi ship $ */
3 g_UserId NUMBER;
4 g_ErrorCode VARCHAR2(9);
5
6 -- =============================================================
7 -- Desc: Qurery msc_planning_process table and returns.
8 -- ProcessName
9 -- ProcessFlowId
10 -- CurrRunSequence
11 --
12 -- Input:
13 -- UserId User ID.
14 -- ResponsibilityId Responsibility Id.
15 -- ProcessId Process Id.
16 --
17 -- Output: The possible return statuses are:
18 -- SUCCESS if everything is ok
19 -- NO_DATA_FOUND
20 -- INVALID_FND_USERID
21 -- INVALID_FND_RESPONSIBILITYID
22 -- =============================================================
23 PROCEDURE GET_PROCESS_INFO(
24 Status OUT NOCOPY VARCHAR2,
25 ProcessName OUT NOCOPY VARCHAR2,
26 ProcessFlowId OUT NOCOPY NUMBER,
27 CurrRunSequence OUT NOCOPY NUMBER,
28 UserId IN NUMBER,
29 ResponsibilityId IN NUMBER,
30 ProcessId IN NUMBER
31 ) AS
32 l_String VARCHAR2(100);
33 l_Skip NUMBER;
34 l_Owner NUMBER;
35 l_ActivityStatus NUMBER;
36 BEGIN
37 -- check user id and responsibility
38 MSC_WS_COMMON.VALIDATE_USER_RESP(l_String, UserId, ResponsibilityId);
39 IF (l_String <> 'OK') THEN
40 Status := l_String;
41 RETURN;
42 END IF;
43
44 BEGIN
45 SELECT
46 process_name, process_flow_id, curr_run_sequence
47 INTO
48 ProcessName, ProcessFlowId, CurrRunSequence
49 FROM msc_planning_process
50 WHERE
51 process_id = ProcessId;
52 EXCEPTION
53 WHEN no_data_found THEN
54 Status := 'NO_DATA_FOUND';
55 RETURN;
56 WHEN others THEN
57 Status := 'ERROR_UNEXPECTED_01001';
58 RETURN;
59 END;
60
61 Status := 'SUCCESS';
62 END GET_PROCESS_INFO;
63
64
65 PROCEDURE GET_PROCESS_INFO_PUBLIC(
66 Status OUT NOCOPY VARCHAR2,
67 ProcessName OUT NOCOPY VARCHAR2,
68 ProcessFlowId OUT NOCOPY NUMBER,
69 CurrRunSequence OUT NOCOPY NUMBER,
70 UserName IN VARCHAR2,
71 RespName IN VARCHAR2,
72 RespApplName IN VARCHAR2,
73 SecurityGroupName IN VARCHAR2,
74 Language IN VARCHAR2,
75 ProcessId IN NUMBER
76 ) AS
77 userid number;
78 respid number;
79 l_String VARCHAR2(30);
80 error_tracking_num number;
81 l_SecutirtGroupId NUMBER;
82 BEGIN
83 error_tracking_num :=2010;
84 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
85 IF (l_String <> 'OK') THEN
86 Status := l_String;
87 RETURN;
88 END IF;
89
90 error_tracking_num :=2030;
91 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSC_SCN_MANAGE_SCENARIOS',l_SecutirtGroupId);
92 IF (l_String <> 'OK') THEN
93 Status := l_String;
94 RETURN;
95 END IF;
96 error_tracking_num :=2040;
97
98 GET_PROCESS_INFO ( Status,ProcessName, ProcessFlowId, CurrRunSequence, userId , respid, ProcessId );
99
100
101
102 EXCEPTION
103 WHEN others THEN
104 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
105
106 return;
107 END GET_PROCESS_INFO_PUBLIC;
108
109 -- =============================================================
110 -- Desc: Qurery msc_planning_proc_activities table and returns.
111 -- PlanId
112 -- Skip
113 -- Owner (Name)
114 -- Time_Out
115 -- Alternate_Owner (Name)
116 -- ActivityStatus
117 -- ActivityType.
118 --
119 -- Input:
120 -- UserId User ID.
121 -- ResponsibilityId Responsibility Id.
122 -- ProcessId Process Id.
123 -- RunSequence Run sequence.
124 -- ProcessScope Activity name.
125 --
126 -- Output: The possible return statuses are:
127 -- SUCCESS if everything is ok
128 -- NO_DATA_FOUND
129 -- INVALID_FND_USERID
130 -- INVALID_FND_RESPONSIBILITYID
131 -- INVALID_MISSING_OWNER_ID
132 -- INVALID_UNKWON_OWNER_ID
133 -- INVALID_UNKWON_ALTERNATE_OWNER_ID
134 -- =============================================================
135 PROCEDURE GET_ACTIVITY_INST_INFO(
136 Status OUT NOCOPY VARCHAR2,
137 PlanId OUT NOCOPY NUMBER,
138 Skip OUT NOCOPY VARCHAR2,
139 OwnerName OUT NOCOPY VARCHAR2,
140 TimeOut OUT NOCOPY NUMBER,
141 AlternateOwnerName OUT NOCOPY VARCHAR2,
142 ActivityStatus OUT NOCOPY VARCHAR2,
143 ActivityType OUT NOCOPY NUMBER,
144 UserId IN NUMBER,
145 ResponsibilityId IN NUMBER,
146 ProcessId IN NUMBER,
147 RunSequence IN NUMBER,
148 ProcessScope IN VARCHAR2
149 ) AS
150 l_String VARCHAR2(100);
151 l_Skip NUMBER;
152 l_Owner NUMBER;
153 l_AltOwner NUMBER;
154 l_ActivityStatus NUMBER;
155 BEGIN
156 -- check user id and responsibility
157 --MSC_WS_COMMON.VALIDATE_USER_RESP(l_String, UserId, ResponsibilityId);
158 --IF (l_String <> 'OK') THEN
159 -- Status := l_String;
160 -- RETURN;
161 -- END IF;
162
163 BEGIN
164 SELECT
165 plan_id, skip, owner,
166 nvl(time_out,1440),
167 alternate_owner,
168 status, activity_type
169 INTO
170 PlanId, l_Skip, l_Owner, TimeOut,
171 l_AltOwner, l_ActivityStatus, ActivityType
172 FROM msc_planning_proc_activities
173 WHERE
174 process_id = ProcessId AND
175 run_sequence = RunSequence AND
176 process_scope = ProcessScope;
177 EXCEPTION
178 WHEN no_data_found THEN
179 Status := 'NO_DATA_FOUND';
180 RETURN;
181 WHEN others THEN
182 Status := 'ERROR_UNEXPECTED_01002';
183 RETURN;
184 END;
185 -- convert Skip
186 CASE l_Skip
187 WHEN 1 THEN
188 Skip := 'Y';
189 WHEN 2 THEN
190 Skip := 'N';
191 ELSE
192 Status := 'ERROR_UNEXPECTED_01003';
193 RETURN;
194 END CASE;
195
196 -- convert Owner from ID to name
197 IF l_Owner IS NULL THEN
198 Status := 'INVALID_MISSING_OWNER_ID';
199 RETURN;
200 END IF;
201 BEGIN
202 SELECT user_name
203 INTO OwnerName
204 FROM fnd_user
205 where user_id = l_Owner;
206 EXCEPTION
207 WHEN no_data_found THEN
208 Status := 'INVALID_UNKWON_OWNER_ID';
209 RETURN;
210 WHEN others THEN
211 Status := 'ERROR_UNEXPECTED_01004';
212 RETURN;
213 END;
214
215 -- convert EscalateTo from ID to name
216 IF l_AltOwner IS NULL THEN
217 AlternateOwnerName := '-1';
218 ELSE
219 BEGIN
220 SELECT user_name
221 INTO AlternateOwnerName
222 FROM fnd_user
223 WHERE user_id = l_AltOwner;
224 EXCEPTION
225 WHEN no_data_found THEN
226 Status := 'INVALID_UNKWON_ALTERNATE_OWNER_ID';
227 RETURN;
228 WHEN others THEN
229 Status := 'ERROR_UNEXPECTED_01005';
230 RETURN;
231 END;
232 END IF;
233
234 -- convert activity status
235 IF l_ActivityStatus =1 THEN
236 ActivityStatus := 'NOT_START';
237 ELSIF l_ActivityStatus = 2 THEN
238 ActivityStatus := 'IN_PROGRESS';
239 ELSIF l_ActivityStatus = 3 THEN
240 ActivityStatus := 'COMPLETED';
241 ELSIF l_ActivityStatus = 4 THEN
242 ActivityStatus := 'ERROR';
243 ELSIF l_ActivityStatus = 5 THEN
244 ActivityStatus := 'ABORTED';
245 ELSIF l_ActivityStatus = 6 THEN
246 ActivityStatus := 'WARNING';
247 ELSE
248 Status := 'ERROR_UNEXPECTED_01006';
249 RETURN;
250 END IF;
251
252 Status := 'SUCCESS';
253 END GET_ACTIVITY_INST_INFO;
254
255
256 PROCEDURE GET_ACTIVITY_INST_INFO_PUBLIC(
257 Status OUT NOCOPY VARCHAR2,
258 PlanId OUT NOCOPY NUMBER,
259 Skip OUT NOCOPY VARCHAR2,
260 OwnerName OUT NOCOPY VARCHAR2,
261 TimeOut OUT NOCOPY NUMBER,
262 AlternateOwnerName OUT NOCOPY VARCHAR2,
263 ActivityStatus OUT NOCOPY VARCHAR2,
264 ActivityType OUT NOCOPY NUMBER,
265 UserName IN VARCHAR2,
266 RespName IN VARCHAR2,
267 RespApplName IN VARCHAR2,
268 SecurityGroupName IN VARCHAR2,
269 Language IN VARCHAR2,
270 ProcessId IN NUMBER,
271 RunSequence IN NUMBER,
272 ProcessScope IN VARCHAR2
273 ) AS
274 userid number;
275 respid number;
276 l_String VARCHAR2(30);
277 error_tracking_num number;
278 l_SecutirtGroupId NUMBER;
279 BEGIN
280 error_tracking_num :=2010;
281 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
282 IF (l_String <> 'OK') THEN
283 Status := l_String;
284 RETURN;
285 END IF;
286
287 error_tracking_num :=2030;
288 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSC_SCN_MANAGE_SCENARIOS',l_SecutirtGroupId);
289 IF (l_String <> 'OK') THEN
290 Status := l_String;
291 RETURN;
292 END IF;
293 error_tracking_num :=2040;
294
295 GET_ACTIVITY_INST_INFO ( Status,
296 PlanId,
297 Skip,
298 OwnerName,
299 TimeOut,
300 AlternateOwnerName,
301 ActivityStatus,
302 ActivityType,
303 userid,
304 respid,
305 ProcessId ,
306 RunSequence ,
307 ProcessScope);
308
309
310
311 EXCEPTION
312 WHEN others THEN
313 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
314
315 return;
316 END GET_ACTIVITY_INST_INFO_PUBLIC;
317
318 -- =============================================================
319 -- Desc: Qurery current activity status.
320 -- Input:
321 -- ProcessInstanceId Process instance Id.
322 -- ActivityId Activity Id.
323 --
324 -- Output: The possible return statuses are:
325 -- OK
326 -- NO_DATA_FOUND
327 -- =============================================================
328 FUNCTION GET_ACTIVITY_STATUS(
329 ActivityStatus OUT NOCOPY NUMBER,
330 ProcessId IN NUMBER,
331 RunSequence IN NUMBER,
332 ProcessScope IN VARCHAR2
333 ) RETURN VARCHAR2 AS
334 BEGIN
335 BEGIN
336 -- SELECT status
337 SELECT status
338 INTO ActivityStatus
339 FROM msc_planning_proc_activities
340 WHERE
341 process_id = ProcessId AND
342 run_sequence = RunSequence AND
343 Process_Scope = ProcessScope;
344 EXCEPTION
345 WHEN no_data_found THEN
346 RETURN 'NO_DATA_FOUND';
347 WHEN others THEN
348 RETURN 'ERROR_UNEXPECTED_01007';
352
349 END;
350 RETURN 'OK';
351 END GET_ACTIVITY_STATUS;
353 -- =============================================================
354 -- Desc: verify the input activity status.
355 -- Input:
356 -- ProcessInstanceId Process instance Id.
357 -- ActivityId Activity Id.
358 --
359 -- Output: The possible return statuses are:
360 -- OK
361 -- NO_DATA_FOUND
362 -- =============================================================
363 FUNCTION VALIDATE_ACTIVITY_STATUS(
364 ProcessId IN NUMBER,
365 RunSequence IN NUMBER,
366 ProcessScope IN VARCHAR2,
367 ActivityStatus IN NUMBER
368 ) RETURN VARCHAR2 AS
369 l_String VARCHAR2(100);
370 l_ActivityStatus NUMBER;
371 BEGIN
372 l_String := GET_ACTIVITY_STATUS(l_ActivityStatus, ProcessId, RunSequence, ProcessScope);
373 IF (l_String <> 'OK') THEN
374 RETURN l_String;
375 END IF;
376
377 -- safety net, Not Start, Aborted and Warning are restricted by xsd.
378 IF ActivityStatus = 1 OR ActivityStatus = 5 OR ActivityStatus = 6 THEN
379 RETURN 'INVALID_STATUS';
380 END IF;
381
382 -- =======================================
383 -- Activity status can be changed from :-
384 -- Not Start to In Process
385 -- In Process to Error
386 -- In Process to Completed
387 -- In Completed to Completed (special requested by Beatrice)
388 -- =======================================
389 CASE ActivityStatus
390 WHEN 2 THEN -- In Process
391 IF l_ActivityStatus <> 1 THEN
392 RETURN 'INVALID_STATUS';
393 END IF;
394 WHEN 3 THEN -- Completed
395 -- New behaviour, able to change from process to completed
396 IF l_ActivityStatus <> 2 AND l_ActivityStatus <> 3 THEN
397 RETURN 'INVALID_STATUS';
398 END IF;
399 WHEN 4 THEN -- Error
400 IF l_ActivityStatus <> 2 THEN
401 RETURN 'INVALID_STATUS';
402 END IF;
403 ELSE
404 RETURN 'INVALID_STATUS';
405 END CASE;
406
407 RETURN 'OK';
408 END VALIDATE_ACTIVITY_STATUS;
409
410 -- =============================================================
411 -- Desc: Update activity_status in msc_planning_proc_activities table.
412 -- Input:
413 -- UserId User ID.
414 -- ResponsibilityId Responsibility Id.
415 -- ProcessInstanceId Process instance Id.
416 -- ProcessId Process Id.
417 -- RunSequence Run sequence.
418 -- ProcessScope Activity name.
419 -- NewStatus New activity status
420 --
421 -- Output: The possible return statuses are:
422 -- SUCCESS if everything is ok
423 -- NO_DATA_FOUND
424 -- INVALID_FND_USERID
425 -- INVALID_FND_RESPONSIBILITYID
426 -- INVALID_STATUS
427 -- =============================================================
428 PROCEDURE SET_ACTIVITY_INST_STATUS(
429 Status OUT NOCOPY VARCHAR2,
430 UserId IN NUMBER,
431 ResponsibilityId IN NUMBER,
432 ProcessId IN NUMBER,
433 RunSequence IN NUMBER,
434 ProcessScope IN VARCHAR2,
435 NewStatus IN VARCHAR2
436 ) AS
437 l_String VARCHAR2(100);
438 l_NewStatus NUMBER;
439 BEGIN
440 -- check user id and responsibility
441 -- MSC_WS_COMMON.VALIDATE_USER_RESP(l_String, UserId, ResponsibilityId);
442 -- IF (l_String <> 'OK') THEN
443 -- Status := l_String;
444 -- RETURN;
445 -- END IF;
446
447 -- convert activity status, NOT_START, ABORTED and WARNING are not allowed in xsd
448 IF NewStatus = 'IN_PROGRESS' THEN
449 l_NewStatus := 2;
450 ELSIF NewStatus = 'COMPLETED' THEN
451 l_NewStatus := 3;
452 ELSIF NewStatus = 'ERROR' THEN
453 l_NewStatus := 4;
454 END IF;
455
456 l_String := VALIDATE_ACTIVITY_STATUS(ProcessId, RunSequence, ProcessScope, l_NewStatus);
457 IF (l_String <> 'OK') THEN
458 Status := l_String;
459 RETURN;
460 END IF;
461
462 -- update msc_planning_proc_activities
463 BEGIN
464 UPDATE msc_planning_proc_activities
465 SET status = l_NewStatus
466 WHERE
467 process_id = ProcessId AND
468 run_sequence = RunSequence AND
469 Process_Scope = ProcessScope;
470 EXCEPTION
471 WHEN others THEN
472 Status := 'ERROR_UNEXPECTED_01008';
473 RETURN;
474 END;
475
476 -- Just find out from Vijay, we don't need to do this
477 -- update scenario_definitions as well if need
478 -- to do:
479 -- if this is the 1st act and is set to In Progress, set scenario_definitions.process_status to In Progress as well
480 -- if this is the last act and is set to Completed, set scenario_definitions.process_status to Completed as well
481 -- if new status is Error, set scenario_definitions.process_status to Error as well
482
483 Status := 'SUCCESS';
484 END SET_ACTIVITY_INST_STATUS;
485
486 PROCEDURE SET_ACTIVITY_INST_ST_PUBLIC(
487 Status OUT NOCOPY VARCHAR2,
488 UserName IN VARCHAR2,
489 RespName IN VARCHAR2,
490 RespApplName IN VARCHAR2,
491 SecurityGroupName IN VARCHAR2,
492 Language IN VARCHAR2,
496 NewStatus IN VARCHAR2
493 ProcessId IN NUMBER,
494 RunSequence IN NUMBER,
495 ProcessScope IN VARCHAR2,
497 ) AS
498 userid number;
499 respid number;
500 l_String VARCHAR2(30);
501 error_tracking_num number;
502 l_SecutirtGroupId NUMBER;
503 BEGIN
504 error_tracking_num :=2010;
505 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
506 IF (l_String <> 'OK') THEN
507 Status := l_String;
508 RETURN;
509 END IF;
510
511 error_tracking_num :=2030;
512 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSC_SCN_MANAGE_SCENARIOS',l_SecutirtGroupId);
513 IF (l_String <> 'OK') THEN
514 Status := l_String;
515 RETURN;
516 END IF;
517 error_tracking_num :=2040;
518
519 SET_ACTIVITY_INST_STATUS ( status,
520 userid ,
521 respid,
522 ProcessId,
523 RunSequence,
524 ProcessScope,
525 NewStatus);
526 -- dbms_output.put_line('USERID=' || userid);
527
528
529 EXCEPTION
530 WHEN others THEN
531 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
532
533 return;
534 END SET_ACTIVITY_INST_ST_PUBLIC;
535
536 -- =============================================================
537 -- Desc: Update msc_planning_process.curr_run_sequence and
538 -- msc_process_instances.actual_start_date.
539 -- Input:
540 -- UserId User ID.
541 -- ResponsibilityId Responsibility Id.
542 -- ProcessInstanceId Process instance Id.
543 -- ProcessId Process Id.
544 -- RunSequence Run sequence.
545 --
546 -- Output: The possible return statuses are:
547 -- SUCCESS if everything is ok
548 -- NO_DATA_FOUND
549 -- INVALID_FND_USERID
550 -- INVALID_FND_RESPONSIBILITYID
551 -- =============================================================
552 PROCEDURE UPDATE_PROCESS(
553 Status OUT NOCOPY VARCHAR2,
554 UserId IN NUMBER,
555 ResponsibilityId IN NUMBER,
556 ProcessId IN NUMBER,
557 RunSequence IN NUMBER
558 ) AS
559 l_String VARCHAR2(100);
560 l_NextActivityId NUMBER;
561 e_NoDataFound EXCEPTION;
562 BEGIN
563 -- check user id and responsibility
564 -- MSC_WS_COMMON.VALIDATE_USER_RESP(l_String, UserId, ResponsibilityId);
565 -- IF (l_String <> 'OK') THEN
566 -- Status := l_String;
567 -- RETURN;
568 -- END IF;
569
570
571 -- update msc_process_instances.actual_start_date
572 UPDATE msc_process_instances
573 SET actual_start_date = sysdate,
574 last_update_date = sysdate,
575 last_updated_by = userid,
576 last_update_login = userid
577 WHERE
578 process_id = ProcessId AND
579 run_sequence = RunSequence;
580 IF SQL%NOTFOUND THEN
581 raise e_NoDataFound;
582 END IF;
583
584 COMMIT;
585 Status := 'SUCCESS';
586
587 EXCEPTION
588 WHEN e_NoDataFound THEN
589 Status := 'NO_DATA_FOUND';
590 RETURN;
591 WHEN others THEN
592 Status := 'ERROR_UNEXPECTED_01009';
593 RETURN;
594
595 END UPDATE_PROCESS;
596
597
598 PROCEDURE UPDATE_PROCESS_PUBLIC(
599 Status OUT NOCOPY VARCHAR2,
600 UserName IN VARCHAR2,
601 RespName IN VARCHAR2,
602 RespApplName IN VARCHAR2,
603 SecurityGroupName IN VARCHAR2,
604 Language IN VARCHAR2,
605 ProcessId IN NUMBER,
606 RunSequence IN NUMBER
607 ) AS
608 userid number;
609 respid number;
610 l_String VARCHAR2(30);
611 error_tracking_num number;
612 l_SecutirtGroupId NUMBER;
613 BEGIN
614 error_tracking_num :=2010;
615 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
616 IF (l_String <> 'OK') THEN
617 Status := l_String;
618 RETURN;
619 END IF;
620
621 error_tracking_num :=2030;
622 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSC_SCN_MANAGE_SCENARIOS',l_SecutirtGroupId);
623 IF (l_String <> 'OK') THEN
624 Status := l_String;
625 RETURN;
626 END IF;
627 error_tracking_num :=2040;
628
629 UPDATE_PROCESS( status,
630 userId ,
631 respid,
632 ProcessId,
633 RunSequence );
634
635
636
637 EXCEPTION
638 WHEN others THEN
639 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
640
641 return;
642 END UPDATE_PROCESS_PUBLIC;
646 -- UserId User ID.
643 -- =============================================================
644 -- Desc: Get value for all parameters.
645 -- Input:
647 -- ResponsibilityId Responsibility Id.
648 -- ProcessId Process Id.
649 -- RunSequence Run sequence.
650 -- ProcessScope Activity Name.
651 --
652 -- Output: The possible return statuses are:
653 -- SUCCESS if everything is ok
654 -- NO_DATA_FOUND
655 -- INVALID_FND_USERID
656 -- INVALID_FND_RESPONSIBILITYID
657 -- =============================================================
658 PROCEDURE GET_PARAMETER_VALUE(
659 Status OUT NOCOPY VARCHAR2,
660 ParameterValues OUT NOCOPY MscActivityParaTbl,
661 UserId IN NUMBER,
662 ResponsibilityId IN NUMBER,
663 ProcessId IN NUMBER,
664 RunSequence IN NUMBER,
665 ProcessScope IN VARCHAR2
666 ) AS
667 CURSOR getParameter_c (idProcess NUMBER, seqRun NUMBER, nameActivity VARCHAR2) IS
668 SELECT param_name, param_value
669 FROM msc_proc_inst_act_params
670 WHERE process_id = idProcess AND
671 run_sequence = seqRun AND
672 activity_id =
673 ( SELECT activity_id FROM msc_planning_proc_activities
674 WHERE process_id = idProcess AND
675 run_sequence = seqRun AND
676 process_scope = nameActivity)
677 ORDER BY parameterSequence;
678 l_String VARCHAR2(100);
679 l_ParameterName VARCHAR2(50);
680 l_ParameterValue VARCHAR2(50);
681 l_Dummy NUMBER;
682 BEGIN
683 BEGIN
684 ParameterValues := MscActivityParaTbl();
685 OPEN getParameter_c(ProcessId, RunSequence, ProcessScope);
686 LOOP
687 FETCH getParameter_c into l_ParameterName, l_ParameterValue;
688 EXIT WHEN getParameter_c%NOTFOUND;
689 ParameterValues.extend;
690 ParameterValues(ParameterValues.count) := MscActivityParaRec(l_ParameterName, l_ParameterValue);
691 END LOOP;
692 CLOSE getParameter_c;
693
694 IF ParameterValues.count = 0 THEN
695 SELECT count(*) INTO l_Dummy
696 FROM msc_proc_inst_act_params
697 WHERE process_id = ProcessId AND
698 run_sequence = RunSequence AND
699 activity_id =
700 ( SELECT activity_id FROM msc_planning_proc_activities
701 WHERE process_id = RunSequence AND
702 run_sequence = RunSequence AND
703 process_scope = ProcessScope);
704 IF l_Dummy = 0 THEN
705 status := 'NO_DATA_FOUND'; -- wrong Process id / activity name
706 RETURN;
707 END IF;
708 -- do nothing, this activity has no parameter
709 END IF;
710 Status := 'SUCCESS';
711
712 EXCEPTION WHEN others THEN
713 status := 'ERROR_UNEXPECTED_01010';
714 END;
715 END GET_PARAMETER_VALUE;
716
717
718 PROCEDURE GET_PARAMETER_VALUE_PUBLIC(
719 Status OUT NOCOPY VARCHAR2,
720 ParameterValues OUT NOCOPY MscActivityParaTbl,
721 UserName IN VARCHAR2,
722 RespName IN VARCHAR2,
723 RespApplName IN VARCHAR2,
724 SecurityGroupName IN VARCHAR2,
725 Language IN VARCHAR2,
726 ProcessId IN NUMBER,
727 RunSequence IN NUMBER,
728 ProcessScope IN VARCHAR2
729 ) AS
730 userid number;
731 respid number;
732 l_String VARCHAR2(30);
733 error_tracking_num number;
734 l_SecutirtGroupId NUMBER;
735 BEGIN
736 ParameterValues := MscActivityParaTbl();
737 error_tracking_num :=2010;
738 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
739 IF (l_String <> 'OK') THEN
740 Status := l_String;
741 RETURN;
742 END IF;
743
744 error_tracking_num :=2030;
745 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSC_SCN_MANAGE_SCENARIOS',l_SecutirtGroupId);
746 IF (l_String <> 'OK') THEN
747 Status := l_String;
748 RETURN;
749 END IF;
750 error_tracking_num :=2040;
751
752 GET_PARAMETER_VALUE ( status,
753 ParameterValues,
754 userid ,
755 respid,
756 ProcessId ,
757 RunSequence,
758 ProcessScope);
759
760
761
762 EXCEPTION
763 WHEN others THEN
764 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
765
766 return;
767 END GET_PARAMETER_VALUE_PUBLIC;
768 -- =============================================================
769 -- Desc: This procedure is invoked from web service to launch
770 -- the Archive Scenario concurrent program. The input
771 -- parameters mirror the parameters for the concurrent program.
772 -- Input:
773 -- UserName User name.
774 -- RespName Responsibility name.
775 -- RespAppName Responsibility application name.
776 -- SecurityGroupName Security group name.
780 -- Output: Procedure returns a status and conc program req id.
777 -- Language Language.
778 -- ScenarioId Scenario Id.
779 --
781 -- The possible return statuses are:
782 -- SUCCESS if everything is ok
783 -- ERROR_SUBMIT failed to submit the concurrent program
784 -- ERROR_UNEXPECTED_# unexpected error
785 -- INVALID_USER_NAME
786 -- INVALID_LANGUAGE
787 -- INVALID_RESP_NAME
788 -- INVALID_SECUTITY_GROUP_NAME
789 -- INVALID_FND_USERID
790 -- INVALID_FND_RESPONSIBILITYID
791 -- INVALID_SCENARIO_ID invalid scenario id
792 -- =============================================================
793 PROCEDURE ARCHIVE_SCENARIO_PUBLIC(
794 ProcessId OUT NOCOPY NUMBER,
795 Status OUT NOCOPY VARCHAR2,
796 UserName IN VARCHAR2,
797 RespName IN VARCHAR2,
798 RespAppName IN VARCHAR2,
799 SecurityGroupName IN VARCHAR2,
800 Language IN VARCHAR2,
801 ScenarioId IN NUMBER
802 ) IS
803 l_String VARCHAR2(100);
804 l_ResponsibilityId NUMBER;
805 l_SecurityGroupId NUMBER;
806 l_Dummy NUMBER;
807 l_Number NUMBER;
808 error_tracking_num NUMBER;
809 BEGIN
810 -- dbms_output.put_line('Matthew: Init');
811 -- init global variables
812 g_ErrorCode := '';
813 ProcessId := -1;
814
815 -- query user id, responsibility id and security group id
816 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, g_UserId, l_ResponsibilityId, l_SecurityGroupId, UserName, RespName, RespAppName, SecurityGroupName, Language);
817 IF (l_String <> 'OK') THEN
818 Status := l_String;
819 RETURN;
820 END IF;
821 error_tracking_num :=2030;
822 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSC_SCN_MANAGE_SCENARIOS',l_SecurityGroupId);
823 IF (l_String <> 'OK') THEN
824 Status := l_String;
825 RETURN;
826 END IF;
827
828 -- check scenario id
829 BEGIN
830 SELECT 1 INTO l_Dummy
831 FROM msc_scenarios
832 WHERE
833 NVL(valid_from, TRUNC(SYSDATE) - 1) < TRUNC(SYSDATE) AND
834 NVL(valid_to, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE) AND
835 scenario_id = ScenarioId;
836 EXCEPTION
837 WHEN no_data_found THEN
838 Status := 'INVALID_SCENARIO_ID';
839 RETURN;
840 WHEN others THEN
841 Status := 'ERROR_UNEXPECTED_01011';
842 RETURN;
843 END;
844
845 -- Now, submit the conc. program to run
846 l_Number := fnd_request.submit_request(
847 application => 'MSC',
848 program => 'MSCSCNAR',
849 argument1 => ScenarioId
850 );
851 IF (l_Number = 0) THEN
852 Status := 'ERROR_SUBMIT';
853 ELSE
854 ProcessId := l_Number;
855 Status := 'SUCCESS';
856 END IF;
857
858 EXCEPTION
859 WHEN others THEN
860 Status := g_ErrorCode;
861
862 END ARCHIVE_SCENARIO_PUBLIC;
863
864 END MSC_WS_SCENARIO_MANAGEMENT;
865
[Home] [Help]