1 package body GHR_CPDF_CHECK7 as
2 /* $Header: ghcpdf07.pkb 120.29 2011/10/27 07:15:54 utokachi ship $ */
3
4 max_basic_pay number(10,2);
5 min_basic_pay number(10,2);
6
7 -- PRIOR BASIC PAY
8
9 procedure chk_prior_basic_pay
10 (p_prior_pay_plan in varchar2 /* Non-SF52 Data Item */
11 ,p_pay_determinant_code in varchar2
12 ,p_prior_pay_rate_det_code in varchar2 /* Non-SF52 Data Item */
13 ,p_prior_basic_pay in varchar2 /* Non-SF52 Data Item */
14 ,p_retained_pay_plan in varchar2 /* Non-SF52 Data Item */
15 ,p_retained_grade in varchar2 /* Non-SF52 Data Item */
16 ,p_retained_step in varchar2 /* Non-SF52 Data Item */
17 ,p_agency_subelement in varchar2 /* Non-SF52 Data Item */
18 ,p_prior_grade_or_level in varchar2 /* Non-SF52 Data Item */
19 ,p_prior_step_or_rate in varchar2 /* Non-SF52 Data Item */
20 ,p_prior_pay_basis in varchar2 /* Non-SF52 Data Item */
21 ,p_first_noac_lookup_code in varchar2
22 ,p_to_basic_pay in varchar2
23 ,p_to_pay_basis in varchar2
24 ,P_effective_date in date
25 ,p_prior_effective_date in date
26 ) is
27
28 l_table_pay pay_user_column_instances_f.value%type;
29 begin
30 hr_utility.set_location('before call to get basic pay',1);
31 hr_utility.set_location('eff date '|| p_prior_effective_date,1);
32 hr_utility.set_location('grade '|| p_prior_grade_or_level,1);
33 hr_utility.set_location('Step '|| p_prior_step_or_rate,1);
34 l_table_pay := GHR_CPDF_CHECK.get_basic_pay(
35 '0000 Oracle Federal Standard Pay Table (AL, ES, EX, GS, GG) No. 0000',
36 'GS' || '-' || p_prior_grade_or_level,
37 p_prior_step_or_rate,
38 p_prior_effective_date);
39 hr_utility.set_location('after call to get basic pay',1);
40
41 -- 570.02.2
42 -- Venkat 06/08/03
43 -- This edit bypassed when the 866 action and Temporary Promotion Step is not null
44 --
45 -- Madhuri 19-MAY-04 From the Start Including the Pay Plan VP in Prior Pay Plan list
46 --
47 IF GHR_GHRWS52L.g_temp_step is NULL
48 OR (GHR_GHRWS52L.g_temp_step is NOT NULL
49 AND p_first_noac_lookup_code <> '866' ) THEN
50 if (
51 p_prior_pay_plan in ('GS','VP') and (p_prior_pay_rate_det_code = '0' or
52 p_prior_pay_rate_det_code= '7') and
53 (p_prior_grade_or_level between '01' and '15') and
54 (p_prior_step_or_rate between '01' and '10')
55 ) then
56 if not (
57 (l_table_pay = to_number(p_prior_basic_pay)) or
58 (p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0)
59 ) then
60 GHR_GHRWS52L.CPDF_Parameter_Check;
61 hr_utility.set_message(8301,'GHR_37746_ALL_PROCEDURE_FAIL');
62 hr_utility.raise_error;
63 end if;
64 end if;
65 END IF;
66
67 -- 570.23.2
68 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
69 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
70 if (p_prior_pay_plan = 'FG' and
71 (
72 p_prior_pay_rate_det_code = '0' or
73 p_prior_pay_rate_det_code = '7'
74 ) and
75 (p_prior_grade_or_level between '01' and '15') and
76 (p_prior_step_or_rate between '01' and '10')
77
78 ) then
79 if not (l_table_pay = to_number(p_prior_basic_pay) or
80 (p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0)) then
81 GHR_GHRWS52L.CPDF_Parameter_Check;
82 hr_utility.set_message(8301,'GHR_37708_ALL_PROCEDURE_FAIL');
83 hr_utility.raise_error;
84 end if;
85 end if;
86 end if;
87
88 -- 570.28.2
89 -- 12/8/00 vravikan From the Start Add 871 to 'other than' (not equal to ) list
90 -- 08/19/02 vravikan From the Start Added one more and condition
91 -- prior_pay_basis = to_pay_basis
92 -- 30/10/03 Ashley From the Start Added nature of action 849 to the list
93 -- 01/30/04 Venkat From the Start Excluded SES Pay Plans
94 -- upd50 06-Feb-07 Raju From 01-Oct-2006 Bug#5745356 delete NOA 849
95 -- upd51 06-Feb-07 Raju From 01-Jan-2007 Bug#5745356 delete 815-817, 825
96 -- 840-848,878-879. Add 890
97 -- upd53 20-Apr-07 Raju From 01-Jan-2007 Bug#5996938 added 815-817, 825
98 -- 827,840-849,878-879.
99
100 if p_effective_date < fnd_date.canonical_to_date('2006/10/01') then
101 if (
102 (substr(p_first_noac_lookup_code,1,1) in ('7','8') and
103 p_first_noac_lookup_code not in
104 ( '702','703','713','721','740','741','815','816','817','825','840','841',
105 '842','843','844','845','846','847','848','849','855','866','867','868',
106 '871','878','879','891','892','893','894','897' )
107 ) and
108 p_prior_pay_plan not in ('ES','EP','IE','FE') and
109 (
110 ((p_prior_basic_pay is not null and to_number(p_prior_basic_pay) <> 0) and
111 (p_to_basic_pay is not null and to_number(p_to_basic_pay) <> 0) and
112 (p_prior_pay_basis = p_to_pay_basis) ) and
113 to_number(p_prior_basic_pay) <> to_number(p_to_basic_pay))
114 ) then
115 GHR_GHRWS52L.CPDF_Parameter_Check;
116 hr_utility.set_message(8301,'GHR_37713_ALL_PROCEDURE_FAIL');
117 hr_utility.set_message_token('NOA_CODE','702, 703, 713, 721, 740, 741, 815-817, 825, 840-849, 855, 866-868, 871,878-879, 891-894');
118 hr_utility.raise_error;
119 end if;
120 ELSif p_effective_date < fnd_date.canonical_to_date('2007/01/01') then
121 if (
122 (substr(p_first_noac_lookup_code,1,1) in ('7','8') and
123 p_first_noac_lookup_code not in
124 ( '702','703','713','721','740','741','815','816','817','825','840','841',
125 '842','843','844','845','846','847','848','855','866','867','868',
126 '871','878','879','891','892','893','894' )
127 ) and
128 p_prior_pay_plan not in ('ES','EP','IE','FE') and
129 (
130 ((p_prior_basic_pay is not null and to_number(p_prior_basic_pay) <> 0) and
131 (p_to_basic_pay is not null and to_number(p_to_basic_pay) <> 0) and
132 (p_prior_pay_basis = p_to_pay_basis) ) and
133 to_number(p_prior_basic_pay) <> to_number(p_to_basic_pay))
134 ) then
135 GHR_GHRWS52L.CPDF_Parameter_Check;
136 hr_utility.set_message(8301,'GHR_37713_ALL_PROCEDURE_FAIL');
137 hr_utility.set_message_token('NOA_CODE','702, 703, 713, 721, 740, 741, 815-817, 825, 840-848, 855, 866-868, 871,878-879, 891-894');
138 hr_utility.raise_error;
139 end if;
140 ELSE
141 if (
142 (substr(p_first_noac_lookup_code,1,1) in ('7','8') and
146 '871','878','879','890','891','892','893','894','896','897' )
143 p_first_noac_lookup_code not in
144 ( '702','703','713','721','740','741','815','816','817','825','827','840','841',
145 '842','843','844','845','846','847','848','849','855','866','867','868',
147 ) and
148 p_prior_pay_plan not in ('ES','EP','IE','FE') and
149 (
150 ((p_prior_basic_pay is not null and to_number(p_prior_basic_pay) <> 0) and
151 (p_to_basic_pay is not null and to_number(p_to_basic_pay) <> 0) and
152 (p_prior_pay_basis = p_to_pay_basis) ) and
153 to_number(p_prior_basic_pay) <> to_number(p_to_basic_pay))
154 ) then
155 GHR_GHRWS52L.CPDF_Parameter_Check;
156 hr_utility.set_message(8301,'GHR_37625_ALL_PROCEDURE_FAIL');
157 hr_utility.raise_error;
158 end if;
159 END IF;
160
161 -- 570.50.2
162 -- Commented the edit in Dec'00 Patch.
163 -- Uncommented and modified the edit
164 -- Utokachi Modified on 24-oct-2005 Validate for actions after 01-MAY-2005
165
166 IF p_effective_date >= to_date('2005/05/01','YYYY/MM/DD') THEN
167 if ( p_prior_pay_plan = 'GS' and
168 p_prior_pay_rate_det_code in ('5','6')
169 ) then
170 if ((to_number(p_prior_basic_pay) < to_number(l_table_pay)) and
171 (p_prior_basic_pay is not null and to_number(p_prior_basic_pay) <> 0)
172 ) then
173 GHR_GHRWS52L.CPDF_Parameter_Check;
174 hr_utility.set_message(8301,'GHR_37719_ALL_PROCEDURE_FAIL');
175 hr_utility.raise_error;
176 end if;
177 end if;
178 END IF;
179
180 -- 570.80.2
181 if (
182 p_prior_pay_plan = 'GG' and
183 (
184 p_prior_grade_or_level between '01' and '15' and
185 p_prior_step_or_rate between '01' and '10' and
186 p_prior_pay_rate_det_code in ('0','7')
187 )
188 ) then
189 l_table_pay := GHR_CPDF_CHECK.get_basic_pay(
190 '0000 Oracle Federal Standard Pay Table (AL, ES, EX, GS, GG) No. 0000',
191 'GG' || '-' || p_prior_grade_or_level,
192 p_prior_step_or_rate,
193 p_prior_effective_date);
194 if not (
195 p_prior_basic_pay = l_table_pay or (p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0)
196 ) then
197 GHR_GHRWS52L.CPDF_Parameter_Check;
198 hr_utility.set_message(8301,'GHR_37726_ALL_PROCEDURE_FAIL');
199 hr_utility.raise_error;
200 end if;
201 end if;
202
203 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
204 570.03.2 If prior pay plan is NH,and prior pay rate determinant is
205 0,5,6 or 7, Then prior basic pay must be within the range
206 for the prior grade on Table 37 or Table 38
207 (depending on prior pay rate determinant) or be asterisks.
208 Default: Insert asterisks in pay basis and basic pay.
209
210 if p_effective_date > fnd_date.canonical_to_date('1998/03/01') and
211 p_prior_pay_plan = 'NH' and
212 p_prior_pay_rate_det_code in ('0','5','6','7') then -- added for bug 726125
213 if p_prior_pay_rate_det_code in ('0','7') then
214 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 37',
215 p_prior_grade_or_level ,
216 'Minimum Basic Pay',p_effective_date);
217 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 37',
218 p_prior_grade_or_level ,
219 'Maximum Basic Pay',p_effective_date);
220 elsif p_prior_pay_rate_det_code in ('5','6') then
221 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 38',
222 p_prior_grade_or_level ,
223 'Minimum Basic Pay',p_effective_date);
224 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 38',
225 p_prior_grade_or_level ,
226 'Maximum Basic Pay',p_effective_date);
227 end if;
228 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
229 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay )
230 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
231 then
232 GHR_GHRWS52L.CPDF_Parameter_Check;
233 hr_utility.set_message(8301,'GHR_37865_ALL_PROCEDURE_FAIL');
234 hr_utility.raise_error;
235 end if;
236 end if;
237 */
238
239 /* 570.04.2 If prior pay plan is GS,
240 And prior pay rate determinant is 2, 3, 4, J, K, or R,
241 Then prior basic pay must be equal to or exceed the
242 minimum basic pay for prior grade on Table 2 or be
243 asterisks.
244
245 Default: Insert asterisks in prior pay basis and prior
246 basic pay.*/
247 --upd47 26-Jun-06 Raju From 01-Apr-2003 Terminate the edit
248 if p_effective_date < fnd_date.canonical_to_date('2003/04/01') then
249 if p_prior_pay_plan = 'GS' and
250 p_prior_pay_rate_det_code in ('2','3','4','J','K','R') then
251
252 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 2',
253 p_prior_grade_or_level || '-' || p_prior_step_or_rate,
254 'Minimum Basic Pay',
255 p_prior_effective_date);
256 if min_basic_pay IS NOT NULL and
257 (not(to_number(p_prior_basic_pay) >= min_basic_pay)
258 or p_prior_basic_pay is null)
259 then
260 GHR_GHRWS52L.CPDF_Parameter_Check;
261 hr_utility.set_message(8301,'GHR_37701_ALL_PROCEDURE_FAIL');
262 hr_utility.raise_error;
263 end if;
264 end if;
265 end if;
266
267 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
268 570.05.2 If prior pay plan is NJ,and prior pay rate determinant is
269 0,5,6 or 7, Then prior basic pay must be within the range
270 for the prior grade on Table 39 or Table 40
271 (depending on prior pay rate determinant) or be asterisks.
272 Default: Insert asterisks in pay basis and basic pay.
273
274 if p_effective_date > fnd_date.canonical_to_date('1998/03/01') and
275 p_prior_pay_plan = 'NJ' and
276 p_prior_pay_rate_det_code in ('0','5','6','7') then -- added for bug 726125
277 if p_prior_pay_rate_det_code in ('0','7') then
278 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 39',
279 p_prior_grade_or_level ,
280 'Minimum Basic Pay',p_effective_date);
281 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 39',
282 p_prior_grade_or_level ,
283 'Maximum Basic Pay',p_effective_date);
284 elsif p_prior_pay_rate_det_code in ('5','6') then
285 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 40',
286 p_prior_grade_or_level ,
287 'Minimum Basic Pay',p_effective_date);
288 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 40',
289 p_prior_grade_or_level ,
290 'Maximum Basic Pay',p_effective_date);
291 end if;
292 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
293 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay )
294 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
295 then
296 GHR_GHRWS52L.CPDF_Parameter_Check;
297 hr_utility.set_message(8301,'GHR_37866_ALL_PROCEDURE_FAIL');
298 hr_utility.raise_error;
299 end if;
300 end if;
301 */
302
303 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
304 570.06.2 If prior pay plan is NK,and prior pay rate determinant is
305 0,5,6 or 7, Then prior basic pay must be within the range
306 for the prior grade on Table 41 or Table 42
307 (depending on pay rate determinant) or be asterisks.
308 Default: Insert asterisks in pay basis and basic pay.
309
310 if p_effective_date > fnd_date.canonical_to_date('1998/03/01') and
311 p_prior_pay_plan = 'NK' and
312 p_prior_pay_rate_det_code in ('0','5','6','7') then -- added for bug 726125
313 if p_prior_pay_rate_det_code in ('0','7') then
314 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 41',
315 p_prior_grade_or_level ,
316 'Minimum Basic Pay',p_effective_date);
320 elsif p_prior_pay_rate_det_code in ('5','6') then
317 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 41',
318 p_prior_grade_or_level ,
319 'Maximum Basic Pay',p_effective_date);
321 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 42',
322 p_prior_grade_or_level ,
323 'Minimum Basic Pay',p_effective_date);
324 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 42',
325 p_prior_grade_or_level ,
326 'Maximum Basic Pay',p_effective_date);
327 end if;
328 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
329 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay )
330 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
331 then
332 GHR_GHRWS52L.CPDF_Parameter_Check;
333 hr_utility.set_message(8301,'GHR_37867_ALL_PROCEDURE_FAIL');
334 hr_utility.raise_error;
335 end if;
336 end if;
337 */
338
339 /*570.07.2 If prior pay plan is GM,
340 And prior pay rate determinant is 0 or 7,
341 Then prior basic pay must fall within the appropriate
342 range for prior grade and step or rate on Table 3 or be
343 asterisks.
344
345 Default: Insert asterisks in prior pay basis and prior
346 basic pay. */
347 -- 17-Aug-00 vravikan 1-Jan-2000 Modify Edit to remove step or rate
348 if p_effective_date >= to_date('2000/01/01','yyyy/mm/dd') then
349 if p_prior_pay_plan = 'GM' and
350 p_prior_pay_rate_det_code in ('0','7') then
351
352 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
353 p_prior_grade_or_level ,
354 'Minimum Basic Pay',
355 p_prior_effective_date);
356
357 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
358 p_prior_grade_or_level ,
359 'Maximum Basic Pay',
360 p_prior_effective_date);
361 if min_basic_pay IS NOT NULL and
362 max_basic_pay IS NOT NULL and
363 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay)
364 or p_prior_basic_pay is null)
365 then
366 GHR_GHRWS52L.CPDF_Parameter_Check;
367 hr_utility.set_message(8301,'GHR_37702_ALL_PROCEDURE_FAIL');
368 hr_utility.raise_error;
369 end if;
370 end if;
371 else
372 if p_prior_pay_plan = 'GM' and
373 p_prior_pay_rate_det_code in ('0','7') then
374
375 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
376 p_prior_grade_or_level || '-' || p_prior_step_or_rate,
377 'Minimum Basic Pay',
378 p_prior_effective_date);
379
380 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
381 p_prior_grade_or_level || '-' || p_prior_step_or_rate,
382 'Maximum Basic Pay',
383 p_prior_effective_date);
384 if min_basic_pay IS NOT NULL and
385 max_basic_pay IS NOT NULL and
386 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay)
387 or p_prior_basic_pay is null)
388 then
389 GHR_GHRWS52L.CPDF_Parameter_Check;
390 hr_utility.set_message(8301,'GHR_37702_ALL_PROCEDURE_FAIL');
391 hr_utility.raise_error;
392 end if;
393 end if;
394 end if;
395
396 -- 12/8/00 vravikan From Start Change Edit
397 -- Table 4 to Table 2
398
399 /*570.10.2 If prior pay plan is GM or GH,
400 And prior pay rate determinant is 2, 3, 4, J, K, or R,
401 Then prior basic pay must equal or exceed the minimum
402 basic pay for prior grade on Table 4 or be asterisks.
403
404 Default: Insert asterisks in prior pay basis and prior
405 basic pay.*/
406 --upd47 26-Jun-06 Raju From 01-Apr-2006 Added pay plan GS
407
408 if p_effective_date < fnd_date.canonical_to_date('2006/04/01') then
409 if p_prior_pay_plan in ('GH', 'GM') and
410 p_prior_pay_rate_det_code in ('2','3','4','J','K','R') then
411 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 2',
412 p_prior_grade_or_level || '-' || p_prior_step_or_rate,
413 'Minimum Basic Pay',
414 p_prior_effective_date);
415 if min_basic_pay IS NOT NULL and
416 (not(to_number(p_prior_basic_pay) >= min_basic_pay)
417 or p_prior_basic_pay is null)
418 then
419 GHR_GHRWS52L.CPDF_Parameter_Check;
420 hr_utility.set_message(8301,'GHR_37703_ALL_PROCEDURE_FAIL');
421 hr_utility.set_message_token('PAY_PLAN','GM or GH');
422 hr_utility.raise_error;
423 end if;
424 end if;
425 else
426 if p_prior_pay_plan in ('GH', 'GM','GS') and
427 p_prior_pay_rate_det_code in ('2','3','4','J','K','R') then
428 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 2',
429 p_prior_grade_or_level || '-' || p_prior_step_or_rate,
430 'Minimum Basic Pay',
431 p_prior_effective_date);
432 if min_basic_pay IS NOT NULL and
433 (not(to_number(p_prior_basic_pay) >= min_basic_pay)
434 or p_prior_basic_pay is null)
435 then
436 GHR_GHRWS52L.CPDF_Parameter_Check;
437 hr_utility.set_message(8301,'GHR_37703_ALL_PROCEDURE_FAIL');
438 hr_utility.set_message_token('PAY_PLAN','GH, GM, or GS');
439 hr_utility.raise_error;
440 end if;
441 end if;
442 end if;
446 Then prior basic pay must match the entry for prior
443
444 /*570.13.2 If prior pay plan is EX,
445 And prior pay rate determinant is 0,
447 grade on Table 5 or be asterisks.
448
449 Default: Insert asterisks in prior pay basis and prior
450 basic pay.*/
451 if p_prior_pay_plan = 'EX' and
452 p_prior_pay_rate_det_code = '0' then
453
454 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 5',
455 p_prior_grade_or_level,
456 'Maximum Basic Pay',
457 p_prior_effective_date);
458 if max_basic_pay IS NOT NULL and
459 (to_number(p_prior_basic_pay) <> max_basic_pay
460 or p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0)
461 then
462 GHR_GHRWS52L.CPDF_Parameter_Check;
463 hr_utility.set_message(8301,'GHR_37704_ALL_PROCEDURE_FAIL');
464 hr_utility.raise_error;
465 end if;
466 end if;
467
468 /*570.16.2 If prior pay plan is ES or FE,
469 And prior pay rate determinant is not C,
470 Then prior basic pay must match the entry for prior
471 step or rate on Table 6 or be asterisks.
472
473 Default: Insert asterisks in prior pay basis and prior
474 basic pay.*/
475 -- NAME EFFECTIVE COMMENTS
476 -- Madhuri 21-JAN-2004 End Dating this edit as on 10-JAN-04
477 -- For SES Pay Calculations
478 --
479 -- end dating the edit as on 10-JAN-2004
480 IF p_effective_date < to_date('2004/01/11', 'yyyy/mm/dd') then
481
482 if (p_prior_pay_plan in ('ES', 'FE') and
483 p_prior_pay_rate_det_code <> 'C' ) then
484
485 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 6',
486 p_prior_step_or_rate,
487 'Maximum Basic Pay',
488 p_prior_effective_date);
489 if max_basic_pay IS NOT NULL and
490 (p_prior_basic_pay <> max_basic_pay
491 or p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0)
492 then
493 GHR_GHRWS52L.CPDF_Parameter_Check;
494 hr_utility.set_message(8301,'GHR_37705_ALL_PROCEDURE_FAIL');
495 hr_utility.raise_error;
496 end if;
497 end if;
498 END IF; -- end dating the edit as on 10-JAN-2004
499
500 -- NEW EDIT as on 19-MAY-2004
501 /* 570.17.2: If prior pay plan is ES or FE,
502 and prior pay rate determinant is not C,
503 Then prior basic pay must be within the range on Table 55 */
504
505 -- Name Effective Date Comments
506 -- Madhuri 19-MAY-2004 Added this Edit
507 --
508 IF ( p_effective_date >= to_date('2004/01/11', 'yyyy/mm/dd') )
509 and
510 ( p_prior_pay_plan in ('ES', 'FE') and p_prior_pay_rate_det_code <> 'C' )
511 THEN
512 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 55',
513 p_prior_pay_plan,
514 'Minimum Basic Pay',
515 p_effective_date);
516 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 55',
517 p_prior_pay_plan,
518 'Maximum Basic Pay',
519 p_effective_date);
520
521 if not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay )
522 then
523 GHR_GHRWS52L.CPDF_Parameter_Check;
524 hr_utility.set_message(8301,'GHR_38885_ALL_PROCEDURE_FAIL');
525 -- NEED TO ADD A NEW MESSAGE HERE
526 hr_utility.raise_error;
527 end if;
528 END IF;
529
530 /*570.19.2 If prior pay plan is W-, XE, XF, XG, or XH,
531 And prior pay rate determinant is 0,
532 Then prior basic pay must fall within the range for
533 prior pay plan on Table 11 or be asterisks.
534
535 Default: Insert asterisks in prior pay basis and prior
536 basic pay.*/
537
538 -- Update Date Updated By Effective Date Comments
539 -----------------------------------------------------------------------------------------------------------
540 -- 18/10/2004 Madhuri From the start of the edit Deleting the edit
541 -----------------------------------------------------------------------------------------------------------
542 /*
543 if substr(p_prior_pay_plan,1,1) = 'W' or p_prior_pay_plan in ('XE', 'XF', 'XG', 'XH') then
544 min_basic_pay := NULL;
545 max_basic_pay := NULL;
546 if substr(p_prior_pay_plan,1,1) = 'W' then
547 if p_prior_pay_plan not in ('WG','WL','WM','WS') then
548 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
549 p_prior_pay_plan,
550 'Maximum Basic Pay', p_prior_effective_date);
551 else
552 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
553 p_prior_pay_plan||'-'||p_prior_grade_or_level,
554 'Minimum Basic Pay', p_prior_effective_date);
555 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
556 p_prior_pay_plan||'-'||p_prior_grade_or_level,
557 'Maximum Basic Pay', p_prior_effective_date);
558 end if;
559 else
560 if p_prior_pay_plan = 'XE' then
561 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
562 p_prior_pay_plan,
563 'Minimum Basic Pay', p_prior_effective_date);
564 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
565 p_prior_pay_plan,
566 'Maximum Basic Pay', p_prior_effective_date);
567 elsif p_prior_pay_plan in ('XF', 'XG', 'XH') then
568 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
569 p_prior_pay_plan||'-'||p_prior_grade_or_level,
570 'Minimum Basic Pay', p_prior_effective_date);
571 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
572 p_prior_pay_plan||'-'||p_prior_grade_or_level,
573 'Maximum Basic Pay', p_prior_effective_date);
574 end if;
575 end if;
576 if max_basic_pay IS NOT NULL then
577 if p_prior_pay_rate_det_code = '0'
578 and (not(to_number(p_prior_basic_pay) between nvl(min_basic_pay,0) and nvl(max_basic_pay,0))
579 or p_prior_basic_pay is null)
580 then
581 GHR_GHRWS52L.CPDF_Parameter_Check;
582 hr_utility.set_message(8301,'GHR_37706_ALL_PROCEDURE_FAIL');
583 hr_utility.raise_error;
584 end if;
585 end if;
586 end if; */
587 -------------------------------------------------------------------------------------------------------
588
589 /*
590 Edit #570.21.2:If prior pay plan is GL
591 and prior pay rate determinant is 0 or 7.
592 Then prior basic pay must match the entry for
593 the prior grade and prior step on Table 57 or be asterisks.
594 */
595 -- 570.21.2
596 IF p_effective_date >= fnd_date.canonical_to_date('2007/08/13') THEN
597 IF (
598 p_prior_pay_plan = 'GL' and p_prior_pay_rate_det_code in ('0','7')
599 ) THEN
600 l_table_pay := GHR_CPDF_CHECK.get_basic_pay(
601 '0491 Oracle Federal Special Rate Pay Table (GS) No. 0491',
602 'GL' || '-' || p_prior_grade_or_level,
603 p_prior_step_or_rate,
604 p_prior_effective_date);
605 if not (
606 p_prior_basic_pay = l_table_pay or (p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0)
607 ) then
608 GHR_GHRWS52L.CPDF_Parameter_Check;
609 hr_utility.set_message(8301,'GHR_37750_ALL_PROCEDURE_FAIL');
610 hr_utility.raise_error;
611 end if;
612 END IF;
613
614 END IF;
615
616 /*570.22.2 If prior pay plan is KA,
617 Then prior basic pay must fall within the range on
618 Table 17 or be asterisks.
619
620 Default: Insert asterisks in prior pay basis and prior
621 basic pay. */
622 -------------------------------------------------------------------------------
623 -- Modified by Date Comments
624 -------------------------------------------------------------------------------
625 -- Madhuri 01-MAR-05 Retroactively end dating as of 31-JAN-2002
626 -------------------------------------------------------------------------------
627 IF p_prior_effective_date <= fnd_date.canonical_to_date('20'||'02/01/31') THEN
628 if p_prior_pay_plan = 'KA' then
629 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 17',
630 p_prior_pay_plan ,
631 'Minimum Basic Pay',
632 p_prior_effective_date);
633 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 17',
634 p_prior_pay_plan ,
635 'Maximum Basic Pay',
636 p_prior_effective_date);
637 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL then
638 if (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay) or
639 p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0) then
640 GHR_GHRWS52L.CPDF_Parameter_Check;
641 hr_utility.set_message(8301,'GHR_37707_ALL_PROCEDURE_FAIL');
642 hr_utility.raise_error;
643 end if;
644 end if;
645 end if;
646 END IF;
647 -------------------------------------------------------------------------------
648
649 /*570.24.2 If prior pay plan is FB, FJ, or FX,
650 Then prior basic pay must be no less than step 1 on
651 Table 6 and no more than step 6 on Table 6, or be
652 asterisks.
653 Default: Insert asterisks in prior pay basis and prior
654 basic pay.*/
655 --
656 -- Madhuri 19-MAY-2004 End dated the Edit as of 10-JAN-04
657 --
658 IF p_effective_date < to_date('2004/01/11', 'yyyy/mm/dd') then
659 if p_prior_pay_plan in ('FB', 'FJ', 'FX') then
660 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 6',
661 '01',
662 'Maximum Basic Pay',
663 p_prior_effective_date);
664 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 6',
665 '06',
666 'Maximum Basic Pay',
667 p_prior_effective_date);
668
669 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL then
670 if (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay) or
671 p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0)
672 then
673 GHR_GHRWS52L.CPDF_Parameter_Check;
674 hr_utility.set_message(8301,'GHR_37709_ALL_PROCEDURE_FAIL');
675 hr_utility.raise_error;
676 end if;
677 end if;
678 end if;
679 END IF;
680
681 /*570.25.2 If prior pay plan is FT,
682 Then prior basic pay must be within the range on Table
683 21 or be asterisks.
684
685 Default: Insert asterisks in prior pay basis and prior
686 basic pay.*/
687 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
688 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
689 if p_prior_pay_plan = 'FT' then
690 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
691 p_prior_pay_plan ,
692 'Minimum Basic Pay',
693 p_prior_effective_date);
694 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
695 p_prior_pay_plan ,
696 'Maximum Basic Pay',
697 p_prior_effective_date);
698 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL then
699 if (not (to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay )
700 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
701 then
702 GHR_GHRWS52L.CPDF_Parameter_Check;
703 hr_utility.set_message(8301,'GHR_37710_ALL_PROCEDURE_FAIL');
704 hr_utility.raise_error;
705 end if;
706 end if;
707 end if;
708 end if;
709
710 /*570.26.2 If prior pay plan is FL, FS, or FW,
711 Then prior basic pay must be no less than the minimum
712 for pay plan WG on Table 11 and no more than the
713 maximum for pay plan WS on Table 11, or be asterisks.
714
715 Default: Insert asterisks in prior pay basis and prior
716 basic pay.*/
717 -- Update Date Updated By Effective Date Comments
718 -----------------------------------------------------------------------------------------------------------
719 -- 18/10/2004 Madhuri From the start of the edit Deleting the edit
720 -----------------------------------------------------------------------------------------------------------
721
722 /*if p_prior_pay_plan in ('FL', 'FS', 'FW') then
723 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
724 'WG'||'-'||p_prior_grade_or_level,
725 'Minimum Basic Pay',p_prior_effective_date);
726 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
727 'WS'||'-'||p_prior_grade_or_level,
728 'Maximum Basic Pay',p_prior_effective_date);
729
730 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL then
731 if (not (to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay )
732 or p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0)
733 then
734 GHR_GHRWS52L.CPDF_Parameter_Check;
735 hr_utility.set_message(8301,'GHR_37711_ALL_PROCEDURE_FAIL');
736 hr_utility.raise_error;
737 end if;
738 end if;
739 end if; */
740 -----------------------------------------------------------------------------------------------------------
741
742 /*570.27.2 If prior pay plan is FM,
743 And prior pay rate determinant is 0 or 7,
744 Then prior basic pay must match the entry for prior
745 grade and prior step or rate on Table 3 or be
746 asterisks.
747 Default: Insert asterisks in prior pay basis and prior
748 basic pay.*/
749 -- 17-Aug-00 vravikan 1-Jan-2000 Modify Edit to remove prior step or rate
750 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
751 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
752 if p_effective_date >= to_date('2000/01/01','yyyy/mm/dd') then
753 if p_prior_pay_plan = 'FM' and
754 p_prior_pay_rate_det_code in ('0','7') then
755
759 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
756 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
757 p_prior_grade_or_level ,
758 'Minimum Basic Pay',p_prior_effective_date);
760 p_prior_grade_or_level ,
761 'Maximum Basic Pay',p_prior_effective_date);
762 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
763 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay )
764 or p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0)
765 then
766 GHR_GHRWS52L.CPDF_Parameter_Check;
767 hr_utility.set_message(8301,'GHR_37712_ALL_PROCEDURE_FAIL');
768 hr_utility.raise_error;
769 end if;
770 end if;
771 else
772 if p_prior_pay_plan = 'FM' and
773 p_prior_pay_rate_det_code in ('0','7') then
774
775 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
776 p_prior_grade_or_level || '-' || p_prior_step_or_rate,
777 'Minimum Basic Pay',p_prior_effective_date);
778 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
779 p_prior_grade_or_level || '-' || p_prior_step_or_rate,
780 'Maximum Basic Pay',p_prior_effective_date);
781 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
782 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay )
783 or p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0)
784 then
785 GHR_GHRWS52L.CPDF_Parameter_Check;
786 hr_utility.set_message(8301,'GHR_37712_ALL_PROCEDURE_FAIL');
787 hr_utility.raise_error;
788 end if;
789 end if;
790 end if;
791 end if;
792 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
793 570.29.2 If prior pay plan is DR,
794 And prior pay rate determinant is 0 or 7,
795 Then prior basic pay must be within the range for the
796 prior grade and prior pay rate determinant on Table 28
797 or be asterisks.
798
799 Default: Insert asterisks in prior pay basis and prior
800 basic pay.
801 if p_prior_pay_plan = 'DR' and
802 p_prior_pay_rate_det_code in ('0','7') then
803
804 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 28',
805 p_prior_grade_or_level ,
806 'Minimum Basic Pay',p_prior_effective_date);
807 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 28',
808 p_prior_grade_or_level ,
809 'Maximum Basic Pay',p_prior_effective_date);
810
811 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
812 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay )
813 or p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0)
814 then
815 GHR_GHRWS52L.CPDF_Parameter_Check;
816 hr_utility.set_message(8301,'GHR_38405_ALL_PROCEDURE_FAIL');
817 hr_utility.raise_error;
818 end if;
819 end if;
820 */
821
822 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
823 570.30.2 If prior pay plan is DR,
824 And prior pay rate determinant is 5 or 6,
825 Then prior basic pay must be within the range for the
826 prior grade and prior pay rate determinant on Table 29
827 or be asterisks.
828
829 Default: Insert asterisks in prior pay basis and prior
830 basic pay.
831 if p_prior_pay_plan = 'DR' and
832 p_prior_pay_rate_det_code in ('5','6') then
833
834 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 29',
835 p_prior_grade_or_level ,
836 'Minimum Basic Pay',p_prior_effective_date);
837 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 29',
838 p_prior_grade_or_level ,
839 'Maximum Basic Pay',p_prior_effective_date);
840 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
841 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay )
842 or p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0)
843 then
844 GHR_GHRWS52L.CPDF_Parameter_Check;
845 hr_utility.set_message(8301,'GHR_38406_ALL_PROCEDURE_FAIL');
846 hr_utility.raise_error;
847 end if;
848 end if;
849 */
850
851 /*570.31.2 If prior pay plan is NY,
852 Then prior basic pay must be within the range on
853 Table 26 or be asterisks.
854
855 Default: Insert asterisks in prior pay basis and prior
856 basic pay.*/
857 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
858 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
859 if p_prior_pay_plan = 'NY' then
860 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 26',
861 p_prior_grade_or_level ,
862 'Minimum Basic Pay',p_prior_effective_date);
863 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 26',
864 p_prior_grade_or_level ,
865 'Maximum Basic Pay',p_prior_effective_date);
866 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
867 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay )
868 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
869 then
870 GHR_GHRWS52L.CPDF_Parameter_Check;
871 hr_utility.set_message(8301,'GHR_37714_ALL_PROCEDURE_FAIL');
872 hr_utility.raise_error;
873 end if;
874 end if;
878 prior grade on Table 27 or be asterisks.
875 end if;
876 /*570.32.2 If prior pay plan is NX,
877 Then prior basic pay must be within the range for the
879
880 Default: Insert asterisks in prior pay basis and prior
881 basic pay.*/
882 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
883 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
884 if p_prior_pay_plan = 'NX' then
885
886 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 27',
887 p_prior_grade_or_level ,
888 'Minimum Basic Pay',p_prior_effective_date);
889 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 27',
890 p_prior_grade_or_level ,
891 'Maximum Basic Pay',p_prior_effective_date);
892
893 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
894 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay )
895 or p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0)
896 then
897 GHR_GHRWS52L.CPDF_Parameter_Check;
898 hr_utility.set_message(8301,'GHR_37715_ALL_PROCEDURE_FAIL');
899 hr_utility.raise_error;
900 end if;
901 end if;
902 end if;
903
904 /*570.37.2 If prior pay plan is FC,
905 And prior pay rate determinant is not C,
906 Then Prior Basic Pay must be within the range for prior
907 grade on Table 8 or be asterisks.
908
909 Default: Insert asterisks in prior pay basis and prior
910 basic pay. */
911 -- Update Date By Comments
912 -- 20/2 27-Feb-2003 Madhuri Modified the Requirement
913 -- From Then Prior Basic Pay must match the entry for the grade on Table 8 or be asterisks.
914 -- to Then Prior Basic Pay must be within the range for prior grade on Table 8 or be asterisks.
915 --
916 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
917 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
918 if p_prior_pay_plan = 'FC' and
919 p_prior_pay_rate_det_code <> 'C' then
920
921 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 8',
922 p_prior_grade_or_level || '-' || p_prior_step_or_rate,
923 'Minimum Basic Pay',p_prior_effective_date);
924
925 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 8',
926 p_prior_grade_or_level || '-' || p_prior_step_or_rate,
927 'Maximum Basic Pay',p_prior_effective_date);
928
929 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
930 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay )
931 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
932 then
933 GHR_GHRWS52L.CPDF_Parameter_Check;
934 hr_utility.set_message(8301,'GHR_37716_ALL_PROCEDURE_FAIL');
935 hr_utility.raise_error;
936 end if;
937 end if;
938 end if;
939 /*570.43.2 If prior pay plan is AF, FO, or FP,
940 And prior pay rate determinant is not C,
941 Then prior basic pay must fall within the range for
942 prior grade on Table 10 or be asterisks.
943 Default: Insert asterisks in prior pay basis and prior
944 basic pay.*/
945 if p_prior_pay_plan in ('AF','FO','FP') and
946 p_prior_pay_rate_det_code <> 'C' then
947
948 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 10',
949 p_prior_grade_or_level || '-' || p_prior_step_or_rate,
950 'Minimum Basic Pay',p_prior_effective_date);
951 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 10',
952 p_prior_grade_or_level || '-' || p_prior_step_or_rate,
953 'Maximum Basic Pay',p_prior_effective_date);
954 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
955 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay )
956 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
957 then
958 GHR_GHRWS52L.CPDF_Parameter_Check;
959 hr_utility.set_message(8301,'GHR_37717_ALL_PROCEDURE_FAIL');
960 hr_utility.raise_error;
961 end if;
962 end if;
963
964 -- 570.45.2 If prior pay plan is FA,
965 -- And agency/subelement is ST,
966 -- And prior pay rate determinant is 0,
967 -- And prior grade is 13 or 14,
968 -- Then prior basic pay must equal entry for the grade on
969 -- Table 7 or be asterisks.
970 --
971 -- Default: Insert asterisks in prior pay basis and prior basic pay.
972
973 -- Update Date By Comments
974 -- 20/2 27-Feb-2003 Madhuri Modified the Requirement
975 -- from And prior grade is 01 thru 04 or 13 or 14.
976 -- to And prior grade is 13 or 14.
977 -- 19-MAR-2003 NarasimhaRao Changed the parameter from p_prior_grade_or_level
978 -- to p_prior_step_or_rate
979 -- upd50 06-Feb-07 Raju From 01-Sep-2004 Bug#5745356 delete Edit
980
981 if p_effective_date < to_date('2004/09/01','yyyy/mm/dd') then
982 if p_prior_pay_plan = 'FA' and
983 p_agency_subelement = 'ST' and
984 p_prior_pay_rate_det_code = '0' and
985 p_prior_grade_or_level in ('13','14') then
986
987 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 7',
988 p_prior_step_or_rate,
989 'Maximum Basic Pay',
990 p_prior_effective_date);
991
992 if max_basic_pay IS NOT NULL and
996 GHR_GHRWS52L.CPDF_Parameter_Check;
993 (to_number(p_prior_basic_pay) <> max_basic_pay
994 or p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0)
995 then
997 hr_utility.set_message(8301,'GHR_37718_ALL_PROCEDURE_FAIL');
998 hr_utility.raise_error;
999 end if;
1000 end if;
1001 end if;
1002
1003 /*570.53.2 If prior pay plan is GS,
1004 And prior pay rate determinant is 5, 6, or M,
1005 Then prior basic pay must be equal to or less than the
1006 entry for prior grade on Table 19 or be asterisks.
1007
1008 Default: Insert asterisks in prior pay basis and prior
1009 basic pay.*/
1010 /* Commenting the edit as per the Bug 3147737.
1011 if p_prior_pay_plan = 'GS' and
1012 p_prior_pay_rate_det_code in ('5','6','M') then
1013
1014 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 19',
1015 p_prior_grade_or_level,
1016 'Maximum Basic Pay',
1017 p_prior_effective_date);
1018
1019 if max_basic_pay IS NOT NULL and
1020 (not(to_number(p_prior_basic_pay) <= max_basic_pay)
1021 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
1022 then
1023 GHR_GHRWS52L.CPDF_Parameter_Check;
1024 hr_utility.set_message(8301,'GHR_37720_ALL_PROCEDURE_FAIL');
1025 hr_utility.raise_error;
1026 end if;
1027 end if;
1028 */
1029
1030 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1031 --570.38.2
1032 -- Update Date By Effective Date Comment
1033 -- 9/5 08/12/99 vravikan 01-Apr-99 New Edit
1034 If prior pay plan is NC, And prior pay rate determinant is 0, 5, 6, or 7,
1035 Then prior basic pay must be within the range for the prior grade on Table 44
1036 or Table 45 (depending on prior pay rate determinant). If prior pay rate determinant
1037 is 0 or 7 then compare to table 44. If prior pay rate determinant is 5 or 6
1038 then compare to table 45.
1039
1040
1041 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') then
1042 if p_prior_pay_plan = 'NC' then
1043 if p_prior_pay_rate_det_code in ('0','7')
1044 then
1045
1046 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 44',
1047 p_prior_grade_or_level,
1048 'Minimum Basic Pay',p_prior_effective_date);
1049 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 44',
1050 p_prior_grade_or_level,
1051 'Maximum Basic Pay',p_prior_effective_date);
1052 elsif p_prior_pay_rate_det_code in ('5','6') then
1053 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 45',
1054 p_prior_grade_or_level,
1055 'Minimum Basic Pay',p_prior_effective_date);
1056 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 45',
1057 p_prior_grade_or_level,
1058 'Maximum Basic Pay',p_prior_effective_date);
1059
1060 end if;
1061 end if;
1062 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1063 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay)
1064 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
1065 then
1066 GHR_GHRWS52L.CPDF_Parameter_Check;
1067 hr_utility.set_message(8301,'GHR_37074_ALL_PROCEDURE_FAIL');
1068 hr_utility.raise_error;
1069 end if;
1070 end if;
1071 */
1072
1073 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1074 --570.39.2
1075 -- Update Date By Effective Date Comment
1076 -- 9/5 08/12/99 vravikan 01-Apr-99 New Edit
1077
1078 If prior pay plan is NO, And prior pay rate determinant is 0, 5, 6, or 7,
1079 Then prior basic pay must be within the range for the prior grade on
1080 Table 46 or Table 47 (depending on prior pay rate determinant).
1081 If prior pay rate determinant is 0 or 7 then compare to table 46.
1082 If prior pay rate determinant is 5 or 6 then compare to table 47.
1083
1084 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') then
1085 if p_prior_pay_plan = 'NO' then
1086 if p_prior_pay_rate_det_code in ('0','7')
1087 then
1088
1089 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 46',
1090 p_prior_grade_or_level,
1091 'Minimum Basic Pay',p_prior_effective_date);
1092 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 46',
1093 p_prior_grade_or_level,
1094 'Maximum Basic Pay',p_prior_effective_date);
1095 elsif p_prior_pay_rate_det_code in ('5','6') then
1096 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 47',
1097 p_prior_grade_or_level,
1098 'Minimum Basic Pay',p_prior_effective_date);
1099 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 47',
1100 p_prior_grade_or_level,
1101 'Maximum Basic Pay',p_prior_effective_date);
1102
1103 end if;
1104 end if;
1105 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1106 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay)
1107 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
1108 then
1109 GHR_GHRWS52L.CPDF_Parameter_Check;
1110 hr_utility.set_message(8301,'GHR_37075_ALL_PROCEDURE_FAIL');
1111 hr_utility.raise_error;
1112 end if;
1113 end if;
1114 */
1115 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1116 --570.40.2
1117 -- Update Date By Effective Date Comment
1118 -- 9/5 08/12/99 vravikan 01-Apr-99 New Edit
1122 or Table 49 (depending on prior pay rate determinant).
1119
1120 If prior pay plan is NP, And prior pay rate determinant is 0, 5, 6, or 7,
1121 Then prior basic pay must be within the range for the prior grade on Table 48
1123 If prior pay rate determinant is 0 or 7 then compare to table 48.
1124 If prior pay rate determinant is 5 or 6 then compare to table 49.
1125
1126 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') then
1127 if p_prior_pay_plan = 'NP' then
1128 if p_prior_pay_rate_det_code in ('0','7')
1129 then
1130
1131 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 48',
1132 p_prior_grade_or_level,
1133 'Minimum Basic Pay',p_prior_effective_date);
1134 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 48',
1135 p_prior_grade_or_level,
1136 'Maximum Basic Pay',p_prior_effective_date);
1137 elsif p_prior_pay_rate_det_code in ('5','6') then
1138 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 49',
1139 p_prior_grade_or_level,
1140 'Minimum Basic Pay',p_prior_effective_date);
1141 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 49',
1142 p_prior_grade_or_level,
1143 'Maximum Basic Pay',p_prior_effective_date);
1144
1145 end if;
1146 end if;
1147
1148 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1149 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay)
1150 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
1151 then
1152 GHR_GHRWS52L.CPDF_Parameter_Check;
1153 hr_utility.set_message(8301,'GHR_37076_ALL_PROCEDURE_FAIL');
1154 hr_utility.raise_error;
1155 end if;
1156 end if;
1157 */
1158 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1159 --570.41.2
1160 -- Update Date By Effective Date Comment
1161 -- 9/5 08/12/99 vravikan 01-Apr-99 New Edit
1162
1163 If prior pay plan is NR, And prior pay rate determinant is 0, 5, 6, or 7,
1164 Then prior basic pay must be within the range for the prior grade on
1165 Table 50 or Table 51 (depending on prior pay rate determinant).
1166 If prior pay rate determinant is 0 or 7 then compare to table 50.
1167 If prior pay rate determinant is 5 or 6 then compare to table 51.
1168
1169
1170 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') then
1171 if p_prior_pay_plan = 'NR' then
1172 if p_prior_pay_rate_det_code in ('0','7')
1173 then
1174
1175 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 50',
1176 p_prior_grade_or_level,
1177 'Minimum Basic Pay',p_prior_effective_date);
1178 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 50',
1179 p_prior_grade_or_level,
1180 'Maximum Basic Pay',p_prior_effective_date);
1181 elsif p_prior_pay_rate_det_code in ('5','6') then
1182 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 51',
1183 p_prior_grade_or_level,
1184 'Minimum Basic Pay',p_prior_effective_date);
1185 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 51',
1186 p_prior_grade_or_level,
1187 'Maximum Basic Pay',p_prior_effective_date);
1188
1189 end if;
1190 end if;
1191
1192 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1193 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay)
1194 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
1195 then
1196 GHR_GHRWS52L.CPDF_Parameter_Check;
1197 hr_utility.set_message(8301,'GHR_37077_ALL_PROCEDURE_FAIL');
1198 hr_utility.raise_error;
1199 end if;
1200 end if;
1201 */
1202 /*570.56.2 If prior pay plan is GM,
1203 And prior pay rate determinant is 5, 6, or M,
1204 Then prior basic pay must fall within the range for the
1205 prior grade on Table 20 or be asterisks.
1206
1207 Default: Insert asterisks in prior pay basis and prior
1208 basic pay.*/
1209 /* Commented as per the bug 3147737
1210
1211 if p_prior_pay_plan = 'GM' and
1212 p_prior_pay_rate_det_code in ('5', '6', 'M') then
1213
1214 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 20',
1215 p_prior_grade_or_level,
1216 'Minimum Basic Pay',p_prior_effective_date);
1217 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 20',
1218 p_prior_grade_or_level,
1219 'Maximum Basic Pay',p_prior_effective_date);
1220
1221 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1222 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay)
1223 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
1224 then
1225 GHR_GHRWS52L.CPDF_Parameter_Check;
1226 hr_utility.set_message(8301,'GHR_37721_ALL_PROCEDURE_FAIL');
1227 hr_utility.raise_error;
1228 end if;
1229 end if;
1230 */
1231 /*570.60.2 If prior pay plan is AL,
1232 And prior pay rate determinant is not C,
1233 Then prior basic pay must match the entry for the prior
1234 grade and prior step or rate on Table 22 or be
1235 asterisks.
1236
1237 Default: Insert asterisks in prior pay basis and prior
1238 basic pay.
1239 Basis for Edit: 5 U.S.C. 5372 */
1240
1241 if p_prior_pay_plan = 'AL' and
1242 p_prior_pay_rate_det_code <> 'C' then
1243
1244 if p_prior_grade_or_level in ('01', '02') then
1245 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 22',
1246 p_prior_grade_or_level,
1247 'Minimum Basic Pay',
1248 p_prior_effective_date);
1249 else
1250 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 22',
1251 p_prior_grade_or_level || '-' || p_prior_step_or_rate,
1252 'Minimum Basic Pay',
1253 p_prior_effective_date);
1254 end if;
1255
1256 if max_basic_pay IS NOT NULL and
1257 (to_number(p_prior_basic_pay) <> max_basic_pay
1258 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
1259 then
1260 GHR_GHRWS52L.CPDF_Parameter_Check;
1261 hr_utility.set_message(8301,'GHR_37722_ALL_PROCEDURE_FAIL');
1262 hr_utility.raise_error;
1263 end if;
1264 end if;
1265
1266 /*570.65.2 If prior pay plan is CA,
1267 And prior pay rate determinant is not C,
1268 Then prior basic pay must match the entry on Table 23
1269 for the prior grade or be asterisks.
1270
1271 Default: Insert asterisks in prior pay basis and prior
1272 basic pay.
1273
1274 Basis for Edit: 5 U.S.C. 5372a*/
1275 if p_prior_pay_plan = 'CA' and
1276 p_prior_pay_rate_det_code <> 'C' then
1277
1278 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 23',
1279 p_prior_grade_or_level,
1280 'Minimum Basic Pay',p_prior_effective_date);
1281
1282 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1283 (to_number(p_prior_basic_pay) <> max_basic_pay
1284 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
1285 then
1286 GHR_GHRWS52L.CPDF_Parameter_Check;
1287 hr_utility.set_message(8301,'GHR_37723_ALL_PROCEDURE_FAIL');
1288 hr_utility.raise_error;
1289 end if;
1290 end if;
1291
1292 /*570.70.2 If prior pay plan is SL or ST,
1293 And prior pay rate determinant is not C,
1294 Then prior basic pay must be within the range in
1295 Table 21 or be asterisks.
1296
1297 Default: Insert asterisks in prior pay basis and prior
1298 basic pay.
1299 Basis for Edit: 5 U.S.C. 5376*/
1300
1301 -- Upd57 30-Jul-09 Mani Bug # 8653515 Added PRD D in the condition
1302
1303 IF p_effective_date >= to_date('2008/10/14','RRRR/MM/DD') then --Bug#8653515
1304 if p_prior_pay_plan in ('ST', 'SL') and
1305 p_prior_pay_rate_det_code NOT IN ('C','D') then
1306
1307 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
1308 p_prior_pay_plan ,
1309 'Minimum Basic Pay',p_prior_effective_date);
1310
1311 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
1312 p_prior_pay_plan ,
1313 'Maximum Basic Pay',p_prior_effective_date);
1314
1315 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1316 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay)
1317 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
1318 then
1319 GHR_GHRWS52L.CPDF_Parameter_Check;
1320 hr_utility.set_message(8301,'GHR_37724_ALL_PROCEDURE_FAIL');
1321 hr_utility.set_message_token('PRD','not C or D');
1322 hr_utility.raise_error;
1323 end if;
1324 end if;
1325 ELSE
1326 if p_prior_pay_plan in ('ST', 'SL') and
1327 p_prior_pay_rate_det_code <> 'C' then
1328
1329 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
1330 p_prior_pay_plan ,
1331 'Minimum Basic Pay',p_prior_effective_date);
1332
1333 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
1334 p_prior_pay_plan ,
1335 'Maximum Basic Pay',p_prior_effective_date);
1336
1337 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1338 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay)
1339 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
1340 then
1341 GHR_GHRWS52L.CPDF_Parameter_Check;
1342 hr_utility.set_message(8301,'GHR_37724_ALL_PROCEDURE_FAIL');
1343 hr_utility.set_message_token('PRD','not C');
1344 hr_utility.raise_error;
1345 end if;
1346 end if;
1347 end if;
1348
1349 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1350 570.71.2 If prior pay plan is ND,
1351 And prior pay rate determinant is 0, 5, 6, or 7,
1352 Then prior basic pay must be within the range for the
1353 prior grade on Table 30 or Table 31 (depending on prior
1354 pay rate determinant) or be asterisks.
1355
1356 Default: Insert asterisks in prior pay basis and prior
1357 basic pay.
1358
1359 -- added extra if for bug 726125
1360 if p_prior_pay_plan = 'ND' and
1361 p_prior_pay_rate_det_code in ('0','5','6','7') then
1362 if p_prior_pay_rate_det_code in ('0','7') then
1363 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 30',
1364 p_prior_grade_or_level ,
1365 'Minimum Basic Pay',
1366 p_prior_effective_date);
1367 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 30',
1368 p_prior_grade_or_level ,
1369 'Maximum Basic Pay',
1370 p_prior_effective_date);
1371 elsif p_prior_pay_rate_det_code in ('5','6') then
1372 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 31',
1373 p_prior_grade_or_level ,
1374 'Minimum Basic Pay',
1375 p_prior_effective_date);
1376 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 31',
1377 p_prior_grade_or_level ,
1378 'Maximum Basic Pay',
1379 p_prior_effective_date);
1380 end if;
1381
1382 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL then
1383 if not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay)
1384 or p_prior_basic_pay is null
1385 then
1386 GHR_GHRWS52L.CPDF_Parameter_Check;
1387 hr_utility.set_message(8301,'GHR_38407_ALL_PROCEDURE_FAIL');
1388 hr_utility.raise_error;
1389 end if;
1390 end if;
1391 end if;
1392 */
1393
1394 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1395 570.72.2 If prior pay plan is NG,
1396 And prior pay rate determinant is 0, 5, 6, or 7,
1397 Then prior basic pay must be within the range for the
1398 prior grade on Table 32 or Table 33 (depending on prior
1399 pay rate determinant) or be asterisks.
1400
1401 Default: Insert asterisks in prior pay basis and prior
1402 basic pay.
1403
1404 -- added extra if for bug 726125
1405 if p_prior_pay_plan = 'NG' and
1406 p_prior_pay_rate_det_code in ('0','5','6','7') then
1407 if p_prior_pay_rate_det_code in ('0','7') then
1408 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 32',
1409 p_prior_grade_or_level ,
1410 'Minimum Basic Pay',
1411 p_prior_effective_date);
1412 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 32',
1413 p_prior_grade_or_level ,
1414 'Maximum Basic Pay',
1415 p_prior_effective_date);
1416 elsif p_prior_pay_rate_det_code in ('5','6') then
1417 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 33',
1418 p_prior_grade_or_level ,
1419 'Minimum Basic Pay',
1420 p_prior_effective_date);
1421 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 33',
1422 p_prior_grade_or_level,
1423 'Maximum Basic Pay',
1424 p_prior_effective_date);
1425 end if;
1426
1427 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL then
1428 if (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay)
1429 or p_prior_basic_pay is null)
1430 then
1431 GHR_GHRWS52L.CPDF_Parameter_Check;
1432 hr_utility.set_message(8301,'GHR_38408_ALL_PROCEDURE_FAIL');
1433 hr_utility.raise_error;
1434 end if;
1435 end if;
1436 end if;
1437 */
1438
1439 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1440 570.73.2 If prior pay plan is NT,
1441 And prior pay rate determinant is 0, 5, 6, or 7,
1442 Then prior basic pay must be within the range for the
1443 prior grade on Table 34 or Table 35 (depending on prior
1444 pay rate determinant) or be asterisks.
1445
1446 Default: Insert asterisks in prior pay basis and prior
1447 basic pay.
1448
1449 if p_prior_pay_plan = 'NT' and
1450 p_prior_pay_rate_det_code in ('0','5','6','7') then -- added for bug 726125
1451 if p_prior_pay_rate_det_code in ('0','7') then
1452 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 34',
1453 p_prior_grade_or_level ,'Minimum Basic Pay',p_prior_effective_date);
1454 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 34',
1455 p_prior_grade_or_level ,'Maximum Basic Pay',p_prior_effective_date);
1456 elsif p_prior_pay_rate_det_code in ('5','6') then
1457 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 35',
1458 p_prior_grade_or_level ,'Minimum Basic Pay',p_prior_effective_date);
1459 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 35',
1460 p_prior_grade_or_level ,'Maximum Basic Pay',p_prior_effective_date);
1461 end if;
1462
1463 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL then
1464 if (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay)
1465 or p_prior_basic_pay is null)
1466 then
1467 GHR_GHRWS52L.CPDF_Parameter_Check;
1468 hr_utility.set_message(8301,'GHR_38409_ALL_PROCEDURE_FAIL');
1469 hr_utility.raise_error;
1470 end if;
1471 end if;
1472 end if;
1473 */
1474
1475 /* Commented -- Dec 2001 Patch
1476 570.75.2 If prior pay plan is TP,
1477 And prior pay basis is SY,
1478 Then prior basic pay must be within the range on
1479 Table 24 or be asterisks.
1480
1481 Default: Insert asterisks in prior pay basis and prior
1482 basic pay.
1483 if p_prior_pay_plan = 'TP' and
1484 p_prior_pay_basis = 'SY' then
1485
1486 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 24',
1487 p_prior_pay_plan ,
1488 'Minimum Basic Pay',
1489 p_prior_effective_date);
1490 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 24',
1491 p_prior_pay_plan ,
1492 'Maximum Basic Pay',
1493 p_prior_effective_date);
1494 if max_basic_pay IS NOT NULL and
1495 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay)
1496 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
1497 then
1498 GHR_GHRWS52L.CPDF_Parameter_Check;
1499 hr_utility.set_message(8301,'GHR_37725_ALL_PROCEDURE_FAIL');
1500 hr_utility.raise_error;
1501 end if;
1502 end if;
1503 */
1504
1505 /*570.82.2 If prior pay plan is GG,
1506 And prior grade is SL,
1507 And prior pay rate determinant is 0,
1508 Then prior basic pay must be within the range on
1509 Table 21 or be asterisks.
1510
1511 Default: Insert asterisks in prior pay basis and prior
1512 basic pay.*/
1513 if p_prior_pay_plan = 'CG' and
1514 p_prior_pay_rate_det_code = '0' and
1515 p_prior_grade_or_level = 'SL' then
1516
1517 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
1518 p_prior_pay_plan ,
1519 'Minimum Basic Pay',
1520 p_prior_effective_date);
1521
1522 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
1523 p_prior_pay_plan ,
1524 'Maximum Basic Pay',
1525 p_prior_effective_date);
1526 if max_basic_pay IS NOT NULL and min_basic_pay is not null and
1527 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay)
1528 or p_prior_basic_pay is null and to_number(p_prior_basic_pay) = 0)
1529 then
1530 GHR_GHRWS52L.CPDF_Parameter_Check;
1531 hr_utility.set_message(8301,'GHR_37727_ALL_PROCEDURE_FAIL');
1532 hr_utility.raise_error;
1533 end if;
1534 end if;
1535
1536 /*570.83.2 If prior pay plan is IJ,
1537 And prior pay rate determinant is 0 or 7,
1538 Then prior basic pay must match the entry for the prior
1539 step or rate on Table 36 or be asterisks.
1540
1541 Default: Insert asterisks in prior pay basis and prior
1542 basic pay.*/
1543 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1544 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1545 if p_prior_pay_plan = 'IJ' and
1546 p_prior_pay_rate_det_code in ('0','7') then
1547
1548 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 36',
1549 p_prior_step_or_rate ,
1550 'Maximum Basic Pay',
1551 p_prior_effective_date);
1552 if max_basic_pay IS NOT NULL and
1553 (not(to_number(p_prior_basic_pay) <> max_basic_pay)
1554 or p_prior_basic_pay is null)
1555 then
1556 GHR_GHRWS52L.CPDF_Parameter_Check;
1557 hr_utility.set_message(8301,'GHR_38410_ALL_PROCEDURE_FAIL');
1558 hr_utility.raise_error;
1559 end if;
1560 end if;
1561 end if;
1562
1563 /*570.84.2 If prior pay plan is GG,
1564 And prior grade is 01 through 15,
1565 And prior pay rate determinant is 2, 3, 4, J, K, or R,
1566 Then prior basic pay must be equal to or exceed the
1567 minimum for prior grade on Table 2 or be asterisks.
1568
1569 Default: Insert asterisks in prior pay basis and prior
1570 basic pay.*/
1571 -- Bug# 9255822 added PRD Y
1572
1573 if p_prior_pay_plan = 'CG' and
1574 p_prior_grade_or_level between '01' and '15' and
1575 p_prior_pay_rate_det_code in ('2','3','4','J','K','R','Y') then
1576
1577 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 2',
1578 p_prior_grade_or_level||'-'||p_prior_step_or_rate ,
1579 'Minimum Basic Pay',
1580 p_prior_effective_date);
1581 if min_basic_pay IS NOT NULL and
1582 (not(to_number(p_prior_basic_pay) >= min_basic_pay)
1583 or p_prior_basic_pay is null or to_number(p_prior_basic_pay) = 0)
1584 then
1585 GHR_GHRWS52L.CPDF_Parameter_Check;
1586 hr_utility.set_message(8301,'GHR_37728_ALL_PROCEDURE_FAIL');
1587 hr_utility.raise_error;
1588 end if;
1589 end if;
1590
1591 /*570.86.2 If prior pay plan is GH,
1592 And prior pay rate determinant is 0 or 7,
1593 Then prior basic pay must be within the range for the
1594 appropriate prior grade on Table 3 or be asterisks.
1595
1596 Default: Insert asterisks in prior pay basis and prior
1597 basic pay.*/
1598 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1599 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1600 if p_prior_pay_plan = 'GH' and
1601 p_prior_pay_rate_det_code in ('0','7') then
1602
1603 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
1604 p_prior_grade_or_level||'-'||p_prior_step_or_rate ,
1605 'Minimum Basic Pay',
1606 p_prior_effective_date);
1607 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
1608 p_prior_grade_or_level||'-'||p_prior_step_or_rate ,
1609 'Maximum Basic Pay',
1610 p_prior_effective_date);
1611
1612 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1613 (not(to_number(p_prior_basic_pay) between min_basic_pay and max_basic_pay)
1614 or p_prior_basic_pay is null
1615 or to_number(p_prior_basic_pay) = 0)
1616 then
1617 GHR_GHRWS52L.CPDF_Parameter_Check;
1618 hr_utility.set_message(8301,'GHR_37729_ALL_PROCEDURE_FAIL');
1619 hr_utility.raise_error;
1620 end if;
1621 end if;
1622 end if;
1623 end chk_prior_basic_pay;
1624 --
1625 --
1626 -- Name: Locality Adjustment
1627 /* Table 25 contains up to a maximum of 3 percentages.
1628 The locality adjustment amount will pass the lookup if, as a percentage of basic pay,
1629 it represents any percentage shown for the locality area, with the "as of date" of the
1630 file falling within the date range, on Table 25. (If a more percise check is needed,
1631 as is the case of area 41, a subsequent relationship edit will catch the error.)
1632 Locality pay is generated within CPDF according to the duty station. For definitions,
1633 see the Guide to Personnel Data Standards. */
1634 --
1635 --
1636 procedure chk_locality_adj
1637 (p_to_pay_plan in varchar2
1638 ,p_to_basic_pay in varchar2
1639 ,p_pay_rate_determinant_code in varchar2
1640 ,p_retained_pay_plan in varchar2
1641 ,p_prior_pay_plan in varchar2
1642 ,p_prior_pay_rate_det_code in varchar2 /* Non-SF52 Data Item */
1643 ,p_locality_pay_area in varchar2 /* Non-SF52 Data Item */
1644 ,p_to_locality_adj in varchar2
1645 ,p_effective_date in date
1646 ,p_as_of_date in date /* Non-SF52 */
1647 ,p_first_noac_lookup_code in varchar2
1648 ,p_agency_subelement in varchar2
1649 ,p_duty_station_Code in varchar2
1650 ,p_special_pay_table_id in varchar2 --Bug# 5745356(upd50)
1651 ) is
1652 l_lpa_area ghr_locality_pay_areas_f.locality_pay_area_code%type;
1653 l_lpa_pct ghr_locality_pay_areas_f.adjustment_percentage%type;
1654 l_leo_pct ghr_locality_pay_areas_f.leo_adjustment_percentage%type;
1655 l_lpa_effective_start_date ghr_locality_pay_areas_f.effective_start_date%type;
1656 l_lpa_effective_end_date ghr_locality_pay_areas_f.effective_end_date%type;
1657 l_lpa_pct_max ghr_locality_pay_areas_f.adjustment_percentage%type;
1658 l_lpa_pct_min ghr_locality_pay_areas_f.adjustment_percentage%type;
1659 l_effective_date date;
1660 l_pay_table VARCHAR2(4); --Bug# 5745356(upd50)
1661 l_table5_value number(10,2); --Bug# 8309414
1662
1663 CURSOR c1 is
1664 SELECT effective_start_date, effective_end_date,
1665 NVL(adjustment_percentage,0),
1666 NVL(leo_adjustment_percentage,0)
1667 FROM ghr_locality_pay_areas_f
1668 WHERE locality_pay_area_code = p_locality_pay_area
1669 AND trunc(l_effective_date) between effective_start_date
1670 and nvl(effective_end_date, l_effective_date);
1671
1672 begin
1673 IF p_first_noac_lookup_code = '866' THEN
1674 l_effective_date := p_effective_date + 1;
1675 ELSE
1676 l_effective_date := p_effective_date;
1677 END IF;
1678
1679 open c1;
1680 fetch c1 into l_lpa_effective_start_date, l_lpa_effective_end_date,
1681 l_lpa_pct, l_leo_pct;
1682 close c1;
1683 l_table5_value:= GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 5',
1684 '04' ,
1685 'Maximum Basic Pay',p_effective_date);
1686 --
1687 -- 27-jul-98
1688 -- all the edits where the locality adj as a percentage of basic pay was being compared
1689 -- to the lpa_pct has been modified to include leo_pct.
1690 -- bug #703978
1691 --
1692
1693 /*
1694 -- This edit is DELETED as per update 8 of the edit manual.
1695 -- commented out for delete by skutteti on 12-oct-1998
1696 --
1697 -- 652.05.2
1698 if ( p_locality_pay_area in ('02','05','08','11','14','15','17','20','23','26','29','32','35',
1699 '38','41','42','43','45','47','55','56','57','58','59','62','71','74','77','80','88','98')) then
1700 if ((trunc(p_effective_date) not between l_lpa_effective_start_date and
1701 nvl(l_lpa_effective_end_date, p_effective_date)) and
1702 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0)) then
1703 GHR_GHRWS52L.CPDF_Parameter_Check;
1704 hr_utility.set_message(8301,'GHR_37730_ALL_PROCEDURE_FAIL');
1705 hr_utility.raise_error;
1706 end if;
1707 end if;
1708 */
1709
1710
1711 -- 652.10.1
1712 -- 'p_as_of_date' has been determined to be effective date.
1713 -------------------------------------------------------------------------------
1714 --- Modified by Date Comments
1715 -------------------------------------------------------------------------------
1716 --- Madhuri 01-MAR-05 Change the condition 'And locality pay area is 02 through 88'
1717 --- To:'And locality pay area is other than ZY or ZZ' as of 09-JAN-2005
1718 -- Raju 08-Nov-2005 UPD 42(Bug 4567571) Delete PRD 3,J and K as of 01-May-2005
1719 -- amrchakr 28-Sep-2006 Remove locality pay area 'ZY' effective 01-jul-2006
1720 -- Raju 28-Jan-2010 Commenting the below edit since 652.10.1 and 652.10.2 are same bug# 9309565
1721 -------------------------------------------------------------------------------
1722 /*
1723 IF ( p_effective_date < to_date('09/01/2005', 'dd/mm/yyyy')) THEN
1724 if (
1725 p_to_pay_plan in ('GS','GM','GG','GH','FO','FP') and
1726 p_pay_rate_determinant_code in ('0','3','7','J','K') and
1727 p_locality_pay_area between '02' and '88' and
1728 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0) and
1729 (p_to_basic_pay is not null and to_number(p_to_basic_pay) <> 0)
1730 ) then
1731 if (
1732 l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
1733 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
1734 ) then
1735 GHR_GHRWS52L.CPDF_Parameter_Check;
1736 hr_utility.set_message(8301,'GHR_37731_ALL_PROCEDURE_FAIL');
1737 --hr_utility.set_message_token('LOC_PAY','02 through 88');
1738 hr_utility.raise_error;
1739 end if;
1740 end if;
1741 ELSIF ( p_effective_date < to_date('01/05/2005', 'dd/mm/yyyy')) THEN
1742 if (
1743 p_to_pay_plan in ('GS','GM','GG','GH','FO','FP') and
1744 p_pay_rate_determinant_code in ('0','3','7','J','K') and
1745 p_locality_pay_area NOT IN ('ZY','ZZ') and
1746 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0) and
1747 (p_to_basic_pay is not null and to_number(p_to_basic_pay) <> 0)
1748 ) then
1749 if (
1750 l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
1751 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
1752 ) then
1753 GHR_GHRWS52L.CPDF_Parameter_Check;
1754 hr_utility.set_message(8301,'GHR_38928_ALL_PROCEDURE_FAIL ');
1755 hr_utility.set_message_token('PRD_LIST','0, 3, 7, J, or K');
1756 hr_utility.raise_error;
1757 end if;
1758 end if;
1759
1760 ELSIF (( p_effective_date >= to_date('01/05/2005', 'dd/mm/yyyy'))
1761 AND
1762 ( p_effective_date < to_date('01/07/2006', 'dd/mm/yyyy'))
1763 )
1764 THEN
1765 if (
1766 p_to_pay_plan in ('GS','GM','GG','GH','FO','FP') and
1767 p_pay_rate_determinant_code in ('0','7') and
1768 p_locality_pay_area NOT IN ('ZY','ZZ') and
1769 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0) and
1770 (p_to_basic_pay is not null and to_number(p_to_basic_pay) <> 0)
1771 ) then
1772 if (
1773 l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
1774 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
1775 ) then
1776 GHR_GHRWS52L.CPDF_Parameter_Check;
1777 hr_utility.set_message(8301,'GHR_38928_ALL_PROCEDURE_FAIL');
1778 hr_utility.set_message_token('PRD_LIST','0, or 7');
1779 hr_utility.raise_error;
1780 end if;
1781 end if;
1782
1783 ELSIF ( p_effective_date >= to_date('01/07/2005', 'dd/mm/yyyy'))
1784 AND ( p_effective_date < to_date('13/08/2007', 'dd/mm/yyyy')) THEN
1785
1786 if (
1787 p_to_pay_plan in ('GS','GM','GG','GH','FO','FP') and
1788 p_pay_rate_determinant_code in ('0','7') and
1789 p_locality_pay_area NOT IN ('ZZ') and
1790 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0) and
1791 (p_to_basic_pay is not null and to_number(p_to_basic_pay) <> 0)
1792 )
1793 then
1794 if (
1795 l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
1796 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
1797 ) then
1798 GHR_GHRWS52L.CPDF_Parameter_Check;
1799 hr_utility.set_message(8301,'GHR_37694_ALL_PROCEDURE_FAIL');
1800 hr_utility.raise_error;
1801 end if;
1802 end if;
1803 ELSE
1804 if (
1805 p_to_pay_plan in ('GS','GM','GG','GH','GL','FO','FP') and
1806 p_pay_rate_determinant_code in ('0','7') and
1807 p_locality_pay_area NOT IN ('ZZ') and
1808 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0) and
1809 (p_to_basic_pay is not null and to_number(p_to_basic_pay) <> 0)
1810 )
1811 then
1812 if (
1813 (l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
1814 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) ) OR
1815 (round(to_number(p_to_locality_adj)+to_number(p_to_basic_pay)) > l_table5_value)
1816 ) then
1817 GHR_GHRWS52L.CPDF_Parameter_Check;
1818 hr_utility.set_message(8301,'GHR_37456_ALL_PROCEDURE_FAIL');
1819 hr_utility.raise_error;
1820 end if;
1821 end if;
1822
1823 END IF;
1824 */
1825 -------------------------------------------------------------------------------
1826
1827 -- 652.10.2
1828 /* 'p_as_of_date' has been determined to be effective date' */
1829 -------------------------------------------------------------------------------
1830 --- Modified by Date Comments
1831 -------------------------------------------------------------------------------
1832 --- Madhuri 01-MAR-05 Change the condition 'And locality pay area is 02 through 88'
1833 --- To:'And locality pay area is other than ZY or ZZ' as of 09-JAN-2005
1834 -- Raju 08-Nov-2005 UPD 42(Bug 4567571) Delete PRD 3,J and K as of 01-May-2005
1835 -- amrchakr 28-sep-2006 Remove locality pay area 'ZY' effective 01-jul-2006
1836 -- UPD 56 8309414 Raju From 13-Aug-07
1837 -- U58 Raju 01-jan-2010 Bug 9503972
1838 -------------------------------------------------------------------------------
1839 IF ( p_effective_date < to_date('09/01/2005', 'dd/mm/yyyy')) THEN
1840 if (
1841 p_to_pay_plan in ('GS','GM','GG','GH','FO','FP') and
1842 p_pay_rate_determinant_code in ('0','3','7','J','K') and
1843 p_locality_pay_area between '02' and '88' and
1844 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0) and
1845 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> 0)
1846 ) then
1847 if (l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
1848 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
1849 ) then
1850 GHR_GHRWS52L.CPDF_Parameter_Check;
1851 hr_utility.set_message(8301,'GHR_37747_ALL_PROCEDURE_FAIL');
1852 hr_utility.raise_error;
1853 end if;
1854 end if;
1855 ELSIF ( p_effective_date < to_date('01/05/2005', 'dd/mm/yyyy')) THEN
1856 if (
1857 p_to_pay_plan in ('GS','GM','GG','GH','FO','FP') and
1858 p_pay_rate_determinant_code in ('0','3','7','J','K') and
1859 p_locality_pay_area NOT IN ('ZY','ZZ') and
1860 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0) and
1861 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> 0)
1862 ) then
1863 if (l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
1864 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
1865 ) then
1866 GHR_GHRWS52L.CPDF_Parameter_Check;
1867 hr_utility.set_message(8301,'GHR_38929_ALL_PROCEDURE_FAIL');
1868 hr_utility.set_message_token('PRD_LIST','0, 3, 7, J, or K');
1869 hr_utility.raise_error;
1870 end if;
1871 end if;
1872
1873 ELSIF (( p_effective_date >= to_date('01/05/2005', 'dd/mm/yyyy'))
1874 AND
1875 ( p_effective_date < to_date('01/07/2006', 'dd/mm/yyyy'))
1876 )
1877 THEN
1878 if (
1879 p_to_pay_plan in ('GS','GM','GG','GH','FO','FP') and
1880 p_pay_rate_determinant_code in ('0','7') and
1881 p_locality_pay_area NOT IN ('ZY','ZZ') and
1882 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0) and
1883 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> 0)
1884 ) then
1885 if (l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
1886 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
1887 ) then
1888 GHR_GHRWS52L.CPDF_Parameter_Check;
1889 hr_utility.set_message(8301,'GHR_38929_ALL_PROCEDURE_FAIL');
1890 hr_utility.set_message_token('PRD_LIST','0, or 7');
1891 hr_utility.raise_error;
1892 end if;
1893 end if;
1894
1895 ELSIF ( p_effective_date >= to_date('01/07/2006', 'dd/mm/yyyy'))
1896 AND ( p_effective_date < to_date('13/08/2007', 'dd/mm/yyyy')) THEN
1897 if (
1898 p_to_pay_plan in ('GS','GM','GG','GH','FO','FP') and
1899 p_pay_rate_determinant_code in ('0','7') and
1900 p_locality_pay_area NOT IN ('ZZ') and
1901 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0) and
1902 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> 0)
1903 ) then
1904 if (l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
1905 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
1906 ) then
1907 GHR_GHRWS52L.CPDF_Parameter_Check;
1908 hr_utility.set_message(8301,'GHR_37695_ALL_PROCEDURE_FAIL');
1909 hr_utility.raise_error;
1910 end if;
1911 end if;
1912 ELSIF p_effective_date < fnd_date.canonical_to_date('2010/01/01') THEN
1913 if (p_to_pay_plan in ('GS','GM','GG','GL','GH','FO','FP') and
1914 p_pay_rate_determinant_code in ('0','7') and
1915 p_locality_pay_area NOT IN ('ZZ') and
1916 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0) and
1917 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> 0)
1918 ) then
1919 if ((l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
1920 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) ) OR
1921 (round(to_number(p_to_locality_adj)+to_number(p_to_basic_pay)) > l_table5_value)
1922 ) then
1923 GHR_GHRWS52L.CPDF_Parameter_Check;
1924 hr_utility.set_message(8301,'GHR_37457_ALL_PROCEDURE_FAIL');
1925 hr_utility.set_message_token('LOC_AREA','ZZ');
1926 hr_utility.raise_error;
1927 end if;
1928 end if;
1929 ELSE
1930 if (p_to_pay_plan in ('GS','GM','GG','GL','GH','FO','FP') and
1931 p_pay_rate_determinant_code in ('0','7') and
1932 p_locality_pay_area NOT IN ('AK','HI','ON','ZZ') and
1933 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0) and
1934 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> 0)
1935 ) then
1936 if ((l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
1937 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) ) OR
1938 (round(to_number(p_to_locality_adj)+to_number(p_to_basic_pay)) > l_table5_value)
1939 ) then
1940 GHR_GHRWS52L.CPDF_Parameter_Check;
1941 hr_utility.set_message(8301,'GHR_37457_ALL_PROCEDURE_FAIL');
1942 hr_utility.set_message_token('LOC_AREA','AK, HI, ON, ZZ');
1943 hr_utility.raise_error;
1944 end if;
1945 end if;
1946 END IF;
1947 -------------------------------------------------------------------------------
1948 -- 652.15.1
1949 /* If pay plan is GS, GM, GG, or GH,
1950 And pay rate determinant is 5, 6, E, F, or M,
1951 And locality pay area is 02 through 88,
1952 Then the locality adjustment amount may not be spaces
1953 or an amount greater than the highest percentage
1954 present and currently valid for the locality pay area
1955 on Table 25.
1956
1957 Default: Insert asterisks in locality adjustment. */
1958
1959 -- 'p_as_of_date' has been determined to be effective_date.
1960 -- Adds pay rate determinants E and F effective 01-mar-1998
1961 -------------------------------------------------------------------------------
1962 --- Modified by Date Comments
1963 -------------------------------------------------------------------------------
1964 --- Madhuri 01-MAR-05 Change the condition 'And locality pay area is 02 through 88'
1965 --- To:'And locality pay area is other than ZY or ZZ' as of 09-JAN-2005
1966 -- Raju 31/1/06 Commented the code effective from 01-May-2005
1967 -- upd50 06-Feb-07 Raju From 01-Oct-2006 Bug#5745356
1968 -- upd50 27-Nov-11 Raju From 01-Oct-2006 Bug# 13257133 Modified to Greater than
1969 -- earlier wrongly coded as less then with bug 5745356
1970 -------------------------------------------------------------------------------
1971 IF p_effective_date < fnd_date.canonical_to_date('1998/03/01') then
1972 if (
1973 p_to_pay_plan in ('GS','GM','GG','GH') and
1974 p_pay_rate_determinant_code in ('6','5','M') and
1975 p_locality_pay_area between '02' and '88' and
1976 (p_to_locality_adj is not null or to_number(p_to_locality_adj) <> 0) and
1977 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> 0)
1978 ) then
1979 if (
1980 l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
1981 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
1982 ) then
1983 GHR_GHRWS52L.CPDF_Parameter_Check;
1984 hr_utility.set_message(8301,'GHR_37732_ALL_PROCEDURE_FAIL');
1985 hr_utility.raise_error;
1986 end if;
1987 end if;
1988 ELSE --- meaning effective_date is greater than '1998/03/01'
1989 -- this check is for dates from 01-MAR-1998 to 08-JAN-2005
1990
1991 IF ( p_effective_date < to_date('09/01/2005', 'dd/mm/yyyy') ) THEN
1992 if (p_to_pay_plan in ('GS','GM','GG','GH') and
1993 p_pay_rate_determinant_code in ('6','5','M','E','F') and
1994 p_locality_pay_area between '02' and '88' and
1995 (p_to_locality_adj is not null or to_number(p_to_locality_adj) <> 0) and
1996 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> 0)
1997 ) then
1998 if (
1999 l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
2000 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
2001 ) then
2002 GHR_GHRWS52L.CPDF_Parameter_Check;
2003 hr_utility.set_message(8301,'GHR_37868_ALL_PROCEDURE_FAIL');
2004 hr_utility.raise_error;
2005 end if;
2006 end if;
2007 -- FWFA Changes
2008 ELSIF ( p_effective_date < to_date('01/05/2005', 'dd/mm/yyyy') ) THEN
2009 if (
2010 p_to_pay_plan in ('GS','GM','GG','GH') and
2011 p_pay_rate_determinant_code in ('6','5','M','E','F') and
2012 p_locality_pay_area NOT IN ('ZY','ZZ') and
2013 (p_to_locality_adj is not null or to_number(p_to_locality_adj) <> 0) and
2014 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> 0)
2015 ) then
2016
2017 if (
2018 l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
2019 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
2020 ) then
2021 GHR_GHRWS52L.CPDF_Parameter_Check;
2022 hr_utility.set_message(8301,'GHR_38930_ALL_PROCEDURE_FAIL');
2023 hr_utility.raise_error;
2024 end if;
2025 end if;
2026 -- Begin Bug# 4999292 and 4917098
2027 /*ELSE
2028 if (p_to_pay_plan in ('GS','GM','GG','GH') and
2029 p_pay_rate_determinant_code in ('6','5','E','F')
2030 ) then
2031
2032 if to_number(p_to_locality_adj) <= 0 then
2033 GHR_GHRWS52L.CPDF_Parameter_Check;
2034 hr_utility.set_message(8301,'GHR_38981_ALL_PROCEDURE_FAIL');
2035 hr_utility.raise_error;
2036 end if;
2037 end if;*/
2038 -- End Bug# 4999292 and 4917098
2039 -- FWFA Changes
2040 ELSIF ( p_effective_date >= to_date('01/10/2006', 'dd/mm/yyyy') ) THEN -- 13257133 modified as greater than or equal
2041 l_pay_table := SUBSTR(ghr_pay_calc.get_user_table_name(p_special_pay_table_id),1,4);
2042 if (p_to_pay_plan in ('GS','GM','GG','GH') and
2043 p_pay_rate_determinant_code in ('6','5','E','F') and
2047 if to_number(p_to_locality_adj) <= 0 then
2044 l_pay_table <> '0491'
2045 ) then
2046
2048 GHR_GHRWS52L.CPDF_Parameter_Check;
2049 hr_utility.set_message(8301,'GHR_38981_ALL_PROCEDURE_FAIL');
2050 hr_utility.raise_error;
2051 end if;
2052 end if;
2053
2054 END IF; -- less or greater then 09-JAN-05 check
2055 END IF;
2056 -------------------------------------------------------------------------------
2057
2058 -- 652.15.2
2059 /* If pay plan = GS, GM, GG, GH, FO, or FP,
2060 And PRD = 6, 5, or M,
2061 And locality pay area is not 99,
2062 Then the locality adjustment amount may not be greater
2063 than the highest percentage on Table 25 for the area,
2064 valid as of the effective date.
2065
2066 Default: Insert asterisks in locality adjustment.
2067
2068 Basis for Edit: 5 U.S.C. 5304
2069 'p_as_of_date' has been determined to be effective date. */
2070 -------------------------------------------------------------------------------
2071 --- Modified by Date Comments
2072 -------------------------------------------------------------------------------
2073 --- Madhuri 01-MAR-05 Change the condition 'And locality pay area is 02 through 88'
2074 --- To:'And locality pay area is other than ZY or ZZ' as of 09-JAN-05
2075 -- utokachi 10/21/05 Validate for actions after 01-MAY-2005
2076 -- Raju 31/1/06 Commented the code effective from 01-May-2005
2077 -------------------------------------------------------------------------------
2078 IF ( p_effective_date < to_date('09/01/2005', 'dd/mm/yyyy') ) THEN
2079 if (
2080 p_to_pay_plan in ('GS','GM','GG','GH','FO','FP') and
2081 p_pay_rate_determinant_code in ('6','5','M') and
2082 p_locality_pay_area between '02' and '88' and
2083 (p_to_locality_adj is not null or to_number(p_to_locality_adj) <> 0) and
2084 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> 0)
2085 ) then
2086 if (
2087 l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
2088 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
2089 ) then
2090 GHR_GHRWS52L.CPDF_Parameter_Check;
2091 hr_utility.set_message(8301,'GHR_37748_ALL_PROCEDURE_FAIL');
2092 hr_utility.raise_error;
2093 end if;
2094 end if;
2095 -- FWFA Changes
2096 ELSIF ( p_effective_date < to_date('01/05/2005', 'dd/mm/yyyy') ) THEN
2097 if (
2098 p_to_pay_plan in ('GS','GM','GG','GH','FO','FP') and
2099 p_pay_rate_determinant_code in ('6','5','M') and
2100 p_locality_pay_area NOT IN ('ZY','ZZ') and
2101 (p_to_locality_adj is not null or to_number(p_to_locality_adj) <> 0) and
2102 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> 0)
2103 ) then
2104 if (
2105 l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
2106 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
2107 ) then
2108 GHR_GHRWS52L.CPDF_Parameter_Check;
2109 hr_utility.set_message(8301,'GHR_38931_ALL_PROCEDURE_FAIL');
2110 hr_utility.raise_error;
2111 end if;
2112 end if;
2113 -- Begin Bug# 4999292
2114 /*ELSIF ( p_effective_date >= to_date('01/05/2005', 'dd/mm/yyyy') ) THEN
2115 if (
2116 p_to_pay_plan in ('GS','GM','GG','GH') and
2117 p_pay_rate_determinant_code in ('6','5') ) then
2118 if (to_number(p_to_locality_adj) <= 0 ) then
2119 GHR_GHRWS52L.CPDF_Parameter_Check;
2120 hr_utility.set_message(8301,'GHR_38982_ALL_PROCEDURE_FAIL');
2121 hr_utility.raise_error;
2122 end if;
2123 end if;*/
2124 -- End Bug# 4999292
2125 -- FWFA Changes
2126 END IF;
2127 -------------------------------------------------------------------------------
2128 -- 652.20.3
2129 -- Update Date By Comments
2130 -- 21/2 20-Feb-2003 Madhuri Adding HS to other than list
2131 -- 30-OCT-2003 Ashley Commented second condition
2132 -------------------------------------------------------------------------------
2133 --- Modified by Date Comments
2134 -------------------------------------------------------------------------------
2135 --- Madhuri 01-MAR-05 Change the condition "If Locality Pay Area is 99"
2136 --- To: If locality pay area is ZZ as of 09-JAN-05
2137 -- utokachi 10/24/05 Validate for actions after 01-MAY-2005
2138 -- Raju 26-Jun-06 Validate for actions after 01-Jan-2006
2139 -- and added pay plan condition affective frm 01-Apr-06
2140 -- arbasu 26-Oct-06 Added PRD condition for GHR_38932_ALL_PROCEDURE_FAIL
2141 -------------------------------------------------------------------------------
2142 IF p_effective_date < fnd_date.canonical_to_date('1998/09/01') then
2143 if (p_locality_pay_area = '99' and
2144 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0)) then
2145 GHR_GHRWS52L.CPDF_Parameter_Check;
2146 hr_utility.set_message(8301,'GHR_37733_ALL_PROCEDURE_FAIL');
2147 hr_utility.raise_error;
2148 end if;
2149 ELSE -- meaning bet 01-SEP-1998 to 01-JAN-2005
2150 IF ( p_effective_date < to_date('09/01/2005', 'dd/mm/yyyy') ) THEN
2151 -- If locality pay area is 99, And agency is other than AM, GY, HS, IB, or ST
2152 -- Then locality adjustment must be spaces or asterisks.
2153 if p_locality_pay_area = '99' and
2154 p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0
2155 then
2156 hr_utility.set_message(8301,'GHR_37896_ALL_PROCEDURE_FAIL');
2157 hr_utility.raise_error;
2158 end if;
2159 --FWFA Changes
2160 ELSIF p_effective_date < to_date('2005/05/01','YYYY/MM/DD') THEN
2161 -- If locality pay area is 99, And agency is other than AM, GY, HS, IB, or ST
2162 -- Then locality adjustment must be spaces or asterisks.
2166 then
2163 if p_locality_pay_area = 'ZZ' and
2164 p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0 and
2165 p_pay_rate_determinant_code NOT IN ('6','E','F')
2167 hr_utility.set_message(8301,'GHR_38932_ALL_PROCEDURE_FAIL');
2168 hr_utility.raise_error;
2169 end if;
2170 ELSIF p_effective_date < to_date('2006/01/01','YYYY/MM/DD') THEN --Modified for Bug# 5073313
2171 if p_locality_pay_area = 'ZZ' and p_pay_rate_determinant_code NOT in ('6','5','E','F')
2172 AND p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0
2173 then
2174 hr_utility.set_message(8301,'GHR_38978_ALL_PROCEDURE_FAIL');
2175 hr_utility.raise_error;
2176 end if;
2177 -- Begin Bug# 5073313
2178 elsif p_effective_date < fnd_date.canonical_to_date('2006/04/01') then
2179 if substr(p_agency_subelement,1,2) not in ('HS') and p_locality_pay_area = 'ZZ' and
2180 p_pay_rate_determinant_code NOT in ('6','5','E','F')
2181 AND p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0
2182 then
2183 hr_utility.set_message(8301,'GHR_37065_ALL_PROCEDURE_FAIL');
2184 hr_utility.set_message_token('PAY_PLAN','5, 6, E, or F');
2185 hr_utility.raise_error;
2186 end if;
2187 else
2188 if substr(p_agency_subelement,1,2) not in ('HS') and p_locality_pay_area = 'ZZ' and
2189 p_pay_rate_determinant_code NOT in ('6','5','E','F')and
2190 p_to_pay_plan in ('GM','GS')
2191 AND p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0
2192 then
2193 hr_utility.set_message(8301,'GHR_37065_ALL_PROCEDURE_FAIL');
2194 hr_utility.set_message_token('PAY_PLAN','5, 6, E, or F, and pay plan is GM or GS');
2195 hr_utility.raise_error;
2196 end if;
2197 -- End Bug# 5073313
2198 END IF;
2199 -- FWFA Changes
2200 END IF;
2201 -------------------------------------------------------------------------------
2202
2203 --Commented as per EOY 2003 cpdf changes by Ashley
2204 -- 652.21.3
2205 -- Update Date By Comments
2206 -- 21/2 20-Feb-2003 Madhuri Adding HS to the list
2207 -- If locality pay area is 99,
2208 -- And agency is AM, GY, HS, IB, or ST
2209 -- And either:
2210 -- The first two positions of duty station are other
2211 -- than CA or MX,
2212 -- Or pay plan is FO or FP,
2213 -- Then locality adjustment must be spaces or asterisks.
2214
2215 /* if p_effective_date >= fnd_date.canonical_to_date('1998/09/01') then
2216 if p_locality_pay_area = '99' and
2217 substr(p_agency_subelement,1,2) in ('AM','GY','HS','IB','ST') and
2218 (
2219 substr(p_duty_station_code,1,2) not in ('CA','MX') or
2220 p_to_pay_plan in ('FO','FP')
2221 ) and
2222 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0) then
2223 hr_utility.set_message(8301,'GHR_37897_ALL_PROCEDURE_FAIL');
2224 hr_utility.raise_error;
2225 end if;
2226 end if;*/
2227
2228
2229 -- 652.30.3
2230 -- Update Date By Effective Date Comment
2231 -- 9 04/05/99 vravikan All pay plans starting with W but not 'WM'
2232 -- 20/2 20-Feb-2003 Madhuri Deleting pay plans EH and EI.
2233 -- UPD 41(Bug 4567571) Raju 08-Nov-2005 Adds pay plan ES and FE
2234 -- Raju 08-Nov-2005 UPD 42(Bug 4567571) Delete PRD U and V as of 01-May-2005
2235 -- Upd57 30-Jul-09 Mani Bug # 8653515 Added SL, ST in the condition from 12-Apr-2009
2236 -- U58 Raju 01-jan-2010 Bug 9503972
2237
2238 IF ( p_effective_date < to_date('01/05/2005', 'dd/mm/yyyy') ) THEN
2239 if (
2240 (
2241 p_to_pay_plan in ('EX','ZZ','ES','FE') or
2242 substr(p_to_pay_plan,1,1) in ('B','X') or
2243 (substr(p_to_pay_plan,1,1) in ('W') and p_to_pay_plan not in ('WM') )
2244 ) and
2245 p_pay_rate_determinant_code not in ('A','B','E','F','S','U','V') and
2246 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0)
2247 ) then
2248 GHR_GHRWS52L.CPDF_Parameter_Check;
2249 hr_utility.set_message(8301,'GHR_37734_ALL_PROCEDURE_FAIL');
2250 hr_utility.set_message_token('PRD_LIST','A, B, E, F, S, U, or V');
2251 hr_utility.set_message_token('PP_LIST','EX, ES, FE or ZZ');
2252 hr_utility.raise_error;
2253 end if;
2254 ELSIF ( p_effective_date < to_date('12/04/2009', 'dd/mm/yyyy') ) THEN
2255 if (
2256 ( p_to_pay_plan in ('EX','ZZ','ES','FE') or
2257 substr(p_to_pay_plan,1,1) in ('B','X') or
2258 (substr(p_to_pay_plan,1,1) in ('W') and p_to_pay_plan not in ('WM') )
2259 ) and
2260 p_pay_rate_determinant_code not in ('A','B','E','F','S') and
2261 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0)
2262 ) then
2263 GHR_GHRWS52L.CPDF_Parameter_Check;
2264 hr_utility.set_message(8301,'GHR_37734_ALL_PROCEDURE_FAIL');
2265 hr_utility.set_message_token('PRD_LIST','A, B, E, F, or S');
2266 hr_utility.set_message_token('PP_LIST','EX, ES, FE or ZZ');
2267 hr_utility.raise_error;
2268 end if;
2269 ELSIF p_effective_date < fnd_date.canonical_to_date('2010/01/01') THEN
2270 if (
2271 ( p_to_pay_plan in ('EX','ZZ','ES','FE','SL','ST') or
2272 substr(p_to_pay_plan,1,1) in ('B','X') or
2273 (substr(p_to_pay_plan,1,1) in ('W') and p_to_pay_plan not in ('WM') )
2274 ) and
2275 p_pay_rate_determinant_code not in ('A','B','E','F','S') and
2276 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0)
2277 ) then
2278 GHR_GHRWS52L.CPDF_Parameter_Check;
2279 hr_utility.set_message(8301,'GHR_37734_ALL_PROCEDURE_FAIL');
2280 hr_utility.set_message_token('PRD_LIST','A, B, E, F, or S');
2281 hr_utility.set_message_token('PP_LIST','EX, ES, FE, SL, ST or ZZ');
2282 hr_utility.raise_error;
2283 end if;
2284 ELSE
2285 if (
2289 p_pay_rate_determinant_code not in ('A','B','E','F','S') and
2286 ( p_to_pay_plan in ('EX','ZZ','ES','FE','SL','ST') or
2287 substr(p_to_pay_plan,1,1) in ('B','X') or
2288 (substr(p_to_pay_plan,1,1) in ('W') and p_to_pay_plan not in ('WM') )) and
2290 p_locality_pay_area NOT IN ('AK','HI','ON') AND
2291 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0)
2292 ) then
2293 GHR_GHRWS52L.CPDF_Parameter_Check;
2294 hr_utility.set_message(8301,'GHR_37760_ALL_PROCEDURE_FAIL');
2295 hr_utility.set_message_token('PRD_LIST','A, B, E, F, or S');
2296 hr_utility.set_message_token('PP_LIST','EX, ES, FE, SL, ST or ZZ');
2297 hr_utility.raise_error;
2298 end if;
2299 END IF;
2300
2301 -- 652.35.3
2302 -- upd49 19-Jan-07 Raju From 01-Apr-2004 Bug#5619873 Terminate the Edit
2303 IF p_effective_date < to_date('01/04/2004', 'dd/mm/yyyy') THEN
2304 if (
2305 p_pay_rate_determinant_code = 'C' and
2306 (p_to_locality_adj is not null and to_number(p_to_locality_adj) <> 0)
2307 ) then
2308 GHR_GHRWS52L.CPDF_Parameter_Check;
2309 hr_utility.set_message(8301,'GHR_37735_ALL_PROCEDURE_FAIL');
2310 hr_utility.raise_error;
2311 end if;
2312 END IF;
2313 -- 652.45.1
2314 -- 12/8/00 vravikan From the Start Remove GH and GM
2315 -- 19/03/04 Madhuri Raise edit error only if the NOAC is not 817
2316 --
2317 -- Modified EDIT for Student Loan Repayment
2318 -- The to Basic Pay will be NULL for 817 action, so this edit will be fired
2319 -- Checking for NOAC 817 before firing edit
2320 -------------------------------------------------------------------------------
2321 --- Modified by Date Comments
2322 -------------------------------------------------------------------------------
2323 --- Madhuri 01-MAR-05 Change the condition 'And locality pay area is 02 through 88'
2324 --- To:'And locality pay area is other than ZY or ZZ' as of 09-JAN-05
2325 -- Raju 08-Nov-2005 UPD 42(Bug 4567571) Delete PRD U and V as of 01-May-2005
2326 -- amrchakr 28-sep-2006 Change the edit no. from 652.45.1 to 652.45.3
2327 -- And remove the lacality pay area 'ZY' effective 01-jul-2006
2328 -- Raju 24-Jan-2007 Change the edit no. from 652.45.3 to 652.45.1 eff from 01-Sep-2006
2329 -- UPD 56 8309414 Raju From start Remove PRD E and F
2330 -- U58 Raju 01-jan-2010 Bug 9503972
2331 -------------------------------------------------------------------------------
2332 IF ( p_effective_date < to_date('09/01/2005', 'dd/mm/yyyy') ) THEN
2333 if ( p_retained_pay_plan in ('GS','GG') and
2334 p_pay_rate_determinant_code in ('A','B','U','V') and
2335 p_locality_pay_area between '02' and '88' and
2336 (p_to_locality_adj is null) ) and
2337 ( p_first_noac_lookup_code <> '817' )
2338 then
2339 GHR_GHRWS52L.CPDF_Parameter_Check;
2340 hr_utility.set_message(8301,'GHR_37736_ALL_PROCEDURE_FAIL');
2341 hr_utility.raise_error;
2342 end if;
2343
2344 ELSIF ( p_effective_date < to_date('01/05/2005', 'dd/mm/yyyy') ) THEN
2345 if (p_retained_pay_plan in ('GS','GG') and
2346 p_pay_rate_determinant_code in ('A','B','U','V') and
2347 p_locality_pay_area NOT IN ('ZY','ZZ') and
2348 (p_to_locality_adj is null)) and
2349 ( p_first_noac_lookup_code <> '817' )
2350 then
2351 GHR_GHRWS52L.CPDF_Parameter_Check;
2352 hr_utility.set_message(8301,'GHR_38933_ALL_PROCEDURE_FAIL');
2353 hr_utility.set_message_token('PRD_LIST','A, B, U, or V');
2354 hr_utility.raise_error;
2355 end if;
2356
2357 ELSIF ( p_effective_date >= to_date('01/05/2005', 'dd/mm/yyyy') ) AND
2358 ( p_effective_date < to_date('01/07/2006', 'dd/mm/yyyy') ) THEN
2359 if ( p_retained_pay_plan in ('GS','GG') and
2360 p_pay_rate_determinant_code in ('A','B') and
2361 p_locality_pay_area NOT IN ('ZY','ZZ') and
2362 (p_to_locality_adj is null))
2363 and ( p_first_noac_lookup_code <> '817' ) then
2364 GHR_GHRWS52L.CPDF_Parameter_Check;
2365 hr_utility.set_message(8301,'GHR_38933_ALL_PROCEDURE_FAIL');
2366 hr_utility.set_message_token('PRD_LIST','A or B');
2367 hr_utility.raise_error;
2368 end if;
2369
2370 ELSIF ( p_effective_date >= to_date('01/07/2006', 'dd/mm/yyyy') ) AND
2371 p_effective_date < fnd_date.canonical_to_date('2010/01/01') THEN
2372 if ( p_retained_pay_plan in ('GS','GG') and
2373 p_pay_rate_determinant_code in ('A','B') and
2374 p_locality_pay_area NOT IN ('ZZ') and
2375 (p_to_locality_adj is null)) and
2376 ( p_first_noac_lookup_code <> '817' )
2377 then
2378 GHR_GHRWS52L.CPDF_Parameter_Check;
2379 if ( p_effective_date < to_date('01/09/2006', 'dd/mm/yyyy') ) THEN
2380 hr_utility.set_message(8301,'GHR_37696_ALL_PROCEDURE_FAIL');
2381 hr_utility.raise_error;
2382 else
2383 hr_utility.set_message(8301,'GHR_37434_ALL_PROCEDURE_FAIL');
2384 hr_utility.set_message_token('LOC_AREA','ZZ');
2385 hr_utility.raise_error;
2386 end if;
2387 end if;
2388 ELSE
2389 if ( p_retained_pay_plan in ('GS','GG') and
2390 p_pay_rate_determinant_code in ('A','B') and
2391 p_locality_pay_area NOT IN ('AK','HI','ON','ZZ') and
2392 (p_to_locality_adj is null)) and
2393 ( p_first_noac_lookup_code <> '817' )
2394 then
2395 GHR_GHRWS52L.CPDF_Parameter_Check;
2396 hr_utility.set_message(8301,'GHR_37434_ALL_PROCEDURE_FAIL');
2397 hr_utility.set_message_token('LOC_AREA','AK, HI, ON, ZZ');
2398 hr_utility.raise_error;
2399 end if;
2400 END IF;
2401 -------------------------------------------------------------------------------
2402
2403 -- 652.60.1
2404 /* 'p_as_of_date' has been determined to be effective date. */
2405 -- NAME EFFECTIVE COMMENTS
2406 -- Madhuri 21-JAN-2004 End Dating this edit as on 10-JAN-04
2410 -- end dating the edit as on 10-JAN-2004
2407 -- For SES Pay Calculations
2408 -- Madhuri 19-MAY-2004 Removing the pay plans ES, FE from the list
2409 --
2411 --
2412 --- Bug # 8320557 Removed SL and ST pay plans
2413 -------------------------------------------------------------------------------
2414 --- Modified by Date Comments
2415 -------------------------------------------------------------------------------
2416 --- Madhuri 01-MAR-05 Change the condition 'And locality pay area is 02 through 88'
2417 --- To:'And locality pay area is other than ZY or ZZ' as of 09-JAN-05
2418 --- NOT MODIFYING THIS EDIT as THE EDIT IS END DATED AS OF 11 JAN 04
2419
2420 -------------------------------------------------------------------------------
2421 if ( p_effective_date < to_date('2004/01/11', 'yyyy/mm/dd') and
2422 p_to_pay_plan in ('AL','CA') and
2423 p_locality_pay_area between '02' and '88' and
2424 (p_to_locality_adj is not null or to_number(p_to_locality_adj) <> 0) and
2425 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> 0)
2426 ) then
2427 if (
2428 l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
2429 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
2430 ) then
2431 GHR_GHRWS52L.CPDF_Parameter_Check;
2432 hr_utility.set_message(8301,'GHR_37737_ALL_PROCEDURE_FAIL');
2433 hr_utility.raise_error;
2434 end if;
2435 end if;
2436 -------------------------------------------------------------------------------
2437
2438 -- 652.60.2
2439 /* 'p_as_of_date' has been detemined to be effective date. */
2440 -- Madhuri 21-JAN-2004 End Dating this edit as on 10-JAN-04
2441 -- For SES Pay Calculations
2442 -- Madhuri 19-MAY-2004 Removing the pay plans ES, FE from the list
2443 --
2444 -- end dating the edit as on 10-JAN-2004
2445 --- Bug # 8320557 Removed SL and ST pay plans
2446 -------------------------------------------------------------------------------
2447 --- Modified by Date Comments
2448 -------------------------------------------------------------------------------
2449 --- Madhuri 01-MAR-05 Change the condition 'And locality pay area is 02 through 88'
2450 --- To:'And locality pay area is other than ZY or ZZ' as of 09-JAN-05
2451 --- NOT MODIFYING THIS EDIT as THE EDIT IS END DATED AS OF 11 JAN 04
2452 -------------------------------------------------------------------------------
2453 if (p_effective_date < to_date('2004/01/11', 'yyyy/mm/dd') and
2454 p_to_pay_plan in ('AL','CA') and
2455 p_locality_pay_area between '02' and '88' and
2456 (p_to_locality_adj is not null or to_number(p_to_locality_adj) <> 0) and
2457 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> 0)
2458 )
2459 then
2460 if (
2461 l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
2462 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
2463 ) then
2464 GHR_GHRWS52L.CPDF_Parameter_Check;
2465 hr_utility.set_message(8301,'GHR_37749_ALL_PROCEDURE_FAIL');
2466 hr_utility.raise_error;
2467 end if;
2468 end if;
2469 -------------------------------------------------------------------------------
2470 -- 652.75.1
2471 -- 'p_as_of_date' has been determined to be effective date.
2472 /*
2473 --
2474 -- Commented the edit for bug 557188 on 7-aug-98
2475 --
2476 if (
2477 p_to_pay_plan not in ('GS','GM') and
2478 p_pay_rate_determinant_code in ('A','B','E','F','U','V') and
2479 p_retained_pay_plan not in ('GS', 'GM') and
2480 p_locality_pay_area between '02' and '88' and
2481 (p_to_locality_adj is not null or to_number(p_to_locality_adj) <> 0) and
2482 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> 0)
2483 )
2484 then
2485 if (
2486 l_lpa_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2) and
2487 l_leo_pct < round((to_number(p_to_locality_adj)/to_number(p_to_basic_pay)*100),2)
2488 ) then
2489 GHR_GHRWS52L.CPDF_Parameter_Check;
2490 hr_utility.set_message(8301,'GHR_37738_ALL_PROCEDURE_FAIL');
2491 hr_utility.raise_error;
2492 end if;
2493 end if;
2494 */
2495
2496 -- 652.80.1
2497 -------------------------------------------------------------------------------
2498 --- Modified by Date Comments
2499 -------------------------------------------------------------------------------
2500 --- Madhuri 01-MAR-05 FROM: If the Locality pay area is 98,And pay rate determinant is 0, 3, 7, J, or K,
2501 --- Then the amount of the locality adjustment, as a percentage of basic pay, must fall within the range of the highest and lowest percentages on Table 25 for locality pay area 98, or be spaces or asterisks.
2502 --- TO: If the locality pay area is ZY, And pay rate determinant is 0, 3, 7, J, or K,
2503 --- Then the amount of the locality adjustment, as a percentage of basic pay, must fall within the range of the highest and lowest percentages on Table 25 for locality pay area ZY, or be spaces or asterisks.
2504 -- Raju 08-Nov-2005 UPD 42(Bug 4567571) Delete PRD 3,J and K as of 01-May-2005
2505 -- amrchakr 29-sep-2006 End dated the edit fron 01-jul-2006
2506 -------------------------------------------------------------------------------
2507 IF ( p_effective_date < to_date('09/01/2005', 'dd/mm/yyyy') ) THEN
2508 if (
2509 p_locality_pay_area = '98' and
2510 p_pay_rate_determinant_code in ('0','3','7','J','K') and
2511 p_to_basic_pay is not null
2512 ) then
2513 if (
2514 round(((nvl(to_number(p_to_locality_adj),0)/to_number(p_to_basic_pay))*100),2)
2515 not between l_lpa_pct and l_leo_pct
2516 ) then
2517 GHR_GHRWS52L.CPDF_Parameter_Check;
2518 hr_utility.set_message(8301,'GHR_37759_ALL_PROCEDURE_FAIL');
2522 ELSIF ( p_effective_date < to_date('01/05/2005', 'dd/mm/yyyy') ) THEN
2519 hr_utility.raise_error;
2520 end if;
2521 end if;
2523 if (
2524 p_locality_pay_area = 'ZY' and
2525 p_pay_rate_determinant_code in ('0','3','7','J','K') and
2526 p_to_basic_pay is not null
2527 ) then
2528 if (
2529 round(((nvl(to_number(p_to_locality_adj),0)/to_number(p_to_basic_pay))*100),2)
2530 not between l_lpa_pct and l_leo_pct
2531 ) then
2532 GHR_GHRWS52L.CPDF_Parameter_Check;
2533 hr_utility.set_message(8301,'GHR_38934_ALL_PROCEDURE_FAIL');
2534 hr_utility.set_message_token('PRD_LIST','0, 3, 7, J, or K');
2535 hr_utility.raise_error;
2536 end if;
2537 end if;
2538
2539 ELSIF ( p_effective_date >= to_date('01/05/2005', 'dd/mm/yyyy') )
2540 AND
2541 ( p_effective_date < to_date('01/07/2006', 'dd/mm/yyyy') )
2542 THEN
2543 if (
2544 p_locality_pay_area = 'ZY' and
2545 p_pay_rate_determinant_code in ('0','7') and
2546 p_to_basic_pay is not null
2547 ) then
2548 if (
2549 round(((nvl(to_number(p_to_locality_adj),0)/to_number(p_to_basic_pay))*100),2)
2550 not between l_lpa_pct and l_leo_pct
2551 ) then
2552 GHR_GHRWS52L.CPDF_Parameter_Check;
2553 hr_utility.set_message(8301,'GHR_38934_ALL_PROCEDURE_FAIL');
2554 hr_utility.set_message_token('PRD_LIST','0, or 7');
2555 hr_utility.raise_error;
2556 end if;
2557 end if;
2558 END IF;
2559 -------------------------------------------------------------------------------
2560 /*
2561 -- 652.95.2
2562 if ((p_to_pay_plan in ('GS','GM','GG','GH','FO','FP','FE','ES','ST','AL','CA','SL') and
2563 p_locality_pay_area <> '99' and
2564 p_pay_rate_determinant_code not in ('A','B','C','E','F','U','V') and
2565 p_effective_date >= fnd_date.canonical_to_date('1994/01/01')) and
2566 (
2567 substr(p_first_noac_lookup_code,1,1) <> '3' and
2568 substr(p_first_noac_lookup_code,1,1) <> '4'
2569 )
2570 and
2571 (p_to_locality_adj is null)
2572 ) then
2573 GHR_GHRWS52L.CPDF_Parameter_Check;
2574 hr_utility.set_message(8301,'GHR_37739_ALL_PROCEDURE_FAIL');
2575 hr_utility.raise_error;
2576 end if;
2577 */
2578 end chk_locality_adj;
2579 /* Name:
2580 -- PRIOR LOCALITY ADJUSTMENT
2581 */
2582 procedure chk_prior_locality_adj
2583 (p_to_pay_plan in varchar2
2584 ,p_to_basic_pay in varchar2
2585 ,p_prior_pay_plan in varchar2
2586 ,p_pay_rate_determinant_code in varchar2
2587 ,p_retained_pay_plan in varchar2
2588 ,p_prior_pay_rate_det_code in varchar2
2589 ,p_locality_pay_area in varchar2
2590 ,p_prior_locality_pay_area in varchar2
2591 ,p_prior_basic_pay in varchar2
2592 ,p_to_locality_adj in varchar2
2593 ,p_prior_locality_adj in varchar2
2594 ,p_prior_loc_adj_effective_date in date
2595 ,p_first_noac_lookup_code in varchar2
2596 ,p_as_of_date in date
2597 ,p_agency_subelement in varchar2
2598 ,p_prior_duty_station in varchar2
2599 ,p_effective_date in date
2600 ) is
2601
2602 l_lpa_pct ghr_locality_pay_areas_f.adjustment_percentage%type;
2603 l_leo_pct ghr_locality_pay_areas_f.leo_adjustment_percentage%type;
2604 l_lpa_effective_start_date pay_user_rows_f.effective_start_date%type;
2605 l_lpa_effective_end_date pay_user_rows_f.effective_end_date%type;
2606 l_table5_value number(10,2); --Bug# 8309414
2607
2608 CURSOR c1 is
2609 SELECT effective_start_date, effective_end_date,
2610 NVL(adjustment_percentage,0), NVL(leo_adjustment_percentage,0)
2611 FROM ghr_locality_pay_areas_f
2612 WHERE locality_pay_area_code = p_prior_locality_pay_area
2613 AND p_prior_loc_adj_effective_date BETWEEN effective_start_date and effective_end_date;
2614
2615 begin
2616 open c1;
2617 fetch c1 into l_lpa_effective_start_date, l_lpa_effective_end_date,
2618 l_lpa_pct, l_leo_pct;
2619 close c1;
2620 l_table5_value:= GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 5',
2621 '04' ,
2622 'Maximum Basic Pay',p_effective_date);
2623 --
2624 -- 27-jul-98
2625 -- The edits where the locality adj as a percentage of prior basic pay was being compared
2626 -- to the lpa_pct has been modified to include leo_pct.
2627 -- bug #703978
2628 --
2629
2630 /*653.08.2 If prior pay plan is GS, GM, GG, GH, FO, FP, FE,
2631 ES, ST, AL, CA, or SL,
2632 Then prior locality adjustment, as a percentage of
2633 prior basic pay, must not be greater than the highest
2634 percentage on Table 25 for the prior locality pay area.
2635
2636 Default: Insert asterisks in prior locality
2637 adjustment.
2638
2639 Basis for Edit: 5 U.S.C. 5304 */
2640 /*
2641 --
2642 -- Commented the edit for bug 557188 on 7-aug-98
2643 --
2644 if (
2645 nvl(p_prior_pay_plan,hr_api.g_varchar2) in ('GS','GM','GG','GH','FO','FP','FE','ES','ST','AL','CA','SL')
2646 ) then
2647
2648 if (p_prior_basic_pay is not null or to_number(p_prior_basic_pay) <> 0) then
2649 if (
2650 l_lpa_pct < round((nvl(to_number(p_prior_locality_adj),0)/to_number(p_prior_basic_pay))*100,2) and
2651 l_leo_pct < round((nvl(to_number(p_prior_locality_adj),0)/to_number(p_prior_basic_pay))*100,2)
2652 ) then
2653 GHR_GHRWS52L.CPDF_Parameter_Check;
2654 hr_utility.set_message(8301,'GHR_37741_ALL_PROCEDURE_FAIL');
2655 hr_utility.raise_error;
2656 end if;
2657 end if;
2658 end if;
2659 */
2660
2661 -- Dec. 2001 Patch Removed the NOAC check
2665 Then the amount of the prior locality adjustment, as a
2662 /*653.15.2 If prior pay plan = GS, GM, GG, GH, FO, or FP,
2663 And prior PRD = 0, 3, 7, J, or K,
2664 And prior locality pay area is not 99,
2666 percentage of prior basic pay, must match any
2667 percentage for the locality pay area on Table 25, valid
2668 as of the effective date or be spaces or asterisks.
2669
2670 Default: Insert asterisks in prior locality
2671 adjustment.
2672
2673 Basis for Edit: 5 U.S.C. 5304 */
2674 -------------------------------------------------------------------------------
2675 --- Modified by Date Comments
2676 -------------------------------------------------------------------------------
2677 --- Madhuri 01-MAR-05 FROM:'And prior locality pay area is 02 through 88'
2678 --- To: 'And prior locality pay area is other than ZY or ZZ' as of 09-Jan-05
2679 -- Raju 08-Nov-2005 UPD 42(Bug 4567571) Delete prior PRD 3,J and K as of 01-May-2005
2680 -- amrchakr 05-Oct-2006 Removed prior locality pay area 'ZY' effective from 01-jul-2006
2681 -- UPD 56 8309414 Raju From 13-Aug-07
2682 -- U58 Raju 01-jan-2010 Bug 9503972
2683 -------------------------------------------------------------------------------
2684 IF ( p_effective_date < to_date('09/01/2005', 'dd/mm/yyyy') ) THEN
2685 if (
2686 nvl(p_prior_pay_plan,hr_api.g_varchar2) in ('GS','GM','GG','GH','FO','FP') and
2687 nvl(p_prior_pay_rate_det_code,hr_api.g_varchar2) in ('0','3','7','J','K') and
2688 nvl(p_locality_pay_area,hr_api.g_varchar2) between '02' and '88' and
2689 (p_prior_locality_adj is not null or to_number(p_prior_locality_adj) <> 0) and
2690 (p_prior_basic_pay is not null or p_prior_basic_pay <> 0)
2691 ) then
2692 if (
2693 l_lpa_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2) and
2694 l_leo_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2)
2695 ) then
2696 GHR_GHRWS52L.CPDF_Parameter_Check;
2697 hr_utility.set_message(8301,'GHR_37742_ALL_PROCEDURE_FAIL');
2698 hr_utility.raise_error;
2699 end if;
2700 end if;
2701 ELSIF ( p_effective_date < to_date('01/05/2005', 'dd/mm/yyyy') ) THEN
2702 if (
2703 nvl(p_prior_pay_plan,hr_api.g_varchar2) in ('GS','GM','GG','GH','FO','FP') and
2704 nvl(p_prior_pay_rate_det_code,hr_api.g_varchar2) in ('0','3','7','J','K') and
2705 nvl(p_locality_pay_area,hr_api.g_varchar2) NOT IN ('ZY','ZZ') and
2706 (p_prior_locality_adj is not null or to_number(p_prior_locality_adj) <> 0) and
2707 (p_prior_basic_pay is not null or p_prior_basic_pay <> 0)
2708 ) then
2709 if (
2710 l_lpa_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2) and
2711 l_leo_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2)
2712 ) then
2713 GHR_GHRWS52L.CPDF_Parameter_Check;
2714 hr_utility.set_message(8301,'GHR_38935_ALL_PROCEDURE_FAIL'); -- NEW MESG TOKEN REQD
2715 hr_utility.set_message_token('PRD_LIST','0, 3, 7, J, or K');
2716 hr_utility.raise_error;
2717 end if;
2718 end if;
2719
2720 ELSIF ( p_effective_date >= to_date('01/05/2005', 'dd/mm/yyyy')
2721 AND
2722 p_effective_date < to_date('01/07/2006', 'dd/mm/yyyy')
2723 ) THEN
2724 if (
2725 nvl(p_prior_pay_plan,hr_api.g_varchar2) in ('GS','GM','GG','GH','FO','FP') and
2726 nvl(p_prior_pay_rate_det_code,hr_api.g_varchar2) in ('0','7') and
2727 nvl(p_locality_pay_area,hr_api.g_varchar2) NOT IN ('ZY','ZZ') and
2728 (p_prior_locality_adj is not null or to_number(p_prior_locality_adj) <> 0) and
2729 (p_prior_basic_pay is not null or p_prior_basic_pay <> 0)
2730 ) then
2731 if (
2732 l_lpa_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2) and
2733 l_leo_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2)
2734 ) then
2735 GHR_GHRWS52L.CPDF_Parameter_Check;
2736 hr_utility.set_message(8301,'GHR_38935_ALL_PROCEDURE_FAIL');
2737 hr_utility.set_message_token('PRD_LIST','0, or 7');
2738 hr_utility.raise_error;
2739 end if;
2740 end if;
2741
2742 ELSIF ( p_effective_date >= to_date('01/07/2006', 'dd/mm/yyyy') )
2743 AND( p_effective_date < to_date('13/08/2007', 'dd/mm/yyyy')) THEN
2744 if (
2745 nvl(p_prior_pay_plan,hr_api.g_varchar2) in ('GS','GM','GG','GH','FO','FP') and
2746 nvl(p_prior_pay_rate_det_code,hr_api.g_varchar2) in ('0','7') and
2747 nvl(p_locality_pay_area,hr_api.g_varchar2) NOT IN ('ZZ') and
2748 (p_prior_locality_adj is not null or to_number(p_prior_locality_adj) <> 0) and
2749 (p_prior_basic_pay is not null or p_prior_basic_pay <> 0)
2750 ) then
2751 if (
2752 l_lpa_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2) and
2753 l_leo_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2)
2754 ) then
2755 GHR_GHRWS52L.CPDF_Parameter_Check;
2756 hr_utility.set_message(8301,'GHR_37697_ALL_PROCEDURE_FAIL');
2757 hr_utility.raise_error;
2758 end if;
2759 end if;
2760 ELSIF p_effective_date < fnd_date.canonical_to_date('2010/01/01') THEN
2761 if (nvl(p_prior_pay_plan,hr_api.g_varchar2) in ('GS','GM','GG','GH','GL','FO','FP') and
2762 nvl(p_prior_pay_rate_det_code,hr_api.g_varchar2) in ('0','7') and
2763 nvl(p_locality_pay_area,hr_api.g_varchar2) NOT IN ('ZZ') and
2764 (p_prior_locality_adj is not null or to_number(p_prior_locality_adj) <> 0) and
2765 (p_prior_basic_pay is not null or p_prior_basic_pay <> 0)
2766 ) then
2767
2768 if ((l_lpa_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2) and
2769 l_leo_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2)) OR
2770 -- (round(to_number(p_to_locality_adj)+to_number(p_to_basic_pay)) > l_table5_value)
2774 GHR_GHRWS52L.CPDF_Parameter_Check;
2771 --Bug 8490941 need to consider the prior locality and basic for comparison with EX04 rate of Table5
2772 (round(to_number(p_prior_locality_adj)+to_number(p_prior_basic_pay)) > l_table5_value)
2773 ) then
2775 hr_utility.set_message(8301,'GHR_37458_ALL_PROCEDURE_FAIL');
2776 hr_utility.set_message_token('LOC_AREA','ZZ');
2777 hr_utility.raise_error;
2778 end if;
2779 end if;
2780 ELSE
2781 if (nvl(p_prior_pay_plan,hr_api.g_varchar2) in ('GS','GM','GG','GH','GL','FO','FP') and
2782 nvl(p_prior_pay_rate_det_code,hr_api.g_varchar2) in ('0','7') and
2783 nvl(p_locality_pay_area,hr_api.g_varchar2) NOT IN ('AK','HI','ON','ZZ') and
2784 (p_prior_locality_adj is not null or to_number(p_prior_locality_adj) <> 0) and
2785 (p_prior_basic_pay is not null or p_prior_basic_pay <> 0)
2786 ) then
2787
2788 if ((l_lpa_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2) and
2789 l_leo_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2)) OR
2790 -- (round(to_number(p_to_locality_adj)+to_number(p_to_basic_pay)) > l_table5_value)
2791 --Bug 8490941 need to consider the prior locality and basic for comparison with EX04 rate of Table5
2792 (round(to_number(p_prior_locality_adj)+to_number(p_prior_basic_pay)) > l_table5_value)
2793 ) then
2794 GHR_GHRWS52L.CPDF_Parameter_Check;
2795 hr_utility.set_message(8301,'GHR_37458_ALL_PROCEDURE_FAIL');
2796 hr_utility.set_message_token('LOC_AREA','AK, HI, ON, ZZ');
2797 hr_utility.raise_error;
2798 end if;
2799 end if;
2800 END IF;
2801 -------------------------------------------------------------------------------
2802
2803 /*653.16.2 If prior pay plan is FO, FP, GG, GH, GM, or GS,
2804 And prior pay rate determinant is 5, 6, or M,
2805 And prior locality pay area is 02 through 88,
2806 Then the prior locality adjustment may not be spaces or
2807 an amount greater than the highest percentage for the
2808 locality pay area on Table 25
2809 */
2810 -------------------------------------------------------------------------------
2811 --- Modified by Date Comments
2812 -------------------------------------------------------------------------------
2813 --- Madhuri 01-MAR-05 Change the condition 'And locality pay area is 02 through 88'
2814 --- To:'And locality pay area is other than ZY or ZZ' as of 09-JAN-05
2815 --- Narasimha Rao 21-SEP-05 Modified as a part of FWFA Changes. Restricted
2816 --- the edit to actions processed before 01-MAY-2005.
2817 --- For actions processed on or after 01-MAY-2005,
2818 --- this edit will be skipped.
2819 -- utokachi 10/21/05 Validate for actions after 01-MAY-2005
2820 -- Raju 10/26/05 Commented for Bug# 4699444
2821 -------------------------------------------------------------------------------
2822 /*
2823 IF p_effective_date >= to_date('2000/10/01', 'yyyy/mm/dd') THEN
2824
2825 -- check for 09-JAN-05 starts here
2826 IF ( p_effective_date < to_date('09/01/2005', 'dd/mm/yyyy') ) THEN
2827
2828 if ( nvl(p_prior_pay_plan,hr_api.g_varchar2) in ('GS','GM','GG','GH','FO','FP') and
2829 nvl(p_prior_pay_rate_det_code,hr_api.g_varchar2) in ('5','6','M') and
2830 nvl(p_locality_pay_area,hr_api.g_varchar2) between '02' and '88' and
2831 (p_prior_locality_adj is not null or to_number(p_prior_locality_adj) <> 0) and
2832 (p_prior_basic_pay is not null or p_prior_basic_pay <> 0)
2833 ) then
2834
2835 if (
2836 l_lpa_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2) and
2837 l_leo_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2)
2838 ) then
2839 GHR_GHRWS52L.CPDF_Parameter_Check;
2840 hr_utility.set_message(8301,'GHR_37680_ALL_PROCEDURE_FAIL');
2841 hr_utility.raise_error;
2842 end if;
2843 end if;
2844 -- FWFA Changes Bug#4444609
2845 ELSIF p_effective_date < to_date('2005/05/01','yyyy/mm/dd') THEN
2846
2847 if ( nvl(p_prior_pay_plan,hr_api.g_varchar2) in ('GS','GM','GG','GH','FO','FP') and
2848 nvl(p_prior_pay_rate_det_code,hr_api.g_varchar2) in ('5','6','M') and
2849 nvl(p_locality_pay_area,hr_api.g_varchar2) NOT IN ('ZY','ZZ') and
2850 (p_prior_locality_adj is not null or to_number(p_prior_locality_adj) <> 0) and
2851 (p_prior_basic_pay is not null or p_prior_basic_pay <> 0)
2852 ) then
2853 if (
2854 l_lpa_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2) and
2855 l_leo_pct < round((to_number(p_prior_locality_adj)/to_number(p_prior_basic_pay)*100),2)
2856 ) then
2857 GHR_GHRWS52L.CPDF_Parameter_Check;
2858 hr_utility.set_message(8301,'GHR_38936_ALL_PROCEDURE_FAIL');
2859 hr_utility.raise_error;
2860 end if;
2861 end if;
2862 ELSE
2863 if ( nvl(p_prior_pay_plan,hr_api.g_varchar2) in ('GS','GM','GG','GH') and
2864 nvl(p_prior_pay_rate_det_code,hr_api.g_varchar2) in ('5','6')
2865 ) then
2866 if to_number(p_prior_locality_adj) <= 0 then
2867 GHR_GHRWS52L.CPDF_Parameter_Check;
2868 hr_utility.set_message(8301,'GHR_38979_ALL_PROCEDURE_FAIL');
2869 hr_utility.raise_error;
2870 end if;
2871 end if;
2872 END IF; -- check for 09-JAN-05 ends here
2873
2874 END IF; -- only if eff date is greater than 01-OCT-2000
2875 -- FWFA Changes
2876 */
2877 -------------------------------------------------------------------------------
2878 /* p_prior_locality_pay_area has been activated. */
2879
2880 -- 653.30.2
2881 -- Update Date By Comments
2882 -- 21/2 20-Feb-2003 Madhuri Adding HS to other than list
2883 -- 30-OCT-2003 Ashley Commented the second condition(Agency subelement cond.)
2884 -------------------------------------------------------------------------------
2888 --- To:'If prior locality pay area is ZZ' as of 09-JAN-05
2885 --- Modified by Date Comments
2886 -------------------------------------------------------------------------------
2887 --- Madhuri 01-MAR-05 Change the condition 'If prior locality pay area is 99'
2889 --upd47 26-Jun-06 Raju From 01-Apr-2006 Added prior pay plan condition
2890 -------------------------------------------------------------------------------
2891 IF p_effective_date < fnd_date.canonical_to_date('1998/09/01') THEN
2892 if (p_prior_locality_pay_area = '99' and
2893 (p_prior_locality_adj is not null and to_number(p_prior_locality_adj) <> 0)) then
2894 GHR_GHRWS52L.CPDF_Parameter_Check;
2895 hr_utility.set_message(8301,'GHR_37743_ALL_PROCEDURE_FAIL');
2896 hr_utility.raise_error;
2897 end if;
2898 ELSE
2899 -- between 01-SEP-98 to 09-JAN-05
2900 IF ( p_effective_date < to_date('09/01/2005', 'dd/mm/yyyy') ) THEN
2901 -- If prior locality pay area is 99,
2902 -- And agency is other than AM, GY, HS, IB, or ST
2903 -- Then prior locality adjustment must be spaces or asterisks.
2904 if p_prior_locality_pay_area = '99' and
2905 p_prior_locality_adj is not null and
2906 to_number(p_prior_locality_adj) <> 0
2907 then
2908 GHR_GHRWS52L.CPDF_Parameter_Check;
2909 hr_utility.set_message(8301,'GHR_37898_ALL_PROCEDURE_FAIL');
2910 hr_utility.raise_error;
2911 end if;
2912 -- FWFA Changes
2913 ELSIF p_effective_date < to_date('2005/05/01','YYYY/MM/DD') THEN
2914 -- If prior locality pay area is 99,
2915 -- And agency is other than AM, GY, HS, IB, or ST
2916 -- Then prior locality adjustment must be spaces or asterisks.
2917 if p_prior_locality_pay_area = 'ZZ' and
2918 p_prior_locality_adj is not null and
2919 to_number(p_prior_locality_adj) <> 0
2920 then
2921 GHR_GHRWS52L.CPDF_Parameter_Check;
2922 hr_utility.set_message(8301,'GHR_38937_ALL_PROCEDURE_FAIL');
2923 hr_utility.raise_error;
2924 end if;
2925 ELSif p_effective_date < fnd_date.canonical_to_date('2006/04/01') then
2926 if p_prior_locality_pay_area = 'ZZ' and
2927 p_prior_pay_rate_det_code not in ('5','6','E','F') and
2928 p_prior_locality_adj is not null and
2929 to_number(p_prior_locality_adj) <> 0
2930 then
2931 GHR_GHRWS52L.CPDF_Parameter_Check;
2932 hr_utility.set_message(8301,'GHR_38980_ALL_PROCEDURE_FAIL');
2933 hr_utility.raise_error;
2934 end if;
2935 ELSE
2936 if substr(p_agency_subelement,1,2) <> 'HS' and
2937 p_prior_locality_pay_area = 'ZZ' and
2938 p_prior_pay_rate_det_code not in ('5','6','E','F') and
2939 p_prior_pay_plan in ('GM','GS') and
2940 p_prior_locality_adj is not null and
2941 to_number(p_prior_locality_adj) <> 0
2942 then
2943 GHR_GHRWS52L.CPDF_Parameter_Check;
2944 hr_utility.set_message(8301,'GHR_37162_ALL_PROCEDURE_FAIL');
2945 hr_utility.raise_error;
2946 end if;
2947 END IF;
2948 -- FWFA Changes
2949 END IF;
2950 -------------------------------------------------------------------------------
2951 --Commented as per EOY 2003 cpdf changes by Ashley
2952 -- 653.31.2
2953 -- Update Date By Comments
2954 -- 21/2 20-Feb-2003 Madhuri Adding HS to the list
2955
2956 -- If prior locality pay area is 99,
2957 -- And agency is AM, GY, HS, IB, or ST,
2958 -- And either:
2959 -- The first two positions of prior duty station are other
2960 -- than CA or MX,
2961 -- Or prior pay plan is FO or FP,
2962 -- Then prior locality adjustment must be spaces or asterisks.
2963
2964 /* if p_effective_date >= fnd_date.canonical_to_date('1998/09/01') then
2965 if p_prior_locality_pay_area = '99' and
2966 substr(p_agency_subelement,1,2) in ('AM','GY','HS','IB','ST') and
2967 (
2968 substr(p_prior_duty_station,1,2) not in ('CA','MX') or
2969 p_prior_pay_plan in ('FO','FP')
2970 ) and
2971 (p_prior_locality_adj is not null and to_number(p_prior_locality_adj) <> 0) then
2972 hr_utility.set_message(8301,'GHR_37899_ALL_PROCEDURE_FAIL');
2973 hr_utility.raise_error;
2974 end if;
2975 end if;
2976 */
2977
2978 -- 653.40.2
2979 -- Update Date By Effective Date Comment
2980 -- 9 04/05/99 vravikan All pay plans starting with W but not 'WM'
2981 -- 10/21/05 utokachi Validate for actions before to 01-MAY-2005
2982 -- 08-Nov-05 Raju UPD 42(Bug 4567571) Delete Prior PRD U and V as of 01-May-2005
2983 -- 14-Nov-05 Raju UPD 45(Bug 4567571) Terminate the Edit effective 01-Sep-2005
2984 -- 20-Dec-05 Raju Bug# 4879781 Terminate the Edit effective 01-May-2005
2985 -- Begin Fix for Bug#4879781
2986 --IF p_effective_date < to_date('2005/09/01','YYYY/MM/DD') THEN
2987 -- End Fix for Bug#4879781
2988 --FWFA Changes
2989 IF p_effective_date < to_date('2005/05/01','YYYY/MM/DD') THEN
2990 if (
2991 (substr(p_prior_pay_plan,1,1) in ('B','X') or
2992 (substr(p_prior_pay_plan,1,1) in ('W') and p_prior_pay_plan not in ('WM') ) or
2993 p_prior_pay_plan in ('EH','EI','EX','ZZ')) and
2994 p_prior_pay_rate_det_code not in ('A','B','E','F','S','U','V') and
2995 (p_prior_locality_adj is not null and to_number(p_prior_locality_adj) <> 0)
2996 ) then
2997 GHR_GHRWS52L.CPDF_Parameter_Check;
2998 hr_utility.set_message(8301,'GHR_37744_ALL_PROCEDURE_FAIL');
2999 hr_utility.set_message_token('PRD_LIST','A, B, E, F, S, U, or V');
3000 hr_utility.raise_error;
3001 end if;
3002 -- Begin Fix for Bug#4879781
3003 /*ELSIF p_effective_date >= to_date('2005/05/01','YYYY/MM/DD') THEN
3004 if (
3005 (substr(p_prior_pay_plan,1,1) in ('B','X') or
3006 (substr(p_prior_pay_plan,1,1) in ('W') and p_prior_pay_plan not in ('WM') ) or
3007 p_prior_pay_plan in ('EH','EI','EX','ZZ')) and
3008 p_prior_pay_rate_det_code not in ('A','B','E','F','S') and
3009 (p_prior_locality_adj is not null and to_number(p_prior_locality_adj) <> 0)
3010 ) then
3011 GHR_GHRWS52L.CPDF_Parameter_Check;
3012 hr_utility.set_message(8301,'GHR_37744_ALL_PROCEDURE_FAIL');
3013 hr_utility.set_message_token('PRD_LIST','A, B, E, F, or S');
3014 hr_utility.raise_error;
3015 end if;
3016 END IF;*/
3017 --FWFA Changes
3018 -- End Fix for Bug#4879781
3019 end if;
3020
3021
3022 -- 653.45.2
3023 if (
3024 p_prior_pay_rate_det_code = 'C' and
3025 (p_prior_locality_adj is not null and to_number(p_prior_locality_adj) <> 0)
3026
3027 ) then
3028 GHR_GHRWS52L.CPDF_Parameter_Check;
3029 hr_utility.set_message(8301,'GHR_37745_ALL_PROCEDURE_FAIL');
3030 hr_utility.raise_error;
3031 end if;
3032
3033 -- 653.50.2
3034
3035 -- Update Date By Effective Date Comment
3036 -- U58 20/04/10 Raju 01/01/2010 Bug 9503972
3037
3038 IF p_effective_date >= fnd_date.canonical_to_date('2010/01/01') THEN
3039 if (nvl(p_prior_pay_plan,hr_api.g_varchar2) in ('ES','EX','FE','SL','ST','ZZ') AND
3040 p_prior_pay_rate_det_code NOT IN ('A','B','E','F','S') and
3041 p_prior_locality_pay_area NOT IN ('AK','HI','ON') AND
3042 (p_prior_locality_adj is not null and to_number(p_prior_locality_adj) <> 0)) then
3043
3044 GHR_GHRWS52L.CPDF_Parameter_Check;
3045 hr_utility.set_message(8301,'GHR_37757_ALL_PROCEDURE_FAIL');
3046 hr_utility.raise_error;
3047 end if;
3048 END IF;
3049 hr_utility.set_location('Leaving CPDF 7 ',1);
3050 end chk_prior_locality_adj;
3051 end GHR_CPDF_CHECK7;
[Home] [Help]