APPS.CN_RATE_TIERS_PKG SQL Statements

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

Line: 7

Notes   : If we add, update or delete a tier we must 'INCOMPLETE' any comp
          plans that this schedule is assigned to (via a quota).
	  This is done in the calls to cn_comp_plans_pkg.set_status.
          Since there is no custom validation in this package the com plan
	  status update could've gone at the start of the begin procedure.
	  But for consistency with the other packages we call it in each
	  individual insert, update and delete procedure.*/

PROCEDURE INSERT_ROW
  (X_RATE_TIER_ID          IN OUT NOCOPY CN_RATE_TIERS.RATE_TIER_ID%TYPE,
   X_RATE_SCHEDULE_ID      IN     CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
   X_COMMISSION_AMOUNT     IN     CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
   X_RATE_SEQUENCE         IN     CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
   --R12 MOAC Changes--Start
   X_ORG_ID                IN     CN_RATE_TIERS.ORG_ID%TYPE,
   --R12 MOAC Changes--End
   X_CREATION_DATE         IN     CN_RATE_TIERS.CREATION_DATE%TYPE    := SYSDATE,
   X_CREATED_BY            IN     CN_RATE_TIERS.CREATED_BY%TYPE       := FND_GLOBAL.USER_ID,
   X_LAST_UPDATE_DATE      IN     CN_RATE_TIERS.LAST_UPDATE_DATE%TYPE := SYSDATE,
   X_LAST_UPDATED_BY       IN     CN_RATE_TIERS.LAST_UPDATED_BY%TYPE  := FND_GLOBAL.USER_ID,
   X_LAST_UPDATE_LOGIN     IN     CN_RATE_TIERS.LAST_UPDATE_LOGIN%TYPE:= FND_GLOBAL.LOGIN_ID) IS

  CURSOR C IS SELECT RATE_TIER_ID FROM CN_RATE_TIERS
    WHERE RATE_TIER_ID = X_RATE_TIER_ID;

Line: 32

  CURSOR id IS SELECT CN_RATE_TIERS_S.NEXTVAL FROM DUAL;

Line: 52

   insert into CN_RATE_TIERS
     (RATE_TIER_ID,
      RATE_SCHEDULE_ID,
      COMMISSION_AMOUNT,
      RATE_SEQUENCE,
      --R12 MOAC Changes--Start
      ORG_ID,
      --R12 MOAC Changes--End
      CREATED_BY,
      CREATION_DATE,
      LAST_UPDATE_LOGIN,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      OBJECT_VERSION_NUMBER)
     VALUES
     (X_RATE_TIER_ID,
      X_RATE_SCHEDULE_ID,
      X_COMMISSION_AMOUNT,
      X_RATE_SEQUENCE,
      --R12 MOAC Changes--Start
      X_ORG_ID,
      --R12 MOAC Changes--End
      X_CREATED_BY,
      X_CREATION_DATE,
      X_LAST_UPDATE_LOGIN,
      X_LAST_UPDATE_DATE,
      X_LAST_UPDATED_BY,
      0);

Line: 90

   cn_srp_rate_assigns_pkg.insert_record
     (x_rate_schedule_id     => x_rate_schedule_id,
      x_rate_tier_id	     => x_rate_tier_id,
      x_rate_sequence        => x_rate_sequence,

      -- these are not used anymore
      x_srp_plan_assign_id   => null,
      x_srp_quota_assign_id  => null,
      x_srp_rate_assign_id   => null,
      x_quota_id	     => null,
      x_commission_Rate      => null,
      x_commission_amount    => null,
      x_disc_rate_table_flag => null);

Line: 104

  END INSERT_ROW;

Line: 111

   select object_version_number
     from CN_RATE_TIERS
    where RATE_TIER_ID = X_RATE_TIER_ID
      for update of RATE_TIER_ID nowait;

Line: 122

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

Line: 136

PROCEDURE UPDATE_ROW
  (X_RATE_TIER_ID          IN     CN_RATE_TIERS.RATE_TIER_ID%TYPE,
   X_RATE_SCHEDULE_ID      IN     CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
   X_COMMISSION_AMOUNT     IN     CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
   X_RATE_SEQUENCE         IN     CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
   X_OBJECT_VERSION_NUMBER IN OUT NOCOPY    CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE,
   X_LAST_UPDATE_DATE      IN     CN_RATE_TIERS.LAST_UPDATE_DATE%TYPE := SYSDATE,
   X_LAST_UPDATED_BY       IN     CN_RATE_TIERS.LAST_UPDATED_BY%TYPE  := FND_GLOBAL.USER_ID,
   X_LAST_UPDATE_LOGIN     IN     CN_RATE_TIERS.LAST_UPDATE_LOGIN%TYPE:= FND_GLOBAL.LOGIN_ID) IS

   l_commission_amount_old   number := 0;

Line: 157

   UPDATE cn_rate_tiers SET
     rate_schedule_id                  =     x_rate_schedule_id,
     commission_amount                 =     x_commission_amount,
     RATE_SEQUENCE                     =     X_RATE_SEQUENCE,
     last_update_date                  =     X_Last_Update_Date,
     last_updated_by                   =     X_Last_Updated_By,
     last_update_login                 =     X_Last_Update_Login,
     OBJECT_VERSION_NUMBER             =     X_OBJECT_VERSION_NUMBER
   WHERE rate_tier_id = X_rate_tier_id;

Line: 172

   select commission_amount into l_commission_amount_old
     from cn_rate_tiers
    where rate_tier_id = x_rate_tier_id;

Line: 190

  END UPDATE_ROW;

Line: 203

  PROCEDURE Delete_Row
    (X_RATE_SCHEDULE_ID      IN     CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
     X_RATE_TIER_ID          IN     CN_RATE_TIERS.RATE_TIER_ID%TYPE) IS

  BEGIN

    IF X_Rate_Schedule_Id IS NOT NULL THEN

      IF X_Rate_Tier_Id IS NULL THEN

        DELETE FROM cn_rate_tiers
        WHERE rate_schedule_id = X_Rate_Schedule_Id;

Line: 224

        DELETE FROM cn_rate_tiers
        WHERE  rate_tier_id = x_rate_tier_id;

Line: 231

        cn_srp_rate_assigns_pkg.delete_record(
			   x_srp_plan_assign_id	=> null
		          ,x_srp_rate_assign_id	=> null
		          ,x_quota_id		=> null
		          ,x_rate_schedule_id	=> x_rate_schedule_id
		          ,x_rate_tier_id	=> x_rate_tier_id);

Line: 243

  END Delete_Row;