[Home] [Help]
PACKAGE BODY: APPS.GHR_SF52_VALIDN_PKG
Source
1 PACKAGE BODY ghr_sf52_validn_pkg AS
2 /* $Header: ghvalidn.pkb 120.0 2005/05/29 03:40:47 appldev noship $ */
3
4 g_package varchar2(33) := 'ghr_sf52_validn_pkg. ';
5 --
6
7 /*===========================================================================*
8 | Copyright (c) 1997 Oracle Corporation |
9 | All rights reserved. |
10 *============================================================================*/
11
12 --
13 /*
14 -- ---------------------------------------------------------------------------
15 -- |-----------------------------< chk_requested_by_title>---------------------------|
16 -- ---------------------------------------------------------------------------
17 --
18
19 procedure chk_requested_by_title
20 (p_requested_by_person_id in ghr_pa_requests.requested_by_person_id%type
21 ,p_requested_by_title in ghr_pa_requests.requested_by_title%type
22 ,p_effective_date in date
23 )
24 is
25
26 l_title ghr_pa_requests.requested_by_title%type;
27 l_proc varchar2(72) := g_package ||'chk_requested_by_title' ;
28
29 begin
30 hr_utility.set_location('Entering:'|| l_proc, 10);
31 if p_requested_by_title is not null then
32 l_title := ghr_api.get_position_title
33 (p_person_id => p_requested_by_person_id
34 ,p_effective_date => trunc(nvl(p_effective_date,sysdate))
35 );
36 hr_utility.set_location(l_proc, 20);
37 if nvl(l_title,hr_api.g_varchar2) <> p_requested_by_title then
38 hr_utility.set_message(8301,'GHR_38094_INV_REQ_TITLE');
39 hr_utility.raise_error;
40 end if;
41 hr_utility.set_location(l_proc, 30);
42 end if;
43 hr_utility.set_location('leaving :' ||l_proc, 40);
44 end chk_requested_by_title;
45 --
46
47 --
48 -- ---------------------------------------------------------------------------
49 -- |-----------------------------< chk_authorized_by_title>---------------------------|
50 -- ---------------------------------------------------------------------------
51 --
52
53 procedure chk_authorized_by_title
54 (p_authorized_by_person_id in ghr_pa_requests.authorized_by_person_id%type
55 ,p_authorized_by_title in ghr_pa_requests.authorized_by_title%type
56 ,p_effective_date in date
57 )
58 is
59
60 l_title ghr_pa_requests.authorized_by_title%type;
61 l_proc varchar2(72) := g_package ||'chk_authorized_by_title' ;
62
63 begin
64 hr_utility.set_location('Entering:'|| l_proc, 10);
65 if p_authorized_by_title is not null then
66 l_title := ghr_api.get_position_title
67 (p_person_id => p_authorized_by_person_id
68 ,p_effective_date => trunc(nvl(p_effective_date,sysdate))
69 );
70 hr_utility.set_location(l_proc, 20);
71 if nvl(l_title,hr_api.g_varchar2) <> p_authorized_by_title then
72 hr_utility.set_message(8301,'GHR_38095_INV_AUTH_TITLE');
73 hr_utility.raise_error;
74 end if;
75 hr_utility.set_location(l_proc, 30);
76 end if;
77 hr_utility.set_location('leaving :' ||l_proc, 40);
78 end chk_authorized_by_title;
79 --
80 */
81
82
83 -- ---------------------------------------------------------------------------
84 -- |-----------------------------< prelim_req_chk_for_update_hr >---------------------------|
85 -- ---------------------------------------------------------------------------
86 --
87
88 Procedure prelim_req_chk_for_update_hr
89 (p_pa_request_rec in ghr_pa_requests%rowtype
90 ) is
91
92 l_proc varchar2(72) := g_package || 'primary_reqd_chk_for_update_hr';
93 l_notification_id ghr_pa_requests.pa_notification_id%type;
94 l_person_type per_person_types.system_person_type%type;
95 l_session ghr_history_api.g_session_var_type;
96
97 l_new_line VARCHAR2(1) := substr('
98 ',1,1); -- Bug 1844515 Anil
99 l_null_list VARCHAR2(120) ; -- Bug 1844515 Anil
100
101 Cursor c_sf50 is
102 select pa_notification_id
103 from ghr_pa_requests par
104 where par.pa_request_id = p_pa_request_rec.pa_request_id;
105
106 Cursor c_per_type is
107 Select ppt.system_person_type
108 from per_people_f per,
109 per_person_types ppt
110 where per.person_id = p_pa_request_rec.person_id
111 and p_pa_request_rec.effective_date
112 between per.effective_start_date and per.effective_end_date
113 and ppt.person_type_id = per.person_type_id;
114
115
116 begin
117
118 hr_utility.set_location('Entering ' || l_proc,10);
119 ghr_history_api.get_g_session_var(l_session);
120
121 -- Check to see if the SF52 is not already processe
122
123 for sf50 in c_sf50 loop
124 hr_utility.set_location(l_proc,15);
125 l_notification_id := sf50.pa_notification_id;
126 end loop;
127 if l_notification_id is not null then
128 hr_utility.set_message(8301,'GHR_38389_ALREADY_PROCESSED');
129 hr_utility.raise_error;
130 end if;
131
132 -- check to see if the approval_date is not null
133 -- commented for the time being until the codes are fixed to handle elec. auth.
134
135 If p_pa_request_rec.approval_date is null then
136 hr_utility.set_message(8301,'GHR_38522_APPROVAL_REQUIRED');
137 hr_utility.raise_error;
138 End if;
139
140
141 -- Check that an effective_date is entered
142 If p_pa_request_rec.effective_date is null then
143 hr_utility.set_message(8301,'GHR_38185_EFF_DATE_REQUIRED');
144 ghr_upd_hr_validation.form_item_name := 'PAR.EFFECTIVE_DATE';
145 hr_utility.raise_error;
146 End if;
147
148
149 -- Note : What is the case of Position based Actions ??
150 -- Assuming that the following are the Position based families that will not require an assignmentid
151
152 If p_pa_request_rec.noa_family_code not in ('POS_ABOLISH','POSN_CHG','POS_CHG','POS_ESTABLISH','POS_REVIEW') then
153 If p_pa_request_rec.employee_assignment_id is null then
154 If p_pa_request_rec.noa_family_code = 'APP' then
155 for per_type in c_per_type loop
156 l_person_type := per_type.system_person_type;
157 end loop;
158 End if;
159 If p_pa_request_rec.noa_family_code = 'APP' and l_person_type = 'EX_EMP' then
160 Null;
161 Else
162 hr_utility.set_message(8301,'GHR_38146_ASSIGNMENT_ID_NULL');
163 ghr_upd_hr_validation.form_item_name := 'PAR.EMPLOYEE_LAST_NAME';
164 hr_utility.raise_error;
165 End if;
166 End if;
167 End if;
168
169 -- Check to see if atleast one position is entered
170
171 If p_pa_request_rec.from_position_id is null and
172 p_pa_request_rec.to_position_id is null then
173 hr_utility.set_message(8301,'GHR_38191_ATLEAST_ONE_POSITION');
174 hr_utility.raise_error;
175 End if;
176
177
178 -- Check to see if the To_position_id is entered , when the family being processed is APP
179 If p_pa_request_rec.to_position_id is null then
180 If p_pa_request_rec.noa_family_code = 'APP' then
181 hr_utility.set_message(8301,'GHR_38182_POSN_NOT_BE_NULL');
182 hr_utility.raise_error;
183 End if;
184 End if;
185
186 --Bug 1844515 Anil
187 --To check if second legal authority code is entered for a dual action request
188 IF p_pa_request_rec.first_noa_code NOT IN ( '001', '002') AND
189 p_pa_request_rec.second_noa_code IS NOT NULL THEN
190
191 -- Determines that the request is dual action.
192 IF p_pa_request_rec.first_action_la_code1 IS NULL AND
193 p_pa_request_rec.second_action_la_code1 IS NULL THEN
194
195 l_null_list := '5-C. Code ,' || l_new_line || '5-D. Legal Authority ,'
196 || l_new_line ||
197 '6-C. Code ,' || l_new_line || '6-D. Legal Authority.' ;
198 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
199 fnd_message.set_token('REQUIRED_LIST',l_null_list);
200 hr_utility.raise_error;
201
202 elsif p_pa_request_rec.second_action_la_code1 IS NULL THEN
203
204 l_null_list := '6-C. Code ,' || l_new_line || '6-D. Legal Authority.' ;
205 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
206 fnd_message.set_token('REQUIRED_LIST',l_null_list);
207 hr_utility.raise_error;
208
209 END IF;
210
211 END IF;
212
213 --End Bug 1844515
214
215 End prelim_req_chk_for_update_hr;
216
217 ------------------------------------------------------------------------------------------------------
218
219 --
220 -- ---------------------------------------------------------------------------
221 -- |-----------------------------< chk_position_title_seq_desc>---------------------------|
222 -- ---------------------------------------------------------------------------
223 --
224
225 procedure chk_position_title_seq_desc
226 (p_to_position_id in per_positions.position_id%type
227 ,p_to_position_title in ghr_pa_requests.to_position_title%type
228 ,p_to_position_number in ghr_pa_requests.to_position_number%type
229 ,p_to_position_seq_no in ghr_pa_requests.to_position_seq_no%type
230 ,p_effective_date in date default sysdate
231 )
232 is
233 l_bgp_id per_positions.business_group_id%type;
234 l_title ghr_pa_requests.to_position_title%type;
235 l_desc_no varchar2(150);
236 l_seq_no number;
237 l_proc varchar2(72) := g_package ||'chk_position_title_seq_desc' ;
238
239 cursor c_bgp_id is
240 select pos.business_group_id
241 from hr_all_positions_f pos
242 where pos.position_id = p_to_position_id
243 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
244
245
246 begin
247 hr_utility.set_location('Entering:'|| l_proc, 10);
248 if p_to_position_id is not null then
249 for bgp_id in c_bgp_id loop
250 l_bgp_id := bgp_id.business_group_id;
251 end loop;
252 l_title := ghr_api.get_position_title_pos
253 (p_position_id => p_to_position_id
254 ,p_business_group_id => l_bgp_id
255 ,p_effective_date => p_effective_date
256 );
257
258 if p_to_position_title is not null then
259 if nvl(l_title,hr_api.g_varchar2) <> p_to_position_title then
260 hr_utility.set_message(8301,'GHR_38072_INV_TO_POS_TITLE');
261 hr_utility.raise_error;
262 end if;
263 end if;
264
265 l_desc_no := ghr_api.get_position_desc_no_pos
266 (p_position_id => p_to_position_id
267 ,p_business_group_id => l_bgp_id
268 ,p_effective_date => p_effective_date
269 );
270 if p_to_position_number is not null then
271 if nvl(l_desc_no ,hr_api.g_varchar2) <> p_to_position_number then
272 hr_utility.set_message(8301,'GHR_38073_INV_TO_POS_NUMBER');
273 hr_utility.raise_error;
274 end if;
275 end if;
276
277 l_seq_no := ghr_api.get_position_sequence_no_pos
278 (p_position_id => p_to_position_id
279 ,p_business_group_id => l_bgp_id
280 ,p_effective_date => p_effective_date
281 );
282
283 if p_to_position_seq_no is not null then
284 if nvl(l_seq_no ,hr_api.g_number) <> p_to_position_seq_no then
285 hr_utility.set_message(8301,'GHR_38267_INV_TO_POS_SEQ_NUMB');
286 hr_utility.raise_error;
287 end if;
288 end if;
289
290 end if;
291 end chk_position_title_seq_desc;
292
293 -- ---------------------------------------------------------------------------
294 -- |-----------------------------< chk_award_uom>---------------------------|
295 -- ---------------------------------------------------------------------------
296
297 procedure chk_award_uom
298 (p_award_uom in ghr_pa_requests.award_uom%TYPE
299 ,p_effective_date in date) is
300
301 l_not_exists boolean ;
302 l_proc varchar2(72) := g_package ||'chk_award_uom';
303 l_api_updating boolean;
304 --
305 begin
306 hr_utility.set_location('Entering:'|| l_proc, 10);
307 --
308 -- Check if award_uom is valid
309 --
310 If p_award_uom is not null then
311 --
312 l_not_exists :=
313 hr_api.not_exists_in_hr_lookups
314 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
315 ,p_lookup_code => p_award_uom
316 ,p_lookup_type => 'GHR_US_AWARD_UOM'
317 );
318 if l_not_exists then
319 hr_utility.set_message(8301,'GHR_38170_INV_AWARD_UOM');
320 hr_utility.raise_error;
321 end if;
322 end if;
323 hr_utility.set_location(l_proc, 50);
324 --
325 --
326 hr_utility.set_location(' Leaving:'|| l_proc, 70);
327 end chk_award_uom;
328
329 -- ---------------------------------------------------------------------------
330 -- |-----------------------------< chk_appropriation_code1>---------------------------|
331 -- ---------------------------------------------------------------------------
332
333 procedure chk_appropriation_code1
334 (p_appropriation_code1 in ghr_pa_requests.appropriation_code1%TYPE
335 ,p_effective_date in date) is
336
337 l_not_exists boolean ;
338 l_proc varchar2(72) := g_package ||'chk_appropriation_code1';
339 l_api_updating boolean;
340 --
341 begin
342 hr_utility.set_location('Entering:'|| l_proc, 10);
343 --
344 -- Check if appropriation_code1 is valid
345 --
346 If p_appropriation_code1 is not null then
347 --
348 l_not_exists :=
349 hr_api.not_exists_in_hr_lookups
350 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
351 ,p_lookup_code => p_appropriation_code1
352 ,p_lookup_type => 'GHR_US_APPROPRIATION_CODE1'
353 );
354 if l_not_exists then
355 hr_utility.set_message(8301,'GHR_38074_INV_APP_CODE1');
356 hr_utility.raise_error;
357 end if;
358 end if;
359 hr_utility.set_location(l_proc, 50);
360 --
361 --
362 hr_utility.set_location(' Leaving:'|| l_proc, 70);
363 end chk_appropriation_code1;
364
365 -- ---------------------------------------------------------------------------
366 -- |-----------------------------< chk_appropriation_code2>---------------------------|
367 -- ---------------------------------------------------------------------------
368
369 procedure chk_appropriation_code2
370 (p_appropriation_code2 in ghr_pa_requests.appropriation_code2%TYPE
371 ,p_effective_date in date) is
372
373 l_not_exists boolean ;
374 l_proc varchar2(72) := g_package ||'chk_appropriation_code2';
375 l_api_updating boolean;
376 --
377 begin
378 hr_utility.set_location('Entering:'|| l_proc, 10);
379 --
383 --
380 -- Check if appropriation_code2 is valid
381 --
382 If p_appropriation_code2 is not null then
384 l_not_exists :=
385 hr_api.not_exists_in_hr_lookups
386 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
387 ,p_lookup_code => p_appropriation_code2
388 ,p_lookup_type => 'GHR_US_APPROPRIATION_CODE2'
389 );
390 if l_not_exists then
391 hr_utility.set_message(8301,'GHR_38075_INV_APP_CODE2');
392 hr_utility.raise_error;
393 end if;
394 end if;
395 hr_utility.set_location(l_proc, 50);
396 --
397 --
398 hr_utility.set_location(' Leaving:'|| l_proc, 70);
399 end chk_appropriation_code2;
400
401
402 -- ---------------------------------------------------------------------------
403 -- |-----------------------------< chk_annuitant_indicator>---------------------------|
404 -- ---------------------------------------------------------------------------
405
406 procedure chk_annuitant_indicator
407 (p_annuitant_indicator in ghr_pa_requests.annuitant_indicator%TYPE
408 ,p_effective_date in date) is
409
410 l_not_exists boolean ;
411 l_proc varchar2(72) := g_package ||'chk_annuitant_indicator';
412 l_api_updating boolean;
413 --
414 begin
415 hr_utility.set_location('Entering:'|| l_proc, 10);
416 --
417 -- Check if annuitant_indicator is valid
418 --
419 If p_annuitant_indicator is not null then
420 --
421 l_not_exists :=
422 hr_api.not_exists_in_hr_lookups
423 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
424 ,p_lookup_code => p_annuitant_indicator
425 ,p_lookup_type => 'GHR_US_ANNUITANT_INDICATOR'
426 );
427 if l_not_exists then
428 hr_utility.set_message(8301,'GHR_38069_INV_ANN_INDICATOR');
429 hr_utility.raise_error;
430 end if;
431 end if;
432 hr_utility.set_location(l_proc, 50);
433 --
434 --
435 hr_utility.set_location(' Leaving:'|| l_proc, 70);
436 end chk_annuitant_indicator;
437
438 -- ---------------------------------------------------------------------------
439 -- |-----------------------------< chk_bargaining_unit_status>---------------------------|
440 -- ---------------------------------------------------------------------------
441
442 procedure chk_bargaining_unit_status
443 (p_bargaining_unit_status in ghr_pa_requests.bargaining_unit_status%TYPE
444 ,p_effective_date in date) is
445
446 l_not_exists boolean;
447 l_proc varchar2(72) := g_package ||'chk_bargaining_unit_status';
448 l_api_updating boolean;
449 --
450 begin
451 hr_utility.set_location('Entering:'|| l_proc, 10);
452 --
453 -- Check if bargaining_unit_status is valid
454 --
455 If p_bargaining_unit_status is not null then
456 --
457 l_not_exists :=
458 hr_api.not_exists_in_hr_lookups
459 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
460 ,p_lookup_code => p_bargaining_unit_status
461 ,p_lookup_type => 'GHR_US_BARG_UNIT_STATUS'
462 );
463 if l_not_exists then
464 hr_utility.set_message(8301,'GHR_38076_INV_BARG_UNIT_STATUS');
465 hr_utility.raise_error;
466 end if;
467 end if;
468 hr_utility.set_location(l_proc, 50);
469 --
470 --
471 hr_utility.set_location(' Leaving:'|| l_proc, 70);
472 end chk_bargaining_unit_status;
473
474 -- ---------------------------------------------------------------------------
475 -- |-----------------------------< chk_supervisory_status>---------------------------|
476 -- ---------------------------------------------------------------------------
477
478 procedure chk_supervisory_status
479 (p_supervisory_status in ghr_pa_requests.supervisory_status%TYPE
480 ,p_effective_date in date) is
481
482 l_not_exists boolean;
483 l_proc varchar2(72) := g_package ||'chk_supervisory_status';
484 l_api_updating boolean;
485 --
486 begin
487 hr_utility.set_location('Entering:'|| l_proc, 10);
488 --
489 If p_supervisory_status is not null then
490 --
491 l_not_exists :=
492 hr_api.not_exists_in_hr_lookups
493 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
494 ,p_lookup_code => p_supervisory_status
495 ,p_lookup_type => 'GHR_US_SUPERVISORY_STATUS'
496 );
497 if l_not_exists then
498 hr_utility.set_message(8301,'GHR_38077_INV_SUPERV_STATUS');
499 hr_utility.raise_error;
500 end if;
501 end if;
502 hr_utility.set_location(l_proc, 50);
503 --
504 --
505 hr_utility.set_location(' Leaving:'|| l_proc, 70);
506 end chk_supervisory_status;
507
508 -- ---------------------------------------------------------------------------
509 -- |-----------------------------< chk_functional_class>---------------------------|
510 -- ---------------------------------------------------------------------------
511
515
512 procedure chk_functional_class
513 (p_functional_class in ghr_pa_requests.functional_class%TYPE
514 ,p_effective_date in date) is
516 l_not_exists boolean ;
517 l_proc varchar2(72) := g_package ||'chk_functional_class';
518 l_api_updating boolean;
519 --
520 begin
521 hr_utility.set_location('Entering:'|| l_proc, 10);
522 --
523 If p_functional_class is not null then
524 --
525 l_not_exists :=
526 hr_api.not_exists_in_hr_lookups
527 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
528 ,p_lookup_code => p_functional_class
529 ,p_lookup_type => 'GHR_US_FUNCTIONAL_CLASS'
530 );
531 if l_not_exists then
532 hr_utility.set_message(8301,'GHR_38078_INV_FUNC_CLASS');
533 hr_utility.raise_error;
534 end if;
535 end if;
536 hr_utility.set_location(l_proc, 50);
537 --
538 hr_utility.set_location(' Leaving:'|| l_proc, 70);
539 end chk_functional_class;
540
541
542 -- ---------------------------------------------------------------------------
543 -- |-----------------------------< chk_position_occupied>---------------------------|
544 -- ---------------------------------------------------------------------------
545
546 procedure chk_position_occupied
547 (p_position_occupied in ghr_pa_requests.position_occupied%TYPE
548 ,p_effective_date in date) is
549
550 l_not_exists boolean ;
551 l_proc varchar2(72) := g_package ||'chk_position_occupied';
552 l_api_updating boolean;
553 --
554 begin
555 hr_utility.set_location('Entering:'|| l_proc, 10);
556 --
557 -- Check mandatory parameters have been set
558 If p_position_occupied is not null then
559 --
560 l_not_exists :=
561 hr_api.not_exists_in_hr_lookups
562 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
563 ,p_lookup_code => p_position_occupied
564 ,p_lookup_type => 'GHR_US_POSITION_OCCUPIED'
565 );
566 if l_not_exists then
567 hr_utility.set_message(8301,'GHR_38079_INV_POS_OCCUPIED');
568 hr_utility.raise_error;
569 end if;
570 end if;
571 hr_utility.set_location(l_proc, 50);
572 --
573 hr_utility.set_location(' Leaving:'|| l_proc, 70);
574 end chk_position_occupied;
575
576 -- ---------------------------------------------------------------------------
577 -- |-----------------------------< chk_fegli>---------------------------|
578 -- ---------------------------------------------------------------------------
579
580 procedure chk_fegli
581 (p_fegli in ghr_pa_requests.fegli%TYPE
582 ,p_effective_date in date) is
583
584 l_not_exists boolean ;
585 l_proc varchar2(72) := g_package ||'chk_fegli';
586 l_api_updating boolean;
587 --
588 begin
589 hr_utility.set_location('Entering:'|| l_proc, 10);
590 --
591 -- Check if fegli is valid
592 --
593 If p_fegli is not null then
594 --
595 l_not_exists :=
596 hr_api.not_exists_in_hr_lookups
597 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
598 ,p_lookup_code => p_fegli
599 ,p_lookup_type => 'GHR_US_FEGLI'
600 );
601 if l_not_exists then
602 hr_utility.set_message(8301,'GHR_38070_INV_FEGLI');
603 hr_utility.raise_error;
604 end if;
605 end if;
606 hr_utility.set_location(l_proc, 50);
607 --
608 hr_utility.set_location(' Leaving:'|| l_proc, 70);
609 end chk_fegli;
610
611 -- ---------------------------------------------------------------------------
612 -- |-----------------------------< chk_retirement_plan>---------------------------|
613 -- ---------------------------------------------------------------------------
614
615
616
617 procedure chk_retirement_plan
618 (p_retirement_plan in ghr_pa_requests.retirement_plan%TYPE
619 ,p_effective_date in date
620 ) is
621
622 l_not_exists boolean;
623 l_proc varchar2(72) := g_package ||'chk_retirement_plan';
624 l_api_updating boolean;
625 --
626 begin
627 hr_utility.set_location('Entering:'|| l_proc, 10);
628 --
629 -- Check if retirement_plan is valid
630 --
631 If p_retirement_plan is not null then
632 --
633 l_not_exists :=
634 hr_api.not_exists_in_hr_lookups
635 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
636 ,p_lookup_code => p_retirement_plan
637 ,p_lookup_type => 'GHR_US_RETIREMENT_PLAN'
638 );
639 if l_not_exists then
640 hr_utility.set_message(8301,'GHR_38071_INV_RET_PLAN');
641 hr_utility.raise_error;
642 end if;
643 end if;
644 hr_utility.set_location(l_proc, 50);
645 --
646 hr_utility.set_location(' Leaving:'|| l_proc, 70);
647 end chk_retirement_plan;
648
649 -- ---------------------------------------------------------------------------
653 procedure chk_tenure
650 -- |-----------------------------< chk_tenure>---------------------------|
651 -- ---------------------------------------------------------------------------
652
654 (p_tenure in ghr_pa_requests.tenure%TYPE
655 ,p_effective_date in date) is
656
657 l_not_exists boolean;
658 l_proc varchar2(72) := g_package ||'chk_tenure';
659 l_api_updating boolean;
660 --
661 begin
662
663 hr_utility.set_location('Entering:'|| l_proc, 10);
664 --
665 -- Check if tenure is valid
666 --
667 If p_tenure is not null then
668 --
669 l_not_exists :=
670 hr_api.not_exists_in_hr_lookups
671 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
672 ,p_lookup_code => p_tenure
673 ,p_lookup_type => 'GHR_US_TENURE'
674 );
675 if l_not_exists then
676 hr_utility.set_message(8301,'GHR_38080_INV_TENURE');
677 hr_utility.raise_error;
678 end if;
679 end if;
680 hr_utility.set_location(l_proc, 50);
681 --
682 hr_utility.set_location(' Leaving:'|| l_proc, 70);
683 end chk_tenure;
684
685 -- ---------------------------------------------------------------------------
686 -- |-----------------------------< chk_pay_rate_determinant>---------------------------|
687 -- ---------------------------------------------------------------------------
688
689 procedure chk_pay_rate_determinant
690 (p_pay_rate_determinant in ghr_pa_requests.pay_rate_determinant%TYPE
691 ,p_effective_date in date
692 ) is
693
694 l_not_exists boolean ;
695 l_proc varchar2(72) := g_package ||'chk_pay_rate_determinant';
696 l_api_updating boolean;
697 --
698 begin
699 hr_utility.set_location('Entering:'|| l_proc, 10);
700 --
701 -- Check if pay_rate_determinant is valid
702 --
703 If p_pay_rate_determinant is not null then
704 --
705 l_not_exists :=
706 hr_api.not_exists_in_hr_lookups
707 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
708 ,p_lookup_code => p_pay_rate_determinant
709 ,p_lookup_type => 'GHR_US_PAY_RATE_DETERMINANT'
710 );
711 if l_not_exists then
712 hr_utility.set_message(8301,'GHR_38081_INV_PAY_RATE_DET');
713 hr_utility.raise_error;
714 end if;
715 end if;
716 hr_utility.set_location(l_proc, 50);
717 --
718 hr_utility.set_location(' Leaving:'|| l_proc, 70);
719 end chk_pay_rate_determinant;
720
721 -- ---------------------------------------------------------------------------
722 -- |-----------------------------< chk_pay_basis>---------------------------|
723 -- ---------------------------------------------------------------------------
724
725 procedure chk_pay_basis
726 (p_pay_basis in ghr_pa_requests.to_pay_basis%TYPE
727 ,p_effective_date in date
728 ) is
729
730 l_not_exists boolean ;
731 l_proc varchar2(72) := g_package ||'chk_pay_basis';
732 l_api_updating boolean;
733 --
734 begin
735 hr_utility.set_location('Entering:'|| l_proc, 10);
736 --
737 -- Check if pay_basis is valid
738 --
739 If p_pay_basis is not null then
740 --
741 l_not_exists :=
742 hr_api.not_exists_in_hr_lookups
743 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
744 ,p_lookup_code => p_pay_basis
745 ,p_lookup_type => 'GHR_US_PAY_BASIS'
746 );
747 if l_not_exists then
748 hr_utility.set_message(8301,'GHR_38082_INV_TO_PAY_BASIS');
749 hr_utility.raise_error;
750 end if;
751 end if;
752 hr_utility.set_location(l_proc, 50);
753 --
754 hr_utility.set_location(' Leaving:'|| l_proc, 70);
755 end chk_pay_basis;
756
757 -- ---------------------------------------------------------------------------
758 -- |-----------------------------< chk_pay_plan>---------------------------|
759 -- ---------------------------------------------------------------------------
760 -- Note : This procedure is no longer reqd. as the col. from and to pay_plans
761 -- are foreign keys to the ghr_pay_plan table
762
763 procedure chk_pay_plan
764 (p_pay_plan in ghr_pa_requests.to_pay_plan%TYPE
765 ,p_effective_date in date
766 ) is
767
768 l_not_exists boolean ;
769 l_proc varchar2(72) := g_package ||'chk_pay_plan';
770 l_api_updating boolean;
771 --
772 begin
773 hr_utility.set_location('Entering:'|| l_proc, 10);
774 --
775 -- Check if pay_plan is valid
776 --
777 If p_pay_plan is not null then
778 --
779 l_not_exists :=
780 hr_api.not_exists_in_hr_lookups
781 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
782 ,p_lookup_code => p_pay_plan
783 ,p_lookup_type => 'GHR_US_PAY_PLAN'
784 );
785 if l_not_exists then
786 hr_utility.set_message(8301,'GHR_38083_INV_TO_PAY_PLAN');
787 hr_utility.raise_error;
788 end if;
789 end if;
793 end chk_pay_plan;
790 hr_utility.set_location(l_proc, 50);
791 --
792 hr_utility.set_location(' Leaving:'|| l_proc, 70);
794
795 -- ---------------------------------------------------------------------------
796 -- |-----------------------------< chk_grade_or_level>---------------------------|
797 -- ---------------------------------------------------------------------------
798
799 procedure chk_grade_or_level
800 (p_grade_or_level in ghr_pa_requests.to_grade_or_level%TYPE
801 ,p_effective_date in date
802 ) is
803
804 l_not_exists boolean ;
805 l_proc varchar2(72) := g_package ||'chk_grade_or_level';
806 l_api_updating boolean;
807 --
808 begin
809 hr_utility.set_location('Entering:'|| l_proc, 10);
810 --
811 -- Check if grade_or_level is valid
812 --
813 If p_grade_or_level is not null then
814 --
815 l_not_exists :=
816 hr_api.not_exists_in_hr_lookups
817 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
818 ,p_lookup_code => p_grade_or_level
819 ,p_lookup_type => 'GHR_US_GRADE_OR_LEVEL' -- check this
820 );
821 if l_not_exists then
822 hr_utility.set_message(8301,'GHR_38084_INV_TO_GRADE_OR_LEV');
823 hr_utility.raise_error;
824 end if;
825 end if;
826 hr_utility.set_location(l_proc, 50);
827 --
828 hr_utility.set_location(' Leaving:'|| l_proc, 70);
829 end chk_grade_or_level;
830
831 -- ---------------------------------------------------------------------------
832 -- |-----------------------------< chk_to_occ_code>---------------------------|
833 -- ---------------------------------------------------------------------------
834
835 procedure chk_to_occ_code
836 (p_to_occ_code in ghr_pa_requests.to_occ_code%TYPE
837 ,p_effective_date in date
838 ) is
839
840 l_not_exists boolean ;
841 l_proc varchar2(72) := g_package ||'chk_to_occ_code';
842 l_api_updating boolean;
843 --
844 begin
845 hr_utility.set_location('Entering:'|| l_proc, 10);
846 --
847 -- Check if to_occ_code is valid
848 --
849 If p_to_occ_code is not null then
850 --
851 l_not_exists :=
852 hr_api.not_exists_in_hr_lookups
853 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
854 ,p_lookup_code => p_to_occ_code
855 ,p_lookup_type => 'GHR_US_OCC_SERIES'
856 );
857 if l_not_exists then
858 hr_utility.set_message(8301,'GHR_38085_INV_TO_OCC_CODE');
859 hr_utility.raise_error;
860 end if;
861 end if;
862 hr_utility.set_location(l_proc, 50);
863 --
864 hr_utility.set_location(' Leaving:'|| l_proc, 70);
865 end chk_to_occ_code;
866
867 -- ---------------------------------------------------------------------------
868 -- |-----------------------------< chk_step_or_rate>---------------------------|
869 -- ---------------------------------------------------------------------------
870
871 procedure chk_step_or_rate
872 (p_step_or_rate in ghr_pa_requests.to_step_or_rate%TYPE
873 ,p_effective_date in date
874 ) is
875
876 l_not_exists boolean ;
877 l_proc varchar2(72) := g_package ||'chk_step_or_rate';
878 l_api_updating boolean;
879 --
880 begin
881 hr_utility.set_location('Entering:'|| l_proc, 10);
882 --
883 -- Check if step_or_rate is valid
884 --
885 If p_step_or_rate is not null then
886 --
887 l_not_exists :=
888 hr_api.not_exists_in_hr_lookups
889 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
890 ,p_lookup_code => p_step_or_rate
891 ,p_lookup_type => 'GHR_US_STEP'
892 );
893 if l_not_exists then
894 hr_utility.set_message(8301,'GHR_38086_INV_TO_STEP_OR_RATE');
895 hr_utility.raise_error;
896 end if;
897 end if;
898 hr_utility.set_location(l_proc, 50);
899 --
900 hr_utility.set_location(' Leaving:'|| l_proc, 70);
901 end chk_step_or_rate;
902
903 -- ---------------------------------------------------------------------------
904 -- |-----------------------------< chk_citizenship>---------------------------|
905 -- ---------------------------------------------------------------------------
906
907 procedure chk_citizenship
908 (p_citizenship in ghr_pa_requests.citizenship%TYPE
909 ,p_effective_date in date
910 ) is
911
912 l_not_exists boolean ;
913 l_proc varchar2(72) := g_package ||'chk_citizenship';
914 l_api_updating boolean;
915 --
916 begin
917 hr_utility.set_location('Entering:'|| l_proc, 10);
918 --
919 -- Check if citizenship is valid
920 --
921 If p_citizenship is not null then
922 --
923 l_not_exists :=
924 hr_api.not_exists_in_hr_lookups
925 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
926 ,p_lookup_code => p_citizenship
927 ,p_lookup_type => 'GHR_US_CITIZENSHIP'
928 );
929 if l_not_exists then
933 end if;
930 hr_utility.set_message(8301,'GHR_38384_INV_CITIZENSHIP');
931 hr_utility.raise_error;
932 end if;
934 hr_utility.set_location(l_proc, 50);
935 --
936 hr_utility.set_location(' Leaving:'|| l_proc, 70);
937 end chk_citizenship;
938
939
940 -- ---------------------------------------------------------------------------
941 -- |-----------------------------< chk_vet_status>---------------------------|
942 -- ---------------------------------------------------------------------------
943
944 procedure chk_vet_status
945 (p_veterans_status in ghr_pa_requests.veterans_status%TYPE
946 ,p_effective_date in date
947 ) is
948
949 l_not_exists boolean ;
950 l_proc varchar2(72) := g_package ||'chk_vet_status';
951 l_api_updating boolean;
952 --
953 begin
954 hr_utility.set_location('Entering:'|| l_proc, 10);
955 --
956 -- Check if vet_status is valid
957 --
958 If p_veterans_status is not null then
959 --
960 l_not_exists :=
961 hr_api.not_exists_in_hr_lookups
962 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
963 ,p_lookup_code => p_veterans_status
964 ,p_lookup_type => 'GHR_US_VET_STATUS' -- check this
965 );
966 if l_not_exists then
967 hr_utility.set_message(8301,'GHR_38088_INV_VET_STATUS');
968 hr_utility.raise_error;
969 end if;
970 end if;
971 hr_utility.set_location(l_proc, 50);
972 --
973 hr_utility.set_location(' Leaving:'|| l_proc, 70);
974 end chk_vet_status;
975
976 -- ---------------------------------------------------------------------------
977 -- |-----------------------------< chk_vet_pref>---------------------------|
978 -- ---------------------------------------------------------------------------
979
980 procedure chk_vet_pref
981 (p_veterans_preference in ghr_pa_requests.veterans_preference%TYPE
982 ,p_effective_date in date
983 ) is
984
985 l_not_exists boolean ;
986 l_proc varchar2(72) := g_package ||'chk_vet_pref';
987 l_api_updating boolean;
988 --
989 begin
990 hr_utility.set_location('Entering:'|| l_proc, 10);
991 --
992 -- Check if vet_pref is valid
993 --
994 If p_veterans_preference is not null then
995 --
996 l_not_exists :=
997 hr_api.not_exists_in_hr_lookups
998 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
999 ,p_lookup_code => p_veterans_preference
1000 ,p_lookup_type => 'GHR_US_VETERANS_PREF' -- check this
1001 );
1002 if l_not_exists then
1003 hr_utility.set_message(8301,'GHR_38089_INV_VET_PREF');
1004 hr_utility.raise_error;
1005 end if;
1006 end if;
1007 hr_utility.set_location(l_proc, 50);
1008 --
1009 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1010 end chk_vet_pref;
1011
1012 -- ---------------------------------------------------------------------------
1013 -- |-----------------------------< chk_work_schedule>---------------------------|
1014 -- ---------------------------------------------------------------------------
1015
1016 procedure chk_work_schedule
1017 (p_work_schedule in ghr_pa_requests.work_schedule%TYPE
1018 ,p_effective_date in date
1019 ) is
1020
1021 l_not_exists boolean ;
1022 l_proc varchar2(72) := g_package ||'chk_work_schedule';
1023 l_api_updating boolean;
1024 --
1025 begin
1026 hr_utility.set_location('Entering:'|| l_proc, 10);
1027 --
1028 -- Check if work_schedule is valid
1029 --
1030 If p_work_schedule is not null then
1031 --
1032 l_not_exists :=
1033 hr_api.not_exists_in_hr_lookups
1034 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
1035 ,p_lookup_code => p_work_schedule
1036 ,p_lookup_type => 'GHR_US_WORK_SCHEDULE' -- check this
1037 );
1038 if l_not_exists then
1039 hr_utility.set_message(8301,'GHR_38090_INV_WORK_SCHED');
1040 hr_utility.raise_error;
1041 end if;
1042 end if;
1043 hr_utility.set_location(l_proc, 50);
1044 --
1045 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1046 end chk_work_schedule;
1047
1048 -- ---------------------------------------------------------------------------
1049 -- |-----------------------------< chk_academic_discipline>---------------------------|
1050 -- ---------------------------------------------------------------------------
1051
1052 procedure chk_academic_discipline
1053 (p_academic_discipline in ghr_pa_requests.academic_discipline%TYPE
1054 ,p_effective_date in date
1055 ) is
1056
1057 l_not_exists boolean ;
1058 l_proc varchar2(72) := g_package ||'chk_academic_discipline';
1059 l_api_updating boolean;
1060 --
1061 begin
1062 hr_utility.set_location('Entering:'|| l_proc, 10);
1063 --
1064 -- Check if academic_discipline is valid
1065 --
1066 If p_academic_discipline is not null then
1067 --
1068 l_not_exists :=
1069 hr_api.not_exists_in_hr_lookups
1073 );
1070 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
1071 ,p_lookup_code => p_academic_discipline
1072 ,p_lookup_type => 'GHR_US_ACADEMIC_DISCIPLINE'
1074 if l_not_exists then
1075 hr_utility.set_message(8301,'GHR_38091_INV_ACAD_DISC');
1076 hr_utility.raise_error;
1077 end if;
1078 end if;
1079 hr_utility.set_location(l_proc, 50);
1080 --
1081 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1082 end chk_academic_discipline;
1083
1084 -- ---------------------------------------------------------------------------
1085 -- |-----------------------------< chk_education_level>---------------------------|
1086 -- ---------------------------------------------------------------------------
1087
1088 procedure chk_education_level
1089 (p_education_level in ghr_pa_requests.education_level%TYPE
1090 ,p_effective_date in date
1091 ) is
1092
1093 l_not_exists boolean ;
1094 l_proc varchar2(72) := g_package ||'chk_education_level';
1095 l_api_updating boolean;
1096 --
1097 begin
1098 hr_utility.set_location('Entering:'|| l_proc, 10);
1099 --
1100 -- Check if education_level is valid
1101 --
1102 If p_education_level is not null then
1103 --
1104 l_not_exists :=
1105 hr_api.not_exists_in_hr_lookups
1106 (p_effective_date => trunc(nvl(p_effective_date,sysdate))
1107 ,p_lookup_code => p_education_level
1108 ,p_lookup_type => 'GHR_US_EDUCATIONAL_LEVEL'
1109 );
1110 if l_not_exists then
1111 hr_utility.set_message(8301,'GHR_38092_INV_EDUC_LEVEL');
1112 hr_utility.raise_error;
1113 end if;
1114 end if;
1115 hr_utility.set_location(l_proc, 50);
1116 --
1117 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1118 end chk_education_level;
1119
1120 -- ---------------------------------------------------------------------------
1121 -- |-----------------------------< chk_year_degree_attained>---------------------------|
1122 -- ---------------------------------------------------------------------------
1123
1124 procedure chk_year_degree_attained
1125 (p_year_degree_attained in ghr_pa_requests.year_degree_attained%TYPE
1126 ) is
1127
1128 begin
1129 If p_year_degree_attained > to_number(to_char(sysdate,'yyyy')) then
1130 hr_utility.set_message(8301,'GHR_38093_INV_DEGR_ATT_YEAR');
1131 hr_utility.raise_error;
1132 end if;
1133 end chk_year_degree_attained;
1134
1135
1136 -- ---------------------------------------------------------------------------
1137 -- |-----------------------------< perform_validation>---------------------------|
1138 -- ---------------------------------------------------------------------------
1139 procedure perform_validn
1140 (p_rec in ghr_pa_requests%ROWTYPE
1141 ) is
1142
1143 begin
1144
1145 -- The following 2 procedures have been commented out as a fix for bug #555121
1146 -- as per Jon's suggestion of removing the validation
1147 --
1148 -- Set up the CLIENT_INFO
1149 --
1150 ghr_utility.set_client_info;
1151
1152 /* chk_requested_by_title
1153 (p_requested_by_person_id => p_rec.requested_by_person_id
1154 ,p_requested_by_title => p_rec.requested_by_title
1155 ,p_effective_date => p_rec.effective_date
1156 );
1157
1158 chk_authorized_by_title
1159 (p_authorized_by_person_id => p_rec.authorized_by_person_id
1160 ,p_authorized_by_title => p_rec.authorized_by_title
1161 ,p_effective_date => p_rec.effective_date
1162 );
1163 */
1164
1165 chk_position_title_seq_desc
1166 (p_to_position_id => p_rec.to_position_id
1167 ,p_to_position_title => p_rec.to_position_title
1168 ,p_to_position_number => p_rec.to_position_number
1169 ,p_to_position_seq_no => p_rec.to_position_seq_no
1170 ,p_effective_date => p_rec.effective_date
1171 );
1172
1173 chk_to_occ_code
1174 (p_to_occ_code => p_rec.to_occ_code
1175 ,p_effective_date => p_rec.effective_date
1176 );
1177
1178 chk_grade_or_level
1179 (p_grade_or_level => p_rec.to_grade_or_level
1180 ,p_effective_date => p_rec.effective_date
1181 );
1182
1183 chk_step_or_rate
1184 (p_step_or_rate => p_rec.to_step_or_rate
1185 ,p_effective_date => p_rec.effective_date
1186 );
1187
1188 chk_award_uom
1189 (p_award_uom => p_rec.award_uom
1190 ,p_effective_date => p_rec.effective_date
1191 );
1192
1193 chk_pay_basis
1194 (p_pay_basis => p_rec.to_pay_basis
1195 ,p_effective_date => p_rec.effective_date
1196 );
1197
1198 chk_vet_pref
1199 (p_veterans_preference => p_rec.veterans_preference
1200 ,p_effective_date => p_rec.effective_date
1201 );
1202
1203 chk_tenure
1204 (p_tenure => p_rec.tenure
1205 ,p_effective_date => p_rec.effective_date
1206 );
1207
1208 chk_fegli
1209 (p_fegli => p_rec.fegli
1210 ,p_effective_date => p_rec.effective_date
1211 );
1212
1213 chk_annuitant_indicator
1214 (p_annuitant_indicator => p_rec.annuitant_indicator
1215 ,p_effective_date => p_rec.effective_date
1216 );
1217
1218 chk_pay_rate_determinant
1219 (p_pay_rate_determinant => p_rec.pay_rate_determinant
1220 ,p_effective_date => p_rec.effective_date
1221 );
1222
1223 chk_retirement_plan
1224 (p_retirement_plan => p_rec.retirement_plan
1225 ,p_effective_date => p_rec.effective_date
1226 );
1227
1228 chk_work_schedule
1229 (p_work_schedule => p_rec.work_schedule
1230 ,p_effective_date => p_rec.effective_date
1231 );
1232
1233 chk_position_occupied
1234 (p_position_occupied => p_rec.position_occupied
1235 ,p_effective_date => p_rec.effective_date
1236 );
1237
1238
1239 chk_appropriation_code1
1240 (p_appropriation_code1 => p_rec.appropriation_code1
1241 ,p_effective_date => p_rec.effective_date
1242 );
1243
1244 chk_appropriation_code2
1245 (p_appropriation_code2 => p_rec.appropriation_code2
1246 ,p_effective_date => p_rec.effective_date
1247 );
1248
1249
1250 chk_bargaining_unit_status
1251 (p_bargaining_unit_status => p_rec.bargaining_unit_status
1252 ,p_effective_date => p_rec.effective_date
1253 );
1254
1255 chk_education_level
1256 (p_education_level => p_rec.education_level
1257 ,p_effective_date => p_rec.effective_date
1258 );
1259
1260 chk_year_degree_attained
1261 (p_year_degree_attained => p_rec.year_degree_attained);
1262
1263 chk_academic_discipline
1264 (p_academic_discipline => p_rec.academic_discipline
1265 ,p_effective_date => p_rec.effective_date
1266 );
1267
1268 chk_functional_class
1269 (p_functional_class => p_rec.functional_class
1270 ,p_effective_date => p_rec.effective_date
1271 );
1272
1273 chk_citizenship
1274 (p_citizenship => p_rec.citizenship
1275 ,p_effective_date => p_rec.effective_date
1276 );
1277
1278 chk_vet_status
1279 (p_veterans_status => p_rec.veterans_status
1280 ,p_effective_date => p_rec.effective_date
1281 );
1282
1283 chk_supervisory_status
1284 (p_supervisory_status => p_rec.supervisory_status
1285 ,p_effective_date => p_rec.effective_date
1286 );
1287
1288 end perform_validn;
1289
1290 end ghr_sf52_validn_pkg;