APPS.HR_US_PYZIPCHK SQL Statements

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

Line: 31

  select count(*)
  from   pay_us_counties co,
         pay_ca_cities_v ct,
         pay_us_zip_codes pc
  where  co.state_code = '70'
  and    co.county_code = ct.province_code
  and    co.county_code = pc.county_code
  and    pc.state_code = '70'
  and    ct.city_code  = pc.city_code
  and    ct.city_name  = p_city_name
  and    co.county_abbrev = p_county_name
  and substr(p_zip_code,1,3) between substr(pc.zip_start,1,3) and substr(pc.zip_end,1,3)
  and substr(p_zip_code,5,3) between '0A0' and '9Z9';

Line: 46

  select count(*)
  from   pay_ca_provinces_v pr
  ,      pay_ca_cities_v ct
  ,      pay_ca_postal_codes_v pc
  where  pr.province_code = ct.province_code
  and    pr.province_code = pc.province_code
  and    ct.city_code = pc.city_code
  and    ct.city_name = p_city_name
  and    pr.province_abbrev = p_county_name
  and    substr(p_zip_code,1,3) between substr(pc.code_start,1,3)
                                    and substr(pc.code_end,1,3)
  and    substr(p_zip_code,5,3) between '0A0' and '9Z9';

Line: 75

            insert into per_us_inval_addresses (address_id)
            values (p_address_id);

Line: 86

             insert into per_us_inval_addresses (address_id)
             values (p_address_id);

Line: 118

  select count(*)
  from   pay_us_counties co,
         pay_ca_cities_v ct,
         pay_us_zip_codes pc
  where  co.state_code = '70'
  and    co.county_code = ct.province_code
  and    co.county_code = pc.county_code
  and    pc.state_code = '70'
  and    ct.city_code  = pc.city_code
  and    ct.city_name  = p_city_name
  and    co.county_abbrev = p_county_name
  and substr(p_zip_code,1,3) between substr(pc.zip_start,1,3) and substr(pc.zip_end,1,3)
  and substr(p_zip_code,5,3) between '0A0' and '9Z9';

Line: 133

  select count(*)
  from   pay_ca_provinces_v pr
  ,      pay_ca_cities_v ct
  ,      pay_ca_postal_codes_v pc
  where  pr.province_code = ct.province_code
  and    pr.province_code = pc.province_code
  and    ct.city_code = pc.city_code
  and    ct.city_name = p_city_name
  and    pr.province_abbrev = p_county_name
  and    substr(p_zip_code,1,3) between substr(pc.code_start,1,3)
                                    and substr(pc.code_end,1,3)
  and    substr(p_zip_code,5,3) between '0A0' and '9Z9';

Line: 156

            insert into per_us_inval_locations (location_id)
            values (p_location_id);

Line: 167

             insert into per_us_inval_locations (location_id)
             values (p_location_id);

Line: 179

 select per.address_id,
        per.region_1,
        per.region_2,
        per.town_or_city,
        per.postal_code
 from   per_addresses per
 where  per.style in ('US','CA') or per.country in ('US','CA') ;--Added for Bug#8982883

Line: 189

 select hr.location_id,
        hr.region_1,
        hr.region_2,
        hr.town_or_city,
        hr.postal_code
 from   hr_locations_all hr
 where  hr.style in ('US','CA') or hr.country in ('US','CA') ;--Added for Bug#8982883