APPS.JTF_RS_DEFAULT_GROUPS_CONC_PUB SQL Statements

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

Line: 21

                                       defaulted to. Modified First insert stmt to
                                       exclude group with group_id = -1
***************************************************************************/

/*****************************************************************************
  This procedure will populate default groups for Field Service District (usage:
  'FLD_SRV_DISTRICT') through concurrent program "Update Primary Districts for
  Field Service Engineers".

  Created By     nsinghai     07/21/2004
*****************************************************************************/

PROCEDURE  populate_fs_district
  (ERRBUF                    OUT NOCOPY VARCHAR2,
   RETCODE                   OUT NOCOPY VARCHAR2)
  IS

  TYPE default_grp_type IS RECORD
  (p_resource_id NUMBER,
   p_user_id NUMBER,
   p_resource_number VARCHAR2(60),
   p_group_id NUMBER,
   p_start_date DATE,
   p_end_date DATE
   );

Line: 84

  SELECT DISTINCT resource_id
  FROM   JTF_RS_DEFAULT_GROUPS_INT
  ;

Line: 89

  SELECT resource_id, user_id, resource_number, group_id, start_date, end_date
  FROM   JTF_RS_DEFAULT_GROUPS_INT
  WHERE  resource_id = ll_resource_id
  AND    start_date <= end_date
  ORDER BY denorm_count ASC, role_type_priority ASC, role_priority ASC, START_DATE desc;

Line: 96

    SELECT i.tablespace, i.index_tablespace, u.oracle_username
    FROM   fnd_product_installations i, fnd_application a, fnd_oracle_userid u
    WHERE  a.application_short_name = 'JTF'
    AND    a.application_id = i.application_id
    AND    u.oracle_id = i.oracle_id;

Line: 119

 	    SELECT value INTO l_parallel
	    FROM v$pq_sysstat  WHERE trim(statistic) = 'Servers Idle';

Line: 167

    SELECT owner, table_owner, tablespace_name
    INTO   l_index_owner, l_table_owner, l_index_tblspace
    FROM   ALL_INDEXES
    WHERE  TABLE_NAME = 'JTF_RS_DEFAULT_GROUPS_INT'
    AND    index_name = 'JTF_RS_DEFAULT_GROUPS_INT_N1'
    AND    table_owner= l_jtfu;

Line: 181

    SELECT owner, table_owner, tablespace_name
    INTO   l_index_owner, l_table_owner, l_index_tblspace
    FROM   ALL_INDEXES
    WHERE  TABLE_NAME = 'JTF_RS_GROUPS_DENORM'
    AND    index_name = 'JTF_RS_GROUPS_DENORM_U1'
	AND    table_owner= l_jtfu;

Line: 202

  l_stage      := 'Stage=INSERT_IN_JTF_RS_DEFAULT_GROUPS_INT_TABLE';

Line: 204

  INSERT INTO /*+ APPEND PARALLEL(sg1) NOLOGGING */ JTF_RS_DEFAULT_GROUPS_int sg1
       (default_groups_id, resource_id, user_id, resource_number,
        group_id, role_type_code, role_id, role_type_priority,
        role_priority, start_date, end_date, denorm_count, usage,
        created_by, creation_date, last_updated_by, last_update_date )
  SELECT JTF_RS_DEFAULT_GROUPS_STAGE_s.NEXTVAL default_groups_id,
       x.resource_id, x.user_id, x.resource_number,
       x.group_id,
       x.role_type_code, x.role_id, x.role_type_priority, x.role_priority,
       x.start_date, x.end_date, x.denorm_count, 'FLD_SRV_DISTRICT' usage,
       l_user_id, sysdate, l_user_id, sysdate
  FROM (
    SELECT /*+ use_hash(rrl mem usg rol den res) PARALLEL(rrl)
             PARALLEL(mem) PARALLEL(usg) PARALLEL(rol) PARALLEL(den)
             PARALLEL(res) */
      mem.resource_id
     ,mem.group_id
     ,res.user_id
     ,res.resource_number
     ,DECODE(rol.role_type_code,'CSF_REPRESENTATIVE',1,'CSF_DEBRIEF',2,
      'CSF_DEBRIEF_REVIEWAGENT',3,'CSF_DISPATCHER',4,'CSF_PLANNER',5,
	  'CSF_DBI_DISTRICT',6,7
      ) role_type_priority
     ,DECODE('Y',rol.member_flag,1,rol.manager_flag,2) role_priority
     ,rrl.start_date_active start_date
     ,nvl(rrl.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR')) end_date
     ,rrl.role_id
     ,rol.role_type_code
     ,COUNT(den.group_id) denorm_count
   FROM  jtf_rs_role_relations rrl
        ,jtf_rs_group_members mem
        ,jtf_rs_group_usages usg
        ,jtf_rs_roles_b rol
        ,jtf_rs_groups_denorm den
        ,jtf_rs_resource_extns res
   WHERE  rrl.role_resource_type = 'RS_GROUP_MEMBER'
     AND  rrl.delete_flag        = 'N'
     AND  rrl.role_resource_id   = mem.group_member_id
     AND  mem.delete_flag        = 'N'
     AND  mem.group_id           <> -1
     AND  mem.group_id           = usg.group_id
     AND  usg.usage              = 'FLD_SRV_DISTRICT'
     AND  rrl.role_id            = rol.role_id
     AND  rol.role_type_code IN ('CSF_REPRESENTATIVE','CSF_DEBRIEF',
      'CSF_DEBRIEF_REVIEWAGENT','CSF_DISPATCHER','CSF_PLANNER','CSF_DBI_DISTRICT')
     AND  'Y' IN (rol.member_flag, rol.manager_flag)
     AND  mem.resource_id        = res.resource_id
     AND  mem.group_id           = den.group_id
     AND  (	rrl.start_date_active BETWEEN den.start_date_active AND
            nvl(den.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR'))
       OR
           den.start_date_active BETWEEN rrl.start_date_active AND
           nvl(rrl.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR'))
         )
   GROUP BY mem.resource_id, res.user_id, res.resource_number,
     mem.group_id, rrl.start_date_active, rrl.end_date_active,
     DECODE(rol.role_type_code,'CSF_REPRESENTATIVE',1,'CSF_DEBRIEF',2,
      'CSF_DEBRIEF_REVIEWAGENT',3,'CSF_DISPATCHER',4,'CSF_PLANNER',5,
	  'CSF_DBI_DISTRICT',6,7),
     DECODE('Y',rol.member_flag,1,rol.manager_flag,2)
    ,rrl.role_id, rol.role_type_code
  ) x ;

Line: 269

  /* Insert all the rest of the Field Service Resources who do not have any group
     assign to them along with those who have groups assigned for limited time
  */

  l_stage      := 'Stage=INSERT_IN_JTF_RS_DEFAULT_GROUPS_INT_TABLE_FOR_UNASSIGNED_GROUPS';

Line: 275

  INSERT INTO /*+ APPEND PARALLEL(sg1) NOLOGGING */ JTF_RS_DEFAULT_GROUPS_INT sg1
       (default_groups_id, resource_id, user_id, resource_number,
        group_id, role_type_code, role_id, role_type_priority,
        role_priority, start_date, end_date, denorm_count, usage,
        created_by, creation_date, last_updated_by, last_update_date )
  SELECT JTF_RS_DEFAULT_GROUPS_STAGE_s.NEXTVAL default_groups_id,
       x.resource_id, x.user_id, x.resource_number,
       x.group_id,
       x.role_type_code, x.role_id, x.role_type_priority, x.role_priority,
       x.start_date, x.end_date, x.denorm_count, 'FLD_SRV_DISTRICT' usage,
       l_user_id, sysdate, l_user_id, sysdate
  FROM (
      SELECT /*+ use_hash(rrl rol res) PARALLEL(rrl) PARALLEL(rol) PARALLEL(res)*/
             DISTINCT
             res.resource_id,
             res.user_id,
             res.resource_number,
             -1 group_id,
             'NONE' role_type_code,
             -1 role_id,
             99 role_type_priority,
             99 role_priority,
             TO_DATE('01/01/1900','MM/DD/RRRR') START_DATE,
             TO_DATE('12/31/4712','MM/DD/RRRR') END_DATE,
             99999999 denorm_count
      FROM  jtf_rs_role_relations rrl
           ,jtf_rs_roles_b rol
           ,jtf_rs_resource_extns res
     WHERE  rrl.role_resource_type = 'RS_INDIVIDUAL'
       AND  rrl.delete_flag        = 'N'
       AND  rrl.role_id            = rol.role_id
       AND  rol.role_type_code IN ('CSF_REPRESENTATIVE','CSF_DEBRIEF',
            'CSF_DEBRIEF_REVIEWAGENT','CSF_DISPATCHER',
           'CSF_PLANNER','CSF_DBI_DISTRICT')
       AND  rrl.role_resource_id   = res.resource_id
     ) x;

Line: 429

                  temp_default_grp_tab.DELETE(l);

Line: 540

        temp_default_grp_tab.DELETE;

Line: 557

       INSERT INTO /*+ APPEND PARALLEL(sg1) NOLOGGING */ JTF_RS_DEFAULT_GROUPS_STAGE sg1
                (default_groups_id, resource_id, user_id, resource_number,
                 group_id, start_date, end_date, usage,
                 created_by, creation_date, last_updated_by, last_update_date )
       VALUES (JTF_RS_DEFAULT_GROUPS_STAGE_S.NEXTVAL, g_default_grp_tab(o).p_resource_id,
                 g_default_grp_tab(o).p_user_id, g_default_grp_tab(o).p_resource_number,
				 g_default_grp_tab(o).p_group_id, g_default_grp_tab(o).p_start_date,
				 g_default_grp_tab(o).p_end_date, l_usage,
                 l_user_id, sysdate, l_user_id, sysdate);

Line: 571

     g_default_grp_tab.DELETE;

Line: 590

  l_stage := 'Stage=DELETE_DATA_FROM_JTF_RS_DEFAULT_GROUPS_TABLE';

Line: 592

  DELETE FROM jtf_rs_default_groups
  WHERE (resource_id, NVL(user_id,-84), group_id, START_DATE, end_date, usage) IN
  (
   SELECT resource_id, NVL(user_id,-84), group_id, START_DATE, end_date, usage
   FROM   jtf_rs_default_groups
   MINUS
   SELECT resource_id, NVL(user_id,-84), group_id, START_DATE, end_date, usage
   FROM   jtf_rs_default_groups_stage
  )  ;

Line: 608

  l_stage := 'Stage=INSERT_DATA_IN_JTF_RS_DEFAULT_GROUPS_TABLE';

Line: 610

  INSERT INTO /*+ APPEND PARALLEL(sg1) NOLOGGING */ jtf_rs_default_groups sg1
            (default_groups_id, resource_id, user_id, resource_number,
             group_id, start_date, end_date, usage,
             created_by, creation_date, last_updated_by, last_update_date )
  SELECT
      jtf_rs_default_groups_s.NEXTVAL, resource_id, user_id, resource_number,
      group_id, start_date, end_date, usage,
      l_user_id created_by, SYSDATE creation_date, l_user_id last_updated_by, SYSDATE
  FROM (
         SELECT resource_id, user_id, resource_number, group_id, START_DATE, end_date, usage
         FROM jtf_rs_default_groups_stage
         MINUS
         SELECT resource_id, user_id, resource_number, group_id, START_DATE, end_date, usage
         FROM jtf_rs_default_groups
       );

Line: 670

   SELECT  group_id
   INTO    l_group_id
   FROM    jtf_rs_default_groups
   WHERE   resource_id = p_resource_id
   AND     usage       = p_usage
   AND     l_date BETWEEN start_date AND end_date;