APPS.AMS_LIST_RULES_PKG SQL Statements

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

Line: 3

procedure INSERT_ROW (
  X_ROWID in OUT NOCOPY VARCHAR2,
  X_LIST_RULE_ID in NUMBER,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_LIST_RULE_NAME in VARCHAR2,
  X_WEIGHTAGE_FOR_DEDUPE in NUMBER,
  X_ACTIVE_FROM_DATE in DATE,
  X_ACTIVE_TO_DATE in DATE,
  X_LIST_RULE_TYPE in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_CREATION_DATE in DATE,
  X_CREATED_BY in NUMBER,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  x_LIST_SOURCE_TYPE in        VARCHAR2,
  x_ENABLED_FLAG      in       VARCHAR2,
  x_SEEDED_FLAG       in       VARCHAR2
) is
  cursor C is select ROWID from AMS_LIST_RULES_ALL
    where LIST_RULE_ID = X_LIST_RULE_ID
    ;

Line: 26

  insert into AMS_LIST_RULES_ALL (
    LIST_RULE_ID,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    OBJECT_VERSION_NUMBER,
    LIST_RULE_NAME,
    WEIGHTAGE_FOR_DEDUPE,
    ACTIVE_FROM_DATE,
    ACTIVE_TO_DATE,
    DESCRIPTION,
    LIST_RULE_TYPE,
    LIST_SOURCE_TYPE,
    ENABLED_FLAG,
    SEEDED_FLAG
  ) values (
    X_LIST_RULE_ID,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_LOGIN,
    X_OBJECT_VERSION_NUMBER,
    X_LIST_RULE_NAME,
    X_WEIGHTAGE_FOR_DEDUPE,
    X_ACTIVE_FROM_DATE,
    X_ACTIVE_TO_DATE,
    X_DESCRIPTION,
    X_LIST_RULE_TYPE,
    x_LIST_SOURCE_TYPE,
    x_ENABLED_FLAG,
    x_SEEDED_FLAG
  );

Line: 62

  insert into AMS_LIST_RULES_ALL_TL (
    LIST_RULE_ID,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    LIST_RULE_NAME,
    DESCRIPTION,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_LIST_RULE_ID,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_LOGIN,
    X_LIST_RULE_NAME,
    X_DESCRIPTION,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from AMS_LIST_RULES_ALL_TL T
    where T.LIST_RULE_ID = X_LIST_RULE_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 100

end INSERT_ROW;

Line: 112

  cursor c1 is select
      OBJECT_VERSION_NUMBER,
      LIST_RULE_NAME,
      WEIGHTAGE_FOR_DEDUPE,
      ACTIVE_FROM_DATE,
      ACTIVE_TO_DATE,
      LIST_RULE_TYPE,
      DESCRIPTION
    from AMS_LIST_RULES_ALL
    where LIST_RULE_ID = X_LIST_RULE_ID
    for update of LIST_RULE_ID nowait;

Line: 146

procedure UPDATE_ROW (
  X_LIST_RULE_ID in NUMBER,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_LIST_RULE_NAME in VARCHAR2,
  X_WEIGHTAGE_FOR_DEDUPE in NUMBER,
  X_ACTIVE_FROM_DATE in DATE,
  X_ACTIVE_TO_DATE in DATE,
  X_LIST_RULE_TYPE in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  x_LIST_SOURCE_TYPE in        VARCHAR2,
  x_ENABLED_FLAG      in       VARCHAR2,
  x_SEEDED_FLAG       in       VARCHAR2
) is
begin
  update AMS_LIST_RULES_ALL set
    OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
    LIST_RULE_NAME = X_LIST_RULE_NAME,
    WEIGHTAGE_FOR_DEDUPE = X_WEIGHTAGE_FOR_DEDUPE,
    ACTIVE_FROM_DATE = X_ACTIVE_FROM_DATE,
    ACTIVE_TO_DATE = X_ACTIVE_TO_DATE,
    LIST_RULE_TYPE = X_LIST_RULE_TYPE,
    DESCRIPTION = X_DESCRIPTION,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    LIST_SOURCE_TYPE = x_LIST_SOURCE_TYPE,
    ENABLED_FLAG = x_ENABLED_FLAG,
    SEEDED_FLAG = x_SEEDED_FLAG
  where LIST_RULE_ID = X_LIST_RULE_ID;

Line: 182

  update AMS_LIST_RULES_ALL_TL set
    LIST_RULE_NAME = X_LIST_RULE_NAME,
    DESCRIPTION = X_DESCRIPTION,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    SOURCE_LANG = userenv('LANG')
  where LIST_RULE_ID = X_LIST_RULE_ID
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 195

end UPDATE_ROW;

Line: 197

procedure DELETE_ROW (
  X_LIST_RULE_ID in NUMBER
) is
begin
  delete from AMS_LIST_RULES_ALL_TL
  where LIST_RULE_ID = X_LIST_RULE_ID;

Line: 208

  delete from AMS_LIST_RULES_ALL
  where LIST_RULE_ID = X_LIST_RULE_ID;

Line: 215

end DELETE_ROW;

Line: 238

   l_last_updated_by number;

Line: 241

     SELECT object_version_number, last_updated_by
     FROM   ams_list_rules_all
     WHERE  list_rule_id =  x_list_rule_id;

Line: 246

     SELECT 'x'
     FROM   ams_list_rules_all
     WHERE  list_rule_id = x_list_rule_id;

Line: 251

      SELECT ams_list_rules_all_s.NEXTVAL
      FROM DUAL;

Line: 276

      ams_list_rules_pkg.Insert_Row (
         X_ROWID                 => l_row_id,
         X_LIST_RULE_ID          => l_list_rule_id,
         X_OBJECT_VERSION_NUMBER => l_obj_verno,
         X_LIST_RULE_NAME        => x_list_rule_name,
         X_WEIGHTAGE_FOR_DEDUPE  => x_weightage_for_dedupe,
         X_ACTIVE_FROM_DATE      => SYSDATE,
         X_ACTIVE_TO_DATE        => SYSDATE,
         X_LIST_RULE_TYPE        => x_list_rule_type,
         X_DESCRIPTION           => x_description,
         X_CREATION_DATE         => SYSDATE,
         X_CREATED_BY            => l_user_id,
         X_LAST_UPDATE_DATE      => SYSDATE,
         X_LAST_UPDATED_BY       => l_user_id,
         X_LAST_UPDATE_LOGIN     => 0,
         x_LIST_SOURCE_TYPE      => x_LIST_SOURCE_TYPE,
         x_ENABLED_FLAG          => x_ENABLED_FLAG,
         x_SEEDED_FLAG           => x_SEEDED_FLAG
      );

Line: 298

      FETCH c_obj_verno INTO l_obj_verno, l_last_updated_by;

Line: 301

 if (l_last_updated_by in (1,2,0) OR
          NVL(x_custom_mode,'PRESERVE')='FORCE') THEN

      ams_list_rules_pkg.Update_Row (
         X_LIST_RULE_ID          => x_list_rule_id,
         X_OBJECT_VERSION_NUMBER => l_obj_verno,
         X_LIST_RULE_NAME        => x_list_rule_name,
         X_WEIGHTAGE_FOR_DEDUPE  => x_weightage_for_dedupe,
         X_ACTIVE_FROM_DATE      => TO_DATE (x_active_from_date, 'YYYY/MM/DD'),
         X_ACTIVE_TO_DATE        => TO_DATE (x_active_to_date, 'YYYY/MM/DD'),
         X_LIST_RULE_TYPE        => x_list_rule_type,
         X_DESCRIPTION           => x_description,
         X_LAST_UPDATE_DATE      => SYSDATE,
         X_LAST_UPDATED_BY       => l_user_id,
         X_LAST_UPDATE_LOGIN     => 0,
         x_LIST_SOURCE_TYPE      => x_LIST_SOURCE_TYPE,
         x_ENABLED_FLAG          => x_ENABLED_FLAG,
         x_SEEDED_FLAG           => x_SEEDED_FLAG
      );

Line: 334

  cursor c_last_updated_by is
                  select last_updated_by
                  FROM AMS_LIST_RULES_ALL_TL
                  where  LIST_RULE_ID =  X_LIST_RULE_ID
                  and  USERENV('LANG') = LANGUAGE;

Line: 340

        l_last_updated_by number;

Line: 344

     open c_last_updated_by;

Line: 345

     fetch c_last_updated_by into l_last_updated_by;

Line: 346

     close c_last_updated_by;

Line: 348

     if (l_last_updated_by in (1,2,0) OR
            NVL(x_custom_mode,'PRESERVE')='FORCE') THEN


    -- only UPDATE rows that have not been altered by user
    UPDATE AMS_LIST_RULES_ALL_TL
    SET
        LIST_RULE_NAME = NVL(X_LIST_RULE_NAME, LIST_RULE_NAME),
        DESCRIPTION = NVL(X_DESCRIPTION, DESCRIPTION),
        SOURCE_LANG = userenv('LANG'),
        LAST_UPDATE_DATE = SYSDATE,
        LAST_UPDATED_BY = decode(x_owner, 'SEED', 1, 0),
        LAST_UPDATE_LOGIN = 0
    WHERE LIST_RULE_ID = X_LIST_RULE_ID
    AND   userenv('LANG') IN (language, source_lang);

Line: 369

procedure INSERT_FIELD (
  X_ROWID in OUT NOCOPY VARCHAR2,
  X_LIST_RULE_FIELD_ID in NUMBER,
  X_FIELD_COLUMN_NAME in VARCHAR2,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_SUBSTRING_LENGTH in NUMBER,
  X_WEIGHTAGE in NUMBER,
  X_SEQUENCE_NUMBER in NUMBER,
  X_LIST_RULE_ID in NUMBER,
  X_FIELD_TABLE_NAME in VARCHAR2,
  X_CREATION_DATE in DATE,
  X_CREATED_BY in NUMBER,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  x_WORD_REPLACEMENT_CODE  in VARCHAR2,
  x_LIST_SOURCE_FIELD_ID in NUMBER
) is
  cursor C is select ROWID from AMS_LIST_RULE_FIELDS
    where LIST_RULE_FIELD_ID = X_LIST_RULE_FIELD_ID
    ;

Line: 391

  insert into AMS_LIST_RULE_FIELDS (
    FIELD_COLUMN_NAME,
    OBJECT_VERSION_NUMBER,
    SUBSTRING_LENGTH,
    WEIGHTAGE,
    SEQUENCE_NUMBER,
    LIST_RULE_FIELD_ID,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    LIST_RULE_ID,
    FIELD_TABLE_NAME,
    WORD_REPLACEMENT_CODE,
    LIST_SOURCE_FIELD_ID
  ) values (
    X_FIELD_COLUMN_NAME,
    X_OBJECT_VERSION_NUMBER,
    X_SUBSTRING_LENGTH,
    X_WEIGHTAGE,
    X_SEQUENCE_NUMBER,
    X_LIST_RULE_FIELD_ID,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_LOGIN,
    X_LIST_RULE_ID,
    X_FIELD_TABLE_NAME,
    x_WORD_REPLACEMENT_CODE,
    x_LIST_SOURCE_FIELD_ID
  );

Line: 433

end INSERT_FIELD;

Line: 445

  cursor c1 is select
      FIELD_COLUMN_NAME,
      OBJECT_VERSION_NUMBER,
      SUBSTRING_LENGTH,
      WEIGHTAGE,
      SEQUENCE_NUMBER,
      LIST_RULE_ID,
      FIELD_TABLE_NAME
    from AMS_LIST_RULE_FIELDS
    where LIST_RULE_FIELD_ID = X_LIST_RULE_FIELD_ID
    for update of LIST_RULE_FIELD_ID nowait;

Line: 479

procedure UPDATE_FIELD (
  X_LIST_RULE_FIELD_ID in NUMBER,
  X_FIELD_COLUMN_NAME in VARCHAR2,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_SUBSTRING_LENGTH in NUMBER,
  X_WEIGHTAGE in NUMBER,
  X_SEQUENCE_NUMBER in NUMBER,
  X_LIST_RULE_ID in NUMBER,
  X_FIELD_TABLE_NAME in VARCHAR2,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  x_WORD_REPLACEMENT_CODE  in VARCHAR2,
  x_LIST_SOURCE_FIELD_ID in NUMBER
) is
begin
  update AMS_LIST_RULE_FIELDS set
    FIELD_COLUMN_NAME = X_FIELD_COLUMN_NAME,
    OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
    SUBSTRING_LENGTH = X_SUBSTRING_LENGTH,
    WEIGHTAGE = X_WEIGHTAGE,
    SEQUENCE_NUMBER = X_SEQUENCE_NUMBER,
    LIST_RULE_ID = X_LIST_RULE_ID,
    FIELD_TABLE_NAME = X_FIELD_TABLE_NAME,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    WORD_REPLACEMENT_CODE = x_WORD_REPLACEMENT_CODE,
    LIST_SOURCE_FIELD_ID = x_LIST_SOURCE_FIELD_ID
  where LIST_RULE_FIELD_ID = X_LIST_RULE_FIELD_ID;

Line: 513

end UPDATE_FIELD;

Line: 515

procedure DELETE_FIELD (
  X_LIST_RULE_FIELD_ID in NUMBER
) is
begin
  delete from AMS_LIST_RULE_FIELDS
  where LIST_RULE_FIELD_ID = X_LIST_RULE_FIELD_ID;

Line: 526

end DELETE_FIELD;

Line: 548

   l_last_updated_by number;

Line: 551

     SELECT object_version_number, last_updated_by
     FROM   ams_list_rule_fields
     WHERE  list_rule_field_id =  x_list_rule_field_id;

Line: 556

     SELECT 'x'
     FROM   ams_list_rule_fields
     WHERE  list_rule_field_id = x_list_rule_field_id;

Line: 561

     SELECT  'x'
     FROM   ams_list_rule_fields
     WHERE  LIST_RULE_ID = X_LIST_RULE_ID
       AND  LAST_UPDATED_BY     <> 1 and last_updated_by <> 2 and last_updated_by <> 0;

Line: 567

      SELECT ams_list_rule_fields_s.NEXTVAL
      FROM DUAL;

Line: 598

      ams_list_rules_pkg.Insert_Field (
         X_ROWID                 => l_row_id,
         X_LIST_RULE_FIELD_ID    => l_list_rule_field_id,
         X_FIELD_COLUMN_NAME     => x_field_column_name,
         X_OBJECT_VERSION_NUMBER => l_obj_verno,
         X_SUBSTRING_LENGTH      => x_substring_length,
         X_WEIGHTAGE             => x_weightage,
         X_SEQUENCE_NUMBER       => x_sequence_number,
         X_LIST_RULE_ID          => x_list_rule_id,
         X_FIELD_TABLE_NAME      => x_field_table_name,
         X_CREATION_DATE         => SYSDATE,
         X_CREATED_BY            => l_user_id,
         X_LAST_UPDATE_DATE      => SYSDATE,
         X_LAST_UPDATED_BY       => l_user_id,
         X_LAST_UPDATE_LOGIN     => 0,
         x_WORD_REPLACEMENT_CODE => x_WORD_REPLACEMENT_CODE,
         x_LIST_SOURCE_FIELD_ID  => x_LIST_SOURCE_FIELD_ID
      );

Line: 619

      FETCH c_obj_verno INTO l_obj_verno, l_last_updated_by;

Line: 622

 if (l_last_updated_by in (1,2,0) OR
          NVL(x_custom_mode,'PRESERVE')='FORCE') THEN

      ams_list_rules_pkg.Update_Field (
         X_LIST_RULE_FIELD_ID    => x_list_rule_field_id,
         X_FIELD_COLUMN_NAME     => x_field_column_name,
         X_OBJECT_VERSION_NUMBER => l_obj_verno,
         X_SUBSTRING_LENGTH      => x_substring_length,
         X_WEIGHTAGE             => x_weightage,
         X_SEQUENCE_NUMBER       => x_sequence_number,
         X_LIST_RULE_ID          => x_list_rule_id,
         X_FIELD_TABLE_NAME      => x_field_table_name,
         X_LAST_UPDATE_DATE      => SYSDATE,
         X_LAST_UPDATED_BY       => l_user_id,
         X_LAST_UPDATE_LOGIN     => 0,
         x_WORD_REPLACEMENT_CODE => x_WORD_REPLACEMENT_CODE,
         x_LIST_SOURCE_FIELD_ID  => x_LIST_SOURCE_FIELD_ID
      );