DBA Data[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;