1 PACKAGE BODY UMX_REG_REQUESTS_PVT AS
2 /* $Header: UMXVRRSB.pls 120.6.12010000.2 2008/12/03 13:01:55 jstyles 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
533 begin
534
535 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
536 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
537 'fnd.plsql.UMXVRRSB.approve_username_reg_request.begin',
538 'regRequestId: ' || p_reg_request_id ||
539 ' | username: ' || p_username ||
540 ' | personPartyId: ' || p_person_party_id ||
541 ' | startDate: ' || p_start_date ||
542 ' | endDate: ' || p_end_date);
543 end if;
544
545 -- Call FND's activate_user_account API to activate the user account.
546 fnd_user_pkg.EnableUser (
547 username => p_username,
548 start_date => nvl (p_start_date, sysdate),
549 end_date => nvl (p_end_date, fnd_user_pkg.null_date));
550
551 -- Need to update the Person Party's ID in the FND_USER table
552 fnd_user_pkg.UpdateUserParty (
553 x_user_name => p_username,
554 x_owner => NULL,
555 x_person_party_id => p_person_party_id);
556
557 -- Update the record in the Reg Requests table with status and party id
558 UMX_REG_REQUESTS_PKG.update_row (
559 X_REG_REQUEST_ID => p_reg_request_id,
560 X_STATUS_CODE => 'APPROVED',
561 X_REQUESTED_FOR_PARTY_ID => p_person_party_id);
562
563 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
564 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
565 'fnd.plsql.UMXVRRSB.approve_username_reg_request.end', 'End');
566 end if;
567
568 end approve_username_reg_request;
569
570 --
571 -- Procedure : reject_cancel_username_reg_req
572 -- Type : Private
573 -- Pre_reqs : None
574 -- Description : This API will ...
575 -- 1) Release the username by calling
576 -- fnd_user_pkg.release_username API.
577 -- 2) Set the status code to "REJECT" or "CANCELLED" in
578 -- UMX_REG_REQUESTS table.
579 -- 3) Remove the REQUESTED_FOR_USER_ID
580 -- 4) If the REQUESTED_BY_USER_ID is the same as the
581 -- REQUESTED_FOR_USER_ID, remove the
582 -- REQUESTED_BY_USER_ID
583 --
584 -- Input Parameters :
585 -- @param p_reg_request_id
586 -- Description : ID for the registration request
587 -- Required : Yes
588 -- @param p_username
589 -- Description : Username of the account
590 -- Required : Yes
591 -- @param p_user_id
592 -- Description : User ID of the account
593 -- Required : Yes
594 -- @param p_status_code
595 -- Description : Status code of the reg request
596 -- Required : Yes
597 -- Output :
598 -- None
599 -- Description :
600 --
601 Procedure reject_cancel_username_reg_req (
602 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
603 p_username in FND_USER.USER_NAME%TYPE,
604 p_user_id in FND_USER.USER_ID%TYPE,
605 p_status_code in UMX_REG_REQUESTS.STATUS_CODE%TYPE) is
606
607 l_requested_by_user_id umx_reg_requests.requested_by_user_id%type;
608
609 cursor getRequestedByUserID (p_reg_request_id in UMX_reg_requests.reg_request_id%type) is
610 select requested_by_user_id
611 from umx_reg_requests
612 where reg_request_id = p_reg_request_id;
613
614 begin
615
616 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
617 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
618 'fnd.plsql.UMXVRRSB.reject_cancel_username_reg_req.begin',
619 'regRequestId: ' || p_reg_request_id ||
620 ' | username: ' || p_username ||
621 ' | userId: ' || p_user_id ||
622 ' | statusCode: ' || p_status_code);
623 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
624 'fnd.plsql.UMXVRRSB.reject_cancel_username_reg_req.begin',
625 'Before calling fnd_user_pkg.RemovePendingUser');
626 end if;
627
628 -- Call FND's release_username API to release/delete the username
629 fnd_user_pkg.RemovePendingUser (username => p_username);
630
631 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
632 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
633 'fnd.plsql.UMXVRRSB.reject_cancel_username_reg_req.begin',
634 'After calling fnd_user_pkg.RemovePendingUser');
635 end if;
636
637 -- Query the requested_by_user_id from UMX_reg_requests table
638 -- If the requested_by_user_id is equal to the requested_for_user_id,
639 -- make requested_by_user_id null to avoid dangling foreign key.
640 open getRequestedByUserID (p_reg_request_id);
641 fetch getRequestedByUserID into l_requested_by_user_id;
642 if (getRequestedByUserID%notfound) then
643 -- cannot find the record
644 close getRequestedByUserID;
645 raise_application_error ('-20000', '<<is this a correct p_reg_request_id?>>');
646 end if;
647
648 if (p_user_id = l_requested_by_user_id) then
649 -- User requested his own account, we need to make the
650 -- requested_by_user_id to null.
651 l_requested_by_user_id := fnd_api.g_miss_num;
652 end if;
653
654 UMX_REG_REQUESTS_PKG.update_row (
655 X_REG_REQUEST_ID => p_reg_request_id,
656 X_STATUS_CODE => p_status_code,
657 X_REQUESTED_FOR_USER_ID => fnd_api.g_miss_num,
658 X_REQUESTED_BY_USER_ID => l_requested_by_user_id);
659
660 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
661 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
662 'fnd.plsql.UMXVRRSB.reject_cancel_username_reg_req.end', 'End');
663 end if;
664
665 end reject_cancel_username_reg_req;
666
667 --
668 -- Procedure : reject_username_reg_request
669 -- Type : Private
670 -- Pre_reqs : None
671 -- Description : This API will call reject_cancel_username_reg_req
672 -- with status code = "REJECTED".
673 --
674 -- This API should be called from Self-Service
675 -- Registration or Admin Creation.
676 -- Input Parameters :
677 -- @param p_reg_request_id
678 -- Description : ID for the registration request
679 -- Required : Yes
680 -- @param p_user_id
681 -- Description : User ID of the user account
682 -- Required : Yes
683 -- @param p_username
684 -- Description : Username of the account
685 -- Required : Yes
686 -- Output :
687 -- None
688 -- Description :
689 --
690 Procedure reject_username_reg_request (
691 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
692 p_user_id in FND_USER.USER_ID%TYPE,
693 p_username in FND_USER.USER_NAME%TYPE) is
694
695 begin
696
697 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
698 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
699 'fnd.plsql.UMXVRRSB.reject_username_reg_request.begin',
700 'regRequestId: ' || p_reg_request_id ||
701 ' | userId: ' || p_user_id ||
702 ' | username: ' || p_username);
703 end if;
704
705 -- Call reject_cancel_username_reg_req with status code = 'REJECTED'
706 reject_cancel_username_reg_req (
707 p_reg_request_id => p_reg_request_id,
708 p_username => p_username,
709 p_user_id => p_user_id,
710 p_status_code => 'REJECTED');
711
712 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
713 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
714 'fnd.plsql.UMXVRRSB.reject_username_reg_request.end', 'End');
715 end if;
716
717 end reject_username_reg_request;
718
719 --
720 -- Procedure : cancel_username_reg_request
721 -- Type : Private
722 -- Pre_reqs : None
723 -- Description : This API will call reject_cancel_username_reg_req API
724 -- with status_code = 'CANCELLED'.
725 --
726 -- This API should be called from Self-Service Registration or
727 -- Admin Creation.
728 -- Input Parameters :
729 -- @param p_reg_request_id
730 -- Description : ID for the registration request
731 -- Required : Yes
732 -- @param p_user_id
733 -- Description : ID of the user account
734 -- Required : Yes
735 -- @param p_username
736 -- Description : Username of the account
737 -- Required : Yes
738 -- Output :
739 -- None
740 -- Description :
741 --
742 Procedure cancel_username_reg_request (
743 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
744 p_user_id in FND_USER.USER_ID%TYPE,
745 p_username in FND_USER.USER_NAME%TYPE) is
746
747 begin
748
749 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
750 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
751 'fnd.plsql.UMXVRRSB.cancel_username_reg_request.begin',
752 'regRequestId: ' || p_reg_request_id ||
753 ' | userId: ' || p_user_id ||
754 ' | username: ' || p_username);
755 end if;
756
757 -- Call reject_cancel_username_reg_req with status code = 'CANCELLED'
758 reject_cancel_username_reg_req (
759 p_reg_request_id => p_reg_request_id,
760 p_username => p_username,
761 p_user_id => p_user_id,
762 p_status_code => 'CANCELLED');
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.cancel_username_reg_request.end', 'End');
767 end if;
768
769 end cancel_username_reg_request;
770
771 --
772 -- Procedure : approve_reject_reg_request
773 -- Type : Private
774 -- Pre_reqs : None
775 -- Description : This API will set the status code of a record in
776 -- UMX_REG_REQUESTS table.
777 --
778 -- Input Parameters :
779 -- @param p_reg_request_id
780 -- Description : ID for the registration request
781 -- Required : Yes
782 -- @param p_status_code
783 -- Description : Status code of the record in UMX_REG_REQUESTS table
784 -- Required : Yes
785 -- Output :
786 -- None
787 -- Description :
788 --
789 Procedure approve_reject_reg_request (
790 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
791 p_status_code in UMX_REG_REQUESTS.STATUS_CODE%type) is
792
793 begin
794
795 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
796 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
797 'fnd.plsql.UMXVRRSB.approve_reject_reg_request.begin',
798 'regRequestId: ' || p_reg_request_id ||
799 ' | statusCode: ' || p_status_code);
800 end if;
801
802 -- update the record in the Reg Requests table with status to status_code
803 UMX_REG_REQUESTS_PKG.update_row (X_REG_REQUEST_ID => p_reg_request_id,
804 X_STATUS_CODE => p_status_code);
805
806 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
807 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
808 'fnd.plsql.UMXVRRSB.approve_reject_reg_request.end', 'End');
809 end if;
810
811 end approve_reject_reg_request;
812
813 --
814 -- Procedure : approve_reg_request
815 -- Type : Private
816 -- Pre_reqs : None
817 -- Description : This API will approve_reject_reg_request to approve
818 -- the Reg Request in UMX_REG_REQUESTS table.
819 --
820 -- This API should be called from ART or SMART.
821 -- Input Parameters :
822 -- @param p_reg_request_id
823 -- Description : ID for the registration request
824 -- Required : Yes
825 -- Output :
826 -- None
827 -- Description :
828 --
829 Procedure approve_reg_request (p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE) is
830 begin
831
832 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
833 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
834 'fnd.plsql.UMXVRRSB.approve_reg_request.begin',
835 'regRequestId: ' || p_reg_request_id);
836 end if;
837
838 -- Call approve_reject_reg_request to update the record in
839 -- UMX_REG_REQUESTS table.
840 approve_reject_reg_request (p_reg_request_id => p_reg_request_id,
841 p_status_code => 'APPROVED');
842
843 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
844 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
845 'fnd.plsql.UMXVRRSB.approve_reg_request.end', 'End');
846 end if;
847
848 end approve_reg_request;
849
850 --
851 -- Procedure : reject_reg_request
852 -- Type : Private
853 -- Pre_reqs : None
854 -- Description : This API will approve_reject_reg_request to reject
855 -- the Reg Request in UMX_REG_REQUESTS table.
856 --
857 -- This API should be called from ART or SMART.
858 -- Input Parameters :
859 -- @param p_reg_request_id
860 -- Description : ID for the registration request
861 -- Required : Yes
862 -- Output :
863 -- None
864 -- Description :
865 --
866 Procedure reject_reg_request (p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE) is
867 begin
868
869 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
870 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
871 'fnd.plsql.UMXVRRSB.reject_reg_request.begin',
872 'regRequestId: ' || p_reg_request_id);
873 end if;
874
875 -- Call approve_reject_reg_request to update the record in
876 -- UMX_REG_REQUESTS table.
877 approve_reject_reg_request (p_reg_request_id => p_reg_request_id,
878 p_status_code => 'REJECTED');
879
880 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
881 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
882 'fnd.plsql.UMXVRRSB.reject_reg_request.end', 'End');
883 end if;
884
885 end reject_reg_request;
886
887 -- Function
888 -- getNextApproverPvt
889 --
890 -- Description
891 -- Private API that will call the ame_api2.GetNextApprover API
892 -- IN
893 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
894 -- itemkey - A string generated from the application object's primary key.
895 -- OUT
896 -- l_next_approver - result of the process based on which the next step is followed
897 function getNextApproverPvt (p_ame_application_id in varchar2,
898 p_ame_transaction_type_id in varchar2,
899 p_reg_request_id in varchar2) return ame_util.approverRecord2 is
900
901 l_approval_complete varchar2 (1);
902 l_next_approvers ame_util.approverstable2;
903 l_next_approver ame_util.approverRecord2;
904 i number := 1;
905
906 begin
907
908 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
909 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
910 'fnd.plsql.UMXVRRSB.getNextApproverPvt.begin',
911 'p_ame_application_id=' || p_ame_application_id ||
912 ' | p_ame_transaction_type_id=' || p_ame_transaction_type_id ||
913 ' | p_reg_request_id=' || p_reg_request_id);
914 end if;
915
916 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
917 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
918 'fnd.plsql.UMXVRRSB.getNextApproverPvt',
919 'Before calling ame_api2.getNextApprovers4 (' ||
920 p_ame_application_id || ',' ||
921 p_ame_transaction_type_id || ',' ||
922 p_reg_request_id || ',' ||
923 ame_util.booleanFalse || ')');
924 end if;
925
926 ame_api2.getNextApprovers4 (
927 applicationIdIn => to_number (p_ame_application_id),
928 transactionTypeIn => p_ame_transaction_type_id,
929 transactionIdIn => p_reg_request_id,
930 flagApproversAsNotifiedIn => ame_util.booleanFalse,
931 approvalProcessCompleteYNOut => l_approval_complete,
932 nextApproversOut => l_next_approvers);
933
934 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
935 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
936 'fnd.plsql.UMXVRRSB.getNextApproverPvt',
937 'After calling ame_api2.getNextApprovers4 (' ||
938 l_approval_complete || ')');
939 end if;
940
941 if (l_next_approvers.count > 0) then
942 loop
943 if (l_next_approvers.exists(i)) then
944 -- We are exiting because our Workflow Process will only support
945 -- serial approval.
946 l_next_approver := l_next_approvers(i);
947
948 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
949 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
950 'fnd.plsql.UMXVRRSB.getNextApproverPvt',
951 'approver username:'|| l_next_approver.name);
952 end if;
953
954 exit;
955 end if;
956 i := i + 1;
957 if (i > l_next_approvers.count) then
958 exit;
959 end if;
960 end loop;
961 end if;
962
963 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
964 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
965 'fnd.plsql.UMXVRRSB.getNextApproverPvt.End',
966 'l_next_approver=' || l_next_approver.name);
967 end if;
968
969 return l_next_approver;
970 end getNextApproverPvt;
971
972 Procedure get_current_approver_info (p_reg_request_id in varchar2,
973 p_application_id in varchar2 default null,
974 p_transaction_type_id in varchar2 default null,
975 x_approver_name out nocopy varchar2,
976 x_approver_email out nocopy varchar2) is
977
978 cursor get_req_request_info (p_reg_request_id in umx_reg_requests.reg_request_id%type) is
979 select ame_application_id, ame_transaction_type_id
980 from umx_reg_requests
981 where reg_request_id = p_reg_request_id;
982
983 l_current_approver ame_util.approverRecord2;
984 l_application_id UMX_REG_REQUESTS.ame_application_id%type;
985 l_transaction_type_id UMX_REG_REQUESTS.ame_transaction_type_id%type;
986
987 l_role_info_tbl wf_directory.wf_local_roles_tbl_type;
988
989 Begin
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.get_current_approver_info.begin',
994 'regRequestId: ' || p_reg_request_id ||
995 ' | applicationId: ' || p_application_id ||
996 ' | transactionTypeId: ' || p_transaction_type_id);
997 end if;
998
999 l_application_id := p_application_id;
1000 l_transaction_type_id := p_transaction_type_id;
1001
1002 -- Try to get the required parameters if they are not being passed when calling this API.
1003 if (l_application_id is null or
1004 l_transaction_type_id is null) then
1005 -- If any of these required variable is NULL, then we will query from UMX_REG_REQUESTS table
1006 if (p_reg_request_id is not null) then
1007 -- OK, we can query and get the required info.
1008 open get_req_request_info (p_reg_request_id);
1009 fetch get_req_request_info into l_application_id, l_transaction_type_id;
1010 close get_req_request_info;
1011 else
1012 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.');
1013 end if;
1014 end if;
1015
1016 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1017 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1018 'fnd.plsql.UMXVRRSB.get_current_approver_info',
1019 'regRequestId: ' || p_reg_request_id ||
1020 ' | applicationId: ' || l_application_id ||
1021 ' | transactionTypeId: ' || l_transaction_type_id);
1022 end if;
1023
1024 if ((p_reg_request_id is not null) and
1025 (l_application_id is not null) and
1026 (l_transaction_type_id is not null)) then
1027 -- Get the current Approver name
1028 -- only if the application ID, Reg Request ID and Transaction Type ID is not null.
1029 begin
1030
1031 l_current_approver := getNextApproverPvt (l_application_id, l_transaction_type_id, p_reg_request_id);
1032
1033 exception
1034 when others
1035 -- Suppress it for now, we will log the error statement.
1036 then
1037 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) then
1038 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
1039 'fnd.plsql.UMXVRRSB.get_current_approver_info',
1040 'Exception occurs when calling ame_api.getNextApprover.');
1041 end if;
1042 end;
1043
1044 x_approver_name := l_current_approver.display_name;
1045
1046 wf_directory.GetRoleInfo2 (role => l_current_approver.name,
1047 role_info_tbl => l_role_info_tbl);
1048
1049 x_approver_email := l_role_info_tbl(1).email_address;
1050
1051 end if;
1052
1053 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1054 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1055 'fnd.plsql.UMXVRRSB.get_current_approver_info.end', 'End');
1056 end if;
1057
1058 End get_current_approver_info;
1059
1060 --
1061 -- Procedure : get_pend_acct_info
1062 -- Type : Private
1063 -- Pre_reqs : None
1064 -- Description : This API will return the current approver's username
1065 -- by calling ame_api.getNextApprover and email address.
1066 -- Active from and Active to from UMX's Workflow
1067 -- Input Parameters :
1068 -- @param p_requester_user_id
1069 -- Description : Requester user ID
1070 -- Required : Yes (If x_reg_request_id is provided, p_requester_user_id is not required)
1071 -- @param x_reg_request_id
1072 -- Description : Reg Request ID
1073 -- Required : Yes (If p_requester_user_id is provided, x_reg_request_id is not required)
1074 -- Output :
1075 -- x_reg_request_id
1076 -- Description : Reg Request ID
1077 -- x_requested_for_username
1078 -- Description : Requested for Username
1079 -- x_approver_name
1080 -- Description: Formated name of the current approver
1081 -- x_approver_email_address
1082 -- Description: Email address of the current approver
1083 -- x_status_code
1084 -- Description: Status code of the request
1085 -- x_active_from
1086 -- Description: The string version of the user account's start date.
1087 -- If the start date is before the sysdate, then it will
1088 -- return "Date of approval".
1089 -- x_active_to
1090 -- Description: The string version of the user account's end date .
1091 -- If the end date is null or x_active_to is "Date of approval",
1092 -- then it will return null.
1093 -- x_justification
1094 -- Description: Justification
1095 --
1096 Procedure get_pend_acct_info (
1097 p_requester_user_id in FND_USER.USER_ID%TYPE default null,
1098 x_reg_request_id in out NOCOPY UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
1099 x_requested_for_username out NOCOPY FND_USER.USER_NAME%TYPE,
1100 x_approver_name out NOCOPY varchar2,
1101 x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
1102 x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
1103 x_active_from out NOCOPY varchar2,
1104 x_active_to out NOCOPY varchar2,
1105 x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE) is
1106
1107 l_application_id AME_CALLING_APPS.FND_APPLICATION_ID%TYPE;
1108 l_transaction_type_id AME_CALLING_APPS.TRANSACTION_TYPE_ID%TYPE;
1109
1110 cursor get_reg_req_info_from_userid (p_user_id in FND_USER.USER_ID%TYPE) is
1111 select reg_request_id, status_code, ame_application_id,
1112 ame_transaction_type_id, requested_username, justification
1113 from umx_reg_requests
1114 where requested_for_user_id = p_user_id
1115 and requested_username is not null;
1116
1117 cursor get_reg_req_info_from_regid (p_reg_req_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE) is
1118 select status_code, ame_application_id, ame_transaction_type_id, justification
1119 from umx_reg_requests
1120 where reg_request_id = p_reg_req_id;
1121
1122 cursor getUserName (l_user_id in fnd_user.user_id%type) is
1123 select user_name
1124 from fnd_user
1125 where user_id = l_user_id;
1126
1127 begin
1128
1129 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1130 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1131 'fnd.plsql.UMXVRRSB.get_pend_acct_info.begin',
1132 'requesterUserId: ' || p_requester_user_id ||
1133 ' | regRequestId: ' || x_reg_request_id);
1134 end if;
1135
1136 -- The first thing we have to do is to get the pending information from the
1137 -- UMX_REG_REQUESTS table.
1138 if (p_requester_user_id is not null) then
1139
1140 -- Since the requester_user_id is not null, which means the request is a
1141 -- user account, query the reg req table base on the requester_user_id
1142 -- Get the regRequest ID, ame application id, ame transaction type id from
1143 -- the RegRequests table by the user id where the status is PENDING.
1144 open get_reg_req_info_from_userid (p_requester_user_id);
1145 fetch get_reg_req_info_from_userid into
1146 x_reg_request_id, x_status_code, l_application_id,
1147 l_transaction_type_id, x_requested_for_username, x_justification;
1148 if (get_reg_req_info_from_userid%notfound) then
1149 -- Bug 4312235: We have a pending user but we are missing a record in the
1150 -- reg request table. We will exit now.
1151 close get_reg_req_info_from_userid;
1152 open getUserName (p_requester_user_id);
1153 fetch getUserName into x_requested_for_username;
1154 close getUserName;
1155 x_status_code := 'PENDING';
1156 return;
1157 end if;
1158 close get_reg_req_info_from_userid;
1159
1160 -- Lowercase the username
1161 if (x_requested_for_username is not null) then
1162 x_requested_for_username := lower (x_requested_for_username);
1163 end if;
1164
1165 elsif (x_reg_request_id is not null) then
1166
1167 -- Get the ame application id, ame transaction type id from
1168 -- the RegRequests table by the regReqID where the status is PENDING.
1169 open get_reg_req_info_from_regid (x_reg_request_id);
1170 fetch get_reg_req_info_from_regid into
1171 x_status_code, l_application_id,
1172 l_transaction_type_id, x_justification;
1173 if (get_reg_req_info_from_regid%notfound) then
1174 close get_reg_req_info_from_regid;
1175 raise_application_error ('-20000', 'Cannot find AME info in the Req Request Table with req_request_id: ' || x_reg_request_id);
1176 end if;
1177 close get_reg_req_info_from_regid;
1178
1179 else
1180
1181 -- There is an error while calling this API:
1182 -- All required input parameters are null
1183 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.');
1184
1185 end if;
1186
1187 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1188 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1189 'fnd.plsql.UMXVRRSB.get_pend_acct_info',
1190 'statusCode: ' || x_status_code);
1191 end if;
1192
1193 -- Get Current Approver name and email address
1194 if (x_status_code = 'PENDING') then
1195 -- Status code could be 'VERIFYING', in that case, don't get the next approver.
1196 UMX_REG_REQUESTS_PVT.get_current_approver_info (p_reg_request_id => x_reg_request_id,
1197 p_application_id => l_application_id,
1198 p_transaction_type_id => l_transaction_type_id,
1199 x_approver_name => x_approver_name,
1200 x_approver_email => x_approver_email_address);
1201 end if;
1202
1203 -- Get activeFrom and activeTo
1204 x_active_from := wf_engine.getitemattrtext (
1205 itemtype => UMX_REGISTRATION_UTIL.G_ITEM_TYPE,
1206 itemkey => x_reg_request_id,
1207 aname => 'REQUESTED_START_DATE');
1208
1209 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1210 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1211 'fnd.plsql.UMXVRRSB.get_pend_acct_info',
1212 'activeFrom: ' || x_active_from);
1213 end if;
1214
1215 if (x_active_from is null) or (fnd_date.canonical_to_date (x_active_from) <= sysdate) then
1216 -- active from is null, get fnd message for "Date of approval"
1217 fnd_message.set_name ('FND', 'UMX_USER_ACCT_ACTIVE_FROM_VAL');
1218 x_active_from := fnd_message.get;
1219 else
1220 x_active_from := fnd_date.date_to_displaydate (fnd_date.canonical_to_date (x_active_from));
1221 end if;
1222
1223 x_active_to := wf_engine.getitemattrtext (
1224 itemtype => UMX_REGISTRATION_UTIL.G_ITEM_TYPE,
1225 itemkey => x_reg_request_id,
1226 aname => 'REQUESTED_END_DATE');
1227
1228 if (x_active_to is not null) then
1229 x_active_to := fnd_date.date_to_displaydate (fnd_date.canonical_to_date (x_active_to));
1230 end if;
1231
1232 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1233 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1234 'fnd.plsql.UMXVRRSB.get_pend_acct_info.end',
1235 'x_reg_request_id: ' || x_reg_request_id ||
1236 ' | x_requested_for_username: ' || x_requested_for_username ||
1237 ' | x_approver_name: ' || x_approver_name ||
1238 ' | x_approver_email_address: ' || x_approver_email_address ||
1239 ' | x_status_code: ' || x_status_code ||
1240 ' | x_active_from: ' || x_active_from ||
1241 ' | x_active_to: ' || x_active_to ||
1242 ' | x_justification: ' || x_justification);
1243 end if;
1244
1245 end get_pend_acct_info;
1246
1247 --
1248 -- Procedure : get_pend_acct_info_with_userid
1249 -- Type : Private
1250 -- Pre_reqs : None
1251 -- Description : This API will return the current approver's username
1252 -- by calling ame_api.getNextApprover and email address.
1253 -- Active from and Active to from UMX's Workflow
1254 -- Input Parameters :
1255 -- @param p_requester_user_id
1256 -- Description : Requester user ID
1257 -- Required : Yes
1258 -- Output :
1259 -- x_reg_request_id
1260 -- Description: Reg Request ID
1261 -- x_requested_for_username
1262 -- Description: Requested for Username
1263 -- x_approver_name
1264 -- Description: Formated name of the current approver
1265 -- x_approver_email_address
1266 -- Description: Email address of the current approver
1267 -- x_status_code
1268 -- Description: Status code of the request
1269 -- x_active_from
1270 -- Description: The string version of the user account's start date.
1271 -- If the start date is before the sysdate, then it will
1272 -- return "Date of approval".
1273 -- x_active_to
1274 -- Description: The string version of the user account's end date .
1275 -- If the end date is null or x_active_to is "Date of approval",
1276 -- then it will return null.
1277 -- x_justification
1278 -- Description: Justification
1279 --
1280 Procedure get_pend_acct_info_with_userid (
1281 p_requester_user_id in FND_USER.USER_ID%TYPE,
1282 x_reg_request_id out NOCOPY UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
1283 x_requested_for_username out NOCOPY FND_USER.USER_NAME%TYPE,
1284 x_approver_name out NOCOPY varchar2,
1285 x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
1286 x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
1287 x_active_from out NOCOPY varchar2,
1288 x_active_to out NOCOPY varchar2,
1289 x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE) is
1290
1291 l_reg_request_id UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE;
1292
1293 begin
1294
1295 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1296 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1297 'fnd.plsql.UMXVRRSB.get_pend_acct_info_with_userid.begin',
1298 'p_requester_user_id: ' || p_requester_user_id);
1299 end if;
1300
1301 UMX_REG_REQUESTS_PVT.get_pend_acct_info (
1302 p_requester_user_id => p_requester_user_id,
1303 x_reg_request_id => x_reg_request_id,
1304 x_requested_for_username => x_requested_for_username,
1305 x_approver_name => x_approver_name,
1306 x_approver_email_address => x_approver_email_address,
1307 x_status_code => x_status_code,
1308 x_active_from => x_active_from,
1309 x_active_to => x_active_to,
1310 x_justification => x_justification);
1311
1312 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1313 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1314 'fnd.plsql.UMXVRRSB.get_pend_acct_info_with_userid.end',
1315 'x_reg_request_id: ' || x_reg_request_id ||
1316 ' | x_requested_for_username: ' || x_requested_for_username ||
1317 ' | x_approver_name: ' || x_approver_name ||
1318 ' | x_approver_email_address: ' || x_approver_email_address ||
1319 ' | x_status_code: ' || x_status_code ||
1320 ' | x_active_from: ' || x_active_from ||
1321 ' | x_active_to: ' || x_active_to ||
1322 ' | x_justification: ' || x_justification);
1323 end if;
1324
1325 end get_pend_acct_info_with_userid;
1326
1327 --
1328 -- Procedure : get_pend_acct_info_with_reqid
1329 -- Type : Private
1330 -- Pre_reqs : None
1331 -- Description : This API will return the current approver's username
1332 -- by calling ame_api.getNextApprover and email address.
1333 -- Input Parameters :
1334 -- @param x_reg_request_id
1335 -- Description : Reg Request ID
1336 -- Required : Yes
1337 -- Output :
1338 -- x_approver_name
1339 -- Description: Formated name of the current approver
1340 -- x_approver_email_address
1341 -- Description: Email address of the current approver
1342 -- x_status_code
1343 -- Description: Status code of the request
1344 -- x_active_from
1345 -- Description: The string version of the user account's start date.
1346 -- If the start date is before the sysdate, then it will
1347 -- return "Date of approval".
1348 -- x_active_to
1349 -- Description: The string version of the user account's end date .
1350 -- If the end date is null or x_active_to is "Date of approval",
1351 -- then it will return null.
1352 -- x_justification
1353 -- Description: Justification
1354 --
1355 Procedure get_pend_acct_info_with_reqid (
1356 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
1357 x_approver_name out NOCOPY varchar2,
1358 x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
1359 x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
1360 x_active_from out NOCOPY varchar2,
1361 x_active_to out NOCOPY varchar2,
1362 x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE) is
1363
1364 l_reg_request_id UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE;
1365 l_requested_for_username FND_USER.USER_NAME%TYPE;
1366
1367 begin
1368
1369 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1370 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1371 'fnd.plsql.UMXVRRSB.get_pend_acct_info_with_reqid.begin',
1372 'p_reg_request_id: ' || p_reg_request_id);
1373 end if;
1374
1375 l_reg_request_id := p_reg_request_id;
1376
1377 UMX_REG_REQUESTS_PVT.get_pend_acct_info (
1378 x_reg_request_id => l_reg_request_id,
1379 x_requested_for_username => l_requested_for_username,
1380 x_approver_name => x_approver_name,
1381 x_approver_email_address => x_approver_email_address,
1382 x_status_code => x_status_code,
1383 x_active_from => x_active_from,
1384 x_active_to => x_active_to,
1385 x_justification => x_justification);
1386
1387 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1388 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1389 'fnd.plsql.UMXVRRSB.get_pend_acct_info_with_reqid.end',
1390 'x_approver_name:' || x_approver_name ||
1391 ' | x_approver_email_address: ' || x_approver_email_address ||
1392 ' | x_status_code: ' || x_status_code ||
1393 ' | x_active_from: ' || x_active_from ||
1394 ' | x_active_to: ' || x_active_to ||
1395 ' | x_justification: ' || x_justification);
1396 end if;
1397
1398 end get_pend_acct_info_with_reqid;
1399
1400 --
1401 -- Procedure : get_error_wf_info
1402 -- Type : Private
1403 -- Pre_reqs : None
1404 -- Description : This API will call wf_engine.iteminfo to get the status
1405 -- of the main UMX Workflow and all its event subscribers.
1406 -- Input Parameters (Mandatory):
1407 -- p_reg_request_id: Registration Request ID
1408 --
1409 -- Output Parameters:
1410 -- x_itemtype: Workflow's Item Type
1411 -- x_itemkey: Workflow's Item Key
1412 -- x_status: Workflow's Status
1413 -- x_result: Result
1414 -- x_actid: Activity ID
1415 -- x_errname: Error Name
1416 -- x_errmsg: Error Message
1417 -- x_errstack: Error Stack
1418 --
1419 --
1420 procedure get_error_wf_info (p_reg_request_id in wf_items.item_type%type,
1421 x_itemtype out nocopy wf_items.item_type%type,
1422 x_itemkey out nocopy wf_items.item_key%type,
1423 x_status out nocopy varchar2,
1424 x_result out nocopy varchar2,
1425 x_actid out nocopy number,
1426 x_errname out nocopy varchar2,
1427 x_errmsg out nocopy varchar2,
1428 x_errstack out nocopy varchar2) IS
1429
1430 cursor get_child_workflow is
1431 select * from wf_items
1432 where parent_item_type = umx_registration_util.g_item_type
1433 and parent_item_key = p_reg_request_id;
1434
1435 child get_child_workflow%rowtype;
1436
1437 BEGIN
1438
1439 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1440 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1441 'fnd.plsql.UMXVRRSB.get_error_wf_info.begin',
1442 'p_reg_request_id: ' || p_reg_request_id);
1443 end if;
1444
1445 -- Find the status of the main workflow first
1446 x_itemtype := umx_registration_util.g_item_type;
1447 x_itemkey := p_reg_request_id;
1448 wf_engine.iteminfo (itemtype => x_itemtype,
1449 itemkey => x_itemkey,
1450 status => x_status,
1451 result => x_result,
1452 actid => x_actid,
1453 errname => x_errname,
1454 errmsg => x_errmsg,
1455 errstack => x_errstack);
1456
1457 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1458 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1459 'fnd.plsql.UMXVRRSB.get_error_wf_info',
1460 'x_status: ' || x_status);
1461 end if;
1462
1463 if not (x_status = 'ERROR') then
1464 for child in get_child_workflow
1465 loop
1466 x_itemtype := child.item_type;
1467 x_itemkey := child.item_key;
1468 wf_engine.iteminfo (itemtype => x_itemtype,
1469 itemkey => x_itemkey,
1470 status => x_status,
1471 result => x_result,
1472 actid => x_actid,
1473 errname => x_errname,
1474 errmsg => x_errmsg,
1475 errstack => x_errstack);
1476 exit when x_status = 'ERROR';
1477 end loop;
1478 end if;
1479
1480 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1481 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1482 'fnd.plsql.UMXVRRSB.get_error_wf_info.end',
1483 'x_itemtype: ' || x_itemtype ||
1484 ' | x_itemkey: ' || x_itemkey ||
1485 ' | x_status: ' || x_status ||
1486 ' | x_result: ' || x_result ||
1487 ' | x_actid: ' || x_actid ||
1488 ' | x_errname: ' || x_errname ||
1489 ' | x_errmsg: ' || x_errmsg ||
1490 ' | x_errstack: ' || x_errstack);
1491 end if;
1492
1493 END get_error_wf_info;
1494
1495 --
1496 -- Procedure : get_error_wf_info
1497 -- Type : Private
1498 -- Pre_reqs : None
1499 -- Description : This API will call wf_engine.iteminfo to get the status
1500 -- of the main UMX Workflow and all its event subscribers.
1501 -- Input Parameters (Mandatory):
1502 -- p_user_id: User ID of the requester
1503 --
1504 -- Output Parameters:
1505 -- x_itemtype: Workflow's Item Type
1506 -- x_itemkey: Workflow's Item Key
1507 -- x_status: Workflow's Status
1508 -- x_result: Result
1509 -- x_actid: Activity ID
1510 -- x_errname: Error Name
1511 -- x_errmsg: Error Message
1512 -- x_errstack: Error Stack
1513 --
1514 --
1515 procedure get_error_wf_info (p_user_id in fnd_user.user_id%type,
1516 x_itemtype out nocopy wf_items.item_type%type,
1517 x_itemkey out nocopy wf_items.item_key%type,
1518 x_status out nocopy varchar2,
1519 x_result out nocopy varchar2,
1520 x_actid out nocopy number,
1521 x_errname out nocopy varchar2,
1522 x_errmsg out nocopy varchar2,
1523 x_errstack out nocopy varchar2) IS
1524
1525 cursor get_reg_req_id_with_user_id (l_user_id in fnd_user.user_id%type) is
1526 select reg_request_id
1527 from umx_reg_requests
1528 where requested_for_user_id = l_user_id;
1529
1530 l_reg_req_id umx_reg_requests.reg_request_id%type;
1531
1532 BEGIN
1533
1534 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1535 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1536 'fnd.plsql.UMXVRRSB.get_error_wf_info.begin',
1537 'p_user_id: ' || p_user_id);
1538 end if;
1539
1540 -- Get the reg request id from user id
1541 open get_reg_req_id_with_user_id (p_user_id);
1542 fetch get_reg_req_id_with_user_id into l_reg_req_id;
1543 if (get_reg_req_id_with_user_id%notfound) then
1544 -- There is a problem here. A pending user but with no record in the
1545 -- Reg Table.
1546 close get_reg_req_id_with_user_id;
1547 x_status := 'PENDING';
1548 else
1549 close get_reg_req_id_with_user_id;
1550
1551 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1552 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1553 'fnd.plsql.UMXVRRSB.get_error_wf_info',
1554 'l_reg_req_id: ' || l_reg_req_id);
1555 end if;
1556
1557 get_error_wf_info (p_reg_request_id => l_reg_req_id,
1558 x_itemtype => x_itemtype,
1559 x_itemkey => x_itemkey,
1560 x_status => x_status,
1561 x_result => x_result,
1562 x_actid => x_actid,
1563 x_errname => x_errname,
1564 x_errmsg => x_errmsg,
1565 x_errstack => x_errstack);
1566 end if;
1567
1568 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1569 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1570 'fnd.plsql.UMXVRRSB.get_error_wf_info.end',
1571 'x_itemtype: ' || x_itemtype ||
1572 ' | x_itemkey: ' || x_itemkey ||
1573 ' | x_status: ' || x_status ||
1574 ' | x_result: ' || x_result ||
1575 ' | x_actid: ' || x_actid ||
1576 ' | x_errname: ' || x_errname ||
1577 ' | x_errmsg: ' || x_errmsg ||
1578 ' | x_errstack: ' || x_errstack);
1579 end if;
1580
1581 END get_error_wf_info;
1582
1583 --
1584 -- Function : is_pend_request_error
1585 -- Type : Private
1586 -- Pre_reqs : None
1587 -- Description : This API will call wf_engine.iteminfo to get the status
1588 -- of the main UMX Workflow and all its event subscribers.
1589 -- It will return 'Y' if account is in error stage and 'N' if otherwise.
1590 -- Input Parameters (Mandatory):
1591 -- p_reg_request_id: Registration Request ID
1592 --
1593 -- Output Parameter:
1594 -- It will return 'Y' if pending account has error and 'N' if otherwise.
1595 --
1596 function is_pend_request_error (p_reg_request_id in umx_reg_requests.reg_request_id%type) return varchar2 is
1597
1598 l_itemtype wf_items.item_type%type;
1599 l_itemkey wf_items.item_key%type;
1600 l_status varchar2(8);
1601 l_result varchar2(30);
1602 l_actid number;
1603 l_errname varchar2(30);
1604 l_errmsg varchar2(2000);
1605 l_errstack varchar2(4000);
1606
1607 BEGIN
1608
1609 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1610 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1611 'fnd.plsql.UMXVRRSB.is_pend_request_error.begin',
1612 'p_reg_request_id: ' || p_reg_request_id);
1613 end if;
1614
1615 get_error_wf_info (p_reg_request_id => p_reg_request_id,
1616 x_itemtype => l_itemtype,
1617 x_itemkey => l_itemkey,
1618 x_status => l_status,
1619 x_result => l_result,
1620 x_actid => l_actid,
1621 x_errname => l_errname,
1622 x_errmsg => l_errmsg,
1623 x_errstack => l_errstack);
1624
1625 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1626 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1627 'fnd.plsql.UMXVRRSB.is_pend_request_error.end',
1628 'l_status: ' || l_status);
1629 end if;
1630
1631 if (l_status = 'ERROR') then
1632 return ('Y');
1633 else
1634 return ('N');
1635 end if;
1636
1637 END is_pend_request_error;
1638
1639 --
1640 -- Function : is_pend_account_error
1641 -- Type : Private
1642 -- Pre_reqs : None
1643 -- Description : This API will call wf_engine.iteminfo to get the status
1644 -- of the main UMX Workflow and all its event subscribers.
1645 -- It will return 'Y' if account is in error stage and 'N' if otherwise.
1646 -- Input Parameters (Mandatory):
1647 -- p_user_id: User ID of the requester
1648 --
1649 -- Output Parameter:
1650 -- It will return 'Y' if pending account has error and 'N' if otherwise.
1651 --
1652 function is_pend_account_error (p_user_id in fnd_user.user_id%type) return varchar2 is
1653
1654 l_itemtype wf_items.item_type%type;
1655 l_itemkey wf_items.item_key%type;
1656 l_status varchar2(8);
1657 l_result varchar2(30);
1658 l_actid number;
1659 l_errname varchar2(30);
1660 l_errmsg varchar2(2000);
1661 l_errstack varchar2(4000);
1662
1663 BEGIN
1664
1665 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1666 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1667 'fnd.plsql.UMXVRRSB.is_pend_account_error.begin',
1668 'p_user_id: ' || p_user_id);
1669 end if;
1670
1671 get_error_wf_info (p_user_id => p_user_id,
1672 x_itemtype => l_itemtype,
1673 x_itemkey => l_itemkey,
1674 x_status => l_status,
1675 x_result => l_result,
1676 x_actid => l_actid,
1677 x_errname => l_errname,
1678 x_errmsg => l_errmsg,
1679 x_errstack => l_errstack);
1680
1681 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1682 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1683 'fnd.plsql.UMXVRRSB.is_pend_account_error.end',
1684 'l_status: ' || l_status);
1685 end if;
1686
1687 if (l_status = 'ERROR') then
1688 return ('Y');
1689 else
1690 return ('N');
1691 end if;
1692
1693 END is_pend_account_error;
1694
1695 END UMX_REG_REQUESTS_PVT;
[Home] [Help]