[Home] [Help]
PACKAGE BODY: APPS.MSC_SCN_ACTIVITY_WF
Source
1 PACKAGE BODY MSC_SCN_ACTIVITY_WF AS
2 /* $Header: MSCSCWFB.pls 120.1 2008/02/19 12:32:45 skakani noship $*/
3
4
5 PROCEDURE SendFYINotification ( userID IN NUMBER, -- sender user id
6 respID in NUMBER, --sender resp id
7 language IN Varchar2,
8 wfName IN VARCHAR2,
9 wfProcessName IN varchar2,
10 p_activity_id IN NUMBER,
11 status IN OUT NOCOPY VARCHAR2
12 ) is
13
14 l_itemtype varchar2(30);
15 l_itemkey varchar2(300);
16 v_email varchar2(100);
17 v_receiver varchar2(80);
18
19 CURSOR C_ACTIVITYINFO (p_activity_id in number) IS
20 SELECT MSA.ACTIVITY_NAME, MSA.FINISH_BY, MSA.PRIORITY, MSA.STATUS, MSA.OWNER, MSA.ALTERNATE_OWNER,
21 MSA.ACT_COMMENT,
22 MS.SCENARIO_NAME, MSS.SCENARIO_SET_NAME
23 FROM MSC_SCENARIO_ACTIVITIES MSA,
24 MSC_SCENARIOS MS,
25 MSC_SCENARIO_SETS MSS
26 WHERE
27 ACTIVITY_ID= p_activity_id
28 AND MSA.SCENARIO_ID=MS.SCENARIO_ID(+)
29 AND MSA.SCENARIO_SET_ID=MSS.SCENARIO_SET_ID(+);
30
31 l_activity_name varchar2(80);
32 l_finish_by date;
33 l_priority number;
34 l_status number;
35 l_owner number;
36 l_alternate_owner number;
37 l_act_comment varchar2(4000);
38 l_itemowner varchar2(80);
39 l_scenario_name varchar2(80);
40 l_scenario_set_name varchar2(80);
41 l_owner_name varchar2(80);
42 l_alt_owner_name varchar2(80);
43 l_status_name varchar2(80);
44
45
46 begin
47 -- call fnd_global.apps_initialize
48 MSC_WS_COMMON.VALIDATE_USER_RESP(status, userId, respId);
49 IF (status <> 'OK') THEN
50 RETURN;
51 END IF;
52
53 -- validation of wfName and tokenValues array
54
55 if wfName = 'SCN_MGMT' and p_activity_id is null then
56 status:= 'ERROR_NO_ACTIVITY_ID';
57 return;
58 end if;
59
60 select MSC_FORM_QUERY_S.nextval into l_itemkey from dual;
61
62 select user_name into l_itemowner from fnd_user where user_id=userId; --WF owner
63
64 open c_activityinfo(p_activity_id);
65 fetch c_activityinfo into l_activity_name, l_finish_by, l_priority, l_status,
66 l_owner, l_alternate_owner, l_act_comment, l_scenario_name ,l_scenario_set_name;
67 close c_activityinfo;
68
69 --dbms_output.put_line('p_activity_id='||p_activity_id ||'l_activity_name='||l_activity_name);
70
71 begin
72 select user_name into l_owner_name from fnd_user where user_id = l_owner;
73 select user_name into l_alt_owner_name from fnd_user where user_id = l_alternate_owner;
74 select meaning into l_status_name from mfg_lookups where lookup_type like 'MSC_SCN_ACTIVITY_STATES' and lookup_code=l_status;
75
76 exception when others then
77 null;
78 --DBMS_OUTPUT.PUT_LINE('SQLcode='||SQLCODE);
79 --DBMS_OUTPUT.PUT_LINE(SQLERRM);
80 end;
81
82 if(wfProcessName = 'ACT_OWNER_PROCESS') then
83 v_receiver := l_owner_name; --owner -notifcation recipent
84 else
85 v_receiver := l_alt_owner_name; --alt owner -notifcation recipent
86 end if;
87
88 --dbms_output.put_line(' NotifIcation recipent='||v_receiver);
89
90 l_itemtype := wfName;
91
92 wf_engine.CreateProcess( l_itemtype, l_itemkey, wfProcessName);
93
94 wf_engine.setitemuserkey( l_itemtype, l_itemkey, 'USERKEY_SCN ' || l_itemkey);
95
96 wf_engine.setitemowner( l_itemtype, l_itemkey, l_itemowner );
97
98 wf_engine.setitemattrtext(l_itemtype, l_itemkey, 'SEND_TO_ROLE', v_receiver);
99 wf_engine.setitemattrtext(l_itemtype, l_itemkey, 'ACTIVITY_NAME_ATTR', l_activity_name);
100 wf_engine.setitemattrdate(l_itemtype, l_itemkey, 'FINISH_BY_ATTR', l_finish_by);
101 wf_engine.setitemattrtext(l_itemtype, l_itemkey, 'STATUS_ATTR', l_status_name);
102 wf_engine.setitemattrtext(l_itemtype, l_itemkey, 'OWNER_ATTR',l_owner_name);
103 wf_engine.setitemattrtext(l_itemtype, l_itemkey, 'ALT_OWNER_ATTR',l_alt_owner_name);
104 wf_engine.setitemattrtext(l_itemtype, l_itemkey, 'SCENARIO_ATTR',l_scenario_name);
105 wf_engine.setitemattrtext(l_itemtype, l_itemkey, 'SCENARIO_SET_ATTR',l_scenario_set_name);
106
107
108 wf_engine.startprocess(l_itemtype, l_itemkey);
109
110 --DBMS_OUTPUT.PUT_LINE('WF started -Process='|| wfProcessName || ' USERKEY_SCN ' || l_itemkey);
111
112 status:= 'SUCCESS';
113
114 RETURN;
115
116 EXCEPTION when others then
117 --DBMS_OUTPUT.PUT_LINE('SQLcode='||SQLCODE);
118 --DBMS_OUTPUT.PUT_LINE(SQLERRM);
119
120 status:= 'UNKNOWN_ERROR';
121 end SendFYINotification;
122
123
124 -- -------------------------------------------------------
125 -- skakani 13-Feb-2008 -- New procedure Monitor_Scn_Changes
126 -- This procedure will be executed as a C/P, which will be scheduled
127 -- to run every day and identifies all the activities which are either
128 -- NOT started or STILL in progress, but expected to be complted as on date.
129 -- -------------------------------------------------------------------------
130
131 PROCEDURE Monitor_Scn_Changes(errbuf OUT NOCOPY VARCHAR2,
132 retcode OUT NOCOPY VARCHAR2) IS
133
134 CURSOR c_activity_info IS
135 SELECT MSA.ACTIVITY_ID , MSA.ACTIVITY_NAME,
136 MSA.FINISH_BY , MSA.PRIORITY ,
137 MSA.STATUS , MSA.OWNER ,
138 MSA.ALTERNATE_OWNER, MSA.ACT_COMMENT ,
139 MS.SCENARIO_NAME , MSS.SCENARIO_SET_NAME
140 FROM MSC_SCENARIO_ACTIVITIES MSA,
141 MSC_SCENARIOS MS,
142 MSC_SCENARIO_SETS MSS
143 WHERE MSA.STATUS IN (1,2) -- 1 Not Started , 2 In Progress
144 AND MSA.FINISH_BY < TRUNC(SYSDATE)
145 AND MSA.SCENARIO_ID=MS.SCENARIO_ID(+)
146 AND MSA.SCENARIO_SET_ID=MSS.SCENARIO_SET_ID(+);
147
148 l_userID NUMBER; -- sender user id
149 l_respID NUMBER; --sender resp id
150 l_language VARCHAR2(30);
151 l_status VARCHAR2(100);
152 l_counter NUMBER := 0;
153 BEGIN
154 --fnd_global.apps_initialize(1068, 23329, 724);
155
156 l_userID:= fnd_global.USER_ID; -- 1068
157 l_respID:= fnd_global.RESP_ID;-- 23329, 724
158 l_language:= userenv('LANG'); --fnd_global.NLS_LANGUAGE;
159 MSC_UTIL.MSC_DEBUG('Lang:'||l_language);
160 MSC_UTIL.MSC_DEBUG('User_Id:'||l_userID);
161 MSC_UTIL.MSC_DEBUG('Resp_Id:'||l_respID);
162 MSC_UTIL.MSC_DEBUG('Starting work flow...');
163
164 l_counter := 0;
165
166 FOR rec_activity_info IN c_activity_info LOOP
167 DECLARE
168 l_scenario_name VARCHAR2(100);
169 l_scenario_set_name VARCHAR2(100);
170 BEGIN
171 l_counter := l_counter +1;
172 l_scenario_name:= rec_activity_info.scenario_name;
173 l_scenario_set_name := rec_activity_info.scenario_set_name;
174
175 SendFYINotification( l_userID, -- sender user id
176 l_respID, --sender resp id
177 l_language,
178 'SCN_MGMT',
179 'ACT_ALT_OWNER_PROCESS',
180 rec_activity_info.activity_id,
181 l_status );
182 EXCEPTION
183 WHEN OTHERS THEN
184 MSC_UTIL.MSC_DEBUG('Error sending Notification#'||l_counter);
185 MSC_UTIL.MSC_DEBUG('Status: '||l_status);
186 MSC_UTIL.MSC_DEBUG('scenario_name:'||l_scenario_name);
187 MSC_UTIL.MSC_DEBUG('scenario_set_name:'||l_scenario_set_name);
188 END;
189 END LOOP;
190 MSC_UTIL.MSC_DEBUG('Completed sending notifications.');
191 retcode := 0;
192 EXCEPTION
193 WHEN OTHERS THEN
194 MSC_UTIL.MSC_DEBUG('Error generating Notifications.');
195 MSC_UTIL.MSC_DEBUG('Status: '||l_status);
196 errbuf := l_status||':'||sqlerrm(sqlcode);
197 retcode := 1;
198 END Monitor_Scn_Changes;
199 END MSC_SCN_ACTIVITY_WF;