[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_CITY_UPD
Source
1 PACKAGE BODY pay_ca_city_upd AS
2 /* $Header: pycactup.pkb 115.3 2003/03/12 19:42:09 ssouresr noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1999 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_ca_city_upd
21
22 Description : Package that is used to update Canadian city names
23 to their correct French Canadian spelling.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ---- ---- ------ ------- -----------
29 23-DEC-02 ssouresr 115.0 2428688 Created.
30 15-JAN-03 ssouresr 115.1 Modified to make compatible
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)
40 RETURN varchar2 IS
41 v_prov_abbrev varchar2(5);
42 BEGIN
43
44 SELECT county_abbrev
45 INTO v_prov_abbrev
46 FROM pay_us_counties
47 WHERE county_code = p_county_code
48 AND state_code = '70';
49
50 RETURN v_prov_abbrev;
51
52 END;
53
54 FUNCTION get_derived_locale (p_town_or_city in varchar2,
55 p_country in varchar2)
56 RETURN varchar2 IS
57 v_derived_locale varchar2(240);
58 v_separator varchar2(10);
59 BEGIN
60 IF (ltrim(p_town_or_city) IS NULL) OR
61 (ltrim(p_country) IS NULL) THEN
62 v_separator := '';
63 ELSE
64 v_separator := ', ';
65 END IF;
66
67 v_derived_locale := ltrim(p_town_or_city) || v_separator || ltrim(p_country);
68
69 RETURN v_derived_locale;
70 END;
71
72
73 PROCEDURE cityname_bulk_upd (errbuf out nocopy varchar2,
74 retcode out nocopy number)
75 IS
76 CURSOR city_mappings IS
77 SELECT city_name,
78 county_code,
79 city_code,
80 display_city_name
81 FROM pay_ca_display_cities;
82
83 CURSOR duplicate_cities IS
84 SELECT DISTINCT
85 pdc1.display_city_name,
86 pdc1.city_name,
87 pdc1.county_code,
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
97 AND pdc1.city_code = pcn.city_code
98 AND pdc1.county_code = pcn.county_code
99 AND pdc1.city_name = pcn.city_name
100 AND pcn.state_code = '70'
101 ORDER BY pdc1.display_city_name, pcn.primary_flag DESC;
102
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
112 AND pcn.city_name = pdc.display_city_name;
113
114
115 v_prov prov_list;
116 v_county_code county_code_list;
117 v_city_code city_code_list;
118 v_old_city old_city_name_list;
119 v_new_city new_city_name_list;
120
121 v_dup_old_city old_city_name_list;
122 v_dup_new_city new_city_name_list;
123 v_dup_city_code city_code_list;
124 v_dup_county_code county_code_list;
125 v_dup_primary_flag primary_flag_list;
126
127 v_current_city varchar2(90);
128
129 v_exists_city old_city_name_list;
130 v_exists_city_code city_code_list;
131 v_exists_county_code county_code_list;
132
133 v_old_row_count number := 0;
134 v_new_row_count number := 0;
135 v_rows_in_collect number := 0;
136 v_commit_limit natural := 200;
137
138 v_errortext varchar2(512);
139 v_errorcode number;
140
141 BEGIN
142 -- hr_utility.trace_on(1,'Oracle');
143
144 hr_utility.trace('Starting cityname_bulk_upd ');
145
146 OPEN duplicate_cities;
147 FETCH duplicate_cities BULK COLLECT INTO
148 v_dup_new_city,
149 v_dup_old_city,
150 v_dup_county_code,
151 v_dup_city_code,
152 v_dup_primary_flag;
153
154 CLOSE duplicate_cities;
155
156 hr_utility.trace('Loaded all duplicate city name mappings from pay_ca_display_cities');
157
158 IF v_dup_new_city.COUNT > 0 THEN
159
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'
169 AND primary_flag = 'N';
170
171 ELSE
172 v_current_city := v_dup_new_city(j);
173 END IF;
174
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,
184 v_exists_county_code,
185 v_exists_city_code;
186
187 CLOSE translation_exists;
188
189 hr_utility.trace('Loaded all existing city name translations');
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';
199
200 END IF;
201
202 hr_utility.trace('Completed bulk deletion of cities which have existing translations ');
203
204 OPEN city_mappings;
205 LOOP
206 FETCH city_mappings BULK COLLECT INTO
207 v_old_city,
208 v_county_code,
209 v_city_code,
210 v_new_city
211 LIMIT v_commit_limit;
212
213 v_old_row_count := v_new_row_count;
214 v_new_row_count := city_mappings%ROWCOUNT;
215
216 v_rows_in_collect := v_new_row_count - v_old_row_count;
217
218 EXIT WHEN (v_rows_in_collect = 0);
219
220 hr_utility.trace('Loaded batch of city name mappings from pay_ca_display_cities');
221
222 IF v_old_city.COUNT > 0 THEN
223
224 FOR j IN v_old_city.first..v_old_city.last LOOP
225 v_prov(j) := prov_abbrev(v_county_code(j));
226 END LOOP;
227
228 hr_utility.trace('Starting bulk update on per_addresses ');
229
230 FORALL j IN v_old_city.first..v_old_city.last
231 UPDATE per_addresses
232 SET town_or_city = substrb(v_new_city(j),1,30),
233 derived_locale = decode(derived_locale, NULL, NULL,
234 get_derived_locale(v_new_city(j),country))
235 WHERE region_1 = v_prov(j)
236 AND town_or_city = v_old_city(j)
237 AND style = 'CA';
238
239 COMMIT;
240
241 hr_utility.trace('Starting bulk update on hr_locations_all ');
242
243 FORALL j IN v_old_city.first..v_old_city.last
244 UPDATE hr_locations_all
245 SET town_or_city = substrb(v_new_city(j),1,30),
246 derived_locale = decode(derived_locale, NULL, NULL,
247 get_derived_locale(v_new_city(j),country))
248 WHERE region_1 = v_prov(j)
249 AND town_or_city = v_old_city(j)
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)
259 WHERE county_code = v_county_code(j)
260 AND city_name = v_old_city(j)
261 AND city_code = v_city_code(j)
262 AND state_code = '70';
263
264 COMMIT;
265
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
275 WHEN OTHERS THEN
276 v_errorcode := SQLCODE;
277 v_errortext := SQLERRM;
278 hr_utility.trace('Error during update process: ' || v_errortext || ' ' || v_errorcode);
279 errbuf := v_errortext;
280 retcode := v_errorcode;
281 ROLLBACK;
282
283 END; -- end of cityname_bulk_upd
284
285 END pay_ca_city_upd; -- end of package