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.AMW_VIOLATION_PUB SQL Statements

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

Line: 31


    SELECT user_id INTO p_user_id  FROM fnd_user WHERE user_name = p_user_name;

Line: 60


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

Line: 186


      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: 202


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

Line: 217


    'select 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: 267


    'select count(function_id) from ( '
    || l_func_id_sql
    || ') ';

Line: 272


    'select 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: 322


    'select count(group_code) from ( '
    || l_func_set_id_sql
    ||' ) ';

Line: 332


  '  select ur.role_orig_system_id '
  ||'  from '
  || G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'      ,amw_constraint_entries cst '
  ||'  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'' '
  ||'  UNION '
  ||'  select cst.function_id as orig_system_id '
  ||'  from  amw_constraint_entries cst '
  ||'  where  cst.constraint_rev_id = :3 '
  ||'  and cst.function_id = :4 ';

Line: 350


' select count(role_orig_system_id) from ( '
|| l_resp_all_sql
||')';

Line: 356


  '  select cst.group_code '
  ||'  from '
  || G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'      ,amw_constraint_entries cst '
  ||'  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'' '
  ||'  UNION '
  ||'  select cst.group_code '
  ||'  from   amw_constraint_entries cst '
  ||'  where  cst.constraint_rev_id = :3 '
  ||'  and cst.function_id = :4 ';

Line: 373


  ' select count(group_code) from ( '
|| l_resp_set_all_sql
||')';

Line: 381


    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: 443


            l_func_sql :='select count(function_id)'
                        ||'from ('
                        ||'select function_id from ( '
                        ||   l_func_id_sql
                        ||') '
                        ||' MINUS '
                        ||' select FUNCTION_ID from amw_constraint_entries where constraint_rev_id = :10'
                        ||')';

Line: 545


            l_resp_sql := 'select count(distinct role_orig_system_id)'
             ||' from ('
             ||  l_resp_all_sql
             ||' MINUS '
             ||' select FUNCTION_ID from amw_constraint_entries where constraint_rev_id = :5'
             ||')';

Line: 700


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

Line: 711


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

Line: 722


    '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: 772


    '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: 824


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

Line: 832


    'select func.user_function_name '
  ||'  from amw_constraint_entries rcd '
  ||'      ,'|| G_AMW_FORM_FUNCTIONS_VL ||' func '
  ||'  where rcd.constraint_rev_id = :1  '
  ||'    and rcd.function_id = func.function_id ';

Line: 841


    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: 853


  '  select ur.role_orig_system_id '
  ||'  from '
  || G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'      ,amw_constraint_entries cst '
  ||'  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'' '
  ||'  UNION '
  ||'  select cst.function_id as orig_system_id '
  ||'  from  amw_constraint_entries cst '
  ||'  where  cst.constraint_rev_id = :3 '
  ||'  and cst.function_id = :4 ';

Line: 871


' select count(role_orig_system_id) from ( '
|| l_resp_all_sql
||')';

Line: 877


  '  select cst.group_code '
  ||'  from '
  || G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'      ,amw_constraint_entries cst '
  ||'  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'' '
  ||'  UNION '
  ||'  select cst.group_code '
  ||'  from   amw_constraint_entries cst '
  ||'  where  cst.constraint_rev_id = :3 '
  ||'  and cst.function_id = :4 ';

Line: 894


  ' select count(group_code) from ( '
|| l_resp_set_all_sql
||')';

Line: 1161


            l_resp_sql := 'select count(distinct role_orig_system_id)'
             ||' from ('
             ||  l_resp_all_sql
             ||' MINUS '
             ||' select FUNCTION_ID from amw_constraint_entries where constraint_rev_id = :5'
             ||')';

Line: 1427


    '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: 1522


    '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 ur.role_orig_system_id = rcd.responsibility_id '
  ||'    and ur.role_orig_system = ''FND_RESP'' '
  ||'    and ur.role_orig_system_id = resp.responsibility_id  '
  ||' UNION ALL  '
  ||' select distinct resp.responsibility_name '
  ||' from amw_constraint_entries cste '
  ||'      ,'||G_AMW_USER_ROLES||' ur '
  ||'      ,'||G_AMW_user||' u '
  ||'      ,'||G_AMW_RESPONSIBILITY_VL||' resp '
  ||'  where cste.constraint_rev_id = :3 '
  ||'    and u.user_id = :4 '
  ||'    and u.user_name = ur.user_name '
  ||'    and ur.role_orig_system_id = cste.function_id '
  ||'    and ur.role_orig_system = ''FND_RESP'' '
  ||'    and ur.role_orig_system_id = resp.responsibility_id ';

Line: 1632


    '  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: 1722


select distinct FUNCTION_ID
from FND_COMPILED_MENU_FUNCTIONS
where MENU_ID = p_menu_id;

Line: 1729


g_menu_function_id_list.delete();

Line: 1759


        SELECT sub_menu_id menu_id
        FROM   fnd_menu_entries
        START WITH menu_id =p_menu_id
        CONNECT BY PRIOR sub_menu_id = menu_id
        UNION
        SELECT menu_id
        FROM fnd_menu_entries
        START WITH menu_id =p_menu_id
        CONNECT BY PRIOR menu_id = sub_menu_id;

Line: 1777


        select distinct FUNCTION_ID
        from FND_MENU_ENTRIES  --FND_COMPILED_MENU_FUNCTIONS
        where MENU_ID = p_menu_id;

Line: 1782


        select distinct FUNCTION_ID
        from FND_MENU_ENTRIES --FND_COMPILED_MENU_FUNCTIONS
        where MENU_ID = p_menu_id or MENU_ID = p_sub_menu_id;

Line: 1790


    g_menu_id_list.delete();

Line: 1791


    g_function_id_list.delete();

Line: 1792


    g_menu_function_id_list.delete();

Line: 1832


g_menu_function_id_list.delete();

Line: 1893


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)
and objective_code = 'PR';

Line: 1902


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: 1909


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: 1917


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

Line: 1929


        g_constraint_function_id_list.delete();

Line: 1930


        g_constraint_group_code_list.delete();

Line: 1939


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

Line: 1991


            g_group_code_list.delete();

Line: 2080


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)
and objective_code = 'PR';

Line: 2089


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: 2096


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: 2108


    g_constraint_function_id_list.delete();

Line: 2109


    g_constraint_group_code_list.delete();

Line: 2119


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

Line: 2177


                g_group_code_list.delete();