[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_USER_ADMIN_PVT
Source
1 PACKAGE BODY JTF_UM_USER_ADMIN_PVT as
2 /* $Header: JTFVUUAB.pls 120.5 2005/12/07 05:18:47 vimohan ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'JTF_UM_USER_ADMIN_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'JTFVUUAB.pls';
6
7 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
8 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
9 G_MODULE VARCHAR2(40) := 'JTF.UM.PLSQL.USERADMIN';
10 l_is_debug_parameter_on boolean := JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(G_MODULE);
11
12
13 /**
14 * Function : getUserStatusCode
15 * Type : Private
16 * Pre_reqs :
17 * Description : Return the status code of a user.
18 * Parameters :
19 * input parameters
20 * p_userid
21 * description: FND User ID to return the status code
22 * required : Y
23 * output parameters
24 * status code
25 * description: The usertype status code of the user.
26 * Errors : possible errors raised by this API
27 * Other Comments :
28 */
29 FUNCTION getUserStatusCode (p_userid in number) return varchar2 is
30
31 l_status_code VARCHAR2 (30) := null;
32
33 cursor getUserStatusCodeCursor is
34 select status_code
35 from jtf_um_usertype_reg
36 where user_id = p_userid
37 and nvl (effective_end_date, sysdate + 1) > sysdate;
38
39 BEGIN
40 OPEN getUserStatusCodeCursor;
41 FETCH getUserStatusCodeCursor INTO l_status_code;
42 if (getUserStatusCodeCursor%notfound) then
43 -- Cannot find the status code of the user.
44 close getUserStatusCodeCursor;
45 RETURN null;
46 end if;
47 CLOSE getUserStatusCodeCursor;
48
49 RETURN l_status_code;
50 END getUserStatusCode;
51
52 /**
53 * Function : getUserID
54 * Type : Private
55 * Pre_reqs :
56 * Description : Return the user id of a user.
57 * Parameters :
58 * input parameters
59 * p_username
60 * description: FND User name
61 * required : Y
62 * output parameters
63 * userid
64 * description: The userid of the provided username
65 * Errors : possible errors raised by this API
66 * Other Comments :
67 */
68 function getUserID (p_username in varchar2) return number is
69
70 l_userid number;
71
72 cursor getUserIDCursor is
73 select user_id
74 from fnd_user
75 where user_name = p_username
76 and (nvl (end_date, sysdate + 1) > sysdate or
77 to_char(END_DATE) = to_char(FND_API.G_MISS_DATE)) ;
78
79 begin
80 open getUserIDCursor;
81 fetch getUserIDCursor into l_userid;
82 IF (getUserIDCursor%notfound) THEN
83 -- Cannot find the userid of the user.
84 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
85 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_UA_MISSING_USERID');
86 FND_MESSAGE.Set_Token('0', G_PKG_NAME, FALSE);
87 FND_MESSAGE.Set_Token('1', 'p_username', FALSE);
88 FND_MSG_PUB.ADD;
89 END IF;
90 close getUserIDCursor;
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
92 END IF;
93 close getUserIDCursor;
94
95 return l_userid;
96 end getUserID;
97
98 /**
99 * Procedure : INACTIVE_USER
100 * Type : Private
101 * Pre_reqs : WF_DIRECTORY.CreateAdHocUser and
102 * WF_DIRECTORY.SetAdHocUserAttr
103 * Description : Inactive an user with these Scenarios
104 * 1. Usertype Request is PENDING
105 * - call FND_USER_PKG.DisableUser API
106 * - Kill the usertype WF
107 * - Set the usertype_reg table status to REJECTED
108 * - Find all the *PENDING* Enrollments, and REJECT these
109 * (believe we have a USERTYPE_REJECTED status or similar)
110 * in the subscription_reg table
111 * - revoke the "PENDING_APPROVAL" responsibility
112 *
113 * 2. Usertype Request is UPGRADE_PENDING
114 * - call FND_USER_PKG.DisableUser API
115 * - Set the usertype_reg table status to REJECTED
116 * - Reject the old approval task
117 *
118 * 3. Usertype Request is APPROVED or UPGRADE
119 * - call FND_USER_PKG.DisableUser API
120 * - Do not set the usertype_reg table status (leave as APPROVED)
121 * - Find all the *PENDING* Enrollments, and REJECT these (REJECTED status)
122 * in the subscription_reg table
123 * - Do not change the status for any approved / rejected enrollments
124 * Parameters :
125 * input parameters
126 * p_username
127 * description: The inactive username.
128 * required : Y
129 * output parameters
130 * Errors : possible errors raised by this API
131 * Other Comments :
132 */
133 PROCEDURE INACTIVE_USER (p_api_version_number in number,
134 p_init_msg_list in varchar2 default FND_API.G_FALSE,
135 p_commit in varchar2 default FND_API.G_FALSE,
136 p_validation_level in number default FND_API.G_VALID_LEVEL_FULL,
137 p_username in varchar2,
138 x_return_status out NOCOPY varchar2,
139 x_msg_data out NOCOPY varchar2,
140 x_msg_count out NOCOPY number) is
141
142 l_api_version_number NUMBER := 1.0;
143 l_api_name VARCHAR2 (50) := 'INACTIVE_USER';
144 l_username VARCHAR2 (100) := p_username;
145 l_userid NUMBER;
146 l_itemtype VARCHAR2 (8);
147 l_itemkey VARCHAR2 (240);
148 l_status_code VARCHAR2 (30);
149 l_task_id NUMBER;
150 l_party_id NUMBER;
151 l_sort_data JTF_TASKS_PUB.SORT_DATA;
152 l_task_table JTF_TASKS_PUB.TASK_TABLE_TYPE;
153 l_total_retrieved NUMBER;
154 l_total_returned NUMBER;
155 l_version_num NUMBER;
156
157 cursor getUTWFInfo is
158 select wf_item_type, to_char (usertype_reg_id)
159 from jtf_um_usertype_reg
160 where user_id = l_userid
161 and status_code = 'PENDING'
162 and nvl (effective_end_date, sysdate + 1) > sysdate;
163
164 cursor getEnrollWFInfo is
165 select wf_item_type, subscription_reg_id
166 from jtf_um_subscription_reg
167 where user_id = l_userid
168 and status_code = 'PENDING'
169 and nvl (effective_end_date, sysdate + 1) > sysdate;
170
171 cursor getPartyID is
172 select customer_id
173 from fnd_user
174 where user_id = l_userid
175 and (nvl (end_date, sysdate + 1) > sysdate OR
176 to_char(END_DATE) = to_char(FND_API.G_MISS_DATE)) ;
177
178 BEGIN
179 -- Write to debug log
180 -- JTF_DEBUG_PUB.add_debug_msg('EVENT', G_MODULE, 'Entering API send_Password ...');
181
182 -- Standard call to check for call compatibility.
183 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
184 p_api_version_number,
185 l_api_name,
186 G_PKG_NAME)
187 THEN
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189 END IF;
190
191 -- call the user hook if it is a public api. Private APIs do not require a user hook call.
192
193 -- Standard Start of API savepoint
194 SAVEPOINT INACTIVE_USER;
195
196 -- Initialize message list if p_init_msg_list is set to TRUE.
197 IF FND_API.to_Boolean (p_init_msg_list) THEN
198 FND_MSG_PUB.initialize;
199 END IF;
200
201 -- Initialize API return status to success
202 x_return_status := FND_API.G_RET_STS_SUCCESS;
203
204 --
205 -- Beginning of API body
206 --
207 -- Validate required fields
208 IF (p_username is null) THEN
209 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
210 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_REQUIRED_FIELD');
211 FND_MESSAGE.Set_Token('API_NAME', G_PKG_NAME, FALSE);
212 FND_MESSAGE.Set_Token('FIELD', 'p_username', FALSE);
213 FND_MSG_PUB.ADD;
214 END IF;
215 RAISE FND_API.G_EXC_ERROR;
216 END IF;
217
218 -- Get the userid
219 l_userid := getUserID (p_username);
220
221 -- Get the user usertype status code
222 l_status_code := getUserStatusCode (l_userid);
223
224 IF (l_status_code = 'PENDING') THEN
225
226 -- 1. abort Usertype WF Process first
227 -- query all itemtype and itemkey before aborting WF
228 OPEN getUTWFInfo;
229 FETCH getUTWFInfo INTO l_itemtype, l_itemkey;
230 -- abort WF only if we can find the itemtype and itemkey.
231 IF (getUTWFInfo%found) THEN
232 JTF_UM_WF_APPROVAL.abort_process (l_itemtype, l_itemkey);
233 END IF;
234 CLOSE getUTWFInfo;
235
236 -- 2. Set the usertype_reg table status to REJECTED
237 UPDATE JTF_UM_USERTYPE_REG
238 SET status_code = 'REJECTED',
239 last_updated_by = fnd_global.user_id,
240 last_update_date = sysdate,
241 last_approver_comment = 'USER DELETED',
242 effective_end_date = sysdate
243 WHERE user_id = l_userid
244 AND nvl (effective_end_date, sysdate + 1) > sysdate
245 AND status_code = 'PENDING';
246
247 -- 3. Find all the PENDING enrollments and set USER_REJECTED.
248 UPDATE JTF_UM_SUBSCRIPTION_REG
249 SET status_code = 'USER_REJECTED',
250 last_updated_by = fnd_global.user_id,
251 last_update_date = sysdate,
252 last_approver_comment = 'USER DELETED',
253 effective_end_date = sysdate
254 WHERE user_id = l_userid
255 AND nvl (effective_end_date, sysdate + 1) > sysdate
256 AND status_code = 'PENDING';
257
258 -- 4. Revoke the PENDING_APPROVAL responsibility.
259 JTF_UM_USERTYPE_CREDENTIALS.REVOKE_RESPONSIBILITY (
260 X_USER_ID => l_userid,
261 X_RESPONSIBILITY_KEY => 'JTF_PENDING_APPROVAL',
262 X_APPLICATION_ID => 690);
263
264 ELSIF (l_status_code = 'UPGRADE_APPROVAL_PENDING') THEN
265
266 -- 1. Set the usertype_reg table status to REJECTED
267 UPDATE JTF_UM_USERTYPE_REG
268 SET status_code = 'REJECTED',
269 last_updated_by = fnd_global.user_id,
270 last_update_date = sysdate,
271 last_approver_comment = 'USER DELETED',
272 effective_end_date = sysdate
273 WHERE user_id = l_userid
274 AND nvl (effective_end_date, sysdate + 1) > sysdate
275 AND status_code = 'PENDING';
276
277 -- 2. Reject the old approval task
278 -- To reject the old approval,
279 -- first, we need the user's party_id
280 open getPartyID;
281 fetch getPartyID into l_party_id;
282 close getPartyID;
283
284 -- second, with the party_id, we can query the task_id.
285 l_sort_data(1).field_name := 'task_id';
286 l_sort_data(1).asc_dsc_flag := 'A';
287 l_sort_data(2).field_name := 'task_name';
288 l_sort_data(2).asc_dsc_flag := 'D';
289
290 JTF_TASKS_PUB.query_task (P_API_VERSION => 1.0,
291 p_start_pointer => 1,
292 p_rec_wanted => 10,
293 p_show_all => 'Y',
294 p_object_type_code => 'ISUPPORT',
295 p_task_status_id => 10,
296 p_task_type_id => 1,
297 P_sort_data => l_sort_data,
298 p_customer_id => l_party_id,
299 p_source_object_id => l_party_id,
300 x_return_status => x_return_status,
301 x_msg_count => x_msg_count,
302 x_msg_data => x_msg_data,
303 x_task_table => l_task_table,
304 x_total_retrieved => l_total_retrieved,
305 x_total_returned => l_total_returned,
306 x_object_version_number => l_version_num);
307
308 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
309 RAISE FND_API.G_EXC_ERROR;
310 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
311 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
312 END IF;
313
314 -- now, we can update the task with the task_id
315 task_mgr.update_task ('1.0', '1',
316 l_task_table(l_task_table.first).task_id,
317 'USER DELETED', 'REJECT', x_msg_data,
318 x_return_status, x_msg_count);
319
320 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
321 RAISE FND_API.G_EXC_ERROR;
322 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
324 END IF;
325
326 ELSIF (l_status_code = 'APPROVED') OR (l_status_code = 'UPGRADE') THEN
327 -- 1. abort Enrollment WF Process
328 -- query all itemtype and itemkey before aborting WF
329 FOR enrollRegRow in getEnrollWFInfo LOOP
330 jtf_um_wf_approval.abort_process (enrollRegRow.wf_item_type, to_char(enrollRegRow.subscription_reg_id));
331 END LOOP;
332
333 -- 2. Find all the PENDING enrollments and set USER_REJECTED.
334 UPDATE JTF_UM_SUBSCRIPTION_REG
335 SET status_code = 'USER_REJECTED',
336 last_updated_by = fnd_global.user_id,
337 last_update_date = sysdate,
338 last_approver_comment = 'USER DELETED',
339 effective_end_date = sysdate
340 WHERE user_id = l_userid
341 AND nvl (effective_end_date, sysdate + 1) > sysdate
342 AND status_code = 'PENDING';
343
344 END IF;
345
346 -- End date the user with the FND_API
347 FND_USER_PKG.DisableUser (p_username);
348
349 --
350 -- End of API body
351 --
352
353 -- Standard check for p_commit
354 IF FND_API.to_Boolean (p_commit) THEN
355 COMMIT WORK;
356 END IF;
357
358 -- Standard call to get message count and if count is 1, get message info.
359 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
360 p_data => x_msg_data);
361
362 -- call the user hook if it is a public api. Private APIs do not require a user hook call.
363
364 -- Write to debug log
365 -- JTF_DEBUG_PUB.add_debug_msg('EVENT', G_MODULE, 'Exiting API send_Password ...');
366
367 EXCEPTION
368 WHEN FND_API.G_EXC_ERROR THEN
369 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS (P_API_NAME => L_API_NAME,
370 P_PKG_NAME => G_PKG_NAME,
371 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
372 P_SQLCODE => SQLCODE,
373 P_SQLERRM => SQLERRM,
374 X_MSG_COUNT => X_MSG_COUNT,
375 X_MSG_DATA => X_MSG_DATA,
376 X_RETURN_STATUS => X_RETURN_STATUS);
377
378 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
379 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS (P_API_NAME => L_API_NAME,
380 P_PKG_NAME => G_PKG_NAME,
381 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
382 P_SQLCODE => SQLCODE,
383 P_SQLERRM => SQLERRM,
384 X_MSG_COUNT => X_MSG_COUNT,
385 X_MSG_DATA => X_MSG_DATA,
386 X_RETURN_STATUS => X_RETURN_STATUS);
387
388 WHEN OTHERS THEN
389 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS (P_API_NAME => L_API_NAME,
390 P_PKG_NAME => G_PKG_NAME,
391 P_EXCEPTION_LEVEL => JTF_DEBUG_PUB.G_EXC_OTHERS,
392 P_SQLCODE => SQLCODE,
393 P_SQLERRM => SQLERRM,
394 X_MSG_COUNT => X_MSG_COUNT,
395 X_MSG_DATA => X_MSG_DATA,
396 X_RETURN_STATUS => X_RETURN_STATUS);
397
398 END INACTIVE_USER;
399
400
401 /**
402 * This API creates an entry into the jtf_usertype_reg table. It also sets the
403 * responsibility to "pending". If approval is required a workflow is initiated
404 * if not the credentials are assigned.
405 */
406
407 PROCEDURE Create_System_User(p_username in varchar2,
408 p_usertype_id in number,
409 p_user_id in number,
410 x_user_reg_id out NOCOPY number,
411 x_approval_id out NOCOPY number) is
412
413 l_wf_item_type varchar2(200);
414 l_USER_ID NUMBER := FND_GLOBAL.USER_ID;
415 l_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
416 --x_usertype_reg_id NUMBER;
417 l_respKey varchar2(50) := 'JTF_PENDING_APPROVAL';
418 l_status varchar2(10) := 'PENDING';
419 l_application_id number := 690;
420 l_usertype_key varchar2(100);
421
422 cursor c_wf_item_type is
423 SELECT WF_ITEM_TYPE
424 FROM JTF_UM_APPROVALS_B APR,
425 JTF_UM_USERTYPES_B UT
426 WHERE UT.APPROVAL_ID = APR.APPROVAL_ID
427 AND UT.USERTYPE_ID = p_usertype_id;
428
429 cursor c_usertype is
430 SELECT APPROVAL_ID, USERTYPE_KEY
431 FROM JTF_UM_USERTYPES_B
432 WHERE USERTYPE_ID = p_usertype_id;
433
434 l_method varchar2(25) := 'Create_system_user';
435 Begin
436
437 JTF_DEBUG_PUB.log_entering_method(g_module, l_method);
438 for i in c_wf_item_type loop
439 l_wf_item_type := i.wf_item_type;
440 end loop;
441
442 if l_is_debug_parameter_on then
443 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'before insert um reg row');
444 end if;
445
446
447 JTF_UM_USERTYPES_PKG.INSERT_UMREG_ROW(
448 X_USERTYPE_ID => p_usertype_id,
449 X_LAST_APPROVER_COMMENT => null,
450 X_APPROVER_USER_ID => null,
451 X_EFFECTIVE_END_DATE => null,
452 X_WF_ITEM_TYPE => l_wf_item_type,
453 X_EFFECTIVE_START_DATE => sysdate,
454 X_USERTYPE_REG_ID => x_user_reg_id,
455 X_USER_ID => p_user_id,
456 X_STATUS_CODE => l_status,
457 X_CREATION_DATE => sysdate,
458 X_CREATED_BY => l_user_id,
459 X_LAST_UPDATE_DATE => sysdate,
460 X_LAST_UPDATED_BY => l_user_id,
461 X_LAST_UPDATE_LOGIN => l_login_id);
462
463 if l_is_debug_parameter_on then
464 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'After insert um reg row');
465 end if;
466
467
468 -- for bug 4378387 , end_date all subscriptions for the current
469 -- user type which might have been granted to the user before
470 -- this is done to ensure that all roles/resp are granted once again
471 -- details in bug.
472 UPDATE JTF_UM_SUBSCRIPTION_REG
473 SET effective_END_DATE =SYSDATE, last_update_login=l_user_id,last_update_date=sysdate
474 WHERE USER_ID = p_user_id and status_code='APPROVED'
475 and effective_start_date < sysdate and nvl(effective_end_date,sysdate +1) > sysdate
476 AND SUBSCRIPTION_ID IN
477 (select SUBSCRIPTION_ID from JTF_UM_USERTYPE_SUBSCRIP
478 where userTYpe_id=p_usertype_id and
479 effective_start_date < sysdate and nvl(effective_end_date,sysdate +1) > sysdate);
480
481
482
483
484 if l_is_debug_parameter_on then
485 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'returning from insert um reg row reg id '|| x_user_reg_id);
486 end if;
487
488
489
490 for i in c_usertype loop
491 x_approval_id := i.approval_id;
492 l_usertype_key := i.usertype_key;
493 end loop;
494
495 if (x_approval_id is null or x_approval_id = -1) then
496 JTF_UM_USERTYPE_CREDENTIALS.ASSIGN_USERTYPE_CREDENTIALS(p_username, p_user_id, p_usertype_id);
497 else
498 if l_is_debug_parameter_on then
499 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'involing jtf_um_wf_approval.CreateProcess usertype_id ' || p_usertype_id||' p_user_id ' || p_user_id || ' reg id '|| x_user_reg_id || ' approval id ' || x_approval_id);
500 end if;
501 JTF_UM_USERTYPE_CREDENTIALS.ASSIGN_DEFAULT_RESPONSIBILITY(p_user_id,l_respKey,l_application_id);
502 jtf_um_wf_approval.CreateProcess (requestType => 'USERTYPE',
503 requestID => p_usertype_id,
504 requesterUserID => p_user_id,
505 requestRegID => x_user_reg_id);
506 end if;
507
508 JTF_DEBUG_PUB.log_exiting_method(g_module, l_method);
509
510 end create_system_user;
511
512
513 PROCEDURE queryUTTemplateInfo(p_usertype_id in number,
514 x_usertype_key out NOCOPY varchar2,
515 x_usertype_name out NOCOPY varchar2,
516 x_template_id out NOCOPY number,
517 x_page_name out NOCOPY varchar2,
518 x_template_handler out NOCOPY varchar2,
519 x_explicit_enr_count out NOCOPY number
520 ) is
521
522
523 CURSOR c_uttemplate is
524 SELECT UT.USERTYPE_KEY, UT.USERTYPE_NAME, TMPL.TEMPLATE_ID,
525 TMPL.PAGE_NAME, TMPL.TEMPLATE_HANDLER
526 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_USERTYPE_TMPL UTMPL,
527 JTF_UM_TEMPLATES_B TMPL
528 WHERE UT.usertype_id = p_usertype_id
529 AND UT.usertype_id = UTMPL.usertype_id
530 AND UTMPL.template_id = TMPL.template_id
531 AND SYSDATE between nvl(UT.EFFECTIVE_START_DATE, sysdate)
532 and nvl(UT.EFFECTIVE_END_DATE, sysdate)
533 AND SYSDATE between nvl(TMPL.EFFECTIVE_START_DATE, sysdate)
534 and nvl(TMPL.EFFECTIVE_END_DATE, sysdate)
535 AND SYSDATE between nvl(UTMPL.EFFECTIVE_START_DATE, sysdate)
536 and nvl(UTMPL.EFFECTIVE_END_DATE, sysdate);
537
538 l_method varchar2(40) := 'queryUTTemplateInfo';
539 begin
540 JTF_DEBUG_PUB.log_entering_method(g_module, l_method);
541
542 for i in c_uttemplate loop
543 x_usertype_key := i.usertype_key;
544 x_usertype_name := i.usertype_name;
545 x_template_id := i.template_id;
546 x_page_name := i.page_name;
547 x_template_handler := i.template_handler;
548 end loop;
549
550
551 JTF_DEBUG_PUB.log_exiting_method(g_module, l_method);
552 end queryUTTemplateInfo;
553
554 end JTF_UM_USER_ADMIN_PVT;