[Home] [Help]
PACKAGE BODY: APPS.CAC_CAL_PRIVS_PVT
Source
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_CAL_GRANTS';
1 PACKAGE BODY CAC_CAL_PRIVS_PVT AS
2 /* $Header: caccpvb.pls 120.1 2006/07/28 09:13:21 sankgupt noship $ */
3
5
6 CURSOR C_LOGGEDIN_RESOURCE IS
7 select resource_id, resource_type
8 from cac_cal_resources
9 where user_name = FND_GLOBAL.USER_NAME;
10
11 CURSOR C_GRANTEE_USER
12 (
13 b_resource_id NUMBER,
14 b_resource_type VARCHAR2
15 ) IS
16 select user_name
17 from cac_cal_resources
18 where resource_id = b_resource_id
19 and resource_type = b_resource_type;
20
21 CURSOR C_INSTANCE_SET_ID IS
22 select instance_set_id from fnd_object_instance_sets
23 where instance_set_name = 'JTF_TASK_RESOURCE_TASKS';
24
25 PROCEDURE INSERT_GRANTS
26 ( p_grantee IN VARCHAR2
27 , p_resource_id IN NUMBER
28 , p_resource_type IN VARCHAR2
29 , p_instance_set_id IN NUMBER
30 , p_start_date IN DATE
31 , p_end_date IN DATE
32 , p_appointment_access IN VARCHAR2
33 , p_task_access IN VARCHAR2
34 , p_booking_access IN VARCHAR2
35 ) IS
36
37 l_grant_guid RAW(16);
38 l_return_status VARCHAR2(1);
39 l_error NUMBER;
40
41 BEGIN
42
43 IF (p_appointment_access IS NOT NULL)
44 THEN
45 fnd_grants_pkg.grant_function
46 ( p_api_version => 1.0
47 , p_menu_name => p_appointment_access
48 , p_instance_type => 'INSTANCE'
49 , p_object_name => 'CAC_CAL_RESOURCES'
50 , p_instance_pk1_value => TO_CHAR(p_resource_id)
51 , p_instance_pk2_value => p_resource_type
52 , p_grantee_type => 'USER'
53 , p_grantee_key => p_grantee
54 , p_start_date => p_start_date
55 , p_end_date => p_end_Date
56 , p_program_name => 'CALENDAR'
57 , p_program_tag => 'CAC_CAL_ACCESS'
58 , x_grant_guid => l_grant_guid
59 , x_success => l_return_status
60 , x_errorcode => l_error
61 , p_name => 'Calendar: Appointment Access'
62 , p_description => 'This is used for delgating calendar access'
63 );
64
65 IF (l_return_status <> FND_API.G_TRUE)
66 THEN
67 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
68 END IF;
69 END IF;
70
71 IF (p_booking_access IS NOT NULL)
72 THEN
73 fnd_grants_pkg.grant_function
74 ( p_api_version => 1.0
75 , p_menu_name => p_booking_access
76 , p_instance_type => 'INSTANCE'
77 , p_object_name => 'CAC_CAL_RESOURCES'
78 , p_instance_pk1_value => TO_CHAR(p_resource_id)
79 , p_instance_pk2_value => p_resource_type
80 , p_grantee_type => 'USER'
81 , p_grantee_key => p_grantee
82 , p_start_date => p_start_date
83 , p_end_date => p_end_Date
84 , p_program_name => 'CALENDAR'
85 , p_program_tag => 'CAC_CAL_ACCESS'
86 , x_grant_guid => l_grant_guid
87 , x_success => l_return_status
88 , x_errorcode => l_error
89 , p_name => 'Calendar: Booking Access'
90 , p_description => 'This is used for delgating calendar access'
91 );
92
93 IF (l_return_status <> FND_API.G_TRUE)
94 THEN
95 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96 END IF;
97 END IF;
98
99 IF (p_task_access IS NOT NULL)
100 THEN
101 fnd_grants_pkg.grant_function
102 ( p_api_version => 1.0
103 , p_menu_name => p_task_access
104 , p_instance_type => 'INSTANCE'
105 , p_object_name => 'CAC_CAL_RESOURCES'
106 , p_instance_pk1_value => TO_CHAR(p_resource_id)
107 , p_instance_pk2_value => p_resource_type
108 , p_grantee_type => 'USER'
109 , p_grantee_key => p_grantee
110 , p_start_date => p_start_date
111 , p_end_date => p_end_Date
112 , p_program_name => 'CALENDAR'
113 , p_program_tag => 'CAC_CAL_ACCESS'
114 , x_grant_guid => l_grant_guid
115 , x_success => l_return_status
116 , x_errorcode => l_error
117 , p_name => 'Calendar: Task Access'
118 , p_description => 'This is used for delgating calendar access'
119 );
120
121 IF (l_return_status <> FND_API.G_TRUE)
122 THEN
123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
124 END IF;
125
126 fnd_grants_pkg.grant_function
127 ( p_api_version => 1.0
128 , p_menu_name => p_task_access
132 , p_grantee_type => 'USER'
129 , p_instance_type => 'SET'
130 , p_instance_set_id => p_instance_set_id
131 , p_object_name => 'JTF_TASKS'
133 , p_grantee_key => p_grantee
134 , p_start_date => p_start_date
135 , p_end_date => p_end_Date
136 , p_program_name => 'CALENDAR'
137 , p_program_tag => 'CAC_CAL_ACCESS'
138 , p_parameter1 => TO_CHAR(p_resource_id)
139 , x_grant_guid => l_grant_guid
140 , x_success => l_return_status
141 , x_errorcode => l_error
142 , p_name => 'Calendar: Task Data Access'
143 , p_description => 'This is used for delgating calendar access'
144 );
145
146 IF (l_return_status <> FND_API.G_TRUE)
147 THEN
148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
149 END IF;
150
151 END IF;
152
153 END INSERT_GRANTS;
154
155
156 PROCEDURE CREATE_GRANTS
157 ( p_grantee_user_name IN VARCHAR2
158 , p_grantee_start_date IN DATE
159 , p_grantee_end_date IN DATE
160 , p_appointment_access IN VARCHAR2
161 , p_task_access IN VARCHAR2
162 , p_booking_access IN VARCHAR2
163 , x_return_status OUT NOCOPY VARCHAR2
164 , x_msg_count OUT NOCOPY NUMBER
165 , x_msg_data OUT NOCOPY VARCHAR2
166 ) IS
167
168
169 l_api_name CONSTANT VARCHAR2(30) := 'create_grants';
170 l_resource_id NUMBER;
171 l_resource_type VARCHAR2(80);
172 l_instance_set_id NUMBER;
173
174 BEGIN
175
176 x_return_status := FND_API.G_RET_STS_SUCCESS;
177
178 OPEN C_LOGGEDIN_RESOURCE;
179 FETCH C_LOGGEDIN_RESOURCE
180 INTO l_resource_id,l_resource_type;
181 CLOSE C_LOGGEDIN_RESOURCE;
182
183 OPEN C_INSTANCE_SET_ID;
184 FETCH C_INSTANCE_SET_ID
185 INTO l_instance_set_id;
186 CLOSE C_INSTANCE_SET_ID;
187
188 INSERT_GRANTS
189 ( p_grantee => p_grantee_user_name
190 , p_resource_id => l_resource_id
191 , p_resource_type => l_resource_type
192 , p_instance_set_id => l_instance_set_id
193 , p_start_date => p_grantee_start_date
194 , p_end_date => p_grantee_end_date
195 , p_appointment_access => p_appointment_access
196 , p_task_access => p_task_access
197 , p_booking_access => p_booking_access
198 );
199
200 /***************************************************************************
201 ** Standard call to get message count and if count is > 1, get message info
202 ***************************************************************************/
203 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
204 , p_data => x_msg_data
205 );
206
207 EXCEPTION
208 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
209 THEN
210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
211 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
212 , p_data => x_msg_data
213 );
214 WHEN OTHERS
215 THEN
216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
217 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
218 THEN
219 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
220 , l_api_name
221 );
222 END IF;
223 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
224 , p_data => x_msg_data
225 );
226
227 END CREATE_GRANTS;
228
229
230 PROCEDURE UPDATE_GRANTS
231 ( p_grantee_user_name IN VARCHAR2
232 , p_grantee_start_date IN DATE
233 , p_grantee_end_date IN DATE
234 , p_appointment_access IN VARCHAR2
235 , p_task_access IN VARCHAR2
236 , p_booking_access IN VARCHAR2
237 , x_return_status OUT NOCOPY VARCHAR2
238 , x_msg_count OUT NOCOPY NUMBER
239 , x_msg_data OUT NOCOPY VARCHAR2
240 ) IS
241
242 CURSOR C_GET_GRANTS
243 (
244 b_grantee VARCHAR2,
245 b_resource_id NUMBER,
246 b_resource_type VARCHAR2,
247 b_instance_set_id NUMBER
248 ) IS
249 SELECT fgs.grant_guid
250 FROM FND_GRANTS fgs
251 , FND_MENUS fmu
252 , FND_OBJECTS fos
253 WHERE fgs.object_id = fos.object_id
254 AND fos.obj_name = 'CAC_CAL_RESOURCES'
255 AND fgs.menu_id = fmu.menu_id
256 AND fmu.menu_name IN ( 'JTF_CAL_READ_ACCESS'
257 , 'JTF_CAL_FULL_ACCESS'
258 , 'JTF_TASK_READ_ONLY'
259 , 'JTF_TASK_FULL_ACCESS'
260 , 'CAC_BKG_READ_ONLY_ACCESS'
261 )
262 AND fgs.grantee_type = 'USER'
263 AND fgs.grantee_key = b_grantee
264 AND fgs.instance_type = 'INSTANCE'
265 AND fgs.instance_pk1_value = TO_CHAR(b_resource_id)
266 AND fgs.instance_pk2_value = b_resource_type
267 AND fgs.program_tag = 'CAC_CAL_ACCESS'
268 UNION ALL
269 SELECT fgs.grant_guid
270 FROM FND_GRANTS fgs
271 , FND_MENUS fmu
272 , FND_OBJECTS fos
273 WHERE fgs.object_id = fos.object_id
274 AND fos.obj_name = 'JTF_TASKS'
275 AND fgs.menu_id = fmu.menu_id
276 AND fmu.menu_name IN ( 'JTF_TASK_READ_ONLY'
277 , 'JTF_TASK_FULL_ACCESS'
278 )
279 AND fgs.grantee_type = 'USER'
280 AND fgs.grantee_key = b_grantee
281 AND fgs.instance_type = 'SET'
282 AND fgs.instance_set_id = b_instance_set_id
283 AND fgs.parameter1 = TO_CHAR(b_resource_id)
284 AND fgs.PROGRAM_TAG = 'CAC_CAL_ACCESS' ;
285
286
287 l_api_name CONSTANT VARCHAR2(30) := 'update_grants';
288 l_resource_id NUMBER;
289 l_resource_type VARCHAR2(80);
290 l_grantee VARCHAR2(100);
291 l_instance_set_id NUMBER;
292 l_return_status VARCHAR2(1);
293 l_error NUMBER;
294
295 BEGIN
296
297 x_return_status := FND_API.G_RET_STS_SUCCESS;
298
299 OPEN C_LOGGEDIN_RESOURCE;
300 FETCH C_LOGGEDIN_RESOURCE
301 INTO l_resource_id,l_resource_type;
302 CLOSE C_LOGGEDIN_RESOURCE;
303
304 OPEN C_INSTANCE_SET_ID;
305 FETCH C_INSTANCE_SET_ID
306 INTO l_instance_set_id;
307 CLOSE C_INSTANCE_SET_ID;
308
309 FOR ref_cursor IN C_GET_GRANTS(p_grantee_user_name,l_resource_id,l_resource_type,l_instance_set_id)
310 LOOP
311 fnd_grants_pkg.revoke_grant
312 ( p_api_version => 1.0
313 , p_grant_guid => ref_cursor.grant_guid
314 , x_success => l_return_status
315 , x_errorcode => l_error
316 );
317
318 IF (l_return_status <> FND_API.G_TRUE)
319 THEN
320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
321 END IF;
322 END LOOP;
323
324 INSERT_GRANTS
325 ( p_grantee => p_grantee_user_name
326 , p_resource_id => l_resource_id
327 , p_resource_type => l_resource_type
328 , p_instance_set_id => l_instance_set_id
329 , p_start_date => p_grantee_start_date
330 , p_end_date => p_grantee_end_date
331 , p_appointment_access => p_appointment_access
332 , p_task_access => p_task_access
333 , p_booking_access => p_booking_access
334 );
335
336 /***************************************************************************
337 ** Standard call to get message count and if count is > 1, get message info
338 ***************************************************************************/
339 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
340 , p_data => x_msg_data
341 );
342
343 EXCEPTION
344 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
345 THEN
346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
347 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
348 , p_data => x_msg_data
349 );
350 WHEN OTHERS
351 THEN
352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
353 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
354 THEN
355 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
356 , l_api_name
357 );
358 END IF;
359 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
360 , p_data => x_msg_data
361 );
362
363 END UPDATE_GRANTS;
364
365
366 END CAC_CAL_PRIVS_PVT;