APPS.AMS_METRIC_FORMULA_PVT SQL Statements

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

Line: 96

      SELECT AMS_METRIC_FORMULAS_s.NEXTVAL
      FROM dual;

Line: 100

      SELECT 1
      FROM AMS_METRIC_FORMULAS
      WHERE METRIC_FORMULA_ID = l_id;

Line: 105

      select nvl(max(sequence),0)
      from ams_metric_formulas
      where metric_id = l_metric_id;

Line: 220

      Ams_Utility_Pvt.debug_message( 'Private API: Calling Ams_Metric_Formulas_Pkg.Insert_Row');

Line: 224

   Ams_Metric_Formulas_Pkg.Insert_Row(
      X_ROWID => l_rowid,
      X_METRIC_FORMULA_ID => l_metric_formula_id,
      X_METRIC_ID => l_metric_formula_rec.metric_id,
      X_SOURCE_TYPE => l_metric_formula_rec.source_type,
      X_SOURCE_ID => l_metric_formula_rec.source_id,
      X_SOURCE_SUB_ID => l_metric_formula_rec.source_sub_id,
      X_USE_SUB_ID_FLAG => l_metric_formula_rec.use_sub_id_flag,
      X_SOURCE_VALUE => l_metric_formula_rec.source_value,
      X_TOKEN => l_metric_formula_rec.token,
      X_SEQUENCE => l_metric_formula_rec.sequence,
      X_NOTATION_TYPE => l_metric_formula_rec.notation_type,
      X_OBJECT_VERSION_NUMBER => 1,
      X_CREATION_DATE => SYSDATE,
      X_CREATED_BY => FND_GLOBAL.USER_ID,
      X_LAST_UPDATE_DATE => SYSDATE,
      X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
      X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
   );

Line: 317

PROCEDURE Update_Metric_Formula(
    p_api_version_number         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_met_formula_rec            IN    met_formula_rec_type,
    x_object_version_number      OUT nocopy NUMBER
    )
IS
   CURSOR c_get_metric_formula(l_METRIC_FORMULA_ID NUMBER)
   return met_formula_rec_type IS
       SELECT
       metric_formula_id,
       last_update_date,
       last_updated_by,
       creation_date,
       created_by,
       last_update_login,
       object_version_number,
       metric_id,
       source_type,
       source_id,
       source_sub_id,
       source_value,
       token,
       notation_type,
       use_sub_id_flag,
       sequence
       FROM  AMS_METRIC_FORMULAS
      WHERE METRIC_FORMULA_ID = l_METRIC_FORMULA_ID;

Line: 354

      select nvl(max(sequence),G_SEQUENCE_INCREMENT) from ams_metric_formulas
      where metric_id = l_metric_id;

Line: 357

   L_API_NAME                  CONSTANT VARCHAR2(30) := 'Update_metric_formula';

Line: 369

   SAVEPOINT UPDATE_metric_formula_sp;

Line: 397

     Ams_Utility_Pvt.debug_message('Private API: - Open Cursor to Select');

Line: 406

      Ams_Utility_Pvt.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
         p_token_name   => 'INFO',
         p_token_value  => 'metric_formula_id='||l_tar_metric_formula_rec.METRIC_FORMULA_ID);

Line: 472

        p_validation_mode  => JTF_PLSQL_API.g_update,
        p_metric_formula_rec  => l_tar_metric_formula_rec,
        x_return_status    => x_return_status,
        x_msg_count        => x_msg_count,
        x_msg_data         => x_msg_data);

Line: 492

     Ams_Utility_Pvt.debug_message('Private API: Calling Ams_Metric_Formulas_Pkg.Update_Row, sequence='||l_tar_metric_formula_rec.sequence);

Line: 496

   Ams_Metric_Formulas_Pkg.Update_Row(
      X_METRIC_FORMULA_ID => l_tar_metric_formula_rec.METRIC_FORMULA_ID,
      X_METRIC_ID => l_tar_metric_formula_rec.metric_id,
      X_SOURCE_TYPE => l_tar_metric_formula_rec.source_type,
      X_SOURCE_ID => l_tar_metric_formula_rec.source_id,
      X_SOURCE_SUB_ID => l_tar_metric_formula_rec.source_sub_id,
      X_USE_SUB_ID_FLAG => l_tar_metric_formula_rec.use_sub_id_flag,
      X_SOURCE_VALUE => l_tar_metric_formula_rec.source_value,
      X_TOKEN => l_tar_metric_formula_rec.token,
      X_SEQUENCE => l_tar_metric_formula_rec.sequence,
      X_NOTATION_TYPE => l_tar_metric_formula_rec.notation_type,
      X_OBJECT_VERSION_NUMBER => l_object_version_number,
      X_LAST_UPDATE_DATE => sysdate,
      X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
      X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID);

Line: 541

     ROLLBACK TO UPDATE_metric_formula_sp;

Line: 551

     ROLLBACK TO UPDATE_metric_formula_sp;

Line: 561

     ROLLBACK TO UPDATE_metric_formula_sp;

Line: 573

END Update_metric_formula;

Line: 576

PROCEDURE Delete_Metric_Formula(
    p_api_version_number         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_metric_formula_id                   IN  NUMBER,
    p_object_version_number      IN   NUMBER
    )

IS
   L_API_NAME                  CONSTANT VARCHAR2(30) := 'Delete_metric_formula';

Line: 595

   SAVEPOINT DELETE_metric_formula_SP;

Line: 637

   Ams_Utility_Pvt.debug_message( 'Private API: Calling delete table handler');

Line: 641

   AMS_METRIC_FORMULAS_PKG.Delete_Row(
       X_METRIC_FORMULA_ID  => p_METRIC_FORMULA_ID);

Line: 672

     ROLLBACK TO DELETE_metric_formula_SP;

Line: 682

     ROLLBACK TO DELETE_metric_formula_SP;

Line: 692

     ROLLBACK TO DELETE_metric_formula_SP;

Line: 704

END Delete_metric_formula;

Line: 728

      SELECT METRIC_FORMULA_ID
      FROM AMS_METRIC_FORMULAS
      WHERE METRIC_FORMULA_ID = p_METRIC_FORMULA_ID
      AND object_version_number = p_object_version
      FOR UPDATE NOWAIT;

Line: 842

   cursor c_check_sequence_update(p_metric_formula_id number, p_metric_id number,
          p_sequence NUMBER) IS
     select COUNT(1) from ams_metric_formulas
     where metric_id = p_metric_id
     and metric_formula_id <> p_metric_formula_id
     and sequence = p_sequence;

Line: 851

     select COUNT(1) from ams_metric_formulas
     where metric_id = p_metric_id
     and sequence = p_sequence;

Line: 880

   elsif p_validation_mode = JTF_PLSQL_API.G_UPDATE THEN
      open c_check_sequence_update(p_metric_formula_rec.metric_formula_id,
            p_metric_formula_rec.metric_id, p_metric_formula_rec.sequence);

Line: 883

      fetch c_check_sequence_update INTO l_dummy;

Line: 884

      close c_check_sequence_update;

Line: 983

   ELSE  -- Update

      IF p_metric_formula_rec.METRIC_FORMULA_ID = FND_API.g_miss_num OR p_metric_formula_rec.METRIC_FORMULA_ID IS NULL THEN
         FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');

Line: 1013

      SELECT arc_metric_used_for_object, metric_calculation_type
      FROM ams_metrics_all_b
     WHERE metric_id = p_metric_id;

Line: 1018

      SELECT count(1) FROM AMS_CATEGORIES_VL
     WHERE CATEGORY_ID = p_CATEGORY_ID
     and arc_category_created_for = 'METR';

Line: 1023

      SELECT count(1) FROM AMS_CATEGORIES_VL
     WHERE CATEGORY_ID = p_SUB_CATEGORY_ID
     and parent_category_id = p_category_id
     and arc_category_created_for = 'METR';

Line: 1029

      SELECT arc_metric_used_for_object FROM ams_metrics_all_b
     WHERE metric_id = p_metric_id
     and metric_calculation_type <> 'FORMULA';

Line: 1436

      g_stack_tbl.delete(l_index);

Line: 1508

g_source_type_tbl.delete;

Line: 1509

g_source_id_tbl.delete;

Line: 1510

g_source_sub_id_tbl.delete;

Line: 1511

g_source_value_tbl.delete;

Line: 1512

g_token_tbl.delete;

Line: 1513

g_use_sub_id_flag_tbl.delete;

Line: 1514

g_index_tbl.delete;

Line: 1515

g_stack_tbl.delete;

Line: 1662

    SELECT
       decode(source_type, 'METRIC', m.metrics_name,
          'CATEGORY', c.category_name||delim.meaning||
             decode(source_sub_id, null,
                decode(use_sub_id_flag,'Y',excsubcat.meaning,incsubcat.meaning),
                sc.category_name), 'OPERATOR', opers.meaning,
					 to_char(source_value)) source_name,
        f.TOKEN, f.sequence ,
        f.object_version_number ,
        f.metric_formula_id,
        f.notation_type ,
        f.source_type ,
        f.use_sub_id_flag ,
        f.source_value ,
        f.source_id,
        f.source_sub_id
    FROM ams_metric_formulas f, ams_metrics_vl m, ams_lookups opers,
         ams_categories_vl c, ams_categories_vl sc, ams_lookups delim,
         ams_lookups incsubcat, ams_lookups excsubcat
    WHERE f.metric_id = l_metric_id
      AND f.source_id = m.metric_id(+)
      AND f.source_id = c.category_id(+)
      AND c.arc_category_created_for(+) = 'METR'
      AND f.source_sub_id = sc.category_id(+)
      AND sc.arc_category_created_for(+) = 'METR'
      AND f.token = opers.lookup_code(+)
      AND opers.lookup_type(+) = 'AMS_METRIC_OPERAND_TYPE'
      AND f.notation_type = 'INFIX'
      AND delim.lookup_type = 'AMS_METRIC_STRINGS'
      AND delim.lookup_code = 'COLON_COLON'
      AND incsubcat.lookup_type = 'AMS_METRIC_STRINGS'
      AND incsubcat.lookup_code = 'INC_SUB_CAT'
      AND excsubcat.lookup_type = 'AMS_METRIC_STRINGS'
      AND excsubcat.lookup_code = 'EXC_SUB_CAT'
      ORDER BY f.sequence ASC ;

Line: 1720

   g_infix_tbl.delete;

Line: 1753

      ams_metric_pvt_w.update_metric(
         p_api_version => 1,
         p_init_msg_list => FND_API.G_FALSE,
         p_commit => FND_API.G_FALSE,
         p_validation_level => FND_API.G_VALID_LEVEL_FULL,
         x_return_status => l_return_status,
         x_msg_count => l_msg_count,
         x_msg_data => l_msg_data,
         p7_a0 => p_metric_id,
         p7_a6 => p_object_version_number,
         p7_a25 => g_infix_formula
      );

Line: 1768

      delete from ams_metric_formulas
      where metric_id = p_metric_id
      and notation_type = 'POSTFIX';

Line: 1774

         insert into ams_metric_formulas
            (METRIC_FORMULA_ID            ,
            LAST_UPDATE_DATE              ,
            LAST_UPDATED_BY               ,
            CREATION_DATE                 ,
            CREATED_BY                    ,
            LAST_UPDATE_LOGIN             ,
            OBJECT_VERSION_NUMBER         ,
            METRIC_ID                     ,
            SOURCE_TYPE                   ,
            SOURCE_ID                     ,
            TOKEN                         ,
            SEQUENCE                      ,
            NOTATION_TYPE                 ,
            SOURCE_VALUE                  ,
            SOURCE_SUB_ID                 ,
            USE_SUB_ID_FLAG               )
         values
            (AMS_METRIC_FORMULAS_S.nextval,
            sysdate,
            0,
            sysdate,
            0,
            0,
            1,
            p_metric_id,
            g_source_type_tbl(l_index),
            g_source_id_tbl(l_index),
            g_token_tbl(l_index)     ,
            g_index_tbl(l_index)   ,
            'POSTFIX',
            g_source_value_tbl(l_index)      ,
            g_source_sub_id_tbl(l_index)      ,
            g_use_sub_id_flag_tbl(l_index)     );