APPS.PAY_CA_CITY_UPD dependencies on PAY_US_CITY_NAMES

Line 35: pay_us_city_names. Also committing

31: with Oracle 8i
32: 17-JAN-03 ssouresr 115.2 truncated translated names to 30
33: characters
34: 07-MAR-03 ssouresr 115.3 Remove duplicate cities on
35: pay_us_city_names. Also committing
36: updates in batches.
37: */
38:
39: FUNCTION prov_abbrev (p_county_code in varchar2)

Line 92: pay_us_city_names pcn

88: pdc1.city_code,
89: pcn.primary_flag
90: FROM pay_ca_display_cities pdc1,
91: pay_ca_display_cities pdc2,
92: pay_us_city_names pcn
93: WHERE pdc1.city_code = pdc2.city_code
94: AND pdc1.county_code = pdc2.county_code
95: AND pdc1.display_city_name = pdc2.display_city_name
96: AND pdc1.city_name <> pdc2.city_name

Line 107: FROM pay_us_city_names pcn,

103: CURSOR translation_exists IS
104: SELECT pdc.city_name,
105: pdc.county_code,
106: pdc.city_code
107: FROM pay_us_city_names pcn,
108: pay_ca_display_cities pdc
109: WHERE pcn.state_code = '70'
110: AND pcn.county_code = pdc.county_code
111: AND pcn.city_code = pdc.city_code

Line 164: DELETE pay_us_city_names

160: FOR j IN v_dup_new_city.first..v_dup_new_city.last LOOP
161:
162: IF v_current_city = v_dup_new_city(j) THEN
163:
164: DELETE pay_us_city_names
165: WHERE city_code = v_dup_city_code(j)
166: AND county_code = v_dup_county_code(j)
167: AND city_name = v_dup_old_city(j)
168: AND state_code = '70'

Line 179: hr_utility.trace('Completed deletion of duplicate cities from pay_us_city_names ');

175: END LOOP;
176:
177: END IF;
178:
179: hr_utility.trace('Completed deletion of duplicate cities from pay_us_city_names ');
180:
181: OPEN translation_exists;
182: FETCH translation_exists BULK COLLECT INTO
183: v_exists_city,

Line 194: DELETE pay_us_city_names

190:
191: IF v_exists_city.COUNT > 0 THEN
192:
193: FORALL j IN v_exists_city.first..v_exists_city.last
194: DELETE pay_us_city_names
195: WHERE city_code = v_exists_city_code(j)
196: AND county_code = v_exists_county_code(j)
197: AND city_name = v_exists_city(j)
198: AND state_code = '70';

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

250: AND style = 'CA';
251:
252: COMMIT;
253:
254: hr_utility.trace('Starting bulk update on pay_us_city_names ');
255:
256: FORALL j IN v_old_city.first..v_old_city.last
257: UPDATE pay_us_city_names
258: SET city_name = substrb(v_new_city(j),1,30)

Line 257: UPDATE pay_us_city_names

253:
254: hr_utility.trace('Starting bulk update on pay_us_city_names ');
255:
256: FORALL j IN v_old_city.first..v_old_city.last
257: UPDATE pay_us_city_names
258: SET city_name = substrb(v_new_city(j),1,30)
259: WHERE county_code = v_county_code(j)
260: AND city_name = v_old_city(j)
261: AND city_code = v_city_code(j)

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

266: END IF;
267:
268: END LOOP;
269:
270: hr_utility.trace('Completed update on per_addresses,hr_locations_all and pay_us_city_names');
271:
272: CLOSE city_mappings;
273:
274: EXCEPTION