[Home] [Help]
PACKAGE BODY: APPS.PE_PEI_UPD
Source
1 Package Body pe_pei_upd as
2 /* $Header: pepeirhi.pkb 120.1 2005/07/25 05:01:42 jpthomas noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pe_pei_upd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< update_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml update logic. The processing of
17 -- this procedure is:
18 -- 1) Increment the object_version_number by 1 if the object_version_number
19 -- is defined as an attribute for this entity.
20 -- 2) To set and unset the g_api_dml status as required (as we are about to
21 -- perform dml).
22 -- (Note: Sue 1/29/97 Removed the need for setting g_api_dml as this is a new
23 -- table and therefore there is no ovn trigger to use it).
24 -- 3) To update the specified row in the schema using the primary key in
25 -- the predicates.
26 -- 4) To trap any constraint violations that may have occurred.
27 -- 5) To raise any other errors.
28 --
29 -- Pre Conditions:
30 -- This is an internal private procedure which must be called from the upd
31 -- procedure.
32 --
33 -- In Parameters:
34 -- A Pl/Sql record structre.
35 --
36 -- Post Success:
37 -- The specified row will be updated in the schema.
38 --
39 -- Post Failure:
40 -- On the update dml failure it is important to note that we always reset the
41 -- g_api_dml status to false.
42 -- If a check, unique or parent integrity constraint violation is raised the
43 -- constraint_error procedure will be called.
44 -- If any other error is reported, the error will be raised after the
45 -- g_api_dml status is reset.
46 -- (Note: Sue 1/29/97 Removed the need for setting g_api_dml as this is a new
47 -- table and therefore there is no ovn trigger to use it).
48 --
49 -- Developer Implementation Notes:
50 -- The update 'set' attribute list should be modified if any of your
51 -- attributes are not updateable.
52 --
53 -- Access Status:
54 -- Internal Table Handler Use Only.
55 --
56 -- {End Of Comments}
57 -- ----------------------------------------------------------------------------
58 Procedure update_dml(p_rec in out NOCOPY pe_pei_shd.g_rec_type) is
59 --
60 l_proc varchar2(72) := g_package||'update_dml';
61 --
62 Begin
63 hr_utility.set_location('Entering:'||l_proc, 5);
64 --
65 -- Increment the object version
66 --
67 p_rec.object_version_number := p_rec.object_version_number + 1;
68 --
69 -- Update the per_people_extra_info Row
70 --
71 update per_people_extra_info
72 set
73 person_extra_info_id = p_rec.person_extra_info_id,
74 request_id = p_rec.request_id,
75 program_application_id = p_rec.program_application_id,
76 program_id = p_rec.program_id,
77 program_update_date = p_rec.program_update_date,
78 pei_attribute_category = p_rec.pei_attribute_category,
79 pei_attribute1 = p_rec.pei_attribute1,
80 pei_attribute2 = p_rec.pei_attribute2,
81 pei_attribute3 = p_rec.pei_attribute3,
82 pei_attribute4 = p_rec.pei_attribute4,
83 pei_attribute5 = p_rec.pei_attribute5,
84 pei_attribute6 = p_rec.pei_attribute6,
85 pei_attribute7 = p_rec.pei_attribute7,
86 pei_attribute8 = p_rec.pei_attribute8,
87 pei_attribute9 = p_rec.pei_attribute9,
88 pei_attribute10 = p_rec.pei_attribute10,
89 pei_attribute11 = p_rec.pei_attribute11,
90 pei_attribute12 = p_rec.pei_attribute12,
91 pei_attribute13 = p_rec.pei_attribute13,
92 pei_attribute14 = p_rec.pei_attribute14,
93 pei_attribute15 = p_rec.pei_attribute15,
94 pei_attribute16 = p_rec.pei_attribute16,
95 pei_attribute17 = p_rec.pei_attribute17,
96 pei_attribute18 = p_rec.pei_attribute18,
97 pei_attribute19 = p_rec.pei_attribute19,
98 pei_attribute20 = p_rec.pei_attribute20,
99 pei_information_category = p_rec.pei_information_category,
100 pei_information1 = p_rec.pei_information1,
101 pei_information2 = p_rec.pei_information2,
102 pei_information3 = p_rec.pei_information3,
103 pei_information4 = p_rec.pei_information4,
104 pei_information5 = p_rec.pei_information5,
105 pei_information6 = p_rec.pei_information6,
106 pei_information7 = p_rec.pei_information7,
107 pei_information8 = p_rec.pei_information8,
108 pei_information9 = p_rec.pei_information9,
109 pei_information10 = p_rec.pei_information10,
110 pei_information11 = p_rec.pei_information11,
111 pei_information12 = p_rec.pei_information12,
112 pei_information13 = p_rec.pei_information13,
113 pei_information14 = p_rec.pei_information14,
114 pei_information15 = p_rec.pei_information15,
115 pei_information16 = p_rec.pei_information16,
116 pei_information17 = p_rec.pei_information17,
117 pei_information18 = p_rec.pei_information18,
118 pei_information19 = p_rec.pei_information19,
119 pei_information20 = p_rec.pei_information20,
120 pei_information21 = p_rec.pei_information21,
121 pei_information22 = p_rec.pei_information22,
122 pei_information23 = p_rec.pei_information23,
123 pei_information24 = p_rec.pei_information24,
124 pei_information25 = p_rec.pei_information25,
125 pei_information26 = p_rec.pei_information26,
126 pei_information27 = p_rec.pei_information27,
127 pei_information28 = p_rec.pei_information28,
128 pei_information29 = p_rec.pei_information29,
129 pei_information30 = p_rec.pei_information30,
130 object_version_number = p_rec.object_version_number
131 where person_extra_info_id = p_rec.person_extra_info_id;
132 --
133 hr_utility.set_location(' Leaving:'||l_proc, 10);
134 --
135 Exception
136 When hr_api.check_integrity_violated Then
137 -- A check constraint has been violated
138 pe_pei_shd.constraint_error
139 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
140 When hr_api.parent_integrity_violated Then
141 -- Parent integrity has been violated
142 pe_pei_shd.constraint_error
143 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
144 When hr_api.unique_integrity_violated Then
145 -- Unique integrity has been violated
146 pe_pei_shd.constraint_error
147 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
148 When Others Then
149 Raise;
150 End update_dml;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------------------------------< pre_update >------------------------------|
154 -- ----------------------------------------------------------------------------
155 -- {Start Of Comments}
156 --
157 -- Description:
158 -- This private procedure contains any processing which is required before
159 -- the update dml.
160 --
161 -- Pre Conditions:
162 -- This is an internal procedure which is called from the upd procedure.
163 --
164 -- In Parameters:
165 -- A Pl/Sql record structre.
166 --
167 -- Post Success:
168 -- Processing continues.
169 --
170 -- Post Failure:
171 -- If an error has occurred, an error message and exception will be raised
172 -- but not handled.
173 --
174 -- Developer Implementation Notes:
175 -- Any pre-processing required before the update dml is issued should be
176 -- coded within this procedure. It is important to note that any 3rd party
177 -- maintenance should be reviewed before placing in this procedure.
178 --
179 -- Access Status:
180 -- Internal Table Handler Use Only.
181 --
182 -- {End Of Comments}
183 -- ----------------------------------------------------------------------------
184 Procedure pre_update(p_rec in pe_pei_shd.g_rec_type) is
185 --
186 l_proc varchar2(72) := g_package||'pre_update';
187 --
188 Begin
189 hr_utility.set_location('Entering:'||l_proc, 5);
190 --
191 hr_utility.set_location(' Leaving:'||l_proc, 10);
192 End pre_update;
193 --
194 -- ----------------------------------------------------------------------------
195 -- |-----------------------------< post_update >------------------------------|
196 -- ----------------------------------------------------------------------------
197 -- {Start Of Comments}
198 --
199 -- Description:
200 -- This private procedure contains any processing which is required after the
201 -- update dml.
202 --
203 -- Pre Conditions:
204 -- This is an internal procedure which is called from the upd procedure.
205 --
206 -- In Parameters:
207 -- A Pl/Sql record structre.
208 --
209 -- Post Success:
210 -- Processing continues.
211 --
212 -- Post Failure:
213 -- If an error has occurred, an error message and exception will be raised
214 -- but not handled.
215 --
216 -- Developer Implementation Notes:
217 -- Any post-processing required after the update dml is issued should be
218 -- coded within this procedure. It is important to note that any 3rd party
219 -- maintenance should be reviewed before placing in this procedure.
220 --
221 -- Access Status:
222 -- Internal Table Handler Use Only.
223 --
224 -- {End Of Comments}
225 -- ----------------------------------------------------------------------------
226 Procedure post_update(p_rec in pe_pei_shd.g_rec_type) is
227 --
228 l_proc varchar2(72) := g_package||'post_update';
229 l_eot date := to_date('31/12/4712','dd/mm/yyyy');
230 --
231 Begin
232 hr_utility.set_location('Entering:'||l_proc, 5);
233 --
234 -- This is a hook point and the user hook for post_update is called here.
235 --
236 begin
237 pe_pei_rku.after_update (
238 p_person_extra_info_id => p_rec.person_extra_info_id ,
239 p_person_id => p_rec.person_id ,
240 p_information_type => p_rec.information_type ,
241 p_request_id => p_rec.request_id ,
242 p_program_application_id => p_rec.program_application_id ,
243 p_program_id => p_rec.program_id ,
244 p_program_update_date => p_rec.program_update_date ,
245 p_pei_attribute_category => p_rec.pei_attribute_category ,
246 p_pei_attribute1 => p_rec.pei_attribute1 ,
247 p_pei_attribute2 => p_rec.pei_attribute2 ,
248 p_pei_attribute3 => p_rec.pei_attribute3 ,
249 p_pei_attribute4 => p_rec.pei_attribute4 ,
250 p_pei_attribute5 => p_rec.pei_attribute5 ,
251 p_pei_attribute6 => p_rec.pei_attribute6 ,
252 p_pei_attribute7 => p_rec.pei_attribute7 ,
253 p_pei_attribute8 => p_rec.pei_attribute8 ,
254 p_pei_attribute9 => p_rec.pei_attribute9 ,
255 p_pei_attribute10 => p_rec.pei_attribute10 ,
256 p_pei_attribute11 => p_rec.pei_attribute11 ,
257 p_pei_attribute12 => p_rec.pei_attribute12 ,
258 p_pei_attribute13 => p_rec.pei_attribute13 ,
259 p_pei_attribute14 => p_rec.pei_attribute14 ,
260 p_pei_attribute15 => p_rec.pei_attribute15 ,
261 p_pei_attribute16 => p_rec.pei_attribute16 ,
262 p_pei_attribute17 => p_rec.pei_attribute17 ,
263 p_pei_attribute18 => p_rec.pei_attribute18 ,
264 p_pei_attribute19 => p_rec.pei_attribute19 ,
265 p_pei_attribute20 => p_rec.pei_attribute20 ,
266 p_pei_information_category => p_rec.pei_information_category,
267 p_pei_information1 => p_rec.pei_information1 ,
268 p_pei_information2 => p_rec.pei_information2 ,
269 p_pei_information3 => p_rec.pei_information3 ,
270 p_pei_information4 => p_rec.pei_information4 ,
271 p_pei_information5 => p_rec.pei_information5 ,
272 p_pei_information6 => p_rec.pei_information6 ,
273 p_pei_information7 => p_rec.pei_information7 ,
274 p_pei_information8 => p_rec.pei_information8 ,
275 p_pei_information9 => p_rec.pei_information9 ,
276 p_pei_information10 => p_rec.pei_information10 ,
277 p_pei_information11 => p_rec.pei_information11 ,
278 p_pei_information12 => p_rec.pei_information12 ,
279 p_pei_information13 => p_rec.pei_information13 ,
280 p_pei_information14 => p_rec.pei_information14 ,
281 p_pei_information15 => p_rec.pei_information15 ,
282 p_pei_information16 => p_rec.pei_information16 ,
283 p_pei_information17 => p_rec.pei_information17 ,
284 p_pei_information18 => p_rec.pei_information18 ,
285 p_pei_information19 => p_rec.pei_information19 ,
286 p_pei_information20 => p_rec.pei_information20 ,
287 p_pei_information21 => p_rec.pei_information21 ,
288 p_pei_information22 => p_rec.pei_information22 ,
289 p_pei_information23 => p_rec.pei_information23 ,
290 p_pei_information24 => p_rec.pei_information24 ,
291 p_pei_information25 => p_rec.pei_information25 ,
292 p_pei_information26 => p_rec.pei_information26 ,
293 p_pei_information27 => p_rec.pei_information27 ,
294 p_pei_information28 => p_rec.pei_information28 ,
295 p_pei_information29 => p_rec.pei_information29 ,
296 p_pei_information30 => p_rec.pei_information30 ,
297 p_person_id_o => pe_pei_shd.g_old_rec.person_id ,
298 p_information_type_o => pe_pei_shd.g_old_rec.information_type ,
299 p_request_id_o => pe_pei_shd.g_old_rec.request_id ,
300 p_program_application_id_o => pe_pei_shd.g_old_rec.program_application_id ,
301 p_program_id_o => pe_pei_shd.g_old_rec.program_id ,
302 p_program_update_date_o => pe_pei_shd.g_old_rec.program_update_date ,
303 p_pei_attribute_category_o => pe_pei_shd.g_old_rec.pei_attribute_category ,
304 p_pei_attribute1_o => pe_pei_shd.g_old_rec.pei_attribute1 ,
305 p_pei_attribute2_o => pe_pei_shd.g_old_rec.pei_attribute2 ,
306 p_pei_attribute3_o => pe_pei_shd.g_old_rec.pei_attribute3 ,
307 p_pei_attribute4_o => pe_pei_shd.g_old_rec.pei_attribute4 ,
308 p_pei_attribute5_o => pe_pei_shd.g_old_rec.pei_attribute5 ,
309 p_pei_attribute6_o => pe_pei_shd.g_old_rec.pei_attribute6 ,
310 p_pei_attribute7_o => pe_pei_shd.g_old_rec.pei_attribute7 ,
311 p_pei_attribute8_o => pe_pei_shd.g_old_rec.pei_attribute8 ,
312 p_pei_attribute9_o => pe_pei_shd.g_old_rec.pei_attribute9 ,
313 p_pei_attribute10_o => pe_pei_shd.g_old_rec.pei_attribute10 ,
314 p_pei_attribute11_o => pe_pei_shd.g_old_rec.pei_attribute11 ,
315 p_pei_attribute12_o => pe_pei_shd.g_old_rec.pei_attribute12 ,
316 p_pei_attribute13_o => pe_pei_shd.g_old_rec.pei_attribute13 ,
317 p_pei_attribute14_o => pe_pei_shd.g_old_rec.pei_attribute14 ,
318 p_pei_attribute15_o => pe_pei_shd.g_old_rec.pei_attribute15 ,
319 p_pei_attribute16_o => pe_pei_shd.g_old_rec.pei_attribute16 ,
320 p_pei_attribute17_o => pe_pei_shd.g_old_rec.pei_attribute17 ,
321 p_pei_attribute18_o => pe_pei_shd.g_old_rec.pei_attribute18 ,
322 p_pei_attribute19_o => pe_pei_shd.g_old_rec.pei_attribute19 ,
323 p_pei_attribute20_o => pe_pei_shd.g_old_rec.pei_attribute20 ,
324 p_pei_information_category_o => pe_pei_shd.g_old_rec.pei_information_category ,
325 p_pei_information1_o => pe_pei_shd.g_old_rec.pei_information1 ,
326 p_pei_information2_o => pe_pei_shd.g_old_rec.pei_information2 ,
327 p_pei_information3_o => pe_pei_shd.g_old_rec.pei_information3 ,
328 p_pei_information4_o => pe_pei_shd.g_old_rec.pei_information4 ,
329 p_pei_information5_o => pe_pei_shd.g_old_rec.pei_information5 ,
330 p_pei_information6_o => pe_pei_shd.g_old_rec.pei_information6 ,
331 p_pei_information7_o => pe_pei_shd.g_old_rec.pei_information7 ,
332 p_pei_information8_o => pe_pei_shd.g_old_rec.pei_information8 ,
333 p_pei_information9_o => pe_pei_shd.g_old_rec.pei_information9 ,
334 p_pei_information10_o => pe_pei_shd.g_old_rec.pei_information10 ,
335 p_pei_information11_o => pe_pei_shd.g_old_rec.pei_information11 ,
336 p_pei_information12_o => pe_pei_shd.g_old_rec.pei_information12 ,
337 p_pei_information13_o => pe_pei_shd.g_old_rec.pei_information13 ,
338 p_pei_information14_o => pe_pei_shd.g_old_rec.pei_information14 ,
339 p_pei_information15_o => pe_pei_shd.g_old_rec.pei_information15 ,
340 p_pei_information16_o => pe_pei_shd.g_old_rec.pei_information16 ,
341 p_pei_information17_o => pe_pei_shd.g_old_rec.pei_information17 ,
342 p_pei_information18_o => pe_pei_shd.g_old_rec.pei_information18 ,
343 p_pei_information19_o => pe_pei_shd.g_old_rec.pei_information19 ,
344 p_pei_information20_o => pe_pei_shd.g_old_rec.pei_information20 ,
345 p_pei_information21_o => pe_pei_shd.g_old_rec.pei_information21 ,
346 p_pei_information22_o => pe_pei_shd.g_old_rec.pei_information22 ,
347 p_pei_information23_o => pe_pei_shd.g_old_rec.pei_information23 ,
348 p_pei_information24_o => pe_pei_shd.g_old_rec.pei_information24 ,
349 p_pei_information25_o => pe_pei_shd.g_old_rec.pei_information25 ,
350 p_pei_information26_o => pe_pei_shd.g_old_rec.pei_information26 ,
351 p_pei_information27_o => pe_pei_shd.g_old_rec.pei_information27 ,
352 p_pei_information28_o => pe_pei_shd.g_old_rec.pei_information28 ,
353 p_pei_information29_o => pe_pei_shd.g_old_rec.pei_information29 ,
354 p_pei_information30_o => pe_pei_shd.g_old_rec.pei_information30
355 );
356 exception
357 when hr_api.cannot_find_prog_unit then
358 hr_api.cannot_find_prog_unit_error
359 ( p_module_name => 'PER_PEOPLE_EXTRA_INFO'
360 ,p_hook_type => 'AU'
361 );
362 end;
363 -- End of API User Hook for post_update.
364 --
365 --
366 if p_rec.INFORMATION_TYPE = 'PQH_ROLE_USERS' then
367 if nvl(pe_pei_shd.g_old_rec.pei_information5,'N') = 'N'
368 and nvl(p_rec.PEI_INFORMATION5,'N') = 'Y' then
369 declare
370 l_user_name varchar2(50);
371 l_start_date date;
372 l_expiration_date date;
373 cursor c1 is
374 select usr.user_name, usr.start_date, nvl(usr.end_date, hr_general.end_of_time)
375 from fnd_user usr
376 where usr.employee_id = p_rec.person_id;
377 begin
378 open c1;
379 fetch c1 into l_user_name, l_start_date, l_expiration_date;
380 if c1%found then
381 close c1;
382
383 WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system => 'PER',
384 p_user_orig_system_id => p_rec.person_id,
385 p_role_orig_system => 'PQH_ROLE',
386 p_role_orig_system_id => p_rec.pei_information3,
387 p_start_date => l_start_date,
388 p_expiration_date => l_expiration_date);
389 else
390 close c1;
391 end if;
392 end;
393 elsif nvl(pe_pei_shd.g_old_rec.pei_information5,'N') = 'Y'
394 and nvl(p_rec.PEI_INFORMATION5,'N') = 'Y'
395 and (pe_pei_shd.g_old_rec.pei_information3 <> p_rec.PEI_INFORMATION3) then
396 declare
397 l_user_name varchar2(50);
398 l_start_date date;
399 l_expiration_date date;
400 cursor c1 is
401 select usr.user_name, usr.start_date, usr.start_date
402 from fnd_user usr
403 where usr.employee_id = p_rec.person_id;
404
405 begin
406 open c1;
407 fetch c1 into l_user_name, l_start_date, l_expiration_date;
408 if c1%found then
409 close c1;
410
411 WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system => 'PER',
412 p_user_orig_system_id => pe_pei_shd.g_old_rec.person_id,
413 p_role_orig_system => 'PQH_ROLE',
414 p_role_orig_system_id => pe_pei_shd.g_old_rec.pei_information3,
415 p_start_date => l_start_date,
416 p_expiration_date => l_expiration_date
417 );
418 else
419 close c1;
420 end if;
421 end;
422 declare
423 l_user_name varchar2(50);
424 l_start_date date;
425 l_expiration_date date;
426 cursor c1 is
427 select usr.user_name, usr.start_date, nvl(usr.end_date, hr_general.end_of_time)
428 from fnd_user usr
429 where usr.employee_id = p_rec.person_id;
430 begin
431 open c1;
432 fetch c1 into l_user_name, l_start_date, l_expiration_date;
433 if c1%found then
434 close c1;
435
436 WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system => 'PER',
437 p_user_orig_system_id => p_rec.person_id,
438 p_role_orig_system => 'PQH_ROLE',
439 p_role_orig_system_id => p_rec.pei_information3,
440 p_start_date => l_start_date,
441 p_expiration_date => l_expiration_date);
442 else
443 close c1;
444 end if;
445 end;
446 elsif nvl(pe_pei_shd.g_old_rec.pei_information5,'N') = 'Y'
447 and nvl(p_rec.PEI_INFORMATION5,'N') = 'N' then
448 declare
449 l_user_name varchar2(50);
450 l_start_date date;
451 l_expiration_date date;
452 cursor c1 is
453 select usr.user_name, usr.start_date, usr.start_date
454 from fnd_user usr
455 where usr.employee_id = p_rec.person_id;
456
457 begin
458 open c1;
459 fetch c1 into l_user_name, l_start_date, l_expiration_date;
460 if c1%found then
461 close c1;
462 WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system => 'PER',
463 p_user_orig_system_id => pe_pei_shd.g_old_rec.person_id,
464 p_role_orig_system => 'PQH_ROLE',
465 p_role_orig_system_id => pe_pei_shd.g_old_rec.pei_information3,
466 p_start_date => l_start_date,
467 p_expiration_date => l_expiration_date);
468 else
469 close c1;
470 end if;
471 end;
472 end if;
473 end if;
474 hr_utility.set_location(' Leaving:'||l_proc, 10);
475 End post_update;
476 --
477 -- ----------------------------------------------------------------------------
478 -- |-----------------------------< convert_defs >-----------------------------|
479 -- ----------------------------------------------------------------------------
480 -- {Start Of Comments}
481 --
482 -- Description:
483 -- The Convert_Defs procedure has one very important function:
484 -- It must return the record structure for the row with all system defaulted
485 -- values converted into its corresponding parameter value for update. When
486 -- we attempt to update a row through the Upd process , certain
487 -- parameters can be defaulted which enables flexibility in the calling of
488 -- the upd process (e.g. only attributes which need to be updated need to be
489 -- specified). For the upd process to determine which attributes
490 -- have NOT been specified we need to check if the parameter has a reserved
491 -- system default value. Therefore, for all parameters which have a
492 -- corresponding reserved system default mechanism specified we need to
493 -- check if a system default is being used. If a system default is being
494 -- used then we convert the defaulted value into its corresponding attribute
495 -- value held in the g_old_rec data structure.
496 --
497 -- Pre Conditions:
498 -- This private function can only be called from the upd process.
499 --
500 -- In Parameters:
501 -- A Pl/Sql record structre.
502 --
503 -- Post Success:
504 -- The record structure will be returned with all system defaulted parameter
505 -- values converted into its current row attribute value.
506 --
507 -- Post Failure:
508 -- No direct error handling is required within this function. Any possible
509 -- errors within this procedure will be a PL/SQL value error due to conversion
510
511 -- of datatypes or data lengths.
512 --
513 -- Developer Implementation Notes:
514 -- None.
515 --
516 -- Access Status:
517 -- Internal Table Handler Use Only.
518 --
519 -- {End Of Comments}
520 -- ----------------------------------------------------------------------------
521 Procedure convert_defs(p_rec in out NOCOPY pe_pei_shd.g_rec_type) is
522 --
523 l_proc varchar2(72) := g_package||'convert_defs';
524 --
525 Begin
526 --
527 hr_utility.set_location('Entering:'||l_proc, 5);
528 --
529 -- We must now examine each argument value in the
530 -- p_rec plsql record structure
531 -- to see if a system default is being used. If a system default
532 -- is being used then we must set to the 'current' argument value.
533 --
534 If (p_rec.person_id = hr_api.g_number) then
535 p_rec.person_id :=
536 pe_pei_shd.g_old_rec.person_id;
537 End If;
538 If (p_rec.information_type = hr_api.g_varchar2) then
539 p_rec.information_type :=
540 pe_pei_shd.g_old_rec.information_type;
541 End If;
542 If (p_rec.request_id = hr_api.g_number) then
543 p_rec.request_id :=
544 pe_pei_shd.g_old_rec.request_id;
545 End If;
546 If (p_rec.program_application_id = hr_api.g_number) then
547 p_rec.program_application_id :=
548 pe_pei_shd.g_old_rec.program_application_id;
549 End If;
550 If (p_rec.program_id = hr_api.g_number) then
551 p_rec.program_id :=
552 pe_pei_shd.g_old_rec.program_id;
553 End If;
554 If (p_rec.program_update_date = hr_api.g_date) then
555 p_rec.program_update_date :=
556 pe_pei_shd.g_old_rec.program_update_date;
557 End If;
558 If (p_rec.pei_attribute_category = hr_api.g_varchar2) then
559 p_rec.pei_attribute_category :=
560 pe_pei_shd.g_old_rec.pei_attribute_category;
561 End If;
562 If (p_rec.pei_attribute1 = hr_api.g_varchar2) then
563 p_rec.pei_attribute1 :=
564 pe_pei_shd.g_old_rec.pei_attribute1;
565 End If;
566 If (p_rec.pei_attribute2 = hr_api.g_varchar2) then
567 p_rec.pei_attribute2 :=
568 pe_pei_shd.g_old_rec.pei_attribute2;
569 End If;
570 If (p_rec.pei_attribute3 = hr_api.g_varchar2) then
571 p_rec.pei_attribute3 :=
572 pe_pei_shd.g_old_rec.pei_attribute3;
573 End If;
574 If (p_rec.pei_attribute4 = hr_api.g_varchar2) then
575 p_rec.pei_attribute4 :=
576 pe_pei_shd.g_old_rec.pei_attribute4;
577 End If;
578 If (p_rec.pei_attribute5 = hr_api.g_varchar2) then
579 p_rec.pei_attribute5 :=
580 pe_pei_shd.g_old_rec.pei_attribute5;
581 End If;
582 If (p_rec.pei_attribute6 = hr_api.g_varchar2) then
583 p_rec.pei_attribute6 :=
584 pe_pei_shd.g_old_rec.pei_attribute6;
585 End If;
586 If (p_rec.pei_attribute7 = hr_api.g_varchar2) then
587 p_rec.pei_attribute7 :=
588 pe_pei_shd.g_old_rec.pei_attribute7;
589 End If;
590 If (p_rec.pei_attribute8 = hr_api.g_varchar2) then
591 p_rec.pei_attribute8 :=
592 pe_pei_shd.g_old_rec.pei_attribute8;
593 End If;
594 If (p_rec.pei_attribute9 = hr_api.g_varchar2) then
595 p_rec.pei_attribute9 :=
596 pe_pei_shd.g_old_rec.pei_attribute9;
597 End If;
598 If (p_rec.pei_attribute10 = hr_api.g_varchar2) then
599 p_rec.pei_attribute10 :=
600 pe_pei_shd.g_old_rec.pei_attribute10;
601 End If;
602 If (p_rec.pei_attribute11 = hr_api.g_varchar2) then
603 p_rec.pei_attribute11 :=
604 pe_pei_shd.g_old_rec.pei_attribute11;
605 End If;
606 If (p_rec.pei_attribute12 = hr_api.g_varchar2) then
607 p_rec.pei_attribute12 :=
608 pe_pei_shd.g_old_rec.pei_attribute12;
609 End If;
610 If (p_rec.pei_attribute13 = hr_api.g_varchar2) then
611 p_rec.pei_attribute13 :=
612 pe_pei_shd.g_old_rec.pei_attribute13;
613 End If;
614 If (p_rec.pei_attribute14 = hr_api.g_varchar2) then
615 p_rec.pei_attribute14 :=
616 pe_pei_shd.g_old_rec.pei_attribute14;
617 End If;
618 If (p_rec.pei_attribute15 = hr_api.g_varchar2) then
619 p_rec.pei_attribute15 :=
620 pe_pei_shd.g_old_rec.pei_attribute15;
621 End If;
622 If (p_rec.pei_attribute16 = hr_api.g_varchar2) then
623 p_rec.pei_attribute16 :=
624 pe_pei_shd.g_old_rec.pei_attribute16;
625 End If;
626 If (p_rec.pei_attribute17 = hr_api.g_varchar2) then
627 p_rec.pei_attribute17 :=
628 pe_pei_shd.g_old_rec.pei_attribute17;
629 End If;
630 If (p_rec.pei_attribute18 = hr_api.g_varchar2) then
631 p_rec.pei_attribute18 :=
632 pe_pei_shd.g_old_rec.pei_attribute18;
633 End If;
634 If (p_rec.pei_attribute19 = hr_api.g_varchar2) then
635 p_rec.pei_attribute19 :=
636 pe_pei_shd.g_old_rec.pei_attribute19;
637 End If;
638 If (p_rec.pei_attribute20 = hr_api.g_varchar2) then
639 p_rec.pei_attribute20 :=
640 pe_pei_shd.g_old_rec.pei_attribute20;
641 End If;
642 If (p_rec.pei_information_category = hr_api.g_varchar2) then
643 p_rec.pei_information_category :=
644 pe_pei_shd.g_old_rec.pei_information_category;
645 End If;
646 If (p_rec.pei_information1 = hr_api.g_varchar2) then
647 p_rec.pei_information1 :=
648 pe_pei_shd.g_old_rec.pei_information1;
649 End If;
650 If (p_rec.pei_information2 = hr_api.g_varchar2) then
651 p_rec.pei_information2 :=
652 pe_pei_shd.g_old_rec.pei_information2;
653 End If;
654 If (p_rec.pei_information3 = hr_api.g_varchar2) then
655 p_rec.pei_information3 :=
656 pe_pei_shd.g_old_rec.pei_information3;
657 End If;
658 If (p_rec.pei_information4 = hr_api.g_varchar2) then
659 p_rec.pei_information4 :=
660 pe_pei_shd.g_old_rec.pei_information4;
661 End If;
662 If (p_rec.pei_information5 = hr_api.g_varchar2) then
663 p_rec.pei_information5 :=
664 pe_pei_shd.g_old_rec.pei_information5;
665 End If;
666 If (p_rec.pei_information6 = hr_api.g_varchar2) then
667 p_rec.pei_information6 :=
668 pe_pei_shd.g_old_rec.pei_information6;
669 End If;
670 If (p_rec.pei_information7 = hr_api.g_varchar2) then
671 p_rec.pei_information7 :=
672 pe_pei_shd.g_old_rec.pei_information7;
673 End If;
674 If (p_rec.pei_information8 = hr_api.g_varchar2) then
675 p_rec.pei_information8 :=
676 pe_pei_shd.g_old_rec.pei_information8;
677 End If;
678 If (p_rec.pei_information9 = hr_api.g_varchar2) then
679 p_rec.pei_information9 :=
680 pe_pei_shd.g_old_rec.pei_information9;
681 End If;
682 If (p_rec.pei_information10 = hr_api.g_varchar2) then
683 p_rec.pei_information10 :=
684 pe_pei_shd.g_old_rec.pei_information10;
685 End If;
686 If (p_rec.pei_information11 = hr_api.g_varchar2) then
687 p_rec.pei_information11 :=
688 pe_pei_shd.g_old_rec.pei_information11;
689 End If;
690 If (p_rec.pei_information12 = hr_api.g_varchar2) then
691 p_rec.pei_information12 :=
692 pe_pei_shd.g_old_rec.pei_information12;
693 End If;
694 If (p_rec.pei_information13 = hr_api.g_varchar2) then
695 p_rec.pei_information13 :=
696 pe_pei_shd.g_old_rec.pei_information13;
697 End If;
698 If (p_rec.pei_information14 = hr_api.g_varchar2) then
699 p_rec.pei_information14 :=
700 pe_pei_shd.g_old_rec.pei_information14;
701 End If;
702 If (p_rec.pei_information15 = hr_api.g_varchar2) then
703 p_rec.pei_information15 :=
704 pe_pei_shd.g_old_rec.pei_information15;
705 End If;
706 If (p_rec.pei_information16 = hr_api.g_varchar2) then
707 p_rec.pei_information16 :=
708 pe_pei_shd.g_old_rec.pei_information16;
709 End If;
710 If (p_rec.pei_information17 = hr_api.g_varchar2) then
711 p_rec.pei_information17 :=
712 pe_pei_shd.g_old_rec.pei_information17;
713 End If;
714 If (p_rec.pei_information18 = hr_api.g_varchar2) then
715 p_rec.pei_information18 :=
716 pe_pei_shd.g_old_rec.pei_information18;
717 End If;
718 If (p_rec.pei_information19 = hr_api.g_varchar2) then
719 p_rec.pei_information19 :=
720 pe_pei_shd.g_old_rec.pei_information19;
721 End If;
722 If (p_rec.pei_information20 = hr_api.g_varchar2) then
723 p_rec.pei_information20 :=
724 pe_pei_shd.g_old_rec.pei_information20;
725 End If;
726 If (p_rec.pei_information21 = hr_api.g_varchar2) then
727 p_rec.pei_information21 :=
728 pe_pei_shd.g_old_rec.pei_information21;
729 End If;
730 If (p_rec.pei_information22 = hr_api.g_varchar2) then
731 p_rec.pei_information22 :=
732 pe_pei_shd.g_old_rec.pei_information22;
733 End If;
734 If (p_rec.pei_information23 = hr_api.g_varchar2) then
735 p_rec.pei_information23 :=
736 pe_pei_shd.g_old_rec.pei_information23;
737 End If;
738 If (p_rec.pei_information24 = hr_api.g_varchar2) then
739 p_rec.pei_information24 :=
740 pe_pei_shd.g_old_rec.pei_information24;
741 End If;
742 If (p_rec.pei_information25 = hr_api.g_varchar2) then
743 p_rec.pei_information25 :=
744 pe_pei_shd.g_old_rec.pei_information25;
745 End If;
746 If (p_rec.pei_information26 = hr_api.g_varchar2) then
747 p_rec.pei_information26 :=
748 pe_pei_shd.g_old_rec.pei_information26;
749 End If;
750 If (p_rec.pei_information27 = hr_api.g_varchar2) then
751 p_rec.pei_information27 :=
752 pe_pei_shd.g_old_rec.pei_information27;
753 End If;
754 If (p_rec.pei_information28 = hr_api.g_varchar2) then
755 p_rec.pei_information28 :=
756 pe_pei_shd.g_old_rec.pei_information28;
757 End If;
758 If (p_rec.pei_information29 = hr_api.g_varchar2) then
759 p_rec.pei_information29 :=
760 pe_pei_shd.g_old_rec.pei_information29;
761 End If;
762 If (p_rec.pei_information30 = hr_api.g_varchar2) then
763 p_rec.pei_information30 :=
764 pe_pei_shd.g_old_rec.pei_information30;
765 End If;
766
767 --
768 hr_utility.set_location(' Leaving:'||l_proc, 10);
769 --
770 End convert_defs;
771 --
772 -- ----------------------------------------------------------------------------
773 -- |---------------------------------< upd >----------------------------------|
774 -- ----------------------------------------------------------------------------
775 Procedure upd
776 (
777 p_rec in out NOCOPY pe_pei_shd.g_rec_type,
778 p_validate in boolean default false
779 ) is
780 --
781 l_proc varchar2(72) := g_package||'upd';
782 --
783 Begin
784 hr_utility.set_location('Entering:'||l_proc, 5);
785 --
786 -- Determine if the business process is to be validated.
787 --
788 If p_validate then
789 --
790 -- Issue the savepoint.
791 --
792 SAVEPOINT upd_pe_pei;
793 End If;
794 --
795 -- We must lock the row which we need to update.
796 --
797 pe_pei_shd.lck
798 (
799 p_rec.person_extra_info_id,
800 p_rec.object_version_number
801 );
802 --
803 -- 1. During an update system defaults are used to determine if
804 -- arguments have been defaulted or not. We must therefore
805 -- derive the full record structure values to be updated.
806 --
807 -- 2. Call the supporting update validate operations.
808 --
809 convert_defs(p_rec);
810 pe_pei_bus.update_validate(p_rec);
811 --
812 -- Call the supporting pre-update operation
813 --
814 pre_update(p_rec);
815 --
816 -- Update the row.
817 --
818 update_dml(p_rec);
819 --
820 -- Call the supporting post-update operation
821 --
822 post_update(p_rec);
823 --
824 -- If we are validating then raise the Validate_Enabled exception
825 --
826 If p_validate then
827 Raise HR_Api.Validate_Enabled;
828 End If;
829 --
830 hr_utility.set_location(' Leaving:'||l_proc, 10);
831 Exception
832 When HR_Api.Validate_Enabled Then
833 --
834 -- As the Validate_Enabled exception has been raised
835 -- we must rollback to the savepoint
836 --
837 ROLLBACK TO upd_pe_pei;
838 End upd;
839 --
840 -- ----------------------------------------------------------------------------
841 -- |---------------------------------< upd >----------------------------------|
842 -- ----------------------------------------------------------------------------
843 Procedure upd
844 (
845 p_person_extra_info_id in number,
846 p_request_id in number default hr_api.g_number,
847 p_program_application_id in number default hr_api.g_number,
848 p_program_id in number default hr_api.g_number,
849 p_program_update_date in date default hr_api.g_date,
850 p_pei_attribute_category in varchar2 default hr_api.g_varchar2,
851 p_pei_attribute1 in varchar2 default hr_api.g_varchar2,
852 p_pei_attribute2 in varchar2 default hr_api.g_varchar2,
853 p_pei_attribute3 in varchar2 default hr_api.g_varchar2,
854 p_pei_attribute4 in varchar2 default hr_api.g_varchar2,
855 p_pei_attribute5 in varchar2 default hr_api.g_varchar2,
856 p_pei_attribute6 in varchar2 default hr_api.g_varchar2,
857 p_pei_attribute7 in varchar2 default hr_api.g_varchar2,
858 p_pei_attribute8 in varchar2 default hr_api.g_varchar2,
859 p_pei_attribute9 in varchar2 default hr_api.g_varchar2,
860 p_pei_attribute10 in varchar2 default hr_api.g_varchar2,
861 p_pei_attribute11 in varchar2 default hr_api.g_varchar2,
862 p_pei_attribute12 in varchar2 default hr_api.g_varchar2,
863 p_pei_attribute13 in varchar2 default hr_api.g_varchar2,
864 p_pei_attribute14 in varchar2 default hr_api.g_varchar2,
865 p_pei_attribute15 in varchar2 default hr_api.g_varchar2,
866 p_pei_attribute16 in varchar2 default hr_api.g_varchar2,
867 p_pei_attribute17 in varchar2 default hr_api.g_varchar2,
868 p_pei_attribute18 in varchar2 default hr_api.g_varchar2,
869 p_pei_attribute19 in varchar2 default hr_api.g_varchar2,
870 p_pei_attribute20 in varchar2 default hr_api.g_varchar2,
871 p_pei_information_category in varchar2 default hr_api.g_varchar2,
872 p_pei_information1 in varchar2 default hr_api.g_varchar2,
873 p_pei_information2 in varchar2 default hr_api.g_varchar2,
874 p_pei_information3 in varchar2 default hr_api.g_varchar2,
875 p_pei_information4 in varchar2 default hr_api.g_varchar2,
876 p_pei_information5 in varchar2 default hr_api.g_varchar2,
877 p_pei_information6 in varchar2 default hr_api.g_varchar2,
878 p_pei_information7 in varchar2 default hr_api.g_varchar2,
879 p_pei_information8 in varchar2 default hr_api.g_varchar2,
880 p_pei_information9 in varchar2 default hr_api.g_varchar2,
881 p_pei_information10 in varchar2 default hr_api.g_varchar2,
882 p_pei_information11 in varchar2 default hr_api.g_varchar2,
883 p_pei_information12 in varchar2 default hr_api.g_varchar2,
884 p_pei_information13 in varchar2 default hr_api.g_varchar2,
885 p_pei_information14 in varchar2 default hr_api.g_varchar2,
886 p_pei_information15 in varchar2 default hr_api.g_varchar2,
887 p_pei_information16 in varchar2 default hr_api.g_varchar2,
888 p_pei_information17 in varchar2 default hr_api.g_varchar2,
889 p_pei_information18 in varchar2 default hr_api.g_varchar2,
890 p_pei_information19 in varchar2 default hr_api.g_varchar2,
891 p_pei_information20 in varchar2 default hr_api.g_varchar2,
892 p_pei_information21 in varchar2 default hr_api.g_varchar2,
893 p_pei_information22 in varchar2 default hr_api.g_varchar2,
894 p_pei_information23 in varchar2 default hr_api.g_varchar2,
895 p_pei_information24 in varchar2 default hr_api.g_varchar2,
896 p_pei_information25 in varchar2 default hr_api.g_varchar2,
897 p_pei_information26 in varchar2 default hr_api.g_varchar2,
898 p_pei_information27 in varchar2 default hr_api.g_varchar2,
899 p_pei_information28 in varchar2 default hr_api.g_varchar2,
900 p_pei_information29 in varchar2 default hr_api.g_varchar2,
901 p_pei_information30 in varchar2 default hr_api.g_varchar2,
902 p_object_version_number in out NOCOPY number,
903 p_validate in boolean default false
904 ) is
905 --
906 l_rec pe_pei_shd.g_rec_type;
907 l_proc varchar2(72) := g_package||'upd';
908 --
909 Begin
910 hr_utility.set_location('Entering:'||l_proc, 5);
911 --
912 -- Call conversion function to turn arguments into the
913 -- l_rec structure.
914 --
915 l_rec :=
916 pe_pei_shd.convert_args
917 (
918 p_person_extra_info_id,
919 hr_api.g_number,
920 hr_api.g_varchar2,
921 p_request_id,
922 p_program_application_id,
923 p_program_id,
924 p_program_update_date,
925 p_pei_attribute_category,
926 p_pei_attribute1,
927 p_pei_attribute2,
928 p_pei_attribute3,
929 p_pei_attribute4,
930 p_pei_attribute5,
931 p_pei_attribute6,
932 p_pei_attribute7,
933 p_pei_attribute8,
934 p_pei_attribute9,
935 p_pei_attribute10,
936 p_pei_attribute11,
937 p_pei_attribute12,
938 p_pei_attribute13,
939 p_pei_attribute14,
940 p_pei_attribute15,
941 p_pei_attribute16,
942 p_pei_attribute17,
943 p_pei_attribute18,
944 p_pei_attribute19,
945 p_pei_attribute20,
946 p_pei_information_category,
947 p_pei_information1,
948 p_pei_information2,
949 p_pei_information3,
950 p_pei_information4,
951 p_pei_information5,
952 p_pei_information6,
953 p_pei_information7,
954 p_pei_information8,
955 p_pei_information9,
956 p_pei_information10,
957 p_pei_information11,
958 p_pei_information12,
959 p_pei_information13,
960 p_pei_information14,
961 p_pei_information15,
962 p_pei_information16,
963 p_pei_information17,
964 p_pei_information18,
965 p_pei_information19,
966 p_pei_information20,
967 p_pei_information21,
968 p_pei_information22,
969 p_pei_information23,
970 p_pei_information24,
971 p_pei_information25,
972 p_pei_information26,
973 p_pei_information27,
974 p_pei_information28,
975 p_pei_information29,
976 p_pei_information30,
977 p_object_version_number
978 );
979 --
980 -- Having converted the arguments into the
981 -- plsql record structure we call the corresponding record
982 -- business process.
983 --
984 upd(l_rec, p_validate);
985 p_object_version_number := l_rec.object_version_number;
986 --
987 hr_utility.set_location(' Leaving:'||l_proc, 10);
988 End upd;
989 --
990 end pe_pei_upd;