APPS.CN_SRP_PAYGROUP_PVT SQL Statements

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

Line: 47

   SELECT COUNT(1) INTO l_dummy
     FROM cn_srp_pay_groups_all
     WHERE salesrep_id = p_salesrep_id
     AND   pay_group_id =  p_pay_group_id
     AND   start_date  = p_start_date
     AND   ( (end_date = p_end_date) OR
	     (end_date IS NULL AND p_end_date IS NULL) )
	       AND   ((p_srp_pay_group_id IS NOT NULL AND
		       srp_pay_group_id<> p_srp_pay_group_id)
		      OR
		      (p_srp_pay_group_id IS NULL));

Line: 73

   SELECT start_date_active, end_date_active
     INTO l_srp_start_date, l_srp_end_date
     FROM cn_salesreps
    WHERE salesrep_id = p_salesrep_id
      AND org_id = p_org_id;

Line: 149

      SELECT start_date, end_date
        INTO l_pp_start_date, l_pp_end_date
        FROM cn_pay_groups_all
       WHERE pay_group_id = p_pay_group_id;

Line: 183

   SELECT COUNT(1)
     INTO l_count
     FROM cn_pay_groups_all
     WHERE (( p_start_date NOT BETWEEN start_date AND end_date )
	    OR  (p_end_date NOT BETWEEN start_date AND end_date))
     AND pay_group_id = p_pay_group_id;

Line: 206

   SELECT count(1)
     INTO l_count
     FROM cn_srp_pay_groups_all
     WHERE p_start_date between start_date AND Nvl(end_date, p_start_date)
     AND salesrep_id = p_salesrep_id
     AND org_id      = p_org_id
     AND srp_pay_group_id <> p_srp_pay_group_id;

Line: 227

   SELECT count(1)
     INTO l_count
     FROM cn_srp_pay_groups_all
     WHERE Nvl(p_end_date, l_null_date) between start_date
     AND Nvl(end_date, Nvl(p_end_date, l_null_date))
     AND salesrep_id = p_salesrep_id
     AND org_id      = p_org_id
     AND srp_pay_group_id <> p_srp_pay_group_id;

Line: 249

   SELECT count(1)
     INTO l_count
     FROM cn_srp_pay_groups_all
     WHERE salesrep_id = p_salesrep_id
     AND org_id        = p_org_id
     AND p_start_date <= start_date
     AND Nvl(p_end_date, l_null_date) >= Nvl(end_date, l_null_date)
     AND srp_pay_group_id <> p_srp_pay_group_id;

Line: 333

     select 1 into l_count from dual where not exists
       (select 1 from cn_srp_periods_all csp, cn_posting_details_sum_all cpd
	 where cpd.credited_salesrep_id = p_salesrep_id
	   AND cpd.org_id               = p_org_id
	   AND cpd.pay_period_id = csp.period_id
   	   AND csp.salesrep_id = cpd.credited_salesrep_id
	   AND csp.end_date > p_assign_end_date);

Line: 377

PROCEDURE srp_plan_assignment_for_delete
  (p_role_id        IN cn_roles.role_id%TYPE,
   p_role_plan_id   IN cn_role_plans.role_plan_id%TYPE,
   p_salesrep_id    IN cn_salesreps.salesrep_id%TYPE,
   p_org_id         IN cn_salesreps.org_id%TYPE,
   x_return_status  OUT NOCOPY VARCHAR2,
   p_loading_status IN  VARCHAR2,
   x_loading_status OUT NOCOPY VARCHAR2 ) IS

      CURSOR l_cur IS
	 SELECT srp_role_id
	   FROM cn_srp_roles
	   WHERE role_id = p_role_id
	   and salesrep_id =p_salesrep_id
	   AND org_id = p_org_id;

Line: 411

	cn_srp_plan_assigns_pvt.delete_srp_plan_assigns
	  (
	   p_api_version        => 1.0,
	   x_return_status      => l_return_status,
	   x_msg_count          => l_msg_count,
	   x_msg_data           => l_msg_data,
	   p_srp_role_id        => l_rec.srp_role_id,
	   p_role_plan_id       => p_role_plan_id,
	   x_loading_status     => l_loading_status);

Line: 428

END srp_plan_assignment_for_delete;

Line: 455

    ELSIF (p_operation = 'Update') THEN
      l_key := l_key || '-' || p_paygroup_assign_rec.object_version_number;

Line: 473

   l_list.DELETE;

Line: 504

      SELECT role_id, srp_role_id,start_date, nvl(end_date,l_null_date) end_date
	FROM cn_srp_roles
	WHERE salesrep_id = p_salesrep_id
	  AND org_id      = p_paygroup_assign_rec.org_id;

Line: 510

      SELECT role_plan_id
        FROM cn_role_plans
        WHERE role_id = p_role_id
	  AND org_id = p_paygroup_assign_rec.org_id;

Line: 518

	 SELECT comp_plan_id,
	   start_date,
	   end_date
	   FROM cn_srp_plan_assigns_all
	   WHERE role_id = p_role_id
	   AND salesrep_id = p_salesrep_id
	   AND org_id = p_paygroup_assign_rec.org_id;

Line: 550

   SELECT cn_srp_pay_groups_s.NEXTVAL
     INTO l_srp_pay_group_id
     FROM dual;

Line: 568

   SELECT name, employee_number
     INTO l_employee_name, l_employee_number
     FROM cn_salesreps
    WHERE salesrep_id = p_paygroup_assign_rec.salesrep_id
      AND org_id      = p_paygroup_assign_rec.org_id;

Line: 616

        x_operation         => 'INSERT',
	x_srp_pay_group_id  => l_srp_pay_group_id,
	x_salesrep_id       => p_paygroup_assign_rec.salesrep_id,
	x_pay_group_id      => p_paygroup_assign_rec.pay_group_id,
	x_start_date        => p_paygroup_assign_rec.assignment_start_date,
	x_end_date          => p_paygroup_assign_rec.assignment_end_date,
	x_lock_flag         => p_paygroup_assign_rec.lock_flag,
        x_role_pay_group_id => p_paygroup_assign_rec.role_pay_group_id,
	x_org_id            => p_paygroup_assign_rec.org_id,
	x_attribute_category=> p_paygroup_assign_rec.attribute_category,
	x_attribute1        => p_paygroup_assign_rec.attribute1,
	x_attribute2        => p_paygroup_assign_rec.attribute2,
	x_attribute3        => p_paygroup_assign_rec.attribute3,
	x_attribute4        => p_paygroup_assign_rec.attribute4,
	x_attribute5        => p_paygroup_assign_rec.attribute5,
	x_attribute6        => p_paygroup_assign_rec.attribute6,
	x_attribute7        => p_paygroup_assign_rec.attribute7,
	x_attribute8        => p_paygroup_assign_rec.attribute8,
	x_attribute9        => p_paygroup_assign_rec.attribute9,
	x_attribute10       => p_paygroup_assign_rec.attribute10,
	x_attribute11       => p_paygroup_assign_rec.attribute10,
	x_attribute12       => p_paygroup_assign_rec.attribute12,
	x_attribute13       => p_paygroup_assign_rec.attribute13,
	x_attribute14       => p_paygroup_assign_rec.attribute14,
	x_attribute15       => p_paygroup_assign_rec.attribute15,
	x_last_update_date  => Sysdate,
	x_last_updated_by   => fnd_global.user_id,
	x_creation_date     => Sysdate,
	x_created_by        => fnd_global.user_id,
	x_last_update_login => fnd_global.login_id,
    x_object_version_number => p_paygroup_assign_rec.object_version_number);

Line: 682

	      cn_srp_plan_assigns_pvt.Update_Srp_Plan_Assigns
		(p_api_version   => 1.0,
		 x_return_status => x_return_status,
		 x_msg_count	 => x_msg_count,
		 x_msg_data	 => x_msg_data,
		 p_srp_role_id   => roles.srp_role_id,
		 p_role_plan_id  => role_plans.role_plan_id,
		 x_loading_status => x_loading_status );

Line: 701

	      select name
		into l_pay_group_name
		from cn_pay_groups_all
		where pay_group_id = p_paygroup_assign_rec.pay_group_id;

Line: 813

PROCEDURE Update_Srp_Pay_Group
  (  	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_loading_status           OUT NOCOPY  VARCHAR2                       ,
  	x_msg_count		   OUT NOCOPY	NUMBER			      ,
  	x_msg_data		   OUT NOCOPY	VARCHAR2                      ,
	p_paygroup_assign_rec      IN OUT NOCOPY  PayGroup_assign_rec
  	) IS

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

Line: 830

      SELECT role_id,srp_role_id,start_date, nvl(end_date,l_null_date) end_date
	FROM cn_srp_roles
	WHERE salesrep_id = p_salesrep_id
	  AND org_id      = p_paygroup_assign_rec.org_id;

Line: 836

      SELECT role_plan_id
        FROM cn_role_plans_all
       WHERE role_id = p_role_id
	 AND org_id = p_paygroup_assign_rec.org_id;

Line: 844

	 SELECT comp_plan_id,
	   start_date,
	   end_date
	   FROM cn_srp_plan_assigns_all
	   WHERE role_id = p_role_id
	   AND salesrep_id = p_salesrep_id
	   AND org_id = p_paygroup_assign_rec.org_id;

Line: 854

      select srp_role_id from cn_srp_roles where
	salesrep_id = l_salesrep_id
	and role_id = 54
	AND org_id = p_paygroup_assign_rec.org_id;

Line: 860

      select salesrep_id, start_date, end_date
	from cn_srp_payee_assigns_all
	where payee_id = l_payee_id
	AND org_id = p_paygroup_assign_rec.org_id;

Line: 886

   SAVEPOINT	Update_Srp_Pay_Group;

Line: 905

   x_loading_status := 'CN_UPDATED';

Line: 909

   select object_version_number, salesrep_id, start_date, end_date, lock_flag
     into l_ovn_old, l_old_salesrep_id,
          l_old_assignment_start_date, l_old_assignment_end_date, l_old_lock_flag
     from cn_srp_pay_groups_all
     where srp_pay_group_id = p_paygroup_assign_rec.srp_pay_group_id;

Line: 947

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

Line: 950

      x_loading_status := 'CN_CANNOT_UPDATE_LOCK';

Line: 959

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

Line: 962

      x_loading_status := 'CN_CANNOT_UPDATE_LOCK';

Line: 980

   SELECT name, employee_number
     INTO l_employee_name, l_employee_number
     FROM cn_salesreps
    WHERE salesrep_id = p_paygroup_assign_rec.salesrep_id
      AND org_id      = p_paygroup_assign_rec.org_id;

Line: 1068

	       SELECT count(*)
		 into l_ws_count
		 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
		      cn_payruns_all prun
		 WHERE w.salesrep_id      = p_paygroup_assign_rec.salesrep_id
		 AND   w.org_id           = p_paygroup_assign_rec.org_id
		 AND   prun.pay_period_id = prd.period_id
		 AND   prun.payrun_id     = w.payrun_id
		 AND   prd.org_id         = p_paygroup_assign_rec.org_id
		 AND   prun.pay_group_id  = p_paygroup_assign_rec.pay_group_id
		 AND   w.quota_id is null
		   AND (
			(prd.start_date BETWEEN l_date_range_action_tbl(i).start_date
			 AND nvl(l_date_range_action_tbl(i).end_date,l_null_date))
			OR
			(prd.end_date between l_date_range_action_tbl(i).start_date
			 AND nvl(l_date_range_action_tbl(i).end_date,l_null_date))
			);

Line: 1121

         SELECT count(1) INTO l_dummy
            FROM  cn_payment_worksheets W, cn_period_statuses prd, cn_payruns prun
            WHERE w.salesrep_id      = p_paygroup_assign_rec.salesrep_id
	    AND   w.org_id           = p_paygroup_assign_rec.org_id
	    AND   prun.pay_period_id = prd.period_id
	    AND   prd.org_id         = p_paygroup_assign_rec.org_id
            AND   prun.payrun_id     = w.payrun_id
            AND  greatest(prd.start_date, p_paygroup_assign_rec.assignment_end_date) <
                 least(prd.end_date, nvl(l_old_assignment_end_date, prd.end_date));

Line: 1147

         SELECT count(1) INTO l_dummy
           FROM cn_payment_worksheets W, cn_period_statuses prd, cn_payruns prun
            WHERE w.salesrep_id      = p_paygroup_assign_rec.salesrep_id
	    AND   w.org_id           = p_paygroup_assign_rec.org_id
	    AND   prun.pay_period_id = prd.period_id
	    AND   prd.org_id         = p_paygroup_assign_rec.org_id
            AND   prun.payrun_id     = w.payrun_id
            AND  greatest(prd.start_date, l_old_assignment_start_date) <
                 least(prd.end_date, p_paygroup_assign_rec.assignment_start_date);

Line: 1160

                  FND_MESSAGE.SET_NAME ('CN' , 'CN_SPG_UPDATE_NOT_ALLOWED');

Line: 1163

               x_loading_status := 'CN_SPG_UPDATE_NOT_ALLOWED';

Line: 1175

	x_operation         => 'UPDATE',
	x_srp_pay_group_id  => p_paygroup_assign_rec.srp_pay_group_id,
	x_salesrep_id       => p_paygroup_assign_rec.salesrep_id,
	x_pay_group_id      => p_paygroup_assign_rec.pay_group_id,
	x_start_date        => p_paygroup_assign_rec.assignment_start_date,
	x_end_date          => p_paygroup_assign_rec.assignment_end_date,
	x_lock_flag         => p_paygroup_assign_rec.lock_flag,
        x_role_pay_group_id => p_paygroup_assign_rec.role_pay_group_id,
	x_org_id            => p_paygroup_assign_rec.org_id,
	x_attribute_category=> p_paygroup_assign_rec.attribute_category,
	x_attribute1        => p_paygroup_assign_rec.attribute1,
	x_attribute2        => p_paygroup_assign_rec.attribute2,
	x_attribute3        => p_paygroup_assign_rec.attribute3,
	x_attribute4        => p_paygroup_assign_rec.attribute4,
	x_attribute5        => p_paygroup_assign_rec.attribute5,
	x_attribute6        => p_paygroup_assign_rec.attribute6,
	x_attribute7        => p_paygroup_assign_rec.attribute7,
	x_attribute8        => p_paygroup_assign_rec.attribute8,
	x_attribute9        => p_paygroup_assign_rec.attribute9,
	x_attribute10       => p_paygroup_assign_rec.attribute10,
	x_attribute11       => p_paygroup_assign_rec.attribute10,
	x_attribute12       => p_paygroup_assign_rec.attribute12,
	x_attribute13       => p_paygroup_assign_rec.attribute13,
	x_attribute14       => p_paygroup_assign_rec.attribute14,
	x_attribute15       => p_paygroup_assign_rec.attribute15,
	x_last_update_date  => Sysdate,
	x_last_updated_by   => fnd_global.user_id,
	x_creation_date     => Sysdate,
	x_created_by        => fnd_global.user_id,
	x_last_update_login => fnd_global.login_id,
    x_object_version_number => p_paygroup_assign_rec.object_version_number);

Line: 1209

	(p_operation              => 'Update',
	 p_paygroup_assign_rec    => p_paygroup_assign_rec);

Line: 1218

	-- Added by Zack 01/15/02, update cn_srp_plan_assign if necessary.
	-- clku, bug 2772005, nvl the end dates here
	IF(
	    (p_paygroup_assign_rec.assignment_start_date <> l_old_assignment_start_date )
	    AND
	    ( (roles.start_date <= least(p_paygroup_assign_rec.assignment_start_date, l_old_assignment_start_date)
	       AND roles.end_date >= least(p_paygroup_assign_rec.assignment_start_date, l_old_assignment_start_date) )
	      OR
	      (roles.start_date <= greatest(p_paygroup_assign_rec.assignment_start_date, l_old_assignment_start_date)
	       AND roles.end_date >= greatest(p_paygroup_assign_rec.assignment_start_date, l_old_assignment_start_date) ) )
            OR
	    (nvl(p_paygroup_assign_rec.assignment_end_date, l_null_date) <> nvl(l_old_assignment_end_date, l_null_date) )
	    AND
	    ( (roles.start_date <= least(nvl(p_paygroup_assign_rec.assignment_end_date, l_null_date), nvl(l_old_assignment_end_date, l_null_date))
	       AND roles.end_date >= least(nvl(p_paygroup_assign_rec.assignment_end_date, l_null_date), nvl(l_old_assignment_end_date, l_null_date)) )
	      OR
	      (roles.start_date <= greatest(nvl(p_paygroup_assign_rec.assignment_end_date, l_null_date), nvl(l_old_assignment_end_date, l_null_date))
	       AND roles.end_date >= greatest(nvl(p_paygroup_assign_rec.assignment_end_date, l_null_date), nvl(l_old_assignment_end_date, l_null_date)) ) )
	  ) THEN
	   FOR role_plans IN get_role_plans(roles.role_id) LOOP
	      cn_srp_plan_assigns_pvt.Update_Srp_Plan_Assigns
                (
                 p_api_version    => 1.0,
                 x_return_status => x_return_status,
                 x_msg_count	 => x_msg_count,
                 x_msg_data	 => x_msg_data,
                 p_srp_role_id   => roles.srp_role_id,
                 p_role_plan_id  => role_plans.role_plan_id,
                 x_loading_status  => x_loading_status );

Line: 1258

	      select name
		into l_pay_group_name
		from cn_pay_groups_all
		where pay_group_id = p_paygroup_assign_rec.pay_group_id;

Line: 1330

      ROLLBACK TO Update_Srp_Pay_Group;

Line: 1339

      ROLLBACK TO Update_Srp_Pay_Group;

Line: 1349

      ROLLBACK TO Update_Srp_Pay_Group;

Line: 1363

END update_srp_pay_group;

Line: 1368

PROCEDURE valid_delete_srp_pay_group
  (  	p_paygroup_assign_rec      IN paygroup_assign_rec                     ,
     	p_init_msg_list		   IN	VARCHAR2,
  	x_loading_status	   OUT NOCOPY	VARCHAR2	     	      ,
  	x_return_status		   OUT NOCOPY	VARCHAR2	     	      ,
  	x_msg_count		   OUT NOCOPY	NUMBER			      ,
  	x_msg_data		   OUT NOCOPY	VARCHAR2
	) IS


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

Line: 1387

      select srp_role_id from cn_srp_roles where
	salesrep_id = l_salesrep_id
	and role_id = 54
	AND org_id  = p_paygroup_assign_rec.org_id;

Line: 1393

	 select salesrep_id, start_date, end_date
	   from cn_srp_payee_assigns_all
	   where payee_id = l_payee_id
	     AND org_id   = p_paygroup_assign_rec.org_id;

Line: 1410

   x_loading_status := 'CN_DELETED';

Line: 1441

	SELECT count(*)
	  into l_ws_count
	  FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
	  cn_payruns_all prun
	  WHERE w.salesrep_id = p_paygroup_assign_rec.salesrep_id
	  AND   w.org_id      = p_paygroup_assign_rec.org_id
	  AND   prun.pay_period_id = prd.period_id
	  AND   prun.payrun_id     = w.payrun_id
	  AND   prd.org_id         = p_paygroup_assign_rec.org_id
	  AND   prun.pay_group_id  = p_paygroup_assign_rec.pay_group_id
	  AND   w.quota_id is null
	    AND (
		 (prd.start_date BETWEEN p_paygroup_assign_rec.assignment_start_date
		  AND nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date))
		 OR
		 (prd.end_date between p_paygroup_assign_rec.assignment_start_date
		  AND nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date))
		 );

Line: 1476

   SELECT SRP_PAY_GROUP_ID
     INTO l_srp_pay_group_id
     FROM cn_srp_pay_groups_all
    WHERE pay_group_id = p_paygroup_assign_rec.pay_group_id
      AND start_date=p_paygroup_assign_rec.assignment_start_date
      AND (end_date =p_paygroup_assign_rec.assignment_end_date OR
	   end_date IS NULL)
      AND salesrep_id =p_paygroup_assign_rec.salesrep_id;

Line: 1485

   SELECT COUNT(1) INTO l_count from cn_srp_pay_groups_all
    WHERE srp_pay_group_id = l_srp_pay_group_id
     AND salesrep_id = p_paygroup_assign_rec.salesrep_id
     AND pay_group_id= p_paygroup_assign_rec.pay_group_id
     AND org_id      = p_paygroup_assign_rec.org_id
     -- AND (lock_flag='N'OR lock_flag IS NULL)
       AND (start_date between p_paygroup_assign_rec.assignment_start_date AND
	    nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date))
       AND (nvl(end_date,l_null_date) between
	    p_paygroup_assign_rec.assignment_start_date AND
	    nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date))
       AND NOT EXISTS
       (SELECT 1 FROM cn_payment_worksheets_all W,
	cn_period_statuses_all prd, cn_payruns_all prun
	WHERE w.salesrep_id = p_paygroup_assign_rec.salesrep_id
	AND   w.org_id      = p_paygroup_assign_rec.org_id
	AND   prun.pay_period_id = prd.period_id
	AND   prun.payrun_id     = w.payrun_id
	AND   prun.pay_group_id  = p_paygroup_assign_rec.pay_group_id
	AND   prd.org_id         = p_paygroup_assign_rec.org_id
	AND ((prd.start_date BETWEEN
	      p_paygroup_assign_rec.assignment_start_date AND
	      nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date))
	     OR (prd.end_date between
		 p_paygroup_assign_rec.assignment_start_date AND
		 nvl(p_paygroup_assign_rec.assignment_end_date,l_null_date))));

Line: 1559

END valid_delete_srp_pay_group;

Line: 1564

PROCEDURE Delete_Srp_Pay_Group
(  	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_loading_status           OUT NOCOPY  VARCHAR2 	              ,
	x_msg_count		   OUT NOCOPY	NUMBER		    	      ,
	x_msg_data		   OUT NOCOPY	VARCHAR2               	      ,
        p_paygroup_assign_rec            IN  PayGroup_assign_rec
 	) IS

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

Line: 1590

	 SELECT role_plan_id,role_id
	   FROM cn_role_plans_all
	  WHERE role_id = l_role_id
	    AND org_id  = p_paygroup_assign_rec.org_id;

Line: 1596

	 SELECT srp_role_id,salesrep_id
	   FROM cn_srp_roles
	  WHERE role_id = l_role_id
	    AND org_id  = p_paygroup_assign_rec.org_id;

Line: 1602

	 SELECT role_id, srp_role_id,start_date, nvl(end_date,l_null_date) end_date
	   FROM cn_srp_roles
	  WHERE salesrep_id = p_salesrep_id
	    AND org_id = p_paygroup_assign_rec.org_id;

Line: 1610

	    SELECT comp_plan_id,
	      start_date,
	      end_date
	      FROM cn_srp_plan_assigns_all
	      WHERE role_id   = p_role_id
	      AND salesrep_id = p_salesrep_id
	      AND org_id      = p_paygroup_assign_rec.org_id;

Line: 1619

         select pay_group_id,start_date,end_date
	   from cn_srp_pay_groups_all
	   where salesrep_id = l_salesrep_id
	     AND org_id      = p_paygroup_assign_rec.org_id;

Line: 1629

   SAVEPOINT	Delete_Srp_Pay_Group;

Line: 1648

   x_loading_status := 'CN_DELETED';

Line: 1651

   valid_delete_srp_pay_group
     (	p_paygroup_assign_rec      => p_paygroup_assign_rec,
	p_init_msg_list            => p_init_msg_list,
  	x_loading_status	   => x_loading_status,
	x_return_status		   => x_return_status,
  	x_msg_count		   => x_msg_count,
  	x_msg_data		   => x_msg_data);

Line: 1664

   SELECT SRP_PAY_GROUP_ID
     INTO l_srp_pay_group_id
     FROM cn_srp_pay_groups_all
    WHERE pay_group_id = p_paygroup_assign_rec.pay_group_id
      AND start_date=p_paygroup_assign_rec.assignment_start_date
      AND (end_date =p_paygroup_assign_rec.assignment_end_date OR
	   end_date IS NULL)
      AND salesrep_id =p_paygroup_assign_rec.salesrep_id;

Line: 1673

   DELETE FROM cn_srp_pay_groups_all
    WHERE srp_pay_group_id = l_srp_pay_group_id;

Line: 1682

   SELECT count (*), min(start_date),nvl(max(end_date),l_null_date) end_date
     INTO l_count,l_start_date,l_end_date
     FROM cn_srp_pay_groups_all
    WHERE salesrep_id = p_paygroup_assign_rec.salesrep_id
      AND org_id      = p_paygroup_assign_rec.org_id;

Line: 1695

	    srp_plan_assignment_for_delete
	      (p_role_id        => role_plans.role_id,
	       p_role_plan_id   => role_plans.role_plan_id,
	       p_salesrep_id    => p_paygroup_assign_rec.salesrep_id,
	       p_org_id         => p_paygroup_assign_rec.org_id,
	       x_return_status  => x_return_status,
	       p_loading_status => l_loading_status,
	       x_loading_status => x_loading_status);

Line: 1723

			cn_srp_plan_assigns_pvt.Update_Srp_Plan_Assigns
			  ( p_api_version     =>    1.0,
			    x_return_status   => x_return_status,
			    x_msg_count	      => x_msg_count,
			    x_msg_data	      => x_msg_data,
			    p_srp_role_id     => roles.srp_role_id,
			    p_role_plan_id    => role_plans.role_plan_id,
			    x_loading_status  => x_loading_status );

Line: 1792

      ROLLBACK TO Delete_Srp_Pay_Group;

Line: 1803

      ROLLBACK TO Delete_Srp_Pay_Group;

Line: 1813

      ROLLBACK TO Delete_Srp_Pay_Group;

Line: 1826

END Delete_Srp_Pay_Group;

Line: 1832

PROCEDURE Delete_Mass_Asgn_Srp_Pay
  (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_role_id        IN    NUMBER,
   p_role_pay_group_id  IN    NUMBER,
   x_loading_status     OUT NOCOPY  VARCHAR2
   ) IS

      l_return_status        VARCHAR2(2000);

Line: 1867

   SELECT org_id
     INTO l_org_id
     FROM cn_role_pay_groups
     WHERE role_pay_group_id = p_role_pay_group_id;

Line: 1872

     select salesrep_id, start_date, end_date
       into l_salesrep_id, l_srp_start_date, l_srp_end_date
       from cn_srp_roles
       where srp_role_id = p_srp_role_id
       AND org_id = l_org_id; -- vensrini

Line: 1879

     SELECT COUNT(1) INTO l_count
       FROM cn_role_pay_groups
      WHERE role_pay_group_id = p_role_pay_group_id
        AND Greatest(l_srp_start_date, start_date) <=
            Least(Nvl(l_srp_end_date, l_null_date),
		  Nvl(end_date,       l_null_date));

Line: 1893

     select spp.start_date, spp.end_date, spp.salesrep_id,
	    spp.lock_flag,cpp.pay_group_id, spp.org_id
       into l_start_date, l_end_date, l_salesrep_id,
            l_lock_flag,l_pay_group_id, l_org_id
       from cn_srp_pay_groups_all spp, cn_pay_groups_all cpp
      where spp.role_pay_group_id = p_role_pay_group_id
        AND spp.salesrep_id = l_salesrep_id
       AND cpp.pay_group_id = spp.pay_group_id
       AND Greatest(spp.start_date, l_srp_start_date) <=
            Least(Nvl(spp.end_date,l_null_date),
		  Nvl(l_srp_end_date,l_null_date));

Line: 1914

    SELECT count(*)
      into l_count
      FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
           cn_payruns_all prun
      WHERE w.salesrep_id      = l_salesrep_id
      AND   w.org_id           = l_org_id
      AND   prun.pay_period_id = prd.period_id
      AND   prun.payrun_id     = w.payrun_id
      AND   prun.pay_group_id  = l_pay_group_id
      AND   prd.org_id         = l_org_id
      AND ((prd.start_date BETWEEN l_start_date AND nvl(l_end_date,l_null_date)) OR
	   (prd.end_date between l_start_date AND nvl(l_end_date,l_null_date)) );

Line: 1930

        Update cn_srp_pay_groups_all
		set role_pay_group_id = null
		where role_pay_group_id = p_role_pay_group_id
        and salesrep_id = l_salesrep_id
        and org_id = l_org_id;

Line: 1944

    delete_srp_pay_group
      (
       p_api_version        => 1.0,
       x_return_status      => l_return_status,
       x_loading_status     => l_loading_status,
       x_msg_count          => l_msg_count,
       x_msg_data           => l_msg_data,
       p_paygroup_assign_rec=> newrec);

Line: 1969

END Delete_Mass_Asgn_Srp_Pay;