eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AMS_ACCESS_DENORM_PVT dependencies on AMS_ACT_ACCESS

Line 206: INSERT INTO ams_act_access_denorm

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

Line 219: SELECT ams_act_access_denorm_s.nextval

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

Line 232: FROM ams_act_access_denorm

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: );

Line 240: UPDATE ams_act_access_denorm

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

Line 270: FROM ams_act_access_denorm

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:

Line 281: UPDATE ams_act_access_denorm

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

Line 301: UPDATE ams_act_access_denorm aacd

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

Line 311: FROM ams_act_access aac,

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

Line 343: DELETE FROM AMS_ACT_ACCESS_DENORM aacd

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

Line 348: FROM ams_act_access aac,

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

Line 363: FROM ams_act_access

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

Line 372: UPDATE ams_act_access_denorm aacd

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

Line 381: FROM ams_act_access aac,

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

Line 415: UPDATE ams_act_access_denorm aacd

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

Line 435: INSERT INTO ams_act_access_denorm

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

Line 448: SELECT ams_act_access_denorm_s.nextval

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

Line 469: FROM ams_act_access_denorm

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: );

Line 490: UPDATE ams_act_access_denorm aacd

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

Line 499: FROM ams_act_access aac,

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

Line 520: UPDATE ams_act_access_denorm aacd

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

Line 530: FROM ams_act_access aac,

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

Line 547: FROM ams_act_access aac,

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

Line 562: FROM ams_act_access aac,

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

Line 578: FROM ams_act_access aac

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'

Line 603: DELETE FROM ams_act_access_denorm aacd

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,

Line 616: FROM ams_act_access aac,

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

Line 631: FROM ams_act_access

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 ) ;

Line 644: UPDATE ams_act_access_denorm aacd

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

Line 652: FROM ams_act_access aac,

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

Line 668: FROM ams_act_access

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

Line 690: TYPE user_role_tbl IS TABLE OF ams_act_access.user_or_role_id%TYPE ;

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:

Line 691: TYPE object_id_tbl IS TABLE OF ams_act_access.act_access_to_object_id%TYPE ;

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 ;

Line 692: TYPE object_tbl IS TABLE OF ams_act_access.arc_act_access_to_object%TYPE ;

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 ;

Line 693: TYPE admin_flag_tbl IS TABLE OF ams_act_access.admin_flag%TYPE ;

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 ;

Line 706: FROM ams_act_access

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:

Line 726: DELETE FROM ams_act_access_denorm aacd

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,

Line 737: FROM ams_act_access aac,

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)

Line 752: FROM ams_act_access

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 ) ;

Line 761: UPDATE ams_act_access_denorm aacd

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

Line 769: FROM ams_act_access aac,

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)

Line 785: FROM ams_act_access

781: AND aac.admin_flag = 'Y'
782: AND aac.delete_flag = 'N'
783: UNION ALL
784: SELECT user_or_role_id
785: FROM ams_act_access
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

Line 813: TYPE user_role_tbl IS TABLE OF ams_act_access.user_or_role_id%TYPE ;

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:

Line 814: TYPE object_id_tbl IS TABLE OF ams_act_access.act_access_to_object_id%TYPE ;

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 ;

Line 815: TYPE object_tbl IS TABLE OF ams_act_access.arc_act_access_to_object%TYPE ;

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 ;

Line 816: TYPE admin_flag_tbl IS TABLE OF ams_act_access.admin_flag%TYPE ;

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 ;

Line 829: FROM ams_act_access

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:

Line 846: UPDATE ams_act_access_denorm aacd

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

Line 868: INSERT INTO ams_act_access_denorm

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

Line 881: SELECT ams_act_access_denorm_s.nextval

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)

Line 902: FROM ams_act_access_denorm

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: );

Line 911: UPDATE ams_act_access_denorm aacd

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

Line 921: FROM ams_act_access aac,

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)

Line 939: UPDATE ams_act_access_denorm aacd

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

Line 949: FROM ams_act_access aac,

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)

Line 964: FROM ams_act_access aac,

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)

Line 980: FROM ams_act_access aac

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'

Line 1004: TYPE user_role_tbl IS TABLE OF ams_act_access.user_or_role_id%TYPE ;

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:

Line 1005: TYPE object_id_tbl IS TABLE OF ams_act_access.act_access_to_object_id%TYPE ;

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 ;

Line 1006: TYPE object_tbl IS TABLE OF ams_act_access.arc_act_access_to_object%TYPE ;

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 ;

Line 1007: TYPE admin_flag_tbl IS TABLE OF ams_act_access.admin_flag%TYPE ;

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 ;

Line 1019: FROM ams_act_access

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' ;

Line 1036: UPDATE ams_act_access_denorm aacd

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

Line 1058: INSERT INTO ams_act_access_denorm

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

Line 1071: SELECT ams_act_access_denorm_s.nextval

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)

Line 1092: FROM ams_act_access_denorm

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: );

Line 1118: TYPE user_role_tbl IS TABLE OF ams_act_access.user_or_role_id%TYPE ;

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:

Line 1119: TYPE object_id_tbl IS TABLE OF ams_act_access.act_access_to_object_id%TYPE ;

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 ;

Line 1120: TYPE object_tbl IS TABLE OF ams_act_access.arc_act_access_to_object%TYPE ;

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 ;

Line 1121: TYPE admin_flag_tbl IS TABLE OF ams_act_access.admin_flag%TYPE ;

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 ;

Line 1133: FROM ams_act_access

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:

Line 1143: FROM ams_act_access act,

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'

Line 1176: DELETE FROM ams_act_access_denorm aacd

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,

Line 1189: FROM ams_act_access aac,

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)

Line 1204: FROM ams_act_access

1200: AND jgm.delete_flag = 'N'
1201: AND jgm.resource_id = aacd.resource_id
1202: UNION ALL
1203: SELECT 1
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)
1207: AND arc_user_or_role_type = 'USER'
1208: AND user_or_role_id = aacd.resource_id ) ;

Line 1212: UPDATE ams_act_access_denorm aacd

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

Line 1220: FROM ams_act_access aac,

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)

Line 1236: FROM ams_act_access

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

Line 1264: TYPE user_role_tbl IS TABLE OF ams_act_access.user_or_role_id%TYPE ;

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:

Line 1265: TYPE object_id_tbl IS TABLE OF ams_act_access.act_access_to_object_id%TYPE ;

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 ;

Line 1266: TYPE object_tbl IS TABLE OF ams_act_access.arc_act_access_to_object%TYPE ;

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 ;

Line 1267: TYPE admin_flag_tbl IS TABLE OF ams_act_access.admin_flag%TYPE ;

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 ;

Line 1279: FROM ams_act_access

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:

Line 1296: UPDATE ams_act_access_denorm aacd

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

Line 1306: FROM ams_act_access aac,

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)

Line 1323: UPDATE ams_act_access_denorm aacd

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

Line 1333: FROM ams_act_access aac,

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)

Line 1348: FROM ams_act_access aac,

1344: AND jgm.resource_id = aacd.resource_id
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)

Line 1364: FROM ams_act_access aac

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'

Line 1387: TYPE object_id_tbl IS TABLE OF ams_act_access.act_access_to_object_id%TYPE ;

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 ;

Line 1388: TYPE object_tbl IS TABLE OF ams_act_access.arc_act_access_to_object%TYPE ;

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 ;

Line 1389: TYPE admin_flag_tbl IS TABLE OF ams_act_access.admin_flag%TYPE ;

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 ;

Line 1401: FROM ams_act_access aac,

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'

Line 1418: FROM ams_act_access aac,

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'

Line 1435: FROM ams_act_access aac,

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'

Line 1458: INSERT INTO ams_act_access_denorm

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

Line 1471: SELECT ams_act_access_denorm_s.nextval

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)

Line 1484: FROM ams_act_access_denorm

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: );

Line 1492: UPDATE ams_act_access_denorm

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

Line 1518: TYPE object_id_tbl IS TABLE OF ams_act_access.act_access_to_object_id%TYPE ;

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 ;

Line 1519: TYPE object_tbl IS TABLE OF ams_act_access.arc_act_access_to_object%TYPE ;

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 ;

Line 1520: TYPE admin_flag_tbl IS TABLE OF ams_act_access.admin_flag%TYPE ;

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 ;

Line 1532: FROM ams_act_access aac,

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'

Line 1549: FROM ams_act_access aac,

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'

Line 1566: FROM ams_act_access aac,

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'

Line 1591: DELETE FROM AMS_ACT_ACCESS_DENORM aacd

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

Line 1596: FROM ams_act_access aac,

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)

Line 1611: FROM ams_act_access

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

Line 1620: UPDATE ams_act_access_denorm aacd

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

Line 1629: FROM ams_act_access aac,

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)

Line 1672: DELETE FROM ams_act_access_denorm aacd

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,

Line 1685: FROM ams_act_access aac,

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

Line 1700: FROM ams_act_access

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 ) ;

Line 1718: FROM ams_act_access

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';

Line 1730: DELETE FROM ams_act_access_denorm aacd

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

Line 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

Line 1748: SELECT ams_act_access_denorm_s.nextval

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

Line 1759: FROM ams_act_access

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:

Line 1774: DELETE FROM ams_act_access

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' ;

Line 1790: populates the resources in that group in the ams_act_access_denorm table.

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.

Line 1817: FROM ams_act_access

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.

Line 1830: FROM ams_act_access

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

Line 1853: FROM ams_act_access_denorm;

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;

Line 1889: DELETE ams_act_access

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.

Line 1919: FROM ams_act_access act,

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

Line 1937: FROM ams_act_access act,

1933: , jrg.creation_date
1934: , jrg.last_update_date
1935: , jrg.end_date_active
1936: , act.admin_flag
1937: FROM ams_act_access act,
1938: jtf_rs_grp_relations jrg
1939: WHERE act.arc_user_or_role_type = 'GROUP'
1940: AND act.user_or_role_id= jrg.group_id
1941: AND jrg.last_update_date >= p_last_run_date

Line 1953: FROM ams_act_access aac,

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

Line 1981: FROM ams_act_access_denorm;

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

Line 2087: FROM ams_act_access act,

2083: SELECT act.act_access_to_object_id
2084: , act.arc_act_access_to_object
2085: , jrg.group_id
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'

Line 2109: FROM ams_act_access aac,

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'

Line 2145: FROM ams_act_access aac,

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'

Line 2163: FROM ams_act_access aac,

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'

Line 2181: FROM ams_act_access aac,

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'

Line 2204: FROM ams_act_access aac,

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'

Line 2240: FROM ams_act_access aac,

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'

Line 2258: FROM ams_act_access aac,

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'

Line 2276: FROM ams_act_access aac,

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'

Line 2305: FROM ams_act_access_denorm;

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: