[Home] [Help]
PACKAGE BODY: APPS.PER_PTU_BUS
Source
1 Package Body per_ptu_bus 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_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |--------------------------< dt_update_validate >--------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure is used for referential integrity of datetracked
17 -- parent entities when a datetrack update operation is taking place
18 -- and where there is no cascading of update defined for this entity.
19 --
20 -- Prerequisites:
21 -- This procedure is called from the update_validate.
22 --
23 -- In Parameters:
24 --
25 -- Post Success:
26 -- Processing continues.
27 --
28 -- Post Failure:
29 --
30 -- Developer Implementation Notes:
31 -- This procedure should not need maintenance unless the HR Schema model
32 -- changes.
33 --
34 -- Access Status:
35 -- Internal Row Handler Use Only.
36 --
37 -- {End Of Comments}
38 -- ----------------------------------------------------------------------------
39 Procedure dt_update_validate
40 (
41 p_person_id in number default hr_api.g_number,
42 p_datetrack_mode in varchar2,
43 p_validation_start_date in date,
44 p_validation_end_date in date) Is
45 --
46 l_proc varchar2(72) := g_package||'dt_update_validate';
47 l_integrity_error Exception;
48 l_table_name all_tables.table_name%TYPE;
49 --
50 Begin
51 hr_utility.set_location('Entering:'||l_proc, 5);
52 --
53 -- Ensure that the p_datetrack_mode argument is not null
54 --
55 hr_api.mandatory_arg_error
56 (p_api_name => l_proc,
57 p_argument => 'datetrack_mode',
58 p_argument_value => p_datetrack_mode);
59 --
60 -- Only perform the validation if the datetrack update mode is valid
61 --
62 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
63 --
64 --
65 -- Ensure the arguments are not null
66 --
67 hr_api.mandatory_arg_error
68 (p_api_name => l_proc,
69 p_argument => 'validation_start_date',
70 p_argument_value => p_validation_start_date);
71 --
72 hr_api.mandatory_arg_error
73 (p_api_name => l_proc,
74 p_argument => 'validation_end_date',
75 p_argument_value => p_validation_end_date);
76 --
77 If ((nvl(p_person_id, hr_api.g_number) <> hr_api.g_number) and
78 NOT (dt_api.check_min_max_dates
79 (p_base_table_name => 'per_all_people_f', -- Bug 3111207
80 p_base_key_column => 'person_id',
81 p_base_key_value => p_person_id,
82 p_from_date => p_validation_start_date,
83 p_to_date => p_validation_end_date))) Then
84 l_table_name := 'people';
85 Raise l_integrity_error;
86 End If;
87 --
88 --
89 --
90 End If;
91 --
92 hr_utility.set_location(' Leaving:'||l_proc, 10);
93 Exception
94 When l_integrity_error Then
95 --
96 -- A referential integrity check was violated therefore
97 -- we must error
98 --
99 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
100 hr_utility.set_message_token('TABLE_NAME', l_table_name);
101 hr_utility.raise_error;
102 When Others Then
103 --
104 -- An unhandled or unexpected error has occurred which
105 -- we must report
106 --
107 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
108 hr_utility.set_message_token('PROCEDURE', l_proc);
109 hr_utility.set_message_token('STEP','15');
110 hr_utility.raise_error;
111 End dt_update_validate;
112 --
113 -- ----------------------------------------------------------------------------
114 -- |--------------------------< dt_delete_validate >--------------------------|
115 -- ----------------------------------------------------------------------------
116 -- {Start Of Comments}
117 --
118 -- Description:
119 -- This procedure is used for referential integrity of datetracked
120 -- child entities when either a datetrack DELETE or ZAP is in operation
121 -- and where there is no cascading of delete defined for this entity.
122 -- For the datetrack mode of DELETE or ZAP we must ensure that no
123 -- datetracked child rows exist between the validation start and end
124 -- dates.
125 --
126 -- Prerequisites:
127 -- This procedure is called from the delete_validate.
128 --
129 -- In Parameters:
130 --
131 -- Post Success:
132 -- Processing continues.
133 --
134 -- Post Failure:
135 -- If a row exists by determining the returning Boolean value from the
136 -- generic dt_api.rows_exist function then we must supply an error via
137 -- the use of the local exception handler l_rows_exist.
138 --
139 -- Developer Implementation Notes:
140 -- This procedure should not need maintenance unless the HR Schema model
141 -- changes.
142 --
143 -- Access Status:
144 -- Internal Row Handler Use Only.
145 --
146 -- {End Of Comments}
147 -- ----------------------------------------------------------------------------
148 Procedure dt_delete_validate
149 (p_person_type_usage_id in number,
150 p_datetrack_mode in varchar2,
151 p_validation_start_date in date,
152 p_validation_end_date in date) Is
153 --
154 l_proc varchar2(72) := g_package||'dt_delete_validate';
155 l_rows_exist Exception;
156 l_table_name all_tables.table_name%TYPE;
157 --
158 Begin
159 hr_utility.set_location('Entering:'||l_proc, 5);
160 --
161 -- Ensure that the p_datetrack_mode argument is not null
162 --
163 hr_api.mandatory_arg_error
164 (p_api_name => l_proc,
165 p_argument => 'datetrack_mode',
166 p_argument_value => p_datetrack_mode);
167 --
168 -- Only perform the validation if the datetrack mode is either
169 -- DELETE or ZAP
170 --
171 If (p_datetrack_mode = 'DELETE' or
172 p_datetrack_mode = 'ZAP') then
173 --
174 --
175 -- Ensure the arguments are not null
176 --
177 hr_api.mandatory_arg_error
178 (p_api_name => l_proc,
179 p_argument => 'validation_start_date',
180 p_argument_value => p_validation_start_date);
181 --
182 hr_api.mandatory_arg_error
183 (p_api_name => l_proc,
184 p_argument => 'validation_end_date',
185 p_argument_value => p_validation_end_date);
186 --
187 hr_api.mandatory_arg_error
188 (p_api_name => l_proc,
189 p_argument => 'person_type_usage_id',
190 p_argument_value => p_person_type_usage_id);
191 --
192 --
193 --
194 End If;
195 --
196 hr_utility.set_location(' Leaving:'||l_proc, 10);
197 Exception
198 When l_rows_exist Then
199 --
200 -- A referential integrity check was violated therefore
201 -- we must error
202 --
203 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
204 hr_utility.set_message_token('TABLE_NAME', l_table_name);
205 hr_utility.raise_error;
206 When Others Then
207 --
208 -- An unhandled or unexpected error has occurred which
209 -- we must report
210 --
211 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
212 hr_utility.set_message_token('PROCEDURE', l_proc);
213 hr_utility.set_message_token('STEP','15');
214 hr_utility.raise_error;
215 End dt_delete_validate;
216 --
217 --
218 --
219 -- -----------------------------------------------------------------------
220 -- |------------------------------< chk_df >-----------------------------|
221 -- -----------------------------------------------------------------------
222 --
223 -- Description:
224 -- Validates the all Descriptive Flexfield values.
225 --
226 -- Pre-conditions:
227 -- All other columns have been validated. Must be called as the
228 -- last step from insert_validate and update_validate.
229 --
230 -- In Arguments:
231 -- p_rec
232 --
233 -- Post Success:
234 -- If the Descriptive Flexfield structure column and data values are
235 -- all valid this procedure will end normally and processing will
236 -- continue.
237 --
238 -- Post Failure:
239 -- If the Descriptive Flexfield structure column value or any of
240 -- the data values are invalid then an application error is raised as
241 -- a PL/SQL exception.
242 --
243 -- Access Status:
244 -- Internal Row Handler Use Only.
245 --
246 procedure check_df
247 (p_rec in per_ptu_shd.g_rec_type) is
248 --
249 l_proc varchar2(72) := g_package||'chk_df';
250 --
251 begin
252 hr_utility.set_location('Entering:'||l_proc, 10);
253 --
254 if ((p_rec.person_type_usage_id is not null) and (
255 nvl(per_ptu_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
256 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
257 nvl(per_ptu_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
258 nvl(p_rec.attribute1, hr_api.g_varchar2) or
259 nvl(per_ptu_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
260 nvl(p_rec.attribute2, hr_api.g_varchar2) or
261 nvl(per_ptu_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
262 nvl(p_rec.attribute3, hr_api.g_varchar2) or
263 nvl(per_ptu_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
264 nvl(p_rec.attribute4, hr_api.g_varchar2) or
265 nvl(per_ptu_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
266 nvl(p_rec.attribute5, hr_api.g_varchar2) or
267 nvl(per_ptu_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
268 nvl(p_rec.attribute6, hr_api.g_varchar2) or
269 nvl(per_ptu_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
270 nvl(p_rec.attribute7, hr_api.g_varchar2) or
271 nvl(per_ptu_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
272 nvl(p_rec.attribute8, hr_api.g_varchar2) or
273 nvl(per_ptu_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
274 nvl(p_rec.attribute9, hr_api.g_varchar2) or
275 nvl(per_ptu_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
276 nvl(p_rec.attribute10, hr_api.g_varchar2) or
277 nvl(per_ptu_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
278 nvl(p_rec.attribute11, hr_api.g_varchar2) or
279 nvl(per_ptu_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
280 nvl(p_rec.attribute12, hr_api.g_varchar2) or
281 nvl(per_ptu_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
282 nvl(p_rec.attribute13, hr_api.g_varchar2) or
283 nvl(per_ptu_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
284 nvl(p_rec.attribute14, hr_api.g_varchar2) or
285 nvl(per_ptu_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
286 nvl(p_rec.attribute15, hr_api.g_varchar2) or
287 nvl(per_ptu_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
288 nvl(p_rec.attribute16, hr_api.g_varchar2) or
289 nvl(per_ptu_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
290 nvl(p_rec.attribute17, hr_api.g_varchar2) or
291 nvl(per_ptu_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
292 nvl(p_rec.attribute18, hr_api.g_varchar2) or
293 nvl(per_ptu_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
294 nvl(p_rec.attribute19, hr_api.g_varchar2) or
295 nvl(per_ptu_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
296 nvl(p_rec.attribute20, hr_api.g_varchar2) or
297 nvl(per_ptu_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
298 nvl(p_rec.attribute21, hr_api.g_varchar2) or
299 nvl(per_ptu_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
300 nvl(p_rec.attribute22, hr_api.g_varchar2) or
301 nvl(per_ptu_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
302 nvl(p_rec.attribute23, hr_api.g_varchar2) or
303 nvl(per_ptu_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
304 nvl(p_rec.attribute24, hr_api.g_varchar2) or
305 nvl(per_ptu_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
306 nvl(p_rec.attribute25, hr_api.g_varchar2) or
307 nvl(per_ptu_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
308 nvl(p_rec.attribute26, hr_api.g_varchar2) or
309 nvl(per_ptu_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
310 nvl(p_rec.attribute27, hr_api.g_varchar2) or
311 nvl(per_ptu_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
312 nvl(p_rec.attribute28, hr_api.g_varchar2) or
313 nvl(per_ptu_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
314 nvl(p_rec.attribute29, hr_api.g_varchar2) or
315 nvl(per_ptu_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
316 nvl(p_rec.attribute30, hr_api.g_varchar2)))
317 or
318 (p_rec.person_type_usage_id is null) then
319 --
320 -- Only execute the validation if absolutely necessary:
321 -- a) During update, the structure column value or any
322 -- of the attribute values have actually changed.
323 -- b) During insert.
324 --
325 hr_dflex_utility.ins_or_upd_descflex_attribs
326 (p_appl_short_name => 'PER'
327 ,p_descflex_name => 'PER_PERSON_TYPE_USAGES'
328 ,p_attribute_category => p_rec.attribute_category
329 ,p_attribute1_name => 'ATTRIBUTE1'
330 ,p_attribute1_value => p_rec.attribute1
331 ,p_attribute2_name => 'ATTRIBUTE2'
332 ,p_attribute2_value => p_rec.attribute2
333 ,p_attribute3_name => 'ATTRIBUTE3'
334 ,p_attribute3_value => p_rec.attribute3
335 ,p_attribute4_name => 'ATTRIBUTE4'
336 ,p_attribute4_value => p_rec.attribute4
337 ,p_attribute5_name => 'ATTRIBUTE5'
338 ,p_attribute5_value => p_rec.attribute5
339 ,p_attribute6_name => 'ATTRIBUTE6'
340 ,p_attribute6_value => p_rec.attribute6
341 ,p_attribute7_name => 'ATTRIBUTE7'
342 ,p_attribute7_value => p_rec.attribute7
343 ,p_attribute8_name => 'ATTRIBUTE8'
344 ,p_attribute8_value => p_rec.attribute8
345 ,p_attribute9_name => 'ATTRIBUTE9'
346 ,p_attribute9_value => p_rec.attribute9
347 ,p_attribute10_name => 'ATTRIBUTE10'
348 ,p_attribute10_value => p_rec.attribute10
349 ,p_attribute11_name => 'ATTRIBUTE11'
350 ,p_attribute11_value => p_rec.attribute11
351 ,p_attribute12_name => 'ATTRIBUTE12'
352 ,p_attribute12_value => p_rec.attribute12
353 ,p_attribute13_name => 'ATTRIBUTE13'
354 ,p_attribute13_value => p_rec.attribute13
355 ,p_attribute14_name => 'ATTRIBUTE14'
356 ,p_attribute14_value => p_rec.attribute14
357 ,p_attribute15_name => 'ATTRIBUTE15'
358 ,p_attribute15_value => p_rec.attribute15
359 ,p_attribute16_name => 'ATTRIBUTE16'
360 ,p_attribute16_value => p_rec.attribute16
361 ,p_attribute17_name => 'ATTRIBUTE17'
362 ,p_attribute17_value => p_rec.attribute17
363 ,p_attribute18_name => 'ATTRIBUTE18'
364 ,p_attribute18_value => p_rec.attribute18
365 ,p_attribute19_name => 'ATTRIBUTE19'
366 ,p_attribute19_value => p_rec.attribute19
367 ,p_attribute20_name => 'ATTRIBUTE20'
368 ,p_attribute20_value => p_rec.attribute20
369 ,p_attribute21_name => 'ATTRIBUTE21'
370 ,p_attribute21_value => p_rec.attribute21
371 ,p_attribute22_name => 'ATTRIBUTE22'
372 ,p_attribute22_value => p_rec.attribute22
373 ,p_attribute23_name => 'ATTRIBUTE23'
374 ,p_attribute23_value => p_rec.attribute23
375 ,p_attribute24_name => 'ATTRIBUTE24'
376 ,p_attribute24_value => p_rec.attribute24
377 ,p_attribute25_name => 'ATTRIBUTE25'
378 ,p_attribute25_value => p_rec.attribute25
379 ,p_attribute26_name => 'ATTRIBUTE26'
380 ,p_attribute26_value => p_rec.attribute26
381 ,p_attribute27_name => 'ATTRIBUTE27'
382 ,p_attribute27_value => p_rec.attribute27
383 ,p_attribute28_name => 'ATTRIBUTE28'
384 ,p_attribute28_value => p_rec.attribute28
385 ,p_attribute29_name => 'ATTRIBUTE29'
386 ,p_attribute29_value => p_rec.attribute29
387 ,p_attribute30_name => 'ATTRIBUTE30'
388 ,p_attribute30_value => p_rec.attribute30
389 );
390 end if;
391 --
392 hr_utility.set_location(' Leaving:'||l_proc, 20);
393 end check_df;
394 --
395 -- --------------------------------------------------------------------------
396 -- |---------------------< chk_non_updateable_args >------------------------|
397 -- --------------------------------------------------------------------------
398 Procedure chk_non_updateable_args
399 (p_rec in per_ptu_shd.g_rec_type
400 ,p_effective_date in date
401 ) is
402 --
403 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
404 l_error exception;
405 l_argument varchar2(30);
406 --
407 Begin
408 hr_utility.set_location('Entering:'||l_proc, 10);
409 --
410 -- Only proceed with validation if a row exists for
411 -- the current record in the HR Schema
412 --
413 if not per_ptu_shd.api_updating
414 (p_person_type_usage_id => p_rec.person_type_usage_id
415 ,p_object_version_number => p_rec.object_version_number
416 ,p_effective_date => p_effective_date
417 ) then
418 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
419 hr_utility.set_message_token('PROCEDURE', l_proc);
420 hr_utility.set_message_token('STEP', '20');
421 end if;
422 hr_utility.set_location(l_proc, 30);
423 --
424 -- Bug# 2195731 Starts here. If Condition changed.
425 --
426 if p_rec.person_id <> per_ptu_shd.g_old_rec.person_id then
427 l_argument := 'person_id';
428 raise l_error;
429 end if;
430 --
431 -- Bug# 2195731 Ends here.
432 --
433 hr_utility.set_location(l_proc, 50);
434 --
435 exception
436 when l_error then
437 hr_api.argument_changed_error
438 (p_api_name => l_proc
439 ,p_argument => l_argument
440 );
441 when others then
442 raise;
443 hr_utility.set_location(' Leaving:'||l_proc, 100);
444 end chk_non_updateable_args;
445
446 --
447 -- -----------------------------------------------------------------
448 -- |-----------------------< chk_person_id >-------------------------|
449 -- -----------------------------------------------------------------
450 --
451 -- Description:
452 -- Check that the person_id is not null and that it refers to a row on
453 -- the parent PER_ALL_PEOPLE_F table.
454 --
455 -- Pre-Requisites:
456 -- None
457 --
458 -- In Parameters:
459 -- p_person_id
460 --
461 -- Post Success:
462 -- Processing continues if the person_id is valid.
463 --
464 -- Post Failure:
465 -- An application error is raised and processing is terminated if
466 -- the person_id is invalid.
467 --
468 -- Access Status:
469 -- Internal Row Handler Use Only.
470 --
471 Procedure chk_person_id
472 (p_person_id in number
473 ) is
474 --
475 -- Local declarations
476 l_proc varchar2(72) := g_package||' chk_person_id';
477 l_person_id number;
478
479 -- Setup cursor for valid module type check
480 cursor csr_valid_person_id is
481 select person_id
482 from per_all_people_f
483 where person_id = p_person_id;
484 --
485 begin
486 hr_utility.set_location('Entering: '||l_proc,5);
487 --
488 --------------------------------
489 -- Check person id not null --
490 --------------------------------
491 hr_api.mandatory_arg_error
492 (p_api_name => l_proc,
493 p_argument => 'p_person_id',
494 p_argument_value => p_person_id);
495
496 --------------------------------
497 -- Check person id is valid --
498 --------------------------------
499 open csr_valid_person_id;
500 fetch csr_valid_person_id into l_person_id;
501 if csr_valid_person_id%notfound then
502 close csr_valid_person_id;
503 per_ptu_shd.constraint_error('PER_PERSON_TYPE_USAGES_F_FK1');
504 end if;
505 close csr_valid_person_id;
506
507 hr_utility.set_location('Leaving: '||l_proc,10);
508 end chk_person_id;
509 --
510 --
511 -- -----------------------------------------------------------------
512 -- |-----------------------< chk_person_type_id >------------------|
513 -- -----------------------------------------------------------------
514 --
515 -- Description:
516 -- Check that the person_type_id is not null and that it refers to a row on
517 -- the parent PER_PERSON_TYPES table.
518 -- Amendments to ensure that only valid person_types are inserted/updated
519 --
520 -- Pre-Requisites:
521 -- None
522 --
523 -- In Parameters:
524 -- p_person_type_usage_id
525 -- p_person_type_id
526 -- p_effective_date
527 -- p_object_version_number
528 --
529 -- Post Success:
530 -- Processing continues if the person_type_id is valid.
531 --
532 -- Post Failure:
533 -- An application error is raised and processing is terminated if
534 -- the person_type_id is invalid.
535 --
536 -- Access Status:
537 -- Internal Row Handler Use Only.
538 --
539 Procedure chk_person_type_id
540 (p_person_type_usage_id in number,
541 p_person_id in number,
542 p_person_type_id in number,
543 p_effective_date in date,
544 p_object_version_number in number
545 ) is
546 --
547 -- Local declarations
548 l_proc varchar2(72) := g_package||' chk_person_type_id';
549 l_person_type per_person_types.system_person_type%TYPE;
550 l_business_group_id per_person_types.business_group_id%TYPE;
551 l_old_person_type per_person_types.system_person_type%TYPE;
552 l_person_type_id number;
553 l_api_updating boolean;
554 --
555 -- Setup cursor for valid module type check
556 --
557 cursor csr_valid_person_type ( lc_person_type_id number)
558 is
559 select system_person_type ,business_group_id
560 from per_person_types
561 where person_type_id = lc_person_type_id;
562
563 --
564 -- Setup cursor for combination check
565 --
566 -- We are doing this check regardless of the enabled flag
567 -- as even old records must be used for this validation
568 cursor csr_check_uniqueness is
569 select person_type_usage_id
570 from per_person_type_usages_f
571 where person_type_id in ( select person_type_id
572 from per_person_types
573 where system_person_type = l_person_type
574 and business_group_id = l_business_group_id )
575 and person_id = p_person_id
576 and ((effective_start_date <= p_effective_date and
577 effective_end_date >= p_effective_date) or
578 (effective_start_date >= p_effective_date));
579 --
580 begin
581 hr_utility.set_location('Entering: '||l_proc,5);
582 --
583 -----------------------------------
584 -- Check person type id not null --
585 -----------------------------------
586 --
587 hr_api.mandatory_arg_error
588 (p_api_name => l_proc,
589 p_argument => 'p_person_type_id',
590 p_argument_value => p_person_type_id);
591 --
592 l_api_updating := per_ptu_shd.api_updating
593 (p_effective_date => p_effective_date,
594 p_person_type_usage_id => p_person_type_usage_id,
595 p_object_version_number => p_object_version_number
596 );
597 --
598 -- Proceed with validation based on outcome of api_updating call.
599 --
600 -- We are initially checking only for valid person types
601 -- i.e. those that can be maintained by the user
602 --
603 hr_utility.set_location('At: '||l_proc,10);
604
605 if ((l_api_updating and
606 per_ptu_shd.g_old_rec.person_type_id <> p_person_type_id) or
607 (not l_api_updating)) then
608 -----------------------------------
609 -- Check person type id is valid --
610 -----------------------------------
611 open csr_valid_person_type(p_person_type_id);
612 fetch csr_valid_person_type into l_person_type , l_business_group_id;
613 if csr_valid_person_type%notfound then
614 close csr_valid_person_type;
615 per_ptu_shd.constraint_error('PER_PERSON_TYPE_USAGES_F_FK2');
616 end if;
617 close csr_valid_person_type;
618
619 hr_utility.set_location('At: '||l_proc,15);
620 --
621 -- Based on whether this is called call the relevant procedure
622 --
623 if ( not l_api_updating ) THEN
624
625 --
626 -- This processing will only take place if it is create
627 --
628
629 if ( l_person_type = 'EX_EMP' or
630 l_person_type = 'EX_APL' or
631 l_person_type = 'EMP_APL' or
632 l_person_type = 'EX_EMP_APL' or
633 l_person_type = 'APL_EX_APL') then
634 per_ptu_shd.constraint_error('PER_PERSON_TYPE_USAGES_F_FK2');
635 end if;
636 --
637 --
638 hr_utility.set_location('At: '||l_proc,20);
639 ----------------------------------------
640 -- Check uniqueness of created record --
641 ----------------------------------------
642 --
643 open csr_check_uniqueness;
644 fetch csr_check_uniqueness into l_person_type_id;
645 if csr_check_uniqueness%found then
646 close csr_check_uniqueness;
647 fnd_message.set_name('PER', 'HR_52376_PTU_DUPLICATE_REC');
648 fnd_message.raise_error;
649 end if;
650 close csr_check_uniqueness;
651 --
652 else
653
654 -- On update we need to check whether the system person_type
655 -- is the same as the existing one
656 -- As per business rules person type can be updated from one
657 -- flavour to an other.
658
659 hr_utility.set_location('At: '||l_proc,25);
660
661
662 -----------------------------------
663 -- Check person type id is valid --
664 -----------------------------------
665 open csr_valid_person_type(per_ptu_shd.g_old_rec.person_type_id);
666 fetch csr_valid_person_type into l_old_person_type , l_business_group_id;
667 if csr_valid_person_type%notfound then
668 close csr_valid_person_type;
669 per_ptu_shd.constraint_error('PER_PERSON_TYPE_USAGES_F_FK2');
670 end if;
671 close csr_valid_person_type;
672 --
673 -- PTU changes: now allow transitions between type and ex_type
674 --
675 IF (l_old_person_type <> l_person_type)
676 AND ( (l_old_person_type in ('EMP','EX_EMP')
677 and l_person_type not in ('EMP','EX_EMP'))
678 or (l_old_person_type in ('APL','EX_APL')
679 and l_person_type not in ('APL','EX_APL'))) THEN
680
681 fnd_message.set_name('PER', 'HR_52362_PTU_INV_PER_TYPE_ID');
682 fnd_message.raise_error;
683
684 END IF;
685
686 end if;
687 end if;
688
689 hr_utility.set_location('Leaving: '||l_proc,50);
690 end chk_person_type_id;
691 --
692 -- ----------------------------------------------------------------------------
693 -- |---------------------------< insert_validate >----------------------------|
694 -- ----------------------------------------------------------------------------
695 Procedure insert_validate
696 (p_rec in per_ptu_shd.g_rec_type,
697 p_effective_date in date,
698 p_datetrack_mode in varchar2,
699 p_validation_start_date in date,
700 p_validation_end_date in date) is
701 --
702 l_proc varchar2(72) := g_package||'insert_validate';
703 --
704 Begin
705 hr_utility.set_location('Entering:'||l_proc, 5);
706 --
707 per_per_bus.set_security_group_id
708 (
709 p_person_id => p_rec.person_id
710 );
711 --
712 hr_utility.set_location('Entering:'||l_proc, 7);
713 --
714 -- Call all supporting business operations
715 --
716 -- Validate the Person Id
717 chk_person_id
718 (p_person_id => p_rec.person_id);
719 --
720 -- Validate the Person Type Id
721 chk_person_type_id
722 (p_person_type_usage_id => p_rec.person_type_usage_id,
723 p_person_id => p_rec.person_id,
724 p_person_type_id => p_rec.person_type_id,
725 p_effective_date => p_effective_date,
726 p_object_version_number => p_rec.object_version_number);
727 --
728 -- Call descriptive flexfield validation routines
729 --
730 per_ptu_bus.check_df(p_rec => p_rec);
731 --
732 hr_utility.set_location(' Leaving:'||l_proc, 10);
733 End insert_validate;
734 --
735 -- ----------------------------------------------------------------------------
736 -- |---------------------------< update_validate >----------------------------|
737 -- ----------------------------------------------------------------------------
738 Procedure update_validate
739 (p_rec in per_ptu_shd.g_rec_type,
740 p_effective_date in date,
741 p_datetrack_mode in varchar2,
742 p_validation_start_date in date,
743 p_validation_end_date in date) is
744 --
745 l_proc varchar2(72) := g_package||'update_validate';
746 --
747 Begin
748 hr_utility.set_location('Entering:'||l_proc, 5);
749 --
750 per_per_bus.set_security_group_id
751 (
752 p_person_id => p_rec.person_id
753 );
754 hr_utility.set_location('Entering:'||l_proc, 7);
755 --
756 -- Call all supporting business operations
757 --
758 chk_non_updateable_args
759 (p_rec => p_rec
760 ,p_effective_date => p_effective_date);
761 --
762 -- Validate the Person Type Id
763 chk_person_type_id
764 (p_person_type_usage_id => p_rec.person_type_usage_id,
765 p_person_id => p_rec.person_id,
766 p_person_type_id => p_rec.person_type_id,
767 p_effective_date => p_effective_date,
768 p_object_version_number => p_rec.object_version_number);
769 --
770 -- Call the datetrack update integrity operation
771 --
772 dt_update_validate
773 (p_person_id => p_rec.person_id,
774 p_datetrack_mode => p_datetrack_mode,
775 p_validation_start_date => p_validation_start_date,
776 p_validation_end_date => p_validation_end_date);
777 --
778 --
779 -- Call descriptive flexfield validation routines
780 --
781 per_ptu_bus.check_df(p_rec => p_rec);
782 --
783 hr_utility.set_location(' Leaving:'||l_proc, 10);
784 End update_validate;
785 --
786 -- ----------------------------------------------------------------------------
787 -- |---------------------------< delete_validate >----------------------------|
788 -- ----------------------------------------------------------------------------
789 Procedure delete_validate
790 (p_rec in per_ptu_shd.g_rec_type,
791 p_effective_date in date,
792 p_datetrack_mode in varchar2,
793 p_validation_start_date in date,
794 p_validation_end_date in date) is
795 --
796 l_proc varchar2(72) := g_package||'delete_validate';
797 --
798 Begin
799 hr_utility.set_location('Entering:'||l_proc, 5);
800 --
801 -- Call all supporting business operations
802 --
803 --chk_is_valid_delete(p_person_type_usage_id => p_rec.person_type_usage_id,
804 --p_effective_date => p_effective_date);
805
806 dt_delete_validate
807 (p_datetrack_mode => p_datetrack_mode,
808 p_validation_start_date => p_validation_start_date,
809 p_validation_end_date => p_validation_end_date,
810 p_person_type_usage_id => p_rec.person_type_usage_id);
811 --
812 hr_utility.set_location(' Leaving:'||l_proc, 10);
813 End delete_validate;
814 --
815 --
816 -- ---------------------------------------------------------------------------
817 -- |---------------------< return_legislation_code >-------------------------|
818 -- ---------------------------------------------------------------------------
819 --
820 function return_legislation_code
821 (p_person_type_usage_id in number
822 ) return varchar2 is
823 --
824 -- Cursor to find legislation code
825 --
826 cursor csr_leg_code is
827 select pbg.legislation_code
828 from per_business_groups pbg
829 , per_person_type_usages_f ptu
830 , per_all_people_f per
831 where ptu.person_type_usage_id = p_person_type_usage_id
832 and ptu.person_id = per.person_id
833 and pbg.business_group_id = per.business_group_id;
834 --
835 -- Declare local variables
836 --
837 l_legislation_code varchar2(150);
838 l_proc varchar2(72) := 'return_legislation_code';
839 begin
840 hr_utility.set_location('Entering:'|| l_proc, 10);
841 --
842 -- Ensure that all the mandatory parameter are not null
843 --
844 hr_api.mandatory_arg_error(p_api_name => l_proc,
845 p_argument => 'person_type_usage_id',
846 p_argument_value => p_person_type_usage_id);
847 --
848 if nvl(g_person_type_usage_id, hr_api.g_number) = p_person_type_usage_id then
849 --
850 -- The legislation code has already been found with a previous
851 -- call to this function. Just return the value in the global
852 -- variable.
853 --
854 l_legislation_code := g_legislation_code;
855 hr_utility.set_location(l_proc, 20);
856 else
857 --
858 -- The ID is different to the last call to this function
859 -- or this is the first call to this function.
860 --
861 open csr_leg_code;
862 fetch csr_leg_code into l_legislation_code;
863 if csr_leg_code%notfound then
864 --
865 -- The primary key is invalid therefore we must error
866 --
867 close csr_leg_code;
868 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
869 hr_utility.raise_error;
870 end if;
871 hr_utility.set_location(l_proc, 30);
872 --
873 -- Set the global variables so the values are
874 -- available for the next call to this function
875 --
876 close csr_leg_code;
877 g_person_type_usage_id := p_person_type_usage_id;
878 g_legislation_code := l_legislation_code;
879 end if;
880 hr_utility.set_location(' Leaving:'|| l_proc, 40);
881 --
882 return l_legislation_code;
883 end return_legislation_code;
884 --
885 end per_ptu_bus;