DBA Data[Home] [Help]

APPS.AMS_DMSELECTION_PVT SQL Statements

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

Line: 138

   PROCEDURE update_action_summary (
      p_arc_object_for     IN VARCHAR2,
      p_object_for_id      IN NUMBER,
      p_seeded_data_source IN BOOLEAN,
      x_return_status      OUT NOCOPY VARCHAR2
   );
Line: 227

   PROCEDURE update_source_target (
      p_object_type     IN VARCHAR2,
      p_object_id       IN NUMBER,
      x_return_status   OUT NOCOPY VARCHAR2
   );
Line: 321

   PROCEDURE get_insert_fields (
      p_select_object_type IN VARCHAR2,
      p_select_object_id   IN NUMBER,
      p_workbook_owner     IN VARCHAR2,
      p_workbook_name      IN VARCHAR2,
      p_worksheet_name     IN VARCHAR2,
      x_insert_fields      OUT NOCOPY VARCHAR2,
      x_return_status      OUT NOCOPY VARCHAR2,
      x_pk_field           OUT NOCOPY VARCHAR2
   );
Line: 348

      p_select_object_type IN VARCHAR2,
      p_select_object_id   IN NUMBER,
      p_workbook_owner     IN VARCHAR2,
      p_workbook_name      IN VARCHAR2,
      p_worksheet_name     IN VARCHAR2,
      p_is_b2b_custprof      IN BOOLEAN,
      x_from_clause        OUT NOCOPY VARCHAR2,
      x_return_status      OUT NOCOPY VARCHAR2
   );
Line: 374

      p_select_object_type IN VARCHAR2,
      p_select_object_id   IN NUMBER,
      p_workbook_owner     IN VARCHAR2,
      p_workbook_name      IN VARCHAR2,
      p_worksheet_name     IN VARCHAR2,
      p_is_b2b_custprof      IN BOOLEAN,
      x_where_clause       OUT NOCOPY VARCHAR2,
      x_return_status      OUT NOCOPY VARCHAR2
   );
Line: 464

   PROCEDURE validate_selection_status (
         p_model_id                 IN NUMBER,
         p_model_type               IN VARCHAR2,
         p_workbook_owner           IN VARCHAR2,
         p_workbook_name            IN VARCHAR2,
         p_worksheet_name           IN VARCHAR2,
         p_select_object_type       IN VARCHAR2,
         p_select_object_id         IN NUMBER,
         x_return_status OUT NOCOPY VARCHAR2
   );
Line: 487

   PROCEDURE validate_product_selections (
         p_model_id IN NUMBER,
         x_return_status OUT NOCOPY VARCHAR2
   );
Line: 497

   PROCEDURE Preview_Selections (
      p_arc_object      IN VARCHAR2,
      p_object_id       IN NUMBER,
      x_return_status   OUT NOCOPY VARCHAR2
   )
   IS
      L_API_NAME        CONSTANT VARCHAR2(30) := 'Preview Selections';
Line: 507

         SELECT target_id
         FROM   ams_dm_models_all_b
         WHERE  model_id = p_model_id
         ;
Line: 513

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

      update_action_summary (
         p_arc_object_for  => p_arc_object,
         p_object_for_id   => p_object_id,
         p_seeded_data_source => l_seeded_data_source,
         x_return_status   => l_return_status
      );
Line: 676

      p_msg_data        => L_API_NAME || ': After update_action_summary  Status= ' || l_return_status
  );
Line: 729

   END Preview_Selections;
Line: 736

   PROCEDURE Aggregate_Selections (
      p_arc_object      IN VARCHAR2,
      p_object_id       IN NUMBER,
      x_return_status   OUT NOCOPY VARCHAR2
   )
   IS
      L_API_NAME        CONSTANT VARCHAR2(30) := 'Aggregate Selections';
Line: 752

      Preview_Selections (
         p_arc_object      => p_arc_object,
         p_object_id       => p_object_id,
         x_return_status   => x_return_status
      );
Line: 763

      update_source_target (
         p_object_type     => p_arc_object,
         p_object_id       => p_object_id,
         x_return_status   => x_return_status
      );
Line: 784

   END Aggregate_Selections;
Line: 805

         SELECT arc_incl_object_from,
                incl_object_id
         FROM   ams_list_select_actions
         WHERE  arc_action_used_by = p_arc_object_for
         AND    action_used_by_id = p_object_for_id
         ;
Line: 814

         SELECT workbook_name,
                worksheet_name,
                workbook_owner_name
         FROM   ams_discoverer_sql
         WHERE  discoverer_sql_id = p_discoverer_sql_id
         ;
Line: 823

         SELECT target.data_source_id , target.target_id
         FROM   ams_dm_models_all_b model, ams_dm_targets_b target
         WHERE  model.model_id = p_model_id
         AND    target.target_id = model.target_id
         ;
Line: 830

         SELECT target.data_source_id , target.target_id
         FROM   ams_dm_scores_all_b score, ams_dm_models_all_b model, ams_dm_targets_b target
         WHERE  score.score_id = p_score_id
         AND    model.model_id = score.model_id
         AND    target.target_id = model.target_id
         ;
Line: 838

         SELECT model_type
         FROM ams_dm_models_vl
         WHERE model_id=p_model_id
         ;
Line: 844

         SELECT model_id, model_type
         FROM ams_dm_models_vl
         WHERE model_id=(select model_id from ams_dm_scores_vl where score_id=p_scor_id)
         ;
Line: 853

      l_insert_clause      VARCHAR2(16000);
Line: 854

      l_insert_fields      VARCHAR2(16000);
Line: 864

      l_insert_string      VARCHAR2(32000);
Line: 890

          l_insert_clause := 'INSERT INTO ams_dm_target_stg_gt (arc_object_used_by, ';
Line: 891

          l_insert_clause := l_insert_clause || 'object_used_by_id, arc_object, ';
Line: 892

          l_insert_clause := l_insert_clause || 'object_id, party_id) SELECT ';
Line: 900

             l_insert_clause := 'INSERT INTO ams_dm_org_contacts (arc_object_used_by, ';
Line: 901

             l_insert_clause := l_insert_clause || 'object_used_by_id, arc_object, ';
Line: 902

             l_insert_clause := l_insert_clause || 'object_id, party_id, org_party_id) SELECT ';
Line: 908

            validate_product_selections (
               p_model_id           => l_model_id,
               x_return_status      => x_return_status
            );
Line: 929

            validate_selection_status (
               p_model_id           => l_model_id,
               p_model_type         => l_model_type,
               p_workbook_owner     => l_workbook_rec.workbook_owner_name,
               p_workbook_name      => l_workbook_rec.workbook_name,
               p_worksheet_name     => l_workbook_rec.worksheet_name,
	       p_select_object_type => l_object_rec.arc_incl_object_from,
               p_select_object_id   => l_object_rec.incl_object_id,
               x_return_status      => x_return_status
            );
Line: 943

	    get_insert_fields (
               p_select_object_type => l_object_rec.arc_incl_object_from,
               p_select_object_id   => l_object_rec.incl_object_id,
               p_workbook_owner     => l_workbook_rec.workbook_owner_name,
               p_workbook_name      => l_workbook_rec.workbook_name,
               p_worksheet_name     => l_workbook_rec.worksheet_name,
               x_insert_fields      => l_insert_fields,
               x_return_status      => x_return_status,
               x_pk_field      => l_pk_field
            );
Line: 958

               p_select_object_type => l_object_rec.arc_incl_object_from,
               p_select_object_id   => l_object_rec.incl_object_id,
               p_workbook_owner     => l_workbook_rec.workbook_owner_name,
               p_workbook_name      => l_workbook_rec.workbook_name,
               p_worksheet_name     => l_workbook_rec.worksheet_name,
               p_is_b2b_custprof      => l_is_b2b_custprof,
               x_from_clause        => l_from_clause,
               x_return_status      => x_return_status
            );
Line: 974

               p_select_object_type => l_object_rec.arc_incl_object_from,
               p_select_object_id   => l_object_rec.incl_object_id,
               p_workbook_owner     => l_workbook_rec.workbook_owner_name,
               p_workbook_name      => l_workbook_rec.workbook_name,
               p_worksheet_name     => l_workbook_rec.worksheet_name,
               p_is_b2b_custprof      => l_is_b2b_custprof,
               x_where_clause       => l_where_clause,
               x_return_status      => x_return_status
            );
Line: 989

               l_insert_fields := l_insert_fields || ', hpr.object_id ';
Line: 1001

            l_sql_statement := l_insert_clause || l_insert_fields ||
                     ' FROM ' || l_from_clause;
Line: 1027

             EXECUTE IMMEDIATE 'INSERT INTO ams_dm_target_stg_gt(arc_object_used_by,object_used_by_id,arc_object,object_id,party_id)
        SELECT arc_object_used_by,object_used_by_id,arc_object,object_id,org_party_id
        FROM ams_dm_org_contacts  GROUP BY arc_object_used_by,object_used_by_id,arc_object,object_id,org_party_id';
Line: 1043

         get_insert_fields (
            p_select_object_type => G_ALTERNATIVE_DATA_SOURCE,
            p_select_object_id   => l_data_source_id,
            p_workbook_owner     => l_workbook_rec.workbook_owner_name,
            p_workbook_name      => l_workbook_rec.workbook_name,
            p_worksheet_name     => l_workbook_rec.worksheet_name,
            x_insert_fields      => l_insert_fields,
            x_return_status      => x_return_status,
            x_pk_field      => l_pk_field
         );
Line: 1058

            p_select_object_type => G_ALTERNATIVE_DATA_SOURCE,
            p_select_object_id   => l_target_id,
            p_workbook_owner     => l_workbook_rec.workbook_owner_name,
            p_workbook_name      => l_workbook_rec.workbook_name,
            p_worksheet_name     => l_workbook_rec.worksheet_name,
            p_is_b2b_custprof      => l_is_b2b_custprof,
            x_from_clause        => l_from_clause,
            x_return_status      => x_return_status
         );
Line: 1089

            p_select_object_type => G_ALTERNATIVE_DATA_SOURCE,
            p_select_object_id   => l_data_source_id,
            p_workbook_owner     => l_workbook_rec.workbook_owner_name,
            p_workbook_name      => l_workbook_rec.workbook_name,
            p_worksheet_name     => l_workbook_rec.worksheet_name,
            p_is_b2b_custprof      => l_is_b2b_custprof,
            x_where_clause       => l_where_clause,
            x_return_status      => x_return_status
         );
Line: 1102

         l_sql_statement := l_insert_clause || l_insert_fields ||
                  ' FROM ' || l_from_clause;
Line: 1154

         SELECT workbook_name,
                worksheet_name,
                workbook_owner_name
         FROM   ams_discoverer_sql
         WHERE  discoverer_sql_id = p_discoverer_sql_id
         ;
Line: 1204

         'INSERT INTO ams_dm_target_stg_gt ' ||
         '(arc_object_used_by, object_used_by_id, arc_object, object_id, party_id) ' ||
         'SELECT :arc_object' || ', :object_id' || ', :disco_wb, :disco_id' || ', ' || l_source_pk_field ||
         ' ' || l_from_and_where
      USING p_arc_object_for, p_object_for_id, 'DIWB', p_discoverer_sql_id;
Line: 1240

         SELECT model_type
         FROM ams_dm_models_vl
         WHERE model_id=p_model_id
         ;
Line: 1246

         SELECT model_id
         FROM   ams_dm_scores_all_b
         WHERE  score_id = p_score_id
      ;
Line: 1295

           DELETE FROM  ams_dm_org_contacts WHERE ARC_OBJECT_USED_BY =  p_arc_object_for  AND OBJECT_USED_BY_ID =  p_object_for_id;
Line: 1340

         SELECT a.source_type_code,
	        a.source_object_name || '.' || a.source_object_pk_field
         FROM   ams_list_src_types a , ams_discoverer_sql b
         WHERE  a.master_source_type_flag = 'Y'
         AND    a.enabled_flag = 'Y'
	 AND    b.workbook_owner_name = p_workbook_owner_name
	 AND    b.workbook_name = p_workbook_name
	 AND    b.worksheet_name = p_worksheet_name
	 AND    a.source_type_code = b.source_type_code
         ;
Line: 1423

         SELECT list_action_type, arc_incl_object_from, incl_object_id
         FROM   ams_list_select_actions
         WHERE  arc_action_used_by = p_arc_object_for
         AND    action_used_by_id = p_object_for_id
         ORDER BY order_number
         ;
Line: 1432

         SELECT target_positive_value
         FROM   ams_dm_models_all_b
         WHERE  model_id = p_model_id
         ;
Line: 1438

         SELECT COUNT(*),
                NVL (SUM (DECODE (target_value, l_target_value, 1, 0)), 0)
--       FROM ams_dm_inter_source_stg
         FROM ams_dm_int_src_stg_gt
         WHERE arc_object_used_by = p_arc_object_for
         AND   object_used_by_id = p_object_for_id
         AND   enabled_flag = 'Y'
         ;
Line: 1448

        SELECT status_code
          FROM ams_dm_models_all_b
         WHERE model_id = p_model_id;
Line: 1453

        SELECT status_code
          FROM ams_dm_scores_all_b
         WHERE score_id = p_score_id;
Line: 1458

         SELECT model_id
         FROM   ams_dm_scores_all_b
         WHERE  score_id = p_score_id
    ;
Line: 1464

         SELECT model_type
         FROM ams_dm_models_vl
         WHERE model_id=p_model_id
         ;
Line: 1487

      DELETE /*+ index(AMS_DM_SOURCE AMS_DM_SOURCE_U2) */ FROM ams_dm_source
      WHERE arc_used_for_object = p_arc_object_for
      AND   used_for_object_id = p_object_for_id;
Line: 1498

         UPDATE ams_dm_models_all_b
         SET    total_records = 0,
                total_positives = 0
         WHERE  model_id = p_object_for_id
         ;
Line: 1510

         UPDATE ams_dm_scores_all_b
         SET    total_records = 0,
                total_positives = 0
         WHERE  score_id = p_object_for_id
         ;
Line: 1562

      INSERT INTO ams_dm_int_src_stg_gt (
            arc_object_used_by,
            object_used_by_id,
            party_id,
            enabled_flag,
            random_generated_num,
            target_value
         )
         SELECT p_arc_object_for
                , p_object_for_id
                , t.party_id
                , 'N'
                , NULL
                , DECODE (t.target_flag, 'Y', l_target_value, '0')
--       FROM   ams_dm_target_stg t, HZ_PARTIES p
         FROM   ams_dm_target_stg_gt t, HZ_PARTIES p
               WHERE  t.arc_object_used_by = p_arc_object_for
               AND    t.object_used_by_id = p_object_for_id
               AND    t.arc_object = l_sources_rec.arc_incl_object_from
               AND    t.object_id = l_sources_rec.incl_object_id
               AND    t.party_id = p.party_id
               AND    p.party_type = l_party_type;
Line: 1592

               INSERT INTO ams_dm_int_src_stg_gt (
                  arc_object_used_by,
                  object_used_by_id,
                  party_id,
                  enabled_flag,
                  random_generated_num,
                  target_value
               )
               SELECT p_arc_object_for
                      , p_object_for_id
                      , t.party_id
                      , 'N'
                      , NULL
                      , DECODE (t.target_flag, 'Y', l_target_value, '0')
--             FROM   ams_dm_target_stg t , HZ_PARTIES p
               FROM   ams_dm_target_stg_gt t , HZ_PARTIES p
               WHERE  t.arc_object_used_by = p_arc_object_for
               AND    t.object_used_by_id = p_object_for_id
               AND    t.arc_object = l_sources_rec.arc_incl_object_from
               AND    t.object_id = l_sources_rec.incl_object_id
               AND    t.party_id = p.party_id
               AND    p.party_type = l_party_type
               AND NOT EXISTS (SELECT 1
--                             FROM   ams_dm_inter_source_stg i
                               FROM   ams_dm_int_src_stg_gt i
                               WHERE  i.arc_object_used_by = t.arc_object_used_by
                               AND    i.object_used_by_id = t.object_used_by_id
                               AND    i.party_id = t.party_id)
               ;
Line: 1624

               DELETE FROM ams_dm_int_src_stg_gt i
               WHERE arc_object_used_by = p_arc_object_for
               AND   object_used_by_id = p_object_for_id
               AND   EXISTS (SELECT 1
                             FROM   ams_dm_target_stg_gt t    -- ams_dm_target_stg t
                             WHERE  t.arc_object_used_by = p_arc_object_for
                             AND    t.object_used_by_id = p_object_for_id
                             AND    t.arc_object = l_sources_rec.arc_incl_object_from
                             AND    t.object_id = l_sources_rec.incl_object_id
                             AND    t.party_id = i.party_id)
               ;
Line: 1638

               DELETE FROM ams_dm_int_src_stg_gt i
               WHERE arc_object_used_by = p_arc_object_for
               AND   object_used_by_id = p_object_for_id
               AND   NOT EXISTS (SELECT 1
--                               FROM   ams_dm_target_stg t
                                 FROM   ams_dm_target_stg_gt t
                                 WHERE  t.arc_object_used_by = p_arc_object_for
                                 AND    t.object_used_by_id = p_object_for_id
                                 AND    t.arc_object = l_sources_rec.arc_incl_object_from
                                 AND    t.object_id = l_sources_rec.incl_object_id
                                 AND    t.party_id = i.party_id)
               ;
Line: 1658

         INSERT INTO ams_dm_int_src_stg_gt (
            arc_object_used_by,
            object_used_by_id,
            party_id,
            enabled_flag,
            random_generated_num,
            target_value
         )
         SELECT p_arc_object_for
                , p_object_for_id
                , party_id
                , 'N'
                , NULL
                , DECODE (target_flag, 'Y', l_target_value, '0')
--       FROM   ams_dm_target_stg
         FROM   ams_dm_target_stg_gt
         WHERE  arc_object_used_by = p_arc_object_for
         AND    object_used_by_id = p_object_for_id
         ;
Line: 1694

   INSERT INTO ams_dm_source (
    source_id,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    last_update_login,
    object_version_number,
    arc_used_for_object,
    used_for_object_id,
    party_id,
    target_value
   )
   SELECT ams_dm_source_s.NEXTVAL,
        SYSDATE,
        l_concUserId,
        SYSDATE,
        l_userId,
        l_concUserId,
        1,
        p_arc_object_for,
        p_object_for_id,
        party_id,
        target_value
-- FROM ams_dm_inter_source_stg
   FROM ams_dm_int_src_stg_gt
   WHERE arc_object_used_by = p_arc_object_for
   AND object_used_by_id = p_object_for_id
   AND enabled_flag = 'Y'
   ;
Line: 1738

         UPDATE ams_dm_models_all_b
         SET total_records = l_total_records
           , total_positives = l_total_positives
         WHERE model_id = p_object_for_id
         ;
Line: 1747

               AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_MODEL_SELECTIONS_EMPTY');
Line: 1767

         UPDATE ams_dm_scores_all_b
         SET total_records = l_row_count
         WHERE score_id = p_object_for_id
         ;
Line: 1775

               AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_SCORE_SELECTIONS_EMPTY');
Line: 1786

         DELETE FROM ams_dm_org_contacts
--          WHERE org_party_id NOT IN (SELECT distinct party_id from ams_dm_inter_source_stg WHERE arc_object_used_by = p_arc_object_for AND
            WHERE org_party_id NOT IN (SELECT distinct party_id from ams_dm_int_src_stg_gt WHERE arc_object_used_by = p_arc_object_for AND
                                                 object_used_by_id = p_object_for_id);
Line: 1826

         SELECT sql_string
         FROM   ams_discoverer_sql
         WHERE  workbook_owner_name = p_workbook_owner_name
         AND    workbook_name = p_workbook_name
         AND    worksheet_name = p_worksheet_name
         AND    sequence_order >= p_str_num
         ORDER BY sequence_order
         ;
Line: 1907

         SELECT m.model_id
                , m.model_type
                , m.target_positive_value
                , t.target_id
                , t.data_source_id
         FROM   ams_dm_models_all_b m, ams_dm_targets_b t
         WHERE  m.model_id = p_model_id
         AND    t.target_id = m.target_id
         ;
Line: 1919

         SELECT field.source_column_name
		, source1.source_object_name
		, source2.source_object_name
                , source2.source_object_pk_field
                , source1.source_object_name||decode(UPPER(source1.remote_flag),'Y','@'||source1.database_link,'')
                , source2.source_object_name||decode(UPPER(source2.remote_flag),'Y','@'||source2.database_link,'')
                , target.data_source_id
                , target.target_source_id
		, field.enabled_flag
         FROM   ams_dm_targets_b target, ams_list_src_fields field, ams_list_src_types source1, ams_list_src_types source2
         WHERE  target.target_id = p_target_id
         AND    field.list_source_field_id = source_field_id
         AND    source2.list_source_type_id = target.data_source_id
         AND    source1.list_source_type_id = target.target_source_id
         ;
Line: 1989

               UPDATE ams_dm_target_stg_gt t
                  SET    target_flag = (SELECT L_POSITIVE_TARGET_VALUE
                                     FROM   dual
                                     -- choang - 21-nov-2003 - bug 3275817
                                     -- changed to not exists and having clause
                                     WHERE NOT EXISTS (SELECT 1
                                        -- changed rosharma 19-jun-2003 bug # 3004453
                                        --FROM   ams_dm_party_details_time p
                                        --WHERE  p.party_id = t.party_id
                                        --AND    p.tot_num_order_3_months < 1
                                     FROM   bic_party_summ p , hz_relationships hpr
                                     WHERE  hpr.party_id = t.party_id
                                     AND  hpr.status = 'A'
                                     AND  hpr.subject_table_name = 'HZ_PARTIES'
                                     AND  hpr.object_table_name = 'HZ_PARTIES'
                                     AND  hpr.directional_flag = 'F'
                                     AND  hpr.relationship_code IN ('CONTACT_OF' ,   'EMPLOYEE_OF')
                                     AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
                                     AND  p.party_id = hpr.object_id        --the org's party id
                                     HAVING SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, p.period_start_date)) - 3),1,0,p.order_num)) >= 1
                                             -- end change rosharma 19-jun-2003 bug # 3004453
                                   )
                                 )
                  WHERE  arc_object_used_by = G_OBJECT_TYPE_MODEL
                  AND    object_used_by_id = p_model_id;
Line: 2016

               UPDATE ams_dm_target_stg_gt t
                  SET    target_flag = (SELECT L_POSITIVE_TARGET_VALUE
                                     FROM   dual
                                     -- choang - 21-nov-2003 - bug 3275817
                                     -- changed to not exists and having clause
                                     WHERE NOT EXISTS (SELECT 1
                                     -- changed rosharma 19-jun-2003 bug # 3004453
                                     --FROM   ams_dm_party_details_time p
                                     --WHERE  p.party_id = t.party_id
                                     --AND    p.tot_num_order_3_months < 1
                                     FROM   bic_party_summ p
                                     WHERE  p.party_id = t.party_id
                                     HAVING SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, p.period_start_date)) - 3),1,0,p.order_num)) >= 1
                      -- end change rosharma 19-jun-2003 bug # 3004453
                                   )
                                 )
                  WHERE  arc_object_used_by = G_OBJECT_TYPE_MODEL
                  AND    object_used_by_id = p_model_id;
Line: 2037

            UPDATE ams_dm_target_stg_gt t
               SET    target_flag = (SELECT L_POSITIVE_TARGET_VALUE
                                     FROM   dual
                                     WHERE EXISTS (SELECT 1
                                                   FROM   ams_campaign_schedules_b c, jtf_ih_interactions i,
                                                          jtf_ih_results_b r, ams_list_select_actions l
                                                   WHERE  c.schedule_id = l.incl_object_id
                                                   AND    l.arc_action_used_by = t.arc_object_used_by
                                                   AND    l.action_used_by_id = t.object_used_by_id
                                                   AND    i.party_id = t.party_id
                                                   AND    i.source_code = c.source_code
                                                   AND    r.result_id = i.result_id
                                                   AND    r.positive_response_flag = 'Y'
                                                  ))
               WHERE  arc_object_used_by = G_OBJECT_TYPE_MODEL
               AND    object_used_by_id = p_model_id;
Line: 2076

             l_sql := 'UPDATE ams_dm_target_stg_gt t';
Line: 2077

             l_sql := l_sql || ' SET t.target_flag = (SELECT ''' || L_POSITIVE_TARGET_VALUE || '''';
Line: 2078

             l_sql := l_sql || ' FROM dual WHERE EXISTS (SELECT 1';
Line: 2147

                  UPDATE ams_dm_target_stg_gt t
                     SET    target_flag = (SELECT L_POSITIVE_TARGET_VALUE
                                                  FROM   dual
                                                  WHERE EXISTS (SELECT 1
                                                                 FROM AMS_ACT_PRODUCTS aa, OE_ORDER_HEADERS_ALL oh, OE_ORDER_LINES_ALL ol,
                                                                          hz_cust_account_roles hcr, hz_relationships hpr
                                                                 WHERE aa.ARC_ACT_PRODUCT_USED_BY = 'MODL'
                                                                           and aa.ACT_PRODUCT_USED_BY_ID = p_model_id
                                                                           and ol.INVENTORY_ITEM_ID IN
                                                                                  (SELECT DISTINCT ic.INVENTORY_ITEM_ID
                                                                                    FROM MTL_ITEM_CATEGORIES ic
                                                                                    WHERE ic.CATEGORY_ID = aa.CATEGORY_ID
                                                                                         AND (ic.INVENTORY_ITEM_ID = aa.INVENTORY_ITEM_ID  OR aa.INVENTORY_ITEM_ID IS NULL))
                                                                           --and (oh.ORG_ID = aa.ORGANIZATION_ID OR aa.ORGANIZATION_ID IS NULL)
                                                                           --and ol.ORG_ID  = nvl(aa.ORGANIZATION_ID,ol.ORG_ID)
                                                                           and oh.SHIP_TO_CONTACT_ID = hcr.CUST_ACCOUNT_ROLE_ID
                                                                           and hcr.PARTY_ID=hpr.PARTY_ID
                                                                           and hpr.party_id = t.party_id
                                                                           and hpr.status = 'A'
                                                                           and hpr.subject_table_name = 'HZ_PARTIES'
                                                                           and hpr.object_table_name = 'HZ_PARTIES'
                                                                           and hpr.directional_flag = 'F'
                                                                           and hpr.relationship_code IN ('CONTACT_OF','EMPLOYEE_OF')
                                                                           and (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
                                                                           and oh.header_id = ol.header_id
                                                                           and nvl(oh.cancelled_flag,'N') <> 'Y'
                                                                           and nvl(oh.FLOW_STATUS_CODE,'N') <> 'CANCELLED'
                                                                           and nvl(ol.cancelled_flag,'N') <> 'Y'
                                                                           and nvl(ol.FLOW_STATUS_CODE,'N') <> 'CANCELLED'
                                                                           and oh.order_category_code <> 'RETURN'
                                                                           and ol.line_category_code <> 'RETURN'
                                                                          )
                                                  )
                  WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
                  AND    object_used_by_id = p_model_id;
Line: 2184

               UPDATE ams_dm_target_stg_gt t
                  SET    target_flag = (SELECT L_POSITIVE_TARGET_VALUE
                                           FROM   dual
                                           WHERE EXISTS (SELECT 1
                                                            FROM AMS_ACT_PRODUCTS aa,
								 OE_ORDER_HEADERS_ALL oh,
								 OE_ORDER_LINES_ALL ol,
								 --hz_cust_site_uses_all hcsu,
								 --hz_cust_acct_sites_all hcs,
								 HZ_CUST_ACCOUNTS hc
                                                                 WHERE aa.ARC_ACT_PRODUCT_USED_BY = 'MODL'
                                                                       and aa.ACT_PRODUCT_USED_BY_ID = p_model_id
                                                                       and ol.INVENTORY_ITEM_ID IN
                                                                            (SELECT DISTINCT ic.INVENTORY_ITEM_ID
                                                                                FROM MTL_ITEM_CATEGORIES ic
                                                                                WHERE ic.CATEGORY_ID = aa.CATEGORY_ID
                                                                                     AND (ic.INVENTORY_ITEM_ID = aa.INVENTORY_ITEM_ID  OR aa.INVENTORY_ITEM_ID IS NULL))
                                                                       --and (oh.ORG_ID = aa.ORGANIZATION_ID OR aa.ORGANIZATION_ID IS NULL)
                                                                       --and ol.ORG_ID  = nvl(aa.ORGANIZATION_ID,ol.ORG_ID)
                                                                       --and oh.SHIP_TO_ORG_ID = hcsu.site_use_id
                       						       --and hcsu.cust_acct_site_id = hcs.cust_acct_site_id
								       --and hcs.cust_account_id = hc.cust_account_id
                                                                       and oh.SOLD_TO_ORG_ID = hc.cust_account_id
                                                                       and hc.PARTY_ID = t.party_id
                                                                       and oh.header_id = ol.header_id
                                                                       and nvl(oh.cancelled_flag,'N') <> 'Y'
                                                                       and nvl(oh.FLOW_STATUS_CODE,'N') <> 'CANCELLED'
                                                                       and nvl(ol.cancelled_flag,'N') <> 'Y'
                                                                       and nvl(ol.FLOW_STATUS_CODE,'N') <> 'CANCELLED'
                                                                       and oh.order_category_code <> 'RETURN'
                                                                       and ol.line_category_code <> 'RETURN'
                                                                    )
                                                        )
               WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
               AND    object_used_by_id = p_model_id;
Line: 2254

              l_sql := 'UPDATE ams_dm_target_stg_gt t';
Line: 2255

              l_sql := l_sql || ' SET t.target_flag = (SELECT ''' || L_POSITIVE_TARGET_VALUE || '''';
Line: 2291

      UPDATE ams_dm_target_stg_gt
      SET    target_flag = L_NEGATIVE_TARGET_VALUE
      WHERE  arc_object_used_by = G_OBJECT_TYPE_MODEL
      AND    object_used_by_id = p_model_id
      AND    target_flag IS NULL;
Line: 2313

   PROCEDURE update_action_summary (
      p_arc_object_for     IN VARCHAR2,
      p_object_for_id      IN NUMBER,
      p_seeded_data_source IN BOOLEAN,
      x_return_status      OUT NOCOPY VARCHAR2
   )
   IS
      L_API_NAME           CONSTANT VARCHAR2(30) := 'Update Select Actions Summary';
Line: 2331

         SELECT list_select_action_id, object_version_number
         FROM   ams_list_select_actions
         WHERE  arc_action_used_by = p_arc_object_for
         AND    action_used_by_id = p_object_for_id
         AND    arc_incl_object_from = p_arc_incl_object
         AND    incl_object_id = p_incl_object_id
         ;
Line: 2340

         SELECT arc_object,
                object_id,
                COUNT(*) total_selected,  -- total selected
                SUM (DECODE (target_flag, 'Y', 1, 0)) total_targeted
--       FROM   ams_dm_target_stg
         FROM   ams_dm_target_stg_gt
         WHERE  arc_object_used_by = p_arc_object_for
         AND    object_used_by_id = p_object_for_id
         GROUP BY arc_object, object_id
         ;
Line: 2363

         FETCH c_action_version INTO l_action_rec.list_select_action_id, l_action_rec.object_version_number;
Line: 2366

         l_action_rec.no_of_rows_used := l_summary_rec.total_selected;
Line: 2369

         AMS_ListAction_PVT.Update_ListAction (
            p_api_version        => 1.0,
            p_init_msg_list      => FND_API.G_FALSE,
            p_commit             => FND_API.G_TRUE,
            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,
            p_action_rec         => l_action_rec
         );
Line: 2393

   END update_action_summary;
Line: 2410

   PROCEDURE update_source_target (
      p_object_type     IN VARCHAR2,
      p_object_id       IN NUMBER,
      x_return_status   OUT NOCOPY VARCHAR2
   )
   IS
      L_API_NAME              CONSTANT VARCHAR2(30) := 'Update Source Target';
Line: 2424

         SELECT m.model_id
                , m.model_type
                , m.target_positive_value
                , t.target_id
                , t.data_source_id
         FROM   ams_dm_models_all_b m, ams_dm_targets_b t
         WHERE  m.model_id = p_model_id
         AND    t.target_id = m.target_id
         ;
Line: 2436

         SELECT m.model_id
                , m.model_type
                , m.target_positive_value
                , t.target_id
                , t.data_source_id
         FROM   ams_dm_models_all_b m, ams_dm_scores_all_b s, ams_dm_targets_b t
         WHERE  m.model_id = s.model_id
         AND    s.score_id = p_score_id
         AND    t.target_id = m.target_id
         ;
Line: 2467

         UPDATE /*+ index(t AMS_DM_SOURCE_U2) */ ams_dm_source t
         SET    target_value = (SELECT L_POSITIVE_TARGET_VALUE
                                FROM   dual
                                WHERE EXISTS (SELECT 1
                                              FROM   hz_cust_accounts c, hz_suspension_activity s
                                              WHERE  c.party_id = t.party_id
                                              AND    s.cust_account_id = c.cust_account_id
                                              AND    s.action_type = L_NON_LOYAL_CODE
                                             ))
         WHERE  arc_used_for_object = p_object_type
         AND    used_for_object_id = p_object_id;
Line: 2479

         UPDATE /*+ index(t AMS_DM_SOURCE_U2) */ ams_dm_source t
         SET    target_value = (SELECT L_POSITIVE_TARGET_VALUE
                                FROM   dual
                                WHERE EXISTS (SELECT 1
                                              FROM   ams_campaign_schedules_b c,
                                                     jtf_ih_interactions i,
                                                     ams_list_select_actions l
                                              WHERE  c.schedule_id = l.incl_object_id
                                              AND    l.arc_action_used_by = t.arc_used_for_object
                                              AND    l.action_used_by_id = t.used_for_object_id
                                              AND    i.party_id = t.party_id
                                              AND    i.source_code = c.source_code
/*** enable this code when positive responses are captured
                                WHERE EXISTS (SELECT 1
                                              FROM   ams_campaign_schedules_b c, jtf_ih_interactions i,
                                                     jtf_ih_results_b r, ams_list_select_actions l
                                              WHERE  c.schedule_id = l.incl_object_id
                                              AND    l.arc_action_used_by = t.arc_used_for_object
                                              AND    l.action_used_by_id = t.used_for_object_id
                                              AND    i.party_id = t.party_id
                                              AND    i.source_code = c.source_code
                                              AND    r.result_id = i.result_id
                                              AND    r.positive_response_flag = 'Y'
***/
                                             ))
         WHERE  arc_used_for_object = p_object_type
         AND    used_for_object_id = p_object_id;
Line: 2511

      UPDATE /*+ index(AMS_DM_SOURCE AMS_DM_SOURCE_U2) */ ams_dm_source
      SET    target_value = L_NEGATIVE_TARGET_VALUE
      WHERE  arc_used_for_object = p_object_type
      AND    used_for_object_id = p_object_id
      AND    target_value IS NULL;
Line: 2526

   END update_source_target;
Line: 2542

      Preview_Selections (
         p_arc_object      => p_arc_object,
         p_object_id       => p_object_id,
         x_return_status   => l_return_status
      );
Line: 2585

      Aggregate_Selections (
         p_arc_object      => p_arc_object,
         p_object_id       => p_object_id,
         x_return_status   => l_return_status
      );
Line: 2615

      L_SELECTION_TYPE_STANDARD  CONSTANT VARCHAR2(30) := 'STANDARD';
Line: 2616

      L_SELECTION_TYPE_NTH_ROW   CONSTANT VARCHAR2(30) := 'NTH_RECORD';
Line: 2617

      L_SELECTION_TYPE_PCT       CONSTANT VARCHAR2(30) := 'RANDOM';
Line: 2623

      l_row_selection_type    VARCHAR2(30);
Line: 2628

         SELECT min_records,
                max_records,
                row_selection_type,
                every_nth_row,
                pct_random
         FROM   ams_dm_models_all_b
         WHERE  model_id = p_model_id
         ;
Line: 2637

         SELECT min_records,
                max_records,
                row_selection_type,
                every_nth_row,
                pct_random
         FROM   ams_dm_scores_all_b
         WHERE  score_id = p_score_id
         ;
Line: 2647

         SELECT COUNT(*)
--       FROM   ams_dm_inter_source_stg
         FROM   ams_dm_int_src_stg_gt
         WHERE  arc_object_used_by = p_arc_object
         AND    object_used_by_id = p_object_id
         ;
Line: 2665

         FETCH c_model_details INTO l_min_records, l_max_records, l_row_selection_type, l_every_nth_row, l_pct_random;
Line: 2669

         FETCH c_score_details INTO l_min_records, l_max_records, l_row_selection_type, l_every_nth_row, l_pct_random;
Line: 2678

      IF l_row_selection_type = L_SELECTION_TYPE_STANDARD THEN
--       UPDATE ams_dm_inter_source_stg
         UPDATE ams_dm_int_src_stg_gt
         SET    enabled_flag = 'Y'
         WHERE  arc_object_used_by = p_arc_object_for
         AND    object_used_by_id = p_object_for_id
         AND    rownum <= NVL (l_max_records, l_total_records)
         ;
Line: 2687

      ELSIF l_row_selection_type = L_SELECTION_TYPE_NTH_ROW THEN
         randomize_nth_rows (
            p_arc_object_for  => p_arc_object_for,
            p_object_for_id   => p_object_for_id,
            p_min_rows        => l_min_records,
            p_max_rows        => l_max_records,
            p_total_rows      => l_total_records,
            p_every_nth_row   => l_every_nth_row,
            x_return_status   => x_return_status
         );
Line: 2701

      ELSIF l_row_selection_type = L_SELECTION_TYPE_PCT THEN
         randomize_by_pct (
            p_arc_object_for  => p_arc_object_for,
            p_object_for_id   => p_object_for_id,
            p_min_rows        => l_min_records,
            p_max_rows        => l_max_records,
            p_total_rows      => l_total_records,
            p_pct_random      => l_pct_random,
            x_return_status   => x_return_status
         );
Line: 2715

         AMS_Utility_PVT.error_message ('AMS_DM_BAD_SELECTION_TYPE', 'SELECTION_TYPE', l_row_selection_type);
Line: 2741

         SELECT party_id
--       FROM   ams_dm_inter_source_stg
         FROM   ams_dm_int_src_stg_gt
         WHERE  arc_object_used_by = p_arc_object
         AND    object_used_by_id = p_object_id
         ORDER BY random_generated_num
         ;
Line: 2763

      UPDATE ams_dm_int_src_stg_gt
      SET    random_generated_num = DBMS_RANDOM.random
      WHERE  arc_object_used_by = p_arc_object_for
      AND    object_used_by_id = p_object_for_id
      ;
Line: 2780

         UPDATE ams_dm_int_src_stg_gt
         SET    enabled_flag = 'Y'
         WHERE  arc_object_used_by = p_arc_object_for
         AND    object_used_by_id = p_object_for_id
         AND    party_id = l_object_ids(i);
Line: 2819

      UPDATE ams_dm_int_src_stg_gt
      SET    enabled_flag = DECODE (MOD (rownum, p_every_nth_row), 0, 'Y', 'N')
      WHERE  arc_object_used_by = p_arc_object_for
      AND    object_used_by_id = p_object_for_id
      ;
Line: 2830

         UPDATE ams_dm_int_src_stg_gt
         SET    enabled_flag = DECODE (SIGN (l_local_max_rows - rownum), -1, 'N', 'Y')
         WHERE  arc_object_used_by = p_arc_object_for
         AND    object_used_by_id = p_object_for_id
         AND    enabled_flag = 'Y'
         ;
Line: 2865

   PROCEDURE get_insert_fields (
      p_select_object_type IN VARCHAR2,
      p_select_object_id   IN NUMBER,
      p_workbook_owner     IN VARCHAR2,
      p_workbook_name      IN VARCHAR2,
      p_worksheet_name     IN VARCHAR2,
      x_insert_fields      OUT NOCOPY VARCHAR2,
      x_return_status      OUT NOCOPY VARCHAR2,
      x_pk_field             OUT NOCOPY VARCHAR2
   )
   IS
      CURSOR c_pk_field (p_list_source_type_id IN NUMBER) IS
         SELECT source_object_name || '.' || source_object_pk_field
         FROM   ams_list_src_types
         WHERE  list_source_type_id = p_list_source_type_id
         ;
Line: 2886

      x_insert_fields := ':p_object_type, :p_object_id , :p_select_object_type, ';
Line: 2887

      x_insert_fields := x_insert_fields || ':p_select_object_id , ';
Line: 2889

      IF p_select_object_type = G_ALTERNATIVE_DATA_SOURCE THEN
         OPEN c_pk_field (p_select_object_id);
Line: 2894

         x_insert_fields := x_insert_fields || l_source_pk_field;
Line: 2896

      ELSIF p_select_object_type = 'LIST' THEN
         x_insert_fields := x_insert_fields || 'e.party_id';
Line: 2899

      ELSIF p_select_object_type = 'CSCH' THEN
         x_insert_fields := x_insert_fields || 'e.party_id';
Line: 2902

      ELSIF p_select_object_type = 'CELL' THEN
         x_insert_fields := x_insert_fields || 'aps.party_id';
Line: 2905

      ELSIF p_select_object_type = 'DIWB' THEN
         get_customer_field (
            p_workbook_owner_name   => p_workbook_owner,
            p_workbook_name   => p_workbook_name,
            p_worksheet_name  => p_worksheet_name,
            x_customer_field  => l_source_pk_field,
            x_return_status   => x_return_status
         );
Line: 2917

         x_insert_fields := x_insert_fields || l_source_pk_field;
Line: 2920

   END get_insert_fields;
Line: 2941

      p_select_object_type IN VARCHAR2,
      p_select_object_id   IN NUMBER,
      p_workbook_owner     IN VARCHAR2,
      p_workbook_name      IN VARCHAR2,
      p_worksheet_name     IN VARCHAR2,
      p_is_b2b_custprof      IN BOOLEAN,
      x_from_clause        OUT NOCOPY VARCHAR2,
      x_return_status      OUT NOCOPY VARCHAR2
   )
   IS
      L_API_NAME           CONSTANT VARCHAR2(30) := 'get_from_clause';
Line: 2955

         SELECT a.source_object_name||decode(UPPER(a.remote_flag),'Y','@'||a.database_link,'')
    FROM   ams_list_src_types a, ams_dm_targets_b b
    WHERE  a.list_source_type_id = b.data_source_id
    AND    b.target_id = p_target_id
    UNION
    SELECT a.source_object_name||decode(UPPER(a.remote_flag),'Y','@'||a.database_link,'')
         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: 2980

      IF p_select_object_type = G_ALTERNATIVE_DATA_SOURCE THEN
         x_from_clause := '';
Line: 2982

         OPEN c_source_object (p_select_object_id);
Line: 2995

      ELSIF p_select_object_type = 'LIST' THEN
         IF p_is_b2b_custprof THEN
            x_from_clause := 'ams_list_entries e';
Line: 3001

      ELSIF p_select_object_type = 'CSCH' THEN
         x_from_clause := 'ams_act_lists l, ams_list_entries e';
Line: 3003

      ELSIF p_select_object_type = 'CELL' THEN
         x_from_clause := 'ams_party_market_segments aps';
Line: 3005

      ELSIF p_select_object_type = 'DIWB' THEN
         get_from_sql (
            p_workbook_owner_name   => p_workbook_owner,
            p_workbook_name   => p_workbook_name,
            p_worksheet_name  => p_worksheet_name,
            x_from_sql        => l_from_and_where,
            x_found           => l_found,
            x_return_status   => x_return_status
         );
Line: 3045

      p_select_object_type IN VARCHAR2,
      p_select_object_id   IN NUMBER,
      p_workbook_owner     IN VARCHAR2,
      p_workbook_name      IN VARCHAR2,
      p_worksheet_name     IN VARCHAR2,
      p_is_b2b_custprof      IN BOOLEAN,
      x_where_clause       OUT NOCOPY VARCHAR2,
      x_return_status      OUT NOCOPY VARCHAR2
   )
   IS
      L_API_NAME        CONSTANT VARCHAR2(30) := 'get_where_clause';
Line: 3059

         SELECT ds.source_object_name || '.' || field.source_column_name
         FROM   ams_dm_models_all_b model, ams_dm_targets_b target, ams_list_src_fields field , ams_list_src_types ds
         WHERE  model.model_id = p_model_id
         AND    target.target_id = model.target_id
         AND    field.list_source_field_id = target.source_field_id
         AND    ds.list_source_type_id = target.target_source_id
         ;
Line: 3068

         SELECT source.data_source_id
         FROM   ams_dm_models_all_b model, ams_dm_target_sources source, ams_list_src_types lst
         WHERE  model.model_id = p_model_id
         AND    source.target_id = model.target_id
         AND    lst.list_source_type_id = source.data_source_id
         AND    lst.enabled_flag = 'Y'
         AND EXISTS (SELECT 1 FROM ams_list_src_type_assocs c,ams_dm_targets_b d
	             WHERE d.target_id = model.target_id
		     AND c.MASTER_SOURCE_TYPE_ID = d.data_source_id
		     AND c.SUB_SOURCE_TYPE_ID = source.data_source_id
		     AND c.enabled_flag = 'Y')
         ;
Line: 3082

         SELECT source.data_source_id
         FROM   ams_dm_scores_all_b score, ams_dm_models_all_b model, ams_dm_target_sources source, ams_list_src_types lst
         WHERE  score.score_id = p_score_id
         AND    model.model_id = score.model_id
         AND    source.target_id = model.target_id
         AND    lst.list_source_type_id = source.data_source_id
         AND    lst.enabled_flag = 'Y'
         AND EXISTS (SELECT 1 FROM ams_list_src_type_assocs c,ams_dm_targets_b d
	             WHERE d.target_id = model.target_id
		     AND c.MASTER_SOURCE_TYPE_ID = d.data_source_id
		     AND c.SUB_SOURCE_TYPE_ID = source.data_source_id
		     AND c.enabled_flag = 'Y')
         ;
Line: 3107

      IF p_select_object_type = G_ALTERNATIVE_DATA_SOURCE THEN
         IF p_object_type = G_OBJECT_TYPE_MODEL THEN
            OPEN c_target_field (p_object_id);
Line: 3119

            p_data_source_id  => p_select_object_id,
            x_filter          => l_perz_filter,
            x_return_status   => x_return_status
         );
Line: 3134

               get_related_ds_condition ( p_master_ds_id => p_select_object_id,
                                          p_child_ds_id  => l_child_ds_id,
                                          x_sql_stmt     => l_relation_cond);
Line: 3149

               get_related_ds_condition ( p_master_ds_id => p_select_object_id,
                                          p_child_ds_id  => l_child_ds_id,
                                          x_sql_stmt     => l_relation_cond);
Line: 3209

      ELSIF p_select_object_type = 'LIST' THEN
         IF p_is_b2b_custprof  THEN
            x_where_clause := 'e.list_header_id = :list_header_id ' ||
                               'AND e.enabled_flag = ''Y''';
Line: 3218

      ELSIF p_select_object_type = 'CSCH' THEN
         x_where_clause := 'l.list_used_by = ''CSCH'' ' ||
                           'AND l.list_used_by_id = :incl_object_id ' ||
                           'AND l.list_act_type = ''TARGET'' ' ||
                           'AND e.list_header_id = l.list_header_id ' ||
                           'AND e.enabled_flag = ''Y''';
Line: 3224

      ELSIF p_select_object_type = 'CELL' THEN
         x_where_clause := 'aps.market_segment_id = :object_id';
Line: 3226

      ELSIF p_select_object_type = 'DIWB' THEN
	 get_wb_filter (
            p_workbook_owner  => p_workbook_owner,
            p_workbook_name   => p_workbook_name,
            p_worksheet_name  => p_worksheet_name,
            x_filter          => l_from_and_where,
            x_return_status   => x_return_status
         );
Line: 3403

         SELECT target_value,
                target_operator,
                range_value
         FROM ams_dm_target_values_b
         WHERE target_id = p_target_id;
Line: 3499

     SELECT d.SOURCE_CATEGORY
     FROM   ams_dm_models_all_b m,ams_dm_targets_b t,ams_list_src_types d
     WHERE  m.model_id = p_model_id
     AND    m.target_id = t.target_id
     AND    t.DATA_SOURCE_ID=d.LIST_SOURCE_TYPE_ID;
Line: 3570

         SELECT a.SOURCE_OBJECT_NAME as CHILD_SOURCE,
         b.SUB_SOURCE_TYPE_PK_COLUMN as CHILD_COLUMN,
         c.SOURCE_OBJECT_NAME as PARENT_SOURCE,
         NVL(b.MASTER_SOURCE_TYPE_PK_COLUMN , c.SOURCE_OBJECT_PK_FIELD) as PARENT_COLUMN
    FROM ams_list_src_types a, ams_list_src_type_assocs b, ams_list_src_types c
         where b.MASTER_SOURCE_TYPE_ID = p_master_ds_id
         and b.SUB_SOURCE_TYPE_ID = p_child_ds_id
    and b.ENABLED_FLAG = 'Y'
         and a.LIST_SOURCE_TYPE_ID=b.SUB_SOURCE_TYPE_ID
         and c.LIST_SOURCE_TYPE_ID = b.MASTER_SOURCE_TYPE_ID
	 and a.enabled_flag = 'Y'
	 and c.enabled_flag = 'Y'
         ;
Line: 3629

     SELECT 1
     FROM   ams_dm_targets_b t,ams_list_src_types d
     WHERE  t.target_id = p_target_id
     AND    t.DATA_SOURCE_ID=d.LIST_SOURCE_TYPE_ID
     AND    d.SOURCE_TYPE_CODE = 'AMS_DM_PARTY_ATTRIBUTES_V'
     UNION
     SELECT 1
     FROM   ams_dm_target_sources t,ams_list_src_types d
     WHERE  t.target_id = p_target_id
     AND    t.DATA_SOURCE_ID=d.LIST_SOURCE_TYPE_ID
     AND    d.SOURCE_TYPE_CODE = 'AMS_DM_PARTY_ATTRIBUTES_V'
     ;
Line: 3679

      SELECT d.source_type_code
      FROM   ams_dm_models_all_b m,ams_dm_targets_b t,ams_list_src_types d
      WHERE  m.model_id = p_model_id
      AND    m.target_id = t.target_id
      AND    t.data_source_id=d.list_source_type_id;
Line: 3719

   PROCEDURE validate_selection_status (
         p_model_id                 IN NUMBER,
         p_model_type               IN VARCHAR2,
         p_workbook_owner           IN VARCHAR2,
         p_workbook_name            IN VARCHAR2,
         p_worksheet_name           IN VARCHAR2,
         p_select_object_type       IN VARCHAR2,
         p_select_object_id         IN NUMBER,
         x_return_status OUT NOCOPY VARCHAR2
   )
   IS
   L_API_NAME        VARCHAR2(30) := 'Validate Selection Status';
Line: 3733

      SELECT list_name, status_code
      FROM   ams_list_headers_vl
      WHERE  list_header_id = p_list_id
      ;
Line: 3739

      SELECT cell_name, status_code
      FROM   ams_cells_vl
      WHERE  cell_id = p_cell_id
      ;
Line: 3747

      SELECT sql_string, sequence_order
      FROM ams_discoverer_sql
      WHERE workbook_name = p_workbook_name
      AND worksheet_name = p_worksheet_name
      AND workbook_owner_name = p_workbook_owner_name
      ORDER BY sequence_order;
Line: 3763

      IF p_select_object_type = 'LIST' THEN
         OPEN c_valid_list (p_select_object_id);
Line: 3769

            AMS_Utility_PVT.debug_message (L_API_NAME || ' :: Selected List Name : '|| l_list_name || ', Status : ' || l_status_code );
Line: 3777

      ELSIF p_select_object_type = 'CELL' THEN
         OPEN c_valid_cell (p_select_object_id);
Line: 3783

            AMS_Utility_PVT.debug_message (L_API_NAME || ' :: Selected Segment Name : '|| l_cell_name || ', Status : ' || l_status_code );
Line: 3791

      ELSIF p_select_object_type = 'DIWB' THEN
         IF (AMS_DEBUG_HIGH_ON) THEN
            AMS_Utility_PVT.debug_message (L_API_NAME || ' :: Checking Workbook : '|| p_workbook_name || '::' || p_worksheet_name );
Line: 3818

   END validate_selection_status;
Line: 3833

   PROCEDURE validate_product_selections (
         p_model_id IN NUMBER,
         x_return_status OUT NOCOPY VARCHAR2
   )
   IS
   L_API_NAME        VARCHAR2(60) := 'Validate Product Affinity Selection Status';
Line: 3841

      SELECT a.category_id, a.inventory_item_id, a.organization_id, a.category_set_id, b.padded_concatenated_segments
      FROM   ams_act_products a, mtl_system_items_kfv b
      WHERE  a.ARC_ACT_PRODUCT_USED_BY = 'MODL'
      AND    a.ACT_PRODUCT_USED_BY_ID  = p_model_id
      AND    a.inventory_item_id = b.inventory_item_id(+)
      AND    a.organization_id = b.organization_id(+)
      AND    a.category_set_id in (select distinct category_set_id from ENI_PROD_DEN_HRCHY_PARENTS_V)
      ;
Line: 3851

      SELECT 1
      FROM   ENI_PROD_DEN_HRCHY_PARENTS_V
      WHERE  category_id = p_cat_id
      AND    (disable_date IS NULL OR disable_date > SYSDATE)
      ;
Line: 3858

      SELECT 1
      FROM   mtl_system_items_kfv items, mtl_item_categories_v cats
      WHERE  cats.category_id = p_cat_id
      AND    cats.inventory_item_id = p_prod_id
      AND    items.inventory_item_id = p_prod_id
      AND    UPPER(items.INVENTORY_ITEM_STATUS_CODE) <> 'INACTIVE'
      AND    items.organization_id = p_org_id
      AND    cats.organization_id = p_org_id
      AND    cats.category_set_id = p_cat_set_id
      ;
Line: 3917

            AMS_Utility_PVT.debug_message (L_API_NAME || ' :: Raising error...No categories/products selected.');
Line: 3925

   END validate_product_selections;