DBA Data[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