[Home] [Help]
PACKAGE BODY: APPS.PE_POI_BUS
Source
1 Package Body pe_poi_bus as
2 /* $Header: pepoirhi.pkb 120.0 2005/05/31 14:50:38 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pe_poi_bus.'; -- Global package name
9 --
10 --
11 -- The following two global variables are only to be
12 -- used by the return_legislation_code function.
13 --
14 g_legislation_code varchar2(150) default null;
15 g_position_extra_info_id number default null;
16 --
17 --
18 --
19 -- ---------------------------------------------------------------------------
20 -- |----------------------< set_security_group_id >--------------------------|
21 -- ---------------------------------------------------------------------------
22 --
23 Procedure set_security_group_id
24 (p_position_extra_info_id in number
25 ) is
26 --
27 -- Declare cursor
28 --
29 -- EDIT_HERE In the following cursor statement add join(s) between
30 -- per_position_extra_info and PER_BUSINESS_GROUPS
31 -- so that the security_group_id for
32 -- the current business group context can be derived.
33 -- Remove this comment when the edit has been completed.
34 cursor csr_sec_grp is
35 select pbg.security_group_id
36 from per_business_groups pbg
37 , per_position_extra_info poi
38 , hr_all_positions_f pos
39 where poi.position_extra_info_id = p_position_extra_info_id
40 and pbg.business_group_id = pos.business_group_id
41 and pos.position_id = poi.position_id;
42 --
43 -- Declare local variables
44 --
45 l_security_group_id number;
46 l_proc varchar2(72) := g_package||'set_security_group_id';
47 --
48 begin
49 --
50 hr_utility.set_location('Entering:'|| l_proc, 10);
51 --
52 -- Ensure that all the mandatory parameter are not null
53 --
54 hr_api.mandatory_arg_error
55 (p_api_name => l_proc
56 ,p_argument => 'position_extra_info_id'
57 ,p_argument_value => p_position_extra_info_id
58 );
59 --
60 open csr_sec_grp;
61 fetch csr_sec_grp into l_security_group_id;
62 --
63 if csr_sec_grp%notfound then
64 --
65 close csr_sec_grp;
66 --
67 -- The primary key is invalid therefore we must error
68 --
69 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
70 fnd_message.raise_error;
71 --
72 end if;
73 close csr_sec_grp;
74 --
75 -- Set the security_group_id in CLIENT_INFO
76 --
77 hr_api.set_security_group_id
78 (p_security_group_id => l_security_group_id
79 );
80 --
81 hr_utility.set_location(' Leaving:'|| l_proc, 20);
82 --
83 end set_security_group_id;
84 --
85
86 -- ---------------------------------------------------------------------------
87 -- |---------------------< return_legislation_code >-------------------------|
88 -- ---------------------------------------------------------------------------
89 --
90 Function return_legislation_code
91 (p_position_extra_info_id in number
92 )
93 Return Varchar2 Is
94 --
95 -- Declare cursor
96 --
97 -- In the following cursor statement add join(s) between
98 -- per_position_extra_info, per_positions and PER_BUSINESS_GROUPS
99 -- so that the legislation_code for
100 -- the current business group context can be derived.
101 -- Remove this comment when the edit has been completed.
102 cursor csr_leg_code is
103 select pbg.legislation_code
104 from per_business_groups pbg
105 , per_position_extra_info poi
106 , per_positions pos
107 where poi.position_extra_info_id = p_position_extra_info_id
108 and pos.position_id = poi.position_id
109 and pbg.business_group_id = pos.business_group_id;
110 --
111 -- Declare local variables
112 --
113 l_legislation_code varchar2(150);
114 l_proc varchar2(72) := g_package||'return_legislation_code';
115 --
116 Begin
117 --
118 hr_utility.set_location('Entering:'|| l_proc, 10);
119 --
120 --
121 -- Ensure that all the mandatory parameter are not null
122 --
123 hr_api.mandatory_arg_error
124 (p_api_name => l_proc
125 ,p_argument => 'position_extra_info_id'
126 ,p_argument_value => p_position_extra_info_id
127 );
128 --
129 if ( nvl(pe_poi_bus.g_position_extra_info_id, hr_api.g_number)
130 = p_position_extra_info_id) then
131 --
132 -- The legislation code has already been found with a previous
133 -- call to this function. Just return the value in the global
134 -- variable.
135 --
136 l_legislation_code := pe_poi_bus.g_legislation_code;
137 hr_utility.set_location(l_proc, 20);
138 else
139 --
140 -- The ID is different to the last call to this function
141 -- or this is the first call to this function.
142 --
143 open csr_leg_code;
144 fetch csr_leg_code into l_legislation_code;
145 --
146 if csr_leg_code%notfound then
147 --
148 -- The primary key is invalid therefore we must error
149 --
150 close csr_leg_code;
151 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
152 fnd_message.raise_error;
153 end if;
154 hr_utility.set_location(l_proc,30);
155 --
156 -- Set the global variables so the values are
157 -- available for the next call to this function.
158 --
159 --
160 close csr_leg_code;
161 pe_poi_bus.g_position_extra_info_id := p_position_extra_info_id;
162 pe_poi_bus.g_legislation_code := l_legislation_code;
163 end if;
164 hr_utility.set_location(' Leaving:'|| l_proc, 40);
165 return l_legislation_code;
166 end return_legislation_code;
167
168 --
169 -- ----------------------------------------------------------------------------
170 -- |--------------------------<chk_position_info_type >-----------------------|
171 -- ----------------------------------------------------------------------------
172 -- {Start Of Comments}
173 --
174 -- Description:
175 -- Validates that the position information type exists in table
176 -- per_position_info_types where active_inactive_flag is 'Y'.
177 --
178 -- Pre Conditions:
179 -- Data must be existed in table per_position_info_types.
180 --
181 -- In Parameters:
182 -- p_information_type
183 --
184 -- Post Success:
185 -- Processing continues.
186 --
187 -- Post Failure:
188 -- An application error will be raised and processing is terminated.
189 --
190 -- Developer Implementation Notes:
191 -- For insert, your business rules should be executed from this procedure and
192 -- should ideally (unless really necessary) just be straight procedure or
193 -- function calls. Try and avoid using conditional branching logic.
194 --
195 -- Access Status:
196 -- Internal Table Handler Use Only.
197 --
198 -- {End Of Comments}
199 -- ----------------------------------------------------------------------------
200 Procedure chk_position_info_type
201 (
202 p_information_type in per_position_info_types.information_type%type
203 ) is
204 --
205 l_proc varchar2(72) := g_package||'chk_position_info_type';
206 l_flag per_position_info_types.active_inactive_flag%type;
207 --
208 cursor c_pos_info_type (code varchar2) is
209 select poit.active_inactive_flag
210 from per_position_info_types poit
211 where poit.information_type = code;
212 --
213 Begin
214 hr_utility.set_location('Entering:'||l_proc, 1);
215 --
216 -- Check mandatory parameters have been set
217 --
218 hr_api.mandatory_arg_error
219 (
220 p_api_name => l_proc,
221 p_argument => 'information_type',
222 p_argument_value => p_information_type
223 );
224 --
225 hr_utility.set_location(l_proc, 2);
226 --
227 -- Check that the ACTIVE_INACTIVE_FLAG of Position
228 -- Information type is active.
229 --
230 open c_pos_info_type (p_information_type);
231 fetch c_pos_info_type into l_flag;
232 if c_pos_info_type%notfound then
233 close c_pos_info_type;
234 hr_utility.set_message(800, 'HR_INV_INFO_TYPE');
235 hr_utility.raise_error;
236 end if;
237 close c_pos_info_type;
238 --
239 if l_flag = 'N' then
240 hr_utility.set_message(800, 'HR_INACTIVE_INFO_TYPE');
241 hr_utility.raise_error;
242 end if;
243 --
244 hr_utility.set_location(' Leaving:'||l_proc, 3);
245 --
246 End chk_position_info_type;
247 --
248 -- ----------------------------------------------------------------------------
249 -- |---------------------------< Chk_position_id >----------------------------|
250 -- ----------------------------------------------------------------------------
251 -- {Start Of Comments}
252 --
253 -- Description:
254 -- Verify that the value in POSITION_ID is in the hr_positions table.
255 --
256 -- Pre Conditions:
257 --
258 --
259 -- In Parameters:
260 -- p_position_id
261 --
262 -- Post Success:
263 -- Processing continues.
264 --
265 -- Post Failure:
266 -- An application error will be raised and processing is terminated.
267 --
268 -- Developer Implementation Notes:
269 -- For insert, your business rules should be executed from this procedure and
270 -- should ideally (unless really necessary) just be straight procedure or
271 -- function calls. Try and avoid using conditional branching logic.
272 --
273 -- Access Status:
274 -- Internal Table Handler Use Only.
275 --
276 -- {End Of Comments}
277 -- ----------------------------------------------------------------------------
278 Procedure chk_position_id
279 (
280 p_position_id in per_position_extra_info.position_id%type
281 ) is
282 --
283 l_proc varchar2(72) := g_package||'chk_position_id';
284 l_dummy varchar2(1);
285 --
286 --
287 -- Changed 12-Oct-99 SCNair (per_positions to hr_positions) date tracked position req.
288 --
289 cursor c_valid_pos (id number) is
290 select 'x'
291 from hr_all_positions_f
292 where position_id = id;
293 --
294 Begin
295 hr_utility.set_location('Entering:'||l_proc, 1);
296 --
297 -- Check mandatory parameters have been set
298 --
299 hr_api.mandatory_arg_error
300 (
301 p_api_name => l_proc,
302 p_argument => 'position_id',
303 p_argument_value => p_position_id
304 );
305 hr_utility.set_location(l_proc, 2);
306 --
307 -- Check that the position_id is in the hr_positions table.
308 --
309 open c_valid_pos (p_position_id);
310 fetch c_valid_pos into l_dummy;
311 if c_valid_pos%notfound then
312 close c_valid_pos;
313 hr_utility.set_message(800, 'HR_INV_POS_ID');
314 hr_utility.raise_error;
315 end if;
316 close c_valid_pos;
317 --
318 hr_utility.set_location(' Leaving:'||l_proc, 3);
319 --
320 End chk_position_id;
321 --
322 -- ----------------------------------------------------------------------------
323 -- |--------------------< chk_multiple_occurences_flag >----------------------|
324 -- ----------------------------------------------------------------------------
325 -- {Start Of Comments}
326 --
327 -- Description:
328 -- Verify that the number of rows should not exceed one when
329 -- multiple_occurences_flag = 'N'.
330 --
331 -- Pre Conditions:
332 -- This procedure should execute after procedure chk_information_type.
333 --
334 -- In Parameters:
335 -- p_information_type
336 -- p_position_id
337 --
338 -- Post Success:
339 -- Processing continues.
340 --
341 -- Post Failure:
342 -- An application error will be raised and processing is terminated.
343 --
344 -- Developer Implementation Notes:
345 -- For insert, your business rules should be executed from this procedure and
346 -- should ideally (unless really necessary) just be straight procedure or
347 -- function calls. Try and avoid using conditional branching logic.
348 --
349 -- Access Status:
350 -- Internal Table Handler Use Only.
351 --
352 -- {End Of Comments}
353 -- ----------------------------------------------------------------------------
354 Procedure chk_multiple_occurences_flag
355 (p_information_type in per_position_extra_info.information_type%type
356 ,p_position_id in per_position_extra_info.position_id%type
357 ) is
358 --
359 l_proc varchar2(72) := g_package||'chk_multiple_occurences_flag';
360 l_multi_occur_flag per_position_info_types.multiple_occurences_flag%type;
361 l_dummy varchar2(1);
362 l_found_poi boolean;
363 --
364 cursor c_multi_occur_flag (code varchar2) is
365 select multiple_occurences_flag
366 from per_position_info_types
367 where information_type = code;
368 --
369 cursor c_get_row (code varchar2, id number) is
370 select 'x'
371 from per_position_extra_info
372 where information_type = code
373 and position_id = id;
374 --
375 Begin
376 hr_utility.set_location('Entering:'||l_proc, 5);
377 --
378 open c_multi_occur_flag (p_information_type);
379 fetch c_multi_occur_flag into l_multi_occur_flag;
380 --
381 -- The following case should not happen since procedure
382 -- chk_information_type should capture this error.
383 --
384 if c_multi_occur_flag%notfound then
385 close c_multi_occur_flag;
386 hr_utility.set_message(800, 'HR_INV_INFO_TYPE');
387 hr_utility.raise_error;
388 end if;
389 --
390 close c_multi_occur_flag;
391 --
392 hr_utility.set_location(l_proc, 10);
393 --
394 open c_get_row(p_information_type, p_position_id);
395 fetch c_get_row into l_dummy;
396 if c_get_row%notfound then
397 l_found_poi := FALSE;
398 else
399 l_found_poi := TRUE;
400 end if;
401 close c_get_row;
402 --
403 if l_found_poi and l_multi_occur_flag = 'N' then
404 hr_utility.set_message(800, 'HR_MORE_THAN_1_EXTRA_INFO');
405 hr_utility.raise_error;
406 end if;
407 --
408 hr_utility.set_location(' Leaving:'||l_proc, 15);
409 --
410 End chk_multiple_occurences_flag;
411 --
412 -- ----------------------------------------------------------------------------
413 -- |-----------------------< chk_non_updateable_args >------------------------|
414 -- ----------------------------------------------------------------------------
415 -- {Start Of Comments}
416 --
417 -- Description:
418 -- Verify that the non updateable arguments not changed.
419 --
420 -- Pre Conditions:
421 --
422 --
423 -- In Parameters:
424 -- p_rec
425 --
426 -- Post Success:
427 -- Processing continues.
428 --
429 -- Post Failure:
430 -- An application error will be raised and processing is terminated.
431 --
432 -- Developer Implementation Notes:
433 -- For insert, your business rules should be executed from this procedure and
434 -- should ideally (unless really necessary) just be straight procedure or
435 -- function calls. Try and avoid using conditional branching logic.
436 --
437 -- Access Status:
438 -- Internal Table Handler Use Only.
439 --
440 -- {End Of Comments}
441 -- ----------------------------------------------------------------------------
442 Procedure chk_non_updateable_args (p_rec in pe_poi_shd.g_rec_type) is
443 --
444 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
445 l_error exception;
446 l_argument varchar2(30);
447 --
448 Begin
449 hr_utility.set_location('Entering:'||l_proc, 10);
450 --
451 -- Only proceed with validation if a row exists for
452 -- the current record in the HR Schema
453 --
454 if not pe_poi_shd.api_updating
455 (p_position_extra_info_id => p_rec.position_extra_info_id
456 ,p_object_version_number => p_rec.object_version_number) then
457 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
458 hr_utility.set_message_token('PROCEDURE', l_proc);
459 hr_utility.set_message_token('STEP', '20');
460 end if;
461 --
462 hr_utility.set_location(l_proc, 30);
463 --
464 if nvl(p_rec.information_type, hr_api.g_varchar2) <>
465 nvl(pe_poi_shd.g_old_rec.information_type, hr_api.g_varchar2) then
466 l_argument := 'information_type';
467 raise l_error;
468 end if;
469 --
470 hr_utility.set_location(l_proc, 40);
471 --
472 if nvl(p_rec.position_id, hr_api.g_number) <>
473 nvl(pe_poi_shd.g_old_rec.position_id, hr_api.g_number) then
474 l_argument := 'position_id';
475 raise l_error;
476 end if;
477 --
478 hr_utility.set_location(' Leaving:'||l_proc, 50);
479 exception
480 when l_error then
481 hr_api.argument_changed_error
482 (p_api_name => l_proc
483 ,p_argument => l_argument
484 );
485 hr_utility.set_location(l_proc, 60);
486 when others then
487 hr_utility.set_location(l_proc, 70);
488 raise;
489 end chk_non_updateable_args;
490 --
491 -- -----------------------------------------------------------------------
492 -- |------------------------------< chk_df >-----------------------------|
493 -- -----------------------------------------------------------------------
494 --
495 -- Description:
496 -- Validates the all Descriptive Flexfield values.
497 --
498 -- Pre-conditions:
499 -- All other columns have been validated. Must be called as the
500 -- last step from insert_validate and update_validate.
501 --
502 -- In Arguments:
503 -- p_rec
504 --
505 -- Post Success:
506 -- If the Descriptive Flexfield structure column and data values are
507 -- all valid this procedure will end normally and processing will
508 -- continue.
509 --
510 -- Post Failure:
511 -- If the Descriptive Flexfield structure column value or any of
512 -- the data values are invalid then an application error is raised as
513 -- a PL/SQL exception.
514 --
515 -- Access Status:
516 -- Internal Row Handler Use Only.
517 --
518 procedure chk_df
519 (p_rec in pe_poi_shd.g_rec_type) is
520 --
521 l_proc varchar2(72) := g_package||'chk_df';
522 --
523 begin
524 hr_utility.set_location('Entering:'||l_proc, 10);
525 --
526 if ((p_rec.position_extra_info_id is not null) and (
527 nvl(pe_poi_shd.g_old_rec.poei_attribute_category, hr_api.g_varchar2) <>
528 nvl(p_rec.poei_attribute_category, hr_api.g_varchar2) or
529 nvl(pe_poi_shd.g_old_rec.poei_attribute1, hr_api.g_varchar2) <>
530 nvl(p_rec.poei_attribute1, hr_api.g_varchar2) or
531 nvl(pe_poi_shd.g_old_rec.poei_attribute2, hr_api.g_varchar2) <>
532 nvl(p_rec.poei_attribute2, hr_api.g_varchar2) or
533 nvl(pe_poi_shd.g_old_rec.poei_attribute3, hr_api.g_varchar2) <>
534 nvl(p_rec.poei_attribute3, hr_api.g_varchar2) or
535 nvl(pe_poi_shd.g_old_rec.poei_attribute4, hr_api.g_varchar2) <>
536 nvl(p_rec.poei_attribute4, hr_api.g_varchar2) or
537 nvl(pe_poi_shd.g_old_rec.poei_attribute5, hr_api.g_varchar2) <>
538 nvl(p_rec.poei_attribute5, hr_api.g_varchar2) or
539 nvl(pe_poi_shd.g_old_rec.poei_attribute6, hr_api.g_varchar2) <>
540 nvl(p_rec.poei_attribute6, hr_api.g_varchar2) or
541 nvl(pe_poi_shd.g_old_rec.poei_attribute7, hr_api.g_varchar2) <>
542 nvl(p_rec.poei_attribute7, hr_api.g_varchar2) or
543 nvl(pe_poi_shd.g_old_rec.poei_attribute8, hr_api.g_varchar2) <>
544 nvl(p_rec.poei_attribute8, hr_api.g_varchar2) or
545 nvl(pe_poi_shd.g_old_rec.poei_attribute9, hr_api.g_varchar2) <>
546 nvl(p_rec.poei_attribute9, hr_api.g_varchar2) or
547 nvl(pe_poi_shd.g_old_rec.poei_attribute10, hr_api.g_varchar2) <>
548 nvl(p_rec.poei_attribute10, hr_api.g_varchar2) or
549 nvl(pe_poi_shd.g_old_rec.poei_attribute11, hr_api.g_varchar2) <>
550 nvl(p_rec.poei_attribute11, hr_api.g_varchar2) or
551 nvl(pe_poi_shd.g_old_rec.poei_attribute12, hr_api.g_varchar2) <>
552 nvl(p_rec.poei_attribute12, hr_api.g_varchar2) or
553 nvl(pe_poi_shd.g_old_rec.poei_attribute13, hr_api.g_varchar2) <>
554 nvl(p_rec.poei_attribute13, hr_api.g_varchar2) or
555 nvl(pe_poi_shd.g_old_rec.poei_attribute14, hr_api.g_varchar2) <>
556 nvl(p_rec.poei_attribute14, hr_api.g_varchar2) or
557 nvl(pe_poi_shd.g_old_rec.poei_attribute15, hr_api.g_varchar2) <>
558 nvl(p_rec.poei_attribute15, hr_api.g_varchar2) or
559 nvl(pe_poi_shd.g_old_rec.poei_attribute16, hr_api.g_varchar2) <>
560 nvl(p_rec.poei_attribute16, hr_api.g_varchar2) or
561 nvl(pe_poi_shd.g_old_rec.poei_attribute17, hr_api.g_varchar2) <>
562 nvl(p_rec.poei_attribute17, hr_api.g_varchar2) or
563 nvl(pe_poi_shd.g_old_rec.poei_attribute18, hr_api.g_varchar2) <>
564 nvl(p_rec.poei_attribute18, hr_api.g_varchar2) or
565 nvl(pe_poi_shd.g_old_rec.poei_attribute19, hr_api.g_varchar2) <>
566 nvl(p_rec.poei_attribute19, hr_api.g_varchar2) or
567 nvl(pe_poi_shd.g_old_rec.poei_attribute20, hr_api.g_varchar2) <>
568 nvl(p_rec.poei_attribute20, hr_api.g_varchar2)))
569 or
570 (p_rec.position_extra_info_id is null) then
571 --
572 -- Only execute the validation if absolutely necessary:
573 -- a) During update, the structure column value or any
574 -- of the attribute values have actually changed.
575 -- b) During insert.
576 --
577 hr_dflex_utility.ins_or_upd_descflex_attribs
578 (p_appl_short_name => 'PER'
579 ,p_descflex_name => 'PER_POSITION_EXTRA_INFO'
580 ,p_attribute_category => p_rec.poei_attribute_category
581 ,p_attribute1_name => 'POEI_ATTRIBUTE1'
582 ,p_attribute1_value => p_rec.poei_attribute1
583 ,p_attribute2_name => 'POEI_ATTRIBUTE2'
584 ,p_attribute2_value => p_rec.poei_attribute2
585 ,p_attribute3_name => 'POEI_ATTRIBUTE3'
586 ,p_attribute3_value => p_rec.poei_attribute3
587 ,p_attribute4_name => 'POEI_ATTRIBUTE4'
588 ,p_attribute4_value => p_rec.poei_attribute4
589 ,p_attribute5_name => 'POEI_ATTRIBUTE5'
590 ,p_attribute5_value => p_rec.poei_attribute5
591 ,p_attribute6_name => 'POEI_ATTRIBUTE6'
592 ,p_attribute6_value => p_rec.poei_attribute6
593 ,p_attribute7_name => 'POEI_ATTRIBUTE7'
594 ,p_attribute7_value => p_rec.poei_attribute7
595 ,p_attribute8_name => 'POEI_ATTRIBUTE8'
596 ,p_attribute8_value => p_rec.poei_attribute8
597 ,p_attribute9_name => 'POEI_ATTRIBUTE9'
598 ,p_attribute9_value => p_rec.poei_attribute9
599 ,p_attribute10_name => 'POEI_ATTRIBUTE10'
600 ,p_attribute10_value => p_rec.poei_attribute10
601 ,p_attribute11_name => 'POEI_ATTRIBUTE11'
602 ,p_attribute11_value => p_rec.poei_attribute11
603 ,p_attribute12_name => 'POEI_ATTRIBUTE12'
604 ,p_attribute12_value => p_rec.poei_attribute12
605 ,p_attribute13_name => 'POEI_ATTRIBUTE13'
606 ,p_attribute13_value => p_rec.poei_attribute13
607 ,p_attribute14_name => 'POEI_ATTRIBUTE14'
608 ,p_attribute14_value => p_rec.poei_attribute14
609 ,p_attribute15_name => 'POEI_ATTRIBUTE15'
610 ,p_attribute15_value => p_rec.poei_attribute15
611 ,p_attribute16_name => 'POEI_ATTRIBUTE16'
612 ,p_attribute16_value => p_rec.poei_attribute16
613 ,p_attribute17_name => 'POEI_ATTRIBUTE17'
614 ,p_attribute17_value => p_rec.poei_attribute17
615 ,p_attribute18_name => 'POEI_ATTRIBUTE18'
616 ,p_attribute18_value => p_rec.poei_attribute18
617 ,p_attribute19_name => 'POEI_ATTRIBUTE19'
618 ,p_attribute19_value => p_rec.poei_attribute19
619 ,p_attribute20_name => 'POEI_ATTRIBUTE20'
620 ,p_attribute20_value => p_rec.poei_attribute20);
621 end if;
622 --
623 hr_utility.set_location(' Leaving:'||l_proc, 20);
624 end chk_df;
625 --
626 -- -----------------------------------------------------------------------
627 -- |------------------------------< chk_ddf >----------------------------|
628 -- -----------------------------------------------------------------------
629 --
630 -- Description:
631 -- Validates the all Developer Descriptive Flexfield values.
632 --
633 -- Pre-conditions:
634 -- All other columns have been validated. Must be called as the
635 -- second last step from insert_validate and update_validate.
636 -- Before any Descriptive Flexfield (chk_df) calls.
637 --
638 -- In Arguments:
639 -- p_rec
640 --
641 -- Post Success:
642 -- If the Developer Descriptive Flexfield structure column and data
643 -- values are all valid this procedure will end normally and
644 -- processing will continue.
645 --
646 -- Post Failure:
647 -- If the DDF structure column value or any of the data values
648 -- are invalid then an application error is raised as
649 -- a PL/SQL exception.
650 --
651 -- Access Status:
652 -- Internal Row Handler Use Only.
653 --
654 -- {End Of Comments}
655 -- ----------------------------------------------------------------------------
656 --
657 procedure chk_ddf
658 (p_rec in pe_poi_shd.g_rec_type) is
659 --
660 l_proc varchar2(72) := g_package||'chk_ddf';
661 l_error exception;
662 --
663 Begin
664 hr_utility.set_location('Entering:'||l_proc, 5);
665 --
666 -- Check if the row is being inserted or updated and a
667 -- value has changed
668 --
669 if (p_rec.position_extra_info_id is null)
670 or ((p_rec.position_extra_info_id is not null)
671 and
672 nvl(pe_poi_shd.g_old_rec.poei_attribute_category, hr_api.g_varchar2) <>
673 nvl(p_rec.poei_information_category, hr_api.g_varchar2) or
674 nvl(pe_poi_shd.g_old_rec.poei_information1, hr_api.g_varchar2) <>
675 nvl(p_rec.poei_information1, hr_api.g_varchar2) or
676 nvl(pe_poi_shd.g_old_rec.poei_information2, hr_api.g_varchar2) <>
677 nvl(p_rec.poei_information2, hr_api.g_varchar2) or
678 nvl(pe_poi_shd.g_old_rec.poei_information3, hr_api.g_varchar2) <>
679 nvl(p_rec.poei_information3, hr_api.g_varchar2) or
680 nvl(pe_poi_shd.g_old_rec.poei_information4, hr_api.g_varchar2) <>
681 nvl(p_rec.poei_information4, hr_api.g_varchar2) or
682 nvl(pe_poi_shd.g_old_rec.poei_information5, hr_api.g_varchar2) <>
683 nvl(p_rec.poei_information5, hr_api.g_varchar2) or
684 nvl(pe_poi_shd.g_old_rec.poei_information6, hr_api.g_varchar2) <>
685 nvl(p_rec.poei_information6, hr_api.g_varchar2) or
686 nvl(pe_poi_shd.g_old_rec.poei_information7, hr_api.g_varchar2) <>
687 nvl(p_rec.poei_information7, hr_api.g_varchar2) or
688 nvl(pe_poi_shd.g_old_rec.poei_information8, hr_api.g_varchar2) <>
689 nvl(p_rec.poei_information8, hr_api.g_varchar2) or
690 nvl(pe_poi_shd.g_old_rec.poei_information9, hr_api.g_varchar2) <>
691 nvl(p_rec.poei_information9, hr_api.g_varchar2) or
692 nvl(pe_poi_shd.g_old_rec.poei_information10, hr_api.g_varchar2) <>
693 nvl(p_rec.poei_information10, hr_api.g_varchar2) or
694 nvl(pe_poi_shd.g_old_rec.poei_information11, hr_api.g_varchar2) <>
695 nvl(p_rec.poei_information11, hr_api.g_varchar2) or
696 nvl(pe_poi_shd.g_old_rec.poei_information12, hr_api.g_varchar2) <>
697 nvl(p_rec.poei_information12, hr_api.g_varchar2) or
698 nvl(pe_poi_shd.g_old_rec.poei_information13, hr_api.g_varchar2) <>
699 nvl(p_rec.poei_information13, hr_api.g_varchar2) or
700 nvl(pe_poi_shd.g_old_rec.poei_information14, hr_api.g_varchar2) <>
701 nvl(p_rec.poei_information14, hr_api.g_varchar2) or
702 nvl(pe_poi_shd.g_old_rec.poei_information15, hr_api.g_varchar2) <>
703 nvl(p_rec.poei_information15, hr_api.g_varchar2) or
704 nvl(pe_poi_shd.g_old_rec.poei_information16, hr_api.g_varchar2) <>
705 nvl(p_rec.poei_information16, hr_api.g_varchar2) or
706 nvl(pe_poi_shd.g_old_rec.poei_information17, hr_api.g_varchar2) <>
707 nvl(p_rec.poei_information17, hr_api.g_varchar2) or
708 nvl(pe_poi_shd.g_old_rec.poei_information18, hr_api.g_varchar2) <>
709 nvl(p_rec.poei_information18, hr_api.g_varchar2) or
710 nvl(pe_poi_shd.g_old_rec.poei_information19, hr_api.g_varchar2) <>
711 nvl(p_rec.poei_information19, hr_api.g_varchar2) or
712 nvl(pe_poi_shd.g_old_rec.poei_information20, hr_api.g_varchar2) <>
713 nvl(p_rec.poei_information20, hr_api.g_varchar2) or
714 nvl(pe_poi_shd.g_old_rec.poei_information21, hr_api.g_varchar2) <>
715 nvl(p_rec.poei_information21, hr_api.g_varchar2) or
716 nvl(pe_poi_shd.g_old_rec.poei_information22, hr_api.g_varchar2) <>
717 nvl(p_rec.poei_information22, hr_api.g_varchar2) or
718 nvl(pe_poi_shd.g_old_rec.poei_information23, hr_api.g_varchar2) <>
719 nvl(p_rec.poei_information23, hr_api.g_varchar2) or
720 nvl(pe_poi_shd.g_old_rec.poei_information24, hr_api.g_varchar2) <>
721 nvl(p_rec.poei_information24, hr_api.g_varchar2) or
722 nvl(pe_poi_shd.g_old_rec.poei_information25, hr_api.g_varchar2) <>
723 nvl(p_rec.poei_information25, hr_api.g_varchar2) or
724 nvl(pe_poi_shd.g_old_rec.poei_information26, hr_api.g_varchar2) <>
725 nvl(p_rec.poei_information26, hr_api.g_varchar2) or
726 nvl(pe_poi_shd.g_old_rec.poei_information27, hr_api.g_varchar2) <>
727 nvl(p_rec.poei_information27, hr_api.g_varchar2) or
728 nvl(pe_poi_shd.g_old_rec.poei_information28, hr_api.g_varchar2) <>
729 nvl(p_rec.poei_information28, hr_api.g_varchar2) or
730 nvl(pe_poi_shd.g_old_rec.poei_information29, hr_api.g_varchar2) <>
731 nvl(p_rec.poei_information29, hr_api.g_varchar2) or
732 nvl(pe_poi_shd.g_old_rec.poei_information30, hr_api.g_varchar2) <>
733 nvl(p_rec.poei_information30, hr_api.g_varchar2))
734 then
735 --
736 hr_dflex_utility.ins_or_upd_descflex_attribs
737 (p_appl_short_name => 'PER'
738 ,p_descflex_name => 'Extra Position Info DDF'
739 ,p_attribute_category => p_rec.poei_information_category
740 ,p_attribute1_name => 'POEI_INFORMATION1'
741 ,p_attribute1_value => p_rec.poei_information1
742 ,p_attribute2_name => 'POEI_INFORMATION2'
743 ,p_attribute2_value => p_rec.poei_information2
744 ,p_attribute3_name => 'POEI_INFORMATION3'
745 ,p_attribute3_value => p_rec.poei_information3
746 ,p_attribute4_name => 'POEI_INFORMATION4'
747 ,p_attribute4_value => p_rec.poei_information4
748 ,p_attribute5_name => 'POEI_INFORMATION5'
749 ,p_attribute5_value => p_rec.poei_information5
750 ,p_attribute6_name => 'POEI_INFORMATION6'
751 ,p_attribute6_value => p_rec.poei_information6
752 ,p_attribute7_name => 'POEI_INFORMATION7'
753 ,p_attribute7_value => p_rec.poei_information7
754 ,p_attribute8_name => 'POEI_INFORMATION8'
755 ,p_attribute8_value => p_rec.poei_information8
756 ,p_attribute9_name => 'POEI_INFORMATION9'
757 ,p_attribute9_value => p_rec.poei_information9
758 ,p_attribute10_name => 'POEI_INFORMATION10'
759 ,p_attribute10_value => p_rec.poei_information10
760 ,p_attribute11_name => 'POEI_INFORMATION11'
761 ,p_attribute11_value => p_rec.poei_information11
762 ,p_attribute12_name => 'POEI_INFORMATION12'
763 ,p_attribute12_value => p_rec.poei_information12
764 ,p_attribute13_name => 'POEI_INFORMATION13'
765 ,p_attribute13_value => p_rec.poei_information13
766 ,p_attribute14_name => 'POEI_INFORMATION14'
767 ,p_attribute14_value => p_rec.poei_information14
768 ,p_attribute15_name => 'POEI_INFORMATION15'
769 ,p_attribute15_value => p_rec.poei_information15
770 ,p_attribute16_name => 'POEI_INFORMATION16'
771 ,p_attribute16_value => p_rec.poei_information16
772 ,p_attribute17_name => 'POEI_INFORMATION17'
773 ,p_attribute17_value => p_rec.poei_information17
774 ,p_attribute18_name => 'POEI_INFORMATION18'
775 ,p_attribute18_value => p_rec.poei_information18
776 ,p_attribute19_name => 'POEI_INFORMATION19'
777 ,p_attribute19_value => p_rec.poei_information19
778 ,p_attribute20_name => 'POEI_INFORMATION20'
779 ,p_attribute20_value => p_rec.poei_information20
780 ,p_attribute21_name => 'POEI_INFORMATION21'
781 ,p_attribute21_value => p_rec.poei_information21
782 ,p_attribute22_name => 'POEI_INFORMATION22'
783 ,p_attribute22_value => p_rec.poei_information22
784 ,p_attribute23_name => 'POEI_INFORMATION23'
785 ,p_attribute23_value => p_rec.poei_information23
786 ,p_attribute24_name => 'POEI_INFORMATION24'
787 ,p_attribute24_value => p_rec.poei_information24
788 ,p_attribute25_name => 'POEI_INFORMATION25'
789 ,p_attribute25_value => p_rec.poei_information25
790 ,p_attribute26_name => 'POEI_INFORMATION26'
791 ,p_attribute26_value => p_rec.poei_information26
792 ,p_attribute27_name => 'POEI_INFORMATION27'
793 ,p_attribute27_value => p_rec.poei_information27
794 ,p_attribute28_name => 'POEI_INFORMATION28'
795 ,p_attribute28_value => p_rec.poei_information28
796 ,p_attribute29_name => 'POEI_INFORMATION29'
797 ,p_attribute29_value => p_rec.poei_information29
798 ,p_attribute30_name => 'POEI_INFORMATION30'
799 ,p_attribute30_value => p_rec.poei_information30
800 );
801 --
802 end if;
803 --
804 hr_utility.set_location(' Leaving:'||l_proc, 10);
805 end chk_ddf;
806 --
807 -- --------------------------------------------------------------------------------
808 -- |---------------------------< permit_extended_pay_poi >----------------------------|
809 -- --------------------------------------------------------------------------------
810 function permit_extended_pay_poi(p_rec in pe_poi_shd.g_rec_type) return boolean is
811 l_position_family varchar2(100);
812 l_chk boolean := false;
813 cursor c1 is
814 select poei_information3
815 from per_position_extra_info
816 where position_id = p_rec.position_id
817 and position_extra_info_id <> p_rec.position_extra_info_id
818 and information_type = 'PER_FAMILY'
819 and poei_information3 in ('ACADEMIC','FACULTY');
820 begin
821 if p_rec.position_id is not null then
822 open c1;
823 fetch c1 into l_position_family;
824 if c1%found then
825 close c1;
826 return true;
827 else
828 close c1;
829 if (p_rec.poei_information3 in ('ACADEMIC', 'FACULTY')) then
830 return true;
831 else
832 return false;
833 end if;
834 end if;
835 else
836 return(false);
837 end if;
838 end;
839
840 function permit_extended_pay(p_position_id varchar2) return boolean is
841 l_position_family varchar2(100);
842 l_chk boolean := false;
843 cursor c1 is
844 select poei_information3
845 from per_position_extra_info
846 where position_id = p_position_id
847 and information_type = 'PER_FAMILY'
848 and poei_information3 in ('ACADEMIC','FACULTY');
849 begin
850 if p_position_id is not null then
851 open c1;
852 fetch c1 into l_position_family;
853 if c1%found then
854 close c1;
855 return true;
856 else
857 close c1;
858 return false;
859 end if;
860 else
861 return(false);
862 end if;
863 end;
864 --
865 -- --------------------------------------------------------------------------------
866 -- |---------------------------< chk_position_family >----------------------------|
867 -- --------------------------------------------------------------------------------
868 procedure chk_position_family(p_work_period_type_cd varchar2, p_rec in pe_poi_shd.g_rec_type) is
869 begin
870 if ( nvl(p_work_period_type_cd,'N') = 'Y' ) then
871 if (not permit_extended_pay_poi(p_rec)) then
872 -- Cannot change Position Family of Position Extra Info
873 -- to Others if extended pay is permitted
874 hr_utility.set_message(800, 'HR_INV_POI_FAMILY');
875 hr_utility.raise_error;
876 end if;
877 end if;
878 end;
879 --
880 -- ----------------------------------------------------------------------------
881 -- |---------------------------< insert_validate >----------------------------|
882 -- ----------------------------------------------------------------------------
883 Procedure insert_validate(p_rec in pe_poi_shd.g_rec_type) is
884 --
885 l_proc varchar2(72) := g_package||'insert_validate';
886 l_position_rec hr_all_positions_f%rowtype;
887 l_position_start_date date;
888 l_effective_date date := trunc(sysdate);
889 --
890 cursor c_effective_date is
891 select effective_date
892 from fnd_sessions
893 where session_id = userenv('sessionid');
894 --
895 cursor c_position(p_position_id number, p_effective_date date) is
896 select *
897 from hr_all_positions_f
898 where position_id = p_position_id
899 and p_effective_date between effective_start_date and effective_end_date;
900 --
901 Begin
902 hr_utility.set_location('Entering:'||l_proc, 5);
903 --
904 open c_effective_date;
905 fetch c_effective_date into l_effective_date;
906 close c_effective_date;
907 --
908 open c_position(p_rec.position_id, l_effective_date);
909 fetch c_position into l_position_rec;
910 --
911 -- Validate Position Id
912 --
913 if c_position%notfound then
914 close c_position;
915 hr_utility.set_message(800, 'HR_INV_POS_ID');
916 hr_utility.raise_error;
917 end if;
918 --
919 close c_position;
920 --
921 /*
922 per_pos_bus.set_security_group_id
923 (p_position_id => p_rec.position_id
924 );
925 */
926 hr_psf_bus.set_security_group_id
927 (p_position_id => p_rec.position_id
928 );
929 --
930 hr_utility.set_location('Entering:'||l_proc, 7);
931 --
932 -- Validate Position Info Type
933 --
934 chk_position_info_type
935 (p_information_type => p_rec.information_type
936 );
937 /*
938 --
939 -- Validate Position ID
940 --
941 chk_position_id
942 (p_position_id => p_rec.position_id
943 );
944 */
945 --
946 -- Validate Multiple Occurence Flag
947 --
948 chk_multiple_occurences_flag
949 (p_information_type => p_rec.information_type
950 ,p_position_id => p_rec.position_id
951 );
952 --
953 -- Call ddf procedure to validation Developer Descriptive Flexfields
954 --
955 pe_poi_bus.chk_ddf(p_rec => p_rec);
956 --
957 -- Call df procedure to validate Descriptive Flexfields
958 --
959 pe_poi_bus.chk_df(p_rec => p_rec);
960 --
961 -- Validate Seasonal Dates for Position Extra Info
962 --
963 if (p_rec.information_type = 'PER_SEASONAL') then
964 if (nvl(l_position_rec.seasonal_flag,'N') = 'N' )then
965 -- Cannot add Seasonal dates to Position Extra Info if seasonal_flag<>'Y'
966 hr_utility.set_message(800, 'HR_INV_POI_SEASONAL');
967 hr_utility.raise_error;
968 end if;
969 if (fnd_date.canonical_to_date(p_rec.poei_information3)
970 > fnd_date.canonical_to_date(p_rec.poei_information4)) then
971 -- Seasonal end date should be later than seasonal start date
972 hr_utility.set_message(800, 'HR_INV_POI_SEASONAL_DATES');
973 hr_utility.raise_error;
974 end if;
975 end if;
976 --
977 -- Validate Overlap Dates for Position Extra Info
978 --
979 if (p_rec.information_type = 'PER_OVERLAP') then
980 if ( l_position_rec.overlap_period is null )then
981 -- Cannot add Overlap dates to Position Extra Info if overlap_period is null
982 hr_utility.set_message(800, 'HR_INV_POI_OVERLAP');
983 hr_utility.raise_error;
984 end if;
985 l_position_start_date := pqh_utility.position_start_date(p_rec.position_id);
986 if (fnd_date.canonical_to_date(p_rec.poei_information3) < l_position_start_date) then
987 -- Overlap start date should be greater than or equal to position start date
988 hr_utility.set_message(800, 'HR_INV_POI_OVERLAP_ST_DT');
989 hr_utility.set_message_token('POSITION_START_DATE', l_position_start_date);
990 hr_utility.raise_error;
991 end if;
992 if (fnd_date.canonical_to_date(p_rec.poei_information3)
993 > fnd_date.canonical_to_date(p_rec.poei_information4)) then
994 -- Overlap end date should be later than overlap start date
995 hr_utility.set_message(800, 'HR_INV_POI_OVERLAP_DATES');
996 hr_utility.raise_error;
997 end if;
998 end if;
999 --
1000 --
1001 -- Validate Reservation Info for PTX Extra Info
1002 --
1003 if (p_rec.information_type = 'PER_RESERVED') then
1004 l_position_start_date := pqh_utility.position_start_date(p_rec.position_id);
1005 if (fnd_date.canonical_to_date(p_rec.poei_information3) < l_position_start_date) then
1006 -- Reservation start date should be greater than or equal to position start date
1007 hr_utility.set_message(800, 'HR_INV_POI_RESERVED_ST_DT');
1008 hr_utility.set_message_token('POSITION_START_DATE', l_position_start_date);
1009 hr_utility.raise_error;
1010 end if;
1011 if (fnd_date.canonical_to_date(p_rec.poei_information3)
1012 > fnd_date.canonical_to_date(p_rec.poei_information4)) then
1013 -- Reservation end date should be later than reservation start date
1014 hr_utility.set_message(800, 'HR_INV_POI_RESERVED_DATES');
1015 hr_utility.raise_error;
1016 end if;
1017 if (p_rec.poei_information6 <= 0) then
1018 -- FTE should be greater than 0
1019 hr_utility.set_message(800, 'HR_INV_POI_RESERVED_FTE');
1020 hr_utility.raise_error;
1021 end if;
1022 --
1023 -- check whether reserved fte is available
1024 --
1025 pqh_psf_bus.pqh_poei_validate(p_rec.position_id,
1026 p_rec.position_extra_info_id, p_rec.poei_information5,
1027 fnd_date.canonical_to_date(p_rec.poei_information3),
1028 fnd_date.canonical_to_date(p_rec.poei_information4) , p_rec.poei_information6);
1029 end if;
1030 --
1031 hr_utility.set_location(' Leaving:'||l_proc, 30);
1032 --
1033 End insert_validate;
1034 --
1035 -- ----------------------------------------------------------------------------
1036 -- |---------------------------< update_validate >----------------------------|
1037 -- ----------------------------------------------------------------------------
1038 Procedure update_validate(p_rec in pe_poi_shd.g_rec_type) is
1039 --
1040 l_proc varchar2(72) := g_package||'update_validate';
1041 l_position_rec hr_all_positions_f%rowtype;
1042 l_position_start_date date;
1043 l_effective_date date := trunc(sysdate);
1044 --
1045 cursor c_effective_date is
1046 select effective_date
1047 from fnd_sessions
1048 where session_id = userenv('sessionid');
1049 --
1050 cursor c_position(p_position_id number, p_effective_date date) is
1051 select *
1052 from hr_all_positions_f
1053 where position_id = p_position_id
1054 and p_effective_date between effective_start_date and effective_end_date;
1055 --
1056 Begin
1057 hr_utility.set_location('Entering:'||l_proc, 5);
1058 --
1059 open c_effective_date;
1060 fetch c_effective_date into l_effective_date;
1061 close c_effective_date;
1062 --
1063 open c_position(p_rec.position_id, l_effective_date);
1064 fetch c_position into l_position_rec;
1065 close c_position;
1066 --
1067 /*
1068 per_pos_bus.set_security_group_id
1069 (p_position_id => p_rec.position_id
1070 );
1071 */
1072 hr_psf_bus.set_security_group_id
1073 (p_position_id => p_rec.position_id
1074 );
1075 --
1076 hr_utility.set_location('Entering:'||l_proc, 7);
1077 --
1078 -- Validate Non-Updateable Fields
1079 --
1080 chk_non_updateable_args (p_rec => p_rec);
1081 --
1082 -- Call ddf procedure to validation Developer Descriptive Flexfields
1083 --
1084 pe_poi_bus.chk_ddf(p_rec => p_rec);
1085 --
1086 -- Call df procedure to validation Descriptive Flexfields
1087 --
1088 pe_poi_bus.chk_df(p_rec => p_rec);
1089 --
1090 --
1091 if (p_rec.information_type = 'PER_SEASONAL') then
1092 if (nvl(l_position_rec.seasonal_flag,'N') = 'N' )then
1093 -- Cannot add Seasonal dates to Position Extra Info if seasonal_flag<>'Y'
1094 hr_utility.set_message(800, 'HR_INV_POI_SEASONAL');
1095 hr_utility.raise_error;
1096 end if;
1097 if (fnd_date.canonical_to_date(p_rec.poei_information3)
1098 > fnd_date.canonical_to_date(p_rec.poei_information4)) then
1099 -- Seasonal end date should be later than seasonal start date
1100 hr_utility.set_message(800, 'HR_INV_POI_SEASONAL_DATES');
1101 hr_utility.raise_error;
1102 end if;
1103 end if;
1104 --
1105 -- Validate Overlap Dates for Position Extra Info
1106 --
1107 if (p_rec.information_type = 'PER_OVERLAP') then
1108 if ( l_position_rec.overlap_period is null )then
1109 -- Cannot add Overlap dates to Position Extra Info if overlap_period is null
1110 hr_utility.set_message(800, 'HR_INV_POI_OVERLAP');
1111 hr_utility.raise_error;
1112 end if;
1113 l_position_start_date := pqh_utility.position_start_date(p_rec.position_id);
1114 if (fnd_date.canonical_to_date(p_rec.poei_information3) < l_position_start_date) then
1115 -- Overlap start date should be greater than or equal to position start date
1116 hr_utility.set_message(800, 'HR_INV_POI_OVERLAP_ST_DT');
1117 hr_utility.set_message_token('POSITION_START_DATE', l_position_start_date);
1118 hr_utility.raise_error;
1119 end if;
1120 if (fnd_date.canonical_to_date(p_rec.poei_information3)
1121 > fnd_date.canonical_to_date(p_rec.poei_information4)) then
1122 -- Overlap end date should be later than overlap start date
1123 hr_utility.set_message(800, 'HR_INV_POI_OVERLAP_DATES');
1124 hr_utility.raise_error;
1125 end if;
1126 end if;
1127 --
1128 if (p_rec.information_type = 'PER_RESERVED') then
1129 l_position_start_date := pqh_utility.position_start_date(p_rec.position_id);
1130 if (fnd_date.canonical_to_date(p_rec.poei_information3) < l_position_start_date) then
1131 -- Reservation start date should be greater than or equal to position start date
1132 hr_utility.set_message(800, 'HR_INV_POI_RESERVED_ST_DT');
1133 hr_utility.set_message_token('POSITION_START_DATE', l_position_start_date);
1134 hr_utility.raise_error;
1135 end if;
1136 if (fnd_date.canonical_to_date(p_rec.poei_information3)
1137 > fnd_date.canonical_to_date(p_rec.poei_information4)) then
1138 -- Reservation end date should be later than reservation start date
1139 hr_utility.set_message(800, 'HR_INV_POI_RESERVED_DATES');
1140 hr_utility.raise_error;
1141 end if;
1142 if (p_rec.poei_information6 <= 0) then
1143 -- FTE should be greater than 0
1144 hr_utility.set_message(800, 'HR_INV_POI_RESERVED_FTE');
1145 hr_utility.raise_error;
1146 end if;
1147 --
1148 -- check whether reserved fte is available
1149 --
1150 pqh_psf_bus.pqh_poei_validate(p_rec.position_id,
1151 p_rec.position_extra_info_id, p_rec.poei_information5,
1152 fnd_date.canonical_to_date(p_rec.poei_information3),
1153 fnd_date.canonical_to_date(p_rec.poei_information4) , p_rec.poei_information6);
1154 --
1155 elsif (p_rec.information_type = 'PER_FAMILY') then
1156 -- Validate Position Family for Position Extra Info
1157 chk_position_family(l_position_rec.work_period_type_cd, p_rec);
1158 end if;
1159 --
1160 hr_utility.set_location(' Leaving:'||l_proc, 30);
1161 --
1162 End update_validate;
1163 --
1164 -- ----------------------------------------------------------------------------
1165 -- |---------------------------< delete_validate >----------------------------|
1166 -- ----------------------------------------------------------------------------
1167 Procedure delete_validate(p_rec in pe_poi_shd.g_rec_type) is
1168 --
1169 l_proc varchar2(72) := g_package||'delete_validate';
1170 l_position_rec hr_all_positions_f%rowtype;
1171 l_effective_date date := trunc(sysdate);
1172 --
1173 cursor c_effective_date is
1174 select effective_date
1175 from fnd_sessions
1176 where session_id = userenv('sessionid');
1177 --
1178 cursor c_position(p_position_id number, p_effective_date date) is
1179 select *
1180 from hr_all_positions_f
1181 where position_id = p_position_id
1182 and p_effective_date between effective_start_date and effective_end_date;
1183 --
1184 Begin
1185 hr_utility.set_location('Entering:'||l_proc, 5);
1186 --
1187 open c_effective_date;
1188 fetch c_effective_date into l_effective_date;
1189 close c_effective_date;
1190 --
1191 --
1192 -- Call all supporting business operations
1193 --
1194 open c_position(p_rec.position_id, l_effective_date);
1195 fetch c_position into l_position_rec;
1196 close c_position;
1197 --
1198 -- Validate Position Family for Position Extra Info
1199 --
1200 if (p_rec.information_type = 'PER_FAMILY') then
1201 if (l_position_rec.work_period_type_cd = 'Y') then
1202 -- Cannot delete Position Family of Position Extra Info
1203 -- if extended pay is permitted
1204 hr_utility.set_message(800, 'HR_INV_POI_FAMILY_DEL');
1205 hr_utility.raise_error;
1206 end if;
1207 end if;
1208 hr_utility.set_location(' Leaving:'||l_proc, 10);
1209 --
1210 End delete_validate;
1211 --
1212 end pe_poi_bus;