APPS.CN_ROLE_PAY_GROUPS_PVT 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: 67

  select org_id, pay_group_id, start_date, end_date
  into l_org_id, l_pay_group_id, l_role_pg_start_date, l_role_pg_end_date
  from cn_role_pay_groups
  where role_pay_group_id = p_role_pay_group_id;

Line: 73

  select start_date, end_date, salesrep_id
  into l_srp_role_start_date, l_srp_role_end_date, l_salesrep_id
  from cn_srp_roles
  where srp_role_id = p_srp_role_id
    and org_id = l_org_id;

Line: 92

  select start_date_active, end_date_active
  into l_res_start_date, l_res_end_date
  from cn_salesreps
  where salesrep_id = l_salesrep_id
    and org_id = l_org_id;

Line: 111

  select start_date, end_date
  into l_pg_start_date, l_pg_end_date
  from cn_pay_groups
  where pay_group_id = l_pay_group_id;

Line: 137

	 SELECT *
	   FROM cn_roles
	   WHERE name = l_role_name;

Line: 168

	 SELECT *
	   FROM cn_pay_groups
	   WHERE name = l_pay_group_name and org_id =l_org_id;

Line: 198

	 SELECT *
	   FROM cn_role_pay_groups
	   WHERE role_pay_group_id = l_role_pay_group_id;

Line: 229

	 SELECT *
	   FROM cn_role_pay_groups
	   WHERE role_pay_group_id = l_role_pay_group_id;

Line: 255

   SELECT pay_group_id
     INTO l_pay_group_id
     FROM cn_pay_groups
     WHERE  name = p_pay_group_name and org_id=p_org_id;

Line: 278

   SELECT name
     INTO l_pay_group_name
     FROM cn_pay_groups
     WHERE  pay_group_id = p_pay_group_id ;

Line: 308

	 SELECT role_pay_group_id
	   FROM cn_role_pay_groups
	   WHERE role_id = l_role_id AND
	   pay_group_id = l_pay_group_id AND
	   start_date = l_start_date AND
	   ((end_date = l_end_date) OR
	    (end_date IS NULL AND l_end_date IS NULL));

Line: 344

PROCEDURE check_valid_insert
  (
   x_return_status          OUT NOCOPY VARCHAR2,
   x_msg_count              OUT NOCOPY NUMBER,
   x_msg_data               OUT NOCOPY VARCHAR2,
   p_role_pay_groups_rec     IN  role_pay_groups_rec_type,
   x_role_id                OUT NOCOPY cn_roles.role_id%TYPE,
   x_pay_group_id           OUT NOCOPY cn_role_pay_groups.pay_group_id%TYPE,
   p_loading_status         IN  VARCHAR2,
   x_loading_status         OUT NOCOPY VARCHAR2
   ) IS

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

Line: 364

	 SELECT start_date, end_date, pay_group_id
	   FROM cn_role_pay_groups
	   WHERE role_id = l_role_id and org_id=l_org_id;

Line: 369

	 SELECT start_date, end_date
	   FROM cn_pay_groups
	   WHERE name = l_pay_group_name and org_id=l_org_id;

Line: 446

   SELECT COUNT(1)
     INTO l_count
     FROM cn_pay_groups
     WHERE (( p_role_pay_groups_rec.start_date NOT BETWEEN start_date AND end_date )
	    OR  (p_role_pay_groups_rec.end_date NOT BETWEEN start_date AND end_date))
     AND pay_group_id = x_pay_group_id;

Line: 469

      /* SELECT count(1)
      INTO l_count
      FROM cn_role_pay_groups
      WHERE p_role_pay_groups_rec.start_date between start_date AND Nvl(end_date, p_role_pay_groups_rec.start_date)
      AND role_id = x_role_id; */

Line: 516

      /* SELECT count(1)
        INTO l_count
        FROM cn_role_pay_groups
        WHERE Nvl(p_role_pay_groups_rec.end_date, l_null_date) between start_date
        AND Nvl(end_date, Nvl(p_role_pay_groups_rec.end_date, l_null_date))
        AND role_id = x_role_id;

Line: 537

   SELECT count(1)
        INTO l_count
        FROM cn_role_pay_groups
        WHERE p_role_pay_groups_rec.start_date <= start_date
        AND Nvl(p_role_pay_groups_rec.end_date, l_null_date) >= Nvl(end_date, l_null_date) AND role_id = x_role_id;

Line: 598

END check_valid_insert;

Line: 607

PROCEDURE check_valid_delete
  (
   x_return_status          OUT NOCOPY VARCHAR2,
   x_msg_count              OUT NOCOPY NUMBER,
   x_msg_data               OUT NOCOPY VARCHAR2,
   p_role_pay_groups_rec          IN  role_pay_groups_rec_type,
   x_role_pay_group_id           OUT NOCOPY NUMBER,
   p_loading_status         IN  VARCHAR2,
   x_loading_status         OUT NOCOPY VARCHAR2
   ) IS

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

Line: 691

END check_valid_delete;

Line: 698

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: 729

	cn_srp_plan_assigns_pvt.delete_srp_plan_assigns
	  (
	   p_api_version        => 1.0,
	   p_init_msg_list      => fnd_api.g_false,
	   p_commit             => fnd_api.g_false,
	   p_validation_level   => fnd_api.g_valid_level_full,
	   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: 749

END srp_plan_assignment_for_delete;

Line: 785

        SELECT role_id, srp_role_id,start_date, nvl(end_date,l_null_date) end_date,org_id
      	FROM cn_srp_roles
      	WHERE salesrep_id = p_salesrep_id and org_id=p_org_id;

Line: 790

            SELECT role_plan_id
              FROM cn_role_plans
              WHERE role_id = p_role_id and org_id=p_org_id;

Line: 798

      	 SELECT comp_plan_id,
      	   start_date,
      	   end_date
      	   FROM cn_srp_plan_assigns
      	   WHERE role_id = p_role_id
      	   AND salesrep_id = p_salesrep_id and org_id=p_org_id;

Line: 806

	 SELECT srp_role_id,salesrep_id,start_date,end_date,org_id
	   FROM cn_srp_roles WHERE role_id = l_role_id and org_id=l_org_id;

Line: 838

   x_loading_status := 'CN_INSERTED';

Line: 847

   check_valid_insert
     ( x_return_status       => x_return_status,
       x_msg_count           => x_msg_count,
       x_msg_data            => x_msg_data,
       p_role_pay_groups_rec => p_role_pay_groups_rec,
       x_role_id             => l_role_id,
       x_pay_group_id        => l_pay_group_id,
       p_loading_status      => l_loading_status, -- in
       x_loading_status      => x_loading_status  -- out
       );

Line: 863

      cn_role_pay_groups_pkg.INSERT_ROW
	(
	  x_rowid                  => G_ROWID
	 ,x_role_pay_group_id 	   => l_role_pay_group_id
	 ,x_role_id                => l_role_id
	 ,x_pay_group_id           => l_pay_group_id
	 ,x_start_date             => p_role_pay_groups_rec.start_date
	 ,x_end_date               => p_role_pay_groups_rec.end_date
	 ,x_attribute_category     => p_role_pay_groups_rec.ATTRIBUTE_CATEGORY
	 ,x_attribute1             => p_role_pay_groups_rec.ATTRIBUTE1
	 ,x_attribute2             => p_role_pay_groups_rec.ATTRIBUTE2
	 ,x_attribute3             => p_role_pay_groups_rec.ATTRIBUTE3
	 ,x_attribute4             => p_role_pay_groups_rec.ATTRIBUTE4
	 ,x_attribute5             => p_role_pay_groups_rec.ATTRIBUTE5
	 ,x_attribute6             => p_role_pay_groups_rec.ATTRIBUTE6
	 ,x_attribute7             => p_role_pay_groups_rec.ATTRIBUTE7
	 ,x_attribute8             => p_role_pay_groups_rec.ATTRIBUTE8
	 ,x_attribute9             => p_role_pay_groups_rec.ATTRIBUTE9
	 ,x_attribute10            => p_role_pay_groups_rec.ATTRIBUTE10
	 ,x_attribute11            => p_role_pay_groups_rec.ATTRIBUTE11
	 ,x_attribute12            => p_role_pay_groups_rec.ATTRIBUTE12
	 ,x_attribute13            => p_role_pay_groups_rec.ATTRIBUTE13
	 ,x_attribute14            => p_role_pay_groups_rec.ATTRIBUTE14
	 ,x_attribute15            => p_role_pay_groups_rec.ATTRIBUTE15
	 ,x_created_by             => g_created_by
	 ,x_creation_date          => g_creation_date
	 ,x_last_update_login      => g_last_update_login
	 ,x_last_update_date       => g_last_update_date
	 ,x_last_updated_by        => g_last_updated_by
     ,x_org_id                 => p_role_pay_groups_rec.ORG_ID
     ,x_object_version_number  => p_role_pay_groups_rec.object_version_number);

Line: 936

	    SELECT COUNT(1)
	     INTO l_count
	     FROM cn_pay_groups
	     WHERE (l_rec.start_date NOT BETWEEN start_date AND end_date OR
		   (l_rec.end_date IS NOT NULL AND
		    l_rec.end_date NOT BETWEEN start_date AND end_date))
	     AND pay_group_id = l_pay_group_id;

Line: 950

     SELECT count(1) into l_count
       FROM cn_srp_pay_groups
	   WHERE salesrep_id = l_rec.salesrep_id
	   AND   org_id = p_role_pay_groups_rec.ORG_ID
	   AND Greatest(start_date, l_rec.start_date) <=
	          Least(Nvl(end_date, l_null_date),
			Nvl(l_rec.end_date, l_null_date));

Line: 961

	SELECT cn_srp_pay_groups_s.NEXTVAL
	  INTO l_srp_pay_group_id
	  FROM dual;

Line: 966

	  (x_operation         => 'INSERT',
	   x_srp_pay_group_id  => l_srp_pay_group_id,
	   x_salesrep_id       => l_rec.salesrep_id,
	   x_pay_group_id      => l_pay_group_id,
	   x_start_date        => l_rec.start_date,
	   x_end_date          => l_rec.end_date,
	   x_lock_flag         => l_lock_flag,
	   x_role_pay_group_id => l_role_pay_group_id,
	   x_org_id            => p_role_pay_groups_rec.org_id,
	   x_attribute_category =>p_role_pay_groups_rec.attribute_category,
	   x_attribute1        => p_role_pay_groups_rec.attribute1,
	   x_attribute2        => p_role_pay_groups_rec.attribute2,
	   x_attribute3        => p_role_pay_groups_rec.attribute3,
	   x_attribute4        => p_role_pay_groups_rec.attribute4,
	   x_attribute5        => p_role_pay_groups_rec.attribute5,
	   x_attribute6        => p_role_pay_groups_rec.attribute6,
	   x_attribute7        => p_role_pay_groups_rec.attribute7,
	   x_attribute8        => p_role_pay_groups_rec.attribute8,
	   x_attribute9        => p_role_pay_groups_rec.attribute9,
	  x_attribute10       => p_role_pay_groups_rec.attribute10,
	  x_attribute11       => p_role_pay_groups_rec.attribute11,
	  x_attribute12       => p_role_pay_groups_rec.attribute12,
	  x_attribute13       => p_role_pay_groups_rec.attribute13,
	  x_attribute14       => p_role_pay_groups_rec.attribute14,
	  x_attribute15       => p_role_pay_groups_rec.attribute15,
	  x_last_update_date  => g_last_update_date,
	  x_last_updated_by   => g_last_updated_by,
	  x_creation_date     => g_creation_date,
	  x_created_by        => g_created_by,
	  x_last_update_login => g_last_update_login,
	  x_object_version_number => l_object_version_number);

Line: 1011

		 cn_srp_plan_assigns_pvt.Update_Srp_Plan_Assigns
			(
			 p_api_version    =>    1.0,
			 p_init_msg_list  =>  FND_API.G_FALSE,
			 p_commit	  => FND_API.G_FALSE,
			 p_validation_level  => FND_API.G_VALID_LEVEL_FULL,
			 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: 1130

PROCEDURE Delete_Role_Pay_Groups
(  	p_api_version              IN	NUMBER				      ,
   	p_init_msg_list		   IN	VARCHAR2 := FND_API.G_FALSE   	      ,
	p_commit	    	   IN  	VARCHAR2 := FND_API.G_FALSE   	      ,
	p_validation_level	   IN  	NUMBER	 := FND_API.G_VALID_LEVEL_FULL,
	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_role_pay_groups_rec  IN OUT NOCOPY  role_pay_groups_rec_type
 	) IS

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

Line: 1160

              SELECT role_id, srp_role_id,start_date, nvl(end_date,l_null_date) end_date,org_id
            	FROM cn_srp_roles
      	WHERE salesrep_id = p_salesrep_id and org_id=p_org_id;

Line: 1167

      	               SELECT role_plan_id,role_id
      	                 FROM cn_role_plans
                    WHERE role_id =l_role_id and org_id=l_org_id;

Line: 1175

            	 SELECT comp_plan_id,
            	   start_date,
            	   end_date,
            	   org_id
            	   FROM cn_srp_plan_assigns
            	   WHERE role_id = p_role_id
            	   AND salesrep_id = p_salesrep_id
                   and org_id=p_org_id;

Line: 1187

                  SELECT srp_role_id,salesrep_id,start_date,end_date,org_id
                    FROM cn_srp_roles
              WHERE role_id = l_role_id
              AND salesrep_id IN (select salesrep_id from cn_srp_pay_groups where pay_group_id=l_pay_group_id)
              and org_id=l_org_id;

Line: 1199

   SAVEPOINT	delete_role_pay_groups;

Line: 1218

   x_loading_status := 'CN_DELETED';

Line: 1229

   check_valid_delete
     ( x_return_status  => x_return_status,
       x_msg_count      => x_msg_count,
       x_msg_data       => x_msg_data,
       p_role_pay_groups_rec  => p_role_pay_groups_rec,
       x_role_pay_group_id   => l_role_pay_group_id,
       p_loading_status => l_loading_status, -- in
       x_loading_status => x_loading_status  -- out
       );

Line: 1248

      cn_role_pay_groups_pkg.delete_row(x_role_pay_group_id => l_role_pay_group_id);

Line: 1253

           SAVEPOINT delete_srp_pay_groups;

Line: 1255

	   delete from cn_srp_pay_groups where salesrep_id=salesrep.salesrep_id
	     AND role_pay_group_id=l_role_pay_group_id
	     and org_id = l_org_id
	     AND lock_flag='N' AND pay_group_id=l_pay_group_id AND (start_date between p_role_pay_groups_rec.start_date AND nvl(p_role_pay_groups_rec.end_date,l_null_date)) AND
	     (nvl(end_date,l_null_date) between p_role_pay_groups_rec.start_date AND nvl(p_role_pay_groups_rec.end_date,l_null_date))
	     AND NOT EXISTS (SELECT 1 FROM cn_payment_worksheets W, cn_period_statuses prd, cn_payruns prun
			     WHERE w.salesrep_id = salesrep.salesrep_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 prun.org_id = l_org_id
			     AND ((prd.start_date BETWEEN p_role_pay_groups_rec.start_date AND nvl(p_role_pay_groups_rec.end_date,l_null_date)) OR
				  (prd.end_date between p_role_pay_groups_rec.start_date AND nvl(p_role_pay_groups_rec.end_date,l_null_date)) ));

Line: 1275

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

Line: 1289

	   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
	     where salesrep_id = salesrep.salesrep_id and org_id=salesrep.org_id;

Line: 1300

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

Line: 1328

				cn_srp_plan_assigns_pvt.Update_Srp_Plan_Assigns
				  (
				   p_api_version    =>    1.0,
				   p_init_msg_list  =>  FND_API.G_FALSE,
				   p_commit	  => FND_API.G_FALSE,
				   p_validation_level  => FND_API.G_VALID_LEVEL_FULL,
				   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,--changed this to support my new change(Harlen.Renu)
				   p_role_plan_id  => role_plans.role_plan_id,
				   x_loading_status  => x_loading_status );

Line: 1393

		Update cn_srp_pay_groups_all
		set role_pay_group_id = null
		where role_pay_group_id = l_role_pay_group_id;

Line: 1421

      ROLLBACK TO Delete_Role_Pay_Groups;

Line: 1432

      ROLLBACK TO Delete_Role_Pay_Groups;

Line: 1442

      ROLLBACK TO Delete_Role_Pay_Groups;

Line: 1455

END Delete_Role_Pay_Groups;