APPS.AMS_SCORERESULT_PVT SQL Statements

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

Line: 86

PROCEDURE insert_percentile_results (
    p_score_id          IN   NUMBER
);

Line: 123

      SELECT AMS_DM_SCORE_RESULTS_s.NEXTVAL
      FROM dual;

Line: 127

      SELECT 1 FROM dual
      WHERE EXISTS (SELECT 1 FROM AMS_DM_SCORE_RESULTS
                    WHERE SCORE_RESULT_ID = l_id);

Line: 209

      AMS_DM_SCORE_RESULTS_PKG.Insert_Row(
          px_score_result_id        => l_score_result_id,
          p_last_update_date        => SYSDATE,
          p_last_updated_by         => FND_GLOBAL.USER_ID,
          p_creation_date           => SYSDATE,
          p_created_by              => FND_GLOBAL.USER_ID,
          p_last_update_login       => FND_GLOBAL.CONC_LOGIN_ID,
          px_object_version_number  => l_object_version_number,
          p_score_id                => p_scoreresult_rec.score_id,
          p_decile                  => p_scoreresult_rec.decile,
          p_num_records             => p_scoreresult_rec.num_records,
          p_score                   => p_scoreresult_rec.score,
          p_confidence              => p_scoreresult_rec.confidence
      );

Line: 293

PROCEDURE Update_Scoreresult(
    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_scoreresult_rec         IN    scoreresult_rec_type,
    x_object_version_number   OUT NOCOPY  NUMBER
)
IS
   L_API_NAME                  CONSTANT VARCHAR2(30) := 'Update_Scoreresult';

Line: 315

      SELECT *
      FROM  AMS_DM_SCORE_RESULTS
      WHERE score_result_id = p_score_result_id;

Line: 321

      SAVEPOINT UPDATE_Scoreresult_PVT;

Line: 351

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

Line: 359

         AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
            p_token_name   => 'INFO',
            p_token_value  => 'Scoreresult') ;

Line: 390

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

Line: 403

      AMS_UTILITY_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');

Line: 407

      AMS_DM_SCORE_RESULTS_PKG.Update_Row(
          p_score_result_id      => p_scoreresult_rec.score_result_id,
          p_last_update_date     => SYSDATE,
          p_last_updated_by      => FND_GLOBAL.USER_ID,
          p_creation_date        => SYSDATE,
          p_created_by           => l_ref_scoreresult_rec.created_by,
          p_last_update_login    => FND_GLOBAL.CONC_LOGIN_ID,
          p_object_version_number  => p_scoreresult_rec.object_version_number + 1,
          p_score_id             => p_scoreresult_rec.score_id,
          p_decile               => p_scoreresult_rec.decile,
          p_num_records          => p_scoreresult_rec.num_records,
          p_score                => p_scoreresult_rec.score,
          p_confidence           => p_scoreresult_rec.confidence
      );

Line: 450

     ROLLBACK TO UPDATE_Scoreresult_PVT;

Line: 460

     ROLLBACK TO UPDATE_Scoreresult_PVT;

Line: 470

     ROLLBACK TO UPDATE_Scoreresult_PVT;

Line: 482

End Update_Scoreresult;

Line: 485

PROCEDURE Delete_Scoreresult(
    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_score_result_id         IN  NUMBER,
    p_object_version_number   IN   NUMBER
)
IS
   L_API_NAME                  CONSTANT VARCHAR2(30) := 'Delete_Scoreresult';

Line: 501

      SAVEPOINT DELETE_Scoreresult_PVT;

Line: 534

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

Line: 538

      AMS_DM_SCORE_RESULTS_PKG.Delete_Row(
          p_SCORE_RESULT_ID  => p_SCORE_RESULT_ID);

Line: 569

     ROLLBACK TO DELETE_Scoreresult_PVT;

Line: 579

     ROLLBACK TO DELETE_Scoreresult_PVT;

Line: 589

     ROLLBACK TO DELETE_Scoreresult_PVT;

Line: 601

End Delete_Scoreresult;

Line: 624

      SELECT SCORE_RESULT_ID
      FROM AMS_DM_SCORE_RESULTS
      WHERE SCORE_RESULT_ID = p_SCORE_RESULT_ID
      AND object_version_number = p_object_version
      FOR UPDATE NOWAIT;

Line: 800

   ELSE  -- update mode
      IF p_scoreresult_rec.score_result_id IS NULL THEN
         AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_SCORRES_NO_ID');

Line: 968

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

Line: 1101

PROCEDURE insert_percentile_results (
    p_score_id          IN   NUMBER
)
IS
   L_API_NAME              VARCHAR2(30) := 'insert_percentile_results';

Line: 1108

      SELECT count(*)
      FROM   ams_dm_source s
      WHERE  s.arc_used_for_object = 'SCOR'
      AND    s.used_for_object_id = p_score_id
      AND    s.continuous_score IS NOT NULL;

Line: 1115

      SELECT (s.continuous_score/100) confidence , party_id
      FROM   ams_dm_source s
      WHERE  s.arc_used_for_object = 'SCOR'
      AND    s.used_for_object_id = p_score_id
      AND    s.continuous_score IS NOT NULL
      ORDER BY  s.continuous_score desc;

Line: 1151

   SAVEPOINT insert_percentile_results;

Line: 1159

   DELETE FROM ams_dm_score_pct_results
   WHERE score_id = l_score_id;

Line: 1241

            /*INSERT INTO ams_dm_score_pct_results (
               score_result_id,
               last_update_date,
               last_updated_by,
               creation_date,
               created_by,
               last_update_login,
               object_version_number,
               score_id,
               percentile,
               num_records,
               num_records_cum,
               confidence,
               confidence_cum,
               random_result
            ) VALUES (
               ams_dm_score_pct_results_s.NEXTVAL,
               SYSDATE,
               FND_GLOBAL.user_id,
               SYSDATE,
               FND_GLOBAL.user_id,
               FND_GLOBAL.conc_login_id,
               1,
               l_score_id,
               l_percentile,
               l_record_count,
               l_num_records_cum,
               l_avg_confidence,
               l_avg_confidence_cum,
               0
            ); */

Line: 1279

            l_sql_str := 'update ams_dm_source set percentile = :1' ;

Line: 1284

            /*     l_sql_str := 'update ams_dm_source set percentile = :1' ;

Line: 1296

	       update ams_dm_source set percentile = l_percentile
               WHERE arc_used_for_object = 'SCOR' AND used_for_object_id = l_score_id AND party_id = l_temp_party_id_list(k) ;

Line: 1304

            l_temp_party_id_list.delete;

Line: 1311

      l_conf_list.delete;

Line: 1312

      l_party_id_list.delete;

Line: 1320

      AMS_UTILITY_PVT.debug_message('Inserted: ' || l_total_records  || ' Records');

Line: 1327

         INSERT INTO ams_dm_score_pct_results (
               score_result_id,
               last_update_date,
               last_updated_by,
               creation_date,
               created_by,
               last_update_login,
               object_version_number,
               score_id,
               percentile,
               num_records,
               num_records_cum,
               confidence,
               confidence_cum,
               random_result
            ) VALUES (
               ams_dm_score_pct_results_s.NEXTVAL,
               SYSDATE,
               FND_GLOBAL.user_id,
               SYSDATE,
               FND_GLOBAL.user_id,
               FND_GLOBAL.conc_login_id,
               1,
               l_score_id,
               l_percentile_list(h),
               l_record_count_list(h),
               l_num_recs_cum_list(h),
               l_avg_conf_list(h),
               l_avg_conf_cum_list(h),
               0
            );

Line: 1358

         INSERT INTO ams_dm_score_pct_results (
            score_result_id,
            last_update_date,
            last_updated_by,
            creation_date,
            created_by,
            last_update_login,
            object_version_number,
            score_id,
            percentile,
            num_records,
            num_records_cum,
            confidence,
            confidence_cum,
            random_result
         ) VALUES (
            ams_dm_score_pct_results_s.NEXTVAL,
            SYSDATE,
            FND_GLOBAL.user_id,
            SYSDATE,
            FND_GLOBAL.user_id,
            FND_GLOBAL.conc_login_id,
            1,
            l_score_id,
            0,
            0,
            0,
            0,
            0,
            0
         );

Line: 1390

      l_percentile_list.delete;

Line: 1391

      l_record_count_list.delete;

Line: 1392

      l_num_recs_cum_list.delete;

Line: 1393

      l_avg_conf_list.delete;

Line: 1394

      l_avg_conf_cum_list.delete;

Line: 1397

         AMS_UTILITY_PVT.debug_message('Inserted record for zeroth percentile: ');

Line: 1401

      UPDATE ams_dm_score_pct_results
         SET RANDOM_RESULT = l_avg_confidence_cum
       WHERE score_id = l_score_id
         AND percentile > 0;

Line: 1414

     ROLLBACK TO insert_percentile_results;

Line: 1419

     ROLLBACK TO insert_percentile_results;

Line: 1424

     ROLLBACK TO insert_percentile_results;

Line: 1428

END insert_percentile_results;

Line: 1445

      SELECT s.decile,
             s.score_result,
             AVG (s.continuous_score) confidence,
             COUNT(*) row_count
      FROM   ams_dm_source s
      WHERE  s.arc_used_for_object = 'SCOR'
      AND    s.used_for_object_id = p_score_id
      AND    s.decile IS NOT NULL
      AND    s.continuous_score IS NOT NULL
      GROUP BY s.decile, s.score_result;

Line: 1457

      SELECT m.model_type, m.model_id
      FROM   ams_dm_scores_all_b s, ams_dm_models_all_b m
      WHERE  s.score_id = p_score_id
      AND    m.model_id = s.model_id;

Line: 1493

   DELETE FROM ams_dm_score_results
   WHERE score_id = p_score_id;

Line: 1499

      INSERT INTO ams_dm_score_results (
         score_result_id,
         last_update_date,
         last_updated_by,
         creation_date,
         created_by,
         last_update_login,
         object_version_number,
         score_id,
         decile,
         num_records,
         score,
         confidence
      ) VALUES (
         ams_dm_score_results_s.NEXTVAL,
         SYSDATE,
         FND_GLOBAL.user_id,
         SYSDATE,
         FND_GLOBAL.user_id,
         FND_GLOBAL.conc_login_id,
         1,
         p_score_id,
         l_result_rec.decile,
         l_result_rec.row_count,
         l_result_rec.score_result,
         l_result_rec.confidence
      );

Line: 1530

      AMS_UTILITY_PVT.debug_message('Inserted Decile Records ');

Line: 1538

   AMS_DMSelection_PVT.is_org_prod_affn(
        p_model_id => l_model_id,
        x_is_org_prod     => l_is_org_prod
       );

Line: 1545

      insert_percentile_results (p_score_id);

Line: 1640

   SELECT L.SOURCE_TYPE_CODE, l.source_object_pk_field,T.target_id
     FROM AMS_DM_SCORES_ALL_B S, AMS_DM_MODELS_ALL_B M, AMS_DM_TARGETS_B T, AMS_LIST_SRC_TYPES L
    WHERE S.SCORE_ID       = p_score_id
      AND S.MODEL_ID       = M.MODEL_ID
      AND M.TARGET_ID      = T.TARGET_ID
      AND T.DATA_SOURCE_ID = L.LIST_SOURCE_TYPE_ID;

Line: 1648

      SELECT model_type
      FROM ams_dm_models_vl
      WHERE model_id=p_model_id;

Line: 1653

      SELECT model_id
      FROM AMS_DM_SCORES_ALL_B
      WHERE score_id=p_scor_id;

Line: 1665

      SELECT AMS_DM_SCORE_LISTS_S.NEXTVAL
      FROM dual;

Line: 1667

   l_insertSql      VARCHAR2(1000);

Line: 1691

   AMS_DMSelection_PVT.is_b2b_data_source(
         p_model_id => l_modl_id,
         x_is_b2b     => l_is_b2b
      );

Line: 1726

      l_sql_str := 'SELECT ams_dm_org_contacts.party_id, '''|| l_master_type || '''';

Line: 1739

      l_sql_str := 'SELECT party_id, '''|| l_master_type || '''';

Line: 1775

   ELSE  -- If List Generation is successful then insert into AMS_DM_SCORE_LISTS. kbasavar 1/22/2004 for 3363509

      l_insertSql  := 'INSERT INTO AMS_DM_SCORE_LISTS(SCORE_LIST_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,';

Line: 1778

      l_insertSql  := l_insertSql || 'CREATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, SCORE_ID, LIST_HEADER_ID)' ;

Line: 1779

      l_insertSql  := l_insertSql || ' VALUES(AMS_DM_SCORE_LISTS_S.NEXTVAL, SYSDATE, :1, SYSDATE, :2 ' ;

Line: 1780

      l_insertSql  := l_insertSql || ', :3 , 1, :4, :5)';

Line: 1783

         AMS_Utility_PVT.debug_message ('SCORE LISTS  SQL = ' || l_insertSql);

Line: 1786

      EXECUTE Immediate l_insertSql USING FND_GLOBAL.USER_ID,FND_GLOBAL.USER_ID,FND_GLOBAL.CONC_LOGIN_ID,p_score_id, x_list_header_id ;

Line: 1824

      SELECT m.target_group_type, s.owner_user_id
      FROM   ams_dm_scores_all_b s, ams_dm_models_all_b m
      WHERE  s.score_id = p_score_id
      AND    m.model_id = s.model_id;