APPS.CN_SRP_HIER_PROC_PVT SQL Statements

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

Line: 5

G_LAST_UPDATE_DATE          DATE := Sysdate;

Line: 6

G_LAST_UPDATED_BY           NUMBER := fnd_global.user_id;

Line: 9

G_LAST_UPDATE_LOGIN         NUMBER := fnd_global.login_id;

Line: 74

     SELECT parent_comp_group_id parent_group_id,
       trunc(start_date_active) start_date, trunc(end_date_active) end_date
	FROM cn_qm_group_hier
	WHERE comp_group_id = p_group.group_id;

Line: 223

      SELECT comp_group_id group_id,
	trunc(start_date_active) start_date,
	trunc(end_date_active) end_date
	FROM cn_qm_group_hier
	WHERE parent_comp_group_id = p_group.group_id;

Line: 373

     SELECT srp_role_id, comp_group_id, trunc(start_date_active) start_date_active,
       trunc(end_date_active) end_date_active
       FROM cn_qm_srp_groups
       WHERE srp_id = p_salesrep_id
       AND manager_flag = 'N';

Line: 380

     SELECT manager_srp_id, comp_group_id, trunc(start_date_active) start_date_active,
       trunc(end_date_active) end_date_active, role_id
       FROM cn_qm_mgr_groups
       WHERE comp_group_id = l_comp_group_id;

Line: 386

     SELECT srp_id, comp_group_id, trunc(start_date_active) start_date_active,
       trunc(end_date_active) end_date_active, role_id
       FROM cn_qm_srp_groups
       WHERE comp_group_id = l_comp_group_id;

Line: 392

     SELECT 1
       FROM cn_qm_mgr_groups
       WHERE manager_srp_id = p_salesrep_id;

Line: 397

     SELECT parent_comp_group_id
       FROM cn_qm_group_hier
       WHERE comp_group_id = l_comp_group_id
       AND trunc(start_date_active) <= p_effective_date
       AND (trunc(end_date_active) >= p_effective_date
       OR end_date_active IS NULL);

Line: 577

     SELECT srp_role_id, comp_group_id, trunc(start_date_active) start_date_active,
       trunc(end_date_active) end_date_active, role_id, manager_srp_id
       FROM cn_qm_mgr_groups
       WHERE manager_srp_id = p_salesrep_id
       AND comp_group_id = p_comp_group_id;

Line: 584

     SELECT srp_id, comp_group_id, trunc(start_date_active) start_date_active,
       trunc(end_date_active) end_date_active, role_id
       FROM cn_qm_srp_groups
       WHERE comp_group_id = l_comp_group_id;

Line: 733

     SELECT manager_srp_id salesrep_id, role_id ,comp_group_id,
       trunc(start_date_active) start_date_active,
       trunc(end_date_active) end_date_active
       FROM cn_qm_mgr_groups
       WHERE comp_group_id = p_group_id
       AND manager_srp_id <> p_srp.salesrep_id
       AND manager_flag = 'Y';

Line: 742

     SELECT srp_id salesrep_id, role_id, comp_group_id,
       trunc(start_date_active) start_date, trunc(end_date_active) end_date
       FROM cn_qm_mgr_srp_groups
       WHERE comp_group_id = p_group_id;

Line: 835

               l_srp_tbl.DELETE;

Line: 965

     SELECT srp_id salesrep_id, trunc(start_date_active) start_date,
       trunc(end_date_active) end_date, role_id, comp_group_id, manager_flag
       FROM cn_qm_mgr_srp_groups
       WHERE comp_group_id = p_group_id
       ORDER BY manager_flag DESC;

Line: 972

      SELECT manager_flag
        FROM cn_qm_mgr_groups
        WHERE comp_group_id = p_srp.group_id
        AND manager_srp_id = p_srp.salesrep_id
        AND (trunc(start_date_active) <= p_srp.effective_date)
        AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);

Line: 1187

     SELECT
      g.group_id group_id,
        trunc(g.start_date_active) start_date,
        trunc(g.end_date_active) end_date
      FROM jtf_rs_groups_vl g, jtf_rs_group_usages u
      WHERE
          g.group_id = p_group_id
         AND  g.group_id = u.group_id
         AND u.usage='SF_PLANNING'
         AND i_level = 0
      UNION ALL
    SELECT  r.group_id group_id,
        trunc(r.start_date_active) start_date,
        trunc(r.end_date_active) end_date
     FROM jtf_rs_grp_relations r,
      jtf_rs_group_usages u1,
      jtf_rs_group_usages u2
     WHERE
       u1.group_id = r.group_id
      AND u2.group_id = r.related_group_id
      AND u1.usage='SF_PLANNING'
      AND u2.usage='SF_PLANNING'
      AND r.delete_flag = 'N'
      AND r.related_group_id = p_group_id
   ;

Line: 1214

     SELECT DISTINCT jrs.salesrep_id salesrep_id
       FROM jtf_rs_group_members jgm,
            jtf_rs_salesreps_mo_v jrs
       WHERE jgm.group_id = p_group_id
       AND jgm.resource_id = jrs.resource_id
       AND nvl(jgm.delete_flag,'N') = 'N'
     ;

Line: 1223

      SELECT NVL(MAX(manager_srp_id),0) -- will return null if grp has no manager
        FROM cn_qm_mgr_groups
        WHERE comp_group_id = p_group_id
        AND (trunc(start_date_active) <= p_srp.effective_date)
        AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);

Line: 1230

      SELECT manager_flag
        FROM cn_qm_mgr_groups
        WHERE comp_group_id = p_srp.group_id
        AND manager_srp_id = p_srp.salesrep_id
        AND (trunc(start_date_active) <= p_srp.effective_date)
        AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);

Line: 1414

     SELECT
      g.group_id group_id,
        trunc(g.start_date_active) start_date,
        trunc(g.end_date_active) end_date
      FROM jtf_rs_groups_vl g, jtf_rs_group_usages u
      WHERE
          g.group_id = p_group_id
         AND  g.group_id = u.group_id
         AND u.usage='SF_PLANNING'
         AND i_level = 0
      UNION ALL
    SELECT  r.group_id group_id,
        trunc(r.start_date_active) start_date,
        trunc(r.end_date_active) end_date
     FROM jtf_rs_grp_relations r,
      jtf_rs_group_usages u1,
      jtf_rs_group_usages u2
     WHERE
       u1.group_id = r.group_id
      AND u2.group_id = r.related_group_id
      AND u1.usage='SF_PLANNING'
      AND u2.usage='SF_PLANNING'
      AND r.delete_flag = 'N'
      AND r.related_group_id = p_group_id
   ;

Line: 1441

     SELECT DISTINCT jrs.salesrep_id salesrep_id
       FROM jtf_rs_group_members jgm,
            jtf_rs_salesreps_mo_v jrs
       WHERE jgm.group_id = p_group_id
       AND jgm.resource_id = jrs.resource_id
       AND nvl(jgm.delete_flag,'N') = 'N'
     ;

Line: 1450

      SELECT NVL(MAX(manager_srp_id),0) -- will return null if grp has no manager
        FROM cn_qm_mgr_groups
        WHERE comp_group_id = p_group_id
        AND (trunc(start_date_active) <= p_srp.effective_date)
        AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);

Line: 1457

      SELECT manager_flag
        FROM cn_qm_mgr_groups
        WHERE comp_group_id = p_srp.group_id
        AND manager_srp_id = p_srp.salesrep_id
        AND (trunc(start_date_active) <= p_srp.effective_date)
        AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);

Line: 1467

      SELECT NVL(MAX(jrs.salesrep_id),0) manager_srp_id
        FROM
         jtf_rs_groups_vl jg, jtf_rs_role_relations  jrr, jtf_rs_salesreps jrs,
         jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u,
         cn_srp_role_dtls srd
        WHERE
          jg.group_id = jgm.group_id
          and jgm.manager_flag = 'Y'
          and jrs.resource_id = jgm.resource_id
          and u.group_id = jgm.group_id
          and u.usage = 'SF_PLANNING'
          and jrr.role_resource_type = 'RS_INDIVIDUAL'
          and jrr.role_resource_id = jrs.resource_id
          and jrr.role_id = jgm.role_id
          and jrr.role_id = jr.role_id
          and jr.role_type_code = 'SALES_COMP'
          and jrr.delete_flag <> 'Y'
          and jrr.start_date_active <= jgm.start_date_active
          and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
          AND jrs.SALESREP_ID > 0
          AND srd.srp_role_id = jrr.role_relate_id
          AND jgm.group_id = p_srp.group_id
          AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
          AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date);

Line: 1493

      SELECT jgm.manager_flag manager_flag
        FROM
         jtf_rs_groups_vl jg, jtf_rs_role_relations  jrr, jtf_rs_salesreps jrs,
         jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u,
         cn_srp_role_dtls srd
        WHERE
          jg.group_id = jgm.group_id
          and jgm.manager_flag = 'Y'
          and jrs.resource_id = jgm.resource_id
          and u.group_id = jgm.group_id
          and u.usage = 'SF_PLANNING'
          and jrr.role_resource_type = 'RS_INDIVIDUAL'
          and jrr.role_resource_id = jrs.resource_id
          and jrr.role_id = jgm.role_id
          and jrr.role_id = jr.role_id
          and jr.role_type_code = 'SALES_COMP'
          and jrr.delete_flag <> 'Y'
          and jrr.start_date_active <= jgm.start_date_active
          and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
          AND jrs.SALESREP_ID > 0
          AND srd.srp_role_id = jrr.role_relate_id
          AND jgm.group_id = p_srp.group_id
          AND jrs.salesrep_id = p_srp.salesrep_id
          AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
          AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date);

Line: 1554

      OPEN mgr_check_rc FOR SELECT jgm.manager_flag manager_flag
         FROM
            jtf_rs_groups_vl jg, jtf_rs_role_relations  jrr, jtf_rs_salesreps jrs,
             jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u,
             cn_srp_role_dtls srd
         WHERE
          jg.group_id = jgm.group_id
          and jgm.manager_flag = 'Y'
          and jrs.resource_id = jgm.resource_id
          and u.group_id = jgm.group_id
          and u.usage = 'SF_PLANNING'
          and jrr.role_resource_type = 'RS_INDIVIDUAL'
          and jrr.role_resource_id = jrs.resource_id
          and jrr.role_id = jgm.role_id
          and jrr.role_id = jr.role_id
          and jr.role_type_code = 'SALES_COMP'
          and jrr.delete_flag <> 'Y'
          and jrr.start_date_active <= jgm.start_date_active
          and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
          AND jrs.SALESREP_ID > 0
          AND srd.srp_role_id = jrr.role_relate_id
          AND jgm.group_id = p_srp.group_id
          AND jrs.salesrep_id = p_srp.salesrep_id
          AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
          AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date);

Line: 1580

      OPEN mgr_check_rc FOR SELECT manager_flag
        FROM cn_qm_mgr_groups
        WHERE comp_group_id = p_srp.group_id
        AND manager_srp_id = p_srp.salesrep_id
        AND (trunc(start_date_active) <= p_srp.effective_date)
        AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);

Line: 1619

            OPEN grp_mgr_rc FOR SELECT NVL(MAX(jrs.salesrep_id),0) manager_srp_id
                 FROM
                 jtf_rs_groups_vl jg, jtf_rs_role_relations  jrr, jtf_rs_salesreps jrs,
                 jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u,
                 cn_srp_role_dtls srd
                WHERE
                  jg.group_id = jgm.group_id
                  and jgm.manager_flag = 'Y'
                  and jrs.resource_id = jgm.resource_id
                  and u.group_id = jgm.group_id
                  and u.usage = 'SF_PLANNING'
                  and jrr.role_resource_type = 'RS_INDIVIDUAL'
                  and jrr.role_resource_id = jrs.resource_id
                  and jrr.role_id = jgm.role_id
                  and jrr.role_id = jr.role_id
                  and jr.role_type_code = 'SALES_COMP'
                  and jrr.delete_flag <> 'Y'
                  and jrr.start_date_active <= jgm.start_date_active
                  and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
                  AND jrs.SALESREP_ID > 0
                  AND srd.srp_role_id = jrr.role_relate_id
                  AND jgm.group_id = eachgroup.group_id
                  AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
                  AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date);

Line: 1644

               OPEN grp_mgr_rc FOR SELECT NVL(MAX(manager_srp_id),0)
               FROM cn_qm_mgr_groups
               WHERE comp_group_id = eachgroup.group_id
               AND (trunc(start_date_active) <= p_srp.effective_date)
               AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);

Line: 1806

     SELECT srd.srp_role_id, srd.srp_id, job_title_id, overlay_flag, non_std_flag, srd.role_id role_id,
       g.role_name role_name, job_discretion, status, plan_activate_status, club_eligible_flag,
       org_code, trunc(start_date) start_date, trunc(end_date) end_date, g.comp_group_id group_id
       FROM cn_qm_mgr_srp_groups g,
            cn_srp_role_dtls_v srd
       WHERE g.comp_group_id = p_group_id
        AND g.srp_role_id = srd.srp_role_id
        AND (trunc(srd.start_date) <= p_srp.effective_date)
        AND (srd.end_date IS NULL OR trunc(srd.end_date) >= p_srp.effective_date)
        AND (trunc(g.start_date_active) <= p_srp.effective_date)
        AND (g.end_date_active IS NULL OR trunc(g.end_date_active) >= p_srp.effective_date)
        AND srd.job_title_id <> -99
       ORDER BY g.manager_flag DESC;

Line: 1821

      SELECT manager_flag
        FROM cn_qm_mgr_groups
        WHERE comp_group_id = p_srp.group_id
        AND manager_srp_id = p_srp.salesrep_id
        AND (trunc(start_date_active) <= p_srp.effective_date)
        AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);

Line: 2026

     SELECT srd.srp_role_id, srd.srp_id, job_title_id, overlay_flag, non_std_flag, srd.role_id role_id,
       g.role_name role_name, job_discretion, status, plan_activate_status, club_eligible_flag,
       org_code, trunc(start_date) start_date, trunc(end_date) end_date, g.comp_group_id group_id
       FROM cn_qm_mgr_srp_groups g,
            cn_srp_role_dtls_v srd
       WHERE g.comp_group_id = p_group_id
        AND g.srp_role_id = srd.srp_role_id
        AND (trunc(srd.start_date) <= p_srp.effective_date)
        AND (srd.end_date IS NULL OR trunc(srd.end_date) >= p_srp.effective_date)
        AND (trunc(g.start_date_active) <= p_srp.effective_date)
        AND (g.end_date_active IS NULL OR trunc(g.end_date_active) >= p_srp.effective_date)
        AND srd.job_title_id <> -99
       ORDER BY g.manager_flag DESC;

Line: 2041

      SELECT manager_flag
        FROM cn_qm_mgr_groups
        WHERE comp_group_id = p_srp.group_id
        AND manager_srp_id = p_srp.salesrep_id
        AND (trunc(start_date_active) <= p_srp.effective_date)
        AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);

Line: 2050

     SELECT srdd.srp_role_id, srdd.srp_id, srdd.job_title_id, srdd.overlay_flag, srdd.non_std_flag,
            srdd.role_id role_id,srdd.job_discretion, srdd.status, srdd.plan_activate_status, srdd.club_eligible_flag,
            srdd.org_code,trunc(srdd.start_date) start_date, trunc(srdd.end_date) end_date,
            jgm.role_name role_name,jgm.group_id group_id
       FROM
            jtf_rs_groups_vl jg, jtf_rs_role_relations  jrr, jtf_rs_salesreps jrs,
            jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u ,
            cn_srp_role_dtls_v srdd
       WHERE
          jg.group_id = jgm.group_id
          and (jgm.manager_flag = 'Y' or jgm.member_flag = 'Y')
          and jrs.resource_id = jgm.resource_id
          and u.group_id = jgm.group_id
          and u.usage = 'SF_PLANNING'
          and jrr.role_resource_type = 'RS_INDIVIDUAL'
          and jrr.role_resource_id = jrs.resource_id
          and jrr.role_id = jgm.role_id
          and jrr.role_id = jr.role_id
          and jr.role_type_code = 'SALES_COMP'
          and jrr.delete_flag <> 'Y'
          and jrr.start_date_active <= jgm.start_date_active
          and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
          AND jrs.SALESREP_ID > 0
          AND srdd.srp_role_id = jrr.role_relate_id
          AND jgm.group_id = p_group_id -- Added before this.
          AND jrr.role_relate_id = srdd.srp_role_id
          AND (trunc(srdd.start_date) <= p_srp.effective_date)
          AND (srdd.end_date IS NULL OR trunc(srdd.end_date) >= p_srp.effective_date)
          AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
          AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date)
          AND srdd.job_title_id <> -99
       ORDER BY jgm.manager_flag DESC;

Line: 2085

      SELECT jgm.manager_flag manager_flag
        FROM
         jtf_rs_groups_vl jg, jtf_rs_role_relations  jrr, jtf_rs_salesreps jrs,
         jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u,
         cn_srp_role_dtls srd
        WHERE
          jg.group_id = jgm.group_id
          and jgm.manager_flag = 'Y'
          and jrs.resource_id = jgm.resource_id
          and u.group_id = jgm.group_id
          and u.usage = 'SF_PLANNING'
          and jrr.role_resource_type = 'RS_INDIVIDUAL'
          and jrr.role_resource_id = jrs.resource_id
          and jrr.role_id = jgm.role_id
          and jrr.role_id = jr.role_id
          and jr.role_type_code = 'SALES_COMP'
          and jrr.delete_flag <> 'Y'
          and jrr.start_date_active <= jgm.start_date_active
          and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
          AND jrs.SALESREP_ID > 0
          AND srd.srp_role_id = jrr.role_relate_id
          AND jgm.group_id = p_srp.group_id
          AND jrs.salesrep_id = p_srp.salesrep_id
          AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
          AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date);

Line: 2151

        SELECT jgm.manager_flag manager_flag
        FROM
         jtf_rs_groups_vl jg, jtf_rs_role_relations  jrr, jtf_rs_salesreps jrs,
         jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u,
         cn_srp_role_dtls srd
        WHERE
          jg.group_id = jgm.group_id
          and jgm.manager_flag = 'Y'
          and jrs.resource_id = jgm.resource_id
          and u.group_id = jgm.group_id
          and u.usage = 'SF_PLANNING'
          and jrr.role_resource_type = 'RS_INDIVIDUAL'
          and jrr.role_resource_id = jrs.resource_id
          and jrr.role_id = jgm.role_id
          and jrr.role_id = jr.role_id
          and jr.role_type_code = 'SALES_COMP'
          and jrr.delete_flag <> 'Y'
          and jrr.start_date_active <= jgm.start_date_active
          and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
          AND jrs.SALESREP_ID > 0
          AND srd.srp_role_id = jrr.role_relate_id
          AND jgm.group_id = p_srp.group_id
          AND jrs.salesrep_id = p_srp.salesrep_id
          AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
          AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date);

Line: 2177

          OPEN mgr_check_rc FOR       SELECT manager_flag
        FROM cn_qm_mgr_groups
        WHERE comp_group_id = p_srp.group_id
        AND manager_srp_id = p_srp.salesrep_id
        AND (trunc(start_date_active) <= p_srp.effective_date)
        AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);

Line: 2196

           OPEN members_csr_rc FOR SELECT srdd.srp_role_id, srdd.srp_id, srdd.job_title_id, srdd.overlay_flag, srdd.non_std_flag,
                srdd.role_id role_id,jgm.role_name role_name,srdd.job_discretion, srdd.status, srdd.plan_activate_status, srdd.club_eligible_flag,
                srdd.org_code,trunc(srdd.start_date) start_date, trunc(srdd.end_date) end_date,
                jgm.group_id group_id
           FROM
            jtf_rs_groups_vl jg, jtf_rs_role_relations  jrr, jtf_rs_salesreps jrs,
            jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u ,
            cn_srp_role_dtls_v srdd
           WHERE
              jg.group_id = jgm.group_id
              and (jgm.manager_flag = 'Y' or jgm.member_flag = 'Y')
              and jrs.resource_id = jgm.resource_id
              and u.group_id = jgm.group_id
              and u.usage = 'SF_PLANNING'
              and jrr.role_resource_type = 'RS_INDIVIDUAL'
              and jrr.role_resource_id = jrs.resource_id
              and jrr.role_id = jgm.role_id
              and jrr.role_id = jr.role_id
              and jr.role_type_code = 'SALES_COMP'
              and jrr.delete_flag <> 'Y'
              and jrr.start_date_active <= jgm.start_date_active
              and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
              AND jrs.SALESREP_ID > 0
              AND srdd.srp_role_id = jrr.role_relate_id
              AND jgm.group_id = p_srp.group_id -- Added before this.
              AND jrr.role_relate_id = srdd.srp_role_id
              AND (trunc(srdd.start_date) <= p_srp.effective_date)
              AND (srdd.end_date IS NULL OR trunc(srdd.end_date) >= p_srp.effective_date)
              AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
              AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date)
              AND srdd.job_title_id <> -99
              ORDER BY jgm.manager_flag DESC;

Line: 2229

           OPEN members_csr_rc FOR SELECT srd.srp_role_id, srd.srp_id, job_title_id, overlay_flag, non_std_flag, srd.role_id role_id,
               g.role_name role_name, job_discretion, status, plan_activate_status, club_eligible_flag,
               org_code, trunc(start_date) start_date, trunc(end_date) end_date, g.comp_group_id group_id
           FROM cn_qm_mgr_srp_groups g,
                cn_srp_role_dtls_v srd
           WHERE g.comp_group_id = p_srp.group_id
                AND g.srp_role_id = srd.srp_role_id
                AND (trunc(srd.start_date) <= p_srp.effective_date)
                AND (srd.end_date IS NULL OR trunc(srd.end_date) >= p_srp.effective_date)
                AND (trunc(g.start_date_active) <= p_srp.effective_date)
                AND (g.end_date_active IS NULL OR trunc(g.end_date_active) >= p_srp.effective_date)
                AND srd.job_title_id <> -99
               ORDER BY g.manager_flag DESC;

Line: 2309

               OPEN members_csr_rc FOR SELECT srdd.srp_role_id, srdd.srp_id, srdd.job_title_id, srdd.overlay_flag, srdd.non_std_flag,
                srdd.role_id role_id,jgm.role_name role_name,srdd.job_discretion, srdd.status, srdd.plan_activate_status, srdd.club_eligible_flag,
                srdd.org_code,trunc(srdd.start_date) start_date, trunc(srdd.end_date) end_date,
                jgm.group_id group_id
               FROM
                jtf_rs_groups_vl jg, jtf_rs_role_relations  jrr, jtf_rs_salesreps jrs,
                jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u ,
                cn_srp_role_dtls_v srdd
               WHERE
                  jg.group_id = jgm.group_id
                  and (jgm.manager_flag = 'Y' or jgm.member_flag = 'Y')
                  and jrs.resource_id = jgm.resource_id
                  and u.group_id = jgm.group_id
                  and u.usage = 'SF_PLANNING'
                  and jrr.role_resource_type = 'RS_INDIVIDUAL'
                  and jrr.role_resource_id = jrs.resource_id
                  and jrr.role_id = jgm.role_id
                  and jrr.role_id = jr.role_id
                  and jr.role_type_code = 'SALES_COMP'
                  and jrr.delete_flag <> 'Y'
                  and jrr.start_date_active <= jgm.start_date_active
                  and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
                  AND jrs.SALESREP_ID > 0
                  AND srdd.srp_role_id = jrr.role_relate_id
                  AND jgm.group_id = l_descendant_group(eachgroup).group_id -- Added before this.
                  AND jrr.role_relate_id = srdd.srp_role_id
                  AND (trunc(srdd.start_date) <= p_srp.effective_date)
                  AND (srdd.end_date IS NULL OR trunc(srdd.end_date) >= p_srp.effective_date)
                  AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
                  AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date)
                  AND srdd.job_title_id <> -99
                  ORDER BY jgm.manager_flag DESC;

Line: 2342

                   OPEN members_csr_rc FOR SELECT srd.srp_role_id, srd.srp_id, job_title_id, overlay_flag, non_std_flag, srd.role_id role_id,
                   g.role_name role_name, job_discretion, status, plan_activate_status, club_eligible_flag,
                   org_code, trunc(start_date) start_date, trunc(end_date) end_date, g.comp_group_id group_id
                   FROM cn_qm_mgr_srp_groups g,
                    cn_srp_role_dtls_v srd
                   WHERE g.comp_group_id = l_descendant_group(eachgroup).group_id
                    AND g.srp_role_id = srd.srp_role_id
                    AND (trunc(srd.start_date) <= p_srp.effective_date)
                    AND (srd.end_date IS NULL OR trunc(srd.end_date) >= p_srp.effective_date)
                    AND (trunc(g.start_date_active) <= p_srp.effective_date)
                    AND (g.end_date_active IS NULL OR trunc(g.end_date_active) >= p_srp.effective_date)
                    AND srd.job_title_id <> -99
                   ORDER BY g.manager_flag DESC;

Line: 2485

     SELECT 1
       FROM cn_qm_mgr_groups
       WHERE manager_srp_id = p_srp.salesrep_id;

Line: 2490

     SELECT parent_comp_group_id
       FROM cn_qm_group_hier
       WHERE comp_group_id = p_group_id
       AND trunc(start_date_active) <= p_srp.effective_date
       AND (trunc(end_date_active) >= p_srp.effective_date
       OR end_date_active IS NULL);

Line: 2498

     SELECT manager_srp_id salesrep_id, role_id ,comp_group_id,
       trunc(start_date_active) start_date_active,
       trunc(end_date_active) end_date_active
       FROM cn_qm_mgr_groups
       WHERE comp_group_id = p_group_id
       AND manager_srp_id <> p_srp.salesrep_id
       AND manager_flag = 'Y';