APPS.CCT_JTFRESOURCEROUTING_PUB SQL Statements

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

Line: 34

      SELECT distinct res.resource_id
      FROM   jtf_rs_resource_extns res,
		   jtf_rs_role_relations res_roles,
		   jtf_rs_roles_b roles
      where  res.resource_id in
      ((SELECT DISTINCT comp_ele.person_id
      FROM per_competence_elements comp_ele,
           per_competences comp
      WHERE (upper(comp_ele.competence_type) = upper(p_competency_type) and
	     upper(comp.name) = upper(p_competency_name) and
		comp.competence_id = comp_ele.competence_id)))
      and res.resource_id=res_roles.role_resource_id
	 and res_roles.role_id=roles.role_id
	 and (roles.role_type_code='CALLCENTER'
	 or roles.role_type_code='ICENTER');

Line: 84

       SELECT resource_id
       FROM jtf_rs_group_members
       WHERE delete_flag='N'
       AND group_id = (  SELECT distinct a.group_id
                        FROM jtf_rs_groups_vl a,
                             jtf_rs_group_usages_vl b
                        WHERE b.usage='CALL'
                        AND b.group_ID=a.group_ID
                        AND a.start_date_active<=sysdate
                        AND nvl(a.end_date_active,sysdate)>=sysdate
                        AND upper(a.group_name) = upper(p_group_name));

Line: 133

       SELECT resource_id
       FROM jtf_rs_group_members
       WHERE delete_flag='N'
       AND group_id= ( SELECT  a.group_id
                       FROM jtf_rs_groups_vl a,
                            jtf_rs_group_usages_vl b
                       WHERE b.usage='CALL'
                       AND b.group_ID=a.group_ID
                       AND a.start_date_active<=sysdate
                       AND nvl(a.end_date_active,sysdate)>=sysdate
                       AND a.group_number = p_group_number);

Line: 177

    l_select_csr             INTEGER;

Line: 182

       SELECT a.sql_text
       FROM JTF_RS_DYNAMIC_GROUPS_vl a
       WHERE upper(a.usage)='CALL'
       AND a.start_date_active<=sysdate
       AND nvl(a.end_date_active,sysdate)>=sysdate
       AND a.group_name = p_group_name
       AND sql_text is not null;

Line: 199

        l_select_csr := DBMS_SQL.OPEN_CURSOR;

Line: 200

        DBMS_SQL.PARSE(l_select_csr, l_text, DBMS_SQL.native);

Line: 201

        DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID);

Line: 202

        l_dummy := DBMS_SQL.EXECUTE(l_select_csr);

Line: 206

          IF DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 THEN
	        EXIT;

Line: 210

          DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);

Line: 217

        DBMS_SQL.CLOSE_CURSOR(l_select_csr);

Line: 237

    l_select_csr         INTEGER;

Line: 240

       SELECT a.sql_text
       FROM JTF_RS_DYNAMIC_GROUPS_B a
       WHERE upper(a.usage)='CALL'
       AND a.start_date_active<=sysdate
       AND nvl(a.end_date_active,sysdate)>=sysdate
       AND a.group_number = p_group_number
       AND sql_text is not null;

Line: 257

        l_select_csr := DBMS_SQL.OPEN_CURSOR;

Line: 258

        DBMS_SQL.PARSE(l_select_csr, l_text, DBMS_SQL.native);

Line: 259

        DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID);

Line: 260

        l_dummy := DBMS_SQL.EXECUTE(l_select_csr);

Line: 264

          IF DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 THEN
	        EXIT;

Line: 268

          DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);

Line: 275

        DBMS_SQL.CLOSE_CURSOR(l_select_csr);

Line: 295

    select  res.resource_id
        from jtf_rs_resource_extns res
            ,jtf_rs_role_relations res_roles
                ,jtf_rs_roles_b roles
        where  res.resource_id = res_roles.role_resource_id
            and res_roles.role_resource_type = 'RS_INDIVIDUAL'
            and res_roles.start_date_active<=sysdate
            and nvl(res_roles.end_date_active,sysdate)>=sysdate
            and res_roles.delete_flag = 'N'
            and res_roles.role_id=roles.role_id
            and (roles.role_type_code = 'CALLCENTER'
            or roles.role_type_code='ICENTER')
        MINUS
        select resource_id
        from jtf_rs_group_members
        where delete_flag='N'
        and group_id =  ( select a.group_id
                       from jtf_rs_groups_vl a,
                            jtf_rs_group_usages b
                       where upper(a.group_name) = upper(p_group_name)
                       and b.usage='CALL'
                       and b.group_ID=a.group_ID
                       and a.start_date_active<=sysdate
                       and nvl(a.end_date_active,sysdate)>=sysdate );

Line: 372

    select  res.resource_id
        from jtf_rs_resource_extns res
            ,jtf_rs_role_relations res_roles
                ,jtf_rs_roles_b roles
        where  res.resource_id = res_roles.role_resource_id
            and res_roles.role_resource_type = 'RS_INDIVIDUAL'
            and res_roles.start_date_active<=sysdate
            and nvl(res_roles.end_date_active,sysdate)>=sysdate
            and res_roles.delete_flag = 'N'
            and res_roles.role_id=roles.role_id
            and (roles.role_type_code = 'CALLCENTER'
            or roles.role_type_code='ICENTER')
        MINUS
        select resource_id
        from jtf_rs_group_members
        where delete_flag='N'
        and group_id =  ( select a.group_id
                       from jtf_rs_groups_b a,
                            jtf_rs_group_usages b
                       where a.group_number = p_group_number
                       and b.usage='CALL'
                       and b.group_ID=a.group_ID
                       and a.start_date_active<=sysdate
                       and nvl(a.end_date_active,sysdate)>=sysdate );

Line: 447

    l_default_select         VARCHAR2(4000);

Line: 451

    l_select_csr             INTEGER;

Line: 456

       SELECT a.sql_text
       FROM JTF_RS_DYNAMIC_GROUPS_vl a
       WHERE upper(a.usage)='CALL'
       AND a.start_date_active<=sysdate
       AND nvl(a.end_date_active,sysdate)>=sysdate
       AND upper(a.group_name) = upper(p_group_name)
       AND sql_text is not null;

Line: 472

      l_default_select :=
        'SELECT distinct res.resource_id '||
        'FROM jtf_rs_resource_extns res '||
         '   ,jtf_rs_role_relations res_roles '||
	     '   ,jtf_rs_roles_b roles '||
        'WHERE  res.resource_id = res_roles.role_resource_id '||
	    'and res_roles.role_id=roles.role_id '||
	    'and (roles.role_type_code = '||l_apos|| 'CALLCENTER' ||l_apos||
	    'or roles.role_type_code='||l_apos||'ICENTER'||l_apos||
        ' MINUS  ' ;

Line: 482

     l_query := l_default_select || l_text ;

Line: 486

        l_select_csr := DBMS_SQL.OPEN_CURSOR;

Line: 487

        DBMS_SQL.PARSE(l_select_csr, l_query, DBMS_SQL.native);

Line: 488

        DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID);

Line: 489

        l_dummy := DBMS_SQL.EXECUTE(l_select_csr);

Line: 493

          IF DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 THEN
	        EXIT;

Line: 497

          DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);

Line: 504

        DBMS_SQL.CLOSE_CURSOR(l_select_csr);

Line: 522

    l_default_select         VARCHAR2(4000);

Line: 526

    l_select_csr             INTEGER;

Line: 531

       SELECT a.sql_text
       FROM JTF_RS_DYNAMIC_GROUPS_vl a
       WHERE upper(a.usage)='CALL'
       AND a.start_date_active<=sysdate
       AND nvl(a.end_date_active,sysdate)>=sysdate
       AND a.group_number = p_group_number
       AND sql_text is not null;

Line: 547

      l_default_select :=
        'SELECT distinct res.resource_id '||
        'FROM jtf_rs_resource_extns res '||
         '   ,jtf_rs_role_relations res_roles '||
	     '   ,jtf_rs_roles_b roles '||
        'WHERE  res.resource_id = res_roles.role_resource_id '||
	    'and res_roles.role_id=roles.role_id '||
	    'and (roles.role_type_code = '||l_apos|| 'CALLCENTER' ||l_apos||
	    'or roles.role_type_code='||l_apos||'ICENTER'||l_apos||
        ' MINUS  ' ;

Line: 557

     l_query := l_default_select || l_text ;

Line: 561

        l_select_csr := DBMS_SQL.OPEN_CURSOR;

Line: 562

        DBMS_SQL.PARSE(l_select_csr, l_query, DBMS_SQL.native);

Line: 563

        DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID);

Line: 564

        l_dummy := DBMS_SQL.EXECUTE(l_select_csr);

Line: 568

          IF DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 THEN
	        EXIT;

Line: 572

          DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);

Line: 579

        DBMS_SQL.CLOSE_CURSOR(l_select_csr);