APPS.CN_ROLE_PMT_PLANS_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: 26

	 SELECT *
	   FROM cn_roles
	   WHERE name = l_role_name;

Line: 57

	 SELECT *
	   FROM cn_pmt_plans
	   WHERE pmt_plan_id = l_pmt_plan_id
	   AND org_id = l_org_id;

Line: 110

PROCEDURE check_valid_insert
  (
   x_return_status          OUT NOCOPY VARCHAR2,
   x_msg_count              OUT NOCOPY NUMBER,
   x_msg_data               OUT NOCOPY VARCHAR2,
   p_role_pmt_plan_rec      IN  role_pmt_plan_rec_type,
   x_role_id                OUT NOCOPY cn_roles.role_id%TYPE,
   x_pmt_plan_id            OUT NOCOPY cn_pmt_plans.pmt_plan_id%TYPE,
   p_loading_status         IN  VARCHAR2,
   x_loading_status         OUT NOCOPY VARCHAR2
   ) IS

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

Line: 125

	 SELECT start_date, end_date, pmt_plan_id
	   FROM cn_role_pmt_plans
	   WHERE role_id = l_role_id
       AND org_id = l_org_id;

Line: 131

	 SELECT start_date, end_date
	   FROM cn_pmt_plans
	   WHERE pmt_plan_id = l_pmt_plan_id
	   AND org_id = l_org_id;

Line: 270

        select payment_group_code into
        l_payment_group_code
	    from cn_pmt_plans
        where pmt_plan_id = l_rec.pmt_plan_id;

Line: 275

       select payment_group_code into
       l_pp_payment_group_code
	   from cn_pmt_plans
	   where pmt_plan_id = p_role_pmt_plan_rec.pmt_plan_id
	   and org_id = p_role_pmt_plan_rec.org_id;

Line: 344

END check_valid_insert;

Line: 352

PROCEDURE check_valid_update
  (
   x_return_status          OUT NOCOPY VARCHAR2,
   x_msg_count              OUT NOCOPY NUMBER,
   x_msg_data               OUT NOCOPY VARCHAR2,
   p_role_pmt_plan_rec_old      IN  role_pmt_plan_rec_type,
   p_role_pmt_plan_rec_new      IN  role_pmt_plan_rec_type,
   x_role_pmt_plan_id_old       OUT NOCOPY cn_role_pmt_plans.role_pmt_plan_id%TYPE,
   x_role_id                OUT NOCOPY cn_roles.role_id%TYPE,
   x_pmt_plan_id           OUT NOCOPY cn_pmt_plans.pmt_plan_id%TYPE,
   x_date_update_only       OUT NOCOPY VARCHAR2,
   p_loading_status         IN  VARCHAR2,
   x_loading_status         OUT NOCOPY VARCHAR2
   ) IS

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

Line: 373

	 SELECT start_date, end_date, pmt_plan_id
	   FROM cn_role_pmt_plans
	   WHERE role_id = l_role_id AND
	   role_pmt_plan_id <> l_role_pmt_plan_id;

Line: 379

	 SELECT *
	   FROM cn_role_pmt_plans
	   WHERE role_pmt_plan_id = l_role_pmt_plan_id;

Line: 386

	 SELECT start_date, end_date
	   FROM cn_pmt_plans
	   WHERE pmt_plan_id = l_pmt_plan_id;

Line: 649

	 x_date_update_only := FND_API.G_FALSE;

Line: 651

	 x_date_update_only := FND_API.G_TRUE;

Line: 697

END check_valid_update;

Line: 705

PROCEDURE check_valid_delete
  (
   x_return_status          OUT NOCOPY VARCHAR2,
   x_msg_count              OUT NOCOPY NUMBER,
   x_msg_data               OUT NOCOPY VARCHAR2,
   p_role_pmt_plan_rec          IN  role_pmt_plan_rec_type,
   x_role_pmt_plan_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: 789

END check_valid_delete;

Line: 795

PROCEDURE srp_pmt_plan_asgn_for_insert
  (p_role_id        IN cn_roles.role_id%TYPE,
   p_role_pmt_plan_id   IN cn_role_pmt_plans.role_pmt_plan_id%TYPE,
   p_suppress_flag IN VARCHAR2 := 'N',
   x_return_status  OUT NOCOPY VARCHAR2,
   p_loading_status IN  VARCHAR2,
   x_loading_status OUT NOCOPY VARCHAR2 ) IS

      /* CURSOR l_cur IS
      select sr.srp_role_id                srp_role_id,
             nvl(srd.job_title_id, G_MISS_JOB_TITLE) job_title_id,
	     nvl(srd.plan_activate_status, 'NOT_PUSHED') push_status
	from cn_srp_roles                  sr,
	     cn_srp_role_dtls              srd
       where role_id                     = p_role_id
         and srd.role_model_id is NULL
         -- CHANGED FOR MODELING IMPACT
	 and sr.srp_role_id              = srd.srp_role_id(+);*/

Line: 818

      select *
	from cn_srp_roles
       where role_id                     = p_role_id
           ;

Line: 824

      select csr.*
	from cn_srp_roles csr, cn_salesreps cs
       where csr.role_id = p_role_id
       and csr.salesrep_id = cs.salesrep_id
       and csr.org_id = cs.org_id
       and csr.org_id = l_org_id;

Line: 847

     select org_id into l_org_id
     from cn_role_pmt_plans
     where role_pmt_plan_id = p_role_pmt_plan_id;

Line: 855

	   --	dbms_output.put_line('insert into cn_srp_pmt_plans...');

Line: 897

END srp_pmt_plan_asgn_for_insert;

Line: 903

PROCEDURE srp_pmt_plan_asgn_for_update
  (p_role_pmt_plan_id     IN  cn_role_pmt_plans.role_pmt_plan_id%TYPE,
   p_role_id              IN  cn_roles.role_id%TYPE,
   p_date_update_only IN  VARCHAR2,
   x_return_status    OUT NOCOPY VARCHAR2,
   p_loading_status   IN  VARCHAR2,
   x_loading_status   OUT NOCOPY VARCHAR2 ) IS


      /* CURSOR l_cur IS
      select sr.srp_role_id                srp_role_id,
             nvl(srd.job_title_id, G_MISS_JOB_TITLE) job_title_id,
	     nvl(srd.plan_activate_status, 'NOT_PUSHED') push_status
	from cn_srp_roles                  sr,
	     cn_srp_role_dtls              srd
       where role_id                     = p_role_id
         and srd.role_model_id is NULL
         -- CHANGED FOR MODELING IMPACT
	 and sr.srp_role_id              = srd.srp_role_id(+);*/

Line: 924

        select srp_role_id
        from cn_srp_roles
        where role_id = p_role_id
        and org_id = l_org_id;

Line: 950

   select org_id into l_org_id
   from cn_role_pmt_plans
   where role_pmt_plan_id = p_role_pmt_plan_id;

Line: 956

	CN_SRP_PMT_PLANS_PUB.update_mass_asgn_srp_pmt_plan
	      (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_pmt_plan_id       => p_role_pmt_plan_id,
	       x_loading_status         => l_loading_status);

Line: 972

END srp_pmt_plan_asgn_for_update;

Line: 978

PROCEDURE srp_pmt_plan_asgn_for_delete
  (p_role_id            IN cn_roles.role_id%TYPE,
   p_role_pmt_plan_id   IN cn_role_pmt_plans.role_pmt_plan_id%TYPE,
   p_suppress_flag IN VARCHAR2 := 'N',
   x_return_status  OUT NOCOPY VARCHAR2,
   p_loading_status IN  VARCHAR2,
   x_loading_status OUT NOCOPY VARCHAR2 ) IS

    CURSOR l_cur (l_org_id cn_role_pmt_plans.org_id%TYPE) IS
	 SELECT srp_role_id
	   FROM cn_srp_roles
	   WHERE role_id = p_role_id
	   AND org_id = l_org_id;

Line: 1007

   select org_id into l_org_id
   from cn_role_pmt_plans
   where role_pmt_plan_id = p_role_pmt_plan_id;

Line: 1014

	CN_SRP_PMT_PLANS_PUB.delete_mass_asgn_srp_pmt_plan
	  (
	   p_api_version        => 1.0,
	   p_init_msg_list      => fnd_api.g_true,
	   p_commit             => fnd_api.g_true,
	   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_pmt_plan_id   => p_role_pmt_plan_id,
	   x_loading_status     => l_loading_status);

Line: 1054

END srp_pmt_plan_asgn_for_delete;

Line: 1106

   x_loading_status := 'CN_INSERTED';

Line: 1115

   check_valid_insert
     ( x_return_status  => x_return_status,
       x_msg_count      => x_msg_count,
       x_msg_data       => x_msg_data,
       p_role_pmt_plan_rec  => p_role_pmt_plan_rec,
       x_role_id        => l_role_id,
       x_pmt_plan_id   => l_pmt_plan_id,
       p_loading_status => x_loading_status, -- in
       x_loading_status => x_loading_status  -- out
       );

Line: 1130

      SELECT cn_role_pmt_plans_s.NEXTVAL INTO l_role_pmt_plan_id
	FROM dual;

Line: 1133

      cn_role_pmt_plans_pkg.INSERT_ROW
	(
	 x_org_id		   => p_role_pmt_plan_rec.org_id
	 ,x_role_pmt_plan_id 	   => l_role_pmt_plan_id
	 ,x_role_id                => l_role_id
	 ,x_pmt_plan_id            => l_pmt_plan_id
	 ,x_start_date             => p_role_pmt_plan_rec.start_date
	 ,x_end_date               => p_role_pmt_plan_rec.end_date
	 ,x_attribute_category     => p_role_pmt_plan_rec.ATTRIBUTE_CATEGORY
	 ,x_attribute1             => p_role_pmt_plan_rec.ATTRIBUTE1
	 ,x_attribute2             => p_role_pmt_plan_rec.ATTRIBUTE2
	 ,x_attribute3             => p_role_pmt_plan_rec.ATTRIBUTE3
	 ,x_attribute4             => p_role_pmt_plan_rec.ATTRIBUTE4
	 ,x_attribute5             => p_role_pmt_plan_rec.ATTRIBUTE5
	 ,x_attribute6             => p_role_pmt_plan_rec.ATTRIBUTE6
	 ,x_attribute7             => p_role_pmt_plan_rec.ATTRIBUTE7
	 ,x_attribute8             => p_role_pmt_plan_rec.ATTRIBUTE8
	 ,x_attribute9             => p_role_pmt_plan_rec.ATTRIBUTE9
	 ,x_attribute10            => p_role_pmt_plan_rec.ATTRIBUTE10
	 ,x_attribute11            => p_role_pmt_plan_rec.ATTRIBUTE11
	 ,x_attribute12            => p_role_pmt_plan_rec.ATTRIBUTE12
	 ,x_attribute13            => p_role_pmt_plan_rec.ATTRIBUTE13
	 ,x_attribute14            => p_role_pmt_plan_rec.ATTRIBUTE14
	 ,x_attribute15            => p_role_pmt_plan_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);

Line: 1165

      srp_pmt_plan_asgn_for_insert(p_role_id        => l_role_id,
		   		   p_role_pmt_plan_id   => l_role_pmt_plan_id,
		   		   p_suppress_flag => 'Y',
				   x_return_status  => x_return_status,
				   p_loading_status => x_loading_status,
				   x_loading_status => x_loading_status);

Line: 1238

PROCEDURE Update_Role_Pmt_Plan
(  	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_pmt_plan_rec_old    IN   role_pmt_plan_rec_type := G_MISS_ROLE_PMT_PLAN_REC,
        p_ovn                      IN   cn_role_pmt_plans.object_version_number%TYPE,
	p_role_pmt_plan_rec_new    IN   role_pmt_plan_rec_type := G_MISS_ROLE_PMT_PLAN_REC
	) IS

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

Line: 1257

      l_date_update_only           VARCHAR2(1);

Line: 1268

   SAVEPOINT	update_role_pmt_plan;

Line: 1287

   x_loading_status := 'CN_UPDATED';

Line: 1298

   check_valid_update
     ( x_return_status       => x_return_status,
       x_msg_count           => x_msg_count,
       x_msg_data            => x_msg_data,
       p_role_pmt_plan_rec_old   => p_role_pmt_plan_rec_old,
       p_role_pmt_plan_rec_new   => p_role_pmt_plan_rec_new,
       x_role_pmt_plan_id_old    => l_role_pmt_plan_id_old,
       x_role_id             => l_role_id,
       x_pmt_plan_id        => l_pmt_plan_id,
       x_date_update_only    => l_date_update_only,
       p_loading_status      => x_loading_status, -- in
       x_loading_status      => x_loading_status  -- out
       );

Line: 1318

      cn_role_pmt_plans_pkg.UPDATE_ROW
	(
	 x_org_id			=> p_role_pmt_plan_rec_new.org_id
	 ,x_role_pmt_plan_id            => l_role_pmt_plan_id_old
	 ,x_role_id                => l_role_id
	 ,x_pmt_plan_id            => l_pmt_plan_id
	 ,x_start_date             => p_role_pmt_plan_rec_new.start_date
	 ,x_end_date               => p_role_pmt_plan_rec_new.end_date
	 ,x_attribute_category     => p_role_pmt_plan_rec_new.ATTRIBUTE_CATEGORY
	 ,x_attribute1             => p_role_pmt_plan_rec_new.ATTRIBUTE1
	 ,x_attribute2             => p_role_pmt_plan_rec_new.ATTRIBUTE2
	 ,x_attribute3             => p_role_pmt_plan_rec_new.ATTRIBUTE3
	 ,x_attribute4             => p_role_pmt_plan_rec_new.ATTRIBUTE4
	 ,x_attribute5             => p_role_pmt_plan_rec_new.ATTRIBUTE5
	 ,x_attribute6             => p_role_pmt_plan_rec_new.ATTRIBUTE6
	 ,x_attribute7             => p_role_pmt_plan_rec_new.ATTRIBUTE7
	 ,x_attribute8             => p_role_pmt_plan_rec_new.ATTRIBUTE8
	 ,x_attribute9             => p_role_pmt_plan_rec_new.ATTRIBUTE9
	 ,x_attribute10            => p_role_pmt_plan_rec_new.ATTRIBUTE10
	 ,x_attribute11            => p_role_pmt_plan_rec_new.ATTRIBUTE11
	 ,x_attribute12            => p_role_pmt_plan_rec_new.ATTRIBUTE12
	 ,x_attribute13            => p_role_pmt_plan_rec_new.ATTRIBUTE13
	 ,x_attribute14            => p_role_pmt_plan_rec_new.ATTRIBUTE14
	 ,x_attribute15            => p_role_pmt_plan_rec_new.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_object_version_number  => p_ovn);

Line: 1356

      srp_pmt_plan_asgn_for_update(p_role_pmt_plan_id     => l_role_pmt_plan_id_old,
                                   p_role_id              => l_role_id,
				   p_date_update_only => l_date_update_only,
				   x_return_status    => x_return_status,
				   p_loading_status   => x_loading_status,
				   x_loading_status   => x_loading_status);

Line: 1388

      ROLLBACK TO update_role_pmt_plan;

Line: 1397

      ROLLBACK TO update_role_pmt_plan;

Line: 1407

      ROLLBACK TO update_role_pmt_plan;

Line: 1421

END update_role_pmt_plan;

Line: 1427

PROCEDURE Delete_Role_Pmt_Plan
(  	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_pmt_plan_rec            IN   role_pmt_plan_rec_type := G_MISS_ROLE_PMT_PLAN_REC
 	) IS

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

Line: 1453

   SAVEPOINT	delete_role_pmt_plan;

Line: 1472

   x_loading_status := 'CN_DELETED';

Line: 1481

   check_valid_delete
     ( x_return_status  => x_return_status,
       x_msg_count      => x_msg_count,
       x_msg_data       => x_msg_data,
       p_role_pmt_plan_rec  => p_role_pmt_plan_rec,
       x_role_pmt_plan_id   => l_role_pmt_plan_id,
       p_loading_status => x_loading_status, -- in
       x_loading_status => x_loading_status  -- out
       );

Line: 1498

      srp_pmt_plan_asgn_for_delete(p_role_id        => l_role_id,
				   p_role_pmt_plan_id   => l_role_pmt_plan_id,
				   p_suppress_flag => 'Y',
				   x_return_status  => x_return_status,
				   p_loading_status => x_loading_status,
				   x_loading_status => x_loading_status);

Line: 1510

      cn_role_pmt_plans_pkg.delete_row(x_role_pmt_plan_id => l_role_pmt_plan_id);

Line: 1534

      ROLLBACK TO delete_role_pmt_plan;

Line: 1543

      ROLLBACK TO delete_role_pmt_plan;

Line: 1553

      ROLLBACK TO delete_role_pmt_plan;

Line: 1566

END delete_role_pmt_plan;