[Home] [Help]
PACKAGE BODY: APPS.AMS_ACCESS_DENORM_PVT
Source
1 PACKAGE BODY AMS_access_denorm_PVT AS
2 /* $Header: amsvdenb.pls 120.2 2010/05/19 08:04:16 amlal ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30):='AMS_access_denorm_PVT';
4 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
5 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
6 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
7
8 /* sunkumar: 02-10-03 overloaded insert resource to error_message also */
9
10 PROCEDURE insert_resource( p_resource_id IN NUMBER
11 , p_object_type IN VARCHAR2
12 , p_object_id IN NUMBER
13 , p_edit_metrics IN VARCHAR2
14 , x_return_status OUT NOCOPY VARCHAR2
15 , x_msg_count OUT NOCOPY NUMBER
16 , x_msg_data OUT NOCOPY VARCHAR2
17 )
18 IS
19
20 l_api_name CONSTANT VARCHAR2(30) := 'insert_resource';
21
22 BEGIN
23
24 insert_resource( p_resource_id => p_resource_id
25 , p_object_type => p_object_type
26 , p_object_id => p_object_id
27 , p_edit_metrics => p_edit_metrics
28 );
29
30 x_return_status := FND_API.G_RET_STS_SUCCESS;
31
32 EXCEPTION
33 WHEN OTHERS THEN
34 x_return_status := FND_API.g_ret_sts_unexp_error ;
35 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
36 THEN
37 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
38 END IF;
39 FND_MSG_PUB.count_and_get (
40 p_encoded => FND_API.g_false,
41 p_count => x_msg_count,
42 p_data => x_msg_data
43 );
44
45 END;
46
47
48
49 PROCEDURE insert_resource( p_resource_id IN NUMBER
50 , p_object_type IN VARCHAR2
51 , p_object_id IN NUMBER
52 , p_edit_metrics IN VARCHAR2
53 , x_return_status OUT NOCOPY VARCHAR2
54 )
55 IS
56 BEGIN
57
58 insert_resource( p_resource_id => p_resource_id
59 , p_object_type => p_object_type
60 , p_object_id => p_object_id
61 , p_edit_metrics => p_edit_metrics
62 );
63
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65
66 EXCEPTION
67 WHEN OTHERS THEN
68 x_return_status := FND_API.G_RET_STS_ERROR;
69 END;
70
71
72
73 /* sunkumar: 02-10-03 overloaded update resource to add error_message also */
74 PROCEDURE update_resource( p_resource_id IN NUMBER
75 , p_object_type IN VARCHAR2
76 , p_object_id IN NUMBER
77 , p_edit_metrics IN VARCHAR2
78 , x_return_status OUT NOCOPY VARCHAR2
79 , x_msg_count OUT NOCOPY NUMBER
80 , x_msg_data OUT NOCOPY VARCHAR2
81 )
82 IS
83
84 l_api_name CONSTANT VARCHAR2(30) := 'update_resource';
85
86 BEGIN
87
88 update_resource( p_resource_id => p_resource_id
89 , p_object_type => p_object_type
90 , p_object_id => p_object_id
91 , p_edit_metrics => p_edit_metrics
92 );
93
94 x_return_status := FND_API.G_RET_STS_SUCCESS;
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 x_return_status := FND_API.g_ret_sts_unexp_error ;
99 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
100 THEN
101 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
102 END IF;
103 FND_MSG_PUB.count_and_get (
104 p_encoded => FND_API.g_false,
105 p_count => x_msg_count,
106 p_data => x_msg_data
107 );
108
109 END;
110
111
112 PROCEDURE update_resource( p_resource_id IN NUMBER
113 , p_object_type IN VARCHAR2
114 , p_object_id IN NUMBER
115 , p_edit_metrics IN VARCHAR2
116 , x_return_status OUT NOCOPY VARCHAR2
117 )
118 IS
119 BEGIN
120
121 update_resource( p_resource_id => p_resource_id
122 , p_object_type => p_object_type
123 , p_object_id => p_object_id
124 , p_edit_metrics => p_edit_metrics
125 );
126
127 x_return_status := FND_API.G_RET_STS_SUCCESS;
128
129 EXCEPTION
130 WHEN OTHERS THEN
131 x_return_status := FND_API.G_RET_STS_ERROR;
132 END;
133
134
135 /* sunkumar: 02-10-03 overloaded delete resource to add error_message also */
136 PROCEDURE delete_resource( p_resource_id IN NUMBER
137 , p_object_type IN VARCHAR2
138 , p_object_id IN NUMBER
139 , p_edit_metrics IN VARCHAR2
140 , x_return_status OUT NOCOPY VARCHAR2
141 , x_msg_count OUT NOCOPY NUMBER
142 , x_msg_data OUT NOCOPY VARCHAR2
143 )
144 IS
145
146 l_api_name CONSTANT VARCHAR2(30) := 'delete_resource';
147
148 BEGIN
149
150 delete_resource( p_resource_id => p_resource_id
151 , p_object_type => p_object_type
152 , p_object_id => p_object_id
153 , p_edit_metrics => p_edit_metrics
154 );
155
156 x_return_status := FND_API.G_RET_STS_SUCCESS;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 x_return_status := FND_API.g_ret_sts_unexp_error ;
161 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
162 THEN
163 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
164 END IF;
165 FND_MSG_PUB.count_and_get (
166 p_encoded => FND_API.g_false,
167 p_count => x_msg_count,
168 p_data => x_msg_data
169 );
170
171 END;
172
173
174 PROCEDURE delete_resource( p_resource_id IN NUMBER
175 , p_object_type IN VARCHAR2
176 , p_object_id IN NUMBER
177 , p_edit_metrics IN VARCHAR2
178 , x_return_status OUT NOCOPY VARCHAR2
179 )
180 IS
181 BEGIN
182
183 delete_resource( p_resource_id => p_resource_id
184 , p_object_type => p_object_type
185 , p_object_id => p_object_id
186 , p_edit_metrics => p_edit_metrics
187 );
188
189 x_return_status := FND_API.G_RET_STS_SUCCESS;
190 EXCEPTION
191 WHEN OTHERS THEN
192 x_return_status := FND_API.G_RET_STS_ERROR;
193 END;
194
195 PROCEDURE insert_resource( p_resource_id IN NUMBER
196 , p_object_type IN VARCHAR2
197 , p_object_id IN NUMBER
198 , p_edit_metrics IN VARCHAR2
199 )
200 IS
201 l_user_id NUMBER := fnd_global.user_id;
202 l_login_id NUMBER := fnd_global.conc_login_id;
203 l_sysdate DATE := SYSDATE;
204 BEGIN
205
206 INSERT INTO ams_act_access_denorm
207 ( access_denorm_id
208 , resource_id
209 , edit_metrics_yn
210 , object_type
211 , object_id
212 , source_code
213 , creation_date
214 , created_by
215 , last_update_date
216 , last_updated_by
217 , last_update_login
218 )
219 SELECT ams_act_access_denorm_s.nextval
220 , p_resource_id
221 , p_edit_metrics
222 , p_object_type
223 , p_object_id
224 , ams_access_pvt.get_source_code(p_object_type,p_object_id)
225 , l_sysdate
226 , l_user_id
227 , l_sysdate
228 , l_user_id
229 , l_login_id
230 FROM dual
231 WHERE NOT EXISTS ( SELECT 1
232 FROM ams_act_access_denorm
233 WHERE resource_id = p_resource_id
234 AND object_type = p_object_type
235 AND object_id = p_object_id
236 );
237
238 IF SQL%NOTFOUND THEN
239 IF p_edit_metrics = 'Y' THEN
240 UPDATE ams_act_access_denorm
241 SET edit_metrics_yn = p_edit_metrics,
242 last_updated_by = l_user_id,
243 last_update_date = l_sysdate,
244 last_update_login = l_login_id
245 WHERE object_type = p_object_type
246 AND object_id = p_object_id
247 AND resource_id = p_resource_id
248 AND edit_metrics_yn = 'N' ;
249 END IF;
250 END IF;
251 END insert_resource;
252
253
254 PROCEDURE update_resource( p_resource_id IN NUMBER
255 , p_object_type IN VARCHAR2
256 , p_object_id IN NUMBER
257 , p_edit_metrics IN VARCHAR2
258 )
259 IS
260 l_user_id NUMBER := fnd_global.user_id;
261 l_login_id NUMBER := fnd_global.conc_login_id;
262 l_sysdate DATE := SYSDATE;
263
264 CURSOR what_is_edit_metrics( c_resource_id IN NUMBER
265 , c_object_type IN VARCHAR2
266 , c_object_id IN NUMBER
267 )
268 IS
269 SELECT edit_metrics_yn
270 FROM ams_act_access_denorm
271 WHERE object_type = p_object_type
272 AND object_id = p_object_id
273 AND resource_id = p_resource_id;
274
275 l_edit_metrics VARCHAR2(1);
276
277 BEGIN
278
279 IF p_edit_metrics = 'Y' THEN
280
281 UPDATE ams_act_access_denorm
282 SET edit_metrics_yn = p_edit_metrics
283 , last_updated_by = l_user_id
284 , last_update_date = l_sysdate
285 , last_update_login = l_login_id
286 WHERE object_type = p_object_type
287 AND object_id = p_object_id
288 AND resource_id = p_resource_id
289 AND edit_metrics_yn = 'N';
290
291 ELSIF p_edit_metrics = 'N' THEN
292
293 OPEN what_is_edit_metrics( p_resource_id
294 , p_object_type
295 , p_object_id);
296 FETCH what_is_edit_metrics INTO l_edit_metrics;
297 CLOSE what_is_edit_metrics;
298
299 IF l_edit_metrics <> 'N' THEN
300
301 UPDATE ams_act_access_denorm aacd
302 SET edit_metrics_yn = p_edit_metrics
303 , last_updated_by = l_user_id
304 , last_update_date = l_sysdate
305 , last_update_login = l_login_id
306 WHERE object_type = p_object_type
307 AND object_id = p_object_id
308 AND resource_id = p_resource_id
309 AND edit_metrics_yn = 'Y'
310 AND not exists ( SELECT 1
311 FROM ams_act_access aac,
312 jtf_rs_groups_denorm jgd,
313 jtf_rs_group_members jgm
314 WHERE aac.arc_act_access_to_object = p_object_type
315 AND aac.act_access_to_object_id = p_object_id
316 AND arc_user_or_role_type = 'GROUP'
317 AND user_or_role_id = jgd.parent_group_id
318 AND jgd.group_id = jgm.group_id
319 AND jgd.start_date_active <= TRUNC(SYSDATE)
320 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
321 AND jgm.delete_flag='N'
322 AND jgm.resource_id = aacd.resource_id
323 AND aac.delete_flag = 'N'
324 AND aac.admin_flag='Y' );
325 END IF;
326
327 END IF;
328
329 END;
330
331 PROCEDURE delete_resource( p_resource_id IN NUMBER
332 , p_object_type IN VARCHAR2
333 , p_object_id IN NUMBER
334 , p_edit_metrics IN VARCHAR2
335 )
336 IS
337 l_user_id NUMBER := fnd_global.user_id;
338 l_login_id NUMBER := fnd_global.conc_login_id;
339 l_sysdate DATE := SYSDATE;
340
341 BEGIN
342
343 DELETE FROM AMS_ACT_ACCESS_DENORM aacd
344 WHERE resource_id = p_resource_id
345 AND object_type = p_object_type
346 AND object_id = p_object_id
347 AND not exists ( SELECT 1
348 FROM ams_act_access aac,
349 jtf_rs_groups_denorm jgd,
350 jtf_rs_group_members jgm -- INtroduce soft DELETE flag FOR resources.
351 WHERE aac.arc_act_access_to_object = p_object_type
352 AND aac.act_access_to_object_id = p_object_id
353 AND arc_user_or_role_type = 'GROUP'
354 AND user_or_role_id = jgd.parent_group_id
355 AND jgd.group_id = jgm.group_id
356 AND jgd.start_date_active <= TRUNC(SYSDATE)
357 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
358 AND jgm.resource_id = p_resource_id
359 AND jgm.delete_flag = 'N'
360 AND aac.delete_flag = 'N'
361 UNION ALL
362 SELECT 1
363 FROM ams_act_access
364 WHERE arc_act_access_to_object = p_object_type
365 AND act_access_to_object_id = p_object_id
366 AND arc_user_or_role_type = 'USER'
367 AND user_or_role_id = aacd.resource_id
368 );
369
370
371 IF p_edit_metrics = 'Y' THEN
372 UPDATE ams_act_access_denorm aacd
373 SET edit_metrics_yn = 'N'
374 , last_updated_by = l_user_id
375 , last_update_date = l_sysdate
376 , last_update_login = l_login_id
377 WHERE object_type = p_object_type
378 AND object_id = p_object_id
379 AND resource_id = p_resource_id
380 AND resource_id not IN (SELECT jgm.resource_id
381 FROM ams_act_access aac,
382 jtf_rs_groups_denorm jgd,
383 jtf_rs_group_members jgm
384 WHERE aac.arc_act_access_to_object = p_object_type
385 AND aac.act_access_to_object_id = p_object_id
386 AND arc_user_or_role_type = 'GROUP'
387 AND user_or_role_id = jgd.parent_group_id
388 AND jgd.group_id = jgm.group_id
389 AND jgd.start_date_active <= TRUNC(SYSDATE)
390 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
391 AND jgm.delete_flag='N'
392 AND jgm.resource_id = aacd.resource_id
393 AND aac.delete_flag = 'N')
394 AND edit_metrics_yn = 'Y' ;
395
396 END IF;
397
398 END;
399
400 PROCEDURE insert_group( p_group_id IN NUMBER
401 , p_object_type IN VARCHAR2
402 , p_object_id IN NUMBER
403 , p_edit_metrics IN VARCHAR2
404 )
405 IS
409 BEGIN
406 l_user_id NUMBER := fnd_global.user_id;
407 l_login_id NUMBER := fnd_global.conc_login_id;
408 l_sysdate DATE := SYSDATE;
410
411 -- If the resource already exists as part of another group or as user AND
412 -- if that group or user's edit metrics is 'N', update to 'Y'.
413
414 IF p_edit_metrics = 'Y' THEN
415 UPDATE ams_act_access_denorm aacd
416 SET edit_metrics_yn = p_edit_metrics
417 , last_updated_by = l_user_id
418 , last_update_date = l_sysdate
419 , last_update_login = l_login_id
420 WHERE object_type = p_object_type
421 AND object_id = p_object_id
422 AND resource_id IN ( SELECT jgm.resource_id
423 FROM jtf_rs_groups_denorm jgd,
424 jtf_rs_group_members jgm
425 WHERE jgd.parent_group_id = p_group_id
426 AND jgd.group_id = jgm.group_id
427 AND jgd.start_date_active <= TRUNC(SYSDATE)
428 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
429 AND jgm.delete_flag='N'
430 )
431 AND edit_metrics_yn = 'N' ;
432 END IF;
433
434 -- insert if a resource in a group doesnot exist in the denorm
435 INSERT INTO ams_act_access_denorm
436 ( access_denorm_id
437 , resource_id
438 , edit_metrics_yn
439 , object_type
440 , object_id
441 , source_code
442 , creation_date
443 , created_by
444 , last_update_date
445 , last_updated_by
446 , last_update_login
447 )
448 SELECT ams_act_access_denorm_s.nextval
449 , resource_id
450 , p_edit_metrics
451 , p_object_type
452 , p_object_id
453 , ams_access_pvt.get_source_code(p_object_type,p_object_id)
454 , l_sysdate
455 , l_user_id
456 , l_sysdate
457 , l_user_id
458 , l_login_id
459 FROM (
460 SELECT DISTINCT resource_id
461 FROM jtf_rs_groups_denorm jgd,
462 jtf_rs_group_members jgm
463 WHERE jgd.parent_group_id = p_group_id
464 AND jgd.group_id = jgm.group_id
465 AND jgd.start_date_active <= TRUNC(SYSDATE)
466 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
467 AND jgm.delete_flag='N'
468 AND NOT EXISTS ( SELECT 1
469 FROM ams_act_access_denorm
470 WHERE resource_id = jgm.resource_id
471 AND object_type = p_object_type
472 AND object_id = p_object_id)
473 );
474 end insert_group;
475
476
477 PROCEDURE update_group( p_group_id IN NUMBER
478 , p_object_type IN VARCHAR2
479 , p_object_id IN NUMBER
480 , p_edit_metrics IN VARCHAR2
481 )
482 IS
483 l_user_id NUMBER := fnd_global.user_id;
484 l_login_id NUMBER := fnd_global.conc_login_id;
485 l_sysdate DATE := SYSDATE;
486
487 BEGIN
488
489 IF p_edit_metrics = 'Y' THEN
490 UPDATE ams_act_access_denorm aacd
491 SET edit_metrics_yn = p_edit_metrics
492 , last_updated_by = l_user_id
493 , last_update_date = l_sysdate
494 , last_update_login = l_login_id
495 WHERE object_type = p_object_type
496 AND object_id = p_object_id
497 AND edit_metrics_yn = 'N'
498 AND EXISTS ( SELECT 1
499 FROM ams_act_access aac,
500 jtf_rs_groups_denorm jgd,
501 jtf_rs_group_members jgm
502 WHERE aac.arc_act_access_to_object = p_object_type
503 AND aac.act_access_to_object_id = p_object_id
504 AND arc_user_or_role_type = 'GROUP'
505 AND user_or_role_id = p_group_id
506 AND user_or_role_id = jgd.parent_group_id
507 AND jgd.group_id = jgm.group_id
508 AND jgd.start_date_active <= TRUNC(SYSDATE)
509 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
510 AND jgm.delete_flag='N'
511 AND jgm.resource_id = aacd.resource_id
512 AND aac.delete_flag = 'N' );
513
514 ELSIF p_edit_metrics = 'N' THEN
515 -- individual owners allready in denorm,
516 -- update entries if the resource is part of the group heirarchy
517 -- AND the resource is not part of a group which has edit metrics as 'Y'
518 -- added by VMODUR 13-MAR-2003
519 -- The Owner may be part of the above groups and should not be updated
520 UPDATE ams_act_access_denorm aacd
521 SET edit_metrics_yn = p_edit_metrics
522 , last_updated_by = l_user_id
523 , last_update_date = l_sysdate
524 , last_update_login = l_login_id
525 WHERE object_type = p_object_type
526 AND object_id = p_object_id
527 AND edit_metrics_yn = 'Y'
528 /* Roliing back perf suggested change
529 AND EXISTS ( SELECT 1
530 FROM ams_act_access aac,
531 jtf_rs_groups_denorm jgd,
532 jtf_rs_group_members jgm
533 WHERE aac.arc_act_access_to_object = p_object_type
534 AND aac.act_access_to_object_id = p_object_id
535 AND arc_user_or_role_type = 'GROUP'
536 AND user_or_role_id = jgd.parent_group_id
537 AND jgd.group_id = jgm.group_id
538 AND jgd.start_date_active <= TRUNC(SYSDATE)
542 AND aac.delete_flag = 'N'
539 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
540 AND jgm.delete_flag='N'
541 AND jgm.resource_id = aacd.resource_id
543 AND NVL(aac.admin_flag,'N')='N' --anchaudh 21-MAR-03
544 )
545 */
546 AND EXISTS ( SELECT 1
547 FROM ams_act_access aac,
548 jtf_rs_groups_denorm jgd,
549 jtf_rs_group_members jgm
550 WHERE aac.arc_act_access_to_object = p_object_type
551 AND aac.act_access_to_object_id = p_object_id
552 AND arc_user_or_role_type = 'GROUP'
553 AND user_or_role_id = jgd.parent_group_id
554 AND jgd.group_id = jgm.group_id
555 AND jgd.start_date_active <= TRUNC(SYSDATE)
556 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
557 AND jgm.delete_flag='N'
558 AND jgm.resource_id = aacd.resource_id
559 AND aac.delete_flag = 'N'
560 )
561 AND NOT EXISTS ( SELECT 1
562 FROM ams_act_access aac,
563 jtf_rs_groups_denorm jgd,
564 jtf_rs_group_members jgm
565 WHERE aac.arc_act_access_to_object = p_object_type
566 AND aac.act_access_to_object_id = p_object_id
567 AND arc_user_or_role_type = 'GROUP'
568 AND user_or_role_id = jgd.parent_group_id
569 AND jgd.group_id = jgm.group_id
570 AND jgd.start_date_active <= TRUNC(SYSDATE)
571 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
572 AND jgm.delete_flag='N'
573 AND jgm.resource_id = aacd.resource_id
574 AND aac.delete_flag = 'N'
575 AND aac.admin_flag='Y'
576 )
577 AND NOT EXISTS ( SELECT 1
578 FROM ams_act_access aac
579 WHERE aac.act_access_to_object_id = p_object_id
580 AND aac.arc_act_access_to_object = p_object_type
581 AND aac.user_or_role_id = aacd.resource_id
582 AND aac.arc_user_or_role_type = 'USER'
583 AND aac.delete_flag = 'N'
584 AND aac.admin_flag = 'Y'
585 );
586 END IF;
587 END update_group;
588
589 PROCEDURE delete_group( p_group_id IN NUMBER
590 , p_object_type IN VARCHAR2
591 , p_object_id IN NUMBER
592 , p_edit_metrics IN VARCHAR2
593 )
594 IS
595 l_user_id NUMBER := fnd_global.user_id;
596 l_login_id NUMBER := fnd_global.conc_login_id;
597 l_sysdate DATE := SYSDATE;
598 -- First DELETE groups
599 BEGIN
600 -- Delete if resource belongs to the group that is being deleted
601 -- AND it does not exist as part of any other group
602 -- or exist as 'USER' FOR the object.
603 DELETE FROM ams_act_access_denorm aacd
604 WHERE object_type = p_object_type
605 AND object_id = p_object_id
606 AND resource_id IN ( SELECT jgm.resource_id
607 FROM jtf_rs_groups_denorm jgd,
608 jtf_rs_group_members jgm
609 WHERE jgd.parent_group_id = p_group_id
610 AND jgd.group_id = jgm.group_id
611 AND jgd.start_date_active <= TRUNC(SYSDATE)
612 -- delete every group even if it was end dated earlier than SYSDATE - SVEERAVE 05/15/02
613 -- AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
614 AND jgm.delete_flag='N' )
615 AND NOT EXISTS ( SELECT 1
616 FROM ams_act_access aac,
617 jtf_rs_groups_denorm jgd,
618 jtf_rs_group_members jgm
619 WHERE aac.arc_act_access_to_object = p_object_type
620 AND aac.act_access_to_object_id = p_object_id
621 AND arc_user_or_role_type = 'GROUP'
622 AND aac.delete_flag = 'N'
623 AND user_or_role_id = jgd.parent_group_id
624 AND jgd.group_id = jgm.group_id
625 AND jgd.start_date_active <= TRUNC(SYSDATE)
626 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
627 AND jgm.delete_flag = 'N'
628 AND jgm.resource_id = aacd.resource_id
629 UNION ALL
630 SELECT 1
631 FROM ams_act_access
632 WHERE arc_act_access_to_object = p_object_type
633 AND act_access_to_object_id = p_object_id
634 AND arc_user_or_role_type = 'USER'
635 AND user_or_role_id = aacd.resource_id ) ;
636
637 -- If a group that is being deleted has edit metrics 'N',
638 -- we do not need to handle because it won't change any thing,
639 -- but if it is 'Y' that is being deleted
640 -- then we have to evaluate if resource belongs to another group but with edit_metrics_yn
641
642 IF p_edit_metrics = 'Y' THEN
643
644 UPDATE ams_act_access_denorm aacd
645 SET edit_metrics_yn = 'N'
646 , last_updated_by = l_user_id
647 , last_update_date = l_sysdate
648 , last_update_login = l_login_id
649 WHERE object_type = p_object_type
650 AND object_id = p_object_id
654 jtf_rs_group_members jgm
651 AND resource_id NOT IN ( SELECT jgm.resource_id
652 FROM ams_act_access aac,
653 jtf_rs_groups_denorm jgd,
655 WHERE aac.arc_act_access_to_object = p_object_type
656 AND aac.act_access_to_object_id = p_object_id
657 AND arc_user_or_role_type = 'GROUP'
658 AND user_or_role_id = jgd.parent_group_id
659 AND jgd.group_id = jgm.group_id
660 AND jgd.start_date_active <= TRUNC(SYSDATE)
661 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
662 AND jgm.delete_flag='N'
663 AND jgm.resource_id = aacd.resource_id
664 AND aac.admin_flag = 'Y'
665 AND aac.delete_flag = 'N'
666 UNION ALL
667 SELECT user_or_role_id
668 FROM ams_act_access
669 WHERE arc_act_access_to_object = p_object_type
670 AND act_access_to_object_id = p_object_id
671 AND arc_user_or_role_type = 'USER'
672 AND user_or_role_id = aacd.resource_id
673 AND admin_flag = 'Y'
674 )
675 AND edit_metrics_yn = 'Y';
676
677 END IF;
678
679 end delete_group;
680
681
682 ------------------------------------------------------
683 -- PROCEDURE : bulk_all_delete_group
684 ------------------------------------------------------
685
686 PROCEDURE bulk_all_delete_group
687
688 IS
689
690 TYPE user_role_tbl IS TABLE OF ams_act_access.user_or_role_id%TYPE ;
691 TYPE object_id_tbl IS TABLE OF ams_act_access.act_access_to_object_id%TYPE ;
692 TYPE object_tbl IS TABLE OF ams_act_access.arc_act_access_to_object%TYPE ;
693 TYPE admin_flag_tbl IS TABLE OF ams_act_access.admin_flag%TYPE ;
694
695 l_tbl_user_role user_role_tbl ;
696 l_tbl_object_id object_id_tbl ;
697 l_tbl_object object_tbl ;
698 l_tbl_admin_flag admin_flag_tbl ;
699
700
701 CURSOR csr_delete_groups IS
702 SELECT user_or_role_id
703 ,act_access_to_object_id
704 ,arc_act_access_to_object
705 ,admin_flag
706 FROM ams_act_access
707 WHERE arc_user_or_role_type = 'GROUP'
708 and delete_flag = 'Y' ;
709
710
711 BEGIN
712
713 Ams_Utility_Pvt.Write_Conc_Log('The program bulk_all_delete_group started... ');
714
715 OPEN csr_delete_groups ;
716
717 FETCH csr_delete_groups BULK COLLECT INTO l_tbl_user_role,l_tbl_object_id,l_tbl_object,l_tbl_admin_flag ;
718
719 CLOSE csr_delete_groups ;
720
721 Ams_Utility_Pvt.Write_Conc_Log('Data is bulk collected ');
722
723
724 -- Bulk delete
725 FORALL i IN 1..l_tbl_object_id.COUNT
726 DELETE FROM ams_act_access_denorm aacd
727 WHERE object_type = l_tbl_object(i)
728 AND object_id = l_tbl_object_id(i)
729 AND resource_id IN ( SELECT jgm.resource_id
730 FROM jtf_rs_groups_denorm jgd,
731 jtf_rs_group_members jgm
732 WHERE jgd.parent_group_id = l_tbl_user_role(i)
733 AND jgd.group_id = jgm.group_id
734 AND jgd.start_date_active <= TRUNC(SYSDATE)
735 AND jgm.delete_flag='N' )
736 AND NOT EXISTS ( SELECT 1
737 FROM ams_act_access aac,
738 jtf_rs_groups_denorm jgd,
739 jtf_rs_group_members jgm
740 WHERE aac.arc_act_access_to_object = l_tbl_object(i)
741 AND aac.act_access_to_object_id = l_tbl_object_id(i)
742 AND arc_user_or_role_type = 'GROUP'
743 AND aac.delete_flag = 'N'
744 AND user_or_role_id = jgd.parent_group_id
745 AND jgd.group_id = jgm.group_id
746 AND jgd.start_date_active <= TRUNC(SYSDATE)
747 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
748 AND jgm.delete_flag = 'N'
749 AND jgm.resource_id = aacd.resource_id
750 UNION ALL
751 SELECT 1
752 FROM ams_act_access
753 WHERE arc_act_access_to_object = l_tbl_object(i)
754 AND act_access_to_object_id = l_tbl_object_id(i)
755 AND arc_user_or_role_type = 'USER'
756 AND user_or_role_id = aacd.resource_id ) ;
757
758 Ams_Utility_Pvt.Write_Conc_Log('Bulk delete operation done ');
759 -- Bulk Update for admin flag
760 FORALL i IN 1..l_tbl_object_id.COUNT
761 UPDATE ams_act_access_denorm aacd
762 SET edit_metrics_yn = 'N'
763 , last_updated_by = fnd_global.user_id
764 , last_update_date = sysdate
765 , last_update_login = fnd_global.user_id
766 WHERE object_type = l_tbl_object(i)
767 AND object_id = l_tbl_object_id(i)
768 AND resource_id NOT IN ( SELECT jgm.resource_id
769 FROM ams_act_access aac,
770 jtf_rs_groups_denorm jgd,
771 jtf_rs_group_members jgm
772 WHERE aac.arc_act_access_to_object = l_tbl_object(i)
773 AND aac.act_access_to_object_id = l_tbl_object_id(i)
774 AND arc_user_or_role_type = 'GROUP'
775 AND user_or_role_id = jgd.parent_group_id
776 AND jgd.group_id = jgm.group_id
777 AND jgd.start_date_active <= TRUNC(SYSDATE)
778 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
779 AND jgm.delete_flag='N'
780 AND jgm.resource_id = aacd.resource_id
781 AND aac.admin_flag = 'Y'
785 FROM ams_act_access
782 AND aac.delete_flag = 'N'
783 UNION ALL
784 SELECT user_or_role_id
786 WHERE arc_act_access_to_object = l_tbl_object(i)
787 AND act_access_to_object_id = l_tbl_object_id(i)
788 AND arc_user_or_role_type = 'USER'
789 AND user_or_role_id = aacd.resource_id
790 AND admin_flag = 'Y'
791 )
792 AND edit_metrics_yn = 'Y'
793 AND edit_metrics_yn = NVL(l_tbl_admin_flag(i),'N');
794
795
796 Ams_Utility_Pvt.Write_Conc_Log('The procedure completed ');
797
798 END bulk_all_delete_group ;
799
800
801 ------------------------------------------------------
802 -- PROCEDURE : bulk_all_refresh_group
803 ------------------------------------------------------
804
805 PROCEDURE bulk_all_refresh_group
806
807 IS
808
809 l_user_id NUMBER := fnd_global.user_id;
810 l_login_id NUMBER := fnd_global.conc_login_id;
811 l_sysdate DATE := SYSDATE;
812
813 TYPE user_role_tbl IS TABLE OF ams_act_access.user_or_role_id%TYPE ;
814 TYPE object_id_tbl IS TABLE OF ams_act_access.act_access_to_object_id%TYPE ;
815 TYPE object_tbl IS TABLE OF ams_act_access.arc_act_access_to_object%TYPE ;
816 TYPE admin_flag_tbl IS TABLE OF ams_act_access.admin_flag%TYPE ;
817
818 l_tbl_user_role user_role_tbl ;
819 l_tbl_object_id object_id_tbl ;
820 l_tbl_object object_tbl ;
821 l_tbl_admin_flag admin_flag_tbl ;
822
823
824 CURSOR csr_delete_groups IS
825 SELECT user_or_role_id
826 ,act_access_to_object_id
827 ,arc_act_access_to_object
828 ,admin_flag
829 FROM ams_act_access
830 WHERE arc_user_or_role_type = 'GROUP'
831 and delete_flag = 'N' ;
832
833
834 BEGIN
835 Ams_Utility_Pvt.Write_Conc_Log('Procedure bulk_all_refresh_group started ');
836 OPEN csr_delete_groups ;
837
838 FETCH csr_delete_groups BULK COLLECT INTO l_tbl_user_role,l_tbl_object_id,l_tbl_object,l_tbl_admin_flag ;
839
840 CLOSE csr_delete_groups ;
841
842 Ams_Utility_Pvt.Write_Conc_Log('bulk_all_refresh_group - cursor closed ');
843
844 -- Bulk update for admin flag
845 FORALL i IN 1..l_tbl_object_id.COUNT
846 UPDATE ams_act_access_denorm aacd
847 SET edit_metrics_yn = 'Y'
848 , last_updated_by = l_user_id
849 , last_update_date = l_sysdate
850 , last_update_login = l_login_id
851 WHERE object_type = l_tbl_object(i)
852 AND object_id = l_tbl_object_id(i)
853 AND edit_metrics_yn = 'N'
854 AND l_tbl_admin_flag(i)='Y'
855 AND resource_id IN ( SELECT jgm.resource_id
856 FROM jtf_rs_groups_denorm jgd,
857 jtf_rs_group_members jgm
858 WHERE jgd.parent_group_id = l_tbl_user_role(i)
859 AND jgd.group_id = jgm.group_id
860 AND jgd.start_date_active <= TRUNC(SYSDATE)
861 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
862 AND jgm.delete_flag = 'N'
863 );
864
865 Ams_Utility_Pvt.Write_Conc_Log('bulk_all_refresh_group - first forall ');
866 -- Bulk insert
867 FORALL i IN 1..l_tbl_object_id.COUNT
868 INSERT INTO ams_act_access_denorm
869 ( access_denorm_id
870 , resource_id
871 , edit_metrics_yn
872 , object_type
873 , object_id
874 , source_code
875 , creation_date
876 , created_by
877 , last_update_date
878 , last_updated_by
879 , last_update_login
880 )
881 SELECT ams_act_access_denorm_s.nextval
882 , resource_id
883 , l_tbl_admin_flag(i)
884 , l_tbl_object(i)
885 , l_tbl_object_id(i)
886 , ams_access_pvt.get_source_code(l_tbl_object(i),l_tbl_object_id(i))
887 , l_sysdate
888 , l_user_id
889 , l_sysdate
890 , l_user_id
891 , l_login_id
892 FROM (
893 SELECT DISTINCT resource_id
894 FROM jtf_rs_groups_denorm jgd,
895 jtf_rs_group_members jgm
896 WHERE jgd.parent_group_id = l_tbl_user_role(i)
897 AND jgd.group_id = jgm.group_id
898 AND jgd.start_date_active <= TRUNC(SYSDATE)
899 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
900 AND jgm.delete_flag='N'
901 AND NOT EXISTS ( SELECT 1
902 FROM ams_act_access_denorm
903 WHERE resource_id = jgm.resource_id
904 AND object_type = l_tbl_object(i)
905 AND object_id = l_tbl_object_id(i))
906 );
907
908 Ams_Utility_Pvt.Write_Conc_Log('bulk_all_refresh_group - second forall ');
909 -- Bulk access update for admin flag as true
910 FORALL i IN 1..l_tbl_object_id.COUNT
914 , last_update_date = l_sysdate
911 UPDATE ams_act_access_denorm aacd
912 SET edit_metrics_yn = 'Y'
913 , last_updated_by = l_user_id
915 , last_update_login = l_login_id
916 WHERE object_type = l_tbl_object(i)
917 AND object_id = l_tbl_object_id(i)
918 AND edit_metrics_yn = 'N'
919 AND l_tbl_admin_flag(i)='Y'
920 AND EXISTS ( SELECT 1
921 FROM ams_act_access aac,
922 jtf_rs_groups_denorm jgd,
923 jtf_rs_group_members jgm
924 WHERE aac.arc_act_access_to_object = l_tbl_object(i)
925 AND aac.act_access_to_object_id = l_tbl_object_id(i)
926 AND arc_user_or_role_type = 'GROUP'
927 AND user_or_role_id = l_tbl_user_role(i)
928 AND user_or_role_id = jgd.parent_group_id
929 AND jgd.group_id = jgm.group_id
930 AND jgd.start_date_active <= TRUNC(SYSDATE)
931 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
932 AND jgm.delete_flag='N'
933 AND jgm.resource_id = aacd.resource_id
934 AND aac.delete_flag = 'N' );
935
936 Ams_Utility_Pvt.Write_Conc_Log('bulk_all_refresh_group - third forall ');
937 -- Bulk access update for admin flag as false
938 FORALL i IN 1..l_tbl_object_id.COUNT
939 UPDATE ams_act_access_denorm aacd
940 SET edit_metrics_yn = l_tbl_admin_flag(i)
941 , last_updated_by = l_user_id
942 , last_update_date = l_sysdate
943 , last_update_login = l_login_id
944 WHERE object_type = l_tbl_object(i)
945 AND object_id = l_tbl_object_id(i)
946 AND edit_metrics_yn = 'Y'
947 AND l_tbl_admin_flag(i)='N'
948 AND EXISTS ( SELECT 1
949 FROM ams_act_access aac,
950 jtf_rs_groups_denorm jgd,
951 jtf_rs_group_members jgm
952 WHERE aac.arc_act_access_to_object = l_tbl_object(i)
953 AND aac.act_access_to_object_id = l_tbl_object_id(i)
954 AND arc_user_or_role_type = 'GROUP'
955 AND user_or_role_id = jgd.parent_group_id
956 AND jgd.group_id = jgm.group_id
957 AND jgd.start_date_active <= TRUNC(SYSDATE)
958 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
959 AND jgm.delete_flag='N'
960 AND jgm.resource_id = aacd.resource_id
961 AND aac.delete_flag = 'N'
962 )
963 AND NOT EXISTS ( SELECT 1
964 FROM ams_act_access aac,
965 jtf_rs_groups_denorm jgd,
966 jtf_rs_group_members jgm
967 WHERE aac.arc_act_access_to_object = l_tbl_object(i)
968 AND aac.act_access_to_object_id = l_tbl_object_id(i)
969 AND arc_user_or_role_type = 'GROUP'
970 AND user_or_role_id = jgd.parent_group_id
971 AND jgd.group_id = jgm.group_id
972 AND jgd.start_date_active <= TRUNC(SYSDATE)
973 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
974 AND jgm.delete_flag='N'
975 AND jgm.resource_id = aacd.resource_id
976 AND aac.delete_flag = 'N'
977 AND aac.admin_flag='Y'
978 )
979 AND NOT EXISTS ( SELECT 1
980 FROM ams_act_access aac
981 WHERE aac.act_access_to_object_id = l_tbl_object_id(i)
982 AND aac.arc_act_access_to_object = l_tbl_object(i)
983 AND aac.user_or_role_id = aacd.resource_id
984 AND aac.arc_user_or_role_type = 'USER'
985 AND aac.delete_flag = 'N'
986 AND aac.admin_flag = 'Y'
987 );
988 Ams_Utility_Pvt.Write_Conc_Log('bulk_all_refresh_group - end of forall ');
989 END bulk_all_refresh_group ;
990
991
992 -------------------------------------------------------------------------
993 --bulk_insert_group
994 -------------------------------------------------------------------------
995
996 PROCEDURE bulk_insert_group (p_last_run_date IN date)
997
998 IS
999
1000 l_user_id NUMBER := fnd_global.user_id;
1001 l_login_id NUMBER := fnd_global.conc_login_id;
1002 l_sysdate DATE := SYSDATE;
1003
1004 TYPE user_role_tbl IS TABLE OF ams_act_access.user_or_role_id%TYPE ;
1005 TYPE object_id_tbl IS TABLE OF ams_act_access.act_access_to_object_id%TYPE ;
1006 TYPE object_tbl IS TABLE OF ams_act_access.arc_act_access_to_object%TYPE ;
1007 TYPE admin_flag_tbl IS TABLE OF ams_act_access.admin_flag%TYPE ;
1008
1009 l_tbl_user_role user_role_tbl ;
1010 l_tbl_object_id object_id_tbl ;
1011 l_tbl_object object_tbl ;
1012 l_tbl_admin_flag admin_flag_tbl ;
1013
1014 CURSOR crs_insert_grp IS
1015 SELECT user_or_role_id
1016 ,act_access_to_object_id
1017 ,arc_act_access_to_object
1018 ,admin_flag
1019 FROM ams_act_access
1020 WHERE arc_user_or_role_type = 'GROUP'
1021 AND last_update_date >= p_last_run_date
1022 and creation_date > p_last_run_date
1023 and delete_flag = 'N' ;
1024
1025 BEGIN
1026 Ams_Utility_Pvt.Write_Conc_Log('bulk_insert_group - start ');
1027
1028 OPEN crs_insert_grp ;
1029
1030 FETCH crs_insert_grp BULK COLLECT INTO l_tbl_user_role,l_tbl_object_id,l_tbl_object,l_tbl_admin_flag ;
1031
1032 CLOSE crs_insert_grp ;
1033
1034 -- Bulk update for admin flag as 'Y'
1035 FORALL i IN 1..l_tbl_object_id.COUNT
1036 UPDATE ams_act_access_denorm aacd
1037 SET edit_metrics_yn = 'Y'
1038 , last_updated_by = l_user_id
1039 , last_update_date = l_sysdate
1040 , last_update_login = l_login_id
1041 WHERE object_type = l_tbl_object(i)
1042 AND object_id = l_tbl_object_id(i)
1043 AND resource_id IN ( SELECT jgm.resource_id
1044 FROM jtf_rs_groups_denorm jgd,
1045 jtf_rs_group_members jgm
1046 WHERE jgd.parent_group_id = l_tbl_user_role(i)
1047 AND jgd.group_id = jgm.group_id
1051 )
1048 AND jgd.start_date_active <= TRUNC(SYSDATE)
1049 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1050 AND jgm.delete_flag='N'
1052 AND edit_metrics_yn = 'N'
1053 AND l_tbl_admin_flag(i)='N';
1054
1055
1056 -- Bulk insert
1057 FORALL i IN 1..l_tbl_object_id.COUNT
1058 INSERT INTO ams_act_access_denorm
1059 ( access_denorm_id
1060 , resource_id
1061 , edit_metrics_yn
1062 , object_type
1063 , object_id
1064 , source_code
1065 , creation_date
1066 , created_by
1067 , last_update_date
1068 , last_updated_by
1069 , last_update_login
1070 )
1071 SELECT ams_act_access_denorm_s.nextval
1072 , resource_id
1073 , l_tbl_admin_flag(i)
1074 , l_tbl_object(i)
1075 , l_tbl_object_id(i)
1076 , ams_access_pvt.get_source_code(l_tbl_object(i),l_tbl_object_id(i))
1077 , l_sysdate
1078 , l_user_id
1079 , l_sysdate
1080 , l_user_id
1081 , l_login_id
1082 FROM (
1083 SELECT DISTINCT resource_id
1084 FROM jtf_rs_groups_denorm jgd,
1085 jtf_rs_group_members jgm
1086 WHERE jgd.parent_group_id = l_tbl_user_role(i)
1087 AND jgd.group_id = jgm.group_id
1088 AND jgd.start_date_active <= TRUNC(SYSDATE)
1089 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1090 AND jgm.delete_flag='N'
1091 AND NOT EXISTS ( SELECT 1
1092 FROM ams_act_access_denorm
1093 WHERE resource_id = jgm.resource_id
1094 AND object_type = l_tbl_object(i)
1095 AND object_id = l_tbl_object_id(i))
1096 );
1097
1098
1099
1100
1101 END bulk_insert_group ;
1102
1103
1104 ---------------------------------------------------------------------------------------------
1105 -- bulk_delete_group
1106 ---------------------------------------------------------------------------------------------
1107
1108
1109 PROCEDURE bulk_delete_group (p_last_run_date IN date,
1110 p_reason_code varchar2)
1111
1112 IS
1113
1114 l_user_id NUMBER := fnd_global.user_id;
1115 l_login_id NUMBER := fnd_global.conc_login_id;
1116 l_sysdate DATE := SYSDATE;
1117
1118 TYPE user_role_tbl IS TABLE OF ams_act_access.user_or_role_id%TYPE ;
1119 TYPE object_id_tbl IS TABLE OF ams_act_access.act_access_to_object_id%TYPE ;
1120 TYPE object_tbl IS TABLE OF ams_act_access.arc_act_access_to_object%TYPE ;
1121 TYPE admin_flag_tbl IS TABLE OF ams_act_access.admin_flag%TYPE ;
1122
1123 l_tbl_user_role user_role_tbl ;
1124 l_tbl_object_id object_id_tbl ;
1125 l_tbl_object object_tbl ;
1126 l_tbl_admin_flag admin_flag_tbl ;
1127
1128 CURSOR csr_del_grp IS
1129 SELECT user_or_role_id
1130 ,act_access_to_object_id
1131 ,arc_act_access_to_object
1132 ,admin_flag
1133 FROM ams_act_access
1134 WHERE arc_user_or_role_type = 'GROUP'
1135 AND last_update_date > p_last_run_date
1136 and delete_flag = 'Y' ;
1137
1138 CURSOR cur_get_del_grp IS
1139 SELECT jrg.group_id user_or_role_id
1140 , act.act_access_to_object_id act_access_to_object_id
1141 , act.arc_act_access_to_object arc_act_access_to_object
1142 , admin_flag
1143 FROM ams_act_access act,
1144 jtf_rs_groups_b jrg
1145 WHERE act.arc_user_or_role_type = 'GROUP'
1146 AND act.user_or_role_id= jrg.group_id
1147 AND act.delete_flag = 'N'
1148 AND jrg.end_date_active IS NOT NULL
1149 AND jrg.end_date_active >= p_last_run_date
1150 AND jrg.end_date_active <= TRUNC(SYSDATE) ;
1151
1152
1153
1154 BEGIN
1155 Ams_Utility_Pvt.Write_Conc_Log('bulk_delete_group - start ');
1156
1157 if(p_reason_code = 'AMS_ACCESS') then
1158
1159 OPEN csr_del_grp ;
1160
1161 FETCH csr_del_grp BULK COLLECT INTO l_tbl_user_role,l_tbl_object_id,l_tbl_object,l_tbl_admin_flag ;
1162
1163 CLOSE csr_del_grp ;
1164 else
1165
1166 OPEN cur_get_del_grp ;
1167
1168 FETCH cur_get_del_grp BULK COLLECT INTO l_tbl_user_role,l_tbl_object_id,l_tbl_object,l_tbl_admin_flag ;
1169
1170 CLOSE cur_get_del_grp ;
1171
1172 end if ;
1173
1174 FORALL i IN 1..l_tbl_object_id.COUNT
1175
1176 DELETE FROM ams_act_access_denorm aacd
1177 WHERE object_type = l_tbl_object(i)
1178 AND object_id = l_tbl_object_id(i)
1179 AND resource_id IN ( SELECT jgm.resource_id
1180 FROM jtf_rs_groups_denorm jgd,
1181 jtf_rs_group_members jgm
1182 WHERE jgd.parent_group_id = l_tbl_user_role(i)
1183 AND jgd.group_id = jgm.group_id
1184 AND jgd.start_date_active <= TRUNC(SYSDATE)
1185 -- delete every group even if it was end dated earlier than SYSDATE - SVEERAVE 05/15/02
1186 -- AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1187 AND jgm.delete_flag='N' )
1188 AND NOT EXISTS ( SELECT 1
1189 FROM ams_act_access aac,
1190 jtf_rs_groups_denorm jgd,
1191 jtf_rs_group_members jgm
1192 WHERE aac.arc_act_access_to_object = l_tbl_object(i)
1193 AND aac.act_access_to_object_id = l_tbl_object_id(i)
1194 AND arc_user_or_role_type = 'GROUP'
1195 AND aac.delete_flag = 'N'
1196 AND user_or_role_id = jgd.parent_group_id
1197 AND jgd.group_id = jgm.group_id
1198 AND jgd.start_date_active <= TRUNC(SYSDATE)
1199 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1200 AND jgm.delete_flag = 'N'
1201 AND jgm.resource_id = aacd.resource_id
1202 UNION ALL
1203 SELECT 1
1207 AND arc_user_or_role_type = 'USER'
1204 FROM ams_act_access
1205 WHERE arc_act_access_to_object = l_tbl_object(i)
1206 AND act_access_to_object_id = l_tbl_object_id(i)
1208 AND user_or_role_id = aacd.resource_id ) ;
1209
1210
1211 FORALL i IN 1..l_tbl_object_id.COUNT
1212 UPDATE ams_act_access_denorm aacd
1213 SET edit_metrics_yn = 'N'
1214 , last_updated_by = l_user_id
1215 , last_update_date = l_sysdate
1216 , last_update_login = l_login_id
1217 WHERE object_type = l_tbl_object(i)
1218 AND object_id = l_tbl_object_id(i)
1219 AND resource_id NOT IN ( SELECT jgm.resource_id
1220 FROM ams_act_access aac,
1221 jtf_rs_groups_denorm jgd,
1222 jtf_rs_group_members jgm
1223 WHERE aac.arc_act_access_to_object = l_tbl_object(i)
1224 AND aac.act_access_to_object_id = l_tbl_object_id(i)
1225 AND arc_user_or_role_type = 'GROUP'
1226 AND user_or_role_id = jgd.parent_group_id
1227 AND jgd.group_id = jgm.group_id
1228 AND jgd.start_date_active <= TRUNC(SYSDATE)
1229 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1230 AND jgm.delete_flag='N'
1231 AND jgm.resource_id = aacd.resource_id
1232 AND aac.admin_flag = 'Y'
1233 AND aac.delete_flag = 'N'
1234 UNION ALL
1235 SELECT user_or_role_id
1236 FROM ams_act_access
1237 WHERE arc_act_access_to_object = l_tbl_object(i)
1238 AND act_access_to_object_id = l_tbl_object_id(i)
1239 AND arc_user_or_role_type = 'USER'
1240 AND user_or_role_id = aacd.resource_id
1241 AND admin_flag = 'Y'
1242 )
1243 AND edit_metrics_yn = 'Y'
1244 AND edit_metrics_yn = NVL(l_tbl_admin_flag(i),'N');
1245
1246 END bulk_delete_group ;
1247
1248
1249
1250 ----------------------------------------------------------------------------------------------
1251 -- BULK_update_GROUP
1252 ----------------------------------------------------------------------------------------------
1253
1254
1255
1256 PROCEDURE bulk_update_group (p_last_run_date IN date)
1257
1258 IS
1259
1260 l_user_id NUMBER := fnd_global.user_id;
1261 l_login_id NUMBER := fnd_global.conc_login_id;
1262 l_sysdate DATE := SYSDATE;
1263
1264 TYPE user_role_tbl IS TABLE OF ams_act_access.user_or_role_id%TYPE ;
1265 TYPE object_id_tbl IS TABLE OF ams_act_access.act_access_to_object_id%TYPE ;
1266 TYPE object_tbl IS TABLE OF ams_act_access.arc_act_access_to_object%TYPE ;
1267 TYPE admin_flag_tbl IS TABLE OF ams_act_access.admin_flag%TYPE ;
1268
1269 l_tbl_user_role user_role_tbl ;
1270 l_tbl_object_id object_id_tbl ;
1271 l_tbl_object object_tbl ;
1272 l_tbl_admin_flag admin_flag_tbl ;
1273
1274 CURSOR csr_upd_grp IS
1275 SELECT user_or_role_id
1276 ,act_access_to_object_id
1277 ,arc_act_access_to_object
1278 ,admin_flag
1279 FROM ams_act_access
1280 WHERE arc_user_or_role_type = 'GROUP'
1281 AND last_update_date > p_last_run_date
1282 and delete_flag = 'N' ;
1283
1284 BEGIN
1285 Ams_Utility_Pvt.Write_Conc_Log('bulk_update_group - start ');
1286
1287
1288 OPEN csr_upd_grp ;
1289
1290 FETCH csr_upd_grp BULK COLLECT INTO l_tbl_user_role,l_tbl_object_id,l_tbl_object,l_tbl_admin_flag ;
1291
1292 CLOSE csr_upd_grp ;
1293
1294
1295 FORALL i IN 1..l_tbl_object_id.COUNT
1296 UPDATE ams_act_access_denorm aacd
1297 SET edit_metrics_yn = 'Y'
1298 , last_updated_by = l_user_id
1299 , last_update_date = l_sysdate
1300 , last_update_login = l_login_id
1301 WHERE object_type = l_tbl_object(i)
1302 AND object_id = l_tbl_object_id(i)
1303 AND edit_metrics_yn = 'N'
1304 AND l_tbl_admin_flag(i)='Y'
1305 AND EXISTS ( SELECT 1
1306 FROM ams_act_access aac,
1307 jtf_rs_groups_denorm jgd,
1308 jtf_rs_group_members jgm
1309 WHERE aac.arc_act_access_to_object = l_tbl_object(i)
1310 AND aac.act_access_to_object_id = l_tbl_object_id(i)
1311 AND arc_user_or_role_type = 'GROUP'
1312 AND user_or_role_id = l_tbl_user_role(i)
1313 AND user_or_role_id = jgd.parent_group_id
1314 AND jgd.group_id = jgm.group_id
1315 AND jgd.start_date_active <= TRUNC(SYSDATE)
1316 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1317 AND jgm.delete_flag='N'
1318 AND jgm.resource_id = aacd.resource_id
1319 AND aac.delete_flag = 'N' );
1320
1321
1322 FORALL i IN 1..l_tbl_object_id.COUNT
1323 UPDATE ams_act_access_denorm aacd
1324 SET edit_metrics_yn = 'N'
1325 , last_updated_by = l_user_id
1326 , last_update_date = l_sysdate
1327 , last_update_login = l_login_id
1328 WHERE object_type = l_tbl_object(i)
1329 AND object_id = l_tbl_object_id(i)
1330 AND edit_metrics_yn = 'Y'
1331 AND l_tbl_admin_flag(i)='N'
1332 AND EXISTS ( SELECT 1
1333 FROM ams_act_access aac,
1334 jtf_rs_groups_denorm jgd,
1335 jtf_rs_group_members jgm
1336 WHERE aac.arc_act_access_to_object = l_tbl_object(i)
1337 AND aac.act_access_to_object_id = l_tbl_object_id(i)
1338 AND arc_user_or_role_type = 'GROUP'
1339 AND user_or_role_id = jgd.parent_group_id
1340 AND jgd.group_id = jgm.group_id
1341 AND jgd.start_date_active <= TRUNC(SYSDATE)
1342 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1343 AND jgm.delete_flag='N'
1344 AND jgm.resource_id = aacd.resource_id
1348 FROM ams_act_access aac,
1345 AND aac.delete_flag = 'N'
1346 )
1347 AND NOT EXISTS ( SELECT 1
1349 jtf_rs_groups_denorm jgd,
1350 jtf_rs_group_members jgm
1351 WHERE aac.arc_act_access_to_object = l_tbl_object(i)
1352 AND aac.act_access_to_object_id = l_tbl_object_id(i)
1353 AND arc_user_or_role_type = 'GROUP'
1354 AND user_or_role_id = jgd.parent_group_id
1355 AND jgd.group_id = jgm.group_id
1356 AND jgd.start_date_active <= TRUNC(SYSDATE)
1357 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1358 AND jgm.delete_flag='N'
1359 AND jgm.resource_id = aacd.resource_id
1360 AND aac.delete_flag = 'N'
1361 AND aac.admin_flag='Y'
1362 )
1363 AND NOT EXISTS ( SELECT 1
1364 FROM ams_act_access aac
1365 WHERE aac.act_access_to_object_id = l_tbl_object_id(i)
1366 AND aac.arc_act_access_to_object = l_tbl_object(i)
1367 AND aac.user_or_role_id = aacd.resource_id
1368 AND aac.arc_user_or_role_type = 'USER'
1369 AND aac.delete_flag = 'N'
1370 AND aac.admin_flag = 'Y'
1371 );
1372
1373 END bulk_update_group ;
1374
1375 ---------------------------------------------------------------------------------
1376 -- bulk_insert_resource
1377 ---------------------------------------------------------------------------------
1378
1379 PROCEDURE bulk_insert_resource (p_last_run_date IN date)
1380 IS
1381
1382 l_user_id NUMBER := fnd_global.user_id;
1383 l_login_id NUMBER := fnd_global.conc_login_id;
1384 l_sysdate DATE := SYSDATE;
1385
1386 TYPE resource_tbl IS TABLE OF jtf_rs_group_members.resource_id%TYPE ;
1387 TYPE object_id_tbl IS TABLE OF ams_act_access.act_access_to_object_id%TYPE ;
1388 TYPE object_tbl IS TABLE OF ams_act_access.arc_act_access_to_object%TYPE ;
1389 TYPE admin_flag_tbl IS TABLE OF ams_act_access.admin_flag%TYPE ;
1390
1391 l_tbl_res resource_tbl ;
1392 l_tbl_object_id object_id_tbl ;
1393 l_tbl_object object_tbl ;
1394 l_tbl_admin_flag admin_flag_tbl ;
1395
1396 CURSOR cur_get_crt_res IS
1397 SELECT jgm.resource_id,
1398 aac.act_access_to_object_id,
1399 aac.arc_act_access_to_object,
1400 aac.admin_flag
1401 FROM ams_act_access aac,
1402 jtf_rs_groups_denorm jrg,
1403 jtf_rs_group_members jgm
1404 WHERE
1405 ( aac.arc_user_or_role_type = 'GROUP'
1406 AND aac.user_or_role_id= jrg.parent_group_id
1407 AND aac.delete_flag='N'
1408 AND jgm.delete_flag = 'N'
1409 AND jrg.group_id = jgm.group_id
1410 AND jrg.start_date_active >= p_last_run_date
1411 AND jrg.start_date_active <= TRUNC(SYSDATE)
1412 )
1413 UNION
1414 SELECT jgm.resource_id
1415 , aac.act_access_to_object_id
1416 , aac.arc_act_access_to_object
1417 , aac.admin_flag
1418 FROM ams_act_access aac,
1419 jtf_rs_groups_denorm jrg,
1420 jtf_rs_group_members jgm
1421 WHERE
1422 ( aac.arc_user_or_role_type = 'GROUP'
1423 AND aac.user_or_role_id= jrg.parent_group_id
1424 AND aac.delete_flag='N'
1425 AND jgm.delete_flag = 'N'
1426 AND jrg.group_id = jgm.group_id
1427 AND jrg.last_update_date > p_last_run_date
1428 AND jrg.start_date_active <= TRUNC(SYSDATE)
1429 )
1430 UNION
1431 SELECT jgm.resource_id
1432 , aac.act_access_to_object_id
1433 , aac.arc_act_access_to_object
1434 , aac.admin_flag
1435 FROM ams_act_access aac,
1436 jtf_rs_groups_denorm jrg,
1437 jtf_rs_group_members jgm
1438 WHERE
1439 ( aac.arc_user_or_role_type = 'GROUP'
1440 AND aac.user_or_role_id= jrg.parent_group_id
1441 AND aac.delete_flag='N'
1442 AND jgm.delete_flag = 'N'
1443 AND jrg.group_id = jgm.group_id
1444 AND jgm.creation_date > p_last_run_date
1445 );
1446
1447
1448 BEGIN
1449 Ams_Utility_Pvt.Write_Conc_Log('bulk_insert_resource - start ');
1450
1451 OPEN cur_get_crt_res ;
1452
1453 FETCH cur_get_crt_res BULK COLLECT INTO l_tbl_res,l_tbl_object_id,l_tbl_object,l_tbl_admin_flag ;
1454
1455 CLOSE cur_get_crt_res ;
1456
1457 FORALL i IN 1..l_tbl_object_id.COUNT
1458 INSERT INTO ams_act_access_denorm
1459 ( access_denorm_id
1460 , resource_id
1461 , edit_metrics_yn
1462 , object_type
1463 , object_id
1464 , source_code
1465 , creation_date
1466 , created_by
1467 , last_update_date
1468 , last_updated_by
1469 , last_update_login
1470 )
1471 SELECT ams_act_access_denorm_s.nextval
1472 , l_tbl_res(i)
1473 , l_tbl_admin_flag(i)
1474 , l_tbl_object(i)
1475 , l_tbl_object_id(i)
1476 , ams_access_pvt.get_source_code(l_tbl_object(i),l_tbl_object_id(i))
1477 , l_sysdate
1478 , l_user_id
1479 , l_sysdate
1480 , l_user_id
1481 , l_login_id
1482 FROM dual
1483 WHERE NOT EXISTS ( SELECT 1
1484 FROM ams_act_access_denorm
1485 WHERE resource_id = l_tbl_res(i)
1486 AND object_type = l_tbl_object(i)
1487 AND object_id = l_tbl_object_id(i)
1488 );
1489
1490
1491 FORALL i IN 1..l_tbl_object_id.COUNT
1492 UPDATE ams_act_access_denorm
1493 SET edit_metrics_yn = l_tbl_admin_flag(i),
1494 last_updated_by = l_user_id,
1495 last_update_date = l_sysdate,
1496 last_update_login = l_login_id
1497 WHERE object_type = l_tbl_object(i)
1498 AND object_id = l_tbl_object_id(i)
1499 AND resource_id = l_tbl_res(i)
1503 END bulk_insert_resource ;
1500 AND edit_metrics_yn = 'N'
1501 AND l_tbl_admin_flag(i) = 'Y' ;
1502
1504
1505 -----------------------------------------------------------------------------------------
1506 -- bulk_delete_resource
1507 ------------------------------------------------------------------------------------------
1508
1509 PROCEDURE bulk_delete_resource (p_last_run_date IN date)
1510
1511 IS
1512
1513 l_user_id NUMBER := fnd_global.user_id;
1514 l_login_id NUMBER := fnd_global.conc_login_id;
1515 l_sysdate DATE := SYSDATE;
1516
1517 TYPE resource_tbl IS TABLE OF jtf_rs_group_members.resource_id%TYPE ;
1518 TYPE object_id_tbl IS TABLE OF ams_act_access.act_access_to_object_id%TYPE ;
1519 TYPE object_tbl IS TABLE OF ams_act_access.arc_act_access_to_object%TYPE ;
1520 TYPE admin_flag_tbl IS TABLE OF ams_act_access.admin_flag%TYPE ;
1521
1522 l_tbl_resource resource_tbl ;
1523 l_tbl_object_id object_id_tbl ;
1524 l_tbl_object object_tbl ;
1525 l_tbl_admin_flag admin_flag_tbl ;
1526
1527 CURSOR cur_get_del_res IS
1528 SELECT jgm.resource_id
1529 ,aac.act_access_to_object_id
1530 , aac.arc_act_access_to_object
1531 , aac.admin_flag
1532 FROM ams_act_access aac,
1533 jtf_rs_groups_denorm jrg,
1534 jtf_rs_group_members jgm
1535 WHERE
1536 ( aac.arc_user_or_role_type = 'GROUP'
1537 AND aac.user_or_role_id= jrg.parent_group_id
1538 AND aac.delete_flag='N'
1539 AND jrg.group_id = jgm.group_id
1540 AND jrg.end_date_active IS NOT NULL
1541 AND jrg.end_date_active >= p_last_run_date
1542 AND jrg.end_date_active <= TRUNC(SYSDATE)
1543 )
1544 UNION
1545 SELECT jgm.resource_id,
1546 aac.act_access_to_object_id
1547 , aac.arc_act_access_to_object
1548 , aac.admin_flag
1549 FROM ams_act_access aac,
1550 jtf_rs_groups_denorm jrg,
1551 jtf_rs_group_members jgm
1552 WHERE
1553 ( aac.arc_user_or_role_type = 'GROUP'
1554 AND aac.user_or_role_id= jrg.parent_group_id
1555 AND aac.delete_flag='N'
1556 AND jrg.group_id = jgm.group_id
1557 AND jrg.last_update_date > p_last_run_date
1558 AND jrg.end_date_active IS NOT NULL
1559 AND jrg.end_date_active <= TRUNC(SYSDATE)
1560 )
1561 UNION
1562 SELECT jgm.resource_id,
1563 aac.act_access_to_object_id
1564 , aac.arc_act_access_to_object
1565 , aac.admin_flag
1566 FROM ams_act_access aac,
1567 jtf_rs_groups_denorm jrg,
1568 jtf_rs_group_members jgm
1569 WHERE
1570 ( aac.arc_user_or_role_type = 'GROUP'
1571 AND aac.user_or_role_id= jrg.parent_group_id
1572 AND aac.delete_flag='N'
1573 AND jgm.delete_flag = 'Y'
1574 AND jrg.group_id = jgm.group_id
1575 AND jgm.last_update_date >= p_last_run_date
1576 );
1577
1578
1579 BEGIN
1580 Ams_Utility_Pvt.Write_Conc_Log('bulk_delete_group - start ');
1581
1582
1583 OPEN cur_get_del_res ;
1584
1585 FETCH cur_get_del_res BULK COLLECT INTO l_tbl_resource,l_tbl_object_id,l_tbl_object,l_tbl_admin_flag ;
1586
1587 CLOSE cur_get_del_res ;
1588
1589
1590 FORALL i IN 1..l_tbl_object_id.COUNT
1591 DELETE FROM AMS_ACT_ACCESS_DENORM aacd
1592 WHERE resource_id = l_tbl_resource(i)
1593 AND object_type = l_tbl_object(i)
1594 AND object_id = l_tbl_object_id(i)
1595 AND not exists ( SELECT 1
1596 FROM ams_act_access aac,
1597 jtf_rs_groups_denorm jgd,
1598 jtf_rs_group_members jgm
1599 WHERE aac.arc_act_access_to_object = l_tbl_object(i)
1600 AND aac.act_access_to_object_id = l_tbl_object_id(i)
1601 AND arc_user_or_role_type = 'GROUP'
1602 AND user_or_role_id = jgd.parent_group_id
1603 AND jgd.group_id = jgm.group_id
1604 AND jgd.start_date_active <= TRUNC(SYSDATE)
1605 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1606 AND jgm.resource_id = l_tbl_resource(i)
1607 AND jgm.delete_flag = 'N'
1608 AND aac.delete_flag = 'N'
1609 UNION ALL
1610 SELECT 1
1611 FROM ams_act_access
1612 WHERE arc_act_access_to_object = l_tbl_object(i)
1613 AND act_access_to_object_id = l_tbl_object_id(i)
1614 AND arc_user_or_role_type = 'USER'
1615 AND user_or_role_id = aacd.resource_id
1616 );
1617
1618
1619 FORALL i IN 1..l_tbl_object_id.COUNT
1620 UPDATE ams_act_access_denorm aacd
1621 SET edit_metrics_yn = 'N'
1622 , last_updated_by = l_user_id
1623 , last_update_date = l_sysdate
1624 , last_update_login = l_login_id
1625 WHERE object_type = l_tbl_object(i)
1626 AND object_id = l_tbl_object_id(i)
1627 AND resource_id = l_tbl_resource(i)
1628 AND resource_id not IN (SELECT jgm.resource_id
1629 FROM ams_act_access aac,
1630 jtf_rs_groups_denorm jgd,
1631 jtf_rs_group_members jgm
1632 WHERE aac.arc_act_access_to_object = l_tbl_object(i)
1633 AND aac.act_access_to_object_id = l_tbl_object_id(i)
1634 AND arc_user_or_role_type = 'GROUP'
1635 AND user_or_role_id = jgd.parent_group_id
1636 AND jgd.group_id = jgm.group_id
1637 AND jgd.start_date_active <= TRUNC(SYSDATE)
1638 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1639 AND jgm.delete_flag='N'
1640 AND jgm.resource_id = aacd.resource_id
1641 AND aac.delete_flag = 'N')
1642 AND edit_metrics_yn = 'Y'
1643 AND l_tbl_admin_flag(i) = 'Y' ;
1644
1645 END bulk_delete_resource ;
1646
1647
1648
1652 , p_edit_metrics IN VARCHAR2
1649 PROCEDURE refresh_group( p_group_id IN NUMBER
1650 , p_object_type IN VARCHAR2
1651 , p_object_id IN NUMBER
1653 )
1654 IS
1655 BEGIN
1656 -- add new resources which are not present in the denorm table.
1657 insert_group( p_group_id => p_group_id
1658 , p_object_type => p_object_type
1659 , p_object_id => p_object_id
1660 , p_edit_metrics => p_edit_metrics
1661 );
1662 -- update the edit metrics in the denorm table.
1663 update_group( p_group_id => p_group_id
1664 , p_object_type => p_object_type
1665 , p_object_id => p_object_id
1666 , p_edit_metrics => p_edit_metrics
1667 );
1668 -- delete the resources in the denorm table which are end-dated,
1669 -- or no longer present in the group.
1670 -- this deleted resource should not be part of any other active group or the user of
1671 -- the object.
1672 DELETE FROM ams_act_access_denorm aacd
1673 WHERE aacd.object_type = p_object_type
1674 AND aacd.object_id = p_object_id
1675 AND NOT EXISTS ( SELECT 1 --anchaudh 21-MAR-03
1676 FROM jtf_rs_groups_denorm jgd,
1677 jtf_rs_group_members jgm
1678 WHERE jgd.parent_group_id = p_group_id
1679 AND jgd.group_id = jgm.group_id
1680 AND jgd.start_date_active <= TRUNC(SYSDATE)
1681 AND NVL(jgd.end_date_active,SYSDATE) >= TRUNC(SYSDATE)
1682 AND jgm.delete_flag='N'
1683 AND jgm.resource_id = aacd.resource_id) --anchaudh 21-MAR-03
1684 AND NOT EXISTS ( SELECT 1
1685 FROM ams_act_access aac,
1686 jtf_rs_groups_denorm jgd,
1687 jtf_rs_group_members jgm
1688 WHERE aac.arc_act_access_to_object = p_object_type
1689 AND aac.act_access_to_object_id = p_object_id
1690 AND arc_user_or_role_type = 'GROUP'
1691 AND aac.delete_flag = 'N'
1692 AND user_or_role_id = jgd.parent_group_id
1693 AND jgd.group_id = jgm.group_id
1694 AND jgd.start_date_active <= TRUNC(SYSDATE)
1695 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
1696 AND jgm.delete_flag = 'N'
1697 AND jgm.resource_id = aacd.resource_id)
1698 AND NOT EXISTS ( --anchaudh 21-MAR-03
1699 SELECT 1
1700 FROM ams_act_access
1701 WHERE arc_act_access_to_object = p_object_type
1702 AND act_access_to_object_id = p_object_id
1703 AND arc_user_or_role_type = 'USER'
1704 AND user_or_role_id = aacd.resource_id ) ;
1705 END refresh_group;
1706
1707 PROCEDURE ams_object_denorm ( errbuf OUT NOCOPY VARCHAR2
1708 , retcode OUT NOCOPY VARCHAR2
1709 , p_object_id IN NUMBER
1710 , p_object_type IN VARCHAR2 )
1711 IS
1712 CURSOR cur_get_object_changes IS
1713 SELECT arc_user_or_role_type
1714 ,user_or_role_id
1715 ,act_access_to_object_id
1716 ,arc_act_access_to_object
1717 ,admin_flag
1718 FROM ams_act_access
1719 WHERE act_access_to_object_id = p_object_id
1720 AND arc_act_access_to_object = p_object_type
1721 AND arc_user_or_role_type = 'GROUP'
1722 AND delete_flag = 'N';
1723
1724 l_user_id NUMBER := fnd_global.user_id;
1725 l_login_id NUMBER := fnd_global.conc_login_id;
1726 l_sysdate DATE := SYSDATE;
1727
1728 BEGIN
1729
1730 DELETE FROM ams_act_access_denorm aacd
1731 WHERE aacd.object_type = p_object_type
1732 AND aacd.object_id = p_object_id;
1733
1734 INSERT INTO ams_act_access_denorm
1735 (
1736 access_denorm_id
1737 , resource_id
1738 , edit_metrics_yn
1739 , object_type
1740 , object_id
1741 , source_code
1742 , creation_date
1743 , created_by
1744 , last_update_date
1745 , last_updated_by
1746 , last_update_login
1747 )
1748 SELECT ams_act_access_denorm_s.nextval
1749 ,user_or_role_id
1750 ,admin_flag
1751 ,arc_act_access_to_object
1752 ,act_access_to_object_id
1753 ,ams_access_pvt.get_source_code(arc_act_access_to_object,act_access_to_object_id)
1754 ,l_sysdate
1755 ,l_user_id
1756 ,l_sysdate
1757 ,l_user_id
1758 ,l_login_id
1759 FROM ams_act_access
1760 WHERE arc_act_access_to_object = p_object_type
1761 AND act_access_to_object_id = p_object_id
1762 AND arc_user_or_role_type = 'USER';
1763
1764 FOR object_rec IN cur_get_object_changes LOOP
1765
1766 insert_group( object_rec.user_or_role_id
1767 ,object_rec.arc_act_access_to_object
1768 ,object_rec.act_access_to_object_id
1769 ,object_rec.admin_flag
1770 );
1771
1772 END LOOP;
1773
1774 DELETE FROM ams_act_access
1775 WHERE arc_user_or_role_type = 'GROUP'
1776 AND arc_act_access_to_object = p_object_type
1777 AND act_access_to_object_id = p_object_id
1778 AND delete_flag = 'Y' ;
1779
1780 RETCODE := 0;
1781 EXCEPTION
1782 WHEN OTHERS THEN
1786
1783 RETCODE := 2;
1784 ERRBUF := SQLERRM;
1785 END;
1787 /*
1788 Modified to include an additional parameter to run in full mode.
1789 This concurrent program picks the groups associated with the object, and
1790 populates the resources in that group in the ams_act_access_denorm table.
1791 By default, it will pick only the groups which are modified after the most recent
1792 previous run date of conc. program.
1793 However, user can choose to run it in full mode, in which it will refresh every group
1794 from the object.
1795 */
1796 PROCEDURE ams_access_denorm ( errbuf OUT NOCOPY VARCHAR2
1797 , retcode OUT NOCOPY VARCHAR2
1798 , p_full_mode IN VARCHAR2 := Fnd_Api.G_FALSE
1799 )
1800 IS
1801 l_user_id NUMBER := fnd_global.user_id;
1802 l_login_id NUMBER := fnd_global.conc_login_id;
1803 l_sysdate DATE := SYSDATE;
1804 l_last_run_date DATE;
1805 l_program_application_id NUMBER := 530;
1806 l_concurrent_program_id NUMBER;
1807
1808 -- Get just the delta records for incremental mode
1809 CURSOR cur_get_access_changes (p_last_run_date DATE) IS
1810 SELECT user_or_role_id
1811 ,act_access_to_object_id
1812 ,arc_act_access_to_object
1813 ,admin_flag
1814 ,delete_flag
1815 ,creation_date
1816 ,last_update_date
1817 FROM ams_act_access
1818 WHERE arc_user_or_role_type = 'GROUP'
1819 AND last_update_date >= p_last_run_date;
1820
1821 -- Get all the records needed for full mode.
1822 CURSOR cur_get_all_access IS
1823 SELECT user_or_role_id
1824 ,act_access_to_object_id
1825 ,arc_act_access_to_object
1826 ,admin_flag
1827 ,delete_flag
1828 ,creation_date
1829 ,last_update_date
1830 FROM ams_act_access
1831 WHERE arc_user_or_role_type = 'GROUP';
1832
1833 CURSOR cur_get_conc_program_id IS
1834 SELECT concurrent_program_id
1835 FROM fnd_concurrent_programs
1836 WHERE application_id = 530
1837 AND concurrent_program_name = 'AMSADENO';
1838
1839 CURSOR cur_get_latest_start_date IS
1840 SELECT max(actual_start_date)
1841 FROM fnd_concurrent_requests
1842 WHERE program_application_id = l_program_application_id
1843 AND concurrent_program_id = l_concurrent_program_id
1844 AND status_code = 'C'
1845 AND phase_code = 'C';
1846
1847 -- Used only once i.e the first time ever this concurrent program is run
1848 -- Commenting this as we cannot rely on this statement as last_update_date
1849 -- could be even changed while a new object is created.
1850 /*
1851 CURSOR cur_get_latest_run_date IS
1852 SELECT max(last_update_date)
1853 FROM ams_act_access_denorm;
1854 */
1855 BEGIN
1856
1857 OPEN cur_get_conc_program_id;
1858 FETCH cur_get_conc_program_id INTO l_concurrent_program_id;
1859 CLOSE cur_get_conc_program_id;
1860 -- Get the most recent conc. request, and use that to drive the delta.
1861 OPEN cur_get_latest_start_date ;
1862 FETCH cur_get_latest_start_date INTO l_last_run_date;
1863 CLOSE cur_get_latest_start_date ;
1864
1865 IF (l_last_run_date IS NULL) OR p_full_mode IN (Fnd_Api.G_TRUE, 'Y') THEN
1866
1867 Ams_Utility_Pvt.Write_Conc_Log('** Start the bulk operations ');
1868
1869 bulk_all_delete_group ;
1870
1871 bulk_all_refresh_group ;
1872
1873 Ams_Utility_Pvt.Write_Conc_Log('** End the bulk operations ');
1874
1875 ELSE
1876 --l_last_run_date := SYSDATE - 1000000;
1877 Ams_Utility_Pvt.Write_Conc_Log('** Start the bulk operations : prev date ');
1878
1879 bulk_insert_group (l_last_run_date) ;
1880
1881 bulk_delete_group (l_last_run_date, 'AMS_ACCESS') ;
1882
1883 bulk_update_group (l_last_run_date) ;
1884 Ams_Utility_Pvt.Write_Conc_Log('** End the bulk operations : prev date');
1885
1886 END IF; -- IF p_full_mode IN (Fnd_Api.G_TRUE, 'Y') THEN
1887
1888 -- delete all the deleted group associations.
1889 DELETE ams_act_access
1890 WHERE arc_user_or_role_type = 'GROUP'
1891 AND delete_flag = 'Y' ;
1892
1893 -- return the success code.
1894 retcode := 0;
1895 EXCEPTION
1896 WHEN OTHERS THEN
1897 ROLLBACK;
1898 RETCODE := 2;
1899 ERRBUF := SQLERRM;
1900 END ams_access_denorm;
1901 /*
1902
1903 PROCEDURE jtf_access_denorm ( errbuf OUT NOCOPY VARCHAR2
1904 , retcode OUT NOCOPY VARCHAR2)
1905 IS
1906
1907 l_user_id NUMBER := fnd_global.user_id;
1908 l_login_id NUMBER := fnd_global.conc_login_id;
1909 l_sysdate DATE := sysdate;
1910 l_program_application_id NUMBER := 530;
1911 l_concurrent_program_id NUMBER;
1912
1913 -- CURSOR to operate on groups that are DELETEd
1914 CURSOR cur_get_object_grp_res( p_last_run_date DATE) IS
1915 SELECT act.act_access_to_object_id
1916 , act.arc_act_access_to_object
1917 , jrg.group_id
1918 , admin_flag
1919 FROM ams_act_access act,
1920 JTF_RS_GROUPS_B jrg
1921 WHERE act.arc_user_or_role_type = 'GROUP'
1922 AND act.user_or_role_id= jrg.group_id
1923 AND jrg.last_update_date >= p_last_run_date
1924 AND jrg.end_date_active <= trunc(sysdate)
1925 AND act.delete_flag = 'N';
1926
1927 -- CURSOR to operate on group relations that are created or DELETEd
1928 CURSOR cur_get_obj_grp_relation_res( p_last_run_date DATE) IS
1929 SELECT jrg.group_id
1930 , jrg.start_date_active
1931 , act.act_access_to_object_id
1932 , act.arc_act_access_to_object
1933 , jrg.creation_date
1934 , jrg.last_update_date
1935 , jrg.end_date_active
1936 , act.admin_flag
1940 AND act.user_or_role_id= jrg.group_id
1937 FROM ams_act_access act,
1938 jtf_rs_grp_relations jrg
1939 WHERE act.arc_user_or_role_type = 'GROUP'
1941 AND jrg.last_update_date >= p_last_run_date
1942 AND act.delete_flag = 'N';
1943
1944 -- CURSOR to operate on group members that are created or DELETEd
1945 CURSOR cur_get_object_res_groups( p_last_run_date DATE) IS
1946 SELECT aac.act_access_to_object_id
1947 , aac.arc_act_access_to_object
1948 , jgm.resource_id
1949 , jgm.delete_flag
1950 , jgm.creation_date
1951 , jgm.last_update_date
1952 ,aac.admin_flag
1953 FROM ams_act_access aac,
1954 jtf_rs_groups_denorm jrg,
1955 jtf_rs_group_members jgm
1956 WHERE aac.arc_user_or_role_type = 'GROUP'
1957 AND aac.user_or_role_id= jrg.parent_group_id
1958 AND jrg.group_id = jgm.group_id
1959 AND jrg.start_date_active <= trunc(sysdate)
1960 AND nvl(jrg.end_date_active,trunc(sysdate)) >= trunc(sysdate)
1961 AND jgm.last_update_date >= p_last_run_date
1962 AND aac.delete_flag='N';
1963
1964 CURSOR cur_get_conc_program_id IS
1965 SELECT concurrent_program_id
1966 FROM fnd_concurrent_programs
1967 WHERE application_id = 530
1968 AND concurrent_program_name = 'AMSJDENO';
1969
1970 CURSOR cur_get_latest_start_date IS
1971 SELECT MAX(actual_start_date)
1972 FROM fnd_concurrent_requests
1973 WHERE program_application_id = l_program_application_id
1974 AND concurrent_program_id = l_concurrent_program_id
1975 AND status_code = 'C'
1976 AND phase_code = 'C';
1977
1978 -- Used only once i.e the first time ever this concurrent program is run
1979 CURSOR cur_get_latest_run_date IS
1980 SELECT MAX(last_update_date)
1981 FROM ams_act_access_denorm;
1982
1983 l_last_run_date date;
1984
1985 BEGIN
1986
1987 OPEN cur_get_conc_program_id;
1988 FETCH cur_get_conc_program_id INTO l_concurrent_program_id;
1989 CLOSE cur_get_conc_program_id;
1990
1991 OPEN cur_get_latest_start_date;
1992 FETCH cur_get_latest_start_date INTO l_last_run_date;
1993 CLOSE cur_get_latest_start_date;
1994
1995 IF l_last_run_date is null THEN
1996 OPEN cur_get_latest_run_date;
1997 FETCH cur_get_latest_run_date INTO l_last_run_date;
1998 CLOSE cur_get_latest_run_date;
1999 END IF;
2000 -- l_last_run_date := sysdate - 1000000;
2001 FOR grp_res_rec IN cur_get_object_grp_res(l_last_run_date) LOOP
2002 --dbms_output.put_line(' groups ');
2003
2004 delete_group( p_group_id => grp_res_rec.group_id
2005 , p_object_type => grp_res_rec.arc_act_access_to_object
2006 , p_object_id => grp_res_rec.act_access_to_object_id
2007 , p_edit_metrics => grp_res_rec.admin_flag
2008 );
2009
2010 END LOOP;
2011
2012 FOR grprel_res_rec IN cur_get_obj_grp_relation_res(l_last_run_date) LOOP
2013 --dbms_output.put_line(' group relations ');
2014
2015 IF ( (grprel_res_rec.creation_date >= l_last_run_date )
2016 AND (nvl(grprel_res_rec.start_date_active,sysdate) <= sysdate)
2017 AND ( nvl(grprel_res_rec.end_date_active,sysdate) >= sysdate)
2018 )
2019 THEN
2020 --dbms_output.put_line(' insert group relations ');
2021
2022 insert_group( p_group_id => grprel_res_rec.group_id
2023 , p_object_type => grprel_res_rec.arc_act_access_to_object
2024 , p_object_id => grprel_res_rec.act_access_to_object_id
2025 , p_edit_metrics => grprel_res_rec.admin_flag
2026 );
2027
2028 ELSIF ( nvl(grprel_res_rec.end_date_active,sysdate) <= sysdate ) THEN
2029 --dbms_output.put_line(' DELETE group relations ');
2030
2031 delete_group( p_group_id => grprel_res_rec.group_id
2032 , p_object_type => grprel_res_rec.arc_act_access_to_object
2033 , p_object_id => grprel_res_rec.act_access_to_object_id
2034 , p_edit_metrics => grprel_res_rec.admin_flag
2035 );
2036
2037 END IF;
2038
2039
2040 END LOOP;
2041
2042 FOR grpmembers_rec IN cur_get_object_res_groups(l_last_run_date) LOOP
2043 --dbms_output.put_line(' DELETE group members');
2044 IF ((grpmembers_rec.creation_date >= l_last_run_date )
2045 AND ( grpmembers_rec.delete_flag = 'N') )
2046 THEN
2047 -- dbms_output.put_line(' insert group members');
2048
2049 insert_resource( p_resource_id => grpmembers_rec.resource_id
2050 , p_object_type => grpmembers_rec.arc_act_access_to_object
2051 , p_object_id => grpmembers_rec.act_access_to_object_id
2052 , p_edit_metrics => grpmembers_rec.admin_flag
2053 );
2054
2055 ELSIF ( (grpmembers_rec.delete_flag = 'Y') ) THEN
2056 --dbms_output.put_line(' DELETE group members');
2057
2058 delete_resource( p_resource_id => grpmembers_rec.resource_id
2059 , p_object_type => grpmembers_rec.arc_act_access_to_object
2060 , p_object_id => grpmembers_rec.act_access_to_object_id
2061 , p_edit_metrics => grpmembers_rec.admin_flag
2062 );
2063 END IF;
2064
2065 END LOOP;
2066 retcode := 0;
2067 end;
2068 */
2069
2070 PROCEDURE jtf_access_denorm ( errbuf OUT NOCOPY VARCHAR2
2071 , retcode OUT NOCOPY VARCHAR2
2072 )
2073 IS
2074
2075 l_user_id NUMBER := fnd_global.user_id;
2076 l_login_id NUMBER := fnd_global.conc_login_id;
2077 l_sysdate DATE := SYSDATE;
2078 l_program_application_id NUMBER := 530;
2079 l_concurrent_program_id NUMBER;
2080
2081 -- CURSOR to operate on groups that are deleted
2085 , jrg.group_id
2082 CURSOR cur_get_del_grp(p_last_run_date DATE) IS
2083 SELECT act.act_access_to_object_id
2084 , act.arc_act_access_to_object
2086 , admin_flag
2087 FROM ams_act_access act,
2088 jtf_rs_groups_b jrg
2089 WHERE act.arc_user_or_role_type = 'GROUP'
2090 AND act.user_or_role_id= jrg.group_id
2091 AND act.delete_flag = 'N'
2092 -- AND jrg.last_update_date >= p_last_run_date -- this will not pick any rows which are end dated in future.
2093 AND jrg.end_date_active IS NOT NULL
2094 AND jrg.end_date_active >= p_last_run_date -- added to pick only the rows which are ending after previous run.
2095 AND jrg.end_date_active <= TRUNC(SYSDATE) ;
2096
2097 -- CURSOR to get group members that are created through new child group relationship
2098 -- or got created because of changing the start date of child relationship after previous run
2099 -- or got created manually in the main group or child group.
2100 -- Replaced the following cursor - Replace OR's with unions as suggested by Perf Team in
2101 -- Bug 3071312
2102 /*
2103 CURSOR cur_get_crt_res(p_last_run_date DATE) IS
2104 SELECT aac.act_access_to_object_id
2105 , aac.arc_act_access_to_object
2106 , jgm.resource_id
2107 , jgm.last_update_date
2108 , aac.admin_flag
2109 FROM ams_act_access aac,
2110 jtf_rs_groups_denorm jrg,
2111 jtf_rs_group_members jgm
2112 WHERE
2113 ( aac.arc_user_or_role_type = 'GROUP'
2114 AND aac.user_or_role_id= jrg.parent_group_id
2115 AND aac.delete_flag='N'
2116 AND jgm.delete_flag = 'N'
2117 AND jrg.group_id = jgm.group_id
2118 AND jrg.start_date_active >= p_last_run_date
2119 AND jrg.start_date_active <= TRUNC(SYSDATE)
2120 )
2121 OR
2122 ( aac.arc_user_or_role_type = 'GROUP'
2123 AND aac.user_or_role_id= jrg.parent_group_id
2124 AND aac.delete_flag='N'
2125 AND jgm.delete_flag = 'N'
2126 AND jrg.group_id = jgm.group_id
2127 AND jrg.last_update_date > p_last_run_date
2128 AND jrg.start_date_active <= TRUNC(SYSDATE)
2129 )
2130 OR
2131 ( aac.arc_user_or_role_type = 'GROUP'
2132 AND aac.user_or_role_id= jrg.parent_group_id
2133 AND aac.delete_flag='N'
2134 AND jgm.delete_flag = 'N'
2135 AND jrg.group_id = jgm.group_id
2136 AND jgm.creation_date > p_last_run_date
2137 );
2138 */
2139 CURSOR cur_get_crt_res(p_last_run_date DATE) IS
2140 SELECT aac.act_access_to_object_id
2141 , aac.arc_act_access_to_object
2142 , jgm.resource_id
2143 , jgm.last_update_date
2144 , aac.admin_flag
2145 FROM ams_act_access aac,
2146 jtf_rs_groups_denorm jrg,
2147 jtf_rs_group_members jgm
2148 WHERE
2149 ( aac.arc_user_or_role_type = 'GROUP'
2150 AND aac.user_or_role_id= jrg.parent_group_id
2151 AND aac.delete_flag='N'
2152 AND jgm.delete_flag = 'N'
2153 AND jrg.group_id = jgm.group_id
2154 AND jrg.start_date_active >= p_last_run_date
2155 AND jrg.start_date_active <= TRUNC(SYSDATE)
2156 )
2157 UNION
2158 SELECT aac.act_access_to_object_id
2159 , aac.arc_act_access_to_object
2160 , jgm.resource_id
2161 , jgm.last_update_date
2162 , aac.admin_flag
2163 FROM ams_act_access aac,
2164 jtf_rs_groups_denorm jrg,
2165 jtf_rs_group_members jgm
2166 WHERE
2167 ( aac.arc_user_or_role_type = 'GROUP'
2168 AND aac.user_or_role_id= jrg.parent_group_id
2169 AND aac.delete_flag='N'
2170 AND jgm.delete_flag = 'N'
2171 AND jrg.group_id = jgm.group_id
2172 AND jrg.last_update_date > p_last_run_date
2173 AND jrg.start_date_active <= TRUNC(SYSDATE)
2174 )
2175 UNION
2176 SELECT aac.act_access_to_object_id
2177 , aac.arc_act_access_to_object
2178 , jgm.resource_id
2179 , jgm.last_update_date
2180 , aac.admin_flag
2181 FROM ams_act_access aac,
2182 jtf_rs_groups_denorm jrg,
2183 jtf_rs_group_members jgm
2184 WHERE
2185 ( aac.arc_user_or_role_type = 'GROUP'
2186 AND aac.user_or_role_id= jrg.parent_group_id
2187 AND aac.delete_flag='N'
2188 AND jgm.delete_flag = 'N'
2189 AND jrg.group_id = jgm.group_id
2190 AND jgm.creation_date > p_last_run_date
2191 );
2192
2193 -- CURSOR to get group members that are deleted by end-dating child group relationship
2194 -- or those changed end-date in the child group relationship after previous run
2195 -- or got deleted manually.
2196 -- Cursor replaced by those suggested by Perf Team as per Bug 3071312
2197 /*
2198 CURSOR cur_get_del_res(p_last_run_date DATE) IS
2199 SELECT aac.act_access_to_object_id
2200 , aac.arc_act_access_to_object
2201 , jgm.resource_id
2202 , jgm.last_update_date
2203 , aac.admin_flag
2204 FROM ams_act_access aac,
2205 jtf_rs_groups_denorm jrg,
2206 jtf_rs_group_members jgm
2207 WHERE
2208 ( aac.arc_user_or_role_type = 'GROUP'
2209 AND aac.user_or_role_id= jrg.parent_group_id
2210 AND aac.delete_flag='N'
2211 AND jrg.group_id = jgm.group_id
2212 AND jrg.end_date_active IS NOT NULL
2213 AND jrg.end_date_active >= p_last_run_date
2214 AND jrg.end_date_active <= TRUNC(SYSDATE)
2215 )
2216 OR
2217 ( aac.arc_user_or_role_type = 'GROUP'
2218 AND aac.user_or_role_id= jrg.parent_group_id
2219 AND aac.delete_flag='N'
2220 AND jrg.group_id = jgm.group_id
2221 AND jrg.last_update_date > p_last_run_date
2225 OR
2222 AND jrg.end_date_active IS NOT NULL
2223 AND jrg.end_date_active <= TRUNC(SYSDATE)
2224 )
2226 ( aac.arc_user_or_role_type = 'GROUP'
2227 AND aac.user_or_role_id= jrg.parent_group_id
2228 AND aac.delete_flag='N'
2229 AND jgm.delete_flag = 'Y'
2230 AND jrg.group_id = jgm.group_id
2231 AND jgm.last_update_date >= p_last_run_date
2232 );
2233 */
2234 CURSOR cur_get_del_res(p_last_run_date DATE) IS
2235 SELECT aac.act_access_to_object_id
2236 , aac.arc_act_access_to_object
2237 , jgm.resource_id
2238 , jgm.last_update_date
2239 , aac.admin_flag
2240 FROM ams_act_access aac,
2241 jtf_rs_groups_denorm jrg,
2242 jtf_rs_group_members jgm
2243 WHERE
2244 ( aac.arc_user_or_role_type = 'GROUP'
2245 AND aac.user_or_role_id= jrg.parent_group_id
2246 AND aac.delete_flag='N'
2247 AND jrg.group_id = jgm.group_id
2248 AND jrg.end_date_active IS NOT NULL
2249 AND jrg.end_date_active >= p_last_run_date
2250 AND jrg.end_date_active <= TRUNC(SYSDATE)
2251 )
2252 UNION
2253 SELECT aac.act_access_to_object_id
2254 , aac.arc_act_access_to_object
2255 , jgm.resource_id
2256 , jgm.last_update_date
2257 , aac.admin_flag
2258 FROM ams_act_access aac,
2259 jtf_rs_groups_denorm jrg,
2260 jtf_rs_group_members jgm
2261 WHERE
2262 ( aac.arc_user_or_role_type = 'GROUP'
2263 AND aac.user_or_role_id= jrg.parent_group_id
2264 AND aac.delete_flag='N'
2265 AND jrg.group_id = jgm.group_id
2266 AND jrg.last_update_date > p_last_run_date
2267 AND jrg.end_date_active IS NOT NULL
2268 AND jrg.end_date_active <= TRUNC(SYSDATE)
2269 )
2270 UNION
2271 SELECT aac.act_access_to_object_id
2272 , aac.arc_act_access_to_object
2273 , jgm.resource_id
2274 , jgm.last_update_date
2275 , aac.admin_flag
2276 FROM ams_act_access aac,
2277 jtf_rs_groups_denorm jrg,
2278 jtf_rs_group_members jgm
2279 WHERE
2280 ( aac.arc_user_or_role_type = 'GROUP'
2281 AND aac.user_or_role_id= jrg.parent_group_id
2282 AND aac.delete_flag='N'
2283 AND jgm.delete_flag = 'Y'
2284 AND jrg.group_id = jgm.group_id
2285 AND jgm.last_update_date >= p_last_run_date
2286 );
2287
2288 CURSOR cur_get_conc_program_id IS
2289 SELECT concurrent_program_id
2290 FROM fnd_concurrent_programs
2291 WHERE concurrent_program_name = 'AMSJDENO';
2292
2293 CURSOR cur_get_latest_start_date IS
2294 SELECT MAX(actual_start_date)
2295 FROM fnd_concurrent_requests
2296 WHERE program_application_id = l_program_application_id
2297 AND concurrent_program_id = l_concurrent_program_id
2298 AND status_code = 'C'
2299 AND phase_code = 'C';
2300
2301 -- Used only once i.e the first time ever this concurrent program is run
2302 -- Use the minimum last_update_date
2303 CURSOR cur_get_latest_run_date IS
2304 SELECT MIN(last_update_date)
2305 FROM ams_act_access_denorm;
2306 l_last_run_date DATE;
2307
2308 BEGIN
2309
2310 OPEN cur_get_conc_program_id;
2311 FETCH cur_get_conc_program_id INTO l_concurrent_program_id;
2312 CLOSE cur_get_conc_program_id;
2313
2314 OPEN cur_get_latest_start_date;
2315 FETCH cur_get_latest_start_date INTO l_last_run_date;
2316 CLOSE cur_get_latest_start_date;
2317
2318 IF l_last_run_date IS NULL THEN
2319 OPEN cur_get_latest_run_date;
2320 FETCH cur_get_latest_run_date INTO l_last_run_date;
2321 CLOSE cur_get_latest_run_date;
2322 END IF;
2323
2324 -- handle all the groups which are directly associated to the objects, and are deleted.
2325 bulk_delete_group (l_last_run_date, 'JTF_ACCESS') ;
2326
2327 -- create all the resources which are added manually in the main group or child group
2328 -- and, also create all the resources came via new child group relations.
2329
2330 bulk_insert_resource(l_last_run_date) ;
2331
2332 -- delete all the resources which are deleted manually in the main group or child group
2333 -- and, also deleted because child group relationship is end-dated.
2334
2335 bulk_delete_resource(l_last_run_date) ;
2336
2337 retcode := 0;
2338 EXCEPTION
2339 WHEN OTHERS THEN
2340 ROLLBACK;
2341 RETCODE := 2;
2342 ERRBUF := SQLERRM;
2343 end jtf_access_denorm;
2344 end ams_access_denorm_pvt;