eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.JTF_RS_GROUP_RELATE_VUHK SQL Statements

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

Line: 36


  PROCEDURE  update_res_group_relate_pre
  (P_GROUP_RELATE_ID      IN   JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE,
   P_START_DATE_ACTIVE    IN   JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE   DEFAULT  FND_API.G_MISS_DATE,
   P_END_DATE_ACTIVE      IN   JTF_RS_GRP_RELATIONS.END_DATE_ACTIVE%TYPE   DEFAULT  FND_API.G_MISS_DATE,
   P_OBJECT_VERSION_NUM   IN   JTF_RS_GRP_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
   P_DATA                   OUT  NOCOPY VARCHAR2,
   P_COUNT            OUT  NOCOPY NUMBER,
   P_RETURN_CODE            OUT  NOCOPY VARCHAR2
   ) IS
      l_start_date        DATE;

Line: 55


      l_api_name          VARCHAR2(30) := 'update_res_group_relate_pre';

Line: 66


	 SELECT r.start_date_active, r.end_date_active, r.group_id
	   FROM jtf_rs_group_usages u,
	        jtf_rs_grp_relations r
	   WHERE r.group_relate_id = p_group_relate_id
	     AND u.group_id = r.group_id AND u.usage = 'SALES_COMP';

Line: 74


	 SELECT name
	   FROM cn_comp_groups
	   WHERE comp_group_id = g_group_id;

Line: 80


	 SELECT p.period_id,
	        greatest(p_start_date, p.start_date) start_date,
	        Decode(p_end_date, NULL, p.end_date, Least(p_end_date, p.end_date)) end_date
	   FROM cn_srp_intel_periods p
	  WHERE p.salesrep_id = p_salesrep_id
	    AND (p_end_date IS NULL OR p.start_date <= p_end_date)
	    AND (p.end_date >= p_start_date);

Line: 90


         select distinct ct.name name,
                ct.comp_team_id team_id,
                greatest(p_start_date, cg.start_date_active, ct.start_date_active) start_date,
                least(nvl(ct.end_date_active, l_max_date), nvl(cg.end_date_active, l_max_date), nvl(p_end_date, l_max_date)) end_date
         from cn_srp_comp_teams_v srt, cn_comp_teams ct, cn_srp_comp_groups_v cg
         where (p_salesrep_id IS NULL or srt.salesrep_id = p_salesrep_id)
           and (p_salesrep_id IS NULL or cg.salesrep_id = p_salesrep_id)
           and cg.comp_group_id = p_group_id
           and srt.comp_team_id = ct.comp_team_id
           and (cg.start_date_active <= ct.start_date_active
             or cg.start_date_active between ct.start_date_active and nvl (ct.end_date_active, cg.start_date_active))
           and nvl(cg.end_date_active, ct.start_date_active) >= ct.start_date_active
           and (cg.end_date_active IS NULL OR p_start_date <= cg.end_date_active)
  	       and (p_end_date IS NULL OR p_end_date >= cg.start_date_active);

Line: 106


	SELECT org_id FROM cn_repositories_all WHERE status = 'A';

Line: 151


	      -- delete the period (g_start_date_old, p_start_date_active) which is not active any more
	      IF (p_start_date_active > g_start_date_old) THEN
		 IF (g_end_date_old IS NOT NULL AND g_end_date_old < p_start_date_active) THEN
		    l_end_date := g_end_date_old;

Line: 182


			p_action             => 'DELETE_ROLL_PULL',
			p_action_link_id     => NULL,
			p_base_salesrep_id   => NULL,
			p_base_comp_group_id => NULL,
			p_event_log_id       => g_event_log_id,
			x_action_link_id     => l_roll_action_link_id,
			p_org_id             => o.org_id);

Line: 226


		 l_srp_tbl.DELETE;

Line: 229


	      -- delete the period (p_end_date_active, g_end_date_old) which is not active any more
	      IF ((g_end_date_old IS NULL AND p_end_date_active IS NOT NULL) OR p_end_date_active < g_end_date_old) THEN
		 IF (p_end_date_active < g_start_date_old) THEN
		    l_start_date := g_start_date_old;

Line: 260


			p_action             => 'DELETE_ROLL_PULL',
			p_action_link_id     => NULL,
			p_base_salesrep_id   => NULL,
			p_base_comp_group_id => NULL,
			p_event_log_id       => g_event_log_id,
			x_action_link_id     => l_roll_action_link_id,
			p_org_id             => o.org_id);

Line: 305


		 l_srp_tbl.DELETE;

Line: 330


  END update_res_group_relate_pre;

Line: 332


  PROCEDURE  delete_res_group_relate_pre
  (P_GROUP_RELATE_ID      IN   JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE,
   P_OBJECT_VERSION_NUM   IN   JTF_RS_GROUPS_VL.OBJECT_VERSION_NUMBER%TYPE,
   P_DATA                 OUT  NOCOPY VARCHAR2,
   P_COUNT                OUT  NOCOPY NUMBER,
   P_RETURN_CODE          OUT  NOCOPY VARCHAR2
   ) IS
      l_event_log_id      NUMBER;

Line: 351


      l_api_name          VARCHAR2(30) := 'delete_res_group_relate_pre';

Line: 361


	 SELECT r.start_date_active, r.end_date_active, r.group_id
	   FROM jtf_rs_group_usages u,
	        jtf_rs_grp_relations r
	   WHERE r.group_relate_id = p_group_relate_id
	     AND u.group_id = r.group_id AND u.usage = 'SALES_COMP';

Line: 369


	 SELECT name
	   FROM cn_comp_groups
	   WHERE comp_group_id = l_group_id;

Line: 375


	 SELECT p.period_id,
	        greatest(p_start_date, p.start_date) start_date,
	        Decode(p_end_date, NULL, p.end_date, Least(p_end_date, p.end_date)) end_date
	   FROM cn_srp_intel_periods p
	  WHERE p.salesrep_id = p_salesrep_id
	    AND (p_end_date IS NULL OR p.start_date <= p_end_date)
	    AND (p.end_date >= p_start_date);

Line: 385


         select distinct ct.name name,
                ct.comp_team_id team_id,
                greatest(p_start_date, cg.start_date_active, ct.start_date_active) start_date,
                Least(nvl(ct.end_date_active, l_max_date), nvl(cg.end_date_active, l_max_date), nvl(p_end_date, l_max_date)) end_date
         from cn_srp_comp_teams_v srt, cn_comp_teams ct, cn_srp_comp_groups_v cg
         where (p_salesrep_id IS NULL or srt.salesrep_id = p_salesrep_id)
           and (p_salesrep_id IS NULL or cg.salesrep_id = p_salesrep_id)
           and cg.comp_group_id = p_group_id
           and srt.comp_team_id = ct.comp_team_id
           and (cg.start_date_active <= ct.start_date_active
             or cg.start_date_active between ct.start_date_active and nvl (ct.end_date_active, cg.start_date_active))
           and nvl(cg.end_date_active, ct.start_date_active) >= ct.start_date_active
           and (cg.end_date_active IS NULL OR p_start_date <= cg.end_date_active)
  	       and (p_end_date IS NULL OR p_end_date >= cg.start_date_active);

Line: 401


	SELECT org_id FROM cn_repositories_all WHERE status = 'A';

Line: 432


		( p_event_name      => 'CHANGE_CP_HIER_DELETE',
		  p_object_name     => l_group_name,
		  p_object_id       => l_group_id,
		  p_start_date      => NULL,
		  p_start_date_old  => l_start_date,
		  p_end_date        => NULL,
		  p_end_date_old    => l_end_date,
		  x_event_log_id    => l_event_log_id,
		  p_org_id          => o.org_id);

Line: 449


		  p_action             => 'DELETE_ROLL_PULL',
		  p_action_link_id     => NULL,
		  p_base_salesrep_id   => NULL,
		  p_base_comp_group_id => NULL,
		  p_event_log_id       => l_event_log_id,
		  x_action_link_id     => p_action_link_id,
		  p_org_id             => o.org_id);

Line: 530


	      l_srp_tbl.DELETE;

Line: 552


  END delete_res_group_relate_pre;

Line: 587


	 SELECT 1
	   FROM jtf_rs_group_usages
	   WHERE group_id = p_group_id AND usage = 'SALES_COMP';

Line: 593


	 SELECT name
	   FROM cn_comp_groups
	  WHERE comp_group_id = p_group_id;

Line: 599


	 SELECT p.period_id,
	        greatest(p_start_date, p.start_date) start_date,
	        Decode(p_end_date, NULL, p.end_date, Least(p_end_date, p.end_date)) end_date
	   FROM cn_srp_intel_periods p
	  WHERE p.salesrep_id = p_salesrep_id
	    AND (p_end_date IS NULL OR p.start_date <= p_end_date)
	    AND (p.end_date >= p_start_date);

Line: 609


         select distinct ct.name name,
                ct.comp_team_id team_id,
                greatest(p_start_date, cg.start_date_active, ct.start_date_active) start_date,
                least(nvl(ct.end_date_active, l_max_date), nvl(cg.end_date_active, l_max_date), nvl(p_end_date, l_max_date)) end_date
         from cn_srp_comp_teams_v srt, cn_comp_teams ct, cn_srp_comp_groups_v cg
         where srt.salesrep_id = p_salesrep_id
           and cg.salesrep_id = p_salesrep_id
           and cg.comp_group_id = p_group_id
           and srt.comp_team_id = ct.comp_team_id
           and (cg.start_date_active <= ct.start_date_active
             or cg.start_date_active between ct.start_date_active and nvl (ct.end_date_active, cg.start_date_active))
           and nvl(cg.end_date_active, ct.start_date_active) >= ct.start_date_active
           and (cg.end_date_active IS NULL OR p_start_date <= cg.end_date_active)
  	       and (p_end_date IS NULL OR p_end_date >= cg.start_date_active);

Line: 625


	SELECT org_id FROM cn_repositories_all WHERE status = 'A';

Line: 755


	      l_srp_tbl.DELETE;

Line: 780


  PROCEDURE  update_res_group_relate_post
  (P_GROUP_RELATE_ID      IN   JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE,
   P_START_DATE_ACTIVE    IN   JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE   DEFAULT  FND_API.G_MISS_DATE,
   P_END_DATE_ACTIVE      IN   JTF_RS_GRP_RELATIONS.END_DATE_ACTIVE%TYPE   DEFAULT  FND_API.G_MISS_DATE,
   P_OBJECT_VERSION_NUM   IN   JTF_RS_GRP_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
   P_DATA                 OUT  NOCOPY VARCHAR2,
   P_COUNT                OUT  NOCOPY NUMBER,
   P_RETURN_CODE          OUT  NOCOPY VARCHAR2
   ) IS
      l_start_date        DATE;

Line: 798


      l_api_name          VARCHAR2(30) := 'update_res_group_relate_post';

Line: 807


	 SELECT p.period_id,
	        greatest(p_start_date, p.start_date) start_date,
	        Decode(p_end_date, NULL, p.end_date, Least(p_end_date, p.end_date)) end_date
	   FROM cn_srp_intel_periods p
	  WHERE p.salesrep_id = p_salesrep_id
	    AND (p_end_date IS NULL OR p.start_date <= p_end_date)
	    AND (p.end_date >= p_start_date);

Line: 817


         select distinct ct.name name,
                ct.comp_team_id team_id,
                greatest(p_start_date, cg.start_date_active, ct.start_date_active) start_date,
                Least(nvl(ct.end_date_active, l_max_date), nvl(cg.end_date_active, l_max_date), nvl(p_end_date, l_max_date) ) end_date
         from cn_srp_comp_teams_v srt, cn_comp_teams ct, cn_srp_comp_groups_v cg
         where srt.salesrep_id = p_salesrep_id
           and cg.salesrep_id = p_salesrep_id
           and cg.comp_group_id = p_group_id
           and srt.comp_team_id = ct.comp_team_id
           and (cg.start_date_active <= ct.start_date_active
             or cg.start_date_active between ct.start_date_active and nvl (ct.end_date_active, cg.start_date_active))
           and nvl(cg.end_date_active, ct.start_date_active) >= ct.start_date_active
           and (cg.end_date_active IS NULL OR p_start_date <= cg.end_date_active)
  	       and (p_end_date IS NULL OR p_end_date >= cg.start_date_active);

Line: 833


	SELECT org_id FROM cn_repositories_all WHERE status = 'A';

Line: 869


	-- insert the period(p_start_date_active, g_start_date_old) which becomes active
	IF (p_start_date_active < g_start_date_old) THEN
	   IF (p_end_date_active IS NOT NULL AND p_end_date_active < g_start_date_old) THEN
	      l_end_date := p_end_date_active;

Line: 929


	   l_srp_tbl.DELETE;

Line: 932


	-- insert the period (l_end_date_old, p_end_date_active) which becomes active.
	IF ((p_end_date_active IS NULL AND g_end_date_old IS NOT NULL) OR p_end_date_active > g_end_date_old) THEN
	   IF (g_end_date_old < p_start_date_active) THEN
	      l_start_date := p_start_date_active;

Line: 992


	   l_srp_tbl.DELETE;

Line: 1013


  END update_res_group_relate_post;

Line: 1015


  PROCEDURE  delete_res_group_relate_post
  (P_GROUP_RELATE_ID      IN   JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE,
   P_OBJECT_VERSION_NUM   IN   JTF_RS_GROUPS_VL.OBJECT_VERSION_NUMBER%TYPE,
   P_DATA                 OUT  NOCOPY VARCHAR2,
   P_COUNT                OUT  NOCOPY NUMBER,
   P_RETURN_CODE          OUT  NOCOPY VARCHAR2
   ) IS
  BEGIN
     p_return_code := fnd_api.g_ret_sts_success;

Line: 1024


  END delete_res_group_relate_post;