1 PACKAGE BODY AMS_access_denorm_PVT AS
2 /* $Header: amsvdenb.pls 115.13 2004/06/16 10:49:19 vmodur 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
235 AND object_id = p_object_id
232 FROM ams_act_access_denorm
233 WHERE resource_id = p_resource_id
234 AND object_type = p_object_type
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)
324 AND aac.admin_flag='Y' );
321 AND jgm.delete_flag='N'
322 AND jgm.resource_id = aacd.resource_id
323 AND aac.delete_flag = 'N'
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
406 l_user_id NUMBER := fnd_global.user_id;
407 l_login_id NUMBER := fnd_global.conc_login_id;
408 l_sysdate DATE := SYSDATE;
409 BEGIN
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
442 , creation_date
439 , object_type
440 , object_id
441 , source_code
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)
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
542 AND aac.delete_flag = 'N'
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
562 FROM ams_act_access aac,
559 AND aac.delete_flag = 'N'
560 )
561 AND NOT EXISTS ( SELECT 1
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
651 AND resource_id NOT IN ( SELECT jgm.resource_id
652 FROM ams_act_access aac,
653 jtf_rs_groups_denorm jgd,
654 jtf_rs_group_members jgm
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)
665 AND aac.delete_flag = 'N'
662 AND jgm.delete_flag='N'
663 AND jgm.resource_id = aacd.resource_id
664 AND aac.admin_flag = 'Y'
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 PROCEDURE refresh_group( p_group_id IN NUMBER
683 , p_object_type IN VARCHAR2
684 , p_object_id IN NUMBER
685 , p_edit_metrics IN VARCHAR2
686 )
687 IS
688 BEGIN
689 -- add new resources which are not present in the denorm table.
690 insert_group( p_group_id => p_group_id
691 , p_object_type => p_object_type
692 , p_object_id => p_object_id
693 , p_edit_metrics => p_edit_metrics
694 );
695 -- update the edit metrics in the denorm table.
696 update_group( p_group_id => p_group_id
697 , p_object_type => p_object_type
698 , p_object_id => p_object_id
699 , p_edit_metrics => p_edit_metrics
700 );
701 -- delete the resources in the denorm table which are end-dated,
702 -- or no longer present in the group.
703 -- this deleted resource should not be part of any other active group or the user of
704 -- the object.
705 DELETE FROM ams_act_access_denorm aacd
706 WHERE aacd.object_type = p_object_type
707 AND aacd.object_id = p_object_id
708 AND NOT EXISTS ( SELECT 1 --anchaudh 21-MAR-03
709 FROM jtf_rs_groups_denorm jgd,
710 jtf_rs_group_members jgm
711 WHERE jgd.parent_group_id = p_group_id
712 AND jgd.group_id = jgm.group_id
713 AND jgd.start_date_active <= TRUNC(SYSDATE)
714 AND NVL(jgd.end_date_active,SYSDATE) >= TRUNC(SYSDATE)
715 AND jgm.delete_flag='N'
716 AND jgm.resource_id = aacd.resource_id) --anchaudh 21-MAR-03
717 AND NOT EXISTS ( SELECT 1
718 FROM ams_act_access aac,
719 jtf_rs_groups_denorm jgd,
720 jtf_rs_group_members jgm
721 WHERE aac.arc_act_access_to_object = p_object_type
722 AND aac.act_access_to_object_id = p_object_id
723 AND arc_user_or_role_type = 'GROUP'
724 AND aac.delete_flag = 'N'
725 AND user_or_role_id = jgd.parent_group_id
726 AND jgd.group_id = jgm.group_id
727 AND jgd.start_date_active <= TRUNC(SYSDATE)
728 AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
729 AND jgm.delete_flag = 'N'
730 AND jgm.resource_id = aacd.resource_id)
731 AND NOT EXISTS ( --anchaudh 21-MAR-03
732 SELECT 1
733 FROM ams_act_access
734 WHERE arc_act_access_to_object = p_object_type
735 AND act_access_to_object_id = p_object_id
736 AND arc_user_or_role_type = 'USER'
737 AND user_or_role_id = aacd.resource_id ) ;
738 END refresh_group;
739
740 PROCEDURE ams_object_denorm ( errbuf OUT NOCOPY VARCHAR2
741 , retcode OUT NOCOPY VARCHAR2
742 , p_object_id IN NUMBER
743 , p_object_type IN VARCHAR2 )
744 IS
745 CURSOR cur_get_object_changes IS
746 SELECT arc_user_or_role_type
747 ,user_or_role_id
748 ,act_access_to_object_id
749 ,arc_act_access_to_object
750 ,admin_flag
751 FROM ams_act_access
752 WHERE act_access_to_object_id = p_object_id
753 AND arc_act_access_to_object = p_object_type
754 AND arc_user_or_role_type = 'GROUP'
755 AND delete_flag = 'N';
756
757 l_user_id NUMBER := fnd_global.user_id;
758 l_login_id NUMBER := fnd_global.conc_login_id;
759 l_sysdate DATE := SYSDATE;
760
761 BEGIN
762
763 DELETE FROM ams_act_access_denorm aacd
764 WHERE aacd.object_type = p_object_type
765 AND aacd.object_id = p_object_id;
766
767 INSERT INTO ams_act_access_denorm
768 (
769 access_denorm_id
770 , resource_id
771 , edit_metrics_yn
772 , object_type
773 , object_id
774 , source_code
775 , creation_date
776 , created_by
780 )
777 , last_update_date
778 , last_updated_by
779 , last_update_login
781 SELECT ams_act_access_denorm_s.nextval
782 ,user_or_role_id
783 ,admin_flag
784 ,arc_act_access_to_object
785 ,act_access_to_object_id
786 ,ams_access_pvt.get_source_code(arc_act_access_to_object,act_access_to_object_id)
787 ,l_sysdate
788 ,l_user_id
789 ,l_sysdate
790 ,l_user_id
791 ,l_login_id
792 FROM ams_act_access
793 WHERE arc_act_access_to_object = p_object_type
794 AND act_access_to_object_id = p_object_id
795 AND arc_user_or_role_type = 'USER';
796
797 FOR object_rec IN cur_get_object_changes LOOP
798
799 insert_group( object_rec.user_or_role_id
800 ,object_rec.arc_act_access_to_object
801 ,object_rec.act_access_to_object_id
802 ,object_rec.admin_flag
803 );
804
805 END LOOP;
806
807 DELETE FROM ams_act_access
808 WHERE arc_user_or_role_type = 'GROUP'
809 AND arc_act_access_to_object = p_object_type
810 AND act_access_to_object_id = p_object_id
811 AND delete_flag = 'Y' ;
812
813 RETCODE := 0;
814 EXCEPTION
815 WHEN OTHERS THEN
816 RETCODE := 2;
817 ERRBUF := SQLERRM;
818 END;
819
820 /*
821 Modified to include an additional parameter to run in full mode.
822 This concurrent program picks the groups associated with the object, and
823 populates the resources in that group in the ams_act_access_denorm table.
824 By default, it will pick only the groups which are modified after the most recent
825 previous run date of conc. program.
826 However, user can choose to run it in full mode, in which it will refresh every group
827 from the object.
828 */
829 PROCEDURE ams_access_denorm ( errbuf OUT NOCOPY VARCHAR2
830 , retcode OUT NOCOPY VARCHAR2
831 , p_full_mode IN VARCHAR2 := Fnd_Api.G_FALSE
832 )
833 IS
834 l_user_id NUMBER := fnd_global.user_id;
835 l_login_id NUMBER := fnd_global.conc_login_id;
836 l_sysdate DATE := SYSDATE;
837 l_last_run_date DATE;
838 l_program_application_id NUMBER := 530;
839 l_concurrent_program_id NUMBER;
840
841 -- Get just the delta records for incremental mode
842 CURSOR cur_get_access_changes (p_last_run_date DATE) IS
843 SELECT user_or_role_id
844 ,act_access_to_object_id
845 ,arc_act_access_to_object
846 ,admin_flag
847 ,delete_flag
848 ,creation_date
849 ,last_update_date
850 FROM ams_act_access
851 WHERE arc_user_or_role_type = 'GROUP'
852 AND last_update_date >= p_last_run_date;
853
854 -- Get all the records needed for full mode.
855 CURSOR cur_get_all_access IS
856 SELECT user_or_role_id
857 ,act_access_to_object_id
858 ,arc_act_access_to_object
859 ,admin_flag
860 ,delete_flag
861 ,creation_date
862 ,last_update_date
863 FROM ams_act_access
864 WHERE arc_user_or_role_type = 'GROUP';
865
866 CURSOR cur_get_conc_program_id IS
867 SELECT concurrent_program_id
868 FROM fnd_concurrent_programs
869 WHERE application_id = 530
870 AND concurrent_program_name = 'AMSADENO';
871
872 CURSOR cur_get_latest_start_date IS
873 SELECT max(actual_start_date)
874 FROM fnd_concurrent_requests
875 WHERE program_application_id = l_program_application_id
876 AND concurrent_program_id = l_concurrent_program_id
877 AND status_code = 'C'
878 AND phase_code = 'C';
879
880 -- Used only once i.e the first time ever this concurrent program is run
881 -- Commenting this as we cannot rely on this statement as last_update_date
882 -- could be even changed while a new object is created.
883 /*
884 CURSOR cur_get_latest_run_date IS
885 SELECT max(last_update_date)
886 FROM ams_act_access_denorm;
887 */
888 BEGIN
889
890 OPEN cur_get_conc_program_id;
891 FETCH cur_get_conc_program_id INTO l_concurrent_program_id;
892 CLOSE cur_get_conc_program_id;
893 -- Get the most recent conc. request, and use that to drive the delta.
894 OPEN cur_get_latest_start_date ;
895 FETCH cur_get_latest_start_date INTO l_last_run_date;
896 CLOSE cur_get_latest_start_date ;
897
898 IF (l_last_run_date IS NULL) OR p_full_mode IN (Fnd_Api.G_TRUE, 'Y') THEN
899 FOR l_all_access_rec IN cur_get_all_access LOOP
900 IF (l_all_access_rec.delete_flag = 'Y') THEN
901 delete_group( p_group_id => l_all_access_rec.user_or_role_id
902 , p_object_type => l_all_access_rec.arc_act_access_to_object
903 , p_object_id => l_all_access_rec.act_access_to_object_id
904 , p_edit_metrics => l_all_access_rec.admin_flag
905 );
906 ELSIF (l_all_access_rec.delete_flag = 'N') THEN
907 refresh_group( p_group_id => l_all_access_rec.user_or_role_id
908 , p_object_type => l_all_access_rec.arc_act_access_to_object
909 , p_object_id => l_all_access_rec.act_access_to_object_id
913 END LOOP;
910 , p_edit_metrics => l_all_access_rec.admin_flag
911 );
912 END IF;
914 ELSE
915 --l_last_run_date := SYSDATE - 1000000;
916 FOR access_rec IN cur_get_access_changes(l_last_run_date) LOOP
917 IF ((access_rec.creation_date > l_last_run_date) AND (access_rec.delete_flag = 'N') ) THEN
918 insert_group( p_group_id => access_rec.user_or_role_id
919 , p_object_type => access_rec.arc_act_access_to_object
920 , p_object_id => access_rec.act_access_to_object_id
921 , p_edit_metrics => access_rec.admin_flag
922 );
923 ELSIF ( (access_rec.last_update_date > l_last_run_date) AND (access_rec.delete_flag = 'Y') ) THEN
924 delete_group( p_group_id => access_rec.user_or_role_id
925 , p_object_type => access_rec.arc_act_access_to_object
926 , p_object_id => access_rec.act_access_to_object_id
927 , p_edit_metrics => access_rec.admin_flag
928 );
929
930 ELSIF ( (access_rec.last_update_date > l_last_run_date) AND (access_rec.delete_flag = 'N') ) THEN
931 --dbms_output.put_line('-- Only change that could have happened is that edit metrics could have changed.');
932 update_group( p_group_id => access_rec.user_or_role_id
933 , p_object_type => access_rec.arc_act_access_to_object
934 , p_object_id => access_rec.act_access_to_object_id
935 , p_edit_metrics => access_rec.admin_flag
936 );
937 END IF;
938 END LOOP; -- for FOR access_rec IN cur_get_access_changes(l_last_run_date) LOOP
939 END IF; -- IF p_full_mode IN (Fnd_Api.G_TRUE, 'Y') THEN
940
941 -- delete all the deleted group associations.
942 DELETE ams_act_access
943 WHERE arc_user_or_role_type = 'GROUP'
944 AND delete_flag = 'Y' ;
945
946 -- return the success code.
947 retcode := 0;
948 EXCEPTION
949 WHEN OTHERS THEN
950 ROLLBACK;
951 RETCODE := 2;
952 ERRBUF := SQLERRM;
953 END ams_access_denorm;
954 /*
955
956 PROCEDURE jtf_access_denorm ( errbuf OUT NOCOPY VARCHAR2
957 , retcode OUT NOCOPY VARCHAR2)
958 IS
959
960 l_user_id NUMBER := fnd_global.user_id;
961 l_login_id NUMBER := fnd_global.conc_login_id;
962 l_sysdate DATE := sysdate;
963 l_program_application_id NUMBER := 530;
964 l_concurrent_program_id NUMBER;
965
966 -- CURSOR to operate on groups that are DELETEd
967 CURSOR cur_get_object_grp_res( p_last_run_date DATE) IS
968 SELECT act.act_access_to_object_id
969 , act.arc_act_access_to_object
970 , jrg.group_id
971 , admin_flag
972 FROM ams_act_access act,
973 JTF_RS_GROUPS_B jrg
974 WHERE act.arc_user_or_role_type = 'GROUP'
975 AND act.user_or_role_id= jrg.group_id
976 AND jrg.last_update_date >= p_last_run_date
977 AND jrg.end_date_active <= trunc(sysdate)
978 AND act.delete_flag = 'N';
979
980 -- CURSOR to operate on group relations that are created or DELETEd
981 CURSOR cur_get_obj_grp_relation_res( p_last_run_date DATE) IS
982 SELECT jrg.group_id
983 , jrg.start_date_active
984 , act.act_access_to_object_id
985 , act.arc_act_access_to_object
986 , jrg.creation_date
987 , jrg.last_update_date
988 , jrg.end_date_active
989 , act.admin_flag
990 FROM ams_act_access act,
991 jtf_rs_grp_relations jrg
992 WHERE act.arc_user_or_role_type = 'GROUP'
993 AND act.user_or_role_id= jrg.group_id
994 AND jrg.last_update_date >= p_last_run_date
995 AND act.delete_flag = 'N';
996
997 -- CURSOR to operate on group members that are created or DELETEd
998 CURSOR cur_get_object_res_groups( p_last_run_date DATE) IS
999 SELECT aac.act_access_to_object_id
1000 , aac.arc_act_access_to_object
1001 , jgm.resource_id
1002 , jgm.delete_flag
1003 , jgm.creation_date
1004 , jgm.last_update_date
1005 ,aac.admin_flag
1006 FROM ams_act_access aac,
1007 jtf_rs_groups_denorm jrg,
1008 jtf_rs_group_members jgm
1009 WHERE aac.arc_user_or_role_type = 'GROUP'
1010 AND aac.user_or_role_id= jrg.parent_group_id
1011 AND jrg.group_id = jgm.group_id
1012 AND jrg.start_date_active <= trunc(sysdate)
1013 AND nvl(jrg.end_date_active,trunc(sysdate)) >= trunc(sysdate)
1014 AND jgm.last_update_date >= p_last_run_date
1015 AND aac.delete_flag='N';
1016
1017 CURSOR cur_get_conc_program_id IS
1018 SELECT concurrent_program_id
1019 FROM fnd_concurrent_programs
1020 WHERE application_id = 530
1021 AND concurrent_program_name = 'AMSJDENO';
1022
1023 CURSOR cur_get_latest_start_date IS
1024 SELECT MAX(actual_start_date)
1025 FROM fnd_concurrent_requests
1026 WHERE program_application_id = l_program_application_id
1027 AND concurrent_program_id = l_concurrent_program_id
1028 AND status_code = 'C'
1029 AND phase_code = 'C';
1030
1031 -- Used only once i.e the first time ever this concurrent program is run
1032 CURSOR cur_get_latest_run_date IS
1033 SELECT MAX(last_update_date)
1034 FROM ams_act_access_denorm;
1035
1039
1036 l_last_run_date date;
1037
1038 BEGIN
1040 OPEN cur_get_conc_program_id;
1041 FETCH cur_get_conc_program_id INTO l_concurrent_program_id;
1042 CLOSE cur_get_conc_program_id;
1043
1044 OPEN cur_get_latest_start_date;
1045 FETCH cur_get_latest_start_date INTO l_last_run_date;
1046 CLOSE cur_get_latest_start_date;
1047
1048 IF l_last_run_date is null THEN
1049 OPEN cur_get_latest_run_date;
1050 FETCH cur_get_latest_run_date INTO l_last_run_date;
1051 CLOSE cur_get_latest_run_date;
1052 END IF;
1053 -- l_last_run_date := sysdate - 1000000;
1054 FOR grp_res_rec IN cur_get_object_grp_res(l_last_run_date) LOOP
1055 --dbms_output.put_line(' groups ');
1056
1057 delete_group( p_group_id => grp_res_rec.group_id
1058 , p_object_type => grp_res_rec.arc_act_access_to_object
1059 , p_object_id => grp_res_rec.act_access_to_object_id
1060 , p_edit_metrics => grp_res_rec.admin_flag
1061 );
1062
1063 END LOOP;
1064
1065 FOR grprel_res_rec IN cur_get_obj_grp_relation_res(l_last_run_date) LOOP
1066 --dbms_output.put_line(' group relations ');
1067
1068 IF ( (grprel_res_rec.creation_date >= l_last_run_date )
1069 AND (nvl(grprel_res_rec.start_date_active,sysdate) <= sysdate)
1070 AND ( nvl(grprel_res_rec.end_date_active,sysdate) >= sysdate)
1071 )
1072 THEN
1073 --dbms_output.put_line(' insert group relations ');
1074
1075 insert_group( p_group_id => grprel_res_rec.group_id
1076 , p_object_type => grprel_res_rec.arc_act_access_to_object
1077 , p_object_id => grprel_res_rec.act_access_to_object_id
1078 , p_edit_metrics => grprel_res_rec.admin_flag
1079 );
1080
1081 ELSIF ( nvl(grprel_res_rec.end_date_active,sysdate) <= sysdate ) THEN
1082 --dbms_output.put_line(' DELETE group relations ');
1083
1084 delete_group( p_group_id => grprel_res_rec.group_id
1085 , p_object_type => grprel_res_rec.arc_act_access_to_object
1086 , p_object_id => grprel_res_rec.act_access_to_object_id
1087 , p_edit_metrics => grprel_res_rec.admin_flag
1088 );
1089
1090 END IF;
1091
1092
1093 END LOOP;
1094
1095 FOR grpmembers_rec IN cur_get_object_res_groups(l_last_run_date) LOOP
1096 --dbms_output.put_line(' DELETE group members');
1097 IF ((grpmembers_rec.creation_date >= l_last_run_date )
1098 AND ( grpmembers_rec.delete_flag = 'N') )
1099 THEN
1100 -- dbms_output.put_line(' insert group members');
1101
1102 insert_resource( p_resource_id => grpmembers_rec.resource_id
1103 , p_object_type => grpmembers_rec.arc_act_access_to_object
1104 , p_object_id => grpmembers_rec.act_access_to_object_id
1105 , p_edit_metrics => grpmembers_rec.admin_flag
1106 );
1107
1108 ELSIF ( (grpmembers_rec.delete_flag = 'Y') ) THEN
1109 --dbms_output.put_line(' DELETE group members');
1110
1111 delete_resource( p_resource_id => grpmembers_rec.resource_id
1112 , p_object_type => grpmembers_rec.arc_act_access_to_object
1113 , p_object_id => grpmembers_rec.act_access_to_object_id
1114 , p_edit_metrics => grpmembers_rec.admin_flag
1115 );
1116 END IF;
1117
1118 END LOOP;
1119 retcode := 0;
1120 end;
1121 */
1122
1123 PROCEDURE jtf_access_denorm ( errbuf OUT NOCOPY VARCHAR2
1124 , retcode OUT NOCOPY VARCHAR2
1125 )
1126 IS
1127
1128 l_user_id NUMBER := fnd_global.user_id;
1129 l_login_id NUMBER := fnd_global.conc_login_id;
1130 l_sysdate DATE := SYSDATE;
1131 l_program_application_id NUMBER := 530;
1132 l_concurrent_program_id NUMBER;
1133
1134 -- CURSOR to operate on groups that are deleted
1135 CURSOR cur_get_del_grp(p_last_run_date DATE) IS
1136 SELECT act.act_access_to_object_id
1137 , act.arc_act_access_to_object
1138 , jrg.group_id
1139 , admin_flag
1140 FROM ams_act_access act,
1141 jtf_rs_groups_b jrg
1142 WHERE act.arc_user_or_role_type = 'GROUP'
1143 AND act.user_or_role_id= jrg.group_id
1144 AND act.delete_flag = 'N'
1145 -- AND jrg.last_update_date >= p_last_run_date -- this will not pick any rows which are end dated in future.
1146 AND jrg.end_date_active IS NOT NULL
1147 AND jrg.end_date_active >= p_last_run_date -- added to pick only the rows which are ending after previous run.
1148 AND jrg.end_date_active <= TRUNC(SYSDATE) ;
1149
1150 -- CURSOR to get group members that are created through new child group relationship
1151 -- or got created because of changing the start date of child relationship after previous run
1152 -- or got created manually in the main group or child group.
1153 -- Replaced the following cursor - Replace OR's with unions as suggested by Perf Team in
1154 -- Bug 3071312
1155 /*
1156 CURSOR cur_get_crt_res(p_last_run_date DATE) IS
1157 SELECT aac.act_access_to_object_id
1158 , aac.arc_act_access_to_object
1159 , jgm.resource_id
1160 , jgm.last_update_date
1161 , aac.admin_flag
1162 FROM ams_act_access aac,
1163 jtf_rs_groups_denorm jrg,
1164 jtf_rs_group_members jgm
1165 WHERE
1169 AND jgm.delete_flag = 'N'
1166 ( aac.arc_user_or_role_type = 'GROUP'
1167 AND aac.user_or_role_id= jrg.parent_group_id
1168 AND aac.delete_flag='N'
1170 AND jrg.group_id = jgm.group_id
1171 AND jrg.start_date_active >= p_last_run_date
1172 AND jrg.start_date_active <= TRUNC(SYSDATE)
1173 )
1174 OR
1175 ( aac.arc_user_or_role_type = 'GROUP'
1176 AND aac.user_or_role_id= jrg.parent_group_id
1177 AND aac.delete_flag='N'
1178 AND jgm.delete_flag = 'N'
1182 )
1179 AND jrg.group_id = jgm.group_id
1180 AND jrg.last_update_date > p_last_run_date
1181 AND jrg.start_date_active <= TRUNC(SYSDATE)
1183 OR
1184 ( aac.arc_user_or_role_type = 'GROUP'
1185 AND aac.user_or_role_id= jrg.parent_group_id
1186 AND aac.delete_flag='N'
1187 AND jgm.delete_flag = 'N'
1188 AND jrg.group_id = jgm.group_id
1189 AND jgm.creation_date > p_last_run_date
1190 );
1191 */
1192 CURSOR cur_get_crt_res(p_last_run_date DATE) IS
1193 SELECT aac.act_access_to_object_id
1194 , aac.arc_act_access_to_object
1195 , jgm.resource_id
1196 , jgm.last_update_date
1197 , aac.admin_flag
1198 FROM ams_act_access aac,
1199 jtf_rs_groups_denorm jrg,
1200 jtf_rs_group_members jgm
1201 WHERE
1202 ( aac.arc_user_or_role_type = 'GROUP'
1203 AND aac.user_or_role_id= jrg.parent_group_id
1204 AND aac.delete_flag='N'
1205 AND jgm.delete_flag = 'N'
1206 AND jrg.group_id = jgm.group_id
1207 AND jrg.start_date_active >= p_last_run_date
1208 AND jrg.start_date_active <= TRUNC(SYSDATE)
1209 )
1210 UNION
1211 SELECT aac.act_access_to_object_id
1212 , aac.arc_act_access_to_object
1213 , jgm.resource_id
1214 , jgm.last_update_date
1215 , aac.admin_flag
1216 FROM ams_act_access aac,
1217 jtf_rs_groups_denorm jrg,
1218 jtf_rs_group_members jgm
1219 WHERE
1220 ( aac.arc_user_or_role_type = 'GROUP'
1221 AND aac.user_or_role_id= jrg.parent_group_id
1222 AND aac.delete_flag='N'
1223 AND jgm.delete_flag = 'N'
1224 AND jrg.group_id = jgm.group_id
1225 AND jrg.last_update_date > p_last_run_date
1226 AND jrg.start_date_active <= TRUNC(SYSDATE)
1227 )
1228 UNION
1229 SELECT aac.act_access_to_object_id
1230 , aac.arc_act_access_to_object
1231 , jgm.resource_id
1232 , jgm.last_update_date
1233 , aac.admin_flag
1234 FROM ams_act_access aac,
1235 jtf_rs_groups_denorm jrg,
1236 jtf_rs_group_members jgm
1237 WHERE
1238 ( aac.arc_user_or_role_type = 'GROUP'
1239 AND aac.user_or_role_id= jrg.parent_group_id
1240 AND aac.delete_flag='N'
1241 AND jgm.delete_flag = 'N'
1242 AND jrg.group_id = jgm.group_id
1243 AND jgm.creation_date > p_last_run_date
1244 );
1245
1246 -- CURSOR to get group members that are deleted by end-dating child group relationship
1247 -- or those changed end-date in the child group relationship after previous run
1248 -- or got deleted manually.
1249 -- Cursor replaced by those suggested by Perf Team as per Bug 3071312
1250 /*
1251 CURSOR cur_get_del_res(p_last_run_date DATE) IS
1252 SELECT aac.act_access_to_object_id
1253 , aac.arc_act_access_to_object
1254 , jgm.resource_id
1255 , jgm.last_update_date
1256 , aac.admin_flag
1257 FROM ams_act_access aac,
1258 jtf_rs_groups_denorm jrg,
1259 jtf_rs_group_members jgm
1260 WHERE
1261 ( aac.arc_user_or_role_type = 'GROUP'
1262 AND aac.user_or_role_id= jrg.parent_group_id
1263 AND aac.delete_flag='N'
1264 AND jrg.group_id = jgm.group_id
1265 AND jrg.end_date_active IS NOT NULL
1266 AND jrg.end_date_active >= p_last_run_date
1267 AND jrg.end_date_active <= TRUNC(SYSDATE)
1268 )
1269 OR
1270 ( aac.arc_user_or_role_type = 'GROUP'
1271 AND aac.user_or_role_id= jrg.parent_group_id
1272 AND aac.delete_flag='N'
1273 AND jrg.group_id = jgm.group_id
1274 AND jrg.last_update_date > p_last_run_date
1275 AND jrg.end_date_active IS NOT NULL
1276 AND jrg.end_date_active <= TRUNC(SYSDATE)
1277 )
1278 OR
1279 ( aac.arc_user_or_role_type = 'GROUP'
1280 AND aac.user_or_role_id= jrg.parent_group_id
1281 AND aac.delete_flag='N'
1282 AND jgm.delete_flag = 'Y'
1283 AND jrg.group_id = jgm.group_id
1284 AND jgm.last_update_date >= p_last_run_date
1285 );
1286 */
1287 CURSOR cur_get_del_res(p_last_run_date DATE) IS
1288 SELECT aac.act_access_to_object_id
1289 , aac.arc_act_access_to_object
1290 , jgm.resource_id
1291 , jgm.last_update_date
1292 , aac.admin_flag
1293 FROM ams_act_access aac,
1294 jtf_rs_groups_denorm jrg,
1295 jtf_rs_group_members jgm
1296 WHERE
1297 ( aac.arc_user_or_role_type = 'GROUP'
1298 AND aac.user_or_role_id= jrg.parent_group_id
1299 AND aac.delete_flag='N'
1300 AND jrg.group_id = jgm.group_id
1301 AND jrg.end_date_active IS NOT NULL
1302 AND jrg.end_date_active >= p_last_run_date
1303 AND jrg.end_date_active <= TRUNC(SYSDATE)
1304 )
1305 UNION
1306 SELECT aac.act_access_to_object_id
1307 , aac.arc_act_access_to_object
1308 , jgm.resource_id
1309 , jgm.last_update_date
1310 , aac.admin_flag
1311 FROM ams_act_access aac,
1312 jtf_rs_groups_denorm jrg,
1313 jtf_rs_group_members jgm
1314 WHERE
1315 ( aac.arc_user_or_role_type = 'GROUP'
1316 AND aac.user_or_role_id= jrg.parent_group_id
1317 AND aac.delete_flag='N'
1318 AND jrg.group_id = jgm.group_id
1319 AND jrg.last_update_date > p_last_run_date
1320 AND jrg.end_date_active IS NOT NULL
1321 AND jrg.end_date_active <= TRUNC(SYSDATE)
1322 )
1323 UNION
1324 SELECT aac.act_access_to_object_id
1325 , aac.arc_act_access_to_object
1326 , jgm.resource_id
1330 jtf_rs_groups_denorm jrg,
1327 , jgm.last_update_date
1328 , aac.admin_flag
1329 FROM ams_act_access aac,
1331 jtf_rs_group_members jgm
1332 WHERE
1333 ( aac.arc_user_or_role_type = 'GROUP'
1334 AND aac.user_or_role_id= jrg.parent_group_id
1335 AND aac.delete_flag='N'
1336 AND jgm.delete_flag = 'Y'
1337 AND jrg.group_id = jgm.group_id
1338 AND jgm.last_update_date >= p_last_run_date
1339 );
1340
1341 CURSOR cur_get_conc_program_id IS
1342 SELECT concurrent_program_id
1343 FROM fnd_concurrent_programs
1344 WHERE concurrent_program_name = 'AMSJDENO';
1345
1346 CURSOR cur_get_latest_start_date IS
1347 SELECT MAX(actual_start_date)
1348 FROM fnd_concurrent_requests
1349 WHERE program_application_id = l_program_application_id
1350 AND concurrent_program_id = l_concurrent_program_id
1351 AND status_code = 'C'
1352 AND phase_code = 'C';
1353
1354 -- Used only once i.e the first time ever this concurrent program is run
1355 -- Use the minimum last_update_date
1356 CURSOR cur_get_latest_run_date IS
1357 SELECT MIN(last_update_date)
1358 FROM ams_act_access_denorm;
1359 l_last_run_date DATE;
1360
1361 BEGIN
1362
1363 OPEN cur_get_conc_program_id;
1364 FETCH cur_get_conc_program_id INTO l_concurrent_program_id;
1365 CLOSE cur_get_conc_program_id;
1366
1367 OPEN cur_get_latest_start_date;
1368 FETCH cur_get_latest_start_date INTO l_last_run_date;
1369 CLOSE cur_get_latest_start_date;
1370
1371 IF l_last_run_date IS NULL THEN
1372 OPEN cur_get_latest_run_date;
1373 FETCH cur_get_latest_run_date INTO l_last_run_date;
1374 CLOSE cur_get_latest_run_date;
1375 END IF;
1376
1377 -- handle all the groups which are directly associated to the objects, and are deleted.
1378 FOR l_del_grp_rec IN cur_get_del_grp(l_last_run_date) LOOP
1379 --dbms_output.put_line(' groups ');
1380 delete_group( p_group_id => l_del_grp_rec.group_id
1381 , p_object_type => l_del_grp_rec.arc_act_access_to_object
1382 , p_object_id => l_del_grp_rec.act_access_to_object_id
1383 , p_edit_metrics => l_del_grp_rec.admin_flag
1384 );
1385 END LOOP;
1386
1387 -- create all the resources which are added manually in the main group or child group
1388 -- and, also create all the resources came via new child group relations.
1389 FOR l_crt_res_rec IN cur_get_crt_res(l_last_run_date) LOOP
1390 --dbms_output.put_line(' groups ');
1391 insert_resource( p_resource_id => l_crt_res_rec.resource_id
1392 , p_object_type => l_crt_res_rec.arc_act_access_to_object
1393 , p_object_id => l_crt_res_rec.act_access_to_object_id
1394 , p_edit_metrics => l_crt_res_rec.admin_flag
1395 );
1396 END LOOP;
1397
1398 -- delete all the resources which are deleted manually in the main group or child group
1399 -- and, also deleted because child group relationship is end-dated.
1400 FOR l_del_res_rec IN cur_get_del_res(l_last_run_date) LOOP
1401 --dbms_output.put_line(' DELETE group members');
1402 delete_resource( p_resource_id => l_del_res_rec.resource_id
1403 , p_object_type => l_del_res_rec.arc_act_access_to_object
1404 , p_object_id => l_del_res_rec.act_access_to_object_id
1405 , p_edit_metrics => l_del_res_rec.admin_flag
1406 );
1407 END LOOP;
1408 retcode := 0;
1409 EXCEPTION
1410 WHEN OTHERS THEN
1411 ROLLBACK;
1412 RETCODE := 2;
1413 ERRBUF := SQLERRM;
1414 end jtf_access_denorm;
1415 end ams_access_denorm_pvt;
[Home] [Help]