1 package body GHR_CPDF_CHECK2 as
2 /* $Header: ghcpdf02.pkb 120.4.12000000.6 2007/04/20 09:39:28 utokachi noship $ */
3 --
4 --
5 /* Name:
6 Instructional Program
7 */
8 procedure chk_instructional_pgm
9 (p_education_level in varchar2
10 ,p_academic_discipline in varchar2
11 ,p_year_degree_attained in varchar2
12 ,p_first_noac_lookup_code in varchar2
13 ,p_effective_date in date
14 ,p_tenure_group_code in varchar2
15 ,p_to_pay_plan in varchar2
16 ,p_employee_date_of_birth in Date
17 ) is
18 begin
19
20 -- 005.02.3
21 if to_number(p_education_level) > 12
22 and
23 p_academic_discipline is null
24 then
25 hr_utility.set_message(8301, 'GHR_37101_ALL_PROCEDURE_FAIL');
26 hr_utility.raise_error;
27 end if;
28 ------
29
30 -- 780.04.3
31 -- Update date By Start Date Comment
32 -- 28-Nov-2002 Madhuri From the begining added education level cond to edit
33 -- this edit has been renamed from 005.04.3
34 if p_academic_discipline is not null
35 and
36 p_year_degree_attained is null
37 and
38 to_number(p_education_level) not in (14,16,18,20,22)
39 then
40 hr_utility.set_message(8301, 'GHR_37102_ALL_PROCEDURE_FAIL');
41 hr_utility.raise_error;
42 end if;
43
44 -- 005.07.1
45 -- 28-NOV-02 Madhuri End dated the edit with 31-July-2002
46
47 if p_effective_date <= to_date('2002/07/31','yyyy/mm/dd') then
48 if (
49 p_education_level <>'06' and
50 p_education_level <>'10' and
51 to_number(p_education_level) < 13
52 ) and
53 (p_academic_discipline is not null or p_year_degree_attained is not null )
54 then
55 hr_utility.set_message(8301, 'GHR_37103_ALL_PROCEDURE_FAIL');
56 hr_utility.raise_error;
57 end if;
58 end if;
59
60 -- 780.05.1
61 -- this edit has been renamed from 005.10.1
62 if p_year_degree_attained is not null
63 and
64 p_employee_date_of_birth is not null
65 and
66 (
67 to_number(p_year_degree_attained) <
68 (to_number(to_char(p_employee_date_of_birth,'YYYY'))+17)
69 )
70 then
71 hr_utility.set_message(8301, 'GHR_37104_ALL_PROCEDURE_FAIL');
72 hr_utility.raise_error;
73 end if;
74
75 -- 005.13.2
76
77 -- updation Date By Comment
78 -- 28-NOV-2002 Madhuri End dated the edit as of 31-Jul-2002
79
80 if p_effective_date<=to_date('2002/07/31','yyyy/mm/dd') then
81 if (
82 p_education_level <> '06' and p_education_level <> '10'
83 and
84 to_number(p_education_level) < 13
85 )
86 and
87 (
88 p_academic_discipline is not null
89 or
90 p_year_degree_attained is not null
91 )
92 then
93 hr_utility.set_message(8301, 'GHR_37105_ALL_PROCEDURE_FAIL');
94 hr_utility.raise_error;
95 end if;
96 end if;
97
98 -- 005.13.3
99 -- 28-NOV-2002 Madhuri Created the edit from 01-Aug-2002
100 -- 30-DEC-2002 VNARASIM Changed condition "to_number(p_education_level) >= 13" to
101 -- "to_number(p_education_level) < 13".
102
103 if p_effective_date >= to_date('2002/08/01','yyyy/mm/dd') then
104 if (
105 p_education_level <> '06' and p_education_level <> '10'
106 and
107 to_number(p_education_level) < 13
108 )
109 and
110 (
111 p_academic_discipline is not null
112 or
113 p_year_degree_attained is not null
114 )
115 then
116 hr_utility.set_message(8301, 'GHR_37927_ALL_PROCEDURE_FAIL');
117 hr_utility.raise_error;
118 end if;
119 end if;
120
121 -- 005.15.2
122 if substr(p_first_noac_lookup_code,1,1)='1'
123 and
124 p_effective_date > TO_DATE('1993/09/30', 'YYYY/MM/DD')
125 and
126 (p_tenure_group_code ='1' or p_tenure_group_code= '2' )
127 and
128 (p_education_level ='06' or p_education_level='10' )
129 and
130 (p_academic_discipline is null or p_year_degree_attained is null)
131 then
132 hr_utility.set_message(8301, 'GHR_37106_ALL_PROCEDURE_FAIL');
133 hr_utility.raise_error;
134 end if;
135
136 -- 005.20.2
137 if substr(p_first_noac_lookup_code,1,1)='1'
138 and
139 p_effective_date > TO_DATE('1993/09/30', 'YYYY/MM/DD')
140 and
141 p_to_pay_plan ='ES'
142 and
143 (p_education_level ='06' or p_education_level='10' )
144 and
145 (p_academic_discipline is null or p_year_degree_attained is null)
146 then
147 hr_utility.set_message(8301, 'GHR_37107_ALL_PROCEDURE_FAIL');
148 hr_utility.raise_error;
149 end if;
150
151 end chk_instructional_pgm;
152
153 /* Name:
154 chk_Award_Amount
155 */
156
157 procedure chk_Award_Amount
158 (p_First_NOAC_Lookup_Code in varchar2
159 -- Bug#4486823 RRR Changes
160 ,p_First_NOAC_Lookup_desc in varchar2
161 ,p_One_Time_Payment_Amount in number
162 ,p_To_Basic_Pay in number
163 ,p_Adj_Base_Pay in number
164 ,p_First_Action_NOA_LA_Code1 in varchar2
165 ,p_First_Action_NOA_LA_Code2 in varchar2
166 ,p_to_pay_plan in varchar2
167 ,p_effective_date in date
168 ) is
169 begin
170
171 -- 050.02.2
172 -- Award Req 8/15/00 vravikan 30-sep-2000 End date
173 -- vravikan 01-Oct-2000 Add 840-847
174 -- vnarasim 01-OCT-2000 Add 848
175 -- Ashley 10-OCT-2003 Add 849
176 -- vnarasim 22-MAR-2006 Removed 825
177 -- UPD 50(Bug 5745356) Raju 01-Oct-2006 Delete 849
178 -- UPD 51(Bug 5745356) Raju From 01-Jan-2007 add NOAs 826,849,885,886,887,889
179 -- 825 removed from the this edit as 825 is moved to incentive family
180
181 if p_effective_date <= to_date('2000/09/30','yyyy/mm/dd') then
182 if (p_First_NOAC_Lookup_Code in ('817', '849','872','873','874',
183 '875','876','877','878','879','885','889') OR
184 (p_First_NOAC_Lookup_Code = '815' AND p_First_NOAC_Lookup_desc = 'Recruitment Bonus') OR
185 (p_First_NOAC_Lookup_Code = '816' AND p_First_NOAC_Lookup_desc = 'Relocation Bonus')
186 ) and
187 (p_One_Time_Payment_Amount <= 0 or
188 p_One_Time_Payment_Amount is null)
189 then
190 hr_utility.set_message(8301, 'GHR_37108_ALL_PROCEDURE_FAIL');
191 hr_utility.raise_error;
192 end if;
193 elsif p_effective_date < to_date('2006/10/01','yyyy/mm/dd') then
194
195 if (p_First_NOAC_Lookup_Code in ('817', '840','841','842','843',
196 '844','845','846','847','848','849','878','879') OR
197 (p_First_NOAC_Lookup_Code = '815' AND p_First_NOAC_Lookup_desc = 'Recruitment Bonus') OR
198 (p_First_NOAC_Lookup_Code = '816' AND p_First_NOAC_Lookup_desc = 'Relocation Bonus')
199 ) and
200 (p_One_Time_Payment_Amount <= 0 or
201 p_One_Time_Payment_Amount is null)
202 then
203 hr_utility.set_message(8301, 'GHR_37414_ALL_PROCEDURE_FAIL');
204 hr_utility.set_message_token('NOA_CODE','815, 816, 817, 840 through 849, 878, or 879');
205 hr_utility.raise_error;
206 end if;
207 elsif p_effective_date < to_date('2007/01/01','yyyy/mm/dd') then
208 if (p_First_NOAC_Lookup_Code in ('817', '840','841','842','843',
209 '844','845','846','847','848','878','879') OR
210 (p_First_NOAC_Lookup_Code = '815' AND p_First_NOAC_Lookup_desc = 'Recruitment Bonus') OR
211 (p_First_NOAC_Lookup_Code = '816' AND p_First_NOAC_Lookup_desc = 'Relocation Bonus')
212 ) and
213 (p_One_Time_Payment_Amount <= 0 or
214 p_One_Time_Payment_Amount is null)
215 then
216 hr_utility.set_message(8301, 'GHR_37414_ALL_PROCEDURE_FAIL');
217 hr_utility.set_message_token('NOA_CODE','815, 816, 817, 840 through 848, 878, or 879');
218 hr_utility.raise_error;
219 end if;
220 else
221 if (p_First_NOAC_Lookup_Code in ('817', '826', '840','841','842','843',
222 '844','845','846','847','849','848','878','879','885','886','887','889') OR
223 (p_First_NOAC_Lookup_Code = '815' AND p_First_NOAC_Lookup_desc = 'Recruitment Bonus') OR
224 (p_First_NOAC_Lookup_Code = '816' AND p_First_NOAC_Lookup_desc = 'Relocation Bonus')
225 ) and
226 (p_One_Time_Payment_Amount <= 0 or
227 p_One_Time_Payment_Amount is null)
228 then
229 hr_utility.set_message(8301, 'GHR_37414_ALL_PROCEDURE_FAIL');
230 hr_utility.set_message_token('NOA_CODE','815, 816, 817, 826, 840 through 849, 878, 879, 885, 886, 887 or 889');
231 hr_utility.raise_error;
232 end if;
233 end if;
234
235 -- 050.04.2
236 -------------------------------------------------------------------------
237 -- Modified by Date Comments
238 -------------------------------------------------------------------------
239 -- Award Req 8/15/00 vravikan 30-sep-2000 End date
240 -- 01-Oct-2000 Add 840-847
241 -- vnarasim 01-Oct-2000 Add 848
242 -- Ashley 30-OCT-2003 Add 849
243 -- UPD 38 Madhuri 01-MAR-2005 add 826, 827 to list
244 -- UPD 41(Bug 4567571) Raju 08-Nov-2005 Delete 827 from list
245 -- vnarasim 22-Mar-2006 Delete 825
246 -- vnarasim 12-APR-2006 Added 825.
247 -- UPD 50(Bug 5745356) Raju 01-Oct-2006 Delete 849
248 -- UPD 51(Bug 5911585) AVR 03-MAR-2007 Delete the edit as of 01-JAN-2004.
249 -------------------------------------------------------------------------
250 if p_effective_date <= to_date('2000/09/30','yyyy/mm/dd') then
251 if p_One_Time_Payment_Amount is not null
252 and
253 p_First_NOAC_Lookup_Code not in ('815','816','817','825','826','849',
254 '872','873','874','875',
255 '876','877','878','879','885','889')
256 then
257 hr_utility.set_message(8301, 'GHR_37109_ALL_PROCEDURE_FAIL');
258 hr_utility.raise_error;
259 end if;
260 -----elsif p_effective_date < to_date('2006/10/01','yyyy/mm/dd') then
261 elsif p_effective_date < to_date('2004/01/01','yyyy/mm/dd') then
262 if p_One_Time_Payment_Amount is not null
263 and
264 p_First_NOAC_Lookup_Code not in ('815','816','817','825','826','840','841',
265 '842','843','844','845','846',
266 '847','848','849','878','879',
267 '885','886','887','889') --bug 5482191
268 then
269 hr_utility.set_message(8301, 'GHR_37415_ALL_PROCEDURE_FAIL');
270 hr_utility.set_message_token('NOA_CODE','815, 816, 817, 825, 840 through 849, 878, or 879');
271 hr_utility.raise_error;
272 end if;
273 else
274 null;
275 /***** Commented by AVR for Bug 5911585
276 if p_One_Time_Payment_Amount is not null and
277 p_First_NOAC_Lookup_Code not in ('815','816','817','825','826','840','841',
278 '842','843','844','845','846',
279 '847','848','878','879',
280 '885','886','887','889')
281 then
282 hr_utility.set_message(8301, 'GHR_37415_ALL_PROCEDURE_FAIL');
283 hr_utility.set_message_token('NOA_CODE','815, 816, 817, 825, 840 through 848, 878, or 879');
284 hr_utility.raise_error;
285 end if;
286 ************* AVR - Bug 5911585 end ******/
287 end if;
288 -------------------------------------------------------------------------
289
290 -- 17-Aug-00 vravikan From the Start Change from equal to 25% of basic+locality adj.
291 -- to not more than 25% of basic+locality adj.
292 -- 050.06.2
293 if p_First_NOAC_Lookup_Code ='819'
294 and
295 p_One_Time_Payment_Amount <>0
296 and
297 p_One_Time_Payment_Amount is not null
298 and
299 round(p_One_Time_Payment_Amount,0) > round(.25*(p_Adj_Base_Pay),0)
300 then
301 hr_utility.set_message(8301, 'GHR_37110_ALL_PROCEDURE_FAIL');
302 hr_utility.raise_error;
303 end if;
304
305 --Commented as per EOY 2003 cpdf changes by Ashley
306 -- 050.07.2
307 -- Award Req 8/15/00 vravikan 30-sep-2000 End date
308 /* if p_effective_date <= to_date('2000/09/30','yyyy/mm/dd') then
309 if p_First_NOAC_Lookup_Code ='889'
310 and
311 p_To_Pay_Plan ='GM'
312 and
313 p_One_Time_Payment_Amount > (p_TO_Basic_Pay * .20)
314 then
315 hr_utility.set_message(8301, 'GHR_37111_ALL_PROCEDURE_FAIL');
316 hr_utility.raise_error;
317 end if;
318 end if;*/
319
320 -- 050.10.2
321 if p_First_NOAC_Lookup_Code ='818'
322 and
323 not
324 (p_One_Time_Payment_Amount = 0
325 or
326 p_One_Time_Payment_Amount between 10 and 25)
327 and
328 p_One_Time_Payment_Amount is not null
329 then
330 hr_utility.set_message(8301, 'GHR_37112_ALL_PROCEDURE_FAIL');
331 hr_utility.raise_error;
332 end if;
333
334 -- 050.15.2
335 if p_First_NOAC_Lookup_Code ='818'
336 and
337 (substr(p_To_Pay_Plan,1,1) in ('B','W','X')
338 or
339 p_To_Pay_Plan ='ES')
340 then
341 hr_utility.set_message(8301, 'GHR_37113_ALL_PROCEDURE_FAIL');
342 hr_utility.raise_error;
343 end if;
344
345 -- 050.20.2
346 -- UPD 41(Bug 4567571) Raju 08-Nov-2005 Terminate from 01-May-2002
347 if p_effective_date < to_date('2002/05/01','yyyy/mm/dd') then
348 if p_First_NOAC_Lookup_Code ='815'
349 and
350 p_One_Time_Payment_Amount > .25 * p_TO_Basic_Pay
351 and
352 p_One_Time_Payment_Amount is not null
353 then
354 hr_utility.set_message(8301, 'GHR_37114_ALL_PROCEDURE_FAIL');
355 hr_utility.raise_error;
356 end if;
357 End if;
358
359
360 --Commented as per EOY 2003 cpdf changes by Ashley
361 -- 050.28.2
362 -- Award Req 8/15/00 vravikan 30-sep-2000 End date
363 /* if p_effective_date <= to_date('2000/09/30','yyyy/mm/dd') then
364 if p_First_NOAC_Lookup_Code ='873'
365 and
366 p_One_Time_Payment_Amount > (.05* p_TO_Basic_Pay)
367 then
368 hr_utility.set_message(8301, 'GHR_37115_ALL_PROCEDURE_FAIL');
369 hr_utility.raise_error;
370 end if;
371 end if;*/
372
373 -- 050.30.2
374
375 -- UPD 41(Bug 4567571) Raju 08-Nov-2005 Terminate from 01-May-2002
376 if p_effective_date < to_date('2002/05/01','yyyy/mm/dd') then
380 and
377 if p_First_NOAC_Lookup_Code ='816'
378 and
379 (p_First_Action_NOA_LA_Code1 <>'ZTY' or p_First_Action_NOA_LA_Code2 <> 'ZTY' )
381 p_One_Time_Payment_Amount > (.25* p_TO_Basic_Pay)
382 then
383 hr_utility.set_message(8301, 'GHR_37116_ALL_PROCEDURE_FAIL');
384 hr_utility.raise_error;
385 end if;
386 End if;
387
388 -- 050.35.2
389
390 -- UPD 41(Bug 4567571) Raju 08-Nov-2005 Terminate from 01-May-2002
391 if p_effective_date < to_date('2002/05/01','yyyy/mm/dd') then
392 if p_First_NOAC_Lookup_Code ='816'
393 and
394 (p_First_Action_NOA_LA_Code1 ='ZTY' or p_First_Action_NOA_LA_Code2 = 'ZTY' )
395 and
396 p_One_Time_Payment_Amount > (.25* p_TO_Basic_Pay)
397 and
398 p_One_Time_Payment_Amount > 15000
399 then
400 hr_utility.set_message(8301, 'GHR_37117_ALL_PROCEDURE_FAIL');
401 hr_utility.raise_error;
402 end if;
403 End if;
404 /*
405 -- 050.40.2
406 if p_First_NOAC_Lookup_Code ='825'
407 and
408 p_One_Time_Payment_Amount > 25000
409 then
410 hr_utility.set_message(8301, 'GHR_37118_ALL_PROCEDURE_FAIL');
411 hr_utility.raise_error;
412 end if;*/
413
414 end chk_Award_Amount;
415
416 /* Name:
417 -- C_Benefit_Amount
418 */
419
420 procedure chk_Benefit_Amount
421 (p_First_NOAC_Lookup_Code in varchar2
422 ,p_Benefit_Amount in varchar2 -- non SF52
423 ,p_effective_date in date
424 ) is
425 begin
426
427 --Commented as per EOY 2003 cpdf changes by Ashley
428 -- 070.02.2
429 -- Award Req 8/15/00 vravikan 30-sep-2000 End date
430 /* if p_effective_date <= to_date('2000/09/30','yyyy/mm/dd') then
431 if p_Benefit_Amount is not null
432 and
433 p_First_NOAC_Lookup_Code not in ('872','874','875','876','877')
434 then
435 hr_utility.set_message(8301, 'GHR_37165_ALL_PROCEDURE_FAIL');
436 hr_utility.raise_error;
437
438 end if;
439 end if;*/
440 null;
441
442 end chk_Benefit_Amount;
443
444
445 /* Name:
446 chk_Cur_Appt_Auth
447 */
448
449 procedure chk_Cur_Appt_Auth
450 (p_First_Action_NOA_LA_Code1 in varchar2
451 ,p_First_Action_NOA_LA_Code2 in varchar2
452 ,p_Cur_Appt_Auth_1 in varchar2 -- non SF52 item
453 ,p_Cur_Appt_Auth_2 in varchar2 -- non SF52 item
454 ,p_Agency_Subelement in varchar2 -- non SF52 item
455 ,p_To_OCC_Code in varchar2
456 ,p_First_NOAC_Lookup_Code in varchar2
457 ,p_Position_Occupied_Code in varchar2
458 ,p_To_Pay_Plan in varchar2
459 ,p_Handicap in varchar2 -- non SF52 item
460 ,p_Tenure_Goupe_Code in varchar2
461 ,p_To_Grade_Or_Level in varchar2
462 ,p_Vet_Pref_Code in varchar2
463 ,p_Duty_Station_Lookup_Code in varchar2
464 ,p_Service_Computation_Date in date
465 ,p_effective_date in date
466 ) is
467 l_Service_Computation_Date Date;
468 begin
469
470 -- 100.02.3
471 -- renamed the edit from 100.01.3 for the april release
472 if
473 (
474 p_Cur_Appt_Auth_1= 'ZVB' or p_Cur_Appt_Auth_2='ZVB'
475 )
476 and
477 p_Agency_Subelement <> 'TD03'
478 then
479 hr_utility.set_message(8301, 'GHR_37119_ALL_PROCEDURE_FAIL');
480 hr_utility.raise_error;
481 end if;
482
483 /* Commented as per December 2000 cpdf changes -- vravikan
484 --
485 -- 100.03.3 If either current appointment authority is ZTA,
486 -- And position occupied is 1,
487 -- Then agency/subelement must be DJ03.
488 if p_effective_date >= fnd_date.canonical_to_date('1998/09/01') then
489 if ( p_Cur_Appt_Auth_1= 'ZTA' or p_Cur_Appt_Auth_2='ZTA' ) and
490 p_Position_Occupied_Code = '1' and
491 p_Agency_Subelement <> 'DJ03' then
492 hr_utility.set_message(8301, 'GHR_37892_ALL_PROCEDURE_FAIL');
493 hr_utility.raise_error;
494 end if;
495 end if;
496
497 */
498 -- 100.04.1
499 -- Dec. 2001 Patch --- Delete YAM
500 if (
501 p_Cur_Appt_Auth_1 in ('YBM','YGM','Y1M','Y2M','Y3M')
502 or
503 p_Cur_Appt_Auth_2 in ('YBM','YGM','Y1M','Y2M','Y3M')
504 )
505 and
506 p_to_pay_plan = 'GS'
507 and
508 substr(p_to_occ_code,3,2) <>'99'
509 and
510 p_to_occ_code is not null
511 then
512 hr_utility.set_message(8301, 'GHR_37120_ALL_PROCEDURE_FAIL');
513 hr_utility.raise_error;
514 end if;
515
516 /* same as 100.04.1
517 -- 100.05.2
518 if
519 (
520 p_Cur_Appt_Auth_1 in ('YAM','YBM','YGM','Y1M','Y2M','Y3M')
521 or
522 p_Cur_Appt_Auth_2 in ('YAM','YBM','YGM','Y1M','Y2M','Y3M')
523 )
524 and
525 p_To_Pay_Plan='GS'
529 p_To_OCC_Code is not null
526 and
527 substr(p_To_OCC_Code,3,2) <> '99'
528 and
530 then
531 hr_utility.set_message(8301, 'GHR_37120_ALL_PROCEDURE_FAIL');
532 hr_utility.raise_error;
533 end if;
534 */
535 --100.06.3
536 -- Updation Date Updated By Remarks
537 -- ============================================
538 -- 19-MAR-2003 vnarasim Added agency/subelement HSBC.
539 -- 30-OCT-2003 Ashley Deleted agency/subelement TD19
540 --
541 if p_effective_date >= fnd_date.canonical_to_date('20'||'00/10/01') then
542 if (p_Cur_Appt_Auth_1 = 'ZVC' OR p_Cur_Appt_Auth_2 = 'ZVC') AND
543 (p_agency_subelement NOT IN ('HSBC'))
544 then
545 hr_utility.set_message(8301, 'GHR_37925_ALL_PROCEDURE_FAIL');
546 hr_utility.raise_error;
547 end if;
548 end if;
549 -- 100.07.3
550 if
551 (p_Cur_Appt_Auth_1 in ('Y1K','Y2K','Y3K','Y4K','Y5K')
552 or
553 p_Cur_Appt_Auth_2 in ('Y1K','Y2K','Y3K','Y4K','Y5K')
554 )
555 and
556 p_Tenure_Goupe_Code <> '0'
557 and
558 p_Tenure_Goupe_Code <> '3'
559 and
560 p_Tenure_Goupe_Code is not null
561 then
562 hr_utility.set_message(8301, 'GHR_37121_ALL_PROCEDURE_FAIL');
563 hr_utility.raise_error;
564 end if;
565 -- Update Date By Effective Date Comment
566 -- 11 01/03/00 vravikan 11/01/99 Add Edit
567
568 -- 100.09.3 If either current appointment authority is UDM
569 -- Then agency must be TR.
570 if p_effective_date >= fnd_date.canonical_to_date('19'||'99/11/01') then
571 if ( p_Cur_Appt_Auth_1= 'UDM' or p_Cur_Appt_Auth_2='UDM' ) and
572 substr(p_Agency_Subelement,1,2) <> 'TR' then
573 hr_utility.set_message(8301, 'GHR_37411_ALL_PROCEDURE_FAIL');
574 hr_utility.raise_error;
575 end if;
576 end if;
577
578
579 -- 100.10.3 --100.10.1 Renumbered this edit to 100.10.3
580 -- Dec 01 Patch 12/10/01 vravikan Delete KDM, KFM, KHM
581 --upd49 08-Jan-07 Raju From 01-Sep-2006 Delete JYM
582 if p_effective_date < fnd_date.canonical_to_date('2006/09/01') then
583 if (p_Cur_Appt_Auth_1 in ('BPM','H2L','J8M',
584 'JYM','UFM','V8K','VEM','VPE','ZVB','ZVC')
585 or
586 substr(p_Cur_Appt_Auth_1,1,1) in ('W','X','Y')
587 or
588 p_Cur_Appt_Auth_2 in ('BPM','H2L','J8M',
589 'JYM','UFM','V8K','VEM','VPE','ZVB','ZVC')
590 or
591 substr(p_Cur_Appt_Auth_2,1,1) in ('W','X','Y')
592 )
593 and
594 p_position_occupied_code <>'2'
595 and
596 p_position_occupied_code is not null
597 then
598 hr_utility.set_message(8301, 'GHR_37122_ALL_PROCEDURE_FAIL');
599 hr_utility.set_message_token('APP_AUTH',
600 'BPM, H2L, J8M, JYM, UFM, V8K, VEM, VPE, W--, X--, Y--, ZVB or ZVC');
601 hr_utility.raise_error;
602 end if;
603 else
604 if (p_Cur_Appt_Auth_1 in ('BPM','H2L','J8M',
605 'UFM','V8K','VEM','VPE','ZVB','ZVC') or
606 substr(p_Cur_Appt_Auth_1,1,1) in ('W','X','Y') or
607 p_Cur_Appt_Auth_2 in ('BPM','H2L','J8M',
608 'UFM','V8K','VEM','VPE','ZVB','ZVC') or
609 substr(p_Cur_Appt_Auth_2,1,1) in ('W','X','Y')
610 ) and
611 p_position_occupied_code <>'2' and
612 p_position_occupied_code is not null
613 then
614 hr_utility.set_message(8301, 'GHR_37122_ALL_PROCEDURE_FAIL');
615 hr_utility.set_message_token('APP_AUTH',
616 'BPM, H2L, J8M, UFM, V8K, VEM, VPE, W--, X--, Y--, ZVB or ZVC');
617 hr_utility.raise_error;
618 end if;
619 end if;
620
621 -- 100.11.2
622 -- Dec 01 Patch 12/10/01 vravikan Delete KDM, KFM, KHM, and TXX
623 --upd47 26-Jun-06 Raju From 01-Apr-2003 Terminate the edit
624 if p_effective_date < fnd_date.canonical_to_date('2003/04/01') then
625 if ( p_Cur_Appt_Auth_1 in ('BPM','H2L','J8M','JYM',
626 'UFM','V8K','VEM','VPE','ZVB','ZVC') or
627 substr(p_Cur_Appt_Auth_1,1,1) in ('W','X','Y') or
628 p_Cur_Appt_Auth_2 in ('BPM','H2L','J8M','JYM',
629 'UFM','V8K','VEM','VPE','ZVB','ZVC') or
630 substr(p_Cur_Appt_Auth_2,1,1) in ('W','X','Y')) and
631 (to_number(p_First_NOAC_Lookup_Code) not between 100 and 199 and
632 to_number(p_First_NOAC_Lookup_Code) not between 500 and 599
633 ) and
634 p_Position_Occupied_Code <> '2' and
635 p_Position_Occupied_Code is not null
636 then
637 hr_utility.set_message(8301, 'GHR_37123_ALL_PROCEDURE_FAIL');
638 hr_utility.raise_error;
639 end if;
640 end if;
641
642 --100.12.1
643 -- Update Date By Effective Date Comment
644 -- 8 04/01/99 vravikan 10/01/98 Add Edit
645 -- 48 26/09/06 amrchakr 01-jul-2006 Changed edit number from 100.12.1 to 100.12.3
646 if p_effective_date >= fnd_date.canonical_to_date('1998/10/01') then
647 if
648 (
649 p_Cur_Appt_Auth_1 in ('ZRL')
650 or
651 p_Cur_Appt_Auth_2 in ('ZRL') )
652 and
653 p_agency_subelement <> 'DJ02'
654 and
655 p_agency_subelement is not null
656 then
660 else
657 if p_effective_date < fnd_date.canonical_to_date('2006/07/01') then
658 hr_utility.set_message(8301, 'GHR_37055_ALL_PROCEDURE_FAIL');
659 hr_utility.raise_error;
661 hr_utility.set_message(8301, 'GHR_37691_ALL_PROCEDURE_FAIL');
662 hr_utility.raise_error;
663 end if;
664 end if;
665 end if;
666
667 -- 100.12.2
668 --
669 -- 100.12.3 is renumbered as 100.12.2 from 01-mar-1998
670 -- Hence effective date was introduced.
671 --
672 -- Update Date By Effective Date Comment
673 -- 8 04/19/99 vravikan 09/01/1998 Date Correction
674 -- from 1-mar-98 to 1-sep-98
675 -- Adding LAC P2M
676 if p_effective_date < fnd_date.canonical_to_date('1998/09/01') then
677 if p_To_Pay_Plan = 'ES' and
678 p_Cur_Appt_Auth_1 not in ('NRM','NSM','NTM','NVM','NWM','NXM','P2M','P3M','P5M','P7M',
679 'V2M','VAG','VBJ','VCJ','V4L','V4M','V6M','V4P') and
680 p_Cur_Appt_Auth_1 is not null then
681 hr_utility.set_message(8301, 'GHR_37124_ALL_PROCEDURE_FAIL');
682 hr_utility.raise_error;
683 end if;
684 end if;
685
686 -- 100.13.3 -- 100.13.1 renumbered as 100.13.3
687 --
688 -- This is renumbered from 100.12.3 B-9 from 01-mar-1998
689 --
690 -- 8 04/19/99 vravikan 09/01/1998 Date Correction
691 -- from 1-mar-98 to 1-sep-98
692 -- Adding LAC P2M
693 if p_effective_date >= fnd_date.canonical_to_date('1998/09/01') then
694 if p_To_Pay_Plan = 'ES' and
695 p_Cur_Appt_Auth_1 not in ('NRM','NSM','NTM','NVM','NWM','NXM','P2M','P3M','P5M','P7M',
696 'V2M','VAG','VBJ','VCJ','V4L','V4M','V6M','V4P') and
697 p_Cur_Appt_Auth_1 is not null then
698 hr_utility.set_message(8301, 'GHR_37875_ALL_PROCEDURE_FAIL');
699 hr_utility.raise_error;
700 end if;
701 end if;
702
703 -- 100.14.3
704 -- Dec 2001 Patch 1-Nov-01 Add AUM
705 if p_effective_date >= to_date('2001/11/01','yyyy/mm/dd') THEN
706 if p_To_Pay_Plan = 'ES'
707 and
708 p_Cur_Appt_Auth_2 not in ('AUM','AWM','BWM','HAM','ZLM')
709 and
710 p_Cur_Appt_Auth_2 is not null
711 then
712 hr_utility.set_message(8301, 'GHR_37125_ALL_PROCEDURE_FAIL');
713 hr_utility.raise_error;
714 end if;
715 else
716 if p_To_Pay_Plan = 'ES'
717 and
718 p_Cur_Appt_Auth_2 not in ('AUM','AWM','BWM','HAM','ZLM')
719 and
720 p_Cur_Appt_Auth_2 is not null
721 then
722 hr_utility.set_message(8301, 'GHR_37125_ALL_PROCEDURE_FAIL');
723 hr_utility.raise_error;
724 end if;
725 end if;
726 -- 100.16.3
727 -- Dec 01 Patch vravikan Delete YKM
728 --upd49 08-Jan-07 Raju Bug#5619873 From 01-Sep-2006 Delete WTM
729 if p_effective_date < fnd_date.canonical_to_date('2006/09/01') then
730 if (p_Cur_Appt_Auth_1 in ('WTM','WUM') or
731 p_Cur_Appt_Auth_2 in ('WTM','WUM')) and
732 p_Handicap <>'04' and
733 to_number(p_Handicap) not between 6 and 94 and
734 p_Handicap is not null then
735 hr_utility.set_message(8301, 'GHR_37126_ALL_PROCEDURE_FAIL');
736 hr_utility.set_message_token('APP_AUTH','WTM, WUM');
737 hr_utility.raise_error;
738 end if;
739 ELSE
740 if (p_Cur_Appt_Auth_1 in ('WUM') or
741 p_Cur_Appt_Auth_2 in ('WUM')) and
742 p_Handicap <>'04' and
743 to_number(p_Handicap) not between 6 and 94 and
744 p_Handicap is not null then
745 hr_utility.set_message(8301, 'GHR_37126_ALL_PROCEDURE_FAIL');
746 hr_utility.set_message_token('APP_AUTH','WUM');
747 hr_utility.raise_error;
748 end if;
749 END IF;
750
751 --
752 -- 100.17.3 If either current appointment authority is Z2W,
753 -- Then agency must be AF, AR, DD, or NV.
754
755 if p_effective_date >= fnd_date.canonical_to_date('1998/09/01') then
756 if ( p_Cur_Appt_Auth_1 = 'Z2W' or p_Cur_Appt_Auth_2 = 'Z2W') and
757 substr(p_Agency_Subelement,1,2) not in ('AF','AR','DD','NV')
758 then
759 hr_utility.set_message(8301, 'GHR_37893_ALL_PROCEDURE_FAIL');
760 hr_utility.raise_error;
761 end if;
762 end if;
763
764 -- 100.19.1
765 -- deleted 'M2M','M4M' and added 'BNK' as per the changes for apr-98 release
766 -- added 'Z2W' on 9-oct-98 for update 8
767 -- 11/11 12/20/99 vravikan 01-Dec-1999 Add ZBA
768 -- Sep'00 Patch vravikan From Begining Add ZBA
769 -- Dec 01 Patch 12/10/01 vravikan Delete CTM,MYM,MZM,M1M, and NEL
770 -- 14-SEP-2004 MADHURI DELETED LEGAL AUTH - MAM, MBM.
771 -- 23-Jun-06 Raju From Beginning Added ZTU, Z5B, Z5C, Z5D, Z5E, and Z5H
772 -- 26-sep-2006 amrchakr 01-jul-2003 Changed edit number from 100.19.1 to 100.19.3
776
773 -- 26-sep-2006 amrchakr 01-jul-2003 Remove Authorities 'JEM','JGM','JJM','JMM','JQM','JVM','J4M' from 100.19.3
774 --------------------------------------------------------------------------------------------
775 if p_effective_date < fnd_date.canonical_to_date('2003/07/01') then
777 if (p_Cur_Appt_Auth_1 in
778 ('ABS','ACM','AYM','A2M','A7M','BBM','BEA','BGL','BKM','BLM','BMA','BMC',
779 'BNE','BNK','BNW','BRM','BSE','BSS','BSW','BTM','BWA','BWE','HDM',
780 'HGM','HJM','HLM','HNM','HRM','H3M','JEM','JGM','JJM','JMM','JQM',
781 'JVM','J4M','KLM','KQM','KTM','KVM','KXM','K1M','K4M','K7M','K9M',
782 'LBM','LEM','LHM','LJM','LKM','LKP','LLM','LPM','LSM','LWM','LYM',
783 'LZM','L1K','L1M','L3M','MCM','MEM','MGM','MJM','MLL',
784 'MLM','MMM','MXM','M6M','M8M','NAM',
785 'NCM','NEM','NFM','NJM','NMM','NUM','Q3M','VHM','VJM','V8L',
786 'V8N','Z2W','ZBA','ZGM','ZGY','ZJK','ZJM','ZMM','ZQM','ZTM','ZTU','Z5B', 'Z5C', 'Z5D', 'Z5E','Z5H'
787 )
788 or
789 p_Cur_Appt_Auth_2 in
790 ('ABS','ACM','AYM','A2M','A7M','BBM','BEA','BGL','BKM','BLM','BMA','BMC',
791 'BNE','BNK','BNW','BRM','BSE','BSS','BSW','BTM','BWA','BWE','HDM',
792 'HGM','HJM','HLM','HNM','HRM','H3M','JEM','JGM','JJM','JMM','JQM',
793 'JVM','J4M','KLM','KQM','KTM','KVM','KXM','K1M','K4M','K7M','K9M',
794 'LBM','LEM','LHM','LJM','LKM','LKP','LLM','LPM','LSM','LWM','LYM',
795 'LZM','L1K','L1M','L3M','MCM','MEM','MGM','MJM','MLL',
796 'MLM','MMM','MXM','M6M','M8M','NAM',
797 'NCM','NEM','NFM','NJM','NMM','NUM','Q3M','VHM','VJM','V8L',
798 'V8N','ZBA','Z2W','ZGM','ZGY','ZJK','ZJM','ZMM','ZQM','ZTM','ZTU','Z5B', 'Z5C', 'Z5D', 'Z5E','Z5H'
799 )
800 )
801 AND
802 p_position_occupied_code <> '1'
803 AND
804 p_position_occupied_code is not null
805 then
806
807 hr_utility.set_message(8301, 'GHR_37127_ALL_PROCEDURE_FAIL');
808 hr_utility.raise_error;
809
810 end if;
811 else
812 if (p_Cur_Appt_Auth_1 in
813 ('ABS','ACM','AYM','A2M','A7M','BBM','BEA','BGL','BKM','BLM','BMA','BMC',
814 'BNE','BNK','BNW','BRM','BSE','BSS','BSW','BTM','BWA','BWE','HDM',
815 'HGM','HJM','HLM','HNM','HRM','H3M',
816 'KLM','KQM','KTM','KVM','KXM','K1M','K4M','K7M','K9M',
817 'LBM','LEM','LHM','LJM','LKM','LKP','LLM','LPM','LSM','LWM','LYM',
818 'LZM','L1K','L1M','L3M','MCM','MEM','MGM','MJM','MLL',
819 'MLM','MMM','MXM','M6M','M8M','NAM',
820 'NCM','NEM','NFM','NJM','NMM','NUM','Q3M','VHM','VJM','V8L',
821 'V8N','Z2W','ZBA','ZGM','ZGY','ZJK','ZJM','ZMM','ZQM','ZTM','ZTU','Z5B', 'Z5C', 'Z5D', 'Z5E','Z5H'
822 )
823 or
824 p_Cur_Appt_Auth_2 in
825 ('ABS','ACM','AYM','A2M','A7M','BBM','BEA','BGL','BKM','BLM','BMA','BMC',
826 'BNE','BNK','BNW','BRM','BSE','BSS','BSW','BTM','BWA','BWE','HDM',
827 'HGM','HJM','HLM','HNM','HRM','H3M',
828 'KLM','KQM','KTM','KVM','KXM','K1M','K4M','K7M','K9M',
829 'LBM','LEM','LHM','LJM','LKM','LKP','LLM','LPM','LSM','LWM','LYM',
830 'LZM','L1K','L1M','L3M','MCM','MEM','MGM','MJM','MLL',
831 'MLM','MMM','MXM','M6M','M8M','NAM',
832 'NCM','NEM','NFM','NJM','NMM','NUM','Q3M','VHM','VJM','V8L',
833 'V8N','ZBA','Z2W','ZGM','ZGY','ZJK','ZJM','ZMM','ZQM','ZTM','ZTU','Z5B', 'Z5C', 'Z5D', 'Z5E','Z5H'
834 )
835 )
836 AND
837 p_position_occupied_code <> '1'
838 AND
839 p_position_occupied_code is not null
840 then
841
842 hr_utility.set_message(8301, 'GHR_37692_ALL_PROCEDURE_FAIL');
843 hr_utility.raise_error;
844
845 end if;
846 end if;
847
848 -- DATE UPDATE_BY COMMENTS
849 -------------------------------------------------------------------------------------------
850 -- 14-SEP-2004 MADHURI DELETED LEGAL AUTH - MAM, MBM.
851 --------------------------------------------------------------------------------------------
852 -- 100.20.2
853 -- deleted 'M2M','M4M' and added 'BNK' as per the changes for apr-98 release
854 -- added 'Z2W' on 9-oct-98 for update 8
855 -- 11/11 12/20/99 vravikan 01-Dec-1999 Add ZBA
856 -- Sep'00 Patch vravikan From Begining Add ZBA
857 -- Dec 01 Patch 12/10/01 vravikan Delete CTM,MYM,MZM,M1M, and NEL
858 -- 23-Jun-06 Raju From Beginning Added Z5B, Z5C, Z5D, Z5E, and Z5H
859 -- 29-sep-2006 amrchakr 01-jul-2003 end dated the edit from 01-jul-2003
860
861 IF p_effective_date < fnd_date.canonical_to_date('2003/07/01') THEN
862 if (
863 p_Cur_Appt_Auth_1 in
864 ('ABS','ACM','AYM','A2M','A7M','BBM','BEA','BGL','BKM','BLM','BMA','BMC',
865 'BNE','BNK','BNW','BRM','BSE','BSS','BSW','BTM','BWA','BWE','HDM',
866 'HGM','HJM','HLM','HNM', 'HRM','H3M','JEM','JGM','JJM','JMM','JQM',
867 'JVM','J4M','KLM','KQM','KTM','KVM','KXM','K1M', 'K4M','K7M','K9M',
868 'LBM','LEM','LHM','LJM','LKM','LKP','LLM','LPM','LSM','LWM','LYM','LZM',
869 'L1K','L1M','L3M','MCM','MEM','MGM','MJM','MLL',
870 'MLM','MMM','MXM','M6M','M8M','NAM','NCM','MEL',
871 'NEM','NFM','NJM','NMM','NUM','Q4M','VHM', 'VJM','V8L','V8N','Z2W',
872 'ZBA','ZGM','ZGY','ZJK','ZJM','ZMM','ZQM','ZTM','ZTU','Z5B', 'Z5C', 'Z5D', 'Z5E','Z5H')
873 or
874 p_Cur_Appt_Auth_2 in
875 ('ABS','ACM','AYM','A2M','A7M','BBM','BEA','BGL','BKM','BLM','BMA','BMC',
879 'LBM','LEM','LHM','LJM','LKM','LKP','LLM','LPM','LSM','LWM','LYM','LZM',
876 'BNE','BNK','BNW','BRM','BSE','BSS','BSW','BTM','BWA','BWE','HDM',
877 'HGM','HJM','HLM','HNM', 'HRM','H3M','JEM','JGM','JJM','JMM','JQM',
878 'JVM','J4M','KLM','KQM','KTM','KVM','KXM','K1M', 'K4M','K7M','K9M',
880 'L1K','L1M','L3M','MCM','MEM','MGM','MJM','MLL',
881 'MLM','MMM','MXM', 'M6M','M8M','NAM','NCM','MEL',
882 'NEM','NFM','NJM','NMM','NUM','Q4M','VHM', 'VJM','V8L','V8N','Z2W',
883 'ZBA','ZGM','ZGY','ZJK','ZJM','ZMM','ZQM','ZTM','ZTU','Z5B', 'Z5C', 'Z5D', 'Z5E','Z5H')
884 )
885 and
886 (
887 to_number(p_First_NOAC_Lookup_Code) not between 100 and 199
888 and
889 to_number(p_First_NOAC_Lookup_Code) not between 500 and 599
890 )
891 and
892 p_Position_Occupied_Code <> '1'
893 and
894 p_Position_Occupied_Code is not null
895 then
896 hr_utility.set_message(8301, 'GHR_37128_ALL_PROCEDURE_FAIL');
897 hr_utility.raise_error;
898 end if;
899 END IF;
900 --
901
902 -- 100.22.3
903 if ((
904 (
905 substr(p_Cur_Appt_Auth_1,1,1)= 'M'
906 or
907 substr(p_Cur_Appt_Auth_1,1,1)= 'N'
908 )
909 and
910 p_Cur_Appt_Auth_1 <>'NUM'
911 )
912 or
913 (
914 (
915 substr(p_Cur_Appt_Auth_2,1,1)= 'M'
916 or
917 substr(p_Cur_Appt_Auth_2,1,1)= 'N'
918 )
919 and
920 p_Cur_Appt_Auth_2 <>'NUM'
921 ))
922 and
923 p_Tenure_Goupe_Code <> '0'
924 and
925 p_Tenure_Goupe_Code <> '3'
926 and
927 p_Tenure_Goupe_Code is not null
928 then
929 hr_utility.set_message(8301, 'GHR_37129_ALL_PROCEDURE_FAIL');
930 hr_utility.raise_error;
931 end if;
932
933 -- 100.25.3
934 if ((
935 substr(p_Cur_Appt_Auth_1,1,1)='K'
936 and p_Cur_Appt_Auth_1<>'KLM'
937 )
938 or
939 (
940 substr(p_Cur_Appt_Auth_2,1,1)='K'
941 and p_Cur_Appt_Auth_2<>'KLM'
942 ))
943 and
944 p_Tenure_Goupe_Code <>'2'
945 and
946 p_Tenure_Goupe_Code <>'1'
947 and
948 p_Tenure_Goupe_Code is not null
949 then
950 hr_utility.set_message(8301, 'GHR_37130_ALL_PROCEDURE_FAIL');
951 hr_utility.raise_error;
952 end if;
953
954 -- 100.28.3
955 -- Dec 01 Patch 12/10/01 vravikan Add YCM
956 -- Delete YAM, Y4M
957 -- Upd 47 23-Jun-06 Raju From 01-Apr-06 Added condition pay plan is other than Yx
958 -- Bug 5735389 26-dec-2006 amrchakr Removed the condition 'p_Tenure_Goupe_Code <>'1'' from ELSE part.
959
960 IF p_effective_date < fnd_date.canonical_to_date('2006/04/01') THEN
961 if ( p_Cur_Appt_Auth_1 in ('YBM','YCM','YGM','Y1M','Y2M','Y3M') or
962 p_Cur_Appt_Auth_2 in ('YBM','YCM','YGM','Y1M','Y2M','Y3M')) and
963 p_Tenure_Goupe_Code <>'1' and
964 p_Tenure_Goupe_Code <>'2' and
965 p_Tenure_Goupe_Code is not null
966 then
967 hr_utility.set_message(8301, 'GHR_37131_ALL_PROCEDURE_FAIL');
968 hr_utility.set_message_token('APP_ATRTY','YBM, YCM, YGM, Y1M, Y2M, or Y3M');
969 hr_utility.raise_error;
970 end if;
971 ELSE
972 if ( p_Cur_Appt_Auth_1 in ('YBM','YCM','YGM','Y1M','Y2M','Y3M') or
973 p_Cur_Appt_Auth_2 in ('YBM','YCM','YGM','Y1M','Y2M','Y3M')) and
974 substr(p_To_Pay_Plan,1,1)<>'Y' and
975 p_Tenure_Goupe_Code <>'2' and
976 p_Tenure_Goupe_Code is not null
977 then
978 hr_utility.set_message(8301, 'GHR_37131_ALL_PROCEDURE_FAIL');
979 hr_utility.set_message_token('APP_ATRTY','YBM, YCM, YGM, Y1M, Y2M, or Y3M, And pay plan is other than Yx');
980 hr_utility.raise_error;
981 end if;
982 END IF;
983
984 -- 100.31.3
985 if (
986 p_Cur_Appt_Auth_1 ='WXM'
987 or p_Cur_Appt_Auth_2 ='WXM'
988 )
989 and
990 p_Tenure_Goupe_Code <>'0'
991 and
992 p_Tenure_Goupe_Code <>'3'
993 and
994 p_Tenure_Goupe_Code is not null
995 then
996 hr_utility.set_message(8301, 'GHR_37132_ALL_PROCEDURE_FAIL');
997 hr_utility.raise_error;
998 end if;
999
1000 -- 100.35.3
1001 if (
1002 p_Cur_Appt_Auth_1 ='V8V' or p_Cur_Appt_Auth_2 ='V8V'
1003 )
1004 and
1005 (
1006 substr(p_Agency_Subelement,1,2) <>'VA'
1007 or p_Position_Occupied_Code <>'2'
1008 )
1009 then
1010 hr_utility.set_message(8301, 'GHR_37133_ALL_PROCEDURE_FAIL');
1011 hr_utility.raise_error;
1012 end if;
1013
1014 -- 100.36.3
1015 if p_To_Pay_Plan='GS'
1016 and
1017 p_To_Grade_Or_Level in ('01','02','03','04','05','06','07','08','09','10')
1018 and
1019 (
1020 p_Cur_Appt_Auth_1 = 'ZKM'
1021 or
1022 p_Cur_Appt_Auth_1 = 'ZNM'
1023 or
1024 p_Cur_Appt_Auth_2 = 'ZKM'
1025 or
1026 p_Cur_Appt_Auth_2 = 'ZNM'
1030 hr_utility.raise_error;
1027 )
1028 then
1029 hr_utility.set_message(8301, 'GHR_37134_ALL_PROCEDURE_FAIL');
1031 end if;
1032
1033 -- 100.37.3
1034 if (
1035 p_Cur_Appt_Auth_1 in ('NEM','LBM','LZM')
1036 or p_Cur_Appt_Auth_2 in ('NEM','LBM','LZM')
1037 )
1038 and
1039 (
1040 p_Vet_Pref_Code = '1'
1041 or p_Vet_Pref_Code = '5'
1042 )
1043 then
1044 hr_utility.set_message(8301, 'GHR_37135_ALL_PROCEDURE_FAIL');
1045 hr_utility.raise_error;
1046 end if;
1047
1048 -- 100.38.3
1049 --
1050 -- 12/8/00 vravikan From the Start Add UAM
1051 -- If either current appointment authority is Z2U, then agency must be
1052 -- AF, AR, DD or NV.
1053 --
1054 if p_effective_date >= fnd_date.canonical_to_date('1998/03/01') then
1055 if (p_Cur_Appt_Auth_1 in ( 'Z2U','UAM') or
1056 p_Cur_Appt_Auth_2 in ('Z2U','UAM') ) and
1057 substr(p_Agency_Subelement,1,2) not in ('AF','AR','DD','NV') then
1058 hr_utility.set_message(8301, 'GHR_37876_ALL_PROCEDURE_FAIL');
1059 hr_utility.raise_error;
1060 end if;
1061 end if;
1062
1063 /* Commented -- Dec 2001 Patch
1064 -- 100.52.3
1065 if (
1066 substr(p_Cur_Appt_Auth_1,1,1)='T'
1067 or
1068 substr(p_Cur_Appt_Auth_2,1,1)='T'
1069 )
1070 and
1071 substr(p_Duty_Station_Lookup_Code,1,2) <> 'PM'
1072 and
1073 p_Duty_Station_Lookup_Code is not null
1074 then
1075 hr_utility.set_message(8301, 'GHR_37136_ALL_PROCEDURE_FAIL');
1076 hr_utility.raise_error;
1077 end if;
1078 */
1079
1080 -- 100.55.1
1081 if ( p_Cur_Appt_Auth_1 in
1082 ('NRM','NSM','NTM','NVM','NWM','NXM','V2M','VCJ','VBJ','VAG','V6M','V4L','V4M','V4P')
1083 or
1084 p_Cur_Appt_Auth_2 in
1085 ('NRM','NSM','NTM','NVM','NWM','NXM','V2M','VCJ','VBJ','VAG','V6M','V4L','V4M','V4P')
1086 )
1087 and
1088 p_to_pay_plan <> 'ES'
1089 and
1090 p_to_pay_plan is not null
1091 then
1092 hr_utility.set_message(8301, 'GHR_37137_ALL_PROCEDURE_FAIL');
1093 hr_utility.raise_error;
1094 end if;
1095
1096 -- 100.56.2
1097 if (
1098 p_Cur_Appt_Auth_1 in ('NRM','NSM','NTM','NVM','NWM','NXM',
1099 'VAG','VBJ','VCJ','V4L','V4M','V6M','V4P')
1100 or
1101 p_Cur_Appt_Auth_2 in ('NRM','NSM','NTM','NVM','NWM','NXM','VAG',
1102 'VBJ','VCJ','V4L','V4M','V6M','V4P')
1103 )
1104 and
1105 p_To_Pay_Plan <>'ES'
1106 and
1107 p_To_Pay_Plan is not null
1108 then
1109 hr_utility.set_message(8301, 'GHR_37138_ALL_PROCEDURE_FAIL');
1110 hr_utility.raise_error;
1111 end if;
1112
1113 -- 100.58.3
1114 -- Raju 20-Apr-2007 UPD 53(Bug 5996938) Delete cur_appt_auth2 From Starting
1115 -- l_Service_Computation_Date := TO_DATE(p_Service_Computation_Date, 'DD-MON-YYYY');
1116 l_Service_Computation_Date := p_Service_Computation_Date;
1117 if (p_Cur_Appt_Auth_1='ZZZ'
1118 --or p_Cur_Appt_Auth_2='ZZZ'
1119 )
1120 and (( l_Service_Computation_Date >= TO_DATE('1982/01/01','YYYY/MM/DD')
1121 and l_Service_Computation_Date is not null
1122 )or(p_Position_Occupied_Code <> '1' and
1123 p_Position_Occupied_Code is not null )
1124 )
1125 then
1126 hr_utility.set_message(8301, 'GHR_37139_ALL_PROCEDURE_FAIL');
1127 hr_utility.raise_error;
1128 end if;
1129
1130 /*
1131 -- 100.61.2
1132 if (
1133 (
1134 substr(p_First_NOAC_Lookup_Code,1,1)='1' and p_First_NOAC_Lookup_Code <>'132'
1135 )
1136 or
1137 substr(p_First_NOAC_Lookup_Code,1,1)='5'
1138 )
1139 and
1140 (
1141 p_Cur_Appt_Auth_1 <>p_First_Action_NOA_LA_Code1
1142 or
1143 p_Cur_Appt_Auth_2 <>p_First_Action_NOA_LA_Code2
1144 )
1145 then
1146 hr_utility.set_message(8301, 'GHR_37140_ALL_PROCEDURE_FAIL');
1147 hr_utility.raise_error;
1148 end if;
1149 */
1150
1151 -- 100.64.3
1152 if (
1153 p_Cur_Appt_Auth_1='WEM' or p_Cur_Appt_Auth_2='WEM'
1154 )
1155 and
1156 p_To_OCC_Code <> '0904'
1157 and
1158 p_To_OCC_Code is not null
1159 then
1160 hr_utility.set_message(8301, 'GHR_37141_ALL_PROCEDURE_FAIL');
1161 hr_utility.raise_error;
1162 end if;
1163
1164 -- 100.70.3
1165 if (
1166 p_Cur_Appt_Auth_1='WDM' or p_Cur_Appt_Auth_2='WDM'
1167 )
1168 and
1169 not(
1170 (
1171 p_To_OCC_Code = '0905' or p_To_OCC_Code = '1222'
1172 )
1173 or
1174 p_To_OCC_Code is null
1175 )
1176 then
1177 hr_utility.set_message(8301, 'GHR_37142_ALL_PROCEDURE_FAIL');
1178 hr_utility.raise_error;
1179 end if;
1180
1181 -- 100.73.3
1182 if (
1183 p_Cur_Appt_Auth_1 in ('ACM','AYM','KQM')
1184 or
1185 p_Cur_Appt_Auth_2 in ('ACM','AYM','KQM')
1186 )
1187 and
1191
1188 p_Agency_Subelement<>'RR00'
1189 and
1190 (p_To_OCC_Code = '0904' or p_To_OCC_Code = '0905')
1192 then
1193 hr_utility.set_message(8301, 'GHR_37143_ALL_PROCEDURE_FAIL');
1194 hr_utility.raise_error;
1195 end if;
1196
1197 -- 100.76.3
1198 if (
1199 p_Cur_Appt_Auth_1='VEM' or p_Cur_Appt_Auth_2='VEM' or
1200 p_Cur_Appt_Auth_1='H2L' or p_Cur_Appt_Auth_2='H2L'
1201 )
1202 and
1203 p_To_Pay_Plan not in ('ED','EF','EH')
1204 and
1205 p_To_Pay_Plan is not null
1206 then
1207 hr_utility.set_message(8301, 'GHR_37144_ALL_PROCEDURE_FAIL');
1208 hr_utility.raise_error;
1209 end if;
1210
1211 -- 100.98.3
1212 if p_Cur_Appt_Auth_1='ZZZ'
1213 and
1214 p_Cur_Appt_Auth_2 <>'ZZZ'
1215 and
1216 p_Cur_Appt_Auth_2 is not null
1217 then
1218 hr_utility.set_message(8301, 'GHR_37145_ALL_PROCEDURE_FAIL');
1219 hr_utility.raise_error;
1220 end if;
1221
1222 -- 100.99.3
1223 if p_Cur_Appt_Auth_2='ZZZ'
1224 and
1225 p_Cur_Appt_Auth_1 <> 'ZZZ'
1226 and
1227 p_Cur_Appt_Auth_1 is not null
1228 then
1229 hr_utility.set_message(8301, 'GHR_37146_ALL_PROCEDURE_FAIL');
1230 hr_utility.raise_error;
1231 end if;
1232
1233
1234 end chk_Cur_Appt_Auth;
1235
1236 /* Name:
1237 -- C_Date_of_Birth
1238 */
1239
1240 procedure chk_Date_of_Birth
1241 ( p_First_NOAC_Lookup_Code in varchar2
1242 ,p_Effective_Date in date
1243 ,p_Employee_Date_of_Birth in date
1244 ,p_Duty_Station_Lookup_Code in varchar2
1245 ,p_as_of_date in date
1246 ) is
1247 begin
1248
1249 -- 110.02.2
1250 if p_First_NOAC_Lookup_Code ='300'
1251 and
1252 (to_number(substr(to_char(p_Effective_Date, 'MMDDYYYY'),5,4)) -
1253 to_number(substr(to_char(p_employee_date_of_birth, 'MMDDYYYY'),5,4)) ) < 50
1254 then
1255 hr_utility.set_message(8301, 'GHR_37147_ALL_PROCEDURE_FAIL');
1256 hr_utility.raise_error;
1257 end if;
1258
1259 -- 110.05.2
1260 -- Date of birth must be at least 13 years less than the
1261 -- effective date of personnel action, or be asterisks.
1262
1263 if (months_between(p_as_of_date, p_employee_date_of_birth) < 156 ) then
1264 hr_utility.set_message(8301, 'GHR_38412_ALL_PROCEDURE_FAIL');
1265 hr_utility.raise_error;
1266 end if;
1267
1268 -- 110.07.2
1269 -- end dated this edit on 27-oct-98 for bug 745246
1270 if p_effective_date <= fnd_date.canonical_to_date('1998/07/31') then
1271 if (
1272 (
1273 substr(p_Duty_Station_Lookup_Code,1,1) in ('0','1','2','3','4','5','6','7','8','9')
1274 and
1275 substr(p_Duty_Station_Lookup_Code,2,1) in ('0','1','2','3','4','5','6','7','8','9')
1276 )
1277 or
1278 substr(p_Duty_Station_Lookup_Code,2,1) = 'Q'
1279 )
1280 and
1281 (to_number(substr(to_char(p_Effective_Date, 'MMDDYYYY'),5,4)) -
1282 to_number(substr(to_char(p_employee_date_of_birth, 'MMDDYYYY'),5,4)) ) < 15
1283 then
1284 hr_utility.set_message(8301, 'GHR_37149_ALL_PROCEDURE_FAIL');
1285 hr_utility.raise_error;
1286 end if;
1287 end if;
1288
1289 end chk_Date_of_Birth;
1290
1291 /* Name:
1292 -- Duty Station
1293 */
1294
1295 procedure chk_duty_station
1296 (p_to_play_plan in varchar2
1297 ,p_agency_sub_element in varchar2
1298 ,p_duty_station_lookup in varchar2
1299 ,p_First_Action_NOA_LA_Code1 in varchar2
1300 ,p_First_Action_NOA_LA_Code2 in varchar2
1301 ,p_effective_date in date
1302 ) is
1303 begin
1304
1305 -- 120.00.1 From Part B,C, Notes
1306 if (
1307 substr(p_duty_station_lookup,1,1) not in ('0','1','2','3','4','5','6','7','8','9')
1308 and
1309 substr(p_duty_station_lookup,2,1) not in ('0','1','2','3','4','5','6','7','8','9')
1310 ) and
1311 substr(p_duty_station_lookup,-3,3) <> '000' then
1312 hr_utility.set_message(8301, 'GHR_37150_ALL_PROCEDURE_FAIL');
1313 hr_utility.raise_error;
1314 end if;
1315
1316 /* Commented -- Dec 2001 Patch
1317 -- 120.02.3
1318
1319 if substr(p_to_play_plan,1,2) in ('CZ','SZ','WZ')
1320 and
1321 p_agency_sub_element <> 'PC00'
1322 and
1323 substr( p_duty_station_lookup,1,2) <> 'PM'
1324 and
1325 p_duty_station_lookup is not null
1326 then
1327 hr_utility.set_message(8301, 'GHR_37151_ALL_PROCEDURE_FAIL');
1328 hr_utility.raise_error;
1329 end if;
1330 */
1331
1332 -- 120.03.3
1333 -- 10/4 08/13/99 vravikan 01-Apr-99 New Edit
1334 -- 20/2 20/02/03 Madhuri Commented Edit for Delete - March 2003 Legislative Patch
1335
1336 /*if p_effective_date >= fnd_date.canonical_to_date('19'||'99/04/01') then
1337 if substr(p_to_play_plan,1,2) in ('TW')
1338 and
1339 substr( p_duty_station_lookup,1,2) not in ( '11','24','51' )
1340 and
1341 p_duty_station_lookup is not null
1342 then
1343 hr_utility.set_message(8301, 'GHR_37166_ALL_PROCEDURE_FAIL');
1344 hr_utility.raise_error;
1345 end if;
1346 end if; */
1347
1348 -- 120.04.3
1352
1349 -- Update Date By Effective Date Comment
1350 -- 9 04/28/99 vravikan Change Edit-May 99 Patch
1351 -- 48 26/09/06 amrchakr 01-jul-2006 Remove condition of agency DJ02
1353 if p_effective_date < to_date('2006/07/01','yyyy/mm/dd')
1354 then
1355 if substr(p_to_play_plan,1,2) in ('WQ','WR','WU')
1356 and
1357 p_agency_sub_element <> 'DJ02'
1358 and
1359 substr(p_duty_station_lookup,1,2) <> 'RQ'
1360 and
1361 p_duty_station_lookup is not null
1362 then
1363 hr_utility.set_message(8301, 'GHR_37152_ALL_PROCEDURE_FAIL');
1364 hr_utility.raise_error;
1365 end if;
1366 else
1367 if substr(p_to_play_plan,1,2) in ('WQ','WR','WU')
1368 and
1369 substr(p_duty_station_lookup,1,2) <> 'RQ'
1370 and
1371 p_duty_station_lookup is not null
1372 then
1373 hr_utility.set_message(8301, 'GHR_37600_ALL_PROCEDURE_FAIL');
1374 hr_utility.raise_error;
1375 end if;
1376 end if;
1377
1378
1379 -- 120.07.2
1380 -- upd50 06-Feb-07 Raju From 01-Oct-2006 Bug#5745356 delete NOA 849
1381 if p_effective_date < to_date('2006/10/01','yyyy/mm/dd') then
1382 if (substr(p_First_Action_NOA_LA_Code1,1,1)='T' or
1383 substr(p_First_Action_NOA_LA_Code2,1,1)='T') and
1384 substr(p_duty_station_lookup,1,2) <>'PM' and
1385 p_duty_station_lookup is not null
1386 then
1387 hr_utility.set_message(8301, 'GHR_37153_ALL_PROCEDURE_FAIL');
1388 hr_utility.raise_error;
1389 end if;
1390 end if;
1391
1392 /*
1393 This has to be deleted according to the update 6 manual for april release
1394 -- 120.10.3
1395 if substr(p_duty_station_lookup,1,2) = 'US'
1396 and
1397 p_agency_sub_element <> 'DJ02'
1398 then
1399 hr_utility.set_message(8301, 'GHR_37154_ALL_PROCEDURE_FAIL');
1400 hr_utility.raise_error;
1401 end if;
1402 */
1403
1404 end chk_duty_station;
1405
1406
1407 /* Name:
1408 -- C_Education_Level
1409 */
1410
1411 procedure chk_Education_Level
1412 ( p_tenure_group_code in varchar2
1413 ,p_education_level in varchar2
1414 ,p_pay_plan in varchar2
1415 ) is
1416 begin
1417
1418 -- 130.02.3
1419 if (
1420 p_tenure_group_code = '1' or p_tenure_group_code = '2'
1421 )
1422 and
1423 p_education_level is null
1424 then
1425 hr_utility.set_message(8301, 'GHR_37155_ALL_PROCEDURE_FAIL');
1426 hr_utility.raise_error;
1427 end if;
1428
1429 -- 130.04.3
1430 if p_pay_plan = 'ES'
1431 and
1432 p_education_level is null
1433 then
1437
1434 hr_utility.set_message(8301, 'GHR_37156_ALL_PROCEDURE_FAIL');
1435 hr_utility.raise_error;
1436 end if;
1438 end chk_Education_Level;
1439
1440 /* Name:
1441 -- C_Effective_Date
1442 */
1443
1444 procedure chk_effective_date
1445 ( p_First_NOAC_Lookup_Code in varchar2
1446 ,p_Effective_Date in date
1447 ,p_Production_Date in date -- Non SF52 item
1448 ) is
1449 begin
1450 null;
1451 /*
1452 commented by skutteti on 7-apr-98. As per the new changes this edit has to be deleted.
1453 -- 140.02.2
1454 if (
1455 p_First_NOAC_Lookup_Code = '117' or
1456 p_First_NOAC_Lookup_Code = '517'
1457 )
1458 and
1459 not (
1460 to_char(p_Effective_Date,'MMDD')>= '0513'
1461 and
1462 to_char(p_Effective_Date,'MMDD')<= '0930'
1463 )
1464 then
1465 hr_utility.set_message(8301, 'GHR_37157_ALL_PROCEDURE_FAIL');
1466 hr_utility.raise_error;
1467 end if;
1468 */
1469
1470
1471 /* p_Production_Date can not be found.
1472 -- 140.08.2
1473 if Months_Between(p_Effective_Date,p_Production_Date)>6
1474 or
1475 -- Year_Between (p_Effective_Date,p_Production_Date)<-2
1476
1477
1478 to_number(substr(to_char(p_Effective_Date, 'MMDDYYYY'),5,4)) -
1479 to_number(substr(to_char(p_Production_Date, 'MMDDYYYY'),5,4)) ) < -2
1480 then
1481 hr_utility.set_message(8301, 'GHR_37158_ALL_PROCEDURE_FAIL');
1482 hr_utility.raise_error;
1483 end if;
1484 */
1485
1486 end chk_effective_date;
1487
1488 /* Name:
1489 Handicap
1490 */
1491
1492 procedure chk_Handicap
1493 (p_First_Action_NOA_Code1 in varchar2
1494 ,p_First_Action_NOA_Code2 in varchar2
1495 ,p_First_NOAC_Lookup_Code in varchar2
1496 ,p_Effective_Date in date --bug# 5619873
1497 ,p_Handicap in varchar2 -- non SF52 item
1498 ) is
1499 begin
1500
1501 -- 220.02.2
1502 -- Dec 01 Patch vravikan Delete YKM
1503 --upd49 08-Jan-07 Raju Bug#5619873 From 01-Sep-2006 Delete WTM
1504 if p_effective_date < fnd_date.canonical_to_date('2006/09/01') then
1505 if ( p_First_Action_NOA_Code1 in ('WTM','WUM') or
1506 p_First_Action_NOA_Code2 in ('WTM','WUM')) and
1507 to_number(p_Handicap) not between 6 and 94 and
1508 p_Handicap <>'04' and
1509 p_Handicap is not null then
1510 hr_utility.set_message(8301, 'GHR_37159_ALL_PROCEDURE_FAIL');
1511 hr_utility.set_message_token('APP_AUTH','WTM, WUM');
1512 hr_utility.raise_error;
1513 end if;
1514 else
1515 if ( p_First_Action_NOA_Code1 in ('WUM') or
1516 p_First_Action_NOA_Code2 in ('WUM')) and
1517 to_number(p_Handicap) not between 6 and 94 and
1518 p_Handicap <>'04' and
1519 p_Handicap is not null then
1520 hr_utility.set_message(8301, 'GHR_37159_ALL_PROCEDURE_FAIL');
1521 hr_utility.set_message_token('APP_AUTH','WUM');
1522 hr_utility.raise_error;
1523 end if;
1524 end if;
1525
1526
1527 -- 220.05.2
1528 if substr(p_First_NOAC_Lookup_Code,1,1)='1'
1529 and
1530 p_Handicap='04'
1531 then
1532 hr_utility.set_message(8301, 'GHR_37160_ALL_PROCEDURE_FAIL');
1533 hr_utility.raise_error;
1534 end if;
1535
1536 end chk_Handicap;
1537
1538 /* Name
1539 -- Individual/Group Award
1540 */
1541
1542 procedure chk_indiv_Award
1543 (p_First_NOAC_Lookup_Code in varchar2
1544 ,p_Indiv_Award in varchar2
1545 ,p_effective_date in date
1546 ) is
1547 begin
1548
1549 --Commented as per EOY 2003 cpdf changes by Ashley
1550 -- 240.02.2
1551 -- Award Req 8/15/00 vravikan 30-sep-2000 End date
1552 /* if p_effective_date <= to_date('2000/09/30','yyyy/mm/dd') then
1553 if p_First_NOAC_Lookup_Code in ('874','875','876','877')
1554 and
1555 p_Indiv_Award is null
1556 then
1557 hr_utility.set_message(8301, 'GHR_37161_ALL_PROCEDURE_FAIL');
1558 hr_utility.raise_error;
1559 end if;
1560 end if;*/
1561 null;
1562 end chk_indiv_Award;
1563
1564 end GHR_CPDF_CHECK2;
[Home] [Help]