APPS.CN_SRP_PMT_PLANS_PUB SQL Statements

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

Line: 57

      SELECT pmt_plan_id, credit_type_id,
	     start_date, end_date
	INTO x_srp_pmt_plans_row.pmt_plan_id,
	     x_srp_pmt_plans_row.credit_type_id,
	     x_pp_start_date,x_pp_end_date
	FROM cn_pmt_plans_all
       WHERE name = p_srp_pmt_plans_rec.pmt_plan_name
         AND org_id = p_srp_pmt_plans_rec.org_id;

Line: 90

      SELECT start_date_active,end_date_active
	INTO x_srp_start_date,x_srp_end_date
        FROM cn_salesreps
	WHERE salesrep_id = x_srp_pmt_plans_row.salesrep_id
	  AND org_id = x_srp_pmt_plans_row.org_id;

Line: 109

	 SELECT *
	   INTO x_srp_pmt_plans_row
	   FROM cn_srp_pmt_plans_all
	   WHERE pmt_plan_id = x_srp_pmt_plans_row.pmt_plan_id
	   AND   salesrep_id = x_srp_pmt_plans_row.salesrep_id
           AND   trunc(start_date) = trunc(p_srp_pmt_plans_rec.start_date)
	   ;

Line: 171

       SELECT cq.quota_id,cq.credit_type_id
       FROM cn_srp_plan_assigns cspa, cn_srp_quota_assigns csqa , cn_quotas cq
       WHERE cspa.salesrep_id = l_salesrep_id
         AND cspa.start_date <= l_start_date
         --Bug 3432689 by Julia Huang on 4/29/04.
         --AND nvl(l_end_date,l_null_date) <= nvl(cspa.end_date,l_null_date)
         AND nvl(l_end_date, nvl(cspa.end_date, l_start_date)) <= nvl(cspa.end_date, nvl(l_end_date,l_start_date))
         AND csqa.srp_plan_assign_id = cspa.srp_plan_assign_id
         AND csqa.quota_id = cq.quota_id;

Line: 205

      p_action <> 'UPDATE' THEN
      IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
	 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PMT_PLAN_EXIST');

Line: 229

   SELECT minimum_amount, maximum_amount
     INTO l_pp_min, l_pp_max
     FROM cn_pmt_plans
     WHERE pmt_plan_id = x_srp_pmt_plans_row.pmt_plan_id;

Line: 234

   SELECT
     Decode(p_srp_pmt_plans_rec.minimum_amount,FND_API.G_MISS_NUM,l_pp_min,
	    p_srp_pmt_plans_rec.minimum_amount),
     Decode(p_srp_pmt_plans_rec.maximum_amount,FND_API.G_MISS_NUM,l_pp_max,
	    p_srp_pmt_plans_rec.maximum_amount)
     INTO
     x_srp_pmt_plans_row.minimum_amount,
     x_srp_pmt_plans_row.maximum_amount
     FROM dual;

Line: 312

   x_loading_status := 'CN_INSERTED';

Line: 336

   SELECT
     Decode(p_srp_pmt_plans_rec.pmt_plan_name,
	    FND_API.G_MISS_CHAR, NULL ,
	    Ltrim(Rtrim(p_srp_pmt_plans_rec.pmt_plan_name))),
     Decode(p_srp_pmt_plans_rec.salesrep_type,
	    FND_API.G_MISS_CHAR, NULL ,
	    Ltrim(Rtrim(p_srp_pmt_plans_rec.salesrep_type))),
     Decode(p_srp_pmt_plans_rec.emp_num,
	    FND_API.G_MISS_CHAR, NULL ,
	    Ltrim(Rtrim(p_srp_pmt_plans_rec.emp_num))),
     Decode(p_srp_pmt_plans_rec.start_date,
	    FND_API.G_MISS_DATE,To_date(NULL) ,
	    trunc(p_srp_pmt_plans_rec.start_date)),
     Decode(p_srp_pmt_plans_rec.end_date,
	    FND_API.G_MISS_DATE,To_date(NULL) ,
	    trunc(p_srp_pmt_plans_rec.end_date))
     INTO
     l_spp_rec.pmt_plan_name,
     l_spp_rec.salesrep_type,
     l_spp_rec.emp_num,
     l_spp_rec.start_date,
     l_spp_rec.end_date
     FROM dual;

Line: 626

PROCEDURE Update_Srp_Pmt_Plan
  (
   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_old_srp_pmt_plans_rec IN    srp_pmt_plans_rec_type,
   p_srp_pmt_plans_rec     IN    srp_pmt_plans_rec_type,
   x_loading_status     OUT NOCOPY  VARCHAR2,
   p_check_lock         IN    VARCHAR2 := NULL
   ) IS

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

Line: 656

      l_action         VARCHAR2(30) := 'UPDATE';

Line: 658

      l_update_rec      cn_srp_pmt_plans_pvt.pmt_plan_assign_rec;

Line: 669

   SAVEPOINT	Update_Srp_Pmt_Plan;

Line: 682

   x_loading_status := 'CN_UPDATED';

Line: 694

      FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');

Line: 697

			 'cn.plsql.cn_srp_pmt_plans_pub.update_srp_pmt_plan.error',
			 true);

Line: 702

	 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');

Line: 716

		     'cn.plsql.cn_srp_pmt_plans_pub.update_srp_pmt_plan.org_validate',
                     'Validated org_id = ' || l_org_id || ' status = ' || l_status);

Line: 725

   SELECT Ltrim(Rtrim(p_old_srp_pmt_plans_rec.pmt_plan_name)),
     Ltrim(Rtrim(p_old_srp_pmt_plans_rec.salesrep_type)),
     Ltrim(Rtrim(p_old_srp_pmt_plans_rec.emp_num)),
     trunc(p_old_srp_pmt_plans_rec.start_date),
     trunc(p_old_srp_pmt_plans_rec.end_date)
     INTO
     l_old_spp_rec.pmt_plan_name,
     l_old_spp_rec.salesrep_type,
     l_old_spp_rec.emp_num,
     l_old_spp_rec.start_date,
     l_old_spp_rec.end_date
     FROM dual;

Line: 767

      SELECT
	Decode(p_srp_pmt_plans_rec.pmt_plan_name,
	       FND_API.G_MISS_CHAR, l_old_spp_rec.pmt_plan_name,
	       Ltrim(Rtrim(p_srp_pmt_plans_rec.pmt_plan_name))),
	Decode(p_srp_pmt_plans_rec.salesrep_type,
	       FND_API.G_MISS_CHAR,  l_old_spp_rec.salesrep_type,
	       Ltrim(Rtrim(p_srp_pmt_plans_rec.salesrep_type))),
	Decode(p_srp_pmt_plans_rec.emp_num,
	       FND_API.G_MISS_CHAR,  l_old_spp_rec.emp_num,
	       Ltrim(Rtrim(p_srp_pmt_plans_rec.emp_num))),
	Decode(p_srp_pmt_plans_rec.start_date,
	       FND_API.G_MISS_DATE, l_old_spp_row.start_date,
	       trunc(p_srp_pmt_plans_rec.start_date)),
	Decode(p_srp_pmt_plans_rec.end_date,
	       FND_API.G_MISS_DATE, l_old_spp_row.end_date,
	       trunc(p_srp_pmt_plans_rec.end_date))
	INTO
	l_spp_rec.pmt_plan_name,
	l_spp_rec.salesrep_type,
	l_spp_rec.emp_num,
	l_spp_rec.start_date,
	l_spp_rec.end_date
	FROM dual;

Line: 812

				'UPDATE_SRP_PMT_PLAN',
				'B',
				'C')
     THEN
      cn_srp_pmt_plans_pub_cuhk.update_srp_pmt_plan_pre
	(p_api_version          => p_api_version,
	 p_init_msg_list	=> fnd_api.g_false,
	 p_commit	        => fnd_api.g_false,
	 p_validation_level	=> p_validation_level,
	 x_return_status        => x_return_status,
	 x_msg_count            => x_msg_count,
	 x_msg_data             => x_msg_data,
	 p_old_srp_pmt_plans_rec=> l_old_spp_rec,
	 p_srp_pmt_plans_rec    => l_spp_rec,
	 x_loading_status       => x_loading_status
	 );

Line: 839

				'UPDATE_SRP_PMT_PLAN',
				'B',
				'V')
     THEN
      cn_srp_pmt_plans_pub_vuhk.update_srp_pmt_plan_pre
	(p_api_version          => p_api_version,
	 p_init_msg_list	=> fnd_api.g_false,
	 p_commit	        => fnd_api.g_false,
	 p_validation_level	=> p_validation_level,
	 x_return_status        => x_return_status,
	 x_msg_count            => x_msg_count,
	 x_msg_data             => x_msg_data,
	 p_old_srp_pmt_plans_rec=> l_old_spp_rec,
	 p_srp_pmt_plans_rec    => l_spp_rec,
	 x_loading_status       => x_loading_status
	 );

Line: 866

      l_update_rec.srp_pmt_plan_id := l_old_spp_row.srp_pmt_plan_id;

Line: 867

      l_update_rec.pmt_plan_id    := l_spp_row.pmt_plan_id;

Line: 868

      l_update_rec.salesrep_id    := l_spp_row.salesrep_id;

Line: 869

      l_update_rec.org_id         := l_spp_row.org_id;

Line: 870

      l_update_rec.start_date     := l_spp_row.start_date;

Line: 871

      l_update_rec.end_date       := l_spp_row.end_date;

Line: 872

      l_update_rec.minimum_amount := l_spp_row.minimum_amount;

Line: 873

      l_update_rec.maximum_amount := l_spp_row.maximum_amount;

Line: 874

      l_update_rec.object_version_number := p_old_srp_pmt_plans_rec.object_version_number;

Line: 875

      l_update_rec.srp_role_id      := p_srp_pmt_plans_rec.srp_role_id;

Line: 876

      l_update_rec.role_pmt_plan_id := p_srp_pmt_plans_rec.role_pmt_plan_id;

Line: 877

      l_update_rec.lock_flag        := p_srp_pmt_plans_rec.lock_flag;

Line: 879

      cn_srp_pmt_plans_pvt.update_srp_pmt_plan
	(  p_api_version              => p_api_version,
	   p_init_msg_list	      => fnd_api.g_false,
	   p_commit	              => fnd_api.g_false,
	   p_validation_level         => p_validation_level,
	   x_return_status	      => x_return_status,
	   x_loading_status           => x_loading_status,
	   x_msg_count		      => x_msg_count,
	   x_msg_data		      => x_msg_data,
	   p_pmt_plan_assign_rec      => l_update_rec);

Line: 903

				'UPDATE_SRP_PMT_PLAN',
				'A',
				'V')
     THEN
      cn_srp_pmt_plans_pub_vuhk.update_srp_pmt_plan_post
	(p_api_version          => p_api_version,
	 p_init_msg_list        => fnd_api.g_false,
	 p_commit	        => fnd_api.g_false,
	 p_validation_level	=> p_validation_level,
	 x_return_status        => x_return_status,
	 x_msg_count            => x_msg_count,
	 x_msg_data             => x_msg_data,
	 p_old_srp_pmt_plans_rec=> l_old_spp_rec,
	 p_srp_pmt_plans_rec    => l_spp_rec,
	 x_loading_status       => x_loading_status
	 );

Line: 930

				'UPDATE_SRP_PMT_PLAN',
				'A',
				'C')
     THEN
      cn_srp_pmt_plans_pub_cuhk.update_srp_pmt_plan_post
	(p_api_version          => p_api_version,
	 p_init_msg_list        => fnd_api.g_false,
	 p_commit	        => fnd_api.g_false,
	 p_validation_level	=> p_validation_level,
	 x_return_status        => x_return_status,
	 x_msg_count            => x_msg_count,
	 x_msg_data             => x_msg_data,
	 p_old_srp_pmt_plans_rec=> l_old_spp_rec,
	 p_srp_pmt_plans_rec    => l_spp_rec,
	 x_loading_status       => x_loading_status
	 );

Line: 1017

      ROLLBACK TO Update_Srp_Pmt_Plan  ;

Line: 1026

      ROLLBACK TO Update_Srp_Pmt_Plan;

Line: 1036

      ROLLBACK TO Update_Srp_Pmt_Plan;

Line: 1050

END Update_Srp_Pmt_Plan;

Line: 1057

PROCEDURE Delete_Srp_Pmt_Plan
  (
   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_pmt_plans_rec  IN    srp_pmt_plans_rec_type,
   x_loading_status     OUT NOCOPY  VARCHAR2,
   p_check_lock         IN  VARCHAR2 := NULL
   ) IS

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

Line: 1092

   SAVEPOINT	Delete_Srp_Pmt_Plan;

Line: 1105

   x_loading_status := 'CN_DELETED';

Line: 1123

		     'cn.plsql.cn_srp_pmt_plans_pub.delete_srp_pmt_plan.org_validate',
                     'Validated org_id = ' || l_org_id || ' status = ' || l_status);

Line: 1131

   SELECT Ltrim(Rtrim(p_srp_pmt_plans_rec.pmt_plan_name)),
     Ltrim(Rtrim(p_srp_pmt_plans_rec.salesrep_type)),
     Ltrim(Rtrim(p_srp_pmt_plans_rec.emp_num)),
     trunc(p_srp_pmt_plans_rec.start_date),
     trunc(p_srp_pmt_plans_rec.end_date)
     INTO
     l_spp_rec.pmt_plan_name,
     l_spp_rec.salesrep_type,
     l_spp_rec.emp_num,
     l_spp_rec.start_date,
     l_spp_rec.end_date
     FROM dual;

Line: 1173

				'DELETE_SRP_PMT_PLAN',
				'B',
				'C')
     THEN
      cn_srp_pmt_plans_pub_cuhk.delete_srp_pmt_plan_pre
	(p_api_version          => p_api_version,
	 p_init_msg_list        => fnd_api.g_false,
	 p_commit	        => fnd_api.g_false,
	 p_validation_level	=> p_validation_level,
	 x_return_status        => x_return_status,
	 x_msg_count            => x_msg_count,
	 x_msg_data             => x_msg_data,
	 p_srp_pmt_plans_rec    => l_spp_rec,
	 x_loading_status       => x_loading_status
	 );

Line: 1198

				'DELETE_SRP_PMT_PLAN',
				'B',
				'V')
     THEN
      cn_srp_pmt_plans_pub_vuhk.delete_srp_pmt_plan_pre
	(p_api_version          => p_api_version,
	 p_init_msg_list        => fnd_api.g_false,
	 p_commit	        => fnd_api.g_false,
	 p_validation_level	=> p_validation_level,
	 x_return_status        => x_return_status,
	 x_msg_count            => x_msg_count,
	 x_msg_data             => x_msg_data,
	 p_srp_pmt_plans_rec    => l_spp_rec,
	 x_loading_status       => x_loading_status
	 );

Line: 1224

   cn_srp_pmt_plans_pvt.delete_srp_pmt_plan
     (
      p_api_version        => p_api_version,
      p_init_msg_list      => fnd_api.g_false,
      p_commit	           => fnd_api.g_false,
      p_validation_level   => p_validation_level,
      x_return_status      => x_return_status,
      x_loading_status     => x_loading_status,
      x_msg_count          => x_msg_count,
      x_msg_data           => x_msg_data,
      p_srp_pmt_plan_id    => l_spp_row.srp_pmt_plan_id);

Line: 1248

				'DELETE_SRP_PMT_PLAN',
				'A',
				'V')
     THEN
      cn_srp_pmt_plans_pub_vuhk.delete_srp_pmt_plan_post
	(p_api_version          => p_api_version,
	 p_init_msg_list        => fnd_api.g_false,
	 p_commit	        => fnd_api.g_false,
	 p_validation_level	=> p_validation_level,
	 x_return_status        => x_return_status,
	 x_msg_count            => x_msg_count,
	 x_msg_data             => x_msg_data,
	 p_srp_pmt_plans_rec    => l_spp_rec,
	 x_loading_status       => x_loading_status
	 );

Line: 1274

				'DELETE_SRP_PMT_PLAN',
				'A',
				'C')
     THEN
      cn_srp_pmt_plans_pub_cuhk.delete_srp_pmt_plan_post
	(p_api_version          => p_api_version,
	 p_init_msg_list        => fnd_api.g_false,
	 p_commit	        => fnd_api.g_false,
	 p_validation_level	=> p_validation_level,
	 x_return_status        => x_return_status,
	 x_msg_count            => x_msg_count,
	 x_msg_data             => x_msg_data,
	 p_srp_pmt_plans_rec    => l_spp_rec,
	 x_loading_status       => x_loading_status
	 );

Line: 1302

				'DELETE_SRP_PMT_PLAN',
				'M',
				'M')
     THEN
      IF  cn_srp_pmt_plans_pub_cuhk.ok_to_generate_msg
	 (p_srp_pmt_plans_rec        => l_spp_rec)
	THEN
	 -- Clear bind variables
--	 XMLGEN.clearBindValues;

Line: 1359

      ROLLBACK TO Delete_Srp_Pmt_Plan  ;

Line: 1368

      ROLLBACK TO Delete_Srp_Pmt_Plan;

Line: 1378

      ROLLBACK TO Delete_Srp_Pmt_Plan;

Line: 1392

END Delete_Srp_Pmt_Plan;

Line: 1432

PROCEDURE Update_Mass_Asgn_Srp_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_msg_count	        OUT NOCOPY  NUMBER,
   x_msg_data	        OUT NOCOPY  VARCHAR2,
   p_srp_role_id        IN    NUMBER,
   p_role_pmt_plan_id   IN    NUMBER,
   x_loading_status     OUT NOCOPY  VARCHAR2
   ) IS
BEGIN
   cn_srp_pmt_plans_pvt.update_mass_asgn_srp_pmt_plan
     (p_api_version      => p_api_version,
      p_init_msg_list    => p_init_msg_list,
      p_commit           => p_commit,
      p_validation_level => p_validation_level,
      x_return_status    => x_return_status,
      x_msg_count        => x_msg_count,
      x_msg_data         => x_msg_data,
      p_srp_role_id      => p_srp_role_id,
      p_role_pmt_plan_id => p_role_pmt_plan_id,
      x_loading_status   => x_loading_status);

Line: 1457

END update_mass_asgn_srp_pmt_plan;

Line: 1464

PROCEDURE Delete_Mass_Asgn_Srp_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_msg_count	        OUT NOCOPY  NUMBER,
   x_msg_data	        OUT NOCOPY  VARCHAR2,
   p_srp_role_id        IN    NUMBER,
   p_role_pmt_plan_id   IN    NUMBER,
   x_loading_status     OUT NOCOPY  VARCHAR2
   ) IS
BEGIN
   cn_srp_pmt_plans_pvt.delete_mass_asgn_srp_pmt_plan
     (p_api_version      => p_api_version,
      p_init_msg_list    => p_init_msg_list,
      p_commit           => p_commit,
      p_validation_level => p_validation_level,
      x_return_status    => x_return_status,
      x_msg_count        => x_msg_count,
      x_msg_data         => x_msg_data,
      p_srp_role_id      => p_srp_role_id,
      p_role_pmt_plan_id => p_role_pmt_plan_id,
      x_loading_status   => x_loading_status);

Line: 1489

END Delete_Mass_Asgn_Srp_Pmt_Plan;