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