eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CN_RATE_DIMENSIONS_PVT SQL Statements

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

Line: 21


	SELECT 0
	  FROM dual
         WHERE NOT exists (SELECT 1 FROM cn_calc_sql_exps WHERE calc_sql_exp_id = p_calc_sql_exp_id)
        UNION ALL
        SELECT 1
	  FROM cn_calc_sql_exps
         WHERE calc_sql_exp_id = p_calc_sql_exp_id
	   AND exp_type_code like '%DDT%'
        UNION ALL
        SELECT 2
	  FROM cn_calc_sql_exps
         WHERE calc_sql_exp_id = p_calc_sql_exp_id
	   AND (exp_type_code IS NULL OR exp_type_code NOT LIKE '%DDT%');

Line: 36


	SELECT 1
	  FROM cn_rate_dimensions
	  WHERE name = p_name
	    AND (p_rate_dimension_id IS NULL OR p_rate_dimension_id <> rate_dimension_id)
          --R12 MOAC Changes--Start
          AND org_id = p_org_id;

Line: 223


   cn_rate_dimensions_pkg.insert_row
     (x_rate_dimension_id     => x_rate_dimension_id,
      x_name                  => p_name,
      x_description           => p_description,
      x_dim_unit_code         => p_dim_unit_code,
      x_number_tier           => l_number_tier,
      --R12 MOAC Changes--Start
      x_org_id                => p_org_id
      --R12 MOAC Changes--End
     );

Line: 237


      select name into l_dimension_name
      from   cn_rate_dimensions
      where  rate_dimension_id = x_rate_dimension_id;

Line: 272


	 cn_rate_dim_tiers_pkg.insert_row
	   (x_rate_dim_tier_id    => l_temp_id,
	    x_rate_dimension_id   => x_rate_dimension_id,
	    x_minimum_amount      => p_tiers_tbl(i).minimum_amount,
	    x_maximum_amount      => p_tiers_tbl(i).maximum_amount,
	    x_min_exp_id          => p_tiers_tbl(i).min_exp_id,
	    x_max_exp_id          => p_tiers_tbl(i).max_exp_id,
	    x_string_value        => p_tiers_tbl(i).string_value,
	    x_tier_sequence       => p_tiers_tbl(i).tier_sequence,
          --R12 MOAC Changes--Start
          x_org_id              => p_org_id
          --R12 MOAC Changes--End
         );

Line: 346


PROCEDURE Update_Dimension
  (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_dimension_id          IN      CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE,
   p_name                       IN      CN_RATE_DIMENSIONS.NAME%TYPE,
   p_description                IN      CN_RATE_DIMENSIONS.DESCRIPTION%TYPE := NULL,
   p_dim_unit_code              IN      CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE,
   p_number_tier                IN      CN_RATE_DIMENSIONS.NUMBER_TIER%TYPE, -- not used
   p_tiers_tbl                  IN      tiers_tbl_type := g_miss_tiers_tbl,
   --R12 MOAC Changes--Start
   p_org_id                     IN      CN_RATE_DIMENSIONS.ORG_ID%TYPE,   --new
   p_object_version_number      IN OUT NOCOPY CN_RATE_DIMENSIONS.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';

Line: 370


     l_delete_flag             VARCHAR2(1);

Line: 381


	SELECT dim_unit_code
	  FROM cn_rate_dimensions
	  WHERE rate_dimension_id = p_rate_dimension_id;

Line: 386


	SELECT 1
	  FROM dual
	  WHERE exists (SELECT 1
			FROM cn_rate_sch_dims rsd
			WHERE rsd.rate_dimension_id = p_rate_dimension_id
			AND exists (SELECT 1
				    FROM cn_rt_formula_asgns
				    WHERE rate_schedule_id = rsd.rate_schedule_id));

Line: 396


	SELECT rate_dim_tier_id
	  FROM cn_rate_dim_tiers
	  WHERE rate_dimension_id = p_rate_dimension_id;

Line: 401


   SAVEPOINT   Update_Dimension;

Line: 444


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

Line: 454


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

Line: 468


	 l_delete_flag := 'Y';

Line: 472


	       l_delete_flag := 'N';

Line: 477


	 IF (l_delete_flag = 'Y') THEN
	    delete_tier(p_api_version       => 1.0,
			p_rate_dim_tier_id  => db_tier.rate_dim_tier_id,
			x_return_status     => x_return_status,
			x_msg_count         => x_msg_count,
			x_msg_data          => x_msg_data);

Line: 495


	    cn_rate_dim_tiers_pkg.update_row
	      (x_rate_dim_tier_id      => p_tiers_tbl(i).rate_dim_tier_id,
	       x_rate_dimension_id     => p_rate_dimension_id,
	       x_minimum_amount        => p_tiers_tbl(i).minimum_amount,
	       x_maximum_amount        => p_tiers_tbl(i).maximum_amount,
	       x_min_exp_id            => p_tiers_tbl(i).min_exp_id,
	       x_max_exp_id            => p_tiers_tbl(i).max_exp_id,
	       x_string_value          => p_tiers_tbl(i).string_value,
	       x_tier_sequence         => p_tiers_tbl(i).tier_sequence,
	       x_object_version_number => p_tiers_tbl(i).object_version_number);

Line: 541


   select name into l_dimension_name_old
   from   cn_rate_dimensions
   where  rate_dimension_id = p_rate_dimension_id;

Line: 545


   select dim_unit_code into l_type_old
   from   cn_rate_dimensions
   where  rate_dimension_id = p_rate_dimension_id;

Line: 552


   select count(*) into l_number_tier from cn_rate_dim_tiers
    where rate_dimension_id = p_rate_dimension_id;

Line: 559


   cn_rate_dimensions_pkg.update_row
     (x_rate_dimension_id      => p_rate_dimension_id,
      x_name                   => p_name,
      x_description            => p_description,
      x_dim_unit_code          => p_dim_unit_code,
      x_number_tier            => l_number_tier,
      x_object_version_number  => p_object_version_number);

Line: 572


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

Line: 592


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

Line: 643


      ROLLBACK TO Update_Dimension;

Line: 650


      ROLLBACK TO Update_Dimension;

Line: 657


      ROLLBACK TO Update_Dimension;

Line: 670


END Update_Dimension;

Line: 674


PROCEDURE Delete_Dimension
  (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_dimension_id          IN      CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE,
   --R12 MOAC Changes--Start
   p_object_version_number      IN     CN_RATE_DIMENSIONS.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';

Line: 698


	SELECT 1
	  FROM dual
	  WHERE exists (SELECT 1
			FROM cn_rate_sch_dims
			WHERE rate_dimension_id = p_rate_dimension_id);

Line: 705


   SAVEPOINT   Delete_Dimension;

Line: 737


   SELECT org_id INTO l_org_id
   FROM   cn_rate_dimensions
   WHERE  rate_dimension_id = p_rate_dimension_id;

Line: 741


   SELECT name INTO l_dimension_name
   FROM   cn_rate_dimensions
   WHERE  rate_dimension_id = p_rate_dimension_id;

Line: 748


   cn_rate_dimensions_pkg.delete_row(p_rate_dimension_id);

Line: 754


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

Line: 764


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


      ROLLBACK TO Delete_Dimension;

Line: 795


      ROLLBACK TO Delete_Dimension;

Line: 802


      ROLLBACK TO Delete_Dimension;

Line: 815


END Delete_Dimension;

Line: 828


PROCEDURE delete_tier
  (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_dim_tier_id           IN      CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE,
   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_Tier';

Line: 854


	SELECT rate_schedule_id
	  FROM cn_rate_sch_dims
	  WHERE rate_dimension_id = l_rate_dimension_id;

Line: 859


	SELECT 1
	  FROM dual
	  WHERE NOT exists (SELECT 1
			    FROM cn_rate_dim_tiers
			    WHERE rate_dimension_id = l_rate_dimension_id
			    AND rate_dim_tier_id <> p_rate_dim_tier_id);

Line: 868


    Select  minimum_amount, maximum_amount, min_exp_id, max_exp_id,
    string_value, org_id
    from    cn_rate_dim_tiers
    where   rate_dim_tier_id = p_rate_dim_tier_id;

Line: 877


   SAVEPOINT   Delete_Tier;

Line: 898


      SELECT rate_dimension_id, tier_sequence
	INTO l_rate_dimension_id, l_tier_sequence
	FROM cn_rate_dim_tiers
       WHERE rate_dim_tier_id = p_rate_dim_tier_id;

Line: 904


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

Line: 922


      SELECT rate_dim_sequence
	INTO l_rate_dim_sequence
	FROM cn_rate_sch_dims
	WHERE rate_schedule_id = rate_table.rate_schedule_id
	AND rate_dimension_id = l_rate_dimension_id;

Line: 929


      cn_multi_rate_schedules_pvt.delete_rate_tiers
	(p_rate_schedule_id   => rate_table.rate_schedule_id,
	 p_rate_dim_sequence  => l_rate_dim_sequence,
	 p_tier_sequence      => l_tier_sequence);

Line: 943


   cn_rate_dim_tiers_pkg.delete_row(p_rate_dim_tier_id);

Line: 953


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

Line: 959


         select name into l_from from cn_calc_sql_exps
         where calc_sql_exp_id = l_old_rec.min_exp_id;

Line: 961


         select name into l_to from cn_calc_sql_exps
         where calc_sql_exp_id = l_old_rec.max_exp_id;

Line: 963


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

Line: 969


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

Line: 995


   update cn_rate_dim_tiers set tier_sequence = tier_sequence - 1
    where rate_dimension_id = l_rate_dimension_id
      and tier_sequence    >= l_tier_sequence;

Line: 1001


   UPDATE cn_rate_dimensions
      SET number_tier = (select count(*) from cn_rate_dim_tiers
	 		 where rate_dimension_id = l_rate_dimension_id)
    WHERE rate_dimension_id = l_rate_dimension_id;

Line: 1019


      ROLLBACK TO Delete_Tier;

Line: 1026


      ROLLBACK TO Delete_Tier;

Line: 1033


      ROLLBACK TO Delete_Tier;

Line: 1046


END Delete_Tier;

Line: 1049


PROCEDURE update_tier
  (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_dim_tier_id           IN      CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE,
   p_rate_dimension_id          IN      CN_RATE_DIM_TIERS.RATE_DIMENSION_ID%TYPE,
   p_dim_unit_code              IN      CN_RATE_DIM_TIERS.DIM_UNIT_CODE%TYPE,
   p_minimum_amount             IN      CN_RATE_DIM_TIERS.MINIMUM_AMOUNT%TYPE := cn_api.g_miss_num,
   p_maximum_amount             IN      CN_RATE_DIM_TIERS.MAXIMUM_AMOUNT%TYPE := cn_api.g_miss_num,
   p_min_exp_id                 IN      CN_RATE_DIM_TIERS.MIN_EXP_ID%TYPE     := cn_api.g_miss_num,
   p_max_exp_id                 IN      CN_RATE_DIM_TIERS.MAX_EXP_ID%TYPE     := cn_api.g_miss_num,
   p_string_value               IN      CN_RATE_DIM_TIERS.STRING_VALUE%TYPE   := cn_api.g_miss_char,
   p_tier_sequence              IN      CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE  := cn_api.g_miss_num,
   -- R12 MOAC Changes --Start
   p_object_version_number      IN OUT NOCOPY CN_RATE_DIM_TIERS.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_Tier';

Line: 1083


    Select  minimum_amount, maximum_amount, min_exp_id, max_exp_id, string_value
    from    cn_rate_dim_tiers
    where   rate_dim_tier_id = p_rate_dim_tier_id;

Line: 1092


   SAVEPOINT   Update_tier;

Line: 1144


   cn_rate_dim_tiers_pkg.update_row
     (x_rate_dim_tier_id      => p_rate_dim_tier_id,
      x_rate_dimension_id     => p_rate_dimension_id,
      x_minimum_amount        => p_minimum_amount,
      x_maximum_amount        => p_maximum_amount,
      x_min_exp_id            => p_min_exp_id,
      x_max_exp_id            => p_max_exp_id,
      x_string_value          => p_string_value,
      x_tier_sequence         => p_tier_sequence,
      x_object_version_number => p_object_version_number);

Line: 1171


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

Line: 1179


            select name into l_from_old from cn_calc_sql_exps
            where calc_sql_exp_id = l_old_rec.min_exp_id;

Line: 1181


            select name into l_to_old from cn_calc_sql_exps
            where calc_sql_exp_id = l_old_rec.max_exp_id;

Line: 1183


            select name into l_from_new from cn_calc_sql_exps
            where calc_sql_exp_id = p_min_exp_id;

Line: 1185


            select name into l_to_new from cn_calc_sql_exps
            where calc_sql_exp_id = p_max_exp_id;

Line: 1187


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

Line: 1194


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

Line: 1232


      ROLLBACK TO Update_tier;

Line: 1239


      ROLLBACK TO Update_tier;

Line: 1246


      ROLLBACK TO Update_tier;

Line: 1259


END Update_tier;

Line: 1305


	SELECT rate_schedule_id
	  FROM cn_rate_sch_dims
	  WHERE rate_dimension_id = p_rate_dimension_id;

Line: 1353


      SELECT rate_dim_sequence
	INTO l_rate_dim_sequence
	FROM cn_rate_sch_dims
       WHERE rate_dimension_id = p_rate_dimension_id
	 AND rate_schedule_id  = rate_table.rate_schedule_id;

Line: 1369


   update cn_rate_dim_tiers set tier_sequence = tier_sequence + 1
    where rate_dimension_id  = p_rate_dimension_id
      and tier_sequence     >= p_tier_sequence;

Line: 1374


   cn_rate_dim_tiers_pkg.insert_row
     (x_rate_dim_tier_id    => x_rate_dim_tier_id,
      x_rate_dimension_id   => p_rate_dimension_id,
      x_minimum_amount      => p_minimum_amount,
      x_maximum_amount      => p_maximum_amount,
      x_min_exp_id          => p_min_exp_id,
      x_max_exp_id          => p_max_exp_id,
      x_string_value        => p_string_value,
      x_tier_sequence       => p_tier_sequence,
      -- R12 MOAC Changes --Start
      x_org_id              => p_org_id
      -- R12 MOAC Changes --End
     );

Line: 1401


          select name into l_from from cn_calc_sql_exps
          where calc_sql_exp_id = p_min_exp_id;

Line: 1403


          select name into l_to from cn_calc_sql_exps
          where calc_sql_exp_id = p_max_exp_id;

Line: 1435


   UPDATE cn_rate_dimensions
      SET number_tier = (select count(*) from cn_rate_dim_tiers
	 		 where rate_dimension_id = p_rate_dimension_id)
    WHERE rate_dimension_id = p_rate_dimension_id;