[Home] [Help]
PACKAGE BODY: APPS.PE_PEI_INS
Source
1 Package Body pe_pei_ins 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_ins.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< insert_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml insert logic. The processing of
17 -- this procedure are as follows:
18 -- 1) Initialise the object_version_number to 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 therfore there is no ovn trigger to use it).
24 -- 3) To insert the row into the schema.
25 -- 4) To trap any constraint violations that may have occurred.
26 -- 5) To raise any other errors.
27 --
28 -- Pre Conditions:
29 -- This is an internal private procedure which must be called from the ins
30 -- procedure and must have all mandatory attributes set (except the
31 -- object_version_number which is initialised within this procedure).
32 --
33 -- In Parameters:
34 -- A Pl/Sql record structre.
35 --
36 -- Post Success:
37 -- The specified row will be inserted into the schema.
38 --
39 -- Post Failure:
40 -- On the insert 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 therfore there is no ovn trigger to use it).
48 --
49 -- Developer Implementation Notes:
50 -- None.
51 --
52 -- Access Status:
53 -- Internal Table Handler Use Only.
54 --
55 -- {End Of Comments}
56 -- ----------------------------------------------------------------------------
57 Procedure insert_dml(p_rec in out NOCOPY pe_pei_shd.g_rec_type) is
58 --
59 l_proc varchar2(72) := g_package||'insert_dml';
60 --
61 Begin
62 hr_utility.set_location('Entering:'||l_proc, 5);
63 p_rec.object_version_number := 1; -- Initialise the object version
64 --
65 -- Insert the row into: per_people_extra_info
66 --
67 insert into per_people_extra_info
68 ( person_extra_info_id,
69 person_id,
70 information_type,
71 request_id,
72 program_application_id,
73 program_id,
74 program_update_date,
75 pei_attribute_category,
76 pei_attribute1,
77 pei_attribute2,
78 pei_attribute3,
79 pei_attribute4,
80 pei_attribute5,
81 pei_attribute6,
82 pei_attribute7,
83 pei_attribute8,
84 pei_attribute9,
85 pei_attribute10,
86 pei_attribute11,
87 pei_attribute12,
88 pei_attribute13,
89 pei_attribute14,
90 pei_attribute15,
91 pei_attribute16,
92 pei_attribute17,
93 pei_attribute18,
94 pei_attribute19,
95 pei_attribute20,
96 pei_information_category,
97 pei_information1,
98 pei_information2,
99 pei_information3,
100 pei_information4,
101 pei_information5,
102 pei_information6,
103 pei_information7,
104 pei_information8,
105 pei_information9,
106 pei_information10,
107 pei_information11,
108 pei_information12,
109 pei_information13,
110 pei_information14,
111 pei_information15,
112 pei_information16,
113 pei_information17,
114 pei_information18,
115 pei_information19,
116 pei_information20,
117 pei_information21,
118 pei_information22,
119 pei_information23,
120 pei_information24,
121 pei_information25,
122 pei_information26,
123 pei_information27,
124 pei_information28,
125 pei_information29,
126 pei_information30,
127 object_version_number
128 )
129 Values
130 ( p_rec.person_extra_info_id,
131 p_rec.person_id,
132 p_rec.information_type,
133 p_rec.request_id,
134 p_rec.program_application_id,
135 p_rec.program_id,
136 p_rec.program_update_date,
137 p_rec.pei_attribute_category,
138 p_rec.pei_attribute1,
139 p_rec.pei_attribute2,
140 p_rec.pei_attribute3,
141 p_rec.pei_attribute4,
142 p_rec.pei_attribute5,
143 p_rec.pei_attribute6,
144 p_rec.pei_attribute7,
145 p_rec.pei_attribute8,
146 p_rec.pei_attribute9,
147 p_rec.pei_attribute10,
148 p_rec.pei_attribute11,
149 p_rec.pei_attribute12,
150 p_rec.pei_attribute13,
151 p_rec.pei_attribute14,
152 p_rec.pei_attribute15,
153 p_rec.pei_attribute16,
154 p_rec.pei_attribute17,
155 p_rec.pei_attribute18,
156 p_rec.pei_attribute19,
157 p_rec.pei_attribute20,
158 p_rec.pei_information_category,
159 p_rec.pei_information1,
160 p_rec.pei_information2,
161 p_rec.pei_information3,
162 p_rec.pei_information4,
163 p_rec.pei_information5,
164 p_rec.pei_information6,
165 p_rec.pei_information7,
166 p_rec.pei_information8,
167 p_rec.pei_information9,
168 p_rec.pei_information10,
169 p_rec.pei_information11,
170 p_rec.pei_information12,
171 p_rec.pei_information13,
172 p_rec.pei_information14,
173 p_rec.pei_information15,
174 p_rec.pei_information16,
175 p_rec.pei_information17,
176 p_rec.pei_information18,
177 p_rec.pei_information19,
178 p_rec.pei_information20,
179 p_rec.pei_information21,
180 p_rec.pei_information22,
181 p_rec.pei_information23,
182 p_rec.pei_information24,
183 p_rec.pei_information25,
184 p_rec.pei_information26,
185 p_rec.pei_information27,
186 p_rec.pei_information28,
187 p_rec.pei_information29,
188 p_rec.pei_information30,
189 p_rec.object_version_number
190 );
191 --
192 hr_utility.set_location(' Leaving:'||l_proc, 10);
193 Exception
194 When hr_api.check_integrity_violated Then
195 -- A check constraint has been violated
196 pe_pei_shd.constraint_error
197 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
198 When hr_api.parent_integrity_violated Then
199 -- Parent integrity has been violated
200 pe_pei_shd.constraint_error
201 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
202 When hr_api.unique_integrity_violated Then
203 -- Unique integrity has been violated
204 pe_pei_shd.constraint_error
205 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
206 When Others Then
207 Raise;
208 End insert_dml;
209 --
210 -- ----------------------------------------------------------------------------
211 -- |------------------------------< pre_insert >------------------------------|
212 -- ----------------------------------------------------------------------------
213 -- {Start Of Comments}
214 --
215 -- Description:
216 -- This private procedure contains any processing which is required before
217 -- the insert dml. Presently, if the entity has a corresponding primary
218 -- key which is maintained by an associating sequence, the primary key for
219 -- the entity will be populated with the next sequence value in
220 -- preparation for the insert dml.
221 --
222 -- Pre Conditions:
223 -- This is an internal procedure which is called from the ins procedure.
224 --
225 -- In Parameters:
226 -- A Pl/Sql record structre.
227 --
228 -- Post Success:
229 -- Processing continues.
230 --
231 -- Post Failure:
232 -- If an error has occurred, an error message and exception will be raised
233 -- but not handled.
234 --
235 -- Developer Implementation Notes:
236 -- Any pre-processing required before the insert dml is issued should be
237 -- coded within this procedure. As stated above, a good example is the
238 -- generation of a primary key number via a corresponding sequence.
239 -- It is important to note that any 3rd party maintenance should be reviewed
240 -- before placing in this procedure.
241 --
242 -- Access Status:
243 -- Internal Table Handler Use Only.
244 --
245 -- {End Of Comments}
246 -- ----------------------------------------------------------------------------
247 Procedure pre_insert(p_rec in out NOCOPY pe_pei_shd.g_rec_type) is
248 --
249 l_proc varchar2(72) := g_package||'pre_insert';
250 --
251 Cursor C_Sel1 is select per_people_extra_info_s.nextval from sys.dual;
252 --
253 Begin
254 hr_utility.set_location('Entering:'||l_proc, 5);
255 --
256 --
257 -- Select the next sequence number
258 --
259 Open C_Sel1;
260 Fetch C_Sel1 Into p_rec.person_extra_info_id;
261 Close C_Sel1;
262 --
263 hr_utility.set_location(' Leaving:'||l_proc, 10);
264 End pre_insert;
265 --
266 -- ----------------------------------------------------------------------------
267 -- |-----------------------------< post_insert >------------------------------|
268 -- ----------------------------------------------------------------------------
269 -- {Start Of Comments}
270 --
271 -- Description:
272 -- This private procedure contains any processing which is required after the
273 -- insert dml.
274 --
275 -- Pre Conditions:
276 -- This is an internal procedure which is called from the ins procedure.
277 --
278 -- In Parameters:
279 -- A Pl/Sql record structre.
280 --
281 -- Post Success:
282 -- Processing continues.
283 --
284 -- Post Failure:
285 -- If an error has occurred, an error message and exception will be raised
286 -- but not handled.
287 --
288 -- Developer Implementation Notes:
289 -- Any post-processing required after the insert dml is issued should be
290 -- coded within this procedure. It is important to note that any 3rd party
291 -- maintenance should be reviewed before placing in this procedure.
292 --
293 -- Access Status:
294 -- Internal Table Handler Use Only.
295 --
296 -- {End Of Comments}
297 -- ----------------------------------------------------------------------------
298 Procedure post_insert(p_rec in pe_pei_shd.g_rec_type) is
299 --
300 l_proc varchar2(72) := g_package||'post_insert';
301 --
302 Begin
303 hr_utility.set_location('Entering:'||l_proc, 5);
304 --
305 -- This is a hook point and the user hook for post_insert is called here.
306 --
307 begin
308 pe_pei_rki.after_insert (
309 p_person_extra_info_id => p_rec.person_extra_info_id ,
310 p_person_id => p_rec.person_id ,
311 p_information_type => p_rec.information_type ,
312 p_request_id => p_rec.request_id ,
313 p_program_application_id => p_rec.program_application_id ,
314 p_program_id => p_rec.program_id ,
315 p_program_update_date => p_rec.program_update_date ,
316 p_pei_attribute_category => p_rec.pei_attribute_category ,
317 p_pei_attribute1 => p_rec.pei_attribute1 ,
318 p_pei_attribute2 => p_rec.pei_attribute2 ,
319 p_pei_attribute3 => p_rec.pei_attribute3 ,
320 p_pei_attribute4 => p_rec.pei_attribute4 ,
321 p_pei_attribute5 => p_rec.pei_attribute5 ,
322 p_pei_attribute6 => p_rec.pei_attribute6 ,
323 p_pei_attribute7 => p_rec.pei_attribute7 ,
324 p_pei_attribute8 => p_rec.pei_attribute8 ,
325 p_pei_attribute9 => p_rec.pei_attribute9 ,
326 p_pei_attribute10 => p_rec.pei_attribute10 ,
327 p_pei_attribute11 => p_rec.pei_attribute11 ,
328 p_pei_attribute12 => p_rec.pei_attribute12 ,
329 p_pei_attribute13 => p_rec.pei_attribute13 ,
330 p_pei_attribute14 => p_rec.pei_attribute14 ,
331 p_pei_attribute15 => p_rec.pei_attribute15 ,
332 p_pei_attribute16 => p_rec.pei_attribute16 ,
333 p_pei_attribute17 => p_rec.pei_attribute17 ,
334 p_pei_attribute18 => p_rec.pei_attribute18 ,
335 p_pei_attribute19 => p_rec.pei_attribute19 ,
336 p_pei_attribute20 => p_rec.pei_attribute20 ,
337 p_pei_information_category => p_rec.pei_information_category,
338 p_pei_information1 => p_rec.pei_information1 ,
339 p_pei_information2 => p_rec.pei_information2 ,
340 p_pei_information3 => p_rec.pei_information3 ,
341 p_pei_information4 => p_rec.pei_information4 ,
342 p_pei_information5 => p_rec.pei_information5 ,
343 p_pei_information6 => p_rec.pei_information6 ,
344 p_pei_information7 => p_rec.pei_information7 ,
345 p_pei_information8 => p_rec.pei_information8 ,
346 p_pei_information9 => p_rec.pei_information9 ,
347 p_pei_information10 => p_rec.pei_information10 ,
348 p_pei_information11 => p_rec.pei_information11 ,
349 p_pei_information12 => p_rec.pei_information12 ,
350 p_pei_information13 => p_rec.pei_information13 ,
351 p_pei_information14 => p_rec.pei_information14 ,
352 p_pei_information15 => p_rec.pei_information15 ,
353 p_pei_information16 => p_rec.pei_information16 ,
354 p_pei_information17 => p_rec.pei_information17 ,
355 p_pei_information18 => p_rec.pei_information18 ,
356 p_pei_information19 => p_rec.pei_information19 ,
357 p_pei_information20 => p_rec.pei_information20 ,
358 p_pei_information21 => p_rec.pei_information21 ,
359 p_pei_information22 => p_rec.pei_information22 ,
360 p_pei_information23 => p_rec.pei_information23 ,
361 p_pei_information24 => p_rec.pei_information24 ,
362 p_pei_information25 => p_rec.pei_information25 ,
363 p_pei_information26 => p_rec.pei_information26 ,
364 p_pei_information27 => p_rec.pei_information27 ,
365 p_pei_information28 => p_rec.pei_information28 ,
366 p_pei_information29 => p_rec.pei_information29 ,
367 p_pei_information30 => p_rec.pei_information30
368 );
369 exception
370 when hr_api.cannot_find_prog_unit then
371 hr_api.cannot_find_prog_unit_error
372 ( p_module_name => 'PER_PEOPLE_EXTRA_INFO'
373 ,p_hook_type => 'AI'
374 );
375 end;
376 --
377 -- End of API User Hook for post_insert.
378 --
379 --
380 if p_rec.INFORMATION_TYPE = 'PQH_ROLE_USERS' then
381 if nvl(p_rec.PEI_INFORMATION5,'N') = 'Y' then
382 declare
383 l_user_name varchar2(50);
384 l_start_date date;
385 l_expiration_date date;
386 cursor c1 is
387 select usr.user_name, usr.start_date, nvl(usr.end_date, hr_general.end_of_time)
388 from fnd_user usr
389 where usr.employee_id = p_rec.person_id;
390 begin
391 open c1;
392 fetch c1 into l_user_name, l_start_date, l_expiration_date;
393 if c1%found then
394 close c1;
395 WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system => 'PER',
396 p_user_orig_system_id => p_rec.person_id,
397 p_role_orig_system => 'PQH_ROLE',
398 p_role_orig_system_id => p_rec.pei_information3,
399 p_start_date => l_start_date,
400 p_expiration_date => l_expiration_date);
401 else
402 close c1;
403 end if;
404 end;
405 end if;
406 end if;
407 --
408 hr_utility.set_location(' Leaving:'||l_proc, 10);
409 End post_insert;
410 --
411 -- ----------------------------------------------------------------------------
412 -- |---------------------------------< ins >----------------------------------|
413 -- ----------------------------------------------------------------------------
414 Procedure ins
415 (
416 p_rec in out NOCOPY pe_pei_shd.g_rec_type,
417 p_validate in boolean default false
418 ) is
419 --
420 l_proc varchar2(72) := g_package||'ins';
421 --
422 Begin
423 hr_utility.set_location('Entering:'||l_proc, 5);
424 --
425 -- Determine if the business process is to be validated.
426 --
427 If p_validate then
428 --
429 -- Issue the savepoint.
430 --
431 SAVEPOINT ins_pe_pei;
432 End If;
433 --
434 -- Call the supporting insert validate operations
435 --
436 pe_pei_bus.insert_validate(p_rec);
437 --
438 -- Call the supporting pre-insert operation
439 --
440 pre_insert(p_rec);
441 --
442 -- Insert the row
443 --
444 insert_dml(p_rec);
445 --
446 -- Call the supporting post-insert operation
447 --
448 post_insert(p_rec);
449 --
450 -- If we are validating then raise the Validate_Enabled exception
451 --
452 If p_validate then
453 Raise HR_Api.Validate_Enabled;
454 End If;
455 --
456 hr_utility.set_location(' Leaving:'||l_proc, 10);
457 Exception
458 When HR_Api.Validate_Enabled Then
459 --
460 -- As the Validate_Enabled exception has been raised
461 -- we must rollback to the savepoint
462 --
463 ROLLBACK TO ins_pe_pei;
464 end ins;
465 --
466 -- ----------------------------------------------------------------------------
467 -- |---------------------------------< ins >----------------------------------|
468 -- ----------------------------------------------------------------------------
469 Procedure ins
470 (
471 p_person_extra_info_id out NOCOPY number,
472 p_person_id in number,
473 p_information_type in varchar2,
474 p_request_id in number default null,
475 p_program_application_id in number default null,
476 p_program_id in number default null,
477 p_program_update_date in date default null,
478 p_pei_attribute_category in varchar2 default null,
479 p_pei_attribute1 in varchar2 default null,
480 p_pei_attribute2 in varchar2 default null,
481 p_pei_attribute3 in varchar2 default null,
482 p_pei_attribute4 in varchar2 default null,
483 p_pei_attribute5 in varchar2 default null,
484 p_pei_attribute6 in varchar2 default null,
485 p_pei_attribute7 in varchar2 default null,
486 p_pei_attribute8 in varchar2 default null,
487 p_pei_attribute9 in varchar2 default null,
488 p_pei_attribute10 in varchar2 default null,
489 p_pei_attribute11 in varchar2 default null,
490 p_pei_attribute12 in varchar2 default null,
491 p_pei_attribute13 in varchar2 default null,
492 p_pei_attribute14 in varchar2 default null,
493 p_pei_attribute15 in varchar2 default null,
494 p_pei_attribute16 in varchar2 default null,
495 p_pei_attribute17 in varchar2 default null,
496 p_pei_attribute18 in varchar2 default null,
497 p_pei_attribute19 in varchar2 default null,
498 p_pei_attribute20 in varchar2 default null,
499 p_pei_information_category in varchar2 default null,
500 p_pei_information1 in varchar2 default null,
501 p_pei_information2 in varchar2 default null,
502 p_pei_information3 in varchar2 default null,
503 p_pei_information4 in varchar2 default null,
504 p_pei_information5 in varchar2 default null,
505 p_pei_information6 in varchar2 default null,
506 p_pei_information7 in varchar2 default null,
507 p_pei_information8 in varchar2 default null,
508 p_pei_information9 in varchar2 default null,
509 p_pei_information10 in varchar2 default null,
510 p_pei_information11 in varchar2 default null,
511 p_pei_information12 in varchar2 default null,
512 p_pei_information13 in varchar2 default null,
513 p_pei_information14 in varchar2 default null,
514 p_pei_information15 in varchar2 default null,
515 p_pei_information16 in varchar2 default null,
516 p_pei_information17 in varchar2 default null,
517 p_pei_information18 in varchar2 default null,
518 p_pei_information19 in varchar2 default null,
519 p_pei_information20 in varchar2 default null,
520 p_pei_information21 in varchar2 default null,
521 p_pei_information22 in varchar2 default null,
522 p_pei_information23 in varchar2 default null,
523 p_pei_information24 in varchar2 default null,
524 p_pei_information25 in varchar2 default null,
525 p_pei_information26 in varchar2 default null,
526 p_pei_information27 in varchar2 default null,
527 p_pei_information28 in varchar2 default null,
528 p_pei_information29 in varchar2 default null,
529 p_pei_information30 in varchar2 default null,
530 p_object_version_number out NOCOPY number,
531 p_validate in boolean default false
532 ) is
533 --
534 l_rec pe_pei_shd.g_rec_type;
535 l_proc varchar2(72) := g_package||'ins';
536 --
537 Begin
538 hr_utility.set_location('Entering:'||l_proc, 5);
539 --
540 -- Call conversion function to turn arguments into the
541 -- p_rec structure.
542 --
543 l_rec :=
544 pe_pei_shd.convert_args
545 (
546 null,
547 p_person_id,
548 p_information_type,
549 p_request_id,
550 p_program_application_id,
551 p_program_id,
552 p_program_update_date,
553 p_pei_attribute_category,
554 p_pei_attribute1,
555 p_pei_attribute2,
556 p_pei_attribute3,
557 p_pei_attribute4,
558 p_pei_attribute5,
559 p_pei_attribute6,
560 p_pei_attribute7,
561 p_pei_attribute8,
562 p_pei_attribute9,
563 p_pei_attribute10,
564 p_pei_attribute11,
565 p_pei_attribute12,
566 p_pei_attribute13,
567 p_pei_attribute14,
568 p_pei_attribute15,
569 p_pei_attribute16,
570 p_pei_attribute17,
571 p_pei_attribute18,
572 p_pei_attribute19,
573 p_pei_attribute20,
574 p_pei_information_category,
575 p_pei_information1,
576 p_pei_information2,
577 p_pei_information3,
578 p_pei_information4,
579 p_pei_information5,
580 p_pei_information6,
581 p_pei_information7,
582 p_pei_information8,
583 p_pei_information9,
584 p_pei_information10,
585 p_pei_information11,
586 p_pei_information12,
587 p_pei_information13,
588 p_pei_information14,
589 p_pei_information15,
590 p_pei_information16,
591 p_pei_information17,
592 p_pei_information18,
593 p_pei_information19,
594 p_pei_information20,
595 p_pei_information21,
596 p_pei_information22,
597 p_pei_information23,
598 p_pei_information24,
599 p_pei_information25,
600 p_pei_information26,
601 p_pei_information27,
602 p_pei_information28,
603 p_pei_information29,
604 p_pei_information30,
605 null
606 );
607 --
608 -- Having converted the arguments into the pe_pei_rec
609 -- plsql record structure we call the corresponding record business process.
610 --
611 ins(l_rec, p_validate);
612 --
613 -- As the primary key argument(s)
614 -- are specified as an OUT's we must set these values.
615 --
616 p_person_extra_info_id := l_rec.person_extra_info_id;
617 p_object_version_number := l_rec.object_version_number;
618 --
619 hr_utility.set_location(' Leaving:'||l_proc, 10);
620 End ins;
621 --
622 end pe_pei_ins;