1 package body GHR_CPDF_CHECK1 as
2 /* $Header: ghcpdf01.pkb 120.11.12020000.3 2012/12/05 04:59:57 utokachi ship $ */
3
4 /* Name:
5 Bargaining Unit
6 */
7
8
9 procedure chk_bargaining_unit
10 (p_to_pay_plan in varchar2
11 ,p_agency_sub_element in varchar2 --non SF52
12 ,p_bargaining_unit_status_code in varchar2
13 ) is
14 begin
15
16 -- 060.02.1
17 if p_to_pay_plan = 'ES'
18 and
19 p_bargaining_unit_status_code <> '8888'
20 and
21 p_bargaining_unit_status_code is not null
22 then
23 hr_utility.set_message(8301, 'GHR_37001_ALL_PROCEDURE_FAIL');
24 hr_utility.raise_error;
25 end if;
26
27 /* Commented as per December 2000 cpdf changes -- vravikan
28 -- 060.04.1
29 if p_agency_sub_element in ('AF07','ARAS','DD12','LT00','NV15','TRAC' )
30 and
31 p_bargaining_unit_status_code <> '8888'
32 and
33 p_bargaining_unit_status_code is not null
34 then
35 hr_utility.set_message(8301, 'GHR_37002_ALL_PROCEDURE_FAIL');
36 hr_utility.raise_error;
37 end if;
38 */
39 end chk_bargaining_unit ;
40
41 /* Name:
42 Federal Employees Group Life Insurance
43 */
44
45 procedure chk_fegli
46 (p_to_basic_pay in varchar2
47 ,p_to_pay_plan in varchar2
48 ,p_fegli_code in varchar2
49 ,p_effective_date in date
50 ) is
51 begin
52
53 --170.04.1
54 -- Update Date By Effective Date Comment
55 -- ? 05/06/99 vravikan 04/25/99 Fegli changes
56 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/25') then
57 if p_to_basic_pay = '0'
58 and
59 p_to_pay_plan <> 'VC'
60 and
61 p_fegli_code <> 'A0'
62 and
63 p_fegli_code is not null
64 then
65 hr_utility.set_message(8301, 'GHR_37038_ALL_PROCEDURE_FAIL');
66 hr_utility.raise_error;
67 end if;
68 else
69 if p_to_basic_pay = '0'
70 and
71 p_to_pay_plan <> 'VC'
72 and
73 p_fegli_code <> 'A'
74 and
75 p_fegli_code is not null
76 then
77 hr_utility.set_message(8301, 'GHR_37003_ALL_PROCEDURE_FAIL');
78 hr_utility.raise_error;
79 end if;
80 end if;
81 end chk_fegli;
82
83 /* Name:
84 FSLA Category
85 */
86
87 procedure chk_fsla_category
88 (p_duty_station_lookup_code in varchar2
89 ,p_to_pay_plan in varchar2
90 ,p_agency_subelement in varchar2
91 ,p_flsa_category in varchar2
92 ,p_to_grade_or_level in varchar2
93 ,p_effective_date in date --Bug# 5619873
94 )is
95 begin
96 --Venkat 03/27/2000 -- Bug # 1246822 -- replacing or with and
97
98 -- 180.02.1
99 --upd49 08-Jan-07 Raju Bug#5619873 From 01-Sep-2006 Terminate the edit
100 if p_effective_date < fnd_date.canonical_to_date('2006/09/01') then
101 if (substr(upper(p_duty_station_lookup_code),1,2) between 'AA' and 'ZZ')
102 and (substr(p_duty_station_lookup_code,1,2)<>'US'
103 and -- Bug # 1246822
104 substr(p_duty_station_lookup_code,2,1)<>'Q' )
105 and p_flsa_category <> 'E'
106 and p_flsa_category is not null
107 then
108 hr_utility.set_message(8301, 'GHR_37004_ALL_PROCEDURE_FAIL');
109 hr_utility.raise_error;
110 end if;
111 end if;
112 -- 180.04.1
113 --upd49 08-Jan-07 Raju Bug#5619873 From 01-Sep-2006 Terminate the edit
114 if p_effective_date < fnd_date.canonical_to_date('2006/09/01') then
115 if (p_to_pay_plan = 'WG' or p_to_pay_plan = 'WL')
116 and
117 p_agency_subelement not in ('AFNG','AFZG','ARNG')
118 and
119 ((substr(p_duty_station_lookup_code,1,2) between '00' and '99')
120 or
121 substr(p_duty_station_lookup_code,1,2)='US'
122 or
123 substr(p_duty_station_lookup_code,2,1)='Q')
124 and
125 p_flsa_category <> 'N'
126 and
127 p_flsa_category is not null
128 then
129 hr_utility.set_message(8301, 'GHR_37005_ALL_PROCEDURE_FAIL');
130 hr_utility.raise_error;
131 end if;
132 end if;
133 -- 180.07.1
134 --upd49 08-Jan-07 Raju Bug#5619873 From 01-Sep-2006 Terminate the edit
135 if p_effective_date < fnd_date.canonical_to_date('2006/09/01') then
136 if p_to_pay_plan = 'GS'
137 and
138 p_agency_subelement not in ('AFNG','AFZG','ARNG')
139 and
140 p_to_grade_or_level between '01' and '04'
141 and
142 (substr(p_duty_station_lookup_code,1,2) between '00' and '99'
143 or
144 substr(p_duty_station_lookup_code,1,2)='US'
145 or
146 substr(p_duty_station_lookup_code,2,1)='Q')
147 and
148 p_flsa_category <>'N'
149 and
150 p_flsa_category is not null
151 then
152 hr_utility.set_message(8301, 'GHR_37006_ALL_PROCEDURE_FAIL');
153 hr_utility.raise_error;
154 end if;
155 end if;
156 -- 180.09.1
157 -- UPD 56 (Bug# 8309414) edit adding eff 07-sep-2007
158 if p_effective_date > fnd_date.canonical_to_date('2007/09/06') then
159 IF (NOT(substr(p_duty_station_lookup_code,1,2) between '00' and '99') AND
160 substr(p_duty_station_lookup_code,1,2) NOT IN ('AQ','CQ','GQ','JQ','LQ','MQ','RQ','VQ','WQ') )
161 AND p_flsa_category <>'E' THEN
162 hr_utility.set_message(8301, 'GHR_37452_ALL_PROCEDURE_FAIL');
163 hr_utility.raise_error;
164 END IF;
165 END IF;
166
167 -- 180.13.1
168 if p_to_pay_plan in ('AL','CA','ES','EX','SL','ST')
169 and
170 p_flsa_category <> 'E'
171 and
172 p_flsa_category is not null
173 then
174 hr_utility.set_message(8301, 'GHR_37007_ALL_PROCEDURE_FAIL');
175 hr_utility.raise_error;
176 end if;
177
178 end chk_fsla_category;
179
180
181 /* Name:
182 Functional Classification
183 */
184
185 procedure chk_functional_classification
186 (p_to_occ_code in varchar2
187 ,p_functional_class in varchar2
188 ,p_effective_date in date --Bug# 5619873
189 ) is
190 begin
191
192 -- 200.04.1
193 --upd49 08-Jan-07 Raju Bug#5619873 From 01-Oct-2006 change 0470-0493 to 0470-0487
194 -- remove occu code 1540
195 --Bug # 9471636 17-Mar-2010 Mani Added 1501
196 -- U58 Raju 01-jan-2010 Bug 9503972
197 if p_effective_date < fnd_date.canonical_to_date('2006/10/01') then
198
199 if (p_to_occ_code in
200 ('0020','0101','0110','0140','0150','0170','0180','0184'
201 ,'0185','0190','0193','0401','0403','0457','0460','0601'
202 ,'0602','0610','0644','0660','0662','0665','0668','0680'
203 ,'0690','0696','0701','0801','0803','0804','0810','0819'
204 ,'0896','1220','1221','1350','1360','1370','1372','1373'
205 ,'1501','1529','1530','1540','1550')
206 or p_to_occ_code between '0405' and '0415'
207 or p_to_occ_code between '0430' and '0454'
208 or p_to_occ_code between '0470' and '0493'
209 or p_to_occ_code between '0630' and '0635'
210 or p_to_occ_code between '0637' and '0639'
211 or p_to_occ_code between '0806' and '0808'
212 or p_to_occ_code between '0830' and '0855'
213 or p_to_occ_code between '0858' and '0871'
214 or p_to_occ_code between '0880' and '0894'
215 or p_to_occ_code between '1223' and '1226'
216 or p_to_occ_code between '1301' and '1310'
217 or p_to_occ_code between '1313' and '1315'
218 or p_to_occ_code between '1320' and '1340'
219 or p_to_occ_code between '1380' and '1386'
220 or p_to_occ_code between '1510' and '1520')
221 and to_number(p_functional_class) <= 10
222 and p_functional_class is not null
223 then
224 hr_utility.set_message(8301, 'GHR_37008_ALL_PROCEDURE_FAIL');
225 hr_utility.raise_error;
226 end if;
227 elsif p_effective_date < fnd_date.canonical_to_date('2010/01/01') then
228 if (p_to_occ_code in
229 ('0020','0101','0110','0140','0150','0170','0180','0184'
230 ,'0185','0190','0193','0401','0403','0457','0460','0601'
231 ,'0602','0610','0644','0660','0662','0665','0668','0680'
232 ,'0690','0696','0701','0801','0803','0804','0810','0819'
233 ,'0896','1220','1221','1350','1360','1370','1372','1373'
234 ,'1501','1529','1530','1550')
235 or p_to_occ_code between '0405' and '0415'
236 or p_to_occ_code between '0430' and '0454'
237 or p_to_occ_code between '0470' and '0487'
238 or p_to_occ_code between '0630' and '0635'
239 or p_to_occ_code between '0637' and '0639'
240 or p_to_occ_code between '0806' and '0808'
241 or p_to_occ_code between '0830' and '0855'
242 or p_to_occ_code between '0858' and '0871'
243 or p_to_occ_code between '0880' and '0894'
244 or p_to_occ_code between '1223' and '1226'
245 or p_to_occ_code between '1301' and '1310'
246 or p_to_occ_code between '1313' and '1315'
247 or p_to_occ_code between '1320' and '1340'
248 or p_to_occ_code between '1380' and '1386'
249 or p_to_occ_code between '1510' and '1520')
250 and to_number(p_functional_class) <= 10
251 and p_functional_class is not null
252 then
253 hr_utility.set_message(8301, 'GHR_37008_ALL_PROCEDURE_FAIL');
254 hr_utility.raise_error;
255 end if;
256 else
257 if (p_to_occ_code in
258 ('0020','0101','0110','0140','0150','0170','0180','0184'
259 ,'0185','0190','0193','0401','0403','0457','0460','0601'
260 ,'0602','0610','0644','0660','0662','0665','0668','0680'
261 ,'0690','0696','0701','0801','0803','0804','0810','0819'
262 ,'0896','1220','1221','1350','1360','1370','1372','1373'
263 ,'1501','1529','1530','1550')
264 or p_to_occ_code between '0405' and '0415'
265 or p_to_occ_code between '0430' and '0435'
266 or p_to_occ_code between '0437' and '0454'
267 or p_to_occ_code between '0470' and '0487'
268 or p_to_occ_code between '0630' and '0635'
269 or p_to_occ_code between '0637' and '0639'
270 or p_to_occ_code between '0806' and '0808'
271 or p_to_occ_code between '0830' and '0855'
272 or p_to_occ_code between '0858' and '0871'
273 or p_to_occ_code between '0880' and '0894'
274 or p_to_occ_code between '1223' and '1226'
275 or p_to_occ_code between '1301' and '1310'
276 or p_to_occ_code between '1313' and '1315'
277 or p_to_occ_code between '1320' and '1340'
278 or p_to_occ_code between '1380' and '1386'
279 or p_to_occ_code between '1510' and '1520')
280 and to_number(p_functional_class) <= 10
281 and p_functional_class is not null
282 then
283 hr_utility.set_message(8301, 'GHR_37008_ALL_PROCEDURE_FAIL');
284 hr_utility.raise_error;
285 end if;
286 end if;
287
288 -- 200.07.1
289 --upd49 08-Jan-07 Raju Bug#5619873 From 01-Oct-2006 change 0470-0493 to 0470-0487
290 -- remove occu code 1540
291 --Bug # 9471636 17-Mar-2010 Mani Added 1501
292 if p_effective_date < fnd_date.canonical_to_date('2006/10/01') then
293 if (p_to_occ_code not in
294 ('0020','0101','0110','0140','0150','0170','0180','0184'
295 ,'0185','0190','0193','0401','0403','0457','0460','0601'
296 ,'0602','0610','0644','0660','0662','0665','0668','0680'
297 ,'0690','0696','0701','0801','0803','0804','0810','0819'
298 ,'0896','1220','1221','1350','1360','1370','1372','1373'
299 ,'1501','1529','1530','1540','1550')
300 and p_to_occ_code not between '0405' and '0415'
301 and p_to_occ_code not between '0430' and '0454'
302 and p_to_occ_code not between '0470' and '0493'
303 and p_to_occ_code not between '0630' and '0635'
304 and p_to_occ_code not between '0637' and '0639'
305 and p_to_occ_code not between '0806' and '0808'
306 and p_to_occ_code not between '0830' and '0855'
307 and p_to_occ_code not between '0858' and '0871'
308 and p_to_occ_code not between '0880' and '0894'
309 and p_to_occ_code not between '1223' and '1226'
310 and p_to_occ_code not between '1301' and '1310'
311 and p_to_occ_code not between '1313' and '1315'
312 and p_to_occ_code not between '1320' and '1340'
313 and p_to_occ_code not between '1380' and '1386'
314 and p_to_occ_code not between '1510' and '1520')
315 and p_functional_class <> '00'
316 and p_functional_class is not null
317 then
318 hr_utility.set_message(8301, 'GHR_37009_ALL_PROCEDURE_FAIL');
319 hr_utility.raise_error;
320 end if;
321 else
322 if (p_to_occ_code not in
323 ('0020','0101','0110','0140','0150','0170','0180','0184'
324 ,'0185','0190','0193','0401','0403','0457','0460','0601'
325 ,'0602','0610','0644','0660','0662','0665','0668','0680'
326 ,'0690','0696','0701','0801','0803','0804','0810','0819'
327 ,'0896','1220','1221','1350','1360','1370','1372','1373'
328 ,'1501','1529','1530','1550')
329 and p_to_occ_code not between '0405' and '0415'
330 and p_to_occ_code not between '0430' and '0454'
331 and p_to_occ_code not between '0470' and '0487'
332 and p_to_occ_code not between '0630' and '0635'
333 and p_to_occ_code not between '0637' and '0639'
334 and p_to_occ_code not between '0806' and '0808'
335 and p_to_occ_code not between '0830' and '0855'
336 and p_to_occ_code not between '0858' and '0871'
337 and p_to_occ_code not between '0880' and '0894'
338 and p_to_occ_code not between '1223' and '1226'
339 and p_to_occ_code not between '1301' and '1310'
340 and p_to_occ_code not between '1313' and '1315'
341 and p_to_occ_code not between '1320' and '1340'
342 and p_to_occ_code not between '1380' and '1386'
343 and p_to_occ_code not between '1510' and '1520')
344 and p_functional_class <> '00'
345 and p_functional_class is not null
346 then
347 hr_utility.set_message(8301, 'GHR_37009_ALL_PROCEDURE_FAIL');
348 hr_utility.raise_error;
349 end if;
350 end if;
351
352 end chk_functional_classification;
353
354 /* Name:
355 HEALTH PLAN
356 */
357
358 procedure chk_health_plan
359 (p_health_plan in varchar2 --non SF52
360 ,p_tenure_group_code in varchar2
361 ,p_work_schedule_code in varchar2
362 ,p_to_pay_basis in varchar2
363 ,p_to_pay_status in varchar2 --non SF52
364 ,p_submission_date in date --non SF52
365 ,p_Cur_Appt_Auth_1 in varchar2
366 ,p_Cur_Appt_Auth_2 in varchar2
367 ) is
368
369 begin
370
371 /* not supported in the product due to unsolved submission date
372 -- 230.00.1 note: this procedure is from Part B Notes section.
373 if p_health_plan is null
374 and
375 to_char(p_submission_date,'MM') <> '03'
376 and
377 to_char(p_submission_date,'MM') <> '09'
378 then
379 hr_utility.set_message(8301, 'GHR_370_ALL_PROCEDURE_FAIL');
380 hr_utility.raise_error;
381 end if;
382 */
383
384 -- 230.02.01
385 if p_health_plan = 'ZZZ'
386 and
387 p_tenure_group_code <> '0'
388 and
389 p_tenure_group_code <> '3'
390 and
391 p_tenure_group_code is not null
392 and
393 p_work_schedule_code not in ('G','I','J','Q')
394 and
395 p_work_schedule_code is not null
396 and
397 p_to_pay_basis not in ('PW','FB','WC')
398 and
399 p_to_pay_basis is not null
400 and
401 p_Cur_Appt_Auth_1 not in ('YAM','YBM','YGM','Y1M','Y2M','Y3M','Y4M')
402 and
403 p_Cur_Appt_Auth_1 is not null
404 and
405 p_Cur_Appt_Auth_2 not in ('YAM','YBM','YGM','Y1M','Y2M','Y3M','Y4M')
406 and
407 p_Cur_Appt_Auth_2 is not null
408 -- and
409 -- p_to_pay_status <> 'N'
410
411 then
412 hr_utility.set_message(8301, 'GHR_37031_ALL_PROCEDURE_FAIL');
413 hr_utility.raise_error;
414 end if;
415
416 end chk_health_plan;
417
418
419
420 /* Name:
421 -- Retained Grade
422 */
423
424 procedure chk_retain_grade
428 ,p_effective_date in date
425 (p_retain_pay_plan in varchar2 --non SF52
426 ,p_retain_grade in varchar2 --non SF52
427 ,p_pay_rate_determinant_code in varchar2
429 ) is
430
431 begin
432
433 -- 610.02.1
434 if p_retain_pay_plan = 'WL'
435 and
436 p_retain_grade not between '01' and '15'
437 then
438 hr_utility.set_message(8301, 'GHR_37029_ALL_PROCEDURE_FAIL');
439 hr_utility.raise_error;
440 end if;
441
442 -- 610.04.1
443 if p_retain_pay_plan = 'WS'
444 and
445 p_retain_grade not between '01' and '19'
446 then
447 hr_utility.set_message(8301, 'GHR_37010_ALL_PROCEDURE_FAIL');
448 hr_utility.raise_error;
449 end if;
450
451 /* Commented as per December 2000 cpdf changes -- vravikan
452 -- 610.07.1
453 if p_retain_pay_plan = 'GM'
454 and
455 p_retain_grade not between '14' and '15'
456 then
457 hr_utility.set_message(8301, 'GHR_37011_ALL_PROCEDURE_FAIL');
458 hr_utility.raise_error;
459 end if;
460 */
461
462 -- 610.10.1
463 if p_retain_pay_plan = 'GS'
464 and
465 p_retain_grade not between '01' and '15'
466 then
467 hr_utility.set_message(8301, 'GHR_37012_ALL_PROCEDURE_FAIL');
468 hr_utility.raise_error;
469 end if;
470
471 -- 610.13.1
472 if p_retain_pay_plan = 'WG'
473 and
474 p_retain_grade not between '01' and '15'
475 then
476 hr_utility.set_message(8301, 'GHR_37013_ALL_PROCEDURE_FAIL');
477 hr_utility.raise_error;
478 end if;
479
480
481 -- 610.16.1
482 -- UPD 43(Bug 4567571) Raju 09-Nov-2005 Delete PRD M effective date from 01-May-2005
483 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
484 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
485 if p_effective_date < fnd_date.canonical_to_date('2005/05/01') then
486 if p_retain_grade is not null and
487 p_pay_rate_determinant_code not in ('A','B','E','F','M','U','V')
488 then
489 hr_utility.set_message(8301, 'GHR_37014_ALL_PROCEDURE_FAIL');
490 hr_utility.set_message_token('PRD_LIST','A, B, E, F, M, U, or V');
491 hr_utility.raise_error;
492 end if;
493 elsif p_effective_date >= fnd_date.canonical_to_date('2005/05/01') then
494 if p_retain_grade is not null and
495 p_pay_rate_determinant_code not in ('A','B','E','F','U','V')
496 then
497 hr_utility.set_message(8301, 'GHR_37014_ALL_PROCEDURE_FAIL');
498 hr_utility.set_message_token('PRD_LIST','A, B, E, F, U, or V');
499 hr_utility.raise_error;
500 end if;
501 end if;
502 end if;
503 -- 610.19.1
504 if p_pay_rate_determinant_code in ('A','B','E','F','U','V')
505 and
506 p_retain_grade is null
507 then
508 hr_utility.set_message(8301, 'GHR_37015_ALL_PROCEDURE_FAIL');
509 hr_utility.raise_error;
510 end if;
511
512 /* Commented as per December 2000 cpdf changes -- vravikan
513 -- 610.25.1
514 if p_retain_pay_plan = 'GH'
515 and
516 p_retain_grade <>'14'
517 and
518 p_retain_grade <>'15'
519 and
520 p_retain_grade is not null
521 then
522 hr_utility.set_message(8301, 'GHR_37016_ALL_PROCEDURE_FAIL');
523 hr_utility.raise_error;
524 end if;
525 */
526
527 -- 610.30.1
528 if p_retain_pay_plan = 'GG'
529 and
530 p_retain_grade not between '01' and '15'
531 and
532 p_retain_grade is not null
533 then
534 hr_utility.set_message(8301, 'GHR_37017_ALL_PROCEDURE_FAIL');
535 hr_utility.raise_error;
536 end if;
537
538 end chk_retain_grade;
539
540 /* Name:
541 -- Retained Pay Plan
542 */
543
544 procedure chk_retain_pay_plan
545 (p_retain_grade in varchar2 --non SF52
546 ,p_retain_pay_plan in varchar2 --non SF52
547 ,p_retain_step in varchar2 --non SF52
548 ,p_to_pay_plan in varchar2
549 ,p_pay_rate_determinant_code in varchar2
550 ,p_effective_date in date
551
552 ) is
553 begin
554 -- 620.02.1
555 -- If Pay rate determinant is A, B, E, F, U, or V,
556 -- Then retained pay plan may not be GH or GM
557 -- 12/8/00 vravikan 01-Oct-2000 New Edit
558 if p_effective_date >= fnd_date.canonical_to_date('2000/10/01') then
559 if p_pay_rate_determinant_code is not null and
560 p_pay_rate_determinant_code in ('A','B','E','F','U','V') and
561 p_retain_pay_plan in ('GH','GM') then
562 hr_utility.set_message(8301, 'GHR_37663_ALL_PROCEDURE_FAIL');
563 hr_utility.raise_error;
564 end if;
565 end if;
566
567
568 -- 620.04.1
569 if p_retain_grade is not null
570 and
571 (p_retain_pay_plan is null
572 or
573 p_retain_step is null )
574 then
575 hr_utility.set_message(8301, 'GHR_37018_ALL_PROCEDURE_FAIL');
576 hr_utility.raise_error;
577 end if;
578
579 /* Commented as per December 2000 cpdf changes -- vravikan
580 -- 620.10.1
581 if p_to_pay_plan = 'GS'
582 and
583 p_retain_pay_plan = 'GM'
584 then
585 hr_utility.set_message(8301, 'GHR_37019_ALL_PROCEDURE_FAIL');
586 hr_utility.raise_error;
587 end if;
588 */
589
590 /* Commented as per December 2000 cpdf changes -- vravikan
591 -- 620.13.1
595 then
592 if p_to_pay_plan = 'GM'
593 and
594 p_retain_pay_plan = 'GS'
596 hr_utility.set_message(8301, 'GHR_37020_ALL_PROCEDURE_FAIL');
597 hr_utility.raise_error;
598 end if;
599
600 */
601 end chk_retain_pay_plan;
602
603 /* Name:
604 -- Retained Step
605 */
606
607 procedure chk_retain_step
608 (p_pay_rate_determinant_code 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_retain_pay_plan in varchar2 --non SF52
614 ,p_retain_grade in varchar2 --non SF52
615 ,p_retain_step in varchar2 --non SF52
616 ,p_effective_date in date
617 ) is
618 begin
619
620 -- 630.02.1
621 -- UPD 43(Bug 4567571) Raju 09-Nov-2005 Delete PRD M effective date from 01-May-2005
622 if p_effective_date < to_date('2005/05/01','yyyy/mm/dd') then
623 if p_pay_rate_determinant_code in ('A','B','E','F','M') and
624 p_Cur_Appt_Auth_1 <> 'UAM' and
625 p_Cur_Appt_Auth_2 <> 'UAM' and
626 (p_retain_pay_plan = 'GS' or
627 p_retain_pay_plan ='GG') and
628 p_retain_grade between '01' and '15' and
629 p_retain_step not between '01' and '10' and
630 p_retain_step is not null
631 then
632 hr_utility.set_message(8301, 'GHR_37021_ALL_PROCEDURE_FAIL');
633 hr_utility.set_message_token('PRD_LIST','A, B, E, F, or M');
634 hr_utility.raise_error;
635 end if;
636 elsif p_effective_date >= to_date('2005/05/01','yyyy/mm/dd') then
637 if p_pay_rate_determinant_code in ('A','B','E','F') and
638 p_Cur_Appt_Auth_1 <> 'UAM' and
639 p_Cur_Appt_Auth_2 <> 'UAM' and
640 (p_retain_pay_plan = 'GS' or
641 p_retain_pay_plan ='GG') and
642 p_retain_grade between '01' and '15' and
643 p_retain_step not between '01' and '10' and
644 p_retain_step is not null
645 then
646 hr_utility.set_message(8301, 'GHR_37021_ALL_PROCEDURE_FAIL');
647 hr_utility.set_message_token('PRD_LIST','A, B, E, or F');
648 hr_utility.raise_error;
649 end if;
650 end if;
651
652
653 -- 630.04.1
654 /* Commented as per December 2000 cpdf changes -- vravikan
655 -- 17-Aug-00 vravikan 01-jan-2000 Delete 99 from step codes
656 if p_effective_date >= to_date('2000/01/01','yyyy/mm/dd') then
657 if p_pay_rate_determinant_code in ('A','B','E','F','M')
658 and
659 p_retain_pay_plan = 'GM'
660 and
661 p_retain_step <> '00'
662 and
663 p_retain_step is not null
664 then
665 hr_utility.set_message(8301, 'GHR_37413_ALL_PROCEDURE_FAIL');
666 hr_utility.raise_error;
667 end if;
668 else
669 if p_pay_rate_determinant_code in ('A','B','E','F','M')
670 and
671 p_retain_pay_plan = 'GM'
672 and
673 p_retain_step <> '00'
674 and
675 p_retain_step <> '99'
676 and
677 p_retain_step is not null
678 then
679 hr_utility.set_message(8301, 'GHR_37022_ALL_PROCEDURE_FAIL');
680 hr_utility.raise_error;
681 end if;
682 end if;
683 */
684
685 -- 630.07.1
686 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
687 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
688 if (p_pay_rate_determinant_code ='U'
689 or
690 p_pay_rate_determinant_code = 'V')
691 and
692 p_retain_pay_plan <> 'WT'
693 and
694 p_retain_step <> '00'
695 and
696 p_retain_step is not null
697 then
698 hr_utility.set_message(8301, 'GHR_37023_ALL_PROCEDURE_FAIL');
699 hr_utility.raise_error;
700 end if;
701 end if;
702 end chk_retain_step;
703
704
705 /* Name:
706 -- Retirement Plan
707 */
708
709 procedure chk_retirement_plan
710 (p_retirement_plan_code in varchar2
711 ,p_fers_coverage in varchar2 --non SF52
712 ,p_effective_date in date
713 ) is
714 --Bug# 15941036 added effective date parameter
715 begin
716
717 -- 640.11.1
718 --Bug# 10189715 added retirement plans O, P
719 --Bug# 13572333 added nvl for p_fers_coverage
720 --Bug# 15941036 added Retirement codes KR,LR,MR,NR,OR,PR
721 IF p_effective_date < fnd_date.canonical_to_date('2013/01/01') THEN
722 if p_retirement_plan_code in ('K','L','M','N','O','P')
723 and NVL(p_fers_coverage,'#') NOT IN ( 'A', 'E' )
724 then
725 hr_utility.set_message(8301, 'GHR_37024_ALL_PROCEDURE_FAIL');
726 hr_utility.set_message_token('RET_PLAN','K, L, M, N, O or P');
727 hr_utility.raise_error;
728 end if;
729 --Begin Bug# 15941036
730 ELSE
731 IF p_retirement_plan_code in ('K','L','M','N','O','P','KR','LR','MR','NR','OR','PR')
732 AND NVL(p_fers_coverage,'#') NOT IN ( 'A', 'E' ) THEN
733 hr_utility.set_message(8301, 'GHR_37024_ALL_PROCEDURE_FAIL');
734 hr_utility.set_message_token('RET_PLAN','K, KR, L, LR, M, MR, N, NR, O, OR, P or PR');
735 hr_utility.raise_error;
736 END IF;
737 END IF;
738 --End Bug# 15941036
739 -- 640.14.1
740 --Bug# 10189715 added retirement plans O
741 --Bug# 15941036 added Retirement codes KR,LR,MR,NR,OR,PR
742 IF p_effective_date < fnd_date.canonical_to_date('2013/01/01') THEN
743 if (p_fers_coverage = 'A'
744 or
745 p_fers_coverage = 'E')
746 and
747 p_retirement_plan_code not in ('K','L','M','N','O','P')
748 and
752 hr_utility.set_message_token('RET_PLAN','K, L, M, N, O or P');
749 p_retirement_plan_code is not null
750 then
751 hr_utility.set_message(8301, 'GHR_37025_ALL_PROCEDURE_FAIL');
753 hr_utility.raise_error;
754 end if;
755 --Begin Bug# 15941036
756 ELSE
757 IF p_fers_coverage IN ('A','E')
758 AND p_retirement_plan_code NOT IN ('K','L','M','N','O','P','KR','LR','MR','NR','OR','PR')
759 AND p_retirement_plan_code IS NOT NULL THEN
760 hr_utility.set_message(8301, 'GHR_37025_ALL_PROCEDURE_FAIL');
761 hr_utility.set_message_token('RET_PLAN','K, KR, L, LR, M, MR, N, NR, O, OR, P or PR');
762 hr_utility.raise_error;
763 END IF;
764 END IF;
765 --End Bug# 15941036
766 end chk_retirement_plan;
767
768
769 /* Name:
770 -- special_pay_table_id
771 */
772
773 procedure chk_special_pay_table_id
774 (p_pay_rate_determinant_code in varchar2
775 ,p_to_pay_plan in varchar2
776 ,p_special_pay_table_id in varchar2 --non SF52
777 -- FWFA Changes Bug#4444609
778 ,p_effective_date in date
779 -- FWFA Changes
780 ) is
781 begin
782
783
784 -- Update Date By Effective Date Comment
785 -- ? 01/28/99 vravikan 01/10/98 Commented - Bug 808117
786 /*
787 -- 695.05.1
788 if p_pay_rate_determinant_code not in ('5','6','E','F','M')
789 and
790 p_special_pay_table_id is not null
791 then
792 hr_utility.set_message(8301, 'GHR_37026_ALL_PROCEDURE_FAIL');
793 hr_utility.raise_error;
794 end if;
795 */
796
797 -- 695.10.1
798 -- Modified as a part of FWFA Changes. Restricted the edit to actions processed
799 -- before 01-MAY-2005. For actions processed on or after 01-MAY-2005, this edit
800 -- will be skipped.
801 -- FWFA Changes Bug#4444609
802 -- UPD 43(Bug 4567571) Raju 09-Nov-2005 Delete PRD M effective date from 01-May-2005
803
804 IF p_effective_date < to_date('2005/05/01','yyyy/mm/dd') THEN
805 if p_to_pay_plan in ('GM','GS')
806 and
807 p_pay_rate_determinant_code in ('5','6','E','F','M')
808 and
809 p_special_pay_table_id is null
810 then
811 hr_utility.set_message(8301, 'GHR_37027_ALL_PROCEDURE_FAIL');
812 hr_utility.set_message_token('PRD_LIST','5, 6, E, F, or M');
813 hr_utility.raise_error;
814 end if;
815 ELSIF p_effective_date >= to_date('2005/05/01','yyyy/mm/dd') THEN
816 if p_to_pay_plan in ('GM','GS') and
817 p_pay_rate_determinant_code in ('5','6','E','F') and
818 p_special_pay_table_id is null
819 then
820 hr_utility.set_message(8301, 'GHR_37027_ALL_PROCEDURE_FAIL');
821 hr_utility.set_message_token('PRD_LIST','5, 6, E, or F');
822 hr_utility.raise_error;
823 end if;
824 END IF;
825
826 -- FWFA Changes
827 end chk_special_pay_table_id ;
828
829
830
831 /* Name:
832 -- U.S. Citizenship
833 */
834
835 procedure chk_us_citizenship
836 (p_citizenship in varchar2
837 ,p_duty_station_lookup_code in varchar2
838 ) is
839 begin
840 null;
841
842 /* COMMENTED on 16-OCT-2002 as per NOV'02 FP Requirements -- VNARASIM
843 -- 740.02.1
844 -- No changes made as edit is end dated : amrchakr
845 if p_citizenship <> '1'
846 and
847 substr(p_duty_station_lookup_code,1,2) <> 'US'
848 and
849 substr(p_duty_station_lookup_code,2,1) <> 'Q'
850 and
851 substr(p_duty_station_lookup_code,1,1) not in ('1','2','3','4','5','6','7','8','9','0')
852 and
853 substr(p_duty_station_lookup_code,2,1) not in ('1','2','3','4','5','6','7','8','9','0')
854 and
855 p_duty_station_lookup_code is not null
856 then
857 hr_utility.set_message(8301, 'GHR_37028_ALL_PROCEDURE_FAIL');
858 hr_utility.raise_error;
859 end if;*/
860
861 end chk_us_citizenship;
862 --
863 --
864 procedure chk_century_info (
865 p_date_of_birth in date
866 ,p_effective_date in date
867 ,p_Service_Computation_Date in date
868 ,p_year_degree_attained in varchar2
869 ,p_rating_of_record_period in varchar2
870 ,p_rating_of_record_per_starts in varchar2
871 ) is
872 begin
873 --
874 -- Procedure added to check the century info.
875 --
876 -- 110.00.1
877 if to_number(to_char(p_date_of_birth, 'YYYY')) NOT BETWEEN 1900 and 2099 then
878 hr_utility.set_message(8301, 'GHR_37887_ALL_PROCEDURE_FAIL');
879 hr_utility.raise_error;
880 end if;
881
882 -- 140.00.2
883 if to_number(to_char(p_effective_date, 'YYYY')) NOT BETWEEN 1900 and 2099 then
884 hr_utility.set_message(8301, 'GHR_37888_ALL_PROCEDURE_FAIL');
885 hr_utility.raise_error;
886 end if;
887
888 -- 660.00.1
889
890 -- renamed from 660.00.1 to 600.00.3
891 -- renamed back to 660.00.1 from 660.00.3 on 12-oct-98 for update 8
892
893 -- Update Date By Effective Date Comment
894 -- ? 05/06/99 vravikan 660.00.1 has changed - if condition is
895 -- removed as both branchings refer to
896 -- same message
897 if to_number(to_char(p_service_computation_date, 'YYYY'))
898 NOT BETWEEN 1900 and 2099 then
899 hr_utility.set_message(8301, 'GHR_37889_ALL_PROCEDURE_FAIL');
900 hr_utility.raise_error;
901 end if;
902
903 -- 780.00.3
907 end if;
904 if to_number(p_year_degree_attained) NOT BETWEEN 1900 and 2099 then
905 hr_utility.set_message(8301, 'GHR_37890_ALL_PROCEDURE_FAIL');
906 hr_utility.raise_error;
908
909 -- 472.00.3
910 if to_number(to_char(fnd_date.canonical_to_date(p_rating_of_record_period), 'yyyy'))
911 NOT BETWEEN 1900 and 2099 then
912 hr_utility.set_message(8301, 'GHR_37891_ALL_PROCEDURE_FAIL');
913 hr_utility.raise_error;
914 end if;
915 --Bug# 4753117 28-Feb-07 Veeramani adding edit for the Appraisal start date
916 if to_number(to_char(fnd_date.canonical_to_date(p_rating_of_record_per_starts), 'yyyy'))
917 NOT BETWEEN 1900 and 2099 then
918 hr_utility.set_message(8301, 'GHR_37891_ALL_PROCEDURE_FAIL');
919 hr_utility.raise_error;
920 end if;
921
922
923
924 end chk_century_info;
925
926 end GHR_CPDF_CHECK1;
[Home] [Help]