APPS.CN_COMP_GRP_HIER_PUB SQL Statements

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

Line: 6

   G_LAST_UPDATE_DATE          DATE    := sysdate;

Line: 7

   G_LAST_UPDATED_BY           NUMBER  := fnd_global.user_id;

Line: 10

   G_LAST_UPDATE_LOGIN         NUMBER  := fnd_global.login_id;

Line: 46

   SELECT hier.comp_group_id group_id, cg.name group_name,
          hier.start_date_active,hier.end_date_active
     FROM cn_comp_group_hier hier,
          cn_comp_groups cg
    WHERE hier.parent_comp_group_id = l_parent_cg_id
      AND hier.comp_group_id = cg.comp_group_id
      AND hier.comp_group_id <> DECODE(p_focus_cg_id,'0',p_comp_group_id,p_focus_cg_id)
      AND delete_flag <> 'Y'
      AND hier.start_date_active <= l_date
      AND ((hier.end_date_active is null) OR
           (hier.end_date_active >= l_date));

Line: 62

   SELECT hier.comp_group_id group_id, cg.name group_name,
          hier.start_date_active,hier.end_date_active
     FROM cn_comp_group_hier hier,
          cn_comp_groups cg
    WHERE hier.parent_comp_group_id = l_parent_cg_id
      AND hier.comp_group_id = cg.comp_group_id
      AND delete_flag <> 'Y'
      AND hier.start_date_active <= l_date
      AND ((hier.end_date_active is null) OR
           (hier.end_date_active >= l_date));

Line: 78

   SELECT cs.name mgr_name,
       	  cs.employee_number mgr_number,
	  cs.salesrep_id salesrep_id,
       	  cscg.role_name mgr_role,
       	  cscg.start_date_active mgr_start_date,
       	  cscg.end_date_active mgr_end_date,
	  cscg.role_id mgr_role_id,
	  cscg.srp_role_id mgr_srp_role_id
     FROM cn_srp_comp_groups_v cscg,
          cn_salesreps cs
    WHERE cscg.comp_group_id = l_comp_group_id
      AND cscg.salesrep_id = cs.salesrep_id
      AND cscg.start_date_active <= l_date
      AND ((cscg.end_date_active is null) OR
           (cscg.end_date_active >= l_date));

Line: 110

   x_loading_status 	:= 'CN_INSERTED';

Line: 117

   SELECT count(*)
     INTO l_counter
     FROM cn_comp_groups cg, cn_srp_comp_groups_v cscg
    WHERE cg.comp_group_id = p_comp_group_id
      AND cg.start_date_active <= p_date
      AND ((cg.end_date_active is null) OR
	   (cg.end_date_active >= p_date))
      AND  cscg.comp_group_id= cg.comp_group_id
      and cscg.salesrep_id = p_salesrep_id
      and cscg.start_date_active <= p_date
      AND ((cscg.end_date_active is null) OR
	   (cscg.end_date_active >= p_date));

Line: 135

      SELECT period_year
        INTO x_period_year
        FROM cn_repositories r, cn_period_statuses ps
       WHERE r.period_type_id 	= ps.period_type_id
         AND r.period_set_id 	= ps.period_set_id
         AND period_status IN ('O','C')
         AND p_date BETWEEN start_date AND end_date;

Line: 168

	    SELECT comp_group_id,
	           parent_comp_group_id,
		   start_date_active,
		   end_date_active
              INTO l_top_hier_tbl(l_counter).cg_salesrep_id,
		   l_top_hier_tbl(l_counter).parent_comp_group_id,
		   l_top_hier_tbl(l_counter).start_date_active,
		   l_top_hier_tbl(l_counter).end_date_active
              FROM cn_comp_group_hier cgh
	     WHERE comp_group_id = l_comp_group_id
	       AND cgh.start_date_active <= p_date
	       AND ((cgh.end_date_active is null) OR
		    (cgh.end_date_active >= p_date));

Line: 188

	          SELECT comp_group_id,
	                 NULL,
		         start_date_active,
		         end_date_active
                    INTO l_top_hier_tbl(l_counter).cg_salesrep_id,
		         l_top_hier_tbl(l_counter).parent_comp_group_id,
		         l_top_hier_tbl(l_counter).start_date_active,
		         l_top_hier_tbl(l_counter).end_date_active
                    FROM cn_comp_groups cg
	           WHERE comp_group_id = l_comp_group_id
	             AND cg.start_date_active <= p_date
	             AND ((cg.end_date_active is null) OR
		          (cg.end_date_active >= p_date));

Line: 223

         SELECT name
	   INTO x_mgr_tbl(l_out_counter).cg_salesrep_name
	   FROM cn_comp_groups
	  WHERE comp_group_id = l_top_hier_tbl(l_counter).cg_salesrep_id;