[Home] [Help]
PACKAGE BODY: APPS.UMX_NOTIFICATION_UTIL
Source
1 PACKAGE BODY UMX_NOTIFICATION_UTIL as
2 /* $Header: UMXNTFSB.pls 120.10.12010000.2 2008/10/13 11:55:20 kkasibha ship $ */
3 -- Start of Comments
4 -- Package name : UMX_NOTIFICATION_UTIL
5 -- Purpose :
6 -- This package contains body for notification details
7
8 --
9 -- Procedure
10 -- Check_Context
11 --
12 -- Description
13 -- populate the wf_local_roles table with information from workflow
14 -- IN
15 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
16 -- itemkey - A string generated from the application object's primary key.
17 -- actid - The function activity (instance id).
18 -- funcmode - Run/Cancel/Timeout
19 -- OUT
20 -- resultout - result of the process based on which the next step is followed
21 procedure Check_Context (item_type in varchar2,
22 item_key in varchar2,
23 activity_id in number,
24 command in varchar2,
25 resultout out NOCOPY varchar2)IS
26
27 l_context varchar2 (30);
28 i number;
29 l_parameter_list wf_parameter_list_t := null;
30 l_event wf_event_t;
31 l_requested_username fnd_user.user_name%type;
32 l_return_status pls_integer;
33 l_registration_data wf_event_t;
34
35 BEGIN
36
37 if (command = 'RUN') then
38
39 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
40 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
41 'fnd.plsql.UMXNTFSB.CheckContext.begin', 'Begin');
42 end if;
43
44 /**
45 ** this is the first method print all the variables in the event obj
46 **/
47
48 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
49
50 l_event := wf_engine.getitemattrevent (itemtype => item_type,
51 itemkey => item_key,
52 name => 'REGISTRATION_DATA');
53
54 l_parameter_list := l_event.getparameterlist ();
55
56 for i in 1..l_parameter_list.count loop
57 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
58 'fnd.plsql.UMXNTFSB.check_context',
59 ' parameter name:'||l_parameter_list (i).getName ()||
60 ' value:'||l_parameter_list (i).getValue ());
61 end loop;
62 end if;
63
64 l_context := wf_engine.getitemattrtext (itemtype => item_type,
65 itemkey => item_key,
66 aname =>'NOTIFICATION_CONTEXT',
67 ignore_notfound => false);
68 if (l_context is not null) then
69
70 if l_context = 'IDENTITY_VERIFICATION' then
71 resultout := 'COMPLETE:IDENTITY_VERIFICATION';
72
73 elsif l_context = 'APPROVAL_REQUIRED' then
74 resultout := 'COMPLETE:APPROVAL_REQUIRED';
75
76 elsif l_context = 'APPROVAL_CONFIRMATION' then
77 -- We have to call the fnd_user_pkg.testusername api to find out
78 -- whether oid is enable. If that is the case, then we have to
79 -- send a differnet notification.
80 l_requested_username := wf_engine.getitemattrtext (itemtype => item_type,
81 itemkey => item_key,
82 aname =>'REQUESTED_USERNAME');
83 l_registration_data :=
84 wf_engine.getitemattrevent (item_type, item_key, 'REGISTRATION_DATA');
85
86 if (l_parameter_list is null) then
87 l_parameter_list := l_registration_data.getParameterList;
88 end if;
89
90 l_return_status := wf_event.GetValueForParameter (
91 p_name => 'TESTUSERNAME_RET_STATUS',
92 p_parameterlist => l_parameter_list);
93
94
95 if (l_return_status = fnd_user_pkg.user_synched) then
96 resultout := 'COMPLETE:APPROVAL_CONFIRMATION_SYNCHED';
97 else
98 resultout := 'COMPLETE:APPROVAL_CONFIRMATION';
99 end if;
100 elsif l_context = 'REJECTION' then
101 resultout := 'COMPLETE:REJECTION';
102 end if;
103
104 end if;
105
106 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
107 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
108 'fnd.plsql.UMXNTFSB.CheckContext.end', 'End');
109 end if;
110
111 end if;
112 END Check_Context;
113
114 --
115 -- Procedure
116 -- Notification_process_done
117 --
118 -- Description
119 -- populate the wf_local_roles table with information from workflow
120 -- IN
121 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
122 -- itemkey - A string generated from the application object's primary key.
123 -- actid - The function activity (instance id).
124 -- funcmode - Run/Cancel/Timeout
125 -- OUT
126 -- resultout - result of the process based on which the next step is followed
127 procedure Notification_Process_Done (item_type in varchar2,
128 item_key in varchar2,
129 activity_id in number,
130 command in varchar2,
131 resultout out NOCOPY varchar2) IS
132
133 l_registration_data wf_event_t;
134 --l_parameter_list wf_parameter_list_t;
135 l_parameter_list wf_parameter_list_t := wf_parameter_list_t(); --bug# 7110551
136 l_parent_itemkey WF_ITEMS.ITEM_KEY%TYPE;
137 l_approval_result varchar2 (30);
138 aname varchar2(30); --bug# 7110551
139 avalue varchar2(2000); --bug# 7110551
140 pList wf_parameter_list_t; --bug# 7110551
141 j number := 1; --bug# 7110551
142
143 BEGIN
144
145 if (command = 'RUN') then
146 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
147 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
148 'fnd.plsql.UMXNTFSB.notificationprocessdone.begin', 'Begin');
149 end if;
150
151 l_registration_data :=
152 wf_engine.getitemattrevent (item_type,item_key,'REGISTRATION_DATA');
153
154 l_approval_result :=
155 wf_engine.getItemattrtext (item_type,item_key,'APPROVAL_RESULT',false);
156
157 /*l_parameter_list := l_registration_data.getparameterlist ();*/ --bug# 7110551
158 /*Fix for bug# 7110551
159 Remove the unwanted parameter '#CONTEXT' which contains the value 'UMXREGWF:item_key' (where item_key is the item_key value for UMXREGWF workflow)
160 from the parameter list being passed to the event.
161 Because of this parameter the parent_item_key and item_key for UMXREGWF workflow
162 are being set to same value by the WF engine.
163 */
164 pList := l_registration_data.getparameterlist ();
165 j := 1;
166 for i in pList.first .. pList.last loop
167 aname := pList(i).GetName;
168 avalue := pList(i).GetValue;
169 begin
170 if aname <> '#CONTEXT' then
171 l_parameter_list.extend;
172 l_parameter_list(j) := WF_PARAMETER_T(aname,avalue);
173 j := j+1;
174 end if;
175 end;
176 end loop;
177
178 wf_event.addParametertoList ('APPROVAL_RESULT',l_approval_result,l_parameter_list);
179
180 l_parent_itemkey :=
181 wf_engine.getItemattrtext (item_type,item_key,'UMX_PARENT_ITEM_KEY',false);
182
183 wf_event.raise ('oracle.apps.fnd.umx.notificationdone', l_parent_itemkey,
184 null,l_parameter_list,sysdate);
185 resultout := 'COMPLETE';
186
187 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
188 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
189 'fnd.plsql.UMXNTFSB.notificationprocessdone.end', 'End');
190 end if;
191 end if;
192
193 EXCEPTION
194 WHEN OTHERS THEN
195 Wf_Core.Context ('UMX_NOTIFICATION_UTIL', 'notification_process_done',
196 item_type, item_key, activity_id);
197 raise;
198 END Notification_Process_Done;
199
200 -- Procedure
201 -- GetNextApprover
202 --
203 -- Description
204 -- populate the wf_local_roles table with information from workflow
205 -- IN
206 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
207 -- itemkey - A string generated from the application object's primary key.
208 -- actid - The function activity (instance id).
209 -- funcmode - Run/Cancel/Timeout
210 -- OUT
211 -- resultout - result of the process based on which the next step is followed
212 procedure GetNextApprover (item_type in varchar2,
213 item_key in varchar2,
214 activity_id in number,
215 command in varchar2,
216 resultout out NOCOPY varchar2) IS
217
218 l_ame_transaction_type_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
219 l_ame_application_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
220 l_reg_request_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
221 l_approver_name fnd_user.USER_NAME%TYPE;
222 l_display_name wf_users.display_name%type;
223 l_next_approver ame_util.approverRecord2;
224 l_requested_for_user_id fnd_user.user_id%type;
225 l_registration_data wf_event_t;
226 l_requested_for_party_id hz_parties.party_id%type;
227 l_user_role_name wf_local_roles.name%type;
228 l_person_first_name WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
229 l_person_last_name WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
230 l_person_middle_name WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
231 l_prefix WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
232 l_suffix WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
233 l_return_status varchar2 (10);
234 l_requester_email_address fnd_user.email_address%type;
235 l_role_display_name wf_local_roles.display_name%type;
236 l_reg_service_type WF_ACTIVITY_ATTRIBUTES.text_default%type;
237
238 l_msg_count number;
239 l_msg_data varchar2 (280);
240 l_formatted_lines_cnt number;
241 l_formatted_name_tbl hz_format_pub.string_tbl_type;
242 l_event wf_event_t;
243 l_status varchar2 (15);
244
245 cursor getusername (l_user_id in fnd_user.user_id%type) is
246 select user_name
247 from fnd_user where user_id = l_user_id;
248
249 BEGIN
250
251 if (command = 'RUN') then
252
253 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
254 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
255 'fnd.plsql.UMXNTFSB.getnextapprover.begin', 'Begin');
256 end if;
257
258 l_ame_application_id := wf_engine.getitemattrtext (
259 itemtype => item_type,
260 itemkey => item_key,
261 aname => 'AME_APPLICATION_ID',
262 ignore_notfound => false);
263
264 l_ame_transaction_type_id := wf_engine.getitemattrtext (
265 itemtype => item_type,
266 itemkey => item_key,
267 aname => 'AME_TRANSACTION_TYPE_ID',
268 ignore_notfound => false);
269
270 l_reg_request_id := wf_engine.getitemattrtext (
271 itemtype => item_type,
272 itemkey => item_key,
273 aname => 'UMX_PARENT_ITEM_KEY',
274 ignore_notfound => false);
275
276 l_next_approver := umx_reg_requests_pvt.getNextApproverPvt (p_ame_application_id => l_ame_application_id,
277 p_ame_transaction_type_id => l_ame_transaction_type_id,
278 p_reg_request_id => l_reg_request_id);
279 l_approver_name := l_next_approver.name;
280
281 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
282 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
283 'fnd.plsql.UMXNTFSB.getnextapprover',
284 'approver name:'|| l_approver_name);
285 end if;
286
287 if (l_approver_name is not null) then
288
289 wf_engine.setItemattrtext (itemtype => item_type,
290 itemkey => item_key,
291 aname => 'APPROVER_NAME',
292 avalue => l_approver_name);
293
294 l_user_role_name := wf_engine.getItemattrtext (
295 itemtype => item_type,
296 itemkey => item_key,
297 aname => 'USER_ROLE_NAME');
298
299 if (l_user_role_name is null) then
300
301 -- add the performer role in notification to be the username
302 -- who is requesting account
303 -- We have to first check to see if the requester has a user account.
304
305 l_reg_service_type :=
306 wf_engine.getItemattrtext (itemtype => item_type,
307 itemkey => item_key,
308 aname => 'REG_SERVICE_TYPE');
309
310 if ((l_reg_service_type = 'ADMIN_CREATION') or
311 (l_reg_service_type = 'SELF_SERVICE')) then
312
313 -- The requester doesn't have a user account and is requesting a
314 -- user account. Check if the person id exists. If the person ID
315 -- exists, then we will use the WF role of that person.
316
317 l_registration_data :=
318 wf_engine.getitemattrevent (item_type, item_key, 'REGISTRATION_DATA');
319 l_requested_for_party_id := wf_event.GetValueForParameter (
320 p_name => 'PERSON_PARTY_ID',
321 p_parameterlist => l_registration_data.getParameterList);
322
323 if (l_requested_for_party_id is not null) then
324
325 -- Get the Person WF Role
326 wf_directory.GetUserName (
327 p_orig_system => 'HZ_PARTY',
328 p_orig_system_id => l_requested_for_party_id,
329 p_name => l_user_role_name,
330 p_display_name => l_display_name);
331
332 else
333 -- l_requested_for_party_id is null. Create an ad hoc role.
334 l_person_first_name := wf_engine.getItemattrtext (
335 itemtype => item_type,
336 itemkey => item_key,
337 aname => 'FIRST_NAME');
338
339 l_person_last_name := wf_engine.getItemattrtext (
340 itemtype => item_type,
341 itemkey => item_key,
342 aname => 'LAST_NAME');
343
344 l_person_middle_name := wf_engine.getItemattrtext (
345 itemtype => item_type,
346 itemkey => item_key,
347 aname => 'MIDDLE_NAME');
348
349 l_suffix := wf_engine.getitemattrtext (
350 itemtype => item_type,
351 itemkey => item_key,
352 aname => 'PERSON_SUFFIX');
353
354 l_prefix := wf_engine.getitemattrtext (
355 itemtype => item_type,
356 itemkey => item_key,
357 aname => 'PRE_NAME_ADJUNCT');
358
359 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
360 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
361 'fnd.plsql.UMXNTFSB.getnextapprover',
362 'Before calling hz_format_pub.format_name (' ||
363 l_person_first_name || ',' || l_person_middle_name ||
364 l_person_last_name || ',' || l_prefix ||
365 l_suffix || ')');
366 end if;
367
368 hz_format_pub.format_name (
369 p_person_first_name => l_person_first_name ,
370 p_person_middle_name => l_person_middle_name,
371 p_person_last_name => l_person_last_name,
372 p_person_title => l_prefix,
373 p_person_name_suffix => l_suffix,
374 x_return_status => l_return_status,
375 x_msg_count => l_msg_count,
376 x_msg_data => l_msg_data,
377 x_formatted_name => l_role_display_name,
378 x_formatted_lines_cnt => l_formatted_lines_cnt,
379 x_formatted_name_tbl => l_formatted_name_tbl);
380
381 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
382 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
383 'fnd.plsql.UMXNTFSB.getnextapprover',
384 'After calling hz_format_pub.format_name (' ||
385 l_return_status || ',' || l_msg_count ||
386 l_role_display_name || ',' || l_formatted_lines_cnt || ')');
387 end if;
388
389 l_user_role_name := '~UMX_' || l_reg_request_id;
390
391 l_requester_email_address := wf_engine.getitemattrtext (
392 itemtype => item_type,
393 itemkey => item_key,
394 aname => 'EMAIL_ADDRESS');
395
396 wf_directory.CreateAdHocRole (role_name => l_user_role_name,
397 role_display_name => l_role_display_name,
398 email_address => l_requester_email_address,
399 owner_tag => 'FND');
400
401 end if;
402
403 else
404
405 -- REG_SERVICE_TYPE is ADDITIONAL_ACCESS
406 l_requested_for_user_id :=
407 wf_engine.getItemattrtext (itemtype => item_type,
408 itemkey => item_key,
409 aname => 'REQUESTED_FOR_USER_ID');
410
411 open getUserName (l_requested_for_user_id);
412 fetch getUserName into l_user_role_name;
413 close getUserName;
414
415 end if;
416
417 wf_engine.setItemattrtext (itemtype => item_type,
418 itemkey => item_key,
419 aname => 'USER_ROLE_NAME',
420 avalue => l_user_role_name);
421
422 l_event := wf_engine.getitemattrevent (itemtype => item_type,
423 itemkey => item_key,
424 name => 'REGISTRATION_DATA');
425
426 l_status := UMX_REGISTRATION_UTIL.set_event_object (
427 p_event => l_event,
428 p_attr_name => 'USER_ROLE_NAME',
429 p_attr_value => l_user_role_name);
430
431 wf_engine.setitemattrevent (itemtype => item_type,
432 itemkey => item_key,
433 name => 'REGISTRATION_DATA',
434 event => l_event);
435 end if;
436 resultout := 'COMPLETE:T';
437 else
438 resultout := 'COMPLETE:F';
439 end if;
440
441 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
442 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
443 'fnd.plsql.UMXNTFSB.getnextapprover.end', 'End');
444 end if;
445 end if;
446
447 EXCEPTION
448 WHEN others THEN
449 Wf_Core.Context ('UMX_NOTIFICATION_UTIL', 'getNextApprover', item_type, item_key);
450 raise;
451
452 END GetNextApprover;
453
454 -- Procedure
455 -- get_recipient_username
456 --
457 -- Description
458 -- Return the username of the notification recipient.
459 -- IN
460 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
461 -- itemkey - A string generated from the application object's primary key.
462 -- actid - The function activity (instance id).
463 -- funcmode - Run/Cancel/Timeout
464 -- OUT
465 -- resultout - result of the process based on which the next step is followed
466 procedure get_recipient_username (item_type in varchar2,
467 item_key in varchar2,
468 activity_id in number,
469 command in varchar2,
470 resultout out NOCOPY varchar2) is
471
472 --l_context WF_ACTIVITY_ATTRIBUTES.text_default%type;
473 l_display_name varchar2 (100);
474 l_first_name WF_ACTIVITY_ATTRIBUTES.text_default%type;
475 l_last_name WF_ACTIVITY_ATTRIBUTES.text_default%type;
476 l_middle_name WF_ACTIVITY_ATTRIBUTES.text_default%type;
477 l_suffix WF_ACTIVITY_ATTRIBUTES.text_default%type;
478 l_prefix WF_ACTIVITY_ATTRIBUTES.text_default%type;
479 l_requested_for_user_id WF_ACTIVITY_ATTRIBUTES.text_default%type;
480 l_username WF_ACTIVITY_ATTRIBUTES.text_default%type;
481 l_email_address WF_ACTIVITY_ATTRIBUTES.text_default%type;
482 l_primary_phone WF_ACTIVITY_ATTRIBUTES.text_default%type;
483 l_phone_area_code WF_ACTIVITY_ATTRIBUTES.text_default%type;
484 l_phone_country_code WF_ACTIVITY_ATTRIBUTES.text_default%type;
485 l_justification WF_ACTIVITY_ATTRIBUTES.text_default%type;
486 l_reg_service_type WF_ACTIVITY_ATTRIBUTES.text_default%type;
487 l_mins WF_ACTIVITY_ATTRIBUTES.number_default%type;
488 l_days WF_ACTIVITY_ATTRIBUTES.number_default%type;
489 l_registration_data wf_event_t;
490 l_person_party_id hz_parties.party_id%type;
491
492 x_return_status varchar2 (10);
493 x_msg_count number;
494 x_msg_data varchar2 (280);
495 x_formatted_name varchar2 (300);
496 x_formatted_phone varchar2 (300);
497 x_formatted_lines_cnt number;
498 x_formatted_name_tbl hz_format_pub.string_tbl_type;
499
500 cursor get_username_from_userid (p_user_id in number) is
501 select fu.user_name, fu.email_address, hzp.Person_first_name,
502 hzp.Person_last_name, hzp.Person_middle_name, hzp.person_Name_suffix,
503 hzp.person_pre_name_adjunct
504 from fnd_user fu, hz_parties hzp
505 where fu.user_id = p_user_id
506 and hzp.party_id = fu.person_party_id;
507
508
509 begin
510
511 if (command = 'RUN') then
512
513 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
514 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
515 'fnd.plsql.UMXNTFSB.getreceipientusername.begin', 'Begin');
516 end if;
517
518 l_first_name := wf_engine.getitemattrtext (
519 itemtype => item_type,
520 itemkey => item_key,
521 aname =>'FIRST_NAME',
522 ignore_notfound => true);
523
524 l_last_name := wf_engine.getitemattrtext (
525 itemtype => item_type,
526 itemkey => item_key,
527 aname =>'LAST_NAME',
528 ignore_notfound => true);
529
530 l_middle_name := wf_engine.getitemattrtext (
531 itemtype => item_type,
532 itemkey => item_key,
533 aname =>'MIDDLE_NAME',
534 ignore_notfound => true);
535
536 l_suffix := wf_engine.getitemattrtext (
537 itemtype => item_type,
538 itemkey => item_key,
539 aname =>'PERSON_SUFFIX',
540 ignore_notfound => true);
541
542 l_prefix := wf_engine.getitemattrtext (
543 itemtype => item_type,
544 itemkey => item_key,
545 aname =>'PRE_NAME_ADJUNCT',
546 ignore_notfound => true);
547
548 l_requested_for_user_id := wf_engine.getitemattrtext (
549 itemtype => item_type,
550 itemkey => item_key,
551 aname =>'REQUESTED_FOR_USER_ID',
552 ignore_notfound => false);
553
554 l_username := wf_engine.getitemattrtext (
555 itemtype => item_type,
556 itemkey => item_key,
557 aname =>'REQUESTED_USERNAME',
558 ignore_notfound => false);
559
560 l_reg_service_type := wf_engine.getitemattrtext (
561 itemtype => item_type,
562 itemkey => item_key,
563 aname =>'REG_SERVICE_TYPE',
564 ignore_notfound => false);
565
566
567 /**
568 ** this is for additional access workflow where username will not be passed
569 **/
570 if (((l_reg_service_type = 'ADDITIONAL_ACCESS') or
571 (l_reg_service_type = 'ADMIN_ADDITIONAL_ACCESS')) and
572 (l_requested_for_user_id is not null)) then
573
574 open get_username_from_userid (l_requested_for_user_id);
575 fetch get_username_from_userid
576 into l_username,l_email_address,l_first_name,
577 l_last_name,l_middle_name,l_suffix,l_prefix;
578
579 wf_engine.setItemattrtext (
580 itemtype => item_type,
581 itemkey => item_key,
582 aname => 'REQUESTED_USERNAME',
583 avalue => l_username);
584
585 wf_engine.setItemattrtext (
586 itemtype => item_type,
587 itemkey => item_key,
588 aname => 'EMAIL_ADDRESS',
589 avalue => l_email_address);
590
591 if (get_username_from_userid%notfound) then
592 close get_username_from_userid;
593 raise_application_error ('-20000','invalid userid to send notification.');
594 end if;
595
596 close get_username_from_userid;
597
598 end if;
599
600 hz_format_pub.format_name (
601 p_person_first_name =>l_first_name ,
602 p_person_middle_name => l_middle_name,
603 p_person_last_name => l_last_name,
604 p_person_title => l_prefix,
605 p_person_name_suffix => l_suffix,
606 x_return_status => x_return_status,
607 x_msg_count => x_msg_count,
608 x_msg_data => x_msg_data,
609 x_formatted_name => x_formatted_name,
610 x_formatted_lines_cnt => x_formatted_lines_cnt,
611 x_formatted_name_tbl => x_formatted_name_tbl);
612
613 wf_engine.setItemattrtext (
614 itemtype => item_type,
615 itemkey => item_key,
616 aname => 'USER_DISPLAY_NAME',
617 avalue => LOWER (l_username));
618
619 if (x_formatted_name is null) then
620 -- The formatted name is missing due to first name, last name, etc are missing.
621 -- Get the formatted name from person party id
622 -- Get the person party id from the event message
623 l_registration_data :=
624 wf_engine.getitemattrevent (item_type, item_key, 'REGISTRATION_DATA');
625 l_person_party_id := wf_event.GetValueForParameter (
626 p_name => 'PERSON_PARTY_ID',
627 p_parameterlist => l_registration_data.getParameterList);
628 x_formatted_name := hz_format_pub.format_name (p_party_id => l_person_party_id);
629 end if;
630
631 wf_engine.setItemattrtext (
632 itemtype => item_type,
633 itemkey => item_key,
634 aname => 'FORMATED_NAME',
635 avalue => x_formatted_name);
636
637 -- getting a formated phone number
638
639 l_primary_phone := wf_engine.getitemattrtext (
640 itemtype => item_type,
641 itemkey => item_key,
642 aname =>'PRIMARY_PHONE',
643 ignore_notfound => false);
644
645 l_phone_country_code := wf_engine.getitemattrtext (
646 itemtype => item_type,
647 itemkey => item_key,
648 aname =>'COUNTRY_CODE',
649 ignore_notfound => false);
650
651 l_phone_area_code := wf_engine.getitemattrtext (
652 itemtype => item_type,
653 itemkey => item_key,
654 aname =>'AREA_CODE',
655 ignore_notfound => false);
656
657 -- phone extension is not available in phone formating
658
659 HZ_FORMAT_PHONE_V2PUB.phone_display (
660 p_phone_country_code => l_phone_country_code,
661 p_phone_area_code => l_phone_area_code,
662 p_phone_number => l_primary_phone,
663 x_formatted_phone_number => x_formatted_phone,
664 x_return_status => x_return_status,
665 x_msg_count => x_msg_count,
666 x_msg_data => x_msg_data );
667
668 wf_engine.setItemattrtext (
669 itemtype => item_type,
670 itemkey => item_key,
671 aname => 'FORMATED_PHONE',
672 avalue => x_formatted_phone);
673 --justification
674 l_justification := wf_engine.getitemattrtext (
675 itemtype => item_type,
676 itemkey => item_key,
677 aname =>'JUSTIFICATION',
678 ignore_notfound => false);
679
680 if (l_justification is null ) then
681
682 wf_engine.setItemattrtext (
683 itemtype => item_type,
684 itemkey => item_key,
685 aname => 'JUSTIFICATION',
686 avalue => fnd_message.get_string ('FND','UMX_NOT_AVAIL'));
687 end if;
688
689 --convert the mins timeout to days.
690 l_mins := wf_engine.GetItemAttrNumber (
691 itemtype => item_type,
692 itemkey => item_key,
693 aname =>'MINS_TO_TIMEOUT',
694 ignore_notfound => true);
695 if (l_mins > 0) then
696 l_days := l_mins / 1440;
697 wf_engine.setItemattrtext (
698 itemtype => item_type,
699 itemkey => item_key,
700 aname => 'DAYS_TO_TIMEOUT',
701 avalue => l_days);
702 end if;
703
704 resultout := 'COMPLETE:';
705
706 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
707 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
708 'fnd.plsql.UMXNTFSB.getrecipientusername.end', 'End');
709 end if;
710
711 end if; --command = run
712
713 EXCEPTION
714 WHEN others THEN
715 Wf_Core.Context ('UMX_NOTIFICATION_UTIL', 'getRecipientUsername', item_type, item_key);
716 raise;
717 end get_recipient_username;
718
719 procedure throw_exception (item_type in varchar2,
720 item_key in varchar2,
721 activity_id in number,
722 command in varchar2,
723 resultout out NOCOPY varchar2) is
724 begin
725 if (command = 'RUN') then
726 raise_application_error ('-20000', 'error out');
727 end if;
728 end throw_exception;
729
730 procedure UpdateApprovalStatus (item_type in varchar2,
731 item_key in varchar2,
732 activity_id in number,
733 command in varchar2,
734 resultout out NOCOPY varchar2) IS
735
736 l_ame_transaction_type_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
737 l_ame_application_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
738 l_reg_request_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
739 l_current_approver ame_util.approverRecord2;
740
741 BEGIN
742
743 if (command = 'RUN') then
744
745 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
746 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
747 'fnd.plsql.UMXNTFSB.updateapprovalstatus.begin', 'Begin');
748 end if;
749
750 -- get the next approver record again, this will not increment
751 -- approver chain it returns the same approver
752 l_ame_application_id := wf_engine.getitemattrtext (
753 itemtype => item_type,
754 itemkey => item_key,
755 aname =>'AME_APPLICATION_ID',
756 ignore_notfound => false);
757
758 l_ame_transaction_type_id := wf_engine.getitemattrtext (
759 itemtype => item_type,
760 itemkey => item_key,
761 aname =>'AME_TRANSACTION_TYPE_ID',
762 ignore_notfound => false);
763
764 l_reg_request_id := wf_engine.getitemattrtext (
765 itemtype => item_type,
766 itemkey => item_key,
767 aname =>'UMX_PARENT_ITEM_KEY',
768 ignore_notfound => false);
769
770 --populate the l_current_approver record
771 l_current_approver := umx_reg_requests_pvt.getNextApproverPvt (p_ame_application_id => l_ame_application_id,
772 p_ame_transaction_type_id => l_ame_transaction_type_id,
773 p_reg_request_id => l_reg_request_id);
774
775 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
776 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
777 'fnd.plsql.UMXNTFSB.updateapprovalstatus',
778 'approver username:'|| l_current_approver.name);
779 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
780 'fnd.plsql.UMXNTFSB.updateapprovalstatus',
781 'Before calling ame_api2.updateapprovalstatus (' ||
782 l_ame_application_id || ',' || l_ame_transaction_type_id || ',' ||
783 l_reg_request_id || ',' || l_current_approver.name || ')');
784 end if;
785
786 l_current_approver.approval_status := ame_util.approvedStatus;
787 ame_api2.updateapprovalstatus (applicationIdIn => l_ame_application_id,
788 transactionTypeIn => l_ame_transaction_type_id,
789 transactionIdIn => l_reg_request_id,
790 approverIn => l_current_approver);
791
792 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
793 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
794 'fnd.plsql.UMXNTFSB.updateapprovalstatus',
795 'After calling ame_api2.updateapprovalstatus.');
796 end if;
797
798 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
799 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
800 'fnd.plsql.UMXNTFSB.updateapprovalstatus.end', 'End');
801 end if;
802
803 end if;
804
805 EXCEPTION
806 WHEN others THEN
807 Wf_Core.Context ('UMX_NOTIFICATION_UTIL', 'updateApprovalStatus', item_type, item_key);
808 raise;
809
810 END UpdateApprovalStatus;
811
812 procedure UpdateRejectedStatus (item_type in varchar2,
813 item_key in varchar2,
814 activity_id in number,
815 command in varchar2,
816 resultout out NOCOPY varchar2) IS
817
818 l_ame_transaction_type_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
819 l_ame_application_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
820 l_reg_request_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
821 l_rejection_reason WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
822 l_current_approver ame_util.approverRecord2;
823 l_event wf_event_t;
824 l_status varchar2 (15);
825
826 BEGIN
827
828 if (command = 'RUN') then
829
830 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
831 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
832 'fnd.plsql.UMXNTFSB.updateRejectedStatus.begin', 'Begin');
833 end if;
834
835 -- get the next approver record again, this will not increment
836 -- approver chain it returns the same approver
837 l_ame_application_id := wf_engine.getitemattrtext (itemtype => item_type,
838 itemkey => item_key,
839 aname =>'AME_APPLICATION_ID',
840 ignore_notfound => false);
841 l_ame_transaction_type_id := wf_engine.getitemattrtext (itemtype => item_type,
842 itemkey => item_key,
843 aname =>'AME_TRANSACTION_TYPE_ID',
844 ignore_notfound => false);
845 l_reg_request_id := wf_engine.getitemattrtext (itemtype => item_type,
846 itemkey => item_key,
847 aname =>'UMX_PARENT_ITEM_KEY',
848 ignore_notfound => false);
849 l_rejection_reason := wf_engine.getitemattrtext (itemtype => item_type,
850 itemkey => item_key,
851 aname =>'WF_NOTE',
852 ignore_notfound => false);
853
854 --populate the l_current_approver record
855 l_current_approver := umx_reg_requests_pvt.getNextApproverPvt (p_ame_application_id => l_ame_application_id,
856 p_ame_transaction_type_id => l_ame_transaction_type_id,
857 p_reg_request_id => l_reg_request_id);
858
859 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
860 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
861 'fnd.plsql.UMXNTFSB.updateRejectedStatus',
862 'approver username:'|| l_current_approver.name);
863 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
864 'fnd.plsql.UMXNTFSB.updateRejectedStatus',
865 'Before calling ame_api2.updateapprovalstatus (' ||
866 l_ame_application_id || ',' || l_ame_transaction_type_id || ',' ||
867 l_reg_request_id || ',' || l_current_approver.name || ')');
868 end if;
869
870 l_current_approver.approval_status := ame_util.rejectStatus;
871 ame_api2.updateapprovalstatus (applicationIdIn => l_ame_application_id,
872 transactionTypeIn => l_ame_transaction_type_id,
873 transactionIdIn => l_reg_request_id,
874 approverIn => l_current_approver);
875
876 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
877 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
878 'fnd.plsql.UMXNTFSB.updateRejectedStatus',
879 'After calling ame_api2.updateapprovalstatus.');
880 end if;
881
882 -- populate the rejected reason into event object, and main wf
883 -- so that, this will go into rejection notification
884 l_event := wf_engine.getitemattrevent (itemtype => item_type,
885 itemkey => item_key,
886 name => 'REGISTRATION_DATA');
887
888 l_status := UMX_REGISTRATION_UTIL.set_event_object (
889 p_event => l_event,
890 p_attr_name => 'WF_NOTE',
891 p_attr_value => l_rejection_reason);
892
893 wf_engine.setitemattrevent (itemtype => item_type,
894 itemkey => item_key,
895 name => 'REGISTRATION_DATA',
896 event => l_event);
897
898 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
899 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
900 'fnd.plsql.UMXNTFSB.updaterejectedstatus.end', 'End');
901 end if;
902
903 end if;
904
905 EXCEPTION
906 WHEN others THEN
907 wf_core.context ('UMX_NOTIFICATION_UTIL', 'UpdateRejectedStatus', item_type, item_key);
908 raise;
909 END UpdateRejectedStatus;
910
911 -- Procedure
912 -- query_role_display_name
913 --
914 -- Description
915 -- query the wf_local_roles table for role_display_name
916 -- also query the username for this request if it was not passed (ART,SMART)
917 -- IN
918 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
919 -- itemkey - A string generated from the application object's primary key.
920 -- actid - The function activity (instance id).
921 -- funcmode - Run/Cancel/Timeout
922 -- OUT
923 -- resultout - result of the process based on which the next step is followed
924 procedure query_role_display_name (item_type in varchar2,
925 item_key in varchar2,
926 activity_id in number,
927 command in varchar2,
928 resultout out NOCOPY varchar2) is
929
930 l_role_name wf_local_roles.name%type;
931 l_role_display_name wf_all_roles_vl.display_name%type;
932
933 BEGIN
934
935 if (command = 'RUN') then
936
937 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
938 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
939 'fnd.plsql.UMXNTFSB.queryroledisplayname.begin', 'Begin');
940 end if;
941
942 l_role_name := wf_engine.getitemattrtext (itemtype => item_type,
943 itemkey => item_key,
944 aname =>'WF_ROLE_NAME',
945 ignore_notfound => false);
946
947 if (l_role_name is not null) then
948
949 begin
950 select display_name into l_role_display_name
951 from wf_all_roles_vl
952 where name = l_role_name;
953 exception
954 when NO_DATA_FOUND THEN
955 l_role_display_name :='';
956 end;
957
958 end if;
959
960 if (l_role_display_name is not null) then
961 wf_engine.setitemattrtext (itemtype => item_type,
962 itemkey => item_key,
963 aname => 'ROLE_DISPLAY_NAME',
964 avalue => l_role_display_name);
965 end if;
966
967 resultout := 'COMPLETE';
968
969 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
970 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
971 'fnd.plsql.UMXNTFSB.queryRoleDisplayName.end',
972 'roleDisplayName:'|| l_role_display_name);
973 end if;
974 end if;
975
976 END query_role_display_name;
977
978 end UMX_NOTIFICATION_UTIL;