[Home] [Help]
PACKAGE BODY: APPS.UMX_NOTIFICATION_UTIL
Source
1 PACKAGE BODY UMX_NOTIFICATION_UTIL as
2 /* $Header: UMXNTFSB.pls 120.10.12010000.3 2008/12/15 16:37:44 jstyles 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 if (l_person_party_id is null) then
629 x_formatted_name := l_username;
630 else
631 x_formatted_name := hz_format_pub.format_name (p_party_id => l_person_party_id);
632 end if;
633 end if;
634
635 wf_engine.setItemattrtext (
636 itemtype => item_type,
637 itemkey => item_key,
638 aname => 'FORMATED_NAME',
639 avalue => x_formatted_name);
640
641 -- getting a formated phone number
642
643 l_primary_phone := wf_engine.getitemattrtext (
644 itemtype => item_type,
645 itemkey => item_key,
646 aname =>'PRIMARY_PHONE',
647 ignore_notfound => false);
648
649 l_phone_country_code := wf_engine.getitemattrtext (
650 itemtype => item_type,
651 itemkey => item_key,
652 aname =>'COUNTRY_CODE',
653 ignore_notfound => false);
654
655 l_phone_area_code := wf_engine.getitemattrtext (
656 itemtype => item_type,
657 itemkey => item_key,
658 aname =>'AREA_CODE',
659 ignore_notfound => false);
660
661 -- phone extension is not available in phone formating
662
663 HZ_FORMAT_PHONE_V2PUB.phone_display (
664 p_phone_country_code => l_phone_country_code,
665 p_phone_area_code => l_phone_area_code,
666 p_phone_number => l_primary_phone,
667 x_formatted_phone_number => x_formatted_phone,
668 x_return_status => x_return_status,
669 x_msg_count => x_msg_count,
670 x_msg_data => x_msg_data );
671
672 wf_engine.setItemattrtext (
673 itemtype => item_type,
674 itemkey => item_key,
675 aname => 'FORMATED_PHONE',
676 avalue => x_formatted_phone);
677 --justification
678 l_justification := wf_engine.getitemattrtext (
679 itemtype => item_type,
680 itemkey => item_key,
681 aname =>'JUSTIFICATION',
682 ignore_notfound => false);
683
684 if (l_justification is null ) then
685
686 wf_engine.setItemattrtext (
687 itemtype => item_type,
688 itemkey => item_key,
689 aname => 'JUSTIFICATION',
690 avalue => fnd_message.get_string ('FND','UMX_NOT_AVAIL'));
691 end if;
692
693 --convert the mins timeout to days.
694 l_mins := wf_engine.GetItemAttrNumber (
695 itemtype => item_type,
696 itemkey => item_key,
697 aname =>'MINS_TO_TIMEOUT',
698 ignore_notfound => true);
699 if (l_mins > 0) then
700 l_days := l_mins / 1440;
701 wf_engine.setItemattrtext (
702 itemtype => item_type,
703 itemkey => item_key,
704 aname => 'DAYS_TO_TIMEOUT',
705 avalue => l_days);
706 end if;
707
708 resultout := 'COMPLETE:';
709
710 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
711 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
712 'fnd.plsql.UMXNTFSB.getrecipientusername.end', 'End');
713 end if;
714
715 end if; --command = run
716
717 EXCEPTION
718 WHEN others THEN
719 Wf_Core.Context ('UMX_NOTIFICATION_UTIL', 'getRecipientUsername', item_type, item_key);
720 raise;
721 end get_recipient_username;
722
723 procedure throw_exception (item_type in varchar2,
724 item_key in varchar2,
725 activity_id in number,
726 command in varchar2,
727 resultout out NOCOPY varchar2) is
728 begin
729 if (command = 'RUN') then
730 raise_application_error ('-20000', 'error out');
731 end if;
732 end throw_exception;
733
734 procedure UpdateApprovalStatus (item_type in varchar2,
735 item_key in varchar2,
736 activity_id in number,
737 command in varchar2,
738 resultout out NOCOPY varchar2) IS
739
740 l_ame_transaction_type_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
741 l_ame_application_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
742 l_reg_request_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
743 l_current_approver ame_util.approverRecord2;
744
745 BEGIN
746
747 if (command = 'RUN') then
748
749 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
750 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
751 'fnd.plsql.UMXNTFSB.updateapprovalstatus.begin', 'Begin');
752 end if;
753
754 -- get the next approver record again, this will not increment
755 -- approver chain it returns the same approver
756 l_ame_application_id := wf_engine.getitemattrtext (
757 itemtype => item_type,
758 itemkey => item_key,
759 aname =>'AME_APPLICATION_ID',
760 ignore_notfound => false);
761
762 l_ame_transaction_type_id := wf_engine.getitemattrtext (
763 itemtype => item_type,
764 itemkey => item_key,
765 aname =>'AME_TRANSACTION_TYPE_ID',
766 ignore_notfound => false);
767
768 l_reg_request_id := wf_engine.getitemattrtext (
769 itemtype => item_type,
770 itemkey => item_key,
771 aname =>'UMX_PARENT_ITEM_KEY',
772 ignore_notfound => false);
773
774 --populate the l_current_approver record
775 l_current_approver := umx_reg_requests_pvt.getNextApproverPvt (p_ame_application_id => l_ame_application_id,
776 p_ame_transaction_type_id => l_ame_transaction_type_id,
777 p_reg_request_id => l_reg_request_id);
778
779 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
780 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
781 'fnd.plsql.UMXNTFSB.updateapprovalstatus',
782 'approver username:'|| l_current_approver.name);
783 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
784 'fnd.plsql.UMXNTFSB.updateapprovalstatus',
785 'Before calling ame_api2.updateapprovalstatus (' ||
786 l_ame_application_id || ',' || l_ame_transaction_type_id || ',' ||
787 l_reg_request_id || ',' || l_current_approver.name || ')');
788 end if;
789
790 l_current_approver.approval_status := ame_util.approvedStatus;
791 ame_api2.updateapprovalstatus (applicationIdIn => l_ame_application_id,
792 transactionTypeIn => l_ame_transaction_type_id,
793 transactionIdIn => l_reg_request_id,
794 approverIn => l_current_approver);
795
796 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
797 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
798 'fnd.plsql.UMXNTFSB.updateapprovalstatus',
799 'After calling ame_api2.updateapprovalstatus.');
800 end if;
801
802 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
803 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
804 'fnd.plsql.UMXNTFSB.updateapprovalstatus.end', 'End');
805 end if;
806
807 end if;
808
809 EXCEPTION
810 WHEN others THEN
811 Wf_Core.Context ('UMX_NOTIFICATION_UTIL', 'updateApprovalStatus', item_type, item_key);
812 raise;
813
814 END UpdateApprovalStatus;
815
816 procedure UpdateRejectedStatus (item_type in varchar2,
817 item_key in varchar2,
818 activity_id in number,
819 command in varchar2,
820 resultout out NOCOPY varchar2) IS
821
822 l_ame_transaction_type_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
823 l_ame_application_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
824 l_reg_request_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
825 l_rejection_reason WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
826 l_current_approver ame_util.approverRecord2;
827 l_event wf_event_t;
828 l_status varchar2 (15);
829
830 BEGIN
831
832 if (command = 'RUN') then
833
834 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
835 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
836 'fnd.plsql.UMXNTFSB.updateRejectedStatus.begin', 'Begin');
837 end if;
838
839 -- get the next approver record again, this will not increment
840 -- approver chain it returns the same approver
841 l_ame_application_id := wf_engine.getitemattrtext (itemtype => item_type,
842 itemkey => item_key,
843 aname =>'AME_APPLICATION_ID',
844 ignore_notfound => false);
845 l_ame_transaction_type_id := wf_engine.getitemattrtext (itemtype => item_type,
846 itemkey => item_key,
847 aname =>'AME_TRANSACTION_TYPE_ID',
848 ignore_notfound => false);
849 l_reg_request_id := wf_engine.getitemattrtext (itemtype => item_type,
850 itemkey => item_key,
851 aname =>'UMX_PARENT_ITEM_KEY',
852 ignore_notfound => false);
853 l_rejection_reason := wf_engine.getitemattrtext (itemtype => item_type,
854 itemkey => item_key,
855 aname =>'WF_NOTE',
856 ignore_notfound => false);
857
858 --populate the l_current_approver record
859 l_current_approver := umx_reg_requests_pvt.getNextApproverPvt (p_ame_application_id => l_ame_application_id,
860 p_ame_transaction_type_id => l_ame_transaction_type_id,
861 p_reg_request_id => l_reg_request_id);
862
863 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
864 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
865 'fnd.plsql.UMXNTFSB.updateRejectedStatus',
866 'approver username:'|| l_current_approver.name);
867 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
868 'fnd.plsql.UMXNTFSB.updateRejectedStatus',
869 'Before calling ame_api2.updateapprovalstatus (' ||
870 l_ame_application_id || ',' || l_ame_transaction_type_id || ',' ||
871 l_reg_request_id || ',' || l_current_approver.name || ')');
872 end if;
873
874 l_current_approver.approval_status := ame_util.rejectStatus;
875 ame_api2.updateapprovalstatus (applicationIdIn => l_ame_application_id,
876 transactionTypeIn => l_ame_transaction_type_id,
877 transactionIdIn => l_reg_request_id,
878 approverIn => l_current_approver);
879
880 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
881 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
882 'fnd.plsql.UMXNTFSB.updateRejectedStatus',
883 'After calling ame_api2.updateapprovalstatus.');
884 end if;
885
886 -- populate the rejected reason into event object, and main wf
887 -- so that, this will go into rejection notification
888 l_event := wf_engine.getitemattrevent (itemtype => item_type,
889 itemkey => item_key,
890 name => 'REGISTRATION_DATA');
891
892 l_status := UMX_REGISTRATION_UTIL.set_event_object (
893 p_event => l_event,
894 p_attr_name => 'WF_NOTE',
895 p_attr_value => l_rejection_reason);
896
897 wf_engine.setitemattrevent (itemtype => item_type,
898 itemkey => item_key,
899 name => 'REGISTRATION_DATA',
900 event => l_event);
901
902 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
903 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
904 'fnd.plsql.UMXNTFSB.updaterejectedstatus.end', 'End');
905 end if;
906
907 end if;
908
909 EXCEPTION
910 WHEN others THEN
911 wf_core.context ('UMX_NOTIFICATION_UTIL', 'UpdateRejectedStatus', item_type, item_key);
912 raise;
913 END UpdateRejectedStatus;
914
915 -- Procedure
916 -- query_role_display_name
917 --
918 -- Description
919 -- query the wf_local_roles table for role_display_name
920 -- also query the username for this request if it was not passed (ART,SMART)
921 -- IN
922 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
923 -- itemkey - A string generated from the application object's primary key.
924 -- actid - The function activity (instance id).
925 -- funcmode - Run/Cancel/Timeout
926 -- OUT
927 -- resultout - result of the process based on which the next step is followed
928 procedure query_role_display_name (item_type in varchar2,
929 item_key in varchar2,
930 activity_id in number,
931 command in varchar2,
932 resultout out NOCOPY varchar2) is
933
934 l_role_name wf_local_roles.name%type;
935 l_role_display_name wf_all_roles_vl.display_name%type;
936
937 BEGIN
938
939 if (command = 'RUN') then
940
941 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
942 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
943 'fnd.plsql.UMXNTFSB.queryroledisplayname.begin', 'Begin');
944 end if;
945
946 l_role_name := wf_engine.getitemattrtext (itemtype => item_type,
947 itemkey => item_key,
948 aname =>'WF_ROLE_NAME',
949 ignore_notfound => false);
950
951 if (l_role_name is not null) then
952
953 begin
954 select display_name into l_role_display_name
955 from wf_all_roles_vl
956 where name = l_role_name;
957 exception
958 when NO_DATA_FOUND THEN
959 l_role_display_name :='';
960 end;
961
962 end if;
963
964 if (l_role_display_name is not null) then
965 wf_engine.setitemattrtext (itemtype => item_type,
966 itemkey => item_key,
967 aname => 'ROLE_DISPLAY_NAME',
968 avalue => l_role_display_name);
969 end if;
970
971 resultout := 'COMPLETE';
972
973 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
974 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
975 'fnd.plsql.UMXNTFSB.queryRoleDisplayName.end',
976 'roleDisplayName:'|| l_role_display_name);
977 end if;
978 end if;
979
980 END query_role_display_name;
981
982 end UMX_NOTIFICATION_UTIL;