[Home] [Help]
PACKAGE BODY: APPS.PER_PTU_SHD
Source
1 Package Body per_ptu_shd as
2 /* $Header: pepturhi.pkb 120.0 2005/05/31 15:57:51 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_ptu_shd.'; -- Global package name
9
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
15 --
16 l_proc varchar2(72) := g_package||'constraint_error';
17 --
18 Begin
19 hr_utility.set_location('Entering:'||l_proc, 5);
20 --
21 If (p_constraint_name = 'PER_PERSON_TYPE_USAGES_F_FK1') Then
22 hr_utility.set_message(801, 'HR_52361_PTU_INVALID_PERSON_ID');
23 hr_utility.set_message_token('PROCEDURE', l_proc);
24 hr_utility.set_message_token('STEP','10');
25 hr_utility.raise_error;
26 elsif (p_constraint_name = 'PER_PERSON_TYPE_USAGES_F_UK1') Then
27 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
28 hr_utility.set_message_token('PROCEDURE', l_proc);
29 hr_utility.set_message_token('STEP','15');
30 hr_utility.raise_error;
31 elsif (p_constraint_name = 'PER_PERSON_TYPE_USAGES_F_FK2') Then
32 hr_utility.set_message(801, 'HR_52362_PTU_INV_PER_TYPE_ID');
33 hr_utility.set_message_token('PROCEDURE', l_proc);
34 hr_utility.set_message_token('STEP','20');
35 hr_utility.raise_error;
36 Else
37 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
38 hr_utility.set_message_token('PROCEDURE', l_proc);
39 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
40 hr_utility.raise_error;
41 End If;
42 --
43 hr_utility.set_location(' Leaving:'||l_proc, 10);
44 End constraint_error;
45 --
46 -- ----------------------------------------------------------------------------
47 -- |-----------------------------< api_updating >-----------------------------|
48 -- ----------------------------------------------------------------------------
49 Function api_updating
50 (p_effective_date in date,
51 p_person_type_usage_id in number,
52 p_object_version_number in number
53 ) Return Boolean Is
54 --
55 --
56 -- Cursor selects the 'current' row from the HR Schema
57 --
58 Cursor C_Sel1 is
59 select
60 person_type_usage_id,
61 person_id,
62 person_type_id,
63 effective_start_date,
64 effective_end_date,
65 object_version_number,
66 request_id,
67 program_application_id,
68 program_id,
69 program_update_date,
70 attribute_category,
71 attribute1,
72 attribute2,
73 attribute3,
74 attribute4,
75 attribute5,
76 attribute6,
77 attribute7,
78 attribute8,
79 attribute9,
80 attribute10,
81 attribute11,
82 attribute12,
83 attribute13,
84 attribute14,
85 attribute15,
86 attribute16,
87 attribute17,
88 attribute18,
89 attribute19,
90 attribute20,
91 attribute21,
92 attribute22,
93 attribute23,
94 attribute24,
95 attribute25,
96 attribute26,
97 attribute27,
98 attribute28,
99 attribute29,
100 attribute30
101 from per_person_type_usages_f
102 where person_type_usage_id = p_person_type_usage_id
103 and p_effective_date
104 between effective_start_date and effective_end_date;
105 --
106 l_proc varchar2(72) := g_package||'api_updating';
107 l_fct_ret boolean;
108 --
109 Begin
110 hr_utility.set_location('Entering:'||l_proc, 5);
111 --
112 If (p_effective_date is null or
113 p_person_type_usage_id is null or
114 p_object_version_number is null) Then
115 --
116 -- One of the primary key arguments is null therefore we must
117 -- set the returning function value to false
118 --
119 l_fct_ret := false;
120 Else
121 If (p_person_type_usage_id = g_old_rec.person_type_usage_id and
122 p_object_version_number = g_old_rec.object_version_number) Then
123 hr_utility.set_location(l_proc, 10);
124 --
125 -- The g_old_rec is current therefore we must
126 -- set the returning function to true
127 --
128 l_fct_ret := true;
129 Else
130 --
131 -- Select the current row
132 --
133 Open C_Sel1;
134 Fetch C_Sel1 Into g_old_rec;
135 If C_Sel1%notfound Then
136 Close C_Sel1;
137 --
138 -- The primary key is invalid therefore we must error
139 --
140 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
141 hr_utility.raise_error;
142 End If;
143 Close C_Sel1;
144 If (p_object_version_number <> g_old_rec.object_version_number) Then
145 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
146 hr_utility.raise_error;
147 End If;
148 hr_utility.set_location(l_proc, 15);
149 l_fct_ret := true;
150 End If;
151 End If;
152 hr_utility.set_location(' Leaving:'||l_proc, 20);
153 Return (l_fct_ret);
154 --
155 End api_updating;
156 --
157 -- ----------------------------------------------------------------------------
158 -- |--------------------------< find_dt_del_modes >---------------------------|
159 -- ----------------------------------------------------------------------------
160 Procedure find_dt_del_modes
161 (p_effective_date in date,
162 p_base_key_value in number,
163 p_zap out nocopy boolean,
164 p_delete out nocopy boolean,
165 p_future_change out nocopy boolean,
166 p_delete_next_change out nocopy boolean) is
167 --
168 l_proc varchar2(72) := g_package||'find_dt_del_modes';
169 --
170 l_parent_key_value1 number;
171 --
172 Cursor C_Sel1 Is
173 select ptu.person_id
174 from per_person_type_usages_f ptu
175 where ptu.person_type_usage_id = p_base_key_value
176 and p_effective_date between ptu.effective_start_date
177 and ptu.effective_end_date;
178 --
179 --
180 Begin
181 hr_utility.set_location('Entering:'||l_proc, 5);
182 Open C_Sel1;
183 Fetch C_Sel1 Into l_parent_key_value1;
184 If C_Sel1%notfound then
185 Close C_Sel1;
186 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
187 hr_utility.set_message_token('PROCEDURE', l_proc);
188 hr_utility.set_message_token('STEP','10');
189 hr_utility.raise_error;
190 End If;
191 Close C_Sel1;
192 --
193 -- Call the corresponding datetrack api
194 --
195 dt_api.find_dt_del_modes
196 (p_effective_date => p_effective_date,
197 p_base_table_name => 'per_person_type_usages_f',
198 p_base_key_column => 'person_type_usage_id',
199 p_base_key_value => p_base_key_value,
200 p_parent_table_name1 => 'per_people_f',
201 p_parent_key_column1 => 'person_id',
202 p_parent_key_value1 => l_parent_key_value1,
203 p_zap => p_zap,
204 p_delete => p_delete,
205 p_future_change => p_future_change,
206 p_delete_next_change => p_delete_next_change
207 );
208 --
209 -- Set the disallowed modes to false.
210 --
211 -- p_zap := false;
212 -- p_delete := false;
213 -- p_future_change := false;
214 -- p_delete_next_change := false;
215
216 if hr_person_type_usage_info.IsNonCoreHRPersonType
217 (p_base_key_value,
218 p_effective_date)
219 then
220 p_future_change := false;
221 p_delete_next_change := false;
222 else
223 if hr_person_type_usage_info.FutSysPerTypeChgExists
224 (p_base_key_value,
225 p_effective_date)
226 then
227 p_zap := false;
228 p_delete := false;
229 p_future_change := false;
230 p_delete_next_change := false;
231 else
232 p_zap := false;
233 p_delete := false;
234 -- p_future_change := true;
235 -- p_delete_next_change := true;
236 end if;
237 end if;
238 --
239 --
240 hr_utility.set_location(' Leaving:'||l_proc, 10);
241 End find_dt_del_modes;
242 --
243 -- ----------------------------------------------------------------------------
244 -- |--------------------------< find_dt_upd_modes >---------------------------|
245 -- ----------------------------------------------------------------------------
246 Procedure find_dt_upd_modes
247 (p_effective_date in date,
248 p_base_key_value in number,
249 p_correction out nocopy boolean,
250 p_update out nocopy boolean,
251 p_update_override out nocopy boolean,
252 p_update_change_insert out nocopy boolean) is
253 --
254 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
255 --
256 Begin
257 hr_utility.set_location('Entering:'||l_proc, 5);
258 --
259 -- Call the corresponding datetrack api
260 --
261 dt_api.find_dt_upd_modes
262 (p_effective_date => p_effective_date,
263 p_base_table_name => 'per_person_type_usages_f',
264 p_base_key_column => 'person_type_usage_id',
265 p_base_key_value => p_base_key_value,
266 p_correction => p_correction,
267 p_update => p_update,
268 p_update_override => p_update_override,
269 p_update_change_insert => p_update_change_insert
270 );
271 --
272 -- Set the disallowed modes to false.
273 --
274 -- p_update_override := false;
275 -- p_update_change_insert := false;
276 -- p_update := false;
277 --
278 if hr_person_type_usage_info.IsNonCoreHRPersonType
279 (p_base_key_value,
280 p_effective_date)
281 then
282 p_update_override := false;
283 p_update_change_insert := false;
284 p_update := false;
285 else
286 if hr_person_type_usage_info.FutSysPerTypeChgExists
287 (p_base_key_value,
288 p_effective_date)
289 then
290 p_update_override := false;
291 p_update := false;
292 end if;
293 end if;
294 --
295
296 hr_utility.set_location(' Leaving:'||l_proc, 10);
297 End find_dt_upd_modes;
298 --
299 -- ----------------------------------------------------------------------------
300 -- |------------------------< upd_effective_end_date >------------------------|
301 -- ----------------------------------------------------------------------------
302 Procedure upd_effective_end_date
303 (p_effective_date in date,
304 p_base_key_value in number,
305 p_new_effective_end_date in date,
306 p_validation_start_date in date,
307 p_validation_end_date in date,
308 p_object_version_number out nocopy number) is
309 --
310 l_proc varchar2(72) := g_package||'upd_effective_end_date';
311 l_object_version_number number;
312 --
313 -- Start of Fix for WWBUG 1408379
314 --
315 l_old ben_ptu_ler.g_ptu_ler_rec;
316 l_new ben_ptu_ler.g_ptu_ler_rec;
317 --
318 cursor c1 is
319 select *
320 from per_person_type_usages_f
321 where person_type_usage_id = p_base_key_value
322 and p_effective_date
323 between effective_start_date
324 and effective_end_date;
325 --
326 l_c1 c1%rowtype;
327 l_rows_found boolean := false;
328 --
329 -- End of Fix for WWBUG 1408379
330 --
331 --
332 Begin
333 hr_utility.set_location('Entering:'||l_proc, 5);
334 --
335 -- Because we are updating a row we must get the next object
336 -- version number.
337 --
338 l_object_version_number :=
339 dt_api.get_object_version_number
340 (p_base_table_name => 'per_person_type_usages_f',
341 p_base_key_column => 'person_type_usage_id',
342 p_base_key_value => p_base_key_value);
343 --
344 hr_utility.set_location(l_proc, 10);
345 --
346 --
347 -- Start of Fix for WWBUG 1408379
348 --
349 open c1;
350 --
351 fetch c1 into l_c1;
352 if c1%found then
353 --
354 l_rows_found := true;
355 --
356 end if;
357 --
358 close c1;
359 --
360 -- Update the specified datetrack row setting the effective
361 -- end date to the specified new effective end date.
362 --
363 update per_person_type_usages_f t
364 set t.effective_end_date = p_new_effective_end_date,
365 t.object_version_number = l_object_version_number
366 where t.person_type_usage_id = p_base_key_value
367 and p_effective_date
368 between t.effective_start_date and t.effective_end_date;
369 --
370 -- Start of Fix for WWBUG 1408379
371 --
372 if l_rows_found then
373 --
374 l_old.person_type_usage_id := l_c1.person_type_usage_id;
375 l_old.person_id := l_c1.person_id;
376 l_old.person_type_id := l_c1.person_type_id;
377 l_old.effective_start_date := l_c1.effective_start_date;
378 l_old.effective_end_date := l_c1.effective_end_date;
379 l_new.person_type_usage_id := l_c1.person_type_usage_id;
380 l_new.person_id := l_c1.person_id;
381 l_new.person_type_id := l_c1.person_type_id;
382 l_new.effective_start_date := l_c1.effective_start_date;
383 l_new.effective_end_date := p_new_effective_end_date;
384 --
385 ben_ptu_ler.ler_chk(p_old => l_old,
386 p_new => l_new,
387 p_effective_date => p_effective_date);
388 --
389 end if;
390 --
391 -- End of Fix for WWBUG 1408379
392 --
393 p_object_version_number := l_object_version_number;
394 hr_utility.set_location(' Leaving:'||l_proc, 15);
395 --
396 Exception
397 When Others Then
398 Raise;
399 End upd_effective_end_date;
400 --
401 -- ----------------------------------------------------------------------------
402 -- |---------------------------------< lck >----------------------------------|
403 -- ----------------------------------------------------------------------------
404 Procedure lck
405 (p_effective_date in date,
406 p_datetrack_mode in varchar2,
407 p_person_type_usage_id in number,
408 p_object_version_number in number,
409 p_validation_start_date out nocopy date,
410 p_validation_end_date out nocopy date) is
411 --
412 l_proc varchar2(72) := g_package||'lck';
413 l_validation_start_date date;
414 l_validation_end_date date;
415 l_object_invalid exception;
419 -- ensuring that the object version numbers match.
416 l_argument varchar2(30);
417 --
418 -- Cursor C_Sel1 selects the current locked row as of session date
420 --
421 Cursor C_Sel1 is
422 select
423 person_type_usage_id,
424 person_id,
425 person_type_id,
426 effective_start_date,
427 effective_end_date,
428 object_version_number,
429 request_id,
430 program_application_id,
431 program_id,
432 program_update_date,
433 attribute_category,
434 attribute1,
435 attribute2,
436 attribute3,
437 attribute4,
438 attribute5,
439 attribute6,
440 attribute7,
441 attribute8,
442 attribute9,
443 attribute10,
444 attribute11,
445 attribute12,
446 attribute13,
447 attribute14,
448 attribute15,
449 attribute16,
450 attribute17,
451 attribute18,
452 attribute19,
453 attribute20,
454 attribute21,
455 attribute22,
456 attribute23,
457 attribute24,
458 attribute25,
459 attribute26,
460 attribute27,
461 attribute28,
462 attribute29,
463 attribute30
464 from per_person_type_usages_f
465 where person_type_usage_id = p_person_type_usage_id
466 and p_effective_date
467 between effective_start_date and effective_end_date
468 for update nowait;
469 --
470 --
471 --
472 Begin
473 hr_utility.set_location('Entering:'||l_proc, 5);
474 --
475 -- Ensure that all the mandatory arguments are not null
476 --
477 hr_api.mandatory_arg_error(p_api_name => l_proc,
478 p_argument => 'effective_date',
479 p_argument_value => p_effective_date);
480 --
481 hr_api.mandatory_arg_error(p_api_name => l_proc,
482 p_argument => 'datetrack_mode',
483 p_argument_value => p_datetrack_mode);
484 --
485 hr_api.mandatory_arg_error(p_api_name => l_proc,
486 p_argument => 'person_type_usage_id',
487 p_argument_value => p_person_type_usage_id);
488 --
489 hr_api.mandatory_arg_error(p_api_name => l_proc,
490 p_argument => 'object_version_number',
491 p_argument_value => p_object_version_number);
492 --
493 -- Check to ensure the datetrack mode is not INSERT.
494 --
495 If (p_datetrack_mode <> 'INSERT') then
496 --
497 -- We must select and lock the current row.
498 --
499 Open C_Sel1;
500 Fetch C_Sel1 Into g_old_rec;
501 If C_Sel1%notfound then
502 Close C_Sel1;
503 --
504 -- The primary key is invalid therefore we must error
505 --
506 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
507 hr_utility.raise_error;
508 End If;
509 Close C_Sel1;
510 If (p_object_version_number <> g_old_rec.object_version_number) Then
511 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
512 hr_utility.raise_error;
513 End If;
514 hr_utility.set_location(l_proc, 15);
515 --
516 --
517 -- Validate the datetrack mode mode getting the validation start
518 -- and end dates for the specified datetrack operation.
519 --
520 dt_api.validate_dt_mode
521 (p_effective_date => p_effective_date,
522 p_datetrack_mode => p_datetrack_mode,
523 p_base_table_name => 'per_person_type_usages_f',
524 p_base_key_column => 'person_type_usage_id',
525 p_base_key_value => p_person_type_usage_id,
526 p_parent_table_name1 => 'per_all_people_f',
527 p_parent_key_column1 => 'person_id',
528 p_parent_key_value1 => g_old_rec.person_id,
529 p_enforce_foreign_locking => true,
530 p_validation_start_date => l_validation_start_date,
531 p_validation_end_date => l_validation_end_date);
532 Else
533 --
534 -- We are doing a datetrack 'INSERT' which is illegal within this
535 -- procedure therefore we must error (note: to lck on insert the
536 -- private procedure ins_lck should be called).
537 --
538 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
539 hr_utility.set_message_token('PROCEDURE', l_proc);
540 hr_utility.set_message_token('STEP','20');
541 hr_utility.raise_error;
542 End If;
543 --
544 -- Set the validation start and end date OUT arguments
545 --
546 p_validation_start_date := l_validation_start_date;
547 p_validation_end_date := l_validation_end_date;
548 --
549 hr_utility.set_location(' Leaving:'||l_proc, 30);
550 --
551 -- We need to trap the ORA LOCK exception
552 --
553 Exception
554 When HR_Api.Object_Locked then
555 --
556 -- The object is locked therefore we need to supply a meaningful
557 -- error message.
558 --
559 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
560 hr_utility.set_message_token('TABLE_NAME', 'per_person_type_usages_f');
564 -- The object doesn't exist or is invalid
561 hr_utility.raise_error;
562 When l_object_invalid then
563 --
565 --
566 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
567 hr_utility.set_message_token('TABLE_NAME', 'per_person_type_usages_f');
568 hr_utility.raise_error;
569 End lck;
570 --
571 -- ----------------------------------------------------------------------------
572 -- |-----------------------------< convert_args >-----------------------------|
573 -- ----------------------------------------------------------------------------
574 Function convert_args
575 (
576 p_person_type_usage_id in number,
577 p_person_id in number,
578 p_person_type_id in number,
579 p_effective_start_date in date,
580 p_effective_end_date in date,
581 p_object_version_number in number,
582 p_request_id in number,
583 p_program_application_id in number,
584 p_program_id in number,
585 p_program_update_date in date,
586 p_attribute_category in varchar2,
587 p_attribute1 in varchar2,
588 p_attribute2 in varchar2,
589 p_attribute3 in varchar2,
590 p_attribute4 in varchar2,
591 p_attribute5 in varchar2,
592 p_attribute6 in varchar2,
593 p_attribute7 in varchar2,
594 p_attribute8 in varchar2,
595 p_attribute9 in varchar2,
596 p_attribute10 in varchar2,
597 p_attribute11 in varchar2,
598 p_attribute12 in varchar2,
599 p_attribute13 in varchar2,
600 p_attribute14 in varchar2,
601 p_attribute15 in varchar2,
602 p_attribute16 in varchar2,
603 p_attribute17 in varchar2,
604 p_attribute18 in varchar2,
605 p_attribute19 in varchar2,
606 p_attribute20 in varchar2,
607 p_attribute21 in varchar2,
608 p_attribute22 in varchar2,
609 p_attribute23 in varchar2,
610 p_attribute24 in varchar2,
611 p_attribute25 in varchar2,
612 p_attribute26 in varchar2,
613 p_attribute27 in varchar2,
614 p_attribute28 in varchar2,
615 p_attribute29 in varchar2,
616 p_attribute30 in varchar2
617 )
618 Return g_rec_type is
619 --
620 l_rec g_rec_type;
621 l_proc varchar2(72) := g_package||'convert_args';
622 --
623 Begin
624 --
625 hr_utility.set_location('Entering:'||l_proc, 5);
626 --
627 -- Convert arguments into local l_rec structure.
628 --
629 l_rec.person_type_usage_id := p_person_type_usage_id;
630 l_rec.person_id := p_person_id;
631 l_rec.person_type_id := p_person_type_id;
632 l_rec.effective_start_date := p_effective_start_date;
633 l_rec.effective_end_date := p_effective_end_date;
634 l_rec.object_version_number := p_object_version_number;
635 l_rec.request_id := p_request_id;
636 l_rec.program_application_id := p_program_application_id;
637 l_rec.program_id := p_program_id;
638 l_rec.program_update_date := p_program_update_date;
639 l_rec.attribute_category := p_attribute_category;
640 l_rec.attribute1 := p_attribute1;
641 l_rec.attribute2 := p_attribute2;
642 l_rec.attribute3 := p_attribute3;
643 l_rec.attribute4 := p_attribute4;
644 l_rec.attribute5 := p_attribute5;
645 l_rec.attribute6 := p_attribute6;
646 l_rec.attribute7 := p_attribute7;
647 l_rec.attribute8 := p_attribute8;
648 l_rec.attribute9 := p_attribute9;
652 l_rec.attribute13 := p_attribute13;
649 l_rec.attribute10 := p_attribute10;
650 l_rec.attribute11 := p_attribute11;
651 l_rec.attribute12 := p_attribute12;
653 l_rec.attribute14 := p_attribute14;
654 l_rec.attribute15 := p_attribute15;
655 l_rec.attribute16 := p_attribute16;
656 l_rec.attribute17 := p_attribute17;
657 l_rec.attribute18 := p_attribute18;
658 l_rec.attribute19 := p_attribute19;
659 l_rec.attribute20 := p_attribute20;
660 l_rec.attribute21 := p_attribute21;
661 l_rec.attribute22 := p_attribute22;
662 l_rec.attribute23 := p_attribute23;
663 l_rec.attribute24 := p_attribute24;
664 l_rec.attribute25 := p_attribute25;
665 l_rec.attribute26 := p_attribute26;
666 l_rec.attribute27 := p_attribute27;
667 l_rec.attribute28 := p_attribute28;
668 l_rec.attribute29 := p_attribute29;
669 l_rec.attribute30 := p_attribute30;
670 --
671 -- Return the plsql record structure.
672 --
673 hr_utility.set_location(' Leaving:'||l_proc, 10);
674 Return(l_rec);
675 --
676 End convert_args;
677 --
678 -- ----------------------------------------------------------------------------
679 -- |--------------------------< set_called_from_form >------------------------|
680 -- ----------------------------------------------------------------------------
681 procedure set_called_from_form
682 ( p_flag in boolean ) as
683 begin
684 g_called_from_form:=p_flag;
685 end;
686 --
687
688 end per_ptu_shd;