[Home] [Help]
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
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
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: );
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
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:
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
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
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
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
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
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
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
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
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
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: );
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
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
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
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
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
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'
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,
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
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 ) ;
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
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
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
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,
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
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 ) ;
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';
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
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
777: , last_update_date
778: , last_updated_by
779: , last_update_login
780: )
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
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:
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' ;
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.
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.
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
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;
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.
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
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
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
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:
1036: l_last_run_date date;
1037:
1038: BEGIN
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'
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
1166: ( aac.arc_user_or_role_type = 'GROUP'
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'
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'
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'
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'
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'
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'
1325: , aac.arc_act_access_to_object
1326: , jgm.resource_id
1327: , jgm.last_update_date
1328: , aac.admin_flag
1329: FROM ams_act_access aac,
1330: jtf_rs_groups_denorm jrg,
1331: jtf_rs_group_members jgm
1332: WHERE
1333: ( aac.arc_user_or_role_type = 'GROUP'
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: