[Home] [Help]
PACKAGE BODY: APPS.PV_USER_ROLE_PVT
Source
1 PACKAGE BODY PV_USER_ROLE_PVT AS
2 /* $Header: pvxvrolb.pls 115.5 2002/12/17 04:30:04 svnathan ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='PV_USER_ROLE_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(15) := 'pvxvrolb.pls';
6 G_PRIMARY_USER CONSTANT VARCHAR2(30) := 'PV_PARTNER_PRIMARY_USER_ENRL';
7 G_BUSINESS_USER CONSTANT VARCHAR2(30) := 'PV_PARTNER_BUSINESS_USER_ENRL';
8 G_APP_ID CONSTANT NUMBER := 691;
9
10
11
12
13 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
14 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
15 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
16
17 PROCEDURE ASSIGN_DEF_ROLES(
18 p_api_version_number IN NUMBER
19 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
20 ,p_commit IN VARCHAR2 := FND_API.g_false
21 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
22 ,P_USERNAME in VARCHAR2
23 ,P_USERTYPE in VARCHAR
24 ,x_return_status OUT NOCOPY VARCHAR2
25 ,x_msg_count OUT NOCOPY NUMBER
26 ,x_msg_data OUT NOCOPY VARCHAR2
27 )
28
29 IS
30
31 l_api_version CONSTANT NUMBER := 1.0;
32 l_api_name CONSTANT VARCHAR2(30) := 'ASSIGN_DEF_ROLES';
33 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
34
35 l_enrltype_id NUMBER;
36 l_usertype_key VARCHAR2(30);
37 l_principal_name VARCHAR2(255);
38 L_ENRLTYPE_KEY VARCHAR2(30);
39
40
41 cursor find_enrltype is select subscription_id from jtf_um_subscriptions_b
42 where subscription_key = L_ENRLTYPE_KEY
43 and (effective_end_date is null or effective_end_date > sysdate) ;
44
45
46 cursor primary_enroll_roles is select jusr.principal_name
47 from jtf_um_subscriptions_b jusb, jtf_um_subscription_role jusr
48 where subscription_key = L_ENRLTYPE_KEY
49 and jusb.subscription_id = jusr.subscription_id
50 and jusr.effective_start_date <= sysdate
51 and nvl(jusr.effective_end_date, sysdate) >= sysdate
52 and jusb.effective_start_date <= sysdate
53 and nvl(jusb.effective_end_date, sysdate) >= sysdate
54 and jusb.enabled_flag = 'Y';
55
56 cursor business_enroll_roles is select jusr.principal_name
57 from jtf_um_subscriptions_b jusb, jtf_um_subscription_role jusr
58 where subscription_key = L_ENRLTYPE_KEY
59 and jusb.subscription_id = jusr.subscription_id
60 and jusr.effective_start_date <= sysdate
61 and nvl(jusr.effective_end_date, sysdate) >= sysdate
62 and jusb.effective_start_date <= sysdate
63 and nvl(jusb.effective_end_date, sysdate) >= sysdate;
64
65
66
67 BEGIN
68 SAVEPOINT ASSIGN_DEF_ROLES;
69
70 IF (PV_DEBUG_HIGH_ON) THEN
71
72
73
74 PVX_Utility_PVT.debug_message(l_full_name||': start');
75
76 END IF;
77
78 IF FND_API.to_boolean(p_init_msg_list) THEN
79 FND_MSG_PUB.initialize;
80 END IF;
81
82
83 IF NOT FND_API.compatible_api_call(
84 l_api_version,
85 p_api_version_number,
86 l_api_name,
87 g_pkg_name
88 ) THEN
89 RAISE FND_API.g_exc_unexpected_error;
90 END IF;
91
92 x_return_status := FND_API.g_ret_sts_success;
93
94 IF(UPPER(P_USERTYPE) = 'PRIMARY') THEN
95 L_ENRLTYPE_KEY:=G_PRIMARY_USER;
96 ELSIF(UPPER(P_USERTYPE) = 'BUSINESS') THEN
97 L_ENRLTYPE_KEY:=G_BUSINESS_USER;
98 ELSE
99 --raise exception by adding apppropriate FND message as not valid user type passed in.
100 IF (PV_DEBUG_HIGH_ON) THEN
101 Pvx_Utility_Pvt.debug_message('ERROR: NOT valid USER TYPE IS passed IN.');
102 END IF;
103 --raise exception by adding apppropriate FND message as not valid user type passed in.
104 FND_MESSAGE.set_name('PV', 'PV_USER_ROLE_NO_TYPE');
105 FND_MSG_PUB.add;
106 x_return_status := FND_API.g_ret_sts_error;
107 RAISE FND_API.g_exc_error;
108 END IF;
109
110
111 open find_enrltype;
112 fetch find_enrltype into l_enrltype_id;
113 /* Validation of enrollment Key. */
114 IF find_enrltype%NOTFOUND THEN
115 FND_MESSAGE.set_name('PV', 'PV_USER_ROLE_NO_ENRL');
116 FND_MSG_PUB.add;
117 x_return_status := FND_API.g_ret_sts_error;
118 close find_enrltype;
119 RAISE FND_API.g_exc_error;
120 END IF;
121 close find_enrltype;
122
123
124
125 -- Find out primary default roles
126 IF(UPPER(P_USERTYPE) = 'PRIMARY') THEN
127
128 open primary_enroll_roles;
129
130 loop
131 fetch primary_enroll_roles into l_principal_name;
132 exit when primary_enroll_roles%NOTFOUND;
133
134
135 -- Assign default roles
136 if l_principal_name is not null then
137
138 -- Make sure that user name is not null
139 if P_USERNAME is not null then
140 JTF_AUTH_BULKLOAD_PKG.ASSIGN_ROLE
141 ( USER_NAME => P_USERNAME,
142 ROLE_NAME => l_principal_name,
143 OWNERTABLE_NAME => 'JTF_UM_SUBSCRIPTIONS_B',
144 OWNERTABLE_KEY => l_enrltype_id
145 -- ,APP_ID => 691
146 );
147 end if;
148
149 end if;
150 end loop;
151 close primary_enroll_roles;
152
153 ELSIF (UPPER(P_USERTYPE) = 'BUSINESS') THEN
154 -- Find out business default roles
155
156 open business_enroll_roles;
157
158 loop
159 fetch business_enroll_roles into l_principal_name;
160 exit when business_enroll_roles%NOTFOUND;
161
162
163 -- Assign default roles
164 if l_principal_name is not null then
165
166 -- Make sure that user name is not null
167 if P_USERNAME is not null then
168 JTF_AUTH_BULKLOAD_PKG.ASSIGN_ROLE
169 ( USER_NAME => P_USERNAME,
170 ROLE_NAME => l_principal_name,
171 OWNERTABLE_NAME => 'JTF_UM_SUBSCRIPTIONS_B',
172 OWNERTABLE_KEY => l_enrltype_id
173 -- ,APP_ID => 691
174 );
175 end if;
176
177 end if;
178 end loop;
179 close business_enroll_roles;
180 END IF;
181
182
183 -- Check for commit
184 IF FND_API.to_boolean(p_commit) THEN
185 COMMIT;
186 END IF;
187
188 FND_MSG_PUB.count_and_get(
189 p_encoded => FND_API.g_false,
190 p_count => x_msg_count,
191 p_data => x_msg_data
192
193 );
194
195 IF (PV_DEBUG_HIGH_ON) THEN
196
197
198
199 PVX_Utility_PVT.debug_message(l_full_name ||': end');
200
201 END IF;
202
203 EXCEPTION
204 WHEN FND_API.g_exc_error THEN
205 ROLLBACK TO ASSIGN_DEF_ROLES;
206 x_return_status := FND_API.g_ret_sts_error;
207 FND_MSG_PUB.count_and_get (
208 p_encoded => FND_API.g_false
209 ,p_count => x_msg_count
210 ,p_data => x_msg_data
211 );
212
213
214 WHEN FND_API.g_exc_unexpected_error THEN
215 ROLLBACK TO ASSIGN_DEF_ROLES;
216 x_return_status := FND_API.g_ret_sts_unexp_error ;
217 FND_MSG_PUB.count_and_get (
218 p_encoded => FND_API.g_false
219 ,p_count => x_msg_count
220 ,p_data => x_msg_data
221 );
222
223
224 WHEN OTHERS THEN
225 ROLLBACK TO ASSIGN_DEF_ROLES;
226
227 x_return_status := FND_API.g_ret_sts_unexp_error ;
228
229 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
230 THEN
231 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
232 END IF;
233
234 FND_MSG_PUB.count_and_get(
235 p_encoded => FND_API.g_false
236 ,p_count => x_msg_count
237 ,p_data => x_msg_data
238 );
239
240
241 END ASSIGN_DEF_ROLES;
242
243
244
245
246
247
248
249
250 END PV_USER_ROLE_PVT;