1 package body GHR_CPDF_CHECK6 as
2 /* $Header: ghcpdf06.pkb 120.12.12010000.3 2008/12/11 11:04:40 utokachi ship $ */
3
4 /* Name:
5 -- Prior Work Schedule
6 */
7
8 procedure chk_prior_work_schedule
9 (p_prior_work_schedule in varchar2 --non SF52
10 ,p_work_schedule_code in varchar2
11 ,p_first_noac_lookup_code in varchar2
12 ) is
13 begin
14
15 -- 590.02.2
16 if p_first_noac_lookup_code = '781' and
17 p_prior_work_schedule is not null and
18 p_work_schedule_code is not null and
19 p_prior_work_schedule = p_work_schedule_code then
20 hr_utility.set_message(8301, 'GHR_37601_ALL_PROCEDURE_FAIL');
21 hr_utility.raise_error;
22 end if;
23
24 -- 590.04.2
25 if p_first_noac_lookup_code = '430' and
26 p_prior_work_schedule not in ('G','J','Q','T') and
27 p_prior_work_schedule is not null then
28 hr_utility.set_message(8301, 'GHR_37602_ALL_PROCEDURE_FAIL');
29 hr_utility.raise_error;
30 end if;
31
32 end chk_prior_work_schedule;
33
34
35 /* Name:
36 -- Race or National Origin
37 */
38
39
40 procedure chk_race_or_natnl_origin
41 (p_race_or_natnl_origin in varchar2 --non SF52
42 ,p_duty_station_lookup_code in varchar2
43 ,p_ethnic_race_info in varchar2
44 ,p_first_action_noa_la_code1 in varchar2
45 ,p_first_action_noa_la_code2 in varchar2
46 ,p_effective_date in date
47 ) is
48 begin
49
50 -- Begin Bug# 4567571
51 -- 165.00.3
52 -- 17-Nov-2005 Raju Bug# 4567571 Added this Edit for UPD44.
53
54 IF p_effective_date >= to_date('01/01/2006','MM/DD/RRRR') THEN
55 --IF p_effective_date >= to_date('10/01/2005','MM/DD/RRRR') THEN
56 IF p_ethnic_race_info is not null THEN
57 IF INSTR(p_ethnic_race_info,'1')<1 THEN
58 hr_utility.set_message(8301, 'GHR_38988_ALL_PROCEDURE_FAIL');
59 hr_utility.raise_error;
60 END IF;
61
62 IF INSTR(p_ethnic_race_info,' ')> 0 THEN
63 hr_utility.set_message(8301, 'GHR_38988_ALL_PROCEDURE_FAIL');
64 hr_utility.raise_error;
65 END IF;
66 END IF;
67 END IF;
68
69
70
71 -- 165.09.2
72 -- 17-Nov-2005 Raju Bug# 4567571 Added this Edit for UPD44.
73
74 IF p_effective_date >= to_date('07/01/2006','MM/DD/RRRR') THEN
75 --IF p_effective_date >= to_date('11/01/2005','MM/DD/RRRR') THEN
76 hr_utility.set_location('p_first_action_noa_la_code1 ' || p_first_action_noa_la_code1,111);
77 hr_utility.set_location('p_ethnic_race_info ' || p_ethnic_race_info,111);
78
79 IF (substr(p_first_action_noa_la_code1, 1, 1) = '1' or substr(p_first_action_noa_la_code2, 1, 1) = '1') and
80 p_ethnic_race_info is null THEN
81 hr_utility.set_message(8301, 'GHR_38991_ALL_PROCEDURE_FAIL');
82 hr_utility.raise_error;
83 end if;
84 END IF;
85
86 -- 165.11.2
87 -- 17-Nov-2005 Raju Bug# 4567571 Added this Edit for UPD44.
88
89 IF p_effective_date >= to_date('07/01/2006','MM/DD/RRRR') THEN
90 --IF p_effective_date >= to_date('11/01/2005','MM/DD/RRRR') THEN
91 hr_utility.set_location('p_first_action_noa_la_code1 ' || p_first_action_noa_la_code1,111);
92 hr_utility.set_location('p_race_or_natnl_origin ' || p_race_or_natnl_origin,111);
93
94 IF (substr(p_first_action_noa_la_code1, 1, 1) = '1' or substr(p_first_action_noa_la_code2, 1, 1) = '1') and
95 p_race_or_natnl_origin is not null THEN
96 hr_utility.set_message(8301, 'GHR_38992_ALL_PROCEDURE_FAIL');
97 hr_utility.raise_error;
98 end if;
99 END IF;
100 -- End Bug# 4567571
101
102 -- 165.05.3, 165.05.1
103 -- 17-Nov-2005 Raju Bug# 4567571 Added this Edit for UPD44.
104 -- This code handles Edit 165.07.3 also
105 -- UPD 50(Bug 5745356) Raju from 01-Oct-2006 Rename 165.05.3 to 165.05.1
106 IF p_effective_date >= to_date('01/01/2006','MM/DD/RRRR') THEN
107 --IF p_effective_date >= to_date('10/01/2005','MM/DD/RRRR') THEN
108 IF p_effective_date >= to_date('10/01/2006','MM/DD/RRRR') THEN
109 IF (p_first_action_noa_la_code1 <> '817' or p_first_action_noa_la_code2 <> '817') and
110 p_ethnic_race_info is null and
111 p_race_or_natnl_origin IS NULL THEN
112 --Bug# 6959477 message number 38822 is duplicated, so created new message with #38160
113 hr_utility.set_message(8301, 'GHR_38160_ALL_PROCEDURE_FAIL');
114 hr_utility.raise_error;
115 end if;
116 ELSE
117 IF p_ethnic_race_info is null and
118 p_race_or_natnl_origin IS NULL THEN
119 hr_utility.set_message(8301, 'GHR_38989_ALL_PROCEDURE_FAIL');
120 hr_utility.raise_error;
121 end if;
122 END IF;
123 END IF;
124
125 -- Uncommented this procedure on 7/13/98 by skutteti
126 -- 600.02.3
127 if p_race_or_natnl_origin = 'Y'
128 and
129 substr(p_duty_station_lookup_code, 1, 2) <> 'RQ'
130 and
131 p_duty_station_lookup_code is not null
132 then
133 hr_utility.set_message(8301, 'GHR_37603_ALL_PROCEDURE_FAIL');
134 hr_utility.raise_error;
135 end if;
136
137 -- 600.04.3
138 if p_race_or_natnl_origin in ('F','G','H','J','K','L','M','N','P','Q') and
139 substr(p_duty_station_lookup_code, 1, 2) <> '15'
140 and
141 p_duty_station_lookup_code is not null
142 then
143 hr_utility.set_message(8301, 'GHR_37604_ALL_PROCEDURE_FAIL');
144 hr_utility.raise_error;
145 end if;
146
147 -- 600.07.3
148 if substr(p_duty_station_lookup_code, 1, 2) = 'RQ' and
149 (p_race_or_natnl_origin <> 'D'
150 and
151 p_race_or_natnl_origin <> 'Y')
152 and
153 p_race_or_natnl_origin is not null
154 then
155 hr_utility.set_message(8301, 'GHR_37605_ALL_PROCEDURE_FAIL');
156 hr_utility.raise_error;
157 end if;
158
159 -- 600.10.3
160 if substr(p_duty_station_lookup_code, 1, 2) = '15' and
161 p_race_or_natnl_origin not in ('A','C','D','E','F','G','H','I','J',
162 'K','L','M','N','O','P','Q')
163 and
164 p_race_or_natnl_origin is not null
165 then
166 hr_utility.set_message(8301, 'GHR_37606_ALL_PROCEDURE_FAIL');
167 hr_utility.raise_error;
168 end if;
169
170 end chk_race_or_natnl_origin;
171
172
173 /* Name:
174 Prior Duty Station
175 */
176 procedure chk_prior_duty_station
177 (p_prior_duty_station in varchar2
178 ,p_agency_subelement in varchar2
179 ) is
180 begin
181
182 -- 654.03.2
183 null;
184 /* This is not required according to the update6 summary of the edit manual.
185 if substr(p_prior_duty_station, 1, 2) = 'US' and
186 p_agency_subelement <> 'DJ02' then
187 hr_utility.set_message(8301, 'GHR_37607_ALL_PROCEDURE_FAIL');
188 hr_utility.raise_error;
189 end if;
190 */
191
192 end chk_prior_duty_station;
193
194 /* Name:
195 -- Retention Allowance
196 */
197
198
199 procedure chk_retention_allowance
200 (p_retention_allowance in varchar2 --non SF52
201 ,p_to_pay_plan in varchar2
202 ,p_to_basic_pay in varchar2
203 ,p_first_noac_lookup_code in varchar2
204 ,p_first_action_noa_la_code1 in varchar2
205 ,p_first_action_noa_la_code2 in varchar2
206 ) is
207 begin
208
209 /* Commented -- August 2001 10.7 Patch
210 -- 655.02.1
211 if p_retention_allowance is not null and
212 to_number(p_retention_allowance) <= 0 then
213 hr_utility.set_message(8301, 'GHR_37666_ALL_PROCEDURE_FAIL');
214 hr_utility.raise_error;
215 end if;
216 */
217
218 -- 655.10.3
219 -- 18/2 16-Oct-2002 vnarasim From the begining Added Pay plans SK,SO
220 -- UPDATE UPDATE DATE UPDATED BY COMMENTS
221 -------------------------------------------------------------------------------
222 -- Update 37 09-NOV-2004 MADHURI Delete pay plans AA, AL,
223 -- CA, SK, and SO.
224 -------------------------------------------------------------------------------
225
226 if p_to_pay_plan in ('ES','EX','GM','SL','ST')
227 and
228 p_retention_allowance is not null
229 and
230 to_number(p_retention_allowance) > (to_number(p_to_basic_pay) * .25) then
231 hr_utility.set_message(8301, 'GHR_37667_ALL_PROCEDURE_FAIL');
232 hr_utility.raise_error;
233 end if;
234 --
235 -- 655.15.2
236 /* The following edits were commented out to allow null values to be used to
237 end other pay elements: Staffing Differential, Retention Allowance and Supervisory Differential.
238 if p_first_noac_lookup_code = '810' and
239 (p_first_action_noa_la_code1 = 'VPG' or p_first_action_noa_la_code2 = 'VPG')
240 and
241 p_retention_allowance is null
242 then
243 hr_utility.set_message(8301, 'GHR_37668_ALL_PROCEDURE_FAIL');
244 hr_utility.raise_error;
245 end if;
246 */
247 /* Commented -- August 2001 10.7 Patch
248 -- 655.20.2
249 if p_first_noac_lookup_code <> 810 and
250 p_retention_allowance is not null and
251 to_number(p_retention_allowance) <= 0 then
252 hr_utility.set_message(8301, 'GHR_37669_ALL_PROCEDURE_FAIL');
253 hr_utility.raise_error;
254 end if;
255 */
256
257 end chk_retention_allowance;
258
259
260
261 /* Name:
262 -- Staffing Differential
263 */
264
265 procedure chk_staffing_differential
266 (p_staffing_differential in varchar2
267 ,p_first_noac_lookup_code in varchar2
268 ,p_first_action_noa_la_code1 in varchar2
269 ,p_first_action_noa_la_code2 in varchar2
270 ) is
271 begin
272
273 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
274 -- 656.10.1
275 if p_staffing_differential is not null and
276 to_number(p_staffing_differential) <= 0 then
277 hr_utility.set_message(8301, 'GHR_37670_ALL_PROCEDURE_FAIL');
278 hr_utility.raise_error;
279 end if;
280 */
281
282 -- 656.15.2
283 /* The following edits were commented out to allow null values to be used to
284 end other pay elements: Staffing Differential, Retention Allowance and Supervisory Differential.
285 if p_first_noac_lookup_code = '810' and
286 (p_first_action_noa_la_code1 = 'ZTS' or p_first_action_noa_la_code2= 'ZTS') and
287 p_staffing_differential is null then
288 hr_utility.set_message(8301, 'GHR_37671_ALL_PROCEDURE_FAIL');
289 hr_utility.raise_error;
290 end if;
291 */
292 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
293 -- 656.20.2
294 if p_first_noac_lookup_code <> '810' and
295 p_staffing_differential is not null and
296 to_number(p_staffing_differential) <= 0 then
297 hr_utility.set_message(8301, 'GHR_37672_ALL_PROCEDURE_FAIL');
298 hr_utility.raise_error;
299 end if;
300 */
301 null;
302 end chk_staffing_differential;
303
304
305 /* Name:
306 -- Supervisor Differential
307 */
308
309 procedure chk_supervisory_differential
310 (p_supervisory_differential in varchar2 --non SF52
311 ,p_first_noac_lookup_code in varchar2
312 ,p_first_action_noa_la_code1 in varchar2
313 ,p_first_action_noa_la_code2 in varchar2
314 ,p_effective_date in date
315 ) is
316 begin
317
318 -- 657.15.1
319 -- Commented by Ashley for bug 3251402 (EOY 03)
320 /* if p_supervisory_differential is not null and
321 to_number(p_supervisory_differential) <= 0 then
322 hr_utility.set_message(8301, 'GHR_37673_ALL_PROCEDURE_FAIL');
323 hr_utility.raise_error;
324 end if;
325 */
326
327 -- 657.20.2
328 /* The following edits were commented out to allow null values to be used to
329 end other pay elements: Staffing Differential, Retention Allowance and Supervisory Differential.
330 if p_first_noac_lookup_code = '810' and
331 (p_first_action_noa_la_code1 = 'VPH' or p_first_action_noa_la_code2 = 'VPH') and
332 p_supervisory_differential is null then
333 hr_utility.set_message(8301, 'GHR_37674_ALL_PROCEDURE_FAIL');
334 hr_utility.raise_error;
335 end if;
336 */
337 -- 657.25.2
338 -- EOY'02 Patch 13-NOV-02 vnarasim End dated the Edit as of 31-MAY-2001
339
340 if p_effective_date <= to_date('31/05/2001','dd/mm/yyyy') then
341 if p_first_noac_lookup_code <> '810' and
342 p_supervisory_differential is not null and
343 to_number(p_supervisory_differential) <= 0 then
344 hr_utility.set_message(8301, 'GHR_37675_ALL_PROCEDURE_FAIL');
345 hr_utility.raise_error;
346 end if;
347 end if;
348 end chk_supervisory_differential;
349
350
351
352 /* Name:
353 -- Service Computation Date
354 */
355
356 procedure chk_service_comp_date
357 (p_service_computation_date in date
358 ,p_effective_date in date
359 ,p_employee_date_of_birth in date
360 ,p_duty_station_lookup_code in varchar2
361 ,p_first_noac_lookup_code in varchar2
362 ,p_credit_mil_svc in varchar2 --non SF52
363 ,p_submission_date in date --non SF52
364 ) is
365
366 begin
367
368 -- 660.03.1
369 -- Renumbered from 660.01.1
370 if p_service_computation_date > p_submission_date
371 and
372 p_service_computation_date is not null
373 then
374 hr_utility.set_message(8301, 'GHR_37608_ALL_PROCEDURE_FAIL');
375 hr_utility.raise_error;
376 end if;
377
378 -- 660.02.3
379 if
380 -- year_between(p_service_computation_date - p_employee_date_of_birth) <= 13
381 (to_number(substr(to_char(p_service_computation_date, 'MMDDYYYY'),5,4)) -
382 to_number(substr(to_char(p_employee_date_of_birth, 'MMDDYYYY'),5,4)) ) <=13
383
384 and
385 p_service_computation_date is not null
386 then
387 hr_utility.set_message(8301, 'GHR_37609_ALL_PROCEDURE_FAIL');
388 hr_utility.raise_error;
389 end if;
390
391 -- 660.04.3
392 -- end dated this edit on 27-oct-98 for bug 745246
393 if p_effective_date <= to_date('31/07/1998','dd/mm/yyyy') then
394 if ((
395 substr(p_duty_station_lookup_code, 1, 1) in ( '0','1','2','3','4','5','6','7','8','9')
396 and
397 substr(p_duty_station_lookup_code, 2, 1) in ( '0','1','2','3','4','5','6','7','8','9')
398 ) or
399 substr(p_duty_station_lookup_code,2,1) = 'Q'
400 or
401 p_duty_station_lookup_code is null
402 )
403 and
404 -- Year_Between(p_service_computation_date - p_employee_date_of_birth) < 15
405 (to_number(substr(to_char(p_service_computation_date, 'MMDDYYYY'),5,4)) -
406 to_number(substr(to_char(p_employee_date_of_birth, 'MMDDYYYY'),5,4)) ) < 15
407 and
408 p_service_computation_date is not null
409 then
410 hr_utility.set_message(8301, 'GHR_37610_ALL_PROCEDURE_FAIL');
411 hr_utility.raise_error;
412 end if;
413 end if;
414
415 -- 660.07.2
416 if p_service_computation_date > p_effective_date
417 and
418 p_service_computation_date is not null
419 then
420 hr_utility.set_message(8301, 'GHR_37611_ALL_PROCEDURE_FAIL');
421 hr_utility.raise_error;
422 end if;
423
424 -- 660.10.2
425 if substr(p_first_noac_lookup_code, 1, 1) = '1'
426 -- and
427 -- hr_utility.is_numeric(p_credit_mil_svc)
428 and
429 to_number(p_credit_mil_svc) > 0 and
430 p_service_computation_date >= p_effective_date then
434
431 hr_utility.set_message(8301, 'GHR_37612_ALL_PROCEDURE_FAIL');
432 hr_utility.raise_error;
433 end if;
435 end chk_service_comp_date;
436
437 /* Name:
438 -- Social Security
439 */
440
441
442 procedure chk_Social_Security
443 ( p_agency_sub in varchar2 --non SF52
444 ,p_employee_National_ID in varchar2
445 ,p_personnel_officer_ID in varchar2 --non SF52
446 ,p_effective_date in date --Bug 5487271
447 ) is
448 l_dummy varchar2(1);
449 cursor c_found_poi is
450 select '1' from ghr_pois
451 where personnel_office_id =
452 substr(p_employee_National_ID,2,2)||substr(p_employee_National_ID,5,2);
453 begin
454
455 -- Bug 1854488 -- 3/22/02
456 -- 680.00.1 Part B, Note
457 -- Update Date By Effective Date Bug Comment
458 -- 30-OCT-2003 ajose From the Begining 3237673 Added HSDA.
459 -- 14-Nov-2005 Raju From the Begining 4567571 Rename the Edit 690.00.1 to 680.00.1
460 -- 26-sep-2006 amrchakr 01-jul-2006 5487271 Changes edit from 680.00.1 to 680.00.3
461 -- and modified 680.00.3 to remove agency comparision of DJ02 or HSDA
462
463 if p_effective_date < to_date('01/07/2006','dd/mm/yyyy') then
464 if p_agency_sub NOT IN ('HSDA','DJ02') and
465 (
466 substr(p_employee_National_ID,1,1) = '8' or
467 substr(p_employee_National_ID,1,1) = '9'
468 ) then
469 Begin
470 open c_found_poi;
471 fetch c_found_poi into l_dummy;
472 if c_found_poi%notfound then
473
474 hr_utility.set_message(8301, 'GHR_37613_ALL_PROCEDURE_FAIL');
475 hr_utility.raise_error;
476 end if;
477 close c_found_poi;
478 end;
479 end if;
480 else
481 if
482 substr(p_employee_National_ID,1,1) = '8' or
483 substr(p_employee_National_ID,1,1) = '9'
484 then
485 Begin
486 open c_found_poi;
487 fetch c_found_poi into l_dummy;
488 if c_found_poi%notfound then
489 hr_utility.set_message(8301, 'GHR_37698_ALL_PROCEDURE_FAIL');
490 hr_utility.raise_error;
491 end if;
492 close c_found_poi;
493 end;
494 end if;
495 end if;
496
497 -- 680.00.2 Part C, Note
498 -- Update Date By Effective Date Bug Comment
499 -- 18-OCT-2002 vnarasim From the Begining 2631140 Modified first 3 positions
500 -- of SSN from less than 738 to
501 -- less than 800.
502 -- 14-Nov-2005 Raju From the Begining 4567571 Rename the Edit 690.00.2 to 680.00.2
503 -- 26-sep-2006 amrchakr 01-jul-2006 5487271 Changes edit from 680.00.2 to 680.00.3
504 if (
505 substr(p_employee_National_ID,1,1) = '8' or
506 substr(p_employee_National_ID,1,1) = '9'
507 ) then
508 Begin
509 open c_found_poi;
510 fetch c_found_poi into l_dummy;
511 if c_found_poi%notfound then
512 if p_effective_date < to_date('01/07/2006','dd/mm/yyyy') then
513 hr_utility.set_message(8301, 'GHR_37614_ALL_PROCEDURE_FAIL');
514 hr_utility.raise_error;
515 else
516 hr_utility.set_message(8301, 'GHR_37699_ALL_PROCEDURE_FAIL');
517 hr_utility.raise_error;
518 end if;
519 end if;
520 close c_found_poi;
521 end;
522 end if;
523
524 if (
525 substr(p_employee_National_ID,1,1) <> '8' and
526 substr(p_employee_National_ID,1,1) <> '9'
527 ) and
528 substr(p_employee_National_ID,1,3) not between '000' and '800'
529 then
530 if p_effective_date < to_date('01/07/2006','dd/mm/yyyy') then
531 hr_utility.set_message(8301, 'GHR_37615_ALL_PROCEDURE_FAIL');
532 hr_utility.raise_error;
533 else
534 hr_utility.set_message(8301, 'GHR_38478_ALL_PROCEDURE_FAIL');
535 hr_utility.raise_error;
536 end if;
537 end if;
538
539 end chk_Social_Security;
540
541
542 /* Name:
543 -- Step or Rate
544 */
545
546
547 procedure chk_step_or_rate
548 (p_step_or_rate in varchar2
549 ,p_pay_rate_determinant in varchar2
550 ,p_to_pay_plan in varchar2
551 ,p_to_grade_or_level in varchar2
552 ,p_first_action_noa_la_code1 in varchar2
553 ,p_first_action_noa_la_code2 in varchar2
554 ,p_Cur_Appt_Auth_1 in varchar2 --non SF52 item
555 ,p_Cur_Appt_Auth_2 in varchar2 --non SF52 item
556 ,p_effective_date in date
557 ,p_rpa_step_or_rate in varchar2
558 ) is
559
560 begin
561
562 -- 700.02.3
563 -- Update Date By Effective Date Bug Comment
564 -- 9 09/14/99 vravikan 01-Mar-1999 992944 Exclude T
565 -- 06/25/03 vravikan Trigger this edit if the g_temp_step is not null
566 -- and prd is '0' or '6'
570
567 -- 06/26/03 vravikan Use p_rpa_step_or_rate if g_temp_step is not null
568 -- becuase p_step_or_rate contains TPS value
569 -- 12/11/08 Raju Start date 7633560 Exclude D
571 IF GHR_GHRWS52L.g_temp_step is not null THEN
572 if p_effective_date >= fnd_date.canonical_to_date('1999/03/01') then
573 if (
574 p_pay_rate_determinant in ('0','6','2','3','4','A','B','C','E','F','G','H',
575 'I','J','K','L','N','O',
576 'Q','R','S','U','V','W','X','Y','Z')
577 ) and
578 p_to_pay_plan not in ('WT','FA','EX')
579 and
580 p_rpa_step_or_rate <> '00'
581 and
582 p_rpa_step_or_rate is not null
583 then
584 hr_utility.set_message(8301, 'GHR_37186_ALL_PROCEDURE_FAIL');
585 hr_utility.raise_error;
586 end if;
587 else
588 if (
589 p_pay_rate_determinant in ('0','6','2','3','4','A','B','C','E','F','G','H','I','J','K','L','N','O',
590 'Q','R','S','T','U','V','W','X','Y','Z')
591 ) and
592 p_to_pay_plan not in ('WT','FA','EX')
593 and
594 p_rpa_step_or_rate <> '00'
595 and
596 p_rpa_step_or_rate is not null
597 then
598 hr_utility.set_message(8301, 'GHR_37616_ALL_PROCEDURE_FAIL');
599 hr_utility.raise_error;
600 end if;
601 end if;
602 ELSE
603 if p_effective_date >= fnd_date.canonical_to_date('1999/03/01') then
604 if (
605 p_pay_rate_determinant in ('2','3','4','A','B','C','E','F','G','H',
606 'I','J','K','L','N','O',
607 'Q','R','S','U','V','W','X','Y','Z')
608 ) and
609 p_to_pay_plan not in ('WT','FA','EX')
610 and
611 p_step_or_rate <> '00'
612 and
613 p_step_or_rate is not null
614 then
615 hr_utility.set_message(8301, 'GHR_37186_ALL_PROCEDURE_FAIL');
616 hr_utility.raise_error;
617 end if;
618 else
619 if (
620 p_pay_rate_determinant in ('2','3','4','A','B','C','E','F','G','H','I','J','K','L','N','O',
621 'Q','R','S','T','U','V','W','X','Y','Z')
622 ) and
623 p_to_pay_plan not in ('WT','FA','EX')
624 and
625 p_step_or_rate <> '00'
626 and
627 p_step_or_rate is not null
628 then
629 hr_utility.set_message(8301, 'GHR_37616_ALL_PROCEDURE_FAIL');
630 hr_utility.raise_error;
631 end if;
632 end if;
633 END IF;
634
635 -- 700.04.3
636 -- -- Madhuri 19-MAY-2004 Included VP in the list
637 --
638 if (p_to_pay_plan in ('GG','GS','VP'))
639 and
640 p_Cur_Appt_Auth_1 <> 'UAM'
641 and
642 p_Cur_Appt_Auth_2 <> 'UAM'
643 and
644 (p_to_grade_or_level between '01' and '15')
645 and
646 p_pay_rate_determinant in ('0','5','6','7')
647 and
648 p_step_or_rate not in ('01','02','03','04','05','06','07','08',
649 '09','10','11','12','13','14','15')
650 and
651 p_step_or_rate is not null
652 then
653 hr_utility.set_message(8301, 'GHR_37617_ALL_PROCEDURE_FAIL');
654 hr_utility.raise_error;
655 end if;
656
657 -- 700.07.3
658 -- Madhuri 19-MAY-2004 Removed VP from the list
659 -- 13-Jun-06 Raju Terminate the edit eff from 01-Jan-03
660 --
661 IF p_effective_date < fnd_date.canonical_to_date('2003/01/01') then --Bug# 5073313
662 if (p_to_pay_plan ='VM' ) and /*or p_to_pay_plan ='VP') */
663 p_to_grade_or_level <> 97 and
664 p_step_or_rate not in ('01','02','03','04','05','06',
665 '07','08','09','10') and
666 p_step_or_rate is not null
667 then
668 hr_utility.set_message(8301, 'GHR_37618_ALL_PROCEDURE_FAIL');
669 hr_utility.raise_error;
670 end if;
671 END IF;
672
673 -- 700.10.3
674 -- Update/Change Date By Effective Date Comment
675 -- 13-Jun-06 Raju 01-Jan-03 Terminate the edit
676 IF p_effective_date < fnd_date.canonical_to_date('2003/01/01') then --Bug# 5073313
677 if p_to_pay_plan = 'VM' and p_to_grade_or_level = '97' and
678 p_step_or_rate not in ('01','02','03','04','05','06',
679 '07','08','09') and
680 p_step_or_rate is not null
681 then
682 hr_utility.set_message(8301, 'GHR_37619_ALL_PROCEDURE_FAIL');
683 hr_utility.raise_error;
684 end if;
685 END IF;
686
687 -- 700.12.3
688 if p_to_pay_plan = 'VN' and
689 p_step_or_rate not in ('00','01','02','03','04','05','06','07',
690 '08','09','10','11','12','13','14','15',
691 '16','17','18','19','20','21','22','23',
692 '24','25','26','27','28')
693 and
694 p_step_or_rate is not null
695 then
696 hr_utility.set_message(8301, 'GHR_37620_ALL_PROCEDURE_FAIL');
697 hr_utility.raise_error;
698 end if;
699
700 -- 700.14.3
701 -- Update 7 on 16 jun 98. Added 00 to step or rate
702 --upd47 26-Jun-06 Raju From 01-Apr-2006 Added 611,613
706 p_step_or_rate is not null then
703 if p_effective_date < fnd_date.canonical_to_date('2006/04/01') then
704 if p_to_pay_plan = 'XE' and
705 p_step_or_rate not in ('00','01','02','03') and
707 hr_utility.set_message(8301, 'GHR_37621_ALL_PROCEDURE_FAIL');
708 hr_utility.set_message_token('STEP_RATE','00 through 03');
709 hr_utility.raise_error;
710 end if;
711 else
712 if p_to_pay_plan = 'XE' and
713 p_step_or_rate not in ('01','02','03') and
714 p_step_or_rate is not null then
715 hr_utility.set_message(8301, 'GHR_37621_ALL_PROCEDURE_FAIL');
716 hr_utility.set_message_token('STEP_RATE','01 through 03');
717 hr_utility.raise_error;
718 end if;
719 end if;
720
721 -- 700.16.3
722 if (p_to_pay_plan ='CE' or p_to_pay_plan ='CY') and
723 p_step_or_rate not in ('00','01','02','03','04','05','06','07',
724 '08','09','10','11','12','13','14','15',
725 '16','17','18','19','20','21')
726 and
727 p_step_or_rate is not null
728 then
729 hr_utility.set_message(8301, 'GHR_37622_ALL_PROCEDURE_FAIL');
730 hr_utility.raise_error;
731 end if;
732
733 -- 700.19.3
734 -- 17-Aug-00 vravikan 01-jan-2000 Delete 99
735 -- 06-Aug-07 Raju 5132113 Added GR Phy and Dentist change
736 if p_effective_date >= to_date('1999/01/01','yyyy/mm/dd') then
737 if p_to_pay_plan in ('GR','GM') and p_step_or_rate <> '00' and
738 p_step_or_rate is not null
739 then
740 hr_utility.set_message(8301, 'GHR_37436_ALL_PROCEDURE_FAIL');
741 hr_utility.set_message_token('PAY_PLAN',p_to_pay_plan);--Bug# 6341069
742 hr_utility.raise_error;
743 end if;
744 else
745 if p_to_pay_plan = 'GM' and
746 (p_step_or_rate <> '00' and p_step_or_rate <> '99')
747 and
748 p_step_or_rate is not null
749 then
750 hr_utility.set_message(8301, 'GHR_37623_ALL_PROCEDURE_FAIL');
751 hr_utility.raise_error;
752 end if;
753 end if;
754
755 -- 700.22.2
756 -- EOY'02 Patch 13-NOV-02 vnarasim End dated the Edit as of 31-MAY-2001
757
758 if p_effective_date <= to_date('31/05/2001','dd/mm/yyyy') then
759 if (p_to_pay_plan ='ES' or p_to_pay_plan ='FE') and
760 p_step_or_rate not in ('01','02','03','04','05','06') and
761 p_step_or_rate is not null then
762 hr_utility.set_message(8301, 'GHR_37624_ALL_PROCEDURE_FAIL');
763 hr_utility.raise_error;
764 end if;
765 end if;
766
767 -- 700.31.3
768 if p_to_pay_plan = 'FO' and
769 p_step_or_rate not in ('01','02','03','04','05','06','07','08',
770 '09','10','11','12','13','14')
771 and
772 p_step_or_rate is not null
773 then
774 hr_utility.set_message(8301, 'GHR_376_ALL_PROCEDURE_FAIL');
775 hr_utility.raise_error;
776 end if;
777
778 -- 700.34.3
779 if p_to_pay_plan = 'FP' and
780 p_to_grade_or_level in ('01','02','03','04','05','06','07','08','09') and
781 p_step_or_rate not in ('01','02','03','04','05','06','07','08',
782 '09','10','11','12','13','14')
783 and
784 p_step_or_rate is not null
785 then
786 hr_utility.set_message(8301, 'GHR_37626_ALL_PROCEDURE_FAIL');
787 hr_utility.raise_error;
788 end if;
789
790 -- 700.35.3
791 if p_to_pay_plan = 'FP' and
792 p_to_grade_or_level in ('AA','BB','CC','DD','EE') and
793 p_step_or_rate not in ('01','02','03','04','05')
794 and
795 p_step_or_rate is not null
796 then
797 hr_utility.set_message(8301, 'GHR_37627_ALL_PROCEDURE_FAIL');
798 hr_utility.raise_error;
799 end if;
800
801 -- 700.37.3
802 if p_to_pay_plan = 'AF' and
803 p_step_or_rate not in ('01','02','03','04','05')
804 and
805 p_step_or_rate is not null
806 then
807 hr_utility.set_message(8301, 'GHR_37628_ALL_PROCEDURE_FAIL');
808 hr_utility.raise_error;
809 end if;
810
811 -- 700.40.3
812 if p_to_pay_plan = 'FC' and
813 p_to_grade_or_level in ('02','03','04','05','06','07',
814 '08','09','10','11','12') and
815 p_step_or_rate not in ('01','02','03','04','05','06',
816 '07','08','09','10')
817 and
818 p_step_or_rate is not null
819 then
820 hr_utility.set_message(8301, 'GHR_37629_ALL_PROCEDURE_FAIL');
821 hr_utility.raise_error;
822 end if;
823
824 -- 700.43.3
825 if p_to_pay_plan = 'FC' and p_to_grade_or_level = '13' and
826 p_step_or_rate not in ('01','02','03','04','05',
827 '06','07','08','09')
828 and
829 p_step_or_rate is not null
830 then
831 hr_utility.set_message(8301, 'GHR_37630_ALL_PROCEDURE_FAIL');
832 hr_utility.raise_error;
833 end if;
834
835 -- 700.47.3
836 if p_to_pay_plan = 'FC' and p_to_grade_or_level = '14' and
837 p_step_or_rate not in ('01','02','03','04','05')
838 and
839 p_step_or_rate is not null
840 then
841 hr_utility.set_message(8301, 'GHR_37631_ALL_PROCEDURE_FAIL');
845 -- 700.50.3
842 hr_utility.raise_error;
843 end if;
844
846 if p_to_pay_plan in ('CA','SL','ST') and
847 p_step_or_rate <> '00'
848 and
849 p_step_or_rate is not null
850 then
851
852 hr_utility.set_message(8301, 'GHR_37632_ALL_PROCEDURE_FAIL');
853 hr_utility.raise_error;
854 end if;
855
856 -- 700.55.3
857 if p_to_pay_plan = 'AL' and
858 (p_to_grade_or_level = '01' or p_to_grade_or_level = '02') and
859 (p_step_or_rate <> '00'
860 and
861 p_step_or_rate is not null)
862 then
863 hr_utility.set_message(8301, 'GHR_37633_ALL_PROCEDURE_FAIL');
864 hr_utility.raise_error;
865 end if;
866
867 -- 700.60.3
868 if p_to_pay_plan = 'AL' and p_to_grade_or_level = '03' and
869 -- p_step_or_rate not in ('A','B','C','D','E','F') -- bug 611870
870 p_step_or_rate not in ('01','02','03','04','05','06')
871 and
872 p_step_or_rate is not null
873 then
874 hr_utility.set_message(8301, 'GHR_37634_ALL_PROCEDURE_FAIL');
875 hr_utility.raise_error;
876 end if;
877
878 -- 700.62.3
879 if p_to_pay_plan = 'GG' and p_to_grade_or_level = 'SL' and
880 p_step_or_rate <>'00'
881 and
882 p_step_or_rate is not null
883 then
884 hr_utility.set_message(8301, 'GHR_37635_ALL_PROCEDURE_FAIL');
885 hr_utility.raise_error;
886 end if;
887
888 -- 700.65.3
889 if p_to_pay_plan = 'GG' and
890 p_to_grade_or_level in ('01','02','03','04','05',
891 '06','07','08','09','10',
892 '11','12','13','14','15') and
893 p_pay_rate_determinant in ('0','5','6','7') and
894 (p_Cur_Appt_Auth_1 = 'UAM' or p_Cur_Appt_Auth_2 = 'UAM') and
895 p_step_or_rate not in ('01','02','03','04','05','06',
896 '07','08','09','10','11','12')
897 and
898 p_step_or_rate is not null
899 then
900 hr_utility.set_message(8301, 'GHR_37636_ALL_PROCEDURE_FAIL');
901 hr_utility.raise_error;
902 end if;
903
904 -- 700.67.3
905 if p_to_pay_plan = 'FG'
906 and
907 p_step_or_rate not in ('01','02','03','04','05','06',
908 '07','08','09','10', '00')
909 and
910 p_step_or_rate is not null
911 then
912 hr_utility.set_message(8301, 'GHR_37637_ALL_PROCEDURE_FAIL');
913 hr_utility.raise_error;
914 end if;
915
916 -- 700.69.3
917 /* If pay plan is IJ,
918 And pay rate determinant is 0 or 7,
919 Then step or rate must be 01 through 04 or asterisks.
920 Default: Insert asterisks in step or rate. */
921
922 if p_to_pay_plan = 'IJ' and
923 p_pay_rate_determinant in ('0','7') and
924 p_step_or_rate not in ('01','02','03','04') and
925 p_step_or_rate is not null then
926 hr_utility.set_message(8301, 'GHR_38552_ALL_PROCEDURE_FAIL');
927 hr_utility.raise_error;
928 end if;
929
930
931 end chk_step_or_rate;
932
933 /* Name:
934 -- Supervisory Status
935 */
936
937
938 procedure chk_supervisory_status
939 (p_supervisory_status_code in varchar2
940 ,p_to_pay_plan in varchar2
941 ,p_effective_date in date
942 ) is
943 begin
944
945 -- 710.07.3
946 if p_to_pay_plan in ('BS','JR','JT','KS','NS','WA',
947 'WN','WQ','WS','XC','XN','XS')
948 and
949 p_supervisory_status_code <>'2'
950 and
951 p_supervisory_status_code is not null
952 then
953 hr_utility.set_message(8301, 'GHR_37638_ALL_PROCEDURE_FAIL');
954 hr_utility.raise_error;
955 end if;
956
957 -- 710.10.3
958 if p_to_pay_plan = 'FA' and
959 p_supervisory_status_code <>'2'
960 and
961 p_supervisory_status_code is not null
962 then
963 hr_utility.set_message(8301, 'GHR_37639_ALL_PROCEDURE_FAIL');
964 hr_utility.raise_error;
965 end if;
966
967 -- 710.13.3
968 if p_to_pay_plan in ('ES','EX','FE') and
969 (p_supervisory_status_code <>'2' and p_supervisory_status_code <>'8')
970 and
971 p_supervisory_status_code is not null
972 then
973 hr_utility.set_message(8301, 'GHR_37640_ALL_PROCEDURE_FAIL');
974 hr_utility.raise_error;
975 end if;
976
977 -- 710.16.3
978 if p_to_pay_plan = 'GM' and
979 p_supervisory_status_code not in ('2','4','5','7')
980 and
981 p_supervisory_status_code is not null
982 then
983 hr_utility.set_message(8301, 'GHR_37641_ALL_PROCEDURE_FAIL');
984 hr_utility.raise_error;
985 end if;
986
987 -- 710.19.3
988 if p_to_pay_plan in ('BL','JL','JQ','KL','NL',
989 'WL','WO','WR','XB','XL') and
990 (p_supervisory_status_code <>'6')
991 and
992 p_supervisory_status_code is not null
993 then
994 hr_utility.set_message(8301, 'GHR_37642_ALL_PROCEDURE_FAIL');
995 hr_utility.raise_error;
996 end if;
997
998 -- 710.22.3
999 if p_to_pay_plan in ('BB','ED','EE','EF','EG',
1003 (p_supervisory_status_code <> '4' and p_supervisory_status_code <> '8')
1000 'EH','EI','JG','JP','KG',
1001 'NA','WD','WG','WK','WT',
1002 'WU','WY','XA','XD','XP') and
1004 and
1005 p_supervisory_status_code is not null
1006 then
1007 hr_utility.set_message(8301, 'GHR_37643_ALL_PROCEDURE_FAIL');
1008 hr_utility.raise_error;
1009 end if;
1010 -- 710.25.3
1011 -- Dec 2001 Patch 1-Sep-2001 Delete WZ
1012 if p_effective_date <= to_date('2000/08/31','yyyy/mm/dd') THEN
1013 if p_to_pay_plan in ('BP','WB','WE','WM','WZ') and
1014 p_supervisory_status_code not in ('2','6','8') and
1015 p_supervisory_status_code is not null then
1016 hr_utility.set_message(8301, 'GHR_37644_ALL_PROCEDURE_FAIL');
1017 hr_utility.raise_error;
1018 end if;
1019 else
1020 if p_to_pay_plan in ('BP','WB','WE','WM') and
1021 p_supervisory_status_code not in ('2','6','8') and
1022 p_supervisory_status_code is not null then
1023 hr_utility.set_message(8301, 'GHR_37923_ALL_PROCEDURE_FAIL');
1024 hr_utility.raise_error;
1025 end if;
1026 end if;
1027
1028 end chk_supervisory_status;
1029
1030
1031 /* Name:
1032 -- Tenure
1033 */
1034
1035 procedure chk_tenure
1036 (p_tenure_group_code in varchar2
1037 ,p_to_pay_plan in varchar2
1038 ,p_first_action_noa_la_code1 in varchar2
1039 ,p_first_action_noa_la_code2 in varchar2
1040 ,p_first_noac_lookup_code in varchar2
1041 ,p_Cur_Appt_Auth_1 in varchar2 --non SF52 item
1042 ,p_Cur_Appt_Auth_2 in varchar2 --non SF52 item
1043 ,p_effective_date in date
1044 ) is
1045 begin
1046
1047 -- 720.02.3
1048 if p_to_pay_plan ='ES' and p_tenure_group_code <> 0 then
1049 hr_utility.set_message(8301, 'GHR_37645_ALL_PROCEDURE_FAIL');
1050 hr_utility.raise_error;
1051 end if;
1052
1053 -- 720.04.3
1054 if p_to_pay_plan in ('VM','VN','VP')
1055 and
1056 p_tenure_group_code <>'1'
1057 and
1058 p_tenure_group_code is not null
1059 then
1060 hr_utility.set_message(8301, 'GHR_37646_ALL_PROCEDURE_FAIL');
1061 hr_utility.raise_error;
1062 end if;
1063
1064 -- 720.08.3
1065 if (p_Cur_Appt_Auth_1 in ('Y7M', 'Y8M', 'Y9K', 'Y9M') or
1066 p_Cur_Appt_Auth_2 in ('Y7M', 'Y8M', 'Y9K', 'Y9M')) and
1067 p_tenure_group_code not in ('0', '3' ) and
1068 p_tenure_group_code is not null
1069 then
1070 hr_utility.set_message(8301, 'GHR_37647_ALL_PROCEDURE_FAIL');
1071 hr_utility.raise_error;
1072 end if;
1073
1074 -- 720.13.2
1075 -- Updated_by Updated_on Effective_Date Description
1076 -- amrchakr 28-sep-2006 01-jul-2003 Delete NOA's 151,155,157,551,555
1077
1078 if p_effective_date < to_date('2003/07/01','yyyy/mm/dd') THEN
1079 if p_first_noac_lookup_code in ('100','130','140','151','155',
1080 '157','500','540','551','555') and
1081 p_tenure_group_code <> '1' and
1082 p_tenure_group_code <> '2' and
1083 p_tenure_group_code is not null
1084 then
1085 hr_utility.set_message(8301, 'GHR_37648_ALL_PROCEDURE_FAIL');
1086 hr_utility.raise_error;
1087 end if;
1088 else
1089 if p_first_noac_lookup_code in ('100','130','140','500','540') and
1090 p_tenure_group_code <> '1' and
1091 p_tenure_group_code <> '2' and
1092 p_tenure_group_code is not null
1093 then
1094 hr_utility.set_message(8301, 'GHR_37700_ALL_PROCEDURE_FAIL');
1095 hr_utility.raise_error;
1096 end if;
1097 end if;
1098
1099 -- 720.16.2
1100 -- Updated_by Updated_on Effective_Date Description
1101 -- amrchakr 28-sep-2006 01-jul-2003 Delete NOA's 150, 550
1102
1103 if p_effective_date < to_date('2003/07/01','yyyy/mm/dd') THEN
1104 if p_first_noac_lookup_code in ('101','141','150','501','541','550') and
1105 p_tenure_group_code <>'2' and
1106 p_tenure_group_code is not null then
1107 hr_utility.set_message(8301, 'GHR_37649_ALL_PROCEDURE_FAIL');
1108 hr_utility.raise_error;
1109 end if;
1110 else
1111 if p_first_noac_lookup_code in ('101','141','501','541') and
1112 p_tenure_group_code <>'2' and
1113 p_tenure_group_code is not null then
1114 hr_utility.set_message(8301, 'GHR_37587_ALL_PROCEDURE_FAIL');
1115 hr_utility.raise_error;
1116 end if;
1117 end if;
1118
1119 --UPDATED_BY DATE COMMENTS
1120 ------------------------------------------------------------
1121 -- Madhuri 14-SEP-2004 Removed the NOACS- 112, 512
1122 --amrchakr 28-sep-2006 Delete NOA's 153,154,553,554
1123 --Raju 11-Oct-2007 Delete NOA's 171,571 Bug#6469079
1124 -------------------------------------------------------------
1125
1126 -- 720.19.2
1127 if p_effective_date < to_date('01/03/1998', 'dd/mm/yyyy') then
1128 if p_first_noac_lookup_code in ('107','108','115','117','120',
1129 '122','124','153','154','171','190',
1130 '507','508','515','517','520',
1134 p_tenure_group_code is not null then
1131 '522','524','553','554','571','590') and
1132 p_tenure_group_code <> '0' and
1133 p_tenure_group_code <> '3' and
1135 hr_utility.set_message(8301, 'GHR_37650_ALL_PROCEDURE_FAIL');
1136 hr_utility.raise_error;
1137 end if;
1138 elsif p_effective_date < to_date('2003/07/01','yyyy/mm/dd') THEN
1139 -- removed noac 117 and 517 as per update 7
1140 -- removed noacs 112, 512 for EOY I
1141 if p_first_noac_lookup_code in ('107','108','115','120',
1142 '122','124','153','154','171','190',
1143 '507','508','515','520',
1144 '522','524','553','554','571','590') and
1145 p_tenure_group_code <> '0' and
1146 p_tenure_group_code <> '3' and
1147 p_tenure_group_code is not null then
1148 hr_utility.set_message(8301, 'GHR_37870_ALL_PROCEDURE_FAIL');
1149 hr_utility.raise_error;
1150 end if;
1151 elsif p_effective_date < to_date('2007/05/01','RRRR/MM/DD') THEN -- Bug#6469079
1152 if p_first_noac_lookup_code in ('107','108','115','120',
1153 '122','124','171','190',
1154 '507','508','515','520',
1155 '522','524','571','590') and
1156 p_tenure_group_code <> '0' and
1157 p_tenure_group_code <> '3' and
1158 p_tenure_group_code is not null then
1159 hr_utility.set_message(8301, 'GHR_37588_ALL_PROCEDURE_FAIL');
1160 hr_utility.set_message_token('NOAC','107, 108, 115, 120, 122, 124, 171, 190, 507, 508, 515, 520, 522, 524, 571, or 590');
1161 hr_utility.raise_error;
1162 end if;
1163 ELSE--Begin Bug#6469079
1164 if p_first_noac_lookup_code in ('107','108','115','120',
1165 '122','124','190',
1166 '507','508','515','520',
1167 '522','524','590') and
1168 p_tenure_group_code <> '0' and
1169 p_tenure_group_code <> '3' and
1170 p_tenure_group_code is not null then
1171 hr_utility.set_message(8301, 'GHR_37588_ALL_PROCEDURE_FAIL');
1172 hr_utility.set_message_token('NOAC','107, 108, 115, 120, 122, 124, 190, 507, 508, 515, 520, 522, 524, or 590');
1173 hr_utility.raise_error;
1174 end if;--End Bug#6469079
1175 end if;
1176
1177
1178 -- 720.22.2
1179 -- Dec 01 Patch 12/10/01 vravikan From the Begining -- Add YCM
1180 -- 01-Oct-01 -- Delete YAM and Y4M
1181 --upd47 26-Jun-06 Raju From 01-Apr-2006 Added pay plan condition
1182 if p_effective_date < fnd_date.canonical_to_date('2006/04/01') then
1183 if p_effective_date >= to_date('2001/10/01','yyyy/mm/dd') then
1184 if p_first_action_noa_la_code1 in ('YBM','YCM','YGM',
1185 'Y1M','Y2M','Y3M') and
1186 p_tenure_group_code <>'2' and
1187 p_tenure_group_code is not null then
1188 hr_utility.set_message(8301, 'GHR_37919_ALL_PROCEDURE_FAIL');
1189 hr_utility.raise_error;
1190 end if;
1191 else
1192 if p_first_action_noa_la_code1 in ('YAM','YBM','YCM','YGM',
1193 'Y1M','Y2M','Y3M','Y4M') and
1194 p_tenure_group_code <>'2' and
1195 p_tenure_group_code is not null then
1196 hr_utility.set_message(8301, 'GHR_37651_ALL_PROCEDURE_FAIL');
1197 hr_utility.raise_error;
1198 end if;
1199 end if;
1200 else
1201 if p_first_action_noa_la_code1 in ('YAM','YBM','YCM','YGM',
1202 'Y1M','Y2M','Y3M','Y4M') and
1203 substr(p_to_pay_plan,1,1) in ('Y') and
1204 p_tenure_group_code <>'2' and
1205 p_tenure_group_code is not null then
1206 hr_utility.set_message(8301, 'GHR_37187_ALL_PROCEDURE_FAIL');
1207 hr_utility.raise_error;
1208 end if;
1209 end if;
1210
1211 -- 720.25.2
1212 if p_first_noac_lookup_code in ('170', '570') and
1213 p_first_action_noa_la_code1 not in ('ZKM','ZNM') and
1214 p_first_action_noa_la_code2 not in ('ZKM','ZNM') and
1215 p_tenure_group_code = '0' then
1216 hr_utility.set_message(8301, 'GHR_37652_ALL_PROCEDURE_FAIL');
1217 hr_utility.raise_error;
1218 end if;
1219
1220 -- 720.28.2
1221 -- added effective dates on 16-jul-1998
1222 if p_effective_date < to_date('01/03/1998', 'dd/mm/yyyy') then
1223 if (p_first_noac_lookup_code in ('760','761')) and
1224 p_tenure_group_code <> '0' and
1225 p_tenure_group_code <> '3' and
1226 p_tenure_group_code is not null then
1227 hr_utility.set_message(8301, 'GHR_37653_ALL_PROCEDURE_FAIL');
1228 hr_utility.raise_error;
1229 end if;
1230 else
1231 if (p_first_noac_lookup_code = '760') and
1232 p_tenure_group_code <> '0' and
1233 p_tenure_group_code <> '3' and
1234 p_tenure_group_code is not null then
1235 hr_utility.set_message(8301, 'GHR_37869_ALL_PROCEDURE_FAIL');
1236 hr_utility.raise_error;
1237 end if;
1238 end if;
1239
1240 -- 720.31.2
1241 if p_first_noac_lookup_code = '765' and
1242 p_tenure_group_code <>'3' and
1246 end if;
1243 p_tenure_group_code is not null then
1244 hr_utility.set_message(8301, 'GHR_37654_ALL_PROCEDURE_FAIL');
1245 hr_utility.raise_error;
1247
1248 -- 720.34.2
1249 if (p_first_noac_lookup_code = '892' or p_first_noac_lookup_code = '893')
1250 and
1251 p_to_pay_plan = 'GS' and
1252 p_tenure_group_code = '0' then
1253 hr_utility.set_message(8301, 'GHR_37655_ALL_PROCEDURE_FAIL');
1254 hr_utility.raise_error;
1255 end if;
1256
1257 -- 720.35.3
1258 --UPDATED_BY DATE COMMENTS
1259 ------------------------------------------------------------
1260 --amrchakr 28-sep-2006 Remove the tenure 3 from 01/07/2006
1261 -------------------------------------------------------------
1262
1263 if p_effective_date < to_date('01/07/2006', 'dd/mm/yyyy') then
1264 if (p_Cur_Appt_Auth_1 = 'ZKM' or p_Cur_Appt_Auth_2 = 'ZKM') and
1265 (p_to_pay_plan ='AD' or p_to_pay_plan ='EX') and
1266 ( p_tenure_group_code <> '0' and p_tenure_group_code <> '3')
1267 then
1268 hr_utility.set_message(8301, 'GHR_37656_ALL_PROCEDURE_FAIL');
1269 hr_utility.raise_error;
1270 end if;
1271 else
1272 if (p_Cur_Appt_Auth_1 = 'ZKM' or p_Cur_Appt_Auth_2 = 'ZKM') and
1273 (p_to_pay_plan ='AD' or p_to_pay_plan ='EX') and
1274 p_tenure_group_code <> '0'
1275 then
1276 hr_utility.set_message(8301, 'GHR_37589_ALL_PROCEDURE_FAIL');
1277 hr_utility.raise_error;
1278 end if;
1279 end if;
1280
1281 -- 720.40.3
1282 if (p_Cur_Appt_Auth_1 = 'ZNM' or p_Cur_Appt_Auth_2 = 'ZNM') and
1283 (p_to_pay_plan ='AD' or p_to_pay_plan ='EX') and
1284 p_tenure_group_code <> '0' then
1285 hr_utility.set_message(8301, 'GHR_37657_ALL_PROCEDURE_FAIL');
1286 hr_utility.raise_error;
1287 end if;
1288
1289 end chk_tenure;
1290
1291 /* Name:
1292 -- Veterans Preference
1293 */
1294
1295 procedure chk_veterans_pref
1296 (p_veterans_preference_code in varchar2
1297 ,p_first_action_noa_la_code1 in varchar2
1298 ,p_first_action_noa_la_code2 in varchar2
1299 ) is
1300 begin
1301
1302 -- 750.02.2
1303 if (p_first_action_noa_la_code1 in ('LBM','LZM','NEM','MMM') or
1304 p_first_action_noa_la_code2 in ('LBM','LZM','NEM','MMM')) and
1305 p_veterans_preference_code not in ('2','3','4','6') and
1306 p_veterans_preference_code is not null then
1307 hr_utility.set_message(8301, 'GHR_37658_ALL_PROCEDURE_FAIL');
1308 hr_utility.raise_error;
1309 end if;
1310
1311 end chk_veterans_pref;
1312
1313 /* Name:
1314 -- Veterans Status
1315 */
1316
1317
1318 procedure chk_veterans_status
1319 (p_veterans_status_code in varchar2
1320 ,p_veterans_preference_code in varchar2
1321 ,p_first_noac_lookup_code in varchar2
1322 ,p_agency_sub in varchar2 --non SF52
1323 ,p_first_action_noa_la_code1 in varchar2
1324 ) is
1325 begin
1326
1327 --760.00.1 From Part B, notes
1328 if p_veterans_status_code is null and
1329 (
1330 p_agency_sub not in ('AFNG','AFZG','ARNG')
1331 or
1332 (p_agency_sub = 'CM63' and p_first_action_noa_la_code1 = 'XZM')
1333 )
1334 then
1335 hr_utility.set_message(8301, 'GHR_37659_ALL_PROCEDURE_FAIL');
1336 hr_utility.raise_error;
1337 end if;
1338
1339 -- 760.05.3
1340 if p_veterans_status_code = 'X' and
1341 p_veterans_preference_code in ('2','3','4','6') then
1342 hr_utility.set_message(8301, 'GHR_37660_ALL_PROCEDURE_FAIL');
1343 hr_utility.raise_error;
1344 end if;
1345
1346 -- 760.10.2
1347 -- Updation Date Updated By Remarks
1348 -- ============================================
1349 -- 19-MAR-2003 vnarasim Added Other than 132 condition.
1350
1351 if substr(p_first_noac_lookup_code,1,1) = '1' and
1352 p_first_noac_lookup_code <> '132' and
1353 p_veterans_status_code = 'N' then
1354 hr_utility.set_message(8301, 'GHR_37661_ALL_PROCEDURE_FAIL');
1355 hr_utility.raise_error;
1356 end if;
1357
1358 end chk_veterans_status;
1359
1360 /* Name:
1361 -- Work Schedule
1362 */
1363
1364 procedure chk_work_schedule
1365 (p_work_schedule_code in varchar2
1366 ,p_first_noac_lookup_code in varchar2
1367 ) is
1368 begin
1369
1370 -- 770.02.2
1371 -- 28-Nov-2002 Madhuri removed NOA Code 430
1372 if (p_first_noac_lookup_code ='280') and
1373 -- or p_first_noac_lookup_code ='430')
1374 (NVL(p_work_schedule_code,'G') not in ('G','Q','J','T')) then
1375 hr_utility.set_message(8301, 'GHR_37662_ALL_PROCEDURE_FAIL');
1376 hr_utility.raise_error;
1377 end if;
1378
1379 end chk_work_schedule;
1380 --
1381 --
1382 --
1383 procedure chk_degree_attained
1384 ( p_effective_date in date
1385 ,p_year_degree_attained in varchar2
1386 ,p_as_of_date in date
1387 ) is
1388 begin
1389 -- 780.03.1
1390 /* as_of_date is the same as effective date.
1391 if p_year_degree_attained is not null then
1392 if NOT (p_year_degree_attained <= p_as_of_date) then
1393 hr_utility.set_message(8301, 'GHR_99997_ALL_PROCEDURE_FAIL');
1394 hr_utility.raise_error;
1395 end if;
1396 end if;
1397 */
1398 -- 780.03.2
1399 if p_year_degree_attained is not null then
1400 if NOT (to_number(p_year_degree_attained) <=
1401 to_number(to_char(p_effective_date,'YYYY'))) then
1402 hr_utility.set_message(8301, 'GHR_38411_ALL_PROCEDURE_FAIL');
1403 hr_utility.raise_error;
1404 end if;
1405 end if;
1406
1407
1408 end chk_degree_attained;
1409
1410
1411 end GHR_CPDF_CHECK6;
[Home] [Help]