1 package body GHR_CPDF_CHECK5 as
2 /* $Header: ghcpdf05.pkb 120.18.12010000.3 2008/12/16 11:59:12 utokachi ship $ */
3
4 max_per_diem number(5) := 1000;
5 min_basic_pay number(10,2);
6 max_basic_pay number(10,2);
7
8
9 /* Name:
10 --RATING OF RECORD
11 */
12
13
14 procedure chk_rating_of_rec
15 (p_rating_of_record_level in varchar2 --non SF52
16 ,p_rating_of_record_pattern in varchar2 --non SF52
17 ,p_rating_of_record_period in varchar2 --non SF52
18 ,p_rating_of_record_per_starts in varchar2
19 ,p_first_noac_lookup_code in varchar2
20 ,p_effective_date in date
21 ,p_submission_date in date --non SF52
22 ,p_to_pay_plan in varchar2
23 ) is
24
25 l_end_date date;
26 l_start_date date;
27 begin
28
29 /* this procedure is commented out requested by functional people*/
30 --Uncommented for bug 3084133
31 -- upd49 19-Jan-07 Raju From Beginning Bug#5619873 817
32 -- upd55 11-Oct-07 Raju From 01-May-2007 Bug#6469079 add pattren condition
33 -- 470.02.2
34 IF p_effective_date < to_date('2007/05/01','RRRR/MM/DD') then --Bug#6469079
35 if p_first_noac_lookup_code not in ('001','817') and
36 p_rating_of_record_level is null then
37 hr_utility.set_message(8301, 'GHR_37501_ALL_PROCEDURE_FAIL');
38 hr_utility.raise_error;
39 end if;
40 ELSE ---Begin Bug#6469079
41 if p_first_noac_lookup_code not in ('001','817') and
42 p_rating_of_record_pattern IN('A','B','C','D','E','F','G','H') and
43 p_rating_of_record_level is null then
44 hr_utility.set_message(8301, 'GHR_38426_ALL_PROCEDURE_FAIL');
45 hr_utility.raise_error;
46 end if; --End Bug#6469079
47 END IF;
48
49
50 --470.03.3
51 -- 10/6 08/13/99 vravikan 01-Oct-99 New Edit
52 -- 18-Sep-00 vravikan 01-Oct-99 Add Z
53 -- 11-Oct-07 Raju 01-Jul-08 delete Z(Bug#6469079)
54 /* If rating of record (pattern) is A, Then rating of record(level) must
55 be 1,3,X or Z. */
56 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/10/01')
57 and p_effective_date < to_date('2008/07/01','RRRR/MM/DD') then --Bug#6469079
58 if p_rating_of_record_pattern = 'A' and
59 p_rating_of_record_level not in ('1','3','X','Z') and
60 p_rating_of_record_level is not null then
61 hr_utility.set_message(8301, 'GHR_37176_ALL_PROCEDURE_FAIL');
62 hr_utility.set_message_token('REC_LEVEL','1, 3, X or Z');
63 hr_utility.raise_error;
64 end if;
65 else --Begin Bug#6469079
66 if p_rating_of_record_pattern = 'A' and
67 p_rating_of_record_level not in ('1','3','X') and
68 p_rating_of_record_level is not null then
69 hr_utility.set_message(8301, 'GHR_37176_ALL_PROCEDURE_FAIL');
70 hr_utility.set_message_token('REC_LEVEL','1, 3 or X');
71 hr_utility.raise_error;
72 end if; --End Bug#6469079
73 end if;
74 --470.04.3
75 -- 10/6 08/13/99 vravikan 01-Oct-99 New Edit
76 -- 18-Sep-00 vravikan 01-Oct-99 Add Z
77 -- 11-Oct-07 Raju 01-Jul-08 delete Z(Bug#6469079)
78 /* If rating of record (pattern) is B, Then rating of record(level) must
79 be 1,3,5,X or Z. */
80 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/10/01')
81 and p_effective_date < to_date('2008/07/01','RRRR/MM/DD') then --Bug#6469079
82 if p_rating_of_record_pattern = 'B' and
83 p_rating_of_record_level not in ('1','3','5','X','Z') and
84 p_rating_of_record_level is not null then
85 hr_utility.set_message(8301, 'GHR_37177_ALL_PROCEDURE_FAIL');
86 hr_utility.set_message_token('REC_LEVEL','1, 3, 5, X or Z');
87 hr_utility.raise_error;
88 end if;
89 else --Begin Bug#6469079
90 if p_rating_of_record_pattern = 'B' and
91 p_rating_of_record_level not in ('1','3','5','X') and
92 p_rating_of_record_level is not null then
93 hr_utility.set_message(8301, 'GHR_37177_ALL_PROCEDURE_FAIL');
94 hr_utility.set_message_token('REC_LEVEL','1, 3, 5 or X ');
95 hr_utility.raise_error;
96 end if; --End Bug#6469079
97 end if;
98 --470.05.3
99 -- 10/6 08/13/99 vravikan 01-Oct-99 New Edit
100 -- 18-Sep-00 vravikan 01-Oct-99 Add Z
101 -- 11-Oct-07 Raju 01-Jul-08 delete Z(Bug#6469079)
102 /* If rating of record (pattern) is C, Then rating of record(level) must
103 be 1,3,4,X or Z. */
104 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/10/01')
105 and p_effective_date < to_date('2008/07/01','RRRR/MM/DD') then --Bug#6469079
106 if p_rating_of_record_pattern = 'C' and
107 p_rating_of_record_level not in ('1','3','4','X','Z') and
108 p_rating_of_record_level is not null then
109 hr_utility.set_message(8301, 'GHR_37178_ALL_PROCEDURE_FAIL');
110 hr_utility.set_message_token('REC_LEVEL','1, 3, 4, X or Z');
111 hr_utility.raise_error;
112 end if;
113 else --Begin Bug#6469079
114 if p_rating_of_record_pattern = 'C' and
115 p_rating_of_record_level not in ('1','3','4','X') and
116 p_rating_of_record_level is not null then
117 hr_utility.set_message(8301, 'GHR_37178_ALL_PROCEDURE_FAIL');
118 hr_utility.set_message_token('REC_LEVEL','1, 3, 4 or X');
119 hr_utility.raise_error;
120 end if; --End Bug#6469079
121 end if;
122 --470.06.3
123 -- 10/6 08/13/99 vravikan 01-Oct-99 New Edit
124 -- 18-Sep-00 vravikan 01-Oct-99 Add Z
125 -- 11-Oct-07 Raju 01-Jul-08 delete Z(Bug#6469079)
126 /* If rating of record (pattern) is D, Then rating of record(level) must
127 be 1,2,3,X or Z. */
128 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/10/01')
129 and p_effective_date < to_date('2008/07/01','RRRR/MM/DD') then --Bug#6469079
130 if p_rating_of_record_pattern = 'D' and
131 p_rating_of_record_level not in ('1','2','3','X','Z') and
132 p_rating_of_record_level is not null then
133 hr_utility.set_message(8301, 'GHR_37179_ALL_PROCEDURE_FAIL');
134 hr_utility.set_message_token('REC_LEVEL','1, 2, 3, X or Z');
135 hr_utility.raise_error;
136 end if;
137 else --Begin Bug#6469079
138 if p_rating_of_record_pattern = 'D' and
139 p_rating_of_record_level not in ('1','2','3','X') and
140 p_rating_of_record_level is not null then
141 hr_utility.set_message(8301, 'GHR_37179_ALL_PROCEDURE_FAIL');
142 hr_utility.set_message_token('REC_LEVEL','1, 2, 3 or X');
143 hr_utility.raise_error;
144 end if; --End Bug#6469079
145 end if;
146
147 --470.07.3
148 -- 10/6 08/13/99 vravikan 01-Oct-99 New Edit
149 -- 18-Sep-00 vravikan 01-Oct-99 Add Z
150 -- 11-Oct-07 Raju 01-Jul-08 delete Z(Bug#6469079)
151 /* If rating of record (pattern) is E, Then rating of record(level) must
152 be 1,3,4,5,X or Z. */
153 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/10/01')
154 and p_effective_date < to_date('2008/07/01','RRRR/MM/DD') then --Bug#6469079
155 if p_rating_of_record_pattern = 'E' and
156 p_rating_of_record_level not in ('1','3','4','5','X','Z') and
157 p_rating_of_record_level is not null then
158 hr_utility.set_message(8301, 'GHR_37180_ALL_PROCEDURE_FAIL');
159 hr_utility.set_message_token('REC_LEVEL','1, 3, 4, 5, X or Z');
160 hr_utility.raise_error;
161 end if;
162 else --Begin Bug#6469079
163 if p_rating_of_record_pattern = 'E' and
164 p_rating_of_record_level not in ('1','3','4','5','X') and
165 p_rating_of_record_level is not null then
166 hr_utility.set_message(8301, 'GHR_37180_ALL_PROCEDURE_FAIL');
167 hr_utility.set_message_token('REC_LEVEL','1, 3, 4, 5 or X');
168 hr_utility.raise_error;
169 end if; --End Bug#6469079
170 end if;
171 --470.08.3
172 -- 10/6 08/13/99 vravikan 01-Oct-99 New Edit
173 -- 18-Sep-00 vravikan 01-Oct-99 Add Z
174 -- 11-Oct-07 Raju 01-Jul-08 delete Z(Bug#6469079)
175 /* If rating of record (pattern) is F, Then rating of record(level) must
176 be 1,2,3,5,X or Z. */
177 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/10/01')
178 and p_effective_date < to_date('2008/07/01','RRRR/MM/DD') then --Bug#6469079
179 if p_rating_of_record_pattern = 'F' and
180 p_rating_of_record_level not in ('1','2','3','5','X','Z') and
181 p_rating_of_record_level is not null then
182 hr_utility.set_message(8301, 'GHR_37181_ALL_PROCEDURE_FAIL');
183 hr_utility.set_message_token('REC_LEVEL','1, 2, 3, 5, X or Z');
184 hr_utility.raise_error;
185 end if;
186 else --Begin Bug#6469079
187 if p_rating_of_record_pattern = 'F' and
188 p_rating_of_record_level not in ('1','2','3','5','X') and
189 p_rating_of_record_level is not null then
190 hr_utility.set_message(8301, 'GHR_37181_ALL_PROCEDURE_FAIL');
191 hr_utility.set_message_token('REC_LEVEL','1, 2, 3, 5 or X');
192 hr_utility.raise_error;
193 end if; --End Bug#6469079
194 end if;
195 --470.09.3
196 -- 10/6 08/13/99 vravikan 01-Oct-99 New Edit
197 -- 18-Sep-00 vravikan 01-Oct-99 Add Z
198 -- 11-Oct-07 Raju 01-Jul-08 delete Z(Bug#6469079)
199 /* If rating of record (pattern) is G, Then rating of record(level) must
200 be 1,2,3,4,X or Z. */
201 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/10/01')
202 and p_effective_date < to_date('2008/07/01','RRRR/MM/DD') then --Bug#6469079
203 if p_rating_of_record_pattern = 'G' and
204 p_rating_of_record_level not in ('1','2','3','4','X','Z') and
205 p_rating_of_record_level is not null then
206 hr_utility.set_message(8301, 'GHR_37182_ALL_PROCEDURE_FAIL');
207 hr_utility.set_message_token('REC_LEVEL','1, 2, 3, 4, X or Z');
208 hr_utility.raise_error;
209 end if;
210 else --Begin Bug#6469079
211 if p_rating_of_record_pattern = 'G' and
212 p_rating_of_record_level not in ('1','2','3','4','X') and
213 p_rating_of_record_level is not null then
214 hr_utility.set_message(8301, 'GHR_37182_ALL_PROCEDURE_FAIL');
215 hr_utility.set_message_token('REC_LEVEL','1, 2, 3, 4 or X');
216 hr_utility.raise_error;
217 end if; --End Bug#6469079
218 end if;
219 --470.10.3
220 -- 10/6 08/13/99 vravikan 01-Oct-99 New Edit
221 -- 02/16/00 vravikan 1196230 Add record_level 'Z'
222 -- 11-Oct-07 Raju 01-Jul-08 delete Z(Bug#6469079)
223 /* If rating of record (pattern) is H, Then rating of record(level) must
224 be 1,2,3,4,5,X or Z. */
225 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/10/01')
226 and p_effective_date < to_date('2008/07/01','RRRR/MM/DD') then --Bug#6469079
227 if p_rating_of_record_pattern = 'H' and
228 p_rating_of_record_level not in ('1','2','3','4','5','X','Z') and
229 p_rating_of_record_level is not null then
230 hr_utility.set_message(8301, 'GHR_37183_ALL_PROCEDURE_FAIL');
231 hr_utility.set_message_token('REC_LEVEL','1, 2, 3, 4, 5, X or Z');
232 hr_utility.raise_error;
233 end if;
234 else --Begin Bug#6469079
235 if p_rating_of_record_pattern = 'H' and
236 p_rating_of_record_level not in ('1','2','3','4','5','X') and
237 p_rating_of_record_level is not null then
238 hr_utility.set_message(8301, 'GHR_37183_ALL_PROCEDURE_FAIL');
239 hr_utility.set_message_token('REC_LEVEL','1, 2, 3, 4, 5 or X');
240 hr_utility.raise_error;
241 end if; --End Bug#6469079
242 end if;
243
244 -- 471.02.3
245 if p_rating_of_record_level in ('1','2','3','4','5') and
246 (p_rating_of_record_pattern is null or
247 p_rating_of_record_period is null or
248 ----Bug# 4753117 28-Feb-07 Veeramani adding Appraisal start date
249 p_rating_of_record_per_starts is null) then
250 hr_utility.set_message(8301, 'GHR_37502_ALL_PROCEDURE_FAIL');
251 hr_utility.raise_error;
252 end if;
253
254 --471.03.3
255 -- 10/6 08/16/99 vravikan 01-Oct-99 New Edit
256 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/10/01') then
257 if p_to_pay_plan in ('ES') and
258 (p_rating_of_record_pattern is not null and
259 p_rating_of_record_pattern in ('A','B','C','E') ) then
260 hr_utility.set_message(8301, 'GHR_37184_ALL_PROCEDURE_FAIL');
261 hr_utility.raise_error;
262 end if;
263 end if;
264
265 --471.06.1
266 -- upd55 11-Oct-2007 Raju 01-May-2007 Bug# 6469079 New Edit
267
268 if p_effective_date >= to_date('2007/05/01','RRRR/MM/DD') then
269 if p_rating_of_record_level is null and
270 p_rating_of_record_pattern not in ('Z') then
271 hr_utility.set_message(8301, 'GHR_38428_ALL_PROCEDURE_FAIL');
272 hr_utility.raise_error;
273 end if;
274 end if;
275
276
277 /* void due to lack of submission date
278 --472.02.1
279 if p_rating_of_record_period is not null and
280 fnd_date.canonical_to_date(p_rating_of_record_period) > p_submission_date then
281 hr_utility.set_message(8301, 'GHR_37503_ALL_PROCEDURE_FAIL');
282 hr_utility.raise_error;
283 end if;
284 */
285 --Bug# 4753117 28-Feb-07 Veeramani commented as this edit is not required as per the
286 --bug description
287
288 /*--472.02.2
289 if p_rating_of_record_period is not null and
290 fnd_date.canonical_to_date(p_rating_of_record_period) > p_effective_date then
291 hr_utility.set_message(8301, 'GHR_37504_ALL_PROCEDURE_FAIL');
292 hr_utility.raise_error;
293 end if;
294 */
295
296 --472.03.3
297 if p_rating_of_record_level = 'X' and
298 p_rating_of_record_period is not null then
299 hr_utility.set_message(8301, 'GHR_37505_ALL_PROCEDURE_FAIL');
300 hr_utility.raise_error;
301 end if;
302 --472.04.2
303 -- 12/08/00 vravikan 01-Oct-00 New Edit
304 --Bug# 4753117 28-Feb-07 Veeramani adding edit 472.04.2 with respective to the
305 --Appraisal start date
306 /* If rating of record (period) is not spaces,
307 Then it must not be more than 5 years earlier than the effective date of
308 personnel action.
309 */
310 l_end_date := fnd_date.canonical_to_date(p_rating_of_record_period);
311 l_start_date := fnd_date.canonical_to_date(p_rating_of_record_per_starts);
312 if p_effective_date >= to_date('2000/10/01','yyyy/mm/dd') and
313 ((p_rating_of_record_period is not null and
314 months_between(p_effective_date,l_end_date ) > 60) or
315 (p_rating_of_record_per_starts is not null and
316 months_between(p_effective_date,l_start_date ) > 60)) then
317 hr_utility.set_message(8301, 'GHR_37857_ALL_PROCEDURE_FAIL');
318 hr_utility.raise_error;
319 end if;
320
321
322 end chk_rating_of_rec;
323
324
325
326 /* Name:
327 -- Position Occupied
328 */
329
330
331 procedure chk_position_occupied
332 (p_position_occupied_code in varchar2
333 ,p_to_pay_plan in varchar2
334 ,p_first_noac_lookup_code in varchar2
335 ,p_effective_date in date
336 ) is
337 begin
338
339
340 -- 500.02.3
341 -- 17-Aug-00 vravikan From the Start Add VE
342 -- Dec 2001 Patch 01-Jul-01 Delete CZ, SZ, and WZ
343 if p_effective_date >= to_date('2001/07/01','yyyy/mm/dd') THEN
344 if (
345 p_to_pay_plan in ('ED','EE','EF','EG','EH','EI',
346 'MA','SV','SW','VE','VM','VN','VP')
347 or
348 (substr(p_to_pay_plan,1,1)= 'F' and
349 p_to_pay_plan <> 'FC'
350 )
351 )
352 and
353 p_position_occupied_code <> '2'
354 and
355 p_position_occupied_code is not null
356 then
357 hr_utility.set_message(8301, 'GHR_37908_ALL_PROCEDURE_FAIL');
358 hr_utility.raise_error;
359 end if;
360 else
361 if (
362 p_to_pay_plan in ('CZ','ED','EE','EF','EG','EH','EI',
363 'MA','SV','SW','SZ','VE','VM','VN','VP','WZ')
364 or
365 (substr(p_to_pay_plan,1,1)= 'F' and
366 p_to_pay_plan <> 'FC'
367 )
368 )
369 and
370 p_position_occupied_code <> '2'
371 and
372 p_position_occupied_code is not null
373 then
374 hr_utility.set_message(8301, 'GHR_37506_ALL_PROCEDURE_FAIL');
375 hr_utility.raise_error;
376 end if;
377 end if;
378
379 -- 500.04.3
380 if p_to_pay_plan = 'ES' and
381 p_position_occupied_code not in ('3','4')
382 and
383 p_position_occupied_code is not null
384 then
385 hr_utility.set_message(8301, 'GHR_37507_ALL_PROCEDURE_FAIL');
386 hr_utility.raise_error;
387 end if;
388
389 -- 500.07.2
390 if (
391 substr(p_first_noac_lookup_code,1,1) <> '3'
392 and
393 substr(p_first_noac_lookup_code,1,1) <> '4'
394 ) and
395 (p_position_occupied_code = '3'
396 or
397 p_position_occupied_code = '4')
398 and
399 p_to_pay_plan <>'ES'
400 and
401 p_position_occupied_code is not null
402 then
403 hr_utility.set_message(8301, 'GHR_37508_ALL_PROCEDURE_FAIL');
404 hr_utility.raise_error;
405 end if;
406
407 --UPDATED_BY DATE COMMENTS
408 ------------------------------------------------------------
409 -- Madhuri 14-SEP-2004 Removed the NOACS- 112, 512
410 -------------------------------------------------------------
411 -- 500.13.2
412 if p_first_noac_lookup_code in ('100','101','107','108','115',
413 '120','122','124','140','141','500',
414 '501','507','508','515','520',
415 '522','524','540','541') and
416 p_position_occupied_code <> '1' then
417 hr_utility.set_message(8301, 'GHR_37509_ALL_PROCEDURE_FAIL');
418 hr_utility.raise_error;
419 end if;
420
421 --UPDATED_BY DATE COMMENTS
422 ------------------------------------------------------------
423 -- amrchakr 26-SEP-2006 Removed the NOACS- '150','151','153',
424 -- '154','155', '157' effective from 2003/07/01
425 -------------------------------------------------------------
426 -- 500.16.2
427 if p_effective_date < fnd_date.canonical_to_date('2003/07/01') then
428 if p_first_noac_lookup_code in ('150','151','153','154','155',
429 '157','170','171','550','551',
430 '553','554','555','570','571')
431 and
432 p_position_occupied_code <>'2'
433 and
434 p_position_occupied_code is not null
435 then
436 hr_utility.set_message(8301, 'GHR_37510_ALL_PROCEDURE_FAIL');
437 hr_utility.raise_error;
438 end if;
439 else
440 if p_first_noac_lookup_code in ('170','171','550','551',
441 '553','554','555','570','571')
442 and
443 p_position_occupied_code <>'2'
444 and
445 p_position_occupied_code is not null
446 then
447 hr_utility.set_message(8301, 'GHR_37693_ALL_PROCEDURE_FAIL');
448 hr_utility.raise_error;
449 end if;
450 end if;
451
452 end chk_position_occupied;
453
454 /* Name:
455 -- Prior Occupation
456 */
457
458 procedure chk_prior_occupation
459 (p_prior_occupation_code in varchar2 --non SF52
460 ,p_occupation_code in varchar2 --non SF52
461 ,p_first_noac_lookup_code in varchar2
462 ,p_prior_pay_plan in varchar2 --non SF52
463 ,p_agency_subelement in varchar2 --non SF52
464 ,p_effective_date in date
465 ) is
466 begin
467
468 -- 520.02.2
469 --26-Jun-06 Raju From 01-Apr-2006 Added 611,613,894
470 --22-Jan-07 upd 49 Raju From 01-Apr-2006 delete 894
471 --05-MAR-07 AVR Modify the CPDF edit 520.02.2 by adding 890 NOA.
472 ------------- do not change the message until OPM Guidelines.
473 if p_effective_date < to_date('2006/04/01','yyyy/mm/dd') then
474 if p_prior_occupation_code <> p_occupation_code and
475 p_prior_occupation_code is not null and
476 p_occupation_code is not null and
477 p_first_noac_lookup_code not in ('702','703','713','721','740','741','800','850','855') and
478 substr(p_first_noac_lookup_code,1,1) <> 5
479 then
480 hr_utility.set_message(8301, 'GHR_37511_ALL_PROCEDURE_FAIL');
481 hr_utility.raise_error;
482 end if;
483 else
484 if p_prior_occupation_code <> p_occupation_code and
485 p_prior_occupation_code is not null and
486 p_occupation_code is not null and
487 p_first_noac_lookup_code not in
488 ('611','613','702','703','713','721','740','741','800','850','855','890') and
489 substr(p_first_noac_lookup_code,1,1) <> 5
490 then
491 hr_utility.set_message(8301, 'GHR_37163_ALL_PROCEDURE_FAIL');
492 hr_utility.raise_error;
493 end if;
494 end if;
495
496 -- 520.04.2
497 -- 12/12/01 Change 2200 to 2500
498 if (
499 (p_prior_pay_plan ='LG' or p_prior_pay_plan ='ST')
500 or
501 substr(p_prior_pay_plan,1,1)='G'
502 ) and
503 to_number(p_prior_occupation_code) >= 2500
504 and
505 p_prior_occupation_code is not null
506 then
507 hr_utility.set_message(8301, 'GHR_37512_ALL_PROCEDURE_FAIL');
508 hr_utility.raise_error;
509 end if;
510
511 -- 520.07.2
512 if substr(p_prior_pay_plan,1,1) in ('K','W','X')
513 and
514 to_number(p_prior_occupation_code) <= 2499
515 and
516 p_prior_occupation_code is not null
517 then
518 hr_utility.set_message(8301, 'GHR_37513_ALL_PROCEDURE_FAIL');
519 hr_utility.raise_error;
520 end if;
521
522 -- 520.13.2
523 if p_prior_occupation_code = '0605' and
524 substr(p_agency_subelement, 1, 2) <> 'VA' then
525 hr_utility.set_message(8301, 'GHR_37514_ALL_PROCEDURE_FAIL');
526 hr_utility.raise_error;
527 end if;
528
529 -- 520.16.2
530 -- 28-Nov-2002 Madhuri Commented the edit.
531 /* if p_prior_occupation_code = '0805' and
532 substr(p_agency_subelement, 1, 2) not in ('AF','AR','DD','NV') then
533 hr_utility.set_message(8301, 'GHR_37515_ALL_PROCEDURE_FAIL');
534 hr_utility.raise_error;
535 end if;*/
536
537 -- 520.19.2
538 -- removed 2806, 2808 as per update 7 on 16-jul-98
539 -- End Dated as on 31st July 2001 for CPDF EOY 11i - Madhuri
540
541 if p_effective_date <= to_date('2001/07/31','yyyy/mm/dd') then
542 if p_prior_occupation_code in ('2619','2843') and
543 substr(p_agency_subelement, 1, 2) <> 'DN' then
544 hr_utility.set_message(8301, 'GHR_37516_ALL_PROCEDURE_FAIL');
545 hr_utility.raise_error;
546 end if;
547 end if;
548
549 --520.20.2
550 -- added on 16 jul 1998 as per update 7
551 -- If prior occupation is 2806 or 2808, then the first two positions of
552 -- agency/subelement must be DN or IN
553 -- End Dated as on 31st July 2001 for CPDF EOY 11i - Madhuri
554
555 if p_effective_date <= to_date('2001/07/31','yyyy/mm/dd') and
556 p_effective_date >= to_date('1998/03/01','yyyy/mm/dd') then
557 if p_prior_occupation_code in ('2806','2808') and
558 substr(p_agency_subelement, 1, 2) NOT IN ('DN', 'IN') then
559 hr_utility.set_message(8301, 'GHR_37873_ALL_PROCEDURE_FAIL');
560 hr_utility.raise_error;
561 end if;
562 end if;
563
564 end chk_prior_occupation;
565
566
567 /* Name:
568 -- Prior Pay Basis
569 */
570
571 procedure chk_prior_pay_basis
572 (p_prior_pay_basis in varchar2 --non SF52
573 ,p_prior_pay_plan in varchar2 --non SF52
574 ,p_agency_subelement in varchar2 --non SF52
575 ,p_prior_basic_pay in varchar2 --non SF52
576 ,p_effective_date in date
577 ,p_prior_effective_date in date
578 ,p_prior_pay_rate_det_code in varchar2
579 ) is
580 cursor c_fw_pay_plans( p_pay_plan varchar2) is
581 SELECT 'X'
582 FROM ghr_pay_plans
583 WHERE equivalent_pay_plan = 'FW'
584 AND pay_plan = p_pay_plan;
585 l_prior_basic_pay VARCHAR2(50);
586 begin
587
588 -- 530.02.2
589 -- 17-Aug-00 vravikan From the Start Add one more condition
590 -- PRD is other than A,B,E,F,M,U or V
591 -- UPD 43(Bug 4567571) Raju 09-Nov-2005 Delete Prior PRD M effective date from 01-May-2005
592 if p_effective_date < to_date('2005/05/01','yyyy/mm/dd') THEN
593 if p_prior_pay_rate_det_code not in ('A','B','E','F','M','U','V') then
594 if p_prior_pay_plan in ('AL','CA','ES','EX','GG','GH','GM','GS','SL')
595 and
596 p_prior_pay_basis <> 'PA'
597 and
598 p_prior_pay_basis is not null
599 then
600 hr_utility.set_message(8301, 'GHR_37517_ALL_PROCEDURE_FAIL');
601 hr_utility.set_message_token('PRD_LIST','A, B, E, F, M, U, or V');
602 hr_utility.raise_error;
603 end if;
604
605 if p_prior_pay_plan = 'KA'
606 and
607 (p_prior_pay_basis <>'PA' and p_prior_pay_basis <>'PH')
608 then
609 hr_utility.set_message(8301, 'GHR_37580_ALL_PROCEDURE_FAIL');
610 hr_utility.set_message_token('PRD_LIST','A, B, E, F, M, U, or V');
611 hr_utility.raise_error;
612 end if;
613
614 if substr(p_prior_pay_plan,1,1) = 'X' and
615 p_prior_pay_basis<>'PH'then
616 hr_utility.set_message(8301, 'GHR_37582_ALL_PROCEDURE_FAIL');
617 hr_utility.set_message_token('PRD_LIST','A, B, E, F, M, U, or V');
618 hr_utility.raise_error;
619 end if;
620
621 if p_prior_pay_plan = 'ZZ' and p_prior_pay_basis <>'WC' then
622 hr_utility.set_message(8301, 'GHR_37583_ALL_PROCEDURE_FAIL');
623 hr_utility.set_message_token('PRD_LIST','A, B, E, F, M, U, or V');
624 hr_utility.raise_error;
625 end if;
626 end if;
627
628 elsif p_effective_date >= to_date('2005/05/01','yyyy/mm/dd') THEN
629 if p_prior_pay_rate_det_code not in ('A','B','E','F','U','V') then
630 if p_prior_pay_plan in ('AL','CA','ES','EX','GG','GH','GM','GS','SL') and
631 p_prior_pay_basis <> 'PA' and
632 p_prior_pay_basis is not null
633 then
634 hr_utility.set_message(8301, 'GHR_37517_ALL_PROCEDURE_FAIL');
635 hr_utility.set_message_token('PRD_LIST','A, B, E, F, U, or V');
636 hr_utility.raise_error;
637 end if;
638
639 if p_prior_pay_plan = 'KA' and
640 (p_prior_pay_basis <>'PA' and p_prior_pay_basis <>'PH')
641 then
642 hr_utility.set_message(8301, 'GHR_37580_ALL_PROCEDURE_FAIL');
643 hr_utility.set_message_token('PRD_LIST','A, B, E, F, U, or V');
644 hr_utility.raise_error;
645 end if;
646
647 if substr(p_prior_pay_plan,1,1) = 'X' and
648 p_prior_pay_basis<>'PH'then
649 hr_utility.set_message(8301, 'GHR_37582_ALL_PROCEDURE_FAIL');
650 hr_utility.set_message_token('PRD_LIST','A, B, E, F, U, or V');
651 hr_utility.raise_error;
652 end if;
653
654 if p_prior_pay_plan = 'ZZ' and p_prior_pay_basis <>'WC' then
655 hr_utility.set_message(8301, 'GHR_37583_ALL_PROCEDURE_FAIL');
656 hr_utility.set_message_token('PRD_LIST','A, B, E, F, U, or V');
657 hr_utility.raise_error;
658 end if;
659 end if;
660 end if;
661 /*530.10.2 If prior pay basis is PD,
662 And agency/subelement is CU, FD, FL, FY, TRAJ, or TR35,
663 Then prior basic pay may not exceed 1000.
664
665 Default: Insert asterisks in prior pay basis and prior
666 basic pay.*/
667 -- UPDATE_DATE UPDATED_BY EFFECTIVE_DATE COMMENTS
668 -----------------------------------------------------------------------------
669 -- 18-oct-04 Madhuri from start of edit Terminating the edit.
670 --
671
672 /*
673 if p_prior_pay_basis = 'PD' and
674 (substr(p_agency_subelement,1,2) in ('CU','FD','FL','FY') or
675 p_agency_subelement in ('TRAJ','TR35')) and
676 to_number(p_prior_basic_pay) > max_per_diem then
677 hr_utility.set_message(8301, 'GHR_37519_ALL_PROCEDURE_FAIL');
678 hr_utility.raise_error;
679 end if; */
680 --
681 --
682 /*530.07.2 If prior pay basis is PA, PH, PM, BW, or WC,
683 Then prior basic pay must not be greater than the
684 maximum shown in table 18.
685
686 Default: Insert asterisks in prior pay basis and prior
687 basic pay.*/
688
689 -- UPDATE DATE UPDATE BY BUG NO COMMENTS
690 -----------------------------------------------------------------------------------------------------------------
691 -- 18-Oct-04 Madhuri Modifying the existing edit as under from 11-JAN-2004.
692 -- If pay basis is BW, PA, PD, PH, or WC,
693 -- Then basic pay must be within the range for the pay basis shown in Table 56.
694 -- splitting the error message for if and else part also.
695 -- 19-NOV-04 Madhuri Not splitting message. new message 38918 is being used now.
696 -- 29-DEC-04 Madhuri Modified rule of checking basic pay against range. No need to divide by 2087.
697 --- Modified for bug 4089960
698 -----------------------------------------------------------------------------------------------------------------
699 IF ( p_prior_pay_plan <> 'AD' ) THEN -- Sundar Bug 4307246
700 IF ( p_effective_date < to_date('2004/01/11','yyyy/mm/dd') ) then
701
702 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 18',
703 p_prior_pay_basis,
704 'Maximum Basic Pay',
705 p_prior_effective_date);
706
707 IF max_basic_pay IS NOT NULL then
708 IF (p_prior_pay_basis ='PA' and to_number(p_prior_basic_pay)> max_basic_pay ) or
709 (p_prior_pay_basis ='PH' and (to_number(p_prior_basic_pay)/2087)> max_basic_pay ) or
710 (p_prior_pay_basis ='PM' and to_number(p_prior_basic_pay)> max_basic_pay ) or
711 (p_prior_pay_basis ='BW' and to_number(p_prior_basic_pay)> max_basic_pay ) or
712 (p_prior_pay_basis ='WC' and to_number(p_prior_basic_pay)> max_basic_pay )
713 then
714 hr_utility.set_message(8301, 'GHR_37518_ALL_PROCEDURE_FAIL');
715 hr_utility.raise_error;
716 END IF;
717 END IF;
718 ELSE -- if after 11th Jan 2004
719 -- From: If prior pay basis is PA, PH, PM, BW, or WC, Then prior basic pay must not be greater than the maximum shown in table 18.
720 -- To: If prior pay basis is BW, PA, PD, PH, or WC, Then prior basic pay must be within the range for the pay basis shown in Table 56.
721
722 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 56',
723 p_prior_pay_basis,
724 'Maximum Basic Pay',
725 p_prior_effective_date);
726
727 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 56',
728 p_prior_pay_basis,
729 'Minimum Basic Pay',
730 p_prior_effective_date);
731
732 l_prior_basic_pay := p_prior_basic_pay;
733 for pay_plan_rec in c_fw_pay_plans(p_prior_pay_plan) loop
734 hr_utility.set_location('Inside FW pay plan ',45);
735 IF p_prior_pay_basis = 'PH' THEN
736 hr_utility.set_location('Inside PH',50);
737 l_prior_basic_pay := to_char(to_number(p_prior_basic_pay)/2087);
738 END IF;
739 end loop;
740
741 IF ( max_basic_pay IS NOT NULL and min_basic_pay IS NOT NULL ) then
742 IF (p_prior_pay_basis ='PA' and NOT (to_number(l_prior_basic_pay) BETWEEN min_basic_pay AND max_basic_pay )) or
743 (p_prior_pay_basis ='PH' and NOT (to_number(l_prior_basic_pay) BETWEEN min_basic_pay AND max_basic_pay )) or
744 (p_prior_pay_basis ='PD' and NOT (to_number(l_prior_basic_pay) BETWEEN min_basic_pay AND max_basic_pay )) or
745 (p_prior_pay_basis ='BW' and NOT (to_number(l_prior_basic_pay) BETWEEN min_basic_pay AND max_basic_pay)) or
746 (p_prior_pay_basis ='WC' and NOT (to_number(l_prior_basic_pay) BETWEEN min_basic_pay AND max_basic_pay ))
747 THEN
748 hr_utility.set_message(8301, 'GHR_38918_ALL_PROCEDURE_FAIL');
749 hr_utility.raise_error;
750 END IF;
751 END IF;
752
753 END IF;
754 END IF; -- IF ( p_prior_pay_plan <> 'AD' ) THEN
755
756 /*530.12.2 If prior pay basis is PD,
757 And agency/subelement is other than CU, FD, FL, FY,
758 TRAJ, or TR35,
759 Then prior basic pay may not exceed the maximum on
760 Table 18.
761
762 Default: Insert asterisks in prior pay basis and prior
763 basic pay.*/
764
765 -- UPDATE_DATE UPDATED_BY EFFECTIVE_DATE COMMENTS
766 -----------------------------------------------------------------------------
767 -- 18-oct-04 Madhuri from start of edit Terminating the edit.
768 --
769 /* if p_prior_pay_basis = 'PD' and
770 (substr(p_agency_subelement,1,2) in ('CU','FD','FL','FY') or
771 p_agency_subelement in ('TRAJ','TR35')) and
772 to_number(p_prior_basic_pay) > max_basic_pay then
773 hr_utility.set_message(8301, 'GHR_37520_ALL_PROCEDURE_FAIL');
774 hr_utility.raise_error;
775 end if;
776 end if;*/
777
778
779 end chk_prior_pay_basis;
780
781 /* Name:
782 -- Prior Grade
783 */
784
785 procedure chk_prior_grade
786 (p_prior_pay_plan in varchar2 --non SF52
787 ,p_grade_or_level in varchar2
788 ,p_prior_grade in varchar2 --non SF52
789 ,p_to_pay_plan in varchar2
790 ,p_first_noac_lookup_code in varchar2
791 ,p_prior_pay_rate_det_code in varchar2 --non SF52
792 ,p_effective_date in date
793 ) is
794 begin
795
796 -- 540.02.2
797 if (p_to_pay_plan = 'GM' or p_to_pay_plan = 'GS') and
798 (p_prior_pay_plan = 'GM' or p_prior_pay_plan = 'GS') and
799 p_grade_or_level <> p_prior_grade and
800 p_grade_or_level is not null and
801 p_prior_grade is not null and
802 (p_first_noac_lookup_code not in ( '702','703','713','740','741') and
803 substr(p_first_noac_lookup_code,1,1) <> '5') then
804 hr_utility.set_message(8301, 'GHR_37521_ALL_PROCEDURE_FAIL');
805 hr_utility.raise_error;
806 end if;
807
808 -- 540.03.2
809 if p_prior_pay_plan = 'FG' and
810 p_prior_grade not between '01' and '15'
811 and
812 p_prior_grade is not null
813 then
814 hr_utility.set_message(8301, 'GHR_37522_ALL_PROCEDURE_FAIL');
815 hr_utility.raise_error;
816 end if;
817
818 -- 540.04.2
819 if p_first_noac_lookup_code = '769' and
820 p_prior_grade <> p_grade_or_level then
821 hr_utility.set_message(8301, 'GHR_37523_ALL_PROCEDURE_FAIL');
822 hr_utility.raise_error;
823 end if;
824
825 -- 540.05.2
826 if p_prior_pay_plan = 'FM' and
827 p_prior_grade not between '13' and '15'
828 and
829 p_prior_grade is not null
830 then
831 hr_utility.set_message(8301, 'GHR_37524_ALL_PROCEDURE_FAIL');
832 hr_utility.raise_error;
833 end if;
834 -- 540.06.2
835 -- Update/Change Date By Effective Date Comment
836 -- 9/2 08/16/99 vravikan 01-Mar-1999 New Edit
837 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/03/01') then
838 if p_prior_pay_plan = 'EZ' and
839 p_prior_grade not between '01' and '08'
840 and
841 p_prior_grade is not null
842 then
843 hr_utility.set_message(8301, 'GHR_37185_ALL_PROCEDURE_FAIL');
844 hr_utility.raise_error;
845 end if;
846 end if;
847
848 -- 540.07.2
849 -- Update Date By Effective Date Comment
850 -- 9 14/05/99 vravikan Add prior pay plans CG and MG
851 if p_prior_pay_plan in ( 'CG','MG','WL','XG' ) and
852 p_prior_grade not between '01' and '15'
853 and
854 p_prior_grade is not null
855 then
856 hr_utility.set_message(8301, 'GHR_37525_ALL_PROCEDURE_FAIL');
857 hr_utility.raise_error;
858 end if;
859
860 -- 540.10.2
861 if (p_prior_pay_plan = 'WS' or p_prior_pay_plan = 'XH') and
862 p_prior_grade not between '01' and '19'
863
864 and
865 p_prior_grade is not null
866 then
867 hr_utility.set_message(8301, 'GHR_37526_ALL_PROCEDURE_FAIL');
868 hr_utility.raise_error;
869 end if;
870
871 -- 540.13.2
872 if p_prior_pay_plan = 'FA' and
873 p_prior_pay_rate_det_code <>'S' and
874 p_prior_grade not in ( 'CA','CM','MC','NC','OC','01','02','03','04','13','14')
875 and
876 p_prior_grade is not null
877 then
878 hr_utility.set_message(8301, 'GHR_37527_ALL_PROCEDURE_FAIL');
879 hr_utility.raise_error;
880 end if;
881
882 -- 540.16.2
883 if p_prior_pay_plan = 'CE' and
884 p_prior_grade not between '01' and '17' and
885 p_prior_grade is not null
886 then
887 hr_utility.set_message(8301, 'GHR_37528_ALL_PROCEDURE_FAIL');
888 hr_utility.raise_error;
889 end if;
890
891 -- 540.17.2
892 -- If prior pay plan is CY,
893 -- Then prior grade must be 01 through 24 or asterisks.
894 --
895 -- Included effective date on 16-jul-1998
896 if p_effective_date >= fnd_date.canonical_to_date('1998/03/01') then
897 if p_prior_pay_plan = 'CY' and
898 p_prior_grade not between '01' and '24' and
899 p_prior_grade is not null then
900 hr_utility.set_message(8301, 'GHR_37874_ALL_PROCEDURE_FAIL');
901 hr_utility.raise_error;
902 end if;
903 end if;
904
905 -- 540.18.2
906 if (p_prior_pay_plan = 'AL' or p_prior_pay_plan = 'CA') and
907 p_prior_grade not between '01' and '03'
908 and
909 p_prior_grade is not null
910 then
911 hr_utility.set_message(8301, 'GHR_37529_ALL_PROCEDURE_FAIL');
912 hr_utility.raise_error;
913 end if;
914
915 -- 540.19.2
916 if p_prior_pay_plan = 'GM' and
917 p_prior_grade not between '13' and '15'
918 and
919 p_prior_grade is not null
920 then
921 hr_utility.set_message(8301, 'GHR_37530_ALL_PROCEDURE_FAIL');
922 hr_utility.raise_error;
923 end if;
924
925 -- 540.20.2
926 if p_prior_pay_plan = 'GS' and
927 p_prior_grade not between '01' and '15'
928 and
929 p_prior_grade is not null
930 then
931 hr_utility.set_message(8301, 'GHR_37531_ALL_PROCEDURE_FAIL');
932 hr_utility.raise_error;
933 end if;
934
935 -- 540.22.2
936 -- Update/Change Date By Effective Date Comment
937 -- 13-Jun-06 Raju 01-Jan-03 Terminate the edit
938 IF p_effective_date < fnd_date.canonical_to_date('2003/01/01') then --Bug# 5073313
939 if p_prior_pay_plan = 'VM' and
940 p_prior_grade not in ('11','12','13','14','15','96','97') and
941 p_prior_grade is not null
942 then
943 hr_utility.set_message(8301, 'GHR_37532_ALL_PROCEDURE_FAIL');
944 hr_utility.raise_error;
945 end if;
946 END IF;
947
948 -- 540.25.2
949 if p_prior_pay_plan = 'VN' and
950 p_prior_grade not in ('01','02','03','04','05','06','08','09',
951 '11','12','13','14','15')
952
953 and
954 p_prior_grade is not null
955 then
956 hr_utility.set_message(8301, 'GHR_37533_ALL_PROCEDURE_FAIL');
957 hr_utility.raise_error;
958 end if;
959
960 -- 540.28.2
961 if p_prior_pay_plan = 'VP' and
962 p_prior_grade not between '11' and '15'
963 and
964 p_prior_grade is not null
965 then
966 hr_utility.set_message(8301, 'GHR_37534_ALL_PROCEDURE_FAIL');
967 hr_utility.raise_error;
968 end if;
969
970 -- 540.29.2
971 if p_prior_pay_plan = 'DR' and
972 p_prior_grade not between '01' and '04'
973 and
974 p_prior_grade is not null
975 then
976 hr_utility.set_message(8301, 'GHR_37538_ALL_PROCEDURE_FAIL');
977 hr_utility.raise_error;
978 end if;
979
980 -- 540.31.2
981 if (p_prior_pay_plan = 'WG' or p_prior_pay_plan = 'XF') and
982 p_prior_grade not between '01' and '15'
983 and
984 p_prior_grade is not null
985 then
986 hr_utility.set_message(8301, 'GHR_37535_ALL_PROCEDURE_FAIL');
987 hr_utility.raise_error;
988 end if;
989
990 -- 540.32.2
991 if (p_prior_pay_plan = 'ND' or p_prior_pay_plan = 'NT') and
992 p_prior_grade not between '01' and '06'
993 and
994 p_prior_grade is not null
995 then
996 hr_utility.set_message(8301, 'GHR_37585_ALL_PROCEDURE_FAIL');
997 hr_utility.raise_error;
998 end if;
999
1000 -- 540.33.2
1001 if p_prior_pay_plan = 'NG' and
1002 p_prior_grade not between '01' and '05'
1003 and
1004 p_prior_grade is not null
1005 then
1006 hr_utility.set_message(8301, 'GHR_37536_ALL_PROCEDURE_FAIL');
1007 hr_utility.raise_error;
1008 end if;
1009
1010 -- 540.34.2
1011 if p_prior_pay_plan = 'EX' and
1012 p_prior_grade not between '01' and '05'
1013 and
1014 p_prior_grade is not null
1015 then
1016 hr_utility.set_message(8301, 'GHR_37536_ALL_PROCEDURE_FAIL');
1017 hr_utility.raise_error;
1018 end if;
1019
1020 -- 540.35.2
1021 --
1022 -- added on 16 jul 98 as per update 7
1023 --
1024 if p_effective_date >= fnd_date.canonical_to_date('1998/03/01') then
1025 if p_prior_pay_plan in ('NH', 'NJ') and
1026 p_prior_grade not between '01' and '04' and
1027 p_prior_grade is not null then
1028 hr_utility.set_message(8301, 'GHR_37871_ALL_PROCEDURE_FAIL');
1029 hr_utility.raise_error;
1030 end if;
1031 end if;
1032
1033 -- 540.36.2
1034 --
1035 -- added on 16 jul 98 as per update 7
1036 --
1037 if p_effective_date >= fnd_date.canonical_to_date('1998/03/01') then
1038 if p_prior_pay_plan = 'NK' and
1039 p_prior_grade not between '01' and '03' and
1040 p_prior_grade is not null then
1041 hr_utility.set_message(8301, 'GHR_37872_ALL_PROCEDURE_FAIL');
1042 hr_utility.raise_error;
1043 end if;
1044 end if;
1045
1046 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1047 -- 540.37.2
1048 if p_prior_pay_plan = 'OC' and
1049 p_prior_grade not between '01' and '25'
1050 and
1051 p_prior_grade is not null
1052 then
1053 hr_utility.set_message(8301, 'GHR_37537_ALL_PROCEDURE_FAIL');
1054 hr_utility.raise_error;
1055 end if;
1056 */
1057 -- 540.38.2
1058 -- Update Date By Effective Date Comment
1059 -- 8 01/28/99 vravikan 10/01/1998 New Edit
1060 -- 13-Jun-06 Raju 01-Jan-03 Terminate the edit
1061 -- If prior pay plan is FV, then prior grade must be 'AA' through 'MM' or asterisks
1062 --
1063 if p_effective_date >= fnd_date.canonical_to_date('1998/10/01') and
1064 p_effective_date < fnd_date.canonical_to_date('2003/01/01') then --Bug# 5073313
1065 if p_prior_pay_plan = 'FV' and
1066 p_prior_grade not in ('AA','BB','CC','DD','EE','FF','GG','HH','II',
1067 'JJ','KK','LL','MM') and
1068 p_prior_grade is not null then
1069 hr_utility.set_message(8301, 'GHR_37033_ALL_PROCEDURE_FAIL');
1070 hr_utility.raise_error;
1071 end if;
1072 end if;
1073
1074 -- 540.39.2
1075 --
1076 -- Update Date By Effective Date Comment
1077 -- 8 01/28/99 vravikan 10/01/1998 New Edit
1078 -- If prior pay plan is EV, then prior grade must be 01 through 03 or asterisks
1079 --
1080 if p_effective_date >= fnd_date.canonical_to_date('1998/10/01') then
1081 if p_prior_pay_plan = 'EV' and
1082 p_prior_grade not in ('01', '02', '03') and
1083 p_prior_grade is not null then
1084 hr_utility.set_message(8301, 'GHR_37034_ALL_PROCEDURE_FAIL');
1085 hr_utility.raise_error;
1086 end if;
1087 end if;
1088
1089 -- 540.40.2
1090 if p_prior_pay_plan = 'NY' and
1091 p_prior_grade not between '01' and '04'
1092 and
1093 p_prior_grade is not null
1094 then
1095 hr_utility.set_message(8301, 'GHR_37538_ALL_PROCEDURE_FAIL');
1096 hr_utility.raise_error;
1097 end if;
1098
1099 -- 540.43.2
1100 if p_prior_pay_plan = 'FO' and
1101 p_prior_grade not between '01' and '08'
1102 and
1103 p_prior_grade is not null
1104 then
1105 hr_utility.set_message(8301, 'GHR_37539_ALL_PROCEDURE_FAIL');
1106 hr_utility.raise_error;
1107 end if;
1108
1109 -- 540.45.2
1110 if p_prior_pay_plan = 'FP' and
1111 (p_prior_grade not between '01' and '09' or
1112 p_prior_grade in ('AA','BB','CC','DD','EE')
1113 )
1114 and
1115 p_prior_grade is not null
1116 then
1117 hr_utility.set_message(8301, 'GHR_37540_ALL_PROCEDURE_FAIL');
1118 hr_utility.raise_error;
1119 end if;
1120
1121 -- 540.46.2
1122 if p_prior_pay_plan = 'FE' and
1123 p_prior_grade not in ('CA','CM','MC','OC','01','02','03')
1124 and
1125 p_prior_grade is not null
1126 then
1127 hr_utility.set_message(8301, 'GHR_37541_ALL_PROCEDURE_FAIL');
1128 hr_utility.raise_error;
1129 end if;
1130
1131 -- 540.49.2
1132 if p_prior_pay_plan = 'AF' and
1133 p_prior_grade not in ('AA','BB','CC','DD','EE')
1134 and
1135 p_prior_grade is not null
1136 then
1137 hr_utility.set_message(8301, 'GHR_37542_ALL_PROCEDURE_FAIL');
1138 hr_utility.raise_error;
1139 end if;
1140
1141 -- 540.52.2
1142 if p_prior_pay_plan = 'FC' and
1143 p_prior_grade not between '02' and '14'
1144 and
1145 p_prior_grade is not null
1146 then
1147 hr_utility.set_message(8301, 'GHR_37543_ALL_PROCEDURE_FAIL');
1148 hr_utility.raise_error;
1149 end if;
1150
1151 -- 540.55.2
1152 if p_prior_pay_plan = 'GG' and
1153 not( p_prior_grade between '01' and '15' or
1154 p_prior_grade is null or
1155 p_prior_grade = 'SL' )
1156 then
1157 hr_utility.set_message(8301, 'GHR_37544_ALL_PROCEDURE_FAIL');
1158 hr_utility.raise_error;
1159 end if;
1160
1161 -- 540.56.2
1162 -- Update/Change Date By Effective Date Comment
1163 -- 9/5 08/13/99 vravikan 01-Apr-99 New Edit
1164 /* If prior pay plan is NC, then prior grade must be 01 through 03 */
1165 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') then
1166 if p_prior_pay_plan = 'NC' and
1167 not( p_prior_grade in ('01','02','03') or
1168 p_prior_grade is null )
1169 then
1170 hr_utility.set_message(8301, 'GHR_37071_ALL_PROCEDURE_FAIL');
1171 hr_utility.raise_error;
1172 end if;
1173 end if;
1174 -- 540.57.2
1175 -- Update/Change Date By Effective Date Comment
1176 -- 9/5 08/13/99 vravikan 01-Apr-99 New Edit
1177 --upd47 26-Jun-06 Raju 01-Apr-2006 Change prior grade 04 to 05
1178 --7642919 16-Dec-08 Raju 01-Apr-2006 Included grade 04
1179 /* If prior pay plan is NO, then prior grade must be 01 through 04 */
1180 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') then
1181 if p_effective_date < fnd_date.canonical_to_date('2006/04/01') then
1182 if p_prior_pay_plan = 'NO' and
1183 not( p_prior_grade in ('01','02','03','04') or
1184 p_prior_grade is null ) then
1185 hr_utility.set_message(8301, 'GHR_37072_ALL_PROCEDURE_FAIL');
1186 hr_utility.set_message_token('PRIOR_GRD','01 through 04');
1187 hr_utility.raise_error;
1188 end if;
1189 else
1190 if p_prior_pay_plan = 'NO' and
1191 not( p_prior_grade in ('01','02','03','04','05') or
1192 p_prior_grade is null ) then
1193 hr_utility.set_message(8301, 'GHR_37072_ALL_PROCEDURE_FAIL');
1194 hr_utility.set_message_token('PRIOR_GRD','01 through 05');
1195 hr_utility.raise_error;
1196 end if;
1197 end if;
1198 end if;
1199
1200 -- 540.58.2
1201 -- Update/Change Date By Effective Date Comment
1202 -- 9/5 08/13/99 vravikan 01-Apr-99 New Edit
1203 /* If prior pay plan is NP or NR, then prior grade must be 01 through 05 */
1204 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') then
1205 if p_prior_pay_plan in ( 'NP','NR') and
1206 not( p_prior_grade in ('01','02','03','04','05') or
1207 p_prior_grade is null )
1208 then
1209 hr_utility.set_message(8301, 'GHR_37073_ALL_PROCEDURE_FAIL');
1210 hr_utility.raise_error;
1211 end if;
1212 end if;
1213 -- 540.60.2
1214 if p_prior_pay_plan = 'GH' and
1215 not( p_prior_grade between '13' and '15' or
1216 p_prior_grade is null )
1217 then
1218 hr_utility.set_message(8301, 'GHR_37545_ALL_PROCEDURE_FAIL');
1219 hr_utility.raise_error;
1220 end if;
1221
1222 -- 540.61.2
1223 if (p_prior_pay_plan = 'SL' or p_prior_pay_plan = 'XE')
1224 and
1225 p_prior_grade <> '00'
1226 and
1227 p_prior_grade is not null
1228 then
1229 hr_utility.set_message(8301, 'GHR_37546_ALL_PROCEDURE_FAIL');
1230 hr_utility.raise_error;
1231 end if;
1232 -- 540.62.2
1233 -- Update/Change Date By Effective Date Comment
1234 -- 18-Aug-00 vravikan 01-May-2000 New Edit
1235 -- 13-Jun-06 Raju 01-Jan-03 Terminate the edit
1236
1237 /* If Prior Pay plan is VE,
1238 Then prior grade must be 01, 02, or asterisks */
1239 if p_effective_date >= to_date('2000/05/01', 'yyyy/mm/dd') and
1240 p_effective_date < fnd_date.canonical_to_date('2003/01/01') then --Bug# 5073313
1241 if p_prior_pay_plan = 'VE' and
1242 not( p_prior_grade in ('01','02') or
1243 p_prior_grade is null )
1244 then
1245 hr_utility.set_message(8301, 'GHR_37421_ALL_PROCEDURE_FAIL');
1246 hr_utility.raise_error;
1247 end if;
1248 end if;
1249
1250 -- 540.63.2
1251 -- 18-Aug-00 vravikan 01-Jun-2000 New Edit
1252 /* If Prior Pay plan is NB,
1253 Then prior grade must be 01 through 09, or asterisks */
1254 if p_effective_date >= to_date('2000/06/01', 'yyyy/mm/dd') then
1255 if p_prior_pay_plan = 'NB' and
1256 p_prior_grade not in ('01', '02','03','04','05','06','07','08','09')
1257 and p_prior_grade is not null
1258 then
1259 hr_utility.set_message(8301, 'GHR_37422_ALL_PROCEDURE_FAIL');
1260 hr_utility.raise_error;
1261 end if;
1262 end if;
1263 -- Begin Bug# 5073313
1264 --540.65.2
1265 -- Update/Change Date By Effective Date Comment
1266 -- Upd 46 13-Jun-06 Raju 01-Jan-2006 New Edit
1267 If p_effective_date >= to_date('2006/01/01', 'yyyy/mm/dd') then
1268 if p_prior_pay_plan in ('GL') and
1269 p_prior_grade not in ('03','04','05','06','07','08','09','10') and
1270 p_prior_grade is not null
1271 then
1272 hr_utility.set_message(8301, 'GHR_37432_ALL_PROCEDURE_FAIL');
1273 hr_utility.raise_error;
1274 end if;
1275 end if;
1276 -- End Bug# 5073313
1277
1278 --Begin Bug# 5745356
1279 --550.00.2
1280 If p_effective_date >= to_date('2006/10/01', 'yyyy/mm/dd') then
1281 if p_prior_pay_plan in ('GL') and
1282 p_prior_grade > '10' and
1283 p_prior_grade is not null then
1284 --Bug# 6959477 message number 38629 is duplicated, so created new message with #38158
1285 hr_utility.set_message(8301, 'GHR_38158_ALL_PROCEDURE_FAIL');
1286 hr_utility.raise_error;
1287 end if;
1288 end if;
1289 --End Bug# 5745356
1290
1291 end chk_prior_grade;
1292
1293 /* Name:
1294 -- Prior Pay Plan
1295 */
1296
1297 procedure chk_prior_pay_plan
1298 (p_prior_pay_plan in varchar2 --non SF52
1299 ,p_to_pay_plan in varchar2
1300 ,p_first_noac_lookup_code in varchar2
1301 --,p_prior_effective_date in date -- deleted Bug# 6010943
1302 ,p_effective_date in date -- Added Bug# 6010943
1303 ) is
1304 begin
1305
1306 -- 550.02.2
1307 -- Update Date By Effective Date Comment
1308 -- 8 01/28/99 vravikan 10/01/1998 Add nature of action codes 600-610
1309 --upd47 26-Jun-06 Raju From 01-Apr-2006 Added 611,613
1310 -- upd51 06-Feb-07 Raju From 01-Jan-2007 Bug#5745356 add 890
1311 --BuG# 6010943 Raju Modified the p_prior_effective_date to p_effective_date
1312 --
1313 if p_effective_date < fnd_date.canonical_to_date('1998/10/01') then
1314 if p_prior_pay_plan <> p_to_pay_plan and
1315 p_prior_pay_plan is not null and
1316 p_to_pay_plan is not null and
1317 not(p_first_noac_lookup_code in ('702','703','713','721','740','741','850','855','894') or
1318 substr(p_first_noac_lookup_code,1,1)= '5') then
1319 hr_utility.set_message(8301, 'GHR_37547_ALL_PROCEDURE_FAIL');
1320 hr_utility.raise_error;
1321 end if;
1322 elsif p_effective_date < fnd_date.canonical_to_date('2006/04/01') then
1323 if p_prior_pay_plan <> p_to_pay_plan and
1324 p_prior_pay_plan is not null and
1325 p_to_pay_plan is not null and
1326 not(p_first_noac_lookup_code in ('600','601','602','603','604','605','606','607','608','609','610',
1327 '702','703','713','721','740','741','850','855','894') or
1328 substr(p_first_noac_lookup_code,1,1)= '5') then
1329 hr_utility.set_message(8301, 'GHR_37035_ALL_PROCEDURE_FAIL');
1330 hr_utility.set_message_token('NOA_CODE','702, 703, 713, 721, 740, 741, 850, 855 or 894');
1331 hr_utility.raise_error;
1332 end if;
1333 elsif p_effective_date < fnd_date.canonical_to_date('2007/01/01') then
1334 if p_prior_pay_plan <> p_to_pay_plan and
1335 p_prior_pay_plan is not null and
1336 p_to_pay_plan is not null and
1337 not(p_first_noac_lookup_code in ('600','601','602','603','604','605','606','607','608','609','610',
1338 '611','613','702','703','713','721','740','741','850','855','894') or
1339 substr(p_first_noac_lookup_code,1,1)= '5') then
1340 hr_utility.set_message(8301, 'GHR_37035_ALL_PROCEDURE_FAIL');
1341 hr_utility.set_message_token('NOA_CODE','611, 613, 702, 703, 713, 721, 740, 741, 850, 855 or 894 ');
1342 hr_utility.raise_error;
1343 end if;
1344 else
1345 if p_prior_pay_plan <> p_to_pay_plan and
1346 p_prior_pay_plan is not null and
1347 p_to_pay_plan is not null and
1348 not(p_first_noac_lookup_code in ('600','601','602','603','604','605','606','607','608','609','610',
1349 '611','613','702','703','713','721','740','741','850','855','890','894') or
1350 substr(p_first_noac_lookup_code,1,1)= '5') then
1351 hr_utility.set_message(8301, 'GHR_37035_ALL_PROCEDURE_FAIL');
1352 hr_utility.set_message_token('NOA_CODE','611, 613, 702, 703, 713, 721, 740, 741, 850, 855, 890, or 894');
1353 hr_utility.raise_error;
1354 end if;
1355 end if;
1356
1357 end chk_prior_pay_plan;
1358
1359
1360
1361 -- Prior Pay Rate Determinant
1362
1363
1364 procedure chk_prior_pay_rate_determinant
1365 (p_prior_pay_rate_det_code in varchar2 --non SF52 item
1366 ,p_pay_rate_determinant in varchar2
1367 ,p_prior_pay_plan in varchar2 --non SF52 item
1368 ,p_to_pay_plan in varchar2
1369 ,p_agency in varchar2
1370 ,p_First_NOAC_Lookup_Code in varchar2
1371 ,p_prior_duty_stn in varchar2 --non SF52 item
1372 ,p_prior_effective_date in date
1373 ,P_effective_date in date -- FWFA Change
1374 ) is
1375 l_session ghr_history_api.g_session_var_type;
1376
1377 begin
1378
1379 -- 560.02.2
1380 -- UPD 43(Bug 4567571) Raju 09-Nov-2005 Delete Prior PRD M effective date from 01-May-2005
1381 IF P_effective_date < to_date('2005/05/01','YYYY/MM/DD') THEN
1382 if p_First_NOAC_Lookup_Code = '741' and
1383 p_prior_pay_rate_det_code not in ('A','B','E','F','M','U','V') and
1384 p_prior_pay_rate_det_code is not null
1385 then
1386 hr_utility.set_message(8301, 'GHR_37548_ALL_PROCEDURE_FAIL');
1387 hr_utility.set_message_token('PRD_LIST','A, B, E, F, M, U, or V');
1388 hr_utility.raise_error;
1389 end if;
1390 ELSIF P_effective_date >= to_date('2005/05/01','YYYY/MM/DD') THEN
1391 if p_First_NOAC_Lookup_Code = '741' and
1392 p_prior_pay_rate_det_code not in ('A','B','E','F','U','V') and
1393 p_prior_pay_rate_det_code is not null
1394 then
1395 hr_utility.set_message(8301, 'GHR_37548_ALL_PROCEDURE_FAIL');
1396 hr_utility.set_message_token('PRD_LIST','A, B, E, F, U, or V');
1397 hr_utility.raise_error;
1398 end if;
1399 END IF;
1400
1401 -- 560.04.2
1402 -- 06/25/03 vravikan By passing this edit if the
1403 -- employee on RG Temporary promotion
1404 --upd47 26-Jun-06 Raju From 01-Apr-2006 Added pay plan is other than Yx condition
1405 if P_effective_date < fnd_date.canonical_to_date('2006/04/01') then
1406 IF GHR_GHRWS52L.g_temp_step IS NULL THEN
1407 if p_First_NOAC_Lookup_Code = '703' and
1408 p_prior_pay_rate_det_code in ('A','B','E','F','U','V') and
1409 p_pay_rate_determinant not in ('A','B','E','F','U','V') and
1410 p_pay_rate_determinant is not null
1411 then
1412 hr_utility.set_message(8301, 'GHR_37549_ALL_PROCEDURE_FAIL');
1413 hr_utility.set_message_token('PRD_CODE','A, B, E, F, U, or V');
1414 hr_utility.raise_error;
1415 end if;
1416 END IF;
1417 ELSE
1418 IF GHR_GHRWS52L.g_temp_step IS NULL THEN
1419 if p_First_NOAC_Lookup_Code = '703' and
1420 p_prior_pay_rate_det_code in ('A','B','E','F','U','V') and
1421 substr(p_prior_pay_plan,1,1) <> ('Y') and
1422 p_pay_rate_determinant not in ('A','B','E','F','U','V') and
1423 p_pay_rate_determinant is not null
1424 then
1425 hr_utility.set_message(8301, 'GHR_37549_ALL_PROCEDURE_FAIL');
1426 hr_utility.set_message_token('PRD_CODE','A, B, E, F, U, or V, and pay plan is other than Yx');
1427 hr_utility.raise_error;
1428 end if;
1429 END IF;
1430 END IF;
1431
1432 -- 560.06.2
1433 if p_prior_pay_rate_det_code = 'C' and
1434 (p_prior_pay_plan in ('ED','EE','EF','EG','EH','EI','ZZ') or
1435 substr(p_prior_pay_plan,1,1) in ('B','W','X'))
1436 then
1437 hr_utility.set_message(8301, 'GHR_37550_ALL_PROCEDURE_FAIL');
1438 hr_utility.raise_error;
1439 end if;
1440
1441 -- 560.07.2
1442 if p_prior_pay_rate_det_code = '4' and
1443 ( substr(p_prior_pay_plan,1,1) ='W' or
1444 substr(p_prior_pay_plan,1,1) ='X' )
1445 then
1446 hr_utility.set_message(8301, 'GHR_37551_ALL_PROCEDURE_FAIL');
1447 hr_utility.raise_error;
1448 end if;
1449
1450 -- 560.10.2
1451 -- 06/25/03 vravikan By passing this edit if the
1452 -- employee on RG Temporary promotion
1453 -- 10/30/03 Ashley Add nature of action 849 to the list
1454 -- 10/03/05 vnarasim Added 892,893 in the other than list.
1455 -- 10/21/05 utokachi Validate for actions prior to 01-MAY-2005
1456 -- UPD 43(Bug 4567571) Raju 09-Nov-2005 Delete Prior PRD and PRD M effective date from 01-May-2005
1457 -- UPD 45(Bug 4567571) Raju 14-Nov-2005 Terminate the edit effective 01-Sep-2005
1458
1459 IF P_effective_date < to_date('2005/09/01','YYYY/MM/DD') THEN
1460 -- FWFA Changes
1461 IF P_effective_date < to_date('2005/05/01','YYYY/MM/DD') THEN
1462 IF GHR_GHRWS52L.g_temp_step IS NULL THEN
1463 if (substr(p_First_NOAC_Lookup_Code,1,1) = '7'
1464 or
1465 substr(p_First_NOAC_Lookup_Code,1,1)='8')
1466 and
1467 p_First_NOAC_Lookup_Code not in ('702','703','713','721','740','800','815','816','817',
1468 '825','840','841','842','843','844','845','846',
1469 '847','848','849','866','878','879','892','893','894','899')
1470 and
1471 p_pay_rate_determinant <> 'M'
1472 and
1473 (p_prior_pay_rate_det_code not in ('5','7','M') or p_prior_pay_rate_det_code is null)
1474 and
1475 p_pay_rate_determinant <> p_prior_pay_rate_det_code
1476 and
1477 p_pay_rate_determinant is not null
1478 then
1479 hr_utility.set_message(8301, 'GHR_37552_ALL_PROCEDURE_FAIL');
1480 hr_utility.raise_error;
1481 end if;
1482 END IF;
1483 ELSIF P_effective_date >= to_date('2005/05/01','YYYY/MM/DD') THEN
1484 IF GHR_GHRWS52L.g_temp_step IS NULL THEN
1485 if (substr(p_First_NOAC_Lookup_Code,1,1) = '7'
1486 or
1487 substr(p_First_NOAC_Lookup_Code,1,1)='8')
1488 and
1489 p_First_NOAC_Lookup_Code not in ('702','703','713','721','740','800','815','816','817',
1490 '825','840','841','842','843','844','845','846',
1491 '847','848','849','866','878','879','892','893','894','899')
1492 and
1493 (p_prior_pay_rate_det_code not in ('5','7') or p_prior_pay_rate_det_code is null)
1494 and
1495 p_pay_rate_determinant <> p_prior_pay_rate_det_code
1496 and
1497 p_pay_rate_determinant is not null
1498 then
1499 hr_utility.set_message(8301, 'GHR_38986_ALL_PROCEDURE_FAIL');
1500 hr_utility.raise_error;
1501 end if;
1502 END IF;
1503 END IF;
1504 -- FWFA Changes
1505 End if;
1506
1507
1508 -- 560.13.2
1509 if p_prior_pay_plan in ('FA','ST','EX') and
1510 p_prior_pay_rate_det_code not in ('C','S','0') and
1511 p_prior_pay_rate_det_code is not null
1512 then
1513 hr_utility.set_message(8301, 'GHR_37553_ALL_PROCEDURE_FAIL');
1514 hr_utility.raise_error;
1515 end if;
1516
1517 -- 560.28.2
1518 -- Update Date By Effective Date Comment
1519 -------------------------------------------------------------------------------------------------
1520 --Bug#825741 03/08/99 vravikan Code correction
1521 -- 18/10/04 Madhuri start of the edit including the PRD - 2 to the list
1522 --
1523 if p_prior_pay_plan = 'ES' and
1524 p_prior_pay_rate_det_code not in ('C','0','2') and
1525 p_prior_pay_rate_det_code is not null
1526 then
1527 hr_utility.set_message(8301, 'GHR_37554_ALL_PROCEDURE_FAIL');
1528 hr_utility.raise_error;
1529 end if;
1530
1531 -- 560.30.2
1532 --
1533 -- Get the session variable to check whether the action is correction
1534 -- If correction skip this edit as the prior_duty_station value might
1535 -- be incorrect. Bug #709282
1536 --
1537 -------------------------------------------------------------------------------
1538 -- Modified by Date Comments
1539 -------------------------------------------------------------------------------
1540 -- Madhuri 01-MAR-05 Retroactively end dating as of 31-JAN-2002
1541 -------------------------------------------------------------------------------
1542 IF p_prior_effective_date <= fnd_date.canonical_to_date('20'||'02/01/31') THEN
1543
1544 ghr_history_api.get_g_session_var(l_session);
1545 If l_session.noa_id_correct is null then
1546
1547 if p_prior_pay_rate_det_code = 'M' and
1548 /* This is the code for the cities of Boston, Chicago, Los Angeles, New York, Philadelphia, San Diego,
1549 San Francisco, and Washington D.C.*/
1550 (
1551 substr(p_prior_duty_stn,1,2) not in ('05','08','41','45','56','71','74','80','11') and
1552
1553 /* This selects the counties that make up Boston CMSA */
1554 (
1555 substr(p_prior_duty_stn,1,2) = '25' and
1556 substr(p_prior_duty_stn,7,3) not in ('009','017','021','023','025')
1557 ) and
1558 /* This selects the parts of other counties that make up Boston CMSA */
1559 /* part of Bristol County */
1560 p_prior_duty_stn not in ('250007005','250039005','250096005','250188005','250251005',
1561 '250254005','250281005','250299005','250315005','250385005','250670005','250850005','250911005',
1562 '250912005','250913005','250924005','251064005','251062005','251135005','251219005','251225005',
1563 '251280005')
1564 and
1565 /* part of Hampden County */
1566 p_prior_duty_stn <> '250489013'
1567 and
1568 /* part of Worcester County */
1569 p_prior_duty_stn not in ('250032027','250055027','250079027','250080027','250098027',
1570 '250110027','250902027','250910027','250916027','250918027','250927027','250944027','250117027',
1571 '250123027','250150027','250185027','250186027','250189027','250220027','250252027','250263027',
1572 '250272027','250280027','250332027','250350027','250390027','250436027','250467027','250480027',
1573 '250510027','250555027','250565027','250585027','250610027','250619027','250640027','250664027',
1574 '250745027','250780027','250785027','250820027','250834027','250900027','250943027','250980027',
1575 '250999027','251450027','251800027','251100027','251172027','251200027','251203027','251204027',
1576 '251210027','251228027','251240027','251260027','251273027','251266027','251271027','251278027',
1577 '251269027','251283027','251310027','251320027','251376027','251380027','251390027','251395027',
1578 '251410027','251439027','251455027','251470027','251500027','251520027')
1579 and
1580 /* New Hampshire */
1581 /* part of Hillsborough County */
1582 p_prior_duty_stn not in ('330011011','330018011','330031011','330160011','330180011',
1583 '330234011','330240011','330299011','330310011','330324011','330334011','330340011','330344011',
1584 '330350011','330357011','330401011','330434011','330509011','330540011')
1585 and
1586 /* part of Merrimack County */
1587 p_prior_duty_stn <> '330236013' and
1588 /* part of Rockingham County */
1589 p_prior_duty_stn not in ('330012015','330013015','330025015','330032015','330045015',
1590 '330087015','330085015','330105015','330108015','330112015','330123015','330130015','330153015',
1591 '330176015','330195015','330200015','330199015','330201015','330252015','330355015','330354015',
1592 '330356015','330370015','330381015','330382015','330391015','330384015','330417015','330430015',
1593 '330435015','330445015','330447015','330448015','330462015','330466015','330474015','330475015',
1594 '330478015','330255015','330305015','330533015','330527015','330551015')
1595 and
1596 /* part of Strafford County */
1597 p_prior_duty_stn not in ('330029017','330090017','330100017','330140017','330281017',
1598 '330311017','330342017','330345017','330440017','330443017','330470017')
1599 and
1600 /* Maine */
1601 /* part of York County */
1602 p_prior_duty_stn not in ('230450031','231445031','232450031','234250031','234300031',
1603 '237450031','239800031','239900031','239950031')
1604 and
1605 /* Connecticut */
1606 /* part of Windham County */
1607 p_prior_duty_stn not in ('090231015','090259015','090373015','090500015','090603015',
1608 '090749015')
1609 and
1610 /* Chiacago */
1611 /* Illinois */
1612 (
1613 substr(p_prior_duty_stn,1,2) = '17' and
1614 substr(p_prior_duty_stn,7,3) not in ('031','037','043','063','089','091','093','097',
1615 '111','197')
1616 ) and
1617 /* Indiana */
1618 (
1619 substr(p_prior_duty_stn,1,2) = '18' and
1620 substr(p_prior_duty_stn,7,3) not in ('089','027')
1621 ) and
1622 /* Wisconsin */
1623 (
1624 substr(p_prior_duty_stn,1,2) = '55' and
1625 substr(p_prior_duty_stn,7,3) <> '059'
1626 ) and
1627 /* Los Angeles */
1628 (
1629 (substr(p_prior_duty_stn,1,2) = '06' and
1630 substr(p_prior_duty_stn,7,3) not in ('037','059','065','071','083','111')) and
1631 p_prior_duty_stn <> '061077029'
1632 ) and
1633 /* New York */
1634 (
1635 substr(p_prior_duty_stn,1,2) = '36' and
1636 substr(p_prior_duty_stn,7,3) not in ('005','027','047','059','061','071','079','081',
1637 '085','087','103','119')
1638 ) and
1639 /* New Jersey */
1640 (
1641 substr(p_prior_duty_stn,1,2) = '34' and
1642 substr(p_prior_duty_stn,7,3) not in ('003','013','017','019','021','023','025','027',
1643 '029','031','035','037','039','041')
1644 ) and
1645 /* Connecticut */
1646 (
1647 substr(p_prior_duty_stn,1,2) = '09' and
1648 substr(p_prior_duty_stn,7,3) not in ('001','0009')
1649 ) and
1650 /* part of Litchfield County */
1651 p_prior_duty_stn not in ('090051005','090083005','090247005','090629005','090740005',
1652 '090802005','090805005','090450005','090454005','090535005','090817005','090857005')
1653 and
1654 /* part of Middlesex County */
1655 p_prior_duty_stn not in ('090130007','090332007')
1656 and
1657 /* Pennsylvania */
1658 (
1659 substr(p_prior_duty_stn,1,2) = '42' and
1660 substr(p_prior_duty_stn,7,3) <> '103'
1661 ) and
1662 /* Philadelphia */
1663 /* Pennsylvania */
1664 (
1665 substr(p_prior_duty_stn,1,2) = '42' and
1666 substr(p_prior_duty_stn,7,3) not in ('017','029','045','091','101')
1667 ) and
1668 /* New Jersey */
1669 (
1670 substr(p_prior_duty_stn,1,2) = '34' and
1671 substr(p_prior_duty_stn,7,3) not in ('001','005','007','009','011','015','033')
1672 ) and
1673 /* Delaware */
1674 (
1675 substr(p_prior_duty_stn,1,2) = '10' and
1676 substr(p_prior_duty_stn,7,3) <> '015'
1677 ) and
1678 /* San Diego */
1679 (
1680 substr(p_prior_duty_stn,1,2) = '06' and
1681 substr(p_prior_duty_stn,7,3) <> '073'
1682 ) and
1683 /* San Francisco */
1684 (
1685 substr(p_prior_duty_stn,1,2) = '06' and
1686 substr(p_prior_duty_stn,7,3) not in ('001','013','041','055','075','081','085',
1687 '087','095','097')
1688 ) and
1689 /* Washington DC */
1690 /* Maryland */
1691 (
1692 substr(p_prior_duty_stn,1,2) = '24' and
1693 substr(p_prior_duty_stn,7,3) not in ('003','005','009','013','017','021','025',
1694 '027','031','033','035','037','043','510')
1695 ) and
1696 /* Virginia */
1697 (
1698 substr(p_prior_duty_stn,1,2) = '51' and
1699 substr(p_prior_duty_stn,7,3) not in ('013','043','047','059','061','099','107',
1700 '153','177','179','187','510','600','610','630','683','685')
1701 ) and
1702 /* West Virginia */
1703 (
1704 substr(p_prior_duty_stn,1,2) = '54' and
1705 substr(p_prior_duty_stn,7,3) not in ('003','037')
1706 )
1707 ) then
1708 hr_utility.set_message(8301, 'GHR_37266_ALL_PROCEDURE_FAIL');
1709 hr_utility.raise_error;
1710 end if;
1711 end if;
1712 END IF; -- End of checking date <= 31 Jan 2002
1713 -------------------------------------------------------------------------------
1714 -- 560.40.2
1715 -- Update/Change Date By Effective Date Comment
1716 -- 9/4 08/10/99 vravikan 01-Mar-99 Exclude PRD T.
1717 If p_prior_effective_date >= fnd_date.canonical_to_date('19'||'99/03/01') then
1718 if p_prior_pay_plan in ('GM','GS') and
1719 p_prior_pay_rate_det_code in ('P','T') then
1720 hr_utility.set_message(8301, 'GHR_37063_ALL_PROCEDURE_FAIL');
1721 hr_utility.raise_error;
1722 end if;
1723 else
1724 if p_prior_pay_plan in ('GM','GS') and
1725 p_prior_pay_rate_det_code = 'P' then
1726 hr_utility.set_message(8301, 'GHR_37556_ALL_PROCEDURE_FAIL');
1727 hr_utility.raise_error;
1728 end if;
1729 end if;
1730
1731 -- 560.43.2
1732 -- If prior pay rate determinant is Z,
1733 -- Then prior pay plan must not be FO or FP,
1734 -- And agency must be AM, GY, or ST,
1735 -- And the first two positions of prior duty station must be CA or MX.
1736
1737 -- Date By Effective Date Comment
1738 -- 30-OCT-2003 Ashley From the Begining New Edit.
1739
1740 IF p_prior_pay_rate_det_code = 'Z' AND
1741 p_prior_pay_plan IN ('FO','FP') AND
1742 p_agency NOT IN ('AM','GY','ST') AND
1743 substr(p_prior_duty_stn,1,2) NOT IN ('CA','MX') THEN
1744 hr_utility.set_message(8301, 'GHR_38842_ALL_PROCEDURE_FAIL');
1745 hr_utility.raise_error;
1746 END IF;
1747
1748 end chk_prior_pay_rate_determinant;
1749
1750
1751
1752
1753 /* Name:
1754 -- Prior Step or Rate
1755 */
1756
1757 procedure chk_prior_step_or_rate
1758 (p_prior_step_or_rate in varchar2 --non SF52
1759 ,p_first_noac_lookup_code in varchar2
1760 ,p_to_step_or_rate in varchar2
1761 ,p_pay_rate_determinant_code in varchar2
1762 ,p_to_pay_plan in varchar2
1763 ,p_prior_pay_rate_det_code in varchar2 --non SF52
1764 ,p_prior_pay_plan in varchar2 --non SF52
1765 ,p_prior_grade in varchar2 --non SF52
1766 ,p_prior_effective_date in date
1767 ,p_cur_appt_auth_1 in varchar2
1768 ,p_cur_appt_auth_2 in varchar2
1769 ,p_effective_date in date
1770
1771 ) is
1772 begin
1773
1774 -- 580.02.2
1775 -- Date By Effective Date Comment
1776 -- 30-OCT-2003 Ashley From the Begining Added nature of action 849.
1777 -- upd50 06-Feb-07 Raju From 01-Oct-2006 Bug#5745356 delete NOA 849
1778 -- upd51 06-Feb-07 Raju From 01-Jan-2007 Bug#5745356 delete 815-817, 825
1779 -- 840-848,878-879. Add 890
1780 -- upd53 20-Apr-07 Raju From 01-Jan-2007 Bug#5996938 added 815-817, 825
1781 -- 827,840-849,878-879.
1782 -- 27-Jul-07 Raju 6279690 Added 890 from the beginning.
1783 -- 890 is there in the previous versions. erroneously deleted while doing upd53.
1784
1785
1786 IF p_prior_effective_date < to_date('2006/10/01','yyyy/mm/dd') then
1787 if (substr(p_first_noac_lookup_code,1,1) = '7' or
1788 substr(p_first_noac_lookup_code,1,1) = '8') and
1789 p_first_noac_lookup_code not in ('702','703','713','721','740','815','816','817','840','841',
1790 '842','843','844','845','846','847','848','849','855','866',
1791 '867','868','878','879','890','891','892','893','894','899') and
1792 p_to_step_or_rate is not null and
1793 p_prior_step_or_rate is not null and
1794 p_prior_step_or_rate <> p_to_step_or_rate
1795 then
1796 hr_utility.set_message(8301, 'GHR_37557_ALL_PROCEDURE_FAIL');
1797 hr_utility.raise_error;
1798 end if;
1799 ELSIF p_prior_effective_date < to_date('2007/01/01','yyyy/mm/dd') THEN
1800 if (substr(p_first_noac_lookup_code,1,1) = '7' or
1801 substr(p_first_noac_lookup_code,1,1) = '8') and
1802 p_first_noac_lookup_code not in ('702','703','713','721','740','815','816','817','840','841',
1803 '842','843','844','845','846','847','848','855','866',
1804 '867','868','878','879','890','891','892','893','894','899') and
1805 p_to_step_or_rate is not null and
1806 p_prior_step_or_rate is not null and
1807 p_prior_step_or_rate <> p_to_step_or_rate
1808 then
1809 --Bug# 6959477 message number 38805 is duplicated, so created new message with #38159
1810 hr_utility.set_message(8301, 'GHR_38159_ALL_PROCEDURE_FAIL');
1811 hr_utility.raise_error;
1812 end if;
1813 ELSE
1814 if (substr(p_first_noac_lookup_code,1,1) = '7' or
1815 substr(p_first_noac_lookup_code,1,1) = '8') and
1816 p_first_noac_lookup_code not in ('702','703','713','721','740','815','816','817','825','827',
1817 '840','841','842','843','844','845','846','847','848','849',
1818 '855','866','867','868','871','878','879','890','891','892','893','894','899') and
1819 p_to_step_or_rate is not null and
1820 p_prior_step_or_rate is not null and
1821 p_prior_step_or_rate <> p_to_step_or_rate
1822 then
1823 --Bug# 6959477 message number 38584 is duplicated, so created new message with #38156
1824 hr_utility.set_message(8301, 'GHR_38156_ALL_PROCEDURE_FAIL');
1825 hr_utility.raise_error;
1826 end if;
1827 END IF;
1828
1829 -- 580.03.2
1830 if p_prior_pay_plan = 'FG'
1831 and
1832 p_prior_step_or_rate not in ('00','01','02','03','04','05','06',
1833 '07','08','09','10')
1834 and
1835 p_prior_step_or_rate is not null
1836 then
1837 hr_utility.set_message(8301, 'GHR_37558_ALL_PROCEDURE_FAIL');
1838 hr_utility.raise_error;
1839 end if;
1840
1841 -- 580.04.2
1842 -- UPD 43(Bug 4567571) Raju 09-Nov-2005 Delete PRD M effective date from 01-May-2005
1843 if p_prior_effective_date < to_date('2005/05/01','yyyy/mm/dd') then
1844 if p_to_pay_plan = 'GS' and
1845 (p_first_noac_lookup_code = '892' or
1846 p_first_noac_lookup_code = '893') and
1847 p_pay_rate_determinant_code in ('0','5','6','7','M') and
1848 to_number(p_prior_step_or_rate) > to_number(p_to_step_or_rate) and
1849 p_prior_step_or_rate is not null
1850 then
1851 hr_utility.set_message(8301, 'GHR_37559_ALL_PROCEDURE_FAIL');
1852 hr_utility.set_message_token('PRD_LIST','0, 5, 6, 7, or M');
1853 hr_utility.raise_error;
1854 end if;
1855 elsif p_prior_effective_date >= to_date('2005/05/01','yyyy/mm/dd') then
1856 if p_to_pay_plan = 'GS' and
1857 (p_first_noac_lookup_code = '892' or
1858 p_first_noac_lookup_code = '893') and
1859 p_pay_rate_determinant_code in ('0','5','6','7') and
1860 to_number(p_prior_step_or_rate) > to_number(p_to_step_or_rate) and
1861 p_prior_step_or_rate is not null
1862 then
1863 hr_utility.set_message(8301, 'GHR_37559_ALL_PROCEDURE_FAIL');
1864 hr_utility.set_message_token('PRD_LIST','0, 5, 6, or 7');
1865 hr_utility.raise_error;
1866 end if;
1867 end if;
1868
1869
1870 -- 580.07.2
1871 if (p_prior_pay_plan = 'CE' or p_prior_pay_plan = 'CY')
1872 and
1873 (to_number(p_prior_step_or_rate) not between 00 and 21)
1874 and
1875 p_prior_step_or_rate is not null
1876 then
1877 hr_utility.set_message(8301, 'GHR_37560_ALL_PROCEDURE_FAIL');
1878 hr_utility.raise_error;
1879 end if;
1880
1881 -- 580.10.2
1882 -- 17-Aug-00 vravikan 01-jan-2000 Delete 99 form step/rate codes
1883 if p_prior_effective_date >= to_date('2000/01/01','yyyy/mm/dd') then
1884 if p_prior_pay_plan = 'GM' and p_prior_step_or_rate <> '00'
1885 and
1886 p_prior_step_or_rate is not null
1887 then
1888 hr_utility.set_message(8301, 'GHR_37424_ALL_PROCEDURE_FAIL');
1889 hr_utility.raise_error;
1890 end if;
1891 else
1892 if p_prior_pay_plan = 'GM' and
1893 (p_prior_step_or_rate <>'00' and p_prior_step_or_rate <>'99' )
1894 and
1895 p_prior_step_or_rate is not null
1896 then
1897 hr_utility.set_message(8301, 'GHR_37561_ALL_PROCEDURE_FAIL');
1898 hr_utility.raise_error;
1899 end if;
1900 end if;
1901
1902 -- 580.13.2
1903 -- NAME EFFECTIVE COMMENTS
1904 -- Madhuri 21-JAN-2004 End Dating this edit as on 10-JAN-04
1905 -- For SES Pay Calculations
1906 --p_effective_date
1907 --upd47 26-Jun-06 Raju From 01-Jan-2004 Terminate the edit
1908 if p_effective_date < fnd_date.canonical_to_date('2004/01/01') then
1909 if (p_effective_date < to_date('2004/01/11', 'yyyy/mm/dd') and
1910 (p_prior_pay_plan = 'ES' or p_prior_pay_plan = 'FE') and
1911 (to_number(p_prior_step_or_rate) not between 1 and 6) and
1912 p_prior_step_or_rate is not null ) then
1913 hr_utility.set_message(8301, 'GHR_37562_ALL_PROCEDURE_FAIL');
1914 hr_utility.raise_error;
1915 end if;
1916 end if;
1917 -- 580.19.2
1918 --upd47 26-Jun-06 Raju From 01-Apr-2003 Terminate the edit
1919 if p_effective_date < fnd_date.canonical_to_date('2003/04/01') then
1920 if (p_prior_pay_rate_det_code in ('2','3','4') or
1921 p_prior_pay_rate_det_code in ('A','B','C','D','E','F','G','H','I','J',
1922 'K','L','N','O','P','Q','R','S','T','U',
1923 'V','W','X','Y','Z')) and
1924 p_prior_pay_plan not in ('WT','FA','EX') and
1925 p_prior_step_or_rate <>'00' and
1926 p_prior_step_or_rate is not null
1927 then
1928 hr_utility.set_message(8301, 'GHR_37563_ALL_PROCEDURE_FAIL');
1929 hr_utility.raise_error;
1930 end if;
1931 end if;
1932
1933 -- 580.22.2
1934 -- 12/8/00 vravikan From the Start Add UAM
1935 -- Madhuri 19-MAY-04 Madhuri From the Start Included VP for list of prior Pay Plan
1936 --
1937 if (p_prior_pay_plan in ('GS', 'GG', 'VP') and
1938 (p_prior_grade between '01' and '15') and
1939 p_prior_pay_rate_det_code in ('0','5','6','7') and
1940 p_cur_appt_auth_1 <> 'UAM' and p_cur_appt_auth_2 <> 'UAM' and
1941 (to_number(p_prior_step_or_rate) not between 1 and 10) and
1942 p_prior_step_or_rate is not null )
1943 then
1944 hr_utility.set_message(8301, 'GHR_37564_ALL_PROCEDURE_FAIL');
1945 hr_utility.raise_error;
1946 end if;
1947
1948 -- 580.25.2
1949 -- 18-Sep-00 vravikan From the Start Add VE
1950 -- 19-MAY-04 Madhuri From Start Removed pay plan VP from list
1951 -- 13-Jun-06 Raju 01-Jan-03 Terminate the edit
1952 --
1953 IF p_effective_date < fnd_date.canonical_to_date('2003/01/01') then --Bug# 5073313
1954 if (p_prior_pay_plan in ('VE','VM'/*,'VP'*/)) and
1955 p_prior_grade <> '97' and
1956 (to_number(p_prior_step_or_rate) not between 0 and 10) and
1957 p_prior_step_or_rate is not null
1958 then
1959 hr_utility.set_message(8301, 'GHR_37565_ALL_PROCEDURE_FAIL');
1960 hr_utility.raise_error;
1961 end if;
1962 END IF;
1963
1964 -- 580.26.2
1965 if p_prior_pay_plan = 'VM' and p_prior_grade = '97' and
1966 (to_number(p_prior_step_or_rate) not between 0 and 9)
1967 and
1968 p_prior_step_or_rate is not null
1969 then
1970 hr_utility.set_message(8301, 'GHR_37566_ALL_PROCEDURE_FAIL');
1971 hr_utility.raise_error;
1972 end if;
1973
1974 -- 580.27.2
1975 if p_prior_pay_plan = 'VN' and
1976 (to_number(p_prior_step_or_rate) not between 0 and 28)
1977 and
1978 p_prior_step_or_rate is not null
1979 then
1980 hr_utility.set_message(8301, 'GHR_37567_ALL_PROCEDURE_FAIL');
1981 hr_utility.raise_error;
1982 end if;
1983
1984 -- 580.29.2
1985 if p_prior_pay_plan = 'XE' and
1986 (to_number(p_prior_step_or_rate) not between 1 and 3)
1987 and
1988 p_prior_step_or_rate is not null
1989 then
1990 hr_utility.set_message(8301, 'GHR_37568_ALL_PROCEDURE_FAIL');
1991 hr_utility.raise_error;
1992 end if;
1993
1994 -- 580.31.2
1995 if p_prior_pay_plan = 'FO' and
1996 (to_number(p_prior_step_or_rate) not between 1 and 14)
1997 and
1998 p_prior_step_or_rate is not null
1999 then
2000 hr_utility.set_message(8301, 'GHR_37569_ALL_PROCEDURE_FAIL');
2001 hr_utility.raise_error;
2002 end if;
2003
2004 -- 580.32.2
2005 if (p_prior_pay_plan = 'FP' and p_prior_grade between '01' and '09') and
2006 (to_number(p_prior_step_or_rate) not between 1 and 14)
2007 and
2008 p_prior_step_or_rate is not null
2009 then
2010 hr_utility.set_message(8301, 'GHR_37570_ALL_PROCEDURE_FAIL');
2011 hr_utility.raise_error;
2012 end if;
2013
2014 -- 580.33.2
2015 if (p_prior_pay_plan = 'FP' and p_prior_grade in ('AA','BB','CC','DD','EE') and
2016 to_number(p_prior_step_or_rate) not between 1 and 5)
2017 and
2018 p_prior_step_or_rate is not null
2019 then
2020 hr_utility.set_message(8301, 'GHR_37571_ALL_PROCEDURE_FAIL');
2021 hr_utility.raise_error;
2022 end if;
2023
2024 -- 580.34.2
2025 if p_prior_pay_plan = 'AF' and
2026 (to_number(p_prior_step_or_rate) not between 1 and 5)
2027 and
2028 p_prior_step_or_rate is not null
2029 then
2030 hr_utility.set_message(8301, 'GHR_37572_ALL_PROCEDURE_FAIL');
2031 hr_utility.raise_error;
2032 end if;
2033
2034 -- 580.37.2
2035 if (p_prior_pay_plan = 'FC' and
2036 p_prior_grade between '02' and '12') and
2037 (to_number(p_prior_step_or_rate) not between 1 and 10)
2038 and
2039 p_prior_step_or_rate is not null
2040 then
2041 hr_utility.set_message(8301, 'GHR_37573_ALL_PROCEDURE_FAIL');
2042 hr_utility.raise_error;
2043 end if;
2044
2045 -- 580.40.2
2046 if (p_prior_pay_plan = 'FC' and p_prior_grade = '13') and
2047 (to_number(p_prior_step_or_rate) not between 1 and 9)
2048 and
2049 p_prior_step_or_rate is not null
2050 then
2051 hr_utility.set_message(8301, 'GHR_37574_ALL_PROCEDURE_FAIL');
2052 hr_utility.raise_error;
2053 end if;
2054
2055 -- 580.43.2
2056 if (p_prior_pay_plan = 'FC' and p_prior_grade = '14') and
2057 (to_number(p_prior_step_or_rate) not between 1 and 5)
2058 and
2059 p_prior_step_or_rate is not null
2060 then
2061 hr_utility.set_message(8301, 'GHR_37575_ALL_PROCEDURE_FAIL');
2062 hr_utility.raise_error;
2063 end if;
2064
2065 -- 580.46.2
2066 if p_prior_pay_plan in ('CA','SL','ST') and
2067 p_prior_step_or_rate <>'00'
2068 and
2069 p_prior_step_or_rate is not null
2070 then
2071 hr_utility.set_message(8301, 'GHR_37576_ALL_PROCEDURE_FAIL');
2072 hr_utility.raise_error;
2073 end if;
2074
2075 -- 580.49.2
2076 if p_prior_pay_plan = 'AL'
2077 and
2078 (p_prior_grade ='01' or p_prior_grade ='02')
2079 and
2080 p_prior_step_or_rate <>'00'
2081 and
2082 p_prior_step_or_rate is not null
2083 then
2084 hr_utility.set_message(8301, 'GHR_37577_ALL_PROCEDURE_FAIL');
2085 hr_utility.raise_error;
2086 end if;
2087
2088 -- 580.52.2
2089 if p_prior_pay_plan = 'AL' and p_prior_grade = '03' and
2090 -- p_prior_step_or_rate not in ('A','B','C','D','E','F') bug # 612826
2091 p_prior_step_or_rate not in ('01','02','03','04','05','06')
2092 and
2093 p_prior_step_or_rate is not null
2094 then
2095 hr_utility.set_message(8301, 'GHR_37578_ALL_PROCEDURE_FAIL');
2096 hr_utility.raise_error;
2097 end if;
2098
2099 -- 580.55.2
2100 if p_prior_pay_plan = 'GG' and p_prior_grade = 'SL' and
2101 p_prior_step_or_rate <>'00'
2102 and
2103 p_prior_step_or_rate is not null
2104 then
2105 hr_utility.set_message(8301, 'GHR_37579_ALL_PROCEDURE_FAIL');
2106 hr_utility.raise_error;
2107 end if;
2108
2109 -- 580.57.2
2110 if (p_prior_pay_plan = 'IJ' and p_prior_pay_rate_det_code in ('0','7')) then
2111 if NVL(p_prior_step_or_rate, '01') not in ('01','02','03','04') then
2112 hr_utility.set_message(8301, 'GHR_38413_ALL_PROCEDURE_FAIL');
2113 hr_utility.raise_error;
2114 end if;
2115 end if;
2116
2117 end chk_prior_step_or_rate;
2118
2119 end GHR_CPDF_CHECK5;
[Home] [Help]