[Home] [Help]
PACKAGE BODY: APPS.PER_VAC_BUS
Source
1 Package Body per_vac_bus as
2 /* $Header: pevacrhi.pkb 120.2 2010/12/24 11:51:11 avarri ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_vac_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_vacancy_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_vacancy_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id
29 from per_business_groups pbg
30 , per_all_vacancies vac
31 where vac.vacancy_id = p_vacancy_id
32 and pbg.business_group_id = vac.business_group_id;
33 --
34 -- Declare local variables
35 --
36 l_security_group_id number;
37 l_proc varchar2(72) := g_package||'set_security_group_id';
38 --
39 begin
40 --
41 hr_utility.set_location('Entering:'|| l_proc, 10);
42 --
43 -- Ensure that all the mandatory parameter are not null
44 --
45 hr_api.mandatory_arg_error
46 (p_api_name => l_proc
47 ,p_argument => 'vacancy_id'
48 ,p_argument_value => p_vacancy_id
49 );
50 --
51 open csr_sec_grp;
52 fetch csr_sec_grp into l_security_group_id;
53 --
54 if csr_sec_grp%notfound then
55 --
56 close csr_sec_grp;
57 --
58 -- The primary key is invalid therefore we must error
59 --
60 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
61 hr_multi_message.add
62 (p_associated_column1 => nvl(p_associated_column1,'VACANCY_ID'));
63 --
64 else
65 close csr_sec_grp;
66 --
67 -- Set the security_group_id in CLIENT_INFO
68 --
69 hr_api.set_security_group_id
70 (p_security_group_id => l_security_group_id
71 );
72 --
73 end if;
74 hr_utility.set_location(' Leaving:'|| l_proc, 20);
75 --
76 end set_security_group_id;
77 --
78 -- ---------------------------------------------------------------------------
79 -- |---------------------< return_legislation_code >-------------------------|
80 -- ---------------------------------------------------------------------------
81 --
82 Function return_legislation_code
83 (p_vacancy_id in number
84 )
85 Return Varchar2 Is
86 --
87 -- Declare cursor
88 --
89 cursor csr_leg_code is
90 select pbg.legislation_code
91 from per_business_groups pbg
92 , per_all_vacancies vac
93 where vac.vacancy_id = p_vacancy_id
94 and pbg.business_group_id = vac.business_group_id;
95 --
96 -- Declare local variables
97 --
98 l_legislation_code varchar2(150);
99 l_proc varchar2(72) := g_package||'return_legislation_code';
100 --
101 Begin
102 --
103 hr_utility.set_location('Entering:'|| l_proc, 10);
104 --
105 -- Ensure that all the mandatory parameter are not null
106 --
107 hr_api.mandatory_arg_error
108 (p_api_name => l_proc
109 ,p_argument => 'vacancy_id'
110 ,p_argument_value => p_vacancy_id
111 );
112 --
113 if ( nvl(per_vac_bus.g_vacancy_id, hr_api.g_number)
114 = p_vacancy_id) then
115 --
116 -- The legislation code has already been found with a previous
117 -- call to this function. Just return the value in the global
118 -- variable.
119 --
120 l_legislation_code := per_vac_bus.g_legislation_code;
121 hr_utility.set_location(l_proc, 20);
122 else
123 --
124 -- The ID is different to the last call to this function
125 -- or this is the first call to this function.
126 --
127 open csr_leg_code;
128 fetch csr_leg_code into l_legislation_code;
129 --
130 if csr_leg_code%notfound then
131 --
132 -- The primary key is invalid therefore we must error
133 --
134 close csr_leg_code;
135 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
136 fnd_message.raise_error;
137 end if;
138 hr_utility.set_location(l_proc,30);
139 --
140 -- Set the global variables so the values are
141 -- available for the next call to this function.
142 --
143 close csr_leg_code;
144 per_vac_bus.g_vacancy_id := p_vacancy_id;
145 per_vac_bus.g_legislation_code := l_legislation_code;
146 end if;
147 hr_utility.set_location(' Leaving:'|| l_proc, 40);
148 return l_legislation_code;
149 end return_legislation_code;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |------------------------------< chk_df >----------------------------------|
153 -- ----------------------------------------------------------------------------
154 --
155 -- Description:
156 -- Validates all the Descriptive Flexfield values.
157 --
158 -- Prerequisites:
159 -- All other columns have been validated. Must be called as the
160 -- last step from insert_validate and update_validate.
161 --
162 -- In Arguments:
163 -- p_rec
164 --
165 -- Post Success:
166 -- if the Descriptive Flexfield structure column and data values are
167 -- all valid this procedure will end normally and processing will
168 -- continue.
169 --
170 -- Post Failure:
171 -- if the Descriptive Flexfield structure column value or any of
172 -- the data values are invalid then an application error is raised as
173 -- a PL/SQL exception.
174 --
175 -- Access Status:
176 -- Internal Row Handler Use Only.
177 --
178 -- ----------------------------------------------------------------------------
179 procedure chk_df
180 (p_rec in per_vac_shd.g_rec_type
181 ) is
182 --
183 l_proc varchar2(72) := g_package || 'chk_df';
184 --
185 begin
186 hr_utility.set_location('Entering:'||l_proc,10);
187 --
188 if ((p_rec.vacancy_id is not null) and (
189 nvl(per_vac_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
190 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
191 nvl(per_vac_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
192 nvl(p_rec.attribute1, hr_api.g_varchar2) or
193 nvl(per_vac_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
194 nvl(p_rec.attribute2, hr_api.g_varchar2) or
195 nvl(per_vac_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
196 nvl(p_rec.attribute3, hr_api.g_varchar2) or
197 nvl(per_vac_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
198 nvl(p_rec.attribute4, hr_api.g_varchar2) or
199 nvl(per_vac_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
200 nvl(p_rec.attribute5, hr_api.g_varchar2) or
201 nvl(per_vac_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
202 nvl(p_rec.attribute6, hr_api.g_varchar2) or
203 nvl(per_vac_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
204 nvl(p_rec.attribute7, hr_api.g_varchar2) or
205 nvl(per_vac_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
206 nvl(p_rec.attribute8, hr_api.g_varchar2) or
207 nvl(per_vac_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
208 nvl(p_rec.attribute9, hr_api.g_varchar2) or
209 nvl(per_vac_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
210 nvl(p_rec.attribute10, hr_api.g_varchar2) or
211 nvl(per_vac_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
212 nvl(p_rec.attribute11, hr_api.g_varchar2) or
213 nvl(per_vac_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
214 nvl(p_rec.attribute12, hr_api.g_varchar2) or
215 nvl(per_vac_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
216 nvl(p_rec.attribute13, hr_api.g_varchar2) or
217 nvl(per_vac_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
218 nvl(p_rec.attribute14, hr_api.g_varchar2) or
219 nvl(per_vac_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
220 nvl(p_rec.attribute15, hr_api.g_varchar2) or
221 nvl(per_vac_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
222 nvl(p_rec.attribute16, hr_api.g_varchar2) or
223 nvl(per_vac_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
224 nvl(p_rec.attribute17, hr_api.g_varchar2) or
225 nvl(per_vac_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
226 nvl(p_rec.attribute18, hr_api.g_varchar2) or
227 nvl(per_vac_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
228 nvl(p_rec.attribute19, hr_api.g_varchar2) or
229 nvl(per_vac_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
230 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
231 or (p_rec.vacancy_id is null) then
232 --
233 -- Only execute the validation if absolutely necessary:
234 -- a) During update, the structure column value or any
235 -- of the attribute values have actually changed.
236 -- b) During insert.
237 --
238 hr_dflex_utility.ins_or_upd_descflex_attribs
239 (p_appl_short_name => 'PER'
240 ,p_descflex_name => 'PER_VACANCIES'
241 ,p_attribute_category => p_rec.attribute_category
242 ,p_attribute1_name => 'ATTRIBUTE1'
243 ,p_attribute1_value => p_rec.attribute1
244 ,p_attribute2_name => 'ATTRIBUTE2'
245 ,p_attribute2_value => p_rec.attribute2
246 ,p_attribute3_name => 'ATTRIBUTE3'
247 ,p_attribute3_value => p_rec.attribute3
248 ,p_attribute4_name => 'ATTRIBUTE4'
249 ,p_attribute4_value => p_rec.attribute4
250 ,p_attribute5_name => 'ATTRIBUTE5'
251 ,p_attribute5_value => p_rec.attribute5
252 ,p_attribute6_name => 'ATTRIBUTE6'
253 ,p_attribute6_value => p_rec.attribute6
254 ,p_attribute7_name => 'ATTRIBUTE7'
255 ,p_attribute7_value => p_rec.attribute7
256 ,p_attribute8_name => 'ATTRIBUTE8'
257 ,p_attribute8_value => p_rec.attribute8
258 ,p_attribute9_name => 'ATTRIBUTE9'
259 ,p_attribute9_value => p_rec.attribute9
260 ,p_attribute10_name => 'ATTRIBUTE10'
261 ,p_attribute10_value => p_rec.attribute10
262 ,p_attribute11_name => 'ATTRIBUTE11'
263 ,p_attribute11_value => p_rec.attribute11
264 ,p_attribute12_name => 'ATTRIBUTE12'
265 ,p_attribute12_value => p_rec.attribute12
266 ,p_attribute13_name => 'ATTRIBUTE13'
267 ,p_attribute13_value => p_rec.attribute13
268 ,p_attribute14_name => 'ATTRIBUTE14'
269 ,p_attribute14_value => p_rec.attribute14
270 ,p_attribute15_name => 'ATTRIBUTE15'
271 ,p_attribute15_value => p_rec.attribute15
272 ,p_attribute16_name => 'ATTRIBUTE16'
273 ,p_attribute16_value => p_rec.attribute16
274 ,p_attribute17_name => 'ATTRIBUTE17'
275 ,p_attribute17_value => p_rec.attribute17
276 ,p_attribute18_name => 'ATTRIBUTE18'
277 ,p_attribute18_value => p_rec.attribute18
278 ,p_attribute19_name => 'ATTRIBUTE19'
279 ,p_attribute19_value => p_rec.attribute19
280 ,p_attribute20_name => 'ATTRIBUTE20'
281 ,p_attribute20_value => p_rec.attribute20
282 );
283 end if;
284 --
285 hr_utility.set_location(' Leaving:'||l_proc,20);
286 end chk_df;
287 --
288 -- ----------------------------------------------------------------------------
289 -- |-----------------------< chk_non_updateable_args >------------------------|
290 -- ----------------------------------------------------------------------------
291 -- {Start Of Comments}
292 --
293 -- Description:
294 -- This procedure is used to ensure that non updateable attributes have
295 -- not been updated. if an attribute has been updated an error is generated.
296 --
297 -- Pre Conditions:
298 -- g_old_rec has been populated with details of the values currently in
299 -- the database.
300 --
301 -- In Arguments:
302 -- p_rec has been populated with the updated values the user would like the
303 -- record set to.
304 --
305 -- Post Success:
306 -- Processing continues if all the non updateable attributes have not
307 -- changed.
308 --
309 -- Post Failure:
310 -- An application error is raised if any of the non updatable attributes
311 -- have been altered.
312 --
313 -- {End Of Comments}
314 -- ----------------------------------------------------------------------------
315 Procedure chk_non_updateable_args
316 (p_rec in per_vac_shd.g_rec_type
317 ) IS
318 --
319 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
320 --
321 Begin
322 --
323 -- Only proceed with the validation if a row exists for the current
324 -- record in the HR Schema.
325 --
326 if not per_vac_shd.api_updating
327 (p_vacancy_id => p_rec.vacancy_id
328 ,p_object_version_number => p_rec.object_version_number
329 ) then
330 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
331 fnd_message.set_token('PROCEDURE ', l_proc);
332 fnd_message.set_token('STEP ', '5');
333 fnd_message.raise_error;
334 end if;
335
336
337 if per_vac_shd.g_old_rec.business_group_id <> p_rec.business_group_id
338 then
339 hr_api.argument_changed_error
340 (p_api_name => l_proc
341 ,p_argument => 'business_group_id'
342 ,p_base_table => per_vac_shd.g_tab_name
343 );
344 end if;
345
346 if per_vac_shd.g_old_rec.vacancy_id <> p_rec.vacancy_id then
347 hr_api.argument_changed_error
348 (p_api_name => l_proc
349 ,p_argument => 'vacancy_id'
350 ,p_base_table => per_vac_shd.g_tab_name
351 );
352 end if;
353
354 if per_vac_shd.g_old_rec.name <> p_rec.name then
355 hr_api.argument_changed_error
356 (p_api_name => l_proc
357 ,p_argument => 'name'
358 ,p_base_table => per_vac_shd.g_tab_name
359 );
360 end if;
361 --
362 End chk_non_updateable_args;
363
364 --
365 -- ----------------------------------------------------------------------------
366 -- |-------------------------------< chk_name>--------------------------------|
367 -- ----------------------------------------------------------------------------
368 -- {Start Of Comments}
369 --
370 -- Description:
371 -- This procedure ensures a valid vacancy name
372 -- Pre Conditions:
373 -- g_old_rec has been populated with details of the values currently in
374 -- the database.
375 --
376 -- In Arguments:
377 -- p_name
378 -- p_business_group_id
379 -- Post Success:
380 -- Processing continues if vacancy name is not null and unique
381 --
382 -- Post Failure:
383 -- An application error is raised if vacacny name is null or exists already
384 --
385 -- {End Of Comments}
386 -- ----------------------------------------------------------------------------
387 Procedure chk_name
388 (p_name in per_all_vacancies.name%TYPE
389 ,p_business_group_id in per_all_vacancies.business_group_id%TYPE
390 ) IS
391 --
392 l_proc varchar2(72) := g_package || 'chk_name';
393 l_name varchar2(1);
394 cursor csr_name is
395 select null
396 from per_all_vacancies
397 where name = p_name
398 and business_group_id = p_business_group_id;
399 --
400 Begin
401 hr_utility.set_location('Entering:'||l_proc,10);
402 --
403 if (p_name is null)
404 then
405 hr_api.mandatory_arg_error
406 (p_api_name => l_proc
407 ,p_argument => 'NAME'
408 ,p_argument_value => p_name
409 );
410 end if;
411 --
412 open csr_name;
413 fetch csr_name into l_name;
414 --
415 hr_utility.set_location(l_proc,20);
416 if (csr_name%found)
417 then
418 close csr_name;
419 fnd_message.set_name('PER','IRC_412115_DUPLICATE_VAC_NAME');
420 fnd_message.set_token('INFORMATION_TYPE','VACANCY');
421 fnd_message.raise_error;
422 end if;
423 close csr_name;
424 --
425 hr_utility.set_location(' Leaving:'||l_proc,30);
426 exception
427 when app_exception.application_exception then
428 if hr_multi_message.exception_add
429 (p_associated_column1 => 'PER_ALL_VACANCIES.NAME'
430 ) then
431 hr_utility.set_location(' Leaving:'|| l_proc, 40);
432 raise;
433 end if;
434 hr_utility.set_location(' Leaving:'|| l_proc, 50);
435 End chk_name;
436 --
437 -- ----------------------------------------------------------------------------
438 -- |---------------------------<chk_vacancy_dates>----------------------------|
439 -- ----------------------------------------------------------------------------
440 -- {Start Of Comments}
441 --
442 -- Description:
443 -- This procedure ensures valid dates are entered
444 -- Pre Conditions:
445 -- g_old_rec has been populated with details of the values currently in
446 -- the database.
447 --
448 -- In Arguments:
449 -- p_vacancy_id
450 -- p_object_version_number
451 -- p_date_from
452 -- p_date_to
453 -- Post Success:
454 -- Processing continues if from and to dates are valid and from date is lesser
455 -- to date
456 --
457 -- Post Failure:
458 -- An application error is raised if dates entered are not valid
459 --
460 -- {End Of Comments}
461 -- ----------------------------------------------------------------------------
462 Procedure chk_vacancy_dates
463 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
464 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
465 ,p_date_from in per_all_vacancies.date_from%TYPE
466 ,p_date_to in per_all_vacancies.date_to%TYPE
467 ) IS
468 --
469 l_proc varchar2(72) := g_package || 'chk_vacancy_dates';
470 l_api_updating boolean;
471 --
472 Begin
473 hr_utility.set_location('Entering:'||l_proc,10);
474 l_api_updating := per_vac_shd.api_updating
475 (p_vacancy_id => p_vacancy_id
476 ,p_object_version_number => p_object_version_number);
477 --
478 hr_utility.set_location(l_proc,20);
479 if ((l_api_updating
480 and
481 (nvl(per_vac_shd.g_old_rec.date_from, hr_api.g_date) <>
482 nvl(p_date_from, hr_api.g_date) or
483 nvl(per_vac_shd.g_old_rec.date_to, hr_api.g_date) <>
484 nvl(p_date_to, hr_api.g_date)))
485 or
486 (NOT l_api_updating)) then
487 --
488 hr_utility.set_location(l_proc,30);
489 if (p_date_from is null)
490 then
491 fnd_message.set_name('PER','PER_289443_VAC_DATE_FROM_MND');
492 hr_multi_message.add
493 (p_associated_column1 => 'PER_ALL_VACANCIES.DATE_FROM'
494 );
495 hr_multi_message.end_validation_set();
496 end if;
497 hr_utility.set_location(l_proc,40);
498 --
499 if (p_date_from > nvl(p_date_to,hr_api.g_eot))
500 then
501 fnd_message.set_name('PER','IRC_ALL_DATE_START_END');
502 hr_multi_message.add
503 (p_associated_column1 => 'PER_ALL_VACANCIES.DATE_FROM'
504 ,p_associated_column2 => 'PER_ALL_VACANCIES.DATE_TO'
505 );
506 end if;
507 end if;
508 --
509 hr_utility.set_location(' Leaving:'||l_proc,50);
510 End chk_vacancy_dates;
511 --
512 -- ----------------------------------------------------------------------------
513 -- |-----------------------------< chk_status >-------------------------------|
514 -- ----------------------------------------------------------------------------
515 -- {Start Of Comments}
516 --
517 -- Description:
518 -- This procedure ensures a valid vacancy status
519 -- Pre Conditions:
520 -- g_old_rec has been populated with details of the values currently in
521 -- the database.
522 --
523 -- In Arguments:
524 -- p_vacancy_id
525 -- p_object_version_number
526 -- p_status
527 -- p_effective_date
528 -- Post Success:
529 -- Processing continues if status is valid
530
531 --
532 -- Post Failure:
533 -- An application error is raised if status code is not valid
534 --
535 -- {End Of Comments}
536 -- ----------------------------------------------------------------------------
537 Procedure chk_status
538 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
539 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
540 ,p_status in per_all_vacancies.status%TYPE
541 ,p_effective_date in date
542 ) IS
543 --
544 l_proc varchar2(72) := g_package || 'chk_status';
545 l_api_updating boolean;
546 l_exists boolean;
547 --
548 Begin
549 --
550 hr_utility.set_location('Entering:'||l_proc,10);
551 l_api_updating := per_vac_shd.api_updating
552 (p_vacancy_id => p_vacancy_id
553 ,p_object_version_number => p_object_version_number);
554 --
555 hr_utility.set_location(l_proc,20);
556 if ((l_api_updating
557 and
558 nvl(per_vac_shd.g_old_rec.status, hr_api.g_varchar2) <>
559 nvl(p_status, hr_api.g_varchar2))
560 or
561 (NOT l_api_updating)) then
562 if (p_status is not null)
563 then
564 l_exists := hr_api.not_exists_in_hr_lookups(p_effective_date
565 ,'VACANCY_STATUS'
566 ,p_status);
567 hr_utility.set_location(l_proc,30);
568 if (l_exists = true)
569 then
570 fnd_message.set_name('PER','PER_289444_VAC_INV_STATUS_CODE');
571 fnd_message.raise_error;
572 end if;
573 hr_utility.set_location(l_proc,30);
574 end if;
575 end if;
576 --
577 hr_utility.set_location(' Leaving:'||l_proc,40);
578 exception
579 when app_exception.application_exception then
580 if hr_multi_message.exception_add
581 (p_associated_column1 => 'PER_ALL_VACANCIES.STATUS'
582 ) then
583 hr_utility.set_location(' Leaving:'|| l_proc, 50);
584 raise;
585 end if;
586 hr_utility.set_location(' Leaving:'|| l_proc, 60);
587 --
588 End chk_status;
589 --
590 -- ----------------------------------------------------------------------------
591 -- |----------------------<chk_budget_measurement_type>-----------------------|
592 -- ----------------------------------------------------------------------------
593 -- {Start Of Comments}
594 --
595 -- Description:
596 -- This procedure ensures a valid budget measurement type
597 -- Pre Conditions:
598 -- g_old_rec has been populated with details of the values currently in
599 -- the database.
600 --
601 -- In Arguments:
602 -- p_vacancy_id
603 -- p_object_version_number
604 -- p_budget_measurement_type
605 -- p_effective_date
606 -- Post Success:
607 -- Processing continues if budget measurement code is valid
608
609 --
610 -- Post Failure:
611 -- An application error is raised if budget measurement code is not valid
612 --
613 -- {End Of Comments}
614 -- ----------------------------------------------------------------------------
615 Procedure chk_budget_measurement_type
616 (
617 p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
618 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
619 ,p_budget_measurement_type in per_all_vacancies.budget_measurement_type%TYPE
620 ,p_effective_date in date
621 ) IS
622 --
623 l_proc varchar2(72) := g_package || 'chk_budget_measurement_type';
624 l_api_updating boolean;
625 --
626 Begin
627 hr_utility.set_location('Entering:'||l_proc,10);
628 --
629 l_api_updating := per_vac_shd.api_updating
630 (p_vacancy_id => p_vacancy_id
631 ,p_object_version_number => p_object_version_number);
632 --
633 hr_utility.set_location(l_proc,20);
634 if ((l_api_updating
635 and
636 nvl(per_vac_shd.g_old_rec.budget_measurement_type,hr_api.g_varchar2) <>
637 nvl(p_budget_measurement_type, hr_api.g_varchar2))
638 or
639 (NOT l_api_updating)) then
640 --
641 if (p_budget_measurement_type is not null)
642 then
643 l_api_updating := hr_api.not_exists_in_hr_lookups(p_effective_date
644 ,'BUDGET_MEASUREMENT_TYPE'
645 ,p_budget_measurement_type);
646 hr_utility.set_location(l_proc,30);
647 if (l_api_updating = true)
648 then
649 fnd_message.set_name('PER','PER_289445_VAC_INV_BUD_MST_TYP');
650 fnd_message.raise_error;
651 end if;
652 hr_utility.set_location(l_proc,40);
653 end if;
654 end if;
655 --
656 hr_utility.set_location(' Leaving:'||l_proc,50);
657 exception
658 when app_exception.application_exception then
659 if hr_multi_message.exception_add
660 (p_associated_column1 => 'PER_ALL_VACANCIES.BUDGET_MEASUREMENT_TYPE'
661 ) then
662 hr_utility.set_location(' Leaving:'|| l_proc, 60);
663 raise;
664 end if;
665 hr_utility.set_location(' Leaving:'|| l_proc, 70);
666 End chk_budget_measurement_type;
667 --
668 -- ----------------------------------------------------------------------------
669 -- |---------------------------<chk_security_method>--------------------------|
670 -- ----------------------------------------------------------------------------
671 -- {Start Of Comments}
672 --
673 -- Description:
674 -- This procedure ensures a valid security method
675 -- Pre Conditions:
676 -- g_old_rec has been populated with details of the values currently in
677 -- the database.
678 --
679 -- In Arguments:
680 -- p_vacancy_id
681 -- p_object_version_number
682 -- p_security_method
683 -- p_effective_date
684 -- Post Success:
685 -- Processing continues if security method is valid
686 --
687 -- Post Failure:
688 -- An application error is raised if budget measurement code is not valid
689 --
690 -- {End Of Comments}
691 -- ----------------------------------------------------------------------------
692 Procedure chk_security_method
693 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
694 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
695 ,p_security_method in per_all_vacancies.security_method%TYPE
696 ,p_effective_date in date
697 ) IS
698 --
699 l_proc varchar2(72) := g_package || 'chk_security_method';
700 l_api_updating boolean;
701 --
702 Begin
703 hr_utility.set_location('Entering:'||l_proc,10);
704 --
705 l_api_updating := per_vac_shd.api_updating
706 (p_vacancy_id => p_vacancy_id
707 ,p_object_version_number => p_object_version_number);
708 --
709 hr_utility.set_location(l_proc,20);
710 if ((l_api_updating
711 and
712 nvl(per_vac_shd.g_old_rec.security_method,hr_api.g_varchar2) <>
713 nvl(p_security_method, hr_api.g_varchar2))
714 or
715 (NOT l_api_updating)) then
716 --
717 hr_utility.set_location(l_proc,30);
718 if(p_security_method is not null)
719 then
720 l_api_updating := hr_api.not_exists_in_hr_lookups(p_effective_date
721 ,'IRC_SECURITY_METHOD'
722 ,p_security_method);
723 hr_utility.set_location(l_proc,40);
724 if (l_api_updating = true)
725 then
726 fnd_message.set_name('PER','PER_289446_VAC_INV_SEC_METHOD');
727 fnd_message.raise_error;
728 end if;
729 hr_utility.set_location(l_proc,50);
730 end if;
731 end if;
732 hr_utility.set_location(' Leaving:'||l_proc,60);
733 exception
734 when app_exception.application_exception then
735 if hr_multi_message.exception_add
736 (p_associated_column1 => 'PER_ALL_VACANCIES.SECURITY_METHOD'
737 ) then
738 hr_utility.set_location(' Leaving:'|| l_proc, 70);
739 raise;
740 end if;
741 hr_utility.set_location(' Leaving:'|| l_proc, 80);
742 End chk_security_method;
743 --
744 -- ----------------------------------------------------------------------------
745 -- |--------------------------<chk_requisition_id>----------------------------|
746 -- ----------------------------------------------------------------------------
747 -- {Start Of Comments}
748 --
749 -- Description:
750 -- This procedure ensures requisition column exsits in per_requisitions table
751 -- Pre Conditions:
752 -- g_old_rec has been populated with details of the values currently in
753 -- the database.
754 --
755 -- In Arguments:
756 -- p_requisition_id
757 -- p_date_from
758 -- p_business_group_id
759 -- Post Success:
760 -- Processing continues if requisition id exists
761 --
762 -- Post Failure:
763 -- An application error is raised if requisition id is not valid
764 --
765 -- {End Of Comments}
766 -- ----------------------------------------------------------------------------
767 Procedure chk_requisition_id
768 (p_requisition_id in per_all_vacancies.requisition_id%TYPE
769 ,p_date_from in per_all_vacancies.date_from%TYPE
770 ,p_business_group_id in per_all_vacancies.business_group_id%TYPE
771 ) IS
772 --
773 l_proc varchar2(72) := g_package || 'chk_requisition_id';
774 l_date_from per_all_vacancies.date_from%TYPE;
775 l_date_to per_all_vacancies.date_to%TYPE;
776 cursor csr_requisition is
777 select date_from,date_to
778 from per_requisitions
779 where requisition_id = p_requisition_id
780 and business_group_id = p_business_group_id;
781 --
782 Begin
783 --
784 hr_utility.set_location('Entering:'||l_proc,10);
785 open csr_requisition;
786 fetch csr_requisition into l_date_from,l_date_to;
787 if (csr_requisition%NOTFOUND) then
788 close csr_requisition;
789 fnd_message.set_name('PER','PER_289447_VAC_INV_REQ_ID');
790 hr_multi_message.add
791 (p_associated_column1 => 'PER_ALL_VACANCIES.REQUISITION_ID'
792 );
793 else
794 close csr_requisition;
795 hr_utility.set_location(l_proc,20);
796 if(p_date_from not between l_date_from and nvl(l_date_to,hr_api.g_eot)) then
797 fnd_message.set_name('PER','PER_289448_VAC_REQ_ID_INV_DATE');
798 hr_multi_message.add
799 (p_associated_column1 => 'PER_ALL_VACANCIES.REQUISITION_ID'
800 ,p_associated_column2 => 'PER_ALL_VACANCIES.DATE_FROM'
801 ,p_associated_column3 => 'PER_ALL_VACANCIES.DATE_TO'
802 );
803 end if;
804 end if;
805 --
806 hr_utility.set_location(' Leaving:'||l_proc,30);
807 End chk_requisition_id;
808 --
809 -- ---------------------------------------------------------------------------
810 -- |------------------------< chk_position_id >------------------------------|
811 -- ---------------------------------------------------------------------------
812 --
813 procedure chk_position_id
814 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
815 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
816 ,p_position_id in per_all_vacancies.position_id%TYPE
817 ,p_business_group_id in per_all_vacancies.business_group_id%TYPE
818 ,p_effective_date in date
819 )
820 is
821 --
822 l_proc varchar2(72) := g_package||'chk_position_id';
823 l_exists varchar2(1);
824 l_api_updating boolean;
825 l_position_id per_all_vacancies.position_id%TYPE;
826 l_pos_bus_group_id per_all_vacancies.business_group_id%TYPE;
827 --
828 cursor csr_valid_pos is
829 select hp.business_group_id
830 from hr_all_positions_f hp
831 , per_shared_types ps
832 where hp.position_id = p_position_id
833 and p_effective_date
834 between hp.effective_start_date
835 and hp.effective_end_date
836 and p_effective_date
837 between hp.date_effective
838 and nvl(hp.date_end, hr_api.g_eot)
839 and ps.shared_type_id = hp.availability_status_id
840 and ps.system_type_cd = 'ACTIVE' ;
841 --
842 begin
843 hr_utility.set_location('Entering:'|| l_proc, 10);
844 --
845 -- Only proceed with validation if :
846 -- a) The current g_old_rec is current and
847 -- b) The value for position_id has changed
848 --
849 l_api_updating := per_vac_shd.api_updating
850 (p_vacancy_id => p_vacancy_id
851 ,p_object_version_number => p_object_version_number);
852 --
853 hr_utility.set_location(l_proc,20);
854 if ((l_api_updating
855 and
856 nvl(per_vac_shd.g_old_rec.position_id, hr_api.g_number) <>
857 nvl(p_position_id, hr_api.g_number))
858 or
859 (NOT l_api_updating)) then
860 hr_utility.set_location(l_proc, 30);
861 --
862 -- Check that if the value for position_id is not null
863 -- then it exists date effective in HR_POSITIONS
864 --
865 if p_position_id is not null then
866 --
867 -- Check if the position_id exists date effectively
868 --
869 open csr_valid_pos;
870 fetch csr_valid_pos into l_pos_bus_group_id;
871 if csr_valid_pos%notfound then
872 close csr_valid_pos;
873 fnd_message.set_name('PER','PER_289449_VAC_INV_POS_ID');
874 fnd_message.raise_error;
875 end if;
876 close csr_valid_pos;
877 hr_utility.set_location(l_proc, 40);
878 --
879 -- Check if the business_group_id for the assignment matches
880 -- the business_group_id in HR_POSITIONS date effectively.
881 --
882 if l_pos_bus_group_id <> p_business_group_id then
883 --
884 fnd_message.set_name('PAY', 'HR_51009_ASG_INVALID_BG_POS');
885 fnd_message.raise_error;
886 --
887 end if;
888 hr_utility.set_location(l_proc, 50);
889 --
890 end if;
891 --
892 end if;
893 --
894 hr_utility.set_location('Leaving'||l_proc, 80);
895 exception
896 when app_exception.application_exception then
897 if hr_multi_message.exception_add
898 (p_associated_column1 => 'PER_ALL_VACANCIES.POSITION_ID'
899 ) then
900 hr_utility.set_location(' Leaving:'|| l_proc, 40);
901 raise;
902 end if;
903 hr_utility.set_location(' Leaving:'|| l_proc, 50);
904 end chk_position_id;
905 ------------------------------------------------------------------------------
906 -------------------< chk_position_id_grade_id >-------------------------------
907 ------------------------------------------------------------------------------
908 --
909 procedure chk_position_id_grade_id
910 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
911 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
912 ,p_position_id in per_all_vacancies.position_id%TYPE
913 ,p_grade_id in per_all_vacancies.grade_id%TYPE
914 ,p_effective_date in date
915 ,p_inv_pos_grade_warning out nocopy boolean
916 )
917 as
918 l_proc varchar2(72) := g_package||'chk_position_id_grade_id';
919 l_exists varchar2(1);
920 l_api_updating boolean;
921 l_inv_pos_grade_warning boolean := false;
922 --
923 cursor csr_valid_pos_val_grd is
924 select null
925 from per_valid_grades
926 where position_id = p_position_id
927 and grade_id = p_grade_id
928 and p_effective_date
929 between date_from
930 and nvl(date_to, hr_api.g_eot);
931 --
932 begin
933 --
934 hr_utility.set_location('Entering:'|| l_proc, 1);
935 if hr_multi_message.no_exclusive_error
936 (p_check_column1 => 'PER_ALL_VACANCIES.POSITION_ID'
937 ,p_check_column2 => 'PER_ALL_VACANCIES.GRADE_ID'
938 ,p_check_column3 => 'PER_ALL_VACANCIES.DATE_FROM'
939 ) then
940 --
941 -- Only proceed with validation if :
942 -- a) The current g_old_rec is current and
943 -- b) The value for position_id or grade_id has changed
944 --
945 l_api_updating := per_vac_shd.api_updating
946 (p_vacancy_id => p_vacancy_id
947 ,p_object_version_number => p_object_version_number);
948 --
949 if (l_api_updating and
950 ((nvl(per_vac_shd.g_old_rec.position_id, hr_api.g_number) <>
951 nvl(p_position_id, hr_api.g_number))
952 or
953 (nvl(per_vac_shd.g_old_rec.grade_id, hr_api.g_number) <>
954 nvl(p_grade_id, hr_api.g_number))
955 ))
956 or
957 (NOT l_api_updating) then
958 --
959 hr_utility.set_location(l_proc, 2);
960 --
961 -- Check that position_id and grade_id both contain not null values
962 --
963 if p_position_id is not null and p_grade_id is not null then
964 --
965 -- Check if the position_id and grade_id exist date effectively
966 --
967 open csr_valid_pos_val_grd;
968 fetch csr_valid_pos_val_grd into l_exists;
969 if csr_valid_pos_val_grd%notfound then
970 l_inv_pos_grade_warning := true;
971 end if;
972 close csr_valid_pos_val_grd;
973 hr_utility.set_location(l_proc, 3);
974 --
975 end if;
976 --
977 end if;
978 --
979 p_inv_pos_grade_warning := l_inv_pos_grade_warning;
980 end if;
981 hr_utility.set_location('Leaving'||l_proc, 4);
982
983 end chk_position_id_grade_id;
984 --
985 -- ----------------------------------------------------------------------------
986 -- ------------------------< chk_position_id_org_id >--------------------------
987 -- ----------------------------------------------------------------------------
988 --
989 procedure chk_position_id_org_id
990 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
991 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
992 ,p_position_id in per_all_vacancies.position_id%TYPE
993 ,p_organization_id in per_all_vacancies.organization_id%TYPE
994 ,p_effective_date in date
995 )
996 as
997 l_proc varchar2(72) := g_package||'chk_position_id_org_id';
998 l_exists varchar2(1);
999 l_api_updating boolean;
1000 --
1001 --
1002
1003 cursor csr_valid_pos_org_comb is
1004 select null
1005 from hr_all_positions_f hp
1006 where hp.position_id = p_position_id
1007 and p_effective_date
1008 between hp.effective_start_date
1009 and hp.effective_end_date
1010 and hp.organization_id = p_organization_id;
1011 --
1012 begin
1013 --
1014 hr_utility.set_location('Entering:'|| l_proc, 1);
1015 if hr_multi_message.no_exclusive_error
1016 (p_check_column1 => 'PER_ALL_VACANCIES.POSITION_ID'
1017 ,p_check_column2 => 'PER_ALL_VACANCIES.ORGANIZATION_ID'
1018 ,p_check_column3 => 'PER_ALL_VACANCIES.DATE_FROM'
1019 ) then
1020 --
1021 -- Only proceed with validation if :
1022 -- a) The current g_old_rec is current and
1023 -- b) The value for position_id or grade_id has changed
1024 --
1025 l_api_updating := per_vac_shd.api_updating
1026 (p_vacancy_id => p_vacancy_id
1027 ,p_object_version_number => p_object_version_number);
1028 --
1029 if (l_api_updating and
1030 ((nvl(per_vac_shd.g_old_rec.position_id, hr_api.g_number) <>
1031 nvl(p_position_id, hr_api.g_number))
1032 or
1033 (nvl(per_vac_shd.g_old_rec.organization_id, hr_api.g_number) <>
1034 nvl(p_organization_id, hr_api.g_number))
1035 ))
1036 or
1037 (NOT l_api_updating) then
1038 --
1039 hr_utility.set_location(l_proc, 2);
1040 --
1041 -- Check if the position is null
1042 --
1043 If p_position_id is not null then
1044 --
1045 -- Check if assignment position_id and organization_id combination
1046 -- matches the combination in HR_POSITIONS.
1047 --
1048 hr_utility.set_location(l_proc, 3);
1049 open csr_valid_pos_org_comb;
1050 fetch csr_valid_pos_org_comb into l_exists;
1051 if csr_valid_pos_org_comb%notfound then
1052 close csr_valid_pos_org_comb;
1053 fnd_message.set_name('PAY', 'HR_51055_ASG_INV_POS_ORG_COMB');
1054 fnd_message.raise_error;
1055 end if;
1056 close csr_valid_pos_org_comb;
1057 --
1058 end if;
1059 end if;
1060 --
1061 end if;
1062 hr_utility.set_location('Leaving'||l_proc, 4);
1063 exception
1064 when app_exception.application_exception then
1065 if hr_multi_message.exception_add
1066 (p_associated_column1 => 'PER_ALL_VACANCIES.POSITION_ID'
1067 ,p_associated_column2 => 'PER_ALL_VACANCIES.ORGANIZATION_ID'
1068 ) then
1069 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1070 raise;
1071 end if;
1072 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1073 end chk_position_id_org_id;
1074 --
1075 ------------------------------------------------------------------------------
1076 -------------------------< chk_position_id_job_id >---------------------------
1077 ------------------------------------------------------------------------------
1078 --
1079 procedure chk_position_id_job_id
1080 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
1081 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
1082 ,p_position_id in per_all_vacancies.position_id%TYPE
1083 ,p_job_id in per_all_vacancies.job_id%TYPE
1084 ,p_effective_date in date
1085 )
1086 as
1087 l_proc varchar2(72) := g_package||'chk_position_id_job_id';
1088 l_exists varchar2(1);
1089 l_api_updating boolean;
1090 --
1091 --
1092 cursor csr_valid_pos_job_comb is
1093 select null
1094 from hr_all_positions_f hp
1095 where hp.position_id = p_position_id
1096 and p_effective_date
1097 between hp.effective_start_date
1098 and hp.effective_end_date
1099 and hp.job_id = p_job_id;
1100 --
1101 begin
1102 hr_utility.set_location('Entering:'|| l_proc, 10);
1103 if hr_multi_message.no_exclusive_error
1104 (p_check_column1 => 'PER_ALL_VACANCIES.POSITION_ID'
1105 ,p_check_column2 => 'PER_ALL_VACANCIES.JOB_ID'
1106 ,p_check_column3 => 'PER_ALL_VACANCIES.DATE_FROM'
1107 ) then
1108 --
1109 -- Only proceed with validation if :
1110 -- a) The current g_old_rec is current and
1111 -- b) The value for position or job has changed
1112 --
1113 l_api_updating := per_vac_shd.api_updating
1114 (p_vacancy_id => p_vacancy_id
1115 ,p_object_version_number => p_object_version_number);
1116 hr_utility.set_location(l_proc, 30);
1117 --
1118 if (l_api_updating and
1119 ((nvl(per_vac_shd.g_old_rec.position_id, hr_api.g_number) <>
1120 nvl(p_position_id, hr_api.g_number))
1121 or
1122 (nvl(per_vac_shd.g_old_rec.job_id, hr_api.g_number) <>
1123 nvl(p_job_id, hr_api.g_number))
1124 ))
1125 or
1126 (NOT l_api_updating)
1127 then
1128 hr_utility.set_location(l_proc, 40);
1129 --
1130 -- Check if the assignment job and position are not null
1131 --
1132 if p_position_id is not null and p_job_id is not null then
1133 --
1134 -- Check if assignment position and job combination matches
1135 -- the combination in HR_POSITIONS
1136 --
1137 open csr_valid_pos_job_comb;
1138 fetch csr_valid_pos_job_comb into l_exists;
1139 if csr_valid_pos_job_comb%notfound then
1140 close csr_valid_pos_job_comb;
1141 fnd_message.set_name('PAY', 'HR_51056_ASG_INV_POS_JOB_COMB');
1142 fnd_message.raise_error;
1143 end if;
1144 close csr_valid_pos_job_comb;
1145 --
1146 elsif p_job_id is null and p_position_id is not null then
1147 --
1148 -- Position is not null but job is null
1149 --
1150 fnd_message.set_name('PAY', 'HR_51057_ASG_JOB_NULL_VALUE');
1151 fnd_message.raise_error;
1152 --
1153 end if;
1154 --
1155 end if;
1156 end if;
1157 --
1158 hr_utility.set_location('Leaving'||l_proc, 3);
1159 exception
1160 when app_exception.application_exception then
1161 if hr_multi_message.exception_add
1162 (p_associated_column1 => 'PER_ALL_VACANCIES.POSITION_ID'
1163 ,p_associated_column2 => 'PER_ALL_VACANCIES.JOB_ID'
1164 ) then
1165 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1166 raise;
1167 end if;
1168 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1169 end chk_position_id_job_id;
1170 --
1171 -- ----------------------------------------------------------------------------
1172 -- |---------------------------<chk_job_id>-----------------------------------|
1173 -- ----------------------------------------------------------------------------
1174 -- {Start Of Comments}
1175 --
1176 -- Description:
1177 -- This procedure ensures a valid Job Id is entered
1178 -- Pre Conditions:
1179 -- g_old_rec has been populated with details of the values currently in
1180 -- the database.
1181 --
1182 -- In Arguments:
1183 -- p_vacancy_id
1184 -- p_object_version_number
1185 -- p_job_id
1186 -- p_effective_date
1187 -- p_business_group_id
1188 -- Post Success:
1189 -- Processing continues if Job id is valid
1190
1191 --
1192 -- Post Failure:
1193 -- An application error is raised if Job id is not valid
1194 --
1195 -- {End Of Comments}
1196 -- ----------------------------------------------------------------------------
1197 Procedure chk_job_id
1198 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
1199 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
1200 ,p_job_id in per_all_vacancies.job_id%TYPE
1201 ,p_effective_date in date
1202 ,p_business_group_id in per_all_vacancies.business_group_id%TYPE
1203 )
1204 IS
1205 --
1206 l_proc varchar2(72) := g_package || 'chk_job_id';
1207 l_api_updating boolean;
1208 l_date_from per_all_vacancies.date_from%TYPE;
1209 l_date_to per_all_vacancies.date_to%TYPE;
1210 cursor csr_job is
1211 select date_from,date_to
1212 from per_jobs
1213 where job_id = p_job_id
1214 and business_group_id = p_business_group_id;
1215 --
1216 Begin
1217 --
1218 hr_utility.set_location('Entering:'||l_proc,10);
1219 --
1220 l_api_updating := per_vac_shd.api_updating
1221 (p_vacancy_id => p_vacancy_id
1222 ,p_object_version_number => p_object_version_number);
1223 --
1224 hr_utility.set_location(l_proc,20);
1225 if ((l_api_updating
1226 and
1227 nvl(per_vac_shd.g_old_rec.job_id,hr_api.g_number) <>
1228 nvl(p_job_id, hr_api.g_number))
1229 or
1230 (NOT l_api_updating)) then
1231 --
1232 if (p_job_id is not null) then
1233 open csr_job;
1234 fetch csr_job into l_date_from,l_date_to;
1235 hr_utility.set_location(l_proc,30);
1236 if (csr_job%notfound) then
1237 close csr_job;
1238 fnd_message.set_name('PER','PER_289451_VAC_INV_JOB_ID');
1239 hr_multi_message.add
1240 (p_associated_column1 => 'PER_ALL_VACANCIES.JOB_ID'
1241 );
1242 else
1243 close csr_job;
1244 hr_utility.set_location(l_proc,40);
1245 if (p_effective_date not between l_date_from and nvl(l_date_to,hr_api.g_eot))
1246 then
1247 fnd_message.set_name('PER','PER_289452_VAC_JOB_ID_INV_DATE');
1248 hr_multi_message.add
1249 (p_associated_column1 => 'PER_ALL_VACANCIES.JOB_ID'
1250 ,p_associated_column2 => 'PER_ALL_VACANCIES.DATE_FROM'
1251 );
1252 end if;
1253 end if;
1254 end if;
1255 end if;
1256 --
1257 hr_utility.set_location(' Leaving:'||l_proc,50);
1258 End chk_job_id;
1259 --
1260 -- ---------------------------------------------------------------------------
1261 -- |----------------------< chk_job_id_grade_id >----------------------------|
1262 -- ---------------------------------------------------------------------------
1263 --
1264 procedure chk_job_id_grade_id
1265 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
1266 ,p_job_id in per_all_vacancies.job_id%TYPE
1267 ,p_grade_id in per_all_vacancies.grade_id%TYPE
1268 ,p_effective_date in date
1269 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1270 ,p_inv_job_grade_warning out nocopy boolean
1271 )
1272 is
1273 --
1274 l_proc varchar2(72) := g_package||'chk_job_id_grade_id';
1275 l_api_updating boolean;
1276 l_exists varchar2(1);
1277 l_inv_job_grade_warning boolean := false;
1278 --
1279 cursor csr_val_job_grade is
1280 select null
1281 from per_valid_grades
1282 where job_id = p_job_id
1283 and grade_id = p_grade_id
1284 and p_effective_date
1285 between date_from
1286 and nvl(date_to, hr_api.g_eot);
1287 --
1288 begin
1289 hr_utility.set_location('Entering:'|| l_proc, 10);
1290 if hr_multi_message.no_exclusive_error
1291 (p_check_column1 => 'PER_ALL_VACANCIES.JOB_ID'
1292 ,p_check_column2 => 'PER_ALL_VACANCIES.GRADE_ID'
1293 ) then
1294 --
1295 -- Check if the vacancy is being updated.
1296 --
1297 l_api_updating := per_vac_shd.api_updating
1298 (p_vacancy_id => p_vacancy_id
1299 ,p_object_version_number => p_object_version_number
1300 );
1301 hr_utility.set_location(l_proc, 30);
1302 --
1303 -- Only proceed with validation if :
1304 -- a) The current g_old_rec is current and
1305 -- b) The value for job or grade has changed.
1306 --
1307 if (l_api_updating
1308 and
1309 ((nvl(per_vac_shd.g_old_rec.job_id, hr_api.g_number)
1310 <> nvl(p_job_id, hr_api.g_number))
1311 or
1312 (nvl(per_vac_shd.g_old_rec.grade_id, hr_api.g_number)
1313 <> nvl(p_grade_id, hr_api.g_number))))
1314 or
1315 NOT l_api_updating then
1316 hr_utility.set_location(l_proc, 40);
1317 --
1318 -- Check that both job and grade are set.
1319 --
1320 if p_job_id is not null and p_grade_id is not null then
1321 --
1322 -- Check if the job and grade exists date effectively in
1323 -- PER_VALID_GRADES.
1324 --
1325 open csr_val_job_grade;
1326 fetch csr_val_job_grade into l_exists;
1327 if csr_val_job_grade%notfound then
1328 p_inv_job_grade_warning := true;
1329 end if;
1330 close csr_val_job_grade;
1331 hr_utility.set_location(l_proc, 50);
1332 --
1333 end if;
1334 hr_utility.set_location(l_proc, 60);
1335 --
1336 end if;
1337 end if;
1338 --
1339 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1340 exception
1341 when app_exception.application_exception then
1342 if hr_multi_message.exception_add
1343 (p_associated_column1 => 'PER_ALL_VACANCIES.JOB_ID'
1344 ,p_associated_column2 => 'PER_ALL_VACANCIES.GRADE_ID'
1345 ) then
1346 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1347 raise;
1348 end if;
1349 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1350 end chk_job_id_grade_id;
1351 --
1352 -- ----------------------------------------------------------------------------
1353 -- |---------------------------------<chk_grade_id>---------------------------|
1354 -- ----------------------------------------------------------------------------
1355 -- {Start Of Comments}
1356 --
1357 -- Description:
1358 -- This procedure ensures a valid grade Id is entered
1359 -- Pre Conditions:
1360 -- g_old_rec has been populated with details of the values currently in
1361 -- the database.
1362 --
1363 -- In Arguments:
1364 -- p_vacancy_id
1365 -- p_object_version_number
1366 -- p_business_group_id
1367 -- p_grade_id
1368 -- p_effective_date
1369 -- Post Success:
1370 -- Processing continues if Grade id is valid
1371 --
1372 -- Post Failure:
1373 -- An application error is raised if Grade id is not valid
1374 --
1375 -- {End Of Comments}
1376 -- ----------------------------------------------------------------------------
1377 Procedure chk_grade_id
1378 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
1379 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
1380 ,p_grade_id in per_all_vacancies.grade_id%TYPE
1381 ,p_effective_date in date
1382 ,p_business_group_id in per_all_vacancies.business_group_id%TYPE
1383 )
1384 IS
1385 --
1386 l_proc varchar2(72) := g_package || 'chk_grade_id';
1387 l_api_updating boolean;
1388 l_date_from per_all_vacancies.date_from%TYPE;
1389 l_date_to per_all_vacancies.date_to%TYPE;
1390 cursor csr_grade is
1391 select date_from,date_to
1392 from per_grades
1393 where grade_id = p_grade_id
1394 and business_group_id = p_business_group_id;
1395 --
1396 Begin
1397 hr_utility.set_location('Entering:'||l_proc,10);
1398 --
1399 l_api_updating := per_vac_shd.api_updating
1400 (p_vacancy_id => p_vacancy_id
1401 ,p_object_version_number => p_object_version_number);
1402 --
1403 hr_utility.set_location(l_proc,20);
1404 if ((l_api_updating
1405 and
1406 nvl(per_vac_shd.g_old_rec.grade_id,hr_api.g_number) <>
1407 nvl(p_grade_id, hr_api.g_number))
1408 or
1409 (NOT l_api_updating)) then
1410 -- --
1411 hr_utility.set_location(l_proc,30);
1412 if (p_grade_id is not null)
1413 then
1414 open csr_grade;
1415 fetch csr_grade into l_date_from,l_date_to;
1416 hr_utility.set_location(l_proc,40);
1417 if (csr_grade%notfound)
1418 then
1419 close csr_grade;
1420 fnd_message.set_name('PER','PER_289453_VAC_INV_GRD_ID');
1421 hr_multi_message.add
1422 (p_associated_column1 => 'PER_ALL_VACANCIES.GRADE_ID'
1423 );
1424 else
1425 close csr_grade;
1426 hr_utility.set_location(l_proc,50);
1427 if (p_effective_date not between l_date_from and nvl(l_date_to,hr_api.g_eot))
1428 then
1429 fnd_message.set_name('PER','PER_289454_VAC_GRD_ID_INV_DATE');
1430 hr_multi_message.add
1431 (p_associated_column1 => 'PER_ALL_VACANCIES.GRADE_ID'
1432 ,p_associated_column2 => 'PER_ALL_VACANCIES.DATE_FROM'
1433 );
1434 end if;
1435 end if;
1436 end if;
1437 end if;
1438 --
1439 hr_utility.set_location(' Leaving:'||l_proc,60);
1440 End chk_grade_id;
1441 --
1442 -- ----------------------------------------------------------------------------
1443 -- |--------------------------<chk_organization_id>---------------------------|
1444 -- ----------------------------------------------------------------------------
1445 -- {Start Of Comments}
1446 --
1447 -- Description:
1448 -- This procedure ensures a valid Organization Id is entered
1449 -- Pre Conditions:
1450 -- g_old_rec has been populated with details of the values currently in
1451 -- the database.
1452 --
1453 -- In Arguments:
1454 -- p_vacancy_id
1455 -- p_object_version_number
1456 -- p_organization_id
1457 -- p_effective_date
1458 -- p_business_group_id
1459 -- Post Success:
1460 -- Processing continues if Organization id is valid
1461 --
1462 -- Post Failure:
1463 -- An application error is raised if Organization id is not valid
1464 --
1465 -- {End Of Comments}
1466 -- ----------------------------------------------------------------------------
1467 Procedure chk_organization_id
1468 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
1469 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
1470 ,p_organization_id in per_all_vacancies.organization_id%TYPE
1471 ,p_effective_date in date
1472 ,p_business_group_id in per_all_vacancies.business_group_id%TYPE
1473 )
1474 IS
1475 --
1476 l_proc varchar2(72) := g_package || 'chk_organization_id';
1477 l_api_updating boolean;
1478 l_date_from per_all_vacancies.date_from%TYPE;
1479 l_date_to per_all_vacancies.date_to%TYPE;
1480 cursor csr_organization is
1481 select date_from,date_to
1482 from hr_all_organization_units
1483 where organization_id = p_organization_id
1484 and business_group_id = p_business_group_id;
1485 --
1486 Begin
1487 hr_utility.set_location('Entering:'||l_proc,10);
1488 --
1489 l_api_updating := per_vac_shd.api_updating
1490 (p_vacancy_id => p_vacancy_id
1491 ,p_object_version_number => p_object_version_number);
1492 --
1493 hr_utility.set_location(l_proc,20);
1494 if ((l_api_updating
1495 and
1496 nvl(per_vac_shd.g_old_rec.organization_id,hr_api.g_number) <>
1497 nvl(p_organization_id, hr_api.g_number))
1498 or
1499 (NOT l_api_updating)) then
1500 --
1501 if (p_organization_id is not null)
1502 then
1503 open csr_organization;
1504 fetch csr_organization into l_date_from,l_date_to;
1505 hr_utility.set_location(l_proc,30);
1506 if (csr_organization%notfound)
1507 then
1508 close csr_organization;
1509 fnd_message.set_name('PER','PER_289455_VAC_INV_ORG_ID');
1510 hr_multi_message.add
1511 (p_associated_column1 => 'PER_ALL_VACANCIES.ORGANIZATION_ID'
1512 );
1513 else
1514 close csr_organization;
1515 hr_utility.set_location(l_proc,40);
1516 if (p_effective_date not between l_date_from and nvl(l_date_to,hr_api.g_eot))
1517 then
1518 fnd_message.set_name('PER','PER_289456_VAC_ORG_ID_INV_DATE');
1519 hr_multi_message.add
1520 (p_associated_column1 => 'PER_ALL_VACANCIES.ORGANIZATION_ID'
1521 ,p_associated_column2 => 'PER_ALL_VACANCIES.DATE_FROM'
1522 );
1523 end if;
1524 end if;
1525 end if;
1526 end if;
1527 hr_utility.set_location(' Leaving:'||l_proc,50);
1528 End chk_organization_id;
1529 --
1530 -- ----------------------------------------------------------------------------
1531 -- |--------------------------<chk_people_group_id>---------------------------|
1532 -- ----------------------------------------------------------------------------
1533 -- {Start Of Comments}
1534 --
1535 -- Description:
1536 -- This procedure ensures a valid People Group Id is entered
1537 -- Pre Conditions:
1538 -- g_old_rec has been populated with details of the values currently in
1539 -- the database.
1540 --
1541 -- In Arguments:
1542 -- p_people_group_id
1543 -- p_effective_date
1544 -- p_vacancy_id
1545 -- p_object_version_number
1546 -- Post Success:
1547 -- Processing continues if People group id is valid
1548 --
1549 -- Post Failure:
1550 -- An application error is raised if People Group Id is not valid
1551 --
1552 -- {End Of Comments}
1553 -- ----------------------------------------------------------------------------
1554 Procedure chk_people_group_id
1555 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
1556 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
1557 ,p_people_group_id in per_all_vacancies.people_group_id%TYPE
1558 ,p_effective_date in date
1559 )
1560 IS
1561 --
1562 l_proc varchar2(72) := g_package || 'chk_people_group_id';
1563 l_date_from date;
1564 l_date_to date;
1565 l_api_updating boolean;
1566 cursor csr_peoplegrp is
1567 select start_date_active,end_date_active
1568 from pay_people_groups
1569 where people_group_id = p_people_group_id;
1570 --
1571 Begin
1572 hr_utility.set_location('Entering:'||l_proc,10);
1573 --
1574 l_api_updating := per_vac_shd.api_updating
1575 (p_vacancy_id => p_vacancy_id
1576 ,p_object_version_number => p_object_version_number);
1577 --
1578 hr_utility.set_location(l_proc,20);
1579 if ((l_api_updating
1580 and
1581 nvl(per_vac_shd.g_old_rec.people_group_id,hr_api.g_number) <>
1582 nvl(p_people_group_id, hr_api.g_number))
1583 or
1584 (NOT l_api_updating)) then
1585 --
1586 if(p_people_group_id is not null)
1587 then
1588 open csr_peoplegrp;
1589 fetch csr_peoplegrp into l_date_from,l_date_to;
1590 --
1591 hr_utility.set_location(l_proc,20);
1592 if (csr_peoplegrp%notfound)
1593 then
1594 close csr_peoplegrp;
1595 fnd_message.set_name('PER','PER_289457_VAC_INV_GRP_ID');
1596 hr_multi_message.add
1597 (p_associated_column1 => 'PER_ALL_VACANCIES.PEOPLE_GROUP_ID'
1598 );
1599 else
1600 close csr_peoplegrp;
1601 hr_utility.set_location(l_proc,30);
1602 if (p_effective_date not between l_date_from and nvl(l_date_to,hr_api.g_eot))
1603 then
1604 fnd_message.set_name('PER','PER_289458_VAC_GRP_ID_INV_DATE');
1605 hr_multi_message.add
1606 (p_associated_column1 => 'PER_ALL_VACANCIES.PEOPLE_GROUP_ID'
1607 ,p_associated_column2 => 'PER_ALL_VACANCIES.DATE_FROM'
1608 );
1609 end if;
1610 end if;
1611 end if;
1612 end if;
1613 hr_utility.set_location(' Leaving:'||l_proc,40);
1614 End chk_people_group_id;
1615 --
1616 -- ----------------------------------------------------------------------------
1617 -- |--------------------------<chk_location_id>-------------------------------|
1618 -- ----------------------------------------------------------------------------
1619 -- {Start Of Comments}
1620 --
1621 -- Description:
1622 -- This procedure ensures a valid Location Id is entered
1623 -- Pre Conditions:
1624 -- g_old_rec has been populated with details of the values currently in
1625 -- the database.
1626 --
1627 -- In Arguments:
1628 -- p_vacancy_id
1629 -- p_object_version_number
1630 -- p_location_id
1631 -- Post Success:
1632 -- Processing continues if Location id is valid
1633 --
1634 -- Post Failure:
1635 -- An application error is raised if Location Id is not valid
1636 --
1637 -- {End Of Comments}
1638 -- ----------------------------------------------------------------------------
1639 Procedure chk_location_id
1640 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
1641 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
1642 ,p_location_id in per_all_vacancies.location_id%TYPE
1643 )
1644 IS
1645 --
1646 l_proc varchar2(72) := g_package || 'chk_location_id';
1647 l_location_id varchar2(1);
1648 l_api_updating boolean;
1649 cursor csr_location is
1650 select null
1651 from hr_locations_all
1652 where location_id = p_location_id;
1653 --
1654 Begin
1655 hr_utility.set_location('Entering:'||l_proc,10);
1656 --
1657 l_api_updating := per_vac_shd.api_updating
1658 (p_vacancy_id => p_vacancy_id
1659 ,p_object_version_number => p_object_version_number);
1660 --
1661 hr_utility.set_location(l_proc,20);
1662 if ((l_api_updating
1663 and
1664 nvl(per_vac_shd.g_old_rec.location_id,hr_api.g_number) <>
1665 nvl(p_location_id, hr_api.g_number))
1666 or
1667 (NOT l_api_updating)) then
1668 --
1669 if (p_location_id is not null)
1670 then
1671 open csr_location;
1672 fetch csr_location into l_location_id;
1673 hr_utility.set_location(l_proc,30);
1674 if (csr_location%notfound)
1675 then
1676 close csr_location;
1677 fnd_message.set_name('PER','PER_289459_VAC_INV_LOCATION_ID');
1678 fnd_message.raise_error;
1679 end if;
1680 close csr_location;
1681 end if;
1682 end if;
1683 hr_utility.set_location(' Leaving:'||l_proc,40);
1684 exception
1685 when app_exception.application_exception then
1686 if hr_multi_message.exception_add
1687 (p_associated_column1 => 'PER_ALL_VACANCIES.LOCATION_ID'
1688 ) then
1689 hr_utility.set_location(' Leaving:'|| l_proc, 50);
1690 raise;
1691 end if;
1692 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1693 End chk_location_id;
1694 --
1695 -- ----------------------------------------------------------------------------
1696 -- |---------------------------<chk_recruiter_id>-----------------------------|
1697 -- ----------------------------------------------------------------------------
1698 -- {Start Of Comments}
1699 --
1700 -- Description:
1701 -- This procedure ensures a valid Recruiter Id is entered
1702 -- Pre Conditions:
1703 -- g_old_rec has been populated with details of the values currently in
1704 -- the database.
1705 --
1706 -- In Arguments:
1707 -- p_vacancy_id
1708 -- p_object_version_number
1709 -- p_recruiter_id
1710 -- p_effective_date
1711 -- p_business_group_id
1712 -- Post Success:
1713 -- Processing continues if Recruiter id is valid
1714 --
1715 -- Post Failure:
1716 -- An application error is raised if Recruiter Id is not valid
1717 --
1718 -- {End Of Comments}
1719 -- ----------------------------------------------------------------------------
1720 Procedure chk_recruiter_id
1721 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
1722 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
1723 ,p_recruiter_id in per_all_vacancies.recruiter_id%TYPE
1724 ,p_effective_date in date
1725 ,p_business_group_id in per_all_vacancies.business_group_id%TYPE
1726 )
1727 IS
1728 --
1729 l_proc varchar2(72) := g_package || 'chk_recruiter_id';
1730 l_recruiter_id varchar2(1);
1731 l_api_updating boolean;
1732 --
1733 cursor csr_recruiter1 is
1734 select null
1735 from per_all_people_f
1736 where person_id = p_recruiter_id
1737 and (p_business_group_id = business_group_id
1738 or (p_business_group_id <> business_group_id
1739 and nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y'));
1740
1741 --
1742 cursor csr_recruiter2 is
1743 select null
1744 from per_all_people_f
1745 where person_id = p_recruiter_id
1746 and p_effective_date between effective_start_date and effective_end_date;
1747 --
1748 Begin
1749 hr_utility.set_location('Entering:'||l_proc,10);
1750 --
1751 l_api_updating := per_vac_shd.api_updating
1752 (p_vacancy_id => p_vacancy_id
1753 ,p_object_version_number => p_object_version_number);
1754 --
1755 hr_utility.set_location(l_proc,20);
1756 if ((l_api_updating
1757 and
1758 nvl(per_vac_shd.g_old_rec.recruiter_id,hr_api.g_number) <>
1759 nvl(p_recruiter_id, hr_api.g_number))
1760 or
1761 (NOT l_api_updating)) then
1762 --
1763 if (p_recruiter_id is not null)
1764 then
1765 open csr_recruiter1;
1766 fetch csr_recruiter1 into l_recruiter_id;
1767 hr_utility.set_location(l_proc,30);
1768 if (csr_recruiter1%notfound)
1769 then
1770 close csr_recruiter1;
1771 fnd_message.set_name('PER','PER_289460_VAC_INV_REC_ID');
1772 hr_multi_message.add
1773 (p_associated_column1 => 'PER_ALL_VACANCIES.RECRUITER_ID'
1774 );
1775 else
1776 close csr_recruiter1;
1777 --
1778 open csr_recruiter2;
1779 fetch csr_recruiter2 into l_recruiter_id;
1780 --
1781 hr_utility.set_location(l_proc,40);
1782 if (csr_recruiter2%notfound)
1783 then
1784 close csr_recruiter2;
1785 fnd_message.set_name('PER','PER_289461_VAC_REC_ID_INV_DATE');
1786 hr_multi_message.add
1787 (p_associated_column1 => 'PER_ALL_VACANCIES.RECRUITER_ID'
1788 );
1789 else
1790 close csr_recruiter2;
1791 end if;
1792 end if;
1793 end if;
1794 end if;
1795 --
1796 hr_utility.set_location(' Leaving:'||l_proc,50);
1797 End chk_recruiter_id;
1798 --
1799 -- ----------------------------------------------------------------------------
1800 -- |-----------------------<chk_number_of_openings>------------------------|
1801 -- ----------------------------------------------------------------------------
1802 -- {Start Of Comments}
1803 --
1804 -- Description:
1805 -- This procedure ensures positive number is entered for openings
1806 -- Pre Conditions:
1807 -- g_old_rec has been populated with details of the values currently in
1808 -- the database.
1809 --
1810 -- In Arguments:
1811 -- p_vacancy_id
1812 -- p_object_version_number
1813 -- p_num_open
1814 -- Post Success:
1815 -- Processing continues if openings is positive
1816 --
1817 -- Post Failure:
1818 -- An application error is raised if openings is not positive
1819 --
1820 -- {End Of Comments}
1821 -- ----------------------------------------------------------------------------
1822 Procedure chk_number_of_openings
1823 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
1824 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
1825 ,p_num_open in per_all_vacancies.number_of_openings%TYPE
1826 )
1827 IS
1828 --
1829 l_proc varchar2(72) := g_package || 'chk_number_of_openings';
1830 --
1831 Begin
1832 hr_utility.set_location('Entering:'||l_proc,10);
1833 --
1834 --
1835 hr_utility.set_location(l_proc,30);
1836 if (p_num_open <= 0)
1837 then
1838 fnd_message.set_name('PER','PER_289462_VAC_INV_NO_OF_OPEN');
1839 fnd_message.raise_error;
1840 end if;
1841 --
1842 hr_utility.set_location(' Leaving:'||l_proc,40);
1843 exception
1844 when app_exception.application_exception then
1845 if hr_multi_message.exception_add
1846 (p_associated_column1 => 'PER_ALL_VACANCIES.NUMBER_OF_OPENINGS'
1847 ) then
1848 hr_utility.set_location(' Leaving:'|| l_proc, 50);
1849 raise;
1850 end if;
1851 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1852 End chk_number_of_openings;
1853 --
1854 -- ----------------------------------------------------------------------------
1855 -- |-----------------------<chk_budget_measurement_value>---------------------|
1856 -- ----------------------------------------------------------------------------
1857 -- {Start Of Comments}
1858 --
1859 -- Description:
1860 -- This procedure ensures positive number is entered for budget measurement value
1861 -- Pre Conditions:
1862 -- g_old_rec has been populated with details of the values currently in
1863 -- the database.
1864 --
1865 -- In Arguments:
1866 -- p_vacancy_id
1867 -- p_object_version_number
1868 -- p_bud_mea_val
1869 -- Post Success:
1870 -- Processing continues if budget measurement value is positive
1871
1872 --
1873 -- Post Failure:
1874 -- An application error is raised if budget measurement value is not positive
1875 --
1876 -- {End Of Comments}
1877 -- ----------------------------------------------------------------------------
1878 Procedure chk_budget_measurement_value
1879 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
1880 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
1881 ,p_bud_mea_val in per_all_vacancies.budget_measurement_value%TYPE
1882 )
1883 IS
1884 --
1885 l_proc varchar2(72) := g_package || 'chk_budget_measurement_value';
1886 --
1887 Begin
1888 hr_utility.set_location('Entering:'||l_proc,10);
1889 --
1890 --
1891 hr_utility.set_location(l_proc,20);
1892 --
1893 hr_utility.set_location(l_proc,20);
1894 if (p_bud_mea_val <= 0)
1895 then
1896 fnd_message.set_name('PER','PER_289462_VAC_INV_NO_OF_OPEN');
1897 fnd_message.raise_error;
1898 end if;
1899 hr_utility.set_location(' Leaving:'||l_proc,30);
1900 exception
1901 when app_exception.application_exception then
1902 if hr_multi_message.exception_add
1903 (p_associated_column1 => 'PER_ALL_VACANCIES.BUDGET_MEASUREMENT_VALUE'
1904 ) then
1905 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1906 raise;
1907 end if;
1908 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1909 End chk_budget_measurement_value;
1910 --
1911 -- ----------------------------------------------------------------------------
1912 -- |---------------------------< chk_manager_id >-----------------------------|
1913 -- ----------------------------------------------------------------------------
1914 -- {Start Of Comments}
1915 --
1916 -- Description:
1917 -- This procedure ensures a valid Manager Id is entered
1918 -- Pre Conditions:
1919 -- g_old_rec has been populated with details of the values currently in
1920 -- the database.
1921 --
1922 -- In Arguments:
1923 -- p_vacancy_id
1924 -- p_object_version_number
1925 -- p_manager_id
1926 -- p_effective_date
1927 -- p_business_group_id
1928 -- Post Success:
1929 -- Processing continues if Manager id is valid
1930 --
1931 -- Post Failure:
1932 -- An application error is raised if Manager Id is not valid
1933 --
1934 -- {End Of Comments}
1935 -- ----------------------------------------------------------------------------
1936 Procedure chk_manager_id
1937 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
1938 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
1939 ,p_manager_id in per_all_vacancies.manager_id%TYPE
1940 ,p_effective_date in date
1941 ,p_business_group_id in per_all_vacancies.business_group_id%TYPE
1942 )
1943 IS
1944 --
1945 l_proc varchar2(72) := g_package || 'chk_manager_id';
1946 l_manager_id varchar2(1);
1947 l_api_updating boolean;
1948 --
1949 cursor csr_manager1 is
1950 select null
1951 from per_all_people_f
1952 where person_id = p_manager_id
1953 and(p_business_group_id = business_group_id
1954 or (p_business_group_id <> business_group_id
1955 and nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y'));
1956 --
1957 cursor csr_manager2 is
1958 select null
1959 from per_all_people_f
1960 where person_id = p_manager_id
1961 and p_effective_date between effective_start_date and effective_end_date;
1962 --
1963 Begin
1964 hr_utility.set_location('Entering:'||l_proc,10);
1965 --
1966 l_api_updating := per_vac_shd.api_updating
1967 (p_vacancy_id => p_vacancy_id
1968 ,p_object_version_number => p_object_version_number);
1969 --
1970 hr_utility.set_location(l_proc,20);
1971 if ((l_api_updating
1972 and
1973 nvl(per_vac_shd.g_old_rec.manager_id,hr_api.g_number) <>
1974 nvl(p_manager_id, hr_api.g_number))
1975 or
1976 (NOT l_api_updating)) then
1977 --
1978 if (p_manager_id is not null)
1979 then
1980 open csr_manager1;
1981 fetch csr_manager1 into l_manager_id;
1982 hr_utility.set_location(l_proc,20);
1983 if (csr_manager1%notfound)
1984 then
1985 close csr_manager1;
1986 fnd_message.set_name('PER','PER_289464_VAC_INV_MGR_ID');
1987 hr_multi_message.add
1988 (p_associated_column1 => 'PER_ALL_VACANCIES.MANAGER_ID'
1989 );
1990 else
1991 close csr_manager1;
1992 --
1993 open csr_manager2;
1994 fetch csr_manager2 into l_manager_id;
1995 hr_utility.set_location(l_proc,30);
1996 if (csr_manager2%notfound)
1997 then
1998 close csr_manager2;
1999 fnd_message.set_name('PER','PER_289465_VAC_MGR_ID_INV_DATE');
2000 hr_multi_message.add
2001 (p_associated_column1 => 'PER_ALL_VACANCIES.MANAGER_ID'
2002 );
2003 else
2004 close csr_manager2;
2005 end if;
2006 end if;
2007 end if;
2008 end if;
2009 --
2010 hr_utility.set_location(' Leaving:'||l_proc,40);
2011 End chk_manager_id;
2012 --
2013 -- ----------------------------------------------------------------------------
2014 -- |---------------------------< chk_manager_assignment_id >------------------|
2015 -- ----------------------------------------------------------------------------
2016 -- {Start Of Comments}
2017 --
2018 -- Description:
2019 -- This procedure ensures that manager is not an applicant while updating the vacancy
2020 -- Pre Conditions:
2021 -- g_old_rec has been populated with details of the values currently in
2022 -- the database.
2023 --
2024 -- In Arguments:
2025 -- p_vacancy_id
2026 -- p_object_version_number
2027 -- p_manager_id
2028 -- p_effective_date
2029 -- Post Success:
2030 -- Processing continues if Manager id is valid
2031 --
2032 -- Post Failure:
2033 -- An application error is raised if manager is an existing applicant for the same vacancy
2034 --
2035 -- {End Of Comments}
2036 -- ----------------------------------------------------------------------------
2037 Procedure chk_manager_assignment_id
2038 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
2039 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
2040 ,p_manager_id in per_all_vacancies.manager_id%TYPE
2041 ,p_effective_date in date
2042 )
2043 IS
2044 --
2045 l_proc varchar2(72) := g_package || 'chk_manager_assignment_id';
2046 l_exists varchar2(1);
2047 l_api_updating boolean;
2048 --
2049 cursor csr_manager is
2050 select null
2051 from per_all_assignments_f paf,per_all_people_f ppf
2052 where paf.vacancy_id = p_vacancy_id
2053 and ppf.person_id = paf.person_id
2054 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
2055 and ppf.party_id = (select ppf.party_id
2056 from per_all_people_f ppf
2057 where ppf.person_id = p_manager_id
2058 and p_effective_date between ppf.effective_start_date and ppf.effective_end_date);
2059 --
2060 Begin
2061 hr_utility.set_location('Entering:'||l_proc,10);
2062 --
2063 l_api_updating := per_vac_shd.api_updating
2064 (p_vacancy_id => p_vacancy_id
2065 ,p_object_version_number => p_object_version_number);
2066 --
2067 hr_utility.set_location(l_proc,20);
2068 if ((l_api_updating
2069 and
2070 nvl(per_vac_shd.g_old_rec.manager_id,hr_api.g_number) <>
2071 nvl(p_manager_id, hr_api.g_number))
2072 or
2073 (NOT l_api_updating)) then
2074 --
2075 if (p_manager_id is not null)
2076 then
2077 --
2078 -- Check that the manager is not existing applicant for the same vacancy.
2079 --
2080 open csr_manager;
2081 fetch csr_manager into l_exists;
2082 hr_utility.set_location(l_proc,30);
2083 if (csr_manager%found)
2084 then
2085 close csr_manager;
2086 fnd_message.set_name('PER','IRC_VAC_MGR_EQUAL_APPL');
2087 hr_multi_message.add
2088 (p_associated_column1 => 'PER_ALL_VACANCIES.MANAGER_ID'
2089 );
2090 else
2091 close csr_manager;
2092 end if;
2093 end if;
2094 end if;
2095 --
2096 hr_utility.set_location(' Leaving:'||l_proc,40);
2097 End chk_manager_assignment_id;
2098
2099 --
2100 -- ----------------------------------------------------------------------------
2101 -- |-------------------------<chk_primary_posting_id>-------------------------|
2102 -- ----------------------------------------------------------------------------
2103 -- {Start Of Comments}
2104 --
2105 -- Description:
2106 -- This procedure ensures a valid Posting Content Id is entered
2107 -- Pre Conditions:
2108 -- g_old_rec has been populated with details of the values currently in
2109 -- the database.
2110 --
2111 -- In Arguments:
2112 -- p_vacancy_id
2113 -- p_object_version_number
2114 -- p_primary_posting_id
2115 --
2116 -- Post Success:
2117 -- Processing continues if Posting Content id is valid
2118 --
2119 -- Post Failure:
2120 -- An application error is raised if Posting Content id is not valid
2121 --
2122 -- {End Of Comments}
2123 -- ----------------------------------------------------------------------------
2124 Procedure chk_primary_posting_id
2125 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
2126 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
2127 ,p_primary_posting_id in per_all_vacancies. primary_posting_id %TYPE
2128 )
2129 IS
2130 --
2131 l_proc varchar2(72) := g_package || 'chk_primary_posting_id ';
2132 l_primary_posting varchar2(1);
2133 l_api_updating boolean;
2134 --
2135 cursor csr_posting_contents is
2136 select null
2137 from irc_posting_contents
2138 where posting_content_id = p_primary_posting_id;
2139 --
2140 Begin
2141 hr_utility.set_location('Entering:'||l_proc,10);
2142 --
2143 l_api_updating := per_vac_shd.api_updating
2144 (p_vacancy_id => p_vacancy_id
2145 ,p_object_version_number => p_object_version_number);
2146 --
2147 hr_utility.set_location(l_proc,20);
2148 if ((l_api_updating
2149 and
2150 nvl(per_vac_shd.g_old_rec.primary_posting_id,hr_api.g_number) <>
2151 nvl(p_primary_posting_id, hr_api.g_number))
2152 or
2153 (NOT l_api_updating)) then
2154 --
2155 if (p_primary_posting_id is not null)
2156 then
2157 open csr_posting_contents;
2158 fetch csr_posting_contents into l_primary_posting;
2159 hr_utility.set_location(l_proc,30);
2160 if (csr_posting_contents%notfound)
2161 then
2162 close csr_posting_contents;
2163 fnd_message.set_name('PER','PER_449565_VAC_INV_POSTING_ID');
2164 hr_multi_message.add
2165 (p_associated_column1 => 'PER_ALL_VACANCIES.PRIMARY_POSTING_ID'
2166 );
2167 else
2168 close csr_posting_contents;
2169 end if;
2170 end if;
2171 end if;
2172 --
2173 hr_utility.set_location(' Leaving:'||l_proc,40);
2174 End chk_primary_posting_id;
2175 --
2176 -- ----------------------------------------------------------------------------
2177 -- |-------------------------<chk_assessment_id>------------------------------|
2178 -- ----------------------------------------------------------------------------
2179 -- {Start Of Comments}
2180 --
2181 -- Description:
2182 -- This procedure ensures a valid Assessment Id is entered
2183 -- Pre Conditions:
2184 -- g_old_rec has been populated with details of the values currently in
2185 -- the database.
2186 --
2187 -- In Arguments:
2188 -- p_vacancy_id
2189 -- p_object_version_number
2190 -- p_assessment_id
2191 --
2192 -- Post Success:
2193 -- Processing continues if Assessment id is valid
2194 --
2195 -- Post Failure:
2196 -- An application error is raised if Assessment id is not valid
2197 --
2198 -- {End Of Comments}
2199 -- ----------------------------------------------------------------------------
2200 Procedure chk_assessment_id
2201 (p_vacancy_id in per_all_vacancies.vacancy_id%TYPE
2202 ,p_object_version_number in per_all_vacancies.object_version_number%TYPE
2203 ,p_assessment_id in per_all_vacancies.assessment_id%TYPE
2204 )
2205 IS
2206 --
2207 l_proc varchar2(72) := g_package || 'chk_assessment_id ';
2208 l_assessment varchar2(1);
2209 l_api_updating boolean;
2210 --
2211 cursor csr_assessments is
2212 select null
2213 from ota_learning_objects
2214 where data_source='OIR'
2215 and test_id = p_assessment_id;
2216 --
2217 Begin
2218 hr_utility.set_location('Entering:'||l_proc,10);
2219 --
2220 l_api_updating := per_vac_shd.api_updating
2221 (p_vacancy_id => p_vacancy_id
2222 ,p_object_version_number => p_object_version_number);
2223 --
2224 hr_utility.set_location(l_proc,20);
2225 if ((l_api_updating
2226 and
2227 nvl(per_vac_shd.g_old_rec.assessment_id,hr_api.g_number) <>
2228 nvl(p_assessment_id, hr_api.g_number))
2229 or
2230 (NOT l_api_updating)) then
2231 --
2232 if (p_assessment_id is not null)
2233 then
2234 open csr_assessments;
2235 fetch csr_assessments into l_assessment;
2236 hr_utility.set_location(l_proc,30);
2237 if (csr_assessments%notfound)
2238 then
2239 close csr_assessments;
2240 fnd_message.set_name('PER','PER_449566_VAC_INV_ASSESS_ID');
2241 hr_multi_message.add
2242 (p_associated_column1 => 'PER_ALL_VACANCIES.ASSESSMENT_ID'
2243 );
2244 else
2245 close csr_assessments;
2246 end if;
2247 end if;
2248 end if;
2249 --
2250 hr_utility.set_location(' Leaving:'||l_proc,40);
2251 End chk_assessment_id;
2252 --
2253 -- ----------------------------------------------------------------------------
2254 -- |---------------------------< insert_validate >----------------------------|
2255 -- ----------------------------------------------------------------------------
2256 Procedure insert_validate
2257 (p_rec in per_vac_shd.g_rec_type
2258 ,p_effective_date in date
2259 ,p_inv_pos_grade_warning out nocopy boolean
2260 ,p_inv_job_grade_warning out nocopy boolean
2261 ) is
2262 --
2263 l_proc varchar2(72) := g_package||'insert_validate';
2264 l_inv_job_grade_warning boolean;
2265 l_inv_pos_grade_warning boolean;
2266 --
2267 Begin
2268 hr_utility.set_location('Entering:'||l_proc, 5);
2269 --
2270 -- Call all supporting business operations
2271 --
2272 hr_api.validate_bus_grp_id(p_rec.business_group_id
2273 ,p_associated_column1 => per_vac_shd.g_tab_name||'.BUSINESS_GROUP_ID');
2274 --
2275 hr_utility.set_location(l_proc, 10);
2276 --
2277 chk_vacancy_dates
2278 (p_vacancy_id => p_rec.vacancy_id
2279 ,p_object_version_number => p_rec.object_version_number
2280 ,p_date_from => p_rec.date_from
2281 ,p_date_to => p_rec.date_to
2282 );
2283 --
2284 hr_multi_message.end_validation_set();
2285 --
2286 hr_utility.set_location(l_proc, 20);
2287 --
2288 chk_name
2289 (p_name => p_rec.name
2290 ,p_business_group_id => p_rec.business_group_id
2291 );
2292 --
2293 hr_utility.set_location(l_proc, 30);
2294 --
2295 chk_status
2296 (p_vacancy_id => p_rec.vacancy_id
2297 ,p_object_version_number => p_rec.object_version_number
2298 ,p_status => p_rec.status
2299 ,p_effective_date => p_effective_date
2300 );
2301 --
2302 hr_utility.set_location(l_proc, 40);
2303 --
2304 chk_budget_measurement_type
2305 (p_vacancy_id => p_rec.vacancy_id
2306 ,p_object_version_number => p_rec.object_version_number
2307 ,p_budget_measurement_type => p_rec.budget_measurement_type
2308 ,p_effective_date => p_effective_date
2309 );
2310 --
2311 hr_utility.set_location(l_proc, 50);
2312 --
2313 chk_security_method
2314 (p_vacancy_id => p_rec.vacancy_id
2315 ,p_object_version_number => p_rec.object_version_number
2316 ,p_security_method => p_rec.security_method
2317 ,p_effective_date => p_effective_date
2318 );
2319 --
2320 hr_utility.set_location(l_proc, 60);
2321 --
2322 chk_requisition_id
2323 (p_requisition_id => p_rec.requisition_id
2324 ,p_date_from => p_rec.date_from
2325 ,p_business_group_id => p_rec.business_group_id
2326 );
2327 --
2328 hr_utility.set_location(l_proc, 70);
2329 --
2330 chk_organization_id
2331 (p_vacancy_id => p_rec.vacancy_id
2332 ,p_object_version_number => p_rec.object_version_number
2333 ,p_organization_id => p_rec.organization_id
2334 ,p_effective_date => p_effective_date
2335 ,p_business_group_id => p_rec.business_group_id
2336 );
2337 --
2338 hr_utility.set_location(l_proc, 80);
2339 --
2340 chk_job_id
2341 (p_vacancy_id => p_rec.vacancy_id
2342 ,p_object_version_number => p_rec.object_version_number
2343 ,p_job_id => p_rec.job_id
2344 ,p_effective_date => p_effective_date
2345 ,p_business_group_id => p_rec.business_group_id
2346 );
2347 --
2348 hr_utility.set_location(l_proc, 90);
2349 --
2350 chk_position_id
2351 (p_vacancy_id => p_rec.vacancy_id
2352 ,p_object_version_number => p_rec.object_version_number
2353 ,p_position_id => p_rec.position_id
2354 ,p_business_group_id => p_rec.business_group_id
2355 ,p_effective_date => p_effective_date
2356 );
2357 --
2358 hr_utility.set_location(l_proc, 100);
2359 --
2360 chk_position_id_org_id
2361 (p_vacancy_id => p_rec.vacancy_id
2362 ,p_object_version_number => p_rec.object_version_number
2363 ,p_position_id => p_rec.position_id
2364 ,p_organization_id => p_rec.organization_id
2365 ,p_effective_date => p_effective_date
2366 );
2367 --
2368 hr_utility.set_location(l_proc, 110);
2369 --
2370 chk_position_id_job_id
2371 (p_vacancy_id => p_rec.vacancy_id
2372 ,p_object_version_number => p_rec.object_version_number
2373 ,p_position_id => p_rec.position_id
2374 ,p_job_id => p_rec.job_id
2375 ,p_effective_date => p_effective_date
2376 );
2377 --
2378 hr_utility.set_location(l_proc, 120);
2379 --
2380 chk_grade_id
2381 (p_vacancy_id => p_rec.vacancy_id
2382 ,p_object_version_number => p_rec.object_version_number
2383 ,p_grade_id => p_rec.grade_id
2384 ,p_effective_date => p_effective_date
2385 ,p_business_group_id => p_rec.business_group_id
2386 );
2387 --
2388 hr_utility.set_location(l_proc, 130);
2389 --
2390 chk_position_id_grade_id
2391 (p_vacancy_id => p_rec.vacancy_id
2392 ,p_object_version_number => p_rec.object_version_number
2393 ,p_position_id => p_rec.position_id
2394 ,p_grade_id => p_rec.grade_id
2395 ,p_effective_date => p_effective_date
2396 ,p_inv_pos_grade_warning => l_inv_pos_grade_warning
2397 );
2398 --
2399 hr_utility.set_location(l_proc, 140);
2400 --
2401 per_vac_bus.chk_job_id_grade_id
2402 (p_vacancy_id => p_rec.vacancy_id
2403 ,p_job_id => p_rec.job_id
2404 ,p_grade_id => p_rec.grade_id
2405 ,p_effective_date => p_effective_date
2406 ,p_object_version_number => p_rec.object_version_number
2407 ,p_inv_job_grade_warning => l_inv_job_grade_warning
2408 );
2409 --
2410 hr_utility.set_location(l_proc, 150);
2411 --
2412 chk_people_group_id
2413 (p_vacancy_id => p_rec.vacancy_id
2414 ,p_object_version_number => p_rec.object_version_number
2415 ,p_people_group_id => p_rec.people_group_id
2416 ,p_effective_date => p_effective_date
2417 );
2418 --
2419 hr_utility.set_location(l_proc, 160);
2420 --
2421 chk_location_id
2422 (p_vacancy_id => p_rec.vacancy_id
2423 ,p_object_version_number => p_rec.object_version_number
2424 ,p_location_id => p_rec.location_id
2425 );
2426 --
2427 hr_utility.set_location(l_proc, 170);
2428 --
2429 chk_recruiter_id
2430 (p_vacancy_id => p_rec.vacancy_id
2431 ,p_object_version_number => p_rec.object_version_number
2432 ,p_recruiter_id => p_rec.recruiter_id
2433 ,p_business_group_id => p_rec.business_group_id
2434 ,p_effective_date => p_effective_date
2435 );
2436 --
2437 hr_utility.set_location(l_proc, 180);
2438 --
2439 chk_number_of_openings
2440 (p_vacancy_id => p_rec.vacancy_id
2441 ,p_object_version_number => p_rec.object_version_number
2442 ,p_num_open => p_rec.number_of_openings
2443 );
2444 --
2445 hr_utility.set_location(l_proc, 190);
2446 --
2447 chk_budget_measurement_value
2448 (p_vacancy_id => p_rec.vacancy_id
2449 ,p_object_version_number => p_rec.object_version_number
2450 ,p_bud_mea_val => p_rec.budget_measurement_value
2451 );
2452 --
2453 hr_utility.set_location(l_proc, 200);
2454 --
2455 chk_manager_id
2456 (p_vacancy_id => p_rec.vacancy_id
2457 ,p_object_version_number => p_rec.object_version_number
2458 ,p_manager_id => p_rec.manager_id
2459 ,p_effective_date => p_effective_date
2460 ,p_business_group_id => p_rec.business_group_id
2461 );
2462 --
2463 hr_utility.set_location(l_proc, 210);
2464 --
2465 chk_primary_posting_id
2466 (p_vacancy_id => p_rec.vacancy_id
2467 ,p_object_version_number => p_rec.object_version_number
2468 ,p_primary_posting_id => p_rec.primary_posting_id
2469 );
2470 --
2471 hr_utility.set_location(l_proc, 220);
2472 --
2473 chk_assessment_id
2474 (p_vacancy_id => p_rec.vacancy_id
2475 ,p_object_version_number => p_rec.object_version_number
2476 ,p_assessment_id => p_rec.assessment_id
2477 );
2478 --
2479 -- ER#8530112 starts here
2480 per_vacancies_pkg.
2481 CHK_POS_BUDGET_VAL( p_rec.Position_Id,p_effective_date,p_rec.Organization_Id,p_rec.Number_Of_Openings,p_rec.Vacancy_Id);
2482
2483 -- ER#8530112 ends here
2484 hr_utility.set_location(l_proc, 230);
2485 --
2486 per_vac_bus.chk_df(p_rec);
2487 --
2488 hr_utility.set_location(l_proc, 240);
2489 --
2490 p_inv_job_grade_warning:=l_inv_job_grade_warning;
2491 p_inv_pos_grade_warning:=l_inv_pos_grade_warning;
2492 --
2493 hr_utility.set_location(' Leaving:'||l_proc, 250);
2494 End insert_validate;
2495 --
2496 -- ----------------------------------------------------------------------------
2497 -- |---------------------------< update_validate >----------------------------|
2498 -- ----------------------------------------------------------------------------
2499 Procedure update_validate
2500 (p_rec in per_vac_shd.g_rec_type
2501 ,p_effective_date in date
2502 ,p_inv_pos_grade_warning out nocopy boolean
2503 ,p_inv_job_grade_warning out nocopy boolean
2504 ) is
2505 --
2506 l_proc varchar2(72) := g_package||'update_validate';
2507 l_inv_job_grade_warning boolean;
2508 l_inv_pos_grade_warning boolean;
2509 --
2510 Begin
2511 hr_utility.set_location('Entering:'||l_proc, 10);
2512 --
2513 -- Call all supporting business operations
2514 --
2515 --
2516 hr_utility.set_location(l_proc, 190);
2517 --
2518 chk_non_updateable_args
2519 (p_rec => p_rec
2520 );
2521 --
2522 --
2523 hr_api.validate_bus_grp_id(p_rec.business_group_id
2524 ,p_associated_column1 => per_vac_shd.g_tab_name||'.BUSINESS_GROUP_ID');
2525 --
2526 hr_utility.set_location(l_proc, 20);
2527 --
2528 chk_vacancy_dates
2529 (p_vacancy_id => p_rec.vacancy_id
2530 ,p_object_version_number => p_rec.object_version_number
2531 ,p_date_from => p_rec.date_from
2532 ,p_date_to => p_rec.date_to
2533 );
2534 --
2535 hr_utility.set_location(l_proc, 30);
2536 --
2537 chk_status
2538 (p_vacancy_id => p_rec.vacancy_id
2539 ,p_object_version_number => p_rec.object_version_number
2540 ,p_status => p_rec.status
2541 ,p_effective_date => p_effective_date
2542 );
2543 --
2544 hr_utility.set_location(l_proc, 40);
2545 --
2546 chk_budget_measurement_type
2547 (p_vacancy_id => p_rec.vacancy_id
2548 ,p_object_version_number => p_rec.object_version_number
2549 ,p_budget_measurement_type => p_rec.budget_measurement_type
2550 ,p_effective_date => p_effective_date
2551 );
2552 --
2553 hr_utility.set_location(l_proc, 50);
2554 --
2555 chk_organization_id
2556 (p_vacancy_id => p_rec.vacancy_id
2557 ,p_object_version_number => p_rec.object_version_number
2558 ,p_organization_id => p_rec.organization_id
2559 ,p_effective_date => p_effective_date
2560 ,p_business_group_id => per_vac_shd.g_old_rec.business_group_id
2561 );
2562 --
2563 hr_utility.set_location(l_proc, 60);
2564 --
2565 chk_job_id
2566 (p_vacancy_id => p_rec.vacancy_id
2567 ,p_object_version_number => p_rec.object_version_number
2568 ,p_job_id => p_rec.job_id
2569 ,p_effective_date => p_effective_date
2570 ,p_business_group_id => per_vac_shd.g_old_rec.business_group_id
2571 );
2572 --
2573 hr_utility.set_location(l_proc, 70);
2574 --
2575 chk_position_id
2576 (p_vacancy_id => p_rec.vacancy_id
2577 ,p_object_version_number => p_rec.object_version_number
2578 ,p_position_id => p_rec.position_id
2579 ,p_business_group_id => p_rec.business_group_id
2580 ,p_effective_date => p_effective_date
2581 );
2582 --
2583 hr_utility.set_location(l_proc, 80);
2584 --
2585 chk_position_id_org_id
2586 (p_vacancy_id => p_rec.vacancy_id
2587 ,p_object_version_number => p_rec.object_version_number
2588 ,p_position_id => p_rec.position_id
2589 ,p_organization_id => p_rec.organization_id
2590 ,p_effective_date => p_effective_date
2591 );
2592 --
2593 hr_utility.set_location(l_proc, 90);
2594 --
2595 chk_position_id_job_id
2596 (p_vacancy_id => p_rec.vacancy_id
2597 ,p_object_version_number => p_rec.object_version_number
2598 ,p_position_id => p_rec.position_id
2599 ,p_job_id => p_rec.job_id
2600 ,p_effective_date => p_effective_date
2601 );
2602 --
2603 hr_utility.set_location(l_proc, 100);
2604 --
2605 chk_grade_id
2606 (p_vacancy_id => p_rec.vacancy_id
2607 ,p_object_version_number => p_rec.object_version_number
2608 ,p_grade_id => p_rec.grade_id
2609 ,p_effective_date => p_effective_date
2610 ,p_business_group_id => per_vac_shd.g_old_rec.business_group_id
2611 );
2612 --
2613 hr_utility.set_location(l_proc, 110);
2614 --
2615 chk_position_id_grade_id
2616 (p_vacancy_id => p_rec.vacancy_id
2617 ,p_object_version_number => p_rec.object_version_number
2618 ,p_position_id => p_rec.position_id
2619 ,p_grade_id => p_rec.grade_id
2620 ,p_effective_date => p_effective_date
2621 ,p_inv_pos_grade_warning => l_inv_pos_grade_warning
2622 );
2623 --
2624 hr_utility.set_location(l_proc, 120);
2625 --
2626 per_vac_bus.chk_job_id_grade_id
2627 (p_vacancy_id => p_rec.vacancy_id
2628 ,p_job_id => p_rec.job_id
2629 ,p_grade_id => p_rec.grade_id
2630 ,p_effective_date => p_effective_date
2631 ,p_object_version_number => p_rec.object_version_number
2632 ,p_inv_job_grade_warning => l_inv_job_grade_warning
2633 );
2634 --
2635 hr_utility.set_location(l_proc, 130);
2636 --
2637 chk_people_group_id
2638 (p_vacancy_id => p_rec.vacancy_id
2639 ,p_object_version_number => p_rec.object_version_number
2640 ,p_people_group_id => p_rec.people_group_id
2641 ,p_effective_date => p_effective_date
2642 );
2643 --
2644 hr_utility.set_location(l_proc, 140);
2645 --
2646 chk_location_id
2647 (p_vacancy_id => p_rec.vacancy_id
2648 ,p_object_version_number => p_rec.object_version_number
2649 ,p_location_id => p_rec.location_id
2650 );
2651 --
2652 hr_utility.set_location(l_proc, 150);
2653 --
2654 chk_recruiter_id
2655 (p_vacancy_id => p_rec.vacancy_id
2656 ,p_object_version_number => p_rec.object_version_number
2657 ,p_recruiter_id => p_rec.recruiter_id
2658 ,p_effective_date => p_effective_date
2659 ,p_business_group_id => per_vac_shd.g_old_rec.business_group_id
2660 );
2661 --
2662 hr_utility.set_location(l_proc, 160);
2663 --
2664 chk_number_of_openings
2665 (p_vacancy_id => p_rec.vacancy_id
2666 ,p_object_version_number => p_rec.object_version_number
2667 ,p_num_open => p_rec.number_of_openings
2668 );
2669 --
2670 hr_utility.set_location(l_proc, 170);
2671 --
2672 chk_budget_measurement_value
2673 (p_vacancy_id => p_rec.vacancy_id
2674 ,p_object_version_number => p_rec.object_version_number
2675 ,p_bud_mea_val => p_rec.budget_measurement_value
2676 );
2677 --
2678 hr_utility.set_location(l_proc, 180);
2679 --
2680 chk_manager_id
2681 (p_vacancy_id => p_rec.vacancy_id
2682 ,p_object_version_number => p_rec.object_version_number
2683 ,p_manager_id => p_rec.manager_id
2684 ,p_effective_date => p_effective_date
2685 ,p_business_group_id => per_vac_shd.g_old_rec.business_group_id
2686 );
2687 --
2688 hr_utility.set_location(l_proc, 190);
2689 --
2690 chk_manager_assignment_id
2691 (p_vacancy_id => p_rec.vacancy_id
2692 ,p_object_version_number => p_rec.object_version_number
2693 ,p_manager_id => p_rec.manager_id
2694 ,p_effective_date => p_effective_date
2695 );
2696 --
2697 hr_utility.set_location(l_proc, 200);
2698 --
2699 chk_primary_posting_id
2700 (p_vacancy_id => p_rec.vacancy_id
2701 ,p_object_version_number => p_rec.object_version_number
2702 ,p_primary_posting_id => p_rec.primary_posting_id
2703 );
2704 --
2705 hr_utility.set_location(l_proc, 210);
2706 --
2707 chk_assessment_id
2708 (p_vacancy_id => p_rec.vacancy_id
2709 ,p_object_version_number => p_rec.object_version_number
2710 ,p_assessment_id => p_rec.assessment_id
2711 );
2712 --
2713 -- ER#8530112 starts here
2714 per_vacancies_pkg.
2715 CHK_POS_BUDGET_VAL( p_rec.Position_Id,p_effective_date,p_rec.Organization_Id,p_rec.Number_Of_Openings,p_rec.Vacancy_Id);
2716 -- ER#8530112 ends here
2717 hr_utility.set_location(l_proc, 220);
2718 --
2719 per_vac_bus.chk_df(p_rec);
2720 --
2721 p_inv_job_grade_warning:=l_inv_job_grade_warning;
2722 p_inv_pos_grade_warning:=l_inv_pos_grade_warning;
2723 --
2724 hr_utility.set_location(' Leaving:'||l_proc, 230);
2725 End update_validate;
2726 --
2727 -- ----------------------------------------------------------------------------
2728 -- |---------------------------< delete_validate >----------------------------|
2729 -- ----------------------------------------------------------------------------
2730 Procedure delete_validate
2731 (p_rec in per_vac_shd.g_rec_type
2732 ) is
2733 --
2734 l_proc varchar2(72) := g_package||'delete_validate';
2735 --
2736 Begin
2737 hr_utility.set_location('Entering:'||l_proc, 5);
2738 --
2739 -- Call all supporting business operations
2740 --
2741 hr_utility.set_location(' Leaving:'||l_proc, 10);
2742 End delete_validate;
2743 --
2744 end per_vac_bus;