[Home] [Help]
PACKAGE BODY: APPS.MSC_WS_NOTIFICATION_BPEL
Source
1 PACKAGE BODY MSC_WS_NOTIFICATION_BPEL AS
2 /* $Header: MSCWNOTB.pls 120.2.12010000.3 2008/07/08 18:42:37 bnaghi ship $ */
3
4
5
6 --========================= NOTIFICATION =====================================
7
8
9 procedure set_wf_approver_role
10 (
11 itemtype IN VARCHAR2,
12 itemkey IN VARCHAR2,
13 actid IN NUMBER,
14 funcmode IN VARCHAR2,
15 result in out nocopy varchar2
16 ) is
17 v_role_email varchar2(100);
18 n_ctr NUMBER :=0;
19 begin
20 v_role_email := upper ( wf_engine.getitemattrtext( itemtype => itemtype, itemkey =>itemkey, aname => 'SEND_TO_EMAIL'));
21
22 select count(*)
23 into n_ctr
24 from wf_local_roles
25 where name = v_role_email;
26
27 if n_ctr = 0
28 then
29 wf_directory.createadhocrole( role_name => v_role_email,
30 role_display_name => v_role_email,
31 role_description => v_role_email,
32 notification_preference => 'MAILHTML',
33 email_address => v_role_email,
34 status => 'ACTIVE',
35 expiration_date => NULL);
36
37 end if;
38
39 wf_engine.setitemattrtext(itemtype => itemtype,
40 itemkey => itemkey,
41 aname => 'SEND_TO_ROLE',
42 avalue => v_role_email);
43
44
45 result := 'complete:y';
46
47 EXCEPTION
48 when no_data_found then
49 result := 'complete:n';
50 return;
51 when others then
52 result:= 'UNKNOWN_ERROR';
53 return;
54
55 end set_wf_approver_role;
56
57 FUNCTION SendFYINotification ( userID IN NUMBER, --- sender user id
58 respID in NUMBER, --sender resp id
59 receiver in varchar2, -- notification goes to this guy
60 language IN Varchar2,
61 wfName IN VARCHAR2,
62 wfProcessName IN varchar2,
63 tokenValues IN MsgTokenValuePairList
64 )return VARCHAR2 is
65 i NUMBER :=0;
66 status varchar2(30);
67 l_itemtype varchar2(30);
68 l_itemkey varchar2(300);
69 l_useritemkey varchar2(300);
70 v_email varchar2(100);
71
72 begin
73 -- call fnd_global.apps_initialize
74 MSC_WS_COMMON.VALIDATE_USER_RESP(status, userId, respId);
75 IF (status <> 'OK') THEN
76 RETURN status;
77 END IF;
78
79 if (tokenValues is NULL or tokenValues.Count = 0 ) then
80 return 'ERROR_NO_TOKENS_PROVIDED_IN_INPUT';
81 end if;
82
83
84 select MSC_FORM_QUERY_S.nextval into l_itemkey from dual;
85
86 select email_address into v_email from fnd_user where user_name = receiver;
87
88 l_itemtype := wfName;
89
90 wf_engine.CreateProcess( l_itemtype, l_itemkey, wfProcessName);
91
92 -- Following to be changed to be NLS compliant
93 l_useritemkey := 'Planning Process';
94
95 wf_engine.setitemowner( l_itemtype, l_itemkey, receiver );
96 wf_engine.setitemattrtext(l_itemtype, l_itemkey, 'SEND_TO_ROLE', receiver);
97 wf_engine.setitemattrtext(l_itemtype, l_itemkey, 'SEND_TO_EMAIL', v_email);
98
99
100 for i in 1 .. tokenValues.COUNT
101 loop
102 --dbms_output.put_line('t-v: ' || tokenValues(i).token || ' and ' || tokenValues(i).value);
103 wf_engine.setitemattrtext( l_itemtype, l_itemkey, tokenValues(i).token, tokenValues(i).value);
104
105 if (tokenValues(i).token = 'PROCESS')
106 then l_useritemkey := l_useritemkey || ' (' ||tokenValues(i).value|| ')';
107 end if;
108 end loop;
109
110
111 wf_engine.setitemuserkey(l_itemtype, l_itemkey, l_useritemkey);
112 wf_engine.startprocess(l_itemtype, l_itemkey);
113
114 return 'SUCCESS';
115
116 EXCEPTION
117 when no_data_found then
118 return 'NO_DATA_FOUND';
119 when others then
120 return 'UNKNOWN_ERROR';
121
122 end SendFYINotification;
123
124
125 procedure Lookup
126 (
127 itemtype IN VARCHAR2,
128 itemkey IN VARCHAR2,
129 actid IN NUMBER,
130 funcmode IN VARCHAR2,
131 result in out nocopy varchar2
132 ) is
133 actTypeId varchar2(100);
134 actType varchar2(100);
135 begin
136 actTypeId := wf_engine.getitemattrtext(itemtype, itemkey, 'ACTIVITY_TYPE_ID');
137
138 select meaning into actType
139 from mfg_lookups
140 where lookup_type = 'MSC_PROCESS_ACTIVITY_TYPES'
141 and lookup_code = to_number(actTypeId);
142
143 wf_engine.setitemattrtext(itemtype, itemkey, 'ACTIVITY_TYPE', actType);
144
145 result := 'complete:y';
146
147 EXCEPTION
148 when no_data_found then
149 result := 'complete:n';
150 return;
151 when others then
152 result:= 'UNKNOWN_ERROR';
153 return;
154
155 end Lookup;
156
157
158 procedure Lookup_Plan
159 (
160 itemtype IN VARCHAR2,
161 itemkey IN VARCHAR2,
162 actid IN NUMBER,
163 funcmode IN VARCHAR2,
164 result in out nocopy varchar2
165 ) is
166 planName varchar2(100);
167 planId varchar2(100);
168 begin
169 planId := wf_engine.getitemattrtext(itemtype, itemkey, 'PLAN_ID');
170
171 select COMPILE_DESIGNATOR
172 into planName
173 from msc_plans
174 where plan_id = to_number(planId);
175
176 wf_engine.setitemattrtext(itemtype, itemkey, 'PLAN_NAME', planName);
177
178 result := 'complete:y';
179
180 EXCEPTION
181 when no_data_found then
182 result := 'complete:n';
183 return;
184 when others then
185 result:= 'UNKNOWN_ERROR';
186 return;
187 end Lookup_Plan;
188
189
190 procedure Lookup_Escalation
191 (
192 itemtype IN VARCHAR2,
193 itemkey IN VARCHAR2,
194 actid IN NUMBER,
195 funcmode IN VARCHAR2,
196 result in out nocopy varchar2
197 ) is
198 escId varchar2(100);
199 escLevel varchar2(100);
200 begin
201 escId := wf_engine.getitemattrtext(itemtype, itemkey, 'ESCALATION_LEVEL_ID');
202
203 -- 1 => 'Primary Owner'
204 -- 2 => 'Alternate Owner'
205
206 select meaning into escLevel
207 from mfg_lookups
208 where lookup_type = 'MSC_ESCALATION_LEVEL'
209 and lookup_code = to_number(escId);
210
211 wf_engine.setitemattrtext(itemtype, itemkey, 'ESCALATION_LEVEL', escLevel);
212
213 result := 'complete:y';
214
215 EXCEPTION
216 when no_data_found then
217 result := 'complete:n';
218 return;
219 when others then
220 result:= 'UNKNOWN_ERROR';
221 return;
222
223 end Lookup_Escalation;
224
225 FUNCTION SendFYINotificationPublic (
226 UserName IN VARCHAR2,
227 RespName IN VARCHAR2,
228 RespApplName IN VARCHAR2,
229 SecurityGroupName IN VARCHAR2,
230 receiver in varchar2,
231 language IN Varchar2,
232 wfName IN VARCHAR2,
233 wfProcessName IN varchar2,
234 tokenValues IN MsgTokenValuePairList)
235 return VARCHAR2 is
236
237 userid number;
238 respid number;
239 l_String VARCHAR2(30);
240 error_tracking_num number;
241 l_SecutirtGroupId NUMBER;
242 status varchar2(30);
243 BEGIN
244 error_tracking_num :=2010;
245 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
246 IF (l_String <> 'OK') THEN
247
248 RETURN l_String;
249 END IF;
250
251 error_tracking_num :=2030;
252 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSC_SCN_MANAGE_SCENARIOS',l_SecutirtGroupId);
253 IF (l_String <> 'OK') THEN
254
255 RETURN l_string;
256
257 END IF;
258 error_tracking_num :=2040;
259 status := SendFYINotification ( userid,respid,receiver ,language,wfName,wfProcessName ,tokenValues);
260
261
262 return status;
263 EXCEPTION
264 WHEN others THEN
265 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
266
267 return status ;
268 end SendFYINotificationPublic;
269
270 END MSC_WS_NOTIFICATION_BPEL;