APPS.PV_CATEGORY_MIGRATION SQL Statements

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

Line: 71

   l_update_ddl           VARCHAR2(4000);

Line: 74

   l_rows_inserted        NUMBER;

Line: 76

   l_revert_selected_attr BOOLEAN := FALSE;

Line: 80

      SELECT DISTINCT
             b.code old_value, b.product_category_id
      FROM   pv_enty_attr_values a,
      (
      select interest_type code,
             TO_CHAR(interest_type_id) id,
             product_category_id
      from   as_interest_types_vl
      union
      select i.interest_type ||'/'||p.code code,
             i.interest_type_id ||'/'||p.interest_code_id id,
             p.product_category_id
      from   as_interest_types_vl i, as_interest_codes_vl p
      where  i.interest_type_id*1 = p.interest_type_id and
             p.parent_interest_code_id is null
      union
      select i.interest_type ||'/'||p.code ||'/'||s.code code,
             i.interest_type_id ||'/'||p.interest_code_id ||'/'||s.interest_code_id id,
             s.product_category_id
      from   as_interest_types_vl i, as_interest_codes_vl p,
             as_interest_codes_vl s
      where  i.interest_type_id = p.interest_type_id and
             p.interest_type_id = s.interest_type_id*1 and
             s.parent_interest_code_id = p.interest_code_id
      ) b
      WHERE  a.attr_value = b.id AND
             a.attribute_id IN (1, 510) AND
             b.product_category_id IS NULL;

Line: 110

   CURSOR c_selected_attr_values IS
      SELECT  DISTINCT
              a.attribute_value old_value
      FROM    pv_selected_attr_values a,
              pv_enty_select_criteria c,
      (
      select interest_type code,
             TO_CHAR(interest_type_id) id,
             product_category_id
      from   as_interest_types_vl
      union
      select i.interest_type ||'/'||p.code code,
             i.interest_type_id ||'/'||p.interest_code_id id,
             p.product_category_id
      from   as_interest_types_vl i, as_interest_codes_vl p
      where  i.interest_type_id*1 = p.interest_type_id and
             p.parent_interest_code_id is null
      union
      select i.interest_type ||'/'||p.code ||'/'||s.code code,
             i.interest_type_id ||'/'||p.interest_code_id ||'/'||s.interest_code_id id,
             s.product_category_id
      from   as_interest_types_vl i, as_interest_codes_vl p,
             as_interest_codes_vl s
      where  i.interest_type_id = p.interest_type_id and
             p.interest_type_id = s.interest_type_id*1 and
             s.parent_interest_code_id = p.interest_code_id
      ) b
      WHERE  b.id = a.attribute_value AND
             a.selectioN_criteria_id    = c.selection_criteria_id AND
             c.attribute_id IN (1, 510) AND
             b.product_category_id IS NULL;

Line: 143

   l_insert_into_table1 VARCHAR2(32000) :=
     'INSERT INTO pv_single_prod_h_mappings
      SELECT a.enty_attr_val_id,
             b.product_category_id new_value,
             b.code old_value
      FROM   pv_enty_attr_values a,
      (
            select to_char(interest_type_id) code, product_category_id
            from   as_interest_types_b
            union
            select to_char(i.interest_type_id)||''/''||p.interest_code_id code,
                   p.product_category_id
            from   as_interest_types_b i, as_interest_codes_b p
            where  i.interest_type_id*1 = p.interest_type_id and
                   p.parent_interest_code_id is null
      union
      select to_char(i.interest_type_id)||''/''||p.interest_code_id||''/''||
             s.interest_code_id code,
             s.product_category_id
      from   as_interest_types_b i, as_interest_codes_b p, as_interest_codes_b s
      where  i.interest_type_id = p.interest_type_id and
             p.interest_type_id = s.interest_type_id*1 and
             s.parent_interest_code_id = p.interest_code_id
      ) b
      WHERE  a.attr_value = b.code AND
             a.attribute_id IN (1, 510)';

Line: 170

   l_insert_into_table2 VARCHAR2(32000) :=
     'INSERT INTO pv_single_prod_h_mappings2
      SELECT  a.attr_value_id,
              b.product_category_id new_value,
              a.attribute_value old_value
      FROM    pv_selected_attr_values a,
              pv_enty_select_criteria c,
      (
      select to_char(interest_type_id) code, product_category_id
      from   as_interest_types_b
      union
      select to_char(i.interest_type_id)||''/''||p.interest_code_id code,
             p.product_category_id
      from   as_interest_types_b i, as_interest_codes_b p
      where  i.interest_type_id*1 = p.interest_type_id and
             p.parent_interest_code_id is null
      union
      select to_char(i.interest_type_id)||''/''||p.interest_code_id||''/''||
             s.interest_code_id code,
             s.product_category_id
      from   as_interest_types_b i, as_interest_codes_b p, as_interest_codes_b s
      where  i.interest_type_id = p.interest_type_id and
             p.interest_type_id = s.interest_type_id*1 and
            s.parent_interest_code_id = p.interest_code_id
      ) b
      WHERE  b.code = a.attribute_value AND
             a.selectioN_criteria_id    = c.selection_criteria_id AND
             c.attribute_id IN (1, 510)';

Line: 203

      SELECT i.tablespace,
             i.index_tablespace,
             u.oracle_username
      FROM   fnd_product_installations i,
             fnd_application a,
             fnd_oracle_userid u
      WHERE  a.application_short_name = 'PV' AND
             a.application_id = i.application_id AND
             u.oracle_id = i.oracle_id;

Line: 245

   FOR x IN (SELECT 'x' row_exists FROM pv_single_prod_h_mappings) LOOP
      l_revert_entity_attr := TRUE;

Line: 249

   FOR x IN (SELECT 'x' row_exists FROM pv_single_prod_h_mappings2) LOOP
      l_revert_selected_attr := TRUE;

Line: 257

     l_update_ddl :=
     'UPDATE pv_enty_attr_values a
      SET    attr_value = (SELECT old_value
                           FROM   pv_single_prod_h_mappings b
                           WHERE  a.enty_attr_val_id = b.enty_attr_val_id)
      WHERE  EXISTS       (SELECT 1
                           FROM   pv_single_prod_h_mappings c
                           WHERE  a.enty_attr_val_id = c.enty_attr_val_id)';

Line: 266

      l_string := SUBSTR(l_update_ddl, 1, 300);

Line: 268

      EXECUTE IMMEDIATE l_update_ddl;

Line: 271

         p_msg_name      => 'PV_SPH_ROWS_UPDATED',
         p_token1        => 'ROWS',
         p_token1_value  => SQL%ROWCOUNT
      );

Line: 282

   IF (l_revert_selected_attr) THEN
     l_update_ddl :=
     'UPDATE pv_selected_attr_values a
      SET    attribute_value =
                          (SELECT old_value
                           FROM   pv_single_prod_h_mappings2 b
                           WHERE  a.attr_value_id = b.attr_value_id)
      WHERE  EXISTS       (SELECT 1
                           FROM   pv_single_prod_h_mappings2 c
                           WHERE  a.attr_value_id = c.attr_value_id)';

Line: 293

      l_string := SUBSTR(l_update_ddl, 1, 300);

Line: 295

      EXECUTE IMMEDIATE l_update_ddl;

Line: 298

         p_msg_name      => 'PV_SPH_ROWS_UPDATED',
         p_token1        => 'ROWS',
         p_token1_value  => SQL%ROWCOUNT
      );

Line: 354

      p_token1_value  => 'pv_selected_attr_values'
   );

Line: 361

   FOR x IN c_selected_attr_values LOOP
      Debug(LPAD(x.old_value, 60));

Line: 431

   l_string := SUBSTR(l_insert_into_table1, 1, 300);

Line: 432

   EXECUTE IMMEDIATE l_insert_into_table1;

Line: 434

   l_rows_inserted := SQL%ROWCOUNT;

Line: 437

      p_msg_name      => 'PV_SPH_ROWS_INSERTED',
      p_token1        => 'ROWS',
      p_token1_value  => l_rows_inserted
   );

Line: 461

      p_token2_value  => 'pv_selected_attr_values'
   );

Line: 480

   l_string := SUBSTR(l_insert_into_table2, 1, 300);

Line: 481

   EXECUTE IMMEDIATE l_insert_into_table2;

Line: 483

   l_rows_inserted := SQL%ROWCOUNT;

Line: 485

      p_msg_name      => 'PV_SPH_ROWS_INSERTED',
      p_token1        => 'ROWS',
      p_token1_value  => l_rows_inserted
   );

Line: 509

      p_msg_name      => 'PV_SPH_UPDATE_TABLE',
      p_token1        => 'TABLE',
      p_token1_value  => 'pv_enty_attr_values'
   );

Line: 515

   l_update_ddl :=
     'UPDATE pv_enty_attr_values a
      SET    attr_value = (SELECT new_value
                           FROM   pv_single_prod_h_mappings b
                           WHERE  a.enty_attr_val_id = b.enty_attr_val_id)
      WHERE  EXISTS       (SELECT 1
                           FROM   pv_single_prod_h_mappings c
                           WHERE  a.enty_attr_val_id = c.enty_attr_val_id)';

Line: 524

   l_string := SUBSTR(l_update_ddl, 1, 300);

Line: 526

   EXECUTE IMMEDIATE l_update_ddl;

Line: 529

      p_msg_name      => 'PV_SPH_ROWS_UPDATED',
      p_token1        => 'ROWS',
      p_token1_value  => SQL%ROWCOUNT
   );

Line: 542

      p_msg_name      => 'PV_SPH_UPDATE_TABLE',
      p_token1        => 'TABLE',
      p_token1_value  => 'pv_selected_attr_values'
   );

Line: 548

   l_update_ddl :=
     'UPDATE pv_selected_attr_values a
      SET    attribute_value =
                          (SELECT new_value
                           FROM   pv_single_prod_h_mappings2 b
                           WHERE  a.attr_value_id = b.attr_value_id)
      WHERE  EXISTS       (SELECT 1
                           FROM   pv_single_prod_h_mappings2 c
                           WHERE  a.attr_value_id = c.attr_value_id)';

Line: 558

   l_string := SUBSTR(l_update_ddl, 1, 300);

Line: 560

   EXECUTE IMMEDIATE l_update_ddl;

Line: 563

      p_msg_name      => 'PV_SPH_ROWS_UPDATED',
      p_token1        => 'ROWS',
      p_token1_value  => SQL%ROWCOUNT
   );

Line: 585

   Debug('SELECT old_value, new_value');

Line: 593

      p_token1_value  => 'pv_selected_attr_values',
      p_token2        => 'TABLE2',
      p_token2_value  => 'pv_single_prod_h_mappings2'
   );

Line: 599

   Debug('SELECT old_value, new_value');