APPS.JTF_RS_SRP_GROUPS_CONC_PUB SQL Statements

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

Line: 80

  SELECT salesrep_id, org_id
  FROM   jtf_rs_salesreps
  ;

Line: 85

  SELECT salesrep_id, org_id, resource_id, user_id, group_id, start_date, end_date
  FROM   jtf_rs_srp_groups_int
  WHERE  salesrep_id = ll_salesrep_id
  AND    nvl(org_id,-99)      = nvl(ll_org_id,-99)
  AND    start_date <= end_date
  ORDER BY denorm_count, role_type_priority, role_priority;

Line: 93

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

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

Line: 164

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

Line: 178

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

  l_stage      := 'Stage=INSERT_IN_JTF_RS_SRP_GROUPS_INT_TABLE';

Line: 200

  INSERT INTO /*+ APPEND PARALLEL(sg1) NOLOGGING */ jtf_rs_srp_groups_int sg1
       (srp_groups_id, resource_id, salesrep_id, org_id, user_id,
        group_id, role_type_code, role_id, role_type_priority,
        role_priority, start_date, end_date, denorm_count,
        created_by, creation_date, last_updated_by, last_update_date )
  SELECT jtf_rs_srp_groups_stage_s.NEXTVAL srp_groups_id,
       x.resource_id, x.salesrep_id, x.org_id, x.user_id,
       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,
       l_user_id, sysdate, l_user_id, sysdate
  FROM (
    SELECT /*+ use_hash(rrl mem usg rol den res srp) PARALLEL(rrl)
             PARALLEL(mem) PARALLEL(usg) PARALLEL(rol) PARALLEL(den)
             PARALLEL(res) PARALLEL(srp) */
      mem.resource_id
     ,mem.group_id
     ,srp.salesrep_id
     ,srp.org_id
     ,res.user_id
     ,DECODE(rol.role_type_code,'SALES',1,2) role_type_priority
     --,DECODE('Y',rol.manager_flag,1,rol.admin_flag,2,rol.lead_flag,3,4)role_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_salesreps srp
        ,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.resource_id        = srp.resource_id
     AND  mem.group_id           = usg.group_id
     AND  usg.usage              = 'SALES'
     AND  rrl.role_id            = rol.role_id
     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, srp.salesrep_id, srp.org_id,
           res.user_id, mem.group_id, rrl.start_date_active,
     rrl.end_date_active, DECODE(rol.role_type_code,'SALES',1,2),
--     DECODE('Y',rol.manager_flag,1,rol.admin_flag,2,rol.lead_flag,3,4)
     DECODE('Y',rol.member_flag,1,rol.manager_flag,2)
    ,rrl.role_id, rol.role_type_code
  ) x ;

Line: 263

  l_stage      := 'Stage=INSERT_IN_JTF_RS_SRP_GROUPS_INT_TABLE_FOR_UNASSIGNED_GROUPS';

Line: 265

  INSERT INTO /*+ APPEND PARALLEL(sg1) NOLOGGING */ jtf_rs_srp_groups_int sg1
       (srp_groups_id, resource_id, salesrep_id, org_id, user_id,
        group_id, role_type_code, role_id, role_type_priority,
        role_priority, start_date, end_date, denorm_count,
        created_by, creation_date, last_updated_by, last_update_date )
  SELECT jtf_rs_srp_groups_stage_s.NEXTVAL srp_groups_id,
       x.resource_id, x.salesrep_id, x.org_id, x.user_id,
       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,
       l_user_id, sysdate, l_user_id, sysdate
  FROM (
      SELECT /*+ use_hash(srp res) PARALLEL(srp) PARALLEL(res) */
             srp.resource_id,
             srp.salesrep_id,
             srp.org_id,
             res.user_id,
             -1 group_id,
             'NONE' role_type_code,
             -1 role_id,
             9 role_type_priority,
             9 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_salesreps srp,
             jtf_rs_resource_extns res
      WHERE  srp.resource_id = res.resource_id
     ) x;

Line: 413

                  temp_srp_grp_tab.DELETE(l);

Line: 530

        temp_srp_grp_tab.DELETE;

Line: 547

       INSERT INTO /*+ APPEND PARALLEL(sg1) NOLOGGING */ jtf_rs_srp_groups_stage sg1
                (srp_groups_id, resource_id, salesrep_id, org_id, user_id,
                 group_id, start_date, end_date,
                 created_by, creation_date, last_updated_by, last_update_date )
       VALUES (jtf_rs_srp_groups_stage_s.NEXTVAL, g_srp_grp_tab(o).p_resource_id,
                 g_srp_grp_tab(o).p_salesrep_id, g_srp_grp_tab(o).p_org_id,
                 g_srp_grp_tab(o).p_user_id, g_srp_grp_tab(o).p_group_id,
                 g_srp_grp_tab(o).p_start_date, g_srp_grp_tab(o).p_end_date,
                 l_user_id, sysdate, l_user_id, sysdate);

Line: 561

     g_srp_grp_tab.DELETE;

Line: 583

  l_stage := 'Stage=DELETE_DATA_FROM_JTF_RS_SRP_GROUPS_TABLE';

Line: 585

  DELETE FROM jtf_rs_srp_groups
  WHERE (resource_id, salesrep_id, NVL(org_id,-99), NVL(user_id,-84), group_id, START_DATE, end_date) IN
  (
   SELECT resource_id, salesrep_id, NVL(org_id,-99), NVL(user_id,-84), group_id, START_DATE, end_date
   FROM   jtf_rs_srp_groups
   MINUS
   SELECT resource_id, salesrep_id, NVL(org_id,-99), NVL(user_id,-84), group_id, START_DATE, end_date
   FROM   jtf_rs_srp_groups_stage
  )  ;

Line: 601

  l_stage := 'Stage=INSERT_DATA_IN_JTF_RS_SRP_GROUPS_TABLE';

Line: 603

  INSERT INTO /*+ APPEND PARALLEL(sg1) NOLOGGING */ jtf_rs_srp_groups sg1
        (srp_groups_id, resource_id, salesrep_id, org_id, user_id,
         group_id, start_date, end_date,
         created_by, creation_date, last_updated_by, last_update_date )
  SELECT
      jtf_rs_srp_groups_s.NEXTVAL, resource_id, salesrep_id, org_id, user_id,
      group_id, start_date, end_date,
      l_user_id created_by, SYSDATE creation_date, l_user_id last_updated_by, SYSDATE
  FROM (
         SELECT resource_id, salesrep_id, org_id, user_id, group_id, START_DATE, end_date
         FROM jtf_rs_srp_groups_stage
         MINUS
         SELECT resource_id, salesrep_id, org_id, user_id, group_id, START_DATE, end_date
         FROM jtf_rs_srp_groups
       );