1 Package Body irc_ipd_bus as
2 /* $Header: iripdrhi.pkb 120.0 2005/07/26 15:09:42 mbocutt noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_ipd_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_pending_data_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_pending_data_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare local variables
26 --
27 l_security_group_id number;
28 l_proc varchar2(72) := g_package||'set_security_group_id';
29 l_legislation_code varchar2(150);
30 --
31 begin
32 --
33 hr_utility.set_location('Entering:'|| l_proc, 10);
34 --
35 -- Ensure that all the mandatory parameter are not null
36 --
37 hr_api.mandatory_arg_error
38 (p_api_name => l_proc
39 ,p_argument => 'pending_data_id'
40 ,p_argument_value => p_pending_data_id
41 );
42 --
43 --
44 hr_utility.set_location(' Leaving:'|| l_proc, 20);
45 --
46 end set_security_group_id;
47 --
48 -- ---------------------------------------------------------------------------
49 -- |---------------------< return_legislation_code >-------------------------|
50 -- ---------------------------------------------------------------------------
51 --
52 Function return_legislation_code
53 (p_pending_data_id in number
54 )
55 Return Varchar2 Is
56 --
57 -- Declare local variables
58 --
59 l_legislation_code varchar2(150) := 'NONE';
60 l_proc varchar2(72) := g_package||'return_legislation_code';
61 --
62 Begin
63 --
64 hr_utility.set_location('Entering:'|| l_proc, 10);
65 --
66 -- Ensure that all the mandatory parameter are not null
67 --
68 hr_api.mandatory_arg_error
69 (p_api_name => l_proc
70 ,p_argument => 'pending_data_id'
71 ,p_argument_value => p_pending_data_id
72 );
73 --
74 hr_utility.set_location(' Leaving:'|| l_proc, 40);
75 return l_legislation_code;
76 end return_legislation_code;
77 --
78 -- ----------------------------------------------------------------------------
79 -- |-----------------------------< chk_ddf >----------------------------------|
80 -- ----------------------------------------------------------------------------
81 --
82 -- Description:
83 -- Validates all the Developer Descriptive Flexfield values.
84 --
85 -- Prerequisites:
86 -- All other columns have been validated. Must be called as the
87 -- last step from insert_validate and update_validate.
88 --
89 -- In Arguments:
90 -- p_rec
91 --
92 -- Post Success:
93 -- If the Developer Descriptive Flexfield structure column and data values
94 -- are all valid this procedure will end normally and processing will
95 -- continue.
96 --
97 -- Post Failure:
98 -- If the Developer Descriptive Flexfield structure column value or any of
99 -- the data values are invalid then an application error is raised as
100 -- a PL/SQL exception.
101 --
102 -- Access Status:
103 -- Internal Row Handler Use Only.
104 --
105 -- ----------------------------------------------------------------------------
106 procedure chk_ddf
107 (p_rec in irc_ipd_shd.g_rec_type
108 ) is
109 --
110 l_proc varchar2(72) := g_package || 'chk_ddf';
111 --
112 begin
113 hr_utility.set_location('Entering:'||l_proc,10);
114 --
115 if ((p_rec.pending_data_id is not null) and (
116 nvl(irc_ipd_shd.g_old_rec.per_information_category, hr_api.g_varchar2) <>
117 nvl(p_rec.per_information_category, hr_api.g_varchar2) or
118 nvl(irc_ipd_shd.g_old_rec.per_information1, hr_api.g_varchar2) <>
119 nvl(p_rec.per_information1, hr_api.g_varchar2) or
120 nvl(irc_ipd_shd.g_old_rec.per_information2, hr_api.g_varchar2) <>
121 nvl(p_rec.per_information2, hr_api.g_varchar2) or
122 nvl(irc_ipd_shd.g_old_rec.per_information3, hr_api.g_varchar2) <>
123 nvl(p_rec.per_information3, hr_api.g_varchar2) or
124 nvl(irc_ipd_shd.g_old_rec.per_information4, hr_api.g_varchar2) <>
125 nvl(p_rec.per_information4, hr_api.g_varchar2) or
126 nvl(irc_ipd_shd.g_old_rec.per_information5, hr_api.g_varchar2) <>
127 nvl(p_rec.per_information5, hr_api.g_varchar2) or
128 nvl(irc_ipd_shd.g_old_rec.per_information6, hr_api.g_varchar2) <>
129 nvl(p_rec.per_information6, hr_api.g_varchar2) or
130 nvl(irc_ipd_shd.g_old_rec.per_information7, hr_api.g_varchar2) <>
131 nvl(p_rec.per_information7, hr_api.g_varchar2) or
132 nvl(irc_ipd_shd.g_old_rec.per_information8, hr_api.g_varchar2) <>
133 nvl(p_rec.per_information8, hr_api.g_varchar2) or
134 nvl(irc_ipd_shd.g_old_rec.per_information9, hr_api.g_varchar2) <>
135 nvl(p_rec.per_information9, hr_api.g_varchar2) or
136 nvl(irc_ipd_shd.g_old_rec.per_information10, hr_api.g_varchar2) <>
137 nvl(p_rec.per_information10, hr_api.g_varchar2) or
138 nvl(irc_ipd_shd.g_old_rec.per_information11, hr_api.g_varchar2) <>
139 nvl(p_rec.per_information11, hr_api.g_varchar2) or
140 nvl(irc_ipd_shd.g_old_rec.per_information12, hr_api.g_varchar2) <>
141 nvl(p_rec.per_information12, hr_api.g_varchar2) or
142 nvl(irc_ipd_shd.g_old_rec.per_information13, hr_api.g_varchar2) <>
143 nvl(p_rec.per_information13, hr_api.g_varchar2) or
144 nvl(irc_ipd_shd.g_old_rec.per_information14, hr_api.g_varchar2) <>
145 nvl(p_rec.per_information14, hr_api.g_varchar2) or
146 nvl(irc_ipd_shd.g_old_rec.per_information15, hr_api.g_varchar2) <>
147 nvl(p_rec.per_information15, hr_api.g_varchar2) or
148 nvl(irc_ipd_shd.g_old_rec.per_information16, hr_api.g_varchar2) <>
149 nvl(p_rec.per_information16, hr_api.g_varchar2) or
150 nvl(irc_ipd_shd.g_old_rec.per_information17, hr_api.g_varchar2) <>
151 nvl(p_rec.per_information17, hr_api.g_varchar2) or
152 nvl(irc_ipd_shd.g_old_rec.per_information18, hr_api.g_varchar2) <>
153 nvl(p_rec.per_information18, hr_api.g_varchar2) or
154 nvl(irc_ipd_shd.g_old_rec.per_information19, hr_api.g_varchar2) <>
155 nvl(p_rec.per_information19, hr_api.g_varchar2) or
156 nvl(irc_ipd_shd.g_old_rec.per_information20, hr_api.g_varchar2) <>
157 nvl(p_rec.per_information20, hr_api.g_varchar2) or
158 nvl(irc_ipd_shd.g_old_rec.per_information21, hr_api.g_varchar2) <>
159 nvl(p_rec.per_information21, hr_api.g_varchar2) or
160 nvl(irc_ipd_shd.g_old_rec.per_information22, hr_api.g_varchar2) <>
161 nvl(p_rec.per_information22, hr_api.g_varchar2) or
162 nvl(irc_ipd_shd.g_old_rec.per_information23, hr_api.g_varchar2) <>
163 nvl(p_rec.per_information23, hr_api.g_varchar2) or
164 nvl(irc_ipd_shd.g_old_rec.per_information24, hr_api.g_varchar2) <>
165 nvl(p_rec.per_information24, hr_api.g_varchar2) or
166 nvl(irc_ipd_shd.g_old_rec.per_information25, hr_api.g_varchar2) <>
167 nvl(p_rec.per_information25, hr_api.g_varchar2) or
168 nvl(irc_ipd_shd.g_old_rec.per_information26, hr_api.g_varchar2) <>
169 nvl(p_rec.per_information26, hr_api.g_varchar2) or
170 nvl(irc_ipd_shd.g_old_rec.per_information27, hr_api.g_varchar2) <>
171 nvl(p_rec.per_information27, hr_api.g_varchar2) or
172 nvl(irc_ipd_shd.g_old_rec.per_information28, hr_api.g_varchar2) <>
173 nvl(p_rec.per_information28, hr_api.g_varchar2) or
174 nvl(irc_ipd_shd.g_old_rec.per_information29, hr_api.g_varchar2) <>
175 nvl(p_rec.per_information29, hr_api.g_varchar2) or
176 nvl(irc_ipd_shd.g_old_rec.per_information30, hr_api.g_varchar2) <>
177 nvl(p_rec.per_information30, hr_api.g_varchar2) ))
178 or (p_rec.pending_data_id is null) then
179 --
180 -- Only execute the validation if absolutely necessary:
181 -- a) During update, the structure column value or any
182 -- of the attribute values have actually changed.
183 -- b) During insert.
184 --
185 hr_dflex_utility.ins_or_upd_descflex_attribs
186 (p_appl_short_name => 'PER'
187 ,p_descflex_name => 'Person Developer DF'
188 ,p_attribute_category => p_rec.per_information_category
189 ,p_attribute1_name => 'PER_INFORMATION1'
190 ,p_attribute1_value => p_rec.per_information1
191 ,p_attribute2_name => 'PER_INFORMATION2'
192 ,p_attribute2_value => p_rec.per_information2
193 ,p_attribute3_name => 'PER_INFORMATION3'
194 ,p_attribute3_value => p_rec.per_information3
195 ,p_attribute4_name => 'PER_INFORMATION4'
196 ,p_attribute4_value => p_rec.per_information4
197 ,p_attribute5_name => 'PER_INFORMATION5'
198 ,p_attribute5_value => p_rec.per_information5
199 ,p_attribute6_name => 'PER_INFORMATION6'
200 ,p_attribute6_value => p_rec.per_information6
201 ,p_attribute7_name => 'PER_INFORMATION7'
202 ,p_attribute7_value => p_rec.per_information7
203 ,p_attribute8_name => 'PER_INFORMATION8'
204 ,p_attribute8_value => p_rec.per_information8
205 ,p_attribute9_name => 'PER_INFORMATION9'
206 ,p_attribute9_value => p_rec.per_information9
207 ,p_attribute10_name => 'PER_INFORMATION10'
208 ,p_attribute10_value => p_rec.per_information10
209 ,p_attribute11_name => 'PER_INFORMATION11'
210 ,p_attribute11_value => p_rec.per_information11
211 ,p_attribute12_name => 'PER_INFORMATION12'
212 ,p_attribute12_value => p_rec.per_information12
213 ,p_attribute13_name => 'PER_INFORMATION13'
214 ,p_attribute13_value => p_rec.per_information13
215 ,p_attribute14_name => 'PER_INFORMATION14'
216 ,p_attribute14_value => p_rec.per_information14
217 ,p_attribute15_name => 'PER_INFORMATION15'
218 ,p_attribute15_value => p_rec.per_information15
219 ,p_attribute16_name => 'PER_INFORMATION16'
220 ,p_attribute16_value => p_rec.per_information16
221 ,p_attribute17_name => 'PER_INFORMATION17'
222 ,p_attribute17_value => p_rec.per_information17
223 ,p_attribute18_name => 'PER_INFORMATION18'
224 ,p_attribute18_value => p_rec.per_information18
225 ,p_attribute19_name => 'PER_INFORMATION19'
226 ,p_attribute19_value => p_rec.per_information19
227 ,p_attribute20_name => 'PER_INFORMATION20'
228 ,p_attribute20_value => p_rec.per_information20
229 ,p_attribute21_name => 'PER_INFORMATION21'
230 ,p_attribute21_value => p_rec.per_information21
231 ,p_attribute22_name => 'PER_INFORMATION22'
232 ,p_attribute22_value => p_rec.per_information22
233 ,p_attribute23_name => 'PER_INFORMATION23'
234 ,p_attribute23_value => p_rec.per_information23
235 ,p_attribute24_name => 'PER_INFORMATION24'
236 ,p_attribute24_value => p_rec.per_information24
237 ,p_attribute25_name => 'PER_INFORMATION25'
238 ,p_attribute25_value => p_rec.per_information25
239 ,p_attribute26_name => 'PER_INFORMATION26'
240 ,p_attribute26_value => p_rec.per_information26
241 ,p_attribute27_name => 'PER_INFORMATION27'
242 ,p_attribute27_value => p_rec.per_information27
243 ,p_attribute28_name => 'PER_INFORMATION28'
244 ,p_attribute28_value => p_rec.per_information28
245 ,p_attribute29_name => 'PER_INFORMATION29'
246 ,p_attribute29_value => p_rec.per_information29
247 ,p_attribute30_name => 'PER_INFORMATION30'
248 ,p_attribute30_value => p_rec.per_information30
249 );
250 end if;
251 --
252 hr_utility.set_location(' Leaving:'||l_proc,20);
253 end chk_ddf;
254 --
255 -- ----------------------------------------------------------------------------
256 -- |-----------------------< chk_non_updateable_args >------------------------|
257 -- ----------------------------------------------------------------------------
258 -- {Start Of Comments}
259 --
260 -- Description:
261 -- This procedure is used to ensure that non updateable attributes have
262 -- not been updated. If an attribute has been updated an error is generated.
263 --
264 -- Pre Conditions:
265 -- g_old_rec has been populated with details of the values currently in
266 -- the database.
267 --
268 -- In Arguments:
269 -- p_rec has been populated with the updated values the user would like the
270 -- record set to.
271 --
272 -- Post Success:
273 -- Processing continues if all the non updateable attributes have not
274 -- changed.
275 --
276 -- Post Failure:
277 -- An application error is raised if any of the non updatable attributes
278 -- have been altered.
279 --
280 -- {End Of Comments}
281 -- ----------------------------------------------------------------------------
282 Procedure chk_non_updateable_args
283 (p_rec in irc_ipd_shd.g_rec_type
284 ) IS
285 --
286 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
287 --
288 Begin
289 --
290 -- Only proceed with the validation if a row exists for the current
291 -- record in the HR Schema.
292 --
293 IF NOT irc_ipd_shd.api_updating
294 (p_pending_data_id => p_rec.pending_data_id
295 ) THEN
296 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
297 fnd_message.set_token('PROCEDURE ', l_proc);
298 fnd_message.set_token('STEP ', '5');
299 fnd_message.raise_error;
300 END IF;
301 --
302 --
303 -- Checks to ensure non-updateable args have
304 -- not been updated.
305 if p_rec.pending_data_id <> irc_ipd_shd.g_old_rec.pending_data_id
306 then
307 hr_api.argument_changed_error
311 );
308 (p_api_name => l_proc
309 ,p_argument => 'PENDING_DATA_ID'
310 ,p_base_table => irc_ipd_shd.g_tab_nam
312 end if;
313 --
314 if nvl(p_rec.vacancy_id, hr_api.g_number) <>
315 nvl(irc_ipd_shd.g_old_rec.vacancy_id
316 ,hr_api.g_number)
317 then
318 hr_api.argument_changed_error
319 (p_api_name => l_proc
320 ,p_argument => 'VACANCY_ID'
321 ,p_base_table => irc_ipd_shd.g_tab_nam
322 );
323 end if;
324 --
325 if p_rec.creation_date <> irc_ipd_shd.g_old_rec.creation_date
326 then
327 hr_api.argument_changed_error
328 (p_api_name => l_proc
329 ,p_argument => 'CREATION_DATE'
330 ,p_base_table => irc_ipd_shd.g_tab_nam
331 );
332 end if;
333 --
334 End chk_non_updateable_args;
335 --
336 -- ----------------------------------------------------------------------------
337 -- |---------------------------< chk_vacancy_id >----------------------------|
338 -- ----------------------------------------------------------------------------
339 -- {Start Of Comments}
340 --
341 -- Description:
342 -- This procedure is used to ensure that vacancy_id exists in
343 -- per_all_vacancies and is valid on creation_date.
344 --
345 -- Pre Conditions:
346 -- g_old_rec has been populated with details of the values currently in
347 -- the database.
348 --
349 -- In Arguments:
350 -- p_vacancy_id
351 -- p_creation_date
352 --
353 -- Post Success:
354 -- Processing continues if the vacancy id exists and is valid on
355 -- creation_date.
356 --
357 -- Post Failure:
358 -- An application error is raised if the vacancy id does not exist
359 -- or is not valid on creation_date.
360 --
361 -- {End Of Comments}
362 -- ----------------------------------------------------------------------------
363 Procedure chk_vacancy_id
364 (p_vacancy_id in irc_pending_data.vacancy_id%type
365 ,p_creation_date in irc_pending_data.creation_date%type
366 ) IS
367 --
368 l_proc varchar2(72) := g_package || 'chk_vacancy_id';
369 l_num number;
370 --
371 cursor csr_vacancy_id is
372 select 1
373 from per_all_vacancies
374 where vacancy_id = p_vacancy_id
375 and nvl(date_to,hr_api.g_eot) >= p_creation_date
376 and date_from <= p_creation_date;
377 --
378 Begin
379 --
380 hr_utility.set_location(' Entering:'||l_proc,10);
381 --
382 hr_api.mandatory_arg_error
383 (p_api_name => l_proc
384 ,p_argument => 'creation date'
385 ,p_argument_value => p_creation_date
386 );
387 --
388 -- Check if the vacancy id is valid on creation date.
389 --
390 open csr_vacancy_id;
391 fetch csr_vacancy_id into l_num;
392 hr_utility.set_location(l_proc,20);
393 if csr_vacancy_id%notfound then
394 close csr_vacancy_id;
395 hr_utility.set_message(800,'IRC_412015_BAD_VACANCY_ID');
396 hr_utility.raise_error;
397 end if;
398 close csr_vacancy_id;
399 hr_utility.set_location(l_proc,30);
400 --
401 exception
402 when app_exception.application_exception then
403 if hr_multi_message.exception_add
404 (p_associated_column1 =>
405 'IRC_PENDING_DATA.VACANCY_ID'
406 ) then
407 hr_utility.set_location(' Leaving:'||l_proc,50);
408 raise;
409 end if;
410 --
411 hr_utility.set_location(' Leaving:'||l_proc,60);
412 End chk_vacancy_id;
413 --
414 -- ----------------------------------------------------------------------------
415 -- |-------------------< chk_job_already_apld_for >--------------------|
416 -- ----------------------------------------------------------------------------
417 -- {Start Of Comments}
418 --
419 -- Description:
420 -- This procedure is used to ensure that person has not already for the job
421 --
422 -- Pre Conditions:
423 -- g_old_rec has been populated with details of the values currently in
424 -- the database.
425 --
426 -- In Arguments:
427 -- p_vacancy_id
428 -- p_email_address
429 --
430 -- Post Success:
431 -- Processing continues if the person has not already for the job.
432 --
433 -- Post Failure:
434 -- An application error is raised if the vacancy id exists for the person id.
435 --
436 -- {End Of Comments}
437 -- ----------------------------------------------------------------------------
438 Procedure chk_job_already_apld_for
439 (p_vacancy_id in irc_pending_data.vacancy_id%type
440 ,p_email_address in irc_pending_data.email_address%type
441 ) IS
442 --
443 l_proc varchar2(72) := g_package || 'chk_job_already_apld_for';
444 l_num number;
445 --
446 cursor csr_job is
447 select 1
448 from irc_pending_data
449 where vacancy_id = p_vacancy_id
450 and upper(email_address) = upper(p_email_address);
451 --
452 Begin
453 --
454 hr_utility.set_location(' Entering:'||l_proc,10);
455 --
456 if hr_multi_message.no_exclusive_error(
457 p_check_column1 => 'IRC_PENDING_DATA.VACANCY_ID'
458 ) then
459 --
460 open csr_job;
461 fetch csr_job into l_num;
462 hr_utility.set_location(l_proc,20);
463 if csr_job%found then
464 close csr_job;
465 hr_utility.set_message(800,'IRC_APL_ALREADY_APPLIED');
466 hr_utility.raise_error;
467 end if;
468 close csr_job;
469 end if;
470 --
471 exception
472 when app_exception.application_exception then
473 if hr_multi_message.exception_add
477 hr_utility.set_location(' Leaving:'||l_proc,30);
474 (p_associated_column1 => 'IRC_PENDING_DATA.VACANCY_ID'
475 ,p_associated_column2 => 'IRC_PENDING_DATA.EMAIL_ADDRESS'
476 ) then
478 raise;
479 end if;
480 --
481 hr_utility.set_location(' Leaving:'||l_proc,40);
482 End chk_job_already_apld_for;
483 --
484 -- ----------------------------------------------------------------------------
485 -- |---------------------------< chk_sex >----------------------------|
486 -- ----------------------------------------------------------------------------
487 -- {Start Of Comments}
488 --
489 -- Description:
490 -- This procedure is used to ensure that sex exists in
491 -- lookup.
492 --
493 -- Pre Conditions:
494 -- g_old_rec has been populated with details of the values currently in
495 -- the database.
496 --
497 -- In Arguments:
498 -- p_sex
499 -- p_creation_date
500 --
501 -- Post Success:
502 -- Processing continues if the sex exists in lookup.
503 --
504 -- Post Failure:
505 -- An application error is raised if the sex does not exist
506 -- in the lookup.
507 --
508 -- {End Of Comments}
509 -- ----------------------------------------------------------------------------
510 Procedure chk_sex
511 (p_sex in irc_pending_data.sex%type
512 ,p_creation_date in irc_pending_data.creation_date%type
513 ,p_pending_data_id in irc_pending_data.pending_data_id%type
514 ) IS
515 --
516 l_api_updating boolean;
517 l_proc varchar2(72) := g_package||'chk_sex';
518 --
519 Begin
520 hr_utility.set_location('Entering:'||l_proc,10);
521 --
522 hr_api.mandatory_arg_error
523 (p_api_name => l_proc
524 ,p_argument => 'creation date'
525 ,p_argument_value => p_creation_date
526 );
527 l_api_updating := irc_ipd_shd.api_updating
528 (p_pending_data_id => p_pending_data_id
529 );
530 --
531 if hr_multi_message.no_exclusive_error(
532 p_check_column1 => 'IRC_PENDING_DATA.VACANCY_ID'
533 ) then
534 --
535 if (l_api_updating and nvl(p_sex,hr_api.g_varchar2)
536 <> nvl(irc_ipd_shd.g_old_rec.sex,hr_api.g_varchar2)
537 or not l_api_updating) then
538 --
539 --Check if sex is set
540 --
541 if p_sex is not null then
542 hr_utility.set_location('Entering:'||l_proc,20);
543 --
544 --Check if the sex exists in hr_lookups
545 --
546 if hr_api.not_exists_in_hr_lookups
547 (p_effective_date => p_creation_date
548 ,p_lookup_type => 'SEX'
549 ,p_lookup_code => p_sex
550 ) then
551 hr_utility.set_message(800,'HR_7511_PER_SEX_INVALID');
552 hr_utility.raise_error;
553 end if;
554 end if;
555 end if;
556 end if;
557 exception
558 when app_exception.application_exception then
559 if hr_multi_message.exception_add
560 (p_associated_column1 =>
561 'IRC_PENDING_DATA.SEX'
562 ) then
563 hr_utility.set_location(' Leaving:'||l_proc,30);
564 raise;
565 end if;
566 hr_utility.set_location(' Leaving:'||l_proc,50);
567 --
568 --
569 End chk_sex;
570 --
571 -- ----------------------------------------------------------------------------
572 -- |----------------------------< chk_allow_access >--------------------------|
573 -- ----------------------------------------------------------------------------
574 --
575 -- Description:
576 -- Validates that the p_allow_access is either 'Y' or 'N'
577 --
578 -- Prerequisites:
579 -- Must be called in insert_validate.
580 --
581 -- In Arguments:
582 -- p_allow_access
583 --
584 -- Post Success:
585 -- If p_allow_access is either 'Y' or 'N' then continue.
586 --
587 -- Post Failure:
588 -- If p_allow_access is not 'Y' or 'N'
589 -- then throw an error indicating the same.
590 --
591 -- Access Status:
592 -- Internal Row Handler Use Only.
593 --
594 -- ----------------------------------------------------------------------------
595 Procedure chk_allow_access
596 (p_allow_access in irc_pending_data.allow_access%type
597 ) is
598 --
599 l_proc varchar2(72) := g_package||'chk_allow_access';
600 --
601 Begin
602 --
603 hr_utility.set_location(' Entering:'||l_proc,10);
604 --
605 hr_api.mandatory_arg_error
606 (p_api_name => l_proc
607 ,p_argument => 'ALLOW_ACCESS'
608 ,p_argument_value => p_allow_access
609 );
610 --
611 hr_utility.set_location(l_proc,20);
612 --
613 if p_allow_access not in ('Y','N') then
614 hr_utility.set_message(800,'IRC_412005_BAD_ALLOW_ACCESS');
615 hr_utility.raise_error;
616 end if;
617 --
618 hr_utility.set_location(' Leaving:'||l_proc,30);
619 --
620 exception
621 when app_exception.application_exception then
622 if hr_multi_message.exception_add
623 (p_associated_column1 => 'IRC_PENDING_DATA.ALLOW_ACCESS'
624 ) then
625 --
626 hr_utility.set_location(' Leaving:'||l_proc,40);
627 --
628 raise;
629 end if;
630 --
631 hr_utility.set_location(' Leaving:'||l_proc,50);
632 --
633 --
634 --
635 End chk_allow_access;
636 --
637 -- ---------------------------------------------------------------------------
638 -- |---------------------< chk_GB_per_information >-------------------------|
642 -- - Checks that the values held in developer descriptive flexfields
639 -- ---------------------------------------------------------------------------
640 --
641 -- Description:
643 -- are valid for a category of 'GB'
644 -- - Validates that per information3 and per information11 to 20 are null.
645 -- - Validates that per information2, 4 ,9 and 10 exists as a lookup code on
646 -- HR_LOOKUPS for the lookup type 'YES_NO' with an enabled flag set to 'Y'
647 -- and the person is active on HR_LOOKUPS on creation date.
648 -- - Validates that per information1 exists as a lookup code on HR_LOOKUPS
649 -- for the lookup type 'ETH_TYPE' with an enabled flag set to 'Y' and the
650 -- person is active on HR_LOOKUPS on effective date.
651 -- - Validates that per information5 is less than or equal to 30 characters
652 -- and in uppercase.
653 --
654 -- Pre-conditions:
655 -- A GB per information category
656 --
657 -- In Arguments:
658 -- p_person_id
659 -- p_per_information_category
660 -- p_per_information1
661 -- p_per_information2
662 -- p_per_information3
663 -- p_per_information4
664 -- p_per_information5
665 -- p_per_information6
666 -- p_per_information7
667 -- p_per_information8
668 -- p_per_information9
669 -- p_per_information10
670 -- p_per_information11
671 -- p_per_information12
672 -- p_per_information13
673 -- p_per_information14
674 -- p_per_information15
675 -- p_per_information16
676 -- p_per_information17
677 -- p_per_information18
678 -- p_per_information19
679 -- p_per_information20
680 -- p_effective_date
681 --
682 -- Post Success:
683 -- Processing continues if:
684 -- - per_information3 and per_information11 to 20 values are null
685 -- - per information2, 4 , 9 and 10 exists as a lookup code in HR_LOOKUPS
686 -- for the lookup type 'YES_NO' where the enabled flag is 'Y'
687 -- and active on HR_LOOKUPS on creation_date.
688 -- - per information1 exists as a lookup code in HR_LOOKUPS
689 -- for the lookup type 'ETH_TYPE' where the enabled flag is 'Y'
690 -- and active on HR_LOOKUPS on creation_date.
691 -- - per_information5 is less than or equal to 30 characters long
692 -- and upper case.
693 -- Post Failure:
694 -- An application error is raised and processing is terminated if:
695 -- - any of per_information3 and per_information10 to 20 values are
696 -- not null.
697 -- - any of per information2, 4,9 and 10 does'nt exist as a lookup code
698 -- in HR_LOOKUPS for the lookup type 'YES_NO' where the enabled flag
699 -- is 'Y' and the person is active on HR_LOOKUPS on creation date.
700 -- - per information1 does'nt exist as a lookup code in HR_LOOKUPS
701 -- for the lookup type 'ETH_TYPE' where the enabled flag is 'Y'
702 -- the person is active on HR_LOOKUPS on creation date.
703 -- - per_information5 is not less than or equal to 30 characters long
704 -- or not upper case.
705 --
706 -- Access Status:
707 -- Internal Table Handler Use Only.
708 --
709 procedure chk_GB_per_information
710 (p_person_id in irc_pending_data.person_id%TYPE
711 ,p_per_information_category in irc_pending_data.per_information_category
712 %TYPE
713 ,p_per_information1 in irc_pending_data.per_information1%TYPE
714 ,p_per_information2 in irc_pending_data.per_information2%TYPE
715 ,p_per_information3 in irc_pending_data.per_information3%TYPE
716 ,p_per_information4 in irc_pending_data.per_information4%TYPE
717 ,p_per_information5 in irc_pending_data.per_information5%TYPE
718 ,p_per_information6 in irc_pending_data.per_information6%TYPE
719 ,p_per_information7 in irc_pending_data.per_information7%TYPE
720 ,p_per_information8 in irc_pending_data.per_information8%TYPE
721 ,p_per_information9 in irc_pending_data.per_information9%TYPE
722 ,p_per_information10 in irc_pending_data.per_information10%TYPE
723 ,p_per_information11 in irc_pending_data.per_information11%TYPE
724 ,p_per_information12 in irc_pending_data.per_information12%TYPE
725 ,p_per_information13 in irc_pending_data.per_information13%TYPE
726 ,p_per_information14 in irc_pending_data.per_information14%TYPE
727 ,p_per_information15 in irc_pending_data.per_information15%TYPE
728 ,p_per_information16 in irc_pending_data.per_information16%TYPE
729 ,p_per_information17 in irc_pending_data.per_information17%TYPE
730 ,p_per_information18 in irc_pending_data.per_information18%TYPE
731 ,p_per_information19 in irc_pending_data.per_information19%TYPE
732 ,p_per_information20 in irc_pending_data.per_information20%TYPE
733 ,p_per_information21 in irc_pending_data.per_information21%TYPE
734 ,p_per_information22 in irc_pending_data.per_information22%TYPE
735 ,p_per_information23 in irc_pending_data.per_information23%TYPE
736 ,p_per_information24 in irc_pending_data.per_information24%TYPE
737 ,p_per_information25 in irc_pending_data.per_information25%TYPE
738 ,p_per_information26 in irc_pending_data.per_information26%TYPE
739 ,p_per_information27 in irc_pending_data.per_information27%TYPE
740 ,p_per_information28 in irc_pending_data.per_information28%TYPE
741 ,p_per_information29 in irc_pending_data.per_information29%TYPE
742 ,p_per_information30 in irc_pending_data.per_information30%TYPE
743 ,p_creation_date in irc_pending_data.creation_date%TYPE
744 )
745 is
746 --
747 l_error exception;
748 l_proc varchar2(72) := g_package||'chk_GB_per_information';
752 l_per_information6 irc_pending_data.per_information6%TYPE;
749 l_api_updating boolean;
750 l_lookup_type varchar2(30);
751 l_info_attribute number(2);
753 l_per_information7 irc_pending_data.per_information7%TYPE;
754 l_per_information8 irc_pending_data.per_information8%TYPE;
755 l_output varchar2(150);
756 l_rgeflg varchar2(10);
757 --
758 begin
759 hr_utility.set_location('Entering:'|| l_proc, 10);
760 --
761 -- Check the mandatory parameters
762 --
763 --
764 hr_api.mandatory_arg_error
765 (p_api_name => l_proc
766 ,p_argument => 'creation_date'
767 ,p_argument_value => p_creation_date
768 );
769 --
770 -- We know the per_information_category is GB, so check the rest of
771 -- the per_information fields within this context.
772 --
773 -- Check if the per_information1 value exists in hr_lookups
774 -- where the lookup_type is 'ETH_TYPE'
775 --
776 if p_per_information1 is not null then
777 --
778 -- Check that per information1 exists in hr_lookups for the
779 -- lookup type 'ETH_TYPE' with an enabled flag set to 'Y' and that
780 -- the person is active on creation date in hr_lookups.
781 --
782 if hr_api.not_exists_in_hr_lookups
783 (p_effective_date => p_creation_date
784 ,p_lookup_type => 'ETH_TYPE'
785 ,p_lookup_code => p_per_information1
786 )
787 then
788 --
789 hr_utility.set_message(801, 'HR_7524_PER_INFO1_INVALID');
790 hr_utility.raise_error;
791 --
792 end if;
793 --
794 end if;
795 --
796 -- Check if the per_information2 value exists in hr_lookups
797 -- where the lookup_type is 'YES_NO'
798 --
799 if p_per_information2 is not null then
800 --
801 -- Check that per information2 exists in hr_lookups for the
802 -- lookup type 'YES_NO' with an enabled flag set to 'Y' and that
803 -- the person is active on creation date in hr_lookups.
804 --
805 if hr_api.not_exists_in_hr_lookups
806 (p_effective_date => p_creation_date
807 ,p_lookup_type => 'YES_NO'
808 ,p_lookup_code => p_per_information2
809 )
810 then
811 --
812 hr_utility.set_message(801, 'HR_7525_PER_INFO2_INVALID');
813 hr_utility.raise_error;
814 --
815 end if;
816 --
817 end if;
818 --
819 -- Check if the per_information4 value exists in hr_lookups
820 -- where the lookup_type is 'YES_NO'
821 --
822 if p_per_information4 is not null then
823 --
824 -- Check that per information4 exists in hr_lookups for the
825 -- lookup type 'YES_NO' with an enabled flag set to 'Y' and that
826 -- the person is active on creation date in hr_lookups.
827 --
828 if hr_api.not_exists_in_hr_lookups
829 (p_effective_date => p_creation_date
830 ,p_lookup_type => 'YES_NO'
831 ,p_lookup_code => p_per_information4
832 )
833 then
834 --
835 hr_utility.set_message(801, 'HR_7526_PER_INFO4_INVALID');
836 hr_utility.raise_error;
837 --
838 end if;
839 --
840 end if;
841 --
842 -- Check if p_per_information5 is greater than 30 characters long
843 --
844 if p_per_information5 is not null then
845 if length(p_per_information5) > 30 then
846 -- Error: Work Permit (PER_INFORMATION5) cannot be longer than
847 -- 30 characters
848 hr_utility.set_message(801, 'HR_7527_PER_INFO5_LENGTH');
849 hr_utility.raise_error;
850 end if;
851 --
852 -- Check if p_per_information5 is not upper case
853 --
854 --if p_per_information5 <> upper(p_per_information5) then
855 -- Error: Enter the Work Permit value (PER_INFORMATION5) in
856 -- upper case
857 --hr_utility.set_message(801, 'HR_7528_PER_INFO5_CASE');
858 --hr_utility.raise_error;
859 --end if;
860 end if;
861 --
862 -- Check if p_per_information6 is in the range 0 - 99.
863 --
864 if p_per_information6 is not null then
865 --
866 l_per_information6 := p_per_information6;
867 hr_chkfmt.checkformat(value => l_per_information6
868 ,format => 'I'
869 ,output => l_output
870 ,minimum => NULL
871 ,maximum => NULL
872 ,nullok => 'Y'
873 ,rgeflg => l_rgeflg
874 ,curcode => NULL);
875 --
876 if to_number(l_per_information6) < 0 or
877 to_number(l_per_information6) > 99 then
878 -- Error: Additional pension years (PER_INFORMATION6) not in the
879 -- range 0 - 99.
880 hr_utility.set_message(801, 'HR_51272_PER_INFO6_INVALID');
881 hr_utility.raise_error;
882 end if;
883 end if;
884 --
885 -- Check if p_per_information7 is in the range 1 - 11.
886 --
887 if p_per_information7 is not null then
888 --
889 l_per_information7 := p_per_information7;
890 hr_chkfmt.checkformat(value => l_per_information7
891 ,format => 'I'
892 ,output => l_output
893 ,minimum => NULL
894 ,maximum => NULL
895 ,nullok => 'Y'
896 ,rgeflg => l_rgeflg
897 ,curcode => NULL);
898 --
899 if to_number(l_per_information7) < 1 or
903 hr_utility.set_message(801, 'HR_51273_PER_INFO7_INVALID');
900 to_number(l_per_information7) > 11 then
901 -- Error: Additional pension months (PER_INFORMATION7) not in the
902 -- range 1 - 11.
904 hr_utility.raise_error;
905 end if;
906 end if;
907 --
908 -- Check if p_per_information8 is number.
909 --
910 if p_per_information8 is not null then
911 --
912 l_per_information8 := p_per_information8;
913 hr_chkfmt.checkformat(value => l_per_information8
914 ,format => 'I'
915 ,output => l_output
916 ,minimum => NULL
917 ,maximum => NULL
918 ,nullok => 'Y'
919 ,rgeflg => l_rgeflg
920 ,curcode => NULL);
921 end if;
922 --
923 -- Check if the per_information9 value exists in hr_lookups
924 -- where the lookup_type is 'YES_NO'
925 --
926 if p_per_information9 is not null then
927 --
928 -- Check that per information9 exists in hr_lookups for the
929 -- lookup type 'YES_NO' with an enabled flag set to 'Y' and that
930 -- the person is active on creation date in hr_lookups.
931 --
932 if hr_api.not_exists_in_hr_lookups
933 (p_effective_date => p_creation_date
934 ,p_lookup_type => 'YES_NO'
935 ,p_lookup_code => p_per_information9
936 )
937 then
938 --
939 hr_utility.set_message(801, 'HR_51274_PER_INFO9_INVALID');
940 hr_utility.raise_error;
941 --
942 end if;
943 --
944 end if;
945 if p_per_information10 is not null then
946 --
947 -- Check that per information10 exists in hr_lookups for the
948 -- lookup type 'YES_NO' with an enabled flag set to 'Y' and that
949 -- the person is active on creation date in hr_lookups.
950 --
951 if hr_api.not_exists_in_hr_lookups
952 (p_effective_date => p_creation_date
953 ,p_lookup_type => 'YES_NO'
954 ,p_lookup_code => p_per_information10
955 )
956 then
957 --
958 hr_utility.set_message(801, 'HR_78105_PER_INFO10_INVALID');
959 hr_utility.raise_error;
960 --
961 end if;
962 --
963 end if;
964 --
965 -- Check if any of the remaining per_information parameters are not
966 -- null
967 -- (developer descriptive flexfields not used for GB)
968 --
969 if p_per_information3 is not null then
970 l_info_attribute := 3;
971 raise l_error;
972 elsif p_per_information11 is not null then
973 l_info_attribute := 11;
974 raise l_error;
975 elsif p_per_information12 is not null then
976 l_info_attribute := 12;
977 raise l_error;
978 elsif p_per_information13 is not null then
979 l_info_attribute := 13;
980 raise l_error;
981 elsif p_per_information14 is not null then
982 l_info_attribute := 14;
983 raise l_error;
984 elsif p_per_information15 is not null then
985 l_info_attribute := 15;
986 raise l_error;
987 elsif p_per_information16 is not null then
988 l_info_attribute := 16;
989 raise l_error;
990 elsif p_per_information17 is not null then
991 l_info_attribute := 17;
992 raise l_error;
993 elsif p_per_information18 is not null then
994 l_info_attribute := 18;
995 raise l_error;
996 elsif p_per_information19 is not null then
997 l_info_attribute := 19;
998 raise l_error;
999 elsif p_per_information20 is not null then
1000 l_info_attribute := 20;
1001 raise l_error;
1002 elsif p_per_information21 is not null then
1003 l_info_attribute := 21;
1004 raise l_error;
1005 elsif p_per_information22 is not null then
1006 l_info_attribute := 22;
1007 raise l_error;
1008 elsif p_per_information23 is not null then
1009 l_info_attribute := 23;
1010 raise l_error;
1011 elsif p_per_information24 is not null then
1012 l_info_attribute := 24;
1013 raise l_error;
1014 elsif p_per_information25 is not null then
1015 l_info_attribute := 25;
1016 raise l_error;
1017 elsif p_per_information26 is not null then
1018 l_info_attribute := 26;
1019 raise l_error;
1020 elsif p_per_information27 is not null then
1021 l_info_attribute := 27;
1022 raise l_error;
1023 elsif p_per_information28 is not null then
1024 l_info_attribute := 28;
1025 raise l_error;
1026 elsif p_per_information29 is not null then
1027 l_info_attribute := 29;
1028 raise l_error;
1029 elsif p_per_information30 is not null then
1030 l_info_attribute := 30;
1031 raise l_error;
1032 end if;
1033 exception
1034 when l_error then
1035 -- Error: Do not enter PER_INFORMATION99 for this legislation
1036 hr_utility.set_message(801, 'HR_7529_PER_INFO_NOT_NULL');
1037 hr_utility.set_message_token('NUM',to_char(l_info_attribute));
1038 hr_utility.raise_error;
1039 --
1040 hr_utility.set_location(' Leaving:'|| l_proc, 220);
1041 end chk_GB_per_information;
1042 --
1043 -- ---------------------------------------------------------------------------
1044 -- |---------------------< chk_US_per_information >-------------------------|
1045 -- ---------------------------------------------------------------------------
1046 -- Description:
1047 -- - Checks that the values held in developer descriptive flexfields
1048 -- are valid for a category of 'US'.
1049 -- - Validates that per information6 and 9 exist as a lookup code on
1053 -- for the lookup type 'US_ETHNIC_GROUP' with an enabled flag set to 'Y'
1050 -- HR_LOOKUPS for the lookup type 'YES_NO' with an enabled flag
1051 -- set to 'Y' and the person is active on creation_date in HR_LOOKUPS.
1052 -- - Validates that per information1 exists as a lookup code on HR_LOOKUPS
1054 -- and the person is active on creation_date in HR_LOOKUPS.
1055 -- - Validates that per information2 exists as a lookup code on HR_LOOKUPS
1056 -- for the lookup type 'PER_US_I9_STATE' with an enabled flag set to 'Y'
1057 -- and the person is active on creation_date in HR_LOOKUPS.
1058 -- - Validates that per information4 exists as a lookup code on HR_LOOKUPS
1059 -- for the lookup type 'US_VISA_TYPE' with an enabled flag set to 'Y'
1060 -- and the person is active on creation_date in HR_LOOKUPS.
1061 -- - Validates that per information5 exists as a lookup code on HR_LOOKUPS
1062 -- for the lookup type 'US_VETERAN_STATUS' with an enabled flag set to 'Y'
1063 -- and the person is active on creation_date in HR_LOOKUPS.
1064 -- - Validates that per information7 exists as a lookup code on HR_LOOKUPS
1065 -- for the lookup type 'US_NEW_HIRE_STATUS' with enabled flag set to 'Y'
1066 -- and the person is active on creation_date in HR_LOOKUPS.
1067 -- - Validates that when per information7 is set to 'EXCL' that per
1068 -- information8 exists as a lookup code on HR_LOOKUPS for the lookup type
1069 -- 'US_NEW_HIRE_EXCEPTIONS' with an enabled flag set to 'Y' and the
1070 -- person is active on creation_date in HR_LOOKUPS.
1071 -- - Validates that per information10 exists as a lookup code on
1072 -- HR_LOOKUPS for the lookup type 'YES_NO' with an enabled flag
1073 -- set to 'Y' and the person is active on creation_date in HR_LOOKUPS.
1074 --
1075 -- Pre-conditions:
1076 -- None
1077 --
1078 -- In Arguments:
1079 -- p_person_id
1080 -- p_per_information_category
1081 -- p_per_information1
1082 -- p_per_information2
1083 -- p_per_information3
1084 -- p_per_information4
1085 -- p_per_information5
1086 -- p_per_information6
1087 -- p_per_information7
1088 -- p_per_information8
1089 -- p_per_information9
1090 -- p_per_information10
1091 -- p_per_information11
1092 -- p_per_information12
1093 -- p_per_information13
1094 -- p_per_information14
1095 -- p_per_information15
1096 -- p_per_information16
1097 -- p_per_information17
1098 -- p_per_information18
1099 -- p_per_information19
1100 -- p_per_information20
1101 -- p_per_information21
1102 -- p_per_information22
1103 -- p_per_information23
1104 -- p_per_information24
1105 -- p_per_information25
1106 -- p_per_information26
1107 -- p_per_information27
1108 -- p_per_information28
1109 -- p_per_information29
1110 -- p_per_information30
1111 -- p_effective_date
1112 -- p_api_updating
1113 --
1114 -- Post Success:
1115 -- Processing continues if:
1116 -- - per information6 and 9 exist as a lookup code in HR_LOOKUPS
1117 -- for the lookup type 'YES_NO' where the enabled flag is 'Y' and
1118 -- the person is active on creation_date in HR_LOOKUPS.
1119 -- - per information1 exists as a lookup code in HR_LOOKUPS
1120 -- for the lookup type 'US_ETHNIC_GROUP' where the enabled flag is
1121 -- 'Y' and the person is active on creation_date in HR_LOOKUPS.
1122 -- - per information2 exists as a lookup code in HR_LOOKUPS
1123 -- for the lookup type 'PER_US_I9_STATE' where the enabled flag is
1124 -- 'Y' and the person is active on creation_date in HR_LOOKUPS.
1125 -- - per information3 is a valid date and 11 characters long.
1126 -- - per information4 exists as a lookup code in HR_LOOKUPS
1127 -- for the lookup type 'US_VISA_TYPE' where the enabled flag is
1128 -- 'Y' and the person is active on creation_date in HR_LOOKUPS.
1129 -- - per information5 exists as a lookup code in HR_LOOKUPS
1130 -- for the lookup type 'US_VETERAN_STATUS' where the enabled flag is
1131 -- 'Y' and the person is active on creation_date in HR_LOOKUPS.
1132 -- - per information7 exists as a lookup code in HR_LOOKUPS
1133 -- for the lookup type 'US_NEW_HIRE_STATUS' where the enabled flag is
1134 -- 'Y' and the person is active on creation_date in HR_LOOKUPS.
1135 -- - when per information7 is set to 'EXCL' and per information8 exists
1136 -- as a lookup code in HR_LOOKUPS for the lookup type
1137 -- 'US_NEW_HIRE_EXCEPTIONS' where the enabled flag is 'Y' and
1138 -- the person is active on creation_date in HR_LOOKUPS.
1139 --
1140 -- 9) per_information9 value exists in hr_lookups
1141 -- where lookup_type = 'YES_NO'
1142 --
1143 -- 10) per information10 exists as a lookup code in HR_LOOKUPS
1144 -- for the lookup type 'YES_NO' where the enabled flag is 'Y' and
1145 -- the person is active on creation_date in HR_LOOKUPS.
1146 --
1147 -- 11) per_information11 to 20 values are null
1148 --
1149 -- Post Failure:
1150 -- An application error is raised and processing is terminated if:
1151 -- - per information6 and 9 does not exist as a lookup code in
1152 -- HR_LOOKUPS for the lookup type 'YES_NO' where the
1153 -- enabled flag is 'Y' and the person is active on
1154 -- creation_date on HR_LOOKUPS.
1155 -- - per information1 doesn't exist as a lookup code in HR_LOOKUPS
1156 -- for the lookup type 'US_ETHNIC_GROUP' where the enabled flag is
1157 -- 'Y' and and the person is active on creation_date on HR_LOOKUPS.
1158 -- - per information2 doesn't exist as a lookup code in HR_LOOKUPS
1159 -- for the lookup type 'PER_US_I9_STATE' where the enabled flag is
1160 -- 'Y' and and the person is active on creation_date on HR_LOOKUPS.
1161 -- - per information3 value is an invalid date or less than 11
1162 -- characters long.
1166 -- - per information5 doesn't exist as a lookup code in HR_LOOKUPS
1163 -- - per information4 doesn't exist as a lookup code in HR_LOOKUPS
1164 -- for the lookup type 'US_VISA_TYPE' where the enabled flag is
1165 -- 'Y' and and the person is active on creation_date on HR_LOOKUPS.
1167 -- for the lookup type 'US_VETERAN_STATUS' where the enabled flag is
1168 -- 'Y' and and the person is active on creation_date on HR_LOOKUPS.
1169 -- - per information7 doesn't exist as a lookup code in HR_LOOKUPS
1170 -- for the lookup type 'US_NEW_HIRE_STATUS' where the enabled flag is
1171 -- 'Y' and and the person is active on creation_date on HR_LOOKUPS.
1172 -- - per information8 doesn't exist as a lookup code in HR_LOOKUPS
1173 -- for the lookup type 'US_NEW_HIRE_EXCEPTION' where the enabled flag is
1174 -- 'Y' and the person is active on creation_date on HR_LOOKUPS.
1175 -- - when per information7 is set to 'EXCL' and per information8 doesn't
1176 -- exist as a lookup code in HR_LOOKUPS for the lookup type
1177 -- 'US_NEW_HIRE_EXCEPTIONS' where the enabled flag is 'Y'
1178 -- and the person is active on creation_date on HR_LOOKUPS.
1179 --
1180 -- 9) per_information9 value does not exists in hr_lookups
1181 -- where lookup_type = 'YES_NO'
1182 --
1183 -- 10) per information10 does not exist as a lookup code in HR_LOOKUPS
1184 -- for the lookup type 'YES_NO' where the enabled flag is 'Y'
1185 -- and the person is active on creation_date on HR_LOOKUPS.
1186 --
1187 -- 11) per_information11 to 20 values are not null
1188 --
1189 -- Access Status:
1190 -- Internal Table Handler Use Only
1191 --
1192 procedure chk_US_per_information
1193 (p_person_id in irc_pending_data.person_id%TYPE
1194 ,p_per_information_category in irc_pending_data.per_information_category
1195 %TYPE
1196 ,p_per_information1 in irc_pending_data.per_information1%TYPE
1197 ,p_per_information2 in irc_pending_data.per_information2%TYPE
1198 ,p_per_information3 in irc_pending_data.per_information3%TYPE
1199 ,p_per_information4 in irc_pending_data.per_information4%TYPE
1200 ,p_per_information5 in irc_pending_data.per_information5%TYPE
1201 ,p_per_information6 in irc_pending_data.per_information6%TYPE
1202 ,p_per_information7 in irc_pending_data.per_information7%TYPE
1203 ,p_per_information8 in irc_pending_data.per_information8%TYPE
1204 ,p_per_information9 in irc_pending_data.per_information9%TYPE
1205 ,p_per_information10 in irc_pending_data.per_information10%TYPE
1206 ,p_per_information11 in irc_pending_data.per_information11%TYPE
1207 ,p_per_information12 in irc_pending_data.per_information12%TYPE
1208 ,p_per_information13 in irc_pending_data.per_information13%TYPE
1209 ,p_per_information14 in irc_pending_data.per_information14%TYPE
1210 ,p_per_information15 in irc_pending_data.per_information15%TYPE
1211 ,p_per_information16 in irc_pending_data.per_information16%TYPE
1212 ,p_per_information17 in irc_pending_data.per_information17%TYPE
1213 ,p_per_information18 in irc_pending_data.per_information18%TYPE
1214 ,p_per_information19 in irc_pending_data.per_information19%TYPE
1215 ,p_per_information20 in irc_pending_data.per_information20%TYPE
1216 ,p_per_information21 in irc_pending_data.per_information21%TYPE
1217 ,p_per_information22 in irc_pending_data.per_information22%TYPE
1218 ,p_per_information23 in irc_pending_data.per_information23%TYPE
1219 ,p_per_information24 in irc_pending_data.per_information24%TYPE
1220 ,p_per_information25 in irc_pending_data.per_information25%TYPE
1221 ,p_per_information26 in irc_pending_data.per_information26%TYPE
1222 ,p_per_information27 in irc_pending_data.per_information27%TYPE
1223 ,p_per_information28 in irc_pending_data.per_information28%TYPE
1224 ,p_per_information29 in irc_pending_data.per_information29%TYPE
1225 ,p_per_information30 in irc_pending_data.per_information30%TYPE
1226 ,p_creation_date in irc_pending_data.creation_date%TYPE
1227 ,p_api_updating in boolean
1228 )
1229 is
1230 --
1231 l_error exception;
1232 l_per_information3 per_all_people_f.per_information3%TYPE;
1233 l_output varchar2(150);
1234 l_info_attribute number(2);
1235 l_proc varchar2(72) := g_package||'chk_US_per_information';
1236 --
1237 Begin
1238 hr_utility.set_location('Entering:'|| l_proc, 10);
1239 --
1240 -- Check the mandatory parameters
1241 --
1242 hr_api.mandatory_arg_error
1243 (p_api_name => l_proc
1244 ,p_argument => 'creation_date'
1245 ,p_argument_value => p_creation_date
1246 );
1247 --
1248 -- We know the per_information_category is US, so check the rest of
1249 -- the per_information fields within this context.
1250 --
1251 -- Check if the value for per information1 is set on insert or has
1252 -- changed on update.
1253 --
1254 if (((nvl(p_per_information1,hr_api.g_varchar2)
1255 <> nvl(irc_ipd_shd.g_old_rec.per_information1,hr_api.g_varchar2)
1256 and p_api_updating)
1257 or (NOT p_api_updating))
1258 and p_per_information1 is not null)
1259 then
1260 -- Check that per information1 exists in hr_lookups for the
1261 -- lookup type 'US_ETHNIC_GROUP' with an enabled flag set to 'Y'
1262 -- the person is active on creation date in hr_lookups.
1263 --
1264 if hr_api.not_exists_in_hr_lookups
1265 (p_effective_date => p_creation_date
1266 ,p_lookup_type => 'US_ETHNIC_GROUP'
1267 ,p_lookup_code => p_per_information1
1268 )
1269 then
1270 --
1274 end if;
1271 hr_utility.set_message(801, 'HR_7524_PER_INFO1_INVALID');
1272 hr_utility.raise_error;
1273 --
1275 end if;
1276 -- Check if the value for per information2 is set on insert or has
1277 -- changed on update.
1278 --
1279 if (((nvl(p_per_information2,hr_api.g_varchar2) <>
1280 nvl(irc_ipd_shd.g_old_rec.per_information2,hr_api.g_varchar2)
1281 and p_api_updating) or
1282 (NOT p_api_updating)) and
1283 p_per_information2 is not null)
1284 then
1285 --
1286 -- Check that per information2 exists in hr_lookups for the
1287 -- lookup type 'PER_US_I9_STATE' with an enabled flag set to 'Y' and that
1288 -- the person is active on creation date in hr_lookups.
1289 --
1290 if hr_api.not_exists_in_hr_lookups
1291 (p_effective_date => p_creation_date
1292 ,p_lookup_type => 'PER_US_I9_STATE'
1293 ,p_lookup_code => p_per_information2
1294 )
1295 then
1296 --
1297 hr_utility.set_message(801, 'HR_51243_PER_INFO2_INVALID');
1298 hr_utility.raise_error;
1299 end if;
1300 end if;
1301 -- Check if the value for per information3 is set on insert or has
1302 -- changed on update.
1303 --
1304 if (((nvl(p_per_information3,hr_api.g_varchar2) <>
1305 nvl(irc_ipd_shd.g_old_rec.per_information3,hr_api.g_varchar2)
1306 and p_api_updating) or
1307 (NOT p_api_updating)) and
1308 p_per_information3 is not null)
1309 then
1310 --
1311 -- Check if the per_information3 value is an 11 character date
1312 -- field.
1313 --
1314 l_per_information3 := p_per_information3;
1315 hr_chkfmt.changeformat(input => l_per_information3
1316 ,output => l_output
1317 ,format => 'D'
1318 ,curcode => NULL);
1319 end if;
1320 --
1321 -- Check if the value for per information4 is set on insert or has
1322 -- changed on update.
1323 --
1324 if (((nvl(p_per_information4,hr_api.g_varchar2) <>
1325 nvl(irc_ipd_shd.g_old_rec.per_information4,hr_api.g_varchar2)
1326 and p_api_updating) or
1327 (NOT p_api_updating)) and
1328 p_per_information4 is not null)
1329 then
1330 --
1331 -- Check that per information4 exists in hr_lookups for the
1332 -- lookup type 'US_VISA_TYPE' with an enabled flag set to 'Y'
1333 -- the person is active on creation date in hr_lookups.
1334 --
1335 if hr_api.not_exists_in_hr_lookups
1336 (p_effective_date => p_creation_date
1337 ,p_lookup_type => 'US_VISA_TYPE'
1338 ,p_lookup_code => p_per_information4
1339 )
1340 then
1341 --
1342 hr_utility.set_message(801, 'HR_51245_PER_INFO4_INVALID');
1343 hr_utility.raise_error;
1344 --
1345 end if;
1346 end if;
1347 --
1348 -- Check if the value for per information5 is set on insert or has
1349 -- changed on update.
1350 --
1351 if (((nvl(p_per_information5,hr_api.g_varchar2) <>
1352 nvl(irc_ipd_shd.g_old_rec.per_information5,hr_api.g_varchar2)
1353 and p_api_updating) or
1354 (NOT p_api_updating)) and
1355 p_per_information5 is not null)
1356 then
1357 --
1358 -- Check that per information5 exists in hr_lookups for the
1359 -- lookup type 'US_VISA_TYPE' with an enabled flag set to 'Y'
1360 -- the person is active on creation date in hr_lookups.
1361 --
1362 if hr_api.not_exists_in_hr_lookups
1363 (p_effective_date => p_creation_date
1364 ,p_lookup_type => 'US_VETERAN_STATUS'
1365 ,p_lookup_code => p_per_information5
1366 )
1367 then
1368 --
1369 hr_utility.set_message(801, 'HR_51246_PER_INFO5_INVALID');
1370 hr_utility.raise_error;
1371 --
1372 end if;
1373 end if;
1374 --
1375 -- Check if the value for per information6 is set on insert or has
1376 -- changed on update.
1377 --
1378 if (((nvl(p_per_information6,hr_api.g_varchar2) <>
1379 nvl(irc_ipd_shd.g_old_rec.per_information6,hr_api.g_varchar2)
1380 and p_api_updating) or
1381 (NOT p_api_updating)) and
1382 p_per_information6 is not null)
1383 then
1384 --
1385 -- Check that per information6 exists in hr_lookups for the
1386 -- lookup type 'YES_NO' with an enabled flag set to 'Y' and that
1387 -- the person is active on creation date in hr_lookups.
1388 --
1389 if hr_api.not_exists_in_hr_lookups
1390 (p_effective_date => p_creation_date
1391 ,p_lookup_type => 'YES_NO'
1392 ,p_lookup_code => p_per_information6
1393 )
1394 then
1395 --
1396 hr_utility.set_message(801, 'HR_51247_PER_INFO6_INVALID');
1397 hr_utility.raise_error;
1398 --
1399 end if;
1400 end if;
1401 --
1402 -- Check if the value for per information7 is set on insert or has
1403 -- changed on update.
1404 --
1405 if (((nvl(p_per_information7,hr_api.g_varchar2) <>
1406 nvl(irc_ipd_shd.g_old_rec.per_information7,hr_api.g_varchar2)
1407 and p_api_updating) or
1408 (NOT p_api_updating)) and
1409 p_per_information7 is not null)
1410 then
1411 --
1412 -- Check that per information7 exists in hr_lookups for the
1413 -- lookup type 'US_NEW_HIRE_STATUS' with an enabled flag set to 'Y'
1414 -- the person is active on creation date in hr_lookups.
1415 --
1416 if hr_api.not_exists_in_hr_lookups
1417 (p_effective_date => p_creation_date
1421 then
1418 ,p_lookup_type => 'US_NEW_HIRE_STATUS'
1419 ,p_lookup_code => p_per_information7
1420 )
1422 --
1423 hr_utility.set_message(801, 'HR_51285_PER_INFO7_INVALID');
1424 hr_utility.raise_error;
1425 --
1426 end if;
1427 --
1428 end if;
1429 --
1430 -- Check if the value for per information8 is set on insert or has
1431 -- changed on update.
1432 --
1433 if (((nvl(p_per_information8,hr_api.g_varchar2) <>
1434 nvl(irc_ipd_shd.g_old_rec.per_information8,hr_api.g_varchar2)
1435 and p_api_updating) or
1436 (NOT p_api_updating)) and
1437 p_per_information8 is not null)
1438 then
1439 --
1440 -- Check if per information7 is 'EXCL'
1441 --
1442 if nvl(p_per_information7,hr_api.g_varchar2) <> 'EXCL'
1443 then
1444 --
1445 -- Error: Field must be null because per_info7 is not 'EXCL'
1446 --
1447 hr_utility.set_message(801, 'HR_51286_PER_INFO8_NOT_NULL');
1448 hr_utility.raise_error;
1449 else
1450 --
1451 -- Check that per information7 exists in hr_lookups for the
1452 -- lookup type 'US_NEW_HIRE_EXCEPTIONS' with an enabled flag set to 'Y'
1453 -- the person is active on creation date in hr_lookups.
1454 --
1455 if hr_api.not_exists_in_hr_lookups
1456 (p_effective_date => p_creation_date
1457 ,p_lookup_type => 'US_NEW_HIRE_EXCEPTIONS'
1458 ,p_lookup_code => p_per_information8
1459 )
1460 then
1461 --
1462 hr_utility.set_message(801, 'HR_51287_PER_INFO8_INVALID');
1463 hr_utility.raise_error;
1464 --
1465 end if;
1466 end if;
1467 end if;
1468 --
1469 -- Check if the value for per information9 is set on insert or has
1470 -- changed on update.
1471 --
1472 if (((nvl(p_per_information9,hr_api.g_varchar2) <>
1473 nvl(irc_ipd_shd.g_old_rec.per_information9,hr_api.g_varchar2)
1474 and p_api_updating) or
1475 (NOT p_api_updating)) and
1476 p_per_information9 is not null)
1477 then
1478 --
1479 -- Check that per information9 exists in hr_lookups for the
1480 -- lookup type 'YES_NO' with an enabled flag set to 'Y' and that
1481 -- the person is active on creation date in hr_lookups.
1482 --
1483 if hr_api.not_exists_in_hr_lookups
1484 (p_effective_date => p_creation_date
1485 ,p_lookup_type => 'YES_NO'
1486 ,p_lookup_code => p_per_information9
1487 )
1488 then
1489 --
1490 hr_utility.set_message(801, 'HR_51288_PER_INFO9_INVALID');
1491 hr_utility.raise_error;
1492 --
1493 end if;
1494 end if;
1495 --
1496 if (((nvl(p_per_information10,hr_api.g_varchar2) <>
1497 nvl(irc_ipd_shd.g_old_rec.per_information10,hr_api.g_varchar2)
1498 and p_api_updating) or
1499 (NOT p_api_updating)) and
1500 p_per_information10 is not null)
1501 then
1502 --
1503 -- Check that per information10 exists in hr_lookups for the
1504 -- lookup type 'YES_NO' with an enabled flag set to 'Y' and that
1505 -- the person is active on creation date in hr_lookups.
1506 --
1507 if hr_api.not_exists_in_hr_lookups
1508 (p_effective_date => p_creation_date
1509 ,p_lookup_type => 'YES_NO'
1510 ,p_lookup_code => p_per_information10
1511 )
1512 then
1513 --
1514 hr_utility.set_message(801, 'PER_52390_PER_INFO10_INVALID');
1515 hr_utility.raise_error;
1516 --
1517 end if;
1518 end if;
1519 --
1520 -- Check if any of the remaining per_information parameters are not
1521 -- null
1522 -- (developer descriptive flexfields not used for US)
1523 --
1524
1525 -- TM removed check for p_per_information10 now that it is being used.
1526 if p_per_information11 is not null then
1527 l_info_attribute := 11;
1528 raise l_error;
1529 elsif p_per_information12 is not null then
1530 l_info_attribute := 12;
1531 raise l_error;
1532 elsif p_per_information13 is not null then
1533 l_info_attribute := 13;
1534 raise l_error;
1535 elsif p_per_information14 is not null then
1536 l_info_attribute := 14;
1537 raise l_error;
1538 elsif p_per_information15 is not null then
1539 l_info_attribute := 15;
1540 raise l_error;
1541 elsif p_per_information16 is not null then
1542 l_info_attribute := 16;
1543 raise l_error;
1544 elsif p_per_information17 is not null then
1545 l_info_attribute := 17;
1546 raise l_error;
1547 elsif p_per_information18 is not null then
1548 l_info_attribute := 18;
1549 raise l_error;
1550 elsif p_per_information19 is not null then
1551 l_info_attribute := 19;
1552 raise l_error;
1553 elsif p_per_information20 is not null then
1554 l_info_attribute := 20;
1555 raise l_error;
1556 elsif p_per_information21 is not null then
1557 l_info_attribute := 21;
1558 raise l_error;
1559 elsif p_per_information22 is not null then
1560 l_info_attribute := 22;
1561 raise l_error;
1562 elsif p_per_information23 is not null then
1563 l_info_attribute := 23;
1564 raise l_error;
1565 elsif p_per_information24 is not null then
1566 l_info_attribute := 24;
1567 raise l_error;
1568 elsif p_per_information25 is not null then
1569 l_info_attribute := 25;
1570 raise l_error;
1571 elsif p_per_information26 is not null then
1572 l_info_attribute := 26;
1573 raise l_error;
1577 elsif p_per_information28 is not null then
1574 elsif p_per_information27 is not null then
1575 l_info_attribute := 27;
1576 raise l_error;
1578 l_info_attribute := 28;
1579 raise l_error;
1580 elsif p_per_information29 is not null then
1581 l_info_attribute := 29;
1582 raise l_error;
1583 elsif p_per_information30 is not null then
1584 l_info_attribute := 30;
1585 raise l_error;
1586 end if;
1587 --
1588 exception
1589 when l_error then
1590 -- Error: Do not enter PER_INFORMATION99 for this legislation
1591 hr_utility.set_message(801, 'HR_7529_PER_INFO_NOT_NULL');
1592 hr_utility.set_message_token('NUM',to_char(l_info_attribute));
1593 hr_utility.raise_error;
1594 --
1595 hr_utility.set_location(' Leaving:'||l_proc,50);
1596 End chk_US_per_information;
1597 --
1598 --
1599 -- ---------------------------------------------------------------------------
1600 -- |-----------------------< chk_per_information >--------------------------|
1601 -- ---------------------------------------------------------------------------
1602 --
1603 -- Description:
1604 -- Checks that the values held in developer descriptive flexfields
1605 -- are valid
1606 --
1607 -- This routine calls separate local validation procedures to perform
1608 -- validation for each specific category. At present the suppported
1609 -- categories are 'GB', 'US'
1610
1611 -- Pre-conditions:
1612 -- None
1613 --
1614 -- In Arguments:
1615 -- p_rec
1616 --
1617 --
1618 -- Post Success:
1619 -- If the value in per_information_category value is 'GB' or 'US' then
1620 -- processing continues
1621 --
1622 -- Post Failure:
1623 -- If the value in per_information_category value is not 'GB' or 'US' then
1624 -- an application error will be raised and processing is terminated
1625 --
1626 -- Access Status:
1627 -- Internal Table Handler Use Only.
1628 --
1629 -- ----------------------------------------------------------------------------
1630 Procedure chk_per_information
1631 (p_rec in out nocopy irc_ipd_shd.g_rec_type
1632 ) is
1633 --
1634 l_error exception;
1635 l_api_updating boolean;
1636 l_info_attribute number(2);
1637 l_proc varchar2(72) := g_package || 'chk_per_information';
1638 --
1639 Begin
1640 hr_utility.set_location('Entering:'||l_proc,10);
1641 --
1642 --
1643 -- Only proceed with validation if:
1644 -- a) The current g_old_rec is current and
1645 -- b) Any of the per_information (developer descriptive flex) values have
1646 -- changed
1647 -- c) A record is being inserted
1648 l_api_updating := irc_ipd_shd.api_updating
1649 (p_pending_data_id => p_rec.pending_data_id
1650 );
1651 if ((l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information_category,
1652 hr_api.g_varchar2)
1653 <> nvl(p_rec.per_information_category,hr_api.g_varchar2)) or
1654 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information1,
1655 hr_api.g_varchar2)
1656 <> nvl(p_rec.per_information1,hr_api.g_varchar2)) or
1657 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information2,
1658 hr_api.g_varchar2)
1659 <> nvl(p_rec.per_information2,hr_api.g_varchar2)) or
1660 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information3,
1661 hr_api.g_varchar2)
1662 <> nvl(p_rec.per_information3,hr_api.g_varchar2)) or
1663 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information4,
1664 hr_api.g_varchar2)
1665 <> nvl(p_rec.per_information4,hr_api.g_varchar2)) or
1666 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information5,
1667 hr_api.g_varchar2)
1668 <> nvl(p_rec.per_information5,hr_api.g_varchar2)) or
1669 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information6,
1670 hr_api.g_varchar2)
1671 <> nvl(p_rec.per_information6,hr_api.g_varchar2)) or
1672 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information7,
1673 hr_api.g_varchar2)
1674 <> nvl(p_rec.per_information7,hr_api.g_varchar2)) or
1675 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information8,
1676 hr_api.g_varchar2)
1677 <> nvl(p_rec.per_information8,hr_api.g_varchar2)) or
1678 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information9,
1679 hr_api.g_varchar2)
1680 <> nvl(p_rec.per_information9,hr_api.g_varchar2)) or
1681 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information10,
1682 hr_api.g_varchar2)
1683 <> nvl(p_rec.per_information10,hr_api.g_varchar2)) or
1684 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information11,
1685 hr_api.g_varchar2)
1686 <> nvl(p_rec.per_information11,hr_api.g_varchar2)) or
1687 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information12,
1688 hr_api.g_varchar2)
1689 <> nvl(p_rec.per_information12,hr_api.g_varchar2)) or
1690 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information13,
1691 hr_api.g_varchar2)
1692 <> nvl(p_rec.per_information13,hr_api.g_varchar2)) or
1693 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information14,
1694 hr_api.g_varchar2)
1695 <> nvl(p_rec.per_information14,hr_api.g_varchar2)) or
1696 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information15,
1697 hr_api.g_varchar2)
1698 <> nvl(p_rec.per_information15,hr_api.g_varchar2)) or
1699 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information16,
1700 hr_api.g_varchar2)
1701 <> nvl(p_rec.per_information16,hr_api.g_varchar2)) or
1705 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information18,
1702 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information17,
1703 hr_api.g_varchar2)
1704 <> nvl(p_rec.per_information17,hr_api.g_varchar2)) or
1706 hr_api.g_varchar2)
1707 <> nvl(p_rec.per_information18,hr_api.g_varchar2)) or
1708 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information19,
1709 hr_api.g_varchar2)
1710 <> nvl(p_rec.per_information19,hr_api.g_varchar2)) or
1711 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information20,
1712 hr_api.g_varchar2)
1713 <> nvl(p_rec.per_information20,hr_api.g_varchar2)) or
1714 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information21,
1715 hr_api.g_varchar2)
1716 <> nvl(p_rec.per_information21,hr_api.g_varchar2)) or
1717 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information22,
1718 hr_api.g_varchar2)
1719 <> nvl(p_rec.per_information22,hr_api.g_varchar2)) or
1720 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information23,
1721 hr_api.g_varchar2)
1722 <> nvl(p_rec.per_information23,hr_api.g_varchar2)) or
1723 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information24,
1724 hr_api.g_varchar2)
1725 <> nvl(p_rec.per_information24,hr_api.g_varchar2)) or
1726 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information25,
1727 hr_api.g_varchar2)
1728 <> nvl(p_rec.per_information25,hr_api.g_varchar2)) or
1729 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information26,
1730 hr_api.g_varchar2)
1731 <> nvl(p_rec.per_information26,hr_api.g_varchar2)) or
1732 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information27,
1733 hr_api.g_varchar2)
1734 <> nvl(p_rec.per_information27,hr_api.g_varchar2)) or
1735 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information28,
1736 hr_api.g_varchar2)
1737 <> nvl(p_rec.per_information28,hr_api.g_varchar2)) or
1738 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information29,
1739 hr_api.g_varchar2)
1740 <> nvl(p_rec.per_information29,hr_api.g_varchar2)) or
1741 (l_api_updating and nvl(irc_ipd_shd.g_old_rec.per_information30,
1742 hr_api.g_varchar2)
1743 <> nvl(p_rec.per_information30,hr_api.g_varchar2)) or
1744 (NOT l_api_updating))
1745 then
1746 -- Check if the per_information_category is 'GB' or 'US' calling
1747 -- the appropriate validation routine or generating an error
1748 --
1749 -- If p_rec.per_information_category is not null then
1750 If p_rec.per_information_category = 'GB' then
1751 --
1752 -- GB specific validation.
1753 --
1754 hr_utility.set_location('Entering:'||l_proc,20);
1755 irc_ipd_bus.chk_GB_per_information
1756 (p_person_id => p_rec.person_id
1757 ,p_per_information_category => p_rec.per_information_category
1758 ,p_per_information1 => p_rec.per_information1
1759 ,p_per_information2 => p_rec.per_information2
1760 ,p_per_information3 => p_rec.per_information3
1761 ,p_per_information4 => p_rec.per_information4
1762 ,p_per_information5 => p_rec.per_information5
1763 ,p_per_information6 => p_rec.per_information6
1764 ,p_per_information7 => p_rec.per_information7
1765 ,p_per_information8 => p_rec.per_information8
1766 ,p_per_information9 => p_rec.per_information9
1767 ,p_per_information10 => p_rec.per_information10
1768 ,p_per_information11 => p_rec.per_information11
1769 ,p_per_information12 => p_rec.per_information12
1770 ,p_per_information13 => p_rec.per_information13
1771 ,p_per_information14 => p_rec.per_information14
1772 ,p_per_information15 => p_rec.per_information15
1773 ,p_per_information16 => p_rec.per_information16
1774 ,p_per_information17 => p_rec.per_information17
1775 ,p_per_information18 => p_rec.per_information18
1776 ,p_per_information19 => p_rec.per_information19
1777 ,p_per_information20 => p_rec.per_information20
1778 ,p_per_information21 => p_rec.per_information21
1779 ,p_per_information22 => p_rec.per_information22
1780 ,p_per_information23 => p_rec.per_information23
1781 ,p_per_information24 => p_rec.per_information24
1782 ,p_per_information25 => p_rec.per_information25
1783 ,p_per_information26 => p_rec.per_information26
1784 ,p_per_information27 => p_rec.per_information27
1785 ,p_per_information28 => p_rec.per_information28
1786 ,p_per_information29 => p_rec.per_information29
1787 ,p_per_information30 => p_rec.per_information30
1788 ,p_creation_date => p_rec.creation_date
1789 );
1790 elsif p_rec.per_information_category = 'US' then
1791 --
1792 -- US specific validation.
1793 --
1794 hr_utility.set_location('Entering:'||l_proc,30);
1795 irc_ipd_bus.chk_US_per_information
1796 (p_person_id => p_rec.person_id
1797 ,p_per_information_category => p_rec.per_information_category
1798 ,p_per_information1 => p_rec.per_information1
1799 ,p_per_information2 => p_rec.per_information2
1800 ,p_per_information3 => p_rec.per_information3
1801 ,p_per_information4 => p_rec.per_information4
1802 ,p_per_information5 => p_rec.per_information5
1806 ,p_per_information9 => p_rec.per_information9
1803 ,p_per_information6 => p_rec.per_information6
1804 ,p_per_information7 => p_rec.per_information7
1805 ,p_per_information8 => p_rec.per_information8
1807 ,p_per_information10 => p_rec.per_information10
1808 ,p_per_information11 => p_rec.per_information11
1809 ,p_per_information12 => p_rec.per_information12
1810 ,p_per_information13 => p_rec.per_information13
1811 ,p_per_information14 => p_rec.per_information14
1812 ,p_per_information15 => p_rec.per_information15
1813 ,p_per_information16 => p_rec.per_information16
1814 ,p_per_information17 => p_rec.per_information17
1815 ,p_per_information18 => p_rec.per_information18
1816 ,p_per_information19 => p_rec.per_information19
1817 ,p_per_information20 => p_rec.per_information20
1818 ,p_per_information21 => p_rec.per_information21
1819 ,p_per_information22 => p_rec.per_information22
1820 ,p_per_information23 => p_rec.per_information23
1821 ,p_per_information24 => p_rec.per_information24
1822 ,p_per_information25 => p_rec.per_information25
1823 ,p_per_information26 => p_rec.per_information26
1824 ,p_per_information27 => p_rec.per_information27
1825 ,p_per_information28 => p_rec.per_information28
1826 ,p_per_information29 => p_rec.per_information29
1827 ,p_per_information30 => p_rec.per_information30
1828 ,p_creation_date => p_rec.creation_date
1829 ,p_api_updating => l_api_updating
1830 );
1831 else
1832 irc_ipd_bus.chk_ddf(p_rec=>p_rec);
1833 end if;
1834 end if;
1835 exception
1836 when l_error then
1837 hr_utility.set_message(800, 'HR_7529_PER_INFO_NOT_NULL');
1838 hr_utility.set_message_token('NUM',to_char(l_info_attribute));
1839 hr_multi_message.add;
1840 hr_utility.set_location(' Leaving:'||l_proc,50);
1841 End chk_per_information;
1842 --
1843 -- ----------------------------------------------------------------------------
1844 -- |---------------------------< insert_validate >----------------------------|
1845 -- ----------------------------------------------------------------------------
1846 Procedure insert_validate
1847 (p_rec in out nocopy irc_ipd_shd.g_rec_type
1848 ) is
1849 --
1850 l_proc varchar2(72) := g_package||'insert_validate';
1851 --
1852 Begin
1853 hr_utility.set_location('Entering:'||l_proc, 5);
1854 --
1855 -- Call all supporting business operations
1856 -- Validate Dependent Attributes
1857 --
1858 hr_utility.set_location(l_proc, 20);
1859 --
1860 if (p_rec.vacancy_id is not null) then
1861 irc_ipd_bus.chk_vacancy_id(
1862 p_vacancy_id => p_rec.vacancy_id
1863 ,p_creation_date => p_rec.creation_date
1864 );
1865 irc_ipd_bus.chk_job_already_apld_for(
1866 p_vacancy_id => p_rec.vacancy_id
1867 ,p_email_address => p_rec.email_address
1868 );
1869 irc_ipd_bus.chk_sex(
1870 p_sex => p_rec.sex
1871 ,p_creation_date => p_rec.creation_date
1872 ,p_pending_data_id => p_rec.pending_data_id
1873 );
1874 end if;
1875 --
1876 hr_utility.set_location(l_proc, 30);
1877 --
1878 if (p_rec.allow_access is not null) then
1879 --
1880 hr_utility.set_location(l_proc, 40);
1881 --
1882 irc_ipd_bus.chk_allow_access(
1883 p_allow_access => p_rec.allow_access
1884 );
1885 end if;
1886 --
1887 hr_utility.set_location(l_proc, 50);
1888 -- Validate Developer Descriptive Flexfields
1889 --
1890 irc_ipd_bus.chk_per_information(
1891 p_rec => p_rec
1892 );
1893 --
1894 hr_utility.set_location(l_proc, 60);
1895 --
1896 irc_ipd_bus.chk_ddf(p_rec);
1897 --
1898 hr_utility.set_location(' Leaving:'||l_proc, 70);
1899 End insert_validate;
1900 --
1901 -- ----------------------------------------------------------------------------
1902 -- |---------------------------< update_validate >----------------------------|
1903 -- ----------------------------------------------------------------------------
1904 Procedure update_validate
1905 (p_rec in out nocopy irc_ipd_shd.g_rec_type
1906 ) is
1907 --
1908 l_proc varchar2(72) := g_package||'update_validate';
1909 --
1910 Begin
1911 hr_utility.set_location('Entering:'||l_proc, 5);
1912 --
1913 -- Call all supporting business operations
1914 --
1915 -- Validate Dependent Attributes
1916 --
1917 chk_non_updateable_args(
1918 p_rec => p_rec
1919 );
1920 --
1921 hr_utility.set_location(l_proc, 30);
1922 --
1923 irc_ipd_bus.chk_sex(
1924 p_sex => p_rec.sex
1925 ,p_creation_date => p_rec.creation_date
1926 ,p_pending_data_id => p_rec.pending_data_id
1927 );
1928 --
1929 hr_utility.set_location(l_proc, 40);
1930 --
1931 -- Validate Developer Descriptive Flexfields
1932 --
1933 irc_ipd_bus.chk_per_information(
1934 p_rec => p_rec
1935 );
1936 --
1937 hr_utility.set_location(l_proc, 50);
1938
1939 irc_ipd_bus.chk_ddf(p_rec);
1940 --
1941 hr_utility.set_location(' Leaving:'||l_proc, 60);
1942 End update_validate;
1943 --
1944 -- ----------------------------------------------------------------------------
1945 -- |---------------------------< delete_validate >----------------------------|
1946 -- ----------------------------------------------------------------------------
1947 Procedure delete_validate
1948 (p_rec in irc_ipd_shd.g_rec_type
1949 ) is
1950 --
1951 l_proc varchar2(72) := g_package||'delete_validate';
1952 --
1953 Begin
1954 hr_utility.set_location('Entering:'||l_proc, 5);
1955 --
1956 -- Call all supporting business operations
1957 --
1958 hr_utility.set_location(' Leaving:'||l_proc, 10);
1959 End delete_validate;
1960 --
1961 end irc_ipd_bus;
[Home] [Help]