APPS.IEM_ROUTE_PUB SQL Statements

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

Line: 210

deleteFlag      JTF_RS_ROLE_RELATIONS.DELETE_FLAG%TYPE := 'N';

Line: 216

    select
     c.resource_id
   into
     resourceId
   from
    jtf_rs_roles_b a,
    jtf_rs_role_relations b,
    jtf_rs_group_members c,
    --jtf_rs_resource_values d,
	iem_agents d,
    JTF_RS_GROUPS_B e,
    JTF_RS_RESOURCE_EXTNS f,
    FND_USER_RESP_GROUPS g,
    FND_USER h,
    JTF_RS_GROUP_USAGES i,
    FND_RESPONSIBILITY resp
   where
    a.role_type_code = roleTypeCode
   and
    a.role_id = b.role_id
   and
    b.delete_flag = deleteFlag
   and
     b.START_DATE_ACTIVE< sysdate
   and
    ( b.END_DATE_ACTIVE>sysdate or b.END_DATE_ACTIVE is null)
   and
    b.role_resource_id = c.resource_id
   and
    c.group_id = groupIdToCheck
   and
    c.delete_flag = deleteFlag
   and
    c.resource_id = d.resource_id
   and
    --d.value_type = emailAccountId
	d.email_account_id = emailAccountId
   and
    c.group_id = e.group_id
   and
    e.START_DATE_ACTIVE< sysdate
   and
    ( e.END_DATE_ACTIVE>sysdate or e.END_DATE_ACTIVE is null)
   and
     c.resource_id = f.resource_id
   and
     f.START_DATE_ACTIVE< sysdate
   and
    ( f.END_DATE_ACTIVE>sysdate or f.END_DATE_ACTIVE is null)
   and
     f.user_id = g.user_id
   and
     g.START_DATE< sysdate
   and
    ( g.END_DATE>sysdate or g.END_DATE is null)
   and
--    ( g.responsibility_id = 23720 or g.responsibility_id = 23107 )
    ( g.responsibility_id = resp.responsibility_id and resp.application_id=680)
   and
    ( resp.responsibility_key = 'EMAIL_CENTER_SUPERVISOR' or resp.responsibility_key='IEM_SA_AGENT')
   and
    g.user_id = h.user_id
   and
     h.START_DATE< sysdate
   and
    ( h.END_DATE>sysdate or h.END_DATE is null)
   and
     c.group_id = i.group_id
   and
     i.usage = groupUsage
   and
    d.resource_id not in (select agent_id from iem_reroute_hists where message_id=p_message_id)
   and
    rownum = 1;

Line: 333

  select
    a.route_id,
    a.boolean_type_code,
    a.procedure_name,
    a.all_email,
    b.destination_group_id,
    b.default_group_id
  from
    iem_routes a,
    iem_account_routes b
  where
    a.route_id = b.route_id
  and
    b.enabled_flag = p_enabled_flag
  and
    b.email_account_id = p_account_id
  order by b.priority;

Line: 354

  select
    key_type_code,
    operator_type_code,
    value
  from
    iem_route_rules
  where
    route_id = p_route_id;

Line: 450

             ruleHold.delete;

Line: 459

                 runTimekeyVals_tbl.delete;

Line: 598

                            select count(*) into l_count from iem_reroute_hists
                                where message_id=l_msg_id and  agent_id=l_agent_id;