APPS.JTF_RS_ROLES_VUHK SQL Statements

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

Line: 60

PROCEDURE  update_rs_resource_roles_pre
  (P_ROLE_ID          	     IN      JTF_RS_ROLES_B.ROLE_ID%TYPE,
   P_ROLE_TYPE_CODE          IN      JTF_RS_ROLES_B.ROLE_TYPE_CODE%TYPE,
   P_ROLE_CODE               IN      JTF_RS_ROLES_B.ROLE_CODE%TYPE,
   P_ROLE_NAME               IN      JTF_RS_ROLES_TL.ROLE_NAME%TYPE,
   P_ROLE_DESC               IN      JTF_RS_ROLES_TL.ROLE_DESC%TYPE,
   P_ACTIVE_FLAG             IN      JTF_RS_ROLES_B.ACTIVE_FLAG%TYPE,
   P_SEEDED_FLAG             IN      JTF_RS_ROLES_B.SEEDED_FLAG%TYPE,
   P_MEMBER_FLAG             IN      JTF_RS_ROLES_B.MEMBER_FLAG%TYPE,
   P_ADMIN_FLAG              IN      JTF_RS_ROLES_B.ADMIN_FLAG%TYPE,
   P_LEAD_FLAG               IN      JTF_RS_ROLES_B.LEAD_FLAG%TYPE,
   P_MANAGER_FLAG            IN      JTF_RS_ROLES_B.MANAGER_FLAG%TYPE,
   X_RETURN_STATUS           OUT     NOCOPY VARCHAR2,
   X_MSG_COUNT               OUT     NOCOPY NUMBER,
   X_MSG_DATA                OUT     NOCOPY VARCHAR2) IS

   l_manager_flag           jtf_rs_roles_b.manager_flag%TYPE;

Line: 81

   l_api_name               VARCHAR2(30) := 'update_rs_resource_roles_pre';

Line: 96

   SELECT manager_flag, member_flag
     FROM jtf_rs_roles_b
    WHERE role_id = p_role_id
      AND role_type_code = 'SALES_COMP';

Line: 102

   SELECT cscg.salesrep_id,
          cscg.comp_group_id,
          intel.period_id,
          greatest(cscg.start_date_active, intel.start_date) start_date,
          decode(cscg.end_date_active, null, intel.end_date,
		 Least(cscg.end_date_active, intel.end_date)) end_date
     FROM cn_srp_comp_groups_v cscg,
          cn_srp_intel_periods intel
    WHERE cscg.role_id = p_role_id
      and intel.salesrep_id = cscg.salesrep_id
      and cscg.start_date_active <= intel.end_date
      and (cscg.end_date_active is null or
	   cscg.end_date_active >= intel.start_date);

Line: 118

        select distinct ct.name name,
               ct.comp_team_id team_id,
               greatest(scg.start_date_active, ct.start_date_active) start_date,
               Least(nvl(ct.end_date_active, l_max_date) , nvl(scg.end_date_active, l_max_date)) end_date
        from cn_srp_comp_teams_v srt, cn_comp_teams ct, cn_srp_comp_groups_v scg
        where scg.role_id = p_role_id
          and srt.salesrep_id = scg.salesrep_id
          and srt.comp_team_id = ct.comp_team_id
          and (scg.start_date_active <= ct.start_date_active
            or scg.start_date_active between ct.start_date_active and nvl (ct.end_date_active, scg.start_date_active))
          and nvl(scg.end_date_active, ct.start_date_active) >= ct.start_date_active;

Line: 131

       SELECT org_id
	 FROM cn_repositories_all
	WHERE status = 'A';

Line: 193

	    -- log event for the update of the manager_flag
	    cn_mark_events_pkg.log_event
	      (p_event_name      => 'CHANGE_CP_DELETE_MGR',
	       p_object_name     => p_role_name,
	       p_object_id       => p_role_id,
	       p_start_date      => NULL,
	       p_start_date_old  => NULL,
	       p_end_date        => NULL,
	       p_end_date_old    => NULL,
	       x_event_log_id    => l_event_log_id,
	       p_org_id          => o.org_id);

Line: 213

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

Line: 224

	    -- log event for the update of the manager_flag
	    cn_mark_events_pkg.log_event
	      (p_event_name      => 'CHANGE_CP_ADD_MGR',
	       p_object_name     => p_role_name,
	       p_object_id       => p_role_id,
	       p_start_date      => NULL,
	       p_start_date_old  => NULL,
	       p_end_date        => NULL,
	       p_end_date_old    => NULL,
	       x_event_log_id    => l_event_log_id,
	       p_org_id          => o.org_id);

Line: 297

END update_rs_resource_roles_pre;

Line: 303

PROCEDURE  update_rs_resource_roles_post
  (P_ROLE_ID                 IN      JTF_RS_ROLES_B.ROLE_ID%TYPE,
   P_ROLE_TYPE_CODE          IN      JTF_RS_ROLES_B.ROLE_TYPE_CODE%TYPE,
   P_ROLE_CODE               IN      JTF_RS_ROLES_B.ROLE_CODE%TYPE,
   P_ROLE_NAME               IN      JTF_RS_ROLES_TL.ROLE_NAME%TYPE,
   P_ROLE_DESC               IN      JTF_RS_ROLES_TL.ROLE_DESC%TYPE,
   P_ACTIVE_FLAG             IN      JTF_RS_ROLES_B.ACTIVE_FLAG%TYPE,
   P_SEEDED_FLAG             IN      JTF_RS_ROLES_B.SEEDED_FLAG%TYPE,
   P_MEMBER_FLAG             IN      JTF_RS_ROLES_B.MEMBER_FLAG%TYPE,
   P_ADMIN_FLAG              IN      JTF_RS_ROLES_B.ADMIN_FLAG%TYPE,
   P_LEAD_FLAG               IN      JTF_RS_ROLES_B.LEAD_FLAG%TYPE,
   P_MANAGER_FLAG            IN      JTF_RS_ROLES_B.MANAGER_FLAG%TYPE,
   X_RETURN_STATUS           OUT     NOCOPY VARCHAR2,
   X_MSG_COUNT               OUT     NOCOPY NUMBER,
   X_MSG_DATA                OUT     NOCOPY VARCHAR2) IS
BEGIN
   x_return_status := fnd_api.g_ret_sts_success;

Line: 320

END update_rs_resource_roles_post;

Line: 325

PROCEDURE  delete_rs_resource_roles_pre
  (P_ROLE_ID                 IN      JTF_RS_ROLES_B.ROLE_ID%TYPE,
   P_ROLE_CODE               IN      JTF_RS_ROLES_B.ROLE_CODE%TYPE,
   X_RETURN_STATUS           OUT     NOCOPY VARCHAR2,
   X_MSG_COUNT               OUT     NOCOPY NUMBER,
   X_MSG_DATA                OUT     NOCOPY VARCHAR2) IS

   -- for API call to CN role details
   l_return_status              VARCHAR2(1);

Line: 340

END delete_rs_resource_roles_pre;

Line: 345

PROCEDURE  delete_rs_resource_roles_post
  (P_ROLE_ID                 IN      JTF_RS_ROLES_B.ROLE_ID%TYPE,
   P_ROLE_CODE               IN      JTF_RS_ROLES_B.ROLE_CODE%TYPE,
   X_RETURN_STATUS           OUT     NOCOPY VARCHAR2,
   X_MSG_COUNT               OUT     NOCOPY NUMBER,
   X_MSG_DATA                OUT     NOCOPY VARCHAR2) IS
BEGIN
   x_return_status := fnd_api.g_ret_sts_success;

Line: 353

END delete_rs_resource_roles_post;