1 PACKAGE BODY PER_FNAME_PKG AS
2 /* $Header: pepefnam.pkb 115.1 2004/03/23 01:00:11 bsubrama noship $ */
3
4 procedure REBUILD_FULLNAME ( errbuf out NOCOPY varchar2,
5 retcode out NOCOPY NUMBER,
6 p_legislation_code varchar2)
7 IS
8
9
10 --
11 -- Set up the PL/SQL tables used to hold values returned from Bulk Collect
12 --
13 type person_id_list is table of per_all_people_f.person_id%type;
14 TYPE effective_start_date_list IS TABLE OF per_all_people_f.effective_start_date%TYPE;
15 TYPE effective_end_date_list IS TABLE OF per_all_people_f.effective_end_Date%TYPE;
16 -- Fix 3386992 Start
17 type first_name_list is table of per_all_people_f.first_name%type;
18 type middle_names_list is table of per_all_people_f.middle_names%type;
19 type last_name_list is table of per_all_people_f.last_name%type;
20 type known_as_list is table of per_all_people_f.known_as%type;
21 type title_list is table of per_all_people_f.title%type;
22 type suffix_list is table of per_all_people_f.suffix%type;
23 type date_of_birth_list is table of per_all_people_f.date_of_birth%type;
24 type business_group_id_list is table of per_all_people_f.business_group_id%type;
25 type full_name_list is table of per_all_people_f.full_name%type;
26 -- Fix 3386992 End
27
28 --
29 -- Declare the variables based on this new type
30 --
31 l_person_id_list person_id_list;
32 l_effective_start_date_list effective_start_date_list;
33 l_effective_end_date_list effective_end_date_list;
34 -- Fix 3386992 Start
35 l_first_name_list first_name_list;
36 l_middle_names_list middle_names_list;
37 l_last_name_list last_name_list;
38 l_known_as_list known_as_list;
39 l_title_list title_list;
40 l_suffix_list suffix_list;
41 l_date_of_birth_list date_of_birth_list;
42 l_business_group_id_list business_group_id_list;
43 l_full_name_list full_name_list;
44 l_dflag varchar2(100);
45 -- Fix 3386992 End
46
47 -- Cursor to check for installed legislation
48 CURSOR instl_prod (p_legislation_code varchar2) IS
49 SELECT 'x'
50 FROM hr_legislation_installations
51 WHERE application_short_name = 'PER'
52 AND (status='I' OR action IS NOT NULL)
53 AND legislation_code = p_legislation_code;
54
55
56 -- cursor to check package body is delivered by Localization Team
57 CURSOR lgsl_pkb(p_legislation_code varchar2) IS
58 SELECT 'x'
59 FROM user_objects
60 WHERE object_type='PACKAGE BODY'
61 AND object_name LIKE 'HR_'||p_legislation_code||'_UTILITY'
62 AND length(object_name)=13
63 ORDER BY object_name;
64
65 -- Cursor to select all persons belonging to legislation code.
66 cursor csr_get_people_in_leg (p_legislation_code varchar2) is
67 select person_id,
68 effective_start_date,
69 effective_end_date
70 from per_all_people_f per, per_business_groups bg
71 where per.business_group_id = bg.business_group_id
72 and bg.legislation_code = p_legislation_code;
73
74 -- Fix 3386992 Start
75 -- This trigger is similar to the one being used already csr_get_people_in_leg
76 -- but fetches additional fields from the table
77 cursor csr_get_people_details_in_leg (p_legislation_code varchar2) is
78 select person_id,
79 effective_start_date,
80 effective_end_date,
81 first_name,
82 middle_names,
83 last_name,
84 known_as,
85 title,
86 suffix,
87 date_of_birth,
88 per.business_group_id,
89 full_name
90 from per_all_people_f per, per_business_groups bg
91 where per.business_group_id = bg.business_group_id
92 and bg.legislation_code = p_legislation_code;
93 -- Fix 3386992 End
94
95 -- Declare local variables
96 l_dummy varchar2(1);
97 l_old_row_count number := 0; -- Fix 3386992 Initialize to zero
98 l_new_row_count number := 0; -- Fix 3386992 Initialize to zero
99 l_rows_in_this_collect number;
100
101 -- Fix 3386992
102 -- Variable holds the state of installation of the package hr_<leg_code>_utility
103 l_leg_util_installed boolean := true;
104
105 -- p_legislation_code varchar2(2):= 'US'; -- Should be reomved while creating Proc
106 --
107 -- This block defines the number of rows to update before committing
108 --
109 l_commit_block NATURAL := 100;
110
111
112
113 BEGIN
114
115 retcode := 0;
116 errbuf := NULL;
117
118 if p_legislation_code is null then
119 -- In the rare case that this script is called incorrectly
120 -- without being passed a legislation code, raise an error
121 --
122 fnd_message.set_name('PER', 'PER_52123_AMD_LEG_CODE_INV');
123 hr_utility.raise_error;
124 end if;
125
126 OPEN instl_prod(p_legislation_code);
127 FETCH instl_prod INTO l_dummy;
128 IF instl_prod%notfound THEN
129 CLOSE instl_prod;
130 retcode := 1;
131 errbuf := 'Legislation Not Installed';
132 fnd_message.set_name('PER', 'PER_52123_AMD_LEG_CODE_INV');
133 hr_utility.raise_error;
134 return;
135 END IF;
136
137 CLOSE instl_prod;
138
139 -- Fix 3386992
140 -- Initialize the value of the variable l_leg_util_installed
141 -- based on whether the package is installed or not.
142 OPEN lgsl_pkb(p_legislation_code);
143 FETCH lgsl_pkb INTO l_dummy;
144 IF lgsl_pkb%notfound THEN
145 CLOSE lgsl_pkb;
146 l_leg_util_installed := false;
147 else
148 CLOSE lgsl_pkb;
149 l_leg_util_installed := true;
150 END IF;
151
152
153 OPEN csr_get_people_in_leg(p_legislation_code);
154 OPEN csr_get_people_details_in_leg(p_legislation_code);
155
156 LOOP -- Wish to loop round and commit every l_commit_block rows,
157 -- processing l_commit_block (=100) records at a time.
158
159 -- Fix 3386992
160 -- if the package hr_<LEG_CODE>_utility is installed then
161 -- use trigger code to generate full_name
162 -- else use hr_person.derive_full_name
163 if l_leg_util_installed then
164 FETCH csr_get_people_in_leg BULK COLLECT INTO
165 l_person_id_list,
166 l_effective_start_date_list,
167 l_effective_end_date_list
168 LIMIT l_commit_block;
169
170 -- We need to keep a count of how many rows we are bringing back in
171 -- each iteration of the loop. Row Count is cumulative.
172 -- There appears to be a bug in the way BULK COLLECT and LIMIT works
173 -- in that if the COLLECT retrieves less rows that the LIMIT number
174 -- it raises a CSR%NOTFOUND exception. Therefore we cannot exit on
175 -- this exception as the last rows will not get processed.
176 --
177 -- To get around this, we keep track of how many rows will be
178 -- processed on this iteration and exit when this drops to zero.
179
180 l_old_row_count := l_new_row_count;
181 l_new_row_count := csr_get_people_in_leg%ROWCOUNT;
182
183 l_rows_in_this_collect := l_new_row_count - l_old_row_count;
184
185 --
186 -- Break out of the loop when the BULK COLLECT has got all rows
187 --
188 EXIT WHEN (l_rows_in_this_collect = 0);
189
190 FORALL j IN l_person_id_list.FIRST..l_person_id_list.LAST
191
192 -- Touching the rows by update statement so that the actual
193 -- Full name and Order name is derived by logic in
194 -- trigger PER_ALL_PEOPLE_F_NAME
195 --
196
197 UPDATE per_all_people_f
198 SET last_name = last_name
199 WHERE person_id = l_person_id_list(j)
200 and effective_start_date = l_effective_start_date_list(j)
201 and effective_end_date = l_effective_end_date_list(j);
202 COMMIT;
203
204 else
205
206 FETCH csr_get_people_details_in_leg BULK COLLECT INTO
207 l_person_id_list,
208 l_effective_start_date_list,
209 l_effective_end_date_list,
210 l_first_name_list,
211 l_middle_names_list,
212 l_last_name_list,
213 l_known_as_list,
214 l_title_list,
215 l_suffix_list,
216 l_date_of_birth_list,
217 l_business_group_id_list,
218 l_full_name_list
219 LIMIT l_commit_block;
220
221 -- We need to keep a count of how many rows we are bringing back in
222 -- each iteration of the loop. Row Count is cumulative.
223 -- There appears to be a bug in the way BULK COLLECT and LIMIT works
224 -- in that if the COLLECT retrieves less rows that the LIMIT number
225 -- it raises a CSR%NOTFOUND exception. Therefore we cannot exit on
226 -- this exception as the last rows will not get processed.
227 --
228 -- To get around this, we keep track of how many rows will be
229 -- processed on this iteration and exit when this drops to zero.
230
231 l_old_row_count := l_new_row_count;
232 l_new_row_count := csr_get_people_details_in_leg%ROWCOUNT;
233
234 l_rows_in_this_collect := l_new_row_count - l_old_row_count;
235
236 --
237 -- Break out of the loop when the BULK COLLECT has got all rows
238 --
239 EXIT WHEN (l_rows_in_this_collect = 0);
240
241 -- For each record, generate the full_name using
242 -- procedure hr_person.derive_full_name
243 for counter in 1 .. l_rows_in_this_collect
244 loop
245 hr_person.derive_full_name
246 (p_first_name => l_first_name_list(counter),
247 p_middle_names => l_middle_names_list(counter),
248 p_last_name => l_last_name_list(counter),
249 p_known_as => l_known_as_list(counter),
250 p_title => l_title_list(counter),
251 p_suffix => l_suffix_list(counter),
252 p_date_of_birth => l_date_of_birth_list(counter),
253 p_person_id => l_person_id_list(counter),
254 p_business_group_id => l_business_group_id_list(counter),
255 p_full_name => l_full_name_list(counter) ,
256 p_duplicate_flag => l_dflag);
257 end loop;
258
259 FORALL j IN l_person_id_list.FIRST..l_person_id_list.LAST
260
261 -- Touching the rows by update statement so that the actual
262 -- Full name and Order name is derived by logic in
263 -- trigger PER_ALL_PEOPLE_F_NAME
264 --
265
266 UPDATE per_all_people_f
267 SET full_name = l_full_name_list(j) -- Fix 3386992
268 WHERE person_id = l_person_id_list(j)
269 and effective_start_date = l_effective_start_date_list(j)
270 and effective_end_date = l_effective_end_date_list(j);
271
272 COMMIT;
273 end if;
274
275 END LOOP;
276
277 EXCEPTION
278 WHEN OTHERS THEN
279 retcode := sqlcode;
280 errbuf := sqlerrm;
281 ROLLBACK;
282 END;
283
284 END PER_FNAME_PKG;
[Home] [Help]