[Home] [Help]
PACKAGE BODY: APPS.PV_USER_MGMT_PVT
Source
1 PACKAGE BODY PV_USER_MGMT_PVT as
2 /* $Header: pvxvummb.pls 120.18 2006/05/24 22:07:06 dgottlie ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PV_USER_MGMT_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvummb.pls';
6
7 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
8 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
9 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
10
11
12 PROCEDURE create_user_resource
13 (
14 p_api_version_number IN NUMBER
15 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
16 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
17 ,p_partner_user_rec IN partner_user_rec_type
18 ,x_return_status OUT NOCOPY VARCHAR2
19 ,x_msg_count OUT NOCOPY NUMBER
20 ,x_msg_data OUT NOCOPY VARCHAR2
21 )
22 IS
23 l_api_version_number CONSTANT NUMBER := 1.0;
24 l_api_name CONSTANT VARCHAR2(30) := 'create_user_resource';
25 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
26
27 l_person_rel_party_id NUMBER;
28 l_resource_id NUMBER;
29 l_resource_number VARCHAR2(30);
30 l_partner_id NUMBER;
31 l_partner_group_id NUMBER;
32 l_person_first_name VARCHAR2(150);
33 l_person_last_name VARCHAR2(150);
34 l_org_party_name VARCHAR2(360);
35 l_org_contact_id NUMBER;
36 l_role_relate_id NUMBER;
37 l_group_member_id NUMBER;
38 l_profile_saved boolean;
39 l_user_id NUMBER;
40 l_object_version_number NUMBER;
41
42 l_resource_exists boolean := false;
43 l_role_exists boolean := false;
44 l_group_assigned boolean := false;
45 l_group_role_assigned boolean := false;
46
47 resourceRec PVX_Misc_PVT.admin_rec_type;
48
49
50 cursor c_get_user_details(cv_person_rel_party_id NUMBER) IS
51 select pvpp.partner_id, pvpp.PARTNER_GROUP_ID , person_hzp.PERSON_FIRST_NAME, person_hzp.person_last_name, hzoc.org_contact_id, org_hzp.party_name
52 from HZ_PARTIES PERSON_HZP, HZ_RELATIONSHIPS HZR, PV_PARTNER_PROFILES pvpp, hz_org_contacts hzoc, HZ_PARTIES ORG_HZP
53 where HZR.party_id = cv_person_rel_party_id
54 and HZR.directional_flag = 'F'
55 and hzr.relationship_code = 'EMPLOYEE_OF'
56 and HZR.subject_table_name ='HZ_PARTIES'
57 and HZR.object_table_name ='HZ_PARTIES'
58 and hzr.start_date <= SYSDATE
59 and (hzr.end_date is null or hzr.end_date > SYSDATE)
60 and hzr.status = 'A'
61 and hzr.subject_id = person_hzp.party_id
62 and person_hzp.status = 'A'
63 and hzr.object_id = pvpp.partner_party_id
64 and pvpp.status = 'A'
65 and pvpp.partner_group_id is not null
66 and hzoc.PARTY_RELATIONSHIP_ID = hzr.relationship_id
67 and hzr.object_id = org_hzp.party_id;
68
69
70
71 cursor c_get_resource_id (cv_person_rel_party_id NUMBER)is
72 select resource_id, resource_number, user_id, object_version_number
73 from jtf_rs_resource_extns
74 where source_id = cv_person_rel_party_id
75 and category='PARTY'
76 and start_date_active <= sysdate
77 and (end_date_active is null or end_date_active >= sysdate);
78
79
80 cursor c_get_role_relate_id(cv_resource_id NUMBER) is
81 select role_relate_id
82 from jtf_rs_role_relations rr, jtf_rs_roles_b rrb
83 where role_resource_id = cv_resource_id
84 and role_resource_type = 'RS_INDIVIDUAL'
85 and rr.start_date_active <= sysdate
86 and (rr.end_date_active is null or rr.end_date_active >=sysdate)
87 and rrb.role_id = rr.role_id
88 and rrb.role_code= 'PARTNER_CONTACT_MEMBER'
89 and (rr.delete_flag is null or rr.delete_flag='N')
90 and (rr.active_flag is null or rr.active_flag = 'Y')
91 and (rrb.active_flag = 'Y');
92
93 cursor c_get_group_member_id (cv_resource_id NUMBER, cv_partner_group_id NUMBER) is
94 select group_member_id
95 from jtf_rs_group_members
96 where group_id = cv_partner_group_id
97 and resource_id = cv_resource_id
98 and (delete_flag is null or delete_flag = 'N');
99
100 cursor c_get_group_role_assigned (cv_group_member_id NUMBER) is
101 select role_relate_id
102 from jtf_rs_role_relations rr, jtf_rs_roles_b rrb
103 where role_resource_id = cv_group_member_id
104 and role_resource_type = 'RS_GROUP_MEMBER'
105 and rr.start_date_active <= sysdate
106 and (rr.end_date_active is null or rr.end_date_active >=sysdate)
107 and rrb.role_id = rr.role_id
108 and rrb.role_code= 'PARTNER_CONTACT_MEMBER'
109 and (rr.delete_flag is null or rr.delete_flag='N')
110 and (rr.active_flag is null or rr.active_flag = 'Y')
111 and (rrb.active_flag = 'Y');
112
113 BEGIN
114
115 ---------------Initialize --------------------
116 -- Standard Start of API savepoint
117 SAVEPOINT create_user_resource;
118
119 -- Initialize message list if p_init_msg_list is set to TRUE.
120 IF FND_API.to_Boolean( p_init_msg_list )
121 THEN
122 FND_MSG_PUB.initialize;
123 END IF;
124
125 -- Standard call to check for call compatibility.
126 IF NOT FND_API.Compatible_API_Call (
127 l_api_version_number
128 ,p_api_version_number
129 ,l_api_name
130 ,G_PKG_NAME
131 )
132 THEN
133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134 END IF;
135
136 -- Debug Message
137 IF (PV_DEBUG_HIGH_ON) THEN
138 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - start');
139 END IF;
140
141
142 -- Initialize API return status to SUCCESS
143 x_return_status := FND_API.G_RET_STS_SUCCESS;
144 -------------End Of Initialize -------------------------------
145
146
147 IF (p_partner_user_rec.person_rel_party_id IS NULL or p_partner_user_rec.person_rel_party_id = FND_API.G_MISS_NUM) THEN
148 fnd_message.SET_NAME ('PV', 'PV_MISSING_PERSON_REL_ID');
149 fnd_msg_pub.ADD;
150 raise FND_API.G_EXC_ERROR;
151 END IF;
152
153 open c_get_user_details(p_partner_user_rec.person_rel_party_id);
154 fetch c_get_user_details into l_partner_id, l_partner_group_id, l_person_first_name, l_person_last_name,l_org_contact_id, l_org_party_name;
155 if (c_get_user_details%NOTFOUND) THEN
156 fnd_message.SET_NAME ('PV', 'PV_INVALID_PARTNER');
157 fnd_msg_pub.ADD;
158 raise FND_API.G_EXC_ERROR;
159 END IF;
160 close c_get_user_details;
161
162 l_person_rel_party_id := p_partner_user_rec.person_rel_party_id;
163 IF (PV_DEBUG_HIGH_ON) THEN
164 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - l_person_rel_party_id =' || to_char(l_person_rel_party_id));
165 END IF;
166
167
168 --get resource id into l_resource_id number
169 open c_get_resource_id( l_person_rel_party_id);
170 fetch c_get_resource_id into l_resource_id, l_resource_number, l_user_id, l_object_version_number;
171 if (c_get_resource_id%FOUND) then
172 l_resource_exists := true;
173 IF (PV_DEBUG_HIGH_ON) THEN
174 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - l_resource_exists := true');
175 END IF;
176
177 end if;
178 close c_get_resource_id;
179
180
181 if (l_resource_exists = true) then
182 jtf_rs_resource_pub.update_resource (
183 P_API_VERSION => p_api_version_number,
184 P_INIT_MSG_LIST => fnd_api.g_false,
185 P_COMMIT => fnd_api.g_false,
186 P_RESOURCE_ID => l_resource_id,
187 P_RESOURCE_NUMBER => l_resource_number,
188 P_USER_ID => p_partner_user_rec.user_ID,
189 P_SOURCE_NAME => FND_API.G_MISS_CHAR,
190 P_OBJECT_VERSION_NUM => l_object_version_number,
191 P_USER_NAME => p_partner_user_rec.user_name,
192 X_RETURN_STATUS => x_return_status,
193 X_MSG_COUNT => x_msg_count,
194 X_MSG_DATA => x_msg_data
195 );
196 end if;
197
198
199
200 if (l_resource_exists = false) then
201 IF (PV_DEBUG_HIGH_ON) THEN
202 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - l_resource_exists := false');
203 END IF;
204 resourceRec.source_last_name := l_person_last_name;
205 resourceRec.source_first_name := l_person_first_name;
206 resourceRec.user_name := upper(p_partner_user_rec.user_Name);
207 resourceRec.user_id := p_partner_user_rec.user_ID;
208 resourceRec.resource_type := 'PARTY';
209 resourceRec.partner_id := p_partner_user_rec.person_rel_party_id;
210 resourceRec.contact_id := l_org_contact_id;
211 resourceRec.source_name := l_person_first_name || ' ' || l_person_last_name;
212 resourceRec.resource_name := resourceRec.source_name;
213 resourceRec.source_org_id := l_partner_id;
214 resourceRec.source_org_name := l_org_party_name;
215 --PVX_UTILITY_PVT.debug_message('admin resource');
216 PVX_Misc_PVT.Admin_Resource
217 (
218 p_api_version => p_api_version_number
219 ,p_init_msg_list => FND_API.g_false
220 ,p_commit => FND_API.g_false
221 ,x_return_status => x_return_status
222 ,x_msg_count => x_msg_count
223 ,x_msg_data => x_msg_data
224 ,p_admin_rec => resourceRec
225 ,p_mode => 'CREATE'
226 ,x_resource_id => l_resource_id
227 ,x_resource_number => l_resource_number
228 );
229
230 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
232 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
233 RAISE FND_API.G_EXC_ERROR;
234 END IF;
235 end if;
236
237 if (l_resource_exists = true) then
238 -- fetch role into l_role_relate_id
239 open c_get_role_relate_id(l_resource_id);
240 fetch c_get_role_relate_id into l_role_relate_id;
241 if (c_get_role_relate_id%FOUND) then
242 l_role_exists := true;
243 IF (PV_DEBUG_HIGH_ON) THEN
244 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - l_role_exists := true');
245 END IF;
246 end if;
247 close c_get_role_relate_id;
248 end if;
249
250
251
252 if (l_role_exists = false) then
253 IF (PV_DEBUG_HIGH_ON) THEN
254 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - l_role_exists := false');
255 END IF;
256 resourceRec.role_resource_id := l_resource_id;
257 resourceRec.role_resource_type := 'RS_INDIVIDUAL';
258 resourceRec.role_code := 'PARTNER_CONTACT_MEMBER';
259 --PVX_UTILITY_PVT.debug_message('admin role for individual');
260 PVX_Misc_PVT.Admin_Role
261 (
262 p_api_version => p_api_version_number
263 ,p_init_msg_list => FND_API.g_false
264 ,p_commit => FND_API.g_false
265 ,x_return_status => x_return_status
266 ,x_msg_count => x_msg_count
267 ,x_msg_data => x_msg_data
268 ,p_admin_rec => resourceRec
269 ,p_mode => 'CREATE'
270 ,x_role_relate_id => l_role_relate_id
271 );
272
273 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
275 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
276 RAISE FND_API.G_EXC_ERROR;
277 END IF;
278 end if;
279
280
281 if (l_resource_exists = true) then
282 -- fetch check group into l_group_member_id
283 open c_get_group_member_id(l_resource_id, l_partner_group_id);
284 fetch c_get_group_member_id into l_group_member_id;
285 if (c_get_group_member_id%FOUND) then
286 l_group_assigned := true;
287 IF (PV_DEBUG_HIGH_ON) THEN
288 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - l_group_assigned := true');
289 END IF;
290 end if;
291 close c_get_group_member_id;
292 end if;
293
294
295
296 if (l_group_assigned = false) then
297 IF (PV_DEBUG_HIGH_ON) THEN
298 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - l_group_assigned := false');
299 END IF;
300 resourceRec.role_resource_id := l_resource_id;
301 resourceRec.resource_number := l_resource_number;
302 resourceRec.group_id := l_partner_group_id;
303
304 --PVX_UTILITY_PVT.debug_message('admin group member');
305 PVX_Misc_PVT.Admin_Group_Member
306 (
307 p_api_version => p_api_version_number
308 ,p_init_msg_list => FND_API.g_false
309 ,p_commit => FND_API.g_false
310 ,x_return_status => x_return_status
311 ,x_msg_count => x_msg_count
312 ,x_msg_data => x_msg_data
313 ,p_admin_rec => resourceRec
314 ,p_mode => 'CREATE'
315 ,x_group_member_id => l_group_member_id
316 );
317
318 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
319 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
320 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
321 RAISE FND_API.G_EXC_ERROR;
322 END IF;
323 end if;
324
325 --PVX_UTILITY_PVT.debug_message('l_group_member_id =' || to_char(l_group_member_id));
326
327
328 if (l_resource_exists = true and l_group_assigned = true) then
329 open c_get_group_role_assigned (l_group_member_id) ;
330 fetch c_get_group_role_assigned into l_role_relate_id;
331 if (c_get_group_role_assigned%FOUND) then
332 l_group_role_assigned := true;
333 IF (PV_DEBUG_HIGH_ON) THEN
334 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - l_group_role_assigned := true');
335 END IF;
336 end if;
337 close c_get_group_role_assigned;
338 end if;
339
340
341
342 if (l_group_role_assigned = false) then
343 IF (PV_DEBUG_HIGH_ON) THEN
344 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - l_group_role_assigned := false');
345 END IF;
346 resourceRec.role_resource_id := l_group_member_id;
347 resourceRec.role_resource_type := 'RS_GROUP_MEMBER';
348 resourceRec.role_code := 'PARTNER_CONTACT_MEMBER';
349
350 --PVX_UTILITY_PVT.debug_message('admin role for group');
351 PVX_Misc_PVT.Admin_Role
352 (
353 p_api_version => p_api_version_number
354 ,p_init_msg_list => FND_API.g_false
355 ,p_commit => FND_API.g_false
356 ,x_return_status => x_return_status
357 ,x_msg_count => x_msg_count
358 ,x_msg_data => x_msg_data
359 ,p_admin_rec => resourceRec
360 ,p_mode => 'CREATE'
361 ,x_role_relate_id => l_role_relate_id
362 );
363
364 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
366 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
367 RAISE FND_API.G_EXC_ERROR;
368 END IF;
369 end if;
370 l_profile_saved := fnd_profile.save
371 (X_NAME => 'ASF_DEFAULT_GROUP_ROLE',
372 X_VALUE => l_partner_group_id||'(Member)',
373 X_LEVEL_NAME => 'USER',
374 X_LEVEL_VALUE => p_partner_user_rec.user_ID);
375
376 If (not l_profile_saved) THEN
377 fnd_message.SET_NAME ('PV', 'PV_PROFILE_NOT_EXISTS');
378 fnd_msg_pub.ADD;
379 raise FND_API.G_EXC_ERROR;
380 END IF;
381
382
383
384 FND_MSG_PUB.Count_And_Get
385 ( p_encoded => FND_API.G_FALSE,
386 p_count => x_msg_count,
387 p_data => x_msg_data
388 );
389
390 IF FND_API.to_Boolean( p_commit ) THEN
391 COMMIT WORK;
392 END IF;
393
394 EXCEPTION
395 WHEN FND_API.G_EXC_ERROR THEN
396 ROLLBACK TO create_user_resource;
397 x_return_status := FND_API.G_RET_STS_ERROR;
398 FND_MSG_PUB.Count_And_Get (
399 p_encoded => FND_API.G_FALSE
400 ,p_count => x_msg_count
401 ,p_data => x_msg_data
402 );
403
404
405 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
406 ROLLBACK TO create_user_resource;
407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
408 -- Standard call to get message count and if count=1, get the message
409 FND_MSG_PUB.Count_And_Get (
410 p_encoded => FND_API.G_FALSE
411 ,p_count => x_msg_count
412 ,p_data => x_msg_data
413 );
414
415 WHEN OTHERS THEN
416 ROLLBACK TO create_user_resource;
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
418 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
419 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
420 END IF;
421 FND_MSG_PUB.Count_And_Get (
422 p_encoded => FND_API.G_FALSE
423 ,p_count => x_msg_count
424 ,p_data => x_msg_data
425 );
426
427 END create_user_resource;
428
429 PROCEDURE register_partner_and_user
430 (
431 p_api_version_number IN NUMBER
432 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
433 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
434 ,p_partner_rec IN Partner_Rec_type
435 ,P_partner_type IN VARCHAR2
436 ,p_partner_user_rec IN partner_user_rec_type
437 ,x_return_status OUT NOCOPY VARCHAR2
438 ,x_msg_count OUT NOCOPY NUMBER
439 ,x_msg_data OUT NOCOPY VARCHAR2
440 )
441 IS
442
443 l_api_version_number CONSTANT NUMBER := 1.0;
444 l_api_name CONSTANT VARCHAR2(30) := 'Register_Partner_And_User';
445 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
446
447
448 l_partner_id NUMBER;
449 l_default_resp_id NUMBER;
450 l_resp_map_rule_id NUMBER;
451 l_partner_group_id NUMBER;
452 l_global_partner_id NUMBER := null;
453
454 l_partner_types_tbl PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type;
455
456 cursor get_user_role(l_user_type_id NUMBER) IS
457 select jtfperm.permission_name
458 from jtf_auth_principals_b jtfp1, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
459 where jtfp1.is_user_flag = 0
460 and jtfp1.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
461 and jtfrp.positive_flag = 1
462 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
463 and jtfperm.permission_name in (G_PARTNER_PERMISSION, G_PRIMARY_PERMISSION)
464 and jtfp1.principal_name IN
465 (select principal_name
466 from jtf_um_usertype_role jtur
467 where jtur.usertype_id = l_user_type_id
468 and (jtur.effective_end_date is null or jtur.effective_end_date > sysdate)
469 union all
470 select jtsr.principal_name
471 from jtf_um_usertype_subscrip jtus, jtf_um_subscription_role jtsr
472 where jtus.usertype_id = l_user_type_id
473 and (jtus.effective_end_date is null or jtus.effective_end_date > sysdate)
474 and jtus.subscription_flag = 'IMPLICIT'
475 and jtus.subscription_id = jtsr.subscription_id
476 and (jtsr.effective_end_date is null or jtsr.effective_end_date > sysdate)
477 )
478 group by jtfperm.permission_name;
479
480 is_partner_user boolean := false;
481 is_primary_user boolean := false;
482 l_role varchar2(10);
483
484 BEGIN
485
486 ---------------Initialize --------------------
487 -- Standard Start of API savepoint
488 SAVEPOINT Register_Partner_And_User;
489
490 -- Initialize message list if p_init_msg_list is set to TRUE.
491 IF FND_API.to_Boolean( p_init_msg_list )
492 THEN
493 FND_MSG_PUB.initialize;
494 END IF;
495
496 -- Standard call to check for call compatibility.
497 IF NOT FND_API.Compatible_API_Call (
498 l_api_version_number
499 ,p_api_version_number
500 ,l_api_name
501 ,G_PKG_NAME
502 )
503 THEN
504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
505 END IF;
506
507 -- Debug Message
508 IF (PV_DEBUG_HIGH_ON) THEN
509 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - start');
510 END IF;
511
512
513 -- Initialize API return status to SUCCESS
514 x_return_status := FND_API.G_RET_STS_SUCCESS;
515 -------------End Of Initialize -------------------------------
516
517 IF FND_GLOBAL.User_Id IS NULL THEN
518 FND_MESSAGE.set_name('PV', 'PV_API_USER_PROFILE_MISSING');
519 FND_MSG_PUB.add;
520 RAISE FND_API.G_EXC_ERROR;
521 END IF;
522
523
524 IF P_partner_type IS NULL THEN
525 fnd_message.SET_NAME ('PV', 'PV_MISSING_PRTNR_TYPE');
526 fnd_msg_pub.ADD;
527 raise FND_API.G_EXC_ERROR;
528 END IF;
529
530 IF (p_partner_rec.partner_party_id IS NULL or p_partner_rec.partner_party_id = FND_API.G_MISS_NUM) THEN
531 fnd_message.SET_NAME ('PV', 'PV_MISSING_ORGZN_ID');
532 fnd_msg_pub.ADD;
533 raise FND_API.G_EXC_ERROR;
534 END IF;
535
536 IF (p_partner_rec.member_type IS NULL or p_partner_rec.member_type = FND_API.G_MISS_CHAR) THEN
537 fnd_message.SET_NAME ('PV', 'PV_MISSING_MEMBER_TYPE');
538 fnd_msg_pub.ADD;
539 raise FND_API.G_EXC_ERROR;
540 END IF;
541
542 IF (p_partner_user_rec.user_id IS NULL or p_partner_user_rec.user_id = FND_API.G_MISS_NUM) THEN
543 fnd_message.SET_NAME ('PV', 'PV_MISSING_USER_ID');
544 fnd_msg_pub.ADD;
545 raise FND_API.G_EXC_ERROR;
546 END IF;
547
548
549 if(p_partner_rec.member_type = 'SUBSIDIARY') THEN
550 IF(p_partner_rec.global_prtnr_org_number IS NULL OR p_partner_rec.global_prtnr_org_number = FND_API.G_MISS_CHAR) THEN
551 fnd_message.SET_NAME ('PV', 'PV_MISSING_GLOBAL_ID');
552 fnd_msg_pub.ADD;
553 raise FND_API.G_EXC_ERROR;
554 ELSE
555 l_global_partner_id := Pv_ptr_member_type_pvt.get_global_partner_id(p_global_prtnr_org_number => p_partner_rec.global_prtnr_org_number);
556 END IF;
557 END IF;
558
559 IF (p_partner_user_rec.user_type_id IS NULL or p_partner_user_rec.user_type_id = FND_API.G_MISS_NUM) THEN
560 fnd_message.SET_NAME ('PV', 'PV_MISSING_USERTYPE_ID');
561 fnd_msg_pub.ADD;
562 raise FND_API.G_EXC_ERROR;
563 END IF;
564
565
566 FOR X in get_user_role(p_partner_user_rec.user_type_id) LOOP
567 IF (X.permission_Name = G_PARTNER_PERMISSION) THEN
568 is_partner_user := true;
569 ELSIF (X.permission_name = G_PRIMARY_PERMISSION) THEN
570 is_primary_user := true;
571 END IF;
572 END LOOP;
573
574 IF((not is_partner_user) or (not is_primary_user)) THEN
575 fnd_message.SET_NAME ('PV', 'PV_NOT_PRTNR_PRIMARY_USER');
576 fnd_msg_pub.ADD;
577 raise FND_API.G_EXC_ERROR;
578 END IF;
579
580 l_partner_types_tbl(1).attr_value := P_partner_type;
581 l_partner_types_tbl(1).attr_value_extn := 'Y' ;
582
583 pv_partner_util_pvt.Create_Relationship
584 (
585 p_api_version_number => p_api_version_number
586 ,p_init_msg_list => FND_API.g_false
587 ,p_commit => FND_API.G_FALSE
588 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
589 ,x_return_status => x_return_status
590 ,x_msg_data => x_msg_data
591 ,x_msg_count => x_msg_count
592 ,p_party_id => p_partner_rec.partner_party_id
593 ,p_partner_types_tbl => l_partner_types_tbl
594 ,p_vad_partner_id => NULL
595 ,p_member_type => p_partner_rec.member_type
596 ,p_global_partner_id => l_global_partner_id
597 ,x_partner_id => l_partner_id
598 ,x_default_resp_id => l_default_resp_id
599 ,x_resp_map_rule_id => l_resp_map_rule_id
600 ,x_group_id => l_partner_group_id
601 );
602
603 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
604 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
605 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
606 RAISE FND_API.G_EXC_ERROR;
607 END IF;
608
609
610
611 FND_MSG_PUB.Count_And_Get
612 ( p_encoded => FND_API.G_FALSE,
613 p_count => x_msg_count,
614 p_data => x_msg_data
615 );
616
617 IF FND_API.to_Boolean( p_commit ) THEN
618 COMMIT WORK;
619 END IF;
620
621 EXCEPTION
622 WHEN FND_API.G_EXC_ERROR THEN
623 ROLLBACK TO Register_Partner_And_User;
624 x_return_status := FND_API.G_RET_STS_ERROR;
625 FND_MSG_PUB.Count_And_Get (
626 p_encoded => FND_API.G_FALSE
627 ,p_count => x_msg_count
628 ,p_data => x_msg_data
629 );
630
631
632 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
633 ROLLBACK TO Register_Partner_And_User;
634 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
635 -- Standard call to get message count and if count=1, get the message
636 FND_MSG_PUB.Count_And_Get (
637 p_encoded => FND_API.G_FALSE
638 ,p_count => x_msg_count
639 ,p_data => x_msg_data
640 );
641
642 WHEN OTHERS THEN
643 ROLLBACK TO Register_Partner_And_User;
644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
645 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
646 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
647 END IF;
648 FND_MSG_PUB.Count_And_Get (
649 p_encoded => FND_API.G_FALSE
650 ,p_count => x_msg_count
651 ,p_data => x_msg_data
652 );
653
654 END register_partner_and_user;
655
656
657 PROCEDURE register_partner_user
658 (
659 p_api_version_number IN NUMBER
660 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
661 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
662 ,p_partner_user_rec IN partner_User_rec_type
663 ,x_return_status OUT NOCOPY VARCHAR2
664 ,x_msg_count OUT NOCOPY NUMBER
665 ,x_msg_data OUT NOCOPY VARCHAR2
666 )
667 IS
668
669 l_api_version_number CONSTANT NUMBER := 1.0;
670 l_api_name CONSTANT VARCHAR2(30) := 'register_partner_user';
671 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
672
673
674 cursor get_user_role(l_user_type_id NUMBER) IS
675 select jtfperm.permission_name
676 from jtf_auth_principals_b jtfp1, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
677 where jtfp1.is_user_flag = 0
678 and jtfp1.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
679 and jtfrp.positive_flag = 1
680 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
681 and jtfperm.permission_name in (G_PARTNER_PERMISSION, G_PRIMARY_PERMISSION)
682 and jtfp1.principal_name IN
683 (select principal_name
684 from jtf_um_usertype_role jtur
685 where jtur.usertype_id = l_user_type_id
686 and (jtur.effective_end_date is null or jtur.effective_end_date > sysdate)
687 union all
688 select jtsr.principal_name
689 from jtf_um_usertype_subscrip jtus, jtf_um_subscription_role jtsr
690 where jtus.usertype_id = l_user_type_id
691 and (jtus.effective_end_date is null or jtus.effective_end_date > sysdate)
692 and jtus.subscription_flag = 'IMPLICIT'
693 and jtus.subscription_id = jtsr.subscription_id
694 and (jtsr.effective_end_date is null or jtsr.effective_end_date > sysdate)
695 )
696 group by jtfperm.permission_name;
697
698 is_partner_user boolean := false;
699 is_primary_user boolean := false;
700 l_role varchar2(10);
701
702
703 BEGIN
704
705 ---------------Initialize --------------------
706 -- Standard Start of API savepoint
707 SAVEPOINT register_partner_user;
708
709 -- Initialize message list if p_init_msg_list is set to TRUE.
710 IF FND_API.to_Boolean( p_init_msg_list )
711 THEN
712 FND_MSG_PUB.initialize;
713 END IF;
714
715 -- Standard call to check for call compatibility.
716 IF NOT FND_API.Compatible_API_Call (
717 l_api_version_number
718 ,p_api_version_number
719 ,l_api_name
720 ,G_PKG_NAME
721 )
722 THEN
723 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
724 END IF;
725
726 -- Debug Message
727 IF (PV_DEBUG_HIGH_ON) THEN
728
729 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - start');
730 END IF;
731
732
733 -- Initialize API return status to SUCCESS
734 x_return_status := FND_API.G_RET_STS_SUCCESS;
735 -------------End Of Initialize -------------------------------
736
737
738 IF FND_GLOBAL.User_Id IS NULL THEN
739 FND_MESSAGE.set_name('PV', 'PV_API_USER_PROFILE_MISSING');
740 FND_MSG_PUB.add;
741 RAISE FND_API.G_EXC_ERROR;
742 END IF;
743
744 IF (p_partner_user_rec.user_id IS NULL or p_partner_user_rec.user_id = FND_API.G_MISS_NUM) THEN
745 fnd_message.SET_NAME ('PV', 'PV_MISSING_USER_ID');
746 fnd_msg_pub.ADD;
747 raise FND_API.G_EXC_ERROR;
748 END IF;
749
750 IF (p_partner_user_rec.user_type_id IS NULL or p_partner_user_rec.user_type_id = FND_API.G_MISS_NUM) THEN
751 fnd_message.SET_NAME ('PV', 'PV_MISSING_USERTYPE_ID');
752 fnd_msg_pub.ADD;
753 raise FND_API.G_EXC_ERROR;
754 END IF;
755
756
757 FOR X in get_user_role(p_partner_user_rec.user_type_id) LOOP
758 IF (X.permission_Name = G_PARTNER_PERMISSION) THEN
759 is_partner_user := true;
760 ELSIF (X.permission_name = G_PRIMARY_PERMISSION) THEN
761 is_primary_user := true;
762 END IF;
763 END LOOP;
764
765 IF(not is_partner_user) THEN
766 fnd_message.SET_NAME ('PV', 'PV_NOT_PRTNR_USER');
767 fnd_msg_pub.ADD;
768 raise FND_API.G_EXC_ERROR;
769 END IF;
770
771 create_user_resource(
772 p_api_version_number => p_api_version_number
773 ,p_init_msg_list => FND_API.g_false
774 ,p_commit => FND_API.G_FALSE
775 ,p_partner_user_rec => p_partner_user_rec
776 ,x_return_status => x_return_status
777 ,x_msg_count => x_msg_count
778 ,x_msg_data => x_msg_data
779 );
780
781
782 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
783 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
784 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
785 RAISE FND_API.G_EXC_ERROR;
786 END IF;
787
788 IF (is_primary_user) THEN
789 l_role := pv_user_Resp_pvt.G_PRIMARY;
790 ELSE
791 l_role := pv_user_Resp_pvt.G_BUSINESS;
792 END IF;
793
794 pv_user_Resp_pvt.assign_user_resps(
795 p_api_version_number => p_api_version_number
796 ,p_init_msg_list => FND_API.g_false
797 ,p_commit => FND_API.G_FALSE
798 ,x_return_status => x_return_status
799 ,x_msg_count => x_msg_count
800 ,x_msg_data => x_msg_data
801 ,p_user_id => p_partner_user_rec.user_id
802 ,p_user_role_code => l_role
803 );
804
805 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
807 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
808 RAISE FND_API.G_EXC_ERROR;
809 END IF;
810
811
812 FND_MSG_PUB.Count_And_Get
813 ( p_encoded => FND_API.G_FALSE,
814 p_count => x_msg_count,
815 p_data => x_msg_data
816 );
817
818 IF FND_API.to_Boolean( p_commit ) THEN
819 COMMIT WORK;
820 END IF;
821
822 EXCEPTION
823 WHEN FND_API.G_EXC_ERROR THEN
824 ROLLBACK TO register_partner_user;
825 x_return_status := FND_API.G_RET_STS_ERROR;
826 FND_MSG_PUB.Count_And_Get (
827 p_encoded => FND_API.G_FALSE
828 ,p_count => x_msg_count
829 ,p_data => x_msg_data
830 );
831
832
833 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
834 ROLLBACK TO register_partner_user;
835 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
836 -- Standard call to get message count and if count=1, get the message
837 FND_MSG_PUB.Count_And_Get (
838 p_encoded => FND_API.G_FALSE
839 ,p_count => x_msg_count
840 ,p_data => x_msg_data
841 );
842
843 WHEN OTHERS THEN
844 ROLLBACK TO register_partner_user;
845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
846 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
847 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
848 END IF;
849 FND_MSG_PUB.Count_And_Get (
850 p_encoded => FND_API.G_FALSE
851 ,p_count => x_msg_count
852 ,p_data => x_msg_data
853 );
854
855 END register_partner_user;
856
857
858 PROCEDURE revoke_role
859 (
860 p_api_version_number IN NUMBER
861 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
862 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
863 ,p_role_name IN JTF_VARCHAR2_TABLE_1000
864 ,p_user_name IN VARCHAR2
865 ,x_return_status OUT NOCOPY VARCHAR2
866 ,x_msg_count OUT NOCOPY NUMBER
867 ,x_msg_data OUT NOCOPY VARCHAR2
868 )
869 IS
870
871 l_api_version_number CONSTANT NUMBER := 1.0;
872 l_api_name CONSTANT VARCHAR2(30) := 'revoke_role';
873 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
874
875
876 cursor check_curr_role_perms(l_role_name JTF_VARCHAR2_TABLE_1000) IS
877 select jtfperm.permission_name , jtfp1.jtf_auth_principal_id
878 from jtf_auth_principals_b jtfp1, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
879 where jtfp1.is_user_flag = 0
880 and jtfp1.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
881 and jtfrp.positive_flag = 1
882 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
883 and jtfperm.permission_name in (G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
884 and jtfp1.principal_name IN (Select * from table (CAST(l_role_name AS JTF_VARCHAR2_TABLE_1000)));
885
886
887 cursor check_curr_user_perms(l_role_id JTF_NUMBER_TABLE, l_user_name varchar2) IS
888 Select jtfperm.permission_name
889 FROM jtf_auth_principal_maps jtfpm, jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd, jtf_auth_role_perms jtfrp,
890 jtf_auth_permissions_b jtfperm
891 where jtfp1.principal_name = l_user_name
892 and jtfp1.is_user_flag=1
893 and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
894 and jtfrp.jtf_auth_principal_id = jtfpm.jtf_auth_parent_principal_id
895 and jtfrp.positive_flag = 1
896 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
897 and jtfperm.permission_name in ( G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
898 and jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
899 and jtfd.domain_name='CRM_DOMAIN'
900 and jtfpm.jtf_auth_parent_principal_id NOT IN (Select * from table (CAST(l_role_id AS JTF_NUMBER_TABLE)))
901 group by jtfperm.permission_name;
902
903 cursor get_user_id(l_user_name varchar2) IS
904 Select fndu.user_id
905 from fnd_user fndu
906 where fndu.user_name = l_user_name;
907
908
909
910 is_prtnr_perm_revoked boolean := false;
911 is_primary_perm_revoked boolean := false;
912 l_role_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
913 is_primary_user boolean := false;
914 is_partner_user boolean := false;
915 l_user_id NUMBER;
916 l_role varchar2(10);
917
918
919 BEGIN
920
921 ---------------Initialize --------------------
922 -- Standard Start of API savepoint
923 SAVEPOINT revoke_role;
924
925 -- Initialize message list if p_init_msg_list is set to TRUE.
926 IF FND_API.to_Boolean( p_init_msg_list )
927 THEN
928 FND_MSG_PUB.initialize;
929 END IF;
930
931 -- Standard call to check for call compatibility.
932 IF NOT FND_API.Compatible_API_Call (
933 l_api_version_number
934 ,p_api_version_number
935 ,l_api_name
936 ,G_PKG_NAME
937 )
938 THEN
939 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
940 END IF;
941
942 -- Debug Message
943 IF (PV_DEBUG_HIGH_ON) THEN
944
945 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - start');
946 END IF;
947
948
949 -- Initialize API return status to SUCCESS
950 x_return_status := FND_API.G_RET_STS_SUCCESS;
951 -------------End Of Initialize -------------------------------
952
953
954 IF FND_GLOBAL.User_Id IS NULL THEN
955 FND_MESSAGE.set_name('PV', 'PV_API_USER_PROFILE_MISSING');
956 FND_MSG_PUB.add;
957 RAISE FND_API.G_EXC_ERROR;
958 END IF;
959
960 IF (p_role_name.count < 1) THEN
961 fnd_message.SET_NAME ('PV', 'PV_MISSING_ROLE_NAME');
962 fnd_msg_pub.ADD;
963 raise FND_API.G_EXC_ERROR;
964 END IF;
965
966 IF (p_user_name IS NULL or p_user_name = FND_API.G_MISS_CHAR) THEN
967 fnd_message.SET_NAME ('PV', 'PV_MISSING_USER_NAME');
968 fnd_msg_pub.ADD;
969 raise FND_API.G_EXC_ERROR;
970 END IF;
971
972 /** Check if partner or primary permissions are part of the current role. IF NOT, we do not need to do any thing **/
973 FOR X in check_curr_role_perms(p_role_name) LOOP
974 l_role_ids.extend;
975 l_role_ids(l_role_ids.count) := x.jtf_auth_principal_id;
976 IF (x.permission_name = G_PARTNER_PERMISSION) THEN
977 is_primary_perm_revoked := true;
978 elsif (x.permission_name = G_PRIMARY_PERMISSION) THEN
979 is_prtnr_perm_revoked := true;
980 END IF;
981 END LOOP;
982
983 /** If partner or primary permissions are part of the current role **/
984 if(is_primary_perm_revoked or is_prtnr_perm_revoked) THEN
985
986 /** check if current user has either partner or primary persmissions assigned with out considering the role
987 that is being revoked **/
988 FOR X in check_curr_user_perms(l_role_ids, p_user_name) LOOP
989 IF(X.permission_name = G_PARTNER_PERMISSION) THEN
990 is_partner_user := true;
991 elsif (X.permission_name = G_PRIMARY_PERMISSION) THEN
992 is_primary_user := true;
993 end if;
994 END LOOP;
995
996
997 open get_user_id(p_user_name);
998 fetch get_user_id into l_user_id;
999 close get_user_id;
1000
1001
1002 /** If G_PARTNER_PERMISSION permission is revoked and user will become non partner user after the role is revoked **/
1003 If(NOT is_partner_user and is_prtnr_perm_revoked) THEN
1004
1005 if(is_primary_user or is_primary_perm_revoked) THEN
1006 l_role := PV_USER_RESP_PVT.G_PRIMARY;
1007 else
1008 l_role := PV_USER_RESP_PVT.G_BUSINESS;
1009 END IF;
1010
1011 PV_USER_RESP_PVT.revoke_user_resps(
1012 p_api_version_number => p_api_version_number
1013 ,p_init_msg_list => FND_API.g_false
1014 ,p_commit => FND_API.G_FALSE
1015 ,x_return_status => x_return_status
1016 ,x_msg_count => x_msg_count
1017 ,x_msg_data => x_msg_data
1018 ,p_user_id => l_user_id
1019 ,p_user_role_code => l_role
1020 );
1021
1022 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1023 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1024 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1025 RAISE FND_API.G_EXC_ERROR;
1026 END IF;
1027
1028 /** If partner user and primary user permission is revoked and user will become business user after this **/
1029 elsif (is_partner_user and (NOT is_primary_user) and is_primary_perm_revoked) THEN
1030 PV_USER_RESP_PVT.switch_user_resp(
1031 p_api_version_number => p_api_version_number
1032 ,p_init_msg_list => FND_API.g_false
1033 ,p_commit => FND_API.G_FALSE
1034 ,x_return_status => x_return_status
1035 ,x_msg_count => x_msg_count
1036 ,x_msg_data => x_msg_data
1037 ,p_user_id => l_user_id
1038 ,p_from_user_role_code => PV_USER_RESP_PVT.G_PRIMARY
1039 ,p_to_user_role_code => PV_USER_RESP_PVT.G_BUSINESS
1040 );
1041
1042 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1043 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1044 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1045 RAISE FND_API.G_EXC_ERROR;
1046 END IF;
1047
1048 END IF;
1049 end if;
1050
1051 FND_MSG_PUB.Count_And_Get
1052 ( p_encoded => FND_API.G_FALSE,
1053 p_count => x_msg_count,
1054 p_data => x_msg_data
1055 );
1056
1057 IF FND_API.to_Boolean( p_commit ) THEN
1058 COMMIT WORK;
1059 END IF;
1060
1061 EXCEPTION
1062 WHEN FND_API.G_EXC_ERROR THEN
1063 ROLLBACK TO revoke_role;
1064 x_return_status := FND_API.G_RET_STS_ERROR;
1065 FND_MSG_PUB.Count_And_Get (
1066 p_encoded => FND_API.G_FALSE
1067 ,p_count => x_msg_count
1068 ,p_data => x_msg_data
1069 );
1070
1071
1072 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1073 ROLLBACK TO revoke_role;
1074 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1075 -- Standard call to get message count and if count=1, get the message
1076 FND_MSG_PUB.Count_And_Get (
1077 p_encoded => FND_API.G_FALSE
1078 ,p_count => x_msg_count
1079 ,p_data => x_msg_data
1080 );
1081
1082 WHEN OTHERS THEN
1083 ROLLBACK TO revoke_role;
1084 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1085 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1086 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1087 END IF;
1088 FND_MSG_PUB.Count_And_Get (
1089 p_encoded => FND_API.G_FALSE
1090 ,p_count => x_msg_count
1091 ,p_data => x_msg_data
1092 );
1093
1094 END revoke_role;
1095
1096
1097 PROCEDURE delete_role
1098 (
1099 p_api_version_number IN NUMBER
1100 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1101 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1102 ,p_role_name IN JTF_VARCHAR2_TABLE_1000
1103 ,x_return_status OUT NOCOPY VARCHAR2
1104 ,x_msg_count OUT NOCOPY NUMBER
1105 ,x_msg_data OUT NOCOPY VARCHAR2
1106 )
1107 IS
1108
1109 l_api_version_number CONSTANT NUMBER := 1.0;
1110 l_api_name CONSTANT VARCHAR2(30) := 'delete_role';
1111 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1112
1113
1114 cursor check_curr_role_perms(cv_role_name JTF_VARCHAR2_TABLE_1000) IS
1115 select jtfperm.permission_name , jtfp1.jtf_auth_principal_id
1116 from jtf_auth_principals_b jtfp1, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
1117 where jtfp1.is_user_flag = 0
1118 and jtfp1.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
1119 and jtfrp.positive_flag = 1
1120 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
1121 and jtfperm.permission_name in (G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
1122 and jtfp1.principal_name IN (Select * from table (CAST(cv_role_name AS JTF_VARCHAR2_TABLE_1000)));
1123
1124
1125
1126 cursor get_users_w_curr_role(cv_role_id JTF_NUMBER_TABLE) IS
1127 select /*+ cardinality( t 10 ) */ jtfp2.principal_name, fndu.user_id
1128 from jtf_auth_principal_maps jtfpm, jtf_auth_principals_b jtfp2,jtf_auth_principals_b jtfp1,
1129 fnd_user fndu, jtf_rs_resource_extns jtfre, (Select column_value from table
1130 (CAST(cv_role_id AS JTF_NUMBER_TABLE))) t
1131 where jtfp1.jtf_auth_principal_id = t.column_value
1132 and jtfp1.is_user_flag = 0
1133 and jtfp1.jtf_auth_principal_id = jtfpm.jtf_auth_parent_principal_id
1134 and jtfpm.jtf_auth_principal_id = jtfp2.jtf_auth_principal_id
1135 and jtfp2.is_user_flag=1
1136 and jtfp2.principal_name = fndu.user_name
1137 and fndu.user_id = jtfre.user_id
1138 and jtfre.category = 'PARTY';
1139
1140
1141 /** ??? Does this query need to handle inactive resources or partner contacts **/
1142 cursor get_usrs_perm_wo_curr_role(cv_role_id JTF_NUMBER_TABLE, cv_user_name JTF_VARCHAR2_TABLE_1000) IS
1143 select /*+ cardinality( t 10 ) */ jtfperm.permission_name, jtfp3.principal_name
1144 from jtf_auth_principals_b jtfp3, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm, jtf_auth_principal_maps jtfpm2, (Select column_value from table (CAST(cv_user_name AS JTF_VARCHAR2_TABLE_1000))) t
1145 where jtfp3.principal_name = t.column_value
1146 and jtfp3.is_user_flag = 1 and jtfp3.jtf_auth_principal_id=jtfpm2.jtf_auth_principal_id
1147 and jtfpm2.jtf_auth_parent_principal_id = jtfrp.JTF_AUTH_PRINCIPAL_ID
1148 and jtfpm2.jtf_auth_parent_principal_id NOT IN (Select * from table (CAST(cv_role_id AS JTF_NUMBER_TABLE)))
1149 and jtfrp.JTF_AUTH_PERMISSION_ID = jtfperm.jtf_auth_permission_id
1150 and jtfperm.permission_name IN (G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
1151 and jtfrp.positive_flag = 1
1152 group by jtfperm.permission_name, jtfp3.principal_name
1153 order by jtfp3.principal_name;
1154
1155
1156 is_prtnr_perm_revoked boolean := false;
1157 is_primary_perm_revoked boolean := false;
1158 is_primary_user boolean := false;
1159 is_partner_user boolean := false;
1160
1161 l_role_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1162 l_user_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1163 l_user_names JTF_VARCHAR2_TABLE_1000 := JTF_VARCHAR2_TABLE_1000();
1164 l_user_perm_changed JTF_VARCHAR2_TABLE_1000 := JTF_VARCHAR2_TABLE_1000();
1165
1166 l_prev_user_name VARCHAR2(255) := null;
1167 l_role varchar2(10);
1168
1169
1170 BEGIN
1171
1172 ---------------Initialize --------------------
1173 -- Standard Start of API savepoint
1174 SAVEPOINT delete_role;
1175
1176 -- Initialize message list if p_init_msg_list is set to TRUE.
1177 IF FND_API.to_Boolean( p_init_msg_list )
1178 THEN
1179 FND_MSG_PUB.initialize;
1180 END IF;
1181
1182 -- Standard call to check for call compatibility.
1183 IF NOT FND_API.Compatible_API_Call (
1184 l_api_version_number
1185 ,p_api_version_number
1186 ,l_api_name
1187 ,G_PKG_NAME
1188 )
1189 THEN
1190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1191 END IF;
1192
1193 -- Debug Message
1194 IF (PV_DEBUG_HIGH_ON) THEN
1195
1196 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - start');
1197 END IF;
1198
1199
1200 -- Initialize API return status to SUCCESS
1201 x_return_status := FND_API.G_RET_STS_SUCCESS;
1202 -------------End Of Initialize -------------------------------
1203
1204
1205 IF FND_GLOBAL.User_Id IS NULL THEN
1206 FND_MESSAGE.set_name('PV', 'PV_API_USER_PROFILE_MISSING');
1207 FND_MSG_PUB.add;
1208 RAISE FND_API.G_EXC_ERROR;
1209 END IF;
1210
1211 IF (p_role_name.count < 1) THEN
1212 fnd_message.SET_NAME ('PV', 'PV_MISSING_ROLE_NAME');
1213 fnd_msg_pub.ADD;
1214 raise FND_API.G_EXC_ERROR;
1215 END IF;
1216
1217 /** Check if partner or primary permissions are part of the current role. IF NOT, we do not need to do any thing **/
1218 FOR X in check_curr_role_perms(p_role_name) LOOP
1219 l_role_ids.extend;
1220 l_role_ids(l_role_ids.count) := x.jtf_auth_principal_id;
1221 IF (x.permission_name = G_PARTNER_PERMISSION) THEN
1222 is_primary_perm_revoked := true;
1223 elsif (x.permission_name = G_PRIMARY_PERMISSION) THEN
1224 is_prtnr_perm_revoked := true;
1225 END IF;
1226 END LOOP;
1227
1228
1229 /** If partner or primary permissions are part of the current role **/
1230 IF (is_primary_perm_revoked or is_prtnr_perm_revoked) THEN
1231 FOR X in get_users_w_curr_role(l_role_ids) LOOP
1232 l_user_names.extend;
1233 l_user_names(l_user_names.count) := X.principal_name;
1234 l_user_ids.extend;
1235 l_user_ids(l_user_ids.count) := X.user_id;
1236 l_user_perm_changed.extend;
1237 END LOOP;
1238
1239 IF (l_user_names.count > 0) THEN
1240 FOR X in get_usrs_perm_wo_curr_role(l_role_ids, l_user_names) LOOP
1241 IF(l_prev_user_name is not null and l_prev_user_name <> X.principal_name) THEN
1242 FOR Y in 1..l_user_names.count LOOP
1243 IF (l_user_names(Y) = l_prev_user_name) THEN
1244 if(is_partner_user and is_primary_user) THEN
1245 l_user_perm_changed(Y) := 'PP';
1246 elsif(is_partner_user and not is_primary_user) THEN
1247 l_user_perm_changed(Y) := 'PT';
1248 elsif(not is_partner_user and is_primary_user) THEN
1249 l_user_perm_changed(Y) := 'PR';
1250 end if;
1251 is_partner_user := false;
1252 is_primary_user := false;
1253 l_prev_user_name := X.principal_name;
1254 END IF;
1255 END LOOP;
1256 ELSIF (l_prev_user_name is null) THEN
1257 l_prev_user_name := X.principal_name;
1258 END IF;
1259 IF (x.permission_name = G_PARTNER_PERMISSION) THEN
1260 is_partner_user := true;
1261 elsif (X.permission_name = G_PRIMARY_PERMISSION) THEN
1262 is_primary_user := true;
1263 end if;
1264 END LOOP;
1265
1266 FOR Y in 1..l_user_names.count LOOP
1267 IF (l_user_names(Y) = l_prev_user_name) THEN
1268 if(is_partner_user and is_primary_user) THEN
1269 l_user_perm_changed(Y) := 'PP';
1270 elsif(is_partner_user and not is_primary_user) THEN
1271 l_user_perm_changed(Y) := 'PT';
1272 elsif(not is_partner_user and is_primary_user) THEN
1273 l_user_perm_changed(Y) := 'PR';
1274 end if;
1275 END IF;
1276 END LOOP;
1277
1278
1279
1280 FOR X in 1..l_user_names.count loop
1281 IF (is_primary_perm_revoked and (l_user_perm_changed(X) is null or l_user_perm_changed(X) = 'PR')) THEN
1282 IF (l_user_perm_changed(X) = 'PR' or is_primary_perm_revoked) THEN
1283 l_role := PV_USER_RESP_PVT.G_PRIMARY;
1284 else
1285 l_role := PV_USER_RESP_PVT.G_BUSINESS;
1286 END IF;
1287
1288 PV_USER_RESP_PVT.revoke_user_resps(
1289 p_api_version_number => p_api_version_number
1290 ,p_init_msg_list => FND_API.g_false
1291 ,p_commit => FND_API.G_FALSE
1292 ,x_return_status => x_return_status
1293 ,x_msg_count => x_msg_count
1294 ,x_msg_data => x_msg_data
1295 ,p_user_id => l_user_ids(X)
1296 ,p_user_role_code => l_role
1297 );
1298
1299 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1301 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1302 RAISE FND_API.G_EXC_ERROR;
1303 END IF;
1304 ELSIF (is_primary_perm_revoked and l_user_perm_changed(X) = 'PT') THEN
1305 PV_USER_RESP_PVT.switch_user_resp(
1306 p_api_version_number => p_api_version_number
1307 ,p_init_msg_list => FND_API.g_false
1308 ,p_commit => FND_API.G_FALSE
1309 ,x_return_status => x_return_status
1310 ,x_msg_count => x_msg_count
1311 ,x_msg_data => x_msg_data
1312 ,p_user_id => l_user_ids(X)
1313 ,p_from_user_role_code => PV_USER_RESP_PVT.G_PRIMARY
1314 ,p_to_user_role_code => PV_USER_RESP_PVT.G_BUSINESS
1315 );
1316
1317 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1319 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1320 RAISE FND_API.G_EXC_ERROR;
1321 END IF;
1322
1323 END IF;
1324 END LOOP;
1325
1326 END IF;
1327 END IF;
1328
1329 FND_MSG_PUB.Count_And_Get
1330 ( p_encoded => FND_API.G_FALSE,
1331 p_count => x_msg_count,
1332 p_data => x_msg_data
1333 );
1334
1335 IF FND_API.to_Boolean( p_commit ) THEN
1336 COMMIT WORK;
1337 END IF;
1338
1339 EXCEPTION
1340 WHEN FND_API.G_EXC_ERROR THEN
1341 ROLLBACK TO delete_role;
1342 x_return_status := FND_API.G_RET_STS_ERROR;
1343 FND_MSG_PUB.Count_And_Get (
1344 p_encoded => FND_API.G_FALSE
1345 ,p_count => x_msg_count
1346 ,p_data => x_msg_data
1347 );
1348
1349
1350 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1351 ROLLBACK TO delete_role;
1352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1353 -- Standard call to get message count and if count=1, get the message
1354 FND_MSG_PUB.Count_And_Get (
1355 p_encoded => FND_API.G_FALSE
1356 ,p_count => x_msg_count
1357 ,p_data => x_msg_data
1358 );
1359
1360 WHEN OTHERS THEN
1361 ROLLBACK TO delete_role;
1362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1363 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1364 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1365 END IF;
1366 FND_MSG_PUB.Count_And_Get (
1367 p_encoded => FND_API.G_FALSE
1368 ,p_count => x_msg_count
1369 ,p_data => x_msg_data
1370 );
1371
1372 END delete_role;
1373
1374
1375 PROCEDURE assign_role
1376 (
1377 p_api_version_number IN NUMBER
1378 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1379 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1380 ,p_role_name IN JTF_VARCHAR2_TABLE_1000
1381 ,p_user_name IN VARCHAR2
1382 ,x_return_status OUT NOCOPY VARCHAR2
1383 ,x_msg_count OUT NOCOPY NUMBER
1384 ,x_msg_data OUT NOCOPY VARCHAR2
1385 )
1386 IS
1387 l_api_version_number CONSTANT NUMBER := 1.0;
1388 l_api_name CONSTANT VARCHAR2(30) := 'assign_role';
1389 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1390
1391
1392 cursor check_curr_role_perms(l_role_name JTF_VARCHAR2_TABLE_1000) IS
1393 select jtfperm.permission_name
1394 from jtf_auth_principals_b jtfp1, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
1395 where jtfp1.is_user_flag = 0 and jtfp1.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
1396 and jtfrp.positive_flag = 1 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
1397 and jtfperm.permission_name IN ( G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
1398 and jtfp1.principal_name in (Select * from table (CAST(l_role_name AS JTF_VARCHAR2_TABLE_1000)))
1399 group by jtfperm.permission_name;
1400
1401 cursor get_user_id(l_user_name VARCHAR2) IS
1402 select fndu.user_id
1403 from fnd_user fndu
1404 where fndu.user_name = l_user_name;
1405
1406 cursor get_user_permissions(l_user_name VARCHAR2) IS
1407 select jtfperm.permission_name
1408 from jtf_auth_principals_b jtfp1, jtf_auth_principal_maps jtfpm,
1409 jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
1410 where jtfp1.principal_name = l_user_name
1411 and jtfp1.is_user_flag = 1
1412 and jtfp1.jtf_auth_principal_id = jtfpm.jtf_auth_principal_id
1413 and jtfpm.JTF_AUTH_PARENT_PRINCIPAL_ID = jtfrp.jtf_auth_principal_id
1414 and jtfrp.positive_flag = 1
1415 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
1416 and jtfperm.permission_name in (G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
1417 group by jtfperm.permission_name;
1418
1419
1420 cursor validate_partner_user(l_user_id NUMBER) IS
1421 select 'X' from dual
1422 where exists
1423 (select 'X' from jtf_rs_resource_extns
1424 where user_id = l_user_id and category='PARTY');
1425
1426 is_prtnr_perm_assigned boolean := false;
1427 is_primary_perm_assigned boolean := false;
1428 l_role_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1429 is_primary_user boolean := false;
1430 is_partner_user boolean := false;
1431 l_user_id NUMBER;
1432 is_user_changed boolean := true;
1433 l_role varchar2(10);
1434 isValidPrtnrUser boolean := false;
1435
1436
1437 BEGIN
1438
1439 ---------------Initialize --------------------
1440 -- Standard Start of API savepoint
1441 SAVEPOINT assign_role;
1442
1443 -- Initialize message list if p_init_msg_list is set to TRUE.
1444 IF FND_API.to_Boolean( p_init_msg_list )
1445 THEN
1446 FND_MSG_PUB.initialize;
1447 END IF;
1448
1449 -- Standard call to check for call compatibility.
1450 IF NOT FND_API.Compatible_API_Call (
1451 l_api_version_number
1452 ,p_api_version_number
1453 ,l_api_name
1454 ,G_PKG_NAME
1455 )
1456 THEN
1457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1458 END IF;
1459
1460 -- Debug Message
1461 IF (PV_DEBUG_HIGH_ON) THEN
1462
1463 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - start');
1464 END IF;
1465
1466
1467 -- Initialize API return status to SUCCESS
1468 x_return_status := FND_API.G_RET_STS_SUCCESS;
1469 -------------End Of Initialize -------------------------------
1470
1471
1472 IF FND_GLOBAL.User_Id IS NULL THEN
1473 FND_MESSAGE.set_name('PV', 'PV_API_USER_PROFILE_MISSING');
1474 FND_MSG_PUB.add;
1475 RAISE FND_API.G_EXC_ERROR;
1476 END IF;
1477
1478 IF (p_role_name.count < 1) THEN
1479 fnd_message.SET_NAME ('PV', 'PV_MISSING_ROLE_NAME');
1480 fnd_msg_pub.ADD;
1481 raise FND_API.G_EXC_ERROR;
1482 END IF;
1483
1484 IF (p_user_name IS NULL or p_user_name = FND_API.G_MISS_CHAR) THEN
1485 fnd_message.SET_NAME ('PV', 'PV_MISSING_USER_NAME');
1486 fnd_msg_pub.ADD;
1487 raise FND_API.G_EXC_ERROR;
1488 END IF;
1489
1490 /** check if current roles that are being assigned has G_PARTNER_PERMISSION OR G_PRIMARY_PERMISSION permissions **/
1491 FOR X in check_curr_role_perms(p_role_name) LOOP
1492 IF (x.permission_name = G_PARTNER_PERMISSION) THEN
1493 is_prtnr_perm_assigned := true;
1494 elsif (x.permission_name = G_PRIMARY_PERMISSION) THEN
1495 is_primary_perm_assigned := true;
1496 END IF;
1497 END LOOP;
1498
1499 IF(is_primary_perm_assigned or is_prtnr_perm_assigned) THEN
1500
1501 FOR X in get_user_id(p_user_name) LOOP
1502 l_user_id := X.USER_ID;
1503 END LOOP;
1504
1505 /** Check if user is partner user or partner primary user **/
1506 FOR X in get_user_permissions(p_user_name) LOOP
1507 IF (x.permission_name = G_PARTNER_PERMISSION) THEN
1508 is_partner_user := true;
1509 elsif (x.permission_name = G_PRIMARY_PERMISSION) THEN
1510 is_primary_user := true;
1511 END IF;
1512 END LOOP;
1513
1514 /** If partner or primary permissions are part of the current role **/
1515 If (NOT is_partner_user and is_prtnr_perm_assigned) THEN
1516 FOR X in validate_partner_user(l_user_id) LOOP
1517 isValidPrtnrUser := true;
1518 END LOOP;
1519
1520 IF (NOT isValidPrtnrUser) THEN
1521 fnd_message.SET_NAME ('PV', 'PV_INVALID_PTNR_USER');
1522 fnd_msg_pub.ADD;
1523 raise FND_API.G_EXC_ERROR;
1524 END IF;
1525
1526 IF(is_primary_perm_assigned OR is_primary_user) THEN
1527 l_role := PV_USER_RESP_PVT.G_PRIMARY;
1528 else
1529 l_role := PV_USER_RESP_PVT.G_BUSINESS;
1530 end if;
1531
1532 PV_USER_RESP_PVT.assign_user_resps(
1533 p_api_version_number => p_api_version_number
1534 ,p_init_msg_list => FND_API.g_false
1535 ,p_commit => FND_API.G_FALSE
1536 ,x_return_status => x_return_status
1537 ,x_msg_count => x_msg_count
1538 ,x_msg_data => x_msg_data
1539 ,p_user_id => l_user_id
1540 ,p_user_role_code => l_role
1541 );
1542
1543 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1545 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1546 RAISE FND_API.G_EXC_ERROR;
1547 END IF;
1548
1549 ELSIF (is_partner_user and (NOT is_primary_user) and is_primary_perm_assigned) THEN
1550
1551 PV_USER_RESP_PVT.switch_user_resp(
1552 p_api_version_number => p_api_version_number
1553 ,p_init_msg_list => FND_API.g_false
1554 ,p_commit => FND_API.G_FALSE
1555 ,x_return_status => x_return_status
1556 ,x_msg_count => x_msg_count
1557 ,x_msg_data => x_msg_data
1558 ,p_user_id => l_user_id
1559 ,p_from_user_role_code => PV_USER_RESP_PVT.G_BUSINESS
1560 ,p_to_user_role_code => PV_USER_RESP_PVT.G_PRIMARY
1561 );
1562
1563 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1564 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1565 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1566 RAISE FND_API.G_EXC_ERROR;
1567 END IF;
1568
1569 END IF;
1570 END IF;
1571
1572 FND_MSG_PUB.Count_And_Get
1573 ( p_encoded => FND_API.G_FALSE,
1574 p_count => x_msg_count,
1575 p_data => x_msg_data
1576 );
1577
1578 IF FND_API.to_Boolean( p_commit ) THEN
1579 COMMIT WORK;
1580 END IF;
1581
1582 EXCEPTION
1583 WHEN FND_API.G_EXC_ERROR THEN
1584 ROLLBACK TO assign_role;
1585 x_return_status := FND_API.G_RET_STS_ERROR;
1586 FND_MSG_PUB.Count_And_Get (
1587 p_encoded => FND_API.G_FALSE
1588 ,p_count => x_msg_count
1589 ,p_data => x_msg_data
1590 );
1591
1592
1593 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1594 ROLLBACK TO assign_role;
1595 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1596 -- Standard call to get message count and if count=1, get the message
1597 FND_MSG_PUB.Count_And_Get (
1598 p_encoded => FND_API.G_FALSE
1599 ,p_count => x_msg_count
1600 ,p_data => x_msg_data
1601 );
1602
1603 WHEN OTHERS THEN
1604 ROLLBACK TO assign_role;
1605 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1606 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1607 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1608 END IF;
1609 FND_MSG_PUB.Count_And_Get (
1610 p_encoded => FND_API.G_FALSE
1611 ,p_count => x_msg_count
1612 ,p_data => x_msg_data
1613 );
1614
1615 END assign_role;
1616
1617
1618 PROCEDURE update_role
1619 (
1620 p_api_version_number IN NUMBER
1621 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1622 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1623 ,p_assigned_perms IN JTF_VARCHAR2_TABLE_1000
1624 ,p_unassigned_perms IN JTF_VARCHAR2_TABLE_1000
1625 ,p_role_name IN VARCHAR2
1626 ,x_return_status OUT NOCOPY VARCHAR2
1627 ,x_msg_count OUT NOCOPY NUMBER
1628 ,x_msg_data OUT NOCOPY VARCHAR2
1629 )
1630 IS
1631 l_api_version_number CONSTANT NUMBER := 1.0;
1632 l_api_name CONSTANT VARCHAR2(30) := 'update_role';
1633 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1634
1635
1636 cursor get_user_permissions(cv_role_id NUMBER, cv_user_name JTF_VARCHAR2_TABLE_1000) IS
1637 select /*+ cardinality( t 10 ) */ jtfperm.permission_name, jtfp3.principal_name
1638 from jtf_auth_principals_b jtfp3, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm, jtf_auth_principal_maps jtfpm2, (Select column_value from table (CAST(cv_user_name AS JTF_VARCHAR2_TABLE_1000))) t
1639 where jtfp3.principal_name = t.column_value
1640 and jtfp3.is_user_flag = 1
1641 and jtfp3.jtf_auth_principal_id=jtfpm2.jtf_auth_principal_id
1642 and jtfpm2.jtf_auth_parent_principal_id = jtfrp.JTF_AUTH_PRINCIPAL_ID
1643 and jtfrp.JTF_AUTH_PERMISSION_ID = jtfperm.jtf_auth_permission_id
1644 and jtfperm.permission_name IN (G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
1645 and jtfrp.positive_flag = 1
1646 and jtfpm2.jtf_auth_parent_principal_id <> cv_role_id
1647 group by jtfperm.permission_name, jtfp3.principal_name
1648 order by jtfp3.principal_name;
1649
1650
1651 cursor get_users_w_curr_role(cv_role_id NUMBER) IS
1652 select jtfp2.principal_name, fndu.user_id
1653 from jtf_auth_principal_maps jtfpm, jtf_auth_principals_b jtfp2,jtf_auth_principals_b jtfp1,
1654 fnd_user fndu, jtf_rs_resource_extns jtfre
1655 where jtfp1.jtf_auth_principal_id = cv_role_id
1656 and jtfp1.is_user_flag = 0
1657 and jtfp1.jtf_auth_principal_id = jtfpm.jtf_auth_parent_principal_id
1658 and jtfpm.jtf_auth_principal_id = jtfp2.jtf_auth_principal_id
1659 and jtfp2.is_user_flag=1
1660 and jtfp2.principal_name = fndu.user_name
1661 and fndu.user_id = jtfre.user_id
1662 and jtfre.category = 'PARTY';
1663
1664
1665 cursor check_curr_role_perms(l_role_name VARCHAR2) IS
1666 select jtfperm.permission_name, jtfp1.jtf_auth_principal_id
1667 from jtf_auth_principals_b jtfp1, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
1668 where jtfp1.is_user_flag = 0 and jtfp1.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
1669 and jtfrp.positive_flag = 1 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
1670 and jtfperm.permission_name IN ( G_PRIMARY_PERMISSION, G_PARTNER_PERMISSION)
1671 and jtfp1.principal_name = l_role_name;
1672
1673
1674 is_prtnr_perm_assigned boolean := false;
1675 is_primary_perm_assigned boolean := false;
1676 is_prtnr_perm_revoked boolean := false;
1677 is_primary_perm_revoked boolean := false;
1678
1679 is_primary_user boolean := false;
1680 is_partner_user boolean := false;
1681
1682 l_user_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1683 l_user_names JTF_VARCHAR2_TABLE_1000 := JTF_VARCHAR2_TABLE_1000();
1684 l_user_perm_changed JTF_VARCHAR2_TABLE_1000 := JTF_VARCHAR2_TABLE_1000();
1685
1686 l_role varchar2(10);
1687 l_prev_user_name VARCHAR2(255);
1688 l_role_id NUMBER;
1689 l_prtnr_perm_exists boolean := false;
1690 l_primary_perm_exists boolean := false;
1691
1692
1693
1694 BEGIN
1695
1696 ---------------Initialize --------------------
1697 -- Standard Start of API savepoint
1698 SAVEPOINT update_role;
1699
1700 -- Initialize message list if p_init_msg_list is set to TRUE.
1701 IF FND_API.to_Boolean( p_init_msg_list )
1702 THEN
1703 FND_MSG_PUB.initialize;
1704 END IF;
1705
1706 -- Standard call to check for call compatibility.
1707 IF NOT FND_API.Compatible_API_Call (
1708 l_api_version_number
1709 ,p_api_version_number
1710 ,l_api_name
1711 ,G_PKG_NAME
1712 )
1713 THEN
1714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1715 END IF;
1716
1717 -- Debug Message
1718 IF (PV_DEBUG_HIGH_ON) THEN
1719
1720 PVX_UTILITY_PVT.debug_message('API: ' || l_api_name || ' - start');
1721 END IF;
1722
1723
1724 -- Initialize API return status to SUCCESS
1725 x_return_status := FND_API.G_RET_STS_SUCCESS;
1726 -------------End Of Initialize -------------------------------
1727
1728
1729 IF FND_GLOBAL.User_Id IS NULL THEN
1730 FND_MESSAGE.set_name('PV', 'PV_API_USER_PROFILE_MISSING');
1731 FND_MSG_PUB.add;
1732 RAISE FND_API.G_EXC_ERROR;
1733 END IF;
1734
1735 IF (p_role_name IS NULL or p_role_name = FND_API.G_MISS_CHAR) THEN
1736 fnd_message.SET_NAME ('PV', 'PV_MISSING_ROLE_NAME');
1737 fnd_msg_pub.ADD;
1738 raise FND_API.G_EXC_ERROR;
1739 END IF;
1740
1741 IF (p_assigned_perms IS NOT NULL and p_assigned_perms.count > 0) THEN
1742 FOR i in p_assigned_perms.FIRST .. p_assigned_perms.LAST LOOP
1743 IF (p_assigned_perms(i) = G_PARTNER_PERMISSION) THEN
1744 is_prtnr_perm_assigned := true;
1745 elsif (p_assigned_perms(i) = G_PRIMARY_PERMISSION) THEN
1746 is_primary_perm_assigned := true;
1747 END IF;
1748 END LOOP;
1749 END IF;
1750
1751
1752 IF (p_unassigned_perms IS NOT NULL and p_unassigned_perms.count > 0) THEN
1753 FOR i in p_unassigned_perms.FIRST .. p_unassigned_perms.LAST LOOP
1754 IF (p_unassigned_perms(i) = G_PARTNER_PERMISSION) THEN
1755 is_prtnr_perm_revoked := true;
1756 elsif (p_unassigned_perms(i) = G_PRIMARY_PERMISSION) THEN
1757 is_primary_perm_revoked := true;
1758 END IF;
1759 END LOOP;
1760 END IF;
1761
1762 If(is_prtnr_perm_assigned or is_primary_perm_assigned or is_prtnr_perm_revoked or is_primary_perm_revoked) THEN
1763
1764 FOR Y in check_curr_role_perms(p_role_name) LOOP
1765 l_role_id := Y.jtf_auth_principal_id;
1766 IF (Y.permission_name = G_PARTNER_PERMISSION) THEN
1767 l_prtnr_perm_exists := true;
1768 ELSIF (Y.permission_name = G_PRIMARY_PERMISSION) THEN
1769 l_primary_perm_exists := true;
1770 END IF;
1771 END LOOP;
1772
1773
1774 FOR X in get_users_w_curr_role(l_role_id) LOOP
1775 l_user_names.extend;
1776 l_user_names(l_user_names.count) := X.principal_name;
1777 l_user_ids.extend;
1778 l_user_ids(l_user_ids.count) := X.user_id;
1779 l_user_perm_changed.extend;
1780 END LOOP;
1781
1782 IF (l_user_names.count > 0) THEN
1783 FOR X in get_user_permissions(l_role_id, l_user_names) LOOP
1784 IF(l_prev_user_name is not null and l_prev_user_name <> X.principal_name) THEN
1785 FOR Y in 1..l_user_names.count LOOP
1786 IF (l_user_names(Y) = l_prev_user_name) THEN
1787 if(is_partner_user and is_primary_user) THEN
1788 l_user_perm_changed(Y) := 'PP';
1789 elsif(is_partner_user and not is_primary_user) THEN
1790 l_user_perm_changed(Y) := 'PT';
1791 elsif(not is_partner_user and is_primary_user) THEN
1792 l_user_perm_changed(Y) := 'PR';
1793 end if;
1794 is_partner_user := false;
1795 is_primary_user := false;
1796 l_prev_user_name := X.principal_name;
1797 END IF;
1798 END LOOP;
1799 ELSIF (l_prev_user_name is null) THEN
1800 l_prev_user_name := X.principal_name;
1801 END IF;
1802 IF (x.permission_name = G_PARTNER_PERMISSION) THEN
1803 is_partner_user := true;
1804 elsif (X.permission_name = G_PRIMARY_PERMISSION) THEN
1805 is_primary_user := true;
1806 end if;
1807 END LOOP;
1808
1809
1810 FOR Y in 1..l_user_names.count LOOP
1811 IF (l_user_names(Y) = l_prev_user_name) THEN
1812 if(is_partner_user and is_primary_user) THEN
1813 l_user_perm_changed(Y) := 'PP';
1814 elsif(is_partner_user and not is_primary_user) THEN
1815 l_user_perm_changed(Y) := 'PT';
1816 elsif(not is_partner_user and is_primary_user) THEN
1817 l_user_perm_changed(Y) := 'PR';
1818 end if;
1819 END IF;
1820 END LOOP;
1821
1822
1823 FOR X in 1..l_user_names.count loop
1824 IF (is_prtnr_perm_assigned or is_primary_perm_assigned) THEN
1825 IF ((l_user_perm_changed(X) is null or l_user_perm_changed(X) = 'PR') and is_prtnr_perm_assigned) THEN
1826 IF(is_primary_perm_assigned OR l_user_perm_changed(X) = 'PR' or (l_primary_perm_exists and NOT is_primary_perm_revoked)) THEN
1827 l_role := PV_USER_RESP_PVT.G_PRIMARY;
1828 else
1829 l_role := PV_USER_RESP_PVT.G_BUSINESS;
1830 end if;
1831
1832 PV_USER_RESP_PVT.assign_user_resps(
1833 p_api_version_number => p_api_version_number
1834 ,p_init_msg_list => FND_API.g_false
1835 ,p_commit => FND_API.G_FALSE
1836 ,x_return_status => x_return_status
1837 ,x_msg_count => x_msg_count
1838 ,x_msg_data => x_msg_data
1839 ,p_user_id => l_user_ids(X)
1840 ,p_user_role_code => l_role
1841 );
1842
1843 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1845 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1846 RAISE FND_API.G_EXC_ERROR;
1847 END IF;
1848 ELSIF ((l_user_perm_changed(X) = 'PT'
1849 or (l_prtnr_perm_exists and NOT is_prtnr_perm_revoked))
1850 and is_primary_perm_assigned) THEN
1851
1852 PV_USER_RESP_PVT.switch_user_resp(
1853 p_api_version_number => p_api_version_number
1854 ,p_init_msg_list => FND_API.g_false
1855 ,p_commit => FND_API.G_FALSE
1856 ,x_return_status => x_return_status
1857 ,x_msg_count => x_msg_count
1858 ,x_msg_data => x_msg_data
1859 ,p_user_id => l_user_ids(X)
1860 ,p_from_user_role_code => PV_USER_RESP_PVT.G_BUSINESS
1861 ,p_to_user_role_code => PV_USER_RESP_PVT.G_PRIMARY
1862 );
1863
1864 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1865 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1866 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1867 RAISE FND_API.G_EXC_ERROR;
1868 END IF;
1869 END IF;
1870 END IF;
1871
1872 IF (is_prtnr_perm_revoked or is_primary_perm_revoked) THEN
1873 /** If G_PARTNER_PERMISSION permission is revoked and user will become non partner user after the role is revoked **/
1874 If((l_user_perm_changed(X) = 'PR' or l_user_perm_changed(X) is null) and is_prtnr_perm_revoked) THEN
1875 IF(l_primary_perm_exists or l_user_perm_changed(X) = 'PR') THEN
1876 l_role := PV_USER_RESP_PVT.G_PRIMARY;
1877 else
1878 l_role := PV_USER_RESP_PVT.G_BUSINESS;
1879 end if;
1880
1881 PV_USER_RESP_PVT.revoke_user_resps(
1882 p_api_version_number => p_api_version_number
1883 ,p_init_msg_list => FND_API.g_false
1884 ,p_commit => FND_API.G_FALSE
1885 ,x_return_status => x_return_status
1886 ,x_msg_count => x_msg_count
1887 ,x_msg_data => x_msg_data
1888 ,p_user_id => l_user_ids(X)
1889 ,p_user_role_code => l_role
1890 );
1891
1892 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1894 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1895 RAISE FND_API.G_EXC_ERROR;
1896 END IF;
1897
1898 /** If partner user and primary user permission is revoked and user will become business user after this **/
1899 elsif ((l_user_perm_changed(X) = 'PT' or (l_user_perm_changed(X) is null and l_prtnr_perm_exists)) and is_primary_perm_revoked) THEN
1900
1901
1902 PV_USER_RESP_PVT.switch_user_resp(
1903 p_api_version_number => p_api_version_number
1904 ,p_init_msg_list => FND_API.g_false
1905 ,p_commit => FND_API.G_FALSE
1906 ,x_return_status => x_return_status
1907 ,x_msg_count => x_msg_count
1908 ,x_msg_data => x_msg_data
1909 ,p_user_id => l_user_ids(X)
1910 ,p_from_user_role_code => PV_USER_RESP_PVT.G_PRIMARY
1911 ,p_to_user_role_code => PV_USER_RESP_PVT.G_BUSINESS
1912 );
1913
1914 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1915 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1916 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1917 RAISE FND_API.G_EXC_ERROR;
1918 END IF;
1919 END IF;
1920 END IF;
1921 END LOOP;
1922
1923
1924 END IF;
1925 END IF;
1926
1927
1928
1929
1930 FND_MSG_PUB.Count_And_Get
1931 ( p_encoded => FND_API.G_FALSE,
1932 p_count => x_msg_count,
1933 p_data => x_msg_data
1934 );
1935
1936 IF FND_API.to_Boolean( p_commit ) THEN
1937 COMMIT WORK;
1938 END IF;
1939
1940 EXCEPTION
1941 WHEN FND_API.G_EXC_ERROR THEN
1942 ROLLBACK TO update_role;
1943 x_return_status := FND_API.G_RET_STS_ERROR;
1944 FND_MSG_PUB.Count_And_Get (
1945 p_encoded => FND_API.G_FALSE
1946 ,p_count => x_msg_count
1947 ,p_data => x_msg_data
1948 );
1949
1950
1951 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1952 ROLLBACK TO update_role;
1953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1954 -- Standard call to get message count and if count=1, get the message
1955 FND_MSG_PUB.Count_And_Get (
1956 p_encoded => FND_API.G_FALSE
1957 ,p_count => x_msg_count
1958 ,p_data => x_msg_data
1959 );
1960
1961 WHEN OTHERS THEN
1962 ROLLBACK TO update_role;
1963 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1964 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1965 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1966 END IF;
1967 FND_MSG_PUB.Count_And_Get (
1968 p_encoded => FND_API.G_FALSE
1969 ,p_count => x_msg_count
1970 ,p_data => x_msg_data
1971 );
1972
1973 END update_role;
1974
1975
1976
1977 FUNCTION is_partner_user (p_rel_party_id IN NUMBER) RETURN VARCHAR2 IS
1978
1979 cursor cv_is_partner_user(l_rel_party_id NUMBER) IS
1980 select 1 from dual where exists
1981 (
1982 select /*+ use_nl (hzr res) */ pvpp.partner_id, hzr.party_id, hzr.subject_id ,pvpp.partner_party_id
1983 from jtf_rs_resource_extns RES, hz_relationships hzr, pv_partner_profiles pvpp
1984 where RES.category = 'PARTY' and RES.source_id = 6310
1985 and RES.start_date_active <= SYSDATE
1986 and (RES.end_date_active is null or RES.end_date_active > SYSDATE)
1987 and RES.source_id = hzr.party_id and hzr.directional_flag = 'F'
1988 and hzr.relationship_code = 'EMPLOYEE_OF' and HZR.subject_table_name ='HZ_PARTIES'
1989 and HZR.object_table_name ='HZ_PARTIES' and hzr.start_date <= SYSDATE
1990 and (hzr.end_date is null or hzr.end_date > sysdate)
1991 and hzr.object_id = pvpp.partner_party_id
1992 and pvpp.status = 'A' and exists
1993 (
1994 select 1 from jtf_auth_principal_maps jtfpm,jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd,
1995 jtf_auth_principals_b jtfp2,jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
1996 where jtfp1.principal_name = RES.user_name and jtfp1.is_user_flag=1
1997 and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id and jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
1998 and jtfp2.is_user_flag=0 and jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
1999 and jtfrp.positive_flag = 1 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
2000 and jtfperm.permission_name = G_PARTNER_PERMISSION
2001 and jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
2002 and jtfd.domain_name='CRM_DOMAIN'
2003 )
2004 );
2005
2006 is_partner_user varchar2(1) := 'N';
2007
2008
2009 BEGIN
2010
2011 FOR x in cv_is_partner_user(p_rel_party_id) loop
2012 is_partner_user := 'Y';
2013 end loop;
2014
2015 return is_partner_user;
2016 EXCEPTION
2017 WHEN NO_DATA_FOUND THEN
2018 return is_partner_user;
2019 END; --is_partner_user
2020
2021
2022
2023 /*+====================================================================
2024 | FUNCTION NAME
2025 | post_approval
2026 |
2027 | DESCRIPTION
2028 | This function is seeded as a subscription to the approval event
2029 |
2030 | USAGE
2031 | - creates resps and resources when an approval event happens
2032 |
2033 +======================================================================*/
2034
2035 FUNCTION post_approval(
2036 p_subscription_guid IN RAW,
2037 p_event IN OUT NOCOPY wf_event_t)
2038
2039 RETURN VARCHAR2
2040 IS
2041
2042 l_api_version_number CONSTANT NUMBER := 1.0;
2043 l_api_name CONSTANT VARCHAR2(30) := 'post_approval';
2044
2045 l_key VARCHAR2(240) := p_event.GetEventKey();
2046 l_id NUMBER;
2047 l_userreg_id NUMBER;
2048 l_usertype_key VARCHAR2(240);
2049 l_usertype_appId VARCHAR2(240);
2050 l_customer_id NUMBER;
2051 l_user_id NUMBER;
2052 l_user_type_id NUMBER;
2053 l_user_name VARCHAR2(100);
2054 x_return_status VARCHAR2(1);
2055 x_msg_count NUMBER;
2056 x_msg_data VARCHAR2(240);
2057 is_partner NUMBER;
2058 partner_User_rec partner_User_rec_type;
2059 x_msg_index NUMBER;
2060
2061 -- get user id, user name, customer id and usertype id
2062 Cursor c_get_user_info(c_reg_id NUMBER, c_user_type VARCHAR2) IS
2063 select usr.user_id, usr.user_name, usr.customer_id, jureg.usertype_id
2064 from fnd_user usr, jtf_um_usertype_reg jureg
2065 where jureg.usertype_reg_id=c_reg_id
2066 and usr.user_id = jureg.user_id;
2067
2068 -- check if the person is a partner user
2069 Cursor c_is_partner_user(c_usertype_id NUMBER) is
2070 select 1 from dual
2071 where exists
2072 ( select jtfperm.permission_name
2073 from jtf_auth_principals_b jtfp1, jtf_auth_role_perms jtfrp, jtf_auth_permissions_b jtfperm
2074 where jtfp1.is_user_flag = 0
2075 and jtfp1.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
2076 and jtfrp.positive_flag = 1
2077 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
2078 and jtfperm.permission_name = 'PV_PARTNER_USER'
2079 and jtfp1.principal_name IN
2080 (select principal_name
2081 from jtf_um_usertype_role jtur
2082 where jtur.usertype_id = c_usertype_id
2083 and (jtur.effective_end_date is null or jtur.effective_end_date > sysdate)
2084 union all
2085 select jtsr.principal_name
2086 from jtf_um_usertype_subscrip jtus, jtf_um_subscription_role jtsr
2087 where jtus.usertype_id = c_usertype_id
2088 and (jtus.effective_end_date is null or jtus.effective_end_date > sysdate)
2089 and jtus.subscription_flag = 'IMPLICIT'
2090 and jtus.subscription_id = jtsr.subscription_id
2091 and (jtsr.effective_end_date is null or jtsr.effective_end_date > sysdate)
2092 )
2093 );
2094
2095 BEGIN
2096
2097 FND_MSG_PUB.initialize;
2098
2099 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2100 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2101 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
2102 ,'Entered Post Approval procedure');
2103 END IF;
2104
2105 l_userreg_id := p_event.getValueForParameter('USERTYPEREG_ID');
2106 l_usertype_key := p_event.getValueForParameter('USER_TYPE_KEY');
2107 l_usertype_appId := p_event.getValueForParameter('APPID');
2108
2109 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2110 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2111 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
2112 ,'l_userreg_id=' || l_userreg_id || ',l_usertype_key=' || l_usertype_key || ',l_usertype_appId=' || l_usertype_appId);
2113 END IF;
2114
2115 OPEN c_get_user_info(l_userreg_id, l_usertype_key);
2116 FETCH c_get_user_info into l_user_id, l_user_name, l_customer_id, l_user_type_id;
2117 CLOSE c_get_user_info;
2118
2119 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2120 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2121 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
2122 ,'After c_get_user_info: l_user_id' || l_user_id || ',l_user_name=' || l_user_name || ',l_customer_id=' || l_customer_id || ',l_user_type_id=' || l_user_type_id);
2123 END IF;
2124
2125 OPEN c_is_partner_user(l_user_type_id);
2126 FETCH c_is_partner_user into is_partner;
2127 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2128 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2129 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
2130 ,'is_partner='|| to_char(is_partner) || ';');
2131 END IF;
2132 if (c_is_partner_user%NOTFOUND or is_partner <> 1 ) then
2133 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2134 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2135 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
2136 ,'l_user_name:' || l_user_name || '-- This user is not a partner user; no action; returning SUCCESS');
2137 END IF;
2138 RETURN 'SUCCESS';
2139 end if;
2140 CLOSE c_is_partner_user;
2141
2142 -- create the partner_user_rec
2143 partner_user_rec.user_name := l_user_name;
2144 partner_user_rec.person_rel_party_id := l_customer_id;
2145 partner_user_rec.user_id := l_user_id;
2146 partner_user_rec.user_type_id := l_user_type_id;
2147
2148 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2149 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2150 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
2151 ,'this is a partner user; created partner user rec and going to call pv_user_mgmt_pvt.register_partner_user');
2152 END IF;
2153
2154 -- jkylee: call register partner user procedure
2155 pv_user_mgmt_pvt.register_partner_user
2156 (
2157 p_api_version_number => l_api_version_number
2158 ,p_init_msg_list => FND_API.g_false
2159 ,p_commit => FND_API.G_FALSE
2160 ,p_partner_user_rec => partner_user_rec
2161 ,x_return_status => x_return_status
2162 ,x_msg_count => x_msg_count
2163 ,x_msg_data => x_msg_data
2164 );
2165
2166 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2167 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2168 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
2169 ,'after call pv_user_mgmt_pvt.register_partner_user: x_return_status='|| x_return_status || ',x_msg_count=' || to_char(x_msg_count));
2170 END IF;
2171
2172 FND_MSG_PUB.Count_And_Get
2173 ( p_encoded => FND_API.G_FALSE,
2174 p_count => x_msg_count,
2175 p_data => x_msg_data
2176 );
2177
2178 if (x_return_status = 'S') then
2179 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2180 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2181 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
2182 ,'returning SUCCESS to the function');
2183 END IF;
2184 RETURN 'SUCCESS';
2185 else
2186
2187 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2188 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2189 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
2190 ,'x_return_status is not returning S');
2191 END IF;
2192 FOR k IN 1 .. x_msg_count LOOP
2193 fnd_msg_pub.get (
2194 p_msg_index => k
2195 ,p_encoded => FND_API.G_FALSE
2196 ,p_data => x_msg_data
2197 ,p_msg_index_out => x_msg_index);
2198
2199
2200 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2201 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
2202 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
2203 ,'in the error msg loop: k=' || to_char(k)|| ',x_msg_data=' || x_msg_data);
2204 END IF;
2205 END LOOP;
2206
2207 WF_CORE.CONTEXT('PV', 'post_approval',
2208 p_event.getEventName(), p_subscription_guid);
2209 WF_EVENT.setErrorInfo(p_event, 'ERROR DURING CALL TO pv_user_mgmt_pvt.register_partner_user');
2210 RETURN 'ERROR';
2211
2212 end if;
2213
2214 RETURN 'SUCCESS';
2215
2216
2217 EXCEPTION
2218 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2219 WF_CORE.CONTEXT('PV', 'post_approval',
2220 p_event.getEventName(), p_subscription_guid);
2221 WF_EVENT.setErrorInfo(p_event, 'UNEXPECTED ERROR');
2222 RETURN 'ERROR';
2223 WHEN OTHERS THEN
2224 WF_CORE.CONTEXT('PV', 'post_approval',
2225 p_event.getEventName(), p_subscription_guid);
2226 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2227 RETURN 'ERROR';
2228 END;
2229
2230
2231 --=============================================================================+
2232 --| Procedure |
2233 --| update_elig_prgm_4_new_ptnr |
2234 --| |
2235 --| Parameters |
2236 --| IN |
2237 --| OUT |
2238 --| |
2239 --| |
2240 --| NOTES |
2241 --| |
2242 --| HISTORY |
2243 --| |
2244 --==============================================================================
2245 PROCEDURE update_elig_prgm_4_new_ptnr(
2246 p_api_version_number IN NUMBER
2247 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2248 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2249 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
2250 ,x_return_status OUT NOCOPY VARCHAR2
2251 ,x_msg_count OUT NOCOPY NUMBER
2252 ,x_msg_data OUT NOCOPY VARCHAR2
2253 ,p_partner_id IN NUMBER
2254 ,p_member_type IN VARCHAR2 := null
2255 )
2256 IS
2257 CURSOR c_get_program_info IS
2258 SELECT prg.program_id, prereq_process_rule_id
2259 FROM pv_partner_program_b prg
2260 WHERE prg.program_status_code = 'ACTIVE'
2261 AND prg.program_level_code = 'MEMBERSHIP'
2262 AND NVL(prg.allow_enrl_until_date, SYSDATE +1) >= SYSDATE
2263 AND prg.enabled_flag = 'Y';
2264
2265 CURSOR c_get_prereq (c_program_id NUMBER) IS
2266 SELECT change_from_program_id
2267 FROM pv_pg_enrl_change_rules rule
2268 WHERE rule.change_to_program_id = c_program_id
2269 AND rule.change_direction_code = 'PREREQUISITE'
2270 AND rule.EFFECTIVE_FROM_DATE <= SYSDATE
2271 AND NVL(rule.EFFECTIVE_TO_DATE, SYSDATE+1) >= SYSDATE
2272 AND rule.ACTIVE_FLAG = 'Y';
2273
2274 CURSOR c_is_no_prereq_membership(c_program_id NUMBER, c_partner_id NUMBER) IS
2275 SELECT 1
2276 FROM dual
2277 WHERE not exists(
2278 SELECT 1
2279 FROM pv_pg_memberships memb
2280 WHERE memb.program_id = c_program_id
2281 AND memb.partner_id = c_partner_id
2282 AND memb.MEMBERSHIP_STATUS_CODE = 'ACTIVE'
2283 AND memb.START_DATE <= SYSDATE
2284 AND NVL(memb.ACTUAL_END_DATE,NVL(memb.ORIGINAL_END_DATE,SYSDATE+1)) >= SYSDATE
2285 );
2286
2287 CURSOR c_elig_program_id_seq IS
2288 SELECT PV_PG_ELIG_PROGRAMS_S.NEXTVAL
2289 FROM dual;
2290
2291 l_api_name CONSTANT VARCHAR2(30) := 'update_elig_prgm_4_new_ptnr';
2292 l_cnt NUMBER;
2293 l_partner_id NUMBER;
2294 l_member_type VARCHAR2(500);
2295 l_program_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2296 l_process_rule_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2297 l_rule_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2298 l_attr_id_tbl JTF_NUMBER_TABLE;
2299 l_attr_evaluation_result_tbl JTF_VARCHAR2_TABLE_100;
2300 l_partner_attr_value_tbl JTF_VARCHAR2_TABLE_4000;
2301 l_evaluation_criteria_tbl JTF_VARCHAR2_TABLE_4000;
2302 l_rule_pass_flag VARCHAR2(4);
2303 l_delimiter VARCHAR2(10);
2304 l_user_id NUMBER := FND_GLOBAL.USER_ID();
2305 l_prereq_exist BOOLEAN;
2306 l_no_membership BOOLEAN;
2307 l_stmt_str VARCHAR2(4000);
2308 l_nextval NUMBER;
2309
2310 BEGIN
2311 IF (PV_DEBUG_HIGH_ON) THEN
2312 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - start');
2313 END IF;
2314
2315 x_return_status := FND_API.G_RET_STS_SUCCESS;
2316
2317 FOR x IN c_get_program_info LOOP
2318 l_prereq_exist := false;
2319 l_no_membership := false;
2320 IF (PV_DEBUG_HIGH_ON) THEN
2321 PVX_UTILITY_PVT.debug_message('x.program_id = ' || x.program_id);
2322 PVX_UTILITY_PVT.debug_message('p_partner_id = ' || p_partner_id);
2323 END IF;
2324 FOR y IN c_get_prereq(x.program_id) LOOP
2325 l_prereq_exist := true;
2326 IF (PV_DEBUG_HIGH_ON) THEN
2327 PVX_UTILITY_PVT.debug_message('y.change_from_program_id = ' || y.change_from_program_id);
2328 END IF;
2329 FOR z IN c_is_no_prereq_membership(y.change_from_program_id, p_partner_id) LOOP
2330 l_no_membership := true;
2331 IF (PV_DEBUG_HIGH_ON) THEN
2332 PVX_UTILITY_PVT.debug_message('prereq exists but no active membership.');
2333 END IF;
2334 END LOOP;
2335 EXIT WHEN l_no_membership;
2336 END LOOP;
2337 IF ((l_prereq_exist and (not l_no_membership)) or (not l_prereq_exist)) THEN
2338 IF (x.prereq_process_rule_id IS NULL) THEN
2339 OPEN c_elig_program_id_seq;
2340 FETCH c_elig_program_id_seq INTO l_nextval;
2341 CLOSE c_elig_program_id_seq;
2342
2343 l_stmt_str := 'INSERT
2344 INTO pv_pg_elig_programs
2345 (
2346 ELIG_PROGRAM_ID,
2347 PROGRAM_ID,
2348 PARTNER_ID,
2349 ELIGIBILITY_CRIT_CODE,
2350 CREATION_DATE,
2351 CREATED_BY,
2352 LAST_UPDATE_DATE,
2353 LAST_UPDATED_BY,
2354 LAST_UPDATE_LOGIN ,
2355 OBJECT_Version_number
2356 )
2357 VALUES
2358 (
2359 :1,
2360 :2,
2361 :3,
2362 :4,
2363 :5,
2364 :6,
2365 :7,
2366 :8,
2367 :9,
2368 :10
2369 )';
2370
2371 EXECUTE IMMEDIATE l_stmt_str
2372 USING l_nextval,
2373 x.program_id,
2374 p_partner_id,
2375 'PREREQ',
2376 SYSDATE,
2377 l_user_id,
2378 SYSDATE,
2379 l_user_id,
2380 l_user_id,
2381 1.0;
2382
2383 ELSE
2384 l_rule_id_tbl.extend;
2385 l_rule_id_tbl(1) := x.prereq_process_rule_id;
2386 IF (PV_DEBUG_HIGH_ON) THEN
2387 PVX_UTILITY_PVT.debug_message('l_rule_id_tbl(1) = ' || l_rule_id_tbl(1));
2388 END IF;
2389
2390 PV_RULE_EVALUATION_PUB.quick_partner_eval_outcome(
2391 p_api_version => p_api_version_number
2392 ,p_init_msg_list => FND_API.G_FALSE
2393 ,p_commit => FND_API.G_FALSE
2394 ,p_validation_level => p_validation_level
2395 ,p_partner_id => p_partner_id
2396 ,p_rule_id_tbl => l_rule_id_tbl
2397 ,x_rule_pass_flag => l_rule_pass_flag
2398 ,x_return_status => x_return_status
2399 ,x_msg_count => x_msg_count
2400 ,x_msg_data => x_msg_data
2401 );
2402
2403 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2404 IF (PV_DEBUG_HIGH_ON) THEN
2405 PVX_UTILITY_PVT.debug_message('x_return_status = ' || x_return_status);
2406 END IF;
2407 RAISE FND_API.G_EXC_ERROR;
2408 END IF;
2409
2410 IF (PV_DEBUG_HIGH_ON) THEN
2411 PVX_UTILITY_PVT.debug_message('l_rule_pass_flag = '||l_rule_pass_flag);
2412 END IF;
2413
2414 IF (l_rule_pass_flag = 'PASS') THEN
2415 IF (PV_DEBUG_HIGH_ON) THEN
2416 PVX_UTILITY_PVT.debug_message('PASS: x.program_id = '|| x.program_id);
2417 PVX_UTILITY_PVT.debug_message('PASS: p_partner_id = '|| p_partner_id);
2418 END IF;
2419 OPEN c_elig_program_id_seq;
2420 FETCH c_elig_program_id_seq INTO l_nextval;
2421 CLOSE c_elig_program_id_seq;
2422
2423 l_stmt_str := 'INSERT
2424 INTO pv_pg_elig_programs
2425 (
2426 ELIG_PROGRAM_ID,
2427 PROGRAM_ID,
2428 PARTNER_ID,
2429 ELIGIBILITY_CRIT_CODE,
2430 CREATION_DATE,
2431 CREATED_BY,
2432 LAST_UPDATE_DATE,
2433 LAST_UPDATED_BY,
2434 LAST_UPDATE_LOGIN ,
2435 OBJECT_Version_number
2436 )
2437 VALUES
2438 (
2439 :1,
2440 :2,
2441 :3,
2442 :4,
2443 :5,
2444 :6,
2445 :7,
2446 :8,
2447 :9,
2448 :10
2449 )';
2450
2451 EXECUTE IMMEDIATE l_stmt_str
2452 USING l_nextval,
2453 x.program_id,
2454 p_partner_id,
2455 'PREREQ',
2456 SYSDATE,
2457 l_user_id,
2458 SYSDATE,
2459 l_user_id,
2460 l_user_id,
2461 1.0;
2462 END IF;
2463 END IF;
2464 END IF;
2465 END LOOP;
2466
2467 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2468 IF (PV_DEBUG_HIGH_ON) THEN
2469 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
2470 END IF;
2471 END IF;
2472
2473 FND_MSG_PUB.Count_And_Get (
2474 p_encoded => FND_API.G_FALSE,
2475 p_count => x_msg_count,
2476 p_data => x_msg_data
2477 );
2478
2479 -- Standard check for p_commit
2480 IF FND_API.to_Boolean( p_commit ) THEN
2481 COMMIT WORK;
2482 END IF;
2483
2484 EXCEPTION
2485 WHEN PVX_Utility_PVT.resource_locked THEN
2486 x_return_status := FND_API.g_ret_sts_error;
2487 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
2488
2489 WHEN FND_API.G_EXC_ERROR THEN
2490 x_return_status := FND_API.G_RET_STS_ERROR;
2491 -- Standard call to get message count and if count=1, get the message
2492 FND_MSG_PUB.Count_And_Get (
2493 p_encoded => FND_API.G_FALSE,
2494 p_count => x_msg_count,
2495 p_data => x_msg_data
2496 );
2497
2498 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2500 -- Standard call to get message count and if count=1, get the message
2501 FND_MSG_PUB.Count_And_Get (
2502 p_encoded => FND_API.G_FALSE,
2503 p_count => x_msg_count,
2504 p_data => x_msg_data
2505 );
2506
2507 WHEN OTHERS THEN
2508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2509 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2510 THEN
2511 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2512 END IF;
2513 -- Standard call to get message count and if count=1, get the message
2514 FND_MSG_PUB.Count_And_Get (
2515 p_encoded => FND_API.G_FALSE,
2516 p_count => x_msg_count,
2517 p_data => x_msg_data
2518 );
2519 END update_elig_prgm_4_new_ptnr;
2520
2521 END PV_USER_MGMT_PVT;