[Home] [Help]
PACKAGE BODY: APPS.GHR_REI_BUS
Source
1 Package Body ghr_rei_bus as
2 /* $Header: ghreirhi.pkb 120.2.12010000.2 2008/09/02 07:19:59 vmididho ship $ */
3 -- ----------------------------------------------------------------------------
4 -- | Private Global Definitions |
5 -- ----------------------------------------------------------------------------
6 --
7 g_package varchar2(33) := ' ghr_rei_bus.'; -- Global package name
8 -- ----------------------------------------------------------------------------
9 -- |---------------------------< Chk_PA_REQUEST_id >----------------------------|
10 -- ----------------------------------------------------------------------------
11 -- {Start Of Comments}
12 --
13 -- Description:
14 -- Verify that the value in PA_REQUEST_ID is in the GHR_PA_REQUESTS table.
15 --
16 -- Pre Conditions:
17 --
18 --
19 -- In Parameters:
20 -- p_pa_request_id
21 --
22 -- Post Success:
23 -- Processing continues.
24 --
25 -- Post Failure:
26 -- An application error will be raised and processing is terminated.
27 --
28 -- Developer Implementation Notes:
29 -- For insert, your business rules should be executed from this procedure and
30 -- should ideally (unless really necessary) just be straight procedure or
31 -- function calls. Try and avoid using conditional branching logic.
32 --
33 -- Access Status:
34 -- Internal Table Handler Use Only.
35 --
36 -- {End Of Comments}
37 -- ----------------------------------------------------------------------------
38 Procedure chk_pa_request_id
39 (
40 p_pa_request_id in ghr_pa_request_extra_info.pa_request_id%type
41 ) is
42 --
43 l_proc varchar2(72) := g_package||'chk_pa_request_id';
44 l_dummy varchar2(1);
45 --
46 cursor c_valid_req is
47 select 'x'
48 from ghr_pa_requests
49 where pa_request_id = p_pa_request_id;
50 --
51 Begin
52 hr_utility.set_location('Entering:'||l_proc, 1);
53 --
54 -- Check mandatory parameters have been set
55 --
56 hr_api.mandatory_arg_error
57 (
58 p_api_name => l_proc,
59 p_argument => 'pa_request_id',
60 p_argument_value => p_pa_request_id
61 );
62 hr_utility.set_location(l_proc, 2);
63 --
64 -- Check that the pa_request_id is in the ghr_pa_requests table.
65 --
66 open c_valid_req;
67 fetch c_valid_req into l_dummy;
68 if c_valid_req%notfound then
69 close c_valid_req;
70 hr_utility.set_message(8301, 'HR_38119_INV_REQ_ID');
71 hr_utility.raise_error;
72 end if;
73 close c_valid_req;
74 --
75 hr_utility.set_location(' Leaving:'||l_proc, 3);
76 --
77 End chk_pa_request_id;
78 --
79 --
80 -- ----------------------------------------------------------------------------
81 -- |-----------------------< chk_non_updateable_args >------------------------|
82 -- ----------------------------------------------------------------------------
83 -- {Start Of Comments}
84 --
85 -- Description:
86 -- Verify that the non updateable arguments not changed.
87 -- For the PA_REQUEST_EXTRA_INFO table neither of the FK's can be updated
88 -- i.e. PA_REQUEST_ID and INFORMATION_TYPE
89 --
90 -- Pre Conditions:
91 -- None
92 --
93 -- In Parameters:
94 -- p_rec
95 --
96 -- Post Success:
97 -- Processing continues.
98 --
99 -- Post Failure:
100 -- An application error will be raised and processing is terminated.
101 --
102 -- Developer Implementation Notes:
103 -- None
104 --
105 -- Access Status:
106 -- Internal Row Handler Use Only.
107 --
108 -- {End Of Comments}
109 -- ----------------------------------------------------------------------------
110 --
111 Procedure chk_non_updateable_args(p_rec in ghr_rei_shd.g_rec_type) is
112 --
113 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
114 l_error exception;
115 l_argument varchar2(30);
116 --
117 Begin
118 --
119 hr_utility.set_location('Entering:'|| l_proc,10);
120 --
121 -- Only proceed with validation if a row exists for
122 -- the current record in the HR Schema.
123 if not ghr_rei_shd.api_updating
124 (p_pa_request_extra_info_id => p_rec.pa_request_extra_info_id
125 ,p_object_version_number => p_rec.object_version_number
126 ) then
127 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
128 hr_utility.set_message_token('PROCEDURE', l_proc);
129 hr_utility.set_message_token('STEP', '20');
130 end if;
131 --
132 hr_utility.set_location (l_proc, 30);
133 --
134 if nvl(p_rec.pa_request_id,hr_api.g_number)
135 <> nvl(ghr_rei_shd.g_old_rec.pa_request_id,hr_api.g_number) then
136 l_argument := 'pa_request_id';
137 raise l_error;
138 end if;
139 --
140 if nvl(p_rec.information_type,hr_api.g_varchar2)
141 <> nvl(ghr_rei_shd.g_old_rec.information_type,hr_api.g_varchar2) then
142 l_argument := 'information_type';
143 raise l_error;
144 end if;
145 --
146 hr_utility.set_location(' Leaving : '|| l_proc, 40);
147 --
148 exception
149 when l_error then
150 hr_api.argument_changed_error
151 (p_api_name => l_proc
152 ,p_argument => l_argument);
153 when others then
154 raise;
155 end chk_non_updateable_args;
156 --
157 --
158 --
159 --
160 -- ----------------------------------------------------------------------------
161 -- |------------------------< chk_pa_request_info_type >------------------------|
162 -- ----------------------------------------------------------------------------
163 Procedure chk_pa_request_info_type(p_information_type varchar2,
164 p_multiple_occurrences_flag out nocopy varchar2) is
165 -- ----------------------------------------------------------------------------
166 -- {Start Of Comments}
167 --
168 -- Description:
169 -- This procedure check number of rows against an info type
170 -- Information_type must exist with active_inactive_flag='Y',
171 -- FK GHR_PA_REQUEST_EXTRA_INFO_FK1, ensures the existence of row in info type table
172 -- but it should exist with active_inactive_flag = 'Y'
173 --
174 --
175 -- Pre Conditions:
176 -- This private procedure is called from insert/update_validate procedure.
177 --
178 -- In Parameters:
179 -- A Pl/Sql record structure, and multiple occurrence flag.
180 --
181 -- Post Success:
182 -- Processing continues.
183 --
184 -- Post Failure:
185 -- If a business rules fails the error will not be handled by this procedure
186 -- unless explicity coded.
187 --
188 -- Developer Implementation Notes:
189 -- None
190 --
191 -- Access Status:
192 -- Internal Table Handler Use Only.
193 --
194 -- {End Of Comments}
195 -- ----------------------------------------------------------------------------
196 l_proc varchar2(72) := g_package||'chk_pa_request_info_type';
197 l_active_inactive_flag GHR_PA_REQUEST_INFO_TYPES.ACTIVE_INACTIVE_FLAG%TYPE;
198 l_inactive_type exception;
199 --
200 CURSOR c_info_type IS
201 SELECT rit.multiple_occurrences_flag
202 ,rit.active_inactive_flag
203 FROM ghr_pa_request_info_types rit
204 WHERE rit.information_type = p_information_type
205 ;
206 --
207 Begin
208 hr_utility.set_location('Entering:'||l_proc, 5);
209 --
210 --
211 -- Check mandatory parameters have been set
212 --
213 hr_api.mandatory_arg_error
214 (
215 p_api_name => l_proc,
216 p_argument => 'information_type',
217 p_argument_value => p_information_type
218 );
219 open c_info_type;
220 fetch c_info_type into p_multiple_occurrences_flag, l_active_inactive_flag;
221 --
222 -- Check if there is any matching row for given info type
223 --
224 if c_info_type%NOTFOUND then
225 raise no_data_found;
226 end if;
227 --
228 -- Check if info type is active or not.
229 --
230 if l_active_inactive_flag = 'N' then
231 raise l_inactive_type;
232 end if;
233 --
234 close c_info_type;
235 --
236 hr_utility.set_location(' Leaving:'||l_proc, 10);
237 --
238 Exception
239 --
240 when l_inactive_type then
241 close c_info_type;
242 hr_utility.set_message(8301, 'HR_38120_INACTIVE_INFO_TYPE');
243 hr_utility.raise_error;
244 --
245 when no_data_found then
246 close c_info_type;
247 hr_utility.set_message(8301, 'HR_38121_INVALID_INFO_TYPE');
248 hr_utility.raise_error;
249 --
250 End chk_pa_request_info_type;
251 --
252 --
253 -- Ensures that number of rows should not exceed one,
254 -- if multiple_occurrences_flag='N'
255 --
256 -- ----------------------------------------------------------------------------
257 -- |----------------------------< chk_count_rows >----------------------------|
258 -- ----------------------------------------------------------------------------
259 Procedure chk_count_rows(p_information_type in varchar2
260 , p_pa_request_id in number
261 , p_multiple_occurrences_flag in varchar2
262 ) is
263 -- ----------------------------------------------------------------------------
264 -- {Start Of Comments}
265 --
266 -- Description:
267 -- This procedure check number of rows against an info type
268 --
269 -- Pre Conditions:
270 -- This private procedure is called from insert/update_validate procedure.
271 --
272 -- In Parameters:
273 -- A Pl/Sql record structure
274 --
275 -- Out Parameters
276 -- multiple occurrence flag
277 --
278 -- Post Success:
279 -- Processing continues.
280 --
281 -- Post Failure:
282 -- If a business rules fails the error will not be handled by this procedure
283 -- unless explicity coded.
284 --
285 -- Developer Implementation Notes:
286 -- None
287 --
288 -- Access Status:
289 -- Internal Table Handler Use Only.
290 --
291 -- {End Of Comments}
292 -- ----------------------------------------------------------------------------
293 --
294 l_proc varchar2(72) := g_package||'chk_count_rows';
295 l_dummy varchar2(1);
296 l_success exception;
297 l_failure exception;
298 --
299 CURSOR c_count_rows IS
300 SELECT 'x'
301 FROM ghr_pa_request_extra_info rei
302 WHERE rei.information_type = p_information_type
303 AND rei.pa_request_id = p_pa_request_id ;
304 --
305 Begin
306 hr_utility.set_location('Entering:'||l_proc, 5);
307 --
308 if p_multiple_occurrences_flag = 'N' then
309 --
310 -- Check mandatory parameters have been set
311 --
312 hr_api.mandatory_arg_error
313 (
314 p_api_name => l_proc,
315 p_argument => 'information_type',
316 p_argument_value => p_information_type
317 );
318 --
319 --
320 hr_api.mandatory_arg_error
321 (
322 p_api_name => l_proc,
323 p_argument => 'pa_request_id',
324 p_argument_value => p_pa_request_id
325 );
326 open c_count_rows;
327 fetch c_count_rows into l_dummy;
328 if c_count_rows%FOUND then
329 close c_count_rows;
330 raise l_failure;
331 else
332 close c_count_rows;
333 raise l_success;
334 end if;
335 end if;
336 --
337 hr_utility.set_location(' Leaving:'||l_proc, 10);
338 Exception
339 when l_success then
340 hr_utility.set_location(' Leaving:'||l_proc, 10);
341 when l_failure then
342 hr_utility.set_message(8301, 'GHR_38122_INFO_TYPE_ALLOWS_1_R');
343 hr_utility.raise_error;
344
345 End chk_count_rows;
346 -- -------------------------------------------------------------------------
347 -- |----------------------------- < chk_reason_for_submission > ------------|
348 -- -------------------------------------------------------------------------
349 --
350 procedure chk_reason_for_submission
351 (p_pa_request_extra_info_id in ghr_pa_request_extra_info.pa_request_extra_info_id%TYPE
352 ,p_reason_for_submission in ghr_pa_request_extra_info.rei_information3%TYPE
353 ,p_effective_date in date
354 ,p_object_version_number in number
355 ) is
356 --
357 l_proc varchar2(72) := 'chk_reason_for_submission';
358 l_api_updating boolean;
359 --
360 begin
361 hr_utility.set_location('Entering: '|| l_proc, 10);
362 --
363 -- Check mandatory parameters have been set
364 --
365 hr_api.mandatory_arg_error
366 (p_api_name => l_proc
367 ,p_argument => 'effective date'
368 ,p_argument_value => p_effective_date
369 );
370 --
371 -- Only proceed with validation if:
372 -- a) The current g_old_rec is current and
373 -- b) The reason_for_submission value has changed
374 -- c) A record is being inserted
375 --
376 l_api_updating := ghr_rei_shd.api_updating
377 (p_pa_request_extra_info_id => p_pa_request_extra_info_id
378 ,p_object_version_number => p_object_version_number
379 );
380 if ((l_api_updating and
381 nvl(ghr_rei_shd.g_old_rec.rei_information3,hr_api.g_varchar2)
382 <> nvl(p_reason_for_submission, hr_api.g_varchar2)) or
383 (not l_api_updating))
384 then
385 hr_utility.set_location(l_proc, 20);
386 --
387 -- If reason_for_submission is not null then
388 -- Check if the reason_for_submission value exists in hr_lookups
389 -- where the lookup_type is 'GHR_US_SUBMISSION_REASON'
390 --
391 if p_reason_for_submission is not null
392 then
393 if hr_api.not_exists_in_hr_lookups
394 (p_effective_date => p_effective_date
395 ,p_lookup_type => 'GHR_US_SUBMISSION_REASON'
396 ,p_lookup_code => p_reason_for_submission
397 )
398 then
399 -- Error: Invalid reason_for_submission
400 hr_utility.set_message(8301, 'GHR_REASON_FOR_SUB_INVALID');
401 hr_utility.raise_error;
402 end if;
403 hr_utility.set_location(l_proc, 30);
404 end if;
405 end if;
406 hr_utility.set_location(' Leavig:'|| l_proc, 40);
407 end chk_reason_for_submission;
408 --
409 -- ----------------------------------------------------------------------------------------------------
410 -- |------------------------------- < chk_explanation > ----------------------------------------------|
411 -- ----------------------------------------------------------------------------------------------------
412 --
413 procedure chk_explanation
414 (p_reason_for_submission in ghr_pa_request_extra_info.rei_information3%TYPE
415 ,p_pa_request_extra_info_id in ghr_pa_request_extra_info.pa_request_extra_info_id%TYPE
416 ,p_explanation in ghr_pa_request_extra_info.rei_information4%TYPE
417 ,p_object_version_number in number
418 ) is
419 --
420 l_proc varchar2(72) := 'chk_explanation';
421 l_api_updating boolean;
422 --
423 begin
424 hr_utility.set_location('Entering: '|| l_proc, 10);
425 --
426 -- Only proceed with validation if a record is being inserted
427 --
428 l_api_updating := ghr_rei_shd.api_updating
429 (p_pa_request_extra_info_id => p_pa_request_extra_info_id
430 ,p_object_version_number => p_object_version_number
431 );
432 IF (l_api_updating
433 AND
434 ((nvl(ghr_rei_shd.g_old_rec.rei_information3,hr_api.g_varchar2)
435 <> nvl(p_reason_for_submission,hr_api.g_varchar2)))
436 OR
437 ((nvl(ghr_rei_shd.g_old_rec.rei_information4,hr_api.g_varchar2)
438 <> nvl(p_explanation,hr_api.g_varchar2)))
439 )
440 OR
441 NOT l_api_updating THEN
442 /*
443 if ((l_api_updating and
444 nvl(ghr_rei_shd.g_old_rec.rei_information4,hr_api.g_varchar2)
445 <> nvl(p_explanation, hr_api.g_varchar2)) or
446 (not l_api_updating))
447 then
448 */
449 if (p_reason_for_submission = '4' and (p_explanation is null))
450 then
451 hr_utility.set_message(8301, 'GHR_NO_EXPLANATION');
452 hr_utility.raise_error;
453 end if;
454 end if;
455 hr_utility.set_location('Leaving: '|| l_proc, 20);
456 end chk_explanation;
457 --
458 -- ---------------------------------------------------------------------------------------------------
459 -- |-------------------------------- < chk_service > ------------------------------------------------|
460 -- ---------------------------------------------------------------------------------------------------
461 --
462 procedure chk_service
463 (p_pa_request_extra_info_id in ghr_pa_request_extra_info.pa_request_extra_info_id%TYPE
464 ,p_service in ghr_pa_request_extra_info.rei_information5%TYPE
465 ,p_effective_date in date
466 ,p_object_version_number in number
467 ) is
468 --
469 l_proc varchar2(72) := 'chk_service';
470 l_api_updating boolean;
471 --
472 begin
473 hr_utility.set_location('Entering: '|| l_proc, 10);
474 --
475 -- Check mandatory parameters have been set
476 --
477 hr_api.mandatory_arg_error
478 (p_api_name => l_proc
479 ,p_argument => 'service'
480 ,p_argument_value => p_service
481 );
482 --
483 -- Only proceed with validation if:
484 -- a) The current g_old_rec is current and
485 -- b) The service value has changed
486 -- c) A record is being inserted
487 --
488 l_api_updating := ghr_rei_shd.api_updating
489 (p_pa_request_extra_info_id => p_pa_request_extra_info_id
490 ,p_object_version_number => p_object_version_number
491 );
492 --
493 if ((l_api_updating and
494 nvl(ghr_rei_shd.g_old_rec.rei_information5,hr_api.g_varchar2)
495 <> nvl(p_service, hr_api.g_varchar2)) or
496 (not l_api_updating))
497 then
498 hr_utility.set_location(l_proc, 20);
499 --
500 -- If service is not null then
501 -- Check if the service value exists in hr_lookups
502 -- where the lookup type is 'GHR_US_SERVICE'
503 --
504 if p_service is not null
505 then
506 if hr_api.not_exists_in_hr_lookups
507 (p_effective_date => p_effective_date
508 ,P_lookup_type => 'GHR_US_SERVICE'
509 ,p_lookup_code => p_service
510 )
511 then
512 -- Error: Invalid Service
513 hr_utility.set_message(8301, 'GHR_SERVICE_INVALID');
514 hr_utility.raise_error;
515 end if;
516 hr_utility.set_location(l_proc, 30);
517 end if;
518 end if;
519 hr_utility.set_location(' Leaving:'|| l_proc, 40);
520 end chk_service;
521 --
522 -- ----------------------------------------------------------------------------
523 -- |---------------------------< Chk_ddf_for_866 >----------------------------|
524 -- ----------------------------------------------------------------------------
525 -- {Start Of Comments}
526 --
527 -- Description:
528 -- Verify that the retained grade rec in the EI for 866 (info type :'GHR_US_PAR_TERM_RET_GRADE' ),
529 -- is not already end dated.
530 -- We could not do this in the value set because of cancellation action which require
531 -- even end dated actions to be viewed by the user.
532 --
533 -- Pre Conditions:
534 --
535 --
536 -- In Parameters:
537 -- p_rei_information3
538 --
539 -- Post Success:
540 -- Processing continues.
541 --
542 -- Post Failure:
543 -- An application error will be raised and processing is terminated.
544 --
545 -- Developer Implementation Notes:
546 --
547 -- Access Status:
548 -- Internal Table Handler Use Only.
549 --
550 -- {End Of Comments}
551 -- ----------------------------------------------------------------------------
552 Procedure chk_ddf_for_866
553 (p_rec in ghr_rei_shd.g_rec_type
554 ) is
555
556 l_proc varchar2(72) := g_package||'chk_ddf_for_866';
557 l_first_noa_code ghr_pa_requests.first_noa_code%type;
558 l_effective_date date;
559
560 Cursor c_date_end is
561 Select pei_information2
562 From per_people_extra_info
563 Where person_extra_info_id = p_rec.rei_information3;
564
565 Cursor c_find_noa_code is
566 Select first_noa_code,effective_date
567 From ghr_pa_requests
568 Where pa_request_id = p_rec.pa_request_id;
569
570 Begin
571
572 hr_utility.set_location('Entering:'||l_proc, 5);
573
574 for find_noa_code in c_find_noa_code loop
575 l_first_noa_code := find_noa_code.first_noa_code;
576 l_effective_date := nvl(find_noa_code.effective_date,sysdate);
577 end loop;
578
579 if l_first_noa_code not in ('001', '002') then
580 If p_rec.information_type = 'GHR_US_PAR_TERM_RET_GRADE' then
581 for date_end in C_date_end loop
582 If nvl(fnd_date.canonical_to_date(date_end.pei_information2),l_effective_date)
583 < l_effective_date then
584 hr_utility.set_message(8301,'GHR_38500_REC_ALREADY_ENDED');
585 hr_utility.raise_error;
586 End if;
587 End loop;
588 End if;
589 End if;
590
591 hr_utility.set_location('Leaving:'||l_proc, 10);
592
593 End chk_ddf_for_866;
594
595 -- ----------------------------------------------------------------------------
596 -- |---------------------------< Chk_ddf_for_temp_promo >----------------------------|
597 -- ----------------------------------------------------------------------------
598 -- {Start Of Comments}
599 --
600 -- Description:
601 -- Verify that the retained grade rec in the EI for 866 and 703
602 -- (info type :'GHR_US_PAR_RG_TEMP_PROMO' ) should not have TPS value
603 -- If there is no active Retained Grade record for the employee
604 --
605 -- Pre Conditions:
606 --
607 --
608 -- In Parameters:
609 -- p_rec
610 --
611 -- Post Success:
612 -- Processing continues.
613 --
614 -- Post Failure:
615 -- An application error will be raised and processing is terminated.
616 --
617 -- Developer Implementation Notes:
618 --
619 -- Access Status:
620 -- Internal Table Handler Use Only.
621 --
622 -- {End Of Comments}
623 -- ----------------------------------------------------------------------------
624 Procedure chk_ddf_for_temp_promo
625 (p_rec in ghr_rei_shd.g_rec_type
626 ) is
627
628 l_proc varchar2(72) := g_package||'chk_ddf_for_temp_promo';
629 l_first_noa_code ghr_pa_requests.first_noa_code%type;
630 l_effective_date date;
631 l_person_id ghr_pa_requests.person_id%type;
632
633
634 Cursor c_find_noa_code is
635 Select first_noa_code,effective_date,person_id
636 From ghr_pa_requests
637 Where pa_request_id = p_rec.pa_request_id;
638
639 l_retained_grade ghr_pay_calc.retained_grade_rec_type;
640 Begin
641 hr_utility.set_location('Entering:'||l_proc, 5);
642
643 for find_noa_code in c_find_noa_code loop
644 l_first_noa_code := find_noa_code.first_noa_code;
645 l_effective_date := nvl(find_noa_code.effective_date,sysdate);
646 l_person_id := find_noa_code.person_id;
647 end loop;
648 IF l_person_id is not null AND
649 l_effective_date is NOT NULL AND
650 l_first_noa_code in ('866','703') AND
651 p_rec.information_type = 'GHR_US_PAR_RG_TEMP_PROMO' THEN
652 BEGIN
653 l_retained_grade := ghr_pc_basic_pay.get_retained_grade_details
654 (l_person_id
655 ,l_effective_date
656 ,p_rec.pa_request_id);
657 EXCEPTION WHEN OTHERS THEN
658 IF p_rec.rei_information3 is NOT NULL THEN
659 hr_utility.set_message(8301,'GHR_38823_TEMP_PROMO_NO_RG');
660 hr_utility.raise_error;
661 ELSE
662 NULL;
663 END IF;
664 END;
665 END IF;
666 hr_utility.set_location('Leaving:'||l_proc, 10);
667
668 End chk_ddf_for_temp_promo;
669 --
670 -- ----------------------------------------------------------------------------
671 -- |---------------------------< insert_validate >----------------------------|
672 -- ----------------------------------------------------------------------------
673 Procedure insert_validate(p_rec in ghr_rei_shd.g_rec_type) is
674 --
675 l_proc varchar2(72) := g_package||'insert_validate';
676 l_multiple_occurrences_flag ghr_pa_request_info_types.multiple_occurrences_flag%type;
677 --
678 l_person_id per_people_f.person_id%type;
679 l_position_id per_positions.position_id%type;
680 l_bus_group_id per_people_f.business_group_id%type;
681
682 -- Bug#5729582 (i)added local variable l_effective_date
683 -- (ii) Modified the cursors c_get_perpos_ids,c_per_bus_group_id,c_pos_bus_group_id
684 l_effective_date ghr_pa_requests.effective_date%type;
685
686 cursor c_get_perpos_ids is
687 select par.person_id,nvl(par.from_position_id,par.to_position_id) position_id,
688 par.effective_date
689 from ghr_pa_requests par
690 where pa_request_id = p_rec.pa_request_id;
691 cursor c_per_bus_group_id(p_person_id in per_people_f.person_id%TYPE) is
692 select ppf.business_group_id
693 from per_people_f ppf
694 where ppf.person_id = p_person_id
695 and l_effective_date between ppf.effective_start_date
696 and ppf.effective_end_date;
697 cursor c_pos_bus_group_id(p_position_id in per_positions.position_id%TYPE ) is
698 select pos.business_group_id
699 from hr_all_positions_f pos -- Venkat -- Position DT
700 where pos.position_id = p_position_id
701 and l_effective_date between pos.effective_start_date
702 and pos.effective_end_date;
703 Begin
704 hr_utility.set_location('Entering:'||l_proc, 5);
705 --
706 hr_utility.set_location('Getting Ids',1);
707 -- Getting person_id,position_id
708 for c_get_perpos_rec in c_get_perpos_ids
709 loop
710 l_person_id := c_get_perpos_rec.person_id;
711 l_position_id := c_get_perpos_rec.position_id;
712 --Bug#5729582
713 -- Bug# 6215050 added NVL as it is raising an error saving to inbox with
714 -- out giving effective date
715 l_effective_date := nvl(c_get_perpos_rec.effective_date,sysdate);
716 exit;
717 end loop;
718 hr_utility.set_location('Person Id '||to_char(l_person_id),2);
719 hr_utility.set_location('Position Id '||to_char(l_position_id),2);
720 --
721 --
722 -- Set up the CLIENT_INFO
723 --
724 ghr_utility.set_client_info(p_person_id =>l_person_id,
725 p_position_id =>l_position_id);
726 --
727 -- Call all supporting business operations
728 --
729 --
730 -- 1) Call chk_pa_request_id to validate oa_request_id
731 chk_pa_request_id( p_pa_request_id => p_rec.pa_request_id );
732 --
733 -- 2) Call info_type procedure to validate info_type
734 --
735 chk_pa_request_info_type(p_information_type => p_rec.information_type
736 ,p_multiple_occurrences_flag => l_multiple_occurrences_flag);
737 --
738 --
739 -- 3) Call count_rows procedure to allow/disallow inserts in extra_info
740 chk_count_rows(p_information_type => p_rec.information_type
741 ,p_pa_request_id => p_rec.pa_request_id
742 ,p_multiple_occurrences_flag => l_multiple_occurrences_flag
743 );
744 --
745 --4)
746 chk_ddf_for_866(p_rec);
747
748 --5)
749 chk_ddf_for_temp_promo(p_rec);
750 --
751 -- Extra DDF Validation for PD Employee information
752 ghr_rei_bus.chk_ddf_extra_val(p_rec) ;
753 -- Call df procedure to validate Descritive Flex Fields
754 /*
755 ghr_rei_flex.df(p_rec) ;
756 */
757 hr_dflex_utility.ins_or_upd_descflex_attribs
758 (p_appl_short_name => 'GHR'
759 ,p_descflex_name => 'GHR_PA_REQUEST_EXTRA_INFO'
760 ,p_attribute_category => p_rec.rei_attribute_category
761 ,p_attribute1_name => 'REI_ATTRIBUTE1'
762 ,p_attribute1_value => p_rec.REI_ATTRIBUTE1
763 ,p_attribute2_name => 'REI_ATTRIBUTE2'
764 ,p_attribute2_value => p_rec.REI_ATTRIBUTE2
765 ,p_attribute3_name => 'REI_ATTRIBUTE3'
766 ,p_attribute3_value => p_rec.REI_ATTRIBUTE3
767 ,p_attribute4_name => 'REI_ATTRIBUTE4'
768 ,p_attribute4_value => p_rec.REI_ATTRIBUTE4
769 ,p_attribute5_name => 'REI_ATTRIBUTE5'
770 ,p_attribute5_value => p_rec.REI_ATTRIBUTE5
771 ,p_attribute6_name => 'REI_ATTRIBUTE6'
772 ,p_attribute6_value => p_rec.REI_ATTRIBUTE6
773 ,p_attribute7_name => 'REI_ATTRIBUTE7'
774 ,p_attribute7_value => p_rec.REI_ATTRIBUTE7
775 ,p_attribute8_name => 'REI_ATTRIBUTE8'
776 ,p_attribute8_value => p_rec.REI_ATTRIBUTE8
777 ,p_attribute9_name => 'REI_ATTRIBUTE9'
778 ,p_attribute9_value => p_rec.REI_ATTRIBUTE9
779 ,p_attribute10_name => 'REI_ATTRIBUTE10'
780 ,p_attribute10_value => p_rec.REI_ATTRIBUTE10
781 ,p_attribute11_name => 'REI_ATTRIBUTE11'
782 ,p_attribute11_value => p_rec.REI_ATTRIBUTE11
783 ,p_attribute12_name => 'REI_ATTRIBUTE12'
784 ,p_attribute12_value => p_rec.REI_ATTRIBUTE12
785 ,p_attribute13_name => 'REI_ATTRIBUTE13'
786 ,p_attribute13_value => p_rec.REI_ATTRIBUTE13
787 ,p_attribute14_name => 'REI_ATTRIBUTE14'
788 ,p_attribute14_value => p_rec.REI_ATTRIBUTE14
789 ,p_attribute15_name => 'REI_ATTRIBUTE15'
790 ,p_attribute15_value => p_rec.REI_ATTRIBUTE15
791 ,p_attribute16_name => 'REI_ATTRIBUTE16'
792 ,p_attribute16_value => p_rec.REI_ATTRIBUTE16
793 ,p_attribute17_name => 'REI_ATTRIBUTE17'
794 ,p_attribute17_value => p_rec.REI_ATTRIBUTE17
795 ,p_attribute18_name => 'REI_ATTRIBUTE18'
796 ,p_attribute18_value => p_rec.REI_ATTRIBUTE18
797 ,p_attribute19_name => 'REI_ATTRIBUTE19'
798 ,p_attribute19_value => p_rec.REI_ATTRIBUTE19
799 ,p_attribute20_name => 'REI_ATTRIBUTE20'
800 ,p_attribute20_value => p_rec.REI_ATTRIBUTE20
801 );
802 --
803 hr_utility.set_location(l_proc, 10);
804 --
805 --
806 --
807 hr_utility.set_location(l_proc, 15);
808 -- Call ddf procedure to validate Developer Descritive Flex Fields
809 --
810 /*
811 ghr_rei_flex_ddf.ddf(p_rec) ;
812 */
813 -- Business group Id has to be populated for the server side DDF Validation
814 -- Get the Business Group Id as follows
815 -- Get the person_id/position_id from ghr_pa_requests using pa_request_id
816 -- With person_id/position_id get the business group id form per_peope_f/per_positions
817 -- Set the business group_id using fnd_profile.put
818 -- If there is no person_id or position_id in ghr_pa_requests do not validate using dflex -- ??
819
820 --Getting Business Group Id
821 if l_person_id is not null then
822 for c_per_bus_rec in c_per_bus_group_id(l_person_id)
823 loop
824 l_bus_group_id := c_per_bus_rec.business_group_id;
825 exit;
826 end loop;
827 end if;
828 if l_person_id is null then
829 if l_position_id is not null then
830 for c_pos_bus_rec in c_pos_bus_group_id(l_position_id)
831 loop
832 l_bus_group_id := c_pos_bus_rec.business_group_id;
833 exit;
834 end loop;
835 end if;
836 end if;
837 if l_person_id is not null or l_position_id is not null then
838 --Putting the BUSINESS GROUP_ID
839 hr_utility.set_location('BG ID '||l_bus_group_id,4);
840 fnd_profile.put('PER_BUSINESS_GROUP_ID',l_bus_group_id);
841 hr_dflex_utility.ins_or_upd_descflex_attribs
842 (p_appl_short_name => 'GHR'
843 ,p_descflex_name => 'Extra PA Request Info DDF'
844 ,p_attribute_category => p_rec.rei_information_category
845 ,p_attribute1_name => 'REI_INFORMATION1'
846 ,p_attribute1_value => p_rec.REI_INFORMATION1
847 ,p_attribute2_name => 'REI_INFORMATION2'
848 ,p_attribute2_value => p_rec.REI_INFORMATION2
849 ,p_attribute3_name => 'REI_INFORMATION3'
850 ,p_attribute3_value => p_rec.REI_INFORMATION3
851 ,p_attribute4_name => 'REI_INFORMATION4'
852 ,p_attribute4_value => p_rec.REI_INFORMATION4
853 ,p_attribute5_name => 'REI_INFORMATION5'
854 ,p_attribute5_value => p_rec.REI_INFORMATION5
855 ,p_attribute6_name => 'REI_INFORMATION6'
856 ,p_attribute6_value => p_rec.REI_INFORMATION6
857 ,p_attribute7_name => 'REI_INFORMATION7'
858 ,p_attribute7_value => p_rec.REI_INFORMATION7
859 ,p_attribute8_name => 'REI_INFORMATION8'
860 ,p_attribute8_value => p_rec.REI_INFORMATION8
861 ,p_attribute9_name => 'REI_INFORMATION9'
862 ,p_attribute9_value => p_rec.REI_INFORMATION9
863 ,p_attribute10_name => 'REI_INFORMATION10'
864 ,p_attribute10_value => p_rec.REI_INFORMATION10
865 ,p_attribute11_name => 'REI_INFORMATION11'
866 ,p_attribute11_value => p_rec.REI_INFORMATION11
867 ,p_attribute12_name => 'REI_INFORMATION12'
868 ,p_attribute12_value => p_rec.REI_INFORMATION12
869 ,p_attribute13_name => 'REI_INFORMATION13'
870 ,p_attribute13_value => p_rec.REI_INFORMATION13
871 ,p_attribute14_name => 'REI_INFORMATION14'
872 ,p_attribute14_value => p_rec.REI_INFORMATION14
873 ,p_attribute15_name => 'REI_INFORMATION15'
874 ,p_attribute15_value => p_rec.REI_INFORMATION15
875 ,p_attribute16_name => 'REI_INFORMATION16'
876 ,p_attribute16_value => p_rec.REI_INFORMATION16
877 ,p_attribute17_name => 'REI_INFORMATION17'
878 ,p_attribute17_value => p_rec.REI_INFORMATION17
879 ,p_attribute18_name => 'REI_INFORMATION18'
880 ,p_attribute18_value => p_rec.REI_INFORMATION18
881 ,p_attribute19_name => 'REI_INFORMATION19'
882 ,p_attribute19_value => p_rec.REI_INFORMATION19
883 ,p_attribute20_name => 'REI_INFORMATION20'
884 ,p_attribute20_value => p_rec.REI_INFORMATION20
885 ,p_attribute21_name => 'REI_INFORMATION21'
886 ,p_attribute21_value => p_rec.REI_INFORMATION21
887 ,p_attribute22_name => 'REI_INFORMATION22'
888 ,p_attribute22_value => p_rec.REI_INFORMATION22
889 ,p_attribute23_name => 'REI_INFORMATION23'
890 ,p_attribute23_value => p_rec.REI_INFORMATION23
891 ,p_attribute24_name => 'REI_INFORMATION24'
892 ,p_attribute24_value => p_rec.REI_INFORMATION24
893 ,p_attribute25_name => 'REI_INFORMATION25'
894 ,p_attribute25_value => p_rec.REI_INFORMATION25
895 ,p_attribute26_name => 'REI_INFORMATION26'
896 ,p_attribute26_value => p_rec.REI_INFORMATION26
897 ,p_attribute27_name => 'REI_INFORMATION27'
898 ,p_attribute27_value => p_rec.REI_INFORMATION27
899 ,p_attribute28_name => 'REI_INFORMATION28'
900 ,p_attribute28_value => p_rec.REI_INFORMATION28
901 ,p_attribute29_name => 'REI_INFORMATION29'
902 ,p_attribute29_value => p_rec.REI_INFORMATION29
903 ,p_attribute30_name => 'REI_INFORMATION30'
904 ,p_attribute30_value => p_rec.REI_INFORMATION30
905 );
906 end if;
907 --
908 --
909 --
910 hr_utility.set_location(' Leaving:'||l_proc, 10);
911 End insert_validate;
912 --
913 -- ----------------------------------------------------------------------------
914 -- |---------------------------< update_validate >----------------------------|
915 -- ----------------------------------------------------------------------------
916 Procedure update_validate(p_rec in ghr_rei_shd.g_rec_type) is
917 --
918 l_proc varchar2(72) := g_package||'update_validate';
919 --
920 l_person_id per_people_f.person_id%type;
921 l_position_id per_positions.position_id%type;
922 l_bus_group_id per_people_f.business_group_id%type;
923
924 -- Bug#5729582 (i)added local variable l_effective_date
925 -- (ii) Modified the cursors c_get_perpos_ids,c_per_bus_group_id,c_pos_bus_group_id
926 l_effective_date ghr_pa_requests.effective_date%type;
927
928 cursor c_get_perpos_ids is
929 select par.person_id,nvl(par.from_position_id,par.to_position_id) position_id,
930 par.effective_date
931 from ghr_pa_requests par
932 where pa_request_id = p_rec.pa_request_id;
933 cursor c_per_bus_group_id(p_person_id in per_people_f.person_id%TYPE) is
934 select ppf.business_group_id
935 from per_people_f ppf
936 where ppf.person_id = p_person_id
937 and l_effective_date between ppf.effective_start_date
938 and ppf.effective_end_date;
939 cursor c_pos_bus_group_id(p_position_id in per_positions.position_id%TYPE ) is
940 select pos.business_group_id
941 from hr_all_positions_f pos -- Venkat -- Position DT
942 where pos.position_id = p_position_id
943 and l_effective_date between pos.effective_start_date
944 and pos.effective_end_date;
945 Begin
946 hr_utility.set_location('Entering:'||l_proc, 5);
947 --
948 hr_utility.set_location('Getting Ids',1);
949 -- Getting person_id,position_id
950 for c_get_perpos_rec in c_get_perpos_ids
951 loop
952 l_person_id := c_get_perpos_rec.person_id;
953 l_position_id := c_get_perpos_rec.position_id;
954 --Bug#5729582
955 -- Bug#6215050 added NVL to consider sysdate if effective date is NULL
956 l_effective_date := NVL(c_get_perpos_rec.effective_date,sysdate);
957 exit;
958 end loop;
959 hr_utility.set_location('Person Id '||to_char(l_person_id),2);
960 hr_utility.set_location('Position Id '||to_char(l_position_id),2);
961 --
962 --
963 -- Set up the CLIENT_INFO
964 --
965 ghr_utility.set_client_info(p_person_id =>l_person_id,
966 p_position_id =>l_position_id);
967 -- Call all supporting business operations
968 --
969 chk_ddf_for_866(p_rec);
970 --
971 chk_ddf_for_temp_promo(p_rec);
972 --
973 -- 2) Check those columns which cannot be updated have not changed.
974 --
975 hr_utility.set_location(l_proc, 10);
976 --
977 chk_non_updateable_args (p_rec => p_rec);
978 -- Extra DDF Validation for PD Employee information
979 ghr_rei_bus.chk_ddf_extra_val(p_rec) ;
980 --
981 -- Call df procedure to validate Descritive Flex Fields
982 --
983 /*
984 ghr_rei_flex.df(p_rec) ;
985 */
986 hr_dflex_utility.ins_or_upd_descflex_attribs
987 (p_appl_short_name => 'GHR'
988 ,p_descflex_name => 'GHR_PA_REQUEST_EXTRA_INFO'
989 ,p_attribute_category => p_rec.rei_attribute_category
990 ,p_attribute1_name => 'REI_ATTRIBUTE1'
991 ,p_attribute1_value => p_rec.REI_ATTRIBUTE1
992 ,p_attribute2_name => 'REI_ATTRIBUTE2'
993 ,p_attribute2_value => p_rec.REI_ATTRIBUTE2
994 ,p_attribute3_name => 'REI_ATTRIBUTE3'
995 ,p_attribute3_value => p_rec.REI_ATTRIBUTE3
996 ,p_attribute4_name => 'REI_ATTRIBUTE4'
997 ,p_attribute4_value => p_rec.REI_ATTRIBUTE4
998 ,p_attribute5_name => 'REI_ATTRIBUTE5'
999 ,p_attribute5_value => p_rec.REI_ATTRIBUTE5
1000 ,p_attribute6_name => 'REI_ATTRIBUTE6'
1001 ,p_attribute6_value => p_rec.REI_ATTRIBUTE6
1002 ,p_attribute7_name => 'REI_ATTRIBUTE7'
1003 ,p_attribute7_value => p_rec.REI_ATTRIBUTE7
1004 ,p_attribute8_name => 'REI_ATTRIBUTE8'
1005 ,p_attribute8_value => p_rec.REI_ATTRIBUTE8
1006 ,p_attribute9_name => 'REI_ATTRIBUTE9'
1007 ,p_attribute9_value => p_rec.REI_ATTRIBUTE9
1008 ,p_attribute10_name => 'REI_ATTRIBUTE10'
1009 ,p_attribute10_value => p_rec.REI_ATTRIBUTE10
1010 ,p_attribute11_name => 'REI_ATTRIBUTE11'
1011 ,p_attribute11_value => p_rec.REI_ATTRIBUTE11
1012 ,p_attribute12_name => 'REI_ATTRIBUTE12'
1013 ,p_attribute12_value => p_rec.REI_ATTRIBUTE12
1014 ,p_attribute13_name => 'REI_ATTRIBUTE13'
1015 ,p_attribute13_value => p_rec.REI_ATTRIBUTE13
1016 ,p_attribute14_name => 'REI_ATTRIBUTE14'
1017 ,p_attribute14_value => p_rec.REI_ATTRIBUTE14
1018 ,p_attribute15_name => 'REI_ATTRIBUTE15'
1019 ,p_attribute15_value => p_rec.REI_ATTRIBUTE15
1020 ,p_attribute16_name => 'REI_ATTRIBUTE16'
1021 ,p_attribute16_value => p_rec.REI_ATTRIBUTE16
1022 ,p_attribute17_name => 'REI_ATTRIBUTE17'
1023 ,p_attribute17_value => p_rec.REI_ATTRIBUTE17
1024 ,p_attribute18_name => 'REI_ATTRIBUTE18'
1025 ,p_attribute18_value => p_rec.REI_ATTRIBUTE18
1026 ,p_attribute19_name => 'REI_ATTRIBUTE19'
1027 ,p_attribute19_value => p_rec.REI_ATTRIBUTE19
1028 ,p_attribute20_name => 'REI_ATTRIBUTE20'
1029 ,p_attribute20_value => p_rec.REI_ATTRIBUTE20
1030 );
1031 --
1032 hr_utility.set_location(l_proc, 15);
1033 --
1034 --
1035 --
1036 hr_utility.set_location(l_proc, 20);
1037 --
1038 -- Call ddf procedure to validate Developer Descritive Flex Fields
1039 --
1040 /*
1041 ghr_rei_flex_ddf.ddf(p_rec) ;
1042 */
1043 -- Business group Id has to be populated for the server side DDF Validation
1044 -- Get the Business Group Id as follows
1045 -- Get the person_id/position_id from ghr_pa_requests using pa_request_id
1046 -- With person_id/position_id get the business group id form per_peope_f/per_positions
1047 -- Set the business group_id using fnd_profile.put
1048 -- If there is no person_id or position_id in ghr_pa_requests do not validate using dflex -- ??
1049
1050 --Getting Business Group Id
1051 if l_person_id is not null then
1052 for c_per_bus_rec in c_per_bus_group_id(l_person_id)
1053 loop
1054 l_bus_group_id := c_per_bus_rec.business_group_id;
1055 exit;
1056 end loop;
1057 end if;
1058 if l_person_id is null then
1059 if l_position_id is not null then
1060 for c_pos_bus_rec in c_pos_bus_group_id(l_position_id)
1061 loop
1062 l_bus_group_id := c_pos_bus_rec.business_group_id;
1063 exit;
1064 end loop;
1065 end if;
1066 end if;
1067 if l_person_id is not null or l_position_id is not null then
1068 --Putting the BUSINESS GROUP_ID
1069 hr_utility.set_location('BG ID '||l_bus_group_id,4);
1070 fnd_profile.put('PER_BUSINESS_GROUP_ID',l_bus_group_id);
1071 hr_dflex_utility.ins_or_upd_descflex_attribs
1072 (p_appl_short_name => 'GHR'
1073 ,p_descflex_name => 'Extra PA Request Info DDF'
1074 ,p_attribute_category => p_rec.rei_information_category
1075 ,p_attribute1_name => 'REI_INFORMATION1'
1076 ,p_attribute1_value => p_rec.REI_INFORMATION1
1077 ,p_attribute2_name => 'REI_INFORMATION2'
1078 ,p_attribute2_value => p_rec.REI_INFORMATION2
1079 ,p_attribute3_name => 'REI_INFORMATION3'
1080 ,p_attribute3_value => p_rec.REI_INFORMATION3
1081 ,p_attribute4_name => 'REI_INFORMATION4'
1082 ,p_attribute4_value => p_rec.REI_INFORMATION4
1083 ,p_attribute5_name => 'REI_INFORMATION5'
1084 ,p_attribute5_value => p_rec.REI_INFORMATION5
1085 ,p_attribute6_name => 'REI_INFORMATION6'
1086 ,p_attribute6_value => p_rec.REI_INFORMATION6
1087 ,p_attribute7_name => 'REI_INFORMATION7'
1088 ,p_attribute7_value => p_rec.REI_INFORMATION7
1089 ,p_attribute8_name => 'REI_INFORMATION8'
1090 ,p_attribute8_value => p_rec.REI_INFORMATION8
1091 ,p_attribute9_name => 'REI_INFORMATION9'
1092 ,p_attribute9_value => p_rec.REI_INFORMATION9
1093 ,p_attribute10_name => 'REI_INFORMATION10'
1094 ,p_attribute10_value => p_rec.REI_INFORMATION10
1095 ,p_attribute11_name => 'REI_INFORMATION11'
1096 ,p_attribute11_value => p_rec.REI_INFORMATION11
1097 ,p_attribute12_name => 'REI_INFORMATION12'
1098 ,p_attribute12_value => p_rec.REI_INFORMATION12
1099 ,p_attribute13_name => 'REI_INFORMATION13'
1100 ,p_attribute13_value => p_rec.REI_INFORMATION13
1101 ,p_attribute14_name => 'REI_INFORMATION14'
1102 ,p_attribute14_value => p_rec.REI_INFORMATION14
1103 ,p_attribute15_name => 'REI_INFORMATION15'
1104 ,p_attribute15_value => p_rec.REI_INFORMATION15
1105 ,p_attribute16_name => 'REI_INFORMATION16'
1106 ,p_attribute16_value => p_rec.REI_INFORMATION16
1107 ,p_attribute17_name => 'REI_INFORMATION17'
1108 ,p_attribute17_value => p_rec.REI_INFORMATION17
1109 ,p_attribute18_name => 'REI_INFORMATION18'
1110 ,p_attribute18_value => p_rec.REI_INFORMATION18
1111 ,p_attribute19_name => 'REI_INFORMATION19'
1112 ,p_attribute19_value => p_rec.REI_INFORMATION19
1113 ,p_attribute20_name => 'REI_INFORMATION20'
1114 ,p_attribute20_value => p_rec.REI_INFORMATION20
1115 ,p_attribute21_name => 'REI_INFORMATION21'
1116 ,p_attribute21_value => p_rec.REI_INFORMATION21
1117 ,p_attribute22_name => 'REI_INFORMATION22'
1118 ,p_attribute22_value => p_rec.REI_INFORMATION22
1119 ,p_attribute23_name => 'REI_INFORMATION23'
1120 ,p_attribute23_value => p_rec.REI_INFORMATION23
1121 ,p_attribute24_name => 'REI_INFORMATION24'
1122 ,p_attribute24_value => p_rec.REI_INFORMATION24
1123 ,p_attribute25_name => 'REI_INFORMATION25'
1124 ,p_attribute25_value => p_rec.REI_INFORMATION25
1125 ,p_attribute26_name => 'REI_INFORMATION26'
1126 ,p_attribute26_value => p_rec.REI_INFORMATION26
1127 ,p_attribute27_name => 'REI_INFORMATION27'
1128 ,p_attribute27_value => p_rec.REI_INFORMATION27
1129 ,p_attribute28_name => 'REI_INFORMATION28'
1130 ,p_attribute28_value => p_rec.REI_INFORMATION28
1131 ,p_attribute29_name => 'REI_INFORMATION29'
1132 ,p_attribute29_value => p_rec.REI_INFORMATION29
1133 ,p_attribute30_name => 'REI_INFORMATION30'
1134 ,p_attribute30_value => p_rec.REI_INFORMATION30
1135 );
1136 end if;
1137 --
1138 --
1139 --
1140 hr_utility.set_location(' Leaving:'||l_proc, 10);
1141 End update_validate;
1142 --
1143 -- ----------------------------------------------------------------------------
1144 -- |---------------------------< delete_validate >----------------------------|
1145 -- ----------------------------------------------------------------------------
1146 Procedure delete_validate(p_rec in ghr_rei_shd.g_rec_type) is
1147 --
1148 l_proc varchar2(72) := g_package||'delete_validate';
1149 --
1150 Begin
1151 hr_utility.set_location('Entering:'||l_proc, 5);
1152 --
1153 -- Call all supporting business operations
1154 --
1155 hr_utility.set_location(' Leaving:'||l_proc, 10);
1156 End delete_validate;
1157 --
1158 procedure chk_ddf_extra_val
1159 (p_rec in ghr_rei_shd.g_rec_type) is
1160 --
1161 l_proc varchar2(72) := g_package||'chk_ddf_extra_val';
1162 l_error exception;
1163 --
1164 Begin
1165 hr_utility.set_location('Entering:'||l_proc, 5);
1166 --
1167 hr_utility.set_location('Entering:'||l_proc, 5);
1168 --
1169 -- Call check procedures to validate reason for submission, explanation and
1170 -- service only
1171 -- when information type is 'GHR_US_PD_GEN_EMP'.
1172 --
1173 if p_rec.information_type is not null then
1174 if p_rec.information_type = 'GHR_US_PD_GEN_EMP' then
1175 chk_reason_for_submission
1176 (p_pa_request_extra_info_id => p_rec.pa_request_extra_info_id
1177 ,p_reason_for_submission => p_rec.rei_information3
1178 ,p_effective_date => sysdate
1179 ,p_object_version_number => p_rec.object_version_number
1180 );
1181 chk_explanation
1182 (p_reason_for_submission => p_rec.rei_information3
1183 ,p_pa_request_extra_info_id => p_rec.pa_request_extra_info_id
1184 ,p_explanation => p_rec.rei_information4
1185 ,p_object_version_number => p_rec.object_version_number
1186 );
1187 /*
1188 chk_service
1189 (p_pa_request_extra_info_id => p_rec.pa_request_extra_info_id
1190 ,p_service => p_rec.rei_information5
1191 ,p_effective_date => sysdate
1192 ,p_object_version_number => p_rec.object_version_number
1193 );
1194 */
1195 end if;
1196 end if;
1197 /*
1198 -- Check for value of reference field an then
1199 -- call relevant validation procedure.
1200 --
1201 if <reference field value> is not null then
1202 --
1203 -- Reference field => Information type
1204 -- Reference field value => 'A'
1205 --
1206 else
1207 --
1208 -- Reference field values is not supported
1209 --
1210 hr_utility.set_message(8301, 'GHR_38117_FLEX_INV_REF_FIELD_V');
1211 hr_utility.raise_error;
1212 end if;
1213 else
1214 --
1215 -- When the reference field is null, check
1216 -- that none of the attribute fields have
1217 -- been set
1218 --
1219 endif;
1220
1221 if p_rec.rei_information1 is not null then
1222 raise l_error;
1223 elsif p_rec.rei_information2 is not null then
1224 raise l_error;
1225 elsif p_rec.rei_information3 is not null then
1226 raise l_error;
1227 elsif p_rec.rei_information4 is not null then
1228 raise l_error;
1229 elsif p_rec.rei_information5 is not null then
1230 raise l_error;
1231 elsif p_rec.rei_information6 is not null then
1232 raise l_error;
1233 elsif p_rec.rei_information7 is not null then
1234 raise l_error;
1235 elsif p_rec.rei_information8 is not null then
1236 raise l_error;
1237 elsif p_rec.rei_information9 is not null then
1238 raise l_error;
1239 elsif p_rec.rei_information10 is not null then
1240 raise l_error;
1241 elsif p_rec.rei_information11 is not null then
1242 raise l_error;
1243 elsif p_rec.rei_information12 is not null then
1244 raise l_error;
1245 elsif p_rec.rei_information13 is not null then
1246 raise l_error;
1247 elsif p_rec.rei_information14 is not null then
1248 raise l_error;
1249 elsif p_rec.rei_information15 is not null then
1250 raise l_error;
1251 elsif p_rec.rei_information16 is not null then
1252 raise l_error;
1253 elsif p_rec.rei_information17 is not null then
1254 raise l_error;
1255 elsif p_rec.rei_information18 is not null then
1256 raise l_error;
1257 elsif p_rec.rei_information19 is not null then
1258 raise l_error;
1259 elsif p_rec.rei_information20 is not null then
1260 raise l_error;
1261 elsif p_rec.rei_information21 is not null then
1262 raise l_error;
1263 elsif p_rec.rei_information22 is not null then
1264 raise l_error;
1265 elsif p_rec.rei_information23 is not null then
1266 raise l_error;
1267 elsif p_rec.rei_information24 is not null then
1268 raise l_error;
1269 elsif p_rec.rei_information25 is not null then
1270 raise l_error;
1271 elsif p_rec.rei_information26 is not null then
1272 raise l_error;
1273 elsif p_rec.rei_information27 is not null then
1274 raise l_error;
1275 elsif p_rec.rei_information28 is not null then
1276 raise l_error;
1277 elsif p_rec.rei_information29 is not null then
1278 raise l_error;
1279 elsif p_rec.rei_information30 is not null then
1280 raise l_error;
1281 end if;
1282 */
1283 --
1284
1285 hr_utility.set_location(' Leaving:'||l_proc, 10);
1286 exception
1287 when l_error then
1288 hr_utility.set_message(8301, 'GHR_38118_FLEX_INV_ATTRIBUTE_A');
1289 hr_utility.raise_error;
1290 hr_utility.set_location(' Leaving:'||l_proc, 10);
1291 end chk_ddf_extra_val;
1292 end ghr_rei_bus;