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.CN_SRP_PAYEE_ASSIGNS_PVT SQL Statements

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

Line: 8


PROCEDURE delete_trigger
  (old_salesrep_id         NUMBER,
   old_srp_quota_assign_id NUMBER,
   old_start_date          DATE,
   old_end_date            DATE,
   old_org_id              NUMBER) IS

   x_salesrep_name cn_salesreps.name%TYPE;

Line: 18


      SELECT name
        INTO x_salesrep_name
        FROM cn_salesreps
       WHERE salesrep_id = old_salesrep_id
	 AND org_id      = old_org_id;

Line: 35


END delete_trigger;

Line: 37


PROCEDURE insert_trigger
  (new_salesrep_id         NUMBER,
   new_srp_quota_assign_id NUMBER,
   new_start_date          DATE,
   new_end_date            DATE,
   new_org_id              NUMBER) IS

      x_salesrep_name cn_salesreps.name%TYPE;

Line: 47


      SELECT name
	INTO x_salesrep_name
	FROM cn_salesreps
       WHERE salesrep_id = new_salesrep_id
	 AND org_id      = new_org_id;

Line: 64


END insert_trigger;

Line: 66


PROCEDURE update_trigger
  (old_salesrep_id     NUMBER,
   old_payee_id        NUMBER,
   old_start_date      DATE,
   old_end_date        DATE,
   new_srp_quota_assign_id NUMBER,
   new_salesrep_id     NUMBER,
   new_payee_id        NUMBER,
   new_start_date      DATE,
   new_end_date        DATE,
   new_org_id          NUMBER) IS

      x_salesrep_name cn_salesreps.name%TYPE;

Line: 81


      SELECT name
        INTO x_salesrep_name
        FROM cn_salesreps
       WHERE salesrep_id = new_salesrep_id
	 AND org_id      = new_org_id;

Line: 118


END update_trigger;

Line: 146


   SELECT count(1)
     INTO l_count
     FROM cn_quotas_all
    WHERE quota_id = p_quota_id
      AND p_start_date >= start_date
      AND Nvl(p_end_date, l_end_of_time) <=
          Nvl(end_date,   l_end_of_time);

Line: 166


   SELECT COUNT(1)
     INTO l_count
     FROM cn_srp_quota_assigns sqa, cn_srp_plan_assigns spa
    WHERE sqa.srp_quota_assign_id = p_srp_quota_assign_id
      AND sqa.srp_plan_assign_id = spa.srp_plan_assign_id
      AND p_start_date >= start_date
      AND Nvl(p_end_date, l_end_of_time) <=
          Nvl(end_date,   l_end_of_time);

Line: 187


   SELECT count(1)
     INTO l_count
     FROM cn_srp_payee_assigns_all
    WHERE srp_quota_assign_id = p_srp_quota_assign_id
      AND delete_flag = 'N'
      AND srp_payee_assign_id <> Nvl(p_srp_payee_assign_id, -1)
      AND Greatest(start_date,  p_start_date) <=
          Least(Nvl(end_date,   l_end_of_time),
		Nvl(p_end_date, l_end_of_time));

Line: 244


   SELECT start_date, nvl(end_date,l_end_of_time) end_date
     FROM cn_srp_pay_groups_all
    WHERE salesrep_id = p_payee_id
      AND org_id = p_org_id;

Line: 250


   SELECT start_date, nvl(end_date,l_end_of_time) end_date
     FROM cn_srp_roles
    WHERE salesrep_id = p_payee_id
      AND org_id      = p_org_id
      AND role_id     = g_payee_role;

Line: 263


   SELECT name, employee_number
     INTO l_payee_name, l_emp_num
     FROM cn_salesreps
    WHERE salesrep_id = p_payee_id
      AND org_id      = p_org_id;

Line: 269


   SELECT COUNT(1)
     INTO l_count
     FROM cn_salesreps
    WHERE salesrep_id = p_payee_id
      AND org_id      = p_org_id
      AND start_date_active <= p_start_date
      AND ((end_date_active IS NULL AND p_end_date IS NULL ) OR
	   (end_date_active IS NULL AND p_end_date IS NOT NULL ) OR
	   (end_date_active >= p_end_date));

Line: 319


   SELECT payee_assign_flag, q.name, q.quota_id
     INTO l_payee_assign_flag, l_pe_name, l_quota_id
     FROM cn_quotas_all q, cn_srp_quota_assigns_all sqa
    WHERE sqa.srp_quota_assign_id = p_srp_quota_assign_id
      AND q.quota_id = sqa.quota_id;

Line: 329


	 SELECT name
	   INTO l_srp_name
	   FROM cn_salesreps
	  WHERE salesrep_id = p_salesrep_id
	    AND org_id = p_org_id;

Line: 511


   x_loading_status := 'CN_INSERTED';

Line: 518


   SELECT spa.salesrep_id, sqa.org_id, sqa.quota_id, spa.comp_plan_id
     INTO l_salesrep_id, l_org_id, l_quota_id, l_comp_plan_id
     FROM cn_srp_quota_assigns_all sqa, cn_srp_plan_assigns_all spa
    WHERE srp_quota_assign_id = p_srp_quota_assign_id
      AND sqa.srp_plan_assign_id = spa.srp_plan_assign_id;

Line: 538


   cn_srp_payee_assigns_pkg.insert_record
     ( x_srp_payee_assign_id => x_srp_payee_assign_id
      ,p_srp_quota_assign_id => p_srp_quota_assign_id
      ,p_org_id              => l_org_id
      ,p_payee_id	     => p_payee_id
      ,p_quota_id	     => l_quota_id
      ,p_salesrep_id         => l_salesrep_id
      ,p_start_date          => p_start_date
      ,p_end_date            => p_end_date
      ,p_last_update_date    => sysdate
      ,p_last_updated_by     => fnd_global.user_id
      ,p_creation_date       => sysdate
      ,p_created_by          => fnd_global.user_id
      ,p_last_update_login   => fnd_global.login_id);

Line: 553


   insert_trigger
     (new_salesrep_id         => l_salesrep_id,
      new_srp_quota_assign_id => p_srp_quota_assign_id,
      new_start_date          => p_start_date,
      new_end_date            => p_end_date,
      new_org_id              => l_org_id);

Line: 579


   SELECT name INTO l_payee_name
     FROM cn_salesreps
    WHERE salesrep_id = p_payee_id
      AND org_id = l_org_id;

Line: 604


   SELECT object_version_number
     INTO x_object_version_number
     FROM cn_srp_payee_assigns_all
    WHERE srp_payee_assign_id = x_srp_payee_assign_id;

Line: 666


PROCEDURE Update_Srp_Payee_Assigns
  (
   p_api_version          IN	NUMBER,
   p_init_msg_list	  IN	VARCHAR2,
   p_commit	    	  IN  	VARCHAR2,
   p_validation_level	  IN  	NUMBER,
   x_return_status	  OUT NOCOPY VARCHAR2,
   x_msg_count		  OUT NOCOPY NUMBER,
   x_msg_data		  OUT NOCOPY VARCHAR2,
   p_srp_payee_assign_id      IN   NUMBER,
   p_payee_id                 IN   NUMBER,
   p_start_date               IN   DATE,
   p_end_date                 IN   DATE,
   p_object_version_number    IN OUT NOCOPY NUMBER,
   x_loading_status       OUT NOCOPY  VARCHAR2
   ) IS

      l_api_name		 CONSTANT VARCHAR2(30)
	                         := 'Update_Srp_Payee_Assigns';

Line: 692


	 SELECT srp_quota_assign_id, payee_id, start_date, end_date,
	        quota_id, salesrep_id, org_id, object_version_number
	   FROM cn_srp_payee_assigns_all
	  WHERE srp_payee_assign_id = l_srp_payee_asgn_id;

Line: 700


	 SELECT ps.start_date, ps.end_date
	   FROM cn_payment_worksheets_all w,
	        cn_srp_payee_assigns_all  spa,
	        cn_payruns_all            p,
	        cn_period_statuses_all    ps
	  WHERE (w.salesrep_id = spa.payee_id or
		 w.salesrep_id = spa.salesrep_id)
	    AND w.quota_id is NULL
	    AND w.org_id = spa.org_id
	    AND p.payrun_id = w.payrun_id
	    AND p.org_id = w.org_id
	    AND p.pay_period_id = ps.period_id
	    AND p.org_id = ps.org_id
	    AND spa.srp_payee_assign_id = l_srp_payee_assign_id
	    AND spa.org_id = l_old_rec.org_id;

Line: 726


   SAVEPOINT	update_srp_payee_assigns;

Line: 745


   x_loading_status := 'CN_UPDATED';

Line: 758


	 fnd_message.set_name('CN', 'CN_RECORD_UPDATED');

Line: 761


      x_loading_status := 'CN_RECORD_UPDATED';

Line: 766


   SELECT spa.comp_plan_id
     INTO l_comp_plan_id
     FROM cn_srp_quota_assigns_all sqa, cn_srp_plan_assigns_all spa
    WHERE sqa.srp_quota_assign_id = l_old_rec.srp_quota_assign_id
      AND sqa.srp_plan_assign_id = spa.srp_plan_assign_id;

Line: 783


   x_loading_status := 'CN_UPDATED';

Line: 821


   cn_srp_payee_assigns_pkg.update_record
     (p_srp_payee_assign_id => p_srp_payee_assign_id,
      p_payee_id	    => p_payee_id,
      p_start_date          => p_start_date,
      p_end_date            => p_end_date,
      p_last_update_date    => Sysdate,
      p_last_updated_by     => fnd_global.user_id,
      p_last_update_login   => fnd_global.login_id);

Line: 831


   update_trigger
     (old_salesrep_id         => l_old_rec.salesrep_id,
      old_payee_id            => l_old_rec.payee_id,
      old_start_date          => l_old_rec.start_date,
      old_end_date            => l_old_rec.end_date,
      new_srp_quota_assign_id => l_old_rec.srp_quota_assign_id,
      new_salesrep_id         => l_old_rec.salesrep_id,
      new_payee_id            => p_payee_id,
      new_start_date          => p_start_date,
      new_end_date            => p_end_date,
      new_org_id              => l_old_rec.org_id);

Line: 844


   l_event_name := 'oracle.apps.cn.resource.PlanAssign.UpdatePayee';

Line: 860


   l_list.DELETE;

Line: 884


     SELECT name INTO l_old_payee_name
       FROM cn_salesreps
      WHERE salesrep_id = l_old_rec.payee_id
        AND org_id = l_old_rec.org_id;

Line: 888


     SELECT name INTO l_payee_name
       FROM cn_salesreps
      WHERE salesrep_id = p_payee_id
        AND org_id = l_old_rec.org_id;

Line: 908


   SELECT object_version_number
     INTO p_object_version_number
     FROM cn_srp_payee_assigns_all
    WHERE srp_payee_assign_id = p_srp_payee_assign_id;

Line: 930


      ROLLBACK TO update_srp_payee_assigns;

Line: 939


      ROLLBACK TO update_srp_payee_assigns;

Line: 949


      ROLLBACK TO Update_srp_payee_assigns;

Line: 963


END Update_Srp_Payee_Assigns ;

Line: 969


PROCEDURE Valid_Delete_Srp_Payee_Assigns
  (   	p_init_msg_list		   IN	VARCHAR2,
	x_return_status		   OUT NOCOPY VARCHAR2,
	x_msg_count		   OUT NOCOPY NUMBER,
	x_msg_data		   OUT NOCOPY VARCHAR2,
	p_srp_payee_assign_id      IN   NUMBER,
	x_loading_status           OUT NOCOPY  VARCHAR2) IS

     l_api_name           CONSTANT VARCHAR2(30) := 'Valid_Delete_Srp_Payee_Assigns';

Line: 983


	select ps.start_date, ps.end_date
	  from cn_payment_worksheets_all w,
	       cn_srp_payee_assigns_all  spa,
	       cn_payruns_all            p,
	       cn_period_statuses_all    ps
	 where (w.salesrep_id = spa.payee_id or
		w.salesrep_id = spa.salesrep_id)
	   AND w.org_id = spa.org_id
	   AND w.quota_id is null
	   AND p.payrun_id = w.payrun_id
	   AND p.pay_period_id = ps.period_id
	   AND p.org_id = ps.org_id
	   AND spa.srp_payee_assign_id = p_srp_payee_assign_id
	   AND spa.org_id = l_org_id;

Line: 1006


   x_loading_status := 'CN_DELETED';

Line: 1008


   SELECT start_date, end_date, org_id
     INTO l_start_date, l_end_date, l_org_id
     FROM cn_srp_payee_assigns_all
    WHERE srp_payee_assign_id = p_srp_payee_assign_id;

Line: 1062


END valid_delete_srp_payee_assigns;

Line: 1069


PROCEDURE Delete_Srp_Payee_Assigns
  (  	p_api_version              IN	NUMBER,
   	p_init_msg_list		   IN	VARCHAR2,
	p_commit	    	   IN  	VARCHAR2,
	p_validation_level	   IN  	NUMBER,
	x_return_status		   OUT NOCOPY VARCHAR2,
	x_msg_count		   OUT NOCOPY NUMBER,
	x_msg_data		   OUT NOCOPY VARCHAR2,
	p_srp_payee_assign_id      IN  NUMBER,
	x_loading_status           OUT NOCOPY  VARCHAR2
	) IS


      l_api_name	   CONSTANT VARCHAR2(30)
	:= 'Delete_Srp_Payee_Assigns';

Line: 1098


   SAVEPOINT	delete_srp_payee_assigns;

Line: 1116


   x_loading_status := 'CN_DELETED';

Line: 1123


   valid_delete_srp_payee_assigns
     (p_init_msg_list            => p_init_msg_list,
      x_return_status            => x_return_status,
      x_msg_count                => x_msg_count,
      x_msg_data                 => x_msg_data,
      p_srp_payee_assign_id      => p_srp_payee_assign_id,
      x_loading_status           => x_loading_status);

Line: 1136


   SELECT salesrep_id, srp_quota_assign_id, start_date, end_date, org_id, payee_id
     INTO l_salesrep_id, l_srp_quota_assign_id, l_start_date, l_end_date, l_org_id, l_payee_id
     FROM cn_srp_payee_assigns_all
    WHERE srp_payee_assign_id = p_srp_payee_assign_id;

Line: 1141


   cn_srp_payee_assigns_pkg.delete_record
     (p_srp_payee_assign_id  => p_srp_payee_assign_id);

Line: 1144


   delete_trigger
     (old_salesrep_id         => l_salesrep_id,
      old_srp_quota_assign_id => l_srp_quota_assign_id,
      old_start_date          => l_start_date,
      old_end_date            => l_end_date,
      old_org_id              => l_org_id);

Line: 1152


   SELECT name INTO l_payee_name
     FROM cn_salesreps
    WHERE salesrep_id = l_payee_id
      AND org_id = l_org_id;

Line: 1194


      ROLLBACK TO Delete_srp_payee_assigns;

Line: 1203


      ROLLBACK TO Delete_srp_payee_assigns;

Line: 1213


      ROLLBACK TO delete_srp_payee_assigns;

Line: 1226


END Delete_Srp_Payee_Assigns;