[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.6.12010000.3 2008/12/05 05:27:14 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 -- ****** MAIN Program CURSORS *********
238 -- Cursor to verify whether Person benefits EIT is created or not
239 CURSOR c_eit_exists IS
240 SELECT 'x'
241 FROM per_people_info_types
242 WHERE information_type = 'GHR_US_PER_BENEFIT_INFO';
243
244 -- Cursor to pick person records of Federal Persons
245 CURSOR c_batch_persons(c_session_id IN NUMBER,
246 c_batch_no IN NUMBER) IS
247 SELECT pa_request_id person_id, batch_no
248 FROM GHR_MTS_TEMP
249 WHERE session_id = c_session_id
250 AND batch_no = c_batch_no;
251
252 CURSOR c_sessionid is
253 SELECT userenv('sessionid') sesid
254 FROM dual;
255
256 -- Cursor to pick the History Rows for a given Person ID
257 CURSOR c_person_history(p_person_id NUMBER) IS
258 SELECT *
259 FROM ghr_pa_history
260 WHERE person_id = p_person_id
261 AND (
262 (table_name = 'PER_PEOPLE_EXTRA_INFO' and information5 = 'GHR_US_PER_GROUP1') OR
263 (table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
264 AND information4 IN (SELECT b.input_value_id
265 FROM pay_element_types_f a,pay_input_values_f b
266 WHERE a.element_type_id = b.element_type_id
267 AND (
268 (a.element_name = 'FEGLI' AND b.NAME = 'Eligibility Expiration') OR
269 (a.element_name = 'Retirement Plan' AND b.NAME = 'FERS Eligibility Expires') OR
270 (a.element_name = 'Health Benefits' and b.NAME in('LWOP Contingncy Strt Date','LWOP Contingncy End Date',
271 'Child Eq Court Ord Date')) OR
272 (a.element_name = 'Health Benefits Pre tax' and b.NAME in('LWOP Contingncy Strt Date','LWOP Contingncy End Date',
273 'Child Eq Court Ord Date')) OR
274 (a.element_name = 'TSP' and b.NAME in('Agncy Contrib Elig Date','Emp Contrib Elig Date'))
275 )
276 )
277 )
278 )
279 ORDER BY effective_date,table_name,process_date,information1,pa_history_id;
280
281
282 PROCEDURE print_ben_record(p_benefits_eit_rec IN ghr_api.per_benefit_info_type,p_person_id IN NUMBER,
283 p_effective_date IN DATE) IS
284 BEGIN
285 hr_utility.set_location('PERSON ID : '||to_char(p_person_id),500);
286 hr_utility.set_location('Eff Date : '||to_char(p_effective_date),505);
287 hr_utility.set_location('FEGLI Date Elg Exp : '|| p_benefits_eit_rec.FEGLI_Date_Eligibility_Expires,510);
288 hr_utility.set_location('FEHB Date Elg Exp : '|| p_benefits_eit_rec.FEHB_Date_Eligibility_expires,520);
289 hr_utility.set_location('FEHB Date Temp Elg : '|| p_benefits_eit_rec.FEHB_Date_temp_eligibility,530);
290 hr_utility.set_location('FEHB Dte Dep Cer Exp : '|| p_benefits_eit_rec.FEHB_Date_dependent_cert_expir,540);
291 hr_utility.set_location('FEHB LWOP Cont St Dt : '|| p_benefits_eit_rec.FEHB_LWOP_contingency_st_date,550);
292 hr_utility.set_location('FEHB LWOP Cont Ed Dt : '|| p_benefits_eit_rec.FEHB_LWOP_contingency_end_date,560);
293 hr_utility.set_location('FEHB Chld Eq Crt Dt : '|| p_benefits_eit_rec.FEHB_Child_equiry_court_date,570);
294 hr_utility.set_location('FERS Date Elg Exp : '|| p_benefits_eit_rec.FERS_Date_eligibility_expires,580);
295 hr_utility.set_location('FERS Election Dt : '|| p_benefits_eit_rec.FERS_Election_Date,590);
296 hr_utility.set_location('FERS Election Ind : '|| p_benefits_eit_rec.FERS_Election_Indicator,600);
297 hr_utility.set_location('TSP Agn Cont Elg Dt : '|| p_benefits_eit_rec.TSP_Agncy_Contrib_Elig_date,610);
298 hr_utility.set_location('TSP Emp Cont Elg Dt : '|| p_benefits_eit_rec.TSP_Emp_Contrib_Elig_date,620);
299 END print_ben_record;
300
301 -- Procedure to get person Full Name, SSN
302 PROCEDURE get_person_name_ssn(p_person_id IN per_people_f.person_id%TYPE
303 ,p_effective_date IN DATE
304 ,p_full_name OUT NOCOPY per_people_f.full_name%TYPE
305 ,p_national_identifier OUT NOCOPY per_people_f.national_identifier%TYPE
306 ) IS
307 CURSOR cur_per IS
308 SELECT per.full_name
309 ,per.national_identifier
310 FROM per_all_people_f per
311 WHERE per.person_id = p_person_id
312 AND NVL(p_effective_date,TRUNC(sysdate)) between per.effective_start_date
313 and per.effective_end_date;
314 BEGIN
315 FOR cur_per_rec IN cur_per LOOP
316 p_full_name := cur_per_rec.full_name;
317 p_national_identifier := cur_per_rec.national_identifier;
318 END LOOP;
319 EXCEPTION
320 WHEN others THEN
321 p_full_name := NULL ;
322 p_national_identifier := NULL;
323 RAISE;
324 END get_person_name_ssn;
325
326 -- Procedure to BUILD the intermediate Benefits Record
327 PROCEDURE build_benefits_rec(p_pa_history_rec IN ghr_pa_history%rowtype,
328 p_benefits_eit_rec IN OUT nocopy ghr_api.per_benefit_info_type
329 ) IS
330
331 l_benefits_eit_rec ghr_api.per_benefit_info_type;
332 l_element_name VARCHAR2(150);
333 l_input_value VARCHAR2(150);
334 l_input_value_id NUMBER(20);
335
336 CURSOR c_element_inpval(p_input_value_id NUMBER) IS
337 SELECT a.element_name element, b.name input_value
338 FROM pay_element_types_f a,pay_input_values_f b
339 WHERE a.element_type_id = b.element_type_id
340 AND b.input_value_id = p_input_value_id;
341
342
343 BEGIN
344 hr_utility.set_location('Entering build_benefits_rec',0);
345 l_benefits_eit_rec := p_benefits_eit_rec;
346 IF p_pa_history_rec.table_name = 'PER_PEOPLE_EXTRA_INFO' AND p_pa_history_rec.information5 = 'GHR_US_PER_GROUP1' THEN
347 hr_utility.set_location('Assigning values for FEHB Person EIT',5);
348 p_benefits_eit_rec.FEHB_Date_Eligibility_expires := p_pa_history_rec.information19;
349 p_benefits_eit_rec.FEHB_Date_temp_eligibility := p_pa_history_rec.information20;
350 p_benefits_eit_rec.FEHB_Date_dependent_cert_expir := p_pa_history_rec.information21;
351 ELSIF p_pa_history_rec.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F' THEN
352 l_element_name := NULL;
353 l_input_value := NULL;
354 l_input_value_id := p_pa_history_rec.information4;
355 FOR element_inpval_rec IN c_element_inpval(l_input_value_id)
356 LOOP
357 l_element_name := element_inpval_rec.element;
358 l_input_value := element_inpval_rec.input_value;
359 EXIT;
360 END LOOP;
361 IF l_element_name = 'FEGLI' and l_input_value = 'Eligibility Expiration' THEN
362 p_benefits_eit_rec.FEGLI_Date_Eligibility_Expires := p_pa_history_rec.information6;
363 ELSIF l_element_name = 'Retirement Plan' and l_input_value = 'FERS Eligibility Expires' THEN
364 p_benefits_eit_rec.FERS_Date_eligibility_expires := p_pa_history_rec.information6;
365 ELSIF l_element_name = 'Health Benefits' and l_input_value = 'LWOP Contingncy Strt Date' THEN
366 p_benefits_eit_rec.FEHB_LWOP_contingency_st_date := p_pa_history_rec.information6;
367 ELSIF l_element_name = 'Health Benefits' and l_input_value = 'LWOP Contingncy End Date' THEN
368 p_benefits_eit_rec.FEHB_LWOP_contingency_end_date := p_pa_history_rec.information6;
369 ELSIF l_element_name = 'Health Benefits' and l_input_value = 'Child Eq Court Ord Date' THEN
370 p_benefits_eit_rec.FEHB_Child_equiry_court_date := p_pa_history_rec.information6;
371 ELSIF l_element_name = 'Health Benefits Pre tax' and l_input_value = 'LWOP Contingncy Strt Date' THEN
372 p_benefits_eit_rec.FEHB_LWOP_contingency_st_date := p_pa_history_rec.information6;
373 ELSIF l_element_name = 'Health Benefits Pre tax' and l_input_value = 'LWOP Contingncy End Date' THEN
374 p_benefits_eit_rec.FEHB_LWOP_contingency_end_date := p_pa_history_rec.information6;
375 ELSIF l_element_name = 'Health Benefits Pre tax' and l_input_value = 'Child Eq Court Ord Date' THEN
376 p_benefits_eit_rec.FEHB_Child_equiry_court_date := p_pa_history_rec.information6;
377 ELSIF l_element_name = 'TSP' and l_input_value = 'Agncy Contrib Elig Date' THEN
378 p_benefits_eit_rec.TSP_Agncy_Contrib_Elig_date := p_pa_history_rec.information6;
379 ELSIF l_element_name = 'TSP' and l_input_value = 'Emp Contrib Elig Date' THEN
380 p_benefits_eit_rec.TSP_Emp_Contrib_Elig_date := p_pa_history_rec.information6;
381 END IF;
382 ELSE
383 -- SKIP the Record;
384 NULL;
385 END IF;
386 hr_utility.set_location('Leaving build_benefits_rec',10);
387 EXCEPTION
388 WHEN OTHERS THEN
389 p_benefits_eit_rec := l_benefits_eit_rec;
390 RAISE;
391 END build_benefits_rec;
392
393 PROCEDURE insert_benefits_eit_rec(p_person_id IN NUMBER,
394 p_benefits_eit_rec IN ghr_api.per_benefit_info_type,
395 p_effective_date IN DATE) IS
396
397 l_information_type per_people_extra_info.information_type%type;
398 l_person_extra_info_id NUMBER;
399 l_object_version_number NUMBER;
400
401 CURSOR c_person_extra_info(p_person_id NUMBER) IS
402 SELECT person_extra_info_id,
403 object_version_number
404 FROM per_people_extra_info
405 WHERE person_id = p_person_id
406 AND information_type = 'GHR_US_PER_BENEFIT_INFO';
407
408
409 BEGIN
410 hr_utility.set_location('Entering Insert benefits EIT REC',0);
411 l_information_type := 'GHR_US_PER_BENEFIT_INFO';
412 hr_utility.set_location('Calling Create Person Extra Info',10);
413 FOR per_extra_info_rec IN c_person_extra_info(p_person_id)
414 LOOP
415 l_person_extra_info_id := per_extra_info_rec.person_extra_info_id;
416 l_object_version_number := per_extra_info_rec.object_version_number;
417 END LOOP;
418
419 -- print_ben_record(p_benefits_eit_rec,p_person_id,p_effective_date);
420
421 IF l_person_extra_info_id IS NULL THEN
422 ghr_person_extra_info_api.create_person_extra_info
423 (p_validate => false
424 ,p_person_id => p_person_id
425 ,p_information_type => l_information_type
426 ,p_effective_date => p_effective_date
427 ,p_pei_information_category => l_information_type
428 ,p_pei_information3 => p_benefits_eit_rec.FEGLI_Date_Eligibility_Expires
429 ,p_pei_information4 => p_benefits_eit_rec.FEHB_Date_Eligibility_expires
430 ,p_pei_information5 => p_benefits_eit_rec.FEHB_Date_temp_eligibility
431 ,p_pei_information6 => p_benefits_eit_rec.FEHB_Date_dependent_cert_expir
432 ,p_pei_information7 => p_benefits_eit_rec.FEHB_LWOP_contingency_st_date
433 ,p_pei_information8 => p_benefits_eit_rec.FEHB_LWOP_contingency_end_date
434 ,p_pei_information10 => p_benefits_eit_rec.FEHB_Child_equiry_court_date
435 ,p_pei_information11 => p_benefits_eit_rec.FERS_Date_eligibility_expires
436 ,p_pei_information12 => p_benefits_eit_rec.FERS_Election_Date
437 ,p_pei_information13 => p_benefits_eit_rec.FERS_Election_Indicator
438 ,p_pei_information14 => p_benefits_eit_rec.TSP_Agncy_Contrib_Elig_date
439 ,p_pei_information15 => p_benefits_eit_rec.TSP_Emp_Contrib_Elig_date
440 ,p_person_extra_info_id => l_person_extra_info_id
441 ,p_object_version_number => l_object_version_number
442 );
443 hr_utility.set_location('Person Extra Info ID: '||to_char(l_person_extra_info_id),20);
444 hr_utility.set_location('Object Version Number: '||to_char(l_object_version_number),30);
445 ELSE
446 ghr_person_extra_info_api.update_person_extra_info
447 (p_person_extra_info_id => l_person_extra_info_id
448 ,p_object_version_number => l_object_version_number
449 ,p_effective_date => p_effective_date
450 ,p_pei_information3 => p_benefits_eit_rec.FEGLI_Date_Eligibility_Expires
451 ,p_pei_information4 => p_benefits_eit_rec.FEHB_Date_Eligibility_expires
452 ,p_pei_information5 => p_benefits_eit_rec.FEHB_Date_temp_eligibility
453 ,p_pei_information6 => p_benefits_eit_rec.FEHB_Date_dependent_cert_expir
454 ,p_pei_information7 => p_benefits_eit_rec.FEHB_LWOP_contingency_st_date
455 ,p_pei_information8 => p_benefits_eit_rec.FEHB_LWOP_contingency_end_date
456 ,p_pei_information10 => p_benefits_eit_rec.FEHB_Child_equiry_court_date
457 ,p_pei_information11 => p_benefits_eit_rec.FERS_Date_eligibility_expires
458 ,p_pei_information12 => p_benefits_eit_rec.FERS_Election_Date
459 ,p_pei_information13 => p_benefits_eit_rec.FERS_Election_Indicator
460 ,p_pei_information14 => p_benefits_eit_rec.TSP_Agncy_Contrib_Elig_date
461 ,p_pei_information15 => p_benefits_eit_rec.TSP_Emp_Contrib_Elig_date
462 );
463 END IF;
464 hr_utility.set_location('Leaving Insert Benefits EIT REC',40);
465 END insert_benefits_eit_rec;
466
467 -- ***************** MAIN PROGRAM ***************
468 BEGIN
469 FOR s_id IN c_sessionid
470 LOOP
471 l_sid := s_id.sesid;
472 EXIT;
473 END LOOP;
474
475 BEGIN
476 UPDATE fnd_sessions SET SESSION_ID = l_sid
477 WHERE SESSION_ID = l_sid;
478 IF SQL%NOTFOUND THEN
479 INSERT INTO fnd_sessions(SESSION_ID,EFFECTIVE_DATE)
480 VALUES (l_sid,sysdate);
481 END IF;
482 END;
483
484 --hr_utility.set_location('Entering the Conversion Process...',0);
485 l_errbuf := NULL;
486 l_retcode := 0;
487 l_per_err_cnt := 0;
488
489
490 --
491 --
492 l_req := fnd_profile.VALUE ('CONC_REQUEST_ID');
493 l_program_name := 'GHR_BEN_EIT_CREATION_'||l_req;
494 l_dummy := 'y';
495 -- Check whether the benefits information type is created or not.
496 Open c_eit_exists;
497 Fetch c_eit_exists into l_dummy;
498 close c_eit_exists;
499
500 IF l_dummy <> 'x' THEN
501 p_errbuf := 'Benefits EIT GHR_US_PER_BENEFIT_INFO is missing. ' ||
502 'Please run ghinfoty.sql and submit this concurrent program again.';
503 p_retcode := 1;
504 ELSE
505 FOR federal_persons_rec IN c_batch_persons(p_session_id,p_batch_no)
506 LOOP
507 BEGIN
508 l_current_person_id := federal_persons_rec.person_id;
509 -- hr_utility.set_location('Processing Person '||to_char(l_current_person_id)||'....',20);
510 -- initialise Benefits EIT Record
511 l_benefits_eit_rec := NULL;
512 l_new_effective_date := to_date('1900/01/01','YYYY/MM/DD');
513 l_old_effective_date := to_date('1900/01/01','YYYY/MM/DD');
514 l_cnt := 0;
515 OPEN c_person_history(l_current_person_id);
516 LOOP
517 BEGIN
518 FETCH c_person_history INTO l_pa_history_rec;
519 l_cnt := l_cnt + 1;
520 IF c_person_history%FOUND THEN
521 --hr_utility.set_location('Processing history record '||to_char(l_pa_history_rec.pa_history_id)||'....',30);
522 --hr_utility.set_location('History record found',40);
523 l_new_effective_date := l_pa_history_rec.effective_date;
524 IF (l_new_effective_date = l_old_effective_date OR l_cnt = 1) THEN
525 build_benefits_rec(l_pa_history_rec,l_benefits_eit_rec);
526 ELSE
527 insert_benefits_eit_rec(l_current_person_id,l_benefits_eit_rec,l_old_effective_date);
528 build_benefits_rec(l_pa_history_rec,l_benefits_eit_rec);
529 END IF;
530 l_old_effective_date := l_new_effective_date;
531 ELSE
532 hr_utility.set_location('History record NOT found ...',50);
533 -- Added l_Cnt >1 condition. This is to check whether there are history records exists or not.
534 -- If no history record exists for a person, creation of benefits records can be skipped.
535 IF l_cnt > 1 THEN
536 insert_benefits_eit_rec(l_current_person_id,l_benefits_eit_rec,l_old_effective_date);
537 END IF;
538 -- print_ben_record(l_benefits_eit_rec,l_current_person_id,l_old_effective_date);
539 EXIT;
540 END IF;
541 --hr_utility.set_location('Completed Processing History Records. ',60);
542 EXCEPTION
543 WHEN OTHERS THEN
544 --hr_utility.set_location('Error Occured while processing history records',65);
545 Close c_person_history;
546 RAISE;
547 END;
548 END LOOP; -- For Person_history_rec Cursor
549 Close c_person_history;
550 COMMIT;
551 --hr_utility.set_location('Completed Processing for Person '||to_char(l_current_person_id),70);
552 EXCEPTION
553 WHEN OTHERS THEN
554 l_per_err_cnt := l_per_err_cnt + 1 ;
555 --hr_utility.set_location('Error Occured while processing Person ID',75);
556 get_person_name_ssn(l_current_person_id,l_new_effective_date,l_full_name,l_ssn);
557 l_log_text := 'System unable to create Benefits EIT for the Person: '||l_full_name||
558 '; SSN: '||l_ssn||'; Error: '||sqlerrm;
559 ghr_wgi_pkg.create_ghr_errorlog (p_program_name => l_program_name
560 ,p_log_text => l_log_text
561 ,p_message_name => 'Benefits EIT Creation Error'
562 ,p_log_date => SYSDATE
563 );
564 COMMIT;
565 END;
566 END LOOP; -- For Person_rec Cursor
567
568 -- Set Concurrent program completion messages
569 IF l_per_err_cnt > 0 THEN
570 p_retcode := 1;
571 hr_utility.set_location('Ret code ' || to_char(l_retcode),1);
572 p_errbuf := 'Unable to create benefits EIT for some person records. Please see the federal process log for details.';
573 ELSE
574 p_retcode := l_retcode;
575 hr_utility.set_location('Ret code ' || to_char(l_retcode),1);
576 p_errbuf := 'Process Completed Successfully';
577 END IF;
578
579 -- Update the completion status.
580 UPDATE GHR_MTS_TEMP
581 SET completion_status = p_retcode
582 WHERE session_id = p_session_id
583 AND batch_no = p_batch_no;
584
585 COMMIT;
586 END IF; -- End of c_eit_exists%FOUND.
587 EXCEPTION
588 WHEN OTHERS THEN
589 hr_utility.set_location('ERROR Occured '||sqlerrm,100);
590 p_errbuf := 'Process Errored Out with error message: '||sqlerrm;
591 p_retcode := 2;
592 END execute_conversion;
593
594
595 PROCEDURE ValidateRun(p_result OUT nocopy varchar2) IS
596
597 GHR_APPLICATION_ID constant number:=8301;
598 GHR_STATUS_INSTALLED constant varchar2(2):='I';
599
600 cursor csr_ghr_installed is
601 select status
602 from fnd_product_installations
603 where application_id = GHR_APPLICATION_ID;
604
605 l_installed fnd_product_installations.status%type;
606 l_result varchar2(10) ;
607
608 BEGIN
609 l_result := 'FALSE';
610 open csr_ghr_installed;
611 fetch csr_ghr_installed into l_installed;
612 if ( l_installed = GHR_STATUS_INSTALLED ) then
613 l_result := 'TRUE';
614 end if;
615 close csr_ghr_installed;
616
617 p_result := l_result;
618 --
619 END ValidateRun;
620
621 --Begin Bug# 6594288,6729058,7537134 Added this procedure for Concurrent program Process
622 -- Health Benefits Data Conversion
623
624 -- This Procedure is to end date or Create new elements pertaining to
625 -- Health Benefits and benefit pre tax elements.
626 PROCEDURE execute_conv_hlt_plan ( p_errbuf OUT NOCOPY VARCHAR2,
627 p_retcode OUT NOCOPY NUMBER,
628 p_business_group_id in Number) is
629
630 l_assignment_id pay_element_entries_f.assignment_id%type;
631 l_req VARCHAR2 (25);
632 l_ssn per_all_people_f.national_identifier%TYPE;
633 l_program_name ghr_process_log.program_name%TYPE;
634 cursor cur_business_group is
635 select business_group_id from per_assignments_f
636 where assignment_id = l_assignment_id;
637
638 Cursor cur_ssn is
639 SELECT ppf.national_identifier
640 FROM per_assignments_f paf, per_people_f ppf
641 WHERE ppf.person_id = paf.person_id
642 AND paf.primary_flag = 'Y'
643 AND paf.assignment_type <> 'B'
644 AND to_date('2009/01/04','YYYY/MM/DD') BETWEEN paf.effective_start_date
645 AND paf.effective_end_date
646 AND to_date('2009/01/04','YYYY/MM/DD') BETWEEN ppf.effective_start_date
647 AND ppf.effective_end_date
648 AND paf.assignment_id =l_assignment_id;
649
650 l_effective_date date;
651 l_name pay_input_values_f.name%type;
652 l_input_value_id pay_input_values_f.input_value_id%type;
653 l_input_value_id_enrol pay_input_values_f.input_value_id%type;
654 l_effective_start_date pay_element_entries_f.effective_start_date%type;
655 l_effective_end_date pay_element_entries_f.effective_end_date%type;
656 l_element_entry_id pay_element_entries_f.element_entry_id%type;
657 l_object_version_number pay_element_entries_f.object_version_number%type;
658 l_screen_entry_value pay_element_entry_values_f.screen_entry_value%type;
659 l_screen_entry_value_enrol pay_element_entry_values_f.screen_entry_value%type;
660 l_effective_start_date_enrol pay_element_entry_values_f.effective_start_date%type;
661 l_effective_end_date_enrol pay_element_entry_values_f.effective_end_date%type;
662 l_business_group_id per_assignments_f.business_group_id%type;
663 l_update_flag number := 0;
664 l_health_plan_mod number := 0;
665 l_datetrack_update_mode varchar2(25);
666
667 l_out_effective_start_date pay_element_entries_f.effective_start_date%type;
668 l_out_effective_end_date pay_element_entries_f.effective_end_date%type;
669 l_out_update_warning boolean;
670
671 l_exists boolean := false;
672 l_check_date date;
673
674 BEGIN
675 l_req := fnd_profile.VALUE ('CONC_REQUEST_ID');
676 l_program_name := 'GHR_HB_CNVR_'||l_req;
677 -------------------------------------------------------------------------
678 --Script for Health Plan changes
679 -------------------------------------------------------------------------
680 declare
681 cursor cur_health_benefits is
682 select b.name name,
683 f.input_value_id input_value_id,
684 e.effective_start_date effective_start_date,
685 e.effective_end_date effective_end_date,
686 e.element_entry_id element_entry_id,
687 e.assignment_id assignment_id,
688 c.business_group_id business_group_id,--Bug# 6735031
689 e.object_version_number object_version_number,
690 f.screen_entry_value screen_entry_value
691 from pay_element_types_f a,
692 pay_input_values_f b,
693 pay_element_links_f c,
694 pay_link_input_values_f d,
695 pay_element_entries_f e,
696 pay_element_entry_values_f f
697 where a.element_type_id = b.element_type_id
698 and a.element_type_id = c.element_type_id
699 and c.element_link_id = d.element_link_id
700 and b.input_value_id = d.input_value_id
701 and e.element_link_id = c.element_link_id
702 and f.element_entry_id = e.element_entry_id
703 and f.input_value_id = b.input_value_id
704 and c.business_group_id = nvl(p_business_group_id,c.business_group_id)--Bug# 6735031
705 and e.effective_start_date = f.effective_start_date
706 and e.effective_end_date = f.effective_end_date
707 and e.effective_end_date > to_date('2009/01/03','YYYY/MM/DD')
708 and a.element_name = 'Health Benefits'
709 and b.name = 'Health Plan'
710 and f.screen_entry_value in
711 ('2L','4N','5M','5W','6Y','8J','9E','BW','DV','FB','FT','GN','GT','HZ','IE','JA','KS','LN','LT','MP','PU','SA','VW','YN','9F','2C','53');
712
713 cursor cur_health_benefits_pt is
714 select b.name name,
715 f.input_value_id input_value_id,
716 e.effective_start_date effective_start_date,
717 e.effective_end_date effective_end_date,
718 e.element_entry_id element_entry_id,
719 e.assignment_id assignment_id,
720 c.business_group_id business_group_id, --Bug# 6735031
721 e.object_version_number object_version_number,
722 f.screen_entry_value screen_entry_value
723 from pay_element_types_f a,
724 pay_input_values_f b,
725 pay_element_links_f c,
726 pay_link_input_values_f d,
727 pay_element_entries_f e,
728 pay_element_entry_values_f f
729 where a.element_type_id = b.element_type_id
730 and a.element_type_id = c.element_type_id
731 and c.element_link_id = d.element_link_id
732 and b.input_value_id = d.input_value_id
733 and e.element_link_id = c.element_link_id
734 and f.element_entry_id = e.element_entry_id
735 and f.input_value_id = b.input_value_id
736 and c.business_group_id = nvl(p_business_group_id,c.business_group_id) --Bug#6735031
737 and e.effective_start_date = f.effective_start_date
738 and e.effective_end_date = f.effective_end_date
739 and e.effective_end_date > to_date('2009/01/03','YYYY/MM/DD')
740 and a.element_name = 'Health Benefits Pre tax'
741 and b.name = 'Health Plan'
742 and f.screen_entry_value in
743 ('2L','4N','5M','5W','6Y','8J','9E','BW','DV','FB','FT','GN','GT','HZ','IE','JA','KS','LN','LT','MP','PU','SA','VW','YN','9F','2C','53');
744
745 cursor cur_hb_fr is
746 select b.name name,
747 f.input_value_id input_value_id,
748 e.effective_start_date effective_start_date,
749 e.effective_end_date effective_end_date,
750 e.element_entry_id element_entry_id,
751 e.assignment_id assignment_id,
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(l_business_group_id,c.business_group_id)
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 > l_check_date
771 and e.element_entry_id = l_element_entry_id
772 and a.element_name = 'Health Benefits'
773 and b.name = 'Health Plan'
774 and f.screen_entry_value in
775 ('2L','4N','5M','5W','6Y','8J','9E','BW','DV','FB','FT','GN','GT','HZ','IE','JA','KS','LN','LT','MP','PU','SA','VW','YN','9F','2C','53');
776
777 cursor cur_hb_pt_fr is
778 select b.name name,
779 f.input_value_id input_value_id,
780 e.effective_start_date effective_start_date,
781 e.effective_end_date effective_end_date,
782 e.element_entry_id element_entry_id,
783 e.assignment_id assignment_id,
784 e.object_version_number object_version_number,
785 f.screen_entry_value screen_entry_value
786 from pay_element_types_f a,
787 pay_input_values_f b,
788 pay_element_links_f c,
789 pay_link_input_values_f d,
790 pay_element_entries_f e,
791 pay_element_entry_values_f f
792 where a.element_type_id = b.element_type_id
793 and a.element_type_id = c.element_type_id
794 and c.element_link_id = d.element_link_id
795 and b.input_value_id = d.input_value_id
796 and e.element_link_id = c.element_link_id
797 and f.element_entry_id = e.element_entry_id
798 and f.input_value_id = b.input_value_id
799 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
800 and e.effective_start_date = f.effective_start_date
801 and e.effective_end_date = f.effective_end_date
802 and e.effective_end_date > l_check_date
803 and e.element_entry_id = l_element_entry_id
804 and a.element_name = 'Health Benefits Pre tax'
805 and b.name = 'Health Plan'
806 and f.screen_entry_value in
807 ('2L','4N','5M','5W','6Y','8J','9E','BW','DV','FB','FT','GN','GT','HZ','IE','JA','KS','LN','LT','MP','PU','SA','VW','YN','9F','2C','53');
808
809 cursor cur_hb_enroll is
810 select f.input_value_id input_value_id,
811 f.screen_entry_value screen_entry_value
812 from pay_element_types_f a,
813 pay_input_values_f b,
814 pay_element_links_f c,
815 pay_link_input_values_f d,
816 pay_element_entries_f e,
817 pay_element_entry_values_f f
818 where a.element_type_id = b.element_type_id
819 and a.element_type_id = c.element_type_id
820 and c.element_link_id = d.element_link_id
821 and b.input_value_id = d.input_value_id
822 and e.element_link_id = c.element_link_id
823 and f.element_entry_id = e.element_entry_id
824 and f.input_value_id = b.input_value_id
825 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
826 and e.effective_start_date = f.effective_start_date
827 and e.effective_end_date = f.effective_end_date
828 and f.effective_end_date > to_date('2009/01/03','YYYY/MM/DD')
829 and a.element_name = 'Health Benefits'
830 and b.name = 'Enrollment'
831 and e.element_entry_id = l_element_entry_id;
832
833
834 cursor cur_hb_pt_enroll is
835 select f.input_value_id input_value_id,
836 f.screen_entry_value screen_entry_value,
837 f.effective_start_date effective_start_date,
838 f.effective_end_date effective_end_date
839 from pay_element_types_f a,
840 pay_input_values_f b,
841 pay_element_links_f c,
842 pay_link_input_values_f d,
843 pay_element_entries_f e,
844 pay_element_entry_values_f f
845 where a.element_type_id = b.element_type_id
846 and a.element_type_id = c.element_type_id
847 and c.element_link_id = d.element_link_id
848 and b.input_value_id = d.input_value_id
849 and e.element_link_id = c.element_link_id
850 and f.element_entry_id = e.element_entry_id
851 and f.input_value_id = b.input_value_id
852 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
853 and e.effective_start_date = f.effective_start_date
854 and e.effective_end_date = f.effective_end_date
855 and f.effective_start_date = l_effective_start_date
856 and f.effective_end_date = l_effective_end_date
857 and f.effective_end_date > to_date('2009/01/03','YYYY/MM/DD')
858 and a.element_name = 'Health Benefits Pre tax'
859 and b.name = 'Enrollment'
860 and e.element_entry_id = l_element_entry_id;
861
862
863 BEGIN --A1
864
865 l_update_flag := 0;
866 l_element_entry_id := null;
867 l_effective_date := to_date('2009/01/04','YYYY/MM/DD');
868
869
870 ----- A. Conversion.
871 ----- Fetch the data pertaining to Health benefits element and the input value
872 ----- is Health Plan
873
874 for cur_health_benefits_rec in cur_health_benefits
875 loop
876 l_name := cur_health_benefits_rec.name;
877 l_input_value_id := cur_health_benefits_rec.input_value_id;
878 l_effective_start_date := cur_health_benefits_rec.effective_start_date;
879 l_effective_end_date := cur_health_benefits_rec.effective_end_date;
880 l_check_date := cur_health_benefits_rec.effective_end_date;
881 l_element_entry_id := cur_health_benefits_rec.element_entry_id;
882 l_assignment_id := cur_health_benefits_rec.assignment_id;
883 l_business_group_id := cur_health_benefits_rec.business_group_id;--Bug# 6735031
884 l_object_version_number := cur_health_benefits_rec.object_version_number;
885 l_screen_entry_value := cur_health_benefits_rec.screen_entry_value;
886
887 for cur_hb_enroll_rec in cur_hb_enroll loop
888 l_input_value_id_enrol := cur_hb_enroll_rec.input_value_id;
889 l_screen_entry_value_enrol := cur_hb_enroll_rec.screen_entry_value;
890 exit;
891 end loop;
892 IF l_screen_entry_value in
893 ('2L','4N','5M','5W','6Y','8J','9E','BW','DV','FB','FT','GN','GT','HZ','IE','JA','KS','LN','LT','MP','PU','SA','VW','YN') THEN
894 l_update_flag := 1;
895 l_screen_entry_value_enrol := 'Y';
896 l_screen_entry_value := 'ZZ';
897 elsif l_screen_entry_value in('9F','2C') and l_screen_entry_value_enrol in('4','5') THEN
898 l_update_flag := 1;
899 l_screen_entry_value_enrol := 'Y';
900 l_screen_entry_value := 'ZZ';
901 elsif l_screen_entry_value in('53') and l_screen_entry_value_enrol in('4','5') THEN
902 l_update_flag := 1;
903 IF l_screen_entry_value_enrol in('4') THEN
904 l_screen_entry_value_enrol := '1';
905 ELSIF l_screen_entry_value_enrol in('5') THEN
906 l_screen_entry_value_enrol := '2';
907 END IF;
908 l_screen_entry_value := 'V3';
909 end if;
910
911 l_exists := false;
912
913 if l_effective_start_date >= l_effective_date then
914 l_datetrack_update_mode := 'CORRECTION';
915 l_effective_date := l_effective_start_date;
916 elsif l_effective_start_date < l_effective_date and
917 to_char(l_effective_end_date,'YYYY/MM/DD') = '4712/12/31' then
918 l_datetrack_update_mode := 'UPDATE';
919 ----Check for future rows.
920 elsif l_effective_start_date < l_effective_date then
921 for update_mode_a in cur_hb_fr loop
922 l_exists := true;
923 exit;
924 end loop;
925 If l_exists then
926 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
927 Else
928 l_datetrack_update_mode := 'UPDATE';
929 End if;
930 end if;
931
932 if l_update_flag = 1 then
933 l_update_flag := 0;
934 BEGIN --A2
935 ghr_element_entry_api.update_element_entry
936 ( p_datetrack_update_mode => l_datetrack_update_mode
937 ,p_effective_date => l_effective_date
938 ,p_business_group_id => l_business_group_id
939 ,p_element_entry_id => l_element_entry_id
940 ,p_object_version_number => l_object_version_number
941 ,p_input_value_id1 => l_input_value_id_enrol
942 ,p_entry_value1 => l_screen_entry_value_enrol
943 ,p_input_value_id2 => l_input_value_id
944 ,p_entry_value2 => l_screen_entry_value
945 ,p_effective_start_date => l_out_effective_start_date
946 ,p_effective_end_date => l_out_effective_end_date
947 ,p_update_warning => l_out_update_warning
948 );
949 l_health_plan_mod := l_health_plan_mod + 1;
950 exception
951 when others then
952 for l_cur_ssn in cur_ssn loop
953 l_ssn := l_cur_ssn.national_identifier;
954 exit;
955 end loop;
956 ghr_wgi_pkg.create_ghr_errorlog(
957 p_program_name => l_program_name,
958 p_message_name => 'A. Upgrade for HB1-ERROR',
959 p_log_text => 'Error : Upgrade of Health Benefits Error Processing ' ||
960 ' For SSN ' || l_ssn ||
961 ' Element ' || to_char(l_element_entry_id) ||
962 ' Assignment ' || to_char(l_assignment_id) ||
963 ' SQLERR ' || SQLERRM,
964 p_log_date => sysdate);
965 commit;
966 END;--A2
967 end if;
968 l_effective_date := to_date('2009/01/04','YYYY/MM/DD');
969 end loop;
970 BEGIN --A3
971 if l_health_plan_mod <> 0 then
972 ghr_wgi_pkg.create_ghr_errorlog(
973 p_program_name => l_program_name,
974 p_message_name => 'A. Upgrade Script for HB1',
975 p_log_text => 'Health Benefits Data Modified successfully....'
976 || to_char(l_health_plan_mod) || ' rows',
977 p_log_date => sysdate);
978 else
979 ghr_wgi_pkg.create_ghr_errorlog(
980 p_program_name => l_program_name,
981 p_message_name => 'A. Upgrade Script for HB1',
982 p_log_text => 'Health Benefits Data Not required to modify...',
983 p_log_date => sysdate);
984 end if;
985 commit;
986 exception
987 when others then
988 ghr_wgi_pkg.create_ghr_errorlog(
989 p_program_name => l_program_name,
990 p_message_name => 'A. Upgrade Script for HB1',
991 p_log_text => 'Error : Upgrade of Health Benefits Error Processing ' ||
992 ' Element ' || to_char(l_element_entry_id) ||
993 ' Assignment ' || to_char(l_assignment_id) ||
994 ' SQLERR ' || SQLERRM,
995 p_log_date => sysdate);
996 commit;
997 END; --A3
998
999 ----- FOR 'Health Benefits Pre tax'
1000 l_update_flag := 0;
1001 l_element_entry_id := null;
1002 l_effective_date := to_date('2009/01/04','YYYY/MM/DD');
1003 l_health_plan_mod := 0;
1004
1005 begin --B1
1006 ----- A. Conversion.
1007 ----- Fetch the data pertaining to Health benefits Pre tax element and the input value
1008 ----- is Health Plan
1009
1010 for cur_health_benefits_pt_rec in cur_health_benefits_pt loop --Loop1
1011 l_name := cur_health_benefits_pt_rec.name;
1012 l_input_value_id := cur_health_benefits_pt_rec.input_value_id;
1013 l_effective_start_date := cur_health_benefits_pt_rec.effective_start_date;
1014 l_effective_end_date := cur_health_benefits_pt_rec.effective_end_date;
1015 l_check_date := cur_health_benefits_pt_rec.effective_end_date;
1016 l_element_entry_id := cur_health_benefits_pt_rec.element_entry_id;
1017 l_assignment_id := cur_health_benefits_pt_rec.assignment_id;
1018 l_business_group_id := cur_health_benefits_pt_rec.business_group_id;--Bug# 6735031
1019 l_object_version_number := cur_health_benefits_pt_rec.object_version_number;
1020 l_screen_entry_value := cur_health_benefits_pt_rec.screen_entry_value;
1021
1022 for cur_hb_pt_enroll_rec in cur_hb_pt_enroll loop
1023 l_input_value_id_enrol := cur_hb_pt_enroll_rec.input_value_id;
1024 l_screen_entry_value_enrol := cur_hb_pt_enroll_rec.screen_entry_value;
1025 exit;
1026 end loop;
1027
1028 IF l_screen_entry_value in
1029 ('2L','4N','5M','5W','6Y','8J','9E','BW','DV','FB','FT','GN','GT','HZ','IE','JA','KS','LN','LT','MP','PU','SA','VW','YN') THEN
1030 l_update_flag := 1;
1031 l_screen_entry_value_enrol := 'Y';
1032 l_screen_entry_value := 'ZZ';
1033 ELSIF l_screen_entry_value in('9F','2C') AND l_screen_entry_value_enrol in('4','5') THEN
1034 l_update_flag := 1;
1035 l_screen_entry_value_enrol := 'Y';
1036 l_screen_entry_value := 'ZZ';
1037 ELSIF l_screen_entry_value in('53') AND l_screen_entry_value_enrol in('4','5') THEN
1038 l_update_flag := 1;
1039 IF l_screen_entry_value_enrol in('4') THEN
1040 l_screen_entry_value_enrol := '1';
1041 ELSIF l_screen_entry_value_enrol in('5') THEN
1042 l_screen_entry_value_enrol := '2';
1043 END IF;
1044 l_screen_entry_value := 'V3';
1045 END IF;
1046
1047 l_exists := false;
1048
1049 if l_effective_start_date >= l_effective_date then
1050 l_datetrack_update_mode := 'CORRECTION';
1051 l_effective_date := l_effective_start_date;
1052 elsif (l_effective_start_date < l_effective_date) and
1053 (to_char(l_effective_end_date,'YYYY/MM/DD') = '4712/12/31') then
1054 l_datetrack_update_mode := 'UPDATE';
1055 ----Check for future rows.
1056 elsif (l_effective_start_date < l_effective_date) then
1057 for update_mode in cur_hb_pt_fr loop
1058 l_exists := true;
1059 exit;
1060 end loop;
1061 If l_exists then
1062 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
1063 Else
1064 l_datetrack_update_mode := 'UPDATE';
1065 End if;
1066 end if;
1067
1068 if l_update_flag = 1 then
1069 BEGIN --B2
1070 l_update_flag := 0;
1071 ghr_element_entry_api.update_element_entry
1072 ( p_datetrack_update_mode => l_datetrack_update_mode
1073 ,p_effective_date => l_effective_date
1074 ,p_business_group_id => l_business_group_id
1075 ,p_element_entry_id => l_element_entry_id
1076 ,p_object_version_number => l_object_version_number
1077 ,p_input_value_id1 => l_input_value_id_enrol
1078 ,p_entry_value1 => l_screen_entry_value_enrol
1079 ,p_input_value_id2 => l_input_value_id
1080 ,p_entry_value2 => l_screen_entry_value
1081 ,p_effective_start_date => l_out_effective_start_date
1082 ,p_effective_end_date => l_out_effective_end_date
1083 ,p_update_warning => l_out_update_warning
1084 );
1085 l_health_plan_mod := l_health_plan_mod + 1;
1086 exception
1087 when others then
1088 for l_cur_ssn in cur_ssn loop
1089 l_ssn := l_cur_ssn.national_identifier;
1090 exit;
1091 end loop;
1092 ghr_wgi_pkg.create_ghr_errorlog(
1093 p_program_name => l_program_name,
1094 p_message_name => 'A. Upgrade for HBPT1-ERROR',
1095 p_log_text => 'Error : Upgrade of Health Benefits Error Processing ' ||
1096 ' For SSN ' || l_ssn ||
1097 ' Element ' || to_char(l_element_entry_id) ||
1098 ' Assignment ' || to_char(l_assignment_id) ||
1099 ' SQLERR ' || SQLERRM,
1100 p_log_date => sysdate);
1101 commit;
1102 END;--B2
1103 end if;
1104 l_effective_date := to_date('2009/01/04','YYYY/MM/DD');
1105 end loop;--Loop1
1106 BEGIN --B3
1107 if l_health_plan_mod <> 0 then
1108 ghr_wgi_pkg.create_ghr_errorlog(
1109 p_program_name => l_program_name,
1110 p_message_name => 'A. Upgrade Script HBPT1',
1111 p_log_text => 'Health Benefits Pre tax Data Modified successfully....'
1112 || to_char(l_health_plan_mod) || ' rows',
1113 p_log_date => sysdate);
1114 else
1115 ghr_wgi_pkg.create_ghr_errorlog(
1116 p_program_name => l_program_name,
1117 p_message_name => 'A. Upgrade Script HBPT1',
1118 p_log_text => 'Health Benefits Pre tax Data Not required to modify...',
1119 p_log_date => sysdate);
1120
1121 end if;
1122 commit;
1123 exception
1124 when others then
1125 ghr_wgi_pkg.create_ghr_errorlog(
1126 p_program_name => l_program_name,
1127 p_message_name => 'A. Upgrade Script HBPT1',
1128 p_log_text => 'Error : Upgrade of Health Benefits Pre tax Error Processing ' ||
1129 ' Element ' || to_char(l_element_entry_id) ||
1130 ' Assignment ' || to_char(l_assignment_id) ||
1131 ' SQLERR ' || SQLERRM,
1132 p_log_date => sysdate);
1133 commit;
1134 end;--B3
1135 END; --B1
1136 END; --A1
1137 ------------------------------------------------------------
1138 --Script for Only Health Plan changes
1139 ------------------------------------------------------------
1140 declare
1141
1142 cursor cur_health_benefits is
1143 select b.name name,
1144 f.input_value_id input_value_id,
1145 e.effective_start_date effective_start_date,
1146 e.effective_end_date effective_end_date,
1147 e.element_entry_id element_entry_id,
1148 e.assignment_id assignment_id,
1149 c.business_group_id business_group_id,--Bug# 6735031
1150 e.object_version_number object_version_number,
1151 f.screen_entry_value screen_entry_value
1152 from pay_element_types_f a,
1153 pay_input_values_f b,
1154 pay_element_links_f c,
1155 pay_link_input_values_f d,
1156 pay_element_entries_f e,
1157 pay_element_entry_values_f f
1158 where a.element_type_id = b.element_type_id
1159 and a.element_type_id = c.element_type_id
1160 and c.element_link_id = d.element_link_id
1161 and b.input_value_id = d.input_value_id
1162 and e.element_link_id = c.element_link_id
1163 and f.element_entry_id = e.element_entry_id
1164 and f.input_value_id = b.input_value_id
1165 and c.business_group_id = nvl(p_business_group_id,c.business_group_id)--Bug# 6735031
1166 and e.effective_start_date = f.effective_start_date
1167 and e.effective_end_date = f.effective_end_date
1168 and e.effective_end_date > to_date('2009/01/03','YYYY/MM/DD')
1169 and a.element_name = 'Health Benefits'
1170 and b.name = 'Health Plan'
1171 and f.screen_entry_value in ('53','HQ','JV');
1172
1173 cursor cur_health_benefits_pt is
1174 select b.name name,
1175 f.input_value_id input_value_id,
1176 e.effective_start_date effective_start_date,
1177 e.effective_end_date effective_end_date,
1178 e.element_entry_id element_entry_id,
1179 e.assignment_id assignment_id,
1180 c.business_group_id business_group_id,--Bug# 6735031
1181 e.object_version_number object_version_number,
1182 f.screen_entry_value screen_entry_value
1183 from pay_element_types_f a,
1184 pay_input_values_f b,
1185 pay_element_links_f c,
1186 pay_link_input_values_f d,
1187 pay_element_entries_f e,
1188 pay_element_entry_values_f f
1189 where a.element_type_id = b.element_type_id
1190 and a.element_type_id = c.element_type_id
1191 and c.element_link_id = d.element_link_id
1192 and b.input_value_id = d.input_value_id
1193 and e.element_link_id = c.element_link_id
1194 and f.element_entry_id = e.element_entry_id
1195 and f.input_value_id = b.input_value_id
1196 and c.business_group_id = nvl(p_business_group_id,c.business_group_id)--Bug# 6735031
1197 and e.effective_start_date = f.effective_start_date
1198 and e.effective_end_date = f.effective_end_date
1199 and e.effective_end_date > to_date('2009/01/03','YYYY/MM/DD')
1200 and a.element_name = 'Health Benefits Pre tax'
1201 and b.name = 'Health Plan'
1202 and f.screen_entry_value in ('53','HQ','JV');
1203
1204 cursor cur_hb_fr is
1205 select b.name name,
1206 f.input_value_id input_value_id,
1207 e.effective_start_date effective_start_date,
1208 e.effective_end_date effective_end_date,
1209 e.element_entry_id element_entry_id,
1210 e.assignment_id assignment_id,
1211 e.object_version_number object_version_number,
1212 f.screen_entry_value screen_entry_value
1213 from pay_element_types_f a,
1214 pay_input_values_f b,
1215 pay_element_links_f c,
1216 pay_link_input_values_f d,
1217 pay_element_entries_f e,
1218 pay_element_entry_values_f f
1219 where a.element_type_id = b.element_type_id
1220 and a.element_type_id = c.element_type_id
1221 and c.element_link_id = d.element_link_id
1222 and b.input_value_id = d.input_value_id
1223 and e.element_link_id = c.element_link_id
1224 and f.element_entry_id = e.element_entry_id
1225 and f.input_value_id = b.input_value_id
1226 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
1227 and e.effective_start_date = f.effective_start_date
1228 and e.effective_end_date = f.effective_end_date
1229 and e.effective_end_date > l_check_date
1230 and e.element_entry_id = l_element_entry_id
1231 and a.element_name = 'Health Benefits'
1232 and b.name = 'Health Plan'
1233 and f.screen_entry_value in ('53','HQ','JV');
1234
1235 cursor cur_hb_pt_fr is
1236 select b.name name,
1237 f.input_value_id input_value_id,
1238 e.effective_start_date effective_start_date,
1239 e.effective_end_date effective_end_date,
1240 e.element_entry_id element_entry_id,
1241 e.assignment_id assignment_id,
1242 e.object_version_number object_version_number,
1243 f.screen_entry_value screen_entry_value
1244 from pay_element_types_f a,
1245 pay_input_values_f b,
1246 pay_element_links_f c,
1247 pay_link_input_values_f d,
1248 pay_element_entries_f e,
1249 pay_element_entry_values_f f
1250 where a.element_type_id = b.element_type_id
1251 and a.element_type_id = c.element_type_id
1252 and c.element_link_id = d.element_link_id
1253 and b.input_value_id = d.input_value_id
1254 and e.element_link_id = c.element_link_id
1255 and f.element_entry_id = e.element_entry_id
1256 and f.input_value_id = b.input_value_id
1257 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
1258 and e.effective_start_date = f.effective_start_date
1259 and e.effective_end_date = f.effective_end_date
1260 and e.effective_end_date > l_check_date
1261 and e.element_entry_id = l_element_entry_id
1262 and a.element_name = 'Health Benefits Pre tax'
1263 and b.name = 'Health Plan'
1264 and f.screen_entry_value in ('53','HQ','JV');
1265
1266 cursor cur_hb_enroll is
1267 select f.input_value_id input_value_id,
1268 f.screen_entry_value screen_entry_value
1269 from pay_element_types_f a,
1270 pay_input_values_f b,
1271 pay_element_links_f c,
1272 pay_link_input_values_f d,
1273 pay_element_entries_f e,
1274 pay_element_entry_values_f f
1275 where a.element_type_id = b.element_type_id
1276 and a.element_type_id = c.element_type_id
1277 and c.element_link_id = d.element_link_id
1278 and b.input_value_id = d.input_value_id
1279 and e.element_link_id = c.element_link_id
1280 and f.element_entry_id = e.element_entry_id
1281 and f.input_value_id = b.input_value_id
1282 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
1283 and e.effective_start_date = f.effective_start_date
1284 and e.effective_end_date = f.effective_end_date
1285 and f.effective_end_date > to_date('2009/01/03','YYYY/MM/DD')
1286 and a.element_name = 'Health Benefits'
1287 and b.name = 'Enrollment'
1288 and e.element_entry_id = l_element_entry_id;
1289
1290
1291 cursor cur_hb_pt_enroll is
1292 select f.input_value_id input_value_id,
1293 f.screen_entry_value screen_entry_value,
1294 f.effective_start_date effective_start_date,
1295 f.effective_end_date effective_end_date
1296 from pay_element_types_f a,
1297 pay_input_values_f b,
1298 pay_element_links_f c,
1299 pay_link_input_values_f d,
1300 pay_element_entries_f e,
1301 pay_element_entry_values_f f
1302 where a.element_type_id = b.element_type_id
1303 and a.element_type_id = c.element_type_id
1304 and c.element_link_id = d.element_link_id
1305 and b.input_value_id = d.input_value_id
1306 and e.element_link_id = c.element_link_id
1307 and f.element_entry_id = e.element_entry_id
1308 and f.input_value_id = b.input_value_id
1309 and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
1310 and e.effective_start_date = f.effective_start_date
1311 and e.effective_end_date = f.effective_end_date
1312 and f.effective_start_date = l_effective_start_date
1313 and f.effective_end_date = l_effective_end_date
1314 and f.effective_end_date > to_date('2009/01/03','YYYY/MM/DD')
1315 and a.element_name = 'Health Benefits Pre tax'
1316 and b.name = 'Enrollment'
1317 and e.element_entry_id = l_element_entry_id;
1318
1319 BEGIN --D1
1320 l_update_flag := 0;
1321 l_element_entry_id := null;
1322 l_effective_date := to_date('2009/01/04','YYYY/MM/DD');
1323 l_health_plan_mod := 0;
1324
1325 ----- A. Conversion.
1326 ----- Fetch the data pertaining to Health benefits element and the input value
1327 ----- is Health Plan
1328
1329 for cur_health_benefits_rec in cur_health_benefits
1330 loop
1331 l_name := cur_health_benefits_rec.name;
1332 l_input_value_id := cur_health_benefits_rec.input_value_id;
1333 l_effective_start_date := cur_health_benefits_rec.effective_start_date;
1334 l_effective_end_date := cur_health_benefits_rec.effective_end_date;
1335 l_check_date := cur_health_benefits_rec.effective_end_date;
1336 l_element_entry_id := cur_health_benefits_rec.element_entry_id;
1337 l_assignment_id := cur_health_benefits_rec.assignment_id;
1338 l_business_group_id := cur_health_benefits_rec.business_group_id;--Bug# 6735031
1339 l_object_version_number := cur_health_benefits_rec.object_version_number;
1340 l_screen_entry_value := cur_health_benefits_rec.screen_entry_value;
1341
1342 for cur_hb_enroll_rec in cur_hb_enroll loop
1343 l_input_value_id_enrol := cur_hb_enroll_rec.input_value_id;
1344 l_screen_entry_value_enrol := cur_hb_enroll_rec.screen_entry_value;
1345 exit;
1346 end loop;
1347 if l_screen_entry_value in ('53','HQ') and l_screen_entry_value_enrol in('1','2') then
1348 l_update_flag := 1;
1349 l_screen_entry_value := 'V3';
1350 end if;
1351 if l_screen_entry_value in ('JV') and l_screen_entry_value_enrol in('1','2') then
1352 l_update_flag := 1;
1353 l_screen_entry_value := 'JG';
1354 end if;
1355
1356 l_exists := false;
1357
1358 if l_effective_start_date >= l_effective_date then
1359 l_datetrack_update_mode := 'CORRECTION';
1360 l_effective_date := l_effective_start_date;
1361 elsif l_effective_start_date < l_effective_date and
1362 to_char(l_effective_end_date,'YYYY/MM/DD') = '4712/12/31' then
1363 l_datetrack_update_mode := 'UPDATE';
1364 ----Check for future rows.
1365 elsif l_effective_start_date < l_effective_date then
1366 for update_mode_a in cur_hb_fr loop
1367 l_exists := true;
1368 exit;
1369 end loop;
1370 If l_exists then
1371 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
1372 Else
1373 l_datetrack_update_mode := 'UPDATE';
1374 End if;
1375 end if;
1376
1377 if l_update_flag = 1 then
1378 BEGIN --D2
1379 l_update_flag := 0;
1380 ghr_element_entry_api.update_element_entry
1381 ( p_datetrack_update_mode => l_datetrack_update_mode
1382 ,p_effective_date => l_effective_date
1383 ,p_business_group_id => l_business_group_id
1384 ,p_element_entry_id => l_element_entry_id
1385 ,p_object_version_number => l_object_version_number
1386 ,p_input_value_id1 => l_input_value_id
1387 ,p_entry_value1 => l_screen_entry_value
1388 ,p_effective_start_date => l_out_effective_start_date
1389 ,p_effective_end_date => l_out_effective_end_date
1390 ,p_update_warning => l_out_update_warning
1391 );
1392 l_health_plan_mod := l_health_plan_mod + 1;
1393 exception
1394 when others then
1395 for l_cur_ssn in cur_ssn loop
1396 l_ssn := l_cur_ssn.national_identifier;
1397 exit;
1398 end loop;
1399 ghr_wgi_pkg.create_ghr_errorlog(
1400 p_program_name => l_program_name,
1401 p_message_name => 'A. Upgrade for HB2-ERROR',
1402 p_log_text => 'Error : Upgrade of Health Benefits Error Processing ' ||
1403 ' For SSN ' || l_ssn ||
1404 ' Element ' || to_char(l_element_entry_id) ||
1405 ' Assignment ' || to_char(l_assignment_id) ||
1406 ' SQLERR ' || SQLERRM,
1407 p_log_date => sysdate);
1408 commit;
1409 END; --D2
1410 end if;
1411 l_effective_date := to_date('2009/01/04','YYYY/MM/DD');
1412 end loop;
1413 BEGIN --D3
1414 if l_health_plan_mod <> 0 then
1415 ghr_wgi_pkg.create_ghr_errorlog(
1416 p_program_name => l_program_name,
1417 p_message_name => 'A. Upgrade Script for HB2',
1418 p_log_text => 'Health Benefits Data Modified successfully....'
1419 || to_char(l_health_plan_mod) || ' rows',
1420 p_log_date => sysdate);
1421
1422 else
1423 ghr_wgi_pkg.create_ghr_errorlog(
1424 p_program_name => l_program_name,
1425 p_message_name => 'A. Upgrade Script for HB2',
1426 p_log_text => 'Health Benefits Data Not required to modify...',
1427 p_log_date => sysdate);
1428 end if;
1429 commit;
1430 exception
1431 when others then
1432 ghr_wgi_pkg.create_ghr_errorlog(
1433 p_program_name => l_program_name,
1434 p_message_name => 'A. Upgrade Script for HB2',
1435 p_log_text => 'Error : Upgrade of Health Benefits Error Processing ' ||
1436 ' Element ' || to_char(l_element_entry_id) ||
1437 ' Assignment ' || to_char(l_assignment_id) ||
1438 ' SQLERR ' || SQLERRM,
1439 p_log_date => sysdate);
1440 commit;
1441 END; --D3
1442
1443 ----- FOR 'Health Benefits Pre tax'
1444 l_update_flag := 0;
1445 l_element_entry_id := null;
1446 l_effective_date := to_date('2009/01/04','YYYY/MM/DD');
1447 l_health_plan_mod := 0;
1448
1449 begin --D4
1450 ----- A. Conversion.
1451 ----- Fetch the data pertaining to Health benefits Pre tax element and the input value
1452 ----- is Health Plan
1453
1454 for cur_health_benefits_pt_rec in cur_health_benefits_pt loop
1455 l_name := cur_health_benefits_pt_rec.name;
1456 l_input_value_id := cur_health_benefits_pt_rec.input_value_id;
1457 l_effective_start_date := cur_health_benefits_pt_rec.effective_start_date;
1458 l_effective_end_date := cur_health_benefits_pt_rec.effective_end_date;
1459 l_check_date := cur_health_benefits_pt_rec.effective_end_date;
1460 l_element_entry_id := cur_health_benefits_pt_rec.element_entry_id;
1461 l_assignment_id := cur_health_benefits_pt_rec.assignment_id;
1462 l_business_group_id := cur_health_benefits_pt_rec.business_group_id;--Bug# 6735031
1463 l_object_version_number := cur_health_benefits_pt_rec.object_version_number;
1464 l_screen_entry_value := cur_health_benefits_pt_rec.screen_entry_value;
1465
1466 for cur_hb_pt_enroll_rec in cur_hb_pt_enroll loop
1467 l_input_value_id_enrol := cur_hb_pt_enroll_rec.input_value_id;
1468 l_screen_entry_value_enrol := cur_hb_pt_enroll_rec.screen_entry_value;
1469 exit;
1470 end loop;
1471 if l_screen_entry_value in ('53','HQ') and l_screen_entry_value_enrol in('1','2') then
1472 l_update_flag := 1;
1473 l_screen_entry_value := 'V3';
1474 end if;
1475 if l_screen_entry_value in ('JV') and l_screen_entry_value_enrol in('1','2') then
1476 l_update_flag := 1;
1477 l_screen_entry_value := 'JG';
1478 end if;
1479
1480 l_exists := false;
1481
1482 if l_effective_start_date >= l_effective_date then
1483 l_datetrack_update_mode := 'CORRECTION';
1484 l_effective_date := l_effective_start_date;
1485 elsif (l_effective_start_date < l_effective_date) and
1486 (to_char(l_effective_end_date,'YYYY/MM/DD') = '4712/12/31') then
1487 l_datetrack_update_mode := 'UPDATE';
1488 ----Check for future rows.
1489 elsif (l_effective_start_date < l_effective_date) then
1490 for update_mode in cur_hb_pt_fr loop
1491 l_exists := true;
1492 exit;
1493 end loop;
1494 If l_exists then
1495 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
1496 Else
1497 l_datetrack_update_mode := 'UPDATE';
1498 End if;
1499 end if;
1500
1501 if l_update_flag = 1 then
1502 BEGIN --D5
1503 l_update_flag := 0;
1504 ghr_element_entry_api.update_element_entry
1505 ( p_datetrack_update_mode => l_datetrack_update_mode
1506 ,p_effective_date => l_effective_date
1507 ,p_business_group_id => l_business_group_id
1508 ,p_element_entry_id => l_element_entry_id
1509 ,p_object_version_number => l_object_version_number
1510 ,p_input_value_id1 => l_input_value_id
1511 ,p_entry_value1 => l_screen_entry_value
1512 ,p_effective_start_date => l_out_effective_start_date
1513 ,p_effective_end_date => l_out_effective_end_date
1514 ,p_update_warning => l_out_update_warning
1515 );
1516 l_health_plan_mod := l_health_plan_mod + 1;
1517 exception
1518 when others then
1519 for l_cur_ssn in cur_ssn loop
1520 l_ssn := l_cur_ssn.national_identifier;
1521 exit;
1522 end loop;
1523 ghr_wgi_pkg.create_ghr_errorlog(
1524 p_program_name => l_program_name,
1525 p_message_name => 'A. Upgrade for HBPT3-ERROR',
1526 p_log_text => 'Error : Upgrade of Health Benefits Pre tax Error Processing '||
1527 ' For SSN ' || l_ssn ||
1528 ' Element ' || to_char(l_element_entry_id) ||
1529 ' Assignment ' || to_char(l_assignment_id) ||
1530 ' SQLERR ' || SQLERRM,
1531 p_log_date => sysdate);
1532 commit;
1533 END; --D5
1534 end if;
1535 l_effective_date := to_date('2009/01/04','YYYY/MM/DD');
1536 end loop;
1537 BEGIN --D6
1538 if l_health_plan_mod <> 0 then
1539 ghr_wgi_pkg.create_ghr_errorlog(
1540 p_program_name => l_program_name,
1541 p_message_name => 'A. Upgrade Script HBPT3',
1542 p_log_text => 'Health Benefits Pre tax Data Modified successfully....'
1543 || to_char(l_health_plan_mod) || ' rows',
1544 p_log_date => sysdate);
1545 else
1546 ghr_wgi_pkg.create_ghr_errorlog(
1547 p_program_name => l_program_name,
1548 p_message_name => 'A. Upgrade Script HBPT3',
1549 p_log_text => 'Health Benefits Pre tax Data Not required to modify...',
1550 p_log_date => sysdate);
1551
1552 end if;
1553 commit;
1554 exception
1555 when others then
1556 ghr_wgi_pkg.create_ghr_errorlog(
1557 p_program_name => l_program_name,
1558 p_message_name => 'A. Upgrade Script HBPT3',
1559 p_log_text => 'Error : Upgrade of Health Benefits Pre tax Error Processing ' ||
1560 ' Element ' || to_char(l_element_entry_id) ||
1561 ' Assignment ' || to_char(l_assignment_id) ||
1562 ' SQLERR ' || SQLERRM,
1563 p_log_date => sysdate);
1564 commit;
1565 end; --D6
1566 END; --D4
1567 END; --D1
1568 END execute_conv_hlt_plan;
1569
1570 --End Bug# 7537134
1571 -- to see Bug# 6594288,6729058 changes open version 115.10
1572
1573 END GHR_ELT_TO_BEN_PKG;