1 PACKAGE BODY UMX_REG_REQUESTS_PVT AS
2 /* $Header: UMXVRRSB.pls 120.11 2011/02/17 08:11:07 spakanat ship $ */
3
4 -- procedure
5 --
6 --
7 PROCEDURE populateRegRecord (p_reg_request in out NOCOPY REG_REQUEST_TYPE,
8 x_reg_function_id out NOCOPY varchar2) IS
9
10 cursor getPersonPartyId ( x_user_id in number ) is
11
12 select PERSON_PARTY_ID
13 from FND_USER
14 where USER_ID = X_USER_ID
15 and nvl( END_DATE, sysdate+1) > sysdate;
16
17 cursor getRegSvcFromRegCode ( x_reg_service_code in varchar2 ) is
18
19 select URS.REG_SERVICE_TYPE, URS.WF_ROLE_NAME, URS.AME_APPLICATION_ID,
20 URS.AME_TRANSACTION_TYPE_ID, URS.REG_FUNCTION_ID, WE.NAME,
21 URS.EMAIL_VERIFICATION_FLAG
22 from UMX_REG_SERVICES_B URS, WF_EVENTS WE
23 where URS.REG_SERVICE_CODE = X_REG_SERVICE_CODE
24 and nvl(URS.END_DATE, sysdate+1) > sysdate
25 and URS.WF_NOTIFICATION_EVENT_GUID = WE.GUID;
26
27 cursor getRegSvcFromRoleName (x_wf_role_name in varchar2) is
28
29 select URS.REG_SERVICE_TYPE, URS.REG_SERVICE_CODE, URS.AME_APPLICATION_ID,
30 URS.AME_TRANSACTION_TYPE_ID, URS.REG_FUNCTION_ID, WE.NAME,
31 URS.EMAIL_VERIFICATION_FLAG
32 from UMX_REG_SERVICES_B URS, WF_EVENTS WE
33 where URS.WF_ROLE_NAME = x_wf_role_name
34 and nvl(URS.END_DATE, sysdate+1) > sysdate
35 and URS.REG_SERVICE_TYPE = 'ADDITIONAL_ACCESS'
36 and URS.WF_NOTIFICATION_EVENT_GUID = WE.GUID;
37
38 BEGIN
39
40 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
41 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
42 'fnd.plsql.UMXVRRSB.populateRegRecord.begin',
43 'regServiceCode: ' || p_reg_request.reg_service_code ||
44 ' | requestedForUserId: ' || p_reg_request.requested_for_user_id);
45 end if;
46
47 if (p_reg_request.reg_service_code is not null) then
48
49 -- ART request. query the person_party_id
50 -- throw exception if user id is not passed
51
52 if ( p_reg_request.requested_for_user_id is null) then
53 fnd_message.set_name('FND','UMX_COMMON_MISS_PARAM_MSG');
54 fnd_message.set_token('PARAM', 'p_reg_request.requested_for_user_id');
55 fnd_message.set_token('API', 'UMX_REG_REQUESTS_PVT.populateRegRecord');
56 raise_application_error ('-20000', fnd_message.get);
57
58 else
59
60 open getPersonPartyId ( p_reg_request.requested_for_user_id );
61 fetch getPersonPartyId into p_reg_request.requested_for_party_id;
62
63 if (getPersonPartyId%notfound) then
64 close getPersonPartyId;
65 fnd_message.set_name('FND','UMX_COMMON_MISS_PARAM_MSG');
66 fnd_message.set_token('PARAM', 'getPersonPartyId');
67 fnd_message.set_token('API', 'UMX_REG_REQUESTS_PVT.populateRegRecord');
68 raise_application_error ('-20000', fnd_message.get);
69 end if;
70
71 close getPersonPartyId;
72
73 end if;
74
75 -- populate the regrecord based on the reg_service_code
76 -- request from art
77 open getRegSvcFromRegCode (p_reg_request.reg_service_code);
78 fetch getRegSvcFromRegCode into
79 p_reg_request.reg_service_type,
80 p_reg_request.wf_role_name,
81 p_reg_request.ame_application_id,
82 p_reg_request.AME_TRANSACTION_TYPE_ID,
83 x_reg_function_id,
84 p_reg_request.WF_EVENT_NAME,
85 p_reg_request.EMAIL_VERIFICATION_FLAG;
86
87 if (getRegSvcFromRegCode%notfound) then
88 close getRegSvcFromRegCode;
89 raise_application_error('-20000','illegal reg_service_code passed');
90 end if;
91
92 close getRegSvcFromRegCode;
93
94 elsif (p_reg_request.wf_role_name is null) then
95 -- this is a smart request and role name should be passed
96 fnd_message.set_name('FND','UMX_COMMON_MISS_PARAM_MSG');
97 fnd_message.set_token('PARAM', 'p_reg_request.wf_role_name');
98 fnd_message.set_token('API', 'UMX_REG_REQUESTS_PVT.populateRegRecord');
99 raise_application_error ('-20000', fnd_message.get);
100 end if;
101
102 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
103 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
104 'fnd.plsql.UMXVRRSB.populateRegRecord',
105 'regServiceCode: ' || p_reg_request.reg_service_code ||
106 ' | requestedForUserId: ' || p_reg_request.requested_for_user_id);
107 end if;
108
109 if (p_reg_request.reg_service_code is null and
110 p_reg_request.wf_role_name is not null) then
111 -- query data based on role name smart request
112 open getRegSvcFromRoleName (p_reg_request.wf_role_name);
113
114 fetch getRegSvcFromRoleName into
115 p_reg_request.reg_service_type,
116 p_reg_request.reg_service_code,
117 p_reg_request.ame_application_id,
118 p_reg_request.AME_TRANSACTION_TYPE_ID,
119 x_reg_function_id,
120 p_reg_request.WF_EVENT_NAME,
121 p_reg_request.EMAIL_VERIFICATION_FLAG;
122
123 if (getRegSvcFromRoleName%notfound) then
124 -- this is a direct assigned role from smart
125 p_reg_request.reg_service_type := 'DIRECT_ASSIGNED';
126 end if;
127
128 close getRegSvcFromRoleName;
129 end if;
130
131 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
132 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
133 'fnd.plsql.UMXVRRSB.populateRegRecord.end',
134 'regFunctionId:' || x_reg_function_id);
135 end if;
136
137 END populateRegRecord;
138
139 --procedure
140 --
141 --
142 PROCEDURE validate_fnd_lookup (p_lookup_type IN VARCHAR2,
143 p_column IN VARCHAR2,
144 p_column_value IN VARCHAR2,
145 x_return_status IN OUT NOCOPY VARCHAR2) IS
146 CURSOR c1 IS
147 SELECT 'Y'
148 FROM fnd_lookup_values
149 WHERE lookup_type = p_lookup_type
150 AND lookup_code = p_column_value
151 AND ROWNUM = 1;
152
153 l_exist VARCHAR2(1);
154
155 BEGIN
156
157 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
158 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
159 'fnd.plsql.UMXVRRSB.validate_fnd_lookup.begin',
160 'lookupType: ' || p_lookup_type ||
161 ' | column: ' || p_column ||
162 ' | columnValue: ' || p_column_value);
163 end if;
164
165 IF (p_column_value IS NOT NULL AND p_column_value <> fnd_api.g_miss_char ) THEN
166 OPEN c1;
167 FETCH c1 INTO l_exist;
168 IF c1%NOTFOUND THEN
169 CLOSE c1;
170 fnd_message.set_name('AR','HZ_API_INVALID_LOOKUP');
171 fnd_message.set_token('COLUMN',p_column);
172 fnd_message.set_token('LOOKUP_TYPE',p_lookup_type);
173 fnd_msg_pub.add;
174 x_return_status := fnd_api.g_ret_sts_error;
175 END IF;
176 CLOSE c1;
177 END IF;
178
179 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
180 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
181 'fnd.plsql.UMXVRRSB.validate_fnd_lookup.end',
182 'returnStatus: ' || x_return_status);
183 end if;
184
185 END validate_fnd_lookup;
186
187 --
188 -- Procedure : update_reg_request
189 -- Type : Private
190 -- Pre_reqs : None
191 -- Description : This API will create a registration request
192 -- into the UMX_REG_REQUESTS table.
193 -- Before registration request can be inserted into
194 -- UMX_REG_REQUESTS table, this API will check to see
195 -- if the requester already have a valid association to
196 -- this access role in wf_local_user_role. This API will
197 -- return null if there is a valid access role.
198 -- Input Parameters (Mandatory):
199 -- p_reg_request.reg_type_code: The code of the registration service type
200 -- code.
201 --
202 -- At least one of the below parameter needs to be passed in as an input
203 -- parameter:
204 -- p_reg_request.wf_role_name: The user_id of the user who this
205 -- registration request is requested for.
206 -- p_reg_request.reg_service_code: The Person Party ID of the person who
207 -- this request is requested for.
208 -- Input Parameters (non-Mandatory):
209 -- p_extra_check: Check if user already has an association with the role.
210 -- Output Parameters:
211 -- x_reg_request_id: Registration Request ID
212 --
213 procedure update_reg_request (p_reg_request in out NOCOPY REG_REQUEST_TYPE) IS
214 BEGIN
215 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
216 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
217 'fnd.plsql.UMXVRRSB.update_reg_request.begin', 'Begin');
218 end if;
219
220 UMX_REG_REQUESTS_PKG.update_row (
221 X_REG_REQUEST_ID => p_reg_request.reg_request_id,
222 X_STATUS_CODE => p_reg_request.status_code,
223 X_REQUESTED_BY_USER_ID => fnd_global.user_id,
224 X_REQUESTED_FOR_USER_ID => p_reg_request.requested_for_user_id,
225 X_REQUESTED_FOR_PARTY_ID => p_reg_request.requested_for_party_id,
226 X_REQUESTED_USERNAME => upper (p_reg_request.requested_username),
227 X_REQUESTED_START_DATE => p_reg_request.requested_start_date,
228 X_REQUESTED_END_DATE => p_reg_request.requested_end_date,
229 X_WF_ROLE_NAME => p_reg_request.wf_role_name,
230 X_REG_SERVICE_CODE => p_reg_request.reg_service_code,
231 X_AME_APPLICATION_ID => p_reg_request.ame_application_id,
232 X_AME_TRANSACTION_TYPE_ID => p_reg_request.ame_transaction_type_id,
233 X_JUSTIFICATION => p_reg_request.justification
234 );
235 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
236 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
237 'fnd.plsql.UMXVRRSB.update_reg_request.end', 'End');
238 end if;
239
240 END update_reg_request;
241
242 --
243 -- Procedure : delete_reg_request
244 -- Type : Private
245 -- Pre_reqs : None
246 -- Description : This API will delete a registration request
247 -- into the UMX_REG_REQUESTS table.
248 -- Before registration request can be inserted into
249 -- UMX_REG_REQUESTS table, this API will check to see
250 -- if the requester already have a valid association to
251 -- this access role in wf_local_user_role. This API will
252 -- return null if there is a valid access role.
253 -- Input Parameters (Mandatory):
254 -- p_reg_request.reg_type_code: The code of the registration service type
255 -- code.
256 --
257 -- At least one of the below parameter needs to be passed in as an input
258 -- parameter:
259 -- p_reg_request.wf_role_name: The user_id of the user who this
260 -- registration request is requested for.
261 -- p_reg_request.reg_service_code: The Person Party ID of the person who
262 -- this request is requested for.
263 -- Input Parameters (non-Mandatory):
264 -- p_extra_check: Check if user already has an association with the role.
265 -- Output Parameters:
266 -- x_reg_request_id: Registration Request ID
267 --
268 procedure delete_reg_request (
269 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE
270 ) is
271 BEGIN
272 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
273 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
274 'fnd.plsql.UMXVRRSB.delete_reg_request.begin',
275 'regRequestId: ' || p_reg_request_id);
276 end if;
277
278 UMX_REG_REQUESTS_PKG.DELETE_ROW (X_REG_REQUEST_ID => p_reg_request_id);
279
280 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
281 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
282 'fnd.plsql.UMXVRRSB.delete_reg_request.end', 'End');
283 end if;
284
285 END delete_reg_request;
286
287 --
288 -- Function : is_username_available
289 -- Type : PRIVATE
290 -- Pre_reqs : None
291 -- Description : It will query if username is being used in
292 -- FND_USER table.
293 -- input parameters :
294 -- @param p_username
295 -- Description: username to perform the check
296 -- Required : Y
297 -- output :
298 -- Description : It will output boolean value of true or false.
299 -- true - username is available
300 -- false - username is not available
301 --
302 function is_username_available (p_username in FND_USER.USER_NAME%TYPE) return boolean is
303
304 cursor getUserFromFNDUSER (l_username in fnd_user.user_name%type) is
305 select user_name
306 from fnd_user
307 where user_name = l_username;
308
309 l_username_available boolean;
310 l_username fnd_user.user_name%type;
311
312 begin
313
314 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
315 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
316 'fnd.plsql.UMXVRRSB.is_username_available.begin',
317 'username: ' || p_username);
318 end if;
319
320 l_username := (RTRIM (LTRIM (p_username)));
321
322 if (p_username is null) or (l_username is null) then
323 -- Username is a required input parameter.
324 fnd_message.set_name('FND','UMX_COMMON_MISS_PARAM_MSG');
325 fnd_message.set_token('PARAM', 'p_username');
326 fnd_message.set_token('API', 'UMX_REG_REQUESTS_PVT.is_username_available');
327 raise_application_error ('-20000', fnd_message.get);
328 end if;
329
330 open getUserFromFNDUSER (l_username);
331 fetch getUserFromFNDUSER into l_username;
332 if (getUserFromFNDUSER%notfound) then
333 -- Query didn't find out username in FND_USER table,
334 -- username is available
335 l_username_available := true;
336
337 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
338 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
339 'fnd.plsql.UMXVRRSB.is_username_available.end',
340 'usernameAvailable: true');
341 end if;
342
343 else
344 -- Query returns something.
345 -- username is not available
346 l_username_available := false;
347
348 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
349 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
350 'fnd.plsql.UMXVRRSB.is_username_available.end',
351 'usernameAvailable: false');
352 end if;
353
354 end if;
355 close getUserFromFNDUSER;
356
357 return l_username_available;
358
359 end is_username_available;
360
361 --
362 -- Function : reserve_username
363 -- Type : Private
364 -- Pre_reqs : None
365 -- Description : This API will ...
366 -- 1) Call fnd_user_pkg.reserve_username API to reserve
367 -- the requested username.
368 -- 2) Update the UMX_REG_REQUESTS table with the
369 -- requested for username and requested by username
370 -- (if requested by is null).
371 --
372 -- This API should be called when user requests a user
373 -- account
374 --
375 -- Input Parameters (Mandatory):
376 -- p_reg_request_id : Registration Request ID
377 -- p_username : username to be reserved
378 -- p_owner : 'SEED', 'CUST' (customer) or NULL
379 -- (fnd_global.user_id)
380 -- p_unencrypted_password : Unencrypted password
381 -- Output Parameters:
382 -- Description : It will either return the user ID if the username is
383 -- successfully reserved or null if otherwise.
384 --
385 function reserve_username (
386 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
387 p_username in FND_USER.USER_NAME%TYPE,
388 p_owner in varchar2 default null,
389 p_unencrypted_password in varchar2,
390 p_session_number in number default 0,
391 p_last_logon_date in date default null,
392 p_description in varchar2 default null,
393 p_password_date in date default null,
394 p_password_accesses_left in number default null,
395 p_password_lifespan_accesses in number default null,
396 p_password_lifespan_days in number default null,
397 p_email_address in FND_USER.EMAIL_ADDRESS%TYPE default null,
398 p_fax in varchar2 default null,
399 p_person_party_id in FND_USER.PERSON_PARTY_ID%TYPE default null
400 ) return fnd_user.user_id%type is
401
402 l_user_id fnd_user.user_id%type;
403 l_requested_by_user_id UMX_reg_requests.requested_by_user_id%type;
404
405 cursor getRequestedByUserId (p_reg_request_id in UMX_reg_requests.reg_request_id%type) is
406 select requested_by_user_id
407 from umx_reg_requests
408 where reg_request_id = p_reg_request_id;
409
410 begin
411
412 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
413 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
414 'fnd.plsql.UMXVRRSB.reserve_username.begin',
415 'regRequestId: ' || p_reg_request_id ||
416 ' | username: ' || p_username ||
417 ' | owner: ' || p_owner ||
418 ' | sessionNumber: ' || p_session_number ||
419 ' | lastLogonDate: ' || p_last_logon_date ||
420 ' | description: ' || p_description ||
421 ' | passwordDate: ' || p_password_date ||
422 ' | passwordAccessesLeft: ' || p_password_accesses_left ||
423 ' | passwordLifespanAccesses: ' || p_password_lifespan_accesses ||
424 ' | passwordLifespanDays: ' || p_password_lifespan_days ||
425 ' | emailAddress: ' || p_email_address ||
426 ' | fax: ' || p_fax ||
427 ' | personPartyId: ' || p_person_party_id);
428 end if;
429
430 -- First call fnd's resrve_username to reserve a username in FND user table.
431 -- Still waiting for their true implementation from the proposal
432 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
433 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
434 'fnd.plsql.UMXVRRSB.reserve_username',
435 'Before calling fnd_user_pkg.CreatePendingUser');
436 end if;
437
438 l_user_id := fnd_user_pkg.CreatePendingUser (
439 x_user_name => p_username,
440 x_owner => p_owner,
441 x_unencrypted_password => p_unencrypted_password,
442 x_session_number => p_session_number,
443 x_description => p_description,
444 x_password_date => nvl (p_password_date, fnd_user_pkg.null_date),
445 x_password_accesses_left => p_password_accesses_left,
446 x_password_lifespan_accesses => p_password_lifespan_accesses,
447 x_password_lifespan_days => p_password_lifespan_days,
448 x_email_address => p_email_address,
449 x_fax => p_fax,
450 x_person_party_id => p_person_party_id);
451
452 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
453 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
454 'fnd.plsql.UMXVRRSB.reserve_username',
455 'After calling fnd_user_pkg.CreatePendingUser');
456 end if;
457
458 -- Find who is the requested_by_user_id. If it is null, then
459 -- we need to update the new user_id to the requested_by_user_id as well.
460 open getRequestedByUserId (p_reg_request_id);
461 fetch getRequestedByUserId into l_requested_by_user_id;
462 if (getRequestedByUserId%notfound) then
463 close getRequestedByUserId;
464 raise_application_error ('-20000', '<<requested_by_user_id is missing in the umx_reg_requests table>>');
465 end if;
466 close getRequestedByUserId;
467
468 if (l_requested_by_user_id is null) then
469 -- requested_by_user_id is null, need to update with l_user_id.
470 l_requested_by_user_id := l_user_id;
471 end if;
472
473 -- Update the Reg Requests table
474 UMX_REG_REQUESTS_PKG.update_row (
475 X_REG_REQUEST_ID => p_reg_request_id,
476 X_REQUESTED_BY_USER_ID => l_requested_by_user_id,
477 X_REQUESTED_FOR_USER_ID => l_USER_ID,
478 X_REQUESTED_USERNAME => p_username,
479 X_STATUS_CODE => 'PENDING');
480
481 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
482 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
483 'fnd.plsql.UMXVRRSB.reserve_username.end',
484 'userId: ' || l_user_id);
485 end if;
486
487 return l_user_id;
488
489 end reserve_username;
490
491 --
492 -- Procedure : approve_username_reg_request
493 -- Type : Private
494 -- Pre_reqs : None
495 -- Description : This API will ...
496 -- 1) Activiate the user account by calling
497 -- fnd_user_pkg.activate_user_account.
498 -- 2) Set the status code to "APPROVED" in
499 -- UMX_REG_REQUESTS table.
500 --
501 -- This API should be called from Self-Service Registration or
502 -- Admin Creation.
503 -- Input Parameters :
504 -- @param p_reg_request_id
505 -- Description : ID for the registration request
506 -- Required : Yes
507 -- @param p_username
508 -- Description : The username of the user account.
509 -- Required : Yes
510 -- @param p_start_date
511 -- Description : Starting active date of the user account.
512 -- Required : No
513 -- @param p_end_date
514 -- Description : Inactive date of the user account.
515 -- Required : No
516 -- @param p_person_party_id
517 -- Description : The person party ID of the user account.
518 -- Required : No
519
520 -- Output :
521 -- None
522 -- Description :
523 --
524 Procedure approve_username_reg_request (
525 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
526 p_username in FND_USER.USER_NAME%TYPE,
527 p_person_party_id in FND_USER.PERSON_PARTY_ID%TYPE,
528 p_start_date in FND_USER.START_DATE%TYPE default sysdate,
529 p_end_date in FND_USER.END_DATE%TYPE default null) is
530
531 l_start_date fnd_user.start_date%type;
532 eid fnd_user.employee_id%type;
533 pid per_people_f.person_id%type;
534 oid hz_parties.orig_system_reference%type;
535 uid fnd_user.user_id%type;
536
537 begin
538
539 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
540 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
541 'fnd.plsql.UMXVRRSB.approve_username_reg_request.begin',
542 'regRequestId: ' || p_reg_request_id ||
543 ' | username: ' || p_username ||
544 ' | personPartyId: ' || p_person_party_id ||
545 ' | startDate: ' || p_start_date ||
546 ' | endDate: ' || p_end_date);
547 end if;
548
549 -- Call FND's activate_user_account API to activate the user account.
550 fnd_user_pkg.EnableUser (
551 username => p_username,
552 start_date => nvl (p_start_date, sysdate),
553 end_date => nvl (p_end_date, fnd_user_pkg.null_date));
554
555 -- Need to update the Person Party's ID in the FND_USER table
556 fnd_user_pkg.UpdateUserParty (
557 x_user_name => p_username,
558 x_owner => NULL,
559 x_person_party_id => p_person_party_id);
560
561
562 -- begin changes for hrms future employee and security attributes
563 BEGIN
564 SELECT user_id
565 INTO uid
566 FROM FND_USER
567 WHERE USER_NAME = UPPER(p_username) ;
568
569 EXCEPTION
570 WHEN NO_DATA_FOUND THEN
571 uid := NULL;
572 END;
573 BEGIN
574 SELECT orig_system_reference
575 INTO oid
576 FROM HZ_PARTIES
577 WHERE party_id = p_person_party_id ;
578
579 EXCEPTION
580 WHEN NO_DATA_FOUND THEN
581 oid := NULL;
582 END;
583 -- If the user is an employee, then populate employee security attributes
584 IF(oid LIKE 'PER%') THEN
585 BEGIN
586 SELECT EMPLOYEE_ID
587 INTO eid
588 FROM FND_USER
589 WHERE USER_NAME = UPPER(p_username) ;
590
591 EXCEPTION
592 WHEN NO_DATA_FOUND THEN
593 eid := NULL;
594 END;
595 -- for future users employee id wont be populated even if orig system ref is PER%
596 IF(EID IS NULL ) THEN
597 -- return the first person_id from HRMS , this would be used to populate FND_USER
598 BEGIN
599 SELECT person_id
600 INTO pid
601 FROM per_people_f
602 WHERE party_id = p_person_party_id
603 AND rownum =1;
604
605 EXCEPTION
606 WHEN NO_DATA_FOUND THEN
607 pid := NULL;
608 END;
609 -- Changes for employee security attributes ( For HRMS Employee) + for future employee bug#7460262
610 -- calling UpdateUser would populate default security attributes, but for this customer one-off we
611 -- are explicitily inserting them. The insert commands should be removed for next releases
612 fnd_user_pkg.UpdateUser (x_user_name =>p_username, x_owner => NULL , x_employee_id =>pid);
613 eid :=pid;
614 END IF;
615 -- Changes for employee security attributes ( For HRMS Employee)
616 -- populate both the default security attributes with employee_id as value.
617 BEGIN
618 -- ICX_HR_PERSON_ID
619 UPDATE AK_WEB_USER_SEC_ATTR_VALUES
620 SET NUMBER_VALUE = eid ,
621 LAST_UPDATED_BY = fnd_global.user_id,
622 LAST_UPDATE_DATE = SYSDATE ,
623 LAST_UPDATE_LOGIN = fnd_global.login_id
624 WHERE WEB_USER_ID = uid
625 AND ATTRIBUTE_CODE = 'ICX_HR_PERSON_ID'
626 AND ATTRIBUTE_APPLICATION_ID = 178;
627
628 IF (sql%rowcount = 0) THEN
629 INSERT
630 INTO ak_web_user_sec_attr_values
631 (
632 web_user_id ,
633 attribute_code ,
634 attribute_application_id,
635 number_value ,
636 created_by ,
637 creation_date ,
638 last_updated_by ,
639 last_update_date ,
640 last_update_login
641 )
642 VALUES
643 (
644 uid ,
645 'ICX_HR_PERSON_ID',
646 178 ,
647 eid ,
648 fnd_global.user_id,
649 SYSDATE ,
650 fnd_global.user_id,
651 SYSDATE ,
652 fnd_global.login_id
653 );
654
655 END IF;
656 -- TO_PERSON_ID
657 UPDATE AK_WEB_USER_SEC_ATTR_VALUES
658 SET NUMBER_VALUE = eid ,
659 LAST_UPDATED_BY =fnd_global.user_id,
660 LAST_UPDATE_DATE = SYSDATE ,
661 LAST_UPDATE_LOGIN = fnd_global.login_id
662 WHERE WEB_USER_ID = uid
663 AND ATTRIBUTE_CODE = 'TO_PERSON_ID'
664 AND ATTRIBUTE_APPLICATION_ID = 178;
665
666 IF (sql%rowcount = 0) THEN
667 INSERT
668 INTO ak_web_user_sec_attr_values
669 (
670 web_user_id ,
671 attribute_code ,
672 attribute_application_id,
673 number_value ,
674 created_by ,
675 creation_date ,
676 last_updated_by ,
677 last_update_date ,
678 last_update_login
679 )
680 VALUES
681 (
682 uid ,
683 'TO_PERSON_ID' ,
684 178 ,
685 eid ,
686 fnd_global.user_id,
687 SYSDATE ,
688 fnd_global.user_id,
689 SYSDATE ,
690 fnd_global.login_id
691 );
692
693 END IF;
694 EXCEPTION
695 WHEN OTHERS THEN
696 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
697 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE, 'fnd.plsql.UMXVRRSB.approve_username_reg_request.begin', 'When inserting security attributes');
698 END IF;
699 END;
700 END IF;
701 --end changes for hrms and security attributes
702
703
704
705 -- Update the record in the Reg Requests table with status and party id
706 UMX_REG_REQUESTS_PKG.update_row (
707 X_REG_REQUEST_ID => p_reg_request_id,
708 X_STATUS_CODE => 'APPROVED',
709 X_REQUESTED_FOR_PARTY_ID => p_person_party_id);
710
711 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
712 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
713 'fnd.plsql.UMXVRRSB.approve_username_reg_request.end', 'End');
714 end if;
715
716 end approve_username_reg_request;
717
718 --
719 -- Procedure : reject_cancel_username_reg_req
720 -- Type : Private
721 -- Pre_reqs : None
722 -- Description : This API will ...
723 -- 1) Release the username by calling
724 -- fnd_user_pkg.release_username API.
725 -- 2) Set the status code to "REJECT" or "CANCELLED" in
726 -- UMX_REG_REQUESTS table.
727 -- 3) Remove the REQUESTED_FOR_USER_ID
728 -- 4) If the REQUESTED_BY_USER_ID is the same as the
729 -- REQUESTED_FOR_USER_ID, remove the
730 -- REQUESTED_BY_USER_ID
731 --
732 -- Input Parameters :
733 -- @param p_reg_request_id
734 -- Description : ID for the registration request
735 -- Required : Yes
736 -- @param p_username
737 -- Description : Username of the account
738 -- Required : Yes
739 -- @param p_user_id
740 -- Description : User ID of the account
741 -- Required : Yes
742 -- @param p_status_code
743 -- Description : Status code of the reg request
744 -- Required : Yes
745 -- Output :
746 -- None
747 -- Description :
748 --
749 Procedure reject_cancel_username_reg_req (
750 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
751 p_username in FND_USER.USER_NAME%TYPE,
752 p_user_id in FND_USER.USER_ID%TYPE,
753 p_status_code in UMX_REG_REQUESTS.STATUS_CODE%TYPE) is
754
755 l_requested_by_user_id umx_reg_requests.requested_by_user_id%type;
756
757 cursor getRequestedByUserID (p_reg_request_id in UMX_reg_requests.reg_request_id%type) is
758 select requested_by_user_id
759 from umx_reg_requests
760 where reg_request_id = p_reg_request_id;
761
762 begin
763
764 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
765 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
766 'fnd.plsql.UMXVRRSB.reject_cancel_username_reg_req.begin',
767 'regRequestId: ' || p_reg_request_id ||
768 ' | username: ' || p_username ||
769 ' | userId: ' || p_user_id ||
770 ' | statusCode: ' || p_status_code);
771 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
772 'fnd.plsql.UMXVRRSB.reject_cancel_username_reg_req.begin',
773 'Before calling fnd_user_pkg.RemovePendingUser');
774 end if;
775
776 -- Call FND's release_username API to release/delete the username
777 fnd_user_pkg.RemovePendingUser (username => p_username);
778
779 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
780 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
781 'fnd.plsql.UMXVRRSB.reject_cancel_username_reg_req.begin',
782 'After calling fnd_user_pkg.RemovePendingUser');
783 end if;
784
785 -- Query the requested_by_user_id from UMX_reg_requests table
786 -- If the requested_by_user_id is equal to the requested_for_user_id,
787 -- make requested_by_user_id null to avoid dangling foreign key.
788 open getRequestedByUserID (p_reg_request_id);
789 fetch getRequestedByUserID into l_requested_by_user_id;
790 if (getRequestedByUserID%notfound) then
791 -- cannot find the record
792 close getRequestedByUserID;
793 raise_application_error ('-20000', '<<is this a correct p_reg_request_id?>>');
794 end if;
795
796 if (p_user_id = l_requested_by_user_id) then
797 -- User requested his own account, we need to make the
798 -- requested_by_user_id to null.
799 l_requested_by_user_id := fnd_api.g_miss_num;
800 end if;
801
802 UMX_REG_REQUESTS_PKG.update_row (
803 X_REG_REQUEST_ID => p_reg_request_id,
804 X_STATUS_CODE => p_status_code,
805 X_REQUESTED_FOR_USER_ID => fnd_api.g_miss_num,
806 X_REQUESTED_BY_USER_ID => l_requested_by_user_id);
807
808 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
809 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
810 'fnd.plsql.UMXVRRSB.reject_cancel_username_reg_req.end', 'End');
811 end if;
812
813 end reject_cancel_username_reg_req;
814
815 --
816 -- Procedure : reject_username_reg_request
817 -- Type : Private
818 -- Pre_reqs : None
819 -- Description : This API will call reject_cancel_username_reg_req
820 -- with status code = "REJECTED".
821 --
822 -- This API should be called from Self-Service
823 -- Registration or Admin Creation.
824 -- Input Parameters :
825 -- @param p_reg_request_id
826 -- Description : ID for the registration request
827 -- Required : Yes
828 -- @param p_user_id
829 -- Description : User ID of the user account
830 -- Required : Yes
831 -- @param p_username
832 -- Description : Username of the account
833 -- Required : Yes
834 -- Output :
835 -- None
836 -- Description :
837 --
838 Procedure reject_username_reg_request (
839 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
840 p_user_id in FND_USER.USER_ID%TYPE,
841 p_username in FND_USER.USER_NAME%TYPE) is
842
843 begin
844
845 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
846 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
847 'fnd.plsql.UMXVRRSB.reject_username_reg_request.begin',
848 'regRequestId: ' || p_reg_request_id ||
849 ' | userId: ' || p_user_id ||
850 ' | username: ' || p_username);
851 end if;
852
853 -- Call reject_cancel_username_reg_req with status code = 'REJECTED'
854 reject_cancel_username_reg_req (
855 p_reg_request_id => p_reg_request_id,
856 p_username => p_username,
857 p_user_id => p_user_id,
858 p_status_code => 'REJECTED');
859
860 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
861 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
862 'fnd.plsql.UMXVRRSB.reject_username_reg_request.end', 'End');
863 end if;
864
865 end reject_username_reg_request;
866
867 --
868 -- Procedure : cancel_username_reg_request
869 -- Type : Private
870 -- Pre_reqs : None
871 -- Description : This API will call reject_cancel_username_reg_req API
872 -- with status_code = 'CANCELLED'.
873 --
874 -- This API should be called from Self-Service Registration or
875 -- Admin Creation.
876 -- Input Parameters :
877 -- @param p_reg_request_id
878 -- Description : ID for the registration request
879 -- Required : Yes
880 -- @param p_user_id
881 -- Description : ID of the user account
882 -- Required : Yes
883 -- @param p_username
884 -- Description : Username of the account
885 -- Required : Yes
886 -- Output :
887 -- None
888 -- Description :
889 --
890 Procedure cancel_username_reg_request (
891 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
892 p_user_id in FND_USER.USER_ID%TYPE,
893 p_username in FND_USER.USER_NAME%TYPE) is
894
895 begin
896
897 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
898 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
899 'fnd.plsql.UMXVRRSB.cancel_username_reg_request.begin',
900 'regRequestId: ' || p_reg_request_id ||
901 ' | userId: ' || p_user_id ||
902 ' | username: ' || p_username);
903 end if;
904
905 -- Call reject_cancel_username_reg_req with status code = 'CANCELLED'
906 reject_cancel_username_reg_req (
907 p_reg_request_id => p_reg_request_id,
908 p_username => p_username,
909 p_user_id => p_user_id,
910 p_status_code => 'CANCELLED');
911
912 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
913 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
914 'fnd.plsql.UMXVRRSB.cancel_username_reg_request.end', 'End');
915 end if;
916
917 end cancel_username_reg_request;
918
919 --
920 -- Procedure : approve_reject_reg_request
921 -- Type : Private
922 -- Pre_reqs : None
923 -- Description : This API will set the status code of a record in
924 -- UMX_REG_REQUESTS table.
925 --
926 -- Input Parameters :
927 -- @param p_reg_request_id
928 -- Description : ID for the registration request
929 -- Required : Yes
930 -- @param p_status_code
931 -- Description : Status code of the record in UMX_REG_REQUESTS table
932 -- Required : Yes
933 -- Output :
934 -- None
935 -- Description :
936 --
937 Procedure approve_reject_reg_request (
938 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
939 p_status_code in UMX_REG_REQUESTS.STATUS_CODE%type) is
940
941 begin
942
943 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
944 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
945 'fnd.plsql.UMXVRRSB.approve_reject_reg_request.begin',
946 'regRequestId: ' || p_reg_request_id ||
947 ' | statusCode: ' || p_status_code);
948 end if;
949
950 -- update the record in the Reg Requests table with status to status_code
951 UMX_REG_REQUESTS_PKG.update_row (X_REG_REQUEST_ID => p_reg_request_id,
952 X_STATUS_CODE => p_status_code);
953
954 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
955 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
956 'fnd.plsql.UMXVRRSB.approve_reject_reg_request.end', 'End');
957 end if;
958
959 end approve_reject_reg_request;
960
961 --
962 -- Procedure : approve_reg_request
963 -- Type : Private
964 -- Pre_reqs : None
965 -- Description : This API will approve_reject_reg_request to approve
966 -- the Reg Request in UMX_REG_REQUESTS table.
967 --
968 -- This API should be called from ART or SMART.
969 -- Input Parameters :
970 -- @param p_reg_request_id
971 -- Description : ID for the registration request
972 -- Required : Yes
973 -- Output :
974 -- None
975 -- Description :
976 --
977 Procedure approve_reg_request (p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE) is
978 begin
979
980 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
981 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
982 'fnd.plsql.UMXVRRSB.approve_reg_request.begin',
983 'regRequestId: ' || p_reg_request_id);
984 end if;
985
986 -- Call approve_reject_reg_request to update the record in
987 -- UMX_REG_REQUESTS table.
988 approve_reject_reg_request (p_reg_request_id => p_reg_request_id,
989 p_status_code => 'APPROVED');
990
991 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
992 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
993 'fnd.plsql.UMXVRRSB.approve_reg_request.end', 'End');
994 end if;
995
996 end approve_reg_request;
997
998 --
999 -- Procedure : reject_reg_request
1000 -- Type : Private
1001 -- Pre_reqs : None
1002 -- Description : This API will approve_reject_reg_request to reject
1003 -- the Reg Request in UMX_REG_REQUESTS table.
1004 --
1005 -- This API should be called from ART or SMART.
1006 -- Input Parameters :
1007 -- @param p_reg_request_id
1008 -- Description : ID for the registration request
1009 -- Required : Yes
1010 -- Output :
1011 -- None
1012 -- Description :
1013 --
1014 Procedure reject_reg_request (p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE) is
1015 begin
1016
1017 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1018 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1019 'fnd.plsql.UMXVRRSB.reject_reg_request.begin',
1020 'regRequestId: ' || p_reg_request_id);
1021 end if;
1022
1023 -- Call approve_reject_reg_request to update the record in
1024 -- UMX_REG_REQUESTS table.
1025 approve_reject_reg_request (p_reg_request_id => p_reg_request_id,
1026 p_status_code => 'REJECTED');
1027
1028 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1029 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1030 'fnd.plsql.UMXVRRSB.reject_reg_request.end', 'End');
1031 end if;
1032
1033 end reject_reg_request;
1034
1035 -- Function
1036 -- getNextApproverPvt
1037 --
1038 -- Description
1039 -- Private API that will call the ame_api2.GetNextApprover API
1040 -- IN
1041 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1042 -- itemkey - A string generated from the application object's primary key.
1043 -- OUT
1044 -- l_next_approver - result of the process based on which the next step is followed
1045 function getNextApproverPvt (p_ame_application_id in varchar2,
1046 p_ame_transaction_type_id in varchar2,
1047 p_reg_request_id in varchar2) return ame_util.approverRecord2 is
1048
1049 l_approval_complete varchar2 (1);
1050 l_next_approvers ame_util.approverstable2;
1051 l_next_approver ame_util.approverRecord2;
1052 i number := 1;
1053
1054 begin
1055
1056 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1057 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1058 'fnd.plsql.UMXVRRSB.getNextApproverPvt.begin',
1059 'p_ame_application_id=' || p_ame_application_id ||
1060 ' | p_ame_transaction_type_id=' || p_ame_transaction_type_id ||
1061 ' | p_reg_request_id=' || p_reg_request_id);
1062 end if;
1063
1064 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1065 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1066 'fnd.plsql.UMXVRRSB.getNextApproverPvt',
1067 'Before calling ame_api2.getNextApprovers4 (' ||
1068 p_ame_application_id || ',' ||
1069 p_ame_transaction_type_id || ',' ||
1070 p_reg_request_id || ',' ||
1071 ame_util.booleanFalse || ')');
1072 end if;
1073
1074 ame_api2.getNextApprovers4 (
1075 applicationIdIn => to_number (p_ame_application_id),
1076 transactionTypeIn => p_ame_transaction_type_id,
1077 transactionIdIn => p_reg_request_id,
1078 flagApproversAsNotifiedIn => ame_util.booleanFalse,
1079 approvalProcessCompleteYNOut => l_approval_complete,
1080 nextApproversOut => l_next_approvers);
1081
1082 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1083 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1084 'fnd.plsql.UMXVRRSB.getNextApproverPvt',
1085 'After calling ame_api2.getNextApprovers4 (' ||
1086 l_approval_complete || ')');
1087 end if;
1088
1089 if (l_next_approvers.count > 0) then
1090 loop
1091 if (l_next_approvers.exists(i)) then
1092 -- We are exiting because our Workflow Process will only support
1093 -- serial approval.
1094 l_next_approver := l_next_approvers(i);
1095
1096 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1097 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1098 'fnd.plsql.UMXVRRSB.getNextApproverPvt',
1099 'approver username:'|| l_next_approver.name);
1100 end if;
1101
1102 exit;
1103 end if;
1104 i := i + 1;
1105 if (i > l_next_approvers.count) then
1106 exit;
1107 end if;
1108 end loop;
1109 end if;
1110
1111 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1112 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1113 'fnd.plsql.UMXVRRSB.getNextApproverPvt.End',
1114 'l_next_approver=' || l_next_approver.name);
1115 end if;
1116
1117 return l_next_approver;
1118 end getNextApproverPvt;
1119
1120 Procedure get_current_approver_info (p_reg_request_id in varchar2,
1121 p_application_id in varchar2 default null,
1122 p_transaction_type_id in varchar2 default null,
1123 x_approver_name out nocopy varchar2,
1124 x_approver_email out nocopy varchar2) is
1125
1126 cursor get_req_request_info (p_reg_request_id in umx_reg_requests.reg_request_id%type) is
1127 select ame_application_id, ame_transaction_type_id
1128 from umx_reg_requests
1129 where reg_request_id = p_reg_request_id;
1130
1131 l_current_approver ame_util.approverRecord2;
1132 l_application_id UMX_REG_REQUESTS.ame_application_id%type;
1133 l_transaction_type_id UMX_REG_REQUESTS.ame_transaction_type_id%type;
1134
1135 l_role_info_tbl wf_directory.wf_local_roles_tbl_type;
1136
1137 Begin
1138
1139 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1140 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1141 'fnd.plsql.UMXVRRSB.get_current_approver_info.begin',
1142 'regRequestId: ' || p_reg_request_id ||
1143 ' | applicationId: ' || p_application_id ||
1144 ' | transactionTypeId: ' || p_transaction_type_id);
1145 end if;
1146
1147 l_application_id := p_application_id;
1148 l_transaction_type_id := p_transaction_type_id;
1149
1150 -- Try to get the required parameters if they are not being passed when calling this API.
1151 if (l_application_id is null or
1152 l_transaction_type_id is null) then
1153 -- If any of these required variable is NULL, then we will query from UMX_REG_REQUESTS table
1154 if (p_reg_request_id is not null) then
1155 -- OK, we can query and get the required info.
1156 open get_req_request_info (p_reg_request_id);
1157 fetch get_req_request_info into l_application_id, l_transaction_type_id;
1158 close get_req_request_info;
1159 else
1160 raise_application_error ('-200000', 'Required input parameters are missing. The API get_current_approver_info needs to be called with p_reg_request_id or combination of p_application_id and l_transaction_type_id.');
1161 end if;
1162 end if;
1163
1164 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1165 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1166 'fnd.plsql.UMXVRRSB.get_current_approver_info',
1167 'regRequestId: ' || p_reg_request_id ||
1168 ' | applicationId: ' || l_application_id ||
1169 ' | transactionTypeId: ' || l_transaction_type_id);
1170 end if;
1171
1172 if ((p_reg_request_id is not null) and
1173 (l_application_id is not null) and
1174 (l_transaction_type_id is not null)) then
1175 -- Get the current Approver name
1176 -- only if the application ID, Reg Request ID and Transaction Type ID is not null.
1177 begin
1178
1179 l_current_approver := getNextApproverPvt (l_application_id, l_transaction_type_id, p_reg_request_id);
1180
1181 exception
1182 when others
1183 -- Suppress it for now, we will log the error statement.
1184 then
1185 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) then
1186 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
1187 'fnd.plsql.UMXVRRSB.get_current_approver_info',
1188 'Exception occurs when calling ame_api.getNextApprover.');
1189 end if;
1190 end;
1191
1192 x_approver_name := l_current_approver.display_name;
1193
1194 wf_directory.GetRoleInfo2 (role => l_current_approver.name,
1195 role_info_tbl => l_role_info_tbl);
1196
1197 x_approver_email := l_role_info_tbl(1).email_address;
1198
1199 end if;
1200
1201 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1202 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1203 'fnd.plsql.UMXVRRSB.get_current_approver_info.end', 'End');
1204 end if;
1205
1206 End get_current_approver_info;
1207
1208 --
1209 -- Procedure : get_pend_acct_info
1210 -- Type : Private
1211 -- Pre_reqs : None
1212 -- Description : This API will return the current approver's username
1213 -- by calling ame_api.getNextApprover and email address.
1214 -- Active from and Active to from UMX's Workflow
1215 -- Input Parameters :
1216 -- @param p_requester_user_id
1217 -- Description : Requester user ID
1218 -- Required : Yes (If x_reg_request_id is provided, p_requester_user_id is not required)
1219 -- @param x_reg_request_id
1220 -- Description : Reg Request ID
1221 -- Required : Yes (If p_requester_user_id is provided, x_reg_request_id is not required)
1222 -- Output :
1223 -- x_reg_request_id
1224 -- Description : Reg Request ID
1225 -- x_requested_for_username
1226 -- Description : Requested for Username
1227 -- x_approver_name
1228 -- Description: Formated name of the current approver
1229 -- x_approver_email_address
1230 -- Description: Email address of the current approver
1231 -- x_status_code
1232 -- Description: Status code of the request
1233 -- x_active_from
1234 -- Description: The string version of the user account's start date.
1235 -- If the start date is before the sysdate, then it will
1236 -- return "Date of approval".
1237 -- x_active_to
1238 -- Description: The string version of the user account's end date .
1239 -- If the end date is null or x_active_to is "Date of approval",
1240 -- then it will return null.
1241 -- x_justification
1242 -- Description: Justification
1243 --
1244 Procedure get_pend_acct_info (
1245 p_requester_user_id in FND_USER.USER_ID%TYPE default null,
1246 x_reg_request_id in out NOCOPY UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
1247 x_requested_for_username out NOCOPY FND_USER.USER_NAME%TYPE,
1248 x_approver_name out NOCOPY varchar2,
1249 x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
1250 x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
1251 x_active_from out NOCOPY varchar2,
1252 x_active_to out NOCOPY varchar2,
1253 x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE) is
1254
1255 l_application_id AME_CALLING_APPS.FND_APPLICATION_ID%TYPE;
1256 l_transaction_type_id AME_CALLING_APPS.TRANSACTION_TYPE_ID%TYPE;
1257
1258 cursor get_reg_req_info_from_userid (p_user_id in FND_USER.USER_ID%TYPE) is
1259 select reg_request_id, status_code, ame_application_id,
1260 ame_transaction_type_id, requested_username, justification
1261 from umx_reg_requests
1262 where requested_for_user_id = p_user_id
1263 and requested_username is not null;
1264
1265 cursor get_reg_req_info_from_regid (p_reg_req_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE) is
1266 select status_code, ame_application_id, ame_transaction_type_id, justification
1267 from umx_reg_requests
1268 where reg_request_id = p_reg_req_id;
1269
1270 cursor getUserName (l_user_id in fnd_user.user_id%type) is
1271 select user_name
1272 from fnd_user
1273 where user_id = l_user_id;
1274
1275 begin
1276
1277 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1278 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1279 'fnd.plsql.UMXVRRSB.get_pend_acct_info.begin',
1280 'requesterUserId: ' || p_requester_user_id ||
1281 ' | regRequestId: ' || x_reg_request_id);
1282 end if;
1283
1284 -- The first thing we have to do is to get the pending information from the
1285 -- UMX_REG_REQUESTS table.
1286 if (p_requester_user_id is not null) then
1287
1288 -- Since the requester_user_id is not null, which means the request is a
1289 -- user account, query the reg req table base on the requester_user_id
1290 -- Get the regRequest ID, ame application id, ame transaction type id from
1291 -- the RegRequests table by the user id where the status is PENDING.
1292 open get_reg_req_info_from_userid (p_requester_user_id);
1293 fetch get_reg_req_info_from_userid into
1294 x_reg_request_id, x_status_code, l_application_id,
1295 l_transaction_type_id, x_requested_for_username, x_justification;
1296 if (get_reg_req_info_from_userid%notfound) then
1297 -- Bug 4312235: We have a pending user but we are missing a record in the
1298 -- reg request table. We will exit now.
1299 close get_reg_req_info_from_userid;
1300 open getUserName (p_requester_user_id);
1301 fetch getUserName into x_requested_for_username;
1302 close getUserName;
1303 x_status_code := 'PENDING';
1304 return;
1305 end if;
1306 close get_reg_req_info_from_userid;
1307
1308 -- Lowercase the username
1309 if (x_requested_for_username is not null) then
1310 x_requested_for_username := lower (x_requested_for_username);
1311 end if;
1312
1313 elsif (x_reg_request_id is not null) then
1314
1315 -- Get the ame application id, ame transaction type id from
1316 -- the RegRequests table by the regReqID where the status is PENDING.
1317 open get_reg_req_info_from_regid (x_reg_request_id);
1318 fetch get_reg_req_info_from_regid into
1319 x_status_code, l_application_id,
1320 l_transaction_type_id, x_justification;
1321 if (get_reg_req_info_from_regid%notfound) then
1322 close get_reg_req_info_from_regid;
1323 raise_application_error ('-20000', 'Cannot find AME info in the Req Request Table with req_request_id: ' || x_reg_request_id);
1324 end if;
1325 close get_reg_req_info_from_regid;
1326
1327 else
1328
1329 -- There is an error while calling this API:
1330 -- All required input parameters are null
1331 raise_application_error ('-20000', 'Both p_requester_user_id and x_reg_request_id is null while calling UMX_REG_REQUESTS_PVT.get_pend_acct_info API.');
1332
1333 end if;
1334
1335 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1336 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1337 'fnd.plsql.UMXVRRSB.get_pend_acct_info',
1338 'statusCode: ' || x_status_code);
1339 end if;
1340
1341 -- Get Current Approver name and email address
1342 if (x_status_code = 'PENDING') then
1343 -- Status code could be 'VERIFYING', in that case, don't get the next approver.
1344 UMX_REG_REQUESTS_PVT.get_current_approver_info (p_reg_request_id => x_reg_request_id,
1345 p_application_id => l_application_id,
1346 p_transaction_type_id => l_transaction_type_id,
1347 x_approver_name => x_approver_name,
1348 x_approver_email => x_approver_email_address);
1349 end if;
1350
1351 -- Get activeFrom and activeTo
1352 x_active_from := wf_engine.getitemattrtext (
1353 itemtype => UMX_REGISTRATION_UTIL.G_ITEM_TYPE,
1354 itemkey => x_reg_request_id,
1355 aname => 'REQUESTED_START_DATE');
1356
1357 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1358 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1359 'fnd.plsql.UMXVRRSB.get_pend_acct_info',
1360 'activeFrom: ' || x_active_from);
1361 end if;
1362
1363 if (x_active_from is null) or (fnd_date.canonical_to_date (x_active_from) <= sysdate) then
1364 -- active from is null, get fnd message for "Date of approval"
1365 fnd_message.set_name ('FND', 'UMX_USER_ACCT_ACTIVE_FROM_VAL');
1366 x_active_from := fnd_message.get;
1367 else
1368 x_active_from := fnd_date.date_to_displaydate ( dateval => fnd_date.canonical_to_date (x_active_from) , calendar_aware => fnd_date.calendar_aware );
1369 end if;
1370
1371 x_active_to := wf_engine.getitemattrtext (
1372 itemtype => UMX_REGISTRATION_UTIL.G_ITEM_TYPE,
1373 itemkey => x_reg_request_id,
1374 aname => 'REQUESTED_END_DATE');
1375
1376 if (x_active_to is not null) then
1377 x_active_to := fnd_date.date_to_displaydate ( dateval => fnd_date.canonical_to_date (x_active_to), calendar_aware => fnd_date.calendar_aware );
1378 end if;
1379
1380 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1381 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1382 'fnd.plsql.UMXVRRSB.get_pend_acct_info.end',
1383 'x_reg_request_id: ' || x_reg_request_id ||
1384 ' | x_requested_for_username: ' || x_requested_for_username ||
1385 ' | x_approver_name: ' || x_approver_name ||
1386 ' | x_approver_email_address: ' || x_approver_email_address ||
1387 ' | x_status_code: ' || x_status_code ||
1388 ' | x_active_from: ' || x_active_from ||
1389 ' | x_active_to: ' || x_active_to ||
1390 ' | x_justification: ' || x_justification);
1391 end if;
1392
1393 end get_pend_acct_info;
1394
1395 --
1396 -- Procedure : get_pend_acct_info_with_userid
1397 -- Type : Private
1398 -- Pre_reqs : None
1399 -- Description : This API will return the current approver's username
1400 -- by calling ame_api.getNextApprover and email address.
1401 -- Active from and Active to from UMX's Workflow
1402 -- Input Parameters :
1403 -- @param p_requester_user_id
1404 -- Description : Requester user ID
1405 -- Required : Yes
1406 -- Output :
1407 -- x_reg_request_id
1408 -- Description: Reg Request ID
1409 -- x_requested_for_username
1410 -- Description: Requested for Username
1411 -- x_approver_name
1412 -- Description: Formated name of the current approver
1413 -- x_approver_email_address
1414 -- Description: Email address of the current approver
1415 -- x_status_code
1416 -- Description: Status code of the request
1417 -- x_active_from
1418 -- Description: The string version of the user account's start date.
1419 -- If the start date is before the sysdate, then it will
1420 -- return "Date of approval".
1421 -- x_active_to
1422 -- Description: The string version of the user account's end date .
1423 -- If the end date is null or x_active_to is "Date of approval",
1424 -- then it will return null.
1425 -- x_justification
1426 -- Description: Justification
1427 --
1428 Procedure get_pend_acct_info_with_userid (
1429 p_requester_user_id in FND_USER.USER_ID%TYPE,
1430 x_reg_request_id out NOCOPY UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
1431 x_requested_for_username out NOCOPY FND_USER.USER_NAME%TYPE,
1432 x_approver_name out NOCOPY varchar2,
1433 x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
1434 x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
1435 x_active_from out NOCOPY varchar2,
1436 x_active_to out NOCOPY varchar2,
1437 x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE) is
1438
1439 l_reg_request_id UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE;
1440
1441 begin
1442
1443 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1444 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1445 'fnd.plsql.UMXVRRSB.get_pend_acct_info_with_userid.begin',
1446 'p_requester_user_id: ' || p_requester_user_id);
1447 end if;
1448
1449 UMX_REG_REQUESTS_PVT.get_pend_acct_info (
1450 p_requester_user_id => p_requester_user_id,
1451 x_reg_request_id => x_reg_request_id,
1452 x_requested_for_username => x_requested_for_username,
1453 x_approver_name => x_approver_name,
1454 x_approver_email_address => x_approver_email_address,
1455 x_status_code => x_status_code,
1456 x_active_from => x_active_from,
1457 x_active_to => x_active_to,
1458 x_justification => x_justification);
1459
1460 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1461 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1462 'fnd.plsql.UMXVRRSB.get_pend_acct_info_with_userid.end',
1463 'x_reg_request_id: ' || x_reg_request_id ||
1464 ' | x_requested_for_username: ' || x_requested_for_username ||
1465 ' | x_approver_name: ' || x_approver_name ||
1466 ' | x_approver_email_address: ' || x_approver_email_address ||
1467 ' | x_status_code: ' || x_status_code ||
1468 ' | x_active_from: ' || x_active_from ||
1469 ' | x_active_to: ' || x_active_to ||
1470 ' | x_justification: ' || x_justification);
1471 end if;
1472
1473 end get_pend_acct_info_with_userid;
1474
1475 --
1476 -- Procedure : get_pend_acct_info_with_reqid
1477 -- Type : Private
1478 -- Pre_reqs : None
1479 -- Description : This API will return the current approver's username
1480 -- by calling ame_api.getNextApprover and email address.
1481 -- Input Parameters :
1482 -- @param x_reg_request_id
1483 -- Description : Reg Request ID
1484 -- Required : Yes
1485 -- Output :
1486 -- x_approver_name
1487 -- Description: Formated name of the current approver
1488 -- x_approver_email_address
1489 -- Description: Email address of the current approver
1490 -- x_status_code
1491 -- Description: Status code of the request
1492 -- x_active_from
1493 -- Description: The string version of the user account's start date.
1494 -- If the start date is before the sysdate, then it will
1495 -- return "Date of approval".
1496 -- x_active_to
1497 -- Description: The string version of the user account's end date .
1498 -- If the end date is null or x_active_to is "Date of approval",
1499 -- then it will return null.
1500 -- x_justification
1501 -- Description: Justification
1502 --
1503 Procedure get_pend_acct_info_with_reqid (
1504 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
1505 x_approver_name out NOCOPY varchar2,
1506 x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
1507 x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
1508 x_active_from out NOCOPY varchar2,
1509 x_active_to out NOCOPY varchar2,
1510 x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE) is
1511
1512 l_reg_request_id UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE;
1513 l_requested_for_username FND_USER.USER_NAME%TYPE;
1514
1515 begin
1516
1517 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1518 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1519 'fnd.plsql.UMXVRRSB.get_pend_acct_info_with_reqid.begin',
1520 'p_reg_request_id: ' || p_reg_request_id);
1521 end if;
1522
1523 l_reg_request_id := p_reg_request_id;
1524
1525 UMX_REG_REQUESTS_PVT.get_pend_acct_info (
1526 x_reg_request_id => l_reg_request_id,
1527 x_requested_for_username => l_requested_for_username,
1528 x_approver_name => x_approver_name,
1529 x_approver_email_address => x_approver_email_address,
1530 x_status_code => x_status_code,
1531 x_active_from => x_active_from,
1532 x_active_to => x_active_to,
1533 x_justification => x_justification);
1534
1535 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1536 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1537 'fnd.plsql.UMXVRRSB.get_pend_acct_info_with_reqid.end',
1538 'x_approver_name:' || x_approver_name ||
1539 ' | x_approver_email_address: ' || x_approver_email_address ||
1540 ' | x_status_code: ' || x_status_code ||
1541 ' | x_active_from: ' || x_active_from ||
1542 ' | x_active_to: ' || x_active_to ||
1543 ' | x_justification: ' || x_justification);
1544 end if;
1545
1546 end get_pend_acct_info_with_reqid;
1547
1548 --
1549 -- Procedure : get_error_wf_info
1550 -- Type : Private
1551 -- Pre_reqs : None
1552 -- Description : This API will call wf_engine.iteminfo to get the status
1553 -- of the main UMX Workflow and all its event subscribers.
1554 -- Input Parameters (Mandatory):
1555 -- p_reg_request_id: Registration Request ID
1556 --
1557 -- Output Parameters:
1558 -- x_itemtype: Workflow's Item Type
1559 -- x_itemkey: Workflow's Item Key
1560 -- x_status: Workflow's Status
1561 -- x_result: Result
1562 -- x_actid: Activity ID
1563 -- x_errname: Error Name
1564 -- x_errmsg: Error Message
1565 -- x_errstack: Error Stack
1566 --
1567 --
1568 procedure get_error_wf_info (p_reg_request_id in wf_items.item_type%type,
1569 x_itemtype out nocopy wf_items.item_type%type,
1570 x_itemkey out nocopy wf_items.item_key%type,
1571 x_status out nocopy varchar2,
1572 x_result out nocopy varchar2,
1573 x_actid out nocopy number,
1574 x_errname out nocopy varchar2,
1575 x_errmsg out nocopy varchar2,
1576 x_errstack out nocopy varchar2) IS
1577
1578 cursor get_child_workflow is
1579 select * from wf_items
1580 where parent_item_type = umx_registration_util.g_item_type
1581 and parent_item_key = p_reg_request_id;
1582
1583 child get_child_workflow%rowtype;
1584
1585 BEGIN
1586
1587 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1588 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1589 'fnd.plsql.UMXVRRSB.get_error_wf_info.begin',
1590 'p_reg_request_id: ' || p_reg_request_id);
1591 end if;
1592
1593 -- Find the status of the main workflow first
1594 x_itemtype := umx_registration_util.g_item_type;
1595 x_itemkey := p_reg_request_id;
1596 wf_engine.iteminfo (itemtype => x_itemtype,
1597 itemkey => x_itemkey,
1598 status => x_status,
1599 result => x_result,
1600 actid => x_actid,
1601 errname => x_errname,
1602 errmsg => x_errmsg,
1603 errstack => x_errstack);
1604
1605 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1606 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1607 'fnd.plsql.UMXVRRSB.get_error_wf_info',
1608 'x_status: ' || x_status);
1609 end if;
1610
1611 if not (x_status = 'ERROR') then
1612 for child in get_child_workflow
1613 loop
1614 x_itemtype := child.item_type;
1615 x_itemkey := child.item_key;
1616 wf_engine.iteminfo (itemtype => x_itemtype,
1617 itemkey => x_itemkey,
1618 status => x_status,
1619 result => x_result,
1620 actid => x_actid,
1621 errname => x_errname,
1622 errmsg => x_errmsg,
1623 errstack => x_errstack);
1624 exit when x_status = 'ERROR';
1625 end loop;
1626 end if;
1627
1628 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1629 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1630 'fnd.plsql.UMXVRRSB.get_error_wf_info.end',
1631 'x_itemtype: ' || x_itemtype ||
1632 ' | x_itemkey: ' || x_itemkey ||
1633 ' | x_status: ' || x_status ||
1634 ' | x_result: ' || x_result ||
1635 ' | x_actid: ' || x_actid ||
1636 ' | x_errname: ' || x_errname ||
1637 ' | x_errmsg: ' || x_errmsg ||
1638 ' | x_errstack: ' || x_errstack);
1639 end if;
1640
1641 END get_error_wf_info;
1642
1643 --
1644 -- Procedure : get_error_wf_info
1645 -- Type : Private
1646 -- Pre_reqs : None
1647 -- Description : This API will call wf_engine.iteminfo to get the status
1648 -- of the main UMX Workflow and all its event subscribers.
1649 -- Input Parameters (Mandatory):
1650 -- p_user_id: User ID of the requester
1651 --
1652 -- Output Parameters:
1653 -- x_itemtype: Workflow's Item Type
1654 -- x_itemkey: Workflow's Item Key
1655 -- x_status: Workflow's Status
1656 -- x_result: Result
1657 -- x_actid: Activity ID
1658 -- x_errname: Error Name
1659 -- x_errmsg: Error Message
1660 -- x_errstack: Error Stack
1661 --
1662 --
1663 procedure get_error_wf_info (p_user_id in fnd_user.user_id%type,
1664 x_itemtype out nocopy wf_items.item_type%type,
1665 x_itemkey out nocopy wf_items.item_key%type,
1666 x_status out nocopy varchar2,
1667 x_result out nocopy varchar2,
1668 x_actid out nocopy number,
1669 x_errname out nocopy varchar2,
1670 x_errmsg out nocopy varchar2,
1671 x_errstack out nocopy varchar2) IS
1672
1673 cursor get_reg_req_id_with_user_id (l_user_id in fnd_user.user_id%type) is
1674 select reg_request_id
1675 from umx_reg_requests
1676 where requested_for_user_id = l_user_id;
1677
1678 l_reg_req_id umx_reg_requests.reg_request_id%type;
1679
1680 BEGIN
1681
1682 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1683 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1684 'fnd.plsql.UMXVRRSB.get_error_wf_info.begin',
1685 'p_user_id: ' || p_user_id);
1686 end if;
1687
1688 -- Get the reg request id from user id
1689 open get_reg_req_id_with_user_id (p_user_id);
1690 fetch get_reg_req_id_with_user_id into l_reg_req_id;
1691 if (get_reg_req_id_with_user_id%notfound) then
1692 -- There is a problem here. A pending user but with no record in the
1693 -- Reg Table.
1694 close get_reg_req_id_with_user_id;
1695 x_status := 'PENDING';
1696 else
1697 close get_reg_req_id_with_user_id;
1698
1699 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1700 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1701 'fnd.plsql.UMXVRRSB.get_error_wf_info',
1702 'l_reg_req_id: ' || l_reg_req_id);
1703 end if;
1704
1705 get_error_wf_info (p_reg_request_id => l_reg_req_id,
1706 x_itemtype => x_itemtype,
1707 x_itemkey => x_itemkey,
1708 x_status => x_status,
1709 x_result => x_result,
1710 x_actid => x_actid,
1711 x_errname => x_errname,
1712 x_errmsg => x_errmsg,
1713 x_errstack => x_errstack);
1714 end if;
1715
1716 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1717 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1718 'fnd.plsql.UMXVRRSB.get_error_wf_info.end',
1719 'x_itemtype: ' || x_itemtype ||
1720 ' | x_itemkey: ' || x_itemkey ||
1721 ' | x_status: ' || x_status ||
1722 ' | x_result: ' || x_result ||
1723 ' | x_actid: ' || x_actid ||
1724 ' | x_errname: ' || x_errname ||
1725 ' | x_errmsg: ' || x_errmsg ||
1726 ' | x_errstack: ' || x_errstack);
1727 end if;
1728
1729 END get_error_wf_info;
1730
1731 --
1732 -- Function : is_pend_request_error
1733 -- Type : Private
1734 -- Pre_reqs : None
1735 -- Description : This API will call wf_engine.iteminfo to get the status
1736 -- of the main UMX Workflow and all its event subscribers.
1737 -- It will return 'Y' if account is in error stage and 'N' if otherwise.
1738 -- Input Parameters (Mandatory):
1739 -- p_reg_request_id: Registration Request ID
1740 --
1741 -- Output Parameter:
1742 -- It will return 'Y' if pending account has error and 'N' if otherwise.
1743 --
1744 function is_pend_request_error (p_reg_request_id in umx_reg_requests.reg_request_id%type) return varchar2 is
1745
1746 l_itemtype wf_items.item_type%type;
1747 l_itemkey wf_items.item_key%type;
1748 l_status varchar2(8);
1749 l_result varchar2(30);
1750 l_actid number;
1751 l_errname varchar2(30);
1752 l_errmsg varchar2(2000);
1753 l_errstack varchar2(4000);
1754
1755 BEGIN
1756
1757 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1758 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1759 'fnd.plsql.UMXVRRSB.is_pend_request_error.begin',
1760 'p_reg_request_id: ' || p_reg_request_id);
1761 end if;
1762
1763 get_error_wf_info (p_reg_request_id => p_reg_request_id,
1764 x_itemtype => l_itemtype,
1765 x_itemkey => l_itemkey,
1766 x_status => l_status,
1767 x_result => l_result,
1768 x_actid => l_actid,
1769 x_errname => l_errname,
1770 x_errmsg => l_errmsg,
1771 x_errstack => l_errstack);
1772
1773 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1774 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1775 'fnd.plsql.UMXVRRSB.is_pend_request_error.end',
1776 'l_status: ' || l_status);
1777 end if;
1778
1779 if (l_status = 'ERROR') then
1780 return ('Y');
1781 else
1782 return ('N');
1783 end if;
1784
1785 END is_pend_request_error;
1786
1787 --
1788 -- Function : is_pend_account_error
1789 -- Type : Private
1790 -- Pre_reqs : None
1791 -- Description : This API will call wf_engine.iteminfo to get the status
1792 -- of the main UMX Workflow and all its event subscribers.
1793 -- It will return 'Y' if account is in error stage and 'N' if otherwise.
1794 -- Input Parameters (Mandatory):
1795 -- p_user_id: User ID of the requester
1796 --
1797 -- Output Parameter:
1798 -- It will return 'Y' if pending account has error and 'N' if otherwise.
1799 --
1800 function is_pend_account_error (p_user_id in fnd_user.user_id%type) return varchar2 is
1801
1802 l_itemtype wf_items.item_type%type;
1803 l_itemkey wf_items.item_key%type;
1804 l_status varchar2(8);
1805 l_result varchar2(30);
1806 l_actid number;
1807 l_errname varchar2(30);
1808 l_errmsg varchar2(2000);
1809 l_errstack varchar2(4000);
1810
1811 BEGIN
1812
1813 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1814 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1815 'fnd.plsql.UMXVRRSB.is_pend_account_error.begin',
1816 'p_user_id: ' || p_user_id);
1817 end if;
1818
1819 get_error_wf_info (p_user_id => p_user_id,
1820 x_itemtype => l_itemtype,
1821 x_itemkey => l_itemkey,
1822 x_status => l_status,
1823 x_result => l_result,
1824 x_actid => l_actid,
1825 x_errname => l_errname,
1826 x_errmsg => l_errmsg,
1827 x_errstack => l_errstack);
1828
1829 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1830 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1831 'fnd.plsql.UMXVRRSB.is_pend_account_error.end',
1832 'l_status: ' || l_status);
1833 end if;
1834
1835 if (l_status = 'ERROR') then
1836 return ('Y');
1837 else
1838 return ('N');
1839 end if;
1840
1841 END is_pend_account_error;
1842
1843 END UMX_REG_REQUESTS_PVT;
[Home] [Help]