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