1 Package Body per_per_bus1 as
2 /* $Header: peperrhi.pkb 120.14.12010000.3 2008/10/01 06:47:05 pvelugul ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_per_bus1.'; -- Global package name
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 procedure df_update_validate
12 (p_rec in per_per_shd.g_rec_type) is
13 --
14 l_proc varchar2(72) := g_package||'df_update_validate';
15 --
16 begin
17 hr_utility.set_location('Entering:'||l_proc, 10);
18 --
19 if nvl(per_per_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
20 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
21 nvl(per_per_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
22 nvl(p_rec.attribute1, hr_api.g_varchar2) or
23 nvl(per_per_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
24 nvl(p_rec.attribute2, hr_api.g_varchar2) or
25 nvl(per_per_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
26 nvl(p_rec.attribute3, hr_api.g_varchar2) or
27 nvl(per_per_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
28 nvl(p_rec.attribute4, hr_api.g_varchar2) or
29 nvl(per_per_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
30 nvl(p_rec.attribute5, hr_api.g_varchar2) or
31 nvl(per_per_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
32 nvl(p_rec.attribute6, hr_api.g_varchar2) or
33 nvl(per_per_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
34 nvl(p_rec.attribute7, hr_api.g_varchar2) or
35 nvl(per_per_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
36 nvl(p_rec.attribute8, hr_api.g_varchar2) or
37 nvl(per_per_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
38 nvl(p_rec.attribute9, hr_api.g_varchar2) or
39 nvl(per_per_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
40 nvl(p_rec.attribute10, hr_api.g_varchar2) or
41 nvl(per_per_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
42 nvl(p_rec.attribute11, hr_api.g_varchar2) or
43 nvl(per_per_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
44 nvl(p_rec.attribute12, hr_api.g_varchar2) or
45 nvl(per_per_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
46 nvl(p_rec.attribute13, hr_api.g_varchar2) or
47 nvl(per_per_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
48 nvl(p_rec.attribute14, hr_api.g_varchar2) or
49 nvl(per_per_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
50 nvl(p_rec.attribute15, hr_api.g_varchar2) or
51 nvl(per_per_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
52 nvl(p_rec.attribute16, hr_api.g_varchar2) or
53 nvl(per_per_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
54 nvl(p_rec.attribute17, hr_api.g_varchar2) or
55 nvl(per_per_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
56 nvl(p_rec.attribute18, hr_api.g_varchar2) or
57 nvl(per_per_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
58 nvl(p_rec.attribute19, hr_api.g_varchar2) or
59 nvl(per_per_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
60 nvl(p_rec.attribute20, hr_api.g_varchar2) or
61 nvl(per_per_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
62 nvl(p_rec.attribute21, hr_api.g_varchar2) or
63 nvl(per_per_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
64 nvl(p_rec.attribute22, hr_api.g_varchar2) or
65 nvl(per_per_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
66 nvl(p_rec.attribute23, hr_api.g_varchar2) or
67 nvl(per_per_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
68 nvl(p_rec.attribute24, hr_api.g_varchar2) or
69 nvl(per_per_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
70 nvl(p_rec.attribute25, hr_api.g_varchar2) or
71 nvl(per_per_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
72 nvl(p_rec.attribute26, hr_api.g_varchar2) or
73 nvl(per_per_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
74 nvl(p_rec.attribute27, hr_api.g_varchar2) or
75 nvl(per_per_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
76 nvl(p_rec.attribute28, hr_api.g_varchar2) or
77 nvl(per_per_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
78 nvl(p_rec.attribute29, hr_api.g_varchar2) or
79 nvl(per_per_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
80 nvl(p_rec.attribute30, hr_api.g_varchar2) then
81 -- either the attribute_category or attribute1..30 have changed
82 -- so we must call the flex stub
83 per_per_flex.df(p_rec => p_rec);
84 end if;
85 --
86 hr_utility.set_location(' Leaving:'||l_proc, 10);
87 end df_update_validate;
88 --
89 -- ---------------------------------------------------------------------------
90 -- |------------------< chk_unsupported_attributes >-----------------------|
91 -- ---------------------------------------------------------------------------
92 --
93 procedure chk_unsupported_attributes
94 (p_person_id in per_all_people_f.person_id%TYPE
95 ,p_fast_path_employee in per_all_people_f.fast_path_employee%TYPE
96 ,p_order_name in per_all_people_f.order_name%TYPE
97 ,p_projected_start_date in per_all_people_f.projected_start_date%TYPE
98 ,p_rehire_authorizor in per_all_people_f.rehire_authorizor%TYPE
99 ,p_effective_date in date
100 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
101 )
102 is
103 --
104 l_proc varchar2(72) := g_package||'chk_unsupported_attributes';
105 l_api_updating boolean;
106 l_exists varchar2(1);
107 --
108 begin
109 hr_utility.set_location('Entering:'|| l_proc, 10);
110 --
111 -- Check mandatory parameters have been set
112 --
113 hr_api.mandatory_arg_error
114 (p_api_name => l_proc
115 ,p_argument => 'effective date'
116 ,p_argument_value => p_effective_date
117 );
118 --
119 -- Check if the person is being updated
120 --
121 l_api_updating := per_per_shd.api_updating
122 (p_person_id => p_person_id
123 ,p_effective_date => p_effective_date
124 ,p_object_version_number => p_object_version_number
125 );
126 hr_utility.set_location('Entering:'|| l_proc, 20);
127 --
128 if (l_api_updating and nvl(per_per_shd.g_old_rec.fast_path_employee, hr_api.g_varchar2)
129 <> nvl(p_fast_path_employee, hr_api.g_varchar2))
130 or
131 (l_api_updating and nvl(per_per_shd.g_old_rec.order_name, hr_api.g_varchar2)
132 <> nvl(p_order_name, hr_api.g_varchar2))
133 or
134 (l_api_updating and nvl(per_per_shd.g_old_rec.projected_start_date, hr_api.g_date)
135 <> nvl(p_projected_start_date, hr_api.g_date))
136 or
137 (l_api_updating and nvl(per_per_shd.g_old_rec.rehire_authorizor, hr_api.g_varchar2)
138 <> nvl(p_rehire_authorizor, hr_api.g_varchar2))
139 or
140 (NOT l_api_updating)
141 then
142 --
143 -- Check if any of the unsupported attributes are set
144 --
145 if p_fast_path_employee is not null then
146 --
147 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
148 hr_utility.set_message_token('PROCEDURE', l_proc);
149 hr_utility.set_message_token('STEP', 'Fast Path Employee Null Check');
150 hr_utility.raise_error;
151 --
152 end if;
153 --
154 if p_order_name is not null then
155 --
156 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
157 hr_utility.set_message_token('PROCEDURE', l_proc);
158 hr_utility.set_message_token('STEP', 'Order Name Null Check');
159 hr_utility.raise_error;
160 --
161 end if;
162 --
163 if p_projected_start_date is not null then
164 --
165 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
166 hr_utility.set_message_token('PROCEDURE', l_proc);
167 hr_utility.set_message_token('STEP', 'Projected Start Date Null Check');
168 hr_utility.raise_error;
169 --
170 end if;
171 --
172 if p_rehire_authorizor is not null then
173 --
174 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
175 hr_utility.set_message_token('PROCEDURE', l_proc);
176 hr_utility.set_message_token('STEP', 'Rehire Authorizor Null Check');
177 hr_utility.raise_error;
178 --
179 end if;
180 --
181 hr_utility.set_location(l_proc, 30);
182 --
183 end if;
184 --
185 hr_utility.set_location(' Leaving:'|| l_proc, 40);
186 end chk_unsupported_attributes;
187 --
188 -- ---------------------------------------------------------------------------
189 -- |----------------< chk_correspondence_language >--------------------------|
190 -- ---------------------------------------------------------------------------
191 --
192 procedure chk_correspondence_language
193 (p_person_id in per_all_people_f.person_id%TYPE
194 ,p_effective_date in date
195 ,p_correspondence_language in per_all_people_f.correspondence_language%TYPE
196 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
197 )
198 is
199 --
200 l_proc varchar2(72) := g_package||'chk_correspondence_language';
201 l_api_updating boolean;
202 l_exists varchar2(1);
203 --
204 Cursor C_Sel1 Is
205 select null
206 from fnd_languages
207 where p_correspondence_language = language_code;
208 --
209 begin
210 hr_utility.set_location('Entering:'|| l_proc, 10);
211 --
212 -- Check mandatory parameters have been set
213 --
214 hr_api.mandatory_arg_error
215 (p_api_name => l_proc
216 ,p_argument => 'effective date'
217 ,p_argument_value => p_effective_date
218 );
219 --
220 -- Check if the person is being updated
221 --
222 l_api_updating := per_per_shd.api_updating
223 (p_person_id => p_person_id
224 ,p_effective_date => p_effective_date
225 ,p_object_version_number => p_object_version_number
226 );
227 hr_utility.set_location(l_proc, 20);
228 --
229 if ((l_api_updating and
230 nvl(per_per_shd.g_old_rec.correspondence_language, hr_api.g_varchar2)
231 <> nvl(p_correspondence_language, hr_api.g_varchar2)) or
232 (NOT l_api_updating))
233 then
234 --
235 -- Check if correspondence language is set.
236 --
237 if p_correspondence_language is not null then
238 --
239 -- Check the correspondence language exists in FND_LANGUAGES table.
240 --
241 open C_Sel1;
242 fetch C_Sel1 into l_exists;
243 if C_Sel1%NOTFOUND
244 then
245 --
246 close C_Sel1;
247 hr_utility.set_message(801, 'PER_????_PER_INV_COR_LANG');
248 hr_utility.raise_error;
249 --
250 end if;
251 close C_Sel1;
252 end if;
253 end if;
254 hr_utility.set_location('Leaving '||l_proc,30);
255 exception
256 when app_exception.application_exception then
257 if hr_multi_message.exception_add
258 (p_associated_column1 => 'PER_ALL_PEOPLE_F.CORRESPONDENCE_LANGUAGE'
259 )
260 then
261 hr_utility.set_location(' Leaving:'||l_proc, 40);
262 raise;
263 end if;
264 hr_utility.set_location(' Leaving:'||l_proc,50);
265 end chk_correspondence_language;
266 --
267 -- ---------------------------------------------------------------------------
268 -- |----------------< chk_coord_ben_med_cvg_dates >--------------------------|
269 -- ---------------------------------------------------------------------------
270 --
271 procedure chk_coord_ben_med_cvg_dates
272 (p_coord_ben_med_cvg_strt_dt in date
273 ,p_coord_ben_med_cvg_end_dt in date
274 )
275 is
276 --
277 l_proc varchar2(72) := g_package||'chk_coord_ben_med_cvg_dates';
278 --
279 begin
280 hr_utility.set_location('Entering:'|| l_proc, 10);
281 --
282 -- Make sure that the coverage start date is before the coverage end date.
283 --
284 if p_coord_ben_med_cvg_strt_dt is not null or
285 p_coord_ben_med_cvg_end_dt is not null then
286 --
287 -- Make sure that cvg strt dt is after cvg strt dt.
288 --
289 if (p_coord_ben_med_cvg_strt_dt is null and
290 p_coord_ben_med_cvg_end_dt is not null) or
291 (p_coord_ben_med_cvg_strt_dt >
292 nvl(p_coord_ben_med_cvg_end_dt,hr_api.g_eot)) then
293 --
294 -- Error as end dt can not be set unless strt dt has been set
295 --
296 fnd_message.set_name('PER','HR_289110_MED_CVG_DATES');
297 fnd_message.raise_error;
298 --
299 end if;
300 --
301 end if;
302 hr_utility.set_location('Leaving '||l_proc,30);
303 exception
304 when app_exception.application_exception then
305 if hr_multi_message.exception_add
306 (p_associated_column1 => 'PER_ALL_PEOPLE_F.COORD_BEN_MED_CVG_STRT_DT'
307 ,p_associated_column2 => 'PER_ALL_PEOPLE_F.COORD_BEN_MED_CVG_END_DT'
308 ) then
309 hr_utility.set_location(' Leaving:'||l_proc, 40);
310 raise;
311 end if;
312 hr_utility.set_location(' Leaving:'||l_proc,50);
313 end;
314 --
315 -- ---------------------------------------------------------------------------
316 -- |------------------------< chk_other_coverages >--------------------------|
317 -- ---------------------------------------------------------------------------
318 --
319 procedure chk_other_coverages
320 (p_attribute10 in varchar2
321 ,p_coord_ben_med_insr_crr_name in varchar2
322 ,p_coord_ben_med_cvg_end_dt in date
323 ,p_coord_ben_no_cvg_flag in varchar2
324 ,p_effective_date in date
325 )
326 is
327 --
328 l_proc varchar2(72) := g_package||'chk_other_coverages';
329 --
330 begin
331 --
332 hr_utility.set_location('Entering:'|| l_proc, 10);
333 --
334 -- Check that the ben_no_cvg_flag is set accordingly based on the value
335 -- of the covered under medicare flag.
336 --
337 if hr_multi_message.no_all_inclusive_error
338 (p_check_column1 => 'PER_ALL_PEOPLE_F.COORD_BEN_MED_INSR_CRR_NAME'
339 ,p_check_column2 => 'PER_ALL_PEOPLE_F.COORD_BEN_MED_CVG_END_DT'
340 )
341 then
342 if p_attribute10 = 'Y' or
343 (p_coord_ben_med_insr_crr_name is not null and
344 nvl(p_coord_ben_med_cvg_end_dt,hr_api.g_eot) > p_effective_date) then
345 --
346 if p_coord_ben_no_cvg_flag = 'Y' then
347 --
348 fnd_message.set_name('PER','HR_289111_CVG_FLAG_SET');
349 fnd_message.raise_error;
350 --
351 end if;
352 --
353 end if;
354 end if;
355 hr_utility.set_location('Leaving '||l_proc,30);
356 exception
357 when app_exception.application_exception then
358 if hr_multi_message.exception_add
359 (p_associated_column1 => 'PER_ALL_PEOPLE_F.COORD_BEN_MED_INSR_CRR_NAME'
360 ,p_associated_column2 => 'PER_ALL_PEOPLE_F.COORD_BEN_MED_CVG_END_DT'
361 ,p_associated_column3 => 'PER_ALL_PEOPLE_F.COORD_BEN_NO_CVG_FLAG'
362 ,p_associated_column4 => 'PER_ALL_PEOPLE_F.ATTRIBUTE10'
363 ) then
364 hr_utility.set_location(' Leaving:'||l_proc, 40);
365 raise;
366 end if;
367 hr_utility.set_location(' Leaving:'||l_proc,50);
368 end;
369 -- ---------------------------------------------------------------------------
370 -- |----------------< chk_fte_capacity >--------------------------|
371 -- ---------------------------------------------------------------------------
372 --
373 procedure chk_fte_capacity
374 (p_person_id in per_all_people_f.person_id%TYPE
375 ,p_effective_date in date
379 is
376 ,p_fte_capacity in per_all_people_f.fte_capacity%TYPE
377 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
378 )
380 --
381 l_proc varchar2(72) := g_package||'chk_fte_capacity';
382 l_api_updating boolean;
383 l_exists varchar2(1);
384 --
385 begin
386 hr_utility.set_location('Entering:'|| l_proc, 10);
387 --
388 -- Check mandatory parameters have been set
389 --
390 hr_api.mandatory_arg_error
391 (p_api_name => l_proc
392 ,p_argument => 'effective date'
393 ,p_argument_value => p_effective_date
394 );
395 --
396 -- Check if the person is being updated
397 --
398 l_api_updating := per_per_shd.api_updating
399 (p_person_id => p_person_id
400 ,p_effective_date => p_effective_date
401 ,p_object_version_number => p_object_version_number
402 );
403 hr_utility.set_location(l_proc, 20);
404 --
405 if ((l_api_updating and
406 nvl(per_per_shd.g_old_rec.fte_capacity, hr_api.g_number)
407 <> nvl(p_fte_capacity, hr_api.g_number)) or
408 (NOT l_api_updating))
409 then
410 --
411 -- Check if fte_capacity is set.
412 --
413 if p_fte_capacity is not null then
414 --
415 -- Check the fte capacity is in the correct range
416 --
417 if p_fte_capacity < 0 OR
418 p_fte_capacity > 100
419 then
420 --Changes done for Bug 7201892
421 hr_utility.set_message(801, 'HR_51856_EMP_FTE_VALUE');
422 hr_utility.raise_error;
423 --
424 end if;
425 end if;
426 end if;
427 hr_utility.set_location('Leaving '||l_proc,30);
428 exception
429 when app_exception.application_exception then
430 if hr_multi_message.exception_add
431 (p_associated_column1 => 'PER_ALL_PEOPLE_F.FTE_CAPACITY'
432 ) then
433 hr_utility.set_location(' Leaving:'||l_proc, 40);
434 raise;
435 end if;
436 hr_utility.set_location(' Leaving:'||l_proc,50);
437 end chk_fte_capacity;
438 --
439 -- ---------------------------------------------------------------------------
440 -- |----------------< chk_coord_ben_med_details >----------------------------|
441 -- ---------------------------------------------------------------------------
442 --
443 procedure chk_coord_ben_med_details
444 (p_coord_ben_med_cvg_strt_dt in date
445 ,p_coord_ben_med_cvg_end_dt in date
446 ,p_coord_ben_med_ext_er in varchar2
447 ,p_coord_ben_med_pl_name in varchar2
448 ,p_coord_ben_med_insr_crr_name in varchar2
449 ,p_coord_ben_med_insr_crr_ident in varchar2
450 )
451 is
452 --
453 l_proc varchar2(72) := g_package||'chk_coord_ben_med_details';
454 --
455 begin
456 --
457 hr_utility.set_location('Entering:'|| l_proc, 10);
458 --
459 -- Check if the correct attributes have been set
460 -- If any of the attributes have been set for benefits coverage then at
461 -- minimum the insurance carrier name must be set.
462 --
463 if hr_multi_message.no_all_inclusive_error
464 (p_check_column1 => 'PER_ALL_PEOPLE_F.COORD_BEN_MED_CVG_STRT_DT'
465 ,p_check_column2 => 'PER_ALL_PEOPLE_F.COORD_BEN_MED_CVG_END_DT'
466 )
467 then
468 if p_coord_ben_med_insr_crr_name is null and
469 (p_coord_ben_med_cvg_strt_dt is not null or
470 p_coord_ben_med_cvg_end_dt is not null or
471 p_coord_ben_med_ext_er is not null or
472 p_coord_ben_med_pl_name is not null or
473 p_coord_ben_med_insr_crr_name is not null or
474 p_coord_ben_med_insr_crr_ident is not null) then
475 --
476 -- Error as benefit coverage info has been entered without the carrier
477 -- name being entered.
478 --
479 fnd_message.set_name('PER','HR_289112_BEN_MED_DETAILS');
480 fnd_message.raise_error;
481 --
482 end if;
483 end if;
484 --
485 hr_utility.set_location('Leaving '||l_proc,30);
486 exception
487 when app_exception.application_exception then
488 if hr_multi_message.exception_add
489 (p_associated_column1 => 'PER_ALL_PEOPLE_F.COORD_BEN_MED_INSR_CRR_NAME'
490 ) then
491 hr_utility.set_location(' Leaving:'||l_proc, 40);
492 raise;
493 end if;
494 hr_utility.set_location(' Leaving:'||l_proc,50);
495 --
496 end;
497 -- ---------------------------------------------------------------------------
498 -- |-----------------< chk_BACKGROUND_CHECK_STATUS >-----------------------|
499 -- ---------------------------------------------------------------------------
500 --
501 procedure chk_BACKGROUND_CHECK_STATUS
502 (p_person_id in per_all_people_f.person_id%TYPE
503 ,p_BACKGROUND_CHECK_STATUS in per_all_people_f.BACKGROUND_CHECK_STATUS%TYPE
504 ,p_effective_date in date
505 ,p_validation_start_date in date
506 ,p_validation_end_date in date
507 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
508 )
509 is
510 --
514 --
511 l_proc varchar2(72) := g_package||'chk_BACKGROUND_CHECK_STATUS';
512 l_api_updating boolean;
513 l_exists varchar2(1);
515 begin
516 hr_utility.set_location('Entering:'|| l_proc, 10);
517 --
518 -- Check mandatory parameters have been set
519 --
520 hr_api.mandatory_arg_error
521 (p_api_name => l_proc
522 ,p_argument => 'effective date'
523 ,p_argument_value => p_effective_date
524 );
525 --
526 hr_api.mandatory_arg_error
527 (p_api_name => l_proc
528 ,p_argument => 'validation_start_date'
529 ,p_argument_value => p_validation_start_date
530 );
531 --
532 hr_api.mandatory_arg_error
533 (p_api_name => l_proc
534 ,p_argument => 'validation_end_date'
535 ,p_argument_value => p_validation_end_date
536 );
537 --
538 -- Check if the person is being updated
539 --
540 l_api_updating := per_per_shd.api_updating
541 (p_person_id => p_person_id
542 ,p_effective_date => p_effective_date
543 ,p_object_version_number => p_object_version_number
544 );
545 hr_utility.set_location('Entering:'|| l_proc, 20);
546 --
547 if ((l_api_updating and
548 nvl(per_per_shd.g_old_rec.BACKGROUND_CHECK_STATUS, hr_api.g_varchar2)
549 <> nvl(p_BACKGROUND_CHECK_STATUS, hr_api.g_varchar2)) or
550 (NOT l_api_updating))
551 then
552 --
553 -- Check if Background Check Status is set.
554 --
555 if p_BACKGROUND_CHECK_STATUS is not null then
556 --
557 -- Check that the Background Check Status exists in hr_lookups for the
558 -- lookup type 'YES_NO' with an enabled flag set to 'Y' and that the
559 -- effective start date of the Person is between start date active and
560 -- end date active in hr_lookups.
561 --
562 if hr_api.not_exists_in_dt_hr_lookups
563 (p_effective_date => p_effective_date
564 ,p_validation_start_date => p_validation_start_date
565 ,p_validation_end_date => p_validation_end_date
566 ,p_lookup_type => 'YES_NO'
567 ,p_lookup_code => p_BACKGROUND_CHECK_STATUS
568 )
569 then
570 --
571 hr_utility.set_message(801, 'PER_52083_PER_INV_BK_CH_ST');
572 hr_utility.raise_error;
573 --
574 end if;
575 --
576 end if;
577 hr_utility.set_location(l_proc, 30);
578 --
579 end if;
580 --
581 hr_utility.set_location(' Leaving:'|| l_proc, 40);
582 exception
583 when app_exception.application_exception then
584 if hr_multi_message.exception_add
585 (p_associated_column1 => 'PER_ALL_PEOPLE_F.BACKGROUND_CHECK_STATUS'
586 ) then
587 hr_utility.set_location(' Leaving:'||l_proc, 50);
588 raise;
589 end if;
590 hr_utility.set_location(' Leaving:'||l_proc,60);
591 end chk_BACKGROUND_CHECK_STATUS;
592 --
593 -- ---------------------------------------------------------------------------
594 -- |--------------------------< chk_blood_type >---------------------------|
595 -- ---------------------------------------------------------------------------
596 --
597 procedure chk_blood_type
598 (p_person_id in per_all_people_f.person_id%TYPE
599 ,p_blood_type in per_all_people_f.blood_type%TYPE
600 ,p_effective_date in date
601 ,p_validation_start_date in date
602 ,p_validation_end_date in date
603 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
604 )
605 is
606 --
607 l_proc varchar2(72) := g_package||'chk_blood_type';
608 l_api_updating boolean;
609 l_exists varchar2(1);
610 --
611 begin
612 hr_utility.set_location('Entering:'|| l_proc, 10);
613 --
614 -- Check mandatory parameters have been set
615 --
616 hr_api.mandatory_arg_error
617 (p_api_name => l_proc
618 ,p_argument => 'effective date'
619 ,p_argument_value => p_effective_date
620 );
621 --
622 hr_api.mandatory_arg_error
623 (p_api_name => l_proc
624 ,p_argument => 'validation_start_date'
625 ,p_argument_value => p_validation_start_date
626 );
627 --
628 hr_api.mandatory_arg_error
629 (p_api_name => l_proc
630 ,p_argument => 'validation_end_date'
631 ,p_argument_value => p_validation_end_date
632 );
633 --
634 -- Check if the person is being updated
635 --
636 l_api_updating := per_per_shd.api_updating
637 (p_person_id => p_person_id
638 ,p_effective_date => p_effective_date
639 ,p_object_version_number => p_object_version_number
640 );
641 hr_utility.set_location('Entering:'|| l_proc, 20);
642 --
643 if ((l_api_updating and
644 nvl(per_per_shd.g_old_rec.blood_type, hr_api.g_varchar2)
645 <> nvl(p_blood_type, hr_api.g_varchar2)) or
646 (NOT l_api_updating))
647 then
648 --
649 -- Check if Blood Type is set.
650 --
654 -- lookup type 'BLOOD_TYPE' with an enabled flag set to 'Y' and that the
651 if p_blood_type is not null then
652 --
653 -- Check that the Blood Type exists in hr_lookups for the
655 -- effective start date of the Person is between start date active and
656 -- end date active in hr_lookups.
657 --
658 if hr_api.not_exists_in_dt_hr_lookups
659 (p_effective_date => p_effective_date
660 ,p_validation_start_date => p_validation_start_date
661 ,p_validation_end_date => p_validation_end_date
662 ,p_lookup_type => 'BLOOD_TYPE'
663 ,p_lookup_code => p_blood_type
664 )
665 then
666 --
667 hr_utility.set_message(800, 'PER_52111_PER_INV_BL_TYPE');
668 hr_utility.raise_error;
669 --
670 end if;
671 --
672 end if;
673 hr_utility.set_location(l_proc, 30);
674 --
675 end if;
676 --
677 hr_utility.set_location(' Leaving:'|| l_proc, 40);
678 exception
679 when app_exception.application_exception then
680 if hr_multi_message.exception_add
681 (p_associated_column1 => 'PER_ALL_PEOPLE_F.BLOOD_TYPE'
682 ) then
683 hr_utility.set_location(' Leaving:'||l_proc, 50);
684 raise;
685 end if;
686 hr_utility.set_location(' Leaving:'||l_proc,60);
687 end chk_blood_type;
688 --
689 -- ---------------------------------------------------------------------------
690 -- |-----------------< chk_student_status >-----------------------|
691 -- ---------------------------------------------------------------------------
692 --
693 procedure chk_student_status
694 (p_person_id in per_all_people_f.person_id%TYPE
695 ,p_student_status in per_all_people_f.student_status%TYPE
696 ,p_effective_date in date
697 ,p_validation_start_date in date
698 ,p_validation_end_date in date
699 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
700 )
701 is
702 --
703 l_proc varchar2(72) := g_package||'chk_student_status';
704 l_api_updating boolean;
705 l_exists varchar2(1);
706 --
707 begin
708 hr_utility.set_location('Entering:'|| l_proc, 10);
709 --
710 -- Check mandatory parameters have been set
711 --
712 hr_api.mandatory_arg_error
713 (p_api_name => l_proc
714 ,p_argument => 'effective date'
715 ,p_argument_value => p_effective_date
716 );
717 --
718 hr_api.mandatory_arg_error
719 (p_api_name => l_proc
720 ,p_argument => 'validation_start_date'
721 ,p_argument_value => p_validation_start_date
722 );
723 --
724 hr_api.mandatory_arg_error
725 (p_api_name => l_proc
726 ,p_argument => 'validation_end_date'
727 ,p_argument_value => p_validation_end_date
728 );
729 --
730 -- Check if the person is being updated
731 --
732 l_api_updating := per_per_shd.api_updating
733 (p_person_id => p_person_id
734 ,p_effective_date => p_effective_date
735 ,p_object_version_number => p_object_version_number
736 );
737 hr_utility.set_location('Entering:'|| l_proc, 20);
738 --
739 if ((l_api_updating and
740 nvl(per_per_shd.g_old_rec.student_status, hr_api.g_varchar2)
741 <> nvl(p_student_status, hr_api.g_varchar2)) or
742 (NOT l_api_updating))
743 then
744 --
745 -- Check if Student Status is set.
746 --
747 if p_student_status is not null then
748 --
749 -- Check that the Student Status exists in hr_lookups for the
750 -- lookup type 'STUDENT_STATUS' with an enabled flag set to 'Y' and that the
751 -- effective start date of the Person is between start date active and
752 -- end date active in hr_lookups.
753 --
754 if hr_api.not_exists_in_dt_hr_lookups
755 (p_effective_date => p_effective_date
756 ,p_validation_start_date => p_validation_start_date
757 ,p_validation_end_date => p_validation_end_date
758 ,p_lookup_type => 'STUDENT_STATUS'
759 ,p_lookup_code => p_student_status
760 )
761 then
762 --
763 hr_utility.set_message(800, 'PER_52112_PER_INV_STUD_STAT');
764 hr_utility.raise_error;
765 --
766 end if;
767 --
768 end if;
769 hr_utility.set_location(l_proc, 30);
770 --
771 end if;
772 --
773 hr_utility.set_location(' Leaving:'|| l_proc, 40);
774 exception
775 when app_exception.application_exception then
776 if hr_multi_message.exception_add
777 (p_associated_column1 => 'PER_ALL_PEOPLE_F.STUDENT_STATUS'
778 ) then
779 hr_utility.set_location(' Leaving:'||l_proc,50);
780 raise;
781 end if;
782 hr_utility.set_location(' Leaving:'||l_proc,60);
783 end chk_student_status;
784 --
785 -- ---------------------------------------------------------------------------
786 -- |-------------------------< chk_work_schedule >-------------------------|
790 (p_person_id in per_all_people_f.person_id%TYPE
787 -- ---------------------------------------------------------------------------
788 --
789 procedure chk_work_schedule
791 ,p_work_schedule in per_all_people_f.work_schedule%TYPE
792 ,p_effective_date in date
793 ,p_validation_start_date in date
794 ,p_validation_end_date in date
795 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
796 )
797 is
798 --
799 l_proc varchar2(72) := g_package||'chk_work_schedule';
800 l_api_updating boolean;
801 l_exists varchar2(1);
802 --
803 begin
804 hr_utility.set_location('Entering:'|| l_proc, 10);
805 --
806 -- Check mandatory parameters have been set
807 --
808 hr_api.mandatory_arg_error
809 (p_api_name => l_proc
810 ,p_argument => 'effective date'
811 ,p_argument_value => p_effective_date
812 );
813 --
814 hr_api.mandatory_arg_error
815 (p_api_name => l_proc
816 ,p_argument => 'validation_start_date'
817 ,p_argument_value => p_validation_start_date
818 );
819 --
820 hr_api.mandatory_arg_error
821 (p_api_name => l_proc
822 ,p_argument => 'validation_end_date'
823 ,p_argument_value => p_validation_end_date
824 );
825 --
826 -- Check if the person is being updated
827 --
828 l_api_updating := per_per_shd.api_updating
829 (p_person_id => p_person_id
830 ,p_effective_date => p_effective_date
831 ,p_object_version_number => p_object_version_number
832 );
833 hr_utility.set_location('Entering:'|| l_proc, 20);
834 --
835 if ((l_api_updating and
836 nvl(per_per_shd.g_old_rec.work_schedule, hr_api.g_varchar2)
837 <> nvl(p_work_schedule, hr_api.g_varchar2)) or
838 (NOT l_api_updating))
839 then
840 --
841 -- Check if Work Schedule is set.
842 --
843 if p_work_schedule is not null then
844 --
845 -- Check that the Work Schedule exists in hr_lookups for the
846 -- lookup type 'WORK_SCHEDULE' with an enabled flag set to 'Y' and that the
847 -- effective start date of the Person is between start date active and
848 -- end date active in hr_lookups.
849 --
850 if hr_api.not_exists_in_dt_hr_lookups
851 (p_effective_date => p_effective_date
852 ,p_validation_start_date => p_validation_start_date
853 ,p_validation_end_date => p_validation_end_date
854 ,p_lookup_type => 'WORK_SCHEDULE'
855 ,p_lookup_code => p_work_schedule
856 )
857 then
858 --
859 hr_utility.set_message(800, 'PER_52113_PER_INV_WK_SCHD');
860 hr_utility.raise_error;
861 --
862 end if;
863 --
864 end if;
865 hr_utility.set_location(l_proc, 30);
866 --
867 end if;
868 --
869 hr_utility.set_location(' Leaving:'|| l_proc, 40);
870 exception
871 when app_exception.application_exception then
872 if hr_multi_message.exception_add
873 (p_associated_column1 => 'PER_ALL_PEOPLE_F.WORK_SCHEDULE'
874 ) then
875 hr_utility.set_location(' Leaving:'||l_proc, 50);
876 raise;
877 end if;
878 hr_utility.set_location(' Leaving:'||l_proc,60);
879 end chk_work_schedule;
880 --
881 -- ---------------------------------------------------------------------------
882 -- |-------------------< chk_rehire_recommendation >-----------------------|
883 -- ---------------------------------------------------------------------------
884 --
885 procedure chk_rehire_recommendation
886 (p_person_id in per_all_people_f.person_id%TYPE
887 ,p_rehire_recommendation in per_all_people_f.rehire_recommendation%TYPE
888 ,p_effective_date in date
889 ,p_validation_start_date in date
890 ,p_validation_end_date in date
891 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
892 )
893 is
894 --
895 l_proc varchar2(72) := g_package||'chk_rehire_recommendation';
896 l_api_updating boolean;
897 l_exists varchar2(1);
898 --
899 begin
900 hr_utility.set_location('Entering:'|| l_proc, 10);
901 --
902 -- Check mandatory parameters have been set
903 --
904 hr_api.mandatory_arg_error
905 (p_api_name => l_proc
906 ,p_argument => 'effective date'
907 ,p_argument_value => p_effective_date
908 );
909 --
910 hr_api.mandatory_arg_error
911 (p_api_name => l_proc
912 ,p_argument => 'validation_start_date'
913 ,p_argument_value => p_validation_start_date
914 );
915 --
916 hr_api.mandatory_arg_error
917 (p_api_name => l_proc
918 ,p_argument => 'validation_end_date'
919 ,p_argument_value => p_validation_end_date
920 );
921 --
922 -- Check if the person is being updated
923 --
924 l_api_updating := per_per_shd.api_updating
925 (p_person_id => p_person_id
929 hr_utility.set_location('Entering:'|| l_proc, 20);
926 ,p_effective_date => p_effective_date
927 ,p_object_version_number => p_object_version_number
928 );
930 --
931 if ((l_api_updating and
932 nvl(per_per_shd.g_old_rec.rehire_recommendation, hr_api.g_varchar2)
933 <> nvl(p_rehire_recommendation, hr_api.g_varchar2)) or
934 (NOT l_api_updating))
935 then
936 --
937 -- Check if Rehire Recommendation is set.
938 --
939 if p_rehire_recommendation is not null then
940 --
941 -- Check that the Rehire Recommendation exists in hr_lookups for the
942 -- lookup type 'YES_NO' with an enabled flag set to 'Y' and that the
943 -- effective start date of the Person is between start date active and
944 -- end date active in hr_lookups.
945 --
946 if hr_api.not_exists_in_dt_hr_lookups
947 (p_effective_date => p_effective_date
948 ,p_validation_start_date => p_validation_start_date
949 ,p_validation_end_date => p_validation_end_date
950 ,p_lookup_type => 'YES_NO'
951 ,p_lookup_code => p_rehire_recommendation
952 )
953 then
954 --
955 hr_utility.set_message(800, 'PER_52114_PER_INV_REH_REC');
956 hr_utility.raise_error;
957 --
958 end if;
959 --
960 end if;
961 hr_utility.set_location(l_proc, 30);
962 --
963 end if;
964 --
965 hr_utility.set_location(' Leaving:'|| l_proc, 40);
966 exception
967 when app_exception.application_exception then
968 if hr_multi_message.exception_add
969 (p_associated_column1 => 'PER_ALL_PEOPLE_F.REHIRE_RECOMMENDATION'
970 ) then
971 hr_utility.set_location(' Leaving:'||l_proc, 50);
972 raise;
973 end if;
974 hr_utility.set_location(' Leaving:'||l_proc,60);
975 end chk_rehire_recommendation;
976 -- ----------------------------------------------------------------------------
977 -- |----------------------< chk_benefit_group_id >----------------------------|
978 -- ----------------------------------------------------------------------------
979 --
980 --
981 -- Description:
982 -- - Validates that benefit_group_id exists in BEN_BENFTS_GRP where
983 -- on the effective date.
984 --
985 -- Pre-conditions:
986 -- Valid p_person_id.
987 --
988 -- In Arguments:
989 -- p_person_id
990 -- p_benefit_group_id
991 -- p_effective_date
992 -- p_object_version_number
993 --
994 -- Post Success:
995 -- Processing continues if:
996 -- - benefit_group_id = benfts_grp_id in the BEN_BENFTS_GRP on the
997 -- effective date.
998 --
999 -- Post Failure:
1000 -- An application error is raised and processing is terminated if:
1001 -- - Benefit group id doesn't exist in BEN_BENFTS_GRP on the
1002 -- effective date.
1003 --
1004 -- Access Status:
1005 -- Internal Table Handler Use Only.
1006
1007 procedure chk_benefit_group_id
1008 (p_person_id in per_all_people_f.person_id%TYPE
1009 ,p_benefit_group_id in per_all_people_f.benefit_group_id%TYPE
1010 ,p_effective_date in date
1011 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
1012 )
1013 is
1014 --
1015 cursor csr_chk_benefit_group_id is
1016 select null
1017 from ben_benfts_grp
1018 where benfts_grp_id = p_benefit_group_id;
1019 --
1020 l_proc varchar2(72) := g_package||'chk_benefit_group_id';
1021 l_benefit_group_id number;
1022 --
1023 begin
1024 hr_utility.set_location('Entering:'|| l_proc, 10);
1025 --
1026 -- Check mandatory parameters have been set
1027 --
1028 hr_api.mandatory_arg_error
1029 (p_api_name => l_proc
1030 ,p_argument => 'effective_date'
1031 ,p_argument_value => p_effective_date
1032 );
1033 hr_utility.set_location(l_proc, 20);
1034 --
1035 if p_benefit_group_id is NOT NULL then
1036 open csr_chk_benefit_group_id;
1037 fetch csr_chk_benefit_group_id into l_benefit_group_id;
1038 if csr_chk_benefit_group_id%NOTFOUND then
1039 close csr_chk_benefit_group_id;
1040 hr_utility.set_message(800, 'PER_52385_BEN_GROUP_ID');
1041 hr_utility.raise_error;
1042 end if;
1043 close csr_chk_benefit_group_id;
1044 --
1045 end if;
1046 --
1047 hr_utility.set_location(' Leaving:'||l_proc, 30);
1048 --
1049 exception
1050 when app_exception.application_exception then
1051 if hr_multi_message.exception_add
1052 (p_associated_column1 => 'PER_ALL_PEOPLE_F.BENEFIT_GROUP_ID'
1053 ) then
1054 hr_utility.set_location(' Leaving:'||l_proc, 40);
1055 raise;
1056 end if;
1057 hr_utility.set_location(' Leaving:'||l_proc,50);
1058 end chk_benefit_group_id;
1059 --
1060 -- ----------------------------------------------------------------------------
1061 -- |----------------------< chk_date_death_and_rcpt_cert >--------------------|
1062 -- ----------------------------------------------------------------------------
1063 --
1064 -- Description:
1065 -- - Validates that if the date of death is null the date of recipt of death
1069 -- Pre-conditions:
1066 -- certificate is also null. Also validates that the date the death
1067 -- certificate is received is the same or later than the date of death.
1068 --
1070 -- Valid p_person_id
1071 --
1072 -- In Arguments:
1073 -- p_person_id
1074 -- p_receipt_of_death_cert_date
1075 -- p_effective_date
1076 -- p_date_of_death
1077 -- p_object_version_number
1078 --
1079 -- Post Success:
1080 -- Processing continues if:
1081 -- - date_of_death is null and the receipt_of_death_cert_date is null.
1082 -- - receipt_of_death_cert_date is on or later than the date_of_death.
1083 --
1084 -- Post Failure:
1085 -- An application error is raised and processing is terminated if:
1086 -- - The receipt_of_death_cert date is not null and the date_of_death is null.
1087 -- - The receipt_of_death_cert_date is earlier than the date_of_death.
1088 --
1089 -- Access Status:
1090 -- Internal Table Handler Use Only.
1091 --
1092 procedure chk_date_death_and_rcpt_cert
1093 (p_person_id in per_all_people_f.person_id%TYPE
1094 ,p_receipt_of_death_cert_date in per_all_people_f.receipt_of_death_cert_date%TYPE
1095 ,p_effective_date in date
1096 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
1097 ,p_date_of_death in date
1098 )
1099 is
1100 --
1101 l_proc varchar2(72) := g_package||'chk_date_death_and_rcpt_cert';
1102 --
1103 begin
1104 --
1105 hr_utility.set_location('Entering:'|| l_proc,5);
1106 --
1107 if p_receipt_of_death_cert_date is NOT NULL then
1108 if p_date_of_death is NULL then
1109 hr_utility.set_message(800,'PER_52424_DATE_DEATH_RCPT_CERT');
1110 hr_multi_message.add
1111 (p_associated_column1 => 'PER_ALL_PEOPLE_F.RECEIPT_OF_DEATH_CERT_DATE'
1112 ,p_associated_column2 => 'PER_ALL_PEOPLE_F.DATE_OF_DEATH'
1113 );
1114 elsif p_receipt_of_death_cert_date <= nvl(p_date_of_death, hr_api.g_date) then
1115 hr_utility.set_message(800,'PER_52962_CERT_DTE_LT_DOD');
1116 hr_multi_message.add
1117 (p_associated_column1 => 'PER_ALL_PEOPLE_F.RECEIPT_OF_DEATH_CERT_DATE'
1118 ,p_associated_column2 => 'PER_ALL_PEOPLE_F.DATE_OF_DEATH'
1119 );
1120 end if;
1121 end if;
1122 --
1123 hr_utility.set_location(' Leaving:'||l_proc, 10);
1124 --
1125 end chk_date_death_and_rcpt_cert;
1126
1127 -- ----------------------------------------------------------------------------
1128 -- |----------------------< chk_birth_adoption_date >---------------------|
1129 -- ----------------------------------------------------------------------------
1130 --
1131 -- Description:
1132 -- - Validates that if the date of birth is null then the dependent's adoption
1133 -- date is also null. Also validates that the dependent's date of adoption
1134 -- is the same or later than the date of birth.
1135 --
1136 -- Pre-conditions:
1137 -- Valid p_person_id
1138 --
1139 -- In Arguments:
1140 -- p_person_id
1141 -- p_dpdnt_adoption_date
1142 -- p_effective_date
1143 -- p_date_of_birth
1144 -- p_object_version_number
1145 --
1146 -- Post Success:
1147 -- Processing continues if:
1148 -- - dpdnt_adoption_date is null if date_of_birth is null
1149 -- - dpdnt_adoption_date is on or later than the date_of_birth.
1150 --
1151 -- Post Failure:
1152 -- An application error is raised and processing is terminated if:
1153 -- - The dpdnt_adoption_date is not null and the date_of_birth is null.
1154 -- - The dpdnt_adoption_date is earlier than the date_of_birth.
1155 --
1156 -- Access Status:
1157 -- Internal Table Handler Use Only.
1158 --
1159 procedure chk_birth_adoption_date
1160 (p_person_id in per_all_people_f.person_id%TYPE
1161 ,p_dpdnt_adoption_date in per_all_people_f.dpdnt_adoption_date%TYPE
1162 ,p_date_of_birth in date
1163 ,p_effective_date in date
1164 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
1165 )
1166 is
1167 --
1168 l_proc varchar2(72) := g_package||'chk_birth_adoption_date';
1169 --
1170 begin
1171 --
1172 hr_utility.set_location('Entering:'|| l_proc,5);
1173 --
1174 if p_dpdnt_adoption_date is NOT NULL then
1175 if p_date_of_birth IS NULL then
1176 hr_utility.set_message(800,'PER_52425_DATE_BIRTH_ADOPTION');
1177 hr_multi_message.add
1178 (p_associated_column1 => 'PER_ALL_PEOPLE_F.DPDNT_ADOPTION_DATE'
1179 ,p_associated_column2 => 'PER_ALL_PEOPLE_F.DATE_OF_BIRTH'
1180 );
1181
1182 -- bug fix : 2164967 begins
1183
1184 elsif p_dpdnt_adoption_date < nvl(p_date_of_birth, hr_api.g_date) then
1185 hr_utility.set_message(800,'PER_52961_ADOPT_LT_DOB');
1186 hr_multi_message.add
1187 (p_associated_column1 => 'PER_ALL_PEOPLE_F.DPDNT_ADOPTION_DATE'
1188 ,p_associated_column2 => 'PER_ALL_PEOPLE_F.DATE_OF_BIRTH'
1189 );
1190
1191 -- bug fix : 2164967 ends
1192
1193 end if;
1194 end if;
1195 --
1196 hr_utility.set_location(' Leaving:'||l_proc, 10);
1197 --
1198 end chk_birth_adoption_date;
1199 --
1200 -- ----------------------------------------------------------------------------
1204 -- Description:
1201 -- |----------------------< chk_date_of_death >-------------------------------|
1202 -- ----------------------------------------------------------------------------
1203 --
1205 -- - Validates that the date of death is the same or later than the
1206 -- date of birth.
1207 --
1208 -- Pre-conditions:
1209 -- Valid p_person_id.
1210 --
1211 -- In Arguments:
1212 -- p_person_id
1213 -- p_date_of_death
1214 -- p_effective_date
1215 -- p_date_of_birth
1216 -- p_object_version_number
1217 --
1218 -- Post Success:
1219 -- Processing continues if:
1220 -- - date_of_death is on or later than the date_of_birth.
1221 --
1222 -- Post Failure:
1223 -- An application error is raised and processing is terminated if:
1224 -- - The date_of_death is earlier than the date_of_birth.
1225 --
1226 -- Access Status:
1227 -- Internal Table Handler Use Only.
1228 --
1229 procedure chk_date_of_death
1230 (p_person_id in per_all_people_f.person_id%TYPE
1231 ,p_date_of_death in per_all_people_f.date_of_death%TYPE
1232 ,p_date_of_birth in per_all_people_f.date_of_birth%TYPE
1233 ,p_effective_date in date
1234 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
1235 )
1236 is
1237 --
1238 l_proc varchar2(72) := g_package||'chk_date_of_death';
1239 --
1240 --fix for bug 4262496 starts here.
1241 l_con_start_date date;
1242
1243 cursor csr_chk_sd is
1244 select date_start
1245 from per_contact_relationships
1246 where contact_person_id=p_person_id;
1247 --fix for bug 4262496 ends here.
1248 begin
1249 --
1250 hr_utility.set_location('Entering:'|| l_proc,5);
1251 if hr_multi_message.no_exclusive_error
1252 (p_check_column1 => 'PER_ALL_PEOPLE_F.DATE_OF_BIRTH'
1253 ,p_check_column2 => 'PER_ALL_PEOPLE_F.DATE_OF_DEATH'
1254 ) then
1255 if p_date_of_death is NOT NULL then
1256 if p_date_of_death < nvl(p_date_of_birth, hr_api.g_date) then --fix for bug 4262496.
1257 hr_utility.set_message(800,'PER_52963_DOD_LT_DOB');
1258 hr_utility.raise_error;
1259 end if;
1260 end if;
1261 --fix for bug 4262496 starts here.
1262 open csr_chk_sd;
1263 loop
1264 fetch csr_chk_sd into l_con_start_date;
1265 exit when csr_chk_sd%notfound ;
1266 if p_date_of_death < l_con_start_date then
1267 hr_utility.set_message(800,'HR_449686_RELATION_EXISTS');
1268 hr_utility.raise_error;
1269
1270 end if;
1271 end loop;
1272 close csr_chk_sd;
1273 --fix for bug 4262496 ends here.
1274 end if;
1275 --
1276 hr_utility.set_location(' Leaving:'||l_proc, 10);
1277 --
1278 exception
1279 when app_exception.application_exception then
1280 if hr_multi_message.exception_add
1281 (p_associated_column1 => 'PER_ALL_PEOPLE_F.DATE_OF_BIRTH'
1282 ,p_associated_column2 => 'PER_ALL_PEOPLE_F.DATE_OF_DEATH'
1283 ) then
1284 hr_utility.set_location(' Leaving:'||l_proc, 20);
1285 raise;
1286 end if;
1287 hr_utility.set_location(' Leaving:'||l_proc,30);
1288 end chk_date_of_death;
1289 --
1290 -- ---------------------------------------------------------------------------
1291 -- |-----------------< chk_rd_flag >----------------------------------------|
1292 -- ---------------------------------------------------------------------------
1293 --
1294 -- Description:
1295 -- - Validates that registered disabled exists as a lookup code on
1296 -- HR_LOOKUPS for the lookup type 'REGISTERED_DISABLED' with an enabled
1297 -- flag set to 'Y' and the effective start date of the Person between
1298 -- start date active and end date active on HR_LOOKUPS.
1299 --
1300 -- Pre-conditions:
1301 -- Valid p_person_id.
1302 --
1303 -- In Arguments:
1304 -- p_person_id
1305 -- p_registered_disabled_flag
1306 -- p_effective_date
1307 -- p_validation_start_date
1308 -- p_validation_end_date
1309 -- p_object_version_number
1310 --
1311 -- Post Success:
1312 -- Processing continues if:
1313 -- - Student Status exists as a lookup code in HR_LOOKUPS
1314 -- for the lookup type 'REGISTERED_DISABLED' where the enabled flag is 'Y' and
1315 -- the effective start date of the Person is between start date
1316 -- active and end date active on HR_LOOKUPS.
1317 --
1318 -- Post Failure:
1319 -- An application error is raised and processing is terminated if:
1320 -- - Registered_disabled doesn't exist as a lookup code in
1321 -- HR_LOOKUPS for the lookup type 'REGISTERED_DISABLED' where the enabled flag
1322 -- is 'Y' and the effective start date of the person is between
1323 -- start date active and end date active on HR_LOOKUPS.
1324 --
1325 -- Access Status:
1326 -- Internal Table Handler Use Only.
1327 --
1328 procedure chk_rd_flag
1329 (p_person_id in per_all_people_f.person_id%TYPE
1330 ,p_registered_disabled_flag in per_all_people_f.registered_disabled_flag%TYPE
1331 ,p_effective_date in date
1332 ,p_validation_start_date in date
1333 ,p_validation_end_date in date
1334 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
1335 )
1336 is
1340 --
1337 --
1338 l_proc varchar2(72) := g_package||'chk_rd_flag';
1339 l_exists varchar2(1);
1341 begin
1342 hr_utility.set_location('Entering:'|| l_proc, 10);
1343 --
1344 -- Check mandatory parameters have been set
1345 --
1346 hr_api.mandatory_arg_error
1347 (p_api_name => l_proc
1348 ,p_argument => 'effective date'
1349 ,p_argument_value => p_effective_date
1350 );
1351 --
1352 hr_api.mandatory_arg_error
1353 (p_api_name => l_proc
1354 ,p_argument => 'validation_start_date'
1355 ,p_argument_value => p_validation_start_date
1356 );
1357 --
1358 hr_api.mandatory_arg_error
1359 (p_api_name => l_proc
1360 ,p_argument => 'validation_end_date'
1361 ,p_argument_value => p_validation_end_date
1362 );
1363 hr_utility.set_location('Entering:'|| l_proc, 20);
1364 --
1365 -- Check if registered_disabled_flag is set.
1366 --
1367 if p_registered_disabled_flag is not null then
1368 --
1369 -- Check that the registered_disabled_flag exists in hr_lookups for the
1370 -- lookup type 'REGISTERED_DISABLED' with an enabled flag set to 'Y' and that the
1371 -- effective start date of the Person is between start date active and
1372 -- end date active in hr_lookups.
1373 --
1374 if hr_api.not_exists_in_dt_hr_lookups
1375 (p_effective_date => p_effective_date
1376 ,p_validation_start_date => p_validation_start_date
1377 ,p_validation_end_date => p_validation_end_date
1378 ,p_lookup_type => 'REGISTERED_DISABLED'
1379 ,p_lookup_code => p_registered_disabled_flag
1380 )
1381 then
1382 --
1383 hr_utility.set_message(800, 'PER_52386_REG_DISABLED');
1384 hr_utility.raise_error;
1385 --
1386 end if;
1387 --
1388 end if;
1389 hr_utility.set_location(l_proc, 30);
1390 --
1391 --
1392 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1393 exception
1394 when app_exception.application_exception then
1395 if hr_multi_message.exception_add
1396 (p_associated_column1 => 'PER_ALL_PEOPLE_F.REGISTERED_DISABLED_FLAG'
1397 ) then
1398 hr_utility.set_location(' Leaving:'||l_proc, 50);
1399 raise;
1400 end if;
1401 hr_utility.set_location(' Leaving:'||l_proc,60);
1402 end chk_rd_flag;
1403 --
1404 -- ---------------------------------------------------------------------------
1405 -- |-------------------------< chk_uses_tobacco >---------------------------|
1406 -- ---------------------------------------------------------------------------
1407 --
1408 -- Description:
1409 -- - Validates that Uses Tobacco exists as a lookup code on
1410 -- HR_LOOKUPS for the lookup type 'TOBACCO_USER' with an enabled
1411 -- flag set to 'Y' and the effective start date of the Person between
1412 -- start date active and end date active on HR_LOOKUPS.
1413 --
1414 -- Pre-conditions:
1415 -- Valid p_person_id.
1416 --
1417 -- In Arguments:
1418 -- p_person_id
1419 -- p_uses_tobacco_flag
1420 -- p_effective_date
1421 -- p_validation_start_date
1422 -- p_validation_end_date
1423 -- p_object_version_number
1424 --
1425 -- Post Success:
1426 -- Processing continues if:
1427 -- - Uses tobacco exists as a lookup code in HR_LOOKUPS
1428 -- for the lookup type 'TOBACCO_USER' where the enabled flag is 'Y' and
1429 -- the effective start date of the Person is between start date
1430 -- active and end date active on HR_LOOKUPS.
1431 --
1432 -- Post Failure:
1433 -- An application error is raised and processing is terminated if:
1434 -- - Uses Tobacco doesn't exist as a lookup code in
1435 -- HR_LOOKUPS for the lookup type 'TOBACCO_USER' where the enabled flag
1436 -- is 'Y' and the effective start date of the person is between
1437 -- start date active and end date active on HR_LOOKUPS.
1438 --
1439 -- Access Status:
1440 -- Internal Table Handler Use Only.
1441 --
1442 procedure chk_uses_tobacco
1443 (p_person_id in per_all_people_f.person_id%TYPE
1444 ,p_uses_tobacco_flag in per_all_people_f.uses_tobacco_flag%TYPE
1445 ,p_effective_date in date
1446 ,p_validation_start_date in date
1447 ,p_validation_end_date in date
1448 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
1449 )
1450 is
1451 --
1452 l_proc varchar2(72) := g_package||'chk_uses_tobacco';
1453 l_exists varchar2(1);
1454 --
1455 begin
1456 hr_utility.set_location('Entering:'|| l_proc, 10);
1457 --
1458 -- Check mandatory parameters have been set
1459 --
1460 hr_api.mandatory_arg_error
1461 (p_api_name => l_proc
1462 ,p_argument => 'effective date'
1463 ,p_argument_value => p_effective_date
1464 );
1465 --
1466 hr_api.mandatory_arg_error
1467 (p_api_name => l_proc
1468 ,p_argument => 'validation_start_date'
1469 ,p_argument_value => p_validation_start_date
1470 );
1471 --
1472 hr_api.mandatory_arg_error
1473 (p_api_name => l_proc
1474 ,p_argument => 'validation_end_date'
1475 ,p_argument_value => p_validation_end_date
1476 );
1480 --
1477 hr_utility.set_location('Entering:'|| l_proc, 20);
1478 --
1479 -- Check if uses_tobacco_flag is set.
1481 if p_uses_tobacco_flag is not null then
1482 --
1483 -- Check that the uses_tobacco_flag exists in hr_lookups for the
1484 -- lookup type 'TOBACCO_USER' with an enabled flag set to 'Y' and that the
1485 -- effective start date of the Person is between start date active and
1486 -- end date active in hr_lookups.
1487 --
1488 if hr_api.not_exists_in_dt_hr_lookups
1489 (p_effective_date => p_effective_date
1490 ,p_validation_start_date => p_validation_start_date
1491 ,p_validation_end_date => p_validation_end_date
1492 ,p_lookup_type => 'TOBACCO_USER'
1493 ,p_lookup_code => p_uses_tobacco_flag
1494 )
1495 then
1496 --
1497 hr_utility.set_message(800, 'PER_52388_USES_TOBACCO_F');
1498 hr_utility.raise_error;
1499 --
1500 end if;
1501 --
1502 end if;
1503 hr_utility.set_location(l_proc, 30);
1504 --
1505 --
1506 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1507 exception
1508 when app_exception.application_exception then
1509 if hr_multi_message.exception_add
1510 (p_associated_column1 => 'PER_ALL_PEOPLE_F.USES_TOBACCO_FLAG'
1511 ) then
1512 hr_utility.set_location(' Leaving:'||l_proc, 50);
1513 raise;
1514 end if;
1515 hr_utility.set_location(' Leaving:'||l_proc,60);
1516 end chk_uses_tobacco;
1517 --
1518 end per_per_bus1;
[Home] [Help]