[Home] [Help]
PACKAGE BODY: APPS.GHR_ELT_TO_BEN_PKG
Source
1 PACKAGE BODY GHR_ELT_TO_BEN_PKG AS
2 /* $Header: ghbencnv.pkb 120.25.12020000.2 2012/11/16 06:10:01 utokachi ship $ */
3
4
5
6 g_proc_name VARCHAR2(100);
7
8 TYPE lt_person_ids IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
9 g_person_ids lt_person_ids;
10
11 -- ============================================================================
12 -- << Procedure: execute_conv_mt >>
13 -- Description:
14 -- This procedure is called from concurrent program. This procedure will
15 -- determine the batch size and call sub programs.
16 -- ============================================================================
17 PROCEDURE EXECUTE_CONV_MT(p_errbuf OUT NOCOPY VARCHAR2
18 ,p_retcode OUT NOCOPY NUMBER
19 ,p_batch_size IN NUMBER
20 ,p_thread_size IN NUMBER) IS
21
22 -- Cursor to fetch distinct person records.
23 CURSOR c_per_records IS
24 SELECT distinct a.person_id person_id
25 FROM per_all_people_f a,hr_organization_information hoi
26 WHERE a.business_group_id = hoi.organization_id
27 AND hoi.org_information_context = 'GHR_US_ORG_INFORMATION';
28
29 -- Cursor to find total number of distinct person records
30 CURSOR c_tot_per_records IS
31 SELECT COUNT(distinct a.person_id) person_count
32 FROM per_all_people_f a,hr_organization_information hoi
33 WHERE a.business_group_id = hoi.organization_id
34 AND hoi.org_information_context = 'GHR_US_ORG_INFORMATION';
35
36 CURSOR c_completion_status(c_session_id NUMBER) IS
37 SELECT max(completion_status) max_status
38 FROM GHR_MTS_TEMP
39 WHERE session_id = c_session_id;
40
41 -- Declaration of Local variables
42 l_batch_size NUMBER;
43 l_thread_size NUMBER;
44 l_batch_no NUMBER;
45 l_batch_counter NUMBER;
46 l_session_id NUMBER;
47 l_parent_request_id NUMBER;
48 l_completion_status NUMBER;
49 l_request_id NUMBER;
50 l_count NUMBER;
51 l_status BOOLEAN;
52 l_log_text VARCHAR2(2000);
53 l_result VARCHAR2(200);
54 rphase VARCHAR2(80);
55 rstatus VARCHAR2(80);
56 dphase VARCHAR2(30);
57 dstatus VARCHAR2(30);
58 message VARCHAR2(240);
59 call_status BOOLEAN;
60 l_update_name pay_upgrade_definitions.short_name%type;
61
62 BEGIN
63 -- Initialization of variables.
64 l_batch_counter := 0;
65 l_batch_no := 1;
66 l_session_id := USERENV('SESSIONID');
67 l_parent_request_id := fnd_profile.VALUE('CONC_REQUEST_ID');
68 l_status := TRUE;
69 g_person_ids.DELETE;
70 g_proc_name := 'GHR_BEN_EIT_CREATION_'|| l_parent_request_id;
71 l_update_name := 'GHR_ELT_BEN_CONV';
72
73 --
74 -- Need to delete the PAY_UPGRADE_STATUS record if the user requested
75 -- Manual submission of Conc. Request
76 DELETE FROM pay_upgrade_status
77 WHERE upgrade_definition_id = (SELECT upgrade_definition_id
78 FROM pay_upgrade_definitions
79 WHERE short_name = l_update_name);
80
81 -- Thread size should be minimum of 10.
82 IF p_thread_size IS NULL OR p_thread_size < 10 THEN
83 l_thread_size := 10;
84 ELSIF p_thread_size > 35 THEN
85 l_thread_size := 35;
86 ELSE
87 l_thread_size := p_thread_size;
88 END IF;
89
90 -- Batch size should be minimum of 1000.
91 IF p_batch_size IS NULL OR p_batch_size < 1000 THEN
92 l_batch_size := 1000;
93 ELSE
94 l_batch_size := p_batch_size;
95 END IF;
96
97 -- Find out Total future action records
98 FOR l_tot_per_rec IN c_tot_per_records LOOP
99 l_count := l_tot_per_rec.person_count;
100 END LOOP;
101
102 -- Revise the batch size if the total future action record is more than
103 -- the product of thread size and batch size.
104 IF l_count > (l_thread_size * l_batch_size) THEN
105 l_batch_size := CEIL(l_count/l_thread_size);
106 END IF;
107
108 -- Loop through the person records and insert them into the appropriate batch.
109 -- If the batch size exceeds the limit, then insert the following records into the next batch.
110 FOR l_c_per_records IN c_per_records
111 LOOP
112 l_result := NULL;
113 IF NVL(l_result,'NOT EXISTS') = 'NOT EXISTS' THEN
114 IF l_batch_counter >= l_batch_size THEN
115 l_batch_no := l_batch_no + 1;
116 l_batch_counter := 0;
117 END IF;
118 -- Insert values into the table
119 INSERT INTO GHR_MTS_TEMP(session_id, batch_no, pa_request_id, action_type)
120 VALUES(l_session_id,l_batch_no,l_c_per_records.person_id, NULL);
121 l_batch_counter := l_batch_counter + 1;
122 END IF;
123 END LOOP;
124
125 COMMIT;
126
127 -- Call child concurrent programs for each and every thread
128 l_log_text := 'Total number of employees: ' || l_count || ' : Number of Batches ' || l_batch_no || ' : Batch size ' || l_batch_size;
129 ghr_wgi_pkg.create_ghr_errorlog(
130 p_program_name => g_proc_name,
131 p_log_text => l_log_text,
132 p_message_name => 'Number of Batches',
133 p_log_date => sysdate
134 );
135 COMMIT;
136
137 -- Commented for testing
138 FOR l_thread IN 1..l_batch_no LOOP
139 -- Concurrent program
140 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
141 application => 'GHR',
142 program => 'GHSUBBENCONV',
143 sub_request => FALSE,
144 argument1=> l_session_id, -- Session ID
145 argument2=> l_thread, -- Batch no
146 argument3=> l_parent_request_id -- Parent request id
147 );
148 g_person_ids(l_thread) := l_request_id;
149 -- fnd_conc_global.set_req_globals(conc_status => 'PAUSED');
150 END LOOP;
151
152 COMMIT;
153
154 IF g_person_ids.COUNT > 0 THEN
155 -- Wait for the child concurrent programs to get finished
156 FOR l_thread_count IN 1..l_batch_no LOOP
157 l_status := TRUE;
158 hr_utility.set_location('batch ' || l_thread_count,1000);
159 hr_utility.set_location('request id ' || g_person_ids(l_thread_count),1000);
160 WHILE l_status = TRUE LOOP
161 call_status := FND_CONCURRENT.GET_REQUEST_STATUS(g_person_ids(l_thread_count),'','',rphase,rstatus,dphase,dstatus, message);
162 hr_utility.set_location('dphase ' || dphase,1000);
163 IF dphase = 'COMPLETE' THEN
164 l_status := FALSE;
165 ELSE
166 dbms_lock.sleep(5);
167 END IF;
168 END LOOP;
169 END LOOP;
170 END IF;
171
172 FOR l_cur_compl_status IN c_completion_status(l_session_id) LOOP
173 l_completion_status := l_cur_compl_status.max_status;
174 END LOOP;
175
176 --hr_utility.trace_off;
177 -- Assigning Return codes
178 IF l_completion_status = 2 THEN
179 p_retcode := 2;
180 p_errbuf := 'There were errors in some person records. Please verify Federal Process Log for details';
181 ELSIF l_completion_status = 1 THEN
182 p_retcode := 1;
183 p_errbuf := 'There were errors in some person records. Please verify Federal Process Log for details' ;
184 ELSE
185 p_retcode := 0;
186 --
187 -- insert the history record in table pay_upgrade_status.
188 --
189 hr_update_utility.setUpdateProcessing(p_update_name => l_update_name);
190 hr_update_utility.setUpdateComplete(p_update_name => l_update_name);
191 --
192 END IF;
193
194 -- Delete the temporary table data.
195 DELETE FROM GHR_MTS_TEMP
196 WHERE session_id = l_session_id;
197
198 COMMIT;
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 p_retcode := 1;
203 p_errbuf := SQLERRM;
204 DELETE FROM GHR_MTS_TEMP
205 WHERE session_id = l_session_id;
206 COMMIT;
207 END EXECUTE_CONV_MT;
208
209 -- *******************************
210 -- procedure execute_conversion
211 -- *******************************
212 Procedure execute_conversion (p_errbuf OUT NOCOPY VARCHAR2
213 ,p_retcode OUT NOCOPY NUMBER
214 ,p_session_id IN NUMBER
215 ,p_batch_no IN NUMBER
216 ,p_parent_request_id IN NUMBER) IS
217
218 -- Variable Declaration
219 l_current_bg_id NUMBER(15);
220 l_current_person_id NUMBER(15);
221 l_benefits_eit_rec ghr_api.per_benefit_info_type;
222 l_pa_history_rec ghr_pa_history%rowtype;
223 l_new_effective_date DATE;
224 l_old_effective_date DATE;
225 l_cnt NUMBER;
226 l_log_text ghr_process_log.log_text%TYPE;
227 l_program_name ghr_process_log.program_name%TYPE;
228 l_req VARCHAR2 (25);
229 l_errbuf VARCHAR2(2000);
230 l_retcode NUMBER;
231
232 l_per_err_cnt NUMBER(15);
233 l_ssn per_all_people_f.national_identifier%TYPE;
234 l_full_name per_all_people_f.full_name%TYPE;
235 l_dummy VARCHAR2(1);
236 l_sid NUMBER;
237
238 --Bug # 9329643
239 l_emp_num per_people_f.employee_number%type;
240 -- ****** MAIN Program CURSORS *********
241 -- Cursor to verify whether Person benefits EIT is created or not
242 CURSOR c_eit_exists IS
243 SELECT 'x'
244 FROM per_people_info_types
245 WHERE information_type = 'GHR_US_PER_BENEFIT_INFO';
246
247 -- Cursor to pick person records of Federal Persons
248 CURSOR c_batch_persons(c_session_id IN NUMBER,
249 c_batch_no IN NUMBER) IS
250 SELECT pa_request_id person_id, batch_no
251 FROM GHR_MTS_TEMP
252 WHERE session_id = c_session_id
253 AND batch_no = c_batch_no;
254
255 CURSOR c_sessionid is
256 SELECT userenv('sessionid') sesid
257 FROM dual;
258
259 -- Cursor to pick the History Rows for a given Person ID
260 CURSOR c_person_history(p_person_id NUMBER) IS
261 SELECT *
262 FROM ghr_pa_history
263 WHERE person_id = p_person_id
264 AND (
265 (table_name = 'PER_PEOPLE_EXTRA_INFO' and information5 = 'GHR_US_PER_GROUP1') OR
266 (table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
267 AND information4 IN (SELECT b.input_value_id
268 FROM pay_element_types_f a,pay_input_values_f b
269 WHERE a.element_type_id = b.element_type_id
270 AND (
271 (a.element_name = 'FEGLI' AND b.NAME = 'Eligibility Expiration') OR
272 (a.element_name = 'Retirement Plan' AND b.NAME = 'FERS Eligibility Expires') OR
273 (a.element_name = 'Health Benefits' and b.NAME in('LWOP Contingncy Strt Date','LWOP Contingncy End Date',
274 'Child Eq Court Ord Date')) OR
275 (a.element_name = 'Health Benefits Pre tax' and b.NAME in('LWOP Contingncy Strt Date','LWOP Contingncy End Date',
276 'Child Eq Court Ord Date')) OR
277 (a.element_name = 'TSP' and b.NAME in('Agncy Contrib Elig Date','Emp Contrib Elig Date'))
278 )
279 )
280 )
281 )
282 ORDER BY effective_date,table_name,process_date,information1,pa_history_id;
283
284
285 PROCEDURE print_ben_record(p_benefits_eit_rec IN ghr_api.per_benefit_info_type,p_person_id IN NUMBER,
286 p_effective_date IN DATE) IS
287 BEGIN
288 hr_utility.set_location('PERSON ID : '||to_char(p_person_id),500);
289 hr_utility.set_location('Eff Date : '||to_char(p_effective_date),505);
290 hr_utility.set_location('FEGLI Date Elg Exp : '|| p_benefits_eit_rec.FEGLI_Date_Eligibility_Expires,510);
291 hr_utility.set_location('FEHB Date Elg Exp : '|| p_benefits_eit_rec.FEHB_Date_Eligibility_expires,520);
292 hr_utility.set_location('FEHB Date Temp Elg : '|| p_benefits_eit_rec.FEHB_Date_temp_eligibility,530);
293 hr_utility.set_location('FEHB Dte Dep Cer Exp : '|| p_benefits_eit_rec.FEHB_Date_dependent_cert_expir,540);
294 hr_utility.set_location('FEHB LWOP Cont St Dt : '|| p_benefits_eit_rec.FEHB_LWOP_contingency_st_date,550);
295 hr_utility.set_location('FEHB LWOP Cont Ed Dt : '|| p_benefits_eit_rec.FEHB_LWOP_contingency_end_date,560);
296 hr_utility.set_location('FEHB Chld Eq Crt Dt : '|| p_benefits_eit_rec.FEHB_Child_equiry_court_date,570);
297 hr_utility.set_location('FERS Date Elg Exp : '|| p_benefits_eit_rec.FERS_Date_eligibility_expires,580);
298 hr_utility.set_location('FERS Election Dt : '|| p_benefits_eit_rec.FERS_Election_Date,590);
299 hr_utility.set_location('FERS Election Ind : '|| p_benefits_eit_rec.FERS_Election_Indicator,600);
300 hr_utility.set_location('TSP Agn Cont Elg Dt : '|| p_benefits_eit_rec.TSP_Agncy_Contrib_Elig_date,610);
301 hr_utility.set_location('TSP Emp Cont Elg Dt : '|| p_benefits_eit_rec.TSP_Emp_Contrib_Elig_date,620);
302 END print_ben_record;
303
304 -- Procedure to get person Full Name, SSN
305 PROCEDURE get_person_name_ssn(p_person_id IN per_people_f.person_id%TYPE
306 ,p_effective_date IN DATE
307 ,p_full_name OUT NOCOPY per_people_f.full_name%TYPE
308 ,p_national_identifier OUT NOCOPY per_people_f.national_identifier%TYPE
309 ) IS
310 CURSOR cur_per IS
311 SELECT per.full_name
312 ,per.national_identifier
313 FROM per_all_people_f per
314 WHERE per.person_id = p_person_id
315 AND NVL(p_effective_date,TRUNC(sysdate)) between per.effective_start_date
316 and per.effective_end_date;
317 BEGIN
318 FOR cur_per_rec IN cur_per LOOP
319 p_full_name := cur_per_rec.full_name;
320 p_national_identifier := cur_per_rec.national_identifier;
321 END LOOP;
322 EXCEPTION
323 WHEN others THEN
324 p_full_name := NULL ;
325 p_national_identifier := NULL;
326 RAISE;
327 END get_person_name_ssn;
328
329 -- Procedure to BUILD the intermediate Benefits Record
330 PROCEDURE build_benefits_rec(p_pa_history_rec IN ghr_pa_history%rowtype,
331 p_benefits_eit_rec IN OUT nocopy ghr_api.per_benefit_info_type
332 ) IS
333
334 l_benefits_eit_rec ghr_api.per_benefit_info_type;
335 l_element_name VARCHAR2(150);
336 l_input_value VARCHAR2(150);
337 l_input_value_id NUMBER(20);
338
339 CURSOR c_element_inpval(p_input_value_id NUMBER) IS
340 SELECT a.element_name element, b.name input_value
341 FROM pay_element_types_f a,pay_input_values_f b
342 WHERE a.element_type_id = b.element_type_id
343 AND b.input_value_id = p_input_value_id;
344
345
346 BEGIN
347 hr_utility.set_location('Entering build_benefits_rec',0);
348 l_benefits_eit_rec := p_benefits_eit_rec;
349 IF p_pa_history_rec.table_name = 'PER_PEOPLE_EXTRA_INFO' AND p_pa_history_rec.information5 = 'GHR_US_PER_GROUP1' THEN
350 hr_utility.set_location('Assigning values for FEHB Person EIT',5);
351 p_benefits_eit_rec.FEHB_Date_Eligibility_expires := p_pa_history_rec.information19;
352 p_benefits_eit_rec.FEHB_Date_temp_eligibility := p_pa_history_rec.information20;
353 p_benefits_eit_rec.FEHB_Date_dependent_cert_expir := p_pa_history_rec.information21;
354 ELSIF p_pa_history_rec.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F' THEN
355 l_element_name := NULL;
356 l_input_value := NULL;
357 l_input_value_id := p_pa_history_rec.information4;
358 FOR element_inpval_rec IN c_element_inpval(l_input_value_id)
359 LOOP
360 l_element_name := element_inpval_rec.element;
361 l_input_value := element_inpval_rec.input_value;
362 EXIT;
363 END LOOP;
364 IF l_element_name = 'FEGLI' and l_input_value = 'Eligibility Expiration' THEN
365 p_benefits_eit_rec.FEGLI_Date_Eligibility_Expires := p_pa_history_rec.information6;
366 ELSIF l_element_name = 'Retirement Plan' and l_input_value = 'FERS Eligibility Expires' THEN
367 p_benefits_eit_rec.FERS_Date_eligibility_expires := p_pa_history_rec.information6;
368 ELSIF l_element_name = 'Health Benefits' and l_input_value = 'LWOP Contingncy Strt Date' THEN
369 p_benefits_eit_rec.FEHB_LWOP_contingency_st_date := p_pa_history_rec.information6;
370 ELSIF l_element_name = 'Health Benefits' and l_input_value = 'LWOP Contingncy End Date' THEN
371 p_benefits_eit_rec.FEHB_LWOP_contingency_end_date := p_pa_history_rec.information6;
372 ELSIF l_element_name = 'Health Benefits' and l_input_value = 'Child Eq Court Ord Date' THEN
373 p_benefits_eit_rec.FEHB_Child_equiry_court_date := p_pa_history_rec.information6;
374 ELSIF l_element_name = 'Health Benefits Pre tax' and l_input_value = 'LWOP Contingncy Strt Date' THEN
375 p_benefits_eit_rec.FEHB_LWOP_contingency_st_date := p_pa_history_rec.information6;
376 ELSIF l_element_name = 'Health Benefits Pre tax' and l_input_value = 'LWOP Contingncy End Date' THEN
377 p_benefits_eit_rec.FEHB_LWOP_contingency_end_date := p_pa_history_rec.information6;
378 ELSIF l_element_name = 'Health Benefits Pre tax' and l_input_value = 'Child Eq Court Ord Date' THEN
379 p_benefits_eit_rec.FEHB_Child_equiry_court_date := p_pa_history_rec.information6;
380 ELSIF l_element_name = 'TSP' and l_input_value = 'Agncy Contrib Elig Date' THEN
381 p_benefits_eit_rec.TSP_Agncy_Contrib_Elig_date := p_pa_history_rec.information6;
382 ELSIF l_element_name = 'TSP' and l_input_value = 'Emp Contrib Elig Date' THEN
383 p_benefits_eit_rec.TSP_Emp_Contrib_Elig_date := p_pa_history_rec.information6;
384 END IF;
385 ELSE
386 -- SKIP the Record;
387 NULL;
388 END IF;
389 hr_utility.set_location('Leaving build_benefits_rec',10);
390 EXCEPTION
391 WHEN OTHERS THEN
392 p_benefits_eit_rec := l_benefits_eit_rec;
393 RAISE;
394 END build_benefits_rec;
395
396 PROCEDURE insert_benefits_eit_rec(p_person_id IN NUMBER,
397 p_benefits_eit_rec IN ghr_api.per_benefit_info_type,
398 p_effective_date IN DATE) IS
399
400 l_information_type per_people_extra_info.information_type%type;
401 l_person_extra_info_id NUMBER;
402 l_object_version_number NUMBER;
403
404 CURSOR c_person_extra_info(p_person_id NUMBER) IS
405 SELECT person_extra_info_id,
406 object_version_number
407 FROM per_people_extra_info
408 WHERE person_id = p_person_id
409 AND information_type = 'GHR_US_PER_BENEFIT_INFO';
410
411
412 BEGIN
413 hr_utility.set_location('Entering Insert benefits EIT REC',0);
414 l_information_type := 'GHR_US_PER_BENEFIT_INFO';
415 hr_utility.set_location('Calling Create Person Extra Info',10);
416 FOR per_extra_info_rec IN c_person_extra_info(p_person_id)
417 LOOP
418 l_person_extra_info_id := per_extra_info_rec.person_extra_info_id;
419 l_object_version_number := per_extra_info_rec.object_version_number;
420 END LOOP;
421
422 -- print_ben_record(p_benefits_eit_rec,p_person_id,p_effective_date);
423
424 IF l_person_extra_info_id IS NULL THEN
425 ghr_person_extra_info_api.create_person_extra_info
426 (p_validate => false
427 ,p_person_id => p_person_id
428 ,p_information_type => l_information_type
429 ,p_effective_date => p_effective_date
430 ,p_pei_information_category => l_information_type
431 ,p_pei_information3 => p_benefits_eit_rec.FEGLI_Date_Eligibility_Expires
432 ,p_pei_information4 => p_benefits_eit_rec.FEHB_Date_Eligibility_expires
433 ,p_pei_information5 => p_benefits_eit_rec.FEHB_Date_temp_eligibility
434 ,p_pei_information6 => p_benefits_eit_rec.FEHB_Date_dependent_cert_expir
435 ,p_pei_information7 => p_benefits_eit_rec.FEHB_LWOP_contingency_st_date
436 ,p_pei_information8 => p_benefits_eit_rec.FEHB_LWOP_contingency_end_date
437 ,p_pei_information10 => p_benefits_eit_rec.FEHB_Child_equiry_court_date
438 ,p_pei_information11 => p_benefits_eit_rec.FERS_Date_eligibility_expires
439 ,p_pei_information12 => p_benefits_eit_rec.FERS_Election_Date
440 ,p_pei_information13 => p_benefits_eit_rec.FERS_Election_Indicator
441 ,p_pei_information14 => p_benefits_eit_rec.TSP_Agncy_Contrib_Elig_date
442 ,p_pei_information15 => p_benefits_eit_rec.TSP_Emp_Contrib_Elig_date
443 ,p_person_extra_info_id => l_person_extra_info_id
444 ,p_object_version_number => l_object_version_number
445 );
446 hr_utility.set_location('Person Extra Info ID: '||to_char(l_person_extra_info_id),20);
447 hr_utility.set_location('Object Version Number: '||to_char(l_object_version_number),30);
448 ELSE
449 ghr_person_extra_info_api.update_person_extra_info
450 (p_person_extra_info_id => l_person_extra_info_id
451 ,p_object_version_number => l_object_version_number
452 ,p_effective_date => p_effective_date
453 ,p_pei_information3 => p_benefits_eit_rec.FEGLI_Date_Eligibility_Expires
454 ,p_pei_information4 => p_benefits_eit_rec.FEHB_Date_Eligibility_expires
455 ,p_pei_information5 => p_benefits_eit_rec.FEHB_Date_temp_eligibility
456 ,p_pei_information6 => p_benefits_eit_rec.FEHB_Date_dependent_cert_expir
457 ,p_pei_information7 => p_benefits_eit_rec.FEHB_LWOP_contingency_st_date
458 ,p_pei_information8 => p_benefits_eit_rec.FEHB_LWOP_contingency_end_date
459 ,p_pei_information10 => p_benefits_eit_rec.FEHB_Child_equiry_court_date
460 ,p_pei_information11 => p_benefits_eit_rec.FERS_Date_eligibility_expires
461 ,p_pei_information12 => p_benefits_eit_rec.FERS_Election_Date
462 ,p_pei_information13 => p_benefits_eit_rec.FERS_Election_Indicator
463 ,p_pei_information14 => p_benefits_eit_rec.TSP_Agncy_Contrib_Elig_date
464 ,p_pei_information15 => p_benefits_eit_rec.TSP_Emp_Contrib_Elig_date
465 );
466 END IF;
467 hr_utility.set_location('Leaving Insert Benefits EIT REC',40);
468 END insert_benefits_eit_rec;
469
470 -- ***************** MAIN PROGRAM ***************
471 BEGIN
472 FOR s_id IN c_sessionid
473 LOOP
474 l_sid := s_id.sesid;
475 EXIT;
476 END LOOP;
477
478 BEGIN
479 UPDATE fnd_sessions SET SESSION_ID = l_sid
480 WHERE SESSION_ID = l_sid;
481 IF SQL%NOTFOUND THEN
482 INSERT INTO fnd_sessions(SESSION_ID,EFFECTIVE_DATE)
483 VALUES (l_sid,sysdate);
484 END IF;
485 END;
486
487 --hr_utility.set_location('Entering the Conversion Process...',0);
488 l_errbuf := NULL;
489 l_retcode := 0;
490 l_per_err_cnt := 0;
491
492
493 --
494 --
495 l_req := fnd_profile.VALUE ('CONC_REQUEST_ID');
496 l_program_name := 'GHR_BEN_EIT_CREATION_'||l_req;
497 l_dummy := 'y';
498 -- Check whether the benefits information type is created or not.
499 Open c_eit_exists;
500 Fetch c_eit_exists into l_dummy;
501 close c_eit_exists;
502
503 IF l_dummy <> 'x' THEN
504 p_errbuf := 'Benefits EIT GHR_US_PER_BENEFIT_INFO is missing. ' ||
505 'Please run ghinfoty.sql and submit this concurrent program again.';
506 p_retcode := 1;
507 ELSE
508 FOR federal_persons_rec IN c_batch_persons(p_session_id,p_batch_no)
509 LOOP
510 BEGIN
511 l_current_person_id := federal_persons_rec.person_id;
512 -- hr_utility.set_location('Processing Person '||to_char(l_current_person_id)||'....',20);
513 -- initialise Benefits EIT Record
514 l_benefits_eit_rec := NULL;
515 l_new_effective_date := to_date('1900/01/01','YYYY/MM/DD');
516 l_old_effective_date := to_date('1900/01/01','YYYY/MM/DD');
517 l_cnt := 0;
518 OPEN c_person_history(l_current_person_id);
519 LOOP
520 BEGIN
521 FETCH c_person_history INTO l_pa_history_rec;
522 l_cnt := l_cnt + 1;
523 IF c_person_history%FOUND THEN
524 --hr_utility.set_location('Processing history record '||to_char(l_pa_history_rec.pa_history_id)||'....',30);
525 --hr_utility.set_location('History record found',40);
526 l_new_effective_date := l_pa_history_rec.effective_date;
527 IF (l_new_effective_date = l_old_effective_date OR l_cnt = 1) THEN
528 build_benefits_rec(l_pa_history_rec,l_benefits_eit_rec);
529 ELSE
530 insert_benefits_eit_rec(l_current_person_id,l_benefits_eit_rec,l_old_effective_date);
531 build_benefits_rec(l_pa_history_rec,l_benefits_eit_rec);
532 END IF;
533 l_old_effective_date := l_new_effective_date;
534 ELSE
535 hr_utility.set_location('History record NOT found ...',50);
536 -- Added l_Cnt >1 condition. This is to check whether there are history records exists or not.
537 -- If no history record exists for a person, creation of benefits records can be skipped.
538 IF l_cnt > 1 THEN
539 insert_benefits_eit_rec(l_current_person_id,l_benefits_eit_rec,l_old_effective_date);
540 END IF;
541 -- print_ben_record(l_benefits_eit_rec,l_current_person_id,l_old_effective_date);
542 EXIT;
543 END IF;
544 --hr_utility.set_location('Completed Processing History Records. ',60);
545 EXCEPTION
546 WHEN OTHERS THEN
547 --hr_utility.set_location('Error Occured while processing history records',65);
548 Close c_person_history;
549 RAISE;
550 END;
551 END LOOP; -- For Person_history_rec Cursor
552 Close c_person_history;
553 COMMIT;
554 --hr_utility.set_location('Completed Processing for Person '||to_char(l_current_person_id),70);
555 EXCEPTION
556 WHEN OTHERS THEN
557 l_per_err_cnt := l_per_err_cnt + 1 ;
558 --hr_utility.set_location('Error Occured while processing Person ID',75);
559 get_person_name_ssn(l_current_person_id,l_new_effective_date,l_full_name,l_ssn);
560 --Bug # 9329643
561 l_emp_num := ghr_pa_requests_pkg2.get_employee_number
562 (p_person_id => l_current_person_id,
563 p_effective_date => l_new_effective_date);
564 l_log_text := 'System unable to create Benefits EIT for the Person: '||l_full_name||
565 '; Emp No: '||l_emp_num||'; Error: '||sqlerrm;
566 ghr_wgi_pkg.create_ghr_errorlog (p_program_name => l_program_name
567 ,p_log_text => l_log_text
568 ,p_message_name => 'Benefits EIT Creation Error'
569 ,p_log_date => SYSDATE
570 );
571 COMMIT;
572 END;
573 END LOOP; -- For Person_rec Cursor
574
575 -- Set Concurrent program completion messages
576 IF l_per_err_cnt > 0 THEN
577 p_retcode := 1;
578 hr_utility.set_location('Ret code ' || to_char(l_retcode),1);
579 p_errbuf := 'Unable to create benefits EIT for some person records. Please see the federal process log for details.';
580 ELSE
581 p_retcode := l_retcode;
582 hr_utility.set_location('Ret code ' || to_char(l_retcode),1);
583 p_errbuf := 'Process Completed Successfully';
584 END IF;
585
586 -- Update the completion status.
587 UPDATE GHR_MTS_TEMP
588 SET completion_status = p_retcode
589 WHERE session_id = p_session_id
590 AND batch_no = p_batch_no;
591
592 COMMIT;
593 END IF; -- End of c_eit_exists%FOUND.
594 EXCEPTION
595 WHEN OTHERS THEN
596 hr_utility.set_location('ERROR Occured '||sqlerrm,100);
597 p_errbuf := 'Process Errored Out with error message: '||sqlerrm;
598 p_retcode := 2;
599 END execute_conversion;
600
601
602 PROCEDURE ValidateRun(p_result OUT nocopy varchar2) IS
603
604 GHR_APPLICATION_ID constant number:=8301;
605 GHR_STATUS_INSTALLED constant varchar2(2):='I';
606
607 cursor csr_ghr_installed is
608 select status
609 from fnd_product_installations
610 where application_id = GHR_APPLICATION_ID;
611
612 l_installed fnd_product_installations.status%type;
613 l_result varchar2(10) ;
614
615 BEGIN
616 l_result := 'FALSE';
617 open csr_ghr_installed;
618 fetch csr_ghr_installed into l_installed;
619 if ( l_installed = GHR_STATUS_INSTALLED ) then
620 l_result := 'TRUE';
621 end if;
622 close csr_ghr_installed;
623
624 p_result := l_result;
625 --
626 END ValidateRun;
627
628
629
630 --Begin Bug# 6594288,6729058,7537134,9009719,10180368 Added this procedure for Concurrent program Process
631 -- Health Benefits Data Conversion
632
633 -- This Procedure is to end date or Create new elements pertaining to
634 -- Health Benefits and benefit pre tax elements.
635 -- bug 13080216 new HB elements from 1-Jan-2012 and Previous end dates with 31-Dec-2011
636 -- bug 14729720 new HB elements from 13-Jan-2013 and Previous end dates with 12-Jan-2012
637
638 PROCEDURE execute_conv_hlt_plan ( p_errbuf OUT NOCOPY VARCHAR2,
639 p_retcode OUT NOCOPY NUMBER,
640 p_business_group_id in Number) is
641
642 l_assignment_id pay_element_entries_f.assignment_id%type;
643 l_req VARCHAR2 (25);
644 l_full_name per_people_f.full_name%type; --bug# 10419074
645 l_log_text VARCHAR2(2000); --bug# 10419074
646 l_program_name ghr_process_log.program_name%TYPE;
647
648 --Bug # 9329643
649 l_emp_num per_people_f.employee_number%type;
650
651
652 --Bug # 9329643 added employee number
653 Cursor cur_ssn is
654 SELECT ppf.last_name,ppf.first_name, ppf.middle_names, ppf.employee_number --bug# 10419074
655 FROM per_assignments_f paf, per_people_f ppf
656 WHERE ppf.person_id = paf.person_id
657 AND paf.primary_flag = 'Y'
658 AND paf.assignment_type <> 'B'
659 AND to_date('2013/01/13','YYYY/MM/DD') BETWEEN paf.effective_start_date
660 AND paf.effective_end_date
661 AND to_date('2013/01/13','YYYY/MM/DD') BETWEEN ppf.effective_start_date
662 AND ppf.effective_end_date
663 AND paf.assignment_id =l_assignment_id;
664
665 l_effective_date date;
666 l_name pay_input_values_f.name%type;
667 l_input_value_id pay_input_values_f.input_value_id%type;
668 l_input_value_id_enrol pay_input_values_f.input_value_id%type;
669 l_effective_start_date pay_element_entries_f.effective_start_date%type;
670 l_effective_end_date pay_element_entries_f.effective_end_date%type;
671 l_element_entry_id pay_element_entries_f.element_entry_id%type;
672 l_object_version_number pay_element_entries_f.object_version_number%type;
673 l_screen_entry_value pay_element_entry_values_f.screen_entry_value%type;
674 l_screen_entry_value_enrol pay_element_entry_values_f.screen_entry_value%type;
675 l_effective_start_date_enrol pay_element_entry_values_f.effective_start_date%type;
676 l_effective_end_date_enrol pay_element_entry_values_f.effective_end_date%type;
677 l_business_group_id per_assignments_f.business_group_id%type;
678 l_update_flag number := 0;
679 l_health_plan_mod number := 0;
680 l_datetrack_update_mode varchar2(25);
681
682 l_out_effective_start_date pay_element_entries_f.effective_start_date%type;
683 l_out_effective_end_date pay_element_entries_f.effective_end_date%type;
684 l_out_update_warning boolean;
685
686 l_exists boolean := false;
687 l_check_date date;
688
689 Cursor c_get_ds_code is
690 select dut.duty_station_code
691 from hr_location_extra_info lei,
692 per_all_assignments_f asg,
693 ghr_duty_stations_v dut
694 where asg.assignment_id = l_assignment_id
695 and l_effective_date between
696 asg.effective_Start_date and asg.effective_end_date
697 and asg.location_id = lei.location_id
698 and lei.information_type = 'GHR_US_LOC_INFORMATION'
699 and lei.lei_information3 = dut.duty_station_id
700 and dut.duty_station_code like '06%107'
701 and l_effective_date between
702 dut.effective_start_date and dut.effective_end_date;
703 l_duty_station_code ghr_duty_stations_v.duty_station_code%type;
704
705 BEGIN
706 l_req := fnd_profile.VALUE ('CONC_REQUEST_ID');
707 l_program_name := 'GHR_HB_CNVR_'||l_req;
708 -------------------------------------------------------------------------
709 --Script for Health Plan changes
710 -------------------------------------------------------------------------
711 declare
712 cursor cur_health_benefits is
713 select b.name name,
714 f.input_value_id input_value_id,
715 e.effective_start_date effective_start_date,
716 e.effective_end_date effective_end_date,
717 e.element_entry_id element_entry_id,
718 e.assignment_id assignment_id,
719 c.business_group_id business_group_id,--Bug# 6735031
720 e.object_version_number object_version_number,
721 f.screen_entry_value screen_entry_value
722 from pay_element_types_f a,
723 pay_input_values_f b,
724 pay_element_links_f c,
725 pay_link_input_values_f d,
726 pay_element_entries_f e,
727 pay_element_entry_values_f f
728 where a.element_type_id = b.element_type_id
729 and a.element_type_id = c.element_type_id
730 and c.element_link_id = d.element_link_id
731 and b.input_value_id = d.input_value_id
732 and e.element_link_id = c.element_link_id
733 and f.element_entry_id = e.element_entry_id
734 and f.input_value_id = b.input_value_id
735 and c.business_group_id = nvl(p_business_group_id,c.business_group_id)--Bug# 6735031
736 and e.effective_start_date = f.effective_start_date
737 and e.effective_end_date = f.effective_end_date
738 and e.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
739 and a.element_name = 'Health Benefits'
740 and b.name = 'Health Plan'
741 and f.screen_entry_value in
742 ('L4','IK','SW','YW');
743
744 cursor cur_health_benefits_pt is
745 select b.name name,
746 f.input_value_id input_value_id,
747 e.effective_start_date effective_start_date,
748 e.effective_end_date effective_end_date,
749 e.element_entry_id element_entry_id,
750 e.assignment_id assignment_id,
751 c.business_group_id business_group_id, --Bug# 6735031
752 e.object_version_number object_version_number,
753 f.screen_entry_value screen_entry_value
754 from pay_element_types_f a,
755 pay_input_values_f b,
756 pay_element_links_f c,
757 pay_link_input_values_f d,
758 pay_element_entries_f e,
759 pay_element_entry_values_f f
760 where a.element_type_id = b.element_type_id
761 and a.element_type_id = c.element_type_id
762 and c.element_link_id = d.element_link_id
763 and b.input_value_id = d.input_value_id
764 and e.element_link_id = c.element_link_id
765 and f.element_entry_id = e.element_entry_id
766 and f.input_value_id = b.input_value_id
767 and c.business_group_id = nvl(p_business_group_id,c.business_group_id) --Bug#6735031
768 and e.effective_start_date = f.effective_start_date
769 and e.effective_end_date = f.effective_end_date
770 and e.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
771 and a.element_name = 'Health Benefits Pre tax'
772 and b.name = 'Health Plan'
773 and f.screen_entry_value in
774 ('L4','IK','SW','YW');
775
776 cursor cur_hb_fr is
777 select b.name name,
778 f.input_value_id input_value_id,
779 e.effective_start_date effective_start_date,
780 e.effective_end_date effective_end_date,
781 e.element_entry_id element_entry_id,
782 e.assignment_id assignment_id,
783 e.object_version_number object_version_number,
784 f.screen_entry_value screen_entry_value
785 from pay_element_types_f a,
786 pay_input_values_f b,
787 pay_element_links_f c,
788 pay_link_input_values_f d,
789 pay_element_entries_f e,
790 pay_element_entry_values_f f
791 where a.element_type_id = b.element_type_id
792 and a.element_type_id = c.element_type_id
793 and c.element_link_id = d.element_link_id
794 and b.input_value_id = d.input_value_id
795 and e.element_link_id = c.element_link_id
796 and f.element_entry_id = e.element_entry_id
797 and f.input_value_id = b.input_value_id
798 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
799 and e.effective_start_date = f.effective_start_date
800 and e.effective_end_date = f.effective_end_date
801 and e.effective_end_date > l_check_date
802 and e.element_entry_id = l_element_entry_id
803 and a.element_name = 'Health Benefits'
804 and b.name = 'Health Plan'
805 and f.screen_entry_value in
806 ('L4','IK','SW','YW');
807
808 cursor cur_hb_pt_fr is
809 select b.name name,
810 f.input_value_id input_value_id,
811 e.effective_start_date effective_start_date,
812 e.effective_end_date effective_end_date,
813 e.element_entry_id element_entry_id,
814 e.assignment_id assignment_id,
815 e.object_version_number object_version_number,
816 f.screen_entry_value screen_entry_value
817 from pay_element_types_f a,
818 pay_input_values_f b,
819 pay_element_links_f c,
820 pay_link_input_values_f d,
821 pay_element_entries_f e,
822 pay_element_entry_values_f f
823 where a.element_type_id = b.element_type_id
824 and a.element_type_id = c.element_type_id
825 and c.element_link_id = d.element_link_id
826 and b.input_value_id = d.input_value_id
827 and e.element_link_id = c.element_link_id
828 and f.element_entry_id = e.element_entry_id
829 and f.input_value_id = b.input_value_id
830 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
831 and e.effective_start_date = f.effective_start_date
832 and e.effective_end_date = f.effective_end_date
833 and e.effective_end_date > l_check_date
834 and e.element_entry_id = l_element_entry_id
835 and a.element_name = 'Health Benefits Pre tax'
836 and b.name = 'Health Plan'
837 and f.screen_entry_value in
838 ('L4','IK','SW','YW');
839
840 cursor cur_hb_enroll is
841 select f.input_value_id input_value_id,
842 f.screen_entry_value screen_entry_value
843 from pay_element_types_f a,
844 pay_input_values_f b,
845 pay_element_links_f c,
846 pay_link_input_values_f d,
847 pay_element_entries_f e,
848 pay_element_entry_values_f f
849 where a.element_type_id = b.element_type_id
850 and a.element_type_id = c.element_type_id
851 and c.element_link_id = d.element_link_id
852 and b.input_value_id = d.input_value_id
853 and e.element_link_id = c.element_link_id
854 and f.element_entry_id = e.element_entry_id
855 and f.input_value_id = b.input_value_id
856 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
857 and e.effective_start_date = f.effective_start_date
858 and e.effective_end_date = f.effective_end_date
859 and f.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
860 and a.element_name = 'Health Benefits'
861 and b.name = 'Enrollment'
862 and e.element_entry_id = l_element_entry_id;
863
864
865 cursor cur_hb_pt_enroll is
866 select f.input_value_id input_value_id,
867 f.screen_entry_value screen_entry_value,
868 f.effective_start_date effective_start_date,
869 f.effective_end_date effective_end_date
870 from pay_element_types_f a,
871 pay_input_values_f b,
872 pay_element_links_f c,
873 pay_link_input_values_f d,
874 pay_element_entries_f e,
875 pay_element_entry_values_f f
876 where a.element_type_id = b.element_type_id
877 and a.element_type_id = c.element_type_id
878 and c.element_link_id = d.element_link_id
879 and b.input_value_id = d.input_value_id
880 and e.element_link_id = c.element_link_id
881 and f.element_entry_id = e.element_entry_id
882 and f.input_value_id = b.input_value_id
883 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
884 and e.effective_start_date = f.effective_start_date
885 and e.effective_end_date = f.effective_end_date
886 and f.effective_start_date = l_effective_start_date
887 and f.effective_end_date = l_effective_end_date
888 and f.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
889 and a.element_name = 'Health Benefits Pre tax'
890 and b.name = 'Enrollment'
891 and e.element_entry_id = l_element_entry_id;
892
893
894 BEGIN --A1
895
896 l_update_flag := 0;
897 l_element_entry_id := null;
898 l_effective_date := to_date('2013/01/13','YYYY/MM/DD');
899
900
901 ----- A. Conversion.
902 ----- Fetch the data pertaining to Health benefits element and the input value
903 ----- is Health Plan
904
905 for cur_health_benefits_rec in cur_health_benefits
906 loop
907 l_name := cur_health_benefits_rec.name;
908 l_input_value_id := cur_health_benefits_rec.input_value_id;
909 l_effective_start_date := cur_health_benefits_rec.effective_start_date;
910 l_effective_end_date := cur_health_benefits_rec.effective_end_date;
911 l_check_date := cur_health_benefits_rec.effective_end_date;
912 l_element_entry_id := cur_health_benefits_rec.element_entry_id;
913 l_assignment_id := cur_health_benefits_rec.assignment_id;
914 l_business_group_id := cur_health_benefits_rec.business_group_id;--Bug# 6735031
915 l_object_version_number := cur_health_benefits_rec.object_version_number;
916 l_screen_entry_value := cur_health_benefits_rec.screen_entry_value;
917
918 for cur_hb_enroll_rec in cur_hb_enroll loop
919 l_input_value_id_enrol := cur_hb_enroll_rec.input_value_id;
920 l_screen_entry_value_enrol := cur_hb_enroll_rec.screen_entry_value;
921 exit;
922 end loop;
923 IF l_screen_entry_value in ('L4','IK','SW','YW') THEN
924 l_update_flag := 1;
925 l_screen_entry_value_enrol := 'Y';
926 l_screen_entry_value := 'ZZ';
927 END IF;
928
929 l_exists := false;
930
931 if l_effective_start_date >= l_effective_date then
932 l_datetrack_update_mode := 'CORRECTION';
933 l_effective_date := l_effective_start_date;
934 elsif l_effective_start_date < l_effective_date and
935 to_char(l_effective_end_date,'YYYY/MM/DD') = '4712/12/31' then
936 l_datetrack_update_mode := 'UPDATE';
937 ----Check for future rows.
938 elsif l_effective_start_date < l_effective_date then
939 for update_mode_a in cur_hb_fr loop
940 l_exists := true;
941 exit;
942 end loop;
943 If l_exists then
944 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
945 Else
946 l_datetrack_update_mode := 'UPDATE';
947 End if;
948 end if;
949
950 if l_update_flag = 1 then
951 l_update_flag := 0;
952 BEGIN --A2
953 --Begin Bug# 10419074
954 for l_cur_ssn in cur_ssn loop
955 l_full_name := l_cur_ssn.last_name||','|| l_cur_ssn.first_name||' '|| l_cur_ssn.middle_names;
956 l_emp_num := l_cur_ssn.employee_number;--Bug # 9329643
957 exit;
958 end loop;
959 --End Bug# 10419074
960 ghr_element_entry_api.update_element_entry
961 ( p_datetrack_update_mode => l_datetrack_update_mode
962 ,p_effective_date => l_effective_date
963 ,p_business_group_id => l_business_group_id
964 ,p_element_entry_id => l_element_entry_id
965 ,p_object_version_number => l_object_version_number
966 ,p_input_value_id1 => l_input_value_id_enrol
967 ,p_entry_value1 => l_screen_entry_value_enrol
968 ,p_input_value_id2 => l_input_value_id
969 ,p_entry_value2 => l_screen_entry_value
970 ,p_effective_start_date => l_out_effective_start_date
971 ,p_effective_end_date => l_out_effective_end_date
972 ,p_update_warning => l_out_update_warning
973 );
974 l_health_plan_mod := l_health_plan_mod + 1;
975 --Begin Bug# 10419074
976 p_retcode := 0;
977 l_log_text := 'For '||'Employee Name: '|| l_full_name ||
978 ' Emp No: '|| l_emp_num ||' Health Benefit conversion Script1 Successfully completed';
979 p_errbuf := l_log_text;
980
981 ghr_wgi_pkg.create_ghr_errorlog(
982 p_program_name => l_program_name,
983 p_message_name => substr('HB conversion Successful',1,30),
984 p_log_text => substr(l_log_text,1,2000),
985 p_log_date => sysdate);
986 --End Bug# 10419074
987 exception
988 when others then
989 ghr_wgi_pkg.create_ghr_errorlog(
990 p_program_name => l_program_name,
991 p_message_name => 'HB conversion - ERROR',
992 p_log_text => 'Error : Health Benefits Conversion script1 Errored out' ||
993 ' For Emp Name ' || l_full_name ||
994 ' Emp No ' || l_emp_num ||
995 ' Element ' || to_char(l_element_entry_id) ||
996 ' Assignment ' || to_char(l_assignment_id) ||
997 ' SQLERR ' || SQLERRM,
998 p_log_date => sysdate);
999 commit;
1000 END;--A2
1001 end if;
1002 l_effective_date := to_date('2013/01/13','YYYY/MM/DD');
1003 end loop;
1004 BEGIN --A3
1005 if l_health_plan_mod = 0 then --bug# 10419074
1006 ghr_wgi_pkg.create_ghr_errorlog(
1007 p_program_name => l_program_name,
1008 p_message_name => substr('No HB conversion',1,30),
1009 p_log_text => 'No Health Benefits Data Modified with Conversion Script1...',
1010 p_log_date => sysdate);
1011 end if;
1012 commit;
1013 exception
1014 when others then
1015 ghr_wgi_pkg.create_ghr_errorlog(
1016 p_program_name => l_program_name,
1017 p_message_name => 'HB conversion - ERROR',
1018 p_log_text => 'Error : Health Benefits Conversion script1 Errored out' ||
1019 ' Element ' || to_char(l_element_entry_id) ||
1020 ' Assignment ' || to_char(l_assignment_id) ||
1021 ' SQLERR ' || SQLERRM,
1022 p_log_date => sysdate);
1023 commit;
1024 END; --A3
1025
1026 ----- FOR 'Health Benefits Pre tax'
1027 l_update_flag := 0;
1028 l_element_entry_id := null;
1029 l_effective_date := to_date('2013/01/13','YYYY/MM/DD');
1030 l_health_plan_mod := 0;
1031
1032 begin --B1
1033 ----- A. Conversion.
1034 ----- Fetch the data pertaining to Health benefits Pre tax element and the input value
1035 ----- is Health Plan
1036
1037 for cur_health_benefits_pt_rec in cur_health_benefits_pt loop --Loop1
1038 l_name := cur_health_benefits_pt_rec.name;
1039 l_input_value_id := cur_health_benefits_pt_rec.input_value_id;
1040 l_effective_start_date := cur_health_benefits_pt_rec.effective_start_date;
1041 l_effective_end_date := cur_health_benefits_pt_rec.effective_end_date;
1042 l_check_date := cur_health_benefits_pt_rec.effective_end_date;
1043 l_element_entry_id := cur_health_benefits_pt_rec.element_entry_id;
1044 l_assignment_id := cur_health_benefits_pt_rec.assignment_id;
1045 l_business_group_id := cur_health_benefits_pt_rec.business_group_id;--Bug# 6735031
1046 l_object_version_number := cur_health_benefits_pt_rec.object_version_number;
1047 l_screen_entry_value := cur_health_benefits_pt_rec.screen_entry_value;
1048
1049 for cur_hb_pt_enroll_rec in cur_hb_pt_enroll loop
1050 l_input_value_id_enrol := cur_hb_pt_enroll_rec.input_value_id;
1051 l_screen_entry_value_enrol := cur_hb_pt_enroll_rec.screen_entry_value;
1052 exit;
1053 end loop;
1054
1055 IF l_screen_entry_value in ('L4','IK','SW','YW') THEN
1056 l_update_flag := 1;
1057 l_screen_entry_value_enrol := 'Y';
1058 l_screen_entry_value := 'ZZ';
1059 END IF;
1060
1061 l_exists := false;
1062
1063 if l_effective_start_date >= l_effective_date then
1064 l_datetrack_update_mode := 'CORRECTION';
1065 l_effective_date := l_effective_start_date;
1066 elsif (l_effective_start_date < l_effective_date) and
1067 (to_char(l_effective_end_date,'YYYY/MM/DD') = '4712/12/31') then
1068 l_datetrack_update_mode := 'UPDATE';
1069 ----Check for future rows.
1070 elsif (l_effective_start_date < l_effective_date) then
1071 for update_mode in cur_hb_pt_fr loop
1072 l_exists := true;
1073 exit;
1074 end loop;
1075 If l_exists then
1076 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
1077 Else
1078 l_datetrack_update_mode := 'UPDATE';
1079 End if;
1080 end if;
1081
1082 if l_update_flag = 1 then
1083 BEGIN --B2
1084 l_update_flag := 0;
1085 --Begin Bug# 10419074
1086 for l_cur_ssn in cur_ssn loop
1087 l_full_name := l_cur_ssn.last_name||','|| l_cur_ssn.first_name||' '|| l_cur_ssn.middle_names;
1088 l_emp_num := l_cur_ssn.employee_number;--Bug # 9329643
1089 exit;
1090 end loop;
1091 --End Bug# 10419074
1092 ghr_element_entry_api.update_element_entry
1093 ( p_datetrack_update_mode => l_datetrack_update_mode
1094 ,p_effective_date => l_effective_date
1095 ,p_business_group_id => l_business_group_id
1096 ,p_element_entry_id => l_element_entry_id
1097 ,p_object_version_number => l_object_version_number
1098 ,p_input_value_id1 => l_input_value_id_enrol
1099 ,p_entry_value1 => l_screen_entry_value_enrol
1100 ,p_input_value_id2 => l_input_value_id
1101 ,p_entry_value2 => l_screen_entry_value
1102 ,p_effective_start_date => l_out_effective_start_date
1103 ,p_effective_end_date => l_out_effective_end_date
1104 ,p_update_warning => l_out_update_warning
1105 );
1106 l_health_plan_mod := l_health_plan_mod + 1;
1107 --Begin Bug# 10419074
1108 p_retcode := 0;
1109 l_log_text := 'For '||'Employee Name: '|| l_full_name ||
1110 ' Emp No: '|| l_emp_num ||' Health Benefit Pre Tax Conversion Script1 Successfully completed';
1111 p_errbuf := l_log_text;
1112
1113 ghr_wgi_pkg.create_ghr_errorlog(
1114 p_program_name => l_program_name,
1115 p_message_name => substr('HBPT conversion Successful',1,30),
1116 p_log_text => substr(l_log_text,1,2000),
1117 p_log_date => sysdate);
1118 --End Bug# 10419074
1119 exception
1120 when others then
1121 ghr_wgi_pkg.create_ghr_errorlog(
1122 p_program_name => l_program_name,
1123 p_message_name => 'HBPT conversion - ERROR',
1124 p_log_text => 'Error : Health Benefits Pre Tax Conversion Script1 Errored out' ||
1125 ' For Emp No ' || l_emp_num ||
1126 ' Element ' || to_char(l_element_entry_id) ||
1127 ' Assignment ' || to_char(l_assignment_id) ||
1128 ' SQLERR ' || SQLERRM,
1129 p_log_date => sysdate);
1130 commit;
1131 END;--B2
1132 end if;
1133 l_effective_date := to_date('2013/01/13','YYYY/MM/DD');
1134 end loop;--Loop1
1135 BEGIN --B3
1136 if l_health_plan_mod = 0 then --bug# 10419074
1137 ghr_wgi_pkg.create_ghr_errorlog(
1138 p_program_name => l_program_name,
1139 p_message_name => substr('No HBPT conversion',1,30),
1140 p_log_text => 'No Health Benefits Pre Tax Data Modified with Conversion Script1...',
1141 p_log_date => sysdate);
1142
1143 end if;
1144 commit;
1145 exception
1146 when others then
1147 ghr_wgi_pkg.create_ghr_errorlog(
1148 p_program_name => l_program_name,
1149 p_message_name => 'HBPT conversion - ERROR',
1150 p_log_text => 'Error : Health Benefits Pre Tax Conversion Script1 Errored out' ||
1151 ' Element ' || to_char(l_element_entry_id) ||
1152 ' Assignment ' || to_char(l_assignment_id) ||
1153 ' SQLERR ' || SQLERRM,
1154 p_log_date => sysdate);
1155 commit;
1156 end;--B3
1157 END; --B1
1158 END; --A1
1159
1160 ------------------------------------------------------------
1161 --Script for Only Enrolment changes
1162 ------------------------------------------------------------
1163 /* declare
1164
1165 cursor cur_health_benefits is
1166 select b.name name,
1167 f.input_value_id input_value_id,
1168 e.effective_start_date effective_start_date,
1169 e.effective_end_date effective_end_date,
1170 e.element_entry_id element_entry_id,
1171 e.assignment_id assignment_id,
1172 c.business_group_id business_group_id,--Bug# 6735031
1173 e.object_version_number object_version_number,
1174 f.screen_entry_value screen_entry_value
1175 from pay_element_types_f a,
1176 pay_input_values_f b,
1177 pay_element_links_f c,
1178 pay_link_input_values_f d,
1179 pay_element_entries_f e,
1180 pay_element_entry_values_f f
1181 where a.element_type_id = b.element_type_id
1182 and a.element_type_id = c.element_type_id
1183 and c.element_link_id = d.element_link_id
1184 and b.input_value_id = d.input_value_id
1185 and e.element_link_id = c.element_link_id
1186 and f.element_entry_id = e.element_entry_id
1187 and f.input_value_id = b.input_value_id
1188 and c.business_group_id = nvl(p_business_group_id,c.business_group_id)--Bug# 6735031
1189 and e.effective_start_date = f.effective_start_date
1190 and e.effective_end_date = f.effective_end_date
1191 and e.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
1192 and a.element_name = 'Health Benefits'
1193 and b.name = 'Health Plan'
1194 and f.screen_entry_value in ('52');
1195
1196 cursor cur_health_benefits_pt is
1197 select b.name name,
1198 f.input_value_id input_value_id,
1199 e.effective_start_date effective_start_date,
1200 e.effective_end_date effective_end_date,
1201 e.element_entry_id element_entry_id,
1202 e.assignment_id assignment_id,
1203 c.business_group_id business_group_id,--Bug# 6735031
1204 e.object_version_number object_version_number,
1205 f.screen_entry_value screen_entry_value
1206 from pay_element_types_f a,
1207 pay_input_values_f b,
1208 pay_element_links_f c,
1209 pay_link_input_values_f d,
1210 pay_element_entries_f e,
1211 pay_element_entry_values_f f
1212 where a.element_type_id = b.element_type_id
1213 and a.element_type_id = c.element_type_id
1214 and c.element_link_id = d.element_link_id
1215 and b.input_value_id = d.input_value_id
1216 and e.element_link_id = c.element_link_id
1217 and f.element_entry_id = e.element_entry_id
1218 and f.input_value_id = b.input_value_id
1219 and c.business_group_id = nvl(p_business_group_id,c.business_group_id)--Bug# 6735031
1220 and e.effective_start_date = f.effective_start_date
1221 and e.effective_end_date = f.effective_end_date
1222 and e.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
1223 and a.element_name = 'Health Benefits Pre tax'
1224 and b.name = 'Health Plan'
1225 and f.screen_entry_value in ('52');
1226
1227 cursor cur_hb_fr is
1228 select b.name name,
1229 f.input_value_id input_value_id,
1230 e.effective_start_date effective_start_date,
1231 e.effective_end_date effective_end_date,
1232 e.element_entry_id element_entry_id,
1233 e.assignment_id assignment_id,
1234 e.object_version_number object_version_number,
1235 f.screen_entry_value screen_entry_value
1236 from pay_element_types_f a,
1237 pay_input_values_f b,
1238 pay_element_links_f c,
1239 pay_link_input_values_f d,
1240 pay_element_entries_f e,
1241 pay_element_entry_values_f f
1242 where a.element_type_id = b.element_type_id
1243 and a.element_type_id = c.element_type_id
1244 and c.element_link_id = d.element_link_id
1245 and b.input_value_id = d.input_value_id
1246 and e.element_link_id = c.element_link_id
1247 and f.element_entry_id = e.element_entry_id
1248 and f.input_value_id = b.input_value_id
1249 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
1250 and e.effective_start_date = f.effective_start_date
1251 and e.effective_end_date = f.effective_end_date
1252 and e.effective_end_date > l_check_date
1253 and e.element_entry_id = l_element_entry_id
1254 and a.element_name = 'Health Benefits'
1255 and b.name = 'Health Plan'
1256 and f.screen_entry_value in ('52');
1257
1258 cursor cur_hb_pt_fr is
1259 select b.name name,
1260 f.input_value_id input_value_id,
1261 e.effective_start_date effective_start_date,
1262 e.effective_end_date effective_end_date,
1263 e.element_entry_id element_entry_id,
1264 e.assignment_id assignment_id,
1265 e.object_version_number object_version_number,
1266 f.screen_entry_value screen_entry_value
1267 from pay_element_types_f a,
1268 pay_input_values_f b,
1269 pay_element_links_f c,
1270 pay_link_input_values_f d,
1271 pay_element_entries_f e,
1272 pay_element_entry_values_f f
1273 where a.element_type_id = b.element_type_id
1274 and a.element_type_id = c.element_type_id
1275 and c.element_link_id = d.element_link_id
1276 and b.input_value_id = d.input_value_id
1277 and e.element_link_id = c.element_link_id
1278 and f.element_entry_id = e.element_entry_id
1279 and f.input_value_id = b.input_value_id
1280 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
1281 and e.effective_start_date = f.effective_start_date
1282 and e.effective_end_date = f.effective_end_date
1283 and e.effective_end_date > l_check_date
1284 and e.element_entry_id = l_element_entry_id
1285 and a.element_name = 'Health Benefits Pre tax'
1286 and b.name = 'Health Plan'
1287 and f.screen_entry_value in ('52');
1288
1289 cursor cur_hb_enroll is
1290 select f.input_value_id input_value_id,
1291 f.screen_entry_value screen_entry_value
1292 from pay_element_types_f a,
1293 pay_input_values_f b,
1294 pay_element_links_f c,
1295 pay_link_input_values_f d,
1296 pay_element_entries_f e,
1297 pay_element_entry_values_f f
1298 where a.element_type_id = b.element_type_id
1299 and a.element_type_id = c.element_type_id
1300 and c.element_link_id = d.element_link_id
1301 and b.input_value_id = d.input_value_id
1302 and e.element_link_id = c.element_link_id
1303 and f.element_entry_id = e.element_entry_id
1304 and f.input_value_id = b.input_value_id
1305 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
1306 and e.effective_start_date = f.effective_start_date
1307 and e.effective_end_date = f.effective_end_date
1308 and f.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
1309 and a.element_name = 'Health Benefits'
1310 and b.name = 'Enrollment'
1311 and e.element_entry_id = l_element_entry_id;
1312
1313
1314 cursor cur_hb_pt_enroll is
1315 select f.input_value_id input_value_id,
1316 f.screen_entry_value screen_entry_value,
1317 f.effective_start_date effective_start_date,
1318 f.effective_end_date effective_end_date
1319 from pay_element_types_f a,
1320 pay_input_values_f b,
1321 pay_element_links_f c,
1322 pay_link_input_values_f d,
1323 pay_element_entries_f e,
1324 pay_element_entry_values_f f
1325 where a.element_type_id = b.element_type_id
1326 and a.element_type_id = c.element_type_id
1327 and c.element_link_id = d.element_link_id
1328 and b.input_value_id = d.input_value_id
1329 and e.element_link_id = c.element_link_id
1330 and f.element_entry_id = e.element_entry_id
1331 and f.input_value_id = b.input_value_id
1332 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
1333 and e.effective_start_date = f.effective_start_date
1334 and e.effective_end_date = f.effective_end_date
1335 and f.effective_start_date = l_effective_start_date
1336 and f.effective_end_date = l_effective_end_date
1337 and f.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
1338 and a.element_name = 'Health Benefits Pre tax'
1339 and b.name = 'Enrollment'
1340 and e.element_entry_id = l_element_entry_id;
1341
1342
1343 BEGIN --C1
1344
1345 l_update_flag := 0;
1346 l_element_entry_id := null;
1347 l_effective_date := to_date('2013/01/13','YYYY/MM/DD');
1348 l_health_plan_mod := 0;
1349
1350 ----- A. Conversion.
1351 ----- Fetch the data pertaining to Health benefits element and the input value
1352 ----- is Health Plan
1353
1354 for cur_health_benefits_rec in cur_health_benefits
1355 loop
1356 l_name := cur_health_benefits_rec.name;
1357 l_input_value_id := cur_health_benefits_rec.input_value_id;
1358 l_effective_start_date := cur_health_benefits_rec.effective_start_date;
1359 l_effective_end_date := cur_health_benefits_rec.effective_end_date;
1360 l_check_date := cur_health_benefits_rec.effective_end_date;
1361 l_element_entry_id := cur_health_benefits_rec.element_entry_id;
1362 l_assignment_id := cur_health_benefits_rec.assignment_id;
1363 l_business_group_id := cur_health_benefits_rec.business_group_id;--Bug# 6735031
1364 l_object_version_number := cur_health_benefits_rec.object_version_number;
1365 l_screen_entry_value := cur_health_benefits_rec.screen_entry_value;
1366
1367 for cur_hb_enroll_rec in cur_hb_enroll loop
1368 l_input_value_id_enrol := cur_hb_enroll_rec.input_value_id;
1369 l_screen_entry_value_enrol := cur_hb_enroll_rec.screen_entry_value;
1370 exit;
1371 end loop;
1372
1373 IF l_screen_entry_value in ('52') THEN
1374 if l_screen_entry_value_enrol in('4') then
1375 l_update_flag := 1;
1376 l_screen_entry_value_enrol := '1';
1377 end if;
1378 if l_screen_entry_value_enrol in('5') then
1379 l_update_flag := 1;
1380 l_screen_entry_value_enrol := '2';
1381 end if;
1382 end if;
1383
1384 l_exists := false;
1385
1386 if l_effective_start_date >= l_effective_date then
1387 l_datetrack_update_mode := 'CORRECTION';
1388 l_effective_date := l_effective_start_date;
1389 elsif l_effective_start_date < l_effective_date and
1390 to_char(l_effective_end_date,'YYYY/MM/DD') = '4712/12/31' then
1391 l_datetrack_update_mode := 'UPDATE';
1392 ----Check for future rows.
1393 elsif l_effective_start_date < l_effective_date then
1394 for update_mode_a in cur_hb_fr loop
1395 l_exists := true;
1396 exit;
1397 end loop;
1398 If l_exists then
1399 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
1400 Else
1401 l_datetrack_update_mode := 'UPDATE';
1402 End if;
1403 end if;
1404
1405 if l_update_flag = 1 then
1406 BEGIN --C2
1407 l_update_flag := 0;
1408 --Begin Bug# 10419074
1409 for l_cur_ssn in cur_ssn loop
1410 l_full_name := l_cur_ssn.last_name||','|| l_cur_ssn.first_name||' '|| l_cur_ssn.middle_names;
1411 l_emp_num := l_cur_ssn.employee_number;--Bug # 9329643
1412 exit;
1413 end loop;
1414 --End Bug# 10419074
1415 ghr_element_entry_api.update_element_entry
1416 ( p_datetrack_update_mode => l_datetrack_update_mode
1417 ,p_effective_date => l_effective_date
1418 ,p_business_group_id => l_business_group_id
1419 ,p_element_entry_id => l_element_entry_id
1420 ,p_object_version_number => l_object_version_number
1421 ,p_input_value_id1 => l_input_value_id_enrol
1422 ,p_entry_value1 => l_screen_entry_value_enrol
1423 ,p_effective_start_date => l_out_effective_start_date
1424 ,p_effective_end_date => l_out_effective_end_date
1425 ,p_update_warning => l_out_update_warning
1426 );
1427 l_health_plan_mod := l_health_plan_mod + 1;
1428 --Begin Bug# 10419074
1429 p_retcode := 0;
1430 l_log_text := 'For '||'Employee Name: '|| l_full_name ||
1431 ' Emp No: '|| l_emp_num ||' Health Benefit conversion Script2 Successfully completed';
1432 p_errbuf := l_log_text;
1433
1434 ghr_wgi_pkg.create_ghr_errorlog(
1435 p_program_name => l_program_name,
1436 p_message_name => substr('HB conversion Successful',1,30),
1437 p_log_text => substr(l_log_text,1,2000),
1438 p_log_date => sysdate);
1439 --End Bug# 10419074
1440 exception
1441 when others then
1442 ghr_wgi_pkg.create_ghr_errorlog(
1443 p_program_name => l_program_name,
1444 p_message_name => 'HB conversion - ERROR',
1445 p_log_text => 'Error : Health Benefits Conversion Script2 Errored out' ||
1446 ' For Emp No ' || l_emp_num ||
1447 ' Element ' || to_char(l_element_entry_id) ||
1448 ' Assignment ' || to_char(l_assignment_id) ||
1449 ' SQLERR ' || SQLERRM,
1450 p_log_date => sysdate);
1451 commit;
1452 END;--C2
1453 end if;
1454 l_effective_date := to_date('2013/01/13','YYYY/MM/DD');
1455 end loop;
1456 BEGIN --C3
1457 if l_health_plan_mod = 0 then --bug# 10419074
1458 ghr_wgi_pkg.create_ghr_errorlog(
1459 p_program_name => l_program_name,
1460 p_message_name => substr('No HB conversion',1,30),
1461 p_log_text => 'No Health Benefits Data Modified with Conversion Script2...',
1462 p_log_date => sysdate);
1463 end if;
1464 commit;
1465 exception
1466 when others then
1467 ghr_wgi_pkg.create_ghr_errorlog(
1468 p_program_name => l_program_name,
1469 p_message_name => 'HB conversion - ERROR',
1470 p_log_text => 'Error : Health Benefits Conversion Script2 Errored out' ||
1471 ' Element ' || to_char(l_element_entry_id) ||
1472 ' Assignment ' || to_char(l_assignment_id) ||
1473 ' SQLERR ' || SQLERRM,
1474 p_log_date => sysdate);
1475 commit;
1476 END; --C3
1477 ----- FOR 'Health Benefits Pre tax'
1478 l_update_flag := 0;
1479 l_element_entry_id := null;
1480 l_effective_date := to_date('2013/01/13','YYYY/MM/DD');
1481 l_health_plan_mod := 0;
1482
1483 begin --C4
1484 ----- A. Conversion.
1485 ----- Fetch the data pertaining to Health benefits Pre tax element and the input value
1486 ----- is Health Plan
1487
1488 for cur_health_benefits_pt_rec in cur_health_benefits_pt loop
1489 l_name := cur_health_benefits_pt_rec.name;
1490 l_input_value_id := cur_health_benefits_pt_rec.input_value_id;
1491 l_effective_start_date := cur_health_benefits_pt_rec.effective_start_date;
1492 l_effective_end_date := cur_health_benefits_pt_rec.effective_end_date;
1493 l_check_date := cur_health_benefits_pt_rec.effective_end_date;
1494 l_element_entry_id := cur_health_benefits_pt_rec.element_entry_id;
1495 l_assignment_id := cur_health_benefits_pt_rec.assignment_id;
1496 l_business_group_id := cur_health_benefits_pt_rec.business_group_id;--Bug# 6735031
1497 l_object_version_number := cur_health_benefits_pt_rec.object_version_number;
1498 l_screen_entry_value := cur_health_benefits_pt_rec.screen_entry_value;
1499
1500 for cur_hb_pt_enroll_rec in cur_hb_pt_enroll loop
1501 l_input_value_id_enrol := cur_hb_pt_enroll_rec.input_value_id;
1502 l_screen_entry_value_enrol := cur_hb_pt_enroll_rec.screen_entry_value;
1503 exit;
1504 end loop;
1505 IF l_screen_entry_value in ('52') THEN
1506 if l_screen_entry_value_enrol in('4') then
1507 l_update_flag := 1;
1508 l_screen_entry_value_enrol := '1';
1509 end if;
1510 if l_screen_entry_value_enrol in('5') then
1511 l_update_flag := 1;
1512 l_screen_entry_value_enrol := '2';
1513 end if;
1514 end if;
1515
1516 l_exists := false;
1517
1518 if l_effective_start_date >= l_effective_date then
1519 l_datetrack_update_mode := 'CORRECTION';
1520 l_effective_date := l_effective_start_date;
1521 elsif (l_effective_start_date < l_effective_date) and
1522 (to_char(l_effective_end_date,'YYYY/MM/DD') = '4712/12/31') then
1523 l_datetrack_update_mode := 'UPDATE';
1524 ----Check for future rows.
1525 elsif (l_effective_start_date < l_effective_date) then
1526 for update_mode in cur_hb_pt_fr loop
1527 l_exists := true;
1528 exit;
1529 end loop;
1530 If l_exists then
1531 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
1532 Else
1533 l_datetrack_update_mode := 'UPDATE';
1534 End if;
1535 end if;
1536
1537 if l_update_flag = 1 then
1538 BEGIN --C5
1539 l_update_flag := 0;
1540 --Begin Bug# 10419074
1541 for l_cur_ssn in cur_ssn loop
1542 l_full_name := l_cur_ssn.last_name||','|| l_cur_ssn.first_name||' '|| l_cur_ssn.middle_names;
1543 l_emp_num := l_cur_ssn.employee_number;--Bug # 9329643
1544 exit;
1545 end loop;
1546 --End Bug# 10419074
1547 ghr_element_entry_api.update_element_entry
1548 ( p_datetrack_update_mode => l_datetrack_update_mode
1549 ,p_effective_date => l_effective_date
1550 ,p_business_group_id => l_business_group_id
1551 ,p_element_entry_id => l_element_entry_id
1552 ,p_object_version_number => l_object_version_number
1553 ,p_input_value_id1 => l_input_value_id_enrol
1554 ,p_entry_value1 => l_screen_entry_value_enrol
1555 ,p_effective_start_date => l_out_effective_start_date
1556 ,p_effective_end_date => l_out_effective_end_date
1557 ,p_update_warning => l_out_update_warning
1558 );
1559 l_health_plan_mod := l_health_plan_mod + 1;
1560 --Begin Bug# 10419074
1561 p_retcode := 0;
1562 l_log_text := 'For '||'Employee Name: '|| l_full_name ||
1563 ' Emp No: '|| l_emp_num ||' Health Benefit Pre Tax conversion Script2 Successfully completed';
1564 p_errbuf := l_log_text;
1565
1566 ghr_wgi_pkg.create_ghr_errorlog(
1567 p_program_name => l_program_name,
1568 p_message_name => substr('HBPT conversion Successful',1,30),
1569 p_log_text => substr(l_log_text,1,2000),
1570 p_log_date => sysdate);
1571 --End Bug# 10419074
1572 exception
1573 when others then
1574 ghr_wgi_pkg.create_ghr_errorlog(
1575 p_program_name => l_program_name,
1576 p_message_name => 'HBPT conversion - ERROR',
1577 p_log_text => 'Error : Health Benefits Pre Tax Conversion Script2 Errored out' ||
1578 ' For Emp No ' || l_emp_num ||
1579 ' Element ' || to_char(l_element_entry_id) ||
1580 ' Assignment ' || to_char(l_assignment_id) ||
1581 ' SQLERR ' || SQLERRM,
1582 p_log_date => sysdate);
1583 commit;
1584 END; --C5
1585 end if;
1586 l_effective_date := to_date('2013/01/13','YYYY/MM/DD');
1587 end loop;
1588 BEGIN --C6
1589 if l_health_plan_mod = 0 then --bug# 10419074
1590 ghr_wgi_pkg.create_ghr_errorlog(
1591 p_program_name => l_program_name,
1592 p_message_name => substr('No HBPT conversion',1,30),
1593 p_log_text => 'No Health Benefits Pre Tax Data Modified with Conversion Script2...',
1594 p_log_date => sysdate);
1595
1596 end if;
1597 commit;
1598 exception
1599 when others then
1600 ghr_wgi_pkg.create_ghr_errorlog(
1601 p_program_name => l_program_name,
1602 p_message_name => 'HBPT conversion - ERROR',
1603 p_log_text => 'Error : Health Benefits Pre Tax Conversion Script2 Errored out' ||
1604 ' Element ' || to_char(l_element_entry_id) ||
1605 ' Assignment ' || to_char(l_assignment_id) ||
1606 ' SQLERR ' || SQLERRM,
1607 p_log_date => sysdate);
1608 commit;
1609
1610 end; --C6
1611 END; --C4
1612 END; --C1
1613 */
1614 ------------------------------------------------------------
1615 --Script for Only Health Plan changes
1616 ------------------------------------------------------------
1617 /* declare
1618
1619 cursor cur_health_benefits is
1620 select b.name name,
1621 f.input_value_id input_value_id,
1622 e.effective_start_date effective_start_date,
1623 e.effective_end_date effective_end_date,
1624 e.element_entry_id element_entry_id,
1625 e.assignment_id assignment_id,
1626 c.business_group_id business_group_id,--Bug# 6735031
1627 e.object_version_number object_version_number,
1628 f.screen_entry_value screen_entry_value
1629 from pay_element_types_f a,
1630 pay_input_values_f b,
1631 pay_element_links_f c,
1632 pay_link_input_values_f d,
1633 pay_element_entries_f e,
1634 pay_element_entry_values_f f
1635 where a.element_type_id = b.element_type_id
1636 and a.element_type_id = c.element_type_id
1637 and c.element_link_id = d.element_link_id
1638 and b.input_value_id = d.input_value_id
1639 and e.element_link_id = c.element_link_id
1640 and f.element_entry_id = e.element_entry_id
1641 and f.input_value_id = b.input_value_id
1642 and c.business_group_id = nvl(p_business_group_id,c.business_group_id)--Bug# 6735031
1643 and e.effective_start_date = f.effective_start_date
1644 and e.effective_end_date = f.effective_end_date
1645 and e.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
1646 and a.element_name = 'Health Benefits'
1647 and b.name = 'Health Plan'
1648 and f.screen_entry_value in ('KQ');
1649
1650 cursor cur_health_benefits_pt is
1651 select b.name name,
1652 f.input_value_id input_value_id,
1653 e.effective_start_date effective_start_date,
1654 e.effective_end_date effective_end_date,
1655 e.element_entry_id element_entry_id,
1656 e.assignment_id assignment_id,
1657 c.business_group_id business_group_id,--Bug# 6735031
1658 e.object_version_number object_version_number,
1659 f.screen_entry_value screen_entry_value
1660 from pay_element_types_f a,
1661 pay_input_values_f b,
1662 pay_element_links_f c,
1663 pay_link_input_values_f d,
1664 pay_element_entries_f e,
1665 pay_element_entry_values_f f
1666 where a.element_type_id = b.element_type_id
1667 and a.element_type_id = c.element_type_id
1668 and c.element_link_id = d.element_link_id
1669 and b.input_value_id = d.input_value_id
1670 and e.element_link_id = c.element_link_id
1671 and f.element_entry_id = e.element_entry_id
1672 and f.input_value_id = b.input_value_id
1673 and c.business_group_id = nvl(p_business_group_id,c.business_group_id)--Bug# 6735031
1674 and e.effective_start_date = f.effective_start_date
1675 and e.effective_end_date = f.effective_end_date
1676 and e.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
1677 and a.element_name = 'Health Benefits Pre tax'
1678 and b.name = 'Health Plan'
1679 and f.screen_entry_value in ('KQ');
1680
1681 cursor cur_hb_fr is
1682 select b.name name,
1683 f.input_value_id input_value_id,
1684 e.effective_start_date effective_start_date,
1685 e.effective_end_date effective_end_date,
1686 e.element_entry_id element_entry_id,
1687 e.assignment_id assignment_id,
1688 e.object_version_number object_version_number,
1689 f.screen_entry_value screen_entry_value
1690 from pay_element_types_f a,
1691 pay_input_values_f b,
1692 pay_element_links_f c,
1693 pay_link_input_values_f d,
1694 pay_element_entries_f e,
1695 pay_element_entry_values_f f
1696 where a.element_type_id = b.element_type_id
1697 and a.element_type_id = c.element_type_id
1698 and c.element_link_id = d.element_link_id
1699 and b.input_value_id = d.input_value_id
1700 and e.element_link_id = c.element_link_id
1701 and f.element_entry_id = e.element_entry_id
1702 and f.input_value_id = b.input_value_id
1703 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
1704 and e.effective_start_date = f.effective_start_date
1705 and e.effective_end_date = f.effective_end_date
1706 and e.effective_end_date > l_check_date
1707 and e.element_entry_id = l_element_entry_id
1708 and a.element_name = 'Health Benefits'
1709 and b.name = 'Health Plan'
1710 and f.screen_entry_value in ('KQ');
1711
1712 cursor cur_hb_pt_fr is
1713 select b.name name,
1714 f.input_value_id input_value_id,
1715 e.effective_start_date effective_start_date,
1716 e.effective_end_date effective_end_date,
1717 e.element_entry_id element_entry_id,
1718 e.assignment_id assignment_id,
1719 e.object_version_number object_version_number,
1720 f.screen_entry_value screen_entry_value
1721 from pay_element_types_f a,
1722 pay_input_values_f b,
1723 pay_element_links_f c,
1724 pay_link_input_values_f d,
1725 pay_element_entries_f e,
1726 pay_element_entry_values_f f
1727 where a.element_type_id = b.element_type_id
1728 and a.element_type_id = c.element_type_id
1729 and c.element_link_id = d.element_link_id
1730 and b.input_value_id = d.input_value_id
1731 and e.element_link_id = c.element_link_id
1732 and f.element_entry_id = e.element_entry_id
1733 and f.input_value_id = b.input_value_id
1734 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
1735 and e.effective_start_date = f.effective_start_date
1736 and e.effective_end_date = f.effective_end_date
1737 and e.effective_end_date > l_check_date
1738 and e.element_entry_id = l_element_entry_id
1739 and a.element_name = 'Health Benefits Pre tax'
1740 and b.name = 'Health Plan'
1741 and f.screen_entry_value in ('KQ');
1742
1743 cursor cur_hb_enroll is
1744 select f.input_value_id input_value_id,
1745 f.screen_entry_value screen_entry_value
1746 from pay_element_types_f a,
1747 pay_input_values_f b,
1748 pay_element_links_f c,
1749 pay_link_input_values_f d,
1750 pay_element_entries_f e,
1751 pay_element_entry_values_f f
1752 where a.element_type_id = b.element_type_id
1753 and a.element_type_id = c.element_type_id
1754 and c.element_link_id = d.element_link_id
1755 and b.input_value_id = d.input_value_id
1756 and e.element_link_id = c.element_link_id
1757 and f.element_entry_id = e.element_entry_id
1758 and f.input_value_id = b.input_value_id
1759 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
1760 and e.effective_start_date = f.effective_start_date
1761 and e.effective_end_date = f.effective_end_date
1762 and f.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
1763 and a.element_name = 'Health Benefits'
1764 and b.name = 'Enrollment'
1765 and e.element_entry_id = l_element_entry_id;
1766
1767
1768 cursor cur_hb_pt_enroll is
1769 select f.input_value_id input_value_id,
1770 f.screen_entry_value screen_entry_value,
1771 f.effective_start_date effective_start_date,
1772 f.effective_end_date effective_end_date
1773 from pay_element_types_f a,
1774 pay_input_values_f b,
1775 pay_element_links_f c,
1776 pay_link_input_values_f d,
1777 pay_element_entries_f e,
1778 pay_element_entry_values_f f
1779 where a.element_type_id = b.element_type_id
1780 and a.element_type_id = c.element_type_id
1781 and c.element_link_id = d.element_link_id
1782 and b.input_value_id = d.input_value_id
1783 and e.element_link_id = c.element_link_id
1784 and f.element_entry_id = e.element_entry_id
1785 and f.input_value_id = b.input_value_id
1786 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
1787 and e.effective_start_date = f.effective_start_date
1788 and e.effective_end_date = f.effective_end_date
1789 and f.effective_start_date = l_effective_start_date
1790 and f.effective_end_date = l_effective_end_date
1791 and f.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
1792 and a.element_name = 'Health Benefits Pre tax'
1793 and b.name = 'Enrollment'
1794 and e.element_entry_id = l_element_entry_id;
1795
1796 BEGIN --D1
1797 l_update_flag := 0;
1798 l_element_entry_id := null;
1799 l_effective_date := to_date('2013/01/13','YYYY/MM/DD');
1800 l_health_plan_mod := 0;
1801
1802 ----- A. Conversion.
1803 ----- Fetch the data pertaining to Health benefits element and the input value
1804 ----- is Health Plan
1805
1806 for cur_health_benefits_rec in cur_health_benefits
1807 loop
1808 l_name := cur_health_benefits_rec.name;
1809 l_input_value_id := cur_health_benefits_rec.input_value_id;
1810 l_effective_start_date := cur_health_benefits_rec.effective_start_date;
1811 l_effective_end_date := cur_health_benefits_rec.effective_end_date;
1812 l_check_date := cur_health_benefits_rec.effective_end_date;
1813 l_element_entry_id := cur_health_benefits_rec.element_entry_id;
1814 l_assignment_id := cur_health_benefits_rec.assignment_id;
1815 l_business_group_id := cur_health_benefits_rec.business_group_id;--Bug# 6735031
1816 l_object_version_number := cur_health_benefits_rec.object_version_number;
1817 l_screen_entry_value := cur_health_benefits_rec.screen_entry_value;
1818
1819 for cur_hb_enroll_rec in cur_hb_enroll loop
1820 l_input_value_id_enrol := cur_hb_enroll_rec.input_value_id;
1821 l_screen_entry_value_enrol := cur_hb_enroll_rec.screen_entry_value;
1822 exit;
1823 end loop;
1824 if l_screen_entry_value in ('KQ') and l_screen_entry_value_enrol in('1','2') then
1825 l_update_flag := 1;
1826 l_screen_entry_value := 'Q8';
1827 end if;
1828
1829 l_exists := false;
1830
1831 if l_effective_start_date >= l_effective_date then
1832 l_datetrack_update_mode := 'CORRECTION';
1833 l_effective_date := l_effective_start_date;
1834 elsif l_effective_start_date < l_effective_date and
1835 to_char(l_effective_end_date,'YYYY/MM/DD') = '4712/12/31' then
1836 l_datetrack_update_mode := 'UPDATE';
1837 ----Check for future rows.
1838 elsif l_effective_start_date < l_effective_date then
1839 for update_mode_a in cur_hb_fr loop
1840 l_exists := true;
1841 exit;
1842 end loop;
1843 If l_exists then
1844 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
1845 Else
1846 l_datetrack_update_mode := 'UPDATE';
1847 End if;
1848 end if;
1849
1850 if l_update_flag = 1 then
1851 BEGIN --D2
1852 l_update_flag := 0;
1853 --Begin Bug# 10419074
1854 for l_cur_ssn in cur_ssn loop
1855 l_full_name := l_cur_ssn.last_name||','|| l_cur_ssn.first_name||' '|| l_cur_ssn.middle_names;
1856 l_emp_num := l_cur_ssn.employee_number;--Bug # 9329643
1857 exit;
1858 end loop;
1859 --End Bug# 10419074
1860 ghr_element_entry_api.update_element_entry
1861 ( p_datetrack_update_mode => l_datetrack_update_mode
1862 ,p_effective_date => l_effective_date
1863 ,p_business_group_id => l_business_group_id
1864 ,p_element_entry_id => l_element_entry_id
1865 ,p_object_version_number => l_object_version_number
1866 ,p_input_value_id1 => l_input_value_id
1867 ,p_entry_value1 => l_screen_entry_value
1868 ,p_effective_start_date => l_out_effective_start_date
1869 ,p_effective_end_date => l_out_effective_end_date
1870 ,p_update_warning => l_out_update_warning
1871 );
1872 l_health_plan_mod := l_health_plan_mod + 1;
1873 --Begin Bug# 10419074
1874 p_retcode := 0;
1875 l_log_text := 'For '||'Employee Name: '|| l_full_name ||
1876 ' Emp No: '|| l_emp_num ||' Health Benefit conversion Script3 Successfully completed';
1877 p_errbuf := l_log_text;
1878
1879 ghr_wgi_pkg.create_ghr_errorlog(
1880 p_program_name => l_program_name,
1881 p_message_name => substr('HB conversion Successful',1,30),
1882 p_log_text => substr(l_log_text,1,2000),
1883 p_log_date => sysdate);
1884 --End Bug# 10419074
1885 exception
1886 when others then
1887 ghr_wgi_pkg.create_ghr_errorlog(
1888 p_program_name => l_program_name,
1889 p_message_name => 'HB conversion - ERROR',
1890 p_log_text => 'Error : Health Benefits Conversion Script3 Errored out' ||
1891 ' For Emp No ' || l_emp_num ||
1892 ' Element ' || to_char(l_element_entry_id) ||
1893 ' Assignment ' || to_char(l_assignment_id) ||
1894 ' SQLERR ' || SQLERRM,
1895 p_log_date => sysdate);
1896 commit;
1897 END; --D2
1898 end if;
1899 l_effective_date := to_date('2013/01/13','YYYY/MM/DD');
1900 end loop;
1901 BEGIN --D3
1902 if l_health_plan_mod = 0 then --bug# 10419074
1903 ghr_wgi_pkg.create_ghr_errorlog(
1904 p_program_name => l_program_name,
1905 p_message_name => substr('No HB conversion',1,30),
1906 p_log_text => 'No Health Benefits Data Modified with Conversion Script3...',
1907 p_log_date => sysdate);
1908 end if;
1909 commit;
1910 exception
1911 when others then
1912 ghr_wgi_pkg.create_ghr_errorlog(
1913 p_program_name => l_program_name,
1914 p_message_name => 'HB conversion - ERROR',
1915 p_log_text => 'Error : Health Benefits Conversion Script3 Errored out' ||
1916 ' Element ' || to_char(l_element_entry_id) ||
1917 ' Assignment ' || to_char(l_assignment_id) ||
1918 ' SQLERR ' || SQLERRM,
1919 p_log_date => sysdate);
1920 commit;
1921 END; --D3
1922
1923 ----- FOR 'Health Benefits Pre tax'
1924 l_update_flag := 0;
1925 l_element_entry_id := null;
1926 l_effective_date := to_date('2013/01/13','YYYY/MM/DD');
1927 l_health_plan_mod := 0;
1928
1929 begin --D4
1930 ----- A. Conversion.
1931 ----- Fetch the data pertaining to Health benefits Pre tax element and the input value
1932 ----- is Health Plan
1933
1934 for cur_health_benefits_pt_rec in cur_health_benefits_pt loop
1935 l_name := cur_health_benefits_pt_rec.name;
1936 l_input_value_id := cur_health_benefits_pt_rec.input_value_id;
1937 l_effective_start_date := cur_health_benefits_pt_rec.effective_start_date;
1938 l_effective_end_date := cur_health_benefits_pt_rec.effective_end_date;
1939 l_check_date := cur_health_benefits_pt_rec.effective_end_date;
1940 l_element_entry_id := cur_health_benefits_pt_rec.element_entry_id;
1941 l_assignment_id := cur_health_benefits_pt_rec.assignment_id;
1942 l_business_group_id := cur_health_benefits_pt_rec.business_group_id;--Bug# 6735031
1943 l_object_version_number := cur_health_benefits_pt_rec.object_version_number;
1944 l_screen_entry_value := cur_health_benefits_pt_rec.screen_entry_value;
1945
1946 for cur_hb_pt_enroll_rec in cur_hb_pt_enroll loop
1947 l_input_value_id_enrol := cur_hb_pt_enroll_rec.input_value_id;
1948 l_screen_entry_value_enrol := cur_hb_pt_enroll_rec.screen_entry_value;
1949 exit;
1950 end loop;
1951 if l_screen_entry_value in ('KQ') and l_screen_entry_value_enrol in('1','2') then
1952 l_update_flag := 1;
1953 l_screen_entry_value := 'Q8';
1954 end if;
1955
1956 l_exists := false;
1957
1958 if l_effective_start_date >= l_effective_date then
1959 l_datetrack_update_mode := 'CORRECTION';
1960 l_effective_date := l_effective_start_date;
1961 elsif (l_effective_start_date < l_effective_date) and
1962 (to_char(l_effective_end_date,'YYYY/MM/DD') = '4712/12/31') then
1963 l_datetrack_update_mode := 'UPDATE';
1964 ----Check for future rows.
1965 elsif (l_effective_start_date < l_effective_date) then
1966 for update_mode in cur_hb_pt_fr loop
1967 l_exists := true;
1968 exit;
1969 end loop;
1970 If l_exists then
1971 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
1972 Else
1973 l_datetrack_update_mode := 'UPDATE';
1974 End if;
1975 end if;
1976
1977 if l_update_flag = 1 then
1978 BEGIN --D5
1979 l_update_flag := 0;
1980 --Begin Bug# 10419074
1981 for l_cur_ssn in cur_ssn loop
1982 l_full_name := l_cur_ssn.last_name||','|| l_cur_ssn.first_name||' '|| l_cur_ssn.middle_names;
1983 l_emp_num := l_cur_ssn.employee_number;--Bug # 9329643
1984 exit;
1985 end loop;
1986 --End Bug# 10419074
1987 ghr_element_entry_api.update_element_entry
1988 ( p_datetrack_update_mode => l_datetrack_update_mode
1989 ,p_effective_date => l_effective_date
1990 ,p_business_group_id => l_business_group_id
1991 ,p_element_entry_id => l_element_entry_id
1992 ,p_object_version_number => l_object_version_number
1993 ,p_input_value_id1 => l_input_value_id
1994 ,p_entry_value1 => l_screen_entry_value
1995 ,p_effective_start_date => l_out_effective_start_date
1996 ,p_effective_end_date => l_out_effective_end_date
1997 ,p_update_warning => l_out_update_warning
1998 );
1999 l_health_plan_mod := l_health_plan_mod + 1;
2000 --Begin Bug# 10419074
2001 p_retcode := 0;
2002 l_log_text := 'For '||'Employee Name: '|| l_full_name ||
2003 ' Emp No: '|| l_emp_num ||' Health Benefit Pre Tax conversion Script3 Successfully completed';
2004 p_errbuf := l_log_text;
2005
2006 ghr_wgi_pkg.create_ghr_errorlog(
2007 p_program_name => l_program_name,
2008 p_message_name => substr('HBPT conversion Successful',1,30),
2009 p_log_text => substr(l_log_text,1,2000),
2010 p_log_date => sysdate);
2011 --End Bug# 10419074
2012 exception
2013 when others then
2014 ghr_wgi_pkg.create_ghr_errorlog(
2015 p_program_name => l_program_name,
2016 p_message_name => 'HBPT conversion - ERROR',
2017 p_log_text => 'Error : Health Benefits Pre Tax Conversion Script3 Errored out'||
2018 ' For Emp No ' || l_emp_num ||
2019 ' Element ' || to_char(l_element_entry_id) ||
2020 ' Assignment ' || to_char(l_assignment_id) ||
2021 ' SQLERR ' || SQLERRM,
2022 p_log_date => sysdate);
2023 commit;
2024 END; --D5
2025 end if;
2026 l_effective_date := to_date('2013/01/13','YYYY/MM/DD');
2027 end loop;
2028 BEGIN --D6
2029 if l_health_plan_mod = 0 then --bug# 10419074
2030 ghr_wgi_pkg.create_ghr_errorlog(
2031 p_program_name => l_program_name,
2032 p_message_name => substr('No HBPT conversion',1,30),
2033 p_log_text => 'No Health Benefits Pre Tax Data Modified with Conversion Script3...',
2034 p_log_date => sysdate);
2035
2036 end if;
2037 commit;
2038 exception
2039 when others then
2040 ghr_wgi_pkg.create_ghr_errorlog(
2041 p_program_name => l_program_name,
2042 p_message_name => 'HBPT conversion - ERROR',
2043 p_log_text => 'Error : Health Benefits Pre Tax Conversion script3 Errored out' ||
2044 ' Element ' || to_char(l_element_entry_id) ||
2045 ' Assignment ' || to_char(l_assignment_id) ||
2046 ' SQLERR ' || SQLERRM,
2047 p_log_date => sysdate);
2048 commit;
2049 end; --D6
2050 END; --D4
2051 END;*/ --D1
2052
2053 END execute_conv_hlt_plan;
2054
2055 --End Bug# 7537134,9009719,10180368
2056 -- to see Bug# 6594288,6729058 changes open version 115.10
2057 --Begin Bug# 8622486
2058 PROCEDURE execute_tsp_conversion (p_errbuf OUT NOCOPY VARCHAR2,
2059 p_retcode OUT NOCOPY NUMBER,
2060 p_business_group_id in Number,
2061 p_agency_effective_date in varchar2,
2062 p_agency_code IN varchar2,
2063 p_agency_sub_code IN varchar2) IS
2064
2065 l_assignment_id pay_element_entries_f.assignment_id%type;
2066 l_position_id per_all_assignments_f.position_id%type;
2067 l_person_id per_all_assignments_f.person_id%type;
2068 l_agncy_contrib_elig_date per_people_extra_info.pei_information14%type;
2069 l_effective_start_date per_all_people_f.effective_start_date%type;
2070 l_effective_end_date per_all_people_f.effective_end_date%type;
2071 l_retirement_plan pay_element_entry_values_f.screen_entry_value%type;
2072 l_agency_effective_date date;
2073 l_calculated_date date;
2074 l_req VARCHAR2 (25);
2075 l_program_name ghr_process_log.program_name%TYPE;
2076
2077
2078 l_effective_date date;
2079
2080 l_name pay_input_values_f.name%type;
2081 l_input_value_id pay_input_values_f.input_value_id%type;
2082 l_tsp_start_date pay_element_entry_values_f.screen_entry_value%type;
2083 l_elmnt_effective_start_date pay_element_entries_f.effective_start_date%type;
2084 l_elmnt_effective_end_date pay_element_entries_f.effective_end_date%type;
2085 l_element_entry_id pay_element_entries_f.element_entry_id%type;
2086 l_object_version_number pay_element_entries_f.object_version_number%type;
2087 l_screen_entry_value pay_element_entry_values_f.screen_entry_value%type;
2088 l_business_group_id per_assignments_f.business_group_id%type;
2089 l_update_flag number := 0;
2090 l_cotrib_update_flag number := 0;
2091 l_elig_date_flag number := 0;
2092 l_datetrack_update_mode varchar2(25);
2093
2094 l_out_effective_start_date pay_element_entries_f.effective_start_date%type;
2095 l_out_effective_end_date pay_element_entries_f.effective_end_date%type;
2096 l_out_update_warning boolean;
2097
2098 l_exists boolean := false;
2099 l_check_date date;
2100 --Bug # 9329643 Modified SSN to Emp No
2101 l_employee_number per_all_people_f.employee_number%type;
2102 l_employee_name per_all_people_f.full_name%type;
2103
2104 l_tsp_abv_agcy_rec_cnt number :=0;
2105 l_tsp_future_rec_cnt number :=0;
2106 l_process_log_upd_flag number :=0;
2107
2108 CURSOR c_tsp_agncy_date IS
2109 select paf.position_id,ppf.person_id,paf.assignment_id,
2110 ppei.pei_information14 ,ppf.effective_start_date, ppf.effective_end_date,ppf.national_identifier,
2111 ppf.full_name,ppf.employee_number
2112 from per_all_people_f ppf, per_all_assignments_f paf, per_people_extra_info ppei
2113 where ppf.person_id=paf.person_id
2114 and ppf.person_id=ppei.person_id
2115 and ppei.information_type='GHR_US_PER_BENEFIT_INFO'
2116 and paf.primary_flag='Y'
2117 and paf.assignment_type<>'B'
2118 and ppf.current_employee_flag='Y'
2119 and ppf.effective_end_date > l_agency_effective_date
2120 --and fnd_date.canonical_to_date(ppei.pei_information14) >= l_agency_effective_date
2121 and ppei.pei_information14 IS NOT NULL
2122 and ppf.business_group_id=paf.business_group_id
2123 and ppf.business_group_id= NVL(p_business_group_id,ppf.business_group_id)
2124 AND ghr_api.get_position_agency_code_pos(paf.position_id,paf.business_group_id) like SUBSTR(p_agency_code,1,2)||SUBSTR(p_agency_sub_code,1,2)||'%' ;
2125
2126 l_rpa_effective_date ghr_pa_requests.effective_date%type;
2127 l_rpa_tsp_status_code ghr_pa_request_extra_info.rei_information15%type;
2128 l_rpa_agncy_contrib_elig_date ghr_pa_request_extra_info.rei_information17%type;
2129 l_pa_request_id ghr_pa_requests.pa_request_id%type;
2130
2131 CURSOR c_future_actions IS
2132 SELECT pa.pa_request_id,
2133 pa_ei.rei_information17, pa_ei.rei_information15,
2134 pa.effective_date
2135 FROM ghr_pa_request_extra_info pa_ei, ghr_pa_requests pa
2136 WHERE pa_ei.information_type='GHR_US_PAR_BENEFITS'
2137 AND pa.noa_family_code in ('APP','CONV_APP')
2138 AND pa.RETIREMENT_PLAN IN('K','L','M','N')
2139 AND pa.effective_date > sysdate
2140 AND pa_ei.pa_request_id=pa.pa_request_id
2141 AND status ='FUTURE_ACTION'
2142 AND NVL(pa.agency_code,pa.from_agency_code) LIKE SUBSTR(p_agency_code,1,2)||SUBSTR(p_agency_sub_code,1,2)||'%' ;
2143
2144 CURSOR c_get_tsp_value IS
2145 select b.name name,
2146 f.input_value_id input_value_id,
2147 e.effective_start_date effective_start_date,
2148 e.effective_end_date effective_end_date,
2149 e.element_entry_id element_entry_id,
2150 c.business_group_id business_group_id,
2151 e.object_version_number object_version_number,
2152 f.screen_entry_value screen_entry_value
2153 from pay_element_types_f a,
2154 pay_input_values_f b,
2155 pay_element_links_f c,
2156 pay_link_input_values_f d,
2157 pay_element_entries_f e,
2158 pay_element_entry_values_f f
2159 where a.element_type_id = b.element_type_id
2160 and a.element_type_id = c.element_type_id
2161 and c.element_link_id = d.element_link_id
2162 and b.input_value_id = d.input_value_id
2163 and e.element_link_id = c.element_link_id
2164 and f.element_entry_id = e.element_entry_id
2165 and f.input_value_id = b.input_value_id
2166 and c.business_group_id = nvl(p_business_group_id,c.business_group_id)
2167 and e.effective_start_date = f.effective_start_date
2168 and e.effective_end_date = f.effective_end_date
2169 and e.effective_end_date > l_agency_effective_date
2170 and a.element_name = 'TSP'
2171 and b.name = 'Status'
2172 and e.assignment_id = l_assignment_id ;
2173
2174 CURSOR c_get_future_tsp_value IS
2175 select b.name name,
2176 f.input_value_id input_value_id,
2177 e.effective_start_date effective_start_date,
2178 e.effective_end_date effective_end_date,
2179 e.element_entry_id element_entry_id,
2180 e.assignment_id assignment_id,
2181 e.object_version_number object_version_number,
2182 f.screen_entry_value screen_entry_value
2183 from pay_element_types_f a,
2184 pay_input_values_f b,
2185 pay_element_links_f c,
2186 pay_link_input_values_f d,
2187 pay_element_entries_f e,
2188 pay_element_entry_values_f f
2189 where a.element_type_id = b.element_type_id
2190 and a.element_type_id = c.element_type_id
2191 and c.element_link_id = d.element_link_id
2192 and b.input_value_id = d.input_value_id
2193 and e.element_link_id = c.element_link_id
2194 and f.element_entry_id = e.element_entry_id
2195 and f.input_value_id = b.input_value_id
2196 and c.business_group_id = nvl(p_business_group_id,c.business_group_id)
2197 and e.effective_start_date = f.effective_start_date
2198 and e.effective_end_date = f.effective_end_date
2199 and e.effective_end_date > l_check_date
2200 and e.element_entry_id = l_element_entry_id
2201 and a.element_name = 'TSP'
2202 and b.name = 'Status'
2203 and e.assignment_id = l_assignment_id
2204 and e.element_entry_id = l_element_entry_id;
2205
2206 CURSOR c_get_tsp_date IS
2207 select f.screen_entry_value screen_entry_value
2208 from pay_element_types_f a,
2209 pay_input_values_f b,
2210 pay_element_links_f c,
2211 pay_link_input_values_f d,
2212 pay_element_entries_f e,
2213 pay_element_entry_values_f f
2214 where a.element_type_id = b.element_type_id
2215 and a.element_type_id = c.element_type_id
2216 and c.element_link_id = d.element_link_id
2217 and b.input_value_id = d.input_value_id
2218 and e.element_link_id = c.element_link_id
2219 and f.element_entry_id = e.element_entry_id
2220 and f.input_value_id = b.input_value_id
2221 and c.business_group_id = nvl(p_business_group_id,c.business_group_id)
2222 and e.effective_start_date = f.effective_start_date
2223 and e.effective_end_date = f.effective_end_date
2224 and e.effective_end_date > l_agency_effective_date
2225 and a.element_name = 'TSP'
2226 and b.name = 'Status Date'
2227 and e.assignment_id = l_assignment_id ;
2228
2229
2230 l_session_var ghr_history_api.g_session_var_type;
2231 l_sess_date DATE;
2232 l_session_id number;
2233 l_peopleei_data per_people_extra_info%rowtype;
2234
2235 cursor get_sess_date is
2236 select trunc(effective_date)
2237 from fnd_sessions
2238 where session_id = l_session_id;
2239
2240 cursor c_peopleei_getovn(cp_people_ei_id number) is
2241 select object_version_number
2242 from per_people_extra_info
2243 where person_extra_info_id = cp_people_ei_id;
2244
2245 BEGIN
2246 l_req := fnd_profile.VALUE ('CONC_REQUEST_ID');
2247 l_program_name := 'GHR_TSP_CONV_'||l_req;
2248 l_agency_effective_date := fnd_date.canonical_to_date(p_agency_effective_date);
2249 BEGIN --B1
2250 l_tsp_abv_agcy_rec_cnt:=0;
2251 for l_tsp_agncy_date in c_tsp_agncy_date LOOP
2252 l_process_log_upd_flag:=0;
2253 l_assignment_id := l_tsp_agncy_date.assignment_id;
2254 l_position_id := l_tsp_agncy_date.position_id;
2255 l_person_id := l_tsp_agncy_date.person_id;
2256 l_agncy_contrib_elig_date := l_tsp_agncy_date.pei_information14;
2257 l_effective_start_date := l_tsp_agncy_date.effective_start_date;
2258 l_effective_end_date := l_tsp_agncy_date.effective_end_date;
2259 --Bug #9329643 modified to change SSN to employee number
2260 -- l_employee_number := l_tsp_agncy_date.national_identifier;
2261 l_employee_number := l_tsp_agncy_date.employee_number;
2262 l_employee_name := l_tsp_agncy_date.full_name;
2263
2264 IF fnd_date.canonical_to_date(l_agncy_contrib_elig_date) >= l_agency_effective_date THEN
2265 IF l_agency_effective_date < l_effective_start_date THEN
2266 l_calculated_date := l_effective_start_date;
2267 ELSE
2268 l_calculated_date:= l_agency_effective_date;
2269 END IF;
2270 GHR_HISTORY_FETCH.fetch_element_entry_value(
2271 p_element_name => 'Retirement Plan',
2272 p_input_value_name => 'Plan',
2273 p_assignment_id => l_assignment_id,
2274 p_date_effective => l_calculated_date,
2275 p_screen_entry_value => l_retirement_plan
2276 );
2277 IF l_retirement_plan IN('K','L', 'M', 'N') THEN
2278 BEGIN --B2
2279 l_update_flag := 0;
2280 l_cotrib_update_flag :=0;
2281 l_element_entry_id := null;
2282 l_effective_date := l_agency_effective_date;
2283
2284 for l_get_tsp_value in c_get_tsp_value
2285 LOOP
2286 l_name := l_get_tsp_value.name;
2287 l_input_value_id := l_get_tsp_value.input_value_id;
2288 l_elmnt_effective_start_date := l_get_tsp_value.effective_start_date;
2289 l_elmnt_effective_end_date := l_get_tsp_value.effective_end_date;
2290 l_check_date := l_get_tsp_value.effective_end_date;
2291 l_element_entry_id := l_get_tsp_value.element_entry_id;
2292 l_business_group_id := l_get_tsp_value.business_group_id;
2293 l_object_version_number := l_get_tsp_value.object_version_number;
2294 l_screen_entry_value := l_get_tsp_value.screen_entry_value;
2295
2296 IF l_screen_entry_value = ('I') THEN
2297 l_update_flag := 1;
2298 l_screen_entry_value := 'E';
2299 ELSIF l_screen_entry_value = ('W') THEN
2300 l_update_flag := 1;
2301 l_screen_entry_value := 'Y';
2302 ELSIF l_screen_entry_value = ('S') THEN
2303 l_update_flag := 1;
2304 l_screen_entry_value := 'T';
2305 END IF;
2306
2307 l_exists := false;
2308
2309 if l_elmnt_effective_start_date >= l_effective_date then
2310 l_datetrack_update_mode := 'CORRECTION';
2311 elsif l_elmnt_effective_start_date < l_effective_date and
2312 to_char(l_elmnt_effective_end_date,'YYYY/MM/DD') = '4712/12/31' then
2313 l_datetrack_update_mode := 'UPDATE';
2314 ----Check for future rows.
2315 elsif l_elmnt_effective_start_date < l_effective_date then
2316 for l_get_future_tsp_value in c_get_future_tsp_value loop
2317 l_exists := true;
2318 exit;
2319 end loop;
2320 If l_exists then
2321 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
2322 Else
2323 l_datetrack_update_mode := 'UPDATE';
2324 End if;
2325 end if;
2326
2327 if l_update_flag = 1 then
2328 l_update_flag := 0;
2329 BEGIN --B3
2330 ghr_element_entry_api.update_element_entry
2331 ( p_datetrack_update_mode => l_datetrack_update_mode
2332 ,p_effective_date => l_calculated_date
2333 ,p_business_group_id => l_business_group_id
2334 ,p_element_entry_id => l_element_entry_id
2335 ,p_object_version_number => l_object_version_number
2336 ,p_input_value_id3 => l_input_value_id
2337 ,p_entry_value3 => l_screen_entry_value
2338 ,p_effective_start_date => l_out_effective_start_date
2339 ,p_effective_end_date => l_out_effective_end_date
2340 ,p_update_warning => l_out_update_warning
2341 );
2342
2343 l_tsp_abv_agcy_rec_cnt := l_tsp_abv_agcy_rec_cnt + 1;
2344 IF (fnd_date.canonical_to_date(l_agncy_contrib_elig_date) >= l_agency_effective_date
2345 AND fnd_date.canonical_to_date(l_agncy_contrib_elig_date) < SYSDATE) THEN
2346 FOR l_get_tsp_date IN c_get_tsp_date LOOP
2347 l_tsp_start_date := l_get_tsp_date.screen_entry_value;
2348 EXIT;
2349 END LOOP;
2350 l_process_log_upd_flag:=1;
2351 --Bug # 9329643 Modified SSN to Emp No
2352 ghr_wgi_pkg.create_ghr_errorlog(
2353 p_program_name => l_program_name,
2354 p_message_name => 'Upgrade for TSP-Warning',
2355 p_log_text => 'Warning : Please verify the TSP Status Start Date ' ||
2356 ' For Person Name ' || l_employee_name ||
2357 ' Emp No ' || l_employee_number ||
2358 ' system date ' || to_char(sysdate,'DD-MON-RRRR') ||
2359 ' TSP Agency Contrib Elig Date ' || l_calculated_date ||
2360 ' TSP status ' || l_screen_entry_value ||
2361 ' TSP status start Date ' || l_tsp_start_date ||
2362 ' Agency Effective Date ' || l_agency_effective_date,
2363 p_log_date => sysdate);
2364 END IF;
2365 exception
2366 when others then
2367 --Bug # 9329643 Modified SSN to Emp No
2368 ghr_wgi_pkg.create_ghr_errorlog(
2369 p_program_name => l_program_name,
2370 p_message_name => 'Upgrade for TSP-ERROR',
2371 p_log_text => 'Error : Upgrade of TSP Error Processing ' ||
2372 ' For Emp No ' || l_employee_number ||
2373 ' Element ' || to_char(l_element_entry_id) ||
2374 ' Assignment ' || to_char(l_assignment_id) ||
2375 ' SQLERR ' || SQLERRM,
2376 p_log_date => sysdate);
2377 commit;
2378 END;--B3
2379 end if;
2380 end loop;
2381
2382 END; --B2
2383 BEGIN --B4
2384
2385 ghr_history_api.reinit_g_session_var;
2386 l_session_var.person_id := l_person_id;
2387 l_session_var.assignment_id := l_assignment_id;
2388 l_session_var.program_name := 'core';
2389 l_session_var.date_effective := trunc(l_calculated_date);
2390 l_session_var.fire_trigger := 'Y';
2391 ghr_history_api.set_g_session_var (l_session_var);
2392
2393 select userenv('sessionid') INTO l_session_id from dual;
2394 open get_sess_date;
2395 fetch get_sess_date into l_sess_date;
2396 IF get_sess_date%NOTFOUND THEN
2397 INSERT INTO fnd_sessions(SESSION_ID,EFFECTIVE_DATE)
2398 values(l_session_id,l_calculated_date);
2399 ELSIF l_sess_date <> l_calculated_date then
2400 update fnd_sessions set effective_date = l_calculated_date
2401 where session_id = l_session_id;
2402 end if;
2403 close get_sess_date;
2404 ghr_history_fetch.fetch_peopleei(p_person_id => l_person_id,
2405 p_information_type => 'GHR_US_PER_BENEFIT_INFO',
2406 p_date_effective => l_calculated_date,
2407 p_per_ei_data => l_peopleei_data);
2408
2409 open c_peopleei_getovn( l_peopleei_data.person_extra_info_id );
2410 Fetch c_peopleei_getovn into l_peopleei_data.object_version_number;
2411 close c_peopleei_getovn;
2412
2413 IF fnd_date.canonical_to_date(l_peopleei_data.pei_information14) > l_agency_effective_date THEN
2414 IF l_agency_effective_date < l_effective_start_date THEN
2415 IF fnd_date.canonical_to_date(l_peopleei_data.pei_information14) > l_effective_start_date THEN
2416 l_peopleei_data.pei_information14 := fnd_date.date_to_canonical(l_effective_start_date);
2417 l_cotrib_update_flag:=1;
2418 ELSE
2419 l_cotrib_update_flag:=0;
2420 END IF;
2421 ELSE
2422 l_peopleei_data.pei_information14 := fnd_date.date_to_canonical(l_agency_effective_date);
2423 l_cotrib_update_flag:=1;
2424 END IF;
2425 IF l_cotrib_update_flag = 1 THEN
2426 l_cotrib_update_flag:=0;
2427 pe_pei_upd.upd(
2428 p_person_extra_info_id => l_peopleei_data.person_extra_info_id ,
2429 p_request_id => l_peopleei_data.request_id ,
2430 p_program_application_id => l_peopleei_data.program_application_id ,
2431 p_program_id => l_peopleei_data.program_id ,
2432 p_program_update_date => l_peopleei_data.program_update_date ,
2433 p_pei_attribute_category => l_peopleei_data.pei_attribute_category ,
2434 p_pei_attribute1 => l_peopleei_data.pei_attribute1 ,
2435 p_pei_attribute2 => l_peopleei_data.pei_attribute2 ,
2436 p_pei_attribute3 => l_peopleei_data.pei_attribute3 ,
2437 p_pei_attribute4 => l_peopleei_data.pei_attribute4 ,
2438 p_pei_attribute5 => l_peopleei_data.pei_attribute5 ,
2439 p_pei_attribute6 => l_peopleei_data.pei_attribute6 ,
2440 p_pei_attribute7 => l_peopleei_data.pei_attribute7 ,
2441 p_pei_attribute8 => l_peopleei_data.pei_attribute8 ,
2442 p_pei_attribute9 => l_peopleei_data.pei_attribute9 ,
2443 p_pei_attribute10 => l_peopleei_data.pei_attribute10 ,
2444 p_pei_attribute11 => l_peopleei_data.pei_attribute11 ,
2445 p_pei_attribute12 => l_peopleei_data.pei_attribute12 ,
2446 p_pei_attribute13 => l_peopleei_data.pei_attribute13 ,
2447 p_pei_attribute14 => l_peopleei_data.pei_attribute14 ,
2448 p_pei_attribute15 => l_peopleei_data.pei_attribute15 ,
2449 p_pei_attribute16 => l_peopleei_data.pei_attribute16 ,
2450 p_pei_attribute17 => l_peopleei_data.pei_attribute17 ,
2451 p_pei_attribute18 => l_peopleei_data.pei_attribute18 ,
2452 p_pei_attribute19 => l_peopleei_data.pei_attribute19 ,
2453 p_pei_attribute20 => l_peopleei_data.pei_attribute20 ,
2454 p_pei_information_category => l_peopleei_data.pei_information_category ,
2455 p_pei_information1 => l_peopleei_data.pei_information1 ,
2456 p_pei_information2 => l_peopleei_data.pei_information2 ,
2457 p_pei_information3 => l_peopleei_data.pei_information3 ,
2458 p_pei_information4 => l_peopleei_data.pei_information4 ,
2459 p_pei_information5 => l_peopleei_data.pei_information5 ,
2460 p_pei_information6 => l_peopleei_data.pei_information6 ,
2461 p_pei_information7 => l_peopleei_data.pei_information7 ,
2462 p_pei_information8 => l_peopleei_data.pei_information8 ,
2463 p_pei_information9 => l_peopleei_data.pei_information9 ,
2464 p_pei_information10 => l_peopleei_data.pei_information10 ,
2465 p_pei_information11 => l_peopleei_data.pei_information11 ,
2466 p_pei_information12 => l_peopleei_data.pei_information12 ,
2467 p_pei_information13 => l_peopleei_data.pei_information13 ,
2468 p_pei_information14 => l_peopleei_data.pei_information14 ,
2469 p_pei_information15 => l_peopleei_data.pei_information15 ,
2470 p_pei_information16 => l_peopleei_data.pei_information16 ,
2471 p_pei_information17 => l_peopleei_data.pei_information17 ,
2472 p_pei_information18 => l_peopleei_data.pei_information18 ,
2473 p_pei_information19 => l_peopleei_data.pei_information19 ,
2474 p_pei_information20 => l_peopleei_data.pei_information20 ,
2475 p_pei_information21 => l_peopleei_data.pei_information21 ,
2476 p_pei_information22 => l_peopleei_data.pei_information22 ,
2477 p_pei_information23 => l_peopleei_data.pei_information23 ,
2478 p_pei_information24 => l_peopleei_data.pei_information24 ,
2479 p_pei_information25 => l_peopleei_data.pei_information25 ,
2480 p_pei_information26 => l_peopleei_data.pei_information26 ,
2481 p_pei_information27 => l_peopleei_data.pei_information27 ,
2482 p_pei_information28 => l_peopleei_data.pei_information28 ,
2483 p_pei_information29 => l_peopleei_data.pei_information29 ,
2484 p_pei_information30 => l_peopleei_data.pei_information30 ,
2485 p_object_version_number => l_peopleei_data.object_version_number
2486 );
2487
2488
2489
2490 ghr_api.g_api_dml := TRUE;
2491 ghr_history_api.post_update_process;
2492 ghr_history_api.reinit_g_session_var;
2493 ghr_api.g_api_dml := FALSE;
2494
2495 IF (l_agency_effective_date <= fnd_date.canonical_to_date(l_agncy_contrib_elig_date)
2496 AND fnd_date.canonical_to_date(l_agncy_contrib_elig_date) < SYSDATE)
2497 AND l_process_log_upd_flag = 0 THEN
2498 l_tsp_abv_agcy_rec_cnt := l_tsp_abv_agcy_rec_cnt+1;
2499 FOR l_get_tsp_date IN c_get_tsp_date LOOP
2500 l_tsp_start_date := l_get_tsp_date.screen_entry_value;
2501 EXIT;
2502 END LOOP;
2503 --Bug # 9329643 Modified SSN to Emp No
2504 ghr_wgi_pkg.create_ghr_errorlog(
2505 p_program_name => l_program_name,
2506 p_message_name => 'Upgrade for TSP-Warning',
2507 p_log_text => 'Warning : Please verify the TSP Status Start Date ' ||
2508 ' For Person Name ' || l_employee_name ||
2509 ' Emp No ' || l_employee_number ||
2510 ' system date ' || to_char(sysdate,'DD-MON-RRRR') ||
2511 ' TSP Agency Contrib Elig Date ' || l_calculated_date ||
2512 ' TSP status ' || l_screen_entry_value ||
2513 ' TSP status start Date ' || l_tsp_start_date ||
2514 ' Agency Effective Date ' || l_agency_effective_date,
2515 p_log_date => sysdate);
2516
2517 ELSE
2518 l_process_log_upd_flag:=0;
2519 END IF;
2520 END IF;--IF l_cotrib_update_flag = 1
2521 END IF;--IF fnd_date.canonical_to_date(l_peopleei_data.pei_information14)
2522 Exception
2523 When Others then
2524 --Bug # 9329643 Modified SSN to Emp No
2525 ghr_wgi_pkg.create_ghr_errorlog(
2526 p_program_name => l_program_name,
2527 p_message_name => 'B.Person for TSP-ERROR',
2528 p_log_text => 'Error : Upgrade of TSP Person EIT Error ' ||
2529 ' For Emp No ' || l_employee_number ||
2530 ' Element ' || to_char(l_element_entry_id) ||
2531 ' Assignment ' || to_char(l_assignment_id) ||
2532 ' SQLERR ' || SQLERRM,
2533 p_log_date => sysdate);
2534
2535 ghr_history_api.reinit_g_session_var;
2536 ghr_api.g_api_dml := FALSE;
2537
2538 END;--B4
2539 END IF;--l_retirement_plan IN('K','L', 'M
2540 END IF;--IF fnd_date.canonical_to_date(l_agncy_contrib_elig_date) >
2541 END LOOP;--for l_tsp_agncy_date
2542 BEGIN --B4
2543 if l_tsp_abv_agcy_rec_cnt <> 0 then
2544
2545 ghr_wgi_pkg.create_ghr_errorlog(
2546 p_program_name => l_program_name,
2547 p_message_name => 'Upgrade Script for TSP',
2548 p_log_text => 'TSP Data Modified successfully....'
2549 || to_char(l_tsp_abv_agcy_rec_cnt) || ' rows',
2550 p_log_date => sysdate);
2551 else
2552 ghr_wgi_pkg.create_ghr_errorlog(
2553 p_program_name => l_program_name,
2554 p_message_name => 'Upgrade Script for TSP',
2555 p_log_text => 'TSP Data Not required to modify...',
2556 p_log_date => sysdate);
2557 end if;
2558 commit;
2559 END; --B4
2560 END; --B1
2561 --Begin Future Actions TSP update
2562 BEGIN --C1
2563 l_tsp_future_rec_cnt :=0;
2564 for l_future_actions in c_future_actions LOOP
2565
2566 l_pa_request_id := l_future_actions.pa_request_id;
2567 l_rpa_agncy_contrib_elig_date := l_future_actions.rei_information17;
2568 l_rpa_effective_date := l_future_actions.effective_date;
2569 l_rpa_tsp_status_code := l_future_actions.rei_information15;
2570
2571 BEGIN --C2
2572 l_update_flag := 0;
2573 l_elig_date_flag := 0;
2574 IF l_rpa_tsp_status_code = ('I') THEN
2575 l_update_flag := 1;
2576 l_rpa_tsp_status_code := 'E';
2577 ELSIF l_rpa_tsp_status_code = ('W') THEN
2578 l_update_flag := 1;
2579 l_rpa_tsp_status_code := 'Y';
2580 ELSIF l_rpa_tsp_status_code = ('S') THEN
2581 l_update_flag := 1;
2582 l_rpa_tsp_status_code := 'T';
2583 END IF;
2584 IF l_rpa_agncy_contrib_elig_date IS NOT NULL AND
2585 (fnd_date.canonical_to_date(l_rpa_agncy_contrib_elig_date) <> l_rpa_effective_date) THEN
2586 l_elig_date_flag :=1;
2587 END IF;
2588 IF l_update_flag = 1 OR l_elig_date_flag =1 THEN
2589 l_tsp_future_rec_cnt := l_tsp_future_rec_cnt+1;
2590 IF l_update_flag = 1 THEN
2591 l_update_flag := 0;
2592 UPDATE ghr_pa_request_extra_info
2593 SET rei_information15 =l_rpa_tsp_status_code
2594 where information_type='GHR_US_PAR_BENEFITS'
2595 AND pa_request_id = l_pa_request_id;
2596 END IF;
2597 IF l_elig_date_flag = 1 THEN
2598 l_elig_date_flag:=0;
2599 UPDATE ghr_pa_request_extra_info
2600 SET rei_information17 = fnd_date.date_to_canonical(l_rpa_effective_date)
2601 where information_type='GHR_US_PAR_BENEFITS'
2602 AND pa_request_id = l_pa_request_id;
2603 END IF;
2604 END IF;
2605 EXCEPTION
2606 WHEN OTHERS THEN
2607 ghr_wgi_pkg.create_ghr_errorlog(
2608 p_program_name => l_program_name,
2609 p_message_name => 'TSP Future act ERROR: ',
2610 p_log_text => 'Error : Upgrade of TSP Errored ' ||
2611 ' Pa Request Id ' || to_char(l_pa_request_id) ||
2612 ' TSP Status Code ' || l_rpa_tsp_status_code ||
2613 ' SQLERR ' || SQLERRM,
2614 p_log_date => sysdate);
2615 commit;
2616
2617 END;--C2
2618 END LOOP;
2619 IF l_tsp_future_rec_cnt <> 0 THEN
2620 ghr_wgi_pkg.create_ghr_errorlog(
2621 p_program_name => l_program_name,
2622 p_message_name => 'Update Script for Future TSP',
2623 p_log_text => 'Total Future Action records Modified successfully.... ' || to_char(l_tsp_future_rec_cnt) || ' rows',
2624 p_log_date => sysdate);
2625 ELSE
2626 ghr_wgi_pkg.create_ghr_errorlog(
2627 p_program_name => l_program_name,
2628 p_message_name => 'Update Script for Future TSP',
2629 p_log_text => 'TSP(Future Actions) Data Not required to modify...',
2630 p_log_date => sysdate);
2631 END IF;
2632 END;--C1
2633 -- End Future Actions TSP update
2634
2635 END execute_tsp_conversion;
2636 --End Bug# 8622486
2637
2638 END GHR_ELT_TO_BEN_PKG;