1 PACKAGE BODY GHR_BEN_VALIDATION AS
2 /* $Header: ghbenval.pkb 120.11.12010000.2 2008/08/05 15:01:51 ubhat 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
35 IS
36 l_payroll_id ghr_pa_request_extra_info.rei_information3%type;
37 l_noa_family_code ghr_pa_requests.noa_family_code%type;
38 l_st_month VARCHAR2(20);
39 l_end_month VARCHAR2(20);
40 l_pay_month VARCHAR2(20);
41 l_start_date per_time_periods.start_date%type;
42 l_validate BOOLEAN;
43
44 CURSOR c_payroll_id(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
45 SELECT rei_information3 payroll_id
46 FROM ghr_pa_request_extra_info
47 WHERE pa_request_id = c_pa_request_id
48 AND information_type = 'GHR_US_PAR_PAYROLL_TYPE';
49
50 CURSOR c_start_date(c_payroll_id pay_payrolls_f.payroll_id%type, c_year varchar2, c_month varchar2) IS
51 SELECT min(start_date) start_date
52 FROM per_time_periods
53 WHERE payroll_id = c_payroll_id
54 AND TO_CHAR(start_date,'YYYY') = c_year
55 AND TO_CHAR(start_date,'MM') = c_month;
56
57 FUNCTION check_element_exist(p_assignment_id in number,
58 p_element_name varchar2,
59 p_effective_date date) RETURN BOOLEAN
60 IS
61 l_new_element_name varchar2(250);
62 l_business_group_id per_all_people_f.business_group_id%type;
63 l_element_type_id pay_element_types_f.element_type_id%type;
64 l_exists BOOLEAN;
65
66 CURSOR c_element_exist(c_assignment_id per_all_assignments_f.assignment_id%type,
67 c_effective_date per_all_assignments_f.effective_start_date%type,
68 c_element_type_id pay_element_types_f.element_type_id%type)
69 IS
70 SELECT 1
71 FROM pay_element_entries_f ele
72 WHERE ele.assignment_id = c_assignment_id
73 AND c_effective_date BETWEEN ele.effective_start_date AND ele.effective_end_date
74 AND element_type_id = c_element_type_id;
75
76 CURSOR c_element_type_id (c_element_name pay_element_types_f.element_name%type,
77 c_business_group_id pay_element_links_f.business_group_id%type,
78 c_effective_date per_all_assignments_f.effective_start_date%type)
79 IS
80 SELECT elt.element_type_id
81 FROM pay_element_links eli, pay_element_types_f elt
82 WHERE elt.element_type_id = eli.element_type_id
83 AND eli.business_group_id = c_business_group_id
84 AND elt.element_name = c_element_name;
85 BEGIN
86 l_exists := FALSE;
87 -- l_business_group_id := FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
88 FND_PROFILE.get('PER_BUSINESS_GROUP_ID', l_business_group_id);
89
90 IF g_debug = TRUE THEN
91 hr_utility.set_location('Entering check_element_exist',12);
92 hr_utility.set_location('l_business_group_id ' || l_business_group_id,12);
93 END IF;
94 l_new_element_name := pqp_fedhr_uspay_int_utils.return_new_element_name(p_element_name,l_business_group_id,p_effective_date,null);
95
96 IF g_debug = TRUE THEN
97 hr_utility.set_location('l_new_element_name ' || l_new_element_name,12);
98 END IF;
99
100 FOR l_cur_el_type IN c_element_type_id(l_new_element_name,l_business_group_id,p_effective_date) LOOP
101 l_element_type_id := l_cur_el_type.element_type_id;
102 END LOOP;
103
104 IF g_debug = TRUE THEN
105 hr_utility.set_location('l_element_type_id ' || l_element_type_id,12);
106 hr_utility.set_location('p_assignment_id ' || p_assignment_id,12);
107 END IF;
108
109 FOR l_cur_el_exists IN c_element_exist(p_assignment_id, p_effective_date, l_element_type_id) LOOP
110 l_exists := TRUE;
111 IF g_debug = TRUE THEN
112 hr_utility.set_location(l_new_element_name || 'element exists',12);
113 END IF;
114 END LOOP;
115
116 RETURN l_exists;
117 END check_element_exist;
118 BEGIN
119 -- Validation for Health Plan
120 -- If called from element entry
121 l_validate := TRUE;
122 hr_utility.set_location('which eit' || p_which_eit,12);
123 IF (p_which_eit = 'E' AND p_passed_element = 'Health Benefits') OR p_which_eit = 'R'
124 THEN
125 IF g_debug = TRUE THEN
126
127 hr_utility.set_location('p_health_plan' || p_health_plan,12);
128 hr_utility.set_location('p_noa_family_code' || p_noa_family_code,12);
129 END IF;
130
131 IF p_which_eit = 'R' AND p_noa_family_code IN ('CONV_APP','EXT_NTE') THEN
132 IF p_health_plan IS NULL AND
133 p_enrollment_option IS NULL AND
134 p_temps_total_cost IS NULL THEN
135 -- Check if element exists already
136 l_validate := NOT(check_element_exist(p_assignment_id => p_assignment_id,
137 p_element_name => 'Health Benefits',
138 p_effective_date => p_effective_date));
139 END IF;
140
141 END IF; -- IF p_which_eit = 'R' AND p_no
142
143 IF l_validate = TRUE THEN
144 IF p_health_plan IS NULL THEN
145 hr_utility.set_message(8301,'GHR_38942_HB_NULL_PLAN_ENROLL');
146 hr_utility.raise_error;
147 END IF;
148
149 -- Enrollment Option
150 IF p_enrollment_option IS NULL THEN
151 hr_utility.set_message(8301,'GHR_38942_HB_NULL_PLAN_ENROLL');
152 hr_utility.raise_error;
153 NULL;
154 ELSE
155 IF p_health_plan = 'ZZ' AND p_enrollment_option NOT IN('W','X','Y','Z') THEN
156 hr_utility.set_message(8301,'GHR_38950_FEHB_INV_PLAN_OPTION');
157 hr_utility.raise_error;
158 END IF;
159
160 IF p_health_plan <> 'ZZ' AND p_enrollment_option NOT IN('1','2','4','5') THEN
161 hr_utility.set_message(8301,'GHR_38960_FEHB_NOT_ZZ');
162 hr_utility.raise_error;
163 END IF; -- IF NVL(l_health_plan,hr
164 END IF; -- IF l_enrollment_option IS NULL T
165
166 -- Pretax waiver Validation
167 IF p_health_plan = 'ZZ' AND p_pre_tax_waiver = 'Y' THEN
168 hr_utility.set_message(8301,'GHR_38956_PRE_TAX_ZZ_PLAN');
169 hr_utility.raise_error;
170 END IF;
171 END IF; -- IF l_validate = TRUE T
172 END IF; -- IF (p_which_eit = 'E' AND
173
174 IF (p_which_eit = 'E' AND p_passed_element = 'TSP') OR p_which_eit = 'R' THEN
175 l_validate := TRUE;
176 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') THEN
177 IF p_tsp_amount IS NOT NULL OR p_tsp_rate IS NOT NULL THEN
178 hr_utility.set_message(8301,'GHR_38958_TSP_AMT_NOT_FERS');
179 hr_utility.raise_error;
180 END IF;
181
182 -- TSP Status should be null
183 IF p_tsp_status IS NOT NULL THEN
184 hr_utility.set_message(8301,'GHR_38958_TSP_AMT_NOT_FERS');
185 hr_utility.raise_error;
186 END IF;
187 END IF; -- IF NVL(l_retirement_plan,hr_api.g_v
188
189 IF p_which_eit = 'E' THEN
190 -- TSP Status should not be null
191 IF p_tsp_status IS NULL THEN
192 hr_utility.set_message(8301,'GHR_38976_TSP_STS_REQD');
193 hr_utility.raise_error;
194 END IF;
195 END IF;
196
197 IF g_debug = TRUE THEN
198 hr_utility.set_location('If Retirement plan is not in FERS or CSRS ',1234);
199 END IF;
200
201 -- Either only Amount should be entered or Rate...
202 IF p_tsp_amount IS NOT NULL AND p_tsp_rate IS NOT NULL THEN
203 hr_utility.set_message(8301,'GHR_38685_RATE_AMOUNT_COMBO');
204 hr_utility.raise_error;
205 END IF;
206
207 -- Status must be 'Y' or 'W'
208 IF p_tsp_amount IS NOT NULL OR p_tsp_rate IS NOT NULL THEN
209 IF NVL(p_tsp_status,hr_api.g_varchar2) NOT IN ('Y','W') THEN
210 hr_utility.set_message(8301,'GHR_38678_INCORRECT_STATUS');
211 hr_utility.raise_error;
212 END IF;
213 END IF;
214
215 IF g_debug = TRUE THEN
216 hr_utility.set_location('p_tsp_status' || p_tsp_status,1234);
217 END IF;
218
219 IF p_tsp_status IN ('Y','W') THEN
220 -- Either Amount or Rate should be entered
221 IF p_tsp_amount IS NULL AND p_tsp_rate IS NULL THEN
222 hr_utility.set_message(8301,'GHR_38677_INV_RATE_OR_STATUS');
223 hr_utility.raise_error;
224 END IF;
225 END IF;
226
227
228 -- TSP Status date should be entered if Status is entered
229 -- Status Date should not be blank
230 IF p_tsp_status IS NOT NULL THEN
231 IF p_tsp_status_date IS NULL THEN
232 hr_utility.set_message(8301,'GHR_38675_INVALID_STATUS_DATE');
233 hr_utility.raise_error;
234 END IF;
235 ELSE
236 IF p_tsp_status_date IS NOT NULL THEN
237 hr_utility.set_message(8301,'GHR_38676_INVALID_STATUS');
238 hr_utility.raise_error;
239 END IF;
240 END IF;
241
242 IF g_debug = TRUE THEN
243 hr_utility.set_location('TSP Status date' ,1234);
244 END IF;
245
246
247 -- 2.2.2.1.6. If retirement Plan is 2, 4, or 5 and user has entered TSP information, provide error:
248 -- "TSP information is not appropriate for retirement plans 2, 4, or 5.
249 -- Please remove any TSP values from the Benefits EIT."
250 IF p_retirement_plan IN ('2','4','5') THEN
251 IF p_tsp_amount IS NOT NULL OR
252 p_tsp_rate IS NOT NULL OR
253 p_tsp_status IS NOT NULL OR
254 p_tsp_status_date IS NOT NULL THEN
255 -- Raise Error message
256 hr_utility.set_message(8301,'GHR_38965_TSP_OTH');
257 hr_utility.raise_error;
258 END IF;
259 END IF; -- IF p_retirement_plan IN ('2','4','5') THEN
260 IF g_debug = TRUE THEN
261 hr_utility.set_location('If retirement Plan is 2, 4, or 5',1234);
262 END IF;
263 END IF; -- IF (p_which_eit = 'E' AND p_passed_element = 'Health Benefits')
264
265 IF p_which_eit = 'R' OR p_which_eit = 'P' THEN
266 -- Validation for FEHB eligibility
267 IF g_debug = TRUE THEN
268 hr_utility.set_location('p_date_fehb_elig ' || to_char(p_date_fehb_elig,'dd/mm/yyyy') ,1234);
269 END IF;
270
271 IF p_date_fehb_elig IS NOT NULL THEN
272 IF p_date_fehb_elig <= p_effective_date THEN
273 hr_utility.set_message(8301,'GHR_38951_BEN_ELIG_DATE');
274 hr_utility.set_message_token('BEN_ELIG_DATE','FEHB Eligibility Expiration Date');
275 hr_utility.raise_error;
276 END IF;
277 IF p_first_noa_code IN ('115','122','149','171','515','522','549','571') THEN
278 hr_utility.set_message(8301,'GHR_38952_FEHB_ELIG_FOR_TMP');
279 hr_utility.raise_error;
280 END IF;
281 END IF;
282
283 IF g_debug = TRUE THEN
284 hr_utility.set_location('p_date_temp_elig ' || to_char(p_date_temp_elig,'dd/mm/yyyy') ,1234);
285 END IF;
286 -- Date Temp Eligibility Expires - Validation
287 IF p_date_temp_elig IS NOT NULL THEN
288 IF p_first_noa_code IS NOT NULL THEN
289 IF p_first_noa_code IN ('115','122','149','171','515','522','549','571') AND p_tenure = '0' THEN
290 IF p_date_temp_elig <= p_effective_date THEN
291 hr_utility.set_message(8301,'GHR_38953_TMP_ELIG_FUT_DATE');
292 hr_utility.raise_error;
293 END IF;
294 END IF;
295 -- If tenure code is not 0, then value should be null
296 IF p_first_noa_code IN ('115','122','149','171','515','522','549','571') AND NVL(p_tenure,hr_api.g_varchar2) <> '0' THEN
297 hr_utility.set_message(8301,'GHR_38954_TMP_ELIG_BLANK');
298 hr_utility.raise_error;
299 END IF;
300 -- This field is valid only for Temp. appointments. else throw error.
301 -- Bug 4668813
302 hr_utility.set_location('p_first_noa_code'|| p_first_noa_code,223);
303
304 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
305 hr_utility.set_message(8301,'GHR_38955_TMP_ELIG_FOR_TMP');
306 hr_utility.raise_error;
307 END IF;
308 ELSIF p_which_eit = 'P' THEN
309 IF p_date_temp_elig <= p_effective_date THEN
310 hr_utility.set_message(8301,'GHR_38953_TMP_ELIG_FUT_DATE');
311 hr_utility.raise_error;
312 END IF;
313 END IF; -- IF p_first_noa_code IS NOT NULL THEN
314 END IF; -- IF p_rei_information4 IS NOT
315
316 --
317 IF p_date_temp_elig IS NOT NULL AND p_date_temp_elig > NVL(p_effective_date,TRUNC(SYSDATE)) THEN
318 IF p_health_plan IS NOT NULL THEN
319 IF p_health_plan <> 'ZZ' AND p_enrollment_option <> 'Z' THEN
320 hr_utility.set_message(8301,'GHR_38964_FEHB_TMP_PLAN');
321 hr_utility.raise_error;
322 END IF;
323 END IF;
324 END IF; -- IF p_date_temp_elig IS NOT NULL
325
326 IF g_debug = TRUE THEN
327 hr_utility.set_location('p_fegli_elig_exp_date ' || to_char(p_fegli_elig_exp_date,'dd/mm/yyyy') ,1234);
328 END IF;
329
330 -- FEGLI Eligibility Expiration Validation
331 IF p_fegli_elig_exp_date IS NOT NULL THEN
332 IF p_fegli_elig_exp_date <= NVL(p_effective_date,TRUNC(SYSDATE)) THEN
333 hr_utility.set_message(8301,'GHR_38951_BEN_ELIG_DATE');
334 hr_utility.set_message_token('BEN_ELIG_DATE','FEGLI Eligibility Expiration Date');
335 hr_utility.raise_error;
336 END IF;
337 END IF; -- IF p_fegli_elig_exp_date IS NOT NULL THEN
338
339 IF g_debug = TRUE THEN
340 hr_utility.set_location('-- FEGLI Eligibility Expiration Validatio',1234);
341 END IF;
342 -- FERS Eligibility Expiration Validation
343 IF p_fers_elig_exp_date IS NOT NULL THEN
344 IF p_fers_elig_exp_date <= NVL(p_effective_date,TRUNC(SYSDATE)) THEN
345 hr_utility.set_message(8301,'GHR_38951_BEN_ELIG_DATE');
346 hr_utility.set_message_token('BEN_ELIG_DATE','FERS Eligibility Expiration Date');
347 hr_utility.raise_error;
348 END IF;
349 END IF; -- IF p_fers_elig_exp_date IS NOT NULL THEN
350 IF g_debug = TRUE THEN
351 hr_utility.set_location('-- FERS Eligibility Expiration Validation',1234);
352 END IF;
353
354 IF p_which_eit = 'R' AND p_noa_family_code IN ('CONV_APP','EXT_NTE') THEN
355 IF p_tsp_amount IS NULL AND
356 p_tsp_rate IS NULL AND
357 p_tsp_status IS NULL AND
358 p_tsp_status_date IS NULL THEN
359 -- Check if element exists already
360 l_validate := NOT(check_element_exist(p_assignment_id => p_assignment_id,
361 p_element_name => 'TSP',
362 p_effective_date => p_effective_date));
363 END IF;
364
365 END IF; -- IF p_which_eit = 'R' AND p_no
366
367 IF l_validate = TRUE THEN
368 IF p_retirement_plan IN ('D','K','L','M','N','P') THEN
369 IF LTRIM(p_tsp_scd) IS NULL THEN
370 -- Raise Error message
371 hr_utility.set_message(8301,'GHR_38957_TSP_FERS');
372 hr_utility.raise_error;
373 END IF;
374 END IF; -- IF p_retirement_plan IN ('D','K','L'...
375 END IF;
376
377 IF p_tsp_scd IS NOT NULL AND p_retirement_plan IS NOT NULL THEN
378 IF p_retirement_plan NOT IN ('D','K','L','M','N','P') THEN
379 -- Raise Error message
380 hr_utility.set_message(8301,'GHR_38392_NON_TSP_FERS');--Bug# 4769233
381 hr_utility.raise_error;
382 END IF;
383 END IF;
384
385 IF p_retirement_plan NOT IN ('D','K','L','M','N','P','1','3','6','C','E','F','G','R','T','H','W') THEN
386 -- Emp Contrib Elig date should be Null
387 IF p_emp_contrib_date IS NOT NULL THEN
388 hr_utility.set_message(8301,'GHR_38958_TSP_AMT_NOT_FERS');
389 hr_utility.raise_error;
390 END IF;
391 END IF; -- IF NVL(l_retirement_plan,hr_api.g_v
392
393 IF g_debug = TRUE THEN
394 hr_utility.set_location('If Retirement plan is not in FERS or CSRS ',1234);
395 END IF;
396
397 -- IF TSP status is I then the date must be future.
398 IF p_tsp_status = 'I' AND NVL(p_effective_date, TRUNC(SYSDATE)) > p_agency_contrib_date THEN
399 hr_utility.set_message(8301,'GHR_38680_INV_AGENCY_CONTRIB');
400 hr_utility.raise_error;
401 END IF;
402
403 IF g_debug = TRUE THEN
404 hr_utility.set_location(' IF TSP status is I',1234);
405 END IF;
406
407
408
409
410
411 -- 2.2.2.1.6. If retirement Plan is 2, 4, or 5 and user has entered TSP information, provide error:
412 -- "TSP information is not appropriate for retirement plans 2, 4, or 5.
413 -- Please remove any TSP values from the Benefits EIT."
414 IF p_which_eit = 'R' THEN
415 IF (p_retirement_plan IN ('2','4','5') AND p_annuitant_indicator IN ('2','3','9') )
416 OR p_retirement_plan IN ('J','X') THEN
417 IF p_agency_contrib_date IS NOT NULL OR
418 p_emp_contrib_date IS NOT NULL THEN
419 -- Raise Error message
420 hr_utility.set_message(8301,'GHR_38965_TSP_OTH');
421 hr_utility.raise_error;
422 END IF;
423 END IF; -- IF p_retirement_plan IN ('2','4','5') THEN
424 END IF;
425
426 IF g_debug = TRUE THEN
427 hr_utility.set_location('If retirement Plan is 2, 4, or 5',1234);
428 END IF;
429
430 IF p_which_eit = 'R' THEN
431 -- If Agency or Emp contrib dates are entered, then status cannot be null
432 IF p_agency_contrib_date IS NOT NULL OR p_emp_contrib_date IS NOT NULL THEN
433 IF p_tsp_status IS NULL THEN
434 hr_utility.set_message(8301,'GHR_38679_BLANK_STATUS');
435 hr_utility.raise_error;
436 END IF;
437 END IF;
438
439 -- Bug 4691271 and 4687755
440 -- If FERS, the value should be not null. Else it should be null.
441 IF p_retirement_plan IN ('D','K','L','M','N','P') THEN
442 IF p_agency_contrib_date IS NULL THEN -- Bug 4693453
443 hr_utility.set_message(8301,'GHR_38977_TSP_AGNCY_DATE_REQD');
444 hr_utility.raise_error;
445 END IF;
446 ELSIF p_agency_contrib_date IS NOT NULL THEN
447 hr_utility.set_message(8301,'GHR_38961_NOT_FERS_AGNCY_DATE');
448 hr_utility.raise_error;
449 END IF;
450 END IF;
451
452 -- 2.2.1.1.3. AND 2.2.2.1.3 TSP Agncy Contrib Elig Date is required for all FERS covered employees.
453 -- If the employee's retirement plan is D, K, L, M, N, or P and the defaulted value is removed by the user
454 -- and a valid date is not entered provide error message: "TSP Agency Contrib Elig Date is required for all FERS employees.
455 -- Please enter a valid date."
456
457 -- First find out the valid date
458 -- Get Payroll ID
459 FOR l_cur_payroll_id IN c_payroll_id(p_pa_request_id) LOOP
460 l_payroll_id := l_cur_payroll_id.payroll_id;
461 END LOOP;
462 /*Bug#6312182*/
463 /*Start: Commented the code as the logic for determining the eligibility date has been changed.
464 IF to_number(to_char(p_effective_date,'MM')) BETWEEN 1 AND 6 THEN
465 FOR l_cur_start_date IN c_start_date(l_payroll_id,to_char(p_effective_date,'YYYY'), '12') LOOP
466 l_start_date := l_cur_start_date.start_date;
467 END LOOP;
468 l_st_month := 'January';
469 l_end_month := 'June';
470 l_pay_month := 'December';
471 ELSE
472 FOR l_cur_start_date IN c_start_date(l_payroll_id,to_char(p_effective_date+365,'YYYY'), '06') LOOP
473 l_start_date := l_cur_start_date.start_date;
474 END LOOP;
475 l_st_month := 'July';
476 l_end_month := 'December';
477 l_pay_month := 'June';
478 END IF; -- IF to_number(to_char(l_effective_date,'MM'))
479 :End*/
480 IF to_number(to_char(p_effective_date,'MM')) BETWEEN 6 AND 11 THEN
481 FOR l_cur_start_date IN c_start_date(l_payroll_id,
482 to_char(p_effective_date + 365, 'YYYY'),'06') LOOP
483 l_start_date := l_cur_start_date.start_date;
484 l_st_month := 'June';
485 l_end_month := 'November';
486 l_pay_month := 'next June';
487 END LOOP;
488 ELSE
489 FOR l_cur_start_date IN c_start_date(l_payroll_id,
490 to_char(p_effective_date + 31,'YYYY'),'12') LOOP
491 l_start_date := l_cur_start_date.start_date;
492 l_st_month := 'December';
493 l_end_month := 'May';
494 l_pay_month := 'next December';
495 END LOOP;
496 END IF; -- IF to_number(to_char(p_effective_date,'MM'))
497 /*Bug#6312182*/
498 -- neet to set based on effective date...
499 -- Bug 4673241 Added NOA's
500 IF p_which_eit = 'R' AND p_first_noa_code IN ('130','132','145','147','140','141','143') THEN
501 NULL;
502 ELSIF p_agency_contrib_date <> l_start_date THEN
503 -- Raise Error message
504 hr_utility.set_message(8301,'GHR_38959_AGNCY_DATE_STRT_DATE');
505 hr_utility.set_message_token('ST_MONTH',l_st_month);
506 hr_utility.set_message_token('END_MONTH',l_end_month);
507 hr_utility.set_message_token('PAY_MONTH',l_pay_month);
508 hr_utility.raise_error;
509 END IF;
510
511 IF g_debug = TRUE THEN
512 hr_utility.set_location('Get Payroll ID',1234);
513 END IF;
514
515
516 -- TSP Emp Contrib Elig date validation
517 IF p_emp_contrib_date IS NOT NULL THEN
518 IF p_noa_family_code = 'APP' AND p_first_noa_code NOT IN ('130','132','145','147') THEN
519 -- Raise Error message
520 hr_utility.set_message(8301,'GHR_38962_APP_EMP_ELIG_DATE');
521 hr_utility.raise_error;
522 ELSE
523 IF NVL(p_effective_date,TRUNC(SYSDATE)) <= p_emp_contrib_date THEN
524 -- Raise Error message
525 hr_utility.set_message(8301,'GHR_38951_BEN_ELIG_DATE');
526 hr_utility.set_message_token('BEN_ELIG_DATE','TSP Emp Contrib Elig date');
527 hr_utility.raise_error;
528 END IF;
529 -- Amount or Rate should be null
530 IF p_tsp_amount IS NOT NULL OR p_tsp_rate IS NOT NULL THEN
531 -- Raise Error message
532 hr_utility.set_message(8301,'GHR_38963_TSP_EMP_ELIG_DATE');
533 hr_utility.raise_error;
534 END IF;
535 END IF; -- IF l_noa_family_code = 'APP' THEN
536 END IF;
537 IF g_debug = TRUE THEN
538 hr_utility.set_location('IF p_rei_information18 IS NOT NULL',1234);
539 END IF;
540
541
542 END IF; -- IF p_which_eit = 'R' THEN
543
544
545
546
547 END validate_benefits;
548
549
550 PROCEDURE validate_create_element(
551 p_effective_date in date
552 ,p_assignment_id in number default null
553 ,p_validation_start_date in date
554 ,p_validation_end_date in date
555 ,p_element_entry_id in number
556 ,p_effective_start_date in date
557 ,p_effective_end_date in date
558 ,p_element_link_id in number default null
559 ,p_element_type_id in number default null
560 ) IS
561
562
563 CURSOR c_element(c_element_name pay_element_types_f.element_name%type,
564 c_assignment_id per_all_assignments_f.assignment_id%type,
565 c_effective_date pay_element_entries_f.effective_start_date%type,
566 c_business_group_id pay_element_links_f.business_group_id%type
567 ) is
568 SELECT a.element_name element_name,
569 b.name ipv_name,
570 f.input_value_id input_value_id,
571 e.effective_start_date effective_start_date,
572 e.effective_end_date effective_end_date,
573 e.element_entry_id element_entry_id,
574 e.assignment_id assignment_id,
575 e.object_version_number object_version_number,
576 f.element_entry_value_id element_entry_value_id,
577 f.screen_entry_value screen_entry_value
578 FROM pay_element_types_f a,
579 pay_input_values_f b,
580 pay_element_entries_f e,
581 pay_element_entry_values_f f,
582 pay_element_links_f g
583 WHERE a.element_type_id = b.element_type_id
584 AND e.element_type_id = a.element_type_id
585 AND f.element_entry_id = e.element_entry_id
586 AND f.input_value_id = b.input_value_id
587 AND g.element_type_id = a.element_type_id
588 AND c_effective_date between g.effective_start_date and g.effective_end_date
589 AND c_effective_date between a.effective_start_date and a.effective_end_date
590 AND c_effective_date between b.effective_start_date and b.effective_end_date
591 AND g.business_group_id = c_business_group_id
592 --and e.effective_start_date = f.effective_start_date
593 --and e.effective_end_date = f.effective_end_date
594 and e.assignment_id = c_assignment_id
595 and a.element_name = c_element_name
596 AND c_effective_date BETWEEN e.effective_start_date AND e.effective_end_date;
597
598 CURSOR c_get_person_id(c_assignment_id per_all_assignments_f.assignment_id%type,
599 c_effective_date per_all_assignments_f.effective_start_date%type)
600 IS
601 SELECT person_id
602 FROM per_all_assignments_f asg
603 WHERE asg.assignment_id = c_assignment_id
604 AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
605
606 CURSOR c_element_name(c_element_link_id pay_element_links_f.element_link_id%type,
607 c_effective_date pay_element_links_f.effective_start_date%type,
608 c_business_group_id pay_element_links_f.business_group_id%type) IS
609 SELECT element_name
610 FROM pay_element_types_f pet, pay_element_links_f pel
611 where pet.element_type_id = pel.element_type_id
612 and c_effective_date between pel.effective_start_date and pel.effective_end_date
613 and pel.business_group_id = c_business_group_id
614 and pel.element_link_id = c_element_link_id;
615
616 l_rate number;
617 l_tsp_status varchar2(20);
618 l_tsp_status_date varchar2(20);
619 l_amount number;
620 l_business_group_id per_all_people_f.business_group_id%type;
621 l_session_date fnd_sessions.effective_date%type;
622
623 l_health_plan varchar2(20);
624 l_enrollment_option varchar2(20);
625 l_temps_total_cost varchar2(20);
626 l_pre_tax_waiver varchar2(20);
627 l_premium_rate varchar2(20);
628 l_per_benefit_info per_people_extra_info%rowtype;
629 l_person_id per_all_people_f.person_id%type;
630 l_new_element_name pay_element_types_f.element_name%type;
631 l_element_name pay_element_types_f.element_name%type;
632 l_ret_plan_name varchar2(200);
633 l_retirement_plan ghr_pa_requests.retirement_plan%type;
634 l_debug_mode BOOLEAN;
635 BEGIN
636 -- Initialization
637 IF ghr_utility.is_ghr = 'TRUE' THEN
638 IF g_debug = TRUE THEN
639 hr_utility.set_location('Entering validate_create_element',110);
640 END IF;
641
642 l_business_group_id := FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
643 l_debug_mode := FALSE;
644
645 IF g_debug = TRUE THEN
646 hr_utility.set_location('l_business_group_id -- ' || l_business_group_id,110);
647 END IF;
648
649 -- Check for the element Name
650 FOR l_cur_element_name IN c_element_name(p_element_link_id,p_effective_date,l_business_group_id) LOOP
651 l_new_element_name := l_cur_element_name.element_name;
652 END LOOP;
653
654 IF g_debug = TRUE THEN
655 hr_utility.set_location('New Element name -- ' || l_new_element_name,110);
656 END IF;
657
658 l_element_name := pqp_fedhr_uspay_int_utils.return_old_element_name(l_new_element_name,l_business_group_id,p_effective_date);
659
660 IF g_debug = TRUE THEN
661 hr_utility.set_location('Element name -- ' || l_element_name,110);
662 END IF;
663
664 l_ret_plan_name := pqp_fedhr_uspay_int_utils.return_new_element_name('Retirement Plan',l_business_group_id,p_effective_date,null);
665
666 FOR l_cur_element IN c_element(l_ret_plan_name,p_assignment_id, p_effective_date,l_business_group_id) LOOP
667 IF l_cur_element.ipv_name = 'Plan' then
668 l_retirement_plan := l_cur_element.screen_entry_value;
669 END IF;
670 END LOOP;
671
672 IF g_debug = TRUE THEN
673 hr_utility.set_location('Retirement plan -- ' || l_retirement_plan,110);
674 END IF;
675
676 IF l_element_name = 'TSP' THEN
677 FOR l_cur_element IN c_element(l_new_element_name,p_assignment_id, p_effective_date,l_business_group_id) LOOP
678 IF l_cur_element.ipv_name = 'Rate' then
679 l_rate := to_number(l_cur_element.screen_entry_value);
680 ELSIF l_cur_element.ipv_name = 'Status' then
681 l_tsp_status := substr(l_cur_element.screen_entry_value,1,1);
682 ELSIF l_cur_element.ipv_name = 'Status Date' then
683 l_tsp_status_date := fnd_date.canonical_to_date(l_cur_element.screen_entry_value);
684 ELSIF l_cur_element.ipv_name = 'Amount' then
685 l_amount := to_number(l_cur_element.screen_entry_value);
686 END IF;
687 END LOOP;
688
689 IF g_debug = TRUE THEN
690 hr_utility.set_location('Entering validation tsp',110);
691 END IF;
692
693 ghr_ben_validation.validate_benefits(
694 p_effective_date => p_effective_date
695 , p_which_eit => 'E'
696 , p_passed_element => 'TSP'
697 , p_tsp_amount => l_amount
698 , p_tsp_rate => l_rate
699 , p_tsp_status => l_tsp_status
700 , p_tsp_status_date => l_tsp_status_date
701 , p_retirement_plan => l_retirement_plan
702 );
703 ELSIF l_element_name = 'Health Benefits' THEN
704 FOR l_cur_element IN c_element(l_new_element_name,p_assignment_id, p_effective_date,l_business_group_id) LOOP
705 IF l_cur_element.ipv_name = 'Enrollment' then
706 l_enrollment_option := l_cur_element.screen_entry_value;
707 ELSIF l_cur_element.ipv_name = 'Health Plan' then
708 l_health_plan := l_cur_element.screen_entry_value;
709 ELSIF l_cur_element.ipv_name = 'Temps Total Cost' then
710 l_temps_total_cost := l_cur_element.screen_entry_value;
711 ELSIF l_cur_element.ipv_name = 'Pre tax Waiver' then
712 l_pre_tax_waiver := l_cur_element.screen_entry_value;
713 ELSIF l_cur_element.ipv_name = 'Premium Rate' then
714 l_premium_rate := to_number(l_cur_element.screen_entry_value);
715 END IF;
716 END LOOP;
717
718 IF g_debug = TRUE THEN
719 hr_utility.set_location('Entering validation fehb',110);
720 END IF;
721
722 -- Validation part
723 validate_benefits(
724 p_effective_date => p_effective_date
725 , p_which_eit => 'E'
726 , p_passed_element => 'Health Benefits'
727 , p_health_plan => l_health_plan
728 , p_enrollment_option => l_enrollment_option
729 , p_temps_total_cost => l_temps_total_cost
730 , p_pre_tax_waiver => l_pre_tax_waiver
731 );
732
733 END IF; -- IF l_element_name = 'TSP
734 END IF; -- IF ghr_utility.is_ghr = 'TRUE
735
736 END validate_create_element;
737
738 PROCEDURE validate_update_element(
739 p_effective_date in date
740 ,P_ASSIGNMENT_ID_O in number default null
741 ,p_validation_start_date in date
742 ,p_validation_end_date in date
743 ,p_element_entry_id in number
744 ,p_effective_start_date in date
745 ,p_effective_end_date in date
746 ,P_ELEMENT_LINK_ID_O in number default null
747 ,P_ELEMENT_TYPE_ID_O in number default null
748 ) IS
749 BEGIN
750 IF g_debug = TRUE THEN
751 hr_utility.set_location('Entering validate_update_element',110);
752 END IF;
753 validate_create_element(
754 p_effective_date => p_effective_date
755 ,P_ASSIGNMENT_ID => P_ASSIGNMENT_ID_O
756 ,p_validation_start_date => p_validation_start_date
757 ,p_validation_end_date => p_validation_end_date
758 ,p_element_entry_id => p_element_entry_id
759 ,p_effective_start_date => p_effective_start_date
760 ,p_effective_end_date => p_effective_end_date
761 ,P_ELEMENT_LINK_ID => P_ELEMENT_LINK_ID_O
762 ,P_ELEMENT_TYPE_ID => P_ELEMENT_TYPE_ID_O
763 );
764
765 END validate_update_element;
766
767 PROCEDURE validate_create_personei(
768 p_person_extra_info_id number,
769 p_information_type in varchar2,
770 p_person_id in number
771 ) IS
772 CURSOR c_assignment(c_person_id per_assignments_f.person_id%type,
773 c_effective_date per_assignments_f.effective_start_date%type) IS
774 SELECT assignment_type, assignment_id
775 FROM per_all_assignments_f asg
776 WHERE asg.person_id = c_person_id
777 AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
778
779 CURSOR c_sess_date(c_session_id fnd_sessions.session_id%type)
780 IS
781 SELECT effective_date
782 FROM fnd_sessions
783 WHERE session_id = c_session_id;
784
785 CURSOR c_per_ei(c_per_extra_info_id per_people_extra_info.person_extra_info_id%type,
786 c_information_type per_people_extra_info.information_type%type) IS
787 SELECT *
788 FROM per_people_extra_info ppei
789 WHERE ppei.person_extra_info_id = c_per_extra_info_id
790 AND ppei.information_type = c_information_type;
791
792 l_effective_date fnd_sessions.effective_date%type;
793 l_session_id fnd_sessions.session_id%type;
794 l_assignment_type per_all_assignments_f.assignment_type%type;
795 l_assignment_id per_all_assignments_f.assignment_id%type;
796 l_agency_contrib_date date;
797 l_emp_contrib_date date;
798 l_fers_elig_exp_date date;
799 l_fegli_elig_exp_date date;
800 l_date_temp_elig date;
801 l_date_fehb_elig date;
802 l_tsp_scd date;
803 l_business_group_id per_all_people_f.business_group_id%type;
804 l_asg_ei_data per_assignment_extra_info%rowtype;
805 l_annuitant_indicator per_assignment_extra_info.aei_information5%type;
806 l_session ghr_history_api.g_session_var_type;
807 l_noa_id number;
808 l_noa_family_code ghr_noa_families.noa_family_code%type;
809
810 -- Bug 4760226
811 CURSOR c_noa_family_code(c_noa_id ghr_nature_of_actions.nature_of_action_id%type,
812 c_effective_date ghr_nature_of_actions.date_from%type) IS
813 SELECT noa_family_code
814 FROM ghr_noa_families
815 WHERE nature_of_action_id = c_noa_id
816 AND c_effective_date BETWEEN NVL(start_date_active,to_date('01/01/1951','dd/mm/yyyy'))
817 AND NVL(end_date_active,to_date('31/12/4712','dd/mm/yyyy'));
818
819 BEGIN
820 IF ghr_utility.is_ghr = 'TRUE' AND p_information_type IN ('GHR_US_PER_BENEFIT_INFO','GHR_US_PER_SCD_INFORMATION') THEN
821 IF g_debug = TRUE THEN
822 hr_utility.set_location('Inside validate_create_personei',12);
823 END IF;
824 l_session_id := USERENV('sessionid');
825 ghr_history_api.get_g_session_var(l_session);
826
827 IF l_session.pa_request_id IS NOT NULL THEN
828 l_effective_date := l_session.date_effective;
829 FOR l_cur_noa_code IN c_noa_family_code(l_session.noa_id, l_effective_date) LOOP
830 l_noa_family_code := l_cur_noa_code.noa_family_code;
831 END LOOP;
832 ELSE
833 FOR l_cur_sess_date IN c_sess_date(l_session_id) LOOP
834 l_effective_date := l_cur_sess_date.effective_date;
835 END LOOP;
836 END IF;
837
838 IF g_debug = TRUE THEN
839 hr_utility.set_location('eff.date ' || l_effective_date,12);
840 END IF;
841
842 -- Fire only for Appt, Conv to appt and extension actions
843 IF l_noa_family_code IN ('APPT','CONV_APPT','EXT_NTE')
844 AND l_session.noa_id_correct IS NULL THEN
845 -- Get Assignment type
846 FOR l_asg_cur IN c_assignment(p_person_id,l_effective_date) LOOP
847 l_assignment_type := l_asg_cur.assignment_type;
848 l_assignment_id := l_asg_cur.assignment_id;
849 END LOOP;
850
851 IF g_debug = TRUE THEN
852 hr_utility.set_location('Assignment_type ' || l_assignment_type,12);
853 hr_utility.set_location('Assignment_id ' || l_assignment_id,12);
854 hr_utility.set_location('p_information_type ' || p_information_type,12);
855 END IF;
856
857 -- Validation only if person is employee
858 IF l_assignment_type = 'E' THEN
859 FOR l_cur_per_ei IN c_per_ei(p_person_extra_info_id,p_information_type) LOOP
860 IF p_information_type = 'GHR_US_PER_BENEFIT_INFO' THEN
861 l_date_fehb_elig := fnd_date.canonical_to_date(l_cur_per_ei.pei_information4);
862 l_date_temp_elig := fnd_date.canonical_to_date(l_cur_per_ei.pei_information5);
863 l_fegli_elig_exp_date := fnd_date.canonical_to_date(l_cur_per_ei.pei_information3);
864 l_fers_elig_exp_date := fnd_date.canonical_to_date(l_cur_per_ei.pei_information11);
865 l_agency_contrib_date := fnd_date.canonical_to_date(l_cur_per_ei.pei_information14);
866 l_emp_contrib_date := fnd_date.canonical_to_date(l_cur_per_ei.pei_information15);
867 ELSIF p_information_type = 'GHR_US_PER_SCD_INFORMATION' THEN
868 l_tsp_scd := fnd_date.canonical_to_date(l_cur_per_ei.pei_information6);
869 END IF;
870 END LOOP;
871 -- Get Annuitant indicator
872 ghr_history_fetch.fetch_asgei
873 (p_assignment_id => l_assignment_id,
874 p_information_type => 'GHR_US_ASG_SF52',
875 p_date_effective => l_effective_date,
876 p_asg_ei_data => l_asg_ei_data
877 );
878 l_annuitant_indicator := l_asg_ei_data.aei_information5;
879 IF g_debug = TRUE THEN
880 hr_utility.set_location('l_annuitant_indicator ' || l_annuitant_indicator,12);
881 hr_utility.set_location('l_tsp_scd ' || to_char(l_tsp_scd,'dd/mm/yyyy'),12);
882 END IF;
883 -- Call Validation package
884 validate_benefits(
885 p_effective_date => l_effective_date
886 , p_which_eit => 'P'
887 , p_date_fehb_elig => l_date_fehb_elig
888 , p_date_temp_elig => l_date_temp_elig
889 , p_tsp_scd => l_tsp_scd
890 , p_agency_contrib_date => l_agency_contrib_date
891 , p_emp_contrib_date => l_emp_contrib_date
892 , p_fegli_elig_exp_date => l_fegli_elig_exp_date
893 , p_fers_elig_exp_date => l_fers_elig_exp_date
894 , p_annuitant_indicator => l_annuitant_indicator);
895 END IF; -- IF l_assignment_type = 'E' THEN
896 END IF; -- IF l_noa_family_code IN ('APPT','CONV_APPT','EXT_NTE')
897 END IF; -- IF ghr_utility.is_ghr
898
899 END validate_create_personei;
900
901
902 end GHR_BEN_VALIDATION;
[Home] [Help]