[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_ENROLLMENT_CREDENTIALS
Source
1 package body JTF_UM_ENROLLMENT_CREDENTIALS as
2 /* $Header: JTFUMECB.pls 120.2 2005/10/29 03:45:13 snellepa ship $ */
3
4 MODULE_NAME CONSTANT VARCHAR2(50) := 'JTF.UM.PLSQL.JTF_UM_ENROLLMENT_CREDENTIALS';
5 l_is_debug_parameter_on boolean := JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(MODULE_NAME);
6
7 PROCEDURE REVOKE_RESPONSIBILITY
8 (
9 X_USER_ID NUMBER,
10 X_RESPONSIBILITY_ID NUMBER,
11 X_APPLICATION_ID NUMBER
12 )
13 IS
14 BEGIN
15 IF Fnd_User_Resp_Groups_Api.Assignment_Exists(
16 user_id => X_USER_ID,
17 responsibility_id => X_RESPONSIBILITY_ID,
18 responsibility_application_id => X_APPLICATION_ID
19 ) THEN
20
21 /*
22 Removed this direct update call as fnd_user_resp_groups is no
23 longer a table. Converted this call to use an API instead.
24
25 UPDATE FND_USER_RESP_GROUPS SET END_DATE = SYSDATE
26 WHERE USER_ID = X_USER_ID
27 AND RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
28 AND RESPONSIBILITY_APPLICATION_ID = X_APPLICATION_ID;
29 */
30
31 Fnd_User_Resp_Groups_Api.UPLOAD_ASSIGNMENT(
32 user_id => X_USER_ID,
33 responsibility_id => X_RESPONSIBILITY_ID,
34 responsibility_application_id => X_APPLICATION_ID,
35 start_date => sysdate,
36 end_date => sysdate, -- Revoke the responsibility
37 description => null );
38 END IF;
39
40 END REVOKE_RESPONSIBILITY;
41
42 PROCEDURE ASSIGN_RESPONSIBILITY
43 (
44 X_USER_ID NUMBER,
45 X_RESPONSIBILITY_ID NUMBER,
46 X_APPLICATION_ID NUMBER
47 )
48 IS
49 BEGIN
50
51 Fnd_User_Resp_Groups_Api.Upload_Assignment(
52 user_id => X_USER_ID,
53 responsibility_id => X_RESPONSIBILITY_ID,
54 responsibility_application_id => X_APPLICATION_ID,
55 start_date => sysdate,
56 end_date => null,
57 description => null );
58
59 END ASSIGN_RESPONSIBILITY;
60
61
62 PROCEDURE ASSIGN_RESPONSIBILITY
63 (
64 X_USER_ID NUMBER,
65 X_RESPONSIBILITY_KEY VARCHAR2,
66 X_APPLICATION_ID NUMBER
67 )
68 IS
69
70 p_responsibility_id NUMBER;
71 CURSOR RESP_KEY IS SELECT RESPONSIBILITY_ID
72 FROM FND_RESPONSIBILITY_VL
73 WHERE RESPONSIBILITY_KEY = X_RESPONSIBILITY_KEY;
74 BEGIN
75
76 OPEN RESP_KEY;
77
78 FETCH RESP_KEY INTO p_responsibility_id;
79
80 CLOSE RESP_KEY;
81
82 IF NVL(p_responsibility_id,0) <> 0 THEN
83
84 ASSIGN_RESPONSIBILITY
85 (
86 X_USER_ID => X_USER_ID,
87 X_RESPONSIBILITY_ID => p_responsibility_id,
88 X_APPLICATION_ID => X_APPLICATION_ID );
89 END IF;
90
91 END ASSIGN_RESPONSIBILITY;
92
93
94 PROCEDURE REVOKE_RESPONSIBILITY
95 (
96 X_USER_ID NUMBER,
97 X_RESPONSIBILITY_KEY VARCHAR2,
98 X_APPLICATION_ID NUMBER
99 )
100 IS
101
102 p_responsibility_id NUMBER;
103 CURSOR RESP_KEY_ID IS SELECT RESPONSIBILITY_ID
104 FROM FND_RESPONSIBILITY_VL
105 WHERE RESPONSIBILITY_KEY = X_RESPONSIBILITY_KEY;
106 BEGIN
107
108 OPEN RESP_KEY_ID;
109
110 FETCH RESP_KEY_ID INTO p_responsibility_id;
111
112 CLOSE RESP_KEY_ID;
113
114 IF NVL(p_responsibility_id,0) <> 0 THEN
115
116 REVOKE_RESPONSIBILITY
117 (
118 X_USER_ID => X_USER_ID,
119 X_RESPONSIBILITY_ID => p_responsibility_id,
120 X_APPLICATION_ID => X_APPLICATION_ID
121 );
122 END IF;
123
124 END REVOKE_RESPONSIBILITY;
125
126 PROCEDURE ASSIGN_ENROLLMENT_CREDENTIALS
127 (
128 X_USER_NAME VARCHAR2,
129 X_USER_ID NUMBER,
130 X_SUBSCRIPTION_ID NUMBER
131 )
132 IS
133
134 l_procedure_name CONSTANT varchar2(30) := 'ASSIGN_ENROLLMENT_CREDENTIALS';
135 p_subscription_resp_id NUMBER;
136 p_subscription_app_id NUMBER;
137 p_principal_name VARCHAR2(255);
138 l_is_del_flag_set BOOLEAN;
139 l_role_id NUMBER;
140 l_version FND_RESPONSIBILITY_VL.VERSION%TYPE;
141 l_def_resp_id NUMBER;
142 l_def_app_id NUMBER;
143 l_def_resp_key FND_RESPONSIBILITY_VL.RESPONSIBILITY_KEY%TYPE;
144 l_def_resp_name FND_RESPONSIBILITY_VL.RESPONSIBILITY_NAME%TYPE;
145
146 CURSOR SUBSCRIPTION_RESP is select FR.RESPONSIBILITY_ID, FR.APPLICATION_ID, FR.VERSION FROM
147 JTF_UM_SUBSCRIPTION_RESP SB,
148 FND_RESPONSIBILITY_VL FR
149 WHERE SB.SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
150 AND SB.RESPONSIBILITY_KEY = FR.RESPONSIBILITY_KEY
151 AND SB.APPLICATION_ID = FR.APPLICATION_ID
152 AND (SB.EFFECTIVE_END_DATE IS NULL OR SB.EFFECTIVE_END_DATE > SYSDATE)
153 AND SB.EFFECTIVE_START_DATE < SYSDATE;
154
155 CURSOR SUBSCRIPTION_ROLES IS SELECT PRINCIPAL_NAME
156 FROM JTF_UM_SUBSCRIPTION_ROLE
157 WHERE SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
158 AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE)
159 AND EFFECTIVE_START_DATE < SYSDATE;
160
161 BEGIN
162
163 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
164 p_message => l_procedure_name
165 );
166
167 if l_is_debug_parameter_on then
168 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
169 p_message => 'X_USER_NAME:' || X_USER_NAME || '+' || 'X_USER_ID:' || X_USER_ID || '+' || 'X_SUBSCRIPTION_ID:' || X_SUBSCRIPTION_ID
170 );
171 end if;
172
173
174 -- Assign Responsibilites based on user type
175 OPEN SUBSCRIPTION_RESP;
176 LOOP
177 FETCH SUBSCRIPTION_RESP INTO p_subscription_resp_id, p_subscription_app_id, l_version;
178 EXIT WHEN SUBSCRIPTION_RESP%NOTFOUND;
179
180 ASSIGN_RESPONSIBILITY
181 (
182 X_USER_ID => X_USER_ID,
183 X_RESPONSIBILITY_ID => p_subscription_resp_id,
184 X_APPLICATION_ID => p_subscription_app_id
185 );
186 -- Revoke Pending approval responsibility, if any, as at least
187 -- one enrollment has been assigned.
188
189
190 JTF_UM_USERTYPE_CREDENTIALS.REVOKE_RESPONSIBILITY
191 ( X_USER_ID => X_USER_ID,
192 X_RESPONSIBILITY_KEY => 'JTF_PENDING_APPROVAL',
193 X_APPLICATION_ID => 690
194 );
195
196
197 -- Make this responsibility a default one, if a user does not
198 -- have one and it is a web based responsibility
199
200 IF l_version = 'W' THEN
201
202 JTF_UM_USERTYPE_CREDENTIALS.get_default_login_resp(
203 p_user_id => X_USER_ID,
204 x_resp_id => l_def_resp_id,
205 x_app_id => l_def_app_id,
206 x_resp_key => l_def_resp_key,
207 x_resp_name => l_def_resp_name
208 );
209
210 IF l_def_resp_id IS NULL AND l_def_app_id IS NULL THEN
211
212 JTF_UM_USERTYPE_CREDENTIALS.set_default_login_resp(
213 p_user_id => X_USER_ID,
214 p_resp_id => p_subscription_resp_id,
215 p_app_id => p_subscription_app_id
216 );
217 END IF;
218 END IF;
219
220 END LOOP;
221 CLOSE SUBSCRIPTION_RESP;
222
223 -- Assign Roles based on user type
224
225 OPEN SUBSCRIPTION_ROLES;
226
227 LOOP
228 FETCH SUBSCRIPTION_ROLES INTO p_principal_name;
229 EXIT WHEN SUBSCRIPTION_ROLES%NOTFOUND;
230
231 JTF_AUTH_BULKLOAD_PKG.ASSIGN_ROLE
232 ( USER_NAME => X_USER_NAME,
233 ROLE_NAME => p_principal_name,
234 OWNERTABLE_NAME => 'JTF_UM_SUBSCRIPTIONS_B',
235 OWNERTABLE_KEY => X_SUBSCRIPTION_ID);
236
237 END LOOP;
238 CLOSE SUBSCRIPTION_ROLES;
239
240 -- Update the status
241
242 UPDATE JTF_UM_SUBSCRIPTION_REG SET STATUS_CODE='APPROVED'
243 WHERE SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
244 AND USER_ID = X_USER_ID;
245
246 -- Grant the delegation role, if the flag is set
247
248 JTF_UM_SUBSCRIPTIONS_PKG.get_grant_delegation_flag
249 (
250 p_subscription_id => X_SUBSCRIPTION_ID,
251 p_user_id => X_USER_ID,
252 x_result => l_is_del_flag_set
253 );
254
255 IF l_is_del_flag_set THEN
256
257 JTF_UM_SUBSCRIPTIONS_PKG.get_delegation_role(
258 p_subscription_id => X_SUBSCRIPTION_ID,
259 x_delegation_role => l_role_id
260 );
261
262
263 IF l_role_id IS NOT NULL THEN
264
265 -- Grant delegation role to a user
266 JTF_UM_UTIL_PVT.GRANT_ROLES(
267 p_user_name => X_USER_NAME ,
268 p_role_id => l_role_id,
269 p_source_name => 'JTF_UM_SUBSCRIPTIONS_B',
270 p_source_id => X_SUBSCRIPTION_ID
271 );
272
273 -- Assign the deleagtion access role
274
275 JTF_AUTH_BULKLOAD_PKG.ASSIGN_ROLE
276 ( USER_NAME => X_USER_NAME,
277 ROLE_NAME => 'JTA_UM_DELEGATION_ACCESS',
278 OWNERTABLE_NAME => 'JTF_UM_SUBSCRIPTIONS_B',
279 OWNERTABLE_KEY => X_SUBSCRIPTION_ID);
280 END IF;
281
282 END IF;
283
284 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
285 p_message => l_procedure_name
286 );
287
288
289 END ASSIGN_ENROLLMENT_CREDENTIALS;
290
291 PROCEDURE REJECT_ENROLL_DEL_PEND_USER (P_USERNAME in VARCHAR2)
292
293 IS
294
295 CURSOR FIND_ENROLL_APPWF_INFO IS
296 SELECT reg.WF_ITEM_TYPE, to_char (reg.SUBSCRIPTION_REG_ID)
297 FROM JTF_UM_SUBSCRIPTION_REG reg, FND_USER fu
298 WHERE fu.USER_NAME = P_USERNAME
299 AND fu.USER_ID = reg.USER_ID
300 AND STATUS_CODE = 'PENDING'
301 AND (reg.EFFECTIVE_END_DATE is null
302 OR reg.EFFECTIVE_END_DATE > sysdate);
303
304 itemtype varchar2 (8);
305 itemkey varchar2 (240);
306
307 BEGIN
308
309 OPEN FIND_ENROLL_APPWF_INFO;
310 FETCH FIND_ENROLL_APPWF_INFO INTO itemtype, itemkey;
311 WHILE FIND_ENROLL_APPWF_INFO%FOUND LOOP
312
313 JTF_UM_WF_APPROVAL.COMPLETEAPPROVALACTIVITY (itemtype, itemkey, 'REJECTED', 'User deleted');
314 FETCH FIND_ENROLL_APPWF_INFO INTO itemtype, itemkey;
315
316 END LOOP;
317 CLOSE FIND_ENROLL_APPWF_INFO;
318
319 END REJECT_ENROLL_DEL_PEND_USER;
320
321 --
322 -- Procedure
323 -- assign_user_enrollment
324 --
325 -- Description
326 -- This API calls other api's that will perform:
327 -- createUserEnrollment;
328 -- if (isApprovalRequired)
329 -- assignEnrollCredentials;
330 -- else
331 -- launchWFProcess;
332 --
333 -- IN
334 -- p_user_id -- userid
335 -- p_username -- username
336 -- p_usertype_id -- usertype id
337 -- p_enrollment_id -- enrollment id
338 -- p_delegate_flag -- delegate flag (T/F)
339 -- p_approval_id -- enrollment's approval id,
340 -- if p_approval_id is null approval is not required.
341 -- if p_approval_id is not null, approval is required.
342 --
343 -- OUT
344 -- x_enrollment_reg_id -- enrollment reg id
345 procedure assign_user_enrollment (p_user_id in number,
346 p_username in varchar2,
347 p_usertype_id in number,
348 p_enrollment_id in number,
349 p_delegate_flag in varchar2,
350 p_approval_id in number,
351 x_enrollment_reg_id out NOCOPY number) is
352
353 METHOD_NAME varchar2 (22) := 'ASSIGN_USER_ENROLLMENT';
354 l_wf_item_type jtf_um_subscription_reg.wf_item_type%TYPE;
355 l_usertype_status jtf_um_usertype_reg.status_code%TYPE;
356
357 cursor get_wf_item_type is
358 select wf_item_type
359 from jtf_um_approvals_b
360 where approval_id = p_approval_id
361 and effective_start_date < sysdate
362 and nvl (effective_end_date, sysdate + 1) > sysdate;
363
364 cursor get_user_usertype_status is
365 select status_code
366 from jtf_um_usertype_reg
367 where user_id = p_user_id
368 and effective_start_date <= sysdate
369 and nvl (effective_end_date, sysdate + 1) > sysdate;
370
371 begin
372
373 -- Log the entering
374 JTF_DEBUG_PUB.LOG_ENTERING_METHOD (MODULE_NAME, METHOD_NAME);
375
376 -- Log parameters
377 if l_is_debug_parameter_on then
378 JTF_DEBUG_PUB.LOG_PARAMETERS (MODULE_NAME || '.' || METHOD_NAME,
379 'p_user_id=' || p_user_id);
380 JTF_DEBUG_PUB.LOG_PARAMETERS (MODULE_NAME || '.' || METHOD_NAME,
381 'p_username=' || p_username);
382 JTF_DEBUG_PUB.LOG_PARAMETERS (MODULE_NAME || '.' || METHOD_NAME,
383 'p_usertype_id=' || p_usertype_id);
384 JTF_DEBUG_PUB.LOG_PARAMETERS (MODULE_NAME || '.' || METHOD_NAME,
385 'p_enrollment_id=' || p_enrollment_id);
386 JTF_DEBUG_PUB.LOG_PARAMETERS (MODULE_NAME || '.' || METHOD_NAME,
387 'p_delegate_flag=' || p_delegate_flag);
388 JTF_DEBUG_PUB.LOG_PARAMETERS (MODULE_NAME || '.' || METHOD_NAME,
389 'p_approval_id=' || p_approval_id);
390
391 end if;
392 -- get the workflow itemtype from jtf_um_approvals_b table
393 if (p_approval_id is null) then
394 -- no approval required
395 l_wf_item_type := '';
396 else
397 -- approval required
398 open get_wf_item_type;
399 fetch get_wf_item_type into l_wf_item_type;
400 if (get_wf_item_type%notfound) then
401 JTF_DEBUG_PUB.LOG_EXCEPTION (MODULE_NAME || '.' || METHOD_NAME, 'Workflow itemtype is missing for approval id ' || p_approval_id);
402 close get_wf_item_type;
403 raise_application_error (-20000, 'Workflow itemtype is missing for approval id ' || p_approval_id);
404 end if;
405 close get_wf_item_type;
406 end if;
407
408 -- insert into the jtf_um_subscription_reg table
409 JTF_UM_SUBSCRIPTIONS_PKG.INSERT_SUBREG_ROW (
410 X_SUBSCRIPTION_ID => p_enrollment_id,
411 X_LAST_APPROVER_COMMENT => null,
412 X_APPROVER_USER_ID => null,
413 X_EFFECTIVE_END_DATE => null,
414 X_WF_ITEM_TYPE => l_wf_item_type,
415 X_EFFECTIVE_START_DATE => SYSDATE,
416 X_SUBSCRIPTION_REG_ID => x_enrollment_reg_id,
417 X_USER_ID => p_user_id,
418 X_STATUS_CODE => 'PENDING',
419 X_CREATION_DATE => SYSDATE,
420 X_CREATED_BY => FND_GLOBAL.USER_ID,
421 X_LAST_UPDATE_DATE => SYSDATE,
422 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
423 X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
424 X_GRANT_DELEGATION_FLAG => p_delegate_flag);
425
426 open get_user_usertype_status;
427 fetch get_user_usertype_status into l_usertype_status;
428 if (get_user_usertype_status%notfound) then
429 JTF_DEBUG_PUB.LOG_EXCEPTION (MODULE_NAME || '.' || METHOD_NAME, 'Usertype status is missing for ' || p_username);
430 close get_user_usertype_status;
431 raise_application_error (-20000, 'Usertype status is missing for ' || p_username);
432 end if;
433 close get_user_usertype_status;
434
435 if (p_approval_id is null) then
436
437 -- no approval required, assignEnrollCredentials
438 if (l_usertype_status = 'APPROVED' or l_usertype_status = 'UPGRADE') then
439 -- usertype doesn't need approval
440 ASSIGN_ENROLLMENT_CREDENTIALS (X_USER_NAME => p_username,
441 X_USER_ID => p_user_id,
442 X_SUBSCRIPTION_ID => p_enrollment_id);
443 end if;
444
445 else
446
447 -- approval required, launch workflow.
448 JTF_UM_WF_APPROVAL.CREATEPROCESS (requestType => 'ENROLLMENT',
449 requestID => p_enrollment_id,
450 requesterUserID => p_user_id,
451 requestRegID => x_enrollment_reg_id);
452
453 if (l_usertype_status = 'APPROVED' or l_usertype_status = 'UPGRADE') then
454 -- usertype doesn't need approval
455 JTF_UM_WF_APPROVAL.launchProcess (requestType => 'ENROLLMENT',
456 requestRegID => x_enrollment_reg_id);
457 end if;
458 end if;
459
460 JTF_DEBUG_PUB.LOG_EXITING_METHOD (MODULE_NAME, METHOD_NAME);
461
462 end assign_user_enrollment;
463
464 END JTF_UM_ENROLLMENT_CREDENTIALS;