[Home] [Help]
PACKAGE BODY: APPS.PER_RESPOWNER_UTIL_SS
Source
1 PACKAGE BODY PER_RESPOWNER_UTIL_SS AS
2 /* $Header: perroutl.pkb 120.0 2005/05/31 19:40:30 appldev noship $ */
3
4 FUNCTION getTableSchema RETURN VARCHAR2 IS
5 l_status VARCHAR2(100) := '';
6 l_industry VARCHAR2(100) := '';
7 l_result BOOLEAN;
8 l_schema_owner VARCHAR2(10) := '';
9 l_debug_level number := fnd_log.g_current_runtime_level;
10 l_stat_level number := fnd_log.level_statement;
11
12 BEGIN
13 l_result := FND_INSTALLATION.GET_APP_INFO(
14 'PER',
15 l_status,
16 l_industry,
17 l_schema_owner);
18
19 if( l_stat_level >= l_debug_level ) then
20 fnd_log.string(fnd_log.level_statement,
21 'per.plsql.'||gv_package||'.getTableSchema', 'l_schema_owner : '||l_schema_owner );
22 end if;
23
24 IF l_result THEN
25 RETURN l_schema_owner;
26 ELSE
27 RETURN 'HR';
28 END IF;
29 END getTableSchema;
30
31 ---This is an internal function. Not in spec
32 ---Function get_object_id
33 ------------------------------
34 Function get_object_id(p_object_name in varchar2
35 ) return number is
36 v_object_id number;
37 l_api_name CONSTANT VARCHAR2(30) := 'GET_OBJECT_ID';
38 Begin
39 select object_id
40 into v_object_id
41 from fnd_objects
42 where obj_name=p_object_name;
43
44 return v_object_id;
45 exception
46 when no_data_found then
47 return null;
48 end;
49
50 FUNCTION get_owned_responsibilites(
51 p_fnd_object in varchar2
52 ,p_user_name in varchar2)
53 RETURN resp_owner_table IS
54
55 CURSOR owned_resps(p_object_id in number,
56 p_owner_name in varchar2) IS
57 SELECT GNT.INSTANCE_PK1_VALUE, GNT.INSTANCE_PK2_VALUE, GNT.INSTANCE_PK3_VALUE
58 FROM fnd_grants gnt, fnd_responsibility fr
59 WHERE GNT.object_id = p_object_id AND
60 (((GNT.grantee_type = 'USER' AND
61 GNT.grantee_key = ''||p_owner_name||'') OR (GNT.grantee_type = 'GROUP' AND
62 GNT.grantee_key in (select role_name from wf_user_roles wur where wur.user_name = fnd_global.user_name()
63 and wur.user_orig_system = 'PER' and
64 wur.user_orig_system_id = fnd_global.employee_id() and
65 (start_date is NULL or start_date <= SYSDATE) and
66 (expiration_date is NULL or expiration_date >= SYSDATE))))) AND
67 (GNT.ctx_secgrp_id = -1 OR GNT.ctx_secgrp_id = FND_GLOBAL.SECURITY_GROUP_ID) AND
68 (GNT.ctx_resp_id = -1 OR GNT.ctx_resp_id = FND_GLOBAL.RESP_ID) AND
69 (GNT.ctx_resp_appl_id = -1 OR GNT.ctx_resp_appl_id = FND_GLOBAL.RESP_APPL_ID) AND
70 (GNT.ctx_org_id = -1 OR GNT.ctx_org_id = FND_PROFILE.VALUE('ORG_ID')) AND
71 GNT.start_date <= sysdate AND
72 (GNT.end_date IS NULL OR GNT.end_date >= sysdate) AND
73 ((GNT.INSTANCE_TYPE = 'INSTANCE')
74 AND fr.responsibility_id = GNT.INSTANCE_PK1_VALUE
75 AND fr.application_id = GNT.INSTANCE_PK2_VALUE
76 AND trunc(sysdate) between trunc(fr.start_date) and nvl(fr.end_date, trunc(sysdate)));
77
78 l_resp_owner_table resp_owner_table;
79 I integer default 0;
80 l_debug_level number := fnd_log.g_current_runtime_level;
81 l_proc_level number := fnd_log.level_procedure;
82
83 BEGIN
84
85 OPEN owned_resps(get_object_id(p_fnd_object), p_user_name);
86 LOOP
87 I := I + 1;
88 FETCH owned_resps into l_resp_owner_table(I);
89 EXIT WHEN owned_resps%NOTFOUND;
90 END LOOP;
91 CLOSE owned_resps; -- close cursor variable
92 return l_resp_owner_table;
93
94 END get_owned_responsibilites;
95
96 PROCEDURE populate_respowner_temp_table (
97 p_fnd_object in varchar2
98 ,p_user_name in varchar2
99 )
100 IS
101 l_resp_owner_table resp_owner_table;
102 I integer default 0;
103 l_debug_level number := fnd_log.g_current_runtime_level;
104 l_proc_level number := fnd_log.level_procedure;
105 l_event_level number := fnd_log.level_event;
106
107 BEGIN
108
109 if( l_event_level >= l_debug_level ) then
110 fnd_log.string(fnd_log.level_event,
111 'per.plsql.'||gv_package||'.populate_respowner_temp_table', 'Entered, p_user_name : '||p_user_name);
112 end if;
113
114 --first get the owned responsibilites for the passed in user
115 l_resp_owner_table := get_owned_responsibilites(
116 p_fnd_object => p_fnd_object
117 ,p_user_name => p_user_name);
118
119 --truncate the table before inserting
120 execute immediate 'truncate table '||getTableSchema||'.per_responsibility_owner';
121 FOR I IN 1 ..l_resp_owner_table.count LOOP
122 insert into per_responsibility_owner(responsibility_id,
123 application_id,
124 security_group_id)
125 values (l_resp_owner_table(I).responsibility_id
126 ,l_resp_owner_table(I).application_id
127 ,l_resp_owner_table(I).security_group_id);
128 END LOOP;
129 commit;
130
131 if( l_proc_level >= l_debug_level ) then
132 fnd_log.string(fnd_log.level_procedure,
133 'per.plsql.'||gv_package||'.populate_respowner_temp_table', 'Rows Inserted : '||to_char(I));
134 end if;
135
136 if( l_event_level >= l_debug_level ) then
137 fnd_log.string(fnd_log.level_event,
138 'per.plsql.'||gv_package||'.populate_respowner_temp_table', 'Leaving ..');
139 end if;
140
141 END populate_respowner_temp_table;
142
143
144 FUNCTION getValueForParameter(pName in varchar2,
145 pParameters in wf_parameter_list_t)
146 RETURN VARCHAR2 IS
147 pos number := 1;
148 BEGIN
149 if (pParameters is null) then
150 return NULL;
151 end if;
152
153 pos := pParameters.LAST;
154 while(pos is not null) loop
155 if (pParameters(pos).getName() = pName) then
156 return pParameters(pos).getValue();
157 end if;
158 pos := pParameters.PRIOR(pos);
159 end loop;
160 return NULL;
161 END getValueForParameter;
162
163
164 PROCEDURE CompleteNotiActivity(username in varchar2,
165 itemtype in varchar2,
166 itemkey in varchar2,
167 activity in varchar2,
168 result in varchar2)
169 IS
170
171 l_debug_level number := fnd_log.g_current_runtime_level;
172 l_event_level number := fnd_log.level_event;
173 l_exp_level number := fnd_log.level_exception;
174
175 BEGIN
176
177 if( l_event_level >= l_debug_level ) then
178 fnd_log.string(fnd_log.level_event,
179 'per.plsql.'||gv_package||'.CompleteNotiActivity', 'Entered, Revoke user : '||username);
180 end if;
181
182 IF (username is null) THEN
183 return;
184 END IF;
185
186 wf_engine.SetItemAttrText(itemtype => itemtype,
187 itemkey => itemkey,
188 aname => 'HR_REVOKE_ACCESS_USER_NAME',
189 avalue => username);
190
191 wf_engine.CompleteActivity(itemtype => itemtype,
192 itemkey => itemkey,
193 activity => activity,
194 result => result);
195 EXCEPTION
196 -- when other retry
197 WHEN OTHERS THEN
198 if( l_exp_level >= l_debug_level ) then
199 fnd_log.string(fnd_log.level_exception,
200 'per.plsql.'||gv_package||'.CompleteNotiActivity', 'Exception, Could not send notification to Revoke user : '||username);
201 end if;
202 wf_engine.HandleError(itemtype => itemtype,
203 itemkey => itemkey,
204 activity => activity,
205 command => 'RETRY');
206 END CompleteNotiActivity;
207
208
209 PROCEDURE send_notification (
210 p_seq in varchar2
211 ,p_parameters in wf_parameter_list_t
212 ,p_resp_name in varchar2
213 ,p_owner in varchar2
214 ,p_userid_clause in varchar2
215 ) IS
216
217 block_actid number;
218 l_noti_ref_cursor ref_cursor;
219 l_user_name fnd_user.user_name%type;
220 l_debug_level number := fnd_log.g_current_runtime_level;
221 l_stat_level number := fnd_log.level_statement;
222 l_event_level number := fnd_log.level_event;
223
224 BEGIN
225 if( l_event_level >= l_debug_level ) then
226 fnd_log.string(fnd_log.level_event,
227 'per.plsql.'||gv_package||'.send_notification', 'Entered, p_resp_name : '||p_resp_name||
228 ' , itemtype : RESPOWN, itemKey : '||p_seq);
229 end if;
230
231 -- Create the notfication workflow process
232 wf_engine.CreateProcess(itemtype => 'RESPOWN',
233 itemkey => p_seq,
234 process => 'HR_REVOKE_ACCESS_JSP_PRC');
235 -- set the owner
236 wf_engine.SetItemOwner(itemtype=> 'RESPOWN'
237 ,itemkey => p_seq
238 ,owner => p_owner);
239 -- set other required attributes
240 wf_engine.SetItemAttrText(itemtype => 'RESPOWN',
241 itemkey => p_seq,
242 aname => 'FROM_USER_NAME',
243 avalue => p_owner);
244
245 wf_engine.SetItemAttrText(itemtype => 'RESPOWN',
246 itemkey => p_seq,
247 aname => 'RESPONSIBILITY',
248 avalue => p_resp_name);
249
250 wf_engine.SetItemAttrText(itemtype => 'RESPOWN',
251 itemkey => p_seq,
252 aname => 'JUSTIFICATION',
253 avalue => getValueForParameter('MESSAGE',
254 p_parameters));
255
256 -- Start the notfication workflow process
257 wf_engine.StartProcess(itemtype => 'RESPOWN',
258 itemkey => p_seq);
259
260
261 -- get the block activity id
262 block_actid := wf_engine.GetItemAttrNumber(itemtype => 'RESPOWN',
263 itemkey => p_seq,
264 aname => 'HR_REVOKE_ACCESS_BLOCK_ACTID');
265
266 if( l_stat_level >= l_debug_level ) then
267 fnd_log.string(fnd_log.level_statement,
268 'per.plsql.'||gv_package||'.send_notification', 'Revoke Block Actid : '||block_actid);
269 end if;
270
271 --Loop thru the users and send notification
272 OPEN l_noti_ref_cursor FOR(gv_user_name_stmt || ' and '|| p_userid_clause);
273 LOOP
274 FETCH l_noti_ref_cursor into l_user_name;
275 IF(l_noti_ref_cursor%FOUND) THEN
276 CompleteNotiActivity(username => l_user_name,
277 itemtype => 'RESPOWN',
278 itemkey => p_seq,
279 activity => wf_engine.GetActivityLabel(block_actid),
280 result => 'Y');
281 l_user_name := null;
282 END IF;
283 EXIT WHEN l_noti_ref_cursor%NOTFOUND;
284 END LOOP;
285 CLOSE l_noti_ref_cursor; -- close cursor variable
286
287 -- Now end the process
288 wf_engine.CompleteActivity(itemtype => 'RESPOWN',
289 itemkey => p_seq,
290 activity => wf_engine.GetActivityLabel(block_actid),
291 result => 'N');
292
293 if( l_event_level >= l_debug_level ) then
294 fnd_log.string(fnd_log.level_event,
295 'per.plsql.'||gv_package||'.send_notification', 'Leaving ...');
296 end if;
297
298 END send_notification;
299
300
301 PROCEDURE raise_wfevent(
302 p_event_name in varchar2
303 ,p_event_data in wf_parameter_list_t
304 ,p_resp_name in varchar2
305 ,p_owner in varchar2
306 ,p_userid_clause in varchar2 default null
307 )IS
308
309 l_event_key number;
310 l_message varchar2(10);
311 --
312 cursor get_seq is
313 select hr_api_transactions_s.nextval from dual;
314 --
315 l_debug_level number := fnd_log.g_current_runtime_level;
316 l_proc_level number := fnd_log.level_procedure;
317 l_event_level number := fnd_log.level_event;
318
319 BEGIN
320 if( l_event_level >= l_debug_level ) then
321 fnd_log.string(fnd_log.level_event,
322 'per.plsql.'||gv_package||'.raise_wfevent', 'Entered, p_event_name : '||p_event_name);
323 end if;
324
325 -- check the status of the business event
326 l_message := wf_event.test(p_event_name);
327 --
328
329 if( l_proc_level >= l_debug_level ) then
330 fnd_log.string(fnd_log.level_procedure,
331 'per.plsql.'||gv_package||'.raise_wfevent', 'Subscription Type : '||l_message);
332 end if;
333
334 IF (l_message='MESSAGE') THEN
335 --
336 -- get a key for the event
337 --
338 open get_seq;
339 fetch get_seq into l_event_key;
340 close get_seq;
341
342
343 -- raise wf_event
344 wf_event.raise
345 (p_event_name => p_event_name,
346 p_event_key => l_event_key,
347 p_parameters => p_event_data);
348
349 -- now send notifications to users
350 send_notification(p_seq => to_char(l_event_key)
351 ,p_parameters => p_event_data
352 ,p_resp_name => p_resp_name
353 ,p_owner => p_owner
354 ,p_userid_clause => p_userid_clause);
355 END IF;
356
357 if( l_event_level >= l_debug_level ) then
358 fnd_log.string(fnd_log.level_event,
359 'per.plsql.'||gv_package||'.raise_wfevent', 'Leaving ...');
360 end if;
361
362 END raise_wfevent;
363
364
365 PROCEDURE revoke_block(
366 itemtype in varchar2
367 ,itemkey in varchar2
368 ,actid in number
369 ,funmode in varchar2
370 ,result in out nocopy varchar2)
371 IS
372 --local variables
373
374 BEGIN
375 -- Do nothing in cancel or timeout mode
376 if (funmode <> wf_engine.eng_run) then
377 result := wf_engine.eng_null;
378 return;
379 end if;
380 -- set the item attribute value with the current activity id
381 -- this will be used when the revoke access user notification is sent.
382 -- and to complete the blocked thread.
383 -- HR_REVOKE_ACCESS_USER_NAME
384 wf_engine.setitemattrnumber(itemtype,itemkey,'HR_REVOKE_ACCESS_BLOCK_ACTID',actid);
385 WF_STANDARD.BLOCK(itemtype,itemkey,actid,funmode,result);
386
387 --resultout := 'NOTIFIED';
388
389 EXCEPTION
390 WHEN OTHERS THEN
391 Wf_Core.Context(gv_package, '.revoke_block', itemtype,
392 itemkey, to_char(actid), funmode);
393 raise;
394 END revoke_block;
395
396 /*
397 This is test subscription function to test the event 'oracle.apps.per.selfservice.respowner.revoke_access'.
398 Uncomment to test, btw needs to create revoke_access_table though.
399 FUNCTION revoke_access_wfevent_subscrb
400 ( p_subscription_guid in raw,
401 p_event in out NOCOPY wf_event_t)
402 RETURN VARCHAR2
403 IS
404
405 I integer := 0;
406 usrIdCnt number := 0;
407 BEGIN
408 usrIdCnt := to_number(p_event.GetValueForParameter('USER_COUNT'));
409
410 FOR I IN 1 .. usrIdCnt LOOP
411 INSERT INTO REVOKE_ACCESS_TABLE (EVENT_KEY,
412 EVENT_NAME,
413 RESP_ID,
414 RESP_APPL_ID,
415 SECURITY_GROUP_ID,
416 USERID_COUNT,
417 MESSAGE,
418 USER_NAME)
419
420 VALUES(p_event.getEventKey,
421 p_event.getEventName,
422 to_number(p_event.GetValueForParameter('RESP_ID')),
423 to_number(p_event.GetValueForParameter('RESP_APPL_ID')),
424 to_number(p_event.GetValueForParameter('SECURITY_GROUP_ID')),
425 usrIdCnt,
426 p_event.GetValueForParameter('MESSAGE'),
427 p_event.GetValueForParameter('USER_NAME'||to_char(I)));
428 END LOOP;
429
430 COMMIT;
431
432 RETURN 'SUCCESS';
433
434 EXCEPTION
435 WHEN OTHERS THEN
436 WF_CORE.CONTEXT('PER_RESPOWNER_UTIL_SS', 'revoke_access_wfevent_subscrb', p_event.getEventName(), p_subscription_guid);
437 WF_EVENT.setErrorInfo(p_event, 'ERROR');
438 RETURN 'ERROR';
439 END revoke_access_wfevent_subscrb;
440 */
441
442
443 END PER_RESPOWNER_UTIL_SS;