[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
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
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
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
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
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,
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
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,
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
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
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
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)
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
911: UPDATE ams_act_access_denorm aacd
912: SET edit_metrics_yn = 'Y'
913: , last_updated_by = l_user_id
914: , last_update_date = l_sysdate
915: , last_update_login = l_login_id
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
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
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
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)
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: );
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,
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
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
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
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
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)
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: );
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
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
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
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,
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
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
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
1786:
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.
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;
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
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: