APPS.CN_MULTI_RATE_SCHEDULES_PVT SQL Statements

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

Line: 22

	SELECT 1
	  FROM cn_rate_schedules
	  WHERE name = p_name
	    AND (p_rate_schedule_id IS NULL OR p_rate_schedule_id <> rate_schedule_id)
          --R12 MOAC Changes--Start
          AND  org_id = p_org_id;

Line: 100

	SELECT 'USED'
	  FROM dual
	  WHERE (exists (SELECT 1
			FROM cn_rt_formula_asgns
			WHERE rate_schedule_id = p_rate_schedule_id))
	  OR (exists (SELECT 1
		     FROM cn_rt_quota_asgns
		     WHERE rate_schedule_id = p_rate_schedule_id));

Line: 146

   select s.name, rqa.start_date, rqa.end_date, s.commission_unit_code, s.rate_schedule_id
     from cn_rate_schedules s, cn_rt_quota_asgns rqa
    where s.rate_schedule_id = rqa.rate_schedule_id
      and rqa.rt_quota_asgn_id = p_rt_quota_asgn_id;

Line: 153

   select s.name,null,null,s.commission_unit_code, s.rate_schedule_id
     from cn_rate_schedules s
    where s.rate_schedule_id =p_rate_schedule_id;

Line: 158

   select d.name, d.rate_dimension_id, d.number_tier
     from cn_rate_sch_dims rsd, cn_rate_dimensions d
    where rate_schedule_id = p_rate_schedule_id
      and rsd.rate_dimension_id = d.rate_dimension_id
    order by rsd.rate_dim_sequence desc;

Line: 165

select decode(rd.dim_unit_code,
              'AMOUNT', rdt.minimum_amount || ' - ' || rdt.maximum_amount,
              'PERCENT', rdt.minimum_amount * 100 || '% - ' || rdt.maximum_amount * 100 || '%',
              'STRING', rdt.string_value,
              'EXPRESSION', e1.name || ' - ' || e2.name) tier
  from cn_rate_dim_tiers_all rdt, cn_rate_dimensions_all rd, cn_calc_sql_exps e1, cn_calc_sql_exps e2
 where rdt.rate_dimension_id = p_rate_dimension_id
   and rdt.tier_sequence = p_sequence
   and rd.rate_dimension_id = rdt.rate_dimension_id
   and rdt.min_exp_id = e1.calc_sql_exp_id(+)
   and rdt.max_exp_id = e2.calc_sql_exp_id(+);

Line: 256

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

Line: 270

  l_list.DELETE;

Line: 369

   cn_multi_rate_schedules_pkg.insert_row(x_rate_schedule_id     => x_rate_schedule_id,
					  x_name                 => p_name,
					  x_commission_unit_code => p_commission_unit_code,
					  x_number_dim           => l_number_dim,
                      --R12 MOAC Changes--Start
                      x_org_id               => p_org_id);

Line: 380

      select name into l_rate_sch_name
      from   cn_rate_schedules
      where  rate_schedule_id = x_rate_schedule_id;

Line: 409

	 cn_rate_sch_dims_pkg.insert_row(x_rate_sch_dim_id     => l_temp_id,
					 x_rate_dimension_id   => p_dims_tbl(i).rate_dimension_id,
					 x_rate_schedule_id    => x_rate_schedule_id,
					 x_rate_dim_sequence   => p_dims_tbl(i).rate_dim_sequence,
                     --R12 MOAC Changes--Start
                     x_org_id              => p_org_id);

Line: 505

PROCEDURE Update_Schedule
  (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,
   p_rate_schedule_id           IN      CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE,
   p_name                       IN      CN_RATE_SCHEDULES.NAME%TYPE,
   p_commission_unit_code       IN      CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE,
   p_number_dim                 IN      CN_RATE_SCHEDULES.NUMBER_DIM%TYPE,  -- not used
   --R12 MOAC Changes--Start
   p_org_id                     IN      CN_RATE_SCHEDULES.ORG_ID%TYPE,   --new
   p_object_version_number      IN OUT NOCOPY CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE, -- Changed
   --R12 MOAC Changes--End
   p_dims_tbl                   IN      dims_tbl_type := g_miss_dims_tbl,
   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) := 'Update_Schedule';

Line: 530

     l_delete_flag                 VARCHAR2(1);

Line: 543

	SELECT commission_unit_code, number_dim
	  FROM cn_rate_schedules
	  WHERE rate_schedule_id = p_rate_schedule_id;

Line: 548

	SELECT rate_sch_dim_id
	  FROM cn_rate_sch_dims
	  WHERE rate_schedule_id = p_rate_schedule_id;

Line: 553

   SAVEPOINT   Update_Schedule;

Line: 604

   select name into l_rate_sch_old
   from   cn_rate_schedules
   where  rate_schedule_id = p_rate_schedule_id;

Line: 608

   select commission_unit_code into l_type_old
   from   cn_rate_schedules
   where  rate_schedule_id = p_rate_schedule_id;

Line: 623

      delete from cn_rate_sch_dims where rate_schedule_id = p_rate_schedule_id;

Line: 624

      delete from cn_rate_tiers    where rate_schedule_id = p_rate_schedule_id;

Line: 629

	 cn_rate_sch_dims_pkg.insert_row
	   (x_rate_sch_dim_id     => l_temp_id,
	    x_rate_dimension_id   => p_dims_tbl(i).rate_dimension_id,
	    x_rate_schedule_id    => p_rate_schedule_id,
	    x_rate_dim_sequence   => p_dims_tbl(i).rate_dim_sequence,
          --R12 MOAC Changes--Start
          x_org_id             => p_org_id
          --R12 MOAC Changes--End
         );

Line: 641

      delete from cn_rate_tiers where rate_schedule_id = p_rate_schedule_id;

Line: 654

   cn_multi_rate_schedules_pkg.update_row
     (x_rate_schedule_id      => p_rate_schedule_id,
      x_name                  => p_name,
      x_commission_unit_code  => p_commission_unit_code,
      x_number_dim            => l_number_dim,
      x_object_version_number => p_object_version_number);

Line: 668

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

Line: 691

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

Line: 746

      ROLLBACK TO Update_Schedule;

Line: 753

      ROLLBACK TO Update_Schedule;

Line: 760

      ROLLBACK TO Update_Schedule;

Line: 773

END Update_Schedule;

Line: 802

PROCEDURE Delete_Schedule
  (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,
   p_rate_schedule_id           IN      CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE,
   --R12 MOAC Changes--Start
   p_object_version_number      IN      CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE, -- new
   --R12 MOAC Changes--End
   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) := 'Delete_Schedule';

Line: 827

   SAVEPOINT   Delete_Schedule;

Line: 857

   SELECT org_id INTO l_org_id
   FROM   cn_rate_schedules
   WHERE  rate_schedule_id = p_rate_schedule_id;

Line: 861

   SELECT name INTO l_rate_sch_name
   FROM   cn_rate_schedules
   WHERE  rate_schedule_id = p_rate_schedule_id;

Line: 867

   cn_multi_rate_schedules_pkg.delete_row(p_rate_schedule_id);

Line: 873

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

Line: 883

                            p_source_object_code      => 'CN_DELETED_OBJECTS',
                            p_notes                   => l_note_msg,
                            p_notes_detail            => l_note_msg,
                            p_note_type               => 'CN_SYSGEN', -- for system generated
                            x_jtf_note_id             => l_note_id -- returned
                           );

Line: 908

      ROLLBACK TO Delete_Schedule;

Line: 915

      ROLLBACK TO Delete_Schedule;

Line: 922

      ROLLBACK TO Delete_Schedule;

Line: 935

END Delete_Schedule;

Line: 967

PROCEDURE delete_dimension_assign
  (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,
   p_rate_sch_dim_id            IN      CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE,
   p_rate_schedule_id           IN      CN_RATE_SCH_DIMS.RATE_SCHEDULE_ID%TYPE,
   --R12 MOAC Changes--Start
   p_object_version_number      IN      CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE, -- new
   --R12 MOAC Changes--End
   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) := 'Delete_Dimension_Assign';

Line: 997

   SAVEPOINT   Delete_Dimension_Assign;

Line: 1028

      SELECT rate_dim_sequence
	INTO l_rate_dim_sequence
	FROM cn_rate_sch_dims
       WHERE rate_schedule_id = p_rate_schedule_id
	 AND rate_sch_dim_id  = p_rate_sch_dim_id;

Line: 1035

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

Line: 1040

   delete_rate_tiers(p_rate_schedule_id  => p_rate_schedule_id,
		     p_rate_dim_sequence => l_rate_dim_sequence);

Line: 1045

   SELECT org_id,rate_dimension_id INTO l_org_id,l_dimension_id
   FROM   cn_rate_sch_dims
   WHERE  rate_sch_dim_id  = p_rate_sch_dim_id;

Line: 1049

   select name into l_dimension_name_old
   from   cn_rate_dimensions
   where  rate_dimension_id = l_dimension_id;

Line: 1053

   select name into l_rate_sch_name_old
   from   cn_rate_schedules
   where  rate_schedule_id = p_rate_schedule_id;

Line: 1060

   cn_rate_sch_dims_pkg.delete_row(x_rate_sch_dim_id   => p_rate_sch_dim_id);

Line: 1066

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

Line: 1091

   UPDATE cn_rate_schedules
      SET number_dim = (select count(*) from cn_rate_sch_dims
		         where rate_schedule_id = p_rate_schedule_id)
    WHERE rate_schedule_id = p_rate_schedule_id;

Line: 1114

      ROLLBACK TO Delete_Dimension_Assign;

Line: 1121

      ROLLBACK TO Delete_Dimension_Assign;

Line: 1128

      ROLLBACK TO Delete_Dimension_Assign;

Line: 1141

END Delete_Dimension_Assign;

Line: 1171

PROCEDURE update_dimension_assign
  (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,
   p_rate_sch_dim_id            IN      CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE,
   p_rate_schedule_id           IN      CN_RATE_SCH_DIMS.RATE_SCHEDULE_ID%TYPE,
   p_rate_dimension_id          IN      CN_RATE_SCH_DIMS.RATE_DIMENSION_ID%TYPE := cn_api.g_miss_num,
   p_rate_dim_sequence          IN      CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE := cn_api.g_miss_num, -- not used
   --R12 MOAC Changes--Start
   p_org_id                     IN      CN_RATE_SCHEDULES.ORG_ID%TYPE,   --new
   p_object_version_number      IN OUT NOCOPY CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE, --changed
   --R12 MOAC Changes--End
   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) := 'Update_Dimension_Assign';

Line: 1206

	SELECT rate_dimension_id, rate_dim_sequence
	  FROM cn_rate_sch_dims
	  WHERE rate_sch_dim_id = p_rate_sch_dim_id;

Line: 1211

	SELECT number_tier
	  FROM cn_rate_dimensions
	  WHERE rate_dimension_id = l_rate_dimension_id_old;

Line: 1216

	SELECT number_tier
	  FROM cn_rate_dimensions
	  WHERE rate_dimension_id = p_rate_dimension_id;

Line: 1221

   SAVEPOINT   Update_Dimension_Assign;

Line: 1266

      select count(*) into l_count from cn_rate_sch_dims
       where rate_schedule_id  = p_rate_schedule_id
	 and rate_dimension_id = p_rate_dimension_id;

Line: 1285

	 delete_rate_tiers(p_rate_schedule_id   => p_rate_schedule_id,
			   p_rate_dim_sequence  => l_rate_dim_sequence,
			   p_tier_sequence      => l_number_tier_new + 1,
			   p_num_tiers          => l_number_tier_old - l_number_tier_new);

Line: 1294

           delete from cn_rate_tiers where rate_sequence <> 1 AND rate_schedule_id= p_rate_schedule_id AND org_id = p_org_id;

Line: 1298

   select rate_dimension_id into l_rate_dimension_id
   from   cn_rate_sch_dims
   where  rate_sch_dim_id = p_rate_sch_dim_id;

Line: 1302

   select name into l_dimension_name_old
   from   cn_rate_dimensions
   where  rate_dimension_id = l_rate_dimension_id;

Line: 1312

   cn_rate_sch_dims_pkg.update_row
     (x_rate_sch_dim_id       => p_rate_sch_dim_id,
      x_rate_schedule_id      => p_rate_schedule_id,
      x_rate_dimension_id     => p_rate_dimension_id,
      x_rate_dim_sequence     => p_rate_dim_sequence,
      x_object_version_number => p_object_version_number);

Line: 1324

        select name into l_dimension_name_new
        from   cn_rate_dimensions
        where  rate_dimension_id = p_rate_dimension_id;

Line: 1328

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

Line: 1362

      ROLLBACK TO Update_Dimension_Assign;

Line: 1369

      ROLLBACK TO Update_Dimension_Assign;

Line: 1376

      ROLLBACK TO Update_Dimension_Assign;

Line: 1389

END Update_Dimension_Assign;

Line: 1455

	SELECT count(1) from cn_rate_tiers
	 WHERE rate_schedule_id = p_rate_schedule_id;

Line: 1489

   select count(*) into l_count from cn_rate_sch_dims
    where rate_schedule_id  = p_rate_schedule_id
      and rate_dimension_id = p_rate_dimension_id;

Line: 1501

   select count(*) into l_num_dims
     from cn_rate_sch_dims
    where rate_schedule_id  = p_rate_schedule_id;

Line: 1515

   cn_rate_sch_dims_pkg.insert_row(x_rate_sch_dim_id    => x_rate_sch_dim_id,
				   x_rate_schedule_id   => p_rate_schedule_id,
				   x_rate_dimension_id  => p_rate_dimension_id,
				   x_rate_dim_sequence  => l_rate_dim_sequence,
                           --R12 MOAC Changes--Start
                           x_org_id             => p_org_id
                           --R12 MOAC Changes--End
                          );

Line: 1527

      select name into l_dimension_name
      from   cn_rate_dimensions
      where  rate_dimension_id = p_rate_dimension_id;

Line: 1531

      select name into l_rate_sch_name
      from   cn_rate_schedules
      where  rate_schedule_id = p_rate_schedule_id;

Line: 1559

   UPDATE cn_rate_schedules
      SET number_dim = l_num_dims + 1
    WHERE rate_schedule_id = p_rate_schedule_id;

Line: 1648

      SELECT COUNT(*) number_tier
	FROM cn_rate_dim_tiers rdt,
	     cn_rate_sch_dims rsd
       WHERE rdt.rate_dimension_id = rsd.rate_dimension_id
	 AND rsd.rate_schedule_id = p_rate_schedule_id
       GROUP BY rsd.rate_dim_sequence
       ORDER BY rsd.rate_dim_sequence;

Line: 1657

      SELECT rate_tier_id, rate_sequence
	FROM cn_rate_tiers
       WHERE rate_schedule_id = p_rate_schedule_id;

Line: 1662

   SELECT number_dim
     INTO l_number_dim
     FROM cn_rate_schedules
    WHERE rate_schedule_id = p_rate_schedule_id;

Line: 1722

      update cn_rate_tiers set rate_sequence = l_new_seq
       where rate_tier_id = t.rate_tier_id;

Line: 1727

   UPDATE cn_srp_rate_assigns sra
     SET rate_sequence = (SELECT rate_sequence
			  FROM cn_rate_tiers
			  WHERE rate_schedule_id = p_rate_schedule_id
			  AND rate_tier_id = sra.rate_tier_id)
     WHERE rate_schedule_id = p_rate_schedule_id;

Line: 1736

PROCEDURE delete_rate_tiers
  (p_rate_schedule_id                   CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
   p_rate_dim_sequence                  CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE,
   p_tier_sequence                      CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE    := NULL,
   p_num_tiers                          NUMBER := 1) IS

   l_number_dim        CN_RATE_SCHEDULES.NUMBER_DIM%TYPE;

Line: 1749

   delete_flag         boolean;

Line: 1752

      SELECT COUNT(*) number_tier
	FROM cn_rate_dim_tiers rdt,
	     cn_rate_sch_dims rsd
       WHERE rdt.rate_dimension_id = rsd.rate_dimension_id
	 AND rsd.rate_schedule_id = p_rate_schedule_id
       GROUP BY rsd.rate_dim_sequence
       ORDER BY rsd.rate_dim_sequence;

Line: 1761

      SELECT rate_tier_id, rate_sequence
	FROM cn_rate_tiers
       WHERE rate_schedule_id = p_rate_schedule_id;

Line: 1766

   SELECT number_dim
     INTO l_number_dim
     FROM cn_rate_schedules
    WHERE rate_schedule_id = p_rate_schedule_id;

Line: 1791

      delete_flag := false;

Line: 1797

	       delete_flag := true;

Line: 1807

		  delete_flag := true;

Line: 1815

      if delete_flag = true then
	 delete from cn_rate_tiers
	   where rate_tier_id = t.rate_tier_id;

Line: 1818

	 delete from cn_srp_rate_assigns
	   where rate_tier_id = t.rate_tier_id;

Line: 1833

	 -- update table
	 update cn_rate_tiers set rate_sequence = l_new_seq
	  where rate_tier_id = t.rate_tier_id;

Line: 1840

   UPDATE cn_srp_rate_assigns sra
     SET rate_sequence = (SELECT rate_sequence
			    FROM cn_rate_tiers
			   WHERE rate_schedule_id = p_rate_schedule_id
			     AND rate_tier_id = sra.rate_tier_id)
     WHERE rate_schedule_id = p_rate_schedule_id;

Line: 1847

END delete_rate_tiers;

Line: 1849

PROCEDURE update_rate
  (p_rate_schedule_id           IN      CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
   p_rate_sequence              IN      CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
   p_commission_amount          IN      CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
   --R12 MOAC Changes--Start
   p_object_version_number      IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, --changed
   p_org_id                             CN_RATE_TIERS.ORG_ID%TYPE --new
   --R12 MOAC Changes--End
   ) IS


   x_return_status                    VARCHAR2(2000);

Line: 1863

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

Line: 1869

	SELECT rate_tier_id, commission_amount
	  FROM cn_rate_tiers
	 WHERE rate_schedule_id = p_rate_schedule_id
	   AND rate_sequence = p_rate_sequence;

Line: 1875

	select sqa.srp_plan_assign_id,
	       sqa.srp_quota_assign_id,
	       sqa.quota_id,
  	       rqa.rt_quota_asgn_id,
	       nvl(sqa.customized_flag, 'N') customized
	  from cn_srp_quota_assigns sqa, cn_rt_quota_asgns rqa
	 where rqa.rate_schedule_id = p_rate_schedule_id
       and sqa.quota_id = rqa.quota_id;

Line: 1886

   SAVEPOINT   Update_rate;

Line: 1895

	 cn_rate_tiers_pkg.insert_row
	   (X_RATE_TIER_ID          => l_rate_tier_id,
	    X_RATE_SCHEDULE_ID      => p_rate_schedule_id,
	    X_COMMISSION_AMOUNT     => p_commission_amount,
	    X_RATE_SEQUENCE         => p_rate_sequence,
          --R12 MOAC Changes--Start
          X_ORG_ID                => p_org_id);

Line: 1913

	 -- lock and update the record
	 cn_rate_tiers_pkg.lock_row
	   (X_RATE_TIER_ID           => l_rate_tier_id,
	    X_OBJECT_VERSION_NUMBER  => p_object_version_number);

Line: 1918

	 cn_rate_tiers_pkg.update_row
	   (X_RATE_TIER_ID           => l_rate_tier_id,
	    X_RATE_SCHEDULE_ID       => p_rate_schedule_id,
	    X_COMMISSION_AMOUNT      => p_commission_amount,
	    X_RATE_SEQUENCE          => p_rate_sequence,
	    X_OBJECT_VERSION_NUMBER  => p_object_version_number);

Line: 1934

	 update cn_srp_rate_assigns r
	    set commission_amount = p_commission_amount
	  where rate_schedule_id  = p_rate_schedule_id
	    and rate_sequence     = p_rate_sequence
	    and exists
	   (select 1 from cn_srp_quota_assigns r2
	     where r.srp_quota_assign_id = r2.srp_quota_assign_id
               and nvl(r2.customized_flag, 'N') = 'N');

Line: 1947

      ROLLBACK TO Update_rate;

Line: 1954

      ROLLBACK TO Update_rate;

Line: 1961

      ROLLBACK TO Update_rate;

Line: 1975

END update_rate;

Line: 1977

PROCEDURE update_srp_rate
  (p_srp_quota_assign_id        IN      CN_SRP_QUOTA_ASSIGNS.SRP_QUOTA_ASSIGN_ID%TYPE,
   p_rt_quota_asgn_id           IN      CN_SRP_RATE_ASSIGNS.RT_QUOTA_ASGN_ID%TYPE,
   p_rate_sequence              IN      CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
   p_commission_amount          IN      CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
   p_object_version_number      IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, -- changed
   --R12 MOAC Changes--Start
   p_org_id                             CN_RATE_TIERS.ORG_ID%TYPE,
   --R12 MOAC Changes--End
      x_return_status      OUT NOCOPY      VARCHAR2,
      x_loading_status     OUT NOCOPY      VARCHAR2,
      x_msg_count          OUT NOCOPY      NUMBER,
      x_msg_data           OUT NOCOPY      VARCHAR2

   ) IS

     l_srp_rate_assign_id    CN_SRP_RATE_ASSIGNS.SRP_RATE_ASSIGN_ID%TYPE;

Line: 2000

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

Line: 2003

	SELECT srp_rate_assign_id, object_version_number, commission_amount
	  FROM cn_srp_rate_assigns
	 WHERE srp_quota_assign_id = p_srp_quota_assign_id
	   AND rt_quota_asgn_id = p_rt_quota_asgn_id
	   AND rate_sequence = p_rate_sequence
	   FOR UPDATE OF srp_rate_assign_id nowait;

Line: 2011

	SELECT rate_tier_id
	  from cn_rate_tiers
	 where rate_schedule_id = l_rate_schedule_id
	   and rate_sequence    = p_rate_sequence;

Line: 2017

	SELECT srp_plan_assign_id, quota_id
	  from CN_SRP_QUOTA_ASSIGNS
	 where srp_quota_assign_id = p_srp_quota_assign_id;

Line: 2023

   SAVEPOINT   update_srp_rate;

Line: 2027

   x_loading_status := 'CN_INSERTED';

Line: 2040

      select rqa.rate_schedule_id into l_rate_schedule_id
	from cn_srp_quota_assigns sqa, cn_rt_quota_asgns rqa
       where rqa.rt_quota_asgn_id = p_rt_quota_asgn_id
	 and sqa.quota_id = rqa.quota_id
	 and sqa.srp_quota_assign_id = p_srp_quota_assign_id;

Line: 2051

	 -- insert rate tier into main rate table
	 cn_rate_tiers_pkg.insert_row
	   (X_RATE_TIER_ID          => l_rate_tier_id,
	    X_RATE_SCHEDULE_ID      => l_rate_schedule_id,
	    X_COMMISSION_AMOUNT     => 0,  -- place holder record
	    X_RATE_SEQUENCE         => p_rate_sequence,
          --R12 MOAC Changes--Start
          X_ORG_ID                => p_org_id);

Line: 2068

      select cn_srp_rate_assigns_s.NEXTVAL into l_srp_rate_assign_id from dual;

Line: 2070

      insert into cn_srp_rate_assigns
	(srp_plan_assign_id,
	 srp_quota_assign_id,
	 srp_rate_assign_id,
	 quota_id,
	 rate_schedule_id,
	 rt_quota_asgn_id,
	 rate_tier_id,
	 rate_sequence,
	 commission_amount,
	 last_update_date,
	 last_updated_by,
	 last_update_login,
	 creation_date,
	 created_by,
	 org_id)
	values
	(l_srp_plan_assign_id,
	 p_srp_quota_assign_id,
	 l_srp_rate_assign_id,
	 l_quota_id,
	 l_rate_schedule_id,
	 p_rt_quota_asgn_id,
	 l_rate_tier_id,
	 p_rate_sequence,
	 p_commission_amount,
	 sysdate,
	 fnd_global.user_id,
	 fnd_global.login_id,
	 sysdate,
	 fnd_global.user_id,
	 p_org_id);

Line: 2125

	 delete from cn_srp_rate_assigns
	  where srp_rate_assign_id = l_srp_rate_assign_id;

Line: 2138

	 update cn_srp_rate_assigns set
	   COMMISSION_AMOUNT      = p_commission_amount,
	   last_update_date       = sysdate,
	   last_updated_by        = fnd_global.user_id,
	   last_update_login      = fnd_global.login_id,
	   object_version_number  = object_version_number + 1
	   WHERE srp_rate_assign_id = l_srp_rate_assign_id;

Line: 2161

      ROLLBACK TO update_srp_rate;

Line: 2168

      ROLLBACK TO update_srp_rate;

Line: 2175

      ROLLBACK TO update_srp_rate;

Line: 2189

END update_srp_rate;

Line: 2211

	SELECT rsd.rate_dim_sequence, rd.number_tier
	  FROM cn_rate_sch_dims rsd,
	       cn_rate_dimensions rd
	  WHERE rsd.rate_schedule_id = p_rate_schedule_id
	  AND rsd.rate_dimension_id = p_rate_dimension_id
	  AND rd.rate_dimension_id = p_rate_dimension_id;

Line: 2219

	SELECT tier_sequence, rate_dimension_id
	  FROM cn_rate_dim_tiers
	  WHERE rate_dim_tier_id = p_rate_dim_tier_id;

Line: 2224

	SELECT rate_tier_id, nvl(commission_amount,0), object_version_number
	  FROM cn_rate_tiers
	 WHERE rate_schedule_id = p_rate_schedule_id
	   AND rate_sequence = x_rate_sequence;

Line: 2317

	select number_dim into l_number_dim from cn_rate_schedules
	 where rate_schedule_id = x_schedule_id;

Line: 2323

	   select rdt.tier_sequence into x_rate_sequence
	     from cn_rate_sch_dims rsd, cn_rate_dim_tiers rdt
	     where rsd.rate_schedule_id = x_schedule_id
	     and rsd.rate_dimension_id = rdt.rate_dimension_id
	     and rdt.rate_dim_tier_id = sres;

Line: 2343

	     --insert into l_tbl((sres));

Line: 2353

PROCEDURE  update_comm_rate(p_rate_schedule_id   IN  CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
                            x_result_tbl  IN  comm_tbl_type,
                            --R12 MOAC Changes--Start
                            p_org_id      IN  CN_RATE_TIERS.ORG_ID%TYPE --new
                            --R12 MOAC Changes--End
                            )
IS
x_ovn number;

Line: 2367

 update_rate(p_rate_schedule_id ,x_result_tbl(Lcntr).p_rate_sequence,x_result_tbl(Lcntr).p_commission_amount,x_ovn,
             --R12 MOAC Changes--Start
             p_org_id);

Line: 2395

select * from cn_rate_sch_dims_all
where rate_schedule_id   = p_rate_schedule_id
and  org_id             =  p_org_id;

Line: 2401

select * from cn_rate_dimensions_all
where rate_dimension_id = l_dim_id
and org_id = p_org_id;

Line: 2407

select * from cn_rate_tiers_all
where rate_schedule_id   = l_rate_schedule_id
and  org_id             =  p_org_id;

Line: 2435

select name into p_name from cn_rate_schedules_all where rate_schedule_id   = p_rate_schedule_id
and  org_id             =  p_org_id;

Line: 2521

update_rate(p_rate_schedule_id ,l_rate_tier.rate_sequence,l_rate_tier.commission_amount,l_rate_tier.object_version_number,
             --R12 MOAC Changes--Start
             p_org_id);