APPS.AMW_VIOLATION_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 75

l_has_icm_sql varchar2(64) := 'select null from AMW_CONSTRAINTS_B where rownum = 1';

Line: 252

      SELECT constraint_rev_id, type_code
      FROM amw_constraints_b
      WHERE start_date <= sysdate AND (end_date IS NULL OR end_date >= sysdate)
      AND objective_code = 'PR';

Line: 266

      SELECT count(*)
      FROM amw_constraint_entries
      WHERE constraint_rev_id = l_constraint_rev_id;

Line: 307

    SELECT count(*)
      FROM amw_constraint_waivers_vl
     WHERE constraint_rev_id = l_constraint_rev_id
       AND object_type = 'USER'
       AND PK1 = l_user_id
       AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);

Line: 316

	SELECT resp.responsibility_name
	  FROM fnd_responsibility_vl resp
	WHERE resp.responsibility_id in (
		SELECT function_id
		FROM amw_constraint_entries cons
		WHERE constraint_rev_id = l_constraint_rev_id
		AND cons.application_id = resp.application_id);

Line: 360

          ' select  distinct ROLE_NAME  from wf_user_role_assignments_v a '||
          ' where a.assigning_role IN ( ' || l_revoked_role_names || ' ) '||
          ' AND a.role_name = a.assigning_role  ) ';

Line: 366

          ' select distinct ROLE_NAME  from wf_user_role_assignments_v b  '||
          ' where user_name = (select user_name from fnd_user where user_id = ' || p_user_id  || ' ) '||
          ' and b.assigning_role IN ( ' || l_revoked_role_names || ' ) '||
          ' and b.role_name <> b.assigning_role  '||
          ' and b.start_date <= sysdate and (b.end_date is null or b.end_date > sysdate)  '||
          ' and b.role_name not in  '||
          ' (SELECT c.ROLE_NAME FROM wf_user_role_assignments_v c WHERE  '||
          ' c.start_date <= sysdate and (c.end_date is null or c.end_date > sysdate)  '||
          ' and c.ASSIGNING_ROLE IN (  '||
          ' select ROLE_NAME from wf_user_role_assignments_v d  '||
          ' where d.role_name = d.assigning_role  '||
          ' and d.role_name not in (' || l_revoked_role_names || ' ) '||
          ' and d.start_date <= sysdate and (d.end_date is null or d.end_date > sysdate))))';

Line: 384

    '  select rcd.function_id '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'      ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
  ||'  where rcd.constraint_rev_id = :1 '
  ||'    and u.user_id = :2 '
  ||'    and u.user_name = ur.user_name '
  ||'    and uar.user_name = ur.user_name '
  ||'    and uar.role_name = ur.role_name '
  ||'    and uar.start_date <= sysdate '
  ||'    and (uar.end_date is null or uar.end_date >= sysdate) '
  ||'    and ( (ur.role_name = rcd.role_name '
  ||'    and ur.role_orig_system = ''UMX'' ) '
  ||'    or ( ur.role_orig_system_id = rcd.responsibility_id '
  ||'    and ur.role_orig_system = ''FND_RESP'' ) ) '
  ||     l_sub_revoked_role_names
  ||'  UNION ALL '
  ||'  select rcd.function_id '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_GRANTS||' gra '
  ||'  where rcd.constraint_rev_id = :3 '
  ||'    and gra.instance_type = ''GLOBAL'' and gra.menu_id = rcd.menu_id '
  ||'    and gra.object_id = -1 and ( gra.grantee_type = ''GLOBAL'' '
  ||'    or ( gra.grantee_type = ''USER'' and gra.grantee_key = (select u.user_name from '
  ||    G_AMW_USER || ' u where u.user_id = :4 ))) ';

Line: 413

    '  select rcd.group_code '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'      ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
  ||'  where rcd.constraint_rev_id = :1 '
  ||'    and u.user_id = :2 '
  ||'    and u.user_name = ur.user_name '
  ||'    and uar.user_name = ur.user_name '
  ||'    and uar.role_name = ur.role_name '
  ||'    and uar.start_date <= sysdate '
  ||'    and (uar.end_date is null or uar.end_date >= sysdate) '
  ||'    and ( (ur.role_name = rcd.role_name '
  ||'    and ur.role_orig_system = ''UMX'' ) '
  ||'    or ( ur.role_orig_system_id = rcd.responsibility_id '
  ||'    and ur.role_orig_system = ''FND_RESP'' ) ) '
  ||     l_sub_revoked_role_names
  ||'  UNION ALL '
  ||'  select rcd.group_code '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_GRANTS||' gra '
  ||'  where rcd.constraint_rev_id = :3 '
  ||'    and gra.instance_type = ''GLOBAL'' and gra.menu_id = rcd.menu_id '
  ||'    and gra.object_id = -1 and ( gra.grantee_type = ''GLOBAL'' '
  ||'    or ( gra.grantee_type = ''USER'' and gra.grantee_key = (select u.user_name from '
  ||    G_AMW_USER || ' u where u.user_id = :4 ))) ';

Line: 441

  '  select ur.role_orig_system_id '
  ||'  from '
  || G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'      ,amw_constraint_entries cst '
  ||'      ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
  ||'  where  u.user_id = :1 '
  ||'    and  cst.constraint_rev_id = :2 '
  ||'    and u.user_name = ur.user_name '
  ||'    and ur.role_orig_system_id = cst.function_id '
  ||'    and ur.role_orig_system = ''FND_RESP'' '
  ||'    and uar.user_name = ur.user_name '
  ||'    and uar.role_name = ur.role_name '
  ||'    and uar.start_date <= sysdate '
  ||'    and (uar.end_date is null or uar.end_date >= sysdate) '
  ||     l_sub_revoked_role_names ;

Line: 459

  '  select cst.group_code '
  ||'  from '
  || G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'      ,amw_constraint_entries cst '
  ||'      ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
  ||'  where  u.user_id = :1 '
  ||'    and  cst.constraint_rev_id = :2 '
  ||'    and u.user_name = ur.user_name '
  ||'    and ur.role_orig_system_id = cst.function_id '
  ||'    and ur.role_orig_system = ''FND_RESP'' '
  ||'    and uar.user_name = ur.user_name '
  ||'    and uar.role_name = ur.role_name '
  ||'    and uar.start_date <= sysdate '
  ||'    and (uar.end_date is null or uar.end_date >= sysdate) '
  ||     l_sub_revoked_role_names;

Line: 487

         ' SELECT distinct SUPER_NAME '||
         ' FROM              WF_ROLE_HIERARCHIES '||
         ' WHERE ENABLED_FLAG = ''Y''  '||
         ' CONNECT BY PRIOR  SUPER_NAME = SUB_NAME  '||
         ' AND  PRIOR        ENABLED_FLAG = ''Y''  '||
         ' START WITH        SUB_NAME in (  '|| l_role_names || ' ) ' ||
         ' union all '||
         ' SELECT NAME '||
         ' FROM WF_ROLES '||
         ' WHERE NAME IN (  '|| l_role_names || ' ) ';

Line: 513

          'select count(distinct function_id) from ( '
        ||   l_func_existing_sql
        ||'  UNION ALL '
        ||'  select rcd.function_id '
        ||'  from amw_role_constraint_denorm rcd '
        ||'  where rcd.constraint_rev_id = :5 '
        ||'    and rcd.role_name in ( '||l_sub_role_names||' ) '
        ||') ';

Line: 523

          'select count(distinct group_code) from ( '
        ||   l_func_set_existing_sql
        ||'  UNION ALL '
        ||'  select rcd.group_code '
        ||'  from amw_role_constraint_denorm rcd '
        ||'  where rcd.constraint_rev_id = :5 '
        ||'    and rcd.role_name in ( '||l_sub_role_names||' ) '
        ||') ';

Line: 533

         'select count(distinct role_orig_system_id) from ( '
        ||   l_resp_existing_sql
        ||'  UNION ALL '
        ||'  select distinct rle.orig_system_id '
        ||'  from  ' || G_AMW_ALL_ROLES_VL || ' rle '
        ||'  , amw_constraint_entries cst '
        ||'  where rle.orig_system = ''FND_RESP'' '
        ||'  and cst.constraint_rev_id = :3 '
        ||'  and cst.function_id = rle.orig_system_id '
        ||'  and rle.name in ( '||l_sub_role_names||' ) '
        ||') ';

Line: 546

          'select count(distinct group_code) from ( '
        ||   l_resp_set_existing_sql
        ||'  UNION ALL '
        ||'  select cst.group_code '
        ||'  from  ' || G_AMW_ALL_ROLES_VL || ' rle '
        ||'  , amw_constraint_entries cst '
        ||'  where rle.orig_system = ''FND_RESP'' '
        ||'  and cst.constraint_rev_id = :3 '
        ||'  and cst.function_id = rle.orig_system_id '
        ||'  and rle.name in ( '||l_sub_role_names||' ) '
        ||') ';

Line: 559

        'select resp.responsibility_name from fnd_responsibility_vl resp'
        ||' where resp.responsibility_id in ( '
	||' select function_id from amw_constraint_entries cons, wf_roles rl '
	||' where constraint_rev_id = :1 '
        ||' and cons.application_id = resp.application_id '
	||' and cons.function_id = rl.orig_system_id '
        ||' and rl.orig_system = ''FND_RESP'' '
        ||' and rl.name in (' ||l_sub_role_names||' ) '
        ||' and rl.owner_tag = (select application_short_name '
	||' from fnd_application app where app.application_id = resp.application_id)) ';

Line: 571

        'select resp.responsibility_name from fnd_responsibility_vl resp'
        ||' where resp.responsibility_id in ( '
	||' select function_id from amw_constraint_entries cons, wf_roles rl '
	||' where constraint_rev_id = :1 '
        ||' and cons.application_id = resp.application_id '
	||' and cons.function_id = rl.orig_system_id '
        ||' and rl.orig_system = ''FND_RESP'' '
        ||' and rl.name not in (' ||l_sub_role_names||' ) '
        ||' and rl.owner_tag = (select application_short_name '
	||' from fnd_application app where app.application_id = resp.application_id)) ';

Line: 596

        l_vio_exist_role_sql :='select count(distinct function_id)'
            ||'from ('
            ||' select function_id from amw_constraint_entries where constraint_rev_id = :1'
            ||' MINUS '
            ||'select distinct function_id from ( '
            ||   l_func_existing_sql
            ||') '
            ||')';

Line: 609

                '  select count(rcd.function_id) '
                ||' from amw_role_constraint_denorm rcd '
                ||'  where rcd.constraint_rev_id = :1 '
                ||'    and rcd.role_name in ( '||l_sub_role_names||' ) ' ;

Line: 642

            l_func_sql :='select count(distinct function_id)'
                    ||'from ('
                    ||'select distinct function_id from ( '
                    ||   l_func_existing_sql
                    ||'  UNION ALL '
                    ||'  select rcd.function_id '
                    ||'  from amw_role_constraint_denorm rcd '
                    ||'  where rcd.constraint_rev_id = :5 '
                    ||'  and rcd.role_name in ( '||l_sub_role_names||' ) '
                    ||') '
                    ||' MINUS '
                    ||' select FUNCTION_ID from amw_constraint_entries where constraint_rev_id = :6'
                    ||')';

Line: 683

        l_vio_exist_role_sql :='select count(distinct function_id) from ( '
            ||   l_func_existing_sql
            ||')';

Line: 710

                '  select count(rcd.function_id) '
                ||' from amw_role_constraint_denorm rcd '
                ||'  where rcd.constraint_rev_id = :1 '
                ||'    and rcd.role_name in ( '||l_sub_role_names||' ) ' ;

Line: 763

        l_vio_exist_role_sql :='select count(distinct group_code) from ( '
            ||  l_func_set_existing_sql
            ||  ') ';

Line: 788

                '  select count( distinct rcd.group_code) '
                ||' from amw_role_constraint_denorm rcd '
                ||'  where rcd.constraint_rev_id = :1 '
                ||'    and rcd.role_name in ( '||l_sub_role_names||' ) ' ;

Line: 841

        l_vio_exist_resp_sql :='select count(distinct function_id)'
            ||'from ('
            ||' select function_id from amw_constraint_entries where constraint_rev_id = :1'
            ||' MINUS '
            ||'select distinct role_orig_system_id from ( '
            ||   l_resp_existing_sql
            ||') '
            ||')';

Line: 877

            l_resp_sql := 'select count(distinct role_orig_system_id)'
             ||' from ('
             ||   l_resp_existing_sql
             ||'  UNION ALL '
             ||'  select distinct rle.orig_system_id  as role_orig_system_id '
             ||'  from  ' || G_AMW_ALL_ROLES_VL || ' rle '
             ||'  , amw_constraint_entries cst '
             ||'  where rle.orig_system = ''FND_RESP'' '
             ||'  and cst.constraint_rev_id = :3 '
             ||'  and cst.function_id = rle.orig_system_id '
             ||'  and rle.name in ( '||l_sub_role_names||' ) '
             ||' MINUS '
             ||' select FUNCTION_ID from amw_constraint_entries where constraint_rev_id = :4'
             ||')';

Line: 944

        l_vio_exist_resp_sql :='select count(distinct role_orig_system_id)'
            ||'from ('
            ||   l_resp_existing_sql
            ||')';

Line: 965

             '  select distinct rle.display_name '
             ||'  from  ' || G_AMW_ALL_ROLES_VL || ' rle '
             ||'  , amw_constraint_entries cst '
             ||'  where rle.orig_system = ''FND_RESP'' '
             ||'  and cst.constraint_rev_id = :1 '
             ||'  and cst.function_id = rle.orig_system_id '
             ||'  and rle.name in ( '||l_sub_role_names||' ) '
             ||'  and rle.owner_tag = (select application_short_name '
             ||'  from fnd_application app where app.application_id = cst.application_id)';

Line: 991

               '  select distinct rle.display_name '
               ||'  from '
               || G_AMW_USER_ROLES||' ur '
               ||'      ,'||G_AMW_user||' u '
               ||'      ,amw_constraint_entries cst '
               ||'      ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
               ||'      ,'||G_AMW_ALL_ROLES_VL || ' rle '
               ||'  where  u.user_id = :1 '
               ||'    and  cst.constraint_rev_id = :2 '
               ||'    and u.user_name = ur.user_name '
               ||'    and ur.role_orig_system_id = cst.function_id '
               ||'    and ur.role_orig_system = ''FND_RESP'' '
               ||'    and ur.role_orig_system = rle.orig_system '
               ||'    and ur.role_orig_system_id = rle.orig_system_id '
               ||'    and ur.role_name = rle.name '
               ||'    and rle.owner_tag = (select application_short_name '
               ||'    from fnd_application app where app.application_id = cst.application_id) '
               ||'    and uar.user_name = ur.user_name '
               ||'    and uar.role_name = ur.role_name '
               ||'    and uar.start_date <= sysdate '
               ||'    and (uar.end_date is null or uar.end_date >= sysdate) '
               ||     l_sub_revoked_role_names ;

Line: 1035

            l_resp_sql := 'select count(distinct role_orig_system_id)'
             ||' from ('
             ||   l_resp_existing_sql
             ||'  UNION ALL '
             ||'  select distinct rle.orig_system_id  as role_orig_system_id '
             ||'  from  ' || G_AMW_ALL_ROLES_VL || ' rle '
             ||'  , amw_constraint_entries cst '
             ||'  where rle.orig_system = ''FND_RESP'' '
             ||'  and cst.constraint_rev_id = :3 '
             ||'  and cst.function_id = rle.orig_system_id '
             ||'  and rle.name in ( '||l_sub_role_names||' ) '
             ||'  and rle.owner_tag = (select application_short_name '
             ||'  from fnd_application app where app.application_id = cst.application_id)'
             ||' MINUS '
             ||' select FUNCTION_ID from amw_constraint_entries where constraint_rev_id = :4'
             ||')';

Line: 1099

        l_vio_exist_resp_sql :='select count(distinct role_orig_system_id)'
            ||'from ('
            ||'select distinct role_orig_system_id from ( '
            ||   l_resp_existing_sql
            ||') '
            ||')';

Line: 1120

             '  select distinct rle.display_name '
             ||'  from  ' || G_AMW_ALL_ROLES_VL || ' rle '
             ||'  , amw_constraint_entries cst '
             ||'  where rle.orig_system = ''FND_RESP'' '
             ||'  and cst.constraint_rev_id = :1 '
             ||'  and cst.function_id = rle.orig_system_id '
             ||'  and rle.name in ( '||l_sub_role_names||' ) '
             ||'  and rle.owner_tag = (select application_short_name '
             ||'  from fnd_application app where app.application_id = cst.application_id)';

Line: 1146

               '  select distinct rle.display_name '
               ||'  from '
               || G_AMW_USER_ROLES||' ur '
               ||'      ,'||G_AMW_user||' u '
               ||'      ,amw_constraint_entries cst '
               ||'      ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
               ||'      ,'||G_AMW_ALL_ROLES_VL || ' rle '
               ||'  where  u.user_id = :1 '
               ||'    and  cst.constraint_rev_id = :2 '
               ||'    and u.user_name = ur.user_name '
               ||'    and ur.role_orig_system_id = cst.function_id '
               ||'    and ur.role_orig_system = ''FND_RESP'' '
               ||'    and ur.role_orig_system = rle.orig_system '
               ||'    and ur.role_orig_system_id = rle.orig_system_id '
               ||'    and ur.role_name = rle.name '
               ||'    and rle.owner_tag = (select application_short_name '
               ||'    from fnd_application app where app.application_id = cst.application_id) '
               ||'    and uar.user_name = ur.user_name '
               ||'    and uar.role_name = ur.role_name '
               ||'    and uar.start_date <= sysdate '
               ||'    and (uar.end_date is null or uar.end_date >= sysdate) '
               ||     l_sub_revoked_role_names ;

Line: 1283

      SELECT constraint_rev_id, type_code, objective_code
        FROM amw_constraints_b
       WHERE start_date <= sysdate AND (end_date IS NULL OR end_date >= sysdate);

Line: 1293

      SELECT count(*)
        FROM amw_constraint_entries
	   WHERE constraint_rev_id=l_constraint_rev_id;

Line: 1302

    'select count(distinct function_id) from ( '
  ||'  select rcd.function_id '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'  where rcd.constraint_rev_id = :1 '
  ||'    and u.user_id = :2 '
  ||'    and u.user_name = ur.user_name '
  ||'    and ur.role_name = rcd.role_name '
  ||'    and ur.role_orig_system = ''UMX'' '
  ||'  UNION ALL '
  ||'  select rcd.function_id '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'  where rcd.constraint_rev_id = :3 '
  ||'    and u.user_id = :4 '
  ||'    and u.user_name = ur.user_name '
  ||'    and ur.role_orig_system_id = rcd.responsibility_id '
  ||'    and ur.role_orig_system = ''FND_RESP'' '
  ||'  UNION ALL '
  ||'  select rcd.function_id '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_GRANTS||' gra '
  ||'      ,'||G_AMW_USER||' u '
  ||'  where rcd.constraint_rev_id = :5 '
  ||'    and u.user_id = :6 '
  ||'    and u.user_name = gra.grantee_key '
  ||'    and gra.grantee_type = ''USER'' '
  ||'    and gra.menu_id = rcd.menu_id '
  ||'    and gra.instance_type = ''GLOBAL'' '
  ||'    and gra.object_id = -1 '
  ||'  UNION ALL '
  ||'  select rcd.function_id '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_GRANTS||' gra '
  ||'  where rcd.constraint_rev_id = :7 '
  ||'    and gra.grantee_key = ''GLOBAL'' '
  ||'    and gra.grantee_type = ''GLOBAL'' '
  ||'    and gra.menu_id = rcd.menu_id '
  ||'    and gra.instance_type = ''GLOBAL'' '
  ||'    and gra.object_id = -1 '
  ||'  UNION ALL '
  ||'  select rcd.function_id '
  ||'  from amw_role_constraint_denorm rcd '
  ||'  where rcd.constraint_rev_id = :8 '
  ||'    and rcd.responsibility_id = :9 '
  ||') ';

Line: 1352

    'select count(distinct group_code) from ( '
  ||'  select rcd.group_code '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'  where rcd.constraint_rev_id = :1 '
  ||'    and u.user_id = :2 '
  ||'    and u.user_name = ur.user_name '
  ||'    and ur.role_name = rcd.role_name '
  ||'    and ur.role_orig_system = ''UMX'' '
  ||'  UNION ALL '
  ||'  select rcd.group_code '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'  where rcd.constraint_rev_id = :3 '
  ||'    and u.user_id = :4 '
  ||'    and u.user_name = ur.user_name '
  ||'    and ur.role_orig_system_id = rcd.responsibility_id '
  ||'    and ur.role_orig_system = ''FND_RESP'' '
  ||'  UNION ALL '
  ||'  select rcd.group_code '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_GRANTS||' gra '
  ||'      ,'||G_AMW_USER||' u '
  ||'  where rcd.constraint_rev_id = :5 '
  ||'    and u.user_id = :6 '
  ||'    and u.user_name = gra.grantee_key '
  ||'    and gra.grantee_type = ''USER'' '
  ||'    and gra.menu_id = rcd.menu_id '
  ||'    and gra.instance_type = ''GLOBAL'' '
  ||'    and gra.object_id = -1 '
  ||'  UNION ALL '
  ||'  select rcd.group_code '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_GRANTS||' gra '
  ||'  where rcd.constraint_rev_id = :7 '
  ||'    and gra.grantee_key = ''GLOBAL'' '
  ||'    and gra.grantee_type = ''GLOBAL'' '
  ||'    and gra.menu_id = rcd.menu_id '
  ||'    and gra.instance_type = ''GLOBAL'' '
  ||'    and gra.object_id = -1 '
  ||'  UNION ALL '
  ||'  select rcd.group_code '
  ||'  from amw_role_constraint_denorm rcd '
  ||'  where rcd.constraint_rev_id = :8 '
  ||'    and rcd.responsibility_id = :9 '
  ||') ';

Line: 1404

    SELECT count(*)
      FROM amw_constraint_waivers
     WHERE constraint_rev_id = l_constraint_rev_id
       AND object_type = 'USER'
       AND PK1 = l_user_id
       AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);

Line: 1548

    G_ROLE_NAME_LIST.DELETE();

Line: 1549

    G_RESPONSIBILITY_ID_LIST.DELETE();

Line: 1550

    G_MENU_ID_LIST.DELETE();

Line: 1551

    G_FUNCTION_ID_LIST.DELETE();

Line: 1552

    G_ENTRY_OBJECT_TYPE_LIST.DELETE();

Line: 1553

    G_GROUP_CODE_LIST.DELETE();

Line: 1556

    G_ROLE_NAME_LIST_HIER.DELETE();

Line: 1557

    G_RESPONSIBILITY_ID_LIST_HIER.DELETE();

Line: 1558

    G_MENU_ID_LIST_HIER.DELETE();

Line: 1559

    G_FUNCTION_ID_LIST_HIER.DELETE();

Line: 1560

    G_ENTRY_OBJECT_TYPE_LIST_HIER.DELETE();

Line: 1561

    G_GROUP_CODE_LIST_HIER.DELETE();

Line: 1665

        'SELECT RULE_TYPE, ACTION_ID '
      ||'  FROM '||G_AMW_RESP_FUNCTIONS
      ||' WHERE application_id = :1 '
      ||'   AND responsibility_id = :2 ';

Line: 1679

        'SELECT MENU_ID, ENTRY_SEQUENCE, FUNCTION_ID, SUB_MENU_ID, GRANT_FLAG '
      ||'  FROM '||G_AMW_MENU_ENTRIES
      ||' WHERE menu_id  = :1 ';

Line: 1695

    /* Select the list of exclusion rules into our cache */
    OPEN excl_c FOR l_excl_dynamic_sql USING
        p_appl_id,
        p_resp_id;

Line: 1923

Procedure Update_Role_Constraint_Denorm (
    errbuf                       OUT  NOCOPY VARCHAR2,
    retcode                      OUT  NOCOPY VARCHAR2,
    p_constraint_rev_id     IN  NUMBER := NULL
)
IS
L_API_NAME                  CONSTANT VARCHAR2(30) := 'Update_Role_Constraint_Denorm';

Line: 1951

        '(SELECT gra.GRANTEE_KEY ROLE_NAME, gra.GRANTEE_ORIG_SYSTEM_ID as responsibility_id, gra.menu_id, ce.function_id, ce.object_type, ce.group_code '
      ||'  FROM AMW_CONSTRAINT_ENTRIES ce '
      ||'      ,'||G_AMW_GRANTS ||' gra '
      ||'      ,'||G_AMW_COMPILED_MENU_FUNCTIONS ||' cmf '
      ||' WHERE gra.menu_id = cmf.menu_id '
      ||'   AND cmf.function_id = ce.function_id '
      --and cmf.grant_flag = ''Y'' '
      ||'   AND ce.CONSTRAINT_REV_ID = :1 '
      ||'   AND gra.INSTANCE_TYPE = ''GLOBAL'' '
      ||'   AND gra.OBJECT_ID = -1 '
      ||'   AND gra.GRANTEE_TYPE = ''GROUP'' '
      ||'   AND gra.start_date <= sysdate AND (gra.end_date >= sysdate or gra.end_date is null) '
      ||' UNION '
      ||' SELECT gra.GRANTEE_KEY ROLE_NAME, gra.GRANTEE_ORIG_SYSTEM_ID as responsibility_id, gra.menu_id, ce.function_id, ce.object_type, ce.group_code '
      ||'  FROM AMW_CONSTRAINT_ENTRIES ce '
      ||'      ,'||G_AMW_GRANTS ||' gra '
      ||'      ,'||G_AMW_COMPILED_MENU_FUNCTIONS ||' cmf '
      ||' WHERE gra.menu_id = cmf.menu_id '
      ||'   AND cmf.function_id = ce.function_id '
      --and cmf.grant_flag = ''Y'' '
      ||'   AND ce.CONSTRAINT_REV_ID = :2 '
      ||'   AND gra.INSTANCE_TYPE = ''GLOBAL'' '
      ||'   AND gra.OBJECT_ID = -1 '
      ||'   AND gra.GRANTEE_TYPE = ''GLOBAL'' '
      ||'   AND gra.start_date <= sysdate AND (gra.end_date >= sysdate or gra.end_date is null) '
      ||') UNION ALL '
      ||' SELECT to_char(null) role_name, resp.responsibility_id, resp.request_group_id menu_id, ce.function_id, ce.object_type, ce.group_code '
      ||' FROM  '||G_AMW_RESPONSIBILITY ||' resp '
      ||'      ,'||G_AMW_REQUEST_GROUP_UNITS ||' rgu '
      ||'      ,AMW_CONSTRAINT_ENTRIES ce '
      ||' WHERE resp.request_group_id = rgu.request_group_id '
      ||'   AND rgu.request_unit_type = ''P'' '
      ||'   AND rgu.request_unit_id = ce.function_id AND ce.object_type = ''CP'' '
      ||'   AND ce.CONSTRAINT_REV_ID = :3 '
      ||'   AND resp.responsibility_id NOT IN (select cw.pk1 from amw_constraint_waivers_vl cw '
      ||'                 where cw.constraint_rev_id=ce.CONSTRAINT_REV_ID '
      ||'                 and cw.object_type=''RESP'' '
      ||'                 and cw.start_date<=sysdate '
      ||'                 and (cw.end_date >= sysdate or cw.end_date is null)) '
      ||'   AND resp.start_date <= sysdate AND (resp.end_date >= sysdate or resp.end_date is null) '
      ;

Line: 2004

        ' SELECT war.name, resp.application_id, resp.responsibility_id, resp.menu_id, ce.function_id, ce.object_type, ce.group_code '
      ||'  FROM AMW_CONSTRAINT_ENTRIES ce '
      ||'      ,'||G_AMW_RESPONSIBILITY_VL ||' resp '
      ||'      ,'||G_AMW_COMPILED_MENU_FUNCTIONS ||' cmf '
      ||'      ,'||G_AMW_ALL_ROLES_VL ||' war '
      ||' WHERE resp.menu_id = cmf.menu_id '
      ||'   AND cmf.function_id = ce.function_id and cmf.grant_flag = ''Y'' '
      ||'   AND (ce.OBJECT_TYPE is null OR ce.OBJECT_TYPE = ''FUNC'') '
      ||'   AND ce.CONSTRAINT_REV_ID = :1 '
      ||'   AND resp.responsibility_id NOT IN (select cw.pk1 from amw_constraint_waivers_vl cw '
      ||'                 where cw.constraint_rev_id=ce.CONSTRAINT_REV_ID '
      ||'                 and cw.object_type=''RESP'' '
      ||'                 and cw.start_date<=sysdate '
      ||'                 and (cw.end_date >= sysdate or cw.end_date is null)) '
      ||'   AND resp.start_date <= sysdate AND (resp.end_date >= sysdate or resp.end_date is null) '
      ||'   AND resp.responsibility_name = war.display_name '
      ||'   AND war.ORIG_SYSTEM = ''FND_RESP'' '
      ||'   AND STATUS = ''ACTIVE'' ';

Line: 2025

      SELECT constraint_rev_id
        FROM amw_constraints_b
       WHERE start_date <= sysdate AND (end_date IS NULL OR end_date >= sysdate);

Line: 2038

      'SELECT count(*) '
    ||'  FROM '||G_AMW_RESP_FUNCTIONS
    ||'  WHERE application_id = :1 '
    ||'  AND responsibility_id = :2 '
    ||'  AND rule_type = :3 '
    ||'  AND action_id = :4 ';

Line: 2048

        DELETE FROM AMW_ROLE_CONSTRAINT_DENORM;

Line: 2081

                INSERT INTO AMW_ROLE_CONSTRAINT_DENORM
                VALUES(sysdate     -- last_update_date
                      ,G_USER_ID   -- last_updated_by
                      ,G_LOGIN_ID  -- last_update_login
                      ,sysdate     -- creation_date
                      ,G_USER_ID   -- created_by
                      ,G_FUNCTION_ID_LIST_HIER(i)    -- function_id
                      ,G_MENU_ID_LIST_HIER(i)        -- menu_id
                      ,l_all_valid_constraints.constraint_rev_id    -- constraint_rev_id
                      ,G_ENTRY_OBJECT_TYPE_LIST_HIER(i)                  -- object_type
                      ,G_GROUP_CODE_LIST_HIER(i)     -- group_code
                      ,G_ROLE_NAME_LIST_HIER(i)      -- role_name
                      ,G_RESPONSIBILITY_ID_LIST_HIER(i));                -- responsibility_id

Line: 2099

            l_applcation_id_list.delete();

Line: 2100

            l_responsibility_id_list.delete();

Line: 2101

            l_role_name_id_list.delete();

Line: 2102

            l_menu_id_list.delete();

Line: 2103

            l_function_id_list.delete();

Line: 2104

            l_entry_object_type_list.delete();

Line: 2105

            l_group_code_list.delete();

Line: 2158

                INSERT INTO AMW_ROLE_CONSTRAINT_DENORM
                VALUES(sysdate     -- last_update_date
                      ,G_USER_ID   -- last_updated_by
                      ,G_LOGIN_ID  -- last_update_login
                      ,sysdate     -- creation_date
                      ,G_USER_ID   -- created_by
                      ,G_FUNCTION_ID_LIST_HIER(i)    -- function_id
                      ,G_MENU_ID_LIST_HIER(i)        -- menu_id
                      ,l_all_valid_constraints.constraint_rev_id    -- constraint_rev_id
                      ,G_ENTRY_OBJECT_TYPE_LIST_HIER(i)                  -- object_type
                      ,G_GROUP_CODE_LIST_HIER(i)     -- group_code
                      ,G_ROLE_NAME_LIST_HIER(i)      -- role_name
                      ,G_RESPONSIBILITY_ID_LIST_HIER(i));                -- responsibility_id

Line: 2180

        DELETE FROM AMW_ROLE_CONSTRAINT_DENORM
        WHERE constraint_rev_id = p_constraint_rev_id;

Line: 2208

            INSERT INTO AMW_ROLE_CONSTRAINT_DENORM
            VALUES(sysdate     -- last_update_date
                  ,G_USER_ID   -- last_updated_by
                  ,G_LOGIN_ID  -- last_update_login
                  ,sysdate     -- creation_date
                  ,G_USER_ID   -- created_by
                  ,G_FUNCTION_ID_LIST_HIER(i)    -- function_id
                  ,G_MENU_ID_LIST_HIER(i)        -- menu_id
                  ,p_constraint_rev_id      -- constraint_rev_id
                  ,G_ENTRY_OBJECT_TYPE_LIST_HIER(i)                  -- object_type
                  ,G_GROUP_CODE_LIST_HIER(i)     -- group_code
                  ,G_ROLE_NAME_LIST_HIER(i)      -- role_name
                  ,G_RESPONSIBILITY_ID_LIST_HIER(i));                -- responsibility_id

Line: 2225

        l_applcation_id_list.delete();

Line: 2226

        l_responsibility_id_list.delete();

Line: 2227

        l_role_name_id_list.delete();

Line: 2228

        l_menu_id_list.delete();

Line: 2229

        l_function_id_list.delete();

Line: 2230

        l_entry_object_type_list.delete();

Line: 2231

        l_group_code_list.delete();

Line: 2285

                INSERT INTO AMW_ROLE_CONSTRAINT_DENORM
                VALUES(sysdate     -- last_update_date
                      ,G_USER_ID   -- last_updated_by
                      ,G_LOGIN_ID  -- last_update_login
                      ,sysdate     -- creation_date
                      ,G_USER_ID   -- created_by
                      ,G_FUNCTION_ID_LIST_HIER(i)    -- function_id
                      ,G_MENU_ID_LIST_HIER(i)        -- menu_id
                      ,p_constraint_rev_id    -- constraint_rev_id
                      ,G_ENTRY_OBJECT_TYPE_LIST_HIER(i)                  -- object_type
                      ,G_GROUP_CODE_LIST_HIER(i)     -- group_code
                      ,G_ROLE_NAME_LIST_HIER(i)      -- role_name
                      ,G_RESPONSIBILITY_ID_LIST_HIER(i));                -- responsibility_id

Line: 2308

END Update_Role_Constraint_Denorm;

Line: 2356

    'select distinct rv.display_name '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_ALL_ROLES_VL||' rv '
  ||'  where rcd.constraint_rev_id = :1 '
  ||'    and (rcd.role_name = rv.name or (rv.orig_system = ''FND_RESP'' and rcd.responsibility_id = rv.orig_system_id ) ) '
  ||'    and rv.name in (:2) ';

Line: 2368

  ' SELECT distinct rv.display_name '
  ||' FROM fnd_responsibility_vl frv '
  ||'      ,'||G_AMW_ALL_ROLES_VL||' rv ,'
  ||'      amw_constraint_entries ace'
  ||' WHERE rv.name IN (:1)'
  ||'       AND frv.responsibility_id = ace.function_id '
  ||'       AND frv.APPLICATION_ID = ace.APPLICATION_ID '
  ||'       AND ace.object_type = ''RESP'''
  ||'       AND ace.constraint_rev_id = :2'
  ||'       AND ( rv.display_name = frv.responsibility_name OR ( rv.orig_system = ''FND_RESP'' AND frv.responsibility_id = rv.orig_system_id ))'
  ||'       AND (frv.end_date is null OR (frv.end_date is not null AND frv.end_date > sysdate))';

Line: 2466

    'select distinct rv.display_name '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'      ,'||G_AMW_ALL_ROLES_VL||' rv '
  ||'  where rcd.constraint_rev_id = :1 '
  ||'    and u.user_id = :2 '
  ||'    and u.user_name = ur.user_name '
  ||'    and ur.role_name = rcd.role_name '
  ||'    and ur.role_orig_system = ''UMX'' '
  ||'    and ur.role_name = rv.name ';

Line: 2545

    'select distinct resp.responsibility_name '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'      ,'||G_AMW_RESPONSIBILITY_VL||' resp '
  ||'  where rcd.constraint_rev_id = :1 '
  ||'    and u.user_id = :2 '
  ||'    and u.user_name = ur.user_name '
  ||'    and rcd.responsibility_id = resp.responsibility_id '
  ||'    and ur.role_orig_system_id = rcd.responsibility_id '
  ||'    and ur.role_orig_system = ''FND_RESP'' '
  ||'    and ur.role_orig_system_id = resp.responsibility_id ';

Line: 2624

    '  select menu.user_menu_name '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_MENUS_VL||' menu '
  ||'      ,'||G_AMW_user||' u '
  ||'      ,'||G_AMW_GRANTS||' gra '
  ||'  where rcd.constraint_rev_id = :1 '
  ||'    and u.user_id = :2 '
  ||'    and u.user_name = gra.grantee_key '
  ||'    and gra.grantee_type = ''USER'' '
  ||'    and gra.instance_type = ''GLOBAL'' '
  ||'    and gra.object_id = -1 '
  ||'    and gra.menu_id = rcd.menu_id '
  ||'    and gra.menu_id = menu.menu_id '
  ||' UNION '
  ||'  select menu.user_menu_name '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_MENUS_VL||' menu '
  ||'      ,'||G_AMW_GRANTS||' gra '
  ||'  where rcd.constraint_rev_id = :3 '
  ||'    and gra.grantee_key = ''GLOBAL'' '
  ||'    and gra.grantee_type = ''GLOBAL'' '
  ||'    and gra.instance_type = ''GLOBAL'' '
  ||'    and gra.object_id = -1 '
  ||'    and gra.menu_id = rcd.menu_id '
  ||'    and gra.menu_id = menu.menu_id ';

Line: 2755

        'SELECT u.user_id '
      ||'  FROM '||G_AMW_USER ||' u '
      ||' WHERE u.user_name = :1  ';

Line: 2852

   'select * from ( '
   -- ALL
 ||' select AMW_VIOLATION_PVT.get_violat_new_role_list(:1,cst.constraint_rev_id,cst.type_code,:2) NEW_ROLE '
 ||'       ,cst.constraint_rev_id '
 ||' from amw_constraints_b cst '
 ||' where cst.type_code = ''ALL'' '
 ||'   and cst.start_date <= sysdate AND (cst.end_date IS NULL OR cst.end_date >= sysdate) '
 ||'   and (select count(*) '
 ||'        from amw_constraint_entries ce '
 ||'        where ce.constraint_rev_id = cst.constraint_rev_id) = ( '
 ||' select count(distinct rcd.function_id) '
 ||' from amw_role_constraint_denorm rcd '
 ||' where rcd.constraint_rev_id = cst.constraint_rev_id '
 ||'   and ( rcd.role_name = :3 '
 ||'      or (rcd.role_name in ( '
 ||'          select ur.role_name '
 ||'          from '||G_AMW_USER_ROLES||' ur '
 ||'              ,'||G_AMW_USER||' u '
 ||'          where u.user_id = :4 '
 ||'            and u.user_name = ur.user_name '
 ||'            and ur.role_orig_system = ''UMX'') '
 ||'          ) '
 ||'      or (rcd.responsibility_id in ( '
 ||'          select ur.role_orig_system_id '
 ||'          from '||G_AMW_USER_ROLES||' ur '
 ||'              ,'||G_AMW_USER||' u '
 ||'          where u.user_id = :5 '
 ||'            and u.user_name = ur.user_name '
 ||'            and ur.role_orig_system = ''FND_RESP'') '
 ||'          ) '
 ||'       or (rcd.menu_id in ( '
 ||'           select gra.menu_id '
 ||'           from '||G_AMW_GRANTS||' gra '
 ||'               ,'||G_AMW_USER||' u '
 ||'           where u.user_id = :6 '
 ||'             and u.user_name = gra.grantee_key '
 ||'             and gra.grantee_type = ''USER'' '
 ||'             and gra.instance_type = ''GLOBAL'' '
 ||'             and gra.object_id = -1) '
 ||'           ) '
 ||'       or (rcd.menu_id in ( '
 ||'           select gra.menu_id '
 ||'           from '||G_AMW_GRANTS||' gra '
 ||'           where gra.grantee_key = ''GLOBAL'' '
 ||'             and gra.grantee_type = ''GLOBAL'' '
 ||'             and gra.instance_type = ''GLOBAL'' '
 ||'             and gra.object_id = -1) '
 ||'           ) '
 ||'   ) '
 ||' ) '
 ||' UNION ALL '
 -- ME
 ||' select AMW_VIOLATION_PVT.get_violat_new_role_list(:7,cst.constraint_rev_id,cst.type_code,:8) NEW_ROLE '
 ||'      , cst.constraint_rev_id '
 ||' from amw_constraints_b cst '
 ||' where cst.type_code = ''ME'' '
 ||'   and cst.start_date <= sysdate AND (cst.end_date IS NULL OR cst.end_date >= sysdate) '
 ||'   and (select count(distinct rcd.function_id) '
 ||'        from amw_role_constraint_denorm rcd '
 ||'        where rcd.constraint_rev_id = cst.constraint_rev_id '
 ||'          and ( rcd.role_name = :9 '
 ||'             or (rcd.role_name in ( '
 ||'                 select ur.role_name '
 ||'                 from '||G_AMW_USER_ROLES||' ur '
 ||'                     ,'||G_AMW_USER||' u '
 ||'                 where u.user_id = :10 '
 ||'                   and u.user_name = ur.user_name '
 ||'                   and ur.role_orig_system = ''UMX'') '
 ||'                 ) '
 ||'             or (rcd.responsibility_id in ( '
 ||'                 select ur.role_orig_system_id '
 ||'                 from '||G_AMW_USER_ROLES||' ur '
 ||'                     ,'||G_AMW_USER||' u '
 ||'                 where u.user_id = :11 '
 ||'                   and u.user_name = ur.user_name '
 ||'                   and ur.role_orig_system = ''FND_RESP'') '
 ||'                 ) '
 ||'             or (rcd.menu_id in ( '
 ||'                 select gra.menu_id '
 ||'                 from '||G_AMW_GRANTS||' gra '
 ||'                     ,'||G_AMW_USER||' u '
 ||'                 where u.user_id = :12 '
 ||'                   and u.user_name = gra.grantee_key '
 ||'                   and gra.grantee_type = ''USER'' '
 ||'                   and gra.instance_type = ''GLOBAL'' '
 ||'                   and gra.object_id = -1) '
 ||'                 ) '
 ||'              or (rcd.menu_id in ( '
 ||'                  select gra.menu_id '
 ||'                  from '||G_AMW_GRANTS||' gra '
 ||'                  where gra.grantee_key = ''GLOBAL'' '
 ||'                    and gra.grantee_type = ''GLOBAL'' '
 ||'                    and gra.instance_type = ''GLOBAL'' '
 ||'                    and gra.object_id = -1) '
 ||'                  ) '
 ||'          ) '
 ||'       ) >= 2 '
 ||' UNION ALL '
 -- SET
 ||' select AMW_VIOLATION_PVT.get_violat_new_role_list(:13,cst.constraint_rev_id,cst.type_code,:14) NEW_ROLE '
 ||'       ,cst.constraint_rev_id '
 ||' from amw_constraints_b cst '
 ||' where cst.type_code = ''SET'' '
 ||'   and cst.start_date <= sysdate AND (cst.end_date IS NULL OR cst.end_date >= sysdate) '
 ||'   and (select count(distinct rcd.group_code) '
 ||'        from amw_role_constraint_denorm rcd '
 ||'        where rcd.constraint_rev_id = cst.constraint_rev_id '
 ||'          and (rcd.role_name = :15 '
 ||'            or (rcd.role_name in ( '
 ||'                select ur.role_name '
 ||'                from '||G_AMW_USER_ROLES||' ur '
 ||'                    ,'||G_AMW_USER||' u '
 ||'                where u.user_id = :16 '
 ||'                  and u.user_name = ur.user_name '
 ||'                  and ur.role_orig_system = ''UMX'') '
 ||'               ) '
 ||'            or (rcd.responsibility_id in ( '
 ||'                select ur.role_orig_system_id '
 ||'                from '||G_AMW_USER_ROLES||' ur '
 ||'                    ,'||G_AMW_USER||' u '
 ||'                where u.user_id = :17 '
 ||'                  and u.user_name = ur.user_name '
 ||'                  and ur.role_orig_system = ''FND_RESP'') '
 ||'               ) '
 ||'            or (rcd.menu_id in ( '
 ||'                select gra.menu_id '
 ||'                from '||G_AMW_GRANTS||' gra '
 ||'                    ,'||G_AMW_USER||' u '
 ||'                where u.user_id = :18 '
 ||'                  and u.user_name = gra.grantee_key '
 ||'                  and gra.grantee_type = ''USER'' '
 ||'                  and gra.instance_type = ''GLOBAL'' '
 ||'                  and gra.object_id = -1) '
 ||'               ) '
 ||'            or (rcd.menu_id in ( '
 ||'                select gra.menu_id '
 ||'                from '||G_AMW_GRANTS||' gra '
 ||'                where gra.grantee_key = ''GLOBAL'' '
 ||'                  and gra.grantee_type = ''GLOBAL'' '
 ||'                  and gra.instance_type = ''GLOBAL'' '
 ||'                  and gra.object_id = -1) '
 ||'               ) '
 ||'          ) '
 ||'       ) >= 2 '
 ||' UNION ALL '
   -- RESPALL
 ||' select AMW_VIOLATION_PVT.get_violat_new_role_list(:19,cst.constraint_rev_id,cst.type_code,:20) NEW_ROLE '
 ||'       ,cst.constraint_rev_id '
 ||' from amw_constraints_b cst '
 ||' where cst.type_code = ''RESPALL'' '
 ||'   and cst.start_date <= sysdate AND (cst.end_date IS NULL OR cst.end_date >= sysdate) '
 ||'   and (select count(*) '
 ||'        from amw_constraint_entries ce '
 ||'        where ce.constraint_rev_id = cst.constraint_rev_id) = ( '
 ||        '  select count(distinct ur.role_orig_system_id) '
 ||'  from '
 || G_AMW_USER_ROLES||' ur '
 ||'      ,'||G_AMW_user||' u '
 ||'      ,amw_constraint_entries ce '
 ||'  where  u.user_id = :21 '
 ||'    and  ce.constraint_rev_id = cst.constraint_rev_id '
 ||'    and u.user_name = ur.user_name '
 ||'    and ur.role_orig_system_id = ce.function_id '
 ||'    and (ur.role_orig_system = ''FND_RESP'' '
 ||'    or ur.role_orig_system_id in ( '
 ||'  select distinct rle.orig_system_id '
 ||'  from  ' || G_AMW_ALL_ROLES_VL || ' rle '
 ||'  where rle.orig_system = ''FND_RESP'' '
 ||'  and ce.function_id = rle.orig_system_id '
 ||'  and rle.name in (:22)) '
 ||') '
 || ') '
 ||' UNION ALL '
   -- RESPME
  ||' select AMW_VIOLATION_PVT.get_violat_new_role_list(:23,cst.constraint_rev_id,cst.type_code,:24) NEW_ROLE '
 ||'      , cst.constraint_rev_id '
 ||' from amw_constraints_b cst '
 ||' where cst.type_code = ''RESPME'' '
 ||'   and cst.start_date <= sysdate AND (cst.end_date IS NULL OR cst.end_date >= sysdate) '
 ||'   and '
 || '  (select count(distinct ur.role_orig_system_id ) '
 ||'  from '
 || G_AMW_USER_ROLES||' ur '
 ||'      ,'||G_AMW_user||' u '
 ||'      ,amw_constraint_entries ce '
 ||'  where  u.user_id = :25 '
 ||'    and  ce.constraint_rev_id = cst.constraint_rev_id '
 ||'    and u.user_name = ur.user_name '
 ||'    and ur.role_orig_system_id = ce.function_id '
 ||'    and (ur.role_orig_system = ''FND_RESP'' '
 ||'    or ur.role_orig_system_id in ( '
 ||'  select distinct rle.orig_system_id '
 ||'  from  ' || G_AMW_ALL_ROLES_VL || ' rle '
 ||'  where rle.orig_system = ''FND_RESP'' '
 ||'  and ce.function_id = rle.orig_system_id '
 ||'  and rle.name in (:26 )) '
 ||') '
 ||') '
 ||' >= 2 '
 ||' UNION ALL '
 -- RESPSET
  ||' select AMW_VIOLATION_PVT.get_violat_new_role_list(:27,cst.constraint_rev_id,cst.type_code,:28) NEW_ROLE '
 ||'      , cst.constraint_rev_id '
 ||' from amw_constraints_b cst '
 ||' where cst.type_code = ''RESPME'' '
 ||'   and cst.start_date <= sysdate AND (cst.end_date IS NULL OR cst.end_date >= sysdate) '
 ||'   and '
 || '  (select count(distinct ur.role_orig_system_id ) '
 ||'  from '
 || G_AMW_USER_ROLES||' ur '
 ||'      ,'||G_AMW_user||' u '
 ||'      ,amw_constraint_entries ce '
 ||'  where  u.user_id = :29 '
 ||'    and  ce.constraint_rev_id = cst.constraint_rev_id '
 ||'    and u.user_name = ur.user_name '
 ||'    and ur.role_orig_system_id = ce.function_id '
 ||'    and (ur.role_orig_system = ''FND_RESP'' '
 ||'    or ur.role_orig_system_id in ( '
 ||'  select distinct rle.orig_system_id '
 ||'  from  ' || G_AMW_ALL_ROLES_VL || ' rle '
 ||'  where rle.orig_system = ''FND_RESP'' '
 ||'  and ce.function_id = rle.orig_system_id '
 ||'  and rle.name in (:30 )) '
 ||') '
 ||') '
 ||' >= 2 '
  ||' ) where NEW_ROLE IS NOT NULL ';

Line: 3083

SELECT CONTROL_ID
          FROM AMW_CONTROLS_ALL_VL ctl
          WHERE ctl.AUTOMATION_TYPE ='SOD'
            AND CURR_APPROVED_FLAG ='Y'
            AND ctl.SOURCE =l_constraint_id;

Line: 3096

SELECT distinct TO_NUMBER(REPLACE(grants.grantee_key,'HZ_PARTY:','')) party_id
FROM AMW_control_Associations ca,
     fnd_grants grants,
     fnd_menus granted_menu,
     fnd_objects obj
WHERE   CONTROL_ID = l_cont_id
   AND  OBJECT_TYPE ='RISK'
   AND  APPROVAL_DATE IS NOT NULL
   AND  DELETION_APPROVAL_DATE IS NULL
   and obj.obj_name = 'AMW_PROCESS_APPR_ETTY'
   AND grants.object_id = obj.object_id
   AND   grants.grantee_type ='USER'
   AND   grantee_key like 'HZ_PARTY%'
   AND   NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
   AND   grants.menu_id = granted_menu.menu_id
   AND   grants.instance_type = 'INSTANCE'
   AND   grants.instance_pk1_value = TO_CHAR(ca.PK1)
   AND   grants.instance_pk2_value = '*NULL*'
   AND   grants.instance_pk3_value = '*NULL*'
   AND   grants.instance_pk4_value = '*NULL*'
   AND   grants.instance_pk5_value = '*NULL*'
   AND   granted_menu.menu_name =  'AMW_RL_PROC_OWNER_ROLE'
   UNION
   SELECT   distinct TO_NUMBER(REPLACE(grants.grantee_key,'HZ_PARTY:','')) party_id
    FROM    AMW_control_Associations ca,
            fnd_grants grants,
            fnd_menus granted_menu,
            fnd_objects obj
    WHERE CONTROL_ID = l_cont_id
        AND  OBJECT_TYPE ='RISK_ORG'
        AND  APPROVAL_DATE IS NOT NULL
        AND  DELETION_APPROVAL_DATE IS NULL
        AND  obj.obj_name = 'AMW_PROCESS_ORGANIZATION'
        AND   grants.object_id = obj.object_id
        AND   grants.grantee_type ='USER'
        AND   grantee_key like 'HZ_PARTY%'
        AND   NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
        AND   grants.menu_id = granted_menu.menu_id
        AND   grants.instance_type = 'INSTANCE'
        AND   grants.instance_pk1_value = to_char(ca.pk1)      -- PASS ORG_ID AS STRING
        AND   grants.instance_pk2_value = to_char(ca.pk2)      -- PASS PROCESS_ID AS STRING I.E. to_char(process_id)
        AND   grants.instance_pk3_value = '*NULL*'
        AND   grants.instance_pk4_value = '*NULL*'
        AND   grants.instance_pk5_value = '*NULL*'
        and   granted_menu.menu_name = 'AMW_ORG_PROC_OWNER_ROLE';

Line: 3199

        SELECT  constraint_id into l_constraint_id
        FROM    AMW_CONSTRAINTS_VL
        WHERE   constraint_rev_id=violated_cst_rev_id_list(i);

Line: 3288

       select constraint_name
       from   amw_constraints_vl
       where  constraint_rev_id = l_constraint_rev_id;

Line: 3293

       select employee_id
       from   amw_employees_current_v
       where  party_id = c_party_id;

Line: 3300

            'SELECT u.user_name '
          ||'  FROM '||G_AMW_USER ||' u '
          ||' WHERE u.user_id = :1  ';

Line: 3404

      SELECT constraint_rev_id, type_code, constraint_name, objective_code
        FROM amw_constraints_vl
       WHERE start_date <= sysdate AND (end_date IS NULL OR end_date >= sysdate);

Line: 3414

      SELECT count(*)
        FROM amw_constraint_entries
	   WHERE constraint_rev_id=l_constraint_rev_id;

Line: 3424

    'select count(distinct function_id) from ( '
  ||'  select rcd.function_id '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'  where rcd.constraint_rev_id = :1 '
  ||'    and u.user_id = :2 '
  ||'    and u.user_name = ur.user_name '
  ||'    and ur.role_name = rcd.role_name '
  ||'    and ur.role_orig_system = ''UMX'' '
  ||'  UNION ALL '
  ||'  select rcd.function_id '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'  where rcd.constraint_rev_id = :3 '
  ||'    and u.user_id = :4 '
  ||'    and u.user_name = ur.user_name '
  ||'    and ur.role_orig_system_id = rcd.responsibility_id '
  ||'    and ur.role_orig_system = ''FND_RESP'' '
  ||'  UNION ALL '
  ||'  select rcd.function_id '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_GRANTS||' gra '
  ||'      ,'||G_AMW_USER||' u '
  ||'  where rcd.constraint_rev_id = :5 '
  ||'    and u.user_id = :6 '
  ||'    and u.user_name = gra.grantee_key '
  ||'    and gra.grantee_type = ''USER'' '
  ||'    and gra.menu_id = rcd.menu_id '
  ||'    and gra.instance_type = ''GLOBAL'' '
  ||'    and gra.object_id = -1 '
  ||'  UNION ALL '
  ||'  select rcd.function_id '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_GRANTS||' gra '
  ||'  where rcd.constraint_rev_id = :7 '
  ||'    and gra.grantee_key = ''GLOBAL'' '
  ||'    and gra.grantee_type = ''GLOBAL'' '
  ||'    and gra.menu_id = rcd.menu_id '
  ||'    and gra.instance_type = ''GLOBAL'' '
  ||'    and gra.object_id = -1 '
  ||'  UNION ALL '
  ||'  select rcd.function_id '
  ||'  from amw_role_constraint_denorm rcd '
  ||'  where rcd.constraint_rev_id = :8 '
  ||'    and rcd.responsibility_id = :9 '
  ||') ';

Line: 3474

    'select count(distinct group_code) from ( '
  ||'  select rcd.group_code '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'  where rcd.constraint_rev_id = :1 '
  ||'    and u.user_id = :2 '
  ||'    and u.user_name = ur.user_name '
  ||'    and ur.role_name = rcd.role_name '
  ||'    and ur.role_orig_system = ''UMX'' '
  ||'  UNION ALL '
  ||'  select rcd.group_code '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'  where rcd.constraint_rev_id = :3 '
  ||'    and u.user_id = :4 '
  ||'    and u.user_name = ur.user_name '
  ||'    and ur.role_orig_system_id = rcd.responsibility_id '
  ||'    and ur.role_orig_system = ''FND_RESP'' '
  ||'  UNION ALL '
  ||'  select rcd.group_code '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_GRANTS||' gra '
  ||'      ,'||G_AMW_USER||' u '
  ||'  where rcd.constraint_rev_id = :5 '
  ||'    and u.user_id = :6 '
  ||'    and u.user_name = gra.grantee_key '
  ||'    and gra.grantee_type = ''USER'' '
  ||'    and gra.menu_id = rcd.menu_id '
  ||'    and gra.instance_type = ''GLOBAL'' '
  ||'    and gra.object_id = -1 '
  ||'  UNION ALL '
  ||'  select rcd.group_code '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_GRANTS||' gra '
  ||'  where rcd.constraint_rev_id = :7 '
  ||'    and gra.grantee_key = ''GLOBAL'' '
  ||'    and gra.grantee_type = ''GLOBAL'' '
  ||'    and gra.menu_id = rcd.menu_id '
  ||'    and gra.instance_type = ''GLOBAL'' '
  ||'    and gra.object_id = -1 '
  ||'  UNION ALL '
  ||'  select rcd.group_code '
  ||'  from amw_role_constraint_denorm rcd '
  ||'  where rcd.constraint_rev_id = :8 '
  ||'    and rcd.responsibility_id = :9 '
  ||') ';

Line: 3526

    'select resp.responsibility_name '
  ||'  from amw_role_constraint_denorm rcd '
  ||'      ,'||G_AMW_RESPONSIBILITY_VL||' resp '
  ||'  where rcd.constraint_rev_id = :1 and resp.responsibility_id = :2 '
  ||'    and rcd.responsibility_id = resp.responsibility_id ';

Line: 3535

    SELECT count(*)
      FROM amw_constraint_waivers
     WHERE constraint_rev_id = l_constraint_rev_id
       AND object_type = 'USER'
       AND PK1 = l_user_id
       AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);

Line: 3800

select distinct FUNCTION_ID
from FND_COMPILED_MENU_FUNCTIONS
where MENU_ID = p_menu_id;

Line: 3805

select *
from AMW_CONSTRAINTS_VL
where
(TYPE_CODE = 'ALL' or TYPE_CODE = 'ME' or TYPE_CODE = 'SET') and
START_DATE <= sysdate and
(END_DATE is null or END_DATE >= sysdate);

Line: 3813

select distinct FUNCTION_ID, GROUP_CODE
from AMW_CONSTRAINT_ENTRIES
where
CONSTRAINT_REV_ID = p_constraint_rev_id and
(OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);

Line: 3820

select distinct GROUP_CODE
from AMW_CONSTRAINT_ENTRIES
where
CONSTRAINT_REV_ID = p_constraint_rev_id and
(OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);

Line: 3845

select USER_FUNCTION_NAME into m_function_name
from FND_FORM_FUNCTIONS_VL
where FUNCTION_ID = g_constraint_function_id_list(i);

Line: 3973

select distinct FUNCTION_ID
from FND_COMPILED_MENU_FUNCTIONS
where MENU_ID in
(
select MENU_ID
from FND_MENU_ENTRIES
start with MENU_ID = p_menu_id
connect by prior MENU_ID = SUB_MENU_ID
);

Line: 3984

select *
from AMW_CONSTRAINTS_VL
where
(TYPE_CODE = 'ALL' or TYPE_CODE = 'ME' or TYPE_CODE = 'SET') and
START_DATE <= sysdate and
(END_DATE is null or END_DATE >= sysdate);

Line: 3992

select distinct FUNCTION_ID, GROUP_CODE
from AMW_CONSTRAINT_ENTRIES
where
CONSTRAINT_REV_ID = p_constraint_rev_id and
(OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);

Line: 3999

select distinct GROUP_CODE
from AMW_CONSTRAINT_ENTRIES
where
CONSTRAINT_REV_ID = p_constraint_rev_id and
(OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);

Line: 4024

select USER_FUNCTION_NAME into m_function_name
from FND_FORM_FUNCTIONS_VL
where FUNCTION_ID = g_constraint_function_id_list(i);