1 package body GHR_CPDF_CHECK8 as
2 /* $Header: ghcpdf08.pkb 120.7.12010000.7 2010/01/08 06:51:36 utokachi ship $ */
3
4 min_basic_pay number(10,2);
5 max_basic_pay number(10,2);
6
7 FUNCTION get_pay_table_value (
8 p_effective_date IN DATE
9 , p_pay_plan IN VARCHAR2
10 , p_grade_or_level IN VARCHAR2
11 , p_step_or_rate IN VARCHAR2
12 )
13 RETURN NUMBER
14 IS
15 CURSOR pay_table IS
16 SELECT
17 b.value
18 FROM pay_user_rows_f a
19 , pay_user_column_instances_f b
20 , pay_user_columns c
21 , pay_user_tables d
22 WHERE TRUNC(p_effective_date) BETWEEN b.effective_start_date AND b.effective_end_date
23 AND TRUNC(p_effective_date) BETWEEN a.effective_start_date AND a.effective_end_date
24 AND a.row_low_range_or_name = p_pay_plan ||'-'|| p_grade_or_level
25 AND c.user_column_name = p_step_or_rate
26 AND d.user_table_id = c.user_table_id
27 AND c.user_column_id = b.user_column_id
28 AND d.user_table_id = a.user_table_id
29 AND a.user_row_id = b.user_row_id
30 AND d.user_table_name = '0000 Oracle Federal Standard Pay Table (AL, ES, EX, GS, GG) No. 0000';
31 l_value NUMBER;
32 BEGIN
33 OPEN pay_table;
34 FETCH pay_table into l_value;
35 CLOSE pay_table;
36 RETURN l_value;
37 END get_pay_table_value;
38
39 procedure basic_pay
40 (p_to_pay_plan in varchar2
41 ,p_rate_determinant_code in varchar2
42 ,p_to_basic_pay in varchar2
43 ,p_retained_pay_plan in varchar2 /* Non-SF52 Data Item */
44 ,p_retained_grade in varchar2 /* Non-SF52 Data Item */
45 ,p_retained_step in varchar2 /* Non-SF52 Data Item */
46 ,p_agency_subelement in varchar2 /* Non-SF52 Data Item */
47 ,p_to_grade_or_level in varchar2
48 ,p_to_step_or_rate in varchar2
49 ,p_to_pay_basis in varchar2
50 ,p_first_action_noa_la_code1 in varchar2
51 ,p_first_action_noa_la_code2 in varchar2
52 ,p_first_noac_lookup_code in varchar2
53 ,p_effective_date in date
54 ,p_occupation_code in varchar2
55 ) is
56 l_table_pay pay_user_column_instances_f.value%type;
57 l_effective_date date;
58 l_pay_plan ghr_pay_plans.pay_plan%type;
59 begin
60 -- 650.02.3
61 -- update date By Comments
62 -- 20-Feb-2003 Madhuri Renumbered edit as 650.02.3, previously 650.04.1 and 650.02.2
63
64 IF p_first_noac_lookup_code = '866' THEN
65 l_effective_date := p_effective_date + 1;
66 END IF;
67
68 if p_to_grade_or_level is not null
69 and p_to_step_or_rate is not null
70 and p_to_pay_plan in ('GS' ,'VP')
71 and p_rate_determinant_code in ('0', '7')
72 and p_to_grade_or_level between '01' and '15'
73 and p_to_step_or_rate between '01' and '10'
74 then
75 l_pay_plan := p_to_pay_plan;
76 l_table_pay := get_pay_table_value (
77 p_effective_date => l_effective_date
78 , p_pay_plan => l_pay_plan -- 'GS'
79 , p_grade_or_level => p_to_grade_or_level
80 , p_step_or_rate => p_to_step_or_rate
81 ) ;
82 if not (l_table_pay = to_number(p_to_basic_pay) or
83 p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
84 then
85 GHR_GHRWS52L.CPDF_Parameter_Check;
86 hr_utility.set_message(8301,'GHR_37801_ALL_PROCEDURE_FAIL');
87 hr_utility.raise_error;
88 end if;
89 end if;
90
91 -- 650.07.1
92 if ((p_retained_grade is not null and p_retained_step is not null) and
93 (
94 (p_to_pay_plan = 'GG' or p_to_pay_plan = 'GS') and
95 (p_rate_determinant_code = 'A' or
96 p_rate_determinant_code = 'B') and
97 (p_retained_pay_plan = 'GG' or p_retained_pay_plan = 'GS') and
98 p_retained_grade >= p_to_grade_or_level and
99 (p_retained_grade between '01' and '15') and
100 (p_retained_step between '01' and '10')
101 )
102 ) then
103 l_table_pay := get_pay_table_value (
104 p_effective_date => p_effective_date
105 , p_pay_plan => p_retained_pay_plan
106 , p_grade_or_level => p_retained_grade
107 , p_step_or_rate => p_retained_step
108 ) ;
109 if (l_table_pay <> to_number(p_to_basic_pay) and
110 p_to_basic_pay is not null and
111 to_number(p_to_basic_pay) <> 0) then
112 GHR_GHRWS52L.CPDF_Parameter_Check;
113 hr_utility.set_message(8301,'GHR_37804_ALL_PROCEDURE_FAIL');
114 hr_utility.raise_error;
115 end if;
116 end if;
117
118 --650.11.1
119 -- Update Date By Effective Date Comment
120 -- 8 05/14/99 vravikan 10/01/98 New Edit
121 -- 13-Jun-06 Raju 01-Jan-03 Terminate the edit
122 /* If Pay plan is JA
123 then basic pay must be within the range on Table 43 */
124 if p_effective_date >= fnd_date.canonical_to_date('1998/10/01') and
125 p_effective_date < fnd_date.canonical_to_date('2003/01/01') then --Bug# 5073313
126 if ( p_to_pay_plan = 'JA' ) then
127 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 43',
128 p_to_pay_plan,'Minimum Basic Pay', p_effective_date);
129 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 43',
130 p_to_pay_plan,'Maximum Basic Pay',p_effective_date);
131 if not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
132 then
133 GHR_GHRWS52L.CPDF_Parameter_Check;
134 hr_utility.set_message(8301,'GHR_37057_ALL_PROCEDURE_FAIL');
135 hr_utility.raise_error;
136 end if;
137 end if;
138 end if;
139 -- 650.26.3
140 -- 2/1/01 -- vravikan -- Passing 'GS' to get_pay_table_value instead of 'FG' as
141 -- 0000 pay table does not have any value for 'FG'
142
143 /* If pay plan is FG,
144 And pay rate determinant is 0 or 7,
145 Then basic pay must match the entry for grade and step
146 or rate on Table 1 or be asterisks.
147 Default: Insert asterisks in pay basis and basic pay. */
148 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
149 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
150 if (
151 p_to_pay_plan = 'FG' and
152 (p_rate_determinant_code = '0' or
153 p_rate_determinant_code = '7')
154 ) then
155 l_table_pay := get_pay_table_value (
156 p_effective_date => p_effective_date
157 , p_pay_plan => 'GS'
158 , p_grade_or_level => p_to_grade_or_level
159 , p_step_or_rate => p_to_step_or_rate
160 ) ;
161 if not (nvl(l_table_pay,0) = to_number(p_to_basic_pay) or
162 (p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)) then
163 GHR_GHRWS52L.CPDF_Parameter_Check;
164 hr_utility.set_message(8301,'GHR_37811_ALL_PROCEDURE_FAIL');
165 hr_utility.raise_error;
166 end if;
167 end if;
168 end if;
169 /* previous code was incorrect
170 if ((p_retained_grade is not null and p_retained_step is not null) and
171 p_to_pay_plan = 'FG' and
172 (p_rate_determinant_code = '0' or
173 p_rate_determinant_code = '7') and
174 (p_retained_grade between '01' and '15') and (p_retained_step between '01' and '10')
175 ) then
176 l_table_pay := get_pay_table_value (
177 p_effective_date => p_effective_date
178 , p_pay_plan => 'GS'
179 , p_grade_or_level => p_retained_grade
180 , p_step_or_rate => p_retained_step
181 ) ;
182 if not (l_table_pay = to_number(p_to_basic_pay) or
183 (p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)) then
184 GHR_GHRWS52L.CPDF_Parameter_Check;
185 hr_utility.set_message(8301,'GHR_37811_ALL_PROCEDURE_FAIL');
186 hr_utility.raise_error;
187 end if;
188 end if;
189 */
190
191 -- 650.31.1
192 /* If pay plan is EX,
193 And pay rate determinant is 0,
194 Then basic pay must equal the entry for the grade on
195 Table 5 or be asterisks.
196
197 Default: Insert asterisks in pay basis and basic pay. */
198
199 IF p_to_pay_plan = 'EX' and
200 p_rate_determinant_code = '0' THEN
201 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 5',
202 p_to_grade_or_level ,
203 'Maximum Basic Pay',
204 p_effective_date);
205 IF NVL(to_number(p_to_basic_pay), 0) <> max_basic_pay THEN
206 GHR_GHRWS52L.CPDF_Parameter_Check;
207 hr_utility.set_message(8301,'GHR_37816_ALL_PROCEDURE_FAIL');
208 hr_utility.raise_error;
209 END IF;
210 END IF;
211
212 --650.32.3
213 -- Update Date By Effective Date Comment
214 -- 18-Aug-00 vravikan 01-May-2000 New Edit
215 -- 13-Jun-06 Raju 01-Jan-03 Terminate the edit
216 /* If Pay plan is VE
217 And pay rate determinant is 0,
218 then basic pay must be within the range for the grade on Table 52 */
219 if p_effective_date >= to_date('2000/05/01','yyyy/mm/dd') and
220 p_effective_date < fnd_date.canonical_to_date('2003/01/01') then --Bug# 5073313
221 if ( p_to_pay_plan = 'VE' and p_rate_determinant_code = '0' ) THEN
222 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 52',
223 p_to_grade_or_level,'Minimum Basic Pay',p_effective_date);
224 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 52',
225 p_to_grade_or_level,'Maximum Basic Pay',p_effective_date);
226 if not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
227 then
228 GHR_GHRWS52L.CPDF_Parameter_Check;
229 hr_utility.set_message(8301,'GHR_37425_ALL_PROCEDURE_FAIL');
230 hr_utility.raise_error;
231 end if;
232 end if;
233 end if;
234 -- commented by Raju as it is duplicate
235 --650.32.3
236 -- Update Date By Effective Date Comment
237 -- 18-Aug-00 vravikan 01-May-2000 New Edit
238 /* If Pay plan is VE
239 And pay rate determinant is 0,
240 then basic pay must be within the range for the grade on Table 52 */
241 /* if p_effective_date >= to_date('2000/05/01','yyyy/mm/dd') then
242 if ( p_to_pay_plan = 'VE' and p_rate_determinant_code = '0' ) THEN
243 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 52',
244 p_to_grade_or_level,
245 'Minimum Basic Pay',
246 p_effective_date);
247 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 52',
248 p_to_grade_or_level,
249 'Maximum Basic Pay',
250 p_effective_date);
251 if not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
252 then
253 GHR_GHRWS52L.CPDF_Parameter_Check;
254 hr_utility.set_message(8301,'GHR_37425_ALL_PROCEDURE_FAIL');
255 hr_utility.raise_error;
256 end if;
257 end if;
258 end if;
259 */
260 -- 650.37.1
261 -- Madhuri 21-JAN-2004 End Dating this edit as on 10-JAN-04
262 -- For SES Pay Calculations
263 -- Madhuri 19-MAY-2004 Renumbering this edit from 650.37.1 to 650.37.3.
264 -- Need to change the if then condition as under
265 -- end dating the edit as on 10-JAN-2004
266 -- FROM: Then basic pay must equal the entry for the step or rate on Table 6 or be asterisks
267 -- TO: Then basic pay must be within the range on Table 55.
268
269 IF ( p_effective_date < to_date('2004/01/11', 'yyyy/mm/dd') and
270 p_to_pay_plan IN ('ES', 'FE') and p_rate_determinant_code = '0' )
271 THEN
272 l_table_pay := get_pay_table_value (
273 p_effective_date => p_effective_date
274 , p_pay_plan => p_to_pay_plan
275 , p_grade_or_level => '00'
276 , p_step_or_rate => p_to_step_or_rate
277 ) ;
278 IF nvl(to_number(p_to_basic_pay), 0) <> l_table_pay THEN
279 GHR_GHRWS52L.CPDF_Parameter_Check;
280 hr_utility.set_message(8301,'GHR_37817_ALL_PROCEDURE_FAIL');
281 hr_utility.raise_error;
282 end if;
283 ELSIF ( p_effective_date >= to_date('2004/01/11', 'yyyy/mm/dd') and
284 p_to_pay_plan IN ('ES', 'FE') and p_rate_determinant_code = '0' )
285 THEN
286 -- 650.37.3
287 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 55',
288 p_to_pay_plan,
289 'Minimum Basic Pay',
290 p_effective_date);
291 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 55',
292 p_to_pay_plan,
293 'Maximum Basic Pay',
294 p_effective_date);
295 if not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
296 then
297 GHR_GHRWS52L.CPDF_Parameter_Check;
298 hr_utility.set_message(8301,'GHR_38884_ALL_PROCEDURE_FAIL');
299 -- NEED TO ADD A NEW MESSAGE HERE
300 hr_utility.raise_error;
301 end if;
302 END IF;
303
304
305
306 -- 650.38.1
307 -- Bug#5089732 Commented the edit as per Norma's Suggession,
308 -- until OPM modifies this edit.
309 /* if ((p_to_grade_or_level is not null and p_to_step_or_rate is not null) and
310 p_to_pay_plan = 'GG' and
311 (
312 p_to_grade_or_level between '01' and '15' and
313 p_to_step_or_rate between '01' and '10' and
314 p_rate_determinant_code in ('0','7')
315 )
316 ) then
317 l_table_pay := get_pay_table_value (
318 p_effective_date => p_effective_date
319 , p_pay_plan => 'GG'
320 , p_grade_or_level => p_to_grade_or_level
321 , p_step_or_rate => p_to_step_or_rate
322 ) ;
323 if not (to_number(p_to_basic_pay) = l_table_pay or
324 (p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)) then
325 GHR_GHRWS52L.CPDF_Parameter_Check;
326 hr_utility.set_message(8301,'GHR_37818_ALL_PROCEDURE_FAIL');
327 hr_utility.raise_error;
328 end if;
329 end if;
330 */
331 -- 650.39.1
332 -- Update Date By Effective Date Comment
333 -- 10-Oct-05 Utokachi 01-MAY-2005 Bug#4444609 added equal or exceed condition
334 -- 09-Nov-2005 Raju 01-May-2005 UPD 43(Bug 4567571)Delete PRD M
335 IF p_effective_date < to_date('2005/05/01','YYYY/MM/DD') THEN
336 if ((p_to_grade_or_level is not null and p_to_step_or_rate is not null) and
337 p_to_pay_plan = 'GG' and
338 (
339 p_to_grade_or_level between '01' and '15' and
340 p_to_step_or_rate between '01' and '10'
341 ) and
342 p_rate_determinant_code in ('5','6','M')
343 ) then
344 l_table_pay := get_pay_table_value (
345 p_effective_date => p_effective_date
346 , p_pay_plan => 'GG'
347 , p_grade_or_level => p_to_grade_or_level
348 , p_step_or_rate => p_to_step_or_rate
349 ) ;
350 if not (to_number(p_to_basic_pay) > l_table_pay or
351 (p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)) then
352 GHR_GHRWS52L.CPDF_Parameter_Check;
353 hr_utility.set_message(8301,'GHR_37819_ALL_PROCEDURE_FAIL');
354 hr_utility.set_message_token('COND','exceed');
355 hr_utility.set_message_token('PRD_LIST','5,6 or M');
356 hr_utility.raise_error;
357 end if;
358 end if;
359 -- FWFA Changes Bug#4444609
360 ELSIF p_effective_date >= to_date('2005/05/01','YYYY/MM/DD') THEN
361 if ((p_to_grade_or_level is not null and p_to_step_or_rate is not null) and
362 p_to_pay_plan = 'GG' and
363 (
364 p_to_grade_or_level between '01' and '15' and
365 p_to_step_or_rate between '01' and '10'
366 ) and
367 p_rate_determinant_code in ('5','6')
368 ) then
369 l_table_pay := get_pay_table_value (
370 p_effective_date => p_effective_date
371 , p_pay_plan => 'GG'
372 , p_grade_or_level => p_to_grade_or_level
373 , p_step_or_rate => p_to_step_or_rate
374 ) ;
375 if not (to_number(p_to_basic_pay) >= l_table_pay or
376 (p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)) then
377 GHR_GHRWS52L.CPDF_Parameter_Check;
378 hr_utility.set_message(8301,'GHR_37819_ALL_PROCEDURE_FAIL');
379 hr_utility.set_message_token('COND','equal or exceed');
380 hr_utility.set_message_token('PRD_LIST','5 or 6');
381 hr_utility.raise_error;
382 end if;
383 end if;
384 END IF;
385 -- FWFA Changes Bug#4444609
386
387 -- 650.39.2
388 -- When pay plan is GG, and grade is 01 through 15, and step is 01 through 10,
389 -- and pay rate determinant is 5 or 6, then basic pay must equal or exceed
390 -- the entry for the grade and step on Table 1 or be asterisks.
391 -- 28-May-2005 utokachi New Edit FWFA Changes Bug# 4658890
392 IF p_effective_date >= to_date('2005/05/01','yyyy/mm/dd') THEN
393 if p_to_pay_plan = 'GG' and
394 p_to_grade_or_level between '01' and '15' and
395 p_to_step_or_rate between '01' and '10' and
396 p_rate_determinant_code in ('5','6')
397 then
398 l_table_pay := get_pay_table_value (
399 p_effective_date => p_effective_date
400 , p_pay_plan => 'GG'
401 , p_grade_or_level => p_to_grade_or_level
402 , p_step_or_rate => p_to_step_or_rate
403 ) ;
404 if (
405 (l_table_pay > to_number(p_to_basic_pay)) and
406 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> '0')
407 ) then
408 GHR_GHRWS52L.CPDF_Parameter_Check;
409 hr_utility.set_message(8301,'GHR_38983_ALL_PROCEDURE_FAIL');
410 hr_utility.raise_error;
411 end if;
412 end if;
413 END IF;
414
415 --650.66.3
416 -- Update Date By Effective Date Comment
417 -- 17-Aug-00 vravikan 01-Jan-2000 New Edit
418 /* If Pay plan is VN
419 And pay rate determinant is 0,
420 And occupation is 0601 or 0603
421 then basic pay must be within the range for the grade on Table 14 */
422 if p_effective_date >= to_date('2000/01/01','yyyy/mm/dd') then
423 if ( p_to_pay_plan = 'VN' and p_rate_determinant_code = '0'
424 and p_occupation_code in ('0601','0603') ) then
425 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 14',
426 p_to_pay_plan,
427 'Minimum Basic Pay',
428 p_effective_date);
429 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 14',
430 p_to_pay_plan,
431 'Maximum Basic Pay',
432 p_effective_date);
433 if not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
434 then
435 GHR_GHRWS52L.CPDF_Parameter_Check;
436 hr_utility.set_message(8301,'GHR_37427_ALL_PROCEDURE_FAIL');
437 hr_utility.raise_error;
438 end if;
439 end if;
440 end if;
441
442 --650.67.3
443 -- Update Date By Effective Date Comment
444 -- 17-Aug-00 vravikan 01-Jan-2000 New Edit
445 /* If Pay plan is VN
446 And pay rate determinant is 0,
447 And occupation is 0605 or 0610
448 then basic pay must be within the range on Table 15 */
449 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
450 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
451 if p_effective_date >= to_date('2000/01/01','yyyy/mm/dd') then
452 if ( p_to_pay_plan = 'VN' and p_rate_determinant_code = '0'
453 and p_occupation_code in ('0605','0610') ) then
454 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 15',
455 p_to_pay_plan,
456 'Minimum Basic Pay',
457 p_effective_date);
458 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 15',
459 p_to_pay_plan,
460 'Maximum Basic Pay',
461 p_effective_date);
462 if not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
463 then
464 GHR_GHRWS52L.CPDF_Parameter_Check;
465 hr_utility.set_message(8301,'GHR_37428_ALL_PROCEDURE_FAIL');
466 hr_utility.raise_error;
467 end if;
468 end if;
469 end if;
470 end if;
471
472 -- 650.69.2
473 -- Update/Change Date By Effective Date Bug Comment
474 -- 10/2 08/13/99 vravikan 992944 Change step 1 to step 10
475 -- upd52 13-Feb-07 Raju From 01-Sep-2004 Bug#5745356 terminate
476
477 if p_effective_date < fnd_date.canonical_to_date('2004/09/01') THEN
478 if (( p_first_action_noa_la_code1 = 'J8M' or
479 p_first_action_noa_la_code2 = 'J8M') and
480 (p_first_noac_lookup_code in ('170','570') and
481 substr(p_to_pay_plan,1,1) in ('W','X'))) then
482 l_table_pay := get_pay_table_value (
483 p_effective_date => p_effective_date
484 , p_pay_plan => 'GS'
485 , p_grade_or_level => '11'
486 , p_step_or_rate => '10') ;
487 if l_table_pay < to_number(p_to_basic_pay) then
488 GHR_GHRWS52L.CPDF_Parameter_Check;
489 hr_utility.set_message(8301,'GHR_37852_ALL_PROCEDURE_FAIL');
490 hr_utility.raise_error;
491 end if;
492 end if;
493 end if;
494 -- 650.89.1
495 /* If pay plan is GG or GS,
496 And pay rate determinant is E, F, or M,
497 And retained pay plan is GG or GS,
498 And retained step is 01 through 10,
499 And retained grade is equal to or higher than grade,
500 Then basic pay must exceed the entry for retained grade
501 and retained step on Table 1 or be asterisks.
502
503 Default: Insert asterisks in pay basis and basic pay. */
504 -- Bug#4444609 Raju 28-May-2005 Added equal or exceed condition effective from 01-May-2005
505 -- UPD 43(Bug 4567571) Raju 09-Nov-2005 Delete PRD M effective date from 01-May-2005
506
507 IF p_effective_date < to_date('2005/05/01','YYYY/MM/DD') THEN
508 if ((p_to_grade_or_level is not null and p_to_step_or_rate is not null) and
509 (p_to_pay_plan = 'GG' or p_to_pay_plan = 'GS') and
510 (p_retained_pay_plan = 'GG' or p_retained_pay_plan = 'GS') and
511 p_rate_determinant_code in ('E','F','M') and
512 p_retained_grade >= p_to_grade_or_level
513 ) then
514 l_table_pay := get_pay_table_value (
515 p_effective_date => p_effective_date
516 , p_pay_plan => p_retained_pay_plan
517 , p_grade_or_level => p_retained_grade
518 , p_step_or_rate => p_retained_step
519 ) ;
520 if not (
521 (nvl(l_table_pay,0) < to_number(p_to_basic_pay)) or
522 (p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
523 ) then
524 GHR_GHRWS52L.CPDF_Parameter_Check;
525 hr_utility.set_message(8301,'GHR_37844_ALL_PROCEDURE_FAIL');
526 hr_utility.set_message_token('COND','exceed');
527 hr_utility.set_message_token('PRD_LIST','E, F or M');
528 hr_utility.raise_error;
529 end if;
530 end if;
531 -- FWFA Changes
532 ELSIF p_effective_date >= to_date('2005/05/01','YYYY/MM/DD') THEN
533 if ((p_to_grade_or_level is not null and p_to_step_or_rate is not null) and
534 (p_to_pay_plan = 'GG' or p_to_pay_plan = 'GS') and
535 (p_retained_pay_plan = 'GG' or p_retained_pay_plan = 'GS') and
536 p_rate_determinant_code in ('E','F') and
537 p_retained_grade >= p_to_grade_or_level
538 ) then
539 l_table_pay := get_pay_table_value (
540 p_effective_date => p_effective_date
541 , p_pay_plan => p_retained_pay_plan
542 , p_grade_or_level => p_retained_grade
543 , p_step_or_rate => p_retained_step
544 ) ;
545 if not (
546 (nvl(l_table_pay,0) <= to_number(p_to_basic_pay)) or
547 (p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
548 ) then
549 GHR_GHRWS52L.CPDF_Parameter_Check;
550 hr_utility.set_message(8301,'GHR_37844_ALL_PROCEDURE_FAIL');
551 hr_utility.set_message_token('COND','equal or exceed');
552 hr_utility.set_message_token('PRD_LIST','E or F');
553 hr_utility.raise_error;
554 end if;
555 end if;
556 END IF;
557 -- FWFA Changes
558
559 -- 650.81.1
560 /*
561 --
562 -- Commented the edit for bug 557188 on 7-aug-98
563 --
564 if ((p_retained_grade is not null and p_retained_step is not null) and
565 (p_to_pay_plan <> 'GG' and p_to_pay_plan <> 'GS') and
566 (p_retained_pay_plan = 'GG' or p_retained_pay_plan = 'GS') and
567 (p_retained_grade between '01' and '15') and (p_retained_step between '01' and '10') and
568 (p_rate_determinant_code = 'A' or p_rate_determinant_code = 'B')
569 ) then
570 l_table_pay := get_pay_table_value (
571 p_effective_date => p_effective_date
572 , p_pay_plan => p_retained_pay_plan
573 , p_grade_or_level => p_retained_grade
574 , p_step_or_rate => p_retained_step
575 ) ;
576 if not (
577 (l_table_pay = to_number(p_to_basic_pay)) or (p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
578 ) then
579 GHR_GHRWS52L.CPDF_Parameter_Check;
580 hr_utility.set_message(8301,'GHR_37837_ALL_PROCEDURE_FAIL');
581 hr_utility.raise_error;
582 end if;
583 end if;
584 */
585
586 -- 650.82.1
587 /*
588 --
589 -- Commented the edit for bug 557188 on 7-aug-98
590 --
591 if ((p_retained_grade is not null and p_retained_step is not null) and
592 (p_to_pay_plan <> 'GG' and p_to_pay_plan <> 'GS') and
593 (p_retained_pay_plan = 'GG' or p_retained_pay_plan = 'GS') and
594 (p_rate_determinant_code = 'E' or p_rate_determinant_code = 'F')
595 ) then
596 l_table_pay := get_pay_table_value (
597 p_effective_date => p_effective_date
598 , p_pay_plan => p_retained_pay_plan
599 , p_grade_or_level => p_retained_grade
600 , p_step_or_rate => p_retained_step
601 ) ;
602 if not (
603 (nvl(l_table_pay,0) < to_number(p_to_basic_pay)) or
604 (p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
605 ) then
606 GHR_GHRWS52L.CPDF_Parameter_Check;
607 hr_utility.set_message(8301,'GHR_37838_ALL_PROCEDURE_FAIL');
608 hr_utility.raise_error;
609 end if;
610 end if;
611 */
612 -- Added on 10-Oct-05 by Utokachi for Validate actions after 01-MAY-2005
613 --FWFA Changes
614 IF p_effective_date >= to_date('2005/05/01','YYYY/MM/DD') THEN
615 if ((p_retained_grade is not null and p_retained_step is not null) and
616 (p_to_pay_plan <> 'GG' and p_to_pay_plan <> 'GS') and
617 (p_retained_pay_plan = 'GG' or p_retained_pay_plan = 'GS') and
618 (p_rate_determinant_code = 'E' or p_rate_determinant_code = 'F')
619 ) then
620 l_table_pay := get_pay_table_value (
621 p_effective_date => p_effective_date
622 , p_pay_plan => p_retained_pay_plan
623 , p_grade_or_level => p_retained_grade
624 , p_step_or_rate => p_retained_step
625 ) ;
626 if not (
627 (nvl(l_table_pay,0) <= to_number(p_to_basic_pay)) or
628 (p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
629 ) then
630 GHR_GHRWS52L.CPDF_Parameter_Check;
631 hr_utility.set_message(8301,'GHR_37838_ALL_PROCEDURE_FAIL');
632 hr_utility.raise_error;
633 end if;
634 end if;
635 END IF;
636 --FWFA Changes
637
638 --650.83.1
639 -- Modified as a part of FWFA Changes. Restricted the edit to actions processed
640 -- before 01-MAY-2005. For actions processed on or after 01-MAY-2005, this edit
641 -- will be skipped.
642 -- FWFA Changes Bug#4444609
643 -- UPD 43(Bug 4567571) Raju 09-Nov-2005 Delete PRD M effective date from 01-May-2005
644 IF p_effective_date < to_date('2005/05/01','yyyy/mm/dd') THEN
645 if p_to_step_or_rate between '01' and '10' and
646 p_to_pay_plan = 'GS' and p_rate_determinant_code in ('5','6','M')
647 then
648 l_table_pay := get_pay_table_value (
649 p_effective_date => p_effective_date
650 , p_pay_plan => 'GS'
651 , p_grade_or_level => p_to_grade_or_level
652 , p_step_or_rate => p_to_step_or_rate
653 ) ;
654 if not (
655 (l_table_pay < to_number(p_to_basic_pay)) or
656 (p_to_basic_pay is null or to_number(p_to_basic_pay) = '0')
657 ) then
658 GHR_GHRWS52L.CPDF_Parameter_Check;
659 hr_utility.set_message(8301,'GHR_37839_ALL_PROCEDURE_FAIL');
660 hr_utility.set_message_token('COND','exceed');
661 hr_utility.set_message_token('PRD_LIST','5,6 or M');
662 hr_utility.raise_error;
663 end if;
664 end if;
665 ELSE
666 if p_to_step_or_rate between '01' and '10' and
667 p_to_pay_plan = 'GS' and p_rate_determinant_code in ('5','6')
668 then
669 l_table_pay := get_pay_table_value (
670 p_effective_date => p_effective_date
671 , p_pay_plan => 'GS'
672 , p_grade_or_level => p_to_grade_or_level
673 , p_step_or_rate => p_to_step_or_rate
674 ) ;
675 if not (
676 (l_table_pay <= to_number(p_to_basic_pay)) or
677 (p_to_basic_pay is null or to_number(p_to_basic_pay) = '0')
678 ) then
679 GHR_GHRWS52L.CPDF_Parameter_Check;
680 hr_utility.set_message(8301,'GHR_37839_ALL_PROCEDURE_FAIL');
681 hr_utility.set_message_token('COND','equal or exceed');
682 hr_utility.set_message_token('PRD_LIST','5 or 6');
683 hr_utility.raise_error;
684 end if;
685 end if;
686 END IF;
687 -- FWFA Changes
688 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
689
690 -- 650.83.2
691 -- When pay plan is GS, and pay rate determinant is 5 or 6,
692 -- then basic pay must equal or exceed the entry for grade
693 -- and step or rate on Table 1 or be asterisks
694
695 -- 28-May-2005 utokachi New Edit FWFA Changes Bug# 4658890
696
697 IF p_effective_date >= to_date('2005/05/01','yyyy/mm/dd') THEN
698 if p_to_pay_plan = 'GS' and p_rate_determinant_code in ('5','6') then
699 l_table_pay := get_pay_table_value (
700 p_effective_date => p_effective_date
701 , p_pay_plan => 'GS'
702 , p_grade_or_level => p_to_grade_or_level
703 , p_step_or_rate => p_to_step_or_rate
704 ) ;
705 if (
706 (l_table_pay > to_number(p_to_basic_pay)) or
707 (p_to_basic_pay is not null or to_number(p_to_basic_pay) <> '0')
708 ) then
709 GHR_GHRWS52L.CPDF_Parameter_Check;
710 hr_utility.set_message(8301,'GHR_38984_ALL_PROCEDURE_FAIL');
711 hr_utility.raise_error;
712 end if;
713 end if;
714 END If;
715
716
717 650.03.3 If pay plan is NH,and pay rate determinant is 0,5,6 or 7
718 Then basic pay must be within the range for the grade on
719 Table 37 or Table 38(depending on pay rate determinant) or
720 be asterisks.
721 Default: Insert asterisks in pay basis and basic pay.
722
723 if p_effective_date > fnd_date.canonical_to_date('1998/03/01') and
724 p_to_pay_plan = 'NH' and
725 p_rate_determinant_code in ('0','5','6','7') then -- added for bug 726125
726 if p_rate_determinant_code in ('0','7') then
727 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 37',
728 p_to_grade_or_level ,
729 'Minimum Basic Pay',p_effective_date);
730 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 37',
731 p_to_grade_or_level ,
732 'Maximum Basic Pay',p_effective_date);
733 elsif p_rate_determinant_code in ('5','6') then
734 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 38',
735 p_to_grade_or_level ,
736 'Minimum Basic Pay',p_effective_date);
737 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 38',
738 p_to_grade_or_level ,
739 'Maximum Basic Pay',p_effective_date);
740 end if;
741 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
742 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
743 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
744 then
745 GHR_GHRWS52L.CPDF_Parameter_Check;
746 hr_utility.set_message(8301,'GHR_37886_ALL_PROCEDURE_FAIL');
747 hr_utility.raise_error;
748 end if;
749 end if;
750 */
751
752 /*650.05.3 If pay plan is NY,
753 Then basic pay must be within the range on Table 26 or
754 be asterisks.
755
756 Default: Insert asterisks in pay basis and basic pay.*/
757 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
758 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
759 if p_to_pay_plan = 'NY' then
760
761 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 26',
762 p_to_grade_or_level ,
763 'Minimum Basic Pay',p_effective_date);
764
765 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 26',
766 p_to_grade_or_level ,
767 'Maximum Basic Pay',p_effective_date);
768
769 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
770 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
771 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
772 then
773 GHR_GHRWS52L.CPDF_Parameter_Check;
774 hr_utility.set_message(8301,'GHR_37802_ALL_PROCEDURE_FAIL');
775 hr_utility.raise_error;
776 end if;
777 end if;
778 end if;
779
780 /*650.06.3 If pay plan is NX,
781 Then basic pay must be within the range for the grade
782 on Table 27 or be asterisks.
783
784 Default: Insert asterisks in pay basis and basic pay.*/
785 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
786 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
787 if p_to_pay_plan = 'NX' then
788
789 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 27',
790 p_to_grade_or_level ,
791 'Minimum Basic Pay',p_effective_date);
792
793 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 27',
794 p_to_grade_or_level ,
795 'Maximum Basic Pay',p_effective_date);
796
797 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
798 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
799 or p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
800 then
801 GHR_GHRWS52L.CPDF_Parameter_Check;
802 hr_utility.set_message(8301,'GHR_37803_ALL_PROCEDURE_FAIL');
803 hr_utility.raise_error;
804 end if;
805 end if;
806 end if;
807
808 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
809 650.08.3 If pay plan is NJ,and pay rate determinant is 0,5,6 or 7
810 Then basic pay must be within the range for the grade on
811 Table 39 or Table 40(depending on pay rate determinant) or
812 be asterisks.
813 Default: Insert asterisks in pay basis and basic pay.
814
815 if p_effective_date > fnd_date.canonical_to_date('1998/03/01') and
816 p_to_pay_plan = 'NJ' and
817 p_rate_determinant_code in ('0','5','6','7') then -- added for bug 726125
818 if p_rate_determinant_code in ('0','7') then
819 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 39',
820 p_to_grade_or_level ,
821 'Minimum Basic Pay',p_effective_date);
822 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 39',
823 p_to_grade_or_level ,
824 'Maximum Basic Pay',p_effective_date);
825 elsif p_rate_determinant_code in ('5','6') then
826 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 40',
827 p_to_grade_or_level ,
828 'Minimum Basic Pay',p_effective_date);
829 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 40',
830 p_to_grade_or_level ,
831 'Maximum Basic Pay',p_effective_date);
832 end if;
833 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
834 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
835 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
836 then
837 GHR_GHRWS52L.CPDF_Parameter_Check;
838 hr_utility.set_message(8301,'GHR_37863_ALL_PROCEDURE_FAIL');
839 hr_utility.raise_error;
840 end if;
841 end if;
842 */
843
844 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
845 650.09.3 If pay plan is NK,and pay rate determinant is 0,5,6 or 7
846 Then basic pay must be within the range for the grade on
847 Table 41 or Table 42(depending on pay rate determinant) or
848 be asterisks.
849 Default: Insert asterisks in pay basis and basic pay.
850
851 if p_effective_date > fnd_date.canonical_to_date('1998/03/01') and
852 p_to_pay_plan = 'NK' and
853 p_rate_determinant_code in ('0','5','6','7') then
854 if p_rate_determinant_code in ('0','7') then
855 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 41',
856 p_to_grade_or_level ,
857 'Minimum Basic Pay',p_effective_date);
858 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 41',
859 p_to_grade_or_level ,
860 'Maximum Basic Pay',p_effective_date);
861 elsif p_rate_determinant_code in ('5','6') then
862 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 42',
863 p_to_grade_or_level ,
864 'Minimum Basic Pay',p_effective_date);
865 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 42',
866 p_to_grade_or_level ,
867 'Maximum Basic Pay',p_effective_date);
868 end if;
869 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
870 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
871 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
872 then
873 GHR_GHRWS52L.CPDF_Parameter_Check;
874 hr_utility.set_message(8301,'GHR_37864_ALL_PROCEDURE_FAIL');
875 hr_utility.raise_error;
876 end if;
877 end if;
878 */
879
880 /*650.13.1 If pay plan is GS,
881 And pay rate determinant is 2, 3, 4, J, K, or R,
882 Then basic pay must equal or exceed the minimum basic
883 pay for the appropriate grade on Table 2 or be
884 asterisks.
885
886 Default: Insert asterisks in pay basis and basic pay.*/
887
888 -- Modified EDIT for Student Loan Repayment
889 -- The to Basic Pay will be NULL for 817 action, so this edit will be fired
890 -- Checking for NOAC 817 before firing edit
891 -- Bug# 9255822 added PRD Y
892
893 if p_to_pay_plan = 'GS' and
894 p_rate_determinant_code in ('2','3','4','J','K','R','Y') then
895
896 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 2',
897 p_to_grade_or_level || '-' || p_to_step_or_rate,
898 'Minimum Basic Pay',p_effective_date);
899
900 if min_basic_pay IS NOT NULL and
901 (not(to_number(p_to_basic_pay) >= min_basic_pay)
902 or (p_to_basic_pay is null and p_first_noac_lookup_code <> '817' ))
903 then
904 GHR_GHRWS52L.CPDF_Parameter_Check;
905 hr_utility.set_message(8301,'GHR_37805_ALL_PROCEDURE_FAIL');
906 hr_utility.raise_error;
907 end if;
908 end if;
909
910 /*650.16.1 If pay rate determinant is U or V,
911 And retained pay plan is GG, GH, GS, or GM,
912 Then basic pay must equal or exceed the minimum basic
913 pay for the retained grade on Table 2 or be asterisks.
914
915 Default: Insert asterisks in pay basis and basic pay.*/
916 /*
917 --
918 -- Commented the edit for bug 557188 on 7-aug-98
919 --
920 if p_rate_determinant_code in ('U','V') and
921 p_to_pay_plan in ('GG','GH','GS','GM') then
922
923 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 2',
924 p_retained_grade || '-' || p_retained_step,
925 'Minimum Basic Pay',p_effective_date);
926
927 if min_basic_pay IS NOT NULL and
928 (not(to_number(p_to_basic_pay) >= min_basic_pay)
929 or p_to_basic_pay is null)
930 then
931 GHR_GHRWS52L.CPDF_Parameter_Check;
932 hr_utility.set_message(8301,'GHR_37806_ALL_PROCEDURE_FAIL');
933 hr_utility.raise_error;
934 end if;
935 end if;
936 */
937
938 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
939 650.19.1 If pay plan is GM,
940 And pay rate determinant is 0 or 7,
941 Then basic pay must fall within the range for grade and
942 step or rate on Table 3 or be asterisks.
943
944 Default: Insert asterisks in pay basis and basic pay.
945 if p_to_pay_plan = 'GM'
946 and p_rate_determinant_code in ('0', '7') then
947
948 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
949 p_to_grade_or_level || '-' || p_to_step_or_rate,
950 'Minimum Basic Pay',p_effective_date);
951
952 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
953 p_to_grade_or_level || '-' || p_to_step_or_rate,
954 'Maximum Basic Pay',p_effective_date);
955
956 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
957 and (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
958 or p_to_basic_pay is null)
959 then
960 GHR_GHRWS52L.CPDF_Parameter_Check;
961 hr_utility.set_message(8301,'GHR_37807_ALL_PROCEDURE_FAIL');
962 hr_utility.raise_error;
963 end if;
964 end if;
965 */
966
967 -- 18-Aug-00 - vravikan --- 01-Jan-2000 New Edit
968 /*
969 650.19.3 If pay plan is GM,
970 And pay rate determinant is 0 or 7,
971 Then basic pay must fall within the range for grade on Table 3
972 */
973 if p_to_pay_plan = 'GM'
974 and p_rate_determinant_code in ('0', '7') then
975
976 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
977 p_to_grade_or_level ,
978 'Minimum Basic Pay',p_effective_date);
979
980 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
981 p_to_grade_or_level ,
982 'Maximum Basic Pay',p_effective_date);
983
984 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
985 and (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
986 or p_to_basic_pay is null)
987 then
988 GHR_GHRWS52L.CPDF_Parameter_Check;
989 hr_utility.set_message(8301,'GHR_37429_ALL_PROCEDURE_FAIL');
990 hr_utility.raise_error;
991 end if;
992 end if;
993 --Commented as per EOY 2003 cpdf changes by Ashley
994 /*650.20.1 If pay plan is GM,
995 And pay rate determinant is 5, 6, or M,
996 Then basic pay must fall within the range for the grade
997 on Table 20 or be asterisks.
998
999 Default: Insert asterisks in pay basis and basic pay.*/
1000 /*if p_to_pay_plan = 'GM'
1001 and p_rate_determinant_code in ('5', '6', 'M') then
1002
1003 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 20',
1004 p_to_grade_or_level,
1005 'Minimum Basic Pay',p_effective_date);
1006 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 20',
1007 p_to_grade_or_level,
1008 'Maximum Basic Pay',p_effective_date);
1009
1010 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1011 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
1012 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
1013 then
1014 GHR_GHRWS52L.CPDF_Parameter_Check;
1015 hr_utility.set_message(8301,'GHR_37808_ALL_PROCEDURE_FAIL');
1016 hr_utility.raise_error;
1017 end if;
1018 end if;*/
1019
1020 /*
1021 CPDF Edit #650.21.2 and 650.21.1 : If pay plan is GL and pay rate determinant is 0 or 7.
1022 Then basic pay must match the entry for the grade and
1023 step on Table 57 or be asterisks.
1024 Table 57 is '0491'
1025 */
1026 -- 650.21.1 and 650.21.2
1027 IF p_effective_date >= fnd_date.canonical_to_date('2007/08/13') THEN
1028 if (
1029 p_to_pay_plan = 'GL' and
1030 (p_rate_determinant_code = '0' or p_rate_determinant_code = '7')
1031 ) then
1032 l_table_pay := GHR_CPDF_CHECK.get_basic_pay(
1033 '0491 Oracle Federal Special Rate Pay Table (GS) No. 0491',
1034 'GL' || '-' || p_to_grade_or_level,
1035 p_to_step_or_rate,
1036 p_effective_date);
1037
1038 if not (nvl(l_table_pay,0) = to_number(p_to_basic_pay) or
1039 (p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)) THEN
1040 GHR_GHRWS52L.CPDF_Parameter_Check;
1041 hr_utility.set_message(8301,'GHR_37751_ALL_PROCEDURE_FAIL');
1042 hr_utility.raise_error;
1043 end if;
1044 end if;
1045
1046 END IF;
1047
1048 /* Commented as per December 2000 cpdf changes -- vravikan
1049 650.22.1 If pay plan is GH or GM,
1050 And pay rate determinant is A or B,
1051 And retained grade is equal to or higher than grade,
1052 And retained pay plan is GH or GM,
1053 Then basic pay must fall within the range for retained
1054 grade and retained step on Table 3 or be asterisks.
1055
1056 Default: Insert asterisks in pay basis and basic pay.
1057 -- 17-Aug-00 vravikan 01-jan-2000 Delete retained step
1058 if p_effective_date >= to_date('2000/01/01','yyyy/mm/dd') then
1059 if p_to_pay_plan in ('GH','GM')
1060 and p_rate_determinant_code in ('A','B')
1061 and p_retained_grade >= p_to_grade_or_level
1062 and p_retained_pay_plan in ('GH','GM') then
1063
1064 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
1065 p_retained_grade,
1066 'Minimum Basic Pay',
1067 p_effective_date);
1068 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
1069 p_retained_grade,
1070 'Maximum Basic Pay',
1071 p_effective_date);
1072
1073 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1074 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
1075 or p_to_basic_pay is null
1076 or to_number(p_to_basic_pay) = 0)
1077 then
1078 GHR_GHRWS52L.CPDF_Parameter_Check;
1079 hr_utility.set_message(8301,'GHR_37433_ALL_PROCEDURE_FAIL');
1080 hr_utility.raise_error;
1081 end if;
1082 end if;
1083 else
1084 if p_to_pay_plan in ('GH','GM')
1085 and p_rate_determinant_code in ('A','B')
1086 and p_retained_grade >= p_to_grade_or_level
1087 and p_retained_pay_plan in ('GH','GM') then
1088
1089 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
1090 p_retained_grade|| '-' || p_retained_step,
1091 'Minimum Basic Pay',
1092 p_effective_date);
1093 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
1094 p_retained_grade|| '-' || p_retained_step ,
1095 'Maximum Basic Pay',
1096 p_effective_date);
1097
1098 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1099 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
1100 or p_to_basic_pay is null
1101 or to_number(p_to_basic_pay) = 0)
1102 then
1103 GHR_GHRWS52L.CPDF_Parameter_Check;
1104 hr_utility.set_message(8301,'GHR_37809_ALL_PROCEDURE_FAIL');
1105 hr_utility.raise_error;
1106 end if;
1107 end if;
1108 end if;
1109 */
1110
1111 -- 12/8/00 vravikan From Start Change Edit
1112 -- Table 4 to Table 2
1113
1114 /*650.25.1 If pay plan is GH or GM,
1115 And pay rate determinant is 2, 3, 4, J, K, or R,
1116 Then basic pay must equal or exceed the minimum basic
1117 pay for the grade on Table 4 or be asterisks.
1118
1119 Default: Insert asterisks in pay basis and basic pay.*/
1120 -- Bug# 9255822 added PRD Y
1121 if p_to_pay_plan in ('GH', 'GM')
1122 and p_rate_determinant_code in ('2','3','4','J','K','R','Y') then
1123
1124 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 2',
1125 p_to_grade_or_level || '-' || p_to_step_or_rate,
1126 'Minimum Basic Pay',p_effective_date);
1127
1128 if min_basic_pay IS NOT NULL and
1129 (not(to_number(p_to_basic_pay) >= min_basic_pay)
1130 or p_to_basic_pay is null)
1131 then
1132 GHR_GHRWS52L.CPDF_Parameter_Check;
1133 hr_utility.set_message(8301,'GHR_37810_ALL_PROCEDURE_FAIL');
1134 hr_utility.raise_error;
1135 end if;
1136 end if;
1137
1138 --Begin Bug# 9255822
1139 /*650.25.2 If pay plan is GH ,GM or GS,
1140 And pay rate determinant is 2, 3, 4, J, K, or R,
1141 Then basic pay must equal or exceed the minimum basic
1142 pay for the grade on Table 4 or be asterisks.
1143
1144 Default: Insert asterisks in pay basis and basic pay.*/
1145 if p_to_pay_plan in ('GH','GM','GS')
1146 and p_rate_determinant_code in ('2','3','4','J','K','R','Y') then
1147
1148 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 2',
1149 p_to_grade_or_level || '-' || p_to_step_or_rate,
1150 'Minimum Basic Pay',p_effective_date);
1151
1152 if min_basic_pay IS NOT NULL and
1153 (not(to_number(p_to_basic_pay) >= min_basic_pay)
1154 or p_to_basic_pay is null)
1155 then
1156 GHR_GHRWS52L.CPDF_Parameter_Check;
1157 hr_utility.set_message(8301,'GHR_38381_ALL_PROCEDURE_FAIL');
1158 hr_utility.raise_error;
1159 end if;
1160 end if;
1161 -- End Bug# 9255822
1162
1163 /*650.27.3 If pay plan is FB, FJ, or FX,
1164 Then basic pay must be no less than step 1 on Table 6
1165 and no more than step 6 on Table 6, or be asterisks.
1166
1167 Default: Insert asterisks in pay basis and basic pay.*/
1168 --
1169 -- Madhuri 19-MAY-2004 End dated the Edit as of 10-JAN-04
1170 --
1171 IF p_effective_date < to_date('2004/01/11', 'yyyy/mm/dd') then
1172 if p_to_pay_plan in ('FB', 'FJ', 'FX') then
1173
1174 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 6',
1175 '01',
1176 'Maximum Basic Pay',
1177 p_effective_date);
1178
1179 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 6',
1180 '06',
1181 'Maximum Basic Pay',
1182 p_effective_date);
1183 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL then
1184 if (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
1185 or p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
1186 then
1187 GHR_GHRWS52L.CPDF_Parameter_Check;
1188 hr_utility.set_message(8301,'GHR_37812_ALL_PROCEDURE_FAIL');
1189 hr_utility.raise_error;
1190 end if;
1191 end if;
1192 end if;
1193 END IF;
1194
1195 /*650.28.3 If pay plan is FT,
1196 Then basic pay must be within the range on Table 21 or
1197 be asterisks.*/
1198 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1199 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1200 if p_to_pay_plan = 'FT' then
1201 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
1202 p_to_pay_plan ,
1203 'Minimum Basic Pay',
1204 p_effective_date);
1205
1206 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
1207 p_to_pay_plan ,
1208 'Maximum Basic Pay',
1209 p_effective_date);
1210
1211 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL then
1212 if (not (to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
1213 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
1214 then
1215 GHR_GHRWS52L.CPDF_Parameter_Check;
1216 hr_utility.set_message(8301,'GHR_37813_ALL_PROCEDURE_FAIL');
1217 hr_utility.raise_error;
1218 end if;
1219 end if;
1220 end if;
1221 end if;
1222 /*650.29.3 If pay plan is FL, FS, or FW,
1223 Then basic pay must be no less than the minimum for pay
1224 plan WG on Table 11 and no more than the maximum for
1225 pay plan WS on Table 11, or be asterisks.
1226
1227 Default: Insert asterisks in pay basis and basic pay.*/
1228
1229 -- Update Date Updated By Effective Date Comments
1230 -----------------------------------------------------------------------------------------------------------
1231 -- 18/10/2004 Madhuri From the start of the edit Deleting the edit
1232 -----------------------------------------------------------------------------------------------------------
1233
1234 /*if p_to_pay_plan in ('FL', 'FS', 'FW') then
1235 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
1236 'WG' || '-' || p_to_grade_or_level,
1237 'Minimum Basic Pay',p_effective_date);
1238
1239 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
1240 'WS' || '-' || p_to_grade_or_level,
1241 'Maximum Basic Pay',p_effective_date);
1242
1243 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL then
1244 if (not (to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
1245 or p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
1246 then
1247 GHR_GHRWS52L.CPDF_Parameter_Check;
1248 hr_utility.set_message(8301,'GHR_37814_ALL_PROCEDURE_FAIL');
1249 hr_utility.raise_error;
1250 end if;
1251 end if;
1252 end if; */
1253 -----------------------------------------------------------------------------------------------------------
1254
1255 /*650.30.3 If pay plan is FM,
1256 And pay rate determinant is 0 or 7,
1257 Then basic pay must match the entry for grade and step
1258 or rate on Table 3 or be asterisks.
1259 Default: Insert asterisks in pay basis and basic pay.*/
1260
1261 -- 17-Aug-00 vravikan 01-jan-2000 Delete step or rate
1262 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1263 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1264 if p_effective_date >= to_date('2000/01/01','yyyy/mm/dd') then
1265 if p_to_pay_plan = 'FM' then
1266
1267 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
1268 p_to_grade_or_level,
1269 'Minimum Basic Pay',p_effective_date);
1270
1271 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
1272 p_to_grade_or_level,
1273 'Maximum Basic Pay',p_effective_date);
1274 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL then
1275 if p_rate_determinant_code in ('0','7')
1276 and (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
1277 or p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
1278 then
1279 GHR_GHRWS52L.CPDF_Parameter_Check;
1280 hr_utility.set_message(8301,'GHR_37435_ALL_PROCEDURE_FAIL');
1281 hr_utility.raise_error;
1282 end if;
1283 end if;
1284 end if;
1285 else
1286 if p_to_pay_plan = 'FM' then
1287
1288 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
1289 p_to_grade_or_level || '-' || p_to_step_or_rate,
1290 'Minimum Basic Pay',p_effective_date);
1291
1292 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
1293 p_to_grade_or_level || '-' || p_to_step_or_rate,
1294 'Maximum Basic Pay',p_effective_date);
1295 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL then
1296 if p_rate_determinant_code in ('0','7')
1297 and (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
1298 or p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
1299 then
1300 GHR_GHRWS52L.CPDF_Parameter_Check;
1301 hr_utility.set_message(8301,'GHR_37815_ALL_PROCEDURE_FAIL');
1302 hr_utility.raise_error;
1303 end if;
1304 end if;
1305 end if;
1306 end if;
1307 end if;
1308
1309 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1310 650.35.1 If pay plan is DR,
1311 And pay rate determinant is 0 or 7,
1312 Then basic pay must be within the range for the grade
1313 and pay rate determinant on Table 28 or be asterisks.
1314 Default: Insert asterisks in pay basis and basic pay.
1315
1316 if p_to_pay_plan = 'DR' and
1317 p_rate_determinant_code in ('0','7') then
1318
1319 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 28',
1320 p_to_grade_or_level ,
1321 'Minimum Basic Pay',p_effective_date);
1322
1323 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 28',
1324 p_to_grade_or_level ,
1325 'Maximum Basic Pay',p_effective_date);
1326
1327 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1328 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
1329 or p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
1330 then
1331 GHR_GHRWS52L.CPDF_Parameter_Check;
1332 hr_utility.set_message(8301,'GHR_37854_ALL_PROCEDURE_FAIL');
1333 hr_utility.raise_error;
1334 end if;
1335 end if;
1336 */
1337
1338 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1339 650.36.1 If pay plan is DR,
1340 And pay rate determinant is 5 or 6,
1341 Then basic pay must be within the range for the grade
1342 and pay rate determinant on Table 29 or be asterisks.
1343
1344 Default: Insert asterisks in pay basis and basic pay.
1345
1346 if p_to_pay_plan = 'DR' and
1347 p_rate_determinant_code in ('5','6') then
1348
1349 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 29',
1350 p_to_grade_or_level ,
1351 'Minimum Basic Pay',p_effective_date);
1352
1353 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 29',
1354 p_to_grade_or_level ,
1355 'Maximum Basic Pay',p_effective_date);
1356
1357 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1358 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
1359 or p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
1360 then
1361 GHR_GHRWS52L.CPDF_Parameter_Check;
1362 hr_utility.set_message(8301,'GHR_37855_ALL_PROCEDURE_FAIL');
1363 hr_utility.raise_error;
1364 end if;
1365 end if;
1366 */
1367
1368 /*650.41.3 If pay plan is GG,
1369 And grade is 01 through 15,
1370 And step is 01 through 10,
1371 And pay rate determinant is 5, 6, or M,
1372 Then basic pay must be equal to or less than the entry
1373 for the grade on Table 19 or be asterisks.
1374
1375 Default: Insert asterisks in pay basis and basic pay.*/
1376 /* Commenting the edit for the bug 3147737
1377 if p_to_pay_plan = 'GG' and
1378 p_rate_determinant_code in ('5','6','M') then
1379
1380 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 19',p_to_grade_or_level,
1381 'Maximum Basic Pay',p_effective_date);
1382
1383 if max_basic_pay IS NOT NULL and
1384 (not(to_number(p_to_basic_pay) < max_basic_pay)
1385 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
1386 then
1387 GHR_GHRWS52L.CPDF_Parameter_Check;
1388 hr_utility.set_message(8301,'GHR_37820_ALL_PROCEDURE_FAIL');
1389 hr_utility.raise_error;
1390 end if;
1391 end if;
1392 */
1393 /*650.42.1 If pay plan is GG,
1394 And grade is 01 through 15,
1395 And pay rate determinant is 2, 3, 4, J, K, or R,
1396 Then basic pay must equal or exceed the entry for the
1397 grade on Table 2 or be asterisks.
1398
1399 Default: Insert asterisks in pay basis and basic pay.*/
1400 -- Bug# 9255822 added PRD Y
1401 if p_to_pay_plan = 'GG' and
1402 p_to_grade_or_level between '01' and '15' and
1403 p_rate_determinant_code in ('2','3','4','J','K','R','Y') then
1404
1405 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 2',
1406 p_to_grade_or_level || '-' || p_to_step_or_rate ,
1407 'Minimum Basic Pay',
1408 p_effective_date);
1409 if min_basic_pay IS NOT NULL
1410 and (not(to_number(p_to_basic_pay) >= min_basic_pay)
1411 or p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
1412 then
1413 GHR_GHRWS52L.CPDF_Parameter_Check;
1414 hr_utility.set_message(8301,'GHR_37821_ALL_PROCEDURE_FAIL');
1415 hr_utility.raise_error;
1416 end if;
1417 end if;
1418
1419 -- 650.43.2 If pay plan is FA,
1420 -- And agency/subelement is ST,
1421 -- And pay rate determinant is 0,
1422 -- And step is 13 or 14,
1423 -- Then basic pay must equal entry for the step on Table 7 or be asterisks.
1424 -- 19-MAR-2003 vnarasim First version of the edit
1425 -- upd52 13-Feb-07 Raju From 01-Sep-2004 Bug#5745356 terminate
1426 if p_effective_date < fnd_date.canonical_to_date('2004/09/01') THEN
1427 if p_to_pay_plan = 'FA'
1428 and p_agency_subelement = 'ST'
1429 and p_rate_determinant_code = '0'
1430 and p_to_step_or_rate in ('13','14') then
1431
1432 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 7',
1433 p_to_step_or_rate ,
1434 'Maximum Basic Pay',p_effective_date);
1435
1436 if max_basic_pay IS NOT NULL
1437 and (to_number(p_to_basic_pay) <> max_basic_pay
1438 or p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
1439 then
1440 GHR_GHRWS52L.CPDF_Parameter_Check;
1441 hr_utility.set_message(8301,'GHR_37928_ALL_PROCEDURE_FAIL');
1442 hr_utility.raise_error;
1443 end if;
1444 end if;
1445 end if;
1446
1447 -- 650.43.3 If pay plan is FA,
1448 -- And agency/subelement is ST,
1449 -- And pay rate determinant is 0,
1450 -- And grade is 13 or 14,
1451 -- Then basic pay must equal entry for the grade on Table 7 or be asterisks.
1452 -- Default: Insert asterisks in pay basis and basic pay.
1453 -- Update Date By Comments
1454 -- ============ ============= ===========================================
1455 -- 20-Feb-2003 Madhuri Renumbered Edit from 650.43.1 to 650.43.3
1456 -- 27-Feb-2003 Madhuri Modified the requirement
1457 -- from And grade is 01 thru 04 or 13 or 14
1458 -- to And grade is 13 or 14
1459 -- 19-MAR-2003 NarasimhaRao Changed the parameter from p_to_grade_or_level to
1460 -- p_to_step_or _rate
1461 -- upd52 13-Feb-07 Raju From 01-Sep-2004 Bug#5745356 terminate
1462 if p_effective_date < fnd_date.canonical_to_date('2004/09/01') THEN
1463 if p_to_pay_plan = 'FA'
1464 and p_agency_subelement = 'ST'
1465 and p_rate_determinant_code = '0'
1466 and p_to_grade_or_level in ('13','14') then
1467
1468 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 7',
1469 p_to_step_or_rate ,
1470 'Maximum Basic Pay',p_effective_date);
1471
1472 if max_basic_pay IS NOT NULL
1473 and (to_number(p_to_basic_pay) <> max_basic_pay
1474 or p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
1475 then
1476 GHR_GHRWS52L.CPDF_Parameter_Check;
1477 hr_utility.set_message(8301,'GHR_37822_ALL_PROCEDURE_FAIL');
1478 hr_utility.raise_error;
1479 end if;
1480 end if;
1481 end if;
1482
1483 /*650.45.1 If pay plan is GH,
1484 And pay rate determinant is 0 or 7,
1485 Then basic pay must be within the range for the grade
1486 on Table 3 or be asterisks.
1487
1488 Default: Insert asterisks in pay basis and basic pay.*/
1489 if p_to_pay_plan = 'GH'
1490 and p_rate_determinant_code in ('0','7') then
1491
1492 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
1493 p_to_grade_or_level || '-' || p_to_step_or_rate,
1494 'Minimum Basic Pay',p_effective_date);
1495 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
1496 p_to_grade_or_level || '-' || p_to_step_or_rate,
1497 'Maximum Basic Pay',p_effective_date);
1498
1499 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
1500 and (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
1501 or p_to_basic_pay is null
1502 or to_number(p_to_basic_pay) = 0)
1503 then
1504 GHR_GHRWS52L.CPDF_Parameter_Check;
1505 hr_utility.set_message(8301,'GHR_37823_ALL_PROCEDURE_FAIL');
1506 hr_utility.raise_error;
1507 end if;
1508 end if;
1509
1510 --Commented as per EOY 2003 cpdf changes by Ashley
1511 /*650.46.1 If pay plan is GH,
1512 And pay rate determinant is 5, 6, or M,
1513 Then basic pay must be within the range for the grade
1514 on Table 20 or be asterisks.
1515
1516 Default: Insert asterisks in pay basis and basic pay.*/
1517
1518 /*if p_to_pay_plan = 'GH'
1519 and p_rate_determinant_code in ('5', '6', 'M') then
1520
1521 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 20',
1522 p_to_grade_or_level,
1523 'Minimum Basic Pay',p_effective_date);
1524
1525 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 20',
1526 p_to_grade_or_level,
1527 'Maximum Basic Pay',p_effective_date);
1528
1529 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
1530 and (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
1531 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
1532 then
1533 GHR_GHRWS52L.CPDF_Parameter_Check;
1534 hr_utility.set_message(8301,'GHR_37824_ALL_PROCEDURE_FAIL');
1535 hr_utility.raise_error;
1536 end if;
1537 end if;*/
1538
1539 /*650.47.1 If pay plan is FC,
1540 And pay rate determinant is 0,
1541 Then basic pay must be within the range for the grade on
1542 Table 8 or be asterisks.
1543
1544 Default: Insert asterisks in pay basis and basic pay.*/
1545 -- Update Date By Comments
1546 -- 20/2 27-Feb-2003 Madhuri Modified the requirement
1547 -- from Then Basic Pay must equal the entry for the grade on Table 8 or be asterisks.
1548 -- to Then Basic Pay must be within the range for the grade on Table 8 or be asterisks.
1549
1550 if p_to_pay_plan = 'FC' and p_rate_determinant_code = '0' then
1551 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 8',
1552 p_to_grade_or_level || '-' || p_to_step_or_rate,
1553 'Minimum Basic Pay',p_effective_date);
1554
1555 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 8',
1556 p_to_grade_or_level || '-' || p_to_step_or_rate,
1557 'Maximum Basic Pay',p_effective_date);
1558
1559 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1560 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
1561 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
1562 then
1563 GHR_GHRWS52L.CPDF_Parameter_Check;
1564 hr_utility.set_message(8301,'GHR_37825_ALL_PROCEDURE_FAIL');
1565 hr_utility.raise_error;
1566 end if;
1567 end if;
1568
1569 /*650.47.2 If pay plan is FC,
1570 And pay rate determinant is not C,
1571 Then basic pay must be within the range for the grade on
1572 Table 8 or be asterisks.
1573
1574 Default: Insert asterisks in pay basis and basic pay.*/
1575 -- Update Date By Comments
1576 -- 20/2 27-Feb-2003 Madhuri Modified the requirement
1577 -- from Then Basic Pay must equal the entry for the grade on Table 8 or be asterisks.
1578 -- to Then Basic Pay must be within the range for the grade on Table 8 or be asterisks.
1579 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1580 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1581 if p_to_pay_plan = 'FC' and p_rate_determinant_code <> 'C' then
1582
1583 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 8',
1584 p_to_grade_or_level || '-' || p_to_step_or_rate,
1585 'Minimum Basic Pay',p_effective_date);
1586
1587 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 8',
1588 p_to_grade_or_level || '-' || p_to_step_or_rate,
1589 'Maximum Basic Pay',p_effective_date);
1590
1591 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1592 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
1593 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
1594 then
1595 GHR_GHRWS52L.CPDF_Parameter_Check;
1596 hr_utility.set_message(8301,'GHR_37826_ALL_PROCEDURE_FAIL');
1597 hr_utility.raise_error;
1598 end if;
1599 end if;
1600 end if;
1601
1602 /*650.48.1 If pay plan is GG,
1603 And grade is SL,
1604 And pay rate determinant is 0,
1605 Then basic pay must be within the range on Table 21 or
1606 be asterisks.
1607
1608 Default: Insert asterisks in pay basis and basic pay.*/
1609
1610 if p_to_pay_plan = 'GG' and
1611 p_to_grade_or_level = 'SL' and
1612 p_rate_determinant_code = '0' then
1613
1614 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
1615 p_to_pay_plan ,'Minimum Basic Pay',p_effective_date);
1616
1617 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
1618 p_to_pay_plan ,'Maximum Basic Pay',p_effective_date);
1619
1620 if max_basic_pay IS NOT NULL and min_basic_pay is not null and
1621 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
1622 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
1623 then
1624 GHR_GHRWS52L.CPDF_Parameter_Check;
1625 hr_utility.set_message(8301,'GHR_37827_ALL_PROCEDURE_FAIL');
1626 hr_utility.raise_error;
1627 end if;
1628 end if;
1629
1630 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1631 650.49.1 If pay plan is ND,
1632 And pay rate determinant is 0, 5, 6, or 7,
1633 Then basic pay must be within the range for the grade
1634 on Table 30 or Table 31 (depending on pay rate
1635 determinant) or be asterisks.
1636
1637 Default: Insert asterisks in pay basis and basic pay.
1638 if p_to_pay_plan = 'ND' and
1639 p_rate_determinant_code in ('0','5','6','7') then
1640 if p_rate_determinant_code in ('0','7') then
1641 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 30',
1642 p_to_grade_or_level ,'Minimum Basic Pay',p_effective_date);
1643 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 30',
1644 p_to_grade_or_level ,'Maximum Basic Pay',p_effective_date);
1645 elsif p_rate_determinant_code in ('5','6') then
1646 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 31',
1647 p_to_grade_or_level ,'Minimum Basic Pay',p_effective_date);
1648 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 31',
1649 p_to_grade_or_level ,'Maximum Basic Pay',p_effective_date);
1650 end if;
1651 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
1652 and not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
1653 or p_to_basic_pay is null
1654 then
1655 GHR_GHRWS52L.CPDF_Parameter_Check;
1656 hr_utility.set_message(8301,'GHR_37856_ALL_PROCEDURE_FAIL');
1657 hr_utility.raise_error;
1658 end if;
1659 end if;
1660 */
1661
1662 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1663 650.50.1 If pay plan is NG,
1664 And pay rate determinant is 0, 5, 6, or 7,
1665 Then basic pay must be within the range for the grade
1666 on Table 32 or Table 33 (depending on pay rate
1667 determinant) or be asterisks.
1668
1669 Default: Insert asterisks in pay basis and basic pay.
1670 if p_to_pay_plan = 'NG' and
1671 p_rate_determinant_code in ('0','5','6','7') then -- added for bug 726125
1672 if p_rate_determinant_code in ('0','7') then
1673 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 32',
1674 p_to_grade_or_level ,'Minimum Basic Pay',p_effective_date);
1675 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 32',
1676 p_to_grade_or_level ,'Maximum Basic Pay',p_effective_date);
1677 elsif p_rate_determinant_code in ('5','6') then
1678 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 33',
1679 p_to_grade_or_level ,'Minimum Basic Pay',p_effective_date);
1680 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 33',
1681 p_to_grade_or_level ,'Maximum Basic Pay',p_effective_date);
1682 end if;
1683 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
1684 and (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
1685 or p_to_basic_pay is null)
1686 then
1687 GHR_GHRWS52L.CPDF_Parameter_Check;
1688 hr_utility.set_message(8301,'GHR_37858_ALL_PROCEDURE_FAIL');
1689 hr_utility.raise_error;
1690 end if;
1691 end if;
1692 */
1693
1694 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1695 650.51.1 If pay plan is NT,
1696 And pay rate determinant is 0, 5, 6, or 7,
1697 Then basic pay must be within the range for the grade
1698 on Table 34 or Table 35 (depending on pay rate
1699 determinant) or be asterisks.
1700
1701 Default: Insert asterisks in pay basis and basic pay.
1702 if p_to_pay_plan = 'NT' and
1703 p_rate_determinant_code in ('0','5','6','7') then -- added for bug 726125
1704 if p_rate_determinant_code in ('0','7') then
1705 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 34',
1706 p_to_grade_or_level ,'Minimum Basic Pay',p_effective_date);
1707 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 34',
1708 p_to_grade_or_level ,'Maximum Basic Pay',p_effective_date);
1709 elsif p_rate_determinant_code in ('5','6') then
1710 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 35',
1711 p_to_grade_or_level ,'Minimum Basic Pay',p_effective_date);
1712 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 35',
1713 p_to_grade_or_level ,'Maximum Basic Pay',p_effective_date);
1714 end if;
1715 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
1716 and (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
1717 or p_to_basic_pay is null)
1718 then
1719 GHR_GHRWS52L.CPDF_Parameter_Check;
1720 hr_utility.set_message(8301,'GHR_37860_ALL_PROCEDURE_FAIL');
1721 hr_utility.raise_error;
1722 end if;
1723 end if;
1724 */
1725
1726 /*650.53.1 If pay plan is AF, FO, or FP,
1727 And pay rate determinant is 0,
1728 Then basic pay must fall within the range for the grade
1729 on Table 10 or be asterisks.
1730
1731 Default: Insert asterisks in pay basis and basic pay.*/
1732 if p_to_pay_plan in ('AF','FO','FP') and
1733 p_rate_determinant_code = '0' then
1734
1735 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 10',
1736 p_to_grade_or_level || '-' || p_to_step_or_rate,
1737 'Minimum Basic Pay',p_effective_date);
1738
1739 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 10',
1740 p_to_grade_or_level || '-' || p_to_step_or_rate,
1741 'Maximum Basic Pay',p_effective_date);
1742
1743 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1744 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
1745 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
1746 then
1747 GHR_GHRWS52L.CPDF_Parameter_Check;
1748 hr_utility.set_message(8301,'GHR_37828_ALL_PROCEDURE_FAIL');
1749 hr_utility.raise_error;
1750 end if;
1751 end if;
1752
1753 -- Dec 2001 Patch -- Renamed Edit 650.57.1 to 650.57.3
1754 /*650.57.3 If pay plan begins with W, or is XE, XF, XG, or XH,
1755 And pay rate determinant is not A, B, E, F, U, or V,
1756 Then basic pay must fall within the range for the pay
1757 plan on Table 11 or be asterisks.
1758
1759 Default: Insert asterisks in pay basis and basic pay.*/
1760
1761 -- Update Date Updated By Effective Date Comments
1762 -----------------------------------------------------------------------------------------------------------
1763 -- 18/10/2004 Madhuri From the start of the edit Deleting the edit
1764 -----------------------------------------------------------------------------------------------------------
1765 /*if (substr(p_to_pay_plan,1,1) = 'W' or
1766 p_to_pay_plan in ('XE','XF','XG','XH')) and
1767 p_rate_determinant_code not in ('A','B','E','F','U','V') then
1768 max_basic_pay := NULL;
1769 min_basic_pay := NULL;
1770 if substr(p_to_pay_plan,1,1) = 'W' and
1771 p_to_pay_plan not in ('WG','WL','WM','WS') then
1772 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
1773 substr(p_to_pay_plan,1,1),
1774 'Maximum Basic Pay',
1775 p_effective_date);
1776 elsif p_to_pay_plan = 'XE' then
1777 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
1778 p_to_pay_plan,
1779 'Minimum Basic Pay',
1780 p_effective_date);
1781 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
1782 p_to_pay_plan,
1783 'Maximum Basic Pay',
1784 p_effective_date);
1785 else
1786 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
1787 p_to_pay_plan ||'-'|| p_to_grade_or_level,
1788 'Minimum Basic Pay',
1789 p_effective_date);
1790 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
1791 p_to_pay_plan||'-'|| p_to_grade_or_level,
1792 'Maximum Basic Pay',
1793 p_effective_date);
1794 end if;
1795 if max_basic_pay is not null and
1796 (not(to_number(p_to_basic_pay) between nvl(min_basic_pay,0) and nvl(max_basic_pay,0))
1797 or p_to_basic_pay is null)
1798 then
1799 GHR_GHRWS52L.CPDF_Parameter_Check;
1800 hr_utility.set_message(8301,'GHR_37829_ALL_PROCEDURE_FAIL');
1801 hr_utility.raise_error;
1802 end if;
1803 end if; */
1804 -----------------------------------------------------------------------------------------------------------
1805
1806 /*650.59.1 If retained pay plan begins with W, or is XF, XG, or XH,
1807 Then basic pay must fall within the range for the
1808 retained pay plan on Table 11 or be asterisks.
1809 Default: Insert asterisks in pay basis and basic pay.
1810
1811 Adds retained pay plan XE effective 1st of march 1998 */
1812 /*
1813 --
1814 -- Commented the edit for bug 557188 on 7-aug-98
1815 --
1816 if p_effective_date < fnd_date.canonical_to_date('1998/03/01') then
1817 if substr(p_retained_pay_plan,1,1) = 'W' or p_retained_pay_plan in ('XF','XG','XH') then
1818 if substr(p_retained_pay_plan,1,1) = 'W' and
1819 p_retained_pay_plan not in ('WG','WL','WM','WS') then
1820 min_basic_pay := 0; -- no check in table
1821 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
1822 substr(p_retained_pay_plan,1,1),
1823 'Maximum Basic Pay',p_effective_date);
1824 else
1825 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
1826 p_retained_pay_plan ||'-'|| p_retained_grade,
1827 'Minimum Basic Pay',p_effective_date);
1828 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
1829 p_retained_pay_plan||'-'|| p_retained_grade,
1830 'Maximum Basic Pay',p_effective_date);
1831 end if;
1832 if max_basic_pay IS NOT NULL then
1833 if (not(to_number(p_to_basic_pay) between nvl(min_basic_pay,0) and nvl(max_basic_pay,0))
1834 or p_to_basic_pay is null)
1835 then
1836 GHR_GHRWS52L.CPDF_Parameter_Check;
1837 hr_utility.set_message(8301,'GHR_37830_ALL_PROCEDURE_FAIL');
1838 hr_utility.raise_error;
1839 end if;
1840 end if;
1841 end if;
1842 else -- greater than 01-mar-1998
1843 if substr(p_retained_pay_plan,1,1) = 'W' or p_retained_pay_plan in ('XF','XG','XH','XE') then
1844 if substr(p_retained_pay_plan,1,1) = 'W' and
1845 p_retained_pay_plan not in ('WG','WL','WM','WS') then
1846 min_basic_pay := 0; -- no check in table
1847 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
1848 substr(p_retained_pay_plan,1,1),
1849 'Maximum Basic Pay',p_effective_date);
1850 elsif p_retained_pay_plan = 'XE' then
1851 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
1852 p_retained_pay_plan,
1853 'Minimum Basic Pay',p_effective_date);
1854 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
1855 p_retained_pay_plan,
1856 'Maximum Basic Pay',p_effective_date);
1857 else
1858 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
1859 p_retained_pay_plan ||'-'|| p_retained_grade,
1860 'Minimum Basic Pay',p_effective_date);
1861 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
1862 p_retained_pay_plan||'-'|| p_retained_grade,
1863 'Maximum Basic Pay',p_effective_date);
1864 end if;
1865 if max_basic_pay IS NOT NULL then
1866 if (not(to_number(p_to_basic_pay) between nvl(min_basic_pay,0) and nvl(max_basic_pay,0))
1867 or p_to_basic_pay is null)
1868 then
1869 GHR_GHRWS52L.CPDF_Parameter_Check;
1870 hr_utility.set_message(8301,'GHR_37861_ALL_PROCEDURE_FAIL');
1871 hr_utility.raise_error;
1872 end if;
1873 end if;
1874 end if;
1875 end if;
1876 */
1877
1878 -- Update/Change Date By Effective Date Comment
1879 -- 13-Jun-06 Raju 01-Jan-03 Terminate the edit
1880 /*650.60.1 If pay plan is VM,
1881 And pay rate determinant is 0,
1882 Then basic pay must fall within the range for the grade
1883 on Table 12 or be asterisks.
1884
1885 Default: Insert asterisks in pay basis and basic pay.*/
1886 IF p_effective_date < fnd_date.canonical_to_date('2003/01/01') then --Bug# 5073313
1887 if p_to_pay_plan = 'VM'
1888 and p_rate_determinant_code = '0' then
1889
1890 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 12',
1891 p_to_grade_or_level, 'Minimum Basic Pay',p_effective_date);
1892 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 12',
1893 p_to_grade_or_level, 'Maximum Basic Pay',p_effective_date);
1894
1895 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
1896 and (not(to_number(p_to_basic_pay) between nvl(min_basic_pay,0) and nvl(max_basic_pay,0))
1897 or p_to_basic_pay is null)
1898 then
1899 GHR_GHRWS52L.CPDF_Parameter_Check;
1900 hr_utility.set_message(8301,'GHR_37831_ALL_PROCEDURE_FAIL');
1901 hr_utility.raise_error;
1902 end if;
1903 end if;
1904 END IF;
1905
1906 /*650.63.1 If pay plan is VP,
1907 And pay rate determinant is 0,
1908 Then basic pay must be within the range for the grade
1909 on Table 13 or be asterisks.
1910
1911 Default: Insert asterisks in pay basis and basic pay.*/
1912 -- NAME EFF DATE COMMENTS
1913 -- Madhuri 19-MAY-2004 Deleting the edit by commenting
1914 --
1915
1916 /*if p_to_pay_plan = 'VP'
1917 and p_rate_determinant_code = '0' then
1918
1919 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 13',
1920 p_to_grade_or_level, 'Minimum Basic Pay',p_effective_date);
1921 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 13',
1922 p_to_grade_or_level, 'Maximum Basic Pay',p_effective_date);
1923
1924 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
1925 (not(to_number(p_to_basic_pay) between nvl(min_basic_pay,0) and nvl(max_basic_pay,0))
1926 or p_to_basic_pay is null)
1927 then
1928 GHR_GHRWS52L.CPDF_Parameter_Check;
1929 hr_utility.set_message(8301,'GHR_37832_ALL_PROCEDURE_FAIL');
1930 hr_utility.raise_error;
1931 end if;
1932 end if; */
1933
1934 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1935 650.66.1 If pay plan is VN,
1936 And pay rate determinant is 0,
1937 Then basic pay must be within the range for the grade
1938 on Table 14 or be asterisks.
1939
1940 Default: Insert asterisks in pay basis and basic pay.
1941 if p_to_pay_plan = 'VN'
1942 and p_rate_determinant_code = '0' then
1943
1944 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 14',
1945 p_to_grade_or_level, 'Minimum Basic Pay',p_effective_date);
1946 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 14',
1947 p_to_grade_or_level, 'Maximum Basic Pay',p_effective_date);
1948
1949 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
1950 and (not(to_number(p_to_basic_pay) between nvl(min_basic_pay,0) and nvl(max_basic_pay,0))
1951 or p_to_basic_pay is null)
1952 then
1953 GHR_GHRWS52L.CPDF_Parameter_Check;
1954 hr_utility.set_message(8301,'GHR_37833_ALL_PROCEDURE_FAIL');
1955 hr_utility.raise_error;
1956 end if;
1957 end if;
1958 */
1959
1960 /*650.72.1 If pay plan is SR,
1961 And agency/subelement is VA,
1962 And pay rate determinant is 0,
1963 Then basic pay must be within the range on Table 16 or
1964 be asterisks.
1965
1966 Default: Insert asterisks in pay plan, grade, step or
1967 rate, pay basis, and basic pay.
1968
1969 Basis for Edit: E.O.12496*/
1970
1971 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 06-Jun-2007
1972 if p_effective_date < fnd_date.canonical_to_date('2007/06/07') then
1973 if p_to_pay_plan = 'SR' and
1974 substr(p_agency_subelement,1,2) = 'VA' and
1975 p_rate_determinant_code = '0' then
1976
1977 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 16',
1978 p_to_pay_plan , 'Minimum Basic Pay',p_effective_date);
1979 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 16',
1980 p_to_pay_plan , 'Maximum Basic Pay',p_effective_date);
1981
1982 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
1983 and (not(to_number(p_to_basic_pay) between nvl(min_basic_pay,0) and nvl(max_basic_pay,0))
1984 or p_to_basic_pay is null)
1985 then
1986 GHR_GHRWS52L.CPDF_Parameter_Check;
1987 hr_utility.set_message(8301,'GHR_37834_ALL_PROCEDURE_FAIL');
1988 hr_utility.raise_error;
1989 end if;
1990 end if;
1991 end if;
1992 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1993 --650.73.3
1994 -- Update Date By Effective Date Comment
1995 -- 9/5 08/12/99 vravikan 01-Apr-99 New Edit
1996 If pay plan is NC, And pay rate determinant is 0, 5, 6, or 7, Then basic pay must
1997 be within the range for the grade on Table 44 or Table 45
1998 (depending on pay rate determinant) or be asterisks.
1999 If pay rate determinant is 0 or 7 then compare to table 44.
2000 If pay rate determinant is 5 or 6 then compare to table 45.
2001
2002 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') then
2003 if p_to_pay_plan = 'NC' then
2004 if p_rate_determinant_code in ('0','7') then
2005
2006 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 44',
2007 p_to_pay_plan , 'Minimum Basic Pay',p_effective_date);
2008 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 44',
2009 p_to_pay_plan , 'Maximum Basic Pay',p_effective_date);
2010
2011 elsif p_rate_determinant_code in ('5','6') then
2012 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 45',
2013 p_to_pay_plan , 'Minimum Basic Pay',p_effective_date);
2014 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 45',
2015 p_to_pay_plan , 'Maximum Basic Pay',p_effective_date);
2016 end if;
2017 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
2018 and (not(to_number(p_to_basic_pay) between nvl(min_basic_pay,0) and nvl(max_basic_pay,0))
2019 or p_to_basic_pay is null)
2020 then
2021 GHR_GHRWS52L.CPDF_Parameter_Check;
2022 hr_utility.set_message(8301,'GHR_37081_ALL_PROCEDURE_FAIL');
2023 hr_utility.raise_error;
2024 end if;
2025 end if;
2026 end if;
2027 */
2028 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
2029 --650.74.3
2030 -- Update Date By Effective Date Comment
2031 -- 9/5 08/12/99 vravikan 01-Apr-99 New Edit
2032 If pay plan is NO, And pay rate determinant is 0, 5, 6, or 7,
2033 Then basic pay must be within the range for the grade on Table 46 or Table 47
2034 (depending on pay rate determinant) or be asterisks.
2035 If pay rate determinant is 0 or 7 then compare to table 46.
2036 If pay rate determinant is 5 or 6 then compare to table 47.
2037
2038 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') then
2039 if p_to_pay_plan = 'NO' then
2040 if p_rate_determinant_code in ('0','7') then
2041
2042 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 46',
2043 p_to_pay_plan , 'Minimum Basic Pay',p_effective_date);
2044 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 46',
2045 p_to_pay_plan , 'Maximum Basic Pay',p_effective_date);
2046
2047 elsif p_rate_determinant_code in ('5','6') then
2048 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 47',
2049 p_to_pay_plan , 'Minimum Basic Pay',p_effective_date);
2050 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 47',
2051 p_to_pay_plan , 'Maximum Basic Pay',p_effective_date);
2052 end if;
2053 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
2054 and (not(to_number(p_to_basic_pay) between nvl(min_basic_pay,0) and nvl(max_basic_pay,0))
2055 or p_to_basic_pay is null)
2056 then
2057 GHR_GHRWS52L.CPDF_Parameter_Check;
2058 hr_utility.set_message(8301,'GHR_37078_ALL_PROCEDURE_FAIL');
2059 hr_utility.raise_error;
2060 end if;
2061 end if;
2062 end if;
2063 */
2064
2065 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
2066 --650.75.3
2067 -- Update Date By Effective Date Comment
2068 -- 9/5 08/12/99 vravikan 01-Apr-99 New Edit
2069 If pay plan is NP, And pay rate determinant is 0, 5, 6, or 7,
2070 Then basic pay must be within the range for the grade on Table 48 or Table 49
2071 (depending on pay rate determinant) or be asterisks.
2072 If pay rate determinant is 0 or 7 then compare to table 48.
2073 If pay rate determinant is 5 or 6 then compare to table 49.
2074
2075 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') then
2076 if p_to_pay_plan = 'NP' then
2077 if p_rate_determinant_code in ('0','7') then
2078
2079 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 48',
2080 p_to_pay_plan , 'Minimum Basic Pay',p_effective_date);
2081 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 48',
2082 p_to_pay_plan , 'Maximum Basic Pay',p_effective_date);
2083
2084 elsif p_rate_determinant_code in ('5','6') then
2085 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 49',
2086 p_to_pay_plan , 'Minimum Basic Pay',p_effective_date);
2087 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 49',
2088 p_to_pay_plan , 'Maximum Basic Pay',p_effective_date);
2089 end if;
2090 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
2091 and (not(to_number(p_to_basic_pay) between nvl(min_basic_pay,0) and nvl(max_basic_pay,0))
2092 or p_to_basic_pay is null)
2093 then
2094 GHR_GHRWS52L.CPDF_Parameter_Check;
2095 hr_utility.set_message(8301,'GHR_37079_ALL_PROCEDURE_FAIL');
2096 hr_utility.raise_error;
2097 end if;
2098 end if;
2099 end if;
2100 */
2101
2102 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
2103 --650.76.3
2104 -- Update Date By Effective Date Comment
2105 -- 9/5 08/12/99 vravikan 01-Apr-99 New Edit
2106 If pay plan is NR, And pay rate determinant is 0, 5, 6, or 7,
2107 Then basic pay must be within the range for the grade on Table 50 or Table 51
2108 (depending on pay rate determinant) or be asterisks.
2109 If pay rate determinant is 0 or 7 then compare to table 50.
2110 If pay rate determinant is 5 or 6 then compare to table 51.
2111 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') then
2112 if p_to_pay_plan = 'NR' then
2113 if p_rate_determinant_code in ('0','7') then
2114
2115 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 50',
2116 p_to_pay_plan , 'Minimum Basic Pay',p_effective_date);
2117 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 50',
2118 p_to_pay_plan , 'Maximum Basic Pay',p_effective_date);
2119
2120 elsif p_rate_determinant_code in ('5','6') then
2121 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 51',
2122 p_to_pay_plan , 'Minimum Basic Pay',p_effective_date);
2123 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 51',
2124 p_to_pay_plan , 'Maximum Basic Pay',p_effective_date);
2125 end if;
2126 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
2127 and (not(to_number(p_to_basic_pay) between nvl(min_basic_pay,0) and nvl(max_basic_pay,0))
2128 or p_to_basic_pay is null)
2129 then
2130 GHR_GHRWS52L.CPDF_Parameter_Check;
2131 hr_utility.set_message(8301,'GHR_37080_ALL_PROCEDURE_FAIL');
2132 hr_utility.raise_error;
2133 end if;
2134 end if;
2135 end if;
2136 */
2137 /*650.78.1 If pay plan is KA,
2138 Then basic pay must be within the range on Table 17 or
2139 be asterisks.
2140
2141 Default: Insert asterisks in pay basis and basic pay.*/
2142 -------------------------------------------------------------------------------
2143 -- Modified by Date Comments
2144 -------------------------------------------------------------------------------
2145 -- Madhuri 01-MAR-05 Retroactively end dating as of 31-JAN-2002
2146 -------------------------------------------------------------------------------
2147 IF p_effective_date <= fnd_date.canonical_to_date('20'||'02/01/31') THEN
2148 if p_to_pay_plan = 'KA' then
2149 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 17',
2150 p_to_pay_plan ,
2151 'Minimum Basic Pay',p_effective_date);
2152
2153 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 17',
2154 p_to_pay_plan ,
2155 'Maximum Basic Pay',p_effective_date);
2156
2157 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL then
2158 if (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
2159 or p_to_basic_pay is null or to_number(p_to_basic_pay) = 0)
2160 then
2161 GHR_GHRWS52L.CPDF_Parameter_Check;
2162 hr_utility.set_message(8301,'GHR_37835_ALL_PROCEDURE_FAIL');
2163 hr_utility.raise_error;
2164 end if;
2165 end if;
2166 end if;
2167 END IF;
2168 -------------------------------------------------------------------------------
2169
2170 --650.80.1 If agency/sub element is DD16,
2171 -- And pay plan is AD or TP,
2172 -- And pay basis is PD or SY,
2173 -- Then basic pay must be within the range on Table 24 or
2174 -- be asterisks.
2175 -- Update Date By Effective Date Comment
2176 -- 16-Oct-2002 vnarasim 01-JUN-2002 New Edit
2177 -- 28-Nov-2002 Madhuri If effective Date<=31stJul2002 looks
2178 -- into CPDF Edit Table 24 else into CPDF Table 54
2179 /* Commented the edit for bug#2956013 on 13-Oct-2003.
2180 if p_effective_date <= fnd_date.canonical_to_date('20'||'02/08/31') then
2181 if p_agency_subelement = 'DD16' and
2182 p_to_pay_plan in ('AD', 'TP') and
2183 p_to_pay_basis in ('PD','SY') then
2184
2185 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 24',
2186 p_to_pay_plan ,
2187 'Minimum Basic Pay',
2188 p_effective_date);
2189 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 24',
2190 p_to_pay_plan ,
2191 'Maximum Basic Pay',
2192 p_effective_date);
2193 if max_basic_pay IS NOT NULL and
2194 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
2195 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
2196 then
2197 GHR_GHRWS52L.CPDF_Parameter_Check;
2198 hr_utility.set_message(8301,'GHR_37836_ALL_PROCEDURE_FAIL');
2199 hr_utility.set_message_token('TABLE_NUMBER','24');
2200 hr_utility.raise_error;
2201 end if;
2202 end if;
2203 elsif p_effective_date >= to_date('2002/09/01','yyyy/mm/dd') then
2204 if p_agency_subelement = 'DD16' and
2205 p_to_pay_plan in ('AD', 'TP') and
2206 p_to_pay_basis in ('PD','SY') then
2207
2208 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 54',
2209 p_to_pay_plan ,
2210 'Minimum Basic Pay',
2211 p_effective_date);
2212 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 54',
2213 p_to_pay_plan ,
2214 'Maximum Basic Pay',
2215 p_effective_date);
2216 if max_basic_pay IS NOT NULL and
2217 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
2218 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
2219 then
2220 GHR_GHRWS52L.CPDF_Parameter_Check;
2221 hr_utility.set_message(8301,'GHR_37836_ALL_PROCEDURE_FAIL');
2222 hr_utility.set_message_token('TABLE_NUMBER','54');
2223 hr_utility.raise_error;
2224 end if;
2225 end if;
2226 end if;
2227 */
2228
2229 /*650.84.1 If pay plan is other than GG or GS,
2230 And retained pay plan is GG or GS,
2231 And retained step is 01 through 10,
2232 And pay rate determinant is E, F, or M,
2233 Then basic pay must be equal to or less than the entry
2234 for the retained grade on Table 19 or be asterisks.
2235
2236 Default: Insert asterisks in pay basis and basic pay.*/
2237 /*
2238 --
2239 -- Commented the edit for bug 557188 on 7-aug-98
2240 --
2241 if not(p_to_pay_plan in ('GS','GG'))
2242 and p_retained_pay_plan in ('GS','GG')
2243 and p_retained_step between '01' and '10'
2244 and p_rate_determinant_code in ('E','F','M') then
2245
2246 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 19',
2247 p_retained_grade,
2248 'Maximum Basic Pay',p_effective_date);
2249
2250 if max_basic_pay IS NOT NULL and
2251 (not(to_number(p_to_basic_pay) < max_basic_pay)
2252 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
2253 then
2254 GHR_GHRWS52L.CPDF_Parameter_Check;
2255 hr_utility.set_message(8301,'GHR_37840_ALL_PROCEDURE_FAIL');
2256 hr_utility.raise_error;
2257 end if;
2258 end if;
2259 */
2260
2261 /*650.85.1 If pay plan is other than GH or GM,
2262 And retained pay plan is GH or GM,
2263 And pay rate determinant is A or B,
2264 Then basic pay must fall within the range for the
2265 retained grade and retained step on Table 3 or be
2266 asterisks.
2267
2268 Default: Insert asterisks in pay basis and basic pay.*/
2269 /*
2270 --
2271 -- Commented the edit for bug 557188 on 7-aug-98
2272 --
2273 if not(p_to_pay_plan in ('GH','GM'))
2274 and p_retained_pay_plan in ('GH','GM')
2275 and p_rate_determinant_code in ('A','B') then
2276
2277 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
2278 p_retained_grade ||'-'|| p_retained_step,
2279 'Minimum Basic Pay',p_effective_date);
2280 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
2281 p_retained_grade ||'-'|| p_retained_step,
2282 'Maximum Basic Pay',p_effective_date);
2283
2284 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL and
2285 (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
2286 or p_to_basic_pay is null
2287 or to_number(p_to_basic_pay) = 0)
2288 then
2289 GHR_GHRWS52L.CPDF_Parameter_Check;
2290 hr_utility.set_message(8301,'GHR_37841_ALL_PROCEDURE_FAIL');
2291 hr_utility.raise_error;
2292 end if;
2293 end if;
2294 */
2295
2296 /*650.86.3 If pay plan is GS,
2297 And pay rate determinant is 5, 6, or M,
2298 Then basic pay must be equal to or less than the entry
2299 for grade on Table 19 or be asterisks.
2300 Default: Insert asterisks in pay basis and basic pay.*/
2301 -- Update Date By Effective Date Comment
2302 -- 06-Feb-2003 Madhuri Modified condition as
2303 -- p_to_basic_pay <= Max_basic_pay
2304 --
2305 /* Commenting the edit as per the bug 3147737
2306
2307 if p_to_pay_plan = 'GS'
2308 and p_rate_determinant_code in ('5','6','M') then
2309
2310 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 19',
2311 p_to_grade_or_level,
2312 'Maximum Basic Pay',p_effective_date);
2313
2314 if max_basic_pay IS NOT NULL
2315 and (not(to_number(p_to_basic_pay) <= max_basic_pay)
2316 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
2317 then
2318 GHR_GHRWS52L.CPDF_Parameter_Check;
2319 hr_utility.set_message(8301,'GHR_37842_ALL_PROCEDURE_FAIL');
2320 hr_utility.raise_error;
2321 end if;
2322 end if;
2323 */
2324 /*650.87.1 If pay plan is other than GH or GM,
2325 And retained pay plan is GH or GM,
2326 And pay rate determinant is E, F, or M,
2327 Then basic pay must fall within the range for the
2328 retained grade on Table 20 or be asterisks.
2329
2330 Default: Insert asterisks in pay basis and basic pay.*/
2331 /*
2332 --
2333 -- Commented the edit for bug 557188 on 7-aug-98
2334 --
2335 if not(p_to_pay_plan in ('GM','GH'))
2336 and p_retained_pay_plan in ('GM','GH')
2337 and p_rate_determinant_code in ('E','F','M') then
2338
2339 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 20',
2340 p_retained_grade,
2341 'Minimum Basic Pay',p_effective_date);
2342
2343 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 20',
2344 p_retained_grade,
2345 'Maximum Basic Pay',p_effective_date);
2346
2347 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
2348 and (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
2349 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
2350 then
2351 GHR_GHRWS52L.CPDF_Parameter_Check;
2352 hr_utility.set_message(8301,'GHR_37843_ALL_PROCEDURE_FAIL');
2353 hr_utility.raise_error;
2354 end if;
2355 end if;
2356 */
2357
2358 /*650.92.1 If pay plan is GG or GS,
2359 And pay rate determinant is E, F, or M,
2360 And retained pay plan is GG or GS,
2361 And retained step is 01 through 10,
2362 And retained grade is equal to or higher than grade,
2363 Then basic pay must be equal to or less than the entry
2364 for the retained grade on Table 19 or be asterisks.
2365
2366 Default: Insert asterisks in pay basis and basic pay.*/
2367
2368 /* Commenting the edit as per the bug 3147737
2369 if p_to_pay_plan in ('GS','GG')
2370 and p_retained_pay_plan in ('GS','GG')
2371 and p_rate_determinant_code in ('E','F','M')
2372 and p_retained_step between '01' and '10' then
2373
2374 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 19',
2375 p_retained_grade,
2376 'Maximum Basic Pay',p_effective_date);
2377
2378 if max_basic_pay IS NOT NULL
2379 and p_retained_grade >= p_to_grade_or_level
2380 and (not(to_number(p_to_basic_pay) < max_basic_pay)
2381 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
2382 then
2383 GHR_GHRWS52L.CPDF_Parameter_Check;
2384 hr_utility.set_message(8301,'GHR_37845_ALL_PROCEDURE_FAIL');
2385 hr_utility.raise_error;
2386 end if;
2387 end if;
2388 */
2389 /* Commented as per December 2000 cpdf changes -- vravikan
2390 650.95.1 If pay plan is GH or GM,
2391 And pay rate determinant is E, F, or M,
2392 And retained grade is equal to or higher than grade,
2393 And retained pay plan is GH or GM,
2394 Then basic pay must fall within the range for the
2395 retained grade on Table 20 or be asterisks.
2396
2397 Default: Insert asterisks in pay basis and basic pay.
2398
2399 if p_to_pay_plan in ('GM','GH')
2400 and p_retained_pay_plan in ('GM','GH')
2401 and p_rate_determinant_code in ('E','F','M') then
2402
2403 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 20',
2404 p_retained_grade,
2405 'Minimum Basic Pay',p_effective_date);
2406 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 20',
2407 p_retained_grade,
2408 'Maximum Basic Pay',p_effective_date);
2409
2410 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
2411 and p_retained_grade >= p_to_grade_or_level
2412 and (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
2413 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
2414 then
2415 GHR_GHRWS52L.CPDF_Parameter_Check;
2416 hr_utility.set_message(8301,'GHR_37846_ALL_PROCEDURE_FAIL');
2417 hr_utility.raise_error;
2418 end if;
2419 end if;
2420 */
2421
2422 /*650.96.1 If pay plan is AL,
2423 And pay rate determinant is not C,
2424 Then basic pay must match the entry for the grade and
2425 step or rate on Table 22 or be asterisks.
2426
2427 Default: Insert asterisks in pay basis and basic pay.
2428
2429 Basis for Edit: 5 U.S.C. 5372*/
2430
2431 if p_to_pay_plan = 'AL'
2432 and p_rate_determinant_code <> 'C' then
2433 if p_to_grade_or_level in ('01', '02') then
2434 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 22',
2435 p_to_grade_or_level,
2436 'Maximum Basic Pay',
2437 p_effective_date);
2438 else
2439 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 22',
2440 p_to_grade_or_level || '-' ||p_to_step_or_rate,
2441 'Maximum Basic Pay',
2442 p_effective_date);
2443 end if;
2444 if max_basic_pay IS NOT NULL
2445 and (to_number(p_to_basic_pay) <> max_basic_pay
2446 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
2447 then
2448 GHR_GHRWS52L.CPDF_Parameter_Check;
2449 hr_utility.set_message(8301,'GHR_37847_ALL_PROCEDURE_FAIL');
2450 hr_utility.raise_error;
2451 end if;
2452 end if;
2453
2454 /*650.97.1 If pay plan is CA,
2455 And pay rate determinant is not C,
2456 Then basic pay must match the entry on Table 23 for the
2457 grade or be asterisks.
2458
2459 Default: Insert asterisks in pay basis and basic pay.
2460
2461 Basis for Edit: 5 U.S.C. 5372a*/
2462 if p_to_pay_plan = 'CA'
2463 and p_rate_determinant_code <> 'C' then
2464
2465 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 23',
2466 p_to_grade_or_level,
2467 'Minimum Basic Pay',p_effective_date);
2468
2469 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
2470 and (to_number(p_to_basic_pay) <> max_basic_pay
2471 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
2472 then
2473 GHR_GHRWS52L.CPDF_Parameter_Check;
2474 hr_utility.set_message(8301,'GHR_37848_ALL_PROCEDURE_FAIL');
2475 hr_utility.raise_error;
2476 end if;
2477 end if;
2478
2479 /*650.98.1 If pay plan is ST or SL,
2480 And pay rate determinant is not C,
2481 Then basic pay must fall within the range on Table 21
2482 or be asterisks.
2483
2484 Default: Insert asterisks in pay basis and basic pay.
2485
2486 Basis for Edit: 5 U.S.C. 5376*/
2487 -- Upd57 30-Jul-09 Mani Bug # 8653515 14-OCT-2008 Added PRD D in the condition
2488
2489 if p_effective_date < fnd_date.canonical_to_date('2008/10/14') then
2490 if p_to_pay_plan in ('ST', 'SL')
2491 and p_rate_determinant_code NOT IN ('C') then
2492
2493 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
2494 p_to_pay_plan ,
2495 'Minimum Basic Pay',p_effective_date);
2496
2497 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
2498 p_to_pay_plan ,
2499 'Maximum Basic Pay',p_effective_date);
2500
2501 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
2502 and (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
2503 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
2504 then
2505 GHR_GHRWS52L.CPDF_Parameter_Check;
2506 hr_utility.set_message(8301,'GHR_37849_ALL_PROCEDURE_FAIL');
2507 hr_utility.set_message_token('PRD','not C');
2508 hr_utility.raise_error;
2509 end if;
2510 end if;
2511 else
2512 if p_to_pay_plan in ('ST', 'SL')
2513 and p_rate_determinant_code NOT IN ('C','D') then
2514
2515 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
2516 p_to_pay_plan ,
2517 'Minimum Basic Pay',p_effective_date);
2518
2519 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
2520 p_to_pay_plan ,
2521 'Maximum Basic Pay',p_effective_date);
2522
2523 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
2524 and (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay)
2525 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
2526 then
2527 GHR_GHRWS52L.CPDF_Parameter_Check;
2528 hr_utility.set_message(8301,'GHR_37849_ALL_PROCEDURE_FAIL');
2529 hr_utility.set_message_token('PRD','not C or D');
2530 hr_utility.raise_error;
2531 end if;
2532 end if;
2533 end if;
2534
2535
2536 /* 650.99.3 If pay plan is IJ,
2537 And pay rate determinant is 0 or 7,
2538 Then basic pay must match the entry for the step or
2539 rate on Table 36 or be asterisks.
2540
2541 Default: Insert asterisks in pay basis and basic pay. */
2542 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
2543 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
2544 if p_to_pay_plan = 'IJ' and
2545 p_rate_determinant_code in ('0','7') then
2546
2547 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 36',
2548 p_to_step_or_rate ,
2549 'Maximum Basic Pay',
2550 p_effective_date);
2551 if max_basic_pay IS NOT NULL and
2552 (not(to_number(p_to_basic_pay) <> max_basic_pay)
2553 or p_to_basic_pay is null)
2554 then
2555 GHR_GHRWS52L.CPDF_Parameter_Check;
2556 hr_utility.set_message(8301,'GHR_38551_ALL_PROCEDURE_FAIL');
2557 hr_utility.raise_error;
2558 end if;
2559 end if;
2560 end if;
2561 /*650.53.2 If pay plan is AF, FO, or FP,
2562 And pay rate determinant is not C,
2563 Then basic pay must fall within the range for the grade
2564 on Table 10 or be asterisks.
2565
2566 Default: Insert asterisks in pay basis and basic pay.*/
2567 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
2568 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
2569 if p_to_pay_plan in ('AF','FO','FP')
2570 and p_rate_determinant_code <> 'C' then
2571
2572 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 10',
2573 p_to_grade_or_level || '-' || p_to_step_or_rate,
2574 'Minimum Basic Pay',p_effective_date);
2575
2576 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 10',
2577 p_to_grade_or_level || '-' || p_to_step_or_rate,
2578 'Maximum Basic Pay',p_effective_date);
2579
2580 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
2581 and (not(to_number(p_to_basic_pay) between min_basic_pay and max_basic_pay )
2582 or p_to_basic_pay is null and to_number(p_to_basic_pay) = 0)
2583 then
2584 GHR_GHRWS52L.CPDF_Parameter_Check;
2585 hr_utility.set_message(8301,'GHR_37850_ALL_PROCEDURE_FAIL');
2586 hr_utility.raise_error;
2587 end if;
2588 end if;
2589 end if;
2590
2591 -- Dec 2001 Patch -- Rename Edit 650.57.2 to 650.57.3
2592 -- Commenting this one as there are two 650.57.3 edits
2593 /*650.57.3 If pay plan is W-, XE, XF, XG, or XH,
2594 And pay rate determinant is not A, B, E, F, U, or V,
2595 Then basic pay must fall within the range for the pay
2596 plan on Table 11 or be asterisks.
2597
2598 Default: Insert asterisks in pay basis and basic pay.
2599
2600 if (substr(p_to_pay_plan,1,1) = 'W' or
2601 p_to_pay_plan in ('XE','XF','XG','XH')) and
2602 p_rate_determinant_code not in ('A','B','E','F','U','V') then
2603 max_basic_pay := NULL;
2604 min_basic_pay := NULL;
2605 if substr(p_to_pay_plan,1,1) = 'W' and p_to_pay_plan not in ('WG','WL','WM','WS') then
2606 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
2607 substr(p_to_pay_plan,1,1),
2608 'Minimum Basic Pay',p_effective_date);
2609 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
2610 substr(p_to_pay_plan,1,1),
2611 'Maximum Basic Pay',p_effective_date);
2612 elsif p_to_pay_plan = 'XE' then
2613 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
2614 p_to_pay_plan,
2615 'Minimum Basic Pay',p_effective_date);
2616 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
2617 p_to_pay_plan,
2618 'Maximum Basic Pay',p_effective_date);
2619 else
2620 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
2621 p_to_pay_plan ||'-'|| p_to_grade_or_level,
2622 'Minimum Basic Pay',p_effective_date);
2623 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
2624 p_to_pay_plan ||'-'|| p_to_grade_or_level,
2625 'Maximum Basic Pay',p_effective_date);
2626 end if;
2627 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL then
2628 if (not(to_number(p_to_basic_pay) between nvl(min_basic_pay,0) and nvl(max_basic_pay,0))
2629 or p_to_basic_pay is null)
2630 then
2631 GHR_GHRWS52L.CPDF_Parameter_Check;
2632 hr_utility.set_message(8301,'GHR_37851_ALL_PROCEDURE_FAIL');
2633 hr_utility.raise_error;
2634 end if;
2635 end if;
2636 end if;
2637 */
2638 /*650.72.2 If pay plan is SR,
2639 And pay rate determinant is not C,
2640 And agency/subelement is VA,
2641 Then basic pay must be within the range shown on
2642 Table 16 or be asterisks.
2643
2644 Default: Insert asterisks in pay plan, grade, step or
2645 rate, pay basis, and basic pay.
2646
2647 Basis for Edit: E.O. 12496*/
2648 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
2649 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
2650 if p_to_pay_plan = 'SR' and
2651 substr(p_agency_subelement,1,2) = 'VA' and
2652 p_rate_determinant_code <> 'C' then
2653
2654 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 16',
2655 p_to_pay_plan , 'Minimum Basic Pay',p_effective_date);
2656 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 16',
2657 p_to_pay_plan , 'Maximum Basic Pay',p_effective_date);
2658
2659 if min_basic_pay IS NOT NULL and max_basic_pay IS NOT NULL
2660 and (not(to_number(p_to_basic_pay) between nvl(min_basic_pay,0) and nvl(max_basic_pay,0))
2661 or p_to_basic_pay is null)
2662 then
2663 GHR_GHRWS52L.CPDF_Parameter_Check;
2664 hr_utility.set_message(8301,'GHR_37853_ALL_PROCEDURE_FAIL');
2665 hr_utility.raise_error;
2666 end if;
2667 end if;
2668 end if;
2669 hr_utility.set_location('Leaving CPDF 8 ',1);
2670 end basic_pay;
2671 end ghr_cpdf_check8;
[Home] [Help]