APPS.JTF_TTY_PROD_CAT_MIGRATE_PVT SQL Statements

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

Line: 8

   and Lead Expected Purchase in the JTF_TERR_VALUES_ALL table. It also updates
   the JTF_TERR_QUAL_ALL table with new qual_usg_ids */

PROCEDURE Migrate_All ( ERRBUF         OUT NOCOPY    VARCHAR2,
                        RETCODE        OUT NOCOPY    VARCHAR2,
                        p_Debug_Flag   IN  VARCHAR2  default 'N') IS
BEGIN

   FND_FILE.PUT_LINE(FND_FILE.LOG,'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));

Line: 51

   SELECT a.terr_value_id
   FROM   jtf_terr_values_all a, jtf_terr_qual_all b
   WHERE  a.terr_qual_id =  b.terr_qual_id
   AND    b.qual_usg_id IN (l_qual_usg_id, l_qual_usg_id_new)
   AND    a.interest_type_id is not null
   AND    a.primary_interest_code_id is null
   AND    a.secondary_interest_code_id is null;

Line: 61

   SELECT a.terr_value_id
   FROM   jtf_terr_values_all a, jtf_terr_qual_all b
   WHERE  a.terr_qual_id =  b.terr_qual_id
   AND    b.qual_usg_id IN (l_qual_usg_id, l_qual_usg_id_new)
   AND    a.primary_interest_code_id is not null
   AND    a.secondary_interest_code_id is null;

Line: 70

   SELECT a.terr_value_id
   FROM   jtf_terr_values_all a, jtf_terr_qual_all b
   WHERE  a.terr_qual_id =  b.terr_qual_id
   AND    b.qual_usg_id IN (l_qual_usg_id, l_qual_usg_id_new)
   AND    a.secondary_interest_code_id is not null;

Line: 91

         UPDATE jtf_terr_values_all jtv
         SET    (value1_id, value2_id) =
                                         (SELECT int.product_category_id, int.product_cat_set_id
                                          FROM   as_interest_types_b int
                                          WHERE  jtv.interest_type_id = int.interest_type_id)
         WHERE jtv.terr_value_id  = l_terr_val_ids_arr(j);

Line: 121

         UPDATE jtf_terr_values_all jtv
         SET    (value1_id, value2_id) =
                                         (SELECT int.product_category_id, int.product_cat_set_id
                                          FROM   as_interest_codes_b int
                                          WHERE  jtv.primary_interest_code_id = int.interest_code_id)
         WHERE jtv.terr_value_id  = l_terr_val_ids_arr(j);

Line: 151

         UPDATE jtf_terr_values_all jtv
         SET    (value1_id, value2_id) =
                                         (SELECT int.product_category_id, int.product_cat_set_id
                                          FROM   as_interest_codes_b int
                                          WHERE  jtv.secondary_interest_code_id = int.interest_code_id)
         WHERE jtv.terr_value_id  = l_terr_val_ids_arr(j);

Line: 174

      UPDATE jtf_terr_qual_all qual
      SET    qual_usg_id = -1142
      WHERE  qual_usg_id = -1023;

Line: 178

         FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated Qualifier Opportunity Expected Purchase to Opportunity Product Category');

Line: 184

      UPDATE jtf_qual_usgs_all
      SET    enabled_flag = 'N'
      WHERE  qual_usg_id = -1023;

Line: 189

	  delete from jtf_seeded_qual_all_b
      where seeded_qual_id = -1024;

Line: 192

      delete from jtf_seeded_qual_all_tl
      where seeded_qual_id = -1024;

Line: 195

	  DELETE FROM jtf_qual_usgs_all
      WHERE  qual_usg_id = -1023;

Line: 203

      UPDATE jtf_qual_usgs_all
      SET    enabled_flag = 'Y'
      WHERE  qual_usg_id = -1142;

Line: 211

      UPDATE jtf_terr_qual_all qual
      SET    qual_usg_id = -1131
      WHERE  qual_usg_id = -1018;

Line: 215

         FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated Qualifier Lead Expected Purchase to Lead Product Category');

Line: 221

	  UPDATE jtf_qual_usgs_all
      SET    enabled_flag = 'N'
      WHERE  qual_usg_id = -1018;

Line: 226

	  delete from jtf_seeded_qual_all_b
      where seeded_qual_id = -1019;

Line: 229

      delete from jtf_seeded_qual_all_tl
      where seeded_qual_id = -1019;

Line: 232

	  DELETE FROM jtf_qual_usgs_all
      WHERE  qual_usg_id = -1018;

Line: 239

      UPDATE jtf_qual_usgs_all
      SET    enabled_flag = 'Y'
      WHERE  qual_usg_id = -1131;

Line: 265

   UPDATE jtf_tty_role_prod_int jtr
   SET    (product_category_id, product_category_set_id) =
                                                            (SELECT int.product_category_id, int.product_cat_set_id
                                                             FROM   as_interest_types_b int
                                                             WHERE  jtr.interest_type_id = int.interest_type_id);