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