1 PACKAGE BODY GHR_BEN_VALIDATION AS
2 /* $Header: ghbenval.pkb 120.29.12020000.13 2013/02/06 11:08:33 vmididho ship $ */
3 --
4 --g_debug BOOLEAN := hr_utility.debug_enabled;
5
6 g_debug BOOLEAN := TRUE;
7
8 PROCEDURE validate_benefits(
9 p_effective_date in date
10 , p_which_eit in varchar2
11 , p_pa_request_id in number default null
12 , p_first_noa_code in varchar2 default null
13 , p_noa_family_code in varchar2 default null
14 , p_passed_element in varchar2 default null
15 , p_health_plan in varchar2 default null
16 , p_enrollment_option in varchar2 default null
17 , p_date_fehb_elig in date default null
18 , p_date_temp_elig in date default null
19 , p_temps_total_cost in varchar2 default null
20 , p_pre_tax_waiver in varchar2 default null
21 , p_tsp_scd in varchar2 default null
22 , p_tsp_amount in number default null
23 , p_tsp_rate in number default null
24 , p_tsp_status in varchar2 default null
25 , p_tsp_status_date in date default null
26 , p_agency_contrib_date in date default null
27 , p_emp_contrib_date in date default null
28 , p_tenure in varchar2 default null
29 , p_retirement_plan in varchar2 default null
30 , p_fegli_elig_exp_date in date default null
31 , p_fers_elig_exp_date in date default null
32 , p_annuitant_indicator in varchar2 default null
33 , p_assignment_id in number default null
34 --Bug # 12973371 Added TSP Roth amount and rate
35 , p_tsp_roth_401k_amt in number default null
36 , p_tsp_roth_401k_rate in number default null)
37 IS
38 l_payroll_id ghr_pa_request_extra_info.rei_information3%type;
39 l_noa_family_code ghr_pa_requests.noa_family_code%type;
40 l_st_month VARCHAR2(20);
41 l_end_month VARCHAR2(20);
42 l_pay_month VARCHAR2(20);
43 l_start_date per_time_periods.start_date%type;
44 l_validate BOOLEAN;
45
46 CURSOR c_payroll_id(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
47 SELECT rei_information3 payroll_id
48 FROM ghr_pa_request_extra_info
49 WHERE pa_request_id = c_pa_request_id
50 AND information_type = 'GHR_US_PAR_PAYROLL_TYPE';
51
52 CURSOR c_start_date(c_payroll_id pay_payrolls_f.payroll_id%type, c_year varchar2, c_month varchar2) IS
53 SELECT min(start_date) start_date
54 FROM per_time_periods
55 WHERE payroll_id = c_payroll_id
56 AND TO_CHAR(start_date,'YYYY') = c_year
57 AND TO_CHAR(start_date,'MM') = c_month;
58
59
60 --check if this person is rehired
61 Cursor c_chk_if_rehire (num_of_days number) is
62 select 'Y'
63 from per_all_assignments_f pasf1, per_all_assignments_f pasf2
64 where pasf2.person_id = pasf1.person_id
65 AND pasf2.assignment_id = p_assignment_id
66 AND p_effective_date between pasf2.effective_start_date and pasf2.effective_end_date
67 and (p_effective_date - num_of_days) between pasf1.effective_start_date and pasf1.effective_end_date
68 and pasf1.assignment_type <> 'B';
69
70 --Begin Bug 12637350
71 CURSOR c_first_nte_date is
72 SELECT first_noa_information1
73 FROM ghr_pa_requests
74 WHERE pa_request_id = p_pa_request_id;
75
76 l_nte_date ghr_pa_requests.first_noa_information1%type;
77
78 cursor cur_asg_prior_date( p_assignment_id in number) is
79 Select asg.effective_start_date
80 from per_all_assignments_f asg
81 Where asg.assignment_id = p_assignment_id
82 and asg.primary_flag = 'Y'
83 and asg.assignment_type <> 'B'
84 order by asg.effective_start_date;
85
86 l_asg_start_date date;
87 --End Bug 12637350
88
89
90 FUNCTION check_element_exist(p_assignment_id in number,
91 p_element_name varchar2,
92 p_effective_date date) RETURN BOOLEAN
93 IS
94 l_new_element_name varchar2(250);
95 l_business_group_id per_all_people_f.business_group_id%type;
96 l_element_type_id pay_element_types_f.element_type_id%type;
97 l_exists BOOLEAN;
98
99 CURSOR c_element_exist(c_assignment_id per_all_assignments_f.assignment_id%type,
100 c_effective_date per_all_assignments_f.effective_start_date%type,
101 c_element_type_id pay_element_types_f.element_type_id%type)
102 IS
103 SELECT 1
104 FROM pay_element_entries_f ele
105 WHERE ele.assignment_id = c_assignment_id
106 AND c_effective_date BETWEEN ele.effective_start_date AND ele.effective_end_date
107 AND element_type_id = c_element_type_id;
108
109 CURSOR c_element_type_id (c_element_name pay_element_types_f.element_name%type,
110 c_business_group_id pay_element_links_f.business_group_id%type,
111 c_effective_date per_all_assignments_f.effective_start_date%type)
112 IS
113 SELECT elt.element_type_id
114 FROM pay_element_links eli, pay_element_types_f elt
115 WHERE elt.element_type_id = eli.element_type_id
116 AND eli.business_group_id = c_business_group_id
117 AND elt.element_name = c_element_name;
118 BEGIN
119 l_exists := FALSE;
120 -- l_business_group_id := FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
121 FND_PROFILE.get('PER_BUSINESS_GROUP_ID', l_business_group_id);
122
123 IF g_debug = TRUE THEN
124 hr_utility.set_location('Entering check_element_exist',12);
125 hr_utility.set_location('l_business_group_id ' || l_business_group_id,12);
126 END IF;
127 l_new_element_name := pqp_fedhr_uspay_int_utils.return_new_element_name(p_element_name,l_business_group_id,p_effective_date,null);
128
129 IF g_debug = TRUE THEN
130 hr_utility.set_location('l_new_element_name ' || l_new_element_name,12);
131 END IF;
132
133 FOR l_cur_el_type IN c_element_type_id(l_new_element_name,l_business_group_id,p_effective_date) LOOP
134 l_element_type_id := l_cur_el_type.element_type_id;
135 END LOOP;
136
137 IF g_debug = TRUE THEN
138 hr_utility.set_location('l_element_type_id ' || l_element_type_id,12);
139 hr_utility.set_location('p_assignment_id ' || p_assignment_id,12);
140 END IF;
141
142 FOR l_cur_el_exists IN c_element_exist(p_assignment_id, p_effective_date, l_element_type_id) LOOP
143 l_exists := TRUE;
144 IF g_debug = TRUE THEN
145 hr_utility.set_location(l_new_element_name || 'element exists',12);
146 END IF;
147 END LOOP;
148
149 RETURN l_exists;
150 END check_element_exist;
151 BEGIN
152 -- Validation for Health Plan
153 -- If called from element entry
154 l_validate := TRUE;
155 hr_utility.set_location('which eit' || p_which_eit,12);
156 IF (p_which_eit = 'E' AND p_passed_element = 'Health Benefits') OR p_which_eit = 'R'
157 THEN
158 IF g_debug = TRUE THEN
159
160 hr_utility.set_location('p_health_plan' || p_health_plan,12);
161 hr_utility.set_location('p_noa_family_code' || p_noa_family_code,12);
162 END IF;
163
164 IF p_which_eit = 'R' AND p_noa_family_code IN ('CONV_APP','EXT_NTE') THEN
165 IF p_health_plan IS NULL AND
166 p_enrollment_option IS NULL AND
167 p_temps_total_cost IS NULL THEN
168 -- Check if element exists already
169 l_validate := NOT(check_element_exist(p_assignment_id => p_assignment_id,
170 p_element_name => 'Health Benefits',
171 p_effective_date => p_effective_date));
172 END IF;
173
174 END IF; -- IF p_which_eit = 'R' AND p_no
175
176 IF l_validate = TRUE THEN
177 IF p_health_plan IS NULL THEN
178 hr_utility.set_message(8301,'GHR_38942_HB_NULL_PLAN_ENROLL');
179 hr_utility.raise_error;
180 END IF;
181
182 -- Enrollment Option
183 IF p_enrollment_option IS NULL THEN
184 hr_utility.set_message(8301,'GHR_38942_HB_NULL_PLAN_ENROLL');
185 hr_utility.raise_error;
186 NULL;
187 ELSE
188 IF p_health_plan = 'ZZ' AND p_enrollment_option NOT IN('W','X','Y','Z') THEN
189 hr_utility.set_message(8301,'GHR_38950_FEHB_INV_PLAN_OPTION');
190 hr_utility.raise_error;
191 END IF;
192
193 IF p_health_plan <> 'ZZ' AND p_enrollment_option NOT IN('1','2','4','5') THEN
194 hr_utility.set_message(8301,'GHR_38960_FEHB_NOT_ZZ');
195 hr_utility.raise_error;
196 END IF; -- IF NVL(l_health_plan,hr
197 END IF; -- IF l_enrollment_option IS NULL T
198
199 -- Pretax waiver Validation
200 IF p_health_plan = 'ZZ' AND p_pre_tax_waiver = 'Y' THEN
201 hr_utility.set_message(8301,'GHR_38956_PRE_TAX_ZZ_PLAN');
202 hr_utility.raise_error;
203 END IF;
204 END IF; -- IF l_validate = TRUE T
205 END IF; -- IF (p_which_eit = 'E' AND
206
207 -- Bug # 11808857 modified to not to raise error when retirment plan is 4 and annuitant indicator is not in 2,3,9
208 IF (p_which_eit = 'E' AND p_passed_element = 'TSP') OR p_which_eit = 'R' THEN
209 l_validate := TRUE;
210
211 --Bug # 12973371
212 -- Bug # 13571004
213 IF p_which_eit = 'R' THEN
214 IF ((p_tsp_roth_401k_amt IS NOT NULL AND p_tsp_roth_401k_amt <> '0' ) OR
215 (p_tsp_roth_401k_rate IS NOT NULL AND p_tsp_roth_401k_rate <> '0' ))
216 AND (p_effective_date < to_date('01/01/2012','dd/mm/yyyy')) THEN
217 hr_utility.set_message(8301,'GHR_38347_TSP_ROTH_NOT_EFF');
218 hr_utility.raise_error;
219 END IF;
220 END IF;
221
222
223 --Bug # 12973371
224 --Bug# 15941036 added Retirement codes DR,KR,LR,MR,NR,PR
225 IF NVL(p_retirement_plan,hr_api.g_varchar2) NOT IN ('D','K','L','M','N','P','1','3','6','C','E','F','G','R','T','H','W','DR','KR','LR','MR','NR','PR') THEN
226 IF NOT(NVL(p_retirement_plan,hr_api.g_varchar2) = '4' AND NVL(p_annuitant_indicator,hr_api.g_varchar2) NOT IN ('2','3','9')) THEN
227 IF p_tsp_amount IS NOT NULL OR p_tsp_rate IS NOT NULL THEN
228 hr_utility.set_message(8301,'GHR_38958_TSP_AMT_NOT_FERS');
229 hr_utility.raise_error;
230 END IF;
231
232 -- TSP Status should be null
233 IF p_tsp_status IS NOT NULL THEN
234 hr_utility.set_message(8301,'GHR_38958_TSP_AMT_NOT_FERS');
235 hr_utility.raise_error;
236 END IF;
237
238 --Bug # 12973371 Added Validation for TSP Roth amount and rate
239 -- Bug # 13571004
240 IF p_which_eit = 'R' THEN
241 IF p_tsp_roth_401k_amt IS NOT NULL OR p_tsp_roth_401k_rate IS NOT NULL THEN
242 hr_utility.set_message(8301,'GHR_38958_TSP_AMT_NOT_FERS');
243 hr_utility.raise_error;
244 END IF;
245 END IF;
246
247 END IF;
248 END IF; -- IF NVL(l_retirement_plan,hr_api.g_v
249
250 IF p_which_eit = 'E' THEN
251 -- TSP Status should not be null
252 IF p_tsp_status IS NULL THEN
253 hr_utility.set_message(8301,'GHR_38976_TSP_STS_REQD');
254 hr_utility.raise_error;
255 END IF;
256 END IF;
257
258 IF g_debug = TRUE THEN
259 hr_utility.set_location('If Retirement plan is not in FERS or CSRS ',1234);
260 END IF;
261
262 -- Either only Amount should be entered or Rate...
263 IF p_tsp_amount IS NOT NULL AND p_tsp_rate IS NOT NULL THEN
264 hr_utility.set_message(8301,'GHR_38685_RATE_AMOUNT_COMBO');
265 hr_utility.raise_error;
266 END IF;
267
268 --Bug # 12973371 Added Validation for TSP Roth amount and rate
269 -- Bug # 13571004
270 IF p_which_eit = 'R' AND p_tsp_roth_401k_amt IS NOT NULL AND p_tsp_roth_401k_rate IS NOT NULL THEN
271 hr_utility.set_message(8301,'GHR_38685_RATE_AMOUNT_COMBO');
272 hr_utility.raise_error;
273 END IF;
274
275 hr_utility.set_location('p_tsp_amount'||p_tsp_amount,10);
276 hr_utility.set_location('p_tsp_rate'||p_tsp_rate,10);
277 -- Status must be 'Y' or 'W' or 'A'
278 IF NVL(p_tsp_amount,'0') <> '0' OR NVL(p_tsp_rate,'0') <> '0' THEN
279 IF NVL(p_tsp_status,hr_api.g_varchar2) NOT IN ('Y','W','A') THEN
280 hr_utility.set_message(8301,'GHR_38678_INCORRECT_STATUS');
281 hr_utility.raise_error;
282 END IF;
283 END IF;
284
285 --Bug # 12973371 Added Validation for TSP Roth amount and rate
286 -- Bug # 13571004
287 --BUG # 13724018 modified to include the status Y, W or A
288 IF p_which_eit = 'R' AND (NVL(p_tsp_roth_401k_amt,'0') <> '0' OR NVL(p_tsp_roth_401k_rate,'0') <> '0') OR
289 NVL(p_tsp_amount,'0') <> '0' OR NVL(p_tsp_rate,'0') <> '0' THEN
290 IF NVL(p_tsp_status,hr_api.g_varchar2) NOT IN ('Y','W','A') THEN
291 hr_utility.set_message(8301,'GHR_38678_INCORRECT_STATUS');
292 hr_utility.raise_error;
293 END IF;
294 END IF;
295
296 IF g_debug = TRUE THEN
297 hr_utility.set_location('p_tsp_status' || p_tsp_status,1234);
298 END IF;
299
300 --BUG # 13718289 removed this validation as while updating
301 -- TSP Status to Y from TSP Roth this will get fire when all the values are nulls.
302 -- This will get fire from PLD and also through RPA
303 /*IF p_tsp_status IN ('Y','W') THEN
304 -- Either Amount or Rate should be entered
305 IF NVL(p_tsp_amount,'0') = '0' AND NVL(p_tsp_rate,'0') = '0'
306 AND NVL(p_tsp_roth_401k_amt,'0') = '0' AND NVL(p_tsp_roth_401k_rate,'0') = '0' THEN
307 hr_utility.set_message(8301,'GHR_38677_INV_RATE_OR_STATUS');
308 hr_utility.raise_error;
309 END IF;
310 END IF;*/
311
312 -- Bug # 13571004
313 --BUG # 13718289 Added for TSP elements as well
314 IF p_which_eit = 'R' AND p_tsp_status IN ('Y','W') THEN
315 -- Either Amount or Rate should be entered
316 IF NVL(p_tsp_roth_401k_amt,'0') = '0' AND NVL(p_tsp_roth_401k_rate,'0') = '0'
317 AND NVL(p_tsp_amount,'0') = '0' AND NVL(p_tsp_rate,'0') = '0' THEN
318 hr_utility.set_message(8301,'GHR_38677_INV_RATE_OR_STATUS');
319 hr_utility.raise_error;
320 END IF;
321 END IF;
322
323
324 -- TSP Status date should be entered if Status is entered
325 -- Status Date should not be blank
326 IF p_tsp_status IS NOT NULL THEN
327 IF p_tsp_status_date IS NULL THEN
328 hr_utility.set_message(8301,'GHR_38675_INVALID_STATUS_DATE');
329 hr_utility.raise_error;
330 END IF;
331 ELSE
332 IF p_tsp_status_date IS NOT NULL THEN
333 hr_utility.set_message(8301,'GHR_38676_INVALID_STATUS');
334 hr_utility.raise_error;
335 END IF;
336 END IF;
337
338 IF g_debug = TRUE THEN
339 hr_utility.set_location('TSP Status date' ,1234);
340 END IF;
341
342
343 -- 2.2.2.1.6. If retirement Plan is 2, 4, or 5 and user has entered TSP information, provide error:
344 -- "TSP information is not appropriate for retirement plans 2, 4, or 5.
345 -- Please remove any TSP values from the Benefits EIT."
346 -- Bug # 11808857 added annuitant indicator validation
347 --Bug # 12973371 Added Validation for TSP Roth amount and rate
348 IF p_retirement_plan IN ('2','4','5') AND p_annuitant_indicator IN ('2','3','9')THEN
349 IF p_tsp_amount IS NOT NULL OR
350 p_tsp_rate IS NOT NULL OR
351 p_tsp_status IS NOT NULL OR
352 p_tsp_status_date IS NOT NULL
353 THEN
354 -- Raise Error message
355 hr_utility.set_message(8301,'GHR_38965_TSP_OTH');
356 hr_utility.raise_error;
357 END IF;
358 END IF; -- IF p_retirement_plan IN ('2','4','5') THEN
359
360 -- Bug # 13571004
361 IF p_which_eit = 'R' AND p_retirement_plan IN ('2','4','5') AND p_annuitant_indicator IN ('2','3','9')THEN
362 IF p_tsp_amount IS NOT NULL OR
363 p_tsp_rate IS NOT NULL OR
364 p_tsp_status IS NOT NULL OR
365 p_tsp_status_date IS NOT NULL OR
366 p_tsp_roth_401k_amt IS NOT NULL OR
367 p_tsp_roth_401k_rate IS NOT NULL THEN
368 -- Raise Error message
369 hr_utility.set_message(8301,'GHR_38965_TSP_OTH');
370 hr_utility.raise_error;
371 END IF;
372 END IF; -- IF p_retirement_plan IN ('2','4','5') THEN
373 IF g_debug = TRUE THEN
374 hr_utility.set_location('If retirement Plan is 2, 4, or 5',1234);
375 END IF;
376
377 IF (p_tsp_rate IS NULL OR p_tsp_rate = '0' )
378 AND (p_tsp_amount IS NULL OR p_tsp_amount = '0')
379 AND (p_tsp_status = 'A' ) THEN
380 hr_utility.set_message(8301,'GHR_37454_TSP_STATUS');
381 hr_utility.raise_error;
382 END IF;
383
384 IF ((p_tsp_rate IS NOT NULL AND p_tsp_rate <> '03' ) OR
385 (p_tsp_amount IS NOT NULL AND p_tsp_amount <> '0' ))
386 AND (p_tsp_status = 'A') THEN
387 hr_utility.set_message(8301,'GHR_37453_TSP_STATUS');
388 hr_utility.raise_error;
389 END IF;
390
391 --Bug # 12973371
392 IF p_which_eit = 'R' AND ((p_tsp_rate IS NOT NULL AND p_tsp_rate <> '03' ) OR
393 (p_tsp_amount IS NOT NULL AND p_tsp_amount <> '0' ) OR
394 (p_tsp_roth_401k_amt IS NOT NULL AND p_tsp_roth_401k_amt <> '0' ) OR
395 (p_tsp_roth_401k_rate IS NOT NULL AND p_tsp_roth_401k_rate <> '0' ))
396 AND (p_tsp_status = 'A') THEN
397 hr_utility.set_message(8301,'GHR_38219_INVALID_TSP_STATUS');
398 hr_utility.raise_error;
399 END IF;
400 --Bug # 12973371
401
402 END IF; -- IF (p_which_eit = 'E' AND p_passed_element = 'TSP')
403
404 IF p_which_eit = 'R' OR p_which_eit = 'P' THEN
405 -- Validation for FEHB eligibility
406 IF g_debug = TRUE THEN
407 hr_utility.set_location('p_date_fehb_elig ' || to_char(p_date_fehb_elig,'dd/mm/yyyy') ,1234);
408 END IF;
409
410 IF p_date_fehb_elig IS NOT NULL THEN
411 IF p_date_fehb_elig <= p_effective_date THEN
412 hr_utility.set_message(8301,'GHR_38951_BEN_ELIG_DATE');
413 hr_utility.set_message_token('BEN_ELIG_DATE','FEHB Eligibility Expiration Date');
414 hr_utility.raise_error;
415 END IF;
416 --Bug# 10632221, removed noa codes 171, 571
417 IF p_first_noa_code IN ('115','122','149','515','522','549') THEN
418 hr_utility.set_message(8301,'GHR_38952_FEHB_ELIG_FOR_TMP');
419 hr_utility.raise_error;
420 END IF;
421 --Begin Bug 12637350
422 IF p_noa_family_code IN ('APP','CONV_APP','EXT_NTE') THEN
423 FOR l_first_nte_date IN c_first_nte_date LOOP
424 l_nte_date := fnd_date.canonical_to_date(l_first_nte_date.first_noa_information1);
425 END LOOP;
426 IF p_noa_family_code IN ('APP') THEN
427 l_asg_start_date := p_effective_date+365;
428 ELSIF p_noa_family_code IN ('CONV_APP','EXT_NTE') THEN
429 for asg_prior_date in cur_asg_prior_date(p_assignment_id) loop
430 l_asg_start_date := asg_prior_date.effective_start_date+365;
431 exit;
432 end loop;
433 END IF;
434 --Bug# 14686129 removed equal condition not to trigger error for 365 days
435 IF l_nte_date < l_asg_start_date THEN
436 hr_utility.set_message(8301,'GHR_38952_FEHB_ELIG_FOR_TMP');
437 hr_utility.raise_error;
438 END IF;
439 END IF;
440 --End Bug 12637350
441 END IF;
442
443 IF g_debug = TRUE THEN
444 hr_utility.set_location('p_date_temp_elig ' || to_char(p_date_temp_elig,'dd/mm/yyyy') ,1234);
445 END IF;
446 -- Date Temp Eligibility Expires - Validation
447 IF p_date_temp_elig IS NOT NULL THEN
448 IF p_first_noa_code IS NOT NULL THEN
449 IF p_first_noa_code IN ('115','122','149','171','515','522','549','571') AND p_tenure = '0' THEN
450 IF p_date_temp_elig <= p_effective_date THEN
451 hr_utility.set_message(8301,'GHR_38953_TMP_ELIG_FUT_DATE');
452 hr_utility.raise_error;
453 END IF;
454 END IF;
455 -- If tenure code is not 0, then value should be null
456 IF p_first_noa_code IN ('115','122','149','171','515','522','549','571') AND NVL(p_tenure,hr_api.g_varchar2) <> '0' THEN
457 hr_utility.set_message(8301,'GHR_38954_TMP_ELIG_BLANK');
458 hr_utility.raise_error;
459 END IF;
460 -- This field is valid only for Temp. appointments. else throw error.
461 -- Bug 4668813
462 hr_utility.set_location('p_first_noa_code'|| p_first_noa_code,223);
463
464 IF p_which_eit = 'R' AND (p_first_noa_code IS NOT NULL AND p_first_noa_code NOT IN ('115','122','149','171','515','522','549','571')) THEN
465 hr_utility.set_message(8301,'GHR_38955_TMP_ELIG_FOR_TMP');
466 hr_utility.raise_error;
467 END IF;
468 ELSIF p_which_eit = 'P' THEN
469 IF p_date_temp_elig <= p_effective_date THEN
470 hr_utility.set_message(8301,'GHR_38953_TMP_ELIG_FUT_DATE');
471 hr_utility.raise_error;
472 END IF;
473 END IF; -- IF p_first_noa_code IS NOT NULL THEN
474 END IF; -- IF p_rei_information4 IS NOT
475
476 --
477 IF p_date_temp_elig IS NOT NULL AND p_date_temp_elig > NVL(p_effective_date,TRUNC(SYSDATE)) THEN
478 IF p_health_plan IS NOT NULL THEN
479 IF p_health_plan <> 'ZZ' AND p_enrollment_option <> 'Z' THEN
480 hr_utility.set_message(8301,'GHR_38964_FEHB_TMP_PLAN');
481 hr_utility.raise_error;
482 END IF;
483 END IF;
484 END IF; -- IF p_date_temp_elig IS NOT NULL
485
486 IF g_debug = TRUE THEN
487 hr_utility.set_location('p_fegli_elig_exp_date ' || to_char(p_fegli_elig_exp_date,'dd/mm/yyyy') ,1234);
488 END IF;
489
490 -- FEGLI Eligibility Expiration Validation
491 IF p_fegli_elig_exp_date IS NOT NULL THEN
492 IF p_fegli_elig_exp_date <= NVL(p_effective_date,TRUNC(SYSDATE)) THEN
493 hr_utility.set_message(8301,'GHR_38951_BEN_ELIG_DATE');
494 hr_utility.set_message_token('BEN_ELIG_DATE','FEGLI Eligibility Expiration Date');
495 hr_utility.raise_error;
496 END IF;
497 END IF; -- IF p_fegli_elig_exp_date IS NOT NULL THEN
498
499 IF g_debug = TRUE THEN
500 hr_utility.set_location('-- FEGLI Eligibility Expiration Validatio',1234);
501 END IF;
502 -- FERS Eligibility Expiration Validation
503 IF p_fers_elig_exp_date IS NOT NULL THEN
504 IF p_fers_elig_exp_date <= NVL(p_effective_date,TRUNC(SYSDATE)) THEN
505 hr_utility.set_message(8301,'GHR_38951_BEN_ELIG_DATE');
506 hr_utility.set_message_token('BEN_ELIG_DATE','FERS Eligibility Expiration Date');
507 hr_utility.raise_error;
508 END IF;
509 END IF; -- IF p_fers_elig_exp_date IS NOT NULL THEN
510 IF g_debug = TRUE THEN
511 hr_utility.set_location('-- FERS Eligibility Expiration Validation',1234);
512 END IF;
513
514 IF p_which_eit = 'R' AND p_noa_family_code IN ('CONV_APP','EXT_NTE') THEN
515 IF p_tsp_amount IS NULL AND
516 p_tsp_rate IS NULL AND
517 p_tsp_status IS NULL AND
518 p_tsp_status_date IS NULL THEN
519 -- Check if element exists already
520 l_validate := NOT(check_element_exist(p_assignment_id => p_assignment_id,
521 p_element_name => 'TSP',
522 p_effective_date => p_effective_date));
523 END IF;
524
525 END IF; -- IF p_which_eit = 'R' AND p_no
526 --Begin Bug# 12557463 commented below code added in ghrvalid.pkb since tsp_scd is moved to new RPA EIT
527 /*
528 IF l_validate = TRUE THEN
529 IF p_retirement_plan IN ('D','K','L','M','N','P') THEN
530 IF LTRIM(p_tsp_scd) IS NULL THEN
531 -- Raise Error message
532 hr_utility.set_message(8301,'GHR_38957_TSP_FERS');
533 hr_utility.raise_error;
534 END IF;
535 END IF; -- IF p_retirement_plan IN ('D','K','L'...
536 END IF;
537
538 --Bug# 11808857 modified to not to raise error when retirment plan is 4 and annuitant indicator is not in 2, 3, 9
539 IF p_tsp_scd IS NOT NULL AND p_retirement_plan IS NOT NULL THEN
540 IF p_retirement_plan NOT IN ('D','K','L','M','N','P') THEN
541 IF NOT(NVL(p_retirement_plan,hr_api.g_varchar2) = '4' AND NVL(p_annuitant_indicator,hr_api.g_varchar2) NOT IN ('2','3','9')) THEN
542 -- Raise Error message
543 hr_utility.set_message(8301,'GHR_38392_NON_TSP_FERS');--Bug# 4769233
544 hr_utility.raise_error;
545 END IF;
546 END IF;
547 END IF;
548 */
549 --End Bug# 12557463
550 -- Bug # 11808857 modified to not to raise error when retirment plan is 4 and annuitant indicator is not in 2, 3, 9
551 --Bug# 15941036 added Retirement codes DR,KR,LR,MR,NR,PR
552 IF p_retirement_plan NOT IN ('D','K','L','M','N','P','1','3','6','C','E','F','G','R','T','H','W','DR','KR','LR','MR','NR','PR') THEN
553 IF NOT(NVL(p_retirement_plan,hr_api.g_varchar2) = '4' AND NVL(p_annuitant_indicator,hr_api.g_varchar2) NOT IN ('2','3','9')) THEN
554 -- Emp Contrib Elig date should be Null
555 IF p_emp_contrib_date IS NOT NULL THEN
556 hr_utility.set_message(8301,'GHR_38958_TSP_AMT_NOT_FERS');
557 hr_utility.raise_error;
558 END IF;
559 END IF;
560 END IF; -- IF NVL(l_retirement_plan,hr_api.g_v
561
562 IF g_debug = TRUE THEN
563 hr_utility.set_location('If Retirement plan is not in FERS or CSRS ',1234);
564 END IF;
565
566 -- IF TSP status is I then the date must be future.
567 IF p_tsp_status = 'I' AND NVL(p_effective_date, TRUNC(SYSDATE)) > p_agency_contrib_date THEN
568 hr_utility.set_message(8301,'GHR_38680_INV_AGENCY_CONTRIB');
569 hr_utility.raise_error;
570 END IF;
571
572 IF g_debug = TRUE THEN
573 hr_utility.set_location(' IF TSP status is I',1234);
574 END IF;
575
576
577
578
579
580 -- 2.2.2.1.6. If retirement Plan is 2, 4, or 5 and user has entered TSP information, provide error:
581 -- "TSP information is not appropriate for retirement plans 2, 4, or 5.
582 -- Please remove any TSP values from the Benefits EIT."
583 IF p_which_eit = 'R' THEN
584 IF (p_retirement_plan IN ('2','4','5') AND p_annuitant_indicator IN ('2','3','9') )
585 OR p_retirement_plan IN ('J','X') THEN
586 IF p_agency_contrib_date IS NOT NULL OR
587 p_emp_contrib_date IS NOT NULL THEN
588 -- Raise Error message
589 hr_utility.set_message(8301,'GHR_38965_TSP_OTH');
590 hr_utility.raise_error;
591 END IF;
592 END IF; -- IF p_retirement_plan IN ('2','4','5') THEN
593 END IF;
594
595 IF g_debug = TRUE THEN
596 hr_utility.set_location('If retirement Plan is 2, 4, or 5',1234);
597 END IF;
598
599 IF p_which_eit = 'R' THEN
600 -- If Agency or Emp contrib dates are entered, then status cannot be null
601 IF p_agency_contrib_date IS NOT NULL OR p_emp_contrib_date IS NOT NULL THEN
602 IF p_tsp_status IS NULL THEN
603 hr_utility.set_message(8301,'GHR_38679_BLANK_STATUS');
604 hr_utility.raise_error;
605 END IF;
606 END IF;
607
608 -- Bug 4691271 and 4687755
609 -- If FERS, the value should be not null. Else it should be null.
610 --Begin Bug# 8622486
611 /*IF p_retirement_plan IN ('D','K','L','M','N','P') THEN
612 IF p_agency_contrib_date IS NULL THEN -- Bug 4693453
613 hr_utility.set_message(8301,'GHR_38977_TSP_AGNCY_DATE_REQD');
614 hr_utility.raise_error;
615 END IF;
616 ELS*/
617 -- Bug # 11808857 modified to not to raise error when retirment plan is 4 and annuitant indicator is not in 2, 3, 9
618 --Bug# 15941036 added Retirement codes DR,KR,LR,MR,NR,PR
619 IF p_retirement_plan NOT IN ('D','K','L','M','N','P','DR','KR','LR','MR','NR','PR') AND p_agency_contrib_date IS NOT NULL THEN
620 IF NOT(NVL(p_retirement_plan,hr_api.g_varchar2) = '4' AND NVL(p_annuitant_indicator,hr_api.g_varchar2) NOT IN ('2','3','9')) THEN
621 hr_utility.set_message(8301,'GHR_38961_NOT_FERS_AGNCY_DATE');
622 hr_utility.raise_error;
623 END IF;
624 END IF;
625 --end Bug# 8622486
626 END IF;
627
628 -- 2.2.1.1.3. AND 2.2.2.1.3 TSP Agncy Contrib Elig Date is required for all FERS covered employees.
629 -- If the employee's retirement plan is D, K, L, M, N, or P and the defaulted value is removed by the user
630 -- and a valid date is not entered provide error message: "TSP Agency Contrib Elig Date is required for all FERS employees.
631 -- Please enter a valid date."
632
633 -- First find out the valid date
634 -- Get Payroll ID
635 FOR l_cur_payroll_id IN c_payroll_id(p_pa_request_id) LOOP
636 l_payroll_id := l_cur_payroll_id.payroll_id;
637 END LOOP;
638 /*Bug#6312182*/
639 /*Start: Commented the code as the logic for determining the eligibility date has been changed.
640 IF to_number(to_char(p_effective_date,'MM')) BETWEEN 1 AND 6 THEN
641 FOR l_cur_start_date IN c_start_date(l_payroll_id,to_char(p_effective_date,'YYYY'), '12') LOOP
642 l_start_date := l_cur_start_date.start_date;
643 END LOOP;
644 l_st_month := 'January';
645 l_end_month := 'June';
646 l_pay_month := 'December';
647 ELSE
648 FOR l_cur_start_date IN c_start_date(l_payroll_id,to_char(p_effective_date+365,'YYYY'), '06') LOOP
649 l_start_date := l_cur_start_date.start_date;
650 END LOOP;
651 l_st_month := 'July';
652 l_end_month := 'December';
653 l_pay_month := 'June';
654 END IF; -- IF to_number(to_char(l_effective_date,'MM'))
655 :End*/
656 --Begin Bug# 8622486
657 /*IF to_number(to_char(p_effective_date,'MM')) BETWEEN 6 AND 11 THEN
658 FOR l_cur_start_date IN c_start_date(l_payroll_id,
659 to_char(p_effective_date + 365, 'YYYY'),'06') LOOP
660 l_start_date := l_cur_start_date.start_date;
661 l_st_month := 'June';
662 l_end_month := 'November';
663 l_pay_month := 'next June';
664 END LOOP;
665 ELSE
666 FOR l_cur_start_date IN c_start_date(l_payroll_id,
667 to_char(p_effective_date + 31,'YYYY'),'12') LOOP
668 l_start_date := l_cur_start_date.start_date;
669 l_st_month := 'December';
670 l_end_month := 'May';
671 l_pay_month := 'next December';
672 END LOOP;
673 END IF; */-- IF to_number(to_char(p_effective_date,'MM'))
674 --End Bug# 8622486
675 /*Bug#6312182*/
676 -- neet to set based on effective date...
677 -- Bug 4673241 Added NOA's
678 IF p_which_eit = 'R' AND p_first_noa_code IN ('130','132','145','147','140','141','143') THEN
679 NULL;
680 --Begin Bug# 8622486
681 /*ELSIF p_agency_contrib_date <> l_start_date THEN
682 -- Raise Error message
683 hr_utility.set_message(8301,'GHR_38959_AGNCY_DATE_STRT_DATE');
684 hr_utility.set_message_token('ST_MONTH',l_st_month);
685 hr_utility.set_message_token('END_MONTH',l_end_month);
686 hr_utility.set_message_token('PAY_MONTH',l_pay_month);
687 hr_utility.raise_error;*/
688 --end Bug# 8622486
689 END IF;
690
691 IF g_debug = TRUE THEN
692 hr_utility.set_location('Get Payroll ID',1234);
693 END IF;
694
695
696 -- TSP Emp Contrib Elig date validation
697 IF p_emp_contrib_date IS NOT NULL THEN
698 IF p_noa_family_code = 'APP' AND p_first_noa_code NOT IN ('130','132','145','147') THEN
699 -- Raise Error message
700 hr_utility.set_message(8301,'GHR_38962_APP_EMP_ELIG_DATE');
701 hr_utility.raise_error;
702 ELSE
703 IF NVL(p_effective_date,TRUNC(SYSDATE)) <= p_emp_contrib_date THEN
704 -- Raise Error message
705 hr_utility.set_message(8301,'GHR_38951_BEN_ELIG_DATE');
706 hr_utility.set_message_token('BEN_ELIG_DATE','TSP Emp Contrib Elig date');
707 hr_utility.raise_error;
708 END IF;
709 -- Amount or Rate should be null
710 IF p_tsp_amount IS NOT NULL OR p_tsp_rate IS NOT NULL THEN
711 -- Raise Error message
712 hr_utility.set_message(8301,'GHR_38963_TSP_EMP_ELIG_DATE');
713 hr_utility.raise_error;
714 END IF;
715 END IF; -- IF l_noa_family_code = 'APP' THEN
716 END IF;
717 IF g_debug = TRUE THEN
718 hr_utility.set_location('IF p_rei_information18 IS NOT NULL',1234);
719 END IF;
720
721
722 END IF; -- IF p_which_eit = 'R' THEN
723 -- Bug # 13571004
724 IF (p_which_eit = 'E' AND p_passed_element = 'TSP Roth 401K') THEN
725 l_validate := TRUE;
726
727 IF (p_effective_date < to_date('01/01/2012','dd/mm/yyyy')) THEN
728 hr_utility.set_message(8301,'GHR_38347_TSP_ROTH_NOT_EFF');
729 hr_utility.raise_error;
730 END IF;
731
732 --- THis is commented as tsp status will be updated with Y after the validations of
733 --- TSP Roth
734 /*IF NVL(p_tsp_roth_401k_amt,0) > 0 OR NVL(p_tsp_roth_401k_rate,0) > 0 THEN
735 IF NVL(p_tsp_status,'X1') NOT IN ('Y') THEN
736 hr_utility.set_message(8301, 'GHR_38678_INCORRECT_STATUS');
737 hr_utility.raise_error;
738 END IF;
739 END IF;*/
740
741 IF p_tsp_roth_401k_amt IS NOT NULL AND p_tsp_roth_401k_rate IS NOT NULL THEN
742 hr_utility.set_message(8301,'GHR_38685_RATE_AMOUNT_COMBO');
743 hr_utility.raise_error;
744 END IF;
745 END IF;
746 -- Bug # 13571004
747
748
749
750 END validate_benefits;
751
752
753 PROCEDURE validate_create_element(
754 p_effective_date in date
755 ,p_assignment_id in number default null
756 ,p_validation_start_date in date
757 ,p_validation_end_date in date
758 ,p_element_entry_id in number
759 ,p_effective_start_date in date
760 ,p_effective_end_date in date
761 ,p_element_link_id in number default null
762 ,p_element_type_id in number default null
763 ) IS
764
765
766 --Bug #12553476 Modified c_element cursor to map the effective date comparison
767 CURSOR c_element(c_element_name pay_element_types_f.element_name%type,
768 c_assignment_id per_all_assignments_f.assignment_id%type,
769 c_effective_date pay_element_entries_f.effective_start_date%type,
770 c_business_group_id pay_element_links_f.business_group_id%type
771 ) is
772 SELECT a.element_name element_name,
773 b.name ipv_name,
774 f.input_value_id input_value_id,
775 e.effective_start_date effective_start_date,
776 e.effective_end_date effective_end_date,
777 e.element_entry_id element_entry_id,
778 e.assignment_id assignment_id,
779 e.object_version_number object_version_number,
780 f.element_entry_value_id element_entry_value_id,
781 f.screen_entry_value screen_entry_value
782 FROM pay_element_types_f a,
783 pay_input_values_f b,
784 pay_element_entries_f e,
785 pay_element_entry_values_f f,
786 pay_element_links_f g
787 WHERE a.element_type_id = b.element_type_id
788 AND e.element_type_id = a.element_type_id
789 AND f.element_entry_id = e.element_entry_id
790 AND f.input_value_id = b.input_value_id
791 AND g.element_type_id = a.element_type_id
792 AND c_effective_date between g.effective_start_date and g.effective_end_date
793 AND c_effective_date between a.effective_start_date and a.effective_end_date
794 AND c_effective_date between b.effective_start_date and b.effective_end_date
795 AND c_effective_date between f.effective_start_date and f.effective_end_date
796 AND g.business_group_id = c_business_group_id
797 --and e.effective_start_date = f.effective_start_date
798 --and e.effective_end_date = f.effective_end_date
799 and e.assignment_id = c_assignment_id
800 and a.element_name = c_element_name
801 AND c_effective_date BETWEEN e.effective_start_date AND e.effective_end_date;
802
803 CURSOR c_get_person_id(c_assignment_id per_all_assignments_f.assignment_id%type,
804 c_effective_date per_all_assignments_f.effective_start_date%type)
805 IS
806 SELECT person_id
807 FROM per_all_assignments_f asg
808 WHERE asg.assignment_id = c_assignment_id
809 AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
810
811 CURSOR c_element_name(c_element_link_id pay_element_links_f.element_link_id%type,
812 c_effective_date pay_element_links_f.effective_start_date%type,
813 c_business_group_id pay_element_links_f.business_group_id%type) IS
814 SELECT element_name
815 FROM pay_element_types_f pet, pay_element_links_f pel
816 where pet.element_type_id = pel.element_type_id
817 and c_effective_date between pel.effective_start_date and pel.effective_end_date
818 and pel.business_group_id = c_business_group_id
819 and pel.element_link_id = c_element_link_id;
820
821 l_rate number;
822 l_tsp_status varchar2(20);
823 l_tsp_status_date varchar2(20);
824 l_amount number;
825 l_business_group_id per_all_people_f.business_group_id%type;
826 l_session_date fnd_sessions.effective_date%type;
827
828 l_health_plan varchar2(20);
829 l_enrollment_option varchar2(20);
830 l_temps_total_cost varchar2(20);
831 l_pre_tax_waiver varchar2(20);
832 l_premium_rate varchar2(20);
833 l_per_benefit_info per_people_extra_info%rowtype;
834 l_person_id per_all_people_f.person_id%type;
835 l_new_element_name pay_element_types_f.element_name%type;
836 l_element_name pay_element_types_f.element_name%type;
837 l_ret_plan_name varchar2(200);
838 l_retirement_plan ghr_pa_requests.retirement_plan%type;
839 l_debug_mode BOOLEAN;
840
841 --12973371 Added TSP Roth Amount and Rate
842 l_roth_amount number;
843 l_roth_rate number;
844
845 l_effective_start_date DATE;
846
847
848 CURSOR c_get_tsp_roth_eff(p_assignment_id in number,
849 p_eff_dt in date,
850 p_bg_id in number)
851 IS
852 select ele.element_entry_id,
853 ele.object_version_number
854 from pay_element_types_f elt,
855 pay_element_links_f eli,
856 pay_element_entries_f ele
857 where ele.assignment_id = p_assignment_id
858 and upper(elt.element_name) = upper('TSP Roth 401K')
859 and elt.element_type_id = eli.element_type_id + 0
860 and elt.element_type_id = ele.element_type_id + 0
861 and ele.element_link_id = eli.element_link_id
862 and (elt.business_group_id is null or elt.business_group_id = p_bg_id)
863 and trunc(p_eff_dt) between elt.effective_start_date
864 and elt.effective_end_date
865 and trunc(p_eff_dt) between eli.effective_start_date
866 and eli.effective_end_date
867 and trunc(p_eff_dt) between ele.effective_start_date
868 and ele.effective_end_date;
869
870
871 l_eff_start_date date;
872 l_eff_end_date date;
873 l_roth_eff_date date;
874 l_old_eff_start_date date;
875 l_warning boolean;
876 l_tsp_amt number;
877 l_tsp_rate number;
878 l_old_tsp_status varchar2(10);
879 l_old_tsp_amt number;
880 l_old_tsp_rate number;
881
882
883 BEGIN
884 -- Initialization
885 IF ghr_utility.is_ghr = 'TRUE' THEN
886 IF g_debug = TRUE THEN
887 hr_utility.set_location('Entering validate_create_element',110);
888 END IF;
889
890 l_business_group_id := FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
891 l_debug_mode := FALSE;
892
893 IF g_debug = TRUE THEN
894 hr_utility.set_location('l_business_group_id -- ' || l_business_group_id,110);
895 END IF;
896
897 -- Check for the element Name
898 FOR l_cur_element_name IN c_element_name(p_element_link_id,p_effective_date,l_business_group_id) LOOP
899 l_new_element_name := l_cur_element_name.element_name;
900 END LOOP;
901
902 IF g_debug = TRUE THEN
903 hr_utility.set_location('New Element name -- ' || l_new_element_name,110);
904 END IF;
905
906 l_element_name := pqp_fedhr_uspay_int_utils.return_old_element_name(l_new_element_name,l_business_group_id,p_effective_date);
907
908 IF g_debug = TRUE THEN
909 hr_utility.set_location('Element name -- ' || l_element_name,110);
910 END IF;
911
912 l_ret_plan_name := pqp_fedhr_uspay_int_utils.return_new_element_name('Retirement Plan',l_business_group_id,p_effective_date,null);
913
914 FOR l_cur_element IN c_element(l_ret_plan_name,p_assignment_id, p_effective_date,l_business_group_id) LOOP
915 IF l_cur_element.ipv_name = 'Plan' then
916 l_retirement_plan := l_cur_element.screen_entry_value;
917 END IF;
918 END LOOP;
919
920 IF g_debug = TRUE THEN
921 hr_utility.set_location('Retirement plan -- ' || l_retirement_plan,110);
922 END IF;
923
924 IF l_element_name = 'TSP' THEN
925 FOR l_cur_element IN c_element(l_new_element_name,p_assignment_id, p_effective_date,l_business_group_id) LOOP
926 IF l_cur_element.ipv_name = 'Rate' then
927 l_rate := to_number(l_cur_element.screen_entry_value);
928 ELSIF l_cur_element.ipv_name = 'Status' then
929 l_tsp_status := substr(l_cur_element.screen_entry_value,1,1);
930 ELSIF l_cur_element.ipv_name = 'Status Date' then
931 l_tsp_status_date := fnd_date.canonical_to_date(l_cur_element.screen_entry_value);
932 ELSIF l_cur_element.ipv_name = 'Amount' then
933 l_amount := to_number(l_cur_element.screen_entry_value);
934 END IF;
935 END LOOP;
936
937 IF g_debug = TRUE THEN
938 hr_utility.set_location('Entering validation tsp',110);
939 END IF;
940
941 ghr_ben_validation.validate_benefits(
942 p_effective_date => p_effective_date
943 , p_which_eit => 'E'
944 , p_passed_element => 'TSP'
945 , p_tsp_amount => l_amount
946 , p_tsp_rate => l_rate
947 , p_tsp_status => l_tsp_status
948 , p_tsp_status_date => l_tsp_status_date
949 , p_retirement_plan => l_retirement_plan
950 , p_tsp_roth_401k_amt => l_roth_amount
951 , p_tsp_roth_401k_rate => l_roth_rate
952 );
953 --BUG # 13571004
954 --BUG # 13816139, 13816180 Modified to include the updation of TSP
955 --and TSP Roth in validate_create_element as this will get
956 --fire during updation/creation of element
957 IF NVL(l_tsp_status,'X1') = 'T' THEN
958 --BUG # 14618123 modified to get Amount and Rate
959 ghr_per_sum.get_element_details('TSP Roth 401K','TSP Roth 401K Amount',p_assignment_id,
960 p_effective_date,l_roth_amount,l_effective_start_date,l_business_group_id);
961 ghr_per_sum.get_element_details('TSP Roth 401K','TSP Roth 401K Rate',p_assignment_id,
962 p_effective_date,l_roth_rate,l_effective_start_date,l_business_group_id);
963 IF l_roth_amount IS NOT NULL OR l_roth_rate IS NOT NULL THEN
964 FOR rec_get_tsp_roth_eff in c_get_tsp_roth_eff(p_assignment_id => p_assignment_id ,
965 p_eff_dt => p_effective_date,
966 p_bg_id => l_business_group_id)
967 LOOP
968 py_element_entry_api.delete_element_entry(
969 p_datetrack_delete_mode => 'DELETE',
970 p_effective_date => (p_effective_date - 1),
971 p_element_entry_id => rec_get_tsp_roth_eff.element_entry_id,
972 p_object_version_number => rec_get_tsp_roth_eff.object_version_number,
973 p_effective_start_date => l_eff_start_date,
974 p_effective_end_date => l_eff_end_date,
975 p_delete_warning => l_warning);
976 END LOOP;
977 END IF;
978 END IF;
979 --BUG # 13571004
980
981
982
983
984
985
986
987
988 ELSIF l_element_name = 'Health Benefits' THEN
989 FOR l_cur_element IN c_element(l_new_element_name,p_assignment_id, p_effective_date,l_business_group_id) LOOP
990 IF l_cur_element.ipv_name = 'Enrollment' then
991 l_enrollment_option := l_cur_element.screen_entry_value;
992 ELSIF l_cur_element.ipv_name = 'Health Plan' then
993 l_health_plan := l_cur_element.screen_entry_value;
994 ELSIF l_cur_element.ipv_name = 'Temps Total Cost' then
995 l_temps_total_cost := l_cur_element.screen_entry_value;
996 ELSIF l_cur_element.ipv_name = 'Pre tax Waiver' then
997 l_pre_tax_waiver := l_cur_element.screen_entry_value;
998 ELSIF l_cur_element.ipv_name = 'Premium Rate' then
999 l_premium_rate := to_number(l_cur_element.screen_entry_value);
1000 END IF;
1001 END LOOP;
1002
1003 IF g_debug = TRUE THEN
1004 hr_utility.set_location('Entering validation fehb',110);
1005 END IF;
1006
1007 -- Validation part
1008 validate_benefits(
1009 p_effective_date => p_effective_date
1010 , p_which_eit => 'E'
1011 , p_passed_element => 'Health Benefits'
1012 , p_health_plan => l_health_plan
1013 , p_enrollment_option => l_enrollment_option
1014 , p_temps_total_cost => l_temps_total_cost
1015 , p_pre_tax_waiver => l_pre_tax_waiver
1016 );
1017 -- Bug # 13571004
1018 ELSIF l_element_name = 'TSP Roth 401K' THEN
1019 FOR l_cur_element IN c_element(l_new_element_name,p_assignment_id, p_effective_date,l_business_group_id) LOOP
1020 IF l_cur_element.ipv_name = 'TSP Roth 401K Amount' then
1021 l_roth_amount := to_number(l_cur_element.screen_entry_value);
1022 ELSIF l_cur_element.ipv_name = 'TSP Roth 401K Rate' then
1023 l_roth_rate := to_number(l_cur_element.screen_entry_value);
1024 END IF;
1025 l_roth_eff_date := l_cur_element.effective_start_date;
1026 END LOOP;
1027
1028 IF g_debug = TRUE THEN
1029 hr_utility.set_location('Entering validation tsp roth',110);
1030 END IF;
1031
1032 ghr_per_sum.get_element_details('TSP','Status',p_assignment_id,
1033 p_effective_date,l_tsp_status,l_effective_start_date,l_business_group_id);
1034
1035 ghr_ben_validation.validate_benefits(
1036 p_effective_date => p_effective_date
1037 , p_which_eit => 'E'
1038 , p_passed_element => 'TSP Roth 401K'
1039 , p_tsp_amount => null
1040 , p_tsp_rate => null
1041 , p_tsp_status => l_tsp_status
1042 , p_tsp_status_date => null
1043 , p_retirement_plan => null
1044 , p_tsp_roth_401k_amt => l_roth_amount
1045 , p_tsp_roth_401k_rate => l_roth_rate
1046 );
1047
1048 --BUG # 13718289
1049 --BUG # 13816139, 13816180 Modified to include the updation of TSP
1050 --and TSP Roth in validate_create_element as this will get
1051 -- fire during updation/creation of element
1052 IF NVL(l_roth_amount,0) > 0 OR NVL(l_roth_rate,0) > 0 THEN
1053 IF NVL(l_tsp_status,'@') <> 'Y' THEN
1054 ghr_per_sum.get_element_details(p_element_name => 'TSP'
1055 ,p_input_value_name => 'Amount'
1056 ,p_assignment_id => p_assignment_id
1057 ,p_effective_date => p_effective_date
1058 ,p_value => l_tsp_amt
1059 ,p_effective_start_date => l_effective_start_date
1060 ,p_business_group_id => l_business_group_id);
1061
1062
1063
1064 ghr_per_sum.get_element_details(p_element_name => 'TSP'
1065 ,p_input_value_name => 'Rate'
1066 ,p_assignment_id => p_assignment_id
1067 ,p_effective_date => p_effective_date
1068 ,p_value => l_tsp_rate
1069 ,p_effective_start_date => l_effective_start_date
1070 ,p_business_group_id => l_business_group_id);
1071
1072 ghr_element_api.process_sf52_element
1073 (p_assignment_id => p_assignment_id
1074 ,p_element_name => 'TSP'
1075 ,p_input_value_name1 => 'Amount'
1076 ,p_value1 => l_tsp_amt
1077 ,p_input_value_name2 => 'Rate'
1078 ,p_value2 => l_tsp_rate
1079 ,p_input_value_name6 => 'Status'
1080 ,p_value6 => 'Y'
1081 ,p_input_value_name7 => 'Status Date'
1082 ,p_value7 =>
1083 fnd_date.date_to_displaydate(p_effective_date)
1084 ,p_effective_date => p_effective_date
1085 ,p_process_warning => l_warning
1086 );
1087 END IF;
1088 --BUG # 13844994
1089 -- BUG # 14618123 commented as it will be handled in PLD to raise an error message
1090 -- If Roth amount and rate is available and status is set to Y then need to raise an error
1091 /* ELSIF NVL(l_roth_amount,'0') = '0' AND NVL(l_roth_rate,'0') = '0' THEN
1092 hr_utility.set_location('l_tsp_status'||l_tsp_status,100);
1093 hr_utility.set_location('l_tsp_amt'||l_tsp_amt,100);
1094 hr_utility.set_location('l_tsp_rate'||l_tsp_rate,100);
1095
1096 ghr_per_sum.get_element_details('TSP','Status',p_assignment_id,
1097 l_roth_eff_date,l_tsp_status,l_eff_start_date,l_business_group_id);
1098
1099 ghr_per_sum.get_element_details('TSP','Amount',p_assignment_id,
1100 l_roth_eff_date,l_tsp_amt,l_eff_start_date,l_business_group_id);
1101
1102 ghr_per_sum.get_element_details('TSP','Rate', p_assignment_id,
1103 l_roth_eff_date,l_tsp_rate,l_eff_start_date,l_business_group_id);
1104 hr_utility.set_location('l_tsp_status'||l_tsp_status,100);
1105 hr_utility.set_location('l_tsp_amt'||l_tsp_amt,100);
1106 hr_utility.set_location('l_tsp_rate'||l_tsp_rate,100);
1107 IF (l_tsp_amt is NULL and (NVL(l_tsp_rate,'0') = '0' or l_tsp_rate = '3')) and l_tsp_status = 'Y' THEN
1108 hr_utility.set_location('in loop1'||l_tsp_status,100);
1109 l_old_eff_start_date:= l_eff_start_date-1;
1110
1111 ghr_per_sum.get_element_details('TSP','Status',p_assignment_id,
1112 l_old_eff_start_date,l_old_tsp_status,l_eff_start_date,l_business_group_id);
1113
1114 ghr_per_sum.get_element_details('TSP','Amount',p_assignment_id,
1115 l_old_eff_start_date,l_old_tsp_amt,l_eff_start_date,l_business_group_id);
1116
1117 ghr_per_sum.get_element_details('TSP','Rate', p_assignment_id,
1118 l_old_eff_start_date,l_old_tsp_rate,l_eff_start_date,l_business_group_id);
1119
1120 hr_utility.set_location('l_old_tsp_status'||l_old_tsp_status,100);
1121 hr_utility.set_location('l_old_tsp_amt'||l_old_tsp_amt,100);
1122 hr_utility.set_location('l_old_tsp_rate'||l_old_tsp_rate,100);
1123
1124 IF l_old_tsp_status is NOT NULL or l_old_tsp_amt is NOT NULL or l_old_tsp_rate is NOT NULL THEN
1125 hr_utility.set_location('in loop2'||l_tsp_status,100);
1126 ghr_element_api.process_sf52_element(p_assignment_id => p_assignment_id
1127 ,p_element_name => 'TSP'
1128 ,p_input_value_name1 => 'Amount'
1129 ,p_value1 => l_old_tsp_amt
1130 ,p_input_value_name2 => 'Rate'
1131 ,p_value2 => l_old_tsp_rate
1132 ,p_input_value_name6 => 'Status'
1133 ,p_value6 => l_old_tsp_status
1134 ,p_input_value_name7 => 'Status Date'
1135 ,p_value7 => l_roth_eff_date
1136 ,p_effective_date => l_roth_eff_date
1137 ,p_process_warning => l_warning
1138 );
1139 ELSE
1140 hr_utility.set_location('in loop3'||l_tsp_status,100);
1141 ghr_element_api.process_sf52_element
1142 (p_assignment_id => p_assignment_id
1143 ,p_element_name => 'TSP'
1144 ,p_input_value_name1 => 'Amount'
1145 ,p_value1 => l_tsp_amt
1146 ,p_input_value_name2 => 'Rate'
1147 ,p_value2 => l_tsp_rate
1148 ,p_input_value_name6 => 'Status'
1149 ,p_value6 => 'T'
1150 ,p_input_value_name7 => 'Status Date'
1151 ,p_value7 => l_roth_eff_date
1152 ,p_effective_date => l_roth_eff_date
1153 ,p_process_warning => l_warning
1154 );
1155 END IF;
1156 END IF; */
1157 END IF;
1158 --BUG # 13844994
1159 --BUG # 13718289
1160 END IF; -- IF l_element_name = 'TSP
1161 END IF; -- IF ghr_utility.is_ghr = 'TRUE
1162
1163 END validate_create_element;
1164
1165 PROCEDURE validate_update_element(
1166 p_effective_date in date
1167 ,P_ASSIGNMENT_ID_O in number default null
1168 ,p_validation_start_date in date
1169 ,p_validation_end_date in date
1170 ,p_element_entry_id in number
1171 ,p_effective_start_date in date
1172 ,p_effective_end_date in date
1173 ,P_ELEMENT_LINK_ID_O in number default null
1174 ,P_ELEMENT_TYPE_ID_O in number default null
1175 ) IS
1176 BEGIN
1177 IF g_debug = TRUE THEN
1178 hr_utility.set_location('Entering validate_update_element',110);
1179 END IF;
1180 validate_create_element(
1181 p_effective_date => p_effective_date
1182 ,P_ASSIGNMENT_ID => P_ASSIGNMENT_ID_O
1183 ,p_validation_start_date => p_validation_start_date
1184 ,p_validation_end_date => p_validation_end_date
1185 ,p_element_entry_id => p_element_entry_id
1186 ,p_effective_start_date => p_effective_start_date
1187 ,p_effective_end_date => p_effective_end_date
1188 ,P_ELEMENT_LINK_ID => P_ELEMENT_LINK_ID_O
1189 ,P_ELEMENT_TYPE_ID => P_ELEMENT_TYPE_ID_O
1190 );
1191
1192 END validate_update_element;
1193
1194 PROCEDURE validate_create_personei(
1195 p_person_extra_info_id number,
1196 p_information_type in varchar2,
1197 p_person_id in number
1198 ) IS
1199 CURSOR c_assignment(c_person_id per_assignments_f.person_id%type,
1200 c_effective_date per_assignments_f.effective_start_date%type) IS
1201 SELECT assignment_type, assignment_id
1202 FROM per_all_assignments_f asg
1203 WHERE asg.person_id = c_person_id
1204 AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
1205
1206 CURSOR c_sess_date(c_session_id fnd_sessions.session_id%type)
1207 IS
1208 SELECT effective_date
1209 FROM fnd_sessions
1210 WHERE session_id = c_session_id;
1211
1212 CURSOR c_per_ei(c_per_extra_info_id per_people_extra_info.person_extra_info_id%type,
1213 c_information_type per_people_extra_info.information_type%type) IS
1214 SELECT *
1215 FROM per_people_extra_info ppei
1216 WHERE ppei.person_extra_info_id = c_per_extra_info_id
1217 AND ppei.information_type = c_information_type;
1218
1219 l_effective_date fnd_sessions.effective_date%type;
1220 l_session_id fnd_sessions.session_id%type;
1221 l_assignment_type per_all_assignments_f.assignment_type%type;
1222 l_assignment_id per_all_assignments_f.assignment_id%type;
1223 l_agency_contrib_date date;
1224 l_emp_contrib_date date;
1225 l_fers_elig_exp_date date;
1226 l_fegli_elig_exp_date date;
1227 l_date_temp_elig date;
1228 l_date_fehb_elig date;
1229 l_tsp_scd date;
1230 l_business_group_id per_all_people_f.business_group_id%type;
1231 l_asg_ei_data per_assignment_extra_info%rowtype;
1232 l_annuitant_indicator per_assignment_extra_info.aei_information5%type;
1233 l_session ghr_history_api.g_session_var_type;
1234 l_noa_id number;
1235 l_noa_family_code ghr_noa_families.noa_family_code%type;
1236
1237 -- Bug 4760226
1238 CURSOR c_noa_family_code(c_noa_id ghr_nature_of_actions.nature_of_action_id%type,
1239 c_effective_date ghr_nature_of_actions.date_from%type) IS
1240 SELECT noa_family_code
1241 FROM ghr_noa_families
1242 WHERE nature_of_action_id = c_noa_id
1243 AND c_effective_date BETWEEN NVL(start_date_active,to_date('01/01/1951','dd/mm/yyyy'))
1244 AND NVL(end_date_active,to_date('31/12/4712','dd/mm/yyyy'));
1245
1246 BEGIN
1247 IF ghr_utility.is_ghr = 'TRUE' AND p_information_type IN ('GHR_US_PER_BENEFIT_INFO','GHR_US_PER_SCD_INFORMATION') THEN
1248 IF g_debug = TRUE THEN
1249 hr_utility.set_location('Inside validate_create_personei',12);
1250 END IF;
1251 l_session_id := USERENV('sessionid');
1252 ghr_history_api.get_g_session_var(l_session);
1253
1254 IF l_session.pa_request_id IS NOT NULL THEN
1255 l_effective_date := l_session.date_effective;
1256 FOR l_cur_noa_code IN c_noa_family_code(l_session.noa_id, l_effective_date) LOOP
1257 l_noa_family_code := l_cur_noa_code.noa_family_code;
1258 END LOOP;
1259 ELSE
1260 FOR l_cur_sess_date IN c_sess_date(l_session_id) LOOP
1261 l_effective_date := l_cur_sess_date.effective_date;
1262 END LOOP;
1263 END IF;
1264
1265 IF g_debug = TRUE THEN
1266 hr_utility.set_location('eff.date ' || l_effective_date,12);
1267 END IF;
1268
1269 -- Fire only for Appt, Conv to appt and extension actions
1270 IF l_noa_family_code IN ('APPT','CONV_APPT','EXT_NTE')
1271 AND l_session.noa_id_correct IS NULL THEN
1272 -- Get Assignment type
1273 FOR l_asg_cur IN c_assignment(p_person_id,l_effective_date) LOOP
1274 l_assignment_type := l_asg_cur.assignment_type;
1275 l_assignment_id := l_asg_cur.assignment_id;
1276 END LOOP;
1277
1278 IF g_debug = TRUE THEN
1279 hr_utility.set_location('Assignment_type ' || l_assignment_type,12);
1280 hr_utility.set_location('Assignment_id ' || l_assignment_id,12);
1281 hr_utility.set_location('p_information_type ' || p_information_type,12);
1282 END IF;
1283
1284 -- Validation only if person is employee
1285 IF l_assignment_type = 'E' THEN
1286 FOR l_cur_per_ei IN c_per_ei(p_person_extra_info_id,p_information_type) LOOP
1287 IF p_information_type = 'GHR_US_PER_BENEFIT_INFO' THEN
1288 l_date_fehb_elig := fnd_date.canonical_to_date(l_cur_per_ei.pei_information4);
1289 l_date_temp_elig := fnd_date.canonical_to_date(l_cur_per_ei.pei_information5);
1290 l_fegli_elig_exp_date := fnd_date.canonical_to_date(l_cur_per_ei.pei_information3);
1291 l_fers_elig_exp_date := fnd_date.canonical_to_date(l_cur_per_ei.pei_information11);
1292 l_agency_contrib_date := fnd_date.canonical_to_date(l_cur_per_ei.pei_information14);
1293 l_emp_contrib_date := fnd_date.canonical_to_date(l_cur_per_ei.pei_information15);
1294 ELSIF p_information_type = 'GHR_US_PER_SCD_INFORMATION' THEN
1295 l_tsp_scd := fnd_date.canonical_to_date(l_cur_per_ei.pei_information6);
1296 END IF;
1297 END LOOP;
1298 -- Get Annuitant indicator
1299 ghr_history_fetch.fetch_asgei
1300 (p_assignment_id => l_assignment_id,
1301 p_information_type => 'GHR_US_ASG_SF52',
1302 p_date_effective => l_effective_date,
1303 p_asg_ei_data => l_asg_ei_data
1304 );
1305 l_annuitant_indicator := l_asg_ei_data.aei_information5;
1306 IF g_debug = TRUE THEN
1307 hr_utility.set_location('l_annuitant_indicator ' || l_annuitant_indicator,12);
1308 hr_utility.set_location('l_tsp_scd ' || to_char(l_tsp_scd,'dd/mm/yyyy'),12);
1309 END IF;
1310 -- Call Validation package
1311 validate_benefits(
1312 p_effective_date => l_effective_date
1313 , p_which_eit => 'P'
1314 , p_date_fehb_elig => l_date_fehb_elig
1315 , p_date_temp_elig => l_date_temp_elig
1316 , p_tsp_scd => l_tsp_scd
1317 , p_agency_contrib_date => l_agency_contrib_date
1318 , p_emp_contrib_date => l_emp_contrib_date
1319 , p_fegli_elig_exp_date => l_fegli_elig_exp_date
1320 , p_fers_elig_exp_date => l_fers_elig_exp_date
1321 , p_annuitant_indicator => l_annuitant_indicator);
1322 END IF; -- IF l_assignment_type = 'E' THEN
1323 END IF; -- IF l_noa_family_code IN ('APPT','CONV_APPT','EXT_NTE')
1324 END IF; -- IF ghr_utility.is_ghr
1325
1326 END validate_create_personei;
1327
1328
1329 end GHR_BEN_VALIDATION;
[Home] [Help]