APPS.AMS_METRIC_PVT SQL Statements

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

Line: 183

   SELECT metric_id, metric_calculation_type, metric_category, accrual_type
   FROM ams_metrics_all_b
   WHERE metric_id = p_metric_rec.metric_id;

Line: 190

   SELECT 1
   FROM ams_metrics_all_b
   WHERE metric_parent_id = p_met_id;

Line: 195

   SELECT metric_id
   FROM ams_metrics_all_b
   WHERE summary_metric_id = p_met_id;

Line: 202

   SELECT metric_id
   FROM ams_metrics_all_b
   WHERE to_number(compute_using_function) = p_met_id;

Line: 322

      SELECT COUNT(1)
      FROM   ams_metrics_all_b
      WHERE  metric_id = l_metric_id;

Line: 327

      SELECT ams_metrics_all_b_s.NEXTVAL
      FROM   dual;

Line: 397

   Ams_Utility_Pvt.debug_message(l_full_name ||': insert');

Line: 441

   INSERT INTO ams_metrics_all_b (
          metric_id,

          creation_date,
          created_by,
          last_update_date,
          last_updated_by,
          last_update_login,

          object_version_number,
          application_id,
          arc_metric_used_for_object,
          metric_calculation_type,
          metric_category,
          accrual_type,
          value_type,
          sensitive_data_flag,
          enabled_flag,
          metric_sub_category,
          function_name,
          metric_parent_id,
          summary_metric_id,
          compute_using_function,
          default_uom_code,
          uom_type,
          formula,
          org_id,
          hierarchy_id,
          set_function_name,
          function_type,
          display_type,
			 target_type,
			 denorm_code
   )
   VALUES (
          l_metrics_rec.metric_id,

          SYSDATE,
          FND_GLOBAL.User_ID,
          SYSDATE,
          FND_GLOBAL.User_ID,
          FND_GLOBAL.Conc_Login_ID,
          1, --Object Version Number
          l_metrics_rec.application_id,
          l_metrics_rec.arc_metric_used_for_object,
          l_metrics_rec.metric_calculation_type,
          l_metrics_rec.metric_category,
          l_metrics_rec.accrual_type,
          l_metrics_rec.value_type,
          l_metrics_rec.sensitive_data_flag,
          l_metrics_rec.enabled_flag,
          l_metrics_rec.metric_sub_category,
          l_metrics_rec.function_name,
          l_metrics_rec.metric_parent_id,
          l_metrics_rec.summary_metric_id,
          l_metrics_rec.compute_using_function,
          l_metrics_rec.default_uom_code,
          l_metrics_rec.uom_type,
          l_metrics_rec.formula,
          l_org_id, --TO_NUMBER (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10)),  -- org_id
          l_metrics_rec.hierarchy_id,
          l_metrics_rec.set_function_name,
          l_func_type,
          l_metrics_rec.display_type,
			 l_metrics_rec.target_type,
			 l_metrics_rec.denorm_code
   );

Line: 511

   Ams_Utility_Pvt.debug_message(l_full_name ||': insert TL ');

Line: 516

   INSERT INTO ams_metrics_all_tl (
          metric_id,
          last_update_date,
          last_updated_by,
          creation_date,
          created_by,
          last_update_login,
          source_lang,
          metrics_name,
          description,
          formula_display,
          LANGUAGE
   )
   SELECT l_metrics_rec.metric_id,
          SYSDATE,
          FND_GLOBAL.User_ID,
          SYSDATE,
          FND_GLOBAL.User_ID,
          FND_GLOBAL.Conc_Login_ID,
          USERENV ('LANG'),
          l_metrics_rec.metrics_name,
          l_metrics_rec.description,
          l_metrics_rec.formula_display,
          l.language_code
   FROM fnd_languages l
   WHERE l.installed_flag IN ('I', 'B')
   AND NOT EXISTS ( SELECT NULL
                    FROM ams_metrics_all_tl t
                    WHERE t.metric_id = l_metrics_rec.metric_id
                    AND t.LANGUAGE = l.language_code);

Line: 627

PROCEDURE Update_Metric (
   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,

   x_return_status       OUT NOCOPY VARCHAR2,
   x_msg_count           OUT NOCOPY NUMBER,
   x_msg_data            OUT NOCOPY VARCHAR2,

   p_metric_rec          IN      metric_rec_type
)
IS
   L_API_VERSION         CONSTANT NUMBER := 1.0;

Line: 641

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

Line: 655

   SAVEPOINT Update_Metric_pvt;

Line: 733

         p_validation_mode => JTF_PLSQL_API.g_update,
         x_return_status   => l_return_status
      );

Line: 795

   UPDATE ams_metrics_all_b
      SET object_version_number       = l_metrics_rec.object_version_number + 1,
          application_id              = l_metrics_rec.application_id,
          arc_metric_used_for_object  =l_metrics_rec.arc_metric_used_for_object,
          metric_calculation_type     = l_metrics_rec.metric_calculation_type,
          metric_category             = l_metrics_rec.metric_category,
          accrual_type                = l_metrics_rec.accrual_type,
          value_type                  = l_metrics_rec.value_type,
          sensitive_data_flag         = l_metrics_rec.sensitive_data_flag,
          enabled_flag                = l_metrics_rec.enabled_flag,
          metric_sub_category         = l_metrics_rec.metric_sub_category,
          function_name               = l_metrics_rec.function_name,
          metric_parent_id            = l_metrics_rec.metric_parent_id,
          summary_metric_id           = l_metrics_rec.summary_metric_id,
          compute_using_function      = l_metrics_rec.compute_using_function,
          default_uom_code            = l_metrics_rec.default_uom_code,
          uom_type                    = l_metrics_rec.uom_type,
          formula                     = l_metrics_rec.formula,
          last_update_date            = SYSDATE,
          last_updated_by             = FND_GLOBAL.User_ID,
          last_update_login           = FND_GLOBAL.Conc_Login_ID,
          hierarchy_id                = l_metrics_rec.hierarchy_id,
          set_function_name           = l_metrics_rec.set_function_name,
          function_type               = l_func_type,
          display_type                = l_metrics_rec.display_type,
          target_type                 = l_metrics_rec.target_type,
			 denorm_code                 = l_metrics_rec.denorm_code
    WHERE metric_id = l_metrics_rec.metric_id;

Line: 837

   Ams_Utility_Pvt.debug_message(l_full_name ||': updateMetrics TL Table');

Line: 840

   UPDATE ams_metrics_all_tl
      SET metrics_name       = l_metrics_rec.metrics_name,
          description        = l_metrics_rec.description,
          formula_display    = l_metrics_rec.formula_display,
          last_update_date   = SYSDATE,
          last_updated_by    = FND_GLOBAL.User_ID,
          last_update_login  = FND_GLOBAL.Conc_Login_ID,
          source_lang        = USERENV ('LANG')
    WHERE metric_id = l_metrics_rec.metric_id
      AND USERENV ('LANG') IN (LANGUAGE, source_lang);

Line: 890

      ROLLBACK TO Update_Metric_pvt;

Line: 898

      ROLLBACK TO Update_Metric_pvt;

Line: 906

      ROLLBACK TO Update_Metric_pvt;

Line: 916

END Update_Metric;

Line: 934

PROCEDURE Delete_Metric (
   p_api_version              IN  NUMBER,
   p_init_msg_list            IN  VARCHAR2 := FND_API.G_FALSE,
   p_commit                   IN  VARCHAR2 := FND_API.G_FALSE,

   x_return_status            OUT NOCOPY VARCHAR2,
   x_msg_count                OUT NOCOPY NUMBER,
   x_msg_data                 OUT NOCOPY VARCHAR2,

   p_metric_id                IN  NUMBER,
   p_object_version_number    IN  NUMBER
)
IS
   L_API_VERSION              CONSTANT NUMBER := 1.0;

Line: 948

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

Line: 954

   SELECT metric_id
   FROM ams_metrics_all_b
   WHERE metric_parent_id = l_met_id;

Line: 960

   SELECT metric_id
   FROM ams_metrics_all_b
   WHERE COMPUTE_USING_FUNCTION = TO_CHAR(l_met_id);

Line: 965

   SELECT metric_id
   FROM ams_metrics_all_b
   WHERE SUMMARY_METRIC_ID = l_met_id;

Line: 976

   SAVEPOINT Delete_Metric_pvt;

Line: 1086

          Ams_Utility_Pvt.debug_message(l_full_name ||': delete with Validation');

Line: 1088

      DELETE FROM ams_metrics_all_b
      WHERE metric_id = p_metric_id
          AND object_version_number = p_object_version_number;

Line: 1103

      DELETE FROM ams_metrics_all_tl
      WHERE metric_id = p_metric_id;

Line: 1116

     delete from ams_metric_formulas
     where metric_id = p_metric_id;

Line: 1146

      ROLLBACK TO Delete_Metric_pvt;

Line: 1154

      ROLLBACK TO Delete_Metric_pvt;

Line: 1162

      ROLLBACK TO Delete_Metric_pvt;

Line: 1172

END Delete_Metric;

Line: 1209

      SELECT metric_id
      FROM ams_metrics_all_b
      WHERE metric_id = p_metric_id
      AND object_version_number = p_object_version_number
      FOR UPDATE OF metric_id NOWAIT;

Line: 1216

      SELECT metric_id
      FROM   ams_metrics_all_tl
      WHERE  metric_id = p_metric_id
      AND    USERENV('LANG') IN (LANGUAGE, source_lang)
      FOR    UPDATE OF metric_id NOWAIT;

Line: 1754

     SELECT 1
     FROM ams_metrics_vl
     WHERE UPPER(METRICS_NAME) = UPPER(p_metrics_name)
        AND arc_metric_used_for_object = p_arc_metric_used_for_object;

Line: 1761

     SELECT 1
     FROM ams_metrics_vl
     WHERE UPPER(METRICS_NAME) = UPPER(p_metrics_name)
        AND arc_metric_used_for_object = p_arc_metric_used_for_object
        AND metric_id <> p_metric_id ;

Line: 1895

      select count(1) from ams_lookups
      where lookup_type in ('AMS_METRIC_ROLLUP_TYPE', 'AMS_METRIC_OBJECT_TYPE', 'AMS_METRIC_ALLOCATION_TYPE')
      and lookup_code = l_arc_metric_used_for_object;

Line: 1900

      select count(1) from ams_lookups
      where lookup_type in ('AMS_METRIC_SPECIAL_TYPE')
      and lookup_code = l_arc_metric_used_for_object;

Line: 1905

      select count(1) from ams_lookups
      where lookup_type in ('AMS_METRIC_SPECIAL_TYPE', 'AMS_METRIC_ROLLUP_TYPE',
           'AMS_METRIC_OBJECT_TYPE', 'AMS_METRIC_ALLOCATION_TYPE')
      and lookup_code = l_arc_metric_used_for_object;

Line: 1914

    SELECT 1 from ams_metrics_vl
    WHERE METRIC_ID = p_metric_id
    AND   metric_calculation_type = p_metric_calculation;

Line: 1920

    SELECT 1 from MTL_UOM_CLASSES
    WHERE UOM_CLASS = p_uom_type;

Line: 1925

    SELECT 1 from AMS_CATEGORIES_VL
    WHERE CATEGORY_ID = p_category_id
    AND   enabled_flag = 'Y'
    AND   arc_category_created_for = 'METR';

Line: 2460

      SELECT   met.*
      FROM     ams_metrics_vl met
      WHERE    met.metric_id = l_metric_id
      ;

Line: 2467

      SELECT   met.*
      FROM     ams_metrics_vl met
      WHERE    met.metric_id = l_metric_id
      ;

Line: 2478

   SELECT COUNT(1)
   FROM   ams_metrics_vl
   WHERE  metric_parent_id = l_metric_id ;

Line: 2483

   SELECT COUNT(1)
   FROM   ams_metrics_vl
   WHERE  summary_metric_id = l_metric_id ;

Line: 2488

   select metric_category, ARC_METRIC_USED_FOR_OBJECT, metric_calculation_type
   from ams_metrics_all_b
   where metric_id = l_metric_id;

Line: 2493

   SELECT category_name
   FROM ams_categories_vl
   where category_id = l_category_id;

Line: 2500

    SELECT 1 from AMS_CATEGORIES_VL
    WHERE CATEGORY_ID = p_category_id
    AND   enabled_flag = 'Y'
    AND   arc_category_created_for = 'METR'
    AND   parent_category_id = p_parent_category_id;

Line: 2509

    SELECT 1 from MTL_UNITS_OF_MEASURE
    WHERE UOM_CODE = p_uom_code
    AND   uom_class = p_uom_class;

Line: 3165

    SELECT 1 from AMS_ACT_METRICS_ALL
    WHERE METRIC_ID = p_metric_id ;

Line: 3309

   SELECT metric_id
          ,last_update_date
          ,last_updated_by
          ,creation_date
          ,created_by
          ,last_update_login
          ,object_version_number
          ,application_id
          ,arc_metric_used_for_object
          ,metric_calculation_type
          ,metric_category
          ,accrual_type
          ,value_type
          ,sensitive_data_flag
          ,enabled_flag
          ,metric_sub_category
          ,function_name
          ,metric_parent_id
          ,summary_metric_id
          ,compute_using_function
          ,default_uom_code
          ,uom_type
          ,formula
          ,metrics_name
          ,description
          ,formula_display
          ,hierarchy_id
          ,set_function_name
          ,display_type
			 ,target_type
			 ,denorm_code
     FROM ams_metrics_vl
    WHERE metric_id = p_metric_rec.metric_id;