[Home] [Help]
PACKAGE BODY: APPS.UMX_REGISTRATION_UTIL
Source
1 package body UMX_REGISTRATION_UTIL as
2 /* $Header: UMXUTILB.pls 120.18 2011/04/12 10:34:13 spakanat ship $ */
3
4 -- Procedure
5 -- check_approval_status
6 -- (DEPRECATED API)
7 -- Description
8 -- check if request has been approved or not
9 -- IN
10 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
11 -- itemkey - A string generated from the application object's primary key.
12 -- actid - The function activity (instance id).
13 -- funcmode - Run/Cancel/Timeout
14 -- OUT
15 -- resultout - result of the process based on which the next step is followed
16 procedure Check_Approval_Status (item_type in varchar2,
17 item_key in varchar2,
18 activity_id in number,
19 command in varchar2,
20 resultout out NOCOPY varchar2) is
21 l_approval_result varchar2 (30);
22 BEGIN
23 /*
24 **logging enter of method
25 */
26
27 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
28 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
29 'fnd.plsql.UMXUTILB.checkApprovalStatus.begin','');
30 end if;
31
32 if (command = 'RUN') then
33 l_approval_result := wf_engine.getitemattrtext (itemtype => item_type,
34 itemkey => item_key,
35 aname => 'APPROVAL_RESULT',
36 ignore_notfound => false);
37
38 if (l_approval_result is not null and l_approval_result = 'APPROVED') then
39 resultout := 'COMPLETE:APPROVED';
40 else
41 resultout := 'COMPLETE:REJECTED';
42 end if;
43 end if;
44 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
45 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
46 'fnd.plsql.UMXUTILB.checkApprovalStatus.end','');
47 end if;
48 END Check_Approval_Status;
49
50 -- function to check if approval workflow has to be launched or not
51 -- commenting out isadmin until decision is made on how to handle it
52 -- will always return false.
53 function check_admin_priv (l_requested_by_user_id in number)
54 return boolean is
55
56 begin
57
58
59 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
60 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
61 'fnd.plsql.UMXUTILB.checkadminpriv.begin',
62 'RequestedByUserid: '||l_requested_by_user_id);
63 end if;
64
65 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
66 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
67 'fnd.plsql.UMXUTILB.checkadminpriv.end','Return false');
68 end if;
69
70 return false;
71 end check_admin_priv;
72
73
74 --Function
75 --Check_admin_grants
76 -- this function checks if the requested_by_user_id has the grants
77 -- and that will skip the approval for additional access regservices.
78
79 function check_admin_grants (l_requested_by_user_id in number) return boolean is
80
81 cursor getUsername is
82 select user_name
83 from fnd_user
84 where user_id = l_requested_by_user_id
85 and nvl (start_date, SYSDATE) <= SYSDATE
86 and nvl (end_date, SYSDATE + 1) > SYSDATE ;
87
88 count int;
89 priv boolean;
90 l_username FND_USER.USER_NAME%TYPE;
91
92 begin
93
94
95 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
96 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
97 'fnd.plsql.UMXUTILB.checkadmingrants.begin',
98 'RequestedByUserid: '||l_requested_by_user_id);
99 end if;
100
101 open getUsername;
102 fetch getUsername into l_username;
103
104 if (getUsername%notfound) then
105 close getUsername;
106 raise_application_error ('-20000',' Invalid Requested_by_user_id was passed to check admin priv');
107 end if;
108 close getUsername;
109
110 priv := fnd_function.test_instance (function_name => 'UMX_SYSTEM_ACCT_ADMINSTRATION',
111 user_name => l_username);
112
113 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
114 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
115 'fnd.plsql.UMXUTILB.checkadmingrants.end',
116 'return : ');
117 end if;
118
119 return priv;
120
121
122
123 end check_admin_grants;
124
125 -- Procedure
126 -- print_Event_params
127 -- prints all the event obj params
128 --
129 procedure print_Event_params (p_event in wf_event_t) is
130 l_parameter_list wf_parameter_list_t;
131 i number ;
132 BEGIN
133
134 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
135 l_parameter_list := p_event.getparameterlist ();
136 for i in 1..l_parameter_list.count loop
137 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
138 'fnd.plsql.UMXUTILB.print','name:'||l_parameter_list (i).getName ()||
139 ' value:'||l_parameter_list (i).getValue ());
140 end loop;
141 end if;
142
143 END print_event_params;
144
145 --
146 -- Procedure
147 -- add_Param_to_Event
148 -- adds the parameter name value pair to the event object
149 --
150
151 Procedure add_param_to_event (p_item_type in varchar2,
152 p_item_key in varchar2,
153 p_attr_name in varchar2,
154 p_attr_value in varchar2) is
155 l_event wf_event_t;
156 BEGIN
157 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
158 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
159 'fnd.plsql.UMXUTILB.addparamtoevent.begin','');
160 end if;
161
162 l_event := wf_engine.getitemattrevent (itemtype => p_item_type,
163 itemkey => p_item_key,
164 name => 'REGISTRATION_DATA');
165 l_event.addParametertoList (p_attr_name,p_attr_value);
166 wf_engine.setitemattrevent (itemtype => p_item_type,
167 itemkey => p_item_key,
168 name => 'REGISTRATION_DATA',
169 event => l_event);
170
171 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
172 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
173 'fnd.plsql.UMXUTILB.addparamtoevent','name: '||p_attr_name
174 ||' value: '||p_attr_value);
175 end if;
176 EXCEPTION
177 WHEN others THEN
178 raise;
179 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
180 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
181 'fnd.plsql.UMXUTILB.addparamtoevent.end','');
182 end if;
183 END add_param_to_event;
184
185 --
186 -- Procedure
187 -- assign_wf_role
188 --
189 -- Description
190 -- populate the wf_local_roles table with information from workflow
191 -- IN
192 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
193 -- itemkey - A string generated from the application object's primary key.
194 -- actid - The function activity (instance id).
195 -- command - Run/Cancel/Timeout
196 -- OUT
197 -- resultout - result of the process based on which the next step is followed
198 procedure assign_wf_role (item_type in varchar2,
199 item_key in varchar2,
200 activity_id in number,
201 command in varchar2,
202 resultout out NOCOPY varchar2) is
203
204 l_wf_role_name wf_local_roles.name%type;
205 l_requested_for_user_id fnd_user.user_id%type;
206 l_user_name fnd_user.user_name%type;
207 l_user_role_start_date DATE;
208 l_user_role_expiration_date DATE;
209 l_justification UMX_REG_REQUESTS.JUSTIFICATION%TYPE;
210 l_regsvc_disp_name umx_reg_services_tl.display_name%type;
211
212 cursor get_username_from_userid (p_user_id in number) is
213 select user_name
214 from fnd_user
215 where user_id = p_user_id;
216
217 BEGIN
218
219 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
220 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
221 'fnd.plsql.UMXUTILB.assignwfRole.begin','itemkey: '||item_key);
222 end if;
223
224 if (command = 'RUN') then
225 l_wf_role_name := wf_engine.getitemattrtext (itemtype => item_type,
226 itemkey => item_key,
227 aname => 'WF_ROLE_NAME',
228 ignore_notfound => false);
229
230
231 l_user_role_start_date := fnd_date.canonical_to_date (
232 wf_engine.getitemattrtext (itemtype => item_type,
233 itemkey => item_key,
234 aname => 'REQUESTED_START_DATE',
235 ignore_notfound => false));
236
237 l_user_role_expiration_date := fnd_date.canonical_to_date (
238 wf_engine.getitemattrtext (itemtype => item_type,
239 itemkey => item_key,
240 aname => 'REQUESTED_END_DATE',
241 ignore_notfound => false));
242
243 l_requested_for_user_id := wf_engine.getitemattrtext (itemtype => item_type,
244 itemkey => item_key,
245 aname => 'REQUESTED_FOR_USER_ID',
246 ignore_notfound => false);
247
248 l_user_name := wf_engine.getitemattrtext (itemtype => item_type,
249 itemkey => item_key,
250 aname => 'REQUESTED_USERNAME',
251 ignore_notfound => false);
252
253 if (l_user_name is null) then
254 open get_username_from_userid (l_requested_for_user_id);
255 fetch get_username_from_userid into l_user_name;
256 close get_username_from_userid;
257 end if;
258
259 if (l_user_name is not null and l_wf_role_name is not null) then
260
261 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
262 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
263 'fnd.plsql.UMXUTILB','invoking propagateuserrole '||
264 'rolename:'||l_wf_role_name||' username:'||l_user_name);
265 end if;
266
267 l_justification := wf_engine.getitemattrtext (
268 itemtype => item_type,
269 itemkey => item_key,
270 aname => 'JUSTIFICATION');
271
272 if (l_justification is null) or (l_justification = '') then
273 -- Jusification is null or empty string. In that case, get the default
274 -- justification from the FND Message.
275 l_regsvc_disp_name := wf_engine.getitemattrtext (
276 itemtype => item_type,
277 itemkey => item_key,
278 aname => 'REGSVC_DISP_NAME');
279
280 fnd_message.set_name (application => 'FND',
281 name => 'UMX_ROLE_DEFAULT_JUSTIFICATION');
282 fnd_message.set_token (token => 'REG_PROCESS_DISPLAY_NAME',
283 value => l_regsvc_disp_name);
284 l_justification := fnd_message.get;
285 end if;
286
287 wf_local_synch.propagateUserRole (p_user_name => l_user_name,
288 p_role_name => l_wf_role_name,
289 p_start_date => nvl (l_user_role_start_date,sysdate),
290 p_expiration_date => l_user_role_expiration_date,
291 p_raiseErrors => true,
292 p_assignmentReason => l_justification);
293
294 UMX_REG_REQUESTS_PKG.UPDATE_ROW (X_REG_REQUEST_ID => to_number (item_key),
295 X_STATUS_CODE => 'APPROVED');
296 end if;
297 resultout := 'COMPLETE';
298 end if;
299
300 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
301 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
302 'fnd.plsql.UMXUTILB.assignwfRole.end','itemkey: '||item_key);
303 end if;
304
305 EXCEPTION
306 WHEN others THEN
307 Wf_Core.Context ('UMX_REGISTRATION_UTIL', 'assign_wf_role', item_type, item_key, activity_id);
308 raise;
309 END assign_wf_role;
310
311 --
312 -- Procedure
313 -- check_approval_defined
314 -- Description
315 -- check if ame approval has been defined for this registration service.
316 -- IN
317 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
318 -- itemkey - A string generated from the application object's primary key.
319 -- actid - The function activity (instance id).
320 -- command - Run/Cancel/Timeout
321 -- OUT
322 -- resultout - result of the process based on which the next step is followed
323 procedure check_approval_defined (item_type in varchar2,
324 item_key in varchar2,
325 activity_id in number,
326 command in varchar2,
327 resultout out NOCOPY varchar2) is
328
329 l_ame_transaction_type_id AME_TRANSACTION_TYPES_V.TRANSACTION_TYPE_ID%TYPE;
330 l_ame_application_id AME_TRANSACTION_TYPES_V.FND_APPLICATION_ID%TYPE;
331 l_reg_svc_code UMX_REG_SERVICES_B.REG_SERVICE_CODE%TYPE;
332 l_reg_svc_type UMX_REG_SERVICES_B.REG_SERVICE_TYPE%TYPE;
333 l_requested_by_user_id fnd_user.user_id%type;
334 l_requested_for_user_id fnd_user.user_id%type;
335 l_rby_userid_string WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
336 l_rfor_userid_string WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
337 l_launch_workflow boolean := false;
338
339 BEGIN
340 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
341 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
342 'fnd.plsql.UMXUTILB.checkapprovaldefined.begin','itemkey: '||item_key);
343 end if;
344
345 if (command = 'RUN') then
346 l_ame_transaction_type_id := wf_engine.getitemattrtext (
347 itemtype => item_type,
348 itemkey => item_key,
349 aname => 'AME_TRANSACTION_TYPE_ID',
350 ignore_notfound => false);
351
352 l_ame_application_id := wf_engine.getitemattrtext (itemtype => item_type,
353 itemkey => item_key,
354 aname => 'AME_APPLICATION_ID',
355 ignore_notfound => false);
356
357 l_reg_svc_code := wf_engine.getitemattrtext (itemtype => item_type,
358 itemkey => item_key,
359 aname => 'REG_SERVICE_CODE',
360 ignore_notfound => false);
361
362 l_reg_svc_type := wf_engine.getitemattrtext (itemtype => item_type,
363 itemkey => item_key,
364 aname => 'REG_SERVICE_TYPE',
365 ignore_notfound => false);
366 l_rby_userid_string := wf_engine.getitemattrtext (itemtype => item_type,
367 itemkey => item_key,
368 aname => 'REQUESTED_BY_USER_ID',
369 ignore_notfound => false);
370 l_requested_by_user_id := to_number (l_rby_userid_string);
371 l_rfor_userid_string := wf_engine.getitemattrtext (itemtype => item_type,
372 itemkey => item_key,
373 aname => 'REQUESTED_FOR_USER_ID',
374 ignore_notfound => false);
375 l_requested_for_user_id := to_number (l_rfor_userid_string);
376
377 -- continue launching the approval wf if one is defined and the
378 -- requested by user is not the one approver privilages
379 -- skip the approval if logged in user is privilaged admin
380 -- launch the workflow even if the admin is privilaged but request is ADMIN_CREATION
381
382 if ((l_reg_svc_type = 'SELF_SERVICE') or
383 (l_reg_svc_type = 'ADMIN_ADDITIONAL_ACCESS')) then
384
385 l_launch_workflow := true;
386
387 elsif ((l_reg_svc_type = 'ADDITIONAL_ACCESS' and
388 not check_admin_priv (l_requested_by_user_id) and
389 not check_admin_grants (l_requested_by_user_id)) or
390 l_requested_by_user_id = l_requested_for_user_id) then
391
392 l_launch_workflow := true;
393
394 elsif (l_reg_svc_type = 'ADMIN_CREATION' and
395 not check_admin_priv (l_requested_by_user_id)) then
396
397 l_launch_workflow := true;
398
399 end if;
400
401 if (l_launch_workflow and
402 l_ame_transaction_type_id is not null and
403 l_ame_application_id is not null) then
404
405 resultout := 'COMPLETE:Y';
406 wf_engine.setItemattrtext (itemtype => item_type,
407 itemkey => item_key,
408 aname => 'NOTIFICATION_CONTEXT',
409 avalue => 'APPROVAL_REQUIRED');
410
411 else
412 resultout := 'COMPLETE:N';
413 end if;
414 end if;
415
416 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
417 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
418 'fnd.plsql.UMXUTILB.checkapprovaldefined.end','itemkey: '||item_key);
419 end if;
420
421 EXCEPTION
422 WHEN others THEN
423 Wf_Core.Context ('UMX_REGISTRATION_UTIL', 'check_approval_defined', item_type, item_key, activity_id);
424 raise;
425 END check_approval_defined;
426
427 --
428 -- Procedure
429 -- check_idnty_vrfy_reqd
430 -- Description
431 -- Check if identity verification is required
432 -- IN
433 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
434 -- itemkey - A string generated from the application object's primary key.
435 -- actid - The function activity (instance id).
436 -- command - Run/Cancel/Timeout
437 -- OUT
438 -- resultout - result of the process based on which the next step is followed
439 procedure check_idnty_vrfy_reqd (item_type in varchar2,
440 item_key in varchar2,
441 activity_id in number,
442 command in varchar2,
443 resultout out NOCOPY varchar2) is
444
445 l_identity_vrfy_reqd UMX_REG_SERVICES_B.EMAIL_VERIFICATION_FLAG%TYPE;
446 BEGIN
447
448 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
449 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
450 'fnd.plsql.UMXUTILB.checkidntyvrfyreqd.begin',
451 'itemkey: '||item_key);
452 end if;
453
454 if (command = 'RUN') then
455 l_identity_vrfy_reqd := wf_engine.getitemattrtext (itemtype => item_type,
456 itemkey => item_key,
457 aname => 'IDENTITY_VERIFICATION_REQD',
458 ignore_notfound => true);
459 if (l_identity_vrfy_reqd is not null AND l_identity_vrfy_reqd = 'Y') then
460 wf_engine.setItemattrtext (itemtype => item_type,
461 itemkey => item_key,
462 aname => 'NOTIFICATION_CONTEXT',
463 avalue => 'IDENTITY_VERIFICATION');
464 -- update the status in the reg table to VERIFYING
465 UMX_REG_REQUESTS_PKG.update_row (
466 X_REG_REQUEST_ID => item_key,
467 X_STATUS_CODE => 'VERIFYING');
468
469 resultout := 'COMPLETE:REQUIRED';
470 else
471 resultout := 'COMPLETE:NOTREQUIRED';
472 end if;
473 end if;
474
475 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
476 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
477 'fnd.plsql.UMXUTILB.checkidntyvrfyreqd.end',
478 'itemkey: '||item_key);
479 end if;
480
481 EXCEPTION
482 WHEN others THEN
483 Wf_Core.Context ('UMX_REGISTRATION_UTIL', 'check_idnty_vrfy_reqd', item_type, item_key, activity_id);
484 raise;
485 END check_idnty_vrfy_reqd;
486
487 --
488 -- Procedure
489 -- check_mandatory_attributes
490 -- Description
491 -- Check if all the mandatory attributes are available.
492 -- IN
493 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
494 -- itemkey - A string generated from the application object's primary key.
495 -- actid - The function activity (instance id).
496 -- command - Run/Cancel/Timeout
497 -- OUT
498 -- resultout - result of the process based on which the next step is followed
499 procedure check_mandatory_attributes (item_type in varchar2,
500 item_key in varchar2,
501 activity_id in number,
502 command in varchar2,
503 resultout out NOCOPY varchar2) is
504
505 l_message_tokens varchar2 (100);
506 l_missing_attribute boolean := false;
507 l_username fnd_user.user_name%type;
508
509 BEGIN
510
511 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
512 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
513 'fnd.plsql.UMXUTILB.check_mandatory_attributes.begin',
514 'item_type: '|| item_type || ' | itemkey: '||item_key);
515 end if;
516
517 if (command = 'RUN') then
518
519 -- First check the user name.
520 l_username := wf_engine.getitemattrtext (itemtype => item_type,
521 itemkey => item_key,
522 aname => 'REQUESTED_USERNAME',
523 ignore_notfound => true);
524
525 if (l_username is null) then
526 l_missing_attribute := true;
527 l_message_tokens := 'REQUESTED_USERNAME';
528 end if;
529
530 if (l_missing_attribute) then
531
532 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
533 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
534 'fnd.plsql.UMXUTILB.check_mandatory_attributes.end',
535 'Exception occurs because mandatory attribute (s) is/are missing: ' || l_message_tokens);
536 end if;
537
538 fnd_message.set_name (application => 'FND',
539 name => 'UMX_MANDATORY_ATTRIBUTES_ERROR');
540 fnd_message.set_token (token => 'ATTRIBUTE_NAMES',
541 value => l_message_tokens);
542
543 raise_application_error ('-20000', fnd_message.get);
544 end if;
545 end if;
546
547 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
548 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
549 'fnd.plsql.UMXUTILB.check_mandatory_attributes.end',
550 'item_type: '|| item_type || ' | itemkey: '|| item_key);
551 end if;
552
553 EXCEPTION
554 WHEN others THEN
555 Wf_Core.Context ('UMX_REGISTRATION_UTIL', 'check_mandatory_attributes', item_type, item_key, activity_id);
556 raise;
557 END check_mandatory_attributes;
558
559 -- Procedure
560 -- create_reg_request
561 -- Description
562 -- Wrapper around UMX_REG_REQUESTS_PVT.create_reg_srv_request
563 -- IN
564 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
565 -- itemkey - A string generated from the application object's primary key.
566 -- actid - The function activity (instance id).
567 -- command - Run/Cancel/Timeout
568 -- OUT
569 -- resultout - result of the process based on which the next step is followed
570 procedure create_reg_request (p_item_type in varchar2,
571 p_item_key in varchar2,
572 p_activity_id in number,
573 p_command in varchar2,
574 p_resultout out NOCOPY varchar2) is
575
576 l_reg_request_id UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE;
577 l_requested_for_user_id UMX_REG_REQUESTS.REQUESTED_FOR_USER_ID%TYPE ;
578 l_requested_by_user_id UMX_REG_REQUESTS.REQUESTED_BY_USER_ID%TYPE ;
579 l_requested_for_party_id UMX_REG_REQUESTS.REQUESTED_FOR_PARTY_ID%TYPE ;
580 l_requested_username UMX_REG_REQUESTS.REQUESTED_USERNAME%TYPE ;
581 l_wf_role_name UMX_REG_REQUESTS.WF_ROLE_NAME%TYPE ;
582 l_reg_service_code UMX_REG_REQUESTS.REG_SERVICE_CODE%TYPE ;
583 l_reg_service_type UMX_REG_REQUESTS.REG_SERVICE_TYPE%TYPE;
584 l_ame_application_id UMX_REG_REQUESTS.AME_APPLICATION_ID%TYPE ;
585 l_ame_transaction_type_id UMX_REG_REQUESTS.AME_TRANSACTION_TYPE_ID%TYPE ;
586 l_request_status_code UMX_REG_REQUESTS.STATUS_CODE%TYPE;
587 l_justification UMX_REG_REQUESTS.JUSTIFICATION%TYPE ;
588 l_requested_start_date DATE;
589 l_requested_end_date DATE;
590
591 l_event wf_event_t;
592
593 BEGIN
594
595 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
596 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
597 'fnd.plsql.UMXUTILB.createregrequest.begin',
598 'itemkey: '||p_item_key);
599 end if;
600
601
602
603 if (p_command = 'RUN') then
604
605 /**
606 ** this is the first method in the workflow so log all the
607 ** parameters passed to it (all params in event object).
608 **/
609
610 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
611
612 l_event := wf_engine.getitemattrevent (itemtype => p_item_type,
613 itemkey => p_item_key,
614 name => 'REGISTRATION_DATA');
615 print_Event_params (p_event => l_event);
616 end if;
617
618 l_reg_request_id := To_Number (p_item_key);
619 l_requested_for_user_id := wf_engine.getitemattrtext (
620 itemtype => p_item_type,
621 itemkey => p_item_key,
622 aname => 'REQUESTED_FOR_USER_ID',
623 ignore_notfound => true);
624
625 l_requested_by_user_id := wf_engine.getitemattrtext (
626 itemtype => p_item_type,
627 itemkey => p_item_key,
628 aname => 'REQUESTED_BY_USER_ID',
629 ignore_notfound => false);
630
631 l_requested_for_party_id := wf_engine.getitemattrtext (
632 itemtype => p_item_type,
633 itemkey => p_item_key,
634 aname => 'PERSON_PARTY_ID',
635 ignore_notfound => true);
636
637 l_requested_username := wf_engine.getitemattrtext (
638 itemtype => p_item_type,
639 itemkey => p_item_key,
640 aname => 'REQUESTED_USERNAME',
641 ignore_notfound => false);
642
643 l_wf_role_name := wf_engine.getitemattrtext (itemtype => p_item_type,
644 itemkey => p_item_key,
645 aname => 'WF_ROLE_NAME',
646 ignore_notfound => true);
647 l_reg_service_code := wf_engine.getitemattrtext (
648 itemtype => p_item_type,
649 itemkey => p_item_key,
650 aname => 'REG_SERVICE_CODE',
651 ignore_notfound => false);
652 l_reg_service_type := wf_engine.getitemattrtext (
653 itemtype => p_item_type,
654 itemkey => p_item_key,
655 aname => 'REG_SERVICE_TYPE',
656 ignore_notfound => false);
657 l_ame_application_id := wf_engine.getitemattrtext (
658 itemtype => p_item_type,
659 itemkey => p_item_key,
660 aname => 'AME_APPLICATION_ID',
661 ignore_notfound => true);
662
663 l_ame_transaction_type_id := wf_engine.getitemattrtext (
664 itemtype => p_item_type,
665 itemkey => p_item_key,
666 aname => 'AME_TRANSACTION_TYPE_ID',
667 ignore_notfound => true);
668
669 l_request_status_code := 'PENDING';
670 l_justification := wf_engine.getitemattrtext (
671 itemtype => p_item_type,
672 itemkey => p_item_key,
673 aname => 'JUSTIFICATION',
674 ignore_notfound => true);
675
676 l_requested_start_date := fnd_date.canonical_to_date (
677 wf_engine.getitemattrtext (itemtype => p_item_type,
678 itemkey => p_item_key,
679 aname => 'REQUESTED_START_DATE',
680 ignore_notfound => false));
681
682 l_requested_end_date := fnd_date.canonical_to_date (
683 wf_engine.getitemattrtext (itemtype => p_item_type,
684 itemkey => p_item_key,
685 aname => 'REQUESTED_END_DATE',
686 ignore_notfound => false));
687
688 UMX_REG_REQUESTS_PKG.insert_row (
689 x_reg_request_id => l_reg_request_id ,
690 x_reg_service_type => l_reg_service_type,
691 x_status_code => l_request_status_code,
692 x_requested_by_user_id => l_requested_by_user_id,
693 x_requested_for_user_id => l_requested_for_user_id,
694 x_requested_for_party_id => l_requested_for_party_id,
695 x_requested_username => l_requested_username,
696 x_requested_start_date => nvl (l_requested_start_date,sysdate),
697 x_requested_end_date => l_requested_end_date,
698 x_wf_role_name => l_wf_role_name,
699 x_reg_service_code => l_reg_service_code,
700 x_ame_application_id => l_ame_application_id,
701 x_ame_transaction_type_id => l_ame_transaction_type_id,
702 x_justification => l_justification);
703 end if;
704
705 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
706 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
707 'fnd.plsql.UMXUTILB.createregrequest.end',
708 'itemkey: '||p_item_key);
709 end if;
710
711 EXCEPTION
712 WHEN others THEN
713 Wf_Core.Context ('UMX_REGISTRATION_UTIL', 'create_reg_request', p_item_type, p_item_key, p_activity_id);
714 raise;
715
716 END create_reg_request;
717
718 -- Procedure
719 -- Reserve UserName
720 -- Description
721 -- Wrapper around Fnd_user_pkg.create_username with status as pending
722 -- IN
723 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
724 -- itemkey - A string generated from the application object's primary key.
725 -- actid - The function activity (instance id).
726 -- command - Run/Cancel/Timeout
727 -- OUT
728 -- resultout - result of the process based on which the next step is followed
729 procedure reserve_username (p_item_type in varchar2,
730 p_item_key in varchar2,
731 p_activity_id in number,
732 p_command in varchar2,
733 p_resultout out NOCOPY varchar2) is
734
735 l_username FND_USER.USER_NAME%TYPE;
736 l_person_party_id HZ_PARTIES.party_id%TYPE;
737 l_temp_party_id varchar2 (25);
738 l_password varchar2 (100);
739 l_expire_password varchar2 (25);
740 l_user_id number;
741 l_temp_user_id varchar2 (25);
742 l_email_address FND_USER.EMAIL_ADDRESS%TYPE;
743 l_fax FND_USER.FAX%TYPE;
744 l_password_date DATE;
745 l_password_message VARCHAR2 (300);
746 l_return_status pls_integer;
747
748 BEGIN
749
750 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
751 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
752 'fnd.plsql.UMXUTILB.reserveusername.begin',
753 'itemkey: '||p_item_key);
754 end if;
755
756 if (p_command = 'RUN') then
757 l_username := wf_engine.getitemattrtext (itemtype => p_item_type,
758 itemkey => p_item_key,
759 aname => 'REQUESTED_USERNAME',
760 ignore_notfound => true);
761
762 l_temp_party_id := wf_engine.getitemattrtext (itemtype => p_item_type,
763 itemkey => p_item_key,
764 aname => 'PERSON_PARTY_ID',
765 ignore_notfound => true);
766 if l_temp_party_id is not null then
767 l_person_party_id := to_number (l_temp_party_id);
768 end if;
769
770 l_password := wf_engine.getitemattrtext (itemtype => p_item_type,
771 itemkey => p_item_key,
772 aname => 'PASSWORD',
773 ignore_notfound => true);
774
775 l_expire_password := wf_engine.getitemattrtext (itemtype => p_item_type,
776 itemkey => p_item_key,
777 aname => 'EXPIRE_PASSWORD',
778 ignore_notfound => true);
779
780 l_email_address := wf_engine.getitemattrtext (itemtype => p_item_type,
781 itemkey => p_item_key,
782 aname => 'EMAIL_ADDRESS',
783 ignore_notfound => false);
784
785 l_fax := wf_engine.getitemattrtext (itemtype => p_item_type,
786 itemkey => p_item_key,
787 aname => 'FAX',
788 ignore_notfound => false);
789
790 --invoke this api after fnd provides the correct api
791 -- change the password date to null if reg_service_type is admin_creation
792 -- setdisplay password to password if it has been genereated
793 -- set it as the fnd_message , this is used in the notification wf
794 if (l_expire_password = 'Y') then
795 l_password_date := null;
796 l_password_message := l_password;
797 else
798 l_password_date := sysdate;
799 --get this from fnd messages
800 l_password_message := fnd_message.get_string ('FND', 'UMX_NTFY_DSPLY_PASSWD');
801 end if;
802
803 add_param_to_event (p_item_type, p_item_key, 'DISPLAY_PASSWORD', l_password_message);
804
805 -- Check if the username is available
806 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
807 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
808 'fnd.plsql.UMXUTILB.reserveusername',
809 'Before invoking fnd_user_pkg.TestUserName API with username is ' || l_username);
810 end if;
811
812 l_return_status := fnd_user_pkg.TestUserName (x_user_name => l_username);
813
814 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
815 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
816 'fnd.plsql.UMXUTILB.reserveusername',
817 'After invoking fnd_user_pkg.TestUserName API with return status is ' || l_return_status);
818 end if;
819
820 if ((l_return_status = fnd_user_pkg.USER_INVALID_NAME) or
821 (l_return_status = fnd_user_pkg.USER_EXISTS_IN_FND) or
822 (l_return_status = fnd_user_pkg.USER_EXISTS_NO_LINK_ALLOWED)) then
823 -- There is problem with the username. Throw error
824 raise_application_error ('-20000', fnd_message.get);
825 else
826 add_param_to_event (p_item_type, p_item_key, 'TESTUSERNAME_RET_STATUS', l_return_status);
827 end if;
828
829 if (l_return_status = fnd_user_pkg.user_synched) then
830 -- Because the account will be synched, we no longer needs to keep
831 -- the user password in apps. Password will be managed "EXTERNALLY".
832 l_password := null;
833 wf_engine.setitemattrtext (itemtype => p_item_type,
834 itemkey => p_item_key,
835 aname => 'PASSWORD',
836 avalue => l_password);
837 add_param_to_event (p_item_type, p_item_key, 'DISPLAY_PASSWORD', l_password);
838 end if;
839
840 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
841 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
842 'fnd.plsql.UMXUTILB.reserveusername',
843 'Before invoking UMX_REG_REQUESTS_PVT.reserve_username API.');
844 end if;
845
846 l_user_id := UMX_REG_REQUESTS_PVT.reserve_username (
847 p_reg_request_id => p_item_key, -- item key is regid
848 p_username => l_username,
849 p_owner => NULL,
850 p_unencrypted_password => l_password,
851 p_password_date => l_password_date,
852 p_email_address => l_email_address,
853 p_fax => l_fax,
854 p_person_party_id => l_person_party_id);
855
856 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
857 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
858 'fnd.plsql.UMXUTILB.reserveusername',
859 'userid: '||l_user_id);
860 end if;
861
862 if (l_user_id is not null) then
863 l_temp_user_id := to_char (l_user_id);
864 wf_engine.setitemattrtext (itemtype => p_item_type,
865 itemkey =>p_item_key,
866 aname => 'REQUESTED_FOR_USER_ID',
867 avalue => l_temp_user_id);
868
869 add_param_to_event (p_item_type,p_item_key,'REQUESTED_FOR_USER_ID',l_temp_user_id);
870
871 p_resultout := 'COMPLETE:T';
872 else
873 p_resultout := 'COMPLETE:N';
874 end if;
875 end if;
876
877 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
878 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
879 'fnd.plsql.UMXUTILB.reserveusername.end',
880 'itemkey: '||p_item_key);
881 end if;
882
883 EXCEPTION
884 WHEN others THEN
885 Wf_Core.Context ('UMX_REGISTRATION_UTIL', 'reserve_username', p_item_type, p_item_key, p_activity_id);
886 raise;
887 END reserve_username;
888
889 -- Procedure
890 -- activate_userName
891 -- Description
892 -- Wrapper around Fnd_user_pkg.update_username with status as approved
893 -- IN
894 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
895 -- itemkey - A string generated from the application object's primary key.
896 -- actid - The function activity (instance id).
897 -- command - Run/Cancel/Timeout
898 -- OUT
899 -- resultout - result of the process based on which the next step is followed
900 procedure activate_username (p_item_type in varchar2,
901 p_item_key in varchar2,
902 p_activity_id in number,
903 p_command in varchar2,
904 p_resultout out NOCOPY varchar2) is
905 l_person_party_id VARCHAR2 (30);
906 l_user_name fnd_user.user_name%type;
907 l_start_date DATE;
908 l_end_date DATE;
909 BEGIN
910
911 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
912 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
913 'fnd.plsql.UMXUTILB.activateusername.begin',
914 'itemkey: '||p_item_key);
915 end if;
916
917
918 if (p_command = 'RUN') then
919 l_person_party_id := wf_engine.getitemattrtext (itemtype => p_item_type,
920 itemkey => p_item_key,
921 aname => 'PERSON_PARTY_ID',
922 ignore_notfound => false);
923
924 l_user_name := wf_engine.getitemattrtext (itemtype => p_item_type,
925 itemkey => p_item_key,
926 aname => 'REQUESTED_USERNAME',
927 ignore_notfound => false);
928
929 l_start_date := fnd_date.canonical_to_date (
930 wf_engine.getitemattrtext (itemtype => p_item_type,
931 itemkey => p_item_key,
932 aname => 'REQUESTED_START_DATE',
933 ignore_notfound => false));
934
935 l_end_date := fnd_date.canonical_to_date (
936 wf_engine.getitemattrtext (itemtype => p_item_type,
937 itemkey => p_item_key,
938 aname => 'REQUESTED_END_DATE',
939 ignore_notfound => false));
940 if (l_person_party_id is not null) then
941 -- call new fnd apis when provided
942 -- change all the dates appropriately and check how to get the format
943 umx_reg_requests_pvt.approve_username_reg_request (
944 p_reg_request_id => to_number (p_item_key),
945 p_username => l_user_name,
946 p_person_party_id => to_number (l_person_party_id),
947 p_start_date => l_start_date,
948 p_end_date => l_end_date);
949 else
950 -- raise an error cannot activate user account with a person_party_id
951 -- there was an error somewhere not traped as party_id should not be
952 -- null here
953
954 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
955 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
956 'fnd.plsql.UMXUTILB.launchusernamepolicy',
957 'person_party_id is null in activate username');
958 end if;
959
960 raise_application_error ('-20000','person_party_id not passed to activate username ');
961 end if;
962 p_resultout := 'COMPLETE';
963 end if;
964
965 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
966 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
967 'fnd.plsql.UMXUTILB.activateusername.end',
968 'itemkey: '||p_item_key);
969 end if;
970
971 END activate_username;
972
973 -- Procedure
974 -- release_userName
975 -- Description
976 -- Wrapper around Fnd_user_pkg.delete_username with status as cancelled
977 -- IN
978 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
979 -- itemkey - A string generated from the application object's primary key.
980 -- actid - The function activity (instance id).
981 -- command - Run/Cancel/Timeout
982 -- OUT
983 -- resultout - result of the process based on which the next step is followed
984 procedure release_username (p_item_type in varchar2,
985 p_item_key in varchar2,
986 p_activity_id in number,
987 p_command in varchar2,
988 p_resultout out NOCOPY varchar2) is
989
990 l_username FND_USER.user_name%type;
991 l_userid FND_USER.user_id%type;
992
993 BEGIN
994
995 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
996 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
997 'fnd.plsql.UMXUTILB.releaseusername.begin',
998 'itemkey: '||p_item_key);
999 end if;
1000
1001 if (p_command = 'RUN') then
1002 l_username := wf_engine.getitemattrtext (itemtype => p_item_type,
1003 itemkey => p_item_key,
1004 aname => 'REQUESTED_USERNAME',
1005 ignore_notfound => false);
1006
1007 l_userid := wf_engine.getitemattrtext (itemtype => p_item_type,
1008 itemkey => p_item_key,
1009 aname => 'REQUESTED_FOR_USER_ID',
1010 ignore_notfound => false);
1011
1012
1013 umx_reg_requests_pvt.reject_username_reg_request (
1014 p_reg_request_id => p_item_key,
1015 p_user_id => l_userid,
1016 p_username => l_username);
1017
1018 end if;
1019
1020 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1021 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1022 'fnd.plsql.UMXUTILB.releaseusername.end',
1023 'itemkey: '||p_item_key);
1024 end if;
1025
1026 END release_username;
1027
1028 -- Procedure
1029 -- increment_sequence
1030 -- Description
1031 -- Procedure which increments the sequence used for raising the events
1032 -- IN
1033 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1034 -- itemkey - A string generated from the application object's primary key.
1035 -- actid - The function activity (instance id).
1036 -- command - Run/Cancel/Timeout
1037 -- OUT
1038 -- resultout - result of the process based on which the next step is followed
1039 procedure increment_sequence (p_item_type in varchar2,
1040 p_item_key in varchar2,
1041 p_activity_id in number,
1042 p_command in varchar2,
1043 p_resultout out NOCOPY varchar2) is
1044
1045 l_event_key varchar2 (25);
1046 l_temp_event_key number;
1047 l_event_type varchar2 (25);
1048
1049 BEGIN
1050
1051 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1052 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1053 'fnd.plsql.UMXUTILB.incrementsequence.begin',
1054 'itemkey: '||p_item_key);
1055 end if;
1056
1057 if (p_command = 'RUN') then
1058
1059 l_event_type := wf_engine.getActivityAttrText (itemtype => p_item_type,
1060 itemkey => p_item_key,
1061 actid => p_activity_id,
1062 aname => 'EVENT_TYPE',
1063 ignore_notfound => true);
1064
1065 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1066 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1067 'fnd.plsql.UMXUTILB.incrementsequence',
1068 'eventtype: '||l_event_type);
1069 end if;
1070
1071 if (l_event_type = 'GENERIC') then
1072 select UMX_EVENTS_S.nextval into l_temp_event_key from dual;
1073 else
1074 select UMX_REG_REQUESTS_S.nextval into l_temp_event_key from dual;
1075 end if;
1076
1077 l_event_key := to_char (l_temp_event_key);
1078 wf_engine.setitemattrtext (itemtype => p_item_type,
1079 itemkey =>p_item_key,
1080 aname => 'EVENT_KEY_SEQUENCE',
1081 avalue => l_event_key);
1082 p_resultout := 'COMPLETE';
1083
1084 end if;
1085
1086 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1087 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1088 'fnd.plsql.UMXUTILB.incrementsequence.end',
1089 'itemkey: '||p_item_key);
1090 end if;
1091
1092 EXCEPTION
1093 WHEN others THEN
1094 Wf_Core.Context ('UMX_REGISTRATION_UTIL', 'increment_sequence', p_item_type, p_item_key, p_activity_id);
1095 raise;
1096
1097 END increment_sequence;
1098
1099 --
1100 -- Procedure
1101 -- Check_password_null
1102 -- (DEPRECATED API)
1103 -- Description
1104 -- Check if the password is null
1105 -- IN
1106 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1107 -- itemkey - A string generated from the application object's primary key.
1108 -- actid - The function activity (instance id).
1109 -- funcmode - Run/Cancel/Timeout
1110 -- OUT
1111 -- resultout - result of the process based on which the next step is followed
1112 procedure check_password_null (item_type in varchar2,
1113 item_key in varchar2,
1114 activity_id in number,
1115 command in varchar2,
1116 resultout out NOCOPY varchar2) is
1117
1118 l_password varchar2 (100);
1119
1120 BEGIN
1121
1122 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1123 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1124 'fnd.plsql.UMXUTILB.checkpasswordnull.begin',
1125 'itemkey: '||item_key);
1126 end if;
1127
1128 if (command = 'RUN') then
1129 l_password := wf_engine.getitemattrtext (itemtype => item_type,
1130 itemkey => item_key,
1131 aname => 'PASSWORD',
1132 ignore_notfound => false);
1133
1134 if (l_password is null) then
1135 resultout := 'COMPLETE:Y';
1136 wf_engine.setitemattrtext (itemtype => item_type,
1137 itemkey => item_key,
1138 aname => 'EXPIRE_PASSWORD',
1139 avalue => 'Y');
1140 else
1141 resultout := 'COMPLETE:N';
1142 end if;
1143 end if;
1144
1145 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1146 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1147 'fnd.plsql.UMXUTILB.checkpasswordnull.end',
1148 'itemkey: '||item_key);
1149 end if;
1150
1151 EXCEPTION
1152 WHEN others THEN
1153 Wf_Core.Context ('UMX_REGISTRATION_UTIL', 'check_username_null', item_type, item_key, activity_id);
1154 raise;
1155 END check_password_null;
1156
1157 --
1158 -- Function
1159 -- set_event_object
1160 --
1161 -- Description
1162 -- This method sets back the changes made to parameters in subscribers back to
1163 -- the the main workflow.
1164 -- IN
1165 -- p_attr_name varchar2
1166 -- this is the attrname that needs to be added to parent wf and event obj
1167 -- p_attr_value varchar2
1168 -- this is the attrvalue of the attrname to be added to parentwf and event
1169 -- IN/OUT
1170 -- p_event - WF_EVENT_T which holds the data that needs to passed from/to
1171 -- subscriber of the event
1172 --
1173 function set_event_object (p_event in out NOCOPY WF_EVENT_T,
1174 p_attr_name in VARCHAR2 DEFAULT NULL,
1175 p_attr_value in VARCHAR2 DEFAULT NULL)
1176 return varchar2 is
1177
1178 l_parent_itemtype WF_ITEMS.ITEM_TYPE%TYPE;
1179 l_parent_itemkey WF_ITEMS.ITEM_KEY%TYPE;
1180 begin
1181
1182 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1183 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1184 'fnd.plsql.UMXUTILB.seteventobject.begin',
1185 'attrname: '||p_attr_name ||
1186 ' attrvalue: '|| p_attr_value);
1187 end if;
1188
1189 l_parent_itemtype := p_event.getvalueforparameter ('UMX_PARENT_ITEM_TYPE');
1190 l_parent_itemkey := p_event.getvalueforparameter ('UMX_PARENT_ITEM_KEY');
1191
1192 -- set attrname and attrvalue into workflow if they are not null
1193
1194 if (p_attr_name is not null) then
1195
1196 p_event.addParametertoList (p_attr_name, p_attr_value);
1197 -- modify this to check if the attr has been defined earlier
1198 --if not then add and set
1199 begin
1200 wf_engine.setitemattrtext (itemtype => l_parent_itemtype,
1201 itemkey => l_parent_itemkey,
1202 aname => p_attr_name,
1203 avalue => p_attr_value);
1204 EXCEPTION
1205 WHEN OTHERS THEN
1206 wf_engine.additemattr (itemtype => l_parent_itemtype,
1207 itemkey => l_parent_itemkey,
1208 aname => p_attr_name,
1209 text_value => p_attr_value);
1210 end;
1211
1212 end if;
1213
1214
1215
1216 -- set the event object into workflow
1217 wf_engine.setitemattrevent (itemtype => l_parent_itemtype,
1218 itemkey => l_parent_itemkey,
1219 name => 'REGISTRATION_DATA',
1220 event => p_event);
1221
1222 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1223 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1224 'fnd.plsql.UMXUTILB.seteventobject.end',
1225 '');
1226 end if;
1227
1228 /**
1229 ** log all the params in event obj
1230 **/
1231
1232 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1233 then
1234 print_Event_params (p_event => p_event);
1235 end if;
1236
1237 return 'SUCCESS';
1238
1239 EXCEPTION
1240 WHEN OTHERS THEN
1241 raise;
1242 end set_event_object;
1243
1244 -- update_reg_request
1245 -- Description
1246 -- Wrapper around UMX_REG_REQUESTS_PVT.update_reg
1247 -- IN
1248 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1249 -- itemkey - A string generated from the application object's primary key.
1250 -- actid - The function activity (instance id).
1251 -- funcmode - Run/Cancel/Timeout
1252 -- OUT
1253 -- resultout - result of the process based on which the next step is followed
1254 procedure update_reg_request (p_item_type in varchar2,
1255 p_item_key in varchar2,
1256 p_activity_id in number,
1257 p_command in varchar2,
1258 p_resultout out NOCOPY varchar2) is
1259
1260 CURSOR getRegRequest (p_reg_request_id in number) is
1261
1262 select STATUS_CODE
1263 from UMX_REG_REQUESTS
1264 where REG_REQUEST_ID = p_reg_request_id;
1265
1266 l_reg_request umx_reg_requests_pvt.reg_request_type;
1267 l_reg_request_id UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE;
1268 l_status_code UMX_REG_REQUESTS.status_code%TYPE;
1269
1270 l_event wf_event_t;
1271
1272 BEGIN
1273
1274 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1275 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1276 'fnd.plsql.UMXUTILB.updateregrequest.begin',
1277 'itemkey: '||p_item_key);
1278 end if;
1279
1280 /**
1281 ** log all the parameters in event object
1282 **/
1283
1284 if (p_command = 'RUN') then
1285
1286 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1287
1288 l_event := wf_engine.getitemattrevent (itemtype => p_item_type,
1289 itemkey => p_item_key,
1290 name => 'REGISTRATION_DATA');
1291 print_Event_params (p_event => l_event);
1292 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1293 'fnd.plsql.UMXUTILB',
1294 'regrequestid: '||p_item_key);
1295 end if;
1296
1297 open getRegRequest (p_reg_request_id => to_number (p_item_key));
1298
1299 fetch getRegRequest into l_status_code;
1300
1301 if (getRegRequest%notfound) then
1302 close getRegRequest;
1303 raise_application_error ('-20000','Invalid reg_request_id in update regrequest');
1304 end if;
1305
1306 close getRegRequest;
1307
1308 l_reg_request.reg_request_id := p_item_key;
1309 l_reg_request.requested_for_user_id := wf_engine.getitemattrtext (
1310 itemtype => p_item_type,
1311 itemkey => p_item_key,
1312 aname => 'REQUESTED_FOR_USER_ID',
1313 ignore_notfound => true);
1314
1315 l_reg_request.requested_by_userid := wf_engine.getitemattrtext (
1316 itemtype => p_item_type,
1317 itemkey => p_item_key,
1318 aname => 'REQUESTED_BY_USER_ID',
1319 ignore_notfound => false);
1320
1321 l_reg_request.requested_for_party_id := wf_engine.getitemattrtext (
1322 itemtype => p_item_type,
1323 itemkey => p_item_key,
1324 aname => 'PERSON_PARTY_ID',
1325 ignore_notfound => true);
1326
1327 l_reg_request.requested_username := wf_engine.getitemattrtext (
1328 itemtype => p_item_type,
1329 itemkey => p_item_key,
1330 aname => 'REQUESTED_USERNAME',
1331 ignore_notfound => false);
1332
1333 l_reg_request.wf_role_name := wf_engine.getitemattrtext (itemtype => p_item_type,
1334 itemkey => p_item_key,
1335 aname => 'WF_ROLE_NAME',
1336 ignore_notfound => true);
1337 l_reg_request.reg_service_code := wf_engine.getitemattrtext (
1338 itemtype => p_item_type,
1339 itemkey => p_item_key,
1340 aname => 'REG_SERVICE_CODE',
1341 ignore_notfound => false);
1342 l_reg_request.reg_service_type := wf_engine.getitemattrtext (
1343 itemtype => p_item_type,
1344 itemkey => p_item_key,
1345 aname => 'REG_SERVICE_TYPE',
1346 ignore_notfound => false);
1347 l_reg_request.ame_application_id := wf_engine.getitemattrtext (
1348 itemtype => p_item_type,
1349 itemkey => p_item_key,
1350 aname => 'AME_APPLICATION_ID',
1351 ignore_notfound => true);
1352
1353 l_reg_request.ame_transaction_type_id := wf_engine.getitemattrtext (
1354 itemtype => p_item_type,
1355 itemkey => p_item_key,
1356 aname => 'AME_TRANSACTION_TYPE_ID',
1357 ignore_notfound => true);
1358
1359 l_reg_request.justification := wf_engine.getitemattrtext (
1360 itemtype => p_item_type,
1361 itemkey => p_item_key,
1362 aname => 'JUSTIFICATION',
1363 ignore_notfound => true);
1364
1365 l_reg_request.requested_start_date := fnd_date.canonical_to_date (
1366 wf_engine.getitemattrtext (itemtype => p_item_type,
1367 itemkey => p_item_key,
1368 aname => 'REQUESTED_START_DATE',
1369 ignore_notfound => false));
1370
1371 l_reg_request.requested_end_date := fnd_date.canonical_to_date (
1372 wf_engine.getitemattrtext (itemtype => p_item_type,
1373 itemkey => p_item_key,
1374 aname => 'REQUESTED_END_DATE',
1375 ignore_notfound => false));
1376
1377
1378 if (l_status_code = 'UNASSIGNED') then
1379 -- We need to update the status code to Pending
1380 l_reg_request.status_code := 'PENDING';
1381 end if;
1382 --update the reg request with latest details
1383 UMX_REG_REQUESTS_PVT.update_reg_request (p_reg_request => l_reg_request);
1384
1385
1386 p_resultout := 'COMPLETE';
1387 end if;
1388
1389 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1390 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1391 'fnd.plsql.UMXUTILB.updateregrequests.end',
1392 'itemkey: '||p_item_key);
1393 end if;
1394
1395 EXCEPTION
1396 WHEN others THEN
1397 Wf_Core.Context ('UMX_REGISTRATION_UTIL', 'Update_Reg_request', p_item_type, p_item_key);
1398 raise;
1399
1400 END update_reg_request;
1401
1402 procedure LaunchEvent (item_type in varchar2,
1403 item_key in varchar2,
1404 activity_id in number,
1405 command in varchar2,
1406 resultout out NOCOPY varchar2) is
1407
1408 l_event wf_event_t;
1409 l_parameter_list wf_parameter_list_t;
1410 l_event_key number;
1411 l_person_party_id fnd_user.person_party_id%type;
1412 BEGIN
1413
1414 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1415 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1416 'fnd.plsql.UMXUTILB.launchevent.begin',
1417 'itemkey: '||item_key);
1418 end if;
1419
1420 if (command = 'RUN') then
1421 l_person_party_id := wf_engine.getitemattrtext (itemtype => item_type,
1422 itemkey => item_key,
1423 aname => 'PERSON_PARTY_ID',
1424 ignore_notfound => false);
1425
1426 if (l_person_party_id is null) then
1427
1428 l_event := wf_engine.getitemattrevent (itemtype => item_type,
1429 itemkey => item_key,
1430 name => 'REGISTRATION_DATA');
1431 l_parameter_list := l_event.getParameterlist ();
1432
1433 select UMX_EVENTS_S.nextval into l_event_key from dual;
1434 wf_event.raise ('oracle.apps.fnd.umx.createpersonparty',l_event_key,null,l_parameter_list,sysdate);
1435
1436 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1437 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1438 'fnd.plsql.UMXUTILB.launchevent',
1439 'event_key: '||l_event_key);
1440 end if;
1441
1442
1443 end if;
1444 end if;
1445
1446 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1447 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1448 'fnd.plsql.UMXUTILB.launchevent.end',
1449 'itemkey: '||item_key);
1450 end if;
1451
1452 EXCEPTION
1453 WHEN others THEN
1454 Wf_Core.Context ('UMX_REGISTRATION_UTIL', 'LaunchEvent', item_type, item_key);
1455 raise;
1456
1457 END LaunchEvent;
1458
1459 -- one more temp work around for launching notificationwf
1460 procedure Start_Notification_Wf (item_type in varchar2,
1461 item_key in varchar2,
1462 activity_id in number,
1463 command in varchar2,
1464 resultout out NOCOPY varchar2) is
1465
1466 l_event wf_event_t;
1467 l_parameter_list wf_parameter_list_t;
1468 l_event_key number;
1469 l_notification_context varchar2 (25);
1470 l_notification_event wf_events.name%type;
1471 BEGIN
1472
1473 if (command = 'RUN') then
1474 l_notification_context := wf_engine.getitemattrtext (itemtype => item_type,
1475 itemkey => item_key,
1476 aname => 'NOTIFICATION_CONTEXT',
1477 ignore_notfound => false);
1478 l_notification_event := wf_engine.getitemattrtext (itemtype => item_type,
1479 itemkey => item_key,
1480 aname => 'WF_NOTIFICATION_EVENT',
1481 ignore_notfound => false);
1482
1483 l_event := wf_engine.getitemattrevent (itemtype => item_type,
1484 itemkey => item_key,
1485 name => 'REGISTRATION_DATA');
1486
1487 l_parameter_list := l_event.getParameterList ();
1488
1489 wf_event.addparametertolist ('NOTIFICATION_CONTEXT', l_notification_context,l_parameter_list);
1490
1491 select UMX_EVENTS_S.nextval into l_event_key from dual;
1492 wf_event.raise (p_event_name => l_notification_event,
1493 p_event_key => l_event_key,
1494 p_parameters => l_parameter_list);
1495 end if;
1496 EXCEPTION
1497 WHEN others THEN
1498 Wf_Core.Context ('UMX_REGISTRATION_UTIL', 'Start_notification_wf', item_type, item_key, activity_id);
1499 raise;
1500 END Start_Notification_Wf;
1501
1502 -- Procedure
1503 -- create_ad_hoc_role
1504 --
1505 -- Description
1506 -- populate the wf_local_roles table with information from workflow
1507 -- IN
1508 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1509 -- itemkey - A string generated from the application object's primary key.
1510 -- actid - The function activity (instance id).
1511 -- funcmode - Run/Cancel/Timeout
1512 -- OUT
1513 -- resultout - result of the process based on which the next step is followed
1514 procedure create_ad_hoc_role (item_type in varchar2,
1515 item_key in varchar2,
1516 activity_id in number,
1517 command in varchar2,
1518 resultout out NOCOPY varchar2) is
1519
1520 l_user_name fnd_user.user_name%type;
1521 l_email_address fnd_user.email_address%type;
1522 l_person_first_name WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
1523 l_person_last_name WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
1524 l_person_middle_name WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
1525 l_prefix WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
1526 l_suffix WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
1527 l_requested_for_user_id WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
1528 l_reg_svc_type WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
1529
1530 l_return_status varchar2 (10);
1531 l_msg_count number;
1532 l_msg_data varchar2 (280);
1533 l_formatted_name varchar2 (300);
1534 l_formatted_lines_cnt number;
1535 l_formatted_name_tbl hz_format_pub.string_tbl_type;
1536
1537 l_ad_hoc_role WF_ACTIVITY_ATTRIBUTES.text_default%type;
1538
1539 BEGIN
1540
1541 if (command = 'RUN') then
1542
1543 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1544 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1545 'fnd.plsql.UMXUTILB.createadhocrole.begin',
1546 'item_type: ' || item_type ||
1547 ' item_key: ' || item_key);
1548 end if;
1549
1550 -- if additional access workflow then we dont have to create adhoc role
1551 -- as it is already a system user
1552 l_reg_svc_type := wf_engine.getItemattrtext (
1553 itemtype => item_type,
1554 itemkey => item_key,
1555 aname => 'REG_SERVICE_TYPE');
1556
1557 if ((l_reg_svc_type <> 'ADDITIONAL_ACCESS') and
1558 (l_reg_svc_type <> 'ADMIN_ADDITIONAL_ACCESS')) then
1559
1560 --check if the adhoc user has already been created then just reactivate
1561 l_ad_hoc_role := wf_engine.getItemattrtext (
1562 itemtype => item_type,
1563 itemkey => item_key,
1564 aname => 'USER_ROLE_NAME');
1565
1566 if (l_ad_hoc_role is null) then
1567
1568 l_person_first_name := wf_engine.getItemattrtext (
1569 itemtype => item_type,
1570 itemkey => item_key,
1571 aname => 'FIRST_NAME');
1572
1573 l_person_middle_name := wf_engine.getItemattrtext (
1574 itemtype => item_type,
1575 itemkey => item_key,
1576 aname => 'MIDDLE_NAME');
1577
1578 l_person_last_name := wf_engine.getItemattrtext (
1579 itemtype => item_type,
1580 itemkey => item_key,
1581 aname => 'LAST_NAME');
1582
1583 l_prefix := wf_engine.getitemattrtext (
1584 itemtype => item_type,
1585 itemkey => item_key,
1586 aname => 'PRE_NAME_ADJUNCT');
1587
1588 l_suffix := wf_engine.getitemattrtext (
1589 itemtype => item_type,
1590 itemkey => item_key,
1591 aname => 'PERSON_SUFFIX');
1592
1593 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1594 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1595 'fnd.plsql.UMXUTILB.createadhocrole',
1596 'calling hz_format_pub.format_name with ' ||
1597 'p_person_first_name=>' || l_person_first_name ||
1598 ', p_person_middle_name=>' || l_person_middle_name ||
1599 ', p_person_last_name=>' || l_person_last_name ||
1600 ', p_person_title=>' || l_prefix ||
1601 ', and p_person_name_suffix=>' || l_suffix);
1602 end if;
1603
1604 hz_format_pub.format_name (
1605 p_person_first_name => l_person_first_name ,
1606 p_person_middle_name => l_person_middle_name,
1607 p_person_last_name => l_person_last_name,
1608 p_person_title => l_prefix,
1609 p_person_name_suffix => l_suffix,
1610 x_return_status => l_return_status,
1611 x_msg_count => l_msg_count,
1612 x_msg_data => l_msg_data,
1613 x_formatted_name => l_formatted_name,
1614 x_formatted_lines_cnt => l_formatted_lines_cnt,
1615 x_formatted_name_tbl => l_formatted_name_tbl);
1616
1617 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1618 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1619 'fnd.plsql.UMXUTILB.createadhocrole',
1620 'Done calling hz_format_pub.format_name with ' ||
1621 'x_return_status=>' || l_return_status ||
1622 ', x_msg_count=>' || l_msg_data ||
1623 ', x_formatted_name=>' || l_formatted_name ||
1624 ', x_formatted_lines_cnt=>' || l_formatted_lines_cnt);
1625 end if;
1626
1627 l_ad_hoc_role := '~UMX_' || item_key;
1628
1629 -- We don't care if hz_format_pub fails, just create a formatted name
1630 -- with first name and last name.
1631 if (l_formatted_name is null) then
1632 l_formatted_name := l_person_first_name || ' ' || l_person_last_name;
1633 end if;
1634
1635 wf_engine.setItemattrtext (
1636 itemtype => item_type,
1637 itemkey => item_key,
1638 aname => 'FORMATED_NAME',
1639 avalue => l_formatted_name);
1640
1641 add_param_to_event (p_item_type => item_type,
1642 p_item_key => item_key,
1643 p_attr_name => 'FORMATED_NAME',
1644 p_attr_value => l_formatted_name);
1645
1646 l_email_address := wf_engine.getItemattrtext (
1647 itemtype => item_type,
1648 itemkey => item_key,
1649 aname => 'EMAIL_ADDRESS');
1650
1651 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1652 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1653 'fnd.plsql.UMXUTILB.createadhocrole',
1654 'before calling wf_directory.CreateAdHocRole:' ||
1655 ' l_ad_hoc_role: ' || l_ad_hoc_role ||
1656 ' l_formatted_name: ' || l_formatted_name ||
1657 ' l_email_address: ' || l_email_address);
1658 end if;
1659
1660 wf_directory.CreateAdHocRole (
1661 role_name => l_ad_hoc_role,
1662 role_display_name => l_formatted_name,
1663 email_address => l_email_address,
1664 owner_tag => 'FND');
1665
1666 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1667 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1668 'fnd.plsql.UMXUTILB.createadhocrole',
1669 'After calling wf_directory.CreateAdHocRole.');
1670 end if;
1671 end if;
1672
1673 else
1674
1675 -- this regsvctype is additional accessrequest notification
1676 -- set USER_ROLE_NAME as the logged in USER_NAME and formating
1677 -- is taken care in UMXNTFSB.pls
1678
1679 l_requested_for_user_id := wf_engine.getItemattrtext (
1680 itemtype => item_type,
1681 itemkey => item_key,
1682 aname => 'REQUESTED_FOR_USER_ID');
1683
1684 if (l_requested_for_user_id is not null) then
1685
1686 select user_name into l_user_name
1687 from fnd_user
1688 where user_id = l_requested_for_user_id;
1689
1690 l_ad_hoc_role := l_user_name;
1691
1692 end if;
1693 end if;-- end for additional_access loop
1694
1695 wf_engine.setItemattrtext (
1696 itemtype => item_type,
1697 itemkey => item_key,
1698 aname => 'USER_ROLE_NAME',
1699 avalue => l_ad_hoc_role);
1700
1701 add_param_to_event (
1702 p_item_type => item_type,
1703 p_item_key => item_key,
1704 p_attr_name => 'USER_ROLE_NAME',
1705 p_attr_value => l_ad_hoc_role);
1706
1707 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1708 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1709 'fnd.plsql.UMXUTILB.createadhocrole.end', '');
1710 end if;
1711 end if;
1712
1713 EXCEPTION
1714 WHEN others THEN
1715 Wf_Core.Context ('UMX_NOTIFICATION_UTIL', 'create_ad_hoc_role',
1716 item_type, item_key, activity_id);
1717 raise;
1718 END create_ad_hoc_role;
1719
1720 --
1721 -- Procedure
1722 -- release_ad_hoc_role
1723 --
1724 -- Description
1725 -- remove the adhoc role
1726 -- IN
1727 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1728 -- itemkey - A string generated from the application object's primary key.
1729 -- actid - The function activity (instance id).
1730 -- funcmode - Run/Cancel/Timeout
1731 -- OUT
1732 -- resultout - result of the process based on which the next step is followed
1733 procedure release_ad_hoc_role (item_type in varchar2,
1734 item_key in varchar2,
1735 activity_id in number,
1736 command in varchar2,
1737 resultout out NOCOPY varchar2) is
1738
1739 l_adhoc_role_name WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
1740 l_reg_svc_type WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
1741
1742 begin
1743
1744 if (command = 'RUN') then
1745
1746 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1747 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1748 'fnd.plsql.UMXUTILB.releaseadhocrole.end',
1749 'item_type: ' || item_type ||
1750 ' item_key: ' || item_key);
1751 end if;
1752
1753 -- There is no need to release adhoc user in additional_access request
1754 -- since there is no adhoc user created.
1755 l_reg_svc_type := wf_engine.getItemattrtext (
1756 itemtype => item_type,
1757 itemkey => item_key,
1758 aname => 'REG_SERVICE_TYPE');
1759
1760 if ((l_reg_svc_type <> 'ADDITIONAL_ACCESS') and
1761 (l_reg_svc_type <> 'ADMIN_ADDITIONAL_ACCESS')) then
1762
1763 l_adhoc_role_name := wf_engine.getItemattrtext (
1764 itemtype => item_type,
1765 itemkey => item_key,
1766 aname => 'USER_ROLE_NAME');
1767
1768 if (l_adhoc_role_name = '~UMX_' || item_key) then
1769 wf_directory.setAdHocRoleStatus (role_name => l_adhoc_role_name,
1770 status => 'INACTIVE');
1771
1772 -- The expiration_date is set to +30 based on the recommandation from WF.
1773 wf_directory.setAdHocRoleExpiration (role_name => l_adhoc_role_name,
1774 expiration_date => sysdate + 30);
1775 end if;
1776
1777 end if;
1778
1779 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1780 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1781 'fnd.plsql.UMXUTILB.releaseadhocrole.end', '');
1782 end if;
1783 end if;
1784
1785 EXCEPTION
1786 WHEN others THEN
1787 Wf_Core.Context ('UMX_NOTIFICATION_UTIL', 'release_ad_hoc_role',
1788 item_type, item_key, activity_id);
1789 raise;
1790
1791 end release_ad_hoc_role;
1792
1793 procedure reject_request (p_item_type in varchar2,
1794 p_item_key in varchar2,
1795 p_activity_id in number,
1796 p_command in varchar2,
1797 p_resultout out NOCOPY varchar2) is
1798 Begin
1799 --null;
1800 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1801 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1802 'fnd.plsql.UMXUTILB.rejectrequest.begin',
1803 'itemkey: '||p_item_key);
1804 end if;
1805
1806 umx_reg_requests_pvt.reject_reg_request
1807 (p_reg_request_id => to_number (p_item_key));
1808
1809 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1810 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1811 'fnd.plsql.UMXUTILB.cancelusername.begin',
1812 'itemkey: '||p_item_key);
1813 end if;
1814 End reject_request;
1815
1816 -- Procedure
1817 -- cancel_username
1818 -- Description
1819 -- Wrapper around Fnd_user_pkg.delete_username with status as cancelled
1820 -- this is for failed identity verification
1821 -- IN
1822 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1823 -- itemkey - A string generated from the application object's primary key.
1824 -- actid - The function activity (instance id).
1825 -- command - Run/Cancel/Timeout
1826 -- OUT
1827 -- resultout - result of the process based on which the next step is followed
1828
1829 procedure cancel_username (p_item_type in varchar2,
1830 p_item_key in varchar2,
1831 p_activity_id in number,
1832 p_command in varchar2,
1833 p_resultout out NOCOPY varchar2) is
1834 l_username FND_USER.user_name%type;
1835 l_userid FND_USER.user_id%type;
1836 Begin
1837
1838 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1839 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1840 'fnd.plsql.UMXUTILB.cancelusername.begin',
1841 'itemkey: '||p_item_key);
1842 end if;
1843
1844 if (p_command = 'RUN') then
1845 l_username := wf_engine.getitemattrtext (itemtype => p_item_type,
1846 itemkey => p_item_key,
1847 aname => 'REQUESTED_USERNAME',
1848 ignore_notfound => false);
1849
1850 l_userid := wf_engine.getitemattrtext (itemtype => p_item_type,
1851 itemkey => p_item_key,
1852 aname => 'REQUESTED_FOR_USER_ID',
1853 ignore_notfound => false);
1854
1855
1856 umx_reg_requests_pvt.cancel_username_reg_request (
1857 p_reg_request_id => p_item_key,
1858 p_user_id => l_userid,
1859 p_username => l_username);
1860
1861 end if;
1862
1863 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1864 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1865 'fnd.plsql.UMXUTILB.cancelusername.end',
1866 'itemkey: '||p_item_key);
1867 end if;
1868
1869 End cancel_username;
1870
1871 -- Procedure
1872 -- update_user_status
1873 -- Description
1874 -- This method is to set the reg status to pending from veryfing
1875 -- this is for sucessful identity verification
1876 -- IN
1877 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1878 -- itemkey - A string generated from the application object's primary key.
1879 -- actid - The function activity (instance id).
1880 -- command - Run/Cancel/Timeout
1881 -- OUT
1882 -- resultout - result of the process based on which the next step is followed
1883
1884 procedure update_user_status (p_item_type in varchar2,
1885 p_item_key in varchar2,
1886 p_activity_id in number,
1887 p_command in varchar2,
1888 p_resultout out NOCOPY varchar2) is
1889 l_userid FND_USER.user_id%type;
1890 Begin
1891
1892 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1893 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1894 'fnd.plsql.UMXUTILB.updateuserstatus.begin',
1895 'itemkey: '||p_item_key);
1896 end if;
1897
1898 if (p_command = 'RUN') then
1899
1900 l_userid := wf_engine.getitemattrtext (itemtype => p_item_type,
1901 itemkey => p_item_key,
1902 aname => 'REQUESTED_FOR_USER_ID',
1903 ignore_notfound => false);
1904
1905 -- set the status from verifying to pending
1906 UMX_REG_REQUESTS_PKG.update_row (X_REG_REQUEST_ID => to_number (p_item_key),
1907 X_STATUS_CODE => 'PENDING');
1908
1909 end if;
1910
1911 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1912 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1913 'fnd.plsql.UMXUTILB.updateuserstatus.end',
1914 'itemkey: '||p_item_key);
1915 end if;
1916
1917 End update_user_status;
1918
1919 -- Procedure
1920 -- Launch_Custom_event
1921 --
1922 -- Description
1923 -- Launches the Custom Event, if one is defined.
1924 -- It also adds the context into event object
1925 -- IN
1926 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1927 -- itemkey - A string generated from the application object's primary key.
1928 -- actid - The function activity (instance id).
1929 -- funcmode - Run/Cancel/Timeout
1930 -- OUT
1931 -- resultout - result of the process based on which the next step is followed
1932 procedure Launch_Custom_Event (item_type in varchar2,
1933 item_key in varchar2,
1934 activity_id in number,
1935 command in varchar2,
1936 resultout out NOCOPY varchar2) is
1937
1938 l_custom_event wf_events.name%type;
1939 l_custom_event_context WF_ACTIVITY_ATTRIBUTES.text_default%TYPE;
1940 l_parameter_list wf_parameter_list_t;
1941 l_event wf_event_t;
1942 l_event_key number;
1943 l_reg_svc_type UMX_REG_SERVICES_B.REG_SERVICE_TYPE%TYPE;
1944
1945 begin
1946
1947 l_reg_svc_type := wf_engine.getitemattrtext (itemtype => item_type,
1948 itemkey => item_key,
1949 aname => 'REG_SERVICE_TYPE');
1950
1951 l_custom_event_context := wf_engine.getActivityAttrText (
1952 itemtype => item_type,
1953 itemkey => item_key,
1954 actid => activity_id,
1955 aname => 'UMX_CUSTOM_EVENT_CONTEXT');
1956
1957 if not ((l_reg_svc_type = 'ADMIN_CREATION' or
1958 l_reg_svc_type = 'SELF_SERVICE') and
1959 (l_custom_event_context = 'ROLE APPROVED')) then
1960
1961 -- If the request is ADMIN_CREATION or SELF_SERVICE, we only have to raise
1962 -- the event during before and after the account is created. We don't have
1963 -- to raise the event when it is role approved.
1964
1965 l_custom_event := wf_engine.getitemattrtext (itemtype => item_type,
1966 itemkey => item_key,
1967 aname => 'CUSTOM_EVENT_NAME');
1968
1969 if (l_custom_event is not null) then
1970 if (l_custom_event_context is not null) then
1971
1972 add_param_to_event (p_item_type => item_type,
1973 p_item_key => item_key,
1974 p_attr_name => 'UMX_CUSTOM_EVENT_CONTEXT',
1975 p_attr_value => l_custom_event_context);
1976
1977 select UMX_EVENTS_S.nextval into l_event_key from dual;
1978
1979 l_event := wf_engine.getitemattrevent (itemtype => item_type,
1980 itemkey => item_key,
1981 name => 'REGISTRATION_DATA');
1982
1983 l_parameter_list := l_event.getParameterlist ();
1984 wf_event.raise (l_custom_event, l_event_key, null, l_parameter_list, sysdate);
1985
1986 else
1987 raise_application_error ('-200001','Event Context is missing in UMX_REGISTRATION_UTIL.Launch_Custom_Event API.');
1988 end if;
1989 end if;
1990 end if;
1991 End Launch_Custom_Event;
1992
1993 -- bug#12324306 DEPRECATED procedure ICM_VIOLATION_CHECK
1994 -- Procedure
1995 -- ICM_VIOLATION_CHECK
1996 -- (DEPRECATED API)
1997 -- Description
1998 -- This API will call the ICM API to check if there are any violation
1999 -- with the requested role.
2000 -- IN
2001 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2002 -- itemkey - A string generated from the application object's primary key.
2003 -- actid - The function activity (instance id).
2004 -- funcmode - Run/Cancel/Timeout
2005 -- OUT
2006 -- resultout - result of the process based on which the next step is followed
2007 procedure ICM_VIOLATION_CHECK (item_type in varchar2,
2008 item_key in varchar2,
2009 activity_id in number,
2010 command in varchar2,
2011 resultout out NOCOPY varchar2) is
2012
2013
2014 BEGIN
2015
2016 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2017 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2018 'fnd.plsql.UMXUTILB.ICM_VIOLATION_CHECK.begin',
2019 'item_type=' || item_type ||
2020 ', item_key=' || item_key);
2021 end if;
2022
2023 EXCEPTION
2024 WHEN others THEN
2025 raise;
2026
2027 END ICM_VIOLATION_CHECK;
2028
2029 --
2030 -- Procedure
2031 -- launch_username_policy
2032 -- (DEPRECATED API)
2033 -- Description
2034 -- This method launches the username policy wf, if username passed to
2035 -- registration workflow is null.
2036 -- IN
2037 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2038 -- itemkey - A string generated from the application object's primary key.
2039 -- actid - The function activity (instance id).
2040 -- command - Run/Cancel/Timeout
2041 -- OUT
2042 -- resultout - result of the process based on which the next step is followed
2043
2044 procedure launch_username_policy (item_type in varchar2,
2045 item_key in varchar2,
2046 activity_id in number,
2047 command in varchar2,
2048 resultout out NOCOPY varchar2) is
2049
2050 l_person_party_id HZ_PARTIES.PARTY_ID%TYPE ;
2051 l_suggested_username FND_USER.USER_NAME%TYPE;
2052
2053 BEGIN
2054
2055 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2056 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2057 'fnd.plsql.UMXUTILB.launchusernamepolicy.begin',
2058 'itemkey: '||item_key);
2059 end if;
2060
2061 if (command = 'RUN') then
2062 l_person_party_id := wf_engine.getitemattrtext (itemtype => item_type,
2063 itemkey => item_key,
2064 aname => 'PERSON_PARTY_ID',
2065 ignore_notfound => true);
2066
2067 UMX_USERNAME_POLICY_PVT.get_suggested_username (
2068 p_person_party_id => l_person_party_id,
2069 x_suggested_username => l_suggested_username);
2070
2071 if (l_suggested_username is null) then
2072 resultout := 'COMPLETE:F';
2073 else
2074 wf_engine.setitemattrtext (itemtype => item_type,
2075 itemkey =>item_key,
2076 aname => 'REQUESTED_USERNAME',
2077 avalue => l_suggested_username);
2078 add_param_to_event (p_item_type => item_type,
2079 p_item_key => item_key,
2080 p_attr_name => 'REQUESTED_USERNAME' ,
2081 p_attr_value => l_suggested_username);
2082 resultout :='COMPLETE:T';
2083 end if;
2084 end if;
2085
2086 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2087 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2088 'fnd.plsql.UMXUTILB.launchusernamepolicy.end',
2089 'itemkey: '||item_key);
2090 end if;
2091
2092 EXCEPTION
2093 WHEN others THEN
2094 Wf_Core.Context ('UMX_REGISTRATION_UTIL', 'launch_username_policy', item_type, item_key, activity_id);
2095 raise;
2096 END launch_username_policy;
2097
2098 --
2099 -- Procedure
2100 -- Check_userName_null
2101 -- (DEPRECATED API)
2102 -- Description
2103 -- Check if the username is null
2104 -- IN
2105 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2106 -- itemkey - A string generated from the application object's primary key.
2107 -- actid - The function activity (instance id).
2108 -- command - Run/Cancel/Timeout
2109 -- OUT
2110 -- resultout - result of the process based on which the next step is followed
2111 procedure check_username_null (item_type in varchar2,
2112 item_key in varchar2,
2113 activity_id in number,
2114 command in varchar2,
2115 resultout out NOCOPY varchar2) is
2116
2117 l_username FND_USER.USER_NAME%TYPE;
2118 BEGIN
2119
2120 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2121 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2122 'fnd.plsql.UMXUTILB.checkusernamenull.begin',
2123 'itemkey: '||item_key);
2124 end if;
2125
2126 if (command = 'RUN') then
2127 l_username := wf_engine.getitemattrtext (itemtype => item_type,
2128 itemkey => item_key,
2129 aname => 'REQUESTED_USERNAME',
2130 ignore_notfound => true);
2131
2132 if (l_username is null) then
2133 resultout := 'COMPLETE:Y';
2134 else
2135 resultout := 'COMPLETE:N';
2136 end if;
2137 end if;
2138
2139 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2140 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2141 'fnd.plsql.UMXUTILB.checkusernamenull.end',
2142 'itemkey: '||item_key);
2143 end if;
2144
2145 EXCEPTION
2146 WHEN others THEN
2147 Wf_Core.Context ('UMX_REGISTRATION_UTIL', 'check_username_null', item_type, item_key, activity_id);
2148 raise;
2149 END check_username_null;
2150
2151 -- procedure custom_code
2152 -- (DEPRECATED API)
2153 -- This api should not have been invoked,
2154 -- it will be done only if username policy failed
2155 procedure custom_code (p_item_type in varchar2,
2156 p_item_key in varchar2,
2157 p_activity_id in number,
2158 p_command in varchar2,
2159 p_resultout out NOCOPY varchar2) is
2160 begin
2161 raise_application_error ('-20001','User Name Policy failed, username is null');
2162 end custom_code;
2163
2164 END UMX_REGISTRATION_UTIL;