1 PACKAGE BODY GHR_CPDF_CHECK3 AS
2 /* $Header: ghcpdf03.pkb 120.26 2011/02/14 10:35:28 utokachi ship $ */
3
4 max_per_diem number(5) := 1000;
5 min_basic_pay number(10,2);
6 max_basic_pay number(10,2);
7
8 /* Name:
9 -- Nature of Action
10 */
11
12 PROCEDURE chk_Nature_of_Action
13 (p_First_NOAC_Lookup_Code IN VARCHAR2
14 ,p_Second_NOAC_Lookup_code IN VARCHAR2
15 ,p_First_Action_NOA_Code1 IN VARCHAR2
16 ,p_First_Action_NOA_Code2 IN VARCHAR2
17 ,p_Cur_Appt_Auth_1 IN VARCHAr2 -- non SF52 item
18 ,p_Cur_Appt_Auth_2 IN VARCHAr2 -- non SF52 item
19 ,p_Employee_Date_of_Birth IN DATE
20 ,p_Duty_Station_Lookup_Code IN VARCHAR2
21 ,p_Employee_First_Name IN VARCHAR2
22 ,p_Employee_Last_Name IN VARCHAR2
23 ,p_Handicap IN VARCHAR2 -- non SF52 item
24 ,p_Organ_Component IN VARCHAR2 -- non SF52 item
25 ,p_Personal_Office_ID IN VARCHAR2 -- non SF52 item
26 ,p_Position_Occ_Code IN VARCHAR2
27 ,p_Race_National_Region IN VARCHAR2 -- non SF52 item
28 ,p_Retirement_Plan_Code IN VARCHAR2
29 ,p_Service_Computation_Date IN DATE
30 ,p_Sex IN VARCHAR2 -- non SF52 item
31 ,p_Supervisory_Status_Code IN VARCHAR2
32 ,p_Tenure_Group_Code IN VARCHAR2
33 ,p_Veterans_Pref_Code IN VARCHAR2
34 ,p_Veterans_Status_Code IN VARCHAR2
35 ,p_Occupation IN VARCHAR2 -- non SF52 item
36 ,p_To_Pay_Basis IN VARCHAR2
37 ,p_To_Grade_Or_Level IN VARCHAR2
38 ,p_To_Pay_Plan IN VARCHAR2
39 ,p_pay_rate_determinant_code IN VARCHAR2
40 ,p_To_Basic_Pay IN VARCHAR2
41 ,p_To_Step_Or_Rate IN VARCHAR2
42 ,p_Work_Sche_Code IN VARCHAR2
43 ,p_Prior_Occupation IN VARCHAR2 -- non SF52 item
44 ,p_Prior_To_Pay_Basis IN VARCHAR2 -- non SF52 item
45 ,p_Prior_To_Grade_Or_Level IN VARCHAR2 -- non SF52 item
46 ,p_Prior_To_Pay_Plan IN VARCHAR2 -- non SF52 item
47 ,p_Prior_Pay_Rate_Det_Code IN VARCHAR2 -- non SF52 item
48 ,p_Prior_To_Basic_Pay IN VARCHAR2 -- non SF52 item
49 ,p_Prior_To_Step_Or_Rate IN VARCHAR2 -- non SF52 item
50 ,p_Prior_Work_Sche_Code IN VARCHAR2 -- non SF52 item
51 ,p_prior_duty_station IN VARCHAR2
52 ,p_Retention_Allowance IN VARCHAR2 -- non SF52 item
53 ,p_Staff_Diff IN VARCHAR2 -- non SF52 item
54 ,p_Supervisory_Diff IN VARCHAR2 -- non SF52 item
55 ,p_To_Locality_Adj IN VARCHAR2
56 ,p_Prior_To_Locality_Adj IN VARCHAR2 -- non SF52 item
57 ,p_noa_family_code IN VARCHAR2
58 ,p_effective_date IN DATE
59 ,p_agency_subelement IN VARCHAR2
60 ,p_ethnic_race_info IN VARCHAR2 -- 17/11/2005 Raju 4567571(UPD45) Added this parameter
61 ,p_rating_of_record_level IN VARCHAR2 --non SF52 item Bug# 10189715
62 ,p_rating_of_record_pattern IN VARCHAR2 --non SF52 item Bug# 10189715
63 ) is
64
65 l_session ghr_history_api.g_session_var_type;
66 l_noa_family_code ghr_families.noa_family_code%type;
67 Cursor c_noa_family_code IS
68 Select fam.noa_family_code
69 from ghr_noa_families nfa,
70 ghr_families fam
71 where nfa.nature_of_action_id in ( select nature_of_action_id from
72 ghr_nature_of_actions where code = p_First_NOAC_Lookup_Code )
73 and nfa.noa_family_code = fam.noa_family_code
74 and fam.update_hr_flag = 'Y';
75
76
77 begin
78
79 -- 370.00.2 From Part C, Notes.
80 if (p_First_NOAC_Lookup_Code = '001'
81 or
82 p_First_NOAC_Lookup_Code = '002'
83 ) and
84 (p_Second_NOAC_Lookup_code = '001'
85 or
86 p_Second_NOAC_Lookup_code = '002'
87 or
88 p_Second_NOAC_Lookup_code is null
89 ) then
90 hr_utility.set_message(8301, 'GHR_37201_ALL_PROCEDURE_FAIL');
91 hr_utility.raise_error;
92 end if;
93
94
95 if (p_First_NOAC_Lookup_Code <> '001'
96 and
97 p_First_NOAC_Lookup_Code <> '002'
98 ) and
99 p_Second_NOAC_Lookup_code is not null
100 then
101 hr_utility.set_message(8301, 'GHR_37202_ALL_PROCEDURE_FAIL');
102 hr_utility.raise_error;
103 end if;
104
105 -- 370.02.2
106
107 -- Update Date By Effective Date Bug Comment
108 ----------------------------------------------------------------------------------------------------------
109 -- 07/11/02 vnarasim 2456012 Deleted the first name check.
110 -- 18/10/2004 Madhuri Instead of just 001 add 817 in the list
111 -- should this be for only first cond or for all?
112 -- 17/03/2005 Madhuri 4109207 817 to be included for all conditions except those
113 -- involving Emp DOB and emp last name.
114 -- 17/11/2005 Raju 4567571 Added the ethinicity and race identification condition UPD45
115 -- 28/10/2010 Raju 10189715 Added Rating of Record(Level)
116 ----------------------------------------------------------------------------------------------------------
117
118 if p_First_NOAC_Lookup_Code not in ('001','817') and
119 p_Cur_Appt_Auth_1 is null then
120 hr_utility.set_message(8301, 'GHR_37272_ALL_PROCEDURE_FAIL');
121 hr_utility.raise_error;
122 end if;
123 if p_First_NOAC_Lookup_Code <>'001' and
124 p_Employee_Date_of_Birth is null then
125 hr_utility.set_message(8301, 'GHR_37273_ALL_PROCEDURE_FAIL');
126 hr_utility.raise_error;
127 end if;
128 if p_First_NOAC_Lookup_Code not in ('001','817') and
129 p_Duty_Station_Lookup_Code is null then
130 hr_utility.set_message(8301, 'GHR_37274_ALL_PROCEDURE_FAIL');
131 hr_utility.raise_error;
132 end if;
133 if p_First_NOAC_Lookup_Code <>'001' and
134 ( p_Employee_Last_Name is null ) then
135 hr_utility.set_message(8301, 'GHR_37288_ALL_PROCEDURE_FAIL');
136 hr_utility.raise_error;
137 end if;
138 if p_First_NOAC_Lookup_Code not in ('001','817') and
139 p_Handicap is null then
140 hr_utility.set_message(8301, 'GHR_37276_ALL_PROCEDURE_FAIL');
141 hr_utility.raise_error;
142 end if;
143 if p_First_NOAC_Lookup_Code not in ('001','817') and
144 p_Organ_Component is null then
145 hr_utility.set_message(8301, 'GHR_37277_ALL_PROCEDURE_FAIL');
146 hr_utility.raise_error;
147 end if;
148 if p_First_NOAC_Lookup_Code not in ('001','817') and
149 p_Personal_Office_ID is null then
150 hr_utility.set_message(8301, 'GHR_37278_ALL_PROCEDURE_FAIL');
151 hr_utility.raise_error;
152 end if;
153 if p_First_NOAC_Lookup_Code not in ('001','817') and
154 p_Position_Occ_Code is null then
155 hr_utility.set_message(8301, 'GHR_37279_ALL_PROCEDURE_FAIL');
156 hr_utility.raise_error;
157 end if;
158 -- Begin Bug# 4567571
159
160 --IF p_effective_date < fnd_date.canonical_to_date('2005/10/01') then
161 IF p_effective_date < fnd_date.canonical_to_date('2006/01/01') then
162 IF p_First_NOAC_Lookup_Code NOT IN ('001','817') and
163 p_Race_National_Region IS NULL THEN
164 hr_utility.set_message(8301, 'GHR_37280_ALL_PROCEDURE_FAIL');
165 hr_utility.raise_error;
166 END IF;
167 --elsif p_effective_date >= fnd_date.canonical_to_date('2005/10/01') then
168 ELSIF p_effective_date >= fnd_date.canonical_to_date('2006/01/01') THEN
169 if p_First_NOAC_Lookup_Code not in ('001','817')
170 and p_effective_date < to_date('2006/07/01','yyyy/mm/dd')then
171 IF (p_Race_National_Region is null and p_ethnic_race_info is null) then
172 hr_utility.set_message(8301, 'GHR_38990_ALL_PROCEDURE_FAIL');
173 hr_utility.raise_error;
174 END IF;
175 elsif p_First_NOAC_Lookup_Code not in ('100','001','817') then
176 IF (p_Race_National_Region is null and p_ethnic_race_info is null) then
177 hr_utility.set_message(8301, 'GHR_38990_ALL_PROCEDURE_FAIL');
178 hr_utility.raise_error;
179 END IF;
180 end if;
181 end if;
182 -- End Bug# 4567571
183 if p_First_NOAC_Lookup_Code not in ('001','817') and
184 p_Retirement_Plan_Code is null then
185 hr_utility.set_message(8301, 'GHR_37281_ALL_PROCEDURE_FAIL');
186 hr_utility.raise_error;
187 end if;
188 if p_First_NOAC_Lookup_Code not in ('001','817') and
189 p_Service_Computation_Date is null then
190 hr_utility.set_message(8301, 'GHR_37282_ALL_PROCEDURE_FAIL');
191 hr_utility.raise_error;
192 end if;
193 if p_First_NOAC_Lookup_Code not in ('001','817') and
194 p_Supervisory_Status_Code is null then
195 hr_utility.set_message(8301, 'GHR_37283_ALL_PROCEDURE_FAIL');
196 hr_utility.raise_error;
197 end if;
198 if p_First_NOAC_Lookup_Code not in ('001','817') and
199 p_Tenure_Group_Code is null then
200 hr_utility.set_message(8301, 'GHR_37284_ALL_PROCEDURE_FAIL');
201 hr_utility.raise_error;
202 end if;
203 if p_First_NOAC_Lookup_Code not in ('001','817') and
204 p_Veterans_Pref_Code is null then
205 hr_utility.set_message(8301, 'GHR_37285_ALL_PROCEDURE_FAIL');
206 hr_utility.raise_error;
207 end if;
208 if p_First_NOAC_Lookup_Code not in ('001','817') and
209 p_Veterans_Status_Code is null then
210 hr_utility.set_message(8301, 'GHR_37286_ALL_PROCEDURE_FAIL');
211 hr_utility.raise_error;
212 end if;
213 if p_First_NOAC_Lookup_Code not in ('001','817') and
214 p_sex is null then
215 hr_utility.set_message(8301, 'GHR_37287_ALL_PROCEDURE_FAIL');
216 hr_utility.raise_error;
217 end if;
218 --Begin Bug 11726461
219 IF p_effective_date < fnd_date.canonical_to_date('2011/02/01') then
220 --Begin Bug 10189715
221 if p_first_noac_lookup_code not in ('001','817') and
222 p_rating_of_record_pattern <> 'Z' and
223 p_rating_of_record_level is null then
224 hr_utility.set_message(8301, 'GHR_38151_ALL_PROCEDURE_FAIL');
225 hr_utility.raise_error;
226 end if;
227 --End Bug 10189715
228 END IF;
229 --End Bug 11726461
230
231 -- 370.04.2
232 -- Update Date By Effective Date Comment
233 -- 8 01/28/99 vravikan 01/10/98 Add nature of action 6__
234 -- 02/24/00 vravikan 1186310 Getting noa_family_code rather
235 -- than using passed family code
236 hr_utility.set_location('passed noa_family_code is ' || p_noa_family_code,1);
237 FOR c_noa_family_code_rec in c_noa_family_code LOOP
238 l_noa_family_code := c_noa_family_code_rec.noa_family_code;
239 hr_utility.set_location('actual noa_family_code is ' || l_noa_family_code,2);
240 exit;
241 END LOOP;
242 if p_effective_date >= fnd_date.canonical_to_date('1998/10/01') then
243 if substr(p_First_NOAC_Lookup_Code,1,1) in ('1','2','5','6','7','8')
244 and
245 l_noa_family_code not in ('AWARD','GHR_STUDENT_LOAN','GHR_INCENTIVE')
246 and
247 ( p_Occupation is null or
248 p_To_Pay_Basis is null or
249 p_To_Grade_Or_Level is null or
250 p_To_Pay_Plan is null or
251 p_pay_rate_determinant_code is null or
252 p_To_Basic_Pay is null or
253 p_To_Step_Or_Rate is null or
254 p_Work_Sche_Code is null )
255 then
256 hr_utility.set_message(8301, 'GHR_37203_ALL_PROCEDURE_FAIL');
257 hr_utility.raise_error;
258 end if;
259 else
260 if substr(p_First_NOAC_Lookup_Code,1,1) in ('1','2','5','7','8')
261 and
262 l_noa_family_code NOT IN ('AWARD','GHR_INCENTIVE')
263 and
264 ( p_Occupation is null or
265 p_To_Pay_Basis is null or
266 p_To_Grade_Or_Level is null or
267 p_To_Pay_Plan is null or
268 p_pay_rate_determinant_code is null or
269 p_To_Basic_Pay is null or
270 p_To_Step_Or_Rate is null or
271 p_Work_Sche_Code is null )
272 then
273 hr_utility.set_message(8301, 'GHR_37204_ALL_PROCEDURE_FAIL');
274 hr_utility.raise_error;
275 end if;
276 end if;
277
278
279 -- 370.07.2
280 --
281 -- Update Date By Effective Date Comment
282 -- 8 01/28/99 vravikan 01/10/98 Add nature of action 6__
283 -- Dec 01 Patch 12/10/01 vravikan Exclude 815-816,825,
284 -- 840-847, and 878-879
285 -- 03/10/2003 vnarasim Exclude 848
286 -- 30/10/2003 Ashley Exclude 849
287 -- Get the session variable to check whether the action is correction
288 -- If correction skip this edit as the prior_duty_station value
289 -- might be incorrect. Bug #709282
290 --
291 --upd47 26-Jun-06 Raju From 01-May-2006 Exclude 849
292 --upd49 08-Jan-07 Raju From 01-Jun-2004 Bug#5619873 Add 826,827
293 -- upd51 06-Feb-07 Raju From 01-Jan-2007 Bug#5745356 add noa 849,885,886,887,889
294 ghr_history_api.get_g_session_var(l_session);
295 if p_effective_date >= to_date('01/10/1998', 'dd/mm/yyyy') then
296 if p_effective_date < fnd_date.canonical_to_date('2006/05/01') then
297 if l_session.noa_id_correct is null then
298 if p_effective_date < fnd_date.canonical_to_date('2004/06/01') then
299 if substr(p_First_NOAC_Lookup_Code,1,1) in ('3','4','5','6','7','8') and
300 p_First_NOAC_Lookup_Code not in ('815','816','817','825','840','841','842',
301 '843','844','845','846','847','848','849','878','879')
302 and
303 (p_Prior_Occupation is null or
304 p_Prior_To_Pay_Basis is null or
305 p_Prior_To_Grade_Or_Level is null or
306 p_Prior_To_Pay_Plan is null or
307 p_Prior_Pay_Rate_Det_Code is null or
308 p_Prior_To_Basic_Pay is null or
309 p_Prior_To_Step_Or_Rate is null or
310 p_Prior_Work_Sche_Code is null or
311 p_prior_Duty_Station is nulL
312 ) then
313 hr_utility.set_message(8301, 'GHR_37205_ALL_PROCEDURE_FAIL');
314 hr_utility.set_message_token('NOA_CODE','3--,4--,5--,6--, 7--, or 8--');
315 hr_utility.raise_error;
316 end if;
317 else -- p_effective_date < '2004/06/01'
318 if substr(p_First_NOAC_Lookup_Code,1,1) in ('3','4','5','6','7','8') and
319 p_First_NOAC_Lookup_Code not in ('815','816','817','825','826','827','840','841','842',
320 '843','844','845','846','847','848','849','878','879')
321 and
322 (p_Prior_Occupation is null or
323 p_Prior_To_Pay_Basis is null or
324 p_Prior_To_Grade_Or_Level is null or
325 p_Prior_To_Pay_Plan is null or
326 p_Prior_Pay_Rate_Det_Code is null or
327 p_Prior_To_Basic_Pay is null or
328 p_Prior_To_Step_Or_Rate is null or
329 p_Prior_Work_Sche_Code is null or
330 p_prior_Duty_Station is nulL
331 ) then
332 hr_utility.set_message(8301, 'GHR_37851_ALL_PROCEDURE_FAIL');
333 hr_utility.raise_error;
334 end if;
335 end if; -- p_effective_date < '2004/06/01'
336 end if; --l_session.noa_id_correct is null
337 elsif p_effective_date < fnd_date.canonical_to_date('2007/01/01') then --p_effective_date < '2006/05/01'
338 if l_session.noa_id_correct is null then
339 if substr(p_First_NOAC_Lookup_Code,1,1) in ('3','4','5','6','7','8') and
340 p_First_NOAC_Lookup_Code not in ('815','816','817','825','826','827','840','841','842',
341 '843','844','845','846','847','848','878','879')
342 and
343 (p_Prior_Occupation is null or
344 p_Prior_To_Pay_Basis is null or
345 p_Prior_To_Grade_Or_Level is null or
346 p_Prior_To_Pay_Plan is null or
347 p_Prior_Pay_Rate_Det_Code is null or
348 p_Prior_To_Basic_Pay is null or
349 p_Prior_To_Step_Or_Rate is null or
350 p_Prior_Work_Sche_Code is null or
351 p_prior_Duty_Station is nulL
352 ) then
353 hr_utility.set_message(8301, 'GHR_37200_ALL_PROCEDURE_FAIL');
354 hr_utility.set_message_token('NOA_CODE','815, 816, 817, 825, 826, 827, 840 through 848, 878, 879');
355 hr_utility.raise_error;
356 end if;
357 end if;
358 else --p_effective_date < '2006/05/01'
359 if l_session.noa_id_correct is null then
360 if substr(p_First_NOAC_Lookup_Code,1,1) in ('3','4','5','6','7','8') and
361 p_First_NOAC_Lookup_Code not in ('815','816','817','825','826','827','840','841','842',
362 '843','844','845','846','847','848','849','878','879','885','886','887','889')
363 and
364 (p_Prior_Occupation is null or
365 p_Prior_To_Pay_Basis is null or
366 p_Prior_To_Grade_Or_Level is null or
367 p_Prior_To_Pay_Plan is null or
368 p_Prior_Pay_Rate_Det_Code is null or
369 p_Prior_To_Basic_Pay is null or
370 p_Prior_To_Step_Or_Rate is null or
371 p_Prior_Work_Sche_Code is null or
372 p_prior_Duty_Station is nulL
373 ) then
374 hr_utility.set_message(8301, 'GHR_37200_ALL_PROCEDURE_FAIL');
375 hr_utility.set_message_token('NOA_CODE','815, 816, 817, 825, 826, 827, 840 through 849, 878, 879, 885, 886, 887, 889');
376 hr_utility.raise_error;
377 end if;
378 end if;
379 end if; --p_effective_date < '2006/05/01'
380 else -- p_effective_date >=01/10/1998'
381 if l_session.noa_id_correct is null then
382 if substr(p_First_NOAC_Lookup_Code,1,1) in ('3','4','5','7','8') and
383 p_First_NOAC_Lookup_Code not in ('815','816','817','825','840','841','842',
384 '843','844','845','846','847','848','849','878','879')
385 and
386 (p_Prior_Occupation is null or
387 p_Prior_To_Pay_Basis is null or
388 p_Prior_To_Grade_Or_Level is null or
389 p_Prior_To_Pay_Plan is null or
390 p_Prior_Pay_Rate_Det_Code is null or
391 p_Prior_To_Basic_Pay is null or
392 p_Prior_To_Step_Or_Rate is null or
393 p_Prior_Work_Sche_Code is null or
394 p_prior_Duty_Station is nulL
395 ) then
396 hr_utility.set_message(8301, 'GHR_37205_ALL_PROCEDURE_FAIL');
397 hr_utility.set_message_token('NOA_CODE','3--,4--,5--, 7--, or 8--');
398 hr_utility.raise_error;
399 end if;
400 end if;
401 end if;
402
403 -- 370.20.2
404 -- 17-Aug-00 vravikan From the Start Delete Staffing Differential
405 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
406 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
407 if p_First_NOAC_Lookup_Code ='810' and
408 p_First_Action_NOA_Code1 is null and
409 p_First_Action_NOA_Code2 is null and
410 (p_Retention_Allowance is null and
411 p_Supervisory_Diff is null
412 ) then
413 hr_utility.set_message(8301, 'GHR_37275_ALL_PROCEDURE_FAIL');
414 hr_utility.raise_error;
415 end if;
416 end if;
417
418
419 -- 370.25.2
420 -- 06/25/03 vravikan By Pass this edit if the employee has
421 -- RG temporary promotion
422 -- 09/29/06 amrchakr Changed the message as per Bug#5487271
423 -- 03/01/07 vmididho removed the comment related to pay_rate_determinant_code and
424 -- changed as per bug#5734491
425 IF GHR_GHRWS52L.g_temp_step IS NULL THEN
426 if p_To_Pay_Plan is null and
427 (p_To_Basic_Pay is not null or
428 p_To_Grade_Or_Level is not null or
429 p_To_Locality_Adj is not null or
430 p_To_Pay_Basis is not null or
431 p_pay_rate_determinant_code is not null or
432 p_To_Step_Or_Rate is not null
433 ) then
434
435 hr_utility.set_message(8301, 'GHR_37206_ALL_PROCEDURE_FAIL');
436 hr_utility.raise_error;
437 end if;
438 END IF;
439
440
441 -- 370.30.2
442 -- 09/29/06 amrchakr Changed the message as per Bug#5487271
443 if p_Prior_To_Pay_Plan is null and
444 (
445 p_Prior_To_Basic_Pay is not null or
446 p_Prior_To_Grade_Or_Level is not null or
447 p_Prior_To_Locality_Adj is not null or
448 p_Prior_To_Pay_Basis is not null or
449 p_Prior_Pay_Rate_Det_Code is not null or
450 p_Prior_To_Step_Or_Rate is not null
451 ) then
452
453 hr_utility.set_message(8301, 'GHR_37207_ALL_PROCEDURE_FAIL');
454 hr_utility.raise_error;
455 end if;
456 --370.35.2
457 -- 12/08/00 vravikan 01-oct-2000 New Edit
458 -- If nature of action is 600 through 610,
459 -- Then agency/subelement must be TD03
460
461 IF p_effective_date >= fnd_date.canonical_to_date('2000/10/01') then
462 IF p_First_NOAC_Lookup_Code between '600' and '610' and
463 p_agency_subelement <> 'TD03' then
464 hr_utility.set_message(8301, 'GHR_37664_ALL_PROCEDURE_FAIL');
465 hr_utility.raise_error;
466 END IF;
467 END IF;
468 --370.36.2
469 -- 12/08/00 vravikan 01-oct-2000 New Edit
470 -- If nature of action is 871,
471 -- Then Agency must be AF,AR,DD, or NV.
472
473 IF p_effective_date >= fnd_date.canonical_to_date('2000/10/01') then
474 IF p_First_NOAC_Lookup_Code = '871' and
475 substr(p_agency_subelement,1,2) not in ('AF','AR','DD','NV') then
476 hr_utility.set_message(8301, 'GHR_37665_ALL_PROCEDURE_FAIL');
477 hr_utility.raise_error;
478 END IF;
479 END IF;
480
481 -- 370.37.2
482 -- Upd 47 23-Jun-06 Raju From 01-Apr-06 New Edit
483 IF p_effective_date >= fnd_date.canonical_to_date('2006/04/01') THEN
484 IF p_First_NOAC_Lookup_Code IN ('611','612','613') AND
485 substr(p_agency_subelement,1,2) not in ('AF','AR','DD','NV') THEN
486 hr_utility.set_message(8301, 'GHR_37164_ALL_PROCEDURE_FAIL');
487 hr_utility.raise_error;
488 end if;
489 END IF;
490
491
492 --390.05.2
493 -- If Nature of Action is 817,
494 -- then Occupation Series (Job) must not be spaces.
495 --
496 -- Madhuri 19-MAY-2004 Adding new edit
497 --
498
499 IF ( p_First_NOAC_Lookup_Code = '817' and p_Occupation is null ) THEN
500 hr_utility.set_message(8301, 'GHR_38886_ALL_PROCEDURE_FAIL');
501 hr_utility.raise_error;
502 END IF;
503
504 end chk_Nature_of_Action;
505
506 /* Name:
507 -- Occupation
508 */
509
510 procedure chk_occupation
511 (p_to_pay_plan in varchar2
512 ,p_occ_code in varchar2
513 ,p_agency_sub in varchar2 -- non SF52 item
514 ,p_duty_station_lookup_code in varchar2
515 ,p_effective_date in date
516 ) is
517 begin
518
519 -- 390.10.1
520 -- 12/12/01 Change 2200 to 2500
521 -- U58 Raju 01-jan-2010 Bug 9503972
522 IF p_effective_date < fnd_date.canonical_to_date('2010/01/01') THEN
523 if ( substr(p_to_pay_plan,1,1) = 'G' or p_to_pay_plan = 'LG' or
524 p_to_pay_plan = 'ST' ) and
525 to_number(p_occ_code) >= 2500 and
526 p_occ_code is not null then
527 hr_utility.set_message(8301, 'GHR_37208_ALL_PROCEDURE_FAIL');
528 hr_utility.set_message_token('PAY_PLAN','LG, or ST');
529
530 hr_utility.raise_error;
531 end if;
532 ELSE
533 if ( substr(p_to_pay_plan,1,1) = 'G' or
534 p_to_pay_plan = 'ST' ) and
535 to_number(p_occ_code) >= 2500 and
536 p_occ_code is not null then
537 hr_utility.set_message(8301, 'GHR_37208_ALL_PROCEDURE_FAIL');
538 hr_utility.set_message_token('PAY_PLAN','or ST');
539 hr_utility.raise_error;
540 end if;
541 END IF;
542
543 -- 390.16.1
544 -- U58 Raju 01-jan-2010 Bug 9503972
545 IF p_effective_date < fnd_date.canonical_to_date('2010/01/01') THEN
546 if substr(p_to_pay_plan,1,1) in ('W','X', 'K') and
547 (to_number(p_occ_code) < 2499) then
548 hr_utility.set_message(8301, 'GHR_37209_ALL_PROCEDURE_FAIL');
549 hr_utility.set_message_token('PAY_PLAN','W-, X-, or K-');
550 hr_utility.raise_error;
551 end if;
552 ELSE
553 if substr(p_to_pay_plan,1,1) in ('W') and
554 (to_number(p_occ_code) < 2499) then
555 hr_utility.set_message(8301, 'GHR_37209_ALL_PROCEDURE_FAIL');
556 hr_utility.set_message_token('PAY_PLAN','W-');
557 hr_utility.raise_error;
558 end if;
559 END IF;
560
561 -- 390.31.1
562 if (p_occ_code = '0605' and substr(p_agency_sub,1,2) <>'VA') then
563 hr_utility.set_message(8301, 'GHR_37210_ALL_PROCEDURE_FAIL');
564 hr_utility.raise_error;
565 end if;
566
567 -- 390.43.1
568 --
569 -- removing occ code 2806 and 2808 as per update 7 on 17-jul-98
570 --
571 -- 12/8/00 vravikan From the Start Add 2806 and 2808
572 -- 11/28/02 Madhuri Commented edit
573 /* if p_occ_code in ('2619','2843','2806','2808')
574 and (
575 substr(p_agency_sub,1,2) <> 'DN'
576 )
577 then
578 hr_utility.set_message(8301, 'GHR_37211_ALL_PROCEDURE_FAIL');
579 hr_utility.raise_error;
580 end if;*/
581
582 /* Commented as per December 2000 cpdf changes -- vravikan
583 -- 390.44.1
584 --
585 -- Added the edit as per update 7 on 17-jul-98
586 --
587 if p_occ_code in ('2806','2808')
588 and (
589 substr(p_agency_sub,1,2) not in ('DN', 'IN07')
590 )
591 then
592 hr_utility.set_message(8301, 'GHR_37881_ALL_PROCEDURE_FAIL');
593 hr_utility.raise_error;
594 end if;
595
596 */
597 -- 390.45.1
598 --
599 -- 12/8/00 vravikan 01-Oct-2000 New Edit
600 -- Upd 43 09-NOV-05 Raju 01-Jul-2004 Terminate the Edit Effective 01-Jul-2004
601 if p_effective_date < fnd_date.canonical_to_date('2004/07/01') then
602 if p_effective_date >= fnd_date.canonical_to_date('2000/10/01') then
603 if p_occ_code in ('4431')
604 and (
605 substr(p_agency_sub,1,2) not in ('LP')
606 )
607 then
608 hr_utility.set_message(8301, 'GHR_37678_ALL_PROCEDURE_FAIL');
609 hr_utility.raise_error;
610 end if;
611 end if;
612 end if;
613
614 -- 390.47.1
615 --
616 -- 12/8/00 vravikan 01-Oct-2000 New Edit
617 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
618 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
619 if p_effective_date >= fnd_date.canonical_to_date('2000/10/01') then
620 if p_occ_code in ('0898','1398','1598')
621 and (
622 p_agency_sub not in ('CM57')
623 )
624 then
625 hr_utility.set_message(8301, 'GHR_37679_ALL_PROCEDURE_FAIL');
626 hr_utility.raise_error;
627 end if;
628 end if;
629 end if;
630 -- 390.07.2
631 -- 12/12/01 Change 2200 to 2500
632 -- U58 Raju 01-jan-2010 Bug 9503972
633 IF p_effective_date < fnd_date.canonical_to_date('2010/01/01') THEN
634 if ( p_to_pay_plan = 'ST' or p_to_pay_plan ='LG' or
635 substr(p_to_pay_plan,1,1) = 'G') and
636 to_number(p_occ_code) >= 2500 and
637 p_occ_code is not null then
638 hr_utility.set_message(8301, 'GHR_37212_ALL_PROCEDURE_FAIL');
639 hr_utility.set_message_token('PAY_PLAN','ST, or LG');
640 hr_utility.raise_error;
641 end if;
642 ELSE
643 if ( p_to_pay_plan = 'ST' or
644 substr(p_to_pay_plan,1,1) = 'G') and
645 to_number(p_occ_code) >= 2500 and
646 p_occ_code is not null then
647 hr_utility.set_message(8301, 'GHR_37212_ALL_PROCEDURE_FAIL');
648 hr_utility.set_message_token('PAY_PLAN','or ST');
649 hr_utility.raise_error;
650 end if;
651 END IF;
652
653 -- 390.13.2
654 -- U58 Raju 01-jan-2010 Bug 9503972
655 IF p_effective_date < fnd_date.canonical_to_date('2010/01/01') THEN
656 if substr(p_to_pay_plan,1,1) in ('W', 'X', 'K') and
657 to_number(p_occ_code) <= 2499 and
658 p_occ_code is not null then
659 hr_utility.set_message(8301, 'GHR_37213_ALL_PROCEDURE_FAIL');
660 hr_utility.set_message_token('PAY_PLAN','W-, X-, or K-');
661 hr_utility.raise_error;
662 end if;
663 ELSE
664 if substr(p_to_pay_plan,1,1) in ('W') and
665 to_number(p_occ_code) <= 2499 and
666 p_occ_code is not null then
667 hr_utility.set_message(8301, 'GHR_37213_ALL_PROCEDURE_FAIL');
668 hr_utility.set_message_token('PAY_PLAN','W-');
669 hr_utility.raise_error;
670 end if;
671 END IF;
672
673 -- 390.28.2
674 if p_occ_code = '0605' and
675 substr(p_agency_sub, 1, 2) <> 'VA'
676 then
677 hr_utility.set_message(8301, 'GHR_37214_ALL_PROCEDURE_FAIL');
678 hr_utility.raise_error;
679 end if;
680
681 /* Commented as per December 2000 cpdf changes -- vravikan
682 -- 390.34.2
683 if p_occ_code = '0805' and
684 not (
685 substr(p_agency_sub, 1, 2) in ('AF', 'AR', 'DD', 'NV')
686 and
687 substr(p_duty_station_lookup_code, 1, 2) in ('04', '06', '15', '32')
688 )
689 and
690 p_duty_station_lookup_code is not null
691 then
692 hr_utility.set_message(8301, 'GHR_37215_ALL_PROCEDURE_FAIL');
693 hr_utility.raise_error;
694 end if;
695
696 */
697 -- 390.40.2
698 --
699 -- removing occ code 2806 and 2808 as per update 7 on 17-jul-98
700 --
701 -- 12/8/00 vravikan From the Start Add 2806 and 2808
702 -- 11/28/02 Madhuri End Dated Edit on 31-Jul-2001
703 if p_effective_date <= to_date('2001/07/31','yyyy/mm/dd') then
704 if p_occ_code in ('2619','2806','2808','2843')
705 and
706 substr(p_agency_sub, 1, 2) <> 'DN'
707 then
708 hr_utility.set_message(8301, 'GHR_37216_ALL_PROCEDURE_FAIL');
709 hr_utility.raise_error;
710 end if;
711 end if;
712
713 /* Commented as per December 2000 cpdf changes -- vravikan
714 -- 390.41.2
715 --
716 -- new edit as per update 7 on 17-jul-98
717 --
718 if p_occ_code in ('2806', '2808')
719 and
720 substr(p_agency_sub, 1, 2) not in ('DN', 'IN07')
721 then
722 hr_utility.set_message(8301, 'GHR_37880_ALL_PROCEDURE_FAIL');
723 hr_utility.raise_error;
724 end if;
725
726 */
727
728 end chk_occupation;
729
730 /* Name:
731 -- Pay Basis
732 */
733
734 procedure chk_pay_basis
735 (p_to_pay_plan in varchar2
736 ,p_pay_basis in varchar2
737 ,p_basic_pay in varchar2
738 ,p_agency_subelement in varchar2 -- non SF52 item
739 ,p_occ_code in varchar2 --Bug# 5745356
740 ,p_effective_date in date
741 ,p_pay_rate_determinant_code in varchar2
742 ) is
743 l_basic_pay number;
744 l_exists varchar2(1);
745 -- cursor added by vravikan for converting basic pay for pay plans havi
746 --ng equivalent pay plan as 'FW'
747 -- Bug# 963123
748 cursor c_fw_pay_plans is
749 SELECT 'X'
750 FROM ghr_pay_plans
751 WHERE equivalent_pay_plan = 'FW'
752 AND pay_plan = p_to_pay_plan;
753
754
755 begin
756 -- 410.02.3
757 -- 17-Aug-00 vravikan From the Start Add one more condition
758 -- PRD is other than A,B,E,F,M,U or V
759 -- Dec 2001 Patch vravikan 01-Jul-01 Delete entry for pay plan 'TP'
760 -- UPD 43(Bug 4567571) Raju 09-Nov-2005 Delete PRD M effective date from 01-May-2005
761 if p_effective_date >= to_date('2005/05/01','yyyy/mm/dd') then
762 if p_pay_rate_determinant_code not in ('A','B','E','F','U','V') then
763 if (p_to_pay_plan = 'AL' and p_pay_basis <> 'PA' and p_pay_basis is not null ) or
764 (p_to_pay_plan = 'CA' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
765 (p_to_pay_plan = 'ES' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
766 (p_to_pay_plan = 'EX' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
767 (p_to_pay_plan = 'GG' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
768 (p_to_pay_plan = 'GH' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
769 (p_to_pay_plan = 'GS' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
770 (p_to_pay_plan = 'GM' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
771 (p_to_pay_plan = 'KA' and p_pay_basis not in ('PA','PH') and p_pay_basis is not null ) or
772 (p_to_pay_plan = 'SL' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
773 (substr(p_to_pay_plan,1,1) = 'X' and p_pay_basis <> 'PH' and p_pay_basis is not null ) or
774 (p_to_pay_plan = 'ZZ' and p_pay_basis <>'WC' and p_pay_basis is not null )
775 then
776 hr_utility.set_message(8301, 'GHR_37909_ALL_PROCEDURE_FAIL');
777 hr_utility.set_message_token('PRD_LIST','A, B, E, F, U, or V');
778 hr_utility.raise_error;
779 end if;
780 end if;
781 elsif p_effective_date >= to_date('2001/07/01','yyyy/mm/dd') then
782 if p_pay_rate_determinant_code not in ('A','B','E','F','M','U','V') then
783 if (p_to_pay_plan = 'AL' and p_pay_basis <> 'PA' and p_pay_basis is not null ) or
784 (p_to_pay_plan = 'CA' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
785 (p_to_pay_plan = 'ES' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
786 (p_to_pay_plan = 'EX' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
787 (p_to_pay_plan = 'GG' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
788 (p_to_pay_plan = 'GH' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
789 (p_to_pay_plan = 'GS' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
790 (p_to_pay_plan = 'GM' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
791 (p_to_pay_plan = 'KA' and p_pay_basis not in ('PA','PH') and p_pay_basis is not null ) or
792 (p_to_pay_plan = 'SL' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
793 (substr(p_to_pay_plan,1,1) = 'X' and p_pay_basis <> 'PH' and p_pay_basis is not null ) or
794 (p_to_pay_plan = 'ZZ' and p_pay_basis <>'WC' and p_pay_basis is not null )
795 then
796 hr_utility.set_message(8301, 'GHR_37909_ALL_PROCEDURE_FAIL');
797 hr_utility.set_message_token('PRD_LIST','A, B, E, F, M, U, or V');
798 hr_utility.raise_error;
799 end if;
800 end if;
801 else
802 if p_pay_rate_determinant_code not in ('A','B','E','F','M','U','V') then
803 if (p_to_pay_plan = 'AL' and p_pay_basis <> 'PA' and p_pay_basis is not null ) or
804 (p_to_pay_plan = 'CA' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
805 (p_to_pay_plan = 'ES' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
806 (p_to_pay_plan = 'EX' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
807 (p_to_pay_plan = 'GG' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
808 (p_to_pay_plan = 'GH' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
809 (p_to_pay_plan = 'GS' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
810 (p_to_pay_plan = 'GM' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
811 (p_to_pay_plan = 'KA' and p_pay_basis not in ('PA','PH') and p_pay_basis is not null ) or
812 (p_to_pay_plan = 'SL' and p_pay_basis <>'PA' and p_pay_basis is not null ) or
813 (p_to_pay_plan = 'TP' and p_pay_basis not in ('FB','PD','SY') and p_pay_basis is not null ) or
814 (substr(p_to_pay_plan,1,1) = 'X' and p_pay_basis <> 'PH' and p_pay_basis is not null ) or
815 (p_to_pay_plan = 'ZZ' and p_pay_basis <>'WC' and p_pay_basis is not null )
816 then
817 hr_utility.set_message(8301, 'GHR_37217_ALL_PROCEDURE_FAIL');
818 hr_utility.raise_error;
819 end if;
820 end if;
821 end if;
822
823
824 /*410.10.3 If pay basis is PD,
825 And agency/subelement is CU, FD, FL, FY, TRAJ, or TR35,
826 Then basic pay may not exceed 1000.
827
828 Default: Insert asterisks in pay basis and basic pay.*/
829
830 -- UPDATE_DATE UPDATED_BY EFFECTIVE_DATE COMMENTS
831 -----------------------------------------------------------------------------
832 -- 18-oct-04 Madhuri from start of edit Terminating the edit.
833 --
834 /* if p_pay_basis ='PD' and
835 (
836 substr(p_agency_subelement,1,2) in ('CU','FD','FL','FY') or
837 p_agency_subelement in ('TRAJ','TR35')
838 ) and
839 to_number(p_basic_pay) >max_per_diem
840 then
841 hr_utility.set_message(8301, 'GHR_37219_ALL_PROCEDURE_FAIL');
842 hr_utility.raise_error;
843 end if;*/
844
845 /*410.07.3 If pay basis is PA, PH, PM, BW, or WC,
846 Then basic pay must not be greater than the maximum
847 shown in Table 18.
848
849 Default: Insert asterisks in pay basis and basic pay.
850 Basis for Edit: CPDF processing requirement*/
851
852 -- UPDATE DATE UPDATE BY BUG NO COMMENTS
853 -------------------------------------------------------------------------------------------------------------
854 -- 18-Oct-04 Madhuri Modifying the existing edit as under from 11-JAN-2004.
855 -- If pay basis is BW, PA, PD, PH, or WC,
856 -- Then basic pay must be within the range for the pay basis shown in Table 56.
857 -- Splitting the error message for if and else part also.
858 -- 19-NOV-04 Madhuri Not splitting message. new message 38918 is being used now.
859 --- Modified for bug 4089960
860 --- 22-Apr-2005 Madhuri Modified the edit per Bug # 4307246, as under:
861 --- If Pay Basis is BW, PA, PD, PH, or WC AND Pay Plan is NOT AD,
862 --- then Basic Pay must be within the range for the Pay Basis shown on Table 56.
863 -- UPD 50(Bug 5745356) Raju 01-Oct-2006 Occupation code is added
864 -- 05-Aug-07 Raju 5132113 Added pay plans GP, GR to edits
865 --- ----------------------------------------------------------------------------------------------------------
866 IF ( p_effective_date < to_date('2004/01/11','yyyy/mm/dd') ) then
867 if p_pay_basis in ('PA', 'PH', 'PM', 'BW', 'WC') then
868 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 18',
869 p_pay_basis,
870 'Maximum Basic Pay',
871 p_effective_date);
872 if max_basic_pay IS NOT NULL then
873 --
874 -- this code is added for bug 657206 as the basic pay is multiplied by 2087
875 -- in the ghrws52l.pkb file for pay_plan of 'W_'
876 -- This is a temporary fix
877 --
878 /* Changed the code to reflect all the pay plans having equivalen
879 t pay plan 'FW' instead of
880 above Bug fix -- Ignore the above Comments -- Venkat 08/12/1999 -
881 - Refer to Bug# 963123 */
882 open c_fw_pay_plans;
883 fetch c_fw_pay_plans into l_exists;
884 if c_fw_pay_plans%found then
885 IF GHR_GHRWS52L.g_fw_annualize = 'Y' and p_pay_basis = 'PH' THEN
886 l_Basic_Pay := to_char(to_number(p_Basic_Pay)/2087);
887 ELSE
888 l_basic_pay := to_number(p_basic_pay);
889 END IF;
890 else
891 l_basic_pay := to_number(p_basic_pay);
892 end if;
893 close c_fw_pay_plans;
894
895 if l_basic_pay > max_basic_pay
896 then
897 hr_utility.set_message(8301, 'GHR_37218_ALL_PROCEDURE_FAIL');
898 hr_utility.raise_error;
899 end if;
900 end if;
901 end if;
902
903 ELSIF ( p_effective_date < to_date('2006/10/01','yyyy/mm/dd') ) then -- after >= 11th Jan 2004
904 -- From:If pay basis is PA, PH, PM, BW, or WC, Then basic pay must not be greater than the maximum shown in Table 18.
905 -- To: If pay basis is BW, PA, PD, PH, or WC, Then basic pay must be within the range for the pay basis shown in Table 56
906 --
907 If ( p_to_pay_plan not in('GP','GR', 'AD') ) THEN
908
909 if p_pay_basis in ('BW','PA', 'PD', 'PH', 'WC') then
910 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 56',
911 p_pay_basis,
912 'Maximum Basic Pay',
913 p_effective_date);
914
915 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 56',
916 p_pay_basis,
917 'Minimum Basic Pay',
918 p_effective_date);
919
920 if ( max_basic_pay IS NOT NULL and min_basic_pay IS NOT NULL ) then
921 --
922 -- this code is added for bug 657206 as the basic pay is multiplied by 2087
923 -- in the ghrws52l.pkb file for pay_plan of 'W_'
924 -- This is a temporary fix
925 --
926 /* Changed the code to reflect all the pay plans having equivalent
927 pay plan 'FW' instead of above Bug fix
928 -- Ignore the above Comments -- Venkat 08/12/1999 -
929 -- Refer to Bug# 963123 */
930
931 open c_fw_pay_plans;
932 fetch c_fw_pay_plans into l_exists;
933 if c_fw_pay_plans%found then
934 -- Bug 4089960
935 IF GHR_GHRWS52L.g_fw_annualize = 'Y' and p_pay_basis = 'PH' THEN
936 l_Basic_Pay := to_char(to_number(p_Basic_Pay)/2087);
937 ELSE
938 l_basic_pay := to_number(p_basic_pay);
939 END IF;
940 else
941 l_basic_pay := to_number(p_basic_pay);
942 end if;
943 close c_fw_pay_plans;
944 -- Bug 4089960
945
946 if ( l_basic_pay NOT BETWEEN min_basic_pay and max_basic_pay ) then
947 hr_utility.set_message(8301, 'GHR_38917_ALL_PROCEDURE_FAIL');
948 hr_utility.raise_error;
949 end if;
950 end if;
951 end if;
952 End if; -- Check for Pay Plan AD
953 ELSIF ( p_effective_date < to_date('2011/02/01','yyyy/mm/dd') ) then -- bug 11726461
954 If p_to_pay_plan not in('GP','GR', 'AD') THEN
955 if p_pay_basis in ('BW','PA', 'PD', 'PH', 'WC') and p_occ_code not in ('0602','0680') then
956 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 56',
957 p_pay_basis, 'Maximum Basic Pay', p_effective_date);
958
959 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 56',
960 p_pay_basis,'Minimum Basic Pay', p_effective_date);
961
962 if ( max_basic_pay IS NOT NULL and min_basic_pay IS NOT NULL ) then
963 open c_fw_pay_plans;
964 fetch c_fw_pay_plans into l_exists;
965 if c_fw_pay_plans%found then
966 IF GHR_GHRWS52L.g_fw_annualize = 'Y' and p_pay_basis = 'PH' THEN
967 l_Basic_Pay := to_char(to_number(p_Basic_Pay)/2087);
968 ELSE
969 l_basic_pay := to_number(p_basic_pay);
970 END IF;
971 else
972 l_basic_pay := to_number(p_basic_pay);
973 end if;
974 close c_fw_pay_plans;
975 if ( l_basic_pay NOT BETWEEN min_basic_pay and max_basic_pay ) then
976 hr_utility.set_message(8301, 'GHR_38554_ALL_PROCEDURE_FAIL');
977 hr_utility.set_message_token('PAY_BASIS','BW, PA, PD, PH, or WC');
978 hr_utility.raise_error;
979 end if;
980 end if;
981 end if;
982 End if; -- Check for Pay Plan AD
983 ELSE --Begin Bug 11726461 added PM
984 If p_to_pay_plan not in('GP','GR', 'AD') THEN
985 if p_pay_basis in ('BW','PA', 'PD', 'PH', 'PM', 'WC') and p_occ_code not in ('0602','0680') then
986 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 56',
987 p_pay_basis, 'Maximum Basic Pay', p_effective_date);
988
989 min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 56',
990 p_pay_basis,'Minimum Basic Pay', p_effective_date);
991
992 if ( max_basic_pay IS NOT NULL and min_basic_pay IS NOT NULL ) then
993 open c_fw_pay_plans;
994 fetch c_fw_pay_plans into l_exists;
995 if c_fw_pay_plans%found then
996 IF GHR_GHRWS52L.g_fw_annualize = 'Y' and p_pay_basis = 'PH' THEN
997 l_Basic_Pay := to_char(to_number(p_Basic_Pay)/2087);
998 ELSE
999 l_basic_pay := to_number(p_basic_pay);
1000 END IF;
1001 else
1002 l_basic_pay := to_number(p_basic_pay);
1003 end if;
1004 close c_fw_pay_plans;
1005 if ( l_basic_pay NOT BETWEEN min_basic_pay and max_basic_pay ) then
1006 hr_utility.set_message(8301, 'GHR_38554_ALL_PROCEDURE_FAIL');
1007 hr_utility.set_message_token('PAY_BASIS','BW, PA, PD, PH, PM, or WC');
1008 hr_utility.raise_error;
1009 end if;
1010 end if;
1011 end if;
1012 End if; -- Check for Pay Plan AD
1013 --End Bug 11726461
1014 END IF;
1015
1016 /*410.12.3 If pay basis is PD,
1017 And agency/subelement is not CU, FD, FL, FY, TRAJ,
1018 or TR35,
1019 Then basic pay may not exceed the maximum on Table 18.
1020
1021 Default: Insert asterisks in pay basis and basic pay.*/
1022
1023 -- UPDATE_DATE UPDATED_BY EFFECTIVE_DATE COMMENTS
1024 -----------------------------------------------------------------------------
1025 -- 18-oct-04 Madhuri from start of edit Terminating the edit.
1026 --
1027 /*
1028 if p_pay_basis = 'PD' and
1029 (substr(p_agency_subelement,1,2) in ('CU','FD','FL','FY') or
1030 p_agency_subelement in ('TRAJ','TR35')) then
1031
1032 max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 18',
1033 p_pay_basis,
1034 'Maximum Basic Pay',
1035 p_effective_date);
1036 if max_basic_pay is NOT NULL then
1037 --
1038 -- this code is added for bug 657206 as the basic pay is multiplied by 2087
1039 -- in the ghrws52l.pkb file for pay_plan of 'W_'
1040 --
1041 -- Changed the code to reflect all the pay plans having equivalent pay plan 'FW' instead of
1042 -- above Bug fix -- Ignore the above Comments -- Venkat 08/12/1999 -
1043 -- Refer to Bug# 963123
1044
1045 open c_fw_pay_plans;
1046 fetch c_fw_pay_plans into l_exists;
1047 if c_fw_pay_plans%found then
1048 l_basic_pay := to_number(p_basic_pay) / 2087;
1049 else
1050 l_basic_pay := to_number(p_basic_pay);
1051 end if;
1052 close c_fw_pay_plans;
1053
1054 if l_basic_pay > max_basic_pay then
1055 hr_utility.set_message(8301, 'GHR_37220_ALL_PROCEDURE_FAIL');
1056 hr_utility.raise_error;
1057 end if;
1058 end if;
1059 end if; */
1060
1061 end chk_pay_basis;
1062
1063 /* Name:
1064 -- Pay Grade
1065 */
1066
1067 procedure chk_pay_grade
1068 (p_to_pay_plan in varchar2
1069 ,p_to_grade_or_level in varchar2
1070 ,p_pay_rate_determinant_code in varchar2
1071 ,p_first_action_noa_la_code1 in varchar2
1072 ,p_first_action_noa_la_code2 in varchar2
1073 ,p_First_NOAC_Lookup_Code in varchar2
1074 ,p_Second_NOAC_Lookup_code in varchar2
1075 ,p_effective_date in date
1076 )is
1077 begin
1078
1079 -- 420.02.3
1080 -- Update Date By Effective Date Comment
1081 -- 9 04/29/99 vravikan Add Pay Plans CG,MG
1082 -- UPD 56 8309414 Raju 17-Apr-2008 Remove pay plan MG
1083 -- U58 Raju 01-jan-2010 Bug 9503972
1084 if p_effective_date < fnd_date.canonical_to_date('2008/04/17') then
1085 if p_to_pay_plan in ( 'CG','MG','WL','XG' )
1086 and p_to_grade_or_level not in
1087 ('01', '02', '03', '04', '05', '06', '07', '08',
1088 '09', '10', '11', '12', '13', '14', '15')
1089 and p_to_grade_or_level is not null then
1090 hr_utility.set_message(8301, 'GHR_37221_ALL_PROCEDURE_FAIL');
1091 hr_utility.set_message_token('PAY_PLAN','CG,MG,WL, or XG');
1092 hr_utility.raise_error;
1093 end if;
1094 ELSIF p_effective_date < fnd_date.canonical_to_date('2010/01/01') THEN
1095 if p_to_pay_plan in ( 'CG','WL','XG' )
1096 and p_to_grade_or_level not in
1097 ('01', '02', '03', '04', '05', '06', '07', '08',
1098 '09', '10', '11', '12', '13', '14', '15')
1099 and p_to_grade_or_level is not null then
1100 hr_utility.set_message(8301, 'GHR_37221_ALL_PROCEDURE_FAIL');
1101 hr_utility.set_message_token('PAY_PLAN','CG,WL, or XG');
1102 hr_utility.raise_error;
1103 end if;
1104 else
1105 if p_to_pay_plan in ( 'WL' )
1106 and p_to_grade_or_level not in
1107 ('01', '02', '03', '04', '05', '06', '07', '08',
1108 '09', '10', '11', '12', '13', '14', '15')
1109 and p_to_grade_or_level is not null then
1110 hr_utility.set_message(8301, 'GHR_37221_ALL_PROCEDURE_FAIL');
1111 hr_utility.set_message_token('PAY_PLAN','WL');
1112 hr_utility.raise_error;
1113 end if;
1114 end if;
1115
1116 -- 420.04.3
1117 -- U58 Raju 01-jan-2010 Bug 9503972
1118 IF p_effective_date < fnd_date.canonical_to_date('2010/01/01') THEN
1119 if (p_to_pay_plan = 'WS' or p_to_pay_plan ='XH') and
1120 p_to_grade_or_level not in
1121 ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10',
1122 '11', '12', '13', '14', '15', '16', '17', '18', '19') and
1123 p_to_grade_or_level is not null then
1124 hr_utility.set_message(8301, 'GHR_37222_ALL_PROCEDURE_FAIL');
1125 hr_utility.set_message_token('PAY_PLAN','WS, or XH');
1126 hr_utility.raise_error;
1127 end if;
1128 ELSE
1129 if (p_to_pay_plan = 'WS') and
1130 p_to_grade_or_level not in
1131 ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10',
1132 '11', '12', '13', '14', '15', '16', '17', '18', '19') and
1133 p_to_grade_or_level is not null then
1134 hr_utility.set_message(8301, 'GHR_37222_ALL_PROCEDURE_FAIL');
1135 hr_utility.set_message_token('PAY_PLAN','WS');
1136 hr_utility.raise_error;
1137 end if;
1138 END IF;
1139
1140 -- 420.07.3
1141 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1142 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1143 if (p_to_pay_plan = 'FA' and p_pay_rate_determinant_code <> 'S')
1144 and
1145 p_to_grade_or_level not in ('CA', 'CM', 'MC', 'NC', 'OC',
1146 '01', '02', '03', '04', '13', '14')
1147 and
1148 p_to_grade_or_level is not null
1149 then
1150 hr_utility.set_message(8301, 'GHR_37223_ALL_PROCEDURE_FAIL');
1151 hr_utility.raise_error;
1152 end if;
1153 end if;
1154 -- 420.10.3
1155 -- removed pay plan 'CY'
1156 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1157 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1158 if p_to_pay_plan ='CE' and
1159 p_to_grade_or_level not in ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10',
1160 '11', '12', '13', '14', '15', '16', '17')
1161 and
1162 p_to_grade_or_level is not null
1163 then
1164 hr_utility.set_message(8301, 'GHR_37224_ALL_PROCEDURE_FAIL');
1165 hr_utility.raise_error;
1166 end if;
1167 end if;
1168 -- 420.11.3
1169 -- added for the april release as per update 6 manual
1170 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1171 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1172 if p_to_pay_plan ='CY' and
1173 p_to_grade_or_level not in ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12',
1174 '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24')
1175 and
1176 p_to_grade_or_level is not null
1177 then
1178 hr_utility.set_message(8301, 'GHR_37882_ALL_PROCEDURE_FAIL');
1179 hr_utility.raise_error;
1180 end if;
1181 end if;
1182
1183 -- 420.13.3
1184 if p_to_pay_plan = 'GM' and
1185 p_to_grade_or_level not in ( '13', '14', '15')
1186 and
1187 p_to_grade_or_level is not null
1188 then
1189 hr_utility.set_message(8301, 'GHR_37225_ALL_PROCEDURE_FAIL');
1190 hr_utility.raise_error;
1191 end if;
1192
1193 -- 420.16.3
1194 if p_to_pay_plan = 'GS' and
1195 p_to_grade_or_level not in ('01', '02', '03', '04', '05', '06', '07', '08',
1196 '09', '10', '11', '12', '13', '14', '15')
1197 and
1198 p_to_grade_or_level is not null
1199 then
1200 hr_utility.set_message(8301, 'GHR_37226_ALL_PROCEDURE_FAIL');
1201 hr_utility.raise_error;
1202 end if;
1203
1204 -- 420.17.3
1205 --
1206 -- If pay plan is NH or NJ, then grade must be 01 through 04 or asterisks
1207 --
1208 -- U58 Raju 31-Dec-2009 Bug 9503972 Terminate the Edit
1209 if p_effective_date < fnd_date.canonical_to_date('2009/12/31') then
1210 if p_effective_date >= fnd_date.canonical_to_date('1998/03/01') then
1211 if p_to_pay_plan in ('NH', 'NJ') and
1212 p_to_grade_or_level not in ('01', '02', '03', '04') and
1213 p_to_grade_or_level is not null then
1214 hr_utility.set_message(8301, 'GHR_37877_ALL_PROCEDURE_FAIL');
1215 hr_utility.raise_error;
1216 end if;
1217 end if;
1218 end if;
1219
1220 -- 420.18.3
1221 --
1222 -- If pay plan is NK, then grade must be 01 through 03 or asterisks
1223 --
1224 -- U58 Raju 31-Dec-2009 Bug 9503972 Terminate the Edit
1225 if p_effective_date < fnd_date.canonical_to_date('2009/12/31') then
1226 if p_effective_date >= fnd_date.canonical_to_date('1998/03/01') then
1227 if p_to_pay_plan = 'NK' and
1228 p_to_grade_or_level not in ('01', '02', '03') and
1229 p_to_grade_or_level is not null then
1230 hr_utility.set_message(8301, 'GHR_37878_ALL_PROCEDURE_FAIL');
1231 hr_utility.raise_error;
1232 end if;
1233 end if;
1234 end if;
1235
1236 -- 420.19.3
1237 -- Update Date By Effective Date Comment
1238 -- 8 01/28/99 vravikan 01/10/98 New Edit
1239 -- If pay plan is FV, then grade must be 'AA' through 'MM' or asterisks
1240 -- 13-Jun-06 Raju 01-Jan-03 Terminate the edit
1241 --
1242 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1243 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1244 if p_effective_date >= fnd_date.canonical_to_date('1998/10/01') and
1245 p_effective_date < fnd_date.canonical_to_date('2003/01/01') then --Bug# 5073313
1246 if p_to_pay_plan = 'FV' and
1247 p_to_grade_or_level not in ('AA','BB','CC','DD','EE','FF','GG','HH','II',
1248 'JJ','KK','LL','MM') and
1249 p_to_grade_or_level is not null then
1250 hr_utility.set_message(8301, 'GHR_37030_ALL_PROCEDURE_FAIL');
1251 hr_utility.raise_error;
1252 end if;
1253 end if;
1254 end if;
1255 -- 420.21.1
1256 --
1257 -- Update Date By Effective Date Comment
1258 -- 8 04/01/99 vravikan 01/10/98 New Edit
1259 -- If pay plan is JA, then grade must be 01 through 04 or asterisks
1260 --
1261 -- UPD 41(Bug 4567571) Raju 08-Nov-2005 Terminate from 01-Jul-2004
1262 if p_effective_date < to_date('2004/07/01','yyyy/mm/dd') then
1263 if p_effective_date >= fnd_date.canonical_to_date('1998/10/01') then
1264 if p_to_pay_plan = 'JA' and
1265 p_to_grade_or_level not in ('01', '02', '03','04') and
1266 p_to_grade_or_level is not null then
1267 hr_utility.set_message(8301, 'GHR_37056_ALL_PROCEDURE_FAIL');
1268 hr_utility.raise_error;
1269 end if;
1270 end if;
1271 end if;
1272
1273 -- 420.20.3
1274 --
1275 -- Update Date By Effective Date Comment
1276 -- 8 01/28/99 vravikan 01/10/98 New Edit
1277 -- If pay plan is EV, then grade must be 01 through 03 or asterisks
1278 --
1279 -- U58 Raju 31-Dec-2009 Bug 9503972 Terminate the Edit
1280 if p_effective_date < fnd_date.canonical_to_date('2009/12/31') then
1281 if p_effective_date >= fnd_date.canonical_to_date('1998/10/01') then
1282 if p_to_pay_plan = 'EV' and
1283 p_to_grade_or_level not in ('01', '02', '03') and
1284 p_to_grade_or_level is not null then
1285 hr_utility.set_message(8301, 'GHR_37032_ALL_PROCEDURE_FAIL');
1286 hr_utility.raise_error;
1287 end if;
1288 end if;
1289 end if;
1290
1291 -- 420.22.3
1292 -- Update Date By Effective Date Comment
1293 -- 13-Jun-06 Raju 01-Jan-03 Terminate the edit
1294 IF p_effective_date < fnd_date.canonical_to_date('2003/01/01') then --Bug# 5073313
1295 if p_to_pay_plan = 'VM' and
1296 p_to_grade_or_level not in ('11', '12', '13', '14', '15', '96', '97') and
1297 p_to_grade_or_level is not null then
1298 hr_utility.set_message(8301, 'GHR_37227_ALL_PROCEDURE_FAIL');
1299 hr_utility.raise_error;
1300 end if;
1301 END IF;
1302 -- 420.23.3
1303 -- Update/Change Date By Effective Date Comment
1304 -- 9/2 08/09/99 vravikan 01-Mar-1999 New Edit
1305 -- If pay plan is EZ, then grade must be 01 through 08.
1306 --
1307 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1308 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1309 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/03/01') then
1310 if p_to_pay_plan = 'EZ' and
1311 p_to_grade_or_level not in ('01','02','03','04','05','06','07','08')
1312 and
1313 p_to_grade_or_level is not null
1314 then
1315 hr_utility.set_message(8301, 'GHR_37059_ALL_PROCEDURE_FAIL');
1316 hr_utility.raise_error;
1317 end if;
1318 end if;
1319 end if;
1320
1321 -- 420.25.3
1322 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1323 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1324 if p_to_pay_plan = 'VN' and
1325 p_to_grade_or_level not in ('01', '02', '03', '04', '05', '06', '08',
1326 '09', '11', '12', '13', '14', '15')
1327 and
1328 p_to_grade_or_level is not null
1329 then
1330 hr_utility.set_message(8301, 'GHR_37228_ALL_PROCEDURE_FAIL');
1331 hr_utility.raise_error;
1332 end if;
1333 end if;
1334 -- 420.28.3
1335 if p_to_pay_plan = 'VP' and
1336 p_to_grade_or_level not in ('11', '12', '13', '14', '15')
1337 and
1338 p_to_grade_or_level is not null
1339 then
1340 hr_utility.set_message(8301, 'GHR_37229_ALL_PROCEDURE_FAIL');
1341 hr_utility.raise_error;
1342 end if;
1343
1344 -- 420.31.3
1345 -- U58 Raju 01-jan-2010 Bug 9503972
1346 IF p_effective_date < fnd_date.canonical_to_date('2010/01/01') THEN
1347 if (p_to_pay_plan = 'WG' or p_to_pay_plan = 'XF') and
1348 p_to_grade_or_level not in
1349 ('01', '02', '03', '04', '05', '06', '07', '08',
1350 '09', '10', '11', '12', '13', '14', '15') and
1351 p_to_grade_or_level is not null then
1352 hr_utility.set_message(8301, 'GHR_37230_ALL_PROCEDURE_FAIL');
1353 hr_utility.set_message_token('PAY_PLAN','WG or XF');
1354 hr_utility.raise_error;
1355 end if;
1356 ELSE
1357 if (p_to_pay_plan = 'WG') and
1358 p_to_grade_or_level not in
1359 ('01', '02', '03', '04', '05', '06', '07', '08',
1360 '09', '10', '11', '12', '13', '14', '15') and
1361 p_to_grade_or_level is not null then
1362 hr_utility.set_message(8301, 'GHR_37230_ALL_PROCEDURE_FAIL');
1363 hr_utility.set_message_token('PAY_PLAN','WG');
1364 hr_utility.raise_error;
1365 end if;
1366 END IF;
1367
1368 -- 420.34.3
1369 if p_to_pay_plan = 'EX' and
1370 p_to_grade_or_level not in ('01', '02', '03', '04', '05')
1371 and
1372 p_to_grade_or_level is not null
1373 then
1374 hr_utility.set_message(8301, 'GHR_37231_ALL_PROCEDURE_FAIL');
1375 hr_utility.raise_error;
1376 end if;
1377
1378 /* Commenting the edit as per Sept.2000 patch -- refer to doc cpdf_edits_00_sep.doc
1379 -- 420.37.3
1380 if p_to_pay_plan = 'OC' and
1381 p_to_grade_or_level not in
1382 ('01', '02', '03', '04', '05', '06', '07',
1383 '08', '09', '10', '11', '12', '13', '14',
1384 '15', '16', '17', '18', '19', '20', '21',
1385 '22', '23', '24', '25')
1386 and
1387 p_to_grade_or_level is not null
1388 then
1389 hr_utility.set_message(8301, 'GHR_37232_ALL_PROCEDURE_FAIL');
1390 hr_utility.raise_error;
1391 end if;
1392 */
1393
1394 -- 420.40.3
1395 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1396 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1397 if p_to_pay_plan = 'DR' and
1398 p_to_grade_or_level not in ('01', '02', '03', '04') and
1399 p_to_grade_or_level is not null
1400 then
1401 hr_utility.set_message(8301, 'GHR_37271_ALL_PROCEDURE_FAIL');
1402 hr_utility.raise_error;
1403 end if;
1404 end if;
1405
1406 -- 420.41.3
1407 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1408 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1409 if (p_to_pay_plan = 'ND' or p_to_pay_plan = 'NT' )
1410 and
1411 p_to_grade_or_level not in ('01', '02', '03', '04',
1412 '05', '06')
1413 and
1414 p_to_grade_or_level is not null
1415 then
1416 hr_utility.set_message(8301, 'GHR_37269_ALL_PROCEDURE_FAIL');
1417 hr_utility.raise_error;
1418 end if;
1419 end if;
1420 -- 420.42.3
1421 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1422 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1423 if p_to_pay_plan = 'NG'
1424 and
1425 p_to_grade_or_level not in ('01', '02', '03', '04',
1426 '05')
1427 and
1428 p_to_grade_or_level is not null
1429 then
1430 hr_utility.set_message(8301, 'GHR_37270_ALL_PROCEDURE_FAIL');
1431 hr_utility.raise_error;
1432 end if;
1433 end if;
1434
1435 -- 420.44.3
1436 if p_to_pay_plan = 'FO' and
1437 p_to_grade_or_level not in ('01', '02', '03', '04',
1438 '05', '06', '07', '08')
1439 and
1440 p_to_grade_or_level is not null
1441 then
1442 hr_utility.set_message(8301, 'GHR_37233_ALL_PROCEDURE_FAIL');
1443 hr_utility.raise_error;
1444 end if;
1445
1446 -- 420.45.3
1447 -- U58 Raju 01-jan-2010 Bug 9503972
1448 IF p_effective_date < fnd_date.canonical_to_date('2010/01/01') THEN
1449 if p_to_pay_plan = 'FP' and
1450 p_to_grade_or_level not in ('01', '02', '03', '04', '05',
1451 '06', '07', '08', '09', 'AA',
1452 'BB', 'CC', 'DD', 'EE') and
1453 p_to_grade_or_level is not null then
1454 hr_utility.set_message(8301, 'GHR_37234_ALL_PROCEDURE_FAIL');
1455 hr_utility.set_message_token('PAY_PLAN',', AA through EE');
1456 hr_utility.raise_error;
1457 end if;
1458 ELSE
1459 if p_to_pay_plan = 'FP' and
1460 p_to_grade_or_level not in ('01', '02', '03', '04', '05',
1461 '06', '07', '08', '09') and
1462 p_to_grade_or_level is not null then
1463 hr_utility.set_message(8301, 'GHR_37234_ALL_PROCEDURE_FAIL');
1464 hr_utility.set_message_token('PAY_PLAN','');
1465 hr_utility.raise_error;
1466 end if;
1467 END IF;
1468
1469 -- 420.47.3
1470 if p_to_pay_plan = 'FE' and
1471 p_to_grade_or_level not in ('CA', 'CM', 'MC', 'OC',
1472 '01', '02', '03')
1473 and
1474 p_to_grade_or_level is not null
1475 then
1476 hr_utility.set_message(8301, 'GHR_37235_ALL_PROCEDURE_FAIL');
1477 hr_utility.raise_error;
1478 end if;
1479
1480 -- 420.50.3
1481 -- U58 Raju 31-Dec-2009 Bug 9503972 Terminate the Edit
1482 if p_effective_date < fnd_date.canonical_to_date('2009/12/31') then
1483 if p_to_pay_plan = 'AF' and
1484 p_to_grade_or_level not in ('AA', 'BB', 'CC', 'DD', 'EE') and
1485 p_to_grade_or_level is not null then
1486 hr_utility.set_message(8301, 'GHR_37236_ALL_PROCEDURE_FAIL');
1487 hr_utility.raise_error;
1488 end if;
1489 end if;
1490
1491 -- 420.51.3
1492 -- U58 Raju 31-Dec-2009 Bug 9503972 Terminate the Edit
1493 if p_effective_date < fnd_date.canonical_to_date('2009/12/31') then
1494 if p_to_pay_plan = 'FM' and
1495 p_to_grade_or_level not in ('13', '14','15')
1496 and
1497 p_to_grade_or_level is not null
1498 then
1499 hr_utility.set_message(8301, 'GHR_37237_ALL_PROCEDURE_FAIL');
1500 hr_utility.raise_error;
1501 end if;
1502 end if;
1503
1504 -- 420.52.3
1505 -- U58 Raju 31-Dec-2009 Bug 9503972 Terminate the Edit
1506 if p_effective_date < fnd_date.canonical_to_date('2009/12/31') then
1507 if p_to_pay_plan = 'FG' and
1508 p_to_grade_or_level not in ('01', '02', '03', '04', '05', '06',
1509 '07', '08', '09', '10','11', '12', '13', '14', '15')
1510 and
1511 p_to_grade_or_level is not null
1512 then
1513 hr_utility.set_message(8301, 'GHR_37238_ALL_PROCEDURE_FAIL');
1514 hr_utility.raise_error;
1515 end if;
1516 end if;
1517
1518 -- 420.53.3
1519 -- U58 Raju 31-Dec-2009 Bug 9503972 Terminate the Edit
1520 if p_effective_date < fnd_date.canonical_to_date('2009/12/31') then
1521 if p_to_pay_plan = 'FC' and
1522 p_to_grade_or_level not in ('02', '03', '04', '05', '06', '07', '08',
1523 '09', '10', '11', '12', '13', '14')
1524 and
1525 p_to_grade_or_level is not null
1526 then
1527 hr_utility.set_message(8301, 'GHR_37239_ALL_PROCEDURE_FAIL');
1528 hr_utility.raise_error;
1529 end if;
1530 end if;
1531
1532 -- 420.55.3
1533 if (p_to_pay_plan = 'AL' or p_to_pay_plan = 'CA') and
1534 p_to_grade_or_level not in ('01', '02', '03')
1535 and
1536 p_to_grade_or_level is not null
1537 then
1538 hr_utility.set_message(8301, 'GHR_37240_ALL_PROCEDURE_FAIL');
1539 hr_utility.raise_error;
1540 end if;
1541
1542 -- 420.59.2
1543 if (
1544 p_first_action_noa_la_code1 = 'J8M'
1545 or
1546 p_first_action_noa_la_code2 = 'J8M'
1547 )and
1548 p_to_pay_plan in ('GG', 'GS') and
1549 p_First_NOAC_Lookup_Code in ('170','570')
1550 and
1551 p_to_grade_or_level not in ('01', '02', '03', '04', '05', '06', '07',
1552 '08', '09', '10', '11')
1553 and
1554 p_to_grade_or_level is not null
1555 then
1556 hr_utility.set_message(8301, 'GHR_37241_ALL_PROCEDURE_FAIL');
1557 hr_utility.raise_error;
1558 end if;
1559
1560 -- 420.60.3
1561 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1562 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1563 if (p_to_pay_plan = 'SL' or p_to_pay_plan = 'XE') and
1564 p_to_grade_or_level <>'00'
1565 and
1566 p_to_grade_or_level is not null
1567 then
1568 hr_utility.set_message(8301, 'GHR_37242_ALL_PROCEDURE_FAIL');
1569 hr_utility.raise_error;
1570 end if;
1571 end if;
1572
1573 -- 420.65.3
1574 if p_to_pay_plan= 'GG' and
1575 not (p_to_grade_or_level in
1576 ('01', '02', '03', '04', '05', '06', '07', '08',
1577 '09', '10', '11', '12', '13', '14', '15', 'SL') or
1578 p_to_grade_or_level is null)
1579 then
1580 hr_utility.set_message(8301, 'GHR_37243_ALL_PROCEDURE_FAIL');
1581 hr_utility.raise_error;
1582 end if;
1583
1584 -- 420.70.3
1585 if p_to_pay_plan = 'GH' and
1586 not (p_to_grade_or_level in ('13', '14', '15') or
1587 p_to_grade_or_level is null)
1588 then
1589 hr_utility.set_message(8301, 'GHR_37244_ALL_PROCEDURE_FAIL');
1590 hr_utility.raise_error;
1591 end if;
1592
1593 -- 420.73.3
1594 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1595 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1596 if p_to_pay_plan = 'NY' and
1597 p_to_grade_or_level not in ('01', '02', '03', '04')
1598 and
1599 p_to_grade_or_level is not null
1600 then
1601 hr_utility.set_message(8301, 'GHR_37245_ALL_PROCEDURE_FAIL');
1602 hr_utility.raise_error;
1603 end if;
1604 end if;
1605
1606 --420.74.3
1607 -- Update/Change Date By Effective Date Comment
1608 -- 9/5 08/12/99 vravikan 01-Apr-1999 New Edit
1609 /*If pay plan is NC,
1610 Then grade must be 01 through 03.*/
1611 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1612 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1613 If p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') then
1614 if p_to_pay_plan = 'NC' and
1615 p_to_grade_or_level not in ('01', '02', '03')
1616 and
1617 p_to_grade_or_level is not null
1618 then
1619 hr_utility.set_message(8301, 'GHR_37067_ALL_PROCEDURE_FAIL');
1620 hr_utility.raise_error;
1621 end if;
1622 end if;
1623 end if;
1624
1625 --420.75.3
1626 -- Update/Change Date By Effective Date Comment
1627 -- 9/5 08/12/99 vravikan 01-Apr-1999 New Edit
1628 -- 18/6 10/16/02 vnarasim 01-Apr-1999 Added grade 05
1629 /*If pay plan is NO,
1630 Then grade must be 01 through 04 or asterisks.*/
1631 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1632 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1633 If p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') then
1634 if p_to_pay_plan = 'NO' and
1635 p_to_grade_or_level not in ('01', '02', '03','04','05')
1636 and
1637 p_to_grade_or_level is not null
1638 then
1639 hr_utility.set_message(8301, 'GHR_37068_ALL_PROCEDURE_FAIL');
1640 hr_utility.raise_error;
1641 end if;
1642 end if;
1643 end if;
1644
1645 --420.76.3
1646 -- Update/Change Date By Effective Date Comment
1647 -- 9/5 08/12/99 vravikan 01-Apr-1999 New Edit
1648 /*If pay plan is NP or NR, Then grade must be 01 through 05 or asterisks*/
1649 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1650 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1651 If p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') then
1652 if p_to_pay_plan in ('NP','NR') and
1653 p_to_grade_or_level not in ('01', '02', '03','04','05')
1654 and
1655 p_to_grade_or_level is not null
1656 then
1657 hr_utility.set_message(8301, 'GHR_37069_ALL_PROCEDURE_FAIL');
1658 hr_utility.raise_error;
1659 end if;
1660 end if;
1661 end if;
1662
1663 --420.77.3
1664 -- Update/Change Date By Effective Date Comment
1665 -- 18-Aug-2000 vravikan 01-May-2000 New Edit
1666 -- 13-Jun-06 Raju 01-Jan-03 Terminate the edit
1667 /*If pay plan is VE , Then grade must be 01 ,02 or asterisks*/
1668 If p_effective_date >= to_date('2000/05/01', 'yyyy/mm/dd') and
1669 p_effective_date < fnd_date.canonical_to_date('2003/01/01') then --Bug# 5073313
1670 if p_to_pay_plan in ('VE') and
1671 p_to_grade_or_level not in ('01', '02')
1672 and
1673 p_to_grade_or_level is not null
1674 then
1675 hr_utility.set_message(8301, 'GHR_37416_ALL_PROCEDURE_FAIL');
1676 hr_utility.raise_error;
1677 end if;
1678 end if;
1679
1680 --420.78.3
1681 -- Update/Change Date By Effective Date Comment
1682 -- 18-Aug-00 vravikan 01-Jun-2000 New Edit
1683 /*If pay plan is NB, Then grade must be 01 through 09 or asterisks*/
1684 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
1685 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
1686 If p_effective_date >= to_date('1999/06/01', 'yyyy/mm/dd') then
1687 if p_to_pay_plan in ('NB') and
1688 p_to_grade_or_level not in ('01', '02','03','04','05','06','07','08','09')
1689 and
1690 p_to_grade_or_level is not null
1691 then
1692 hr_utility.set_message(8301, 'GHR_37417_ALL_PROCEDURE_FAIL');
1693 hr_utility.raise_error;
1694 end if;
1695 end if;
1696 end if;
1697 -- Begin Bug# 5073313
1698 --420.80.3
1699 -- Update/Change Date By Effective Date Comment
1700 -- Upd 46 13-Jun-06 Raju 01-Jan-2006 New Edit
1701 If p_effective_date >= to_date('2006/01/01', 'yyyy/mm/dd') then
1702 if p_to_pay_plan in ('GL') and
1703 p_to_grade_or_level not in ('03','04','05','06','07','08','09','10') and
1704 p_to_grade_or_level is not null
1705 then
1706 hr_utility.set_message(8301, 'GHR_37426_ALL_PROCEDURE_FAIL');
1707 hr_utility.raise_error;
1708 end if;
1709 end if;
1710 -- End Bug# 5073313
1711 --Begin Bug# 5745356
1712 --440.00.3
1713 If p_effective_date >= to_date('2006/10/01', 'yyyy/mm/dd') then
1714 if p_to_pay_plan in ('GL') and
1715 p_to_grade_or_level > '10' and
1716 p_to_grade_or_level is not null then
1717 hr_utility.set_message(8301, 'GHR_37584_ALL_PROCEDURE_FAIL');
1718 hr_utility.raise_error;
1719 end if;
1720 end if;
1721 --End Bug# 5745356
1722
1723 end chk_pay_grade;
1724
1725
1726 /* Name:
1727 Pay Plan
1728 */
1729
1730 procedure chk_pay_plan
1731 (p_to_pay_plan in varchar2
1732 ,p_agency_subelement in varchar2 -- non SF52 item
1733 ,p_pers_office_identifier in varchar2 -- non SF52 item
1734 ,p_to_grade_or_level in varchar2
1735 ,p_first_action_noa_la_code1 in varchar2
1736 ,p_first_action_noa_la_code2 in varchar2
1737 ,p_Cur_Appt_Auth_1 in varchar2 -- non SF52 item
1738 ,p_Cur_Appt_Auth_2 in varchar2 -- non SF52 item
1739 ,p_first_NOAC_Lookup_Code in varchar2
1740 ,p_Pay_Rate_Determinant_Code in varchar2
1741 ,p_Effective_Date in date
1742 ,p_prior_pay_plan in varchar2 -- non SF52 item
1743 ,p_prior_grade in varchar2 -- non SF52 item
1744 ,p_Agency in varchar2 -- non SF52 item
1745 ,p_Supervisory_status_code in varchar2
1746 ) is
1747 l_Effective_Date Date;
1748 begin
1749
1750 -- 440.02.3
1751 /* Enclosed the edit with effctive dates due to the following changes on 17-jul-1998.
1752 1)Added : if pay plan is NH,NJ or NK, then agency/subelement must be
1753 AF,AR,DD or NV
1754 2)Modified : if pay plan is ZA,ZP,ZS or ZT then agency/subelement must be CM57 to CM
1755 */
1756 -- Update Date By Effective Date Comment
1757 -- 8 01/28/99 vravikan 01/10/98 Add the following to edit
1758 -- If Pay plan is AT,EV, or FV then
1759 -- Agency/Subelement Code must be TD03
1760 -- 9 05/05/99 vravikan If Pay plan is CG or EO,
1761 -- Then agency/subelement must be FD--
1762 -- If Pay plan in MG,MS, or MX then
1763 -- agency/subelement must be HUYY
1764
1765 -- 9/4 08/10/99 vravikan 01-Mar-99 If Pay plan is EZ, then agency/subelement
1766 -- must be SE
1767 -- 9/5 08/12/99 vravikan 01-Apr-99 If pay plan is NP or NR,
1768 -- Then grade must be 01 through 05 or asterisks
1769 -- 10/5 08/13/99 vravikan 01-Apr-99 If pay plan is TW, Then
1770 -- Agency/Subelement Code must be TRAI
1771 -- 10/5 08/31/99 vravikan 01-Sep-99 If pay plan is TW, Then
1772 -- Agency/Subelement Code must be TRAD
1773 -- 10/5 08/31/99 vravikan 01-Oct-99 If pay plan is PD, Then
1774 -- Agency/Subelement Code must be TRAC,TRAF, or TR40
1775 -- 11/6 12/14/99 vravikan From the start If pay plan is DA,DG, DP,DS
1776 -- , or DT then
1777 -- Agency/Subelement Code must be NV
1778 -- 17-Aug-00 vravikan From the start If pay plan is VE,
1779 -- Then Agency/Subelement Code must be VA
1780 -- If pay plan is NB
1781 -- Then Agency/Subelement must be TRAJ
1782 -- Delete OC vs TRAJ combination
1783 -- If pay plan is IR then
1784 -- Agency/Subelement must be TR93
1785 -- If pay plan is CB,CF,CH,CI,CJ or CL then
1786 -- Agency/Subelement code must be FD
1787 -- 18/3 16-Oct-02 vnarasim From the Start If Pay Plan is RE or RP then
1788 -- Agency/Subelement code must be TR93.
1789 -- 28-Nov-02 Madhuri From the Start If Pay Plan is TG then
1790 -- Agency/Subelement must be TR35.
1791
1792 -- 20/2 02/20/03 Madhuri from the start If pay plan is CE
1793 -- Then agency/subelement must be IN06(Modified from IN -> IN06)
1794 -- If pay plan is CY
1795 -- Then agency/subelement must be IN06(Modified from IN -> IN06)
1796 -- If pay plan is LE
1797 -- Then agency/subelement must be TRAC(Modified from TR -> TRAC)
1798 -- If pay plan is AF
1799 -- Then agency/subelement must be AG10, AM, CM55, IB, PU, or ST.
1800 -- If pay plan is AG
1801 -- Then agency/subelement must be FD.
1802 -- If pay plan is CU
1803 -- Then agency/subelement must be CU.
1804 -- If pay plan is DL
1805 -- Then agency/subelement must be VA.
1806 -- If pay plan is DN
1807 -- Then agency/subelement must be BF.
1808 -- If pay plan is EN
1809 -- Then agency/subelement must be DN.
1810 -- If pay plan is FE
1811 -- Then agency/subelement must be AG10, AM, CM55, IB, PU, or ST.
1812 -- If pay plan is FO
1813 -- Then agency/subelement must be AG10, AM, CM55, IB, PU, or ST.
1814 -- If pay plan is FP
1815 -- Then agency/subelement must be AG10, AM, CM55, IB, PU, or ST.
1816 -- If pay plan is IE
1817 -- Then agency/subelement must be AF, AR, DD, or NV.
1818 -- If pay plan is IP
1819 -- Then agency/subelement must be AF, AR, DD, or NV.
1820 -- If pay plan is PG
1821 -- Then agency/subelement must be LP.
1822 -- If pay plan is RS
1823 -- Then agency/subelement must be HE.
1824 -- If pay plan is SS
1825 -- Then agency/subelement must be CU.
1826 -- If pay plan is TM
1827 -- Then agency/subelement must be FY.
1828 -- If pay plan is VG
1829 -- Then agency/subelement must be FK or FL.
1830 -- If pay plan is VH
1831 -- Then agency/subelement must be FK or FL.
1832 -- If pay plan is XF
1833 -- Then agency/subelement must be AR.
1834 -- If pay plan is XG
1835 -- Then agency/subelement must be AR.
1836 -- If pay plan is XH
1837 -- Then agency/subelement must be AR.
1838 -- 21/1, 02/20/03 Madhuri from the start If pay plan is PD
1839 -- 21/2 Then agency/subelement must be DJ15,HSAD or HSBD.
1840 -- 21/2 02/20/03 Madhuri from the start If pay plan is EJ
1841 -- Then agency/subelement must be HS.
1842 -- If pay plan is FE
1843 -- Then agency/subelement must be HS.
1844 -- If pay plan is FP
1845 -- Then agency/subelement must be HS.
1846 -- If pay plan is LE
1847 -- Then agency/subelement must be HSAD.
1848 -- 20/2 02/27/03 Madhuri Deleting the following requirement from the Edit
1849 -- If the Pay Plan is TW,
1850 -- Then agency/subelement must be TRAI or TRAD.
1851 -- 03/19/03 Narasimha Rao If the Pay Plan is SV or SW then
1852 -- agency/subelement must be in TD19 or HSBC.
1853 -- 05/07/03 Narasimha Rao There are more than one edit checks for pay
1854 -- plans DR,EJ,LE,PD,FP,FE. Combined these edit
1855 -- checks such that each pay plan will have only
1856 -- one edit check.
1857 -- 30/10/03 Ashley If pay plan is CM,EM
1858 -- Then agency/subelement must be FDxx.
1859 -- If pay plan is CT
1860 -- Then agency/subelement must be CTxx.
1861 -- If pay plan is RA
1862 -- Then agency/subelement must be AGxx.
1863 -- If pay plan is FO
1864 -- Then agency/subelement must be AG34,HSxx.
1865 -- Under pay plan PD, delete agency/subelement TRAF,TRAC
1866 -- If pay plan is FE,FP
1867 -- Then agency/subelement must be AG34.
1868 -- If pay plan is KB,KE,KI,KJ,KM,KN,KO,KP,KT,KU,KV,KW,KX,KY,PJ,PQ,PU,or PZ,
1869 -- Then agency/subelement must be LPxx.
1870 -- For pay plan LE, deleted agency/subelement TRAC.
1871 -- For pay plan SV, deleted agency/subelement TD19.
1872 -- For pay plan SW, deleted agency/subelement TD19.
1873 -- 19/05/04 Madhuri If Pay Plan is YA, YB, YC, YD, YE, YF, YG, YH, YI, YJ, YK,
1874 -- YL, YM, YN, YO, YP, YQ, YR, YS, YT, YU, YV, YW, YX, YY, or YZ,
1875 -- Then Agency/Sub Element must start with DD. (U.32)
1876 -- (That means the first two characters of Agency/Sub-element code must be DD)
1877
1878 -- 14/09/2004 Madhuri i) Delete entries for Pay Plan:DL, JA, JB, JC, JD, and JE
1879 -- ii) If pay plan is XI, XJ, or XK, Then agency/sub-element must be TRAI.
1880 -- 18/10/2004 Madhuri i) If pay plan is NZ, Then agency/subelement must be SM03.
1881 -- 08/11/2005(UPD 42)Raju If pay plan is GE or GI,
1882 -- Then agency/subelement must be LP.( Bug 4567571)
1883 -- 07/03/2006(upd 39)Narasimha Rao IF pay plan is OF, Then agency/sub-element must be HUFH
1884 ----upd47 26-Jun-06 Raju From 01-May-2006 For pay plan codes AF, FA, FE, FO, and FP,
1885 -- Removes agency / sub element code ST
1886 -- If pay plan is HE, HH, HL, HS, HT, or HW, then agency/sub element must be HS
1887 -- 15/11/2006 amrchakr For the payplan YG, the agency/sub elemnt must be AF, AR, DD, NV(Bug 5662041)
1888 -- UPD 50(Bug 5745356) Raju From 01-Oct-2006 added If pay plan is HA, HB, HC, HD, HF,HG,HI,HJ,HK,
1889 -- HM,HN,HO,HP,HQ,HR,HV,HX,HY, or HZ then agency/sub element must be HS
1890 -- UPD 55(Bug 6469079) Raju From 01-May-2007 added pay plan 'OE' to subelement HUFH
1891 -- UPD 56 8309414 Raju From 1-apr-08 Remove MG
1892 -- UPD 59 10189715 Raju From 01-Sep-10 Remove Hx except HU
1893 ----------------------------------------------------------------------------------------------------------------
1894 if (p_to_pay_plan = 'AJ' and substr(p_agency_subelement,1,2) <> 'NU' ) then
1895 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1896 hr_utility.set_message_token('PAY_PLAN','AJ');
1897 hr_utility.set_message_token('AGENCY_CODE','NU');
1898 hr_utility.raise_error;
1899 elsif ( p_to_pay_plan = 'CE' and p_agency_subelement <> 'IN06' ) then
1900 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1901 hr_utility.set_message_token('PAY_PLAN','CE');
1902 hr_utility.set_message_token('AGENCY_CODE','IN06');
1903 hr_utility.raise_error;
1904 elsif ( p_to_pay_plan in ('VE') and
1905 substr(p_agency_subelement,1,2) <> 'VA' ) then
1906 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1907 hr_utility.set_message_token('PAY_PLAN','in VE');
1908 hr_utility.set_message_token('AGENCY_CODE','VA');
1909 hr_utility.raise_error;
1910 elsif ( p_to_pay_plan = 'NB' and p_agency_subelement <> 'TRAJ' ) then
1911 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1912 hr_utility.set_message_token('PAY_PLAN','NB');
1913 hr_utility.set_message_token('AGENCY_CODE','TRAJ');
1914 hr_utility.raise_error;
1915 elsif ( p_to_pay_plan = 'IR' and p_agency_subelement <> 'TR93' ) then
1916 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1917 hr_utility.set_message_token('PAY_PLAN','IR');
1918 hr_utility.set_message_token('AGENCY_CODE','TR93');
1919 hr_utility.raise_error;
1920 elsif ( p_to_pay_plan in ('CB','CF','CH','CI','CJ','CL','CM') and
1921 substr(p_agency_subelement,1,2) <> 'FD' ) then
1922 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1923 hr_utility.set_message_token('PAY_PLAN','in CB,CF,CH,CI,CJ,CL or CM');
1924 hr_utility.set_message_token('AGENCY_CODE','FD');
1925 hr_utility.raise_error;
1926 elsif ( p_to_pay_plan = 'CY' and p_agency_subelement <> 'IN06' ) then
1927 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1928 hr_utility.set_message_token('PAY_PLAN','CY');
1929 hr_utility.set_message_token('AGENCY_CODE','IN06');
1930 hr_utility.raise_error;
1931 elsif ( p_to_pay_plan in('DB','DE','DJ','DK','DQ') and
1932 substr(p_agency_subelement,1,2) not in ('AF','AR','NV','DD') ) then
1933 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1934 hr_utility.set_message_token('PAY_PLAN','in DB,DE,DJ,DK or DQ');
1935 hr_utility.set_message_token('AGENCY_CODE','in AF,AR,NV or DD');
1936 hr_utility.raise_error;
1937 elsif ( p_to_pay_plan = 'DR' and substr(p_agency_subelement,1,2) <> 'AF' ) then
1938 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1939 hr_utility.set_message_token('PAY_PLAN','DR');
1940 hr_utility.set_message_token('AGENCY_CODE','AF');
1941 hr_utility.raise_error;
1942 elsif ( p_to_pay_plan in('DV','DZ') and
1943 substr(p_agency_subelement,1,2) not in ('AF','AR','NV','DD') ) then
1944 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1945 hr_utility.set_message_token('PAY_PLAN','DV,DZ');
1946 hr_utility.set_message_token('AGENCY_CODE','AF,AR,NV or DD');
1947 hr_utility.raise_error;
1948 elsif ( p_to_pay_plan in ('EJ') and substr(p_agency_subelement,1,2) NOT IN('HS','DN') )
1949 then
1950 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1951 hr_utility.set_message_token('PAY_PLAN','EJ');
1952 hr_utility.set_message_token('AGENCY_CODE','DN or HS');
1953 hr_utility.raise_error;
1954 elsif ( p_to_pay_plan in('EK') and
1955 substr(p_agency_subelement,1,2) <> 'DN' ) then
1956 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1957 hr_utility.set_message_token('PAY_PLAN','EK');
1958 hr_utility.set_message_token('AGENCY_CODE','DN');
1959 hr_utility.raise_error;
1960 elsif p_effective_date < fnd_date.canonical_to_date('2006/05/01') and
1961 ( p_to_pay_plan = 'FA' and
1962 substr(p_agency_subelement,1,2) not in ('AM','ST') ) then
1963 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1964 hr_utility.set_message_token('PAY_PLAN','FA');
1965 hr_utility.set_message_token('AGENCY_CODE','AM,ST');
1966 hr_utility.raise_error;
1967 elsif p_effective_date >= fnd_date.canonical_to_date('2006/05/01') and
1968 ( p_to_pay_plan = 'FA' and
1969 substr(p_agency_subelement,1,2) not in ('AM') ) then
1970 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1971 hr_utility.set_message_token('PAY_PLAN','FA');
1972 hr_utility.set_message_token('AGENCY_CODE','AM');
1973 hr_utility.raise_error;
1974 elsif ( p_to_pay_plan = 'FB' and p_agency_subelement <> 'TD03' ) then
1975 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1976 hr_utility.set_message_token('PAY_PLAN','FB');
1977 hr_utility.set_message_token('AGENCY_CODE','TD03');
1978 hr_utility.raise_error;
1979 elsif ( p_to_pay_plan = 'FD' and
1980 substr(p_agency_subelement,1,2) not in ('AF','AR','NV','DD') ) then
1981 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1982 hr_utility.set_message_token('PAY_PLAN','FD');
1983 hr_utility.set_message_token('AGENCY_CODE','AF,AR,NV or DD');
1984 hr_utility.raise_error;
1985 elsif ( p_to_pay_plan in('FF','FG','FJ','FL','FM') and
1986 p_agency_subelement <> 'TD03' ) then
1987 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1988 hr_utility.set_message_token('PAY_PLAN','FF,FG,FJ,FL or FM');
1989 hr_utility.set_message_token('AGENCY_CODE','TD03');
1990 hr_utility.raise_error;
1991 elsif ( p_to_pay_plan in('FN','FS','FT','FW','FX')
1992 and p_agency_subelement <> 'TD03' ) then
1993 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1994 hr_utility.set_message_token('PAY_PLAN','FN,FS,FT,FW or FX');
1995 hr_utility.set_message_token('AGENCY_CODE','TD03');
1996 hr_utility.raise_error;
1997 elsif ( p_to_pay_plan = 'GN' and p_agency_subelement <> 'HE38' ) then
1998 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
1999 hr_utility.set_message_token('PAY_PLAN','GN');
2000 hr_utility.set_message_token('AGENCY_CODE','HE38');
2001 hr_utility.raise_error;
2002 elsif ( p_to_pay_plan = 'IJ' and substr(p_agency_subelement,1,2) <> 'DJ' ) then
2003 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2004 hr_utility.set_message_token('PAY_PLAN','IJ');
2005 hr_utility.set_message_token('AGENCY_CODE','DJ');
2006 hr_utility.raise_error;
2007 elsif ( p_to_pay_plan = 'KA' and substr(p_agency_subelement,1,2) <> 'LP' ) then
2008 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2009 hr_utility.set_message_token('PAY_PLAN','KA');
2010 hr_utility.set_message_token('AGENCY_CODE','LP');
2011 hr_utility.raise_error;
2012 elsif ( p_to_pay_plan in ('KG','KL','KS') and p_agency_subelement<>'TRAI' ) then
2013 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2014 hr_utility.set_message_token('PAY_PLAN','KG,KL or KS');
2015 hr_utility.set_message_token('AGENCY_CODE','TRAI');
2016 hr_utility.raise_error;
2017 elsif ( p_to_pay_plan = 'LE' and p_agency_subelement <>'HSAD' ) then
2018 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2019 hr_utility.set_message_token('PAY_PLAN','LE');
2020 hr_utility.set_message_token('AGENCY_CODE','HSAD');
2021 hr_utility.raise_error;
2022 elsif ( p_to_pay_plan = 'LG' and substr(p_agency_subelement,1,2) <> 'FD' ) then
2023 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2024 hr_utility.set_message_token('PAY_PLAN','LG');
2025 hr_utility.set_message_token('AGENCY_CODE','FD');
2026 hr_utility.raise_error;
2027 elsif ( p_to_pay_plan = 'MA' and substr(p_agency_subelement,1,2) <> 'AG' ) then
2028 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2029 hr_utility.set_message_token('PAY_PLAN','MA');
2030 hr_utility.set_message_token('AGENCY_CODE','AG');
2031 hr_utility.raise_error;
2032 elsif ( p_to_pay_plan in ('ND','NG','NT') and
2033 substr(p_agency_subelement,1,2) <> 'NV' ) then
2034 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2035 hr_utility.set_message_token('PAY_PLAN','in ND,NG or NT');
2036 hr_utility.set_message_token('AGENCY_CODE','NV');
2037 hr_utility.raise_error;
2038 elsif ( p_to_pay_plan in('NX','NY') and
2039 substr(p_agency_subelement,1,2) <> 'KS' ) then
2040 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2041 hr_utility.set_message_token('PAY_PLAN','NX,NY');
2042 hr_utility.set_message_token('AGENCY_CODE','KS');
2043 hr_utility.raise_error;
2044 elsif ( p_to_pay_plan = 'SN' and substr(p_agency_subelement,1,2) <> 'NU' ) then
2045 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2046 hr_utility.set_message_token('PAY_PLAN','SN');
2047 hr_utility.set_message_token('AGENCY_CODE','NU');
2048 hr_utility.raise_error;
2049 elsif ( p_to_pay_plan = 'SP' and substr(p_agency_subelement,1,2) <> 'IN' ) then
2050 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2051 hr_utility.set_message_token('PAY_PLAN','SP');
2052 hr_utility.set_message_token('AGENCY_CODE','IN');
2053 hr_utility.raise_error;
2054 --Begin Bug 9503972
2055 elsif p_effective_date > to_date('2009/12/01','RRRR/MM/DD') AND
2056 ( p_to_pay_plan = 'TF' and substr(p_agency_subelement,1,2) NOT IN( 'FY','HF')) then
2057 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2058 hr_utility.set_message_token('PAY_PLAN','TF');
2059 hr_utility.set_message_token('AGENCY_CODE','FY, HF');
2060 hr_utility.raise_error;
2061 --End Bug 9503972
2062 elsif ( p_to_pay_plan = 'TF' and substr(p_agency_subelement,1,2) <> 'FY' ) then
2063 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2064 hr_utility.set_message_token('PAY_PLAN','TF');
2065 hr_utility.set_message_token('AGENCY_CODE','FY');
2066 hr_utility.raise_error;
2067 elsif ( p_to_pay_plan = 'TP' and p_agency_subelement <> 'DD16' ) then
2068 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2069 hr_utility.set_message_token('PAY_PLAN','TP');
2070 hr_utility.set_message_token('AGENCY_CODE','DD16');
2071 hr_utility.raise_error;
2072 elsif ( p_to_pay_plan = 'TR' and p_agency_subelement not in ('TRAI','TRAD')) then
2073 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2074 hr_utility.set_message_token('PAY_PLAN','TR');
2075 hr_utility.set_message_token('AGENCY_CODE','TRAI,TRAD');
2076 hr_utility.raise_error;
2077 --Begin Bug 9503972
2078 elsif p_effective_date > to_date('2009/12/01','RRRR/MM/DD') AND
2079 (p_to_pay_plan = 'TS' and substr(p_agency_subelement,1,2) NOT IN ('FY','HF')) then
2080 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2081 hr_utility.set_message_token('PAY_PLAN','TS');
2082 hr_utility.set_message_token('AGENCY_CODE','FY, HF');
2083 hr_utility.raise_error;
2084 --End Bug 9503972
2085 elsif ( p_to_pay_plan = 'TS' and substr(p_agency_subelement,1,2) <> 'FY' ) then
2086 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2087 hr_utility.set_message_token('PAY_PLAN','TS');
2088 hr_utility.set_message_token('AGENCY_CODE','FY');
2089 hr_utility.raise_error;
2090 elsif ( p_to_pay_plan in('VC','VM','VN','VP') and
2091 substr(p_agency_subelement,1,2) <> 'VA' ) then
2092 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2093 hr_utility.set_message_token('PAY_PLAN','VC,VM,VN or VP');
2094 hr_utility.set_message_token('AGENCY_CODE','VA');
2095 hr_utility.raise_error;
2096 elsif ( p_to_pay_plan = 'WA' and substr(p_agency_subelement,1,2) <> 'AR' ) then
2097 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2098 hr_utility.set_message_token('PAY_PLAN','WA');
2099 hr_utility.set_message_token('AGENCY_CODE','AR');
2100 hr_utility.raise_error;
2101 elsif ( p_to_pay_plan = 'WE' and substr(p_agency_subelement,1,2) <> 'TR' ) then
2102 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2103 hr_utility.set_message_token('PAY_PLAN','WE');
2104 hr_utility.set_message_token('AGENCY_CODE','TR');
2105 hr_utility.raise_error;
2106 elsif ( p_to_pay_plan in('WJ','WK','WO','WY') and
2107 substr(p_agency_subelement,1,2) <> 'AR' ) then
2108 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2109 hr_utility.set_message_token('PAY_PLAN','WJ,WK,WO or WY');
2110 hr_utility.set_message_token('AGENCY_CODE','AR');
2111 hr_utility.raise_error;
2112 elsif ( p_to_pay_plan in('XA','XB','XC') and
2113 substr(p_agency_subelement,1,2) <> 'IN' ) then
2114 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2115 hr_utility.set_message_token('PAY_PLAN','XA,XB or XC');
2116 hr_utility.set_message_token('AGENCY_CODE','IN');
2117 hr_utility.raise_error;
2118 elsif ( p_to_pay_plan = 'XE' and p_agency_subelement <> 'IN07' ) then
2119 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2120 hr_utility.set_message_token('PAY_PLAN','XE');
2121 hr_utility.set_message_token('AGENCY_CODE','IN07');
2122 hr_utility.raise_error;
2123 elsif ( p_to_pay_plan in ('CG','EO','EM') and substr(p_agency_subelement,1,2) <> 'FD' ) then
2124 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2125 hr_utility.set_message_token('PAY_PLAN','in CG,EO or EM');
2126 hr_utility.set_message_token('AGENCY_CODE','FD');
2127 hr_utility.raise_error;
2128 --Begin Bug# 8309414 remove pay plan MG eff 17-apr-08
2129 elsif p_effective_date < fnd_date.canonical_to_date('2008/04/17') and
2130 ( p_to_pay_plan in ('MG','MS','MX') and substr(p_agency_subelement,1,2) <> 'HU' ) then
2131 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2132 hr_utility.set_message_token('PAY_PLAN','in MG,MS or MX');
2133 hr_utility.set_message_token('AGENCY_CODE','HU');
2134 hr_utility.raise_error;
2135 elsif ( p_to_pay_plan in ('MS','MX') and substr(p_agency_subelement,1,2) <> 'HU' ) then
2136 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2137 hr_utility.set_message_token('PAY_PLAN','in MS or MX');
2138 hr_utility.set_message_token('AGENCY_CODE','HU');
2139 hr_utility.raise_error;
2140 --End Bug# 8309414
2141 elsif ( p_to_pay_plan in('ZA','ZP','ZS','ZT') and
2142 p_agency_subelement <> 'CM' ) then
2143 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2144 hr_utility.set_message_token('PAY_PLAN','in ZA,ZP,ZS or ZT');
2145 hr_utility.set_message_token('AGENCY_CODE','CM');
2146 hr_utility.raise_error;
2147 elsif ( p_to_pay_plan in('DA','DG','DP','DS','DT') and
2148 substr(p_agency_subelement,1,2) <> 'NV' ) then
2149 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2150 hr_utility.set_message_token('PAY_PLAN','in DA,DG,DP,DS or DT');
2151 hr_utility.set_message_token('AGENCY_CODE','NV');
2152 hr_utility.raise_error;
2153 elsif ( p_to_pay_plan in ('NH','NJ','NK') and
2154 substr(p_agency_subelement,1,2) not in ('AF','AR','DD','NV') and
2155 p_effective_date >= fnd_date.canonical_to_date('1998/03/01') ) then
2156 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2157 hr_utility.set_message_token('PAY_PLAN','in NH,NJ or NK');
2158 hr_utility.set_message_token('AGENCY_CODE','in AF,AR,DD or NV');
2159 hr_utility.raise_error;
2160 elsif ( p_to_pay_plan in ('AT','EV','FV') and
2161 p_effective_date >= fnd_date.canonical_to_date('1998/10/01') and
2162 p_agency_subelement <> 'TD03' ) then
2163 hr_utility.set_location('In pay plan AT', 12);
2164 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2165 hr_utility.set_message_token('PAY_PLAN','in AT,EV or FV');
2166 hr_utility.set_message_token('AGENCY_CODE','TD03');
2167 hr_utility.raise_error;
2168 --
2169 -- Deleting entries for Pay Plans - JA,JB,JC,JD,JE. DATE: 14-SEP-2004 for EOY - I
2170 --
2171 /* elsif ( p_to_pay_plan in ( 'JA','JB','JC','JD','JE') and p_agency_subelement <> 'DJ02' ) then
2172 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2173 hr_utility.set_message_token('PAY_PLAN','in JA,JB,JC,JD or JE');
2174 hr_utility.set_message_token('AGENCY_CODE','DJ02');
2175 hr_utility.raise_error;*/
2176 elsif ( p_to_pay_plan in ( 'EZ') and substr(p_agency_subelement,1,2) <> 'SE' and
2177 p_effective_date >= fnd_date.canonical_to_date('19'||'99/03/01') ) then
2178 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2179 hr_utility.set_message_token('PAY_PLAN','EZ');
2180 hr_utility.set_message_token('AGENCY_CODE','SE');
2181 hr_utility.raise_error;
2182 elsif ( p_to_pay_plan in ('NC','NO','NP','NR') and substr(p_agency_subelement,1,2) <> 'NV' and
2183 p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') )
2184 then
2185 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2186 hr_utility.set_message_token('PAY_PLAN','in NC,NO,NP or NR');
2187 hr_utility.set_message_token('AGENCY_CODE','NV');
2188 hr_utility.raise_error;
2189 elsif p_to_pay_plan in ('PD') then
2190 IF ( p_effective_date >= to_date('1999/10/01','yyyy/mm/dd')
2191 and p_agency_subelement not in('TR40','DJ15','HSAD','HSBD')
2192 ) then
2193 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2194 hr_utility.set_message_token('PAY_PLAN','PD');
2195 hr_utility.set_message_token('AGENCY_CODE','in TR40,DJ15,HSAD or HSBD');
2196 hr_utility.raise_error;
2197 Elsif( p_effective_date < to_date('1999/10/01','yyyy/mm/dd')
2198 and p_agency_subelement not in('DJ15','HSAD','HSBD')
2199 ) then
2200 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2201 hr_utility.set_message_token('PAY_PLAN','PD');
2202 hr_utility.set_message_token('AGENCY_CODE','in DJ15, HSAD or HSBD');
2203 hr_utility.raise_error;
2204 END IF;
2205 elsif ( p_to_pay_plan in ('SK','SO') and substr(p_agency_subelement,1,2) <> 'SE')
2206 then
2207 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2208 hr_utility.set_message_token('PAY_PLAN','in SK or SO');
2209 hr_utility.set_message_token('AGENCY_CODE','SE');
2210 hr_utility.raise_error;
2211 elsif ( p_to_pay_plan in ('SV','SW') and p_agency_subelement <>'HSBC')
2212 then
2213 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2214 hr_utility.set_message_token('PAY_PLAN','in SV or SW');
2215 hr_utility.set_message_token('AGENCY_CODE','in HSBC');
2216 hr_utility.raise_error;
2217 elsif ( p_to_pay_plan in ('RE','RP') and p_agency_subelement <> 'TR93')
2218 then
2219 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2220 hr_utility.set_message_token('PAY_PLAN','in RE or RP');
2221 hr_utility.set_message_token('AGENCY_CODE','TR93');
2222 hr_utility.raise_error;
2223 elsif ( p_to_pay_plan in ('TG') and p_agency_subelement <> 'TR35')
2224 then
2225 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2226 hr_utility.set_message_token('PAY_PLAN','TG');
2227 hr_utility.set_message_token('AGENCY_CODE','TR35');
2228 hr_utility.raise_error;
2229 elsif p_effective_date < fnd_date.canonical_to_date('2006/05/01') and
2230 ( p_to_pay_plan in ('AF') and (p_agency_subelement not in ('AG10','CM55')
2231 and substr(p_agency_subelement,1,2) not in ('AM','IB','PU','ST')) )
2232 then
2233 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2234 hr_utility.set_message_token('PAY_PLAN','AF');
2235 hr_utility.set_message_token('AGENCY_CODE','AG10,AM,CM55,IB,PU or ST');
2236 hr_utility.raise_error;
2237
2238 elsif p_effective_date >= fnd_date.canonical_to_date('2006/05/01') and
2239 ( p_to_pay_plan in ('AF') and (p_agency_subelement not in ('AG10','CM55')
2240 and substr(p_agency_subelement,1,2) not in ('AM','IB','PU')) )
2241 then
2242 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2243 hr_utility.set_message_token('PAY_PLAN','AF');
2244 hr_utility.set_message_token('AGENCY_CODE','AG10,AM,CM55,IB or PU ');
2245 hr_utility.raise_error;
2246 elsif ( p_to_pay_plan in ('AG') and substr(p_agency_subelement,1,2) <> 'FD' )
2247 then
2248 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2249 hr_utility.set_message_token('PAY_PLAN','AG');
2250 hr_utility.set_message_token('AGENCY_CODE','FD');
2251 hr_utility.raise_error;
2252 elsif ( p_to_pay_plan in ('CU') and substr(p_agency_subelement,1,2) <> 'CU' )
2253 then
2254 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2255 hr_utility.set_message_token('PAY_PLAN','CU');
2256 hr_utility.set_message_token('AGENCY_CODE','CU');
2257 hr_utility.raise_error;
2258 --
2259 -- Deleting entries for Pay Plans - DL. DATE: 14-SEP-2004 for EOY - I
2260 --
2261 /*elsif ( p_to_pay_plan in ('DL') and substr(p_agency_subelement,1,2) <> 'VA' )
2262 then
2263 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2264 hr_utility.set_message_token('PAY_PLAN','DL');
2265 hr_utility.set_message_token('AGENCY_CODE','VA');
2266 hr_utility.raise_error; */
2267 elsif ( p_to_pay_plan in ('DN') and substr(p_agency_subelement,1,2) <> 'BF' )
2268 then
2269 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2270 hr_utility.set_message_token('PAY_PLAN','DN');
2271 hr_utility.set_message_token('AGENCY_CODE','BF');
2272 hr_utility.raise_error;
2273 elsif ( p_to_pay_plan in ('EN') and substr(p_agency_subelement,1,2) <> 'DN' )
2274 then
2275 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2276 hr_utility.set_message_token('PAY_PLAN','EN');
2277 hr_utility.set_message_token('AGENCY_CODE','DN');
2278 hr_utility.raise_error;
2279 elsif p_effective_date < fnd_date.canonical_to_date('2006/05/01') and
2280 ( p_to_pay_plan in ('FE') and (p_agency_subelement not in ('AG10','CM55','AG34')
2281 and substr(p_agency_subelement,1,2) not in ('AM','HS','IB','PU','ST')) )
2282 then
2283 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2284 hr_utility.set_message_token('PAY_PLAN','FE');
2285 hr_utility.set_message_token('AGENCY_CODE','AG10,AM,CM55,AG34,HS,IB,PU or ST');
2286 hr_utility.raise_error;
2287 elsif p_effective_date >= fnd_date.canonical_to_date('2006/05/01') and
2288 ( p_to_pay_plan in ('FE') and (p_agency_subelement not in ('AG10','CM55','AG34')
2289 and substr(p_agency_subelement,1,2) not in ('AM','HS','IB','PU')) )
2290 then
2291 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2292 hr_utility.set_message_token('PAY_PLAN','FE');
2293 hr_utility.set_message_token('AGENCY_CODE','AG10,AM,CM55,AG34,HS,IB or PU');
2294 hr_utility.raise_error;
2295 elsif p_effective_date < fnd_date.canonical_to_date('2006/05/01') and
2296 ( p_to_pay_plan in ('FO') and (p_agency_subelement not in ('AG10','CM55','AG34')
2297 and substr(p_agency_subelement,1,2) not in ('AM','IB','PU','ST','HS')) )
2298 then
2299 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2300 hr_utility.set_message_token('PAY_PLAN','FO');
2301 hr_utility.set_message_token('AGENCY_CODE','AG10,AM,CM55,AG34,IB,PU,HS or ST');
2302 hr_utility.raise_error;
2303 elsif p_effective_date >= fnd_date.canonical_to_date('2006/05/01') and
2304 ( p_to_pay_plan in ('FO') and (p_agency_subelement not in ('AG10','CM55','AG34')
2305 and substr(p_agency_subelement,1,2) not in ('AM','IB','PU','HS')) )
2306 then
2307 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2308 hr_utility.set_message_token('PAY_PLAN','FO');
2309 hr_utility.set_message_token('AGENCY_CODE','AG10,AM,CM55,AG34,IB,PU or HS');
2310 hr_utility.raise_error;
2311 elsif p_effective_date < fnd_date.canonical_to_date('2006/05/01') and
2312 ( p_to_pay_plan in ('FP') and (p_agency_subelement not in ('AG10','CM55','AG34')
2313 and substr(p_agency_subelement,1,2) not in ('AM','HS','IB','PU','ST')) )
2314 then
2315 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2316 hr_utility.set_message_token('PAY_PLAN','FP');
2317 hr_utility.set_message_token('AGENCY_CODE','AG10,AM,CM55,AG34,HS,IB,PU or ST');
2318 hr_utility.raise_error;
2319 elsif p_effective_date >= fnd_date.canonical_to_date('2006/05/01') and
2320 ( p_to_pay_plan in ('FP') and (p_agency_subelement not in ('AG10','CM55','AG34')
2321 and substr(p_agency_subelement,1,2) not in ('AM','HS','IB','PU')) )
2322 then
2323 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2324 hr_utility.set_message_token('PAY_PLAN','FP');
2325 hr_utility.set_message_token('AGENCY_CODE','AG10,AM,CM55,AG34,HS,IB or PU');
2326 hr_utility.raise_error;
2327 elsif ( p_to_pay_plan in ('IE') and substr(p_agency_subelement,1,2) not in ('AF','AR','DD','NV') )
2328 then
2329 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2330 hr_utility.set_message_token('PAY_PLAN','IE');
2331 hr_utility.set_message_token('AGENCY_CODE','AF,AR,DD or NV');
2332 hr_utility.raise_error;
2333 elsif ( p_to_pay_plan in ('IP') and substr(p_agency_subelement,1,2) not in ('AF','AR','DD','NV') )
2334 then
2335 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2336 hr_utility.set_message_token('PAY_PLAN','IP');
2337 hr_utility.set_message_token('AGENCY_CODE','AF,AR,DD or NV');
2338 hr_utility.raise_error;
2339 elsif ( p_to_pay_plan in ('PG') and substr(p_agency_subelement,1,2) <> 'LP' )
2340 then
2341 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2342 hr_utility.set_message_token('PAY_PLAN','PG');
2343 hr_utility.set_message_token('AGENCY_CODE','LP');
2344 hr_utility.raise_error;
2345 elsif ( p_to_pay_plan in ('RS') and substr(p_agency_subelement,1,2) <> 'HE' )
2346 then
2347 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2348 hr_utility.set_message_token('PAY_PLAN','RS');
2349 hr_utility.set_message_token('AGENCY_CODE','HE');
2350 hr_utility.raise_error;
2351 elsif ( p_to_pay_plan in ('SS') and substr(p_agency_subelement,1,2) <> 'CU' )
2352 then
2353 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2354 hr_utility.set_message_token('PAY_PLAN','SS');
2355 hr_utility.set_message_token('AGENCY_CODE','CU');
2356 hr_utility.raise_error;
2357 --Begin Bug 9503972
2358 elsif p_effective_date > to_date('2009/12/01','RRRR/MM/DD') AND
2359 ( p_to_pay_plan in ('TM') and substr(p_agency_subelement,1,2) NOT IN ('FY','HF')) then
2360 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2361 hr_utility.set_message_token('PAY_PLAN','TM');
2362 hr_utility.set_message_token('AGENCY_CODE','FY, HF');
2363 hr_utility.raise_error;
2364 --End Bug 9503972
2365 elsif ( p_to_pay_plan in ('TM') and substr(p_agency_subelement,1,2) <> 'FY' )
2366 then
2367 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2368 hr_utility.set_message_token('PAY_PLAN','TM');
2369 hr_utility.set_message_token('AGENCY_CODE','FY');
2370 hr_utility.raise_error;
2371 elsif ( p_to_pay_plan in ('VG') and substr(p_agency_subelement,1,2) not in ('FK','FL') )
2372 then
2373 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2374 hr_utility.set_message_token('PAY_PLAN','VG');
2375 hr_utility.set_message_token('AGENCY_CODE','FK or FL');
2376 hr_utility.raise_error;
2377 elsif ( p_to_pay_plan in ('VH') and substr(p_agency_subelement,1,2) not in ('FK','FL') )
2378 then
2379 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2380 hr_utility.set_message_token('PAY_PLAN','VH');
2381 hr_utility.set_message_token('AGENCY_CODE','FK or FL');
2382 hr_utility.raise_error;
2383 elsif ( p_to_pay_plan in ('XF') and substr(p_agency_subelement,1,2) <> 'AR' )
2384 then
2385 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2386 hr_utility.set_message_token('PAY_PLAN','XF');
2387 hr_utility.set_message_token('AGENCY_CODE','AR');
2388 hr_utility.raise_error;
2389 elsif ( p_to_pay_plan in ('XG') and substr(p_agency_subelement,1,2) <> 'AR' )
2390 then
2391 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2392 hr_utility.set_message_token('PAY_PLAN','XG');
2393 hr_utility.set_message_token('AGENCY_CODE','AR');
2394 hr_utility.raise_error;
2395 elsif ( p_to_pay_plan in ('XH') and substr(p_agency_subelement,1,2) <> 'AR' )
2396 then
2397 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2398 hr_utility.set_message_token('PAY_PLAN','XH');
2399 hr_utility.set_message_token('AGENCY_CODE','AR');
2400 hr_utility.raise_error;
2401 elsif ( p_to_pay_plan = 'CT' and substr(p_agency_subelement,1,2) <> 'CT') then
2402 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2403 hr_utility.set_message_token('PAY_PLAN','CT');
2404 hr_utility.set_message_token('AGENCY_CODE','CT');
2405 hr_utility.raise_error;
2406 elsif ( p_to_pay_plan = 'RA' and substr(p_agency_subelement,1,2) <> 'AG' ) then
2407 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2408 hr_utility.set_message_token('PAY_PLAN','RA');
2409 hr_utility.set_message_token('AGENCY_CODE','AG');
2410 hr_utility.raise_error;
2411 elsif ( p_to_pay_plan in ('KB','KE','KI','KJ','KM','KN','KO','KP','KT','KU','KV','KW','KX','KY','PJ','PQ','PU','PZ')
2412 and substr(p_agency_subelement,1,2) <> 'LP' ) then
2413 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2414 hr_utility.set_message_token('PAY_PLAN','KB,KE,KI,KJ,KM,KN,KO,KP,KT,KU,KV,KW,KX,KY,PJ,PQ,PU,or PZ');
2415 hr_utility.set_message_token('AGENCY_CODE','LP');
2416 hr_utility.raise_error;
2417 elsif ( p_to_pay_plan in ('YA', 'YB', 'YC', 'YD', 'YE', 'YF', 'YG', 'YH', 'YI', 'YJ', 'YK', 'YL', 'YM', 'YN', 'YO', 'YP', 'YQ', 'YR', 'YS', 'YT', 'YU', 'YV', 'YW', 'YX', 'YY', 'YZ')
2418 and substr(p_agency_subelement,1,2) not in ('AF','AR','DD','NV') ) then
2419 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2420 hr_utility.set_message_token('PAY_PLAN',' YA, YB, YC, YD, YE, YF, YG, YH, YI, YJ, YK,YL, YM, YN, YO, YP, YQ, YR, YS, YT, YU, YV, YW, YX, YY, or YZ ');
2421 hr_utility.set_message_token('AGENCY_CODE','AF,AR,DD or NV');
2422 hr_utility.raise_error;
2423 elsif ( p_to_pay_plan in ('XI','XJ','XK') and p_agency_subelement <> 'TRAI' ) then
2424 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2425 hr_utility.set_message_token('PAY_PLAN','XI, XJ or XK ');
2426 hr_utility.set_message_token('AGENCY_CODE','TRAI');
2427 hr_utility.raise_error;
2428 elsif ( p_to_pay_plan in ('NZ') and p_agency_subelement <> 'SM03' ) then
2429 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2430 hr_utility.set_message_token('PAY_PLAN','NZ');
2431 hr_utility.set_message_token('AGENCY_CODE','SM03');
2432 hr_utility.raise_error;
2433 elsif ( p_to_pay_plan in ('GE','GI') and substr(p_agency_subelement,1,2) <> 'LP') THEN
2434 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2435 hr_utility.set_message_token('PAY_PLAN','GE or GI');
2436 hr_utility.set_message_token('AGENCY_CODE','LP');
2437 hr_utility.raise_error;
2438 elsif p_effective_date < to_date('2007/05/01','RRRR/MM/DD') AND -- Bug# 6469079
2439 ( p_to_pay_plan = 'OF' and p_agency_subelement <> 'HUFH') THEN
2440 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2441 hr_utility.set_message_token('PAY_PLAN','OF');
2442 hr_utility.set_message_token('AGENCY_CODE','HUFH');
2443 hr_utility.raise_error;
2444 --Begin Bug 9503972
2445 elsif p_effective_date > to_date('2009/12/01','RRRR/MM/DD') AND
2446 ( p_to_pay_plan IN ('OE','OF') and (substr(p_agency_subelement,1,2) <> 'HF' OR p_agency_subelement <> 'HUFH') ) THEN
2447 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2448 hr_utility.set_message_token('PAY_PLAN','OE or OF');
2449 hr_utility.set_message_token('AGENCY_CODE','HF, HUFH');
2450 hr_utility.raise_error;
2451 --end Bug 9503972
2452 --Begin Bug#6469079
2453 elsif ( p_to_pay_plan in('OE','OF') and p_agency_subelement <> 'HUFH') THEN
2454 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2455 hr_utility.set_message_token('PAY_PLAN','OE or OF');
2456 hr_utility.set_message_token('AGENCY_CODE','HUFH');
2457 hr_utility.raise_error;
2458 --End Bug#6469079
2459 --Begin Bug# 5073313
2460 elsif p_effective_date >= fnd_date.canonical_to_date('2006/05/01') and
2461 ( p_to_pay_plan in ('HE','HH','HL','HS','HT','HW') and substr(p_agency_subelement,1,2) <> 'HS' )
2462 then
2463 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2464 hr_utility.set_message_token('PAY_PLAN',p_to_pay_plan);
2465 hr_utility.set_message_token('AGENCY_CODE','HS');
2466 hr_utility.raise_error;
2467 --End bug# 5073313
2468 --Begin Bug# 5745356
2469 elsif p_effective_date >= fnd_date.canonical_to_date('2006/10/01') and
2470 ( p_to_pay_plan in ('HA','HB','HC','HD','HF','HG','HI','HJ','HK','HM','HN','HO',',HP','HQ','HR','HV','HX','HY','HZ') and substr(p_agency_subelement,1,2) <> 'HS' )
2471 then
2472 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2473 hr_utility.set_message_token('PAY_PLAN',p_to_pay_plan);
2474 hr_utility.set_message_token('AGENCY_CODE','HS');
2475 hr_utility.raise_error;
2476 --End bug# 5745356
2477 --Begin Bug# 10189715
2478 elsif p_effective_date >= fnd_date.canonical_to_date('2010/09/01') and
2479 ( p_to_pay_plan in ('HU') and substr(p_agency_subelement,1,2) <> 'HS' )
2480 then
2481 hr_utility.set_message(8301,'GHR_37879_ALL_PROCEDURE_FAIL');
2482 hr_utility.set_message_token('PAY_PLAN',p_to_pay_plan);
2483 hr_utility.set_message_token('AGENCY_CODE','HS');
2484 hr_utility.raise_error;
2485 --End bug# 10189715
2486 else
2487 hr_utility.set_location('No error',14);
2488 end if;
2489
2490 -- 440.04.3
2491 -- added the following personal office identifiers on 9-oct-98
2492 -- 2267,2336,2562,2614,3231,3322,4252
2493 -- Update Date By Effective Date Comment
2494 -- 8 04/01/99 vravikan Added POI 4221
2495 -- 11/7 12/14/99 vravikan 31-Aug-1999 End date the Edit
2496 if p_effective_date < to_date('19'||'99/08/31','yyyy/mm/dd') then
2497 if p_to_pay_plan in ('DA','DS','DG','DP','DT')
2498 and
2499 (
2500 substr(p_agency_subelement,1,2) <> 'NV'
2501 or
2502 p_pers_office_identifier not in ('2256','2267','2336','2415','2431',
2503 '2562','2614','2896','3231','3322','4219','4221','4252')
2504 )
2505 then
2506 hr_utility.set_message(8301, 'GHR_37247_ALL_PROCEDURE_FAIL');
2507 hr_utility.raise_error;
2508 end if;
2509 end if;
2510 -- 420.05.3
2511 -- 11/5 12/14/99 vravikan 01-Aug-1999 Delete the Pay Plan DG
2512 -- Renumbered from 440.05.3 for the april release
2513 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
2514 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
2515 if ( p_effective_date >= to_date('19'||'99/08/01','yyyy/mm/dd') ) then
2516 if (
2517 p_to_pay_plan in ('DA','DS','DT')
2518 and
2519 p_to_grade_or_level not in ('00','01','02','03')
2520 )
2521 or
2522 (
2523 p_to_pay_plan = 'DP'
2524 and
2525 p_to_grade_or_level not in ('00','01','02','03','04','05')
2526 )
2527 or
2528 (
2529 p_to_pay_plan in ('ZA','ZP','ZS','ZT')
2530 and
2531 p_to_grade_or_level not in ('01','02','03','04','05')
2532 )
2533 then
2534 hr_utility.set_message(8301, 'GHR_37410_ALL_PROCEDURE_FAIL');
2535 hr_utility.raise_error;
2536 end if;
2537 else
2538 if (
2539 p_to_pay_plan in ('DA','DS','DT')
2540 and
2541 p_to_grade_or_level not in ('00','01','02','03')
2542 )
2543 or
2544 (
2545 p_to_pay_plan = 'DG'
2546 and
2547 p_pers_office_identifier in('2256','2896')
2548 and
2549 p_to_grade_or_level not in ('00','01','02','03','04','05')
2550 )
2551 or
2552 (
2553 p_to_pay_plan = 'DG'
2554 and
2555 p_pers_office_identifier in('2415','2431','4219')
2556 and
2557 p_to_grade_or_level not in ('00','01','02','03','04')
2558 )
2559 or
2560 (
2561 p_to_pay_plan = 'DP'
2562 and
2563 p_to_grade_or_level not in ('00','01','02','03','04','05')
2564 )
2565 or
2566 (
2567 p_to_pay_plan in ('ZA','ZP','ZS','ZT')
2568 and
2569 p_to_grade_or_level not in ('01','02','03','04','05')
2570 )
2571 then
2572 hr_utility.set_message(8301, 'GHR_37248_ALL_PROCEDURE_FAIL');
2573 hr_utility.raise_error;
2574 end if;
2575 end if;
2576 end if;
2577 -- 440.10.2
2578 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
2579 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
2580 If (
2581 p_first_NOAC_Lookup_Code = '480' or p_first_NOAC_Lookup_Code='762'
2582 )
2583 and
2584 p_to_pay_plan <>'ES'
2585 and
2586 p_to_pay_plan <>'FE'
2587 and
2588 p_to_pay_plan is not null
2589 then
2590 hr_utility.set_message(8301, 'GHR_37249_ALL_PROCEDURE_FAIL');
2591 hr_utility.raise_error;
2592 end if;
2593 end if;
2594 -- 440.12.2
2595 -- Update Date By Effective Date Comment
2596 -- 9 05/03/99 vravikan Added Pay Plan MS
2597 -- 10/30/03 Ashley Deleted Pay Plan EO
2598 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
2599 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
2600 hr_utility.set_location('to pay plan is ' || p_to_pay_plan ,10);
2601 hr_utility.set_location('from pay plan is ' || p_prior_pay_plan ,10);
2602 If p_first_NOAC_Lookup_Code = '879'
2603 and
2604 p_to_pay_plan not in ('ES','FE','MS','TF','TX')
2605 and
2606 p_to_pay_plan is not null
2607 and
2608 p_Pay_Rate_Determinant_Code <> 'S'
2609 then
2610 hr_utility.set_message(8301, 'GHR_37250_ALL_PROCEDURE_FAIL');
2611 hr_utility.raise_error;
2612 end if;
2613 end if;
2614
2615 -- 440.13.2
2616 -- Update/Change Date By Effective Date Comment
2617 -- 9/1 08/09/99 vravikan 01-Oct-98 Add Pay Plans CG,EO,ND,NG,NH,NJ,NK, and NT
2618 -- 9/5 08/12/99 vravikan 01-Apr-99 Add pay plans NC, NO, NP, and NR
2619 -- 11/9 12/14/99 vravikan 01-Nov-1999 Add Pay Plan PD
2620 -- 17-Aug-00 vravikan From the Start Add NB
2621 -- Dec 01 Patch 12/10/01 vravikan Delete OC
2622 -- 10/30/03 Ashley Added Pay Plans CM and EM
2623 --upd47 26-Jun-06 Raju From 01-Apr-2003 Terminate the edit
2624 if p_effective_date < fnd_date.canonical_to_date('2003/04/01') then
2625 if p_effective_date >= to_date('1999/11/01', 'yyyy/mm/dd') then
2626 If p_first_NOAC_Lookup_Code = '891' and
2627 p_to_pay_plan not in ('CG','CM','EM','EO','FM','GH','GM',
2628 'NC','ND','NG','NH','NJ','NK','NO','NP','NR','NT',
2629 'NB','PD','TM','VH','ZA','ZP','ZS','ZT') and
2630 substr(p_to_pay_plan,1,1) <>'D' and
2631 p_to_pay_plan is not null
2632 then
2633 hr_utility.set_message(8301, 'GHR_37412_ALL_PROCEDURE_FAIL');
2634 hr_utility.raise_error;
2635 end if;
2636 elsif p_effective_date >= to_date('1999/04/01', 'yyyy/mm/dd') then
2637 If p_first_NOAC_Lookup_Code = '891' and
2638 p_to_pay_plan not in ('CG','CM','EM','EO','FM','GH','GM',
2639 'NC','ND','NG','NH','NJ','NK','NO','NP','NR','NT',
2640 'NB','TM','VH','ZA','ZP','ZS','ZT') and
2641 substr(p_to_pay_plan,1,1) <>'D' and
2642 p_to_pay_plan is not null then
2643 hr_utility.set_message(8301, 'GHR_37070_ALL_PROCEDURE_FAIL');
2644 hr_utility.raise_error;
2645 end if;
2646 elsif p_effective_date >= to_date('1998/10/01', 'yyyy/mm/dd') then
2647 If p_first_NOAC_Lookup_Code = '891' and
2648 p_to_pay_plan not in ('CG','CM','EM','EO','FM','GH','GM',
2649 'ND','NG','NH','NJ','NK','NT','NB','TM','VH','ZA','ZP','ZS','ZT') and
2650 substr(p_to_pay_plan,1,1) <>'D' and
2651 p_to_pay_plan is not null then
2652 hr_utility.set_message(8301, 'GHR_37058_ALL_PROCEDURE_FAIL');
2653 hr_utility.raise_error;
2654 end if;
2655 else
2656 If p_first_NOAC_Lookup_Code = '891' and
2657 p_to_pay_plan not in ('CM','EM','FM','GH','GM','NB','TM','VH','ZA','ZP','ZS','ZT') and
2658 substr(p_to_pay_plan,1,1) <>'D' and
2659 p_to_pay_plan is not null then
2660 hr_utility.set_message(8301, 'GHR_37251_ALL_PROCEDURE_FAIL');
2661 hr_utility.raise_error;
2662 end if;
2663 end if;
2664 end if;
2665
2666 -- 440.22.2
2667 If p_first_NOAC_Lookup_Code in ('142','143','145','146','147','148',
2668 '149','542','543','546','548','549')
2669 and
2670 p_to_pay_plan not in ('ES','FB','FE','FJ','FX')
2671 and
2672 p_to_pay_plan is not null
2673 then
2674 hr_utility.set_message(8301, 'GHR_37252_ALL_PROCEDURE_FAIL');
2675 hr_utility.raise_error;
2676 end if;
2677
2678 -- 440.25.2
2679 --upd47 26-Jun-06 Raju From 01-Apr-2003 Terminate the edit
2680 if p_effective_date < fnd_date.canonical_to_date('2003/04/01') then
2681 If p_first_NOAC_Lookup_Code ='893' and
2682 p_to_pay_plan ='GM' then
2683 hr_utility.set_message(8301, 'GHR_37253_ALL_PROCEDURE_FAIL');
2684 hr_utility.raise_error;
2685 end if;
2686 end if;
2687
2688 -- 440.30.2
2689 -- Delete the first line of the edit (If effective date is later than october 31, 1993)
2690 l_Effective_Date :=p_Effective_Date;
2691 If substr( p_first_NOAC_Lookup_Code,1,1)='1'
2692 and
2693 p_agency not in ('AF','AR','DD','NV')
2694 and
2695 p_to_pay_plan = 'GM'
2696 then
2697 hr_utility.set_message(8301, 'GHR_37254_ALL_PROCEDURE_FAIL');
2698 hr_utility.raise_error;
2699 end if;
2700
2701 -- 440.35.2
2702 -- Delete the first line of the edit (If effective date is later than october 31, 1993)
2703
2704 IF
2705 (
2706 (substr( p_first_NOAC_Lookup_Code,1,1)='5'
2707 or
2708 p_first_NOAC_Lookup_Code ='721')
2709 and
2710 p_Supervisory_status_code = '8'
2711 )
2712 and
2713 p_to_pay_plan = 'GM'
2714 then
2715 hr_utility.set_message(8301, 'GHR_37255_ALL_PROCEDURE_FAIL');
2716 hr_utility.raise_error;
2717 end if;
2718
2719
2720 -- 440.40.2
2721 -- 12/8/00 vravikan From the Start
2722 -- Rewriting edit to
2723 -- If nature of action is 702,703, 713, or 740,
2724 -- Then pay plan may not be GM.
2725 If p_first_NOAC_Lookup_Code in ('702','703','713','740')
2726 and p_to_pay_plan is not null
2727 and p_to_pay_plan = 'GM'
2728 then
2729 hr_utility.set_message(8301, 'GHR_37256_ALL_PROCEDURE_FAIL');
2730 hr_utility.raise_error;
2731 end if;
2732
2733 -- 440.45.3
2734
2735 if ( p_Cur_Appt_Auth_1 = 'UAM'
2736 or
2737 p_Cur_Appt_Auth_2 = 'UAM' )
2738 and
2739 p_to_pay_plan = 'GS'
2740 then
2741 hr_utility.set_message(8301, 'GHR_37257_ALL_PROCEDURE_FAIL');
2742 hr_utility.raise_error;
2743 end if;
2744
2745 -- 440.46.2
2746 -- 12/8/00 vravikan 01-Oct-2000 New Edit
2747 -- If nature of action is 871, Then pay plan must be GG
2748 if l_effective_date >= fnd_date.canonical_to_date('2000/10/01') then
2749 if p_first_NOAC_Lookup_Code = '871' and
2750 p_to_pay_plan <> 'GG' then
2751 hr_utility.set_message(8301, 'GHR_37677_ALL_PROCEDURE_FAIL');
2752 hr_utility.raise_error;
2753 end if;
2754 end if;
2755 end chk_pay_plan;
2756
2757 /*
2758 -- Pay Rate Determinant
2759 */
2760
2761 procedure chk_pay_rate_determinant
2762 (p_pay_rate_determinant_code in varchar2
2763 ,p_prior_pay_rate_det_code in varchar2 -- non SF52
2764 ,p_to_pay_plan in varchar2
2765 ,p_first_noa_lookup_code in varchar2
2766 ,p_duty_station_lookup_code in varchar2
2767 ,p_agency in varchar2
2768 ,p_effective_date in date
2769 ) is
2770 begin
2771
2772 -- 450.02.3
2773 if p_pay_rate_determinant_code = '4' and
2774 (
2775 substr(p_to_pay_plan, 1, 1) = 'W' or
2776 substr(p_to_pay_plan, 1, 1) = 'X'
2777 )
2778 then
2779 hr_utility.set_message(8301, 'GHR_37258_ALL_PROCEDURE_FAIL');
2780 hr_utility.raise_error;
2781 end if;
2782
2783 -- 450.03.2
2784 -- Update Date By Effective Date Bug Comment
2785 -- 10/07/02 vnarasim 2468911 Logic Modified.See bug desc.
2786
2787 if p_pay_rate_determinant_code = '4'
2788 and
2789 (p_to_pay_plan = 'GM' or p_to_pay_plan = 'GS')
2790 and
2791 not(
2792 (p_prior_pay_rate_det_code = '4' or
2793 p_prior_pay_rate_det_code is null) or
2794 ((p_first_noa_lookup_code in ('702','703','740','741') or
2795 substr(p_first_noa_lookup_code,1,1)= '5'
2796 ) and
2797 (p_prior_pay_rate_det_code in ('5','6','E','F','J','K') or
2798 p_prior_pay_rate_det_code is null
2799 ) )
2800 )
2801 then
2802 hr_utility.set_message(8301, 'GHR_37259_ALL_PROCEDURE_FAIL');
2803 hr_utility.raise_error;
2804 end if;
2805
2806 -- 450.04.3
2807 --upd47 26-Jun-06 Raju From 01-Apr-2003 Terminate the edit
2808 if p_effective_date < fnd_date.canonical_to_date('2003/04/01') then
2809 if (p_to_pay_plan = 'FA' or p_to_pay_plan = 'EX') and
2810 p_pay_rate_determinant_code not in ('C','S','0') and
2811 p_pay_rate_determinant_code is not null then
2812 hr_utility.set_message(8301, 'GHR_37260_ALL_PROCEDURE_FAIL');
2813 hr_utility.raise_error;
2814 end if;
2815 end if;
2816
2817 -- 450.05.3
2818 -- 12/8/00 vravikan 1-oct-00 New Edit
2819 -- If Pay Plan is GH or GM,
2820 -- Then pay rate determinant may not be A,B,E,F,U, or V
2821 IF p_effective_date >= fnd_date.canonical_to_date('2000/10/01') then
2822 IF (p_to_pay_plan = 'GH' or p_to_pay_plan = 'GM')
2823 and
2824 p_pay_rate_determinant_code in ('A','B','E','F','U','V')
2825 and
2826 p_pay_rate_determinant_code is not null
2827 then
2828 hr_utility.set_message(8301, 'GHR_37859_ALL_PROCEDURE_FAIL');
2829 hr_utility.raise_error;
2830 END IF;
2831 END IF;
2832
2833
2834 -- 450.10.3
2835 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
2836 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
2837 if p_pay_rate_determinant_code = 'C' and
2838 (
2839 p_to_pay_plan in ('ED','EE','EF','EG','EH','EI','ZZ')
2840 or
2841 substr(p_to_pay_plan,1,1) in ('B','W','X')
2842 ) then
2843 hr_utility.set_message(8301, 'GHR_37261_ALL_PROCEDURE_FAIL');
2844 hr_utility.raise_error;
2845 end if;
2846 end if;
2847 -- 450.19.3
2848 -- UPDATE_DATE UPDATED_BY EFFECTIVE_DATE COMMENTS
2849 -----------------------------------------------------------------------------
2850 -- 18-Oct-04 Madhuri from start of edit Modifying the edit to
2851 -- include the PRD-2.
2852 -- 11-DEC-08 AVR from start of edit Modified the edit to
2853 -- include the PRD-D
2854 --
2855 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
2856 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
2857 if p_to_pay_plan = 'ES' and
2858 p_pay_rate_determinant_code <> 'C'
2859 and
2860 p_pay_rate_determinant_code <> '0'
2861 and
2862 p_pay_rate_determinant_code <> '2'
2863 and
2864 p_pay_rate_determinant_code <> 'D'--Bug# 7633560
2865 and
2866 p_pay_rate_determinant_code is not null
2867 then
2868 hr_utility.set_message(8301, 'GHR_37262_ALL_PROCEDURE_FAIL');
2869 hr_utility.raise_error;
2870 end if;
2871 end if;
2872
2873 -- 450.22.2
2874 if p_effective_date < to_date('2005/05/01','yyyy/mm/dd') then
2875 if ( p_first_noa_lookup_code= '740' or
2876 p_first_noa_lookup_code= '741')
2877 and
2878 p_pay_rate_determinant_code not in ('A','B','E','F',
2879 'M','U','V')
2880 and
2881 p_pay_rate_determinant_code is not null
2882 and
2883 p_prior_pay_rate_det_code not in ('A','B','E','F',
2884 'M','U','V')
2885 and
2886 p_prior_pay_rate_det_code is not null
2887 then
2888 hr_utility.set_message(8301, 'GHR_37263_ALL_PROCEDURE_FAIL');
2889 hr_utility.set_message_token('PRD_LIST','A, B, E, F, M, U, or V');
2890 hr_utility.raise_error;
2891 end if;
2892 elsif p_effective_date >= to_date('2005/05/01','yyyy/mm/dd') then
2893 if ( p_first_noa_lookup_code= '740' or
2894 p_first_noa_lookup_code= '741') and
2895 p_pay_rate_determinant_code not in ('A','B','E','F','U','V') and
2896 p_pay_rate_determinant_code is not null and
2897 p_prior_pay_rate_det_code not in ('A','B','E','F','U','V') and
2898 p_prior_pay_rate_det_code is not null then
2899 hr_utility.set_message(8301, 'GHR_37263_ALL_PROCEDURE_FAIL');
2900 hr_utility.set_message_token('PRD_LIST','A, B, E, F, U, or V');
2901 hr_utility.raise_error;
2902 end if;
2903 end if;
2904
2905 -- 450.25.2
2906 if (p_first_noa_lookup_code ='702' or
2907 p_first_noa_lookup_code ='721')
2908 and
2909 p_pay_rate_determinant_code in ('A','B','E','F','U','V') then
2910 hr_utility.set_message(8301, 'GHR_37264_ALL_PROCEDURE_FAIL');
2911 hr_utility.raise_error;
2912 end if;
2913
2914 -- 450.28.2
2915 -- Update/Change Date By Effective Date Comment
2916 -- 9/4 08/10/99 vravikan 01-Mar-99 Add PRD T.
2917 -- UPD 43(Bug 4567571) Raju 09-Nov-2005 Delete PRD M effective date from 01-May-2005
2918 --upd47 26-Jun-06 Raju From 01-Apr-2003 Terminate the edit
2919 if p_effective_date < fnd_date.canonical_to_date('2003/04/01') then
2920 If p_effective_date >= fnd_date.canonical_to_date('20'||'05/05/01') then
2921 if (p_first_noa_lookup_code = '892' or
2922 p_first_noa_lookup_code = '893') and
2923 p_pay_rate_determinant_code not in ('0','5','6','7','A','B','E','F','T') and
2924 p_pay_rate_determinant_code is not null
2925 then
2926 hr_utility.set_message(8301, 'GHR_37061_ALL_PROCEDURE_FAIL');
2927 hr_utility.set_message_token('PRD_LIST','0, 5, 6, 7, A, B, E, F or T');
2928 hr_utility.raise_error;
2929 end if;
2930 elsIf p_effective_date >= fnd_date.canonical_to_date('19'||'99/03/01') then
2931 if (p_first_noa_lookup_code = '892' or
2932 p_first_noa_lookup_code = '893') and
2933 p_pay_rate_determinant_code not in ('0','5','6','7','A','B','E','F','M','T') and
2934 p_pay_rate_determinant_code is not null
2935 then
2936 hr_utility.set_message(8301, 'GHR_37061_ALL_PROCEDURE_FAIL');
2937 hr_utility.set_message_token('PRD_LIST','0, 5, 6, 7, A, B, E, F, M or T');
2938 hr_utility.raise_error;
2939 end if;
2940 else
2941 if (p_first_noa_lookup_code = '892' or
2942 p_first_noa_lookup_code = '893') and
2943 p_pay_rate_determinant_code not in ('0','5','6','7','A',
2944 'B','E','F','M') and
2945 p_pay_rate_determinant_code is not null
2946 then
2947 hr_utility.set_message(8301, 'GHR_37265_ALL_PROCEDURE_FAIL');
2948 hr_utility.raise_error;
2949 end if;
2950 end if;
2951 end if;
2952
2953 -- 450.30.3
2954 -------------------------------------------------------------------------------
2955 -- Modified by Date Comments
2956 -------------------------------------------------------------------------------
2957 -- Madhuri 01-MAR-05 Retroactively end dating as of 31-JAN-2002
2958 -- UPDATE 38 Changes
2959 -------------------------------------------------------------------------------
2960 IF p_effective_date <= fnd_date.canonical_to_date('20'||'02/01/31') THEN
2961 if p_pay_rate_determinant_code = 'M' and
2962 /* This is the code for the cities of Boston, Chicago, Los Angeles, New York, Philadelphia, San Diego,
2963 San Francisco, and Washington D.C.*/
2964 (
2965 substr(p_duty_station_lookup_code,1,2) not in ('05','08','41','45','56','71','74','80','11') and
2966
2967 /* This selects the counties that make up Boston CMSA */
2968 (
2969 substr(p_duty_station_lookup_code,1,2) = '25' and
2970 substr(p_duty_station_lookup_code,7,3) not in ('009','017','021','023','025')
2971 ) and
2972 /* This selects the parts of other counties that make up Boston CMSA */
2973 /* part of Bristol County */
2974 p_duty_station_lookup_code not in ('250007005','250039005','250096005','250188005','250251005',
2975 '250254005','250281005','250299005','250315005','250385005','250670005','250850005','250911005',
2976 '250912005','250913005','250924005','251064005','251062005','251135005','251219005','251225005',
2977 '251280005')
2978 and
2979 /* part of Hampden County */
2980 p_duty_station_lookup_code <> '250489013'
2981 and
2982 /* part of Worcester County */
2983 p_duty_station_lookup_code not in ('250032027','250055027','250079027','250080027','250098027',
2984 '250110027','250902027','250910027','250916027','250918027','250927027','250944027','250117027',
2985 '250123027','250150027','250185027','250186027','250189027','250220027','250252027','250263027',
2986 '250272027','250280027','250332027','250350027','250390027','250436027','250467027','250480027',
2987 '250510027','250555027','250565027','250585027','250610027','250619027','250640027','250664027',
2988 '250745027','250780027','250785027','250820027','250834027','250900027','250943027','250980027',
2989 '250999027','251450027','251800027','251100027','251172027','251200027','251203027','251204027',
2990 '251210027','251228027','251240027','251260027','251273027','251266027','251271027','251278027',
2991 '251269027','251283027','251310027','251320027','251376027','251380027','251390027','251395027',
2992 '251410027','251439027','251455027','251470027','251500027','251520027')
2993 and
2994 /* New Hampshire */
2995 /* part of Hillsborough County */
2996 p_duty_station_lookup_code not in ('330011011','330018011','330031011','330160011','330180011',
2997 '330234011','330240011','330299011','330310011','330324011','330334011','330340011','330344011',
2998 '330350011','330357011','330401011','330434011','330509011','330540011')
2999 and
3000 /* part of Merrimack County */
3001 p_duty_station_lookup_code <> '330236013' and
3002 /* part of Rockingham County */
3003 p_duty_station_lookup_code not in ('330012015','330013015','330025015','330032015','330045015',
3004 '330087015','330085015','330105015','330108015','330112015','330123015','330130015','330153015',
3005 '330176015','330195015','330200015','330199015','330201015','330252015','330355015','330354015',
3006 '330356015','330370015','330381015','330382015','330391015','330384015','330417015','330430015',
3007 '330435015','330445015','330447015','330448015','330462015','330466015','330474015','330475015',
3008 '330478015','330255015','330305015','330533015','330527015','330551015')
3009 and
3010 /* part of Strafford County */
3011 p_duty_station_lookup_code not in ('330029017','330090017','330100017','330140017','330281017',
3012 '330311017','330342017','330345017','330440017','330443017','330470017')
3013 and
3014 /* Maine */
3015 /* part of York County */
3016 p_duty_station_lookup_code not in ('230450031','231445031','232450031','234250031','234300031',
3017 '237450031','239800031','239900031','239950031')
3018 and
3019 /* Connecticut */
3020 /* part of Windham County */
3021 p_duty_station_lookup_code not in ('090231015','090259015','090373015','090500015','090603015',
3022 '090749015')
3023 and
3024 /* Chiacago */
3025 /* Illinois */
3026 (
3027 substr(p_duty_station_lookup_code,1,2) = '17' and
3028 substr(p_duty_station_lookup_code,7,3) not in ('031','037','043','063','089','091','093','097',
3029 '111','197')
3030 ) and
3031 /* Indiana */
3032 (
3033 substr(p_duty_station_lookup_code,1,2) = '18' and
3034 substr(p_duty_station_lookup_code,7,3) not in ('089','027')
3035 ) and
3036 /* Wisconsin */
3037 (
3038 substr(p_duty_station_lookup_code,1,2) = '55' and
3039 substr(p_duty_station_lookup_code,7,3) <> '059'
3040 ) and
3041 /* Los Angeles */
3042 (
3043 (substr(p_duty_station_lookup_code,1,2) = '06' and
3044 substr(p_duty_station_lookup_code,7,3) not in ('037','059','065','071','083','111')) and
3045 p_duty_station_lookup_code <> '061077029'
3046 ) and
3047 /* New York */
3048 (
3049 substr(p_duty_station_lookup_code,1,2) = '36' and
3050 substr(p_duty_station_lookup_code,7,3) not in ('005','027','047','059','061','071','079','081',
3051 '085','087','103','119')
3052 ) and
3053 /* New Jersey */
3054 (
3055 substr(p_duty_station_lookup_code,1,2) = '34' and
3056 substr(p_duty_station_lookup_code,7,3) not in ('003','013','017','019','021','023','025','027',
3057 '029','031','035','037','039','041')
3058 ) and
3059 /* Connecticut */
3060 (
3061 substr(p_duty_station_lookup_code,1,2) = '09' and
3062 substr(p_duty_station_lookup_code,7,3) not in ('001','0009')
3063 ) and
3064 /* part of Litchfield County */
3065 p_duty_station_lookup_code not in ('090051005','090083005','090247005','090629005','090740005',
3066 '090802005','090805005','090450005','090454005','090535005','090817005','090857005')
3067 and
3068 /* part of Middlesex County */
3069 p_duty_station_lookup_code not in ('090130007','090332007')
3070 and
3071 /* Pennsylvania */
3072 (
3073 substr(p_duty_station_lookup_code,1,2) = '42' and
3074 substr(p_duty_station_lookup_code,7,3) <> '103'
3075 ) and
3076 /* Philadelphia */
3077 /* Pennsylvania */
3078 (
3079 substr(p_duty_station_lookup_code,1,2) = '42' and
3080 substr(p_duty_station_lookup_code,7,3) not in ('017','029','045','091','101')
3081 ) and
3082 /* New Jersey */
3083 (
3084 substr(p_duty_station_lookup_code,1,2) = '34' and
3085 substr(p_duty_station_lookup_code,7,3) not in ('001','005','007','009','011','015','033')
3086 ) and
3087 /* Delaware */
3088 (
3089 substr(p_duty_station_lookup_code,1,2) = '10' and
3090 substr(p_duty_station_lookup_code,7,3) <> '015'
3091 ) and
3092 /* San Diego */
3093 (
3094 substr(p_duty_station_lookup_code,1,2) = '06' and
3095 substr(p_duty_station_lookup_code,7,3) <> '073'
3096 ) and
3097 /* San Francisco */
3098 (
3099 substr(p_duty_station_lookup_code,1,2) = '06' and
3100 substr(p_duty_station_lookup_code,7,3) not in ('001','013','041','055','075','081','085',
3101 '087','095','097')
3102 ) and
3103 /* Washington DC */
3104 /* Maryland */
3105 (
3106 substr(p_duty_station_lookup_code,1,2) = '24' and
3107 substr(p_duty_station_lookup_code,7,3) not in ('003','005','009','013','017','021','025',
3108 '027','031','033','035','037','043','510')
3109 ) and
3110 /* Virginia */
3111 (
3112 substr(p_duty_station_lookup_code,1,2) = '51' and
3113 substr(p_duty_station_lookup_code,7,3) not in ('013','043','047','059','061','099','107',
3114 '153','177','179','187','510','600','610','630','683','685')
3115 ) and
3116 /* West Virginia */
3117 (
3118 substr(p_duty_station_lookup_code,1,2) = '54' and
3119 substr(p_duty_station_lookup_code,7,3) not in ('003','037')
3120 )
3121 ) then
3122 hr_utility.set_message(8301, 'GHR_37266_ALL_PROCEDURE_FAIL');
3123 hr_utility.raise_error;
3124 end if;
3125 END IF;
3126 -------------------------------------------------------------------------------
3127
3128 -- 450.40.3
3129 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
3130 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
3131 if p_pay_rate_determinant_code = 'P' and
3132 p_agency <>'VA' then
3133 hr_utility.set_message(8301, 'GHR_37267_ALL_PROCEDURE_FAIL');
3134 hr_utility.raise_error;
3135 end if;
3136 end if;
3137
3138 -- 450.42.3
3139 -------------------------------------------------------------------------------
3140 -- Update/Change Date By Effective Date Comment
3141 -- 9/4 08/10/99 vravikan 01-Mar-99 Exclude PRD T.
3142 -------------------------------------------------------------------------------
3143 -- UPD 56 (Bug# 8309414) Terminating the edit eff date 13-Aug-2007
3144 if p_effective_date < fnd_date.canonical_to_date('2007/08/14') then
3145 If p_effective_date >= fnd_date.canonical_to_date('19'||'99/03/01') then
3146 if (p_to_pay_plan= 'GM' or
3147 p_to_pay_plan= 'GS'
3148 ) and
3149 p_pay_rate_determinant_code in ('P','T') then
3150 hr_utility.set_message(8301, 'GHR_37062_ALL_PROCEDURE_FAIL');
3151 hr_utility.raise_error;
3152 end if;
3153 else
3154 if (p_to_pay_plan= 'GM' or
3155 p_to_pay_plan= 'GS'
3156 ) and
3157 p_pay_rate_determinant_code = 'P' then
3158 hr_utility.set_message(8301, 'GHR_37268_ALL_PROCEDURE_FAIL');
3159 hr_utility.raise_error;
3160 end if;
3161 end if;
3162 end if;
3163 -- 450.43.3
3164
3165 -- If pay rate determinant is Z,
3166 -- Then pay plan must not be FO or FP,
3167 -- And agency must be AM, GY, or ST,
3168 -- And the first two positions of duty station must be CA or MX.
3169
3170 -- Update/Change Date By Comment
3171 -- 30-Oct-03 Ashley New Edit
3172
3173
3174 IF p_pay_rate_determinant_code = 'Z' AND
3175 p_to_pay_plan IN ('FO','FP') AND p_agency NOT IN ('AM','GY','ST') AND
3176 substr(p_duty_station_lookup_code,1,2) NOT IN ('CA','MX') THEN
3177 hr_utility.set_message(8301, 'GHR_38841_ALL_PROCEDURE_FAIL');
3178 hr_utility.raise_error;
3179 END IF;
3180
3181 end chk_pay_rate_determinant;
3182
3183 end GHR_CPDF_CHECK3;
[Home] [Help]