[Home] [Help]
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'
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'
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'
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)
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)
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'
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'
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'
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'
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)
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'
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'
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)
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'
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'
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)
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)
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'
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'
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'
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
1048: AND jgd.start_date_active <= TRUNC(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)
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)
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'
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'
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'
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'
1345: AND aac.delete_flag = 'N'
1346: )
1347: AND NOT EXISTS ( SELECT 1
1348: FROM ams_act_access aac,
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'
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
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
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
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
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
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
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'
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'
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)
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'
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
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
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
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
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
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
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
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
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