APPS.AMS_DMSOURCE_PVT SQL Statements

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

Line: 87

PROCEDURE get_select_list (
   p_target_type     IN VARCHAR2,
   x_select_list     OUT NOCOPY VARCHAR2
);

Line: 95

   p_select_list  IN VARCHAR2,
   p_from_clause  IN VARCHAR2,
   p_where_clause IN VARCHAR2
);

Line: 103

   p_select_list  IN VARCHAR2,
   p_from_clause  IN VARCHAR2,
   p_where_clause IN VARCHAR2
);

Line: 118

PROCEDURE get_select_fields (
   p_data_source_id  IN NUMBER,
   p_target_id       IN NUMBER,
   p_is_b2bcustprof      IN BOOLEAN,
   x_select_fields   OUT NOCOPY VARCHAR2,
   x_return_status   OUT NOCOPY VARCHAR2
);

Line: 171

   SELECT SOURCE_ID
   FROM ams_dm_source
   WHERE SOURCE_ID = p_SOURCE_ID
   AND object_version_number = p_object_version
   FOR UPDATE NOWAIT;

Line: 309

      SELECT AMS_dm_source_s.NEXTVAL
      FROM dual;

Line: 313

      SELECT 1 FROM dual
      WHERE EXISTS (SELECT 1 FROM ams_dm_source
                    WHERE SOURCE_ID = l_id);

Line: 397

         ams_dm_source_PKG.Insert_Row(
             px_SOURCE_ID           => l_SOURCE_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_MODEL_TYPE           => p_source_rec.model_type,
             p_ARC_USED_FOR_OBJECT  => p_source_rec.arc_used_for_object,
             p_USED_FOR_OBJECT_ID   => p_source_rec.used_for_object_id,
             p_PARTY_ID             => p_source_rec.party_id,
             p_SCORE_RESULT         => p_source_rec.score_result,
             p_TARGET_VALUE         => p_source_rec.target_value,
             p_CONFIDENCE           => p_source_rec.confidence,
             p_CONTINUOUS_SCORE     => p_source_rec.continuous_score,
             p_decile               => p_source_rec.decile,
             p_percentile           => p_source_rec.percentile);

Line: 417

            AMS_Utility_PVT.error_message ('AMS_API_NO_INSERT');

Line: 487

PROCEDURE Update_Source(
    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_source_rec        IN    Source_Rec_Type,
    X_Object_Version_Number   OUT NOCOPY  NUMBER
)
IS
   l_api_name                  CONSTANT VARCHAR2(30) := 'Update_Source';

Line: 505

      SELECT *
      FROM   ams_dm_source
      WHERE  source_id = p_source_id;

Line: 518

      SAVEPOINT UPDATE_source_PVT;

Line: 548

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

Line: 556

            FND_MESSAGE.Set_Name('AMS', 'API_MISSING_UPDATE_TARGET');

Line: 591

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

Line: 605

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

Line: 609

      ams_dm_source_PKG.Update_Row(
         p_source_id             => p_source_rec.source_id,
         p_last_update_date      => SYSDATE,
         p_last_updated_by       => FND_GLOBAL.user_id,
         p_last_update_login     => FND_GLOBAL.CONC_LOGIN_ID,
         p_object_version_number => p_source_rec.object_version_number + 1,
         p_model_type            => p_source_rec.model_type,
         p_arc_used_for_object   => p_source_rec.arc_used_for_object,
         p_used_for_object_id    => p_source_rec.used_for_object_id,
         p_party_id              => p_source_rec.party_id,
         p_score_result          => p_source_rec.score_result,
         p_target_value          => p_source_rec.target_value,
         p_confidence            => p_source_rec.confidence,
         p_continuous_score      => p_source_rec.continuous_score,
         p_decile                => p_source_rec.decile,
         p_percentile            => p_source_rec.percentile);

Line: 657

     ROLLBACK TO UPDATE_source_PVT;

Line: 667

     ROLLBACK TO UPDATE_source_PVT;

Line: 677

     ROLLBACK TO UPDATE_source_PVT;

Line: 689

End Update_Source;

Line: 692

PROCEDURE Delete_Source(
    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_SOURCE_ID         IN  NUMBER,
    P_Object_Version_Number   IN   NUMBER
)
IS
   CURSOR c_obj_version(c_id NUMBER) IS
      SELECT object_version_number
      FROM ams_dm_source
      WHERE source_id = c_id;

Line: 709

   l_api_name                  CONSTANT VARCHAR2(30) := 'Delete_Source';

Line: 715

      SAVEPOINT DELETE_source_PVT;

Line: 745

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

Line: 769

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

Line: 774

         ams_dm_source_pkg.Delete_Row(
             p_source_id  => p_source_id);

Line: 822

     ROLLBACK TO DELETE_source_PVT;

Line: 832

     ROLLBACK TO DELETE_source_PVT;

Line: 842

     ROLLBACK TO DELETE_source_PVT;

Line: 854

End Delete_Source;

Line: 932

   ELSE  -- update mode
      IF p_source_rec.SOURCE_ID IS NULL THEN
         IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
            FND_MESSAGE.set_name('AMS', 'AMS_DM_SOURCE_NO_SOURCE_ID');

Line: 1275

   UPDATE ams_dm_source
   SET decile = (10 - FLOOR (LEAST (99, continuous_score)/10))
   WHERE arc_used_for_object = 'SCOR'
   AND   used_for_object_id = p_score_id
   AND   continuous_score IS NOT NULL;

Line: 1281

   UPDATE ams_dm_source
   SET percentile = (100 - FLOOR (LEAST (99, continuous_score)))
   WHERE arc_used_for_object = 'SCOR'
   AND   used_for_object_id = p_score_id
   AND   continuous_score IS NOT NULL;

Line: 1339

      SELECT name
      FROM   V$DATABASE;

Line: 1343

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

Line: 1409

   l_sql := 'INSERT INTO ams_dm_apply_stg_gt (SELECT idkey, score, probability FROM ' || l_source_object || ')';

Line: 1432

   UPDATE ams_dm_source s
   SET (score_result, confidence, continuous_score) = (SELECT score, probability * 100, DECODE (score, l_target_positive_value, probability, 1 - probability) * 100
--                                                       FROM ams_dm_apply_stg stg
                                                       FROM ams_dm_apply_stg_gt stg
                                                       WHERE stg.source_id = s.source_id)
--   WHERE s.source_id IN (SELECT source_id from ams_dm_apply_stg);

Line: 1438

   WHERE s.source_id IN (SELECT source_id from ams_dm_apply_stg_gt);

Line: 1441

   DELETE FROM ams_dm_apply_stg_gt;

Line: 1559

   l_select_list           VARCHAR2(32000);

Line: 1571

      SELECT model.target_id
      FROM   ams_dm_models_all_b model
      WHERE  model.model_id = p_model_id
      ;

Line: 1577

      SELECT model.target_id,model.model_id
      FROM   ams_dm_scores_all_b score, ams_dm_models_all_b model
      WHERE  model.model_id = score.model_id
      AND    score.score_id = p_score_id
      ;

Line: 1584

      SELECT SOURCE_OBJECT_NAME || '.' || SOURCE_OBJECT_PK_FIELD
      FROM   AMS_LIST_SRC_TYPES
      WHERE  LIST_SOURCE_TYPE_ID = p_data_source_id
      ;

Line: 1590

       SELECT model_type
       FROM ams_dm_models_vl
       WHERE model_id=p_model_id
       ;

Line: 1652

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

Line: 1666

   get_select_fields (
      p_data_source_id  => p_data_source_id,
      p_target_id       => l_target_id,
      p_is_b2bcustprof      => l_is_b2b_cust,
      x_select_fields   => l_select_list,
      x_return_status   => l_return_status
   );

Line: 1699

      p_msg_data        => SUBSTR ('SQL: ' || l_select_list || l_from_clause || l_where_clause, 1, 4000),
      p_msg_type        => 'DEBUG'
   );

Line: 1709

   l_check_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (';

Line: 1710

   l_check_sql := l_check_sql || 'SELECT ' || l_ds_pk_field || ', COUNT(*) ' || l_from_clause || l_where_clause_sel;

Line: 1744

         l_select_list,
         l_from_clause,
         l_where_clause
      );

Line: 1751

         l_select_list,
         l_from_clause,
         l_where_clause
      );

Line: 1784

    SELECT COUNT(*) FROM ams_dm_source
    WHERE  training_data_flag = data_flag
    AND   arc_used_for_object = p_object_type
    AND   used_for_object_id = p_object_id
    AND TARGET_VALUE = '1';

Line: 1805

    UPDATE ams_dm_source
    SET training_data_flag = DECODE (MOD (ABS (DBMS_RANDOM.random), 10), 0, 'N', 1, 'N', 2, 'N', 'Y')
    WHERE arc_used_for_object = p_object_type
    AND   used_for_object_id = p_object_id
    AND   TARGET_VALUE = '0';

Line: 1811

    UPDATE ams_dm_source
    SET training_data_flag = DECODE (MOD (ABS (DBMS_RANDOM.random), 10), 0, 'N', 1, 'N', 2, 'N', 'Y')
    WHERE arc_used_for_object = p_object_type
    AND   used_for_object_id = p_object_id
    AND   TARGET_VALUE = '1';

Line: 1850

PROCEDURE get_select_list (
   p_target_type     IN VARCHAR2,
   x_select_list     OUT NOCOPY VARCHAR2
)
IS
BEGIN
   x_select_list := '';

Line: 1858

END get_select_list;

Line: 1869

   p_select_list  IN VARCHAR2,
   p_from_clause  IN VARCHAR2,
   p_where_clause IN VARCHAR2
)
IS

l_return_status   VARCHAR2(1);

Line: 1896

   l_sql_str := 'SELECT ' || p_select_list;

Line: 1918

      EXECUTE IMMEDIATE 'GRANT SELECT on ' || l_training_view || ' to ' || l_odm_schema;

Line: 1919

      EXECUTE IMMEDIATE 'GRANT SELECT on ' || l_test_view || ' to ' || l_odm_schema;

Line: 1945

   p_select_list  IN VARCHAR2,
   p_from_clause  IN VARCHAR2,
   p_where_clause IN VARCHAR2
)
IS

   l_result          BOOLEAN;

Line: 1970

   l_sql_str := 'SELECT ' || p_select_list;

Line: 1985

      EXECUTE IMMEDIATE 'GRANT SELECT on ' || l_apply_view || ' to ' || l_odm_schema;

Line: 2017

   PROCEDURE get_select_fields (
      p_data_source_id  IN NUMBER,
      p_target_id       IN NUMBER,
      p_is_b2bcustprof          IN BOOLEAN,
      x_select_fields   OUT NOCOPY VARCHAR2,
      x_return_status   OUT NOCOPY VARCHAR2
   )
   IS
      CURSOR c_dup_fields (p_data_source_id IN NUMBER, p_target_field IN NUMBER, p_target_id IN NUMBER) IS
         SELECT lsf.source_column_name
         FROM   ams_list_src_fields lsf , ams_list_src_types lst
         WHERE  (lst.list_source_type_id = p_data_source_id
	         OR lst.list_source_type_id IN
		   (SELECT dts.data_source_id
		    FROM ams_dm_target_sources dts , ams_list_src_type_assocs lsa
		    WHERE dts.target_id = p_target_id
		    AND   lsa.sub_source_type_id = dts.data_source_id
		    AND   lsa.master_source_type_id = p_data_source_id
		    AND   lsa.enabled_flag = 'Y')
		)
	 AND    lst.enabled_flag = 'Y'
         AND    lsf.list_source_type_id = lst.list_source_type_id
	 AND    lsf.analytics_flag = 'Y'
	 AND    lsf.field_data_type in ('NUMBER' , 'VARCHAR2')
         AND    lsf.list_source_field_id <> p_target_field
         AND    lsf.enabled_flag = 'Y'
	 GROUP BY lsf.source_column_name
	 HAVING   COUNT(*) > 1
         ;

Line: 2048

         SELECT lst.source_object_name || '.' || lsf.source_column_name
         FROM   ams_list_src_fields lsf , ams_list_src_types lst
         WHERE  (lst.list_source_type_id = p_data_source_id
	         OR lst.list_source_type_id IN
		   (SELECT dts.data_source_id
		    FROM ams_dm_target_sources dts , ams_list_src_type_assocs lsa
		    WHERE dts.target_id = p_target_id
		    AND   lsa.sub_source_type_id = dts.data_source_id
		    AND   lsa.master_source_type_id = p_data_source_id
		    AND   lsa.enabled_flag = 'Y')
		)
	 AND    lst.enabled_flag = 'Y'
         AND    lsf.list_source_type_id = lst.list_source_type_id
	 AND    lsf.analytics_flag = 'Y'
	 AND    lsf.field_data_type in ('NUMBER' , 'VARCHAR2')
         AND    lsf.list_source_field_id <> p_target_field
         AND    lsf.enabled_flag = 'Y'
	 AND    lsf.source_column_name not in ('SOURCE_ID', 'TARGET_VALUE')
 	 -- Fix for bug # 4027150, added a filter not to select source_id and target_value
         ;

Line: 2070

         SELECT lsf.source_column_name
         FROM   ams_list_src_fields lsf , ams_list_src_types lst
         WHERE  lst.list_source_type_id IN
                    (SELECT dts.data_source_id
                     FROM ams_dm_target_sources dts , ams_list_src_type_assocs lsa
                     WHERE dts.target_id = p_target_id
                     AND   lsa.sub_source_type_id = dts.data_source_id
                     AND   lsa.master_source_type_id = p_data_source_id
		    AND   lsa.enabled_flag = 'Y')
	 AND    lst.enabled_flag = 'Y'
         AND    lsf.list_source_type_id = lst.list_source_type_id
	 AND    lsf.analytics_flag = 'Y'
	 AND    lsf.field_data_type in ('NUMBER' , 'VARCHAR2')
         AND    lsf.list_source_field_id <> p_target_field
         AND    lsf.enabled_flag = 'Y'
	 GROUP BY lsf.source_column_name
	 HAVING   COUNT(*) > 1
         ;

Line: 2090

         SELECT lst.source_object_name || '.' || lsf.source_column_name
         FROM   ams_list_src_fields lsf , ams_list_src_types lst
         WHERE  lst.list_source_type_id IN
		   (SELECT dts.data_source_id
		    FROM ams_dm_target_sources dts , ams_list_src_type_assocs lsa
		    WHERE dts.target_id = p_target_id
		    AND   lsa.sub_source_type_id = dts.data_source_id
		    AND   lsa.master_source_type_id = p_data_source_id
		    AND   lsa.enabled_flag = 'Y')
	 AND    lst.enabled_flag = 'Y'
         AND    lsf.list_source_type_id = lst.list_source_type_id
	 AND    lsf.analytics_flag = 'Y'
	 AND    lsf.field_data_type in ('NUMBER' , 'VARCHAR2')
         AND    lsf.list_source_field_id <> p_target_field
         AND    lsf.enabled_flag = 'Y'
         ;

Line: 2108

         SELECT target.source_field_id
         FROM   ams_dm_targets_b target
         WHERE  target.target_id = p_target_id
         ;

Line: 2120

      x_select_fields := 'distinct s.source_id';  -- mandatory identifier for ODM results

Line: 2158

         x_select_fields := x_select_fields || ', ' || l_field;

Line: 2168

            x_select_fields := x_select_fields || ', ' || l_field;

Line: 2214

         x_select_fields := x_select_fields || ', ' || l_field;

Line: 2224

            x_select_fields := x_select_fields || ', ' || l_field;

Line: 2237

      x_select_fields := x_select_fields || ', s.target_value';   -- mandatory target field

Line: 2238

   END get_select_fields;

Line: 2255

      SELECT source_object_name
             , source_object_name||decode(UPPER(remote_flag),'Y','@'||database_link,'')
             , source_object_pk_field
      FROM ams_list_src_types
      WHERE list_source_type_id = p_data_source_id
      ;

Line: 2267

      SELECT target_id
      FROM   ams_dm_models_v
      WHERE  model_id = p_model_id
      ;

Line: 2273

      SELECT model.target_id,model.model_id
      FROM   ams_dm_models_v model , ams_dm_scores_v score
      WHERE  model.model_id = score.model_id
      AND    score.score_id = p_score_id
      ;

Line: 2282

      SELECT a.source_object_name , a.source_object_name||decode(UPPER(a.remote_flag),'Y','@'||a.database_link,''), a.list_source_type_id
      FROM   ams_list_src_types a, ams_dm_target_sources b
      WHERE  a.list_source_type_id = b.data_source_id
      AND    a.enabled_flag = 'Y'
      AND    b.target_id = p_target_id
      AND EXISTS (SELECT 1 FROM ams_list_src_type_assocs c,ams_dm_targets_b d
                  WHERE d.target_id = p_target_id
                  AND c.MASTER_SOURCE_TYPE_ID = d.data_source_id
                  AND c.SUB_SOURCE_TYPE_ID = b.data_source_id
                  AND c.enabled_flag = 'Y')
      ;

Line: 2295

       SELECT model_type
       FROM ams_dm_models_vl
       WHERE model_id=p_model_id
       ;

Line: 2351

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

Line: 2378

    AMS_DMSelection_PVT.get_related_ds_condition ( p_master_ds_id => p_data_source_id,
   			       p_child_ds_id  => l_child_ds_id,
   			       x_sql_stmt     => l_relation_cond);