[Home] [Help]
PACKAGE BODY: APPS.PER_REI_SHD
Source
1 Package Body per_rei_shd as
2 /* $Header: pereirhi.pkb 115.6 2003/10/07 19:01:25 ttagawa noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_rei_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 Function return_api_dml_status Return Boolean Is
14 --
15 Begin
16 --
17 Return (nvl(g_api_dml, false));
18 --
19 End return_api_dml_status;
20 --
21 -- ----------------------------------------------------------------------------
22 -- |---------------------------< constraint_error >---------------------------|
23 -- ----------------------------------------------------------------------------
24 Procedure constraint_error
25 (p_constraint_name in all_constraints.constraint_name%TYPE
26 ) Is
27 --
28 l_proc varchar2(72) := g_package||'constraint_error';
29 --
30 Begin
31 --
32 If (p_constraint_name = 'PER_CONTACT_EXTRA_INFO_FK1') Then
33 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
34 fnd_message.set_token('PROCEDURE', l_proc);
35 fnd_message.set_token('STEP','5');
36 fnd_message.raise_error;
37 ElsIf (p_constraint_name = 'PER_CONTACT_EXTRA_INFO_FK2') Then
38 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
39 fnd_message.set_token('PROCEDURE', l_proc);
40 fnd_message.set_token('STEP','10');
41 fnd_message.raise_error;
42 ElsIf (p_constraint_name = 'PER_CONTACT_EXTRA_INFO_PK') Then
43 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
44 fnd_message.set_token('PROCEDURE', l_proc);
45 fnd_message.set_token('STEP','15');
46 fnd_message.raise_error;
47 Else
48 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
49 fnd_message.set_token('PROCEDURE', l_proc);
50 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
51 fnd_message.raise_error;
52 End If;
53 --
54 End constraint_error;
55 --
56 -- ----------------------------------------------------------------------------
57 -- |-----------------------------< api_updating >-----------------------------|
58 -- ----------------------------------------------------------------------------
59 Function api_updating
60 (p_effective_date in date
61 ,p_contact_extra_info_id in number
62 ,p_object_version_number in number
63 ) Return Boolean Is
64 --
65 -- Cursor selects the 'current' row from the HR Schema
66 --
67 Cursor C_Sel1 is
68 select
69 contact_extra_info_id
70 ,effective_start_date
71 ,effective_end_date
72 ,contact_relationship_id
73 ,information_type
74 ,cei_information_category
75 ,cei_information1
76 ,cei_information2
77 ,cei_information3
78 ,cei_information4
79 ,cei_information5
80 ,cei_information6
81 ,cei_information7
82 ,cei_information8
83 ,cei_information9
84 ,cei_information10
85 ,cei_information11
86 ,cei_information12
87 ,cei_information13
88 ,cei_information14
89 ,cei_information15
90 ,cei_information16
91 ,cei_information17
92 ,cei_information18
93 ,cei_information19
94 ,cei_information20
95 ,cei_information21
96 ,cei_information22
97 ,cei_information23
98 ,cei_information24
99 ,cei_information25
100 ,cei_information26
101 ,cei_information27
102 ,cei_information28
103 ,cei_information29
104 ,cei_information30
105 ,cei_attribute_category
106 ,cei_attribute1
107 ,cei_attribute2
108 ,cei_attribute3
109 ,cei_attribute4
110 ,cei_attribute5
111 ,cei_attribute6
112 ,cei_attribute7
113 ,cei_attribute8
114 ,cei_attribute9
115 ,cei_attribute10
116 ,cei_attribute11
117 ,cei_attribute12
118 ,cei_attribute13
119 ,cei_attribute14
120 ,cei_attribute15
121 ,cei_attribute16
122 ,cei_attribute17
123 ,cei_attribute18
124 ,cei_attribute19
125 ,cei_attribute20
126 ,object_version_number
127 ,request_id
128 ,program_application_id
129 ,program_id
130 ,program_update_date
131 from per_contact_extra_info_f
132 where contact_extra_info_id = p_contact_extra_info_id
133 and p_effective_date
134 between effective_start_date and effective_end_date;
135 --
136 l_fct_ret boolean;
137 --
138 Begin
139 --
140 If (p_effective_date is null or
141 p_contact_extra_info_id is null or
142 p_object_version_number is null) Then
143 --
144 -- One of the primary key arguments is null therefore we must
145 -- set the returning function value to false
146 --
147 l_fct_ret := false;
148 Else
149 If (p_contact_extra_info_id =
150 per_rei_shd.g_old_rec.contact_extra_info_id and
151 p_object_version_number =
152 per_rei_shd.g_old_rec.object_version_number
153 ) Then
154 --
155 -- The g_old_rec is current therefore we must
156 -- set the returning function to true
157 --
158 l_fct_ret := true;
159 Else
160 --
161 -- Select the current row
162 --
163 Open C_Sel1;
164 Fetch C_Sel1 Into per_rei_shd.g_old_rec;
165 If C_Sel1%notfound Then
166 Close C_Sel1;
167 --
168 -- The primary key is invalid therefore we must error
169 --
170 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
171 fnd_message.raise_error;
172 End If;
173 Close C_Sel1;
174 If (p_object_version_number
175 <> per_rei_shd.g_old_rec.object_version_number) Then
176 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
177 fnd_message.raise_error;
178 End If;
179 l_fct_ret := true;
180 End If;
181 End If;
182 Return (l_fct_ret);
183 --
184 End api_updating;
185 --
186 -- ----------------------------------------------------------------------------
187 -- |---------------------------< find_dt_upd_modes >--------------------------|
188 -- ----------------------------------------------------------------------------
189 Procedure find_dt_upd_modes
190 (p_effective_date in date
191 ,p_base_key_value in number
192 ,p_correction out nocopy boolean
193 ,p_update out nocopy boolean
194 ,p_update_override out nocopy boolean
195 ,p_update_change_insert out nocopy boolean
196 ) is
197 --
198 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
199 --
200 Begin
201 hr_utility.set_location('Entering:'||l_proc, 5);
202 --
203 -- Call the corresponding datetrack api
204 --
205 dt_api.find_dt_upd_modes
206 (p_effective_date => p_effective_date
207 ,p_base_table_name => 'per_contact_extra_info_f'
208 ,p_base_key_column => 'contact_extra_info_id'
209 ,p_base_key_value => p_base_key_value
210 ,p_correction => p_correction
211 ,p_update => p_update
212 ,p_update_override => p_update_override
213 ,p_update_change_insert => p_update_change_insert
214 );
215 --
216 hr_utility.set_location(' Leaving:'||l_proc, 10);
217 End find_dt_upd_modes;
218 --
219 -- ----------------------------------------------------------------------------
220 -- |---------------------------< find_dt_del_modes >--------------------------|
221 -- ----------------------------------------------------------------------------
222 Procedure find_dt_del_modes
223 (p_effective_date in date
224 ,p_base_key_value in number
225 ,p_zap out nocopy boolean
226 ,p_delete out nocopy boolean
227 ,p_future_change out nocopy boolean
228 ,p_delete_next_change out nocopy boolean
229 ) is
230 --
231 l_proc varchar2(72) := g_package||'find_dt_del_modes';
232 --
233 --
234 Begin
235 hr_utility.set_location('Entering:'||l_proc, 5);
236 --
237 -- Call the corresponding datetrack api
238 --
239 dt_api.find_dt_del_modes
240 (p_effective_date => p_effective_date
241 ,p_base_table_name => 'per_contact_extra_info_f'
242 ,p_base_key_column => 'contact_extra_info_id'
243 ,p_base_key_value => p_base_key_value
244 ,p_zap => p_zap
245 ,p_delete => p_delete
246 ,p_future_change => p_future_change
247 ,p_delete_next_change => p_delete_next_change
248 );
249 --
250 hr_utility.set_location(' Leaving:'||l_proc, 10);
251 End find_dt_del_modes;
252 --
253 -- ----------------------------------------------------------------------------
254 -- |-----------------------< upd_effective_end_date >-------------------------|
255 -- ----------------------------------------------------------------------------
256 Procedure upd_effective_end_date
257 (p_effective_date in date
258 ,p_base_key_value in number
259 ,p_new_effective_end_date in date
260 ,p_validation_start_date in date
261 ,p_validation_end_date in date
262 ,p_object_version_number out nocopy number
263 ) is
264 --
265 l_proc varchar2(72) := g_package||'upd_effective_end_date';
266 l_object_version_number number;
267 --
268 Begin
269 hr_utility.set_location('Entering:'||l_proc, 5);
270 --
271 -- Because we are updating a row we must get the next object
272 -- version number.
273 --
274 l_object_version_number :=
275 dt_api.get_object_version_number
276 (p_base_table_name => 'per_contact_extra_info_f'
277 ,p_base_key_column => 'contact_extra_info_id'
278 ,p_base_key_value => p_base_key_value
279 );
280 --
281 hr_utility.set_location(l_proc, 10);
282 per_rei_shd.g_api_dml := true; -- Set the api dml status
283 --
284 -- Update the specified datetrack row setting the effective
285 -- end date to the specified new effective end date.
286 --
287 update per_contact_extra_info_f t
288 set t.effective_end_date = p_new_effective_end_date
289 , t.object_version_number = l_object_version_number
290 where t.contact_extra_info_id = p_base_key_value
291 and p_effective_date
292 between t.effective_start_date and t.effective_end_date;
293 --
294 per_rei_shd.g_api_dml := false; -- Unset the api dml status
295 p_object_version_number := l_object_version_number;
296 hr_utility.set_location(' Leaving:'||l_proc, 15);
297 --
298 Exception
299 When Others Then
300 per_rei_shd.g_api_dml := false; -- Unset the api dml status
301 Raise;
302 --
303 End upd_effective_end_date;
304 --
305 -- ----------------------------------------------------------------------------
306 -- |---------------------------------< lck >----------------------------------|
307 -- ----------------------------------------------------------------------------
308 Procedure lck
309 (p_effective_date in date
310 ,p_datetrack_mode in varchar2
311 ,p_contact_extra_info_id in number
312 ,p_object_version_number in number
313 ,p_validation_start_date out nocopy date
314 ,p_validation_end_date out nocopy date
315 ) is
316 --
317 l_proc varchar2(72) := g_package||'lck';
318 l_validation_start_date date;
319 l_validation_end_date date;
320 l_argument varchar2(30);
321 l_person_id number;
322 --
323 -- Cursor C_Sel1 selects the current locked row as of session date
324 -- ensuring that the object version numbers match.
325 --
326 Cursor C_Sel1 is
327 select
328 contact_extra_info_id
329 ,effective_start_date
330 ,effective_end_date
331 ,contact_relationship_id
332 ,information_type
333 ,cei_information_category
334 ,cei_information1
335 ,cei_information2
336 ,cei_information3
337 ,cei_information4
338 ,cei_information5
339 ,cei_information6
340 ,cei_information7
341 ,cei_information8
342 ,cei_information9
343 ,cei_information10
344 ,cei_information11
345 ,cei_information12
346 ,cei_information13
347 ,cei_information14
348 ,cei_information15
349 ,cei_information16
350 ,cei_information17
351 ,cei_information18
352 ,cei_information19
353 ,cei_information20
354 ,cei_information21
355 ,cei_information22
356 ,cei_information23
357 ,cei_information24
358 ,cei_information25
359 ,cei_information26
360 ,cei_information27
361 ,cei_information28
362 ,cei_information29
363 ,cei_information30
364 ,cei_attribute_category
365 ,cei_attribute1
366 ,cei_attribute2
367 ,cei_attribute3
368 ,cei_attribute4
369 ,cei_attribute5
370 ,cei_attribute6
371 ,cei_attribute7
372 ,cei_attribute8
373 ,cei_attribute9
374 ,cei_attribute10
375 ,cei_attribute11
376 ,cei_attribute12
377 ,cei_attribute13
378 ,cei_attribute14
379 ,cei_attribute15
380 ,cei_attribute16
381 ,cei_attribute17
382 ,cei_attribute18
383 ,cei_attribute19
384 ,cei_attribute20
385 ,object_version_number
386 ,request_id
387 ,program_application_id
388 ,program_id
389 ,program_update_date
390 from per_contact_extra_info_f
391 where contact_extra_info_id = p_contact_extra_info_id
392 and p_effective_date
393 between effective_start_date and effective_end_date
394 for update nowait;
395 --
396 --
397 --
398 Begin
399 hr_utility.set_location('Entering:'||l_proc, 5);
400 --
401 -- Ensure that all the mandatory arguments are not null
402 --
403 hr_api.mandatory_arg_error(p_api_name => l_proc
404 ,p_argument => 'effective_date'
405 ,p_argument_value => p_effective_date
406 );
407 --
408 hr_api.mandatory_arg_error(p_api_name => l_proc
409 ,p_argument => 'datetrack_mode'
410 ,p_argument_value => p_datetrack_mode
411 );
412 --
413 hr_api.mandatory_arg_error(p_api_name => l_proc
414 ,p_argument => 'contact_extra_info_id'
415 ,p_argument_value => p_contact_extra_info_id
416 );
417 --
418 hr_api.mandatory_arg_error(p_api_name => l_proc
419 ,p_argument => 'object_version_number'
420 ,p_argument_value => p_object_version_number
421 );
422 --
423 -- Check to ensure the datetrack mode is not INSERT.
424 --
425 If (p_datetrack_mode <> hr_api.g_insert) then
426 --
427 -- We must select and lock the current row.
428 --
429 Open C_Sel1;
433 --
430 Fetch C_Sel1 Into per_rei_shd.g_old_rec;
431 If C_Sel1%notfound then
432 Close C_Sel1;
434 -- The primary key is invalid therefore we must error
435 --
436 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
437 fnd_message.raise_error;
438 End If;
439 Close C_Sel1;
440 If (p_object_version_number
441 <> per_rei_shd.g_old_rec.object_version_number) Then
442 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
443 fnd_message.raise_error;
444 End If;
445 --
446 -- PER_CONTACT_EXTRA_INFO_F is a little bit different from the other datetrack tables.
447 -- The parent of this table is PER_ALL_PEOPLE_F, but does not have column "PERSON_ID".
448 -- So we need to derive PERSON_ID from PER_CONTACT_RELATIONSHIPS table to pass
449 -- it to validate_dt_mode.
450 --
451 select person_id
452 into l_person_id
453 from per_contact_relationships
454 where contact_relationship_id = g_old_rec.contact_relationship_id;
455 --
456 -- Validate the datetrack mode mode getting the validation start
457 -- and end dates for the specified datetrack operation.
458 --
459 dt_api.validate_dt_mode
460 (p_effective_date => p_effective_date
461 ,p_datetrack_mode => p_datetrack_mode
462 ,p_base_table_name => 'per_contact_extra_info_f'
463 ,p_base_key_column => 'contact_extra_info_id'
464 ,p_base_key_value => p_contact_extra_info_id
465 ,p_parent_table_name1 => 'per_all_people_f'
466 ,p_parent_key_column1 => 'person_id'
467 ,p_parent_key_value1 => l_person_id
468 ,p_enforce_foreign_locking => true
469 ,p_validation_start_date => l_validation_start_date
470 ,p_validation_end_date => l_validation_end_date
471 );
472 Else
473 --
474 -- We are doing a datetrack 'INSERT' which is illegal within this
475 -- procedure therefore we must error (note: to lck on insert the
476 -- private procedure ins_lck should be called).
477 --
478 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
479 fnd_message.set_token('PROCEDURE', l_proc);
480 fnd_message.set_token('STEP','20');
481 fnd_message.raise_error;
482 End If;
483 --
484 -- Set the validation start and end date OUT arguments
485 --
486 p_validation_start_date := l_validation_start_date;
487 p_validation_end_date := l_validation_end_date;
488 --
489 hr_utility.set_location(' Leaving:'||l_proc, 30);
490 --
491 -- We need to trap the ORA LOCK exception
492 --
493 Exception
494 When HR_Api.Object_Locked then
495 --
496 -- The object is locked therefore we need to supply a meaningful
497 -- error message.
498 --
499 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
500 fnd_message.set_token('TABLE_NAME', 'per_contact_extra_info_f');
501 fnd_message.raise_error;
502 End lck;
503 --
504 -- ----------------------------------------------------------------------------
505 -- |-----------------------------< convert_args >-----------------------------|
506 -- ----------------------------------------------------------------------------
507 Function convert_args
508 (p_contact_extra_info_id in number
509 ,p_effective_start_date in date
510 ,p_effective_end_date in date
511 ,p_contact_relationship_id in number
512 ,p_information_type in varchar2
513 ,p_cei_information_category in varchar2
514 ,p_cei_information1 in varchar2
515 ,p_cei_information2 in varchar2
516 ,p_cei_information3 in varchar2
517 ,p_cei_information4 in varchar2
518 ,p_cei_information5 in varchar2
519 ,p_cei_information6 in varchar2
520 ,p_cei_information7 in varchar2
521 ,p_cei_information8 in varchar2
522 ,p_cei_information9 in varchar2
523 ,p_cei_information10 in varchar2
524 ,p_cei_information11 in varchar2
525 ,p_cei_information12 in varchar2
526 ,p_cei_information13 in varchar2
527 ,p_cei_information14 in varchar2
528 ,p_cei_information15 in varchar2
529 ,p_cei_information16 in varchar2
530 ,p_cei_information17 in varchar2
531 ,p_cei_information18 in varchar2
532 ,p_cei_information19 in varchar2
533 ,p_cei_information20 in varchar2
534 ,p_cei_information21 in varchar2
535 ,p_cei_information22 in varchar2
536 ,p_cei_information23 in varchar2
537 ,p_cei_information24 in varchar2
538 ,p_cei_information25 in varchar2
539 ,p_cei_information26 in varchar2
540 ,p_cei_information27 in varchar2
541 ,p_cei_information28 in varchar2
542 ,p_cei_information29 in varchar2
543 ,p_cei_information30 in varchar2
544 ,p_cei_attribute_category in varchar2
545 ,p_cei_attribute1 in varchar2
546 ,p_cei_attribute2 in varchar2
547 ,p_cei_attribute3 in varchar2
551 ,p_cei_attribute7 in varchar2
548 ,p_cei_attribute4 in varchar2
549 ,p_cei_attribute5 in varchar2
550 ,p_cei_attribute6 in varchar2
552 ,p_cei_attribute8 in varchar2
553 ,p_cei_attribute9 in varchar2
554 ,p_cei_attribute10 in varchar2
555 ,p_cei_attribute11 in varchar2
556 ,p_cei_attribute12 in varchar2
557 ,p_cei_attribute13 in varchar2
558 ,p_cei_attribute14 in varchar2
559 ,p_cei_attribute15 in varchar2
560 ,p_cei_attribute16 in varchar2
561 ,p_cei_attribute17 in varchar2
562 ,p_cei_attribute18 in varchar2
563 ,p_cei_attribute19 in varchar2
564 ,p_cei_attribute20 in varchar2
565 ,p_object_version_number in number
566 ,p_request_id in number
567 ,p_program_application_id in number
568 ,p_program_id in number
569 ,p_program_update_date in date
570 )
571 Return g_rec_type is
572 --
573 l_rec g_rec_type;
574 --
575 Begin
576 --
577 -- Convert arguments into local l_rec structure.
578 --
579 l_rec.contact_extra_info_id := p_contact_extra_info_id;
580 l_rec.effective_start_date := p_effective_start_date;
581 l_rec.effective_end_date := p_effective_end_date;
582 l_rec.contact_relationship_id := p_contact_relationship_id;
583 l_rec.information_type := p_information_type;
584 l_rec.cei_information_category := p_cei_information_category;
585 l_rec.cei_information1 := p_cei_information1;
586 l_rec.cei_information2 := p_cei_information2;
587 l_rec.cei_information3 := p_cei_information3;
588 l_rec.cei_information4 := p_cei_information4;
589 l_rec.cei_information5 := p_cei_information5;
590 l_rec.cei_information6 := p_cei_information6;
591 l_rec.cei_information7 := p_cei_information7;
592 l_rec.cei_information8 := p_cei_information8;
593 l_rec.cei_information9 := p_cei_information9;
594 l_rec.cei_information10 := p_cei_information10;
595 l_rec.cei_information11 := p_cei_information11;
596 l_rec.cei_information12 := p_cei_information12;
597 l_rec.cei_information13 := p_cei_information13;
598 l_rec.cei_information14 := p_cei_information14;
599 l_rec.cei_information15 := p_cei_information15;
600 l_rec.cei_information16 := p_cei_information16;
601 l_rec.cei_information17 := p_cei_information17;
602 l_rec.cei_information18 := p_cei_information18;
603 l_rec.cei_information19 := p_cei_information19;
604 l_rec.cei_information20 := p_cei_information20;
605 l_rec.cei_information21 := p_cei_information21;
606 l_rec.cei_information22 := p_cei_information22;
607 l_rec.cei_information23 := p_cei_information23;
608 l_rec.cei_information24 := p_cei_information24;
609 l_rec.cei_information25 := p_cei_information25;
610 l_rec.cei_information26 := p_cei_information26;
611 l_rec.cei_information27 := p_cei_information27;
612 l_rec.cei_information28 := p_cei_information28;
613 l_rec.cei_information29 := p_cei_information29;
614 l_rec.cei_information30 := p_cei_information30;
615 l_rec.cei_attribute_category := p_cei_attribute_category;
616 l_rec.cei_attribute1 := p_cei_attribute1;
617 l_rec.cei_attribute2 := p_cei_attribute2;
618 l_rec.cei_attribute3 := p_cei_attribute3;
619 l_rec.cei_attribute4 := p_cei_attribute4;
620 l_rec.cei_attribute5 := p_cei_attribute5;
621 l_rec.cei_attribute6 := p_cei_attribute6;
622 l_rec.cei_attribute7 := p_cei_attribute7;
623 l_rec.cei_attribute8 := p_cei_attribute8;
624 l_rec.cei_attribute9 := p_cei_attribute9;
625 l_rec.cei_attribute10 := p_cei_attribute10;
626 l_rec.cei_attribute11 := p_cei_attribute11;
627 l_rec.cei_attribute12 := p_cei_attribute12;
628 l_rec.cei_attribute13 := p_cei_attribute13;
629 l_rec.cei_attribute14 := p_cei_attribute14;
630 l_rec.cei_attribute15 := p_cei_attribute15;
631 l_rec.cei_attribute16 := p_cei_attribute16;
632 l_rec.cei_attribute17 := p_cei_attribute17;
633 l_rec.cei_attribute18 := p_cei_attribute18;
634 l_rec.cei_attribute19 := p_cei_attribute19;
635 l_rec.cei_attribute20 := p_cei_attribute20;
636 l_rec.object_version_number := p_object_version_number;
637 l_rec.request_id := p_request_id;
638 l_rec.program_application_id := p_program_application_id;
639 l_rec.program_id := p_program_id;
640 l_rec.program_update_date := p_program_update_date;
641 --
642 -- Return the plsql record structure.
643 --
644 Return(l_rec);
645 --
646 End convert_args;
647 --
648 -- ----------------------------------------------------------------------------
652 p_flag IN BOOLEAN) AS
649 -- |--------------------------< set_called_from_form >------------------------|
650 -- ----------------------------------------------------------------------------
651 PROCEDURE set_called_from_form(
653 BEGIN
654 g_called_from_form := p_flag;
655 END set_called_from_form;
656 --
657 end per_rei_shd;