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