APPS.PAY_CA_CITY_UPD SQL Statements

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

Line: 22

    Description : Package that is used to update Canadian city names
                  to their correct French Canadian spelling.

    Change List
    -----------
     Date        Name      Vers    Bug No    Description
     ----        ----      ------  -------   -----------
     23-DEC-02   ssouresr  115.0   2428688   Created.
     15-JAN-03   ssouresr  115.1             Modified to make compatible
                                             with Oracle 8i
     17-JAN-03   ssouresr  115.2             truncated translated names to 30
                                             characters
     07-MAR-03   ssouresr  115.3             Remove duplicate cities on
                                             pay_us_city_names. Also committing
                                             updates in batches.
*/

FUNCTION prov_abbrev (p_county_code  in varchar2)
RETURN varchar2 IS
  v_prov_abbrev  varchar2(5);

Line: 44

    SELECT county_abbrev
    INTO  v_prov_abbrev
    FROM  pay_us_counties
    WHERE county_code = p_county_code
    AND   state_code = '70';

Line: 77

  SELECT city_name,
         county_code,
         city_code,
         display_city_name
  FROM pay_ca_display_cities;

Line: 84

  SELECT DISTINCT
          pdc1.display_city_name,
          pdc1.city_name,
          pdc1.county_code,
          pdc1.city_code,
          pcn.primary_flag
  FROM  pay_ca_display_cities pdc1,
        pay_ca_display_cities pdc2,
        pay_us_city_names     pcn
  WHERE pdc1.city_code         = pdc2.city_code
  AND   pdc1.county_code       = pdc2.county_code
  AND   pdc1.display_city_name = pdc2.display_city_name
  AND   pdc1.city_name        <> pdc2.city_name
  AND   pdc1.city_code    = pcn.city_code
  AND   pdc1.county_code  = pcn.county_code
  AND   pdc1.city_name    = pcn.city_name
  AND   pcn.state_code    = '70'
  ORDER BY pdc1.display_city_name, pcn.primary_flag DESC;

Line: 104

  SELECT  pdc.city_name,
          pdc.county_code,
          pdc.city_code
  FROM  pay_us_city_names     pcn,
        pay_ca_display_cities pdc
  WHERE pcn.state_code  = '70'
  AND   pcn.county_code = pdc.county_code
  AND   pcn.city_code   = pdc.city_code
  AND   pcn.city_name   = pdc.display_city_name;

Line: 164

              DELETE pay_us_city_names
              WHERE  city_code    = v_dup_city_code(j)
              AND    county_code  = v_dup_county_code(j)
              AND    city_name    = v_dup_old_city(j)
              AND    state_code   = '70'
              AND    primary_flag = 'N';

Line: 194

         DELETE pay_us_city_names
         WHERE  city_code    = v_exists_city_code(j)
         AND    county_code  = v_exists_county_code(j)
         AND    city_name    = v_exists_city(j)
         AND    state_code   = '70';

Line: 228

               hr_utility.trace('Starting bulk update on per_addresses ');

Line: 231

               UPDATE per_addresses
               SET town_or_city   = substrb(v_new_city(j),1,30),
                   derived_locale = decode(derived_locale, NULL, NULL,
                                           get_derived_locale(v_new_city(j),country))
               WHERE region_1     = v_prov(j)
               AND   town_or_city = v_old_city(j)
               AND   style        = 'CA';

Line: 241

               hr_utility.trace('Starting bulk update on hr_locations_all ');

Line: 244

               UPDATE hr_locations_all
               SET town_or_city   = substrb(v_new_city(j),1,30),
                   derived_locale = decode(derived_locale, NULL, NULL,
                                           get_derived_locale(v_new_city(j),country))
               WHERE region_1     = v_prov(j)
               AND   town_or_city = v_old_city(j)
               AND   style        = 'CA';

Line: 254

               hr_utility.trace('Starting bulk update on pay_us_city_names ');

Line: 257

               UPDATE pay_us_city_names
               SET  city_name     = substrb(v_new_city(j),1,30)
               WHERE county_code  = v_county_code(j)
               AND   city_name    = v_old_city(j)
               AND   city_code    = v_city_code(j)
               AND   state_code   = '70';

Line: 270

     hr_utility.trace('Completed update on per_addresses,hr_locations_all and pay_us_city_names');

Line: 278

          hr_utility.trace('Error during update process: ' || v_errortext || ' ' || v_errorcode);